Best regards,
Stephan
Morrison, Trevor (Trevor) schrieb:
Hi,
I am running the latest MySql on a Windows 2000 machine. I also use the control center gui to do all of my work. I have a Perl program that parses online order data and then using Perl's DBI, I write this data to the database. Now, It all works fine except that each order and that orders' items are written twice to the database. I have looked over my code but only see one insert statement for the order information another for the ordered items. I guess I need a fresh set of eyes to see where my error is!
I have included the code below with the database connection information highlighted in red:
TIA
Trevor
#!/usr/local/bin/perl
#============ # Main script #------------ use strict; use MivaOrder; use Data::Dumper;
#Use Perl's Database Interface (DBI) with the NySQL module to connect the Maverick database use DBI; my %attr = (PrintError => 1, RaiseError => 1); my $dbname='maverick'; my $dbuser='root'; my $dbpass=''; my $dbh=DBI->connect('DBI:mysql:database=maverick;host=localhost;port=3306',"$d buser","$dbpass", \%attr) || die "Unable to connect to database maverick on localhost: $DBI::errstr\n";
my @new_orders; #my $email = qw(c:\\maverick\\trevor_trial2.txt); open(ORDER,$ARGV[0]) or die "Error opening \"$ARGV[0]\": $!\n";
my $order = undef; while(<ORDER>) {
# Keep appending to the order string until we reach the end of the order. unless(/^Site rating\.\.\: \"/) { $order .= $_ and next; }
# We are done with the order.
my $obj = MivaOrder->new($order); #print Dumper(\$obj); push @new_orders,$obj; $order = undef; }
my $sth1 =$dbh->prepare("INSERT INTO miva_retail_orders(wholesale_company,order_number,date,credit_card_type,ship ped,shiptype,bill_name,bill_company,bill_addr1,bill_addr2,bill_city,bill_sta te,bill_zip,bill_country,ship_name,ship_addr1,ship_addr2,ship_city,ship_stat e,ship_zip,ship_country,email,bill_phone_number,bill_phone_number2,ship_phon e_number,ship_phone_number2,sales_tax,shipping_amount,coupon_type,coupon_amo unt,total) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); foreach my $new_order(@new_orders) {
# Enter the database and write the data $sth1->execute($new_order->wholesale_company(),$new_order->order_number(),$n ew_order->date(),$new_order->credit_card_type(),$new_order->shipped(),$new_o rder->ship_type(),$new_order->bill_name(),$new_order->bill_company_name(),$n ew_order->bill_to_street(),$new_order->bill_to_street2(),$new_order->bill_to _city(),$new_order->bill_to_state(),$new_order->bill_to_zip(),$new_order->bi ll_to_country(),$new_order->ship_name(),$new_order->ship_to_street(),$new_or der->ship_to_street2(),$new_order->ship_to_city(),$new_order->ship_to_state( ),$new_order->ship_to_zip(),$new_order->ship_to_country(),$new_order->email_ address(),$new_order->bill_phone_number(),$new_order->bill_phone_number2(),$ new_order->ship_phone_number(),$new_order->ship_phone_number2(),$new_order-> sales_tax(),$new_order->shipping_amount(),$new_order->coupon_type(),$new_ord er->coupon_amount(),$new_order->total());
}
$sth1->finish; $dbh->disconnect;
#================== # Package MivaOrder #------------------ package MivaOrder; use strict; use Carp; use English; use vars qw($AUTOLOAD);
my %fields = ( wholesale_company =>undef, order_number =>undef, date =>undef,
bill_name =>undef, bill_email_address =>undef, bill_phone_number =>undef, bill_phone_number2 =>undef, bill_business_name =>undef, bill_to_street =>undef, bill_to_city =>undef, bill_to_state =>undef, bill_to_zip =>undef, bill_to_country =>undef,
ship_name =>undef, ship_email_address =>undef, ship_phone_number =>undef, ship_phone_number2 =>undef, ship_business_name =>undef, ship_to_street =>undef, ship_to_city =>undef, ship_to_state =>undef, ship_to_zip =>undef, ship_to_country =>undef,
code =>undef, name =>undef, quantity =>undef, price =>undef,
shipping_method =>undef, shipping_amount =>undef,
sales_tax =>undef, total =>undef,
notes =>undef, coupon_type =>undef, coupon_amount =>undef,
credit_card_type =>undef, COD =>undef );
sub AUTOLOAD { my ($self,$value) = @_; $AUTOLOAD =~ /.*::(\w+)/; $self->{$1} = $value if($value); return $self->{$1}; }
sub new { my ($that,$order) = @_; croak "Order file is undefined" if(!$order); my $class = ref($that) || $that; my $self = {}; $self->{$_} = $fields{$_} foreach(keys %fields); bless $self, $class; $self->init($order); return $self; }
sub init { open(TREVOR, "> c:\\maverick\\HITHERE.txt") or die "Error opening HITHERE.txt $!\n"; my ($self,$order) = @_; my @lines = split(/\n/,$order); my $in_address = undef; my $in_order = undef;
foreach my $line(@lines) {
#our $n += 1 if $line =~/Order Number :/; #print $n; #next unless($line =~ /\w+/); chomp ($line);
if($line=~ /([EMAIL PROTECTED])\s+\"/i) { $self->{wholesale_company} = $1; #print $1 . "\n"; }
elsif($line =~ /Order\s+Number\s+:\s+(.*)$/i) { $self->{order_number} = $1; #print $1 . "\n"; }
elsif ($line =~ /Placed\s+:\s+([0-9]{2})\/([0-9]{2})\/([0-9]{4})\s+([0-9]{2}:[0-9]{2}:[0-9]{ 2})/i) { my $mon=$1; my $day=$2; my $year = $3; my $time=$4; my $date = $year . "-" . $mon . "-" . $day . " " . $time ; $self->{date} = $date; #print $date . "\n"; }
elsif (($line =~ /Ship To:/i) || defined($in_address)) { $in_address = 1 and next if(!defined($in_address)); my $regex = qr#(.{0,41})(.{0,41})#; if (!defined($self->{bill_name}))
($self->{bill_name},$self->{ship_name}) = ($line =~ /$regex/); } elsif (!defined($self->{ship_email_address})) { ($self->{ship_email_address},$self->{bill_email_address}) = ($line =~ /$regex/); #print $1 . "\n"; #print $2 . "\n"; } elsif (!defined($self->{ship_phone_number})) { ($self->{ship_phone_number},$self->{bill_phone_number}) = ($line =~ /$regex/); #print $1 . "\n"; #print $2 . "\n"; } elsif (!defined($self->{ship_phone_number2})) { ($self->{ship_phone_number2},$self->{bill_phone_number2})= ($line =~ /$regex/); # $self->{ship_phone_number2}= ($line =~ /^\(?[0-9]{3}\)?-?|s?[0-9]{3}-?|s?[0-9]$/); #$self->{bill_phone_number2} = ($line =~ /\(?[0-9]{3}\)?-?|s?[0-9]{3}-?|s?[0-9]$/); #print $1 . "\n"; #print $2 . "\n"; } elsif (!defined($self->{bill_business_name}) ||!defined($self->{ship_business_name})) { ($self->{ship_business_name},$self->{bill_business_name}) = ($line =~ /$regex/); #print $1 . "\n"; #print $2 . "\n"; } elsif (!defined($self->{bill_to_street}) ||!defined($self->{ship_to_street}))
($self->{ship_to_street},$self->{bill_to_street})
= ($line =~ /$regex/);
#print $1 . "\n";
#print $2 . "\n";
} elsif (!defined($self->{ship_to_city})
||!defined($self->{bill_to_city})){ my ($ship_to,$bill_to) = ($line =~ /$regex/);
($self->{ship_to_zip},$self->{ship_to_state},$self->{ship_to_city} ) # = split(/\s+/,$ship_to);
# = ($ship_to =~ /^(.*)\s+([A-Za-z]{2})\s+([0-9]{5}(-[0-9]{4})?)/);#^(\w+\s+?\w+?\s+?\w+?\s+\w+)\s(\w+)\s(\d{5}-?(\d{4})?)/); #^(\w+\s?\w*.?\s?\w+)\s(\w+)\s(\d
{5}(\W)?(\d{5})?)/);
= map { scalar reverse } split ' ', ( reverse $ship_to), 3;
print TREVOR $self->{order_number}. "\n";
print TREVOR $1 . "\n";
print TREVOR $2 . "\n";
print TREVOR $3 . "\n";
($self->{bill_to_zip},$self->{bill_to_state},$self->{bill_to_city} ) #= split(/\s+/,$bill_to); #= ($line =~ /\s{2,}(\w+.?\s?\w*.?\s?\w+.?)\s?(\w{2})\s?([0-9]{5}-?([0-9]{4})?)/); #= ($bill_to =~ /\s{2,}(.*)\s+([A-Za-z]{2})\s+([0-9]{5}(-[0-9]{4})?)$/);#\s{2,}(\w+(\s+\w+)? \s+\w+)\s+(\w{2})\s?([0-9]{5}-?([0-9]{4})?)/); = map { scalar reverse } split ' ', ( reverse $bill_to), 3; #print $1 . "\n"; #print $2 . "\n"; #print $3 . "\n";
} elsif (!defined($self->{ship_to_country})) { ($self->{ship_to_country},$self->{bill_to_country}) = ($line =~ /$regex/); #print $1 . "\n"; #print $2 . "\n"; $in_address = undef; } }
elsif (($line =~ /Code/) || defined($in_order)) { $in_order = 1 and next if(!defined($in_order)); #if(!defined($self->{code}) && !defined($self->{quantity}) && !defined($self->{price})) { if($line =~ /^(\w+)\s+(.*?)\s+(\d+)\s+(\$\d+\.\d+)/)
($self->{code},$self->{name},$self->{quantity},$self->{price}) = ($line =~ /^(\w+)\s+(.*?)\s+(\d+)\s+(\$\d+\.\d+)/); #print $1 . "\n"; #print $3 . "\n"; #print $4 . "\n";
# Need to write items to table at this point in case we have multiple items ordered. #if (defined($self->{code})&&defined($self->{quantity}) &&defined($self->{price})) { my $sth =$dbh->prepare("INSERT INTO miva_retail_items_ordered(order_number,part_number,quantity,price) VALUES (?,?,?,?)");
$sth->execute($self->{order_number},$self->{code},$self->{quantity},$self->{ price}); # ($self->{code},$self->{quantity},$self->{price}) = undef; #} #print" Defined is: $self->{coupon_type} \n"; }elsif ($line =~ /Coupon:\s+(.*):\s+\(\$(\d+\.\d+)\)/){ #print "In coupon\n"; ($self->{coupon_type},$self->{coupon_amount}) = ($line =~ /Coupon:\s+(.*):\s+\(\$(\d+\.\d+)\)/); #print "Coupon is " . $1 . "\n"; #print "Coupon type is " . $2 . "\n"; }elsif ($line =~ /Shipping:(\s+.*:)?\s+\$(\d+\.\d+)/){ ($self->{shipping_method},$self->{shipping_amount}) = ($line =~ /Shipping:(\s+.*:)?\s+\$(\d+\.\d+)/); #print "In shipping\n"; #print "Shipping Method is: " . $1 . "\n"; #print $2 . "\n"; }elsif ($line =~ /Sales Tax\:\s+\$(\d+\.\d+)/) { ( $self->{sales_tax}) = ($line =~ /Sales Tax\:\s+\$(\d+\.\d+)/); # print "In sales tax: $self->{sales_tax}\n"; #print "Sales Tax is: " . $1 . "\n";
#}elsif (!defined($self->{COD})){ }elsif($line =~ /COD\s+Charge:\s+\$(\d+\.\d+)/){ ($self->{COD}) = ($line =~ /COD\s+Charge:\s+\$(\d+\.\d+)/); #print "DOC is: ". $1 . "\n"; #$in_order = undef; }elsif ($line =~ /\s+Total:\s+\$(\d+\.\d+)/) { $self->{total}= $1; print PARSED "Shippint: $self->{total}\n"; } # endif
} }
# Trim all the spaces before and after the field. $self->{$_} =~ s/^\s+|\s+$// foreach (keys %$self);
}
