CREATE PROCEDURE EventLogReportDailyCount(IN customerparam INT, IN groupparam INT, IN personparam INT, IN taskparam INT,
                                          IN fromparam DATETIME, IN toparam DATETIME, IN daystartparam INT,
                                          IN shift_start_grouping BOOLEAN,IN persontypeparam TEXT)

BEGIN

SET @customerparam = customerparam;
SET @taskparam = taskparam;
SET @daystartparam = daystartparam;
SET @fromparam = fromparam;
SET @toparam = toparam;
SET @startparam= DATE_ADD(fromparam, INTERVAL daystartparam SECOND);
SET @endparam= DATE_ADD(toparam, INTERVAL (86399 + daystartparam) SECOND);
SET @groupparam = groupparam;
SET @personparam = personparam;
SET @shiftstartgroupingparam = shift_start_grouping;
SET @persontypeparam = persontypeparam;

SET
@qry1 = "
SELECT
	CD.cloud_date AS event_day,
	IFNULL( event_count, 0) AS event_count,
	IFNULL(error_count, 0) AS error_count
FROM
	cloud_date_table CD
LEFT JOIN
(
	SELECT
		evt.event_date ,
		COUNT(evt.event_id) AS event_count,
		SUM(evt.error_event) AS error_count
	FROM
		(
		SELECT
			(
			CASE
				WHEN (@shiftstartgroupingparam) THEN date(date_add(CO.shift_start, interval (-@daystartparam) second))
				ELSE date(date_add(CO.event_time, interval (CO.event_time_zone - @daystartparam) second))
			END) AS event_date,
			CO.event_id,
			(CO.event_type <2 AND CI.event_id IS NULL AND COUT.event_id IS NULL) AS error_event
		FROM
			eventlog CO
		INNER JOIN person P
                     ON
			CO.person_id = P.person_id

";

SET
@qry2 = CASE WHEN (@personparam < 0 && @groupparam > 0) THEN
" INNER JOIN user_group_membership UGM ON P.person_id=UGM.member INNER JOIN usergroup UG ON UGM.assigned_group_id=UG.group_id "
ELSE " "
END;

SET
@qry3 = " LEFT JOIN task T ON CO.task_id = T.task_id
    LEFT JOIN visitor_appointments VA ON CO.visit_appointment  = VA.id
	LEFT JOIN roster_item VA_RI ON VA.attendant_roster_item_id  = VA_RI.roster_item_id
	LEFT JOIN roster VA_R ON VA_RI.roster_id = VA_R.roster_id
    LEFT JOIN eventlog CI ON CO.event_id = CI.next_event
    LEFT JOIN eventlog COUT ON CO.next_event = COUT.event_id  WHERE CO.is_deleted = 0
AND
P.is_deleted = 0
AND
VA_R.deleted_date IS NULL
AND
P.customer_id= @customerParam ";

SET
@qry4 = CASE WHEN @personparam > 0 THEN " AND P.person_id = @personparam " ELSE " "
END;

SET
@qry5 = CASE WHEN (@personparam <= 0 && @groupparam > 0) THEN " AND UGM.assigned_group_id= @groupparam AND UGM.end_date IS NULL AND UG.deleted_date IS NULL " ELSE " "
END;
SET @qryPersonType = " AND ((@persontypeparam = 'EMPLOYEE' AND P.person_type IS NULL ) OR (P.person_type = @persontypeparam)) ";
SET
@qry6 = CASE wHEN @taskparam = 0 THEN " AND T.task_id IS NULL "
ELSE
CASE WHEN @taskparam > 0 THEN " AND T.task_id = @taskparam AND T.is_deleted = 0 " ELSE " "
END
END;

SET
@qry7 = " AND
(
  CASE
   WHEN (@shiftstartgroupingparam) THEN
    CO.shift_start
    ELSE date_add(CO.event_time, interval CO.event_time_zone second)
  END) BETWEEN @startparam
AND
@endparam
  )
AS evt
	GROUP BY
		evt.event_date) AS e
	ON
	CD.cloud_date = e.event_date
WHERE
	CD.cloud_date BETWEEN @fromparam
AND @toparam
ORDER BY
	CD.cloud_date";

SET
@qry = CONCAT(@qry1, @qry2, @qry3, @qry4, @qry5, @qryPersonType, @qry6, @qry7);

PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END