start transaction;
DROP PROCEDURE IF EXISTS group_service;
DELIMITER $$
CREATE  PROCEDURE `group_service`(IN `in_head` VARCHAR(30), IN `in_subhead` VARCHAR(30), IN `in_descr` VARCHAR(90), IN `facId` VARCHAR(56), IN `query_type` VARCHAR(30), IN `in_price` FLOAT, IN `in_qty` FLOAT)
BEGIN
IF query_type = 'insert' THEN
INSERT INTO `group_service`(`head`, `subhead`, `description`, `facilityId`,price,quantity) VALUES (in_head,in_subhead,in_descr,facId,in_price,in_qty);
ELSEIF query_type = 'select' THEN
SELECT DISTINCT description,SUM(price)as price FROM group_service WHERE facilityId=facId GROUP BY description;
ELSEIF query_type = 'group_list' THEN
SELECT a.head, a.subhead, a.description, a.price, g.quantity FROM account a JOIN group_service g ON a.head = g.head WHERE g.description = in_descr AND g.facilityId = facId;
END IF;
END$$
DELIMITER ;

-- ALTER TABLE `surgical_note` ADD UNIQUE(`patient_id`, `facilityId`);
DROP PROCEDURE IF EXISTS `surgical_note`; 
DELIMITER $$
CREATE  PROCEDURE `surgical_note`(IN `in_template` VARCHAR(4324), IN `in_patient_name` VARCHAR(80), IN `in_relative` VARCHAR(80), IN `in_agreed` VARCHAR(10), IN `in_witness_by` VARCHAR(80), IN `in_patient_id` VARCHAR(15), IN `in_created_at` DATETIME, IN `in_created_by` VARCHAR(50), IN `facId` VARCHAR(50), IN `query_type` VARCHAR(20))
BEGIN
IF query_type = 'insert' then
INSERT INTO surgical_note_temp(template,facilityId) VALUES (in_template,facId);
ELSEIF query_type='select' THEN
SELECT template FROM surgical_note_temp WHERE facilityId=facId ORDER BY id DESC LIMIT 1;
ELSEIF query_type='insert_surgical_note' THEN
INSERT INTO `surgical_note`(`patient_name`, `relative`, `agreed`, `witness_by`, `patient_id`, `created_at`, `created_by`,facilityId) VALUES (in_patient_name,in_relative,in_agreed,in_witness_by,in_patient_id,in_created_at, in_created_by,facId);
ELSEIF query_type='select_surgical_note' THEN
SELECT * FROM surgical_note WHERE patient_id=in_patient_id AND facilityId=facId;
END IF;
END$$
DELIMITER ;


-- ALTER TABLE `consultations` ADD `patient_name` VARCHAR(60) NULL AFTER `patient_id`;
-- ALTER TABLE `consultations` ADD `seen_by` VARCHAR(60) NULL AFTER `userId`;
DROP PROCEDURE IF EXISTS `consultation_record`;
DELIMITER $$
CREATE  PROCEDURE `consultation_record`(IN `in_query_type` VARCHAR(50), IN `in_consultation_note` VARCHAR(4000), IN `in_decision` VARCHAR(50), IN `in_dressing_request` VARCHAR(500), IN `in_nursing_request` VARCHAR(500), IN `in_user_id` VARCHAR(50), IN `in_facId` VARCHAR(50), IN `in_consult_id` VARCHAR(90), IN `in_pid` VARCHAR(100), IN `in_treatment_plan` VARCHAR(4000), IN `in_report_type` VARCHAR(50), IN `in_date` VARCHAR(50), IN `in_admissionStatus` VARCHAR(50), IN `dateFrom` DATE, IN `dateTo` DATE, IN `in_patient_name` VARCHAR(100), IN `created_by` VARCHAR(70))
    NO SQL
IF in_query_type = 'insert' THEN
        INSERT INTO consultations (id, consultation_notes,userId, decision, dressing_request, nursing_request,facilityId,patient_id,treatmentPlan,patient_name,seen_by) VALUES (in_consult_id, in_consultation_note, in_user_id, in_decision, in_dressing_request, in_nursing_request,in_facId,in_pid,in_treatment_plan,in_patient_name,created_by);
        IF in_admissionStatus = 'pending' THEN
                UPDATE patientrecords SET patientStatus='pending-admission', seen_by=in_user_id, date_seen=now() WHERE id=in_pid AND facilityId=in_facId;
        END IF;

ELSEIF in_query_type = 'update' THEN
UPDATE consultations SET consultation_notes=in_consultation_note, treatmentPlan=in_treatment_plan WHERE id=in_consult_id;

ELSEIF in_query_type = 'list by patient' THEN
        IF in_report_type = 'by_date' THEN
        SELECT a.id, a.patient_id, a.userId, concat(b.firstname,' ',b.lastname) as reviewedBy, a.consultation_notes, a.treatmentPlan, a.decision, a.dressing_request, a.nursing_request, a.nursing_request_status, a.facilityId, a.created_at FROM consultations a JOIN users b ON a.userId = b.username WHERE patient_id=in_pid AND date(created_at)=in_date ORDER BY created_at DESC;
    ELSE
                SELECT a.id, a.patient_id, a.userId, concat(b.firstname,' ',b.lastname) as reviewedBy, a.consultation_notes, a.treatmentPlan, a.decision, a.dressing_request, a.nursing_request, a.nursing_request_status, a.facilityId, a.created_at FROM consultations a JOIN users b ON a.userId = b.username WHERE patient_id=in_pid ORDER BY created_at DESC;
    END IF;
ELSEIF in_query_type = 'by_id' THEN
        SELECT * FROM consultations WHERE id=in_consult_id;
ELSEIF in_query_type = 'pending nursing requests' THEN
        SELECT id, nursing_request,dressing_request, nursing_request_status,treatmentPlan FROM consultations WHERE nursing_request_status='pending' AND facilityId=in_facId;
ELSEIF in_query_type='nursing_req_by_patient' THEN
        SELECT a.id, a.created_at, nursing_request,dressing_request, nursing_request_status, concat(b.firstname,' ',b.lastname) as doctor_name FROM consultations a JOIN users b ON a.userId = b.username WHERE a.facilityId=b.facilityId AND patient_id=in_pid AND nursing_request_status='pending' AND  a.facilityId=in_facId AND (nursing_request!='' OR dressing_request!='') ORDER BY a.created_at DESC;
ELSEIF in_query_type='treatment_plan_by_patient' THEN
        SELECT a.id, a.created_at, treatmentPlan, concat(b.firstname,' ',b.lastname) as doctor_name FROM consultations a JOIN users b ON a.userId = b.username WHERE a.facilityId=b.facilityId AND patient_id=in_pid AND treatment_plan_status='pending' AND  a.facilityId=in_facId AND treatmentPlan!='' ORDER BY a.created_at DESC;
ELSEIF in_query_type = 'complete nursing req' THEN
        UPDATE consultations SET nursing_request_status='completed' WHERE id=in_consult_id;
ELSEIF in_query_type = 'treatment-done' THEN
        UPDATE consultations SET treatment_plan_status='completed', treatment_by=in_user_id WHERE id=in_consult_id;
ELSEIF in_query_type = 'visit_days' THEN
        SELECT DISTINCT date(created_at) as created_at FROM consultations WHERE patient_id=in_pid ORDER BY created_at DESC;
ELSEIF in_query_type = 'surgery_days' THEN
        SELECT DISTINCT date(createdAt) as created_at FROM operationnotes WHERE patientId=in_pid ORDER BY createdAt DESC;
        ELSEIF in_query_type = 'history' THEN
  SELECT a.patient_id, a.userId, a.consultation_notes, a.treatmentPlan, a.decision, a.dressing_request, a.nursing_request, a.nursing_request_status, a.facilityId, a.created_at, a.treatment_plan_status, a.treatment_by, concat(b.surname, ' ', b.firstname, ' ', ifnull(b.other, '')) as full_name FROM `consultations` a JOIN patientrecords b WHERE a.patient_id=b.id and a.facilityId=b.facilityId AND a.userId=in_user_id AND a.facilityId=in_facId AND DATE(a.created_at) BETWEEN date(dateFrom) AND date(dateTo) ORDER BY a.created_at DESC;
END if$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `get_all_consultation`;
DELIMITER $$
CREATE  PROCEDURE `get_all_consultation`(IN `date_from` DATE, IN `date_to` DATE, IN `doc_name` VARCHAR(50), IN `query_type` VARCHAR(30), IN `facId` VARCHAR(80))
BEGIN
IF query_type='select_all' THEN
SELECT * from consultations WHERE facilityId=facId AND DATE(created_at) BETWEEN DATE(date_from) AND DATE(date_to);
ELSEIF query_type = 'select_by_doc' THEN
SELECT * from consultations WHERE userId=doc_name AND facilityId=facId AND DATE(created_at) BETWEEN DATE(date_from) AND DATE(date_to);
END IF;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `query_only_consultation`;
DELIMITER $$
CREATE  PROCEDURE `query_only_consultation`(IN `query_type` VARCHAR(30))
BEGIN
SELECT * FROM `consultations` WHERE consultation_notes LIKE query_type LIMIT 100;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `pharm_get_pharm_users`;

CREATE  PROCEDURE `pharm_get_pharm_users` (IN `in_facilityId` VARCHAR(50))  NO SQL SELECT * FROM users where facilityId=in_facilityId;

DROP PROCEDURE IF EXISTS `pharm_get_pharm_store`;
CREATE  PROCEDURE `pharm_get_pharm_store` (IN `in_facilityId` VARCHAR(50))  NO SQL SELECT branch_name as store_name,store_code,location as location,phone as phone,store_type as storeType,address,manage_by as manage_by FROM `pharm_branches` WHERE facilityId=in_facilityId;
DROP PROCEDURE  IF EXISTS `pharm_add_new_pharm_store`;
DELIMITER $$
CREATE  PROCEDURE `pharm_add_new_pharm_store` (IN `in_store_name` VARCHAR(100), IN `in_store_location` VARCHAR(100), IN `in_phone_number` VARCHAR(100), IN `in_store_type` VARCHAR(100), IN `in_address` VARCHAR(100), IN `in_manage_by` VARCHAR(100), IN `in_facilityId` VARCHAR(100), IN `in_created_by` VARCHAR(100), IN `in_pharm_name` VARCHAR(100), IN `in_store_code` VARCHAR(100), IN `status` VARCHAR(50))  NO SQL BEGIN
if status='insert' THEN

INSERT INTO `pharm_branches`( `branch_name`, `location`, `address`, `phone`, `store_type`, `created_time`, `facilityId`, `admin_name`, `created_by`,store_code,manage_by) VALUES (in_store_name,in_store_location,in_address,in_phone_number,in_store_type,now(),in_facilityId,in_created_by,in_manage_by,in_store_code,in_manage_by);
ELSE
UPDATE `pharm_branches` SET `location`=in_store_location,`phone`=in_phone_number,`store_type`=in_store_type,`address`=in_address,`manage_by`=in_manage_by WHERE store_code=in_store_code and facilityId=in_facilityId;
end if;
END$$
DELIMITER;

DROP TABLE `pharm_branches`;
CREATE TABLE `pharm_branches` (
  `id` int(11) NOT NULL,
  `branch_name` varchar(50) NOT NULL,
  `location` varchar(50) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `crm` varchar(50) DEFAULT NULL,
  `created_time` timestamp NULL DEFAULT current_timestamp(),
  `facilityId` varchar(50) NOT NULL,
  `store_type` varchar(100) NOT NULL,
  `admin_name` varchar(50) NOT NULL,
  `created_by` varchar(50) NOT NULL,
  `manage_by` varchar(100) NOT NULL,
  `store_code` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `pharm_branches` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`id`);
DROP PROCEDURE IF EXISTS `pharm_get_supplier_info`;
CREATE  PROCEDURE `pharm_get_supplier_info` (IN `in_facilityId` VARCHAR(50))  NO SQL SELECT `facilityId`, `supplier_name`, `date`, `address`, `phone`, `supplier_code`, `balance`, `tinnumber` `email` FROM `suppliersinfo` WHERE facilityId=in_facilityId;

ALTER TABLE `suppliersinfo` ADD `supplier_code` VARCHAR(55) NULL AFTER `website`;
ALTER TABLE `suppliersinfo` ADD `tinnumber` VARCHAR(60) NULL AFTER `supplier_code`;

DROP PROCEDURE IF EXISTS `pharm_get_supplier_count`;
CREATE  PROCEDURE `pharm_get_supplier_count` (IN `in_facilityId` VARCHAR(50))  NO SQL SELECT COUNT(*) as num FROM `suppliersinfo` WHERE facilityId=in_facilityId;



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)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 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 = supplier_balance+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 ;

-- ALTER TABLE `supplier_entries` ADD `description` VARCHAR(60) NULL AFTER `created_at`;
-- ALTER TABLE `supplier_entries` ADD `entry_id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`entry_id`);
DROP PROCEDURE IF EXISTS `pharm_add_new_supplier`;
DELIMITER $$
CREATE  PROCEDURE `pharm_add_new_supplier` (IN `in_facilityId` VARCHAR(50), IN `in_supplier_name` VARCHAR(100), IN `in_address` VARCHAR(100), IN `in_phone` VARCHAR(20), IN `in_supplier_code` VARCHAR(50), IN `in_tinnumber` INT, IN `in_supplier_type` VARCHAR(50), IN `in_website` VARCHAR(40), IN `in_vat` INT, IN `in_email` VARCHAR(50), IN `in_other_info` VARCHAR(50), IN `in_version_id` VARCHAR(50), IN `in_balance` INT)   begin
declare supplier_balance,new_supplier_balance float;

SELECT balance into supplier_balance from suppliersinfo WHERE supplier_code=in_supplier_code ;

if supplier_balance is null  THEN
INSERT INTO suppliersinfo(facilityId,supplier_name,address,phone,supplier_code,tinnumber,supplier_type,website,vat, email,other_info,version_id, balance)
  VALUES (in_facilityId,in_supplier_name,in_address,in_phone,in_supplier_code,in_tinnumber,in_supplier_type,in_website,in_vat, in_email,in_other_info,in_version_id, in_balance);
 	IF in_balance != 0 THEN
	INSERT INTO `supplier_entries`(`supplier_id`, `dr`, `cr`, `reference_no`, `facilityId`, `created_at`, `description`, `truckNo`, `waybillNo`, `version_id`, `cost_price`, `quantity`) 
    VALUES (in_supplier_code, 0,in_balance, '', in_facilityId, now(), 'Opening Balance', 
           '', '', in_version_id, 0, 0);
           end if;
	ELSEIF in_balance = 0 THEN 
    INSERT INTO suppliersinfo(facilityId,supplier_name,address,phone,supplier_code,tinnumber,supplier_type,website,vat, email,other_info,version_id, balance)
  VALUES (in_facilityId,in_supplier_name,in_address,in_phone,in_supplier_code,in_tinnumber,in_supplier_type,in_website,in_vat, in_email,in_other_info,in_version_id, in_balance);
  ELSEIF in_balance>0 THEN
    SET new_supplier_balance=supplier_balance+ in_balance;
    
    update suppliersinfo set balance=new_supplier_balance WHERE supplier_code=in_supplier_code;
    
	INSERT INTO `supplier_entries`(`supplier_id`, `dr`, `cr`, `reference_no`, `facilityId`, `created_at`, `description`, `truckNo`, `waybillNo`, `version_id`, `cost_price`, `quantity`) 
    VALUES (in_supplier_code, 0,abs(in_balance), '', in_facilityId, now(), 'Opening Balance', 
           '', '', in_version_id, 0, 0);
           ELSEIF in_balance < 0 THEN
    SET new_supplier_balance=supplier_balance+ in_balance;
    
    update suppliersinfo set balance=new_supplier_balance WHERE supplier_code=in_supplier_code;
    
	INSERT INTO `supplier_entries`(`supplier_id`, `dr`, `cr`, `reference_no`, `facilityId`, `created_at`, `description`, `truckNo`, `waybillNo`, `version_id`, `cost_price`, `quantity`) 
    VALUES (in_supplier_code, 0,in_balance, '', in_facilityId, now(), 'Opening Balance', 
           '', '', in_version_id, 0, 0);
end if;
end$$
DELIMITER ;

-- ALTER TABLE `supplier_entries` ADD `truckNo` VARCHAR(50) NULL AFTER `description`, ADD `waybillNo` VARCHAR(50) NULL AFTER `truckNo`, ADD `version_id` VARCHAR(50) NULL AFTER `waybillNo`, ADD `cost_price` INT NULL AFTER `version_id`, ADD `quantity` INT NULL AFTER `cost_price`;
-- ALTER TABLE `suppliersinfo` ADD `supplier_type` VARCHAR(55) NULL AFTER `tinnumber`, ADD `vat` VARCHAR(30) NULL AFTER `supplier_type`, ADD `email` VARCHAR(40) NULL AFTER `vat`, ADD `other_info` VARCHAR(100) NULL AFTER `email`, ADD `version_id` VARCHAR(60) NULL AFTER `other_info`;
-- ALTER TABLE `suppliersinfo` CHANGE `code` `code` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL, CHANGE `balance` `balance` INT(11) NULL, CHANGE `status` `status` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL, CHANGE `website` `website` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;

DROP  PROCEDURE IF EXISTS `pharm_get_reorder_level`;
CREATE  PROCEDURE `pharm_get_reorder_level` (IN `facId` VARCHAR(50)) NO SQL SELECT * FROM pharm_store WHERE balance < reoder_level AND facilityId=facId and balance>0;
DROP PROCEDURE IF EXISTS `pharm_get_expiry_alert`;
CREATE  PROCEDURE `pharm_get_expiry_alert` (IN `facId` VARCHAR(50))  NO SQL SELECT * FROM pharm_store WHERE DATE_FORMAT(expiry_date, '%Y-%m-%d') BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d') AND DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 6 MONTH), '%Y-%m-%d')  AND (facilityId=facId) ORDER by expiry_date ASC;

CREATE TABLE `pharm_store` (
  `balance` int(11) DEFAULT 0,
  `drug_name` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `price` float DEFAULT NULL,
  `prefix` varchar(50) DEFAULT NULL,
  `item_code` varchar(50) DEFAULT NULL,
  `group_code` varchar(50) DEFAULT NULL,
  `specification` varchar(50) DEFAULT NULL,
  `facilityId` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `expired_status` varchar(50) DEFAULT NULL DEFAULT 'false',
  `expiry_date` date DEFAULT NULL DEFAULT '1111-11-11',
  `store` varchar(50) DEFAULT NULL,
  `selling_price` float DEFAULT NULL,
  `supplier_name` varchar(100) DEFAULT NULL,
  `supplier_code` varchar(100) DEFAULT NULL,
  `store_location` varchar(50) DEFAULT NULL,
  `generic_name` varchar(50) DEFAULT NULL,
  `insert_date` datetime DEFAULT NULL,
  `reoder_level` int(50) DEFAULT NULL,
  `uom` varchar(100) DEFAULT NULL,
  `drug_category` varchar(100) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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 `in_query_type` VARCHAR(100))  NO SQL begin
if in_query_type="all" THEN
SELECT * FROM `pharm_store` WHERE facilityId=in_facilityId;

ELSE 
SELECT * FROM `pharm_store` WHERE facilityId=in_facilityId and balance>0 LIMIT in_from,in_to;
end if;
end$$
DELIMITER ;
DROP PROCEDURE IF EXISTS `pharm_get_drug_view` ;
CREATE  PROCEDURE `pharm_get_drug_view` (IN `in_branch_name` VARCHAR(100), IN `in_item_code` VARCHAR(100), IN `in_facilityId` VARCHAR(50), IN `in_date_from` DATE, IN `in_date_to` DATE, IN `in_drug_name` VARCHAR(100), IN `in_expiry_date` DATE)  NO SQL SELECT expiry_date,receive_date,drug_name,branch_name as store,qty_in,qty_out,selling_price FROM `pharm_store_entries` WHERE branch_name=in_branch_name and drug_name=in_drug_name and expiry_date=in_expiry_date and item_code=in_item_code and facilityId=in_facilityId and date(inserted_time) between in_date_from and in_date_to;
DROP PROCEDURE IF EXISTS `pharm_update_store`;
CREATE  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))   update pharm_store set selling_price=in_selling_price WHERE expiry_date=in_expiry_date and drug_name=in_drug_name and item_code=in_item_code and store=in_store and facilityId=in_facilityId;

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 balance>0;
 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 LIMIT in_from,in_to;
 end if;
 end$$
 DELIMITER ;
 
DROP PROCEDURE IF EXISTS `pharm_get_client`;
 CREATE  PROCEDURE `pharm_get_client` (IN `in_facilityId` VARCHAR(50))  NO SQL SELECT accountNo,accName,balance,contactEmail,contactAddress,contactPhone FROM patientfileno WHERE facilityId=in_facilityId;


DROP PROCEDURE IF EXISTS `pharm_get_client`;

CREATE  PROCEDURE `pharm_get_client` (IN `in_facilityId` VARCHAR(50))  NO SQL SELECT accountNo,accName,balance,contactEmail,contactAddress,contactPhone FROM patientfileno WHERE facilityId=in_facilityId;
-- ALTER TABLE `transactions` CHANGE `receiptDateSN` `receiptDateSN` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '0';
-- ALTER TABLE `transactions` CHANGE `receiptNo` `receiptNo` VARCHAR(50) NULL DEFAULT NULL
-- ALTER TABLE `transactions` CHANGE `createdAt` `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;


ALTER TABLE `supplier_entries` CHANGE `entry_id` `entry_id` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`entry_id`); 
ALTER TABLE `pharm_store_entries` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`id`); 
ALTER TABLE `transactions` CHANGE `id` `id` INT(11) NULL DEFAULT NULL AUTO_INCREMENT, add PRIMARY KEY (`id`);
ALTER TABLE `transactions` ADD `unit_price` DOUBLE NOT NULL DEFAULT '0' AFTER `credit`;


DROP PROCEDURE IF EXISTS `pharm_service_transaction`;
CREATE  PROCEDURE `pharm_service_transaction`(IN `in_description` VARCHAR(1000), 
    IN `in_acct` VARCHAR(50), 
    IN `service_amount` INT, 
    IN `in_receiptDateSN` VARCHAR(70), 
    IN `in_receiptSN` VARCHAR(50), 
    IN `in_mode_of_payment` VARCHAR(20), 
    IN `patientId` VARCHAR(50), 
    IN `in_facId` VARCHAR(50), 
    IN `sourceAcct` VARCHAR(50), 
    IN `userId` VARCHAR(50), 
    IN `in_service_head` VARCHAR(50), 
    IN `txnType` VARCHAR(10), 
    IN `in_date` DATETIME, 
    IN `in_payables_head` VARCHAR(50), 
    IN `in_recievables_head` VARCHAR(50), 
    IN `in_bank_name` VARCHAR(50), 
    IN `in_txn_date` DATE, 
    IN `in_discount` INT, 
    IN `in_discount_head` VARCHAR(50), 
    IN `in_customer_name` VARCHAR(50), 
    IN `in_branch_name` VARCHAR(50), 
    IN `qty` VARCHAR(50), 
    IN `in_version_id` VARCHAR(50), 
    IN `cus_phone` VARCHAR(20), 
    IN `cus_bank` VARCHAR(30), 
    IN `cus_acc_no` VARCHAR(20), 
    IN `transaction_amount` VARCHAR(50), 
    IN `bus_bank` VARCHAR(30), 
    IN `bus_bank_acc_no` VARCHAR(100), 
    IN `in_amount_paid` INT, 
    IN `in_truck_no` VARCHAR(50), 
    IN `in_waybill_no` VARCHAR(50), 
    IN `in_item_list` VARCHAR(500), 
    IN `in_payment_type` VARCHAR(100), 
    IN `in_price` INT(11), 
    IN `in_item_code` VARCHAR(100), 
    IN `in_expiry_date` DATE, 
    IN `in_salesFrom` VARCHAR(100), 
    IN `in_userName` VARCHAR(100),
    IN `_branch` VARCHAR(100)
) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
    declare client_balance int;
    declare main_balance int;
    declare receivable int;
    DECLARE instant_balance int;
    DECLARE cash_amount int;
    DECLARE new_bal int;

    select balance into client_balance  from `patientfileno` where accountNo=in_acct AND facilityId=in_facId;

    set cash_amount = service_amount - in_discount;
    set main_balance = client_balance-cash_amount;
    set receivable  = cash_amount-client_balance;
    set instant_balance = client_balance + cash_amount;
    set new_bal = client_balance - cash_amount;


    IF txnType = 'insta' THEN

        update  `patientfileno` set balance= new_bal where accountNo=in_acct AND facilityId=in_facId;

        -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
         --   values (in_version_id, in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date,in_truck_no,in_waybill_no,qty);

        INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
        -- `client_entries`(`acct`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
        VALUES (in_acct,in_description,0,abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
        insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
        -- transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
           -- values (in_version_id, in_description,in_service_head,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
        values (in_facId,in_txn_date, in_description,in_service_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);


        IF in_discount > 0 THEN
            -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
            -- values (concat(in_version_id, '-2'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
      
            -- transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
            -- values (concat(in_version_id, '-2'), concat('Discount on (', in_item_list,')'),in_discount_head,0,in_discount ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,0,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
            values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);

            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
            INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
            VALUES (in_acct,concat('Discount on (', in_item_list,')'),0,abs(in_discount),in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
            update `patientfileno`  set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
        END IF;


    -- if the customer is registered
    ELSE

        -- if the customer has balance more than what is buying
        if main_balance  > 0  then
            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,in_description,0,abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,qty);

            update `patientfileno`  set balance= new_bal where accountNo=in_acct AND facilityId=in_facId;

            -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
            -- values (concat(in_version_id, '-3'), in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date,in_truck_no,in_waybill_no,qty);

           insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
           values (in_facId,in_txn_date, in_description,in_service_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);

            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
            -- insert into transactions  (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
            values (in_facId,in_txn_date, in_description,in_payables_head,abs(cash_amount),0 ,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);


            -- if there is discount
            IF in_discount > 0 THEN
               -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
                -- values (concat(in_version_id, '-6'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                -- values (concat(in_version_id, '-6'), concat('Discount on (', in_item_list,')'),in_discount_head,0,in_discount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
                
                INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
                -- VALUES (in_acct,in_version_id,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
                VALUES (in_acct,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
                update  `patientfileno` set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
  
            END IF;

        -- if the customer has not enough balance
        elseif main_balance < 0 then
            update  `patientfileno` set balance= main_balance where accountNo=in_acct AND facilityId=in_facId;

            -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
            -- values (concat(in_version_id, '-7'), in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date,in_truck_no,in_waybill_no,qty);
 
            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,in_description,0,abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,qty);
            INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
            VALUES (in_acct,concat('Discount on (', in_item_list,')'),0, abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
            update  `patientfileno` set balance= new_bal where accountNo=in_acct AND facilityId=in_facId;
            
            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
            values (in_facId,in_txn_date, in_description,in_service_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);

            -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date,     customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
            -- values (concat(in_version_id, '-8'), in_description,in_service_head,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);


            -- if the current balance is zero
            if client_balance = 0 then
               -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date,     customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
               -- values (concat(in_version_id, '-9'), in_description,in_recievables_head,abs(cash_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, in_description,in_recievables_head,abs(cash_amount),0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);


                IF in_discount > 0 THEN
                 --   insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
                 --   values (concat(in_version_id, '-10'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
                   -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date,     customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                  --  values (concat(in_version_id, '-10'), concat('Discount on (', in_item_list,')'),in_discount_head,0,in_discount ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                    insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                    values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
            
                   --  INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
                   --  VALUES (in_acct,in_version_id,concat('Discount',' for (', in_item_list, ')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
                    INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
              
                    VALUES (in_acct,concat('Discount on (', in_item_list,')'), abs(in_discount),0, in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
                    update  `patientfileno` set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
  
                END IF;

            elseif client_balance > 0  then

                --  insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
               --  values (concat(in_version_id, '-11'), in_description,in_payables_head,abs(client_balance),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, in_description,in_payables_head,abs(client_balance),0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);

                -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date,     customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                -- values (concat(in_version_id, '-12'), in_description,in_recievables_head,abs(receivable),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
               -- insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`)
               -- values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId);
            
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, in_description,in_recievables_head,abs(receivable),0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
        


                IF in_discount > 0 THEN
                -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
                -- values (concat(in_version_id, '-13'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
                -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                
                -- values (concat(in_version_id, '-13'), concat('Discount on (', in_item_list,')'),in_discount_head,0 ,in_discount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                 insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,abs(in_discount),userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);


              --  INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
              --  VALUES (in_acct,in_version_id,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
                INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
              
                VALUES (in_acct,concat('Discount on (', in_item_list,')'), abs(in_discount),0, in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
                update  `patientfileno` set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
                END IF;

            elseif client_balance < 0  then

               -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                -- values (concat(in_version_id, '-14'), in_description,in_recievables_head,abs(cash_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, in_description,in_recievables_head,abs(receivable),0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
    
                -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
                -- VALUES (in_acct,in_version_id,in_description,0,abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,qty);
                -- update  `patientfileno` set balance= new_bal where accountNo=in_acct AND facilityId=in_facId;

                IF in_discount > 0 THEN
                   -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
           -- values (concat(in_version_id, '-15'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
                -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                -- values (concat(in_version_id, '-15'), concat('Discount on (', in_item_list,')'),in_discount_head,0 ,in_discount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
            
                -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
                -- VALUES (in_acct,in_version_id,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
                INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
              
                VALUES (in_acct,concat('Discount on (', in_item_list,')'), abs(in_discount),0, in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
                update  `patientfileno` set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
                
                END IF;

            end if;
        
        -- if the current balance is equal to what he has in the account
        elseif  main_balance = 0 then

            update  `patientfileno` set balance= main_balance  where accountNo=in_acct AND facilityId=in_facId;

            -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
            -- values (concat(in_version_id, '-16'), in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date,in_truck_no,in_waybill_no,qty);
            
            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,in_description,0,abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,qty);
             INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
            VALUES (in_acct,in_description,0, abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
            update  `patientfileno` set balance= new_bal where accountNo=in_acct AND facilityId=in_facId;
  
            -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
            -- values (concat(in_version_id, '-17'), in_description,in_service_head,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
            values (in_facId,in_txn_date, in_description,in_service_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
    

            -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
            -- values (concat(in_version_id, '-18'), in_description,in_payables_head,abs(client_balance),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
            values (in_facId,in_txn_date, in_description,in_payables_head,abs(client_balance),0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);


            IF in_discount > 0 THEN
               -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
          --  values (concat(in_version_id, '-19'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
                -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                -- values (concat(in_version_id, '-19'), concat('Discount on (', in_item_list,')'),in_discount_head,0,in_discount ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`)
            values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,in_discount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
                
                
            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
            INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
              
            VALUES (in_acct,concat('Discount on (', in_item_list,')'), abs(in_discount),0, in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
            
            update  `patientfileno` set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
   
            END IF;


        end if;

        IF in_amount_paid > 0 THEN
           -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
           -- values (concat(in_version_id, '-20'), in_acct,in_amount_paid,0,in_receiptDateSN,concat(in_payment_type,' for (', in_item_list, ')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);

           --  insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date,     customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
           --  values (concat(in_version_id, '-20'), concat(in_payment_type, ' for (', in_item_list, ')'),in_service_head,in_amount_paid,0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
           insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
            values (in_facId,in_txn_date, concat(in_payment_type, ' for (', in_item_list, ')'),in_service_head,in_amount_paid,0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
               
           -- Haltingx`
           
            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,concat(in_payment_type,' for (', in_item_list, ')'),in_amount_paid,0,in_facId,in_mode_of_payment,in_receiptSN,0);
            INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
            VALUES (in_acct,concat(in_payment_type,' for (', in_item_list, ')'), in_amount_paid,0, in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);

            update  `patientfileno` set balance= new_bal+in_amount_paid where accountNo=in_acct AND facilityId=in_facId;
  
        END IF;

    END IF;
END $$

DROP PROCEDURE IF EXISTS `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)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER 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)
 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");


END $$

DELIMITER ;
ALTER TABLE `transactions` DROP `id`;
ALTER TABLE `transactions` CHANGE `transaction_id` `transaction_id` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`transaction_id`);
ALTER TABLE `account_entries` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`id`);

DROP PROCEDURE IF EXISTS `pharm_getReceiptData`;
CREATE  PROCEDURE `pharm_getReceiptData`(IN `in_receipt_no` VARCHAR(100), IN `in_facilityId` VARCHAR(100)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER SELECT description, description as item_name, debit as amount, debit as price , debit, qty,acct, unit_price  from transactions where receiptDateSN=in_receipt_no and facilityId=in_facilityId;

DROP PROCEDURE IF EXISTS `pharm_add_new_drug_purchase`;
DELIMITER $$
CREATE 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)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN

declare supplier_balance,new_supplier_balance float;
declare drug_code_data,drug_name_data,balance_data,store_data, facilityId_data varchar(50);
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) 
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");

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

update pharm_store set selling_price=in_selling_price WHERE item_code=in_item_code and drug_name=in_item_name  and facilityId= in_facilityId  and store=in_branch_name;

if  drug_code_data=in_item_code and drug_name_data=in_item_name and selling_price_data=in_selling_price and store_data=in_branch_name and facilityId_data= in_facilityId and expiry_date_data=in_expiry_date THEN



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


update pharm_store set balance=new_drug_balance WHERE item_code=in_item_code and drug_name=in_item_name and selling_price=in_selling_price and  facilityId= in_facilityId and expiry_date=in_expiry_date and store_data=in_branch_name;
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)
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);

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) 
                   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');

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);
end if;

END $$

-- ______________________________
ALTER TABLE `lab_requisition` CHANGE `description` `description` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE `lab_requisition` CHANGE `account_name` `account_name` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL, CHANGE `unit_code` `unit_code` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL, CHANGE `unit_name` `unit_name` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL, CHANGE `payable_head` `payable_head` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL, CHANGE `receivable_head` `receivable_head` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL, CHANGE `account` `account` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL, CHANGE `patient_name` `patient_name` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL, CHANGE `discount_head` `discount_head` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
CREATE TABLE `mylikita1`.`charges_fees_temp` (`id` INT NOT NULL AUTO_INCREMENT , `revenueSource` VARCHAR(50) NOT NULL , `amount` INT NOT NULL , `accountHead` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `charges_fees_temp`( `revenueSource`, `amount`, `accountHead`) 
VALUES ('Pharmarcy',100,200),('Laboratory',100,300),('Consultation',300,400);
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 ;

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;
 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
INSERT INTO `charges_fees`(`patient_id`, `user_id`,`dr`, `cr`, `description`, `status`,facilityId) VALUES (p_id,u_id,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;
END IF;
END$$
DELIMITER ;

ALTER TABLE `account_entries` CHANGE `quantity` `quantity` INT(11) NULL DEFAULT NULL;
ALTER TABLE `lab_requisition` ADD `doctor_fullname` VARCHAR(60) NULL AFTER `discount_amount`;
ALTER TABLE `lab_requisition` ADD `doctor_signation` VARCHAR(100) NULL DEFAULT NULL AFTER `receivable_head_name`;
ALTER TABLE `hospitals` ADD `balance` FLOAT NOT NULL DEFAULT '0' AFTER `hasStore`;
ALTER TABLE `patientrecords` CHANGE `patient_id` `patient_id` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`patient_id`);
ALTER TABLE `patientfileno` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`id`);


commit;
=======
ALTER TABLE `dispensary` ADD `drug_id` INT NULL DEFAULT NULL AFTER `request_id`;

DROP PROCEDURE IF EXISTS `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 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
$$




-- new SQL
ALTER TABLE `charges_fees_temp` ADD `time_laps` INT NULL DEFAULT NULL AFTER `accountHead`;
UPDATE `charges_fees_temp` SET `time_laps` = '14' WHERE `charges_fees_temp`.`id` = 3;
---------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pharm_getReceiptData`(IN `in_receipt_no` VARCHAR(100), IN `in_facilityId` VARCHAR(100))
    NO SQL
SELECT description, description as item_name, dr as amount, (dr/quantity) as price , dr as debit,quantity as qty,acct, (dr/quantity) as unit_price  from account_entries where reference_no=in_receipt_no and facilityId=in_facilityId and dr > 0$$
DELIMITER ;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pharm_service_transaction`(IN `in_description` VARCHAR(1000), IN `in_acct` VARCHAR(50), IN `service_amount` INT, IN `in_receiptDateSN` VARCHAR(70), IN `in_receiptSN` VARCHAR(50), IN `in_mode_of_payment` VARCHAR(20), IN `patientId` VARCHAR(50), IN `in_facId` VARCHAR(50), IN `sourceAcct` VARCHAR(50), IN `userId` VARCHAR(50), IN `in_service_head` VARCHAR(50), IN `txnType` VARCHAR(10), IN `in_date` DATETIME, IN `in_payables_head` VARCHAR(50), IN `in_recievables_head` VARCHAR(50), IN `in_bank_name` VARCHAR(50), IN `in_txn_date` DATE, IN `in_discount` INT, IN `in_discount_head` VARCHAR(50), IN `in_customer_name` VARCHAR(50), IN `in_branch_name` VARCHAR(50), IN `qty` VARCHAR(50), IN `in_version_id` VARCHAR(50), IN `cus_phone` VARCHAR(20), IN `cus_bank` VARCHAR(30), IN `cus_acc_no` VARCHAR(20), IN `transaction_amount` VARCHAR(50), IN `bus_bank` VARCHAR(30), IN `bus_bank_acc_no` VARCHAR(100), IN `in_amount_paid` INT, IN `in_truck_no` VARCHAR(50), IN `in_waybill_no` VARCHAR(50), IN `in_item_list` VARCHAR(500), IN `in_payment_type` VARCHAR(100), IN `in_price` INT(11), IN `in_item_code` VARCHAR(100), IN `in_expiry_date` DATE, IN `in_salesFrom` VARCHAR(100), IN `in_userName` VARCHAR(100), IN `_branch` VARCHAR(100))
BEGIN
    declare client_balance int;
    declare main_balance int;
    declare receivable int;
    DECLARE instant_balance int;
    DECLARE cash_amount int;
    DECLARE new_bal int;

    select balance into client_balance  from `patientfileno` where accountNo=in_acct AND facilityId=in_facId;

    set cash_amount = service_amount - in_discount;
    set main_balance = client_balance-cash_amount;
    set receivable  = cash_amount-client_balance;
    set instant_balance = client_balance + cash_amount;
    set new_bal = client_balance - cash_amount;

    IF txnType = 'insta' THEN

        update  `patientfileno` set balance= new_bal where accountNo=in_acct AND facilityId=in_facId;

        -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
         --   values (in_version_id, in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date,in_truck_no,in_waybill_no,qty);

        INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
        -- `client_entries`(`acct`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
        VALUES (in_acct,in_description,abs(service_amount),0,in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
        insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
        -- transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
           -- values (in_version_id, in_description,in_service_head,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
        values (in_facId,in_txn_date, in_description,in_service_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);


        IF in_discount > 0 THEN
            -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
            -- values (concat(in_version_id, '-2'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
      
            -- transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
            -- values (concat(in_version_id, '-2'), concat('Discount on (', in_item_list,')'),in_discount_head,0,in_discount ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,0,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
            values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);

            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
            INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
            VALUES (in_acct,concat('Discount on (', in_item_list,')'),0,abs(in_discount),in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
            update `patientfileno`  set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
        END IF;


    -- if the customer is registered
    ELSE

        -- if the customer has balance more than what is buying
        if main_balance  > 0  then
            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,in_description,0,abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,qty);

            update `patientfileno`  set balance= new_bal where accountNo=in_acct AND facilityId=in_facId;

         INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
            VALUES (in_acct,in_description,abs(service_amount), 0,in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
           insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
           values (in_facId,in_txn_date, in_description,in_service_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);

            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
            -- insert into transactions  (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
            values (in_facId,in_txn_date, in_description,in_payables_head,abs(cash_amount),0 ,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);


            -- if there is discount
            IF in_discount > 0 THEN
               -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
                -- values (concat(in_version_id, '-6'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                -- values (concat(in_version_id, '-6'), concat('Discount on (', in_item_list,')'),in_discount_head,0,in_discount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
                
                INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
                -- VALUES (in_acct,in_version_id,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
                VALUES (in_acct,concat('Discount on (', in_item_list,')'),0,abs(in_discount),in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
                update  `patientfileno` set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
  
            END IF;

        -- if the customer has not enough balance
        elseif main_balance < 0 then
            update  `patientfileno` set balance= main_balance where accountNo=in_acct AND facilityId=in_facId;

            -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
            -- values (concat(in_version_id, '-7'), in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date,in_truck_no,in_waybill_no,qty);
 
            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,in_description,0,abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,qty);
            INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
            VALUES (in_acct,in_description,abs(service_amount),0, in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
            update  `patientfileno` set balance= new_bal where accountNo=in_acct AND facilityId=in_facId;
            
            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
            values (in_facId,in_txn_date, in_description,in_service_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);

            -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date,     customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
            -- values (concat(in_version_id, '-8'), in_description,in_service_head,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);


            -- if the current balance is zero
            if client_balance = 0 then
               -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date,     customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
               -- values (concat(in_version_id, '-9'), in_description,in_recievables_head,abs(cash_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, in_description,in_recievables_head,abs(cash_amount),0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);


                IF in_discount > 0 THEN
                 --   insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
                 --   values (concat(in_version_id, '-10'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
                   -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date,     customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                  --  values (concat(in_version_id, '-10'), concat('Discount on (', in_item_list,')'),in_discount_head,0,in_discount ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                    insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                    values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
            
                   --  INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
                   --  VALUES (in_acct,in_version_id,concat('Discount',' for (', in_item_list, ')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
                    INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
              
                    VALUES (in_acct,concat('Discount on (', in_item_list,')'), 0, abs(in_discount),in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
                    update  `patientfileno` set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
  
                END IF;

            elseif client_balance > 0  then

                --  insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
               --  values (concat(in_version_id, '-11'), in_description,in_payables_head,abs(client_balance),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, in_description,in_payables_head,abs(client_balance),0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);

                -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date,     customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                -- values (concat(in_version_id, '-12'), in_description,in_recievables_head,abs(receivable),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
               -- insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`)
               -- values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId);
            
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, in_description,in_recievables_head,abs(receivable),0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
        


                IF in_discount > 0 THEN
                -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
                -- values (concat(in_version_id, '-13'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
                -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                
                -- values (concat(in_version_id, '-13'), concat('Discount on (', in_item_list,')'),in_discount_head,0 ,in_discount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                 insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,abs(in_discount),userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);


              --  INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
              --  VALUES (in_acct,in_version_id,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
                INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
              
                VALUES (in_acct,concat('Discount on (', in_item_list,')'),0, abs(in_discount), in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
                update  `patientfileno` set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
                END IF;

            elseif client_balance < 0  then

               -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                -- values (concat(in_version_id, '-14'), in_description,in_recievables_head,abs(cash_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, in_description,in_recievables_head,abs(receivable),0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
    
                -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
                -- VALUES (in_acct,in_version_id,in_description,0,abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,qty);
                -- update  `patientfileno` set balance= new_bal where accountNo=in_acct AND facilityId=in_facId;

                IF in_discount > 0 THEN
                   -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
           -- values (concat(in_version_id, '-15'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
                -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                -- values (concat(in_version_id, '-15'), concat('Discount on (', in_item_list,')'),in_discount_head,0 ,in_discount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
                insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
                values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
            
                -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
                -- VALUES (in_acct,in_version_id,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
                INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
              
                VALUES (in_acct,concat('Discount on (', in_item_list,')'), 0,abs(in_discount), in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
                update  `patientfileno` set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
                
                END IF;

            end if;
        
        -- if the current balance is equal to what he has in the account
        elseif  main_balance = 0 then

            update  `patientfileno` set balance= main_balance  where accountNo=in_acct AND facilityId=in_facId;

            -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
            -- values (concat(in_version_id, '-16'), in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date,in_truck_no,in_waybill_no,qty);
            
            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,in_description,0,abs(service_amount),in_facId,in_mode_of_payment,in_receiptSN,qty);
             INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
            VALUES (in_acct,in_description,abs(service_amount),0, in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
            update  `patientfileno` set balance= new_bal where accountNo=in_acct AND facilityId=in_facId;
  
            -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
            -- values (concat(in_version_id, '-17'), in_description,in_service_head,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
            values (in_facId,in_txn_date, in_description,in_service_head,0,service_amount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
    

            -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
            -- values (concat(in_version_id, '-18'), in_description,in_payables_head,abs(client_balance),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
            values (in_facId,in_txn_date, in_description,in_payables_head,abs(client_balance),0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);


            IF in_discount > 0 THEN
               -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
          --  values (concat(in_version_id, '-19'), in_acct,in_discount,0,in_receiptDateSN,concat('Discount on (', in_item_list,')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);
                -- insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date, customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
                -- values (concat(in_version_id, '-19'), concat('Discount on (', in_item_list,')'),in_discount_head,0,in_discount ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
            insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`)
            values (in_facId,in_txn_date, concat('Discount on (', in_item_list,')'),in_discount_head,0,in_discount,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
                
                
            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,concat('Discount on (', in_item_list,')'),abs(in_discount),0,in_facId,in_mode_of_payment,in_receiptSN,0);
            INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
              
            VALUES (in_acct,concat('Discount on (', in_item_list,')'), 0,abs(in_discount), in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);
 
            
            update  `patientfileno` set balance= new_bal+in_discount where accountNo=in_acct AND facilityId=in_facId;
   
            END IF;


        end if;

        IF in_amount_paid > 0 THEN
           -- insert into account_entries (version_id, acct,dr,cr,reference_no,description,facilityId,createdAt,truckNo,waybillNo,quantity)
           -- values (concat(in_version_id, '-20'), in_acct,in_amount_paid,0,in_receiptDateSN,concat(in_payment_type,' for (', in_item_list, ')'),in_facId,in_date,in_truck_no,in_waybill_no,qty);

           --  insert into transactions (version_id, description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date,     customer_name,branch_name,quantity,txn_amount,customer_phone, customer_bank,customer_acc_no, bank_account,truckNo,waybillNo,salesFrom,item_code,expiry_date)
           --  values (concat(in_version_id, '-20'), concat(in_payment_type, ' for (', in_item_list, ')'),in_service_head,in_amount_paid,0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date,in_customer_name,in_branch_name,qty,transaction_amount,cus_phone, cus_bank, cus_acc_no, bus_bank_acc_no,in_truck_no,in_waybill_no,in_salesFrom,in_item_code,in_expiry_date);
        --    insert into `transactions` (`facilityId`, `transaction_date`, `description`, `acct`, `debit`, `credit`, `enteredBy`, `receiptDateSN`, `receiptNo`, `modeOfPayment`, `bank_name`, `status`, `client_acct`, `patient_id`,`qty`,`unit_price`,`branch_name`)
        --     values (in_facId,in_txn_date, concat(in_payment_type, ' for (', in_item_list, ')'),in_service_head,in_amount_paid,0,userId, in_receiptDateSN, in_receiptSN, in_mode_of_payment,in_bank_name,'copleted',cus_acc_no,patientId,qty,in_price,_branch);
               
           -- Haltingx`
           
            -- INSERT INTO `client_entries`(`accountNo`, `version_id`, `description`, `dr`, `cr`, `facilityId`,mode_of_payment,receiptNo,quantity) 
            -- VALUES (in_acct,in_version_id,concat(in_payment_type,' for (', in_item_list, ')'),in_amount_paid,0,in_facId,in_mode_of_payment,in_receiptSN,0);
            INSERT INTO `account_entries`(`acct`,`description`, `dr`, `cr`, `facilityId`,  `mode_of_payment`, `reference_no`, `client_id`, `txn_status`, `quantity`)
            VALUES (in_acct,concat(in_payment_type,' for (', in_item_list, ')'),0, in_amount_paid, in_facId,in_mode_of_payment,in_receiptSN,patientId,'completed',qty);

            update  `patientfileno` set balance= new_bal+in_amount_paid where accountNo=in_acct AND facilityId=in_facId;
  
        END IF;

    END IF;
END$$
DELIMITER ;



-- 22-09-2022


DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `good_transfer`(IN `in_version_id` VARCHAR(100), IN `in_qty_in` INT(6), IN `in_expiring_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 `supplyName` VARCHAR(50), IN `supply_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, facilityId_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, facilityId, expiry_date 
into item_code_data1,description_data1,price_data1,balance_data1, facilityId_data1,expiry_date_data1 
FROM `pharm_store` where drug_name=in_item_name and price=in_unit_price and expiry_date=in_expiring_date and store=in_location_from; 

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`, `price`, `facilityId`, `item_id`, `expiry_date`, `store`, `selling_price`, `supplier_name`, `supplier_code`, `insert_date`, `store_location`) VALUES (in_qty_in,in_item_name,in_unit_price,in_facilityId,in_version_id,in_expiring_date,in_location_to,in_selling_price,supplyName,supply_code,now(),in_location_from);

INSERT INTO pharm_store_entries(receive_date,drug_name,expiry_date,`transfer_from`,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,in_expiring_date,in_location_from,in_qty_in, 0,in_location_to,in_unit_price,in_selling_price,in_mark_up,in_location_from,in_facilityId,in_location_from,supply_code,supplyName,concat("Transfer to ", in_location_to));

INSERT INTO pharm_store_entries(receive_date,drug_name,expiry_date,`transfer_from`,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,in_expiring_date,in_location_from,0 ,in_qty_in,in_location_to,in_unit_price,in_selling_price,in_mark_up,in_location_to,in_facilityId,in_location_to,supply_code,supplyName,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,expiry_date,`transfer_from`,qty_out,qty_in,`transfer_to`,unit_price,selling_price,mark_up,branch_name,facilityId,version_id,supplier_code,supplier_name,sales_type,branch_name)
VALUES(now(),in_item_name,in_expiring_date,in_location_from,in_qty_in, 0,in_location_to,in_unit_price,in_selling_price,in_mark_up,in_location_from,in_facilityId,in_location_from,supply_code,supplyName,concat("Transfer to ", in_location_to));

INSERT INTO pharm_store_entries(receive_date,drug_name,expiry_date,`transfer_from`,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,in_expiring_date,in_location_from,0 ,in_qty_in,in_location_to,in_unit_price,in_selling_price,in_mark_up,in_location_to,in_facilityId,in_location_to,supply_code,supplyName,concat("Transfer to ", in_location_to));
	
end IF;


#INSERT INTO `sale_department`(`version_id`, `item_name`, `qty_in`, `qty_out`, `expiring_date`, `selling_price`, `location_from`, `location_to`,`facilityId`,supplier_name,supplier_code)
#VALUES (in_version_id,in_item_name,0,new_item_balance1,expiry_date_data1,in_selling_price,in_location_from,in_location_to,in_facilityId,supplyName,supply_code);

#SELECT item_code,description,price,ifnull(balance,0), facilityId, expiry_date 
#into item_code_data,description_data,price_data,balance_data, facilityId_data,expiry_date_data 
#FROM `item_description` where description=in_item_name and price=in_selling_price and expiry_date=in_expiring_date and store=in_location_from; 

#set new_item_balance=balance_data+ifnull(in_qty_in,0);

#insert into store(description,price,balance, facilityId, expiry_date,store )
#values (in_item_name,in_selling_price,new_item_balance, in_facilityId,in_expiring_date ,in_location_from) on duplicate key update balance=new_item_balance;

#INSERT INTO `sale_department`(`version_id`, `item_name`, `qty_in`, `qty_out`, `expiring_date`, `selling_price`, `location_from`, `location_to`, `facilityId`,supplier_name,supplier_code,item_status,trn_number)
#VALUES (concat(in_version_id,'-1'),in_item_name,in_qty_in,0,in_expiring_date,in_selling_price,in_location_from,in_location_to,in_facilityId,supplyName,supply_code,"Pending",trn);
call update_number_generator('trn',trn);
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_number_generator` (IN `in_query_type` VARCHAR(50), IN `in_number` INT(50))  NO SQL
BEGIN
UPDATE number_generator set code_no =in_number WHERE prefix=in_query_type;
END$$
DELIMITER ;


CREATE TABLE `number_generator` (
  `description` varchar(100) NOT NULL,
  `prefix` varchar(100) NOT NULL,
  `code_no` int(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `number_generator`(`description`, `prefix`, `code_no`) VALUES ('Goods transfer','trn','1');
ALTER TABLE `pharm_store` CHANGE `item_code` `item_code` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE `pharm_store` CHANGE `item_id` `item_id` VARCHAR(60) NULL;
ALTER TABLE `pharm_store_entries` CHANGE `id` `id` VARCHAR(60) NULL;
ALTER TABLE `pharm_store_entries` CHANGE `inserted_time` `inserted_time` DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `hospitals` ADD `balance` FLOAT NOT NULL DEFAULT '0' AFTER `hasStore`;
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;