ALTER TABLE `supplier_entries` CHANGE `entry_id` `entry_id` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`entry_id`);


drop PROCEDURE if EXISTS pharm_supplier_payment;
DELIMITER $$
CREATE  PROCEDURE `pharm_supplier_payment`(IN `in_facId` VARCHAR(50), IN `userId` VARCHAR(50), IN `in_acct` VARCHAR(50), IN `Amount_paid` INT, IN `in_receiptDateSN` VARCHAR(50), IN `in_receiptSN` VARCHAR(50), IN `in_mode_of_payment` VARCHAR(50), IN `sourceAcct` VARCHAR(50), IN `in_description` VARCHAR(100), IN `in_date` DATETIME, IN `in_payables_head` VARCHAR(50))
BEGIN

declare supplier_balance double;
    declare main_balance double;
    DECLARE balance_paid DOUBLE;
    
    select balance into supplier_balance from suppliersinfo where supplier_code=in_acct AND facilityId=in_facId;

    set main_balance = ifnull(supplier_balance,0)+Amount_paid;
    
    update suppliersinfo set balance= main_balance  where supplier_code=in_acct AND facilityId=in_facId;
    
    
     insert into supplier_entries (supplier_id,dr,cr,reference_no,facilityId,description)
           values (in_acct,0,Amount_paid,in_receiptDateSN,in_facId,in_description);
           
      
     -- insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,facilityId,createdAt,version_id)
        --   values (in_description,sourceAcct,0,Amount_paid,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,in_facId,in_date,in_version_id);
          
           
          -- insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,facilityId,createdAt,version_id)
         --  values (in_description,in_payables_head,0,abs(Amount_paid) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,in_facId,in_date, concat(in_version_id,'-1'));
END$$
DELIMITER ;

drop PROCEDURE if EXISTS pharm_get_sales_drugs;
DELIMITER $$
CREATE  PROCEDURE `pharm_get_sales_drugs`(IN `in_facilityId` VARCHAR(50), IN `query_type` VARCHAR(50), IN `in_from` INT, IN `in_to` INT)
    NO SQL
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 (DATE(expiry_date)> now() OR expiry_date = '1111-11-11') 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 (DATE(expiry_date)> now() OR expiry_date = '1111-11-11') and store=query_type and balance>0 ORDER BY `drug_name` ASC LIMIT in_from,in_to;
 end if;
 end$$
DELIMITER ;



drop PROCEDURE if EXISTS drug_verification;
DELIMITER $$
CREATE  PROCEDURE `drug_verification`(IN `in_drug_name` VARCHAR(100), IN `in_item_code` VARCHAR(100))
SELECT * FROM `pharm_store` WHERE drug_name=in_drug_name and balance>0 and  (DATE(expiry_date)> now() OR expiry_date = '1111-11-11')  ORDER BY expiry_date DESC LIMIT 1$$
DELIMITER ;

drop PROCEDURE if EXISTS new_prescription;
DELIMITER $$
CREATE  PROCEDURE `new_prescription`(IN `in_drug` VARCHAR(100), IN `in_dosage` VARCHAR(50), IN `in_period` VARCHAR(20), IN `in_duration` VARCHAR(20), IN `in_frequency` VARCHAR(20), IN `in_patient_id` VARCHAR(50), IN `in_prescribed_by` VARCHAR(50), IN `in_facilityId` VARCHAR(50), IN `in_status` VARCHAR(50), IN `in_request_id` VARCHAR(50), IN `in_route` VARCHAR(20), IN `in_additionalInfo` VARCHAR(200), IN `in_decision` VARCHAR(20), IN `in_startTime` DATETIME, IN `in_times_per_day` INT, IN `in_id` VARCHAR(50), IN `in_end_date` DATETIME, IN `in_no_of_days` INT, IN `in_drug_count` INT, IN `in_no_times` INT, IN `in_drug_id` VARCHAR(100))
    NO SQL
insert into dispensary(drug_id,drug,dosage,period,duration, frequency, patient_id, prescribed_by, facilityId, status, request_id, route, additionalInfo, decision, startTime, times_per_day, id, end_date, no_of_days, drugCount, no_times) VALUES (in_drug_id,in_drug,in_dosage,in_period,in_duration, in_frequency, in_patient_id, in_prescribed_by, in_facilityId, in_status, in_request_id, in_route, in_additionalInfo, in_decision, in_startTime, in_times_per_day, in_id, in_end_date, in_no_of_days, in_drug_count, in_no_times)$$
DELIMITER ;

drop PROCEDURE if EXISTS pending_prescription;
DELIMITER $$
CREATE  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)
    NO SQL
BEGIN IF query_type='patient-drugs' THEN SELECT drug_id,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$$
DELIMITER ;

drop PROCEDURE if EXISTS pharm_get_drug_sale_search;
DELIMITER $$
CREATE  PROCEDURE `pharm_get_drug_sale_search`(IN `in_drug_name` VARCHAR(50), IN `in_facilityId` VARCHAR(100), IN `in_from` INT, IN `in_to` INT, IN `in_store` VARCHAR(100))
SELECT * FROM `pharm_store` WHERE  balance>0 and (drug_name like in_drug_name or drug_category LIKE  in_drug_name or uom LIKE in_drug_name or generic_name LIKE in_drug_name or barcode LIKE in_drug_name) and (DATE(expiry_date)> now() OR expiry_date = '1111-11-11') and facilityId =in_facilityId and store=in_store LIMIT in_from,in_to$$
DELIMITER ;

drop PROCEDURE if EXISTS pharm_get_purchase_drugs;
DELIMITER $$
CREATE  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   (DATE(expiry_date)> now() OR expiry_date = '1111-11-11') and balance>0 LIMIT in_from,in_to;
ELSE 
SELECT * FROM `pharm_store` WHERE facilityId=in_facilityId AND balance > 0 and   (DATE(expiry_date)> now() OR expiry_date = '1111-11-11');
end if;
end$$
DELIMITER ;

drop PROCEDURE if EXISTS pharm_reports_dashboard;
DELIMITER $$
CREATE  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, grn_no as mop,patient_id 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 SUM(dr) as total,reference_no,acct,createdAt FROM account_entries WHERE dr>0 and facilityId=in_facilityid AND date(createdAt) between in_date_from and in_date_to GROUP BY reference_no;


end if;

end$$
DELIMITER ;


DROP PROCEDURE pharm_add_sale_department;

DELIMITER $$
CREATE  PROCEDURE `pharm_add_sale_department`(IN `in_trn_number` VARCHAR(50), IN `in_item_name` VARCHAR(100), IN `in_qty_in` INT(100), IN `in_expiry_date` DATE, IN `in_selling_price` FLOAT(10), IN `in_branch_location` VARCHAR(100), IN `in_transaction_date` VARCHAR(20), IN `in_item_category` VARCHAR(50), IN `in_item_code` VARCHAR(90), IN `in_movement_source` VARCHAR(50), IN `in_version_id` VARCHAR(50), IN `in_facilityId` VARCHAR(50), IN `in_qty_out` INT(11), IN `in_status` 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_supplier_code` VARCHAR(50), IN `in_supplier_name` VARCHAR(200), IN `in_receipt_no` VARCHAR(50), IN `in_user_id` VARCHAR(50), IN `in_userName` VARCHAR(50), IN `_branch` VARCHAR(50), IN `in_barcode` VARCHAR(100), IN `in_patient_id` VARCHAR(100))
    NO SQL
BEGIN

declare quantity int;
declare quantity_approved int;
declare requisition_number int;
DECLARE items varchar(50);
DECLARE balance_quantity int;
DECLARE new_status varchar(50);
DECLARE approved_balance,new_item_balance1,balance_data1 int;

   
SELECT ifnull(balance,0) into balance_data1
FROM pharm_store where drug_name=in_item_name and selling_price=in_selling_price and expiry_date=in_expiry_date and store=in_branch_location AND item_code=in_item_code; 

set new_item_balance1=balance_data1-ifnull(in_qty_out,0);

update pharm_store set balance=new_item_balance1  where drug_name=in_item_name and selling_price=in_selling_price and expiry_date=in_expiry_date and store=in_branch_location and item_code=in_item_code; 

INSERT INTO pharm_store_entries(version_id, trn_number,drug_name,qty_in,expiry_date,selling_price,transfer_from,
transfer_to,receive_date,qty_out,drug_category,item_code,facilityId,branch_name, grn_no, truckNo,waybillNo,otherInfo,supplier_code,supplier_name,uniqueId,inserted_time,inserted_by,userName,sales_type, barcode,patient_id)

 VALUES(in_version_id,in_trn_number,in_item_name,in_qty_in,in_expiry_date,in_selling_price,in_branch_location,in_movement_source,now(),in_qty_out,
in_item_category,in_item_code,in_facilityId,in_branch_location,in_req_no,in_truck_no,in_waybill_no,in_other_info,in_supplier_code,in_supplier_name,in_receipt_no,now(),in_user_id,in_userName,"sales", in_barcode,in_patient_id);


END$$
DELIMITER ;


pharm_get_drug_search
pharm_get_drug_search


ALTER TABLE `dispensary` CHANGE `price` `price` INT(11) NULL;
ALTER TABLE `pharm_store_entries` ADD `patient_id` VARCHAR(100) NULL AFTER `userName`;
ALTER TABLE `dispensary` CHANGE `drug_id` `drug_id` VARCHAR(11) NULL DEFAULT NULL;        
ALTER TABLE `dispensary` CHANGE `discount` `discount` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL, CHANGE `total` `total` INT(50) NULL, CHANGE `id` `id` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;



ALTER TABLE `patientrecords` CHANGE `patient_id` `patient_id` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`patient_id`);