74 lines
4.4 KiB
Transact-SQL
74 lines
4.4 KiB
Transact-SQL
USE [site_fuchs]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[ocms_getFullItemTree] Script Date: 02.12.2020 21:05:05 ******/
|
|
DROP FUNCTION [dbo].[ocms_getFullItemTree]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[ocms_getFullItemTree] 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_getFullItemTree]
|
|
(
|
|
@view_iid bigint
|
|
,@include_hidden bit
|
|
)
|
|
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,
|
|
[level] int, [sub] bit, [sitepropsub] bit, [focus] 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);
|
|
IF @site is NOT null
|
|
BEGIN
|
|
|
|
WITH cteLevels as (
|
|
SELECT t.*
|
|
, CAST(0 as int) as [level]
|
|
, 0 as [sub]
|
|
, 0 as [sitepropsub]
|
|
FROM [dbo].[ocms_items] as t
|
|
WHERE t.[ocms_iid] = @site
|
|
UNION ALL
|
|
SELECT t.*
|
|
, CAST(c.[level] + 1 as int) as [level]
|
|
, CASE WHEN c.[sub] = 1 THEN 1 WHEN t.[parent_iid] = @view_iid THEN 1 ELSE 0 END as [sub]
|
|
, CASE WHEN c.[sitepropsub] = 1 THEN 1 WHEN t.[parent_iid] = @site and t.[view] = 0 THEN 1 ELSE 0 END as [sitepropsub]
|
|
FROM [dbo].[ocms_items] as t
|
|
inner JOIN cteLevels c on t.[parent_iid] = c.[ocms_iid]
|
|
WHERE (t.[hide] = 0 Or @include_hidden = 1)
|
|
)
|
|
INSERT INTO @itemtree
|
|
SELECT t.*
|
|
, IIF( t.[ocms_iid] = @view_iid , 1, 0) as [focus]
|
|
, ROW_NUMBER() OVER (PARTITION BY [parent_iid] ORDER BY [order], [ocms_iid]) as [new_order]
|
|
FROM cteLevels as t;
|
|
|
|
--SELECT * FROM @itemtree
|
|
END
|
|
|
|
|
|
|
|
RETURN
|
|
END
|
|
GO
|
|
ALTER AUTHORIZATION ON [dbo].[ocms_getFullItemTree] TO SCHEMA OWNER
|
|
GO
|