挂sqlserver计划，系统自动分配拣货任务

USE [P2WMS_WH43]
GO
/****** Object:  StoredProcedure [dbo].[sp_fru_CalcAllocatePickData]    Script Date: 05/04/2018 11:41:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Evan
-- Create date: 2018年4月17日 14点53分
-- Description:    自动分配待拣数据,寄宿在SQL server的job任务[FRU自动按照库位分配拣货任务],每5分钟跑一次
-- Logic:    1：汇总待分配的DN（DN,PartNO,Location,BOL）,同一个DN可能会对应多个不同库位。#temp_DNList
--            2: 汇总需要分配的库位（distinct）。#temp_AllocationLocation
--            3：遍历#temp_AllocationLocation.LocationCode,匹配此LocationCode对应表FRU_PickLocationNew.Account手上DN量最少的账户，
--            将此次循环下的LocationCode的DN（包括这个DN下对应的另外的库位）全部给这个人，最后将已分配的数据从#temp_DNList删除。
-- =============================================
CreatePROCEDURE [dbo].[sp_fru_CalcAllocatePickData]
AS
    BEGIN
----------------------------------------找出未拣货的DN
        IF EXISTS ( SELECT  *
                    FROM    tempdb..sysobjects
                    WHERE   id = OBJECT_ID('tempdb..#temp_DNList') )
            BEGIN         
                DROP TABLE  #temp_box        
            END 
        CREATE TABLE #temp_DNList--汇总未分配账户未拣货的DN
            (
              DN NVARCHAR(MAX) ,
              PartNO NVARCHAR(250) ,
              LocationCode NVARCHAR(250) ,
              BOL NVARCHAR(250)
            )
        INSERT  INTO #temp_DNList
                ( DN ,
                  PartNO ,
                  LocationCode ,
                  BOL
                )
                SELECT  wo.UserDefineAttribute01 ,
                        wop.PartNO ,
                        wopp.LocationCode ,
                        --SUM(CAST( wopp.PickEAQty AS DECIMAL(18,0))) PickEAQty,
                        wo.UserDefineAttribute02
                FROM    dbo.WMS_Outbound wo
                        INNER JOIN dbo.WMS_OutboundPart wop ON wop.OutboundID = wo.OutboundID
                        INNER JOIN dbo.WMS_OutboundPick wopp ON wopp.OutboundPartID = wop.OutboundPartID
                        LEFT JOIN dbo.WMS_FRU_DNAllocationToAccount dta ON dta.DN = wo.UserDefineAttribute01
                WHERE   ISNULL(wopp.TargetLocationCode, '') = ''
                        AND ISNULL(wo.UserDefineAttribute02, '') <> ''
                        AND ISNULL(wop.AllocationEAQty, 0) > 0
                        AND ISNULL(dta.DN, '') = ''
                GROUP BY wo.UserDefineAttribute01 ,
                        wop.PartNO ,
                        wopp.LocationCode ,
                        wo.UserDefineAttribute02
                ORDER BY wopp.LocationCode
                
        SELECT  *
        FROM    #temp_DNList
        ORDER BY LocationCode;
----------------------------------------------------------计算启用的账户手上的已分配的DN数,一个DN对应一个料
        WITH    a AS ( SELECT   fpln.Account ,
                                ISNULL(COUNT(fdt.DN), 0) OrderNum
                       FROM     ( SELECT DISTINCT
                                            Account
                                  FROM      dbo.FRU_PickLocationNew
                                  WHERE     IsUse = 1
                                ) fpln
                                LEFT JOIN WMS_FRU_DNAllocationToAccount fdt ON fdt.Picker = fpln.Account
                                INNER JOIN dbo.WMS_Outbound wo ON wo.UserDefineAttribute01 = fdt.DN
                                                              AND WO.PickState <> 2
                                                              AND wo.ShipmentState <> 3
                       GROUP BY fpln.Account
                     ),
                b AS ( SELECT DISTINCT
                                Account
                       FROM     dbo.FRU_PickLocationNew
                       WHERE    IsUse = 1
                     )
            SELECT  b.Account ,
                    CASE WHEN ISNULL(a.Account, '') <> '' THEN a.OrderNum
                         ELSE 0
                    END OrderNum
            FROM    b
                    LEFT JOIN a ON a.Account = b.Account
---------------------------------------------------------汇总本次需要系统自动分配库位，按照库位分配至账号，优先挑手上DN量最少的账户
        IF EXISTS ( SELECT  *
                    FROM    tempdb..sysobjects
                    WHERE   id = OBJECT_ID('tempdb..#temp_AllocationLocation') )
            BEGIN         
                DROP TABLE  #temp_AllocationLocation        
            END 
        CREATE TABLE #temp_AllocationLocation
            (
              LocationCode NVARCHAR(MAX) ,
              RN INT
            )
        INSERT  INTO #temp_AllocationLocation
                ( LocationCode ,
                  RN
                )
                SELECT  sss.LocationCode ,
                        ROW_NUMBER() OVER ( ORDER BY sss.LocationCode ) RN
                FROM    ( SELECT DISTINCT
                                    LocationCode
                          FROM      #temp_DNList
                        ) sss
        SELECT  *
        FROM    #temp_AllocationLocation
        ORDER BY LocationCode;
        DECLARE @i INT= 1;
        DECLARE @de_Location NVARCHAR(250);--当前遍历处理的库位
        DECLARE @de_Account NVARCHAR(250);--当前要分配任务至的账户，挑选目前手上DN量最少的账户
        WHILE @i <= ( SELECT    MAX(RN)
                      FROM      #temp_AllocationLocation
                    )
            BEGIN
            
                SELECT  @de_Location = LocationCode
                FROM    #temp_AllocationLocation
                WHERE   RN = @i;
                IF EXISTS ( SELECT TOP 1
                                    1
                            FROM    #temp_DNList
                            WHERE   LocationCode = @de_Location )
                    BEGIN
                        WITH    a AS ( SELECT   fpln.Account ,
                                                ISNULL(COUNT(fdt.DN), 0) OrderNum
                                       FROM     ( SELECT DISTINCT
                                                            Account
                                                  FROM      dbo.FRU_PickLocationNew
                                                  WHERE     IsUse = 1
                                                ) fpln
                                                LEFT JOIN WMS_FRU_DNAllocationToAccount fdt ON fdt.Picker = fpln.Account
                                                INNER JOIN dbo.WMS_Outbound wo ON wo.UserDefineAttribute01 = fdt.DN
                                                              AND WO.PickState <> 2
                                                              AND wo.ShipmentState <> 3
                                       GROUP BY fpln.Account
                                     ),
                                b AS ( SELECT DISTINCT
                                                Account
                                       FROM     dbo.FRU_PickLocationNew
                                       WHERE    IsUse = 1
                                                AND Location = SUBSTRING(@de_Location,
                                                              1, 2)
                                     )
                            SELECT TOP 1
                                    @de_Account = sss.Account
                            FROM    ( SELECT    b.Account ,
                                                CASE WHEN ISNULL(a.Account, '') <> ''
                                                     THEN a.OrderNum
                                                     ELSE 0
                                                END OrderNum
                                      FROM      b
                                                LEFT JOIN a ON a.Account = b.Account
                                    ) sss
                            ORDER BY sss.OrderNum ,
                                    sss.Account 
                        --此次遍历的库位未在表FRU_PickLocationNew维护账户，因此未获取到可分配的账户
                        IF ( ISNULL(@de_Account, '') <> '' )
                            BEGIN
                        --发现该Location下存在DN要去不同库位上拣货，那么该DN全部分配给这个人
                                DECLARE @IsRepeatDN INT= 0;--标识，该Location下是否存在DN要去不同库位拣货，1表示存在这些DN
                                IF EXISTS ( SELECT  *
                                            FROM    tempdb..sysobjects
                                            WHERE   id = OBJECT_ID('tempdb..#temp_RepeatDN') )
                                    BEGIN         
                                        DROP TABLE  #temp_RepeatDN        
                                    END 
                                CREATE TABLE #temp_RepeatDN ( DN NVARCHAR(MAX) )
                                IF EXISTS ( SELECT  DN
                                            FROM    #temp_DNList
                                            WHERE   dn IN (
                                                    SELECT  dn
                                                    FROM    #temp_DNList
                                                    WHERE   LocationCode = @de_Location )
                                            GROUP BY DN
                                            HAVING  COUNT(DN) > 1 )
                                    BEGIN
                                        INSERT  INTO #temp_RepeatDN
                                                ( DN
                                                )
                                                SELECT DISTINCT
                                                        SSS.DN
                                                FROM    ( SELECT
                                                              DN
                                                          FROM
                                                              #temp_DNList
                                                          WHERE
                                                              dn IN (
                                                              SELECT
                                                              dn
                                                              FROM
                                                              #temp_DNList
                                                              WHERE
                                                              LocationCode = @de_Location )
                                                          GROUP BY DN
                                                          HAVING
                                                              COUNT(DN) > 1
                                                        ) SSS
                                        SELECT  @IsRepeatDN = 1;
                                    END
                        ---------------------------
                                IF ( @IsRepeatDN <> 1 )
                                    BEGIN
                                        INSERT  INTO dbo.WMS_FRU_DNAllocationToAccount
                                                ( DN ,
                                                  LocationCode ,
                                                  BOL ,
                                                  Picker ,
                                                  CreateBy ,
                                                  CreateDate 
                                                )
                                                SELECT  td.DN ,
                                                        td.LocationCode ,
                                                        td.BOL ,
                                                        @de_Account ,
                                                        'Evan' ,
                                                        GETDATE()
                                                FROM    #temp_DNList td
                                                WHERE   td.LocationCode = @de_Location
                                    END
                                ELSE
                                    BEGIN
                                        INSERT  INTO dbo.WMS_FRU_DNAllocationToAccount
                                                ( DN ,
                                                  LocationCode ,
                                                  BOL ,
                                                  Picker ,
                                                  CreateBy ,
                                                  CreateDate 
                                                )
                                                SELECT  td.DN ,
                                                        td.LocationCode ,
                                                        td.BOL ,
                                                        @de_Account ,
                                                        'Evan' ,
                                                        GETDATE()
                                                FROM    #temp_DNList td
                                                WHERE   td.LocationCode = @de_Location
                                                        AND TD.DN NOT IN (
                                                        SELECT
                                                              dn
                                                        FROM  #temp_RepeatDN )
                         
                                        INSERT  INTO dbo.WMS_FRU_DNAllocationToAccount
                                                ( DN ,
                                                  LocationCode ,
                                                  BOL ,
                                                  Picker ,
                                                  CreateBy ,
                                                  CreateDate 
                                                )
                                                SELECT  td.DN ,
                                                        td.LocationCode ,
                                                        td.BOL ,
                                                        @de_Account ,
                                                        'Evan' ,
                                                        GETDATE()
                                                FROM    #temp_DNList td
                                                WHERE   TD.DN IN ( SELECT
                                                              dn
                                                              FROM
                                                              #temp_RepeatDN )
                                    END
                         
                                DELETE  FROM #temp_DNList
                                WHERE   LocationCode = @de_Location;
                                DELETE  FROM #temp_DNList
                                WHERE   dn IN ( SELECT  dn
                                                FROM    #temp_RepeatDN );
                                DROP TABLE #temp_RepeatDN;
                            END
                    END
                SET @i = @i + 1;


            END
        SELECT  *
        FROM    #temp_DNList;
-----------------------------------------------------

    END