#!/usr/local/bin/perl

###########################################################################################
#											  #
#			       Database ReSynchronisation Tool				  #
#											  #
#			      Multi-Tier Multi-Table Version 5.1			  #
#											  #
#											  #
#  Use requires DBI & DBD modules and BVCustomPerl.pm					  #
#  This is a package that gives us some handy subroutines that are used here.          	  #
#  Namely:										  #
#	  GetDBvars - returns database connection variables as a hash			  #
#	  LogHeader & LogFooter - logging routines					  #
#											  #
######################################### Purpose #########################################
#											  #
#    ReSyncDBs.pl compares data from 2 databases and will detect a mismatch based on the  #
#  OID and primary key ( which this script detects from the 'bv_attributes' table but	  # 
#     can be altered to use any other table containing the attributes of the table you	  #
#     want to synchronise ).								  #
#  The script then attempts to resynchronise the two by updating the live database based  #
#  on the staging database content. 							  #
#  The situation commonly arises when a user performs an insert into the live and staging #
#  instances with the same primary key. Two different oids will be assigned and the 	  #
#  database staging process will subsequently fail when attempting to insert the 	  #
#  'asynchronous' oid.									  #
#    Updating is a tricky business and this script attempts to detect the parent table	  #
#  and can update parent-child-grandchild relationship tables				  #
#											  #
#    This tool was originally developed on a BroadVision environment, but could be 	  #
#  adapted for use on any system with a staging and live database instance.		  #
#    If you do succesfully port it onto other environments please let me know.		  #
#											  #
#	Andy Pritchard             							  #
#											  #
######################################## Revisions ########################################
#											  #
# 5.1 Adapting for deployment to CPAN							  #
# 5.0 Added multi-parent table resynchronisation ability				  #
# 4.6 Added the '-r -all' option. Added some signal trapping to re-enable constraints     #
#     if it all goes pear-shaped. Altered the update pattern-matching to allow us to	  #
#     update names with leading and trailing spaces.					  #
# 4.5 Altered location poper fix method to take account of mtc_xxx_code column names and  #
#     mtc_table names being different eg destination					  #
# 4.4 Had to change that to a union all select so that we get the xml_id column. If it    #
#     exists.										  #
# 4.3 Altering sql to pick up attribute_name from bv_attributes where the attribute_ids   #
#     dont start at zero								  #
# 4.2 Added check to sql to quit in cases where a table doesnt have defined referential   #
#     integrity column - in which case we cant automate resync				  #
# 4.1 Added sort to keys(% routines 							  #
# 4.0 Contains Functionality to resynchronise failures during the location poper	  #
# 3.2 Allows null xml-id resynchronisation to be specified from the command line (pre 5.1)#
# 3.1 Resyncs BV_CATEGORY's								  #
# 3.0 Added functionality to use the creation time for restriction of hundreds of rows	  #
# 2.0 Resyncs parent-child-grandchild relationships					  #
# 1.0 Resyncs parent-child relationships						  #
#											  #
###################################### The Variables ######################################

$OneAtATime = 1; # Set to 0 to resync all the asynchronous content in one.
		 # Set to 1 (default) to require user intervention for each
		 # Now set by the -all command line flag

$StageToolDir = "/export/home/users/ninja/Resync/"; # Directory where staging files sit
$ThisDir = "/export/home/users/ninja/Resync";	    # Script directory
$database_link = "PS01";	# Name of database link between live and staging instance

$Testing = 0; 	## Set to zero or delete when in production mode - Set to 2 to test with a production query
$debug = 0;	## Set to zero or delete when in production mode
$useCreationDate = 0;	# This is altered by the -ct command line flag later.

###################################### The Settings #######################################

$LogFile = "${ThisDir}/${0}.Process.log";
$InputOutputFile_1 = "${ThisDir}/${0}.input_output.I";
$InputOutputFile_2 = "${ThisDir}/${0}.input_output.II";
$constraintlist = "${ThisDir}/${0}.constraints";
$asynchronous_log = "${ThisDir}/${0}.asynchronous_oids.log";
$MultiParentlog = "${ThisDir}/Multiple_Parents.log";

####################################### The Program #######################################

&usage if ($#ARGV < 0);
$command = ${ARGV}[0];

use BVCustomPerl;
use DBI;

my (@Table,%FailedHierarchy,@errorreturn,@Allerrors,@FailedTableList,@location_poper_tables,$ver) = ();
my %DBvars = &BVCustomPerl::GetDBvars();
my ($LatestStgTlLoadLog);
$user = $DBvars{'USER'};
if ($command eq '-e' || $command eq '-f') {
	$LatestStgTlLoadLog = qx!ls -1tr ${StageToolDir}stage_load*.log | tail -1!;
  	chomp($LatestStgTlLoadLog);
}

usage() unless ($command eq '-e' || $command eq '-f' || $command eq '-r' || $command eq '-t' || $command eq '-stage' || $command eq '-test' || $command eq '-clean');

if ($command eq '-e')
{
	@Allerrors = &stageloaderrors;
	print "\n ---------------------------";
        print " Errors from ", $LatestStgTlLoadLog;
        print   " ---------------------------\n\n";
	foreach (@Allerrors)
	{
		print $_, "\n";
	}
exit 0;

}

if ($command eq '-f' || $command eq '-t')
{
	$MethodName = "PS - PL Resynchronisation - Stage I";
}elsif ($command eq '-stage') {
	$MethodName = "Staging Procedure Resynchronisation";
}else{
	$MethodName = "PS - PL Resynchronisation - Stage II";
}
$Progress = $MethodName;
print "\n----------------------------- $Progress -----------------------------\n\n";
&BVCustomPerl::LogHeader($LogFile,$MethodName,$user,scalar(localtime));
$Progress = "Making Database Connection";
print "1. $Progress\n";
$dbh = DBI->connect("dbi:Oracle:host=$DBvars{'HOST'};sid=$DBvars{'SID'}", 
		    $DBvars{'USER'}, $DBvars{'PASSWORD'},
		    { RaiseError => 1, AutoCommit => 0 }) || die $DBI::err;
$exstat = $?;
LogData("Connected to: $DBvars{'DATABASE'}; as: $DBvars{'USER'}",$Progress) if ($exstat == 0);
print "2. Connected\n" if ($exstat == 0);

########################################### STAGE 1 ###########################################

if ($command eq '-f'){

	qx!rm -f "$InputOutputFile_1"! if -e "$InputOutputFile_1";
	qx!rm -f "$InputOutputFile_2"! if -e "$InputOutputFile_2";
	$Progress = "Checking Failures in Load Log";
        print"3. $Progress\n";
        print"Last log is $LatestStgTlLoadLog\n";
	@Allerrors = &stageloaderrors;
	foreach (@Allerrors)
	{
		next unless /not updated\./;
		s/(\w*)\s*not updated\.\s*/$1/;
		push(@FailedTableList,$_);
		print "$_ \t Failed\n";
		LogData("Failed Table :->$_<-",$Progress);
	}
        unless (@FailedTableList)
	{
		$Progress = "Error Log Checked - No errors Found";
		print "$Progress\nNothing to resynchronise.\n";
		QuitCleanly("Nothing to resynchronise. Finshing.",0);
	} 

###### This section iterates through the failed table list
###### and finds the parent, children, and grandchildren if they exist

	$Progress = "Analysing Table Hierarchy for Failed Tables";
        print"3. $Progress\n";
	LogData("Started",$Progress);
	my($status,%FailedHierarchy) = AnalyseHierarchy(@FailedTableList);

	QuitCleanly('',$status);

}elsif($command eq '-t'){

	qx!rm -f "$InputOutputFile_1"! if -e "$InputOutputFile_1";
	qx!rm -f "$InputOutputFile_2"! if -e "$InputOutputFile_2";
	qx!rm -f "$MultiParentlog"! if -e "$MultiParentlog";
	print "\n ! Must Specify a valid Parent Table Name !\n" unless ($ARGV[1]);
	usage() unless ($ARGV[1]);
	@Table_anycase = $ARGV[1];
	foreach (@Table_anycase) { $_ = uc($_); push(@Table,$_); }
	$Progress = "Analysing Table Hierarchy for @Table";
        print"3. $Progress\n";
	LogData("Started",$Progress);
	my($status,%FailedHierarchy) = AnalyseHierarchy(@Table);

	QuitCleanly("Finished Analysis for @Table",$status);

}elsif($command eq '-stage'){

  $Progress = "Investigating Staging Procedure Failure";
  print "3. $Progress\n";
  LogData("Starting Staging Procedure Analysis",$Progress);

  $Progress = "Investigating Location Poper";
  print "4. $Progress\n";
  LogData("Checking Location Poper Failures",$Progress);

  @FailedRows = StagingFailure('locationpoper','find','');

  if (@FailedRows)
  {
    @location_poper_tables = ();
    foreach(0..$#FailedRows)
    {
  	push(@location_poper_tables, $FailedRows[$_]{'table'});
    }
    @location_poper_tables = MergeArrays(@location_poper_tables);

    print "\n------------------------- Location Poper Resynchronisation -------------------------\n\n";
    print "\n\tFailures were found in the location poper log for:\n\t";
    print join("\n\t", @location_poper_tables);
    print "\n\n\tTo resynchronise :\n\n";
    print "\t\t1. Table updates must occur to both Live and Stage database\n";
    print "\t\t2. Each database will be queried to ensure no clashing upon update\n";
    print "\t\t3. Resynchronised will be based on the assumption that\n";
    print "\t\t   the primary key should be of the order:\n";
    foreach (@location_poper_tables)
    {
	print "\t\t   <Tour Operator> - <Holiday Type> - <Season Code> - <Brochure Code> - <${_}_CODE>\n";
    }
    print "\n\t Ready to continue Y/N ?\n\n";
    ExitOnDemand();
	
    $Progress = "Resynchronising Location Poper";
    print "5. $Progress\n";
    LogData("Resynchronising Location Poper Failures",$Progress);

    $status = StagingFailure('locationpoper','fix',@FailedRows);
    print "There was a problem with this process\n" if ($status != 0);
  }
  QuitCleanly("Finished Staging Procedure Analysis",$status);
  &BVCustomPerl::LogFooter($status,$LogFile,$MethodName,$user,$TimeLine);

########################################### STAGE 2 ###########################################

}elsif($command eq '-r'){

   chomp(@ARGV);
   shift(@ARGV); ## Gets rid of the -r
   while(@ARGV)
   {
	$next = shift(@ARGV);
	if($next eq '-ct') {
	  $useCreationDate = shift(@ARGV);
	  $useCreationDate =~ s/^('|")(.*)\1$/$2/;                           #'
          $useCreationDate = uc($useCreationDate);
	  LogData("User specified creation-time to be >= \'$useCreationDate\'",$Progress);
	}elsif($next eq '-all') {
	  $OneAtATime = 0;
	  LogData("User specified resynch of all content without intervention",$Progress);
	}else{
	  QuitCleanly("Incorrect Flag Specified...Aborting",1); 
	}
   }
      
   our ($multi_table_resync,$resync_complete) = 0;

	# need to ensure we dont overwrite an existing disabled constraints log
	if (-e "${constraintlist}.disabled.log"){
	   print "   Found existing\n\t ${constraintlist}.disabled.log\n   cant continue or may overwrite\n";
	   print "\n    This may be because constraints could not be replaced by $0 \n";
	   print "   Or perhaps you did not cleanly exit the program on a previous run.\n";
	   print "\n    It may be safe to simply delete this file to allow the program to continue,\n";
	   print "   but you should check that the constraints listed therein are actually re-enabled before doing so...\n\n";
	QuitCleanly("Aborting since ${constraintlist}.disabled.log exists",1); }
	
	# Check whether this is a multi-parent run #
	if ((-e $MultiParentlog) and (-e $InputOutputFile_2)) {
	  ## Then it probably is #
	  $Progress = "Beginning Multi-Parent Resynchronisation";
	  LogData("Multiple Parent Table Resynchronisation",$Progress);
	  MultiParentHandler('load');
	  $multi_table_resync = 1;	  
	}
	
   RESYNCHRO_LOOP:	
   while (! $resync_complete) {
        our(@DBRefData) = ();
   
        if ($multi_table_resync) { 
          $resync_complete++ unless (MultiParentHandler('reload'));
          last RESYNCHRO_LOOP if ($resync_complete);
          next RESYNCHRO_LOOP if (MultiParentHandler('ask_user'));
        }
  
	$Progress = "Reading Input-Output File";
	print "3. $Progress\n";
	%ResyncTables = ReadIOFile($InputOutputFile_1); ## This makes a hash from the file fed in.
	
	foreach $level (sort keys(%ResyncTables))
	{
		print "Read Input-Output; Level =>$level<= : Value =>$ResyncTables{$level}<= \n" if ($debug);
		LogData("Level =>$level<= : Tables =>$ResyncTables{$level}<=",$Progress);
		$levelcount1 = $level unless ($level < $levelcount1);
	}

	$complexity = 1 if ($ResyncTables{3}) or $complexity = 0;
	if ($ResyncTables{2})
	{ 
		print "\n     *** Complex Table Structure Found ***\n\n";
		LogData("Complex Table Structure Found",$Progress);
	}else{
		print "   - Normal Table Structure Found\n";
                LogData("Normal Table Structure Found",$Progress);
	}

	if ($complexity) { QuitCleanly("Cannot use this script to resync 4-level hierarchies..",1,''); }
	
	if ($ResyncTables{0} =~ /,/) {
		print "\n*** Attempting Multiple Parent Table Resynchronisation ***\n\n";
		LogData("Multiple Parent Table Resynchronisation",$Progress);
		QuitCleanly("Cannot use this script to resync Multiple Parent Tables..",1,'');
	}
	
	$ParentTable = $ResyncTables{0};
	$Progress = "Retreiving database linkage information";
	print "4. $Progress\n";
	LogData("Resynchronisation Information for $ParentTable",$Progress);
      ## Now split this file into hierarchy levels
	foreach $level (sort keys(%ResyncTables))
	{
		@EachLevelTables = split(/,/, $ResyncTables{$level});
      		## Then split the tables up
		foreach (@EachLevelTables)
		{
		    @TableInfo = DBLinkInfo(0,${_}); # This is a hash of a hash
		    foreach $constraint_no ( 0 .. $#TableInfo)
		    {
			$table = $TableInfo[$constraint_no]{'TABLE_NAME'};
			print "level = $level ; table = $table ; constraint_no = $constraint_no\n" if ($debug);
			$DBRefData[$level]{$table}[$constraint_no] = $TableInfo[$constraint_no];
		    }
		}
	}

  ###################################################################################################
  ## 4-Tier Data Structure is as follows:
  ##	DBRefData -> level -> table -> constraint number -> %DBLinkage hash
  ##		   						|
  ##		 Keys :	 TABLE_NAME, CONSTRAINT_TYPE, STATUS, COLUMN_NAME, POSITION, CONSTRAINT_NAME
  ##
  ## Remember. We just created an array of hashes of hashes
  ## Pretty smart yes but v tricky to compose and access
  ## basically its named $blahblah[index]{hash_key}[index2] = "hash_values"
  ## access with keys %{$blahblah[index]}
  ## then values are $blahblah[index]{hash_key}[index2]{hash_key3} - AP
  ##
  ###################################################################################################

	$levelcount = $#DBRefData;
	if ((@DBRefData) and ($levelcount == $levelcount1)) 
	{
		## can assume this was succesful
		$Progress = "Retrieved Database Linkage Information";
		print "5. $Progress Succesfully\n";
		LogData("$Progress Succesfully",$Progress);
		LogData("Produced \@DBRefData Succesfully",$Progress);
	}else{
		$Progress = "Database Linkage Information Retrieval Failed";
		print $Progress, "\n";
		QuitCleanly("Failed to produce \@DBRefData",1);
	}

	HashReader('print',@DBRefData) if ($debug); # Just to show us what we've got so far

	$Progress = "Finding Enabled Constraints for Failed Tables";
	print "6. $Progress\n";
	LogData("Finding Constraints",$Progress); 

	## Find all enabled constraints and write them for comparison later ##
	qx!rm -f "${constraintlist}.enabled.log"! if -e "${constraintlist}.enabled.log";
	open(OUT,">${constraintlist}.enabled.log") or die "Cant write to ${constraintlist}.enabled.log\n";
  	print OUT "# Constraints which need to be turned off to resynchronise:\n";
	close(OUT);


	$query = "STATUS eq ENABLED";
  	@EnabledConstraintList = QueryHash(0,$levelcount,$query,@DBRefData); 
  		# uses format ($startlevel,$endlevel,$query,@hash)

	## Get parent constraint first
	$query = "CONSTRAINT_TYPE eq P";
  	@EnabledPConstraint = QueryHash(0,0,$query,@EnabledConstraintList); 
	LogData("Writing P Key Constraint for Parent",$Progress);
  	WriteHash("file","${constraintlist}.enabled.log",@EnabledPConstraint); 
  		# <type of output> <name of output> <input hash>
	# That should get the parent info - Now for children 
	# We want all constraint types for upper level but only referential for lower

		LogData("Writing R Integrity Constraints for Child Structure",$Progress);
		@EnabledCConstraints = QueryHash(1,$levelcount,"CONSTRAINT_TYPE eq R",@EnabledConstraintList);
  		WriteHash("file","${constraintlist}.enabled.log",@EnabledCConstraints); 

	system("more ${constraintlist}.enabled.log") if ($debug); ## prints constraints.log if you need.
  	chmod(0444,"${constraintlist}.enabled.log");
	$filecheck = qx!/bin/wc -l "${constraintlist}.enabled.log" !;
	$filecheck =~ s/^\s*(\d+).*\n*$/$1/;
	$infilecount += $levelcount1;
	if ( $filecheck < $infilecount ) {
	  QuitCleanly("Failed when checking enabled constraint log ${constraintlist}.enabled.log only $filecheck lines long.",1);
	}
        print "   Succesfully written applicable constraints to ${constraintlist}.enabled.log\n";
        LogData("Succesfully written applicable constraints to ${constraintlist}.enabled.log",$Progress);

	$Progress = "Finding Asynchrosity for Parent";
        print "7. $Progress\n";
        LogData("Investigating Asynchrosity for $ParentTable",$Progress);

	# use $database_link here to find anything thats out
        ### Firstly get it into a file and then we can check this later ###
        LogData("User specified creation_time must be greater than \'$useCreationDate\'",$Progress) if($useCreationDate);
	@InitialAsynchronousOIDs = Asynchrosity(@DBRefData);

	$Progress = "Completed $ParentTable Table and Constraint Analysis";
        print "7. $Progress\n";
        LogData("Asynchronous OIDs Checked for $ParentTable",$Progress);

	if (@InitialAsynchronousOIDs)
	{
		qx!rm -f "$asynchronous_log"! if -e "$asynchronous_log";
		open(ASYNCHRO,">$asynchronous_log") or die "Cant write to $asynchronous_log\n Cant continue\n";
		print ASYNCHRO @InitialAsynchronousOIDs;
		close(ASYNCHRO);
		if ( -s "$asynchronous_log") {
		  print " Found the following OID information out of synchronisation:\n\n";
		  print @InitialAsynchronousOIDs, "\n";
		  #system("more $asynchronous_log"); ## prints asynchronous oid log if you need.
        	  LogData("Succesfully written oid data to $asynchronous_log",$Progress);
        	  print "   Succesfully written oid data to $asynchronous_log\n";
		  chmod(0444,$asynchronous_log);
		}else{
		  QuitCleanly("Found Errors but Failed to write to $asynchronous_log !",1);
		}
	}else{
	        $message = "Prod Live - Prod Stage $ParentTable Tables are fully synchronised.";
        	LogData($message,$Progress);
		print " $message\n\n";
		
		if ($multi_table_resync) {
		  MultiParentHandler('unload');
		  next RESYNCHRO_LOOP;
		}
		
		QuitCleanly("Succesfully Executed Procedure",0);

	}

	$status = 0;
	&BVCustomPerl::LogFooter($status,$LogFile,$MethodName,$user,$TimeLine);

	## If weve got here you know it means we have to resync
	print "\n----------------------------- $MethodName - Results -----------------------------\n\n";
	print "\tCurrently Enabled Constraints \n\t\t written to:\t${constraintlist}.enabled.log\n";
	print "\tOID data written to:\t$asynchronous_log\n";
	print "\n\tQuit Now if you wish to check these \n";
	print "\n\n\tIf you choose to continue :\n\n";
	print "\t\t1. Constraints will be disabled\n\t\t2. Child tables will be resynchronised\n";
	print "\t\t3. Parent tables will be resynchronised\n\t\t4. Constraints will be re-enabled\n";
	print "\t\t5. Tables will be rechecked for consistency\n\n";
	print "\t Do you wish to continue Y/N ?\n\n";

	&ExitOnDemand();

########################################### STAGE 3 ###########################################
# 
# Remember we have the following variables, hashes, arrays - you name it
#	%ResyncTables with the bare parent child table names
# 	@DBRefData with the 4-d constraint data 
#	and ${constraintlist}.enabled.log
#	$ParentTable is the Parent Table
#	@EnabledPConstraint and @EnabledCConstraints = parent and child constraints that need to be done over
#	@InitialAsynchronousOIDs has all the parent oids that are out
#
#	I'll Put more in here as I remember - Andy
#

	$MethodName = "PS - PL Resynchronisation - Stage III";
$Progress = $MethodName;
print "\n----------------------------- $Progress -----------------------------\n\n";
&BVCustomPerl::LogHeader($LogFile,$MethodName,$user,scalar(localtime));

#################################### Disabling Constraints ####################################

print "\t\tNeed to disable constraints now, are you happy to do this Y/N ?\n\n";
&ExitOnDemand();
LogData("User elected to disable constraints",$Progress);

$Progress = "Disabling Constraints";
print "8. $Progress\n";

# If anything goes wrong from here on we need to enable anything we can #

$SIG{__DIE__} = $SIG{QUIT} = $SIG{INT} = \&SpannerInWorks;

###### Disable Children then parents

  LogData("Disabling Constraints for Children tables",$Progress); 
  ($success, @cDisabledList) = AlterConstraints("disable",@EnabledCConstraints);
  if ($success == 0){ 
	  $message = "Disabled Child Constraints";
	  open(DISABLED_CONS,">${constraintlist}.disabled.log");
	  print DISABLED_CONS "# Succesfully Disabled the Following Constraints:\n";
	  print DISABLED_CONS @cDisabledList, "\n";
  }else{
  ## I'm quitting here if we cant disable children
          QuitCleanly("Failed to Disable Child Constraints. Cannot Continue, re-enabling all constraints",1,'replaceconstraints');
  }

print "   ", $message, "\n"; LogData("$message",$Progress);

###### Disable Parents

LogData("Disabling Constraints for Parent table",$Progress); 
($success, @pDisabledList) = AlterConstraints("disable",@EnabledPConstraint);
if ($success == 0){
	$message = "Disabled Parent Constraints";
	print DISABLED_CONS @pDisabledList, "\n";
}else{
	# If we get here and we've disabled child but not parent constraints
	# we need to re-enable children and exit
	close(DISABLED_CONS);
        QuitCleanly("Failed to Disable Parent Constraints. Cannot Continue, re-enabling all constraints",1,'replaceconstraints');
}
print "   ", $message, "\n"; LogData("$message",$Progress);

close(DISABLED_CONS);
chmod(0444,"${constraintlist}.disabled.log");
$message = "Written Disabled Constraints to ${constraintlist}.disabled.log ";
print "$message\n";
LogData("$message",$Progress);

######################################### Table Updates #########################################

## update subs here
print "\n---------------------------------- Table Updates ----------------------------------\n\n";
print 	"\n\t\tAbout to update any child tables followed by the parent table. " . 
	"\n\t\tIf you choose to exit at any point beyond here, constraints will be replaced for you.\n" .
	"\t\tDo you wish to continue Y/N ?\n\n";
ExitOnDemand("replaceconstraints");
$Progress = "Table Updates";
print "9. $Progress\n";

## Cascading data connectivity realligning
## Need to pass each parent: table&oid&level; for each child: table&oid&level; for each grandchild: no pass
## I dont believe we can extend this if we ever find greatgrandchildren 

foreach $OutOID (@InitialAsynchronousOIDs)
{
    if($OneAtATime) {
	print "\n---------------------------------- Update ----------------------------------\n\n";
        print "\tAbout to Update complete structure for this asynchronous oid:\n";
        print "   $OutOID\n\n";
        print "\tReady to continue?\n\n";
        if (<STDIN> =~ m/n/i)
	{
		$message = "User opted to skip update of $OutOID.";
		if ($debug) {
		  print " $message\n";
		}else{
		  print "   Skipping\n";
		}
		LogData("$message",$Progress); 
		next;
	}
    }
    $depth = 0; 	# we're at parent level
    @OutChildOIDs = P_C_Link($depth,$OutOID); ## Must return info in same way as InitialAsynchronousOIDs
    foreach $child_oid (@OutChildOIDs) #child oid 
    {
        $depth = 1; # child level
        @OutGrandChildOIDs = P_C_Link($depth,$child_oid);
        foreach $grandchild_oid (@OutGrandChildOIDs)
        {
	   # Grand child - I think this is only ever business rules but there may be others
	   UpdateTable(2,$grandchild_oid); # Doing grandhchildren - level 2 ers
        }
	UpdateTable(1,$child_oid); # Doing children - level 1 ers
    }
    UpdateTable(0,$OutOID); # Doing parents - level 0 ers
}

#################################### Re-Enabling Constraints ####################################

$Progress = "Re-Enabling Constraints";
print "10. $Progress\n";
LogData("Enabling Constraints for Parent table",$Progress); 

###### Enable Parent then Children 

($success, @pReEnabledlist) = AlterConstraints("enable",@EnabledPConstraint);
if ($success == 0) { $message = "Enabled Parent Constraints";}else{$message = "Failed to enable Parent Constraints";}
print "   ", $message, "\n"; LogData("$message",$Progress);

###### Enable Children 

LogData("Enabling Constraints for Children tables",$Progress); 
($success, @cReEnabledlist) = AlterConstraints("enable",@EnabledCConstraints);
if ($success == 0){ $message = "Enabled Child Constraints";}else{$message = "Failed to enable Child Constraints";}
print "   ", $message, "\n"; LogData("$message",$Progress);

$SIG{__DIE__} = $SIG{QUIT} = $SIG{INT} = 'DEFAULT'; # Reset the SIG trap

$Progress = "Checking Integrities";
print "11. $Progress\n";

LogData("Checking Constraints",$Progress);

@AnyRemainingCons = &CheckConstraints();
if (@AnyRemainingCons) {
  $message = "WARNING Some constraints have not been re-enabled Check Tables @AnyRemainingCons";
}else{
  $message = "Constraints All Re-Enabled\n";
  qx!mv -f "${constraintlist}.disabled.log" "${constraintlist}.${ParentTable}.log"!;
  qx!rm -f "${constraintlist}.enabled.log" !;
}

print "$message\n";
LogData("$message",$Progress);

## If the re-enable fails we need to restore from Backups and do so here then re-enable cons

  # Check here whether multi-parent resync has finished, if so increment $resync_complete
  if ($multi_table_resync) {
    MultiParentHandler('unload');
  }else{
    $resync_complete++;
  }
}

print "\n------------------------------ $MethodName - Complete ------------------------------\n\n";

	MultiParentHandler('finish') if ($multi_table_resync);
	QuitCleanly("Completed Procedure",0); 

}elsif($command eq '-clean'){

print "Tidying unnecessary files: ";

qx!rm -f "${constraintlist}"\.*.log!;
print "${constraintlist}.\*.log \n";

qx!rm -f "${InputOutputFile_1}"!;
print "${InputOutputFile_1} \n";

qx!rm -f "${asynchronous_log}"!;
print "${asynchronous_log} \n";

print "Cleaned.\n";

}elsif($command eq '-test'){
  # Whatever you want to test
  usage() unless ($debug);
}else{
	usage();
}

############################################ SUBS ############################################

sub usage
{
  print "\n------------------------------------------- $0 ------------------------------------------\n\n";
  print "Purpose:\n\t This script should be used to resynchronise any prod-stage/prod-live discrepancies.\n";
  print "Usage: \n\t $0 ";
  print <<'EOF1';
  [ -efrt -stage -clean ] [ command ] [-ct <DATE>] [ -all ]
  
Options:
           -e                     -   Pull error entries from latest stage_load log
  
           -f                     -   Format errored entries from latest stage_load log
                                      ready for use in resynchronisation.
           -r   [-ct <DATE>] [ -all ]
EOF1
  print "\t\t\t\t  -   Resynchronise tables. Reads from $0\.input_output\n";
  print <<'EOF2';
                                      Can only be run after the -f or -t options.
                                      This option will prompt before constraints are switched off or updates are performed
                 	-ct       -   Use the -ct <date> option to specify a greater than creation time 
                                      for the asynchrosity query.
                                      The <date> format must be of the order 'dd-mmm-yyyy'.
		 	-all	  -   Use the -all option to bypass the need to say 'y' to each individual
				      update. This option will ask once and resynchronise all asynchronous data.
           -stage                 -   Staging Procedure Investigation.
                                      Use this whenever the overnight staging procedures fail.                                      
           -t <PARENT TABLE>      -   Format parent and child tables from specified
                                      parent ready for use in resynchronisation.
  
           -clean                  -   Removes Unnecessary files from the process after completion.
EOF2
  print "\nBasic use:\nRun:";
  print "\t  1.     $0 -t (your database table name)\n";
  print "\t  2.     $0 -r \n";
  print "\n";
  exit 0;
} # end of usage

##############################################################################################

sub SpannerInWorks()
{
  # Need to QuitCleanly in the case of some unexpected failure like a DBI die
  $signame = shift;
  QuitCleanly("Received SIG$signame. Cannot Continue, re-enabling all constraints",1,'replaceconstraints');
}

##############################################################################################

sub StagingFailure($stage,$method,@duplicateOID)
{
  my($stage) = shift;
  my($method) = shift;
  my(@duplicateOID) = @_;
  my($count,$rowcnt,$errorcount) = 0;
  $status = 0;
  if (($stage =~ /locationpoper/) and ($method =~ /find/))
  {
    @duplicateOID = ();
    $sql =	"\t\tSELECT ID, TABLE_NAME, TABLE_OID, NEW_HOLIDAY_TYPE, NEW_BROCHURE_ID, ERROR_DESCRIPTION
    		  FROM MTC_LOCATION_POPER_LOG\@$database_link 
    		  WHERE DATE_CREATED >= sysdate - 2
    		  ";
    $sql .= "OR DATE_CREATED >= sysdate - 4 --For testing only" if ($Testing);
    print "$sql\n";
    
    $sth = $dbh->prepare("$sql");
    $sth->execute or die "Failed to perform SQL statement ... \n";
    while($result  = $sth->fetchrow_arrayref)
    {
        $message = "Failure Reason: $result->[5]";
        LogData("$message",$Progress);
        if ($result->[5] =~ m/duplicating key/)
        {
            # Create array with "table_name" and duplicate oid
            $duplicateOID[$count] = { table => "$result->[1]", 
            				oid => "$result->[2]" };
        }else{
            $message = "This failure is not due to a duplication. Please Investigate";
            print "$message\n";
            LogData("$message",$Progress); 
        }
        print "Location Poper Log: ", @{ $result }, "\n" if ($debug);
        $count++;
    }
    $sth->finish();
    if ($DBI::rows == 0)
    {
    	$message = "No rows returned, location poper was succesful";
    	print "$message\n";
    	LogData("$message",$Progress);
    }
  HashReader('print2d',@duplicateOID) if ($debug);    
  return(@duplicateOID);
  # swithces here for between 'find' and 'fix' options for locationpoper
  }elsif (($stage =~ /locationpoper/) and ($method =~ /fix/)) {
  
    HashReader('print2d',@duplicateOID) if ($debug); 
    for($i = 0; $i <= $#duplicateOID; $i++)
    {
      my($table) = $duplicateOID[$i]{'table'};
      my($mtc_code_3,$mtc_xxx_code);
      ($mtc_code_3 = $table) =~ s/^MTC_//;
      $mtc_code_3 = unpack("A3", $mtc_code_3);
      $exstat = 0;
      # Find the MTC_XXX_CODE column name
      
      $sql = "\t\tSELECT COLUMN_NAME
    		  FROM USER_TAB_COLUMNS\@$database_link 
    		  WHERE TABLE_NAME = \'${table}\'
    		  AND COLUMN_NAME LIKE \'MTC_%${mtc_code_3}%_CODE\'
    		  ";
      print "$sql\n" if ($debug);
      LogData("$sql",$Progress) if ($debug);
    
      $sth = $dbh->prepare("$sql");
      $sth->execute or die "Failed to perform SQL statement ... \n";      
      $result  = $sth->fetchall_arrayref;
      $sth->finish();
      
      $mtc_xxx_code = $result->[0]->[0] if ($DBI::rows == 1);
      $message = "Column name from $table is: $mtc_xxx_code \(of " . $DBI::rows . " Results)";
      print "$message\n" if ($debug);
      LogData("$message",$Progress) if ($debug);
      unless ($mtc_xxx_code) {
        $message = "Couldnt resolve mtc_xxx_code";
        print "$message\n";
        LogData("$message",$Progress);
        return 1;
      }
      
      # This sql is ok for most of the things we use the poper on.
      $sql = "\t\tSELECT ${table}_ID, TO_ID, HOLIDAY_TYPE, SEASON_CODE, BROCHURE_ID, ${mtc_xxx_code}
    		  FROM ${table}\@$database_link 
    		  WHERE OID = \'$duplicateOID[$i]{'oid'}\'
    		  ";
      print "$sql\n";  ### This will find the wrong oid info
      LogData("$sql",$Progress);
    
      $sth = $dbh->prepare("$sql");
      $sth->execute or die "Failed to perform SQL statement ... \n";      
      $result  = $sth->fetchrow_arrayref;
      $sth->finish();
 
      print "Query returns => ", join('::', @{ $result}), "\n" if ($debug);
      $correctkey = "$result->[1]-$result->[2]-$result->[3]-$result->[4]-$result->[5]";
      if ("$result->[0]" ne "$correctkey")
      {
      	print "Current key =>$result->[0]<= ; should be =>$correctkey<=\n";
      	LogData("Found disparity. Current key is $result->[0], correct key should be $correctkey",$Progress);
      		
	print "\n------------------------- Location Poper Resynchronisation -------------------------\n\n";
	print "\n\tChange Existing Primary Key:\t$result->[0]\n";
	print "\n\t\t\t\t To:\t$correctkey\n";
	print "\n\t Y/N ?\n\n";
	$ans = <STDIN>;
      	chomp($ans);
      	next unless ($ans =~ /y/i);
      	LogData("User elected to update ${table} primary key",$Progress);
      		
      	## query table to make sure an update wont clash
      	$rowcnt = DBUpdate('stage','rowcount',"${table}",'*',"WHERE ${table}_ID = \'$correctkey\'");
      	#$rowcnt += DBUpdate('live','rowcount',"${table}",'*',"WHERE ${table}_ID = \'$correctkey\'");
      	
      	# Now that row above is a bit of a minefield quite frankly. 
      	#  	If you leave it in, it will give you the impression that there are problems and stall 
      	# everything when really were only concerned with sorting out prod stage. 
      	# It also means that if you had previously fixed PS, but hadnt been able to fix PL 
      	# then it will continue to throw up the error and you'd think that neither were
      	# fixed and keep going for ever.
      	#  	On the other hand taking it out means that if there is something up, you could fix the 
      	# damage on PS or so you think and then when you run through it again it wont find any errors.
      	# But then you'll need to resync PL to PS in the normal manner.
      	
      	if ($rowcnt != 0)
      	{
      	    print "Ive got a row count = $rowcnt\n";
      	    $message = "Update will cause clash with $rowcnt rows in database. Skipping...";
    	    print "$message\n\n";
    	    LogData("$message",$Progress);
    	    next;
      	}else{
      		$message = "No existing rows with this key, can safely update";
		print "$message\n\n";
    		LogData("$message",$Progress);
      	}
      	    $exstat = DBUpdate('stage','update',"${table}",
      			"${table}_ID = \'$correctkey\'",
      			"WHERE ${mtc_xxx_code} = \'$result->[5]\' AND OID = \'$duplicateOID[$i]{'oid'}\' ");
      	    $exstat += DBUpdate('live','update',"${table}",
      			"${table}_ID = \'$correctkey\'",
      			"WHERE ${mtc_xxx_code} = \'$result->[5]\' AND OID = \'$duplicateOID[$i]{'oid'}\' ");
      	    print "\$exstat = $exstat \n" if ($debug);
      	    if ($exstat)
      	    {
      		$errorcount++;
      		$message = "Update was unsuccesful !!";
      	    }else{
      		$message = "Update Succesful";
    	    }
    	    print "$message\n\n";
   	    LogData("$message",$Progress);
            ## done update to both stage and live
           	
      }elsif ("$result->[0]" == "$correctkey") {
    	$message = "Primary Key Has Been Corrected";
		print "$message\n\n";
    	LogData("$message",$Progress);
    	next;
      }
    }
  }  # end of locationpoper 'fix' option
  return($errorcount);
} # end StagingFailure

##############################################################################################

sub DBUpdate($database,$query_or_update,$table,$select_clause,$where_clause)
{
    my($database,$query_or_update,$table,$select_clause,$where_clause) = @_;
    my($result);
    ${table} .= "\@$database_link" if ($database !~ /live/i);
    if ($query_or_update !~ /update/i) 
    {
	$sql = "\t\t\tSELECT $select_clause
			FROM ${table}
			";
    }elsif ($query_or_update =~ /update/i) {
	$sql = "\t\t\tUPDATE ${table}
			SET $select_clause
			";	
    }
	
    $sql .= "$where_clause\n";
    $sql .= "\t\t\tAND STATUS = 2\n" if ($Testing == 2); ## This is never going to be true
    print "$sql\n";

    $sth = $dbh->prepare("$sql");
    $sth->execute or die "Failed to perform SQL statement ... \n";    
    $result  = $sth->fetchall_arrayref unless ($query_or_update =~ /update/i);
    $result = $result->[0];
    $sth->finish();
    return($DBI::rows) if ($query_or_update =~ /rowcount/i);
    return($result) if ($query_or_update =~ /select/i);
    return($DBI::err) if ($query_or_update =~ /update/i);
} # end 

##############################################################################################

sub P_C_Link($oidish)
{
# Were only going to handle one piece of oid info at a time 
# we can only do child tables with a 1 to 1 relationship - cant remember if this only applied to v 1.*
  my($adultlevel) = shift;
  my($oidish) = shift;
  my(@oid_sturff) = ();
  return() unless ($adultlevel < $levelcount);
  ($childlevel = $adultlevel)++;
  print "\$adultlevel=$adultlevel ; \$levelcount=$levelcount\n" if ($debug);
  $oidish =~ s/\s+//g;
  return() unless ($oidish);
  @oidishdata = split(':',$oidish);

   # We need:-
   # 1. To get an async oid from the info fed in
   # 2. the link column name from the table which is the parent of the p-c relationship
   # 3. the link column name from the table which is the child of the p-c relationship
   # 4. To retreive the prodstage value for the child oid which comes from all this
   # 5. To insert any extra rows that should be updated into the return package.

  foreach (@oidishdata)
  {
    (($summat,$upper_level_table) = split('=>', $_)) if (/^PARENT_TABLE\=\>/);
    (($summat,$oid_column_name) = split('=>', $_)) if (/^OID_COLUMN_NAME\=\>/);
    (($summat,$refint_column_name) = split('=>', $_)) if (/^REFERENTIAL_KEY\=\>/);
    (($summat,$WrongRefint_value) = split('=>', $_)) if (/^PL\.REFERENTIAL_VALUE\=\>/);
    (($summat,$CorrectRefint_value) = split('=>', $_)) if (/^PS\.REFERENTIAL_VALUE\=\>/);
    (($summat,$WrongValue) = split('=>', $_)) if (/^PL\.VALUE\=\>/);
    (($summat,$CorrectValue) = split('=>', $_)) if (/^PS\.VALUE\=\>/);
    (($summat,$referral_column_name) = split('=>', $_)) if (/^REFERRAL_COLUMN\=\>/);
    (($summat,$referral_column_value) = split('=>', $_)) if (/^REFERRAL_COLUMN_VALUE\=\>/);
    print "\@oidishdata :-->$_<--\n" if ($debug);
  }

  if ($debug) {
    print "The Upper Level Table is:\t->$upper_level_table\n";
    print "The  OID  column name is:\t->$oid_column_name\n";
    print "The ref int column name is:\t->$refint_column_name\n";
    print "The Wrong ref int value is:\t->$WrongRefint_value\n";
    print "The Correct ref int value is:\t->$CorrectRefint_value\n";
    print "The Referral Column name is:\t->$referral_column_name\n";
    print "The Referral Column Value is:\t->$referral_column_value\n";
    print "Wrong oid is:\t\t\t->$WrongValue\n";
    print "Correct oid is:\t\t\t->$CorrectValue\n";
    print "Using QueryHash($childlevel,$childlevel,\$query,\@EnabledCConstraints) childlevel = $childlevel\n";
  }
  # Get children of $upper_level_table
  @KidList = QueryHash($childlevel,$childlevel,"CONSTRAINT_TYPE eq P",@DBRefData);
  @KidList = QueryHash($childlevel,$childlevel,"KEY eq TABLE_NAME",@KidList);
  @KidList = HashReader('array',@KidList);
  foreach (@KidList) 
  {
    s/\s+//g;
    next if (/^$/);
    (($blah,$blah1,$blah2,$child_table) = split(':',$_));
    $child_table =~ s/^TABLE_NAME\=\>(.*)$/$1/;
    $message = "Creating update package for child table: $child_table";
    print "$message\n";
    LogData("$message",$Progress);

    @Childstuff = QueryHash($childlevel,$childlevel,"TABLE_NAME eq $child_table",@DBRefData);
    print "Childstuff:\n" if ($debug);
    HashReader('print',@Childstuff) if ($debug);

    # find p - key then r - key
    foreach (P,R) {
      @P_or_Rkey = QueryHash($childlevel,$childlevel,"CONSTRAINT_TYPE eq $_",@Childstuff); # RKey of this child
      @P_or_Rkey = QueryHash($childlevel,$childlevel,"KEY eq COLUMN_NAME",@P_or_Rkey);
      @P_or_Rkey = HashReader('array',@P_or_Rkey);
      foreach $P_or_Rkey (@P_or_Rkey){ (($level,$table,$constraint,$P_or_Rkey_values) = split(':', $P_or_Rkey)); }
      $P_or_Rkey_values =~ s/(\s|\n)//g; # remove spaces and newline
      ($PrimaryKey = $P_or_Rkey_values) =~ s/^COLUMN_NAME\=\>(.*)$/$1/i if (/P/);
      ($ReferentialKey = $P_or_Rkey_values) =~ s/^COLUMN_NAME\=\>(.*)$/$1/i if (/R/);
    }

    ### Only need to do any more if this is a middle level element(parent-child-grandchild set-up)
    if ($childlevel < $levelcount) {

      $plquery =	"\t\tSELECT $PrimaryKey
		  FROM $child_table 
		  WHERE $ReferentialKey in
				  (SELECT $oid_column_name
				  FROM $upper_level_table
				  WHERE $oid_column_name = \'$WrongValue\')
		  ORDER BY $PrimaryKey
		  ";
      print "$plquery\n";  ### This will find the wrong oid info

      $sth = $dbh->prepare("$plquery");
      $sth->execute or die "Failed to perform SQL statement ... \n";      
      while($result  = $sth->fetchrow_arrayref)
          {
                  $result->[0] =~ s/^\s*(.*)\s*$/$1/;
                  push(@plchild_results,$result->[0])
          }
      print "\@plchild_results = @plchild_results\n" if ($debug);

      $psquery =  "\t\tSELECT $PrimaryKey
                  FROM $child_table\@$database_link
		  WHERE $ReferentialKey in
				  (SELECT $oid_column_name
				  FROM $upper_level_table\@$database_link
				  WHERE $oid_column_name = \'$CorrectValue\')
		  ORDER BY $PrimaryKey
                  ";
      print "$psquery\n"; 
      ### This should find the right oid info - we can map them next to one another then
      $sth = $dbh->prepare("$psquery");
      $sth->execute or die "Failed to perform SQL statement ... \n";      
      while($result  = $sth->fetchrow_arrayref)
          {
                  $result->[0] =~ s/^\s*(.*)\s*$/$1/;
                  push(@pschild_results,$result->[0])
          }
      print "\@pschild_results = @pschild_results\n" if ($debug);

      ## Count both if they are different we may have to exit
      $plchildren = @plchild_results;
      $pschildren = @pschild_results;
      if ($pschildren < $plchildren) {
	  $mesg = "More entries in prod live than prod stage for table: $child_table " . 
		  "It is very unwise to try to update these automatically";
          print "\n\t$mesg\n\n";
	  LogData("$mesg",$Progress);
	  QuitCleanly("Table: $child_table is not possible to update automatically... Cannot continue",1,'replaceconstraints');

      }else{ ## We can carry on hopefully

        for ($childcnt = 0; $childcnt < $plchildren; $childcnt++) 
        {

		if ($debug) 
		{
        	  print "We need to update the children of ->$upper_level_table<- \n";
        	  print "Primary Key is $PrimaryKey : Ref Integrity Key is $ReferentialKey\n";
        	  print "Where ->$upper_level_table<- = \'$WrongValue\'. " .
        	        "The child is linked by column ->$ReferentialKey<- \n";
		  print "The Wrong Ref Integrity Value is ->$WrongValue<- : " .
		        "The Correct Ref Integrity Value is ->$CorrectValue<-\n";
		}
        	$outputstr =  	"PARENT_TABLE => $child_table : OID_COLUMN_NAME => $PrimaryKey : " . 
			      	"REFERENTIAL_KEY => $ReferentialKey : " . 
				"PL.VALUE => $plchild_results[$childcnt] : " .
				"PS.VALUE => $pschild_results[$childcnt] : " .
                      		"REFERRAL_COLUMN => : REFERRAL_COLUMN_VALUE => : " . 
				"PL.REFERENTIAL_VALUE => $WrongValue : " .
				"PS.REFERENTIAL_VALUE => $CorrectValue :";
        	print "My \$outputstr is ->$outputstr<-\n" if ($debug);
        	push(@oid_sturff,$outputstr);
        }
      }

    }else{ 
    ## ie we have only got a simple hierarchy(parent, or parent-child)

      if ($debug) 
      {
        print "We need to update the children of ->$upper_level_table<- \n";
        print "Primary Key is $PrimaryKey : Ref Integrity Key is $ReferentialKey\n";
        print "Where ->$upper_level_table<- = \'$WrongValue\'. The child is linked by column ->$ReferentialKey<- \n";
      }
      $outputstr =  "PARENT_TABLE => $child_table : OID_COLUMN_NAME => $PrimaryKey : " .
      		    "REFERENTIAL_KEY => $ReferentialKey : " . 
  		    "PL.VALUE => $WrongValue : PS.VALUE => $CorrectValue : " . 
		    "REFERRAL_COLUMN => : REFERRAL_COLUMN_VALUE => :";
      print "My \$outputstr is ->$outputstr<-\n" if ($debug);
      push(@oid_sturff,$outputstr);
    }
  }
  return(@oid_sturff);
} # end of P_C_Link

##############################################################################################

sub CheckConstraints
{
  # Find what was on
  my(@EnabledData) = ();
  %ConstraintInfo = ReadIOFile("${constraintlist}.enabled.log");
  print "From constraintlist:\n" if ($debug > 1);
  foreach $level (sort keys(%ConstraintInfo)){
    foreach $table (sort keys(%{ $ConstraintInfo{$level} })){
      foreach $constraint (sort keys(%{ $ConstraintInfo{$level}{$table} })){
        $Info = $ConstraintInfo{$level}{$table}{$constraint};
        print "Level->$level<- \nTable ->$table<-\n" if ($debug > 1);
        print "Constraint ->$constraint<- \nInfo ->$Info<-\n" if ($debug);
        @CurrentStatus = DBLinkInfo(0,$table);
        print "\@CurrentStatus=>@CurrentStatus\n" if ($debug > 1);
        foreach $constraint ($#CurrentStatus){ 
          foreach $value (sort keys(%{$CurrentStatus[$constraint]})) {
            $ReEnabledStatus = $CurrentStatus[$constraint]{'STATUS'};
            print "CurrentStatus says constraint = $constraint vals = $CurrentStatus[$constraint]{$value}\n" if ($debug);
            push(@EnabledData, "$table") if ($ReEnabledStatus =~ m/DISABLED/i);
          }
        }
      }
    }
  }
  @EnabledData = MergeArrays(@EnabledData); # just sorts them uniqely
  return(@EnabledData);
}

##############################################################################################

sub UpdateTable($level,@asyncoids)
{
  my($level) = shift;
  my(@asyncoids) = @_;
  my($table,$ColName,$WrongOID,$NewOID,$RefColumn,$Table_ID,$Refint_Key,$WrongRefint_value,$CorrectRefint_value);
  # Children then Parent 
  chomp(@asyncoids);
  print "\@asyncoids---->@asyncoids<----\n" if ($debug);

    foreach $x (@asyncoids) 
    {

	foreach(split(/:/, $x)){
	 chomp;
	 # Pre version 5 I tried this lot:
         #s/(\s|\n)//g;  ## takes out spaces -- had to alter this for cases where a name has spaces in it
         #s/\s*(\=\>)\s*/$1/;  ## takes out spaces each side of delimmiter -- altering again
         #s/^(?:\s*)(.*\=\>.*?)(?:\s*)$/$1/;  ## takes out leading and trailing spaces
         # anyhow these are the right ones to use:
         s/^\s*//;  ## takes out spaces before variable name
         s/\s*(\=\>)/$1/;  ## takes out spaces before delimmiter
         s/(\=\>)\s/$1/;  ## takes out single leading space from the value
         s/\s$//;  ## takes out single trailing space from the value
         # Dont alter this now - there are too many different scenarios to beat this is best
         print "->$_<-\n" if ($debug); ## Displays each element of this array

         (($discard,$table) = split(/=>/,$_)) if (/^PARENT_TABLE\=\>\w+/i);  ## The Parent table name if we need
         (($discard,$ColName) = split(/=>/,$_)) if (/^OID_COLUMN_NAME\=\>\w+/i);  ## The oid column - pkey
         (($discard,$WrongOID) = split(/=>/,$_)) if (/^PL\.VALUE\=\>(?:-|)\d+/i); ## The out of sync PL.OID column
         (($discard,$NewOID) = split(/=>/,$_)) if (/^PS\.VALUE\=\>(?:-|)\d+/i);	  ## The correct PS.OID column
         (($discard,$RefColumn) = split(/=>/,$_)) if (/^REFERRAL_COLUMN\=\>\w+/i);
         (($discard,$Table_ID) = split(/=>/,$_)) if (/^REFERRAL_COLUMN_VALUE\=\>\w+/i);
         (($discard,$Refint_Key) = split('=>', $_)) if (/^REFERENTIAL_KEY\=\>/);
         (($discard,$WrongRefint_value) = split('=>', $_)) if (/^PL\.REFERENTIAL_VALUE\=\>/);
         (($discard,$CorrectRefint_value) = split('=>', $_)) if (/^PS\.REFERENTIAL_VALUE\=\>/);

       }
       print "Synchronising $table\n";

     #### Adding Check to ensure that there is no chance of oids clashing by shifting our info
     #### into a temporary oid space, deleting the new and then updating from the temp space
     #### into the freshly emptied space

       $message = "Checking to ensure no existing oid of value:$NewOID";
       print "$message\n";
       LogData("$message",$Progress);

       $query = "\t\tSELECT COUNT($ColName) FROM $table WHERE $ColName = $NewOID";
       print "$query\n" if ($debug);

       $sth = $dbh->prepare("$query");
       $sth->execute or die "Failed to perform SQL statement ... \n";       
       $result  = $sth->fetchrow_arrayref;
       
       ($result = $result->[0]) =~ s/^\s*(.*)\s*$/$1/;
       print "No of rows =$result\n" if ($debug);

       if ($result) {
         $message = "Found existing content for $ColName value:$NewOID. Removing";
         print "$message\n";
         LogData("$message",$Progress);

         $query = "\t\tDELETE FROM $table WHERE $ColName = $NewOID";
         print "\n", $query, "\n" if ($debug);
         LogData("SQL -->$query<--",$Progress);
         $sth = $dbh->prepare("$query") unless ($Testing);
         $sth->execute or die "Failed to perform SQL statement ... \n" unless ($Testing);         
       }

       $message = " Level=>$level<= : Table =>$table<= Column Name =>$ColName<= Referral Column =>$RefColumn<=" . 
       		  " Wrong (current) $ColName =>$WrongOID<= New (PS) $ColName =>$NewOID<=";
       print "$message\n" if ($debug);
       LogData("About to Perform Update for:$message",$Progress);

       if (($RefColumn) and ($Table_ID)) {
       $query = "\t\tUPDATE $table
	   		SET $ColName = $NewOID
	   		WHERE $RefColumn = \'$Table_ID\'
	   		AND $ColName = $WrongOID";
       }elsif(($Refint_Key ne $ColName) and ($CorrectRefint_value)){
       $query = "\t\tUPDATE $table
	   		SET $ColName = $NewOID, $Refint_Key = $CorrectRefint_value
	   		WHERE $ColName = $WrongOID
	   		AND $Refint_Key = $WrongRefint_value";
       }elsif(($Refint_Key ne $ColName) and (! $CorrectRefint_value)){
	## Added this one to cater for bv_content_ref mainly which uses parent_oid not oid
       $query = "\t\tUPDATE $table
	   		SET $Refint_Key = $NewOID
	   		WHERE $Refint_Key = $WrongOID";
       }else{
       $query = "\t\tUPDATE $table
	   		SET $ColName = $NewOID
	   		WHERE $ColName = $WrongOID";
       }

       print "\n", $query, "\n";
       LogData("SQL -->$query<--",$Progress);

       $sth = $dbh->prepare("$query") unless ($Testing);
       $sth->execute or die "Failed to perform SQL statement ... \n" unless ($Testing);       
       $complete = $DBI::err;
       print "  ";
       print " " . ($complete == 0) ? 'Succesful' : 'Failed !';
       if ($DBI::err){ print " $ThisCon - Error: $DBI::errstr"; $success = 1; 
       }else{
	 $success = 0;
	 LogData("Successful Update: ->$table<- Set ->$ColName<- to ->$NewOID<- " . 
	         "Where ->$RefColumn<- Equals ->$Table_ID<- and ->$ColName<- Was ->$WrongOID<-",$Progress);
       }
       print "\n";
    }

} # end of UpdateTable

##############################################################################################

sub AlterConstraints($on_or_off,@list)
{
  my($on_or_off) = shift;
  my(@list) = @_;
  my($success) = 1 if (@list); # we start with a failed status
  my(@return) = (); 
  $incount = 0;
  #HashReader('print',@list); # Comment this when testing finished
  $on_or_off = 'ENABLE' if ($on_or_off =~ m/enable/i); # just in case - always better to enable
  $on_or_off = 'DISABLE' if ($on_or_off =~ m/disable/i);
  foreach $level ( 0 .. $#list ) {
    foreach $table (sort keys(%{ $list[$level] })) {
      foreach $con ( 0 .. $#{ $list[$level]{$table} }) {
	next unless ($ThisCon = $list[$level]{$table}[$con]{CONSTRAINT_NAME}); # If con 0 is a Ref Int constrnt
	print " Need to =>$on_or_off<= CONSTRAINT_NAME ->$ThisCon<-\n" if ($debug > 1);
	$query = "\tALTER TABLE $table $on_or_off CONSTRAINT $ThisCon";

    	print "\n", $query, "\n";

        $sth = $dbh->prepare("$query");
        $sth->execute or die "Failed to perform SQL statement ... \n";    	
	$incount++;
	$complete = $DBI::err;
	print "   Alteration: "; print " " . ($complete == 0) ? 'Succesful' : 'Failed !';
	if ($DBI::err){ print " $ThisCon - Error: $DBI::errstr"; $success = 1;
	  LogData("Couldnt Alter \(" . lc(${on_or_off}) . "\) -> $ThisCon for table: $table",$Progress);
	}else{
	  $success = 0;
          $ret = "LEVEL : $level : TABLE : $table : CONSTRAINT : $con : TABLE_NAME : $table : STATUS :" .
          " ${on_or_off}D : CONSTRAINT_NAME : $ThisCon";
	  LogData("Altered\(" . lc(${on_or_off}) . "d\) -> $ret",$Progress);
          push(@return,"$ret\n");
	}; print "\n";
      }
    }
  }
  $outcount = @return;
  $success = 1 unless ($outcount == $incount);
  return($success,@return);
} # end of AlterConstraints

##############################################################################################

sub ExitOnDemand(@commands)
{
  my(@commands) = @_;
  while (<STDIN>){
    chomp;
    unless (m/(y)/i) {
      print "Quitting\n"; 
      &MultiParentHandler('finish') if ($multi_table_resync);
      foreach (@commands) {
      	if (/replaceconstraints/i){
          LogData("User opted to quit",$Progress);
          QuitCleanly('Constraints are still disabled - Re-enabling',0,'replaceconstraints');
	}
      }
      LogData("User opted to quit",$Progress);
      QuitCleanly('',0);
    }
    last;
  }
} # end of ExitOnDemand

##############################################################################################

sub QuitCleanly($message,$status,@commands)
{
  my($message) = shift;
  my($status) = shift;
  my(@commands) = @_;
  LogData("$message",$Progress) and print "$message \n" if ($message);
  foreach (@commands) {
    if (/replaceconstraints/i) {
        ($p,@plist) = &AlterConstraints("enable",@EnabledPConstraint);
        ($c,@clist) = &AlterConstraints("enable",@EnabledCConstraints);
        print "Parent Re-Enable Success = $p : Child Re-Enable Success = $c\n";
        LogData("Re-enabling Constraints :- Parent Re-Enable Success = $p : Child Re-Enable Success = $c",$Progress);
        if (($p == 0) and ($c == 0)) { 
	  qx!mv -f "${constraintlist}.disabled.log" "${constraintlist}.enabled.log"!;
	  print "Renamed ${constraintlist}.disabled.log\n";
	}
    }
  }
  $SIG{__DIE__} = $SIG{QUIT} = $SIG{INT} = 'DEFAULT';
  $dbh->disconnect;
  &BVCustomPerl::LogFooter($status,$LogFile,$MethodName,$user,$TimeLine);
  if ($status =~ /^0$/){ exit 0; }elsif($status =~ /^1$/){ exit 1; }else{ exit $status;}

} # end of QuitCleanly

##############################################################################################

sub HashReader($displaytype,@inhash) 
{
    my ($displaytype) = shift; ## You can use 'print' Or 'array' or 'print2d'
    my (@inhash) = @_;
    my (@outhash) = ();
    $displaytype = lc($displaytype);
    for $level ( 0 .. $#inhash )
    {
      print "Now on level $level\n" if ($displaytype =~ m/print/);
      foreach $table ( keys( %{ $inhash[$level]} )) {
	print "Level => $level : Key => $table : Values => $inhash[$level]{$table}\n" if ($displaytype =~ m/print2d/);
	for $constraint ( 0 .. $#{ $inhash[$level]{$table} } ) 
	{
	  foreach $dbkeyinfo (keys( %{ $inhash[$level]{$table}[$constraint]} )) 
          {
	    $output = "Level => $level : Table => $table : Constraint No => $constraint : " . 
               	      "$dbkeyinfo => $inhash[$level]{$table}[$constraint]{$dbkeyinfo}";
	    print "$output\n"  if ($displaytype =~ m/print/);
	    push(@outhash,"$output\n") if ($displaytype =~ m/array/);
          }
	}
      }
    }
    return(@outhash) if ($displaytype =~ m/array/);

} # end of HashReader

##############################################################################################

sub Asynchrosity(@data)
{
  my(@data) = @_;
  my($ParentTable) = sort keys(%{ $data[0]});
  my(@AsynchrousOIDs,@atts) = ();
  my($ParentColumnName);
  foreach $constraint_no (0 .. $#{ $data[0]{$ParentTable} })
  {
    $ParentColumnName = $data[0]{$ParentTable}[$constraint_no]{'COLUMN_NAME'}; 
    last if ($data[0]{$ParentTable}[$constraint_no]{'CONSTRAINT_TYPE'} eq 'P');
  }

    ## Get the name thats basically the mtc_blahblah_id name - occaisionally its different
    ## Need to find Referential Integrity column name 
    ## and primary key for each of these tables

    $query =      "\t\tSELECT * FROM
                    ( SELECT ATTRIBUTE_NAME
                      FROM BV_ATTRIBUTES
                      WHERE TABLE_NAME = \'$ParentTable\'
		      AND ATTRIBUTE_NAME != 'OID'
		      AND ROWNUM = 1
		      ORDER BY ATTRIBUTE_ID )
		    ";
  
    print $query, "\n" if ($debug);

      $sth = $dbh->prepare("$query");
      $sth->execute or die "Failed to perform SQL statement ... \n";      
      while($result  = $sth->fetchrow_arrayref)
	  {
    		  $result->[0] =~ s/^\s*(.*)\s*$/$1/;
		  push(@atts,$result->[0])
	  }
      $ParentRefColumnName = $atts[0];
      $XMLid_exists = ($atts[1]) ? "$atts[1]" : '';
      $sth->finish();

  #    print "Asynchrosity gives \$ParentTable as :$ParentTable: and \$ParentColumnName as :$ParentColumnName:\n",
  #	   "and \$ParentRefColumnName as :$ParentRefColumnName: XMLid_exists as :$XMLid_exists:\n";
      # Need to die if there's no $ParentRefColumnName

      print "   Analysis Gives \'$ParentRefColumnName\' ";
      print "and $XMLid_exists column exists" if ($XMLid_exists); 
      print "\n    Using Creation Time of \'$useCreationDate\' \n" if ($useCreationDate);
      print "\n\n";
      &QuitCleanly('Failed to find referral column name. The following sql will be unsafe!',1) unless ($ParentRefColumnName);
      LogData("Found referral column for $ParentTable =>$ParentRefColumnName<=",$Progress);
      LogData("Checking XML_ID column existence =>$XMLid_exists<=",$Progress);

     ###### This is the query we need for actual production of this script
     $query =      "\t\tSELECT PL.$ParentColumnName, PS.$ParentRefColumnName, PS.$ParentColumnName, PS.$ParentRefColumnName
                    FROM $ParentTable PL, $ParentTable\@$database_link PS
                    WHERE PS.DELETED = 0
		    AND PL.$ParentRefColumnName = PS.$ParentRefColumnName
		    AND PS.$ParentColumnName <> PL.$ParentColumnName \n";

     $query .= "\t\tAND PL.CREATION_TIME >= \'$useCreationDate\' \n" if ($useCreationDate);
     print "$query\n";

     ###### Test query TAKE OUT when producing
     if ($Testing == 2) {
     $query =      "\t\tSELECT PL.$ParentColumnName, PS.$ParentRefColumnName, PS.$ParentColumnName, PS.$ParentRefColumnName
                    FROM $ParentTable PL, $ParentTable\@$database_link PS
                    --FROM $ParentTable PL, $ParentTable PS
                    WHERE PS.DELETED = 0
		    AND PL.$ParentRefColumnName = PS.$ParentRefColumnName
		    --AND PS.$ParentColumnName <> PL.$ParentColumnName
		    AND lower(PS.$ParentRefColumnName) like '%test%'
		    --AND PS.$ParentRefColumnName like '5-0-S2002-%'
		    AND rownum < 3
                    ";

     $query .= "AND PS.XML_ID IS NOT NULL" if ($XMLid_exists);
     print "Testing Query ->\n", $query, "\n";
     }

   LogData("Preparing sequel for oid asynchrosity check. If this fails check were running this on Prod Live " . 
           "and that database link has not changed from \'$database_link\'\.",$Progress);
   print "\n\tIf db prepare fails below - Its possibly because you are not running this command on Production Live\n" . 
   	 "\tor because the name of the database link has changed from \'$database_link\'\.\n\n";

   LogData("Sql: $query",$Progress) if ($debug);

    $sth = $dbh->prepare("$query");
    $sth->execute or die "Failed to perform SQL statement ... \n";    
    while ($result = $sth->fetchrow_arrayref)
    {
	$data = "PARENT_TABLE => $ParentTable : OID_COLUMN_NAME => $ParentColumnName : " . 
		"REFERENTIAL_KEY => $ParentColumnName : PL.VALUE => $result->[0] : PS.VALUE => $result->[2] : " . 
		"REFERRAL_COLUMN => $ParentRefColumnName : REFERRAL_COLUMN_VALUE => $result->[1] :";
	push(@AsynchrousOIDs,"$data\n");
	LogData("Asynchronous => $data",$Progress);
    }
    $sth->finish();

    return(@AsynchrousOIDs);

} # end of Asynchrosity

##############################################################################################

sub WriteHash($outputtype,$outputname,@dbhash)
{
  my($outputtype) = shift;
  my($outputname) = shift;
  my(@dbhash) = @_;
  my(@Output) = (); 
  if ($outputtype =~ m/file/i) {
    open(OUT,">>$outputname") or die "Cant write to $outputname\n";
    LogData("Writing to: $outputname","WriteHash routine called");
  }
  foreach $level ( 0 .. $#dbhash ) {
    foreach $table ( keys( %{ $dbhash[$level]} )) {
      foreach $constraint ( 0 .. $#{ $dbhash[$level]{$table} } ) {
	if ($dbhash[$level]{$table}[$constraint]) {
  	  #@Output = ();  # Take this out if need to empty the array each time
          push(@Output,"LEVEL => $level : TABLE => $dbhash[$level]{$table}[$constraint]{'TABLE_NAME'} : ");
	  push(@Output,"CONSTRAINT => $constraint : ");
	  foreach $key (keys %{ $dbhash[$level]{$table}[$constraint] }) {
	    push(@Output,"$key => $dbhash[$level]{$table}[$constraint]{$key} : ");
	  }
	  if ($outputtype =~ m/file/i) {
	    print OUT @Output, "\n";
	    LogData("Logging: @Output","WriteHash Routine");
	    @Output = ();
	  }
	}
      }
    }
  }
  close(OUT) if ($outputtype =~ m/file/i);

} # end of WriteHash

##############################################################################################

sub QueryHash($startlevel,$endlevel,$query,@inputhash)
{
  my($startlevel) = shift;
  my($endlevel) = shift;
  my($query) = shift;
  my(@inputhash) = @_;
  my(@returnhash,@query) = ();
  @query = split(/ /, $query);
  $find = "$query[0]";
  $criteria = $query[2];
  foreach $level ( $startlevel .. $endlevel ) {
    foreach $table ( keys( %{ $inputhash[$level]} )) {
      foreach $constraint ( 0 .. $#{ $inputhash[$level]{$table}} ) {
	if ($find !~ m/^KEY$/i) {

	  if (($query[1] =~ m/eq/) and ($inputhash[$level]{$table}[$constraint]{$find} =~ m/^$criteria$/ )) {
            foreach $key ( keys( %{ $inputhash[$level]{$table}[$constraint]} )) {
  	        $returnhash[$level]{$table}[$constraint]{$key} = $inputhash[$level]{$table}[$constraint]{$key};
            }
          }elsif (($query[1] =~ m/ne/) and ( $inputhash[$level]{$table}[$constraint]{$find} !~ m/^$criteria$/ )) {
	    foreach $key ( keys( %{ $inputhash[$level]{$table}[$constraint]} )) {
	      $returnhash[$level]{$table}[$constraint]{$key} = $inputhash[$level]{$table}[$constraint]{$key};
	    }
          }elsif(($query[1] !~ m/ne/) and ($query[1] !~ m/eq/)){
	    print "No criteria to restrict QueryHash for $level, $table, $constraint - Returning all data\n";
	    foreach $key ( keys( %{ $inputhash[$level]{$table}[$constraint]} )) {
	      $returnhash[$level]{$table}[$constraint]{$key} = $inputhash[$level]{$table}[$constraint]{$key};
	    }
	  }
        }elsif($find =~ m/^KEY$/i) {
	  ## In this case criteria will be the key in the key/value pair
	  if (($query[1] =~ m/eq/) and ($inputhash[$level]{$table}[$constraint]{$criteria} )) {
	    $returnhash[$level]{$table}[$constraint]{$criteria} = $inputhash[$level]{$table}[$constraint]{$criteria};
	  }
	}
      }
    }
  }
  return(@returnhash);

} # end of QueryHash

##############################################################################################

sub DBLinkInfo($printsql,@TableList)
{
  my($printsql) = shift;
  my(@TableList) = @_;
  my(@DBreturn,@NewTableList) = ();
  foreach (@TableList)
  {
   push(@NewTableList,"\'$_\'");
  }
  @TableList = join(",", @NewTableList);
  ## Need to find Referential Integrity column name 
  ## and primary key for each of these tables
  $query =      "\t\tSELECT COL.TABLE_NAME, CON.CONSTRAINT_TYPE, CON.STATUS, COL.COLUMN_NAME, COL.POSITION, COL.CONSTRAINT_NAME
                  FROM USER_CONSTRAINTS CON, USER_CONS_COLUMNS COL
                  WHERE CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
                  AND CON.CONSTRAINT_TYPE in ('P','R')
                  AND COL.POSITION = 1
                  AND COL.CONSTRAINT_NAME in
                                  (SELECT CONSTRAINT_NAME
                                   FROM USER_CONSTRAINTS
                                   WHERE TABLE_NAME in (@TableList))";

    print $query, "\n" if ($printsql);

    $sth = $dbh->prepare("$query");
    $sth->execute or die "Failed to perform SQL statement ... \n";    
    $constraint_number = 0;
    while ($result = $sth->fetchrow_arrayref)
    {
      foreach $column ($result)
      {
	  #print "Constraint=>$constraint_number : ";
	  #print "$column->[0] : $column->[1] : $column->[2] : $column->[3] : $column->[4] : $column->[5]\n";
	  $DBreturn[$constraint_number]
			= {
			  TABLE_NAME => "$column->[0]",
			  CONSTRAINT_TYPE => "$column->[1]",
			  STATUS => "$column->[2]",
			  COLUMN_NAME => "$column->[3]",
			  POSITION => "$column->[4]",
			  CONSTRAINT_NAME => "$column->[5]",
			  };
	  $constraint_number++;
      }
    }
    $sth->finish();
    foreach ($DBreturn{'CONSTRAINT_NAME'}) { print "   Analysed DB links for: $_[0] \n"; }
    return(@DBreturn);
} # end of DBLinkInfo

##############################################################################################

sub ReadIOFile($file)
{
  my($file) = shift;
  my(%data) = ();
  open(IN,"<${file}") or die "Unable to read $file\n";
  while(<IN>)
  {
    chomp;
    print "Reading $_\n" if ($debug);
    s/#.*//g;
    s/\s*(\S+)\s*/$1/g;
    next unless ($_);
    @rowdata = split(/:/,$_);
      ($discard, $levelvalue) = split(/=>/, shift(@rowdata));
      ($discard, $tablevalue) = split(/=>/, shift(@rowdata));
      $data{$levelvalue} = $tablevalue unless (@rowdata);
      next unless (@rowdata);
      ($discard, $constraintvalue) = split(/=>/, shift(@rowdata));
      ($rest_of_data = join(':', (@rowdata)) );
      $data{$levelvalue}{$tablevalue}{$constraintvalue} = $rest_of_data;
  }
  close(IN);
  return(%data);
} # end of ReadIOFile

##############################################################################################

sub MultiParentHandler($action,%hierarchy)
{
  my($action) = shift;
  my(%hierarchy) = @_;
  my($FileRow,@TableData,$level,$genericheader,@return);
  chomp(my $D8 = `date`);
  chomp(my $USER = `env | grep LOGNAME | cut -f2 -d=`);  
    
  if ($#{$hierarchy{0}} > 0) { print "You arent using this subroutine correctly\n"; return; }  
  
  if ($action eq 'new') {
  
    %main::MultiTblDets = &MultiParentHandler('load'); 
    qx!rm -f $InputOutputFile_2! if (-e $InputOutputFile_2);
    
    open(MULTIPARENT,">$MultiParentlog") or die "Cant write to $MultiParentlog";
    print MULTIPARENT $header1, $header_a, $header2;
    close(MULTIPARENT);
    
    open(IOFILE_2,">$InputOutputFile_2") or die "Cant write to $InputOutputFile_2";
    print IOFILE_2 $header1, $header_b, $header2;
    close(IOFILE_2);
    return(%MultiTblDets);
    
  }elsif ($action eq 'load') {
  
    $genericheader = "\n-------------------------- Multiple-Table Session --------------------------\n\n";
    $genericheader = "\n-------------------------- Multiple-Table Session --------------------------\n\n";

    $header1  = "# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #\n";
    $header_a = "# # # # # # # # # # # # # # Multiple Parent Log # # # # # # # # # # # # # #\n";
    $header_b = "# # # # # # # # # # # # # # Input-Ouput File II # # # # # # # # # # # # # #\n";
    $header2  = "# Created by: $USER\n" .
                "# On: $D8\n" .
                "# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #\n";
    $MultiTblDets{'io2'}{'header'} = $header1 . $header_b . $header2;
    $MultiTblDets{'io2'}{'separator'} = [ 'PARENT','STATUS' ];
    return(%MultiTblDets);
    
  }elsif ($action eq 'add') {

      # There should only be one parent - level 0
    my $parent = $hierarchy{0}[0];  

      ## add this to the multi-parent log and an entry into iofile2
    open(MULTIPARENT,">>$MultiParentlog") or die "Cant append to $MultiParentlog";
    open(IOFILE_2,">>$InputOutputFile_2") or die "Cant append to $InputOutputFile_2";
    
    print MULTIPARENT "# Table Data Start:$parent\n";
    
    foreach $level (sort keys(%hierarchy)) {
      print MULTIPARENT "LEVEL => $level : TABLES => ", join(',', @{$hierarchy{$level}}), "\n";
      print "Parent: $parent : Hierarchy level $level = ", join(',', @{$hierarchy{$level}}), "\n" if ($debug);
      #LogData("Hierarchy level $level = $FailedHierarchy{$level}",$Progress);
    }   
    print MULTIPARENT "# Table Data End:$parent\n";
    print MULTIPARENT "# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #\n";
    
    print IOFILE_2 "PARENT => $parent : STATUS => Unstarted\n";
    
    close(MULTIPARENT);
    close(IOFILE_2);
    
  }elsif ($action eq 'reload') {
  
    # Check which parent were doing next.
    my($parent);
    %{$MultiTblDets{'state'}} = &ReadIOFile($InputOutputFile_2);
    foreach (sort keys(%{$MultiTblDets{'state'}})) {
      print "\'load\' state -> \$_=$_ - contents -> $MultiTblDets{'state'}{$_}\n" if ($debug > 1);
      if ($MultiTblDets{'state'}{$_} =~ /Unstarted/i) {
        $parent = $_;
        last;
      }
    }
    
    if (! $parent) {
      # No more unstarted ones:
      # 1. check that there arent any skipped ones
      # 2. if so tell user to redo these
      # 3. reset all of them to Unstarted and exit cleanly
      print $genericheader;
      print "\tAll Tables Have now been processed.\n";
      LogData("All Tables Have now been processed",$Progress);
      if (@rv = &MultiParentHandler('check')) {
        print "\t User skipped resynchronisation of: @rv\n";
        print "\t It would be advisable to re-run the process\n\n";
        LogData("User skipped resynchronisation of: @rv",$Progress);
      }
      return(0);
    }
    
      ## read from multi-parent log
    open(MULTIPARENT,"<$MultiParentlog") or die "Cant read $MultiParentlog";
    print "Reading $MultiParentlog\n" if ($debug);
    my ($keep) = 0;
    while ($FileRow = <MULTIPARENT>) {
      $keep++ if ($FileRow =~ /Table Data Start\:$parent/);
      next unless ($keep);
      last if ($FileRow =~ /Table Data End\:$parent/);
      push(@TableData,$FileRow);
    }
    close(MULTIPARENT); 
    $TableData[0] = "# Failed Tables (Multiple Resynchronisation):\n";
    # Remove and re-write the input-output file
    chmod(0777,$InputOutputFile_1) if (-e $InputOutputFile_1);
    open(IOFILEOUT,">$InputOutputFile_1") or die "Cant write to $InputOutputFile_1";
    print IOFILEOUT "@TableData\n";
    close(IOFILEOUT);
    $MultiTblDets{'io1'}{'data'} = join('', @TableData);
    &WriteIOFile($InputOutputFile_1,'multiple',\%{$MultiTblDets{'io1'}});
    $MultiTblDets{'state'}{$parent} = 'Started';
    $MultiTblDets{'current'} = $parent;
    &WriteIOFile($InputOutputFile_2,'multiple',\%{$MultiTblDets{'io2'}},%{$MultiTblDets{'state'}});
    return(1);
    
  }elsif ($action eq 'unload') {
  
    %{$MultiTblDets{'state'}} = &ReadIOFile($InputOutputFile_2);
    $parent = $MultiTblDets{'current'};
    $MultiTblDets{'state'}{$parent} = 'Finished';
    &WriteIOFile($InputOutputFile_2,'multiple',\%{$MultiTblDets{'io2'}},%{$MultiTblDets{'state'}});
    
  }elsif ($action eq 'ask_user') {
  
    print $genericheader;
    print "\tReady to check for asynchronisation of: $MultiTblDets{'current'}\n\n";
    print "\tContinue?\n\n";
    if (<STDIN> =~ m/n/i) {
      %{$MultiTblDets{'state'}} = &ReadIOFile($InputOutputFile_2);
      $parent = $MultiTblDets{'current'};
      print " Skipping resynchronisation of $parent.\n";
      LogData("User opted to skip resynchronisation of $parent.",$Progress);
      $MultiTblDets{'state'}{$parent} = 'Skipped';
      &WriteIOFile($InputOutputFile_2,'multiple',\%{$MultiTblDets{'io2'}},%{$MultiTblDets{'state'}});
      return(1);
    }
  
  }elsif ($action eq 'check') {
  
    @return=();
    foreach (sort keys(%{$MultiTblDets{'state'}})) {
      print "\'check\' state -> \$_=$_ - contents -> $MultiTblDets{'state'}{$_}\n" if ($debug > 1);
      push(@return,$_) if ($MultiTblDets{'state'}{$_} =~ /Skipped/i);
    }
    print "\'check\' \@return=@return\n" if ($debug > 1);    
    return(@return);
    
  }elsif ($action eq 'finish') {
  
    %{$MultiTblDets{'state'}} = &ReadIOFile($InputOutputFile_2);
    unlink("$InputOutputFile_2.old") if (-e "$InputOutputFile_2.old");
    rename("$InputOutputFile_2","$InputOutputFile_2.old");
    foreach (sort keys(%{$MultiTblDets{'state'}})) {
      print "\'finish\' state -> \$_=$_ - contents -> $MultiTblDets{'state'}{$_}\n" if ($debug);
      $MultiTblDets{'state'}{$_} = 'Unstarted';
    }
    &WriteIOFile($InputOutputFile_2,'multiple',\%{$MultiTblDets{'io2'}},%{$MultiTblDets{'state'}});
  }
  
} # end of MultiParentHandler

##############################################################################################

sub WriteIOFile($iofile,$type,$extra_info,%IOHash)
{
  my($iofile) = shift;
  my($type) = shift;
  my($extra_info) = shift;
  my(%IOHash) = @_;
  my($header,@separator,$parenttable,$level);
  
  if ($debug > 1) {
    foreach (keys (%IOHash)) { 
      print "WriteIOFile->\$IOHash{$_} = $IOHash{$_}\n"; 
    }  
    print "WriteIOFile->\$\$extra_info{'data'}=$$extra_info{'data'}\n";
    print "WriteIOFile->\$\$extra_info{'header'}=$$extra_info{'header'}\n";
    print "WriteIOFile->\@separator=@separator\n";
    foreach (keys (%{$extra_info})) { 
      print "WriteIOFile->\$extra_info{$_} = $extra_info->{$_}\n" unless (/^(data|header|separator)$/); 
    }
  }
  
  chmod(0777,$iofile) if (-e $iofile);
  open(IOFILE,">$iofile") or die "Cant write to $iofile\n";
  if ((! $type) or ($type eq 'normal')) {
    $header = "# Input-Output File: $iofile\n" . "# Failed Tables:\n";
    @separator = ('LEVEL','TABLES');
  }elsif($type eq 'multiple') {
    $header = $$extra_info{'header'};
    for (@$extra_info{'separator'}) { @separator = @$_; }
  }else{
    print "Incorrect use of \&WriteIOFile\n"; return();
  }
  
  if ($$extra_info{'data'}) {
    print IOFILE $$extra_info{'data'};     
  }else{
    print IOFILE $header;
  
    foreach $parenttable (sort keys(%IOHash)) {
      if ($type eq 'multiple') {
        print IOFILE "$separator[0] => $parenttable : $separator[1] => $IOHash{$parenttable}\n";
      }
      foreach $level (sort keys(%{$IOHash{$parenttable}})) {
        print "Writing IO File Hierarchy level $level = @{$IOHash{$parenttable}{$level}}\n" if ($debug);
        LogData("Hierarchy level $level = @{$IOHash{$parenttable}{$level}}",$Progress);
        #print IOFILE "LEVEL => $level : TABLES => ", join(',', @{$IOHash{$parenttable}{$level}}), "\n";
        print IOFILE "$separator[0] => $level : $separator[1] => ", 
                      join(',', @{$IOHash{$parenttable}{$level}}), "\n";
      }
    }
  }
  close(IOFILE);
  chmod(0444,$iofile);
  return();  
} # end of WriteIOFile

##############################################################################################

sub AnalyseHierarchy(@RawTables)
{
  ###### This section iterates through the failed table list
  ###### and finds the parent, children, and grandchildren if they exist

  my(@RawTables) = @_;
  my(%Parent_List,$ParentTable) = ();

  print"Finding Hierarchy Levels\n\n";

  @RemainingTables = @RawTables;
  
  # Do parent, child, grandchild
  print "Finding Parent Tables\n";
  LogData("Finding Parent Tables from @RawTables",$Progress);
  @ParentList = findRelationTable('parent',@RawTables);
  
  while ($ParentTable = shift(@ParentList)) {
    	## Check that these dont have grandparents
     my(@GrandParentTable) = ();
     print "Checking for Grandparents of $ParentTable ";
     @GrandParentTable = findRelationTable('grandma',$ParentTable) unless ($ParentTable eq 'BV_COLLECTION');
    	# we know that we can resynch from bv_collection downwards 
    	# even though it has greatgrandparents    
     if ($GrandParentTable[0]) {
        print "\nFound Grandparent: @GrandParentTable\n";
        LogData("Found Grandparent for $ParentTable: @GrandParentTable",$Progress);         
          # One grandparent is fine - multiple is not good     	
        if ($GrandParentTable[1]) {
           print "Got multiple Grandparents!!!\n" .
                 "Unable to determine which one to use. This must be done manually using the:\n" .
                 "\t\t $0 -t $ParentTable \n option.\n";
           LogData("Found Multiple Grandparents for $ParentTable - Removing and continuing.",$Progress);
           @ParentList = reduceArray($ParentTable,@ParentList);
           next;
        }      
        push(@ParentList,$GrandParentTable[0]);
        next;
     }else{
        print "- No\n";
     }
     
     @{$FailedHierarchy{$ParentTable}{'0'}} = $ParentTable;
     @ChildTables = findRelationTable('child',$ParentTable);
     @{$FailedHierarchy{$ParentTable}{'1'}} = @ChildTables if (@ChildTables);
     foreach $child (@ChildTables) {
        @GrandChildTables = findRelationTable('child',$child);
        @{$FailedHierarchy{$ParentTable}{'2'}} = @GrandChildTables if (@GrandChildTables);
        foreach $grandchild (@GrandChildTables) {
          ## Just check we havent got great grand-children
          @GreatGrandChildTables = findRelationTable('child',$grandchild);
          @{$FailedHierarchy{$ParentTable}{'3'}} = @GreatGrandChildTables if (@GreatGrandChildTables);
          @RemainingTables = reduceArray($grandchild,@RemainingTables); # Keep a tab on what we have left
        }
        @RemainingTables = reduceArray($child,@RemainingTables); # Keep a tab on what we have left
     }
     @RemainingTables = reduceArray($ParentTable,@RemainingTables); # Keep a tab on what we have left
  }
  
  # Now @ReducedArray should be totally empty
  if (@RemainingTables) {
    print "These tables were not correctly analysed: @RemainingTables\n" .
          "Please run:\n";
    LogData("These tables failed but were not correctly analysed: @RemainingTables",$Progress);
    foreach (@RemainingTables) { 
      print "\t\t $0 -t $_\n";
    }
    print "To analyse these.\n\n";
  }

  @Parent = sort keys(%FailedHierarchy);
     
  if ($#Parent > 0) {
     $message = "Starting multi-table session";
     print "$message\n";
     LogData("$message",$Progress);
     
     &MultiParentHandler('new');
     foreach $parent (sort keys(%FailedHierarchy)) {
       &MultiParentHandler('add',%{$FailedHierarchy{$parent}});
     }

  }else{
     print"Parent Table : $Parent[0]\n";
     LogData("Parent Table is: $Parent[0]",$Progress);
     &WriteIOFile($InputOutputFile_1,'normal','',%FailedHierarchy);
  }

  $Progress = "Found Parent Child Linkages";
  print "$Progress\n";

  if ((-s "$InputOutputFile_1" or (-s "$InputOutputFile_2" and -s $MultiParentlog))
       && (%FailedHierarchy) && $Parent[0])
  {
	$status = 0; 
  	LogData("Program Finishing awaiting user consent to resynchronise tables listed in Input-Output log",$Progress);
	print "\n----------------------------- $MethodName - Results -----------------------------\n\n";
  	print "\tResults written to : $InputOutputFile_1 \n" if (-e $InputOutputFile_1);
  	print "\tResults written to : $InputOutputFile_2 and \n",
  	      "\t                     $MultiParentlog \n" if (-e $InputOutputFile_2);
  	print "\t (You should check ", (-e $InputOutputFile_1) ? 'this file' : 'these files',
  	      " before carrying on)\n\n";
  	print "\t- Modifying the contents could put us in a whole load of trouble -\n";
  	print "\t  As it contains essential parent-child linkage information.\n\n";
  	print "\tRun:\t $0 -r\n\t  to begin resynchronisation of these tables.\n\n";
  	print "\t\t(This will prompt before making any changes to the database)\n\n";

  }else{ 
	$status = 1; 
  	print " Problem with Analysis\n  Exit Status : 1\tCheck and re-run\n";
  	LogData("Program Finishing Problem with Analysis",$Progress);
  }
  return($status,%FailedHierarchy);
} # end of AnalyseHierarchy

##############################################################################################

sub MergeArrays(@Array1,@Array2)
{
  my(@CompleteList) = @_;
  my(%seen,@uniq) = ();
     foreach $table (@CompleteList)
     {
	push(@uniq, $table) unless $seen{$table}++;
     }
  @MergedArray = @uniq;
  return(@MergedArray);
} # end of MergeArrays

##############################################################################################

sub stageloaderrors()
{
  # This gets the errors from the latest stage_load log #
  # Which are detectable by the 3 !!! marks
  $linecnt = 0;
  open(LOG,"<${LatestStgTlLoadLog}");
  @Log = (<LOG>);
  chomp(@Log);
        foreach (@Log)
        {
                chomp;
                $linecnt++;
               	push(@errorreturn, $Log[$linecnt], $Log[$linecnt + 1], $Log[$linecnt + 2]) if /!!!/g;
        }
  return(@errorreturn);
  close(LOG);
} # end of stageloaderrors

##############################################################################################

sub findRelationTable($g_p_or_c,@unquotedtablelist)
{ 
  my ($g_p_or_c) = shift;
  my (@unquotedtablelist) = @_;
  my (@TableList,@DBreturn) = ();
  foreach (@unquotedtablelist) {
	push(@TableList,"\'$_\'");
  }
  
  print "findRelationTable -> finding \'$g_p_or_c\' from: @TableList\n" if ($debug > 1);
  
  @TableList = join(",", @TableList);

  if ($g_p_or_c =~ /parent/)
  {
	LogData("Finding Parents from: @TableList",$Progress);
	$query = 	"\t\tSELECT distinct TABLE_NAME
		  FROM USER_CONSTRAINTS
		  WHERE TABLE_NAME in (@TableList)
		  AND CONSTRAINT_TYPE in (\'P\',\'R\')
		  AND TABLE_NAME not in 
			   	  (SELECT TABLE_NAME
				   FROM USER_CONSTRAINTS
				   WHERE R_CONSTRAINT_NAME in		   				   
	   		 		    (SELECT CONSTRAINT_NAME
					     FROM USER_CONSTRAINTS
					     WHERE TABLE_NAME in (@TableList))   
				  )"; 

  }elsif ($g_p_or_c =~ /child/){
    	LogData("Finding Children of: @TableList",$Progress);
    	$query =      "\t\tSELECT distinct TABLE_NAME
                  FROM USER_CONSTRAINTS
                  WHERE R_CONSTRAINT_NAME in
                                  (SELECT CONSTRAINT_NAME
                                   FROM USER_CONSTRAINTS
                                   WHERE TABLE_NAME in (@TableList))";

  }elsif ($g_p_or_c =~ /grandma/){
     ## Only run this for one at a time ##
    	LogData("Checking for Grandparent from: @TableList",$Progress);
    	$query =      "\t\tSELECT distinct TABLE_NAME
                  FROM USER_CONSTRAINTS
                  WHERE CONSTRAINT_NAME in
                                  (SELECT R_CONSTRAINT_NAME
                                   FROM USER_CONSTRAINTS
                                   WHERE TABLE_NAME in (@TableList))";  
  }
    print $query, "\n" if ($debug);

    $sth = $dbh->prepare("$query");
    $sth->execute or die "Failed to perform SQL statement ... \n";    
    while ($result  = $sth->fetchrow)
    {
        print "findRelationTable <- \'$g_p_or_c\' found $result\n" if ($debug > 1);
        push(@DBreturn,$result);
    }
    $sth->finish();
    return(@DBreturn);
} # end of findRelationTable

##############################################################################################

sub LogData($message,$Progress)
{
  my($message,$Progress) = @_;
  $TIME = scalar(localtime);
  &BVCustomPerl::LogMessage($message, $LogFile, $Progress, $user, $TIME);
  die "Cant write to ${LogFile}\n" unless -w "${LogFile}";
} # end of LogData

##############################################################################################

sub reduceArray($remove,@allTables)
{
  my($remove) = shift;
  my(@allTables) = @_;
  my(@ReducedArray) = ();
  foreach(@allTables)
  {
	push(@ReducedArray,$_) unless /^$remove$/;
  }
  return(@ReducedArray);
} # end of reduceArray

########################################## The END ###########################################