CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_pharmacy_inpatient`(IN `start_date` DATE, IN `end_date` DATE, IN `cashier_filter` INT, IN `method_filter` INT)
BEGIN
-- Pharmacy in-patient: pharmacy money received through Bill payments /
-- Statement / Admission record (the Payment table), not at the pharmacy till.
-- "pharmacy" = paid on a pharmacy invoice; "Pharmacy" = lump-sum customer payment.
-- Invoice total / paid / balance are shown on the first payment row of each
-- invoice only, so the totals row doesn't count an invoice twice.
SELECT
    x.`Date`, x.`Kind`, x.`Invoice`, x.`Patient`, x.`Patient ID`,
    IF(x.first_row = 1, CONCAT('$ ', FORMAT(x.inv_total, 2)), '')   AS `Invoice total`,
    IF(x.first_row = 1, CONCAT('$ ', FORMAT(x.inv_paid, 2)), '')    AS `Invoice paid (all)`,
    IF(x.first_row = 1, CONCAT('$ ', FORMAT(GREATEST(x.inv_total - x.inv_paid, 0), 2)), '') AS `Invoice balance`,
    CONCAT('$ ', FORMAT(x.paid, 2))                                 AS `Paid`,
    CONCAT('$ ', FORMAT(x.discount, 2))                             AS `Discount`,
    x.`Payment method`, x.`Cashier`, x.`Note`
FROM (
    SELECT
        pm.created_at,
        DATE_FORMAT(pm.created_at, '%d %b, %Y %H:%i')                  AS `Date`,
        CASE WHEN BINARY pm.reference = 'Pharmacy' THEN 'Customer payment' ELSE 'Invoice payment' END AS `Kind`,
        COALESCE(CONCAT('#', pos.pos_id), '')                         AS `Invoice`,
        COALESCE(p1.name, p2.name, '')                                AS `Patient`,
        COALESCE(p1.patient_id, p2.patient_id, '')                    AS `Patient ID`,
        IF(pos.id IS NULL, 0, ROW_NUMBER() OVER (PARTITION BY COALESCE(pos.id, -pm.id) ORDER BY pm.created_at, pm.id)) AS first_row,
        COALESCE((SELECT SUM(l.price * l.quantity) FROM phar_pos_products l WHERE l.pos_id = pos.id), 0) AS inv_total,
        -- everything received on the invoice: at the till + later payments (any date)
        COALESCE((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)
          + COALESCE((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) AS inv_paid,
        IF(pm.currency = 2 AND pm.exchange_rate > 0, pm.payment / pm.exchange_rate, pm.payment) AS paid,
        IF(pm.currency = 2 AND pm.exchange_rate > 0, IFNULL(pm.discount, 0) / pm.exchange_rate, IFNULL(pm.discount, 0)) AS discount,
        COALESCE(apt.name, '')                                        AS `Payment method`,
        COALESCE(u.name, '')                                          AS `Cashier`,
        COALESCE(pm.description, '')                                  AS `Note`
    FROM payments pm
    LEFT JOIN phar_pos pos               ON BINARY pm.reference = 'pharmacy' AND pos.id = pm.reference_id
    LEFT JOIN patients p1                ON p1.id = pos.customer_id
    LEFT JOIN phar_customer_payments cp  ON BINARY pm.reference = 'Pharmacy' AND cp.id = pm.reference_id
    LEFT JOIN patients p2                ON p2.id = cp.customer_id
    LEFT JOIN users u                    ON u.id = pm.created_by
    LEFT JOIN acc_payment_types apt      ON apt.id = pm.payment_method
    WHERE pm.reference = 'pharmacy'
      AND pm.status <> 'Refund'
      AND DATE(pm.created_at) BETWEEN start_date AND end_date
      AND (cashier_filter = 0 OR pm.created_by = cashier_filter)
      AND (method_filter = 0 OR pm.payment_method = method_filter)
) x
ORDER BY x.created_at;
END
