229 lines
16 KiB
Transact-SQL
229 lines
16 KiB
Transact-SQL
USE [site_fuchs]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ocms_getView] Script Date: 02.12.2020 21:05:05 ******/
|
|
DROP PROCEDURE [dbo].[ocms_getView]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ocms_getView] 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 PROCEDURE [dbo].[ocms_getView]
|
|
@view_path varchar(255)
|
|
,@locale varchar(5)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF ISNULL(@locale, '') = ''
|
|
SET @locale = 'de'; -- fallback
|
|
IF ISNULL(@view_path, '') = ''
|
|
SET @view_path = 'index';
|
|
|
|
-- 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 null
|
|
THROW 61110, 'site does not exist', 1;
|
|
|
|
--get locales
|
|
DECLARE @locales TABLE([locale] varchar(5), [order] int);
|
|
INSERT INTO @locales
|
|
SELECT * FROM [dbo].[ocms_locales]();
|
|
|
|
|
|
-- get viewtree and paths
|
|
DECLARE @view_iid bigint = null, @view_level int, @view_name nvarchar(255), @view_is_hidden bit;
|
|
DECLARE @viewtree [dbo].[ocms_type_viewtree_full];
|
|
INSERT INTO @viewtree SELECT * FROM [dbo].[ocms_getFullViewTree](@view_path, null); -- any locale to be able to determine language from path
|
|
|
|
SELECT TOP(1)
|
|
@view_iid = it.[ocms_iid]
|
|
, @view_level = it.[level]
|
|
, @locale = it.[locale] -- will override the selected language
|
|
, @view_name = it.[name]
|
|
, @view_is_hidden = it.[hide]
|
|
FROM @viewtree as it
|
|
ORDER BY (CASE WHEN [focus] = 1 AND [locale] = @locale THEN 0 WHEN [focus] = 1 THEN 1 ELSE 2 END) ASC;
|
|
|
|
|
|
--get itemtree
|
|
DECLARE @itemtree [dbo].[ocms_type_itemtree];
|
|
INSERT INTO @itemtree SELECT * FROM [dbo].[ocms_getFullItemTree](@view_iid, @view_is_hidden); -- items are not localized
|
|
|
|
|
|
--get properties
|
|
DECLARE @PROP TABLE(
|
|
[ocms_pid] [bigint] NOT NULL,
|
|
[ocms_iid] [bigint] NOT NULL,
|
|
[key] [varchar](50) NULL,
|
|
[value] [nvarchar](max) NOT NULL,
|
|
[DateModified] [datetime] NULL
|
|
);
|
|
WITH SP As (
|
|
SELECT
|
|
p.*
|
|
,it.[focus]
|
|
,it.[level]
|
|
, ROW_NUMBER() OVER (PARTITION BY p.[ocms_iid], p.[key] ORDER BY CAST((CASE WHEN p.[locale] = '*' THEN 1 ELSE 0 END) as int)) as 'rwi'
|
|
FROM @itemtree as it
|
|
JOIN [dbo].[ocms_properties] as p ON it.[ocms_iid] = p.[ocms_iid]
|
|
WHERE (it.[level] = 0 --site
|
|
OR it.[sub] = 1
|
|
OR it.[sitepropsub] = 1
|
|
OR it.[focus] = 1
|
|
OR it.[parent_iid] = @site) -- or itms below selected
|
|
AND (p.[locale] = @locale OR p.[locale] = '*')
|
|
)
|
|
INSERT INTO @PROP
|
|
SELECT [ocms_pid],[ocms_iid],[key],[value],[DateModified]
|
|
FROM SP
|
|
WHERE rwi = 1;
|
|
|
|
|
|
DECLARE @translations TABLE (
|
|
[ocms_tln] [bigint] NOT NULL,
|
|
[context_typ] [nvarchar](15) NOT NULL,
|
|
[context] [bigint] NULL,
|
|
[value] [nvarchar](255) NOT NULL,
|
|
[translation] [nvarchar](255) NOT NULL,
|
|
[locale] [varchar](5) NOT NULL
|
|
);
|
|
|
|
|
|
/* outputs *********************************************************************************************************************************************************/
|
|
|
|
|
|
-- output nav
|
|
SELECT vt.[ocms_iid]
|
|
,[parent_iid]
|
|
,vt.[ocms_pid]
|
|
,[name]
|
|
,ISNULL(p.[value], vt.[name]) as [title]
|
|
,[view]
|
|
,[template_id]
|
|
,[hide]
|
|
,[new_order] as [order]
|
|
, [path], [level], [sub], [focus], [nav]
|
|
FROM @viewtree as vt -- vt must be filtered by locale
|
|
LEFT JOIN [dbo].[ocms_properties] as p ON vt.[ocms_iid] = p.[ocms_iid] AND p.[key] = 'title' AND p.[locale] = vt.[locale] -- ! localized
|
|
WHERE [nav] = 1
|
|
AND vt.[locale] = @locale;
|
|
|
|
|
|
-- output site properties
|
|
SELECT
|
|
vt.[ocms_iid]
|
|
,p.[ocms_pid]
|
|
,ISNULL(tp.[key], p.[key]) as [key]
|
|
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
|
|
,tp.[type]
|
|
FROM @viewtree as vt -- site is part if viewtree // -- vt must be filtered by locale
|
|
FULL OUTER JOIN [dbo].[ocms_template_properties] as tp ON vt.[template_id] = tp.[ocms_tid]
|
|
FULL OUTER JOIN @PROP as p ON p.[ocms_iid] = vt.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null) -- properties are ! localized, but already filtered for @PROP
|
|
WHERE vt.[ocms_iid] = @site
|
|
AND vt.[locale] = @locale
|
|
UNION
|
|
SELECT
|
|
NULL as [ocms_iid]
|
|
,NULL as [ocms_pid]
|
|
,'DateModified' as [key]
|
|
,FORMAT((SELECT MAX([DateModified]) FROM @PROP),'yyyy-MM-ddTHH:mm:ssZ') as [value]
|
|
,'string';
|
|
|
|
INSERT INTO @translations
|
|
SELECT * from [dbo].[ocms_translations] as TL WHERE TL.[context_typ] = 'site' AND TL.[locale] = @locale;
|
|
|
|
|
|
-- output locales
|
|
WITH pd as (SELECT DISTINCT [ocms_iid], [path], [locale] FROM @viewtree WHERE [ocms_iid] = @view_iid)
|
|
SELECT
|
|
pd.*
|
|
, CAST(IIF(l.[locale] = @locale, 1, 0) as bit) as 'active'
|
|
FROM @locales as l JOIN pd ON l.[locale] = pd.[locale]
|
|
WHERE l.[locale] <> '*';
|
|
|
|
|
|
-- output view properties
|
|
SELECT
|
|
vt.[ocms_iid]
|
|
,p.[ocms_pid]
|
|
,ISNULL(tp.[key], p.[key]) as [key]
|
|
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
|
|
,tp.[type]
|
|
FROM @viewtree as vt -- vt must be filtered by locale
|
|
FULL OUTER JOIN [dbo].[ocms_template_properties] as tp ON vt.[template_id] = tp.[ocms_tid]
|
|
FULL OUTER JOIN @PROP as p ON p.[ocms_iid] = vt.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null) -- properties are ! localized, but already filtered for @PROP
|
|
WHERE vt.[focus] = 1
|
|
AND ISNULL(ISNULL(tp.[key], p.[key]),'') <> ''
|
|
AND vt.[locale] = @locale;
|
|
|
|
INSERT INTO @translations
|
|
SELECT * from [dbo].[ocms_translations] as TL
|
|
WHERE TL.[context] = @view_iid AND TL.[locale] = @locale; --context = 'view' and all other context_types
|
|
|
|
|
|
-- output children
|
|
SELECT
|
|
it.*
|
|
,tpl.[partial_view]
|
|
,tpl.[name] as [template_name]
|
|
,ROW_NUMBER() OVER (PARTITION BY it.[parent_iid], it.[ocms_pid] ORDER BY it.[ORDER], it.[ocms_iid]) as 'item_order'
|
|
FROM @itemtree as it -- items are not localized
|
|
LEFT JOIN [dbo].[ocms_templates] as tpl on it.[template_id] = tpl.[ocms_tid]
|
|
WHERE [sub] = 1 OR [sitepropsub] = 1 OR it.[parent_iid] = @site; --to include also site relevant children
|
|
|
|
INSERT INTO @translations
|
|
SELECT TL.* from @itemtree as it JOIN [dbo].[ocms_translations] as TL ON TL.[context_typ] = 'template' AND TL.[context] = it.[template_id] AND TL.[locale] = @locale;
|
|
|
|
|
|
-- output childrens' properties
|
|
SELECT
|
|
it.[ocms_iid]
|
|
,p.[ocms_pid]
|
|
,ISNULL(tp.[key], p.[key]) as [key]
|
|
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
|
|
,tp.[type]
|
|
FROM @itemtree as it -- items are not localized
|
|
JOIN [dbo].[ocms_template_properties] as tp ON it.[template_id] = tp.[ocms_tid] -- properties are ! localized, but already filtered for @PROP
|
|
FULL OUTER JOIN @PROP as p ON p.[ocms_iid] = it.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null)
|
|
WHERE ([sub] = 1 OR [sitepropsub] = 1 OR it.[parent_iid] = @site) AND [focus] = 0
|
|
AND ISNULL(ISNULL(tp.[key], p.[key]),'') <> '';
|
|
|
|
|
|
|
|
--output translations
|
|
SELECT DISTINCT * FROM @translations;
|
|
|
|
|
|
--output breadcrumbs
|
|
WITH vws as(
|
|
SELECT vt.[ocms_iid], [parent_iid], ISNULL(p.[value], vt.[name]) as [title], [path] FROM @viewtree as vt -- vt must be filtered by locale
|
|
LEFT JOIN [dbo].[ocms_properties] as p ON vt.[ocms_iid] = p.[ocms_iid] AND p.[key] = 'title' AND p.[locale] = vt.[locale] -- ! localized
|
|
)
|
|
,
|
|
cte as (
|
|
SELECT vws.*, [title] as [breadcrumb], 0 as [level]
|
|
FROM vws
|
|
WHERE vws.[ocms_iid] = @view_iid
|
|
UNION ALL
|
|
SELECT vws.*, ( vws.[title] + ' > ' + cte.[breadcrumb]) as [breadcrumb]
|
|
, [level] + 1 as [level]
|
|
FROM vws JOIN cte ON vws.[ocms_iid] = cte.[parent_iid]
|
|
)
|
|
select [title]
|
|
, [path]
|
|
, CASE WHEN [level] = 0 THEN (SELECT TOP(1) [breadcrumb] FROM cte as cte1 ORDER BY [level] DESC) ELSE NULL END as [breadcrumb]
|
|
, ROW_NUMBER() OVER (ORDER BY [level] DESC) as [order]
|
|
FROM cte;
|
|
|
|
|
|
END
|
|
GO
|
|
ALTER AUTHORIZATION ON [dbo].[ocms_getView] TO SCHEMA OWNER
|
|
GO
|