I am working on a side project for a local small business (pro bono). Currently they sell a line of products. The orders and shipping are managed by the popular webapp ShipStation. Since they have been growing, there have been issues managing inventory (the current system is excel and manual counting).

 

My goal is to create a simple inventory management system that will look at shipments, and debit the appropriate skus from the inventory.

 

What I am trying to figure out is how to effectively accomplish this. I am a SQL Server BI developer by trade so this is how I see it working (with my limited webapp knowledge):

1. Some automated process grabs incremental shipment data from ShipStation via their API.
2. Load this data to a SQL DB (mySQL probably).
3. Jobs process this data and debit from the inventory qty.
4. Also have ability for users to add inventory of course.

Unfortunately, I don't know if this is the best process and if so, how to even get started. I have the DB side of it down but getting data via an API and loading it, and then building a simple webapp to view the inventory (php or asp?) is where I am stumped. Any guidance is appreciated.

 

P.S. I dabbled a little with the API feed using apigee console and was able to pull some basic data like all orders or all customers, etc. I'm still reading up on ShipStation's API documentation.

How you have explained it is how i would tackle it. How does there API work can they send you information as soon as an order is confirmed or do you have to poll it several times a day?

 

For the web app you can use any server side language but PHP is a bite easier to learn as there are a lot of online tutorials and quite a few php developers on this forum that could help.

How you have explained it is how i would tackle it. How does there API work can they send you information as soon as an order is confirmed or do you have to poll it several times a day?

 

For the web app you can use any server side language but PHP is a bite easier to learn as there are a lot of online tutorials and quite a few php developers on this forum that could help.

 

OK great. I just wanted to make sure I wasn't doing this some overly difficult way.

 

I will have to poll the API each time I want to get the most recent data. I am thinking every 5 or 10 minutes, which is within their frequency allowance.

 

Now I need to figure out how to take the API's JSON output and insert it into a DB table. Are you familiar with this at all?

Im not sure how there API works but im guessing you would have to do something like this.

 

<?php
	
	/* 
		Automaticly run this script via a cron or scheduled task 
		 - https://en.wikipedia.org/wiki/Cron
		 - https://en.wikipedia.org/wiki/Windows_Task_Scheduler
	*/

	/* Connect to the database - look into PDO - http://php.net/manual/en/book.pdo.php */
	$database = new PDO("PDO String");

	/* Send a request to the API via cURL  -http://php.net/manual/en/book.curl.php */
	$ch = curl_init("API URL");
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($ch, CURLOPT_HEADER, 0);
	$APIresponse = json_decode(curl_exec($ch));

	/* Loop throgh the api response - http://php.net/manual/en/control-structures.foreach.php */
	foreach ($APIresponse["orders"] as $order) {

		foreach ($order["items"] as $item) {

			/* Update your database */
			$update = $database->prepare("UPDATE `stock` SET `qty`=`qty`-:qty WHERE `id` = :id");
			$update->bindParam(":qty", $item["qty"]);
			$update->bindParam(":id", $item["id"]);
			$update->execute();

		}

	}

?>

Im only guessing at the response from the API so the above script will not work if you can get me a samle response from the API i can better talk you through it.

Edited July 16, 201511 yr by D4Y0

Great, thanks for the suggestions. I am very new to web programming. As mentioned, I am a T-SQL programmer presently, learned (and have since forgotten) Java back in college days, and have run through the PHP course on codecademy a year or two ago but never used it. My plan is to run through that tutorial again, and also go through some courses I purchased a while back on udacity. Thanks for the suggestions thus far!