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