Trevor,
Why not try simplify it to 2 fields and remove most of the validation
code? If that works, then you can binary add/delete code until it fails.
BTW 'Red' I guess only works on certain mail clients, but not mine, so
sorry if I missed something obvious.
Regards,
Andy.
Trevor Morrison wrote:
>
> 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
