任务描述
需求描述
需求背景：目前智能刊登的SKU未做人工审核，可能存在账号风险，需要通过人工审核
需求实现：
增加一个审核记录表，以SKU+账号组维度保存，需要包括SKU，账号组，来源渠道（方便关联用户权限），所有从Amazon自动刊登SKU管理及原老品优化池创建的记录（没有刊登过），都需要先走审核流程，创建到审核表，状态为未审核。审核通过后变成审核通过状态，审核不通过则变成审核不通过状态。
符合以下条件的SKU不创建审核记录（不刊登）：
暂时缺货，仿牌，禁售（全站点禁售），停产
一、前端审核页面
从审核记录表获取审核记录展示（只展示该销售人员所负责的来源渠道的关联记录），默认展示未审核记录，页面字段如图所示。
字段取值定义：
添加时间：导入到SKU管理表的时间
审核状态&审核人员&审核时间：该条记录的审核状态和具体操作人员，时间
该SKU上一次审核通过时间：该SKU在审核记录表中最近一次审核通过的时间
该SKU审核通过次数：该SKU在审核表中记录的审核通过次数
搜索条件：
1.时间筛选框下拉项包括，添加时间、上一次审核通过时间
2.系统SKU下拉项包括系统SKU，自定义SKU，母体ID
3.审核状态包括 全部、未审核、审核通过、审核不通过
4.支持批量搜索，批量搜索条件包括：系统SKU，自定义SKU，母体ID，账号组
操作按钮：
1.审核按钮下拉项包括：选中项审核，当前条件审核。这两个操作都需要确认审核状态，状态必填，备注选填。
2.批量设置站点允许审核人员在审核过程中批量修改SKU上架的站点，未勾选的站点对应的来源渠道不能上线，默认全选。站点必须选一个，备注选填
3.列表操作下拉项
包括审核及站点设置功能，页面与批量操作一致。
4.当SKU为母体时，显示母体ID，颜色为蓝色，鼠标悬停显示所有子SKU列表
数据处理要求：
1.所有审核以SKU+账号组维度审核，即一个销售人员审核了该SKU+账号组，则其他销售人员不再审核。
2.如果以母体形式刊登，则只需要审核一次母体，即审核单中只有一条母体的审核单。
3.正式上线后，当前未刊登的记录都需要初始化到审核记录表。
4.符合以下条件的SKU，需要在产品信息列中标红对应信息，例如尺寸超标则尺寸信息标红，例如重量超标则重量信息标红。
--长宽高（单个（套）包装尺寸），其中有一项大于等于60；
--长宽高相加（单个（套）包装尺寸），大于等于90；
--毛重大于等于2000G
5.页面加载数据时需要过滤暂时缺货，仿牌，禁售（全站点禁售），停产
二、刊登流程调整
1.无论是用户通过导入或者是其他来源进入到自动刊登SKU管理表的数据，在队列的执行中，都需要先创建审核记录。
2.刊登资料组装的队列消费服务修改为从审核记录表读取数据，只有审核通过的才能进行刊登流程。
验收标准
暂无
1.修改ERPWorker 增加AmazonAutoPublishCheckSkuJob服务
2.数据库
--亚马逊自动刊登SKU审核页面
IF OBJECT_ID('RB_Amazon_AutoPublish_CheckSku') IS NULL
Begin
CREATE TABLE [dbo].RB_Amazon_AutoPublish_CheckSku(
ID bigint IDENTITY(1,1) NOT NULL,
CustomerID int not null,
Sku varchar(50) not null,
CheckPublishID bigint not null,
OrderSourceGroupId bigint not null,
CheckStatus int not null default(0),
CheckTime datetime null,
CheckUserID int null,
CheckUserName varchar(32) null,
PublishType int not null default(0),
PublishTime datetime null,
PushStatus int not null default(0),
Remarks nvarchar(500) NULL,
PublishSkuID bigint 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_CheckSku] 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_CheckSku'
--对表字段注释
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_CheckSku', @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_CheckSku', @level2type=N'COLUMN',@level2name=N'CustomerID'
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_CheckSku', @level2type=N'COLUMN',@level2name=N'Sku'
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_CheckSku', @level2type=N'COLUMN',@level2name=N'CheckPublishID'
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_CheckSku', @level2type=N'COLUMN',@level2name=N'OrderSourceGroupId'
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_CheckSku', @level2type=N'COLUMN',@level2name=N'CheckStatus'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审核时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_CheckSku', @level2type=N'COLUMN',@level2name=N'CheckTime'
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_CheckSku', @level2type=N'COLUMN',@level2name=N'CheckUserID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审核人名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_CheckSku', @level2type=N'COLUMN',@level2name=N'CheckUserName'
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_CheckSku', @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_CheckSku', @level2type=N'COLUMN',@level2name=N'PublishTime'
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_CheckSku', @level2type=N'COLUMN',@level2name=N'PushStatus'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_CheckSku', @level2type=N'COLUMN',@level2name=N'Remarks'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_CheckSku', @level2type=N'COLUMN',@level2name=N'AddTime'
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_CheckSku', @level2type=N'COLUMN',@level2name=N'PublishSkuID'
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_CheckSku', @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_CheckSku', @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_CheckSku', @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_CheckSku', @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_CheckSku', @level2type=N'COLUMN',@level2name=N'UpdateUserID'
End
IF @@ERROR>0 PRINT 'Error:Failed to create table RB_Amazon_AutoPublish_CheckSku!'
GO
--亚马逊自动刊登SKU审核-刊登来源渠道详情
IF OBJECT_ID('RB_Amazon_AutoPublish_CheckSku_OrderSource') IS NULL
Begin
CREATE TABLE [dbo].RB_Amazon_AutoPublish_CheckSku_OrderSource(
ID bigint IDENTITY(1,1) NOT NULL,
CustomerID int not null,
CheckPublishID bigint not null,
OrderSourceID int not null,
AddTime datetime not null,
UpdateTime datetime not null
CONSTRAINT [PK_RB_Amazon_AutoPublish_CheckSku_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_CheckSku_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_CheckSku_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_CheckSku_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_CheckSku_OrderSource', @level2type=N'COLUMN',@level2name=N'CheckPublishID'
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_CheckSku_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_CheckSku_OrderSource', @level2type=N'COLUMN',@level2name=N'AddTime'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_CheckSku_OrderSource', @level2type=N'COLUMN',@level2name=N'UpdateTime'
End
IF @@ERROR>0 PRINT 'Error:Failed to create table RB_Amazon_AutoPublish_CheckSku_OrderSource!'
GO
--亚马逊自动刊登SKU审核详情
IF OBJECT_ID('RB_Amazon_AutoPublish_CheckSku_Detail') IS NULL
Begin
CREATE TABLE [dbo].RB_Amazon_AutoPublish_CheckSku_Detail(
ID bigint IDENTITY(1,1) NOT NULL,
CustomerID int not null,
Sku varchar(50) not null,
LastCheckTime datetime not null,
CheckPassCount int not null default(0),
CheckNoPassCount int not null default(0),
AddTime datetime not null,
UpdateTime datetime not null
CONSTRAINT [PK_RB_Amazon_AutoPublish_CheckSku_Detail] 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_CheckSku_Detail'
--对表字段注释
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_CheckSku_Detail', @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_CheckSku_Detail', @level2type=N'COLUMN',@level2name=N'CustomerID'
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_CheckSku_Detail', @level2type=N'COLUMN',@level2name=N'Sku'
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_CheckSku_Detail', @level2type=N'COLUMN',@level2name=N'LastCheckTime'
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_CheckSku_Detail', @level2type=N'COLUMN',@level2name=N'CheckPassCount'
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_CheckSku_Detail', @level2type=N'COLUMN',@level2name=N'CheckNoPassCount'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_CheckSku_Detail', @level2type=N'COLUMN',@level2name=N'AddTime'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RB_Amazon_AutoPublish_CheckSku_Detail', @level2type=N'COLUMN',@level2name=N'UpdateTime'
End
IF @@ERROR>0 PRINT 'Error:Failed to create table RB_Amazon_AutoPublish_CheckSku_Detail!'
GO
tableinfo RB_Amazon_AutoPublish_CheckSku
--drop table RB_Amazon_AutoPublish_CheckSku
tableinfo RB_Amazon_AutoPublish_CheckSku_OrderSource
tableinfo RB_Amazon_AutoPublish_CheckSku_Detail
3.核心代码