#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: update_notify_table
#
# This perl script updates the ARCH_DB:hst_notification_table, inserting new
# records and removing replanned records.
#
# During processing the following temporary tables are created and dropped 
# at the end of processing each temp table.
#
#    #new_sms_progs
#    #old_sms_progs
#
#
# Restrictions:
#   The UPDATR must have been run for the MSC file so that
#   the qolink_sms table has been updated. 
#
#
# Interactive Usage:
#	>update_notify_table.pl  [<pod_name> | sms <sms_id>]
#
#	Note that the following environment variables (logicals) are 
#       always required: OPUS_SERVER, SPSS_DB, ARCH_DB and OPUS_DB.
#       These queries require multiple _DBs on SAME server: 
#       keeping DSQUERY as a variable to emphasize this point.
#
#       If no arguments are present, the ENV variable OSF_DATASET  and
#       MSC_DIR must be defined.
#
#       If (sms <sms_id>) is not present, then the ENV variable MSC_DIR is used to
#       locate the pod file to be read for the sms_id.
#
#       If (sms sms_id) is present, the pod file is not read. This sms_id arg
#       is used for a shortcut during testing when the POD file may not be 
#       available.
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 12/03/04 52482    Baum        Initial code
# 03/09/10 64199    MSwam       fix hst_notification_track cleanup queries
# 06/30/10 64432    MSwam       Use single quotes for SQLServer
# 05/11/11 68256    Gardner     Populate new field hnt_skip
# 09/18/12 72255    Sherbert    keep DSQUERY in code to emphasize the sharing 
#----------------------------------------------------------------------------
# set up external routines
unshift @INC,(split /:/, $ENV{PATH});
require 'printmsg.pl';       # prints a formatted status message
require 'parse_sms.pl';      # parse SMS id from msc file header
require 'do_dbi_pkg.pl';     # DoDBI package - convenience routines
#----------------------------------------------------------------------------
    use strict;    # require explicit context for all variables (using "my")

#   Specify exit status values and other constants

    my $PROCESS_FAILURE = 7; # exit status for XPOLL
    my $PROCESS_SUCCESS = 9; # exit status for XPOLL

#   Check for arguments

    my $num_arg = scalar @ARGV;
    my $PIPELINE;
    my $SMS_ID;
    my $MSC_DIR;

    if ($num_arg > 0 && substr($ARGV[0],0,1) ne "-") {
      $PIPELINE = 0;
      if ($num_arg>1) {  
        $SMS_ID = uc($ARGV[1]);   # uppercase it
      }
    } else {
      $PIPELINE = 1;
    }
    if (!defined($SMS_ID)) {
        $MSC_DIR = $ENV{"MSC_DIR"};
        if (!defined( $MSC_DIR)) {
             PrintMsg ("E","Missing MSC_DIR in environment");
             exit( $PROCESS_FAILURE);
        }
    }
    my $POD_FILE;
    
    if ($PIPELINE) {
        $POD_FILE = $ENV{"OSF_DATASET"};
    } else {
        $POD_FILE = $ARGV[0];
    }
    if ( !defined($POD_FILE)) {
       PrintMsg ("E","Missing POD name in args or environment");
       exit( $PROCESS_FAILURE);
    } 

#   Get the required external variables

    my $DSQUERY=           $ENV{"OPUS_SERVER"};
    my $OPUS_DB=           lc($ENV{"OPUS_DB"});
    my $SPSS_DB=           lc($ENV{"SPSS_DB"});
    my $ARCH_DB=           lc($ENV{"ARCH_DB"});
    
    if (!$PIPELINE) {
       PrintMsg("I","OPUS_DB is $OPUS_DB");
       PrintMsg("I","SPSS_DB is $SPSS_DB");
       PrintMsg("I","ARCH_DB is $ARCH_DB");
    }
#   Begin processing
    PrintMsg ("I","--- start --- Update Notify Table ----------");
    
    if ($POD_FILE ne "sms") {
       PrintMsg ("I","--- POD: $POD_FILE");
    }
#   Extract $SMS_ID from POD file if it is not yet defined

    if (!defined($SMS_ID)) {
       my $msc_file = $MSC_DIR.$POD_FILE.'.pod';

       if ( !(($SMS_ID) = Parse_SMS( $msc_file))) {
          exit( $PROCESS_FAILURE);  
       }
    }
    PrintMsg ("I","--- SMS: $SMS_ID");
#   Open server connection and OPUS database for queries, and set err exit code
    my $db = DoDBIopen( $DSQUERY, $OPUS_DB, $PROCESS_FAILURE);
#   More queries are in these subroutines that that do the work.
#   The $db database handle is visible to all the routines in this file 
    create_new_sms_progs();     # creates new_sms_progs temp table
    report_new_sms_progs();     # reports contents for temp table
    add_to_hnt_table();         # uses temp table to add HNT records
    remove_from_hnt_table();    # uses temp table to delete HNT records
    cleanup_hnt_table();        # removes replanned-away entries from HNT
    DoDBI($db,"drop table #new_sms_progs");
    create_old_sms_progs();     # creates old_sms_progs temp table
    report_old_sms_ids();       # reports sms_ids for missing props
    remove_missing_props();     # use temp table to delete HNT records
    DoDBI($db,"drop table #old_sms_progs");    
    DoDBIclose($db);
    PrintMsg ("I","---  end  --- Update Notify Table ----------");
    exit( $PROCESS_SUCCESS);  

#---------------------------------------------------------------------
# end of main procedure -- subroutines follow
#---------------------------------------------------------------------
sub create_new_sms_progs {   # no args    
#
# Create temp table #new_sms_progs from qolink_sms and spss_db.qpdescrip.
# Don't select engineering proposals or STIS/CAL proposals. Don't
# select non-archived (status = M) or missing exposures (status=N). 
#
# PR 64199- remove qolink from query and just use qpdescrip, as it is not
#           necessary and its use ends up excluding STIS programs that 
#           contain only ASNs (since STIS products don't show up in qolink)
#
my $query = <<"EOQ";
SELECT DISTINCT q.program_id, CONVERT(int, p.proposal_id) pep_id
INTO #new_sms_progs
FROM  qolink_sms q, $SPSS_DB..qpdescrip p
WHERE q.sms_id = '$SMS_ID'
  and q.status != 'M' 
  and q.status != 'N'
  and p.program_id = q.program_id
  and p.proposal_typ NOT like 'ENG%'
  and p.proposal_typ != 'CAL/STIS'
EOQ
    my $count = DoDBI($db, $query);
    
    if ($count == 0) {
        DoDBIerr($db, $query,"No records found for SMS $SMS_ID"); 
    } else {
        PrintMsg ("I",
            "$count (program_id,pep_id) pairs put into #new_sms_progs.");
    }
#   create index on temporary table

$query = <<"EOQ";
CREATE UNIQUE CLUSTERED INDEX  #new_sms_pgs_1 
ON #new_sms_progs (program_id)
EOQ
    DoDBI($db, $query);
}
#---------------------------------------------------------------------
sub report_new_sms_progs {   # no args    
#
#  report on new program_id, pep_id pairs in #new_sms_progs

my $query = <<"EOQ";
SELECT program_id, pep_id
FROM #new_sms_progs
EOQ
    my $sth = DoDBIexecute( $db, $query);
    
    while ( my ($prog_id, $pep_id) = DoDBIfetch($db, $query, $sth)) {
         PrintMsg("I","Notification row: $prog_id $pep_id $SMS_ID");
    }
}
#---------------------------------------------------------------------
sub add_to_hnt_table {   # no args    
#
#  add new records to hst_notification_track table

my $query = <<"EOQ";
INSERT INTO $ARCH_DB..hst_notification_track
(hnt_program_id, hnt_pep_id, hnt_sms_id, hnt_archive_start,
 hnt_data_available, hnt_skip)
SELECT program_id, pep_id, '$SMS_ID', NULL, NULL, 'N'
FROM #new_sms_progs p
WHERE not exists (SELECT * FROM $ARCH_DB..hst_notification_track
                  WHERE hnt_program_id = p.program_id and
                        hnt_pep_id = p.pep_id and
                        hnt_sms_id = '$SMS_ID')
EOQ
    my $count = DoDBI($db,$query);
    
    if ($count == 0) {
        PrintMsg ("I","No new records put into hst_notification_track.");
    } else {
        PrintMsg ("I","$count new records put into hst_notification_track.");
    }
}
#---------------------------------------------------------------------
sub remove_from_hnt_table {   # no args    
#
#  Remove missing records from hst_notification_track table. The program_ids
#  are missing from the current MSC data so the notification table must
#  be cleaned up to reflect the exact contents of #new_sms_progs. 

my $query = <<"EOQ";
DELETE $ARCH_DB..hst_notification_track
FROM $ARCH_DB..hst_notification_track n
WHERE hnt_sms_id = '$SMS_ID' and hnt_archive_start is NULL and
      hnt_data_available is NULL and not exists 
        (SELECT * FROM #new_sms_progs
         WHERE program_id = n.hnt_program_id)
EOQ
    my $count = DoDBI($db,$query);
    
    if ($count == 0) {
        PrintMsg ("I",
           "No old records deleted from hst_notification_track.");
    } else {
        PrintMsg ("I",
           "$count old records deleted from hst_notification_track.");
    }
}
#---------------------------------------------------------------------
sub cleanup_hnt_table {   # no args    
#
#  PR 64199- An extra cleanup query to remove any HNT rows that were
#            left around by a replan.
#
#  Remove records from hst_notification_track table that have NULL dates
#  and no rows in qolink_sms table (the missing qolink_sms rows indicate
#  that the records have been "replanned away").

my $query = <<"EOQ";
DELETE $ARCH_DB..hst_notification_track
FROM $ARCH_DB..hst_notification_track n
WHERE hnt_archive_start is NULL and hnt_data_available is NULL and 
hnt_sms_id not in (SELECT distinct sms_id FROM $OPUS_DB..qolink_sms)
EOQ
    my $count = DoDBI($db,$query);
    
    if ($count == 0) {
        PrintMsg ("I",
           "No replanned-away records deleted from hst_notification_track.");
    } else {
        PrintMsg ("I",
           "$count replanned-away records deleted from hst_notification_track.");
    }
}
#---------------------------------------------------------------------
sub create_old_sms_progs {   # no args 
#
# Create temp table #old_sms_progs from qolink_sms
# where old data comes from any prior SMS with eight days of the
# the SMS_ID we are processing now. Ignore exposures that are not archived
# (status = M) and that are lost (status = N). Because of regression tests
# SMS_ID may be designated in the 90's. So for thoroughness we handle the
# year 2000 case but allow the extra leap year day to intervene.

# find lower limit for sms_id 
    my $low_lim_yr = 0 + substr($SMS_ID, 0, 2);  # 2 digit year
    my $low_lim_dy = substr($SMS_ID, 2, 3) - 7;  # 3 digit day of year

    if ($low_lim_dy < 1) {
        # sms date range overlaps year end
        $low_lim_yr -= 1;
        
        if ($low_lim_yr < 0) {
            $low_lim_yr = 99;
        } 
        $low_lim_dy += 365;  # do not worry about leap year here
    } 
    my $low_lim_yrday = sprintf("%02d%03d", $low_lim_yr, $low_lim_dy);
  
my $query = <<"EOQ";
SELECT DISTINCT sms_id, program_id
INTO #old_sms_progs
FROM  qolink_sms
WHERE sms_id > '$low_lim_yrday' and sms_id < '$SMS_ID' and
 status != 'N' and status !='M'
EOQ
    my $count = DoDBI($db,$query);
    if ($count > 0) {
        PrintMsg ("I",
            "$count (sms_id, program_id) pairs put into #old_sms_progs.");
    
#       create index on temporary table

$query = <<"EOQ";
CREATE UNIQUE CLUSTERED INDEX  #old_sms_pgs_1 
ON #old_sms_progs (sms_id, program_id)
EOQ

        DoDBI($db, $query);
    } else {
        PrintMsg ("I",
            "No (sms_id, program_id) pairs put into #old_sms_progs.");
    }
}
#---------------------------------------------------------------------
sub report_old_sms_ids {   # no args    
#
#  report on sms_id in #old_sms_progs

my $query = <<"EOQ";
SELECT distinct sms_id FROM #old_sms_progs
EOQ
    my $sth = DoDBIexecute($db,$query);
    PrintMsg("I","Old sms_ids will be checked for missing program_ids.");
      
    while ( my ($sms_id) = DoDBIfetch($db, $query, $sth) ){
         PrintMsg("I","Old sms_id: $sms_id");
    }
}
#---------------------------------------------------------------------
sub remove_missing_props {   # no args    
#
# Remove old missing records from hst_notification_track table for earlier
# sms_ids.  The hnt_program_ids may be missing from the qolink_sms table for
# older SMSs that have been replanned, so the notification table must be 
# cleaned up to reflect the contents of #old_sms_progs table for every 
# matching hnt_sms_id. 

my $query = <<"EOQ";
DELETE $ARCH_DB..hst_notification_track
FROM $ARCH_DB..hst_notification_track n
WHERE hnt_sms_id in (SELECT distinct sms_id FROM #old_sms_progs)
      and not exists 
        (SELECT * FROM #old_sms_progs 
         WHERE sms_id = n.hnt_sms_id and program_id = n.hnt_program_id)      
      and hnt_archive_start is NULL 
      and hnt_data_available is NULL 
EOQ
    my $count = DoDBI($db,$query);
    
    if ($count == 0) {
        PrintMsg ("I",
         "No old missing programs_ids deleted from hst_notification_track.");
    } else {
        PrintMsg ("I",
         "$count old missing program_ids deleted from hst_notification_track.");
    }
}