#!perl

#--
#-- DESCRIPTION:
#-- This is a script attempting to collect data from the razor DB based off of status
#--

use strict;

use Spreadsheet::ParseExcel::Simple;
use Config;
use Tk;
use Tk::Button;
use Tk::DialogBox;
use Tk::DirSelect;
use Tk::FBox;
use Net::FTP;

my $MSGLOG; #-- Log file handle

#-- THESE ARE THE COLUMNS THAT DATA IS LOCATED IN THE NSWCDD SCP LOGBOOK
#-- IF THE COLUMNS CHANGE IN THE SCP LOGBOOK, THESE ARE THE NUMBERS TO UPDATE!
my $pcmsidColumn = 0; #-- Titled "Change Doc ID" in the SCP logbook
my $statusColumn = 9; #-- Titled "Sub Status" in the SCP logbook
my $stateColumn = 11; #-- Titled "State" in the SCP logbook
my $scpColumn = 8;    #-- Titled "CAR No" in the SCP logbook

#-- THESE ARE THE COLUMNS THAT DATA IS LOCATED IN THE NSWCDD SCR LOGBOOK
#-- IF THE COLUMNS CHANGE IN THE SCR LOGBOOK, THESE ARE THE NUMBERS TO UPDATE!
my $scrPcmsidColumn = 0; #-- Titled "Change Doc ID" in the SCR logbook
my $scrColumn = 3;       #-- Titled "CAR No" in the SCR logbook
my $scrStatusColumn = 4; #-- Titled "Sub Status" in the SCR logbook

my $logFilePath     = "C\:\\"; #-- Default path to store the results of this script in a log file
my $tempPath        = "C\:\\"; #-- Default path to store SCPs and SCRs locally
my $spreadsheetPath = 'C:\\';  #-- Default path for the logbook provided by NSWCDD
my $hostname = "fbm240-1";     #-- Default name of the host to retrieve SCPs from
my $username = "";             #-- Username to log into the above host with
my $password = "";             #-- Password for the above username
my $remoteScpPath = '/home/dev2/razadm/RAZOR_CM/RAZOR_UNIVERSE/PRs/'; #-- Default path SCPs are stored in on the remote host

my @scpStatus;
my $scpStatusCount = 0;
my @scrStatus;
my $scrStatusCount = 0;

#-- Variables to store the start and finish execution times of this script
my ($ssec,$smin,$shour,$smday,$smon,$syear,$swday,$syday,$sisdst);
my ($fsec,$fmin,$fhour,$fmday,$fmon,$fyear,$fwday,$fyday,$fisdst);

#--
#-- printLog: Function to output messages to the shell and logfile
#--
sub printLog {

   my ($msg) = @_;
   
   #--
   #-- DISPLAY THE MESSAGE TO THE LOG FILE AND THE SHELL
   #--
   print $MSGLOG $msg;
}

#--
#--
#-- THIS IS THE MAIN HERE
#--
#--

#-- Verify that this script is running under windows, if not then exit
unless ($Config{osname} eq "MSWin32")
{
  print "Sorry, this script only supports 32-bit Microsoft Windows!\n";
  
  #-- Give the operator time to see the message before exiting!
  print "\n\nPRESS ENTER TO CONTINUE\n";
  my $target = <STDIN>;
}

#-- Create the main window
my $mw = MainWindow->new(-title => 'Log Book Validate');

my $row = 0;
my $column = 0;

#-- Create the text box to enter the path to store the execution log, and provide a directory selection button
$mw->Label(-text => "Log Directory")->grid(-row => $row, -column => $column++);
$mw->Entry(-textvariable => \$logFilePath, -width => 100)->grid(-row => $row, -column => $column++);
my $lds = $mw->DirSelect();
my $LogDirButton = $mw->Button(-text => "...",
            -command => sub { $logFilePath = $lds->Show();	                 
            })->grid(-row => $row, -column => $column++);

$row++;
$column = 0;

#-- Create the text box to enter the local directory to store SCPs, and provide a directory selection button
$mw->Label(-text => "Temp Directory")->grid(-row => $row, -column => $column++);
$mw->Entry(-textvariable => \$tempPath, -width => 100)->grid(-row => $row, -column => $column++);
my $sds = $mw->DirSelect();
my $LogDirButton = $mw->Button(-text => "...",
            -command => sub { $tempPath = $sds->Show();	                 
            })->grid(-row => $row, -column => $column++);

$row++;
$column = 0;

#-- Create the text box to enter the path and name of the logbook, and provide a file selection button
$mw->Label(-text => "Logbook Name")->grid(-row => $row, -column => $column++);
$mw->Entry(-textvariable => \$spreadsheetPath, -width => 100)->grid(-row => $row, -column => $column++);
my $LogDirButton = $mw->Button(-text => "...",
            -command => sub { $spreadsheetPath = $mw->FBox(-filetypes => [['Excel Files', ['*.xls']]])->Show;	                 
            })->grid(-row => $row, -column => $column++);
            
$row++;
$column = 0;

#-- Add text boxes to enter the remote host to retrieve SCPs from, the username and password
my $firstFrame = $mw->Frame()->grid(-row => $row, -column => $column, -columnspan => 3);

$firstFrame->Label(-text => "Hostname")->grid(-row => $row, -column => $column++);
$firstFrame->Entry(-textvariable => \$hostname, -width => 25)->grid(-row => $row, -column => $column++);

$firstFrame->Label(-text => "Username")->grid(-row => $row, -column => $column++);
$firstFrame->Entry(-textvariable => \$username, -width => 25)->grid(-row => $row, -column => $column++);

$firstFrame->Label(-text => "Password")->grid(-row => $row, -column => $column++);
$firstFrame->Entry(-textvariable => \$password, -show => "*", -width => 25)->grid(-row => $row, -column => $column++);

$row++;
$column = 0;

#-- Add a text box to enter the remote path to the SCPs
my $secondFrame = $mw->Frame()->grid(-row => $row, -column => $column, -columnspan => 3);

$secondFrame->Label(-text => "Remote SCP Path")->grid(-row => $row, -column => $column++);
$secondFrame->Entry(-textvariable => \$remoteScpPath, -width => 100)->grid(-row => $row++, -column => $column--);

$secondFrame->Label(-text => "Remote SCR Path")->grid(-row => $row, -column => $column++);
$secondFrame->Entry(-textvariable => \$remoteScrPath, -width => 100)->grid(-row => $row, -column => $column);

$row++;
$column = 0;

#-- Create the buttons to process the logbook, and to exit
my $buttonFrame = $mw->Frame()->grid(-row => $row, -column => $column, -columnspan => 3);

my $executionOption = "SCP";
$buttonFrame->Label(-text => "Logbook type to process?")->grid(-row => $row, -column => $column++);
my $scpRadioButton = $buttonFrame->Radiobutton(-text => "SCP", -variable => \$executionOption, -value => "SCP")->grid(-row => $row, -column => $column++);
my $scrRadioButton = $buttonFrame->Radiobutton(-text => "SCR", -variable => \$executionOption, -value => "SCR")->grid(-row => $row, -column => $column++);

my $GoButton = $buttonFrame->Button(-text => "Validate",
            -command => sub { openLog($executionOption);
            	              if ($executionOption eq "SCP")
            	              { 
            	              	doScpValidate();
            	              }
            	              else {
            	              	doScrValidate();
            	              } 
            	              closeLog();	                 
            })->grid(-row => $row, -column => $column++);
                      
my $ExitButton = $buttonFrame->Button(-text => "Exit",
            -command => sub { #Clear the username and password variables, then exit
							  $username =~ s/(.*)/x/g;
							  $password =~ s/(.*)/x/g;
							  exit;
            })->grid(-row => $row, -column => $column++);

MainLoop;

#--
#-- doScpValidate: Function to validate an SCP logbook against SCPs.
#--
sub doScpValidate {
	print "Processing SCPs...\n";

    #-- Open and read the logbook
	my $xls = Spreadsheet::ParseExcel::Simple->read($spreadsheetPath) or postLogbookOpenFailDialog($spreadsheetPath);

	#-- Change to path to where SCPs should be stored locally.
	chdir $tempPath;
	
	#-- Create an FTP connection to the remote host for retrieving SCPs	
	my $ftp = Net::FTP->new($hostname) or postFtpHostnameFailDialog ($hostname);
	$ftp->login($username, $password) or postUserPasswdFailDialog($username);
	$ftp->cwd($remoteScpPath) or postFtpPathFailDialog($remoteScpPath);

    #-- Initialize variables for the summary of the number of...
	my $matchedPcmsidScp = 0;       #-- Matched PCMS IDs in SCPs
	my $unmatchedPcmsidScp = 0;     #-- Unmatched PCMS IDs in SCPs
	my $mismatchedCancelledScp = 0; #-- Logbook cancelled entries that don't match SCPs
	my $mismatchedDeletedScp = 0;   #-- Logbook deleted entries that don't match SCPs
	my $mismatchedApprovedScp = 0;  #-- Logbook approved entries that don't match SCPs
	my $scpFileErrors = 0;          #-- Errors opening SCPs, i.e. not found SCPs
	
	#-- Output to tab delimited log file the header for the first set of checks
	printLog "SCP\tPCMS ID\tPCMS ID MATCH\tPCMSID FOUND\tSCP STATUS\tLOGBOOK STATUS\tMATCH\n";

    #-- Process each sheet in the logbook that has data, there most likely will only be one
	foreach my $sheet ($xls->sheets) {
		while($sheet->has_data) {
			my @currentLine;
		
		    #-- Get the current line of data, one column from the spreadsheet in each array element
			@currentLine = $sheet->next_row;
		
			#-- PCMS_ID has "FCARCH_" prepended on it, store everything after FCARCH_
			my $pcmsid = 0;
			if ($currentLine[$pcmsidColumn] =~ m/(FCARCH_)(.*)/) {
				$pcmsid = $2;
			}
			else {
				$pcmsid = $currentLine[$pcmsidColumn];
			}
			#-- Status is APPROVED|CANCELLED|DELETED
			my $status = $currentLine[$statusColumn];
			#-- State is APPROVED|CANCELLED|COMPLETED|DELETE
			my $state = $currentLine[$stateColumn];
		
		    #-- Check to see if the CAR No contains an SCP
		    #-- The last line of the conditional is to make sure that we are not processing something 
		    #-- that looks like an SCP, but isn't.
			if (($currentLine[$scpColumn] =~ m/(.*)(\(\S\.\.\.\-\d\d\d)(.*)/ ||
				 $currentLine[$scpColumn] =~ m/(.*)(\(\S\.\.\d\-\d\d\d)(.*)/ ||
				 $currentLine[$scpColumn] =~ m/(.*)(\(\S\.\d\d\-\d\d\d)(.*)/ ||
				 $currentLine[$scpColumn] =~ m/(.*)(\(\S\d\d\d\-\d\d\d)(.*)/) &&
				($currentLine[$scpColumn] !~ m/(.*)(\(\S0\d\d-\d\d\d)(.*)/)) {
				my $scp = $2;
				$scp = substr($scp, 1, 8);
				print "$scp processing\n";
				
				#-- Store SCP and Status from the logbook for searching for
				#-- duplicate SCP entries afterwards
				$scpStatus[$scpStatusCount]{scp} = $scp;
				$scpStatus[$scpStatusCount]{status} = $status;
				$scpStatus[$scpStatusCount]{evaluated} = 0;
				$scpStatus[$scpStatusCount]{state} = $state;
				$scpStatusCount++;
				
				#-- Download the SCP from the remote host
				$ftp->get("$scp.html") or postScpGetFailDialog($scp);
			
			    #-- Open the downloaded SCP
				my $SCPFILE;
				if (!open($SCPFILE, "+<$tempPath\\$scp.html"))
				{ 
					postScpOpenFailDialog($scp);
					$scpFileErrors++;
				}
				else
				{
					#-- Read the entire SCP into an array
					my @scplines = <$SCPFILE>;
					my $scpline;
					my $matchFound = 0;
				
				    #-- Check each line of the SCP for the matching PCMS ID
					foreach $scpline (@scplines)
					{
						if ($scpline =~ m/(.*)(PCMS\_ID\: $pcmsid)(.*)/) {
							$matchFound = 1;
							last;
						}
					}
				
				    #-- If we found a matching PCMS ID, then increment the match counter
					if ($matchFound == 1) {
						#-- Uncomment the following line to add SCR/PCMSID matches to the log
						#printLog "$scp\t$pcmsid\tMATCH\n";
						$matchedPcmsidScp++;
					}
					#-- If we didn't find a matching PCMS ID, then increment the unmatched counter
					else {
						printLog "$scp\t$pcmsid\tNO MATCH";
						$unmatchedPcmsidScp++;
						
						#-- Dump to the execution log the PCMS ID that was found instead
						foreach $scpline (@scplines)
						{
							if ($scpline =~ m/(.*)(PCMS\_ID\: )(.*)(\<\/FONT\>\<\/TD\>\<\/TR\>)(.*)/) {
								if ($3 eq "") {printLog "\tNothing";}
								else {printLog "\t$3";}
								printLog "\t \t$scpStatus[$scpStatusCount-1]{status}\n";
								last;
							}
						}
					}
					
					#-- Save the SCP substatus and compare it to the status in the logbook.
					#-- If the SCP is CANCELLED, DELETED or APPROVED in the logbook, then the
					#-- SCP should be CANCELLED, DELETED or Lib_Working, APPROVED, COMPLETED.
					my $scpStatus = "";
					$matchFound = 0;
					foreach $scpline (@scplines)
					{
						if ($scpline =~ m/(.*)(SUBSTATUS\/DATE\<\/FONT\>\<\/B\> \<BR\>\<FONT )(.*)(\>)(.*)(\s+)(\/)/) {
							$matchFound = 1;
							$scpStatus = $5;
							last;
						}			
					}
					
					#-- If we found the substatus in the SCP
					if ($matchFound == 1){
						#-- Make sure the logbook and SCP match CANCELLED
						if ((($scpStatus[$scpStatusCount-1]{state} eq "CANCELLED") ||
						     ($scpStatus[$scpStatusCount-1]{state} eq "CANCELLED ")) &&
						    ($scpStatus ne "CANCELLED")){
							printLog "$scp\t \t \t \t$scpStatus\tCANCELLED\tNO\n";
							$mismatchedCancelledScp++;
						}
						#-- Make sure the logbook and SCP match DELETED
						elsif ((($scpStatus[$scpStatusCount-1]{state} eq "DELETE") ||
						        ($scpStatus[$scpStatusCount-1]{state} eq "DELETE ")) &&
						       ($scpStatus ne "DELETED")){
							printLog "$scp\t \t \t \t$scpStatus\tDELETED\tNO\n";
							$mismatchedDeletedScp++;
						}
						#-- Make sure the logbook APPROVED/COMPLETED matches SCP for APPROVED/COMPLETED/WORKING
						elsif ((($scpStatus[$scpStatusCount-1]{state} eq "APPROVED") ||
						        ($scpStatus[$scpStatusCount-1]{state} eq "APPROVED ") ||
						        ($scpStatus[$scpStatusCount-1]{state} eq "COMPLETED") ||
						        ($scpStatus[$scpStatusCount-1]{state} eq "COMPLETED ")) &&
						       (($scpStatus ne "LIB_WORKING") && 
						        ($scpStatus ne "APPROVED") && 
						        ($scpStatus ne "COMPLETED"))) {
							printLog "$scp\t \t \t \t$scpStatus\t$scpStatus[$scpStatusCount-1]{state}\tNO\n";
							$mismatchedApprovedScp++;
						}
						#-- Do the reverse check to find logbook entries that do not match
						#-- CANCELLED, DELETED, or APPROVED SCPs
						#-- Make sure the logbook and SCP match CANCELLED
						elsif ((($scpStatus[$scpStatusCount-1]{state} ne "CANCELLED") &&
						        ($scpStatus[$scpStatusCount-1]{state} ne "CANCELLED ")) &&
						       ($scpStatus eq "CANCELLED")){
							printLog "$scp\t \t \t \tCANCELLED\t$scpStatus[$scpStatusCount-1]{state}\tNO\n";
							$mismatchedCancelledScp++;
						}
						#-- Make sure the logbook and SCP match DELETED
						elsif ((($scpStatus[$scpStatusCount-1]{state} ne "DELETE") &&
						        ($scpStatus[$scpStatusCount-1]{state} ne "DELETE ")) &&
						       ($scpStatus eq "DELETED")){
							printLog "$scp\t \t \t \tDELETED\t$scpStatus[$scpStatusCount-1]{state}\tNO\n";
							$mismatchedDeletedScp++;
						}
						#-- Make sure the logbook APPROVED/COMPLETED matches SCP for APPROVED/COMPLETED/WORKING
						elsif ((($scpStatus[$scpStatusCount-1]{state} ne "APPROVED") &&
						        ($scpStatus[$scpStatusCount-1]{state} ne "APPROVED ") &&
						        ($scpStatus[$scpStatusCount-1]{state} ne "COMPLETED") &&
						        ($scpStatus[$scpStatusCount-1]{state} ne "COMPLETED ")) &&
						       (($scpStatus eq "LIB_WORKING") || 
						        ($scpStatus eq "APPROVED") || 
						        ($scpStatus eq "COMPLETED"))) {
							printLog "$scp\t \t \t \t$scpStatus\t$scpStatus[$scpStatusCount-1]{state}\tNO\n";
							$mismatchedApprovedScp++;
						}
					}
					else {
						#-- Create a log message since we couldn't find status in the SCP
						printLog "$scp\t \t \t \tNO STATUS FOUND\t \tNO\n";
					}
				}
				close $SCPFILE;
			}
		}
	}
	
	$ftp->quit();

	printLog "\n\n\n";
	printLog "SCP\tNUM DUPLICATES\tNUM CANCELLED\tNUM DELETED\tNUM NOT CANCELLED OR DELETED\n";

	my $currentScp = 0;
	my $duplicatesCancelled = 0;
	my $duplicatesDeleted = 0;
	my $duplicatesNotCancelledDeleted = 0;

    #-- Go through all of the SCPs in the logbook and look for duplicate entries
	for ($currentScp = 0; $currentScp < $scpStatusCount; $currentScp++) {
		if ($scpStatus[$currentScp]{evaluated} == 1){
			#-- Skip this one, it may be a duplicate and we have covered it.
		}
		else {
			#-- Mark the current SCP as evaluated
			$scpStatus[$currentScp]{evaluated} = 1;
		
			my $nextScp = 0;
			my $cancelledFound = 0;
			my $deletedFound = 0;
			my $duplicateFound = 0;
			my $cancelledCount = 0;
			my $deletedCount = 0;
			
			if ($scpStatus[$currentScp]{status} eq "CANCELLED"){
				$cancelledCount++;
			}
			elsif ($scpStatus[$currentScp]{status} eq "DELETED"){
				$deletedCount++;
			}
			
			#-- Compare the current SCP to all of the remaining SCPs
			for ($nextScp = $currentScp + 1; $nextScp < $scpStatusCount; $nextScp++){
				
				#-- Check to see if the current SCP number matches the SCP number in the next entry
				if ($scpStatus[$currentScp]{scp} eq $scpStatus[$nextScp]{scp}) {
					
					#-- Set the next SCP to evaluated, and indicate that a duplicate was found
					$scpStatus[$nextScp]{evaluated} = 1;
					$duplicateFound++;
				
				    #-- If either SCP is marked as CANCELLED, indicate that a cancelled duplicate was found
					if ($scpStatus[$currentScp]{status} eq "CANCELLED" ||
						$scpStatus[$nextScp]{status} eq "CANCELLED"){
							
						$cancelledFound = 1;
						if ($scpStatus[$nextScp]{status} eq "CANCELLED"){
							$cancelledCount++;
						}
					}
					
				    #-- If either SCP is marked as DELETED, indicate that a deleted duplicate was found
					if ($scpStatus[$currentScp]{status} eq "DELETED" ||
						$scpStatus[$nextScp]{status} eq "DELETED"){
							
						$deletedFound = 1;
						if ($scpStatus[$nextScp]{status} eq "DELETED"){
							$deletedCount++;
						}
					}
				}
			}

            #-- If a duplicate was found, increment the counters for cancelled and duplicate
            #-- SCPs found and log the results		
			if ($duplicateFound > 0){
				printLog "$scpStatus[$currentScp]{scp}\t$duplicateFound";
			
				if ($cancelledFound == 1 || $deletedFound == 1){
					if (($cancelledFound == 1) && ($deletedFound == 1)){
						printLog "\t$cancelledCount\t$deletedCount";
						$duplicatesCancelled += $cancelledCount;
						$duplicatesDeleted += $deletedCount;
					}
					elsif ($cancelledFound == 1){
						printLog "\t$cancelledCount\t0";
						$duplicatesCancelled += $cancelledCount;
					}
					elsif ($deletedFound == 1){
						printLog "\t0\t$deletedCount";
						$duplicatesDeleted += $deletedCount;
					}
					
					my $tempCount = $duplicateFound - ($cancelledCount + $deletedCount);
					printLog "\t$tempCount\n";
					$duplicatesNotCancelledDeleted += $tempCount;
				}
				else {
					printLog "\t0\t0\t$duplicateFound\n";
					$duplicatesNotCancelledDeleted += $duplicateFound;
				}
			}
		}
	}

    #-- Log the results of the summary counters
	printLog "\n\n$matchedPcmsidScp PCMSIDs match PCMSID in SCP.\n";
	printLog "$unmatchedPcmsidScp PCMSIDs do NOT match PCMSID in SCP.\n";
	
	if ($scpFileErrors != 0) {
		printLog "\n$scpFileErrors SCP FILE OPEN ERRORS OCCURRED!\n";
	}
	
	printLog "\n$mismatchedCancelledScp SCPs do not match logbook for CANCELLED status\n";
	printLog "$mismatchedDeletedScp SCPs do not match logbook for DELETED status\n";
	printLog "$mismatchedApprovedScp SCPs do not match logbook for APPROVED status\n";

	printLog "\n$duplicatesCancelled duplicate SCPs with cancelled marked in logbook.\n";
	printLog "$duplicatesDeleted duplicate SCPs with deleted marked marked in logbook.\n";
	printLog "$duplicatesNotCancelledDeleted duplicate SCPs without cancelled or deleted marked in logbook.\n";
}

#--
#-- doScrValidate: Function to validate an SCR logbook against SCRs.
#--
sub doScrValidate {
	print "Processing SCRs...\n";
	
	#-- Open and read the logbook
	my $xls = Spreadsheet::ParseExcel::Simple->read($spreadsheetPath) or postLogbookOpenFailDialog($spreadsheetPath);

	#-- Change to path to where SCRs should be stored locally.
	chdir $tempPath;
	
	#-- Create an FTP connection to the remote host for retrieving SCRs	
	my $ftp = Net::FTP->new($hostname) or postFtpHostnameFailDialog ($hostname);
	$ftp->login($username, $password) or postUserPasswdFailDialog($username);
	$ftp->cwd($remoteScrPath) or postFtpPathFailDialog($remoteScrPath);

    #-- Initialize variables for the summary of the number of...
	my $matchedPcmsidScr = 0;       #-- Matched PCMS IDs in SCRs
	my $unmatchedPcmsidScr = 0;     #-- Unmatched PCMS IDs in SCRs
	my $mismatchedCancelledScr = 0; #-- Logbook cancelled entries that don't match SCRs
	my $mismatchedDeletedScr = 0;   #-- Logbook deleted entries that don't match SCRs
	my $mismatchedApprovedScr = 0;  #-- Logbook approved entries that don't match SCRs
	my $scrFileErrors = 0;          #-- Errors opening SCRs, i.e. not found SCRs
	my $scrFormatErrors = 0;        #-- Errors in format of SCR number found in SCR logbook
	
	#-- Output to tab delimited log file the header for the first set of checks
	printLog "SCR\tSCR FMT ERR\tPCMS ID\tPCMS ID MATCH\tPCMSID FOUND\tSCP STATUS\tLOGBOOK STATUS\tMATCH\n";

    #-- Process each sheet in the logbook that has data, there most likely will only be one
	foreach my $sheet ($xls->sheets) {
		while($sheet->has_data) {
			my @currentLine;
		
		    #-- Get the current line of data, one column from the spreadsheet in each array element
			@currentLine = $sheet->next_row;
		
			#-- PCMS_ID has "FCARCH_" prepended on it, store everything after FCARCH_
			my $pcmsid = 0;
			if ($currentLine[$scrPcmsidColumn] =~ m/(FCARCH_)(.*)/) {
				$pcmsid = $2;
			}
			else {
				$pcmsid = $currentLine[$scrPcmsidColumn];
			}
			#-- Status is SHOPPING LIST|CANCELLED|DELETED
			my $status = $currentLine[$scrStatusColumn];

		    my $scrFound = 0;
		    my $scr = 0;
		    #-- Check to see if the CAR No contains an SCR.
		    #-- The SCR logbook is not as stable as the SCP logbook, multiple checks will be needed to 
		    #-- cover all the different ways that SCRs are found.
		    #-- First grab any SCR that is in parenthesis.
		    #-- The last line of the conditional is to make sure that we are not processing something 
		    #-- that looks like an SCR, but isn't.
			if (($currentLine[$scrColumn] =~ m/(.*)(\()(R\.\.\.\-\d\d\d)(\))(.*)/ ||
				 $currentLine[$scrColumn] =~ m/(.*)(\()(R\.\.\d\-\d\d\d)(\))(.*)/ ||
				 $currentLine[$scrColumn] =~ m/(.*)(\()(R\.\d\d\-\d\d\d)(\))(.*)/ ||
				 $currentLine[$scrColumn] =~ m/(.*)(\()(R\d\d\d\-\d\d\d)(\))(.*)/) &&
				($currentLine[$scrColumn] !~ m/(.*)(\()(R0\d\d-\d\d\d)(\))(.*)/)) {
					
			    $scrFound = 1;
				$scr = $3;
			}
			#-- If there is no SCR in parenthesis, then grab the first SCR on the line
			#-- First check to see if the line starts with an SCR
			elsif (($currentLine[$scrColumn] =~ m/(R\.\.\.\-\d\d\d)(.*)/ ||
				    $currentLine[$scrColumn] =~ m/(R\.\.\d\-\d\d\d)(.*)/ ||
				    $currentLine[$scrColumn] =~ m/(R\.\d\d\-\d\d\d)(.*)/ ||
				    $currentLine[$scrColumn] =~ m/(R\d\d\d\-\d\d\d)(.*)/) &&
				   ($currentLine[$scrColumn] !~ m/(R0\d\d-\d\d\d)(.*)/)) {
				   	
				$scrFound = 1;
				$scr = $1;
			}
			#-- Then, if the line doesn't start with an SCR check to see if the line has an SCR anywhere in it
			elsif (($currentLine[$scrColumn] =~ m/(.*)(R\.\.\.\-\d\d\d)(.*)/ ||
				    $currentLine[$scrColumn] =~ m/(.*)(R\.\.\d\-\d\d\d)(.*)/ ||
				    $currentLine[$scrColumn] =~ m/(.*)(R\.\d\d\-\d\d\d)(.*)/ ||
				    $currentLine[$scrColumn] =~ m/(.*)(R\d\d\d\-\d\d\d)(.*)/) &&
				   ($currentLine[$scrColumn] !~ m/(.*)(R0\d\d-\d\d\d)(.*)/)) {
				   	
				$scrFound = 1;
				$scr = $2;
			}
			#-- Need to see if the SCR number was just thrown in without formatting and log it.
			elsif (($currentLine[$scrColumn] =~ m/(.*)(SCR)(.*)/) &&
			       ($currentLine[$scrColumn] !~ m/(.*)(OPARCH\_OPSCR\_)(.*)/)) {
				printLog "$currentLine[$scrColumn]\tYES\n";
				$scrFormatErrors++;
			}
						
			if ($scrFound == 1) {
				print "$scr processing\n";
				
				#-- Store SCR and Status from the logbook for searching for
				#-- duplicate SCR entries afterwards
				$scrStatus[$scrStatusCount]{scr} = $scr;
				$scrStatus[$scrStatusCount]{status} = $status;
				$scrStatus[$scrStatusCount]{evaluated} = 0;
				$scrStatusCount++;
				
				#-- Download the SCR from the remote host
				$ftp->get("$scr.html") or postScpGetFailDialog($scr);
			
			    #-- Open the downloaded SCR
				my $SCRFILE;
				if (!open($SCRFILE, "+<$tempPath\\$scr.html"))
				{ 
					postScrOpenFailDialog($scr);
					$scrFileErrors++;
				}
				else
				{
					#-- Read the entire SCR into an array
					my @scrlines = <$SCRFILE>;
					my $scrline;
					my $matchFound = 0;
				
				    #-- Check each line of the SCR for the matching PCMS ID
					foreach $scrline (@scrlines)
					{
						if ($scrline =~ m/(.*)(PCMS ID \-$pcmsid)(.*)/) {
							$matchFound = 1;
							last;
						}
					}
				
				    #-- If we found a matching PCMS ID, then increment the match counter
					if ($matchFound == 1) {
						#-- Uncomment the following line to add SCR/PCMSID matches to the log
						#printLog "$scr\t \t$pcmsid\tMATCH\n";
						$matchedPcmsidScr++;
					}
					#-- If we didn't find a matching PCMS ID, then increment the unmatched counter
					else {
						printLog "$scr\t \t$pcmsid\tNO MATCH";
						$unmatchedPcmsidScr++;
						
						#-- Dump to the execution log the PCMS ID that was found instead
						foreach $scrline (@scrlines)
						{
							if ($scrline =~ m/(.*)(PCMS ID \-)(.*)(\<\/FONT\>)(.*)/) {
								if ($3 eq "") {printLog "\tNothing";}
								else {printLog "\t$3";}
								printLog "\t \t$scrStatus[$scrStatusCount-1]{status}\n";
								last;
							}
						}
					}
					
					#-- Save the SCR substatus and compare it to the status in the logbook.
					#-- If the SCR is CANCELLED, DELETED or SHOPPING LIST in the logbook, then the
					#-- SCR should be CANCELLED, DELETED or SHOPPING_LIST.
					my $scrStatus = "";
					my $substatusFound = 0;
					$matchFound = 0;
					foreach $scrline (@scrlines)
					{
						if ($matchFound == 1) {
							#-- If the previous iteration set matchFound, then scrline is the SCR status
							#-- if there is a newline on the end then save everything but the newline
							if ($scrline =~ m/(.*)(\n)/) {$scrStatus = $1;}  
							else {$scrStatus = $scrline;}
							last;
						}
						elsif ($substatusFound == 1) {
							#-- If the previous iteration found the substatus line in the SCR
							#-- Then it set substatusFound and now we are on the line after the
							#-- substatus.  Set matchFound, so on the next iteration we save the
							#-- scrline because the next line is the SCR status
							$matchFound = 1;
						}
						elsif ($scrline =~ m/(.*)(SUBSTATUS\<\/FONT\>\<\/B\>\<BR\>)(.*)/) {
						    #-- Two lines after the current line is the SCR status
						    #-- So set the substatusFound flag and keep going
							$substatusFound = 1;
						}			
					}
					
					#-- If we found the substatus in the SCR
					if ($matchFound == 1){
						#-- Make sure the logbook and SCR match CANCELLED
						if (($scrStatus[$scrStatusCount-1]{status} eq "CANCELLED") &&
						    ($scrStatus ne "CANCELLED")){
							printLog "$scr\t \t \t \t \t$scrStatus\t$scrStatus[$scrStatusCount-1]{status}\tNO\n";
							$mismatchedCancelledScr++;
						}
						#-- Make sure the logbook and SCR match DELETED
						elsif (($scrStatus[$scrStatusCount-1]{status} eq "DELETE") &&
						       ($scrStatus ne "DELETED")){
							printLog "$scr\t \t \t \t \t$scrStatus\t$scrStatus[$scrStatusCount-1]{status}\tNO\n";
							$mismatchedDeletedScr++;
						}
						#-- Make sure the logbook SHOPPING LIST matches SCR for SHOPPING_LIST
						elsif (($scrStatus[$scrStatusCount-1]{status} eq "SHOPPING LIST") &&
						       ($scrStatus ne "SHOPPING_LIST")) {
							printLog "$scr\t \t \t \t \t$scrStatus\t$scrStatus[$scrStatusCount-1]{status}\tNO\n";
							$mismatchedApprovedScr++;
						}
						#-- Now perform the checks in the other direction to determine
						#-- if there is a possible error in the logbook instead of the SCR
						elsif (($scrStatus[$scrStatusCount-1]{status} ne "CANCELLED") &&
						    ($scrStatus eq "CANCELLED")){
							printLog "$scr\t \t \t \t \t$scrStatus\t$scrStatus[$scrStatusCount-1]{status}\tNO\n";
							$mismatchedCancelledScr++;
						}
						#-- Make sure the logbook and SCR match DELETED
						elsif (($scrStatus[$scrStatusCount-1]{status} ne "DELETE") &&
						       ($scrStatus eq "DELETED")){
							printLog "$scr\t \t \t \t \t$scrStatus\t$scrStatus[$scrStatusCount-1]{status}\tNO\n";
							$mismatchedDeletedScr++;
						}
						#-- Make sure the logbook SHOPPING LIST matches SCR for SHOPPING_LIST
						elsif (($scrStatus[$scrStatusCount-1]{status} ne "SHOPPING LIST") &&
						       ($scrStatus eq "SHOPPING_LIST")) {
							printLog "$scr\t \t \t \t \t$scrStatus\t$scrStatus[$scrStatusCount-1]{status}\tNO\n";
							$mismatchedApprovedScr++;
						}
					}
					else {
						#-- Create a log message since we couldn't find status in the SCR
						printLog "$scr\t \t \t \t \tNO STATUS FOUND\t \tNO\n";
					}
				}
				close $SCRFILE;
			}
		}
	}
	
	$ftp->quit();

	printLog "\n\n\n";
	printLog "SCR\tNUM DUPLICATES\tNUM CANCELLED\tNUM DELETED\tNUM NOT CANCELLED OR DELETED\n";

	my $currentScr = 0;
	my $duplicatesCancelled = 0;
	my $duplicatesDeleted = 0;
	my $duplicatesNotCancelledDeleted = 0;

    #-- Go through all of the SCRs in the logbook and look for duplicate entries
	for ($currentScr = 0; $currentScr < $scrStatusCount; $currentScr++) {
		if ($scrStatus[$currentScr]{evaluated} == 1){
			#-- Skip this one, it may be a duplicate and we have covered it.
		}
		else {
			#-- Mark the current SCR as evaluated
			$scrStatus[$currentScr]{evaluated} = 1;
		
			my $nextScr = 0;
			my $cancelledFound = 0;
			my $deletedFound = 0;
			my $duplicateFound = 0;
			my $cancelledCount = 0;
			my $deletedCount = 0;
			
			if ($scrStatus[$currentScr]{status} eq "CANCELLED"){
				$cancelledCount++;
			}
			elsif ($scrStatus[$currentScr]{status} eq "DELETED"){
				$deletedCount++;
			}
			
			#-- Compare the current SCR to all of the remaining SCRs
			for ($nextScr = $currentScr + 1; $nextScr < $scrStatusCount; $nextScr++){
				
				#-- Check to see if the current SCR number matches the SCR number in the next entry
				if ($scrStatus[$currentScr]{scr} eq $scrStatus[$nextScr]{scr}) {
					
					#-- Set the next SCR to evaluated, and indicate that a duplicate was found
					$scrStatus[$nextScr]{evaluated} = 1;
					$duplicateFound++;
				
				    #-- If either SCR is marked as CANCELLED, indicate that a cancelled duplicate was found
					if ($scrStatus[$currentScr]{status} eq "CANCELLED" ||
						$scrStatus[$nextScr]{status} eq "CANCELLED"){
							
						$cancelledFound = 1;
						if ($scrStatus[$nextScr]{status} eq "CANCELLED"){
							$cancelledCount++;
						}
					}
					
				    #-- If either SCR is marked as DELETED, indicate that a deleted duplicate was found
					if ($scrStatus[$currentScr]{status} eq "DELETED" ||
						$scrStatus[$nextScr]{status} eq "DELETED"){
							
						$deletedFound = 1;
						if ($scrStatus[$nextScr]{status} eq "DELETED"){
							$deletedCount++;
						}
					}
				}
			}

            #-- If a duplicate was found, increment the counters for cancelled and duplicate
            #-- SCRs found and log the results		
			if ($duplicateFound > 0){
				printLog "$scrStatus[$currentScr]{scr}\t$duplicateFound";
			
				if ($cancelledFound == 1 || $deletedFound == 1){
					if (($cancelledFound == 1) && ($deletedFound == 1)){
						printLog "\t$cancelledCount\t$deletedCount";
						$duplicatesCancelled += $cancelledCount;
						$duplicatesDeleted += $deletedCount;
					}
					elsif ($cancelledFound == 1){
						printLog "\t$cancelledCount\t0";
						$duplicatesCancelled += $cancelledCount;
					}
					elsif ($deletedFound == 1){
						printLog "\t0\t$deletedCount";
						$duplicatesDeleted += $deletedCount;
					}
					
					my $tempCount = $duplicateFound - ($cancelledCount + $deletedCount);
					printLog "\t$tempCount\n";
					$duplicatesNotCancelledDeleted += $tempCount;
				}
				else {
					printLog "\t0\t0\t$duplicateFound\n";
					$duplicatesNotCancelledDeleted += $duplicateFound;
				}
			}
		}
	}

    #-- Log the results of the summary counters
	printLog "\n\n$matchedPcmsidScr PCMSIDs match PCMSID in SCR.\n";
	printLog "$unmatchedPcmsidScr PCMSIDs do NOT match PCMSID in SCR.\n";
	
	if ($scrFileErrors != 0) {
		printLog "\n$scrFileErrors SCR FILE OPEN ERRORS OCCURRED!\n";
	}
	if ($scrFormatErrors != 0) {
		printLog "\n$scrFormatErrors SCR FORMAT ERRORS IN LOGBOOK OCCURRED!\n";
	}
	
	printLog "\n$mismatchedCancelledScr SCRs do not match logbook for CANCELLED status\n";
	printLog "$mismatchedDeletedScr SCRs do not match logbook for DELETED status\n";
	printLog "$mismatchedApprovedScr SCRs do not match logbook for APPROVED status\n";

	printLog "\n$duplicatesCancelled duplicate SCRs with cancelled marked in logbook.\n";
	printLog "$duplicatesDeleted duplicate SCRs with deleted marked marked in logbook.\n";
	printLog "$duplicatesNotCancelledDeleted duplicate SCRs without cancelled or deleted marked in logbook.\n";
}

#--
#-- openLog: Function to create a logfile with a timestamp
#--
sub openLog {
	
	my ($msg) = @_;
	
	#-- Setup the logfile with date and time stamps.
	($ssec,$smin,$shour,$smday,$smon,$syear,$swday,$syday,$sisdst) = localtime(time);
	
	#-- Add 1900 to the year
	$syear += 1900;
	
	#-- Add one to the month, localtime returns the first month as zero
	$smon += 1;

	open $MSGLOG, "+>$logFilePath\\$msg\_logbook\_validate\_$syear\_$smon\_$smday\-$shour\_$smin\_$ssec.log" or die "Failed to open log file\n";

	printLog "\nExecuted at... DATE: $syear-$smon-$smday TIME: $shour:$smin:$ssec\n\n";
	printLog "Executed on Logbook Name: $spreadsheetPath\n\n";
}

#--
#-- closeLog: Function to close the logfile after calculating and recording
#--           the total execution time
#--
sub closeLog {
	
	($fsec,$fmin,$fhour,$fmday,$fmon,$fyear,$fwday,$fyday,$fisdst) = localtime(time);
	
	#-- Add 1900 to the year
	$fyear += 1900;
	
	#-- Add one to the month, localtime returns the first month as zero
	$fmon += 1;

	printLog "\n\nDone executing at... DATE: $fyear-$fmon-$fmday TIME: $fhour:$fmin:$fsec\n";

	my $thour;
	my $tmin;
	my $tsec;
	
	unless ($fhour < $shour) { $thour = $fhour - $shour; } else { $thour = $shour - $fhour; }
	unless ($fmin < $smin)   { $tmin = $fmin - $smin;    } else { $tmin = $smin - $fmin;    }
	unless ($fsec < $ssec)   { $tsec = $fsec - $ssec;    } else { $tsec = $ssec - $fsec;    }
	   
	printLog "Execution time was $thour:$tmin:$tsec\n";
	
	close $MSGLOG;
	
	my $completeDialog = $mw->DialogBox(-title => "Validation Complete", -buttons => ["OK"]);

	$completeDialog->Label(-justify => "left", -text => 
		"Validation Complete!\n\n" .
		"Log file:\n\n" .
		"$logFilePath\\logbook\_validate\_$syear\_$smon\_$smday\-$shour\_$smin\_$ssec.log\n\n"
	)->grid(-row => 0, -column => 0);
	
	$completeDialog->Show;
}

sub postScpGetFailDialog {
	my ($msg) = @_;
	
	my $dieDialog = $mw->DialogBox(-title => "Script Terminated", -buttons => ["OK"]);
	$dieDialog->Label(-justify => "left", -text => "Failed to retrieve $msg.html!\nValidation will now terminate, please restart tool!"
	)->grid(-row => 0, -column => 0);
	
	$dieDialog->Show;
	
	die "Can't download SCP $msg.html!";
}

sub postFtpHostnameFailDialog {
	my ($msg) = @_;
	
	my $dieDialog = $mw->DialogBox(-title => "FTP Failure", -buttons => ["OK"]);
	$dieDialog->Label(-justify => "left", -text => "Failed to connect to $msg!\nVerify hostname and try again!"
	)->grid(-row => 0, -column => 0);
	
	$dieDialog->Show;
	
	die "Failed to connect to $msg!";
}

sub postUserPasswdFailDialog {
	my ($msg) = @_;
	
	my $dieDialog = $mw->DialogBox(-title => "FTP Failure", -buttons => ["OK"]);
	$dieDialog->Label(-justify => "left", -text => "Failed to login as $msg!\nVerify username and password and try again!"
	)->grid(-row => 0, -column => 0);
	
	$dieDialog->Show;
	
	die "Failed to login as $msg!";
}

sub postFtpPathFailDialog {
	my ($msg) = @_;
	
	my $dieDialog = $mw->DialogBox(-title => "FTP Failure", -buttons => ["OK"]);
	$dieDialog->Label(-justify => "left", -text => "Failed to set path!\nVerify path $msg and try again!"
	)->grid(-row => 0, -column => 0);
	
	$dieDialog->Show;
	
	die "Failed to set path $msg!";
}

sub postLogbookOpenFailDialog {
	my ($msg) = @_;
	
	my $dieDialog = $mw->DialogBox(-title => "Logbook Open Failure", -buttons => ["OK"]);
	$dieDialog->Label(-justify => "left", -text => "Failed to open logbook!\nVerify $msg and try again!"
	)->grid(-row => 0, -column => 0);
	
	$dieDialog->Show;
	
	die "Failed to open logbook $msg!";
}

sub postScpOpenFailDialog {
	my ($msg) = @_;
	
	my $dieDialog = $mw->DialogBox(-title => "SCP Open Failure", -buttons => ["OK"]);
	$dieDialog->Label(-justify => "left", -text => "Failed to open $msg!\nValidation will continue without checking $msg!"
	)->grid(-row => 0, -column => 0);
	
	$dieDialog->Show;
}

sub postScrOpenFailDialog {
	my ($msg) = @_;
	
	my $dieDialog = $mw->DialogBox(-title => "SCR Open Failure", -buttons => ["OK"]);
	$dieDialog->Label(-justify => "left", -text => "Failed to open $msg!\nValidation will continue without checking $msg!"
	)->grid(-row => 0, -column => 0);
	
	$dieDialog->Show;
}