TASK#55301 按订单维度统计分析利润 / 智能中心-智能选品迭代

任务描述

需求描述

需求背景

因现在智能运营方式逐步增加，不知订单下单时都参与了哪些运营策略，以及整体的利润情况

需求内容：

按订单+产品维度进行统计：以支付时间按月分表保存。从9.1号开始统计

注意点：

1、订单：

是否使用优惠券、是否使用专享折扣、是否使用层级促销：根据订单表里的字段【PromotionId】与订单执行的各个策略ID匹配，如果匹配到了，则标记为【是】，反之为【否】。

2、调价：一个订单会跨多个策略的折扣时间，则只取多个策略里最新的一条策略。

3、广告：支持存多个策略ID信息。

4、层级促销：支持存在多个策略ID信息。

5、专享折扣：策略ID只展示系统提交成功，且亚马逊后台状态为【已计划】或【Scheduled】的策略数据

6、优惠券：策略ID只展示系统提交成功，且亚马逊后台状态为【有效】或【已过期】的策略数据

验收标准

运营监控：监控出单由哪些运营手段组成；监控哪些运营手段比较有效。

Listing利润率计算：与订单相关表联表统计，可监控利润率。

历史记录

2023-09-20 11:46:49, 由 罗友军 创建。
2023-09-28 19:20:15, 由 钟喜旺 完成。
修改了 总计消耗，旧值为 "0"，新值为 "40"。
修改了 指派给 ，旧值为 "zhongxiwang"，新值为 "luoyoujun"。
修改了 实际完成，旧值为 ""，新值为 "2023-09-28 19:14:00"。
修改了 任务状态，旧值为 "wait"，新值为 "done"。
修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2023-09-28 19:20:15"。
修改了 预计剩余，旧值为 "37"，新值为 "0"。
修改了 由谁完成，旧值为 ""，新值为 "zhongxiwang"。
开发报告规范：
1、说明修改发布项目工程和服务配置项
发布项目：
AmazonWork

项目：AmazonWork

新增服务

服务1: 按订单维度统计分析利润

服务名：ProfitRateSyncJob

频率: 一小时一次
```
{"ServiceName":"ProfitRateSyncJob","Params":{},"AssemblyFullName":" Erp.Amazon.Worker","TypeFullName":"Erp.Amazon.Worker.CronJob.Common.ProfitRateSyncJob","ExecMethod":"Execute","IsMultiThread":false,"TimeOut":0} 
```
2、备注新增修改数据库表或字段
mysql choose 库添加一张表，在文档说明中

3、截图核心代码逻辑

4、截图自测业务流

5、补充其他注意事项
2023-09-28 19:28:37, 由 钟喜旺 添加备注。

## 生成表格sql

```sql
-- AmazonOrderScore_yyyymm 表格
CREATE TABLE `AmazonOrderScore` (
`Id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`OrderCode` VARCHAR(50) DEFAULT NULL COMMENT '订单号',
`OrderSourceId` INT(11) DEFAULT NULL COMMENT '来源渠道ID',
`SiteCode` VARCHAR(10) DEFAULT NULL COMMENT '站点',
`PayTime` DATETIME DEFAULT NULL COMMENT '支付时间',
`VipType` INT(11) DEFAULT NULL COMMENT 'B2C会员VIP类型（订单维度）',
`VipDiscount` DOUBLE DEFAULT NULL COMMENT 'B2C会员VIP折扣金额（订单维度）',
`Sku` VARCHAR(50) DEFAULT NULL COMMENT '系统SKU',
`Asin` VARCHAR(100) DEFAULT NULL COMMENT 'ASIN',
`SellerSku` VARCHAR(512) DEFAULT NULL COMMENT '渠道SKU',
`IsFBA` BIT(1) DEFAULT NULL COMMENT '是否FBA',
`ProductSalePrice` DOUBLE DEFAULT NULL COMMENT '产品价格（ProductSalePrice = GroupSKU != "" ?GroupProductPrice : ProductPrice）',
`ProductSaleNum` INT(11) DEFAULT NULL COMMENT '购买数量（ProductSaleNum = GroupSKU != ""? GroupProductNum :(ProductNum+ProductNumUpdate)',
`PromotionDiscount` DOUBLE DEFAULT NULL COMMENT 'Promotion促销折扣金额',
`PromotionId` VARCHAR(2000) DEFAULT NULL COMMENT 'Promotion促销编号（JSON数组，层级促销/优惠券）',
`IsOrderCoupon` BIT(1) DEFAULT NULL COMMENT '订单是否使用优惠券',
`IsOrderExclusiveDiscount` BIT(1) DEFAULT NULL COMMENT '订单是否使用专享折扣',
`IsOrderPercentageOff` BIT(1) DEFAULT NULL COMMENT '订单是否使用层级促销',
`OnShelfMode` INT(11) DEFAULT NULL COMMENT '上架方式',
`PublishTime` DATETIME DEFAULT NULL COMMENT '刊登时间',
`AddTime` DATETIME DEFAULT NULL COMMENT '添加时间',
`IsAdjustPrice` BIT(1) DEFAULT NULL COMMENT '是否调价（支付时间在Listing促销时间范围内）',
`AdjustPriceStrategyId` BIGINT(20) DEFAULT NULL COMMENT '调价策略ID',
`AdjustPriceStrategyName` VARCHAR(100) DEFAULT NULL COMMENT '调价策略名称',
`AdjustPriceTime` DATETIME DEFAULT NULL COMMENT '调价时间',
`AdjustPriceDiscountSalesPrice` DOUBLE DEFAULT NULL COMMENT '调价折后价',
`IsBuryWord` BIT(1) DEFAULT NULL COMMENT '是否埋词（在支付时间前7天内是否Listing埋词）',
`BuryWordType` INT(11) DEFAULT NULL COMMENT '埋词类型（通用/界面/节日等）',
`BuryWordStrategyId` BIGINT(20) DEFAULT NULL COMMENT '埋词策略ID',
`BuryWordStrategyName` VARCHAR(100) DEFAULT NULL COMMENT '埋词策略名称',
`BuryWordTime` DATETIME DEFAULT NULL COMMENT '埋词时间',
`BuryWordParseWordTypes` VARCHAR(100) DEFAULT NULL COMMENT '埋入词来源类型（多个以逗号分隔）',
`IsCoupon` BIT(1) DEFAULT NULL COMMENT '是否发放优惠券（支付时间在Listing优惠券时间范围内）',
`CouponType` INT(11) DEFAULT NULL COMMENT '优惠券类型（通用/节日等）',
`CouponStrategyId` BIGINT(20) DEFAULT NULL COMMENT '优惠券策略ID',
`CouponStrategyName` VARCHAR(100) DEFAULT NULL COMMENT '优惠券策略名称',
`CouponTime` DATETIME DEFAULT NULL COMMENT '优惠券发放时间',
`CouponDiscountType` VARCHAR(50) DEFAULT NULL COMMENT '优惠券折扣类型',
`CouponDiscountValue` DOUBLE DEFAULT NULL COMMENT '优惠券折扣点（浮点数表示）',
`IsAdvertise` BIT(1) DEFAULT NULL COMMENT '是否打过广告（支付时间前Listing打过广告）',
`AdvertiseStrategyIds` VARCHAR(2000) DEFAULT NULL COMMENT '广告策略ID（多个以逗号分隔）',
`AdvertiseStrategyNames` VARCHAR(2000) DEFAULT NULL COMMENT '广告策略名称（多个以逗号分隔）',
`AdvertiseTime` DATETIME DEFAULT NULL COMMENT '最新推送广告时间',
`IsExclusiveDiscount` BIT(1) DEFAULT NULL COMMENT '是否专享折扣（支付时间在Listing专享折扣时间范围内）',
`ExclusiveDiscountStrategyId` BIGINT(20) DEFAULT NULL COMMENT '专享折扣策略ID',
`ExclusiveDiscountStrategyName` VARCHAR(100) DEFAULT NULL COMMENT '专享折扣策略名称',
`ExclusiveDiscountTime` DATETIME DEFAULT NULL COMMENT '专享折扣推送时间',
`ExclusiveDiscountValue` DOUBLE DEFAULT NULL COMMENT '专享折扣比例（浮点数表示）',
`IsPercentageOff` BIT(1) DEFAULT NULL COMMENT '是否层级促销（支付时间在Listing 层级促销时间范围内）',
`PercentageOffIds` VARCHAR(2000) DEFAULT NULL COMMENT '层级促销ID（多个以逗号分隔）',
`PercentageOffStrategyIds` VARCHAR(2000) DEFAULT NULL COMMENT '层级促销策略ID（多个以逗号分隔）',
`PercentageOffStrategyNames` VARCHAR(2000) DEFAULT NULL COMMENT '层级促销策略名称（多个以逗号分隔）',
`PercentageOffDiscountValue` VARCHAR(2000) DEFAULT NULL COMMENT '层级促销折扣比例（通过字段ProductSaleNum 取对应层级的折扣比例，浮点数表示，多个以逗号分隔）',
`PercentageOffTime` DATETIME DEFAULT NULL COMMENT '最新层级促销推送时间',
PRIMARY KEY (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='Amazon订单评分表';

create unique index `udx_OrderCode_OSId_SellerSku `
on amazonorderscore (OrderCode, OrderSourceId, SellerSku)
comment '唯一键：OrderCode，OrderSourceId，SellerSku';
```

## 表关系

```sql
-- RB_OrderInfo 和 RB_OrderList 的关系
SELECT *
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
WHERE o.CustomerID=1
AND o.IsPay = 1
-- OR IsFBAOrder = 1 --待确认
AND Len(sku) > 0 --排除无sku
AND o.IsStockOrder = 0 --排除备库订单
AND o.OrderStatus <> 2 --排除作废订单
AND s.OrderSourceType=1 --亚马逊平台

```

## 统计逻辑

**统计调价**
```sql
// 通过来源渠道ID+渠道SKU查询调价上架队列表过去15天内的成功记录
-- 调价
db.getSiblingDB("Amazon_AdjustPrice").getCollection("AdjustPriceQueue").find({
"Status":2
});

```

```c#
// 判断订单支付时间在调价促销时间范围内的记录是否存在
DiscountStartTime <= PayTime && DiscountEndTime >= PayTime;
```

**专享折扣**

```javascript
//通过来源渠道ID+渠道SKU查询专享折扣推送队列表过去15天内结果状态为已成功或已过期的记录

// 已过期判断：字段Msg有以下内容：Expired 已过期状态：成功 =9
db.getSiblingDB("Amazon_AdjustPrice").getCollection("ExclusiveDiscountDetail").find({
"Status":9
});

// 判断订单支付时间在专享折扣生效时间范围内的记录是否存在？

dataModel.PlanStartTime < param.PayTime && dataModel.PlanEndTime > param.PayTime;

```
映射字段

| ExclusiveDiscountDetail| 目标表|
|-------|---------|
|ExclusiveDiscountTime|SubmitTime|
|ExclusiveDiscountStrategyId|StrategyId|
|ExclusiveDiscountValue|DiscountValue|
|ExclusiveDiscountStrategyName|ExclusiveDiscountStrategy(msyql)表.Name|

**广告**

```javascript

// 通过来源渠道ID+渠道SKU查询广告推送队列表（通用策略和节日策略）的成功记录
db.getSiblingDB("zhcxkj_autochoose").getCollection("AmazonAdvertisingStrategyQueueNew").find({
"Status": 2
});
db.getSiblingDB("zhcxkj_autochoose").getCollection("AmazonAdvertisingQueueNew").find({
"Status": 2
});

// 广告的策略名称和上面两张表的策略id关联，策略名称配置在表内
db.getSiblingDB("zhcxkj_amazon").Dictionary.find({"TypeName":"AdvertisingName"});

```

**优惠券**

```javascript
db.getSiblingDB("zhcxkj_autochoose").getCollection("AutoCouponDetail").find({
"Status": 1
});
// status 1 有效 2表示过期

//判断订单支付时间在优惠券生效时间范围内的记录是否存在？
AutoCouponDetail.StartDate <= PayTime && AutoCouponDetail.EndDate >= param.PayTime && PercentageOffDetail.End <= param.PayTime;

// 最新层级促销推送时间
PercentageOffTime
2023-10-20 09:17:56, 由 王地福 添加备注。

标识专享折扣--待产品确认）
2023-10-31 16:39:04, 由周杰关闭。
修改了 任务状态，旧值为 "done"，新值为 "closed"。
修改了 指派给 ，旧值为 "luoyoujun"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "zhoujie"。
修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2023-10-31 16:39:04"。
修改了 关闭原因，旧值为 ""，新值为 "done"。

基本信息
任务的一生

所属迭代	智能中心-智能选品迭代
所属模块	/
相关需求	按订单维度统计分析利润
指派给	Closed 于 2023-10-31 16:39:04
任务类型	后端
任务标签	初始
开发组	仓储物流开发组
任务状态	已关闭
进度	100%
优先级	P1
关键词
抄送给

由谁创建	罗友军于 2023-09-20 11:46:49
由谁完成	钟喜旺于 2023-09-28 19:14:00
由谁取消	暂无
由谁关闭	周杰于 2023-10-31 16:39:04
关闭原因	已完成
最后编辑	周杰于 2023-10-31 16:39:04

团队	最初预计	总计消耗	预计剩余

工时信息

最初预计	37工时
总计消耗	40工时
预计剩余	0工时
预计开始	2023-09-20
实际开始	2023-09-28
截止日期	2023-09-30