[Date Prev][Date Next][Thread Prev][Thread Next][Interchange by date
][Interchange by thread
]
[ic] Preparing Orders
>> I worte a perl/DBI script that extracts data from both the
>> transactions and orderline tables, then outputs a pipe-delimeted
>> flatfile. I use this flatfile along with a word processor's "mail
>> merge" tool to generate faxes... one for each order. I fax these to
>> suppliers who fill the orders. The same could be done for packing slips.
>
> I can just query the (mySQL) database and do a similar thing, but if
> possible I would love to see the script you did write, that (being DBI)
> would probably work for me also.
>
>>
>> This takes place outside of IC and there may well be easier ways. But
>> this works well for me. If you'd like a copy of this script, post here
>> and I'll be happy to share.
>
> Please!
>
> Thanks,
> Michael
OK here's the script. Note that my products databse has a few colums in
addition to the foundation default. But it is a simple script and I'm
sure you can easily modify it for your own use. It works well for me.
I don't know if this will get wrapped strangely when I post but here goes.
DB
---- START SCRIPT --------
#!/usr/bin/perl
use DBI;
# Connect To Database
# * The DBI interface to MySQL uses the method "connect" to make a
# * connection to the database. It takes as it's first argument
# * the string "DBI:mysql:database:hostname", where database is equal
# * to the name of your database, and hostname to the server that it's
# * located on. The second and third arguments, respectively, should
# * be your account username and password. The connection is assigned.
# * to a variable that is used by most other methods in the module.
$database = "MY_data";
$username = "myusername";
$password = "mypassword";
$db = DBI->connect("DBI:mysql:$database:$hostname", $username, $password)
or die "Couldn't connect to database: " . DBI->errstr;
# Execute a Query
# * executing a query is done in two steps. First,
# * the query is setup using the "prepare" method.
# * this requires the use of the variable used to
# * initiate the connection. Second, the "execute"
# * method is called, as shown below.
$query = $db->prepare("SELECT * FROM transactions ORDER BY order_number")
or die "Couldn't prepare statement: " . $db->errstr;
$query->execute
or die "Couldn't execute statement: " . $query->errstr;
# open output file
open (OUTFILE, ">/home/username/orders/results.txt");
# print header row
print OUTFILE "order_number|";
print OUTFILE "order_date|";
print OUTFILE "cc info|";
print OUTFILE "total_cost|";
print OUTFILE "invoice(s)|";
print OUTFILE "notes|";
print OUTFILE "comments|";
print OUTFILE "completed|";
print OUTFILE "fname|";
print OUTFILE "lname|";
print OUTFILE "company|";
print OUTFILE "address1|";
print OUTFILE "address2|";
print OUTFILE "city|";
print OUTFILE "state|";
print OUTFILE "zip|";
print OUTFILE "country|";
print OUTFILE "phone_day|";
print OUTFILE "phone_night|";
print OUTFILE "email|";
print OUTFILE "b_fname|";
print OUTFILE "b_lname|";
print OUTFILE "b_company|";
print OUTFILE "b_address1|";
print OUTFILE "b_address2|";
print OUTFILE "b_city|";
print OUTFILE "b_state|";
print OUTFILE "b_zip|";
print OUTFILE "b_country|";
print OUTFILE "b_phone|";
print OUTFILE "subtotal|";
print OUTFILE "shipping|";
print OUTFILE "salestax|";
print OUTFILE "sku1|";
print OUTFILE "price1|";
print OUTFILE "qty1|";
print OUTFILE "description1|";
print OUTFILE "sku2|";
print OUTFILE "price2|";
print OUTFILE "qty2|";
print OUTFILE "description2|";
print OUTFILE "sku3|";
print OUTFILE "price3|";
print OUTFILE "qty3|";
print OUTFILE "description3|";
print OUTFILE "sku4|";
print OUTFILE "price4|";
print OUTFILE "qty4|";
print OUTFILE "description4|";
print OUTFILE "sku5|";
print OUTFILE "price5|";
print OUTFILE "qty5|";
print OUTFILE "description5|";
print OUTFILE "sku6|";
print OUTFILE "price6|";
print OUTFILE "qty6|";
print OUTFILE "description6|";
print OUTFILE "sku7|";
print OUTFILE "price7|";
print OUTFILE "qty7|";
print OUTFILE "description7|";
print OUTFILE "sku8|";
print OUTFILE "price8|";
print OUTFILE "qty8|";
print OUTFILE "description8|";
print OUTFILE "sku9|";
print OUTFILE "price9|";
print OUTFILE "qty9|";
print OUTFILE "description9|";
print OUTFILE "sku10|";
print OUTFILE "price10|";
print OUTFILE "qty10|";
print OUTFILE "description10|";
print OUTFILE "sku11|";
print OUTFILE "price11|";
print OUTFILE "qty11|";
print OUTFILE "description11|";
print OUTFILE "sku12|";
print OUTFILE "price12|";
print OUTFILE "qty12|";
print OUTFILE "description12|";
print OUTFILE "sku13|";
print OUTFILE "price13|";
print OUTFILE "qty13|";
print OUTFILE "description13|";
print OUTFILE "sku14|";
print OUTFILE "price14|";
print OUTFILE "qty14|";
print OUTFILE "description14|";
print OUTFILE "sku15|";
print OUTFILE "price15|";
print OUTFILE "qty15|";
print OUTFILE "description15|";
print OUTFILE "sku16|";
print OUTFILE "price16|";
print OUTFILE "qty16|";
print OUTFILE "description16|";
print OUTFILE "sku17|";
print OUTFILE "price17|";
print OUTFILE "qty17|";
print OUTFILE "description17|";
print OUTFILE "sku18|";
print OUTFILE "price18|";
print OUTFILE "qty18|";
print OUTFILE "description18|";
print OUTFILE "sku19|";
print OUTFILE "price19|";
print OUTFILE "qty19|";
print OUTFILE "description19|";
print OUTFILE "sku20|";
print OUTFILE "price20|";
print OUTFILE "qty20|";
print OUTFILE "description20|";
print OUTFILE "\n";
while ($array = $query->fetchrow_hashref) {
print OUTFILE "$array->{order_number}|";
print OUTFILE "$array->{order_date}|";
print OUTFILE " |";
print OUTFILE "$array->{total_cost}|";
print OUTFILE " |";
print OUTFILE " |";
#get comments and strip returns
$comments=$array->{comments};
$comments=~ s/[\x00-\x1F]+/ /g;
print OUTFILE "$comments|";
print OUTFILE " |";
print OUTFILE "$array->{fname}|";
print OUTFILE "$array->{lname}|";
print OUTFILE "$array->{company}|";
print OUTFILE "$array->{address1}|";
print OUTFILE "$array->{address2}|";
print OUTFILE "$array->{city}|";
print OUTFILE "$array->{state}|";
print OUTFILE "$array->{zip}|";
print OUTFILE "$array->{country}|";
print OUTFILE "$array->{phone_day}|";
print OUTFILE "$array->{phone_night}|";
print OUTFILE "$array->{email}|";
print OUTFILE "$array->{b_fname}|";
print OUTFILE "$array->{b_lname}|";
print OUTFILE "$array->{b_compnay}|";
print OUTFILE "$array->{b_address1}|";
print OUTFILE "$array->{b_address2}|";
print OUTFILE "$array->{b_city}|";
print OUTFILE "$array->{b_state}|";
print OUTFILE "$array->{b_zip}|";
print OUTFILE "$array->{b_country}|";
print OUTFILE "$array->{b_phone}|";
print OUTFILE "$array->{subtotal}|";
print OUTFILE "$array->{shipping}|";
print OUTFILE "$array->{salestax}|";
# start substuff
# Execute a 2nd Query
$query2 = $db->prepare("SELECT *FROM orderline WHERE order_number =
$array->{order_number} ORDER BY sku")
or die "Couldn't prepare statement: " . $db->errstr;
$query2->execute
or die "Couldn't execute statement: " . $query2->errstr;
#Display results
# $query2->dump_results;
$x=0;
while ($row = $query2->fetchrow_hashref) {
$x=$x+1;
print OUTFILE "$row->{sku}|";
print OUTFILE "$row->{price}|";
print OUTFILE "$row->{quantity}|";
print OUTFILE "$row->{description}|";
}
#if less than 20 item, pad with blanks to 20
until($x==20){
$x=$x+1;
print OUTFILE " |";
print OUTFILE " |";
print OUTFILE " |";
print OUTFILE " |";
}
print OUTFILE "\n";
$query2->finish;
# end substuff
}
# Cleaning Up
# * with the DBI module, it is a good idea to clean up by
# * explicitly ending all queries with the "finish" method,
# * and all connections with the "disconnect" method.
$query->finish;
$db->disconnect;
close OUTFILE;
exit(0);
------------- END SCRIPT ------