DROP PROCEDURE IF EXISTS sp_tat_details;
CREATE PROCEDURE `sp_tat_details`(IN `tat_fromdt` DATE, IN `tat_todt` DATE)
BEGIN
	SELECT
		lab_booking.case_id,
		lab_test_master.analyte_name,
		lab_test_master.analytecode,
		patients.patient_name,
		patients.patient_unique_id,
		lab_department.department_name,
		lab_booking_details.reporting_date,
		lab_booking_details.test_schedule_date,
		lab_booking_tat.booking_time,
		lab_booking_tat.barcode_generated_time,
		lab_booking_tat.hc_phlebo_assignment_time,
		lab_booking_tat.hc_pickup_time,
		lab_booking_tat.hc_reached_on,
		lab_booking_tat.collected_time,
		lab_booking_tat.hc_lab_drop_on,
		lab_booking_tat.lab_acceptance_time,
		lab_booking_tat.report_generation_time,
		lab_booking_tat.doctor_approval_time,
		lab_booking_tat.report_delivery_time,
		CONCAT_WS( " ", users.first_name, users.last_name ) AS collector_name,
		COALESCE(lab_test_tat_details.test_duration,'00:00:00') AS expected_tat ,
		TIMEDIFF(booking_time,report_delivery_time) as actual_tat
	FROM
		lab_booking_details
		JOIN lab_test_master ON lab_booking_details.lab_analyte_id = lab_test_master.id
		JOIN lab_booking_tat ON lab_booking_tat.booking_id = lab_booking_details.lab_booking 
		AND lab_booking_tat.analyte_id = lab_booking_details.lab_analyte_id
		JOIN lab_department ON lab_test_master.department_id = lab_department.id
		JOIN lab_booking ON lab_booking.id = lab_booking_details.lab_booking
		JOIN patients ON lab_booking.patient_id = patients.id
		LEFT JOIN users ON users.id = lab_booking_tat.collected_by
		LEFT JOIN lab_test_tat_details ON lab_test_tat_details.analyte_id = lab_test_master.id 
	WHERE
		(lab_booking.booking_date >= tat_fromdt OR tat_fromdt IS NULL)
		AND (lab_booking.booking_date <= tat_todt OR tat_todt IS NULL)
		AND lab_booking_details.`status` != '-1';
END