User Tools

Site Tools


"Predictive" Inventory Report

One of the 'brick walls' that any business eventually hits is how to manage inventory. If you're a one person shop and you have 10 items to keep track of, you can probably do it all in your head. But once you get to 25 or so items it can get really messy. That's why a few years ago we wrote up our own 'predictive' inventory system. Now to make it absolutely clear, this php code cannot actually see into the future. However, it can look into the past and make a good educated guess about how long your inventory will last assuming you have pretty steady purchasing history.

Basically, this code will look at all your product categories and items and sum up how many orders were placed for these items. Then it puts them in a table with the current inventory/stock. The table entries have a background of light red if your current inventory will run out before then. For example lets look at this screenshot:

If the future purchasing is like the past, then we'll run out of 9V regulated adapters in about 4-6 weeks. If you know the approximate lead time for stuff you buy then you can just keep ordering just when the 'red bars' start showing up too much!

How to install

We suggest writing a few extra functions in your zencart admin to place into the /admin/includes/functions/extra_functions/ folder for generating inventory reports.

Here's one of our functions, which simply makes a giant array of how many of each product we've sold in the past week, 2 weeks, 4 weeks, 6 weeks, etc. Save this into a file called admin/includes/functions/extra_functions/stats_functions.php

function getSalesStats()
  global $db;
  $all = array();
  for ($i = 0; $i < 2000; $i++)
    {      $all[$i] = array(0,0,0,0,0,0,0);    }
  $selectql = $db->Execute("SELECT op.products_id, op.products_quantity, o.date_purchased, DATEDIFF(CURDATE(), o.date_purchased) as diff from orders_products op LEFT JOIN orders o on op.orders_id = o.orders_id WHERE DATEDIFF(CURDATE(), o.date_purchased) < 90");
      $pid = $selectql->fields['products_id'];
      if($selectql->fields['diff'] <= 7)        {         $all[$pid][0] += $selectql->fields['products_quantity'];      }
      if($selectql->fields['diff'] <= 14)       {         $all[$pid][1] += $selectql->fields['products_quantity'];      }
      if($selectql->fields['diff'] <= 21)       {         $all[$pid][2] += $selectql->fields['products_quantity'];      }
      if($selectql->fields['diff'] <= 28)       {         $all[$pid][3] += $selectql->fields['products_quantity'];      }
      if($selectql->fields['diff'] <= 42)       {         $all[$pid][4] += $selectql->fields['products_quantity'];      }
      if($selectql->fields['diff'] <= 56)       {         $all[$pid][5] += $selectql->fields['products_quantity'];      }
      if($selectql->fields['diff'] <= 90)       {         $all[$pid][6] += $selectql->fields['products_quantity'];      }
  return $all;

And you will be able to call this function from any new report-generating pages that you create. For example, here is a report PHP that should just work if you have the report function available

/home/ladyada/public_html/wiki/data/pages/tutorials/zencartmods/inventory_report_5.html.txt · Last modified: 2016/01/28 18:05 (external edit)