#!/usr/bin/perl 
#
# Script which performs a mass-update of the ATS tables, effectively
# closing all open accounts.
#
# State of ATS_WRACCT as of 3/19/02:
#  Name                                      Null?    Type
# ----------------------------------------- -------- ---------------------------
# WRACCT_ID                                 NOT NULL VARCHAR2(9)
# WRACCT_ENTITY_IND                         NOT NULL VARCHAR2(1)
# WRACCT_USERID                             NOT NULL VARCHAR2(40)
# WRACCT_RESOURCE                           NOT NULL VARCHAR2(30)
# WRACCT_GROUP_IND                          NOT NULL VARCHAR2(1)
# WRACCT_SOURCE                             NOT NULL VARCHAR2(12)
# WRACCT_ACTION_DATE                        NOT NULL DATE
# WRACCT_ACTION_CODE                        NOT NULL VARCHAR2(6)
# WRACCT_ACTION_REASON                               VARCHAR2(250)
# WRACCT_END_DATE                                    DATE
# WRACCT_SPON_ID                                     VARCHAR2(9)
# WRACCT_SPON_ENTITY                                 VARCHAR2(1)

use Env;		# For ORACLE_SID env variable
use DBI;		# Database Connection module
use DBD::Oracle;	# Specific Oracle DB driver

# Debug flag
$debug = 1;

# Live run flag
# Change to 1 when ready to run
$live = 0;

my $table_name = "ATS_WRACCT";

# This SQL query returns all unique IDS from the table
my $all_acct = "SELECT UNIQUE WRACCT_USERID FROM $table_name \
		WHERE (WRACCT_RESOURCE = 'LOGIN' OR \
		       WRACCT_RESOURCE = 'TEMP.LOGIN')";


# This SQL query returns the last action code of the passed ID from the table
# The subquery selects the last record entered
my $open_acct = "SELECT WRACCT_ACTION_CODE, WRACCT_ID, WRACCT_USERID, \
		WRACCT_GROUP_IND  FROM $table_name \
		WHERE WRACCT_RESOURCE = ? AND \
      		WRACCT_ACTION_DATE = ( \
       		   SELECT MAX(WRACCT_ACTION_DATE) FROM $table_name \
		   WHERE WRACCT_RESOURCE = ? AND \
                	 WRACCT_USERID = ? \
      		) AND \
      		WRACCT_USERID = ?"; 

# This SQL query inserts a new record into the table
my $update_acct = "INSERT INTO $table_name (WRACCT_ID, WRACCT_ENTITY_IND, 
				WRACCT_USERID, WRACCT_RESOURCE, 
				WRACCT_GROUP_IND, WRACCT_SOURCE, 
				WRACCT_ACTION_DATE, WRACCT_ACTION_CODE, 
				WRACCT_ACTION_REASON, WRACCT_END_DATE, 
				WRACCT_SPON_ID, WRACCT_SPON_ENTITY)
		VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

# Temporary storage
my @ids = ();
my @temp_open_ids = ();
my @open_ids = ();

# Connect and get a database handle
$dbh = &db_connect($debug);

# Get all unique IDs from the DB

$sth = $dbh->prepare("$all_acct");
$sth->execute();
while (@row = $sth->fetchrow_array) {
   push(@ids, $row[0]);
}
$sth->finish;

print "Got $#ids IDS\n" unless (!$debug);

# Get all the open BLUEBIRD IDs and put them in a new list
@temp_open_ids = &get_open_ids($dbh, 'TEMP.LOGIN', @ids);

# Get all the open OSPREY IDs and put them in a new list
@open_ids = &get_open_ids($dbh, 'LOGIN', @ids);

print "Got ".($#open_ids+$#temp_open_ids)." open IDS (".
"normal: $#open_ids temp: $#temp_open_ids)\n" unless (!$debug);

# Now that we have the list of userids, close them
#
if (&close_accts($dbh, 'TEMP.LOGIN', @temp_open_ids) ||
    &close_accts($dbh, 'LOGIN', @open_ids)) {

   print ERR "An error occurred.  Rolling back changes...\n";
   $dbh->rollback;
   $dbh->disconnect;

   exit(1);
}

$dbh->commit unless (!$live);
$dbh->disconnect;

exit(0);



# Get a list of all open IDS for a given resource
#
# Args:    database handle, resource name, list of unique IDs
# Returns: list of UserID, VID, and Group Indicator
#
sub get_open_ids($$@) {

   my ($dbh,$resource,@ids) = @_;
   my @open_ids = ();

   # Get all the open IDs and put them in a new list
   $sth = $dbh->prepare("$open_acct");

   foreach $id (@ids) {
      $sth->execute($resource,$resource,$id, $id);
      my @row = $sth->fetchrow_array;
      if ($row[0] eq 'OPEN') { 
         push(@open_ids, join('!',($row[2],$row[1],$row[3]))); 
      }
   }

   $sth->finish;

   return @open_ids;
}

# Closes the given list of accounts with the current date and current userid
#
# Args:     database handle, resource name, list of IDs
# Returns:  -1 if there is an error, 0 if none
#
sub close_accts($$@) {

   my ($dbh, $resource, @ids) = @_;
   my $error = 0;

   # Do the update for each ID in the list
   $sth = $dbh->prepare("$update_acct") unless (!$live);

   open(DEBUG,">>debug") unless (!$debug);
   
   foreach (@ids) {

      ($uid,$vid,$gind) = split(/!/);

      # check the Entity indicator
      if ($vid =~ /^V/) { $ind = 'U'; } else { $ind = 'S'; }

      # do the update.  Args are order of the table columns
      $rv = $sth->execute($vid, 			# WRACCT_ID
			  $ind, 			# WRACCT_ENTITY_IND
			  $uid, 			# WRACCT_USERID
			  $resource, 			# WRACCT_RESOURCE
			  $gind, 			# WRACCT_GROUP_IND
			  $ENV{'LOGNAME'},		# WRACCT_SOURCE
			  '22-MAR-02', 			# WRACCT_ACTION_DATE 
			  'CLOSE', 			# WRACCT_ACTION_CODE
		 	  'Migration to new ERP',	# WRACCT_ACTION_REASON
		 	  null, 			# WRACCT_END_DATE
			  null, 			# WRACCT_SPON_ID
			  null) 			# WRACCT_SPON_ENTITY
      unless (!$live);


      printf DEBUG "%s:%s:%s:%s:%s:%s:%s:%s:%s\n",$vid,$ind,$uid,$resource,
             $gind,$ENV{'LOGNAME'},'22-MAR-02','CLOSE','Migration to new ERP'
      unless(!$debug);

      # execute() for non-SELECT statements returns the number of 
      # rows affected by the execute.
      # If no rows are affected, execute() returns "0E0"
      # If the rows that are affected are not known, execute() returns "0E0"
      if ($rv eq "0E0" || $rv == -1) { 
         print "Error updating $id!\n"; 
         $error++;
      }
   }

   close(DEBUG) unless (!$debug);

   $sth->finish;

   if ($error) { return -1; }
   else { return 0; }

}
   

# Generic Oracle DB connection sub
#
# Args:  verbose, hostname, portnumber
#
sub db_connect {
  local($verbose,$host,$port) = (@_);

  my $username;
  my $password;
  my $dbname = "$ENV{ORACLE_SID}";

  print "Enter username: ";
  $username = <STDIN>;  chomp($username);
  print "Enter username: ";
  system('stty','-echo');
  $password = <STDIN>;  chomp($password);
  system('stty','echo');

  $host = "test.example.com" unless defined($host);
  $port = 1522 unless defined($port);

  if($verbose){print "\nConnecting to database ($host:$port) $dbname\n";}

  if (!($dbh = DBI->connect('dbi:Oracle:',
                             qq($username/$password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port))(CONNECT_DATA=(SID=$dbname)))),
                             undef,{ AutoCommit => 0 }
                           ))){
    print "Error: Could not connect to the database server: $DBI::errstr\n";
  die; }

  return $dbh;
}