We need a script that we can run around twice-daily via cron. It will do the following:
a) Fetch all orders placed via our Magento store since the last check and save the order data to an external MySQL table.
The data that needs to be recorded is: order ID, date of purchase, date that order status last changed, payment method, order status, shipping service, buyer name/address/phone, ship-to name/address/phone, order currency, total tax, order total, number of items shipped from this order, number of items unshipped, IDs of items shipped (in form XXXX;XXXX;XXXX), IDs of items unshipped.
b) Update the new order status of any orders that have been changed via the Magento web interface (eg, changed from 'processing' to 'complete', or 'complete' to 'closed') since the last check
Essentially it gives us our own record of order data that should been in sync with the Magento data. We can then use for analysis purposes. All changes to order data will be made via Magento - there is no need to synchronise changes from our database to the Magneto system.
We are running Magento ver. 126.96.36.199 and cannot upgrade at the present time.