背景：1.美客多，如果是所有的预估佣金都是0的话，那前后毛率有出入的差异加大，导致业务判断是否发货缺少标准。
2.ERP 首页销售额同比数据按金额 & 时间归类，缺少 平台值，导致环比数值失真。
需求1：
现状：
估算：(订单总额【123.28】- 运费【63.04】- 采购费【9.50】- 交易佣金【0.00】- 手续费用【0.00】-汇率折损费【9.86】- 销售VAT【0.00】 - 自定义费用【1.28】) ÷ 订单总额【123.28】= 利润率【32.12%】
调整：
估算：(订单总额【123.28】- 运费【63.04】- 采购费【9.50】- 交易佣金【22.21】- 手续费用【0.00】-汇率折损费【9.86】- 销售VAT【0.00】 - 自定义费用【1.28】) ÷ 订单总额【123.28】= 利润率【14.11%】
逻辑：
订单日志---预估逻辑，美客多佣金添加
首选逻辑值来源 .RB_Mercadolibre_Orderlist.SaleFee
为空 则 取默认佣金配置项
需求2：
现状：
首页面 ，近7天销售额 同比&环比 值无税费扣减，
首页面 ，销售额 同比 值无含税费扣减.
首页面 ，近7天销售额 同比&环比 值需含税费扣减，
首页面 ，销售额 同比 值需含税费扣减.
仅作参考
https://global-selling.mercadolibre.com/landing/pricing
首页需求参考
SELECT oi.CustomerID,
SUM ( CASE WHEN TotalPrice = 0 AND oi.DownLoadUpdateTime= '2000-01-01' THEN 0 ELSE 1 END ) AS OrderNum,
CONVERT(VARCHAR(100), oi.AddTime, 23) dt,
os.OrderSourceType OrderSourceType,
((sum( TotalPrice )-SUM( oi.TotalActivityMinusPrice )) / (CASE WHEN SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END) = 0 THEN
1
ELSE
SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END)
END)
* SUM ( CASE WHEN TotalPrice = 0 AND oi.DownLoadUpdateTime= '2000-01-01' THEN 0 ELSE 1 END ))AS SalesUSD,
(sum( TotalPrice )-SUM( oi.TotalActivityMinusPrice )) / (CASE WHEN SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END) = 0 THEN
1
ELSE
SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END)
END) AS SinglePrice_ActivityMinusPrice,
SUM( oi.TotalActivityMinusPrice ) as TotalActivityMinusPrice
FROM
(
SELECT oi.CustomerID,
oi.OrderSourceID,
oi.OrderCode,
oi.TotalPrice / (CASE WHEN ExchangeUSD = 0 THEN
1
ELSE
ExchangeUSD
END) as TotalPrice,
oi.DownLoadUpdateTime,
oi.ExchangeUSD,
oi.AddTime,
(CASE
WHEN oi.TotalPrice = 0 THEN
0
ELSE
SUM(ol.ActivityMinusPrice/ (CASE
WHEN ExchangeUSD = 0 THEN
1
ELSE
ExchangeUSD
END)
)
END) as TotalActivityMinusPrice
FROM dbo.RB_OrderInfo oi WITH (NOLOCK)
LEFT JOIN RB_OrderList ol WITH (NOLOCK)
ON oi.CustomerID = ol.CustomerID and oi.OrderCode = ol.OrderCode
WHERE
oi.CustomerID=1
and oi.AddTime >= '2022-03-24'
AND oi.AddTime < '2022-04-09'
AND oi.IsStockOrder = 0
AND IsSystem = 0
AND
(
(
IsPay = 1
AND IsFBAOrder = 0
)
OR IsFBAOrder = 1
)
AND OrderStatus <> 2
GROUP BY oi.CustomerID,
oi.OrderSourceID,
oi.OrderCode,
oi.TotalPrice,
oi.DownLoadUpdateTime,
oi.ExchangeUSD,
oi.AddTime
)oi
inner JOIN RB_OrderSource (NOLOCK) os
ON oi.OrderSourceID = os.ID AND oi.CustomerID= os.CustomerID
GROUP BY oi.CustomerID, CONVERT(VARCHAR(100), oi.AddTime, 23),os.OrderSourceType