#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: stis_gowave_old2new
#
# Purpose: Creates new STIS associations from a list of existing
#          associations and new members.
# 
# 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 a new STIS association for each
# new ASN input name (column1).  Members are either existing science exposures
# (8-chars in column2) or GO wavecal exposures (9-chars in column2 or column3).
#
# Interactive Usage:
#	stis_gowave_old2new.pl file_of_gowave_old2new
#
# 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
# 08/15/06 56359    MSwam       new reqts to ALWAYS make new asns
#----------------------------------------------------------------------------
# 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_old2new.pl file_of_gowave_old2new
   
      where: 
      
      file_of_gowave_old2new - required: this ASCII file can contain
          up to three columns of information:
          column1= name of an existing STIS association whose members will
                   become part of a new ASN
          column2= 8-char association SCIENCE member name OR
                   9-char name of a GO wavecal exposure that will become
                   part of the new ASN
          column3= (optional) 9-char name of a second GO wavecal exposure 
                   that will become part of 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;
   }

   $member2 = "";
   @asn_list = ();
   @member_list = ();
   while (<INFILE>) {
      #
      # split the read buffer ($_) into fields
      ($asnname, $member1, $member2) = split(/\s+/,$_);
      chomp($member1);
      chomp($member2);

      # save the names in separate lists
      push @asn_list, $asnname;
      push @member_list, $member1;

      if ($member2 ne "") {  # could be an empty entry, if not put in lists
        push @asn_list, $asnname;
        push @member_list, $member2;
      }

   }  # end while <INFILE>

   $load_count = scalar @asn_list;
   PrintMsg("I","Loaded $load_count members from $INFILE");

#---------------------------------------------------------------------
# build new ASNs
#---------------------------------------------------------------------
    $last_asn = " ";
    $new_asn_flag = 0;
    while (@asn_list) {
       $asnname = pop @asn_list;
       $member1  = pop @member_list;
PrintMsg("D","popped asn,member for processing: $asnname $member1");

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

       # compute the new ASN number from the last known one for prog/obset
       #   or use the same one if the "pppss" hasn't changed
       #
       if ($asnname ne $last_asn) {
          $new_asn_flag = 1;
          $last_exp_date = " ";
          $last_asn = $asnname;
          #
          # first get the MAX from SPSS_DB:qeassociation
          $query = << "EOQ";
SELECT isnull(SUBSTRING(MAX(q.association_id),7,3),"000") max_asnnum1 
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_asnnum1 = SelectSql( $db, $query, 
                                   "Failed query for MAX asn number 1");
          PrintMsg("D","MAX SPSS asn for $program_id,$obset_id is $max_asnnum1");
          #
          # now get the MAX from OPUS_DB:asn_association
          $query = << "EOQ";
SELECT isnull(SUBSTRING(MAX(m.association_id),7,3),"000") max_asnnum2 
FROM $OPUS_DB..asn_members m
WHERE m.program_id = "$program_id" and
      m.obset_id = "$obset_id"
EOQ
          $max_asnnum2 = SelectSql( $db, $query, 
                                   "Failed query for MAX asn number 2");
          PrintMsg("D","MAX OPUS asn for $program_id,$obset_id is $max_asnnum2");
          if ($max_asnnum1 ge $max_asnnum2) {
             $max_asnnum = $max_asnnum1;
          }
          else {
             $max_asnnum = $max_asnnum2;
          }
          $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");
          #
          # load OLD asn name into stis_old_asns so we can later figure
          # out which old ASNs were replaced with new ones
          $query = << "EOQ";
INSERT stis_old_asns (old_association_id, new_association_id) VALUES
                   ("$asnname","$new_asn")
EOQ
          $count = RunSql( $db, $query );
          PrintMsg("I","$count records inserted into stis_old_asns");
       }

       # insert new members and product in asn_members
       #
       if (length($member1) == 8) {
         $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","$member1_obs_num",
                    "SCIENCE","U","E")
EOQ
       }
       else {
         $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","$member1_obs_num",
                    "AUTO-WAVECAL","U","E")
EOQ
       }
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into asn_members");

       if ($new_asn_flag == 1) {
         $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","$member1_obs_num")
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into asn_product_link");

       # insert new members in stis_go_wavecal, for documenting
       # which old singletons have become asn members
       $query = << "EOQ";
INSERT stis_go_wavecal
  (program_id, obset_id, member_num) VALUES
  ("$program_id","$obset_id","$member1_obs_num")
EOQ
       $count = RunSql( $db, $query );
       PrintMsg("I","$count records inserted into stis_go_wavecal");

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

       # fill asn_association, using last_exp_date of the latest member
       #
       if ($member1_pred_strt gt $last_exp_date) {
         $last_exp_date = $member1_pred_strt;

         if ($new_asn_flag == 1) {
           $query = << "EOQ";
INSERT asn_association (association_id, si_name, last_exp_date, collect_date)
VALUES ("$new_asn","STIS","$last_exp_date"," ")
EOQ
         }
         else {
           $query = << "EOQ";
UPDATE asn_association set last_exp_date = "$last_exp_date" WHERE
association_id = "$new_asn"
EOQ
         }
         PrintMsg("D","query=$query");
         $count = RunSql( $db, $query );
         PrintMsg("I","$count record inserted/updated in asn_association");
       }

       # new dms_asn_id entry for this ASN
       #
       if ($new_asn_flag == 1) {
         $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");
       }

       $new_asn_flag = 0;
    } # 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";
}