ALTER TABLE `pending_txn` ADD `transaction_date` DATETIME NULL AFTER `tx_status`, ADD `cashier_id` VARCHAR(50) NULL AFTER `transaction_date`;
ALTER TABLE `pending_txn` ADD `mode_of_payment` VARCHAR(50)  NULL AFTER `cashier_id`;
ALTER TABLE `pending_txn` ADD `request_id` VARCHAR(50) NULL AFTER `mode_of_payment`;

DELIMITER $$
	CREATE PROCEDURE service_transaction_pharm (in in_query_type varchar(50), in in_txn_id varchar(50), in in_cashier_id varchar(50))
    BEGIN
    	if in_query_type = 'paid' THEN
        	UPDATE pending_txn SET tx_status='paid', cashier_id=in_cashier_id, transaction_date = now() where transaction_id = in_txn_id;
        END IF;
    END
$$



DROP PROCEDURE `pending_txn`;
CREATE PROCEDURE `pending_txn`(IN `in_query_type` VARCHAR(50), IN `in_transaction_id` VARCHAR(50), IN `in_description` VARCHAR(100), IN `in_head` VARCHAR(20), IN `in_subhead` VARCHAR(20), IN `in_amount` INT, IN `in_service_type` VARCHAR(50), IN `in_created_at` DATETIME, IN `in_patient_name` VARCHAR(100), IN `in_patient_id` VARCHAR(20), IN `in_patient_type` VARCHAR(50), IN `in_total_amount` INT, IN `in_tx_status` VARCHAR(45), IN `in_date_from` DATE, IN `in_date_to` DATE, IN `in_client_acc` VARCHAR(50), IN `in_item_code` VARCHAR(100), IN `in_expiry_date` DATE, IN `in_branch_location` VARCHAR(100), IN `in_qty_out` VARCHAR(11), IN `in_request_id` VARCHAR(50)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
        IF in_query_type = 'save' THEN
            INSERT INTO pending_txn (description, head,subhead, amount, service_type, 
                created_at, patient_name, patient_id, patient_type, total_amount, tx_status, client_acc,transaction_id,request_id)
            VALUES (in_description, in_head, in_subhead, in_amount, in_service_type, 
                in_created_at, in_patient_name, in_patient_id, in_patient_type, in_total_amount, 
                in_tx_status, in_client_acc,in_transaction_id,in_request_id);
        ELSEIF in_query_type = 'get_by_status' THEN
            SELECT * FROM pending_txn WHERE tx_status=in_tx_status AND DATE(created_at) BETWEEN in_date_from AND in_date_to;
        END IF;
    END

DROP PROCEDURE `get_pending_tnx`;
DELIMITER $$
CREATE PROCEDURE `get_pending_tnx`(IN `in_date_from` DATE, IN `in_date_to` DATE) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER SELECT * FROM `pending_txn` WHERE tx_status='pending' AND DATE(created_at) BETWEEN in_date_from AND in_date_to$$

DELIMITER $$
create PROCEDURE get_pending_dispense (IN in_query_type VARCHAR(50), in in_request_id VARCHAR(50))
SELECT a.description, a.amount,b.drug,b.dosage,b.duration,b.period,b.no_of_days,b.frequency,b.drugCount,b.times_per_day,b.no_times  FROM `pending_txn` a JOIN dispensary b ON a.request_id=b.request_id WHERE a.request_id=in_request_id AND a.tx_status='paid'$$


DROP PROCEDURE doctorLabRequest;
DELIMITER $$
CREATE PROCEDURE doctorLabRequest(IN in_department VARCHAR(50), IN in_test VARCHAR(100), IN in_percentage VARCHAR(5), IN in_price VARCHAR(11), IN in_code VARCHAR(50), IN in_noOfLabels VARCHAR(7), IN in_test_group VARCHAR(30), IN in_print_type VARCHAR(30), IN in_label_type VARCHAR(30), IN in_patient_id VARCHAR(50), IN in_requested_by VARCHAR(50), IN in_facilityId VARCHAR(50), IN in_status VARCHAR(11), IN query_type VARCHAR(20), IN in_req_id VARCHAR(50), IN in_description VARCHAR(100), IN in_patient_status VARCHAR(20), IN in_from VARCHAR(20), IN in_to VARCHAR(20), IN in_created_by VARCHAR(50), IN in_receiptDateSN VARCHAR(50), IN in_payment_status VARCHAR(20), IN in_old_price VARCHAR(10), IN in_payables_head VARCHAR(10), IN in_recievables_head VARCHAR(10), IN in_account VARCHAR(10), IN in_account_name VARCHAR(50), IN in_patient_name VARCHAR(100), IN in_department_code VARCHAR(10), IN in_unit_code VARCHAR(10), IN in_unit_name VARCHAR(50), IN in_unit VARCHAR(10), IN in_range_from VARCHAR(10), IN in_range_to VARCHAR(10), IN in_client_type VARCHAR(20), IN in_client_account VARCHAR(10), IN in_discount VARCHAR(10), IN in_discount_head VARCHAR(10), IN in_discount_head_name VARCHAR(50), IN in_approval_status VARCHAR(20), IN in_discount_amount VARCHAR(10), IN in_request_id VARCHAR(20))
    NO SQL
BEGIN
IF query_type= 'insert' THEN

    INSERT INTO lab_requisition(test,patient_id,facilityId,price,percentage,
        department,test_group,status,created_by,receiptNo,payment_status,label_type,noOfLabels,
        print_type,payable_head,receivable_head,account,account_name,
        patient_name,department_code,unit_code,unit_name,unit,range_from,range_to,client_type,
        client_account,discount,discount_head,discount_head_name,approval_status,discount_amount,request_id, patient_status, requested_by,description) 
    VALUES (in_test,in_patient_id,in_facilityId,in_price,in_percentage,in_department,in_test_group,
        in_status,in_created_by,in_receiptDateSN,in_payment_status,in_label_type,in_noOfLabels,
        in_print_type,in_payables_head,in_recievables_head,in_account,in_account_name,
        in_patient_name,in_department_code,in_unit_code,in_unit_name,in_unit,in_range_from, in_range_to,
        in_client_type,in_client_account,in_discount,in_discount_head,in_discount_head_name,in_approval_status,
        in_discount_amount,in_req_id,in_patient_status,in_requested_by,in_description);
    COMMIT;
    IF in_price > 0 THEN
    INSERT INTO pending_txn (transaction_id, description, head, subhead, amount, service_type, created_at, 
    patient_name, patient_id, total_amount,client_acc, tx_status, patient_type) VALUES (in_req_id, in_description, '',
    in_account, in_price, 'LAB', now(), in_patient_name, in_patient_id, 0, in_client_account, 'pending', in_client_type);
END IF;

ELSEIF query_type='ordered_list' THEN 
SELECT patient_name as name, created_at, patient_id,'' as DOB,request_id, patient_status FROM lab_requisition 
WHERE status='ordered' AND date(created_at) BETWEEN date(in_from) AND date(in_to) GROUP BY patient_id;

ELSEIF query_type = 'lab_processed' THEN
UPDATE lab_requisition SET status = 'Sample Collected' WHERE request_id = in_req_id;
   call createbooking_list(in_req_id);
  call  createlabcode_list(in_req_id);
END IF;
END$$
DELIMITER ;



DROP PROCEDURE `pharm_service_transaction`;
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), IN `patientType` VARCHAR(50)) 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,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,0,concat('Discount on (', in_item_list,')'),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