[Asterisk-Users] AGI Script to interact with ACCESS Databse a nd Set CID info on the fly.

[Colin Anderson]

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: 
 
1. http://sourceforge.net/projects/odbcsock
<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
 
 

-----Original Message-----
From: Tim King [mailto:tim at compnetwork.net]
Sent: Thursday, September 22, 2005 10:52 AM
To: asterisk-users at lists.digium.com
Subject: [Asterisk-Users] AGI Script to interact with ACCESS Databse and Set
CID info on the fly.



Well guys here comes the fun part. I have a Microsoft access (VBA)
application that interfaces with my SQL database. This app pulls of info
from the SQL record and than picks up the phone and dials that locations
number. I have purchased a few hundred NpaNxx's for my own use. I want get
into too much detail there but no worries this is legal. I need to change my
CID info on the fly. So I am thinking it should be easy to make an AGI
script that just sets the CID info on a particular line using two variables
being passed to it $Line_No to tell it what line to set and than $CID to be
the number to set on that extension for that call. It also should be
relatively simple to have the access app take a look at the area code and
phone number for the location being called and pull a phone number from the
NUMBERS table which has all of my numbers in it and pass that over. The real
question is how do we get Access to speak to an AGI script. Has anyone done
anything like this? Thanks a lot for reading but this will be a fun one. 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-users/attachments/20050922/10c45047/attachment.htm