任务描述
需求描述

需求文档：

http://product-doc-wan.zhcxkj.com:9000/%E4%BA%A7%E5%93%81%E5%9B%9B%E7%BB%84/%E7%8E%8B%E6%99%93%E6%B4%9B/%E6%99%BA%E8%83%BD%E5%88%8A%E7%99%BB/%E6%99%BA%E8%83%BD%E5%88%8A%E7%99%BB/%E8%87%AA%E5%8A%A8%E5%88%8A%E7%99%BBsku%E7%AE%A1%E7%90%86.html

需求说明：

自动刊登SKU管理的数据
主要来源于销售自己用excel导入
另外就是老品优化的提报和新品提报的数据指定使用自动刊登和自动刊登的来源渠道

1、可以根据终审时间、添加时间（这个是指加入这个SKU自动刊登管理列表的时间）、刊登时间查询
2、可以根据系统SKU、自定义SKU、母体ID进行批量查询和单个查询


导入：两个模板导入一个只导入SKU，一个导入SKU和来源渠道关系
根据导入SKU的模块，进行数据导入操作
导入时，判断系统SKU是否是产品库的产品，是才能导入数据，不是该数据就导入失败
导入时，判断SKU是否已经存在，存在就做更新操作，记录更新前到更新后的日志


指定账号组/来源渠道：
点击按钮，提示请选择数据，选择数据后，弹出指定账号组/来源渠道弹窗
进行指定刊登账号组或者刊登的来源渠道的编辑
指定账号组/来源渠道需要记录修改编辑前和编辑后的日

刊登状态启用：
选择数据后，点击启用，弹出启用弹窗，根据选择的数据进行启用

刊登状态禁用：
选择数据后，点击禁用，根据选择的数据进行禁用
启用和禁用需要记录修改编辑日志

当SKU被禁用后，这个SKU都不在进行刊登

自动刊登SKU管理和自动刊登账号组管理的逻辑：

每4个小时定时执行扫描SKU,看SKU在自动刊登账号组里（剔除禁用的、今日刊登达到上限的（周刊登达到上限的）、类目不匹配的来源渠道），然后找到符合条件的来源渠道，进行组装listing然后进行刊登

刊登账号组列：点击账号组打开新的标签页来源渠道，显示对应账号组的来源渠道
SKU刊登详情：点击条数，打开新的标签页SKU刊登详情，显示对应SKU的刊登详情列表

验收标准
暂无
相关用例
历史记录  
  1. 2022-11-02 15:32:12, 由 罗友军 创建。
  2. 2022-11-04 19:33:58, 由 魏鹏军 完成。
    修改了 总计消耗，旧值为 "0"，新值为 "16"。
    修改了 指派给 ，旧值为 "weipengjun"，新值为 "luoyoujun"。
    修改了 实际完成，旧值为 ""，新值为 "2022-11-04 19:32:38"。
    修改了 任务状态，旧值为 "wait"，新值为 "done"。
    修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-11-04 19:33:58"。
    修改了 预计剩余，旧值为 "16"，新值为 "0"。
    修改了 由谁完成，旧值为 ""，新值为 "weipengjun"。

    亚马逊自动刊登-刊登sku管理数据库设计如下：


    --亚马逊自动刊登-刊登sku队列增量队列表
    IF OBJECT_ID('RB_Amazon_AutoPublish_PublishSync_Queue') IS NULL 
    Begin
     CREATE TABLE [dbo].RB_Amazon_AutoPublish_PublishSync_Queue(
    ID bigint IDENTITY(1,1) NOT NULL,
    CustomerID int not null,
    OrderSourceGroupID bigint not null,
    OrderSourceID int not null,
    StartTime datetime not null,
    EndTime datetime not null,
    Status int not null, 
    LastMaxID int not null
     CONSTRAINT [PK_RB_Amazon_AutoPublish_PublishSync_Queue] PRIMARY KEY CLUSTERED 
    (
    [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
      --对表注释
      EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'亚马逊自动刊登-刊登sku队列增量队列表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSync_Queue'
     
      --对表字段注释
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSync_Queue', @level2type=N'COLUMN',@level2name=N'ID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商户号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSync_Queue', @level2type=N'COLUMN',@level2name=N'CustomerID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'账号组id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSync_Queue', @level2type=N'COLUMN',@level2name=N'OrderSourceGroupID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'来源渠道id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSync_Queue', @level2type=N'COLUMN',@level2name=N'OrderSourceID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSync_Queue', @level2type=N'COLUMN',@level2name=N'StartTime'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSync_Queue', @level2type=N'COLUMN',@level2name=N'EndTime'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'执行状态：0 未执行 1 成功 2失败' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSync_Queue', @level2type=N'COLUMN',@level2name=N'Status'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后一次执行最大id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSync_Queue', @level2type=N'COLUMN',@level2name=N'LastMaxID'
    End  
    IF @@ERROR>0 PRINT 'Error:Failed to create table RB_Amazon_AutoPublish_PublishSync_Queue!'
    GO



    --亚马逊自动刊登刊登sku管理
    IF OBJECT_ID('RB_Amazon_AutoPublish_PublishSku') IS NULL 
    Begin
     CREATE TABLE [dbo].RB_Amazon_AutoPublish_PublishSku(
    ID bigint IDENTITY(1,1) NOT NULL,
    CustomerID int not null,
    PublishID bigint not null,
    Sku varchar(50) not null,
    PublishStatus int not null default(1),
    PublishDataSourceType int not null default(0),
    PublishType int not null  default(0),
    PublishTime datetime null,
    AddTime datetime not null,
    AddUserID int not null,
    AddUserName varchar(32) not null,
    UpdateTime datetime not null,
    UpdateUserName varchar(32) not null,
    UpdateUserID int not null
     CONSTRAINT [PK_RB_Amazon_AutoPublish_PublishSku] PRIMARY KEY CLUSTERED 
    (
    [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
      --对表注释
      EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'亚马逊自动刊登账号组-来源渠道' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku'
     
      --对表字段注释
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'ID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商户号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'CustomerID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'刊登队列id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'PublishID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'sku' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'Sku'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'刊登状态：0 禁用 1 启用' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'PublishStatus'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'刊登数据来源：0 excel导入 1 新品提报' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'PublishDataSourceType'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'刊登类型：0 关联sku 1 单变体' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'PublishType'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'刊登时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'PublishTime'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'AddTime'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加人id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'AddUserID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'AddUserName'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'UpdateTime'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'UpdateUserName'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku', @level2type=N'COLUMN',@level2name=N'UpdateUserID'
    End  
    IF @@ERROR>0 PRINT 'Error:Failed to create table RB_Amazon_AutoPublish_PublishSku!'
    GO


    --亚马逊自动刊登sku-账号组
    IF OBJECT_ID('RB_Amazon_AutoPublish_PublishSku_OrderSourceGroup') IS NULL 
    Begin
     CREATE TABLE [dbo].RB_Amazon_AutoPublish_PublishSku_OrderSourceGroup(
    ID bigint IDENTITY(1,1) NOT NULL,
    CustomerID int not null,
    PublishID bigint not null,
    OrderSourceGroupID bigint not null
     CONSTRAINT [PK_RB_Amazon_AutoPublish_PublishSku_OrderSourceGroup] PRIMARY KEY CLUSTERED 
    (
    [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
      --对表注释
      EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'亚马逊自动刊登sku-账号组' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSourceGroup'
     
      --对表字段注释
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSourceGroup', @level2type=N'COLUMN',@level2name=N'ID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商户号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSourceGroup', @level2type=N'COLUMN',@level2name=N'CustomerID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'刊登队列id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSourceGroup', @level2type=N'COLUMN',@level2name=N'PublishID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'账号组id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSourceGroup', @level2type=N'COLUMN',@level2name=N'OrderSourceGroupID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSourceGroup', @level2type=N'COLUMN',@level2name=N'AddTime'
    End  
    IF @@ERROR>0 PRINT 'Error:Failed to create table RB_Amazon_AutoPublish_PublishSku_OrderSourceGroup!'
    GO

    --亚马逊自动刊登sku-来源渠道
    IF OBJECT_ID('RB_Amazon_AutoPublish_PublishSku_OrderSource') IS NULL 
    Begin
     CREATE TABLE [dbo].RB_Amazon_AutoPublish_PublishSku_OrderSource(
    ID bigint IDENTITY(1,1) NOT NULL,
    CustomerID int not null,
    PublishID bigint not null,
    OrderSourceID bigint not null
     CONSTRAINT [PK_RB_Amazon_AutoPublish_PublishSku_OrderSource] PRIMARY KEY CLUSTERED 
    (
    [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
      --对表注释
      EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'亚马逊自动刊登sku-来源渠道' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSource'
     
      --对表字段注释
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSource', @level2type=N'COLUMN',@level2name=N'ID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商户号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSource', @level2type=N'COLUMN',@level2name=N'CustomerID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'刊登sku队列id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSource', @level2type=N'COLUMN',@level2name=N'PublishID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'来源渠道id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSource', @level2type=N'COLUMN',@level2name=N'OrderSourceID'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_PublishSku_OrderSource', @level2type=N'COLUMN',@level2name=N'AddTime'
    End  
    IF @@ERROR>0 PRINT 'Error:Failed to create table RB_Amazon_AutoPublish_PublishSku_OrderSource!'
    GO


  3. 2023-10-31 16:52:54, 由 周杰 关闭。
    修改了 任务状态，旧值为 "done"，新值为 "closed"。
    修改了 指派给 ，旧值为 "luoyoujun"，新值为 "closed"。
    修改了 由谁关闭，旧值为 ""，新值为 "zhoujie"。
    修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2023-10-31 16:52:54"。
    修改了 关闭原因，旧值为 ""，新值为 "done"。
所属迭代 智能中心-智能刊登迭代
所属模块 智能中心-需求池  战略独立项目   智能刊登
相关需求 自动刊登-SKU管理：导入、启用、禁用、设置刊登账号组或来源渠道、查看刊登情况
指派给 Closed 于 2023-10-31 16:52:54
任务类型 后端
任务标签 初始
开发组 仓储物流开发组
任务状态 已关闭
进度 100%
优先级 P2
关键词
抄送给
由谁创建 罗友军 于 2022-11-02 15:32:12
由谁完成 魏鹏军 于 2022-11-04 19:32:38
由谁取消 暂无
由谁关闭 周杰 于 2023-10-31 16:52:54
关闭原因 已完成
最后编辑 周杰 于 2023-10-31 16:52:54
团队 最初预计 总计消耗 预计剩余
工时信息
最初预计 16工时
总计消耗 16工时
预计剩余 0工时
预计开始 0000-00-00
实际开始 2022-11-04
截止日期 2022-11-04