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