<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word"><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1476" name=GENERATOR>
<STYLE>@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
COLOR: windowtext; FONT-FAMILY: Arial; mso-style-type: personal-compose
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2><STRONG>HOWTO: A simple AGI application to modify incoming CallerID on
the fly using SQL Server (or any ODBC DSN)</STRONG></FONT></SPAN></DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2>Requirements: </FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>1. <A
href="http://sourceforge.net/projects/odbcsock">http://sourceforge.net/projects/odbcsock</A></FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>2. The
SQL server must be in the same subnet as your * server</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2>Howto:</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>1.
Install ODBCSocketServer on your SQL server and verify connection with<BR>the
included VB COM app from a Windows box.</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>2.
Decide how you want to transform the Caller ID. In my case, I want to do<BR>a
lookup of the CallerID number in SQL server and prepend the Caller ID with<BR>a
"job number" which is a unique ID we assign to each
customer.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>3. In
the case of a large database with lots of fields it's a good idea to<BR>create a
view in SQL server that has *only* the records you want, then you<BR>can filter
from there. Note the view name.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>4.
Create an ODBC system DSN on the SQL server that points to your SQL<BR>server
DB</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>5.
Modify the following PHP script to your taste. Ensure your PHP.ini in<BR>/etc
has error and warning suppression ON or else the AGI will return<BR>invalid
characters:</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2>#!/usr/bin/php -q<BR><?php</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2> class ODBCSocketServer
{<BR> var
$sHostName; //name of the host to connect to<BR> var $nPort; //port
to connect to<BR> var $sConnectionString; //connection string to
use<BR>
<BR> //function to parse the SQL
<BR>
function ExecSQL($sSQL) {</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2> $fToOpen = fsockopen($this->sHostName,
$this->nPort,<BR>&$errno, &$errstr, 30);<BR> if
(!$fToOpen)<BR> {<BR> //contruct error
string to return<BR> $sReturn =
"<?xml<BR>version=\"1.0\"?>\r\n<result<BR>state=\"failure\">\r\n<error>$errstr</error>\r\n</result>\r\n";<BR> }<BR> else<BR> {<BR> //construct
XML to send<BR> //search and replace HTML chars in SQL
first<BR> $sSQL =
HTMLSpecialChars($sSQL);<BR> $sSend =
"<?xml<BR>version=\"1.0\"?>\r\n<request>\r\n<connectionstring>$this->sConnectionString<BR></connectionstring>\r\n<sql>$sSQL</sql>\r\n</request>\r\n";<BR> //write
request <BR> fputs($fToOpen,
$sSend);<BR> //now read
response<BR> while
(!feof($fToOpen))<BR> {<BR> $sReturn
= $sReturn .<BR>fgets($fToOpen,
128);<BR> }<BR> fclose($fToOpen);<BR> }<BR> return
$sReturn;<BR> }<BR>
}//class</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2> //Here is the code that uses this class. First we create the
class<BR> $oTest = new ODBCSocketServer;</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2> //Set the Hostname, port, and connection
string<BR> $oTest->sHostName =
"192.168.1.17";<BR> $oTest->nPort =
9628;<BR> $oTest->sConnectionString =
"DSN=intranet;UID=sa;PWD=12345;";</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>//It
is bad practice to use the SA account; in sane installations you use
a<BR>non-priviledge elevated user<BR> //now exec the SQL<BR> $sResult
= $oTest->ExecSQL("SELECT * FROM AsteriskCallerID where<BR>homephonecd like
'".$argv[1]."'"); </FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>//the
$argv[x] variable array contains any arguments you pass to the script.<BR>The
array is <BR>// $argv[0] the script itself, $argv[1] the first argument,
$argv[2] the<BR>second, etc.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>$p =
xml_parser_create();<BR>xml_parse_into_struct($p, $sResult, $vals,
$index);</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2>//xml_parse_into_struct takes the returned XML and parses it out into
a<BR>variable array</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2>xml_parser_free($p); //clean up</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>//the
print statement returns your SQL data to Asterisk using the SET<BR>VARIABLE
statement. <BR>//$vals[x][value] returns the contents of the variable based on
it's<BR>variable index, you will<BR>//have to play with the index to determine
which one has the data you want<BR>to return</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>print
"SET VARIABLE LANDMARKCID \"".$vals[4][value];<BR>print "
".$vals[2][value].":\"";</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2>?></FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>6.
Rename this script to transformcallerid.agi and drop it
into<BR>/var/lib/asterisk/agi-bin, & chmod 755 it.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>7.
Modify your dialplan with the AGI script called in a distinct context
and<BR>that context returns control to the original context when
done.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2>[my-inbound-context-with-did]</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2>'Assumption here is that you have several DID's in this context but
should<BR>be able to work without a DID</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>exten
=> 3078,1,SetVar(CURRENTEXTEN=3078)'Set a variable with the
current<BR>extension being processed<BR>exten =>
3078,2,Goto(Transform-CallerID,s,1))<BR>exten =>
3078,3,DoRegualarDialplanStuffHere</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2>[Transform-CallerID]</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff size=2>exten
=> s,1,SetVar(CIDPREFIX=${CALLERIDNUM:3:3})<BR>exten =>
s,2,SetVar(CIDSUFFIX=${CALLERIDNUM:6:10})<BR>exten =>
s,3,agi(transformcallerid.agi|${CIDPREFIX}-${CIDSUFFIX}) 'We keep<BR>phone
numbers in XXX-XXXX format<BR>exten => s,4,NoOp(${LANDMARKCID}) 'Display the
returned variable for<BR>debugging purposes<BR>exten =>
s,5,Gotoif($["${LANDMARKCID}" = " :" ]?6:7) 'If the AGI didn't fond<BR>a match
in the database<BR>exten => s,6,SetVar(LANDMARKCID=UNKNOWN:) 'Prepend the
caller ID with<BR>UNKNOWN:<BR>exten =>
s,7,SetCallerID(${LANDMARKCID}${CALLERIDNUM:3:10}) 'Otherwise<BR>prepend with
the returned SQL data<BR>exten =>
s,8,Goto(my-inbound-context-with-did,${CURRENTEXTEN},3) 'Return<BR>control to
calling context</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=278405716-22092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Tim King
[mailto:tim@compnetwork.net]<BR><B>Sent:</B> Thursday, September 22, 2005
10:52 AM<BR><B>To:</B> asterisk-users@lists.digium.com<BR><B>Subject:</B>
[Asterisk-Users] AGI Script to interact with ACCESS Databse and Set CID info
on the fly.<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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. <o:p></o:p></SPAN></FONT></P></DIV></BLOCKQUOTE></BODY></HTML>