109 lines
7.8 KiB
Transact-SQL
109 lines
7.8 KiB
Transact-SQL
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
|