Database Integrations - Revision history

Admin at 22:04, 21 February 2021

2021-02-21T22:04:31Z

← Older revision		Revision as of 22:04, 21 February 2021
Line 19:		Line 19:
	Details for call costs are stored in the cc_callcosts table with the following structure:		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_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_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_id~~ : Link with the column ID in the ~~asteriskcdrdb.~~cdr ~~table~~		* cc_cdr_sequence : Link with the sequence column in the cdr
	cc_cost : Call cost		* cc_cost : Call cost
	cc_bi_id : Link with the column bi_id in the bi_billings table		* 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:		Consolidated billings can be found in the bi_billings table with the following structure:

Admin at 17:13, 8 November 2017

2017-11-08T17:13:39Z

← Older revision		Revision as of 17:13, 8 November 2017
Line 50:		Line 50:
	* [[te_tenants]] : containing the tenant info. You just need to enter the tenant name (te_name) and tenant code (te_code)		* [[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		* 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)

Admin at 16:01, 12 October 2016

2016-10-12T16:01:54Z

← Older revision		Revision as of 16:01, 12 October 2016
Line 48:		Line 48:
	Creating a new tenant directly in the database needs to update two tables:		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)		* [[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		* 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

Admin at 08:01, 1 August 2016

2016-08-01T08:01:25Z

← Older revision		Revision as of 08:01, 1 August 2016
Line 43:		Line 43:
	* pe_phonebookentries : contains the connection between the phone book and the group of info stored in pd_phonebookdetails		* 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		* 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

Manager at 21:09, 7 June 2016

2016-06-07T21:09:12Z

← Older revision		Revision as of 21:09, 7 June 2016
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

Manager at 20:33, 7 June 2016

2016-06-07T20:33:21Z

← Older revision		Revision as of 20:33, 7 June 2016
Line 21:		Line 21:
	* cc_id : ID of the call cost, used as link in the asteriskcdrdb.cdr table with the column cdr_cc_id		* 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_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		* 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
	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_cdr_id : Link with the column ID in the asteriskcdrdb.cdr table
	cc_cost : Call cost		cc_cost : Call cost

Manager at 20:33, 7 June 2016

2016-06-07T20:33:06Z

← Older revision		Revision as of 20:33, 7 June 2016
Line 19:		Line 19:
	Details for call costs are stored in the cc_callcosts table with the following structure:		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		* cc_id : ID of the call cost, used as link in the asteriskcdrdb.cdr table with the column cdr_cc_id
	cdr_cc_id
	* cc_te_id : Tenant 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		* 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

Manager: Created page with "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 o..."

2016-06-07T20:32:44Z

Created page with "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 o..."

New page

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