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

第一步：

将所有在线Listing全量全新同步 -- 重新执行同步本地在线状态的Listing，以便更新实际在线状态(已执行2022.11.05) INSERT IGNORE INTO m_mercadolibre_listing_productid(CustomerId,OrderSourceId,ProductId,`Status`,`Date`,Time,Creator,CreatorId,CreationTime,SiteStatus,GlobalStatus,MarketStatus,Remark) select 1,a.OrderSourceId,a.ProductId,0,20221105,NOW(),'系统管理员1105',0,NOW(),'normal',0,0 ,'重新同步在线Listing1105' from ( select DISTINCT a.OrderSourceId,a.ProductId from m_mercadolibre_listing_site a where a.CustomerId=1 and a.OrderSourceId>0 and a.`Status`='Active' ) as a

二、不等同步结果，先测试系统中部分数据，如下

-- 同一地方站点有相同的ProductId时，删除时间较早的记录（11.05已执行）
delete from m_mercadolibre_listing_site where id in ( select id from (
select a.id,a.SiteId,a.ProductId,a.LastModificationTime from m_mercadolibre_listing_site a inner join (
select OrderSourceId,SiteId,ProductId,Min(LastModificationTime) Mintime from m_mercadolibre_listing_site where CustomerID=1
GROUP BY OrderSourceId,SiteId,ProductId
HAVING COUNT(1)>1 ) b on a.OrderSourceId=b.OrderSourceId and a.SiteId=b.SiteId and a.ProductId=b.ProductId and a.LastModificationTime=b.Mintime) as tt)

1、优先同步促销候选的CBTID进行同步

2、获取数据库：SELECT DISTINCT OrderSourceId,ProductId FROM `m_mercadolibre_discount_listing` WHERE CustomerId=1 AND `Status`='candidate' 

3、需要去重，剔除重复数据

已执行

 -- 把促销加入同步队列表

INSERT IGNORE INTO m_mercadolibre_listing_productid(CustomerId,OrderSourceId,ProductId,`Status`,`Date`,Time,Creator,CreatorId,CreationTime,SiteStatus,GlobalStatus,MarketStatus,Remark)
select 1,a.OrderSourceId,a.ProductId,0,20221108,NOW(),'系统管理员1108',0,NOW(),'normal',0,0,'促销20221108'  from (
SELECT DISTINCT OrderSourceId,ProductId FROM `m_mercadolibre_discount_listing` WHERE CustomerId=1 AND `Status`='candidate') as a

步骤二 已再执行完成

11月16日，继续执行一次数据可促销数据同步

INSERT IGNORE INTO m_mercadolibre_listing_productid(CustomerId,OrderSourceId,ProductId,`Status`,`Date`,Time,Creator,CreatorId,CreationTime,SiteStatus,GlobalStatus,MarketStatus,Remark)
select 1,a.OrderSourceId,a.ProductId,0,20221116,NOW(),'系统管理员1116',0,NOW(),'normal',0,0,'促销20221116'  from (
SELECT DISTINCT OrderSourceId,ProductId FROM `m_mercadolibre_discount_listing` WHERE CustomerId=1 AND `Status`='candidate') as a

INSERT IGNORE INTO m_mercadolibre_listing_productid(CustomerId,OrderSourceId,ProductId,`Status`,`Date`,Time,Creator,CreatorId,CreationTime,SiteStatus,GlobalStatus,MarketStatus,Remark)
select 1,a.OrderSourceId,a.ProductId,0,20221119,NOW(),'系统管理员1119',0,NOW(),'normal',0,0,'促销状态修复20221119'  from (
SELECT
DISTINCT
ItemId
FROM
  pp_phoenixplan_mercadolibre_queue  
WHERE
  CustomerId=1 
and MarketId=70
   AND `Status` = 'failure' 
AND StrategyType=5
AND FailureReason LIKE'%Item status is not allowed%'
) as a