DROP  PROCEDURE if EXISTS sp_lab_order_list;
CREATE PROCEDURE `sp_lab_order_list`(`var_status` VARCHAR(5))
BEGIN

SELECT
	`prescriptions`.`id`,
	`prescriptions`.`generated_on`,
	GROUP_CONCAT( lab_analyte_master.analyte_name SEPARATOR "<br> " ) AS test_name,
	`prescriptions`.`visit_id`,
	`visit_details`.`visit_no`,
	`visit_details`.`doctor_id`,
	`case_details`.`id` AS `case_id`,
	`case_details`.`case_no`,
	`doctors`.`prnt_name` AS `doctor_name`,
	`patients`.`patient_unique_id`,
	IFNULL( patients.patient_name, p1.patient_name ) patient_name,
	`ipd_admission`.`is_pharmacy_settled`,
	`emergency_priority_master`.`priority_name`,
	`emergency_priority_master`.`color`,
	IFNULL( emergency_priority_master.id, 100 ) AS priority_id 
FROM
	`prescriptions`
	INNER JOIN `prescriptionwise_medicine_test` ON `prescriptionwise_medicine_test`.`prescriptions_id` = `prescriptions`.`id` 
	AND ( `prescriptionwise_medicine_test`.`order_status` = var_status OR `prescriptionwise_medicine_test`.`order_status` = "H" ) 
	AND `prescriptionwise_medicine_test`.`source` = "T"
	LEFT JOIN `visit_details` ON `visit_details`.`id` = `prescriptions`.`visit_id`
	LEFT JOIN `case_details` ON `case_details`.`id` = `visit_details`.`case_id`
	LEFT JOIN `doctors` ON `doctors`.`id` = `visit_details`.`doctor_id`
	LEFT JOIN `patients` ON `patients`.`id` = `case_details`.`patient_id`
	LEFT JOIN `ipd_admission` ON `ipd_admission`.`id` = `case_details`.`opd_ipd_ot_id` 
	AND `case_details`.`opd_ipd_ot_flag` = "I"
	LEFT JOIN `lab_analyte_master` ON `lab_analyte_master`.`id` = `prescriptionwise_medicine_test`.`source_id`
	LEFT JOIN `cart_header` ON `cart_header`.`id` = `prescriptions`.`patient_order_cart_id`
	LEFT JOIN `patients` `p1` ON `cart_header`.`patient_id` = `p1`.`id`
	LEFT JOIN `emergency_details` ON `emergency_details`.`admission_id` = `case_details`.`opd_ipd_ot_id` 
	AND `case_details`.`opd_ipd_ot_flag` = "E"
	LEFT JOIN `emergency_priority_master` ON `emergency_priority_master`.`id` = `emergency_details`.`priority_id` 
GROUP BY
	`prescriptions`.`id` 
ORDER BY
	`priority_id` ASC, prescriptions.id DESC;
	
	END;