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

1、记录阿里云日志

2.1、

-- 策略320平衡库存满足条件未进行队列数据
select
a.OrderSourceId,
a.SellerSku'渠道sku',
a.ProductId'产品ID',
a.Sku,
(select SUM(case when date>date_sub(curdate(),interval 90 day) THEN sd.Sales ELSE 0 END) from m_shopeev2_sale_day_summary AS sd WHERE a.CustomerId=sd.CustomerId and a.OrderSourceId=sd.OrderSourceId and a.SellerSku=sd.SellerSku and a.Sku=sd.Sku) as '渠道SKU近90天销量',
-- sd.Sales'销量',
a.Inventory'在线可售库存',
a2.DynamicInventory as '本地仓库动态库存',
a2.LastModificationTime
FROM m_shopeev2_listing_sub as a
INNER JOIN p_product_inventory as a2
ON a.CustomerId=a2.CustomerId and a.Sku=a2.sku
WHERE a.CustomerId=1 -- 固定
and a.IsGlobal = true-- 固定 去掉全球产品
and a.Status='NORMAL'-- 在线
and a.OrderSourceId in (select ItemValue from pp_replenishment_strategy_targetitem WHERE StrategyId = 320 and ItemValue in (select b.OrderSourceId from sys_ordersource b 
JOIN sys_ordersource_authorization c 
ON b.OrderSourceId = c.OrderSourceId 
WHERE b.IsActive = 1 AND b.IsDelete = 0 AND c.IsActive = 1 AND b.OrderSourceType = 97 AND b.OrderSourceName NOT like '%【%'))
and a.IsMain = false
and a.Inventory > 0
and a2.DynamicInventory < 2
and a.Inventory <> a2.DynamicInventory
-- 过滤否定策略
and a.SKU not in (select ItemValue from pp_negation_strategy_applyitem WHERE IsDeleted = 0 and IsEnabled = 1 and StrategyId in (
select Id from pp_negation_strategy WHERE CustomerId =1 and MarketId = 97 and IsDeleted = 0 and IsEnabled = 1 and Type like '%6%' and ApplyType = 1))
and a.SellerSKU not in (select ItemValue from pp_negation_strategy_applyitem WHERE IsDeleted = 0 and IsEnabled = 1 and StrategyId in (
select Id from pp_negation_strategy WHERE CustomerId =1 and MarketId = 97 and IsDeleted = 0 and IsEnabled = 1 and Type like '%6%' and ApplyType = 3))
and a.ProductId not in (select ItemValue from pp_negation_strategy_applyitem WHERE IsDeleted = 0 and IsEnabled = 1 and StrategyId in (
select Id from pp_negation_strategy WHERE CustomerId =1 and MarketId = 97 and IsDeleted = 0 and IsEnabled = 1 and Type like '%6%' and ApplyType = 4))
and TIMESTAMPDIFF(HOUR,a2.LastModificationTime, NOW()) < 72 + 8 -- 3天内动态库存变更未进入队列
and TIMESTAMPDIFF(HOUR,a2.LastModificationTime, NOW()) > 2 + 8 -- 2小时外动态库存变更未进入队列

2.2

SELECT
  r.StrategyId,
  r.OrderSourceId,
  r.ProductId,
  r.SellerSku,
  r.Sku,
  r.`Status`,
  r.Inventory AS 修改后可售数,
  r.OriginalInventory AS 原可售数,
  w.DynamicInventory AS SKU最新动态库存合计,
  r.Remark AS 补货队列日志,
  r.FailureReason,
  date_add( w.LastModificationTime, INTERVAL 8 HOUR ) AS 动态库存最新更新时间,
  date_add( r.lastReplenishmentTime , INTERVAL 8 HOUR ) AS 补货队列最新执行时间,
  TIMESTAMPDIFF(hour, w.LastModificationTime, r.lastReplenishmentTime) AS 补货执行时间差
FROM
  p_product_inventory w
  JOIN (
SELECT 
r.CustomerId,
r.StrategyId,
r.OrderSourceId,
r.ProductId,
r.SellerSku,
r.OriginalInventory,
r.Inventory ,
r.Remark,
r.FailureReason,
r.Sku,
r.`Status`,
r.LastModificationTime as lastReplenishmentTime 
FROM pp_replenishment as r
JOIN (
SELECT
  Max(Id) as Id
  FROM
    pp_replenishment 
  WHERE
    CustomerId = 1 
    AND MarketId = 97 
    AND StrategyId IN (328,320)-- 设置补货策略
    AND Time>= DATE_SUB(NOW(),interval 72 HOUR)-- 可以设置成比当前查询时间差3天
  GROUP BY
    CustomerId,
    StrategyId, 
    OrderSourceId,
    ProductId,
    SellerSku,
    Sku) p
ON r.Id = p.Id
) r ON w.CustomerId = r.CustomerId 
  AND w.Sku = r.Sku 
WHERE
  w.LastModificationTime >= DATE_ADD(CURDATE(),INTERVAL -56 HOUR) -- 设置48小时的数据
  and w.LastModificationTime <= DATE_ADD(NOW(),INTERVAL -10 HOUR)-- 设置比当前时间晚2个小时
  -- AND DATE_ADD( w.LastModificationTime, INTERVAL 2 HOUR ) < r.lastReplenishmentTime -- 最新补货时间超过最近动态库存同步时间2小时
  AND w.LastModificationTime <= r.lastReplenishmentTime

3.1、

地方店铺多属性产品可售数和Color/Size赋值问题排查：渠道编号14444、平台编号9899691969，平台返回数据结构赋值问题。（已处理）

修复结构后：

因为全球站与地方站代码未公用，所以需要排查全球站多属性产品可售数和Color/Size数据结构使用问题。

同时全球站和地方站的标准产品可能与多属性产品结构存在差异，也需要一起确认是否有赋值问题。

解决结果：排查全球站多属性产品可售数赋值无问题，Color/Size赋值问题同地方站一样（已处理）； 检查全球/地方站标准产品可售数和Color/Size赋值均无问题。

可售数：全球站可售数使用stock_info模型赋值，地方站可售数使用stock_info_v2模型赋值；

Color/Size：全球站多属性产品使用GlobalVariations模型，地方站多属性产品使用Variations模型。

SVN代码提交记录：

3.2、

补货问题排查1：渠道编号14443，系统SKU11936009，同步到本地仓可用库存表的可用库存总和为8，时间为2022-11-12 09:20:12，阿里云补货日志记录程序获取的库存总和为0，时间为2022-11-12 11:03:36，核对ERP库存总和为8，结果是程序获取库存数有误。

解决方案：代码中除Ebay平台之外的所有平台均是调用ERP接口获取库存数，代码修改为从本地仓可用库存表获取库存数。

SVN提交代码记录：

3.3、因与其他业务有冲突代码已回撤

SVN提交代码记录：