CREATE PROCEDURE VisitAppointmentReport(@customerparam INT, @personparam INT, @scheduleparam INT, @appointmentparam INT, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT, @countparam INT)
    AS
BEGIN

DECLARE @QRY NVARCHAR(MAX),@PERSON_QRY VARCHAR(100),@ROSTER_QRY VARCHAR(100),@APPOINTMENT_QRY VARCHAR(100);

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,
            DATEADD(SECOND,CIN.event_time_zone, 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,
            DATEADD(SECOND,  COUT.event_time_zone,  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
        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 = 2 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 = 0
                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 = 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
            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   OFFSET @offsetparam ROWS FETCH NEXT @countparam ROWS ONLY  '

);


DECLARE @parameterDefinition NVARCHAR(4000);
SET @parameterDefinition = N'@customerparam INT, @personparam INT, @scheduleparam INT, @appointmentparam INT, @fromparam DATETIME, @toparam DATETIME, @offsetparam INT, @countparam INT';
EXEC sp_executesql @QRY ,@parameterDefinition, @customerparam=@customerparam, @personparam=@personparam, @scheduleparam=@scheduleparam, @appointmentparam=@appointmentparam, @fromparam=@fromparam, @toparam=@toparam, @offsetparam=@offsetparam, @countparam=@countparam;

END