CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_test_volume`(IN `start_date` DATE, IN `end_date` DATE, IN `kind` VARCHAR(10))
BEGIN
-- Tests performed by type and volume: each lab test / imaging type requested
-- in the period — how many, done, pending, the revenue charged for it and the
-- average turnaround (request -> result) in hours.
SELECT t.kind AS `Kind`, t.test AS `Test`, t.requested AS `Requested`, t.done AS `Done`, t.requested - t.done AS `Pending`,
       CONCAT('$ ', FORMAT(t.revenue, 2)) AS `Revenue`,
       CONCAT('$ ', FORMAT(t.revenue / NULLIF(t.requested, 0), 2)) AS `Avg price`,
       IFNULL(FORMAT(t.tat, 1), '-') AS `Avg turnaround (h)`
FROM (
    SELECT 'Lab' kind, COALESCE(NULLIF(r.routine_name, ''), lt.name, 'Test') test,
           COUNT(*) requested, SUM(l.status = 'Processed') done, SUM(IFNULL(lrr.price, 0)) revenue,
           AVG(IF(l.status = 'Processed', TIMESTAMPDIFF(MINUTE, l.created_at,
               COALESCE((SELECT MIN(res.created_at) FROM lab_request_results res WHERE res.lab_request_id = l.id), l.updated_at)) / 60, NULL)) tat
    FROM lab_request_routines lrr
    JOIN lab_requests l ON l.id = lrr.lab_request_id
    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 DATE(l.date) BETWEEN start_date AND end_date AND (kind = '0' OR kind = '' OR kind = 'Lab')
    GROUP BY test
    UNION ALL
    SELECT 'Imaging', COALESCE(it.name, 'Imaging'), COUNT(*), SUM(i.status = 'Processed'),
           SUM(IFNULL(i.price, 0) + IFNULL((SELECT SUM(ir.price) FROM image_request_routines ir WHERE ir.lab_request_id = i.id), 0)),
           AVG(IF(i.status = 'Processed', TIMESTAMPDIFF(MINUTE, i.created_at,
               COALESCE((SELECT MIN(rep.created_at) FROM imaging_reports rep WHERE rep.imaging_request_id = i.id), i.updated_at)) / 60, NULL))
    FROM image_requests i LEFT JOIN image_types it ON it.id = i.lab_type
    WHERE DATE(i.date) BETWEEN start_date AND end_date AND (kind = '0' OR kind = '' OR kind = 'Imaging')
    GROUP BY it.name
) t
ORDER BY t.kind, t.requested DESC;
END
