Asterisk / FreePBX: Integrating a Caller ID with a Client Database

But what if, with an incoming call, instead of a soulless number, you see on your phone something like “Stroypostavka LLC” or “Ivanov Semyon Petrovich”, obtained from the base of your customers and contractors.
It will be very convenient, right?
And it is implemented very easily.

Traditionally, I will consider two options - for FreePBX, and for "clean" Asterisk.


General preparations

First you need to create a table in the database:

CREATE TABLE `companies` (
`name` CHAR(150) NOT NULL,
`number` BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (`number`)
);

and fill it with a list of customers, employees, and so on. This list can be easily imported through an intermediate CSV from 1C, Active Directory, various CRMs and address books using the many available utilities, from HeidiSQL to PHPMyAdmin, and this part should not cause problems.

FreePBX

In the Caller ID Lookup Sources menu, add the source type: MySQL, enter the server address, login, password, and in the query field specify:

SELECT name FROM companies WHERE number LIKE CONCAT('%',SUBSTRING('[NUMBER]',-7));

and in the inbound routes menu select the freshly created lookup source from the drop-down list.

Asterisk communications framework - that is, “clean” Asterisk

The following lines should be added to the dialplan:

exten => foo,n,MYSQL(Connect connid localhost cdr cdrpass asterisk) ; Указываем хост, логин, пароль, БД.
exten => foo,n,GotoIf($["${connid}" = ""]?nodb) ; Соединение не удалось
exten => foo,n,MYSQL(Query resultid ${connid} SELECT name FROM companies WHERE number="${CALLERID(num)}" LIMIT 1)
exten => foo,n,MYSQL(Fetch fetchid ${resultid} name)
exten => foo,n,MYSQL(Clear ${resultid})
exten => foo,n,Set(CALLERID(name)=${name})
exten => foo,n,MYSQL(Disconnect ${connid})
exten => foo,n(nodb),NoOp(DoneDB)

That's it.