Archive for bilhetagem

Instalando e configurando o postgres para realizar bilhetagem (cdr) do asterisk

Publicado em: 03/05/2009

O objetivo deste artigo é apenas anotar os passos de configuração do postgres bem como a criação de uma base cdr para tarifação (billing) do asterisk, e claro, scriptar tudo isso.

- Obtendo e descompactando:

wget http://wwwmaster.postgresql.org/redir/378/f/source/v8.3.7/postgresql-8.3.7.tar.bz2
 tar -xjvf postgresql-8.3.7.tar.bz2
 cd postgresql-8.3.7

- Pacotes adicionais para compilação:

apt-get install gcc libc6-dev libc-dev libmudflap0-dev zlibc zlib1g-dev

- Configure e compile:

./configure --prefix=/usr/local/postgres --bindir=/usr/bin --sysconfdir=/etc/postgres --without-readline
 make
 make install

- Criando grupo e usuário:

adduser postgres

- Criando diretório para os dados:

mkdir /usr/local/postgres/data
 chown postgres.postgres /usr/local/postgres/data

- Inicializando o banco:

su - postgres
 $ initdb -D /usr/local/postgres/data

 The files belonging to this database system will be owned by user "postgres".
 ***
 This user must also own the server process.
 The database cluster will be initialized with locale en_US.UTF-8.
 The default database encoding has accordingly been set to UTF8.
 The default text search configuration will be set to "english".
 ***
 fixing permissions on existing directory /usr/local/postgres/data ... ok
 creating subdirectories ... ok
 selecting default max_connections ... 100
 selecting default shared_buffers/max_fsm_pages ... 24MB/153600
 creating configuration files ... ok
 creating template1 database in /usr/local/postgres/data/base/1 ... ok
 initializing pg_authid ... ok
 initializing dependencies ... ok
 creating system views ... ok
 loading system objects' descriptions ... ok
 creating conversions ... ok
 creating dictionaries ... ok
 setting privileges on built-in objects ... ok
 creating information schema ... ok
 vacuuming database template1 ... ok
 copying template1 to template0 ... ok
 copying template1 to postgres ... ok
 ***
 WARNING: enabling "trust" authentication for local connections
 You can change this by editing pg_hba.conf or using the -A option the
 next time you run initdb.
 ***Success. You can now start the database server using:
 ***
 postgres -D /usr/local/postgres/data
 or
 pg_ctl -D /usr/local/postgres/data -l logfile start

- Rodando o postgres em segundo plano, gravando logs:

$ postmaster -D /usr/local/postgres/data > /usr/local/postgres/data/logfile &

- Criando base de dados e testando:

$ createdb asterisk
 $ psql asterisk

Banco instalado e funcionando!

Entendendo o header do arquivo do asterisk

1. accountcode: What account number to use (Only used when Authentication is enable)
 2. src: Caller*ID number
 3. dst: Destination extension
 4. dcontext: Destination context
 5. clid: Caller*ID with text
 6. channel: Channel used
 7. dstchannel: Destination channel if appropriate
 8. lastapp: Last application if appropriate
 9. lastdata: Last application data (arguments)
 10. start: Start of call (date/time)
 11. answer: Anwer of call (date/time)
 12. end: End of call (date/time)
 13. duration: Total time in system, in seconds (integer)
 14. billsec: Total time call is up, in seconds (integer)
 15. disposition: What happened to the call: ANSWERED, NO ANSWER, BUSY
 16. amaflags: What flags to use: see amaflags: DOCUMENTATION, BILL, IGNORE

Uma linha de log comum:

 "","4704","22221111","internal","""ftorres"" <4704>","SIP/ftorres-0824a0b8","DGV/1","Dial","Dgv/g1/11112222|60|Tt","2008-08-26 08:33:43","2008-08-26 08:34:11","2008-08-26 08:34:29",46,18,"ANSWERED","DOCUMENTATION"

Agora, vamos organizar isso tudo:

 1. "",
 2. "4704",
 3. "22221111",
 4. "internal",
 5. """ftorres"" <4704>",
 6. "SIP/ftorres-0824a0b8",
 7. "DGV/1",
 8. "Dial",
 9. "Dgv/g1/22221111|60|Tt",
 10. "2008-08-26 08:33:43",
 11. "2008-08-26 08:34:11",
 12. "2008-08-26 08:34:29",
 13. 46,
 14. 18,
 15. "ANSWERED",
 16. "DOCUMENTATION"

Em outras palavras, teriamos:

accountcode,src,dst,dcontext,clid,channel,dstchannel,lastapp,lastdata,start,answer,end,duration,billsec,disposition,amaflags

Agora que já conhecemos a estrutura dos logs, podemos criar nossa tabela.

debian:~# su - postgres
 No directory, logging in with HOME=/
 [email protected]:/$ psql asterisk
 Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

 Type: \copyright for distribution terms
 \h for help with SQL commands
 \? for help with psql commands
 \g or terminate with semicolon to execute query
 \q to quit

 asterisk=# CREATE TABLE "cdr" (
 "accountcode" varchar(20) NOT NULL default '',
 "src" varchar(80) NOT NULL default '',
 "dst" varchar(80) NOT NULL default '',
 "dcontext" varchar(80) NOT NULL default '',
 "clid" varchar(80) NOT NULL default '',
 "channel" varchar(80) NOT NULL default '',
 "dstchannel" varchar(80) NOT NULL default '',
 "lastapp" varchar(80) NOT NULL default '',
 "lastdata" varchar(80) NOT NULL default '',
 "callstartdate" timestamp NOT NULL default '1970-01-01 00:00:00',
 "callanswerdate" timestamp NOT NULL default '1970-01-01 00:00:00',
 "callenddate" timestamp NOT NULL default '1970-01-01 00:00:00',
 "duration" int NOT NULL default '0',
 "billsec" int NOT NULL default '0',
 "disposition" varchar(45) NOT NULL default '',
 "amaflags" varchar(45) NOT NULL default ''
 ); 

Este é o nosso arquivo Master.csv

"","101","11112222","internal","""flavio.torres"" <101>","SIP/flavio.torres-081de478","SIP/TMais-b6f0ac30","Hangup","","2008-11-25 10:16:08",,"2008-11-25 10:16:09",1,0,"NO ANSWER","DOCUMENTATION"
 "","101","11112222","internal","""flavio.torres"" <101>","SIP/flavio.torres-081e62d8","SIP/TMais-081ff048","Hangup","","2008-11-25 10:16:21",,"2008-11-25 10:16:22",1,0,"NO ANSWER","DOCUMENTATION"
 "","101","11112222","internal","""flavio.torres"" <101>","SIP/flavio.torres-081bfe50","SIP/TMais-081d07e0","Dial","[email protected]|45|Tt","2008-11-25 10:16:59",,"2008-11-25 10:17:02",3,0,"NO ANSWER","DOCUMENTATION"
 "","101","01111112222","internal","""flavio.torres"" <101>","SIP/flavio.torres-0816e618","SIP/TMais-081c4c58","Dial","[email protected]|45|Tt","2008-11-25 10:18:02",,"2008-11-25 10:18:18",16,0,"NO ANSWER","DOCUMENTATION"
 "","101","11112222","internal","""flavio.torres"" <101>","SIP/flavio.torres-0816e618","SIP/TMais-081de348","Dial","[email protected]|45|Tt","2008-11-25 10:18:26",,"2008-11-25 10:18:29",3,0,"NO ANSWER","DOCUMENTATION"
 "","102","22221111","internal","""flavio.torres"" <102>","SIP/flavio.torres-0816e618","SIP/TMais-081c8408","Dial","[email protected]|45|Tt","2008-11-25 16:01:49","2008-11-25 16:01:57","2008-11-25 16:02:46",57,49,"ANSWERED","DOCUMENTATION"

Este é o nosso script que fará a leitura do arquivo e importará para o banco:

#!/bin/bash
FILE=$1
sed -i "s#\"#\'#g" ${FILE}
IFS=","
while read ACCOUNTCODE SRC DST DCONTEXT CLID CHANNEL DSTCHANNEL LASTAPP LASTDATA START ANSWER END DURATION BILLSEC DISPOSITION AMAFLAGS;do
        ${ANSWER:="'1970-01-01 00:00:00'"}
        echo "INSERT INTO cdr VALUES ($ACCOUNTCODE, $SRC, $DST, $DCONTEXT, $CLID, $CHANNEL, $DSTCHANNEL, $LASTAPP, $LASTDATA, $START, $ANSWER, \
	$END, $DURATION, $BILLSEC, $DISPOSITION, $AMAFLAGS)" |  psql -U postgres -f- asterisk
done < $FILE

Agora, basta executá-lo assim:

bash LoadMaster.sh Master.csv

É isso aí, agora basta criar uma página ‘php’ que realize a extração dos dados e multiplique o campo BILLSEC por uma taxa da operadora.