CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_image_detailed`(IN `start_date` DATE, IN `end_date` DATE, IN `doctor_filter` INT, IN `cashier_filter` INT, IN `status_filter` VARCHAR(20))
BEGIN
-- Imaging requests: every scan / x-ray, report status and 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.`Request`,
    y.`Patient`,
    y.`Patient ID`,
    y.`Exam`,
    y.`Requesting doctor`,
    y.`Report`,
    y.`Reporting doctor`,
    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`,
        CONCAT('#', COALESCE(t.request_no, t.id)) AS `Request`,
        p.name AS `Patient`,
        p.patient_id AS `Patient ID`,
        COALESCE(it.name, '') AS `Exam`,
        COALESCE(d.name, '') AS `Requesting doctor`,
        CASE WHEN t.status = 'Processed' THEN 'Reported' ELSE 'Pending report' END AS `Report`,
        COALESCE((SELECT rd.name FROM imaging_reports ir JOIN hrm_staffs rd ON rd.id = ir.reporting_doctor_id WHERE ir.imaging_request_id = t.id ORDER BY ir.id DESC LIMIT 1), '') AS `Reporting doctor`,
        IFNULL(t.price, 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 = 'Image request' 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 = 'Image request' 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 = 'Image request' AND pm.reference_id = t.id AND pm.status <> 'Refund') AS cashier,
        t.sort_date AS sort_date
        FROM (SELECT i.*, i.date AS sort_date FROM image_requests i) t LEFT JOIN patients p ON p.id = t.patient LEFT JOIN image_types it ON it.id = t.lab_type LEFT JOIN hrm_staffs d ON d.id = t.doctor_id
        WHERE DATE(t.date) BETWEEN start_date AND end_date AND (doctor_filter = 0 OR t.doctor_id = doctor_filter)
          AND (cashier_filter = 0 OR EXISTS (SELECT 1 FROM payments pm WHERE pm.reference = 'Image request' 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
