CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_aging`(IN `as_of` DATE, IN `type_filter` VARCHAR(20), IN `patient_filter` INT)
BEGIN
-- Outstanding balances by age (aging): every bill still owed on the date, put
-- in a bucket by how old it is — 0-30, 31-60, 61-90, over 90 days. Same bills
-- and payments as Patient balances / Bill -> Statement (other currency in $).
SELECT p.name AS `Patient`, p.patient_id AS `Patient ID`, COALESCE(p.phone, '') AS `Phone`,
       CONCAT('$ ', FORMAT(t.b0, 2))  AS `0-30 days`,
       CONCAT('$ ', FORMAT(t.b30, 2)) AS `31-60 days`,
       CONCAT('$ ', FORMAT(t.b60, 2)) AS `61-90 days`,
       CONCAT('$ ', FORMAT(t.b90, 2)) AS `Over 90 days`,
       CONCAT('$ ', FORMAT(t.total, 2)) AS `Total owed`,
       DATE_FORMAT(t.oldest, '%d %b, %Y') AS `Oldest bill`
FROM (
    SELECT b.patient_id,
           SUM(IF(b.age <= 30, b.bal, 0)) b0,
           SUM(IF(b.age BETWEEN 31 AND 60, b.bal, 0)) b30,
           SUM(IF(b.age BETWEEN 61 AND 90, b.bal, 0)) b60,
           SUM(IF(b.age > 90, b.bal, 0)) b90,
           SUM(b.bal) total, MIN(IF(b.bal > 0.009, b.bdate, NULL)) oldest
    FROM (
        SELECT c.patient_id, c.btype, c.bdate, DATEDIFF(as_of, COALESCE(c.bdate, as_of)) age,
               IF(c.charge < 0, c.charge, GREATEST(c.charge - c.settled, 0)) bal
        FROM (
        SELECT a.patient_id, 'Appointment' AS btype, a.date AS bdate, IFNULL(a.price, 0) AS charge,
               IFNULL(pa.settled, 0) AS settled
        FROM appointments a LEFT JOIN (
            SELECT reference_id, SUM(IF(currency = 2 AND exchange_rate > 0, (payment + IFNULL(discount, 0) + IFNULL(promotion, 0)) / exchange_rate, payment + IFNULL(discount, 0) + IFNULL(promotion, 0))) settled
            FROM payments WHERE reference = 'appointment' AND status <> 'Refund' GROUP BY reference_id) pa ON pa.reference_id = a.id
        UNION ALL
        SELECT l.patient, 'Lab', l.date,
               IFNULL(l.price, 0) + IFNULL((SELECT SUM(r.price) FROM lab_request_routines r WHERE r.lab_request_id = l.id), 0),
               IFNULL(pa.settled, 0)
        FROM lab_requests l LEFT JOIN (
            SELECT reference_id, SUM(IF(currency = 2 AND exchange_rate > 0, (payment + IFNULL(discount, 0) + IFNULL(promotion, 0)) / exchange_rate, payment + IFNULL(discount, 0) + IFNULL(promotion, 0))) settled
            FROM payments WHERE reference = 'Lab request' AND status <> 'Refund' GROUP BY reference_id) pa ON pa.reference_id = l.id
        UNION ALL
        SELECT i.patient, 'Imaging', i.date, IFNULL(i.price, 0), IFNULL(pa.settled, 0)
        FROM image_requests i LEFT JOIN (
            SELECT reference_id, SUM(IF(currency = 2 AND exchange_rate > 0, (payment + IFNULL(discount, 0) + IFNULL(promotion, 0)) / exchange_rate, payment + IFNULL(discount, 0) + IFNULL(promotion, 0))) settled
            FROM payments WHERE reference = 'Image request' AND status <> 'Refund' GROUP BY reference_id) pa ON pa.reference_id = i.id
        UNION ALL
        SELECT ad.patient_id, 'Admission', COALESCE(ad.date_in, ad.date),
               IF(IFNULL(ad.cost, 0) > 0, ad.cost,
                  IFNULL(rm.cost, 0) * GREATEST(COALESCE(NULLIF(ad.days, 0), DATEDIFF(COALESCE(ad.date_out, CURDATE()), COALESCE(ad.date_in, ad.date))), 1)),
               IFNULL(pa.settled, 0)
        FROM admissions ad LEFT JOIN inp_rooms rm ON rm.id = ad.room LEFT JOIN (
            SELECT reference_id, SUM(IF(currency = 2 AND exchange_rate > 0, (payment + IFNULL(discount, 0) + IFNULL(promotion, 0)) / exchange_rate, payment + IFNULL(discount, 0) + IFNULL(promotion, 0))) settled
            FROM payments WHERE reference = 'Admission' AND status <> 'Refund' GROUP BY reference_id) pa ON pa.reference_id = ad.id
        WHERE IFNULL(ad.status, '') <> 'Cancel'
        UNION ALL
        SELECT s.patient, 'Service', s.date, IFNULL(s.rate, 0), IFNULL(pa.settled, 0)
        FROM services s LEFT JOIN (
            SELECT reference_id, SUM(IF(currency = 2 AND exchange_rate > 0, (payment + IFNULL(discount, 0) + IFNULL(promotion, 0)) / exchange_rate, payment + IFNULL(discount, 0) + IFNULL(promotion, 0))) settled
            FROM payments WHERE reference = 'Service' AND status <> 'Refund' GROUP BY reference_id) pa ON pa.reference_id = s.id
        UNION ALL
        SELECT e.patient_id, 'Emergency', DATE(e.date_in), COALESCE(NULLIF(e.cost, 0), IFNULL(e.hours, 0) * IFNULL(e.rate, 0), 0), IFNULL(pa.settled, 0)
        FROM emergencies e LEFT JOIN (
            SELECT reference_id, SUM(IF(currency = 2 AND exchange_rate > 0, (payment + IFNULL(discount, 0) + IFNULL(promotion, 0)) / exchange_rate, payment + IFNULL(discount, 0) + IFNULL(promotion, 0))) settled
            FROM payments WHERE reference = 'emergency' AND status <> 'Refund' GROUP BY reference_id) pa ON pa.reference_id = e.id
        UNION ALL
        SELECT o.patient, 'Operation', o.date, IFNULL(o.rate, 0), IFNULL(pa.settled, 0)
        FROM operations o LEFT JOIN (
            SELECT reference_id, SUM(IF(currency = 2 AND exchange_rate > 0, (payment + IFNULL(discount, 0) + IFNULL(promotion, 0)) / exchange_rate, payment + IFNULL(discount, 0) + IFNULL(promotion, 0))) settled
            FROM payments WHERE reference = 'Operation' AND status <> 'Refund' GROUP BY reference_id) pa ON pa.reference_id = o.id
        UNION ALL
        SELECT d.patient_id, 'Delivery', d.date, IFNULL(d.rate, 0), IFNULL(pa.settled, 0)
        FROM deliveries d LEFT JOIN (
            SELECT reference_id, SUM(IF(currency = 2 AND exchange_rate > 0, (payment + IFNULL(discount, 0) + IFNULL(promotion, 0)) / exchange_rate, payment + IFNULL(discount, 0) + IFNULL(promotion, 0))) settled
            FROM payments WHERE reference = 'Delivery' AND status <> 'Refund' GROUP BY reference_id) pa ON pa.reference_id = d.id
        UNION ALL
        -- pharmacy sales: till payments + later payments on the invoice
        SELECT pos.customer_id, 'Pharmacy', pos.pos_date,
               IFNULL((SELECT SUM(l.price * l.quantity) FROM phar_pos_products l WHERE l.pos_id = pos.id), 0),
               IFNULL((SELECT SUM(IF(pp.currency = 2 AND pp.exchange_rate > 0, (pp.amount + IFNULL(pp.discount, 0)) / pp.exchange_rate, pp.amount + IFNULL(pp.discount, 0)))
                         FROM phar_pos_payments pp WHERE pp.pos_id = pos.id), 0)
             + IFNULL((SELECT SUM(IF(q.currency = 2 AND q.exchange_rate > 0, (q.payment + IFNULL(q.discount, 0)) / q.exchange_rate, q.payment + IFNULL(q.discount, 0)))
                         FROM payments q WHERE BINARY q.reference = 'pharmacy' AND q.reference_id = pos.id AND q.status <> 'Refund'), 0)
        FROM phar_pos pos
        WHERE pos.customer_id > 0 AND IFNULL(pos.status, '') <> 'Void' AND IFNULL(pos.is_deleted, 0) = 0
        UNION ALL
        -- lump-sum pharmacy customer payments not tied to an invoice (negative
        -- bill: lowers the pharmacy balance; the Statement applies them the same way)
        SELECT cp.customer_id, 'Pharmacy', NULL,
               -GREATEST(SUM(IF(cp.currency = 2 AND cp.exchange_rate > 0, (cp.payment + IFNULL(cp.discount, 0)) / cp.exchange_rate, cp.payment + IFNULL(cp.discount, 0)))
                 - IFNULL((SELECT SUM(q.payment + IFNULL(q.discount, 0)) FROM payments q JOIN phar_pos pz ON pz.id = q.reference_id
                            WHERE BINARY q.reference = 'pharmacy' AND q.status <> 'Refund' AND pz.customer_id = cp.customer_id), 0), 0),
               0
        FROM phar_customer_payments cp
        GROUP BY cp.customer_id
        ) c
        WHERE c.patient_id IS NOT NULL AND (c.bdate IS NULL OR c.bdate <= as_of)
          AND (type_filter = '0' OR type_filter = '' OR c.btype = type_filter)
          AND (patient_filter = 0 OR c.patient_id = patient_filter)
    ) b
    GROUP BY b.patient_id
) t
JOIN patients p ON p.id = t.patient_id
WHERE t.total > 0.009
ORDER BY t.b90 DESC, t.total DESC;
END
