Hi
Im so nood in perl and i have a problem need.
When i ever i run this dts_add.pl the run date is not backward compatible.
I ran this program every month and and reference the date from which it was previously ran.
I wanted to run a file dated 2 months ago and i coudnt run it.
Can somebody help me.
Thanks,
Andrew..
This is the code:
use Win32::ADO;
use Win32::OLE;
use Win32::OLE::Variant;
# Time Variables
$time1 = localtime(); # Time of Start of Program
($tsec, $tmin, $thour, $tmday, $tmon, $tyear) = localtime();
$tmon++;
$tyear = 1900 + $tyear;
$time2 = time() - (1 * 24 * 60 * 60); # Date Yesterday for Query
($sec, $min, $hour, $mday, $mon, $year) = localtime($time2);
$mon++; # Increments Month by One
$year = 1900 + $year; # Adds to Make Year Correct
if (length($mday) < 2) {
$mday = "0$mday";
}
$DateRec = "$mon/$mday/$year";
$SeaDate = "$mday/$mon/$year";
$RunDate = "";
sub date_change {
local ($param_1, $param_2) = @_;
if ($param_2 == '1') {
# Convert Date for Oracle Query
$sla1 = index($param_1, "/");
$sla2 = index($param_1, "/", $sla1+1);
$currDay = substr($param_1, 0, $sla1);
# $currDay = "0" . $currDay if $currDay < 10;
$currMon = substr($param_1, ($sla1+1), ($sla2-$sla1-1));
if ($currMon == 1) {$cuMon = "JAN";}
elsif ($currMon == 2) {$cuMon="FEB";}
elsif ($currMon == 3) {$cuMon="MAR";}
elsif ($currMon == 4) {$cuMon="APR";}
elsif ($currMon == 5) {$cuMon="MAY";}
elsif ($currMon == 6) {$cuMon="JUN";}
elsif ($currMon == 7) {$cuMon="JUL";}
elsif ($currMon == 8) {$cuMon="AUG";}
elsif ($currMon == 9) {$cuMon="SEP";}
elsif ($currMon == 10) {$cuMon="OCT";}
elsif ($currMon == 11) {$cuMon="NOV";}
elsif ($currMon == 12) {$cuMon="DEC";}
$currYear = substr($param_1, ($sla2+1), 2);
$currDate = "$currDay-$cuMon-$currYear"; # Formats the date so it can be queried from the Oracle Database
}
else {
$currDate = "20" . join("", reverse( $param_1 =~ m#(\d{2})/(\d{2})/(\d{2})# ));
}
return($currDate);
}
# Open Log File to Log any Errors and Successes
open (LogFile, ">> n:/Web_Site/insp_dev/smmc/quality/queries/dts_Log_File.txt");
flock (LogFile, 2);
print LogFile "\n-------------------- $time1 --------------------\n";
# Set Variables to Count Records
$CountRows = 0;
$CountNLC = 0;
$CountNDup = 0;
$CountDup = 0;
$CountWip = 0;
$CountNeg = 0;
$CountDupRec = 0;
$Dup = "";
# Opens smmc_dts.txt file and reads records into an database.
open (DTS, "c:/InetPub/ftproot/smmc_dts.txt") || die;
# Opens Last Recorded Date
open(run_date, "< n:/Web_Site/insp_dev/smmc/quality/queries/dts_run_date.txt");
@lines = <run_date>;
foreach $line (@lines) {
$compare = $line;
}
close(run_date);
# Setup Connections to Databases
$conn = Win32::OLE->new("ADODB.Connection");
$conn->Open("DSN=smmcdev;UID=web_login;PWD=4website");
# Reads through and adds records to the database
while ($results = <DTS>) {
@record = split(/\|/, $results);
$Totalres = @record;
$returnDate = &date_change(@record[4], 1);
$compareDate = &date_change(@record[4], 2);
print "returnDate: $returnDate | compareDate: $compareDate | compare: $compare \n";
if ($compareDate gt $RunDate) {
$RunDate = $compareDate;
}
if ($compare lt $compareDate) {
for ($x=0; $x<$Totalres; $x++) {
@record[$x] =~ s/^\s*(.*?)\s*$/$1/;
}
$CountRows++;
if (@record[7] gt '0') {
$comp = Win32::OLE->new("ADODB.Recordset");
$sql = "SELECT ID FROM DTS WHERE (('@record[5]' Like Part_No) AND (Vendor_No = @record[1]) AND ('$returnDate' BETWEEN Implementation_Date and Cancel_Date))";
$comp = $conn->execute($sql);
if ($comp->EOF) {
$CountNDup++;
}
else {
$Sql = "INSERT INTO Inspection_Record_Header (Part_No, Lot, PO_No, PO_Line, Lot_Quantity, Quantity_Accepted, RCF, Product_Line, Comm_Code, Vendor_No, Vendor_Name, Date_Received, Hub, Comments, Form_Rev, Quantity_Rejected, Inspect_Time, Inspect_Scrap) VALUES ('@record[5]', 'DTS-@record[2]@record[3]', '@record[2]', '@record[3]', @record[6], @record[6], 'SMMC', '@record[8]', '@record[7]', @record[1], '@record[0]', '$returnDate', 'SMMC', 'DTS Parts Populated by dts_add.pl automatically. Run date: $tmon/$tmday/$tyear.', '1', 0, 0, 0)";
$CountNLC++;
$conn->execute($Sql);
print LogFile "@record[0]\n";
print LogFile "@record[1]\n";
print LogFile "@record[2]\n";
print LogFile "@record[3]\n";
print LogFile "@record[5]\n";
print LogFile "@record[6]\n";
print LogFile "@record[7]\n";
print LogFile "@record[8]\n";
}
if ($comp) {
$comp->Close;
}
}
}
else {
$CountNeg++;
}
}
$conn->Close;
close DTS;
# unlink "c:/InetPub/ftproot/smmc_dts.txt";