This was for an e-commerce company which gets most of their revenue through drop-shipped products. Of course, the vendors have finite supplies of products, and it was not uncommon for them to receive an order for a product that the vendor did not have in stock leading to a cancelled sale and an unhappy customer.
The first step was retrieving stock counts from the vendors. This was sent via email weekly or daily from several vendors.
In order to normalize the data, I used mailparser. While I considered including the mail parsing in the project itself, mailparser was already in use for other things, so it just made sense to leverage mailparser.
The next hurdle was matching up products being sold with vendor products. The sku from the vendor was not always the sku on the website, and there were often multiple products selling the same vendor product, and there were also several instances where a single product was composed of multiple vendor products – sometimes from different vendors.
Fortunately, the company’s ERP (Netsuite) had the data needed to match the products for sale to the vendor products.
I was able to create a few CSV feeds from the ERP using saved searches – each of which was scheduled to email a different mailparser inbox.
The e-Commerce site being used is WooCommerce, so it was quite straightforward to automate the stock management through the WooCommerce Rest API.
Putting it All Together
I used Laravel to aggregate the data. I created a few separate Jobs to run at regular intervals to monitor and manage the data. While this project does have a frontend UI, it is mostly for the sake of feedback. The only input involved in the UI is entering data related to the Feed for each Vendor, which is essentially just the download URL from the respective mailparser inbox.
The Product Data Job
The first step was to gather the product data – this would populate or update the database tables related to the Vendors, Vendor Products, and Products, along with their relationships. Not all vendors send inventory updates, just the ones that make up the majority of sales.
The Product Details Job
Since it’s possible for products to have their stock status changed manually within WooCommerce, we want to use the WC REST API to check the status of each to make sure we only update those that need updating. This is limited to products related to vendors with a feed set. This is a recursive paginated process that takes several minutes to complete, and is scheduled to run daily at a time when the site traffic is lowest.
The Vendor Data Job
This loops through each Vendor that has a feed set. It loops through the list of vendor products and compares the status to the status stored in the DB. If the status is different, then it looks at all associated product entries – if the status is also different from that, that triggers the process to update the status of the associated product in WooCommerce. In the instance where a product consists of multiple vendor products, there are some additional checks in place, of course.
To make troubleshooting the components easier, I created an artisan command to accompany each job, and the artisan command is what the Job schedulers use to execute the schedules.
In addition to the Jobs that run regularly, I created one more:
Occasionally products get rearranged, and relationships change, and monitoring this with a great degree of detail on a regular basis seemed to be a bit too processor intensive. So, I instead opted to periodically drop everything (except for the vendors table), and then queue up all the other jobs in the appropriate sequence to immediately repopulate the tables.
While I could have very easily built the frontend with boring blade templates, I really wanted this to be a SPA application, and being such a fan of VueJs, I looked around for VueJs dashboard templates, and stumbled upon Creative Tim’s Vue Light Bootstrap Dashboard.
I used the line graph on the main dashboard to illustrate the number of products updated for each vendor daily.
The rest shows all products by vendor and an indication of the stock status for the vendor product as well as for the product within WooCommerce, and includes a link to the product/item within WooCommerce and within NetSuite.