CREATE PROCEDURE EventLogReportCount(IN customerparam INT, IN groupparam INT, IN personparam INT, IN taskparam INT,IN errorparam BOOLEAN, IN fromparam DATETIME, IN toparam DATETIME,IN daystartparam INT, IN shift_start_grouping BOOLEAN, IN rosterparam INT,IN appointmentparam INT, IN eventtypesparam TEXT,IN persontypeparam TEXT)

BEGIN

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


SET @qry1 = "SELECT count(*)
 FROM
  (SELECT CO.event_id,
          CO.event_time_zone,
          CO.event_time AS event_time,
          CO.shift_start,
          CO.event_type,
            (CASE WHEN CO.event_type=1 THEN NULL ELSE CO.next_event END) next_event,
            (CASE WHEN CO.event_type=0 THEN NULL ELSE CI.event_id END) previous_event,
   		CO.latitude,
           CO.longitude,
           CO.address,
           CO.description,
           CO.event_origin,
           CDE.data_json AS event_customdata ,
           P.person_id,
           P.employee_code,
           P.first_name,
           P.last_name,
           P.email,
           P.primary_telephone,
           P.address1,
           P.address2,
           P.city,
           P.country,
           P.state_province_region,
           P.zip_or_postal_code,
           P.is_blocked,
           CDU.data_json AS customdata,
           CO.task_id,
           T.name AS task_name,
           CO.device_mode ,
           DG.group_id AS device_group_id,
           DG.group_code AS device_group_code,
           DG.description AS device_group_description,
           (DG.group_id IS NOT NULL
            AND DG.deleted_date IS NOT NULL) device_group_deleted ,
           RG.group_id AS restriction_group_id,
           RG.group_code AS restriction_group_code,
           RG.description AS restriction_group_description,
           (RG.group_id IS NOT NULL
            AND RG.deleted_date IS NOT NULL) restriction_group_deleted ,
            (CASE WHEN CO.event_type=1 THEN CI.event_id ELSE CO.event_id END) CheckinIndex,
                (CASE WHEN CO.event_type=0 THEN CO.next_event ELSE CO.event_id END) CheckoutIndex   FROM eventlog CO
   INNER JOIN person P ON CO.person_id=P.person_id
   LEFT JOIN customdata CDU ON P.custom_data_id = CDU.custom_data_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 @qry9 = CASE WHEN (@rosterparam > 0) THEN " AND VA_RI.roster_id = @rosterparam " ELSE " " END;
SET @qryAppointment = CASE WHEN (@appointmentparam > 0) THEN " AND VA.id = @appointmentparam " ELSE " " END;
SET @qry10 = CONCAT(" AND CO.event_type in (", @eventtypesparam, ") ");

SET @qry3 = CONCAT("
    LEFT JOIN task T ON CO.task_id=T.task_id
    LEFT JOIN usergroup DG ON CO.device_group_id = DG.group_id
    LEFT JOIN usergroup RG ON CO.restriction_group_id = RG.group_id
    LEFT JOIN customdata CDE ON CO.custom_data_id = CDE.custom_data_id
    LEFT JOIN eventlog CI ON CO.event_id=CI.next_event
	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
    WHERE CO.is_deleted=0 ", @qry9,@qryAppointment , @qry10,
" AND
P.is_deleted =0
AND
VA_R.deleted_date IS NULL
AND
P.customer_id= @customerparam

AND
 CO.event_time
  BETWEEN DATE_ADD(@startparam, INTERVAL -2 DAY)
AND
DATE_ADD(@endparam, INTERVAL 2 DAY)

AND
(
  CASE
  WHEN (@shift_start_grouping) THEN
    CO.shift_start
    ELSE date_add(CO.event_time, interval CO.event_time_zone second)
  END) BETWEEN @startparam
AND
@endparam ");

SET @qry4 = CASE WHEN @personparam > 0 THEN " AND P.person_id=@personparam " ELSE " " END;
SET @qryPersonType = " AND ((@persontypeparam = 'EMPLOYEE' AND P.person_type IS NULL ) OR (P.person_type = @persontypeparam)) ";

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 @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 = ") A";

SET @qry8 = CASE WHEN (@errorparam) THEN " WHERE CheckinIndex IS NULL OR CheckoutIndex IS NULL " ELSE " " END;

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

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