php(mysql)中的FIFO计算

lf3rwulv 于 2022-10-22 发布在 PHP

关注(0)|答案(2)|浏览(174)

我有两个数据表stock_incomes、stock_outcomes和stock_outcomes_fifo（我插入的是预先计算的数据）：
stock_incomes（存储剩余数据）

id| Levtv
-----------
7 | 100
8 | 250
9 | 350

stock_outcomes（这里是重点）

id| Quantity
--------------
1 |  150*

我对库存产出没有问题。数量小于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...
}
?>

mysql

来源：https://stackoverflow.com/questions/34775407/fifo-calculation-in-php-mysql

2条答案

按热度按时间

svmlkihl1#

问题已经解决，下面是最终的工作代码，以防有人需要：

<?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 */

?>

赞(0）回复(0）举报 2022-10-22

1zmg4dgp2#

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

赞(0）回复(0）举报 2022-10-22

我来回答

php(mysql)中的FIFO计算

2条答案

相关问题

热门标签

最新问答