CREATE DEFINER=CURRENT_USER PROCEDURE `appointment_rpt`(IN `start_date_filter` DATE, IN `end_date_filter` DATE, IN `doctor_filter` INT)
BEGIN
	

SELECT 
    ROW_NUMBER() OVER (ORDER BY hrm_staffs.name) AS Sqn,hrm_staffs.name AS Doctor,
    COUNT(*) AS Frequency,
    concat("$ ",
    FORMAT(SUM(CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS Total
FROM
    appointments
        JOIN
    hrm_staffs ON hrm_staffs.id = appointments.doctor_id
    join payments on payments.reference_id = appointments.id and payments.reference = "appointment"
	where date between start_date_filter and end_date_filter and (doctor_filter = 0 or doctor_id = doctor_filter)
    

GROUP BY hrm_staffs.name
order by SUM(CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END) desc;

END
