-- use [SOffice8_BCCR] --[S-Office8_Mosquito]
-- go
drop procedure if exists [usp_ssERP8_update_sys_acl_permissions]
go 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC [dbo].[usp_ssERP8_update_sys_acl_permissions] @id_sys_acl_roles=1,@is_allowed=1,@id_objectType=2,@id_object=81,@SP_MODE=N'TEST1'


--====================================================================================================================================
CREATE PROCEDURE [dbo].[usp_ssERP8_update_sys_acl_permissions]
-- Author		Щерю Щерев																									
-- Create date	14/08/2025
-- Version		
--				
-- Serever		MS SQL 2019
-- Description	Updating the rights of role or user to access objects
-- Returns		0 = fail, 1 = success
-- TempTables	
-- Constraint	Ако едно право е забранено за ролята => по подразбиране е забранено, но може да бъде позволено за потребителя
--				Ако едно право е позволено за ролята => по подразбиране е позволено за потребителя
-- Parameters
		@id_objectType		int				NULL=NULL			--	1. id of object type /* 1-nomenclature 2-document 3-report */.null=all objects types
	   ,@id_object			int				NULL=NULL			--	2. ID of objects. null=all objects of this type OR all objects if @id_objectType also is null
	  -------------------------------------- 
	   ,@id_resource		nvarchar(MAX)	NULL=NULL			--	3. ID of resource. null=all resources for this object
																--	[{"id_resource":1},{"id_resource":2},{"id_resource":3}]	 - a few resources
	   ,@id_operation		int				NULL=NULL			--  4. ID of operation. if @is_allowed=1 => null='READ/UPDATE'
																--						if @is_allowed=0 => null='Forbidden'
	   ,@id_sys_acl_roles	nvarchar(MAX)	NULL=NULL			--	5. ID of role. null= for all roles
																--	[{"id_role":1},{"id_role":2},{"id_role":3}]	- a few roles 		
	   ,@id_sys_users		nvarchar(MAX)	NULL=NULL			--	6. ID of user. null= NO user access rights are set
																--	[{"id_user":1},{"id_user":2},{"id_user":3}]	 - a few users
	   ,@is_allowed			bit				NULL=1				--	7. 1=allowed 0=deny
	   ,@only_insert		bit				NULL=0				--	8. 1=Do NOT modify existing right 0=modify all rights
	  -------------------------------------- 
	   ,@ErrMessage			nvarchar(MAX)	NULL=''	OUTPUT		--	9. Text of error
	   ,@Return				nvarchar(2)		NULL=''	OUTPUT		--	10. Exit code parameter
	   ,@SP_MODE		 	nvarchar(30) 	NULL='EXECUTE'  	--	11. Execution mode / Дали е в работен или тестов режим
--====================================================================================================================================
AS BEGIN
/* manualUpdateFlag: No */
BEGIN TRY
/*
	DECLARE  @id_objectType int=2				          
			,@id_object int=9					          
			,@id_resource nvarchar(MAX)			          
			,@id_operation int
			,@id_sys_acl_roles nvarchar(MAX)
			,@id_sys_users  nvarchar(MAX)
			,@is_allowed bit=1					          
			,@only_insert bit=0					          
			---------------------------------          
			,@ErrMessage nvarchar(MAX)=''			                  
			,@Return nvarchar(2)=''			                      
			,@SP_MODE nvarchar(255)='TEST1'   
--*/

-- ,,,@id_resource int,@id_operation int,@id_sys_acl_roles int,@id_sys_users	int,=1,@ErrMessage ,@Return 
	SET NOCOUNT ON;
		DECLARE @ProcedureName sysname = ISNULL(OBJECT_NAME(@@PROCID),N'usp_ssERP8_update_sys_acl_permissions')
		DECLARE @SQLstr nvarchar(MAX), @nRetValue int = 1, @Status nvarchar(1024) = N'0', @ExitCode int
		DECLARE @ParamDefinition nvarchar(MAX), @CommandString nvarchar(MAX)
		DECLARE @HOST nvarchar(255) = HOST_NAME()
		DECLARE @Output_json nvarchar(MAX)
			SET @ErrMessage = N''
			SET @Return = N''

	-- 1. Parameters sniffing
		SET @Status += ',1'
		-- @SP_MODE
			SET @SP_MODE = case when @SP_MODE=N'TEST1'																				then N'TEST1' 
						when @SP_MODE=N'TEST'																						then N'TEST' 
						when exists ( select 1 from [dbo].[sys_global_settings] where [name]=N'SP_MODE' AND [value]=N'TEST')		then N'TEST'
						when exists ( select 1 from [dbo].[sys_global_settings] where [name]=N'SP_MODE' AND [value]=@ProcedureName)	then N'TEST'
						else																											 N'EXECUTE' 
					end
		-- @id_objectType -- select * from sys_systemObjects
		-- select * from sys_acl_resources; select DISTINCT objectType from sys_acl_resources
		-- @id_objectType: null = all resources, 0 = main resources (only without objectType), 1 = nomenclatures, 2 = documents, 3 = reports
				DECLARE @sys_systemObjects_description nvarchar(255)
				IF ISNULL(@id_objectType,-1)=0		BEGIN -- @id_objectType=0 main resources
						SELECT	 @sys_systemObjects_description = N'Main access settings'
								,@id_object=null
													END
				ELSE IF @id_objectType is not null	-- @id_objectType in (1,2,3) посочен е тип на обекта за който ще се разглеждат правата
					 SELECT @sys_systemObjects_description = ( select cast([description] as nvarchar(MAX)) from dbo.sys_systemObjects where [id]= @id_objectType )+N' access settings'

					 ELSE  -- @id_objectType is null	= processing for all resources
						begin SET @id_object=null; SET @sys_systemObjects_description=N'All resources access settings' end
			IF @sys_systemObjects_description is null begin SET @ErrMessage += N'Invalid object type parameter;'; SET @nRetValue = 0 end

		-- @id_object -- select [id] from [dbo].[sys_docType]
			IF ISNULL(@id_objectType,-1) in (1,2,3) AND @id_object is not null	BEGIN -- посочено е id на конкретен обект (номенклатура, документ, справка...)
				SET @id_object = case	when @id_objectType=1 then (select [id] from [dbo].[sys_nmcl] where [id]=@id_object)
										when @id_objectType=2 then (select [id] from [dbo].[sys_docType] where [id]=@id_object)	
										when @id_objectType=3 then (select [id] from [dbo].[sys_reports] where [id]=@id_object)	
									--  when @id_objectType=  then (select [id] from [dbo].[sys_print_forms] where [id]=@id_object)	
										else null
								  end
				IF  @id_object is null begin SET @ErrMessage += N'Invalid id_object parameter;'; SET @nRetValue = 0 end
																				END	
		-- @id_resource -- select * from [dbo].[sys_acl_resources]
			DROP TABLE IF EXISTS #sys_acl_resources
			SELECT TOP(0) * INTO #sys_acl_resources FROM [dbo].[sys_acl_resources]
				ALTER TABLE #sys_acl_resources DROP COLUMN [id]
				ALTER TABLE #sys_acl_resources ADD [id] int
			IF @id_resource is not null				BEGIN -- посочен е конкретен ресурс
				IF ISNUMERIC( @id_resource )=1
					INSERT INTO #sys_acl_resources ([id],[parent_id],[name],[type],[comment],[resource_default],[is_trackable],[flag],[insert_date],[objectType],[objectId])
					SELECT [id],[parent_id],[name],[type],[comment],[resource_default],[is_trackable],[flag],[insert_date],[objectType],[objectId]
					FROM [dbo].[sys_acl_resources] WHERE [id]=cast(@id_resource as int)
				
				ELSE IF ISJSON( @id_resource )=1
					INSERT INTO #sys_acl_resources ([id],[parent_id],[name],[type],[comment],[resource_default],[is_trackable],[flag],[insert_date],[objectType],[objectId])
					SELECT DISTINCT [sys_acl_resources].[id],[parent_id],[name],[type],[comment],[resource_default],[is_trackable],[flag],[insert_date],[objectType],[objectId]
					FROM OPENJSON(@id_resource) with ( id_resource int ) as js
						JOIN [dbo].[sys_acl_resources] ON [sys_acl_resources].[id]=js.[id_resource]
					 
					 ELSE begin SET @ErrMessage += N'Invalid @id_resource parameter;'; SET @nRetValue = 0 end
													END
			ELSE -- @id_resource is null
					INSERT INTO #sys_acl_resources ([id],[parent_id],[name],[type],[comment],[resource_default],[is_trackable],[flag],[insert_date],[objectType],[objectId])
					SELECT [id],[parent_id],[name],[type],[comment],[resource_default],[is_trackable],[flag],[insert_date],[objectType],[objectId] FROM [dbo].[sys_acl_resources]
			IF not exists ( select 1 from #sys_acl_resources ) begin SET @ErrMessage += N'Invalid id_resource parameter;'; SET @nRetValue = 0 end


		-- @id_operation -- select * from [dbo].[sys_acl_operations]
			DECLARE @sys_acl_operations_name nvarchar(255)
			IF @id_operation is not null			BEGIN -- посочен е конкретна операция
				SELECT @sys_acl_operations_name = (select ISNULL([name],N'') from [dbo].[sys_acl_operations] where [id]=@id_operation)
				IF  @sys_acl_operations_name is null begin SET @ErrMessage += N'Invalid id_operation parameter;'; SET @nRetValue = 0 end
													END
			ELSE IF @is_allowed = 1					BEGIN -- Иска се разрешаване => приема се че се разрешават операцията с най-нисък приоритет (най-високо ниво) 	
				 select TOP(1)	 @sys_acl_operations_name =ISNULL([name],N'')
								,@id_operation = [id] 
				 from [dbo].[sys_acl_operations] 
				 where ISNULL([priority],0)>0
				 order by [priority] desc
					IF  @sys_acl_operations_name is null begin SET @ErrMessage += N'Invalid id_operation parameter;'; SET @nRetValue = 0 end
													END	
				 ELSE IF @is_allowed = 0			BEGIN  -- Иска се разрешаване => приема се че се разрешават операцията с най-висок приоритет (забрана)
					  select TOP(1)  @sys_acl_operations_name =ISNULL([name],N'')
									,@id_operation = [id] 
					  from [dbo].[sys_acl_operations] 
					  where ISNULL([priority],0)>0
					  order by [priority] asc
					 	IF  @sys_acl_operations_name is null begin SET @ErrMessage += N'Invalid id_operation parameter;'; SET @nRetValue = 0 end
													END	
													
		-- @id_sys_acl_roles
			DECLARE @tblRoles TABLE ( [id_role] int )
			IF @id_sys_acl_roles is not null		BEGIN -- посочена е конкретна роля
				IF ISNUMERIC( @id_sys_acl_roles )=1
					INSERT INTO @tblRoles ( [id_role] ) SELECT [id] FROM [dbo].[sys_acl_roles] WHERE [id]=cast(@id_sys_acl_roles as int)
				ELSE IF ISJSON( @id_sys_acl_roles )=1
					INSERT INTO @tblRoles ( [id_role] )	SELECT DISTINCT [id] 
														FROM OPENJSON(@id_sys_acl_roles) with ( id_role int ) as js
															JOIN [dbo].[sys_acl_roles] ON [sys_acl_roles].[id]=js.[id_role]
					 ELSE begin SET @ErrMessage += N'Invalid @id_sys_acl_roles parameter;'; SET @nRetValue = 0 end
													END
			ELSE -- @id_sys_acl_roles is null
					INSERT INTO @tblRoles ( [id_role] ) SELECT DISTINCT [id] FROM [dbo].[sys_acl_roles]

		-- @id_sys_users
			DECLARE @tblUsers TABLE ( [id_user] int )
			IF @id_sys_users is not null			BEGIN -- посочен е конкретен потребител
				IF ISNUMERIC( @id_sys_users )=1
					INSERT INTO @tblUsers ( [id_user] ) SELECT [id] FROM [dbo].[sys_users] WHERE [id]=cast(@id_sys_users as int)
				ELSE IF ISJSON( @id_sys_users )=1 
					INSERT INTO @tblUsers ( [id_user] )	SELECT DISTINCT [id] 
														FROM OPENJSON(@id_sys_users) with ( id_user int ) as js
															JOIN [dbo].[sys_users] ON [sys_users].[id]=js.[id_user]


				SET @id_sys_users = (select [id] from [dbo].[sys_users] where [id]=@id_sys_users)
				IF @id_sys_users is null begin SET @ErrMessage += N'Invalid @id_sys_users parameter;'; SET @nRetValue = 0 end
				ELSE IF @id_sys_acl_roles is null SET @id_sys_acl_roles = 0 -- ако потребителя е валиден и не е посочена конкретна рола => за ролите не се пипат правата
													END
		-- if @id_sys_users AND @id_sys_acl_roles are invalid
		IF	not exists (select 1 from @tblRoles )
		AND	not exists (select 1 from @tblUsers )
			begin SET @ErrMessage += N'Missing USER or ROLE to grants permissions;'; SET @nRetValue = 0 end

	-- 2. Logging the data for analysis / Запазване на данни за анализ
		SET @Status += ',2'
		DECLARE @TestSaveParamID int = 0
	IF OBJECT_ID(N'stepsoft.dbo.PARAMETERS') is not null AND LEFT(@SP_MODE,4)=N'TEST'	BEGIN
		DECLARE @PrmString varchar(7168) = ISNULL(cast(@id_objectType as varchar(10)),'null')+','		--	1. id of object type /* 1-nomenclature 2-document 3-report */
										 + ISNULL(cast(@id_object as varchar(10)),'null')+','			--	2. ID of objects. null=all objects of this type
										 + ISNULL(cast(@id_resource as varchar(10)),'null')+','			--	3. ID of resource. null=all resources for this object
										 + ISNULL(cast(@id_operation as varchar(10)),'null')+','		--  4. ID of operation from sys_acl_oprations null=lowest priority operation
										 + ISNULL(cast(@id_sys_acl_roles as varchar(MAX)),'null')+','	--	5. ID of role. null= for all roles
										 + ISNULL(cast(@id_sys_users as varchar(MAX)),'null')+','		--	6. ID of user. null= for all users
										 + ISNULL(cast(@is_allowed as varchar(10)),'null')+','			--	7. 1=allowed 0=deny
										 + ISNULL(@SP_MODE,'null')
		DECLARE @PrmJson nvarchar(MAX) = (	SELECT	 @id_objectType		as [@id_objectType]				--	1. id of object type /* 1-nomenclature 2-document 3-report */
													,@id_object			as [@id_object]					--	2. ID of objects. null=all objects of this type
													,@id_resource		as [@id_resource]				--	3. ID of resource. null=all resources for this object
													,@id_operation		as [@id_operation]				--  4. ID of operation from sys_acl_oprations null=lowest priority operation
													,@id_sys_acl_roles	as [@id_sys_acl_roles]			--	5. ID of role. null= for all roles
													,@id_sys_users		as [@id_sys_users]				--	6. ID of user. null= for no users
													,@is_allowed		as [@is_allowed]				--	7. 1=allowed 0=deny
													,@ErrMessage 		as [@ErrMessage]				--	8. Text of error
													,@Return			as [@Return]					--	9. Exit code parameter
													,@SP_MODE			as [@SP_MODE]					--	10. Execution mode / Дали е в работен или тестов режим
											FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )
		DECLARE @DBName nvarchar(128) = DB_NAME()
			 EXEC [stepsoft].[dbo].[usp_ssTestSaveParam] @PrmMessage = @PrmString, @CallingModule = @ProcedureName, @XMLString = null
														,@Separator = ',', @DataBase = @DBName, @RecordID = @TestSaveParamID OUTPUT
														,@PrmJson = @PrmJson 
																						END -- IF OBJECT_ID(N'stepsoft.dbo.PARAMETERS') is not null AND LEFT(@SP_MODE,4)=N'TEST'	BEGIN
	print @ProcedureName+'->SP_MODE = ' + ISNULL(@SP_MODE,'NULL')+' '+@ProcedureName+'->TestSaveParamID = ' + ISNULL(cast(@TestSaveParamID as varchar(10)),'NULL')
	IF @SP_MODE=N'TEST1' select  @ProcedureName+N'('+@Status+N')' as [procedure], N'Parameters' as tbl
								,@id_objectType	as '@id_objectType',@id_object as '@id_object',@id_resource as '@id_resource'
								,@id_operation as '@id_operation', @sys_acl_operations_name as '@sys_acl_operations_name'
								,@id_sys_acl_roles as '@id_sys_acl_roles',@id_sys_users as '@id_sys_users',@is_allowed as '@is_allowed', @Return as '@Return',@DBName as '@DBName'
								,@SP_MODE as '@SP_MODE',@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
	IF @SP_MODE=N'TEST1' and exists ( select 1 from @tblRoles)	select  @ProcedureName+N'('+@Status+N')' as [procedure], N'table @tblRoles' as tbl,* from @tblRoles
	ELSE IF @SP_MODE=N'TEST1'									select  @ProcedureName+N'('+@Status+N')' as [procedure], N'No rows in table @tblRoles' as tbl
	IF @SP_MODE=N'TEST1' and exists ( select 1 from @tblUsers)	select  @ProcedureName+N'('+@Status+N')' as [procedure], N'table @tblUsers' as tbl,* from @tblUsers
	ELSE IF @SP_MODE=N'TEST1'									select  @ProcedureName+N'('+@Status+N')' as [procedure], N'No rows in table @tblUsers' as tbl


/*
select * from sys_acl_resources
select * from sys_acl_resource_role_rel
select * from sys_acl_resources where name like N'report/%' order by name
select * from sys_reports where id = 95
select * from sys_reports_settings 
select * from sys_nmcl
*/
--======================================================================================================================================
	DECLARE @Output_del TABLE (	[action] nvarchar(255), [id_resource] int, [name_resource] nvarchar(1024), [id_objectType] int, [id_object] int )
	--DECLARE @Output_res TABLE ( [action] nvarchar(255), [id_resource] int, [name_resource] nvarchar(1024), [flag] bit, [id_objectType] int, [id_object] int )
	DECLARE @Output_rel TABLE ( [action] nvarchar(255), [id_resource] int, [name_resource] nvarchar(1024), [id_role] int, [is_allowed] int, [operation] nvarchar(255), [id_objectType] int, [id_object] int  )
	DECLARE @Output_usr TABLE ( [action] nvarchar(255), [id_resource] int, [name_resource] nvarchar(1024), [id_user] int, [is_allowed] int, [operation] nvarchar(255), [id_objectType] int, [id_object] int   )

	-- 3. Delete duplicate names from #sys_acl_resources
		SET @Status += ',3'
		;WITH CTE
		   AS	(  SELECT *, ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY [id]) as Duplicate
				   FROM #sys_acl_resources
				)
		--SELECT * FROM CTE WHERE Duplicate > 1
		DELETE FROM CTE 
			   -- [action], [id_resource], [name_resource], [id_objectType],	  [id_object] int
			OUTPUT N'DELETE', deleted.[id],  deleted.[name],  deleted.[objectType], deleted.[objectId] INTO @Output_del 
		WHERE Duplicate > 1 -- AND [name] like N'DocumentController%'

	-- 4. Grants permissions of roles
	-- MERGE sys_acl_resource_role_rel AND sys_acl_resources
		SET @Status += ',4'
	IF @SP_MODE = N'TEST1'	BEGIN
				SELECT	DISTINCT
						 @ProcedureName+N'('+@Status+N')' as [procedure], N'Roles' as tbl
						,[sys_acl_resources].[id]					as [id_resource]
						,[sys_acl_resources].[name]					as [name_resource]
						,[sys_acl_roles].[id]						as [id_role]
						,case when [sys_acl_resources].[name] LIKE N'%element%'
								then @sys_acl_operations_name
								else null
						  end										as [operation]
						,@is_allowed								as [is_allowed]
						,null										as [owner]
						,null										as [custom_sql]
						,null										as [comment]
						,GetDate()									as [insert_date]
						,[sys_acl_resources].[objectType]		 	as [objectType]
						,[sys_acl_resources].[objectId]				as [objectId]
				FROM @tblRoles as tblRoles
					JOIN [dbo].[sys_acl_roles] ON [sys_acl_roles].[id]=tblRoles.[id_role]
					CROSS JOIN #sys_acl_resources as sys_acl_resources
				ORDER BY [sys_acl_resources].[id]
				SELECT	DISTINCT
						 @ProcedureName+N'('+@Status+N')' as [procedure], N'Users' as tbl
						,[sys_acl_resources].[id]					as [id_resource]
						,[sys_acl_resources].[name]					as [name_resource]
						,[sys_users].[id]							as [id_user]
						,case when [sys_acl_resources].[name] LIKE N'%element%'
								then @sys_acl_operations_name
								else null
						  end										as [operation]
						,@is_allowed								as [is_allowed]
						,null										as [owner]
						,null										as [custom_sql]
						,null										as [comment]
						,GetDate()									as [insert_date]
						,[sys_acl_resources].[objectType]		 	as [objectType]
						,[sys_acl_resources].[objectId]				as [objectId]
				FROM @tblUsers as tblUsers
					JOIN [dbo].[sys_users] ON [sys_users].[id]=tblUsers.[id_user]
					CROSS JOIN #sys_acl_resources as sys_acl_resources
				ORDER BY [sys_acl_resources].[id]
							END -- @SP_MODE = N'TEST1'

	IF @nRetValue = 1 AND exists ( select 1 from @tblRoles) AND @SP_MODE <> N'TEST1'	BEGIN
		MERGE [dbo].[sys_acl_resource_role_rel]							AS tgt  -- select * from [dbo].[sys_acl_resource_role_rel]
		USING (	
				/* DECLARE @id_object int=70,@SP_MODE nvarchar(30)='TEST1',@id_resource int,@id_role int,@id_user	int,@is_allowed	bit,@ErrMessage nvarchar(MAX),@Return nvarchar(2)
				select @id_resource as [@id_resource], @id_role as [@id_role],@id_object as [@id_object]
				--*/
				SELECT	DISTINCT
						 [sys_acl_resources].[id]					as [id_resource]
						,[sys_acl_resources].[name]					as [name_resource]
						,[sys_acl_roles].[id]						as [id_role]
						,case when [sys_acl_resources].[name] LIKE N'%element%'
								then @sys_acl_operations_name
								else null
						  end										as [operation]
						,@is_allowed								as [is_allowed]
						,null										as [owner]
						,null										as [custom_sql]
						,null										as [comment]
						,GetDate()									as [insert_date]
						,[sys_acl_resources].[objectType]		 	as [objectType]
						,[sys_acl_resources].[objectId]				as [objectId]
				FROM @tblRoles as tblRoles
					JOIN [dbo].[sys_acl_roles] ON [sys_acl_roles].[id]=tblRoles.[id_role]
					CROSS JOIN #sys_acl_resources as sys_acl_resources 
			  )															AS src
		ON	(	tgt.id_resource=src.id_resource AND tgt.id_role=src.id_role ) -- ако ресурса съвпада и ролята съвпада
		
			WHEN MATCHED AND @only_insert = 0 THEN UPDATE SET [is_allowed] = src.[is_allowed]
															 ,[operation]  = src.[operation]
															 ,[objectId]   = src.[objectId]	 
															 ,[objectType] = src.[objectType]
			WHEN NOT MATCHED THEN INSERT (	[id_resource],[id_role],[operation],[is_allowed],[owner],[custom_sql],[comment],[insert_date],[objectType],[objectId] )
								  VALUES (	[id_resource],[id_role],[operation],[is_allowed],[owner],[custom_sql],[comment],[insert_date],[objectType],[objectId] )
			OUTPUT $action, src.[id_resource], src.[name_resource], src.[id_role],Inserted.[is_allowed],Inserted.[operation],src.[objectType],src.[objectId] 
				INTO @Output_rel;
					  
																						END -- IF @nRetValue = 1 AND exists ( select 1 from @tblUsers) AND @SP_MODE <> N'TEST1'


	-- 5. Grants permissions of users
	-- MERGE sys_acl_resource_role_rel AND sys_acl_resources
		SET @Status += ',5'
	IF @nRetValue = 1 AND exists (select 1 from @tblUsers) AND @SP_MODE <> N'TEST1'	BEGIN
		MERGE [dbo].[sys_acl_resource_user_rel]						AS tgt  -- select * from [dbo].[sys_acl_resource_user_rel]
		USING (	
				SELECT	DISTINCT
						 [sys_acl_resources].[id]					as [id_resource]
						,[sys_acl_resources].[name]					as [name_resource]
						,[sys_users].[id]							as [id_user]
						,case when [sys_acl_resources].[name] LIKE N'%element%'
								then @sys_acl_operations_name
								else null
						  end										as [operation]
						,@is_allowed								as [is_allowed]
						,null										as [owner]
						,null										as [custom_sql]
						,null										as [comment]
						,GetDate()									as [insert_date]
						,[sys_acl_resources].[objectType]		 	as [objectType]
						,[sys_acl_resources].[objectId]				as [objectId]
				FROM @tblUsers as tblUsers
					JOIN [dbo].[sys_users] ON [sys_users].[id]=tblUsers.[id_user]
					CROSS JOIN #sys_acl_resources as sys_acl_resources
			  )														AS src
		ON	(	tgt.id_resource=src.id_resource AND tgt.id_user=src.id_user ) -- ако ресурса съвпада и потребителя съвпада
		
			WHEN MATCHED AND @only_insert = 0 THEN UPDATE SET [is_allowed] = src.[is_allowed]
															 ,[operation]  = src.[operation]
															 ,[objectId]   = src.[objectId]	 
															 ,[objectType] = src.[objectType]
			WHEN NOT MATCHED THEN INSERT (	[id_resource],[id_user],[operation],[is_allowed],[owner],[custom_sql],[objectType],[objectId] )
								  VALUES (	[id_resource],[id_user],[operation],[is_allowed],[owner],[custom_sql],[objectType],[objectId] )
			OUTPUT $action, src.[id_resource], src.[name_resource], src.[id_user],Inserted.[is_allowed],Inserted.[operation],src.[objectType],src.[objectId] 
				INTO @Output_usr;

																					END	-- IF @nRetValue = 1 AND exists (select 1 from @tblUsers) AND @SP_MODE <> N'TEST1'

	IF @SP_MODE=N'TEST1' AND exists (select 1 from @Output_rel)	select @ProcedureName+N'('+@Status+N')' as  [procedure], N'table @Output_rel' as tbl, * 
																from @Output_rel
																order by [id_role], [id_objectType], [id_object], [name_resource], [id_resource]
	ELSE IF @SP_MODE=N'TEST1'									select @ProcedureName+N'('+@Status+N')' as  [procedure], N'No rows in table @Output_rel' as tbl
	IF @SP_MODE=N'TEST1' AND exists (select 1 from @Output_usr)	select @ProcedureName+N'('+@Status+N')' as  [procedure], N'table @Output_usr' as tbl, * 
																from @Output_usr
																order by [id_user], [id_objectType], [id_object], [name_resource], [id_resource]
	ELSE IF @SP_MODE=N'TEST1'									select @ProcedureName+N'('+@Status+N')' as  [procedure], N'No rows in table @Output_usr' as tbl

	-- 6. Create result json
	SET @Output_json = 
		(SELECT
		--	 ( select * from @tblRoles 	FOR JSON PATH ) as [roles_affected]
		--	,( select * from @tblUsers 	FOR JSON PATH ) as [users_affected] 
			 ( select * from @Output_rel order by [id_role], [id_objectType], [id_object], [name_resource], [id_resource]
										FOR JSON PATH ) as [sys_acl_resource_role_rel]	
			,( select * from @Output_usr order by [id_user], [id_objectType], [id_object], [name_resource], [id_resource]
										FOR JSON PATH ) as [sys_acl_resource_user_rel]	
			,( select * from @Output_del FOR JSON PATH ) as [sys_acl_resource_duplicate]							
		FOR JSON PATH /*, WITHOUT_ARRAY_WRAPPER */ )

--======================================================================================================================================
END TRY
BEGIN CATCH																																																	
-- Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
		--close opened cursors
		--DROP TABLE IF EXISTS #cursors
		--SELECT * INTO #cursors FROM sys.dm_exec_cursors(0) WHERE session_id = @@SPID
		--IF EXISTS (select 1 from #cursors WHERE [name] = 'object_cursor' COLLATE database_default ) begin	CLOSE object_cursor; DEALLOCATE object_cursor	end
		--WHILE @@TRANCOUNT > 0 ROLLBACK TRANSACTION
		DECLARE @ERROR_PROCEDURE nvarchar(128) = ISNULL(ERROR_PROCEDURE(),@ProcedureName)+ISNULL(N'-'+@Status,N'')
		DECLARE @ERROR_MESSAGE nvarchar(2048) = ISNULL(ERROR_MESSAGE(),N'n/a')
		SET @ErrMessage = isnull(@ErrMessage+N',',N'')
							 +N'SQL Msg: ' + ISNULL(cast(ERROR_NUMBER() as nvarchar(20)),N'')	+N','
							 +N' Level ' + ISNULL(cast(ERROR_SEVERITY() as nvarchar(20)),N'')	+N','
							 +N' State ' + ISNULL(cast(ERROR_STATE() as nvarchar(20)),N'')		+N','
							 +N' Proc ' + @ERROR_PROCEDURE									
							 +N'('+ISNULL('Line: '+cast(ERROR_LINE() as nvarchar(20)),N'')		+N'),'
							 + @ERROR_MESSAGE		
		SET @nRetValue = 0 
END CATCH

	-- Запазване на данни за анализ на грешката
	IF @nRetValue = 0 AND @TestSaveParamID > 0 UPDATE [StepSoft].[dbo].[PARAMETERS] SET [ErrMessage] = @ErrMessage WHERE [ID] = @TestSaveParamID 

		DECLARE @Error TABLE ( [Error] nvarchar(2048) ); INSERT INTO @Error ( [Error] ) SELECT [Value] as Error FROM string_split(@ErrMessage,';')	WHERE ISNULL([Value],N'') <> N'' 
		DECLARE @Error_json nvarchar(MAX) = (SELECT CAST([Error] as nvarchar(MAX)) as [Error] FROM @Error FOR JSON AUTO , ROOT('Errors'))
	-- Определяне на перформънс
	IF @TestSaveParamID > 0 UPDATE [StepSoft].[dbo].[PARAMETERS] SET [EndTime] = GETDATE() WHERE [ID] = @TestSaveParamID 
	-- Връщане на резултат
	SET @Return = @nRetValue
	SELECT @Return as [Result], @Error_json as [Error_json], @Output_json as [Output_json]
	   	
RETURN @Return
END

 