CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_opd_visits`(IN `start_date` DATE, IN `end_date` DATE, IN `group_by` VARCHAR(20))
BEGIN
-- OPD (appointment) visits by day, department or doctor: visits, how many
-- were seen (entered), follow-ups, and the card fees charged.
SELECT g.grp AS `Group`, g.visits AS `Visits`, g.seen AS `Seen by doctor`, g.not_seen AS `Not seen`,
       g.follow AS `Follow-ups`, CONCAT('$ ', FORMAT(g.fees, 2)) AS `Card fees`
FROM (
    SELECT CASE WHEN group_by = 'Department' THEN COALESCE(dp.name, '-')
                WHEN group_by = 'Doctor' THEN COALESCE(dr.name, '-')
                ELSE DATE_FORMAT(a.date, '%Y-%m-%d (%a)') END grp,
           COUNT(*) visits,
           SUM(a.entry_status = 'Entered') seen,
           SUM(IFNULL(a.entry_status, '') <> 'Entered') not_seen,
           SUM(a.follow_up_of IS NOT NULL OR t.name LIKE 'Follow%') follow,
           SUM(IFNULL(a.price, 0)) fees
    FROM appointments a
    LEFT JOIN hrm_departments dp ON dp.id = a.department_id
    LEFT JOIN hrm_staffs dr ON dr.id = a.doctor_id
    LEFT JOIN appointment_types t ON t.id = a.appointment_type_id
    WHERE a.date BETWEEN start_date AND end_date
    GROUP BY grp
) g
ORDER BY IF(group_by IN ('Department', 'Doctor'), -g.visits, 0), g.grp;
END
