##########################################################################
#
# routine: DB_executed_flg.pl
#
# purpose: Searches the 'executed' db relation given an ipppssoot and
#          returns the value of the 'executed_flg' db field.
# 
# Input:   $ipppssoot - name of an exposure
#          ENV.OPUS_SERVER - name of database server
#          ENV.OPUS_DB - name of OPUS database
#
# Returns: value of 'executed_flg' db field for ipppssoot OR
#          "" if failed or not found in db
#
# modification history:
#
#   date    opr     who     reason
# -------- -----  --------  --------------------------------------
# 09/03/00 43165  MSwam     first version
# 06/11/03 47245  MSwam     add print for no match case
# 03/25/10 64274  MSwam     Replace ST_DBlib with DBI
# 06/30/10 64432  MSwam     Use single quotes for SQLServer
# 09/18/12 72255  Sherbert  get rid of DSQUERY
# 
##########################################################################
sub DB_executed_flg
{
    my ($ipppssoot) = @_;              # ARGUMENTS from @_
    my ($db, $query, $exp, $count, $lastch, $tmp,
        $progid, $obset_id, $ob_number);                # LOCAL VARS

    # initialize return value for error
    $lastch = "";

    require 'do_dbi_pkg.pl';

    $server = $ENV{"OPUS_SERVER"};
    $database = lc($ENV{"OPUS_DB"});

    my $db = STScI::DBI->connect( "dbi:Sybase:server=$server");

    if (!defined($db)) {
        PrintMsg("E","Cannot connect to server: $DBI::errstr");
        return;
    }
    DoDBI($db,"use $database"); # open database

    # parse ipppsoot fields
    $exp = $ipppssoot;
    $progid = substr($exp,1,3);
    $obset_id = substr($exp,4,2);
    $ob_number = substr($exp,6,2);

    $query = <<"EOQ";
SELECT executed_flg FROM executed
WHERE program_id = '$progid' AND obset_id = '$obset_id' and
      ob_number = '$ob_number'
EOQ

    $count = 0;

    $sth = DoDBIexecute( $db, $query);
    while ( ( $tmp ) = DoDBIfetch( $db, $query, $sth) ) {
        if ($tmp ne $lastch) {
           PrintMsg("D","DB_executed_flg: $progid$obset_id$ob_number executed_flg = $tmp");
           $lastch = $tmp;
           $count++;
        }
    }
    DoDBIclose($db);

    # if multiple last chars were found then exit with an error
    if ($count > 1) {
        PrintMsg("E","ERROR: DB_executed_flg: Ambiguous executed.executed_flg for $progid$obset_id$ob_number\n");
        $lastch = "";
    }
    elsif ($count == 0) {
        PrintMsg("W","DB_executed_flg: No result matched for query: $query\n");
    }

    # return value
    $lastch;
}
1;