统计是否满足待转FBA的订单数时，统计的订单类型需要调整
原逻辑：订单创建类型=“手工创建”排除掉不统计
新逻辑：订单创建类型=“手工创建”“手工创建” 或 平台订单类型=“亚马逊换货订单”排除掉不统计
任务描述
统计是否满足待转FBA的订单数时，统计的订单类型需要调整
原逻辑：订单创建类型=“手工创建”排除掉不统计
新逻辑：订单创建类型=“手工创建”“手工创建” 或 平台订单类型=“亚马逊换货订单”排除掉不统计
| 所属迭代 | 技术研发中心 12月分支版本（2022年） |
|---|---|
| 所属模块 | / |
| 相关需求 | 暂无 |
| 指派给 | Closed 于 2023-10-31 15:02:13 |
| 任务类型 | 后端 |
| 任务标签 | 初始 |
| 开发组 | 运营开发组 |
| 任务状态 | 已关闭 |
| 进度 | 100% |
| 优先级 | P2 |
| 关键词 | |
| 抄送给 |
| 由谁创建 | 梁贵荣 于 2022-12-05 21:06:42 |
|---|---|
| 由谁完成 | huangtiekun 于 2022-12-06 13:49:13 |
| 由谁取消 | 暂无 |
| 由谁关闭 | 周杰 于 2023-10-31 15:02:13 |
| 关闭原因 | 已完成 |
| 最后编辑 | 周杰 于 2023-10-31 15:02:13 |
| 团队 | 最初预计 | 总计消耗 | 预计剩余 |
|---|
| 最初预计 | 1工时 |
|---|---|
| 总计消耗 | 1工时 |
| 预计剩余 | 0工时 |
| 预计开始 | 2022-12-05 |
| 实际开始 | 2022-12-06 |
| 截止日期 | 2022-12-06 |
订单现有的含有“换货订单”的查询逻辑：
根据【待转FBA规则设置】的策略服务，更改查询逻辑：
测试SQL正确性：
SELECT a.OrderSourceID,a.SellerSKU AS OrderSourceSKU,a.SKU
FROM (
SELECT o.OrderSourceID,
l.SKU,
l.SellerSKU,
Sum(l.ProductNum + l.ProductNumUpdate) AS ProductNum,
CONVERT(CHAR(10), PayTime, 120) PayTime,
o.CustomerID,
o.OrderCode,
Sum(CASE
WHEN c.EstimatedSalesVolumeConfigId IS NULL THEN l.ProductNum + l.ProductNumUpdate
WHEN PreferentialRatio > 0
AND o.TotalPrice > 0
AND PromotionDiscountAmount / TotalPrice * 100 > PreferentialRatio THEN 0
WHEN ( CASE
WHEN Len(GroupSKU) > 0 THEN l.GroupProductNum
ELSE l.ProductNum + l.ProductNumUpdate
END ) > c.ExcessProductQuantity
AND c.EstimatedSalesVolumeType = 1 THEN 0
WHEN ( CASE
WHEN Len(GroupSKU) > 0 THEN l.GroupProductNum
ELSE l.ProductNum + l.ProductNumUpdate
END ) > c.ExcessProductQuantity
AND c.EstimatedSalesVolumeType = 2 THEN c.EstimatedSalesVolume
ELSE l.ProductNum + l.ProductNumUpdate
END) EstimatedSalesNum,
( CASE
WHEN PromotionDiscountAmount > 0 THEN 0
ELSE 1
END ) NoDiscountOrderNum
FROM RB_OrderInfo o WITH(nolock)
LEFT JOIN RB_OrderList l WITH(nolock)
ON o.CustomerID = l.CustomerID
AND o.OrderCode = l.OrderCode
LEFT JOIN RB_OrderSource s WITH(nolock)
ON o.CustomerID = s.CustomerID
AND o.OrdersourceID = s.ID
LEFT JOIN RB_Order_EstimatedSalesVolume_Config c(nolock)
ON s.CustomerID = c.CustomerId
AND s.OrderSourceType = c.OrderSourceType
AND c.IsActived = 1
WHERE
o.CustomerID=1
AND ( o.IsPay = 1
OR IsFBAOrder = 1 )
AND o.DownLoadUpdateTime != '2000-1-1'
AND o.PayTime >= '2022-11-06'
AND o.PromotionType not like '%Amazon-ReplacedOrder%'
AND DATEDIFF(YEAR, o.PayTime, '2079-01-01') > 0
AND o.OrderStatus <> 2
AND (LEN(sku) > 0 OR Len(GroupSKU) > 0)
AND o.IsStockOrder = 0
AND ProductNum + ProductNumUpdate > 0
AND l.SellerSKU IN (SELECT CONVERT(VarChar, item) FROM Charsplitforin('T1PDQXQZTR071XF5002314TTH,1G48AKN8P23L1F1OH14TMQ,Q0598FE23F,XLD0903FETI123CI', ','))
AND l.SKU IN (SELECT CONVERT(VarChar, item) FROM Charsplitforin('12193104,12192962,10623496,9516052', ','))
AND o.OrderSourceID IN (SELECT CONVERT(INT, item) FROM Charsplitforin('14876,14876,13922', ','))
GROUP BY o.OrderSourceID,
l.SKU,
l.SellerSKU,
CONVERT(CHAR(10), PayTime, 120),
o.CustomerID,
o.OrderCode,
o.PromotionDiscountAmount
) a
GROUP BY OrderSourceID,
SKU,
SellerSKU,
paytime,
CustomerID