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

-- Stored Procedure

/*

SELECT * FROM ASS_MASTER

SELECT * FROM ASS_MASTER WHERE A

SELECT * FROM CAR_ACCESSORY WHERE ASSET_ID ='ASS000000000003'

[CAR_MASTER_Ins] 'ASS000000000029','CT00003','','','','','','','','','','','','','',100,100,100,100,100,100,

'','','','','','','','','',

'

<Root xmlns="">

	<CarAccessory>

		<CAR_AC_ID></CAR_AC_ID>

		<ASSET_ID>ASS000000000003</ASSET_ID>

		<ASSET_NAME>XE BMW</ASSET_NAME>

		<ACCESSORY_NAME>MAN HINH</ACCESSORY_NAME>

		<QUANTITY>1</QUANTITY>

		<NOTES>MOI MUA</NOTES>

	</CarAccessory>

</Root>

',

'

<Root xmlns="">

	<CarCureSch>

		<CAR_CUR_ID></CAR_CUR_ID>

		<ASSET_ID>ASS000000000003</ASSET_ID>

		<ASSET_NAME>XE BMW </ASSET_NAME>		

		<INDEX_NUMBER>1</INDEX_NUMBER>

		<CURE_DT>20/11/2013</CURE_DT>

		<COUNT>1</COUNT>

		<NOTES>MOI MUA</NOTES>

	</CarCureSch>

</Root>

'

*/

ALTER PROCEDURE dbo.CAR_MASTER_Ins

@p_ASSET_ID	varchar(15)  = NULL,

@p_CAR_TYPE_ID	varchar(15)  = NULL,

@p_MODEL	nvarchar(100)  = NULL,

@p_N_PLATE	varchar(20)  = NULL,

@p_PROCOUNTRY	varchar(15)  = NULL,

@p_CAR_COLOR	nvarchar(100)  = NULL,

@p_MACHINES_ID	nvarchar(100)  = NULL,

@p_SLOPES_ID	nvarchar(100)  = NULL,

@p_MANUFACTURER	varchar(15)  = NULL,

@p_START_DT	VARCHAR(20) = NULL,

@p_OWNER	nvarchar(100)  = NULL,

@p_ENGINE_TYPE	nvarchar(100)  = NULL,

@p_ENGINE_NUMBER	varchar(15)  = NULL,

@p_GEARBOX_TYPE	varchar(15)  = NULL,

@p_FUELS_TYPE	varchar(15)  = NULL,

@p_POWER_RATE	decimal(18,2)  = NULL,

@p_VOLUME	decimal(18,2)  = NULL,

@p_TIRE_SIZE	varchar(100)  = NULL,

@p_LENGTH	decimal(18)  = NULL,

@p_WIDTH	decimal(18)  = NULL,

@p_HEIGHT	decimal(18)  = NULL,

@p_BRANCH_ID	varchar(15)  = NULL,

@p_EMPLOYEE_ID	varchar(15)  = NULL,

@p_PURPOSE varchar(50)=NULL,

@p_NOTES	nvarchar(1000)  = NULL,

@p_RECORD_STATUS	varchar(1)  = NULL,

@p_AUTH_STATUS	varchar(1)  = NULL,

@p_MAKER_ID	varchar(15)  = NULL,

@p_CREATE_DT	VARCHAR(20) = NULL,

@p_CHECKER_ID	varchar(15)  = NULL,

@p_APPROVE_DT	VARCHAR(20) = NULL,

@p_MANUFACTURE_YEAR	VARCHAR(4) = NULL,

@p_IS_SPECIAL VARCHAR(1)=NULL,

@p_CAR_ACCESSORY XML = NULL,

@p_CAR_CURE_SCH XML = NULL,

@p_CAR_DRIVER XML = NULL,--doanptt them luoi danh sach tai xe

-- KHANGTH - 080620, BO SUNG COLMN NEXT_MAT_NUMBER

-- So km tiep theo 

@p_NEXT_MAT_NUMBER DECIMAL(18,0) = NULL,

-- Nội dung bảo trì

@p_MAINT_NOTE NVARCHAR(1000) = NULL,

@p_EMP_NAME NVARCHAR(250) = NULL

--//-------------------------------------------------

AS

	DECLARE @ERRORSYS VARCHAR(20)

	DECLARE @l_BRANCH_ID VARCHAR(20)

    IF(@p_ASSET_ID IS NOT NULL OR @p_ASSET_ID <>'')

    BEGIN

    	--kiem tra ma tai san

    	IF( NOT EXISTS ( SELECT * FROM ASS_MASTER A WHERE A.ASSET_ID = @p_ASSET_ID ))

    	BEGIN

    		SET @ERRORSYS = 'ASS-99999'

    	END

    	ELSE

    	BEGIN IF(EXISTS ( SELECT * FROM ASS_MASTER A WHERE A.ASSET_ID = @p_ASSET_ID ))

    		--LAY THONG TIN DON VI SU DUNG TAI SAN

    		SET @l_BRANCH_ID = (SELECT BRANCH_ID FROM ASS_MASTER A WHERE A.ASSET_ID = @p_ASSET_ID)

    	END

    	--kiem tra ma tai san da cap phat cho xe chua

    	IF( EXISTS ( SELECT * FROM CAR_MASTER A WHERE A.ASSET_ID = @p_ASSET_ID ))

    	BEGIN

    		SET @ERRORSYS = 'CAR-99997'

    	END

    	--kiem tra ma so xe co ton tai chua

    	IF( EXISTS ( SELECT * FROM CAR_MASTER A WHERE A.N_PLATE = @p_N_PLATE ) )

    	BEGIN

    		SET @ERRORSYS = 'CAR-99999'

    	END

    	IF( @ERRORSYS <> '' )

    	BEGIN

    		SELECT ErrorCode Result, ''  ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

    		RETURN '0'

    	END

    END

    ELSE

    BEGIN

    	SET @l_BRANCH_ID = (SELECT tu.TLSUBBRID FROM TL_USER tu  WHERE tu.TLNANME = @p_MAKER_ID)

    END

	Declare @hdoc INT

	Exec sp_xml_preparedocument @hdoc Output,@p_CAR_ACCESSORY

	DECLARE CarAccessory CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/CarAccessory',2)

	WITH 

	(

		ASSET_ID	varchar(15),

		ASSET_NAME	nvarchar(200),

		DEVICE_CODE VARCHAR(15),

		ACCESSORY_NAME	nvarchar(1000)  ,

		UNIT_ID	varchar(15)  ,

		QUANTITY	int ,

		NOTES	nvarchar(1000)

	)

	OPEN CarAccessory

	Exec sp_xml_preparedocument @hdoc Output,@p_CAR_CURE_SCH

	DECLARE CarCureSch CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/CarCureSch',2)

	WITH 

	(

		ASSET_ID	varchar(15),

		ASSET_NAME	nvarchar(200) ,		

		INDEX_NUMBER	decimal(18) ,

		CURE_DT	VARCHAR(20) ,

		[COUNT]        int,

		NOTES	nvarchar(1000)  

	)

	OPEN CarCureSch

	Exec sp_xml_preparedocument @hdoc Output,@p_CAR_DRIVER --doanptt

	DECLARE CarDriver CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/CarDriver',2)

	WITH 

	(

		CAR_DRIVER VARCHAR(15),

		CAR_DRIVER_NAME	Nvarchar(200),

		FROM_DT	varchar(20),

		TO_DT	varchar(20),

		IS_USED varchar(1),

		NOTES	nvarchar(1000)  

	)

	OPEN CarDriver

BEGIN TRANSACTION

DECLARE @l_CAR_ID VARCHAR(15)

		EXEC SYS_CodeMasters_Gen 'CAR_MASTER', @l_CAR_ID out

		IF @l_CAR_ID='' OR @l_CAR_ID IS NULL GOTO ABORT

		-- TuNT 19/02/2020 them truong IS_LIQUID danh dau xe da bi thanh ly

		-- KHANGTH 11/06/2020 them truong MAINT_NOTE lich bao tri

		INSERT INTO CAR_MASTER([CAR_ID],[ASSET_ID],[CAR_TYPE_ID],[MODEL],[N_PLATE],[PROCOUNTRY],[CAR_COLOR],[MACHINES_ID],[SLOPES_ID],[MANUFACTURER],[START_DT],[OWNER],[ENGINE_TYPE],[ENGINE_NUMBER],[GEARBOX_TYPE],[FUELS_TYPE],[POWER_RATE],[VOLUME],[TIRE_SIZE],[LENGTH],[WIDTH],[HEIGHT],[BRANCH_ID],[EMPLOYEE_ID],[PURPOSE],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[MANUFACTURE_YEAR],[IS_SPECIAL], [IS_LIQUID],[NEXT_MAT_NUMBER],[MAINT_NOTE], [EMP_NAME]) 

		VALUES(@l_CAR_ID ,@p_ASSET_ID ,@p_CAR_TYPE_ID ,@p_MODEL ,@p_N_PLATE ,@p_PROCOUNTRY ,@p_CAR_COLOR ,@p_MACHINES_ID ,@p_SLOPES_ID ,@p_MANUFACTURER ,CONVERT(DATETIME, @p_START_DT, 103) ,@p_OWNER ,@p_ENGINE_TYPE ,@p_ENGINE_NUMBER ,@p_GEARBOX_TYPE ,@p_FUELS_TYPE ,@p_POWER_RATE ,@p_VOLUME ,@p_TIRE_SIZE ,@p_LENGTH ,@p_WIDTH ,@p_HEIGHT ,@l_BRANCH_ID ,@p_EMPLOYEE_ID ,@p_PURPOSE,@p_NOTES ,@p_RECORD_STATUS ,@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),@p_MANUFACTURE_YEAR,@p_IS_SPECIAL, '0',@p_NEXT_MAT_NUMBER,@p_MAINT_NOTE, @p_EMP_NAME)

		PRINT 'MASTER'

		IF @@Error <> 0 GOTO ABORT

		--KHAI BAO TOAN BO BIEN TRUOC KHI FETCH DATA

		DECLARE 

		@ASSET_ID	varchar(15)  ,

		@ASSET_NAME	nvarchar(200),

		@DEVICE_CODE VARCHAR(15),

		@ACCESSORY_NAME	nvarchar(1000) ,

		@UNIT_ID	varchar(15)  ,

		@QUANTITY	INT,

		@NOTES	nvarchar(1000) ,

		@CAR_ID	varchar(15),

		@INDEX_NUMBER	decimal(18) ,

		@CURE_DT	VARCHAR(20),

		@COUNT int,

		-- declare driver doanptt

		@CAR_DRIVER VARCHAR(15),

		@CAR_DRIVER_NAME	Nvarchar(200),

		@FROM_DT	varchar(20) ,

		@TO_DT	varchar(20),

		@IS_USED varchar(1)

		--INSERT ACCESSORY DETAIL

		FETCH NEXT FROM CarAccessory INTO  @ASSET_ID,@ASSET_NAME, @DEVICE_CODE,@ACCESSORY_NAME,@UNIT_ID,@QUANTITY,@NOTES

		WHILE @@FETCH_STATUS = 0

		BEGIN

			DECLARE @l_CAR_ACC_ID VARCHAR(15)

			EXEC SYS_CodeMasters_Gen 'CAR_ACCESSORY', @l_CAR_ACC_ID out

			IF @l_CAR_ACC_ID='' OR @l_CAR_ACC_ID IS NULL GOTO ABORT

			INSERT INTO CAR_ACCESSORY([CAR_ACC_ID],[CAR_ID],[ASSET_ID],[ASSET_NAME],[DEVICE_CODE], [ACCESSORY_NAME],[UNIT_ID],[QUANTITY],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])

			VALUES(@l_CAR_ACC_ID ,@l_CAR_ID ,@ASSET_ID ,@ASSET_NAME ,@DEVICE_CODE, @ACCESSORY_NAME ,@UNIT_ID ,

			CONVERT(INT,@QUANTITY) ,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) )

			IF @@ERROR <> '' GOTO ABORT

			FETCH NEXT FROM CarAccessory INTO  @ASSET_ID,@ASSET_NAME,@DEVICE_CODE, @ACCESSORY_NAME,@UNIT_ID,@QUANTITY,@NOTES

		END

		PRINT 'CAR ACCES'

		--INSERT CAR CUR SCHEDULE

		FETCH NEXT FROM CarCureSch INTO  @ASSET_ID,@ASSET_NAME,@INDEX_NUMBER,@CURE_DT,@COUNT,@NOTES

		WHILE @@FETCH_STATUS = 0

		BEGIN

			DECLARE @l_CAR_CU_SCH_ID VARCHAR(15)

			EXEC SYS_CodeMasters_Gen 'CAR_CURE_SCH', @l_CAR_CU_SCH_ID out

			IF @l_CAR_CU_SCH_ID='' OR @l_CAR_CU_SCH_ID IS NULL GOTO ABORT

			INSERT INTO CAR_CURE_SCH([CAR_CU_SCH_ID],[CAR_ID],[ASSET_ID],[ASSET_NAME],[INDEX_NUMBER],[CURE_DT],[COUNT],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])

			VALUES(@l_CAR_CU_SCH_ID ,@l_CAR_ID ,@ASSET_ID ,@ASSET_NAME ,@INDEX_NUMBER ,

			CONVERT(DATETIME, @CURE_DT, 103) ,@COUNT,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) )

			IF @@ERROR <> '' GOTO ABORT

			FETCH NEXT FROM CarCureSch INTO  @ASSET_ID,@ASSET_NAME,@INDEX_NUMBER,@CURE_DT,@COUNT,@NOTES

		END

		--INSERT CAR DRIVER doanptt

		FETCH NEXT FROM CarDriver INTO  @CAR_DRIVER,@CAR_DRIVER_NAME,@FROM_DT,@TO_DT, @IS_USED, @NOTES

		WHILE @@FETCH_STATUS = 0

		BEGIN

			DECLARE @l_CAR_DRIVER_ID VARCHAR(15)

			EXEC SYS_CodeMasters_Gen 'CAR_DRIVER_DT', @l_CAR_DRIVER_ID out

			IF @l_CAR_DRIVER_ID='' OR @l_CAR_DRIVER_ID IS NULL GOTO ABORT

			INSERT INTO CAR_DRIVER_DT([CAR_DRIVER_ID],[CAR_ID],[CAR_DRIVER],[CAR_DRIVER_NAME],[FROM_DT],[TO_DT],[IS_USED],[NOTES],[RECORD_STATUS],[CREATE_DT],[CHECKER_ID],[APPROVE_DT])

							   VALUES(@l_CAR_DRIVER_ID ,@l_CAR_ID ,@CAR_DRIVER ,@CAR_DRIVER_NAME ,CONVERT(DATETIME, @FROM_DT, 103) , CONVERT(DATETIME, @TO_DT, 103), @IS_USED,

							   @NOTES ,@p_RECORD_STATUS ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) )

			IF @@ERROR <> '' GOTO ABORT

			FETCH NEXT FROM CarDriver INTO  @CAR_DRIVER,@CAR_DRIVER_NAME,@FROM_DT,@TO_DT, @IS_USED, @NOTES

		END

		CLOSE CarAccessory

		DEALLOCATE CarAccessory

		CLOSE CarCureSch

		DEALLOCATE CarCureSch

		CLOSE CarDriver

		DEALLOCATE CarDriver

COMMIT TRANSACTION

SELECT '0' as Result, @l_CAR_ID  ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		CLOSE CarAccessory

		DEALLOCATE CarAccessory

		CLOSE CarCureSch

		DEALLOCATE CarCureSch

		CLOSE CarDriver

		DEALLOCATE CarDriver

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ID, '' ErrorDesc

		RETURN '-1'

End