暂无
任务描述
需求描述
1. 背景
仓库绩效管理规则改变，新的绩效为采用记件进行考核，需增加数据导出，支持新业务；
2. 功能设计
1）在【备库订单包裹（新）】页面中，所有状态的选项卡，均做以下修改：
- 搜索条件，增加“集货人员”，位置在拣货人员和装箱人员中间；
- 列表后面，操作人员列，添加“集货”字段，位置在拣货和装箱中间；
集货的取值为：【PDA-备货分拣】扫描打单批次的时候，表示集货成功，取集货时候PDA的登录人员；
2）【报告中心-自定义报告导出-报告下载-仓库工作统计报表】，在现有导出数据的基础上，增加两个页签，命名为：
- 集货数量统计
- 打单数量统计
集货数量统计中，包含以下字段（按顺序从左往右展示）：
集货人；取导出范围内包含的集货人员
产品总数；集货人员对应的包裹的产品数相加
包裹数；集货人员对应的包裹数量
SKU数；集货人员对应的SKU数，不去重（也就是相同包裹有相同产品，累加即可）
打单数量统计中，包含以下字段（按顺序从左往右展示）：
打单人；取导出范围内包含的打单人员
产品总数；打单人员对应的包裹的产品数相加
包裹数；打单人员对应的包裹数量
SKU数；打单人员对应的SKU数，不去重（也就是相同包裹有相同产品，累加即可）
欧代标总量；包裹详情中，欧代标签提示中的数量相加（输入框中的数量是可以更改的，建议取提示中的数量）
验收标准
暂无
开发报告
1.备库包裹(新)添加集货人搜索，并显示
2.集货数量统计
SELECT tb1.SortingPersonName AS 集货人,
ISNULL(ProductNum, 0) AS 产品总数,
ISNULL(PackageCount, 0) AS 包裹数,
ISNULL(SkuCount, 0) AS SKU数
FROM
(
SELECT p.CustomerID,
tt.SortingPersonID,
tt.SortingPersonName,
COUNT(0) AS PackageCount
FROM RB_Procurement_Order_Package (NOLOCK) p
INNER JOIN
(
SELECT DISTINCT
ts.CustomerID,
ts.SortingPersonID,
ts.SortingPersonName,
ts.PackageID
FROM RB_TwoSorting_Package (NOLOCK) ts
INNER JOIN
(
SELECT CustomerID,
PackageID,
MAX(ID) MaxId
FROM RB_TwoSorting_Package (NOLOCK)
WHERE CustomerID = @CustomerID
AND SortingPersonID > 0
AND SortingTime
BETWEEN @StartTime AND @EndTime
GROUP BY CustomerID,
PackageID
) t
ON t.CustomerID = ts.CustomerID
AND t.MaxId = ts.ID
WHERE ts.CustomerID = @CustomerID
AND ts.SortingPersonID > 0
AND ts.SortingTime
BETWEEN @StartTime AND @EndTime
) tt
ON p.CustomerID = tt.CustomerID
AND tt.PackageID = p.ID
INNER JOIN RB_OrderInfo o (NOLOCK)
ON p.MergerPackageNo = o.OrderCode
AND p.CustomerID = o.CustomerID
WHERE p.WareHouseID IN ( @WareHouseIds )
AND p.CustomerID = @CustomerID
AND p.IsStockOrderWithPackage = 1
GROUP BY p.CustomerID,
tt.SortingPersonID,
tt.SortingPersonName
) tb1
LEFT JOIN
(
SELECT CustomerID,
SortingPersonID,
SortingPersonName,
COUNT(0) AS SkuCount,
SUM(ProductNum) AS ProductNum
FROM
(
SELECT SUM(ProductNum) AS ProductNum,
p.CustomerID,
tt.SortingPersonID,
tt.SortingPersonName,
pd.SKU
FROM RB_Procurement_Order_Package p (NOLOCK)
INNER JOIN
(
SELECT DISTINCT
ts.CustomerID,
ts.SortingPersonID,
ts.SortingPersonName,
ts.PackageID
FROM RB_TwoSorting_Package (NOLOCK) ts
INNER JOIN
(
SELECT CustomerID,
PackageID,
MAX(ID) MaxId
FROM RB_TwoSorting_Package (NOLOCK)
WHERE CustomerID = @CustomerID
AND SortingPersonID > 0
AND SortingTime
BETWEEN @StartTime AND @EndTime
GROUP BY CustomerID,
PackageID
) t
ON t.CustomerID = ts.CustomerID
AND t.MaxId = ts.ID
WHERE ts.CustomerID = @CustomerID
AND ts.SortingPersonID > 0
AND ts.SortingTime
BETWEEN @StartTime AND @EndTime
) tt
ON p.CustomerID = tt.CustomerID
AND tt.PackageID = p.ID
LEFT JOIN RB_Procurement_Order_Package_Detail pd (NOLOCK)
ON p.ID = pd.PackageID
AND p.CustomerID = pd.CustomerID
INNER JOIN RB_OrderInfo o (NOLOCK)
ON p.MergerPackageNo = o.OrderCode
AND p.CustomerID = o.CustomerID
LEFT JOIN E_Country (NOLOCK) ec
ON p.Country = ec.Shorthand
WHERE p.WareHouseID IN ( @WareHouseIds )
AND p.CustomerID = @CustomerID
AND p.IsStockOrderWithPackage = 1
GROUP BY p.CustomerID,
tt.SortingPersonID,
tt.SortingPersonName,
SKU
) TB
GROUP BY CustomerID,
SortingPersonID,
SortingPersonName
) tb2
ON tb1.CustomerID = tb2.CustomerID
AND tb1.SortingPersonID = tb2.SortingPersonID
WHERE ISNULL(tb1.SortingPersonID, 0) > 0;
测试环境
3.打单数量统计
SELECT tb1.PrintAdminName AS 打单人,
ISNULL(ProductNum, 0) AS 产品总数,
ISNULL(PackageCount, 0) AS 包裹数,
ISNULL(SkuCount, 0) AS SKU数,
ISNULL(tb2.PrintODNum, 0) AS 欧代标总量
FROM
(
SELECT p.CustomerID,
p.PrintAdminID,
p.PrintAdminName,
COUNT(0) AS PackageCount
FROM RB_Procurement_Order_Package p
INNER JOIN RB_OrderInfo o (NOLOCK)
ON p.MergerPackageNo = o.OrderCode
AND p.CustomerID = o.CustomerID
WHERE p.PrintTime
BETWEEN @StartTime AND @EndTime
AND p.WareHouseID IN ( @WareHouseIds )
AND p.CustomerID = @CustomerID
AND p.IsStockOrderWithPackage = 1
GROUP BY p.CustomerID,
PrintAdminID,
PrintAdminName
) tb1
LEFT JOIN
(
SELECT CustomerID,
PrintAdminName,
PrintAdminID,
COUNT(0) AS SkuCount,
SUM(ProductNum) AS ProductNum,
SUM(PrintODNum) AS PrintODNum
FROM
(
SELECT SUM(ProductNum) AS ProductNum,
p.CustomerID,
p.PrintAdminName,
p.PrintAdminID,
pd.SKU,
ec.IsEuropeanUnion,
pt.IsNeedCeCertification,
CASE
WHEN ec.IsEuropeanUnion = 1
AND pt.IsNeedCeCertification = 1 THEN
SUM(ProductNum)
ELSE
0
END PrintODNum
FROM RB_Procurement_Order_Package p (NOLOCK)
LEFT JOIN RB_Procurement_Order_Package_Detail pd (NOLOCK)
ON p.ID = pd.PackageID
AND p.CustomerID = pd.CustomerID
INNER JOIN RB_OrderInfo o (NOLOCK)
ON p.MergerPackageNo = o.OrderCode
AND p.CustomerID = o.CustomerID
LEFT JOIN E_Country (NOLOCK) ec
ON p.Country = ec.Shorthand
LEFT JOIN dbo.RB_Product (NOLOCK) pt
ON pd.CustomerID = pt.CustomerID
AND pd.SKU = pt.SKU
WHERE p.PrintTime
BETWEEN @StartTime AND @EndTime
AND p.WareHouseID IN ( @WareHouseIds )
AND p.CustomerID = @CustomerID
AND p.IsStockOrderWithPackage = 1
GROUP BY p.CustomerID,
PrintAdminName,
p.PrintAdminID,
pd.SKU,
ec.IsEuropeanUnion,
pt.IsNeedCeCertification
) TB
GROUP BY CustomerID,
PrintAdminName,
PrintAdminID
) tb2
ON tb1.CustomerID = tb2.CustomerID
AND tb1.PrintAdminID = tb2.PrintAdminID
WHERE ISNULL(tb1.PrintAdminID, 0) > 0;