Miscellany

<---------------billParseJO.pl------------------->
open BILL, "currBill.csv" || die "No bill found!\n";
use text::csv;
my $date;
my $csv = Text::CSV->new();
my $xml = "";
my $xmlFirst = "";
my $billNumber;

while (<BILL>) {
    if ($csv->parse($_)) {
        my @field = $csv->fields();
        $field[30] =~ s/\&/\&amp\;/g;
        $date = $field[4];
        $billNumber = $field[3];
        $ref1 = $field[7];
        $ref2 = $field[8];
        $shipDate = $field[22];
        $custName = $field[30];
        $tempFrtCost = $field[36] - $field[37];
        if ($tempFrtCost =~ /^\-?(\d+\.\d{2})$/) {
            $frtCost = $1;
        } elsif ($tempFrtCost =~ /^\-?(\d+\.\d)$/) {
            $frtCost = $1."0";
        } elsif ($tempFrtCost =~ /^\-"(\d+\.)$/) {
            $frtCost = $1."00";
        } elsif ($tempFrtCost =~ /^\-?(\d+)$/) {
            $frtCost = $1.'.00';
        } else {
            print "$tempFrtCost is an invalid format\n";
            next;
        }

        $xml .= '    <ExpenseLineAdd>
                <AccountRef>
                            <FullName>Postage and Delivery</FullName>
                        </AccountRef>
                        <Amount>'.$frtCost.'</Amount>
                <Memo>'.$shipDate.' - '.$custName.' - '.$ref2.' - '.$ref1.'  </Memo>
                         </ExpenseLineAdd>
';

    } else {
        die "The XML input file was invalid!";
    }
}

if ($date =~ /(\d+)(\d{2})(\d{4})/) {
    my $month = $1;
    my $day = $2;
    my $year = $3;
    $month = "0$1" if ($month =~ /^(\d)$/);
    $date = "$year-$month-$day";
} else {
    die "The date isn't right :-(";
}

open XML, "> UPSBill.xml";

$xmlFirst = '<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <BillAddRq requestID="1" >
                <BillAdd>
                    <VendorRef>
                        <FullName>UPS</FullName>
                    </VendorRef>
                    <APAccountRef>
                        <FullName>Accounts Payable</FullName>
                    </APAccountRef>
                    <TxnDate>'.$date.'</TxnDate>
                    <DueDate>'.$date.'</DueDate>
                    <RefNumber>'.$billNumber.'</RefNumber>
                    <TermsRef>
                        <FullName>Net 30</FullName>
                    </TermsRef>
';

$xml .= '                </BillAdd>
            </BillAddRq>
        </QBXMLMsgsRq>
    </QBXML>';

print XML $xmlFirst;
print XML $xml;
<----------------------END-------------------->

<----------------------BillImport.pl--------------->

use Win32::OLE;
use Win32::OLE::Const;
use Win32::OLE::Const 'QBXMLRP2';
Win32::OLE->Option(Warn=>3);
my $lib=Win32::OLE::Const->Load('QBXMLRP2');

#
# Connect
#

our $qbxmlrp = Win32::OLE->new("QBXMLRP2.RequestProcessor");
my $qbfile = "";
$qbxmlrp->OpenConnection("","UPStest");
our $qbticket = $qbxmlrp->BeginSession("$qbfile", 2);
$actualFile = $qbxmlrp->GetCurrentCompanyFileName($qbticket);
print "Companyfile is $actualFile\n";
exit 1 unless ($actualFile);

#
# Read the request file and send it to QuickBooks
#

open(IN,$ARGV[0]) or die "Could not open request file: $ARGV[0]: $!\n";
my @req = <IN>;
my $req = join("\n", @req);
close IN;
$xmlresponse = $qbxmlrp->ProcessRequest($qbticket,$req);
print $xmlresponse;

unless ($xmlresponse =~ /statusCode\=\"0\"/) {
    $xmlresponse =~ /statusCode\=\"(\d+)\"/;
    open LOG, ">> oopslog.txt" || open LOG, "> oopslog.txt";
    print LOG "$1\n";
    close LOG;
}

#
# Close connection, etc.
#

$qbxmlrp->EndSession($qbticket);
$qbxmlrp->CloseConnection();
undef $qbxmlrp;

<-----------------------END-------------------------->

<-----------------------currBill.csv-------------------->
3,0000FY9001,,,,0,,,,,,0,,,,,,,,IDD,INVOICE DUE DATE,,9282005,,,,,,,,,,,,,,0,0
3,0000FY9001,0000FY9001,00000FY9001385,9172005,185.46,1ZFY90010190800083,,Marauder UT cofa,,jimwharton,1,LTR,,,,,,107,ISS,COM. NEXT DAY AIR,1,9142005,Jim Wharton,Cornerstone Collection Supp,16 Norcross St,Roswell,GA,30075,Divisions of Corpora,State of Utah,160 East 300 South 2nd Floor,SALT LAKE CITY,UT,84111,US,21.63,0
<---------------------END---------------------->
back to top

    * Add to Knowledgebase
    * View More Solutions
    *
      Bookmark:

	 	
	10.17.2005 at 06:04AM PDT, ID: 15098933
	
turn123:
Alternate version of billparse

<-----------------------------BillParse.pl--------------------------------------->
open BILL, "<currbill.csv" || die "No bill found!\n";
use text::csv;
my $date;
my $csv = Text::CSV->new();
my $xml = "";
my $xmlFirst = "";
my $billNumber;

while (<BILL>) {
      if ($csv->parse($_)) {
            my @field = $csv->fields();
            $date = $field[4];
            $billNumber = $field[3];
            $xml .= '                        <ItemLineAdd>
                              <ItemRef>
                                    <FullName>Package</FullName>
                              </ItemRef>
                              <Desc>This is a line from the UPS bill with tracking number and whatever info you want</Desc>
                              <Quantity>1</Quantity>
                              <Cost>2</Cost>
                        </ItemLineAdd>
';

      } else {
            die "The XML input file was invalid!";
      }
}

if ($date =~ /(\d+)(\d{2})(\d{4})/) {
      my $month = $1;
      my $day = $2;
      my $year = $3;
      $month = "0$1" if ($month =~ /^(\d)$/);
      $date = "$year-$month-$day";
} else {
      die "The date isn't right :-(";
}

open XML, "> UPSBill.xml";

$xmlFirst = '<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <BillAddRq requestID="1" >
                  <BillAdd>
                        <VendorRef>
                              <FullName>UPS</FullName>
                        </VendorRef>
                        <APAccountRef>
                              <FullName>Accounts Payable</FullName>
                        </APAccountRef>
                        <TxnDate>'.$date.'</TxnDate>
                        <DueDate>'.$date.'</DueDate>
                        <RefNumber>'.$billNumber.'</RefNumber>
                        <TermsRef>
                              <FullName>Net 30</FullName>
                        </TermsRef>
';

$xml .= '                  </BillAdd>
            </BillAddRq>
        </QBXMLMsgsRq>
    </QBXML>';

print XML $xmlFirst;
print XML $xml;
<------------------------------END------------------------------------->