Doing partial searches in the Contacts section

[dspaan]

I noticed that when i enable the contacts section :

169. Added Admin -> Contacts section(must be enabled in System Settings) which
allows for system-wide transfer contacts to be used in conjunction with
Custom Transfer being set to VIEW_CONTACTS and Enable Transfer Presets
set to CONTACTS. Able to be disabled per user. Also can be accessed from
a separate database by using the Alt Log settings in System Settings.
The purpose of this feature is to be used for a receptionist or operator
that needs to transfer calls to non-agents.

Contacts Enabled -This setting enables the Contacts sub-section in Admin which allows a manager to add modify or delete contacts in the system that can be used as part of a Custom Transfer in a campaign where an agent can search for contacts by first name last name or office number and then select one of many numbers associated with that contact. This feature is often used by operators or in switchboard functions where the user would need to transfer a call to a non-agent phone. Default is 0 for disabled.

I have to know the last or first name of the contact exactly in order to find that contact. Is there a way to enable partial searches somewhere?

[blackbird2306]

No there is no way to change the search method without makings changes in the code. All sql queries were made with for instance WHERE last_name="$last_name", which needs the results must be exact like the searching pattern. But you can change this by modifying vdc_db_query.php:
There is this part:

Code: Select all

if ($ACTION == 'SEARCHCONTACTSRESULTSview')
{...}


here you have to change on dozen places the "$searchSQL" part:
e.g.
from:

Code: Select all

$searchSQL .= "office_num='$phone_number'";
$searchSQL = "last_name=\"$last_name\"";


to:

Code: Select all

$searchSQL .= "office_num LIKE '%$phone_number%'";
$searchSQL = "last_name LIKE \"%$last_name%\"";


[dspaan]

Thanks Blackbird! However when a SVN update is run this will all be overwritten, any tips on how to quickly re-apply customizations after an update?

[blackbird2306]

Make the customization as an option in admin menu and put it on the issue tracker. When Matt feels it's a good feature then he will make it as an official part of vicidial. That's the best scenario. Another way is in outsourcing this changed part: "if ($ACTION == 'SEARCHCONTACTSRESULTSview') { }" in a separate .inc.php file and include it like:

Code: Select all

################################################################################
### SEARCHCONTACTSRESULTSview - display search results for contacts search
################################################################################
include ("search.inc.php");
/* comment out original part
if ($ACTION == 'SEARCHCONTACTSRESULTSview')
   {  ...
        }
comment end */


But often the code from single php files won't be altered, so you can replace them with your customized file after update!

[mflorell]

The CONTACTS features were written to specific client specifications, so we wouldn't be changing defaults, but there is always the option to add more settings to extend functionality.

[dspaan]

Blackbird, if i want to do partial searches from the admin backend i suppose i have to modify admin_search_lead.php but which lines would i have to edit?

[blackbird2306]

I guess you are talking about "Search for a lead" and not about contacts section? Yes you need to make changes in "admin_search_lead.php" file. There are many sql queries, which should be "LIKE" and not "=". Let's start with an example for partial search of phone number beginning at line 931 (newest revision 2971 / red part is original part and green is the new code):

##### BEGIN Lead search #####
...
if ($phone)
{
if ($alt_phone_search=="Yes")
{
// old part comment out // $stmt="SELECT $vicidial_list_fields from $vl_table where phone_number='" . mysqli_real_escape_string($link, $phone) . "' or alt_phone='" . mysqli_real_escape_string($link, $phone) . "' or address3='" . mysqli_real_escape_string($link, $phone) . "' $LOGallowed_listsSQL";

// new LIKE part ------------------------ START
$stmt="SELECT $vicidial_list_fields from $vl_table where phone_number LIKE '%" . mysqli_real_escape_string($link, $phone) . "%' or alt_phone LIKE '%" . mysqli_real_escape_string($link, $phone) . "%' or address3 LIKE '%" . mysqli_real_escape_string($link, $phone) . "%' $LOGallowed_listsSQL";
// new LIKE part ------------------------ END

}
else
{

// old part comment out // $stmt="SELECT $vicidial_list_fields from $vl_table where phone_number='" . mysqli_real_escape_string($link, $phone) . "' $LOGallowed_listsSQL";

// new LIKE part ------------------------ START
$stmt="SELECT $vicidial_list_fields from $vl_table where phone_number LIKE '%" . mysqli_real_escape_string($link, $phone) . "%' $LOGallowed_listsSQL";
// new LIKE part ------------------------ END

}
}

-----------------------------------------
and for first name, last name and email (changes about line 997):
if (strlen($first_name)>0)
{
// $first_nameSQL = "first_name='" . mysqli_real_escape_string($link, $first_name) . "'"; $SQLctA++;
$first_nameSQL = "first_name LIKE '%" . mysqli_real_escape_string($link, $first_name) . "%'"; $SQLctA++;
}
if (strlen($last_name)>0)
{
if ($SQLctA > 0) {$andA = 'and';}
// $last_nameSQL = "$andA last_name='" . mysqli_real_escape_string($link, $last_name) . "'";
$last_nameSQL = "$andA last_name LIKE '%" . mysqli_real_escape_string($link, $last_name) . "%'";
}
$stmt="SELECT $vicidial_list_fields from $vl_table where $first_nameSQL $last_nameSQL $LOGallowed_listsSQL";
}
else
{
if ( (strlen($email)>0) )
{
// $email_SQL = "email='" . mysqli_real_escape_string($link, $email) . "'";
$email_SQL = "email LIKE '%" . mysqli_real_escape_string($link, $email) . "%'";
$stmt="SELECT $vicidial_list_fields from $vl_table where $email_SQL $LOGallowed_listsSQL";
}

[dspaan]

Hey Blackbird, thanks! I will test admin lead search with your modified code.

I was trying to do 'Search for a lead' wildcard searches from the agent screen today. I modified the searchSQL strings in vdc_db_query.php for that but it's not working.

Code i changed:


Doing partial search for a client named Roeland:


Result:

[dspaan]

I guess you are talking about "Search for a lead" and not about contacts section? Yes you need to make changes in "admin_search_lead.php" file. There are many sql queries, which should be "LIKE" and not "=". Let's start with an example for partial search of phone number beginning at line 931 (newest revision 2971 / red part is original part and green is the new code):

Does this apply to the backend admin search as well as the agent lead search from the agent screen?

[blackbird2306]

Yes you always have to replace the equal = sign with "LIKE" if you want a sql wildcard search. This means it should be:

Code: Select all

$searchSQL .= "alt_phone LIKE '%$phone_number%'";


and not:

Code: Select all

$searchSQL .= "alt_phone='%$phone_number%'";


[dspaan]

Hey Blackbird,

I modified the admin_lead_search.php according to your instructions but when i do a lead search from the agent screen or from the admin screen i still can't search partial terms. What could be wrong?

Here is the modified code:

Code: Select all

         if ($alt_phone_search=="Yes")
      // old exact search part ------------------------ START         
      //      {
      //      $stmt="SELECT $vicidial_list_fields from $vl_table where phone_number='" . mysqli_real_escape_string($link, $phone) . "' or alt_phone='" . mysqli_real_escape_string($link, $phone) . "' or address3='" . mysqli_real_escape_string($link, $phone) . "' $LOGallowed_listsSQL";
      //      }
      //   else
      //      {
      //      $stmt="SELECT $vicidial_list_fields from $vl_table where phone_number='" . mysqli_real_escape_string($link, $phone) . "' $LOGallowed_listsSQL";
      //      }
      //   }
      // old exact search part ------------------------ STOP
      // new LIKE part ------------------------ START
               {
            $stmt="SELECT $vicidial_list_fields from $vl_table where phone_number= LIKE '%" . mysqli_real_escape_string($link, $phone) . "' or alt_phone LIKE '%" . mysqli_real_escape_string($link, $phone) . "' or address3 LIKE '%" . mysqli_real_escape_string($link, $phone) . "' $LOGallowed_listsSQL";
            }
         else
            {
            $stmt="SELECT $vicidial_list_fields from $vl_table where phone_number= LIKE '%" . mysqli_real_escape_string($link, $phone) . "' $LOGallowed_listsSQL";
            }
         }
      // new LIKE part ------------------------ STOP
      else
         {
         if ($lead_id)
            {
            $stmt="SELECT $vicidial_list_fields from $vl_table where lead_id='" . mysqli_real_escape_string($link, $lead_id) . "' $LOGallowed_listsSQL";
            }
         else
            {
            if ( (strlen($status)>0) or (strlen($list_id)>0) or (strlen($user)>0) or (strlen($owner)>0) )
               {
               $statusSQL = '';
               $list_idSQL = '';
               $userSQL = '';
               $ownerSQL = '';
               $called_countSQL = '';
               if (strlen($status)>0)
                  {
                  $statusSQL = "status='" . mysqli_real_escape_string($link, $status) . "'"; $SQLctA++;
                  }
               if (strlen($list_id)>0)
                  {
                  if ($SQLctA > 0) {$andA = 'and';}
                  $list_idSQL = "$andA list_id='" . mysqli_real_escape_string($link, $list_id) . "'"; $SQLctB++;
                  }
               if (strlen($user)>0)
                  {
                  if ( ($SQLctA > 0) or ($SQLctB > 0) ) {$andB = 'and';}
                  $userSQL = "$andB user='" . mysqli_real_escape_string($link, $user) . "'"; $SQLctC++;
                  }
               if (strlen($owner)>0)
                  {
                  if ( ($SQLctA > 0) or ($SQLctB > 0) or ($SQLctC > 0) ) {$andC = 'and';}
                  $ownerSQL = "$andC owner='" . mysqli_real_escape_string($link, $owner) . "'";
                  }
               if (strlen($called_count)>0)
                  {
                  $called_countSQL = "and called_count='" . mysqli_real_escape_string($link, $called_count) . "'";
                  if ($called_count > 99)
                     {$called_countSQL = "and called_count > " . mysqli_real_escape_string($link, $called_count);}
                  }
               $stmt="SELECT $vicidial_list_fields from $vl_table where $statusSQL $list_idSQL $userSQL $ownerSQL $called_countSQL $LOGallowed_listsSQL";
               }
            else
               {
               if ( (strlen($first_name)>0) or (strlen($last_name)>0) )
                  {
                  $first_nameSQL = '';
                  $last_nameSQL = '';
                  if (strlen($first_name)>0)
                  // old exact search part ------------------------ START      
                  //   {
                  //   $first_nameSQL = "first_name='" . mysqli_real_escape_string($link, $first_name) . "'"; $SQLctA++;
                  //   }
                  //if (strlen($last_name)>0)
                  //   {
                  //   if ($SQLctA > 0) {$andA = 'and';}
                  //   $last_nameSQL = "$andA last_name='" . mysqli_real_escape_string($link, $last_name) . "'";
                  //   }
                  //$stmt="SELECT $vicidial_list_fields from $vl_table where $first_nameSQL $last_nameSQL $LOGallowed_listsSQL";
                  //}
                   //else
                  //{
                  //if ( (strlen($email)>0) )
                  //   {
                  //   $email_SQL = "email='" . mysqli_real_escape_string($link, $email) . "'";
                  //   $stmt="SELECT $vicidial_list_fields from $vl_table where $email_SQL $LOGallowed_listsSQL";
                  //   }
                  // old exact search part ------------------------ STOP
                  // new LIKE part ------------------------ START
                                       {
                     $first_nameSQL = "first_name LIKE '%" . mysqli_real_escape_string($link, $first_name) . "'"; $SQLctA++;
                     }
                  if (strlen($last_name)>0)
                     {
                     if ($SQLctA > 0) {$andA = 'and';}
                     $last_nameSQL = "$andA last_name LIKE '%" . mysqli_real_escape_string($link, $last_name) . "'";
                     }
                  $stmt="SELECT $vicidial_list_fields from $vl_table where $first_nameSQL $last_nameSQL $LOGallowed_listsSQL";
                  }
               else

[blackbird2306]

You did not do it the way I wrote it! Please read it all again (just copy and paste it), because every single character is important and can cause problems! Let's start with "admin_lead_search.php", which is only responsible for admin "Search For A Lead" function and not for agent screen.

There is an equal sign after phone_number= to much and a missing % percent sign at the end:

$stmt="SELECT $vicidial_list_fields from $vl_table where phone_number= LIKE '%" . mysqli_real_escape_string($link, $phone) . "%' or alt_phone LIKE '%" . mysqli_real_escape_string($link, $phone) . "%' or address3 LIKE '%" . mysqli_real_escape_string($link, $phone) . "%' $LOGallowed_listsSQL";
}
else
{
$stmt="SELECT $vicidial_list_fields from $vl_table where phone_number= LIKE '%" . mysqli_real_escape_string($link, $phone) . "%' $LOGallowed_listsSQL";
}
________________________________________________________________________________________________
Further down the same missing percent % sign at the end for first_name and last_name query:

$first_nameSQL = "first_name LIKE '%" . mysqli_real_escape_string($link, $first_name) . "%' "; $SQLctA++;

$last_nameSQL = "$andA last_name LIKE '%" . mysqli_real_escape_string($link, $last_name) . "%' ";

________________________________________________________________________________________________
For agent lead search function you need to apply your changes in "vdc_db_query.php", which is not the same thing like admin search:
http://vicidial.org/VICIDIALforum/viewtopic.php?f=4&t=38174&p=134398#p134378

http://vicidial.org/VICIDIALforum/viewtopic.php?f=4&t=38174&p=134398#p134382
$searchSQL .= "alt_phone LIKE '%$phone_number%'";
and not:
$searchSQL .= "alt_phone='%$phone_number%'";

[dspaan]

Oops! I totally missed that. Thanks for marking the changes in red. It's working now!

One last question, do you know how it is possible as agent to search in the first name field while leaving the other fields blank? The agent lead search does not allow this, but we use that field for storing the company name.

[blackbird2306]

You are welcome. Yes this is also easy to change in "vdc_db_query.php" file (red part original commented out and green the new changes):

// elseif (strlen($last_name) > 0)
elseif (strlen($last_name) > 0 || strlen($first_name) > 0)
{
### last name entered, search by this and other fields
// $searchSQL = "last_name=\"$last_name\"";
if (strlen($last_name) > 0)
{$searchSQL = "last_name=\"$last_name\"";}
if (strlen($first_name) > 0)
{
if (strlen($searchSQL) > 10)
{$searchSQL .= " and ";}
$searchSQL .= "first_name=\"$first_name\"";
}
if (strlen($city) > 0)
{
if (strlen($searchSQL) > 10)
{$searchSQL .= " and ";}
$searchSQL .= "city=\"$city\"";
}

Here for copy and paste:

Code: Select all

// elseif (strlen($last_name) > 0)
elseif (strlen($last_name) > 0 || strlen($first_name) > 0)
         {
         ### last name entered, search by this and other fields
         // $searchSQL = "last_name=\"$last_name\"";
         if (strlen($last_name) > 0)
            {$searchSQL = "last_name=\"$last_name\"";}
         if (strlen($first_name) > 0)
            {
            if (strlen($searchSQL) > 10)
               {$searchSQL .= " and ";}
            $searchSQL .= "first_name=\"$first_name\"";
            }
         if (strlen($city) > 0)
            {
            if (strlen($searchSQL) > 10)
               {$searchSQL .= " and ";}
            $searchSQL .= "city=\"$city\"";
            }


[dspaan]

Thanks again, working fine!