分批认款+分批付款总结

一、项目背景

系统支持部分认款，部分回款

二、具体功能

1.部分认款

• 销项票已开、验收单已审核通过可以认款
• 输入部分认款金额，提交认款
• 需要减去售后退货数据
• 公式：用订单商品数量减去对应的退货数量，然后进行相应的计算

2.生成结算单

• 已认款数据可以生成结算单
• 操作生成计算单批量按照业务公司和供应商分组生成结算单
• 需要减去售后退货数据
• 公式：回款金额/销售折扣率*供应商回款折扣率；最后一笔回款的金额=回款总金额-分批已回款金额

三、ER图

四、测试SQL

SELECT
  a.order_sn,
  SUM(b.discount_amount) AS sum_discount_amount AS "订单金额",,
  SUM(b.discount_amount - COALESCE(tmp1.tui_money, 0)) AS "总应收",
  SUM(ROUND((b.price * (b.g_rate * 0.01)), 2) * (b.num - COALESCE(tmp1.tui_num_total, 0))) AS "总应付"
FROM
  order a
  JOIN order_goods b ON a.id = b.order_id
  LEFT JOIN (
    SELECT
      order_no,
      sku,
      SUM(price * num) AS tui_money,
      SUM(CASE WHEN a.status IN (2, 3, 4) THEN b.num ELSE 0 END) AS tui_num_total
    FROM
      order_after_sales a
      JOIN order_after_sales_detail b ON a.id = b.after_id
    WHERE
      a.type = 1
    GROUP BY
      order_no,
      sku
  ) tmp1 ON b.product_code = tmp1.sku AND a.order_sn = tmp1.order_no
WHERE
  a.order_sn = 'RT20240105171249431098'
GROUP BY
  a.order_sn;

总结