--USE [sOffice8_AGVISER][SOffice8_Zarimex]--[sOffice8_AGVISER]--[SOffice8_MagnumCyprus2022]
--GO
/****** Object:  StoredProcedure [dbo].[usp_ssERP8_SaveDocument]    Script Date: 30.10.2024 г. 15:42:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS [dbo].[usp_ssERP8_SaveDocument]
GO

/*	-- заявка за показване на state и sys таблиците в документ
	-- select distinct state_table, sys_table from ufn_sdCollectDocumentElements(26,'bg')
	-- заявка за показване на типове документи в базата(с българско име) + sys таблиците им + типа на секцията 
	-- select distinct tr.value as [Име],
	-- 				el.section_type,
	-- 				el.sys_table,
	-- 				dt.* 
	-- from sys_doctype as dt 
	-- 	left join sys_translations as tr ON tr.label=dt.label AND tr.locale=N'bg' AND tr.device=N'web' AND filename=N'document'
	-- 	cross apply ufn_sdCollectDocumentElements(dt.id,'bg') as el

declare @r int, @em nvarchar(MAX), @id_sys_stitle int
exec [dbo].[usp_ssERP8_SaveDocument]
			@POS			 = null			
		   ,@id_nmcl_company = 3				
		   ,@id_user		 = 19				
		   ,@lang			 = N'bg'			
		  --------------------------				
		   ,@objectable_id	 = 79
		   ,@internal_number = 204
		   ,@id_sys_stitle	 = 0	
		  --------------------------
		   ,@Return			 = @r OUTPUT
		   ,@ErrMessage		 = @em OUTPUT
		   ,@SP_MODE		 ='TEST1'
select @r as [Return], @em as ErrMessage, @id_sys_stitle as id_sys_stitle
*/
-- =========================================================================================================================
CREATE PROCEDURE 
-- Name			
				[dbo].[usp_ssERP8_SaveDocument]
-- Author		Щерю Щерев																									
-- Create date	16/11/2022
-- Version		15/8/2023 v.5.
--				28/8/2023 v.7.
--				29/8/2023 v.8.
--				25/01/2024 v.NP0
--				17/12/2024 v.12 - Fixed the situation where there is no element for sys_srow in the section
--				28/1/2025  v.13	- Remove table @selectString_add from insert_string	
--				10/4/2025  v.14 - Record was changed by another user
--
-- Serever		MS SQL 2016																										
-- Description	Root procedure of document recording from state table to sys table / Главна процедура за запис на документ от state таблица в sys таблица
-- Returns		0 - fail 1 - success
-- Parameters
		@POS				 	nvarchar(255)		NULL=NULL						-- 1. Workstation name/Name of POS work point
	   ,@id_nmcl_company	 	int					NULL=0							-- 2. ID of current company object
	   ,@id_user			 	int					NULL=0							-- 3. ID of current application user
	   ,@lang					nvarchar(255)		NULL=N'bg'						-- 4. Language of application
	  --------------------------------------	 
	   ,@objectable_id			int					NULL=NULL						-- 5. ID of document type sys_Doctype nomenclature
	   ,@internal_number 	 	int					NULL=NULL						-- 6. Internal_number
	   ,@id_sys_stitle			int					NULL=NULL			OUTPUT		-- 7. ID of inserted record in sys_stitle table
	   ,@keeping_data_state		int					NULL=0							-- 8. Flag for keeping data in state table (=0 do not keep, =1 keep)	
	  ----------------------------------------------------------------------------
	   ,@Return					nvarchar(2)			NULL=NULL			OUTPUT		-- 9. Return value
	   ,@ErrMessage				nvarchar(MAX)		NULL=NULL			OUTPUT		-- 10. Text of error
	   ,@Output_json			nvarchar(MAX)		NULL=NULL			OUTPUT		-- 11. json with results
	   ,@SP_MODE			 	nvarchar(1024)		NULL = N'EXECUTE'				-- 12. Execution mode / Дали е в работен или тестов режим	
-- Constraction 
-- Tables		all tables containing data of document
--=========================================================================================================================
AS BEGIN
SET NOCOUNT ON;
--select 1/0
/*
manualUpdateFlag: No
*/
BEGIN TRY
/*-- dbo.usp_ssERP8_SaveDocument  1, '454', '1', '1', '19'
	DECLARE
		    @POS				nvarchar(255)	= null			
		   ,@id_nmcl_company  	int=1				
		   ,@id_user			int=4				
		   ,@lang				nvarchar(255)= N'bg'			
		   ,@objectable_id		int=82
		   ,@internal_number	int=8
		   ,@id_sys_stitle		int=0	
		   ,@keeping_data_state int=0
		   ---------------
		   ,@Return				nvarchar(2)
		   ,@ErrMessage			nvarchar(MAX)
		   ,@Output_json		nvarchar(MAX)
		   ,@SP_MODE			nvarchar(1024) ='TEST1'

--*/
	DECLARE @ProcedureName sysname = ISNULL(OBJECT_NAME(@@PROCID),N'usp_ssERP8_SaveDocument')
	DECLARE @nRetValue int = 1, @Status varchar(10) = '0'
	DECLARE @ParamDefinition nvarchar(MAX), @CommandString nvarchar(MAX), @prm_ErrMessage nvarchar(MAX), @ExitCode int, @prm_Return int
		SET @ErrMessage = N''
	DECLARE  @state_table nvarchar(128), @sys_table nvarchar(128), @sys_section_id int, @sys_section_type int, @id_sys_stitle_name nvarchar(128)

	--IF OBJECT_ID('tempdb..#ID_SYS_STITLE','U') is not null DROP TABLE #ID_SYS_STITLE; 	CREATE TABLE #ID_SYS_STITLE ( [ID] int )

	-- 1. Parameters sniffing
		SET @Status += ',1'
		-- @SP_MODE
			--DECLARE @SP_MODE nvarchar(20) = [dbo].[ufn_DefineMode] (@ProcedureName, @id_nmcl_company, @POS, @id_user )
			--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
		-- @POS
			IF NULLIF(@POS,N'') is null SET @POS = HOST_NAME()
		-- @objectable_id	int	-- 1. ID of document type sys_Doctype nomenclature
			IF ISNUMERIC(@objectable_id) <> 1
			OR NOT EXISTS (SELECT 1 FROM [dbo].[sys_doctype] WHERE id=@objectable_id)
				begin SET @ErrMessage += N'(3) Invalid parameter @objectable_id'; SET @nRetValue = 0 end
		-- @internal_number int	-- 2. Internal_number
			IF ISNUMERIC(@internal_number) <> 1
				begin SET @ErrMessage += N'(1) Invalid parameter @internal_number:'+ISNULL(CAST(@internal_number as nvarchar(20)),N'null')+N';'; SET @nRetValue = 0 end
		-- @lang
			DECLARE @lang_id int = (SELECT [id] FROM [dbo].[sys_languages] WHERE [language] = @lang AND [status]=1 )
				SET @lang_id = case when @lang_id is null then 1 else @lang_id end
			IF @lang_id is null	begin SET @ErrMessage += N'(7) Error occurred in parameter @lang='+ISNULL(@lang,N'null'); SET @nRetValue = 0 end
		-- @id_user	int NULL=NULL -- 5. ID of current system user
			IF ISNUMERIC(@id_user) <> 1
			OR NOT EXISTS (SELECT 1 FROM [dbo].[sys_users] WHERE id=@id_user)
				begin SET @ErrMessage += N'(9) Invalid parameter @id_user'; SET @nRetValue = 0 end
		-- functions and procedures		
			IF NOT EXISTS (select 1 from sys.objects where [name] = N'ufn_sdCollectDocumentElements')
				begin SET @ErrMessage += N'(5) Missing function ufn_sdCollectDocumentElements'; SET @nRetValue = 0 end
	
			--IF NOT EXISTS (select * from sys.objects where [name] = N'usp_sdRecord')
			--	begin SET @ErrMessage = N'(5) Missing procedure usp_sdRecord'; 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(@POS,'null')+','										-- 1. Workstation name/Name of POS work point
										 + ISNULL(cast(@id_nmcl_company as varchar(10)),'null')+','		-- 2. ID of current company object
										 + ISNULL(cast(@id_user as varchar(10)),'null')+','				-- 3. ID of current application user
										 + ISNULL(@lang,'null')+','										-- 4. Language of application	
										 + ISNULL(cast(@objectable_id as varchar(10)),'null')+','		-- 5. ID of document type sys_Doctype nomenclature
										 + ISNULL(cast(@internal_number as varchar(10)),'null')+','		-- 6. Internal_number
										 + ISNULL(cast(@id_sys_stitle as varchar(10)),'null')+','		-- 7. ID of inserted record in sys_stitle table
										 + ISNULL(@SP_MODE,'null')
		DECLARE @PrmJson nvarchar(MAX) = (	SELECT	 @POS				as [@POS]						-- 1. Workstation name/Name of POS work point
													,@id_nmcl_company	as [@id_nmcl_company]			-- 2. ID of current company object
													,@id_user			as [@id_user]					-- 3. ID of current application user
													,@lang				as [@lang]						-- 4. Language of application
													,@objectable_id		as [@objectable_id]				-- 5. ID of document type sys_Doctype nomenclature
													,@internal_number	as [@internal_number]			-- 6. Internal_number
													,@id_sys_stitle		as [@id_sys_stitle]				-- 7. ID of inserted record in sys_stitle table
											FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )
		DECLARE @DBName nvarchar(128) = DB_NAME()
					 EXEC [stepsoft].[dbo].[usp_ssTestSaveParam] @PrmMessage = @PrmString, @CallingModule = @ProcedureName, @XMLString = null
																,@Separator = ',', @DataBase = @DBName, @RecordID = @TestSaveParamID OUTPUT
																,@PrmJson = @PrmJson 
		print @ProcedureName+'->SP_MODE = ' + ISNULL(@SP_MODE,'NULL')+' '+@ProcedureName+'->TestSaveParamID = ' + ISNULL(cast(@TestSaveParamID as varchar(10)),'NULL')
																										END	-- IF OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam') is not null AND LEFT(@SP_MODE,4) = N'TEST'		
		IF @SP_MODE=N'TEST1' select  @ProcedureName+N'('+@Status+N')' as [procedure], N'Parameters' as tbl
								,@POS as '@POS', @id_nmcl_company	as '@id_nmcl_company', @id_user as '@id_user', @lang as '@lang'
								,@objectable_id as '@objectable_id', @internal_number as '@internal_number', @id_sys_stitle as '@id_sys_stitle'
								,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'

	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 3. Loading data for elements and tables containing data of document / Прочитане на данните за таблици и полета за този документ
	-- select * from dbo.ufn_sdCollectDocumentElements(19,'bg'); select * from ufn_ssERP8_CollectObjects(2,@objectable_id,null,null,null,null,@lang)
		SET @Status += ',3'
		IF OBJECT_ID('tempdb..#used_fields') is not null DROP TABLE #used_fields
		SELECT * INTO #used_fields	FROM dbo.ufn_sdCollectDocumentElements(@objectable_id,@lang)

	IF @nRetValue = 1	BEGIN
		;WITH CTE -- изтриване на дублираните по имена елементи в една таблица (изтрива след първия )
		   AS	(  SELECT element_name, ROW_NUMBER() OVER (PARTITION BY state_table,sys_table,element_name ORDER BY element_id ) as Duplicate 	FROM #used_fields )
		DELETE FROM CTE WHERE Duplicate > 1

		IF EXISTS ( SELECT 1 FROM #used_fields WHERE ISNULL([element_exists],'0') <> '111111' ) begin SET @ErrMessage = N'(1) Invalid definition of elements'; SET @nRetValue = 0 end
						END	-- IF @nRetValue = 1	-- 3. Loading data for elements

	IF OBJECT_ID('tempdb..#Used_fields') is not null	begin
		IF NOT EXISTS (select 1 from #Used_fields)	
								begin SET @ErrMessage += N'(1) Missing document settings ('+cast(@objectable_id as nvarchar(20))+N')'
									  SET @nRetValue = 0 
									  IF @SP_MODE = 'TEST1' select @ProcedureName+N'('+@Status+N')' as [procedure],'No rows in #Used_fields' as tbl
								end

		ELSE IF EXISTS ( SELECT 1 FROM #used_fields WHERE ISNULL([element_exists],'0') <> '111111' ) and @SP_MODE='TEST1'
			 select  @ProcedureName+N'('+@Status+N')' as [procedure],'Invalid element in #Used_fields' as tbl
					,N'SELECT * FROM dbo.ufn_sdCollectDocumentElements('+ISNULL(cast(@objectable_id as nvarchar(20)),N'null')+N','''+ISNULL(cast(@lang as nvarchar(20)),N'null')+N''')' 
			 ELSE IF @SP_MODE = 'TEST1'	select @ProcedureName+N'('+@Status+N')' as [procedure],'#Used_fields' as tbl, * from #Used_fields
										
														end
	ELSE	begin SET @ErrMessage += N'(1) Error occured when load document settings ('+cast(@objectable_id as nvarchar(20))+N')'; 
				  SET @nRetValue = 0 
				  IF @SP_MODE = 'TEST1' select @ProcedureName+N'('+@Status+N')' as [procedure],'Table #Used_fields was not created' as tbl

			end

		-- 3.a Inserting sys_srow for all row section if sys_srow not exists
		SET @Status += 'a'
			DROP TABLE IF EXISTS #srow_table
			CREATE TABLE #srow_table ([sys_table] nvarchar(128),[state_table] nvarchar(128),[sys_section_id] int,[section_type] int )
			INSERT INTO #srow_table ([sys_table],[state_table],[sys_section_id],[section_type])
					SELECT DISTINCT	 N'sys_srow',uf1.[state_table],uf1.[sys_section_id],uf1.[section_type]
					FROM #used_fields as uf1
						left join #used_fields as uf2 ON uf1.[state_table]=uf2.[state_table] and uf2.[sys_table]=N'sys_srow'
					WHERE uf1.[section_type]=1 AND uf1.[sys_table] is not null AND uf1.[state_table] is not null AND uf2.[sys_table] is null

	  IF @SP_MODE = 'TEST1' AND exists (select 1 from #srow_table)  begin
																	 select @ProcedureName+N'('+@Status+N')' as [procedure],'#srow_table with sys_srow' as tbl,* from #srow_table
																	 --SELECT * from tempdb.sys.columns where object_id('tempdb..#used_fields')=[object_id] AND collation_name is not null
																	 --SELECT * from tempdb.sys.columns where object_id('tempdb..#srow_table')=[object_id] AND collation_name is not null
																	end
	  ELSE IF @SP_MODE = 'TEST1'									 select @ProcedureName+N'('+@Status+N')' as [procedure],'No wows in #srow_table' as tbl

	-- 4. Loading id_sys_stitle from state table / Зареждане на id_sys_stitle от state таблицата 
		SET @Status += ',3'
	IF @nRetValue = 1	BEGIN
		SET @ParamDefinition = N'@internal_number int, @id_sys_stitle int OUTPUT'
		SET @CommandString = N'SELECT @id_sys_stitle=[id_record] FROM [dbo].['
						   + ( select top(1) [state_table_title] from #used_fields )
						   + N'] WHERE [internal_number]=@internal_number AND [is_new]=0'
		IF @SP_MODE = 'TEST1' select @ProcedureName+N'('+@Status+N')' as [procedure],'SCRIPT for init of @id_sys_stitle' as tbl
									,@CommandString as '@CommandString', @ParamDefinition as '@ParamDefinition'	
			EXEC @ExitCode=sp_executeSQL @CommandString, @ParamDefinition
										,@internal_number=@internal_number
										,@id_sys_stitle=@id_sys_stitle OUTPUT
			SET @id_sys_stitle=NULLIF(@id_sys_stitle,0)
						END -- IF @nRetValue = 1	BEGIN 4. Loading id_sys_stitle from state table
	IF @SP_MODE = 'TEST1' select @ProcedureName+N'('+@Status+N')' as [procedure],'@id_sys_stitle before action' as tbl, @id_sys_stitle as '@id_sys_stitle', @nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'

	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 5. Creation META QUERY SCRIPTS
		SET @Status += ',5'
		DECLARE @RootQuery nvarchar(MAX)=cast(N'' as nvarchar(MAX))
		DECLARE @SQLTitle nvarchar(MAX), @SQLRow nvarchar(MAX), @SQLTopTitle nvarchar(MAX), @SQLTopRow nvarchar(MAX)

	IF @nRetValue=1	BEGIN
		IF @SP_MODE='TEST1' 
			SET @RootQuery += cast(N'DECLARE @internal_number int='+ISNULL(cast(@internal_number as nvarchar(20)),N'0')
						   +	   N', @lang nvarchar(255)=N''bg'',  @id_sys_stitle int='+ISNULL(cast(@id_sys_stitle as nvarchar(20)),N'0')+N'
		DROP TABLE IF EXISTS #tblInsertedRowID; CREATE TABLE #tblInsertedRowID ( [action] nvarchar(255), [id_sys_srow] int, [rows_group] int, [id_grid_row] int )
'										as nvarchar(MAX))
			SET @RootQuery += cast(N'/* 1. Deleting data from sys tables */
			<delete_from_sys_tables>
/* 2. Merging State tables AND sys_tables */

	DECLARE @tblInsertedTitleID TABLE ( [action] nvarchar(255), [id_sys_stitle] int )
	DECLARE @tblInsertedTitleID1 TABLE ( [action] nvarchar(255), [id_sys_stitle] int, [id] int )
	DECLARE @tblInsertedRowID TABLE ( [action] nvarchar(255), [id_sys_srow] int, [rows_group] int, [id_grid_row] int )
	DECLARE @tblInsertedRowID1 TABLE ( [action] nvarchar(255), [id_sys_srow] int, [rows_group] int, [id_grid_row] int )

			<Merge_State_Sys_tables>
/* 3. Deleting data from state tables */
			<delete_from_state_tables>
'							as nvarchar(MAX))


	SET @SQLTopTitle = cast(N'/* TopTitle => SECTION ID = <section_ID> SECTION UID = <section_UID> SECTION TYPE = <section_type> SYS_TABLE=sys_stitle STATE_TABLE=<TitleStateTable>*/
			MERGE INTO [dbo].[sys_stitle]										as target_tbl
			USING (	SELECT	<SelectList><SelectString_add>[id_record],[is_new]
					FROM	[dbo].[<TitleStateTable>]		
					WHERE	[<TitleStateTable>].[internal_number]=@internal_number 
					  AND ( [<TitleStateTable>].[id_record] is not null OR [<TitleStateTable>].[is_new]=1 )
				  )																as source_tbl
			ON source_tbl.[id_record] = target_tbl.[id] 
			WHEN NOT MATCHED THEN	INSERT (<SelectList><ServiceField>) VALUES (<SelectList><ServiceValue>)
			WHEN	 MATCHED THEN	UPDATE SET <UpdateList>
				OUTPUT $action, INSERTED.ID INTO @tblInsertedTitleID;
SET @id_sys_stitle = (SELECT TOP(1) [id_sys_stitle] FROM @tblInsertedTitleID)
'							as nvarchar(MAX))
	IF @SP_MODE = 'TEST1'
	SET @SQLTopTitle += cast( N'DROP TABLE IF EXISTS #tblInsertedTitleID; SELECT * INTO #tblInsertedTitleID FROM @tblInsertedTitleID
select ''sys_stitle'' as [tbl],<section_ID> as [SECTION ID],* from #tblInsertedTitleID
'							as nvarchar(MAX))


	SET @SQLTitle = cast(N'/* Title => SECTION ID = <section_ID> SECTION UID = <section_UID> SECTION TYPE = <section_type> SYS_TABLE=<sys_stitle_table> STATE_TABLE=<TitleStateTable>*/
/*DECLARE @internal_number int='+ISNULL(cast(@internal_number as nvarchar(20)),N'0')+N', @lang nvarchar(255)=N''bg'', @id_sys_stitle int='+ISNULL(cast(@id_sys_stitle as nvarchar(20)),N'0')+N';
  DECLARE @tblInsertedTitleID TABLE ( [action] nvarchar(255), [id_sys_stitle] int );
		INSERT INTO @tblInsertedTitleID ([action],[id_sys_stitle]) SELECT [action],[id_sys_stitle] FROM #tblInsertedTitleID
  DECLARE @tblInsertedTitleID1 TABLE ( [action] nvarchar(255), [id_sys_stitle] int, [id] int )
  SET @id_sys_stitle = (SELECT TOP(1) [id_sys_stitle] FROM @tblInsertedTitleID)
--*/
DELETE @tblInsertedTitleID1
			MERGE INTO [dbo].[<sys_stitle_table>]								as target_tbl
			USING (	SELECT	<SelectList><SelectString_add>@id_sys_stitle as [id_record]
					FROM	[dbo].[<TitleStateTable>]	<JOIN_RootStateTitleTable>
					WHERE	[<TitleStateTable>].[internal_number]=@internal_number
				  )																as source_tbl
			ON <TitleTableCondition>
			WHEN NOT MATCHED THEN	INSERT (<SelectList><ServiceField>) VALUES (<SelectList><ServiceValue>)
			WHEN	 MATCHED THEN	UPDATE SET <UpdateList>
			WHEN NOT MATCHED BY SOURCE AND target_tbl.[<id_sys_stitle>] = @id_sys_stitle THEN DELETE
				OUTPUT $action, source_tbl.[id_record], INSERTED.ID INTO @tblInsertedTitleID1;
'							as nvarchar(MAX))
	IF @SP_MODE = 'TEST1'
	SET @SQLTitle += cast( N'select ''<sys_stitle_table>'' as [tbl],<section_ID> as [SECTION ID],* from @tblInsertedTitleID1
'							as nvarchar(MAX))


	SET @SQLTopRow = cast(N'/* TopRow => SECTION ID = <section_ID> SECTION UID = <section_UID> SECTION TYPE = <section_type> SYS_TABLE=<sys_srow_table> STATE_TABLE=<RowStateTable>*/
/*DECLARE @internal_number int='+ISNULL(cast(@internal_number as nvarchar(20)),N'0')+N', @lang nvarchar(255)=N''bg'';
	DECLARE @tblInsertedTitleID TABLE ( [action] nvarchar(255), [id_sys_stitle] int );
		INSERT INTO @tblInsertedTitleID ([action],[id_sys_stitle]) SELECT [action],[id_sys_stitle] FROM #tblInsertedTitleID
	DECLARE @id_sys_stitle INT = (SELECT TOP(1) [id_sys_stitle] FROM @tblInsertedTitleID)
	DECLARE @tblInsertedRowID TABLE ( [action] nvarchar(255), [id_sys_srow] int, [rows_group] int, [id_grid_row] int )
--*/
DELETE @tblInsertedRowID
			MERGE INTO [dbo].[<sys_srow_table>]									as target_tbl   
			USING ( SELECT	<SelectList><SelectString_add>@id_sys_stitle as [id_record],[<RowStateTable>].[rows_group],[<RowStateTable>].[id_grid_row]
					FROM	[dbo].[<RowStateTable>]
					WHERE [<RowStateTable>].[internal_number]=@internal_number
				  )																as source_tbl
			ON <RowTableCondition>  
			WHEN NOT MATCHED THEN	INSERT (<SelectList><ServiceField>) VALUES (<SelectList><ServiceValue>)
			WHEN	 MATCHED THEN	UPDATE SET <UpdateList>
			WHEN NOT MATCHED BY SOURCE AND target_tbl.[id_sys_stitle] = @id_sys_stitle AND target_tbl.[sys_section_id]=<section_ID>	THEN DELETE
				OUTPUT $action, INSERTED.ID, source_tbl.[rows_group], source_tbl.[id_grid_row] INTO @tblInsertedRowID;
'							as nvarchar(MAX))
	IF @SP_MODE = 'TEST1'
	SET  @SQLTopRow += cast( N'IF not exists (SELECT 1 FROM #tblInsertedRowID) BEGIN
								INSERT INTO #tblInsertedRowID ([action], [id_sys_srow], [rows_group], [id_grid_row])
								SELECT [action], [id_sys_srow], [rows_group], [id_grid_row] FROM @tblInsertedRowID
																				 END
select ''<sys_srow_table>'' as [tbl],<section_ID> as [SECTION ID],* from @tblInsertedRowID
'							as nvarchar(MAX))


	SET @SQLRow = cast(N'/* Row => SECTION ID = <section_ID> SECTION UID = <section_UID> SECTION TYPE = <section_type>  SYS_TABLE=<sys_srow_table> STATE_TABLE=<RowStateTable>*/
/*	DECLARE @internal_number int='+ISNULL(cast(@internal_number as nvarchar(20)),N'0')+N', @lang nvarchar(255)=N''bg'';
	DECLARE @tblInsertedTitleID TABLE ( [action] nvarchar(255), [id_sys_stitle] int );
		INSERT INTO @tblInsertedTitleID ([action],[id_sys_stitle]) SELECT [action],[id_sys_stitle] FROM #tblInsertedTitleID;
	DECLARE @id_sys_stitle INT = (SELECT TOP(1) [id_sys_stitle] FROM @tblInsertedTitleID)
	DECLARE @tblInsertedRowID TABLE ( [action] nvarchar(255),[id_sys_srow] int, [rows_group] int, [id_grid_row] int );
		INSERT INTO @tblInsertedRowID ([action],[rows_group],[id_grid_row]) SELECT [action],[rows_group],[id_grid_row] FROM #tblInsertedRowID
	DECLARE @tblInsertedRowID1 TABLE ( [action] nvarchar(255), [id_sys_srow] int, [rows_group] int, [id_grid_row] int )
--*/
DELETE @tblInsertedRowID1
			MERGE INTO [dbo].[<sys_srow_table>]									as target_tbl   
			USING ( SELECT	<SelectList><SelectString_add>@id_sys_stitle as [id_record],[<RowStateTable>].[id_grid_row],[<RowStateTable>].[rows_group],o1.[id_sys_srow]
					FROM	[dbo].[<RowStateTable>]
						LEFT JOIN @tblInsertedRowID	 AS o1 ON o1.[rows_group]=[<RowStateTable>].[rows_group] AND o1.[id_grid_row]=[<RowStateTable>].[id_grid_row]
					WHERE [<RowStateTable>].[internal_number]=@internal_number
				  )																as source_tbl
			ON <RowTableCondition>  
			WHEN NOT MATCHED THEN	INSERT (<SelectList><ServiceField>) VALUES (<SelectList><ServiceValue>)
			WHEN	 MATCHED THEN	UPDATE SET <UpdateList>
			WHEN NOT MATCHED BY SOURCE AND target_tbl.[id_sys_stitle] = @id_sys_stitle AND target_tbl.[sys_section_id]=<section_ID>	THEN DELETE
				OUTPUT $action, source_tbl.[id_sys_srow], source_tbl.[rows_group], source_tbl.[id_grid_row] INTO @tblInsertedRowID1;
'							as nvarchar(MAX))
	IF @SP_MODE = 'TEST1'
	SET @SQLRow += cast( N'select ''<sys_srow_table>'' as [tbl],<section_ID> as [SECTION ID],* from @tblInsertedRowID1
'							as nvarchar(MAX))


					END -- IF @nRetValue=1	BEGIN -- 5. Creation META QUERY SCRIPTS	
	IF @SP_MODE = 'TEST1' select @ProcedureName+N'('+@Status+N')' as [procedure],'QUERY STATE->SYS' as tbl
								,@RootQuery as '@RootQuery', @SQLTopTitle as '@SQLTopTitle', @SQLTitle as '@SQLTitle', @SQLTopRow as '@SQLTopRow', @SQLRow as '@SQLRow'

	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 6. Analysis of #used_fields table AND creation of substitution variables in commands
	DECLARE @Subst_variables TABLE ( StateTable nvarchar(128), SysTable nvarchar(128), JoinString nvarchar(MAX)
									,SelectString nvarchar(MAX), UpdateString nvarchar(MAX), SelectString_add nvarchar(MAX)
									,ServiceField nvarchar(MAX), ServiceValue nvarchar(MAX)
									,MERGE_condition nvarchar(MAX)
									,id_sys_stitle_name nvarchar(128)
									,Section_type int, Section_ID nvarchar(255), Section_UID nvarchar(255))
		SET @Status += ',6'
	IF @nRetValue=1	BEGIN
		DECLARE  @JoinString nvarchar(MAX), @SelectString nvarchar(MAX), @UpdateString nvarchar(MAX), @SelectString_add nvarchar(MAX), @ServiceFields nvarchar(MAX), @ServiceValue nvarchar(MAX), @MERGE_condition nvarchar(MAX), @MERGE_condition_srow nvarchar(MAX)
				,@Section_UID nvarchar(255)
		DECLARE @tblServiceFields	 TABLE ( [ServiceField] nvarchar(128)  )
		DECLARE @tblServiceValues	 TABLE ( [ServiceValue] nvarchar(1024) )
		DECLARE @tblSelectString_add TABLE ( [ServiceField] nvarchar(128)  )

		DECLARE table_cursor CURSOR FOR (	
											SELECT DISTINCT	[sys_table] COLLATE database_default,[state_table] COLLATE database_default,[sys_section_id],[section_type]
											FROM #used_fields
											WHERE [sys_table] is not null AND [state_table] is not null
										UNION ALL 
											SELECT [sys_table] COLLATE database_default,[state_table] COLLATE database_default,[sys_section_id],[section_type]
											FROM #srow_table
										 ) 
			OPEN table_cursor;	FETCH NEXT FROM table_cursor INTO  @sys_table ,@state_table, @sys_section_id, @sys_section_type
				WHILE (@@FETCH_STATUS <> -1)	BEGIN
				IF	  (@@FETCH_STATUS <> -2)	
												BEGIN
				-- LOOP in some cases
				IF EXISTS ( SELECT 1 FROM @Subst_variables 
							WHERE StateTable=@State_Table AND SysTable= @Sys_Table AND Section_ID=@sys_section_id AND Section_type = @sys_section_type) -- има вече анализ за тази двойка таблици и секция
				OR COL_LENGTH(@state_table,N'id_record') is null																						-- в state таблицата няма колона за връзка	
							begin -- прескача този ред
								FETCH NEXT FROM table_cursor INTO  @sys_table ,@state_table, @sys_section_id, @sys_section_type; CONTINUE
							end

					-- service fields
					DELETE @tblServiceFields; DELETE @tblServiceValues; DELETE @tblSelectString_add
					-- id_sys_stitle
					   IF COL_LENGTH(@sys_table,N'id_sys_stitle')	is not null 
						  begin 
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'id_sys_stitle')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (N'@id_sys_stitle')
					      end
					-- id_sys_srow
						IF	COL_LENGTH(@sys_table,N'id_sys_srow')	is not null
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'id_sys_srow')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (N'id_sys_srow')
						  end	
					-- sys_section_id
						IF	COL_LENGTH(@sys_table,N'sys_section_id')	is not null 
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'sys_section_id')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (cast(ISNULL(@sys_section_id,0) as nvarchar(20)))						
						  end		
					-- created_at
						IF	COL_LENGTH(@sys_table,N'created_at')	is not null AND COL_LENGTH(@state_table,N'created_at') is not null
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'created_at')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES ( 'N'''+convert( nvarchar(50),GetDate(),121)+N'''' )
						  end		
					-- updated_at
						IF	COL_LENGTH(@sys_table,N'updated_at')	is not null AND COL_LENGTH(@state_table,N'updated_at') is not null
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'updated_at')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES ( 'N'''+convert( nvarchar(50),GetDate(),121)+N'''' )
						  end	
					-- sys_updated_at
						IF	COL_LENGTH(@sys_table,N'sys_updated_at')	is not null AND COL_LENGTH(@state_table,N'sys_updated_at') is not null
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'sys_updated_at')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES ('sys_updated_at' )
						  end							  
					-- updated_by
						IF	COL_LENGTH(@sys_table,N'updated_by') is not null 
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'updated_by')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES ( cast(ISNULL(@id_user,0) as nvarchar(20)) )
						  end		
					-- created_by
						IF	COL_LENGTH(@sys_table,N'created_by') is not null AND COL_LENGTH(@state_table,N'created_by') is not null
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'created_by')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES ( cast(ISNULL(@id_user,0) as nvarchar(20)) )
						  end	
					-- id_parent_grid_row 
						IF	COL_LENGTH(@sys_table,N'id_parent_grid_row') is not null AND COL_LENGTH(@state_table,N'id_parent_grid_row') is not null
						  begin 
							INSERT INTO @tblSelectString_add ([ServiceField]) VALUES (N'[id_parent_grid_row]')
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'id_parent_grid_row')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (N'['+@state_table+N'].[id_parent_grid_row]' )
						  end
					-- 	id_grid_row
						IF	COL_LENGTH(@sys_table,N'id_grid_row') is not null AND COL_LENGTH(@state_table,N'id_grid_row') is not null
						  begin 
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'id_grid_row')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (N'id_grid_row')
						  end
					-- rows_group
						IF	COL_LENGTH(@sys_table,N'rows_group') is not null AND COL_LENGTH(@state_table,N'rows_group') is not null
						  begin 
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'rows_group')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (N'rows_group')
						  end
					-- 	lang
						IF	COL_LENGTH(@sys_table,N'lang') is not null AND COL_LENGTH(@state_table,'lang') is not null
						  begin
							INSERT INTO @tblSelectString_add ([ServiceField]) VALUES (N'[lang]')
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'lang')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (N'[lang]') -- --cast(N',''' as nvarchar(MAX))+cast(@lang as nvarchar(MAX))+cast(N'''' as nvarchar(MAX)) 
						  end
					-- 	id_doctype
						IF	COL_LENGTH(@sys_table,N'id_doctype') is not null
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'id_doctype')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (cast(@objectable_id as nvarchar(20)))
						  end
					-- 	id_user
						IF	COL_LENGTH(@sys_table,N'id_user') is not null
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'id_user')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (cast(@id_user as nvarchar(20)))
						  end
					-- 	[active]
						IF	COL_LENGTH(@sys_table,N'active') is not null
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'active')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (N'1')
						  end
					-- 	[is_annul]
						IF	COL_LENGTH(@sys_table,N'is_annul') is not null
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'is_annul')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (N'0')
						  end
					-- 	[is_deleted]
						IF	COL_LENGTH(@sys_table,N'is_deleted') is not null
						  begin
							INSERT INTO @tblServiceFields ([ServiceField]) VALUES (N'is_deleted')
							INSERT INTO @tblServiceValues ([ServiceValue]) VALUES (N'0')
						  end
					SELECT @ServiceFields = STRING_AGG( cast([ServiceField] as nvarchar(MAX)),N',') FROM @tblServiceFields WHERE [ServiceField] is not null
					SELECT @ServiceValue  = STRING_AGG( cast(ISNULL([ServiceValue],N'''') as nvarchar(MAX)),N',') FROM @tblServiceValues 
					SELECT @SelectString_add = STRING_AGG( cast([ServiceField] as nvarchar(MAX)),N',') FROM @tblSelectString_add WHERE [ServiceField] is not null
					   SET @SelectString_add = ISNULL(@SelectString_add,N'')

					-- select string
					IF	@sys_section_type = 0	-- field
						SELECT @SelectString=STRING_AGG(CONVERT(NVARCHAR(max),N'['+[element_name] COLLATE database_default+N']'), CHAR(44)) 
						FROM #used_fields as uf
							LEFT JOIN @tblServiceFields as sf ON sf.ServiceField COLLATE database_default=uf.element_name COLLATE database_default
						WHERE uf.state_table=@state_table AND uf.sys_table=@sys_table
						  AND sf.ServiceField is null -- AND uf.sys_section_id=@sys_section_id
					ELSE						-- grid	
						SELECT @SelectString=STRING_AGG(CONVERT(NVARCHAR(max),N'['+[element_name] COLLATE database_default+N']'), CHAR(44)) 
						FROM #used_fields as uf
							LEFT JOIN @tblServiceFields as sf ON sf.ServiceField COLLATE database_default=uf.element_name COLLATE database_default
						WHERE uf.state_table=@state_table AND uf.sys_table=@sys_table AND uf.sys_section_id=@sys_section_id
						  AND sf.ServiceField is null
					-- ПРОБЛЕМА ЗАПЕТАИ	----------------------------------------------------------------------------------------------------------
					-- <SelectList><SelectString_add>.......
					-- <SelectList><ServiceField>
					-- <SelectList><ServiceValue>
					-- assembly -- предполага се че винаги има нещо СЛЕД @SelectString в скриптовете
					--/*
					SET @SelectString =	 ISNULL(@SelectString,N'')
						SET @ServiceFields = case when @SelectString=N'' AND @ServiceFields=N'' then	cast(N'' as nvarchar(MAX)) -- това ще генерира грешка
												  when @SelectString=N''						then	@ServiceFields
												  when						 @ServiceFields=N''	then	N''
												  else													cast(N',' as nvarchar(MAX)) + @ServiceFields
											  end
						SET @ServiceValue  = case when @SelectString=N'' AND @ServiceValue=N''  then	cast(N'' as nvarchar(MAX)) -- това ще генерира грешка
												  when @SelectString=N''						then	@ServiceValue
												  when						 @ServiceValue=N''	then	N''
												  else													cast(N',' as nvarchar(MAX)) + @ServiceValue
											  end
						SET @SelectString_add = case when @SelectString=N'' AND @SelectString_add=N'' then cast(N'' as nvarchar(MAX)) -- това НЯМА да генерира грешка
													 when @SelectString=N''							  then @SelectString_add + cast(N',' as nvarchar(MAX))
													 when						@SelectString_add=N'' then cast(N',' as nvarchar(MAX))
													 else												   cast(N',' as nvarchar(MAX))+@SelectString_add+cast(N',' as nvarchar(MAX))
											  end
					--*/
					------------------------------------------------------------------------------------------------------------------------------
					-- update string
					SET @UpdateString = cast(N'' as nvarchar(MAX))
						IF	@sys_section_type= 0	-- field
							SELECT @UpdateString +=STRING_AGG(CONVERT(NVARCHAR(max),+N'['+[element_name] COLLATE database_default+N']=[source_tbl].['+[element_name] COLLATE database_default+N']'), CHAR(44)) 
							FROM #used_fields as uf
								LEFT JOIN @tblServiceFields as sf ON sf.ServiceField COLLATE database_default=uf.element_name COLLATE database_default
							WHERE uf.state_table=@state_table AND uf.sys_table=@sys_table
							  AND sf.ServiceField is null	-- AND uf.sys_section_id=@sys_section_id	
						ELSE 						-- grid	
							SELECT @UpdateString +=STRING_AGG(CONVERT(NVARCHAR(max),[element_name] COLLATE database_default+N'=source_tbl.'+[element_name] COLLATE database_default), CHAR(44)) 
							FROM #used_fields as uf
								LEFT JOIN @tblServiceFields as sf ON sf.ServiceField COLLATE database_default=uf.element_name COLLATE database_default
							WHERE uf.state_table=@state_table AND uf.sys_table=@sys_table AND uf.sys_section_id=@sys_section_id
							  AND sf.ServiceField is null		
					SET @UpdateString = ISNULL(@UpdateString,N'')

						-- sys_updated_at
						IF	COL_LENGTH(@sys_table,N'sys_updated_at')	is not null
								begin SET @UpdateString += case when @UpdateString <> N'' then	cast(N',[sys_updated_at] = '''+convert( nvarchar(50),GetDate(),121)+N'''' as nvarchar(MAX)) 
																else							cast(N'[sys_updated_at] = '''+convert( nvarchar(50),GetDate(),121)+N'''' as nvarchar(MAX)) 
															end
								end	
						-- updated_at
						IF	COL_LENGTH(@sys_table,N'updated_at')	is not null
								begin SET @UpdateString += case when @UpdateString <> N'' then	cast(N',[updated_at] = '''+convert( nvarchar(50),GetDate(),121)+N'''' as nvarchar(MAX)) 
																else							cast(N'[updated_at] = '''+convert( nvarchar(50),GetDate(),121)+N'''' as nvarchar(MAX)) 
															end
								end									
						-- updated_by
						IF	COL_LENGTH(@sys_table,N'updated_by')	is not null
								begin SET @UpdateString += case when @UpdateString <> N'' then	cast(N',[updated_by] = '+cast(ISNULL(@id_user,0) as nvarchar(20)) as nvarchar(MAX)) 
																else							cast(N'[updated_by] = '+cast(ISNULL(@id_user,0) as nvarchar(20)) as nvarchar(MAX)) 
															end				
								end
						-- id_sys_srow
						IF	COL_LENGTH(@sys_table,N'id_sys_srow')	is not null
								begin SET @UpdateString += case when @UpdateString <> N'' then	cast(N',[id_sys_srow] = [source_tbl].[id_sys_srow]' as nvarchar(MAX)) 
																else							cast(N'[id_sys_srow] = [source_tbl].[id_sys_srow]' as nvarchar(MAX)) 
															end				
								end

						-- join string
						SET @JoinString = N'JOIN @tblInsertedTitleID as o ON	['+@state_table+N'].[id_record] = [o].[id_sys_stitle]  OR ['+@state_table+N'].[is_new]=1'
											 
						-- UID 
						SELECT TOP(1) @Section_UID=[UID] FROM [dbo].[sys_sections] WHERE [id]=@sys_section_id 
						SET @Section_UID=ISNULL(@Section_UID,N'n/a')
						 
						-- MERGE_condition дефинира MATCH условието на оператора MERGE (Merge_State_Sys_tables)
						SET @MERGE_condition = cast(N'1=0' as nvarchar(MAX))
							-- заглавие
							IF @sys_section_type=0 AND @sys_table=N'sys_stitle' 
								SET @MERGE_condition = cast( N'source_tbl.[id_record]=target_tbl.[id]'
															as nvarchar(MAX))
							-- редове
							ELSE /*1,6*/	 IF 	COL_LENGTH(@state_table,N'id_grid_row') is not null AND COL_LENGTH(@sys_table,N'id_grid_row') is not null
								SET @MERGE_condition = cast( N'[source_tbl].[id_record]=[target_tbl].[id_sys_stitle] AND [source_tbl].[id_grid_row]=[target_tbl].[id_grid_row]'
															as nvarchar(MAX))
							-- заглавие
							IF @sys_section_type=0 AND @sys_table<>N'sys_stitle' 
								SET @MERGE_condition = cast( N'source_tbl.[id_record]=target_tbl.[id_sys_stitle]'
															as nvarchar(MAX))
							-- редове
							ELSE /*1,6*/	 IF 	COL_LENGTH(@sys_table,N'sys_section_id') is not null 
								SET @MERGE_condition += cast( N' AND [target_tbl].[sys_section_id]='+cast(@sys_section_id as nvarchar(20))
															as nvarchar(MAX))
							-- заглавие																
							IF @sys_section_type=0 AND COL_LENGTH(@state_table,N'lang') is not null AND COL_LENGTH(@sys_table,N'lang') is not null
								SET @MERGE_condition += cast( N' AND source_tbl.[lang]=target_tbl.[lang]'
															as nvarchar(MAX))
							-- редове
							ELSE /*1,6*/	 IF COL_LENGTH(@state_table,N'lang') is not null AND COL_LENGTH(@sys_table,N'lang') is not null 
								SET @MERGE_condition += cast( N' AND source_tbl.[lang]=target_tbl.[lang]'
															as nvarchar(MAX))

						-- id_sys_stitle_name
							SET @id_sys_stitle_name = case when @sys_table=N'sys_stitle' then N'id' else N'id_sys_stitle' end




		-- record into table							
	INSERT INTO @Subst_variables 
(   StateTable,  SysTable, JoinString, SelectString, UpdateString,  ServiceField, ServiceValue, SelectString_add,     Section_type,	  Section_ID, Section_UID,   MERGE_condition, id_sys_stitle_name)
	VALUES	
( @State_Table,@Sys_Table,@JoinString,ISNULL(@SelectString,N''),ISNULL(@UpdateString,N''),@ServiceFields,@ServiceValue,@SelectString_add,@sys_section_type,@sys_section_id,@Section_UID,@MERGE_condition,@id_sys_stitle_name)

												END
								FETCH NEXT FROM table_cursor INTO  @sys_table ,@state_table, @sys_section_id, @sys_section_type
												END
			CLOSE table_cursor; DEALLOCATE table_cursor

		-- Това премахва join стринга за TopTitle. По този критери JoinString='' се подават TopTitle
		UPDATE TOP(1) @Subst_variables SET JoinString =N'' WHERE SysTable=N'sys_stitle'
					END	-- 	IF @nRetValue=1	-- 6. Analysis of #used_fields table AND creation of substitution variables in commands

	--DROP TABLE IF EXISTS #tmp; select * into #tmp from @Subst_variables 
	IF @SP_MODE = 'TEST1' select @ProcedureName+N'('+@Status+N')' as [procedure],'@Subst_variables' as tbl,* FROM @Subst_variables order by StateTable, SysTable


	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 7. Creating SQL script to transfer data between state and sys tables / Създаване на скрипт за прехвърляне на данни между state и sys таблици
		SET @Status += ',7'
	IF @nRetValue=1	BEGIN
		-- 7.1. Deleting data from sys tables
			SET @Status += ',7.1.'
		DECLARE @delete_from_sys_tables nvarchar(MAX) = cast(N'' as nvarchar(MAX))
		SELECT @delete_from_sys_tables += cast( ISNULL(N'
	DELETE FROM '+[SysTable]+' WHERE [id_sys_stitle] = @id_sys_stitle'--+cast(@id_sys_stitle as nvarchar(20))+'
,N'')														as nvarchar(MAX))
		FROM (SELECT DISTINCT [SysTable] FROM @Subst_variables WHERE [MERGE_condition]=N'1=0'  /*[Section_type]=1 OR [Section_type]=2*/ 
			 ) as tbl 
	SET @RootQuery = REPLACE( @RootQuery, N'<delete_from_sys_tables>', @delete_from_sys_tables )

		-- 7.2. Merging State tables AND sys_tables
			SET @Status += ',7.2.'
		DECLARE @Merge_State_Sys_tables nvarchar(MAX) = cast(N'' as nvarchar(MAX))
		SELECT @Merge_State_Sys_tables += cast(N'
'			+	case
					when Section_type = 0 AND NULLIF(JoinString,N'') is null 
										then REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @SQLTopTitle
												, N'<TitleStateTable>',StateTable)
												, N'<TitleTableCondition>', MERGE_condition)
												, N'<SelectList>',SelectString)
												, N'<ServiceField>',ServiceField)
												, N'<ServiceValue>',ServiceValue)
												, N'<SelectString_add>',SelectString_add)
												, N'<UpdateList>', UpdateString)
												, N'<Section_ID>', Section_ID)
												, N'<Section_UID>', Section_UID)
												, N'<Section_TYPE>', Section_Type)

					when Section_type = 0 
										then REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @SQLTitle
												, N'<sys_stitle_table>',SysTable)
												, N'<TitleStateTable>',StateTable)
												, N'<JOIN_RootStateTitleTable>', JoinString)
												, N'<TitleTableCondition>', MERGE_condition)
												, N'<SelectList>',SelectString)
												, N'<ServiceField>',ServiceField)
												, N'<ServiceValue>',ServiceValue)
												, N'<SelectString_add>',SelectString_add)
												, N'<UpdateList>', UpdateString)
												, N'<Section_ID>', Section_ID)
												, N'<Section_UID>', Section_UID)
												, N'<Section_TYPE>', Section_Type)
												, N'<id_sys_stitle>', id_sys_stitle_name)


					when Section_type IN (1,6) AND [SysTable] = N'sys_srow'
										then REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @SQLTopRow
												, N'<sys_srow_table>',SysTable)
												, N'<RowStateTable>',StateTable)
												, N'<JOIN_RootStateTitleTable>', JoinString)
												, N'<RowTableCondition>', MERGE_condition)
												, N'<SelectList>',SelectString)
												, N'<ServiceField>',ServiceField)
												, N'<ServiceValue>',ServiceValue)
												, N'<SelectString_add>',SelectString_add)
												, N'<UpdateList>', UpdateString)
												, N'<Section_ID>', Section_ID)
												, N'<Section_UID>', Section_UID)
												, N'<Section_TYPE>', Section_Type)

					when Section_type IN (1,6)
										then REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @SQLRow
												, N'<sys_srow_table>',SysTable)
												, N'<RowStateTable>',StateTable)
												, N'<JOIN_RootStateTitleTable>', JoinString)
												, N'<RowTableCondition>', MERGE_condition)
												, N'<SelectList>',SelectString)
												, N'<ServiceField>',ServiceField)
												, N'<ServiceValue>',ServiceValue)
												, N'<SelectString_add>',SelectString_add)
												, N'<UpdateList>', UpdateString)
												, N'<Section_ID>', Section_ID)
												, N'<Section_UID>', Section_UID)
												, N'<Section_TYPE>', Section_Type)


					else		N'error:'+SysTable+N','+StateTable+N','+Section_ID+N','+cast(Section_type as nvarchar(20))
				end								as nvarchar(MAX))
		FROM @Subst_variables
		WHERE SelectString<> N'' OR ServiceField <> N''
		ORDER BY [Section_type],[StateTable],[SysTable],[JoinString]
	SET @RootQuery = REPLACE( @RootQuery, N'<Merge_State_Sys_tables>', @Merge_State_Sys_tables )
	--SET @RootQuery = @Merge_State_Sys_tables 

		-- 7.3. Deleting data from state tables
			SET @Status += ',7.3.'
		   DECLARE @delete_data_state bit = case when @keeping_data_state = 1 then 0 else 1 end 
		   DECLARE @delete_from_state_tables nvarchar(MAX) = cast(N'' as nvarchar(MAX))
			SELECT @delete_from_state_tables += cast( N'
	DELETE FROM '+[StateTable]+' WHERE [internal_number] = @internal_number
'												as nvarchar(MAX))
		FROM (SELECT DISTINCT [StateTable] FROM @Subst_variables 
			 ) as tbl 
			
			IF @SP_MODE = N'TEST1' OR @delete_data_state=0	SET @delete_from_state_tables = N'' -- не добавям изтриването
	
			SET @RootQuery = REPLACE( @RootQuery, N'<delete_from_state_tables>', @delete_from_state_tables )

					END	-- 	IF @nRetValue=1	-- 7. Creating SQL script to transfer data between state and sys tables

	IF @SP_MODE = 'TEST1' select @ProcedureName+N'('+@Status+N')' as [procedure], 'SCRIPT BEFORE EXEC' as tbl
								,@RootQuery as '@RootQuery', @internal_number as '@internal_number',@lang as '@lang', @id_sys_stitle as '@id_sys_stitle'
								,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
	-- запазване на генерирания стринг в таблицата с документите ако е в режим на тест
	-- ALTER TABLE [dbo].[sys_docType] ADD [StateSysSQL] nvarchar(MAX)
	IF @SP_MODE = 'TEST' AND EXISTS ( SELECT 1 FROM [dbo].[sys_docType] WHERE [id]=@objectable_id )
			UPDATE [dbo].[sys_docType] SET [StateSysSQL] = @RootQuery WHERE [id]=@objectable_id

	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 8. Execution of @RootQuery / Изпълнение на скрипта за прехвърляне на данни между state и sys таблици
		SET @Status += ',8'
	IF @nRetValue=1	AND @SP_MODE <> N'TEST1'	BEGIN
		DECLARE @sys_updated_at datetime = GetDate()
		DROP TABLE IF EXISTS #tblInsertedRowID; CREATE TABLE #tblInsertedRowID ( [action] nvarchar(255), [id_sys_srow] int, [rows_group] int, [id_grid_row] int )
		SET @ParamDefinition = N'@internal_number int, @lang nvarchar(255), @id_sys_stitle int OUTPUT, @sys_updated_at datetime'
		EXEC @ExitCode = sp_executeSQL	 @RootQuery, @ParamDefinition
										,@internal_number=@internal_number
										,@lang=@lang
										,@id_sys_stitle=@id_sys_stitle OUTPUT
										,@sys_updated_at=@sys_updated_at
		SET @Output_json = ( select  @id_sys_stitle as [id_sys_stitle]
									,(select [id_sys_srow] from #tblInsertedRowID) as [rows_id]
							 for json path, root('Data') )
												END -- IF @nRetValue=1	AND @SP_MODE <> N'TEST1'	BEGIN	-- 8. Execution of @RootQuery	


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
	-- 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(128) = ISNULL(ERROR_PROCEDURE(),@ProcedureName)+ISNULL(N'-'+@Status,N'')
		DECLARE @ERROR_MESSAGE nvarchar(2048) = ISNULL(ERROR_MESSAGE(),N'n/a')
		SET @ErrMessage = isnull(@ErrMessage+N',',N'')																																						-- 33.Флаг за временно блокиране
							 +N'SQL Msg: ' + ISNULL(cast(ERROR_NUMBER() as nvarchar(20)),N'')	+N','
							 +N' Level ' + ISNULL(cast(ERROR_SEVERITY() as nvarchar(20)),N'')	+N','
							 +N' State ' + ISNULL(cast(ERROR_STATE() as nvarchar(20)),N'')		+N','
							 +N' Proc ' + @ERROR_PROCEDURE									
							 +N'('+ISNULL('Line: '+cast(ERROR_LINE() as nvarchar(20)),N'')		+N'),'
							 + @ERROR_MESSAGE		
		SET @nRetValue = 0 
END CATCH
	-- Return of result

	-- Return of result
	IF @nRetValue > 1 SET @nRetValue = 1
	SET @Return = @nRetValue
	
	-- Връщане на грешката към приложениетo
	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 -- Определяне на перформънс
	/*
	НОВ ВАРИАНТ
			[2024-05-16 13:03:18] local.INFO: array (
		  0 => 
		  array (
		    'Return' => '1',
		    'Error_json' => NULL,
		    'Output_json' => '{"Data":[{"id_sys_stitle":2759}]}',
		  ),
		)  
	Второто е вашата процедура, която сега използваме			
		[2024-05-16 13:04:00] local.INFO: array (
		  0 => 
		  array (
		    'result' => '1',
		    'message' => '',
		    'id_sys_stitle' => '1677',
		  ),
		)  
	*/
	-- SELECT @nRetValue as [Return], @ErrMessage as [Error_json], @Output_json as [Output_json] 
	SELECT	 @nRetValue as [Return], @ErrMessage as [message], @id_sys_stitle as [id_sys_stitle]
			,@ErrMessage as [Error_json], @Output_json as [Output_json] 
	RETURN @nRetValue

END	-- End of procedure