Database Integrations: Difference between revisions

From MiRTA PBX documentation
Jump to navigation Jump to search
No edit summary
No edit summary
Line 33: Line 33:
* bi_date : Date of the billing
* bi_date : Date of the billing
* bi_amount : Credit or Debit for the client
* 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

Revision as of 21:09, 7 June 2016

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_id : ID of the call cost, used as link in the asteriskcdrdb.cdr table with the column cdr_cc_id
  • 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

cc_cdr_id : Link with the column ID in the asteriskcdrdb.cdr table 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