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,
    DATEADD(SECOND, EVENT_DATA.first_checkin_timezone , EVENT_DATA.first_checkin_time ) AS first_checkin_time,
    EVENT_DATA.first_checkin_timezone,
    D_IN.description AS first_checkin_device,
    PL_IN.name AS first_checkin_location,
    DATEADD(SECOND, EVENT_DATA.last_checkout_timezone , EVENT_DATA.last_checkout_time) 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,
                    DATEADD(SECOND, (-BASE.appointment_timezone) , BASE.appointment_start) AS appointment_start,
                    DATEADD(SECOND,(-BASE.appointment_timezone), BASE.appointment_end) AS appointment_end,
                    DATEADD(SECOND,(-BASE.appointment_timezone + BASE.shift_start_second), CONVERT(DATETIME, BASE.appointment_day)) AS appointment_shift_start,
                    DATEADD(SECOND, (-BASE.appointment_timezone + BASE.shift_end_second), CONVERT(DATETIME, BASE.appointment_day)) AS appointment_shift_end,
                    DATEADD(SECOND, (-BASE.appointment_timezone + BASE.cin_start) , CONVERT(DATETIME, BASE.appointment_day)) AS appointment_cin_start,
                    DATEADD(SECOND, (-BASE.appointment_timezone + BASE.cout_end) , CONVERT(DATETIME, BASE.appointment_day)) 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 DATEDIFF(SECOND, CONVERT(DATETIME, CONVERT(DATE , va.appointment_start)) , va.appointment_start ) < t.start_time_seconds) THEN CONVERT(DATE,
                                                                                                                                                                               DATEADD(DAY, -1, va.appointment_start))
                                ELSE CONVERT(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 DATEADD(DAY, -2, :onparam) AND DATEADD(DAY, 2, :onparam)
                          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 = 2 THEN 1 ELSE 0 END) AS access_count,
                    SUM(CASE WHEN cout.event_time IS NOT NULL THEN (DATEDIFF(SECOND, cin.event_time, cout.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 DATEADD(DAY , -2, :onparam) AND DATEADD(DAY, 2, :onparam)
                  AND VA.request_status = 'APPROVED'
                  AND p.person_type = 'VISITOR'
                  AND cin.is_deleted = 0
                  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 = 0
                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 = 0



    ) 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  OFFSET 0 ROWS