#!/usr/bin/env perl 
#----------------------------------------------------------------------------
#
# Name: dms_asn_delete.pl
# 
# This perl script is used to delete all database records for a failed
# (invalid) DMS association. The user must provide an association name that
# still exists in the dms_asn_id table. Once this tool is used the 
# dms_asn_insert.pl should not be rerun over the time range that includes this
# deleted association or it will be regenerated.
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 08/24/05 54219    Baum        Initial code 
#----------------------------------------------------------------------------
# set up external routines
unshift @INC,(split /:/, $ENV{PATH});
require 'printmsg.pl';       # prints a formatted status message
require 'runsql.pl';         # run query returning only record count

#specify exit status values

    $PROCESS_FAILURE = 1; 
    $PROCESS_SUCCESS = 0; 

    $usage = <<"EOM";
Usage:
   dms_asn_delete.pl asn_id
   
      where: 
      
      asn_id is 9-character association ID ending in 0.

   This scripts deletes all the related records in the OPUS tables:
   
   dms_asn_id
   qolink_sms
   asn_association
   asn_members
   asn_product_link
EOM

#check for arguments
    $num_arg = scalar @ARGV;
    if ($num_arg != 1) {
       print $usage;
       PrintMsg("E","Invalid arguments - try again.");
       exit ($PROCESS_FAILURE);
    } else {
       $asn_id = uc($ARGV[0]);
    }
    $program_id = substr($asn_id,1,3);
    $obset_id   = substr($asn_id,4,2);
    $member_num = substr($asn_id,6,3);

# get the required external variables

    $DSQUERY=           $ENV{"DSQUERY"};
    $OPUS_DB=           lc($ENV{"OPUS_DB"});

# validate required ENV
    if (!defined($DSQUERY) || !defined($OPUS_DB) ) {
        PrintMsg("E", "DSQUERY and OPUS_DB must be defined.");
        exit ($PROCESS_FAILURE);
    }
# begin processing

    PrintMsg ("I","--- start --- Delete DMS association -------");
    PrintMsg ("I","---  asn_id: $asn_id");
    PrintMsg ("I","--- OPUS_DB: $OPUS_DB");

#---------------------------------------------------------------------
# open database for queries
#---------------------------------------------------------------------
    $db = new ST_DBlib $DSQUERY, $OPUS_DB;
    die if $db == -1;

#---------------------------------------------------------------------
# delete from dms_asn_id and exit if no record deleted
#---------------------------------------------------------------------
    $query = << "EOQ";
DELETE dms_asn_id 
WHERE association_id = "$asn_id" 
EOQ
    
    $count = RunSql( $db, $query );
    PrintMsg("I","$count records deleted from dms_asn_id");

    if ($count == 0) {
        PrintMsg("E","$asn_id was missing from dms_asn_id");
        exit ($PROCESS_FAILURE);
    }

#---------------------------------------------------------------------
# delete qolink_sms for DMS association
#---------------------------------------------------------------------

    $query = << "EOQ";
DELETE qolink_sms
WHERE program_id = "$program_id" and 
      obset_id   = "$obset_id" and
      ob_number  = "$member_num" 
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records deleted from qolink_sms");

#---------------------------------------------------------------------
# delete DMS asn_association
#---------------------------------------------------------------------

    $query = << "EOQ";
DELETE asn_association
WHERE association_id = "$asn_id" 
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records deleted from asn_association");
    
     
#---------------------------------------------------------------------
# delete DMS asn_product_link
#---------------------------------------------------------------------

    $query = << "EOQ";
DELETE asn_product_link
WHERE program_id   = "$program_id" and 
      asn_obset_id = "$obset_id" and
      member_num   = "$member_num" 
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records deleted from asn_product_link");

#---------------------------------------------------------------------
# delete DMS asn_members
#---------------------------------------------------------------------

    $query = << "EOQ";
DELETE asn_members 
WHERE association_id = "$asn_id" 
EOQ

    $count = RunSql( $db, $query );
    PrintMsg("I","$count records deleted from asn_members");
     
#---------------------------------------------------------------------
# end of all queries
#---------------------------------------------------------------------
    $db->dbclose;
    PrintMsg ("I","---  end  --- Delete DMS association -------");
    exit ($PROCESS_SUCCESS);