如何减少所需库存的多行值[基于Fifo的出库]

Question

我正在维护一个库存系统，同时销售一种产品，可用库存将根据最老购买的产品减少，这意味着首先购买的产品将首先售罄。我用以下几行来计算销售额:

$product_id = $request->get('product_id');
$quantity = $request->get('quantity');

我用下面的语句从表中查询了最老的可用产品:

$stock_available = DB::table('purchase_products')
->select('quantity','purchase_price')
->where('quantity','>',0)
->where('product_id',$product_id[$i])
->orderBy('created_at','asc')
->get();

我在for循环中查询了

for ($i = 0; $i < count($product_id); $i++) {
$stock_available = DB::table('purchase_products')
->select('quantity', 'purchase_price', 'id')
->where('quantity', '>', 0)
->where('product_id', $product_id[$i])
->orderBy('created_at', 'asc')
->get();

foreach ($stock_available as $entry) {
if ($quantity[$i] >= $entry->quantity) {
$quantityFilled = $entry->quantity; // the number of items which can be filled with this entry
DB::table('purchase_products')
->where('id', $entry->id)
->update(['quantity' => 0]);
$quantity[$i] = $quantity[$i] - $quantityFilled; // lower the sell count by the quantity filled
} else {
// if $sellCount is less than the $entry->quantity, lower the quantity of entry by $sellCount and save $entry;
$entry->quantity = $entry->quantity - $quantity[$i];
DB::table('purchase_products')
->where('id', $entry->id)
->update(['quantity' => $entry->quantity]);
$quantity[$i] = 0;
}
if ($quantity[$i] == 0) {
break;
}
}
}
return redirect('sales');

但我的问题是我如何减少，如果多行将影响所需的库存。假设我要出售20件商品，但查询的第一行有10件，查询的第二行有5件，查询的第三行有最后5件。我如何减少库存?

Answer 1

您可以在迭代$stock_available的同时保持一个sellCount变量来跟踪要减少多少。

$sellCount = 20; // 20 is based from your example above but can be set to $quantity based on question
foreach($stock_available as $entry)
{
if($sellCount >= $entry->quantity){
$quantityFilled = $entry->quantity; // the number of items which can be filled with this entry
$entry->quantity = 0; // since all of the quantity will be used, remaining quantity will be 0
$entry->save(); // save the entry
$sellCount = $sellCount - $quantityFilled; // lower the sell count by the quantity filled
} else {
// if $sellCount is less than the $entry->quantity, lower the quantity of entry by $sellCount and save $entry;
$entry->quantity = $entry->quantity - $sellCount;
$entry->save();
$sellCount = 0;
}
if ($sellCount == 0) {
break;
}

}

Answer 2

这段代码首先启动一个数据库事务，以确保所有股票更新都自动发生。然后计算给定产品的总可用库存，如果没有足够的可用库存，则抛出异常。

$product_id = 123; // replace with the actual product ID
$quantity_to_decrease = 10;
DB::beginTransaction();
try {
$available_stock = DB::table('stocks')
->where('product_id', $product_id)
->sum('quantity');
if ($available_stock < $quantity_to_decrease) {
throw new Exception("Not enough stock available.");
}
$stocks = DB::table('stocks')
->where('product_id', $product_id)
->orderBy('quantity', 'desc')
->get();
foreach ($stocks as $stock) {
if ($quantity_to_decrease <= 0) {
break;
}
$quantity_to_reduce = min($quantity_to_decrease, $stock->quantity);
DB::table('stocks')
->where('id', $stock->id)
->decrement('quantity', $quantity_to_reduce);
$quantity_to_decrease -= $quantity_to_reduce;
}
DB::commit();
} catch (Exception $e) {
DB::rollback();
return response()->json(['error' => $e->getMessage()], 400);
}
return response()->json(['message' => 'Stock decreased successfully.'], 200);