SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS [dbo].[usp_ssERP8_TransferDataNMCLState]
GO
/*	-- select * from sys_nmcl -- select * from nmcl_price
EXEC [dbo].[usp_ssERP8_TransferDataNMCLState] 
											 @sys_nmcl_id = '23'
											,@lang = 'bg'
											,@internal_number = 6
											,@id_user = 19
											,@nmcl_rows_group_field = 'id_nmcl_stuff'
											--,@nmcl_rows_group_id = 1
											,@StringToFind = N'140'
											,@JsonToSearch = N'["UnitPriceWithVat","UnitPriceBase"]'
											,@SP_MODE=N'TEST1'
--*/

-- =========================================================================================================================
CREATE PROCEDURE 
-- Name			
				[dbo].[usp_ssERP8_TransferDataNMCLState]
-- Author		Щерю Щерев																									
-- Create date	22/06/2023
-- Version		v.3 21/7/2023
--				v.4 19/8/2023
--				v.5 21/8/2023
--				v.6 10/12/2023
--				v.7 07/03/2024 - flag for reloading data in state from nmcl
--				v.11 9/01/2025 - AFRTER use is_deleted flag in state
--
-- Serever		MS SQL 2016																										
-- Description	Execute SQL script transfer data between NMCL and State tables
-- Returns		0 - fail 1 - success
-- Parameters
	    @sys_nmcl_id			nvarchar(128)						-- 1. ID OR name of nomenclature in sys_nmcl table
	   ,@lang					nvarchar(255)	NULL=N'bg'			-- 2. Current language from sys_language table
	   ,@id_user				int				NULL=0				-- 3. Current user from sys_users table
	   ---------------------------------------------
	   ,@internal_number		int									-- 4. Value of internal_number for state tables 
	   ,@nmcl_rows_group_field	nvarchar(130)	NULL=NULL		    -- 5. Name of group token from nmcl_table 
	   ,@nmcl_rows_group_id		int				NULL=NULL		    -- 6. ID of group token from nmcl_table

	   ,@replace_data_state		int				NULL=1			    -- 7. Flag for replace data in state table (=0 not replace/skip, =1 replace)			
	   ,@tablename_extra		nvarchar(128)	NULL=NULL			-- 8. Name of relation table for @nmcl_rows_group_field
	   ,@StringToFind			nvarchar(1024)	NULL=NULL			-- 9. String to find in nomenclature
	   ,@JsonToSearch			nvarchar(MAX)	NULL=NULL			-- 10. Json array with COLUMN NAMES where @StringToFind is searched charindex
																	--					=	N'["UnitPriceWithVat","UnitPriceBase"]'					
	   ---------------------------------------------
	   ,@ErrMessage				nvarchar(MAX)	NULL=NULL OUTPUT	-- 11. Text of error/message
	   ,@Output_json			nvarchar(MAX)	NULL=NULL OUTPUT	-- 12. Result info
	   ,@Return					int				NULL=NULL OUTPUT	-- 13. Return value	(0 - fail 1 - success)
	   ,@SP_MODE				nvarchar(255)	NULL=N'EXECUTE'		-- 14. Operation mode
-- Constraction 
-- Tables		sys_nmcl, all tables containing data of nomenclature
--=========================================================================================================================
AS BEGIN
/* manualUpdateFlag: No */
SET NOCOUNT ON
BEGIN TRY
/*	
DECLARE		  @sys_nmcl_id				nvarchar(128)	=23				-- 1. ID OR name of nomenclature in sys_nmcl table
			 ,@lang						nvarchar(255)	=N'bg'			-- 2. Current language from sys_language table
			 ,@id_user					int				=19				-- 3. Current user from sys_users table
			 -------------------------------------------------------------
			 ,@internal_number			int				=6				-- 4. Value of internal_number for state tables 
			 ,@nmcl_rows_group_field	nvarchar(130)	=NULL		    -- 5. Name of group token from nmcl_table 
			 ,@nmcl_rows_group_id		int				=NULL		    -- 6. ID of group token from nmcl_table
			 ,@replace_data_state		int				=0			    -- 7. Flag for replace data in state table (=0 not replace/skip, =1 replace)			
			 ,@tablename_extra			nvarchar(128)	='nmcl_stuff'	-- 8. Name of relation table for @nmcl_rows_group_field
			 ---------------------------------------------
			 ,@ErrMessage				nvarchar(MAX)	=NULL			-- 10. Text of error/message
			 ,@Return					int				=NULL			-- 11. Return value	(0 - fail 1 - success)
			 ,@SP_MODE					nvarchar(255)	=N'TEST1'		-- 12. Operation mode
--*/


	DECLARE @nRetValue int=1, @ExitCode int, @Status nvarchar(2048) = 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_TransferDataNMCLState'), @POS nvarchar(130) = HOST_NAME()

	DECLARE			 @nmcl_table_ext nvarchar(128), @grid_state_table nvarchar(128), @state_table nvarchar(128)
					,@section_id int, @section_type int, @Section_UID nvarchar(255), @order int
					,@PartitionStr nvarchar(1024), @comment nvarchar(512)
	/*INSERT*/		,@InsertString nvarchar(MAX)
	/*SELECT*/		,@SelectString nvarchar(MAX),	@WhereString nvarchar(MAX)
	/*JOIN*/		,@JoinString nvarchar(MAX)
	/*SERVICE*/		,@ServiceField nvarchar(MAX),	@ServiceValue nvarchar(MAX)
	DECLARE @tblServiceFields TABLE ( [ServiceField] nvarchar(128), [ServiceValue] nvarchar(MAX) )
	DECLARE @sys_updated_field bit = 0
	DECLARE @RetResult bit = case when @ErrMessage = N'usp_ssERP8_SearchGroupIdInNMCL' then 0 else 1 end
	DECLARE @InfoStr nvarchar(MAX)
		SET @ErrMessage = N''

	-- 1. Parameters sniffing
		SET @Status += ',1'
		--	SET @SP_MODE = case when @SP_MODE is null THEN 'TEST' 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
		-- @sys_nmcl_id -- select * from sys_nmcl
			DECLARE @nmcl_table nvarchar(130), @nmcl_table_lang nvarchar(1024)
			IF ISNULL(ISNUMERIC(@sys_nmcl_id),0) = 1	-- that is ID
				SELECT TOP(1)
					   @sys_nmcl_id = n.[id]
					  ,@nmcl_table=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(@sys_nmcl_id as int) AND tr.[locale] = @lang 
			ELSE /*@sys_nmcl_id is NON numeric value*/
				SELECT TOP(1)
					   @sys_nmcl_id = n.[id]
					  ,@nmcl_table=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 = @sys_nmcl_id COLLATE database_default

			IF	NULLIF(@nmcl_table,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
		-- @internal_number
			IF ISNULL(@internal_number,0) <= 0 begin SET @ErrMessage += N';Missing internal_number parameter'; SET @nRetValue = 0 end
		-- @tablename_extra
			SET @tablename_extra = NULLIF(@tablename_extra,N'')
			IF @tablename_extra is not null 
				IF COL_LENGTH(@nmcl_table,N'id_'+@tablename_extra) is null begin SET @ErrMessage += N';Missing tablename_extra field in nomenclature table'; SET @nRetValue = 0 end	

		-- @nmcl_rows_group_field AND @nmcl_rows_group_id
			IF NULLIF(@nmcl_rows_group_field,N'') is null OR ISNULL(@nmcl_rows_group_id,0)<=0 begin SET @nmcl_rows_group_id=0/*; print 'proba'*/ end
			IF @nmcl_rows_group_field is not null AND
					(
						(		@tablename_extra is not null AND COL_LENGTH(@tablename_extra,	@nmcl_rows_group_field) is null )
					OR	(		@tablename_extra is null	 AND COL_LENGTH(@nmcl_table,		@nmcl_rows_group_field) is null	)
					)
						begin SET @ErrMessage += N';Missing group field in nomenclature table'; SET @nRetValue = 0 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'usp_ssERP8_SearchGroupIdInNMCL' )
				begin SET @ErrMessage += N';Missing procedure dbo.usp_ssERP8_SearchGroupIdInNMCL'; 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(@sys_nmcl_id as varchar(10)),'null')+','				-- 1. ID of nomenclature in sys_nmcl table
												 +	ISNULL(cast(@lang as varchar(10)),'null')+','						-- 2. Current language from sys_language table
												 +	ISNULL(cast(@id_user as varchar(10)),'null')+','					-- 3. Current user from sys_users table
												 +	ISNULL(cast(@internal_number as varchar(10)),'null')+','			-- 4. Value of internal_number for state tables 
												 +	ISNULL(cast(@nmcl_rows_group_field as varchar(10)),'null')+','		-- 5. Name of group token from nmcl_table
												 +	ISNULL(cast(@nmcl_rows_group_id as varchar(10)),'null')+','			-- 6. List of ID's of group token from nmcl_table ( "," delimiter )
												 +	ISNULL(cast(@replace_data_state as varchar(20)),'null')+','			-- 7. Flag for replace data in state table (=0 not replace/skip, =1 replace)			
												 +	ISNULL(cast(@tablename_extra as varchar(10)),'null')+','			-- 8. Name of relation table for @nmcl_rows_group_field
												 +	ISNULL(cast(@StringToFind as varchar(1024)),'null')+','				-- 9. String to find in nomenclature
												 +	ISNULL(cast(@JsonToSearch as varchar(MAX)),'null')+','				-- 10. Json array with COLUMN NAMES where @StringToFind is searched charindex =	N'["UnitPriceWithVat","UnitPriceBase"]'
												 +	ISNULL(@SP_MODE,'null')												-- 14.Execution mode 
				DECLARE @DBName nvarchar(128) = DB_NAME()
				DECLARE @xml_comple nvarchar(MAX) = null
				DECLARE @PrmJson nvarchar(MAX) = (	SELECT	 @sys_nmcl_id			as [@sys_nmcl_id]					-- 1. ID of nomenclature in sys_nmcl table
															,@lang					as [@lang]							-- 2. Current language from sys_language table
															,@id_user				as [@id_user]						-- 3. Current user from sys_users table
															,@internal_number		as [@internal_number]				-- 4. Value of internal_number for state tables 
															,@nmcl_rows_group_field	as [@nmcl_rows_group_field]			-- 5. Name of group token from nmcl_table
															,@nmcl_rows_group_id	as [@nmcl_rows_group_ids]			-- 6. List of ID's of group token from nmcl_table ( "," delimiter )
															,@replace_data_state	as [@replace_data_state]			-- 7. Flag for replace data in state table (=0 not replace/skip, =1 replace)			
															,@tablename_extra		as [@tablename_extra]				-- 8. Name of relation table for @nmcl_rows_group_field
															,@StringToFind			as [@StringToFind]					-- 9. String to find in nomenclature
															,@JsonToSearch			as [@JsonToSearch]					-- 10. Json array with COLUMN NAMES where @StringToFind is searched charindex =	N'["UnitPriceWithVat","UnitPriceBase"]'
															,@ErrMessage		 	as [@ErrMessage]					-- 11. Text of error/message
															,@SP_MODE				as [@SP_MODE]						-- 14. Operation 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 @nRetValue = 1 AND 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))

	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'Input parameters' as tbl
								  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
								  ,@lang as '@lang', @lang_id as '@lang_id', @id_user as '@id_user'
								  ,@sys_nmcl_id as '@sys_nmcl_id', @nmcl_table as '@nmcl_table', @nmcl_table_lang as '@nmcl_table_lang'
								  ,@internal_number as '@internal_number'
								  ,@nmcl_rows_group_field as '@nmcl_rows_group_field', @nmcl_rows_group_id as '@nmcl_rows_group_id'
								  ,@replace_data_state as '@replace_data_state'
								  ,@StringToFind as '@StringToFind', @JsonToSearch as '@JsonToSearch'

	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 3. Loading a table with results from the filter option / Зареждане на таблица с резултати от опцията за филтриране
		-- @StringToFind	nvarchar(1024)	NULL=NULL -- 9. String to find in nomenclature
		-- @JsonToSearch	nvarchar(MAX)	NULL=NULL -- 10. Json array with COLUMN NAMES where @StringToFind is searched charindex
		--											  --					=	N'["UnitPriceWithVat","UnitPriceBase"]'
		SET @Status += ',3'
			--DECLARE @ResultFromSearch_json nvarchar(MAX)
			DROP TABLE IF EXISTS #tblResultFromSearch
				CREATE TABLE #tblResultFromSearch ( [rows_group] int, [id_record] int )
	IF @nRetValue = 1 AND @StringToFind is not null	BEGIN
		DECLARE @prm_ErrMessage nvarchar(MAX)
				exec [dbo].[usp_ssERP8_SearchGroupIdInNMCL]  @nmcl_name				= @nmcl_table
															,@StringToFind			= @StringToFind
															,@JsonToSearch			= @JsonToSearch
															,@nmcl_rows_group_field	= @nmcl_rows_group_field
															,@nmcl_rows_group_id	= @nmcl_rows_group_id
															,@tablename_extra		= @tablename_extra
															,@Output_table			= N'#tblResultFromSearch'
															,@ErrMessage			= @prm_ErrMessage OUTPUT
															,@SP_MODE				= @SP_MODE
			--	SET @ResultFromSearch_json = CAST(N'' as nvarchar(MAX))
				--IF ISNULL(ISJSON(@ResultFromSearch_json),0)=1
				--	INSERT INTO #tblResultFromSearch
				--	SELECT [rows_group], [id_record] FROM openjson(@ResultFromSearch_json,'$.Result') 
				--											with ( rows_group int '$.rows_group', id_record int '$.id_record' )
				IF @prm_ErrMessage is not null SET @ErrMessage += @prm_ErrMessage
				--IF not exists (select 1 from #tblResultFromSearch) SET @nRetValue=2

													END -- IF @nRetValue = 1 AND @StringToFind is not null
	IF @SP_MODE = N'TEST1' AND EXISTS (select 1 from #tblResultFromSearch)
								select  @ProcedureName+N'('+@Status+N')' as  [procedure],'table #tblResultFromSearch' as tbl,* from #tblResultFromSearch
	ELSE IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'No rows in table #tblResultFromSearch' as tbl
									   ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
	
	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 4. Load elements metadata / Зареждане на метаданни за елементите
	/*		0 document title
			1 document row
			2 report
			3 nomenclature row
			4 nomenclature dynamic row
			5 nomenclature title
			6 map row
			select * from dbo.ufn_ssERP8_CollectObjects(	 1		-- 1. ID of object type		
															,1		-- 2. ID of source object	
															,null	-- 3. ID of interface form	
															,null	-- 4. ID of section			
															,null	-- 5. ID of element			
															,null	-- 6. ID of table			
															,'bg'	-- 7. Current language		
														)	--*/
		SET @Status += ',4'
	DECLARE @IsDynamicNomenclature bit = 0, @IsTranslateNomenclature bit = 0, @IsGrouping bit= 0
		IF OBJECT_ID('tempdb..#NmclElements') is not null DROP TABLE #NmclElements
			SELECT *
			INTO #NmclElements
			FROM dbo.ufn_ssERP8_CollectObjects( 1, @sys_nmcl_id, null, null, null, null, @lang) 
		WHERE ([sys_state_element_exists] = N'1111' and [section_type] IN (3,5))
		   OR ([sys_state_element_exists] = N'1100' and [section_type] IN (4))
	--IF @nRetValue = 1	begin
			IF EXISTS (SELECT 1 FROM #NmclElements WHERE [section_type]=4) SET @IsDynamicNomenclature=1			-- 4 dynamic row	
			IF EXISTS (SELECT 1 FROM #NmclElements WHERE [section_type] IN (3,5) AND [rm_element_translatable]=1) SET @IsTranslateNomenclature=1
			
			IF @nmcl_rows_group_field is not null AND ISNULL(@nmcl_rows_group_id,0)>0	SET @IsGrouping=1
				SET @grid_state_table = ( select top(1) REPLACE([state_table_name],N'_translations',N'') from #NmclElements where [section_type]=3 )
			IF COL_LENGTH(@grid_state_table,N'sys_updated_at') is not null SET @sys_updated_field=1
	--					end	-- IF @nRetValue = 1

	IF OBJECT_ID('tempdb..#NmclElements') is null begin SET @ErrMessage += N';Missing nomenclature settings'; SET @nRetValue = 0 end	
	  ELSE IF not exists (select 1 from #NmclElements where [section_type]=3 --AND [rm_element_translatable]=0
						 ) begin SET @ErrMessage += N';Invalid nomenclature settings'; SET @nRetValue = 0 end	

	IF (OBJECT_ID('tempdb..#NmclElements') is null)	AND @SP_MODE = N'TEST1'			select  @ProcedureName+N'('+@Status+N')' as  [procedure],'Missing #NmclElements' as tbl, @ErrMessage as '@ErrMessage'
		ELSE IF not exists (select 1 from #NmclElements ) AND @SP_MODE = N'TEST1'	select  @ProcedureName+N'('+@Status+N')' as  [procedure],'Invalid #NmclElements' as tbl, @ErrMessage as '@ErrMessage'
			ELSE IF @SP_MODE = N'TEST1'												select  @ProcedureName+N'('+@Status+N')' as  [procedure],'table #NmclElements' as tbl,* from #NmclElements
	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'SectionTypes' as tbl, @IsDynamicNomenclature as '@IsDynamicSection', @IsTranslateNomenclature as '@IsTranslateNomenclature'

	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 5. Processing on replace flag / Обработка на флага за замяна
		SET @Status += ',5'
print 'test'
		DECLARE @teststring nvarchar(MAX) = cast(N'(0,0)' as nvarchar(MAX))
		SELECT TOP(100) @teststring +=N',('+cast([rows_group] as nvarchar(20))+N','+cast([id_record] as nvarchar(20))+N')'
							from #tblResultFromSearch 
							where [rows_group] is not null AND [id_record] is not null
	DECLARE @SP_MODE_Query nvarchar(MAX) = 
cast( N'/*Procedure: '+ISNULL(@ProcedureName,N'null')+N' - for test in SSMS omly*/
DECLARE @id_user int='+ISNULL(cast(@id_user as nvarchar(20)),N'null')
			+N', @internal_number int='+ISNULL(cast(@internal_number as nvarchar(20)),N'null')
			+N', @nmcl_rows_group_field nvarchar(128)='''+ISNULL(cast(@nmcl_rows_group_field as nvarchar(128)),N'null')+N''''
			+N', @nmcl_rows_group_id int='+ISNULL(cast(@nmcl_rows_group_id as nvarchar(20)),N'null')
			+N', @lang nvarchar(255)='''+ISNULL(cast(@lang as nvarchar(255)),N'null')+N''''
			+N', @lang_id int='+ISNULL(cast(@lang_id as nvarchar(20)),N'null')
			+N'
	DROP TABLE IF EXISTS #Output
	CREATE TABLE #Output  (id INT,rows_group INT,id_grid_row INT,[internal_number] INT,[id_record] INT )
'	as nvarchar(MAX))

+	case when @StringToFind is not null
			then cast(
N'/*TOP 100 of search results */
DROP TABLE IF EXISTS #tblResultFromSearch; CREATE TABLE #tblResultFromSearch ( [rows_group] int, [id_record] int )'
			+ case when exists (select 1 from #tblResultFromSearch) 
					then 	
						'INSERT INTO #tblResultFromSearch ([rows_group], [id_record]) VALUES '
						+@teststring
					else N'' end
					   as nvarchar(MAX))
			else cast(N'' as nvarchar(MAX)) end	

print 'test1'
IF @SP_MODE = N'TEST1' SET @SQLstr = @SP_MODE_Query
	-- 
	DECLARE @Replace_state_query nvarchar(MAX)
		IF @replace_data_state = 0	-- the data in the state table should not be replaced
			SET @Replace_state_query = cast(N'
	IF NOT EXISTS ( SELECT 1 FROM [dbo].[<state_table>] WHERE [internal_number]=@internal_number '
				+case when @nmcl_rows_group_id is not null then N' AND [rows_group]= @nmcl_rows_group_id' else N'' end
		+')'
											as nvarchar(MAX))
	
		ELSE						-- the data in the state table should be replaced		
			SET @Replace_state_query = cast(N'
	DELETE [dbo].[<state_table>] WHERE [internal_number]=@internal_number'
				+case when @nmcl_rows_group_id is not null  then N' AND [rows_group]= @nmcl_rows_group_id' else N'' end
											as nvarchar(MAX))

	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 6. Create of template scripts for transfer / Създаване на template на скриптовете за трансфер
		SET @Status += ',6'
		DECLARE @GridQuery nvarchar(MAX) = cast(N'
<comment>
DECLARE @Output TABLE (	 [id]				int 
						,[rows_group]		int
						,[id_grid_row]		int	
						,[internal_number]	int
						,[id_record]		int
					  )'+@Replace_state_query+N'
		INSERT INTO [dbo].[<state_table>] 
			(	 <insert_string>
				 <service_fields>
			)
		  OUTPUT  INSERTED.[id],INSERTED.[rows_group],INSERTED.[id_grid_row],INSERTED.[internal_number],INSERTED.[id_record]	INTO @Output
		SELECT	 <select_string>
				 <service_values>
		  FROM	[dbo].[<nmcl_table_ext>]
			<join_string>
			<where_string>
INSERT INTO #Output (id,rows_group,id_grid_row,[internal_number],[id_record]) SELECT id,rows_group,id_grid_row,[internal_number],[id_record] FROM @Output		
'						
								as nvarchar(MAX))
		DECLARE @GridQuery_translation nvarchar(MAX) = cast(N'
<comment>'+@Replace_state_query+N'
		INSERT INTO [dbo].[<state_table>] 
			(	 <insert_string>
				 <service_fields>
			)
		SELECT	 <select_string>
				 <service_values>
		  FROM	[dbo].[<nmcl_table_ext>]
			<join_string>
			<where_string>
'						
								as nvarchar(MAX))
		DECLARE @NogridQuery nvarchar(MAX) = cast(N'
<comment>'+@Replace_state_query+N'
		INSERT INTO [dbo].[<state_table>] 
			(	 <insert_string>
				 <service_fields>
			)
		SELECT	 <select_string>
				 <service_values>
		  FROM	[dbo].[<nmcl_table_ext>]
			<join_string>
			<where_string>
'						
								as nvarchar(MAX))

	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'Template of queries' as tbl
								  ,@GridQuery as '@GridQuery'
								  ,@GridQuery_translation as '@GridQuery_translation'
								  ,@NogridQuery as '@NogridQuery'

-- ===========================================================================================================================================================================
	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 7. Create SCRIPT for transfer / Създаване на СКРИПТ за трансфер
	/* GRID секциите на номенклатурата може да е само ЕДНА, а таблиците ДВЕ ( с преводими и с непреводими елементи )
			3 nomenclature row => данните влизат в state___grid___ И state___grid___translations
			(state_form_79_grid_1432, state_form_79_grid_1432_translations )
	   Останалите секции могат да са:	
			4 nomenclature dynamic row
			5 nomenclature title
			=> Данните влизат в state___ и state___translations
			( state_form_79, state_form_79_translations, state_form_79_dynamic, state_form_79_dynamic_translations) 
	*/
		SET @Status += ',7'
		DECLARE @tblCursor TABLE ( [id] int identity, [objectable_table_name] nvarchar(128), [state_table_name] nvarchar(128), [IsTranslateNomenclature] bit, [section_type] int, [object_root] int, [order] int )
		INSERT INTO @tblCursor ( [objectable_table_name], [state_table_name], [IsTranslateNomenclature], [section_type], [object_root], [order] )
		SELECT DISTINCT [objectable_table_name]
					   ,[state_table_name]
					   ,[rm_element_translatable]
					   ,[section_type]
					   ,[object_root]
					   ,case when [rm_element_translatable]=1 then 2 else 1 end
		FROM #NmclElements 
		WHERE [objectable_table_name] is not null AND [state_table_name] is not null
		IF not exists (select 1 from @tblCursor where [objectable_table_name]=@nmcl_table AND [state_table_name]=@grid_state_table)
			INSERT INTO @tblCursor ([objectable_table_name], [state_table_name], [IsTranslateNomenclature], [section_type], [object_root] )
			VALUES (@nmcl_table, @grid_state_table, 0, 3, 0)
		UPDATE tblCursor
			SET [Order] = tmp.[order]
		FROM ( select [id], ROW_NUMBER() OVER ( ORDER BY [section_type],[order]) as [order] from @tblCursor ) as tmp
				JOIN @tblCursor as tblCursor ON tblCursor.id=tmp.id
			
			IF @SP_MODE = N'TEST1' 
			begin
				select  DISTINCT  
								 @ProcedureName+N'('+@Status+N')' as  [procedure],'Pair nmcl->state' as tbl
								,[objectable_table_name] as '@nmcl_table_ext'
								,[state_table_name] as '@state_table'
								,[section_type] as '@section_type'
								,[section_id],[rm_element_translatable]
				from #NmclElements 
				--where [objectable_table_name] is not null AND [state_table_name] is not null
				if exists (select 1 from @tblCursor )
						select  @ProcedureName+N'('+@Status+N')' as  [procedure],'table @tblCursor' as tbl, * from @tblCursor order by [order]
				else	select  @ProcedureName+N'('+@Status+N')' as  [procedure],'table @tblCursor => NO ROWS' as tbl
			end
				
			ELSE IF	@SP_MODE = N'TEST1' AND not exists (	select 1 from #NmclElements where [objectable_table_name] is not null 
																						  AND [state_table_name] is not null 
																						  AND [section_type]=3
																						  --AND [rm_element_translatable]=0
													   )
					begin 
						  select @ProcedureName+N'('+@Status+N')' as  [procedure],'NO Pair nmcl->GRID state' as tbl
						  SET @ErrMessage += N'Missing grid section on nomenclature '+ISNULL(@nmcl_table,N'null')
						  SET @nRetValue = 0
					end
				 ELSE IF not exists (	select 1 from #NmclElements where [objectable_table_name] is not null 
																						  AND [state_table_name] is not null 
																						  AND [section_type]=3
																						  --AND [rm_element_translatable]=0
													   )
					begin 
						  SET @ErrMessage += N'Missing grid section on nomenclature '+ISNULL(@nmcl_table,N'null')
						  SET @nRetValue = 0
					end

DECLARE table_cursor CURSOR FOR SELECT [objectable_table_name], [state_table_name], [IsTranslateNomenclature], [section_type], [order]
								FROM @tblCursor 
								ORDER BY [order]
	OPEN table_cursor;	FETCH NEXT FROM table_cursor INTO  @nmcl_table_ext ,@state_table, @IsTranslateNomenclature, @section_type, @order
		WHILE (@@FETCH_STATUS <> -1)	BEGIN
		IF	  (@@FETCH_STATUS <> -2)	BEGIN
		IF			  @nRetValue = 1	BEGIN 
		SET @Status += ',('+@nmcl_table_ext+'->'+@state_table+')'
		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- load transfer settings	
			select TOP(1)	 
					 @section_id = [section_id]
					,@IsDynamicNomenclature = [element_is_dynamic]
			from #NmclElements 
			where [objectable_table_name] = @nmcl_table_ext AND [state_table_name] = @state_table AND [section_type] = @section_type

		-- substitution variables
			SELECT	 @SelectString=cast(N'' as nvarchar(MAX))
					,@WhereString=cast(N'' as nvarchar(MAX))
					,@InsertString=cast(N'' as nvarchar(MAX))
					,@JoinString=cast(N'' as nvarchar(MAX))  

		---------------------------------------------------------------------------------------------------------------------------------------------------------------------
		-- 7.1. Зареждане на сервизни полета в @tblServiceFields
		DELETE @tblServiceFields -- ( [ServiceField] nvarchar(128), [ServiceValue] nvarchar(MAX) )
		-- required
			-- internal_number
				IF	COL_LENGTH(@state_table,N'internal_number') is null
						begin SET @ErrMessage += N';Missing field internal_number in '+@state_table; SET @nRetValue = 0 end
				ELSE	INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'internal_number', @internal_number )
			-- id_record
				IF	COL_LENGTH(@state_table,N'id_record') is null OR COL_LENGTH(@nmcl_table,N'id') is null
						begin SET @ErrMessage += N';Missing field id_record in '+@state_table; SET @nRetValue = 0 end
				ELSE	INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_record', N'['+@nmcl_table+N'].[ID]' )			
		-- not required
			-- is_deleted
				IF	COL_LENGTH(@state_table,N'is_deleted') is not null
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'is_deleted', N'0' )		
			-- is_new
				IF	COL_LENGTH(@state_table,N'is_new')	is not null --AND COL_LENGTH(@nmcl_table_ext,N'is_new') is not null
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'is_new', N'0' )			
			-- id_user
				IF	COL_LENGTH(@state_table,N'id_user') is not null --AND COL_LENGTH(@nmcl_table_ext,N'id_user') is not null
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_user', cast(ISNULL(@id_user,0) as nvarchar(20)) )
			-- lang
				IF	COL_LENGTH(@state_table,N'lang') is not null AND COL_LENGTH(@nmcl_table_ext,N'lang') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'lang', N'['+@nmcl_table_ext+N'].[lang]' )			
			-- created_at
				IF	COL_LENGTH(@state_table,N'created_at')	is not null AND COL_LENGTH(@nmcl_table,N'created_at') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'created_at', N'['+@nmcl_table+N'].[created_at]' )
			-- updated_at
				IF	COL_LENGTH(@state_table,N'updated_at')	is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'updated_at', N'N'''+convert( nvarchar(50),GetDate(),121)+N'''' )			
			-- sys_updated_at
				IF	COL_LENGTH(@state_table,N'sys_updated_at')	is not null AND COL_LENGTH(@nmcl_table,N'updated_at') is not null
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'sys_updated_at', N'['+@nmcl_table+N'].[updated_at]' )			
			-- updated_by
				IF	COL_LENGTH(@state_table,N'updated_by')	is not null AND COL_LENGTH(@nmcl_table_ext,N'updated_by') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'updated_by', N'['+@nmcl_table+N'].[updated_by]' )			
				ELSE IF	COL_LENGTH(@state_table,N'updated_by')	is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'updated_by', cast(ISNULL(@id_user,0) as nvarchar(20)) )			
			-- created_by
				IF	COL_LENGTH(@state_table,N'created_by')	is not null AND COL_LENGTH(@nmcl_table_ext,N'created_by') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'created_by', N'['+@nmcl_table+N'].[created_by]' )			
			-- id_sys_section
				IF	COL_LENGTH(@state_table,N'id_sys_section')	is not null AND COL_LENGTH(@nmcl_table_ext,N'id_sys_section') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_sys_section', N'['+@nmcl_table_ext+N'].[id_sys_section]' )
				ELSE IF @section_type = 4 /*динамична секция*/
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_sys_section', N'[sys_referenceMap_dynamic].[sys_section_id]' )
			-- id_element_dynamic and value
				IF	COL_LENGTH(@state_table,N'id_element_dynamic')	is not null AND COL_LENGTH(@nmcl_table_ext,N'id_element_dynamic') is not null
				AND COL_LENGTH(@state_table,N'value') is not null AND COL_LENGTH(@nmcl_table_ext,N'value') is not null
					begin 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_element_dynamic', N'['+@nmcl_table_ext+N'].[id_element_dynamic]' )
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'value', N'['+@nmcl_table_ext+N'].[value]' )
				end
		-- grouping fields
			-- rows_group
			IF		(	COL_LENGTH(@state_table,N'rows_group')	is not null AND COL_LENGTH(@tablename_extra	,@nmcl_rows_group_field) is not null AND @IsGrouping=1		)
				 OR	(	COL_LENGTH(@state_table,N'rows_group')	is not null AND COL_LENGTH(@nmcl_table		,@nmcl_rows_group_field) is not null AND @IsGrouping=1		)
 						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'rows_group', N'@nmcl_rows_group_id' )			
				 ELSE		
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'rows_group', N'0' )								
-----------------------------------------
			-- id_grid_row
				IF	COL_LENGTH(@state_table,N'id_grid_row')	is not null
				begin 
				  SET @PartitionStr = case when @IsGrouping=1 AND @tablename_extra is not null then N'PARTITION BY ['+@tablename_extra+N'].['+cast(@nmcl_rows_group_field as nvarchar(130))+N']'
										   when @IsGrouping=1 AND @tablename_extra is null then N'PARTITION BY ['+@nmcl_table+N'].['+cast(@nmcl_rows_group_field as nvarchar(130))+N']'
										   else N''
									   end
				  SET @PartitionStr = N'ROW_NUMBER() OVER ( '+@PartitionStr+N' ORDER BY ['+@nmcl_table+N'].[id] )'
				-- @replace_data_state=1->delate state / @replace_data_state=0->if not exists state...
				--IF exists (select 1 from #tblResultFromSearch)
				--   INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] )				VALUES ( N'id_grid_row', N'[SerachResult].[id_record]' )
					IF @section_type = 3 AND @IsTranslateNomenclature=0	-- Grid tables=СЪЗДАВАНЕ НА @OUTPUT
					   INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] )	VALUES ( N'id_grid_row', @PartitionStr )			
					   ELSE
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue])	VALUES ( N'id_grid_row', N'[OUTPUT].[id_grid_row]' )			
				end
------------------------------------------
				SELECT @ServiceField =ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+[ServiceField]+N']' COLLATE database_default), CHAR(44)),N'')
				FROM @tblServiceFields WHERE [ServiceField] is not null
				SELECT @ServiceValue =ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),ISNULL([ServiceValue],N'NULL') COLLATE database_default), CHAR(44)),N'')
				FROM @tblServiceFields
		IF @SP_MODE = N'TEST1' AND exists (select 1 from @tblServiceFields)	select @ProcedureName+N'('+@Status+N')' as  [procedure],'table @tblServiceFields' tbl,* from @tblServiceFields
		ELSE IF @SP_MODE = N'TEST1'											select @ProcedureName+N'('+@Status+N')' as  [procedure],'No rows in table @tblServiceFields' tbl
		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 7.2. Зареждане на @comment
			SET @InfoStr = case when @section_type=3 then N'3.редове' when @section_type=4 then N'4.динамична' when @section_type=5 then N'5.заглавия' else ISNULL(cast(@section_type as nvarchar(20)),N'null') end
						 + N' ' + case when @IsTranslateNomenclature is null then N'null' when @IsTranslateNomenclature=0 then N'' else N'преводима' end
				SET @comment = cast('/* '+ISNULL(cast(@Order as nvarchar(20)),N'null')+N'.TABLES: '+ISNULL(@nmcl_table_ext,N'null')+'->'+ISNULL(@state_table,N'null')+N' (section type:'+@InfoStr+N') --*/
'									as nvarchar(MAX))
		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 7.3. Зареждане на @SelectString
			IF @section_type <> 4
				SELECT @SelectString=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+[objectable_table_name]+N'].['+[element_name] COLLATE database_default+N']'), CHAR(44)),N'') 
				FROM #NmclElements as e
					LEFT JOIN @tblServiceFields as sf ON sf.ServiceField COLLATE database_default=e.element_name COLLATE database_default
				WHERE e.[state_table_name]=@state_table AND e.[objectable_table_name]=@nmcl_table_ext AND e.[section_type]=@section_type 
				  AND sf.ServiceField is null

				IF NULLIF(@SelectString,N'') is not null AND NULLIF(@ServiceField,N'') is not null
				   SET @SelectString+=cast(N',' as nvarchar(MAX))

		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 7.4. Зареждане на @InsertString
			IF @section_type <> 4
				SELECT @InsertString=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+[element_name] COLLATE database_default+N']'), CHAR(44)),N'') 
				FROM #NmclElements as e
					LEFT JOIN @tblServiceFields as sf ON sf.ServiceField COLLATE database_default=e.element_name COLLATE database_default
				WHERE e.[state_table_name]=@state_table AND e.[objectable_table_name]=@nmcl_table_ext AND e.[section_type]=@section_type 
				  AND sf.ServiceField is null

				IF NULLIF(@InsertString,N'') is not null AND NULLIF(@ServiceField,N'') is not null
				   SET @InsertString+=cast(N',' as nvarchar(MAX))

		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 7.5. Зареждане на @JoinString
			-- всички таблици извън основната номенклатура - правим връзка с основната номенклатура
				IF @nmcl_table_ext <> @nmcl_table	-- translations and dynamic tables
					SET @JoinString += cast(N'
				LEFT JOIN ['+@nmcl_table+N'] ON ['+@nmcl_table_ext+N'].[id_'+@nmcl_table+N'] = ['+@nmcl_table+N'].[id]
'											as nvarchar(MAX))
			-- ако групиращата таблица НЕ е основната номенклатура - правим връзка с нея
				IF @tablename_extra is not null		-- Group
					SET @JoinString += cast(N'
					 JOIN ['+@tablename_extra+N'] ON ['+@nmcl_table+N'].[id_'+@tablename_extra+N'] = ['+@tablename_extra+N'].[id]
'											as nvarchar(MAX))
			-- всички типове секции извън основната по която създаваме OUTPUT таблицата 
				IF @section_type<>3 OR @IsTranslateNomenclature=1	-- Grid tables
--					IF @nmcl_table_ext <> @nmcl_table -- таблици извън основната номенклатура
						SET @JoinString += cast(N'
							JOIN @Output as [OUTPUT] ON ['+@nmcl_table+N'].[id] = [OUTPUT].[id_record]
'												as nvarchar(MAX))
--					ELSE -- основната номенклатура
--						SET @JoinString += cast(N'
--							JOIN @Output as [OUTPUT] ON ['+@nmcl_table+N'].[id] = [OUTPUT].[id]
--'												as nvarchar(MAX))

			-- за ОСНОВНАТА секция по която се създава OUTPUT таблицата, ако има търсене се свързва с резултата му
				IF @section_type=3 AND @IsTranslateNomenclature=0	-- Grid tables = СЪЗДАВАНЕ НА @OUTPUT = връзка с резултата от търсенето
				   IF @StringToFind is not null	-- result from search
					SET @JoinString += cast(N'
					 JOIN #tblResultFromSearch as [SerachResult] ON ['+@nmcl_table+N'].[id] = [SerachResult].[id_record]
'											as nvarchar(MAX))
			-- за динамични секции се свързва с sys_referenceMap_dynamic за да получи секцията на даден sys_element_id (id_element_dynamic)
				IF @section_type=4
					SET @JoinString += cast(N'
					 JOIN [dbo].[sys_referenceMap_dynamic] ON ['+@nmcl_table_ext+N'].[id_element_dynamic] = [sys_referenceMap_dynamic].[sys_element_id]
'											as nvarchar(MAX))


		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 7.6. Зареждане на @WhereString
			IF @section_type=3 AND @IsTranslateNomenclature=0	-- Grid tables	
				SET @WhereString = cast(N'
		WHERE [<nmcl_table>].[active] = 1 AND [<nmcl_table>].[is_deleted] = 0 
'+case when @tablename_extra is not null AND @IsGrouping=1 	
				then N' AND ['+@tablename_extra+'].['+@nmcl_rows_group_field+'] = @nmcl_rows_group_id'
	   when @IsGrouping=1 
				then N' AND ['+@nmcl_table+'].['+@nmcl_rows_group_field+'] = @nmcl_rows_group_id'
	   else N''
   end									as nvarchar(MAX))
			ELSE /*@section_type<>3 OR @IsTranslateNomenclature=1*/ 
				SET @WhereString = cast(N'' as nvarchar(MAX))

												-- insert string
												--SELECT @InsertString=STRING_AGG(CONVERT(NVARCHAR(max),N'['+[nomenclature_table_name]+N'].['+[element_name] COLLATE database_default+N']'), CHAR(44)) 
												--FROM #NmclElements as e
												--	LEFT JOIN @tblServiceFields as sf ON sf.ServiceField COLLATE database_default=e.element_name COLLATE database_default
												--WHERE e.state_table=@state_table AND e.t_nomenclature_table_name=@nmcl_table  
												--  AND sf.ServiceField is null
												--  SET @InsertString += @ServiceValue											
		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 7.7. replace substitution variables
		IF @section_type = 3 AND @IsTranslateNomenclature=0	-- Grid tables 
					SET @CommandString = cast(
					REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @GridQuery
									,N'<comment>',@comment)
									,N'<insert_string>',@InsertString)
									,N'<select_string>',@SelectString)
									,N'<where_string>',@WhereString)
									,N'<service_fields>',@ServiceField)
									,N'<service_values>',@ServiceValue)
									,N'<join_string>',@JoinString)
									,N'<nmcl_table>',@nmcl_table)
									,N'<nmcl_table_ext>',@nmcl_table_ext)
									,N'<state_table>',@state_table)
												as nvarchar(MAX))
		ELSE IF @section_type <> 3 AND @IsTranslateNomenclature=0	-- @section_type <> 3 No grid tables
					SET @CommandString = cast(
					REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @NogridQuery
									,N'<comment>',@comment)
									,N'<insert_string>',@InsertString)
									,N'<select_string>',@SelectString)
									,N'<where_string>',@WhereString)
									,N'<service_fields>',@ServiceField)
									,N'<service_values>',@ServiceValue)
									,N'<join_string>',@JoinString)
									,N'<nmcl_table>',@nmcl_table)
									,N'<nmcl_table_ext>',@nmcl_table_ext)
									,N'<state_table>',@state_table)
												as nvarchar(MAX))
			ELSE -- tables translations
						SET @CommandString = cast(N'
'+					REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @GridQuery_translation
										,N'<comment>',@comment)
										,N'<insert_string>',@InsertString)
										,N'<select_string>',@SelectString)
										,N'<where_string>',@WhereString)
										,N'<service_fields>',@ServiceField)
										,N'<service_values>',@ServiceValue)
										,N'<join_string>',@JoinString)
										,N'<nmcl_table>',@nmcl_table)
										,N'<nmcl_table_ext>',@nmcl_table_ext)
										,N'<state_table>',@state_table)
													as nvarchar(MAX))

				SET @SQLstr += @CommandString+cast(N'
'													 as nvarchar(MAX))

											END -- IF @nRetValue = 1	BEGIN -- ( nmcl->state )
		IF @SP_MODE = N'TEST1' select @ProcedureName+N'('+@Status+N')' as  [procedure],'ROOT QUERY'+case when @IsTranslateNomenclature=1 then N' TRANSLATION' else N'' end as tbl	
									, @comment as '@comment', @nmcl_table as '@nmcl_table', @nmcl_table_ext as '@nmcl_table_ext'
									, @state_table as '@state_table', @IsTranslateNomenclature as '@IsTranslateNomenclature'
									, @SelectString as '@SelectString', @WhereString as '@WhereString', @JoinString as '@JoinString'
									, @ServiceField as '@ServiceField', @ServiceValue as '@ServiceValue'
											END
							FETCH NEXT FROM table_cursor INTO  @nmcl_table_ext ,@state_table, @IsTranslateNomenclature, @section_type, @order
											END
		CLOSE table_cursor; DEALLOCATE table_cursor


-- NMCL -> STATE =======================================================================================================================================================================
	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 8. EXECUTE DYNAMIC SCRIPT
	IF @SP_MODE = N'TEST1'	begin
					select  @ProcedureName+N'('+@Status+N')' as  [procedure],'LAST QUERY' as tbl
												  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
												  ,@SQLstr + N'
select * from #Output'							  as '@SQLstr'
							end
	ELSE IF @nRetValue=1 	begin
		DROP TABLE IF EXISTS #Output
		CREATE TABLE #Output  (id INT,rows_group INT,id_grid_row INT,[internal_number] INT,[id_record] INT )
			SET @ParmDefinition = N'@internal_number int, @lang nvarchar(255), @lang_id int, @id_user int, @nmcl_rows_group_field nvarchar(128), @nmcl_rows_group_id int'
			EXEC @ExitCode = sp_executeSQL @SQLstr, @ParmDefinition
									  ,@internal_number=@internal_number				-- 1. Value of internal_number for state tables 
									  ,@lang=@lang										-- 2. Current language from sys_language table
									  ,@lang_id=@lang_id
									  ,@id_user=@id_user								-- 3. Current user from sys_users table
									  ,@nmcl_rows_group_field=@nmcl_rows_group_field	-- 4. Name of group token from nmcl_table WHEN transfer_vector=0
									  ,@nmcl_rows_group_id=@nmcl_rows_group_id			-- 5. ID of group token from nmcl_table WHEN transfer_vector=0
			IF @ExitCode > 0 begin SET @ErrMessage += N';Error when executing LAST query: '+cast(@ExitCode as nvarchar(20)); SET @nRetValue=0 end
			SET @Output_json = (select * from #Output FOR JSON PATH)
							end -- ELSE 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 AND @TestSaveParamID > 0 UPDATE [StepSoft].[dbo].[PARAMETERS] SET [ErrMessage] = @ErrMessage WHERE [ID] = @TestSaveParamID 
		DECLARE @Error TABLE ( [Error] nvarchar(2048) ); INSERT INTO @Error SELECT [Value] as Error FROM string_split(@ErrMessage,';')	WHERE ISNULL([Value],N'') <> N'' 
			SET @ErrMessage = (SELECT * FROM @Error FOR JSON AUTO , ROOT('Errors'))
	-- Определяне на перформънс
	IF @TestSaveParamID > 0 UPDATE [StepSoft].[dbo].[PARAMETERS] SET [EndTime] = GETDATE() WHERE [ID] = @TestSaveParamID 
	-- Връщане на резултат
	SET @nRetValue = case when @nRetValue>0 then 1 else 0 end
	SET @Return = @nRetValue
	IF @RetResult = 1
		SELECT @nRetValue as [Return], @ErrMessage as [Error_json], @Output_json as [Output_json], @ErrMessage as [ErrMessage] 

RETURN @nRetValue
END