SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS usp_ssERP8_EraseObject
GO 
CREATE PROCEDURE [dbo].[usp_ssERP8_EraseObject]
-- номенклатура -- select * from sys_doctype
--	EXEC [dbo].[usp_ssERP8_EraseObject] @id_object=1, @id_objectType=2  ,@SP_MODE='TEST1'
-- =========================================================================================================================
-- Author		Щерю Щерев																									
-- Create date	18/07/2024
-- Version		v2 06/02/2025 - rejection of procedure [usp_ssERP8_EraseObject]
--				v3 08/04/2025 - bugs fixed
--				v4 01/05/2025 - erase sub-object of object	
--
-- Serever		MS SQL 2019																										
-- Description	Erase object with objectable_id = @id_object
-- Returns		json 
-- Parameters
(		 @id_objectType			int				-- 1. ID of object type (1=nomenclature 2=document 3=report)
		,@id_object				int				-- 2. ID of object ( id from sys_docType OR sys_nmcl OR sys_reports)
		----------------------------																									
		,@id_form				int				NULL=NULL		-- 3. ID of interface form
		,@id_section			int				NULL=NULL		-- 4. ID of section
	    ,@id_element			int				NULL=NULL		-- 5. ID of element
	 --	,@id_table				int				NULL=NULL		-- 6. ID of table
	  --,sys_acl_resources_id	int				NULL=NULL		-- 7. ID of resource							
	  --,is_translatable		bit DEFAULT 0	NULL=NULL		-- 8. Only objects contained translatebility elements					
	  --,is_exists				bit	DEFAULT 1	NULL=NULL		-- 9. Only real existings column in tables
		----------------------------																									
	    ,@lang					nvarchar(255)	NULL=N'bg'					-- 10. Current language
		,@Force_erasing			int				NULL=1			OUTPUT		-- 11. Deleting an object while ignoring barriers
	    ,@Output_json		 	nvarchar(MAX)	NULL=NULL		OUTPUT		-- 12. JSON with rows from cach table on report 			 
	    ,@ErrMessage			nvarchar(MAX)	NULL= N''		OUTPUT		-- 13. Text of error
	    ,@Return				int				NULL=1			OUTPUT		-- 14. Exit code parameter
	    ,@SP_MODE			 	nvarchar(10)  	NULL=N'EXECUTE'				-- 15. Execution mode / Дали е в работен или тестов режим
)
-- =========================================================================================================================
AS BEGIN
/*
manualUpdateFlag: No
*/
BEGIN TRY
/* DECLARE
		@id_object		int		=43								--  1. ID of object ( id from sys_docType OR sys_nmcl )
	   ,@id_objectType	int		=1								--  2. TYPE of source object type (1=nomenclature 2=document 3=report)	
	   -----------------------------								    
		,@id_form		int								-- 3. ID of interface form
		,@id_section	int								-- 4. ID of section
	    ,@id_element	int								-- 5. ID of element
		,@id_table		int								-- 6. ID of table

	   ,@lang			nvarchar(255)	=N'bg'			--  10. Current language
	   ,@Output_json	nvarchar(MAX)					--  11. JSON with rows from cach table on report 
	   ,@ErrMessage		nvarchar(MAX)	= N''			--  12. Text of error
	   ,@Return			int				=1				--  13. Exit code parameter
	   ,@SP_MODE		nvarchar(10)  	=N'TEST1'		--  14. Execution mode / Дали е в работен или тестов режим
--*/

	DECLARE @ProcedureName sysname = ISNULL(OBJECT_NAME(@@PROCID),N'[usp_ssERP8_EraseObject]')
	DECLARE @nRetValue int = 1, @Status varchar(100) = '0', @ExitCode int
	DECLARE @ParamDefinition nvarchar(MAX), @CommandString nvarchar(MAX), @SQLString nvarchar(MAX), @ROWCOUNT int
	SET @ErrMessage = cast(N'' as nvarchar(MAX))

	-- 1. Parameters sniffing
		SET @Status += ',1'
		-- @SP_MODE
			--SET @SP_MODE = case when @SP_MODE is null THEN 'EXECUTE' when @SP_MODE = 'TEST' then 'TEST' when @SP_MODE = 'TEST1' then 'TEST1' else 'EXECUTE' end
			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
		-- lang
			DECLARE @lang_id int = ( SELECT [id] FROM [dbo].[sys_languages] WHERE [language]=@lang AND [status]=1 )
			IF @lang_id is null	begin SET @nRetValue = 0; SET @ErrMessage += N';(1) Invalid language parameter'  end

		-- @id_object and @id_objectType  
		/*	SELECT * FROM [dbo].[sys_objectReference] WHERE [sys_systemObjects_id]=1 AND [objectable_id]=94
			declare	 @id_object		int=2	--  1. ID of source object
					,@id_objectType	int=1	--  2. TYPE of source object type (1=nomenclature 2=document 3=report)	
			declare @SP_MODE nvarchar(255)='TEST1', @ProcedureName nvarchar(128)=N'test', @nRetValue int=1, @ErrMessage nvarchar(MAX)=N'', @Status nvarchar(1024)='test', @lang nvarchar(255)=N'bg'
		--*/
		DECLARE @sys_systemObjects_name nvarchar(1024), @objectable_lang_name nvarchar(1024), @objectable_name nvarchar(128), @cnt_table int
			IF		not exists ( SELECT * FROM [dbo].[sys_objectReference] WHERE [sys_systemObjects_id]=@id_objectType AND [objectable_id]=@id_object		) 
					BEGIN SET @nRetValue = 0; SET @ErrMessage += cast(	  N';<<1>> Invalid parameter @id_object='
																		+ ISNULL(cast(@id_object as nvarchar(20)),N'null')
																		+ N' with @id_objectType='
																		+ ISNULL(cast(@id_objectType as nvarchar(20)),N'null')
																		  as nvarchar(MAX))
					END
			ELSE 	BEGIN
				SET @sys_systemObjects_name = ISNULL((SELECT [description] FROM [dbo].[sys_systemObjects] WHERE [id]=@id_objectType),N'null')
				IF @id_objectType=1	begin -- select * from sys_nmcl
						SELECT TOP(1)  -- select distinct filename from sys_translations
								 @objectable_name = [sys_nmcl].[tablename]
								,@objectable_lang_name = [sys_translations].[value]
						FROM [dbo].[sys_nmcl]
							LEFT JOIN [dbo].[sys_translations] ON [sys_nmcl].[label]=[sys_translations].[label] AND  [device]=N'web' AND [locale]=@lang AND [filename]=N'nomenclature'
						WHERE [sys_nmcl].[id]=@id_object	
						SET @cnt_table = ( select COUNT([tablename]) from [dbo].[sys_nmcl] where [tablename]=@objectable_name )
									end
				ELSE IF @id_objectType=2	begin -- select * from sys_doctype
							SELECT TOP(1)  -- select distinct filename from sys_translations
									 @objectable_name = ''
									,@objectable_lang_name = [sys_translations].[value]
							FROM [dbo].[sys_doctype]
								LEFT JOIN [dbo].[sys_translations] ON [sys_doctype].[label]=[sys_translations].[label] AND [device]=N'web' AND [locale]=@lang AND [filename]=N'document'
							WHERE [sys_doctype].[id]=@id_object	
							SET @cnt_table = 1
											end	
						ELSE IF @id_objectType=3 begin -- select * from sys_reports
								SELECT TOP(1)  -- select distinct filename from sys_translations
										 @objectable_name = ''
										,@objectable_lang_name = [sys_translations].[value]
								FROM [dbo].[sys_reports]
									LEFT JOIN [dbo].[sys_translations] ON [sys_reports].[label]=[sys_translations].[label] AND [device]=N'web' AND [locale]=@lang AND [filename] LIKE N'report%'
								WHERE [sys_reports].[id]=@id_object	
								SET @cnt_table = 1
												end	
							  ELSE SET @objectable_lang_name = N'error'
				IF @objectable_lang_name = N'error'
					begin 
						SET @nRetValue = 0; SET @ErrMessage += cast(	  N';<<1>> Invalid parameter @id_objectType='+ISNULL(cast(@id_objectType as nvarchar(20)),N'null')
																	as nvarchar(MAX))
					end
				--IF @cnt_table <> 1
				--	begin 
				--		SET @nRetValue = 0; SET @ErrMessage += cast(	  N';<<1>> Invalid settings of nomenclature '+ISNULL(cast(@id_object as nvarchar(20)),N'null')+N' in sys_nmcl'						
				--													as nvarchar(MAX))
				--	end

					END
		-- @id_form
				IF @id_form is not null AND not exists (select 1 from [dbo].[sys_form] where [id]=@id_form)
				   begin SET @nRetValue = 0; SET @ErrMessage += cast( N';<<1>> Invalid parameter @id_form: '+ISNULL(cast(@id_form as nvarchar(20)),N'null')
																	  as nvarchar(MAX)) end
		-- @id_section
				IF @id_section is not null AND not exists (select 1 from [dbo].[sys_sections] where [id]=@id_section)
				   begin SET @nRetValue = 0; SET @ErrMessage += cast( N';<<1>> Invalid parameter @id_section: '+ISNULL(cast(@id_section as nvarchar(20)),N'null')
																	  as nvarchar(MAX))	end
		-- @id_element
		DECLARE @element_name nvarchar(128)
				IF @id_element is not null AND not exists (select 1 from [dbo].[sys_element] where [id]=@id_element)
				   begin SET @nRetValue = 0; SET @ErrMessage += cast( N';<<1>> Invalid parameter @id_element: '+ISNULL(cast(@id_element as nvarchar(20)),N'null')
																	  as nvarchar(MAX))	end
				ELSE SET @element_name = (select [name] from [dbo].[sys_element] where [id]=@id_element)
		---- @sys_acl_resources_id
		-- @id_table
		----IF @id_table is not null AND not exists (select 1 from [dbo].[sys_table] where [id]=@id_table)
		----   begin SET @nRetValue = 0; SET @ErrMessage += cast( N';<<1>> Invalid parameter @id_table: '+ISNULL(cast(@id_table as nvarchar(20)),N'null')
		----													  as nvarchar(MAX))	end

	-- 2. Logging the data for analysis / Запазване на данни за анализ
		SET @Status += ',2'
			DECLARE @TestSaveParamID int = 0
		IF OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam','P') 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 object ( id from sys_docType OR sys_nmcl OR sys_reports)
												 +	ISNULL(cast(@id_form as varchar(10)),'null')+','					-- 3. ID of interface form
												 +	ISNULL(cast(@id_section as varchar(10)),'null')+','					-- 4. ID of section
												 +	ISNULL(cast(@id_element as varchar(10)),'null')+','					-- 5. ID of element
											--	 +	ISNULL(cast(@id_table as varchar(10)),'null')+','					-- 6. ID of table
											--	 +	ISNULL(cast(@sys_acl_resources_id as varchar(20)),'null')+','		-- 7. ID of resource
											--	 +	ISNULL(cast(@is_translatable as varchar(10)),'null')+','			-- 8. Only objects contained translatebility elements
											--	 +	ISNULL(cast(@is_exists as varchar(10)),'null')+','					-- 9. Only real existings column in tables
												 +	ISNULL(cast(@lang as varchar(10)),'null')+','						-- 10. Current language
												 +	ISNULL(@SP_MODE,'null')												-- 14.Execution mode 
				DECLARE @DBName nvarchar(128) = DB_NAME()
				DECLARE @xml_comple nvarchar(MAX) = 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 object ( id from sys_docType OR sys_nmcl OR sys_reports)
															,@id_form				as [@id_form]						-- 3. ID of interface form
															,@id_section			as [@id_section]					-- 4. ID of section
															,@id_element			as [@id_element]					-- 5. ID of element	
														--	,@id_table				as [@id_table]						-- 6. ID of table
														--	,@sys_acl_resources_id	as [@sys_acl_resources_id]			-- 7. ID of resource
														--	,@is_translatable		as [@is_translatable]				-- 8. Only objects contained translatebility elements
														--	,@is_exists				as [@is_exists]						-- 9. Only real existings column in tables
															,@lang					as [@lang]							-- 10. Current language
															,@Output_json		 	as [@Output_json]					-- 11. JSON with rows from cach table on report 
															,@ErrMessage			as [@ErrMessage]					-- 12. Text of error
															,@Return				as [@Return]						-- 13. Exit code parameter
															,@SP_MODE			 	as [@SP_MODE]						-- 14. Execution mode / Дали е в работен или тестов режим
													FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )
				EXEC [stepsoft].[dbo].[usp_ssTestSaveParam] @PrmMessage = @PrmString, @CallingModule = @ProcedureName, @XMLString = @xml_comple
															,@Separator = ',', @DataBase = @DBName, @RecordID = @TestSaveParamID OUTPUT
															,@PrmJson=@PrmJson
			END --IF LEFT(@SP_MODE,4) = N'TEST' AND OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam') is not null		
	print @ProcedureName+'->SP_MODE = ' + ISNULL(@SP_MODE,'NULL')+' '+@ProcedureName+'->TestSaveParamID = ' + ISNULL(cast(@TestSaveParamID as varchar(10)),'NULL') + ' '+cast(OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam') as varchar(255))


-- 3. Analyzing of the parameters. Determination of @DELETE_LEVEL
			SET @Status += ',3'
		/*	НИВА:
			1	objectType
			2			└─ object
			3				   └─ form	
			4						└─ section 
			5								└─ element
			6									  └─ element properties AND relations
		*/
		DECLARE @DELETE_LEVEL int = 
			case 
				when @id_element	is not null 
					then 5  --> Трия определен ЕЛЕМЕНТ и ВСИЧКИ НГОВИ: поделементи(свойства)
							--	(от таблицата трия колоната която отговаря на елемента)	
				when @id_section	is not null	
					then 4	--> Трия определена секция и ВСИЧКИ НЕЙНИ: таблици,елементи,поделементи(свойства)
						   	--	(от таблиците трия тези които отговарят за секцията)
				when @id_form		is not null 
					then 3	--> Трия определена форма(=определен обект) и ВСИЧКИ НЕЙНИ: секции,таблици,елементи,поделементи(свойства)
						   	--	(от таблиците трия тези които отговарят за формата)
				when @id_object		is not null 
					then 2	--> Трия определен обект и ВСИЧКИ НЕГОВИ: форми,секции,таблици,елементи,поделементи(свойства)
							--	(от таблиците трия тези които отговарят за обекта)
				when @id_objectType	is not null 
					then 1	--> Трия всички обекти от този тип И ВСИЧКИ ТЕХНИ: форми,секции,таблици,елементи,поделементи(свойства)									
							--	(от таблиците трия тези които отговарят за типа обекти)
				else -1
			end
		IF @DELETE_LEVEL=-1 begin SET @nRetValue = 0; SET @ErrMessage += cast( N';<<1>> Invalid parameters SET' as nvarchar(MAX)) end

		IF @SP_MODE=N'TEST1' select  @ProcedureName+N'('+@Status+N')' as [procedure], N'Parameters' as tbl
								   ,@id_object as '@id_object', @id_objectType as '@id_objectType', @sys_systemObjects_name as '@sys_systemObjects_name'
								   ,@objectable_lang_name as '@objectable_lang_name'
								   ,@id_form as '@id_form', @id_section as '@id_section',@id_element as '@id_element',@element_name as '@element_name'
								   ,@lang as '@lang', @Force_erasing as '@Force_erasing'
								   ,@DELETE_LEVEL as '@DELETE_LEVEL', @nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage', @SP_MODE as '@SP_MODE'

-- =============================================================================================================================================================
--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 4. Object data research / ИЗВЛИЧАНЕ НА ДАННИ ЗА НАСТРОЙКИТЕ
			SET @Status += ',4'
	IF @nRetValue = 1	BEGIN
			SET @Status += 'a'
	-- ИЗВЛИЧАНЕ НА ВСИЧКИ НАСТРОЙКИ КОИТО НЕ КАСАЯТ ТОЗИ ОБЕКТ И/ИЛИ ДЕФИНИРАНИТЕ МУ ПОДЧАСТИ СПОРЕД ПОДАДЕНИТЕ ПАРАМЕТРИ
	-- DECLARE @id_objectType int = 2, @id_object int = 9, @id_form int, @id_section int, @id_element int, @lang nvarchar(255) = N'bg'
		DROP TABLE IF EXISTS #AllSettings
		SELECT fn.* INTO #AllSettings
		FROM dbo.ufn_ssERP8_CollectObjects	(	 null	 -- 1. @sys_systemObjects_id
												,null	 -- 2. @sys_objectable_id
												,null	 -- 3. @sys_form_id
												,null	 -- 4. @sys_section_id
												,null	 -- 5. @sys_element_id
												,null	 -- 6. @sys_table_id	
												,@lang	 -- 7. @lang
											) as fn
		WHERE  NOT (		 (object_type = @id_objectType			OR @id_objectType is null)	-- 1. @sys_systemObjects_id
						AND  (objectable_id = @id_object			OR @id_object is null)		-- 2. @sys_objectable_id
						AND  (object_interface_form_id = @id_form	OR @id_form is null)		-- 3. @sys_form_id
						AND  (section_id = @id_section				OR @id_section is null)		-- 4. @sys_section_id
						AND  (element_id = @id_element				OR @id_element is null)		-- 5. @sys_element_id
				   )
		-- select * from #AllSettings where objectable_id=9 AND object_type=2 AND section_id=17 AND element_id=5305 order by objectable_id
	-- ИЗВЛИЧАНЕ НА ВСИЧКИ НАСТРОЙКИ КОИТО СА ЗА ТОЗИ ОБЕКТ И/ИЛИ ДЕФИНИРАНИТЕ МУ ПОДЧАСТИ СПОРЕД ПОДАДЕНИТЕ ПАРАМЕТРИ 
			SET @Status += 'b'
		DROP TABLE IF EXISTS #ObjectSettings
		SELECT fn.* INTO #ObjectSettings
		FROM dbo.ufn_ssERP8_CollectObjects	(	 @id_objectType	 -- 1. @sys_systemObjects_id
												,@id_object		 -- 2. @sys_objectable_id
												,@id_form		 -- 3. @sys_form_id
												,@id_section	 -- 4. @sys_section_id
												,@id_element	 -- 5. @sys_element_id
												,null			 -- 6. @sys_table_id	
												,@lang			-- 7. @lang
											) as fn
		-- select * from #ObjectSettings where section_id=17 AND element_id=5305 order by objectable_id
						END -- IF @nRetValue = 1

--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 5. CHECK FOR SHARED RESOURCES AND ELEMENTS BETWEEN OBJECTS / ПРОВЕРКА ЗА СПОДЕЛЕНИ МЕЖДУ ОБЕКТИТЕ РЕСУРСИ И ЕЛЕМЕНТИ
			SET @Status += ',5'
	IF @nRetValue = 1	BEGIN
		DECLARE @tables_save TABLE ( [name] nvarchar(128) )
		DECLARE @forms_save TABLE ( [id] int )
		DECLARE @sections_save TABLE ( [id] int )
		DECLARE @elements_save TABLE ( [id] int )
		DECLARE @resources_save TABLE ( [id] int )
		DECLARE @Erase_columns TABLE ( [column_table] nvarchar(128), [column_name] nvarchar(128) )

	   DROP TABLE IF EXISTS #MultiUsed
		CREATE TABLE #MultiUsed ( ItemType nvarchar(255) COLLATE database_default, ItemId nvarchar(128) COLLATE database_default )

			-- АНАЛИЗ ТАБЛИЦИТЕ
			INSERT INTO @tables_save ( [name] )
				SELECT objs.[objectable_table_name] COLLATE database_default
				FROM ( select DISTINCT [objectable_table_name] from #ObjectSettings )  as objs
					JOIN ( select DISTINCT [objectable_table_name] from #AllSettings ) as alls ON alls.[objectable_table_name] COLLATE database_default = objs.objectable_table_name  COLLATE database_default 
			  UNION
				SELECT objs.[state_table_name] COLLATE database_default
				FROM ( select DISTINCT [state_table_name] from #ObjectSettings )  as objs
					JOIN ( select DISTINCT [state_table_name] from #AllSettings ) as alls ON alls.[state_table_name] COLLATE database_default = objs.[state_table_name]  COLLATE database_default 
				-- required tables
				IF not exists (select 1 from @tables_save where [name]=N'sys_srow' ) INSERT INTO @tables_save ([name]) VALUES (N'sys_srow')
				IF not exists (select 1 from @tables_save where [name]=N'sys_stitle')INSERT INTO @tables_save ([name]) VALUES (N'sys_stitle')

			-- АНАЛИЗ НА ФОРМИТЕ
			INSERT INTO @forms_save ( [id] )
				SELECT objs.[object_interface_form_id]
				FROM ( select DISTINCT [object_interface_form_id] from #ObjectSettings )  as objs
					JOIN ( select DISTINCT [object_interface_form_id] from #AllSettings ) as alls ON alls.[object_interface_form_id] = objs.[object_interface_form_id]
			-- АНАЛИЗ НА СЕКЦИИТЕ
			INSERT INTO @sections_save ( [id] )
				SELECT objs.[section_id]
				FROM ( select DISTINCT [section_id] from #ObjectSettings )  as objs
					JOIN ( select DISTINCT [section_id] from #AllSettings ) as alls ON alls.[section_id] = objs.[section_id]
			-- АНАЛИЗ НА ЕЛЕМЕНТИТЕ
			INSERT INTO @elements_save ( [id] )
				SELECT objs.[element_id]
				FROM ( select DISTINCT [element_id] from #ObjectSettings )  as objs
					JOIN ( select DISTINCT [element_id] from #AllSettings ) as alls ON alls.[element_id] = objs.[element_id]
			-- АНАЛИЗ НА РЕСУРСИТЕ
			INSERT INTO @resources_save ( [id] )
				SELECT objs.[rm_sys_acl_resources_id]
				FROM ( select DISTINCT [rm_sys_acl_resources_id] from #ObjectSettings )  as objs
					JOIN ( select DISTINCT [rm_sys_acl_resources_id] from #AllSettings ) as alls ON alls.[rm_sys_acl_resources_id] = objs.[rm_sys_acl_resources_id]

			IF @DELETE_LEVEL<=5	begin
			-- АНАЛИЗ НА ФИЗИЧЕСКИТЕ КОЛОНИ В ТАБЛИЦИТЕ
			   -- column -- select * from sys.columns
				INSERT INTO @Erase_columns ( [column_table], [column_name] )
					SELECT DISTINCT  ObjectSettings.[objectable_table_name] COLLATE database_default as [column_table]
									,[columns].[name] COLLATE database_default as [column_name]
					FROM #ObjectSettings as ObjectSettings
						LEFT JOIN sys.columns as [columns] ON [columns].[name] COLLATE database_default=ObjectSettings.[element_name] COLLATE database_default
														  AND OBJECT_NAME([columns].[object_id]) COLLATE database_default=ObjectSettings.[objectable_table_name] COLLATE database_default
						LEFT JOIN #AllSettings as AllSettings ON [AllSettings].[element_name] COLLATE database_default= [columns].[name] COLLATE database_default
														  AND OBJECT_NAME([columns].[object_id]) COLLATE database_default=AllSettings.[objectable_table_name] COLLATE database_default
					WHERE [columns].[name] is not null AND [AllSettings].[element_name] is null
					  AND OBJECT_NAME([columns].[object_id]) COLLATE database_default IN (select * from @tables_save)
				UNION ALL
					SELECT DISTINCT  ObjectSettings.[state_table_name] COLLATE database_default as [column_table]
									,[columns].[name] COLLATE database_default as [column_name]
					FROM #ObjectSettings as ObjectSettings
						LEFT JOIN sys.columns as [columns] ON [columns].[name] COLLATE database_default=ObjectSettings.[element_name] COLLATE database_default
														  AND OBJECT_NAME([columns].[object_id]) COLLATE database_default=ObjectSettings.[objectable_table_name] COLLATE database_default
						LEFT JOIN #AllSettings as AllSettings ON [AllSettings].[element_name] COLLATE database_default= [columns].[name] COLLATE database_default
														  AND OBJECT_NAME([columns].[object_id]) COLLATE database_default=AllSettings.[objectable_table_name] COLLATE database_default
					WHERE [columns].[name] is not null AND [AllSettings].[element_name] is null
					  AND OBJECT_NAME([columns].[object_id]) COLLATE database_default IN (select * from @tables_save)

								end -- IF @DELETE_LEVEL<=5	

			INSERT INTO #MultiUsed ( ItemType, ItemId ) SELECT N'Tables'	as ItemType, [name] as ItemID	FROM @tables_save WHERE [name] NOT IN (N'sys_srow',N'sys_stitle')
			INSERT INTO #MultiUsed ( ItemType, ItemId ) SELECT N'Forms'		as ItemType, [id]	as ItemID	FROM @forms_save
			INSERT INTO #MultiUsed ( ItemType, ItemId ) SELECT N'Sections'	as ItemType, [id]	as ItemID	FROM @sections_save
			INSERT INTO #MultiUsed ( ItemType, ItemId ) SELECT N'Elements'	as ItemType, [id]	as ItemID	FROM @elements_save 
			INSERT INTO #MultiUsed ( ItemType, ItemId ) SELECT N'Resources'	as ItemType, [id]	as ItemID	FROM @resources_save 

						END -- IF @nRetValue = 1 -- 5. CHECK FOR SHARED RESOURCES AND ELEMENTS BETWEEN OBJECTS	
--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 5a. stop erasing when force erase is off / Спиране на изтриването когато проверката покаже проблеми и @Force_erasing = 0
	IF @nRetValue = 1 AND @Force_erasing = 0	BEGIN
			SET @Status += ',a'	
			SET @CommandString =cast( N'' as nvarchar(MAX))
			SET @ParamDefinition=N'@ROWCOUNT int OUTPUT'
		-- a. В @Erase_columns ( [column_table], [column_name] ). В таблиците column_table има NOT NULL стойности в полето column_name.
				IF exists ( select 1 from @Erase_columns ) AND @DELETE_LEVEL<=5	begin
				SET @ROWCOUNT=0
					SELECT @CommandString +=cast( N'
					SELECT @ROWCOUNT+=COUNT(*) FROM '+[column_table]+N' WHERE '+[column_name]+N' IS NOT NULL
'													as nvarchar(MAX))
					FROM @Erase_columns
																				end -- a.
		---------------
			IF @SP_MODE=N'TEST1' select  @ProcedureName+N'('+@Status+N')' as [procedure], N'STOP ERASING script' as tbl
										,N'DECLARE @ROWCOUNT int=0
										'+@CommandString+N'
											SELECT @ROWCOUNT as [ROWCOUNT]' as '@CommandString'
										,@ParamDefinition as '@ParamDefinition'
			SET @ExitCode = 0
				EXEC @ExitCode=sp_executeSQL @CommandString, @ParamDefinition, @ROWCOUNT=@ROWCOUNT OUTPUT
			IF @ExitCode > 0 begin SET @ErrMessage += N'<<1>> Error occurred while stop_erasing strin executed'; SET @nRetValue=0 end
			IF @ROWCOUNT > 0 SET @Force_erasing = -1
												END
												
--	------------------------------------------------------------------------------------------------------------------------------------------------------------
		IF @SP_MODE=N'TEST1' AND @nRetValue=1	begin
			IF exists (select 1 from @tables_save)	  select @ProcedureName+N'('+@Status+N')' as [procedure], N'Tables that are used in more than one' as tbl
															,(select * from @tables_save FOR JSON PATH ) as [multi_tables]
			ELSE									  select @ProcedureName+N'('+@Status+N')' as [procedure], N'There are no tables that are used in more than one' as tbl
			IF exists (select 1 from @forms_save)	  select @ProcedureName+N'('+@Status+N')' as [procedure], N'Forms that are used in more than one' as tbl
															,(select * from @forms_save FOR JSON PATH ) as [multi_forms]
			ELSE									  select @ProcedureName+N'('+@Status+N')' as [procedure], N'There are no forms that are used in more than one' as tbl
			IF exists (select 1 from @sections_save)  select @ProcedureName+N'('+@Status+N')' as [procedure], N'Sections that are used in more than one' as tbl
															,(select * from @sections_save FOR JSON PATH ) as [multi_sections]
			ELSE									  select @ProcedureName+N'('+@Status+N')' as [procedure], N'There are no sections that are used in more than one' as tbl
		-----
			IF exists (select 1 from @elements_save ) select @ProcedureName+N'('+@Status+N')' as [procedure], N'Elements that are used in more than one' as tbl
															,(select * from @elements_save FOR JSON PATH ) as [multi_elements]
			ELSE									  select @ProcedureName+N'('+@Status+N')' as [procedure], N'There are no elements that are used in more than one' as tbl
			IF exists (select 1 from @resources_save) select @ProcedureName+N'('+@Status+N')' as [procedure], N'Resourcess that are used in more than one' as tbl
														    ,(select * from @resources_save FOR JSON PATH ) as [multi_resources]
			ELSE									  select @ProcedureName+N'('+@Status+N')' as [procedure], N'There are no resources that are used in more than one' as tbl
			IF exists (select 1 from @Erase_columns)  select @ProcedureName+N'('+@Status+N')' as [procedure], N'Columns for erase' as tbl
														    ,(select * from @Erase_columns FOR JSON PATH ) as [Erase_columns]
			ELSE									  select @ProcedureName+N'('+@Status+N')' as [procedure], N'There are no columns for erase' as tbl

			select   @ProcedureName+N'('+@Status+N')' as [procedure], N'Object data research' as tbl
					,@DELETE_LEVEL as '@DELETE_LEVEL'
					,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage', @Force_erasing as '@Force_erasing'

			IF exists (select 1 from #ObjectSettings)  select @ProcedureName+N'('+@Status+N')' as [procedure], N'#ObjectSettings' as tbl, * from #ObjectSettings
			ELSE									  select @ProcedureName+N'('+@Status+N')' as [procedure], N'There are no valid settings in #ObjectSettings' as tbl

												end
-- =============================================================================================================================================================
--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 6. Load object data for erase / ПОПЪЛВАНЕ НА ТАБЛИЦАТА #EraseItems с данни за изтриване 
			SET @Status += ',6'	
	  -- #EraseItems -- съдържа стойности на обекти, секции, елементи, таблици ... които трябва да се изтрият
			DROP TABLE IF EXISTS #EraseItems
			CREATE TABLE #EraseItems ( ItemType nvarchar(255), ItemId nvarchar(128) )
	  -- #EraseFiles
			DROP TABLE IF EXISTS #EraseFiles
			CREATE TABLE #EraseFiles ( [FileID] int, [FileName] nvarchar(128), [origin] nvarchar(128) )

	IF @nRetValue = 1	BEGIN
		-- 6.1. НЕЗАВИСИМО КАКЪВ Е ТИПА НА ОБЕКТА
			IF @DELETE_LEVEL<=3 BEGIN -- ФОРМИ И РЕСУРСИ																											
				SET @Status += 'a'
				INSERT INTO #EraseItems ( ItemType, ItemId )																							
					SELECT DISTINCT N'Forms' as [ItemType], [object_interface_form_id] as [ItemID]														
					FROM #ObjectSettings as ObjectSettings																								
						LEFT JOIN @forms_save as forms_save ON ObjectSettings.[object_interface_form_id] = forms_save.[id]											
					WHERE forms_save.[id] is null AND ObjectSettings.[section_id] is not null															
				SET @Status += 'b'
				INSERT INTO #EraseItems ( ItemType, ItemId )
					SELECT DISTINCT N'sys_acl_resources' as [ItemType], [sys_acl_resources].[id] as [ItemID]
					FROM [dbo].[sys_acl_resources] 
						LEFT JOIN @resources_save as resources_save ON [sys_acl_resources].[id]=resources_save.[id]
					WHERE resources_save.[id] is null AND [sys_acl_resources].[id] is not null
					  AND [sys_acl_resources].[objectType]=@id_objectType
					  AND [sys_acl_resources].[objectID]=@id_object
							    END	-- IF @DELETE_LEVEL<=3																								
			IF @DELETE_LEVEL<=4 BEGIN -- СЕКЦИИ	и ТАБЛИЦИ																										
				SET @Status += 'c'
				INSERT INTO #EraseItems ( ItemType, ItemId )
					SELECT DISTINCT N'Sections' as [ItemType], ObjectSettings.[section_id] as [ItemID]
					FROM #ObjectSettings as ObjectSettings
						LEFT JOIN @sections_save as sections_save ON ObjectSettings.[section_id] = sections_save.[id]
					WHERE sections_save.[id] is null AND ObjectSettings.[section_id] is not null
				SET @Status += 'd'
				-- sys/nmcl/cachereport tables conectived with element
				INSERT INTO #EraseItems ( ItemType, ItemId )
					SELECT DISTINCT N'Tables' as [ItemType], ObjectSettings.[objectable_table_name] COLLATE database_default as [ItemID]
					FROM #ObjectSettings as ObjectSettings
						LEFT JOIN @tables_save as tables_save ON ObjectSettings.[objectable_table_name] COLLATE database_default = tables_save.[name] COLLATE database_default
					WHERE tables_save.[name] is null /* не са определени за запазване */ AND ObjectSettings.[objectable_table_name] is not null
				-- state tables  conectived with element
				UNION
					SELECT DISTINCT N'Tables' as [ItemType], ObjectSettings.[state_table_name] COLLATE database_default as [ItemID]
					FROM #ObjectSettings as ObjectSettings
						LEFT JOIN @tables_save as tables_save ON ObjectSettings.[objectable_table_name] COLLATE database_default = tables_save.[name] COLLATE database_default
					WHERE tables_save.[name] is null /* не са определени за запазване */ AND ObjectSettings.[state_table_name] is not null
				
				-- EMPTY tables - not conectived with element
					-- ЗА ПРЕВОДИ
				INSERT INTO #EraseItems ( ItemType, ItemId )
					SELECT N'Tables' as [ItemType], ei.[ItemId]+N'_translations' COLLATE database_default as [ItemID]
					FROM #EraseItems as ei
						LEFT JOIN @tables_save as tables_save ON ei.[ItemId]+N'_translations' COLLATE database_default = tables_save.[name] COLLATE database_default
					WHERE ei.[ItemType] = N'Tables' AND ei.[ItemId] is not null
					  -- не съществува преводен елемент и затова не са включени в списъка дотук	
					  AND not exists (select 1 from #EraseItems where [ItemType] = N'Tables' AND [ItemId]=ei.[ItemId]+N'_translations')
					  AND tables_save.name is null /* не са определени за запазване */
					-- ИСТОРИЧЕСКИ
				INSERT INTO #EraseItems ( ItemType, ItemId )
					SELECT N'Tables' as [ItemType], ei.[ItemId]+N'_history' COLLATE database_default as [ItemID]
					FROM #EraseItems as ei
					WHERE [ItemType] = N'Tables' AND ei.[ItemId] is not null 
					  -- да не са включени в списъка дотук по някаква причина (по принцип не би трябвало)
					  AND not exists (select 1 from #EraseItems where [ItemType] = N'Tables' AND [ItemId]=ei.[ItemId]+N'_history')
				-- сега вече са включени и таблици които може да ги няма физически
							    END	-- IF @DELETE_LEVEL<=4 
			IF @DELETE_LEVEL<=5 BEGIN -- ЕЛЕМЕНТИ И ПОД ТЯХ
				SET @Status += 'e'
				INSERT INTO #EraseItems ( ItemType, ItemId )
					SELECT DISTINCT N'Elements' as [ItemType], ObjectSettings.[element_id] as [ItemID]
					FROM #ObjectSettings as ObjectSettings
						LEFT JOIN @elements_save as elements_save ON ObjectSettings.[element_id] = elements_save.[id]
					WHERE elements_save.[id] is null AND ObjectSettings.[element_id] is not null
				SET @Status += 'f'
			   -- id_sys_referenceMap -- select * from dbo.ufn_ssERP8_CollectObjects	( 1, 4, null, null, null, null, 'bg' )
				INSERT INTO #EraseItems ( ItemType, ItemId )
					SELECT DISTINCT N'sys_referenceMap' as [ItemType], [rm_id] as [ItemID]
					FROM #ObjectSettings as ObjectSettings
						LEFT JOIN @elements_save as elements_save ON ObjectSettings.[element_id] = elements_save.[id]
					WHERE elements_save.[id] is null AND ObjectSettings.[rm_id] is not null
				SET @Status += 'g'
			   -- id_sys_acl_resources -- select * from sys_acl_resources
			   -- DECLARE @resources_save TABLE ( [id] int )
				INSERT INTO #EraseItems ( ItemType, ItemId )
					SELECT DISTINCT N'sys_acl_resources' as [ItemType], [sys_acl_resources].[id] as [ItemID]
					FROM #ObjectSettings as ObjectSettings
						     JOIN [dbo].[sys_acl_resources] ON [sys_acl_resources].[id]=ObjectSettings.[rm_sys_acl_resources_id]
						LEFT JOIN @resources_save as resources_save ON ObjectSettings.[rm_sys_acl_resources_id] = resources_save.[id]
						LEFT JOIN #EraseItems as ei ON ei.ItemType=N'sys_acl_resources' AND ei.ItemId=[sys_acl_resources].[id]
					WHERE resources_save.[id] is null AND [sys_acl_resources].[id] is not null
					  AND ei.[ItemId] is null -- ако не е този ресурс ВЕЧЕ е подаден за триене
							    
								END	-- IF @DELETE_LEVEL<=5
		-- 6.2. КОГАТО ОБЕКТА Е НОМЕНКЛАТУРА ИЛИ ДОКУМЕНТ
			-- select * from sys_nmcl; select * from ufn_ssERP8_CollectObjects(2,	3,	null, null, 	null, null, 'bg')
			IF @id_objectType IN (1,2)	BEGIN
				IF @DELETE_LEVEL<=2	begin -- ОБЕКТИ
				SET @Status += 'h'
				-- sys_cache_tables_registry -- select TOP(1) * from sys_cache_tables_registry
				INSERT INTO #EraseItems ( ItemType, ItemId )
					SELECT DISTINCT N'sys_cache_tables_registry' as [ItemType], [sys_cache_tables_registry].[id] as [ItemID]
					FROM sys_cache_tables_registry
					WHERE [sys_cache_tables_registry].[id_objectType]=@id_objectType AND [sys_cache_tables_registry].[id_object]=@id_object
								end -- IF @DELETE_LEVEL<=2	
				IF @DELETE_LEVEL<=5 begin -- ЕЛЕМЕНТИ
				SET @Status += 'i'
				-- sys_ranges_criteria -- select top(1) * from sys_ranges_config; select top(1)* from sys_ranges_criteria
				INSERT INTO #EraseItems ( ItemType, ItemId )
					SELECT DISTINCT N'sys_ranges_criteria' as [ItemType], [sys_ranges_criteria].[id] as [ItemID]
					FROM #ObjectSettings as ObjectSettings
						JOIN sys_ranges_config ON [sys_ranges_config].[range_element_name] COLLATE database_default=[ObjectSettings].[element_name] COLLATE database_default
						JOIN sys_ranges_criteria ON [sys_ranges_criteria].[id_sys_ranges_config]=[sys_ranges_config].[id]
									end	-- IF @DELETE_LEVEL<=6

										END	-- IF @id_objectType IN (1,2)
		-- 6.3. КОГАТО ОБЕКТА Е НОМЕНКЛАТУРА ИЛИ ДОКУМЕНТ СПРАВКА
			ELSE IF @id_objectType = 3	BEGIN
				IF @DELETE_LEVEL<=2	begin -- ОБЕКТИ
				SET @Status += 'j'
				-- id_sys_report_settings
					INSERT INTO #EraseItems ( ItemType, ItemId )
						SELECT DISTINCT N'sys_report_settings' as [ItemType], REPLACE([objectable_table_name],N'cachereport',N'') as [ItemID]
						FROM #ObjectSettings as ObjectSettings
						WHERE [ObjectSettings].[objectable_table_name] is not null
									end -- IF @DELETE_LEVEL<=2	
										END	-- IF @id_objectType = 3


						END	-- IF @nRetValue = 1 -- 6. Load object data for erase	
-- =============================================================================================================================================================
	IF not exists (select 1 from #EraseItems )	BEGIN
		SET @nRetValue = 0;  SET @ErrMessage += cast(N';<<1>> Not items for erase for @id_object='+ISNULL(cast(@id_object as nvarchar(20)),N'null')
											 +  N' ('+ISNULL(@objectable_lang_name,N'null')+N')'
											 +  N' with @id_objectType='+  ISNULL(cast(@id_objectType as nvarchar(20)),N'null')

													  as nvarchar(MAX))
												END	
	IF @SP_MODE=N'TEST1'						BEGIN
		IF exists (select 1 from #EraseItems ) select @ProcedureName+N'('+@Status+N')' as [procedure], N'Items for erase in #EraseItems' as tbl, * from #EraseItems
		ELSE								   select @ProcedureName+N'('+@Status+N')' as [procedure], N'NOT Items for erase in #EraseItems' as tbl
		IF exists (select 1 from #EraseFiles ) select @ProcedureName+N'('+@Status+N')' as [procedure], N'Files for erase in #EraseFiles' as tbl, * from #EraseFiles
		ELSE								   select @ProcedureName+N'('+@Status+N')' as [procedure], N'NOT Files for erase in #EraseFiles' as tbl
												END	


-- *************************************************************************************************************************************************************
-- * Create @SQLString for removing / Създаване на SQL стринг за изтриване																					   *
			IF @nRetValue = 1	BEGIN	
-- *************************************************************************************************************************************************************
--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 7. Заглавен коментар  
			SET @Status += ',7'
		IF @SP_MODE = 'TEST1' -- тестов режим
		   BEGIN -- тестов режим
			SET @SQLString = cast(N'
DECLARE @id_objectType int='+ISNULL(cast(@id_objectType as nvarchar(20)),N'null')
	+N',@Objectable_id int='+ISNULL(cast(@id_object as nvarchar(20)),N'null')
	+N',@id_form int='+ISNULL(cast(@id_form as nvarchar(20)),N'null')
	+N',@id_section int='+ISNULL(cast(@id_section as nvarchar(20)),N'null')
	+N',@id_element int='+ISNULL(cast(@id_element as nvarchar(20)),N'null')
	+N',@lang nvarchar(255)='''+ISNULL(@lang,N'null')+N''''
	+N', @ErrMessage nvarchar(MAX)=N'''', @nRetValue int=1

DECLARE @Deleted_id TABLE ( [id] int ) /*for slave deletions*/
DROP TABLE IF EXISTS #ROWCOUNT; CREATE TABLE #ROWCOUNT  ( [TableName] nvarchar(128), [ROWCOUNT] int )
DROP TABLE IF EXISTS #EraseFiles; CREATE TABLE #EraseFiles ( [FileID] int, [FileName] nvarchar(128), [origin] nvarchar(128) )
DROP TABLE IF EXISTS #EraseItems; CREATE TABLE #EraseItems ( ItemType nvarchar(255), ItemId nvarchar(128) )
INSERT INTO #EraseItems ( ItemType, ItemId)
VALUES 
'									as nvarchar(MAX))
/*
N'Forms'
N'Sections'	
N'Tables'
N'Elements'
N'sys_referenceMap'
N'sys_acl_resources'		
N'sys_cache_tables_registry'
N'sys_ranges_criteria'
N'sys_report_settings'
*/
DECLARE @StrTest nvarchar(MAX) = cast('' as nvarchar(MAX))
DECLARE @DelTest bit = 0
		IF EXISTS ( select 1 from #EraseItems WHERE [ItemType]=N'Forms' )						begin
			SELECT @StrTest = STRING_AGG( cast(N'( N''Forms'', '''+[ItemID]+N''')' as nvarchar(MAX)), N',') FROM #EraseItems WHERE [ItemType]=N'Forms'
			SET @SQLString += cast(case when NULLIF(@StrTest,N'') is null then N'' when @DelTest=0 then @StrTest else N','+@StrTest end+N'
' as nvarchar(MAX)); SET @DelTest = 1															end

		IF EXISTS ( select 1 from #EraseItems WHERE [ItemType]=N'Sections' )					begin
			SELECT @StrTest = STRING_AGG( cast(N'( N''Sections'', '''+[ItemID]+N''')' as nvarchar(MAX)), N',') FROM #EraseItems WHERE [ItemType]=N'Sections'
			SET @SQLString += cast(case when NULLIF(@StrTest,N'') is null then N'' when @DelTest=0 then @StrTest else N','+@StrTest end+N'
' as nvarchar(MAX)); SET @DelTest = 1															end
		IF EXISTS ( select 1 from #EraseItems WHERE [ItemType]=N'Tables' )						begin
			SELECT @StrTest = STRING_AGG( cast(N'( N''Tables'', '''+[ItemID]+N''')' as nvarchar(MAX)), N',') FROM #EraseItems WHERE [ItemType]=N'Tables'
			SET @SQLString += cast(case when NULLIF(@StrTest,N'') is null then N'' when @DelTest=0 then @StrTest else N','+@StrTest end+N'
' as nvarchar(MAX)); SET @DelTest = 1															end
		IF EXISTS ( select 1 from #EraseItems WHERE [ItemType]=N'Elements' )					begin
			SELECT @StrTest = STRING_AGG( cast(N'( N''Elements'', '''+[ItemID]+N''')' as nvarchar(MAX)), N',') FROM #EraseItems WHERE [ItemType]=N'Elements'
			SET @SQLString += cast(case when NULLIF(@StrTest,N'') is null then N'' when @DelTest=0 then @StrTest else N','+@StrTest end+N'
' as nvarchar(MAX)); SET @DelTest = 1															end
		IF EXISTS ( select 1 from #EraseItems WHERE [ItemType]=N'sys_referenceMap' )			begin
			SELECT @StrTest = STRING_AGG( cast(N'( N''sys_referenceMap'', '''+[ItemID]+N''')' as nvarchar(MAX)), N',') FROM #EraseItems WHERE [ItemType]=N'sys_referenceMap'
			SET @SQLString += cast(case when NULLIF(@StrTest,N'') is null then N'' when @DelTest=0 then @StrTest else N','+@StrTest end+N'
' as nvarchar(MAX)); SET @DelTest = 1															end
		IF EXISTS ( select 1 from #EraseItems WHERE [ItemType]=N'sys_acl_resources' )			begin
			SELECT @StrTest = STRING_AGG( cast(N'( N''sys_acl_resources'', '''+[ItemID]+N''')' as nvarchar(MAX)), N',') FROM #EraseItems WHERE [ItemType]=N'sys_acl_resources'
			SET @SQLString += cast(case when NULLIF(@StrTest,N'') is null then N'' when @DelTest=0 then @StrTest else N','+@StrTest end+N'
' as nvarchar(MAX)); SET @DelTest = 1															end
		IF EXISTS ( select 1 from #EraseItems WHERE [ItemType]=N'sys_cache_tables_registry' )	begin
			SELECT @StrTest = STRING_AGG( cast(N'( N''sys_cache_tables_registry'', '''+[ItemID]+N''')' as nvarchar(MAX)), N',') FROM #EraseItems WHERE [ItemType]=N'sys_cache_tables_registry'
			SET @SQLString += cast(case when NULLIF(@StrTest,N'') is null then N'' when @DelTest=0 then @StrTest else N','+@StrTest end+N'
' as nvarchar(MAX)); SET @DelTest = 1															end
		IF EXISTS ( select 1 from #EraseItems WHERE [ItemType]=N'sys_ranges_criteria' )			begin
			SELECT @StrTest = STRING_AGG( cast(N'( N''sys_ranges_criteria'', '''+[ItemID]+N''')' as nvarchar(MAX)), N',') FROM #EraseItems WHERE [ItemType]=N'sys_ranges_criteria'
			SET @SQLString += cast(case when NULLIF(@StrTest,N'') is null then N'' when @DelTest=0 then @StrTest else N','+@StrTest end+N'
' as nvarchar(MAX)); SET @DelTest = 1															end
		IF EXISTS ( select 1 from #EraseItems WHERE [ItemType]=N'sys_report_settings' )			begin
			SELECT @StrTest = STRING_AGG( cast(N'( N''sys_report_settings'', '''+[ItemID]+N''')' as nvarchar(MAX)), N',') FROM #EraseItems WHERE [ItemType]=N'sys_report_settings'
			SET @SQLString += cast(case when NULLIF(@StrTest,N'') is null then N'' when @DelTest=0 then @StrTest else N','+@StrTest end+N'
' as nvarchar(MAX)); SET @DelTest = 1															end
		   END -- тестов режим край
		ELSE -- работен режим
			SET @SQLString = cast(N'
/* DECLARE @id_objectType int='+ISNULL(cast(@id_objectType as nvarchar(20)),N'null')
	+N',@Objectable_id int='+ISNULL(cast(@id_object as nvarchar(20)),N'null')
	+N',@id_form int='+ISNULL(cast(@id_form as nvarchar(20)),N'null')
	+N',@id_section int='+ISNULL(cast(@id_section as nvarchar(20)),N'null')
	+N',@id_element int='+ISNULL(cast(@id_element as nvarchar(20)),N'null')
	+N',@lang nvarchar(255)='''+ISNULL(@lang,N'null')+N''''
	+N', @ErrMessage nvarchar(MAX)=N'''', @nRetValue int=1 */

DECLARE @Deleted_id TABLE ( [id] int ) /*for slave deletions*/
'									as nvarchar(MAX))

--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 8. Create @SQLString for removing of COMMON settings / Изтриване на таблици и ОБЩИ настройки за ВСЕКИ ТИП обект
			SET @Status += ',8'
--	------------------------------------------------------------------------------------------------------------------------------------------------------------
IF @DELETE_LEVEL<=2	begin -- АКО ВЪЗЕЛА Е НАД ОБЕКТ --------------------------- ТРИЕ обекта и ВСИЧКИ НЕГОВИ: форми,секции,таблици,елементи,поделементи(свойства)
	-- 8.1. sys_objectReference / Описание на КОНКРЕТНИТЕ обекти дефинирани в системата
	-- select * from sys_objectReference
		IF exists (select 1 from #EraseItems where [ItemType]=N'Forms')
			SET @SQLString += cast( N'
/* 8.1 sys_objectReference / Описание на КОНКРЕТНИТЕ обекти дефинирани в системата */
	DELETE [dbo].[sys_objectReference] FROM [dbo].[sys_objectReference]
		JOIN #EraseItems as EI ON EI.ItemID=[sys_objectReference].[sys_form_id] AND EI.[ItemType]=N''Forms''
	WHERE [sys_systemObjects_id]='+cast(@id_objectType as nvarchar(20))+N'
										 AND [objectable_id]='+cast(@id_object as nvarchar(20))+N' 	
	INSERT INTO #ROWCOUNT VALUES (N''8.1. sys_objectReference'', @@ROWCOUNT)
'										as nvarchar(MAX))
	-- 8.2.	sys_systemObjects_Relations / Релации между системни обекти	
	-- select * from sys_systemObjects_Relations
			SET @SQLString += cast( N'
/* 8.2. sys_systemObjects_Relations / Релации между системни обекти	 */
	DELETE [dbo].[sys_systemObjects_Relations] WHERE ([source_objectType]='+cast(@id_objectType as nvarchar(20))+N' AND [source_id_object]='+cast(@id_object as nvarchar(20))+N') 
												  OR ([destination_objectType]='+cast(@id_objectType as nvarchar(20))+N' AND [destination_id_object]='+cast(@id_object as nvarchar(20))+N') 
	INSERT INTO #ROWCOUNT VALUES (N''8.2. sys_systemObjects_Relations'', @@ROWCOUNT)
'										as nvarchar(MAX))
	-- 8.3. sys_systemObjects_Relations_Columns / Псевдоними на системни обекти за изграждане на join
	-- select * from sys_systemObjects_Relations_Columns
			SET @SQLString += cast( N'
/* 8.3. sys_systemObjects_Relations_Columns / Псевдоними на системни обекти за изграждане на join */
	DELETE [dbo].[sys_systemObjects_Relations_Columns] WHERE [source_objectType]='+cast(@id_objectType as nvarchar(20))+N'
														 AND [source_id_object]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''8.3. sys_systemObjects_Relations_Columns'', @@ROWCOUNT)
'										as nvarchar(MAX))
	-- 8.4. sys_systemObjects_Relations_Columns/Псевдоними на системни обекти за изграждане на join 
	-- select * from [dbo].[sys_systemObjects_Relations_Columns]
			SET @CommandString =cast( N'
/* 8.4. sys_systemObjects_Relations_Columns/Псевдоними на системни обекти за изграждане на join */
'										 as nvarchar(MAX))
			SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_systemObjects_Relations_Columns]
		FROM [dbo].[sys_systemObjects_Relations_Columns]
			JOIN #EraseItems as EI ON EI.[ItemID]=[sys_systemObjects_Relations_Columns].[id_element] AND EI.[ItemType]=N''Elements''
		WHERE [sys_systemObjects_Relations_Columns].[source_objectType]='+cast(@id_objectType as nvarchar(20))+N'
		  AND [sys_systemObjects_Relations_Columns].[source_id_object]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''8.4. sys_systemObjects_Relations_Columns'',@@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 8.5. sys_print_forms / Описание на печатните форми	
	-- select * from sys_print_forms
			SET @SQLString += cast( N'
/* 8.5. sys_print_forms / Описание на печатните форми */
	DELETE [dbo].[sys_print_forms] WHERE [objectType]='+cast(@id_objectType as nvarchar(20))+N'
									 AND [id_object]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''8.5. sys_print_forms'', @@ROWCOUNT)
'										as nvarchar(MAX))
	-- 8.7. sys_internal_enumeration/Броячи 
	-- select * from [dbo].[sys_internal_enumeration]
			SET @CommandString =cast( N'/* 8.7. sys_internal_enumeration/Броячи */
'										 as nvarchar(MAX))
			SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_internal_enumeration]
	WHERE [id_objectType]='+cast(@id_objectType as nvarchar(20))+N'
	   AND [id_object]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''8.7. sys_internal_enumeration'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')

	-- 8.8. sys_cache_tables_registry/кеш таблица
	-- select * from sys_cache_tables_registry
		SET @CommandString = cast(N'/* 8.8.  sys_cache_tables_registry/кеш таблица */' as nvarchar(MAX))
		SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_cache_tables_registry]
	FROM [dbo].[sys_cache_tables_registry]
		JOIN #EraseItems as EI ON [sys_cache_tables_registry].[id]=EI.[ItemID] 
	WHERE EI.[ItemType]=N''sys_cache_tables_registry''
	INSERT INTO #ROWCOUNT VALUES (N''8.8.  sys_cache_tables_registry'',@@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')

	-- 8.9. sys_files/Файлове
	-- select * from [dbo].[sys_files]; select * from sys_systemObjects
	IF OBJECT_ID(N'dbo.sys_files','U') is not null	begin
			SET @CommandString =cast( N'/* 8.9. sys_files/Файлове */
'										 as nvarchar(MAX))
			SELECT @CommandString +=cast( N'
	INSERT INTO  #EraseFiles ( FileID, FileName, origin )
	SELECT [sys_files].[id],[sys_files].[sys_file_name],[sys_systemObjects].[label] 
	FROM [dbo].[sys_files]
		JOIN [dbo].[sys_systemObjects] ON [sys_systemObjects].[label]=[sys_files].[origin] AND [sys_systemObjects].[id]='+cast(@id_objectType as nvarchar(20))+N'
	WHERE [id_object]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''8.9. sys_files'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
													end -- IF OBJECT_ID(N'dbo.sys_files','U') is not null

							end -- IF @DELETE_LEVEL<=2	

 --	------------------------------------------------------------------------------------------------------------------------------------------------------------
 IF @DELETE_LEVEL<=3	begin	-- АКО ВЪЗЕЛА Е НАД ФОРМИ - ТРИЕ определена форма(=определен обект) и ВСИЧКИ НЕЙНИ:секции,таблици,елементи,поделементи(свойства)
	-- 8.10. sys_form / Описание на формите	
	-- select * from sys_form
			SET @CommandString =cast( N'/* 8.10. sys_form / Описание на формите */
'										 as nvarchar(MAX))
		IF exists (select 1 from #EraseItems where [ItemType]=N'Forms')
			SET @CommandString += cast( N'
	DELETE [dbo].[sys_form] FROM [dbo].[sys_form]
		JOIN #EraseItems as EI ON EI.ItemID=[sys_form].[id] AND EI.[ItemType]=N''Forms''
	INSERT INTO #ROWCOUNT VALUES (N''8.10. sys_form'',@@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 8.11. sys_form_display_as / Начин на разполагане на секциите за конкретната форма ( табулирани или стандартни)
	-- select * from sys_form_display_as
 						end -- IF @DELETE_LEVEL<=3

--	------------------------------------------------------------------------------------------------------------------------------------------------------------
IF @DELETE_LEVEL<=4	begin -- АКО ВЪЗЕЛА Е НАД СЕКЦИИ ----------------------------- ТРИЕ определена секция и ВСИЧКИ НЕЙНИ: таблици,елементи,поделементи(свойства)
 	-- 8.12.	sys_sections / Описание на секциите на формите в които се разделят различните елементи в документа/номенклатурата		
	-- select * from sys_sections
			SET @CommandString =cast( N'/* 8.12. sys_sections / Описание на секциите на формите в които се разделят различните елементи в документа/номенклатурата */
'										 as nvarchar(MAX))
		IF exists (select 1 from #EraseItems where [ItemType]=N'Sections')
			SET @CommandString += cast( N'			
	DELETE [dbo].[sys_sections] FROM [dbo].[sys_sections]
		JOIN #EraseItems as EI ON EI.ItemID=[sys_sections].[id] AND EI.[ItemType]=N''Sections''
	INSERT INTO #ROWCOUNT VALUES (N''8.12. sys_sections'',@@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 8.13. sys_section_display_as / Описание на начините на визуализация на секциите
	-- select * from sys_section_display_as
	-- 8.14. DROP на специфичните таблиците на обекта  
		SET @CommandString = cast(N'/* 8.14. DROP SYS,NMCL ... and STATE data tables */' as nvarchar(MAX))
 	 -- tables
		SELECT @CommandString +=cast( N'
	   DROP TABLE IF EXISTS [dbo].['+[ItemId]+N']
	   INSERT INTO #ROWCOUNT VALUES (N''8.14. DROP TABLE '+[ItemId]+N''', @@ROWCOUNT)
'										as nvarchar(MAX))
		FROM #EraseItems as ei WHERE [ItemType]=N'Tables' AND OBJECT_ID([ItemId],'U') is not null
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 8.15. history tables	
--	IF @id_objectType = 2 begin
--		SET @CommandString = cast(N'/* 8.15. DROP history tables */' as nvarchar(MAX))
--		SELECT @CommandString +=cast( N'
--	   DROP TABLE IF EXISTS [dbo].['+[ItemId]+N'_history]
--	   INSERT INTO #ROWCOUNT VALUES (N''8.15. DROP TABLE '+[ItemId]+N'_history'', @@ROWCOUNT)
--'										as nvarchar(MAX))
--		FROM #EraseItems WHERE [ItemType]=N'Tables' AND OBJECT_ID([ItemId]+N'_history','U') is not null
--		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
--						  end -- IF @id_objectType = 2 
	-- 8.16. translations tables	
--	SET @CommandString = cast(N'/* 8.16. DROP translations EMPTY tables */' as nvarchar(MAX))
--		SELECT @CommandString +=cast( N'
--	   DROP TABLE IF EXISTS [dbo].['+[ItemId]+N'_translations]
--	   INSERT INTO #ROWCOUNT VALUES (N''8.16. DROP TABLE '+[ItemId]+N'_translations(EMPTY)'', @@ROWCOUNT)
--'										as nvarchar(MAX))
--		FROM #EraseItems as er
--		WHERE er.[ItemType]=N'Tables' 
--		  AND OBJECT_ID(er.[ItemId]+N'_translations','U') is not null
--		  AND NOT EXISTS (select 1 from #EraseItems where [ItemId] = er.[ItemId]+N'_translations')
--		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 8.17. sys_table/имена на таблици  
		SET @CommandString = cast(N'/* 8.17. sys_table/имена на таблици */' as nvarchar(MAX))
	 -- tables
		SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_table] WHERE [name] = N'''+[ItemID]+N'''
	INSERT INTO #ROWCOUNT VALUES (N''8.17. sys_table '+CAST([ItemID] as nvarchar(128))+N''', @@ROWCOUNT)
'										as nvarchar(MAX))
		FROM #EraseItems WHERE [ItemType]=N'Tables'
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')

						end -- IF @DELETE_LEVEL<=4	

--	------------------------------------------------------------------------------------------------------------------------------------------------------------ 
IF @DELETE_LEVEL<=5		begin -- АКО ВЪЗЕЛА Е НАД ЕЛЕМЕНТИ ----------------------------------- ТРИЕ определения ЕЛЕМЕНТ и ВСИЧКИ НГОВИ: поделементи(свойства)
	-- 8.18. sys_element / Описание на всички елементи в системата
	-- select * from [dbo].[sys_element]
	/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Ако се събира списъка елементи в стринг за динамични обекти стринга ще стане огромен
		select sys_element.id,sys_referenceMap_dynamic.* from sys_referenceMap_dynamic ALTER TABLE DROP COLUMN
			join sys_element ON sys_referenceMap_dynamic.sys_element_id=sys_element.id
		where sys_referenceMap_dynamic.sys_table_id=3011
			select 'proba'
			select * 
			FROM @Erase_columns as ec
				LEFT JOIN #EraseItems as ei ON ei.[ItemType]=N'Tables' AND ei.[ItemID] COLLATE database_default = ec.column_table COLLATE database_default
			WHERE ei.[ItemId] is null -- не изтрита цялата таблица 
	*/
			SET @CommandString =cast( N'/* 8.18. sys_element/Описание на елементи */
	DELETE [dbo].[sys_element]
	FROM [dbo].[sys_element]
		JOIN #EraseItems as EI ON EI.ItemID=[sys_element].[id] AND EI.[ItemType]=N''Elements''
	INSERT INTO #ROWCOUNT VALUES (N''8.18. sys_element'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
			SET @CommandString = cast( N'/* 8.18. DROP COLUMN of sys_element.id/Премахване на колоните по sys_element.id*/
'										as nvarchar(MAX))
			SELECT @CommandString += cast( 
N'ALTER TABLE ['+[column_table]+N'] DROP COLUMN ['+[column_name]+N']
	INSERT INTO #ROWCOUNT VALUES (N''8.18. DROP COLUMN '+column_table+N'.'+[column_name]+''', 1 )
'										as nvarchar(MAX))
			FROM @Erase_columns as ec
				LEFT JOIN #EraseItems as ei ON ei.[ItemType]=N'Tables' AND ei.[ItemID] COLLATE database_default = ec.column_table COLLATE database_default
			WHERE ei.[ItemId] is null -- не e посочена цялата таблица за изтриване
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')

	-- 8.19. sys_referenceMap/Описание на релаците между елементи, форми, секции, таблици и ресурси 
	-- select * from [dbo].[sys_referenceMap]
	-- select * from [dbo].[sys_acl_resources]; select * from [dbo].[sys_acl_resource_role_rel]; select * from [dbo].[sys_acl_resource_user_rel]
			SET @CommandString =cast( N'/* 8.19. sys_referenceMap/Описание на релаците между елементи, форми, секции, таблици и ресурси */
'										 as nvarchar(MAX))
			SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_referenceMap] 
	FROM [dbo].[sys_referenceMap]
 		JOIN #EraseItems as EI1 ON EI1.ItemID=[sys_referenceMap].[id] AND EI1.[ItemType]=N''sys_referenceMap''
	INSERT INTO #ROWCOUNT VALUES (N''8.19. sys_referenceMap'', @@ROWCOUNT)

	DELETE [dbo].[sys_acl_resources] 
	FROM [dbo].[sys_acl_resources] 
 		JOIN #EraseItems as EI1 ON EI1.ItemID=[sys_acl_resources].[id] AND EI1.[ItemType]=N''sys_acl_resources''
	INSERT INTO #ROWCOUNT VALUES (N''8.19. sys_acl_resources'', @@ROWCOUNT)

	DELETE [dbo].[sys_acl_resource_role_rel] 
	FROM [dbo].[sys_acl_resource_role_rel] 
 		JOIN #EraseItems as EI1 ON EI1.ItemID=[sys_acl_resource_role_rel].[id_resource] AND EI1.[ItemType]=N''sys_acl_resources''
	INSERT INTO #ROWCOUNT VALUES (N''8.19. sys_acl_resource_role_rel'', @@ROWCOUNT)

	DELETE [dbo].[sys_acl_resource_user_rel]
	FROM [dbo].[sys_acl_resource_user_rel]
 		JOIN #EraseItems as EI1 ON EI1.ItemID=[sys_acl_resource_user_rel].[id_resource] AND EI1.[ItemType]=N''sys_acl_resources''
	INSERT INTO #ROWCOUNT VALUES (N''8.19. sys_acl_resource_user_rel'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')

	-- 8.20. sys_referenceMap_dynamic/Описание на релаците между елементи, форми, секции, таблици и ресурси 
	-- select * from [dbo].[sys_referenceMap_dynamic]
			SET @CommandString =cast( N'/* 8.20. sys_referenceMap_dynamic/Описание на релаците между елементи, форми, секции, таблици и ресурси и стойности на динамични елементи */
'										 as nvarchar(MAX))
		IF exists (select 1 from #EraseItems where [ItemType]=N'Forms') AND exists (select 1 from #EraseItems where [ItemType]=N'Sections')
			SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_referenceMap_dynamic]
 		JOIN #EraseItems as EI1 ON EI1.ItemID=[sys_sections].[id] AND EI1.[ItemType]=N''Sections''
 		JOIN #EraseItems as EI2 ON EI2.ItemID=[sys_sections].[id] EI1.[ItemType]=N''Forms''
	INSERT INTO #ROWCOUNT VALUES (N''8.20. sys_referenceMap_dynamic'', @@ROWCOUNT)
'										as nvarchar(MAX))
			FROM #EraseItems WHERE [ItemType]=N'Sys_table'
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')

	-- 8.21. sys_validation_rules/ Номенклатура на правилата за валидиране на елементите според фунциите на Laravel
	-- select * from [dbo].[sys_validation_rules]
			
	-- 8.22. sys_validation_rules_element_rel/Релации между sys_validation_rules и sys_referenceMap
	-- select * from [dbo].[sys_validation_rules_element_rel]
			SET @CommandString =cast( N'/* 8.22. sys_validation_rules_element_rel/Релации между sys_validation_rules и sys_referenceMap */
'										 as nvarchar(MAX))
			SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_validation_rules_element_rel]
	FROM [dbo].[sys_validation_rules_element_rel] 
		JOIN #EraseItems as EI ON EI.ItemID=[sys_validation_rules_element_rel].[sys_element_id]
	WHERE EI.[ItemType]=N''Elements''
	INSERT INTO #ROWCOUNT VALUES (N''8.22. sys_validation_rules_element_rel'',@@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 8.23. sys_grid_dependency / Релация между grid и subgrid в документа (съставки)	
	-- select * from sys_grid_dependency
		IF exists (select 1 from #EraseItems where [ItemType]=N'Sections')
			SET @SQLString += cast( N'/* 8.23. sys_grid_dependency / Релация между grid и subgrid в документа (съставки) */
	DELETE [dbo].[sys_grid_dependency] FROM [dbo].[sys_grid_dependency]
	 	LEFT JOIN #EraseItems as EI1 ON EI1.ItemID=[sys_grid_dependency].[source_grid] AND EI1.[ItemType]=N''Sections''
	 	LEFT JOIN #EraseItems as EI2 ON EI2.ItemID=[sys_grid_dependency].[destination_grid] AND EI1.[ItemType]=N''Sections''
	WHERE EI1.ItemID is not null OR EI2.ItemID is not null
	INSERT INTO #ROWCOUNT VALUES (N''8.23. sys_grid_dependency'', @@ROWCOUNT)
'										as nvarchar(MAX))
	-- 8.24. sys_element_dependency / Зависимост между елементите
	-- select * from sys_element_dependency
			SET @SQLString += cast( N'/* 8.24. sys_element_dependency / Зависимост между елементите */	
	DELETE [dbo].[sys_element_dependency]
	FROM [dbo].[sys_element_dependency]
		JOIN #EraseItems as EI ON [sys_element_dependency].[refmapSource]=EI.[ItemID] 
							   OR [sys_element_dependency].[refmapTarget]=EI.[ItemID]
	WHERE EI.[ItemType]=N''sys_referenceMap''
	INSERT INTO #ROWCOUNT VALUES (N''8.24. sys_element_dependency'',@@ROWCOUNT)
'										as nvarchar(MAX))
	-- 8.25. sys_element_dependency_element_required / Задължителни елементи
	-- select * from sys_element_dependency_element_required
	IF OBJECT_ID(N'dbo.sys_element_dependency_element_required','U') is not null	begin
			SET @SQLString += cast( N'/* 8.25. sys_element_dependency_element_required / Задължителни елементи */	
	DELETE [dbo].[sys_element_dependency_element_required]
	FROM [dbo].[sys_element_dependency_element_required]
		JOIN #EraseItems as EI ON [sys_element_dependency_element_required].[sys_element_id]=EI.[ItemID] AND EI.[ItemType]=N''Elements''
	INSERT INTO #ROWCOUNT VALUES (N''8.25. sys_element_dependency_element_required'',@@ROWCOUNT)
'										as nvarchar(MAX))
																					end	--IF OBJECT_ID(N'dbo.sys_element_dependency_element_required','U') is not null
	--	8.26.	sys_translations / Всички преводи в системата - ПРОВЕРКА НА ПРЕВОДИТЕ СЕ ПРАВИ ОТ ДРУГА ПРОЦЕДУРА

	 						end -- IF @DELETE_LEVEL<=5	

--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 9. Create @SQLString for removing of NOMENCLATURES / Изтриване на таблици и настройки за номенклатури
			SET @Status += ',9'
IF @nRetValue = 1 AND @id_objectType=1  AND @DELETE_LEVEL<=2	BEGIN
	-- 9.1. sys_nmcl / Списък на всички номенклатури в системата
	-- select * from sys_nmcl
			SET @SQLString += cast( N'
/* 9.1 sys_nmcl / Списък на всички номенклатури в системата	 */
	DELETE [dbo].[sys_nmcl] WHERE 1='+cast(@id_objectType as nvarchar(20))+N'
							  AND [id]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''9.1 sys_nmcl'', @@ROWCOUNT)
'										as nvarchar(MAX))
	-- 9.2. sys_nmcl_relations/Релации между номенклатури 
	-- select * from [dbo].[sys_nmcl_relations]
			SET @CommandString =cast( N'/* 9.2. sys_nmcl_relations/Релации между номенклатури */
'										 as nvarchar(MAX))
			SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_nmcl_relations]
	WHERE [id_nmcl1]='+cast(@id_object as nvarchar(20))+N'
	   OR [id_nmcl2]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''9.2. sys_nmcl_relations'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')


																END -- IF @nRetValue = 1 AND @id_objectType=1  AND @DELETE_LEVEL<=2	

--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 10. Create @SQLString for removing of DOCUMENTS / Изтриване на таблици и настройки за документи
			SET @Status += ',10'
IF @nRetValue = 1 AND @id_objectType=2 AND @DELETE_LEVEL<=2	BEGIN
	-- 10.1. sys_doctype / Типове документи
	-- select * from sys_doctype
			SET @CommandString = cast( N'
/* 10.1 sys_doctype / Типове документи	 */
	DELETE [dbo].[sys_doctype] WHERE 2='+cast(@id_objectType as nvarchar(20))+N'
								 AND [id]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''10.1. sys_doctype'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')

	-- 10.2. DELETE на данните в sys_srow  
			SET @CommandString = cast(N'
/* 10.2 sys_srow / Редове на документа	 */
		delete syssrow 
		from (	select t.id_doctype,r.id 
				from sys_srow as r
					join sys_stitle as t ON t.id = r.id_sys_stitle
				where t.id_doctype = '+cast(@id_object as nvarchar(20))+N'
			 ) as row
			 join sys_srow as syssrow ON syssrow.id=row.id
	INSERT INTO #ROWCOUNT VALUES (N''10.2. DELETE sys_srow'',@@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 10.3. DELETE на данните в sys_stitle 
				SET @CommandString =cast( N'
/* 10.3 sys_stitle / Заглавни данни на документа */
		delete sys_stitle where [id_doctype]='+cast(@id_object as nvarchar(20))+N'
		INSERT INTO #ROWCOUNT VALUES (N''10.3. DELETE sys_stitle'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 10.4. sys_doctype_report_filter / Вътрешно-документни справки
	-- select * from sys_doctype_report_filter 
			SELECT @CommandString =cast( N'/* 10.4. sys_doctype_report_filter / Вътрешно-документни справки */
	DELETE [dbo].[sys_doctype_report_filter]
	FROM [dbo].[sys_doctype_report_filter]
		JOIN #EraseItems as EI ON EI.[ItemID]=[sys_doctype_report_filter].[id_ref]
	WHERE EI.[ItemType]=N''sys_referenceMap''
	  AND [sys_doctype_report_filter].[id_sys_doctype]='+cast(@id_object as nvarchar(20))+N'
		INSERT INTO #ROWCOUNT VALUES (N''10.4. sys_doctype_report_filter'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 10.5. sys_report_doctype_section / Търсачки(справки зарждащи секция в документ)
	-- select * from sys_report_doctype_section
	IF OBJECT_ID(N'dbo.sys_report_doctype_section',N'U') is not null
	AND exists (select 1 from #EraseItems where [ItemType]=N'Sections')
	  begin
		SET @CommandString =cast( N'/* 10.5. sys_report_doctype_section / Търсачки(справки зарждащи секция в документ) */' as nvarchar(MAX))
		SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_report_doctype_section] 
	 	JOIN #EraseItems as EI ON EI.ItemID=[sys_report_doctype_section].[id_section] AND EI.[ItemType]=N''Sections''	
	INSERT INTO #ROWCOUNT VALUES (N''10.5. sys_report_doctype_section'', @@ROWCOUNT)
'										as nvarchar(MAX))
		FROM #EraseItems WHERE [ItemType]=N'sys_reports_settings' 		
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	  end -- IF OBJECT_ID(N'dbo.sys_report_doctype_section',N'U') is not null AND exists (select 1 from #EraseItems where [ItemType]=N'Sections')
	-- 10.6. sys_doctype_fill / Конфигуриране на създаването на документ от справка 
	-- select * from sys_doctype_fill
			SET @CommandString += cast( N'/* 10.6. sys_doctype_fill / Конфигуриране на създаването на документ от справка */' as nvarchar(MAX))
			SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_doctype_fill] WHERE [id_sys_doctype]='+cast(@id_object as nvarchar(20))+N'
									   AND [id_object]='++cast([ItemId] as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''10.6. sys_doctype_fill'', @@ROWCOUNT)
'										as nvarchar(MAX))
		FROM #EraseItems WHERE [ItemType]=N'sys_reports_settings' 		
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 10.7. sys_document_object_relation / Релация между системен обект и неговия подобект 
	-- select * from sys_document_object_relation
			SET @CommandString += cast( N'/* 10.7. sys_document_object_relation / Релация между системен обект и неговия подобект	 */
	DELETE [dbo].[sys_document_object_relation] WHERE ([source_objectType]='+cast(@id_objectType as nvarchar(20))+N' AND [source_object]='+cast(@id_object as nvarchar(20))+N')
												   OR ([destination_objectType]='+cast(@id_objectType as nvarchar(20))+N' AND [destination_object]='+cast(@id_object as nvarchar(20))+N')
	INSERT INTO #ROWCOUNT VALUES (N''10.7. sys_document_object_relation'',@@ROWCOUNT)
'						as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 10.8. sys_document_relation / Релацията между документите
	-- select * from sys_document_relation
			SET @CommandString = cast( N'10.8. sys_document_relation / Релацията между документите' as nvarchar(MAX))
		IF exists (select 1 from #EraseItems where [ItemType]=N'Sections')
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_document_relation]  
	 	LEFT JOIN #EraseItems as EI1 ON EI1.ItemID=[sys_document_relation].[source_id_section] AND EI1.[ItemType]=N''Sections''		
	 	LEFT JOIN #EraseItems as EI2 ON EI2.ItemID=[sys_document_relation].[destination_id_section] AND EI2.[ItemType]=N''Sections''		
	WHERE 2='+cast(@id_objectType as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''10.8. sys_document_relation'',@@ROWCOUNT)
'						as nvarchar(MAX))
	-- 10.9. sys_document_relation_config / Наследяване на елементи от sys_referenceMap
	-- select * from sys_document_relation_config
			SET @CommandString = cast( N'/* 10.9. sys_document_relation_config / Наследяване на елементи от sys_referenceMap */' as nvarchar(MAX))
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_document_relation_config]
	FROM [dbo].[sys_document_relation_config] 
		JOIN FROM #EraseItems as EI ON EI.[ItemID]=[sys_document_relation_config].[id_source_referenceMap]
									OR EI.[ItemID]=[sys_document_relation_config].[id_destination_referenceMap]	
	WHERE EI.[ItemType]=N''sys_referenceMap''
	  AND [sys_document_relation_config].[id_sys_document_object_relation]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''10.9. sys_document_relation_config'',@@ROWCOUNT)
'						as nvarchar(MAX))

	-- 10.10. sys_ranges_config/Релации между броячи и елементи ---------------------------------------------------------------------------------------------------------
	-- select * from [dbo].[sys_ranges_config]
			SET @CommandString =cast( N'/* 10.10. sys_ranges_config/Релации между броячи и елементи */
'										 as nvarchar(MAX))
			SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_ranges_config]
	WHERE [objectType]='+cast(@id_objectType as nvarchar(20))+N'
	  AND [id_object]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''10.10. sys_ranges_config'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 10.11. sys_ranges_criteria/Критерии за получаване на номер при номерация automatic -------------------------------------------------------------------------------
IF OBJECT_ID(N'dbo.sys_ranges_criteria','U') is not null begin
	-- select * from [dbo].[sys_ranges_criteria]
			SET @CommandString =cast( N'/* 10.11. sys_ranges_criteria/Критерии за получаване на номер при номерация automatic */
'										 as nvarchar(MAX))
			SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_ranges_criteria]
	FROM [dbo].[sys_ranges_criteria]
		JOIN #EraseItems as EI ON EI.[ItemID]=[sys_ranges_criteria].[id]
	WHERE EI.[ItemType]=N''sys_ranges_criteria''
	INSERT INTO #ROWCOUNT VALUES (N''10.11. sys_ranges_criteria'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
														 end -- IF OBJECT_ID(N'dbo.sys_ranges_criteria','U') is not null 

															END -- IF @nRetValue = 1 AND @id_objectType=2 AND @DELETE_LEVEL<=2	

--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 11. Create @SQLString for removing of REPORTS / Изтриване на таблици и настройки за справки
			SET @Status += ',11'
IF @nRetValue = 1 AND @id_objectType=3 AND @DELETE_LEVEL<=2	BEGIN
	-- 11.1. sys_reports / Номенклатура/списък на справките(без вътрешнодокументните) 
	-- select * from sys_reports
			SET @SQLString += cast( N'
/* 11.1 sys_reports / Номенклатура/списък на справките(без вътрешнодокументните)	 */
	DELETE [dbo].[sys_reports] WHERE 3='+cast(@id_objectType as nvarchar(20))+N'
								 AND [id]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''11.1. sys_reports'', @@ROWCOUNT)
'										as nvarchar(MAX))
	-- 11.2. sys_reports_settings / Описание на справките 
	-- select * from sys_reports_settings
			SET @CommandString = cast( N'/* 11.2. sys_reports_settings / Описание на справките */' as nvarchar(MAX))
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_reports_settings]  WHERE [objectType]='+cast(@id_objectType as nvarchar(20))+N'
										   AND [id_object]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''11.2. sys_reports_settings'',@@ROWCOUNT)
'						as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 11.3. sys_reports_columns/ Описание на колоните, които имат справките, за настройна на кеш таблиците на справките
	-- select * from sys_reports_columns
			SET @CommandString = cast( N'/* 11.3. sys_reports_columns/ Описание на колоните, които имат справките, за настройна на кеш таблиците на справките */
'										as nvarchar(MAX))
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_reports_columns]  WHERE 3='+cast(@id_objectType as nvarchar(20))+N' AND [id_object]='+[ItemID]+N'
	INSERT INTO #ROWCOUNT VALUES (N''11.3. sys_reports_columns'',@@ROWCOUNT)
'						as nvarchar(MAX))
		FROM #EraseItems WHERE [ItemType]=N'sys_reports_settings' 		
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 11.4. sys_reports_columns_config / Настройки на колоните на справките	 
	-- select * from sys_reports_columns_config
			SET @CommandString = cast( N'/* 11.4. sys_reports_columns_config / Настройки на колоните на справките */' as nvarchar(MAX))
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_reports_columns_config]  WHERE [id_object]='+[ItemID]+N'
	INSERT INTO #ROWCOUNT VALUES (N''11.4. sys_reports_columns_config'',@@ROWCOUNT)
'												as nvarchar(MAX))
		FROM #EraseItems WHERE [ItemType]=N'sys_reports_settings' 		
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 11.5. sys_reports_edit_column / Настройки на колоните при редакция на ред	 
	-- select * from sys_reports_edit_column
			SET @CommandString = cast( N'/* 11.5. sys_reports_edit_column / Настройки на колоните при редакция на ред */' as nvarchar(MAX))
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_reports_edit_column]  WHERE [id_object]='+[ItemID]+N'
	INSERT INTO #ROWCOUNT VALUES (N''11.5. sys_reports_edit_column'',@@ROWCOUNT)
'												as nvarchar(MAX))
		FROM #EraseItems WHERE [ItemType]=N'sys_reports_settings' 		
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 11.6. sys_reports_settings_filters / кеш на филтрите на справките по потребители	 
	-- select * from sys_reports_settings_filters
			SET @CommandString = cast( N'/* 11.6. sys_reports_settings_filters / кеш на филтрите на справките по потребители */' as nvarchar(MAX))
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_reports_settings_filters]  WHERE [id_sys_reports]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''11.6. sys_reports_settings_filters'',@@ROWCOUNT)
'												as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 11.7. sys_reports_settings_table_configuration_cache / кеш на изгледите на справките по потребители	 
	-- select * from sys_reports_settings_table_configuration_cache
			SET @CommandString = cast( N'/* 11.7. sys_reports_settings_table_configuration_cache/ кеш на изгледите на справките по потребители */' as nvarchar(MAX))
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_reports_settings_table_configuration_cache]  WHERE [id_sys_reports_settings]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''11.7. sys_reports_settings_table_configuration_cache'',@@ROWCOUNT)
'												as nvarchar(MAX))
		FROM #EraseItems WHERE [ItemType]=N'sys_reports_settings' 		
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- 11.8. sys_acl_resources / права за справката	 
			SET @CommandString =cast( N'/* 11.8. sys_acl_resources / права за справката */
DELETE @Deleted_id
'										 as nvarchar(MAX))
			SELECT @CommandString +=cast( N'
	DELETE [dbo].[sys_acl_resources] 
		OUTPUT DELETED.id INTO @Deleted_id 
	FROM [dbo].[sys_acl_resources]
		JOIN #EraseItems as EI ON EI.[ItemID]=[sys_acl_resources].[id]
	WHERE EI.[ItemType]=N''sys_acl_resources''
	INSERT INTO #ROWCOUNT VALUES (N''11.8. sys_acl_resources'', @@ROWCOUNT)
	/*related deletings*/
		DELETE [dbo].[sys_acl_resource_role_rel] 
			FROM [dbo].[sys_acl_resource_role_rel] 
				JOIN @Deleted_id as deleted_resources ON deleted_resources.id=[sys_acl_resource_role_rel].[id_resource]
		INSERT INTO #ROWCOUNT VALUES (N''11.8. sys_acl_resource_role_rel'', @@ROWCOUNT)
		DELETE [dbo].[sys_acl_resource_user_rel]
			FROM [dbo].[sys_acl_resource_user_rel]
				JOIN @Deleted_id as deleted_resources ON deleted_resources.id=[sys_acl_resource_user_rel].[id_resource]
		INSERT INTO #ROWCOUNT VALUES (N''11.8. sys_acl_resource_user_rel'', @@ROWCOUNT)
'										as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
															END -- IF @nRetValue = 1 AND @id_objectType=3 AND @DELETE_LEVEL<=2

--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 12. Inheritance settings / СЕГА СЕ ИЗТРИВАТ НАСТРОЙКИТЕ ЗА ДОКУМЕНТА В ТАБЛИЦИ ЗА НАСЛЕДЯВАНИЯ
			SET @Status += ',12'
IF @nRetValue = 1 AND @id_objectType=2  AND @DELETE_LEVEL<=2	BEGIN
	-- 12.1. sys_document_relation_type / Таблицата съдържа типовете наследяване????????????????
	-- select * from sys_document_relation_type

	-- 12.2. sys_DocTypeInheritance / Наследявания по документи
	-- select * from sys_DocTypeInheritance
			SET @CommandString = cast( N'/* 12.2. sys_DocTypeInheritance / Наследявания по документи */' as nvarchar(MAX))
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_DocTypeInheritance]  WHERE 2='+cast(@id_objectType as nvarchar(20))+N'
											 AND [Id_nmcl_DocType_Ancestor]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''12.2. sys_DocTypeInheritance'',@@ROWCOUNT)
'												as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')

	-- 12.3. sys_InheritanceSettings / Наследявания
	-- select * from sys_InheritanceSettings
			SET @CommandString = cast( N'/* 12.3. sys_InheritanceSettings / Наследявания */' as nvarchar(MAX))
		IF exists (select 1 from #EraseItems where [ItemType]=N'Sections')
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_InheritanceSettings]
	FROM [dbo].[sys_InheritanceSettings] 
	 	JOIN #EraseItems as EI ON EI.ItemID=[sys_InheritanceSettings].[id_sys_section_Ancestor] AND EI.[ItemType]=N''Sections''		
	WHERE 2='+cast(@id_objectType as nvarchar(20))+N'
											 AND [Id_nmcl_DocType_Ancestor]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''12.3. sys_InheritanceSettings'',@@ROWCOUNT)
'												as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
	-- select * from sys_InheritanceMatrix
			SET @CommandString = cast( N'/* 12.3. sys_InheritanceMatrix / Наследявания */' as nvarchar(MAX))
		IF exists (select 1 from #EraseItems where [ItemType]=N'Sections')
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_InheritanceMatrix] 
	FROM [dbo].[sys_InheritanceMatrix] 
	 	JOIN #EraseItems as EI ON EI.ItemID=[sys_InheritanceMatrix].[id_sys_section_Inheritor] AND EI.[ItemType]=N''Sections''		
	WHERE 2='+cast(@id_objectType as nvarchar(20))+N'
											 AND [Id_nmcl_DocType_Inheritor]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''12.3. sys_InheritanceMatrix'',@@ROWCOUNT)
'												as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')
																END -- IF @nRetValue = 1 AND @id_objectType=2 AND @DELETE_LEVEL<=2

--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 13. Quantity settings / СЕГА СЕ ИЗТРИВАТ НАСТРОЙКИТЕ ЗА ДОКУМЕНТА В ТАБЛИЦИ ЗА НАЛИЧНОСТИ
			SET @Status += ',13'
IF @nRetValue = 1 AND @id_objectType=2 AND @DELETE_LEVEL<=2	BEGIN
	-- 13.1. sys_quantityReference / Наличности 
	-- select * from sys_quantityReference
			SET @CommandString = cast( N'/* 13.1. sys_quantityReference / Наличности */' as nvarchar(MAX))
		IF exists (select 1 from #EraseItems where [ItemType]=N'Sections')
			SELECT @CommandString += cast( N'
	DELETE [dbo].[sys_quantityReference]
	FROM [dbo].[sys_quantityReference]
	 	JOIN #EraseItems as EI ON EI.ItemID=[sys_quantityReference].[sys_section_id] AND EI.[ItemType]=N''Sections''		
	WHERE 2='+cast(@id_objectType as nvarchar(20))+N'
											AND [sys_doctype_id]='+cast(@id_object as nvarchar(20))+N'
	INSERT INTO #ROWCOUNT VALUES (N''13.1. sys_InheritanceSettings'',@@ROWCOUNT)
'												as nvarchar(MAX))
		SET @SQLString += ISNULL(cast( @CommandString as nvarchar(MAX)),N'')

															END -- IF @nRetValue = 1 AND @id_objectType=2 AND @DELETE_LEVEL<=2	


-- *************************************************************************************************************************************************************
-- * Create @SQLString for removing / Създаване на SQL стринг за изтриване		К Р А Й																			   *
								END -- IF @nRetValue = 1
-- *************************************************************************************************************************************************************



-- =============================================================================================================================================================
--	------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 14. REAL EXECUTION OF ERASE
		SET @Status += ',14'
	  DROP TABLE IF EXISTS #ROWCOUNT; CREATE TABLE #ROWCOUNT  ( [TableName] nvarchar(128), [ROWCOUNT] int )
IF @nRetValue = 1	BEGIN
	SET @ParamDefinition = N'@ErrMessage nvarchar(MAX) OUTPUT, @nRetValue int=1 OUTPUT'
	-- A. Ако съм в тестов режим, само публикува създадения скрипт
	IF @SP_MODE='TEST1'			begin
		SET @SQLString += cast(N'
		/*============= #ROWCOUNT =================*/
		SELECT N''#ROWCOUNT'' as tbl,* FROM #ROWCOUNT
		/*------------ #EraseFiles ----------------*/
		SELECT N''#EraseFiles'' as tbl,* FROM #EraseFiles
		'						 as nvarchar(MAX))

		IF exists (select 1 from #MultiUsed )	select   @ProcedureName+N'('+@Status+N')' as [procedure], N'#MultiUsed' as tbl,* from #MultiUsed
		ELSE									select   @ProcedureName+N'('+@Status+N')' as [procedure], N'No rows in #MultiUsed' as tbl

		select   @ProcedureName+N'('+@Status+N')' as [procedure], N'FINAL ERASE SCRIPT' as tbl
				,@SQLString as '@SQLString', @ParamDefinition as '@ParamDefinition'

								end -- IF @SP_MODE='TEST1'

	-- B. Ако НЕ съм в тестов режим НО @Force_erasing = -1
	ELSE IF @Force_erasing=-1 SET @ErrMessage +=cast(N'Stopping script for erase' as nvarchar(MAX))
		 -- C. Ако НЕ съм в тестов режим и Е създаден скрипт за изтриване
		 ELSE IF NULLIF(@SQLString,N'') is not null		begin
		 	print 'execute'; print @SQLString; print 'endstring'
		 	 DECLARE @prm_ErrMessage nvarchar(MAX), @prm_RetValue int
		 	 SET @ExitCode = 0
		 		EXEC @ExitCode=sp_executeSQL @SQLString, @ParamDefinition
		 							--	,@Objectable_id=@id_object
		 								,@ErrMessage=@prm_ErrMessage OUTPUT
		 								,@nRetValue=@prm_RetValue OUTPUT
		 	--select @ProcedureName+N'('+@Status+N')' as [procedure], N'FINAL ERASE SCRIPT' as tbl
		 	--		,@SQLString as '@SQLString', @ParamDefinition as '@ParamDefinition'
		 	IF @ExitCode > 0 begin SET @ErrMessage += N'<<1>> Error occurred while erasing object'; SET @nRetValue=0 end
		 	ELSE IF @prm_RetValue = 0 begin SET @ErrMessage += N'<<1>> Error occurred in the script to erasing an object'; SET @nRetValue=0 end
		 		 ELSE begin SET @ErrMessage += ISNULL( @prm_ErrMessage, N'') end
		 												end
					END -- IF @nRetValue = 1	
-- =============================================================================================================================================================
		IF @SP_MODE = 'TEST1' select   @ProcedureName+N'('+@Status+N')' as [procedure], N'FINAL ACTION' as tbl
									  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'


-- =============================================================================================================================================================
-- Create JSON with TASK for OUTPUT
IF @nRetValue = 1	SET @Output_json =	(	SELECT @ROWCOUNT															   as [STOP_ERASING]		
												  ,( SELECT * FROM #ROWCOUNT FOR JSON PATH /*, WITHOUT_ARRAY_WRAPPER*/	)  as [ErasedTables]
												  ,( SELECT * FROM #EraseFiles FOR JSON PATH /*, WITHOUT_ARRAY_WRAPPER*/)  as [FilesForErase] 
												  ,( SELECT  MultiUsed.*
															,@id_object	as [id_object]
															,case when @id_objectType=1 then N'nom'
																  when @id_objectType=2 then N'doc'
													 			  when @id_objectType=3 then N'rep'
																  else N'n/a' end as [id_objectsType]
													 FROM #MultiUsed as MultiUsed
													 FOR JSON PATH /*, WITHOUT_ARRAY_WRAPPER*/)								as [MultiUsed] 

											FOR JSON PATH		
										)
						

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] = 'table_cursor' COLLATE database_default ) 	begin	CLOSE table_cursor;	DEALLOCATE table_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

	-- Return of result
	IF @nRetValue > 1 SET @nRetValue = 1
	SET @Return = @nRetValue
	-- Връщане на грешката към приложението
	DECLARE @Error TABLE ( [Error] nvarchar(2048) ); INSERT INTO @Error SELECT [Value] as Error FROM string_split(@ErrMessage,';')	WHERE ISNULL([Value],N'') <> N'' 
		SET @ErrMessage = cast(		(SELECT cast([Error] as nvarchar(MAX)) as [Error] FROM @Error FOR JSON PATH, ROOT('Errors'))
								as nvarchar(MAX))


	SELECT @nRetValue as [Return], @ErrMessage as [Error_json], @Output_json as [Output_json] 
	RETURN @nRetValue

END	-- End of procedure