任务描述
暂无
需求描述

背景

仓库效率提升优化，节省人力


需求

本地链接：product-doc-wan.zhcxkj.com:9000/产品三组/田子豪/仓库效率优化/仓库效率优化-11月2版本.html
外部链接（供业务方查看）：仓库效率优化-11月2版本 (axshare.com)

验收标准
暂无
历史记录  
  1. 2022-11-08 15:51:24, 由 程伟 创建。
  2. 2022-11-08 16:51:59, 由 程伟 编辑。
    修改了 任务名称，区别为：
    001- 仓库效率优化-15
    001+ 仓库效率优化-点15
    001- 仓库效率优化-15
    001+ 仓库效率优化-点15
  3. 2022-11-17 18:29:28, 由 谢业福 编辑。
    修改了 开发组，旧值为 ""，新值为 "1"。

    需求：

    15、报表导出---4工时

    备货分拨的数据，在自定义报告中（仓库工作统计报表）导出数据：集货人，集货周转箱数量，SKU数量，产品数量。（请把之前此报表中的【集货数量统计】名称改成【分拔数量统计】，集货人改成分拔人，之前的统计方法不变。）

  4. 2022-11-17 19:03:53, 由 谢业福 添加备注。

    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,
                         Sum(skuNum)     AS SkuCount,
                         Sum(ProductNum) AS ProductNum
                  FROM   (SELECT Sum(ProductNum) AS ProductNum,
                                 p.CustomerID,
                                 tt.SortingPersonID,
                                 tt.SortingPersonName,
                                 Count(0)        skuNum
                          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) TB
                  GROUP  BY CustomerID,
                            SortingPersonID,
                            SortingPersonName) tb2
         ON tb1.CustomerID = tb2.CustomerID
            AND tb1.SortingPersonID = tb2.SortingPersonID
WHERE  Isnull(tb1.SortingPersonID, 0) > 0;
    更新后语句 

    SELECT tb1.SortingPersonName   AS 集货人,
       Isnull(ProductNum, 0)   AS 产品总数,
       Isnull(PackageCount, 0) AS 包裹数,
       Isnull(SkuCount, 0)     AS SKU数,
	   ISNULL(tb3.BoxNo,0) AS '集货周转箱数量' 
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,
                         Sum(skuNum)     AS SkuCount,
                         Sum(ProductNum) AS ProductNum
                  FROM   (SELECT Sum(ProductNum) AS ProductNum,
                                 p.CustomerID,
                                 tt.SortingPersonID,
                                 tt.SortingPersonName,
                                 Count(0)        skuNum
                          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) TB
                  GROUP  BY CustomerID,
                            SortingPersonID,
                            SortingPersonName) tb2
         ON tb1.CustomerID = tb2.CustomerID
            AND tb1.SortingPersonID = tb2.SortingPersonID
			LEFT JOIN(SELECT a.CollectionAdminID,
       a.CollectionUserName,a.CustomerID,
       Count(DISTINCT a.TurnoverBoxNo) AS BoxNo
FROM   RB_Package_Picking_ChildWarehouseArea_TurnoverBox a(NOLOCK)
       INNER JOIN dbo.RB_Package_Picking_ChildWarehouseArea_TurnoverBox_Detail b(NOLOCK)
         ON a.CustomerID = b.CustomerID
            AND a.ID = b.TurnoverBoxID
       INNER JOIN dbo.RB_Package_Picking c(NOLOCK)
         ON a.CustomerID = b.CustomerID
            AND a.MainPickingGUID = c.PickingGUID
WHERE  a.IsCollection = 1
       AND a.CustomerID = @CustomerID
	   AND a.CollectionTime BETWEEN @StartTime AND @EndTime     
       AND c.WareHouseID IN( @WareHouseIds)
GROUP  BY a.CollectionAdminID,
          a.CollectionUserName,a.CustomerID
) tb3 ON tb2.CustomerID=tb3.CustomerID AND tb2.SortingPersonID=tb3.CollectionAdminID
WHERE  Isnull(tb1.SortingPersonID, 0) > 0;

  5. 2022-11-17 19:04:31, 由 谢业福 完成。
    修改了 总计消耗，旧值为 "0"，新值为 "5"。
    修改了 实际完成，旧值为 ""，新值为 "2022-11-17 19:04:24"。
    修改了 任务状态，旧值为 "wait"，新值为 "done"。
    修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-11-17 19:04:30"。
    修改了 预计剩余，旧值为 "4"，新值为 "0"。
    修改了 由谁完成，旧值为 ""，新值为 "xieyefu"。
  6. 2022-11-28 14:48:15, 由 zhengqiao 关闭。
    修改了 任务状态，旧值为 "done"，新值为 "closed"。
    修改了 指派给 ，旧值为 "xieyefu"，新值为 "closed"。
    修改了 由谁关闭，旧值为 ""，新值为 "zhengqiao"。
    修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2022-11-28 14:48:15"。
    修改了 关闭原因，旧值为 ""，新值为 "done"。
所属迭代 技术研发中心 11月第2版本（2022年）
所属模块 /
相关需求 仓库效率优化
指派给 Closed 于 2022-11-28 14:48:15
任务类型 后端
任务标签 初始
开发组 百货开发组
任务状态 已关闭
进度 100%
优先级 P2
关键词
抄送给
由谁创建 程伟 于 2022-11-08 15:51:24
由谁完成 谢业福 于 2022-11-17 19:04:24
由谁取消 暂无
由谁关闭 zhengqiao 于 2022-11-28 14:48:15
关闭原因 已完成
最后编辑 zhengqiao 于 2022-11-28 14:48:15
团队 最初预计 总计消耗 预计剩余
工时信息
最初预计 4工时
总计消耗 5工时
预计剩余 0工时
预计开始 0000-00-00
实际开始 2022-11-17
截止日期 0000-00-00