我有两个数据表stock_incomes、stock_outcomes和stock_outomes_fifo（我插入预先计算的数据）：
stock_incomes（存储剩余数据）
id| Levtv
-----------
7 | 100
8 | 250
9 | 350
股票期权（这是要点）
id| Quantity
--------------
1 | 150*
当stock_outcomes.Quantity小于100（来自stock_incomes的min（Id），请参阅下面的代码）时，我没有问题，但我不知道该写什么代码，如果结果>100，我可以得到计算。在我的例子中，我使用了150，我想在下一个表中获得数据：
stock_outcomes_fifo（我希望插入前两个表中预先计算的数据）
id| IncomeId| OutcomeId| OutcomePart| Leftv
---------------------------------------------
1 | 7 | 1 | 100 | 0
2 | 8 | 1 | 50 | 200
这是我的代码，里面有问题（见代码的最后一部分）：
<?php
include_once("config.inc.php");
include_once("db.class.php");
// stock_outcomes
$db = new db($host, $database, $user, $passwd);
$sql = "SELECT * FROM stock_outcomes WHERE Id = '1'";
$mas = $db->get_array($sql);
if($mas) {
foreach ($mas as $k => $v) {
$OutcomeId = $mas[$k]['Id'];
$OutcomeQuantity = $mas[$k]['Quantity'];
}
}
// stock_incomes
$sql = "select * from stock_incomes where Id = (select min(Id) from stock_incomes where Leftv > 0)";
$mas = $db->get_array($sql);
if($mas) {
foreach ($mas as $k => $v) {
$IncomeId = $mas[$k]['Id'];
$IncomeLeftv = $mas[$k]['Leftv'];
}
}
// insert into stock_outcomes_fifo
if ($OutcomeQuantity <= $IncomeLeftv) {
$OutcomePart = $OutcomeQuantity;
$FifoLeftv = $IncomeLeftv - $OutcomeQuantity;
mysql_query("INSERT INTO `stock_outcomes_fifo` (IncomeId,OutcomeId,OutcomePart,Leftv) VALUES ($IncomeId, $OutcomeId, $OutcomePart, $FifoLeftv)");
}
if ($OutcomeQuantity > $IncomeLeftv) {
// I have no idea what php function to use in this case... please give me direction, thank you...
}
?>
问题已经解决，以下是最后的工作代码，以备有人需要：
<?php
include_once("config.inc.php");
include_once("db.class.php");
// stock_outcomes
$db = new db($host, $database, $user, $passwd);
$sql = "SELECT * FROM stock_outcomes WHERE Id = '1'";
$mas = $db->get_array($sql);
if($mas){
foreach ($mas as $k=>$v) {
$OutcomeId=$mas[$k]['Id'];
$OutcomeBarCode=$mas[$k]['BarCode'];
$OutcomeQuantity=$mas[$k]['Quantity'];
}
}
/* - Start code */
if ($OutcomeQuantity > 0) {
$sql = "select * from stock_incomes where Leftv > 0 order by id asc";
$mas = $db->get_array($sql);
if ($mas) {
//filing stock_outcomes_fifo
foreach ($mas as $k=>$v) {
$IncomeId = $mas[$k]['Id'];
$IncomeQuantity = $mas[$k]['Quantity'];
$IncomeUnitPrice = $mas[$k]['UnitPrice'];
$IncomeLeftv = $mas[$k]['Leftv'];
$OutcomePart = min($OutcomeQuantity, $IncomeLeftv);
$FifoLeftv = $IncomeLeftv - $OutcomePart;
$FifoCost = $IncomeUnitPrice * $OutcomePart;
mysql_query("INSERT INTO `stock_outcomes_fifo` (BarCode,IncomeId,OutcomeId,OutcomePart,UnitPrice,Leftv,Cost) VALUES ($OutcomeBarCode, $IncomeId, $OutcomeId, $OutcomePart, $IncomeUnitPrice, $FifoLeftv, $FifoCost)");
mysql_query("UPDATE `stock_incomes` SET Leftv = ".$FifoLeftv." WHERE Id = ".$IncomeId);
$OutcomeQuantity -= $OutcomePart;
if ($OutcomeQuantity <= 0) break;
}
$OutcomeCostQuery = "select sum(Cost) as summ from stock_outcomes_fifo where OutcomeId = ".$OutcomeId."";
$OutcomeCost = mysql_query($OutcomeCostQuery);
$OutcomeCostResult = mysql_fetch_array($OutcomeCost);
mysql_query("UPDATE `stock_outcomes` SET Cost = ".$OutcomeCostResult["summ"]." WHERE Id = ".$OutcomeId."");
}
} /* - Finish code */
?>
Please help me let me explain with this.....
purchase table
id purchase_id product_id qty net_unit_cost created_at
-------------------------------------------------------------------------
1 1 1 10 10 2022-10-10
--------------------------------------------------------------------------
2 2 1 20 12 2022-10-10
Sale table
sale_id product_id qty net_unit_price created_at
1 1 11 15 2022-10-10
in this, if i sold '11' units then how can i subtract from the rows to get remaining units? i've to subtract '10' units from first row and '1' unit from second row...