lms-quicksearch.php.patch

   1 diff -ur lms/modules/quicksearch.php lms.new//modules/quicksearch.php
   2 --- lms/modules/quicksearch.php 2011-03-18 09:59:24.000000000 +0100
   3 +++ lms.new//modules/quicksearch.php    2011-03-29 20:41:29.772258200 +0200
   4 @@ -1,7 +1,7 @@
   5  <?php
   6
   7  /*
   8 - * LMS version 1.11.12 Aris
   9 + * LMS version 1.11-cvs
  10   *
  11   *  (C) Copyright 2001-2011 LMS Developers
  12   *
  13 @@ -21,7 +21,7 @@
  14   *  Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307,
  15   *  USA.
  16   *
  17 - *  $Id$
  18 + *  $Id$
  19   */
  20
  21  function macformat($mac)
  22 @@ -75,12 +75,13 @@
  23         case 'customer':
  24                 if(isset($_GET['ajax'])) // support for AutoSuggest
  25                 {
  26 -                       $candidates = $DB->GetAll('SELECT id, email, address, post_address, deleted,
  27 +                       $candidates = $DB->GetAll('SELECT id, email, address, post_name, post_address, deleted,
  28                             '.$DB->Concat('UPPER(lastname)',"' '",'name').' AS username
  29                                 FROM customersview
  30                                 WHERE '.(preg_match('/^[0-9]+$/', $search) ? 'id = '.intval($search).' OR ' : '').'
  31                                         LOWER('.$DB->Concat('lastname',"' '",'name').') ?LIKE? LOWER(\'%'.$search.'%\')
  32                                         OR LOWER(address) ?LIKE? LOWER(\'%'.$search.'%\')
  33 +                                       OR LOWER(post_name) ?LIKE? LOWER(\'%'.$search.'%\')
  34                                         OR LOWER(post_address) ?LIKE? LOWER(\'%'.$search.'%\')
  35                                         OR LOWER(email) ?LIKE? LOWER(\'%'.$search.'%\')
  36                                 ORDER by deleted, username, email, address
  37 @@ -105,6 +106,10 @@
  38                                     $descriptions[$row['id']] = escape_js(trans('Address:').' '.$row['address']);
  39                                     continue;
  40                                 }
  41 +                               else if (preg_match("~$search~i",$row['post_name'])) {
  42 +                                   $descriptions[$row['id']] = escape_js(trans('Name:').' '.$row['post_name']);
  43 +                                   continue;
  44 +                               }
  45                                 else if (preg_match("~$search~i",$row['post_address'])) {
  46                                     $descriptions[$row['id']] = escape_js(trans('Address:').' '.$row['post_address']);
  47                                     continue;
  48 @@ -156,20 +161,37 @@
  49         case 'node':
  50                 if(isset($_GET['ajax'])) // support for AutoSuggest
  51                 {
  52 -                       $candidates = $DB->GetAll('SELECT n.id, n.name, INET_NTOA(ipaddr) as ip,
  53 -                           INET_NTOA(ipaddr_pub) AS ip_pub, mac
  54 -                               FROM vnodes n
  55 -                               WHERE ('.(preg_match('/^[0-9]+$/',$search) ? 'n.id = '.intval($search).' OR ' : '').'
  56 -                                       LOWER(n.name) ?LIKE? LOWER(\'%'.$search.'%\')
  57 -                                       OR INET_NTOA(ipaddr) ?LIKE? \'%'.$search.'%\'
  58 -                                       OR INET_NTOA(ipaddr_pub) ?LIKE? \'%'.$search.'%\'
  59 -                                       OR LOWER(mac) ?LIKE? LOWER(\'%'.macformat($search).'%\')
  60 -                                       )
  61 -                                       AND NOT EXISTS (
  62 -                        SELECT 1 FROM customerassignments a
  63 -                                   JOIN excludedgroups e ON (a.customergroupid = e.customergroupid)
  64 -                                           WHERE e.userid = lms_current_user() AND a.customerid = n.ownerid)
  65 -                               ORDER BY n.name LIMIT 15');
  66 +                   // Build different query for each database engine,
  67 +                   // MySQL is slow here when vnodes view is used
  68 +                   if ($CONFIG['database']['type'] == 'postgres')
  69 +                           $sql_query = 'SELECT n.id, n.name, INET_NTOA(ipaddr) as ip,
  70 +                               INET_NTOA(ipaddr_pub) AS ip_pub, mac
  71 +                                   FROM vnodes n
  72 +                                   WHERE %where
  73 +                               ORDER BY n.name LIMIT 15';
  74 +            else
  75 +                           $sql_query = 'SELECT n.id, n.name, INET_NTOA(ipaddr) as ip,
  76 +                               INET_NTOA(ipaddr_pub) AS ip_pub, mac
  77 +                                   FROM nodes n
  78 +                                   JOIN (
  79 +                        SELECT nodeid, GROUP_CONCAT(mac SEPARATOR \',\') AS mac
  80 +                        FROM macs
  81 +                        GROUP BY nodeid
  82 +                    ) m ON (n.id = m.nodeid)
  83 +                                   WHERE %where
  84 +                               ORDER BY n.name LIMIT 15';
  85 +
  86 +            $sql_where = '('.(preg_match('/^[0-9]+$/',$search) ? 'n.id = '.intval($search).' OR ' : '').'
  87 +                               LOWER(n.name) ?LIKE? LOWER(\'%'.$search.'%\')
  88 +                               OR INET_NTOA(ipaddr) ?LIKE? \'%'.$search.'%\'
  89 +                               OR INET_NTOA(ipaddr_pub) ?LIKE? \'%'.$search.'%\'
  90 +                               OR LOWER(mac) ?LIKE? LOWER(\'%'.macformat($search).'%\'))
  91 +                           AND NOT EXISTS (
  92 +                    SELECT 1 FROM customerassignments a
  93 +                    JOIN excludedgroups e ON (a.customergroupid = e.customergroupid)
  94 +                               WHERE e.userid = lms_current_user() AND a.customerid = n.ownerid)';
  95 +
  96 +                       $candidates = $DB->GetAll(str_replace('%where', $sql_where,     $sql_query));
  97
  98                         $eglible=array(); $actions=array(); $descriptions=array();
  99                         if ($candidates)