EOFFICE - GD4

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO

ALTER PROCEDURE [dbo].[TR_CONTRACT_Upd]

@p_CONTRACT_ID VARCHAR(15),

@p_CONTRACT_CODE	varchar(150)  = NULL,

@p_CONTRACT_NAME	nvarchar(200)  = NULL,

@p_CONTRACT_TYPE varchar(1) = NULL,

@p_BID_ID	varchar(15)  = NULL,

@p_SUP_ID	varchar(15)  = NULL,

@p_TOTAL_AMT	decimal(18)  = NULL,

@p_DELIVERY_DT	VARCHAR(20) = NULL,

@p_FORM1	varchar(4)  = NULL,

@p_VOUCHER_ID1	varchar(20)  = NULL,

@p_BANK1	varchar(4)  = NULL,

@p_AMOUNT1	decimal(18)  = NULL,

@p_EXP_DT1	VARCHAR(20) = NULL,

@p_RATE1	decimal(18,2)  = NULL,

@p_FORM2	varchar(4)  = NULL,

@p_VOUCHER_ID2	varchar(20)  = NULL,

@p_BANK2	varchar(4)  = NULL,

@p_AMOUNT2	decimal(18)  = NULL,

@p_EXP_DT2	VARCHAR(20) = NULL,

@p_RATE2	decimal(18,2)  = NULL,

@p_REQ_DOC_ID	nvarchar(50)  = NULL,

@p_REQ_DOC_CONTENT	nvarchar(1000)  = NULL,

@p_APPROVE_VALUE	decimal(18)  = NULL,

@p_SIGN_DT	VARCHAR(20) = NULL,

@p_CONSTRUCT_PROGRESS	decimal(18,2)  = NULL,

@p_NOTES	nvarchar(1000)  = NULL,

@p_RECORD_STATUS	varchar(1)  = NULL,

@p_MAKER_ID	varchar(12)  = NULL,

@p_CREATE_DT	VARCHAR(20) = NULL,

@p_AUTH_STATUS	varchar(50)  = NULL,

@p_CHECKER_ID	varchar(12)  = NULL,

@p_APPROVE_DT	VARCHAR(20) = NULL,

@P_START_DT NVARCHAR(20) = NULL,

@P_END_DT NVARCHAR(20) = NULL,

@P_LISTASSET XML = NULL,

@P_LISTPAYMENT XML = NULL,

@P_CON_TRACK XML = NULL,

@p_ListTrREQFile XML,

@p_IS_CLOSED VARCHAR(1) = NULL,

@p_CONT_TYPE VARCHAR(10) = NULL,

@p_CUST_ID VARCHAR(15) = NULL,

@p_BRANCH_ID VARCHAR(15) = NULL,

@p_EXP_CONTRACT INT = NULL, 

@p_DEPOSIT_AMT DECIMAL(18,0) = NULL,

@p_TYPE_PERIOD VARCHAR(15) = NULL,

@p_DEPOSIT_NOTE NVARCHAR(1000) = NULL,

-----------------------BAODNQ 25/2/2022 : Thêm cột vào bảng TR_CONTRACT-------------

@p_IS_SEND_APPR VARCHAR(1) = NULL,

@p_SEND_APPR_DT VARCHAR(20) = NULL,

@p_ADDR NVARCHAR(1000) = NULL,

@p_SUP_CODE VARCHAR(20) = NULL

AS

---- 15.04.2023 LUCTV DIEU CHỈNH KIỂM TRA CÁC THÔNG TIN VỀ NGÀY HIỆU LỰC / NGÀY HẾT HIỆU LỰC / GIÁ TRỊ HỢP ĐỒNG NẾU CÓ ĐIỀU CHỈNH THÌ MỚI GHI NHẬN LOG, NGƯỢC LẠI NẾU KHÔNG THAY ĐỔI THÌ KHÔNG CẦN GHI LOG

		DECLARE @START_DATE_EXEC_OLD DATE, @END_DATE_EXEC_OLD DATE, @TOTAL_AMT_CONTRACT_OLD DECIMAL(18,0), @SUPPLIER_ID_OLD NVARCHAR(1000),@SUPPLIER_NAME_OLD NVARCHAR(1000), 

		@SUPPLIER_NAME NVARCHAR(1000)

		SET @START_DATE_EXEC_OLD = ISNULL((SELECT START_DT FROM TR_CONTRACT WHERE CONTRACT_ID =@p_CONTRACT_ID),'')

		SET @END_DATE_EXEC_OLD = ISNULL((SELECT END_DT FROM TR_CONTRACT WHERE CONTRACT_ID =@p_CONTRACT_ID),'')

		SET @TOTAL_AMT_CONTRACT_OLD = ISNULL((SELECT TOTAL_AMT FROM TR_CONTRACT WHERE CONTRACT_ID =@p_CONTRACT_ID),0)

		SET @SUPPLIER_ID_OLD = ISNULL((SELECT SUP_ID FROM TR_CONTRACT WHERE CONTRACT_ID =@p_CONTRACT_ID),'')

		SET @SUPPLIER_NAME_OLD =ISNULL((SELECT SUP_NAME FROM CM_SUPPLIER WHERE SUP_ID=@SUPPLIER_ID_OLD),'')

		SET @SUPPLIER_NAME =ISNULL((SELECT SUP_NAME FROM CM_SUPPLIER WHERE SUP_ID=@p_SUP_ID),'')

---- END LUCTV 15.04.2023

	IF(EXISTS(SELECT * FROM TR_CONTRACT WHERE CONTRACT_ID = @p_CONTRACT_ID AND AUTH_STATUS = 'U'))

	BEGIN

		SELECT '-1' as Result, @P_CONTRACT_ID  CONTRACT_ID, 

			N'Không thể chỉnh sửa. Hợp đồng ' + @p_CONTRACT_CODE + 

				N' đã được gửi yêu cầu phê duyệt' ErrorDesc

		RETURN '-1'

	END

	DECLARE @sErrorCode VARCHAR(20)

	DECLARE @TEMP TABLE

			(

				[KEY] varchar(15),

				[REF_ID] varchar(15),

				[TYPE] varchar(50)

			)

	--IF (@p_CONTRACT_CODE IS NOT NULL AND @p_CONTRACT_CODE <> '')

	--BEGIN

	--	IF EXISTS(SELECT * FROM TR_CONTRACT A WHERE A.CONTRACT_CODE = @p_CONTRACT_CODE AND A.CONTRACT_ID != @p_CONTRACT_ID )

	--	BEGIN

	--		SET @sErrorCode = 'TR-00001'

	--	END

	--END

	----------------BAODNQ 8/11/2022 : BỎ RÀNG BUỘC TRÙNG SỐ HỢP ĐỒNG------------

	--IF(@p_CONTRACT_CODE IS NOT NULL AND @p_CONTRACT_CODE <> '')

	--BEGIN

	--	IF(EXISTS (SELECT * FROM TR_CONTRACT WHERE CONTRACT_CODE = @p_CONTRACT_CODE AND CONTRACT_ID <> @p_CONTRACT_ID))

	--	BEGIN

	--		DECLARE @p_EXISTS_CONTRACT_ID VARCHAR(15) = (

	--			SELECT TOP 1 CONTRACT_ID FROM TR_CONTRACT WHERE CONTRACT_CODE = @p_CONTRACT_CODE AND CONTRACT_ID <> @p_CONTRACT_ID

	--		)

	--		SELECT '-1' AS Result, '' CONTRACT_ID,

	--			N'Số hợp đồng: ' + @p_CONTRACT_CODE + N' đã tồn tại trên hệ thống ứng với ID hợp đồng: ' 

	--			+ @p_EXISTS_CONTRACT_ID + N'. Vui lòng nhập số hợp đồng khác' ErrorDesc

	--		RETURN '-1'

	--	END

	--END

	IF 	@sErrorCode <> ''

	BEGIN

		SELECT '-1' as Result, ''  CONTRACT_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @sErrorCode

		RETURN '-1'

	END

	DECLARE @REF_CODE VARCHAR(15) = NULL, @PDN_TT VARCHAR(15) = NULL

	SET @REF_CODE =

	(	SELECT TOP 1 B.REQ_PAY_CODE

		FROM TR_REQ_ADVANCE_DT A

		INNER JOIN TR_REQ_ADVANCE_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID

		WHERE A.REF_ID =@p_CONTRACT_ID AND (B.AUTH_STATUS_KT <>'A' OR B.AUTH_STATUS_KT IS NULL)

	)

	--- TIM KIEM XEM PO NAY CO DUOC PHEP THANH TOAN HAY KHONG

	SET @PDN_TT =

	(	SELECT TOP 1 B.REQ_PAY_CODE

		FROM TR_REQ_ADVANCE_DT A

		INNER JOIN TR_REQ_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID

		WHERE A.REF_ID =@p_CONTRACT_ID AND (B.AUTH_STATUS_KT <>'A' OR B.AUTH_STATUS_KT IS NULL)

	)

	IF(@p_IS_CLOSED ='Y' AND @REF_CODE IS NOT NULL)

	BEGIN

			--ROLLBACK TRANSACTION

			SELECT '-1' Result, @p_CONTRACT_ID AS PO_ID, N'Hợp đồng đang được thực hiện tạm ứng với số phiếu: '+@REF_CODE AS ErrorDesc 

			RETURN '-1'

	END

	IF(@p_IS_CLOSED ='Y' AND @PDN_TT IS NOT NULL)

	BEGIN

		--ROLLBACK TRANSACTION

			SELECT '-1' Result, @p_CONTRACT_ID AS PO_ID, N'Hợp đồng đang được thực hiện thanh toán với số phiếu: '+@PDN_TT AS ErrorDesc 

			RETURN '-1'

	END

	----------------BAODNQ 14/11/2022 : NẾU LOẠI HỢP ĐỒNG = DK, KO BẮT BUỘC NHẬP LƯỚI DANH SÁCH HÀNG HÓA------------------

	---------------NGƯỢC LẠI NẾU LÀ (MS, NT), BẮT BUỘC NHẬP LƯỚI DANH SÁCH HÀNG HÓA----------------

	DECLARE @p_TR_CONTRACT_DT_XML_COUNT INT

	SET @p_TR_CONTRACT_DT_XML_COUNT = (

		SELECT @p_LISTASSET.value('count(/Root/AssetDetail/GOODS_ID)', 'INT') AS 'COUNT'

	)

	PRINT @p_TR_CONTRACT_DT_XML_COUNT

	IF(@p_CONT_TYPE <> 'DK' AND @p_TR_CONTRACT_DT_XML_COUNT <= 0)

	BEGIN

		SELECT '-1' Result, '' AS CONTRACT_ID, N'Đối với hợp đồng không phải là hợp đồng định kỳ, lưới danh sách hàng hóa bắt buộc nhập' AS ErrorDesc 

		RETURN '-1'

	END

	-----------------ENDBAODNQ-------------------

	Declare @hdoc INT

	Exec sp_xml_preparedocument @hdoc Output,@P_LISTASSET

	DECLARE AssetDetail CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/AssetDetail',2)

	WITH 

	(

		CD_ID VARCHAR(15),

		GOODS_ID	varchar(15),

		UNIT_ID	varchar(15),

		QUANTITY	decimal(18),

		PRICE	decimal(18),

		NOTES	nvarchar(1000),

		GD_NAME_REAL NVARCHAR(500),

		RECURRING VARCHAR(5),

		UNIT_NAME_REAL NVARCHAR(100),

		-------------------BAODNQ 23/2/2022: Thêm cột vào bảng TR_CONTRACT_DT-------------

		RENT_PRICE DECIMAL(18,0),

		BUILDING_ID VARCHAR(15)

	)

	OPEN AssetDetail

	Exec sp_xml_preparedocument @hdoc Output,@P_LISTPAYMENT

	DECLARE PaymentDetail CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/PaymentDetail',2)

	WITH 

	(

		PAY_ID	VARCHAR(15),

		PAY_PHASE	NVARCHAR(1000),

		EXPECTED_DT	VARCHAR(20),

		[PERCENT]	decimal(18,2),

		[AMOUNT]	decimal(18),

		PAY_STATUS VARCHAR(1),

		PAY_AMOUNT DECIMAL(18),

		PAY_DT VARCHAR(20),

		NOTES	nvarchar(1000)

	)

	OPEN PaymentDetail

	---- LUCTV 02082023_SECRETKEY XÓA NHỮNG LỊCH THANH TOÁN KHÔNG NẰM TRONG XML

	DELETE FROM TR_CONTRACT_PAYMENT WHERE CONTRACT_ID =@p_CONTRACT_ID AND PAY_ID NOT IN (SELECT ISNULL(PAY_ID,'') FROM OPENXML(@hDoc,'/Root/PaymentDetail',2)

	WITH 

	(

		PAY_ID	VARCHAR(15),

		PAY_PHASE	NVARCHAR(1000),

		EXPECTED_DT	VARCHAR(20),

		[PERCENT]	decimal(18,2),

		[AMOUNT]	decimal(18),

		PAY_STATUS VARCHAR(1),

		PAY_AMOUNT DECIMAL(18),

		PAY_DT VARCHAR(20),

		NOTES	nvarchar(1000)

	))

	Exec sp_xml_preparedocument @hdoc Output,@P_CON_TRACK

	DECLARE ConTrack CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/ConTrack',2)

	WITH 

	(

		CONS_TRACK_ID	VARCHAR(15),

		FIX_DESCRIPTION	varchar(200),

		EXPECTED_DT	VARCHAR(20),

		FINISH_DT	VARCHAR(20),

		[STATUS]	VARCHAR(1),

		NOTES	nvarchar(1000)

	)

	OPEN ConTrack

	PRINT 'PASS KHOI TAO'

BEGIN TRANSACTION

			--- 03.04.2023 KIỂM TRA NẾU NHÀ CUNG CẤP HIỆN HÀNH ĐANG TRỎ TỚI PO THÌ PHẢI CẢNH BÁO XEM XÉT LẠI & XÓA PO LƯU NHÁP ĐI

		IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE REQ_DOC_ID=@p_REQ_DOC_ID AND SUP_ID =@p_SUP_ID))

		BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' Result, '' AS CONTRACT_ID, N'Cập nhật hợp đồng thất bại. Hiện phiếu yêu cầu mua sắm này đang có PO số: ' +

							(SELECT TOP 1 ISNULL(PO_CODE,'') FROM TR_PO_MASTER WHERE REQ_DOC_ID=@p_REQ_DOC_ID AND SUP_ID =@p_SUP_ID)+

								N' sử dụng. Vui lòng kiểm tra lại PO và xóa PO này khỏi hệ thống' AS ErrorDesc 

				RETURN '-1'

		END

		--- END 03.04.2023

		---------------BAODNQ 12/12/2022 : HOT_FIX TẠM THỜI-------------------

		--------------NẾU KO TRUYỀN SUP_ID THÌ TỪ SUP_CODE TÌM KIẾM RA SUP_ID---------

		IF((@p_SUP_ID IS NULL OR @p_SUP_ID = '') AND @p_SUP_CODE IS NOT NULL AND @p_SUP_CODE <> '')

		BEGIN

			SET @p_SUP_ID = (SELECT TOP 1 SUP_ID FROM CM_SUPPLIER WHERE SUP_CODE = @p_SUP_CODE)

		END

		--------------ENDBAODNQ 12/12/2022----------------------------

		IF(@p_CONTRACT_NAME ='' OR @p_CONTRACT_NAME IS  NULL)

		BEGIN

			ROLLBACK TRANSACTION

				SELECT '-1' Result, @p_CONTRACT_ID AS PO_ID, N'Tên hợp đồng không được để trống' AS ErrorDesc 

				RETURN '-1'

		END

		-- 03.01.2025 BẮT BUỘC NHẬP NGÀY HIỆU LỰC/ NGÀY HẾT HIỆU LỰC/NGÀY GIAO HÀNG

        DECLARE @CONT_TYPE_NAME NVARCHAR(150)

		IF(@p_CONT_TYPE IN ('MS','NT','K') AND ((@p_SIGN_DT IS NULL OR @p_SIGN_DT = '') OR (@P_START_DT IS NULL OR @P_START_DT = '') OR (@P_END_DT IS NULL OR @P_END_DT = '') ) )

		BEGIN

			SELECT TOP 1 @CONT_TYPE_NAME = CONTENT FROM CM_ALLCODE  WHERE CDTYPE = 'CONTRACT' AND CDNAME = 'CONTRACT_TYPE' AND CDVAL = @p_CONT_TYPE

    			ROLLBACK TRANSACTION

					SELECT '-1' Result, '' AS PO_ID, N'Đối với hợp đồng ' + @CONT_TYPE_NAME + N': bắt buộc nhập Ngày ký hợp đồng/ngày hiệu lực/ngày hết hiệu lực!' AS ErrorDesc 

					RETURN '-1'

		END

		IF(@p_CONT_TYPE IN ('MS') AND( @p_REQ_DOC_ID IS NULL OR @p_REQ_DOC_ID = ''))

		BEGIN

			SELECT TOP 1 @CONT_TYPE_NAME = CONTENT FROM CM_ALLCODE  WHERE CDTYPE = 'CONTRACT' AND CDNAME = 'CONTRACT_TYPE' AND CDVAL = @p_CONT_TYPE

    			ROLLBACK TRANSACTION

					SELECT '-1' Result, '' AS PO_ID, N'Đối với hợp đồng ' + @CONT_TYPE_NAME + N': bắt buộc chọn phiếu yêu cầu mua sắm' AS ErrorDesc 

					RETURN '-1'

		END

		--insert master

		IF @p_DELIVERY_DT = ''  

			SET @p_DELIVERY_DT = NULL 

		IF @p_EXP_DT1 = ''

			SET @p_EXP_DT1 = NULL

		IF @p_EXP_DT2 = ''

			SET @p_EXP_DT2 = NULL

		IF @p_SIGN_DT = ''

			SET @p_SIGN_DT = NULL

		IF @p_CREATE_DT = ''

			SET @p_CREATE_DT = NULL

		IF @p_APPROVE_DT = ''

			SET @p_APPROVE_DT = NULL

		IF @P_START_DT = ''

			SET @P_START_DT = NULL

		IF @P_END_DT = ''

			SET @P_END_DT = NULL

		DECLARE @EXP_DT_OLD DATEtime, @TOTAL_AMT_OLD DECIMAL(18,0), @FROM_EXP_DATE_OLD DATEtime

		SET @FROM_EXP_DATE_OLD = (SELECT START_DT FROM TR_CONTRACT WHERE CONTRACT_ID =@p_CONTRACT_ID)

		SET @EXP_DT_OLD = (SELECT END_DT FROM TR_CONTRACT WHERE CONTRACT_ID =@p_CONTRACT_ID)

		SET @TOTAL_AMT_OLD = (SELECT TOTAL_AMT FROM TR_CONTRACT WHERE CONTRACT_ID =@p_CONTRACT_ID)

		IF(EXISTS (SELECT * FROM TR_CONTRACT WHERE CONTRACT_ID = @p_CONTRACT_ID AND AUTH_STATUS = 'A'))

		BEGIN

			SET @p_CHECKER_ID = NULL

			SET @p_APPROVE_DT = NULL

			SET @p_SEND_APPR_DT = NULL

		END

		UPDATE TR_CONTRACT SET [START_DT] = (CASE WHEN @P_START_DT IS NOT NULL AND @P_START_DT <> '' then CONVERT(DATETIME,@P_START_DT,103) ELSE NULL END), 

		[END_DT] = (CASE WHEN @P_END_DT IS NOT NULL AND @P_END_DT <> '' THEN CONVERT(DATETIME,@P_END_DT,103) ELSE NULL END), [CONTRACT_CODE] = @p_CONTRACT_CODE,

		[CONTRACT_NAME] = @p_CONTRACT_NAME, [CONTRACT_TYPE] = @p_CONTRACT_TYPE ,

		[BID_ID] = @p_BID_ID,[SUP_ID] = @p_SUP_ID,[TOTAL_AMT] = @p_TOTAL_AMT,

		[DELIVERY_DT] = (CASE WHEN @p_DELIVERY_DT IS NOT NULL AND @p_DELIVERY_DT <> '' then CONVERT(DATETIME, @p_DELIVERY_DT, 103) ELSE NULL END),

		[FORM1] = @p_FORM1,[VOUCHER_ID1] = @p_VOUCHER_ID1,[BANK1] = @p_BANK1,

		[AMOUNT1] = @p_AMOUNT1,[EXP_DT1] = (CASE WHEN @p_EXP_DT1 IS NOT NULL AND @p_EXP_DT1 <> '' then CONVERT(DATETIME, @p_EXP_DT1, 103) ELSE NULL END),

		[RATE1] = @p_RATE1,[FORM2] = @p_FORM2,[VOUCHER_ID2] = @p_VOUCHER_ID2,

		[BANK2] = @p_BANK2,[AMOUNT2] = @p_AMOUNT2,[EXP_DT2] = (CASE WHEN @p_EXP_DT2 IS NOT NULL AND @p_EXP_DT2 <> '' then CONVERT(DATETIME, @p_EXP_DT2, 103) ELSE NULL END),

		[RATE2] = @p_RATE2,[REQ_DOC_ID] = @p_REQ_DOC_ID,[REQ_DOC_CONTENT] = @p_REQ_DOC_CONTENT,

		[APPROVE_VALUE] = @p_APPROVE_VALUE,

		[SIGN_DT] =  (CASE WHEN @p_SIGN_DT IS NOT NULL AND @p_SIGN_DT <> '' then CONVERT(DATETIME, @p_SIGN_DT, 103) ELSE NULL END),

		[CONSTRUCT_PROGRESS] = @p_CONSTRUCT_PROGRESS,[NOTES] = @p_NOTES,[RECORD_STATUS] = '1',

		[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[AUTH_STATUS] = 'E',

		[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = (CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END),

		[CONT_TYPE]=@p_CONT_TYPE, CUST_ID = @p_CUST_ID,

		[IS_CLOSED] = @p_IS_CLOSED,

		BRANCH_ID=@p_BRANCH_ID,EXP_CONTRACT=@p_EXP_CONTRACT,DEPOSIT_AMT =@p_DEPOSIT_AMT,TYPE_PERIOD =@p_TYPE_PERIOD, DEPOSIT_NOTE = @p_DEPOSIT_NOTE,

		IS_SEND_APPR = @p_IS_SEND_APPR,

		SEND_APPR_DT = CONVERT(DATE, @p_SEND_APPR_DT, 103),

		[ADDRESS] = @p_ADDR

		WHERE  CONTRACT_ID= @p_CONTRACT_ID

		-- INSERT VAO LOG

		DECLARE @START_DATE_OLD_CONTENT NVARCHAR(100), @P_START_DT_CONTENT NVARCHAR(100),@END_DATE_EXEC_OLD_CONTENT NVARCHAR(100),@P_END_DT_CONTENT NVARCHAR(100),

		@TOTAL_PAYMENT_AMT_OLD DECIMAL(18,0),@TOTAL_PAYMENT_AMT_NEW DECIMAL(18,0)

		SET @TOTAL_PAYMENT_AMT_OLD =(SELECT SUM(AMOUNT) FROM TR_CONTRACT_PAYMENT WHERE CONTRACT_ID =@p_CONTRACT_ID)

		IF(@START_DATE_EXEC_OLD IS NULL OR @START_DATE_EXEC_OLD ='')

		BEGIN

			SET @START_DATE_OLD_CONTENT =N'vô thời hạn'

		END

		ELSE

		BEGIN

			SET @START_DATE_OLD_CONTENT =FORMAT (CONVERT(DATE,@START_DATE_EXEC_OLD,103), 'dd/MM/yyyy')

		END

		IF(@P_START_DT IS NULL OR @P_START_DT ='')

		BEGIN

			SET @P_START_DT_CONTENT =N'vô thời hạn'

		END

		ELSE

		BEGIN

			SET @P_START_DT_CONTENT =FORMAT (CONVERT(DATE,@P_START_DT,103), 'dd/MM/yyyy')

		END

		--- NGÀY HẾT HẠN

		IF(@END_DATE_EXEC_OLD IS NULL OR @END_DATE_EXEC_OLD ='')

		BEGIN

			SET @END_DATE_EXEC_OLD_CONTENT =N'vô thời hạn'

		END

		ELSE

		BEGIN

			SET @END_DATE_EXEC_OLD_CONTENT =FORMAT (CONVERT(DATE,@END_DATE_EXEC_OLD,103), 'dd/MM/yyyy')

		END

		IF(@P_END_DT IS NULL OR @P_END_DT ='')

		BEGIN

			SET @P_END_DT_CONTENT =N'vô thời hạn'

		END

		ELSE

		BEGIN

			SET @P_END_DT_CONTENT =FORMAT (CONVERT(DATE,@P_END_DT,103), 'dd/MM/yyyy')

		END

		IF @@Error <> 0 GOTO ABORT

		PRINT 'UPDATE MASTER SUCCESS'

		--insert asset detail tương đương như NỘI DUNG trong bảng contract detail

		Declare

		@CONS_TRACK_ID VARCHAR(15),

		@FIX_DESCRIPTION VARCHAR(200),

		@FINISH_DT VARCHAR(20),

		@STATUS  VARCHAR(1),

		@CD_ID VARCHAR(15),

		@PAY_ID VARCHAR(15),

		@GOODS_ID	varchar(15),

		@UNIT_ID	varchar(15),

		@QUANTITY	decimal(18),

		@PRICE	decimal(18),

		@NOTES	nvarchar(1000),

		@PAY_PHASE	NVARCHAR(1000),

		@EXPECTED_DT	VARCHAR(20),

		@PERCENT	decimal(18),

		@AMOUNT	decimal(18),

		@PAY_STATUS VARCHAR(1),

		@PAY_AMOUNT DECIMAL(18),

		@PAY_DT VARCHAR(20),

		@GD_NAME_REAL NVARCHAR(500),

		@RECURRING VARCHAR(5),

		@UNIT_NAME_REAL NVARCHAR(100),

		-------------------BAODNQ 23/2/2022: Thêm cột vào bảng TR_CONTRACT_DT-------------

		@RENT_PRICE DECIMAL(18,0),

		@BUILDING_ID VARCHAR(15)

		DELETE FROM TR_CONTRACT_DT WHERE CONTRACT_ID = @P_CONTRACT_ID

		FETCH NEXT FROM AssetDetail INTO @CD_ID,@GOODS_ID,@UNIT_ID,@QUANTITY,@PRICE,@NOTES,@GD_NAME_REAL,@RECURRING,@UNIT_NAME_REAL, @RENT_PRICE, @BUILDING_ID

		WHILE @@FETCH_STATUS = 0	

		BEGIN

			--IF(LEN(@CD_ID) = 0)

			--BEGIN

			--	EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_DT', @CD_ID out

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

			--END

			EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_DT', @CD_ID out

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

			--EXEC TR_CONTRACT_DT_Ins @P_CONTRACT_ID,@GOODS_ID,@UNIT_ID,@QUANTITY,@PRICE,@NOTES,@p_RECORD_STATUS,

			--@p_MAKER_ID,@p_CREATE_DT,@p_AUTH_STATUS,@p_CHECKER_ID,@p_APPROVE_DT

			--ELSE

			INSERT INTO TR_CONTRACT_DT([CD_ID],[CONTRACT_ID],[GOODS_ID],[UNIT_ID],[QUANTITY],[PRICE],[NOTES],

			[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[GD_NAME_REAL],RECURRING,UNIT_NAME_REAL, RENT_PRICE,BUILDING_ID)

			VALUES(@CD_ID ,@P_CONTRACT_ID,

			@GOODS_ID ,

			@UNIT_ID ,

			@QUANTITY ,

			@PRICE ,

			@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END),@GD_NAME_REAL,@RECURRING,@UNIT_NAME_REAL, @RENT_PRICE, @BUILDING_ID)

			FETCH NEXT FROM AssetDetail INTO @CD_ID,@GOODS_ID,@UNIT_ID,@QUANTITY,@PRICE,@NOTES,@GD_NAME_REAL,@RECURRING,@UNIT_NAME_REAL, @RENT_PRICE, @BUILDING_ID

		END

		DELETE FROM CON_TRACK WHERE CONTRACT_ID = @P_CONTRACT_ID

		FETCH NEXT FROM ConTrack INTO @CONS_TRACK_ID,@FIX_DESCRIPTION,@EXPECTED_DT,@FINISH_DT,@STATUS,@NOTES

		WHILE @@FETCH_STATUS = 0	

		BEGIN

			--IF(LEN(@CONS_TRACK_ID) = 0)

			--BEGIN

			--	EXEC SYS_CodeMasters_Gen 'CONS_TRACK_ID', @CONS_TRACK_ID out

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

			--END

			EXEC SYS_CodeMasters_Gen 'CONS_TRACK_ID', @CONS_TRACK_ID out

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

			INSERT INTO CON_TRACK([CONS_TRACK_ID],[CONTRACT_ID],[FIX_DESCRIPTION],[EXPECTED_DT],[STATUS],[FINISH_DT],[NOTES],[CREATED_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[RECORD_STATUS])

			VALUES(@CONS_TRACK_ID ,@p_CONTRACT_ID ,@FIX_DESCRIPTION ,CONVERT(DATETIME, @EXPECTED_DT, 103) ,

			@STATUS ,CONVERT(DATETIME, @FINISH_DT, 103) ,@NOTES ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END) ,@p_RECORD_STATUS )

			FETCH NEXT FROM ConTrack INTO @CONS_TRACK_ID,@FIX_DESCRIPTION,@EXPECTED_DT,@FINISH_DT,@STATUS,@NOTES

		END

	--insert payment detail

	--UPDATE  TR_CONTRACT_PAYMENT SET PAY_ID = RIGHT WHERE CONTRACT_ID = @P_CONTRACT_ID

	FETCH NEXT FROM PaymentDetail INTO @PAY_ID,@PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@PAY_STATUS,@PAY_AMOUNT,@PAY_DT,@NOTES

	WHILE @@FETCH_STATUS = 0

	BEGIN

		IF(LEN(ISNULL(@PAY_ID,'')) = 0 OR @PAY_ID IS NULL OR @PAY_ID = '')

		BEGIN

			EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_PAYMENT', @PAY_ID out

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

		END

		--IF(EXISTS(SELECT * FROM TR_CONTRACT_PAYMENT WHERE PAY_ID =@PAY_ID))

		--BEGIN

		--	UPDATE TR_CONTRACT_PAYMENT SET PAY_ID ='X' WHERE PAY_ID =@PAY_ID

		--END

		--EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_PAYMENT', @PAY_ID out

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

		--EXEC TR_CONTRACT_PAYMENT_Ins @p_CONTRACT_ID,@PAY_PHASE,@EXPECTED_DT,

		--@PERCENT,@AMOUNT,@NOTES,@p_RECORD_STATUS,@p_MAKER_ID,@p_CREATE_DT,@p_AUTH_STATUS,@p_CHECKER_ID,@p_APPROVE_DT

		--ELSE

		--INSERT INTO TR_CONTRACT_PAYMENT([PAY_AMOUNT],PAY_DT, [PAY_ID],[CONTRACT_ID],[PAY_PHASE],[EXPECTED_DT],[PERCENT],[AMOUNT],[PAY_STATUS],

		--[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])

		--VALUES(@PAY_AMOUNT,(CASE WHEN @PAY_DT IS NOT NULL AND @PAY_DT <> '' then CONVERT(DATETIME, @PAY_DT, 103) ELSE NULL END),@PAY_ID ,@p_CONTRACT_ID ,@PAY_PHASE ,CONVERT(DATETIME, @EXPECTED_DT, 103) ,

		--@PERCENT ,@AMOUNT,@PAY_STATUS,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103),

		--@p_AUTH_STATUS ,@p_CHECKER_ID ,(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END))

		-------------BAODNQ 15/9/2022: Fix lại theo BVB_v2_PROD-----------

		-------------Nếu @PAY_ID ko tồn tại trong TR_CONTRACT_PAYMENT, INSERT mới---------

		IF(NOT EXISTS(SELECT * FROM TR_CONTRACT_PAYMENT WHERE PAY_ID = @PAY_ID))

		BEGIN

			INSERT INTO TR_CONTRACT_PAYMENT

				([PAY_AMOUNT],PAY_DT, [PAY_ID],[CONTRACT_ID],[PAY_PHASE],[EXPECTED_DT],[PERCENT],[AMOUNT],[PAY_STATUS],

				[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])

			VALUES(@PAY_AMOUNT,(CASE WHEN @PAY_DT IS NOT NULL AND @PAY_DT <> '' then CONVERT(DATETIME, @PAY_DT, 103) ELSE NULL END),@PAY_ID ,

					@p_CONTRACT_ID ,@PAY_PHASE ,CONVERT(DATETIME, @EXPECTED_DT, 103) , @PERCENT ,@AMOUNT,@PAY_STATUS,@NOTES ,@p_RECORD_STATUS ,

					@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103), @p_AUTH_STATUS ,@p_CHECKER_ID ,

					(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END))

		END

		ELSE

		-------------Nếu @PAY_ID đã tồn tại trong TR_CONTRACT_PAYMENT, UPDATE---------

		BEGIN

			UPDATE TR_CONTRACT_PAYMENT SET

				PAY_AMOUNT = @PAY_AMOUNT,

				PAY_DT =(CASE WHEN @PAY_DT IS NOT NULL AND @PAY_DT <> '' then CONVERT(DATETIME, @PAY_DT, 103) ELSE NULL END),

				CONTRACT_ID = @p_CONTRACT_ID,

				PAY_PHASE = @PAY_PHASE,

				EXPECTED_DT = CONVERT(DATETIME, @EXPECTED_DT, 103),

				[PERCENT] = @PERCENT,

				[AMOUNT] = @AMOUNT,

				PAY_STATUS = @PAY_STATUS,

				NOTES = @NOTES,

				RECORD_STATUS = @p_RECORD_STATUS,

				MAKER_ID = @p_MAKER_ID,

				CREATE_DT = CONVERT(DATETIME, @p_CREATE_DT, 103),

				AUTH_STATUS = @p_AUTH_STATUS,

				CHECKER_ID = @p_CHECKER_ID,

				APPROVE_DT = (CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END)

			WHERE PAY_ID = @PAY_ID

		END

	FETCH NEXT FROM PaymentDetail INTO @PAY_ID,@PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@PAY_STATUS,@PAY_AMOUNT,@PAY_DT,@NOTES

	END

	CLOSE ConTrack

	DEALLOCATE ConTrack

	CLOSE AssetDetail

	DEALLOCATE AssetDetail

	CLOSE PaymentDetail

	DEALLOCATE PaymentDetail

	--- VỊ TRÍ NÀY BẮT ĐẦU GHI LOG

	SET @TOTAL_PAYMENT_AMT_NEW =(SELECT SUM(AMOUNT) FROM TR_CONTRACT_PAYMENT WHERE CONTRACT_ID =@p_CONTRACT_ID)

	DECLARE @p_CONTENT_LOG NVARCHAR(MAX)

	IF(ISNULL(CONVERT(DATE,@START_DATE_EXEC_OLD,103),'') <> ISNULL(CONVERT(DATE,@P_START_DT,103),'')

				OR ISNULL(CONVERT(DATE,@END_DATE_EXEC_OLD,103),'') <> ISNULL(CONVERT(DATE,@P_END_DT,103),'') OR ISNULL(@TOTAL_AMT_CONTRACT_OLD,0) <> ISNULL(@p_TOTAL_AMT,0)

				OR ISNULL(@TOTAL_PAYMENT_AMT_OLD,0) <> ISNULL(@TOTAL_PAYMENT_AMT_NEW,0) OR ISNULL(@p_SUP_ID,0) <> ISNULL(@SUPPLIER_ID_OLD,0) )

				BEGIN

					SET @p_CONTENT_LOG =N' thực hiện chỉnh sửa hợp đồng. Thông tin chỉnh sửa bao gồm: '+

					   CASE WHEN ISNULL(CONVERT(DATE,@START_DATE_EXEC_OLD,103),'') <> ISNULL(CONVERT(DATE,@P_START_DT,103),'') THEN 

					   CHAR(10) +  N'Thông tin ngày hiệu lực hợp đồng. Điều chỉnh từ ngày: ' 

						+ @START_DATE_OLD_CONTENT + N' thành '+ @P_START_DT_CONTENT

					   ELSE  '' END

					  + CASE WHEN CONVERT(DATE,@END_DATE_EXEC_OLD,103) <> CONVERT(DATE,@P_END_DT,103) THEN  CHAR(10) +

					  + N'Thông tin ngày hết hiệu lực hợp đồng. Điều chỉnh từ ngày: ' +  @END_DATE_EXEC_OLD_CONTENT + N' thành ngày: ' + @P_END_DT_CONTENT ELSE '' END

					  + CASE WHEN ISNULL(@TOTAL_AMT_CONTRACT_OLD,0) <> ISNULL(@p_TOTAL_AMT,0) THEN

					  CHAR(10)+ N'Tổng giá trị hợp đồng điều chỉnh từ: '+ ISNULL(FORMAT(@TOTAL_AMT_OLD,'#,###'),'0') + ' VND' + N' thành: '+ ISNULL(FORMAT(@p_TOTAL_AMT,'#,###'),'0') +' VND'

					  ELSE '' END

					  + CASE WHEN ISNULL(@TOTAL_PAYMENT_AMT_OLD,0) <> ISNULL(@TOTAL_PAYMENT_AMT_NEW,0) THEN

					  CHAR(10)+ N'Tổng giá trị lịch thanh toán điều chỉnh từ: '+ ISNULL(FORMAT(@TOTAL_PAYMENT_AMT_OLD,'#,###'),'0') + ' VND' + N' thành: '+ ISNULL(FORMAT(@TOTAL_PAYMENT_AMT_NEW,'#,###'),'0') +' VND'

					  ELSE '' END

					  + CASE WHEN ISNULL(@p_SUP_ID,0) <> ISNULL(@SUPPLIER_ID_OLD,0) THEN

					  CHAR(10)+ N'Nhà cung cấp điều chỉnh từ: '+ ISNULL(@SUPPLIER_NAME_OLD,N'Chưa chọn nhà cung cấp') + ' VND' + N' thành: '+ ISNULL(@SUPPLIER_NAME,N'Chưa chọn nhà cung cấp')

					  ELSE '' END

				END

				ELSE

				BEGIN

					SET @p_CONTENT_LOG =N' thực hiện chỉnh sửa hợp đồng. Tuy nhiên nội dung điều chỉnh không làm thay đổi những thông tin: Ngày hiệu lực hợp đồng, Ngày hết hiệu lực hợp đồng, Tổng giá trị hợp đồng, Tổng giá trị lịch thanh toán, Thông tin nhà cung cấp'

				END

	INSERT INTO dbo.PL_PROCESS

		(

						REQ_ID,

						PROCESS_ID,

						CHECKER_ID,

						APPROVE_DT,

						PROCESS_DESC,NOTES

		)

		VALUES

		(				@p_CONTRACT_ID,        -- REQ_ID - varchar(15)

						'UPDATE',        -- PROCESS_ID - varchar(10)

						@p_MAKER_ID,        -- CHECKER_ID - varchar(15)

						GETDATE(), -- APPROVE_DT - datetime

					   N'User: '+ @p_MAKER_ID +@p_CONTENT_LOG

					  ,N'Chỉnh sửa thông tin hợp đồng'

		)

	--- KẾT THÚC GHI LOG

	IF(@p_RECORD_STATUS ='U')

	BEGIN

		UPDATE TR_CONTRACT SET AUTH_STATUS ='U' WHERE CONTRACT_ID =@p_CONTRACT_ID

	END

	--Insert into TABLE TR_REQUEST_DOC_FILE

		DECLARE @tableTrREQFile TABLE(

			TR_REQUEST_DOC_FILE_ID	varchar(20)  ,

			REQ_ID varchar(20),

			NOTES  nvarchar(200)

		)

		Declare @fdoc INT

		Exec sp_xml_preparedocument @fdoc Output,@p_ListTrREQFile

		INSERT INTO @tableTrREQFile

		SELECT *

		FROM OPENXML(@fDoc,'/Root/ListTrREQ',2)

		WITH 

		(

			TR_REQUEST_DOC_FILE_ID	varchar(20)  ,

			REQ_ID varchar(20),

			NOTES  nvarchar(200)

		)

		--delete TR_REQUEST_DOC_FILE where TR_REQUEST_DOC_FILE_ID not in (select TR_REQUEST_DOC_FILE_ID from @tableTrREQFile) and REQ_ID = @p_CONTRACT_ID

		------------BAODNQ 11/11/2022 : DELETE TR_REQUEST_DOC_FILE----------

		DELETE FROM TR_REQUEST_DOC_FILE WHERE REQ_ID = @p_CONTRACT_ID

		DECLARE ListTrREQFile  CURSOR FOR

		SELECT * FROM @tableTrREQFile

		OPEN ListTrREQFile

		Declare 

		@TR_REQUEST_DOC_FILE_ID	varchar(20)  ,

		@IS_VIEW	bit,

		@REQ_ID varchar(20),

		@_NOTES nvarchar(200)

		FETCH NEXT FROM ListTrREQFile INTO @TR_REQUEST_DOC_FILE_ID,@REQ_ID,@_NOTES

		WHILE @@FETCH_STATUS = 0	

		BEGIN

			DECLARE @l_REQFile_ID VARCHAR(15)

			EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_FILE', @l_REQFile_ID out

			--select @l_REQFile_ID

			--select * from TR_REQUEST_DOC_FILE where [TR_REQUEST_DOC_FILE_ID]= 'TRC000000529764'

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

			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_REQFile_ID, 'TR_CONTRACT')

			IF(select count(*) from TR_REQUEST_DOC_FILE where TR_REQUEST_DOC_FILE_ID = @TR_REQUEST_DOC_FILE_ID ) > 0

			begin

				update TR_REQUEST_DOC_FILE set IS_VIEW=isnull(@IS_VIEW,0),REQ_ID = @p_CONTRACT_ID,NOTES=@_NOTES where TR_REQUEST_DOC_FILE_ID = @TR_REQUEST_DOC_FILE_ID 

			end

			else

			begin

				INSERT INTO dbo.TR_REQUEST_DOC_FILE

				(

					 [TR_REQUEST_DOC_FILE_ID]

					,[REQ_ID]

					,[NOTES]

				)	

				VALUES

				(   

					@l_REQFile_ID

					,@p_CONTRACT_ID,

					@_NOTES

				)

			end

			FETCH NEXT FROM ListTrREQFile INTO @TR_REQUEST_DOC_FILE_ID,@REQ_ID,@_NOTES

		END

		CLOSE ListTrREQFile

		DEALLOCATE ListTrREQFile

		IF @@Error <> 0 GOTO ABORT

		if(select count(*) from @TEMP) = 0

		begin

			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_REQFile_ID, 'TR_CONTRACT')

		end

COMMIT TRANSACTION

IF(@p_RECORD_STATUS ='U')

BEGIN

-- INSERT VAO LOG

	INSERT INTO dbo.PL_PROCESS

	(

						REQ_ID,

						PROCESS_ID,

						CHECKER_ID,

						APPROVE_DT,

						PROCESS_DESC,NOTES

					)

					VALUES

					(   @p_CONTRACT_ID,        -- REQ_ID - varchar(15)

						'SEND',        -- PROCESS_ID - varchar(10)

						@p_MAKER_ID,        -- CHECKER_ID - varchar(15)

						GETDATE(), -- APPROVE_DT - datetime

					   N'Nhân viên gửi phê duyệt phiếu thành công' ,N'Gửi phê duyệt')

	--IF(@p_CONT_TYPE ='DK')

	--BEGIN

	--	UPDATE TR_CONTRACT SET AUTH_STATUS ='A' WHERE CONTRACT_ID =@p_CONTRACT_ID

	--END

	SELECT '2' as Result, @P_CONTRACT_ID  CONTRACT_ID, N'Hợp đồng có ID: '+ @P_CONTRACT_ID +N' đã được gửi phê duyệt thành công. Vui lòng đợi cấp có thẩm quyền phê duyệt' ErrorDesc

	RETURN '2'

	--- 

	--IF(@p_CONT_TYPE ='DK' AND CONVERT(DATE, GETDATE(),103) <='2020-08-30')

END

ELSE

BEGIN

	SELECT '0' as Result, @P_CONTRACT_ID  CONTRACT_ID, N'Hợp đồng có ID: '+ @P_CONTRACT_ID +N' đã được cập nhật dữ liệu thành công. Bạn có thể bấm gửi phê duyệt' ErrorDesc,[REF_ID], [TYPE]  from @TEMP

	RETURN '0'

END

ABORT:

BEGIN

		CLOSE ConTrack

		DEALLOCATE ConTrack

		CLOSE AssetDetail

		DEALLOCATE AssetDetail

		CLOSE PaymentDetail

		DEALLOCATE PaymentDetail

		ROLLBACK TRANSACTION

		SELECT '-1' AS RESULT, ''  CONTRACT_ID,'' ErrorDesc

		RETURN '-1'

End

--03032025_SECRETKEY