Recursion - Inventory System

[blyxx86]

Hello Again Everyone,

I've got a few database tables set up as a test scenario to be able to find total inventory by company, by location, shelf, bin, etc..

Very simply the data structure is like this:
inv_location
inv_location_id
location_name
parent_inv_location_id

Then inventory counts go here
inv_stocktage
inv_stocktake_id
part_id
quantity
inv_location_id (FK to inv_location)
stocktake_date



Ok, so database stuff aside, I'm trying to figure out a decent way to recursively go through each inventory location and see what is listed under it in the stocktake table, group them appropriately and store in an inventory object (seems the most beneficial way to recursively loop through and store the results).

I don't need code written, just some help getting the flow setup.

So far, I have something that goes like this (just pseudo code)

function find_inv($loc_id)
{
    // grab an array of all other inv_locations that have current loc_id as a parent_id
    $array = select items from inv_location table
 
    if $array has records
    {
        foreach(value fround in above-mentioned array as $id)
        {
            // begin recursive loop here
            find_inv($id);
        }
    }
 
    $values = select items from inv_stocktake table and store in some class variable
 
    return this;
 
}
 

Again that is just very rough pseudo code, but I'm just trying to get a feel if my logic is correct.

I get a bit lost on how I will be storing the found $values but I am sure I could work something out.

Any ideas to get me going in the right direction?

Thanks!!

[bilibytes]

i don't understand really well what you want to achieve.

maybe this helps you : Modified Preorder Tree Traversal

good luck

[Dormilich]

don’t know whether that is of relevance: views

[blyxx86]

@Dormilich
I actually use views somewhat regularly in our database here, I would have to develop a recursive stored procedure of sorts to do this, since there can be "N" levels of parents for the inventory location.

[blyxx86]

Let me provide a sample of some desired output (or rough idea):

Loc_id  Part_id  Qty
+1      ABC      5
--3     ABC      2
-+4     ABC      3
  --5   ABC      1
  --6   ABC      2
 

Where under Loc_id Qty equals 5, as you add up all the locations beneath it. Qty 2 in Loc 3, and then recursively lookup Loc_id 5 and 6 to come with a total Qty of 3 in Location 4 (Which has children 5 and 6).

I hope that makes sense.

Basically I need to make a recursive function that will tell me what is in a Warehouse and then be able to drill it down further.

IE.,

+Warehouse
    +Area
      +Shelf
         +Column
           +Bin
              +n...
 

I would use the hierarchical method of left & right, except the massive updates to the table would become a bottleneck as inventory movements may happen thousands of times per day and the inventory needs to only be called when certain functions/reports are ran.

[blyxx86]

So I created a class for it that mostly works for what I need it to.

I hope the comments inside help make sense of what is going on.

class Inventory
{
    var $my_array = array();
    function Inventory($parent_id='', $level=0)
    {
        //constructor calls inner function
        $this->find_inv($parent_id, $level);
    }
 
    /**
     * Function to print out inventory found based on a location_id given
     * @param parent_id
     * @param level
     */
    function find_inv($parent_id='', $level=0)
    {
 
        // choose inventory location to start searching
        // blank value results in showing "IS NULL" for parent_id
        if($parent_id != '')
        {
            $result = mysql_query('SELECT * FROM inv_location '.
                                'WHERE parent_inv_location_id="'.$parent_id.'";');
        } else {
            $result = mysql_query('SELECT * FROM inv_location ' .
                                'WHERE parent_inv_location_id IS NULL;');
        }
        while($row = mysql_fetch_object($result))
        {
            // find parts found at current inventory location
            $inner = mysql_query('SELECT * FROM inv_stocktake ' .
                                'WHERE inv_location_id ='. $row->inv_location_id.';');
 
            // show output of location found, indenting children
            echo str_repeat('  ',$level).$row->name."\n";
 
            while($row2 = mysql_fetch_object($inner))
            {
                // store total qty found based on parent_id given
                // my_array[part_id]['qty'] = total quantity
                if(isset($this->my_array[$row2->part_id]['qty']))
                {
                    $this->my_array[$row2->part_id]['qty'] += $row2->quantity;
                } else {
                    $this->my_array[$row2->part_id]['qty'] = $row2->quantity;
                }
 
                // store quantity found in each location
                // this only shows individual pieces found in location, not the recursive total in location
                // my_array[part_id]['locations'][inv_location_id]['qty'] = qty at location
                if(isset($this->my_array[$row2->part_id]['locations'][$row->inv_location_id]['qty']))
                {
                    $this->my_array[$row2->part_id]['locations'][$row->inv_location_id]['qty'] += $row2->quantity;
                } else {
                    $this->my_array[$row2->part_id]['locations'][$row->inv_location_id]['qty'] = $row2->quantity;
                }
 
                // show standard output of inventory found at location
                echo str_repeat('   ',$level).$row2->part_id." ".$row2->quantity."\n";
            }
 
            // begin recursive lookup
            $this->find_inv($row->inv_location_id, $level+1);
        }
    }
 
    function get_array()
    {
        return $this->my_array;
    }
};
 

I then get to call the function from within another piece of code.

$a = new Inventory($parent_id, $level);
print_r($a->get_array());
 

I insert about 20,000 rows into my (inv_stocktake) table and it is pulling (and printing) the results in 0.24 seconds, compared to my standard page loads of .06seconds. Thank goodness for MySQL caching!!! I was getting about 100 queries running when I ran the sample data, which is somewhat realistic since most of the locations won't have more than a few individual children.

I would obviously like to improve upon what is here, but I think that this is a good start for what I am doing.