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

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

	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

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

		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 +N' thực hiện chỉnh sửa hợp đồng. Thông tin chỉnh sửa bao gồm: '+

					   CASE WHEN CONVERT(DATE,@START_DATE_EXEC_OLD,103) <> 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 : ' 

						+  ISNULL((SELECT FORMAT (@START_DATE_EXEC_OLD, 'dd/MM/yyyy ')),N'vô thời hạn ') + N' thành ngày : '+ ISNULL(@P_START_DT,N'vô thời hạn')

						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 : ' +  ISNULL((SELECT FORMAT (@END_DATE_EXEC_OLD, 'dd/MM/yyyy ')),N'vô thời hạn ') + N' thành ngày : '

					  + ISNULL(@P_END_DT,N'vô thời hạn') 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(@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

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

		)

		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

	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