CREATE PROCEDURE EventLogReport(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 offsetparam INT, IN countparam 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 @offsetparam= offsetparam;
SET @countparam= countparam;
SET @eventtypesparam = eventtypesparam;
SET @persontypeparam = persontypeparam;

SET @qry1 = "SELECT *
 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,
           CO.location_source,
           CO.sensor_data,
           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 ,
            CO.editor AS editor, CO.editor_role AS editor_role,
            CO.created_at AS event_created_at, CO.modified_at AS event_modified_at,
            (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,
             DEV.device_code AS device_code, DEV.description AS device_description, DEV.operating_system AS device_os, DEV.application AS device_application,

		VA.id AS appointment_id,
		VA.appointment_start AS appointment_start,
		VA.appointment_end AS appointment_end,
        VA.time_zone AS appointment_timezone,
        VA.slot_index AS appointment_slot_index,
        VA.sequence_index AS appointment_sequence_index,
		VA_RI.visit_scenario AS scenario_name,
		VA_R.roster_name AS schedule_name,
		VA_LOC.name AS job_location_name,
		VA_RI.slot_time AS slot_time,
		VA_RI.slot_occupancy AS slot_occupancy


    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 @qry10 = CASE WHEN (@rosterparam > 0) THEN " AND VA_R.roster_id = @rosterparam " ELSE " " END;
SET @qryAppointment = CASE WHEN (@appointmentparam > 0) THEN " AND VA.id = @appointmentparam " ELSE " " END;
SET @qry11 = 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 device DEV ON CO.device_id=DEV.device_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 physical_locations VA_LOC ON VA_RI.job_location_id = VA_LOC.id
    WHERE CO.is_deleted=0 ", @qry10,  @qryAppointment , @qry11,
" 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 @qry9 = " ORDER BY event_time LIMIT ? OFFSET ? ";

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

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