DROP FUNCTION IF EXISTS fn_prescription_dtls;
CREATE FUNCTION fn_prescription_dtls(a_visit_id int)
RETURNS TEXT
BEGIN
DECLARE	v_return TEXT;

	SELECT JSON_ARRAYAGG(JSON_OBJECT(	
			'item_type', item_type,
			'order_dtls', order_dtls
			))
	INTO	v_return
	FROM
	(
		SELECT	lab_medicine.item_type as item_type, 
						GROUP_CONCAT(CONCAT(lab_medicine.im_name, ' - ', lab_medicine.order_quantity)) as order_dtls
		FROM		prescriptions ps
		LEFT JOIN	(SELECT	
									pm.prescriptions_id  as prescriptions_id,
									CASE WHEN pm.source = 'M' THEN 'Medicine' ELSE 'Test' END as item_type,
									ml.im_name as im_name,
									pm.order_quantity as order_quantity,
									pm.order_status as order_status
									FROM 	prescriptionwise_medicine_test pm, 
												prescriptions pres,
												(
													SELECT		'M' as item_type, id as id, item_name as  im_name
													FROM		items it
													union ALL
													SELECT 		'T' as item_type, id as id, analyte_name as im_name
													FROM		lab_analyte_master la
												) ml
								WHERE		pm.source = ml.item_type AND pm.source_id = ml.id
								AND			pres.id = pm.prescriptions_id
								AND			pres.visit_id = a_visit_id
						) lab_medicine 
				ON lab_medicine.prescriptions_id= ps.id and lab_medicine.order_status IN ('S', 'G')
		WHERE visit_id = a_visit_id 
		and lab_medicine.im_name is not null
		group by item_type
		) medicine_order_dtls;
	return v_return;
END;
