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

1、新增导出类型“提报上限自动测算”，72。

2、MongoDB增加zhcxkj_autochoose》Erp_ProductDevelop_Sku，提报+周期+sku维度存储记录

        UniqueId=唯一键，ApplicationNumber-CycleTime-Sku
        DevelopId=销售提报id
        ApplicationNumber=销售提报编号
        CycleTime=周期，周度
        Sku=Sku
        ProductName=产品名称
        AddTime=入库时间
        UpdateTime=修改时间
        CurrentStatus=状态，Completed-开发成功，开发失败（退回+驳回待编辑+作废+开发失败）
        CurrentStatusDescription=状态描述
        DevelopStatu=开发状态，1-成功，2-失败
        BrowsePathId=类目id
        BrowsePathName=类目名称
        FirstId { get; set; }
        FirstName { get; set; }
        SecondId { get; set; }
        SecondName { get; set; }
        ThreeId { get; set; }
        ThreeName { get; set; }
        Td_OrderNum=30天出单数量
        Td_SaleNum=30天销量
        Td_Incomes=30天销售额
        Td_Profit=30天利润
        Nd_OrderNum=90天出单数量
        Nd_SaleNum=90天销量
        Nd_Incomes=90天销售额
        Nd_Profit=90天利润
        CheckTime=sku终审时间
        ListingNum=在线listing数量

3、数据源

3.1、销售提报：RB_Product_Development

SELECT top 100 * FROM [dbo].[RB_Product_Development](nolock) WHERE [PersonType] = N'3' AND [AddTime] >= N'2023-12-18' AND [AddTime] < N'2024-06-19' 

3.2、销售提报关联的sku：RB_ProductDevelopment_Related_Product

SELECT * FROM [dbo].[RB_ProductDevelopment_Related_Product] WHERE [ProductDevelopmentId] = N'427547'

select  a.SKU
,SUM(case WHEN DATEDIFF(day, a.PayTime, d.CheckTime) <= 30 THEN a.Profits ELSE 0 END) as TD_Profits
,SUM(case WHEN DATEDIFF(day, a.PayTime, d.CheckTime) <= 30 THEN 1 ELSE 0 END) as TD_OrderNum
,SUM(case WHEN DATEDIFF(day, a.PayTime, d.CheckTime) <= 30 THEN a.Incomes ELSE 0 END) as TD_Incomes
,SUM(case WHEN DATEDIFF(day, a.PayTime, d.CheckTime) <= 30 THEN a.ProductNum + a.ProdutNumUpdate ELSE 0 END) as TD_SaleNum
,SUM(case WHEN DATEDIFF(day, a.PayTime, d.CheckTime) <= 90 THEN a.Profits ELSE 0 END) as ND_Profits
,SUM(case WHEN DATEDIFF(day, a.PayTime, d.CheckTime) <= 90 THEN 1 ELSE 0 END) as ND_OrderNum
,SUM(case WHEN DATEDIFF(day, a.PayTime, d.CheckTime) <= 90 THEN a.Incomes ELSE 0 END) as ND_Incomes
,SUM(case WHEN DATEDIFF(day, a.PayTime, d.CheckTime) <= 90 THEN a.ProductNum + a.ProdutNumUpdate ELSE 0 END) as ND_SaleNum
from rb_report_ordersettlement (nolock) a
inner join (select '2030919' as Id union all
select '8172457' as Id union all
select '11386855' as Id union all
select '15462319' as Id union all
select '18669884' as Id union all
select '11520651' as Id union all
select '18729656' as Id union all
select '17222240' as Id) b on a.SKU=b.Id
left join rb_warehouse (nolock) c on a.CustomerID=c.CustomerID AND a.WareHouseID = c.ID
left join rb_product_admin (nolock) d on a.CustomerID=d.CustomerID and a.SKU=d.SKU
left join rb_businesstable_deletedhistory (nolock) e on a.CustomerID = e.CustomerID and a.ID = e.BusinessDeletedId
where a.CustomerID=1 and a.OrderCode != '' and c.WareHouseType = 1 and e.ID IS NULL
group by a.SKU 

4、自测数据

5、执行情况，CycleTime控制执行的周期
db.getCollection("HotColdDictionary").find({"TypeName" : "Erp_ProductDevelop_Sku:RunServiceAsync"}).limit(100).skip(0)

2、核心代码

3、导出的数据

4、调整数量会多一点点，取的提报上限总数+200，不然提报的总数就达不到最大值