drop  PROCEDURE if EXISTS sp_pharmacy_order_list;
CREATE PROCEDURE `sp_pharmacy_order_list`(`var_status` VARCHAR(5), `is_cancled` VARCHAR(5))
BEGIN
	DECLARE	var_hold_status VARCHAR(1);
	
	if var_status = 'G' THEN
		set var_hold_status = 'G';
	else
		set var_hold_status = 'H';
	end if;

	SELECT id,generated_on,visit_id,visit_no,doctor_id,case_id,case_no,doctor_name,patient_unique_id,title,first_name,
	last_name,middle_name,is_pharmacy_settled,IFNULL(priority_id,100) as priority_id,priority_name, color
	
	FROM
	(SELECT
	`prescriptions`.`id`,
	`prescriptions`.`generated_on`,
	`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`,
	`patients`.`title`,
	`patients`.`first_name`,
	`patients`.`last_name`,
	`patients`.`middle_name`,
	`ipd_admission`.`is_pharmacy_settled`,
	`emergency_priority_master`.id as priority_id,
	`emergency_priority_master`.priority_name,
	emergency_priority_master.color
	
	FROM
	`prescriptions`
	
	INNER JOIN `prescriptionwise_medicine_test` ON `prescriptionwise_medicine_test`.`prescriptions_id` = `prescriptions`.`id` 
	AND ( case when is_cancled = 'N'	then 
					`prescriptionwise_medicine_test`.`order_status` in ( var_status)
				else 
					`prescriptionwise_medicine_test`.`order_status` in ( "H", "G", "S" ) 
				end
)
	AND `prescriptionwise_medicine_test`.`source` = "M"
	
	INNER JOIN `visit_details` ON `visit_details`.`id` = `prescriptions`.`visit_id`
	INNER JOIN `case_details` ON `case_details`.`id` = `visit_details`.`case_id` AND (`case_details`.`opd_ipd_ot_flag` = "I" OR `case_details`.`opd_ipd_ot_flag` = "E")
	INNER JOIN `doctors` ON `doctors`.`id` = `visit_details`.`doctor_id`
	INNER JOIN `patients` ON `patients`.`id` = `case_details`.`patient_id`
	
	INNER JOIN `ipd_admission` ON `ipd_admission`.`id` = `case_details`.`opd_ipd_ot_id` 
	AND (`case_details`.`opd_ipd_ot_flag` = "I" OR `case_details`.`opd_ipd_ot_flag` = "E")
	AND
	case when is_cancled = 'N'	then (`ipd_admission`.`is_discharged` = "N" 
		AND `ipd_admission`.`is_deceased` = "N")
	else
		 (`ipd_admission`.`is_discharged` = "Y" 
		OR `ipd_admission`.`is_deceased` = "Y")
	END
	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

	UNION 
	
	SELECT
	`prescriptions`.`id`,
	`prescriptions`.`generated_on`,
	`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`,
	`patients`.`title`,
	`patients`.`first_name`,
	`patients`.`last_name`,
	`patients`.`middle_name`,
	'N',
	`emergency_priority_master`.id as priority_id,
	`emergency_priority_master`.priority_name,
	emergency_priority_master.color
	
	FROM
	`prescriptions`
	
	INNER JOIN `prescriptionwise_medicine_test` ON `prescriptionwise_medicine_test`.`prescriptions_id` = `prescriptions`.`id` 
	AND ( case when is_cancled = 'N'	then 
					`prescriptionwise_medicine_test`.`order_status` in ( var_status )
				else 
					`prescriptionwise_medicine_test`.`order_status` in ( "H", "G", "S" ) 
				end) 
	AND `prescriptionwise_medicine_test`.`source` = "M"
	
	INNER JOIN `visit_details` ON `visit_details`.`id` = `prescriptions`.`visit_id`
	INNER JOIN `case_details` ON `case_details`.`id` = `visit_details`.`case_id` AND `case_details`.`opd_ipd_ot_flag` = "O"
	INNER JOIN `doctors` ON `doctors`.`id` = `visit_details`.`doctor_id`
	INNER JOIN `patients` ON `patients`.`id` = `case_details`.`patient_id`
	INNER JOIN `opd_doctor_appointments` ON `opd_doctor_appointments`.`id` = `visit_details`.`opd_id` 
	AND `case_details`.`opd_ipd_ot_flag` = "O" 
	AND
	case when is_cancled = 'N'	then
			 (DATE( opd_doctor_appointments.doctor_appointment_date )>= CURRENT_DATE
			AND DATE( opd_doctor_appointments.doctor_appointment_date )<= DATE_ADD(CURRENT_DATE,INTERVAL 1 DAY))
	else 
		DATE( opd_doctor_appointments.doctor_appointment_date )< CURRENT_DATE
	END
	
	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
	
	
	
	UNION
	
	SELECT
	`prescriptions`.`id`,
	`prescriptions`.`generated_on`,
	`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`,
	`patients`.`title`,
	`patients`.`first_name`,
	`patients`.`last_name`,
	`patients`.`middle_name`,
	`ipd_admission`.`is_pharmacy_settled`,
	`emergency_priority_master`.id as priority_id,
	`emergency_priority_master`.priority_name,
	emergency_priority_master.color
	
	FROM
	`prescriptions`
	
	INNER JOIN `prescriptionwise_medicine_test` ON `prescriptionwise_medicine_test`.`prescriptions_id` = `prescriptions`.`id` 
	AND (case when is_cancled = 'N'	then 
					`prescriptionwise_medicine_test`.`order_status` in ( var_status)
				else 
					`prescriptionwise_medicine_test`.`order_status` in ( "H", "G", "S" ) 
				end ) 
	AND `prescriptionwise_medicine_test`.`source` = "M"
	
	INNER JOIN `visit_details` ON `visit_details`.`id` = `prescriptions`.`visit_id`
	INNER JOIN `case_details` ON `case_details`.`id` = `visit_details`.`case_id` AND `case_details`.`opd_ipd_ot_flag` = "T"
	INNER JOIN `doctors` ON `doctors`.`id` = `visit_details`.`doctor_id`
	INNER JOIN `patients` ON `patients`.`id` = `case_details`.`patient_id`
	INNER JOIN `ot_bookings` ON `ot_bookings`.`id` = `case_details`.`opd_ipd_ot_id` 
	AND `case_details`.`opd_ipd_ot_flag` = "T" 
	INNER JOIN `ipd_admission` ON `ipd_admission`.`id` = `ot_bookings`.`ref_opd_ipd_id` 
	AND `case_details`.`opd_ipd_ot_flag` = "T" 
	AND
	case when is_cancled = 'N'	then
		 (`ipd_admission`.`is_discharged` = "N" 
		AND `ipd_admission`.`is_deceased` = "N")
	else
		(`ipd_admission`.`is_discharged` = "Y" 
		OR `ipd_admission`.`is_deceased` = "Y")
	END
	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
	) results ORDER BY priority_id ASC, generated_on DESC;
	
END;