USE [site_fuchs]
GO
/****** Object:  UserDefinedFunction [dbo].[ocms_getFullViewTree]    Script Date: 02.12.2020 21:05:05 ******/
DROP FUNCTION [dbo].[ocms_getFullViewTree]
GO
/****** Object:  UserDefinedFunction [dbo].[ocms_getFullViewTree]    Script Date: 02.12.2020 21:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[ocms_getFullViewTree]
(
	@view_path varchar(255),
	@locale varchar(5)
)
RETURNS @itemtree TABLE (
		[ocms_iid] [bigint] NOT NULL,
		[parent_iid] [bigint] NULL,
		[ocms_pid] [bigint] NULL,
		[name] [nvarchar](255) NULL,
		[view] [bit] NOT NULL,
		[template_id] [bigint] NULL,
		[hide] [bit] NOT NULL,
		[order] [int] NOT NULL,
		[DateCreated] datetime null,
		[DateModified] datetime null,
		[locale] varchar(5) NOT NULL, [alias] varchar(25) NULL, [path] varchar(2000), [level] int, [sub] bit, [focus] bit, [nav] bit, [new_order] int
	)
AS
BEGIN
	
	-- get basics
	DECLARE @site bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE i.[parent_iid] is null);
	SET @view_path = ISNULL(@view_path, '');
	SET @locale = IIF(ISNULL(@locale, '')='', null, @locale);

	IF @site is NOT null
	BEGIN
			--get locales
			DECLARE @locales TABLE([locale] varchar(5), [order] int);
			INSERT INTO @locales
				SELECT * FROM [dbo].[ocms_locales]();

			-- tree in all locales
			WITH itm as(
				-- items are not localized
				SELECT i.*, l.[locale] 
					FROM [dbo].[ocms_items] as i CROSS JOIN (SELECT * FROM @locales as l_ WHERE l_.[locale] <> '*') as l 
					WHERE i.[view] = 1 OR ([ocms_iid] = @site)
			), navitm as (
				select itm.*
					   ,IIF(itm.[name] COLLATE Latin1_General_CI_AS = 'index', 'index', n.[alias]) as 'alias'
					FROM itm
						LEFT JOIN [dbo].[ocms_navigation] as n ON itm.[ocms_iid] = n.[ocms_iid] and itm.[locale] = n.[locale] AND n.[locale] <> '*'
			), cteLevels as (
				SELECT t.*
						, Cast('//' as varchar(2000)) as [path]
						, CAST(0 as int) as [level]
						, 0 as [sub]
					FROM navitm as t
					WHERE t.[ocms_iid] = @site
				UNION ALL
				SELECT navitm.*
						, IIF( navitm.[view] = 0, '', CAST(IIF( c.[path] = '//' , '' , c.[path] + '/' ) + LOWER(navitm.[alias]) as varchar(2000)) ) as [path]
						, CAST(c.[level] + 1 as int) as [level]
						, CASE WHEN c.[sub] = 1 THEN 1 WHEN IIF( CAST(IIF( c.[path] = '//' , '' , c.[path] + '/' ) + ISNULL(LOWER(navitm.[name]), '') as varchar(2000)) = LOWER(@view_path), 1, 0) = 1 THEN 1 ELSE 0 END as [sub]
					FROM navitm
						inner JOIN cteLevels c on navitm.[parent_iid] = c.[ocms_iid] AND navitm.[locale] = c.[locale]
			)
			INSERT INTO @itemtree 
				SELECT t.*
					, IIF( [path] COLLATE Latin1_General_CI_AS = @view_path OR [path] COLLATE Latin1_General_CI_AS like (@view_path + '?%'), 1, 0) as [focus]
					, IIF( [view] = 1 AND [level] > 0 AND [path] <> '' AND [path] <> '//' AND ISNULL(t.[hide],0) = 0 , 1, 0 ) as [nav]
					, ROW_NUMBER() OVER (PARTITION BY [parent_iid] ORDER BY [order], [ocms_iid]) as [new_order]
				FROM cteLevels as t;

			UPDATE @itemtree SET [path] = null WHERE [nav] = 0;


			IF(ISNULL(@locale, '') <> '')
			BEGIN
				DECLARE @view_iid bigint = null, @view_level int;
				SELECT TOP(1) 
					@view_iid = it.[ocms_iid]
					, @view_level = it.[level]
					, @locale = it.[locale]			-- will override the selected language
				FROM @itemtree as it
				WHERE [focus] = 1
					AND ([locale] = @locale OR [name] COLLATE Latin1_General_CI_AS <> 'index');  -- index is available in any language
			
				DELETE FROM @itemtree WHERE [locale] <> @locale;  -- remove anything that is not in selected language
			END

			--SELECT * FROM @itemtree
	END


	
	RETURN 
END
GO
ALTER AUTHORIZATION ON [dbo].[ocms_getFullViewTree] TO  SCHEMA OWNER 
GO