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

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