UPDATE dbo.CM_ALLCODE SET CONTENT=N'Căn cứ Quyết định 166/2020/QĐ-HC ngày 21/5/2020 về việc Ban hành Quy định mua sắm hàng hóa dịch vụ; Căn cứ nhu cầu mua sắm thực tế' WHERE CDTYPE='TR' AND CDNAME='PL_BASED'

ALTER PROC [dbo].[TR_REQUEST_CAR_Upd]
@p_REQ_ID	varchar(15) =NULL,
@p_REQ_CODE varchar(150) = NULL,
@p_EMP_ID varchar(15) = NULL,
@p_EMP_PHONE varchar(15) = NULL,
@p_BRANCH_ID varchar(15) = NULL,
@p_BRANCH_CREATE varchar(15) = NULL,
@p_DEP_ID varchar(15) = NULL,
@p_DEP_CREATE varchar(15) = NULL,
@p_ORIGN nvarchar(100) = NULL,
@p_DESTINATION nvarchar(100) = NULL,
@p_IS_SEND_APPR varchar(15) = NULL,
@p_SEND_APPR_DT varchar(20) = NULL,
@p_SIGN_USER varchar(15) = NULL,
@p_SIGN_DT varchar(20) = NULL,
@p_FROM_DATE varchar(20) = NULL,
@p_REQ_DT varchar(20) = NULL,
@p_TO_DATE varchar(20) = NULL,
@p_NOTES nvarchar(MAX) = NULL,
@p_CAR_ID varchar(15) = NULL,
@p_DRIVER_ID varchar(15) = NULL,
@p_DRIVER_PHONE varchar(15) = NULL,
@p_QUANTITY_TRIP int = NULL,
@p_NOTES_TRIP nvarchar(MAX) = NULL,
@p_UNIT_TRIP varchar(5) = NULL,
@p_TO_TRIP nvarchar(100) = NULL,
@p_FROM_TRIP nvarchar(100) = NULL,
@p_MILRAGE_TRIP int = NULL,
@p_START_TIME_TRIP varchar(20) = NULL,
@p_QUANTITY_RETURN_TRIP int = NULL,
@p_NOTES_RETURN_TRIP varchar(MAX) = NULL,
@p_UNIT_RETURN_TRIP varchar(5) = NULL,
@p_TO_RETURN_TRIP nvarchar(100) = NULL,
@p_FROM_RETURN_TRIP nvarchar(100) = NULL,
@p_MILRAGE_RETURN_TRIP int = NULL,
@p_START_TIME_RETURN_TRIP varchar(20) = NULL,
@p_AUTH_STATUS varchar(1) = NULL,
@p_APPROVE_DT varchar(20) = NULL,
@p_CREATE_DT varchar(20) = NULL,
@p_MAKER_ID varchar(20) = NULL,
@p_ListCarDT xml,
@p_USER_LOGIN varchar(20) = NULL,
@p_USER_UPDATE varchar(20) = NULL,
@p_N_PLATE varchar(20) = NULL,
@p_MO_NAME varchar(20) = NULL,
@p_LAST_BALANCE			decimal(18,2)= NULL,
@p_FIRST_BALANCE		decimal(18,2)= NULL,
@p_CURRENT_POWER_RATE	decimal(18,2)= NULL,
@p_POWER_RATE			decimal(18,2)= NULL,
@p_ListFuel xml,
@p_IS_CONTRACT_CAR varchar(1) = NULL,
@p_OLD_FUEL_NUMBER DECIMAL(18,2) = NULL

AS

BEGIN TRANSACTION
	DECLARE @p_PROCESS_ID VARCHAR(20)
	SELECT @p_PROCESS_ID=PROCESS_ID FROM dbo.TR_REQUEST_CAR WHERE REQ_ID=@p_REQ_ID
	IF NOT EXISTS(SELECT * FROM TR_REQUEST_CAR WHERE REQ_ID <> @p_REQ_ID)
	BEGIN
		SELECT '0' Result, '' REQ_ID, N'Mã phiếu yêu cầu xe chưa tồn tại trong hệ thống' ErrorDesc 
		RETURN '0'
	END 
	DECLARE @IS_SEND_APPR VARCHAR(1),@AUTH_STATUS VARCHAR(1)
	IF(EXISTS(SELECT * FROM TR_REQUEST_CAR WHERE REQ_ID =@p_REQ_ID  AND PROCESS_ID IN ('REJECT','INSERT')))
	BEGIN
		UPDATE TR_REQUEST_CAR
		SET EMP_ID=@p_EMP_ID,EMP_PHONE=@p_EMP_PHONE,BRANCH_ID=@p_BRANCH_ID,DEP_ID=@p_DEP_ID,ORIGN=@p_ORIGN,DESTINATION=@p_DESTINATION,AUTH_STATUS='U',REQ_DT=CONVERT(DATETIME,@p_REQ_DT,104),IS_SEND_APPR='N',FROM_DATE=CONVERT(DATETIME,@p_FROM_DATE,104),TO_DATE=CONVERT(DATETIME,@p_TO_DATE,104),NOTES=@p_NOTES,
		QUANTITY_TRIP=@p_QUANTITY_TRIP,NOTES_TRIP=@p_NOTES_TRIP,UNIT_TRIP=@p_UNIT_TRIP,TO_TRIP=@p_TO_TRIP,FROM_TRIP=@p_FROM_TRIP,START_TIME_TRIP=CONVERT(DATETIME,@p_START_TIME_TRIP, 104)
		WHERE REQ_ID =@p_REQ_ID
		SET @IS_SEND_APPR='N'
		SET @AUTH_STATUS='U'
		DELETE PL_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='UPDATE'
		INSERT INTO dbo.PL_PROCESS
		(
			REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES
		)
		VALUES
		(   
			@p_REQ_ID,'UPDATE',@p_USER_LOGIN,CONVERT(DATETIME,GETDATE(),104),N'Cập nhật PYC xe thành công',N'Cập nhật PYC xe'
		)
	END
	ELSE IF(EXISTS(SELECT * FROM CM_ALLCODE WHERE CDNAME='REQCAR'AND CDVAL=@p_USER_LOGIN)OR EXISTS(SELECT PROCESS_ID FROM TR_REQUEST_CAR WHERE REQ_ID=@p_REQ_ID AND USER_UPDATE=@p_USER_LOGIN))
	BEGIN
		-- update master
			UPDATE dbo.TR_REQUEST_CAR
			SET CAR_ID=@p_CAR_ID,DRIVER_ID=@p_DRIVER_ID,DRIVER_PHONE=@p_DRIVER_PHONE,QUANTITY_TRIP=@p_QUANTITY_TRIP,NOTES_TRIP=@p_NOTES_TRIP,UNIT_TRIP=@p_UNIT_TRIP,TO_TRIP=@p_TO_TRIP,FROM_TRIP=@p_FROM_TRIP,MILRAGE_TRIP=@p_MILRAGE_TRIP,START_TIME_TRIP=CONVERT(DATETIME, @p_START_TIME_TRIP, 104),MO_NAME=@p_MO_NAME,N_PLATE=@p_N_PLATE,
			QUANTITY_RETURN_TRIP=@p_QUANTITY_RETURN_TRIP,NOTES_RETURN_TRIP=@p_NOTES_RETURN_TRIP,UNIT_RETURN_TRIP=@p_UNIT_RETURN_TRIP,TO_RETURN_TRIP=@p_TO_RETURN_TRIP,FROM_RETURN_TRIP=@p_FROM_RETURN_TRIP,MILRAGE_RETURN_TRIP=@p_MILRAGE_RETURN_TRIP,START_TIME_RETURN_TRIP=CONVERT(DATETIME, @p_START_TIME_RETURN_TRIP, 104),
			IS_CONTRACT_CAR=@p_IS_CONTRACT_CAR,AUTH_STATUS='U',POWER_RATE=@p_POWER_RATE,FIRST_BALANCE=@p_FIRST_BALANCE,LAST_BALANCE=@p_LAST_BALANCE,CURRENT_POWER_RATE=@p_CURRENT_POWER_RATE,USER_UPDATE = @p_USER_UPDATE,OLD_FUEL_NUMBER=@p_OLD_FUEL_NUMBER
			WHERE REQ_ID=@p_REQ_ID
			IF @@Error <> 0 GOTO ABORT 
			DELETE FROM dbo.TR_REQUEST_CAR_DETAIL WHERE REQ_ID = @p_REQ_ID
			DELETE FROM dbo.TR_REQ_CAR_FUEL WHERE REQ_ID = @p_REQ_ID
		-- LƯỚI CẬP NHẬT CHI PHÍ
				DECLARE @lstCDT TABLE(
				REQ_DT_ID VARCHAR(15),
				REQ_ID VARCHAR(15),
				FEE_TYPE NVARCHAR(100),
				AMOUNT decimal(18, 0),
				QUANTITY decimal(18, 2),
				NOTES NVARCHAR(MAX),
				UNIT NVARCHAR(20)
				)
		
			DECLARE @doc INT
			Exec sp_xml_preparedocument @doc Output,@p_ListCarDT
		
			INSERT INTO @lstCDT
			SELECT *
			FROM OPENXML(@doc,'/Root/ListCarDetail',2)
			WITH 
			(
				REQ_DT_ID VARCHAR(15),
				REQ_ID VARCHAR(15),
				FEE_TYPE NVARCHAR(100),
				AMOUNT decimal(18, 0),
				QUANTITY decimal(18, 2),
				NOTES NVARCHAR(MAX),
				UNIT NVARCHAR(20)
			)


			DECLARE CarDTCur CURSOR FOR SELECT REQ_DT_ID,REQ_ID,QUANTITY,FEE_TYPE,AMOUNT,NOTES,UNIT FROM @lstCDT
			OPEN CarDTCur

			DECLARE
				@pl_REQ_DT_ID VARCHAR(15),
				@pl_REQ_ID VARCHAR(15),
				@pl_FEE_TYPE NVARCHAR(100),
				@pl_QUANTITY int,
				@pl_AMOUNT decimal(18, 0),
				@pl_NOTES NVARCHAR(MAX),
				@pl_UNIT NVARCHAR(20)

			FETCH NEXT FROM CarDTCur INTO @pl_REQ_DT_ID,@pl_REQ_ID,@pl_QUANTITY,@pl_FEE_TYPE,@pl_AMOUNT,@pl_NOTES,@pl_UNIT
			
			WHILE @@FETCH_STATUS = 0	
			BEGIN

			DECLARE @l_CarDT_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'TR_REQUEST_CAR_DETAIL', @l_CarDT_ID out
			IF @l_CarDT_ID='' OR @l_CarDT_ID IS NULL GOTO ABORT

			INSERT INTO dbo.TR_REQUEST_CAR_DETAIL
			(
			   REQ_DT_ID,REQ_ID,QUANTITY,FEE_TYPE,AMOUNT,NOTES,UNIT
			)
			VALUES
			(  
				@l_CarDT_ID,@p_REQ_ID,@pl_QUANTITY,@pl_FEE_TYPE,@pl_AMOUNT,@pl_NOTES,@pl_UNIT
			)
		
			IF @@ERROR <> 0 GOTO ABORT1
			FETCH NEXT FROM CarDTCur INTO @pl_REQ_DT_ID,@pl_REQ_ID,@pl_QUANTITY,@pl_FEE_TYPE,@pl_AMOUNT,@pl_NOTES,@pl_UNIT
			END
			CLOSE CarDTCur
			DEALLOCATE CarDTCur
			--lưới nhiên liệu
			DECLARE @lstNL TABLE(
				  [FUEL_ID] VARCHAR(20),
				  [REQ_ID] VARCHAR(20),
				  [REFUEL_DATE] VARCHAR(20),
				  [NUMBER_LITERS] decimal(18, 2),
				  [NOTES] NVARCHAR(MAX),
				  [AMOUNT] DECIMAL(18,0)
				)
		
				Exec sp_xml_preparedocument @doc Output,@p_ListFuel
		
				INSERT INTO @lstNL
				SELECT *
				FROM OPENXML(@doc,'/Root/ListFuel',2)
				WITH 
				(
				  FUEL_ID VARCHAR(20),
				  REQ_ID VARCHAR(20),
				  REFUEL_DATE VARCHAR(20),
				  NUMBER_LITERS decimal(18, 2),
				  NOTES NVARCHAR(MAX),
				  AMOUNT DECIMAL(18,0)
				)

				DECLARE NLCur CURSOR FOR SELECT REFUEL_DATE,NUMBER_LITERS,[NOTES],AMOUNT FROM @lstNL
				OPEN NLCur

				DECLARE
				  @pl_REFUEL_DATE VARCHAR(20),
				  @pl_NUMBER_LITERS INT,
				  @AMOUNT DECIMAL(18,0)

				FETCH NEXT FROM NLCur INTO @pl_REFUEL_DATE,@pl_NUMBER_LITERS,@pl_NOTES,@AMOUNT
			
				WHILE @@FETCH_STATUS = 0	
				BEGIN

				DECLARE @l_FUEL_ID VARCHAR(20)
				EXEC SYS_CodeMasters_Gen 'TR_REQ_CAR_FUEL', @l_FUEL_ID out
				IF @l_FUEL_ID='' OR @l_FUEL_ID IS NULL GOTO ABORT

				INSERT INTO dbo.TR_REQ_CAR_FUEL
				(
				  [FUEL_ID],[REFUEL_DATE],[NUMBER_LITERS],[REQ_ID],[NOTES],[AMOUNT]
				)
				VALUES
				(  
					@l_FUEL_ID,CONVERT(DATETIME,@pl_REFUEL_DATE,103),@pl_NUMBER_LITERS,@p_REQ_ID,@pl_NOTES,@AMOUNT
				)
		
				IF @@ERROR <> 0 GOTO ABORT1
				FETCH NEXT FROM NLCur INTO  @pl_REFUEL_DATE,@pl_NUMBER_LITERS,@pl_NOTES,@AMOUNT
				END
				CLOSE NLCur
				DEALLOCATE NLCur
			IF(NOT EXISTS(SELECT USER_UPDATE FROM dbo.TR_REQUEST_CAR WHERE REQ_ID=@p_REQ_ID AND USER_UPDATE=@p_USER_LOGIN))
			BEGIN
				UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET TLNAME=@p_USER_UPDATE WHERE TYPE_JOB='XL' AND REQ_ID=@p_REQ_ID
			END
			IF(EXISTS(SELECT * FROM CM_ALLCODE WHERE CDNAME='REQCAR'AND CDVAL=@p_USER_LOGIN) AND
			NOT EXISTS(SELECT * FROM dbo.TR_REQUEST_CAR WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID IN ('CVINFO_UPD','USER_SEND','CVCOSTUPD','COSTREJECT','CVREJUPD')))
			BEGIN
				UPDATE TR_REQUEST_CAR
				SET PROCESS_ID='CV_CAR_UPD'
				WHERE REQ_ID=@p_REQ_ID
				DELETE FROM PL_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID ='CV_CAR_UPD'
				INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES
				)
				VALUES
				(   
					@p_REQ_ID,'CV_CAR_UPD',@p_USER_LOGIN,CONVERT(DATETIME,GETDATE(),104),N'Chuyên viên điều động xe cập nhật thông tin xe thành công',N'Chuyên viên điều động xe cập nhật thông tin xe'
				)
			END
			ELSE IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_CAR WHERE REQ_ID=@p_REQ_ID AND USER_UPDATE=@p_USER_LOGIN AND PROCESS_ID IN('CV_SEND','USER_UPD','MAKER_REJ','MAKER_CONF','CV_REJECT')))
			BEGIN 
				UPDATE TR_REQUEST_CAR
				SET PROCESS_ID='USER_UPD'
				WHERE REQ_ID=@p_REQ_ID
				DELETE FROM PL_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID ='USER_UPD'
				INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES
				)
				VALUES
				(   
					@p_REQ_ID,'USER_UPD',@p_USER_LOGIN,CONVERT(DATETIME,GETDATE(),103),N'Tài xế cập nhật thông tin xe thành công',N'Tài xế cập nhật thông tin xe'
				)
			END
			ELSE IF(EXISTS(SELECT * FROM CM_ALLCODE WHERE CDNAME='REQCAR'AND CDVAL=@p_USER_LOGIN) AND @p_PROCESS_ID ='USER_SEND')
			BEGIN
				UPDATE TR_REQUEST_CAR
				SET PROCESS_ID='CVCOSTUPD'
				WHERE REQ_ID=@p_REQ_ID
				INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES
				)
				VALUES
				(   
					@p_REQ_ID,'CV_COSTUPD',@p_USER_LOGIN,CONVERT(DATETIME,GETDATE(),103),N'Chuyên viên điều động xe cập nhật chi phí thành công',N'Chuyên viên điều động xe cập nhật chi phí'
				)
			END
			ELSE IF(EXISTS(SELECT * FROM CM_ALLCODE WHERE CDNAME='REQCAR'AND CDVAL=@p_USER_LOGIN) AND @p_PROCESS_ID IN ('COSTREJECT'))
			BEGIN
					UPDATE TR_REQUEST_CAR
					SET PROCESS_ID='CVREJUPD'
					WHERE REQ_ID=@p_REQ_ID
					INSERT INTO dbo.PL_PROCESS
					(
						REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES
					)
					VALUES
					(   
						@p_REQ_ID,'CVREJUPD',@p_USER_LOGIN,CONVERT(DATETIME,GETDATE(),103),N'Chuyên viên điều động xe cập nhật thông tin thành công',N'Chuyên viên điều động xe cập nhật thông tin'
					)
			END
		END
	
COMMIT TRANSACTION
SELECT '0' as Result, @p_REQ_ID  REQ_ID,@IS_SEND_APPR AS IS_SEND_APPR,@AUTH_STATUS AS AUTH_STATUS, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REQ_ID, '' ErrorDesc
		RETURN '-1'
END
ABORT1:
BEGIN
		CLOSE CarDTCur
		DEALLOCATE CarDTCur
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End