Purpose
Integrate the OsCommerce on-line shopping cart with FrontAccounting.
Background
Oscommerce (OSC) http://www.oscommerce.com is a self-contained on-line shopping cart. It has its own database, including inventory with prices and quantity. FrontAccounting (FA) also has its own database including inventory with prices and quantity.
Without integration, sales orders and purchase orders have to be manually "double-entered" into both the cart and the accounting system. This is a lot of extra work, so often sales are only occasionally entered into the accounting system and the accounting purchasing and inventory system might be avoided altogether.
The goal of integration is to synchronize OSC and FA so that OSC sales show up in FA and to keep inventory and prices identical. There are several approaches that one could take to make this happen, such as instrumenting OSC to call the FA API. However, integration of this nature is difficult if synchronization points are spread throughout the code and then maintain in the face of code updates to either system.
The approach taken by this integration is to periodically synchronize the MYSQL databases. This can be an easier approach because a low level database provides a single synchronization point, and no code changes are required to the core of either software product.
In this integration, FA is the inventory and price master, and OSC is the item category, name and description master. This means that products are created and named in OSC and then imported into FA. Sales orders can be created in either OSC or FA, purchase orders are created in FA, sales flow from OSC to FA, and inventory quantity and price changes flow from FA back to OSC.
While this does require the user to manually run the imports (although full automation could be accomplished with a cron job), it saves tremendously because individual orders do not have to be re-entered.
Configuration
This first step is to associate the OSC MYSQL database to FA. However, before attempting this you should test to make sure you can access the OSC database from the command line, because of your MYSQL security configuration. This is particularly a concern if the OSC database resides on a different server. If you run into difficulty, there is a wealth of material available online on how to access MYSQL.
Mysql Host is the machine name or ip address where the database resides.
User is the MYSQL user allowed to access the database.
Password is the password for the MYSQL user allowed to access the database.
DB Name is normally "oscommerce" on most installations.
Those are the only fields necessary to create a connection to the OSC database. The following fields are used during the import process.
"Osc Id" defaults to products_model, although products_id is also a good choice. This will be used for the FA Item ID.
"Osc_Item Prefix" is useful to ensure that the FA Item ID will be unique and not conflict with any existing items in FA.
Order Import
"Order Import" operates on a range of orders by specifying the "Starting Order Date" and "Last Order Date". "Starting Order Date" defaults to the next order date that has not been already imported. "Last Order Date" automatically defaults to the last date in the OSC database.
"Osc Status Id" defaults to blank, which allows import of all OSC orders regardless of order status. This is a good setting if you are using FA to manage your order process and all OSC orders are in the Pending "1" state.
But if you are using OSC to manage your order process, you may want to set this to "3", which is the OSC "Delivered" state, so you only import OSC orders that have been delivered. Then you can allow OSC to cancel or modify orders in the "Pending" or "Processing" states without affecting FA sales or inventory. Note that if you allow OSC to modify or cancel orders that have already been imported into FA, you will also have to modify or cancel the order in FA to keep inventory intact.
"Direct Invoice" will create a fully paid transaction in FA, including Sales Order, Delivery, Invoice and Customer Payment. (To create a Customer Payment successfully, you need to first create a FA Setup->Point of Sale named for each OSC payment method used, such as "Credit Card", "Paypal" or "Cash".)
The import searches for an FA customer with a branch address equal to the OSC delivery address. For this to work, you will need to run "Customer Import" before attempting to import any orders. After "Order Import", you will need to manage the order process in FA and deliver and invoice all the orders, just like for any FA Sales Order.
Alternatively you can create a FA customer with just the name of the state or country in the branch address field. All orders with that state, or if not found, that country, will use that FA customer. This is useful if you are using OSC to manage your order process and want to avoid running "Customer Import" to keep the zillions of OSC customers out of the FA customer database. This works as long as all customers within a given state or country are taxed the same.
"Payment" must be set to "Cash Only" if you want a customer payment to be created.
"Errors" is normally set to Skip. Normally errors should not occur, but it is wise to Skip them. If an error should occur, you could rerun "Order Import" with "Direct Invoice" set to "No" and "Errors" set to Ignore to import the problematic orders into FA as Sales Orders where you can modify them before further processing.
"Trial Run" allows you to verify the integrity of the import before any modification of the FA database.
When orders are imported into FA, a comment "Imported into FA" is added to the OSC order status history. The code prevents the order from being imported again into FA unless the "Allow Duplicate Import" box is checked.