#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: reprocess_nic_saa
#
# This perl script regenerates all the records for the nic_saa_link table
# using the existing nic_saa_dark table and a new delta time of 5100 secs (85
# min).
#
# During processing the following temporary tables are created and dropped 
# soon after their last usage.
#               #obslist          all nicmos observations except dumps
#               #nicsaa           get nic saa data from obslist and nic_saa_exit
#
#
# Interactive Usage:
#	reprocess_nic_saa.pl  
#
#	Note that the following environment variables are also required:
#       DSQUERY, SPSS_DB and OPUS_DB.
#
#  Return status: 0 = success, 1 = failure 
#
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 10/08/03 49553    Baum        Initial version.
#----------------------------------------------------------------------------
# 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
    
    $NIC_SAA_MAX_DELTA = 5100;  # new value for reprocessing

# get the external variables
    $DSQUERY=           $ENV{"DSQUERY"};
    $OPUS_DB=           lc($ENV{"OPUS_DB"});
    $SPSS_DB=           lc($ENV{"SPSS_DB"});

# begin processing
    use ST_DBlib;
    PrintMsg ("I","--- start --- Regenerate NIC_SAA Table Records ----------");
#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    $db = new ST_DBlib $DSQUERY, $OPUS_DB;
    die if $db == -1;
#---------------------------------------------------------------------
# Create temp table #obslist for NICMOS observations since NCS.
# Exclude dumps having blank control_id.
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT o.program_id,o.obset_id,o.ob_number,l.proposal_id,l.alignment_id,
   l.exposure_id,l.version_num
INTO #obslist
FROM  $SPSS_DB..qobservation o, $SPSS_DB..qolink l
WHERE o.pred_strt_tm>"2000.170"
  and o.si_id = "NIC"
  and o.control_id!=" "
  and l.program_id = o.program_id
  and l.obset_id = o.obset_id 
  and l.ob_number = o.ob_number
EOQ
    $count = RunSql( $db, $query );
    PrintMsg ("I","$count NICMOS observations put into #obslist.");
#---------------------------------------------------------------------
#  Create temporary table #nicsaa for NIC SAA exit data. This is the last query
#  using spss_db tables.
#---------------------------------------------------------------------

$query = <<"EOQ";
SELECT l.program_id,l.obset_id,l.ob_number,n.saa_exit,e.config,e.targname
INTO #nicsaa
FROM #obslist l, $SPSS_DB..nic_saa_exit n, $SPSS_DB..qelogsheet e
WHERE l.program_id=n.program_id
  and l.obset_id=n.obset_id 
  and l.ob_number=n.ob_number
  and n.delta_time<$NIC_SAA_MAX_DELTA 
  and l.proposal_id=e.proposal_id 
  and l.obset_id=e.obset_id
  and l.alignment_id=e.alignment_id 
  and l.exposure_id=e.exposure_id 
  and l.version_num=e.version_num 
ORDER BY l.program_id,l.obset_id,l.ob_number
EOQ

    $nicsaa_count = RunSql( $db, $query );
    PrintMsg("I","$nicsaa_count records for #nicsaa.");

    #---------------------------------------------------------------------
    #  Delete old data from nic_saa_link
    #---------------------------------------------------------------------

$query = <<"EOQ";
CREATE UNIQUE CLUSTERED INDEX #nicsaa_1 
ON #nicsaa (program_id,obset_id,ob_number)
DELETE nic_saa_link 
FROM nic_saa_link,#nicsaa
WHERE
  nic_saa_link.program_id=#nicsaa.program_id and
  nic_saa_link.obset_id=#nicsaa.obset_id and
  nic_saa_link.ob_number=#nicsaa.ob_number
EOQ

    $count = RunSql( $db, $query );
    PrintMsg ("I","$count old records deleted from nic_saa_link.");
#---------------------------------------------------------------------
# Use original nic_saa_dark table to create nic_saa_link records.
#---------------------------------------------------------------------

$query = <<"EOQ";
INSERT INTO nic_saa_link
SELECT  s.program_id, s.obset_id, s.ob_number, d.program_id, d.obset_id,
 d.member_num, ("N"+d.program_id+d.obset_id+d.member_num)
FROM #nicsaa s, nic_saa_dark d
WHERE s.targname != "POST-SAA-DARK" and
      d.saa_exit_hour = SUBSTRING(s.saa_exit,1,11) and
      d.config = s.config
EOQ
    $count = RunSql( $db, $query );
    PrintMsg("I","$count records inserted into nic_saa_link.");
#---------------------------------------------------------------------
#  We are done with temporary tables
#---------------------------------------------------------------------

$query = <<"EOQ";
DROP TABLE #obslist
DROP TABLE #nicsaa
EOQ

    RunSql( $db, $query );
#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    $db->dbclose;
    PrintMsg ("I","---  end  --- Regenerate NIC_SAA Table Records ----------");