CREATE PROCEDURE VisitTrendReport(IN customerparam INT, IN scheduleparam INT, IN grouptypeparam INT, IN fromparam DATETIME, IN toparam DATETIME, IN offsetparam INT, IN countparam INT)
BEGIN
SET @customerparam = customerparam;
SET @scheduleparam = scheduleparam;
SET @grouptypeparam = grouptypeparam;
SET @fromparam = fromparam;
SET @toparam = toparam;
SET @offsetparam= offsetparam;
SET @countparam= countparam;


IF grouptypeparam = 1 THEN
    SET @periodBASE = " DATE(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( cloud_date, @fromparam) DIV 7)*7 DAY)) AS period  " ;
    SET @periodDATASET = " DATE(DATE_ADD(@fromparam, INTERVAL ( DATEDIFF( appointment_day, @fromparam) DIV 7)*7 DAY)) AS period  " ;
    SET @periodendQR = "DATE_ADD(DATE_ADD(BASE.period, INTERVAL 7 DAY) , INTERVAL -1 SECOND ) AS periodEnd ";
ELSEIF grouptypeparam = 2 THEN
    SET @periodBASE = " DATE(DATE_ADD(@fromparam, INTERVAL TIMESTAMPDIFF(MONTH, @fromparam ,cloud_date) MONTH )) as period  ";
    SET @periodDATASET = " DATE(DATE_ADD(@fromparam, INTERVAL TIMESTAMPDIFF(MONTH, @fromparam ,appointment_day) MONTH )) as period  ";
    SET @periodendQR = "DATE_ADD(DATE_ADD(BASE.period, INTERVAL 1 MONTH) , INTERVAL -1 SECOND ) AS periodEnd ";
ELSEIF grouptypeparam = 3 THEN
    SET @periodBASE = " DATE(@fromparam) AS period ";
    SET @periodDATASET = " DATE(@fromparam) AS period ";
    SET @periodendQR = "@toparam AS periodEnd ";
ELSE
    SET @periodBASE = " DATE(cloud_date) AS period ";
    SET @periodDATASET = " DATE(appointment_day) AS period ";
    SET @periodendQR = "DATE_ADD(DATE_ADD(BASE.period, INTERVAL 1 DAY) , INTERVAL -1 SECOND ) AS periodEnd ";
END IF;

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

SET @QRY = CONCAT("

SELECT
	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
			",
			@periodBASE,
		" FROM
			cloud_date_table PC
		WHERE
			PC.cloud_date BETWEEN @fromparam AND @toparam
		GROUP BY
			period
            LIMIT ?, ?    ) 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
			, " ,
			SUM(attendance) >0 AS attendance,
			SUM(access) >0 AS access,
			SUM(present)> 0 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 TIME_TO_SEC(TIME(va.appointment_start)) < t.start_time_seconds) THEN DATE(DATE_ADD(va.appointment_start, INTERVAL -1 DAY))
					ELSE 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 = FALSE
			WHERE
				va.customer_id = @customerparam
				AND va.appointment_start BETWEEN @fromparam AND DATE_ADD(@toparam, INTERVAL 1 DAY)
                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


");

PREPARE stmt FROM @QRY;
EXECUTE stmt USING @offsetparam, @countparam;
DEALLOCATE  PREPARE stmt;
END