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

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