CRM/other Database Dips

FreePBX Tips and Tricks
1

Hi,

We had an application where our customer support group wanted to route inbound support calls to the proper queues based some external criteria. In some cases it was because they bought a premium support package (faster call answering) or maybe they had a specific solution that needed to go to an advanced support group. Whichever, the desire was to keep the customer support phone number the same but deliver their calls to queues based on some criteria.

In this example the support queue selected is based on values in the customer table in CRM. The customer is required to enter their customer ID (given to them at install) when prompted. If they pound-out of this or don’t know their customer ID they are dropped to the slowest response queue.

A context is added in extensions_custom.conf that looks like this:

[getcustid-custom]
exten => s,1,Noop(Get Customer ID)
exten => s,n,Set(attempt=0)
exten => s,n(begin),Noop(Get Customer ID Attempt: ${attempt})
exten => s,n,Read(get,“custom/CS-EnterCustomerID&beep”,7,15)
; use the default timeout
;exten => s,n,Gotoif($[ “${LEN(${get})}” < “5”]?begin)
exten => s,n,GotoIf(${get}?done)
exten => s,n,Set(attempt=$[ ${attempt}+1 ])
exten => s,n,GotoIf($[ ${attempt}>1 ]?done:begin)
exten => s,n(done),NoOp(User Entered: ${get})
exten => s,n,Macro(prepend-cid,${get}:slight_smile:
exten => s,n,AGI(getcustid.agi,${get})
exten => s,n,Noop(Database Says: ${CS-Dest})
; this is just to keep from annoying people while testing
;exten => s,n,SayDigits(${CS-Dest})
;exten => s,n,Macro(hangupcall,)
;; if destqueue is ‘’ the database lookup failed
;; we’ll default to queue 431053 in this case
exten => s,n,GotoIf($["${CS-Dest}" = “”]?ext-queues,431053,1)
exten => s,n,Goto(${CS-Dest})

To tie this context into FreePBX, jump to Admin->Custom Destinations and click Add Custom Destination

Enter values:
Custom Destination: getcustid-custom,s,1
Description: GetCustomerID

Then Submit Changes.

The AGI script in this case, simplified for this example, could look something like this:

#!/usr/bin/php -q

<? require 'phpagi.php'; $agi = new AGI(); $no=preg_replace("#[^0-9]#","",$agi->request['agi_platinum']);//remove any non numeric characters $no=$argv[1]; $dbhost = 'localhost'; $db = 'TheDatabase'; $dbuser = 'TheUser'; $dbpass = 'ThePassword'; // default action $dest='app-announcement-22,s,1'; // Dispatch after warning mysql_connect($dbhost,$dbuser,$dbpass); mysql_select_db("$db"); //or die("could not open database"); $row=mysql_query("SELECT queue FROM cs_values WHERE id = $no LIMIT 1"); if (mysql_num_rows($row)==1){ $row=mysql_fetch_array($row); if ($row['queue'] == "1") $dest = "ext-queues,431057,1"; // Partner if ($row['queue'] == "2") $dest = "ext-queues,431056,1"; // Premier if ($row['queue'] == "3") $dest = "ext-queues,431053,1"; // Dispatch if ($row['queue'] == "8") $dest = "ext-queues,431099,1"; // Dispatch } $agi->set_variable("CS-Dest", $dest); /* CREATE TABLE `platinum` ( `id` int(11) NOT NULL, `queue` varchar(120) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `platinum_id_i` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 */ ?>

Note that in the example the app-announcement and ext-queues will all mean nothing in your environment and you will need to change those to something meaningful in your deployment.

With all that, pick your Custom Destination inside your IVR, Inbound Route or wherever and and you’re now doing database dips to determine the routing.

2

Note that I imply paths to the greetings in the dialplan. You will need your own recordings and should update the dialplan to point to your recordings.