SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-- select * from sys_nmcl where tablename like '%stuff%'
-- select * from nmcl_STUFF
-- select * from nmcl_price where UnitPriceWithVat=140
	exec [dbo].[usp_ssERP8_SearchGroupIdInNMCL]  @nmcl_name	='nmcl_price'
												,@StringToFind = N'140'
												,@JsonToSearch = N'["UnitPriceWithVat","UnitPriceBase"]'
												,@nmcl_rows_group_field=N'id_nmcl_stuffGroup'
												,@nmcl_rows_group_id=1
												,@tablename_extra = 'nmcl_stuff'
												,@SP_MODE = 'TEST1'
--*/
DROP PROCEDURE IF EXISTS [dbo].[usp_ssERP8_SearchGroupIdInNMCL]
GO
-- =========================================================================================================================
CREATE PROCEDURE 
-- Name			
				[dbo].[usp_ssERP8_SearchGroupIdInNMCL]
-- Author		Щерю Щерев																									
-- Create date	28/03/2024
-- Version		v.10 1/9/2025 - Before use is_changed flag
--				v.11 1/9/2025 - AFTER use is_changed flag
--
-- Serever		MS SQL 2016																										
-- Description	Search for a STRING in multiple OR all columns of the nomenclature 
-- Returns		0 - fail 1 - success
-- Parameters
	    @nmcl_name				nvarchar(128)						-- 1. Name of nomenclature from sys_nmcl
	   ,@StringToFind			nvarchar(1024)	NULL=NULL			-- 2. String to find in nomenclature
	   ,@JsonToSearch			nvarchar(MAX)	NULL=NULL			-- 3. json array with COLUMN NAMES where @StringToFind is searched charindex
	   ---------------------------------------------	  	
	   ,@nmcl_rows_group_field	nvarchar(128)	NULL=NULL			-- 4. Name of group token from nmcl_table
	   ,@nmcl_rows_group_id		int				NULL=NULL		    -- 5. ID of group token from nmcl_table
	   ,@tablename_extra		nvarchar(128)	NULL=NULL			-- 6. Name of relation table for @nmcl_rows_group_field
	   ---------------------------------------------
	   ,@lang					nvarchar(255)	NULL=N'bg'			-- 7. Current language from sys_language table
	   ,@Output_table			nvarchar(128)	NULL=NULL			-- 8. Name of table for results
	   ,@ErrMessage				nvarchar(MAX)	NULL=NULL OUTPUT	-- 9. Text of error/message
	   ,@Return					int				NULL=NULL OUTPUT	-- 10. Return value	(0 - fail 1 - success)
	   ,@SP_MODE				nvarchar(255)	NULL=N'EXECUTE'		-- 11. Execution mode
-- Constraction 
-- Tables		sys_nmcl, all tables containing data of nomenclature
--=========================================================================================================================
AS BEGIN
/* manualUpdateFlag: No */
SET NOCOUNT ON
BEGIN TRY
/*
DECLARE

		@nmcl_name				nvarchar(128)	= N'nmcl_company'		-- 1. Name of nomenclature from sys_nmcl
	   ,@StringToFind			nvarchar(1024)	= N'Kolo'				-- 2. String to find in nomenclature
	   ,@JsonToSearch			nvarchar(MAX)							-- 3. json array with column names where @StringToFind is searched charindex
																		--					=	N'["UnitPriceWithVat","UnitPriceBase"]'					
	   ---------------------------------------------	  	
	   ,@nmcl_rows_group_field	nvarchar(128)	='id_nmcl_CompanyType'	-- 4. Name of group token from nmcl_table
	   ,@nmcl_rows_group_id		int				=7		 			    -- 5. ID of group token from nmcl_table =1
	   ,@tablename_extra		nvarchar(128)	=N''					-- 6. Name of relation table for @nmcl_rows_group_field
	   ---------------------------------------------
	   ,@lang					nvarchar(255)	=N'bg'					-- 7. Current language from sys_language table
	   ,@ErrMessage				nvarchar(MAX)	=NULL					-- 8. Text of error/message
	   ,@Return					int				=NULL					-- 9. Return value	(0 - fail 1 - success)
	   ,@SP_MODE				nvarchar(255)	=N'TEST1'				-- 10. Execution mode
--*/


	DECLARE @nRetValue int=1, @ExitCode int, @Status nvarchar(255) = N'0'
	DECLARE @ParmDefinition nvarchar(MAX), @CommandString nvarchar(MAX)
	DECLARE @SQLstr nvarchar(MAX) = cast(N'' as nvarchar(MAX))
	DECLARE @ProcedureName sysname = ISNULL(OBJECT_NAME(@@PROCID),N'usp_ssERP8_SearchGroupIdInNMCL'), @POS nvarchar(130) = HOST_NAME()

	DECLARE		/*SELECT*/	@SelectField nvarchar(MAX), @SelectField_tc nvarchar(MAX),
				/*UPDATE*/	@UpdateField  nvarchar(MAX), @UpdateField_tc nvarchar(MAX),
				/*INSERT*/	@InsertField_tc_source  nvarchar(MAX), @InsertField_tc_target  nvarchar(MAX)

	DECLARE @tblServiceFields TABLE (  [ServiceField] nvarchar(255)
									  ,[table_name] nvarchar(128)
									  ,[element_name] nvarchar(128)
									  ,[element_lang_name] nvarchar(1024)
									  ,[element_options] nvarchar(MAX)
									  ,[SQL_element_type_sys] nvarchar(128) )
	DECLARE @state_table nvarchar(128)
		SET @ErrMessage = cast(N'' as nvarchar(MAX))

	-- 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
		-- @StringToFind
			IF NULLIF(@StringToFind,N'') is null begin SET @StringToFind=N'';SET @nRetValue=2 end
		--	IF @StringToFind is null begin SET @StringToFind=N'';SET @nRetValue=2 end
		--	ELSE IF @StringToFind = N''

		-- @nmcl_name
			DECLARE @sys_nmcl_id int, @nmcl_table_lang nvarchar(1024)
			IF ISNULL(ISNUMERIC(@nmcl_name),0) = 1	-- that is ID
				SELECT TOP(1)
					   @sys_nmcl_id = n.[id]
					  ,@nmcl_name=n.[tablename]
					  ,@nmcl_table_lang = ISNULL(tr.[value],N'Not translated') 
				FROM [dbo].[sys_nmcl] as n
					LEFT JOIN sys_translations as tr ON tr.[label] = n.[label] AND tr.[device]=N'web' AND tr.[filename]=N'nomenclature' AND tr.[locale] = @lang
				WHERE n.[id] = cast(@nmcl_name as int) AND tr.[locale] = @lang 
			ELSE /*@nmcl_name is NON numeric value*/
				SELECT TOP(1)
					   @sys_nmcl_id = n.[id]
					  ,@nmcl_name=n.[tablename]
					  ,@nmcl_table_lang = ISNULL(tr.[value],N'Not translated') 
				FROM [dbo].[sys_nmcl] as n
					LEFT JOIN sys_translations as tr ON tr.[label] = n.[label] AND tr.[device]=N'web' AND tr.[filename]=N'nomenclature' AND tr.[locale] = @lang
				WHERE n.[tablename] COLLATE database_default = @nmcl_name COLLATE database_default
			IF	NULLIF(@nmcl_name,N'') is null begin SET @ErrMessage += N';Missing nomenclature id parameter '+cast(@sys_nmcl_id as nvarchar(20)); SET @nRetValue = 0 end
		-- @lang
			DECLARE @lang_id int = (SELECT [id] FROM sys_languages WHERE [language]=@lang AND [status]=1)
			IF @lang_id is null 
				begin SET @ErrMessage += N';Missing language parameter'; SET @nRetValue = 0 end
		-- @JsonToSearch
			DECLARE @FieldsToSearch TABLE ( FieldnameToSearch nvarchar(128)) 
			IF ISJSON(@JsonToSearch) = 1
				INSERT INTO @FieldsToSearch ( FieldnameToSearch )
				SELECT [value] as Fieldname FROM OPENJSON(@JsonToSearch)
		-- @tablename_extra
			IF OBJECT_ID(@tablename_extra, N'U') is null SET @tablename_extra = null
			DECLARE @sys_tablename_extra_id int = (	SELECT TOP(1) n.[id] FROM [dbo].[sys_nmcl] as n WHERE n.[tablename] = @tablename_extra )
					IF @sys_tablename_extra_id is not null
						begin -- declare 
						 IF exists (select 1 from @FieldsToSearch )
							INSERT INTO @tblServiceFields ( [ServiceField], [table_name], [element_name], [element_lang_name], [element_options], [SQL_element_type_sys] )
							SELECT N'['+f.[objectable_table_name]+N'].['+element_name+N']', f.[objectable_table_name], f.[element_name], f.[element_lang_name], f.[element_options], f.[SQL_element_type_sys]
							FROM dbo.ufn_ssERP8_CollectObjects(1,@sys_tablename_extra_id,null,null,null,null,@lang) as f
								JOIN @FieldsToSearch as FieldsToSearch ON FieldsToSearch.FieldnameToSearch COLLATE Database_default = f.[element_name] COLLATE Database_default
							WHERE f.[sys_state_element_exists] LIKE N'1_1_' /*and [section_type] IN (3,4,5)*/ -- select * from dbo.ufn_ssCollectNomenclElements2(17, 'bg')
						 ELSE /*not exists (select 1 from @FieldsToSearch )*/
							INSERT INTO @tblServiceFields ( [ServiceField], [table_name], [element_name], [element_lang_name], [element_options], [SQL_element_type_sys] )
							SELECT N'['+f.[objectable_table_name]+N'].['+element_name+N']', f.[objectable_table_name], f.[element_name], [element_lang_name], f.[element_options], [SQL_element_type_sys]
							FROM dbo.ufn_ssERP8_CollectObjects(1,@sys_tablename_extra_id,null,null,null,null,@lang) as f
							WHERE f.[sys_state_element_exists] LIKE N'1_1_' /*and [section_type] IN (3,4,5)*/ -- select * from dbo.ufn_ssCollectNomenclElements2(17, 'bg')
						end
		DECLARE @GroupFieldTable nvarchar(128) = case when @tablename_extra is not null then @tablename_extra else @nmcl_name end

		-- @nmcl_rows_group_field in @tablename_extra AND @nmcl_rows_group_id
			-- ако @nmcl_rows_group_field ИЛИ @nmcl_rows_group_id е NULL => и двете стават NULL
				IF NULLIF(@nmcl_rows_group_id,0) is null		begin SET @nmcl_rows_group_id=null; SET @nmcl_rows_group_field=null end
				IF @nmcl_rows_group_field is null				begin SET @nmcl_rows_group_id=null end				
				 
			-- ако и двете не са NULL
			IF @nmcl_rows_group_field is not null AND @tablename_extra is not null	begin	-- ако полето НЕ е от номенклатурната таблица
				IF COL_LENGTH( @tablename_extra, @nmcl_rows_group_field ) is null	
					begin SET @ErrMessage += N';Invalid grouping parameter '+ISNULL(@nmcl_rows_group_field,N'null')+N' in table '+ISNULL(@tablename_extra,N'null'); SET @nRetValue = 0 end
																					end
			ELSE IF @nmcl_rows_group_field is not null AND @tablename_extra is null	begin	-- ако полето Е от номенклатурната таблица
				IF COL_LENGTH( @nmcl_name, @nmcl_rows_group_field ) is null			
					begin SET @ErrMessage += N';Invalid grouping parameter '+ISNULL(@nmcl_rows_group_field,N'null')+N' in table '+ISNULL(@nmcl_name,N'null'); SET @nRetValue = 0 end
																					end
		-- required procedures and functions
			IF NOT EXISTS (select 1 from sys.objects where [name] = N'ufn_ssERP8_CollectObjects' )
				begin SET @ErrMessage += N';Missing function dbo.ufn_ssERP8_CollectObjects'; SET @nRetValue = 0 end
			IF NOT EXISTS (select 1 from sys.objects where [name] = N'ufn_ssERP8_StringToWord' )
				begin SET @ErrMessage += N';Missing function dbo.ufn_ssERP8_StringToWord'; SET @nRetValue = 0 end
	

	-- 2. Logging the data for analysis / Запазване на данни за анализ
		SET @Status += ',2'
		DECLARE @TestSaveParamID int = 0
	IF OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam') is not null AND LEFT(@SP_MODE,4) = N'TEST'	begin
		DECLARE @PrmString varchar(7168) =	ISNULL(cast(@nmcl_name as varchar(128)),'null')+','				-- 1. Name of nomenclature from sys_nmcl
										 +	ISNULL(cast(@StringToFind as varchar(1024)),'null')+','			-- 2. String to find in nomenclature
										 +	ISNULL(cast(@JsonToSearch as varchar(MAX)),'null')+','			-- 3. json array with column names where @StringToFind is searched charindex
										 +	ISNULL(cast(@nmcl_rows_group_field as varchar(MAX)),'null')+','	-- 4. Name of group token from nmcl_table
										 +	ISNULL(cast(@nmcl_rows_group_id as varchar(10)),'null')+','		-- 5. ID of group token from nmcl_table
										 +	ISNULL(cast(@tablename_extra as varchar(128)),'null')+','		-- 6. Name of relation table for @nmcl_rows_group_field
										 +	ISNULL(cast(@lang as varchar(255)),'null')+','					-- 7. Current language from sys_language table

		DECLARE @DBName nvarchar(130) = DB_NAME()
		DECLARE @PrmJson nvarchar(MAX) = ( SELECT  @nmcl_name				 as '@nmcl_name'				-- 1. Name of nomenclature from sys_nmcl
												  ,@StringToFind			 as '@StringToFind'				-- 2. String to find in nomenclature
												  ,@JsonToSearch			 as '@JsonToSearch'				-- 3. json array with column names where @StringToFind is searched charindex
												  ,@nmcl_rows_group_field	 as '@nmcl_rows_group_field'	-- 4. Name of group token from nmcl_table
												  ,@nmcl_rows_group_id		 as '@nmcl_rows_group_id'		-- 5. ID of group token from nmcl_table
												  ,@tablename_extra			 as '@tablename_extra'			-- 6. Name of relation table for @nmcl_rows_group_field
												  ,@lang					 as '@lang'						-- 7. Current language from sys_language table
												  ,@ErrMessage				 as '@ErrMessage'				-- 8. Text of error/message
												  ,@Return					 as '@Return'					-- 9. Return value	(0 - fail 1 - success)
												  ,@SP_MODE					 as '@SP_MODE'					-- 10. Execution mode
											FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )
		 EXEC [stepsoft].[dbo].[usp_ssTestSaveParam] @PrmMessage = @PrmString, @CallingModule = @ProcedureName, @XMLString = @JsonToSearch
													,@Separator = '~', @DataBase = @DBName, @RecordID = @TestSaveParamID OUTPUT
													,@PrmJson = @PrmJson
																									end	-- IF OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam') is not null AND LEFT(@SP_MODE,4) = N'TEST' 	
	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],'Parameters' as tbl
								  ,@nmcl_name as '@nmcl_name', @sys_nmcl_id as '@sys_nmcl_id'
								  ,@StringToFind as '@StringToFind', @JsonToSearch as '@JsonWithColumnsToSearch'
								  ,@nmcl_rows_group_field as '@nmcl_rows_group_field', @nmcl_rows_group_id as '@nmcl_rows_group_id'
								  ,@tablename_extra as '@tablename_extra', @sys_tablename_extra_id as '@sys_tablename_extra_id'
								  ,@lang as '@lang', @lang_id as '@lang_id'
								  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage', @SP_MODE as '@SP_MODE'

	--------------------------------------------------------------------------------------------------------------------------------------------------
	-- 3. Load elements metadata / Зареждане на метаданни за елементите
	/*		0 document title
			1 document row
			2 report
			3 nomenclature row
			4 dynamic row
			5 nomenclature title	
			6 map row */
		DECLARE @existsTblFieldsToSearch nvarchar(128)
			SET @Status += ',3'
			DROP TABLE IF EXISTS #NmclElements
				SELECT TOP(0) * INTO #NmclElements FROM dbo.ufn_ssERP8_CollectObjects(1,@sys_nmcl_id,null,null,null,null,@lang)
	IF @nRetValue > 0	BEGIN
	-- select * from dbo.ufn_ssERP8_CollectObjects(1,23,null,null,null,null,'bg') WHERE sys_state_element_exists LIKE N'1_1_'	-- select * from sys_nmcl
		IF exists (select 1 from @FieldsToSearch)	begin
			INSERT INTO #NmclElements
			-- declare @sys_nmcl_id int = 23, @lang nvarchar(255)= N'bg'; declare @FieldsToSearch TABLE ( FieldnameToSearch nvarchar(128)); insert into @FieldsToSearch values ('UnitPriceWithVat'),('UnitPriceBase')
			SELECT f.*
			FROM dbo.ufn_ssERP8_CollectObjects(1,@sys_nmcl_id,null,null,null,null,@lang) as f
				JOIN @FieldsToSearch as [FieldsToSearch]	ON [FieldsToSearch].[FieldnameToSearch] COLLATE database_default = f.[element_name] COLLATE database_default
			WHERE f.[sys_state_element_exists] LIKE N'1_1_' /*and [section_type] IN (3,4,5)*/ -- select * from dbo.ufn_ssCollectNomenclElements2(17, 'bg')
			SET @existsTblFieldsToSearch = N'with @FieldsToSearch'
													end
		ELSE -- not exists ( select 1 from @FieldsToSearch)	
													begin
			INSERT INTO #NmclElements		
			SELECT f.*
			FROM dbo.ufn_ssERP8_CollectObjects(1,@sys_nmcl_id,null,null,null,null,@lang) as f
			WHERE f.[sys_state_element_exists] LIKE N'1_1_' /*and [section_type] IN (3,4,5)*/ -- select * from dbo.ufn_ssCollectNomenclElements2(17, 'bg')
			SET @existsTblFieldsToSearch = N'without @FieldsToSearch'
													end		
						END	-- IF @nRetValue = 1

	-- set @state_table
	IF EXISTS (select 1 from #NmclElements ) 
				SET @state_table = ( select top(1) [state_table_name] from #NmclElements where [section_type]=3 AND [rm_element_translatable]=0 )
	ELSE begin	SET @ErrMessage += N'<<1>> Missing settings of nomenclature '+ISNULL(@nmcl_name,N'null')+N' OR invalid search columns'; SET @nRetValue = 0 end
	-- transit
	IF @nRetValue=2	begin SET @ErrMessage += N'Nothing to search' end

	IF @SP_MODE = N'TEST1' begin
		IF EXISTS ( select 1 from #NmclElements)
				 select	 @ProcedureName+N'('+@Status+N')' as  [procedure],N'Settings of nomenclature '+ISNULL(@nmcl_name,N'null')
									   +N' in #NmclElements '+ISNULL(@existsTblFieldsToSearch,N'null') as tbl
					    ,* from #NmclElements	
			ELSE select  @ProcedureName+N'('+@Status+N')' as  [procedure],N'Missing settings of nomenclature '+ISNULL(@nmcl_name,N'null') as tbl
						,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'

		IF exists ( select 1 from @FieldsToSearch)		select  @ProcedureName+N'('+@Status+N')' as  [procedure],'@FieldsToSearch' as tbl, * from @FieldsToSearch

		IF @nRetValue = 2	select  @ProcedureName+N'('+@Status+N')' as  [procedure],N'Nothing to search' as tbl, @nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
						   end	-- IF @SP_MODE = N'TEST1' 

	--------------------------------------------------------------------------------------------------------------------------------------------------
	-- 4. Create JOIN add string / Създаване на допълнителен JOIN string
			SET @Status += ',4'
		DECLARE @tblJoinString TABLE ( [table_name] nvarchar(128), [element_name] nvarchar(128), [element_options] nvarchar(MAX) )
		DECLARE @JoinString_add nvarchar(MAX) = cast(N'' as nvarchar(MAX))
	IF @nRetValue = 1	begin
		INSERT INTO @tblJoinString ( [table_name], [element_name], [element_options])
		SELECT [objectable_table_name],[element_name], [element_options]
		FROM #NmclElements
		WHERE element_type_8v=N'select' OR element_type_8v=N'autocomplete'
		  AND [element_options] is not null AND [element_name] is not null
		IF exists (select 1 from @tblServiceFields)
		INSERT INTO @tblJoinString ( [table_name], [element_name], [element_options])
		SELECT [table_name], [element_name], [element_options] FROM @tblServiceFields where NULLIF([element_options],N'') is not null
		-- създаване на допълнителен join стринг
		SELECT @JoinString_add += cast(N'
	LEFT JOIN ('
		+REPLACE([element_options]
				,N':langId:', CAST(@lang_id as nvarchar(20)))
		+N') as SQL_'+[element_name]+N' ON SQL_'+[element_name]+N'.[id]='+[table_name]+N'.'+[element_name]+N'
'										as nvarchar(MAX))		
		FROM @tblJoinString

						end	-- IF @nRetValue = 1	
	IF @SP_MODE = N'TEST1' AND exists (select 1 from @tblJoinString) begin
		select  @ProcedureName+N'('+@Status+N')' as  [procedure],'table @tblJoinString' as tbl, * from @tblJoinString
		select  @ProcedureName+N'('+@Status+N')' as  [procedure],'@JoinString_add' as tbl, @JoinString_add as '@JoinString additional'
																	 end	
	ELSE IF @SP_MODE = N'TEST1'										 select  @ProcedureName+N'('+@Status+N')' as  [procedure],'No rows in table @tblJoinString' as tbl
									 		

	--------------------------------------------------------------------------------------------------------------------------------------------------
	-- 5. Create WHERE string / Създаване на WHERE string
			SET @Status += ',5'
		DECLARE @WhereString nvarchar(MAX) = cast(N'' as nvarchar(MAX))
		DECLARE @WhereString_add nvarchar(MAX) = cast(N'' as nvarchar(MAX))
		DECLARE @ServiceDelimiter nchar(1) = N'■' -- ALT+254:declare @j int = 0; while @j<256 begin select @j,CHAR(@j); SET @j+=1 end charindex
	IF @nRetValue = 1	begin
		SELECT @WhereString += cast(	-- select system_type_id,name from sys.types
									case 
										when cf.[table_name] is not null AND cf.[element_name] is not null
											then N'ISNULL([SQL_'+cf.[element_name]+N'].[name],N'''')'
										when md.[SQL_element_type_sys] IN ( N'date',N'time',N'datetime2',N'datetimeoffset',N'tinyint',N'smallint',N'int',N'smalldatetime',N'real',N'money',N'datetime',N'float',
																  N'decimal',N'numeric',N'smallmoney',N'bigint',N'timestamp' )		
											then N'ISNULL(cast(['+[objectable_table_name]+N'].['+md.[element_name]+N'] as nvarchar(255)),N'''')'
										when md.[SQL_element_type_sys] IN ( N'xml',N'text',N'uniqueidentifier',N'ntext',N'sql_variant',N'xml' )		
											then N'cast([ISNULL('+[objectable_table_name]+N'].['+md.[element_name]+N'] as nvarchar(MAX)),N'''')'
										else	 N'ISNULL(cast(['+[objectable_table_name]+N'].['+ md.[element_name]+N'] as nvarchar(MAX)),N'''')'
									end +N'/*'
									+ISNULL(md.[element_lang_name],N'')+N'*/'+N'+N'''+@ServiceDelimiter+'''+
'
										as nvarchar(MAX))
		FROM #NmclElements as md
			LEFT JOIN @tblJoinString as cf ON cf.[table_name] COLLATE database_default=md.[objectable_table_name] COLLATE database_default
										  AND cf.[element_name] COLLATE database_default = md.[element_name] COLLATE database_default

		IF EXISTS ( select 1 from @tblServiceFields )	BEGIN -- полетата от @tablename_extra
					SELECT @WhereString_add += cast(	-- select system_type_id,name from sys.types
												case 
													when cf.[table_name] is not null AND cf.[element_name] is not null
														then N'ISNULL([SQL_'+cf.[element_name]+N'].[name],N'''')'
													when sf.[SQL_element_type_sys] IN ( N'date',N'time',N'datetime2',N'datetimeoffset',N'tinyint',N'smallint',N'int',N'smalldatetime',N'real',N'money',N'datetime',N'float',
																			  N'decimal',N'numeric',N'smallmoney',N'bigint',N'timestamp' )		
														then N'ISNULL(cast('+sf.[ServiceField]+N' as nvarchar(255)),N'''')'
													when sf.[SQL_element_type_sys] IN ( N'xml',N'text',N'uniqueidentifier',N'ntext',N'sql_variant',N'xml' )		
														then N'ISNULL(cast('+sf.[ServiceField]+N' as nvarchar(MAX)),N'''')'
													else	 N'ISNULL(cast('+sf.[ServiceField]+N' as nvarchar(MAX)),N'''')'
												end 
												+ N'/*'+ISNULL(sf.[element_lang_name],N'')+N'*/'+N'+N'''+@ServiceDelimiter+'''+
			'
													as nvarchar(MAX))
					FROM @tblServiceFields as sf
						LEFT JOIN @tblJoinString as cf ON cf.[table_name] COLLATE database_default= sf.[table_name] COLLATE database_default
													  AND cf.[element_name] COLLATE database_default = sf.[element_name] COLLATE database_default
														END
			SET @WhereString += ISNULL(@WhereString_add, cast(N'' as nvarchar(MAX)))+cast(N'N'''' LIKE N''%''+wrd.[Word]+''%''' as nvarchar(MAX)) -- за завършване след последната запетая и LIKE
			SET @WhereString = case when @StringToFind=N'' then N'' else @WhereString end
							 + case when NULLIF(@StringToFind,N'') is not null AND @nmcl_rows_group_id is not null then N' AND ' else N'' end
							 + case when @nmcl_rows_group_id is not null 
										then N' ['+ISNULL(@GroupFieldTable,N'null')+N'].['+ISNULL(@nmcl_rows_group_field,N'null') /*Name of group token from nmcl_table*/
											+N'] = '+ISNULL(CAST(@nmcl_rows_group_id as nvarchar(20)),N'null') /*ID of group token from nmcl_table*/	
										else N'' end
			
						end	-- IF @nRetValue = 1

	IF @SP_MODE = N'TEST1'	 select  @ProcedureName+N'('+@Status+N')' as  [procedure],'WHERE expressionToSearch LIKE %@StringToFind%' as tbl
									,@WhereString_add as '@WhereString_add'
									,@WhereString as '@WhereString', @nRetValue as '@nRetValue'


	--------------------------------------------------------------------------------------------------------------------------------------------------
	-- 6. Create ROOT query / Създаване на ROOT query
			SET @Status += ',6'
		DECLARE @RootQuery nvarchar(MAX)
		DROP TABLE IF EXISTS #Result
		CREATE TABLE #Result ( [rows_group] nvarchar(255), [id_record] int)
	IF @nRetValue = 1	begin
		SET @RootQuery = CAST(
	 N'/* Search for '+N'"'+ISNULL(cast(@StringToFind as nvarchar(1024)),N'null')+N'" in '
   + case when @nmcl_rows_group_field is null then N' all groups' else ISNULL(cast(@nmcl_rows_group_id as nvarchar(20)),N'null')+N' group' end
   + N'*/'						as nvarchar(MAX))
   +CAST(N'/*table #Result defininion */
INSERT INTO #Result ([rows_group], [id_record])
SELECT DISTINCT '
	+case when @nmcl_rows_group_field is not null then N'['+ISNULL(@GroupFieldTable,N'null')+N'].['+ISNULL(@nmcl_rows_group_field,N'null')+N'] as rows_group,' 
		  else N'0 as rows_group,' 
	  end 
	+ N'['+ISNULL(@nmcl_name,N'null')+N'].[ID] as id_record
FROM [dbo].['+@nmcl_name+N']
	'+case when @StringToFind=N'' then N'' else N'CROSS APPLY [dbo].[ufn_ssERP8_StringToWord] ( N'''+@StringToFind+N''',null) as wrd' end + N'
	'+case when OBJECT_ID('[dbo].['+@nmcl_name+N'_translations]') is not null 
		then N'left join [dbo].['+ISNULL(@nmcl_name,N'null')+N'_translations] ON ['+ISNULL(@nmcl_name,N'null')+N'].[id]=['+ISNULL(@nmcl_name,N'null')+N'_translations].[id_'+ISNULL(@nmcl_name,N'null')+N']' /*AND ['+ISNULL(@nmcl_name,N'null')+N'_translations].[lang]=@lang_id'*/ else N'' end +N'
	'+case when @tablename_extra is not null 
		then N'join [dbo].['+ISNULL(@tablename_extra,N'null')+N'] ON ['+ISNULL(@tablename_extra,N'null')+N'].[id]=['+ISNULL(@nmcl_name,N'null')+N'].[id_'+ISNULL(@tablename_extra,N'null')+N']' else N'' end +N'
	'+case when OBJECT_ID('[dbo].['+@tablename_extra+N'_translations]') is not null
		then N'left join [dbo].['+ISNULL(@tablename_extra,N'null')+N'_translations] ON ['+ISNULL(@tablename_extra,N'null')+N'].[id]=['+ISNULL(@tablename_extra,N'null')+N'_translations].[id_'+ISNULL(@tablename_extra,N'null')+N']' /*AND ['+ISNULL(@tablename_extra,N'null')+N'_translations].[lang]=@lang_id'*/ else N'' end +N'
	'+@JoinString_add as nvarchar(MAX))
   +CAST( N'
WHERE '+ISNULL(@WhereString,N'null')+N'
  '			as nvarchar(MAX))
						end	-- IF @nRetValue = 1
	IF @SP_MODE = N'TEST1'	 select  @ProcedureName+N'('+@Status+N')' as  [procedure],'LAST Query' as tbl
									,REPLACE(@RootQuery,N'/*table #Result defininion */'
													  ,case when @SP_MODE=N'TEST1' then  CAST(N'
CREATE TABLE #Result ( [rows_group] nvarchar(255), [id_record] int)
DECLARE @lang_id int='+CAST(@lang_id as nvarchar(20)) as nvarchar(MAX))
															else cast(N'' as nvarchar(MAX)) end						
											)	as 'FinalQuery'	
									,@StringToFind as '@StringToFind', @nmcl_name as '@nmcl_name', @tablename_extra as '@tablename_extra', @WhereString as '@WhereString'
									,@GroupFieldTable as '@GroupFieldTable', @nmcl_rows_group_field as '@nmcl_rows_group_field', @state_table as '@state_table'
									,@ErrMessage as '@ErrMessage', @nRetValue as '@nRetValue'
	--------------------------------------------------------------------------------------------------------------------------------------------------
	-- 7. Result
			SET @Status += ',7'
		DECLARE @Output_json nvarchar(MAX), @Number_of_results int
print 'proba'
	IF @nRetValue = 1	BEGIN
		SET @ParmDefinition = N'@lang_id int'
		EXEC @ExitCode = sp_executeSQL @RootQuery,@ParmDefinition,@lang_id=@lang_id
		IF @ExitCode > 0 begin SET @ErrMessage += N'Error when searching LAST query: '+cast(@ExitCode as nvarchar(20)); SET @nRetValue=0 end
			SET @Number_of_results = ISNULL(( SELECT COUNT(*) FROM #Result ),0)
			IF @SP_MODE=N'TEST1'   select @ProcedureName+N'('+@Status+N')' as  [procedure],'Number of result' as tbl, @Number_of_results as [TotalResult]

		IF @Output_table is not null	begin
print 'proba1'
			SET @SQLstr = N'INSERT INTO '+@Output_table+N' ([rows_group], [id_record]) SELECT [rows_group], [id_record] FROM #Result'
			IF @SP_MODE=N'TEST1'   select @ProcedureName+N'('+@Status+N')' as  [procedure],'Table '+@Output_table as tbl, @SQLstr as '@SQLstr'
				EXEC (@SQLstr)
										end
		ELSE
print 'proba2'
					SET @Output_json =	(
							SELECT   @StringToFind as [StringToSearch]
									,@Number_of_results as [TotalResult]
									,ISNULL((	SELECT   ISNULL([rows_group],0) as [rows_group]
														,[id_record]			as [id_record]
												FROM #Result
													ORDER BY [id_record]
												FOR JSON PATH, INCLUDE_NULL_VALUES	)
											,N'"nothing found"') as [Result]
							FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
										)

						END	-- IF @nRetValue = 1

END TRY
BEGIN CATCH																																																	
-- Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.																																	
		-- close opened transactions
		WHILE @@TRANCOUNT > 0 ROLLBACK TRANSACTION
		-- close opened cursors
			DECLARE @destroyed_cursor_cmd nvarchar(MAX) = N''
			SELECT @destroyed_cursor_cmd += case when [is_open] = 1 then N' CLOSE '+[name]+N';' else N'' end  +  N' DEALLOCATE '+[name]+N';' FROM sys.dm_exec_cursors(@@SPID)
			print @destroyed_cursor_cmd; exec (@destroyed_cursor_cmd)		

		DECLARE @ERROR_PROCEDURE nvarchar(130) = 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 SET @nRetValue=1
	SET @Return = @nRetValue
	DECLARE @Error TABLE ( [Error] nvarchar(2048) ); INSERT INTO @Error SELECT cast([Value] as nvarchar(1024)) as Error FROM string_split(@ErrMessage,';')	WHERE ISNULL([Value],N'') <> N'' 
	SET @ErrMessage = (SELECT * FROM @Error FOR JSON PATH , ROOT('Errors'))
		IF @Output_table is null SELECT @Return as [Return], @ErrMessage as [Error_json], @Output_json as [Output_json]

RETURN @nRetValue
END