#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: backfill_qolink_sms_times.pl
# 
# This perl script is used to backfill any qolink_sms records with time_type
# that is blank. The time_type will be reset to "P" (planning) and both the
# start_time and end_time will be set to the qobservation.pred_strt_tm. The
# qobservation.pred_stop_tm is not valid for this purpose and the asn_table
# task will only look at the start_time when ordering the exposures in the
# ASN FITS table.
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 08/25/05 53998    Baum        Initial code 
#----------------------------------------------------------------------------
# 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

#specify exit status values

    $PROCESS_FAILURE = 1; 
    $PROCESS_SUCCESS = 0; 

# get the required external variables

    $usage = <<"EOM";
Usage:
   backfill_qolink_sms_times.pl OPUS_DB SPSS_DB
   
      where: 
      
      OPUS_DB is the name of the OPUS database to be backfilled.
      SPSS_DB is the name of the SPSS database with qobservation times.
EOM

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

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

    PrintMsg ("I","--- start --- Backfill qolink_sms times -------");
    PrintMsg ("I","--- OPUS_DB: $OPUS_DB");
    PrintMsg ("I","--- SPSS_DB: $SPSS_DB");

#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    $db = new ST_DBlib $DSQUERY, $OPUS_DB;
    die if $db == -1;

#---------------------------------------------------------------------
# set time_type where time is present
#---------------------------------------------------------------------
    PrintMsg("I","Updating blank time_type when start_time is present.");
    $query = << "EOQ";
UPDATE qolink_sms
SET time_type = "P"
WHERE time_type = " " and start_time != " "
EOQ
    
    $count = RunSql( $db, $query );
    PrintMsg("I","$count blank time_type values set to P");

#---------------------------------------------------------------------
# get #update_list
#---------------------------------------------------------------------
    PrintMsg("I","Creating temporary table #update_list");

    $query = << "EOQ";
SELECT q.program_id, q.obset_id, q.ob_number, start_time = o.pred_strt_tm,
  end_time = o.pred_strt_tm
INTO #update_list
FROM qolink_sms q, $SPSS_DB..qobservation o
WHERE q.time_type = " " and q.ob_number like "__" and
      o.program_id = q.program_id and
      o.obset_id = q.obset_id and
      o.ob_number = q.ob_number
UNION
SELECT q.program_id, q.obset_id, q.ob_number, 
    start_time = min(o.pred_strt_tm), end_time=max(o.pred_strt_tm)
FROM qolink_sms q, asn_product_link l, $SPSS_DB..qobservation o
WHERE q.time_type = " " and q.ob_number like "___" and
      l.program_id   = q.program_id and
      l.asn_obset_id = q.obset_id and
      l.member_num   = q.ob_number and
      o.program_id  = l.program_id and
      o.obset_id    = l.obset_id and
      o.ob_number   = l.ob_number
GROUP BY q.program_id, q.obset_id, q.ob_number
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records selected into #update_list");
#---------------------------------------------------------------------
# get index on #update_list
#---------------------------------------------------------------------
    $query = << "EOQ";
create clustered index  #update_list_1 on #update_list
(program_id,obset_id,ob_number)
EOQ
    PrintMsg("I","Creating index for #update_list");
    RunSql( $db, $query );

#---------------------------------------------------------------------
# update blank exposure times in chunks by time
#---------------------------------------------------------------------
   $total = 0;
   @times = ("1997.0","1997.1","1997.2","1997.3",
             "1998.0","1998.1","1998.2","1998.3",
             "1999.0","1999.1","1999.2","1999.3",
             "2000.0","2001.3","2002.0","2002.3");

    foreach $chunk (@times) {
      PrintMsg("I","Updating $chunk times in qolink_sms.");    
      $query = << "EOQ";
UPDATE qolink_sms
SET q.start_time = u.start_time, q.end_time=u.start_time, 
    q.time_type = "P"
FROM qolink_sms q, #update_list u
WHERE 
      q.program_id = u.program_id and
      q.obset_id = u.obset_id and
      q.ob_number = u.ob_number and
      u.start_time like "$chunk%"
EOQ
    
      $count = RunSql( $db, $query );
      PrintMsg("I","$count qolink_sms records backfilled for $chunk");
      $total += $count;
  }
  PrintMsg("I","$total total qolink_sms records backfilled");
  
#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    $db->dbclose;
    PrintMsg ("I","---  end  --- Backfill qolink_sms times -------");
    exit ($PROCESS_SUCCESS);