Caller ID Superfecta

I think I can do this, but anyone with vTiger experience please chime in here.

Some experimentation with vTiger and MySQL queries has resulted in the following simplified queries confirmed effective for vTiger 5.2.1:

SELECT firstname,lastname FROM vtiger_contactdetails WHERE phone LIKE ' _number_ '
SELECT firstname,lastname FROM vtiger_contactdetails WHERE mobile LIKE ' _number_ '
SELECT firstname,lastname FROM vtiger_contactdetails WHERE fax LIKE ' _number_ '

lgaetz wrote:

Also vTiger permits free form number entry so the nested SQL rules from Asteridex for ignoring non-digit characters must be employed. Also need to specify the number of rightmost digits to match.

Using mysql's REGEXP for matching phone number would probably work here.

jkiel wrote:

Using mysql's REGEXP for matching phone number would probably work here.

I think you are probably right, just shows how little I know about all this. If I am reading the help properly the query would look something like this:

SELECT * FROM table WHERE field LIKE ' number ' REGEXP ' what goes here '

I am realy weak on regular expressions, can you help with the what goes here part for ignoring all non-digit characters?

Found the other two phone numbers in table: vtiger_contactsubdetails

These queries are confirmed working for vTiger 5.2.1:

SELECT firstname,lastname FROM vtiger_contactdetails INNER JOIN vtiger_contactsubdetails ON vtiger_contactsubdetails.contactsubscriptionid
=vtiger_contactdetails.contactid WHERE vtiger_contactsubdetails.otherphone LIKE ' _number_ '

SELECT firstname,lastname FROM vtiger_contactdetails INNER JOIN vtiger_contactsubdetails ON vtiger_contactsubdetails.contactsubscriptionid
=vtiger_contactdetails.contactid WHERE vtiger_contactsubdetails.homephone LIKE ' _number_ '

Crap. I just realized there is no field specifically for extension number on the contact page which means that vTiger users will probably store that info in the phone number fields. Would it be possible to have a REGEXP that filters out the characters to the right of the letters 'ex' (which will also catch ext: etc)?

I'm a little rusty on POSIX Regular Expression, working more often with Perl Regular Expressions, but, assuming you're trying to match a number like 5551234567, the following should work:

The statement would be more like:
SELECT * FROM table WHERE field REGEXP "[^0-9]*5[^0-9]*5[^0-9]*5[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*6[^0-9]*7[^0-9]*"

So in this case, we are allowing 0 or more of any non-numeric character before or after each number.

Also, since REGEXP isn't able to make good use of the index (not sure if vTiger has an index for the phone number fields anyway), it may be better to search all tables/fields at the same time using OUTER JOINs and ORs.

lgaetz wrote:

Would it be possible to have a REGEXP that filters out the characters to the right of the letters 'ex' (which will also catch ext: etc)?

That expression I gave should match the number you are looking for, even if nested in something like "Mobile (555) 123-4567 ext:374376"

jkiel wrote:

SELECT * FROM table WHERE field REGEXP "[^0-9]*5[^0-9]*5[^0-9]*5[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*6[^0-9]*7[^0-9]*"

Yep that is it, confirmed working, and it solves the problem with the ext number appended. Great!

The above works for number searches of 10 digits in length, how can you make it generic for numbers in other areas of the world? Also supposing you have a database of international numbers which includes these fictional two: 12345678 and 23456 is there a way to search for 23456 without getting a hit on the other?

lgaetz wrote:

how can you make it generic for numbers in other areas of the world?

The for-next loop you use to build the REGEXP should take care of that, building it as long or short as it needs to be.

Also supposing you have a database of international numbers which includes these fictional two: 12345678 and 23456 is there a way to search for 23456 without getting a hit on the other?

That could be a tricky one, but try:

"(^[^0-9]*001|^[^0-9]*1|^)[^0-9]*5[^0-9]*5[^0-9]*5[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*6[^0-9]*7([^0-9]+|$)"

Replace the "1" or "001" in "(^[^0-9]*001|^[^0-9]*1|^)" with an optional country code/etc. that may or may not be on the number. If you only want to match for one country code, use "(^[^0-9]*1|^)", if you want more just just add another, like (^[^0-9]*002|^[^0-9]*001|^[^0-9]*1|^)

Edit: the more I think about it, the country code thing could be a difficult one...

Edit2: I changed the country code expression a little.

Edit3: Just to describe what's going on in the regexp:

"(^[^0-9]*001|^)", at the start, etc., is searching for the start of the string followed by a country code, or, just the start of the string.

"([^0-9]+|$)", at the end, is searching for one or more non-numeric characters, or the end of the string.

In total, we are searching for: the start of the string, with a possible country code (how to decide what codes to search for beats me), follow by the phone number, with any non-numeric character interleaving allowed, ending with a non-numeric character or the end of the string.

How about

^([\+][0-9]{1,3}([ \.\-])?)?([\(]{1}[0-9]{3}[\)])?([0-9A-Z \.\-]{1,32})((x|ext|extension)?[0-9]{1,4}?)$

I found a good online test for regex patterns at http://www.spaweditor.com/scripts/regex/index.php

ukstevef wrote:

How about [...] I found this at http://regexlib.com/REDetails.aspx?regexp_id=3052 looks promising.

Though that will isolate any number. The REGEXP pattern needed should find a specific number.

I found a good online test for regex patterns at http://www.spaweditor.com/scripts/regex/index.php

Yes. I use it often.

jkiel wrote:

In total, we are searching for: the start of the string, with a possible country code (how to decide what codes to search for beats me), follow by the phone number, with any non-numeric character interleaving allowed, ending with a non-numeric character or the end of the string.

In the scheme for the UK lookups I have my CID Rules as

44|0+ZXXXXXXXXX
0ZXXXXXXXXX

I think maybe we are getting needlessly complex here. If Country codes are stored in the database, the user can use schemes and CID rules as appropriate to build the number to search with. The for-next will build based on the user input of number of rightmost digits which will work great.

I think it would be useful to add something to the regular expression that will ignore any digits to the right of a user specified character (defaults to a case insensitive x but would accept a ':' or some other string) so any extension digits will be ignored, any ideas for that.

What are people's thoughts on getting multiple MySQL hits for the same CID? Should you return the first CNAM found, no CNAM at all, or maybe some other text like "multiple hits"? Should the user be able to determine? All of the other database query sources LIMIT lookups to one result with no way for the user to choose if that is acceptable.

A lot of this conversation applies to other lookup sources. In looking thru Sugar_CRM and Asteridex, I think we are honing some features here that would benefit those and possibly other lookup sources.

lgaetz wrote:

I think maybe we are getting needlessly complex here.

It may be possible CID rules could work here -- but, I think it would be easier and more flexible to just apply an optional "valid prefix/country code" box for the user to fill in, avoiding the need to handle this with multiple schemes. A vTiger DB may have been populated by multiple users, so the more flexible the single search can be, the better. I may have entered 18005551234, Bob may have entered 8005554321. Simply stripping the 1 with a CID rule will not create a match for my record, nor will adding a 1 with a CID rule match Bob's.

I think it would be useful to add something to the regular expression that will ignore any digits to the right of a user specified character (defaults to a case insensitive x but would accept a ':' or some other string) so any extension digits will be ignored, any ideas for that.

The last regular expression I provided should handle what you need, while providing more flexibility. If the number ends with :, great, it will take it. A " ", end of string -- perfect, anything but another number is accepted.

What are people's thoughts on getting multiple MySQL hits for the same CID?

If a last update date is provided for the contact record, the record with the most recent date should be used (ORDER BY date_field DESC). If not, see if there are any other fields that may help with scoring the relevancy of a record.

1. Does not build regular expression yet
2. PHP error if no phone number field checkboxes are selected
3. I'm not sure if I am defaulting checkboxes properly

lgaetz wrote:

1. I'm not sure if I am defaulting checkboxes properly

I set my default to 'on' in the telepest source, and that works for me OK.

Could you combine the SQL so that it is only one query e.g.

SELECT firstname,lastname FROM vtiger_contactdetails WHERE {$run_param['Search_Office_Phone']} = 'on' AND phone REGEXP {$wquery_input} LIMIT 1
UNION
SELECT firstname,lastname FROM vtiger_contactdetails WHERE {$run_param['Search_Mobile_Phone']} = 'on' AND phone REGEXP {$wquery_input} LIMIT 1
UNION
SELECT firstname,lastname FROM vtiger_contactdetails WHERE {$run_param['Search_Fax_Phone']} = 'on' AND phone REGEXP {$wquery_input} LIMIT 1
UNION
SELECT firstname,lastname FROM vtiger_contactdetails WHERE {$run_param['Search_Home_Phone']} = 'on' AND phone REGEXP {$wquery_input} LIMIT 1
UNION
SELECT firstname,lastname FROM vtiger_contactdetails WHERE {$run_param['Search_Other_Phone']} = 'on' AND phone REGEXP {$wquery_input} LIMIT 1

r442 is ready for testing and/or comments.

I have not incorporated ukstevef's UNION suggestion due to time. Same for jkiel's idea of a country code for the regexp.

Found the table (finally!) that stores the created date and modified date for the contact information, so that multiple CNAM results can be sorted by order of date and retun only the newest:

table: vtiger_crmentity
index: crmid
created time field: createdtime
modified time field: modifiedtime

contactid in table vtiger_contactdetails = crmid in table vtiger_crmentity

*edit:

Got a confirmed working SQL that uses modified date to sort the results:

SELECT firstname,lastname, modifiedtime FROM vtiger_contactdetails
INNER JOIN vtiger_contactsubdetails ON vtiger_contactsubdetails.contactsubscriptionid = vtiger_contactdetails.contactid
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_contactdetails.contactid
WHERE vtiger_contactsubdetails.otherphone REGEXP "[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*6[^0-9]*7[^0-9]*8[^0-9]*9[^0-9]*0[^0-9]*" 
ORDER BY modifiedtime DESC

Couple suggestions:

In the REGEXP, the last "[^0-9]*" should be replaced with "([^0-9]+|$)".

Instead of executing separate SELECTs for each lookup, even combined with a UNION, combine them all in one SELECT using WHERE (condition1) OR (condition2) OR ... (etc.).

Behold the query in its final glory:

SELECT firstname, lastname FROM vtiger_contactdetails
INNER JOIN vtiger_contactsubdetails ON vtiger_contactsubdetails.contactsubscriptionid = vtiger_contactdetails.contactid
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_contactdetails.contactid
WHERE
(phone REGEXP '[^0-9]*9[^0-9]*9[^0-9]*0[^0-9]*4[^0-9]*7[^0-9]*9[^0-9]*9[^0-9]*9[^0-9]*2[^0-9]*3([^0-9]+|$)') OR
(mobile REGEXP '[^0-9]*9[^0-9]*9[^0-9]*0[^0-9]*4[^0-9]*7[^0-9]*9[^0-9]*9[^0-9]*9[^0-9]*2[^0-9]*3([^0-9]+|$)') OR
(fax REGEXP '[^0-9]*9[^0-9]*9[^0-9]*0[^0-9]*4[^0-9]*7[^0-9]*9[^0-9]*9[^0-9]*9[^0-9]*2[^0-9]*3([^0-9]+|$)') OR
(homephone REGEXP '[^0-9]*9[^0-9]*9[^0-9]*0[^0-9]*4[^0-9]*7[^0-9]*9[^0-9]*9[^0-9]*9[^0-9]*2[^0-9]*3([^0-9]+|$)') OR
(otherphone REGEXP '[^0-9]*9[^0-9]*9[^0-9]*0[^0-9]*4[^0-9]*7[^0-9]*9[^0-9]*9[^0-9]*9[^0-9]*2[^0-9]*3([^0-9]+|$)') 
ORDER BY modifiedtime DESC

Confirmed working for vTiger 5.2.1

r443 is latest and greatest. With this new single query, I have lost the ability to toggle whether to search or not search individual phone number fields.

r444 has latest incarnation, with number field toggles back in. Everything has been dealt with except the country code in the REGEXP. It seems to work very well, but I only have a small sample dbase for testing.

*edit: r445 has solved the PHP error if no number fields are toggled

Discovered two more tables of records, Accounts and Leads. r448 includes toggles and searches for those tables