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.

Users

The users with their information will be kept in the database. You can see an example of a database 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