ALTER PROCEDURE [dbo].[PL_IMPORT_Ins]
@p_PLAN_CODE VARCHAR(20),
@p_IMP_YEAR	INT  = NULL,
@p_IMP_NAME NVARCHAR(200),
@p_FILE_NAME	NVARCHAR(200)  = NULL,
@p_TOTAL_QTY	NUMERIC(18,0)  = NULL,
@p_TOTAL_ATM	NUMERIC(18,0)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(20)  = NULL,
@p_CREATE_DT	DATETIME = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(20)  = NULL,
@p_APPROVE_DT	DATETIME = NULL,
@p_XMLData XML=NULL
AS
BEGIN TRANSACTION

DECLARE @TableTmp TABLE(
		IMP_YEAR VARCHAR(4),
		PLAN_TYPE_ID VARCHAR(15),
		PLAN_TYPE_CODE VARCHAR(15),
		PLAN_TYPE_NAME NVARCHAR(200), 
		COST_ID VARCHAR(15),
		COST_CODE VARCHAR(100),
		COST_NAME NVARCHAR(200),
		BRANCH_ID VARCHAR(15),
		BRANCH_CODE VARCHAR(15),
		BRANCH_NAME NVARCHAR(200),
		DEP_ID	varchar(15),
		DEP_CODE VARCHAR(15),
		DEP_NAME NVARCHAR(200),
		REF_ID INT,
		REF_CODE VARCHAR(20),
		REF_NAME NVARCHAR(200),
		GOOD_ID varchar(15),
		GOOD_CODE VARCHAR(20),
		GOOD_NAME NVARCHAR(200),
		QTY INT,
		TOTAL_AMT DECIMAL(18,2),
		NOTES NVARCHAR(1000),
		AMT_EMT DECIMAL(18,2),
		AMT_EXE DECIMAL(18,2),
		AMT_TF DECIMAL(18,2),
		AMT_RECEIVE_TF DECIMAL(18,2)
);
	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output, @p_XMLData	
INSERT INTO @TableTmp
 SELECT *
	FROM OPENXML(@hDoc,'/Root/PLIMPORT',2)
	WITH 
	(
	    IMP_YEAR VARCHAR(4),
		PLAN_TYPE_ID VARCHAR(15),
		PLAN_TYPE_CODE VARCHAR(15),
		PLAN_TYPE_NAME NVARCHAR(200), 
		COST_ID VARCHAR(15),
		COST_CODE VARCHAR(100),
		COST_NAME NVARCHAR(200),
		BRANCH_ID VARCHAR(15),
		BRANCH_CODE VARCHAR(15),
		BRANCH_NAME NVARCHAR(200),
		DEP_ID	varchar(15),
		DEP_CODE VARCHAR(15),
		DEP_NAME NVARCHAR(200),
		REF_ID INT,
		REF_CODE VARCHAR(20),
		REF_NAME NVARCHAR(200),
		GOOD_ID varchar(15),
		GOOD_CODE VARCHAR(20),
		GOOD_NAME NVARCHAR(200),
		QTY INT,
		TOTAL_AMT DECIMAL(18,2),
		NOTES NVARCHAR(1000),
		AMT_EMT DECIMAL(18,2),
		AMT_EXE DECIMAL(18,2),
		AMT_TF DECIMAL(18,2),
		AMT_RECEIVE_TF DECIMAL(18,2)
	)
--Update ID
UPDATE @TableTmp  SET BRANCH_ID=(SELECT TOP 1 BRANCH_ID FROM dbo.CM_BRANCH WHERE dbo.CM_BRANCH.BRANCH_CODE=[@TableTmp].BRANCH_CODE),
DEP_ID=(SELECT TOP 1 DEP_ID FROM dbo.CM_DEPARTMENT WHERE dbo.CM_DEPARTMENT.DEP_CODE=[@TableTmp].DEP_CODE),
COST_ID=(SELECT TOP 1 DVDM_ID FROM dbo.CM_DVDM WHERE dbo.CM_DVDM.DVDM_CODE=[@TableTmp].COST_CODE AND IS_DVDM=1 ),
PLAN_TYPE_ID=(SELECT TOP 1 PLAN_TYPE_ID FROM dbo.CM_PLAN_TYPE WHERE dbo.CM_PLAN_TYPE.PLAN_TYPE_CODE=[@TableTmp].PLAN_TYPE_CODE),
GOOD_ID=(SELECT TOP 1 GD_ID FROM dbo.CM_GOODS WHERE dbo.CM_GOODS.GD_CODE=[@TableTmp].GOOD_CODE)

DECLARE @Error NVARCHAR(MAX)
--- Validate dữ liệu
IF(EXISTS(SELECT BRANCH_ID FROM @TableTmp WHERE BRANCH_ID IS NULL OR BRANCH_ID=''))
BEGIN

SET @Error=(select STUFF( (select ';' + BRANCH_CODE from @TableTmp  WHERE BRANCH_ID IS NULL OR BRANCH_ID='' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))	
	

		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Mã đơn vị chưa tồn tại trên hệ thống: '+ @Error ErrorDesc
		RETURN '-1'



END

IF(EXISTS(SELECT DEP_ID FROM @TableTmp WHERE DEP_ID IS NULL OR DEP_ID=''))
BEGIN

		
		SET @Error=(select STUFF( (select ';' + DEP_CODE from @TableTmp  WHERE DEP_ID IS NULL OR DEP_ID='' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))

		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Mã phòng ban chưa tồn tại trên hệ thống: '+@Error ErrorDesc
		RETURN '-1'
END

IF(EXISTS(SELECT COST_ID FROM @TableTmp WHERE (COST_ID IS NULL OR COST_ID='') AND COST_CODE IS NOT NULL AND COST_CODE <>''))
BEGIN

			SET @Error=(select STUFF( (select ';' + COST_CODE from @TableTmp  WHERE COST_ID IS NULL OR COST_ID='' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))	
		
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Mã đơn vị đầu mối chưa tồn tại trên hệ thống: '+@Error ErrorDesc
		RETURN '-1'
END

IF(EXISTS(SELECT PLAN_TYPE_ID FROM @TableTmp WHERE PLAN_TYPE_ID IS NULL OR PLAN_TYPE_ID=''))
BEGIN


		SET @Error=(select STUFF( (select ';' + PLAN_TYPE_CODE from @TableTmp  WHERE PLAN_TYPE_ID IS NULL OR PLAN_TYPE_ID='' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))	

		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Mã loại ngân sách chưa tồn tại trên hệ thống: '+@Error ErrorDesc
		RETURN '-1'
END

IF(EXISTS(SELECT GOOD_ID FROM @TableTmp WHERE GOOD_ID IS NULL OR GOOD_ID=''))
BEGIN

		SET @Error=(select STUFF( (select ';' + GOOD_CODE from @TableTmp  WHERE GOOD_ID IS NULL OR GOOD_ID='' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))	

		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Mã hạn mục chưa tồn tại trên hệ thống: '+@Error ErrorDesc
		RETURN '-1'
END


--Validate hạn mục
IF(EXISTS(SELECT * FROM dbo.PL_MASTER WHERE YEAR= CAST(@p_IMP_YEAR AS VARCHAR(4))))
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 EXISTS(
	SELECT Tmp.GOOD_ID FROM @TableTmp 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_ETM,0) AND  ISNULL(DT.QUANTITY_ETM,0) >0) OR (Tmp.TOTAL_AMT< (ISNULL(DT.AMT_ETM,0) + ISNULL(DT.AMT_TF,0) - ISNULL(DT.AMT_RECEIVE_TF,0)) AND (ISNULL(DT.AMT_ETM,0) + ISNULL(DT.AMT_TF,0) - ISNULL(DT.AMT_RECEIVE_TF,0)) >0) )
 ) OR (NOT EXISTS(SELECT Tmp.GOOD_ID FROM @TableTmp 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_ETM,0) > 0 OR (ISNULL(DT.AMT_ETM,0) + ISNULL(DT.AMT_TF,0) - ISNULL(DT.AMT_RECEIVE_TF,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


DECLARE @l_IMPORT_ID VARCHAR(15)
IF(EXISTS(SELECT IMPORT_ID FROM dbo.PL_IMPORT WHERE IMP_YEAR=@p_IMP_YEAR ))
BEGIN
	SET @l_IMPORT_ID=(SELECT IMPORT_ID FROM PL_IMPORT WHERE IMP_YEAR=@p_IMP_YEAR )
	UPDATE dbo.PL_IMPORT_DT SET IS_UPDATE=0  WHERE IMPORT_ID= @l_IMPORT_ID
	UPDATE dbo.PL_IMPORT SET PLAN_CODE=@p_PLAN_CODE,IMP_NAME=@p_IMP_NAME,FILE_NAME=@p_FILE_NAME,IMP_YEAR=@p_IMP_YEAR,IMP_QTY=@p_TOTAL_QTY,
	IMP_ATM=@p_TOTAL_ATM,NOTES=@p_NOTES,EDITER_ID=@p_MAKER_ID,EDIT_DT= CAST(@p_CREATE_DT AS DATE),CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CAST(@p_APPROVE_DT AS DATE),AUTH_STATUS=@p_AUTH_STATUS,IS_UPDATE=0
	WHERE IMPORT_ID=@l_IMPORT_ID
END
ELSE
BEGIN
		EXEC SYS_CodeMasters_Gen 'PL_IMPORT', @l_IMPORT_ID out
		IF @l_IMPORT_ID='' OR @l_IMPORT_ID IS NULL GOTO ABORT

		INSERT INTO dbo.PL_IMPORT
		(
		    IMPORT_ID,
			PLAN_CODE,
		    IMP_NAME,
		    FILE_NAME,
		    IMP_YEAR,
		    IMP_QTY,
		    IMP_ATM,
		    NOTES,
		    RECORD_STATUS,
		    AUTH_STATUS,
		    MAKER_ID,
		    CREATE_DT,
		    EDITER_ID,
		    EDIT_DT,
		    CHECKER_ID,
		    APPROVE_DT,IS_UPDATE
		)
		VALUES
		(   @l_IMPORT_ID,        -- IMPORT_ID - varchar(20)
		    @p_PLAN_CODE,
			@p_IMP_NAME,       -- IMP_NAME - nvarchar(500)
		    @p_FILE_NAME,       -- FILE_NAME - nvarchar(200)
		    @p_IMP_YEAR,         -- IMP_YEAR - int
		    @p_TOTAL_QTY,      -- IMP_QTY - numeric(18, 0)
		    @p_TOTAL_ATM,      -- IMP_ATM - numeric(18, 2)
		    @p_NOTES,       -- NOTES - nvarchar(1000)
		    @p_RECORD_STATUS,        -- RECORD_STATUS - varchar(1)
		    @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(1)
		    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
		    @p_CREATE_DT, -- CREATE_DT - datetime
		    @p_MAKER_ID,        -- EDITER_ID - varchar(15)
		    CAST(@p_CREATE_DT AS DATE), -- EDIT_DT - datetime
		    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
		    CAST(@p_APPROVE_DT AS DATE),0  -- APPROVE_DT - datetime
		    )
END


	DECLARE ImportDT CURSOR FOR
	SELECT IMP_YEAR,
           PLAN_TYPE_ID,
           PLAN_TYPE_CODE,
           PLAN_TYPE_NAME,
           COST_ID,
           COST_CODE,
           COST_NAME,
           BRANCH_ID,
           BRANCH_CODE,
           BRANCH_NAME,
           DEP_ID,
			DEP_CODE,
			DEP_NAME,
           GOOD_ID,
           GOOD_CODE,
           GOOD_NAME,
           QTY,
           TOTAL_AMT,
           NOTES,REF_CODE,REF_NAME,AMT_EMT,AMT_EXE,AMT_TF,AMT_RECEIVE_TF FROM @TableTmp
	OPEN ImportDT




	DECLARE 
	@IMP_YEAR VARCHAR(4),
	@PLAN_TYPE_ID VARCHAR(15),
	@PLAN_TYPE_CODE VARCHAR(15),
	@PLAN_TYPE_NAME NVARCHAR(200), 
	@COST_ID VARCHAR(15),
	@COST_CODE VARCHAR(15),
	@COST_NAME NVARCHAR(200),
	@BRANCH_ID VARCHAR(15),
	@BRANCH_CODE VARCHAR(15),
	@BRANCH_NAME NVARCHAR(200),
	@DEP_ID	varchar(15),
	@DEP_CODE VARCHAR(15),
	@DEP_NAME NVARCHAR(200),
	@REF_ID INT,
	@REF_CODE VARCHAR(20),
	@REF_NAME NVARCHAR(200),
	@GOOD_ID varchar(15),
	@GODD_CODE VARCHAR(15),
	@GOOD_NAME NVARCHAR(200),@QTY INT,@TOTAL_AMT DECIMAL(18,2),@NOTES NVARCHAR(1000),@AMT_EMT DECIMAL(18,2),
	@AMT_EXE DECIMAL(18,2),
	@AMT_TF DECIMAL(18,2),
	@AMT_RECEIVE_TF DECIMAL(18,2)

	,@IMP_LOG_CODE VARCHAR(15)
	EXEC SYS_CodeMasters_Gen 'IMP_LOG_CODE', @IMP_LOG_CODE out
	IF @IMP_LOG_CODE='' OR @IMP_LOG_CODE IS NULL GOTO ABORT		

	FETCH NEXT FROM ImportDT INTO @IMP_YEAR, @PLAN_TYPE_ID, @PLAN_TYPE_CODE,@PLAN_TYPE_NAME,@COST_ID,@COST_CODE,@COST_NAME,@BRANCH_ID,@BRANCH_CODE,
	@BRANCH_NAME,@DEP_ID,@DEP_CODE,@DEP_NAME,@GOOD_ID,@GODD_CODE,@GOOD_NAME,@QTY,@TOTAL_AMT,@NOTES,@REF_CODE,@REF_NAME,@AMT_EMT,@AMT_EXE,@AMT_TF,@AMT_RECEIVE_TF
	
	WHILE @@FETCH_STATUS = 0	
	BEGIN			
		
	
			

		DECLARE @l_IMPORTDT_ID VARCHAR(20)

		SET @l_IMPORTDT_ID= (SELECT TOP 1 IMP_DT_ID FROM  PL_IMPORT_DT WHERE IMPORT_ID=@l_IMPORT_ID AND ISNULL(PLAN_TYPE_ID,'')=ISNULL(@PLAN_TYPE_ID,'') AND ISNULL(COST_ID,'')=ISNULL(@COST_ID,'') AND BRANCH_ID=@BRANCH_ID AND ISNULL(REF_CODE,'')= ISNULL(@REF_CODE,'')
		AND ISNULL(DEP_ID,'')=ISNULL(@DEP_ID,'') AND ISNULL(GOOD_ID,'')=ISNULL(@GOOD_ID,''))
		IF(@l_IMPORTDT_ID IS NULL OR @l_IMPORTDT_ID='')
		BEGIN
			EXEC SYS_CodeMasters_Gen 'IMPORT_DT', @l_IMPORTDT_ID out
			IF @l_IMPORTDT_ID='' OR @l_IMPORTDT_ID IS NULL GOTO ABORT	
				INSERT INTO dbo.PL_IMPORT_DT
			(
		    IMP_DT_ID,
		    IMPORT_ID,
		    PLAN_TYPE_ID,
			PLAN_TYPE_CODE,
			PLAN_TYPE_NAME,
		    COST_ID,
			COST_CODE,
			COST_NAME,
		    BRANCH_ID,
			BRANCH_CODE,
			BRANCH_NAME,
		    DEP_ID,
			DEP_CODE,
			DEP_NAME,
			REF_CODE,
			REF_NAME,
		    GOOD_ID,
			GOOD_CODE,
			GOOD_NAME,
		    QTY,
		    TOTAL_AMT,
		    NOTES,
			IMP_YEAR,
			IS_UPDATE
		)
		VALUES
		(   @l_IMPORTDT_ID,   -- IMP_DT_ID - varchar(20)
		    @l_IMPORT_ID,   -- IMPORT_ID - varchar(20)
		   @PLAN_TYPE_ID, @PLAN_TYPE_CODE,@PLAN_TYPE_NAME,@COST_ID,@COST_CODE,@COST_NAME,@BRANCH_ID,@BRANCH_CODE,
	        @BRANCH_NAME,@DEP_ID,@DEP_CODE,@DEP_NAME,@REF_CODE,@REF_NAME,@GOOD_ID,@GODD_CODE,@GOOD_NAME,@QTY,@TOTAL_AMT,@NOTES,@IMP_YEAR,1
		  )
		END
	
		ELSE
		BEGIN
			UPDATE dbo.PL_IMPORT_DT SET QTY=@QTY,TOTAL_AMT=@TOTAL_AMT,REF_NAME=@REF_NAME,IS_UPDATE=1
			WHERE IMP_DT_ID=@l_IMPORTDT_ID
		END
		  INSERT INTO dbo.PL_IMPORT_LOG
		  (
		     IMP_LOG_CODE,
		      IMP_ID,
		      FILE_NAME,
		      IMP_YEAR,
		      NOTES,
		    RECORD_STATUS,
		    AUTH_STATUS,
		    MAKER_ID,
		    CREATE_DT,
		    EDITER_ID,
		    EDIT_DT,
		    CHECKER_ID,
		    APPROVE_DT,
			PLAN_TYPE_ID,
			PLAN_TYPE_CODE,
			PLAN_TYPE_NAME,
		    COST_ID,
			COST_CODE,
			COST_NAME,
		    BRANCH_ID,
			BRANCH_CODE,
			BRANCH_NAME,
		    DEP_ID,
			DEP_CODE,
			DEP_NAME,
			REF_CODE,
			REF_NAME,
		    GOOD_ID,
			GOOD_CODE,
			GOOD_NAME,
		      QTY,
		      TOTAL_AMT
		  )
		  VALUES
		  (   
				@IMP_LOG_CODE,
		      @l_IMPORT_ID,        -- IMP_ID_OLD - varchar(20)
		      @p_FILE_NAME,       -- FILE_NAME - nvarchar(200)
		      @p_IMP_YEAR,         -- IMP_YEAR - int
		      @p_NOTES,       -- NOTES - nvarchar(1000)
		      @p_RECORD_STATUS,        -- RECORD_STATUS - varchar(1)
		      @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(1)
		      @p_MAKER_ID,        -- MAKER_ID - varchar(15)
		      @p_CREATE_DT, -- CREATE_DT - datetime
			  @p_MAKER_ID,        -- EDITER_ID - varchar(15)
		      CAST(@p_CREATE_DT AS DATE), -- EDIT_DT - datetime
		      @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
		      CAST(@p_APPROVE_DT AS DATE),  -- APPROVE_DT - datetime
		      @PLAN_TYPE_ID, @PLAN_TYPE_CODE,@PLAN_TYPE_NAME,@COST_ID,@COST_CODE,@COST_NAME,@BRANCH_ID,@BRANCH_CODE,
	        @BRANCH_NAME,@DEP_ID,@DEP_CODE,@DEP_NAME,@REF_CODE,@REF_NAME,@GOOD_ID,@GODD_CODE,@GOOD_NAME,
		      @QTY,    -- QTY - int
		      @TOTAL_AMT -- TOTAL_AMT - numeric(18, 2)
		      )
		  	
		  	
	
		IF @@Error <> 0 GOTO ABORT	
	
		FETCH NEXT FROM ImportDT INTO  @IMP_YEAR, @PLAN_TYPE_ID, @PLAN_TYPE_CODE,@PLAN_TYPE_NAME,@COST_ID,@COST_CODE,@COST_NAME,@BRANCH_ID,@BRANCH_CODE,
	@BRANCH_NAME,@DEP_ID,@DEP_CODE,@DEP_NAME,@GOOD_ID,@GODD_CODE,@GOOD_NAME,@QTY,@TOTAL_AMT,@NOTES,@REF_CODE,@REF_NAME,@AMT_EMT,@AMT_EXE,@AMT_TF,@AMT_RECEIVE_TF
	
	END
	CLOSE ImportDT
	DEALLOCATE ImportDT
	SET @p_TOTAL_ATM=(SELECT SUM(ISNULL(TOTAL_AMT,0)) FROM dbo.PL_IMPORT_DT WHERE IMPORT_ID=@l_IMPORT_ID)
	SELECT @p_TOTAL_QTY=(SELECT SUM(ISNULL(QTY,0)) FROM dbo.PL_IMPORT_DT WHERE IMPORT_ID=@l_IMPORT_ID)
	UPDATE dbo.PL_IMPORT SET IMP_QTY=@p_TOTAL_QTY,IMP_ATM=@p_TOTAL_ATM WHERE IMPORT_ID=@l_IMPORT_ID

		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
SELECT '0' as Result, @l_IMPORT_ID  IMPORT_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, '' ErrorDesc
		RETURN '-1'
End