#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: pdq_summary_updates
# 
# Purpose: Makes new pdq_summary entries for DMS associations.
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 09/15/06 56418    MSwam       first version
# 10/23/06 56418    MSwam       add check for existing pdq_summary entry
# 03/21/07 57374    MSwam       load NULLs in DB for empty qual values
# 08/16/07 58377    MSwam       load real NULLs, not text NULLs
# 12/13/07 58336    MSwam       copy along pdq_severity_code
#----------------------------------------------------------------------------
# set up external routines
unshift @INC,(split /:/, $ENV{PATH});
require 'printmsg.pl';       # prints a formatted status message
require 'runsql.pl';         # run query returning only record count
require 'sql_select_pkg.pl'; # run queries that return records

#specify exit status values

    $PROCESS_FAILURE = 1; 
    $PROCESS_SUCCESS = 0; 

    $usage = <<"EOM";
Usage:
   pdq_summary_updates.pl
   
EOM

# get the required external variables

    $OPUS_SERVER=       $ENV{"OPUS_SERVER"};
    $OPUS_DB=           lc($ENV{"OPUS_DB"});
    $ARCH_SERVER=       $ENV{"ARCH_SERVER"};
    $ARCH_DB=           lc($ENV{"ARCH_DB"});

# validate required ENV
    if (!defined($OPUS_SERVER) || !defined($OPUS_DB) || 
        !defined($ARCH_SERVER) || !defined($ARCH_DB) ) {
        PrintMsg("E",
           "OPUS_SERVER, OPUS_DB, ARCH_SERVER and ARCH_DB must be defined.");
        exit ($PROCESS_FAILURE);
    }
# begin processing

    PrintMsg ("I","--- start --- pdq_summary updates ----------");
    PrintMsg ("I","--- OPUS_DB: $OPUS_SERVER,$OPUS_DB");
    PrintMsg ("I","--- ARCH_DB: $OPUS_SERVER,$ARCH_DB");
    use ST_DBlib;
#---------------------------------------------------------------------
# open databases for queries (two OPUS connections open at once)
#---------------------------------------------------------------------
    $db = new ST_DBlib $OPUS_SERVER, $OPUS_DB;
    die if $db == -1;

    $db2 = new ST_DBlib $OPUS_SERVER, $OPUS_DB;
    die if $db2 == -1;

    $db_arch = new ST_DBlib $ARCH_SERVER, $OPUS_DB;
    die if $db_arch == -1;

#---------------------------------------------------------------------
# query asn names from dms_asn_id
#---------------------------------------------------------------------

    $query = <<"EOQ";
SELECT association_id
FROM  dms_asn_id
EOQ
    
    @asn_list = ();
    $err_msg  = "Cannot query dms_asn_id.";
    $err_msg2 = "Cannot query next dms_asn_id.";
    ($asnname) = FirstRecordSql( $db, $query, $err_msg);
    
    while (defined($asnname)) {
        push @asn_list, $asnname;
        ($asnname) = NextRecordSql( $db, $err_msg2);
    }
    $asn_count = scalar @asn_list;
    PrintMsg("I","Found $asn_count dms_asn_id entries.");
    
#---------------------------------------------------------------------
    while (@asn_list) {
       $asnname = pop @asn_list;

       # if an entry already exists in pdq_summary for the new ASN, skip it
       $asn_ppp = substr($asnname,1,3);
       $asn_ss = substr($asnname,4,2);
       $asn_ooo = substr($asnname,6,3);
       $query_arch = <<"EOQ";
SELECT count(*) pdq_count
FROM  $ARCH_DB..pdq_summary
WHERE pdq_program_id = "$asn_ppp" and
      pdq_obset_id = "$asn_ss" and
      pdq_obsnum = "$asn_ooo"
EOQ
       $err_msg_arch  = "Cannot query pdq_summary for select check.";
       $pdq_count = SelectSql( $db_arch, $query_arch, $err_msg_arch);
       # PrintMsg("I","Found $pdq_count pdq_summary entries for $asnname.");
       if ($pdq_count > 0) {
          next;
       }

       # clear potential quality vars
       $sv_qualcom1 = "";
       $sv_qualcom2 = "";
       $sv_qualcom3 = "";
       $sv_quality  = "";
       $sv_length = 0;
       $sv_severity_code = -99;

       #get the asn member info
       $query = <<"EOQ";
SELECT program_id, obset_id, member_num
FROM  asn_members
WHERE association_id = "$asnname"
EOQ
    
       $err_msg  = "Cannot query asn_members.";
       $err_msg2 = "Cannot query next dms_asn_id.";
       ($program_id, $obset_id, $member_num) = FirstRecordSql( $db, $query, $err_msg);
       while (defined($program_id)) {
          #
          # see if a pdq_summary record exists for the member and if so
          # save it in the quality vars if the new values are longer than
          # the old (or print them out if they are smaller)
          $query_arch = <<"EOQ";
SELECT pdq_comment_1, pdq_comment_2, pdq_comment_3, 
       pdq_quality, pdq_severity_code
FROM  $ARCH_DB..pdq_summary
WHERE pdq_program_id = "$program_id" and
      pdq_obset_id = "$obset_id" and
      pdq_obsnum = "$member_num"
EOQ
          $err_msg_arch  = "Cannot query pdq_summary.";
          ($pdq_qualcom1, $pdq_qualcom2, $pdq_qualcom3, $pdq_quality, $pdq_severity_code) = FirstRecordSql( $db_arch, $query_arch, $err_msg_arch);

          if (defined($pdq_quality)) {
             PrintMsg("I","$program_id$obset_id$member_num: $pdq_quality");
             $new_length = length($pdq_quality);
             if (defined($pdq_qualcom1)) {
                PrintMsg("I","$program_id$obset_id$member_num: $pdq_qualcom1");
                $new_length += length($pdq_qualcom1);
             }
             if (defined($pdq_qualcom2)) {
                PrintMsg("I","$program_id$obset_id$member_num: $pdq_qualcom2");
                $new_length += length($pdq_qualcom2);
             }
             if (defined($pdq_qualcom3)) {
                PrintMsg("I","$program_id$obset_id$member_num: $pdq_qualcom3");
                $new_length += length($pdq_qualcom3);
             }
             if ($new_length > $sv_length) {
                $sv_qualcom1 = $pdq_qualcom1;
                $sv_qualcom2 = $pdq_qualcom2;
                $sv_qualcom3 = $pdq_qualcom3;
                $sv_quality = $pdq_quality;
                $sv_length = $new_length;
                $sv_severity_code = $pdq_severity_code;
             }
          }
          ($program_id, $obset_id, $member_num) = NextRecordSql( $db, $err_msg2);
       } # end of asn member queries

       # see if the new asn replaced an old asn, and if so check for a pdq entry
       $query = <<"EOQ";
SELECT old_association_id
FROM  stis_old_asns
WHERE new_association_id = "$asnname"
EOQ
       $err_msg = "Cannot query stis_old_asns.";
       ($old_asnname) = FirstRecordSql( $db2, $query, $err_msg);

       if (defined($old_asnname)) {
          $query_arch = <<"EOQ";
SELECT pdq_comment_1, pdq_comment_2, pdq_comment_3, 
       pdq_quality, pdq_severity_code
FROM  $ARCH_DB..pdq_summary
WHERE pdq_asn_id = "$old_asnname"
EOQ
          $err_msg_arch  = "Cannot query pdq_summary.";
          ($pdq_qualcom1, $pdq_qualcom2, $pdq_qualcom3, $pdq_quality, $pdq_severity_code) = FirstRecordSql( $db_arch, $query_arch, $err_msg_arch);

          if (defined($pdq_quality)) {
             PrintMsg("I","$old_asnname: $pdq_quality");
             $new_length = length($pdq_quality);
             if (defined($pdq_qualcom1)) {
                PrintMsg("I","         : $pdq_qualcom1");
                $new_length += length($pdq_qualcom1);
             }
             if (defined($pdq_qualcom2)) {
                PrintMsg("I","         : $pdq_qualcom2");
                $new_length += length($pdq_qualcom2);
             }
             if (defined($pdq_qualcom3)) {
                PrintMsg("I","         : $pdq_qualcom3");
                $new_length += length($pdq_qualcom3);
             }
             if ($new_length > $sv_length) {
                $sv_qualcom1 = $pdq_qualcom1;
                $sv_qualcom2 = $pdq_qualcom2;
                $sv_qualcom3 = $pdq_qualcom3;
                $sv_quality = $pdq_quality;
                $sv_length = $new_length;
                $sv_severity_code = $pdq_severity_code;
             }
          }
       } 

       if ($sv_length > 0) {
         PrintMsg("I","$asnname: $sv_qualcom1");
         if (defined($sv_qualcom2)) {
           PrintMsg("I","         : $sv_qualcom2");
         }
         else { $sv_qualcom2 = "NULL";}
         if (defined($sv_qualcom3)) {
           PrintMsg("I","         : $sv_qualcom3");
         }
         else { $sv_qualcom3 = "NULL";}
         if (defined($sv_quality)) {
           PrintMsg("I","         : $sv_quality");
         }
         else { $sv_quality = "NULL";}

         PrintMsg("I"," ");
         #
         # perform INSERT into pdq_summary for the new association
         $query_arch = << "EOQ";
INSERT $ARCH_DB..pdq_summary (pdq_data_set_name, pdq_program_id, pdq_obset_id, pdq_obsnum,
                    pdq_asn_id, pdq_comment_1, pdq_comment_2, pdq_comment_3,
                    pdq_quality, pdq_severity_code) VALUES
                    ("$asnname","$asn_ppp","$asn_ss","$asn_ooo",
                     "$asnname"
EOQ
         if (length($sv_qualcom1) == 0 || $sv_qualcom1 eq "NULL") {
                $query_arch = $query_arch . ",NULL";
         }
         else { $query_arch = $query_arch . ',"' . $sv_qualcom1 . '"'};
         if (length($sv_qualcom2) == 0 || $sv_qualcom2 eq "NULL") {
                $query_arch = $query_arch . ",NULL";
         }
         else { $query_arch = $query_arch . ',"' . $sv_qualcom2 . '"'};
         if (length($sv_qualcom3) == 0 || $sv_qualcom3 eq "NULL") {
                $query_arch = $query_arch . ",NULL";
         }
         else { $query_arch = $query_arch . ',"' . $sv_qualcom3 . '"'};
         if (length($sv_quality) == 0 || $sv_quality eq "NULL") {
                $query_arch = $query_arch . ",NULL";
         }
         else { $query_arch = $query_arch . ',"' . $sv_quality. '"'};
         if ($sv_severity_code == -99) {
                $query_arch = $query_arch .",NULL";
         }
         else { $query_arch = $query_arch .",".$sv_severity_code; }
         $query_arch = $query_arch . ")\n";

         PrintMsg("I","query=$query_arch");
         $count = RunSql( $db_arch, $query_arch );
         PrintMsg("I","$count records inserted into pdq_summary ");
       }
    } # end of asn_list 

#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    $db->dbclose;
    $db_arch->dbclose;
    PrintMsg ("I","---  end  --- pdq_summary updates -------");
    exit( $PROCESS_SUCCESS);