#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: stis_gowave_singl
#
# Purpose: Creates new STIS associations from a list of existing
#          singletons.
# 
# 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
#                dms_asn_id        -- 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.
#
# The entries in the input file create one new STIS association per
# input line.  Each new association will have either 2 or 3 members,
# depending on the number of exposures on the input line.
#
# Interactive Usage:
#	stis_gowave_singl.pl file_of_gowave_singl
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 04/04/06 55219    MSwam       from Baum's "dms_asn_insert.pl" tool
# 07/24/06 56015    MSwam       fill creation_date in dms_asn_id
# 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_singl.pl file_of_gowave_singl
   
      where: 
      
      file_of_gowave_singl - required: this ASCII file can contain
          up to three columns of information:
          column1= name of an existing STIS singleton SCIENCE exposure that
                   will become part of a new ASN
          column2= name of a GO wavecal exposure that will get associated with
                   the SCIENCE exposure in column1 in the new ASN
          column3= (optional) name of a second GO wavecal exposure 
                   that will get associated with
                   the column1 and column2 exposures in the new 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 --- Make New STIS ASNs ----------");
    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;
   }

   @sci_list = ();
   @gowave_list = ();
   while (<INFILE>) {
      #
      # split the read buffer ($_) into fields
      ($sciname, $gowave, $gowave2) = split(/\s+/,$_);
      chomp($gowave);
      chomp($gowave2);

      # save the names in separate lists
      push @sci_list, $sciname;
      push @gowave_list, $gowave;
      push @gowave2_list, $gowave2;  # could be an empty entry

   }  # end while <INFILE>

   $load_count = scalar @sci_list;
   PrintMsg("I","Loaded $load_count rows from $INFILE");

#---------------------------------------------------------------------
# build new ASNs
#---------------------------------------------------------------------
    $last_ppp = " ";
    $last_ss = " ";
    while (@sci_list) {
       $sciname = pop @sci_list;
       $gowave  = pop @gowave_list;
       $gowave2 = pop @gowave2_list;  # might be empty

       # split into ppp, ss, oo
       $program_id = substr($sciname,1,3);
       $obset_id   = substr($sciname,4,2);
       $sci_obs_num = substr($sciname,6,2);
       $gowave_obs_num = substr($gowave,6,2);

       # compute the new ASN number from the last known one for prog/obset
       #   or just keep counting up if the "pppss" hasn't changed
       #
       if ($program_id ne $last_ppp or $obset_id ne $last_ss) {
          $query = << "EOQ";
SELECT isnull(SUBSTRING(MAX(q.association_id),7,3),"000") max_asnnum 
FROM $SPSS_DB..qeassociation q, $SPSS_DB..qolink l
WHERE l.program_id = "$program_id" and
      l.proposal_id = q.proposal_id and
      q.obset_id = "$obset_id"
EOQ
          $max_asnnum = SelectSql( $db, $query, 
                                   "Failed query for MAX asn number");
       }
       else {
          $max_asnnum = $new_asnnum;
       }
       PrintMsg("D","MAX asn for $program_id,$obset_id is $max_asnnum");
       $new_asnnum = base36_3_add($max_asnnum, 1);
       PrintMsg("D","NEW asn is $new_asnnum");
       $new_asn = "O".$program_id.$obset_id.$new_asnnum;
       PrintMsg("D","FULL NEW asn is $new_asn");

       # insert new members and product in asn_members
       #
       $query = << "EOQ";
INSERT asn_members (association_id, program_id, obset_id, member_num,
                    member_type, member_status, product_status) VALUES
                   ("$new_asn","$program_id","$obset_id","$sci_obs_num",
                    "SCIENCE","U","E")
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into asn_members");

       $query = << "EOQ";
INSERT asn_members (association_id, program_id, obset_id, member_num,
                    member_type, member_status, product_status) VALUES
                   ("$new_asn","$program_id","$obset_id","$gowave_obs_num",
                    "AUTO-WAVECAL","U","E")
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into asn_members");

       if ($gowave2 ne "") {  # only if filled
         $gowave2_obs_num = substr($gowave2,6,2);
         $query = << "EOQ";
INSERT asn_members (association_id, program_id, obset_id, member_num,
                    member_type, member_status, product_status) VALUES
                   ("$new_asn","$program_id","$obset_id","$gowave2_obs_num",
                    "AUTO-WAVECAL","U","E")
EOQ
         $count = RunSql( $db, $query );
         PrintMsg("I","$count records inserted into asn_members");
       }

       $query = << "EOQ";
INSERT asn_members (association_id, program_id, obset_id, member_num,
                    member_type, member_status, product_status) VALUES
                   ("$new_asn","$program_id","$obset_id","$new_asnnum",
                    "PRODUCT","P","U")
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into asn_members");

       # insert new members 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","$new_asnnum", "$obset_id","$sci_obs_num")
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into 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","$new_asnnum", "$obset_id","$gowave_obs_num")
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into asn_product_link");

       if ($gowave2 ne "") {  # only if filled
         $query = << "EOQ";
INSERT asn_product_link 
  (program_id, asn_obset_id, member_num, obset_id, ob_number) VALUES
  ("$program_id","$obset_id","$new_asnnum", "$obset_id","$gowave2_obs_num")
EOQ
         $count = RunSql( $db, $query );
         PrintMsg("I","$count records inserted into asn_product_link");
       }

       # get the exp_date for each new member (qobservation.pred_strt_tm)
       #
       $query = << "EOQ";
SELECT pred_strt_tm FROM $SPSS_DB..qobservation q
WHERE program_id = "$program_id" and
      obset_id = "$obset_id" and 
      ob_number = "$sci_obs_num"
EOQ
       $sci_pred_strt = SelectSql( $db, $query, "Failed query for sci_pred_strt");
       PrintMsg("D","sci_pred_strt = $sci_pred_strt");
       $query = << "EOQ";
SELECT pred_strt_tm FROM $SPSS_DB..qobservation
WHERE program_id = "$program_id" and
      obset_id = "$obset_id" and 
      ob_number = "$gowave_obs_num"
EOQ
       $gowave_pred_strt = SelectSql( $db, $query, "Failed query for gowave_pred_strt");
       PrintMsg("D","gowave_pred_strt = $gowave_pred_strt");

       if ($gowave2 ne "") { # only if filled
         $query = << "EOQ";
SELECT pred_strt_tm FROM $SPSS_DB..qobservation
WHERE program_id = "$program_id" and
      obset_id = "$obset_id" and 
      ob_number = "$gowave2_obs_num"
EOQ
         $gowave2_pred_strt = SelectSql( $db, $query, "Failed query for gowave2_pred_strt");
         PrintMsg("D","gowave2_pred_strt = $gowave2_pred_strt");
       }

       # fill asn_association, using last_exp_date of the latest member
       #
       if ($sci_pred_strt gt $gowave_pred_strt) {
         $first_exp_date = $gowave_pred_strt;
         $last_exp_date = $sci_pred_strt;
       }
       else {
         $first_exp_date = $sci_pred_strt;
         $last_exp_date = $gowave_pred_strt;
       }
       if ($gowave2 ne "") {  # only if filled
         if ($first_exp_date gt $gowave2_pred_strt) {
           $first_exp_date = $gowave2_pred_strt;
         }
         elsif ($last_exp_date lt $gowave2_pred_strt) {
           $last_exp_date = $gowave2_pred_strt;
         }
       }

       $query = << "EOQ";
INSERT asn_association (association_id, si_name, last_exp_date, collect_date)
VALUES ("$new_asn","STIS","$last_exp_date"," ")
EOQ
       PrintMsg("D","query=$query");
       $count = RunSql( $db, $query );
       PrintMsg("I","$count record inserted in asn_association");

       # new dms_asn_id entry for this ASN
       #
       $query = << "EOQ";
INSERT dms_asn_id (association_id, sms_id, creation_date) VALUES 
("$new_asn", " ", getdate())
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count record inserted in dms_asn_id");

       $last_ppp = $program_id;
       $last_ss = $obset_id;
    } # end while

    $query = <<"EOQ";
UPDATE dms_asn_id
SET sms_id = q.sms_id
FROM qolink_sms q, asn_members a, dms_asn_id d
WHERE
      d.sms_id = " " and
      d.association_id = a.association_id and
      a.member_type != "PRODUCT" and
      q.program_id = a.program_id and
      q.obset_id   = a.obset_id and
      q.ob_number  = a.member_num
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count dms_asn_id records updated for sms_id");

#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    $db->dbclose;
    PrintMsg ("I","---  end  --- Make New STIS ASNs ----------");
    exit( $PROCESS_SUCCESS);  

#---------------------------------------------------------------------
# end of main procedure -- subroutines follow
#---------------------------------------------------------------------
sub base36_3_add  { # two args: asn_mem_id (3-char base36),
                    #            increment (int)
                    # purpose: increment is added to second digit of asn_id.
                    # return: the new 3-char asn_mem_id
    my ($base,$increment) = @_;
    my $base36 = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    my $first = substr($base,0,1);
    my $second = substr($base,1,1);
    my $first_int = index( $base36, $first);
    my $second_int = index( $base36, $second);
    my $second_sum = $second_int + $increment;
    my $carry = int($second_sum / 36);
    my $new_second_int = $second_sum - 36 * $carry;
    my $new_first_int = $first_int + $carry;
    my $new_value = substr($base36, $new_first_int,1).
        substr($base36,$new_second_int,1)."0";
}