CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_sponsored_bills`(IN `start_date` DATE, IN `end_date` DATE)
BEGIN
-- Insurance / corporate (sponsored) bills: every bill a sponsor took over —
-- the amount the hospital bills the sponsor, with the note written when sponsoring.
SELECT DATE_FORMAT(p.created_at, '%d %b, %Y') AS `Date`,
       CASE LOWER(p.reference) WHEN 'lab request' THEN 'Laboratory' WHEN 'image request' THEN 'Imaging' ELSE p.reference END AS `Bill`,
       CONCAT('#', p.reference_id) AS `Bill #`,
       COALESCE(pt.name, '') AS `Patient`, COALESCE(pt.patient_id, '') AS `Patient ID`,
       CONCAT('$ ', FORMAT(IF(p.currency = 2 AND p.exchange_rate > 0, IFNULL(p.sponsor, 0) / p.exchange_rate, IFNULL(p.sponsor, 0)), 2)) AS `Sponsored amount`,
       COALESCE(p.description, '') AS `Sponsor / note`,
       COALESCE(u.name, '') AS `By`
FROM payments p
LEFT JOIN users u ON u.id = p.created_by
LEFT JOIN patients pt ON pt.id = CASE LOWER(p.reference)
    WHEN 'appointment' THEN (SELECT a.patient_id FROM appointments a WHERE a.id = p.reference_id)
    WHEN 'lab request' THEN (SELECT l.patient FROM lab_requests l WHERE l.id = p.reference_id)
    WHEN 'image request' THEN (SELECT i.patient FROM image_requests i WHERE i.id = p.reference_id)
    WHEN 'service' THEN (SELECT s.patient FROM services s WHERE s.id = p.reference_id)
    WHEN 'operation' THEN (SELECT o.patient FROM operations o WHERE o.id = p.reference_id)
    WHEN 'admission' THEN (SELECT ad.patient_id FROM admissions ad WHERE ad.id = p.reference_id)
    ELSE NULL END
WHERE p.status = 'Sponsored' AND DATE(p.created_at) BETWEEN start_date AND end_date
ORDER BY p.created_at;
END
