asterisk users - Sep 2005 - HOWTO: A simple AGI application to modify incomi ng CallerID on the fly using SQL Server and *not* UnixODBC

Requirements: 

1. http://sourceforge.net/projects/odbcsock

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

This was suprisingly easy to do and added ~ 1/2 a second to the inbound leg
of the call before dialling the extension, this is against a database
with
>50K records. Since there are undoubtedly AGI newbies on the list like me, I
wanted to share it to show how relatively easy it is to do some cool stuff
in Asterisk.