Stock managemet subtracting from oldest first

paulvz · November 3, 2020

Good morning. I need to subtract stock levels from oldest stock first then to the next date.

It is allowed to move into negative values.

example

01-10 stock in 10 stock out 5 - stock count 5

02-10 stock in 10 stock out 7 stock count ... here we need to subtract the 7 from previous days 5 til it reches 0 then stock in subtraction

01-10 stock =0

02-10 stock = 8 --- as 7 -5 gives me -2 in incoming stock is 10 leaving me with stock count of 8.

03-10 stock in 8 stock out 21

02-10 stck level must be 0

03-10 stock lever now is 8-14 = -6 and so on

below is my code.

    $lq_in = new ListQuery('stock_audit5f5795042f369');
    $lq_in->addSimpleFilter('name', '%PEPPER yellow%', 'LIKE' );
    //$lq_in->addSimpleFilter('product_id', $product_id, '=');
    $lq_in->setOrderBy('date_entered');
    $res_in = $lq_in->fetchAll();
$StockArray = [];
foreach($res_in as $out_rec) {
    /*$upd_out = array();
    $upd_out['stock_out_done'] = 0;

    $out_rec_u = ListQuery::quick_fetch('stock_audit5f5795042f369', $out_rec->getField('id'));
    $aud_upd_out = RowUpdate::for_result($out_rec_u);
    $aud_upd_out->set($upd_out);
    $aud_upd_out->save();
    continue;
    */
    $stock_out = $out_rec->getField('stock_out');
    $stock_out_done = $out_rec->getField('stock_out_done');
    $date_entered = $out_rec->getField('date_entered');
    $product_id = $out_rec->getField('product_id');

    
    //echo '<pre>';
   // print_r($out_rec->row);
    $StockItems[] = $out_rec->row;

    //$stock_done = 0;
    /*
    foreach($res_in as $in_rec) {
        $upd = array();
        if($stock_out_new > $in_rec->getField('stock_level')) {
            $upd['stock_level'] = 0;
            $stock_out_new = $stock_out_new-$in_rec->getField('stock_level');
            $stock_done = $in_rec->getField('stock_level');
        } elseif ($stock_out_new == $in_rec->getField('stock_level')) {
            $upd['stock_level'] = 0;
            $stock_out_new = 0;
            $stock_done = $stock_out;
        } elseif($stock_out_new < $in_rec->getField('stock_level')) {
            $upd['stock_level'] = $in_rec->getField('stock_level')-$stock_out_new;
            $stock_out_new = 0;
            $stock_done = $stock_out;
        } else {
            continue;
        }

        $in_rec_u = ListQuery::quick_fetch('stock_audit5f5795042f369', $in_rec->getField('id'));
        $aud_upd = RowUpdate::for_result($in_rec_u);
        $aud_upd->set($upd);
        $aud_upd->save();

        if($stock_out_new == $stock_done)
            break;
    }
    
    $upd_out = array();
    $upd_out['stock_out_done'] = $stock_done;

    $out_rec_u = ListQuery::quick_fetch('stock_audit5f5795042f369', $out_rec->getField('id'));
    $aud_upd_out = RowUpdate::for_result($out_rec_u);
    $aud_upd_out->set($upd_out);
    $aud_upd_out->save();
    */
}
function GetFirstItemWithStockKey($StockItemsarrayk = null){
    if($StockItemsarrayk != null){
        foreach($StockItemsarrayk as $key => $value){
            if(((int) $StockItemsarrayk[$key]['stock_level']) > 0){
                return $key;
            }
        }
    }
}

function SetFirstItemWithStock($StockItemsarray = null){
    if($StockItemsarray != null){
        foreach($StockItemsarray as $key => $value){
            if(((int) $StockItemsarray[$key]['stock_level']) > 0){
                return $StockItemsarray[$key];
            }
        }
    }
}
$remainder = 0;
$pkey = "";
$StockLevelKey = 0;
$StockIn = [];
$StockOut = [];
$InStock = [];

$NewStockItems = $StockItems;
$ArrayKeys = [];
foreach($StockItems as $key => $value){
  $StockIn[$key] = (int) $StockItems[$key]['stock_in'];
  $StockOut[$key] = (int) $StockItems[$key]['stock_out'];
  $InStock[$key] = (int) $StockItems[$key]['stock_level'];
  $ArrayKeys[] = (int)$key;
  
}
//var_dump($InStock);
foreach($NewStockItems as $key => $value){
    if($key < 1){
        if($StockIn[$key] > 0 && $StockOut[$key] == 0 && $InStock[$key] == 0){
            $StockItems[$key]['stock_level'] = ($InStock[$key] + $StockIn[$key]);     
        }
        if($StockIn[$key] == 0 && $StockOut[$key] > 0 && $InStock[$key] == 0){
            $StockItems[$key]['stock_level'] = ($InStock[$key] - $StockOut[$key]);
         
        }
        if($StockIn[$key] > 0 && $StockOut[$key] > 0 && $InStock[$key] == 0){
            $StockItems[$key]['stock_level'] = ($InStock[$key] - $StockOut[$key] + $StockIn[$key]);
            $StockItems[$key]['stock_out'] = 0;
        }
    }
    if($key > 0){
        $previousWithStockItem = SetFirstItemWithStock($StockItems);
        $previousItemWithStockKey = GetFirstItemWithStockKey($StockItems);
       // echo "<pre>";
      //  print_r($previousWithStockItem);
       // var_dump($StockIn[$key]); echo "&nbsp;&nbsp;&nbsp;&nbsp;--IN";
      //  var_dump($InStock[$key]); echo "&nbsp;&nbsp;&nbsp;&nbsp;--- current";
       // var_dump($StockOut[$key]); echo "&nbsp;&nbsp;&nbsp;&nbsp;--- OUT";
        while($StockOut[$key] > 0){
            if($StockOut[$key] > 0 && $previousWithStockItem['stock_level'] > 0){
                $Counter = 0;
                $maxIteration = 0;
                for($Counter = $previousWithStockItem['stock_level']; $Counter >= 0; $Counter--){
                    $StockItems[$previousItemWithStockKey]['stock_level'] = $Counter;
                    if($Counter == 0){
                         $StockOut[$key] = $StockOut[$key] - $maxIteration;
                    }
                    $maxIteration++;
                }
            }
            if((((int) $StockItems[$key]['stock_level'] < 0) || ((int) $StockItems[$key]['stock_level'] === 0))&& ($StockIn[$key] > 0)){
                $valueTotal = $StockItems[$key]['stock_level'] + $StockIn[$key];
                $StockItems[$key]['stock_level'] = $valueTotal;
            }
            echo "<hr/>";
            echo (int) $StockOut[$key];
            echo "<br/>";
             echo (int) $StockItems[$key]['stock_level'];
            echo "<br/>";
             echo "<hr/>";
            if(((int) $StockOut[$key] > 0) && ((int) $StockItems[$key]['stock_level'] > 0) && ((int) $StockItems[$key]['stock_level'] > $StockOut[$key])){
              
                $newStockLevel = $StockItems[$key]['stock_level'] - $StockOut[$key];
                echo $newStockLevel;
                $StockItems[$key]['stock_level'] = $newStockLevel;
                $StockItems[$key]['stock_out'] = 0;
                $StockOut[$key] = 0;
            }
            if((((int) $StockItems[$key]['stock_level'] < 0) || ((int) $StockItems[$key]['stock_level'] === 0))&& ($StockIn[$key] > 0)){
                $valueTotal = $StockItems[$key]['stock_level'] + $StockIn[$key];
                echo $valueTotal;
                $StockItems[$key]['stock_level'] = $valueTotal;
            }
        }
    }
}
echo "<table><tr><td><pre>";
print_r($NewStockItems);
echo "</pre></td><td><pre>";
print_r($StockItems);
echo "</pre></td></table>";
/*
        if($StockIn[$key] > 0 && $StockOut[$key] >0 && $InStock[$key] == 0){
            $StockItems[$key]['stock_level'] = ($InStock[$key] + ($StockIn[$key] + $StockOut[$key]));
            
            $StockOut[$key] = 0;
            $StockItems[$key]['stock_out'] = 0;
        }
        if($StockIn[$key] != 0 && $StockOut[$key] != 0 && $InStock[$key] != 0){
            $StockItems[$key]['stock_level'] = ($InStock[$key] - ($StockOut[$key] + $StockIn[$key]));
            
            $StockOut[$key] = 0;
            $StockItems[$key]['stock_out'] = 0;
        }*/
/**
 @Rule 1 
 # Stockin has value and stock_out = 0 and stock_level = 0 and stock_out_done = null, actualstock to show actual stock level;
 # 
 
 */

/*if($StockCalculation[$Skey]['stock_out'] >= $StockItems[$key]['stock_level']){
                           $StockItems[$key]['stock_out'] = ($StockItems[$key] - 1);
                           $StockItems[$key]['stock_level'] = ($StockItems[$key]['stock_level'] - 1);
                           // If StockOut == 0 next StockOutItem
                           if($StockCalculation[$Skey]['stock_out'] == 0){
                               $remainder = 0;
                                continue;
                           }elseif($StockItems[$key]['stock_level'] == 0){
                               //CurrentInStock == 0 then continue to next CurrentItem
                                $remainder = $StockItems[$key]['stock_out'];
                                continue(2);
                           }
                       }
 $CurrentStockIn = $StockItems[$key]['stock_in'];
   $CurrentStockOut = $StockItems[$key]['stock_out'];
   $CurrentInStock = $StockItems[$key]['stock_level'];
   if($key == 0){
       if($CurrentStockIn > 0 && $CurrentStockOut === 0 && $CurrentInStock == 0){
           $CurrentInStock = $CurrentStockIn;
           //Query update stock level set = stock_in 
           //"UPDATE STOCK SET stock_level = {$CurrentStockIn} where id = {$StockItems[$key]['id']}";
           $StockItems[$key]['stock_level'] = $CurrentStockIn;
       }
       if($CurrentStockIn != 0 && $CurrentStockOut != 0 && $CurrentInStock == 0){
           //Query Update Stocklevel if stock_out > 0 and stock_level = 0
           //"UPDATE STOCK SET stock_level = "+($CurrentStockIn - $CurrentStockOut) + "where id = {$StockItems[$key]['id']}";
           if($CurrentStockIn > $CurrentStockOut && $CurrentStockOut == 0){
               $StockItems[$key]['stock_out'] = 0;
               $StockItems[$key]['stock_level'] = $CurrentInStock = $CurrentStockIn - $CurrentStockOut;
           }
           if($CurrentStockOut > $CurrentStockIn){
               $StockItems[$key]['stock_level'] = $CurrentInStock = $CurrentInStock - $CurrentStockOut;
           }
       }
       if($CurrentInStock != 0 && $CurrentStockOut > 0){
            //If Current in stock below 0 and stock out > 0 then negative more
           //"UPDATE STOCK SET stock_level = "+($CurrentInStock - $CurrentStockOut) + "where id = {$StockItems[$key]['id']}";
           $StockItems[$key]['stock_level'] = $CurrentInStock = ($CurrentInStock - $CurrentStockOut);
           $StockItems[$key]['stock_out'] = 0;
       }
        if($CurrentInStock != 0 && $CurrentStockIn > 0){
            
            //If Current in stock below 0 and stock out > 0 then negative more
           //"UPDATE STOCK SET stock_level = "+($CurrentInStock - $CurrentStockOut) + "where id = {$StockItems[$key]['id']}";
           $StockItems[$key]['stock_level'] = $CurrentInStock = ($CurrentInStock + $CurrentStockIn);
       }
       // Run row update for first item
   }else{
       foreach($StockCalculation as $Skey => $Sval){
           $NextStockOut = $Sval['stock_out'];
           $NextStockIn = $Sval['stock_in'];
           $NextStockLevel = $Sval['stock_level'];
           
           if($Skey > 0 && $key > 0){
              // print_r($NextStockOut);
                   for($i = $NextStockOut; $i >= -1; $i--){
                       if($NextStockOut > 0){
                           if($NextStockOut > 0){
                               /* if($StockItems[$StockLevelKey]['stock_level'] != 0){
                                    $StockItems[($StockLevelKey)]['stock_level'] = ($StockItems[($StockLevelKey)]['stock_level'] - 1);
                                    //$StockItems[($Skey-1)]['stock_out'] = ($StockItems[($Skey-1)]['stock_out'] -1);
                                }
                                if($StockItems[($Skey-1)]['stock_level'] != 0){
                                    $StockItems[($Skey-1)]['stock_level'] = ($StockItems[($Skey-1)]['stock_level'] - 1);
                                }
                           }
                       }
                       $NextStockOut = ($NextStockOut -1);
                       if($NextStockOut != 0){
                           $StockItems[$Skey]['stock_out'] = 0;
                           
                           break;
                       }
                       
                   }
               
           }
           unset($StockCalculation[$Skey]);
       }
   }/*
*/

requinix · November 3, 2020

That was not easy to understand.

01-10
- Incoming: 10
- Outgoing: 5; 5 taken from today's inventory (10-5=5)
- Ending inventory: 01-10 (5)

02-10
- Incoming: 10
- Outgoing: 7; 5 taken from 01-10's inventory (5-5=0), 2 taken from today's inventory (10-2=8)
- Ending inventory: 02-10 (8)

03-10
- Incoming: 8
- Outgoing: 21; 8 taken from 02-10's inventory (8-8=0), 8 taken from today's inventory (8-8=0), remainder 5
- Ending inventory: deficit of 5

04-10
- Incoming: 15
- Outgoing: 3; deficit 5 taken from today's inventory (15-5=10), 3 taken from today's inventory (10-3=7)
- Ending inventory: 04-10 (7)

05-10
- Incoming: 10
- Outgoing: 6; 6 taken from 04-10's inventory (7-6=1)
- Ending inventory: 04-10 (1), 05-10 (10)

06-10
- Incoming: 5
- Outgoing: 7; 1 taken from 04-10's inventory (1-1=0), 6 taken from 05-10's inventory (10-6=4)
- Ending inventory: 05-10 (4), 06-10 (5)

Now that you've posted a whole bunch of code, half of which is commented out and I assume the other half of which does not do what you want it to do, let's disregard it and try starting over from the beginning.

How is all of this data being stored? What do the database tables look like? What does the data in the database tables look like?

mac_gyver · November 3, 2020

1 hour ago, paulvz said:

then to the next date

you should allow for multiple stock in events per date. this will assign a 'stock in id' for each event that increases the stock.

when you consume stock, you would insert 'child' records, using the parent's stock in id to relate the stock out event back to the corresponding stock in event. if you consume stock from more than one stock in event, you would insert a stock out/child record for each amount you take from each stock in event.

to find the earliest stock in event(s) that has(have) an available stock amount equal or greater than a needed quantity, you would join the parent and child records ON the stock in ids, summing the plus stock in amounts and subtracting the stock out amounts.

i'm sure @Barand will be along shortly to post an example of how to do this.

paulvz · November 3, 2020

36 minutes ago, requinix said:
That was not easy to understand.
01-10
- Incoming: 10
- Outgoing: 5; 5 taken from today's inventory (10-5=5)
- Ending inventory: 01-10 (5)

02-10
- Incoming: 10
- Outgoing: 7; 5 taken from 01-10's inventory (5-5=0), 2 taken from today's inventory (10-2=8)
- Ending inventory: 02-10 (8)

03-10
- Incoming: 8
- Outgoing: 21; 8 taken from 02-10's inventory (8-8=0), 8 taken from today's inventory (8-8=0), remainder 5
- Ending inventory: deficit of 5

04-10
- Incoming: 15
- Outgoing: 3; deficit 5 taken from today's inventory (15-5=10), 3 taken from today's inventory (10-3=7)
- Ending inventory: 04-10 (7)

05-10
- Incoming: 10
- Outgoing: 6; 6 taken from 04-10's inventory (7-6=1)
- Ending inventory: 04-10 (1), 05-10 (10)

06-10
- Incoming: 5
- Outgoing: 7; 1 taken from 04-10's inventory (1-1=0), 6 taken from 05-10's inventory (10-6=4)
- Ending inventory: 05-10 (4), 06-10 (5)
Now that you've posted a whole bunch of code, half of which is commented out and I assume the other half of which does not do what you want it to do, let's disregard it and try starting over from the beginning.

How is all of this data being stored? What do the database tables look like? What does the data in the database tables look like?

Barand · November 3, 2020

32 minutes ago, mac_gyver said:

i'm sure @Barand will be along shortly to post an example of how to do this.

I will be waiting for paulvz to answer requinix's questions regarding the data, instead of just reposting it.

paulvz · November 3, 2020

https://forums.phpfreaks.com/profile/3105-barand/

good day - mysql wit 5 field, date - stock id - stock in stock out - stock level

2020-10-01 | 9 | 15 | 10 | 5

2020-10-02| 9 | 10 | 7 |

2020-10-03| 9 | 23 | 25 |

2020-10-04| 9 | 30 | 16 |

on 2020-10-01 stok level is correct as it is the first day 15-10 = 5 so 5 stock left

2020-10-01 stock out is 7. so 7 needs to be subtructed from oldest stock level that os > 0 in this case 7-5 leaving 2020-10-1 with 0 and now before wa add stock in with 2020-10-02with stock of -2. then add stick in of 10 wich leaves 2020-10-2 with a stock value of 8

2020-10-03 has stock out of 23 - sp 2020-10-02 stock wil be 23-8 leaving it 0 and then 2020-10-03 stoin in subtracted from remaining. 9-15 leaving a negative stock of -6

on the 2020-10-04 stock out is 16, but previous day already has a minus, so it wont effect previous day 2020-10-04 stock wil be -16. now we add stock in for 2020-10-04 wich is 16. so first previous stok day calculation 30 -8 making 2020-10-03 0, leaving stock in with 24. now we calculate todays stock 24-16 leaving a stock level of 8.

Hope this makes more sense

Edited November 3, 2020 by paulvz
clarification

Barand · November 3, 2020

Still not totally clear. If, for the time being, we ignore which batch to issue from then you have received 78 and issued 58. That leaves 20, not 8

+------------+-----+----+-----+-------+
| Date       |  ID | In | Out | Level |
+------------+-----+----+-----+-------+
| 2020-10-01 |   9 | 15 |  10 |   5   |
| 2020-10-02 |   9 | 10 |   7 |       |
| 2020-10-03 |   9 | 23 |  25 |       |
| 2020-10-04 |   9 | 30 |  16 |       |
+------------+-----+----+-----+-------+
                   | 78 |  58 |        
                   +----+-----+

paulvz · November 3, 2020

+------------+-----+----+-----+-------+

id| Date | ID | In | Out | Level |new level

+------------+-----+----+-----+-------+

a| 2020-10-01 | 9 | 15 | 10 | 5 | 0 |

b| 2020-10-02 | 9 | 10 | 7 |8 | 0 | b stock out 7-a-level5 leaves a-level with 0 and a remainders of b-stock out with -2. then b-stockin was 10 so 10-2 = 8

c| 2020-10-03 | 9 | 23 | 25 |6 | 0 | now c-stock out - 25. b-stock level = 8(c-stockout 25 - b-stock level 8 = 17. so bstocklevel now becomoes 0, but c-stock in was 23 so it would be 23 -17 = 6. c-level now = 6

d| 2020-10-04 | 9 | 30 | 16 |20 | d-stockout is 16 - c-level is 6 so leavin c level with 0 and d-out with 1o. d-stick in has 30 wich leaves new stock value with 20

e| +------------+-----+----+-----+-------+

| 78 | 58 | +----+-----+

But levels are allowed to go into negative.

ig for example stock out in c was only 2 then

c level would be 6 and d level 30

Edited November 3, 2020 by paulvz

Barand · November 3, 2020

Is this what you want to achieve

 Receipts        Batch   |      a       |      b       |      c       |      d       |
                 Date    |  2020-01-01  |  2020-01-02  |  2020-01-03  |  2020-01-04  |
                 Qty     |     15       |     10       |     23       |     30       |   
 ------------------------+--------------+--------------+--------------+--------------+
 Issues                  |              |              |              |              |
                         |              |              |              |              |
 2020-01-01      10      |     10       |              |              |              | 10 from batch a
 2020-01-02       7      |      5       |      2       |              |              |  5 from batch a,  2 from batch b
 2020-01-03      25      |              |      8       |     17       |              |  8 from batch b, 17 from batch c 
 2020-01-04      16      |              |              |      6       |     10       |  6 from batch c, 10 from batch d

paulvz · November 3, 2020

Yes that is what i need, We wil work our cost price on stock on hand for that day, it might be that no stck has gone out for 3 days, but the stock level has to be in the day it was received as prices changed daily.

paulvz · November 3, 2020

But i only used one product here, there are multiple products

Barand · November 4, 2020

As mac_gyver suggested, have two table viz. stock_receipts, stock_issues.

CREATE TABLE `stock_receipt` (                                   |     CREATE TABLE `stock_issue` (
  `batch_id` int(11) NOT NULL AUTO_INCREMENT,                    |       `stock_issue_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,                             |       `product_id` int(11) DEFAULT NULL,
  `receipt_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,    |       `issue_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `quantity` int(11) DEFAULT NULL,                               |       `job_no` int(11) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,                            |       `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`batch_id`),                                      |       `batch_id` int(11) DEFAULT NULL,
  KEY `index2` (`product_id`,`receipt_date`)                     |       PRIMARY KEY (`stock_issue_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;                            |       KEY `index2` (`product_id`, `issue_date`),
                                                                 |       KEY `index3` (`batch_id`)
                                                                 |     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
                                                                 |

When a new issue is entered,

get the stock records for that product which have unissued stock remaining

SELECT  r.batch_id
     , r.quantity
     , SUM(i.quantity) as issued
     , CASE WHEN SUM(i.quantity) IS NULL
            THEN r.quantity    
            ELSE r.quantity - SUM(i.quantity)
       END as remaining
FROM stock_receipt r 
     LEFT JOIN
     stock_issue i USING (batch_id)
WHERE r.product_id = 9
      AND receipt_date < NOW()
GROUP BY r.batch_id
HAVING remaining > 0;
        
+----------+----------+--------+-----------+
| batch_id | quantity | issued | remaining |
+----------+----------+--------+-----------+
|        1 |       15 |     10 |         5 |
|        2 |       10 |   NULL |        10 |
+----------+----------+--------+-----------+

Loop through these records and allocate remaining quantities, creating issue records containing the batch and quantity. If there are more required issues than remain in stock, write a pending issue for the outstanding quantity without a batch_id. Therefore if 20 are issued, the issue records written to the stock_issues table would be

Batch   Qty
------+------
   1     5
   2    10
         5

When a new receipt is entered, the process is similar but reversed. I.E.

Batch   Qty
------+------
   1     5
   2    10
   3     2
         3

get the pending issues for that product (no batch_id). Loop through them allocating the new batch_id where they can be fulfilled from the new receipt. If 2 are received then 2 of the pending 5 items can come from batch 3 (new batch) there would then be 3 pending.

Below is the code for a small application to illustrate the process

<?php
include 'db_inc.php';
$db = pdoConnect();

###
### PROCESS NEW RECORDS
###
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    if ($_POST['type'] == 'I') {                                                        // issue
        $stmt = $db->prepare("INSERT INTO stock_issue
                              (product_id, job_no, quantity, batch_id)
                              VALUES (?,?,?,?)
                             ");
                             
        // get the stock records with available stock
        $res = $db->prepare("SELECT  r.batch_id
                                     , r.quantity
                                     , SUM(i.quantity) as issued
                                     , CASE WHEN SUM(i.quantity) IS NULL
                                            THEN r.quantity    
                                            ELSE r.quantity - SUM(i.quantity)
                                       END as remaining
                                FROM stock_receipt r 
                                     LEFT JOIN
                                     stock_issue i USING (batch_id)
                                WHERE r.product_id = ?
                                      AND receipt_date < NOW()
                                GROUP BY r.batch_id
                                HAVING remaining > 0;
                                ");
        $res->execute([ $_POST['product'] ]);
        // match them against issue quantity
        $q = $_POST['qty'];
        foreach ($res as $r)  {
            if ($r['remaining'] >= $q) {
                // issue qty met from this batch
                $stmt->execute([$_POST['product'], $_POST['job_no'], $q, $r['batch_id'] ]);
                $q = 0;
                break;
            }
            else {
                // issue the remaining qty
                $stmt->execute([$_POST['product'], $_POST['job_no'], $r['remaining'], $r['batch_id'] ]);
                $q -= $r['remaining'];
            }
        }
        if ($q > 0) {
            // write pending issue
            $stmt->execute([$_POST['product'], $_POST['job_no'], $q, null ]);
        }
    }
    elseif ($_POST['type'] == 'R')  {                                                  // receipt
        $rins = $db->prepare("INSERT INTO stock_receipt
                              (product_id, quantity, price)
                              VALUES (?, ?, ?)
                             ");
        $rins->execute([ $_POST['product'], $_POST['qty'], $_POST['price'] ]);
        $newbatch = $db->lastInsertId();
        // find any pending issues awaiting new stock
        $res = $db->prepare("SELECT stock_issue_id as id
                                  , quantity
                                  , issue_date
                                  , job_no
                             FROM stock_issue
                             WHERE product_id = ?
                                   AND batch_id IS NULL
                             ORDER BY issue_date
                            ");
        $res->execute([ $_POST['product'] ]);
        // loop through pending issues and allocate batch_ids
        $iupdtb = $db->prepare("UPDATE stock_issue
                               SET batch_id = ?
                               WHERE stock_issue_id = ?
                              ");
        $iupdtq = $db->prepare("UPDATE stock_issue
                               SET  batch_id = ?
                                  , quantity = ?
                               WHERE stock_issue_id = ?
                              ");
        $iins = $db->prepare("INSERT INTO stock_issue
                              (product_id, issue_date, job_no, quantity, batch_id)
                              VALUES (?,?,?,?,?)
                             ");
        $q = $_POST['qty'];
        foreach ($res as $i) {
            if ($q == 0) break;
            if ($i['quantity'] <= $q) {
                // issue from this batch
                $iupdtb->execute([ $newbatch, $i['id'] ]);
                $q -= $i['quantity'];
            }
            else {
                // issue what we can and create new pending issue for qty remaining
                $iupdtq->execute([ $newbatch, $q, $i['id'] ]);
                $iins->execute([ $_POST['product'], $i['issue_date'], $i['job_no'], $i['quantity'] - $q, null ]);
                $q = 0;
            }
        }
    }
    
    header("Location: #");
    exit;
}


###
### GET RECEIPTS
###
$res = $db->query("SELECT r.batch_id
                        , r.product_id
                        , r.quantity
                        , r.price
                        , CASE WHEN SUM(i.quantity) IS NULL
                                    THEN r.quantity    
                                    ELSE r.quantity - SUM(i.quantity)
                               END as remaining
                    FROM stock_receipt r 
                         LEFT JOIN
                         stock_issue i USING (batch_id)
                    GROUP BY r.batch_id
                    ORDER BY product_id, receipt_date     
                  ");
$receipts = '';
foreach ($res as $r) {
    $receipts .= "<tr><td>" . join('</td><td>', $r) . "</td></tr>\n";
}

###
### GET ISSUES
###
$res = $db->query("SELECT i.job_no
                        , i.product_id
                        , i.quantity
                        , r.batch_id
                        , r.price
                   FROM stock_issue i 
                        LEFT JOIN 
                        stock_receipt r USING (batch_id)
                   ORDER BY i.product_id, issue_date     
                  ");
$issues = '';
foreach ($res as $r) {
    $issues .= "<tr><td>" . join('</td><td>', $r) . "</td></tr>\n";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Stock Management</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
</head>
<body>
<div class="w3-container w3-green w3-padding">
    <h1>Stock Management</h1>
</div>

<div class="w3-row-padding w3-padding w3-bottombar w3-responsive">
    <div class="w3-col m6">
        <div class="w3-panel w3-padding w3-dark-gray">
            Stock Receipt
        </div>
        <form class="w3-margin w3-padding w3-light-gray" method='POST'>
            <input type="hidden" name="type" value="R"
            <label>Product</label>
            <input class="w3-input" type="number" name="product">
            <label>Quantity</label>
            <input class="w3-input" type="number" name="qty">
            <label>Price</label>
            <input class="w3-input" type="number" step="0.01" name="price">
            <br><button class="w3-button w3-blue">Add receipt</button>
        </form>
    </div>
    <div class="w3-col m6">
        <div class="w3-panel w3-padding w3-dark-gray">
            Stock Issue
        </div>
        <form class="w3-margin w3-padding w3-light-gray" method='POST'>
            <input type="hidden" name="type" value="I"
            <label>Product</label>
            <input class="w3-input" type="number" name="product">
            <label>Quantity</label>
            <input class="w3-input" type="number" name="qty">
            <label>Job Number</label>
            <input class="w3-input" type="number"  name="job_no">
            <br><button class="w3-button w3-blue">Add issue</button>
        </form>
    </div>
</div>

<div class="w3-row-padding w3-padding w3-bottombar w3-responsive">
    <div class="w3-col m6">
        <div class="w3-panel w3-padding w3-dark-gray">
            Receipts
        </div>
        <table class="w3-table-all">
            <tr><th>Batch</th>
                <th>Product</th>
                <th>Quantity</th>
                <th>Price</th>
                <th>Remain</th>
            </tr>
            <?=$receipts?>
        </table>
    </div>
    <div class="w3-col m6">
        <div class="w3-panel w3-padding w3-dark-gray">
            Issues
        </div>
        <table class="w3-table-all">
            <tr><th>Job</th>
                <th>Product</th>
                <th>Qty</th>
                <th>Batch</th>
                <th>Price</th>
            </tr>
            <?=$issues?>
        </table>
    </div>
</div>

</body>
</html>

Sign In

Stock managemet subtracting from oldest first

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information