CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_lab_detailed`(IN `start_date` DATE, IN `end_date` DATE, IN `doctor_filter` INT, IN `cashier_filter` INT, IN `status_filter` VARCHAR(20))
BEGIN
-- Laboratory: every lab request, its tests 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.`Source`,
    y.`Doctor`,
    y.`Tests`,
    y.`Result`,
    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('#', LPAD(COALESCE(t.request_no, t.id), 4, '0')) AS `Request`,
        p.name AS `Patient`,
        p.patient_id AS `Patient ID`,
        CASE WHEN t.admission_id IS NOT NULL THEN 'Admission' WHEN t.emergency_id IS NOT NULL THEN 'Emergency' WHEN t.appointment_id IS NOT NULL THEN 'Appointment' ELSE COALESCE(t.type, 'Outside') END AS `Source`,
        COALESCE(d.name, '') AS `Doctor`,
        COALESCE((SELECT GROUP_CONCAT(COALESCE(NULLIF(r.routine_name, ''), lt.name) SEPARATOR ', ') FROM lab_request_routines lrr LEFT JOIN lab_lab_type_routines r ON r.id = lrr.routine_id LEFT JOIN lab_lab_types lt ON lt.id = COALESCE(NULLIF(r.result_component, 0), r.lab_type_id) WHERE lrr.lab_request_id = t.id), '') AS `Tests`,
        COALESCE(t.status, '') AS `Result`,
        IFNULL(t.price, 0) + (SELECT IFNULL(SUM(lrr.price), 0) FROM lab_request_routines lrr WHERE lrr.lab_request_id = t.id) 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 = 'Lab 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 = 'Lab 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 = 'Lab request' AND pm.reference_id = t.id AND pm.status <> 'Refund') AS cashier,
        t.sort_date AS sort_date
        FROM (SELECT l.*, l.date AS sort_date FROM lab_requests l) t LEFT JOIN patients p ON p.id = t.patient 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 = 'Lab 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
