Hello, I am in a bit of a issue, I am very new to perl but am being requested to make a script to take information from a database and put it into a excel sheet, I was given a co workers script which does almost the same thing but there are alot of extra code because he was checking info against other spreadsheets. Is it possible to pin point what needs to be removed, its blending together too much for my level. if this is too much then anything will help. The script has a GUI module which adds to the difficulty. The Flow chart I have is looking to use the gui to enter the log-in info, check for a file check inside for status (Queued_for_Board) if so gather information Issue Submitted_By and a series of others that I could add and all this placed into excel would it be alot of work to break it down to that from this script? If so then I guess ima have to approach it a different way
#!perl #-- #-- DESCRIPTION: #-- This is a script attempting to collect data from the razor DB base +d 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 LOGBO +OK #-- 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 LOGBO +OK #-- 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 o +f 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 provid +ed by NSWCDD my $hostname = "fbm240-1"; #-- Default name of the host to retriev +e 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 sc +ript 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 => $c +olumn++); $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, an +d 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 => $co +lumn++); $mw->Entry(-textvariable => \$spreadsheetPath, -width => 100)->grid(-r +ow => $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(-r +ow => $row, -column => $column++); $firstFrame->Label(-text => "Username")->grid(-row => $row, -column => + $column++); $firstFrame->Entry(-textvariable => \$username, -width => 25)->grid(-r +ow => $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, -c +olumn => $column++); $secondFrame->Entry(-textvariable => \$remoteScpPath, -width => 100)-> +grid(-row => $row++, -column => $column--); $secondFrame->Label(-text => "Remote SCR Path")->grid(-row => $row, -c +olumn => $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", -variab +le => \$executionOption, -value => "SCP")->grid(-row => $row, -column + => $column++); my $scrRadioButton = $buttonFrame->Radiobutton(-text => "SCR", -variab +le => \$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 variabl +es, 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 SCP +s my $ftp = Net::FTP->new($hostname) or postFtpHostnameFailDialog ($ +hostname); $ftp->login($username, $password) or postUserPasswdFailDialog($use +rname); $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 d +on'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\tL +OGBOOK STATUS\tMATCH\n"; #-- Process each sheet in the logbook that has data, there most li +kely 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 spre +adsheet in each array element @currentLine = $sheet->next_row; #-- PCMS_ID has "FCARCH_" prepended on it, store everythin +g 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 searchin +g 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 PC +MS 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/PC +MSID matches to the log #printLog "$scp\t$pcmsid\tMATCH\n"; $matchedPcmsidScp++; } #-- If we didn't find a matching PCMS ID, then inc +rement 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[$scpStatusCo +unt-1]{status}\n"; last; } } } #-- Save the SCP substatus and compare it to the s +tatus in the logbook. #-- If the SCP is CANCELLED, DELETED or APPROVED i +n the logbook, then the #-- SCP should be CANCELLED, DELETED or Lib_Workin +g, 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 CANCEL +LED if ((($scpStatus[$scpStatusCount-1]{state} eq +"CANCELLED") || ($scpStatus[$scpStatusCount-1]{state} eq +"CANCELLED ")) && ($scpStatus ne "CANCELLED")){ printLog "$scp\t \t \t \t$scpStatus\tCANCE +LLED\tNO\n"; $mismatchedCancelledScp++; } #-- Make sure the logbook and SCP match DELETE +D elsif ((($scpStatus[$scpStatusCount-1]{state} +eq "DELETE") || ($scpStatus[$scpStatusCount-1]{state} +eq "DELETE ")) && ($scpStatus ne "DELETED")){ printLog "$scp\t \t \t \t$scpStatus\tDELET +ED\tNO\n"; $mismatchedDeletedScp++; } #-- Make sure the logbook APPROVED/COMPLETED m +atches 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$scpS +tatus[$scpStatusCount-1]{state}\tNO\n"; $mismatchedApprovedScp++; } #-- Do the reverse check to find logbook entri +es that do not match #-- CANCELLED, DELETED, or APPROVED SCPs #-- Make sure the logbook and SCP match CANCEL +LED elsif ((($scpStatus[$scpStatusCount-1]{state} +ne "CANCELLED") && ($scpStatus[$scpStatusCount-1]{state} +ne "CANCELLED ")) && ($scpStatus eq "CANCELLED")){ printLog "$scp\t \t \t \tCANCELLED\t$scpSt +atus[$scpStatusCount-1]{state}\tNO\n"; $mismatchedCancelledScp++; } #-- Make sure the logbook and SCP match DELETE +D elsif ((($scpStatus[$scpStatusCount-1]{state} +ne "DELETE") && ($scpStatus[$scpStatusCount-1]{state} +ne "DELETE ")) && ($scpStatus eq "DELETED")){ printLog "$scp\t \t \t \tDELETED\t$scpStat +us[$scpStatusCount-1]{state}\tNO\n"; $mismatchedDeletedScp++; } #-- Make sure the logbook APPROVED/COMPLETED m +atches 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$scpS +tatus[$scpStatusCount-1]{state}\tNO\n"; $mismatchedApprovedScp++; } } else { #-- Create a log message since we couldn't fin +d status in the SCP printLog "$scp\t \t \t \tNO STATUS FOUND\t \tN +O\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 duplica +te entries for ($currentScp = 0; $currentScp < $scpStatusCount; $currentScp++ +) { if ($scpStatus[$currentScp]{evaluated} == 1){ #-- Skip this one, it may be a duplicate and we have cover +ed 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 < $scpStatusCoun +t; $nextScp++){ #-- Check to see if the current SCP number matches the + SCP number in the next entry if ($scpStatus[$currentScp]{scp} eq $scpStatus[$nextSc +p]{scp}) { #-- Set the next SCP to evaluated, and indicate th +at 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 t +hat 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 c +ancelled and duplicate #-- SCPs found and log the results if ($duplicateFound > 0){ printLog "$scpStatus[$currentScp]{scp}\t$duplicateFoun +d"; 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 DELE +TED status\n"; printLog "$mismatchedApprovedScp SCPs do not match logbook for APP +ROVED status\n"; printLog "\n$duplicatesCancelled duplicate SCPs with cancelled mar +ked in logbook.\n"; printLog "$duplicatesDeleted duplicate SCPs with deleted marked ma +rked in logbook.\n"; printLog "$duplicatesNotCancelledDeleted duplicate SCPs without ca +ncelled 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 SCR +s my $ftp = Net::FTP->new($hostname) or postFtpHostnameFailDialog ($ +hostname); $ftp->login($username, $password) or postUserPasswdFailDialog($use +rname); $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 d +on'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\t +SCP STATUS\tLOGBOOK STATUS\tMATCH\n"; #-- Process each sheet in the logbook that has data, there most li +kely 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 spre +adsheet in each array element @currentLine = $sheet->next_row; #-- PCMS_ID has "FCARCH_" prepended on it, store everythin +g 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, m +ultiple 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 s +ee 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 witho +ut 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 searchin +g 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 PC +MS 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/PC +MSID matches to the log #printLog "$scr\t \t$pcmsid\tMATCH\n"; $matchedPcmsidScr++; } #-- If we didn't find a matching PCMS ID, then inc +rement 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[$scrStatusCo +unt-1]{status}\n"; last; } } } #-- Save the SCR substatus and compare it to the s +tatus in the logbook. #-- If the SCR is CANCELLED, DELETED or SHOPPING L +IST in the logbook, then the #-- SCR should be CANCELLED, DELETED or SHOPPING_L +IST. my $scrStatus = ""; my $substatusFound = 0; $matchFound = 0; foreach $scrline (@scrlines) { if ($matchFound == 1) { #-- If the previous iteration set matchFou +nd, 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 su +bstatus 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 S +CR status $matchFound = 1; } elsif ($scrline =~ m/(.*)(SUBSTATUS\<\/FONT\>\ +<\/B\>\<BR\>)(.*)/) { #-- Two lines after the current line is th +e SCR status #-- So set the substatusFound flag and kee +p going $substatusFound = 1; } } #-- If we found the substatus in the SCR if ($matchFound == 1){ #-- Make sure the logbook and SCR match CANCEL +LED if (($scrStatus[$scrStatusCount-1]{status} eq +"CANCELLED") && ($scrStatus ne "CANCELLED")){ printLog "$scr\t \t \t \t \t$scrStatus\t$s +crStatus[$scrStatusCount-1]{status}\tNO\n"; $mismatchedCancelledScr++; } #-- Make sure the logbook and SCR match DELETE +D elsif (($scrStatus[$scrStatusCount-1]{status} +eq "DELETE") && ($scrStatus ne "DELETED")){ printLog "$scr\t \t \t \t \t$scrStatus\t$s +crStatus[$scrStatusCount-1]{status}\tNO\n"; $mismatchedDeletedScr++; } #-- Make sure the logbook SHOPPING LIST matche +s 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$s +crStatus[$scrStatusCount-1]{status}\tNO\n"; $mismatchedApprovedScr++; } #-- Now perform the checks in the other direct +ion to determine #-- if there is a possible error in the logboo +k instead of the SCR elsif (($scrStatus[$scrStatusCount-1]{status} +ne "CANCELLED") && ($scrStatus eq "CANCELLED")){ printLog "$scr\t \t \t \t \t$scrStatus\t$s +crStatus[$scrStatusCount-1]{status}\tNO\n"; $mismatchedCancelledScr++; } #-- Make sure the logbook and SCR match DELETE +D elsif (($scrStatus[$scrStatusCount-1]{status} +ne "DELETE") && ($scrStatus eq "DELETED")){ printLog "$scr\t \t \t \t \t$scrStatus\t$s +crStatus[$scrStatusCount-1]{status}\tNO\n"; $mismatchedDeletedScr++; } #-- Make sure the logbook SHOPPING LIST matche +s 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$s +crStatus[$scrStatusCount-1]{status}\tNO\n"; $mismatchedApprovedScr++; } } else { #-- Create a log message since we couldn't fin +d 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 duplica +te entries for ($currentScr = 0; $currentScr < $scrStatusCount; $currentScr++ +) { if ($scrStatus[$currentScr]{evaluated} == 1){ #-- Skip this one, it may be a duplicate and we have cover +ed 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 < $scrStatusCoun +t; $nextScr++){ #-- Check to see if the current SCR number matches the + SCR number in the next entry if ($scrStatus[$currentScr]{scr} eq $scrStatus[$nextSc +r]{scr}) { #-- Set the next SCR to evaluated, and indicate th +at 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 t +hat 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 c +ancelled and duplicate #-- SCRs found and log the results if ($duplicateFound > 0){ printLog "$scrStatus[$currentScr]{scr}\t$duplicateFoun +d"; 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 OCCU +RRED!\n"; } printLog "\n$mismatchedCancelledScr SCRs do not match logbook for +CANCELLED status\n"; printLog "$mismatchedDeletedScr SCRs do not match logbook for DELE +TED status\n"; printLog "$mismatchedApprovedScr SCRs do not match logbook for APP +ROVED status\n"; printLog "\n$duplicatesCancelled duplicate SCRs with cancelled mar +ked in logbook.\n"; printLog "$duplicatesDeleted duplicate SCRs with deleted marked ma +rked in logbook.\n"; printLog "$duplicatesNotCancelledDeleted duplicate SCRs without ca +ncelled 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) = l +ocaltime(time); #-- Add 1900 to the year $syear += 1900; #-- Add one to the month, localtime returns the first month as zer +o $smon += 1; open $MSGLOG, "+>$logFilePath\\$msg\_logbook\_validate\_$syear\_$s +mon\_$smday\-$shour\_$smin\_$ssec.log" or die "Failed to open log fil +e\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 reco +rding #-- the total execution time #-- sub closeLog { ($fsec,$fmin,$fhour,$fmday,$fmon,$fyear,$fwday,$fyday,$fisdst) = l +ocaltime(time); #-- Add 1900 to the year $fyear += 1900; #-- Add one to the month, localtime returns the first month as zer +o $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 { $tho +ur = $shour - $fhour; } unless ($fmin < $smin) { $tmin = $fmin - $smin; } else { $tmi +n = $smin - $fmin; } unless ($fsec < $ssec) { $tsec = $fsec - $ssec; } else { $tse +c = $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\-$shou +r\_$smin\_$ssec.log\n\n" )->grid(-row => 0, -column => 0); $completeDialog->Show; } sub postScpGetFailDialog { my ($msg) = @_; my $dieDialog = $mw->DialogBox(-title => "Script Terminated", -but +tons => ["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 log +book!\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", -butt +ons => ["OK"]); $dieDialog->Label(-justify => "left", -text => "Failed to open $ms +g!\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", -butt +ons => ["OK"]); $dieDialog->Label(-justify => "left", -text => "Failed to open $ms +g!\nValidation will continue without checking $msg!" )->grid(-row => 0, -column => 0); $dieDialog->Show; }
This is a long script and I am going out on a limb here in asking for help, I dont want to ask for too much. But im at a loss right now. Any help would be wonderful
Content restored by GrandFather
In reply to Removing unessary data by reidlos65
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |