Hi,
I am running the latest MySql on a windows 200 machine. I also use the
control center gui to do all my work with. 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 orders items
are written twice to the database. I have looked over my code but only see
one insert statement for order information and 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);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
