#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: dms_asn_pods.pl
# 
# This perl script verifies that the input environment variables are OK, then
# it generates two files :
#   - a list of pod files to be reprocessed to generate
#     new DMS associations. 
#   - a list of jitter exposures to be reprocessed to generate
#     new OMS associations.
#
# This tool is run after using dms_asn_insert.pl that
# creates the list of DMS associations in the dms_asn_id table. The tool is 
# designed to get batches of pod files and jitter exps based on a time range of 
# asn_association.last_exp_date for the associations in dms_asn_id. 
#
# The user must enter two dates, each in the format yyyy.ddd. The pod files, 
# for all DMS associations that are occur at or after the first day up to the
# start of the second day, and that are uncollected, will be listed
# in a file in the default directory named:
#                "dms_pods_yyyy_ddd_to_yyyy_ddd.txt".
#
# The jitter exposures will be written to a similar file:
#                "dms_jits_yyyy_ddd_to_yyyy_ddd.txt".
#
# The dates of the dms pods file come from the script arguments. To avoid 
# overlapping lists of pods, all the pod lists should be kept in the same
# directory where the dates can be easily checked. Note that the end day of 
# one list should match the start day of the next list. If a list
# is too long it should be deleted and two new lists generated with half the 
# number of days duration. 
#
# NOTE: These lists must not be edited to delete entries, otherwise processing
# may be held up due to incomplete associations.
#
#
# Interactive Usage:
#      >dms_asn_pods.pl start_day end_day
#
#      For example:
#      >dms_asn_pods.pl 2004.001 2004.200
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 08/18/05 54219    Baum        Initial code 
# 09/28/05 54362    MSwam       add jitter-member list output file
# 08/02/06 56359    MSwam       remove qolink_sms use
# 03/22/07 55484    MSwam       rework podnames query to handle executed_flg
#                               mismatches
#----------------------------------------------------------------------------
# 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:
   dms_asn_pods.pl start_day end_day
   
      where: 
      
      start_day is formatted yyyy.ddd. DMS associations for which POD files
          are listed will have asn_association.last_exp_date values from
          the start of this day.
          
      end_day is formatted yyyy.ddd. DMS associations for which POD files
          are listed will have asn_association.last_exp_date values that
          do not include this day or after.
EOM

#check for arguments
    $num_arg = scalar @ARGV;
    if ($num_arg != 2) {
       print $usage;
       PrintMsg("E","Invalid arguments - try again.");
       exit ($PROCESS_FAILURE);
    } else {
       $start_day = $ARGV[0];
       $end_day   = $ARGV[1];
    }

# get the required external variables

    $DSQUERY=           $ENV{"DSQUERY"};
    $OPUS_DB=           lc($ENV{"OPUS_DB"});

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

    PrintMsg ("I","--- start --- Generate DMS association pod and jitter lists -------");
    PrintMsg ("I","--- start day: $start_day");
    PrintMsg ("I","---   end day: $end_day");
    PrintMsg ("I","---   OPUS_DB: $OPUS_DB");

# validate dates - check for format dddd.ddd where d is digit
    if (!$start_day =~m/^\d{4}\.\d{3}$/) {
        PrintMsg ("E","start day is not dddd.ddd format, d=digit");
        exit ($PROCESS_FAILURE);
    }
    if (!$end_day =~m/^\d{4}\.\d{3}$/) {
        PrintMsg ("E","end day is not dddd.ddd format, d=digit");
        exit ($PROCESS_FAILURE);
    }
#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    use ST_DBlib;
    $db = new ST_DBlib $DSQUERY, $OPUS_DB;
    die if $db == -1;

#---------------------------------------------------------------------
# create list of associations
#---------------------------------------------------------------------

   $query = <<"EOQ";
SELECT d.association_id
INTO #asn_list
FROM dms_asn_id d, asn_association a
WHERE  a.association_id = d.association_id and
       a.last_exp_date >"$start_day" and
       a.last_exp_date <"$end_day" and
       a.collect_date = " "
EOQ

    $num_asn = RunSql( $db, $query );
    PrintMsg ("I","$num_asn uncollected associations within time range.");

    if ($num_asn == 0) {
      $db->dbclose;
      PrintMsg ("I","---  end  --- Generate DMS association pod and jitter lists -------");
      exit( $PROCESS_SUCCESS);  
    }

#---------------------------------------------------------------------
# create list of exposure datasets, using executed_flg to set 9th char
#---------------------------------------------------------------------
    $query = <<"EOQ";
SELECT SUBSTRING(a.association_id,1,6)+l.ob_number+e.executed_flg dataset
INTO #exp_list
FROM #asn_list a, asn_product_link l, executed e
WHERE  
       l.program_id    = SUBSTRING(a.association_id, 2,3) and
       l.asn_obset_id  = SUBSTRING(a.association_id, 5,2) and
       l.member_num    = SUBSTRING(a.association_id, 7,3) and
       e.program_id = l.program_id and
       e.obset_id   = l.obset_id and
       e.ob_number  = l.ob_number
EOQ

    $num_exp = RunSql( $db, $query );
    PrintMsg ("I","$num_exp exposures for associations.");

#---------------------------------------------------------------------
# open POD list output
#---------------------------------------------------------------------
    $file_name = "dms_pods_".$start_day."_to_".$end_day.".txt";
    
    if (!(open OUTPUT, ">$file_name")) {
        PrintMsg("E", "Cannot open for writes: $file_name.");
        exit ($PROCESS_FAILURE);
    }
#---------------------------------------------------------------------
# get the exposure names in a list
#---------------------------------------------------------------------
    $query = <<"EOQ";
SELECT a.dataset
FROM  #exp_list a
EOQ
    
    @dsets = ();
    $err_msg  = "Cannot query #exp_list.";
    $err_msg2 = "Cannot query next #exp_list.";
    $count = 0;
    ($dset) = FirstRecordSql( $db, $query, $err_msg);

    while (defined($dset)) {
        push(@dsets, $dset);
        $count += 1;
        ($dset) = NextRecordSql( $db, $err_msg2);
    }
    PrintMsg("I","Found $count dataset names.");

#---------------------------------------------------------------------
# query pod names
#   first try the full 9chars (w/executed_flg), but if there is no match
#   then try again with a wildcarded 9th char.
#---------------------------------------------------------------------

PrintMsg("I","Writing $file_name.");
$PODcount = 0;
foreach $dset (@dsets) {
    $query = <<"EOQ";
SELECT p.podname 
FROM  podnames p
WHERE p.ipppssoot = "$dset"
EOQ
    
    $err_msg  = "Cannot query podnames.";
    $err_msg2 = "Cannot query next podnames.";
    $count = 0;
    ($podname) = FirstRecordSql( $db, $query, $err_msg);
    
    while (defined($podname)) {
	print OUTPUT "$podname\n";
        $count += 1;
        $PODcount += 1;
        ($podname) = NextRecordSql( $db, $err_msg2);
    }
 
    if ($count == 0) {
       # no match on executed_flg, so try a wild-carded name
       $wildset = substr($dset,0,8).'%';
       $query = <<"EOQ";
SELECT DISTINCT p.podname 
FROM  podnames p
WHERE p.ipppssoot like "$wildset"
EOQ
       $err_msg  = "Cannot query podnames(wildcard).";
       $err_msg2 = "Cannot query next podnames(wildcard).";
       $count = 0;
       ($podname) = FirstRecordSql( $db, $query, $err_msg);
     
       while (defined($podname)) {
	  print OUTPUT "$podname\n";
          $count += 1;
          $PODcount += 1;
          ($podname) = NextRecordSql( $db, $err_msg2);
       }
    }
} #end foreach dsets

PrintMsg("I","Wrote $PODcount pod file names.");
close OUTPUT;
    
#---------------------------------------------------------------------
# open jitter list output
#---------------------------------------------------------------------

    $file_name = "dms_jits_".$start_day."_to_".$end_day.".txt";
    
    if (!(open OUTPUT, ">$file_name")) {
        PrintMsg("E", "Cannot open for writes: $file_name.");
        exit ($PROCESS_FAILURE);
    }
#---------------------------------------------------------------------
# query temp member table (#exp_list)
#---------------------------------------------------------------------

    $query = <<"EOQ";
SELECT dataset
FROM  #exp_list
EOQ
    
    PrintMsg("I","Writing $file_name.");
    $err_msg  = "Cannot query #exp_list.";
    $err_msg2 = "Cannot query next #exp_list.";
    $count = 0;
    ($member) = FirstRecordSql( $db, $query, $err_msg);
    
    while (defined($member)) {
	print OUTPUT substr($member,0,8)."J\n";
        $count += 1;
        ($member) = NextRecordSql( $db, $err_msg2);
    }
    PrintMsg("I","Wrote $count jitter exposure names.");
    close OUTPUT;
    
#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    $db->dbclose;
    PrintMsg ("I","---  end  --- Generate DMS association pod and jitter lists -------");
    exit( $PROCESS_SUCCESS);