#-------------------------------------------------------------------------------
#  routine: DumpTempTable
#
#  purpose: When verbosity is on, dump the temp tables to a file and print
#           out the query used to create them.  This helps in debugging.
#
#           Output file will be uniquely named:
#             <PID>_tmp_<temp_tbl_name>.out_<SMS_ID>
#
#           Added SMS_ID to unique output name so that differentiating test
#           output from tests with multiple SMS-es was easier.
#
#           Decided to put output files in a subdirectory of current dir, 
#           because they can become numerous over time, especially when 
#           testing with multiple SMS-es, and a local subdir is easy to 
#           find, and this way I can continue with the unique name I devised.
#
#  Input:  $tbl, name of temp table to dump w/o initial pound sign as that
#                is already included in SELECT statement below
#          $db, database containing temp table
#          $sms_id, used in output file name
#          $tblQuery, the query used to create the temp table
#          $verbose, 0 or 1 
#
#  Return: None (prints information and creates a file)
#
# 06/30/10 64432    MSwam     Use single quotes for SQLServer
#-------------------------------------------------------------------------------
sub DumpTempTable {
    my ( $tbl, $db, $sms_id, $tblQuery, $verbose ) = @_;

    ## No need to do anything if verbose not requested
    if ( ! $verbose ) { return; }

    ## Use this to tell user where file is because in pipeline or automated 
    ## tests, current directory location may not be so obvious.
    ## Just hope I do not run into any pathname length limits.
    use Cwd;
    $curDir = cwd();

    my $tmpDir = $curDir . "/" . "TmpTblDir";
    if ( ! -e $tmpDir ) {
        mkdir $tmpDir ;
    }
    my $file   = $tmpDir . "/" . $$ . "_tmp_" . $tbl . ".out_" . $sms_id;

    if ( !open( FILE, ">$file" ) ) {
        die "ERROR: cannot open file $file $! \n";
    } 
    else {
        if ($verbose) { print "## Opened $file \n"; }
    }

    my $query = <<"EOQ";
SELECT * FROM #$tbl
EOQ

    $msg = "Error dumping #$tbl\n";
    $sth = DoDBIexecute( $db, $query);
    ## Make sure first record gets into tmp table output file
    ## Write rest of tmp table to output file
    while ( @dumpMe = DoDBIfetch( $db, $query, $sth ) ) {
        print FILE join( "\t", @dumpMe ), "\n";
    }
    close(FILE);
    ## DoDBIclose($db);

    if ( $verbose ) { 
        print "\n------------\n"; 
        print "## Output sent to $file \n"; 
        print $tblQuery;
    }

}

#-------------------------------------------------------------------------------
#---  compute planned exposure times
#-------------------------------------------------------------------------------
sub ComputeTimes {
    my ( $inst, $pred_start, $opmode, $exptime ) = @_;

    # local declarations
    my ( $exp_sec, $exp_min, $exp_hour, $exp_day );
    my ( $second, $minute, $hour, $day, $year );
    my $new_time;
    my ( $start_time, $end_time );

    # Assume exposure time can be longer than a day but not a year
    if (   $inst eq "O"
        && substr( $opmode, 0, 3 ) eq "ACQ"
        && substr( $opmode, 3, 1 ) ne "/" )
    {

        # this is a STIS ACQ exposure
        $exp_sec = int( 230.0 + 4.0 * $exptime );
    } else {

        # this is an ordinary exposure
        $exp_sec = int($exptime);
    }
    if ( $exp_sec == 0 ) {
        $start_time = $pred_start;
        $end_time   = $pred_start;
    } else {
        my ( $year, $day, $hour, $minute, $second ) =
          ( $pred_start =~ /(\d+)\.(\d+):(\d+):(\d+):(\d+)/ );
        $exp_day  = int( $exp_sec / 86400 );
        $exp_sec  = $exp_sec % 86400;
        $exp_hour = int( $exp_sec / 3600 );
        $exp_sec  = $exp_sec % 3600;
        $exp_min  = int( $exp_sec / 60 );
        $exp_sec  = $exp_sec % 60;

        if ( $inst eq "U" ) {

            # for WFII, the predicted time is an end time - subtract exp times
            $day    -= $exp_day;
            $hour   -= $exp_hour;
            $minute -= $exp_min;
            $second -= $exp_sec;
            if ( $second < 0 ) {
                $second += 60;
                $minute -= 1;
            }
            if ( $minute < 0 ) {
                $minute += 60;
                $hour -= 1;
            }
            if ( $hour < 0 ) {
                $hour += 24;
                $day -= 1;
            }
            if ( $day < 1 ) {
                $day += 365;
                $year -= 1;
                if ( ( $year % 4 ) == 0 ) {
                    $day += 1;
                }
            }
        } else {

            # not WFII - add times
            $day    += $exp_day;
            $hour   += $exp_hour;
            $minute += $exp_min;
            $second += $exp_sec;
            if ( $second > 59 ) {
                $second -= 60;
                $minute += 1;
            }
            if ( $minute > 59 ) {
                $minute -= 60;
                $hour += 1;
            }
            if ( $hour > 23 ) {
                $hour -= 24;
                $day += 1;
            }
            if ( $day > 365 ) {
                if ( ( $year % 4 ) == 0 ) {
                    if ( $day > 366 ) {
                        $day -= 366;
                        $year += 1;
                    }
                } else {
                    $day -= 365;
                    $year += 1;
                }
            }
        }
        $new_time =
          sprintf( "%d.%03d:%02d:%02d:%02d", $year, $day, $hour, $minute, $second );

        if ( $inst eq "U" ) {

            # WFII, the predicted time is an end time
            $start_time = $new_time;
            $end_time   = $pred_start;
        } else {
            $start_time = $pred_start;
            $end_time   = $new_time;
        }
    }
    ( $start_time, $end_time );
}

#-------------------------------------------------------------------------------
# routine: create_qls_DeleteQuery
#
# purpose: Create query to delete from qolink_sms records old SI
#          product records for input si_name (COS and STIS only
#          evidently) precautionary only - no report line (remove this
#          and previous comment and block) (in fact, if decide on NO
#          report line, then ok to combine two queries)
#
# Input:   $si_name, eg. COS or STIS
#          $verbose, 0 or 1 
#
# Return:  $query
#-------------------------------------------------------------------------------
sub create_qls_DeleteQuery {
    my $si_name = $_[0] ;
    my $verbose = $_[1];

    my $query = <<"EOQ";
DELETE qolink_sms
 FROM qolink_sms, #new_asn_list
WHERE #new_asn_list.si_name = '$si_name' 
  AND qolink_sms.program_id = #new_asn_list.program_id 
  AND qolink_sms.obset_id   = SUBSTRING(#new_asn_list.association_id, 5, 2) 
  AND qolink_sms.ob_number  = SUBSTRING(#new_asn_list.association_id, 7, 3)
EOQ

    if ( $verbose ) { print "\n------------\n", $query; }

    return $query;
}

#-------------------------------------------------------------------------------
# routine: count_si_in_tbl_Query
# 
# purpose: Create a query to count records in a table by instrument. 
#          It seemed better to use just one query to create the temp table 
#          for all science instruments, and then use this query to get 
#          the counts because otherwise we need one large query to create 
#          the table for one SI then another just as large query to append 
#          other SIs to it. 
#
#          In order to use SELECT COUNT(*), query must be caled with SelectSql.
#          RunSql works with SELECT * because RunSql only returns a count. 
#          If you use RunSql with SELECT COUNT(*) you get an infinite loop of 
#          error messages like this:
#     "Attempt to initiate a new SQL Server operation with results pending."
#
#  Input:  $inst_id, eg. N, J, or I for NIC, ACS, or WF3
#          $relation, relation from which to obtain count 
#          $verbose, 0 or 1 
#
#  Return: $query
#-------------------------------------------------------------------------------
sub count_si_in_tbl_Query {
    my $inst_id  = $_[0];
    my $relation = $_[1];
    my $verbose  = $_[2];

    my $query = <<"EOQ";
SELECT COUNT(*) 
  FROM $relation  
 WHERE inst = '$inst_id' 
EOQ

    if ( $verbose ) {
        print STDOUT "\n## EXPECT query from count subroutine to contain "
                     . "inst=$inst_id: \n $query";
    }
    return $query;
}

#-------------------------------------------------------------------------------
# routine: count_si_in_tbl_QueryS 
# 
# purpose: Create a query to count records in a temporary table by si_id 
#          instead of instrument. 
#
#          In order to use SELECT COUNT(*), query must be caled with SelectSql.
#          RunSql works with SELECT * because RunSql only returns a count. 
#          If you use RunSql with SELECT COUNT(*) you get an infinite loop of 
#          error messages like this:
#      "Attempt to initiate a new SQL Server operation with results pending."
#
#  Input:  $si_id, science instrument name, eg. NIC, ACS, WFC3, etc. 
#          $relation, relation from which to obtain count 
#          $verbose, 0 or 1 
#
#  Return: $query
#-------------------------------------------------------------------------------
sub count_si_in_tbl_QueryS {
    my $si_id    = $_[0];
    my $relation = $_[1];
    my $verbose  = $_[2];

    my $query = <<"EOQ";
SELECT COUNT(*) 
  FROM $relation  
 WHERE si_id = '$si_id' 
EOQ

    if ( $verbose ) {
        print STDOUT "\n## EXPECT query from count subroutine to contain "
                     . "si_id=$si_id: \n $query";
    }
    return $query;
}

#-------------------------------------------------------------------------------
# routine: create_qls_InsertQuery 
# 
# purpose: Create the query to insert the product records into qolink_sms
#          by specific instrument so a count can be obtained.  
#
# Input:   $inst_id, eg. N, J, or I for NIC, ACS, or WF3
#          $si_name, eg. NIC, ACS, WF3 (?), STIS, or COS
#          (si_name only needed for COS and STIS but easier to 
#                    pass it than to compute it)
#          $SMS_ID
#          $verbose, 0 or 1 
#
# Return:  $query
#-------------------------------------------------------------------------------
sub create_qls_InsertQuery {
    my $inst_id = $_[0];
    my $si_name = $_[1];
    my $sms_id  = $_[2];
    my $verbose = $_[3];

    my $query_easy = <<"EOQ";
INSERT INTO qolink_sms 
SELECT program_id, obset_id, ob_number, '$sms_id', 'U', inst,
  ' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' '
  FROM #nic_acs_prod 
 WHERE inst = '$inst_id'
EOQ

    my $query_hard = <<"EOQ";
INSERT INTO qolink_sms 
SELECT program_id,
       SUBSTRING(#new_asn_list.association_id, 5, 2),
       SUBSTRING(#new_asn_list.association_id, 7, 3),
       '$sms_id',
       'U','$inst_id',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' '
  FROM #new_asn_list
 WHERE si_name = '$si_name' 
EOQ

    if ( $inst_id !~ 'O' && $inst_id !~ 'L' ) {
        ## ACS (J), NIC (N), or WF3 (I)  use query_easy
        $query = $query_easy ;
    }
    else {
        ## inst_id indicates STIS (O) or COS (L) use query_hard
        $query = $query_hard ;
    }

    if ( $verbose ) {
        print STDOUT "\n## EXPECT query from qls subroutine to contain "
                     . "inst=$inst_id: \n $query";
    }

    return $query;

}

#-------------------------------------------------------------------------------
# routine: create_asm_InsertQuery
# 
# purpose: Create query to insert the product records into asn_members.
#          COS query even more complex than STIS, not subroutined yet.
#
# Input:   $inst_id, eg. N, J, or I for NIC, ACS, or WF3
#          $si_name, eg. NIC, ACS, WF3 (?), STIS, or COS
#          (si_name only needed for COS and STIS but easier to 
#                    pass it than to compute it)
#          $product, PRODUCT for STIS, PROD-FP or PROD-RPT for COS
#                    just pass a NULL for rest of SIs ('')
#          $verbose, 0 or 1 
#
# Return:  $query
#-------------------------------------------------------------------------------
sub create_asm_InsertQuery {
    my $inst_id = $_[0];
    my $si_name = $_[1];
    my $product = $_[2];
    my $verbose = $_[3];

    ## Could replace different parts of queries with variables assigned
    ## by inst_id, but that obfuscates the overall query.  Think this is
    ## clearer.

    my $query_common = <<"EOQ";
INSERT INTO asn_members (
  association_id, 
  program_id, 
  obset_id, 
  member_num,
  member_type, 
  member_status, 
  product_status
) 
EOQ

    my $query_easy = <<"EOQ";
SELECT association_id, 
       program_id, 
       obset_id, 
       ob_number, 
       member_type, 
       'P', 
       'U' 
  FROM #nic_acs_prod
 WHERE inst = '$inst_id'
EOQ

    my $query_hard = <<"EOQ";
SELECT n.association_id, 
       n.program_id,
       SUBSTRING(n.association_id, 5, 2),
       SUBSTRING(n.association_id, 7, 3), 
       '$product', 
       'P', 
       'U' 
EOQ

    my $query_hard_from = <<"EOQ";
  FROM #new_asn_list n
 WHERE n.si_name = '$si_name'
EOQ

    ## COS query much more complicated
    my $exposure = '';
    if ( $si_name eq 'COS' ) {
        my @dummy = split /-/, $product;
        $exposure = 'EXP-' . $dummy[1];
    }
    my $query_hard_from_cos = <<"EOQ";
  FROM #new_asn_list n, #datasets d
 WHERE n.si_name = 'COS' 
   AND d.collect = 'Y' 
   AND d.association_id = n.association_id 
   AND d.exp_type = '$exposure'
GROUP BY n.program_id, n.association_id, n.si_name, d.exp_type
EOQ

    if ( $inst_id !~ 'O' && $inst_id !~ 'L' ) {
        ## ACS (J), NIC (N), or WF3 (I)  use query_easy
        $query = $query_common . $query_easy ;
    }
    elsif ( $inst_id =~ 'O' ) {
        ## inst_id indicates STIS (O) use query_hard
        $query = $query_common . $query_hard . $query_hard_from ;
    }
    elsif ( $inst_id =~ 'L' ) {
        $query = $query_common . $query_hard . $query_hard_from_cos ;
    }

    if ( $verbose ) {
        print STDOUT "\n## EXPECT query from asm subroutine to "
                     . "contain inst=$inst_id: \n $query";
    }

    return $query;
}

#-------------------------------------------------------------------------------
# routine: create_apl_InsertQuery 
# 
# purpose: Create a query to insert any records into asn_product_link
#          There are two sets of queries that differ slightly in where the
#          information being inserted comes from.  STIS and COS do it one
#          way (harder), and the other SIs go the other (easier-on-the-eye) 
#          way.  
# 
#          The only catch is dither products which are additional,
#          and therefore borrow FROM a different table (#acs_dither rather
#          than the earlier used #nic_acs_prod)... and differ ever so
#          slightly in the AND parts of the WHERE statement.
#
# Input:   $inst_id, eg. N, J, or I for NIC, ACS, or WF3, eg.
#          $si_name, eg. NIC, ACS, WFC3, STIS, or COS
#          (si_name only needed for COS and STIS but easier to 
#                    pass it than to compute it)
#          $dth_flg, Y or N -- use or do not use the query for dither ASNs
#          $verbose, 0 or 1 
#
# Return:  $query
#-------------------------------------------------------------------------------
sub create_apl_InsertQuery {
    my $inst_id = $_[0];
    my $si_name = $_[1];
    my $dth_flg = $_[2];
    my $verbose = $_[3];

    $query_common = <<"EOQ";
INSERT INTO asn_product_link (
  program_id, 
  asn_obset_id, 
  member_num,
  obset_id, 
  ob_number
)
EOQ

    $query_easy = <<"EOQ";
SELECT n.program_id, 
       n.obset_id, 
       n.ob_number, 
       m.obset_id, 
       m.member_num
  FROM #nic_acs_prod n, asn_members m
WHERE m.association_id = n.association_id 
  AND m.member_type    = n.exp_type
  AND n.inst = '$inst_id'
EOQ

    $query_dth = <<"EOQ";
SELECT n.program_id, 
       n.obset_id, 
       n.ob_number, 
       m.obset_id, 
       m.member_num
  FROM #acs_dither n, asn_members m
WHERE m.association_id = n.association_id 
  AND m.member_status != 'P'
  AND n.inst = '$inst_id'
EOQ

    my $query_hard = <<"EOQ";
SELECT #new_asn_list.program_id,
       SUBSTRING(asn_members.association_id, 5, 2),
       SUBSTRING(asn_members.association_id, 7, 3), 
       asn_members.obset_id,
       asn_members.member_num
  FROM #new_asn_list, asn_members
 WHERE #new_asn_list.si_name = '$si_name' 
   AND asn_members.association_id = #new_asn_list.association_id 
   AND asn_members.member_status='U'
EOQ

    if ( $inst_id !~ 'O' && $inst_id !~ 'L' ) {
        ## ACS (J), NIC (N), or WF3 (I)  use query_easy
        if ( $dth_flg =~ 'N' ) {
            $query = $query_common . $query_easy ;
        }
        else {
            $query = $query_common . $query_dth ;
        }
    }
    else {
        ## inst_id indicates STIS (O) or COS (L) use query_hard
        $query = $query_common . $query_hard ;
    }

    if ( $verbose ) {
        print STDOUT "\n## EXPECT query from apl subroutine to "
                     . "contain inst=$inst_id: \n $query";
    }

    return $query;
}


#-------------------------------------------------------------------------------
# routine: create_prt_UpdateQuery 
# 
# purpose: Create a query to 
#          update times for association products using asn_product_link.
#
#         "Create temporary table containing minimum start and maximum end 
#          times over all exposures for each product for all associations 
#          in both new_asn_list and old_asn_list"
# 
#          There are two sets of queries that differ a bit depending on 
#          if the SMS is a replan or not.  Replans have an extra query
#          tacked on.
#
# Input:   $replan, 0 or 1 
#                   1: yes, SMS is a replan
#                   0: not a replan, this is a new SMS
#          $verbose, 0 or 1 
#
# Return:  $query
#-------------------------------------------------------------------------------
sub create_prt_UpdateQuery {
    my $replan  = $_[0];   
    my $verbose = $_[1];   

    my $main_query = <<"EOQ";
SELECT p.program_id,
       p.asn_obset_id,
       p.member_num,
       prod_start = MIN(le.start_time), 
       prod_end   = MAX(le.end_time)
  INTO #prod_times 
  FROM #new_asn_list, asn_product_link p, qolink_sms le
 WHERE p.program_id   = SUBSTRING(#new_asn_list.association_id, 2, 3) 
   AND p.asn_obset_id = SUBSTRING(#new_asn_list.association_id, 5, 2) 
   AND le.program_id  = p.program_id 
   AND le.obset_id    = p.obset_id 
   AND le.ob_number   = p.ob_number 
   AND le.start_time != ' '
GROUP BY p.program_id, p.asn_obset_id, p.member_num
EOQ

    my $union_query = <<"EOQ";
UNION
SELECT p.program_id,
       p.asn_obset_id,
       p.member_num,
       prod_start = MIN(le.start_time), 
       prod_end   = MAX(le.end_time)
  FROM #old_asn_list, asn_product_link p, qolink_sms le
 WHERE p.program_id   = SUBSTRING(#old_asn_list.association_id, 2, 3) 
   AND p.asn_obset_id = SUBSTRING(#old_asn_list.association_id, 5, 2) 
   AND le.program_id  = p.program_id 
   AND le.obset_id    = p.obset_id 
   AND le.ob_number   = p.ob_number 
   AND le.start_time != ' '
GROUP BY p.program_id, p.asn_obset_id, p.member_num
EOQ

    if ( $replan ) {
        $query = $main_query . $union_query ;
    } else {
        $query = $main_query ;
    }

    if ( $verbose ) {
        ## DumpTempTable is being called right after this
        ## so do not print query here:  it will.
        print STDOUT "\n## EXPECT query from prt subroutine to "
                     . "contain UNION if replan=1.  replan is "
                     . "$replan. \n";
    }

    return $query;
}
1;