#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: gen_observe_list
#
# This perl script creates a text file containing a list of qobservation
# keys - concatenated without spaces - program_id, obset_id, and ob_number,
# for the time range of pred_strt_tm that is consistent with the time range
# of the SMS data that is found in the sms_catalog table.
#
#
# Interactive Usage:
#	See $usage variable below.
#
# Exit status ( 0 - OK , 1 - error)
#
# History:
# Date     OPR     Who         Reason
# -------- ------- ----------  ---------------------------------------------
# 12/13/04 48735   Baum        Initial code
# 06/30/10 64432   MSwam       Use single quotes for SQLServer
# 02/17/11 67574   MSwam       Adjust query for MSSQL failure
# 09/18/12 72255   Sherbert    get rid of DSQUERY
#----------------------------------------------------------------------------
    use strict;    # require explicit context for all variables (using "my")
    use STScI::DBI;
    use vars qw($opt_s $opt_S $opt_d $opt_o); 
    use Getopt::Std;
    
#   Check for arguments

    my $usage = <<EOM;
Usage:
gen_observe_list -s <sms_id> [-S <server>] [-d database] [-o <outfilename>]

  where <sms_id> is the uppercase id used for sms_catalog records,
        <server> is the database server,
        <database> is the database name for sms_catalog and qobservation,
        <outfilename> is the full specification of the list file.

   The default for <server> is the ENV variable OPUS_SERVER.
   The default for <database> is the ENV variable SPSS_DB.
   The default for <outfilename> is "./<sms_id>.lis".
EOM
    my $num_arg = scalar @ARGV;
    if ($num_arg == 0) {
       print $usage;
       exit(1);
    }
    getopts('s:S:d:o:');
    
    if (!defined($opt_s)) {
       print "Must supply sms_id for option -s.\n$usage";
       exit(1);
    } 
    my $sms_id = $opt_s;
    my $server;
    my $database;
    my $outfile;
    
    if (defined($opt_S)) {
        $server = $opt_S;
    } else {
        $server = $ENV{'OPUS_SERVER'};
    }
    if (!defined($server)) {
        print "Must supply -S option or ENV OPUS_SERVER.\n$usage";
        exit(1);
    }
    if (defined($opt_d)) {
        $database = $opt_d;
    } else {
        $database = $ENV{'SPSS_DB'};
    }
    if (!defined($database)) {
        print "Must supply -d option or ENV SPSS_DB.\n$usage";
        exit(1);
    }
    if (defined($opt_o)) {
        $outfile = $opt_o;
    } else {
        $outfile = "./${sms_id}.lis";
    }
    create_observe_list($sms_id,$server,$database,$outfile);
    exit(0);
#-----------------------------------------------------------------------------
sub create_observe_list { # 4 arguments
    my ($sms_id,$server,$database,$outfile) = @_;
    print "Creating $outfile for SMS $sms_id using $server $database\n";

    if (!open (OUTFILE, ">$outfile")) {
        print "Cannot open $outfile.\n";
        exit(1);
    }
    my $dbh = STScI::DBI->connect( "dbi:Sybase:server=$server");

    if (!defined($dbh)) {
        PrintMsg("E","Cannot connect to server: $DBI::errstr");
        exit(1);
    }
    $dbh->do("use $database");
    
    if ($dbh->err) {
        print "Error opening database: ".$dbh->errstr."\n";
        $dbh->disconnect;
        exit(1);
    }
    my $query =<<EOQ;
select distinct o.program_id+o.obset_id+o.ob_number,
o.program_id,o.obset_id,o.ob_number
from qobservation o, sms_catalog s
where s.sms_id='$sms_id' and o.pred_strt_tm > s.start_time and
   o.pred_strt_tm < s.end_time
order by  o.program_id, o.obset_id, o.ob_number
EOQ
    my $sth = $dbh->prepare($query);
    
    if ($dbh->err) {
        print "Cannot prepare query: $query".$dbh->errstr."\n";
        $dbh->disconnect;
        exit(1);
    }
    $sth->execute;

    if ($sth->err) {
        print "Cannot execute query: $query".$dbh->errstr."\n";
        $dbh->disconnect;
        exit(1);
    }
    my $count;
    while (my ($observation,$dontcare1,$dc2,$dc3) = $sth->fetchrow_array) {
        print OUTFILE "$observation\n";
        $count+=1;
    }
    close OUTFILE;
    $dbh->disconnect;
    
    if ($count == 0) {
        print "No records retrieved from query: $query";
        exit(1);    
    } else {
        print "$count records written to $outfile\n";
    }
}