DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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