CREATE PROCEDURE `pending_prescription`(IN `facId` VARCHAR(50), IN `in_status` VARCHAR(50) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER SELECT a.request_id, a.patient_id, count(drug) as count, prescribed_by, concat(b.firstname, ' ', b.surname) as name 
      FROM dispensary a JOIN patientrecords b ON (a.patient_id=b.accountNo) 
      WHERE a.status=in_status and a.facilityId=facId
      group by a.request_id, a.patient_id, prescribed_by,name;

      DROP PROCEDURE `get_dispensary_records`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_dispensary_records`(IN `facId` VARCHAR(50)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN
    SELECT concat(a.drug, ' (', a.generic_name,')') as drug,a.cost_price,a.balance as quantity, dispensary_balance as dispensary_quantity ,a.expiry_date,a.created_at,b.supplier_name as supplier 
    FROM drugpurchaserecords a JOIN suppliersinfo b ON a.supplier=b.id WHERE a.facilityId=facId AND dispensary_balance!=0 ORDER BY created_at DESC;
END