ALTER PROCEDURE [dbo].[BUD_CONTRACT_MASTER_Ins]
@p_CONTRACT_ID	varchar(15)  = NULL,
@p_BUILDING_ID	varchar(15)  = NULL,
@p_CONTRACT_NO	nvarchar(100)  = NULL,
@p_VERSION_NO  int = NULL,
@p_CUST_ID	varchar(15)  = NULL,
@p_INPUT_DT	VARCHAR(20) = NULL,
@p_AMEND_DT	VARCHAR(20) = NULL,
@p_FROM_DT	VARCHAR(20) = NULL,
@p_TO_DATE	VARCHAR(20) = NULL,
@p_CLOSE_DT	VARCHAR(20) = NULL,
@p_MONTHS	int=0,
@p_RENT_PRICE	numeric(18)  = NULL,
@p_NOTES		NVARCHAR(MAX)= NULL,
@p_BRANCH_ID  VARCHAR(15) = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS	varchar(1)  = NULL,
@p_STATUS       VARCHAR(1) = NULL,
@p_MAKER_ID	varchar(15)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(15)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_XML_TEMP	xml = NULL,
@p_VAT DECIMAL(18,0),
@p_VAT_AMT DECIMAL(18,0),
@p_DEPOSITS_AMT DECIMAL(18,0),
@p_PAYMENT_SHEDULE	NVARCHAR(500),
@p_IS_SEND_APPR VARCHAR(15),
@p_SEND_APPR_DT VARCHAR(20),
@p_SIGN_USER VARCHAR(15),
@p_SIGN_DT VARCHAR(15)
AS
		---BAODNQ 30/12/2021:Thêm cột vào bảng BUD_CONTRACT_MASTER-------
		IF (EXISTS ( SELECT * FROM BUD_CONTRACT_MASTER WHERE CONTRACT_NO=@p_CONTRACT_NO AND VERSION_NO=@p_VERSION_NO))
		BEGIN
			SELECT '-1' Result, '' CONTRACT_ID, N'Số hợp đồng này đã tồn tại' ErrorDesc
			RETURN '0'
		END
		BEGIN TRANSACTION

		DECLARE @STT INT = 0

		DECLARE @l_CONTRACT_ID VARCHAR(15)
		IF(LEN(	@p_CONTRACT_ID)=0 OR @p_CONTRACT_ID IS NULL) 	
		EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_MASTER', @p_CONTRACT_ID out
		IF @p_CONTRACT_ID='' OR @p_CONTRACT_ID IS NULL GOTO ABORT
		INSERT INTO BUD_CONTRACT_MASTER([CONTRACT_ID],[BUILDING_ID],[CONTRACT_NO],[VERSION_NO],[CUST_ID],
					[INPUT_DT],[AMEND_DT],[FROM_DT],[TO_DATE],[CLOSE_DT],[MONTHS],[RENT_PRICE],[NOTES],
					[BRANCH_ID],[RECORD_STATUS],[AUTH_STATUS],[STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],
					[APPROVE_DT],[XML_TEMP],[VAT],[VAT_AMT],[DEPOSITS_AMT],[PAYMENT_SHEDULE],[IS_SEND_APPR],[SEND_APPR_DT],
					[SIGN_USER], [SIGN_DT])
		VALUES(@p_CONTRACT_ID ,@p_BUILDING_ID ,@p_CONTRACT_NO ,@p_VERSION_NO ,@p_CUST_ID ,CONVERT(DATE, @p_INPUT_DT, 103) ,
				CONVERT(DATE, @p_AMEND_DT, 103) ,CONVERT(DATE, @p_FROM_DT, 103) ,CONVERT(DATE, @p_TO_DATE, 103) ,
				CONVERT(DATE, @p_CLOSE_DT, 103) ,@p_MONTHS ,@p_RENT_PRICE ,@p_NOTES,@p_BRANCH_ID,
				'1' ,'U','N',@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,NULL ,@p_XML_TEMP,@p_VAT,
				(@p_VAT*@p_RENT_PRICE)/100,@p_DEPOSITS_AMT,@p_PAYMENT_SHEDULE,@p_IS_SEND_APPR,
				@p_SEND_APPR_DT,@p_SIGN_USER,@p_SIGN_DT)
		-----------------------------------------------------------------------------------------------------
		----INSERT VAO BANG BUD_CONTACT
		DELETE FROM BUD_CONTACT WHERE CONTRACT_ID = @p_CONTRACT_ID
		INSERT INTO BUD_CONTACT VALUES(@p_CONTRACT_ID,@p_VERSION_NO,@p_BUILDING_ID,@p_CONTRACT_NO,@p_CUST_ID,CONVERT(DATE, @p_CREATE_DT, 103))
		IF @@Error <> 0 GOTO ABORT
		Declare @hdoc1 INT
		
		----BAODNQ : Thêm các cột vào bảng BUD_CONTRACT_DT---

		DECLARE 
				@d_CONTRACT_DTID   VARCHAR(15),	
				@d_BUILD_ID VARCHAR(15),
				@d_VERSION_NO INT,
				@d_BUILDING_AREA_ID VARCHAR(15),
				@d_RENT_AREA DECIMAL(18,2),
				@d_RENT_AREA_APP DECIMAL(18,2),
				@d_AREA_REMAIN DECIMAL(18,2),
				@d_RENT_PRICE DECIMAL,
				@d_NOTES	NVARCHAR(MAX),
				@d_DIEN_TICH_TANG DECIMAL(18,2),
				@d_DIEN_TICH_SD_NOI_BO DECIMAL(18,2),
				@d_DIEN_TICH_DA_CHO_THUE DECIMAL(18,2),
				@d_DIEN_TICH_CON_LAI DECIMAL(18,2),
				@d_DIEN_TICH_CAN_SD DECIMAL(18,2),
				@d_REASON NVARCHAR(MAX),
				@d_PRICE DECIMAL,
				@d_TOTAL_AMT DECIMAL,
				@d_DIEN_TICH_CON_LAI_FINAL DECIMAL(18,2),
				@d_BRANCH_ID NVARCHAR(15),
				@d_DEP_ID NVARCHAR(15),
				@d_VAT INT,
				@d_PAY_PHASE VARCHAR(15)
				
		Exec sp_xml_preparedocument @hdoc1 Output,@p_XML_TEMP
		DECLARE XmlDataDoc CURSOR FOR
		SELECT *
		FROM OPENXML(@hdoc1,'/Root/XmlData1',2)
		WITH 
		(	
		----BAODNQ : Thêm các cột vào bảng BUD_CONTRACT_DT---
				CONTRACT_DTID   VARCHAR(15),
				BUILD_ID VARCHAR(15),
				VERSION_NO INT,
				BUILDING_AREA_ID VARCHAR(15),
				RENT_AREA DECIMAL(18,2),
				RENT_AREA_APP DECIMAL(18,2),
				AREA_REMAIN DECIMAL(18,2),
				RENT_PRICE DECIMAL,
				NOTES	NVARCHAR(MAX),
				DIEN_TICH_TANG DECIMAL(18,2),
				DIEN_TICH_SD_NOI_BO DECIMAL(18,2),
				DIEN_TICH_DA_CHO_THUE DECIMAL(18,2),
				DIEN_TICH_CON_LAI DECIMAL(18,2),
				DIEN_TICH_CAN_SD DECIMAL(18,2),
				REASON NVARCHAR(MAX),
				PRICE DECIMAL,
				TOTAL_AMT DECIMAL,
				DIEN_TICH_CON_LAI_FINAL DECIMAL(18,2),
				BRANCH_ID NVARCHAR(15),
				DEP_ID NVARCHAR(15),
				VAT INT,
				PAY_PHASE VARCHAR(15)
		)
		OPEN XmlDataDoc		
		----BAODNQ : Thêm các cột vào bảng BUD_CONTRACT_DT---
		FETCH NEXT FROM XmlDataDoc INTO 
									@d_CONTRACT_DTID,	
									@d_BUILD_ID,
									@d_VERSION_NO,
									@d_BUILDING_AREA_ID,
									@d_RENT_AREA,
									@d_RENT_AREA_APP,
									@d_AREA_REMAIN,
									@d_RENT_PRICE,
									@d_NOTES,
									@d_DIEN_TICH_TANG,
									@d_DIEN_TICH_SD_NOI_BO,
									@d_DIEN_TICH_DA_CHO_THUE,
									@d_DIEN_TICH_CON_LAI,
									@d_DIEN_TICH_CAN_SD,
									@d_REASON,
									@d_PRICE,
									@d_TOTAL_AMT,
									@d_DIEN_TICH_CON_LAI_FINAL,
									@d_BRANCH_ID,
									@d_DEP_ID,
									@d_VAT,
									@d_PAY_PHASE
									
		WHILE @@FETCH_STATUS = 0
		BEGIN

			-------------BAODNQ 11/3/2022: Check lỗi insert--------
			DECLARE @VALUE_ERROR DECIMAL(18,0)

			SET @STT= @STT+1
			SET @VALUE_ERROR = (
				SELECT FLOOR_AREA - (UTILZED_AREA + INTERNAL_AREA) 
				FROM BUD_AREA_DT WHERE  BUILDING_AREA_ID = @d_BUILDING_AREA_ID 
			) 
		
			DECLARE @ERRORSYS NVARCHAR(200) =''
			--IF ( @d_DIEN_TICH_CAN_SD > (@VALUE_ERROR + @d_RENT_AREA_APP) OR @d_DIEN_TICH_CAN_SD > @d_DIEN_TICH_CON_LAI_FINAL)
			IF ( @d_DIEN_TICH_CAN_SD > (@VALUE_ERROR + @d_RENT_AREA_APP) OR @d_DIEN_TICH_CAN_SD > @d_DIEN_TICH_CON_LAI)
			BEGIN
				SET @ERRORSYS = 
				N'Lưới chi tiết DTSD nội bộ, ' + 
				N'Dòng '+ convert(nvarchar(2),@stt) +N': DT cần sử dụng phải nhỏ hơn hoặc bằng DT trống '
			END

			--IF ( @d_DIEN_TICH_CAN_SD = 0)
			--BEGIN
			--	SET @ERRORSYS = 
			--	N'Lưới chi tiết DTSD nội bộ, ' + 
			--	N'Dòng '+ convert(nvarchar(2),@stt) +N': DT cần sử dụng phải lớn hơn 0 '
			--END

			IF @ERRORSYS <> '' 
			BEGIN
				CLOSE XmlDataDoc
				DEALLOCATE XmlDataDoc
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' CONTRACT_ID, @ERRORSYS ErrorDesc
				RETURN '-1'
			END 


		---BAODNQ: insert thêm vào các cột trong bảng BUD_CONTRACT_DT---
			EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_DT', @d_CONTRACT_DTID out
			IF @d_CONTRACT_DTID='' OR @d_CONTRACT_DTID IS NULL GOTO ABORT
						INSERT INTO BUD_CONTRACT_DT([CONTRACT_DTID],[BUILD_ID],[CONTRACT_ID],[VERSION_NO],[BUILDING_AREA_ID],[RENT_AREA],[RENT_AREA_APP],
							[AREA_REMAIN],[RENT_PRICE],[NOTES], [DIEN_TICH_TANG], [DIEN_TICH_SD_NOI_BO],[DIEN_TICH_DA_CHO_THUE],[DIEN_TICH_CON_LAI],
							[DIEN_TICH_CAN_SD],[REASON],[PRICE],[TOTAL_AMT],[DIEN_TICH_CON_LAI_FINAL],[BRANCH_ID],[DEP_ID], [VAT], [PAY_PHASE])
			VALUES(@d_CONTRACT_DTID ,@d_BUILD_ID ,@p_CONTRACT_ID ,@d_VERSION_NO ,@d_BUILDING_AREA_ID ,@d_RENT_AREA,0,@d_AREA_REMAIN,@d_RENT_PRICE,@d_NOTES,
					@d_DIEN_TICH_TANG,@d_DIEN_TICH_SD_NOI_BO,@d_DIEN_TICH_DA_CHO_THUE,@d_DIEN_TICH_CON_LAI,@d_DIEN_TICH_CAN_SD,@d_REASON,
					@d_PRICE,@d_TOTAL_AMT,@d_DIEN_TICH_CON_LAI_FINAL,@d_BRANCH_ID,@d_DEP_ID, @d_VAT, @d_PAY_PHASE)
			IF @@Error <> 0 GOTO ABORT	
			FETCH NEXT FROM XmlDataDoc INTO 
									@d_CONTRACT_DTID,	
									@d_BUILD_ID,
									@d_VERSION_NO,
									@d_BUILDING_AREA_ID,
									@d_RENT_AREA,
									@d_RENT_AREA_APP,
									@d_AREA_REMAIN,
									@d_RENT_PRICE,
									@d_NOTES,
									@d_DIEN_TICH_TANG,
									@d_DIEN_TICH_SD_NOI_BO,
									@d_DIEN_TICH_DA_CHO_THUE,
									@d_DIEN_TICH_CON_LAI,
									@d_DIEN_TICH_CAN_SD,
									@d_REASON,
									@d_PRICE,
									@d_TOTAL_AMT,
									@d_DIEN_TICH_CON_LAI_FINAL,
									@d_BRANCH_ID,
									@d_DEP_ID,
									@d_VAT,
									@d_PAY_PHASE
		END
		CLOSE XmlDataDoc
		DEALLOCATE XmlDataDoc
		---BAODNQ: sửa lấy tổng thành TOTAL_AMT---
		SET @p_RENT_PRICE = (SELECT SUM(TOTAL_AMT) FROM BUD_CONTRACT_DT 
															WHERE CONTRACT_ID =@p_CONTRACT_ID
															AND VERSION_NO=@p_VERSION_NO)
												
			UPDATE BUD_CONTRACT_MASTER
			--SET RENT_PRICE=@p_RENT_PRICE*MONTHS
			SET RENT_PRICE = @p_RENT_PRICE
			WHERE CONTRACT_ID=@p_CONTRACT_ID AND VERSION_NO=@p_VERSION_NO

			---BAODNQ 29/12/2021: Insert vào PL_PROCESS lưu lịch sử xử lý
			INSERT INTO dbo.PL_PROCESS
					(
					    REQ_ID,
					    PROCESS_ID,
					    CHECKER_ID,
					    APPROVE_DT,
					    PROCESS_DESC,
					    NOTES
					)
					VALUES
					(   @p_CONTRACT_ID,       
						'INSERT',
					    @p_MAKER_ID,        
					    GETDATE(), 
					    N'Thêm mới thông tin khai báo DTSD nội bộ thành công' ,      
					    N'Thêm mới thông tin khai báo DTSD nội bộ'       
					 )

COMMIT TRANSACTION
SELECT '0' as Result, @p_CONTRACT_ID  CONTRACT_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		--CLOSE XmlDataDoc
		--DEALLOCATE XmlDataDoc
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' CONTRACT_ID, '' ErrorDesc
		RETURN '-1'
End