How to route and bill from a database

Suppose that you have this scenario: you want to route and bill from a database using PostgreSQL.

First you have to create your databases with the tables accordingly to your needs. The table to keep the users with their addresses and their phone number. The table to register all CDRs. The tables used for billing: one to keep the prefixes and one for the prices associated with the prefixes.

Users

The users with their information will be kept in the database. You can see table users as an example of a table schema.

Configure database in Yate

There are 3 steps to accomplish this task:

1. create your database with tables that you need for your configuration (here is an example of a database schema).
2. put the credentials of the database of the database created in Yate's configuration file: pgsqldb.conf).
3. 'tell' Yate which connection to use when users will be registered. This is done in register.conf file.

Set the connection information of database

In PostgreSQL configuration file: pgsqldb.conf we have to set the connection data for this database in a section that will have the same name as the account settled in register.conf:

[yateadmin]
host=localhost
port=5432
database=yateadmin
user=postgres
password=secret
  

After "=" you have to write your connection information.

Set the name of the database

In module used is register.conf, in section [default] the name of the database must be set:

[default]
account=yateadmin

Route calls from database

See an example of a database schema.

In register.conf in section [general] the call.route and call.cdr must be enabled.

[general]
call.route=yes
call.cdr=yes

[call.route] 
query=SELECT location,(CASE WHEN location IS NULL THEN 'offline' ELSE NULL END) AS error FROM users WHERE username='${called}'
result=location
    
[call.cdr]
initquery=UPDATE cdr SET ended=true WHERE ended IS NULL OR NOT ended

cdr_initialize=INSERT INTO cdr VALUES(TIMESTAMP 'EPOCH' + INTERVAL '${time} s','${chan}','${address}','${direction}','${billid}',\
'${caller}','${called}',INTERVAL '${duration} s' ,INTERVAL '${billtime} s',INTERVAL '${ringtime} s','${status}','${reason}',false)

cdr_update=UPDATE cdr SET caller='${caller}',called='${called}',duration=INTERVAL '${duration} s',billtime=INTERVAL '${billtime} s',\
ringtime=INTERVAL '${ringtime} s',status='${status}',reason='${reason}' WHERE chan='${chan}' AND billid='${billid}'
 
cdr_finalize=UPDATE cdr SET caller='${caller}', called='${called}', duration=INTERVAL '${duration} s',billtime=INTERVAL '${billtime} s',\
ringtime=INTERVAL '${ringtime} s',status='${status}',reason='${reason}',ended=true WHERE chan='${chan}' AND billid='${billid}' 

Bill from database

You can make an external script to bill your users.

You need to have a table to keep the prefixes and one for the prices. Then you have to look into the billtime field and do the algorithm for taxing your users.

See also

• Register module - authenticate, register and route users from database.
• PostgreSQL