Customising Intelligent Search

[WHMCS Anwar]

Intelligent Search can be extended to allow you to perform custom searches and return the results of entries that would not otherwise be searchable.  This can be custom data anywhere within your WHMCS database, that you wish to find.

To accomplish this, we will be using the Intelligent Search hook point.   We will elaborate on the sample code provided, which will form the basis of this guide and be adapted for constructing our own custom search. 

<?php
add_hook('IntelligentSearch', 1, function ($vars) {
    /**
     * This is an example of array return for an Intelligent Search.
     * This format is supported in the blend WHMCS Admin Template.
     * Any template based on blend and updated to WHMCS 7.7+ is also supported.
     */
    $searchResults = array();

    // look for exact matches in client notes field
    $result = \WHMCS\Database\Capsule::table('tblclients')
        ->where('notes', $vars['searchTerm'])
        ->get();

    foreach ($result as $client) {
        $searchResults[] = [
          	// The title of the search result. This is required.
            'title' => $client->firstname . ' ' . $client->lastname,
          
          	// The destination url of the search result. This is required.
            'href' => 'clientssummary.php?userid=' . $client->id,
          
          	// An optional subtitle for the search result.
            'subTitle' => $client->email,
          
          	// A font-awesome icon for the search result. Defaults to 'fal fa-star' if not defined.
            'icon' => 'fal fa-user',
        ];
    }
    return $searchResults;
});

Here, we can see that intelligent search results have been expanded to successfully retrieve client note entries.

Using the same concept, we are now going to show you, how to search within Ticket Notes.

Construct a SQL Query for the Data to be Retrieved

We will need to identify the data fields which we need to search, retrieve and then display in the search results.  Once we have identified the particular fields we require, we will construct an SQL Query that can display the data we require.

We would like to search the messages in ticket notes, so the tblticketnotes would precisely be the table we need.  However, for our search results to have more meaning, it would be useful to have the ticket title for the note.  As this field resides in another related table: tbltickets, we will require an INNER JOIN in our SQL query ON,  tbltickets.id = tblticketnotes.ticketid

Now that we know exactly the data we require, and where to join; using our favourite SQL query builder (or editor) we can go ahead and construct our query:

 

Once we have constructed the query, it would be prudent to preview the query, to see if the results yield the dataset that we require:

As you can see from the query results above, we are successfully able to find all ticket notes on the system as well as their ticket titles and other useful data to display in our search results.  It is important that the results contain only the data required, in order to maximize efficiency.

Constructing the Query for the Hook

We must create a Laravel Query equivalent for the raw SQL query above when using Database Capsule.  For more information, visit https://laravel-guide.readthedocs.io/en/latest/queries/#joins

Using our raw SQL query, we should arrive at the following query for our hook:

$query = Capsule::table('tblticketnotes')
    ->select(
        'tblticketnotes.ticketid',
        'tblticketnotes.date', 
        'tblticketnotes.message',
        'tbltickets.title'
    )

    ->join('tbltickets', 'tbltickets.id', '=', 'tblticketnotes.ticketid')
    ->where('tblticketnotes.message', 'LIKE', '%' . $vars['searchTerm'] . '%') 
    ->orderBy('date', 'desc')
    ->get();

Once we tweak the search result output to suite our needs, our final hook should look like this:

<?php

    use WHMCS\Database\Capsule;

    if (!defined("WHMCS"))
    die("This file cannot be accessed directly");

    add_hook('IntelligentSearch', 1, function ($vars) {

    $searchResults = array();
    $query = Capsule::table('tblticketnotes')
    ->select(
        'tblticketnotes.ticketid',
        'tblticketnotes.date', 
        'tblticketnotes.message',
        'tbltickets.title'
    )

    ->join('tbltickets', 'tbltickets.id', '=', 'tblticketnotes.ticketid')
    ->where('tblticketnotes.message', 'LIKE', '%' . $vars['searchTerm'] . '%') 
    ->orderBy('date', 'desc')
    ->get();
        foreach ($query as $show) {
            $searchResults[] = [
                // The title of the search result. This is required.
                'title' => 'Ticket Notes - Support Ticket ID: ' . $show->ticketid, 

                // The destination url of the search result. This is required.
                'href' => 'supporttickets.php?action=view&id=' . $show->ticketid , 

                // The title of the search result. This is required.
                'subTitle' => $show->title . ' ' . $show->date . ' ' . $show->message, 

                // A font-awesome icon for the search result. 
                // Defaults to 'fal fa-star' if not defined.
                'icon' => 'fal fa-star', 
            ];
        }
        return $searchResults;
    });

To use the hook, we save it as a PHP file and place it in our /includes/hooks/ folder and simply perform a search:

Performing a search, should now include results from Ticket Notes.

 

At the time of writing this post, this script was tested on the latest stable release of WHMCS 8.5.1 and should work with any that fall under Active Support as per the LTS schedule here: https://docs.whmcs.com/Long_Term_Support#WHMCS_Version_.26_LTS_Schedule

If you have any feedback or questions, please feel free to reply to this thread!