Topic: Trying to make (eGW) dblookup script work [SOLVED]

[jester]

Hi,
I'm trying to make a agi script do a database lookup (eGroupWare db) for Asterisk so a name can be set for an incoming telephone number. Needing a starting point because i know nothing about linux/asterisk/agi/perl i'm trying to alter an AsteriDex script from Nerd Vittles.

If someone is also interested and knows a little bit about this.... here's what i've come up with so far, but it isn't working yet!:

- Changed permissions on mysqld on my eGW server (different server from Asterisk/SAIL-server):

Code: [Select]

db configuration setprop mysqld LocalNetworkingOnly no
expand-template /etc/my.cnf
/etc/rc.d/init.d/mysqld restart
- Added an extra user on the egroupware database (where 192.168.101.1 is the IP-address my Asterisk/SAIL-server):

Code: [Select]

mysql

GRANT ALL ON egroupware.*
TO '<MyUser>'@'192.168.101.1'
IDENTIFIED BY '<MyPassword>';
flush privileges;
\q;
- dblookup.agi script with the code below in /var/lib/asterisk/agi-bin (permissions/user same as the other agi scripts):

Code: [Select]

#!/usr/bin/perl -w

use Asterisk::AGI;
use LWP::UserAgent;
use DBI;

$AGI = new Asterisk::AGI;

my %input = $AGI->ReadParse();
my ($exten) = @ARGV;

if($exten eq ''){
$exten=$input{'$EXTEN'};
}

$exten = substr($exten,2) ;
$exten =~ s/\D//g;
$AGI->verbose("EXTEN IS: $exten\n");

$AGI->verbose("Ready for database lookup... \n");
$lookupResult = &database_lookup ($exten);
if ($lookupResult != "No Results") {
# my($realexten,$realname) = split(/=:SeParAtor:=/o,$realdude);
# $AGI->verbose("Database match. Number to Dial = $realexten\n");
# $AGI->verbose("Database match. Person to Dial = $realname\n");
  $AGI->verbose("Database match. Person calling = $lookupResult\n");
$lookupResult =~ s/[\,\"\'\(\)\_\ ]+/-/g;
# $AGI->set_variable('DIAL', $realexten);
# $AGI->set_variable('DUDE', $realname);
$AGI->set_variable('DUDE', $lookupResult);
}
else{
$AGI->verbose("Unable to find an database match.\n");
}

exit(0);

sub database_lookup {
my ($exten) = @_;

$dbName = 'egroupware';
$dbHost = '192.168.0.2';
$dbPort = '3306';
$dbUserName = '<MyUser>'
$dbUserPass = '<MyPassword>'
$internatCode = '00';
$localPrefix = '(0)';

my $dbh = DBI->connect("dbi:mysql:database=$dbName;host=$dbHost;port=$dbPort","$dbUserName","$dbUserPass") or die("Connect failed");
my $sth = $dbh->prepare("
SELECT
n_fileas FROM egroupware.egw_addressbook
WHERE
contact_owner < 0
AND (
replace(replace(replace(replace(tel_work,' ',''),'-',''),'+','$internatCode'),'$localPrefix', '') = '$exten'
OR
replace(replace(replace(replace(tel_cell,' ',''),'-',''),'+','$internatCode'),'$localPrefix', '') = '$exten'
OR
replace(replace(replace(replace(tel_other,' ',''),'-',''),'+','$internatCode'),'$localPrefix', '') = '$exten'
)
LIMIT 1;")
$sth->execute;
if($sth->rows == 0)
{
return "No Results";
}
else
{
my $resptr =  $sth->fetchrow_hashref();
my $dialname = $resptr->{"n_fileas"};
return $dialname;
}

$dbh->disconnect;
return "";
}
- Manually altered/added in /etc/asterisk/extensions.conf in [mainmenu] a few lines to call the script specifically on calls coming in on number: 0123456789 (have no idea where to put it permanently when the srcipt would work though).:

Code: [Select]

;        exten => 0123456789,1,agi(selintra,Inbound,${EXTEN})  ; original line by SAIL commented out
        exten => 0123456789,1,EAGI(dblookup.agi|${EXTEN})       ; database caller name lookup in eGW
        exten => 0123456789,2,Set(CALLERID(name)=${DUDE})       ; setting of the found name
        exten => 0123456789,3,agi(selintra,Inbound,${EXTEN})
- To make Asterisk know about the manual alterations to extensions.conf (any changes in SAIL will overwrite these alterations!):

Code: [Select]

asterisk -rvvvvv
reload
exit


As said it is not working yet, with AGI debug on i can see that the script is called but does not return anything.

any help would be appreciated.

Regards,
jester.

[SARK devs]

HI Jester,

OK...

First of all, while calling perl from asterisk is perfectly feasible and, yes we know the guys over at TBox and Nerd Vittles do it all the time, we shudder at the sheer overhead of setting up and tearing down the perl environment in real time while asterisk is running.   How about simply calling the MySQL DB directly from a custom app in asterisk?  Have a look here...

http://www.voip-info.org/wiki/view/Asterisk+cmd+MYSQL

It may be that you can get what you need without having to set up the AGI environment and calls.

If this won't work for you and you really want to build an AGI then agi debug is your friend.  Also, as long as you are running the asterisk master console (start asterisk with asterisk -vvvvc), then you can also use print STDERR in your perl routine and the output will appear on the asterisk master console.  Using these two tools should get you humming along the right track.

 
Kind Regards

S

   

[jester]

Hi Selintra,

I mostly got it working with the suggested MYSQL() command, to make it more human readable for others i was hoping to be able to use some variables in the beginning of the custom app with ${SET(<varname>=<value>)} or SetVar(<varname>=<value>) but both options generate errors on the CLI.

Is variable setting in custom apps maybe restricted in SAIL?

regards,
jester.

[SARK devs]

HI Jester

Is variable setting in custom apps maybe restricted in SAIL?

No it shouldn't be.   However, there is/was a restriction in custom apps that you couldn't use the pipe (|) character to delineate parameters.  This has been fixed in 625 (which is available for download).

Are you sure you are using the correct syntax on your statements?  There is absolutely nothing special about custom apps; the SAIL generator simply plugs them into extensions.conf verbatim and then includes them into either the inbound or outbound context (depending upon which you chose when you created the app).

If you want a second opinion, mail us your app and we'll give a whiz on one of the test servers. 
 
Kind Regards

S

[jester]

Alright, i can't work out how to make this more readable with the use of variables so i'l just post it as it is. As recommended i've now used a custom app with a MYSQL command to make it work. The code is below, please adjust to suit your needs!

In the query i've set the contact_owner to <= 0, this will narrow down the selection to group-addressbooks and system-accounts. If you want it to search all your addresses leave this statement out. If you want to only search a specific addressbook, the you should find out the correct contact_owner Id, for instance with a tool like 'phpMyAdmin' or 'Mysql Query Browser'. With several replace-statements i've filtered out (inter)national access code and characters to get a clean telephone number so we can match it to the incoming one. If you want to search other fields that the ones i picked (tell_work, tell_cell, tell_other) then change the query. With LIMIT 1 the select will only return the first result, so if you have multiple entries with the same telephone number only the first one will be displayed !!!

If your eGroupware db is on a different server you'll still need to change the LocalNetworkingOnly setting as in the first post. Also you still will need to add a user to MySQL also as in the first post.

Don't forget to replace the <host>, <dbUserName>, <dbUserPass> and <dbName> with the correct info. Finally with the last GoTo statement the call is being redirected to the Operator extension (5000).

Code: [Select]

exten => s,1,MYSQL(Connect connid <host> <dbUserName> <dbUserPass> <dbName>)
exten => s,2,MYSQL(Query resultid ${connid} SELECT\ n_fileas\ FROM\ egroupware.egw_addressbook\ WHERE\ contact_owner\ <=\ 0\ AND\ (replace(replace(replace(replace(replace(tel_work\,\'\ \'\,\'\')\,\'-\'\,\'\')\,\'+31\'\,\'0\')\,\'(0)\'\,\'\')\,\'+\'\,\'00\')=${CALLERID(num)}\ OR\ replace(replace(replace(replace(replace(tel_cell\,\'\ \'\,\'\')\,\'-\'\,\'\')\,\'+31\'\,\'0\')\,\'(0)\'\,\'\')\,\'+\'\,\'00\')=${CALLERID(num)}\ OR\ replace(replace(replace(replace(replace(tel_other\,\'\ \'\,\'\')\,\'-\'\,\'\')\,\'+31\'\,\'0\')\,\'(0)\'\,\'\')\,\'+\'\,\'00\')=${CALLERID(num)})\ ORDER\ BY\ n_fileas\ LIMIT\ 1)
exten => s,3,MYSQL(Fetch fetchid ${resultid} TEMP)
exten => s,4,MYSQL(Clear ${resultid})
exten => s,5,MYSQL(Disconnect ${connid})
exten => s,6,Set(CALLERID(name)=${TEMP})
exten => s,7,GoTo(extensions,5000,1)Remark: In the mysql-query spaces, quotes (\’ and \”), comma’s (\,), tilde (\`), and backslash (\\) need to be escaped with a backslash (\).



Hope this is useful to someone else.
jester.

[SARK devs]

Nice work Jester

Well done with the code, you've done a lot with just a few statements..  always nice to see.

Re the last statement...

to pass control to an alias i suggest

Code: [Select]

exten => s,7,GoTo(internal-presets,{alias-number},1)
to pass control to a regular extension I suggest

Code: [Select]

exten => s,7,GoTo(extensions,{extension-number},1)
Very cool custom app

Best

S

[jester]

Hi Selintra,

to pass control to an alias i suggest

Code: [Select]

exten => s,7,GoTo(internal-presets,{alias-number},1)
to pass control to a regular extension I suggest

Code: [Select]

exten => s,7,GoTo(extensions,{extension-number},1)

You're right, i originally was using an alias but changed it for this example.... edited the original post.

Thanx!
jester.