Order Export & Shipping Fulfillment Script For Magento, Version I

back to tech articles
| 18th January 2013
Magento CE 1.7.0.2

There is a newer version of this article available here.

We want to automate the export of the orders received in our Magento store. There are of course extensions to do this, but the process is relatively simple, and the extensions are expensive 😉

We will write a simple php script which can be invoked using a cron job. We can even secure the script (using .htaccess) to ensure that it can’t be accessed by an unsavoury visitor, but we’ll cover that in another article.

We’ll hook into the Magento core code to use the Zend DB layer. This will ensure consistency with the code.

Limitations:

  1. I haven’t covered the loop that creates the CSV/XML file that we will most likely want to export. We’re just looking at how to hook into the db and access the data.
  2. I am querying the sales_flat_order_address table, which means this script does NOT support the multiple address feature provided in Magento (individual addresses for each item). To do that, you need to reference the sales_flat_quote_address table instead.
  3. This script is not a copy/paste job, it will need more to it to make it work sufficiently.

Ok, here’s the code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
<?php
// include the core code we are going to use
require_once('app/Mage.php');
umask (0);
Mage::app('default');

// resources
$resource = Mage::getSingleton('core/resource');

// db access
$db_read = $resource->getConnection('core_read');
$db_write = $resource->getConnection('core_write');

// support table prefix if one is being used
$table_prefix = Mage::getConfig()->getTablePrefix();

// working code below this line
###############################

// count the orders
$order_num = $db_read->fetchOne("SELECT COUNT(*) AS num FROM {$table_prefix}sales_flat_order WHERE status = 'pending'");

// get an array of the orders
$orders = $db_read->fetchAll("SELECT * FROM {$table_prefix}sales_flat_order WHERE status = 'pending'");

for($i=0; $i < intval($order_num); $i++) {
$orderid = $orders[$i]['entity_id'];

$items = $db_read->fetchAll("SELECT
    items.order_id AS orderid,
    items.item_id AS itemid,
    orders.total_item_count AS total_items_in_order,
    items.quote_item_id AS quoteid,
    items.created_at AS orderdate,
    items.product_type,
    items.sku AS itemcode,
    items.name AS itemname,
    items.qty_ordered AS qty_ordered,
    items.qty_shipped AS qty_shipped,
    address.email AS email,
    address.prefix AS title,
    address.firstname AS firstname,
    address.lastname AS lastname,
    address.street AS address,
    address.city AS city,
    address.region AS region,
    address.country_id AS country,
    address.postcode AS postcode,
    address.telephone AS telephone
  FROM sales_flat_order AS orders
    JOIN sales_flat_order_item AS items
      ON items.order_id = orders.entity_id
    LEFT JOIN sales_flat_order_address AS address
      ON orders.entity_id = address.parent_id
  WHERE
    items.order_id = $orderid
    AND address.address_type = 'shipping'
    AND orders.status = 'pending'
");
}

// create a loop and put the order stuff into it and write to csv/xml...

?>

Hopefully this will provide you with a good starting point for your own final script and will help you with accessing the database in a clean, efficient manner.

Submissions of your own code will be gratefully accepted and we can update this post to be even more useful 🙂