USE [MRCSFA]
GO
/****** Object:  StoredProcedure [dbo].[sp_Sync_incentive_new]    Script Date: 11/14/2019 11:33:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--Chỉ lấy IsPDA=1 (chỉ trả PDA, trước đây dùng để xác định IsDirect: 1|0 : Vansales|presales)
--Có thêm trả hàng
--Vì DMSEvaluationDefine ko có Date nên DMSAimIncentiveProgram được xác định sau khi đã xác định KH được trả thưởng
-- =============================================
-- exec [sp_Sync_incentive_new] 3
ALTER PROCEDURE [dbo].[sp_Sync_incentive_new] @CompanyID INT
AS
    BEGIN
        TRUNCATE TABLE DMSAimIncentiveList
        TRUNCATE TABLE DMSAimIncentiveByAmount
        TRUNCATE TABLE dbo.DMSAimIncentiveProgram

        DECLARE @INCENTIVE_TYPE_INVENTORY NVARCHAR(1)  = 'P';
        DECLARE @INCENTIVE_TYPE_AMOUNT NVARCHAR(1)  = 'V';
        DECLARE @INCENTIVE_TYPE_BOTH_PDA NVARCHAR(1)  = 'B';

	--PROGRAM TYPE
		DECLARE @PROGRAM_TYPE_PERFECT_STORE NVARCHAR(1)  = 'P';
		DECLARE @PROGRAM_TYPE_VISIBILITY NVARCHAR(1)  = 'V';

    -- Lấy line được trả thưởng chưa import qua Acu (Qua Acu được check done trong DMSEvaluationListDetails)
    -- Lấy từ đơn hàng có OrderDate < GETDATE(), đơn trong ngày không lấy

        SELECT  DISTINCT
                d.RefNbr ,
                d.IncentiveLineNbr
        INTO    #tempUnImportDone
        FROM    [172.17.0.235].[MRCDMS].dbo.DMSPDAIncentiveDetail d
                INNER JOIN [172.17.0.235].[MRCDMS].dbo.DMSPDAOrder o ON o.OrderNbr = d.OrderNbr
                                                           AND o.CompanyID = d.CompanyID
        WHERE   o.Status = 'U'
                AND DATEDIFF(dd, o.OrderDate, GETDATE()) > 0
                AND d.CompanyID = @CompanyID

        SELECT  c.CustomerCD ,
                c.LocationCD ,
                c.UserName ,
                d.RefNbr ,
                d.LineNbr ,
                d.InventoryID ,
                d.Qty ,
                d.totalbonus ,
                d.IncentiveType,
                d.IncentiveFor,
                br.BreakByLevelName,
                RTRIM(p.FromPeriod) AS Period,
                ISNULL (d.expirydate, EOMONTH(GETDATE())) AS EndDate,
				d.isdone,
				d.Reason
        INTO    #tempDetail
        FROM    [172.17.0.235].[MRCDMS].dbo.DMSEvaluationDefine p
                JOIN [172.17.0.235].[MRCDMS].dbo.DMSEvaluationList l ON l.CompanyID = p.CompanyID
                                                       AND l.RefNbr = p.RefNbr
                JOIN [172.17.0.235].[MRCDMS].dbo.DMSProgramBreakDownDetail br ON br.CompanyID = l.CompanyID
                                                        AND br.ProgramID = l.ProgramID
                                                        AND br.BreakByLevelID = l.LevelID
                JOIN [172.17.0.235].[MRCDMS].dbo.DMSEvaluationListDetails d ON d.CompanyID = d.CompanyID
                                                              AND d.RefNbr =p.RefNbr
                                                              AND d.CustomerID = l.CustomerID
                JOIN dbo.DMSAllCustomer a ON a.LocationID = d.CustomerID
                JOIN dbo.DMSAimCustomer c ON a.CustomerCD = c.CustomerCD
                                                   AND a.LocationCD = c.LocationCD
                LEFT JOIN #tempUnImportDone un ON un.IncentiveLineNbr = d.LineNbr
                                                  AND p.RefNbr = un.RefNbr
        WHERE   p.CompanyID = @CompanyID
                AND p.Status = 'E'
                --AND p.IsPDA = 1
                AND ISNULL(d.isdone, 0) = 0 -- lấy line chưa done
                AND d.Status = 'R'
        --ko có beginDate
                AND DATEDIFF(dd, GETDATE(), ISNULL(d.expirydate, GETDATE())) >= 0
                AND un.IncentiveLineNbr IS NULL -- bỏ line chưa import

        SELECT RefNbr,
                MIN(EndDate) AS EndDate
        INTO #tempEndDate
        FROM #tempDetail
        GROUP BY RefNbr

        INSERT INTO dbo.DMSAimIncentiveList
        (
            UserName,
            CustomerCD,
            LocationCD,
            IncentiveCD,
            InventoryCD,
            IncentiveQty,
            IsDirect,
            LineNbr,
            IncentiveFor,
            Level,
			IsDone,
			Reason

        )
        SELECT DISTINCT
               UserName,          -- UserName - nvarchar(50)
               CustomerCD,        -- CustomerCD - nvarchar(50)
               LocationCD,        -- LocationCD - nvarchar(50)
               RefNbr,            -- IncentiveCD - nvarchar(50)
               i.InventoryCD,     -- InventoryCD - nvarchar(50)
               Qty,               -- IncentiveQty - int
               0,                 -- IsDirect - int
               d.LineNbr,         -- LineNbr - int
               d.IncentiveFor,    -- IncentiveFor - nvarchar(1)
               d.BreakByLevelName, -- Level - nvarchar(50)
			   ISNULL(d.isdone,0),
			   Reason
        FROM #tempDetail d
            INNER JOIN dbo.DMSAimInventoryList i
                ON d.InventoryID = i.InventoryID
        WHERE ISNULL(d.Qty, 0) > 0
              AND d.IncentiveType = @INCENTIVE_TYPE_INVENTORY;

        INSERT INTO dbo.DMSAimIncentiveByAmount
        (
            UserName,
            CustomerCD,
            LocationCD,
            IncentiveCD,
            Level,
            Amount,
            IncentiveFor,
            LineNbr,
			IsDone,
			Reason
        )
        SELECT DISTINCT
               UserName,         -- UserName - nvarchar(50)
               CustomerCD,       -- CustomerCD - nvarchar(50)
               LocationCD,       -- LocationCD - nvarchar(50)
               RefNbr,           -- IncentiveCD - nvarchar(50)
               BreakByLevelName, -- Level - nvarchar(50)
               totalbonus,       -- Amount - float
               IncentiveFor,     -- IncentiveFor - nvarchar(1)
               LineNbr,           -- LineNbr - int
			   ISNULL(isdone,0),
			   Reason
        FROM #tempDetail
        WHERE ISNULL(totalbonus, 0) > 0
              AND IncentiveType = @INCENTIVE_TYPE_AMOUNT;



    --Chỉ lấy CT có KH đang được trả thưởng
        INSERT INTO dbo.DMSAimIncentiveProgram
        (
            UserName,
            IncentiveCD,
            Descr,
            StartDate,
            EndDate,
            Period,
            IncentiveType,
			ProgramType
        )
        SELECT DISTINCT
               e.EmployeeICD,             -- UserName - nvarchar(50)
               d.RefNbr,                  -- IncentiveCD - nvarchar(50)
               ISNULL(d.Descr, d.RefNbr), -- Descr - nvarchar(255)
               GETDATE(),                 -- StartDate - date
               ed.EndDate,                -- EndDate - date
               RTRIM(d.FromPeriod),     -- Period - nvarchar(50)
               CASE
                   WHEN a.IncentiveCD IS NOT NULL
                        AND i.IncentiveCD IS NOT NULL THEN
                       @INCENTIVE_TYPE_BOTH_PDA
                   WHEN a.IncentiveCD IS NOT NULL THEN
                       @INCENTIVE_TYPE_AMOUNT
                   ELSE
                       @INCENTIVE_TYPE_INVENTORY
               END,                        -- IncentiveType - nvarchar(1)
			   IIF(ISNULL(p.IsPerfectStore, 0) = 1,
			    @PROGRAM_TYPE_PERFECT_STORE, @PROGRAM_TYPE_VISIBILITY) -- ProgramType nvarchar(3)

        FROM [172.17.0.235].[MRCDMS].dbo.DMSEvaluationDefine d
			LEFT JOIN [172.17.0.235].[MRCDMS].dbo.DMSProgram p ON	p.CompanyID = d.CompanyID
											AND p.ProgramID = d.ProgramID
            CROSS JOIN dbo.DMSAimEmployeeList e
            JOIN #tempEndDate ed
                ON d.RefNbr = ed.RefNbr
            LEFT JOIN
            (
                SELECT DISTINCT
                       UserName,
                       IncentiveCD
                FROM dbo.DMSAimIncentiveByAmount
            ) a
                ON d.RefNbr = a.IncentiveCD
                   AND a.UserName = e.EmployeeICD
            LEFT JOIN
            (SELECT DISTINCT UserName, IncentiveCD FROM dbo.DMSAimIncentiveList) i
                ON i.IncentiveCD = d.RefNbr
                   AND i.UserName = e.EmployeeICD
        WHERE d.CompanyID = @CompanyID
              AND
              (
                  a.IncentiveCD IS NOT NULL
                  OR i.IncentiveCD IS NOT NULL
              );

        DROP TABLE #tempEndDate
        DROP TABLE #tempUnImportDone
        DROP TABLE #tempDetail
    END