Overview Example
When working with an ERP integration or another system that requires Magento data to be in a very specific format there are a couple of options. You could write a custom module to handle converting the Magento data directly into the format that the end product requires, or you could use an already built module for it.
Recently I found myself in a situation where a client had a custom ERP integration that needed re-formatted data. Additionally, the format was quite a bit outside the format of the Magento data. For a situation like this, I turn to using XTENTO Order Export as it comes with an XSLT option.
This will only be covering the XSLT portion of this. XTENTO has excellent documentation on how to utilize their product.
XSLT in it’s simplest form is a language to convert XML into other formats such as HTML or XML formatted differently.
If you have some XML structured like the following:
<?xml version="1.0" encoding="UTF-8"?> <buster> <first_name>Jamie</first_name> <last_name>Hyneman</last_name> <pet_name>Zero</pet_name> </buster> <buster> <first_name>Karie</first_name> <last_name>Byron</last_name> <pet_name>Gertie</pet_name> </buster>
Maybe you just want to get the pet names from this document. The following XSLT code would accomplish that goal.
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns_xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<ul>
<xsl:for-each select="buster">
<li>
<xsl:value-of select="pet_name" />
</li>
</xsl:for-each>
</ul>
</xsl:template>
</xsl:stylesheet>
The output from the XSLT above is below, considering the size of the data sample we could have done this manually and it would not have been an overwhelming amount of work.
Zero
Gertie
Now that we have a working knowledge of what XSLT is used for, as well as an idea of what kind of results we can achieve, let us move onto something a bit more useful. We will be converting a Magento order XML from Cute Cats Inc. into an XML format that their ERP is expecting.
This is business critical as people need their cute cat pictures to go out, so the faster the better from the client perspective.
For the purposes of the following examples, we will be working with an order, of the following products:
Product Name Product Sku Product Qty Product Price Cat, Awesome 150-sku-01 1 $699.00 Cat, Cool 150-sku-02 1 $555.00
The following is an example of the XML structure we will be working with. Magento stores its orders in a predictable fashion via XML. This example is not exact but it is close enough for the purposes of our examples.
<!-- simplification of how magento stores order information in an xml -->
<!-- Cute Cats Incorporated - We specialize in cuteness -->
<orders>
<order>
<increment_id>100000034</increment_id>
<order_item>
<name>Cat, Awesome</name>
<sku>150-sku-01</sku>
<qty>1</qty>
<price>699.000</qty>
</order_item>
<order_item>
<name>Cat, Awesome</name>
<sku>150-sku-01</sku>
<qty>1</qty>
<price>699.000</qty>
</order_item>
</order>
</orders>
The format that we are trying to reach is like this example:
<!-- example ERP format for orders -->
<!-- Business as Usual LLC - ERP Division -->
100000034,1
<!-- Order Id, number of order (if you had multiple orders this would increment) -->
Cat Awesome,150-sku-01,1.0000,699.0000, <!-- Item Name, Sku, QTY, Price -->
Cat Cool,150-sku-02,1.0000,555.0000, <!-- <!-- Item Name, Sku, QTY, Price -->
To accomplish this we will create an XSL template which will be used for all the orders from Magento. An XSLT template is defined in order to give the XML predefined locations to be output within the document.
The basic structure of an XSL Template is like this:
<?xml version="1.0"?>
<!-- the files tag is to hold every file we work with, its one in most cases with XTENTO -->
<files>
<!-- the file tag is for each individual file -->
<!-- you can control the name of the file output here as seen in the filename attribute -->
<file filename="%lastorderincrementid%.so">
<!-- the xsl:stylesheet is where the actual xsl begins it is important to include the xmlns to php for XTENTO -->
<xsl:stylesheet version="1.0" xmlns_xsl="http://www.w3.org/1999/XSL/Transform" xmlns_php="http://php.net/xsl">
<!-- the output method text tells the xsl what kind of output it will be -->
<xsl:output method="text"/>
<!-- constants are declared if you plan to use them for more than one thing I generally create one for separators -->
<xsl:variable name="sepstart" select="'"'"/>
<xsl:variable name="sepend" select="','"/>
<!-- I also create one for an empty value if my csv will have one -->
<xsl:variable name="emptyval" select="'~'" />
<!-- xsl template match this tells it what character to match on for the template -->
<xsl:template match="/">
<!-- Template will go here -->
<!-- This is where we will be doing most of the work in this blog -->
</xsl:template>
</xsl:stylesheet>
</file>
</files>
We can define constant variables, these can hold field separators as I have done, or any value you want to remain constant, you can then refer to these throughout the XSLT template.
<xsl:variable name="sepstart" select="'"'"/>
<xsl:variable name="sepend" select="','"/>
<xsl:variable name="emptyval" select="'~'" />
Using Simple Fields
I plan to examine three cases with this system that are useful, a simple example, an example using PHP native functionality, and a more complex solution that uses custom built static methods to compare data to an admin field. This multi-layered complexity allows you to change the data as needed to fit practically any requirements.
For the purpose of our simple example we are going to look at a case where we need a simple CSV output with: the sku of every item ordered, the quantity ordered, and the price.
Using XTENTO we will have some basic structure already in place allowing us to loop over order items, and gain access to the data contained within.
<!-- Loop Example -->
<xsl:for-each select="orders/order">
<!-- loop over orders here -->
</xsl:for-each>
Simple Fields – Example 1
First let’s look at how we work with pulling basic field data off of an xml node.
<xsl:value-of select="sku"/>
This would return the following:
150-sku-01,
This is not a lot of code, and it is fairly readable in that form as well. You will get the value of whatever node is selected (in this case, “SKU”).
So to put together some data using the looping we have looked at and how to pull data from nodes would look like:
<xsl:for-each select="orders/order">
<!-- order id -->
<xsl:value-of select="increment_id"/>
<xsl:value-of select="$sepend" />
<xsl:text>
</xsl:text> <!-- this inserts a line break -->
<xsl:for-each select="items/item">
<!-- loop over order items -->
<!-- sku -->
<xsl:value-of select="sku"/>
<xsl:value-of select="$sepend" />
<!-- qty ordered -->
<xsl:value-of select="qty_ordered"/>
<xsl:value-of select="$sepend" />
<!-- price -->
<xsl:value-of select="price"/>
<xsl:value-of select="$sepend" />
<xsl:text>
</xsl:text>
</xsl:for-each>
</xsl:for-each>
Which would output the following:
100000034, <!-- order id -->
150-sku-01,1.0000,699.0000, <!-- sku, qty ordered, price -->
150-sku-02,1.0000,555.0000, <!-- sku, qty ordered, price -->
With only a short template in XSLT, the order has already been changed from a long XML document into a short little piece of CSV data, with exactly the details desired.
PHP Functions – Example 2
Now for a more complex example, where we can call to some PHP native functionality. It is possible to use any PHP function directly from the XSL Template. In this case, I want to include the title or name of my products, but they include commas so that is an issue as it would break the CSV structure. There are a couple of ways to solve this, one will address the entire document as a whole and then this more focused approach.
First, let’s look at calling a PHP Function by itself:
<xsl:value-of select="php:functionString('preg_replace','/[,]/', '',name)"/>
This allows us to call the PHP function “preg_replace”, pass in a regex to strip out commas, and then replace those. We pass in the field id that we are wanting to pass in as a parameter to it, much as we would when using the function within our regular use of PHP.
So if we were to plug that into the XSL template we already have from above it would look like:
...
<xsl:for-each select="items/item">
<!-- loop over order items -->
<!-- name -->
<xsl:value-of select="php:functionString('preg_replace','/[,]/', '',name)"/>
<xsl:value-of select="$sepend" />
<!-- sku -->
...
This will output the following:
100000034,
Cat Awesome,150-sku-01,1.0000,699.0000,
Cat Cool,150-sku-02,1.0000,555.0000,
As you can see we now have the names of those two very important products, “Cat Awesome” and “Cat Cool” which is the backbone of Cute Cats Inc.
Advanced Functionality – Example 3
Let’s say for the purposes of our example that our client Cute Cats Inc. wants to offer free shipping to wholesale customers, who have a specific wholesale_id, which they can set in the admin (once built in another module). These people are after all selling their most important products so let’s take special care of them. In order to accomplish this, we will need to create a static method to perform the task.
To do this first we have to create an XSL.php helper in a module following Magento best practices, like so:
/**
*
* app/code/local/CLS/OrderExport/Helper/Xsl.php
*/
class CLS_OrderExport_Helper_Xsl extends Xtento_OrderExport_Helper_Xsl
{
const XML_FREE_SHIPPING_WHOLESALE_ID = 'path/to/admin/config/field';
/**
* Return if free shipping is available based on the wholesale id in the admin
* @param $orderId
*
* @return int
*/
static function getIsFreeShippingAvailable($orderId)
{
$freeShippingWholesaleId =
Mage::getStoreConfig(self::XML_FREE_SHIPPING_WHOLESALE_ID);
/** @var $order Mage_Sales_Model_Order*/
$order = Mage::getModel('sales/order')->load($orderId);
$customer =
Mage::getModel('customer/customer')->load($order->getCustomerId());
if ($customer->getWholesaleId() == $freeShippingWholesaleId) {
return 1;
}
return 0;
}
}
Then we need to call that function and pass in the entity_id from the order to the method so it can accomplish the logic work.
...
<!-- free shipping 1 for yes, 0 for no -->
<xsl:value-of select="php:functionString('CLS_OrderExport_Helper_Xsl::getIsFreeShippingAvailable',entity_id)"/>
<xsl:value-of select="$sepend" />
<xsl:text>
</xsl:text>
<xsl:for-each select="items/item">
...
With that done we are now able to use the custom functionality giving us an output of:
100000034,1
Cat Awesome,150-sku-01,1.0000,699.0000,
Cat Cool,150-sku-02,1.0000,555.0000,
This lines up exactly with what the ERP integration is expecting from our client. Not only have we accomplished the task, we did not have to manually edit any XML, or write more than one short method in PHP.
Summary
As you can see using the XTENTO module gives you a lot of speed to hit the ground running with, you can use it out of the box with a few lines of XSLT to format the text in any format required, and with a little extra engineering you can perform complex logic to make the output mimic what you need it to say. Below we have the entire XSLT template we have written all compiled into one entry.
<?xml version="1.0"?>
<!-- the files tag is to hold every file we work with, its one in most cases with XTENTO -->
<files>
<!-- the file tag is for each individual file -->
<!-- you can control the name of the file output here as seen in the filename attribute -->
<file filename="%lastorderincrementid%.so">
<!-- the xsl:stylesheet is where the actual xsl begins it is important to include the xmlns to php for XTENTO -->
<xsl:stylesheet version="1.0" xmlns_xsl="http://www.w3.org/1999/XSL/Transform" xmlns_php="http://php.net/xsl">
<!-- the output method text tells the xsl what kind of output it will be I have never used anything other than text -->
<xsl:output method="text"/>
<!-- constants are declared if you plan to use them for more than one thing I generally create one for seperators -->
<xsl:variable name="sepstart" select="'"'"/>
<xsl:variable name="sepend" select="','"/>
<!-- I also create one for an empty value if my csv will have one -->
<xsl:variable name="emptyval" select="'~'" />
<!-- xsl template match this tells it what character to match on for the template -->
<xsl:template match="/">
<!-- Template will go here -->
<xsl:for-each select="orders/order">
<!-- order id -->
<xsl:value-of select="increment_id"/>
<xsl:value-of select="$sepend" />
<!-- free shipping 1 for yes, 0 for no -->
<xsl:value-of select="php:functionString('CLS_OrderExport_Helper_Xsl::getIsFreeShippingAvailable',entity_id)"/>
<xsl:value-of select="$sepend" />
<xsl:text>
</xsl:text> <!-- this inserts a line break -->
<xsl:for-each select="items/item">
<!-- loop over order items -->
<!-- name -->
<xsl:value-of select="php:functionString('preg_replace','/[,]/', '',name)"/>
<xsl:value-of select="$sepend" />
<!-- sku -->
<xsl:value-of select="sku"/>
<xsl:value-of select="$sepend" />
<!-- qty ordered -->
<xsl:value-of select="qty_ordered"/>
<xsl:value-of select="$sepend" />
<!-- price -->
<xsl:value-of select="price"/>
<xsl:value-of select="$sepend" />
<xsl:text>
</xsl:text>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
</file>
</files>
The XTENTO module links up seamlessly with the Magento interface, giving you a good place to drop in your XSL Template. It also brings with it some other functionality you can leverage to make use of Magento’s data.
Some of those other features that are of use within the XTENTO Export XSL are:
<!-- static output -->
<xsl:text>Whatever you want to output goes here.</xsl:text>
<!-- mapping if/then/else -->
<xsl:choose>
<xsl:when test="shipping_method='abc'"><xsl:text>output this text</xsl:text></xsl:when>
<xsl:when test="shipping_method='xyz'"><xsl:text>output something else</xsl:text></xsl:when>
<xsl:otherwise><xsl:text>nothing matches, output this</xsl:text></xsl:otherwise>
</xsl:choose>
<!-- replace all characters in fields -->
<file filename="file_name.csv" search=",;" replace="--">
<!-- this is actually across all fields so much wider than the preg_replace example above which is only a single field-->
You can find purchase information at Xtento Order Export Module & their Documentation is also available.