4 回答

• 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 TABLEpurchase_order_productsADD COLUMN sold_items INT DEFAULT 0;

  初始化数据

  为了使其工作，您必须使sold_items列反映您的真实库存

  您应该使用以下请求初始化表 UPDATEpurchase_order_productsSET sold_items=quantity;

  然后使用您的每个产品的确切库存手动更新表格（这意味着quantity_ordered-sold_items必须反映您的实际库存 .

  这只需要进行一次 .

  将购买价格添加到sales_order_item表

  ALTER TABLE sales_order_item ADD total_purchase_price INT DEFAULT NULL

  输入新的销售订单

  输入新的销售订单时，您必须使用以下命令获取包含剩余项目的最早的采购订单：

  SELECT * FROMpurchase_order_productsWHERE quantity!=sold_items where product_id=4160 ORDER BYpurchase_order_idLIMIT 1;

  然后，您将增加sold_items值，并计算总购买价格（总和）以填充total_purchase_price列 .

  计算保证金

  可以使用sales_order_item表中row_total和total_purchase_price之间的差异轻松计算保证金

  回复于 2024-05-09T01:46:02+08:00
• 6

  为什么不放轻松并在订单表中添加一个利润列，该列表是在客户购买产品时实时计算的 . 这样，您可以仅根据销售订单计算您的损失，因为这是实际的已经以某种方式计算以产生销售价格 . 当然，这仅适用于未来的销售，但您可以使用现有代码进行少量修改，以填充旧记录的利润列，并且您将在更新之前仅对旧事务运行此代码一次 .
  To elaborate more:
  更改表 "sales_order" 添加 "profit" 列 . 这样，您可以使用其他相关列 (total_paid, total_refund, total_due, grand_total) 来计算总和，因为您可能希望通过在计算中根据需要包含这些货币字段来更好地控制报表，例如生成报告使用 total_payed 仅排除 tota_due 或将其包含在不同类型的报告中，换句话说，您只能从此表生成多种报告类型，而不会通过仅添加此列来压倒数据库系统 .
  Edit:
  您还可以向此表添加成本列以便快速检索，并最大限度地减少对其他表的连接和查询，如果您想更进一步，可以为报告添加专用表，这将非常有用，例如生成上个月丢失的报告并检查旧订单状态 .

  回复于 2024-05-09T01:46:02+08:00
• 3

  一些免责声明：

  • 这是一个协助逻辑的尝试，所以's rough code(open to SQL injection attacks, so don' t复制并粘贴这个）

  • 我可能会犯错误，只是想让你走上正轨（和/或进行跟进编辑）

  • 如果您需要每个订单的利润，仅在每个产品的利润中，这将不起作用 . 如果需要，您可能会获得带有BETWEEN子句的日期范围 .

  话虽这么说，我觉得这样的事情对你有用：

  $productsIds = array('4022', '4023', '4160', '4548', '4601');
      foreach($productIds as $pid){
          $sql = "SELECT (soi.revenue - sum(pop.cost)) AS profit, sum(pop.cost) AS total_cost, sum(pop.quantity) AS total_purchased, soi.revenue, soi.total_sold 
                      FROM purchase_order_products pop 
                      JOIN (SELECT sum(price) AS revenue, sum(quantity_ordred) AS total_sold FROM sales_order_item WHERE product_id = ".$pid.") AS soi ON soi.product_id = pop.product_id
                      WHERE pop.product_id = ".$pid." GROUP BY pop.product_id HAVING sum(pop.quantity) < soi.total_sold ORDER BY pop.created_at ASC;";
          $conn->query($sql);
          //do what you want with results
      }
  

  这里的关键是在GROUP BY之后使用HAVING子句来确定在哪里切断查找购买成本的总和 . 只要它们在该范围内，您就可以对它们进行求和，并且您可以通过created_at获得正确的日期排序 .

  再一次，我无法测试这个，我不建议按原样使用这个代码，只是希望这有助于“这里有一个关于如何实现这一点的一般概念” .

  如果我有时间重新创建您的数据库，或者如果您提供带有示例数据的sql转储文件，我可以尝试为您提供一个有效的示例 .

  回复于 2024-05-09T01:46:02+08:00
• 1

  我感谢您使用FIFO方法管理库存 . 但是，这并不意味着您需要使用FIFO来计算边距 . 文章https://en.wikipedia.org/wiki/Inventory_valuation概述了这些选项 . （您所在国家的法规可能会排除某些选项 . ）

  我认为单个销售的FIFO保证金计算的可重复解决方案很复杂 . 期初余额，退货，部分交货，分批装运，无序处理，库存调整，货物损坏等都很复杂 .

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

  通常，通过计算期间库存 Value 的变化来计算期间（日，月等）的保证金/利润来解决这些问题 .

  如果可以使用平均成本方法，则可以使用纯SQL计算边距 . 我相信其他方法似乎需要一些迭代，因为SQL中没有固有的顺序 . （您可以通过创建新表并存储以前的期间值来提高性能 . ）

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

  您可能会发现这篇文章很有趣：Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem . （那里有一些聪明的人！）

  回复于 2024-05-09T01:46:02+08:00