Database Integrations

MiRTA PBX is a realtime PBX, meaning writing to and reading from the database can be done and all data are realtime. MySQL access can be obtained from the local server using one of the following users:

• root with password passw0rd
• asterisk with password asterisk

There are two databases:

• asterisk : containing the configuration and realtime data
• asteriskcdrdb : containing the call history

Billing

For integrating the billing in an external software, it is important to understand how the call cost is computed and stored in the system. The main table to refer is asteriskcdrdb.cdr. This is the standard asterisk table with some columns added. In particular, although the new Asterisk 12 assure now the uniqueness of the uniqueid+sequence columne, a real, database driven, unique column ID has been added and it will be used for computing costs.

Costs of completed calls are computed by a macro when call ends. When a prepaid profile is used, a temporary cost is inserted to prevent the client to run over its credit. Every day the sum of all the costs of the calls are consolidated in a single record in the bi_billings table

Details for call costs are stored in the cc_callcosts table with the following structure:

• cc_te_id : Tenant ID
• cc_uniqueid : Uniqueid of the call. Be warned that is not a unique id as one can easily think, but it is a unique identification for the call, but a call in asterisk can have several legs and so multiple records with the same uniqueid can be present in the cdr, so you need to use the sequence/cc_cdr_sequence value
• cc_cdr_sequence : Link with the sequence column in the cdr
• cc_cost : Call cost
• cc_bi_id : Link with the column bi_id in the bi_billings table

Consolidated billings can be found in the bi_billings table with the following structure:

• bi_id : ID of the call cost, used as link in the cc_callcosts table with the column cc_bi_id
• bi_te_id : Tenant ID
• bi_description : A description of the cost or the credit loaded on the customer account
• bi_date : Date of the billing
• bi_amount : Credit or Debit for the client

Phone Books

The phone book structure is made of several tables for a complete customization:

• pb_phonebooks : containing the phone book name
• pi_phonebookitems : containing the different columns available for the phone book
• pl_phonebooklayout : contains the structure of the phonebook
• pe_phonebookentries : contains the connection between the phone book and the group of info stored in pd_phonebookdetails
• pd_phonebookdetails : contains the info about the entries

Tenants

Creating a new tenant directly in the database needs to update two tables:

• te_tenants : containing the tenant info. You just need to enter the tenant name (te_name) and tenant code (te_code)
• pk_parkinglots : contains the parking lots info. You need to enter at least the te_id from te_tenants in the pk_te_id field and the te_code in the pk_name. Enter the parking lot range in pk_start and pk_end

Extensions

There are right now three types of Extensions, so multiple tables are involved based on the extension type. All Extensions are stored in the ex_extensions table. Each extension is referenced by its id, stored in ex_id column. The type of the extension is stored in ex_tech field, identifying a different secondary table. The secondary id is stored in the ex_tech_id field.

Currently, ex_tech can be:

• SIP - for normal chan_sip extensions. It involves the usage of sipfriends and sipregs tables
• VIRTUAL - for virtual extensions. It involves the usage of ve_virtualextensions and vi_virtualextensionitems
• PJSIP - for new, still experimental pjsip extensions. It involves all pjsip_* tables

For each extension created, based on the name assigned to the extension, the table dn_dialbyname is populated, by creating a three digits access code for the name (dn_number)