ALTER   PROC dbo.TR_REQUEST_JOB_FORM_Ins
@p_REQ_CODE				VARCHAR(15)  = NULL,
@p_REQ_DT				VARCHAR(150)  = NULL,
@p_MAKER_ID				VARCHAR(15)  = NULL,
@p_CREATE_DT			VARCHAR(20)  = NULL,
@p_AUTH_STATUS			VARCHAR(1)  = NULL,
@p_CHECKER_ID			VARCHAR(15)  = NULL,
@p_APPROVE_DT			VARCHAR(20)  = NULL,
@p_REQ_REASON			NVARCHAR(MAX)  = NULL,
@p_REQ_DESC				NVARCHAR(MAX)  = NULL,
@p_NOTES				NVARCHAR(MAX)  = NULL,
@p_REQ_TYPE_TRANS		VARCHAR(15)  = NULL,
@p_REQ_TYPE_GROUP		VARCHAR(15)  = NULL,
@p_REQ_TYPE_BOOKING_HOTEL VARCHAR(15)  = NULL,
@p_REQ_TYPE_BOOKING_AIR	VARCHAR(15)  = NULL,
@p_REQ_TYPE_BOOKING_TRAIN VARCHAR(15)  = NULL,
@p_REQ_TYPE_BOOKING_CAR VARCHAR(15) = NULL,
@p_SENDER_ID			VARCHAR(15)  = NULL,
@p_SEND_DT				VARCHAR(20)  = NULL,
@p_SIGN_USER			VARCHAR(15)  = NULL,
@p_SIGN_DT				VARCHAR(20)  = NULL,
@p_BRANCH_CREATE		VARCHAR(15)  = NULL,
@p_DEP_CREATE			VARCHAR(15)  = NULL,
@p_BRANCH_ID			NVARCHAR(500)  = NULL,
@p_DEP_ID				NVARCHAR(500)  = NULL,
@p_EMP_PHONE 			VARCHAR(20)	NULL,
@p_NUM_TRIP_PEOPLE		VARCHAR(20)	NULL,
@p_OVERALL_TIME 		VARCHAR(20)	NULL,
@p_TYPE_WORK 			VARCHAR(20)	NULL,
@p_IS_SEND_APPR			VARCHAR(15) = NULL,
@p_FROM_DATE			VARCHAR(20) = NULL,
@p_TO_DATE				VARCHAR(20) = NULL,
@p_EMP_ID				VARCHAR(20) = NULL,
@p_HOTEL_NT				NVARCHAR(1000) = NULL,
@p_REQ_CAR_ID			VARCHAR(20)=NULL,
@p_ListReqJobDetail XML = NULL,
@p_ListReqJobTrain XML = NULL
AS


BEGIN TRANSACTION

IF(@p_REQ_TYPE_GROUP IS NULL OR @p_REQ_TYPE_GROUP ='')
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' Result, '' REQ_ID, N'Lưu thất bại! Bạn cần chọn Cá Nhân/Theo đoàn' ErrorDesc 
		RETURN '-1'
	END
IF(@p_REQ_TYPE_BOOKING_HOTEL IS NULL OR @p_REQ_TYPE_BOOKING_HOTEL ='')
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' Result, '' REQ_ID, N'Lưu thất bại! Bạn cần chọn khách sạn' ErrorDesc 
		RETURN '-1'
	END
IF(@p_REQ_TYPE_BOOKING_AIR IS NULL OR @p_REQ_TYPE_BOOKING_AIR ='')
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' Result, '' REQ_ID, N'Lưu thất bại! Bạn cần chọn vé máy bay' ErrorDesc 
		RETURN '-1'
	END
IF((@p_REQ_TYPE_TRANS IS NULL OR @p_REQ_TYPE_TRANS ='')AND @p_REQ_TYPE_BOOKING_AIR ='AIR_B')
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' Result, '' REQ_ID, N'Lưu thất bại! Bạn cần chọn Một chiều/khứ hồi' ErrorDesc 
		RETURN '-1'
	END
IF(@p_REQ_TYPE_BOOKING_TRAIN IS NULL OR @p_REQ_TYPE_BOOKING_TRAIN ='')
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' Result, '' REQ_ID, N'Lưu thất bại! Vé tàu bạn cần chọn Tự đặt/Không' ErrorDesc 
		RETURN '-1'
	END
IF((@p_REQ_CAR_ID IS NULL OR @p_REQ_CAR_ID ='')AND @p_REQ_TYPE_BOOKING_CAR ='CAR_B')
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' Result, '' REQ_ID, N'Lưu thất bại! Bạn cần đăng ký phiếu xe' ErrorDesc 
		RETURN '-1'
	END

DECLARE @lstRJD TABLE(
			EMP_ID		varchar(15),
			TLNANME		varchar(15),
			BRANCH_ID	varchar(15),
			DEP_ID		varchar(15),
			PHONE		varchar(150),
			NOTES		nvarchar(MAX)
		)
DECLARE @doc INT
Exec sp_xml_preparedocument @doc Output,@p_ListReqJobDetail

--Lưới Lộ Trình
		DECLARE @lstRJT TABLE(
			TIME_TRAIN		varchar(15),
			DAY_TRAIN		varchar(15),
			FROM_TRAIN		varchar(15),
			TO_TRAIN		varchar(15),
			NOTES			NVARCHAR(2000),
			VEHICLE_TYPE	varchar(15),
			CONSIGNMENT		varchar(5)
		)
DECLARE @hdoc INT
Exec sp_xml_preparedocument @hdoc Output,@p_ListReqJobTrain

IF(@p_REQ_TYPE_GROUP ='InGroup' AND NOT EXISTS (SELECT *
		FROM OPENXML(@doc,'/Root/ListReqJobDetail',2)
		WITH 
		(
			EMP_ID		varchar(15),
			TLNANME		varchar(15),
			BRANCH_ID	varchar(15),
			DEP_ID		varchar(15),
			PHONE		varchar(150),
			NOTES		nvarchar(MAX)
		)))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' Result, '' REQ_ID, N'Lưu thất bại! Lưới Danh sách công tác không được để trống' ErrorDesc 
		RETURN '-1'
	END

IF(@p_REQ_TYPE_TRANS ='RoundTrip' AND ( SELECT COUNT(*)
		FROM OPENXML(@hdoc,'/Root/ListReqJobTrain',2)
		WITH 
		(
			TIME_TRAIN		varchar(15),
			DAY_TRAIN		varchar(15),
			FROM_TRAIN		varchar(15),
			TO_TRAIN		varchar(15),
			NOTES			NVARCHAR(2000),
			VEHICLE_TYPE	varchar(15),
			CONSIGNMENT		varchar(5)
		) WHERE VEHICLE_TYPE='AIR')<2)
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' Result, '' REQ_ID, N'Lưu thất bại! Lưới lộ trình cần bổ sung thông tin khứ hồi' ErrorDesc 
		RETURN '-1'
	END
	
	-- insert master
	DECLARE @l_REQ_ID varchar(15)
	EXEC SYS_CodeMasters_Gen 'TR_REQUEST_JOB_FORM', @l_REQ_ID OUT
    EXEC [TR_REQ_JOB_CODE_GenKey] 'TR_REQUEST_JOB_FORM', '','AFT', @p_REQ_CODE OUT
	IF @l_REQ_ID='' OR @l_REQ_ID IS NULL GOTO ABORT
	
	INSERT INTO TR_REQUEST_JOB_FORM
	(
		[REQ_ID],[REQ_CODE],[REQ_DT],[REF_ID],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[REQ_REASON],[REQ_DESC],[NOTES],[REQ_TYPE_TRANS],[REQ_TYPE_GROUP],[REQ_TYPE_BOOKING_HOTEL],[REQ_TYPE_BOOKING_AIR],
		[REQ_TYPE_BOOKING_TRAIN],[SENDER_ID],[SEND_DT],[SIGN_USER],[SIGN_DT],[BRANCH_CREATE],[DEP_CREATE],[BRANCH_ID],[DEP_ID],[IS_SEND_APPR],[EMP_PHONE],[NUM_TRIP_PEOPLE],[OVERALL_TIME],[TYPE_WORK],[PROCESS_ID],[FROM_DATE],[TO_DATE],[EMP_ID],[HOTEL_NT],[REQ_TYPE_BOOKING_CAR],[REQ_CAR_ID]
	)		
	VALUES(@l_REQ_ID,@p_REQ_CODE,CONVERT(DATETIME, @p_REQ_DT, 103),@p_MAKER_ID,@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103),'E',@p_CHECKER_ID,CONVERT(DATETIME, @p_APPROVE_DT, 103),@p_REQ_REASON,@p_REQ_DESC,@p_NOTES,@p_REQ_TYPE_TRANS,@p_REQ_TYPE_GROUP,@p_REQ_TYPE_BOOKING_HOTEL,@p_REQ_TYPE_BOOKING_AIR,
			@p_REQ_TYPE_BOOKING_TRAIN,@p_SENDER_ID,CONVERT(DATETIME, @p_SEND_DT, 103),@p_SIGN_USER,CONVERT(DATETIME, @p_SIGN_DT, 103),@p_BRANCH_CREATE,@p_DEP_CREATE,@p_BRANCH_ID,@p_DEP_ID,@p_IS_SEND_APPR,@p_EMP_PHONE,@p_NUM_TRIP_PEOPLE	,@p_OVERALL_TIME,@p_TYPE_WORK,'NEW',CONVERT(DATETIME,@p_FROM_DATE ,103),CONVERT(DATETIME,@p_TO_DATE,103),@p_EMP_ID,@p_HOTEL_NT,@p_REQ_TYPE_BOOKING_CAR,@p_REQ_CAR_ID)
		IF @@Error <> 0 GOTO ABORT
		--Lưới Danh sách công tác
		
		
		INSERT INTO @lstRJD
		SELECT *
		FROM OPENXML(@doc,'/Root/ListReqJobDetail',2)
		WITH 
		(
			EMP_ID		varchar(15),
			TLNANME		varchar(15),
			BRANCH_ID	varchar(15),
			DEP_ID		varchar(15),
			PHONE		varchar(150),
			NOTES		nvarchar(MAX)
		)
		DECLARE PlDetailCur CURSOR FOR 
		SELECT * FROM @lstRJD
		OPEN PlDetailCur

		DECLARE
			@EMP_ID		varchar(15),
			@TLNANME		varchar(15),
			@BRANCH_ID	varchar(15),
			@DEP_ID		varchar(15),
			@PHONE		varchar(150),
			@NOTES		nvarchar(MAX)
		FETCH NEXT FROM PlDetailCur INTO  @EMP_ID, @TLNANME, @BRANCH_ID, @DEP_ID, @PHONE,@NOTES
		WHILE @@FETCH_STATUS = 0	
		BEGIN
		DECLARE @l_RJD_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'TR_REQUEST_JOB_DETAIL', @l_RJD_ID out
		IF @l_RJD_ID='' OR @l_RJD_ID IS NULL GOTO ABORT
		INSERT INTO dbo.TR_REQUEST_JOB_DETAIL
		(
		    REQ_DT_ID, REQ_ID, EMP_ID, TLNANME, BRANCH_ID, DEP_ID, PHONE, NOTES
		)
		VALUES
		(   
			@l_RJD_ID, @l_REQ_ID, @EMP_ID, @TLNANME, @BRANCH_ID, @DEP_ID, @PHONE,@NOTES
		)
		IF @@ERROR <> 0 GOTO ABORT
		FETCH NEXT FROM PlDetailCur INTO  @EMP_ID, @TLNANME, @BRANCH_ID, @DEP_ID, @PHONE,@NOTES
		END
		CLOSE PlDetailCur
		DEALLOCATE PlDetailCur
		
		--Lưới Danh sách Lộ Trình
		INSERT INTO @lstRJT
		SELECT *
		FROM OPENXML(@hdoc,'/Root/ListReqJobTrain',2)
		WITH 
		(
			TIME_TRAIN		varchar(15),
			DAY_TRAIN		varchar(15),
			FROM_TRAIN		varchar(15),
			TO_TRAIN		varchar(15),
			NOTES			NVARCHAR(2000),
			VEHICLE_TYPE	varchar(15),
			CONSIGNMENT		varchar(5)
		)
		DECLARE PlTrainsCur CURSOR FOR SELECT * FROM @lstRJT
		OPEN PlTrainsCur

		DECLARE
			@TIME_TRAIN		varchar(15),
			@DAY_TRAIN		varchar(15),
			@FROM_TRAIN		varchar(15),
			@TO_TRAIN		varchar(15),
			@VEHICLE_TYPE	varchar(15),
			@CONSIGNMENT	varchar(5)
     

		FETCH NEXT FROM PlTrainsCur INTO @TIME_TRAIN, @DAY_TRAIN, @FROM_TRAIN, @TO_TRAIN,@NOTES,@VEHICLE_TYPE,@CONSIGNMENT
			
		WHILE @@FETCH_STATUS = 0	
		BEGIN

		DECLARE @l_RJT_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'TR_REQUEST_JOB_TRAIN', @l_RJT_ID out

		IF @l_RJT_ID='' OR @l_RJT_ID IS NULL GOTO ABORT
   IF(@VEHICLE_TYPE='AIR' AND @CONSIGNMENT IS NULL)
  	BEGIN
  		ROLLBACK TRANSACTION
  		SELECT '-1' Result, '' REQ_ID, N'Lưu thất bại! Lưới lộ trình. Chuyến bay '+@TO_TRAIN+' - '+@FROM_TRAIN +N'cần chọn loại ký gửi' ErrorDesc 
  		RETURN '-1'
  	END
		IF(@VEHICLE_TYPE!='AIR')
		BEGIN
			SET @CONSIGNMENT=NULL
		END
		
		INSERT INTO dbo.TR_REQUEST_JOB_TRAIN
		(
		    TRAIN_ID,REQ_ID, TIME_TRAIN, DAY_TRAIN, FROM_TRAIN, TO_TRAIN, NOTES,VEHICLE_TYPE,CONSIGNMENT,IS_OLD
		)
		VALUES
		(   
			@l_RJT_ID, @l_REQ_ID, @TIME_TRAIN,CONVERT(DATETIME, @DAY_TRAIN, 103), @FROM_TRAIN,@TO_TRAIN,@NOTES, @VEHICLE_TYPE,@CONSIGNMENT,0
		)
		IF @@ERROR <> 0 GOTO ABORT
		FETCH NEXT FROM PlTrainsCur INTO @TIME_TRAIN, @DAY_TRAIN, @FROM_TRAIN, @TO_TRAIN,@NOTES,@VEHICLE_TYPE,@CONSIGNMENT
		END
		CLOSE PlTrainsCur
		DEALLOCATE PlTrainsCur


		INSERT INTO dbo.PL_PROCESS
		(
			REQ_ID,
			PROCESS_ID,
			CHECKER_ID,
			APPROVE_DT,
			PROCESS_DESC,
			NOTES
		)
		VALUES
		(   
			@l_REQ_ID,       
			'INSERT',
			@p_MAKER_ID,        
			GETDATE(), 
			N'Thêm mới PYC công tác thành công' ,      
			N'Thêm mới PYC công tác'       
		)



COMMIT TRANSACTION
SELECT '0' as Result, @l_REQ_ID  REQ_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		CLOSE PlTrainsCur
		DEALLOCATE PlTrainsCur
		CLOSE PlDetailCur
		DEALLOCATE PlDetailCur
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REQ_ID, '' ErrorDesc
		RETURN '-1'
END