This guide is intended to help show you how to create a database driven call routing system to enable calls to be intelligently routed through your system directly to the appropriate agents/groups, in addition this guide will also show you how to update each call so it caries a special URL taking the answering agent straight to the information they need by poping up a web page with all the customer information when they answer the call.
This kind of functionality would cost you tens of thousands in any other system! ;o)
Firstly before we start, this is a rough guide and I am sorry for any errors you may spot.
It is not the best code in the world, and only forms a proof of concept for those interested in database driven call routing and CRM web popping.
Asside; the existing 'Web Launcher' component of HUD pro works by inspecting the channel variables for the existence of the special 'caller_number' and 'caller_name' variables. These are pre-populated special variables which are setup by Asterisk every time a call comes in.
HUD-pro uses these variables by allowing you to form a URL inserting these variables where ever desired.
Allthough very simple to get going, this setup is extremely limited. The only information you have available to you is two rather simple bits of information and you have to configure the same information on every instance of HUD installed across your network.
For example in HUD-pro you would program the following;
http://crm.yourdomain.com/index.php?action=UnifiedSearch&module=H...
or
http://crm.yourdomain.com/index.php?action=UnifiedSearch&module=H...
As you can see you have only two pieces of information to work with.
To bring out the true power of the Web Launching feature for CRM integration requires the URL to be defined server side and not client side, that is to say instead of only having the 'caller_id' or the 'caller_name' from the active channel, to instead have HUD open a browser by reading a single special channel variable called 'URL' populated server side by the PABX which has access to a unlimited information!
This URL can be set by the special asterisk function SendURL('') executed server side during the dial plan.
Because this can be done server side, it also makes provisioning the HUD clients easier as you would only need to simply enable 'URL' Web Launching.
Tie all this in with Database driven call routing and you have individual calls being routed through your system to agents, with each call taking the answering agent straight to the information they need.
So lets get into it then;
Intro)
In the example code below we ask the user for a Ticket Number/Bug number (The ticket number generated by SugarCRM or other CRM when caller previously raised a bug/support call etc), this ticket number is then passed to the database to get the extension number of the agent who the ticket is currently assigned to, then we dial only that single agent and form a URL to open the specific ticket on his/her machine.
NB: Asside HUD currently does not support SendURL(). There are others out there that do, but which in my opionion will never be quite as good as HUD simply due to HUD being part of trixbox itself (As far as my managers are concerned anyway!).
However it has been said that HUD3 does support the URL channel variable. Allthough it is unknown whether this sill also be in HUDlite3. Lets hope it is :o)
Step 1) - Program the IVR to route to your new custom App.
Setup FreePBX to route to your new custom app that we are going to create;
Go to 'Tools', 'Custom Destination'. Set the text 'custom-enterTicketNo,s,1' in the 'Custom Destination' field, give it a name in the 'Description' field.
Now in an IVR you can program an Option to go to the newly created Custom Destination by selecting the name you gave it.
Step 2) - Add the Dial Plan code.
Place the following section at the bottom of extensions_custom.conf;
--START CODE
[custom-enterTicketNo]
include => ext-findmefollow
include => ext-local
#exten => s,1,DigitTimeout(5) ;Set Digit time out 4 secs
#exten => s,n,ResponseTimeout(3) ;Set Response timeout 5 secs
exten => s,1,Read(TicketNo,entercallno,5,skip,1,4) ;Play voice recording 'entercallno' and save keypad presses to the variable 'TicketNo'. You can record any voice prompt and place in /var/lib/asterisk/sounds/custom, or you can upload the .wav file using FreePBX in the System Recordings section.
exten => s,n,NoOp(${TicketNo}) ;Print variable contacts the the debug CLI
exten => s,n,Set(CDR(acountcode)=${TicketNo}) ;Set the special variable 'Account' to be the same as TicketNo (This gets stored in the CDR)
exten => s,n,Set(AgentExt=301) ;Set the variable AgentExt initially to the default extension number (Support group extension)
exten => s,n,Set(CRMURL=localhost/crm) ;Set the variable CRMURL initially to be the URL for CRM homepage
exten => s,n,NoOp(${CRMURL})
exten => s,n,AGI(getext.agi|${TicketNo}|${CHANNEL}|${CALLERID(num)}) ;Call the AGI script '/var/lib/asterisk/agi-bin/getext.agi' passing it the 'TicketNo', the 'CHANNEL' variables and the caller id. If successful it will overwrite 'AgentExt' with the extension of a specific agent
exten => s,n,NoOp(I am now going to try to send the call to the Agent)
exten => s,n,Set(FROMCONTEXT=fromTicketNo)
exten => s,n,Set(RT=${IF($[$["${VMBOX}"!="novm"] | $["foo${CFUEXT}"!="foo"]]?${RINGTIMER}:"")})
exten => s,n,NoOp(RT = ${RT})
exten => s,n,NoOp(DIAL_OPTIONS = ${DIAL_OPTIONS})
exten => s,n,Macro(record-enable,${AgentExt},IN)
exten => s,n,SendURL(${CRMURL}) ;Sets the URL variable in the SIP header for URL pop-up feature to 'CRMURL' (Overridden from above declaration by AGI script)
exten => s,n,Macro(dial,${RT},${DIAL_OPTIONS},${AgentExt})
exten => s,n,Set(SV_DIALSTATUS=${DIALSTATUS})
exten => s,n,NoOp(If engineer is unavilaible forward to support group extension)
exten => s,n,GosubIf($[$["${SV_DIALSTATUS}"="NOANSWER"]]?ext-findmefollow,301,1) ; check for CFU in use on no answer
exten => s,n,GosubIf($[$["${SV_DIALSTATUS}"="BUSY"]]?ext-findmefollow,301,1) ; check for CFB in use on busy
exten => s,n,GosubIf($[$["${SV_DIALSTATUS}"="CHANUNAVAIL"]]?ext-findmefollow,301,1) ; check for CFB in use on busy
exten => h,1,Macro(hangupcall)
--END CODE
When testing the above Dial plan, if you find the call is dropping as soon as you hit the line 'Read(TicketNo,entercallno,5,skip,1,4)' it is probably because Asterisk cannot find or is having trouble reading the audio file.
Try replacing the audio file name with one of Asterisk's standard voice prompts as a test like 'Read(TicketNo,press-0,5,skip,1,4)'
NB: To test go to the command line of your trixbox, and run the command 'asterisk -vvvvvvvvr' (The more v's the more verbose it will be). Then dial into the IVR and press your programmed option.
Step 3) - Check prerequisites.
You will need to install some prerequisites first before the following Perl script will run.
You will need to setup your build environment, Run from the command line;
yum install -y rpm-build gcc gcc-c++ cpp automake make kernel-devel perl-DBD-MySQL
NB: You probably wont need most of the above, but you want to definitely check you have perl-DBD-MySQL installed by running 'rpm -q perl-DBD-MySQL'
Now we want to check the Perl modules are installed.
I would advise to install webmin from the trixbox package manager an do it through there instead of using CPAN.
login to http://trixboxip:10000 and then go to 'Others', 'Perl Modules'.
DBI * Should already be installed
asterisk::perl * Should already be installed
DBD::mysql * This module should have been installed as yum package 'perl-DBD-MySQL'
If you find you are having trouble connecting to the database, install phpmyadmin from the trixbox package manager, open 'phpmyadmin' from the 'system' menu in the trixbox web interface.
Click on 'Privileges'. I had to create an 'admin' user with a password as detailed in the Perl script below and set 'localhost' as the host etc.
Step 4) - Setup the AGI script
Run the following commands;
cd /var/lib/asterisk/agi-bin
touch getext.agi
chown asterisk:asterisk getext.agi
chmod 775 getext.agi
Copy the following code into the newly created file '/var/lib/asterisk/agi-bin/getext.agi'
--START CODE
#!/usr/bin/perl -w
#
#
#use strict;
# Don't Cache variables
$|=1;
# Load DBI and DBD modules
use DBI;
# Load Asterisk modules
use Asterisk::AGI;
# Setup Variables to use
my $AGI = new Asterisk::AGI;
my (@return);
# Retrieve variables
my %input = $AGI->ReadParse();
my ($TicketNo, $Channel, $CallerID);
($TicketNo = $ARGV[0]) =~ s/\s+//g;
($Channel = $ARGV[1]) =~ s/\s+//g;
($CallerID = $ARGV[2]) =~ s/\s+//g;
# Print stored values
$AGI->verbose("TicketNo = $TicketNo");
$AGI->verbose("Channel = $Channel");
$AGI->verbose("CallerID = $CallerID");
$AGI->verbose("Connecting to DB");
my $dsn = 'DBI:mysql:sugarcrm:localhost:3306';
my $db_user_name = 'admin';
my $db_password = 'passw0rd';
my ($id, $password);
my $dbh = DBI->connect($dsn, $db_user_name, $db_password)
or die "Can't connect to the DB: $DBI::errstr\n";
if ($DBI::err) {
$AGI->verbose("Connect to DB FAILED");
$AGI->verbose($DBI::errstr);
$AGI->set_variable('AgentExt','301');
exit;
}
$AGI->verbose("Done DBI->Connect OK");
$dbh->do("SET LOCK MODE TO WAIT 10");
# Check if ticket number is at least 1
# There should be some proper validation checking going on here, but i wont worry for this proof of concept code
if(length($TicketNo) >= 1) {
# Execute stored proc passing ticket number to get extension of agent currently assigned to the ticket
$AGI->verbose("Executing sp_getextfromticket");
$dbh->do("CALL sp_getextfromticket(\@OUT,$TicketNo);");
if ($DBI::err) {
$AGI->verbose("Failed to execute proc");
$AGI->verbose($DBI::errstr);
$AGI->set_variable('AgentExt','301');
exit;
}
my $sql = qq#SELECT \@OUT;#;
my $returnhandle = $dbh->prepare($sql);
$returnhandle->execute();
# Store returned extension from DB into channel variable
@return = $returnhandle->fetchrow_array();
$AGI->verbose("Returned extension = $return[0]");
if($return[0] == '') {
$AGI->verbose("Returned Nothing! Probably invalid ticket.");
$AGI->set_variable('AgentExt','301');
} else {
$AGI->set_variable('AgentExt',$return[0]);
}
# Build URL for CRM ticket
if($return[0] != '') {
my $CRMURL = "localhost/crm?call=" . $TicketNo;
$AGI->set_variable('CRMURL',$CRMURL)
}
} else {
# Execute stored proc passing CallerID to get extension of account manager
$AGI->verbose("Executing sp_getextfromcallid");
$dbh->do("CALL sp_getextfromcallid(\@OUT,$CallerID);");
if ($DBI::err) {
$AGI->verbose("Failed to execute proc");
$AGI->verbose($DBI::errstr);
$AGI->set_variable('AgentExt','301');
exit;
}
my $sql = qq#SELECT \@OUT;#;
my $returnhandle = $dbh->prepare($sql);
$returnhandle->execute();
# Store returned extension from DB into channel varible
@return = $returnhandle->fetchrow_array();
$AGI->verbose("Returned extension = $return[0]");
if($return[0] == '') {
$AGI->verbose("Returned Nothing! Probably invalid or unknown callerid.");
$AGI->set_variable('AgentExt','301');
} else {
$AGI->set_variable('AgentExt',$return[0]);
}
# Build URL for CRM ticket
# Execute stored proc passing CallerID to get customer ID for URL to take you to the customers page
$dbh->do("CALL sp_getcustidfromcallid(\@OUT,$CallerID);");
if ($DBI::err) {
$AGI->verbose("Failed to execute proc");
$AGI->verbose($DBI::errstr);
$AGI->set_variable('AgentExt','301');
exit;
}
my $sql = qq#SELECT \@OUT;#;
my $returnhandle = $dbh->prepare($sql);
$returnhandle->execute();
# Use returned customerID to form URL
$AGI->verbose("Returned CustomerID = $return[0]");
if($return[0] == '') {
$AGI->verbose("Returned Nothing! Probably invalid or unknown callerid.");
} else {
my $CRMURL = "localhost/crm?customerid=" . $return[0];
$AGI->set_variable('CRMURL',$CRMURL)
}
}
# Disconnect from DB
$dbh->disconnect;
--END CODE
I am not even going to pretend this is the most elegant code, it's not! And it is probably full of bugs, but hopefully it should be enough to give you a starting point at intelligently routing your calls straight to account managers or bug owners.
Step 5) - Create stored proc's.
I find that the MySQL implementation of stored procedures and functions a little odd in the way you have to do a subsequent select to get your data back after executing the procedure, but then again that is probably just because i am used to Informix maybe.
Anyway, To create a stored proc I suggest you download and install the community edition of SQLYOG if you don't already have a SQL client (http://www.webyog.com)
To add a stored proc using sqlyog, login to the database instance and select your CRM database. Right Click 'Stored Procs', click create, give it a name, and run the following code;
--START CODE
DELIMITER |
create procedure sp_getextfromticket(OUT oExt INT, IN in_Ticket INT)
BEGIN
DECLARE iAgentID VARCHAR(30);
SELECT assigned_user_id
INTO iAgentID
FROM bugs where bug_number = in_Ticket;
IF iAgentID IS NOT NULL THEN
SELECT distinct phone_work
INTO oExt
FROM users where id = iAgentID;
ELSE
SET oExt = 301;
END IF;
END
|
--END CODE
This is just an example stored proc I threw together which should run against a SugarCRM database on MySQL.
To test it I created a bunch of bogus 'bugs' in Sugar, assigning random Sugar users to them, and then added the Asterisk extension number as the phone_work property for each of the users.
When you call in, type in you bug number, and the call gets routed to the agent who the bug is assigned too :)
To test the proc run the following SQL in SQLYOG;
CALL test_procedure(@OUT,1);
select @OUT;
The first line 'CALL test_procedure(@OUT,1);' calls the proc, declares the name '@OUT' for referencing afterwords, and passes in the number '1' for bug case '1'.
The proc first selects the users id from the 'bugs' table where the bug_number matches the passed in number '1'.
It stores the user id into the local variable 'iAgentID' (local to the proc only for the life time of the proc).
It then takes the user id stored in iAgentID, and performs a select on the users table to get the phone_work column where the user id matches.
The ELSE statement says, if you never found a match for bug_number (I.e. if iAgentID IS NULL), then return the extension 301 instead (Which in my case is the support group extension).
The second line 'select @OUT;' calls to display the returned data.
NB: Notice in the Perl script that the 'do' statement is used when calling the proc as it returns no data, and '\@OUT' is used instead of just '@OUT'.
This is because '@' is a special Perl character, and by prepending the '@' with an '\' forces Perl to not interpret the character.
The '$dbh->prepare($sql)' sequence is used when there is data to be returned.
Step 6) - Over to you!
I will have to leave it down to yourselves to work out what needs to be done in the other stored procs etc to hook into your own CRM databases as needed. But in the single proc example above
will give you the extension number of the agent who a SugarCRM 'bug' is assigned to. So when the caller types in their bug number, the call gets routed through the appropriate agent.
In the section '# Build URL for CRM ticket';
I couldn't see a way after a quick look to see how you could form a URL to take you directly to the bug in SugarCRM as the URL uses a variable called 'record' to retrieve the page data.
Instead of record=2991cbf9-50ad-d72c-a28e-48332e2ae364, which references the primary key 'id' on the bugs table, there is probably another secret variable you can pass in the URL string
to refer to the 'bug_number' column.
Example
http://trixboxip/Sugar/index.php?module=Bugs&action=DetailView&re...
This guide has been provided purely to help people program their own database driven call routing.
This code should not be taken and used 'as is' in any form of production environment.