DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `appointment`(IN `in_user_id` VARCHAR(30), IN `patient_id` VARCHAR(20), IN `patient_n` VARCHAR(90), IN `appointType` VARCHAR(50), IN `loc` VARCHAR(70), IN `in_text` VARCHAR(100), IN `in_from` DATETIME, IN `in_to` DATETIME, IN `facId` VARCHAR(60), IN `query_type` VARCHAR(40), IN `in_id` VARCHAR(11))
BEGIN
IF query_type = 'insert' THEN
INSERT INTO `appointment`(user_id,`patientId`, `patient_name`, `appointmentType`, `location`, `notes`, `start_at`, `end_at`, `facilityId`) VALUES (in_user_id,patient_id,patient_n,appointType,loc,in_text,in_from,in_to,facId);
ELSEIF query_type = 'select' THEN
SELECT * from appointment WHERE facilityId=facId AND user_id=in_user_id;
ELSEIF query_type = 'select_user' THEN
SELECT * from appointment WHERE facilityId=facId AND patientId=patient_id;
ELSEIF query_type = 'select_one' THEN
SELECT * from appointment WHERE id=in_id AND facilityId=facId;
ELSEIF query_type = 'delete' THEN
DELETE from appointment WHERE id=in_id AND facilityId=facId;
END IF;
END$$
DELIMITER ;

CREATE TABLE `appointment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) DEFAULT NULL,
  `patientId` varchar(20) DEFAULT NULL,
  `patient_name` varchar(70) DEFAULT NULL,
  `appointmentType` varchar(40) DEFAULT NULL,
  `location` varchar(80) DEFAULT NULL,
  `notes` varchar(100) DEFAULT NULL,
  `start_at` datetime NOT NULL DEFAULT current_timestamp(),
  `end_at` datetime NOT NULL DEFAULT current_timestamp(),
  `facilityId` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4

INSERT INTO `number_generator` (`description`, `prefix`, `code_no`) VALUES ('Good Receive Note', 'GRN', '0');

delimiter $$
CREATE PROCEDURE `get_number_generator`(IN `in_query_type` VARCHAR(50), IN `in_prefix` VARCHAR(5)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER SELECT code_no + 1 as code_no WHERE prefix = in_prefix;
$$

-- add grn_no to the store table

DROP PROCEDURE pharm_add_new_drug_purchase;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pharm_add_new_drug_purchase`(IN `in_receive_date` DATE, IN `in_item_name` VARCHAR(50), IN `in_po_no` VARCHAR(20), IN `in_qty_in` INT(11), IN `in_qty_out` INT(11), IN `in_store_type` VARCHAR(20), IN `in_grm_no` INT(11), IN `query_type` VARCHAR(20), IN `in_expiry_date` DATE, IN `in_unit_price` FLOAT(11), IN `in_mark_up` FLOAT(11), IN `in_selling_price` FLOAT(11), IN `in_transfer_from` VARCHAR(50), IN `in_status` VARCHAR(20), IN `in_transfer_to` VARCHAR(50), IN `in_branch_name` VARCHAR(30), IN `in_facilityId` VARCHAR(50), IN `in_trn_no` VARCHAR(50), IN `in_uniqueId` VARCHAR(50), IN `in_item_category` VARCHAR(100), IN `in_item_code` VARCHAR(70), IN `in_version_id` VARCHAR(50), IN `in_req_no` VARCHAR(50), IN `in_truck_no` VARCHAR(50), IN `in_waybill_no` VARCHAR(50), IN `in_other_info` VARCHAR(100), IN `in_cost_price` FLOAT, IN `in_supplier_code` VARCHAR(100), IN `in_supplier_name` VARCHAR(100), IN `in_reorder_level` INT, IN `in_receipt_no` VARCHAR(50), IN `in_user_id` VARCHAR(50), IN `in_generic_name` VARCHAR(100), IN `in_uom` VARCHAR(100), IN `in_barcode` VARCHAR(100))
    NO SQL
BEGIN

declare supplier_balance,new_supplier_balance float;
declare drug_code_data,drug_name_data,balance_data,store_data, facilityId_data varchar(50);
DECLARE supplier_name_data varchar(100);
DECLARE selling_price_data,price_data float;
declare expiry_date_data date;
declare new_drug_balance int;
declare var_amount float;
set var_amount=in_qty_in*in_cost_price ;
if query_type= "received" then

select balance  into supplier_balance from suppliersinfo where supplier_code=in_supplier_code;

set new_supplier_balance=supplier_balance-var_amount;
-- update the supplier info balance
update suppliersinfo set balance=new_supplier_balance where supplier_code=in_supplier_code;

-- insert into account entries
INSERT INTO supplier_entries (supplier_id,dr,cr,reference_no,facilityId,created_at,truckNo,waybillNo,description,version_id,cost_price,quantity)
VALUES (in_supplier_code,var_amount,0,'',in_facilityId,now(),in_truck_No,in_waybill_no,in_item_name,in_version_id,in_cost_price ,in_qty_in);


-- insert into store

INSERT INTO pharm_store_entries (receive_date, drug_name, po_no, qty_in,qty_out,store_type,grn_no, expiry_date,unit_price,mark_up,selling_price,transfer_from,transfer_to, branch_name,facilityId,uniqueId,drug_category,item_code,version_id, truckNo, waybillNo, otherInfo, cost_price, supplier_code, supplier_name, reorder_level, inserted_by,inserted_time,sales_type,barcode) 
VALUES(in_receive_date,in_item_name,in_po_no,in_qty_in, in_qty_out, in_store_type,in_grm_no,in_expiry_date, in_cost_price ,in_mark_up, in_selling_price,in_transfer_from,in_transfer_to, in_branch_name, in_facilityId,in_uniqueId, in_item_category,in_item_code,in_version_id, in_truck_no,in_waybill_no,in_other_info, in_cost_price, in_supplier_code, in_supplier_name,in_reorder_level,in_user_id,now(),"Purchase Order",in_barcode);

SELECT item_code,drug_name,price,balance, facilityId, expiry_date,selling_price,store, supplier_name into 
drug_code_data,drug_name_data,price_data,balance_data, facilityId_data,expiry_date_data ,selling_price_data,store_data, supplier_name_data
FROM `pharm_store` where drug_name=in_item_name and store=in_branch_name and expiry_date=in_expiry_date and barcode=in_barcode and
supplier_name= in_supplier_name and price = in_cost_price ; 

update pharm_store set selling_price=in_selling_price WHERE barcode=in_barcode and drug_name=in_item_name  and facilityId= in_facilityId  and store=in_branch_name;

if  drug_name_data=in_item_name and price_data=in_cost_price and store_data=in_branch_name and facilityId_data= in_facilityId and expiry_date_data=in_expiry_date and supplier_name_data=in_supplier_name THEN

set new_drug_balance=ifnull(balance_data,0)+in_qty_in;


update pharm_store set balance=new_drug_balance WHERE drug_name=in_item_name and selling_price=in_selling_price 
and  facilityId= in_facilityId and expiry_date=in_expiry_date and store=in_branch_name and supplier_name= in_supplier_name and price = in_cost_price;

ELSE

insert into pharm_store(item_code,drug_name,price,balance, facilityId, expiry_date,supplier_name,supplier_code,selling_price,store,generic_name,insert_date,reoder_level,uom,drug_category, barcode, grn_no)
values (in_item_code,in_item_name,in_cost_price,in_qty_in, in_facilityId,in_expiry_date,in_supplier_name,in_supplier_code,in_selling_price,in_branch_name,in_generic_name,now(),
        in_reorder_level,in_uom,in_item_category,in_barcode, in_grm_no);

end if;


-- call update_number_generator("grn",in_grm_no);

ELSEIF query_type="transfer" then
INSERT INTO pharm_store_entries (receive_date, item_name, po_no, qty_in,qty_out,store_type,grm_no,
        expiring_date,unit_price,mark_up,selling_price,transfer_from,transfer_to, 
        branch_name,facilityId,trn_number,uniqueId,item_category,item_code,version_id,
        truckNo, waybillNo, otherInfo, supplier_code, supplier_name,reorder_level,inserted_by,sales_type,barcode) 
VALUES(in_receive_date,in_item_name,in_po_no,in_qty_in, in_qty_out,
        in_store_type,in_grm_no, in_expiry_date,in_cost_price,
        in_mark_up,in_selling_price,in_transfer_from,in_transfer_to,
        in_branch_name,in_facilityId,in_trn_no,uniqueId,in_item_category,
        in_item_code,in_version_id,in_truck_no,in_waybill_no,in_other_info, 
        in_supplier_code, in_supplier_name,in_reorder_level,in_user_id,'Purchase Order',in_barcode);

call pharm_add_sale_department(in_trn_no, in_item_name, in_qty_in,in_expiry_date, in_selling_price,in_transfer_from, in_receive_date,in_item_category, in_item_code,in_transfer_to, in_version_id,in_facilityId, 0,in_status,in_req_no,
in_truck_no,in_waybill_no, in_other_info,in_supplier_code, in_supplier_name, in_receipt_no, in_user_id, in_barcode);
end if;

END$$
DELIMITER ;
ALTER TABLE `dispensary` CHANGE `decision` `decision` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

DROP PROCEDURE `pending_prescription`; 
delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pending_prescription`(IN `query_type` VARCHAR(50), IN `in_status` VARCHAR(50), IN `facId` VARCHAR(50), IN `in_request_id` VARCHAR(50), IN `in_from` DATE, IN `in_to` DATE) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN IF query_type='patient-drugs' THEN SELECT drug,drug as drug_name, dosage, frequency, duration, additionalInfo, route, period, request_id FROM dispensary WHERE request_id=in_request_id AND status=in_status; ELSEIF query_type='general-data' THEN 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.id) WHERE a.status=in_status and a.facilityId=facId AND date(a.created_at) BETWEEN in_from AND in_to group by a.request_id, a.patient_id, prescribed_by,name LIMIT 50; END IF; 
END;
END$$
DELIMITER ;

DROP PROCEDURE `pharm_update_store`; 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pharm_update_store`(IN `in_selling_price` FLOAT(11), IN `in_expiry_date` DATE, IN `in_drug_name` VARCHAR(100), IN `in_item_code` VARCHAR(100), IN `in_store` VARCHAR(100), IN `in_facilityId` VARCHAR(100), IN `in_balance` INT)
update pharm_store set selling_price=in_selling_price,balance=in_balance,drug_name=in_drug_name  WHERE expiry_date=in_expiry_date and item_code=in_item_code and store=in_store and facilityId=in_facilityId$$
DELIMITER ;
DROP PROCEDURE `pharm_get_druglist`; 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pharm_get_druglist`(IN `facId` VARCHAR(70), IN `query_type` VARCHAR(20))
    NO SQL
BEGIN
IF query_type = 'stock' THEN
SELECT balance, price,drug_name,expiry_date,selling_price,supplier_name,insert_date,barcode,grn_no from pharm_store WHERE balance>0 AND facilityId = facId ORDER BY `grn_no` DESC;
ELSEIF query_type = 'out_of_stock' THEN 
SELECT balance, price,drug_name,expiry_date,selling_price,supplier_name,insert_date,barcode,grn_no from pharm_store WHERE balance <= 0 AND facilityId = facId ORDER BY `grn_no` DESC;
END IF;
END$$
DELIMITER ;

-- new addding
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `charges`(IN `p_id` VARCHAR(20), IN `u_id` VARCHAR(20), IN `amt` FLOAT, IN `in_cr` FLOAT, IN `decr` VARCHAR(100), IN `in_status` VARCHAR(30), IN `facId` VARCHAR(60), IN `date_from` DATE, IN `date_to` DATE, IN `query_type` VARCHAR(20))
BEGIN
 declare old_balance int;
 declare main_balance int;
 declare paid_balance int;
 declare new_amt int;
 select balance into old_balance  from hospitals where  id=facId;
 set main_balance = old_balance + amt;
 set paid_balance = old_balance - in_cr;

IF query_type = 'insert'  THEN
 IF in_status ='Follow-up' THEN
 SET new_amt = amt DIV 2;
 ELSE
 SET new_amt = amt;
 END IF;
INSERT INTO `charges_fees`(`patient_id`, `user_id`,`dr`, `cr`, `description`, `status`,facilityId) VALUES (p_id,u_id,new_amt,in_cr,decr,in_status,facId);
IF amt > 0 then	
UPDATE `hospitals` SET `balance`=main_balance WHERE id = facId;
ELSE
UPDATE `hospitals` SET `balance`=paid_balance WHERE id = facId;
END IF;

ELSEIF query_type = 'select' THEN
SELECT * FROM charges_fees where facilityId=facId AND date(created_at) BETWEEN date(date_from) AND date(date_to) ORDER BY id DESC;
ELSEIF query_type = 'select_charges' THEN
SELECT * FROM charges_fees where description=decr AND facilityId=facId AND date(created_at) BETWEEN date(date_from) AND date(date_to) ORDER BY id DESC;
ELSEIF query_type = 'balance' THEN
SELECT balance from hospitals WHERE id = facId;
ELSEIF query_type = 'last' THEN
SELECT created_at FROM `charges_fees` WHERE patient_id='7401-1' AND dr != 0 AND dr >150 ORDER BY created_at DESC LIMIT 1;
ELSEIF query_type = 'select_time' THEN 
SELECT time_laps FROM charges_fees_temp WHERE revenueSource = 'Consultation';
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `charges_temp`(IN `head` INT, IN `query_type` VARCHAR(20))
BEGIN
IF query_type = 'select' THEN
SELECT DISTINCT revenueSource FROM charges_fees_temp;
ELSE
SELECT amount,revenueSource FROM `charges_fees_temp` WHERE accountHead=head;
END IF;
END$$
DELIMITER ;

CREATE TABLE `charges_fees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patient_id` varchar(20) NOT NULL,
  `user_id` varchar(20) NOT NULL,
  `dr` float NOT NULL,
  `cr` float NOT NULL,
  `description` varchar(100) NOT NULL,
  `status` varchar(30) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `facilityId` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=utf8mb4
LTER TABLE `hospitals` ADD `balance` FLOAT NOT NULL DEFAULT '0' AFTER `hasStore`;
ALTER TABLE `charges_fees_temp` ADD `time_laps` INT NULL DEFAULT NULL AFTER `accountHead`;
UPDATE `charges_fees_temp` SET `time_laps` = '30' WHERE `charges_fees_temp`.`id` = 3;

------------

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pharm_drug_transfer`(IN `in_version_id` VARCHAR(100), IN `in_qty_in` INT(6), IN `in_expiry_date` DATE, IN `in_selling_price` FLOAT(10), IN `in_location_from` VARCHAR(100), IN `in_location_to` VARCHAR(100), IN `in_facilityId` VARCHAR(100), IN `in_item_name` VARCHAR(100), IN `in_receive_date` DATE, IN `in_unit_price` FLOAT(10), IN `in_mark_up` FLOAT(10), IN `supplier_name` VARCHAR(50), IN `supplier_code` VARCHAR(50)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
DECLARE trn int;
declare item_code_data,description_data,price_data,balance_data, facilityId_data varchar(50);
declare expiry_date_data date;
declare new_item_balance,transfers_balance,new_transfers_balance int;

declare item_code_data1,description_data1,price_data1,balance_data1 varchar(50);
declare expiry_date_data1 date;
declare new_item_balance1 int;

-- SELECT max(ifNull(code_no,0)+1)  INTO trn from number_generator WHERE prefix='trn';

SELECT item_code,drug_name,price,balance, expiry_date
into item_code_data1,description_data1,price_data1,balance_data1,expiry_date_data1 
FROM `pharm_store` where drug_name=in_item_name and price=in_unit_price and expiry_date=in_expiry_date and store=in_location_from AND facilityId=in_facilityId; 

set new_item_balance1=balance_data1-in_qty_in;

update `pharm_store`set balance=new_item_balance1  where drug_name=description_data1 and price=price_data1 and expiry_date=expiry_date_data1  and store=in_location_from; 

SELECT balance INTO transfers_balance from pharm_store where drug_name=description_data1 and price=price_data1 and expiry_date=expiry_date_data1  and store = in_location_to;

IF transfers_balance is null then 
INSERT INTO `pharm_store`(`balance`, `drug_name`,`item_code`, `price`, `facilityId`, `expiry_date`, `store`, `selling_price`, `supplier_name`, `supplier_code`, `insert_date`) 
VALUES (in_qty_in,in_item_name,item_code_data1,in_unit_price,in_facilityId,in_expiry_date,in_location_to,in_selling_price,supplier_name,supplier_code,now());

INSERT INTO pharm_store_entries(receive_date,drug_name,item_code,`expiry_date`,qty_out,qty_in,`transfer_to`,unit_price,selling_price,mark_up,branch_name,facilityId,version_id,supplier_code,supplier_name,sales_type)
VALUES(now(),in_item_name,item_code_data1,in_expiry_date,in_qty_in, 0,in_location_to,in_unit_price,in_selling_price,in_mark_up, in_location_to ,in_facilityId,in_version_id,supplier_code,supplier_name,concat("Transfer to ", in_location_to));

INSERT INTO pharm_store_entries(receive_date,drug_name,item_code,expiry_date,qty_out,qty_in,`transfer_to`,unit_price,selling_price,mark_up,branch_name,facilityId,version_id,supplier_code,supplier_name,sales_type)
VALUES(now(),in_item_name,item_code_data1,in_expiry_date,0 ,in_qty_in,in_location_to,in_unit_price,in_selling_price,in_mark_up,in_location_to,in_facilityId, in_version_id,supplier_code,supplier_name,concat("Transfer to ", in_location_to));


ELSE
SET new_transfers_balance = transfers_balance + in_qty_in;
UPDATE pharm_store SET balance = new_transfers_balance  where drug_name=description_data1 and price=price_data1 and expiry_date=expiry_date_data1  and store = in_location_to;


INSERT INTO pharm_store_entries(receive_date,drug_name,`item_code`,expiry_date,qty_out,qty_in,`transfer_to`,unit_price,selling_price,mark_up,facilityId,supplier_code,supplier_name,version_id,sales_type,branch_name)
VALUES(now(),in_item_name,item_code_data1,in_expiry_date,in_qty_in, 0,in_location_to,in_unit_price,in_selling_price,in_mark_up,in_facilityId,supplier_code,supplier_name,in_version_id ,concat("Transfer to ", in_location_to), in_location_to);

INSERT INTO pharm_store_entries(receive_date,drug_name,`item_code`,expiry_date,qty_out,qty_in,`transfer_to`,unit_price,selling_price,mark_up,branch_name,facilityId,version_id,supplier_code,supplier_name,sales_type)
VALUES(now(),in_item_name,item_code_data1,in_expiry_date,0 ,in_qty_in,in_location_to,in_unit_price,in_selling_price,in_mark_up,in_location_to,in_facilityId,in_location_to,supplier_code,supplier_name,concat("Transfer to ", in_location_to));
	
end IF;

END$$
DELIMITER ;


DROP PROCEDURE `pharm_get_sales_drugs`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pharm_get_sales_drugs`(IN `in_facilityId` VARCHAR(50), IN `query_type` VARCHAR(50), IN `in_from` INT, IN `in_to` INT) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN
if query_type='Show All' THEN 
SELECT ifnull(drug_category ,"") as drug_category,ifnull(generic_name ,"") as generic_name,ifnull(uom ,"") as uom,balance,drug_name,item_code,facilityId,expiry_date,store,selling_price FROM `pharm_store` WHERE facilityId=in_facilityId and balance>0 ORDER BY `drug_name` ASC;
 ELSE 
SELECT ifnull(drug_category ,"") as drug_category,ifnull(generic_name ,"") as generic_name,ifnull(uom ,"") as uom,balance,drug_name,item_code,facilityId,expiry_date,store,selling_price FROM `pharm_store` WHERE facilityId=in_facilityId and store=query_type and balance>0 ORDER BY `drug_name` ASC LIMIT in_from,in_to;
 end if;
 end
 
 -- pharm_reports_dashboard, pending_prescription
 DROP PROCEDURE get_list_of_drugs;
 DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_list_of_drugs`(IN `in_name` VARCHAR(80), IN `in_generic_name` VARCHAR(80), IN `in_id` INT, IN `query_type` VARCHAR(20))
BEGIN
IF query_type='insert' THEN
INSERT INTO `druglist`(`name`, `generic_name`) VALUES (in_name,in_generic_name);
ELSEIF query_type='update' THEN
UPDATE druglist SET name=in_name, generic_name=in_generic_name WHERE id=in_id;
ELSEIF query_type='delete' THEN
DELETE FROM `druglist` WHERE id=in_id;
ELSE
    SELECT DISTINCT id,name as drug, generic_name FROM druglist ORDER BY created_at DESC;
    END IF;
END$$
DELIMITER ;

-- lastest update
-- pharm_get_purchase_drugs
-- pharm_reports_dashboard

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pharm_get_purchase_drugs`(IN `in_facilityId` VARCHAR(50), IN `in_from` INT(15), IN `in_to` INT(15), IN `query_type` VARCHAR(60), IN `in_store` VARCHAR(100))
    NO SQL
begin
if query_type="by_store" THEN
SELECT * FROM `pharm_store` WHERE store = in_store AND facilityId=in_facilityId and balance>0 LIMIT in_from,in_to;
ELSE 
SELECT * FROM `pharm_store` WHERE facilityId=in_facilityId AND balance > 0;
end if;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pharm_reports_dashboard`(IN `in_date_from` DATE, IN `in_date_to` DATE, IN `in_facilityid` VARCHAR(100), IN `query_type` VARCHAR(50))
    NO SQL
begin

if query_type ='Purchase summary' then

SELECT sum(qty_in*unit_price) as total FROM `pharm_store_entries` where sales_type ='Purchase Order' AND date(receive_date) BETWEEN in_date_from and in_date_to and facilityId=in_facilityid;

elseif query_type ='Purchase category summary' then

SELECT receive_date,drug_name as description,qty_in as qty,selling_price, unit_price,unit_price*qty_in as amount,inserted_by, branch_name,supplier_name FROM `pharm_store_entries` where sales_type ='Purchase Order' AND date(receive_date) BETWEEN in_date_from and in_date_to and facilityId=in_facilityid;

elseif query_type ='Sales summary' then

SELECT sum(qty_out*selling_price) as total FROM `pharm_store_entries` where date(receive_date) BETWEEN in_date_from and in_date_to and facilityId=in_facilityid AND qty_out > 0 AND sales_type="sales" ;

elseif query_type ='Sales category summary' then
SELECT  receive_date,drug_name as description,qty_out as qty,selling_price, selling_price*qty_out as amount,inserted_by,sales_type, branch_name,otherInfo FROM `pharm_store_entries` where date(receive_date) BETWEEN in_date_from and in_date_to and facilityid=in_facilityid AND qty_out > 0 AND sales_type="sales";


elseif query_type ='Expenses summary' then

SELECT sum(credit) as total FROM `transactions` where acct ='EXPENSES' and facilityId=in_facilityid and date(createdAt) between in_date_from and in_date_to;
elseif query_type ='Expenses summary' then

SELECT sum(credit) as total FROM `transactions` where acct ='EXPENSES' and facilityId=in_facilityid and date(createdAt) between in_date_from and in_date_to;


elseif query_type ='Discount summary' then
SELECT sum(credit) as total FROM `transactions` where acct ='60000' and facilityId=in_facilityid and date(createdAt) between in_date_from and in_date_to;

elseif query_type ='Discount category summary' then
SELECT description,credit,enteredBy,createdAt,customer_name, branch_name FROM `transactions` where acct ='60000' and facilityId=in_facilityid and date(createdAt) between in_date_from and in_date_to;

elseif query_type ='Debt summary' then

SELECT sum(balance) as total FROM patientfileno where facilityId=in_facilityid AND balance < 0  AND accountNo != in_facilityid;
elseif query_type ='Debt summary' then


select date(createdAt) as receive_date, accname as description,contactPhone,balance as amount from patientfileno where facilityId=in_facilityid AND balance < 0 AND accountNo != '';

elseif query_type ='Debt category summary' then

select date(createdAt) as receive_date, accname as description,contactPhone,balance as amount from patientfileno where facilityId=in_facilityid AND balance < 0 AND accountNo != '';

elseif query_type ='reprint' then
select * FROM transactions where facilityId=in_facilityid AND date(createdAt) between in_date_from and in_date_to GROUP BY receiptDateSN;


end if;

end$$
DELIMITER ;

-- pharm_get_drug_view

ALTER TABLE `nursing_note` ADD `facilityId` VARCHAR(60) NOT NULL AFTER `created_by`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `patient_nursing_notes`(IN `in_report` VARCHAR(200), IN `pat_id` VARCHAR(20), IN `create_at` DATETIME, IN `create_by` VARCHAR(50), IN `facId` VARCHAR(60), IN `query_type` VARCHAR(30))
BEGIN
IF query_type='insert' THEN
INSERT INTO `nursing_note`( `report`, `patient_id`, `created_at`, `created_by`,facilityId) 
VALUES (in_report,pat_id,create_at,create_by,facId);
ELSEIF query_type='select' THEN
SELECT * FROM nursing_note WHERE patient_id=pat_id AND facilityId=facId;

END IF;
END$$
DELIMITER ;

------ 

DROP PROCEDURE `pharm_drug_transfer`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pharm_drug_transfer`(IN `in_version_id` VARCHAR(100), IN `in_qty_in` INT(6), IN `in_expiry_date` DATE, IN `in_selling_price` FLOAT(10), IN `in_location_from` VARCHAR(100), IN `in_location_to` VARCHAR(100), IN `in_facilityId` VARCHAR(100), IN `in_item_name` VARCHAR(100), IN `in_receive_date` DATE, IN `in_unit_price` FLOAT(10), IN `in_mark_up` FLOAT(10), IN `supplier_name` VARCHAR(50), IN `supplier_code` VARCHAR(50))
BEGIN
DECLARE trn int;
declare item_code_data,description_data,price_data,balance_data, facilityId_data varchar(50);
declare expiry_date_data date;
declare new_item_balance,transfers_balance,new_transfers_balance int;

declare item_code_data1,description_data1,price_data1,balance_data1 varchar(50);
declare expiry_date_data1 date;
declare new_item_balance1 int;

-- SELECT max(ifNull(code_no,0)+1)  INTO trn from number_generator WHERE prefix='trn';

SELECT item_code,drug_name,price,balance, expiry_date
into item_code_data1,description_data1,price_data1,balance_data1,expiry_date_data1 
FROM `pharm_store` where drug_name=in_item_name and price=in_unit_price and expiry_date=in_expiry_date and store=in_location_from AND facilityId=in_facilityId; 

set new_item_balance1=balance_data1-in_qty_in;

update `pharm_store`set balance=new_item_balance1  where drug_name=description_data1 and price=price_data1 and expiry_date=expiry_date_data1  and store=in_location_from; 

SELECT balance INTO transfers_balance from pharm_store where drug_name=description_data1 and price=price_data1 and expiry_date=expiry_date_data1  and store = in_location_to;

IF transfers_balance is null then 
INSERT INTO `pharm_store`(`balance`, `drug_name`,`item_code`, `price`, `facilityId`, `expiry_date`, `store`, `selling_price`, `supplier_name`, `supplier_code`, `insert_date`) 
VALUES (in_qty_in,in_item_name,item_code_data1,in_unit_price,in_facilityId,in_expiry_date,in_location_to,in_selling_price,supplier_name,supplier_code,now());

INSERT INTO pharm_store_entries(receive_date,drug_name,item_code,`expiry_date`,qty_out,qty_in,transfer_from,`transfer_to`,unit_price,selling_price,mark_up,branch_name,facilityId,version_id,supplier_code,supplier_name,sales_type)
VALUES(now(),in_item_name,item_code_data1,in_expiry_date,in_qty_in, 0,in_location_from,in_location_to,in_unit_price,in_selling_price,in_mark_up, in_location_to ,in_facilityId,in_version_id,supplier_code,supplier_name,concat("Transfer to ", in_location_to));

INSERT INTO pharm_store_entries(receive_date,drug_name,item_code,expiry_date,qty_out,qty_in,transfer_from,`transfer_to`,unit_price,selling_price,mark_up,branch_name,facilityId,version_id,supplier_code,supplier_name,sales_type)
VALUES(now(),in_item_name,item_code_data1,in_expiry_date,0 ,in_qty_in,in_location_from,in_location_to,in_unit_price,in_selling_price,in_mark_up,in_location_to,in_facilityId, in_version_id,supplier_code,supplier_name,concat("Transfer to ", in_location_to));


ELSE
SET new_transfers_balance = transfers_balance + in_qty_in;
UPDATE pharm_store SET balance = new_transfers_balance  where drug_name=description_data1 and price=price_data1 and expiry_date=expiry_date_data1  and store = in_location_to;


INSERT INTO pharm_store_entries(receive_date,drug_name,`item_code`,expiry_date,qty_out,qty_in,transfer_from,`transfer_to`,unit_price,selling_price,mark_up,facilityId,supplier_code,supplier_name,version_id,sales_type,branch_name)
VALUES(now(),in_item_name,item_code_data1,in_expiry_date,in_qty_in, 0,in_location_from,in_location_to,in_unit_price,in_selling_price,in_mark_up,in_facilityId,supplier_code,supplier_name,in_version_id ,concat("Transfer to ", in_location_to), in_location_to);

INSERT INTO pharm_store_entries(receive_date,drug_name,`item_code`,expiry_date,qty_out,qty_in,transfer_from,`transfer_to`,unit_price,selling_price,mark_up,branch_name,facilityId,version_id,supplier_code,supplier_name,sales_type)
VALUES(now(),in_item_name,item_code_data1,in_expiry_date,0 ,in_qty_in,in_location_from,in_location_to,in_unit_price,in_selling_price,in_mark_up,in_location_to,in_facilityId,in_location_to,supplier_code,supplier_name,concat("Transfer to ", in_location_to));
	
end IF;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `good_transfer_report`(IN `date_from` VARCHAR(30), IN `date_to` VARCHAR(30), IN `facId` VARCHAR(60))
SELECT * FROM `pharm_store_entries` WHERE transfer_to !='' AND transfer_to !='pos' AND transfer_from !='Purchase order' AND qty_in > 0 AND date(inserted_time) BETWEEN date_from AND date_to and facilityId=facId$$
DELIMITER ;



-------------
ALTER TABLE `charges_fees` ADD `patientType` VARCHAR(30) NULL DEFAULT NULL AFTER `status`;
DROP PROCEDURE charges;
DELIMITER $$
CREATE PROCEDURE `charges`(IN `p_id` VARCHAR(20), IN `u_id` VARCHAR(20), IN `amt` FLOAT, IN `in_cr` FLOAT, IN `decr` VARCHAR(100), IN `in_status` VARCHAR(30), IN `facId` VARCHAR(60), IN `date_from` DATE, IN `date_to` DATE, IN `query_type` VARCHAR(20), IN `patient_type` VARCHAR(30))
BEGIN
 declare old_balance int;
 declare main_balance int;
 declare paid_balance int;
 declare new_amt int;
 select balance into old_balance  from hospitals where  id=facId;
 set main_balance = old_balance + amt;
 set paid_balance = old_balance - in_cr;

IF query_type = 'insert'  THEN
 IF in_status ='Follow-up' THEN
 SET new_amt = amt DIV 2;
 ELSE
 SET new_amt = amt;
 END IF;
INSERT INTO `charges_fees`(`patient_id`, `user_id`,`dr`, `cr`, `description`, `status`,facilityId,patientType) VALUES (p_id,u_id,new_amt,in_cr,decr,in_status,facId,patient_type);
IF amt > 0 then	
UPDATE `hospitals` SET `balance`=main_balance WHERE id = facId;
ELSE
UPDATE `hospitals` SET `balance`=paid_balance WHERE id = facId;
END IF;

ELSEIF query_type = 'select' THEN
SELECT * FROM charges_fees where facilityId=facId AND date(created_at) BETWEEN date(date_from) AND date(date_to) ORDER BY id DESC;
ELSEIF query_type = 'select_charges' THEN
SELECT * FROM charges_fees where description=decr AND facilityId=facId AND date(created_at) BETWEEN date(date_from) AND date(date_to) ORDER BY id DESC;
ELSEIF query_type = 'balance' THEN
SELECT balance from hospitals WHERE id = facId;
ELSEIF query_type = 'last' THEN
SELECT created_at FROM `charges_fees` WHERE patient_id='7401-1' AND dr != 0 AND dr >150 ORDER BY created_at DESC LIMIT 1;
ELSEIF query_type = 'select_time' THEN 
SELECT time_laps FROM charges_fees_temp WHERE revenueSource = 'Consultation';
END IF;
END$$
DELIMITER ;

--get_all_patient_trans

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all_patient_trans`(IN `in_client_id` VARCHAR(30), IN `date_from` VARCHAR(20), IN `date_to` VARCHAR(20), IN `facId` VARCHAR(60), IN `query_type` VARCHAR(30))
BEGIN
IF query_type = 'drug' THEN
SELECT * FROM `account_entries` WHERE client_id = in_client_id AND facilityId=facId AND date(createdAt) BETWEEN date_from AND date_to AND quantity > 0;
ELSEIF query_type='test' THEN
SELECT * FROM `account_entries` WHERE client_id = in_client_id AND facilityId=facId AND date(createdAt) BETWEEN date_from AND date_to AND quantity = 0 AND dr = 0;

END if;
END$$
DELIMITER ;