php – Decrementing a stocked item in mysql-ThrowExceptions

Exception or error:

Good day,
I have created a script for an ERP solution, which holds items in a stocks table, with the same SKU appearing multiple times with different quantities and lots.
When I’m selling items, I want the stock quantity to be treated as a sum of all the items with the same SKU, for example:

|item_id     |item_sku|   item_quantity|         lot|
|1           |10101001|   0            |    xyz     |
|2           |10101002|   5            |    012     |
|3           |10101001|   6            |    123     |
|4           |10101001|   7            |    456     |
|5           |10101001|   8            |    yf3     |

If I sell 14 units of 10101001, how would I go about writing the code that leaves item_id[1] untouched (as it’s item_quantity is < 1), take all the quantity from item_id[3] and from item_id[4] and decrement item_id[5]’s quantity field by 1 (14 = 6+7+1) in an object-oriented approach? I thought about using a for each loop, but all I got was decrementing the quantity field over all the rows – so maybe I’m not writing it the right way. Also, I thought about treating the item_quantity as a sum, so grouping by SKU, summing up the item_quantity field, then subtracting the sold_quantity from that sum, but then how would I go about spreading the quantities in the database rows again?
I’m really stuck here, and this issue is driving me mad since Monday evening. Any help will be greatly appreciated.
LE: Here is my code:

$toSell = $value['sales_quantity'];
        $sum = StockDetail::where('sku', $product['sku'])->sum('stock_quantity');
        $updatedStock = $sum - $toSell;
//        StockDetail::where('sku', $product['sku'])->update(['stock_quantity' => $updatedStock]); // doesn't work for lots
        foreach($salesProduct as $key => $value) {
//
// more code here which is irrelevant to the issue I'm facing
//
            foreach($stockNotNull as $stock => $product) {
                if($toSell === 0) {
                    return;
                }
                do {
                    $product->stock_quantity->decrement();
                    $toSell--;
                    if ($product->stock_quantity === 0) {
                        continue;
                    }
                } while($product->stock_quantity > 0 && $toSell > 0);
            }
        }
How to solve:

The logic is:

  1. Start transaction.
  2. Get all rows for given item_sku where item_quantity > 0 ordered by item_quantity ascending.
  3. Initialize remaining_to_sell with the quantity to sell
  4. Iterate over rows, subtracting min(remaining_to_sell, row.item_quantity from item_quantity and save the row. Decrement that quantity from remaining_to_sell
  5. Continue iterating until remaining_to_sell goes to zero.
  6. If at end of rows the remaining_to_sell is not zero, you have no enough stock, so rollback the transaction and throw an error. Otherwise, commit it

I’ll edit the answer soon with the actual code. I’m on mobile now.

Answer´╝Ü

foreach ($stockNotNull as $key => $value) {
            if ($toSell < 1) {
                return;
            }
            do {
                --$stockNotNull[$key]->stock_quantity;
                --$toSell;
                StockDetail::where('stock_id', $stockNotNull[$key]->stock_id)
                    ->update(['stock_quantity' => $stockNotNull[$key]->stock_quantity]);
            } while(($toSell != 0) && ($stockNotNull[$key]->stock_quantity != 0));
        }

It seems I forgot basic algorithms and loops since last I had to do this back in college…

Leave a Reply

Your email address will not be published. Required fields are marked *