View source for How to route and bill from a database

Yate was built to easily allow building applications based on it. Most of the applications require the use of a database.

Yate offers a module that does authentication, registration, routing from database, and it's called [[register]] and his configuration file is register.conf. It also allows to write the cdr(call logs) in the database.

Depending on the level of complexity of your application you can use just some of the features of the [[register]] module and implement some of the functionality from another module or external script.

===Enable messages===

In register.conf file you have to activate handlers for each message you are going to use. That means that you have to enable parameters:

:- user.auth=yes -> Activate handler on the [[user.auth]] message. Used to authenticate users.
:- user.register=yes -> Activate handler on the [[user.register]] message. Used for users registration.
:- call.route=yes -> Activate handler on the [[call.route]] message. Used to route the calls.
:- call.cdr=yes -> Activate handler on the [[call.cdr]] message. Used to write CDRs in the database.

===Configure database in Yate ===

There are 3 steps to accomplish this task:
# create your database with tables that you need for your configuration (here is an example of [[Register_Database_Schema|a database schema]]).
# put the credentials of the database of the database created in Yate's configuration file: pgsqldb.conf).
# '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: [[PostgreSQL|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 connection====

When using [[register]] module, set the name of the database connection in section [default]:

 [default]
 account=yateadmin

===Define, authenticate, register Users===

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

Then in register.conf you have to authenticate, register your users by enabling user.auth and user.register in '''[general]''' and writing their associated queries.

To authenticate and register users user.auth and user.register are enabled. 
register.conf

 [general]
 user.auth=yes
 user.register=yes
 
 [user.auth]
 query=SELECT password FROM users WHERE username='${username}' 
 result=password 
 
 [user.register] 
 query=UPDATE users SET location='${data}',expires=CURRENT_TIMESTAMP + INTERVAL '${expires} s' WHERE username='${username}'

===Route calls from database===

See an example of a [[Register_Database_Schema|database schema]].

In [[Register|register.conf]] in section '''[general]''' the ''call.route'' and ''call.cdr'' must be enabled. They are used for routing the calls and writing the call logs in the database.

 [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}'

For more complex routing like routing to registered lines(gateways) which are defined in the database, you can do a stored procedures.

===Billing from database===

There are two types of billing:

* postpaid billing - the billing can be done for a period of time after the calls ended. You can use the data stored in cdr table ('billtime' field) to make the algorithm for taxing your users. For this you may need custom parameters and you have to add them in cdrbuild.conf. You can read more on [[how to add custom parameters in CDR from routing]] in a dedicated article.

* prepaid billing - the billing must be calculated when the routing is done. You can create a stored procedure in which parameter 'timeout' that will keep the life time for the channel in it. 

On 'cdr.finalize' you have to update the call duration. This can be done from an external script or from a stored procedure.

=== Custom module to do routing===

If you don't want to use stored procedures you can build your own global external module to calculate the timeout.


'''See also'''

* [[Register|Register module]] - authenticate, register and route users from database.
* [[PostgreSQL]]
* [[Routing]]

[[Category:Routing]] [[Category:Database]] [[Category:Billing]]