USE [site_fuchs]
GO
/****** Object:  StoredProcedure [dbo].[ocms_merge_properties]    Script Date: 02.12.2020 21:05:05 ******/
DROP PROCEDURE [dbo].[ocms_merge_properties]
GO
/****** Object:  StoredProcedure [dbo].[ocms_merge_properties]    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_merge_properties]
	@newprop [dbo].[ocms_type_properties_base] READONLY
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @lastpid int = ISNULL (  (SELECT MAX(ISNULL(p.[ocms_pid], ap.[ocms_pid])) 
										FROM [dbo].[ocms_properties] as p FULL OUTER JOIN [ocms_archive__properties] as ap on p.[ocms_pid] = ap.[ocms_pid])
									,0);

	WITH np as (
		-- make sure properties are allowed by templates
		SELECT
			[pid] = isnull(pp.[ocms_pid], ROW_NUMBER() OVER (PARTITION BY pp.[ocms_pid] order BY tp.[ocms_tpid], n.[key]) + @lastpid)
			,n.*
			,tp.[type]
		FROM @newprop as n
			JOIN [dbo].[ocms_items] as i ON n.[ocms_iid] = i.[ocms_iid]
			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 n.[key] = tp.[key]
			LEFT JOIN [dbo].[ocms_properties] as pp ON pp.[key] = n.[key] and pp.[locale] = n.[locale] AND pp.[ocms_iid] = n.[ocms_iid]
	)
	MERGE [dbo].[ocms_properties] as TARGET
		USING np as SOURCE ON TARGET.[key] = SOURCE.[key] and TARGET.[locale] = SOURCE.[locale] AND TARGET.[ocms_iid] = SOURCE.[ocms_iid]
	WHEN MATCHED AND ISNULL(SOURCE.[value], '') = '' AND SOURCE.[type] <> 'array' THEN 
		DELETE
	WHEN MATCHED AND TARGET.[Value] <> SOURCE.[value] THEN 
		UPDATE SET [value] = SOURCE.[value], [DateModified] = GETUTCDATE()
	WHEN NOT MATCHED BY TARGET THEN
		INSERT ([ocms_pid], [ocms_iid], [key], [value], [locale], [DateCreated], [DateModified])
		VALUES (SOURCE.[pid], SOURCE.[ocms_iid], SOURCE.[key], SOURCE.[value], SOURCE.[locale], GETUTCDATE(), GETUTCDATE())
	OUTPUT
		ISNULL(inserted.[ocms_pid], deleted.[ocms_pid]),
		$action,
		inserted.[ocms_iid], inserted.[key], inserted.[value], inserted.[locale], inserted.[DateCreated], inserted.[DateModified], NULL, NULL,
		deleted.[ocms_iid], deleted.[key], deleted.[value], deleted.[locale], deleted.[DateCreated], deleted.[DateModified], NULL, NULL
		INTO [dbo].[ocms_log__properties]
	;

END
GO
ALTER AUTHORIZATION ON [dbo].[ocms_merge_properties] TO  SCHEMA OWNER 
GO