135 lines
9.4 KiB
Transact-SQL
135 lines
9.4 KiB
Transact-SQL
USE [site_fuchs]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ocms_getItem] Script Date: 02.12.2020 21:05:05 ******/
|
|
DROP PROCEDURE [dbo].[ocms_getItem]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ocms_getItem] 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_getItem]
|
|
@ocms_iid bigint
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
-- 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 @viewtree [dbo].[ocms_type_viewtree_full];
|
|
INSERT INTO @viewtree
|
|
SELECT * FROM [dbo].[ocms_getFullViewTree](null, null) -- get full with all languages
|
|
WHERE [ocms_iid] = @ocms_iid; -- only selected view
|
|
|
|
--get itemtree
|
|
DECLARE @itemtree [dbo].[ocms_type_itemtree];
|
|
INSERT INTO @itemtree SELECT * FROM [dbo].[ocms_getFullItemTree](@ocms_iid, 1);
|
|
|
|
/*-----------------------------------------------------------------------------------------------------------------------------------------------------*/
|
|
|
|
|
|
-- output site properties
|
|
SELECT
|
|
t.[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 t
|
|
FULL OUTER JOIN [dbo].[ocms_template_properties] as tp ON t.[template_id] = tp.[ocms_tid]
|
|
FULL OUTER JOIN [dbo].[ocms_properties] as p ON p.[ocms_iid] = t.[ocms_iid] AND p.[value] is not null AND (tp.[key] = p.[key] OR tp.[key] is null)
|
|
WHERE t.[level] = 0;
|
|
|
|
|
|
-- output locales
|
|
SELECT * FROM @locales;
|
|
|
|
-- output item
|
|
SELECT TOP(1)
|
|
it.*
|
|
,p.[key] as 'property_key'
|
|
,p.[locale] as 'property_locale'
|
|
,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 --not necessary a view
|
|
LEFT JOIN [dbo].[ocms_templates] as tpl on it.[template_id] = tpl.[ocms_tid]
|
|
LEFT JOIN [dbo].[ocms_properties] as p on it.[ocms_pid] = p.ocms_pid and p.[value] is not null
|
|
WHERE it.[ocms_iid] = @ocms_iid; -- item in focus
|
|
|
|
-- output children
|
|
SELECT
|
|
it.*
|
|
,p.[key] as 'property_key'
|
|
,p.[locale] as 'property_locale'
|
|
,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
|
|
LEFT JOIN [dbo].[ocms_templates] as tpl on it.[template_id] = tpl.[ocms_tid]
|
|
LEFT JOIN [dbo].[ocms_properties] as p on it.[ocms_pid] = p.ocms_pid and p.[value] is not null
|
|
WHERE it.[ocms_iid] <> @ocms_iid
|
|
AND it.[sub] = 1;
|
|
|
|
-- output items properties
|
|
WITH tp as (
|
|
SELECT tp_.*, ROW_NUMBER() OVER (ORDER BY tp_.[order], tp_.[ocms_tpid], tp_.[key]) as 'tpo', l.locale, l.[order] as [locorder] FROM [dbo].[ocms_template_properties] as tp_ CROSS JOIN @locales as l -- incl. fallback locale '*'
|
|
)
|
|
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.locale
|
|
,tp.[type]
|
|
,tp.[mandatory]
|
|
,IIF(tp.[type] <> 'array', tp.[condition], '') as [condition]
|
|
,ROW_NUMBER() OVER (ORDER BY it.[ocms_iid], tp.[tpo], p.[ocms_pid]) as [order]
|
|
FROM @itemtree as it
|
|
JOIN tp ON it.[template_id] = tp.[ocms_tid]
|
|
FULL OUTER JOIN [dbo].[ocms_properties] as p
|
|
ON p.[ocms_iid] = it.[ocms_iid]
|
|
AND p.[value] is not null
|
|
AND (tp.[key] = p.[key] OR tp.[key] is null)
|
|
AND p.[locale] = tp.[locale]
|
|
WHERE ISNULL(ISNULL(tp.[key], p.[key]),'') <> ''
|
|
AND it.[ocms_iid] = @ocms_iid;
|
|
|
|
|
|
-- output items navs
|
|
WITH nav as (
|
|
select [ocms_iid], [locale], [alias]
|
|
from [dbo].[ocms_navigation] as n WHERE ISNULL(n.[alias], '') <> '' AND n.[locale] <> '*'
|
|
UNION
|
|
SELECT i.[ocms_iid], l.[locale], i.[name]
|
|
FROM [dbo].[ocms_items] as i
|
|
CROSS JOIN @locales as l
|
|
WHERE i.[parent_iid] = @site AND i.[name] = 'index' and l.[locale] <> '*'
|
|
)SELECT
|
|
l.[locale]
|
|
,n.[alias]
|
|
,vt.[path]
|
|
FROM @viewtree as vt
|
|
CROSS JOIN @locales as l
|
|
LEFT JOIN nav as n ON vt.[ocms_iid] = n.[ocms_iid] AND l.[locale] = n.[locale]
|
|
WHERE vt.[ocms_iid] = @ocms_iid
|
|
AND l.[locale] <> '*';
|
|
|
|
END
|
|
GO
|
|
ALTER AUTHORIZATION ON [dbo].[ocms_getItem] TO SCHEMA OWNER
|
|
GO
|