计算销售订单和定购单的准确保证金

Question

我正在尝试生成一个从下面的数据库中计算保证金的报告。问题是产品的成本(存在于purchase_order_products表中)可能会发生变化。

2017年-06-08年产品成本为4022伊拉克第纳尔，而2017-07-25年成本为1094伊拉克第纳尔。这太让人困惑了。我无法得到每一种产品的确切成本。

我编写了一个PHP算法，它循环遍历所有订单和采购订单，并将最老的成本用于最新的成本。但是该算法具有很高的时间复杂度。这可以仅仅使用mysql查询来完成吗？

请查看以下情景：

公司为产品X制作了一份定单:数量3，成本10。

顾客购买2件产品X售价:第1天12件(仍有1件物品库存，成本10)

公司为X产品制作了一份定购单:数量4，成本9。

客户购买3件产品X售价:第2天12

客户购买2个产品X售价:第3天12

公司为X产品制作了一份定单:数量2，成本11.第3天

客户购买2个产品X售价:第3天12

报告：

第一天:以12英镑售出2个产品X，成本10，利润:2*(12-10)

第二天:以12的价格售出3件产品X，1件的成本为10件，2件的成本为9件，

利润:1*(12-10)+2*(12-9)

第三天:以12英镑售出2个产品X，成本9，利润:2*(12-9)

以12英镑售出2个产品X，成本11，利润:2*(12-11)

因此，新销售的产品的利润是按其相应的成本计算的。希望你明白我的意思。

数据库结构：

​

4数据库产品

​

​

产品采购订单为上述产品

​

​

销售产品

​

转储文件附在这里

Answer 1

在给定的时间内，对象的价格由“股票总价格/股票总数”的公式计算。

要获得这个结果，您需要执行两个查询：

• 第一个知道售出物品数量(总价和数量)的人：

sql：

SELECT SUM(row_total) sale_total_cost, SUM(quantity_ordered) sale_total_number
    FROM sales_order_item soi
    JOIN sales_order so ON soi.sales_order_id=so.id
    WHERE so.purchase_date<'2017-06-07 15:03:30'
    AND soi.product_id=4160;

• 第二个知道你买了多少产品的人

sql：

SELECT SUM(pop.cost * pop.quantity) purchase_total_price, SUM(pop.quantity) purchase_total_number
    FROM purchase_order_products pop
    JOIN purchase_order po ON pop.purchase_order_id=po.id
    WHERE po.created_at<'2017-06-07 15:03:30'
        AND pop.product_id=4160;

• 产品4160在2017-02-01 14:23:35的价格是：

(purchase_total_price - sale_total_cost) / (purchase_total_number - sale_total_number)

问题是"sales_order“表是从2017-02-01 14:23:35开始的，而"purchase_order”表是从2017-06-07 08:55:48开始的。因此，结果将是不连贯的，只要你不能跟踪你所有的购买从一开始。

编辑：

如果您可以修改您的表结构，并且只对未来的销售感兴趣。

添加purchase_order_products表中出售的项目数

您必须修改purchase_order_products以使每个产品的消费量：

ALTER TABLE `purchase_order_products` ADD COLUMN sold_items INT DEFAULT 0;

初始化数据

为了使其工作，您必须使sold_items列反映您的真实股票。

应该使用以下请求UPDATE `purchase_order_products` SET sold_items=quantity;初始化表

然后用每个产品的确切库存手动更新表(这意味着quantity_ordered table _items必须反映您的实际库存。

这只需做一次。

将购买价格添加到sales_order_item表中

ALTER TABLE sales_order_item ADD total_purchase_price INT DEFAULT NULL

输入新的销售订单

当您输入新的销售订单时，必须使用以下命令获得包含其余项目的最古老的采购订单：

SELECT * FROM `purchase_order_products` WHERE quantity!=sold_items where product_id=4160 ORDER BY `purchase_order_id` LIMIT 1;

然后，您必须增加sold_items值，并计算总购买价格(sum)，以填充total_purchase_price列。

计算裕度

根据row_total和total_purchase_price在sales_order_item表中的差异可以很容易地计算出边距。

Answer 2

您为什么不轻松一下，在订单表中添加一个利润列，在客户购买product.This时实时计算，您可以完全从销售订单中计算您的标牌，因为这实际上已经计算出来了，以便生成销售价格。当然，这只适用于未来的销售，但是您可以使用现有代码，很少修改就可以填充旧记录的利润列，并且在更新之前只对旧事务运行一次此代码。

详细说明：：

更改表"sales_order"，添加"profit"列。通过这种方式，您可以使用其他相关列(total_paid, total_refund, total_due, grand_total)来计算和，因为您可能希望通过在计算中包括所需的货币字段来更好地控制报表--例如，使用total_payed生成一个仅排除tota_due的报表，或者将其包含在不同类型的报表中，换句话说，您只能从这个表中生成多个报表类型，而只添加这一列，而不影响DB系统。

编辑：

您还可以向该表添加成本列，以便快速检索，并将连接和查询最小化到其他表，如果您想更进一步，可以为报表添加一个专用表，例如，从上个月开始生成缺少的报告和检查旧订单状态将非常有用。

Answer 3

非常感谢您使用FIFO方法来管理股票。但是，这并不意味着您需要使用FIFO来计算边距。文章估价概述了这些选项。(贵国的条例可能会排除一些选择。)

我相信一个可重复的解决方案的FIFO保证金计算的个人销售是复杂的。期初余额、退货、部分交货、分批装运、停产处理、盘点调整、损坏货物等都很复杂.

在您的问题中，数据库结构似乎没有解决这些问题。

通常，这些问题是通过计算期间库存价值的变化来计算期间(日、月等)的利润率/利润来解决的。

如果可以使用平均成本法，则可以使用纯SQL计算保证金。我相信其他方法似乎需要一些迭代，因为SQL中没有固有的顺序。(您可以通过创建一个新表并存储上一个句点值来提高性能。)

我不会太担心将整个解决方案放在SQL中，因为这似乎不会降低问题的计算复杂性。不过，在数据库引擎中进行同样多的计算可能有速度优势，特别是在数据集很大的情况下。

您可能会发现这篇文章很有趣：基于集合的速度同步: FIFO库存SQL问题。(外面有一些聪明的人！)