CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_pending_results`(IN `start_date` DATE, IN `end_date` DATE, IN `kind` VARCHAR(10), IN `older_than_hours` INT)
BEGIN
-- Pending / uncollected results: lab and imaging requests (made in the period) with no result yet,
-- oldest first, with how long they have been waiting and whether they are paid.
SELECT DATE_FORMAT(x.at, '%d %b, %Y %H:%i') AS `Requested`, x.kind AS `Kind`, x.req AS `Request`,
       COALESCE(p.name, '') AS `Patient`, COALESCE(p.patient_id, '') AS `Patient ID`, x.tests AS `Tests`,
       COALESCE(d.name, '') AS `Doctor`,
       CONCAT(FLOOR(TIMESTAMPDIFF(MINUTE, x.at, NOW()) / 60), ' h ', MOD(TIMESTAMPDIFF(MINUTE, x.at, NOW()), 60), ' min') AS `Waiting`,
       COALESCE(x.pay, '') AS `Payment`
FROM (
    SELECT l.created_at at, 'Lab' kind, CONCAT('#', COALESCE(l.request_no, l.id), IF(l.sample_id IS NULL, '', CONCAT(' · S:', l.sample_id))) req, l.patient pid, l.doctor_id,
           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 = l.lab_type WHERE lrr.lab_request_id = l.id), '') tests,
           l.payment_status pay
    FROM lab_requests l WHERE IFNULL(l.status, 'Pending') <> 'Processed' AND (kind = '0' OR kind = '' OR kind = 'Lab')
      AND DATE(l.created_at) BETWEEN start_date AND end_date
    UNION ALL
    SELECT i.created_at, 'Imaging', CONCAT('#', COALESCE(i.request_no, i.id)), i.patient, i.doctor_id, COALESCE(it.name, ''), i.payment_status
    FROM image_requests i LEFT JOIN image_types it ON it.id = i.lab_type
    WHERE IFNULL(i.status, 'Pending') <> 'Processed' AND (kind = '0' OR kind = '' OR kind = 'Imaging')
      AND DATE(i.created_at) BETWEEN start_date AND end_date
) x
LEFT JOIN patients p ON p.id = x.pid
LEFT JOIN hrm_staffs d ON d.id = x.doctor_id
WHERE TIMESTAMPDIFF(HOUR, x.at, NOW()) >= IFNULL(older_than_hours, 0)
ORDER BY x.at;
END
