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

ALTER PROCEDURE [dbo].[BUD_CONTRACT_CUST_MASTER_App]

@p_CONTRACT_ID varchar(15),

@p_VERSION_NO varchar(15),

@P_AUTH_STATUS VARCHAR(1),

@P_CHECKER_ID VARCHAR(12),

@P_APPROVE_DT VARCHAR(25),

@P_TYPE_CONTRACT VARCHAR(1),

@P_XML_TEMP XML

AS

BEGIN TRANSACTION 

		DECLARE @_BUILDING_UTIL_LOG VARCHAR(15)

		DECLARE @p_VALUE_ID VARCHAR(15)

		DECLARE @p_MONTH DECIMAL(18,0)

		DECLARE @VALUE_ERROR DECIMAL(18,0)

		DECLARE @p_RENT_PRICE DECIMAL(18,0)

		DECLARE @TOATAL_INTERNAL DECIMAL(18,0)

		DECLARE @STT INT

		SET @STT =0

		DECLARE @_BUILDING_ID VARCHAR(15)

		DECLARE @_BUILDING_AREA_ID VARCHAR(15)

		DECLARE @_RENT_AREA DECIMAL(18,0)

		DECLARE @_RENT_PRICE_OLD DECIMAL(18,0)

		DECLARE @tmp table(CONTRACT_DTID VARCHAR(15), BUILDING_AREA_ID varchar(15),RENT_AREA DECIMAL(18,2),RENT_PRICE DECIMAL(18,2))

		insert into @tmp  SELECT CONTRACT_DTID, BUILDING_AREA_ID,RENT_AREA,RENT_PRICE  FROM BUD_CONTRACT_CUST_DT

												   WHERE CONTRACT_ID =@p_CONTRACT_ID AND VERSION_NO =@p_VERSION_NO-1

		-----BAODNQ 20/1/2022--------------------

		DECLARE @p_VAT_INIT NUMERIC(18,0)

		SET @p_VAT_INIT = (SELECT VAT FROM BUD_CONTRACT_CUST_MASTER WHERE CONTRACT_ID = @p_CONTRACT_ID)

		-----*** INSERT VERSION -1 VAO TRONG BANG LOG

		IF @@Error <> 0 GOTO ABORT

		Declare @hdoc1 INT

		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,

				@d_RENT_AREA_APP DECIMAL,

				@d_AREA_REMAIN DECIMAL,

				@d_RENT_PRICE DECIMAL,

				@d_NOTES	NVARCHAR(MAX),

				@d_DIEN_TICH_TANG DECIMAL,

				@d_DIEN_TICH_SD_NOI_BO DECIMAL,

				@d_DIEN_TICH_DA_CHO_THUE DECIMAL,

				@d_DIEN_TICH_CON_LAI DECIMAL,

				@d_DIEN_TICH_CAN_CHOTHUE DECIMAL,

				@d_PRICE DECIMAL,

				@d_TOTAL_AMT DECIMAL,

				@d_DIEN_TICH_CON_LAI_FINAL DECIMAL

		Exec sp_xml_preparedocument @hdoc1 Output,@p_XML_TEMP

		DECLARE XmlDataDoc CURSOR FOR

		SELECT *

		FROM OPENXML(@hdoc1,'/Root/XmlData1',2)

		WITH 

		(	

				CONTRACT_DTID   VARCHAR(15),

				BUILD_ID VARCHAR(15),

				VERSION_NO INT,

				BUILDING_AREA_ID VARCHAR(15),

				RENT_AREA DECIMAL,

				RENT_AREA_APP DECIMAL,

				AREA_REMAIN DECIMAL,

				RENT_PRICE DECIMAL,

				NOTES	NVARCHAR(MAX),

				DIEN_TICH_TANG DECIMAL,

				DIEN_TICH_SD_NOI_BO DECIMAL,

				DIEN_TICH_DA_CHO_THUE DECIMAL,

				DIEN_TICH_CON_LAI DECIMAL,

				DIEN_TICH_CAN_CHOTHUE DECIMAL,

				PRICE DECIMAL,

				TOTAL_AMT DECIMAL,

				DIEN_TICH_CON_LAI_FINAL DECIMAL

		)

		OPEN XmlDataDoc			

		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_CHOTHUE,

									@d_PRICE,

									@d_TOTAL_AMT,

									@d_DIEN_TICH_CON_LAI_FINAL

		WHILE @@FETCH_STATUS = 0

		BEGIN	

		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 

							 ) 

							  --+(SELECT SUM(RENT_AREA) FROM BUD_CONTRACT_DT

								 --WHERE CONTRACT_ID =@p_CONTRACT_ID AND CONTRACT_ID IN(SELECT CONTRACT_ID FROM

								 --BUD_CONTRACT_MASTER WHERE AUTH_STATUS='A'))

		--DIEN TICH CHO THUE PHAI LON HON DIEN TICH CON LAI+ DIEN TICH VERSION CŨ TRONG TRUONG

		--HOP TAO PHU LUC HOP DONG							--													

		IF ( @d_DIEN_TICH_CAN_CHOTHUE > (@VALUE_ERROR + @d_RENT_AREA_APP))

				BEGIN

					DECLARE @ERRORSYS NVARCHAR(200) =''

					SET @ERRORSYS = N'Dòng '+ convert(nvarchar(2),@stt) +N': Cột diện tích cho thuê phải nhỏ hơn hoặc bằng cột DT trống: '+ convert(nvarchar(20),@VALUE_ERROR)

				END

				IF @ERRORSYS <> '' 

				BEGIN

					CLOSE XmlDataDoc

					DEALLOCATE XmlDataDoc

					ROLLBACK TRANSACTION

					SELECT '-1' as Result, '' CONTRACT_ID, @ERRORSYS ErrorDesc

					RETURN '-1'

				END 

		----**** INSERT VERSION CURRENT -1-------------------------------------------------------------------------

		--IF(@p_VERSION_NO>1)

		--BEGIN

		--	SET @_BUILDING_AREA_ID= (SELECT TOP(1) BUILDING_AREA_ID FROM @tmp)

		--	SET @_RENT_AREA= (SELECT TOP(1) RENT_AREA FROM @tmp)

		--	SET @p_RENT_PRICE= (SELECT TOP(1) RENT_PRICE FROM @tmp)

		--	EXEC SYS_CodeMasters_Gen 'BUD_UTIL_LOG', @_BUILDING_UTIL_LOG out

		--	IF @_BUILDING_UTIL_LOG='' OR @_BUILDING_UTIL_LOG IS NULL GOTO ABORT

		--	INSERT INTO BUD_UTIL_LOG([UTIL_ID],[BUILDING_AREA_ID],[UTIL_TYPE],[UTIL_RELEASE],[CONTRACT_ID],[VERSION_NO],[UTIL_DT],[AREA],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT])

		--	VALUES(@_BUILDING_UTIL_LOG ,@_BUILDING_AREA_ID ,@P_TYPE_CONTRACT ,'R' ,@p_CONTRACT_ID ,@p_VERSION_NO-1 ,CONVERT(DATETIME, GETDATE(), 103) ,@_RENT_AREA ,'1' ,'A' ,@P_CHECKER_ID ,CONVERT(DATETIME, GETDATE(), 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, GETDATE(), 103) )

		--	------**** INSERT INTO CONTRACT_VALUES ---------------------------------------------------------------

		--	EXEC SYS_CodeMasters_Gen 'CONTRACT_VALUES', @p_VALUE_ID out

		--	IF @p_VALUE_ID='' OR @p_VALUE_ID IS NULL GOTO ABORT

		--	INSERT INTO BUD_CONTRACT_VALUES VALUES (@p_VALUE_ID,@p_CONTRACT_ID,@p_VERSION_NO-1,@_BUILDING_AREA_ID,@P_TYPE_CONTRACT,'R',@p_RENT_PRICE*@_RENT_AREA,NULL,@d_NOTES,

		--										'1','A',@P_CHECKER_ID,CONVERT(DATETIME,GETDATE(),103),@P_CHECKER_ID,CONVERT(DATETIME,GETDATE(),103))

		--	DELETE @tmp WHERE CONTRACT_DTID = (SELECT TOP(1) CONTRACT_DTID FROM @tmp)

		--END

		-----***INSERT VERSION CURRENT------------------------------------------------------------------

		DECLARE @l_UTIL_ID VARCHAR(15)

		EXEC SYS_CodeMasters_Gen 'BUD_UTIL_LOG', @l_UTIL_ID out

		IF @l_UTIL_ID='' OR @l_UTIL_ID IS NULL GOTO ABORT

		-----BAODNQ 17/1/2022: Thêm cột vào bảng BUD_UTIL_LOG

		DECLARE @p_CUSTOMER_ID VARCHAR(15) = 

				(SELECT CUST_ID FROM BUD_CONTRACT_CUST_MASTER WHERE CONTRACT_ID = @p_CONTRACT_ID)

		DECLARE @p_IS_FIRST_TIME VARCHAR(1)

		IF(NOT EXISTS 

			(SELECT * FROM BUD_UTIL_LOG 

				WHERE CONTRACT_ID = @p_CONTRACT_ID 

				AND BUILDING_AREA_ID = @d_BUILDING_AREA_ID

				AND CUSTOMER_ID = @p_CUSTOMER_ID

				AND IS_FIRST_TIME = 'Y'))

		BEGIN

			SET @p_IS_FIRST_TIME = 'Y'

		END

		ELSE

		BEGIN

			SET @p_IS_FIRST_TIME = 'N'

		END

		INSERT INTO BUD_UTIL_LOG([UTIL_ID],[BUILDING_AREA_ID],[UTIL_TYPE],[UTIL_RELEASE],[CONTRACT_ID],

					[VERSION_NO],[UTIL_DT],[AREA],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],

					[APPROVE_DT], [TOTAL_AMT_INIT], [VAT_INIT], [IS_FIRST_TIME], [CUSTOMER_ID])

			VALUES(@l_UTIL_ID ,@d_BUILDING_AREA_ID ,@P_TYPE_CONTRACT ,'U' ,@p_CONTRACT_ID ,@p_VERSION_NO ,

					CONVERT(DATETIME, GETDATE(), 103) ,@d_DIEN_TICH_CAN_CHOTHUE ,'1' ,'A' ,@P_CHECKER_ID ,

					CONVERT(DATETIME, GETDATE(), 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, GETDATE(), 103), 

					@d_TOTAL_AMT, @p_VAT_INIT, @p_IS_FIRST_TIME, @p_CUSTOMER_ID)

		---*** UPDATE CONTRACT_VALUES-------------------------------------------------------

		EXEC SYS_CodeMasters_Gen 'CONTRACT_VALUES', @p_VALUE_ID out

			IF @p_VALUE_ID='' OR @p_VALUE_ID IS NULL GOTO ABORT

			INSERT INTO BUD_CONTRACT_VALUES VALUES (@p_VALUE_ID,@p_CONTRACT_ID,@p_VERSION_NO,@d_BUILDING_AREA_ID,@P_TYPE_CONTRACT,'U',@d_PRICE*@d_DIEN_TICH_CAN_CHOTHUE,NULL,@d_NOTES,

												'1','A',@P_CHECKER_ID,CONVERT(DATETIME,GETDATE(),103),@P_CHECKER_ID,CONVERT(DATETIME,GETDATE(),103))

		---*** UPDATE DIEN TICH CHO KHACH THUE 

		BEGIN

		UPDATE BUD_AREA_DT

		--SET UTILZED_AREA =(

		--	(SELECT ISNULL(SUM(AREA),0) FROM BUD_UTIL_LOG WHERE BUILDING_AREA_ID=@d_BUILDING_AREA_ID AND UTIL_RELEASE='U' AND UTIL_TYPE=@P_TYPE_CONTRACT)

		--	  -

		--	(SELECT ISNULL(SUM(AREA),0) FROM BUD_UTIL_LOG WHERE BUILDING_AREA_ID=@d_BUILDING_AREA_ID AND UTIL_RELEASE='R' AND UTIL_TYPE =@P_TYPE_CONTRACT)

		--	)

		SET UTILZED_AREA = UTILZED_AREA + @d_DIEN_TICH_CAN_CHOTHUE

		WHERE BUILDING_AREA_ID =@d_BUILDING_AREA_ID

		---****** UPDATE BUD_CONTRACT_CUST_DT ******************************************---------

		UPDATE BUD_CONTRACT_CUST_DT

		SET RENT_AREA_APP = RENT_AREA_APP + @d_DIEN_TICH_CAN_CHOTHUE,

			--DIEN_TICH_DA_CHO_THUE = DIEN_TICH_DA_CHO_THUE + @d_DIEN_TICH_CAN_CHOTHUE,

			AREA_REMAIN = AREA_REMAIN - @d_DIEN_TICH_CAN_CHOTHUE,

			--DIEN_TICH_CON_LAI_FINAL = DIEN_TICH_CON_LAI_FINAL - @d_DIEN_TICH_CAN_CHOTHUE

			DIEN_TICH_CON_LAI_FINAL = DIEN_TICH_CON_LAI - @d_DIEN_TICH_CAN_CHOTHUE

		WHERE CONTRACT_DTID = @d_CONTRACT_DTID

		END

		------------------------------------------------------------------------------------------

		IF @@Error <> 0 GOTO ABORT	

		-----BAODNQ 17/1/2022: Insert vào BUD_CONTRACT_LOG khi trưởng dv duyệt thành công

		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_CHOTHUE,

									@d_PRICE,

									@d_TOTAL_AMT,

									@d_DIEN_TICH_CON_LAI_FINAL

		END

		CLOSE XmlDataDoc

		DEALLOCATE XmlDataDoc

		--**** UPDATE BUD_CONTRACT_CUST_MASTER*************

		SET @p_RENT_PRICE = (SELECT SUM(TOTAL_AMT) FROM BUD_CONTRACT_CUST_DT 

														WHERE CONTRACT_ID =@p_CONTRACT_ID

														AND VERSION_NO=@p_VERSION_NO)

		SET @p_MONTH= (SELECT MONTHS FROM BUD_CONTRACT_CUST_MASTER WHERE CONTRACT_ID=@p_CONTRACT_ID 

															 AND   VERSION_NO=@p_VERSION_NO)												

		UPDATE BUD_CONTRACT_CUST_MASTER

		SET RENT_PRICE=@p_RENT_PRICE *@p_MONTH

		WHERE CONTRACT_ID=@p_CONTRACT_ID AND VERSION_NO=@p_VERSION_NO

		---*** UPDATE BUD_MASTER-------------------------------

		SET @_BUILDING_ID =(SELECT BUILDING_ID FROM BUD_CONTRACT_CUST_MASTER WHERE CONTRACT_ID =@p_CONTRACT_ID AND VERSION_NO =@p_VERSION_NO)

		UPDATE BUD_MASTER

		SET UTILZED_AREA = (SELECT ISNULL(SUM(UTILZED_AREA),0) FROM BUD_AREA_DT WHERE BUILD_ID =@_BUILDING_ID)

		WHERE BUILDING_ID=@_BUILDING_ID

		---***UPDATE DIEN TICH CHO THUE TOAN BO TOA NHA

		--SET @TOATAL_INTERNAL  =  (SELECT ISNULL(SUM(INTERNAL_AREA),0) FROM BUD_AREA_DT WHERE BUILD_ID =@_BUILDING_ID)

		--UPDATE BUD_MASTER

		--SET RENT_TOTAL_AREA = TOTAL_AREA -@TOATAL_INTERNAL

		--WHERE BUILDING_ID = @_BUILDING_ID

		UPDATE BUD_CONTRACT_CUST_MASTER SET AUTH_STATUS = 'A', CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)

		WHERE VERSION_NO=@p_VERSION_NO AND CONTRACT_ID =@p_CONTRACT_ID

		---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,       

						'APPROVE',

					    @P_CHECKER_ID,        

					    GETDATE(), 

					    N'Trưởng đơn vị duyệt hợp đồng khách thuê thành công' ,      

					    N'Trưởng đơn vị duyệt hợp đồng khách thuê'       

					 )

		-----BAODNQ 17/1/2022: Insert vào BUD_CONTRACT_LOG khi trưởng dv duyệt thành công

		--DECLARE @p_CUSTOMER_ID VARCHAR(15), 

		--		@p_TOTAL_AMT DECIMAL(18,0), 

		--		@p_IS_FIRST_TIME VARCHAR(1),

		--		@p_VAT DECIMAL(18,0)

		--SET @p_CUSTOMER_ID = (SELECT CUST_ID FROM BUD_CONTRACT_CUST_MASTER WHERE CONTRACT_ID = @p_CONTRACT_ID)

		--SET @p_TOTAL_AMT = (SELECT VAT_AMT FROM BUD_CONTRACT_CUST_MASTER WHERE CONTRACT_ID = @p_CONTRACT_ID)

		--SET @p_VAT = (SELECT VAT FROM BUD_CONTRACT_CUST_MASTER WHERE CONTRACT_ID = @p_CONTRACT_ID)

		--IF(NOT EXISTS (SELECT * FROM BUD_CONTRACT_LOG WHERE CONTRACT_ID = @p_CONTRACT_ID AND IS_FIRST_TIME = 'Y'))

		--BEGIN

		--	SET @p_IS_FIRST_TIME = 'Y'

		--END

		--ELSE

		--BEGIN

		--	SET @p_IS_FIRST_TIME = 'N'

		--END

		--INSERT INTO BUD_CONTRACT_LOG 

		--	VALUES(@p_CONTRACT_ID, @p_CUSTOMER_ID, @p_TOTAL_AMT, @p_VAT ,@p_IS_FIRST_TIME, 

		--			CONVERT(DATETIME,@P_APPROVE_DT,103))

		--IF @@ERROR <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result, @p_VERSION_NO  VERSION_NO, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' CONTRACT_ID, '' ErrorDesc

		RETURN '-1'

End