CREATE PROCEDURE VisitAppointmentReport(IN customerparam INT, IN personparam INT, IN scheduleparam INT, IN appointmentparam INT, IN fromparam DATETIME, IN toparam DATETIME, IN offsetparam INT, IN countparam INT)
BEGIN
SET @customerparam = customerparam;
SET @personparam = personparam;
SET @scheduleparam = scheduleparam;
SET @appointmentparam = appointmentparam;
SET @fromparam = fromparam;
SET @toparam = toparam;
SET @offsetparam= offsetparam;
SET @countparam= countparam;


SET @PERSON_QRY = CASE WHEN (@personparam > 0) THEN " AND V.visitor_id =  @personparam " ELSE " " END;
SET @ROSTER_QRY = CASE WHEN (@scheduleparam > 0) THEN " AND R.roster_id = @scheduleparam " ELSE " " END;
SET @APPOINTMENT_QRY = CASE WHEN (@appointmentparam > 0) THEN " AND VA.id = @appointmentparam " ELSE " " END;

SET @QRY = CONCAT("


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,
    VJ.job_name 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,
    EVENT_DATA.first_checkin_time,
    EVENT_DATA.first_checkin_timezone,
    D_IN.description AS first_checkin_device,
    PL_IN.name AS first_checkin_location,
    EVENT_DATA.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
FROM
    visitor_appointments VA
        LEFT JOIN

    (
        SELECT
            EVENT_CORE.person_id,
            EVENT_CORE.visit_appointment,
            CIN.event_id AS first_checkin,
            DATE_ADD( CIN.event_time, INTERVAL CIN.event_time_zone SECOND) 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,
            DATE_ADD( COUT.event_time, INTERVAL COUT.event_time_zone SECOND) 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
        FROM
            (
                SELECT
                    cin.person_id AS person_id,
                    cin.visit_appointment ,
                    MIN(CASE WHEN cin.event_type = 0 THEN cin.event_time ELSE NULL END) AS first_checkin,
                    MAX(cout.event_time) AS last_checkout,
                    SUM(CASE WHEN cin.event_type = 'ACCESS' THEN 1 ELSE 0 END) AS access_count
                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
                        LEFT JOIN visitor_appointments VA ON
                            cin.visit_appointment = VA.id
                WHERE
                        p.customer_id = @customerparam
                  AND VA.appointment_start BETWEEN @fromparam AND @toparam
                  AND VA.request_status = 'APPROVED'
                  AND p.person_type = 'VISITOR'
                  AND cin.is_deleted = FALSE
                GROUP BY
                    cin.visit_appointment ,
                    cin.person_id,
                    cin.device_id ,
                    cin.physical_location_id) 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
            EVENT_DATA.visit_appointment = 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 visit_job VJ ON
            VJ.id = VA.visit_job_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.first_checkin_location = PL_OUT.id
WHERE
  VA.customer_id =@customerparam
  AND VA.appointment_start BETWEEN @fromparam AND @toparam
  AND VA.request_status = 'APPROVED'
"
, @PERSON_QRY, @ROSTER_QRY, @APPOINTMENT_QRY,

"  ORDER BY VA.appointment_start
  LIMIT  ? , ?

"



);

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