##########################################################################
#
# routine: db_asn_members_collected.pl
#
# Purpose: Look up the collected members of an ASN in the asn_members
#          relation.   Return a list of the member 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:  @members - list of 8-character member names of collected ASN members
#
# Return:  Number of collected members in asn_members for this ASN
#          or empty list if no collected members or db error
#
# Calls:   PrintMsg
#
# modification history:
#
#   date    opr     who     reason
# -------- -----  --------  --------------------------------------
# 09/03/00 43165  MSwam     first version
# 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_members_collected {
    my ($ASN_ID) = @_;                           # ARGUMENTS from @_
    my ($db, $query, @members, $instr);  # LOCAL VARS
    my ($program_id, $obset_id, $member_num);
    my $EXIT_FAILURE = 1;

    # initialize as if failed
    @members = ();

    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 = 'C'
EOQ
    PrintMsg("D","searching for collected ASN members 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(@members, $instr . $program_id . $obset_id . $member_num);
    }
    DoDBIclose($db);
    @members;   # return value
}
1;