-- 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

	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