GoTalk Limit Check

Introduction

This process checks for the number of calls and the number of minutes that have been used to GoTalk. If they exceed the default 100 calls and 500 minutes then the call is placed over another VSP. By default the failover is to Pennytel as they are the cheapest mobile phone provider with per second billing.

extensions.ael

/etc/asterisk/extensions.ael

//04 Mobile Numbers
context mobile-count
          {
           _04XXXXXXXX =>
                 {
                     day=${STRFTIME(,,%d)};

                     if ( ${day} >= 5 )
                        {
                          start=${STRFTIME(,,%Y)}${STRFTIME(,,%m)}05000000;
                          end=${STRFTIME(,,%Y)}${STRFTIME(,,%m)}${day}235959;
                         } else
                            {
                               // If current day is before billing period in month use previous month
                               if ( ${day} >= 1 & ${day} <= 4 )
                                 {
                                    month=${STRFTIME(,,%m)}-1;
                                    start=${STRFTIME(,,%Y)}${month}05000000;
                                    end=${STRFTIME(,,%Y)}${STRFTIME(,,%m)}${day}235959;
                                 }
                            }

                      // If January use previous month and year.
                      if ( ${STRFTIME(,,%m)} == 1 &  ${day} >= 1 & ${day} <= 4 )
                         {
                            year=${STRFTIME(,,%Y)}-1;
                            month=12;
                            start=${year}${month}05000000;
                         }

                     AGI(GoTalk.php,${start},${end});
                     NoOp(${calls} calls and ${minutes} minutes);


                     if ( ${calls} > 100 | ${minutes} > 500 )
                        {
                          Playback(beep&beep);
                          PlayBack(announce/connection-to&announce/GoTalk&announce/failed-trying);
                          PlayBack(announce/PennyTel);
                          Dial(SIP/PennyTel/${EXTEN},${ringer});
                          HangUp();
                        }
                          else Goto(mobiles,${EXTEN},1);

                };
          };

GoTalk.php

Create the file in the folder /var/lib/asterisk/agi-bin

#!/usr/bin/php
<?php

require 'phpagi.php';
$agi = new AGI();

//Billing period start day.
$start = '5';

$mysql_host = 'localhost';
$mysql_user = 'cdruser';
$mysql_password = 'password';
$my_database = 'asteriskcdr';
$dbtable = 'cdr';


$from_date = $argv[1];
$to_date = $argv[2];


$callnum = '0';
$minutes = '0';

$query_duration = "SELECT billsec FROM $dbtable WHERE calldate >= $from_date AND calldate <= $to_date AND disposition='ANSWERED' AND dst like '04%'";
$query_calls = "SELECT COUNT(dst) FROM $dbtable WHERE calldate >= $from_date AND calldate <= $to_date AND disposition='ANSWERED' AND dst like '04%'";
$query_cb_calls = "SELECT COUNT(dst) FROM $dbtable WHERE calldate >= $from_date AND calldate <= $to_date AND disposition='ANSWERED' AND dst like '04%' AND src like '04%'";

// Connecting, selecting database
$link = mysql_connect($mysql_host, $mysql_user, $mysql_password)
    or die('Could not connect: ' . mysql_error());

mysql_select_db($my_database) or die('Could not select database');

// Performing SQL query
$result = mysql_query($query_calls) or die('Query failed: ' . mysql_error());

// Printing results in HTML
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($line as $col_value) {
        $callnum = $col_value;
    }
}



// Performing SQL query
$result = mysql_query($query_cb_calls) or die('Query failed: ' . mysql_error());

// Printing results in HTML
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($line as $col_value) {
        $callnum = $callnum + $col_value;    }
}



// Performing SQL query
$result = mysql_query($query_duration) or die('Query failed: ' . mysql_error());

// Printing results in HTML
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($line as $col_value)
      {
          $point = substr(round($col_value/60,1),-2,1);
          $deci = substr(round($col_value/60,1),-1);

          if ( $point == '.' & $deci <= 4 )
            $minutes += round(($col_value/60)+.49); else $minutes += round($col_value/60);
      }
}


// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);

$agi->set_variable("calls", $callnum);
$agi->set_variable("minutes", $minutes);

?>

Pre-Requisite

To run a php AGI script you need to have three files phpagi-asmanager.php, phpagi-fastagi.php and phpagi.php. The files can be downloaded from sourceforge and be extracted to /var/lib/asterisk/agi-bin