Overview
We have a billing system for unbound clients that needs to be abstracted to handle different VOIP providers. Currently our client use Inphonex as the backend provider, but we are soon to acquire Austrailian customers that are don’t have Inphonex as a VOIP provider. Thus we need to extend the billing system to handle clients with different attributes including VOIP provider. In addition, it needs to handle different taxex/fees depending on the locality (country?).
Scripts Related to the Processes
There are a few scripts in var_adm_bin related to the unbound billing process:
- unbound/unbound_recurring.pl := Make recurring charges, ?runs as daily cron?
- unbound/fetch_inhonex_cdr.pl := Populate the unbound_cdr table. The records are used
- unbound/addon_unbound.pl
- unbound/fix_inphonex_install.pl
Database Tables
live_invoice_items
One of the most important database tables for this project is the live_invoice_items table. The columns are:
- id
- server_id
- item_id
- quantity
- price
- order_header_id
- date_added
- updated
This table is used in the find_customers_over_limit and get_customer_invoices_items routines.
NOTE: live_invoice_items is INSERTed and UPDATEd via F::Order::add_item_to_live_invoice() which is called in provision.pl as well as F::Order.
unbound_cdr
This table is key as well. It is where we determine the minutes used. It is employed in both the find_customers_over_limit and the update_cdr methods. Some important fields are:
- server_id
- invoice_id
- did
- ani
- is_mobile
- call_duration
- billable_duration
- billed_amount
- customer_billed_amount
NOTE: This table is queried in F::Unbound::get_unbound_cdr_by_invoice()
customer
This table joins customer_id with:
- inphonex_id
- netsuite_id
- primary_server_id
- billing_cyle
- reseller_id
- iphonex_reseller_id
- main_address_id
- created
- order_date
Modules Involved
F::Billing::GeneralRecurring
This module is at the heart of the matter. Within this modules are two subroutines, find_customers_over_limit and get_customer_invoices that use the live_invoice_items table.
F::Inphonex
This module has VOIP provider related methods.
F::NetSuite
This modules is called upon when creating cash sales. NetSuite does the credit card processing.
Important Subroutines
find_customers_over_limit()
This method of F::Billing::GeneralRecurring uses the live_invoice_items table in the SQL query:
SELECT sum(quantity)
FROM live_invoice_items
WHERE server_id = ?
AND item_id = ?
Its purpose is to get the minutes available (pooled) which are then compared to minutes used so the cost of overage can be computed.
The used minutes is computed from the following query on the unbound_cdr table:
SELECT unique_id, billable_duration,
customer_billed_amount, country, did,
ani, dialed_number, is_mobile
FROM unbound_cdr
WHERE server_id = ?
AND disposition = "ANSWERED"
AND invoice_id is null
AND calldate >= ?
AND calldate < ?
get_customer_invoices_items()
This method uses the live_invoices_items in the query:
select li.item_id, i.netsuite_id, li.order_header_id,
i.name, li.price, sum(li.quantity) quantity
from live_invoice_items li
join item i
on (li.item_id = i.item_id)
where li.server_id = ?
group by li.item_id;
create_cashSale()
This function is the one that actually charges a person’s credit card using a F::Netsuite object and its add method. The work of the add method is done by the call method.
Services
The services involved in billing are:
- our MySQL database
- NetSuite := for credit card processing
- Inphonex := VOIP provider that provides CDR for determining usage/overage.
- BillSoft := provides tax information
MySQL database
The pbxtra database contains tables for storing parts of the billing equation. Some of them are noted in this paper
NetSuite
Inphonex
BillSoft
Showing changes from previous revision. Removed | Added
