Autocomplete in the Calendar UI

[Guido1982]

Okay, so I've done some quick 'n dirty work on the 'Add Event' UI in the calendar. I've edited the 'addEventUI.php' file as follows:

PHP Code:

    <!-- Dynamically add jQuery UI from a CDN -->
    <script type="text/javascript">
    var script = document.createElement("script");
    script.type = "text/javascript";
    script.src = "https://code.jquery.com/ui/1.11.4/jquery-ui.min.js";
    var uiCSS = document.createElement("link");
    uiCSS.type = "text/css";
    uiCSS.rel = "stylesheet";
    uiCSS.href = "https://code.jquery.com/ui/1.11.4/themes/cupertino/jquery-ui.css";
    document.head.appendChild(script);
    document.head.appendChild(uiCSS);
    </script>
    <!-- End include of jQuery UI -->
    <!-- Autocomplete jQuery -->
  <script>
  jQuery(window).load(function(){
        jQuery( "#account_autocomplete" ).autocomplete({
        // Beware: Autocomplete sets a search term as GET parameter to the URL
        // The php file has to take this into account and use the search term
        // in its mySQL query
          source: "JSON_Accounts.php",
        // On open we have to set a high z-index to the UL, else it will
        // fall behind the 'addEvent' UI box
          open: function(){
            jQuery(this).autocomplete('widget').css('z-index', 999999);
            return false;
          },
          // Here we add the result on selection to a hidden input field
          // That the calendar module uses to connect a crmentity
          select: function( event, ui) {
              // Add crmentity id to hidden input
              jQuery('input[name=parent_id]').attr('value',ui.item.value);
              // Also update the activity name with the activity type and account name
              jQuery('input[name=subject]').val(jQuery('#activitytype option:selected').text() + " " + ui.item.label);
              // Overwrite the input field with the account name in stead of the crmentity
              jQuery('#account_autocomplete').val(ui.item.label);
              return false;
          },
          // Also update the input field with the label during focus
          focus: function( event, ui ) {
              // Overwrite the input field with the account name in stead of the crmentity
              jQuery('#account_autocomplete').val(ui.item.label);
              return false;
          },
          minLength: 2
        // Make sure 'autocomplete' is turned on for this field
        }).attr("autocomplete","on")
        // Add '_renderItem' to use HTML in the results, so we can use
        // things like address fields and better readable markup
        .autocomplete( "instance" )._renderItem = function( ul, item ) {
          return jQuery( "<li>" )
            .append( "<a>" + "<b>" + item.label + "</b>" + "<br>" + item.street + "<br>" + item.code + " " + item.city + "</a>" )
            .appendTo( ul );
        };
    });
  </script>
    <!-- End autocomplete --> 


Added to include jQuery UI and the cupertino theme (which is what the calendar uses), and I added:

PHP Code:

            <tr>
                <td nowrap align="right"><b>Account</b></td>
                <td align="left"><input name="account_autocomplete" type="text" id="account_autocomplete" value="" style="width:50%;"></td>
            </tr> 


To add the field I use for autocomplete. The JSON file for accounts looks like this:

PHP Code:

<?php

// error_reporting(E_ALL);
// ini_set("display_errors", "on");

    include_once('vtlib/Vtiger/Module.php');
    global $adb;
    
    // Lines 11-24 from http://www.wowww.nl/2014/02/01/jquery-autocomplete-tutorial-php-mysql/
    /* prevent direct access to this page */
    $isAjax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND
    strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest';
    if(!$isAjax) {
      $user_error = 'Access denied - direct call is not allowed...';
      trigger_error($user_error, E_USER_ERROR);
    }
    ini_set('display_errors',1);
     
    /* if the 'term' variable is not sent with the request, exit */
    if ( !isset($_REQUEST['term']) ) {
        exit;
    }
    
    // Start the empty 'allaccounts' array
    $allaccounts = array();
    
    // Get the term from the jQuery autocomplete GET request
    $term = trim(strip_tags($_GET['term'])); 
    
    // Get the accounts that match part of the search term
    $accountresults = $adb->query("SELECT accountid, account_no, accountname FROM vtiger_account WHERE accountname LIKE '%$term%'");

    // Loop accounts
    while($account=$adb->fetch_array($accountresults)) {
        
        // Create empty JSON array for this account
        $JsonAccount = array();
        
        // 'vtiger_account' table has no address data, so another query for the table that has those,
        // for the current account (in the array parameter)
        $accountaddressres = $adb->pquery("SELECT accountaddressid, ship_code, ship_street, ship_city FROM vtiger_accountshipads WHERE accountaddressid=?", array($account[accountid]));
        
        // Loop the shipping address resultset
        while ($address=$adb->fetch_array($accountaddressres)) {
            // Add the account name and address as the JSON label
            $JsonAccount['label'] = $account[accountname];
            $JsonAccount['value'] = $account[accountid];
            $JsonAccount['code'] = $address[ship_code];
            $JsonAccount['street'] = $address[ship_street];
            $JsonAccount['city'] = $address[ship_city];
        }
        
        // Push the current account with address info to the 'allaccounts' array
        $allaccounts[] = $JsonAccount;
    }
    
    // echo "<pre>";
    // print_r($allaccounts);
    // echo "</pre>";
    
    echo json_encode($allaccounts, JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE);

?>

Obviously this gets the job done, but is not the most elegant solution. My main problems now are:

• How to get the label for the input autocomplete field to take the system variable for the 'Accounts' module. I do want to use this like this and not use the standard 'related to' because I feel this requires too many clicks. I intend to re-design the entire "Add Event" UI in the future to increase workflow speed and use more keyboard input.
  
• I have to place the JSON_Accounts.php file in the root, because I can't include the 'Module.php' file otherwise. Joe suggested that other module files could use this becuase they use the 'set_include_path()' function, but I don't see that function being used in the standard module files. My guess is that the other files can use includes as if they were files in the root because they get passed through the index.php root file. My file is not registered by the system and so does not pass through the index.php file. Sadly, I don't know how to change this so any help would be appreciated.
  
• I'm thinking the Calendar module already uses a 'jQuery UI' include (haven't done the full research on this), so maybe the double include could be deleted?
  
• Probably the javascript could be moved to a separate file and included in the 'AddEventUI.php' file to make the code less messy.
  
• In the future, I'd like to offer the possibility to connect an event to both an Account as well as a Sales Order. I think this will require a new column in the database and some code that saves this when the event is saved. If anyone has some pointers on this, help is appreciated.
  

[Guido1982]

Of course the first question was too easy:

PHP Code:

<?php echo $app_strings['Accounts']?>

[joebordes]

In general it looks good

• I have to place the JSON_Accounts.php file in the root, because I can't include the 'Module.php' file otherwise. Joe suggested that other module files could use this becuase they use the 'set_include_path()' function, but I don't see that function being used in the standard module files. My guess is that the other files can use includes as if they were files in the root because they get passed through the index.php root file. My file is not registered by the system and so does not pass through the index.php file. Sadly, I don't know how to change this so any help would be appreciated.
  

Calendar4You uses set_include_path, but I wouldn't recommend that. You really don't need it at all if you pass your code through index.php which is what you should do because that guarantees you are protected by the system's validations and makes it harder for your code to get hacked.
You can put your script wherever you want, in any module and then call it like this if you don't want the menus and rest of the layout:

Code:

http://your_server/your_corebos/index.php?module={module_where_you_put_your_script}&action={module_where_you_put_your_script}Ajax&file={your_script_with_no_php_extension}

or like this if you want it be executed after loading the menu and toolbar:

Code:

http://your_server/your_corebos/index.php?module={module_where_you_put_your_script}&action={your_script_with_no_php_extension}

with that your code is always executed from the root of the install so your includes are relative to that.

• I'm thinking the Calendar module already uses a 'jQuery UI' include (haven't done the full research on this), so maybe the double include could be deleted?
  

Yes, you are correct, it loads jquery-ui-1.10.2.custom.min.js

• Probably the javascript could be moved to a separate file and included in the 'AddEventUI.php' file to make the code less messy.
  

Maybe you can get rid of the whole thing, but if you need to load a script you should be able to register a HEADERSCRIPT link

• In the future, I'd like to offer the possibility to connect an event to both an Account as well as a Sales Order. I think this will require a new column in the database and some code that saves this when the event is saved. If anyone has some pointers on this, help is appreciated.
  

You may be interested in the getFieldAutocomplete() and/or getReferenceAutocomplete() webservice functions. We use these for our satellite apps and also to do exactly what you are trying to do from inside the application.
These are already prepared to be executed via the ExecuteFunctions.php script in modules/Vtiger

For example, try typing this into your browser:

Code:

http://your_server/your_corebos/index.php?module=Utilities&action=UtilitiesAjax&file=ExecuteFunctions&functiontocall=getReferenceAutocomplete&searchinmodule=Accounts&limit=5&term=vt

which will return the first 5 accounts whose name start with the term "vt"

[Guido1982]

Hey Joe,

Thanks, I will surely try placing my php files back into the Calendar4You folder. So if I understand correctly, in my jQuery autocomplete function, I could call:

Code:

http://your_server/your_corebos/index.php?module=Calendar4You&action=JSON_Accounts

to make it happen?

I will surely try you built-in autocomplete but for this particular extension I think I'll stick to my solution because it offers to get also the address in the autocomplete picklist (this company has a lot of customers from the same franchise, showing the address also makes selecting the right one easier).

I'm busy creating a link between activity and sales orders as well. I will post here my solution.

EDIT
In the meantime I can confirm your method for pointing to the JSON files through the index URL. I did have to add the 'Ajax' module action, because loading the header first corrupted the JSON, but it works like a charm. Thanks, now I can keep my files neatly in the Calendar4You folder.

[joebordes]

the call you pasted will call your script but with all the menu and tool bars, to get the raw feed you have to call it like this:

Code:

http://your_server/your_corebos/index.php?module=Calendar4You&action=Calendar4YouAjax&file=JSON_Accounts

[Guido1982]

Exactly, thanks. Come to think of it, I use a second file to call the salesorder, but now I can add a parameter to my URL so that I can merge my JSON files into one and make the module choice in the file itself based on the extra parameter.

[Guido1982]

By the way, the standardly included jQuery UI is a custom build and does not include the autocomplete, so I ended up using the extra one anyway.

---

I've updated my code the following way:

I combined the two JSON files I made into this:

PHP Code:

<?php

    include_once('vtlib/Vtiger/Module.php');
    global $adb;
    
    // Lines 11-24 from http://www.wowww.nl/2014/02/01/jquery-autocomplete-tutorial-php-mysql/
    /* prevent direct access to this page */
    $isAjax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND
    strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest';
    if(!$isAjax) {
      $user_error = 'Access denied - direct call is not allowed...';
      trigger_error($user_error, E_USER_ERROR);
    }
    ini_set('display_errors',1);
     
    /* if the 'term' variable is not sent with the request, exit */
    if ( !isset($_REQUEST['term']) ) {
        exit;
    }
    
    // Execute code when autocomplete is requested via 'Accounts' parameter in GET request
    if ( isset($_REQUEST['searchmodule']) && $_REQUEST['searchmodule'] == 'Accounts' ) {
    
        // Start the empty 'allaccounts' array
        $allaccounts = array();
        
        // Get the term from the jQuery autocomplete GET request
        $term = trim(strip_tags($_GET['term'])); 
        
        // Get the accounts that match part of the search term
        $accountresults = $adb->query("SELECT accountid, account_no, accountname FROM vtiger_account WHERE accountname LIKE '%$term%'");

        // Loop accounts
        while($account=$adb->fetch_array($accountresults)) {
            
            // Create empty JSON array for this account
            $JsonAccount = array();
            
            // 'vtiger_account' table has no address data, so another query for the table that has those,
            // for the current account (in the array parameter)
            $accountaddressres = $adb->pquery("SELECT accountaddressid, ship_code, ship_street, ship_city FROM vtiger_accountshipads WHERE accountaddressid=?", array($account[accountid]));
            
            // Loop the shipping address resultset
            while ($address=$adb->fetch_array($accountaddressres)) {
                // Add the account name and address as the JSON label
                $JsonAccount['label'] = $account[accountname];
                $JsonAccount['value'] = $account[accountid];
                $JsonAccount['code'] = $address[ship_code];
                $JsonAccount['street'] = $address[ship_street];
                $JsonAccount['city'] = $address[ship_city];
            }
            
            // Push the current account with address info to the 'allaccounts' array
            $allaccounts[] = $JsonAccount;
        }
        
        echo json_encode($allaccounts, JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE);
    
    // Execute code when autocomplete is requested via 'SalesOrders' parameter in GET request
    } elseif ( isset($_REQUEST['searchmodule']) && $_REQUEST['searchmodule'] == 'SalesOrders' ) {
        
        // Start the empty 'allsalesorders' array
        $allsalesorders = array();
        
        // Get the term from the jQuery autocomplete GET request
        $term = trim(strip_tags($_GET['term'])); 
        
        // Get the salesorders that match part of the search term
        $soresults = $adb->query("SELECT subject, salesorderid, salesorder_no, accountid FROM vtiger_salesorder WHERE salesorder_no LIKE '%$term%'");

        // Loop salesorders
        while($so=$adb->fetch_array($soresults)) {
            
            // Create empty JSON array for this salesorder
            $JsonSO = array();
            
            // Start filling it
            $JsonSO['value'] = $so[salesorderid];
            $JsonSO['label'] = $so[salesorder_no];
            $JsonSO['subject'] = $so[subject];
                    
            // 'vtiger_salesorder' table only has account id,
            // so query the accounts table also for the account name
            $soAccountRes = $adb->pquery("SELECT accountname FROM vtiger_account WHERE accountid=?", array($so[accountid]));
            
            // Loop the results for the account name
            while ($account=$adb->fetch_array($soAccountRes)) {
                // Add the account name and address as the JSON label
                $JsonSO['accountname'] = $account[accountname];
            }
            
            // Push the current account with address info to the 'allaccounts' array
            $allsalesorders[] = $JsonSO;
        }
        
        echo json_encode($allsalesorders, JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE);
    }

?>

So now the JSON file decides which JSON to return based on a GET parameter. Obviously I needed to change the jQuery source call for this, so now that looks like:

Code:

http://crmdevelop.cbx-nederland.nl/index.php?module=Calendar4You&action=Calendar4YouAjax&searchmodule=Accounts&file=JSON

For the Accounts autocomplete field. Next up to do is to have the salesorders JSON check if there was an account already selected and filter the sales orders to the ones that are related to that account.

[Guido1982]

Now I've done that as well. The jQuery is now expanded. The autocomplete call for sales orders is now like:

PHP Code:

        jQuery( "#so_autocomplete" ).autocomplete({
        // Beware: Autocomplete sets a search term as GET parameter to the URL
        // The php file has to take this into account and use the search term
        // in its mySQL query
          source: "http://crmdevelop.cbx-nederland.nl/index.php?module=Calendar4You&action=Calendar4YouAjax&searchmodule=SalesOrders&file=JSON",
        // On open we have to set a high z-index to the UL, else it will
        // fall behind the 'addEvent' UI box
          open: function(){
            jQuery(this).autocomplete('widget').css('z-index', 999999);
            return false;
          },
          // Here we add the result on selection to a hidden input field
          // That the calendar module uses to connect a crmentity
          select: function( event, ui) {
              // Add crmentity id to hidden input
              jQuery('input[name=so_rel]').attr('value',ui.item.value);
              // Overwrite the input field with the salesorder name in stead of the crmentity
              jQuery('#so_autocomplete').val(ui.item.label);
              return false;
          },
          // Also update the input field with the label during focus
          focus: function( event, ui ) {
              // Overwrite the input field with the salesorder name in stead of the crmentity
              jQuery('#so_autocomplete').val(ui.item.label);
              return false;
          },
          minLength: 2
        // Make sure browser 'autocomplete' is turned on for this field
        }).attr("autocomplete","off")
        // Add '_renderItem' to use HTML in the results, so we can use
        // things like address fields and better readable markup
        .autocomplete( "instance" )._renderItem = function( ul, item ) {
          return jQuery( "<li>" )
            .append( "<a>" + "<b>" + item.label + "</b>" + "<br>" + item.subject + "<br>" + item.accountname + "</a>" )
            .appendTo( ul );
        };
        // During typing, dynamically change the autocomplete source to check for accountID
        jQuery("#so_autocomplete").keyup(function(){
            jQuery( "#so_autocomplete" ).autocomplete("option","source","http://crmdevelop.cbx-nederland.nl/index.php?module=Calendar4You&action=Calendar4YouAjax&searchmodule=SalesOrders&accountid="+jQuery('input[name=parent_id]').val()+"&file=JSON");
        }); 


And a few line changed in the JSON.php file:

PHP Code:

        // Get the salesorders that match part of the search term
        // Check to see if there was an account ID passed in the URL
        if (isset($_REQUEST['accountid']) && $_REQUEST['accountid'] != "") {
            // If account ID was set, use it in the query
            $soAccountID = $_REQUEST['accountid'];
            $soresults = $adb->query("SELECT subject, salesorderid, salesorder_no, accountid FROM vtiger_salesorder WHERE salesorder_no LIKE '%$term%' AND accountid = $soAccountID");
        } else {
            // If no account was selected
            $soresults = $adb->query("SELECT subject, salesorderid, salesorder_no, accountid FROM vtiger_salesorder WHERE salesorder_no LIKE '%$term%'");
        } 


Replaced the old query. Now, when you've selected an account, the sales order autocomplete will filter to show only sales orders from this account.

[joebordes]

Wow! Looking good.

[Guido1982]

Hey Joe,

Thanks. I'll be adding this to the inventory pretty soon also. This way people that now use other modules to have autocomplete on product selection will have an incentive to switch to corebos.

I still have a problem with the sales orders being related to the activities, as described in my other post. If someone has any ideas about this I'd be happy to know, since I've hit kind of a dead end for now on this.