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


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
    COUNT(*)
FROM
    visitor_appointments VA
        INNER JOIN visit V ON
            VA.visit_id = V.id
        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
WHERE
     VA.request_status = 'APPROVED' AND 
    VA.customer_id =@customerparam
    AND VA.appointment_start BETWEEN @fromparam AND @toparam

", @PERSON_QRY, @ROSTER_QRY, @APPOINTMENT_QRY
);

PREPARE stmt FROM @QRY;
EXECUTE stmt;
DEALLOCATE  PREPARE stmt;
END