任务描述
需求描述
需求背景：
eBay 由于业务变动及转移了很多账号到直发，有几百万的listing需要更改销售人员名字，系统的当前条件只能修改5万条一次，修改耗时，且业务需要在3月2日前处理好，用于核算业绩
需求内容
查询表格中的账号，所有物品所在地是中国的在线listing，将listing的销售人员，全部改为表格中，每个账号对应的销售人员
直接更新数据库数据，最好在今天（3月1日）下班前处理完成
验收标准
暂无
历史记录
- 2022-03-01 18:19:02, 由 罗友军 创建。
-
2022-03-11 09:25:04, 由 huangtiekun 完成。
修改了 总计消耗，旧值为 "0"，新值为 "24"。
修改了 实际完成，旧值为 ""，新值为 "2022-03-04 18:00"。
修改了 任务状态，旧值为 "wait"，新值为 "done"。
修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-03-11 09:25:04"。
修改了 预计剩余，旧值为 "8"，新值为 "0"。
修改了 由谁完成，旧值为 ""，新值为 "huangtiekun"。
-
2022-04-06 20:24:55, 由 zhengqiao 关闭。
修改了 任务状态，旧值为 "done"，新值为 "closed"。
修改了 指派给 ，旧值为 "huangtiekun"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "zhengqiao"。
修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2022-04-06 20:24:55"。
修改了 关闭原因，旧值为 ""，新值为 "done"。
| 所属迭代 | 【智汇】产品部 3月非正常更新版本（2022年） |
|---|---|
| 所属模块 | / |
| 相关需求 | eBay 批量修改账号listing销售人员--处理数据（无需发版） |
| 指派给 | Closed 于 2022-04-06 20:24:55 |
| 任务类型 | 后端 |
| 任务标签 | 初始 |
| 开发组 | |
| 任务状态 | 已关闭 |
| 进度 | 100% |
| 优先级 | P2 |
| 关键词 | |
| 抄送给 |
| 由谁创建 | 罗友军 于 2022-03-01 18:19:02 |
|---|---|
| 由谁完成 | huangtiekun 于 2022-03-04 18:00:00 |
| 由谁取消 | 暂无 |
| 由谁关闭 | zhengqiao 于 2022-04-06 20:24:55 |
| 关闭原因 | 已完成 |
| 最后编辑 | zhengqiao 于 2022-04-06 20:24:55 |
| 团队 | 最初预计 | 总计消耗 | 预计剩余 |
|---|
工时信息
| 最初预计 | 8工时 |
|---|---|
| 总计消耗 | 24工时 |
| 预计剩余 | 0工时 |
| 预计开始 | 0000-00-00 |
| 实际开始 | 2022-03-11 |
| 截止日期 | 0000-00-00 |
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tbOrderSourceID') and type='U')
drop table #tbOrderSourceID
create table #tbOrderSourceID
(
Id int primary key
)
insert into #tbOrderSourceID
select item as Id from CharSplitForIn('6946,9474,9499,10240,5728,5784,7109,7182,7741,7757,7766,7958,8840,9685,10203,11017,8356,12223,12823,12825',',')
SELECT COUNT(1) FROM (
SELECT P.ListingProductID,
P.CustomerID,
P.OrderSourceID,
P.SiteCode,
P.ListingBasicID,
P.SKU,
P.OrderSourceSKU,
P.BuyItNowPrice,
P.EnableBestOffer,
B.Duration,
B.EnableTimerPosting,
B.TimerPosting,
P.MinBestOfferPrice,
P.AutoAcceptPrice,
P.Quantity,
P.EAN,
P.UPC,
P.EPID,
P.ParentListingProductID,
P.IsVariation,
P.ProductType,
B.TagName,
B.ItemID,
B.ListingType,
B.ProductTitle,
B.ProductSubTitle,
B.AuctionStartPrice,
B.AuctionReservePrice,
B.AuctionBuyItNowPrice,
B.Brand,
B.BrandMPN,
B.ClassID1,
B.ClassID2,
B.StoreClass1,
B.StoreClass2,
B.StoreURL,
B.StartTime,
B.FbaOrderSourceSKU,
B.EndTime,
Isnull(B.UpdateStatus, 0) UpdateStatus,
B.ViewItemURL,
B.AddTime,
B.AdminID,
B.AdminName,
B.UpdateTime,
B.UpdateAdminID,
B.UpdateAdminName,
B.AddItemAdminID,
B.AddItemAdminName,
B.SellerAdminID,
B.SellerAdminName,
B.AddItemFee,
B.OnlineState,
B.Visits,
B.RowVersion,
B.SyncTime,
B.IsSync,
ISNULL(B.QuantitySold,0) QuantitySold,
OS.OrderSourceName,
'' ClientSKU,
'' ProductNameCN,
NULL ProductState,
Isnull(ASP.EnableAutoSupplement, 0) EnableAutoSupplement,
Isnull(ASP.IsInventoryCheck, 0) IsInventoryCheck,
ASP.ListingQty,
ASP.SupplementaryQty,
B.Warehouse,
ELS.SiteCountry,country.CountryDescription,
0 SkuCount,
B.BidCount,
B.BidCurrentPrice,
P.OriginalRetailPrice
FROM RB_Ebay_Listing_Basic_V2 AS B WITH (NOLOCK)
INNER JOIN #tbOrderSourceID OrderSourceIDs ON B.OrderSourceID=OrderSourceIDs.Id
left JOIN RB_Ebay_Listing_Product_V2 AS P WITH (NOLOCK)
ON B.CustomerID = P.CustomerID
AND B.ListingBasicID = P.ListingBasicID
AND B.OrderSourceID = P.OrderSourceID
AND B.SiteCode = P.SiteCode
LEFT OUTER JOIN RB_OrderSource AS OS WITH (NOLOCK)
ON B.CustomerID = OS.CustomerID
AND B.OrderSourceID = OS.ID
LEFT JOIN RB_Ebay_Listing_AutoSupplement_V2 ASP WITH (NOLOCK)
ON ASP.CustomerID = P.CustomerID
AND ASP.ListingBasicID = P.ListingBasicID
AND ASP.ListingProductID = P.ListingProductID
AND ASP.OrderSourceID = P.OrderSourceID
AND ASP.SiteCode = P.SiteCode
LEFT JOIN RB_Ebay_SKU_Sale_Attach_V2 ESSA WITH (NOLOCK)
ON ESSA.SKU = P.SKU
AND ESSA.CustomerID = P.CustomerID
LEFT JOIN RB_Ebay_Listing_Shipping_V2 ELS WITH(NOLOCK)
ON B.ListingBasicID=ELS.ListingBasicID
AND B.CustomerID=ELS.CustomerID
LEFT JOIN E_Ebay_Country_V2 country (NOLOCK)
ON country.Country = ELS.SiteCountry
LEFT JOIN RB_Ebay_Listing_Markdown_Promotion MK WITH(NOLOCK)
ON MK.CustomerID=P.CustomerID
AND MK.ID=B.MarkdownPromotionID
WHERE P.CustomerID = 1 AND P.IsVariation = 0 and B.SellerAdminID != 3638 AND B.OnlineState=1 AND ELS.SiteCountry = 'CN' ) AS PagerTable option(maxdop 2)
---------------刘琼------Count ----------前面20个店铺
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tbOrderSourceID') and type='U')
drop table #tbOrderSourceID
create table #tbOrderSourceID
(
Id int primary key
)
insert into #tbOrderSourceID
select item as Id from CharSplitForIn('10231,10377,10385,10477,10498,11270,13423,13537,13538,13543,13620,5232,10795,11235,7836,9672,12643,13197,13422,13776,6946,9474,9499,10240',',')
SELECT COUNT(1) FROM (
SELECT P.ListingProductID,
P.CustomerID,
P.OrderSourceID,
P.SiteCode,
P.ListingBasicID,
P.SKU,
P.OrderSourceSKU,
P.BuyItNowPrice,
P.EnableBestOffer,
B.Duration,
B.EnableTimerPosting,
B.TimerPosting,
P.MinBestOfferPrice,
P.AutoAcceptPrice,
P.Quantity,
P.EAN,
P.UPC,
P.EPID,
P.ParentListingProductID,
P.IsVariation,
P.ProductType,
B.TagName,
B.ItemID,
B.ListingType,
B.ProductTitle,
B.ProductSubTitle,
B.AuctionStartPrice,
B.AuctionReservePrice,
B.AuctionBuyItNowPrice,
B.Brand,
B.BrandMPN,
B.ClassID1,
B.ClassID2,
B.StoreClass1,
B.StoreClass2,
B.StoreURL,
B.StartTime,
B.FbaOrderSourceSKU,
B.EndTime,
Isnull(B.UpdateStatus, 0) UpdateStatus,
B.ViewItemURL,
B.AddTime,
B.AdminID,
B.AdminName,
B.UpdateTime,
B.UpdateAdminID,
B.UpdateAdminName,
B.AddItemAdminID,
B.AddItemAdminName,
B.SellerAdminID,
B.SellerAdminName,
B.AddItemFee,
B.OnlineState,
B.Visits,
B.RowVersion,
B.SyncTime,
B.IsSync,
ISNULL(B.QuantitySold,0) QuantitySold,
OS.OrderSourceName,
'' ClientSKU,
'' ProductNameCN,
NULL ProductState,
Isnull(ASP.EnableAutoSupplement, 0) EnableAutoSupplement,
Isnull(ASP.IsInventoryCheck, 0) IsInventoryCheck,
ASP.ListingQty,
ASP.SupplementaryQty,
B.Warehouse,
ELS.SiteCountry,country.CountryDescription,
0 SkuCount,
B.BidCount,
B.BidCurrentPrice,
P.OriginalRetailPrice
FROM RB_Ebay_Listing_Basic_V2 AS B WITH (NOLOCK)
INNER JOIN #tbOrderSourceID OrderSourceIDs ON B.OrderSourceID=OrderSourceIDs.Id
left JOIN RB_Ebay_Listing_Product_V2 AS P WITH (NOLOCK)
ON B.CustomerID = P.CustomerID
AND B.ListingBasicID = P.ListingBasicID
AND B.OrderSourceID = P.OrderSourceID
AND B.SiteCode = P.SiteCode
LEFT OUTER JOIN RB_OrderSource AS OS WITH (NOLOCK)
ON B.CustomerID = OS.CustomerID
AND B.OrderSourceID = OS.ID
LEFT JOIN RB_Ebay_Listing_AutoSupplement_V2 ASP WITH (NOLOCK)
ON ASP.CustomerID = P.CustomerID
AND ASP.ListingBasicID = P.ListingBasicID
AND ASP.ListingProductID = P.ListingProductID
AND ASP.OrderSourceID = P.OrderSourceID
AND ASP.SiteCode = P.SiteCode
LEFT JOIN RB_Ebay_SKU_Sale_Attach_V2 ESSA WITH (NOLOCK)
ON ESSA.SKU = P.SKU
AND ESSA.CustomerID = P.CustomerID
LEFT JOIN RB_Ebay_Listing_Shipping_V2 ELS WITH(NOLOCK)
ON B.ListingBasicID=ELS.ListingBasicID
AND B.CustomerID=ELS.CustomerID
LEFT JOIN E_Ebay_Country_V2 country (NOLOCK)
ON country.Country = ELS.SiteCountry
LEFT JOIN RB_Ebay_Listing_Markdown_Promotion MK WITH(NOLOCK)
ON MK.CustomerID=P.CustomerID
AND MK.ID=B.MarkdownPromotionID
WHERE P.CustomerID = 1 AND P.IsVariation = 0 and B.SellerAdminID != 3638 AND B.OnlineState=1 AND ELS.SiteCountry = 'CN' ) AS PagerTable option(maxdop 2)
---------------------王新刚 Page -------前面20个店铺数据
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tbOrderSourceID') and type='U')
drop table #tbOrderSourceID
create table #tbOrderSourceID
(
Id int primary key
)
insert into #tbOrderSourceID
select item as Id from CharSplitForIn('6760,10233,10238,10469,10788,10789,12535,12678,12817,13177,13646,6761,7102,7185,7771,8961,9659,12147,12650,13777',',')
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ListingProductID DESC ) AS [PageIndex] ,* FROM (
SELECT P.ListingProductID,
P.CustomerID,
P.OrderSourceID,
P.SiteCode,
P.ListingBasicID,
P.SKU,
P.OrderSourceSKU,
P.BuyItNowPrice,
P.EnableBestOffer,
B.Duration,
B.EnableTimerPosting,
B.TimerPosting,
P.MinBestOfferPrice,
P.AutoAcceptPrice,
P.Quantity,
P.EAN,
P.UPC,
P.EPID,
P.ParentListingProductID,
P.IsVariation,
P.ProductType,
P.IsPriceStrategy,
P.PriceStrategyID,
P.PriceStrategyName,
B.TagName,
B.ItemID,
B.ListingType,
B.ProductTitle,
B.ProductSubTitle,
B.AuctionStartPrice,
B.AuctionReservePrice,
B.AuctionBuyItNowPrice,
B.Brand,
B.BrandMPN,
B.ClassID1,
B.ClassID2,
B.StoreClass1,
B.StoreClass2,
B.StoreURL,
B.StartTime,
B.FbaOrderSourceSKU,
B.EndTime,
Isnull(B.UpdateStatus, 0) UpdateStatus,
B.ViewItemURL,
B.AddTime,
B.AdminID,
B.AdminName,
B.UpdateTime,
B.UpdateAdminID,
B.UpdateAdminName,
B.AddItemAdminID,
B.AddItemAdminName,
B.SellerAdminID,
B.SellerAdminName,
B.AddItemFee,
B.OnlineState,
B.Visits,
B.RowVersion,
B.SyncTime,
B.IsSync,
ISNULL(B.QuantitySold,0) QuantitySold,
OS.OrderSourceName,
'' ClientSKU,
'' ProductNameCN,
NULL ProductState,
Isnull(ASP.EnableAutoSupplement, 0) EnableAutoSupplement,
Isnull(ASP.IsInventoryCheck, 0) IsInventoryCheck,
ASP.ListingQty,
ASP.SupplementaryQty,
B.Warehouse,
ELS.SiteCountry,
country.CountryDescription,
Isnull(LSA.SalesToday, 0) AS SalesToday,
Isnull(LSA.SalesYesterDay, 0) AS SalesYesterDay,
Isnull(LSA.SalesBeforeDay, 0) AS SalesBeforeDay,
Isnull(LSA.SalesMoth, 0) AS SalesMoth,
Isnull(LSA.SalesDayTrend, 0) AS SalesDayTrend,
Isnull(LSA.ViewsToday, 0) AS ViewsToday,
Isnull(LSA.ViewsYesterDay, 0) AS ViewsYesterDay,
Isnull(LSA.ViewsBeforeDay, 0) AS ViewsBeforeDay,
Isnull(LSA.ViewsMoth, 0) AS ViewsMoth,
Isnull(LSA.ViewsDayTrend, 0) AS ViewsDayTrend,
CASE
WHEN Isnull(LSA.ViewsToday, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesToday, 0)) / CONVERT(FLOAT, LSA.ViewsToday), 4) * 100
END AS RoiToday,
CASE
WHEN Isnull(LSA.ViewsYesterDay, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesYesterDay, 0)) / CONVERT(FLOAT, LSA.ViewsYesterDay), 4) * 100
END AS RoiYesterDay,
CASE
WHEN Isnull(LSA.ViewsBeforeDay, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesBeforeDay, 0)) / CONVERT(FLOAT, LSA.ViewsBeforeDay), 4) * 100
END AS RoiBeforeDay,
CASE
WHEN Isnull(LSA.ViewsMoth, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesMoth, 0)) / CONVERT(FLOAT, LSA.ViewsMoth), 4) * 100
END AS RoiMoth,
0 SkuCount,
B.BidCount,
B.BidCurrentPrice,
P.OriginalRetailPrice,
B.MarkdownPromotionID,
B.MarkdownPromotionName,
B.MarkdownPromotionDisCountName,
B.MarkdownPromotionStartDate,
B.MarkdownPromotionEndDate,
B.MarkdownPromotionDisCountID,
MK.PromotionStatus as MKPromotionStutas
FROM RB_Ebay_Listing_Basic_V2 AS B WITH (NOLOCK)
INNER JOIN #tbOrderSourceID OrderSourceIDs ON B.OrderSourceID=OrderSourceIDs.Id
left JOIN RB_Ebay_Listing_Product_V2 AS P WITH (NOLOCK)
ON B.CustomerID = P.CustomerID
AND B.ListingBasicID = P.ListingBasicID
AND B.OrderSourceID = P.OrderSourceID
AND B.SiteCode = P.SiteCode
left JOIN RB_Ebay_Listing_Sale_Attach_V2 LSA WITH (NOLOCK)
ON LSA.CustomerID = P.CustomerID
AND LSA.ListingBasicID = p.ListingBasicID
AND LSA.OrderSourceID = p.OrderSourceID
LEFT OUTER JOIN RB_OrderSource AS OS WITH (NOLOCK)
ON B.CustomerID = OS.CustomerID
AND B.OrderSourceID = OS.ID
LEFT JOIN RB_Ebay_Listing_AutoSupplement_V2 ASP WITH (NOLOCK)
ON ASP.CustomerID = P.CustomerID
AND ASP.ListingBasicID = P.ListingBasicID
AND ASP.ListingProductID = P.ListingProductID
AND ASP.OrderSourceID = P.OrderSourceID
AND ASP.SiteCode = P.SiteCode
LEFT JOIN RB_Ebay_Listing_Shipping_V2 ELS WITH(NOLOCK)
ON B.ListingBasicID=ELS.ListingBasicID
AND B.CustomerID=ELS.CustomerID
LEFT JOIN E_Ebay_Country_V2 country (NOLOCK)
ON country.Country = ELS.SiteCountry
LEFT JOIN RB_Ebay_Listing_Markdown_Promotion MK WITH(NOLOCK)
ON MK.CustomerID=P.CustomerID
AND MK.ID=B.MarkdownPromotionID
WHERE P.CustomerID = 1
AND P.IsVariation = 0 and B.SellerAdminID != 2948 AND B.OnlineState=1 AND ELS.SiteCountry = 'CN' ) AS TempTable ) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 20 option(maxdop 2)