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

ALTER PROCEDURE [dbo].[BUD_CONTRACT_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_DT

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

		-----*** 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_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_SD DECIMAL,

				@d_REASON NVARCHAR(MAX),

				@d_PRICE DECIMAL,

				@d_TOTAL_AMT DECIMAL,

				@d_DIEN_TICH_CON_LAI_FINAL DECIMAL,

				@d_BRANCH_ID NVARCHAR(15),

				@d_DEP_ID NVARCHAR(15)

		Exec sp_xml_preparedocument @hdoc1 Output,@p_XML_TEMP

		DECLARE XmlDataDoc CURSOR FOR

		SELECT *

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

		WITH 

		(	

		----BAODNQ-21/12/2021: Thêm các cột vào cursor XML-----

				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,

				DIEN_TICH_TANG DECIMAL,

				DIEN_TICH_SD_NOI_BO DECIMAL,

				DIEN_TICH_DA_CHO_THUE DECIMAL,

				DIEN_TICH_CON_LAI DECIMAL,

				DIEN_TICH_CAN_SD DECIMAL,

				REASON NVARCHAR(MAX),

				PRICE DECIMAL,

				TOTAL_AMT DECIMAL,

				DIEN_TICH_CON_LAI_FINAL DECIMAL,

				BRANCH_ID NVARCHAR(15),

				DEP_ID NVARCHAR(15)

		)

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

		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

		----BAODNQ-21/12/2021: Đổi @d_RENT_AREA thành @d_DIEN_TICH_CAN_SD-------

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

				BEGIN

					DECLARE @ERRORSYS NVARCHAR(200) =''

					SET @ERRORSYS = N'Dòng '+ convert(nvarchar(2),@stt) +N': Cột DT cần sử dụng 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_REASON,

		--										'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-21/12/2021: Đổi biến insert vào cột [AREA] thành @d_DIEN_TICH_CAN_SD + thêm cột vào bảng BUD_UTIL_LOG-------

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

		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 = @d_BRANCH_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_SD ,'1' ,'A' ,@P_CHECKER_ID ,

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

					0, @p_IS_FIRST_TIME, @d_BRANCH_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

			----BAODNQ-21/12/2021: Đổi biến insert vào cột [RENT_AMT] thành @d_TOTAL_AMT-------

			INSERT INTO BUD_CONTRACT_VALUES VALUES (@p_VALUE_ID,@p_CONTRACT_ID,@p_VERSION_NO,@d_BUILDING_AREA_ID,@P_TYPE_CONTRACT,'U',@d_TOTAL_AMT,NULL,@d_REASON,

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

		-----***UPDATE DIEN TICH NOI BO SU DUNG NEU TYPE = I:INTERNAL ------------------------------------------------------------------

		IF(@P_TYPE_CONTRACT='I')

		BEGIN

		UPDATE BUD_AREA_DT

		--SET INTERNAL_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 INTERNAL_AREA = INTERNAL_AREA + @d_DIEN_TICH_CAN_SD

		WHERE BUILDING_AREA_ID =@d_BUILDING_AREA_ID

		---****** UPDATE BUD_CONTRACT_DT ******************************************---------

		----BAODNQ-21/12/2021: Đổi biến insert vào cột [RENT_AREA_APP] thành @@d_DIEN_TICH_CAN_SD-------

		UPDATE BUD_CONTRACT_DT

		SET RENT_AREA_APP =RENT_AREA_APP + @d_DIEN_TICH_CAN_SD,

			DIEN_TICH_CON_LAI_FINAL = DIEN_TICH_CON_LAI - @d_DIEN_TICH_CAN_SD

		WHERE CONTRACT_DTID = @d_CONTRACT_DTID

		END

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

		IF @@Error <> 0 GOTO ABORT	

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

		--DECLARE @p_IS_FIRST_TIME VARCHAR(1)

		--IF(NOT EXISTS(SELECT*FROM BUD_CONTRACT_LOG WHERE CONTRACT_ID = @d_CONTRACT_DTID AND @p_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 (@d_CONTRACT_DTID, @d_BRANCH_ID, @d_TOTAL_AMT, 0,@p_IS_FIRST_TIME, CONVERT(DATETIME,@P_APPROVE_DT,103))

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

		END

		CLOSE XmlDataDoc

		DEALLOCATE XmlDataDoc

		--**** UPDATE BUD_CONTRACT_MASTER*************

		----BAODNQ-21/12/2021: Đổi cột thực hiện SUM 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)

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

															 AND   VERSION_NO=@p_VERSION_NO)												

		UPDATE BUD_CONTRACT_MASTER

		--SET RENT_PRICE=@p_RENT_PRICE *@p_MONTH

		SET RENT_PRICE=@p_RENT_PRICE

		WHERE CONTRACT_ID=@p_CONTRACT_ID AND VERSION_NO=@p_VERSION_NO

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

		SET @_BUILDING_ID =(SELECT BUILDING_ID FROM BUD_CONTRACT_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_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 thông tin khai báo DTSD nội bộ thành công' ,      

					    N'Trưởng đơn vị duyệt thông tin khai báo DTSD nội bộ'       

					 )

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