CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_revenue_by`(IN `start_date` DATE, IN `end_date` DATE, IN `group_by` VARCHAR(20))
BEGIN
-- Revenue (money collected) by department, doctor or bill type. Each payment
-- is traced to its bill: the department / doctor of the bill or of the
-- appointment it came from; admission, emergency and pharmacy are their own
-- departments. Refunded and sponsored rows left out, other currency in $.
SELECT r.grp AS `Group`, r.n AS `Payments`, CONCAT('$ ', FORMAT(r.amount, 2)) AS `Collected`,
       CONCAT(FORMAT(100 * r.amount / NULLIF(SUM(r.amount) OVER (), 0), 1), ' %') AS `Share`
FROM (
    SELECT CASE WHEN group_by = 'Doctor' THEN COALESCE(dr.name, '(no doctor)')
                WHEN group_by = 'Type' THEN x.btype
                ELSE COALESCE(dp.name, x.dept_fallback) END AS grp,
           COUNT(*) n, SUM(x.amount) amount
    FROM (
        SELECT p.id,
               IF(p.currency = 2 AND p.exchange_rate > 0, p.payment / p.exchange_rate, p.payment) amount,
               CASE LOWER(p.reference) WHEN 'appointment' THEN 'Appointment' WHEN 'lab request' THEN 'Laboratory' WHEN 'image request' THEN 'Imaging'
                    WHEN 'service' THEN 'Service' WHEN 'operation' THEN 'Operation' WHEN 'admission' THEN 'Admission' WHEN 'emergency' THEN 'Emergency'
                    WHEN 'delivery' THEN 'Delivery' WHEN 'pharmacy' THEN 'Pharmacy' WHEN 'dental plan' THEN 'Dental plan' ELSE p.reference END btype,
               CASE LOWER(p.reference)
                    WHEN 'appointment' THEN (SELECT a.department_id FROM appointments a WHERE a.id = p.reference_id)
                    WHEN 'lab request' THEN (SELECT a.department_id FROM lab_requests l JOIN appointments a ON a.id = l.appointment_id WHERE l.id = p.reference_id)
                    WHEN 'image request' THEN (SELECT a.department_id FROM image_requests i JOIN appointments a ON a.id = i.appointment_id WHERE i.id = p.reference_id)
                    WHEN 'service' THEN (SELECT a.department_id FROM services s JOIN appointments a ON a.id = s.appointment_id WHERE s.id = p.reference_id)
                    WHEN 'operation' THEN (SELECT a.department_id FROM operations o JOIN appointments a ON a.id = o.appointment_id WHERE o.id = p.reference_id)
                    ELSE NULL END dept,
               CASE LOWER(p.reference) WHEN 'admission' THEN 'Inpatient' WHEN 'delivery' THEN 'Inpatient' WHEN 'emergency' THEN 'Emergency'
                    WHEN 'pharmacy' THEN 'Pharmacy' WHEN 'dental plan' THEN 'Dental' WHEN 'lab request' THEN 'Laboratory (direct)'
                    WHEN 'image request' THEN 'Imaging (direct)' WHEN 'operation' THEN 'Theatre (operations)' WHEN 'service' THEN 'Services'
                    WHEN 'appointment' THEN 'Outpatient' ELSE 'Other' END dept_fallback,
               CASE LOWER(p.reference)
                    WHEN 'appointment' THEN (SELECT a.doctor_id FROM appointments a WHERE a.id = p.reference_id)
                    WHEN 'lab request' THEN (SELECT l.doctor_id FROM lab_requests l WHERE l.id = p.reference_id)
                    WHEN 'image request' THEN (SELECT i.doctor_id FROM image_requests i WHERE i.id = p.reference_id)
                    WHEN 'service' THEN (SELECT s.doctor_id FROM services s WHERE s.id = p.reference_id)
                    WHEN 'operation' THEN (SELECT o.dr FROM operations o WHERE o.id = p.reference_id)
                    WHEN 'admission' THEN (SELECT ad.doctor_id FROM admissions ad WHERE ad.id = p.reference_id)
                    WHEN 'emergency' THEN (SELECT e.doctor_id FROM emergencies e WHERE e.id = p.reference_id)
                    ELSE NULL END doctor
        FROM payments p
        WHERE p.status NOT IN ('Refund', 'Sponsored') AND p.reference <> 'Pharmacy' AND DATE(p.created_at) BETWEEN start_date AND end_date
        UNION ALL
        SELECT pp.id, IF(pp.currency = 2 AND pp.exchange_rate > 0, pp.amount / pp.exchange_rate, pp.amount), 'Pharmacy', NULL, 'Pharmacy', NULL
        FROM phar_pos_payments pp
        WHERE IFNULL(pp.amount, 0) > 0 AND pp.refund_of IS NULL AND DATE(pp.date) BETWEEN start_date AND end_date
    ) x
    LEFT JOIN hrm_departments dp ON dp.id = x.dept
    LEFT JOIN hrm_staffs dr ON dr.id = x.doctor
    GROUP BY grp
) r
ORDER BY r.amount DESC;
END
