crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2015
  32  * $Id$
  33  *
  34  */
  35 abstract class CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery {
  36
  37   /**
  38    * @var string
  39    */
  40   protected $name;
  41
  42   /**
  43    * @var string
  44    */
  45   protected $label;
  46
  47   /**
  48    * Class constructor.
  49    *
  50    * @param string $name
  51    * @param string $label
  52    */
  53   public function __construct($name, $label) {
  54     $this->name = $name;
  55     $this->label = $label;
  56   }
  57
  58   /**
  59    * Get label.
  60    *
  61    * @return string
  62    */
  63   public function getLabel() {
  64     return $this->label;
  65   }
  66
  67   /**
  68    * Get name.
  69    *
  70    * @return string
  71    */
  72   public function getName() {
  73     return $this->name;
  74   }
  75
  76   /**
  77    * Execute a query and write out a page worth of matches to $detailTable.
  78    *
  79    * TODO: Consider removing $entityIDTableName from the function-signature. Each implementation could be
  80    * responsible for its own temp tables.
  81    *
  82    * TODO: Understand why $queryLimit and $detailLimit are different
  83    *
  84    * @param string $queryText
  85    *   A string of text to search for.
  86    * @param string $entityIDTableName
  87    *   A temporary table into which we can write a list of all matching IDs.
  88    * @param string $detailTable
  89    *   A table into which we can write details about a page worth of matches.
  90    * @param array|NULL $queryLimit overall limit (applied when building $entityIDTableName)
  91    *                   NULL if no limit; or array(0 => $limit, 1 => $offset)
  92    * @param array|NULL $detailLimit final limit (applied when building $detailTable)
  93    *                   NULL if no limit; or array(0 => $limit, 1 => $offset)
  94    * @return array
  95    *   keys: match-descriptor
  96    *   - count: int
  97    */
  98   public abstract function fillTempTable($queryText, $entityIDTableName, $detailTable, $queryLimit, $detailLimit);
  99
 100   /**
 101    * @return bool
 102    */
 103   public function isActive() {
 104     return TRUE;
 105   }
 106
 107   /**
 108    * @param $tables
 109    * @param $extends
 110    */
 111   public function fillCustomInfo(&$tables, $extends) {
 112     $sql = "
 113 SELECT     cg.table_name, cf.column_name
 114 FROM       civicrm_custom_group cg
 115 INNER JOIN civicrm_custom_field cf ON cf.custom_group_id = cg.id
 116 WHERE      cg.extends IN $extends
 117 AND        cg.is_active = 1
 118 AND        cf.is_active = 1
 119 AND        cf.is_searchable = 1
 120 AND        cf.html_type IN ( 'Text', 'TextArea', 'RichTextEditor' )
 121 ";
 122
 123     $dao = CRM_Core_DAO::executeQuery($sql);
 124     while ($dao->fetch()) {
 125       if (!array_key_exists($dao->table_name, $tables)) {
 126         $tables[$dao->table_name] = array(
 127           'id' => 'entity_id',
 128           'fields' => array(),
 129         );
 130       }
 131       $tables[$dao->table_name]['fields'][$dao->column_name] = NULL;
 132     }
 133   }
 134
 135
 136   /**
 137    * @param string $queryText
 138    * @param array $tables
 139    *   A list of places to query. Keys may be:.
 140    *   - sql: an array of SQL queries to execute
 141    *   - final: an array of SQL queries to execute at the end
 142    *   - *: All other keys are treated as table names
 143    * @return array
 144    *   keys: match-descriptor
 145    *   - count: int
 146    *   - files: NULL | array
 147    */
 148   public function runQueries($queryText, &$tables, $entityIDTableName, $limit) {
 149     $sql = "TRUNCATE {$entityIDTableName}";
 150     CRM_Core_DAO::executeQuery($sql);
 151
 152     $files = NULL;
 153
 154     foreach ($tables as $tableName => $tableValues) {
 155       if ($tableName == 'final') {
 156         continue;
 157       }
 158       else {
 159         if ($tableName == 'sql') {
 160           foreach ($tableValues as $sqlStatement) {
 161             $sql = "
 162 REPLACE INTO {$entityIDTableName} ( entity_id )
 163 $sqlStatement
 164 {$this->toLimit($limit)}
 165 ";
 166             CRM_Core_DAO::executeQuery($sql);
 167           }
 168         }
 169         elseif ($tableName == 'file') {
 170           $searcher = CRM_Core_BAO_File::getSearchService();
 171           if (!($searcher && CRM_Core_Permission::check('access uploaded files'))) {
 172             continue;
 173           }
 174
 175           $query = $tableValues + array(
 176             'text' => CRM_Utils_QueryFormatter::singleton()
 177             ->format($queryText, CRM_Utils_QueryFormatter::LANG_SOLR),
 178           );
 179           list($intLimit, $intOffset) = $this->parseLimitOffset($limit);
 180           $files = $searcher->search($query, $intLimit, $intOffset);
 181           $matches = array();
 182           foreach ($files as $file) {
 183             $matches[] = array('entity_id' => $file['xparent_id']);
 184           }
 185           if ($matches) {
 186             $insertSql = CRM_Utils_SQL_Insert::into($entityIDTableName)->usingReplace()->rows($matches)->toSQL();
 187             CRM_Core_DAO::executeQuery($insertSql);
 188           }
 189         }
 190         else {
 191           $fullTextFields = array(); // array (string $sqlColumnName)
 192           $clauses = array(); // array (string $sqlExpression)
 193
 194           foreach ($tableValues['fields'] as $fieldName => $fieldType) {
 195             if ($fieldType == 'Int') {
 196               if (is_numeric($queryText)) {
 197                 $clauses[] = "$fieldName = {$queryText}";
 198               }
 199             }
 200             else {
 201               $fullTextFields[] = $fieldName;
 202             }
 203           }
 204
 205           if (!empty($fullTextFields)) {
 206             $clauses[] = $this->matchText($tableName, $fullTextFields, $queryText);
 207           }
 208
 209           if (empty($clauses)) {
 210             continue;
 211           }
 212
 213           $whereClause = implode(' OR ', $clauses);
 214
 215           //resolve conflict between entity tables.
 216           if ($tableName == 'civicrm_note' &&
 217             $entityTable = CRM_Utils_Array::value('entity_table', $tableValues)
 218           ) {
 219             $whereClause .= " AND entity_table = '{$entityTable}'";
 220           }
 221
 222           $sql = "
 223 REPLACE  INTO {$entityIDTableName} ( entity_id )
 224 SELECT   {$tableValues['id']}
 225 FROM     $tableName
 226 WHERE    ( $whereClause )
 227 AND      {$tableValues['id']} IS NOT NULL
 228 GROUP BY {$tableValues['id']}
 229 {$this->toLimit($limit)}
 230 ";
 231           CRM_Core_DAO::executeQuery($sql);
 232         }
 233       }
 234     }
 235
 236     if (isset($tables['final'])) {
 237       foreach ($tables['final'] as $sqlStatement) {
 238         CRM_Core_DAO::executeQuery($sqlStatement);
 239       }
 240     }
 241
 242     return array(
 243       'count' => CRM_Core_DAO::singleValueQuery("SELECT count(*) FROM {$entityIDTableName}"),
 244       'files' => $files,
 245     );
 246   }
 247
 248   /**
 249    * Create a SQL expression for matching against a list of.
 250    * text columns.
 251    *
 252    * @param string $table
 253    *   Eg "civicrm_note" or "civicrm_note mynote".
 254    * @param array|string $fullTextFields list of field names
 255    * @param string $queryText
 256    * @return string
 257    *   SQL, eg "MATCH (col1) AGAINST (queryText)" or "col1 LIKE '%queryText%'"
 258    */
 259   public function matchText($table, $fullTextFields, $queryText) {
 260     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 261
 262     if (strpos($table, ' ') === FALSE) {
 263       $tableName = $tableAlias = $table;
 264     }
 265     else {
 266       list ($tableName, $tableAlias) = explode(' ', $table);
 267     }
 268     if (is_scalar($fullTextFields)) {
 269       $fullTextFields = array($fullTextFields);
 270     }
 271
 272     $clauses = array();
 273     if (CRM_Core_InnoDBIndexer::singleton()->hasDeclaredIndex($tableName, $fullTextFields)) {
 274       $formattedQuery = CRM_Utils_QueryFormatter::singleton()
 275         ->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL);
 276
 277       $prefixedFieldNames = array();
 278       foreach ($fullTextFields as $fieldName) {
 279         $prefixedFieldNames[] = "$tableAlias.$fieldName";
 280       }
 281
 282       $clauses[] = sprintf("MATCH (%s) AGAINST ('%s' IN BOOLEAN MODE)",
 283         implode(',', $prefixedFieldNames),
 284         $strtolower(CRM_Core_DAO::escapeString($formattedQuery))
 285       );
 286     }
 287     else {
 288       //CRM_Core_Session::setStatus(ts('Cannot use FTS for %1 (%2)', array(
 289       //  1 => $table,
 290       //  2 => implode(', ', $fullTextFields),
 291       //)));
 292
 293       $formattedQuery = CRM_Utils_QueryFormatter::singleton()
 294         ->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_LIKE);
 295       $escapedText = $strtolower(CRM_Core_DAO::escapeString($formattedQuery));
 296       foreach ($fullTextFields as $fieldName) {
 297         $clauses[] = "$tableAlias.$fieldName LIKE '{$escapedText}'";
 298       }
 299     }
 300     return implode(' OR ', $clauses);
 301   }
 302
 303   /**
 304    * For any records in $toTable that originated with this query,
 305    * append file information.
 306    *
 307    * @param string $toTable
 308    * @param string $parentIdColumn
 309    * @param array $files
 310    *   See return format of CRM_Core_FileSearchInterface::search.
 311    */
 312   public function moveFileIDs($toTable, $parentIdColumn, $files) {
 313     if (empty($files)) {
 314       return;
 315     }
 316
 317     $filesIndex = CRM_Utils_Array::index(array('xparent_id', 'file_id'), $files);
 318     // ex: $filesIndex[$xparent_id][$file_id] = array(...the file record...);
 319
 320     $dao = CRM_Core_DAO::executeQuery("
 321       SELECT distinct {$parentIdColumn}
 322       FROM {$toTable}
 323       WHERE table_name = %1
 324     ", array(
 325       1 => array($this->getName(), 'String'),
 326     ));
 327     while ($dao->fetch()) {
 328       if (empty($filesIndex[$dao->{$parentIdColumn}])) {
 329         continue;
 330       }
 331
 332       CRM_Core_DAO::executeQuery("UPDATE {$toTable}
 333         SET file_ids = %1
 334         WHERE table_name = %2 AND {$parentIdColumn} = %3
 335       ", array(
 336         1 => array(implode(',', array_keys($filesIndex[$dao->{$parentIdColumn}])), 'String'),
 337         2 => array($this->getName(), 'String'),
 338         3 => array($dao->{$parentIdColumn}, 'Int'),
 339       ));
 340     }
 341   }
 342
 343   /**
 344    * @param int|array $limit
 345    * @return string
 346    *   SQL
 347    * @see CRM_Contact_Form_Search_Custom_FullText::toLimit
 348    */
 349   public function toLimit($limit) {
 350     if (is_array($limit)) {
 351       list ($limit, $offset) = $limit;
 352     }
 353     if (empty($limit)) {
 354       return '';
 355     }
 356     $result = "LIMIT {$limit}";
 357     if ($offset) {
 358       $result .= " OFFSET {$offset}";
 359     }
 360     return $result;
 361   }
 362
 363   /**
 364    * @param array|int $limit
 365    * @return array
 366    *   (0 => $limit, 1 => $offset)
 367    */
 368   public function parseLimitOffset($limit) {
 369     if (is_scalar($limit)) {
 370       $intLimit = $limit;
 371     }
 372     else {
 373       list ($intLimit, $intOffset) = $limit;
 374     }
 375     if (!$intOffset) {
 376       $intOffset = 0;
 377     }
 378     return array($intLimit, $intOffset);
 379   }
 380
 381 }