A local company needed to automatically update product stock inventory on their WooCommerce website when a product was sold in-store.
The authors of their point-of-sale system only work with Magento, so it wasn't possible with the existing system.
The Objective
Automatically update online product stock levels when sales are made in store.
The Process
After examining the POS software in-store, I found that the store information was saved to a MySql database. This database was regularly backed up and transferred to the site of the POS software developers.
As WordPress also uses a MySql database, it already has the code needed to interact with another.
I set up a second backup task which uploads a backup of the POS database to a folder under the root of the website, so that it could be accessed by the website, but not directly from the web.
A plugin was then written which regularly checked for new backup files in the upload folder. When it finds a new file, it unzips it to a temporary folder and then inserts it into a specific database (Specified in the plugin settings page).
Once a new backup is detected, it checks for any products that were modified since the last sync. If any products were modified, the stock level and product availability get updated with the information from the store database.
For this to work, the products on the website needed to be linked to a product in-store. One of the issues here was that the store database was flat, with each item unrelated to another. On the website, one product can have multiple variations (Colour, size etc.). So I created a new area in the product edit page which allows you to search for an in-store product and link it to either the main product or a product variation.
The search function uses the Twitter Typeahead/bloodhound library:
To keep the process efficient, only products updated since the last backup get updated on the website. A ‘sync all’ area was added to the plugin settings which runs through all associated products and updates the stock level to match the most recent in-store number.
The Result
People no longer order items online that are out of stock.
When items come back in stock and get updated in the store computer, they automatically get enabled with the correct stock level on the website as well.
There are plans to expand the plugin to add email notifications when no new backup has been detected for a period of time. Also, a page to list in-store products which aren’t associated with a website product, with the option to automatically create a new product with the store information.