• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

Newsletter supplementary material

-The Perl code explination
 -The code
0
turn123
Asked:
turn123
  • 3
1 Solution
 
turn123Author Commented:
Not much to explain this time.  Create a new text document in the same directory as the separate .pl file give in http://www.experts-exchange.com/Q_21188390.html#14117768 -> name it something.pl -> copy and paste the code into it.
0
 
turn123Author Commented:
use strict;
use DBI;
use XML::Simple;

my $filename = "shipping_stuff.mdb";
my $dsn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$filename";
my $dbh = DBI->connect("dbi:ODBC:$dsn", "", "")
     or die "Can't open source DB: $!\n";

my $data = XMLin("output.xml",KeyAttr => ["DataExtName"], ForceArray => [ "DataExtRet","InvoiceLineRet","InvoiceRet" ]);
my $control = 0;
while ($data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{RefNumber} =~ /(.+)/) {
      my $sqlstatement = "INSERT into inv VALUES ('";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{RefNumber}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{TimeCreated}','";
      if ($data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr3} =~ /.+/) {
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr1}','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr2}','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr3}','";
      } elsif ($data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr2} =~ /.+/) {
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr1}','";
            $sqlstatement .= "','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr2}','";
      } else {
            $sqlstatement .= "','";
            $sqlstatement .= "','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr1}','";
      }
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr4}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{City}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{State}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{PostalCode}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Country}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{PONumber}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{TermsRef}->{FullName}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipMethodRef}->{FullName}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{SalesRepRef}->{FullName}','";
      if ($data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr3} =~ /.+/) {
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr1}','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr2}','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr3}','";
      } elsif ($data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr2} =~ /.+/) {
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr1}','";
            $sqlstatement .= "','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr2}','";
      } else {
            $sqlstatement .= "','";
            $sqlstatement .= "','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr1}','";
      }
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr4}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{City}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{State}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{PostalCode}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Country}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{EditSequence}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{TxnID}')";
      open SQL, ">> sql.txt";
      print SQL $sqlstatement . "\n";
      close SQL;
      my $statementhandle = $dbh->prepare("DELETE from inv where ID = '$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{RefNumber}'");
      $statementhandle->execute();
      my $statementhandle = $dbh->prepare($sqlstatement) || die "Cannot prepare statement";
      $statementhandle->execute() || die "Could not execute SQL statement, maybe invalid?";
      ++$control;
}
0
 
turn123Author Commented:
Question moved to Productivity Applications and closed.

turn123,
Page Editor -- Productivity Applications
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now