Asterisk queue callback

SQL Table Structure

If using the dialplan below, put the following table into a database called ‘acd‘.

CREATE TABLE `bit_callers` (
`uniqueid` varchar(15) NOT NULL default ”,
`callback` int(3) NOT NULL default ‘0’,
`callbacknum` varchar(15) NOT NULL default ”,
PRIMARY KEY (`uniqueid`),
KEY `callback` (`callback`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Dialplan Additions

[support-queue]
exten => s,1,Answer
exten => s,n,Wait(1)
exten => s,n,MYSQL(Connect connid 127.0.0.1 acd acdpass acd)
exten => s,n,MYSQL(Query r ${connid} INSERT INTO callers set uniqueid=${UNIQUEID})
exten => s,n,MYSQL(Disconnect ${connid})
exten => s,n,Queue(support|t)

exten => 1,1,Read(CALLBACKNUM|beep|10) ; This is where you request the CallBack Number from your customer. Put your own prompts or whatever here.
exten => 1,n,MYSQL(Connect connid 127.0.0.1 acd acdpass acd)
exten => 1,n,MYSQL(Query r ${connid} UPDATE callers SET callback=1,callbacknum=${CALLBACKNUM} WHERE uniqueid=${UNIQUEID})
exten => 1,n,MYSQL(Disconnect ${connid})
exten => 1,n,Playback(goodbye)
exten => 1,n,Hangup

exten => h,1,MYSQL(Connect connid 127.0.0.1 acd acdpass acd)
exten => h,n,MYSQL(Query r ${connid} DELETE FROM callers where uniqueid=${UNIQUEID} AND callback=0)
exten => h,n,MYSQL(Disconnect ${connid})

[callback]
exten => s,1,Set(QUEUE_PRIO=10)
exten => s,n,Goto(support-queue,s,2)

Perl Daemon (callbackd)

1. !/usr/bin/perl

use strict;
use DBI;
use POSIX;

my $calldir = “/var/spool/asterisk/outgoing”;
my $sleepsec = 20; # How often do we check for callbacks?
my $outchan = “Zap/G2”; # What channel do we call our customers back on?
my $dbhost = “localhost”;
my $dbuser = “acd”;
my $dbpass = “acdpass”;
my $dbname = “acd”;

$| = 1; # Flush stdout

print “Daemonizing…n”;
&daemonize;

my $dbh = DBI->connect(“dbi:mysql:$dbname:$dbhost”,”$dbuser”,”$dbpass”)
or die $DBI::errstr;

while (1) { # begin main loop

sleep($sleepsec);

my $q1 = “SELECT uniqueid,callbacknum FROM callers ” .
“ORDER BY uniqueid LIMIT 1”;

my $row = $dbh->selectrow_hashref( $q1 );

if (!$row->{callbacknum}) { next; }

open CALLFILE, “>>/tmp/cb$row->{uniqueid}.call”;
print CALLFILE “Channel: $outchan/$row->{callbacknum}”;
print CALLFILE “MaxRetries: 3n”;
print CALLFILE “RetryTime: 60n”;
print CALLFILE “WaitTime: 30n”;
print CALLFILE “Context: callbackn”;
print CALLFILE “Extension: sn”;
print CALLFILE “Priority: 1n”;
close CALLFILE;

$dbh->do(“DELETE FROM callers WHERE uniqueid=$row->{uniqueid}”);
rename(“/tmp/cb$row->{uniqueid}.call”, “$calldir/$row->{uniqueid}.call”);

} # end main loop

sub daemonize {

chdir ‘/’ or die “Can’t chdir to /: $!”;
open STDIN, ‘/dev/null’ or die “Can’t read /dev/null: $!”;
open STDOUT, ‘>>/dev/null’ or die “Can’t write to /dev/null: $!”;
open STDERR, ‘>>/dev/null’ or die “Can’t write to /dev/null: $!”;
defined(my $pid = fork) or die “Can’t fork: $!”;
exit if $pid;
setsid or die “Can’t start a new session: $!”;
umask 0;

}

Expanded Queue Callback

Here is an expanded queue callback routine. Multiple named queues are supported, callback numbers are checked against a blacklist which is stored in a MySQL database. The number of calls allowed into all queues are set by a global variable as well as variable that sets the minimum number of calls that must be in all queues before callers hear a message telling of the callback function. We are using this on a Trixbox installation so if you have a system developed from scratch – some adjustments will be needed. This code is built on Tyler’s posting above. This code has be tested but not to the point that we have put it into production, yet. Functionality is there but there may be a bug or two laying around. I welcome suggestions on how to further enhance it.

SQL Table Structure

Three tables are used. Blacklist to hold the phone numbers you want to prevent being entered as a callback number (911 and 311 are here as defaults). Callers which is a list of all calls in all queues. Since we have several queues, we give them text names so both the name of the queue and the queueid (the number a called presses when selecting a queue) is stored. The callback Perl script uses this information to make sure that when a callback occurs it is placed into the proper queue. The Perl script below uses the queuename table to get the callback into the proper queue.

Like Tyler these tables have been put into a database called acd. The database acd has a user acd with a password of acd (change these for production). You will have to give the user acd select, insert, create, delte, and update privileges at a minimum to the database.

CREATE TABLE `bit_blacklist` (
  `uniqueid` int(11) NOT NULL auto_increment,
  `callbacknum` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`uniqueid`),
  KEY `callbacknum` (`callbacknum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Collection of numbers not allowed for customer callback' AUTO_INCREMENT=8 ;

INSERT INTO `bit_blacklist` (`uniqueid`, `callbacknum`) VALUES (1, '311'),
(2, '911'),

CREATE TABLE `bit_callers` (
  `uniqueid` varchar(15) NOT NULL default '',
  `callback` int(3) NOT NULL default '0',
  `callbacknum` varchar(15) NOT NULL default '',
  `queueid` smallint(6) NOT NULL default '8',
  `queuename` varchar(20) default NULL,
  PRIMARY KEY  (`uniqueid`),
  KEY `callback` (`callback`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `bit_queuename` (
  `id` int(11) NOT NULL auto_increment,
  `queuename` varchar(20) NOT NULL default '',
  UNIQUE KEY `id` (`id`),
  KEY `queuename` (`queuename`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 

Dialplan Additions

If you are using Trixbox two variables should be set in globals_custom.conf – QUEUESIZE,CALLBACKANNOUNCETHRES to set the total number of calls you will allow in all queues and how many calls must be in the queues before a caller hears about this option. A custom announcement must be recorded and placed in /var/lib/asterisk/sounds/custom/queue-callback-msg. With Trixbox we put these dial plan additions into the file /etc/asterisk/extensions_custom.conf.

Additionally the code allows only either 7 or 10 digit callback numbers to be entered. It presently only allows one area code (210 – yes, we are in San Antono, Texas). You will need to change the code at label TenDigits for your area code(s).

[ext-queues-custom]

exten => 1,1,Answer 
exten => 1,n,Wait(1)
exten => 1,n,Set(QUEUEID=1)
exten => 1,n,Set(QUEUENAME=Spanish) 
exten => 1,n,MYSQL(Connect connid 127.0.0.1 acd acd acd) 
exten => 1,n,GotoIf($[${QUEUE_PRIO}=10]?EnterQueue)
exten => 1,n,MYSQL(Query r1 ${connid} SELECT COUNT(*) FROM callers) 
exten => 1,n,MYSQL(Fetch fetchid ${r1} COUNT)
exten => 1,n,MYSQL(Clear ${r1}) 
exten => 1,n,GotoIf($[${COUNT} >= ${QUEUESIZE}]?Queuevm) 
exten => 1,n,GoToIf($[${COUNT} >= ${CALLBACKANNOUNCETHRES}]?:EnterQueue)
exten => 1,n,Playback(custom/queue-callback-msg)
exten => 1,n(EnterQueue),MYSQL(Query r ${connid} INSERT INTO callers set uniqueid='${UNIQUEID}',queueid='${QUEUEID}',queuename='${QUEUENAME}') 
exten => 1,n,MYSQL(Disconnect ${connid}) 
exten => 1,n,Queue(${QUEUENAME}|tT)
exten => 1,n(Queuevm),Goto(ext-local,${VM_PREFIX}5857,1)

[set-callback]
exten => 9,1,Playback(after-the-tone)
exten => 9,n,Playback(enter-phone-number10)
exten => 9,n,Read(CALLBACKNUM|beep|10||2|5) ; This is where you request the CallBack Number from your customer. Put your own prompts or whatever here. 
exten => 9,n,GotoIf($["${LEN(${CALLBACKNUM})}"="10"|"${LEN(${CALLBACKNUM})}"="7"]?GoodNumDigit)
exten => 9,n,Playback(wrong-try-again-smarty)
exten => 9,n,GoTo(1)
exten => 9,n(GoodNumDigit),MYSQL(Connect connid 127.0.0.1 acd acd acd) 
exten => 9,n,MYSQL(Query r ${connid} SELECT callbacknum FROM blacklist WHERE callbacknum=${CALLBACKNUM})
exten => 9,n,MYSQL(Fetch f ${r} CALLBACKNUM) 
exten => 9,n,GotoIf($["${f}"="1"]?:GoodNum)
exten => 9,n,Playback(you-dialed-wrong-number)
exten => 9,n,Playback(pls-try-again)
exten => 9,n,GoTo(1) 
exten => 9,n(GoodNum),Playback(you-entered)
exten => 9,n,NoOp(${CALLBACKNUM})
exten => 9,n,NoOp(${CALLERID})
exten => 9,n,SayDigits(${CALLBACKNUM})
exten => 9,n,Wait(1)
exten => 9,n,Playback(after-the-tone)
exten => 9,n,Playback(if-this-is-correct)
exten => 9,n,Playback(press-1)
exten => 9,n,Playback(if-this-is-not-correct)
exten => 9,n,Playback(press-2)
exten => 9,n,Read(CORRECT|beep|1||2|5)
exten => 9,n,GotoIf($["${CORRECT}"="1"]?:1)
exten => 9,n,NoOp(${LEN(${CALLBACKNUM})})
exten => 9,n,NoOp(${CALLBACKNUM})
exten => 9,n,GotoIf($["${LEN(${CALLBACKNUM})}"="10"]?TenDigits)
exten => 9,n(Add9),Set(CALLBACKNUM=9${CALLBACKNUM})
exten => 9,n,GoTo(StoreNum)
exten => 9,n(TenDigits),GoToIf($["${CALLBACKNUM:0:3}"="210"]?:Not210)
exten => 9,n,Set(CALLBACKNUM=${CALLBACKNUM:3:7})
exten => 9,n,GoTo(Add9)
exten => 9,n(Not210),Set(CALLBACKNUM=91${CALLBACKNUM})
exten => 9,n(StoreNum),MYSQL(Query r ${connid} UPDATE callers SET callback=1,callbacknum='${CALLBACKNUM}',queueid='${queueid}' WHERE uniqueid=${UNIQUEID})
exten => 9,n,MYSQL(Disconnect ${connid})
exten => 9,n,NoOp(${CALLBACKNUM})
exten => 9,n,Playback(goodbye) 
exten => 9,n,Hangup

exten => h,1,MYSQL(Connect connid 127.0.0.1 acd acd acd) 
exten => h,n,MYSQL(Query r ${connid} DELETE FROM callers where uniqueid=${UNIQUEID} AND callback=0) 
exten => h,n,MYSQL(Disconnect ${connid})

[callback]
exten => s,1,Set(QUEUE_PRIO=10) 
exten => s,n,Goto(ext-queues-custom,${queueid},1)

Queue Example

An example of one of our queues, placed in /etc/asterisk/queues_custom.conf. You may need to tweek these parameters to fit your requirements.

[Spanish]
wrapuptime=0
timeout=15
strategy=rrmemory
retry=5
queue-youarenext=queue-youarenext
queue-thereare=queue-thereare
queue-thankyou=queue-thankyou
queue-callswaiting=queue-callswaiting
music=default
member=Agent/1036
monitor-join=yes
monitor-format=
maxlen=0
leavewhenempty=no
joinempty=Yes
context=set-callback
announce-holdtime=yes
announce-frequency=30
servicelevel=30
eventwhencalled=yes
eventmemberstatus=yes

Perl Script

This is a slightly modified version of the Perl script posted by Tyler. It has been modified so that the callback will be placed into the proper queue. You will need to modify the outchan parameter to suit your system.

#!/usr/bin/perl
use strict;
use DBI;
use POSIX;

my $calldir =   "/var/spool/asterisk/outgoing";
my $sleepsec =  20;     # How often do we check for callbacks?
my $outchan = "IAX2/priv";  # What channel do we call our customers back on?
my $dbhost =    "localhost";
my $dbuser =    "acd";
my $dbpass =    "acd";
my $dbname =    "acd";

##############################################################################

       $| = 1;  # Flush stdout

       print "Daemonizing...n";
       &daemonize;

       my $dbh = DBI->connect("dbi:mysql:$dbname:$dbhost","$dbuser","$dbpass")
               or die $DBI::errstr;

        my $qql = "SELECT queuename FROM queuename";
        my $aryRef = $dbh->selectall_arrayref($qql);
        my @queuename = map { $aryRef->[$_][0]} 0..$#{$aryRef};

        while (1) {  # begin main loop

               sleep($sleepsec);
                foreach my $queue (@queuename ) {       # check each queue

                        my $q1 =        "SELECT uniqueid,callbacknum,queueid FROM callers WHERE queuename='".$queue."' ".
                                         "ORDER BY uniqueid LIMIT 1";
                        my $row = $dbh->selectrow_hashref( $q1 );

                        if (!$row->{callbacknum}) { next; }

                        open CALLFILE, "+>>/tmp/cb$row->{uniqueid}.call";
                        print CALLFILE "Channel: $outchan/$row->{callbacknum}n";
                        print CALLFILE "MaxRetries: 3n";
                        print CALLFILE "RetryTime: 60n";
                        print CALLFILE "WaitTime: 30n";
                        print CALLFILE "Context: callbackn";
                        print CALLFILE "Extension: sn";
                        print CALLFILE "Priority: 1n";
                        print CALLFILE "Set: queueid=$row->{queueid}n";
                        close CALLFILE;

                        $dbh->do("DELETE FROM callers WHERE uniqueid=$row->{uniqueid}");
                        rename("/tmp/cb$row->{uniqueid}.call", "$calldir/$row->{uniqueid}.call");
                }

         }  # end main loop

##############################################################################

sub daemonize {

   chdir '/'                 or die "Can't chdir to /: $!";
   open STDIN, '/dev/null'   or die "Can't read /dev/null: $!";
   open STDOUT, '>>/dev/null' or die "Can't write to /dev/null: $!";
   open STDERR, '>>/dev/null' or die "Can't write to /dev/null: $!";
   defined(my $pid = fork)   or die "Can't fork: $!";
   exit if $pid;
   setsid                    or die "Can't start a new session: $!";
   umask 0;

}

Posts to Review:

astrerisk dialplan configuration file – extensions.conf vicidial / extras / MySQL_AST_CREATE_tables.sql Asterisk cmd MYSQL Asterisk DialStatus Channel Varriable