CREATE PROCEDURE VisitTrendReport(@customerparam INT, @scheduleparam INT, @grouptypeparam INT, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT, @countparam INT)
    AS
BEGIN

DECLARE @QRY NVARCHAR(MAX),@periodBASE VARCHAR(200),@periodDATASET VARCHAR(200),@periodendQR VARCHAR(200), @ROSTER_FILTER VARCHAR(200);


IF @grouptypeparam = 1
BEGIN
    SET @periodBASE = ' CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam, cloud_date) / 7)* 7,@fromparam)) AS period  ' ;
    SET @periodDATASET = ' CONVERT(DATE,DATEADD(DAY,(DATEDIFF(DAY,@fromparam, appointment_day) / 7)* 7,@fromparam)) AS period  ' ;
    SET @periodendQR = ' DATEADD(SECOND, -1, DATEADD(DAY, 7, CONVERT(DATETIME,BASE.period))) AS periodEnd  ' ;
END
ELSE IF @grouptypeparam = 2
BEGIN
    SET @periodBASE = ' CONVERT(DATE,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,cloud_date)),@fromparam)) AS period  ';
    SET @periodDATASET = ' CONVERT(DATE,DATEADD(MONTH,(DATEDIFF(MONTH,@fromparam,appointment_day)),@fromparam)) AS period  ';
    SET @periodendQR = ' DATEADD(SECOND, -1, DATEADD(MONTH, 1,  CONVERT(DATETIME,BASE.period))) AS periodEnd  ' ;
END
ELSE IF @grouptypeparam = 3
BEGIN
    SET @periodBASE = ' CONVERT(DATE, @fromparam) AS period ';
    SET @periodDATASET = ' CONVERT(DATE, @fromparam) AS period ';
    SET @periodendQR = ' @toparam AS periodEnd  ' ;
END
ELSE
BEGIN
    SET @periodBASE = 'CONVERT(DATE,cloud_date) AS period ';
    SET @periodDATASET = ' CONVERT(DATE, appointment_day) AS period ';
    SET @periodendQR = ' DATEADD(SECOND, -1, DATEADD(DAY, 1,  CONVERT(DATETIME,BASE.period))) AS periodEnd  ' ;
END


SET @ROSTER_FILTER = CASE WHEN (@scheduleparam > 0) THEN ' AND R.roster_id =   @scheduleparam ' ELSE ' ' END;

SET @QRY = CONCAT(

'

SELECT
	CONVERT(DATETIME,BASE.period) AS periodStart,',
	@periodendQR,
	',BASE.roster_id AS scheduleId,
	BASE.roster_name AS scheduleName,
	BASE.roster_description AS scheduleDescription,
	COALESCE(DATASET.appointment_count,0) AS appointmentCount,
	COALESCE(DATASET.attendance,0) AS attendanceCount ,
	COALESCE(DATASET.access,0) AS accessCount,
	COALESCE(DATASET.present,0) AS presentCount
FROM
	(
	SELECT
		period,
		R.roster_id,
		R.roster_name,
		R.roster_description
	FROM
		(
		SELECT
			*
		FROM
			(
			SELECT ',
			@periodBASE ,
			' FROM
				cloud_date_table PC
			WHERE
				PC.cloud_date BETWEEN @fromparam AND @toparam) AS BASE
		GROUP BY
			period

 ORDER BY period OFFSET @offsetparam ROWS FETCH NEXT @countparam ROWS ONLY

  ) AS PC
	CROSS JOIN roster R
	WHERE
		R.customer_id = @customerparam
        AND R.deleted_date IS NULL ',

@ROSTER_FILTER,

') AS BASE
LEFT JOIN
    (
	SELECT
		roster_id,
		period,
		COUNT(appointment_id) AS appointment_count,
		SUM(attendance) AS attendance,
		SUM(access) AS access,
		SUM(present) AS present
	FROM
		(
		SELECT
			roster_id,
			appointment_id, ',
			  @periodDATASET ,
			', CASE
				WHEN SUM(attendance) > 0 THEN 1
				ELSE 0
			END AS attendance,
				CASE
				WHEN SUM(access) >0 THEN 1
				ELSE 0
			END AS access,
			CASE
				WHEN SUM(present)> 0 THEN 1
				ELSE 0
			END AS present
		FROM
			(
			SELECT
				ri.roster_id,
				va.id AS appointment_id,
				e.event_id,
				CASE
					WHEN (e.event_type = 0
						OR e.event_type = 1) THEN 1
					ELSE 0
				END AS attendance,
				CASE
					WHEN (e.event_type = 2) THEN 1
					ELSE 0
				END AS access,
				CASE
					WHEN (e.event_id IS NOT NULL) THEN 1
					ELSE 0
				END AS present,
				CASE
					WHEN (t.task_id IS NOT NULL
						AND CONVERT(TIME ,
						va.appointment_start) < CONVERT(TIME,
						DATEADD(SECOND, t.start_time_seconds, 0))) THEN CONVERT(DATE,
					(DATEADD(DAY, 1, va.appointment_start)))
					ELSE CONVERT(DATE,
					(va.appointment_start))
				END AS appointment_day,
				va.appointment_start AS appoointment_start,
				t.start_time_seconds
			FROM
				visitor_appointments va
			INNER JOIN roster_item ri ON
				va.attendant_roster_item_id = ri.roster_item_id
			LEFT JOIN task t ON
				ri.task_id = t.task_id
			LEFT JOIN roster R ON
				ri.roster_id = R.roster_id
			LEFT JOIN eventlog e ON
				va.id = e.visit_appointment
				AND e.is_deleted = 0
			WHERE
				va.customer_id = @customerparam
				AND va.appointment_start BETWEEN @fromparam AND DATEADD(DAY, 1, @toparam)
                AND R.deleted_date IS NULL ',

@ROSTER_FILTER,

') AS BASE
		GROUP BY
			roster_id,
			appointment_id,
			appointment_day) AS BASE
	GROUP BY
		roster_id,
		period
) AS DATASET

ON
	DATASET.period = BASE.period
	AND DATASET.roster_id = BASE.roster_id

' , ''
);


DECLARE @parameterDefinition NVARCHAR(4000);
SET @parameterDefinition = N'@customerparam INT, @scheduleparam INT, @grouptypeparam INT, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT, @countparam INT';
EXEC sp_executesql @QRY ,@parameterDefinition, @customerparam=@customerparam, @scheduleparam=@scheduleparam, @grouptypeparam=@grouptypeparam, @fromparam=@fromparam, @toparam=@toparam, @offsetparam=@offsetparam, @countparam=@countparam;

END