#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: dms_asn_insert
# 
# This perl script verifies that the input environment variables are OK, then
# it generates a set of queries to insert special DMS 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 NICMOS and ACS database info
# for unassociated datasets that fulfill the new instrument specific rules for
# associations. Once all the old datasets are reprocessed this tool need not be
# reused since new TRANSVERSE rules will be using the new rules. 
#
# During processing the following temporary tables are created and dropped
#            #dms_acqs
#            #dms_obsets
#            #old_asns
#            #dms_exps
#            #dms_asns
#            #asn_list
#            #prod_times
#
# soon after their last usage.
#
# Interactive Usage:
#	dms_asn_insert.pl [previous_sms_id] sms_id
#
#   The sms_id provided must be a baseline SMS so that its end time is
#   a true indication of a SMS boundary that was observed.
#   
#   If previous_sms_id is present then its end time is the lower boundary
#   for processing. Both sms_ids must be baselined (sms_send_stt = "B").
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 08/18/05 54219    Baum        Initial code 
# 09/23/05 54219    MSwam       throw out CAL proposals, match on ap_1,ap_2
# 10/31/05 54219    MSwam       Adjust to EXP-DTH, PROD-DTH
# 11/09/05 54219    MSwam       throw out NICMOS DARKs
# 11/23/05 54799    MSwam       throw out parallels and internal calibrations
# 12/08/05 54799    MSwam       throw out ORBIT-POLE
# 04/13/06 55219    MSwam       adjust max_asn query to use qeassociation
# 07/06/06 56015    MSwam       add sms_id, creation_date to dms_asn_id
# 08/02/06 56359    MSwam       leave qolink_sms alone
# 11/01/07 58880    MSwam       fix bug in max_asn query for multi-instr cases
#----------------------------------------------------------------------------
# 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
require 'sogs_time_pkg.pl';  # manipulate time in SOGS format(yyy.ddd:hh:mm:ss) 

#specify exit status values

    $PROCESS_FAILURE = 1; 
    $PROCESS_SUCCESS = 0; 

    $usage = <<"EOM";
Usage:
   dms_asn_insert.pl [previous_sms_id] sms_id
   
      where: 
      
      previous_sms_id - optional: this must be a baselined SMS found in 
          SPSS_DB sms_catalog. It should be the same value as used for
          the sms_id argument of a previous run. For the first use of this
          tool, this argument must not be used.
      
      sms_id: this must be a baselined SMS found in SPSS_DB:sms_catalog.
          It must NOT be the SMS of the current week. This script checks the
          qolink_sms for archived datasets so all dataset processing 
          must be completed for the SMS specified.
      
      N.B. A replanned SMS will not work because we must find
      an SMS end time that is valid to guarantee complete obset info. 

      When this script is rerun with a later SMS only the new DMS associations
      will be created. If the same SMS or an earlier SMS is used, no new records
      will be inserted.
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) {
       $PRV_ID = "NONE";
       $SMS_ID = $ARGV[0];
    } else {    
       $PRV_ID = $ARGV[0];
       $SMS_ID = $ARGV[1];
    }

# get the required external variables

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

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

    PrintMsg ("I","--- start --- Insert DMS association records ----------");
    PrintMsg ("I","--- start  SMS: $PRV_ID");
    PrintMsg ("I","--- ending SMS: $SMS_ID");
    PrintMsg ("I","--- OPUS_DB: $OPUS_DB");
    PrintMsg ("I","--- SPSS_DB: $SPSS_DB");
    PrintMsg ("I","--- ARCH_DB: $ARCH_DB");
    use ST_DBlib;
#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    $db = new ST_DBlib $DSQUERY, $OPUS_DB;
    die if $db == -1;

#---------------------------------------------------------------------
# select db info to verify PREV SMS is valid and get end time
#---------------------------------------------------------------------
    if ($PRV_ID eq "NONE") {
       $sms_start = "1990.001:00:00:00";
    } else {
       $query = <<"EOQ";
SELECT sms_send_stt,end_time 
FROM $SPSS_DB..sms_catalog 
WHERE sms_id='$PRV_ID'
EOQ

       $err_msg = "Cannot find $PRV_ID in sms_catalog table.";
       ($sms_status,$sms_stop) = SelectSql( $db, $query, $err_msg);

       if (!defined($sms_status) || $sms_status eq ' ' || $sms_status eq '') {
          print $usage;
          PrintMsg ("E",
            "This sms_id $PRV_ID has no SMS baselined in sms_catalog.");
          exit( $PROCESS_FAILURE);  
       }
       if ($sms_status ne 'B') {
         print $usage;
         PrintMsg ("E",
            "This sms_id $SMS_ID is not the name of a baselined SMS.");
         exit( $PROCESS_FAILURE);  
       }
       $sms_start = substr($sms_stop,0,17);
    }

#---------------------------------------------------------------------
# select db info to verify SMS is valid and get end time
#---------------------------------------------------------------------
    $query = <<"EOQ";
SELECT sms_send_stt,end_time 
FROM $SPSS_DB..sms_catalog 
WHERE sms_id='$SMS_ID'
EOQ

    $err_msg = "Cannot find $SMS_ID in sms_catalog table.";
    ($sms_status,$sms_stop) = SelectSql( $db, $query, $err_msg);

    if (!defined($sms_status) || $sms_status eq ' ' || $sms_status eq '') {
      print $usage;
      PrintMsg ("E","This sms_id $SMS_ID has no SMS baselined in sms_catalog.");
      exit( $PROCESS_FAILURE);  
    }
    if ($sms_status ne 'B') {
      print $usage;
      PrintMsg ("E","This sms_id $SMS_ID is not the name of a baselined SMS.");
      exit( $PROCESS_FAILURE);  
    }
    $sms_stop = substr($sms_stop,0,17);
    PrintMsg ("I","---   start time is $sms_start.");
    PrintMsg ("I","--- SMS end time is $sms_stop.");

    if ($sms_start gt $sms_stop) {
      PrintMsg ("E","SMS end time must be greater than start time.");
      exit( $PROCESS_FAILURE);  
    }

#---------------------------------------------------------------------
# Create temp table #dms_acqs 
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT distinct o.program_id,o.obset_id
INTO #dms_acqs
FROM $SPSS_DB..qobservation o, $SPSS_DB..qolink l, $SPSS_DB..qelogsheet q
WHERE o.si_id = "ACS" and o.executed_flg = "E" and  
      o.control_id != " " and o.mt_flag != "T" and
      o.pred_strt_tm >= "$sms_start" and
      o.pred_strt_tm <  "$sms_stop" and
      l.program_id = o.program_id and
      l.obset_id = o.obset_id and
      l.ob_number = o.ob_number and
      q.proposal_id = l.proposal_id and
      q.obset_id = l.obset_id and
      q.alignment_id = l.alignment_id and
      q.exposure_id = l.exposure_id and
      q.version_num = l.version_num and 
      q.opmode = "ACQ"
EOQ

    $num_acqs = RunSql( $db, $query );
    PrintMsg ("I","$num_acqs obsets have ACS ACQ mode exposures.");

#---------------------------------------------------------------------
# create temp table #dms_obsets - the sets of observation that are
# DMS association candidates
#---------------------------------------------------------------------
    PrintMsg ("I","Query to get association candidates.");

#-------------------------------------------------------------------
# NOTE: The coord_id, prime_aper trick below is meant to weed-out
#       parallels.  It was derived from the descriptions of the
#       qobservation.coord_id and prime_aper fields in SPSS documentation.
#       Those descriptions are included in PR 54799 for reference.
#-------------------------------------------------------------------
    $query = <<"EOQ";
SELECT o.program_id, o.obset_id, s.config, s.opmode, o.coord_id, s.targname,
   s.sp_1, s.sp_2, s.sp_3, s.sp_4, s.ap_1, s.ap_2, s.minwave,
   count(*) num_exp
INTO #dms_obsets
FROM $SPSS_DB..qobservation o, qolink_sms q, $SPSS_DB..qolink l, 
     $SPSS_DB..qelogsheet s, $SPSS_DB..qpdescrip p, $ARCH_DB..acs_science a 
WHERE o.si_id="ACS" and 
      o.pred_strt_tm >= "$sms_start" and
      o.pred_strt_tm <  "$sms_stop" and
      o.control_id != " " and
      o.mt_flag != "T" and
      substring(o.coord_id,1,1) = substring(o.prime_aper,1,1) and
      q.program_id = o.program_id and
      q.obset_id = o.obset_id and
      q.ob_number = o.ob_number and 
      q.status = "E" and  
      o.program_id = a.acs_program_id and
      o.obset_id = a.acs_obset_id and
      o.ob_number = a.acs_obsnum and 
      a.acs_imagetyp = "EXT" and
      NOT EXISTS (SELECT * FROM #dms_acqs c 
                  WHERE c.program_id = o.program_id and
                        c.obset_id = o.obset_id) and
      NOT exists (SELECT * FROM asn_members a 
                  WHERE a.program_id = o.program_id and
                        a.obset_id = o.obset_id and
                        a.member_num = o.ob_number) and
      l.program_id = o.program_id and
      l.obset_id = o.obset_id and
      l.ob_number = o.ob_number and
      s.proposal_id = l.proposal_id and
      s.obset_id = l.obset_id and
      s.alignment_id = l.alignment_id and
      s.exposure_id = l.exposure_id and
      s.version_num = l.version_num and 
      p.proposal_id = s.proposal_id and
      p.proposal_typ != "CA" and
      p.prop_category != "CAL/ACS" and
      s.targname not in 
          ("EARTH_CALIB","DARK","NONE","BIAS","TUNGSTEN","DEUTERIUM")
GROUP BY o.program_id, o.obset_id, s.config, s.opmode, o.coord_id, s.targname,
      s.sp_1, s.sp_2, s.sp_3, s.sp_4, s.ap_1, s.ap_2, s.minwave
HAVING count(*) > 1 
UNION
SELECT o.program_id, o.obset_id, s.config, s.opmode, o.coord_id, s.targname,
   s.sp_1, s.sp_2, s.sp_3, s.sp_4, s.ap_1, s.ap_2, s.minwave,
   count(*) num_exp
FROM $SPSS_DB..qobservation o, qolink_sms q, $SPSS_DB..qolink l, 
     $SPSS_DB..qelogsheet s, $SPSS_DB..qpdescrip p, $ARCH_DB..nicmos_science n
WHERE o.si_id="NIC" and 
      o.pred_strt_tm >= "$sms_start" and
      o.pred_strt_tm <  "$sms_stop" and
      o.control_id != " " and
      o.mt_flag != "T" and
      substring(o.coord_id,1,1) = substring(o.prime_aper,1,1) and
      q.program_id = o.program_id and
      q.obset_id = o.obset_id and
      q.ob_number = o.ob_number and 
      q.status = "E" and  
      o.program_id = n.nss_program_id and
      o.obset_id = n.nss_obset_id and
      o.ob_number = n.nss_obsnum and 
      n.nss_imagetyp in ("EXT","SCIENCE") and
      NOT EXISTS (SELECT * FROM asn_members a 
                  WHERE a.program_id = o.program_id and
                        a.obset_id = o.obset_id and
                        a.member_num = o.ob_number) and
      NOT EXISTS (SELECT * FROM $ARCH_DB..oms_summary
                  WHERE oss_program_id = o.program_id and
                        oss_obset_id = o.obset_id and
                        oss_obsnum = o.ob_number and
                        oss_parallel = "YES-SECONDARY") and
      l.program_id = o.program_id and
      l.obset_id = o.obset_id and
      l.ob_number = o.ob_number and
      s.proposal_id = l.proposal_id and
      s.obset_id = l.obset_id and
      s.alignment_id = l.alignment_id and
      s.exposure_id = l.exposure_id and
      s.version_num = l.version_num and 
      p.proposal_id = s.proposal_id and
      p.proposal_typ != "CA" and
      p.prop_category not in ("CAL/NIC","CAL/NIC/PAR") and
      s.opmode != "ACQ" and
      s.targname not in ("DARK","ORBIT-POLE")
GROUP BY o.program_id, o.obset_id, s.config, s.opmode, o.coord_id, s.targname,
      s.sp_1, s.sp_2, s.sp_3, s.sp_4, s.ap_1, s.ap_2, s.minwave
HAVING count(*) > 1 
EOQ

    $num_obsets = RunSql( $db, $query );
    PrintMsg ("I","$num_obsets new associations to be created.");

    if ($num_obsets == 0) {
       $db->dbclose;
       PrintMsg ("I","---  end  --- Insert DMS association records ----------");
       exit( $PROCESS_SUCCESS);  
    }
#---------------------------------------------------------------------
# create temp table #old_asns - the max association member number for
# any obset
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT d.program_id,d.obset_id,MAX(SUBSTRING(q.association_id,7,3)) max_asn
INTO #old_asns
FROM #dms_obsets d, $SPSS_DB..qeassociation q, $SPSS_DB..qolink l
WHERE l.program_id = d.program_id and
      l.obset_id = d.obset_id and
      q.proposal_id = l.proposal_id and
      q.obset_id = l.obset_id
GROUP BY d.program_id, d.obset_id 
EOQ

    $num_old = RunSql( $db, $query );
    PrintMsg ("I","$num_old obsets have old associations.");
    PrintMsg ("I","Query to get all exposures in associations.");

#---------------------------------------------------------------------
# create temp table #dms_exps - the sets of exposures to be put
# into new associations
#
# 06/01/06 55364 MSwam  query updated to exclude existing asn_members
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT 
   o.program_id, o.obset_id, q.config, q.opmode, o.coord_id, q.targname,
   q.sp_1, q.sp_2, q.sp_3, q.sp_4, q.ap_1, q.ap_2, q.minwave, l.ob_number,
   isnull( m.max_asn, "000") max_asn
INTO #dms_exps
FROM #old_asns m, #dms_obsets o, $SPSS_DB..qolink l, $SPSS_DB..qelogsheet q 
WHERE m.program_id  =* o.program_id and
      m.obset_id    =* o.obset_id and
      l.program_id  = o.program_id and
      l.obset_id    = o.obset_id and
      q.proposal_id = l.proposal_id and
      q.obset_id    = l.obset_id and
      q.alignment_id = l.alignment_id and
      q.exposure_id = l.exposure_id and
      q.version_num = l.version_num and 
      NOT EXISTS (SELECT * FROM asn_members a 
                  WHERE a.program_id = l.program_id and
                        a.obset_id = l.obset_id and
                        a.member_num = l.ob_number) and
      o.config = q.config and
      o.opmode = q.opmode and
      o.targname = q.targname and
      o.sp_1 = q.sp_1 and
      o.sp_2 = q.sp_2 and
      o.sp_3 = q.sp_3 and
      o.sp_4 = q.sp_4 and
      o.ap_1 = q.ap_1 and
      o.ap_2 = q.ap_2 and
      o.minwave = q.minwave
EOQ

    $num_exp = RunSql( $db, $query );
    PrintMsg("I","$num_exp exposures to be included in new associations.");
#---------------------------------------------------------------------
#  Drop unneeded tables. Create an index for this table and create 
#  empty #dms_asns table
#---------------------------------------------------------------------
$query = <<"EOQ";
DROP TABLE #dms_acqs
DROP TABLE #old_asns
DROP TABLE #dms_obsets
CREATE UNIQUE CLUSTERED INDEX #dms_exps_1 
ON #dms_exps (
   program_id,obset_id, config, opmode, coord_id, targname, 
   sp_1, sp_2, sp_3, sp_4, ap_1, ap_2, minwave, ob_number, max_asn
)
CREATE TABLE #dms_asns (
   association_id varchar(9), 
   program_id varchar(3),
   obset_id varchar(2),
   member_num varchar(3),
   member_type varchar(12),
   member_status varchar(1),
   product_status varchar(1)   
)
EOQ

    RunSql( $db, $query );
#---------------------------------------------------------------------
# populate temp table #dms_asns - by processing all #dms_exps records
# and dividing the associations where the exposure description changes.
#---------------------------------------------------------------------

    $old_program_id = " ";
    $old_obset_id = " ";
    $old_config = " ";
    $old_opmode = " ";
    $old_coord_id = " ";
    $old_targname = " ";
    $old_sp_1 = " ";
    $old_sp_2 = " ";
    $old_sp_3 = " ";
    $old_sp_4 = " ";
    $old_ap_1 = " ";
    $old_ap_2 = " ";
    $old_minwave = 0.0;
    $new_asn_id = " ";
    $old_mem_type = " ";
    $asn_count = 0;
    $exp_count = 0;

$query = <<"EOQ";
SELECT * from #dms_exps
EOQ
    
    PrintMsg("I","Loading internal exposure list from #dms_exps.");
    $err_msg  = "Cannot query #dms_exps.";
    $err_msg2 = "Cannot query next #dms_exps.";
    @in_record = FirstRecordSql( $db, $query, $err_msg);
    
    while (defined($in_record[0])) {
	push @exp_list, [@in_record];
        @in_record = NextRecordSql( $db, $err_msg2);
    }
    $load_count = scalar @exp_list;
    PrintMsg("I","Loaded $load_count records from #dms_exps.");
        
    if ($load_count == 0) {     
       PrintMsg( "E","Failed to read the #dms_exps table");
       $db->dbclose;
       exit( $PROCESS_FAILURE);
    } 
    PrintMsg("I","Processing dms_exp records to build #dms_asns table."); 
    PrintMsg("I","... this will take awhile");
          
    foreach $record_ptr (@exp_list) {
       @in_record  = @$record_ptr;
       $program_id = $in_record[0];
       $obset_id   = $in_record[1];
       $config     = $in_record[2];
       $opmode     = $in_record[3];
       $coord_id   = $in_record[4];
       $targname   = $in_record[5];
       $sp_1       = $in_record[6];
       $sp_2       = $in_record[7];
       $sp_3       = $in_record[8];
       $sp_4       = $in_record[9];
       $ap_1       = $in_record[10];
       $ap_2       = $in_record[11];
       $minwave    = $in_record[12];
       $ob_number  = $in_record[13];
       $max_asn    = $in_record[14];
       
       if ( $old_program_id eq $program_id   &&
            $old_obset_id   eq $obset_id     &&
            $old_config     eq $config       &&
            $old_opmode     eq $opmode       &&
            $old_coord_id   eq $coord_id     &&
            $old_targname   eq $targname     &&
            $old_sp_1       eq $sp_1         &&
            $old_sp_2       eq $sp_2         &&
            $old_sp_3       eq $sp_3         &&
            $old_sp_4       eq $sp_4         &&
            $old_ap_1       eq $ap_1         &&
            $old_ap_2       eq $ap_2         &&
            $old_minwave    == $minwave ) 
       {  # add this exposure to existing asn
          $query = <<"EOQ";
INSERT INTO #dms_asns VALUES (
            "$new_asn_id","$old_program_id","$old_obset_id",
            "$ob_number","$old_mem_type","U","E")
EOQ

          if( 1 != RunSql( $db, $query )) {
             PrintMsg("E","Failed to insert record into #dms_asns.");
             $db->dbclose;
             exit( $PROCESS_FAILURE);
          }
          $exp_count += 1;
       } else { # create new association
          if ($old_program_id eq $program_id &&
              $old_obset_id   eq $obset_id )
          {  # same obset - increment obset asn counter
              $obset_asn_num += 1;
          } else {
              $obset_asn_num = 1;
          }
          # reset old values          
          $old_program_id = $program_id;
          $old_obset_id   = $obset_id;
          $old_config     = $config;
          $old_opmode     = $opmode;
          $old_coord_id   = $coord_id;
          $old_targname   = $targname;
          $old_sp_1       = $sp_1;
          $old_sp_2       = $sp_2;
          $old_sp_3       = $sp_3;
          $old_sp_4       = $sp_4;
          $old_ap_1       = $ap_1;
          $old_ap_2       = $ap_2;
          $old_minwave    = $minwave;
          
          if (substr($old_config,0,1) eq "N") {
             # this is NICMOS
             $inst = "N";
             $prod_type = "PROD-TARG";
             $old_mem_type = "EXP-TARG";
          } elsif ( substr($old_config,0,1) eq "A"){
             # this is ACS
             $inst = "J";
             $prod_type = "PROD-DTH";
             $old_mem_type = "EXP-DTH";
          } else {
             # this is an error
             PrintMsg("E",
                 "Cannot process $old_program_id$old_obset_id$ob_number.");
             PrintMsg("E","Do not recognize config = $old_config."); 
             $db->dbclose;
             exit( $PROCESS_FAILURE);
          }
          # construct new asn name
          $asn_count += 1;
          $exp_count += 1;
          $new_asn_num = base36_3_add( $max_asn, $obset_asn_num);
          $new_asn_id = $inst.$program_id.$obset_id.$new_asn_num;

          # write asn product record and first exp records
          $query = <<"EOQ";
INSERT INTO #dms_asns VALUES (
            "$new_asn_id","$program_id","$obset_id",
            "$new_asn_num","$prod_type","P","U")
INSERT INTO #dms_asns VALUES (
            "$new_asn_id","$program_id","$obset_id",
            "$ob_number","$old_mem_type","U","E")
EOQ

          if( 2 != RunSql( $db, $query )) {
             PrintMsg("E","Failed to insert 2 records into #dms_asns.");
             $db->dbclose;
             exit( $PROCESS_FAILURE);
          }
       } # end exposure characteristics comparison
    }  # end foreach loop
    PrintMsg("I",
        "$asn_count associations created containing one product each.");
    PrintMsg("I","$exp_count exposures inserted into new associations.");

    # create #asn_list 
    $query = << "EOQ";
SELECT DISTINCT association_id INTO #asn_list FROM #dms_asns
EOQ

    $num_asn = RunSql( $db, $query );
    PrintMsg("I","$num_asn associations inserted into #asn_list.");

    # cleanup output tables before new insertions
    PrintMsg("I","Start cleanup queries - expect 0 records deleted.");

    $query = << "EOQ";
DELETE asn_members FROM asn_members q, #dms_asns d
WHERE q.association_id = d.association_id and
      q.program_id     = d.program_id and
      q.obset_id       = d.obset_id and
      q.member_num     = d.member_num
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records deleted from asn_members");
     
    $query = << "EOQ";
DELETE asn_product_link FROM asn_product_link q, #dms_asns d
WHERE q.program_id     = d.program_id and
      q.asn_obset_id   = d.obset_id and
      q.member_num     = d.member_num and
      d.member_status = "P"
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records deleted from asn_product_link");
     
    $query = << "EOQ";
DELETE asn_association FROM asn_association q, #asn_list d
WHERE q.association_id = d.association_id 
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records deleted from asn_association");

    $query = << "EOQ";
DELETE dms_asn_id FROM dms_asn_id q, #asn_list d
WHERE q.association_id = d.association_id 
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records deleted from dms_asn_id");

    # insert new records into dms_asn_id
    PrintMsg("I","Start inserting records into tables.");

    # the sms_id field is blank for now, gets updated later
    $query = << "EOQ";
INSERT dms_asn_id
SELECT association_id," ",getdate() 
FROM #asn_list
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records inserted into dms_asn_id");

    $query = << "EOQ";
INSERT INTO asn_association( association_id, si_name, last_exp_date, 
       collect_date) 
SELECT d.association_id, max(o.si_id), max(pred_strt_tm), " "
FROM #dms_asns d, $SPSS_DB..qobservation o
WHERE  o.program_id = d.program_id and
       o.obset_id = d.obset_id and
       o.ob_number = d.member_num and
       d.product_status = "E"
GROUP BY d.association_id
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records inserted into asn_association");

    $query = << "EOQ";
INSERT INTO asn_members
SELECT * FROM #dms_asns
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records inserted into asn_members");

    $query = << "EOQ";
INSERT INTO asn_product_link( program_id, asn_obset_id, member_num,
   obset_id, ob_number)
SELECT program_id, obset_id, SUBSTRING(association_id, 7,3),
   obset_id, member_num
FROM #dms_asns
WHERE product_status = "E"
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records inserted into asn_product_link");
    
$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
      q.program_id = a.program_id and
      q.obset_id   = a.obset_id and
      q.ob_number  = a.member_num and
      d.association_id = a.association_id
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  --- Insert DMS association records ----------");
    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";              
}