#!/usr/bin/env perl
#=======================================================================
#
#  NAME
#
#      	cleanup_dms_asn.pl
#
#  DESCRIPTION
#
#      	Use either the user supplied association id or a filename 
#       containing a list of association ids (one per line)
#       to cleanup the science level part of the database for 
#       the new association's members.
#
#  OPTIONS
#
#	S - Database server name
#	D - DADS Database name
#	O - OPUS Database name
#	U - User name with privileges to delete/insert both dbs
#       t - Test Mode - do not run the deletes/inserts
#	h - Print Usage
#
#  HISTORY
#
#	03/08/06  55419  Lisa Gardner    Initial Release
#	05/25/06  55419  Lisa Gardner    Fixed oms and ads cleanup
#	06/14/06  55419  Lisa Gardner    Reset release date for new asns
#	01/24/07  57374  Lisa Gardner    Do not cleanup PDQ entries
#
#=======================================================================

use strict;

use STScI::DBI;			# Used to automatically login
require 'getopts.pl';		# contains Getopts

#-----------------------------------------------------------------------
#
# Get the DB options and input parameter.  Determine if have filename,
# association id or nothing.  A filename is defined as a word containing
# a ".".
#
#-----------------------------------------------------------------------
use vars ( '$opt_S', '$opt_D', '$opt_O', '$opt_U', '$opt_t', '$opt_h' );
&Getopts('S:D:O:U:th');

my $server   = $opt_S;
my $dadsDb   = $opt_D;
my $opusDb   = $opt_O;
my $user     = $opt_U;
my $test     = $opt_t;

my $inpFileName = '';
my $inpAsnId = '';

if ( !$ARGV[0] ) {
   $test = 1;
} elsif ( $ARGV[0] =~ /\./ ) {
   $inpFileName = $ARGV[0];
} else {
   $inpAsnId = $ARGV[0];
}

#-----------------------------------------------------------------------
#
# Print the usage
#
#-----------------------------------------------------------------------
if ( $opt_h or !$server or !$dadsDb or !$opusDb or !$user ) {
   print "Usage: $0 -S<server> -D<dads database> O<opus database> ",
                 "-U<user> [-t] <association id or filename>\n";
   print "Ex: $0 -SCATLOG -Ddadsops -Oopus -Udadsdba N3T5B1010\n",
         "    or\n",
         "Ex: $0 -SCATLOG -Ddadsops -Oopus -Udadsdba sms_123456.lis\n";
   exit;
}

#-----------------------------------------------------------------------
#
# Connect to database
#
#-----------------------------------------------------------------------
my $dbh = STScI::DBI->connect( "dbi:Sybase:server=$server", $user )
        or die "Can't connect to database: $DBI::errstr";

$dbh->do("use $dadsDb") or die;

$dbh->func("LONG","_date_fmt");

#-----------------------------------------------------------------------
#
# Get the dataset list.  This can be done in the following ways.
# 1. If the asssociation id is supplied use that in the query below
# 2. If a filename is supplied (something with a ".<ext>") then
#    open the file and read in the association ids and run the 
#    query below on each association id
# 3. If nothing is supplied run the query on anything it will match.
#    This will be run in test mode only so the changes will be
#    rolled back.  This is useful if you want to know which datasets
#    have the potential of being affected by the tool.
#
# The code will check if the input parameter is a filename.  If it is
# it will open the file, read the contents and process each association.
# If it's not a file it will assume it's an association and process it.
# If nothing is there it will process all potential associations in test 
# mode.  By processing it I mean fill the hashes with the dataset and
# association information.  The hashes are filled in by sending them
# in as hash references.
#
#-----------------------------------------------------------------------
my %dsnList  = ();	# Info for unique datasets being cleaned out
my %asnList  = ();	# Used to figure out how many asns being processed
my %dsnDiff  = ();	# List of dsn's with differing member names
my $procStis = 0;	# Indicate whether there is stis in the list
my %relDate  = ();	# List of release dates for CAL and OMS

if ( $inpFileName ) {
   open ( INP, "<$inpFileName" ) or die "Couldn't open file $inpFileName $!";
   while ( my $asnId = <INP> ) {
      chop $asnId;
      $asnId =~ s/^\s+//;              # Remove leading spaces
      next if $asnId =~ /^#/g;         # Skip comment lines
      next if $asnId =~ /^\s*$/g;      # Skip blank lines
      my $isStis = GetDsnList ( $dbh, $asnId, \%dsnList, \%asnList, 
                            \%dsnDiff, \%relDate );
      $procStis = 1 if $isStis;    
   }
   close( INP );
} elsif ( $inpAsnId ) {
   $procStis = GetDsnList ( $dbh, $inpAsnId, \%dsnList, \%asnList, 
                            \%dsnDiff, \%relDate );
} else {
   $procStis = GetDsnList ( $dbh, '', \%dsnList, \%asnList, 
                            \%dsnDiff, \%relDate );
}

#-- Get the count of number of unique datasets and associations to be processed.
my $dsnCnt = keys %dsnList;
my $asnCnt = keys %asnList;

#-- Send message and exit script if no datasets to process.
if ( $dsnCnt == 0 ) {
   print "There are no datasets to process - exiting script\n";
   exit;
}

#-----------------------------------------------------------------------
#
# Tables to loop through.  This is divided into 2 sections.  The 
# first is the general hash containing records common to all instruments.
# The second is the instrument specific tables.  One of the instrument
# specific tables will be combined with the general to create a hash
# that will be used to generate the delete statements.  
# As usual Stis is different and needs to have it's instrument level 
# tables cleaned out as well since the dataset name in the instrument
# level tables is now the association id.
#
#-----------------------------------------------------------------------
my %general   = ( 'science','sci', 'sci_inst_db_join','sij_sdb',
                'target_keyword','tak','target_synonym','tsy',
                'fixed_target','fit','moving_target_position','mtp',
                'scan_parameters','scp','oms_summary','oss',
                'oms_data','oms');

my %acs       = ( 'acs_science','acs','acs_ref_data','acr');
my %nicmos    = ( 'nicmos_science','nss','nicmos_ref_data','nsr');
my %stis      = ( 'stis_science','sss','stis_ref_data','ssr' );
my %stisInstr = ( 'stis_a_data','ssa','stis_b_data','ssb', 
                  'stis_c_data','ssc','shp_data','shp' );

my %acsList    = ( %general, %acs );
my %nicmosList = ( %general, %nicmos );
my %stisList   = ( %general, %stis, %stisInstr );

#-- Set up the instrument specific xxx_science and xxx_ref_data tables.
#-- This is used to print the table's totals under it's heading.

my %instrS = ( J => 'acs', N => 'nss', O => 'sss' );
my %instrR = ( J => 'acr', N => 'nsr', O => 'ssr' );
#-----------------------------------------------------------------------
#
# Write the header.  If there is a stis association include all the 
# extra stis tables in the header.   Also create the print line format.
#
#-----------------------------------------------------------------------
print "Running $0 using $server/$user/$dadsDb,$opusDb/$inpFileName\n\n";
print "                ***   CLEANUP DMS ASN MEMBER RECORDS FROM DADS DATABASE   ***\n\n";
if ( $procStis ) {
   print " ASN  ID  Dataset   sci INs INr tak tsy scp fit mtp sij oss oms adl ads afl afi pod shp ssa ssb ssc\n";
   print "--------- --------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---\n";
} else {
   print " ASN  ID  Dataset   sci INs INr tak tsy scp fit mtp sij oss oms adl ads afl afi pod\n";
   print "--------- --------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---\n";
}
my $printFmt = "%-9s %-9s %3s %3s %3s %3s %3s %3s %3s %3s %3s " .
               "%3s %3s %3s %3s %3s %3s %3s %3s %3s %3s %3s\n";


#-----------------------------------------------------------------------
#
# Begin making deletes/inserts/updates for each dataset.  All the
# changes are wrapped in a transaction so they can be rolled back
# if any error is encountered.
#
#-----------------------------------------------------------------------
my %acronym = ();		# Combined hash of tables to loop through
my $instrS = '';		# Name of the instrument specific _science table
my $instrR = '';		# Name of instrument specific _ref_data table
my $errMsg = '';                # Lists any errors found while processing
my $prevAsnId = $inpAsnId;      # Used to print a space between associations
my %relDateAsn = '';            # Whether association ids rel date was changed

$dbh->{AutoCommit} = 0;  # enable transactions, if possible
$dbh->{RaiseError} = 1;

eval {

   #-----------------------------------------------------------------------
   #
   # For each dataset:
   # - Update the release date of the new association
   # - Logically delete the oms and/or stis ADS/AFI records
   # - Cleanout the science records of the association members
   # - Add a podname record if needed for retrieval
   #
   #-----------------------------------------------------------------------
   foreach my $dsn ( sort ByAsnId keys %dsnList ) {

      my %dsnTot = ();

      my $asnId = $dsnList{$dsn};
      my $instr = substr($dsn,0,1);

      #-- Determine which table hash to use in the cleanup for this dataset.
      #-- Write out a message and do no further processing if instrument
      #-- is unknown.

      my %acronym = ();
      if ( $instr eq 'J' ) {
         %acronym = %acsList;
      } elsif ( $instr eq 'N' ) {
         %acronym = %nicmosList;
      } elsif ( $instr eq 'O' ) {
         %acronym = %stisList;
      } else {
         print "$dsn is an unknown instrument - it will not be processed.\n";
         next;
      } 

      #-----------------------------------------------------------------------
      #
      # Reset the members release date.  The association will have it's 
      # release date set when processing the first member of the association.
      #
      #-----------------------------------------------------------------------
      $errMsg .= UpdReleaseDate ( $dbh, $dsn, %relDate );

      if ( !$errMsg and !$relDateAsn{$asnId} ) {
         $errMsg .= UpdReleaseDate ( $dbh, $asnId, %relDate );
         $relDateAsn{$asnId} = 1;
      }

      #-----------------------------------------------------------------------
      #
      # Do the partial logical delete (housekeeping tables only) for the
      # members of the OMS and STIS associations since there will not be 
      # new exposures generated in the housekeeping table.  We do not want 
      # the old exposures to be seen by users.
      #
      #-----------------------------------------------------------------------

      $errMsg .= LogDelEntry( $dbh, $dsn, \%dsnTot );

      #-----------------------------------------------------------------------
      #
      # Cleanout of the science table for the dataset's entries.  For OMS
      # and STIS the exposure level table will also have to be cleaned out.
      # dsnTot (used to keep track of deletes per table) will be filled
      # in in DeleteScience.
      #
      #-----------------------------------------------------------------------
      
      DeleteScience ( $dbh, $dsn, \%dsnTot, %acronym );

      #-----------------------------------------------------------------------
      #
      # If there are no OMS records set the error flag which will 
      # ultimately roll back the transaction.  The database must be
      # kept in sync for CAL and OMS.
      #
      #-----------------------------------------------------------------------
 
      if ( !$dsnTot{oss} or !$dsnTot{oms} ) {
         $errMsg .= "$dsn does not have OMS records\n";
      }

      #-----------------------------------------------------------------------
      #
      # If the dataset name being cleaned out does not match the member
      # name of the newly created association then we *may* have to modify
      # opus's podnames table.  
      #
      #-----------------------------------------------------------------------

      if ( $dsnDiff{$dsn} ) {
         
         #-- This is the name of the ipppssoot needed in podnames
         my $memName = $dsnDiff{$dsn};

         $dsnTot{pod} += InsertPodnames ( $dbh, $dsn, $memName );
      }

      #-----------------------------------------------------------------------
      #
      # Print a line of the total deletes/inserts per table per dataset name
      # Put a blank line between asssocation for ease of reading.
      #
      #-----------------------------------------------------------------------
      if ( $prevAsnId and $prevAsnId ne $asnId ) {
         print "\n";
      }
      $prevAsnId = $asnId;

      my $printLine = sprintf($printFmt, $asnId, $dsn, $dsnTot{sci},
                              $dsnTot{$instrS{$instr}}, 
                              $dsnTot{$instrR{$instr}},
                              $dsnTot{tak}, $dsnTot{tsy}, $dsnTot{scp},
                              $dsnTot{fit}, $dsnTot{mtp}, $dsnTot{sij_sdb},
                              $dsnTot{oss}, $dsnTot{oms},
                              $dsnTot{adl}, $dsnTot{ads}, $dsnTot{afl}, 
                              $dsnTot{afi}, $dsnTot{pod}, $dsnTot{shp}, 
                              $dsnTot{ssa}, $dsnTot{ssb}, $dsnTot{ssc});
      print $printLine;

   }
};
   
#-----------------------------------------------------------------------
#
# If there is a problem or your running a test case - rollback the 
# transaction otherwise commit it.  
# Note: rollback means to undo all inserts/deletes/updates.
#
#-----------------------------------------------------------------------

if ($@) {
   print "\nTransaction aborted because $@\n";
   $dbh->rollback;
} elsif ( $errMsg ) {
   print "\n*** Transaction aborted because:\n$errMsg\n";
   $dbh->rollback;
} else {
   if ( $test ) {
      print "\n*** No changes were actually made\n";
      $dbh->rollback;
   } else {
      $dbh->commit;
   }
}
$dbh->disconnect;

#-----------------------------------------------------------------------
#
# Print totals
#
#-----------------------------------------------------------------------
print "\nNumber of datasets to be cleaned out : $dsnCnt\n";
print "Number of asn_ids  to be cleaned out : $asnCnt\n";

exit;

#=======================================================================
#
# Subroutines
#
#=======================================================================
#-----------------------------------------------------------------------
#
# GetDsnList:
#    Determine the list of associations that need to be cleaned up.
#    To do this you find all the datasets in science that have an
#    asn id of NONE (it was not a member of an association) but has a
#    record in assoc_member (it is a member of an association).
#    Save the list of datasets to be processed.  Save the latest release
#    date of all the members of the association to be used in updating
#    the association's release date.  Save the release date for the
#    member in case it has a record in ADS which needs to be updated.
#    Note:  This query will never return any products since they
#           wouldn't have an asn id of NONE.
#
#
#-----------------------------------------------------------------------
sub GetDsnList
{
   my ( $dbh, $inpAsnId, $dsnList, $asnList, $dsnDiff, $relDate ) = @_;

   my $procStis = '';

   my $query = <<"   EOQ";
   select distinct sci_data_set_name, asm_asn_id, asm_member_name,
          convert(char(10),a.ads_release_date,111) + " " +
          convert(char(8),a.ads_release_date,108), 
          convert(char(10),b.ads_release_date,111) + " " +
          convert(char(8),b.ads_release_date,108)
   from science, assoc_member, archive_data_set_all a, archive_data_set_all b
   where sci_asn_id = 'NONE' and sci_program_id = asm_program_id
   and sci_obset_id = asm_obset_id and sci_obsnum = asm_obsnum
   and a.ads_archive_class = 'CAL'
   and sci_data_set_name = a.ads_data_set_name
   and sci_program_id = b.ads_program_id
   and sci_obset_id = b.ads_obset_id and sci_obsnum = b.ads_obsnum
   and b.ads_archive_class = 'OMS' and b.ads_best_version = 'Y'
   EOQ

   #-- If the user supplied an association id only pull out it's entries.
   if ( $inpAsnId ) {
      $query .= "\nand asm_asn_id = '$inpAsnId'"
   }
   
   my $sth = $dbh->prepare($query) or die "Can't prepare query:" . $dbh->errstr;
             $sth->execute         or die "Can't execute query:" . $dbh->errstr;
   
   my $asnExists = 0;
   while ( my ( $dsn, $asnId, $memName, @classRelDate ) 
                                             = $sth->fetchrow_array ) {
      $asnExists = 1;
      $dsnList->{$dsn} = $asnId;
      $asnList->{$asnId}++;
      $dsnDiff->{$dsn} = $memName if $dsn ne $memName;  
      $procStis = 1 if $dsn =~ /^O/ ;
   
      #-- Save the release date for each dataset and the association.
      my @relClass = ( 'CAL','OMS' );
      for ( my $i = 0; $i <= $#relClass; $i++ ) {
         $relDate->{$dsn}{$relClass[$i]} = $classRelDate[$i];
         if ( $classRelDate[$i] gt $relDate->{$asnId}{$relClass[$i]} ) {
            $relDate->{$asnId}{$relClass[$i]} = $classRelDate[$i];
         }
      }
      #print "$asnId;$dsn;$classRelDate[0]**$relDate->{$asnId}{CAL}**$classRelDate[1]**$relDate->{$asnId}{OMS}**\n";
   }

   if ( $inpAsnId and !$asnExists ) {
      print "'$inpAsnId' does not exist in $server/$dadsDb..assoc_member.\n";
   }
   
   return $procStis;
}

#-----------------------------------------------------------------------
#
# UpdReleaseDate:
#    Updates the ads_release_date and sci_release date for OMS and CAL
#    as needed.
#
#-----------------------------------------------------------------------
sub UpdReleaseDate
{
   my ( $dbh, $dsn, $relDate ) = @_;

   my $query = '';
   my $error = '';

   #-- Need to save the original dataset name to use in the relDate 
   #-- hash but need dsnMatch for the query.
   my $dsnMatch = $dsn;
   $dsnMatch =~ s/[SQNPMTOR]$/[SQNPMTORJ]/;

   #-- Update CAL ADS release date if processing either a 
   #-- product/association or non stis members

   if ( $dsn =~ /^[JN]/ or $dsn =~ /0$/ ) {
      $query = <<"      EOQ";
      update archive_data_set_all set ads_release_date = '$relDate{$dsn}{CAL}'
      where ads_data_set_name like '$dsnMatch' 
      and ads_archive_class in ('CAL','ASN')
      EOQ
      my $adsCnt = SetDbValues( $dbh, $query );
      $error .= "CAL Release date not updated in ADS for $dsn\n" if $adsCnt ==0;
   }

   #-- Only update release dates in SCI or ADS for OMS class for 
   #-- products/associations.  

   if ( $dsn =~ /0$/ ) {
      $query = <<"      EOQ";
      update science set sci_release_date = '$relDate{$dsn}{CAL}'
      where sci_data_set_name like '$dsnMatch'
      EOQ
      my $sciCnt = SetDbValues( $dbh, $query );
      $error .= "CAL Release date not updated in SCI for $dsn\n" if $sciCnt ==0;

      $query = <<"      EOQ";
      update archive_data_set_all set ads_release_date = '$relDate{$dsn}{OMS}'
      where ads_data_set_name like '$dsnMatch' and ads_archive_class in ('OMS')
      EOQ
      my $omsCnt = SetDbValues( $dbh, $query );
      $error .= "OMS Release date not updated in ADS for $dsn\n" if $omsCnt ==0;

   }

   ( $error );
}

#-----------------------------------------------------------------------
#
# LogDelEntry:
#    Logically deletes records from the housekeeping tables for oms 
#    and stis members of the new association.
#
#-----------------------------------------------------------------------
sub LogDelEntry
{
   my ( $dbh, $dsn, $dsnTot ) = @_;

   my $error = '';
   my $acList = '';

   #-- wild card last letter of dataset name so match all versions.
   $dsn =~ s/[SQNPMTOR]$/[SQNPMTORJ]/;

   #-- If it's a stis dataset have to delete out all oms and cal
   #-- exposure records from the housekeeping tables.  For all other
   #-- instruments only delete out the oms since the cal entries will
   #-- have new entries overwriting the old entries.

   if ( $dsn =~ /^O/ ) {
      $acList = "('OMS','CAL')";
   } else {
      $acList = "('OMS')";
   }

   #-- Run the 4 queries on the housekeeping tables. 

   my $query = "insert into archive_data_set_log_del select *, null, null, " .
               "getdate(), 'New Association Cleanup' " .
               "from archive_data_set_all where ads_data_set_name " .
               "like '$dsn' and ads_archive_class in $acList";
   $dsnTot->{adl} = SetDbValues( $dbh, $query );

   $query = "insert into archive_files_log_del " .
            "select * from archive_files where afi_data_set_name " .
            "like '$dsn' and afi_archive_class in $acList";
   $dsnTot->{afl} = SetDbValues( $dbh, $query );

   $query = "delete from archive_data_set_all where " .
            "ads_data_set_name like '$dsn' and ads_archive_class in $acList";
   $dsnTot->{ads} = SetDbValues( $dbh, $query );

   $query = "delete from archive_files where " .
            "afi_data_set_name like '$dsn' and afi_archive_class in $acList";
   $dsnTot->{afi} = SetDbValues( $dbh, $query );

   #-- If get a zero records back there is a problem,
   #-- fill in the error message which will ultimately roll back the queries.

   foreach my $acronym ( 'adl','afl','ads','afi' ) {
      if ( $dsnTot->{$acronym} == 0 ) {
         $error .= "No records deleted from " . uc $acronym . 
                   "for $dsn/$acList\n";
      }
   }

   #-- If the counts from ADS/ADS or AFL/AFI don't match that is an error.
   #-- Since it's basically copying one to the other they must match.  If
   #-- they don't fillin the error message which will rollback the changes.
   
   if ( $dsnTot->{adl} != $dsnTot->{ads} ) {
      $error .= "ADL ($dsnTot->{adl}) doesn't match ADS ($dsnTot->{ads}) " .
                "records processed for $dsn/$acList\n";
   }
   if ( $dsnTot->{afl} != $dsnTot->{afi} ) {
      $error .= "AFL ($dsnTot->{afl}) doesn't match AFI ($dsnTot->{afi}) " .
                "records processed for $dsn/$acList\n";
   }

   ( $error );
}

#-----------------------------------------------------------------------
#
# Subroutine DeleteScience:
#    For each dataset loop through all the science level tables and
#    create delete statements for each one.  We are NOT checking
#    whether the table has records in it or not so zero rows deleted
#    is perfectly legitimate for the optional tables (eg. fixed_target).
#
#-----------------------------------------------------------------------
sub DeleteScience
{
   my ( $dbh, $dsn, $dsnTot, %tblList ) = @_;

   my $ppp = substr($dsn,1,3);
   my $ss = substr($dsn,4,2);
   my $oo = substr($dsn,6,2);

   foreach my $tbl ( sort keys %tblList ) {
      my $delLine = "delete from $tbl\nwhere $tblList{$tbl}_program_id = " .
                    "'$ppp' and $tblList{$tbl}_obset_id = '$ss' " .
                    "and $tblList{$tbl}_obsnum = '$oo'";

      #-- oms has a different transmission source than the science
      #-- and it needs the dataset name specified so that only the
      #-- singletons records will be cleaned out and not the associations
      #-- records.

      if ( $tbl eq 'oms_data' ) {
         my $omsDsn = substr($dsn,0,8) . 'J';
         $delLine .= " and $tblList{$tbl}_data_set_name = '$omsDsn'";
      }

      #-- stis tables need the dataset name specified since wavecals
      #-- can belong to more than one association.

      if ( $stisInstr{$tbl} ) {
         $delLine .= " and $tblList{$tbl}_data_set_name = '$dsn'";
      }
      #print "$delLine\ngo\n";

      #-- Run the delete.  Keep track of number of rows deleted per table.
      my $rowsAffected = SetDbValues( $dbh, $delLine );
      $dsnTot->{$tblList{$tbl}} = $rowsAffected;
   }
}
#-----------------------------------------------------------------------
#
# Subroutine InsertPodname:
#    Determine if need to insert a podnames record for the 
#    dataset name passed in.
#
#-----------------------------------------------------------------------
sub InsertPodnames
{
   my ( $dbh, $dsn, $memName ) = @_;

   my $podCnt = 0;

   #-- Get all records from podnames that match the ipppssoo 
   #-- and save in hash

   my $ipso = substr($dsn,0,8) . "%";
   my $query = "select ipppssoot, podname from $opusDb..podnames " .
               "where ipppssoot like '$ipso'";
   my $sth = $dbh->prepare($query) 
                            or die "Can't prepare query:" . $dbh->errstr;
      $sth->execute         or die "Can't execute query:" . $dbh->errstr;

   my %podInfo = ();
   while ( my ( $ipppssoot, $podName ) = $sth->fetchrow_array ) {
      $podInfo{$ipppssoot}{$podName} = 1;
   }

   #-- If there is not a record in podnames that matches the new
   #-- member name then find each podname associated with the old
   #-- dataset name and create a new record in podnames using
   #-- the new member name.

   if ( !exists $podInfo{$memName} ) {
      foreach my $podName ( sort keys %{$podInfo{$dsn}} ) {
         my $insLine =  "insert into $opusDb..podnames " .
                        "values ('$memName','$podName','NACT')\n";

         my $rowsAffected = SetDbValues( $dbh, $insLine );
         $podCnt += $rowsAffected;
      }
   }
   return $podCnt;
}

#-----------------------------------------------------------------------
#
# ByAsnId:
#   This is a perl sorting routine.  Want to group all datasets
#   belonging to a dms association together.  Within the association
#   sort by the dataset names.
#
#-----------------------------------------------------------------------
sub ByAsnId {
   if ( $dsnList{$a} eq $dsnList{$b} ) {
      $a cmp $b;
   } else {
      $dsnList{$a} cmp $dsnList{$b};
   }
}

#-----------------------------------------------------------------------
#
# Subroutine SetDbValues:
#   Delete, Insert or Update a record to the database
#   DBI returns 0E0 when the number of rows returned is 0.  I want
#   the original 0 so I'm translating it back.
#
#-----------------------------------------------------------------------
sub SetDbValues
{
   my ( $dbh, $query ) = @_;

   my $rowsAffected = '';

   eval {
      $rowsAffected = $dbh->do($query) or die $dbh->errstr;
      $rowsAffected = 0 if $rowsAffected eq "0E0";
   };

   die "Error encountered running query:$query\n$@" if $@;

   return $rowsAffected;
}