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

ALTER PROCEDURE dbo.ASS_INVENTORY_MASTER_Upd

@P_INVENT_ID VARCHAR(15) = NULL,

@p_INVENTORY_DT	VARCHAR(20) = NULL,

@p_TERM	nvarchar(20)  = NULL,

@p_BRANCH_ID	varchar(15)  = NULL,

@p_DEPT_ID	varchar(15)  = NULL,

@p_NOTES	NVARCHAR(1000)  = NULL,

@p_RECORD_STATUS	varchar(1)  = NULL,

@p_AUTH_STATUS_DVKD	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_INVENTDETAILS XML = NULL,

@p_BRANCH_CREATE	VARCHAR(15) = NULL,

@p_PARTYDETAILS XML = NULL,

@p_UNSTOCKEDDETAILS XML = NULL,

@p_SIGN_USER VARCHAR(20) = NULL

AS

--Validation is here

/*

DECLARE @ERRORSYS NVARCHAR(15) = '' 

  IF ( NOT EXISTS ( SELECT * FROM ASS_INVENTORY_MASTER WHERE ))

	 SET @ERRORSYS = ''

IF @ERRORSYS <> '' 

BEGIN

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

	RETURN '0'

END 

*/

IF (SELECT AUTH_STATUS FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @P_INVENT_ID) = 'A'

BEGIN

	SELECT '-1' as Result, '' INVENT_ID, N'Thông tin đã được duyệt nên không được phép chỉnh sửa!' ErrorDesc

	return '-1'

END

IF (EXISTS(SELECT * FROM ASS_INVENTORY_MASTER aim WHERE aim.BRANCH_ID = @p_BRANCH_ID

  AND aim.INVENT_ID <> @P_INVENT_ID

  AND aim.TERM = @p_TERM AND aim.INVENTORY_DT = CONVERT(DATETIME, @p_INVENTORY_DT, 103)))

BEGIN

  SELECT '-1' as Result, '' INVENT_ID, N'Đơn vị đã tồn tại kỳ kiểm kê có cùng ngày và đợt kiểm kê.' ErrorDesc

  RETURN '-1'

END

IF (EXISTS(SELECT * FROM ASS_INVENTORY_MASTER aim 

  WHERE aim.BRANCH_ID = @p_BRANCH_ID

    AND aim.INVENT_ID <> @P_INVENT_ID

    AND (aim.AUTH_STATUS <> 'A' OR aim.AUTH_STATUS IS NULL OR aim.AUTH_STATUS_DVKD <> 'A' OR aim.AUTH_STATUS_DVKD IS NULL)))

BEGIN

  SELECT '-1' as Result, '' INVENT_ID, N'Đơn vị có kỳ kiểm kê chưa hoàn thành nên không thể tạo thêm kỳ kiểm kê mới.' ErrorDesc

  RETURN '-1'

END

	Declare @hdoc INT

	Exec sp_xml_preparedocument @hdoc Output,@P_INVENTDETAILS

	DECLARE InventDetail CURSOR FOR

	SELECT *

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

	WITH 

	(

		INVENTDT_ID VARCHAR(15),

		ASSET_ID	varchar(15)  ,

		ASSET_STATUS	nvarchar(20) ,

		INVENT_DESC	nvarchar(1000) ,

		NOTES nvarchar(500),

		BRANCH_USE	varchar(15) ,

		DEPT_USE	varchar(15) ,

		REMAIN_VALUE decimal(18,0)

	)

	OPEN InventDetail

	-- GiaNT 23/08/2021

	Declare @hdocParty INT

	Exec sp_xml_preparedocument @hdocParty Output,@p_PARTYDETAILS

		DECLARE PartyDetail CURSOR FOR

	SELECT *

	FROM OPENXML(@hdocParty,'/Root/PartyDetail',2)

	WITH 

	(

		PARTY_ID		VARCHAR(15),

		INVENT_ID		VARCHAR(15),

		PARTY_NAME		NVARCHAR(100),

		PARTY_ROLE		NVARCHAR(100),

		IS_RECIVE_MAIL		NVARCHAR(100),

		IS_DONE		NVARCHAR(100),

		IS_MAIN	NVARCHAR(1)

	)

	OPEN PartyDetail

	Declare @hdocUnstocked INT

	Exec sp_xml_preparedocument @hdocUnstocked Output,@p_UNSTOCKEDDETAILS

		DECLARE UnstockedDetail CURSOR FOR

	SELECT *

	FROM OPENXML(@hdocUnstocked,'/Root/UnstockedDetail',2)

	WITH 

	(

		UNSTOCKED_ID	VARCHAR(15),

		INVENT_ID		VARCHAR(15),

		ASS_NAME		NVARCHAR(1000),

		SERIAL			NVARCHAR(MAX),

		BRANCH_ID		NVARCHAR(50),

		DEPT_ID			NVARCHAR(50),

		USE_DATE		VARCHAR(20)

	)

	OPEN UnstockedDetail

BEGIN TRANSACTION

	IF @p_APPROVE_DT = '' SET @p_APPROVE_DT = NULL

	UPDATE ASS_INVENTORY_MASTER SET [INVENTORY_DT] = CONVERT(DATETIME, @p_INVENTORY_DT, 103),[TERM] = @p_TERM,[BRANCH_ID] = @p_BRANCH_ID,[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,[AUTH_STATUS_DVKD] = @p_AUTH_STATUS_DVKD,[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),

	BRANCH_CREATE=@p_BRANCH_CREATE,SIGN_USER=@p_SIGN_USER

	WHERE  INVENT_ID= @p_INVENT_ID

	IF @@Error <> 0 GOTO ABORT

	DELETE FROM ASS_INVENTORY_DT WHERE INVENT_ID = @P_INVENT_ID 

	Declare @INVENTDT_ID VARCHAR(15),@ASSET_ID varchar(15), @ASSET_STATUS nvarchar(20),@INVENT_DESC nvarchar(1000), 

	@NOTES nvarchar(500), @DEPT_USE VARCHAR(15), @BRANCH_USE VARCHAR(15), @REMAIN_VALUE decimal(18,0)

	FETCH NEXT FROM InventDetail INTO @INVENTDT_ID,@ASSET_ID, @ASSET_STATUS, @INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE

	--------THIEUVQ THEM PHAN DEPT_ID CHO DOT KIEM KE, TAM THOI LAY THEO TAI SAN DAU TIEN TRONG BANG INVENTORY_DT

	IF @p_BRANCH_ID = 'DV0001'

	BEGIN

		UPDATE ASS_INVENTORY_MASTER SET DEPT_ID = @DEPT_USE WHERE INVENT_ID = @P_INVENT_ID

	END

	--------

		WHILE @@FETCH_STATUS = 0	

		BEGIN

			IF(LEN (@INVENTDT_ID) = 0 )

			BEGIN

				EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_DT', @INVENTDT_ID out

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

			END

			INSERT INTO ASS_INVENTORY_DT([INVENTDT_ID],[INVENT_ID],[ASSET_ID],[ASSET_STATUS],[INVENT_DESC], [NOTES], [BRANCH_USE], [DEPT_USE], [REMAIN_VALUE])

			VALUES(@INVENTDT_ID,@P_INVENT_ID,@ASSET_ID,@ASSET_STATUS,@INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE)

		-- next Group_Id

			IF @@ERROR <> 0 GOTO ABORT

			FETCH NEXT FROM InventDetail INTO @INVENTDT_ID,@ASSET_ID, @ASSET_STATUS, @INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE

		END

	CLOSE InventDetail

	DEALLOCATE InventDetail

	-- GiaNT 23/08/2021

	DELETE FROM ASS_INVENTORY_PARTY_DT WHERE INVENT_ID = @P_INVENT_ID

	DECLARE		

		@PARTY_ID			VARCHAR(15),

		@INVENT_ID			VARCHAR(15),

		@PARTY_NAME			NVARCHAR(200),

		@PARTY_ROLE			NVARCHAR(200),

		@IS_RECIVE_MAIL		VARCHAR(1),

		@IS_DONE		VARCHAR(1),

		@IS_MAIN		VARCHAR(1)

	FETCH NEXT FROM PartyDetail INTO  @PARTY_ID,@INVENT_ID,@PARTY_NAME,@PARTY_ROLE,@IS_RECIVE_MAIL,@IS_DONE,@IS_MAIN

		WHILE @@FETCH_STATUS = 0

		BEGIN			

			DECLARE @l_PARTY_ID VARCHAR(15)	

			EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_PARTY_DT', @l_PARTY_ID out

			IF @l_PARTY_ID='' OR @l_PARTY_ID IS NULL 

			BEGIN

				GOTO ABORT

			END

			INSERT INTO ASS_INVENTORY_PARTY_DT([PARTY_ID], [INVENT_ID], [PARTY_NAME], [PARTY_ROLE],[IS_RECIVE_MAIL],[IS_DONE],[IS_MAIN])

			VALUES(@l_PARTY_ID ,@p_INVENT_ID, @PARTY_NAME, @PARTY_ROLE,@IS_RECIVE_MAIL,@IS_DONE,@IS_MAIN)

			IF @@Error <> 0 GOTO ABORT

			FETCH NEXT FROM PartyDetail INTO  @PARTY_ID,@INVENT_ID,@PARTY_NAME,@PARTY_ROLE,@IS_RECIVE_MAIL,@IS_DONE,@IS_MAIN

		END

	CLOSE PartyDetail

	DEALLOCATE PartyDetail

	DELETE FROM dbo.ASS_INVENTORY_UNSTOCKED WHERE INVENT_ID = @P_INVENT_ID

	DECLARE

		@UNSTOCKED_ID	VARCHAR(15),

		@ASS_NAME		NVARCHAR(1000),

		@SERIAL			NVARCHAR(MAX),

		@BRANCH_ID		NVARCHAR(50),

		@DEPT_ID		NVARCHAR(50),	

		@USE_DATE		VARCHAR(20)

	FETCH NEXT FROM UnstockedDetail INTO @UNSTOCKED_ID,@INVENT_ID,@ASS_NAME,@SERIAL,@BRANCH_ID,@DEPT_ID,@USE_DATE

		WHILE @@FETCH_STATUS = 0

		BEGIN			

			DECLARE @l_UNSTOCKED_ID VARCHAR(15)	

			EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_UNSTOCKED', @l_UNSTOCKED_ID out

			IF @l_UNSTOCKED_ID='' OR @l_UNSTOCKED_ID IS NULL 

			BEGIN

				GOTO ABORT

			END

			INSERT INTO ASS_INVENTORY_UNSTOCKED([UNSTOCKED_ID], [INVENT_ID], [ASS_NAME], [SERIAL],[BRANCH_ID],[DEPT_ID],[USE_DATE])

			VALUES(@l_UNSTOCKED_ID ,@p_INVENT_ID, @ASS_NAME, @SERIAL,@BRANCH_ID,@DEPT_ID,CONVERT(DATETIME,@USE_DATE,103))

			IF @@Error <> 0 GOTO ABORT

			FETCH NEXT FROM UnstockedDetail INTO  @UNSTOCKED_ID,@INVENT_ID,@ASS_NAME,@SERIAL,@BRANCH_ID,@DEPT_ID,@USE_DATE

		END

	CLOSE UnstockedDetail

	DEALLOCATE UnstockedDetail

COMMIT TRANSACTION

SELECT '0' as Result, @P_INVENT_ID  INVENT_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		CLOSE InventDetail

		DEALLOCATE InventDetail

		ROLLBACK TRANSACTION

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

		RETURN '-1'

End