Query Database after DTMF tones and use result

I have tested the IVR setup which works very good and now I’m figuring out how I can query a database using data coming from DTMF tones and use the result.

My Idea is to play an announcement for a caller where after he can put in some digits that I want to use in a query to see if his digits match.

When they match and you actually get 1 row back, I would like to transfer the person to whatever I want, a ringroup, doesn’t matter, this is quite standard.

I’m figuring out what and how I am going to do this. I can use func_odbc, but it seems to be more simple to use “cmd MySQL” because that is the database type I’m going to query.

I’m coming out on this:

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

and this:

voip-info.org/wiki/index.php … +func_odbc

But this doesn’t give me much detailed examples, so if someone can come up with some links this would be nice to gather more information.

Step 2 would be to have 2 sets of digits that a caller can type in and both are used in one query, or maybe two if needed.

But first my step one, the query, use the result as “1” and forward someone to “something” that you like.

Any info is appreciated !

I think - best will be if you start using AGI scripts. astcc - asterisk calling card system is based on same things - prompts for digits, then query’s etc.
You can use anything you want - PHP, Perl, even executables written in C/C++.

[quote=“bira_more”]I think - best will be if you start using AGI scripts. astcc - asterisk calling card system is based on same things - prompts for digits, then query’s etc.
You can use anything you want - PHP, Perl, even executables written in C/C++.[/quote]

Yes I read a lot about that, but people also recommend the cmd MySQL, so I’m figuring out what to use.

I have digged up the following what might be a good one to change:

voip-info.org/wiki/view/MySQ … k+RealTime

 Check incoming CALLERID for Blacklist

This is a simple way to keep a blacklist of phone numbers that you don't want to allow to call you/your company. The Macro will quickly check a MySQL database for the incoming callerid. If the callerid exist and the block is enabled, then the caller will get placed into the [blacklistednumber] context.

Create a new table in you MySQL RealTime connection.

CREATE TABLE `blacklist` (
`id` int(11) NOT NULL auto_increment,
`callerid` varchar(10) NOT NULL default '',
`blockenabled` char(1) NOT NULL default '1',
`notes` longtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into `blacklist` values('1','5556671212','1','Spammer');

The blockenabled column should equal 1 to enable the block. Set this to 0 (zero), if you wish to disable blocking the number.

Insert the following Macro and context into your dial plan.

[globals]
realdb_host=hostnameformysqldb
realdb_user=mysqldbuser
realdb_pass=mysqldbpassword
realdb_db=mysqldbthatcontainsthevoicemailusers

[macro-checkblacklist]
; This Macro will check the blacklist table to see if the callerid of the
; caller exist and blockenabled =1 (TRUE). If the callerid is listed, then
; tell the caller they have been blacklisted and politely HangUp()
;
; ${ARG1} = CallerID of incoming call
;
exten => s,1,MYSQL(Connect connid ${realdb_host} ${realdb_user} ${realdb_pass} ${realdb_db})
exten => s,2,MYSQL(Query resultid ${connid} SELECT\ callerid\ from\ blacklist\ where\ callerid=${ARG1} and blockenabled = 1)
exten => s,3,MYSQL(Fetch fetchid ${resultid} blacklistid)
exten => s,4,MYSQL(Clear ${resultid})
exten => s,5,MYSQL(Disconnect ${connid})
exten => s,6,GoToIf($["${blacklistid}" = ""]?7:fail,1)
exten => s,7,NoOp(${blacklistid})
; If the callerid is listed in the database, then send to blacklistednumber
; context
;
exten => fail,1,NoOp(${blacklistid})
exten => fail,2,GoTo(blacklistednumber,s,1)

[blacklistednumber]
; This is where a call will land if the macro-checkblacklist decides that
; the number should not be allowed to dial the company.
exten => s,1,Wait(2)
exten => s,2,Playback(privacy-you-are-blacklisted)
exten => s,3,Zapateller()
exten => s,4,HangUp()

The last step is to call the macro when a call arrives. On the context for your zap channels, add this line:

[zaptel-incoming]
;Incoming context for PRI.
;
exten => 8005551193,1,Macro(checkblacklist,${CALLERID(num)})
exten => 8005551193,2,Dial(SIP/Phone)

If someone has some comments or advises on this one, always welcome !

Ok, I figured out the AGI is the best option indeed.

What I have discovered is:

  • Collect DTMF tones
  • use them in a php script and set an AGI variable
  • use the AGI variable in your lines

Something what is a big guess to me is:

  • How to collect the DTMF tones
  • Use the DTMF tones in the php script and set it back to an AGI variable.

sub getphone() {
	my ($number) = @_;
	print STDERR "mYnUMBERis:  $number\n";
	
	my $sth = $dbh->prepare("SELECT * FROM routes WHERE " . $dbh->quote($number) . " REGEXP concat('^',pattern) ORDER BY LENGTH(pattern) DESC");
	$sth->execute;
	$res = $sth->fetchrow_hashref;
	$sth->finish;
	return $res;
}


$phoneno = $AGI->get_data("astcc-phonenum",$PhoneNum_Timeout_Dellay);
&getphone($phoneno);

This piece of code shows how to get digits (get_data), and how you can use them with MySQL.
It is from astcc (Perl).

Hi,

Thanks for the info, I figered that out also, so I’m on the good way, so I kinda know what I’m doing.

Now I’m using the AGI as mentioned but I can’find out if you can transfer calls to a callgroup.

What I do is, I get the data (DTMF) after this I do a query using this and see if there is a match. If there is a match I want to transfer the call to a ringgroup, the problem is that I can’t find if that is possible like hangup is.

So, for hangup you can use: $agi -> hangup()

But for transfer to a callgroup, would that be something like: $agi -> transfer($callgroup) ?

Some of commands are encapsulated, others - not.
You can use ANY command directly like $AGI->exec(“dictate”,$param);
I never worked with transfers…

OK, I have figured it out using a creative way using your advice.

Thanks.