深圳智汇创想科技有限公司

需求描述：

一.统计订单费用：

1.统计添加时间为2021年7月1日至2022年4月1日添加的订单，以订单维度统计订单利润报表中的收入和各项费用；

参考表名及字段：

SELECT 
            '订单利润报表' AS '表名',
       ROUND(SUM(oif.Profits), 2) AS '总利润',
       ROUND(SUM(oif.Incomes), 2) AS '总收入',
       ROUND(SUM(oif.SalesPrice), 2) AS '销售收入',
       ROUND(SUM(oif.TransportPay), 2) AS '运费收入',
       ROUND(SUM(oif.SalesRefunds), 2) AS '退款金额',
       ROUND(SUM(oif.PromotionRebates), 2) AS '促销折扣',
       ROUND(SUM(oif.CommisionFee), 2) AS '交易佣金',
       ROUND(SUM(oif.FBATransactionFees), 2) AS 'FBA操作费',
       ROUND(SUM(oif.BuyPrice), 2) AS '采购成本',
       ROUND(SUM(oif.PackageFees), 2) AS '本地仓运费'
 FROM dbo.RB_Report_OrderSettlement oif 
WHERE CustomerID=1 AND ID<=@LastSynID AND oif.UpdateTime<=@LastSynUpdateTime AND ShipTime>='2021-07-01' AND ADDTime <'2022-04-01'

2.添加时间为2021年7月1日至2022年4月1日添加的订单，以订单维度统计销售成本汇总表中的收入和各项费用；

参考表名及字段：

SELECT 
          '销售成本报表' AS '表名',
       ROUND(SUM(oif.Profits), 2) AS '总利润',
       ROUND(SUM(oif.Incomes), 2) AS '总收入',
       ROUND(SUM(oif.SalesPrice), 2) AS '销售收入',
       ROUND(SUM(oif.TransportPay), 2) AS '运费收入',
       ROUND(SUM(oif.SalesRefunds), 2) AS '退款金额',
       ROUND(SUM(oif.PromotionRebates), 2) AS '促销折扣',
       ROUND(SUM(oif.CommisionFee), 2) AS '交易佣金',
       ROUND(SUM(oif.FBATransactionFees), 2) AS 'FBA操作费',
       ROUND(SUM(oif.BuyPrice), 2) AS '采购成本',
       ROUND(SUM(oif.PackageFees), 2) AS '本地仓运费'
 FROM dbo.RB_Finance_Settlement oif 
 WHERE CustomerID=1 AND ShipTime>='2021-07-01' AND ShipTime <'2022-04-01'

二.统计非订单费用：
1.以月为维度，统计订单利润报表的非订单费用；

参考表名及字段：

SELECT 
            '订单利润报表' AS '表名',

        ROUND(SUM(oif.OtherCredits), 2) AS '其他收入',
        ROUND(SUM(oif.OrderSourceOtherDebits), 2) AS '平台其他支出',
        ROUND(SUM(oif.CostOfAdvertising), 2) AS '广告费用',        

 FROM dbo.RB_Report_OrderSettlement oif 
WHERE CustomerID=1 AND ID<=@LastSynID AND oif.UpdateTime<=@LastSynUpdateTime AND ShipTime>='2021-07-01' AND ADDTime <'2022-04-01'

2.以月为维度，统计销售成本汇总表的订单费用；

SELECT 
          '销售成本报表' AS '表名',

       ROUND(SUM(oif.OtherCredits), 2) AS '其他收入',
       ROUND(SUM(oif.OrderSourceOtherDebits), 2) AS '平台其他支出',
       ROUND(SUM(oif.CostOfAdvertising), 2) AS '广告费用',
 FROM dbo.RB_Finance_Settlement oif 
 WHERE CustomerID=1 AND ShipTime>='2021-07-01' AND ShipTime <'2022-04-01'