Search This Blog

Mar 30, 2011

Magento: Generate Daily Order File for Fulfillment Center

Working with product orders is not easy task as we think; it needs a proper inventory management, accuracy and time management aspects. Most of e-commerce companies let fulfillment services to avoid adjournment of orders and maximize their time in enhancing marketing strategies instead of doing that process.

How Does Fulfillment Works?

Fulfillment Companies represent as a warehouse for all your pending orders. The first phase of the process, is requisition of order file from your end every EOD (end of day). Order file includes all informations which serve as the basis for the company in processing your orders. After orders complete, fulfillment will send  confirmation file (shipment confirmation) to inform their clients for order completion.

Generating Order file from Magento?

Dumping order file from Magento needs a PHP script and need to set up a cron job to process the order in a scheduled time.

The first thing that we need to do is to create a script that will pull in order information from the Magento database.

1. In order to pull in order info. first we need to determine magento tables that compose our order file.

Here are the list of tables:
  • sales_flat_order_payment
  • sales_flat_order_grid
  • sales_flat_order
  • sales_flat_order_item
  • sales_flat_order_address
  • directory_country_region

These are the fields that commonly needed by Fulfillment.

  • Order #
  • First Name (Customer)
  • Last Name (Customer)
  • Address
  • City
  • State 
  • Country 
  • Shipping Method
  • Order Date
  • SKU
  • Quantity 

2. Coding the script is next.

#credentials details
$server_host  = 'your_host';
$sql_username = 'sql_username ';
$sql_password = 'sql_password ';
$db         = 'db';

#connect to sql server
sql_connect($server_host, $sql_username,$sql_password,$own_db);

#connections function
function sql_connect($server_host, $mysql_user, $mysql_password,$own_db){

# we connect to
$link = mysql_connect($server_host, $mysql_user, $mysql_password);

if (!$link) {
  die('Could not connect: ' . mysql_error());

 $db_selected = mysql_select_db($own_db, $link);

 if (!$db_selected) {
   die ('Can\'t use ' . $own_db . ' : ' . mysql_error());
  echo 'Connect Successfully'; 

You supposed to query today but of course you need to run this script the end of the day. Then you need to create a csv file attached it to email that will be send to fulfillment personnel.


#get the date of the last order
$last_order = date('Y-m-d', strtotime('yesterday'));

$last_order_filename = date('Ymd', strtotime('yesterday'));

$source = $txtfile = "$last_order_filename.csv";

$mode = FTP_ASCII;

#opening the csv file and make this writable
$fileptr = fopen('/yourfilepath/' . $txtfile, "w"); 

#make the date in this format sample

$yesterday = date('Y-m-d', strtotime('yesterday'));

#query information in the sales flat order table 

$order_query = "SELECT sfo.entity_id, sfo.customer_firstname,sfo.customer_lastname, sfo.created_at, sfo.updated_at, sfo.customer_id, sfog.billing_name, sfo.increment_id, sfog.shipping_name, sfo.shipping_description, sfog.shipping_name, sfo.customer_email, sfo.tax_amount, sfop.shipping_amount FROM sales_flat_order AS sfo, sales_flat_order_grid AS sfog, sales_flat_order_payment as sfop WHERE sfo.entity_id = sfop.parent_id AND sfo.created_at = sfog.created_at AND sfo.updated_at BETWEEN '$yesterday 00:00:00' AND '$yesterday 23:59:59'";

$result = mysql_query($order_query);

#create a csv file

$file_name = $last_order_filename.".csv";  
$fp = fopen($file_name, 'w');
$order_result = array();

$i = 0;

if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {

    $order_result[$i]['Sales Order Number'] = $row['increment_id'];
    $order_result[$i]['Date']  = $row['updated_at'];
    $order_result[$i]['Ship Via Code']      = $row['shipping_description'];
    $order_result[$i]['Email']  = $row['customer_email'];
    $order_result[$i]['Firstname']   = $row['customer_firstname'];
    $order_result[$i]['Lastname']   = $row['customer_lastname'];
    $item_id = $row['entity_id'];
    #Items Information Query based on the $order_query
$ordered_item_query = "SELECT sfoi.sku,, sfoi.qty_ordered, sfoi.price, sfoi.original_price FROM sales_flat_order_item as sfoi WHERE $item_id = sfoi.order_id";
 $ordered_item_result = mysql_query($ordered_item_query);

 if (mysql_num_rows($ordered_item_result) > 0) {
 $item_counter = 0;
           while ($row_item = mysql_fetch_assoc($ordered_item_result)) {
 $order_result[$i]['Product SKU'][$item_counter]  = $row_item['sku'];
 $order_result[$i]['Ordered Qty'][$item_counter]  = $row_item['qty_ordered'];
 $order_result[$i]['Item Count'] = $item_counter;
#Shipping Information Query based on the $order_query
$shipping_query = "SELECT sfoa.street,, sfoa.region, sfoa.postcode,,, sfoa.country_id FROM sales_flat_order_address as sfoa WHERE $item_id = sfoa.parent_id AND sfoa.address_type = 'shipping'";
$shipping_result = mysql_query($shipping_query);
if (mysql_num_rows($shipping_result) > 0) {
 while ($shipping_row = mysql_fetch_assoc($shipping_result)) {
$order_result[$i]['Shipping Company'] = $shipping_row['company'];
$order_result[$i]['Shipping Address'] = $shipping_row['street'];
$order_result[$i]['Shipping City'] = $shipping_row['city'];
$state = $shipping_row['region'];
$state_query = "SELECT dcr.code FROM directory_country_region as dcr WHERE dcr.default_name = '$state'";
$state_result = mysql_query($state_query);
$state_row = mysql_fetch_assoc($state_result);
$order_result[$i]['Shipping State'] = $state_row['code'];
$order_result[$i]['Shipping Zip Code']  = $shipping_row['postcode'];
$order_result[$i]['Shipping Country']  = $shipping_row['country_id'];

$order_result[$i]['Shipping Email'] = $shipping_row['email'];
   #Billing Information Query based on the $order_query
$billing_sql = "SELECT sfoa.street,, sfoa.region,, sfoa.postcode, sfoa.telephone,, sfoa.country_id FROM sales_flat_order_address as sfoa WHERE $item_id = sfoa.parent_id AND sfoa.address_type = 'billing'";
$billing_result = mysql_query($billing_sql);
if (mysql_num_rows($billing_result) > 0) {
 while ($billing_row = mysql_fetch_assoc($billing_result)) {
$order_result[$i]['Billing Company']  = $billing_row['company'];
$order_result[$i]['Billing Address']  = $billing_row['street'];
$order_result[$i]['Billing City'] = $billing_row['city'];
#get the state text
 $state  = $billing_row['region'];
$state_sql = "SELECT dr.code FROM directory_country_region as dr WHERE dr.default_name = '$state'";
$state_result = mysql_query($state_sql);
$state_row = mysql_fetch_assoc($state_result);
$order_result[$i]['Billing State'] = $state_row['code'];
$order_result[$i]['Billing Zip Code'] = $billing_row['postcode'];
$order_result[$i]['Billing Email']   = $billing_row['email'];
$order_result[$i]['Billing Country'] = $billing_row['country_id'];

#put the Information in csv file
   for($i = 0; $i < count($order_result); $i++){
       for($c = 0; $c < $order_result[$i]['Item Count']; $c++){
#get rid off the \n \r in billing and shipping addres
 $order_result[$i]['Billing Address']  = preg_replace("/[\n\r]/"," ", $order_result[$i]['Billing Address']);
 $order_result[$i]['Shipping Address'] = preg_replace("/[\n\r]/"," ", $order_result[$i]['Shipping Address']);
 #csv headers 
 fputcsv($fp, array($order_result[$i]['Sales Order Number'],$order_result[$i]['Lastname'],$order_result[$i]['Firstname'],$order_result[$i]['Billing Company'], $order_result[$i]['Billing Address'],"  ",  $order_result[$i]['Billing City'],$order_result[$i]['Billing State'],$order_result[$i]['Billing Zip Code'],$order_result[$i]['Shipping Country'],$order_result[$i]['Ship Via Code'],$order_result[$i]['Date'] ,$order_result[$i]['Product SKU'][$c],$order_result[$i]['Ordered Qty'][$c],$order_result[$i]['Lastname'],$order_result[$i]['Firstname'],$order_result[$i]['Shipping Company'],$order_result[$i]['Shipping Address'], "  " ,$order_result[$i]['Shipping City'],$order_result[$i]['Shipping State'],$order_result[$i]['Shipping Zip Code'],$order_result[$i]['Email']   ,$order_result[$i]['Shipping Country']));


if($fileptr) fclose($fileptr);

# ---------------------------------------#
#          SEND TO EMAIL         #
# ---------------------------------------#

 $fileatt = '/yourfilepath/' . $txtfile; // Path to the file
 $data = fread($file,filesize($fileatt));
 $fileatt_type = "application/octet-stream"; // File Type
 $fileatt_name = $txtfile; // Filename that will be used for the file as the attachment
 $email_from = ""; // Who the email is from
 $email_subject = "Subject - " . $yesterday; // The Subject of the email
 $email_txt = "Sales Order for - " . $yesterday; // Message that the email has in it
 $email_to = ""; 
  $headers = "From: ".$email_from;
  $file = fopen($fileatt,'rb');
  $data = fread($file,filesize($fileatt));
  $semi_rand = md5(time());
  $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";
  $headers .= "\nMIME-Version: 1.0\n" .
              "Content-Type: multipart/mixed;\n" .
              " boundary=\"{$mime_boundary}\"";
  $email_message = "";
  $email_message .= "This is a multi-part message in MIME format.\n\n" .
                  "--{$mime_boundary}\n" .
                  "Content-Type:text/html; charset=\"iso-8859-1\"\n" .
                 "Content-Transfer-Encoding: 7bit\n\n" .
  $email_message . "\n\n";
  $data = chunk_split(base64_encode($data));
  $email_message .= "--{$mime_boundary}\n" .
                    "Content-Type: {$fileatt_type};\n" .
                    " name=\"{$fileatt_name}\"\n" .
                    "Content-Transfer-Encoding: base64\n\n" .
                   $data . "\n\n" .

  $ok = mail($email_to, $email_subject, $email_message, $headers);
  if($ok) {
  echo "Mail Sent.";
  else {
   die("Email Failed.");

3. After testing the script, you can now set up the cron job in your server.

You can download the script here


Utah Jazz said...

Hi! I`m testing your script. I recieve mails, but attached csv file is empty(( i`m using magento
What i need to check?

Janz said...

Hi Utah,

Do you get any error upon running the script?

Are you sure you have order on the day you run the script?

Let me know!



Utah Jazz said...

Sorry for late answer. All work fine.
Almoust)) Product price in csv empty(
I set up cron and run your script every hour.
P/S $db need to change to $own_db

Anonymous said...

Is it possible to upload the script again some where? Or at least tell me where I can put the scripts to run this. I've been so lost lately and this is exactly what I've needed.

Eddy said...

Alright, I got your script to work. It connects successfully, makes the CSV, but it comes out empty. I have "placed orders" on the same day and the day before, but it's still coming back empty. I am using the newest magento version

Any kind of help? Have the sql values changed in the db's from when you made this to now?

Eddy said...

Ok, I went ahead and played with SQL results, because it wasn't grabbing any of the tables. So far what I've seen is it's the dates.

BETWEEN "DATE" AND "DATE" is what is giving me 0 results. When I remove this it gives me of course the results of the orders, but when I put this and try to play with it, I keep getting 0 results. Any idea why? I of course played around with the dates/times and the orders to see if it would come out, but it's the SQL of BETWEEN that's not working.

Janzell Jurilla said...

That's actually working with Magento version 1.5 and 1.6. But i'm not sure with 1.7. I will try it and I will let you know.

But before could you check if you have the correct format of the date in your query?


Neb said...


Have you done any importing of orders into magento by any chance?


Neb said...

Hi have you done any importing of orders into magento by any chance?

Janzell Jurilla said...

Hi Neb,

Yes I do. Thanks for dropping by.

- Janzell

Neb said...

Hi Janzell,

Do you have a tute on it by any chance?