Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Newsletter supplementary material

Posted on 2004-10-04
3
Medium Priority
?
500 Views
Last Modified: 2011-09-20
-The Perl code explination
 -The code
0
Comment
Question by:turn123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 11

Author Comment

by:turn123
ID: 14256360
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
 
LVL 11

Author Comment

by:turn123
ID: 14256363
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
 
LVL 11

Accepted Solution

by:
turn123 earned 0 total points
ID: 14333423
Question moved to Productivity Applications and closed.

turn123,
Page Editor -- Productivity Applications
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As the Marketing Director for the company I work for, time is a commodity that I have relatively little of.  My time is divided between coaching my team, creating a direction for our company's future AND actually getting stuff done.  As a result, I …
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question