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