SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* select * from sys_nmcl
-- select * from state_form_387_grid_1997
EXEC [dbo].[usp_ssERP8_PageNMCLfromState]
   @nmcl_name		=N'nmcl_stuff'		-- 1. Name of nomenclature from sys_nmcl
  ,@internal_number	=214				-- 2. Value of internal_number for state tables.
  ,@Page_num		=2					-- 3. Number of page in result
  ,@Rows_in_page	=10					-- 4. Number of rows in one page
  ,@OrderBy_json	=					-- 6. Define order of results
		N'[{"column":"id_grid_row","position":1,"ordered":0},{"column":"id_nmcl_StuffGroup","position":2,"ordered":0}]'
  ,@lang			=N'bg'				-- 5. Current language from sys_language table
  ,@SP_MODE			=N'TEST1'			-- 8. Execution mode
--*/
DROP PROCEDURE IF EXISTS [dbo].[usp_ssERP8_PageNMCLfromState]
GO
-- =========================================================================================================================
CREATE PROCEDURE 
-- Name			
				[dbo].[usp_ssERP8_PageNMCLfromState]
-- Author		Щерю Щерев																									
-- Create date	26/08/2025
-- Version		
--
-- Serever		MS SQL 2016																										
-- Description	Pagination of the STATE table of a nomenclature and return json with the records from a given page
-- Returns		0 - fail 1 - success
-- Parameters
	    @nmcl_name				nvarchar(128)						-- 1. Name of nomenclature from sys_nmcl
	   ,@internal_number		int									-- 2. Value of internal_number for state tables.
	   ,@nmcl_rows_group_id		int				NULL=NULL		    -- 3. ID of group token from nmcl_table
	   ,@Page_num				int				NULL=NULL			-- 4. Number of page in result
	   ,@Rows_in_page			int				NULL=NULL			-- 5. Number of rows in one page
	   ,@OrderBy_json			nvarchar(MAX)	NULL=NULL			-- 6. Define order of results
																	-- [{"column":"id_grid_row","position":1,"ordered":0},{"column":"id_record","position":2,"ordered":1}]
																	-- position=place in order clause
																	-- ordered=0 ASC,=1 DESC
	   ---------------------------------------------
	   ,@lang					nvarchar(255)	NULL=N'bg'			-- 7. Current language from sys_language table
	   ,@ErrMessage				nvarchar(MAX)	NULL=NULL OUTPUT	-- 8. Text of error/message
	   ,@Return					int				NULL=NULL OUTPUT	-- 9. Return value	(0 - fail 1 - success)
	   ,@SP_MODE				nvarchar(255)	NULL=N'EXECUTE'		-- 10. Execution mode
-- Constraction 
-- Tables		sys_nmcl, all tables containing data of nomenclature
--=========================================================================================================================
AS BEGIN
/* manualUpdateFlag: No */
SET NOCOUNT ON
BEGIN TRY
/*
DECLARE
   @nmcl_name			nvarchar(128) = N'nmcl_company'	-- 1. Name of nomenclature from sys_nmcl
  ,@internal_number		int			  = 422					-- 2. Value of internal_number for state tables.
  ,@Page_num			int			  = 1					-- 3. Number of page in result
  ,@Rows_in_page		int			  = 10					-- 4. Number of rows in one page
  ,@lang				nvarchar(255) =N'bg'				-- 5. Current language from sys_language table
  ,@ErrMessage			nvarchar(MAX) =N''					-- 6. Text of error/message
  ,@Return				int									-- 7. Return value	(0 - fail 1 - success)
  ,@SP_MODE				nvarchar(255) =N'TEST1'				-- 8. Execution mode

select * from state_form_383_grid_1983 where internal_number=422
--*/
	DECLARE @nRetValue int=1, @ExitCode int, @Status nvarchar(255) = N'0'
	DECLARE @ParmDefinition nvarchar(MAX), @CommandString nvarchar(MAX)
	DECLARE @SQLstr nvarchar(MAX) = cast(N'' as nvarchar(MAX))
	DECLARE @ProcedureName sysname = ISNULL(OBJECT_NAME(@@PROCID),N'usp_ssERP8_PageNMCLfromState'), @POS nvarchar(130) = HOST_NAME()
	DECLARE @state_table nvarchar(128)
		SET @ErrMessage = cast(N'' as nvarchar(MAX))
	DECLARE @test_d datetime = GetDate()

	-- 1. Parameters sniffing
		SET @Status += ',1'
print @Status+N'=>'+cast(DATEDIFF(ms,@test_d,GetDate()) as nvarchar(20)); set @test_d=GETDATE()
		--@SP_MODE	
			SET @SP_MODE = case when @SP_MODE=N'TEST1'																						then N'TEST1' 
								when @SP_MODE=N'TEST'																						then N'TEST' 
								when exists ( select 1 from [dbo].[sys_global_settings] where [name]=N'SP_MODE' AND [value]=N'TEST')		then N'TEST'
								when exists ( select 1 from [dbo].[sys_global_settings] where [name]=N'SP_MODE' AND [value]=@ProcedureName)	then N'TEST'
								else																											 N'EXECUTE' 
							end

		-- @nmcl_name
			DECLARE @sys_nmcl_id int, @nmcl_table_lang nvarchar(1024)
			IF ISNULL(ISNUMERIC(@nmcl_name),0) = 1	-- that is ID
				SELECT TOP(1)
					   @sys_nmcl_id = n.[id]
					  ,@nmcl_name=n.[tablename]
					  ,@nmcl_table_lang = ISNULL(tr.[value],N'Not translated') 
				FROM [dbo].[sys_nmcl] as n
					LEFT JOIN sys_translations as tr ON tr.[label] = n.[label] AND tr.[device]=N'web' AND tr.[filename]=N'nomenclature' AND tr.[locale] = @lang
				WHERE n.[id] = cast(@nmcl_name as int) AND tr.[locale] = @lang 
			ELSE /*@nmcl_name is NON numeric value*/
				SELECT TOP(1)
					   @sys_nmcl_id = n.[id]
					  ,@nmcl_name=n.[tablename]
					  ,@nmcl_table_lang = ISNULL(tr.[value],N'Not translated') 
				FROM [dbo].[sys_nmcl] as n
					LEFT JOIN sys_translations as tr ON tr.[label] = n.[label] AND tr.[device]=N'web' AND tr.[filename]=N'nomenclature' AND tr.[locale] = @lang
				WHERE n.[tablename] COLLATE database_default = @nmcl_name COLLATE database_default
			IF	NULLIF(@nmcl_name,N'') is null begin SET @ErrMessage += N';Missing nomenclature id parameter '+cast(@sys_nmcl_id as nvarchar(20)); SET @nRetValue = 0 end
		-- @nmcl_rows_group_field	    -- 3. Name of group token from nmcl_table 
		-- @nmcl_rows_group_id		    -- 4. ID of group token from nmcl_table
			IF ISNULL(@nmcl_rows_group_id,-1) < 0 SET @nmcl_rows_group_id=null
		-- @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
		-- @OrderBy_json
			DECLARE @OrderBy nvarchar(MAX) = N'id_grid_row'
			DECLARE @tblOrderBy TABLE ( [column] nvarchar(128), [position] int, [ordered] bit DEFAULT 0 )
			IF ISNULL(ISJSON(@OrderBy_json),0)=1 begin
				INSERT INTO	@tblOrderBy ( [column], [position], [ordered] )  
				SELECT * FROM OPENJSON(@OrderBy_json) WITH ( [column] nvarchar(128) '$.column', [position] int '$.position', [ordered] bit '$.ordered' )
			SELECT @OrderBy = STRING_AGG([column]+N' '+case when [ordered]=0 then N'ASC' else N'DESC' end,N',') WITHIN GROUP ( ORDER BY [position] ASC )
			FROM @tblOrderBy WHERE [column] is not null AND [position] is not null
			SET @OrderBy = ISNULL(@OrderBy,N'id_grid_row')
												 end -- IF ISNULL(ISJSON(@OrderBy_json),0)=1

		-- 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'
print @Status+N'=>'+cast(DATEDIFF(ms,@test_d,GetDate()) as nvarchar(20)); set @test_d=GETDATE()
		DECLARE @TestSaveParamID int = 0
	IF OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam') is not null AND LEFT(@SP_MODE,4) = N'TEST'	begin
		DECLARE @PrmString varchar(7168) =	ISNULL(cast(@nmcl_name as varchar(128)),'null')+','				-- 1. Name of nomenclature from sys_nmcl
										 +  ISNULL(cast(@internal_number as varchar(10)),'null')+','		-- 2. Value of internal_number for state tables.
										 +	ISNULL(cast(@nmcl_rows_group_id as varchar(10)),'null')+','		-- 3. ID of group token from nmcl_table
										 +	ISNULL(cast(@Page_num as varchar(10)),'null')+','				-- 4. Number of page in result	
										 +	ISNULL(cast(@Rows_in_page as varchar(10)),'null')+','			-- 5. Number of rows in one page 
										 +	ISNULL(cast(@OrderBy_json as varchar(MAX)),'null')+','			-- 6. Define order of results
										 +	ISNULL(cast(@lang as varchar(255)),'null')+','					-- 7. Current language from sys_language table
										 +	ISNULL(cast(@ErrMessage as varchar(MAX)),'null')				-- 10. Text of error/message

		DECLARE @dBName nvarchar(130) = DB_NAME()
		DECLARE @PrmJson nvarchar(MAX) = ( SELECT  @nmcl_name				 as '@nmcl_name'				-- 1. Name of nomenclature from sys_nmcl
												  ,@internal_number			 as '@internal_number'			-- 2. Value of internal_number for state tables.
												  ,@nmcl_rows_group_id		 as '@nmcl_rows_group_id'		-- 3. ID of group token from nmcl_table
												  ,@Page_num				 as '@Page_num'					-- 4. Number of page in result
												  ,@Rows_in_page			 as '@Rows_in_page'				-- 5. Number of rows in one page
												  ,@OrderBy_json			 as '@OrderBy_json'				-- 6. Define order of results
												  ,@lang					 as '@lang'						-- 7. Current language from sys_language table
												  ,@ErrMessage				 as '@ErrMessage'				-- 8. Text of error/message
												  ,@Return					 as '@Return'					-- 9. Return value	(0 - fail 1 - success)
												  ,@SP_MODE					 as '@SP_MODE'					-- 10. Execution mode
											FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )
		 EXEC [stepsoft].[dbo].[usp_ssTestSaveParam] @PrmMessage = @PrmString, @CallingModule = @ProcedureName, @XMLString = null
													,@Separator = '~', @dataBase = @dBName, @RecordID = @TestSaveParamID OUTPUT
													,@PrmJson = @PrmJson
																									end	-- IF OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam') is not null AND LEFT(@SP_MODE,4) = N'TEST' 	
	print @ProcedureName+'->SP_MODE = ' + ISNULL(@SP_MODE,'NULL')+' '+@ProcedureName+'->TestSaveParamID = ' + ISNULL(cast(@TestSaveParamID as varchar(10)),'NULL')
	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'Parameters' as tbl
								  ,@nmcl_name as '@nmcl_name', @sys_nmcl_id as '@sys_nmcl_id'
								  ,@internal_number as '@internal_number', @nmcl_rows_group_id as '@nmcl_rows_group_id'
								  ,@Page_num as '@Page_num',@Rows_in_page as '@Rows_in_page'
								  ,@OrderBy_json as '@OrderBy_json',@OrderBy as '@OrderBy'
								  ,@lang as '@lang', @lang_id as '@lang_id'
								  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage', @SP_MODE as '@SP_MODE'
	IF @SP_MODE = N'TEST1' AND exists (select 1 from @tblOrderBy)   select  @ProcedureName+N'('+@Status+N')' as  [procedure],'table @tblOrderBy ' as tbl,* from @tblOrderBy
	ELSE IF @SP_MODE = N'TEST1'										select  @ProcedureName+N'('+@Status+N')' as  [procedure],'No rows in table @tblOrderBy' as tbl
	--------------------------------------------------------------------------------------------------------------------------------------------------
	-- 3. Load elements metadata / Зареждане на метаданни за елементите
	/*		0 document title
			1 document row
			2 report
			3 nomenclature row
			4 dynamic row
			5 nomenclature title	
			6 map row */
		DECLARE @existsTblFieldsToSearch nvarchar(128)
			SET @Status += ',3'
print @Status+N'=>'+cast(DATEDIFF(ms,@test_d,GetDate()) as nvarchar(20)); set @test_d=GETDATE()
print '		1=>'+cast(DATEDIFF(ms,@test_d,GetDate()) as nvarchar(20)); set @test_d=GETDATE()
			DROP TABLE IF EXISTS #NmclElements
			SELECT * INTO #NmclElements FROM dbo.ufn_ssERP8_CollectObjects(1,@sys_nmcl_id,null,null,null,null,@lang)
print '		2=>'+cast(DATEDIFF(ms,@test_d,GetDate()) as nvarchar(20)); set @test_d=GETDATE()
		-- set @state_table -- select * from state_form_383_grid_1983
		IF EXISTS (select 1 from #NmclElements ) 
						SET @state_table = ( select top(1) [state_table_name] from #NmclElements where [section_type]=3 AND [rm_element_translatable]=0 )
		   ELSE begin	SET @ErrMessage += N'<<1>> Missing settings of nomenclature '+ISNULL(@nmcl_name,N'null'); SET @nRetValue = 0 end

	IF @SP_MODE=N'TEST1'   select @ProcedureName+N'('+@Status+N')' as  [procedure],'Checking state' as tbl
								,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
								,@internal_number as '@internal_number',@state_table as '@state_table'
	IF @SP_MODE=N'TEST1' AND exists (select 1 from #NmclElements) select @ProcedureName+N'('+@Status+N')' as  [procedure],'table #NmclElements' as tbl, * from #NmclElements
	--------------------------------------------------------------------------------------------------------------------------------------------------
	-- 4. Return json with state table data
	DECLARE @State_result nvarchar(MAX), @State_cnt int
			SET @Status += ',4'
print @Status+N'=>'+cast(DATEDIFF(ms,@test_d,GetDate()) as nvarchar(20)); set @test_d=GETDATE()
		IF @nRetValue > 0 AND @internal_number>0 AND @state_table is not null	BEGIN -- 2=crossing transit; 1=search anything 0=error
			SET @ParmDefinition = N'@State_cnt int OUTPUT,@internal_number int'
			SET @CommandString = 'SELECT @State_cnt=COUNT(id) FROM [dbo].['+@state_table+N'] WHERE [internal_number]=@internal_number'
							   + case when @nmcl_rows_group_id is not null then N' AND [rows_group]='+CAST(@nmcl_rows_group_id as nvarchar(20)) else N'' end

				EXEC sp_executesql @CommandString,@ParmDefinition,@State_cnt=@State_cnt OUTPUT, @internal_number=@internal_number
			IF @SP_MODE=N'TEST1'   select @ProcedureName+N'('+@Status+N')' as  [procedure],'Count of rows' as tbl
										 ,@ParmDefinition as '@ParmDefinition', @CommandString as '@CommandString'
										 ,@State_cnt as '@State_cnt'
			   SET @ParmDefinition = N'@State_result nvarchar(MAX) OUTPUT, @State_cnt int, @internal_number int'
								   + case when @Page_num is not null then N', @Rows_in_page int, @Page_num int'	else N'' end

			   SET @CommandString = CAST( N'SET @State_result = CAST((
											SELECT @State_cnt		AS [TotalResult]
												  ,@Rows_in_page	AS [Rows_in_page]
												  ,@Page_num		AS [Page_num]
												  ,(SELECT  ISNULL([rows_group], 0) AS [rows_group]
														   ,[id_record] AS [id_record]
														   ,[id_grid_row] AS [id_grid_row]
													FROM [dbo].['+@state_table+N']
													WHERE [internal_number]=@internal_number'
								+case when @nmcl_rows_group_id is not null then N' AND [rows_group]='+CAST(@nmcl_rows_group_id as nvarchar(20)) else N'' end+N'
														ORDER BY '+@OrderBy
								+case when @Page_num is not null then N' OFFSET(@Page_num - 1) * @Rows_in_page ROWS FETCH NEXT @Rows_in_page ROWS ONLY' else N'' end+N'
													FOR JSON PATH , INCLUDE_NULL_VALUES 
													)				AS [JsonResult]
											FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )
							AS NVARCHAR(MAX))																		
'											as nvarchar(MAX))
			IF @SP_MODE=N'TEST1'   select @ProcedureName+N'('+@Status+N')' as  [procedure],'Script from state' as tbl
								, @ParmDefinition as '@ParmDefinition',@CommandString as '@CommandString'
								, @State_cnt as '@State_cnt'
								, @internal_number as '@internal_number',@nmcl_rows_group_id as '@nmcl_rows_group_id'
								, @Rows_in_page as '@Rows_in_page', @Page_num as '@Page_num'

print @Status+N'=>'+cast(DATEDIFF(ms,@test_d,GetDate()) as nvarchar(20)); set @test_d=GETDATE()
				-------------------------------------------------------------------
				IF @Page_num is not null
					EXEC sp_executesql @CommandString,@ParmDefinition
						,@State_result=@State_result OUTPUT,@State_cnt=@State_cnt,@internal_number=@internal_number
						,@Rows_in_page=@Rows_in_page,@Page_num=@Page_num
				ELSE 
					EXEC sp_executesql @CommandString,@ParmDefinition
						,@State_result=@State_result OUTPUT,@State_cnt=@State_cnt,@internal_number=@internal_number
				-------------------------------------------------------------------
				
				IF @SP_MODE=N'TEST1'   select @ProcedureName+N'('+@Status+N')' as  [procedure],'Result from state' as tbl
											, @State_result as '@State_result'

																				END	 -- IF @nRetValue > 0 AND @internal_number>0 AND @state_table is not null

print @Status+N'=>'+cast(DATEDIFF(ms,@test_d,GetDate()) as nvarchar(20)); set @test_d=GETDATE()
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 @test_destroyed_cursor_cmd nvarchar(MAX) = N''
			--SELECT @test_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 @test_destroyed_cursor_cmd; exec (@test_destroyed_cursor_cmd)		

		DECLARE @ERROR_PROCEDURE nvarchar(130) = ISNULL(ERROR_PROCEDURE(),@ProcedureName)+ISNULL(N'-'+@Status,N'')
		DECLARE @ERROR_MESSAGE nvarchar(2048) = ISNULL(ERROR_MESSAGE(),N'n/a')
		SET @ErrMessage = isnull(@ErrMessage+N',',N'')
							 +N'SQL Msg: ' + ISNULL(cast(ERROR_NUMBER() as nvarchar(20)),N'')	+N','
							 +N' Level ' + ISNULL(cast(ERROR_SEVERITY() as nvarchar(20)),N'')	+N','
							 +N' State ' + ISNULL(cast(ERROR_STATE() as nvarchar(20)),N'')		+N','
							 +N' Proc ' + @ERROR_PROCEDURE									
							 +N'('+ISNULL('Line: '+cast(ERROR_LINE() as nvarchar(20)),N'')		+N'),'
							 + @ERROR_MESSAGE		
		SET @nRetValue = 0 
END CATCH
	IF @nRetValue > 0 SET @nRetValue=1
	SET @Return = @nRetValue
	DECLARE @Error TABLE ( [Error] nvarchar(2048) ); INSERT INTO @Error SELECT cast([Value] as nvarchar(1024)) as Error FROM string_split(@ErrMessage,';')	WHERE ISNULL([Value],N'') <> N'' 
	SET @ErrMessage = (SELECT * FROM @Error FOR JSON PATH , ROOT('Errors'))
	SELECT @Return as [Return], @ErrMessage as [Error_json], @State_result as [Output_json]

RETURN @nRetValue
END