Integrate Ruby and Asterisk (call detail record) using ActiveRecord

First of all, I would like to let you know I’m working with Asterisk PBX again (not enough, but that’s ok).

As far we know, Asterisk can store entire call logs in a database – MySQL at this point. So, let we have some fun with Ruby and Asterisk and Mysql too, and all we want is: retrieve all calls’ time, in seconds, just to know how much time we spent on phone.

Let’s rock

I will not cover any installation (asterisk, mysql, ruby, rails, etc).

I have a Trixbox running and it stores cdr in MySQL (I think it’s part of FreePBX). Let’s rock:

mysql>  SELECT SUM(duration)
    ->  FROM cdr
    ->  WHERE (
    ->  (dcontext="from-internal" AND dstchannel like "SIP/voip-gw1%")
    ->  AND (calldate BETWEEN '2008-07-01%' AND  '2008-07-30%')
    ->  AND (disposition="ANSWERED")
    ->  )
    ->  GROUP BY dcontext;
+---------------+
| SUM(duration) |
+---------------+
|         90845 |
+---------------+
1 row in set (0.00 sec)

Explaining: we just care about answered calls made in period (between 2008-07-01 and 2008-07-30) via VOIP-GW1 (a trunk which give us N free minutes to talk per month).

Rubyfying

database.yml

We’ll use ActiveRecord as I said. First, we need to setup our database connection, so write a database.yml (like rails’ proj/config/database.yml):

  adapter: mysql
  database: asterisk
  username: asterisk
  password: asterisk
  host: 127.0.0.1
  port: 3306

Running it

#!/usr/bin/env ruby

require 'yaml'
require 'rubygems'
require 'active_record'

database = YAML::load(File.open('database.yml'))
ActiveRecord::Base.establish_connection(database)

class CallReport < ActiveRecord::Base
    set_table_name 'cdr'
    set_primary_key 'uniqueid'

    def self.used_minutes(startdate,finaldate)
      @context = 'from-internal'
      @dstchannel = 'SIP/voip-gw1%'
      @firstdate = "#{startdate}%"
      @lastdate = "#{finaldate}%"
      @disposition = 'ANSWERED'

      @minutes = CallReport.find(:all,
                                  :select => '*, SUM(duration) as total',
                                  :group => 'dcontext',
                                  :conditions => [
                                  'dcontext = ? AND dstchannel like ? AND calldate BETWEEN ? and ? AND DISPOSITION = ?',
                                   @context,@dstchannel,@firstdate,@lastdate,@disposition])
      @minutes.each do |minute|
        return minute.total
      end

    end

end

total_time = CallReport.used_minutes('2008-07-01','2008-07-10')
p total_time

Result

rocha@vorheez:~/devel/ruby/prepaid$ ruby main.rb
"23649"
rocha@vorheez:~/devel/ruby/prepaid$

Posts relacionados:

1. Checar DNS de domínio em 5 linhas de código (Ruby way!)
2. How-To: Install Ruby on Rails (OpenBSD 4.2, two steps)
3. Portabilidade & Asterisk: ligacoes para mesma operadora
4. Ruby, EU TE AMO!!!
5. Universal Ruby Whois @ GitHub