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.

<?php
error_reporting(0);
#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);
 
?>

<?php
#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());
 }else{
  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.

<?php

#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.name, 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'];
      
 $item_counter++;
    
 $order_result[$i]['Item Count'] = $item_counter;
       
        }
    }
#Shipping Information Query based on the $order_query
    
$shipping_query = "SELECT sfoa.street, sfoa.city, sfoa.region, sfoa.postcode, sfoa.company, sfoa.email, 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'];

if($shipping_row['email']){
          
$order_result[$i]['Shipping Email'] = $shipping_row['email'];
                
}
    
 }
}
   #Billing Information Query based on the $order_query
    
$billing_sql = "SELECT sfoa.street, sfoa.city, sfoa.region, sfoa.company, sfoa.postcode, sfoa.telephone, sfoa.email, 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'];
    
if($billing_row['email']){
          
$order_result[$i]['Billing Email']   = $billing_row['email'];
$order_result[$i]['Billing Country'] = $billing_row['country_id'];
        
        }
      }
    }
    
    
       
  $i++;

    }
    
        
}
#put the Information in csv file
 
if(count($order_result)){
   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']));
     
 
       }
    }
}



fclose($fp);

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 = "noreply@ownproducts.com"; // 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 = "email@email.com"; 
  
  
  $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" .
                    "--{$mime_boundary}--\n";
  

  
  $ok = mail($email_to, $email_subject, $email_message, $headers);
  
  if($ok) {
  echo "Mail Sent.";
  
  sleep(5);
  
   fclose($file); 
  } 
  
  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