CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_operation_detailed`(IN `start_date` DATE, IN `end_date` DATE, IN `doctor_filter` INT, IN `cashier_filter` INT, IN `status_filter` VARCHAR(20))
BEGIN
-- Operations: every operation (appointment, admission, outside) and its payment.
-- Paid / discount / cashier come from the Payment rows of each bill (other
-- currency converted to $). Payment status: Paid (nothing left), Partial
-- (something paid), Unpaid, or No charge.
SELECT
    y.`Date`,
    y.`No.`,
    y.`Patient`,
    y.`Patient ID`,
    y.`Operation`,
    y.`Source`,
    y.`Doctor`,
    y.`Status`,
    CONCAT('$ ', FORMAT(y.charge, 2))   AS `Charge`,
    CONCAT('$ ', FORMAT(y.paid, 2))     AS `Paid`,
    CONCAT('$ ', FORMAT(y.discount, 2)) AS `Discount`,
    CONCAT('$ ', FORMAT(y.balance, 2))  AS `Balance`,
    y.pstatus                           AS `Payment status`,
    COALESCE(y.cashier, '')             AS `Cashier`
FROM (
    SELECT x.*,
        GREATEST(x.charge - x.paid - x.discount, 0) AS balance,
        CASE WHEN x.charge <= 0 THEN 'No charge'
             WHEN x.charge - x.paid - x.discount <= 0.009 THEN 'Paid'
             WHEN x.paid + x.discount > 0 THEN 'Partial'
             ELSE 'Unpaid' END AS pstatus
    FROM (
        SELECT
        DATE_FORMAT(t.date, '%d %b, %Y') AS `Date`,
        COALESCE(t.operation_number, t.id) AS `No.`,
        p.name AS `Patient`,
        p.patient_id AS `Patient ID`,
        COALESCE(ot.name, '') AS `Operation`,
        CASE WHEN t.admission_id IS NOT NULL THEN 'Admission' ELSE COALESCE(t.type, '') END AS `Source`,
        COALESCE(d.name, '') AS `Doctor`,
        COALESCE(t.status, '') AS `Status`,
        IFNULL(t.rate, 0) AS charge,
        (SELECT IFNULL(SUM(IF(pm.currency = 2 AND pm.exchange_rate > 0, pm.payment / pm.exchange_rate, pm.payment)), 0) FROM payments pm WHERE pm.reference = 'Operation' AND pm.reference_id = t.id AND pm.status <> 'Refund') AS paid,
        (SELECT IFNULL(SUM(IF(pm.currency = 2 AND pm.exchange_rate > 0, pm.discount / pm.exchange_rate, pm.discount) + IFNULL(IF(pm.currency = 2 AND pm.exchange_rate > 0, pm.promotion / pm.exchange_rate, pm.promotion), 0)), 0) FROM payments pm WHERE pm.reference = 'Operation' AND pm.reference_id = t.id AND pm.status <> 'Refund') AS discount,
        (SELECT GROUP_CONCAT(DISTINCT u.name SEPARATOR ', ') FROM payments pm JOIN users u ON u.id = pm.created_by WHERE pm.reference = 'Operation' AND pm.reference_id = t.id AND pm.status <> 'Refund') AS cashier,
        t.sort_date AS sort_date
        FROM (SELECT o.*, o.date AS sort_date FROM operations o) t LEFT JOIN patients p ON p.id = t.patient LEFT JOIN operation_types ot ON ot.id = t.operation LEFT JOIN hrm_staffs d ON d.id = t.dr
        WHERE DATE(t.date) BETWEEN start_date AND end_date AND (doctor_filter = 0 OR t.dr = doctor_filter)
          AND (cashier_filter = 0 OR EXISTS (SELECT 1 FROM payments pm WHERE pm.reference = 'Operation' AND pm.reference_id = t.id AND pm.status <> 'Refund' AND pm.created_by = cashier_filter))
    ) x
) y
WHERE (status_filter = '0' OR status_filter = '' OR y.pstatus = status_filter)
ORDER BY y.sort_date, 1;
END
