ALTER PROCEDURE [dbo].[PL_IMPORT_App]
    @P_IMPORT_ID VARCHAR(15),
    @P_AUTH_STATUS VARCHAR(1),
    @P_CHECKER_ID VARCHAR(12),
    @P_APPROVE_DT DATETIME = NULL
AS

BEGIN TRY
BEGIN TRANSACTION;

DECLARE @IS_UPDATE BIT 


SET @IS_UPDATE = (SELECT IS_UPDATE FROM dbo.PL_IMPORT WHERE IMPORT_ID=@P_IMPORT_ID )


--VALIDATE dữ liệu

DECLARE @IMPORT_YEAR VARCHAR(4)

SET @IMPORT_YEAR=(SELECT CAST(IMP_YEAR AS VARCHAR(4)) FROM dbo.PL_IMPORT WHERE IMPORT_ID=@P_IMPORT_ID)

IF(@IS_UPDATE=0)
BEGIN
IF(EXISTS(
SELECT DT.TRADE_ID FROM dbo.PL_MASTER PM 
LEFT JOIN dbo.PL_TRADEDETAIL DT ON PM.PLAN_ID=DT.PLAN_ID
WHERE PM.YEAR=@IMPORT_YEAR AND (EXISTS(
	SELECT Tmp.GOOD_ID FROM PL_IMPORT_DT Tmp WHERE (Tmp.IMP_YEAR=PM.YEAR AND Tmp.PLAN_TYPE_ID=PM.PLAN_TYPE_ID 
									AND Tmp.COST_ID=PM.COST_ID AND Tmp.BRANCH_ID=PM.BRANCH_ID 
									AND Tmp.DEP_ID=PM.DEPT_ID AND Tmp.GOOD_ID=DT.GOODS_ID) AND( (Tmp.QTY < ISNULL(DT.QUANTITY_EXE,0) AND  ISNULL(DT.QUANTITY_EXE,0) >0) OR (Tmp.TOTAL_AMT< ISNULL(DT.AMT_EXE,0) AND ISNULL(DT.AMT_EXE,0) >0) )
 ) OR (NOT EXISTS(SELECT Tmp.GOOD_ID FROM PL_IMPORT_DT Tmp WHERE (Tmp.IMP_YEAR=PM.YEAR AND Tmp.PLAN_TYPE_ID=PM.PLAN_TYPE_ID 
									AND Tmp.COST_ID=PM.COST_ID AND Tmp.BRANCH_ID=PM.BRANCH_ID 
									AND Tmp.DEP_ID=PM.DEPT_ID AND Tmp.GOOD_ID=DT.GOODS_ID) ) AND( ISNULL(DT.QUANTITY_EXE,0) > 0 OR ISNULL(DT.AMT_EXE,0) >0)
))))
BEGIN

		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Hạn mục chỉnh sửa đã sử dụng vượt hạn mức chỉnh sửa' ErrorDesc
		RETURN '-1'
END
END






-- Delete dữ liệu ko có trong import hiện tại.
IF(@IS_UPDATE=0)
BEGIN
UPDATE dbo.PL_TRADEDETAIL SET RECORD_STATUS=0 WHERE  NOT EXISTS(SELECT DT.IMP_DT_ID FROM dbo.PL_IMPORT_DT DT WHERE DT.TRADE_ID=PL_TRADEDETAIL.TRADE_ID) AND PL_TRADEDETAIL.PLAN_ID IN (SELECT PLAN_ID FROM dbo.PL_MASTER WHERE YEAR=@IMPORT_YEAR)

UPDATE dbo.PL_MASTER SET RECORD_STATUS=0 WHERE YEAR=@IMPORT_YEAR AND  NOT EXISTS(SELECT DT.IMP_DT_ID FROM dbo.PL_IMPORT_DT DT WHERE DT.PLAN_ID=PL_MASTER.PLAN_ID)
END




DECLARE @PLAN_CODE VARCHAR(20),
		@PLAN_NAME NVARCHAR(200),
		@YEAR INT,
        @PLAN_TYPE_ID VARCHAR(15),
        @COST_ID VARCHAR(15),
        @BRANCH_ID VARCHAR(15),
		@BRANCH_CODE VARCHAR(15),
        @DEP_ID VARCHAR(15),
		@DEP_CODE VARCHAR(15),
        @GOOD_ID VARCHAR(15),
        @QTY INT,
        @TOTAL_AMT DECIMAL(18, 2),
		@AMT_ETM DECIMAL(18, 2),
		@AMT_EXE DECIMAL(18, 2),  
		@AMT_TF DECIMAL(18, 2),
		@AMT_RECIVE_TF DECIMAL(18, 2),
        @NOTES NVARCHAR(1000),
		@IMP_AMT NUMERIC(18, 2),
		@IMP_QTY NUMERIC(18, 0),
		@MARKER_ID VARCHAR(15),
		@EDIT_DT DATETIME,
		@PLAN_ID VARCHAR(15),
		@TRADE_ID VARCHAR(15),
		@REF_CODE VARCHAR(20),
		@REF_NAME NVARCHAR(200),
		@IMP_DT_ID VARCHAR(20)


SELECT @MARKER_ID=MAKER_ID,@EDIT_DT=EDIT_DT FROM dbo.PL_IMPORT WHERE IMPORT_ID=@P_IMPORT_ID


DECLARE Plan_Master CURSOR FOR
SELECT IM.PLAN_CODE,IM.IMP_NAME,IM.IMP_YEAR,SUM(ISNULL(IMDT.QTY,0)) AS QTY,SUM(ISNULL(IMDT.TOTAL_AMT,0)) AS AMT, IMDT.PLAN_TYPE_ID, IMDT.COST_ID,IMDT.BRANCH_ID,IMDT.DEP_ID,
IMDT.PLAN_ID,IMDT.BRANCH_CODE,IMDT.DEP_CODE
FROM dbo.PL_IMPORT IM
LEFT JOIN dbo.PL_IMPORT_DT IMDT ON IM.IMPORT_ID=IMDT.IMPORT_ID
WHERE IM.IMPORT_ID = @P_IMPORT_ID AND IMDT.IS_UPDATE=1
GROUP BY IM.PLAN_CODE,IM.IMP_NAME,IM.IMP_YEAR,IMDT.PLAN_TYPE_ID,IMDT.COST_ID,IMDT.BRANCH_ID,IMDT.DEP_ID,IMDT.PLAN_ID,IMDT.BRANCH_CODE,IMDT.DEP_CODE
OPEN Plan_Master;

	FETCH NEXT FROM Plan_Master INTO @PLAN_CODE,@PLAN_NAME,@YEAR,@IMP_QTY,@IMP_AMT, @PLAN_TYPE_ID,@COST_ID,@BRANCH_ID,@DEP_ID,@PLAN_ID,@BRANCH_CODE,@DEP_CODE
	
	WHILE @@FETCH_STATUS = 0	
	BEGIN	

		SET @PLAN_NAME=N'KẾ HOẠCH NGÂN SÁCH '+ CAST( @YEAR AS VARCHAR(10))
		SET @PLAN_CODE= @DEP_CODE+(SELECT RIGHT(CAST( @YEAR AS VARCHAR(10)),2))

		IF(@PLAN_ID IS NULL OR @PLAN_ID='' )
		BEGIN
			EXEC SYS_CodeMasters_Gen 'PL_MASTER', @PLAN_ID out
			IF @PLAN_ID='' OR @PLAN_ID IS NULL GOTO ABORT	
	
			INSERT INTO dbo.PL_MASTER
			(
				PLAN_ID, PLAN_CODE,PLAN_NAME,BRANCH_ID,DEPT_ID,EFFECT_DT,PLAN_TYPE,TOTAL_AMT,APPROVE_VALUE, 
				VERSON,YEAR,STATUS,NOTES,RECORD_STATUS, MAKER_ID,CREATE_DT,AUTH_STATUS,CHECKER_ID,APPROVE_DT,PLAN_TYPE_ID,COST_ID)
			VALUES
			(   @PLAN_ID,@PLAN_CODE,@PLAN_NAME,@BRANCH_ID,@DEP_ID,GETDATE(),'',@IMP_AMT, @IMP_AMT,      
				1,@YEAR,'1', N'','1', @MARKER_ID,@EDIT_DT,@P_AUTH_STATUS, @P_CHECKER_ID, CAST(@P_APPROVE_DT AS DATE), @PLAN_TYPE_ID,@COST_ID  
			 )
		END
		ELSE
		BEGIN
		 UPDATE dbo.PL_MASTER SET MAKER_ID=@MARKER_ID,CREATE_DT=@EDIT_DT,AUTH_STATUS=@P_AUTH_STATUS,CHECKER_ID=@P_CHECKER_ID,APPROVE_DT=CAST(@P_APPROVE_DT AS DATE)
		 ,TOTAL_AMT=@IMP_AMT,APPROVE_VALUE=@IMP_AMT,VERSON=VERSON+1,PLAN_CODE=@PLAN_CODE,PLAN_NAME=@PLAN_NAME WHERE PLAN_ID=@PLAN_ID
		END
		


		 DECLARE Plan_Detail CURSOR FOR
		 SELECT IMP_DT_ID,GOOD_ID,QTY,TOTAL_AMT,NOTES,TRADE_ID,REF_CODE,REF_NAME,AMT_ETM,AMT_EXE,AMT_TF,AMT_RECEIVE_TF FROM dbo.PL_IMPORT_DT 
		 WHERE IMPORT_ID = @P_IMPORT_ID AND IS_UPDATE=1 AND PLAN_TYPE_ID=@PLAN_TYPE_ID AND ISNULL(COST_ID,'')=ISNULL(@COST_ID,'') AND BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID
		 OPEN Plan_Detail
			FETCH NEXT FROM Plan_Detail INTO @IMP_DT_ID,@GOOD_ID,@QTY,@TOTAL_AMT,@NOTES,@TRADE_ID,@REF_CODE,@REF_NAME,@AMT_ETM,@AMT_EXE,@AMT_TF,@AMT_RECIVE_TF
			WHILE @@FETCH_STATUS = 0	
			BEGIN
			IF(@TRADE_ID IS NULL OR @TRADE_ID='')
			BEGIN
			EXEC SYS_CodeMasters_Gen 'PL_TRADEDETAIL', @TRADE_ID out
			IF @TRADE_ID='' OR @TRADE_ID IS NULL GOTO ABORT	
			
			DELETE FROM dbo.PL_TRADE_REF WHERE TRADE_ID=@TRADE_ID

			INSERT INTO dbo.PL_TRADEDETAIL
			(
			    TRADE_ID,
			    PLAN_ID,
			    GOODS_ID,
			    QUANTITY,
			    AMT_APP,
			    NOTES,
			    RECORD_STATUS,
			    MAKER_ID,
			    CREATE_DT,
			    AUTH_STATUS,
			    CHECKER_ID,
			    APPROVE_DT,
				AMT_ETM,
				AMT_EXE,
				AMT_TF,
				AMT_RECEIVE_TF
			)
			VALUES
			(   @TRADE_ID,        -- TRADE_ID - varchar(15)
			    @PLAN_ID,        -- PLAN_ID - varchar(15)
			    @GOOD_ID,        -- GOODS_ID - varchar(15)
			    @QTY,      -- QUANTITY - decimal(18, 0)
			    @TOTAL_AMT,      -- PRICE - decimal(18, 0)			  
			   @NOTES,       -- NOTES - nvarchar(1000)
			    '1',        -- RECORD_STATUS - varchar(1)
			    @MARKER_ID,        -- MAKER_ID - varchar(12)
			   CAST( @EDIT_DT AS DATE), -- CREATE_DT - datetime
			    @P_AUTH_STATUS,        -- AUTH_STATUS - varchar(50)
			   @P_CHECKER_ID,        -- CHECKER_ID - varchar(12)
			  CAST( @P_APPROVE_DT AS DATE),  -- APPROVE_DT - datetime
			  @AMT_ETM,
			  @AMT_EXE,
			  @AMT_TF,
			  @AMT_RECIVE_TF
			    )	

				INSERT INTO dbo.PL_TRADE_REF
			(
			    TRADE_ID,
			    REF_CODE,
			    REF_NAME
			)
			VALUES
			(   @TRADE_ID, -- TRADE_ID - varchar(15)
			    @REF_CODE, -- REF_CODE - varchar(50)
			    @REF_NAME -- REF_NAME - nvarchar(500)
			    )
			
			END
			
			ELSE
			BEGIN
				UPDATE dbo.PL_TRADEDETAIL SET
				 QUANTITY=@QTY,
				 AMT_APP=@TOTAL_AMT,
				 NOTES=@NOTES,
				 MAKER_ID=@MARKER_ID,
				 CREATE_DT=CAST(@EDIT_DT AS DATE),
				 AUTH_STATUS=@P_AUTH_STATUS,
				 CHECKER_ID= @P_CHECKER_ID,
				 APPROVE_DT=CAST(@P_APPROVE_DT AS DATE)
				-- AMT_ETM=@AMT_ETM,
				-- AMT_EXE=@AMT_EXE,
				-- AMT_TF=@AMT_TF,
				-- AMT_RECEIVE_TF=@AMT_RECIVE_TF
				WHERE TRADE_ID=@TRADE_ID
			END

			

			UPDATE dbo.PL_IMPORT_DT SET PLAN_ID=@PLAN_ID,TRADE_ID=@TRADE_ID WHERE IMP_DT_ID=@IMP_DT_ID

			FETCH NEXT FROM Plan_Detail INTO @IMP_DT_ID,@GOOD_ID,@QTY,@TOTAL_AMT,@NOTES,@TRADE_ID,@REF_CODE,@REF_NAME,@AMT_ETM,@AMT_EXE,@AMT_TF,@AMT_RECIVE_TF
			END
			CLOSE Plan_Detail
			DEALLOCATE Plan_Detail
		FETCH NEXT FROM Plan_Master INTO @PLAN_CODE,@PLAN_NAME,@YEAR,@IMP_QTY,@IMP_AMT, @PLAN_TYPE_ID,@COST_ID,@BRANCH_ID,@DEP_ID,@PLAN_ID,@BRANCH_CODE,@DEP_CODE
	END
	CLOSE Plan_Master
	DEALLOCATE Plan_Master

UPDATE dbo.PL_IMPORT
SET AUTH_STATUS = @P_AUTH_STATUS,
    CHECKER_ID = @P_CHECKER_ID,
    APPROVE_DT = CAST( @P_APPROVE_DT AS DATE)
WHERE IMPORT_ID = @P_IMPORT_ID;

UPDATE dbo.PL_IMPORT_LOG SET AUTH_STATUS = @P_AUTH_STATUS,
    CHECKER_ID = @P_CHECKER_ID,
    APPROVE_DT = CAST( @P_APPROVE_DT AS DATE)
WHERE IMP_ID = @P_IMPORT_ID AND IMP_LOG_CODE= (SELECT TOP (1) IMP_LOG_CODE FROM dbo.PL_IMPORT_LOG WHERE IMP_ID=@P_IMPORT_ID ORDER BY IMP_LOG_ID DESC)


UPDATE dbo.PL_TRADEDETAIL SET PRICE = AMT_APP/ QUANTITY WHERE QUANTITY <> 0 OR QUANTITY IS NULL
UPDATE dbo.PL_TRADEDETAIL SET PRICE = AMT_APP WHERE QUANTITY = 0

UPDATE dbo.LIMIT_ACCUMULATE SET MAX_AMT=ISNULL(( 
SELECT TEMP.AMT_APP FROM (SELECT CD.DVDM_ID,SUM(ISNULL(PT.AMT_APP,0)) AS AMT_APP FROM dbo.CM_DVDM CD 
LEFT JOIN dbo.CM_GOOD_DVDM CGD ON CGD.KHOI_ID=CD.DVDM_ID
LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.GOODS_ID=CGD.GD_ID
WHERE CD.IS_KHOI=1 AND PT.PLAN_ID IN  (SELECT PLAN_ID FROM dbo.PL_MASTER WHERE YEAR=@IMPORT_YEAR)
GROUP BY CD.DVDM_ID
)TEMP WHERE TEMP.DVDM_ID=LIMIT_ACCUMULATE.DVDM_ID
),0) * (SELECT LIMIT_PERCENT FROM dbo.TL_SYSROLE_LIMIT WHERE TL_SYSROLE_LIMIT.ROLE_ID=LIMIT_ACCUMULATE.ROLE_ID AND LIMIT_TYPE='DCNS')/100


COMMIT TRANSACTION;
SELECT '0' AS Result,
       '' ErrorDesc;
RETURN '0';
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION;
    SELECT '-1' AS Result,
			'' ErrorDesc;
    RETURN '-1';		
END CATCH
ABORT:
BEGIN
    ROLLBACK TRANSACTION;
    SELECT '-1' AS Result,
           '' ErrorDesc;
    RETURN '-1';
END;