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_Ins

@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_SIGN_USER	varchar(50)  = 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

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 

*/

	/****PGD KHONG DUOC PHEP THUC HIEN KIEM KE***/

	DECLARE @l_TYPE_CREATE VARCHAR(200)

	SET @l_TYPE_CREATE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_CREATE)

	IF  @l_TYPE_CREATE <> 'CN' AND @l_TYPE_CREATE <> 'HS'

	BEGIN

		SELECT '-1' as Result, '' INVENT_ID, N'Đơn vị không được phép tạo đợ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.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.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 

	(

		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		VARCHAR(100),

		IS_DONE		VARCHAR(100),

		IS_MAIN		VARCHAR(100)

	)

	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

	DECLARE @l_INVENT_ID VARCHAR(15)

	EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_MASTER', @l_INVENT_ID out

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

	INSERT INTO ASS_INVENTORY_MASTER([INVENT_ID],[INVENTORY_DT],[TERM],[BRANCH_ID],[NOTES],[RECORD_STATUS],[AUTH_STATUS_DVKD],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[BRANCH_CREATE],[SIGN_USER],[DEPT_ID],[AUTH_STATUS])

	VALUES(@l_INVENT_ID ,CONVERT(DATETIME, @p_INVENTORY_DT, 103) ,@p_TERM ,@p_BRANCH_ID ,@p_NOTES ,@p_RECORD_STATUS ,@p_AUTH_STATUS_DVKD ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),@p_BRANCH_CREATE,@p_SIGN_USER,@p_DEPT_ID,'E')

	IF @@Error <> 0 GOTO ABORT

	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 @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 = @l_INVENT_ID

	--END

	--------

		WHILE @@FETCH_STATUS = 0	

		BEGIN

			EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_DT', @INVENTDT_ID out

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

			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,@l_INVENT_ID,@ASSET_ID,@ASSET_STATUS,@INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE)

			-- PHONGNT 7/7/2022 Thêm vào ASS_INVENTORY_DT_RPT dùng cho app kiểm kê

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

			VALUES(@INVENTDT_ID,@l_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 @ASSET_ID, @ASSET_STATUS, @INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE

		END

	CLOSE InventDetail

	DEALLOCATE InventDetail

	-- GiaNT 23/08/2021	

	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 ,@l_INVENT_ID, @PARTY_NAME, @PARTY_ROLE,@IS_RECIVE_MAIL,'0',@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

	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 ,@l_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, @l_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