ALTER PROCEDURE [dbo].[REAL_ESTATE_R_H_Ins] 
			@p_BRANCH_MANAGE nvarchar(500) = NULL,
			@p_BRANCH_USE nvarchar(500) = NULL,
			@p_RET_ADDR nvarchar(MAX) = NULL,
			@p_RET_R_H_TIME decimal = NULL,
			@p_START_DT VARCHAR(20) = NULL,
			@p_END_DT VARCHAR(20) = NULL,
			@p_R_LAND_SQUARE decimal = NULL,
			@p_SCALE NVARCHAR(500) = NULL,
			@p_CONSTRUCT_SQUARE decimal = NULL,
			@p_PRICE_DETAIL XML = NULL,
			@p_DEPOSIT varchar(1) = NULL,
			@p_DEPOSIT_PRICE decimal = NULL,
			@p_DEPOSIT_RETURN_DT VARCHAR(20) = NULL,
			@p_PURCHASE_EACH_TERM decimal = NULL,
			@p_PAY_AMT decimal = NULL,
			@p_PAY_PAY_AMT decimal = NULL,
			@p_PAY_MONTH_TIMES INT = NULL,
			@p_NOTES nvarchar(1000) = NULL,
			@p_RECORD_STATUS varchar(1) = NULL,
			@p_AUTH_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_MONTH_TIMES INT = NULL,
			@p_BRANCH_CREATE varchar(15) = NULL,
			----BAODNQ 7/2/2022: Thêm cột vào bảng REAL_ESTATE_R_H------
			@p_TR_CONTRACT_ID varchar(15) = NULL,
			@p_EXTEND_DT varchar(20) = NULL,
			@p_STRUCTURE nvarchar(500) = NULL,
			@p_IS_SEND_APPR VARCHAR(15) = NULL,
			@p_SEND_APPR_DT VARCHAR(20) = NULL,
			@p_SIGN_USER VARCHAR(15) = NULL,
			@p_SIGN_DT VARCHAR(20) = NULL
AS
DECLARE @l_ERROR_MESSAGE VARCHAR(1000)
BEGIN TRANSACTION
	
	IF(CONVERT(DATETIME, @p_END_DT, 103) < CONVERT(DATETIME, @p_START_DT, 103))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RET_R_H_ID, 
			N'Ngày kết thúc không được nhỏ hơn ngày bắt đầu' ErrorDesc
		RETURN '-1'
	END

	IF(CONVERT(DATETIME, @p_EXTEND_DT, 103) < CONVERT(DATETIME, @p_START_DT, 103))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RET_R_H_ID, 
			N'Ngày gia hạn hợp đồng không được nhỏ hơn ngày bắt đầu' ErrorDesc
		RETURN '-1'
	END

	IF(
		(@p_DEPOSIT_RETURN_DT IS NOT NULL OR @p_DEPOSIT_RETURN_DT <> '') 
		AND CONVERT(DATETIME, @p_DEPOSIT_RETURN_DT, 103) < CONVERT(DATETIME, @p_START_DT, 103)
		)
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' RET_R_H_ID, 
			N'Thời điểm hoàn lại tiền cọc không được nhỏ hơn ngày bắt đầu' ErrorDesc
		RETURN '-1'
	END

	DECLARE @p_PRICE_DT_ROW_NO INT = 0

	-- GEN ID
	DECLARE @p_RET_R_H_ID VARCHAR(30)
	DECLARE @p_KeyGen VARCHAR(15);
	EXEC dbo.SYS_CodeMasters_Gen 'REAL_ESTATE_R_H', @p_RET_R_H_ID out
	
	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_PRICE_DETAIL
	DECLARE PriceDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/RetPriceDetail',2)
	WITH 
	(
		RET_PRICE_ID	varchar(15),
		RET_R_H_ID	varchar(15),
		PRICE	decimal(18,0),
		PRICE_DT	VARCHAR(20),
		PRICE_END_DT	VARCHAR(20)
	)
	OPEN PriceDetail

	-- INSERT
	----BAODNQ 7/2/2022: Thêm cột vào bảng REAL_ESTATE_R_H------
	INSERT INTO REAL_ESTATE_R_H (RET_R_H_ID, BRANCH_MANAGE, BRANCH_USE, RET_ADDR, RET_R_H_TIME, START_DT, 
				END_DT,  R_LAND_SQUARE, SCALE, CONSTRUCT_SQUARE, DEPOSIT, DEPOSIT_PRICE, DEPOSIT_RETURN_DT, 
				PURCHASE_EACH_TERM, NOTES, RECORD_STATUS, AUTH_STATUS, MAKER_ID, CREATE_DT, CHECKER_ID, 
				APPROVE_DT,BRANCH_CREATE, MONTH_TIMES, PAY_AMT, PAY_MONTH_TIMES, PAY_PAY_AMT, TR_CONTRACT_ID, 
				EXTEND_DT, STRUCTURE, IS_SEND_APPR,SEND_APPR_DT,SIGN_USER,SIGN_DT)
	VALUES (@p_RET_R_H_ID, @p_BRANCH_MANAGE, @p_BRANCH_USE, @p_RET_ADDR, @p_RET_R_H_TIME, 
			CONVERT(DATETIME, @p_START_DT, 103), CONVERT(DATETIME, @p_END_DT, 103),  @p_R_LAND_SQUARE, @p_SCALE, 
			@p_CONSTRUCT_SQUARE, @p_DEPOSIT, @p_DEPOSIT_PRICE, CONVERT(DATETIME,@p_DEPOSIT_RETURN_DT, 103), @p_PURCHASE_EACH_TERM, 
			@p_NOTES, @p_RECORD_STATUS, @p_AUTH_STATUS, @p_MAKER_ID, CONVERT(DATETIME, @p_CREATE_DT, 103), 
			@p_CHECKER_ID, CONVERT(DATETIME, @p_APPROVE_DT, 103),@p_BRANCH_CREATE,@p_MONTH_TIMES,@p_PAY_AMT, 
			@p_PAY_MONTH_TIMES, @p_PAY_PAY_AMT, @p_TR_CONTRACT_ID, CONVERT(DATETIME, @p_EXTEND_DT, 103), 
			@p_STRUCTURE, @p_IS_SEND_APPR, CONVERT(DATETIME,@p_SEND_APPR_DT ,103), @p_SIGN_USER, CONVERT(DATETIME, @p_SIGN_DT, 103))

	IF @@Error <> 0 GOTO ABORT

	DECLARE @RET_PRICE_ID	varchar(15), @RET_R_H_ID	varchar(15), @PRICE	decimal(18,0), @PRICE_DT	VARCHAR(20), @PRICE_END_DT	VARCHAR(20)
	FETCH NEXT FROM PriceDetail INTO @RET_PRICE_ID, @RET_R_H_ID, @PRICE, @PRICE_DT, @PRICE_END_DT

	WHILE @@FETCH_STATUS = 0	
		BEGIN
			SET @p_PRICE_DT_ROW_NO = @p_PRICE_DT_ROW_NO + 1

			IF(CONVERT(DATETIME, @PRICE_END_DT, 103) <= CONVERT(DATETIME, @PRICE_DT, 103))
			BEGIN
				ROLLBACK TRANSACTION
				CLOSE PriceDetail
				DEALLOCATE PriceDetail
				SELECT '-1' as Result, '' RET_R_H_ID, 
					N'Lưới giá thuê: dòng' +CONVERT(VARCHAR, @p_PRICE_DT_ROW_NO)+
					N': Đến ngày không được nhỏ hơn từ ngày'ErrorDesc
				RETURN '-1'
			END

			EXEC SYS_CodeMasters_Gen 'RET_PRICE', @RET_PRICE_ID out
			IF @RET_PRICE_ID ='' OR @RET_PRICE_ID IS NULL GOTO ABORT
			INSERT INTO RET_PRICE(RET_PRICE_ID, RET_R_H_ID, PRICE, PRICE_DT, PRICE_END_DT)
			VALUES(@RET_PRICE_ID, @p_RET_R_H_ID, @PRICE, CONVERT(DATETIME, @PRICE_DT, 103), CONVERT(DATETIME, @PRICE_END_DT, 103))
			IF @@ERROR <> 0 GOTO ABORT
			FETCH NEXT FROM PriceDetail INTO @RET_PRICE_ID, @RET_R_H_ID, @PRICE, @PRICE_DT, @PRICE_END_DT
		END
		CLOSE PriceDetail
	DEALLOCATE PriceDetail


	--------BAODNQ 14/02/2022: 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_RET_R_H_ID,       
						'INSERT',
					    @p_MAKER_ID,        
					    GETDATE(), 
					    N'Thêm mới BDS thuê làm trụ sở CN/PGD thành công' ,      
					    N'Thêm mới BDS thuê làm trụ sở CN/PGD'       
					 )


COMMIT TRANSACTION
	SELECT '0' as Result, @p_RET_R_H_ID RET_R_H_ID, '' ErrorDesc
	RETURN '0'

ABORT:
BEGIN
	ROLLBACK TRANSACTION
	CLOSE PriceDetail
	DEALLOCATE PriceDetail
	SELECT '-1' as Result, '' RET_R_H_ID, @l_ERROR_MESSAGE ErrorDesc
	RETURN '-1'
END