How Magento deals with the customer's orders ?

Orders in Magento are arranged as a structured group of data that are shared in different database table instances.

The main instance is the Order table that contains the summary of each order placed (date, total amount, tax, customer,...).

Attached to this instance there are 3 sub-instances concerning the order details :

• Product
• Shipping address
• Billing address

These instances describe which products have been ordered and gives details about the customer's adresses.

4 other sub-instances, help to store the different steps in the the order process : 

• Payments
• Invoice
• Shipment
• creditmemo

The above instances can be summarized in a simple diagram :

Of course this diagram doesn't display all the data available in each instance, there is much more data you can extract from your Magento database.

That is where Orders Export Tool can help.

How "Orders Export Tool" exports the Magento Data ?

Orders Export Tool enables the export of all the order data into Xml or Txt/Csv files.

These files can then be transfered into your CRM/ERP software or can be used for reporting purposes.

The way this extension deals with the order data is very simple. When configuring your export profile, you have to complete a template in which you can use static and dynamic values.

Static values are data that don't change from one order to another and that are added in the ouput file to make it readable and dynamic values are data that are different for each order (price, address, product ordered...).

Imagine you want to export all the orders placed into you Magento webstore and what you want to get is a simple txt file consisting of the order # and the total amount of each order.  What you get should look like :

#123456789 	$150.00
#234567891	$275.00
#345678912	$450.00

Your template would be very simple :

#{increment_id}  ${total_paid}

the # and the $ are static values while {increment_id} and {total_paid} are dynamic values that changed for each order.

{increment_id} and {total_paid} refer to the order instance and could also be written {increment_id order} and {total_paid order}.

You might need to complete your export file with the method used by your customers to pay their order. This data can be found in the payment instance. And you should use {method payment}. This syntax means you want to know the value of the payment method for each order.

You might want to know which syntax to use? No worries, Orders Export Tool includes a full library of all data available for all instances : order, products, payment, invoice, shipment, creditmemo...

How to configure your export profile with Orders Export Tool?

When creating or editing an export profile in Orders Export Tool, you have to be aware of some basic settings: 

Export file configuration

• File name : the base name of the exported files (eg : myOrders), no need to add any file extension (xml, csv, txt), the extension does it for you
• File type : this is the main setting that defines if you want either an XML file type or a CSV/TXT file type. Depending on this setting the template section will be managed differently
• File directory : the directory in which the generated file will be saved. We recommend using a protected directory such as var/
• File name format : your file name can be completed with the current date at the time of the exportation. Different formats are available.

Orders export configuration

This section allows you to define which group of orders must be exported

• Store views : allows you to choose one or more store views from your webstore(s) to use in the exportation process
  
• Start with order # : allows you to specify a order number from which to start the export process
  
• Register the last exported # :  allows you to automatically register the last exported order into the 'Start with order #' input
  
• Flag each exported order : allows you to flag each exported order as exported in Sales > Orders
• Export only unmarked orders : allows you to specify that only the orders not yet flagged must be exported

Filters

This section allows you to specify the type of order you want to export

• Order status : at least one status must be selected
• Customer Group : at least one customer group must be selected
• Advanced Filters : allows you to create advanced filters based on specific attributes/values

Ftp Settings

This section defines if and how the files must be uploaded to any FTP location

Scheduled tasks

This section defines when (days and hours) the profiles must be run

Advanced Options

This section defines some very specific behaviors such as multi-file generation and sort process.

How to configure your XML template ?

An XML template is made up of 3 parts :

• the header pattern which is at the start of the file

<orders>

• the order pattern which is iterated for each order included in the file

<order>     
    <customer>
     {customer_lastname,[strtoupper]} {customer_firstname,[strtolower],[ucfirst]}
    </customer>
    <billing>
        {firstname billing} {lastname billing} 
        {postcode billing} {street billing,[implode]} 
        {city billing} {country_id billing}
    </billing>
    <shipping>
        {firstname shipping} {lastname shipping} 
        {postcode shipping} {street shipping,[implode]}
        {city shipping} {country_id shipping}
    </shipping>
    <items>
        {product::start}
          <item id="{item_id product}">
             <name>{name product}</name>
             <weight>{weight product}</weight>
          </item>
        {product::end}
    </items>
<order>

• the footer pattern which is at the bottom of the file

</orders>

The XML structure allows you to use iterative syntax in different parts of the template, as follows :

{product::start}
    <item id="{item_id product}">
          <name>{name product}</name>
          <weight>{weight product}</weight>
    </item>
{product::end}

In most cases, orders contain more than one product, so it may be useful to create an iteration that loops for each ordered product. All the content between the opening and closing markers will repeated for each product and all dynamics values will be replaced

 {product::start}
...
 {product::end}

These iteration markers can be used for all order instances :

{payment::start}
...
 {payment::end}

{invoice::start}
...
 {invoice::end}

{shipment::start}
...
 {shipment::end}

{creditmemo::start}
...
 {creditmemo::end}
 

How to configure your TXT/CSV export profiles?

To create a CSV/TXT datafeed, you just have to choose 'csv' or 'txt' in File type , in the Configuration tab. The template tab will be updated to match with the file type.

• Include header allows you to include or not the column's name in the first row of your CSV/TXT file.
• Columns name is for the attribute name that can be displayed at the top of the file
• Pattern works in the same way as in XML files.
  Like in XML files, you're allowed to use PHP code if you want to customize the pattern.
  You just have to write your PHP code in one single line, and it will work perfectly.
  You can add a field by using the button Add field . The fields can then be re-ordered easily by using the up and down arrows.
• Field delimiter lets you choose which kind of delimiter you want to use to separate your fields.
• Fields enclosure lets you choose which kind of enclosure you want for your data.

The main difference with the XML mode is that you can't use any iteration instructions. Instead, Orders Export Tool uses an auto-consolidation internal  process. This simply means if you try to get the value of any attribute of an instance (product for example) that contains several items, the extension will automatically duplicate the txt/csv line for each item.

With the following configuration :

Logically the module should export one line for each order, but some orders may contain several product items. The extension will create several lines for the same order, as in the following output example :

Order#         Product Name          Product Sku
#10000001      my product A          AAA
#10000001      my product B          BBB
#10000001      my product C          CCC
#10000002      my product D          DDD
#10000003      my product B          BBB
#10000003      my product E          EEE
...
 

How to create customized attributes or computed values ?

In some cases you might need to get the value of one attribute that is not available in any of the default instances (order, product, address, payment, invoice, shipment or creditmemo). Or you might need to use the value of one attribute but with a computed output.

Orders Export Tool allows to create your own customized attributes directly from your admin panel by using some php code.

Here are 3 examples of common customized attributes :

• Get the color of each product of the order

<? $product=Mage::getModel('catalog/product');
      $product->load($item->getProductId());         
      $value=$product->getColor() ?>

• Get a consolidated string of all product skus in the order

 <? $skus = array();
 foreach ($data['products'] as $product) {
 	$skus[]= $product->getSku();
 }
 return implode(',',$skus); ?>

• Get the status of an order and retrieve a computed value for Google Trusted Stores

<? switch($item->getStatus()){
  case 'canceled': $value= 'MerchantCanceled'; break;
  default : $value= 'BuyerCanceled';
} ?>

In order to create a valid customized attribute, you must respect these rules :

• $value is used to store the data that must be returned. You can also use the return instruction
• $item refers to the item of the current instance , eg :
• {your_custom_attribute order} : $item refers to the order instance
• {your_custom_attribute product} : $item refers to the current product instance
• ...
• $data is an associative array that contains all the instances data of the current order. 
• $data[products] contains all the product data relative to the current order
• $data[payments] contains all the payment data relative to the current order