Fuchs_Intranet/db/dbo.fuchs_planner_getSummary.StoredProcedure.sql

USE [site_fuchs]
GO
/****** Object:  StoredProcedure [dbo].[fuchs_planner_getSummary]    Script Date: 02.12.2020 21:05:05 ******/
DROP PROCEDURE [dbo].[fuchs_planner_getSummary]
GO
/****** Object:  StoredProcedure [dbo].[fuchs_planner_getSummary]    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].[fuchs_planner_getSummary]
	@planner tinyint
	,@session varchar(10)
AS
BEGIN
	
	SET NOCOUNT ON;

	DECLARE @GROUPS TABLE (
		   [sessionid] varchar(5)
		   ,[group_id] int
		   ,[code] varchar(10)
		   ,[planner_id] tinyint
           ,[sequence_order] tinyint
           ,[parent_codes] varchar(50)
           ,[condition] varchar(50)
           ,[shortname] varchar(15)
           ,[name] varchar(100)
           ,[description] varchar(max)
           ,[selection_type] varchar(10)
           ,[final] bit
           ,[hide] bit
		   ,[values] varchar(4000)
		   ,[level] tinyint
		   ,[grouporder] tinyint
	);

	IF ISNULL(@session, '') <> ''
		BEGIN
	

				with 
				traffic as (SELECT *,  CAST(CASE WHEN ROW_NUMBER() OVER (PARTITION BY t.[sessionid],t.[group_code] ORDER BY t.[DateSubmitted] DESC) = 1 THEN 1 ELSE 0 END as bit) as 'last' FROM [dbo].[fuchs_planner_traffic] as t WHERE t.[sessionid] = @session and t.[planner_id] = @planner)
				, plannergroups as (SELECT * FROM [dbo].[fuchs_planner_groups] where [planner_id] = @planner
									UNION
									SELECT * FROM (VALUES (
											-1
											,'contact'
											,@planner
											,255
											,'*'
											,NULL
											,'contact_form'
											,'Ihre Kontaktdaten'
											,'<p>Bitte nennen Sie uns Ihre Kontaktdaten, so dass wir Sie kontaktieren können.</p>'
											,'contact'
											,1
											,0
											)
									) as cf (  [group_id]
											,[code]
											,[planner_id]
											,[sequence_order]
											,[parent_codes]
											,[condition]
											,[shortname]
											,[name]
											,[description]
											,[selection_type]
											,[final]
											,[hide]
									)
				  )
				, vals as (SELECT * FROM traffic WHERE [last] = 1)
				, g as (select 
							plannergroups.*
							, ISNULL(vals.[values],'') as 'values'
							, ROW_NUMBER() OVER (PARTITION BY plannergroups.[parent_codes] ORDER BY [sequence_order]) AS 'groupfactor'
						FROM plannergroups left join vals ON plannergroups.[code] = vals.[group_code]
						where plannergroups.hide = 0)
				, cte as ( 
							SELECT @session as [session], g.*, Cast([sequence_order] as tinyint) as [level]
									, CAST(0 as bit) as [exclude]
									, CAST(CASE WHEN [group_id] = -1 THEN POWER(10,7) ELSE 0 END as int) as 'roworder'   /* contact form is always last */
								FROM g
								WHERE g.[sequence_order] = 0 OR g.group_id = -1
							UNION ALL 
							SELECT @session as [session], g.*, Cast(cte.[level] + 1 as tinyint) as [level]
									, CAST(CASE WHEN cte.[exclude] = 1 THEN 1
												WHEN ISNULL(g.[condition], '') in ('', '{}') THEN 0
												WHEN EXISTS (SELECT * FROM g as gv JOIN [dbo].[fuchs_planner_options] as o 
																				ON gv.[group_id] = o.[group_id] and o.[option_type] in ('a','c','o') 
																				  AND [dbo].[ott_jcsv_contains](gv.code, gv.[values], o.[code]) = 1 
																	WHERE [dbo].[ott_csv_contains](g.[condition], o.[code]) = 1 )
												  THEN 0
												ELSE 1
											END as bit) as [exclude]
									, cte.[roworder] + Cast(g.[groupfactor] as int) * CAST(POWER(10,4-cte.[level]) as int) as 'roworder'   /* by this, the children have a lower number than the following on same level */
								FROM g JOIN cte on g.[sequence_order] > cte.[sequence_order] 
									AND cte.group_id >= 0 AND g.group_id >= 0   --not the contact form
									AND [dbo].[ott_csv_contains](g.[parent_codes], cte.[code]) = 1
									AND cte.[final] = 0
									--AND cte.[exclude] = 0
				)
				, f as (
					select 
						cte.*
						, ROW_NUMBER() OVER (ORDER BY [roworder], [sequence_order]) as 'grouporder'
					FROM cte
					where exclude = 0
				)
				insert into @GROUPS
					select
						f.[session]
					   ,f.[group_id]
					   ,f.[code]
					   ,f.[planner_id]
					   ,f.[sequence_order]
					   ,f.[parent_codes]
					   ,f.[condition]
					   ,f.[shortname]
					   ,f.[name]
					   ,f.[description]
					   ,f.[selection_type]
					   ,f.[final]
					   ,f.[hide]
					   ,f.[values]
					   ,f.[level]
					   ,f.[grouporder]
					FROM f
					ORDER BY f.[grouporder] ASC;

	
				-- output
				SELECT 
					g.[group_id]
					,g.[shortname]
					,g.[code]
					,g.[name]
					--,[dbo].[ott_jcontent](g.[code],g.[values]) as 'values'
					, g.[values]
					,g.[grouporder]
					,g.[selection_type]
					FROM @GROUPS as g;

				-- output
				SELECT o.* 
				,ISNULL([dbo].[ott_jcontent](o.[code],g.[values]), '')
					FROM [dbo].[fuchs_planner_options] as o 
						JOIN @GROUPS as g on o.[planner_id] = g.[planner_id] AND o.[group_id] = g.[group_id] 
						AND (ISNULL(o.[condition], '') = ''
							OR EXISTS (SELECT * FROM @GROUPS as gv WHERE [dbo].[ott_csv_contains](o.[condition], gv.[values]) = 1 )
							)
					WHERE (CASE WHEN [dbo].[ott_jcsv_contains](g.[code],g.[values],o.[code]) = 1 THEN 1
								WHEN [dbo].[ott_jcsv_containskey](o.[code], g.[values]) =1 THEN 1
								ELSE 0 END) = 1
							or 1= 1
						--OR ISNULL([dbo].[ott_jcontent](o.[code],g.[values]), '') <> ''


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