Ubercart Integration

 

Overview

The uc_edi_xtuple module (which is available in the xChange) allows you to export order and payment information from your Ubercart store into an XML file that is formatted for import into xTuple ERP.

The uc_edi_xtuple module gets almost all of its code from the uc_edi module, developed by Ryan Szama, the man behind Ubercart. We modified this module to make it more capable of exporting xml. We're hoping that our changes will be incorporated back into the core uc_edi module, but if you want to use Ubercart and xTuple for now, you'll need to use the custom uc_edi_xtuple module, available in the xTuple xChange.

The settings below will allow you to import both order and sales or information from Ubercart into xTuple. The orders will be imported as Open Sales Orders, and the payments will be imported as Miscellaneous Credit Memos.

Prerequisites

To set up a webstore using Ubercart you'll need to have the following environment installed:

  • Drupal 6.x (make sure you don't select Drupal 7--we don't support that version yet)
  • Ubercart 2.x (again, make sure to select the Ubercart version that is compatible with Drupal 6) 

This is not meant to be a guide to installing and configuring Drupal and Ubercart, which is a complex multistep process. You can find instructions fo that at the pages above. Of course, we recommend that you install your Drupal site on a PostgreSQL database, but that's just us.

You'll need to install the uc_edi_xtuple module and enable it, and then follow the configuration instructions below, in order to export orders into a format that can be imported into xTuple.

Configuring uc_edi_xtuple

You're going to configure the ux_edi_xtuple module to export data into an xml format. The xml file that is generated will contain all the information needed to insert the customer, salesorder, salesline and payment (armemo) information into xTuple.

Note that there are some hard coded values in the Order Export Pattern section below, which may not apply to your company. In particular, you'll probably want to change the values for the following nodes:

<sales_rep>jsmith</sales_rep>
<tax_zone>VA TAX</tax_zone>
<terms>PREPAY</terms>
 
You may want to change the value of the <shipvia> node value as well. 

Once you've installed the uc_xtuple_edi module and enabled it, go to <yoursite>/admin/store/settings/edi to access edi configuration page. Expand the “Order export settings” section and enter the following settings:

 

Export directory: [Specify the directory on your web server where the module will place the exported file. This directory will need to be writable, so it should be a file outside of your web root]

Export archive directory:

[Specify the directory where the exported file will be moved when it is archived.]
Export file prefix: [Enter a prefix to use for your exported order files. Leave blank for none.]
Export file extension: xml
Export by email settings: [Configure this section if you want to get emails. This will be a handy way to keep track of activity if you've automated everything.]
Export method: [Pick one.]

Export frequency

[Select the frequency at which automatic exports should occur.]
Ready for export order status: Payment received
Exported order status: Completed
Export order comment:     -
Order export prefix:     <?xml version="1.0"?>
    <!DOCTYPE xtupleimport SYSTEM "xtupleapi.dtd">
    <xtupleimport>
Order export suffix:     </xtupleimport>
Order export pattern:
!_,
<customer ignore="true">, !_,
<customer_number quote="false">, !_,
getCustNumberFromInfo(',!primary_email,'&#44;, !_, 
',!billing_company,'&#44;, !_,
',!billing_first_name,'&#44;, !_,
',!billing_last_name,'&#44;, !_,
',!billing_name,'&#44;, !_,
'true'), !_,
</customer_number>, !_,
<customer_name quote="false">, !_,
getCustNameFromInfo(',!primary_email,'&#44;, !_,
',!billing_company,'&#44;, !_,
',!billing_first_name,'&#44;, !_,
',!billing_last_name,'&#44;, !_,
',!billing_name,'&#44;, !_,
'true'), !_,
</customer_name>, !_,
<ship_via>Parcel Post</ship_via>, !_,
<default_currency>USD</default_currency>, !_,
<billing_contact_first>,!billing_first_name,</billing_contact_first>, !_,
<billing_contact_last>,!billing_last_name,</billing_contact_last>, !_,
<billing_contact_voice>,!billing_phone,</billing_contact_voice>, !_,
<billing_contact_email>,!primary_email,</billing_contact_email>, !_,
<billing_contact_address1>,!billing_company,</billing_contact_address1>, !_,
<billing_contact_address2>,!billing_street1,</billing_contact_address2>, !_,
<billing_contact_address3>,!billing_street2,</billing_contact_address3>, !_,
<billing_contact_city>,!billing_city,</billing_contact_city>, !_,
<billing_contact_state>,!billing_zone_code,</billing_contact_state>, !_,
<billing_contact_postalcode>,!billing_postal_code,</billing_contact_postalcode>, !_,
<billing_contact_country>,!billing_country_name,</billing_contact_country>, !_,
<billing_contact_address_change value="CHANGEALL"/>, !_,
<correspond_contact_first>,!billing_first_name,</correspond_contact_first>, !_,
<correspond_contact_last>,!billing_last_name,</correspond_contact_last>, !_,
<correspond_contact_voice>,!billing_phone,</correspond_contact_voice>, !_,
<correspond_contact_email>,!primary_email,</correspond_contact_email>, !_,
<correspond_contact_address1>,!billing_company,</correspond_contact_address1>, !_,
<correspond_contact_address2>,!billing_street1,</correspond_contact_address2>, !_,
<correspond_contact_address3>,!billing_street2,</correspond_contact_address3>, !_,
<correspond_contact_city>,!billing_city,</correspond_contact_city>, !_,
<correspond_contact_state>,!billing_zone_code,</correspond_contact_state>, !_,
<correspond_contact_postalcode>,!billing_postal_code,</correspond_contact_postalcode>, !_,
<correspond_contact_country>,!billing_country_name,</correspond_contact_country>, !_,
<notes></notes>, !_,
</customer>, !_,
<salesorder>, !_,
<order_number>,!order_id,</order_number>, !_,
<order_date>,!created_date,</order_date>, !_,
<pack_date>,!created_date,</pack_date>, !_,
<originated_by value="Internet"/>, !_,
<sales_rep>jsmith</sales_rep>, !_,
<tax_zone>VA TAX</tax_zone>, !_,
<terms>PREPAY</terms>, !_,
<customer_number quote="false">, !_,
getCustNumberFromInfo(',!primary_email,'&#44;, !_,
', !billing_company,'&#44;, !_,
', !billing_first_name,'&#44;, !_,
', !billing_last_name,'&#44;, !_,
', !billing_name,'&#44;, !_,
'true'), !_,
</customer_number>, !_,
<billto_name>,!billing_name,</billto_name>, !_,
<billto_address1>,!billing_company,</billto_address1>, !_,
<billto_address2>,!billing_street1,</billto_address2>, !_,
<billto_address3>,!billing_street2,</billto_address3>, !_,
<billto_city>,!billing_city,</billto_city>, !_,
<billto_state>,!billing_zone_code,</billto_state>, !_,
<billto_postal_code>,!billing_postal_code,</billto_postal_code>, !_,
<billto_country>,!billing_country_name,</billto_country>, !_,
<shipto_number quote="false">, !_,
getShiptoNumberFromInfo(, !_,
getCustNameFromInfo(',!primary_email,'&#44;, !_,
',!delivery_company,'&#44;, !_,
',!delivery_first_name,'&#44;, !_,
',!delivery_last_name,'&#44;, !_,
',!delivery_name,'&#44;, !_,
'false'), !_,
&#44;, !_,
''&#44;, !_,
''&#44;, !_,
',!delivery_first_name,'&#44;, !_,
',!delivery_last_name,'&#44;, !_,
',!delivery_name,'&#44;, !_,
',!delivery_street1,'&#44;, !_,
',!delivery_street2,'&#44;, !_,
NULL&#44;, !_,
',!delivery_city,'&#44;, !_,
',!delivery_zone_code,'&#44;, !_,
',!delivery_postal_code,'&#44;, !_,
',!delivery_country_name,'&#44;, !_,
true&#44;, !_,
true), !_,
</shipto_number>, !_,
<shipto_name>,!delivery_name,</shipto_name>, !_,
<shipto_address1>,!delivery_street1,</shipto_address1>, !_,
<shipto_address2>,!delivery_street2,</shipto_address2>, !_,
<shipto_city>,!delivery_city,</shipto_city>, !_,
<shipto_state>,!delivery_zone_code,</shipto_state>, !_,
<shipto_postal_code>,!delivery_postal_code,</shipto_postal_code>, !_,
<shipto_country>,!delivery_country_name,</shipto_country>, !_,
<ship_via>Parcel Post</ship_via>, !_,
<currency>USD</currency>, !_,
<freight>,!uc_shipping_charges,</freight>, !_,
<order_notes></order_notes>, !_,
</salesorder>, !_,
!products, !_,
<armemo>, !_,
<customer_number quote="false">, !_,
getCustNumberFromInfo(',!primary_email,'&#44;, !_, 
', !billing_company,'&#44;, !_,
', !billing_first_name,'&#44;, !_,
', !billing_last_name,'&#44;, !_,
', !billing_name,'&#44;, !_,
'true'), !_,
</customer_number>, !_,
<document_date>,!created_date,</document_date>, !_,
<due_date>,!created_date,</due_date>, !_,
<document_number quote="false">,fetcharmemonumber(),</document_number>, !_,
<document_type>,Credit Memo,</document_type>, !_,
<amount>,!order_total,</amount>, !_, 
<alternate_prepaid_account quote="false">,formatglaccount(109),</alternate_prepaid_account>,!_,   
</armemo>, !_,
Order product export pattern:
<salesline>, !_,
<order_number>,!order_id,</order_number>, !_,
<line_number>,!#,</line_number>, !_,
<item_number>,!model,</item_number>, !_,
<qty_ordered>,!qty,</qty_ordered>, !_,
<net_unit_price>,!price,</net_unit_price>, !_,
<scheduled_date quote="false">',!scheduled_date,'</scheduled_date>, !_,
<notes></notes>, !_,
</salesline>, !_,
Export pattern delimiter:     nothing
Next order batch ID: [leave it blank.]

Exporting a file from Ubercart

Once you have some orders placed on your store, you are ready to export them.

Go to <mysite>/admin/store/edi to get to the EDI Import/Export page.

You will see a coundt of any orders that are in “Payment Received” status, meaning they are ready to be exported.

Click on the “Export Orders Now” button to generate the export file. It will be written to the export directory you specified on the configuration page.

You will probably need a mechanism to move the exported file from that directory to one where xTuple ERP can pick it up for importing. In our own implementation we wrote a script that checks the export  directory for any files and move them to a directory on a different server, where we have our xTuple ERP database.

Configuring xTuple ERP to import the xml file you exported from Ubercart

Here's the cool part: because the file we exported from Ubercart is already formatted in xml according to the xtupleapi DTD, you can import it directly into xTuple without transforming it. You still need to do some basic configuration to get xTuple ready for importing, however, which is described here.

Create import/export directories

You will need to set up a directory on the file system to store the xsl files for import and export filtering. You will also need to specify a default directory where xTuple will look for files to import, and where it will place exported files.

If you plan to run xTuple Connect to get or put files via FTP, keep in mind that all files will be transferred to and from directories relative to the system where Connect (Batch Manager) is running. If the Connect client and the xTuple client are running on different systems, make sure that both systems have access to the same file resource at the same relative path. 
 
You'll need to set up these three directories first, and then continue to the configuration step.
  • import
  • export
  • xslt

Configure XML Import and Export

Go to System | Setup and select Configure Import and Export
 
On the XLST Settings tab
 
Set a Default XSLT Directory (e.g. Users/xTuple/xslt)
 

In the XSLT Processor section, if you're using a Mac, enter this:

Macintosh: xsltproc %x %f

If you're not on a Mac, follow the instructions for setting up an xslt processor for your system, available here: http://www.xtuple.org/YahooStoreIntegration

Go to the Import Settings tab

Set a Default Import File Directory (e.g. /Users/xTuple/import)

Go to the XML Export tab

Set a Default Import File Directory (e.g. /Users/xTuple/export)

Click SAVE

Make sure your items are set up

Before you try to import any orders, make sure you have created items in xTuple to match those in the online catalog. To work properly, the value of the Ubercart SKU field must match up with your xTuple Item Number filed.

xTuple Item Number = Ubercart SKU

Miscellaneous Settings

System->Configure Modules->Sales, Uncheck “Use calculated freight values by default”

System > Configure Modules > Sales, change Sales Order# Generation to either "Automatic, Allow Override" or "Manual".

System > Master Information > Configure Data Import and Export

Import the Order

Once all your import/export settings are configured, and you have an xml file from ubercart in your local import directory, you are ready to import the file.

Go to System | Utilities | Import Data

If you did it right, you'll see the file there, ready to import. Highlight the file and click IMPORT SELECTED

You should now have an open Sales Order created in xTuple.

In xTuple, find and process the order (you can find open orders on the Issue to Shipping screen, and from there you can Ship them and Post the Invoice). 

Configure the Export from xTuple

Now it's time to configure the system so you can export the order status. Here's what to do:

Prepare your xslt file

For the export we will need to do a small xsl transformation, to convert the xml file that is exported into a csv file that can be parsed by Ubercart.

Copy the text below and save it into a file called xmltocsv.xsl:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="iso-8859-1"/>
<xsl:template match="//shippedOrders">
<xsl:value-of select="thing" />, <xsl:value-of select="cohead_number" />, <xsl:value-of select="shiphead_shipvia" />, <xsl:value-of select="shiphead_tracknum" />
</xsl:template>
</xsl:stylesheet>

Put the file into the xslt directory you created earlier.

Configure xTuple to use the xslt file

Go to System | Setup and select Configure Import/Export

On the XSLT Settings tab, find the section labelled: Map of XSLT Import and Export Filters.

Click on NEW and enter the following info:

Map Name: xmltocsv

Document Type: xtupleimport

System Identifier: xtupleapi.dtd

Export XSLT File Name: [your local path]/xslt/xmltocsv.xsl

Click SAVE

Configure the Export

Go to System | Utilities | Export Data

You'll need to define a new query set that will find the orders that have been shipped, and output the data that needs to be returned to Ubercart.

In the Query Set section, click NEW and enter the following info:

Name: UpdateShippedOrders
Description: Select shipped orders to update store status

Queries in this set: 

You need to define two queries for this set. Click NEW to define the first one with these values:

Name: shippedOrders

Order: 0
Type of Query: Custom Query

Paste in this text for the query:

SELECT 'ORD' AS thing, cohead_number, shiphead_shipvia, shiphead_tracknum
FROM shiphead, cohead
WHERE cohead_id = shiphead_order_id
AND shiphead_shipped = 't'
AND shiphead_notes NOT LIKE '%exported%'

And click SAVE

Click NEW and enter the second query:

Name: markAsExported
Order: 1

Query text:

UPDATE shiphead SET shiphead_notes = (shiphead_notes || ' exported')
WHERE shiphead_shipped = 't'
AND shiphead_notes NOT LIKE '%exported%';

Click SAVE

Execute the export

While you are still in the Export Data screen, Click on Alternate XML and select the xmltocsv file you set up earlier.

Select the UpdateShippedOrders query set, and click EXPORT.

Give the export file a useful name and save it into your default export directory.

This method will work fine for testing, but you will probably eventually want to use the Schedule method to set up Connect to FTP the file to a server on a periodic basis, rather than manually exporting each time.

Import the Order Status data into Ubercart

The final step is to import the file into Ubercart. Place the file you exported from xTuple into your default import directory for the uc_edi module.

Go to your ubercart site and to to admin/store/edi. You should see that there is 1 file ready to import.

Click IMPORT ORDERS NOW.

You should see all your shipped orders are now complete.