osCommerce and Legacy POS – The Integration Challenge
5/19/2010 8:13 AM
osCommerce is an open source e-Commerce framework that is widely used (11,000 online shops) and well supported (227,000 store owners and developers signed up on their site), with a large number of plug-ins that allow you to do extend it in many ways. It has its own MySQL database that is used to tracks orders, order lines, customers and all other relevant information. Since the structure is fixed and central to the way osCommerce works, integrating it with a third party POS system is an interesting challenge. Here at FusionWare, we have created a plug-in that allows us to easily extend osCommerce to integrate any combination of products, prices, inventory levels, customers and order processing with a 3rd party POS system.
We at FusionWare have had extensive experience connecting to IBM i (also known as AS/400) and MultiValue (PICK, Universe, Unidata, etc.) systems among others, so we were quite up to the challenge of proceeding to connect to the osCommerce environment, and make it generic (able to connect to almost any flavor of POS system).
As a follow-up to Robert Houben’s blogs on our project to provide an open source Web store for IBM i using osCommerce, here are some of the goals and challenges we faced with this specific project and how we dealt with them.
We wanted to achieve appropriate synchronization between the osCommerce MySQL database and that of the legacy IBM i POS system. For example:
- New products added to the POS inventory should show up on the Web at least by the start of the next business day.
- Inventory levels on the Web should be the same as those in the POS system. In other words, if a customer purchases an item on the Web, there should be one less of those items showing up as available for purchase by customers walking in to the store.
- Customer accounts created on the Web should not be duplicates of any created in the POS system.
How would we achieve this integration?
The basic architecture would look something like this:
To pass working data back and forth between osCommerce and the POS system, we decided to use a web services layer. For example, when a customer creates an account, osCommerce needs to check if the customer already exists in the POS system, and, if not, to create the account there.
For loading bulk data from the POS system into the osCommerce MySQL database, for example, nightly updates of products, we decided to create stored procedures in the osCommerce MySQL database and, via Java-based ETL component, execute these as a nightly or on-demand process on the Linux Web Store system.
A very easy and efficient way to implement the web service layer is to use the FusionWare Integration Server. The FusionWare Integration Server is a complete Web Services-based business process framework which combines a powerful workflow engine with an Enterprise Service Bus.
We provided the customer with a FusionWare Integration Server, which was installed on Linux machine that hosts osCommerce, hooked up to the IBM i machine. This enabled us to use our FusionWare Designer to build the required web services.
The FusionWare Designer is an intuitive forms-based user interface, built-in code generators, wizards, query builders and powerful adapters that simplify and accelerate the development and deployment of service-oriented business applications.
Once we had the RPG programs that could be called by the FusionWare Integration Server, the process of creating the web services that exposed those programs as web services was a matter of minutes.
Some of the challenges we faced
- Finding a good place to hook in web services calls that would avoid too much hacking into the osCommerce PHP code.
- Making the solution as reusable as possible – We wanted a generic solution that would be able to connect to any flavor of POS system
How we met these challenges
The first step was to decide on which flavor of SOAP to use in PHP. At the time, PHP SOAP seemed better supported and easier to use than other flavors like NuSOAP, etc., so we went with that one.
Next, we had to figure out a way of hooking in the web services calls so that there wouldn’t be major changes required to the stock osCommerce objects. We found just the place for that…
There is a central function in osCommerce through which all database updates are channeled, and this is where we inserted a small piece of code that would redirect these updates to our FusionWare osCommerce Plug-In.
The FusionWare osCommerce Plug-In is designed as a separate, self-contained folder of PHP objects, with some associated tables and admin records added to the MySQL database, that have functionality to first redirect database updates via the web services layer and then, depending on the success of the web services calls, will allow processing to continue as normal. Some of the components of the FusionWare Plug-In have to be customized to fit in with the specific customer’s business processes, but these components have been designed to make the split between generic and customer-specific components very clear, making the solution very reusable.
Most customers, especially those with very mature legacy POS systems, will inevitably have very customized business process, and these would have to be analyzed and dealt with. Fortunately, the way the osCommerce FusionWare Plug-In has been designed, this should be a fairly painless process.
And there’s more…
Of course, there was more to the project than just providing the integration solution. The customer also wanted:
- Inclusion of some legacy items from their old web-site.
- Some form of user-controlled content management.
Fortunately, although the old web site was a bit of a mix-and-match of technologies, the parts they wanted included in their new integrated Web store were all PHP. So that made including them in osCommerce a great deal easier. We merely added some custom folders and some custom PHP pages, along with some tables required in the MySQL database to hold data associated with these legacy objects, and, with a bit of modification, these items migrate across quite nicely.
For the content management system, we did some research and found an excellent osCommerce community ad-on called Define Content. Define Content is a basic language aware Content Management System (CMS) designed for osCommerce. Using Define Content, we added a number of custom content-managed areas such as an ad rotator panel for the home page, and user-managed info boxes and ad panels in the left and right-hand columns of the web pages. The customer has in fact found this extremely handy and easy to use, and uses it extensively to post new ads, featured events, etc.
Portability of the Solution
Both osCommerce, the FusionWare Integration Server, and the overnight batch processes can run on Windows. osCommerce supports the following stacks:
LAMP – Linux, Apache, MySQL and PHP
WAMP – Windows, Apache, MySQL and PHP
WIMP – Windows, IIS, MySQL and PHP
Any Linux or Windows platform that will support Apache or IIS (on Windows only) and has a Java 1.4 or higher run time available is a candidate platform for this solution.
Copyright ©2010 FusionWare Integration Corp.