#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: stis_gowave_asns
# 
# Purpose: Adds GO WAVECAL singletons to existing STIS associations.
#
# This perl script verifies that the input environment variables are OK, then
# it generates a set of queries to insert special STIS association records into 
# the following tables --
#
#                asn_members
#                asn_association
#                asn_product_link
#                stis_go_wavecal   -- new permanent table
#
# All these new records are generated using old STIS database info
# for unassociated datasets that fulfill the new specific rules for
# associations.
#
# Interactive Usage:
#	stis_gowave_asns.pl file_of_gowave_asns
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 04/04/06 55219    MSwam       from Baum's "dms_asn_insert.pl" tool
# 08/02/06 56359    MSwam       leave qolink_sms alone
#----------------------------------------------------------------------------
# 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:
   stis_gowave_asns.pl file_of_gowave_asns
   
      where: 
      
      file_of_gowave_asns - required: this ASCII file must contain
          up to three columns of information:
          column1= name of an existing STIS assocation that will be added to
          column2= name of a GO wavecal exposure that will get added to the ASN
          column3= (optional) name of a second GO wavecal exposure that will
                              also get added to the ASN
      
EOM

#check for arguments
    $num_arg = scalar @ARGV;
    if ($num_arg < 1 || $num_arg > 2) {
       print $usage;
       PrintMsg("E","Invalid arguments - try again.");
       exit ($PROCESS_FAILURE);
    } elsif ($num_arg ==1) {
       $INFILE = $ARGV[0];
    }

# get the required external variables

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

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

    PrintMsg ("I","--- start --- Insert STIS GO Wavecal records ----------");
    PrintMsg ("I","--- input file: $INFILE");
    PrintMsg ("I","--- OPUS_DB: $OPUS_SERVER,$OPUS_DB");
    PrintMsg ("I","--- SPSS_DB: $OPUS_SERVER,$SPSS_DB");
    use ST_DBlib;
#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    $db = new ST_DBlib $OPUS_SERVER, $OPUS_DB;
    die if $db == -1;

#---------------------------------------------------------------------
# read GO_wavecal ASN info from file into lists
#---------------------------------------------------------------------
   if (!open(INFILE, "$INFILE")) {
      PrintMsg("E","ERROR: failed to open INFILE $INFILE\n");
      die;
   }

   @asn_list = ();
   @exp_list = ();
   while (<INFILE>) {
      #
      # split the read buffer ($_) into fields
      ($asnname, $gowave1, $gowave2) = split(/\s+/,$_);
      chomp($gowave2);
      chomp($gowave1);

#---------------------------------------------------------------------
# verify that the specified ASN exists in asn_members
#---------------------------------------------------------------------

      $query = <<"EOQ";
SELECT count(*) FROM asn_members a
WHERE a.association_id = "$asnname"
EOQ
      $asn_count = SelectSql( $db, $query, "Failed select from asn_members" );
      if (!$asn_count) {
        PrintMsg("E","Association $asnname not found in asn_members.");
        die;
      }
      PrintMsg("D","found $asn_count rows for $asnname");

      # save the asn and wavecal names in separate lists
      push @asn_list, $asnname;
      push @exp_list, $gowave1;

      # if a second member is provided, save it as well
      if ($gowave2 ne "") {
        push @asn_list, $asnname;
        push @exp_list, $gowave2;
      }
   }  # end while <INFILE>
   $load_count = scalar @asn_list;
   PrintMsg("I","Loaded $load_count new members from $INFILE");

#---------------------------------------------------------------------
# add new ASN member entries
#---------------------------------------------------------------------
    while (@asn_list) {
       $asnname = pop @asn_list;
       $memname = pop @exp_list;

       # split into ppp, ss, oo
       $program_id = substr($memname,1,3);
       $obset_id   = substr($memname,4,2);
       $obs_num    = substr($memname,6,2);
       $asn_num    = substr($asnname,6,3);

       # insert new member in asn_members
       #
       $query = << "EOQ";
INSERT asn_members (association_id, program_id, obset_id, member_num,
                    member_type, member_status, product_status) VALUES
                   ("$asnname","$program_id","$obset_id","$obs_num",
                    "AUTO-WAVECAL","U","E")
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into asn_members");

       # insert new member in asn_product_link
       #
       $query = << "EOQ";
INSERT asn_product_link 
  (program_id, asn_obset_id, member_num, obset_id, ob_number) VALUES
  ("$program_id","$obset_id","$asn_num", "$obset_id","$obs_num")
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into asn_product_link");

       # insert into stis_go_wavecal so these new members can be
       #   identified in asn_members by a join
       #
       $query = << "EOQ";
INSERT stis_go_wavecal
  (program_id, obset_id, member_num) VALUES
  ("$program_id","$obset_id","$obs_num")
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into stis_go_wavecal");

       # mark all collected members and products for this ASN as 
       # uncollected in asn_members
       #
       $query = << "EOQ";
UPDATE asn_members set member_status = "U" 
WHERE association_id = "$asnname" and member_status = "C" and 
product_status = "E"
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count member records marked UNCOLLECTED in asn_members");

       $query = << "EOQ";
UPDATE asn_members set product_status = "U" 
WHERE association_id = "$asnname" and product_status = "C" and 
member_status = "P"
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count product records marked UNCOLLECTED in asn_members");

       # get the exp_date for the new member (qobservation.pred_strt_tm)
       # and if > asn_association.last_exp_date, update last_exp_date
       #
       $query = << "EOQ";
UPDATE asn_association set a.last_exp_date = o.pred_strt_tm
FROM asn_association a, $SPSS_DB..qobservation o
WHERE 
      o.program_id = "$program_id" and
      o.obset_id = "$obset_id" and
      o.ob_number = "$obs_num" and
      a.association_id = "$asnname" and
      a.last_exp_date < o.pred_strt_tm
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count record updated in asn_association for last_exp_date");

       # blank the collect date for the ASN
       #
       $query = << "EOQ";
UPDATE asn_association set collect_date = " "
WHERE association_id = "$asnname"
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count record updated in asn_association to blank collect_date");

    }

#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    $db->dbclose;
    PrintMsg ("I","---  end  --- Insert STIS GO Wavecal records ----------");
    exit( $PROCESS_SUCCESS);