78 lines
4.9 KiB
Transact-SQL
78 lines
4.9 KiB
Transact-SQL
USE [site_fuchs]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ocms_getAvailableTemplates] Script Date: 02.12.2020 21:05:05 ******/
|
|
DROP PROCEDURE [dbo].[ocms_getAvailableTemplates]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ocms_getAvailableTemplates] 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_getAvailableTemplates]
|
|
@ocms_iid bigint
|
|
,@ocms_pid bigint
|
|
,@property_key varchar(50)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NOT EXISTS (SELECT * FROM [dbo].[ocms_items] WHERE [ocms_iid] = @ocms_iid)
|
|
THROW 61010, 'item does not exist', 1;
|
|
|
|
DECLARE @TP TABLE(
|
|
[ocms_tpid] [bigint] NOT NULL,
|
|
[ocms_pid] [bigint],
|
|
[condition] [varchar](max) NULL
|
|
);
|
|
INSERT INTO @TP
|
|
SELECT tp.[ocms_tpid], p.[ocms_pid], tp.[condition]
|
|
FROM [dbo].[ocms_items] as i
|
|
JOIN [dbo].[ocms_templates] as t on i.[template_id] = t.[ocms_tid]
|
|
JOIN [dbo].[ocms_template_properties] as tp ON t.[ocms_tid] = tp.[ocms_tid] AND tp.[type] = 'array'
|
|
LEFT JOIN [dbo].[ocms_properties] as p ON i.[ocms_iid] = p.[ocms_iid] AND p.[key] = tp.[key]
|
|
WHERE i.[ocms_iid] = @ocms_iid
|
|
AND (p.[ocms_pid] = @ocms_pid OR (@ocms_pid is null AND tp.[key] = @property_key))
|
|
AND tp.[type] = 'array';
|
|
|
|
IF NOT EXISTS (SELECT * FROM @TP)
|
|
THROW 61010, 'items''s property type is not array', 1;
|
|
|
|
DECLARE @condition varchar(max);
|
|
|
|
SET @condition = ISNULL((SELECT TOP(1) [condition] FROM @TP), '');
|
|
DECLARE @TIDS TABLE ([ocms_tid] bigint);
|
|
|
|
|
|
IF LEFT(@condition, 1) = '{' AND RIGHT(@condition, 1) = '}'
|
|
BEGIN
|
|
INSERT INTO @TIDS SELECT RTRIM(LTRIM(value)) FROM string_split(SUBSTRING(@condition,2,LEN(@condition) - 2), ',');
|
|
END
|
|
ELSE IF @condition <> ''
|
|
BEGIN
|
|
INSERT INTO @TIDS SELECT [ocms_tid] FROM [ocms_templates] WHERE [name] like @condition;
|
|
END
|
|
|
|
--output
|
|
SELECT t.[ocms_tid],t.[name] FROM [dbo].[ocms_templates] as t JOIN @TIDS as tids ON t.[ocms_tid] = tids.ocms_tid;
|
|
|
|
|
|
|
|
|
|
--select STUFF(
|
|
-- (SELECT ',' + [name] as [text()]
|
|
-- FROM [dbo].[ocms_templates]
|
|
-- WHERE name like 'section%'
|
|
-- FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)')
|
|
-- ,1,1,'')
|
|
|
|
|
|
END
|
|
GO
|
|
ALTER AUTHORIZATION ON [dbo].[ocms_getAvailableTemplates] TO SCHEMA OWNER
|
|
GO
|