CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_patient_balances`(IN `start_date` DATE, IN `end_date` DATE, IN `type_filter` VARCHAR(20), IN `patient_type_filter` VARCHAR(20), IN `patient_filter` INT)
BEGIN
-- Patient balances: what each patient still owes, per bill type, for bills
-- dated in the period. Same bills and payments as Bill -> Statement:
-- charge per bill, minus the Payment rows of that bill (other currency
-- converted to $); pharmacy also minus till payments and lump-sum pharmacy
-- customer payments. Only patients with something left to pay are listed.
SELECT
    p.name                                                   AS `Patient`,
    p.patient_id                                             AS `Patient ID`,
    COALESCE(p.phone, '')                                    AS `Phone`,
    IF(EXISTS (SELECT 1 FROM admissions a WHERE a.patient_id = p.id), 'In-patient', 'Out-patient') AS `Type`,
    CONCAT('$ ', FORMAT(t.appointment, 2)) AS `Appointment`,
    CONCAT('$ ', FORMAT(t.lab, 2))         AS `Lab`,
    CONCAT('$ ', FORMAT(t.imaging, 2))     AS `Imaging`,
    CONCAT('$ ', FORMAT(t.admission, 2))   AS `Admission`,
    CONCAT('$ ', FORMAT(t.service, 2))     AS `Service`,
    CONCAT('$ ', FORMAT(t.emergency, 2))   AS `Emergency`,
    CONCAT('$ ', FORMAT(t.operation, 2))   AS `Operation`,
    CONCAT('$ ', FORMAT(t.delivery, 2))    AS `Delivery`,
    CONCAT('$ ', FORMAT(t.pharmacy, 2))    AS `Pharmacy`,
    CONCAT('$ ', FORMAT(t.charge, 2))      AS `Total charge`,
    CONCAT('$ ', FORMAT(t.charge - t.balance, 2)) AS `Paid + discount`,
    CONCAT('$ ', FORMAT(t.balance, 2))     AS `Balance`,
    DATE_FORMAT(t.last_bill, '%d %b, %Y')  AS `Last bill`
FROM (
    SELECT g.*, g.appointment + g.lab + g.imaging + g.admission + g.service + g.emergency + g.operation + g.delivery + g.pharmacy AS balance
    FROM (
        SELECT b.patient_id,
            SUM(IF(b.btype = 'Appointment', b.bal, 0)) AS appointment,
            SUM(IF(b.btype = 'Lab', b.bal, 0))         AS lab,
            SUM(IF(b.btype = 'Imaging', b.bal, 0))     AS imaging,
            SUM(IF(b.btype = 'Admission', b.bal, 0))   AS admission,
            SUM(IF(b.btype = 'Service', b.bal, 0))     AS service,
            SUM(IF(b.btype = 'Emergency', b.bal, 0))   AS emergency,
            SUM(IF(b.btype = 'Operation', b.bal, 0))   AS operation,
            SUM(IF(b.btype = 'Delivery', b.bal, 0))    AS delivery,
            GREATEST(SUM(IF(b.btype = 'Pharmacy', b.bal, 0)), 0) AS pharmacy,
            SUM(b.charge)                              AS charge,
            MAX(b.bdate)                               AS last_bill
        FROM (
    SELECT c.patient_id, c.btype, c.bdate, GREATEST(c.charge, 0) AS charge,
           -- a credit row (negative charge) lowers the balance
           IF(c.charge < 0, c.charge, GREATEST(c.charge - c.settled, 0)) AS 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 BETWEEN start_date AND end_date)
      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
    ) g
) t
JOIN patients p ON p.id = t.patient_id
WHERE t.balance > 0.009
  AND (patient_type_filter = '0' OR patient_type_filter = ''
       OR (patient_type_filter = 'In-patient' AND EXISTS (SELECT 1 FROM admissions a WHERE a.patient_id = p.id))
       OR (patient_type_filter = 'Out-patient' AND NOT EXISTS (SELECT 1 FROM admissions a WHERE a.patient_id = p.id)))
ORDER BY t.balance DESC;
END
