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";

I'm confused, are you trying to run it specifying a date from the past? Why would that run, I would understand a program not understanding time travel since it will never be that specific time and date again. Why don't you code the script to handle command line ARG's to specify the actual file you want it to run against?

i'm not really sure i understand either... you could hack up $currDate or fudge your date refs or maybe just backdate your server...

there's a little part in your SQL updater "Run date: $tmon/$tmday/$tyear"
is that what you'd like changed?