Caller ID Superfecta: THE MODULE

[jpeterman]

Try this simple replacement in callerid.php and let me know if it works for you. I have it working on my end with some minor code changes. This snippet will only search contacts. I will have it all working when I have time (hopefully soon).

For anyone wanting to jump in, the original problem centers around passing from the first sql lookup to the next. For some reason, standard mysql logic is not working reliably.

//--------- SugarCRM --------------------
$wsearch_filter_length = 15; // by changing this variable, you have control over the length of the number to be searched in the database
$value="" ;
if ($SugarCRM=='1') :
        if ($debug=='1') :
          echo "SugarCRM lookup ....", "\n" ;
        endif ;
    $link = mysql_connect("localhost", "root", "passw0rd")
        or die("SugarCRM connection failed");
    mysql_select_db("sugarcrm")
        or die("SugarCRM data base open failed");
    if ($wsearch_type >= 2 && strlen($value) == 0): // search contacts
    $query = "SELECT * FROM contacts WHERE deleted = '0' AND (RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_work,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '$thenumber' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_mobile,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '$thenumber'  OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_home,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '$thenumber' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_other,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '$thenumber' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_fax,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '$thenumber') LIMIT 1";
    $result = mysql_query($query)
        or die("SugarCRM query failed");
        if (mysql_num_rows($result)>0) :
          $row = mysql_fetch_array($result);
          $value=$row["first_name"] . ' ' . $value=$row["last_name"];
        endif ;
    endif;
    mysql_close($link);
        if (strlen($value)>0) :
          echo $value;
          exit ;
        endif ;
endif ;
//--------- End SugarCRM --------------------

 

[tshif]

There are a couple of really exciting possible upgrades for Superfecta being discussed in the forums lately. I wanted to see what everyone here thinks about a couple of things.

1. The Sugar CRM code was contributed I think by Aster1sk - if its not working we should maybe get the creator of it involved in a fix. But I would also like to know what everyones experiences are with jpeterman's version are like. What version of the functionality should we go with in the module?

2. In another thread - there is a modification that changes the way FreePBX deals with caller id - which makes sense if your privider provides some caller id services of their own. I think this is a another option we could include in the module - but I sure would like to hear every else thoughts on the matter. I sort of envision it as a check mark on the Superfecta user interface that turns the special override context off/on.

-tshif

 

[Boolah]

I'd like to see two modifications:

1) The ability to override provider CNAM based on service.

For example, you should be able to specify that if a name exists in AsteriDex, this should override the provider CNAM, but if a name exists in AnyWho, this will not override the provider CNAM.

2) Some kind of generic hook should be provided once all processing has taken place to do something else with the resultant data.

I run a HTPC and have written a function in the callerid.php script to create a socket to my HTPC to display caller ID info when a call comes in. The only annoyance is that when a new version of the callerid.php script comes out, I have to go to each lookup source and add a call back to my function to display the info on my HTPC. If there were a hook in place with the ability to call a user-defined function, this would certainly make things a bit easier. Granted, I'm not sure how useful this will be to the masses, so I can't complain too much.

 

[tshif]

sadly it doesnt work. I only get back the called number not the name from sugarcrm. my number format is +49(7854)1234567. Changing ths to +4978541234567 doesnt really make an difference. At germany the maximum digits should be 15 digits with the international format 0049. with the short form +49 ist should be 13. May we could make some changes to the code which takes care of the extensions language ore an language field at he configuration of the freepbx plugin? MAy we could collect soem international formats here to make your impementation easier?

There is a variable inside the callerid.php script which allows you to change the length of the telephone numbers. Its located near the old block of fixed variable at the top of the script. I suggest you try changing that and seeing if it changes the failure in any way - and let us know.

Sadly - I only roll up the module for Superfecta - I didn't write any part of it. I Think forum user Aser1sk wrote the sugar CRM portion that currently rolled up in the module. Perhaps he could be of some help here.

Also, there's another potential CRM integration floating around here - you should try patching that into the callerID script in place of the one that's there and see if it works better for you.

Im asking folks for feedback about the Sugar integration, and a couple other aspects of the module so we can make it better, and work for more kinds of people in more kinds of applications. Please chime in with your thoughts about that!

-tshif

 

[jpeterman]

OK, Here is a meld of different SugarCRM integrations. This one works great for me now, though I have only tested for two days. Just edit your callerid.php and replace the SugarCRM section. Please let me know if you have any feedback.

-jpeterman

//--------- SugarCRM --------------------
/* BUG FIXES */
// v0.9.5  Fixed PHP/MYSQL closing problem 3/30/2009 jpeterman
// v0.9.3: Fixed contacts query to also lookup phone_home entry
// v0.9.2: Initial Release Version
$wquery_input = "";
$wquery_string = "";
$wquery_result = "";
$wresult_caller_name = "";
$wsearch_filter_length = 10; // by changing this variable, you have control over the length of the number to be searched in the database

if ($SugarCRM=='1'):
    $wquery_input = preg_replace("/\D/","",$_REQUEST['thenumber']); // strip non-digits
    if (strlen($wquery_input) == 0) exit; // abandon search if no number is passed
    if (strlen($wquery_input) > $wsearch_filter_length) $wquery_input = substr($wquery_input, -$wsearch_filter_length); // keep only the filter_length rightmost digits
    $wdb_handle = mysql_connect($wSugarCRM_db_host, $wSugarCRM_db_user, $wSugarCRM_db_pass) or die("SugarCRM connection failed" . mysql_error());
    mysql_select_db($wSugarCRM_db_name) or die("SugarCRM db open error: " . mysql_error());
    mysql_query("SET NAMES 'utf8'") or die("UTF8 set query  failed: " . mysql_error());

    if ($wsearch_type >= 0): // search accounts
        $wquery_string = "SELECT * FROM accounts WHERE deleted = '0' AND (RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(accounts.phone_office,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '" . $wquery_input . "' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(accounts.phone_alternate,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '" . $wquery_input . "' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(accounts.phone_fax,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '" . $wquery_input . "') LIMIT 1";
        $wquery_result = mysql_query($wquery_string) or die("SugarCRM accounts query failed" . mysql_error());
        if (mysql_num_rows($wquery_result)>0):
          $wquery_row = mysql_fetch_array($wquery_result);
          $wresult_caller_name = $wquery_row["name"];
        endif;
    endif;

    if ($wsearch_type >= 1 && strlen($wresult_caller_name) == 0): // search also users, if no result from accounts
        $wquery_string = "SELECT * FROM users WHERE deleted = '0' AND (RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(users.phone_work,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '" . $wquery_input . "' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(users.phone_mobile,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '". $wquery_input ."'  OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(users.phone_home,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '". $wquery_input ."' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(users.phone_other,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '" . $wquery_input . "' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(users.phone_fax,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '" . $wquery_input . "') LIMIT 1";
        $wquery_result = mysql_query($wquery_string) or die("SugarCRM users query failed" . mysql_error());
        if (mysql_num_rows($wquery_result)>0):
          $wquery_row = mysql_fetch_array($wquery_result);
          $wresult_caller_name = $wquery_row["last_name"] . ' ' . $wquery_row["first_name"];
        endif;
    endif;

    if ($wsearch_type >= 2 && strlen($wresult_caller_name) == 0): // search also contacts, if no results from previous searches
        $wquery_string = "SELECT * FROM contacts WHERE deleted = '0' AND (RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_work,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '" . $wquery_input . "' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_mobile,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '". $wquery_input ."'  OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_home,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '" . $wquery_input . "' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_other,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '" . $wquery_input . "' OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contacts.phone_fax,' ',''),'+',''),'-',''),'(',''),')','')," . $wsearch_filter_length . ") LIKE '" . $wquery_input . "') LIMIT 1";
        $wquery_result = mysql_query($wquery_string) or die("SugarCRM contacts query failed" . mysql_error());
        if (mysql_num_rows($wquery_result)>0):
          $wquery_row = mysql_fetch_array($wquery_result);
          $wresult_caller_name = $wquery_row["last_name"] . ' ' . $wquery_row["first_name"];
        endif;
    endif;

    mysql_close($wdb_handle);

    if (strlen($wresult_caller_name)>0) :
        echo $wresult_caller_name;
          exit ;
    endif ;

endif ;

//--------- End SugarCRM --------------------

 

[desmondfuller]

I am a newbie with PiaF and got the module (1.0.3.3) setup without an issue and it works great.

Can anybody give some points on rerouting the SPAM callers to a "dead pool" instead of ringing our phones?

 

[tshif]

Note: This article's download links are obsolete, but left here for archival purposes. Get the current code from the link in the very first post of this thread.


jpeterman -

Heres a module version with your code in it.

Would you test it and see if it works as expected? If it does, since you are willing to work on the code you have contributed - I am leaning toward just integrating your version of the Sugar CRM code.

Let me know how it goes -

 

[jpeterman]

I tried the new version of the rollup and it works great for me. Can someone else using Sugar give it a shot to test functionality?

Mysterious - Does this work for you now?

-jpeterman

 

[tshif]

I tried the new version of the rollup and it works great for me. Can someone else using Sugar give it a shot to test functionality?

Mysterious - Does this work for you now?

-jpeterman

:detectiveb:

Calling all SugarCRM users - we need your feedback. If you can test the beta module with the new Sugar integration from jpeterman - it would be very helpful.

 

[tshif]

Compatible with TrixBOX CE

This module is compatible with the security models used in, and has been tested with the following distributions:
Fonicatec PABX
Foncordiax
PBX In A Flash
TrixBOX CE
The Module’s wiki Page is here.

 

[wardmundy]

My hat is off to all of you that have helped getting Superfecta perfected. This really is the way open source development is supposed to work. And, at least in the application development end of the business, this is the very first time I've ever seen it function at or even near perfection. THANKS!!

 

[Mysterious]

I´ve tested the last postet version and it generaly works with sugarcrm. I made some modification to fit to german number prefixes. It´s not perfect but workes for me. May we should export the callerid modifications i have changed to the freepbx modul gui.

Heres are my changes in callerid.php

$thenumber=$_REQUEST['thenumber'];

if(preg_match('/^[1-9]{1}/',$thenumber)){
 $thenumber = '49781'.$thenumber;
 }

if (substr($thenumber,0,2)=='00') {
 $thenumber = substr($thenumber, -(strlen($thenumber)-2));
// $thenumber = '+'.$thenumber;
 }

if(substr($thenumber,0,1)=='0') {
 $thenumber = substr($thenumber, -(strlen($thenumber)-1));
 $thenumber = '49'.$thenumber;
 }

$npa = substr($thenumber,0,3);
$nxx = substr($thenumber,3,3);
$station = substr($thenumber,6,4);

$wsearch_filter_length = 20;

I had to outline this line so that the sugarcrm lookup contains the "+" sign for lookup because i save my numbers in the format +49 1234 123456

     $wquery_input = $thenumber;
    //$wquery_input = preg_replace("/\D/","",$_REQUEST['thenumber']); // strip non-digits

May someone has an idea how i can readd this line but the "+" sign is preserved and all other ist stripped out.

 

[tshif]

International Sugar

jpeterman, Mysterious;

Well - I think we need to add the search length to the Web UI at the least. We in the U.S. can be a little myopic about internationalization - Ooops! Ignorance is bliss? Hehehe.

So - that much seems like the proverbial no brainier - and I'll add it in the next update.

jpeterman - could you test Mysterious's mods and see if they work in your environment? It would also be really cool if you could generaly chime in on any other thoughts you have about the suggested changes. If you agree with them - could you provide me with the new merged code in the CRM block? Your work was really easy for me to integrate that way. (When I say "easy for me", what I really mean is - I could figure it out, and didnt mess it all up!)

-tshif

 

[rossiv]

I'm getting this:

d,s,20)
    -- Executing [s@macro-user-callerid:20] NoOp("SIP/###########-b7804dd8", "Using CallerID "<!DOCTYPE HTML PUBLIC -//IETF//DTD HTML 2.0//EN>
<html><head>
<title>401 Authorization Required</title>
</head><body>
<h1>Authorization Required</h1>
<p>This server could not verify that you
are authorized to access the document
requested.  Either you supplied the wrong
credentials (e.g., bad password), or your
browser doesn't understand how to supply
the credentials required.</p>
<hr>
<address>Apache/2.2.3 (CentOS) Server at 127.0.0.1 Port 80</address>
</body></html>" <###########>") in new stack

when I do a CID lookup. Also, when I run a manual lookup using a URL, http://192.168.1.24/admin/modules/superfecta/callerid.php?thenumber=THENUMBER, and put a number that is in Asteridex in there, I do not get that name. I get another name. When I put in another Asteridex number, it does not show up in there. Asteridex is enabled in the callerid.php script. How do I get it to lookup Asteridex? Also, is there any way to tell where a specific lookup came from?

 

[tshif]

What veriosn of the module are you using?

Read and Follow ALL instructions for setting it up - INCLUDING the id and password in CellerID Lookup Sources.

 

[rossiv]

V 1.0.4
Now I have entered the Maint user and password in the sources page, but it is still not finding the entry in Asteridex.

 

[tshif]

Note: This article's download links are obsolete, but left here for archival purposes. Get the current code from the link in the very first post of this thread.


Try this as an upgrade and let us know if this helps the SugarCRM issue.

Edit: Oh you said Astridex. Sorry about that. Hmm. The Astridex code has been stable for a while - and is the same in all versions of the module. DId you make any custom changes to Astridex after you installed it?

 

[wardmundy]

I'm getting this:

d,s,20)
    -- Executing [s@macro-user-callerid:20] NoOp("SIP/###########-b7804dd8", "Using CallerID "<!DOCTYPE HTML PUBLIC -//IETF//DTD HTML 2.0//EN>
<html><head>
<title>401 Authorization Required</title>
</head><body>
<h1>Authorization Required</h1>
<p>This server could not verify that you
are authorized to access the document
requested.  Either you supplied the wrong
credentials (e.g., bad password), or your
browser doesn't understand how to supply
the credentials required.</p>
<hr>
<address>Apache/2.2.3 (CentOS) Server at 127.0.0.1 Port 80</address>
</body></html>" <###########>") in new stack

when I do a CID lookup. Also, when I run a manual lookup using a URL, http://192.168.1.24/admin/modules/superfecta/callerid.php?thenumber=THENUMBER, and put a number that is in Asteridex in there, I do not get that name. I get another name. When I put in another Asteridex number, it does not show up in there. Asteridex is enabled in the callerid.php script. How do I get it to lookup Asteridex? Also, is there any way to tell where a specific lookup came from?

You've password-protected the AsteriDex directory. It can't be used for CallerID lookups once you do that.

 

[rossiv]

At least I dont think I protected it. All I did to install asteridex was to run Ward's orgasmatron installer.
The asteridex area is not password protected. Wouldn't it just get it from MySQL?

 

[tshif]

CallerID Superfecta: THE MODULE v1.1.0: The Superfecta module has been updated

Note: This article's download links are obsolete, but left here for archival purposes. Get the current code from the link in the very first post of this thread.


CallerID Superfecta: THE MODULE v1.1.0
The CallerID Superfecta module has been updated.

This is a maintenance release. Upgrading is recommended, but not required. Please get the upgrade here.

Changes:
1. DB ID/PW
This version of the module eliminates the requirement/expectation that the database id and password be the "default" values. These values are parsed from amportal.conf.

2.jpeterman Sugar CRM integration has been adopted as the standard within the module.

3. The module version has been tested and is known to work with Asterisk versions 1.4 and 1.6.

As always - please provide feedback as to your success or failure with this new version of the module.

-tshif