[ic] Preparing Orders

[DB]

>> 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 ------