##########################################################################
#
# routine: db_asn_products.pl
#
# Purpose: Look up the products of an ASN in the asn_members
#          relation.   Return a list of the product names.
#
# Input:   $ASN_ID - name of association to look up members for
#          ENV.OPUS_SERVER - name of DB server
#          ENV.OPUS_DB - name of database containing asn_members
#
# Output:  @products - list of 8-character products names of ASN products
#
# Return:  Number of collected products in asn_members for this ASN
#          or empty list if no products or db error
#
# Calls:   PrintMsg
#
# modification history:
#
#   date    opr     who     reason
# -------- ------ --------  ----------------------------------------------------
# 10/19/04 52106  Sherbert  borrowed from db_asn_members_collected.pl
# 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_asn_products {
    my ($ASN_ID) = @_;                           # ARGUMENTS from @_
    $ASN_ID = uc($ASN_ID);
    my ($db, $query, @products, $instr);  # LOCAL VARS
    my ($program_id, $obset_id, $member_num);

    # initialize as if failed
    @products = ();

    require 'do_dbi_pkg.pl';

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

    $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

    $query = <<"EOQ";
SELECT program_id, obset_id, member_num FROM asn_members
WHERE association_id = '$ASN_ID' and member_status = 'P'
EOQ

    if ($STDB_REPORT_LEVEL) {
      print "\n* * * in DB_asn_products subroutine... \n" ;
      print "\n",$query,"\n" ;
    }

    PrintMsg("D","searching for ASN products for $ASN_ID");
    $instr = substr($ASN_ID,0,1);

    $sth = DoDBIexecute( $db, $query);
    while ( ( $program_id, $obset_id, $member_num ) = DoDBIfetch( $db, $query, $sth) ) {
        PrintMsg("D","found $instr, $program_id, $obset_id, $member_num");
        push(@products, $instr . $program_id . $obset_id . $member_num);
    }

    DoDBIclose($db);
    @products;   # return value
}
1;