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.