WebSVN – DevTools – /generate_release_notes/trunk/src/tools/jats_get_releasenote_data.pl

########################################################################
# Copyright (c) VIX TECHNOLOGY (AUST) LTD
#
# Module name   : jats_get_releasenote_data.pl
# Module type   : Makefile system
# Compiler(s)   : Perl
# Environment(s): jats
#
# Description   : Extract Data from RM in order to generate a release note
#                 The data is stored in XML
#
# Improvements  : Catpure non ripple build reason for Release
#                 Release ReleaseName
#                 Project ProjectName 
#                 BuildFilters BuildMachines
#                 Build Standard
#                 Build Environment
#
#                 Get Jira Credentials from DataBase
#
#
# Usage:        See POD
#
#......................................................................#

require 5.008_002;
use strict;
use warnings;

use Pod::Usage;
use Getopt::Long;
use XML::Simple;
use JSON;
use LWP::UserAgent;
use MIME::Base64;
use POSIX qw(strftime);
use Encode qw(decode encode);
use Getopt::Long;
use Pod::Usage;

use JatsError;
use JatsSystem;
use FileUtils;
use JatsRmApi;
use ArrayHashUtils;
use DBI;

my $VERSION = "1.0.0";                                      # Update this. Inserted into meta data field

my $opt_verbose = 0;
my $opt_help = 0;
my $opt_pvid;
my $opt_prev_pvid;
my $opt_outfile;

#
#   Data Items
#
my $RM_DB;
my %pkgData;
my %pkgPrevData;
my @dependData;
my @dependRunData;
my @unitTestData;
my @additionalNotesData;
my @buildDepDiff;
my @JiraIssues;
my @JiraIssueList;
my %ClearQuestIssueData;
my @ClearQuestIssues;
my @FileList;                                               # Place Holder

#-------------------------------------------------------------------------------
# Function        : Main Entry
#
# Description     :
#
# Inputs          :
#
# Returns         :
#
{
    my $result = GetOptions (
                    "help+"         => \$opt_help,          # flag, multiple use allowed
                    "manual:3"      => \$opt_help,
                    "verbose:+"     => \$opt_verbose,       # flag
                    "pvid:n"        => \$opt_pvid,          # Number
                    "outfile:s"     => \$opt_outfile,       # String
                    );

    #
    #   Process help and manual options
    #
    pod2usage(-verbose => 0, -message => "Version: $VERSION")  if ($opt_help == 1  || ! $result);
    pod2usage(-verbose => 1)  if ($opt_help == 2 );
    pod2usage(-verbose => 2)  if ($opt_help > 2);

    ErrorConfig( 'name'    =>'GetRnInfo', 'verbose' => $opt_verbose );

    #
    #   Sanity Check
    #
    Error ("Must provide pvid") unless $opt_pvid;
    Error ("GBE_JIRA_URL is not configured") unless exists ($ENV{GBE_JIRA_URL});
    Error ("GBE_JIRA_USERNAME is not configured") unless exists ($ENV{GBE_JIRA_USERNAME});
    Error ("GBE_JIRA_PASSWORD is not configured") unless exists ($ENV{GBE_JIRA_PASSWORD});

    #
    #   Get Data Item
    #
    GetPkgInfo($opt_pvid, \%pkgData);
    $opt_prev_pvid = $pkgData{prev_pvid} || $pkgData{'-prev_pvid'};

    #
    #   Get Data on immediate Previous version
    #
    if ($opt_prev_pvid)
    {
        GetPkgInfo($opt_prev_pvid, \%pkgPrevData);
        foreach my $field ( qw(pvid name version modified_stamp pname psuffix dlocked build_type)) {
            $pkgData{previous}{$field} = $pkgPrevData{$field};
        }
    }

    GetPkgIssues();
    GetBuildDependencies();
    GetRunTimeDependencies();
    GetUnitTests();
    GetNotes();
    GetBuildDepDiff();
    GetIssues();

    #
    #   Create a single data structure
    #
    my $data;
   
    $data->{package} = \%pkgData;   
    $data->{build}{dependency} = \@dependData;   
    $data->{builddep}{dependencydiff} = \@buildDepDiff;   
    $data->{runtime}{dependency} = \@dependRunData;   
    $data->{unit_tests}{test} = \@unitTestData;   
    $data->{additional_notes}{note} = \@additionalNotesData;   
    $data->{issues}{jira} = \@JiraIssues;
    $data->{issues}{clearquest} = \@ClearQuestIssues;
    $data->{files}{file} = \@FileList;

    #
    #    Insert some metadata
    #        created datetime in same format as that from Release Manager
    #
    $data->{meta}{data_version} = $VERSION;
    $data->{meta}{datetime} = time;
    $data->{meta}{created} = strftime "%Y-%m-%d %H:%M:%S.0", gmtime(time);

    #
    #   Determine the name of the output file
    #
    unless ($opt_outfile) {
       $opt_outfile = join('_', $pkgData{name}, $pkgData{version});
       $opt_outfile .= '.xml';
    }

    #
    #   Write out sections of XML
    #       Want control over the output order
    #       Use lots of attributes and only elements for arrays
    #       Save as one attribute per line - for readability
    #
    Verbose("Creating: " . DisplayPath($opt_outfile));
    my $xs = XML::Simple->new( NoAttr =>0, AttrIndent => 1 );

    open (my $XML, '>', $opt_outfile) || Error ("Cannot create output file: $opt_outfile", $!);
    print $XML ('<?xml version="1.0" encoding="UTF-8"?>' . "\n");
    print $XML ("<package_data>\n");
    foreach my $section (qw( meta package build builddep runtime unit_tests additional_notes issues files)) {
        print $XML $xs->XMLout($data->{$section}, 'RootName' => $section);
    }
    print $XML ("</package_data>\n");
    close $XML;

    #
    #   All done
    #
    exit 0;
}

#-------------------------------------------------------------------------------
# Function        : populateHash 
#
# Description     : Put an array of data items into a hash
#                   Clean white space from the data
#                   Replace /r and /n sequences with a single <br>
#
# Inputs          : pHash           - ref to output hash
#                   pRow            - Ref to the row data 
#                   pItems          - Ref to an hash array of entry names
#
# Returns         : pHash
#
sub populateHash
{
    my ($pHash, $pRow, $pItems) = @_;

    foreach my $item ( @{$pItems} ) {
        my $data = shift @{$pRow};

        if (defined $data)
        {
            $data =~ s~^\s+~~;
            $data =~ s~\s+$~~;

            #
            #   Clean multiline items
            #
            if ($data =~ m~\n|\r~)
            {
                $data =~ tr~\r~\n~;
                $data =~ tr~\n~\n~s;
                $data =~ s~\n~<br>~g;
                $data =~ s~<br><br>~<br>~gi;
            }

            #
            #   Convert from iso-8859-1 into utf-8
            #   Release Manger appears to store data in iso-8859-1
            #   Note sure about ClearQuest data 
            #
            $data = decode( 'iso-8859-1', $data );
            $data = encode( 'utf-8', $data );

            #
            #   Some other nessessary conversions
            #   Appears to come from ClearQuest data, or RM fields pasted from ClearQuest
            #
            $data =~ s/\023/-/g;                    # Some form of -
            $data =~ s/\x1C/"/g;                    # Some Forms of ' and "
            $data =~ s/\x1D/"/g;
            $data =~ s/\x18/'/g;
            $data =~ s/\x19/'/g;
            $data =~ s/\t/&#9;/g;                   # Tab
            $data =~ s/[[:cntrl:]]/?/g;             # Control Chars
#            $data =~ s/[^[:ascii:]]/?/g;           # Non Ascii

            #
            #   Store in hash
            #
            $pHash->{$item} = $data;
        }
    }
    return $pHash;
}

#-------------------------------------------------------------------------------
# Function        : performSqlQueryCallback 
#
# Description     : Perform a general Sql query and invoke a user function for
#                   each row of results
#
# Inputs          : $fname                  - Name of query for error reporting
#                   $m_sqlstr               - Query string
#                   $f_process              - Function called for each row in the result
#                                             Use closure to have callback modify other data
#
# Returns         : Number of rows found
#
sub performSqlQueryCallback
{
    my ($fname, $m_sqlstr, $f_process ) = @_;
    my $found = 0;

    #
    #   Connect to the database - once
    #
    connectRM(\$RM_DB, 0) unless $RM_DB;

    $m_sqlstr =~ s~\s+~ ~g;
    Verbose3("SQL:", $m_sqlstr);
    my $sth = $RM_DB->prepare($m_sqlstr);
    if ( defined($sth) )
    {
        if ( $sth->execute( ) )
        {
            if ( $sth->rows )
            {
                while ( my @row = $sth->fetchrow_array )
                {
                    $found++;
                    &$f_process(\@row);
                }
            }
            $sth->finish();
        }
        else
        {
            Error("$fname:Execute failure: $m_sqlstr", $sth->errstr() );
        }
    }
    else
    {
        Error("$fname:Prepare failure" );
    }

    unless ( $found )
    {
        Verbose("$fname:No data for: $opt_pvid");
    }
    return $found;
}

#-------------------------------------------------------------------------------
# Function        : populateArrayFromSql 
#
# Description     : Issue an SQL query and push the results into an array of hashes
#                   where each row from the query is a hash and the entire result is an 
#                   array 
#
# Inputs          :     name                - For error reporting
#                       pArray              - Ref to the output array
#                       sql                 - Sql to process
#                       pItems              - Array of tems to extract
#                                             Must match the SQL SELECT arguments
#                                             Item names starting with '-' do not end up in the
#                                             generated XML
# Returns         : 
#
sub populateArrayFromSql
{
    my ($fname, $pArray, $m_sqlstr, $pItems) = @_;

    performSqlQueryCallback($fname, 
                            $m_sqlstr, 
                            sub { 
                                my ($pRow) = @_;
                                my %entry;
                                push @{$pArray}, populateHash( \%entry, $pRow, $pItems);
                                }
                            );
#DebugDumpData("populateArrayFromSql", $pArray);
}

#-------------------------------------------------------------------------------
# Function        : GetPkgInfo  
#
# Description     : Get Basic Package Information    
#
# Inputs          : pvid                - pvid of package to fetch
#                   pHash               - Ref to Hash to populate 
#
# Returns         : 
#
sub GetPkgInfo
{
    my ($pvid, $pHash) = @_;
    my $fname = 'GetPkgInfo';

    #
    #   Now extract the package infromation
    #
    my @items = qw(name version modifier modifier_email modified_stamp owner owner_email pkg_label src_path vcstag reason long_desc short_desc pvid psuffix pname deployable -prev_pvid build_type);
    my $m_sqlstr = 
          "SELECT ".
          "     pkg.pkg_name, ".
          "     pv.pkg_version, ".
          "     lm.full_name AS modifier,".
          "     lm.user_email AS modifier_email, ".
          "     pv.modified_stamp, ".
          "     own.full_name AS owner,".
          "     own.user_email AS owner_email, ".
          "     pv.PKG_LABEL,".
          "     pv.SRC_PATH,".
          "     release_manager.PK_RMAPI.return_vcs_tag(pv.PV_ID),".
          "     pv.comments reason, ".
          "     pv.pv_overview long_desc,".
          "     pv.pv_description short_desc,".
          "     pv.pv_id,".
          "     pv.v_ext,".
          "     pkg.pkg_name || pv.v_ext, ".
          "     pv.is_deployable,".
          "     pv.last_pv_id,".
          "     pv.build_type".
          " FROM RELEASE_MANAGER.PACKAGES pkg, RELEASE_MANAGER.package_versions pv, RELEASE_MANAGER.users lm, RELEASE_MANAGER.users own".
          " WHERE pv.pkg_id = pkg.pkg_id".
          " AND pv.modifier_id = lm.user_id(+)".
          " AND pv.owner_id = own.user_id(+)".
          " AND pv.pv_id = " . $pvid;

    performSqlQueryCallback($fname, 
                            $m_sqlstr, 
                            sub { populateHash( $pHash, $_[0], \@items); }
                            );

    #DebugDumpData("$fname", $pHash);
}

#-------------------------------------------------------------------------------
# Function        : GetPkgIssues 
#
# Description     : Get the Issues for the package 
#
# Inputs          :  
#
# Returns         : 
#
sub GetPkgIssues
{
    #
    #   Get associated Jira issues
    #   Insert Dependency issues into the Package Information
    #
    performSqlQueryCallback('GetPkgIssuesJira', 
                             "SELECT iss_key".
                             " FROM".
                             " RELEASE_MANAGER.JIRA_ISSUES ".
                             " WHERE pv_id in ($opt_pvid)".
                             " ORDER BY iss_key ASC",
                               sub 
                               {
                                   my ($pRow) = @_;
                                   UniquePush (\@JiraIssueList, $pRow->[0]);
                                   push @{$pkgData{issues}{jira}}, $pRow->[0];
                               }
                              );

    #
    #   Get associated ClearQuest Issues
    #       Insert Dependency issues into the Package Information
    #       Keep the body of the ClearQuest data for later
    #
    my @items = qw(iss_uid iss_db iss_id iss_state iss_mod_date iss_notes); 
    performSqlQueryCallback('GetPkgIssuesCQ',
                            "SELECT iss_db || '_' || iss_id as iss_uid, iss_db, iss_id, iss_state, mod_date, notes".
                            " FROM".
                            " RELEASE_MANAGER.CQ_ISSUES cq ".
                            " WHERE pv_id in ($opt_pvid)",
                            sub 
                            {
                                my ($pRow) = @_;
                                my $uid = $pRow->[0];
                                push @{$pkgData{issues}{clearquest}}, $uid;

                                my %entry;
                                populateHash(\%entry,$pRow, \@items);
                                $ClearQuestIssueData{$uid} = \%entry;

                            }
                           );
}

#-------------------------------------------------------------------------------
# Function        : GetBuildDependencies 
#
# Description     : Get the build dependencies and associated information
#
# Inputs          : 
#
# Returns         : 
#
sub GetBuildDependencies
{
    my @items = qw(pvid name version dlocked pkg_label src_path vcstag -pkgid modified_stamp reason pname psuffix build_type); 
    my $m_sqlstr = "SELECT ".
                  "        pv.PV_ID,".
                  "        pkg.PKG_NAME, ".
                  "        pv.PKG_VERSION, ".
                  "        pv.DLOCKED,".
                  "        pv.PKG_LABEL,".
                  "        pv.SRC_PATH,".
                  "        release_manager.PK_RMAPI.return_vcs_tag(pv.PV_ID),".
                  "        pv.PKG_ID,".
                  "        pv.MODIFIED_STAMP," .
                  "        pv.comments as reason,".
                  "        pkg.PKG_NAME || pv.v_ext," .
                  "        pv.v_ext," .
                  "        pv.build_type" .
                  " FROM  ".
                  "         RELEASE_MANAGER.PACKAGE_DEPENDENCIES pd, ".
                  "         RELEASE_MANAGER.PACKAGE_VERSIONS pv, ".
                  "         RELEASE_MANAGER.PACKAGES pkg" .
                  " WHERE ".
                  #"         pv.PV_ID = \'$opt_pvid\' " .
                  "         pd.PV_ID(+) = \'$opt_pvid\' " .
                  "         AND pd.DPV_ID = pv.PV_ID" .
                  "         AND pv.PKG_ID = pkg.PKG_ID" .
                  "";

    populateArrayFromSql( 'GetBuildDependencies', \@dependData, $m_sqlstr, \@items );

    #
    #   Get associated Jira issues
    #       Group issues by pvid
    #
    my %issuedByPvid;
    performSqlQueryCallback( 'GetBuildDependenciesIssuesJira', 
                              "SELECT ji.pv_id, ji.iss_key FROM".
                              "   RELEASE_MANAGER.PACKAGE_DEPENDENCIES pd, ".
                              "   RELEASE_MANAGER.PACKAGE_VERSIONS pv, ".
                              "   RELEASE_MANAGER.JIRA_ISSUES ji ".
                              " WHERE ".
                              "   pd.PV_ID(+) = \'$opt_pvid\' " .
                              "   AND pd.DPV_ID = pv.PV_ID" .
                              "   AND pd.DPV_ID = ji.PV_ID" .
                              " ORDER BY ji.iss_key ASC",
                              sub 
                              {
                                my ($pRow) = @_;
                                my $pvid = $pRow->[0];
                                my $issue = $pRow->[1];
                                push @{$issuedByPvid{$pvid}}, $issue;
                                UniquePush (\@JiraIssueList, $issue);
                              }
                              );

    #
    #   Insert jira issues into the dependendency array
    #
    foreach my $entry (@dependData) {
        my $pvid = $entry->{pvid};
        if (exists $issuedByPvid{$pvid})
        {
            push @{$entry->{issues}{jira}}, @{$issuedByPvid{$pvid}}; 
        }
    }

    #
    #   Get associated ClearQuest issues
    #       Group issues by pvid
    #
    my %CQissuedByPvid;
    @items = qw(-pvid iss_uid iss_db iss_id iss_state iss_mod_date iss_notes);
    performSqlQueryCallback( 'GetBuildDependenciesIssuesCQ', 
                             "SELECT cq.pv_id,iss_db || '_' || iss_id as iss_uid, cq.iss_db, cq.iss_id, cq.iss_state,mod_date, notes FROM".
                             "   RELEASE_MANAGER.PACKAGE_DEPENDENCIES pd, ".
                             "   RELEASE_MANAGER.PACKAGE_VERSIONS pv, ".
                             "   RELEASE_MANAGER.CQ_ISSUES cq ".
                             " WHERE ".
                             "   pd.PV_ID(+) = \'$opt_pvid\' " .
                             "   AND pd.DPV_ID = pv.PV_ID" .
                             "   AND pd.DPV_ID = cq.PV_ID",
                             sub 
                             {
                                my ($pRow) = @_;
                                my $pvid = $pRow->[0];
                                my $uid = $pRow->[1];

                                my %data;
                                populateHash(\%data, $pRow, \@items);

                                push @{$CQissuedByPvid{$pvid}}, $uid;
                                $ClearQuestIssueData{$uid} = \%data; 

                             }
                           );
    
    #
    #   Insert clearquest issues into the dependendency array
    #
    foreach my $entry (@dependData) {
        my $pvid = $entry->{pvid};
        if (exists $CQissuedByPvid{$pvid})
        {
            push @{$entry->{issues}{clearquest}}, @{$CQissuedByPvid{$pvid}}; 
        }
    }

#    DebugDumpData("Dependency Issues", \@dependData, \@issues, \%issues);
}

#-------------------------------------------------------------------------------
# Function        : GetRunTimeDependencies 
#
# Description     : Get the runtime dependencies
#
# Inputs          : 
#
# Returns         : 
#
sub GetRunTimeDependencies
{
    my $m_sqlstr = " SELECT pkg.pkg_name, ".
                   "        pv.pkg_version, ".
                   "        pv.v_ext, ".
                   "        pkg.pkg_name || pv.v_ext, ".
                   "        rtd.rtd_comments, ".
                   "        rtd.rtd_url,".
                   "        pv.pv_id".
                   "   FROM RELEASE_MANAGER.PACKAGES pkg, ".
                   "RELEASE_MANAGER.package_versions pv, ".
                   "RELEASE_MANAGER.runtime_dependencies rtd".
                   "  WHERE pv.pkg_id = pkg.pkg_id".
                   "    AND rtd.rtd_id = pv.pv_id".
                   "    AND rtd.pv_id = " . $opt_pvid .
                   " ORDER BY UPPER(pkg.pkg_name)";
    my @items = qw(name version psuffix pname comment url pvid);

    populateArrayFromSql( 'GetRunTimeDependencies', \@dependRunData, $m_sqlstr, \@items );
}

#-------------------------------------------------------------------------------
# Function        : GetUnitTests 
#
# Description     : Get the unit test information
#
# Inputs          : 
#
# Returns         : 
#
sub GetUnitTests
{
    my $m_sqlstr = << "END_SQL";
    SELECT  ttypes.test_type_id, 
        ttypes.test_type_name,
        ut.TEST_ID, 
        ut.PV_ID, 
        ut.TEST_SUMMARY, 
        ut.COMPLETION_DATE, 
        tusr.full_name AS TESTER,
        tusr.user_email AS TESTER_EMAIL, 
        ut.RESULTS_URL, 
        ut.RESULTS_ATTACHMENT_NAME, 
        ut.ACCEPTANCE_DATE, 
        ausr.FULL_NAME AS REVIEWEE,
        ausr.user_email AS REVIEWEE_EMAIL,
        ut.NUMOF_TEST, 
        ut.TEST_ACCEPTED, 
        ut.REVIEW_COMMENTS
    FROM RELEASE_MANAGER.UNIT_TESTS ut,
      (
         SELECT tt.*
            FROM RELEASE_MANAGER.TEST_TYPES tt
            WHERE NOT tt.DISPLAY_ORDER IS NULL     
      ) ttypes,
      RELEASE_MANAGER.USERS tusr,
      RELEASE_MANAGER.USERS ausr
 WHERE
   ut.TEST_TYPES_FK = ttypes.test_type_id  
   AND ut.pv_id (+) = $opt_pvid
   AND ut.COMPLETED_BY = tusr.user_id (+)
   AND ut.ACCEPTED_BY  = ausr.user_id (+)
ORDER BY ttypes.DISPLAY_ORDER  
END_SQL

    my @items = qw(
        -test_type
        name
        -test_id 
        -pv_id 
        test_summary 
        completion_date 
        tester
        tester_email 
        results_url 
        results_attachment_name 
        acceptance_date 
        reviewee
        reviewee_email
        numof_test 
        test_accepted 
        review_comments
        );

    populateArrayFromSql( 'GetUnitTests', \@unitTestData, $m_sqlstr, \@items );
}

#-------------------------------------------------------------------------------
# Function        : GetNotes 
#
# Description     : Get the addiotional Notes
#
# Inputs          : 
#
# Returns         : 
#
sub GetNotes
{
    my $m_sqlstr = << "END_SQL";
    SELECT an.note_id, an.NOTE_TITLE, an.NOTE_BODY, an.mod_date, usr.full_name, usr.user_email,
        an.mod_date ||' by '|| usr.full_name AS lastmod
    FROM RELEASE_MANAGER.ADDITIONAL_NOTES an,
    RELEASE_MANAGER.USERS usr
    WHERE an.pv_id = $opt_pvid
    AND an.mod_user = usr.user_id(+)
    ORDER BY an.note_id
END_SQL

    my @items = qw(
        -note_id
        note_title
        note_body 
        mod_date
        full_name
        user_email
        );

    populateArrayFromSql( 'GetNotes', \@additionalNotesData, $m_sqlstr, \@items );
}

#-------------------------------------------------------------------------------
# Function        : GetBuildDepDiff 
#
# Description     : Get the Differences in Build Deps between this verion and the previous
#
# Inputs          : 
#
# Returns         : 
#
sub GetBuildDepDiff
{
    #
    #   Only if data can be calculated
    return unless $opt_prev_pvid;

    my $m_sqlstr = << "END_SQL";
  SELECT pkg.pkg_name, comp.PV_ID,comp.OLD_PV_ID,comp.PKG_ID,comp.PKG_VERSION,comp.PREVIOUS_VERSION,comp.STATUS,usr.FULL_NAME, usr.user_email
  FROM RELEASE_MANAGER.PACKAGES pkg, 
       RELEASE_MANAGER.USERS usr,
       (   
        SELECT dpv.pv_id AS pv_id, -1 AS old_pv_id, dpv.pkg_id, NULL AS pkg_version, dpv.pkg_version AS previous_version, 'deleted' AS status, dpv.MODIFIER_ID 
          FROM RELEASE_MANAGER.PACKAGE_VERSIONS dpv,
               RELEASE_MANAGER.PACKAGE_DEPENDENCIES dep,
               (
                SELECT olddpv.pv_id AS previous_pv_id, newdpv.pv_id AS current_pv_id 
                  FROM RELEASE_MANAGER.PACKAGE_VERSIONS newdpv,
                       RELEASE_MANAGER.PACKAGE_DEPENDENCIES newdep,
                       RELEASE_MANAGER.PACKAGE_VERSIONS olddpv,
                       RELEASE_MANAGER.PACKAGE_DEPENDENCIES olddep
                 WHERE newdep.DPV_ID = newdpv.PV_ID 
                   AND olddep.DPV_ID = olddpv.PV_ID
                   AND newdep.pv_id = :PV_ID
                   AND olddep.pv_id = :OLD_PV_ID
                   AND newdpv.pkg_id = olddpv.pkg_id 
                   AND NVL( newdpv.v_ext , 'LINK_A_NULL' ) = NVL( olddpv.v_ext, 'LINK_A_NULL' )
               ) diff
         WHERE dep.DPV_ID = dpv.PV_ID
           AND dep.pv_id = :OLD_PV_ID
           AND dpv.pv_id = diff.previous_pv_id (+)  
           AND (diff.current_pv_id IS NULL OR diff.previous_pv_id IS NULL)
        UNION
        SELECT dpv.pv_id AS pv_id, 0 AS old_pv_id, dpv.pkg_id, dpv.pkg_version, NULL AS previous_version, 'added' AS status, dpv.MODIFIER_ID
          FROM RELEASE_MANAGER.PACKAGE_VERSIONS dpv,
               RELEASE_MANAGER.PACKAGE_DEPENDENCIES dep,
               (
                SELECT olddpv.pv_id AS previous_pv_id, 
                       newdpv.pv_id AS current_pv_id 
                  FROM RELEASE_MANAGER.PACKAGE_VERSIONS newdpv,
                       RELEASE_MANAGER.PACKAGE_DEPENDENCIES newdep,
                       RELEASE_MANAGER.PACKAGE_VERSIONS olddpv,
                       RELEASE_MANAGER.PACKAGE_DEPENDENCIES olddep
                 WHERE newdep.DPV_ID = newdpv.PV_ID 
                   AND olddep.DPV_ID = olddpv.PV_ID
                   AND newdep.pv_id = :PV_ID
                   AND olddep.pv_id = :OLD_PV_ID
                   AND newdpv.pkg_id = olddpv.pkg_id 
                   AND NVL( newdpv.v_ext , 'LINK_A_NULL' ) = NVL( olddpv.v_ext, 'LINK_A_NULL' )
               ) diff
         WHERE dep.DPV_ID = dpv.PV_ID
           AND dep.pv_id = :PV_ID
           AND dpv.pv_id = diff.current_pv_id (+)  
           AND (diff.current_pv_id IS NULL OR diff.previous_pv_id IS NULL)   
        UNION
        SELECT newdpv.pv_id AS pv_id,
               olddep.dpv_id AS old_pv_id, 
               newdpv.pkg_id, newdpv.pkg_version,
               DECODE( newdpv.pv_id, olddpv.pv_id, NULL, olddpv.pkg_version ) AS previous_version,
               DECODE( newdpv.pv_id, olddpv.pv_id, 'same', 'modified' ) AS status, newdpv.MODIFIER_ID
          FROM RELEASE_MANAGER.PACKAGE_VERSIONS newdpv,
               RELEASE_MANAGER.PACKAGE_DEPENDENCIES newdep,
               RELEASE_MANAGER.PACKAGE_VERSIONS olddpv,
               RELEASE_MANAGER.PACKAGE_DEPENDENCIES olddep
         WHERE newdep.DPV_ID = newdpv.PV_ID 
           AND olddep.DPV_ID = olddpv.PV_ID
           AND newdep.pv_id = :PV_ID
           AND olddep.pv_id = :OLD_PV_ID
           AND newdpv.pkg_id = olddpv.pkg_id 
           AND NVL( newdpv.v_ext , 'LINK_A_NULL' ) = NVL( olddpv.v_ext, 'LINK_A_NULL' )
        ) comp
 WHERE comp.pkg_id = pkg.pkg_id  
   AND comp.modifier_id = usr.user_id(+)
 ORDER BY UPPER(pkg.pkg_name) 
END_SQL

    my @items = qw(
        name
        pvid 
        previous_pvid
        -pkg_id
        version 
        previous_version
        status 
        modifier
        modifier_email  
        );

    $m_sqlstr =~ s~:PV_ID~$opt_pvid~g;
    $m_sqlstr =~ s~:OLD_PV_ID~$opt_prev_pvid~g;
    populateArrayFromSql( 'GetBuildDepDiff', \@buildDepDiff, $m_sqlstr, \@items );
}

#-------------------------------------------------------------------------------
# Function        : GetIssues 
#
# Description     : Get issues for the current package and its dependencies
#                   A list of issues has already been determined. This function
#                   will simply get the data behind the issue information
#
#                   There are two sources of issues
#                       ClearQuest
#                       Jira - Details held externally
#
# Inputs          : 
#
# Returns         : 
#
sub GetIssues
{
    GetCQIssues();
    GetJiraIssues();
}

#-------------------------------------------------------------------------------
# Function        : GetCQIssues 
#
# Description     : Get the body of the CQ Issues required by the package and
#                   dependent packages
#
#                   Already have this data
#                   Just need to put it in the right form
#
#                   Would be nice to get more data about the ClearQuest issues
#                   but the database is hard to access - and going away.
#
# Inputs          : 
#
# Returns         : 
#
sub GetCQIssues
{

    my $fname = 'GetCQIssues';
    my $m_sqlstr = << "END_SQL";
       SELECT 
             '__enumCLEARQUEST_DEVI_ID__' || '_' || si.dbid as iss_uid,
              __enumCLEARQUEST_DEVI_ID__ AS iss_db,
              si.dbid AS iss_id,
              si.new_num AS iss_num,
              si.headline AS summary,
              sdef.name AS status,
              si.priority AS priority,
              dbms_lob.substr( si.releaseinfo, 4000, 1 )as releaseinfo,
              si.issue_type AS issue_type,
              si.release_part1,
              si.release_part2,
              si.release_part3,
              si.project,
              si.product,
              si.package_ref,
              dbms_lob.substr( si.notes_log, 4000, 1 ),
              dbms_lob.substr( si.description, 4000, 1 ),
              NULL AS release_id,
              NULL AS client_ref,
              NULL AS severity,
              NULL AS cr_internal,
              NULL AS cr_external,
              sp.fullname AS submitter,
              si.submit_date,
              ow.fullname AS owner,
              ap.fullname AS approver
       FROM release_manager.CQ_software_issue si INNER JOIN
            release_manager.CQ_statedef sdef ON si.state = sdef.id INNER JOIN
            release_manager.CQ_users sp ON si.submitter = sp.dbid INNER JOIN
            release_manager.CQ_users ow ON si.owner = ow.dbid INNER JOIN
            release_manager.CQ_users ap ON si.approver = ap.dbid
       WHERE si.dbid IN ( __DEVIiss__ )
       ORDER BY iss_num ASC
END_SQL

        my @items = qw(
            iss_uid
            iss_db
            iss_id
            iss_num
            summary
            status
            priority
            releaseinfo
            issue_type
            release_part1
            release_part2
            release_part3
            project
            product
            package_ref
            notes_log
            description
            release_id
            client_ref
            severity
            cr_internal
            cr_external
            submitter
            submit_date
            owner
            approver
            );

    #
    #   Create a list of issues to extract
    #       Create a part of an 'in ( xxx xxx )' clause
    #       Push a dummy entry (-1) to ensure the list is never empty
    #
    my @inList;
    push @inList, -1;
    foreach my $entry ( keys %ClearQuestIssueData) {
        push @inList, $ClearQuestIssueData{$entry}{iss_id};
        }

    my $inList = join(',',sort @inList);

    #
    #   Insert data into the SQL string
    #
    $m_sqlstr =~ s~__enumCLEARQUEST_DEVI_ID__~4~g;
    $m_sqlstr =~ s~__DEVIiss__~$inList~;

    #
    #   Perform the query
    #       Merge the information from the ClearQuest Database into the hash of data from RM
    #
   performSqlQueryCallback($fname,
                           $m_sqlstr,
                           sub {
                               my $pHash = \%{$ClearQuestIssueData{$_[0][0]}} ;
                               populateHash( $pHash, $_[0], \@items);
                               push @ClearQuestIssues, $pHash;
                               }
                           );

    #DebugDumpData("ClearQuestIssueData", \%ClearQuestIssueData);
    #DebugDumpData("ClearQuestIssues", \@ClearQuestIssues);
}

#-------------------------------------------------------------------------------
# Function        : cleanIssueTest 
#
# Description     : Clean up multi-line issue text
#
# Inputs          : Text to clean 
#
# Returns         : Cleaned text
#
sub cleanIssueTest
{
    my ($data) = @_;
    #
    #   Clean multiline items
    #       Replace escape (033 or 0x1b)
    #       Replace 'Returns'
    #       Replace multiple new lines with  2 <br>
    #       Replace new lines with <br>
    #
    if ($data )
    {
        $data =~ s~\x1b~<ESC>~gi;
        $data =~ s~\n\r~\n~gs;
        $data =~ s~\r\n~\n~gs;
        $data =~ s~\r~\n~gs;
        $data =~ s~\n{2,}~<br><br>~gi;
        $data =~ s~\n~<br>~gs;
        $data =~ s~(<br>){2,}~<br><br>~gi;
    }
  return $data;
}

#-------------------------------------------------------------------------------
# Function        : GetJiraIssues
#
# Description     : Get the Jira issues for the current package
#                   Extract data from the Jira REST API
#
# Inputs          : 
#
# Returns         : 
#
sub GetJiraIssues
{
    if (@JiraIssueList)
    {
        #
        #   Build up a JSON request to be posted to Jira
        #
        my $jsonRequest;
        $jsonRequest->{jql} = "key in (".join(',', @JiraIssueList).")";
        $jsonRequest->{startAt} = 0;
        $jsonRequest->{maxResults} = scalar @JiraIssueList;
        $jsonRequest->{validateQuery} = "false";
        @{$jsonRequest->{fields}} = qw(key summary status priority issuetype resolution 
                                       description created resolutiondate project reporter
                                       components assignee);

        my $json_text = to_json ($jsonRequest );

        #
        #   Issue the Query
        #       Tried to get LWP to do the Basic Authorization - but no joy
        #       Create the header entry my self
        #
        my $rv;
        my $ua = LWP::UserAgent->new( timeout => 30 );
        $rv = $ua->post( $ENV{GBE_JIRA_URL}. "/rest/api/2/search",
                       'Authorization' => 'Basic ' . encode_base64(join(':',$ENV{GBE_JIRA_USERNAME}, $ENV{GBE_JIRA_PASSWORD})),
                       'Content-Type' => 'application/json',
                       'Content' => $json_text,
                       );

        unless ($rv->is_success) {
            #DebugDumpData("Jira Request", $rv);
            Error("Jira Issue Access:", $rv->status_line);
        }
        my $rx_json = from_json($rv->content);

        #
        #   Extract Issue information from the resultant json (now a hash)
        #
        foreach my $entry ( @{$rx_json->{'issues'}}) {
            my %data;

            $data{'priority'} = $entry->{fields}{priority}{name};
            $data{'summary'} = cleanIssueTest($entry->{fields}{summary});
            $data{'description'} = cleanIssueTest($entry->{fields}{description});
            $data{'created'} = $entry->{fields}{created};
            $data{'reporter'} = $entry->{fields}{reporter}{displayName};
            $data{'reporter_email'} = $entry->{fields}{reporter}{emailAddress};
            $data{'status'} = $entry->{fields}{status}{name};
            $data{'resolution'} = $entry->{fields}{resolution}{name};
            $data{'resolutiondate'} = $entry->{fields}{resolutiondate};
            $data{'type'} = $entry->{fields}{issuetype}{name};
            $data{'url'} = $entry->{self};
            $data{'key'} = $entry->{key};
            $data{'project'} = $entry->{fields}{project}{name};
            $data{'category'} = $entry->{fields}{projectCategory}{name};
            $data{'assignee'} = $entry->{fields}{assignee}{displayName};
            $data{'assignee_email'} = $entry->{fields}{assignee}{emailAddress};
            
            foreach my $item ( @{$entry->{fields}{components}} ) {
                push @{$data{'category'}}, $item->{name};
            }
            push @JiraIssues, \%data;
        }
    }
}


#-------------------------------------------------------------------------------
#   Documentation
#

=pod

=for htmltoc    SYSUTIL::

=head1 NAME

jats_get_releasenote_data - Get Release Note Data

=head1 SYNOPSIS

 jats get_releasenote_data [options]

 Options:
    -help              - Brief help message
    -help -help        - Detailed help message
    -man               - Full documentation
    -verbose           - Display additional progress messages
    -pvid=nn           - PVID of package to process
    -outfile=name      - [Optional] Name of the output XML file
    

=head1 OPTIONS

=over 8

=item B<-help>

Print a brief help message and exits.

=item B<-help -help>

Print a detailed help message with an explanation for each option.

=item B<-man>

Prints the manual page and exits.

=item B<-pvid=nn>

This option provides identifies the PackageVersion to be processed.

This option is mandatory.

=item B<-outfile=name>

This option specifies the output file name.

If not provided by the user the output filename will be created in the current directory
and it will be named after the package name and package version.

If the filename does not end in .xml, then .xml will be appended to the file name.

=back

=head1 DESCRIPTION

This utility program is used to extract sufficient information from Release Manager and other
associated databases so that a Release Note can be created.

The extracted data is stored in an XML format. The intent is that XSLT will be used to create
an HTML based release note.


=head1 EXAMPLE

=head2 jats get_releasenote_data -pvid=983058 -outfile=tmpdata.xml

This will locate a package-version with an id of 983058, extrat required information and create
an XML file called tmpdata.xml.

=cut
Subversion Repositories DevTools

(root)/generate_release_notes/trunk/src/tools/jats_get_releasenote_data.pl – Rev 6310
