We write primitive billing statistics for Asterisk

As you know, Asterisk can store call details, it’s a CDR (Call Detail Records). CDR can be stored in different ways, it is a regular csv-file and almost any database. We have, for example, a regular database on MySQL, with a single tablet - cdr. The goal was set: to write a script that would count the cost of calls.

Stage 1: we study the structure of the cdr table and what is stored in it

We are interested in the fields:

• src- source;
• dst - destination;
• billsec - chargeable seconds (seconds after lifting the handset);
• channel - used channel;
• dstchannel - direction channel;
• calldate - date and time;
• uniqueid - unique identifier;
• disposition - what happened to the call: ANSWERED, NO ANSWER, BUSY, FAILED.
• userfield is a free field.

Stage 2: Create your own tables.

price_russia - a table with codes of Russian regions and prices.

CREATE TABLE IF NOT EXISTS `price_russia ` ( `code` int(10) NOT NULL COMMENT ' ', `cost` varchar(10) NOT NULL COMMENT '', `region` varchar(100) NOT NULL COMMENT '', UNIQUE KEY `code` (`code`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

price_international - table with country codes and prices.

 CREATE TABLE IF NOT EXISTS ` price_international` ( `code` int(10) NOT NULL COMMENT ' ', `price` varchar(10) NOT NULL COMMENT '', `country` varchar(100) NOT NULL COMMENT '', UNIQUE KEY `code` (`code`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

')
clients - table with clients.

 CREATE TABLE IF NOT EXISTS `clients` ( `login` varchar(32) NOT NULL COMMENT '', `password` varchar(32) NOT NULL COMMENT '', `email` varchar(40) NOT NULL COMMENT 'Email', `rate` smallint(4) NOT NULL COMMENT '', UNIQUE KEY `login` (`login`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

* The rate field allows you to give a discount or set a mark-up for a client (we write 90 - we give a discount of 10 percent, we put 110 - we get a mark-up of 10 percent).

clients_ext - client and extension mapping.

 CREATE TABLE IF NOT EXISTS `clients_ext` ( `login` varchar(32) NOT NULL, `ext` int(6) NOT NULL, UNIQUE KEY `ext` (`ext`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

alls - a table with processed calls.

 CREATE TABLE IF NOT EXISTS `calls` ( `uniqueid` varchar(32) NOT NULL, `date` datetime NOT NULL, `login` varchar(32) NOT NULL, `rate` bigint(10) NOT NULL, `ext` bigint(10) NOT NULL, `dst` bigint(20) NOT NULL, `src` bigint(20) NOT NULL, `type` varchar(20) NOT NULL, `minutes` int(10) NOT NULL, `seconds` int(100) NOT NULL, `cost` int(10) NOT NULL, `description` varchar(100) NOT NULL, UNIQUE KEY `uniqueid` (`uniqueid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

* type - call type (incoming / outgoing).

Stage 3: Data Processing.

It is assumed that this script will run on a schedule, and in order not to miss anything, at the beginning of the script execution we mark the raw lines in order to process only them, and not touch those that may appear while the script is running:

 mysql_query("UPDATE cdr SET userfield=`step1` WHERE userfield=``;"); 

The first thing we do is load information into the array about:
1. Clients, their extensions, groups and multiplier.

 function get_clients(){ //      ,       . $query=mysql_query("SELECT rate,ext,login FROM clients, clients_ext WHERE clients_ext.login = clients.login"); while($row=mysql_fetch_assoc($query)){ $clients[$row['ext']]['rate']=$row['rate']; $clients[$row['ext']]['user']=$row['login']; } return $clients; } 

2. Regional codes, description and prices in Russia.

 function get_price_russia(){ //       $query=mysql_query("SELECT * FROM price_russia"); while($row=mysql_fetch_assoc($query)){ $price[$row['code']]['cost']=$row['price']; $price[$row['code']]['description']=$row['region']; } return $price; } 

3. Country codes, description and prices for the world.

 function get_price_international(){ //     . $query=mysql_query("SELECT * FROM price_international"); while($row=mysql_fetch_assoc($query)){ $price[$row['code']]['cost']=$row['price']; $price[$row['code']]['description']=$row['country']; } return $price; } 

Accordingly, we do this:

 $clients=get_clients(); $price['russia']=get_price_russia(); $price['international']=get_price_international(); 

Now we can select the marked lines from the database and start processing.

 $query=mysql_query("SELECT * FROM cdr WHERE userfield=`step1`"); while($row=mysql_fetch_assoc($row)){ // } 

Actually processing ( , .) :

Determine the direction of the call:

 function get_call_type($dst,$src){ $dst=strlen($dst); $src=strlen($src); if($scr<7 && $dst<7)$type='internal'; // . if($src<7 && $dst>=7)$type='outcoming'; //  if($src>=7 && $dst<7)$type='incoming'; // return $type; } $type=get_call_type($row['dst'],$row['src']); 

Now we need to define an extension by which we can identify a customer. Extension can be retrieved from the channel, in the cdr table, these are the “channel” fields for outgoing calls and “dstchannel” for incoming calls.

 function get_ext_from_channel($channel){ //  ext  channel $channel=split("/",$channel); $channel=split("-",$channel[1]); return($channel[0]); } switch ($type) { case 'internal': $ext=$row['src']; break; case 'incoming': $ext=get_ext_from_channel($row['dstchannel']); break; case 'outcoming': $ext=get_ext_from_channel($row['channel']); break; } 

* I know that it's better to use regular expressions, but ...

Now we define which client belongs to the extension.

 $login=$clients[$ext]['login'] 

and multiplier

 $rate=$clients[$ext]['rate'] 

Round seconds to minutes:

 $minutes=ceil($row['billsec']/60); 

If the call is internal or incoming, the price can be set to zero, and leave the description blank. Otherwise, you need to determine where the call went.

 if($type=='outcoming'){ // . }else{ $cost=0; $description=0; } 

Determine the price of the call:

 // 8495     7 . function check_for_moscow($num){ if(strlen($num)==7)$num='8495'.$num; return $num; } $dst=check_for_moscow($row['dst']); //    ,   .       :      10.     11    - ,   - . function get_country_type($number){ if(strlen($number)<=11){ $return='russia'; }else{ $return='international'; } return $return; } $country=get_country_type($dst); $cost=''; $description=''; $i=1; //  -        (8xxx),     4 (810xxx) if($country=='international'){$s=3;}else{$s=1;} //     ,        9 -  .   2-3 . while($cost==''){ $code=substr($dst,$s,$i); $cost=$price[$country][$code]['cost']; $description=$price[$country][$code]['description']; $i++; } 

Now we have everything to write the call information to the table.

 mysql_query("INSERT INTO calls(uniqueid, date, login, rate, ext, dst, src, type, minutes, seconds, cost, description) VALUES ('$row[uniqueid]', '$row[calldate]', '$login', '$rate', '$ext', '$dst', '$row[src]', '$type', '$minutes', '$row[billsec]', '$cost', '$description')"); 

And at the very end, mark the lines as fully processed:

 mysql_query("UPDATE cdr SET userfield=`done` WHERE userfield=`step1`"); 

The above was written for Moscow, but can easily be redone for another city or country.
Link to sources: 77.108.85.102/habr/import.php.txt , 77.108.85.102/habr/functions.php.txt ,