paulvz

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

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.

+------------+-----+----+-----+-------+ 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

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

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]); } }/* */