SELECT
    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,
    CD.data_json AS customdata,
    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.appointment_status AS appointment_status,
    RI.visit_scenario AS scenario_name,
    R.roster_name AS schedule_name,
    PL_RI.name AS job_location,
    RI.slot_time AS slot_time,
    RI.slot_occupancy AS slot_occupancy,
    DATE_ADD(EVENT_DATA.first_checkin_time , INTERVAL EVENT_DATA.first_checkin_timezone SECOND ) AS first_checkin_time,
    EVENT_DATA.first_checkin_timezone,
    D_IN.description AS first_checkin_device,
    PL_IN.name AS first_checkin_location,
    DATE_ADD(EVENT_DATA.last_checkout_time, INTERVAL EVENT_DATA.last_checkout_timezone SECOND ) AS last_checkout_time,
    EVENT_DATA.last_checkout_timezone,
    D_OUT.description AS last_checkout_device,
    PL_OUT.name AS last_checkout_location,
    EVENT_DATA.access_count AS access_count,
    EVENT_DATA.last_attendance_event AS last_attendance_event,
    EVENT_DATA.spent_time AS spent_time
FROM
    (
        SELECT
            :onparam AS calculated_on,
            CURRENT_APPOINTMENTS.*
        FROM
            (
                SELECT
                    BASE.appointment_id,
                    BASE.appointment_day,
                    BASE.appointment_timezone AS appointment_timezone,
                    DATE_ADD(BASE.appointment_start, INTERVAL (-BASE.appointment_timezone) SECOND) AS appointment_start,
                    DATE_ADD(BASE.appointment_end, INTERVAL (-BASE.appointment_timezone) SECOND ) AS appointment_end,
                    DATE_ADD(BASE.appointment_day, INTERVAL (-BASE.appointment_timezone + BASE.shift_start_second) SECOND ) AS appointment_shift_start,
                    DATE_ADD(BASE.appointment_day, INTERVAL (-BASE.appointment_timezone + BASE.shift_end_second) SECOND ) AS appointment_shift_end,
                    DATE_ADD(BASE.appointment_day, INTERVAL (-BASE.appointment_timezone + BASE.cin_start) SECOND ) AS appointment_cin_start,
                    DATE_ADD(BASE.appointment_day, INTERVAL (-BASE.appointment_timezone + BASE.cout_end) SECOND ) AS appointment_cout_end
                FROM
                    (
                        SELECT
                            va.id AS appointment_id,
                            va.time_zone AS appointment_timezone,
                            va.appointment_start,
                            va.appointment_end,
                            CASE
                                WHEN (t.task_id IS NOT NULL
                                    AND TIME_TO_SEC(TIMEDIFF(va.appointment_start, CONVERT(DATE(va.appointment_start), DATETIME))) < t.start_time_seconds) THEN DATE_ADD(va.appointment_start, INTERVAL -1 DAY)
                                ELSE DATE(va.appointment_start)
                                END AS appointment_day,
                            t.start_time_seconds AS shift_start_second,
                            CASE
                            WHEN (t.start_time_seconds >= t.end_time_seconds) THEN t.end_time_seconds + 86400
                            ELSE t.end_time_seconds
                            END AS shift_end_second,
                            CASE
                            WHEN (t.checkin_start IS NOT NULL) THEN
                            (CASE
                            WHEN (t.start_time_seconds >= t.checkin_start) THEN t.checkin_start
                            ELSE t.checkin_start - 86400
                            END)
                            ELSE
                            (CASE
                            WHEN (t.start_time_seconds >= t.end_time_seconds) THEN t.end_time_seconds
                            ELSE t.end_time_seconds - 86400
                            END)
                            END AS cin_start,
                            CASE
                            WHEN (t.checkout_end IS NOT NULL) THEN
                            (CASE
                            WHEN ((CASE
                            WHEN (t.start_time_seconds >= t.end_time_seconds) THEN t.end_time_seconds + 86400
                            ELSE t.end_time_seconds
                            END) < t.checkout_end) THEN t.checkout_end
                            ELSE t.checkout_end + 86400
                            END)
                            ELSE
                            t.start_time_seconds + 86400
                            END AS cout_end
                        FROM
                            visitor_appointments va
                            LEFT JOIN roster_item ri ON
                            va.attendant_roster_item_id = ri.roster_item_id
                            LEFT JOIN roster r ON
                            ri.roster_id = r.roster_id
                            LEFT JOIN task t ON
                            ri.task_id = t.task_id
                        WHERE
                          va.appointment_start BETWEEN DATE_ADD(:onparam , INTERVAL -2 DAY) AND DATE_ADD(:onparam , INTERVAL 2 DAY)
                          AND va.request_status = 'APPROVED'
                          AND va.customer_id = :customerparam
                          AND r.deleted_date IS NULL
                        ) AS BASE) AS CURRENT_APPOINTMENTS
        WHERE
                CURRENT_APPOINTMENTS.appointment_cin_start <= :onparam
          AND CURRENT_APPOINTMENTS.appointment_cout_end > :onparam) AS APPOINTMENTS
        LEFT JOIN
    (
        SELECT
            EVENT_CORE.visit_appointment,
            CIN.event_id AS first_checkin,
            CIN.event_time AS first_checkin_time,
            CIN.event_time_zone AS first_checkin_timezone,
            CIN.device_id AS first_checkin_device,
            CIN.physical_location_id AS first_checkin_location,
            COUT.event_id AS last_checkout,
            COUT.event_time AS last_checkout_time,
            COUT.event_time_zone AS last_checkout_timezone,
            COUT.device_id AS last_checkout_device,
            COUT.physical_location_id AS last_checkout_location,
            EVENT_CORE.access_count AS access_count,
            EVENT_CORE.spent_time AS spent_time,
            CASE
                WHEN last_checkin IS NULL THEN NULL
                WHEN last_checkout IS NULL
                    OR last_checkout < last_checkin THEN 0
                ELSE 1
                END AS last_attendance_event
        FROM
            (
                SELECT
                    cin.visit_appointment ,
                    MIN(CASE WHEN cin.event_type = 0 THEN cin.event_time ELSE NULL END) AS first_checkin,
                    MAX(CASE WHEN cin.event_type = 0 THEN cin.event_time ELSE NULL END) AS last_checkin,
                    MAX(cout.event_time) AS last_checkout,
                    SUM(CASE WHEN cin.event_type = 'ACCESS' THEN 1 ELSE 0 END) AS access_count,
                    SUM(CASE WHEN cout.event_time IS NOT NULL THEN (TIME_TO_SEC(TIMEDIFF(cout.event_time, cin.event_time))) ELSE 0 END ) AS spent_time
                FROM
                    eventlog cin
                        INNER JOIN person p ON
                            cin.person_id = p.person_id
                        LEFT JOIN eventlog cout ON
                                cin.next_event = cout.event_id
                            AND cout.event_time <= :onparam
                        LEFT JOIN visitor_appointments VA ON
                            cin.visit_appointment = VA.id
                WHERE
                        p.customer_id = :customerparam
                  AND VA.appointment_start BETWEEN DATE_ADD(:onparam , INTERVAL -2 DAY) AND DATE_ADD(:onparam , INTERVAL 2 DAY)
                  AND VA.request_status = 'APPROVED'
                  AND p.person_type = 'VISITOR'
                  AND cin.is_deleted = FALSE
                  AND cin.event_time <= :onparam
                GROUP BY
                    cin.visit_appointment) AS EVENT_CORE
                LEFT JOIN eventlog CIN
                          ON
                                      EVENT_CORE.visit_appointment = CIN.visit_appointment
                                  AND
                                      EVENT_CORE.first_checkin = CIN.event_time
                                  AND CIN.event_type = 0
                                  AND CIN.is_deleted = false
                LEFT JOIN eventlog COUT
                          ON
                                      EVENT_CORE.visit_appointment = COUT.visit_appointment
                                  AND
                                      EVENT_CORE.last_checkout = COUT.event_time
                                  AND COUT.event_type = 1
                                  AND COUT.is_deleted = false) AS EVENT_DATA
    ON
            APPOINTMENTS.appointment_id = EVENT_DATA.visit_appointment
        LEFT JOIN visitor_appointments VA ON
            APPOINTMENTS.appointment_id = VA.id
        INNER JOIN visit V ON
            VA.visit_id = V.id
        LEFT JOIN person P ON
            V.visitor_id = P.person_id
        LEFT JOIN customdata CD ON
            P.custom_data_id = CD.custom_data_id
        LEFT JOIN roster_item RI ON
            VA.attendant_roster_item_id = RI.roster_item_id
        LEFT JOIN physical_locations PL_RI ON
            RI.job_location_id = PL_RI.id
        LEFT JOIN roster R ON
            RI.roster_id = R.roster_id
        LEFT JOIN device D_IN ON
            EVENT_DATA.first_checkin_device = D_IN.device_id
        LEFT JOIN physical_locations PL_IN ON
            EVENT_DATA.first_checkin_location = PL_IN.id
        LEFT JOIN device D_OUT ON
            EVENT_DATA.last_checkout_device = D_OUT.device_id
        LEFT JOIN physical_locations PL_OUT ON
            EVENT_DATA.last_checkout_location = PL_OUT.id
WHERE
        VA.customer_id =:customerparam
  :PERSON_QRY
--   AND P.person_id = :person_param
  :ROSTER_QRY
--   AND R.roster_id = :schedule_param
  :APPOINTMENT_QRY
--   AND VA.id = :appointment_param
  ORDER BY VA.appointment_start