E-Commerce Integration

 

Introduction

Many companies that sell products and services over the Web use xTuple ERP to manage their business. We want to do all we can to help connect web-based stores to xTuple ERP databases. This document is intended as a guide to the methods for integrating with the web, and a description of the three free e-commerce integrations on the xChange. We are providing these to the community to download, use, expand and improve.

Data Exchange Methods

There are two ways (that we know of) to connect a web-based store to xTuple ERP:

  1. An exchange of XML files
  2. Directly through the database APIs.

Each of these methods has pros and cons, and each may be best suited for certain scenarios.

XML Method

The XML method means generating an XML file from the web store. This file will contain information about the customer, the order and the payment. This file will then be imported directly into xTuple, or, if the format needs to be change, translated via xslt into the proper form and then imported. We have examples of both of these methods below (Ubercart generates a file that can be imported directly, Yahoo xml must be translated into the xTuple DTD format during import).

API Method

The API method means writing code that allow the e-commerce application to directly access the xTuple ERP tables to write customer, order and payment information into the database.

At first glance, many developers might jump at the API option. It is possible to use our API to pass information back and forth to the store, with the following caveats:

  1. Security. To do this, you will need to open a port to the Postgres database. You can create a user "webstore" and assign certain privileges in xTuple (to view orders, customers, etc). Then, encrypt the communications between the webstore and xTuple, since they will be passing the login info to the store with each request.
  2. Performance. The connection between the two servers (web and xTuple) will have to be robust and reliable, or the performance of the web store will suffer.
  3. Load. If the web store generates a high amount of traffic, it could impact the performance of the xTuple server, and vice versa.

If any of these factors raise concerns, you may want to go to the XML option (the file that's generated can mirror the API Table structure for importing into xTuple). Otherwise, the API is available.

E-commerce solutions

Here are the 3 solutions that we currently offer:

Yahoo Store integration

We developed the Yahoo store integration in 2007 as a working example of how to use the then-new XML import feature in xTuple ERP. The package is designed to help a merchant who uses a Yahoo store front to export XML from the store, and import it into xTuple via the XML import utility. It includes a DTD for translating the Yahoo store XML output into a format suitable for importing into xTuple. This translation requires an xslt translator program. The package includes xalan, an xslt program for Windows users. Mac users will find that xsltproc is already installed as part of Mac OS X.

For details about this solution, see Yahoo Store Integration

Ubercart

xTuple has chosed Drupal as the platform for our own websites, and we are using Ubercart, a Drupal module, as the foundation for the xChange, our online store. We needed a way to move order and payment information from the xChange to our xTuple ERP database (which we use to run our own business, of course).  A little looking revealed that Ryan Szama, the original developer of the Ubercart module for Drupal, had also developed a module called uc_edi, which allows an Ubercart store to export order data in various formats. We snapped this up and got to work.

The uc_edi module was a good start for us, but it was intended primarily for generating comma-separated (csv) files. We needed it to export into xml format. So we modified it. We added the ability to specify a prefix and suffix inside the document, and included some more variables that were needed to make the file generated capable of being imported into xTuple. At this point we'd changed it so much  that we decided to rename it to uc_xtuple_edi.

The XML method was not our only choice for moving data from the xChange. We could have taken a different approach and written a module that used the xTuple API to pass data from the store, but we wanted to simulate what we thought would be a more typical user scenario. In most cases, we reasoned, the Drupal web site and the xTuple database will be hosted on different servers, and XML will be the most logical method for passing data.

For details about this solution, see Implementing the uc_xtuple_edi module.

osCommerce

The xTuple osCommerce Module (xOM) uses the xTuple API to pass data from an osCommerce store to xTuple ERP. We felt it was important to provide at least one example of an e-commerce integration based on the xTuple API. This method is potentially very powerful--you can address any view or table in the database directly, including running queries to populate fields in the web site. For example, in the xTuple configuration for xOM, you first specify the database you wish to connect to, and on the next screen you see data from that specific database, allowing you to select a default sales rep from those already in the database, for example.

The API method is powerful, but it is also much more complicated. In our own experience we found that it took much longer to get an API solution implemented that an XML solution. Instead of placing all the data and commands into a single XML text file that could be handled by the importer utility, we had to write an application that includes queries and functions, handles errors, etc. The XML method can be tackled by a power user—someone comfortable with XML and scripting—but the API method requires an experienced developer.

For details about this solution, see Implementing the xTuple osCommerce Module (xOM).