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

ALTER PROCEDURE dbo.CM_APPROVE_GROUP_Upd

	@p_REQ_ID [varchar](50) NULL,

	@p_GROUP_APPROVES NVARCHAR(MAX)=NULL,

	@p_CURRENT_STEP INT,

	@p_DONE BIT OUT,

	@p_ERROR_MESS NVARCHAR(MAX) OUT

AS

--Validation is here

	DECLARE @ERRORSYS NVARCHAR(15) = '' 

	IF( @ERRORSYS <> '' )

	BEGIN

		SELECT @p_DONE = 0, @p_ERROR_MESS =  @ERRORSYS

		RETURN '-1'

	END

--BEGIN TRANSACTION

	declare @approveTlb table(STEP_LEVEL INT,USERNAME nvarchar(50),PROCESS BIT)

	declare @listApproveTlb table(STEP_LEVEL INT,GROUP_APPROVE nvarchar(max))

	--SLIP LIST

		insert into @listApproveTlb (STEP_LEVEL, GROUP_APPROVE)

		select  row_number() over(order by (select 1)), [value]

		FROM WSISPLIT(@p_GROUP_APPROVES,',')

		WHERE [value] <>'' 

		--DELETE @listApproveTlb WHERE GROUP_APPROVE=''OR GROUP_APPROVE IS NULL

	--	LIST TO TABLE

		insert into @approveTlb (STEP_LEVEL,USERNAME,PROCESS)

		select A.STEP_LEVEL,B.value	,0

		from @listApproveTlb A

		CROSS APPLY (select [value] FROM WSISPLIT(A.GROUP_APPROVE,';') ) B

	--CHECK NHÓM DUYỆT

	IF(EXISTS(SELECT * FROM @approveTlb GROUP BY USERNAME HAVING COUNT(USERNAME)>1))

	BEGIN

		--ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ID, N'DANH SÁCH NHÓM DUYỆT KHÔNG HỢP LỆ. DANH SÁCH NGƯỜI DUYỆT BỊ TRÙNG. VUI LÒNG KIỂM TRA LẠI.' ErrorDesc

		RETURN '-1'

	END

	--END CHECK NHÓM DUYỆT		

	DECLARE @TOT_APPORVE INT = (SELECT COUNT(*) from @approveTlb)

	DECLARE @index_approve INT,@l_APPROVE_GROUP_ID nvarchar(50)

	--RESET CM_APPROVE_GROUP

		DELETE CM_APPROVE_GROUP WHERE REQ_ID=@p_REQ_ID

		EXEC SYS_CodeMasters_Gen_Mult 'CM_APPROVE_GROUP',@TOT_APPORVE, @l_APPROVE_GROUP_ID out, @index_approve out

		INSERT INTO [dbo].[CM_APPROVE_GROUP]

			   ([APPROVE_GROUP_ID]

			   ,[APPROVE_USERNAME]

			   ,[STEP_LEVEL]

			   ,PROCESS_STATUS

			   ,DONE

			   ,CREATE_DT

			   ,[AUTH_STATUS]

			   ,[REQ_ID]

			   ,[TYPE])

		SELECT ('CAG' + right('0000000000000'+convert(varchar(15),(@index_approve -1 + row_number() over(order by (select 1)))),10)),

			USERNAME

			,STEP_LEVEL

			,0

			,0

			,GETDATE()

			,'U'

			,@p_REQ_ID

			,'APP'

		FROM @approveTlb

	--RESET CM_APPROVE_GROUP

	IF @@Error <> 0 GOTO ABORT

	--

--COMMIT TRANSACTION

SELECT @p_DONE = 1, @p_ERROR_MESS =  @ERRORSYS

RETURN '0'

ABORT:

BEGIN

		--ROLLBACK TRANSACTION

		SELECT @p_DONE = 0, @p_ERROR_MESS =  N'CẬP NHẬT NHÓM DUYỆT THẤT BẠI'

		RETURN '-1'

End

GO

ALTER PROCEDURE dbo.CM_APPROVE_GROUP_Ins

	@p_REQ_ID [varchar](50) NULL,

	@p_GROUP_APPROVES NVARCHAR(max)=NULL,

	@p_DONE BIT OUT,

	@p_ERROR_MESS NVARCHAR(MAX) OUT

AS

--Validation is here

	DECLARE @ERRORSYS NVARCHAR(15) = '' 

	IF( @ERRORSYS <> '' )

	BEGIN

		SELECT @p_DONE = 0, @p_ERROR_MESS =  @ERRORSYS

		RETURN '-1'

	END

	--BEGIN TRANSACTION

	declare @listApproveTlb table(STEP_LEVEL INT,GROUP_APPROVE nvarchar(max))

	declare @approveTlb table(STEP_LEVEL INT,USERNAME nvarchar(50))

	--SLIP LIST

		insert into @listApproveTlb (STEP_LEVEL, GROUP_APPROVE)

		select  row_number() over(order by (select 1)), [value]

		FROM WSISPLIT(@p_GROUP_APPROVES,',')

		WHERE [value] <>'' 

		--DELETE @listApproveTlb WHERE GROUP_APPROVE=''OR GROUP_APPROVE IS NULL

	--	LIST TO TABLE

		insert into @approveTlb (STEP_LEVEL,USERNAME)

		select A.STEP_LEVEL,B.value	 

		from @listApproveTlb A

		CROSS APPLY (select [value] FROM WSISPLIT(A.GROUP_APPROVE,';') ) B

	--CHECK NHÓM DUYỆT

		IF(EXISTS(SELECT * FROM @approveTlb GROUP BY USERNAME HAVING COUNT(USERNAME)>1))

		BEGIN

			--ROLLBACK TRANSACTION

			SELECT @p_DONE = 0, @p_ERROR_MESS=N'DANH SÁCH NHÓM DUYỆT KHÔNG HỢP LỆ. DANH SÁCH NGƯỜI DUYỆT BỊ TRÙNG. VUI LÒNG KIỂM TRA LẠI.'

			print 7

			RETURN 0

		END

	--END CHECK NHÓM DUYỆT	

	--START CHECK THƯ KÍ

	/*insert into @approveTlb

	SELECT STEP_LEVEL From @approveTlb

	WHERE dbo.FN_CHECK_ROLE_USER(USERNAME,'TGD')=1*/

	--END CHECK THƯ KÍ

	--INSERT CM_APPROVE_GROUP

	DECLARE @TOT_APPORVE INT = (SELECT COUNT(*) from @approveTlb)

	DECLARE @index_approve INT,@l_APPROVE_GROUP_ID nvarchar(50)

	EXEC SYS_CodeMasters_Gen_Mult 'CM_APPROVE_GROUP',@TOT_APPORVE, @l_APPROVE_GROUP_ID out, @index_approve out

	INSERT INTO [dbo].[CM_APPROVE_GROUP]

           ([APPROVE_GROUP_ID]

           ,[APPROVE_USERNAME]

           ,[STEP_LEVEL]

		   ,PROCESS_STATUS

		   ,DONE

           ,[AUTH_STATUS]

		   ,CREATE_DT

           ,[REQ_ID]

		   ,[TYPE])

	SELECT ('CAG' + right('000000000000000'+convert(varchar(15),(@index_approve -1 + row_number() over(order by (select 1)))),10)),

		USERNAME

		,STEP_LEVEL

		,0

		,0

		,'U'

		,GETDATE()

		,@p_REQ_ID

		,'APP'

	FROM @approveTlb

	IF @@Error <> 0 GOTO ABORT

	--

--COMMIT TRANSACTION

SELECT @p_DONE = 1, @p_ERROR_MESS =  N''

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT @p_DONE = 0, @p_ERROR_MESS =  N'THÊM NHÓM DUYỆT THẤT BẠI'

		RETURN '-1'

End