depending on the value of the variable.
Hello all,
I'm having problems trying to insert data into different columns depending on the
value of the variable.
Here is the situation:
if the value of $za01 = "QA" I need the value of $sdq[$i+1] to be written into the
curr_invent column but if $za01 = "QS" it should be written into the net_sold column.
I don't know why I'm getting all values of $sdq[$i+1] only in the curr_invent column
and nothing in the net_sold. I'm also not sure if I need two different statement
handles or it can be done using just one.
Thanks a lot for your time and help.
Greg
Here's my script:
##################################
#!perl
use strict;
use Date::Format;
use DBI;
use diagnostics;
my $dir = "G:\\apps\\quickedi\\ib";
my $recv_file = "recv.1";
my $elite_file = "item_upc.txt";
print "Poczekaj chwile..............\n";
open(ITEMUPC, "> $dir/$elite_file ") || die "can't create $dir/$elite_file:
$!";
my %upc_itemlist;
my %item_upclist;
my ($upc_num,$item_num);
my $DSN = "Elite";
my $dbh1 = DBI->connect ("dbi:ODBC:$DSN", "", "",
{RaiseError => 1, AutoCommit => 0});
my $SQL = $dbh1->prepare("select upc, item_num from item where org_code = '01' and
item_num = report_item_num and memo_1 <> 'TITLE' and upc is not null and upc <> 'NONE'
order by upc");
$SQL->execute;
while ( my @row = $SQL->fetchrow_array) {
my($upc_num,$item,$price) = @row;
$upc_num=~ s/\s+//g;
($item =$item) =~ s/\s+$//;
print ITEMUPC "$upc_num,$item\n";
}
$dbh1->rollback;
$dbh1->disconnect;
close ITEMUPC;
open(CHECK, "< $dir/$elite_file");
while (<CHECK>) {
chomp;
($upc_num, $item_num) = split /\s*,\s*/;
$upc_itemlist{$upc_num} = $item_num;
}
close CHECK;
no warnings 'uninitialized';
my $dbh = DBI->connect( "dbi:ODBC:EDI Data", "", "" )or die "Can't connect to ODBC
database: $DBI::errstr\n";
my ($count,$customer,$sent,$pad_num,$upc,$za01,$xq02,$xq03,$inventory,$sold);
my @names1 =
qq(customer,upc_num,item_num,store_num,curr_invent,beginning_date,ending_date,sent);
my @names2 =
qq(customer,upc_num,item_num,store_num,net_sold,beginning_date,ending_date,sent);
my $sth1 = $dbh->prepare("INSERT INTO edi_pad_test (@names1) VALUES
(?,?,?,?,?,?,?,?)");
my $sth2 = $dbh->prepare("INSERT INTO edi_pad_test (@names2) VALUES
(?,?,?,?,?,?,?,?)");
open(RECV, "< $dir/$recv_file") || die "can't open $recv_file: $_!";
while (<RECV>) {
s/.$//;
chomp;
my @fields = split /\</;
if (/^ISA<00< <00< <12<2017781300/) {
$customer = "3";
} elsif (/^GS<PD<2017781300/) {
$sent = $fields[4];
} elsif (/^ST<852/) {
$pad_num = $fields[2];
} elsif (/^XQ/) {
$xq02 = $fields[2]; #Begining Date Range
$xq03 = $fields[3]; #Ending Date Range
} elsif (/^N9/) {
#ignore
} elsif (/^LIN/) {
$upc = $fields[3]; #UPC number
} elsif (/^ZA/) {
$za01 = $fields[1]; #QA = Current Inventory Qty, QS = Net Qty Sold
} elsif (/^SDQ/) {
$upc_itemlist{$upc} = "NO_ELITE_ITEM_NUM_FOR_LNT_UPC" unless
exists($upc_itemlist{$upc});
my @sdq = split/\</;
if ($za01 == "QA") {
# the data should be inserted into the curr_invent column
for (my $i=3;$i<=21;$i+=2) {
if (defined $pad_num) { # because other transactions also have
a SDQ lines
if (defined $sdq[$i]) { # some SDQ lines have less then 22
fields
$inventory = $sdq[$i + 1];
print
"Processing:\t$pad_num,$upc,$sdq[$i],$za01.....$inventory\n";
$sth1->execute($customer,$upc,$upc_itemlist{$upc},$sdq[$i],$inventory,$xq02,$xq03,$sent)
or die "can't execute SQL statement: $DBI::errstr\n";
} else {
#ignore
}
} else {
#ignore
}
}
} elsif ($za01 == "QS") {
# the data should be inserted into the net_sold column
for (my $i=3;$i<=21;$i+=2) {
if (defined $pad_num) { # because other transactions also have
a SDQ lines
if (defined $sdq[$i]) { # some SDQ lines have less then 22
fields
$sold = $sdq[$i + 1];
print
"Processing:\t$pad_num,$upc,$sdq[$i],$za01.....$sold\n";
$sth2->execute($customer,$upc,$upc_itemlist{$upc},$sdq[$i],$sold,$xq02,$xq03,$sent) or
die "can't execute SQL statement: $DBI::errstr\n";
} else {
#ignore
}
} else {
#ignore
}
}
} else {
#ignore
}
} elsif (/^CTT/) {
#ignore
} elsif (/^SE/) {
#ignore
} elsif (/^GE/) {
#ignore
} elsif (/^IEA/) {
#ignore
} else {
next;
# print "Unrecognized: ", substr ($_, 0, 10), "\n";
}
}
close RECV;
$dbh->disconnect;
####################################
and the part of my data file:
ISA<00< <00< <12<2017781300 <12<7084061572
<010716<0601<U<00401<000000630<0<P<:
GS<PD<2017781300<7084061572<20010716<0601<630<X<004010VICS
ST<852<6300001
XQ<H<20010708<20010714
N9<IA<1982
LIN<<UP<009281020035
ZA<QA
SDQ<EA<92<129<5<183<23<194<6<288<14<302<3<305<3<320<29<342<1<347<-1<350<-13
SDQ<EA<92<354<15<355<1<358<7<359<7<360<8<362<13<363<2<364<7<365<11<367<3
SDQ<EA<92<368<2<371<5<372<1<373<1<374<4<376<10<377<5<378<-2<379<2<382<1
SDQ<EA<92<383<-2<384<7<386<6<388<5<391<17<392<9<394<25<395<6<398<-1<399<1
SDQ<EA<92<400<14<402<-3<403<-13<405<8<408<17<410<7<411<14<412<9<418<2<419<5
SDQ<EA<92<421<3<422<19<423<-4<424<12<425<19<428<14<429<-7<430<1<438<-1<439<6
SDQ<EA<92<441<8<442<13<443<2<444<-13<445<14<446<7<447<7<448<13<449<4<450<-2
SDQ<EA<92<452<-2<453<1<454<24<458<-3<459<8<461<13<462<13<463<17<464<12<467<1
SDQ<EA<92<468<-9<469<4<470<-1<472<7<474<7<476<17<477<1<478<2<480<23<482<16
SDQ<EA<92<483<12<485<-25<488<-1<489<15<490<6<491<27<495<27<497<16<499<5<500<22
SDQ<EA<92<501<-10<502<-1<503<28<504<-1<505<13<506<-7<507<-2<509<4<510<1<511<-10
SDQ<EA<92<512<6<513<-6<516<21<517<7<518<-1<519<7<520<19<521<4<522<-13<527<4
SDQ<EA<92<529<16<533<13<534<6<535<7<538<-9<539<1<541<23<543<15<544<7<546<3
SDQ<EA<92<549<3<550<-18<553<-1<555<18<561<8<563<4<564<10<571<23<572<1<573<25
SDQ<EA<92<575<2<576<-10<580<7<584<-19<585<22<586<45<587<25<588<6<589<10<590<1
SDQ<EA<92<592<23<593<8<594<1<599<1<602<1<603<13<610<-14<612<-6<616<1<618<9
SDQ<EA<92<620<11<623<16<624<9<625<7<628<16<630<10<633<14<635<20<636<10<637<21
SDQ<EA<92<638<1<640<10<641<25<643<10<646<1<649<30<651<1<656<-2<661<4<663<-10
SDQ<EA<92<664<2<666<15<669<-31<673<8<684<10<688<5
ZA<QS
SDQ<EA<92<371<1<381<1<428<1<478<1<480<1<495<1<497<1<521<1<593<1<630<1
LIN<<UP<009281021315
ZA<QA
SDQ<EA<92<305<31<320<-3<338<2<346<31<348<94<354<30<355<-2<360<35<365<-1<373<102
SDQ<EA<92<374<9<377<77<379<-9<381<49<382<2<386<54<390<87<392<84<394<-1<399<7
SDQ<EA<92<402<-5<405<5<409<73<421<88<425<-3<436<1<437<-4<441<56<462<209<468<1
SDQ<EA<92<480<9<483<-3<488<-2<494<1<495<16<502<-1<515<82<523<1<526<-1<527<101
SDQ<EA<92<536<9<552<92<555<2<564<-2<569<-1<574<57<581<108<586<10<595<-6<601<16
SDQ<EA<92<609<2<616<30<632<1<635<-2<646<4<654<-2
ZA<QS
SDQ<EA<92<348<1<354<1<390<1<394<1<441<1<483<1<488<1<569<1<574<1<635<1
CTT<702
SE<9200<6300001
GE<1<630
IEA<1<000000630
