任务描述
需求描述
1. 需求概述
1.1. 背景
备库订单旧版菜单已关闭，旧版菜单中的数据在新版无法搜索，需复制旧版数据添加到新版；
已和业务方、开发，进行测试，数据复制后，可以正常搜索，部分操作可能出现问题，业务方已同意；
2. 功能设计
2.1. 备库订单（新）增加数
2.1.1 需求说明
1、将【备库订单】中，从2020.1.1至今的数据，批量刷到【备库订单（新）】菜单中；
2、将订单按【备库订单（新）】的分类正确显示：
- 本地备库订单；
- FBA备库订单；
- 海外仓备库订单；
验收标准
暂无
附件
--本地备库订单
SELECT CAST(oi.ID AS nvarchar(200)) Id, oi.OrderCode '订单号',CAST(oi.WareHouseID AS NVARCHAR(200)) '发货仓id',CAST(oi.ToWareHouseID AS NVARCHAR(200))'到货仓id' ,wh.WareHouseName '发货仓名称',towh.WareHouseName '到货仓名称'
--UPDATE bk SET bk.OrderBuildType = 4FROM dbo.RB_OrderInfo (NOLOCK) oi
LEFT JOIN dbo.RB_WareHouse (NOLOCK) wh
ON wh.CustomerID = oi.CustomerID
AND wh.ID = oi.WareHouseID
LEFT JOIN dbo.RB_WareHouse (NOLOCK) towh
ON towh.CustomerID = oi.CustomerID
AND towh.ID = oi.ToWareHouseID
LEFT OUTER JOIN RB_OrderSource (NOLOCK) os
ON oi.OrderSourceID = os.ID
AND oi.CustomerID = os.CustomerID
LEFT JOIN dbo.RB_OrderInfo_BeikuExpand (NOLOCK) bk
ON bk.CustomerID = oi.CustomerID
AND bk.OrderCode = oi.OrderCode
WHERE oi.CustomerID = 1
--AND oi.OrderCode = '202204071531825349'
AND wh.WareHouseType = 1
AND towh.WareHouseType = 1
AND oi.IsStockOrder = 1
AND os.OrderSourceType = 20
AND ISNULL(bk.ID,0)>0
AND ISNULL(bk.OrderBuildType,0) = 0
AND oi.AddTime >= '2020-01-01';
--本地-fba
SELECT CAST(oi.ID AS nvarchar(200)) Id, oi.OrderCode '订单号',CAST(oi.WareHouseID AS NVARCHAR(200)) '发货仓id',CAST(oi.ToWareHouseID AS NVARCHAR(200))'到货仓id' ,wh.WareHouseName '发货仓名称',towh.WareHouseName '到货仓名称'
--UPDATE bk SET bk.OrderBuildType = 1
FROM dbo.RB_OrderInfo (NOLOCK) oi
LEFT JOIN dbo.RB_WareHouse (NOLOCK) wh
ON wh.CustomerID = oi.CustomerID
AND wh.ID = oi.WareHouseID
LEFT JOIN dbo.RB_WareHouse (NOLOCK) towh
ON towh.CustomerID = oi.CustomerID
AND towh.ID = oi.ToWareHouseID
LEFT OUTER JOIN RB_OrderSource (NOLOCK) os
ON oi.OrderSourceID = os.ID
AND oi.CustomerID = os.CustomerID
LEFT JOIN dbo.RB_OrderInfo_BeikuExpand (NOLOCK) bk
ON bk.CustomerID = oi.CustomerID
AND bk.OrderCode = oi.OrderCode
WHERE oi.CustomerID = 1
AND oi.IsSystem = 0
AND oi.IsStockOrder = 1
--AND oi.OrderCode = '202203111042825054'
AND wh.WareHouseType = 1
AND towh.WareHouseType = 3
AND os.OrderSourceType = 1
AND ISNULL(bk.ID,0)>0
AND ISNULL(bk.OrderBuildType,0) = 0
AND ISNULL(oi.AmazonShipmentId,'')<>''
AND oi.AddTime >= '2020-01-01';
--海外仓-fba
SELECT CAST(oi.ID AS nvarchar(200)) Id, oi.OrderCode '订单号',CAST(oi.WareHouseID AS NVARCHAR(200)) '发货仓id',CAST(oi.ToWareHouseID AS NVARCHAR(200))'到货仓id' ,wh.WareHouseName '发货仓名称',towh.WareHouseName '到货仓名称'
--UPDATE bk SET bk.OrderBuildType = 2
FROM dbo.RB_OrderInfo (NOLOCK) oi
LEFT JOIN dbo.RB_WareHouse (NOLOCK) wh
ON wh.CustomerID = oi.CustomerID
AND wh.ID = oi.WareHouseID
LEFT JOIN dbo.RB_WareHouse (NOLOCK) towh
ON towh.CustomerID = oi.CustomerID
AND towh.ID = oi.ToWareHouseID
LEFT OUTER JOIN RB_OrderSource (NOLOCK) os
ON oi.OrderSourceID = os.ID
AND oi.CustomerID = os.CustomerID
LEFT JOIN dbo.RB_OrderInfo_BeikuExpand (NOLOCK) bk
ON bk.CustomerID = oi.CustomerID
AND bk.OrderCode = oi.OrderCode
WHERE oi.CustomerID = 1
AND oi.IsStockOrder = 1
AND wh.WareHouseType <>1 AND wh.WareHouseType <>3
AND towh.WareHouseType = 3
AND os.OrderSourceType = 1
AND ISNULL(bk.ID,0)>0
AND ISNULL(bk.OrderBuildType,0) = 0
AND ISNULL(oi.AmazonShipmentId,'')<>''
AND oi.AddTime >= '2020-01-01';
--本地-海外仓
SELECT CAST(oi.ID AS nvarchar(200)) Id, oi.OrderCode '订单号',CAST(oi.WareHouseID AS NVARCHAR(200)) '发货仓id',CAST(oi.ToWareHouseID AS NVARCHAR(200))'到货仓id' ,wh.WareHouseName '发货仓名称',towh.WareHouseName '到货仓名称'
--UPDATE bk SET bk.OrderBuildType = 3
FROM dbo.RB_OrderInfo (NOLOCK) oi
LEFT JOIN dbo.RB_WareHouse (NOLOCK) wh
ON wh.CustomerID = oi.CustomerID
AND wh.ID = oi.WareHouseID
LEFT JOIN dbo.RB_WareHouse (NOLOCK) towh
ON towh.CustomerID = oi.CustomerID
AND towh.ID = oi.ToWareHouseID
LEFT OUTER JOIN RB_OrderSource (NOLOCK) os
ON oi.OrderSourceID = os.ID
AND oi.CustomerID = os.CustomerID
LEFT JOIN dbo.RB_OrderInfo_BeikuExpand (NOLOCK) bk
ON bk.CustomerID = oi.CustomerID
AND bk.OrderCode = oi.OrderCode
WHERE oi.CustomerID = 1
AND oi.IsSystem = 0
AND oi.IsStockOrder = 1
--AND oi.OrderCode = '202203111042825054'
AND wh.WareHouseType =1
AND towh.WareHouseType <>1 AND towh.WareHouseType <>3
AND ISNULL(bk.OrderBuildType,0) = 0
AND oi.AddTime >= '2020-01-01';
--海外仓-海外仓
SELECT CAST(oi.ID AS nvarchar(200)) Id, oi.OrderCode '订单号',CAST(oi.WareHouseID AS NVARCHAR(200)) '发货仓id',CAST(oi.ToWareHouseID AS NVARCHAR(200))'到货仓id' ,wh.WareHouseName '发货仓名称',towh.WareHouseName '到货仓名称'
--UPDATE bk SET bk.OrderBuildType = 3
FROM dbo.RB_OrderInfo (NOLOCK) oi
LEFT JOIN dbo.RB_WareHouse (NOLOCK) wh
ON wh.CustomerID = oi.CustomerID
AND wh.ID = oi.WareHouseID
LEFT JOIN dbo.RB_WareHouse (NOLOCK) towh
ON towh.CustomerID = oi.CustomerID
AND towh.ID = oi.ToWareHouseID
LEFT OUTER JOIN RB_OrderSource (NOLOCK) os
ON oi.OrderSourceID = os.ID
AND oi.CustomerID = os.CustomerID
LEFT JOIN dbo.RB_OrderInfo_BeikuExpand (NOLOCK) bk
ON bk.CustomerID = oi.CustomerID
AND bk.OrderCode = oi.OrderCode
WHERE oi.CustomerID = 1
AND oi.IsSystem = 0
AND oi.IsStockOrder = 1
--AND oi.OrderCode = '202203111042825054'
AND wh.WareHouseType NOT IN (1,81)
AND towh.WareHouseType <>1 AND towh.WareHouseType <>3
AND ISNULL(bk.ID,0)>0
AND ISNULL(bk.OrderBuildType,0) = 0
AND oi.AddTime >= '2020-01-01';