SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS [dbo].[usp_ssERP8_RefreshDataNMCLState]
GO
/*
USE [S-Office8_Test] -- [SOffice8_S_DeveloperNew]
GO
DECLARE @SQLstr	nvarchar(MAX)
exec [dbo].[usp_ssERP8_RefreshDataNMCLState]
											 @sys_nmcl_id = '4'
											,@lang = 'bg'
											,@id_user = 19
											,@internal_number = 6
											,@nmcl_rows_group_field = ''
											,@nmcl_rows_group_id = 0
											,@ResultFromSearch_json = N'{
											  "StringToSearch": "108:1212",
											  "TotalResult": 1,
											  "Result": [
											    {
											      "rows_group": "0",
											      "id_record": 12
											    }
											  ]
											}'
											,@SQLstr=@SQLstr OUTPUT
											,@SP_MODE=N'TEST1'
select @SQLstr
--*/

-- =========================================================================================================================
CREATE PROCEDURE 
-- Name			
				[dbo].[usp_ssERP8_RefreshDataNMCLState]
-- Author		Щерю Щерев																									
-- Create date	11/04/2025
-- Version		v.10 1/9/2025 - Before use is_changed flag
--
-- Serever		MS SQL 2016																										
-- Description	Execute SQL script for refresh data from NMCL to 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									-- 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
	   ,@insert_backward		int				NULL=1				-- 7. Flag for backward insert NEW data from nmcl TO STATE 
	   ,@tablename_extra		nvarchar(128)	NULL=NULL			-- 8. Name of relation table for @nmcl_rows_group_field
	   ,@ResultFromSearch_json	nvarchar(MAX)	NULL=NULL			-- 9. JSON with results from search. 
								--		{"StringToSearch":"140","TotalResult":2,"Result":[{"rows_group":"0","id_record":86148},{"rows_group":"0","id_record":86149}]}	
	   ---------------------------------------------
	   ,@SQLstr					nvarchar(MAX)	NULL=NULL OUTPUT	-- 10. Text of refresh script
	   ,@ErrMessage				nvarchar(MAX)	NULL=NULL OUTPUT	-- 11. Text of error/message
	   ,@Output_json			nvarchar(MAX)	NULL=NULL OUTPUT	-- 12. Result of procedure
	   ,@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)	= '4'
			,@lang					nvarchar(255)	= 'bg'	
			,@id_user				int				= 19		
			,@internal_number		int				 = 6				
			,@nmcl_rows_group_field	nvarchar(130)	 = ''	
			,@nmcl_rows_group_id	int				
			,@tablename_extra		nvarchar(128)	
			,@SQLstr				nvarchar(MAX)	
			,@ErrMessage			nvarchar(MAX)	
			,@Return				int				
			,@SP_MODE				nvarchar(255)	=N'TEST1'	

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

	DROP TABLE IF EXISTS #Output1
		CREATE TABLE #Output1 (	[table] nvarchar(1024), [id] int, [action] nvarchar(255), [rows_group] int, [id_grid_row] int, [internal_number] int, [id_record] int )


	-- 1. Parameters sniffing
		SET @Status += ',1'
		--	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
		-- @sys_nmcl_id
		DECLARE @nmcl_table nvarchar(130), @nmcl_table_lang nvarchar(1024)
		  IF ISNULL(ISNUMERIC(@sys_nmcl_id),0)=1
			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
			WHERE n.[id] = @sys_nmcl_id 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 - -- 8. Име на свързана таблица в която се намира полето @nmcl_rows_group_field
			SET @tablename_extra = NULLIF(@tablename_extra,N'')
			IF @tablename_extra is not null -- АКО има указана такава таблица в @nmcl_table ТРЯБВА да има поле id_@tablename_extra за да се направи релация между @tablename_extra и @nmcl_table
				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	

		-- @ResultFromSearch_json
			DROP TABLE IF EXISTS #tblResultFromSearch
				CREATE TABLE #tblResultFromSearch ( [rows_group] int, [id_record] int )
				IF ISNULL(ISJSON(@ResultFromSearch_json),0)=1	begin
					INSERT INTO #tblResultFromSearch
					SELECT [rows_group], [id_record] FROM openjson(@ResultFromSearch_json,'$.Result') 
															with ( rows_group int '$.rows_group', id_record int '$.id_record' )
																end
		-- @nmcl_rows_group_field AND @nmcl_rows_group_id - ИМЕ на ГРУПИРАЩО ПОЛЕ и СТОЙНОСТ за текущата група
			DECLARE @nmcl_rows_group_condition nvarchar(MAX) = cast(N'' as nvarchar(MAX))
			-- проверка за групиращо поле и таблица в която се намира то
			IF NULLIF(@nmcl_rows_group_field,N'') is null OR (@nmcl_rows_group_id is null OR @nmcl_rows_group_id<=0)
				begin SET @nmcl_rows_group_field=null;SET @nmcl_rows_group_id=null end

			IF (		@tablename_extra is not null AND @nmcl_rows_group_field is not null AND COL_LENGTH(@tablename_extra,@nmcl_rows_group_field) is null 	)
				begin SET @ErrMessage = N'Missing group field ['+@nmcl_rows_group_field+N'] in '+@tablename_extra+N' table'; SET @nRetValue = 0 
					  SET @nmcl_rows_group_condition = ISNULL( @nmcl_rows_group_condition,cast(N'' as nvarchar(MAX)))
				end
			ELSE IF (	@tablename_extra is null  AND COL_LENGTH(@nmcl_table,	 @nmcl_rows_group_field) is null AND @nmcl_rows_group_field is not null	)
				begin SET @ErrMessage = N'Missing group ['+@nmcl_rows_group_field+N'] in '+ISNULL(@nmcl_table,N'null')+N' table'; SET @nRetValue = 0 
					  SET @nmcl_rows_group_condition = ISNULL( @nmcl_rows_group_condition,cast(N'' as nvarchar(MAX)))				
				end
				ELSE  begin
					  SET @nmcl_rows_group_condition = cast(N' AND [source_table].[rows_group]='''+cast(@nmcl_rows_group_id as nvarchar(20))+'''
'															as nvarchar(MAX))
					  end	
			-- създаване на условие за подредба на state таблицата
			DECLARE @order_condition nvarchar(MAX) = case when @tablename_extra is not null AND @nmcl_rows_group_field is not null
															then N'['+@tablename_extra+N'].['+@nmcl_rows_group_field+N']'
														  else -- when @tablename_extra is null	AND @nmcl_table is not null then
																 N'['+@nmcl_table+N'].[id]'	
														  -- else N'['+@nmcl_table_ext+N'].[id]			
													  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


	-- 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(@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(@insert_backward as varchar(10)),'null')+','			-- 7. Flag for backward insert NEW data from nmcl TO STATE 
												 +	ISNULL(cast(@tablename_extra as varchar(10)),'null')+','			-- 8. Name of relation table for @nmcl_rows_group_field
												 +	ISNULL(@SP_MODE,'null')												-- 11.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_id]			-- 6. List of ID's of group token from nmcl_table ( "," delimiter )
															,@insert_backward		as [@insert_backward]				-- 7. Flag for backward insert NEW data from nmcl TO STATE 	
															,@tablename_extra		as [@tablename_extra]				-- 8. Name of relation table for @nmcl_rows_group_field
															,@ErrMessage		 	as [@ErrMessage]					-- 9. Text of error/message
															,@Return			 	as [@Return]						-- 10. Return value	(0 - fail 1 - success)
															,@SP_MODE				as [@SP_MODE]						-- 11. 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 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', @insert_backward as '@insert_backward'
								  ,@sys_nmcl_id as '@sys_nmcl_id', @nmcl_table as '@nmcl_table', @nmcl_table_lang as '@nmcl_table_lang'
								  ,@tablename_extra as '@tablename_extra'
								  ,@internal_number as '@internal_number', @nmcl_rows_group_field as '@nmcl_rows_group_field', @nmcl_rows_group_id as '@nmcl_rows_group_id'
								  ,@nmcl_rows_group_condition as '@nmcl_rows_group_condition'
								  ,@ResultFromSearch_json as '@ResultFromSearch_json'
	IF @SP_MODE = N'TEST1' AND EXISTS (select 1 from #tblResultFromSearch)
							select  @ProcedureName+N'('+@Status+N')' as  [procedure],'table @tblResultFromSearch' as tbl,* from #tblResultFromSearch
	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 3. Load elements metadata / Зареждане на метаданни за елементите
	-- select * from sys_nmcl; select * from dbo.ufn_ssERP8_CollectObjects(1,4,null,null,null,null,N'bg')
	-- select * from nmcl_currency where id = 2; select * from nmcl_currency_translations where id_nmcl_currency=2
	/*		0 document title
			1 document row
			2 report
			3 nomenclature row
			4 dynamic row
			5 nomenclature title
			6 map sections */
		SET @Status += ',3'
	--IF @nRetValue = 1 /*AND OBJECT_ID('tempdb..#NmclElements1') is null*/	begin
	DROP TABLE IF EXISTS #NmclElements1
		SELECT *
		INTO #NmclElements1
		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,4,5)
	--																		end	-- IF @nRetValue = 1 AND OBJECT_ID('tempdb..#NmclElements1') is null	
	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'#NmclElements1' as tbl,* from #NmclElements1 order by section_id,element_id

-- NMCL -> STATE =======================================================================================================================================================================
	-- 4. Create general scripts for transfer / Създаване на основните скриптове за трансфер
		SET @Status += ',4'
	IF @nRetValue = 1	begin
		DECLARE @TableQuery nvarchar(MAX) = cast(N'<comment>
MERGE INTO [dbo].[<state_table>]							AS target_table
USING	(SELECT	 <select_string>
				,@internal_number		as [internal_number]
				,[<nmcl_table>].[id]	as [id_record]	
		 FROM	[dbo].[<nmcl_table_ext>]
			<join_string>
		 <where_string>
		)													AS source_table
ON			<merge_join_string>
WHEN MATCHED 
		THEN	UPDATE SET  <update_string>
'+ case when @insert_backward = 1 then
N'WHEN NOT MATCHED '+@nmcl_rows_group_condition+N'
		THEN	INSERT	(	<insert_string_field>
								,[is_new]	
								,[internal_number]
								,[id_record]
						)
				VALUES	(	<insert_string_value>
								,0	/*from another user*/
								,[internal_number]
								,[id_record]
						)'
		else N'' end + N'
	<output_table>;'						
								as nvarchar(MAX))

						end	-- IF @nRetValue = 1
	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'Queries' as tbl, @TableQuery as '@TableQuery'


	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	-- 5. Create table script for transfer on @TableQuery/ Създаване на скрипт за трансфер на @TableQuery
		SET @Status += ',5'
			--DECLARE @IsTitleSections bit = 0, @IsDynamicSections bit = 0, @IsTranslateElement bit = 0, , , @IsRowSections bit=0
		DECLARE	@comment nvarchar(MAX), @section_type int, @nmcl_table_ext nvarchar(128), @state_table nvarchar(128), @object_root int
		/*INSERT*/		,@InsertStringField nvarchar(MAX),@InsertStringValue nvarchar(MAX)
		/*SELECT*/		,@SelectString nvarchar(MAX),	@WhereString nvarchar(MAX)
		/*UPDATE*/		,@UpdateString nvarchar(MAX)
		/*JOIN*/		,@JoinString nvarchar(MAX)
		/*SERVICE*/		,@ServiceField nvarchar(MAX),	@ServiceValue nvarchar(MAX)
		/*MERGE_JOIN*/	,@Merge_join_string  nvarchar(MAX)
		/*OUTPUT table*/,@OutputString nvarchar(MAX)
		DECLARE @tblServiceFields TABLE ( [ServiceField] nvarchar(130), [ServiceValue] nvarchar(1024) )
		SET @SQLstr += CAST(N'
/*Procedure: '+ISNULL(@ProcedureName,N'null')+N' - for test in SSMS omly*/

DECLARE @Output_root TABLE	(	 [id]				int 
								,[action]			nvarchar(255)
								,[rows_group]		int
								,[id_grid_row]		int	
								,[internal_number]	int
								,[id_record]		int
							)
DECLARE @Output TABLE	(	 [id]				int 
							,[action]			nvarchar(255)
						)' as nvarchar(MAX))
		IF @SP_MODE=N'TEST1' 
		SET @SQLstr += cast(N'		
	DECLARE  @lang nvarchar(255)	 = N'''+ISNULL(@lang,N'bg')+'''	
			,@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'		
			,@section_type int		 ='+ISNULL(cast(@section_type as nvarchar(20)),N'null')+N'
			,@nmcl_rows_group_id int ='+ISNULL(cast(@nmcl_rows_group_id as varchar(10)),N'null')+N'
	DROP TABLE IF EXISTS #Output1
		CREATE TABLE #Output1 ( [table] nvarchar(128), [id] INT,[action] NVARCHAR(255),[rows_group] INT,[id_grid_row] INT,[internal_number] INT,[id_record] INT	)
		DECLARE @ResultFromSearch_json nvarchar(MAX) = '+ISNULL(N''''+@ResultFromSearch_json+N'''',N'null')+N'
			DROP TABLE IF EXISTS #tblResultFromSearch; CREATE TABLE #tblResultFromSearch ( [rows_group] int, [id_record] int )
			INSERT INTO #tblResultFromSearch
			SELECT [rows_group], [id_record] FROM openjson(@ResultFromSearch_json,''$.Result'') with ( rows_group int ''$.rows_group'', id_record int ''$.id_record'' )
		select * from #tblResultFromSearch
'													as nvarchar(MAX))

		IF @SP_MODE=N'TEST1' AND OBJECT_ID(N'tempdb..#Output') is not null begin
			SET @SQLstr += cast(N'
		DROP TABLE IF EXISTS #Output; CREATE TABLE #Output ( [inserted_id] int )
		INSERT INTO #Output ( [inserted_id] ) VALUES	
' as nvarchar(MAX))
			SELECT TOP(20) @SQLstr += cast(N'('+cast([inserted_id] as nvarchar(20))+N')' as nvarchar(MAX))
			FROM #Output
			SET @SQLstr += cast(N'(0)' as nvarchar(MAX))
																			end

IF @nRetValue = 1 BEGIN -- CURSOR INTO nmcl AND state pairs tables
	DECLARE @j int=0
	DECLARE table_cursor CURSOR FOR SELECT DISTINCT
											[objectable_table_name]	-- име на номенклатурната таблица съдържаща елемента ( nmcl или nmcl_translations)
										   ,[state_table_name]		-- име на state таблицата
										   ,[object_root]
									FROM #NmclElements1 
									WHERE ([state_table_name] is not null AND [objectable_table_name] is not null ) 
									--AND ([section_type] IN ( 3  -- nomenclature row
									--						,4  -- dynamic row
									--						,5))-- nomenclature title
									ORDER BY [object_root]
		OPEN table_cursor;	FETCH NEXT FROM table_cursor INTO  @nmcl_table_ext ,@state_table, @object_root
			WHILE (@@FETCH_STATUS <> -1)	BEGIN
			IF	  (@@FETCH_STATUS <> -2)	BEGIN
	-- записва коя двойка обработва последно в случай на грешка
			SET @j += 1
			SET @Status += N',<<'+cast(@j as nvarchar(20))+N'>>('+@nmcl_table_ext+N'->'+@state_table+N')'

	-- КАКВА Е ДВОЙКАТА ТАБЛИЦИ. Тъй като state таблицата се определя от секцията+преводимост+динамичност се предполага че за двойката таблици ТИПА СЕКЦИЯТА е константа
			SET @section_type = case when EXISTS (SELECT 1 FROM #NmclElements1 WHERE [rm_element_translatable]=1 AND [section_type]=5
																			    AND [objectable_table_name]=@nmcl_table_ext
																			    AND [state_table_name]=@state_table)			then 50 -- преводими ( state_form_387_translations )
									 when EXISTS (SELECT 1 FROM #NmclElements1 WHERE [rm_element_translatable]=1 AND	[section_type]=3
																			    AND [objectable_table_name]=@nmcl_table_ext
																			    AND [state_table_name]=@state_table)			then 30 -- преводими (state_form_387_grid_1997_translations )
									 when EXISTS (SELECT 1 FROM #NmclElements1 WHERE [element_is_dynamic]=1 
																			    AND [section_type]=4
																			    AND [objectable_table_name]=@nmcl_table_ext
																			    AND [state_table_name]=@state_table)			then 4 -- динамична	( state_form_387_dynamic )
									 when EXISTS (SELECT 1 FROM #NmclElements1 WHERE [section_type]=5
																			    AND [objectable_table_name]=@nmcl_table_ext
																			    AND [state_table_name]=@state_table)			then 5 -- заглавна	( state_form_387 )
									else																							 3 -- редове 	( state_form_387_grid_1997 )	
								end	

	-- коментар който се появява в генерирания стринг
			SET @comment = cast( N'/* '+cast(@j as nvarchar(20))+'.Refreshing table: '+ISNULL(@state_table,N'null')+N' from table: '+ISNULL(@nmcl_table_ext,N'null')+N'('+CAST(@section_type as nvarchar(20))+N')'+N' */
'								as nvarchar(MAX))

		IF @section_type > 3 SET @comment += cast(N'
DELETE @Output
'													as nvarchar(MAX))
	-- service fields
		SET @ServiceField=cast(N'' as nvarchar(MAX)); SET @ServiceValue=cast(N'' as nvarchar(MAX))
			-- REQUIRED FIELDS
				-- 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
				-- id_record
					IF	COL_LENGTH(@state_table,N'id_record') is null
						begin SET @ErrMessage = N'Missing field id_record in '+@state_table; SET @nRetValue = 0 end
			-- OPTIONAL FIELDS
				DELETE @tblServiceFields
				-- 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 VALUES (N'id_user', N'@id_user' )
						--INSERT INTO @tblServiceFields VALUES (N'id_user', N'[id_user]' )
				-- 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 VALUES (N'lang', N'[lang]' )
				-- created_at
					IF	COL_LENGTH(@state_table,N'created_at')	is not null AND COL_LENGTH(@nmcl_table_ext,N'created_at') is not null 
						INSERT INTO @tblServiceFields VALUES (N'created_at', N'[created_at]' )
				-- updated_at
					IF	COL_LENGTH(@state_table,N'updated_at')	is not null AND COL_LENGTH(@nmcl_table_ext,N'updated_at') is not null  
						INSERT INTO @tblServiceFields VALUES (N'updated_at', N'[updated_at]' )
				-- sys_updated_at
					IF	COL_LENGTH(@state_table,N'sys_updated_at') is not null AND COL_LENGTH(@nmcl_table_ext,N'updated_at') is not null  
						INSERT INTO @tblServiceFields VALUES (N'sys_updated_at', 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 VALUES (N'updated_by', N'[updated_by]' )
				-- 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 VALUES (N'created_by', N'[created_by]' )
				-- id_sys_section
				--	IF	COL_LENGTH(@state_table,N'id_sys_section')	is not null INSERT INTO @tblServiceFields VALUES (N'id_sys_section', N'@section_id' )
				---- rows_group
				--	IF	COL_LENGTH(@state_table,N'rows_group')	is not null AND COL_LENGTH(@nmcl_table_ext,@nmcl_rows_group_field) is not null AND @nmcl_rows_group_field is not null
				--		INSERT INTO @tblServiceFields VALUES (N'rows_group', N'[rows_group]')
			-- 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 @nmcl_rows_group_field is not null		)
					 OR	(	COL_LENGTH(@state_table,N'rows_group')	is not null AND COL_LENGTH(@nmcl_table		,@nmcl_rows_group_field) is not null AND @nmcl_rows_group_field is not null		)
	 						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 
						INSERT INTO @tblServiceFields VALUES (N'id_grid_row', N'[id_grid_row]' )
				-- 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 VALUES (N'id_element_dynamic', N'['+@nmcl_table_ext+N'].[id_element_dynamic]' )
							INSERT INTO @tblServiceFields VALUES (N'value', N'['+@nmcl_table_ext+N'].[value]' )
						end
	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'@tblServiceFields' as tbl,* from @tblServiceFields

	-- substitution variables
		SELECT	 @SelectString=cast(N'' as nvarchar(MAX))		-- source table
				,@JoinString=cast(N'' as nvarchar(MAX))			-- source table
				,@WhereString=cast(N'' as nvarchar(MAX))		-- source table
				,@Merge_join_string=cast(N'' as nvarchar(MAX))  
				,@UpdateString=cast(N'' as nvarchar(MAX)) 
				,@InsertStringField=cast(N'' as nvarchar(MAX)),@InsertStringValue=cast(N'' as nvarchar(MAX))
				,@OutputString=cast(N'' as nvarchar(MAX))				
		-- source table
			-- select string = elements+service fields
				SELECT @SelectString+=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+@nmcl_table_ext+N'].['+[element_name] COLLATE database_default+N'] as ['+[element_name] COLLATE database_default+N']'), CHAR(44)),N'') 
				FROM #NmclElements1 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 sf.ServiceField is null

				SELECT @SelectString +=case when LEN(@SelectString)>0 AND EXISTS (select 1 from @tblServiceFields) then N',' else N'' end
				SELECT @SelectString+=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),case when LEFT([ServiceValue],1)=N'[' AND RIGHT([ServiceValue],1)=N']'
																					then N'['+@nmcl_table_ext+N'].'+[ServiceValue]
																					else [ServiceValue] 
																				   end	COLLATE database_default
																			 +N' as ['+[ServiceField] COLLATE database_default+N']'), CHAR(44)),N'') 
				FROM @tblServiceFields
				WHERE [ServiceField] not in (N'rows_group',N'id_grid_row')

		-- added [rows_group]
			IF	COL_LENGTH(@state_table,N'rows_group')	is not null 
			AND COL_LENGTH(@nmcl_table_ext,@nmcl_rows_group_field) is not null 
			AND @nmcl_rows_group_field is not null
				SET @SelectString += cast(N'
,['+@nmcl_table_ext+N'].['+cast(@nmcl_rows_group_field as nvarchar(130))+N'] as [rows_group]' as nvarchar(MAX))
			
			ELSE IF	COL_LENGTH(@state_table,N'rows_group')	is not null 
				 AND COL_LENGTH(@tablename_extra,@nmcl_rows_group_field) is not null 
				 AND @nmcl_rows_group_field is not null
				 SET @SelectString += cast(N'
,['+@tablename_extra+N'].['+cast(@nmcl_rows_group_field as nvarchar(130))+N'] as [rows_group]' as nvarchar(MAX)) 
				 
				 ELSE IF COL_LENGTH(@state_table,N'rows_group')	is not null AND @nmcl_rows_group_id is not null
					  SET @SelectString += cast( N'
,'+CAST(@nmcl_rows_group_id as nvarchar(20))+N' as [rows_group]' as nvarchar(MAX))
					  ELSE IF COL_LENGTH(@state_table,N'rows_group')	is not null
						   SET @SelectString += cast( N'
,0 as [rows_group]' as nvarchar(MAX))
		
		-- added [id_grid_row]
			IF @section_type = 3 SET @SelectString += cast(N'
,ROW_NUMBER() OVER ( order by ['+@nmcl_table_ext+N'].[id] ) as [id_grid_row]' as nvarchar(MAX))
--,ROW_NUMBER() OVER ( order by '+@order_condition+N' ) as [id_grid_row]' as nvarchar(MAX))
			ELSE				 SET @SelectString += cast(N'
,[Output].[id_grid_row]  as [id_grid_row]'  as nvarchar(MAX))

	-- JOIN STRING
		-- join string = BETWEEN @tablename_extra AND root table
			IF @tablename_extra is not null
					SET @JoinString += cast(N'LEFT JOIN ['+@tablename_extra+N'] ON ['+@nmcl_table_ext+N'].[id_'+@tablename_extra+N'] = ['+@tablename_extra+N'].[id]
'													as nvarchar(MAX))
		-- join string = BETWEEN root table AND #tblResultFromSearch ( ограничаване по резултатите от търсене)
			IF exists (select 1 from #tblResultFromSearch)
					SET @JoinString += cast(N'
					 JOIN #tblResultFromSearch as [SerachResult] ON ['+@nmcl_table+N'].[id] = [SerachResult].[id_record]
'											as nvarchar(MAX))

		-- join string = BETWEEN nmcl(root) table AND #OUTPUT table from [dbo].[usp_ssERP8_TransferDataStateNMCL] (ограничаване по резултатите от предходно зареждане)
--			IF @section_type = 3 AND OBJECT_ID(N'tempdb..#Output') is not null
--					SET @JoinString += cast(N'JOIN #Output as [Output_upd] ON ['+@nmcl_table+N'].[id] = [Output_upd].[inserted_id]
--'													as nvarchar(MAX))
		-- Ако секцията НЕ е редове (основната секция която се зарежда от основната номенклатура и не е необходим пак join към нея)
			IF @section_type > 3 	BEGIN 
				-- връзка към основната таблица @object_root=0(@nmcl_table). Релацията е id_@nmcl_table=@nmcl_table.id
				IF @section_type<>5
					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))
				-- връзка към @Output_root [Output]
				IF @section_type=30 OR @section_type=50	-- nomenclature translation
					SET @JoinString = cast(N'		JOIN @Output_root as [Output] ON ['+@nmcl_table_ext+N'].[id_'+@nmcl_table+N'] = [OUTPUT].[id_record] 
		JOIN [dbo].['+@nmcl_table+'] ON ['+@nmcl_table_ext+N'].[id_'+@nmcl_table+N'] = ['+@nmcl_table+'].[id] 
'											as nvarchar(MAX))
				ELSE IF @section_type = 4				-- nomenclature dynamic
					SET @JoinString += cast(N'     JOIN @Output_root as [Output] ON ['+@nmcl_table_ext+N'].[id_'+@nmcl_table+N'] = [Output].[id_record]'
											as nvarchar(MAX))
					 ELSE IF @section_type = 5			-- nomenclature title
						SET @JoinString += cast(N'     JOIN @Output_root as [Output] ON ['+@nmcl_table_ext+N'].[id] = [Output].[id_record]	'
												as nvarchar(MAX))
									END -- IF @object_root > 0	BEGIN -- translations and dynamic tables

	-- WHERE STRING
			-- where_string = active + is_deleted + @nmcl_rows_group_field=@nmcl_rows_group_id
					SET @WhereString = cast(N'' as nvarchar(MAX))
					SET @WhereString += cast(case when COL_LENGTH(@nmcl_table,'active') is not null then N'['+@nmcl_table+N'].[active] = 1' end
											as nvarchar(MAX))
					SET @WhereString += cast( case when COL_LENGTH(@nmcl_table,'is_deleted') is not null 
													then case when @WhereString=N'' then N'' else N' AND ' end
														+N'['+@nmcl_table+N'].[is_deleted] = 0' 
													else N''
											  end	
											as nvarchar(MAX))
					SET @WhereString += cast( case when COL_LENGTH(@nmcl_table_ext,@nmcl_rows_group_field) is not null AND @nmcl_rows_group_field is not null
													then case when @WhereString=N'' then N'' else N' AND ' end
														 +N' ['+@nmcl_table_ext+'].['+@nmcl_rows_group_field+'] = @nmcl_rows_group_id'
													else N''
											  end
											as nvarchar(MAX))
					SET @WhereString = case when @WhereString = N'' then cast(N'' as nvarchar(MAX)) else cast(N'WHERE '+@WhereString as nvarchar(MAX)) end

		-- merge_join_string
			SET @Merge_join_string	+=	cast( N' [target_table].[internal_number] = [source_table].[internal_number] AND [target_table].[id_record] = [source_table].[id_record] 
'												 as nvarchar(MAX))
			SET @Merge_join_string	+=	cast( case when COL_LENGTH(@state_table,N'lang') is not null AND COL_LENGTH(@nmcl_table_ext,N'lang') is not null 
													then N' AND [target_table].[lang] = [source_table].[lang]' 
													else N'' 
											   end
												as nvarchar(MAX))

		-- update_string
			SELECT @UpdateString +=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+[element_name] COLLATE database_default+N']=[source_table].['+[element_name] COLLATE database_default+N']'), CHAR(44)),N'') 
			FROM #NmclElements1 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 sf.ServiceField is null
			SELECT @UpdateString+=case when LEN(@UpdateString)>0 AND EXISTS (select 1 from @tblServiceFields) then N',' else N'' end
			SELECT @UpdateString+=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+[ServiceField]+N']' COLLATE database_default
								+ case when LEFT([ServiceValue],1)=N'[' AND RIGHT([ServiceValue],1)=N']' then N'=[source_table].'+[ServiceValue]
																										 else N'='+[ServiceValue] 
								   end  COLLATE database_default) 
													, CHAR(44)),N'') 
			FROM @tblServiceFields

		-- insert string field
			SELECT @InsertStringField +=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+[element_name] COLLATE database_default+N']'), CHAR(44)),N'') 
			FROM #NmclElements1 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 sf.ServiceField is null
			  AND @section_type IN (3,5) -- only for static sections
			SELECT @InsertStringField+=case when LEN(@InsertStringField)>0 AND EXISTS (select 1 from @tblServiceFields) 
											 then N',' else N'' 
											end
			SELECT @InsertStringField+=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),[ServiceField]), CHAR(44)),N'') 
			FROM @tblServiceFields
		-- insert string value
			SELECT @InsertStringValue +=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+[element_name] COLLATE database_default+N']'), CHAR(44)),N'') 
			FROM #NmclElements1 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 sf.ServiceField is null
			  AND @section_type IN (3,5) -- only for static sections
			SELECT @InsertStringValue+=case when LEN(@InsertStringValue)>0 AND EXISTS (select 1 from @tblServiceFields) then N',' else N'' end
			
			SELECT @InsertStringValue+=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),[ServiceValue]), CHAR(44)),N'') 
			FROM @tblServiceFields

		-- output string
			SET @OutputString = case when @section_type=3
									then cast(N'
	OUTPUT  [source_table].[id_record], $action, INSERTED.[rows_group], INSERTED.[id_grid_row], INSERTED.[internal_number], INSERTED.[id_record] INTO @Output_root;
	INSERT INTO #Output1 ( [table], [id], [action], [rows_group], [id_grid_row], [internal_number], [id_record] )
	SELECT   '''+@nmcl_table_ext+N'-'+@state_table+N'''	as [table]
			,[Output].[id]				as [id]
			,[Output].[action]			as [action]
			,[Output].[rows_group]		as [rows_group]		
			,[Output].[id_grid_row]		as [id_grid_row]		
			,[Output].[internal_number]	as [internal_number]	
			,[Output].[id_record]		as [id_record]		
	FROM @Output_root as [Output]'				as nvarchar(MAX))
									 else	cast(N'
	OUTPUT  [source_table].[id_record], $action INTO @Output;
	INSERT INTO #Output1 ( [table], [action], [id] )
	SELECT   '''+@nmcl_table_ext+N'-'+@state_table+N'''	as [table]
			,[Output].[action]				as [id]
			,[Output].[id]				as [id]
	FROM @Output as [Output]'					as nvarchar(MAX))
								end	
					
				-- replace substitution variables
					SET @CommandString = cast(
					REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @TableQuery
									,N'<comment>',@comment)
									,N'<nmcl_table>',@nmcl_table)
									,N'<nmcl_table_ext>',@nmcl_table_ext)
									,N'<state_table>',@state_table)
									,N'<select_string>',@SelectString)
									,N'<join_string>',@JoinString)
									,N'<where_string>',@WhereString)
									,N'<merge_join_string>',@Merge_join_string)
									,N'<update_string>',@UpdateString)
									,N'<insert_string_field>',@InsertStringField)
									,N'<insert_string_value>',@InsertStringValue)
									,N'<output_table>',@OutputString)
												as nvarchar(MAX))

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

			IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'PART QUERY' as tbl	
										  ,@comment as '@comment', @nmcl_table as '@nmcl_table', @nmcl_table_ext as '@nmcl_table_ext', @state_table as '@state_table'
										  ,@SelectString as '@SelectString', @WhereString as '@WhereString', @JoinString as '@JoinString'
										  ,@ServiceField as '@ServiceField', @ServiceValue as '@ServiceValue', @CommandString as 'PART QUERY string'

												END
								FETCH NEXT FROM table_cursor INTO @nmcl_table_ext ,@state_table, @object_root
												END
			CLOSE table_cursor; DEALLOCATE table_cursor
				  END -- IF @nRetValue = 1 BEGIN -- ЦИКЪЛ ПО ДВОЙКИТЕ state И nmcl ТАБЛИЦИ


-- NMCL -> STATE =======================================================================================================================================================================
-- Execute @SQLstr
	-- 6. Execute OR display @SQLstr / Изпълнение или показване на @SQLstr
print @ProcedureName+N'('+@Status+N')'
		SET @Status += N',6'
	IF @SP_MODE = N'TEST1'	-- only display string for test
			select  @ProcedureName+N'('+@Status+N')' as  [procedure],'LAST QUERY' as tbl
				   ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
				   ,@SQLstr as '@SQLstr'
	ELSE 	begin		   	-- execute string
			SET @ParmDefinition = N'@internal_number int, @lang nvarchar(255), @lang_id int, @id_user int, @nmcl_rows_group_field nvarchar(130), @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
			end

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

	SET @Return = @nRetValue
			SET @Output_json = (select * from #Output1 FOR JSON PATH)
			DECLARE @Error_json nvarchar(MAX) = (select [value] as [error] from string_split(@ErrMessage,N';') WHERE NULLIF([value],N'') is not null FOR JSON PATH)
	-- IF @SP_MODE=N'TEST1' SELECT @Return as [Return], @Error_json as [Error_json], @Output_json as [Output_json]

RETURN @nRetValue
END