ALTER PROCEDURE [dbo].[BUD_MASTER_Ins]
@p_ASSET_ID	varchar(15)  = NULL,
@p_BUILDING_NAME	nvarchar(1000)  = NULL,
@p_BRANCH_ID	varchar(15)  = NULL,
@p_ADDRESS	nvarchar(800)  = NULL,
@p_OWNER_OR_RENT	varchar(1)  = NULL,
@p_NUM_FLOOR	int = NULL,
@p_AREA	numeric(18)  = NULL,
@p_TOTAL_AREA	numeric(18)  = NULL,
@p_RENT_TOTAL_AREA	numeric(18)  = NULL,
@p_UTILZED_AREA	numeric(18)  = NULL,
@p_NOTES	ntext = 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_XML_TEMP	xml = NULL,
@p_RENT_AMT DECIMAL(18,0) =NULL,
@p_DEPOSITS_AMT DECIMAL(18,0) =NULL,
@p_RENT_TIME int,
@p_IS_SEND_APPR VARCHAR(15),
@p_SEND_APPR_DT VARCHAR(20),
@p_SIGN_USER VARCHAR(15),
@p_SIGN_DT VARCHAR(15)

AS
/*
--Validation is here
		DECLARE @ERRORSYS NVARCHAR(15) = ''
IF ( NOT EXISTS ( SELECT * FROM BUD_MASTER WHERE BUILDING_ID=@p_BUILDING_ID
	SET @ERRORSYS = ''
	IF @ERRORSYS <> ''
	BEGIN
		SELECT ErrorCode Result, '' BUILDING_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END
*/
BEGIN TRANSACTION
DECLARE @l_BUILDING_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'BUD_MASTER', @l_BUILDING_ID out
		IF @l_BUILDING_ID='' OR @l_BUILDING_ID IS NULL GOTO ABORT
		INSERT INTO BUD_MASTER([BUILDING_ID],[ASSET_ID],[BUILDING_NAME],[BRANCH_ID],[ADDRESS],[OWNER_OR_RENT],
		[NUM_FLOOR],[AREA],[TOTAL_AREA],[RENT_TOTAL_AREA],[UTILZED_AREA],[NOTES],[RECORD_STATUS],
		[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[XML_TEMP],[RENT_AMT],[DEPOSITS_AMT],[RENT_TIME],[IS_SEND_APPR],[SEND_APPR_DT],
					[SIGN_USER], [SIGN_DT]
		)
		VALUES(@l_BUILDING_ID ,@p_ASSET_ID ,@p_BUILDING_NAME ,@p_BRANCH_ID ,@p_ADDRESS ,
		@p_OWNER_OR_RENT ,@p_NUM_FLOOR ,@p_AREA ,@p_TOTAL_AREA ,@p_RENT_TOTAL_AREA ,0 ,
		@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_XML_TEMP,@p_RENT_AMT,@p_DEPOSITS_AMT,@p_RENT_TIME,@p_IS_SEND_APPR
				, @p_SEND_APPR_DT ,@p_SIGN_USER,@p_SIGN_DT)
		IF @@Error <> 0 GOTO ABORT

		-----------BAODNQ 10/3/2022 : Kiểm tra danh sách tầng phải khớp với số tầng của trụ sở
		DECLARE @p_XML_TOTAL_COUNT INT
		SET @p_XML_TOTAL_COUNT = (
			SELECT @p_XML_TEMP.value('count(/Root/XmlData1/BUILDING_AREA_ID)', 'INT') as 'COUNT'
		)
		PRINT @p_XML_TOTAL_COUNT
		IF(@p_NUM_FLOOR <> @p_XML_TOTAL_COUNT)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' BUILDING_ID, 
					N'Số tầng trên lưới danh sách phải khớp với số tầng của trụ sở' ErrorDesc
			RETURN '-1'
		END
		

		Declare @hdoc1 INT
		DECLARE @d_BUILDING_AREA_ID VARCHAR(15),
				@d_FLOOR_NO INT,
				@d_FLOOR_AREA decimal,
				@d_INTERNAL_AREA decimal,
				@d_RENT_AREA decimal,
				@d_UTILZED_AREA decimal,
				@d_NOTES	NVARCHAR(1000)
		Exec sp_xml_preparedocument @hdoc1 Output,@p_XML_TEMP
		DECLARE XmlDataDoc CURSOR FOR
		SELECT *
		FROM OPENXML(@hdoc1,'/Root/XmlData1',2)
		WITH 
		(	
				BUILDING_AREA_ID VARCHAR(15),
				FLOOR_NO INT,
				FLOOR_AREA decimal,
				INTERNAL_AREA decimal,
				RENT_AREA decimal,
				UTILZED_AREA decimal,
				NOTES	NVARCHAR(1000)
		)
		OPEN XmlDataDoc			
		FETCH NEXT FROM XmlDataDoc INTO 
									@d_BUILDING_AREA_ID ,
									@d_FLOOR_NO ,
									@d_FLOOR_AREA ,
									@d_INTERNAL_AREA ,
									@d_RENT_AREA ,
									@d_UTILZED_AREA ,
									@d_NOTES	
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			IF(LEN(	@d_BUILDING_AREA_ID)=0) 	
			EXEC SYS_CodeMasters_Gen 'BUD_AREA_DT', @d_BUILDING_AREA_ID out
			IF @d_BUILDING_AREA_ID='' OR @d_BUILDING_AREA_ID IS NULL GOTO ABORT
			INSERT INTO BUD_AREA_DT([BUILDING_AREA_ID],[BUILD_ID],[FLOOR_NO],[FLOOR_AREA],[INTERNAL_AREA],
			[RENT_AREA],[UTILZED_AREA],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT])
			VALUES(@d_BUILDING_AREA_ID ,@l_BUILDING_ID ,@d_FLOOR_NO ,@d_FLOOR_AREA ,@d_INTERNAL_AREA ,@d_RENT_AREA ,
			@d_UTILZED_AREA ,@d_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) )
			IF @@Error <> 0 GOTO ABORT	
			FETCH NEXT FROM XmlDataDoc INTO 
									@d_BUILDING_AREA_ID ,
									@d_FLOOR_NO ,
									@d_FLOOR_AREA ,
									@d_INTERNAL_AREA ,
									@d_RENT_AREA ,
									@d_UTILZED_AREA ,
									@d_NOTES	
		END
		CLOSE XmlDataDoc
		DEALLOCATE XmlDataDoc
		-----**** UPDATE ------
		--8/12/2021,datmq thêm UTILZED_AREA=(SELECT SUM(UTILZED_AREA))----
		UPDATE BUD_MASTER 
		SET TOTAL_AREA = (SELECT SUM(FLOOR_AREA) FROM BUD_AREA_DT WHERE BUILD_ID=@l_BUILDING_ID)
		WHERE BUILDING_ID=@l_BUILDING_ID
		-- 29/12/2021:datmq  Insert to table PL_PROCESS
		INSERT INTO dbo.PL_PROCESS
					(
					    REQ_ID,
					    PROCESS_ID,
					    CHECKER_ID,
					    APPROVE_DT,
					    PROCESS_DESC,
					    NOTES
					)
					VALUES
					(   @l_BUILDING_ID,       
						'INSERT',
					    @p_MAKER_ID,        
					    GETDATE(), 
					    N'Thêm mới quản lý trụ sở thành công' ,      
					    N'Thêm mới quản lý trụ sở'       
					 )
COMMIT TRANSACTION
SELECT '0' as Result, @l_BUILDING_ID  BUILDING_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		CLOSE XmlDataDoc
		DEALLOCATE XmlDataDoc
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' BUILDING_ID, '' ErrorDesc
		RETURN '-1'
End