CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_doctor_performance`(IN `start_date` DATE, IN `end_date` DATE, IN `doctor_filter` INT)
BEGIN
-- Doctor performance: per doctor in the period — appointments, patients seen,
-- requests made (lab, imaging, prescriptions, admissions), the money collected
-- on their bills, and the commissions earned.
SELECT d.name AS `Doctor`,
       x.appts AS `Appointments`, x.seen AS `Seen`, x.patients AS `Patients`,
       x.labs AS `Lab requests`, x.imgs AS `Imaging requests`, x.rx AS `Prescriptions`, x.adm AS `Admissions`,
       CONCAT('$ ', FORMAT(x.revenue, 2)) AS `Revenue`,
       CONCAT('$ ', FORMAT(x.commission, 2)) AS `Commission`
FROM hrm_staffs d
JOIN (
    SELECT s.id,
        (SELECT COUNT(*) FROM appointments a WHERE a.doctor_id = s.id AND a.date BETWEEN start_date AND end_date) appts,
        (SELECT COUNT(*) FROM appointments a WHERE a.doctor_id = s.id AND a.date BETWEEN start_date AND end_date AND a.entry_status = 'Entered') seen,
        (SELECT COUNT(DISTINCT a.patient_id) FROM appointments a WHERE a.doctor_id = s.id AND a.date BETWEEN start_date AND end_date) patients,
        (SELECT COUNT(*) FROM lab_requests l WHERE l.doctor_id = s.id AND DATE(l.date) BETWEEN start_date AND end_date) labs,
        (SELECT COUNT(*) FROM image_requests i WHERE i.doctor_id = s.id AND DATE(i.date) BETWEEN start_date AND end_date) imgs,
        (SELECT COUNT(*) FROM pres_prescription_heads h JOIN appointments a ON a.id = h.appointment_id WHERE a.doctor_id = s.id AND DATE(h.created_at) BETWEEN start_date AND end_date) rx,
        (SELECT COUNT(*) FROM admissions ad WHERE ad.doctor_id = s.id AND COALESCE(ad.date_in, DATE(ad.date)) BETWEEN start_date AND end_date) adm,
        IFNULL((SELECT SUM(IF(p.currency = 2 AND p.exchange_rate > 0, p.payment / p.exchange_rate, p.payment)) FROM payments p
            WHERE p.status NOT IN ('Refund', 'Sponsored') AND DATE(p.created_at) BETWEEN start_date AND end_date
              AND ((p.reference = 'appointment' AND p.reference_id IN (SELECT a.id FROM appointments a WHERE a.doctor_id = s.id))
                OR (p.reference = 'Lab request' AND p.reference_id IN (SELECT l.id FROM lab_requests l WHERE l.doctor_id = s.id))
                OR (p.reference = 'Image request' AND p.reference_id IN (SELECT i.id FROM image_requests i WHERE i.doctor_id = s.id))
                OR (p.reference = 'Service' AND p.reference_id IN (SELECT sv.id FROM services sv WHERE sv.doctor_id = s.id))
                OR (p.reference = 'Operation' AND p.reference_id IN (SELECT o.id FROM operations o WHERE o.dr = s.id)))), 0) revenue,
        IFNULL((SELECT SUM(c.value) FROM commissions c JOIN payments p ON p.id = c.payment_id
            WHERE c.is_staff = 1 AND c.commissioner_id = s.id AND IFNULL(c.status, '') <> 'Refund' AND DATE(p.created_at) BETWEEN start_date AND end_date), 0) commission
    FROM hrm_staffs s
    WHERE s.job_title = 1 AND (doctor_filter = 0 OR s.id = doctor_filter)
) x ON x.id = d.id
WHERE x.appts + x.labs + x.imgs + x.adm > 0 OR x.revenue > 0
ORDER BY x.revenue DESC, x.appts DESC;
END
