SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER   PROCEDURE [dbo].[PL_APPOINT_CONTRACTOR_Upd]
@p_REQ_ID VARCHAR(15)=NULL,
@p_REQ_CODE	nvarchar(100)  = NULL,
@p_REQ_DT	nvarchar(20) = NULL,
@p_REQ_CONTENT NVARCHAR(4000)=NULL,
@p_SURVEY_CONTENT NVARCHAR(4000) = NULL,
@p_TR_REQUEST_DOC_ID VARCHAR(15) = NULL,
@p_SUP_ID VARCHAR(15) = NULL,
@p_PROPOSE_CONTENT NVARCHAR(4000) = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(20)  = NULL,
@p_CREATE_DT	varchar(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(20)  = NULL,
@p_APPROVE_DT	nvarchar(20) = NULL,
@p_BRANCH_ID VARCHAR(15)=NULL,
@p_DEP_ID VARCHAR(20)=NULL,
@p_PROCESS_ID VARCHAR(20) = NULL,
@p_DVDM_ID VARCHAR(20) = NULL,
@p_SIGN_USER VARCHAR(20) = NULL,
@p_BASED_CONTENT NVARCHAR(3000) = NULL,
@p_TOTAL_AMT_ETM DECIMAL(18,0) = NULL,
@p_TOTAL_AMT DECIMAL(18,0) = NULL,
@p_HAS_GDK_TC VARCHAR(1) = NULL,
@p_APPOINT_REASON NVARCHAR(4000) = NULL,
@p_CATERGORY_NAME NVARCHAR(4000) = NULL,
@p_SUP_SELECT_CONTENT NVARCHAR(4000) = NULL,
@p_REPORT_TEMPLATE_CONTENT NVARCHAR(MAX) = NULL,
@p_TYPE_PL VARCHAR(20) = NULL,
@p_PL_APPOINT_CONTRACTOR_DT XML

AS	
IF(EXISTS (SELECT * FROM PL_APPOINT_CONTRACTOR WHERE REQ_CODE = @p_REQ_CODE AND REQ_ID <> @p_REQ_ID))
BEGIN
	SELECT ErrorCode Result, '' REQ_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'REQ-00001'
	RETURN '-1'
END
IF(EXISTS (
	SELECT * FROM PL_APPOINT_CONTRACTOR WHERE REQ_ID = @p_REQ_ID 
	AND AUTH_STATUS = 'U' AND PROCESS_ID <> 'NEW' AND PROCESS_ID <> 'APPROVE'))
BEGIN
	SELECT '-1' AS Result ,'' REQ_ID, 
		N'Cập nhật thất bại. Tờ trình chỉ định thầu số :' +@p_REQ_CODE+ N' đang trong trạng thái phê duyệt' ErrorDesc
	RETURN '-1'
END
BEGIN TRANSACTION

	SET @p_AUTH_STATUS = 'E'

	IF(EXISTS (SELECT * FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID AND BRANCH_TYPE = 'HS'))
	BEGIN
		SET @p_DEP_ID = (SELECT DEP_ID FROM TL_USER WHERE TLNANME = @p_MAKER_ID AND TLSUBBRID = @p_BRANCH_ID)
	END
	ELSE
	BEGIN
		SET @p_DEP_ID = ''
	END

	UPDATE PL_APPOINT_CONTRACTOR SET
		REQ_CODE = @p_REQ_CODE,
		REQ_DT = CONVERT(DATETIME,@p_REQ_DT, 103),
		REQ_CONTENT=@p_REQ_CONTENT,
		SURVEY_CONTENT = @p_SURVEY_CONTENT,
		TR_REQUEST_DOC_ID = @p_TR_REQUEST_DOC_ID,
		SUP_ID = @p_SUP_ID,
		PROPOSE_CONTENT = @p_PROPOSE_CONTENT,
		BRANCH_ID=@p_BRANCH_ID,
		DEP_ID=@p_DEP_ID,
--		CREATE_DT = CONVERT(DATETIME, @p_CREATE_DT, 103), -- NGUYENTD 31102023_SECRETKEY: FIX LỖI CẬP NHẬT LUÔN NGÀY TẠO 
		RECORD_STATUS=@p_RECORD_STATUS,
		CHECKER_ID=@p_CHECKER_ID,
		APPROVE_DT=CONVERT(DATETIME,@p_APPROVE_DT, 103),
		AUTH_STATUS=@p_AUTH_STATUS,
		MAKER_ID=@p_MAKER_ID,
		DVDM_APP_ID=@p_DVDM_ID,
		SIGN_USER = @p_SIGN_USER,
		BASED_CONTENT=@p_BASED_CONTENT,
		TOTAL_AMT_ETM = @p_TOTAL_AMT_ETM,
		HAS_GDK_TC = @p_HAS_GDK_TC,
		PROCESS_ID = @p_PROCESS_ID,
		APPOINT_REASON = @p_APPOINT_REASON,
		CATERGORY_NAME = @p_CATERGORY_NAME,
		SUP_SELECT_CONTENT = @p_SUP_SELECT_CONTENT,
		REPORT_TEMPLATE_CONTENT = @p_REPORT_TEMPLATE_CONTENT,
		TYPE_PL = @p_TYPE_PL

	WHERE REQ_ID=@p_REQ_ID

	IF @@Error <> 0 GOTO ABORT
	PRINT 'PASS UPDATE'
		
	-------------------INSERT DT--------------
	DELETE FROM PL_APPOINT_CONTRACTOR_DT WHERE REQ_ID= @p_REQ_ID
	
	IF(@p_TOTAL_AMT > @p_TOTAL_AMT_ETM)
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS Result, '' REQ_ID, 
				N'Lưới mô tả chi tiết chọn nhà cung cấp: ' +
				N'Tổng chi phí sau khi tính toán không được vượt quá tổng chi phí dự kiến của các hạng mục mua sắm được chọn' ErrorDesc
		RETURN '-1'
	END

	Declare @hdoc INT
	EXEC sp_xml_preparedocument @hdoc Output,@p_PL_APPOINT_CONTRACTOR_DT
	DECLARE PlAppointContractorDT CURSOR FOR
	SELECT *
	FROM OPENXML(@hdoc, '/Root/PlAppointContractorDT', 2)
	WITH
	(
		--REQ_ID VARCHAR(15),
		HH_ID VARCHAR(15),
		DESCRIPTION NVARCHAR(4000),
		QUANTITY INT,
		UNIT_PRICE DECIMAL(18,0),
		VAT_AMT DECIMAL(18,2),
		TOTAL_AMT DECIMAL(18,0),
		TOTAL_AMT_ETM DECIMAL(18,0),
		TR_REQ_DOC_DT_ID VARCHAR(15),
		SUP_ID VARCHAR(15)
	)
	OPEN PlAppointContractorDT

	DECLARE 
			--@d_REQ_ID VARCHAR(15),
			@d_HH_ID VARCHAR(15),
			@d_DESCRIPTION NVARCHAR(4000),
			@d_QUANTITY INT,
			@d_UNIT_PRICE DECIMAL(18,2),
			@d_VAT_AMT DECIMAL(18,2),
			@d_TOTAL_AMT DECIMAL(18,0),
			@d_TOTAL_AMT_ETM DECIMAL(18,0),
			@d_TR_REQ_DOC_DT_ID VARCHAR(15),
			@d_SUP_ID VARCHAR(15)

	FETCH NEXT FROM PlAppointContractorDT INTO
		--@d_REQ_ID,
		@d_HH_ID,
		@d_DESCRIPTION,
		@d_QUANTITY,
		@d_UNIT_PRICE,
		@d_VAT_AMT,
		@d_TOTAL_AMT,
		@d_TOTAL_AMT_ETM,
		@d_TR_REQ_DOC_DT_ID,
		@d_SUP_ID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE @d_REQ_DT_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'PL_APPOINT_CONTRACTOR_DT', @d_REQ_DT_ID out
		IF @d_REQ_DT_ID='' OR @d_REQ_DT_ID IS NULL GOTO ABORT
		PRINT 'PASS GEN ID DT'

		INSERT INTO PL_APPOINT_CONTRACTOR_DT
			(REQ_DT_ID, REQ_ID, HH_ID, DESCRIPTION, QUANTITY, UNIT_PRICE, VAT_AMT, 
			TOTAL_AMT, TOTAL_AMT_ETM, TR_REQ_DOC_DT_ID, SUP_ID)
		VALUES
			(@d_REQ_DT_ID, @p_REQ_ID, @d_HH_ID, @d_DESCRIPTION, @d_QUANTITY, @d_UNIT_PRICE,
			@d_VAT_AMT, @d_TOTAL_AMT, @d_TOTAL_AMT_ETM, @d_TR_REQ_DOC_DT_ID, @d_SUP_ID)

		IF @@Error <> 0 GOTO ABORT

		FETCH NEXT FROM PlAppointContractorDT INTO
			--@d_REQ_ID,
			@d_HH_ID,
			@d_DESCRIPTION,
			@d_QUANTITY,
			@d_UNIT_PRICE,
			@d_VAT_AMT,
			@d_TOTAL_AMT,
			@d_TOTAL_AMT_ETM,
			@d_TR_REQ_DOC_DT_ID,
			@d_SUP_ID
	END
	PRINT 'PASS INSERT DT'

	CLOSE PlAppointContractorDT
	DEALLOCATE PlAppointContractorDT

	DECLARE @p_MESSAGE NVARCHAR(MAX)
	--------tờ trình xin chi phí---------
	IF(@p_TYPE_PL = 'TTCP')
	BEGIN
		SET @p_MESSAGE = N'Cập nhật tờ trình xin chi phí thành công'
	END
	--------tờ trình chỉ định thầu---------
	ELSE
	BEGIN
		SET @p_MESSAGE = N'Cập nhật tờ trình chỉ định thầu thành công'
	END
		
COMMIT TRANSACTION
SELECT '0' as Result, @p_REQ_ID  REQ_ID,@p_REQ_CODE REQ_CODE, 
	N'Cập nhật tờ trình chỉ định thầu thành công' AS ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS Result ,'' REQ_ID, '' ErrorDesc
		RETURN '-1'
End
--10062025_SECRETKEY