|
lol
just posted this yesterday, it's for any ODBC DSN so Access or SQL or an Excel
spreadsheet, as long as it's set as a DSN. This will work with outgoing Caller
ID as well, it's just how you set it up in your dialplan. If you want I can
email you the .agi since email will undoubtedly mangle the script.
hth.
HOWTO: A simple AGI application to modify incoming CallerID on
the fly using SQL Server (or any ODBC DSN)
Requirements:
2. The
SQL server must be in the same subnet as your * server
Howto:
1.
Install ODBCSocketServer on your SQL server and verify connection with
the included VB COM app from a Windows box. 2.
Decide how you want to transform the Caller ID. In my case, I want to do
a lookup of the CallerID number in SQL server and prepend the Caller ID with a "job number" which is a unique ID we assign to each customer. 3. In
the case of a large database with lots of fields it's a good idea to
create a view in SQL server that has *only* the records you want, then you can filter from there. Note the view name. 4.
Create an ODBC system DSN on the SQL server that points to your SQL
server DB 5.
Modify the following PHP script to your taste. Ensure your PHP.ini in
/etc has error and warning suppression ON or else the AGI will return invalid characters: #!/usr/bin/php -q
<?php class ODBCSocketServer
{
var $sHostName; //name of the host to connect to var $nPort; //port to connect to var $sConnectionString; //connection string to use //function to parse the SQL function ExecSQL($sSQL) { $fToOpen = fsockopen($this->sHostName,
$this->nPort,
&$errno, &$errstr, 30); if (!$fToOpen) { //contruct error string to return $sReturn = "<?xml version=\"1.0\"?>\r\n<result state=\"failure\">\r\n<error>$errstr</error>\r\n</result>\r\n"; } else { //construct XML to send //search and replace HTML chars in SQL first $sSQL = HTMLSpecialChars($sSQL); $sSend = "<?xml version=\"1.0\"?>\r\n<request>\r\n<connectionstring>$this->sConnectionString </connectionstring>\r\n<sql>$sSQL</sql>\r\n</request>\r\n"; //write request fputs($fToOpen, $sSend); //now read response while (!feof($fToOpen)) { $sReturn = $sReturn . fgets($fToOpen, 128); } fclose($fToOpen); } return $sReturn; } }//class //Here is the code that uses this class. First we create the
class
$oTest = new ODBCSocketServer; //Set the Hostname, port, and connection
string
$oTest->sHostName = "192.168.1.17"; $oTest->nPort = 9628; $oTest->sConnectionString = "DSN=intranet;UID=sa;PWD=12345;"; //It
is bad practice to use the SA account; in sane installations you use
a
non-priviledge elevated user //now exec the SQL $sResult = $oTest->ExecSQL("SELECT * FROM AsteriskCallerID where homephonecd like '".$argv[1]."'"); //the
$argv[x] variable array contains any arguments you pass to the script.
The array is // $argv[0] the script itself, $argv[1] the first argument, $argv[2] the second, etc. $p =
xml_parser_create();
xml_parse_into_struct($p, $sResult, $vals, $index); //xml_parse_into_struct takes the returned XML and parses it out into
a
variable array xml_parser_free($p); //clean up
//the
print statement returns your SQL data to Asterisk using the SET
VARIABLE statement. //$vals[x][value] returns the contents of the variable based on it's variable index, you will //have to play with the index to determine which one has the data you want to return print
"SET VARIABLE LANDMARKCID \"".$vals[4][value];
print " ".$vals[2][value].":\""; ?>
6.
Rename this script to transformcallerid.agi and drop it
into
/var/lib/asterisk/agi-bin, & chmod 755 it. 7.
Modify your dialplan with the AGI script called in a distinct context
and
that context returns control to the original context when done. [my-inbound-context-with-did]
'Assumption here is that you have several DID's in this context but
should
be able to work without a DID exten
=> 3078,1,SetVar(CURRENTEXTEN=3078)'Set a variable with the
current
extension being processed exten => 3078,2,Goto(Transform-CallerID,s,1)) exten => 3078,3,DoRegualarDialplanStuffHere [Transform-CallerID]
exten
=> s,1,SetVar(CIDPREFIX=${CALLERIDNUM:3:3})
exten => s,2,SetVar(CIDSUFFIX=${CALLERIDNUM:6:10}) exten => s,3,agi(transformcallerid.agi|${CIDPREFIX}-${CIDSUFFIX}) 'We keep phone numbers in XXX-XXXX format exten => s,4,NoOp(${LANDMARKCID}) 'Display the returned variable for debugging purposes exten => s,5,Gotoif($["${LANDMARKCID}" = " :" ]?6:7) 'If the AGI didn't fond a match in the database exten => s,6,SetVar(LANDMARKCID=UNKNOWN:) 'Prepend the caller ID with UNKNOWN: exten => s,7,SetCallerID(${LANDMARKCID}${CALLERIDNUM:3:10}) 'Otherwise prepend with the returned SQL data exten => s,8,Goto(my-inbound-context-with-did,${CURRENTEXTEN},3) 'Return control to calling context
|
_______________________________________________ --Bandwidth and Colocation sponsored by Easynews.com --
Asterisk-Users mailing list [email protected] http://lists.digium.com/mailman/listinfo/asterisk-users To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
