This question may be a bit beyond the scope of this site, but I figured I'd give it a shot. :) I am developing an e-commerce website for a business using mySQL and ColdFusion. The business is running SIMS ERP software for internal management, and already has VPN connectivity. The ERP software(SIMS) is running on a UNIX server that is local to the warehouse.
For all internal purposes, I want to keep the web data SEPARATE from the data in the ERP software (they don't keep the data clean enough to link it to the site...lots of obsolete, duplicate data and inventory issues to work out yet). However, I DO want the web application to access the SIMS application and automatically perform some tasks, ultimately resulting in web orders being automatically input directlly to SIMS, and resulting purchase orders printed and waiting for the warehouse manager to pull. Very basically, we are skipping the step of manually keying in the order AFTER the customer has submitted it online.
Right now, web orders are simply being emailed to our warehouse manager for manual entry into the ERP software.
Again, I'm not sure who to talk to about this, so if this question cannot be answered here, perhaps one of you can direct me to a good resource for help?
I have a co-located webserver which is managed and located in a different city. It collects orders & processes payment authorizations (storing only the auth # not the cc num). Every night a CF scheduled task runs to create an xml order file. ie:
Locally, I've set up a windows scheduled task which runs an ant script which opens a ssh session to the webserver, collects the xml file via scp and delete the remote file. The file is moved to a directory where order staff have access. In the morning the order staff use our db front end which is built on msaccess to select the file and pass it to sql server which runs a stored procedure which uses sp_xml_preparedocument & openxml to insert the the various xml nodes into the 5 tables associated with orders in our system.
Once imported, order staff have a form to verify customerids & set shipdates.
Each day at end of day, staff manually create an orderstatus.xml file and upload it to the cf server via an admin interface. This updates order status for all active orders.
The system has been in use for about 6 years with various enhancements along the way but when we first implemented it we saved 2 person positions by automatically inputting the 80-100 orders each day.
Now the process takes less than 30 minutes including assigning ship days.
That being said, getting all the pieces to work together mostly seamlessly has been quite a challenge and required learning a lot of new things: ant, ssh, xml w/ mssql and has taken a lot of time but it works so well now that most here don't even know how much better things are today.