SOLVED MYSQL caller id lookup

[modcar]

Hello all;

I've recently migrated from FreePBX to Fusion. One of the functions I had in FreePBX (and am now missing) was caller id lookup using WHMCS

I've googled, and tried a bunch of things, but can't seem to get it working

Help plz

Thanks in advanced!

 

[EasyBB]

Start here and have a play: http://www.pbxforums.com/threads/caller-id-lookup-cidlookup-from-contacts.20/

 

[modcar]

Hello;

I tried that, but it doesn't seem to like the cidlookup action

mod_dialplan_xml.c:57 Invalid Application cidlookup

I should have mentioned, Fusion is multi-tenant

 

[EasyBB]

Invalid Application cidlookup

Do you have cidlookup module enabled? Advanced -> Modules -> CID Lookup

 

[modcar]

I didn't, but do now

I now get:

2017-06-11 22:50:08.332564 [ERR] switch_odbc.c:368 STATE: IM002 CODE 0 ERROR: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

2017-06-11 22:50:08.332564 [CRIT] switch_core_sqldb.c:508 Failure to connect to ODBC mysql!
2017-06-11 22:50:08.332564 [ERR] mod_cidlookup.c:530 Unable to lookup cid: Unable to get database handle. dsn: [mysql://hostaddr=103.xx dbname=xxxxxxx user=yyyyy password=zzzzzzzzzzzz]

I followed this doc to install/configure unix odbc

 

[EasyBB]

dsn: [mysql://hostaddr=103.xx dbname=xx user=xx password=xx]

Hope you haven't posted your real connection details?

I don't think you need to configure odbc. The db you are trying to access is on another machine? Then you probably only need access string entered into the cidlookup.xml file followed by a 'flush memcache 'and 'reload xml'.

 

[modcar]

whoops, not now

So, I have setup an odbc, named it whmcslookup and can connect from terminal

isql -v whmcslookup

SQL> SELECT companyname AS name FROM tblclients WHERE phonenumber = '123456' UNION SELECT companyname AS name FROM tblcontacts WHERE phonenumber = '123456' LIMIT 1  +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name                                                                                                                                                                                                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Company Name                                                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched

I've got this in the /etc/freeswitch/autoload_configs/cidlookup.conf.xml

<!-- WHMCS Lookup -->
      <param name="odbc-dsn" value="whmcslookup"/>
      
      <param name="sql" value="SELECT companyname AS name FROM tblclients WHERE phonenumber = '${caller_id_number}' UNION SELECT companyname AS name FROM tblcontacts WHERE phonenumber = '${caller_id_number}' LIMIT 1"/>

But, Fusion is reporting:

2017-06-12 12:21:31.172577 [ERR] switch_core_sqldb.c:1197 SQL ERR: [SELECT companyname AS name FROM tblclients WHERE phonenumber = '123456' UNION SELECT companyname AS name FROM tblcontacts WHERE phonenumber = '123456' LIMIT 1] no such table: tblcontacts
2017-06-12 12:21:31.172577 [ERR] mod_cidlookup.c:530 Unable to lookup cid: (null)

 

[jsteel]

Have you tried using your database name in your SQL? Ex. Select companyname AS name FROM database.table, also verify that you actually have a table named tblclients and it is spelled the same as in your query.

 

[modcar]

Yes, If I copied and pasted the select string, and added in a number, it would select no problems.

I've given up on this, as the system is a multi-tenant, and it seems to me - either mysql lookup or pgsql lookup (for local contacts). But not both

 

[modcar]

Just incase anyone else wants to do this

In /etc/freeswitch/autoload_configs/cidlookup.conf.xml:

<!-- WHMCS Lookup -->
     <param name="url" value="https://whmcs-domain.com/whmcs-lookup.php?number=${caller_id_number}"/>

And the php file (hosted on the same box as WHMCS):

<?php
$number=$_GET['number'];

//We need to know the number
if ($number == '') { ?>
    <p>Er, you're not ment to be running this, whats going on here?</p>
    <?
    exit();
}
//WHMCS stores numbers as 04xxxxxx or 64.4xxxxxxx
//Drop the zero at the start of the number
if (substr($number, 0, 1) === '0') {
    $number = substr_replace($number, "", 0, 1);
}
//Drop the 64 the start of the number
if (substr($number, 0, 2) === '64') {
    $number = substr_replace($number, "", 0, 2);
}

//Connect to WHMCS DB
$mysqli=new mysqli('localhost','username','password', 'database_name');
if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}


//Now query company name
$company_name = $mysqli->query("SELECT companyname AS name FROM tblclients WHERE phonenumber like '%$number' UNION SELECT companyname AS name FROM tblcontacts WHERE phonenumber like '%$number' LIMIT 1");
//Display company name
while($row = $company_name->fetch_object()) {
    $name= $row->name;
}
//Else, lookup first / last name
if ($name == '') {
    $first_last = $mysqli->query("SELECT firstname, lastname FROM tblclients WHERE phonenumber like '%$number' UNION SELECT firstname, lastname FROM tblcontacts WHERE phonenumber like '%$number' LIMIT 1");
    while($row = $first_last->fetch_object()) {
        $name = $row->firstname.' '.$row->lastname;
    }   
}
echo $name;
?>

 

[EasyBB]

For the sake of security, please add a regex to check $_GET['number'] contains numbers only OR escape html special characters and prepare the query using parameters.