Added unit test

[civicrm-core.git] / CRM / Utils / QueryFormatter.php

diff --git a/CRM/Utils/QueryFormatter.php b/CRM/Utils/QueryFormatter.php
index 63c48386badad2336bbd23e710bf78ac84f4f415..fb4cae82b8348f76ca663c8bed22b1810ca46c1f 100644 (file)
--- a/CRM/Utils/QueryFormatter.php
+++ b/CRM/Utils/QueryFormatter.php
@@ -3,7 +3,7 @@
  +--------------------------------------------------------------------+
  | CiviCRM version 4.7                                                |
  +--------------------------------------------------------------------+
- | Copyright CiviCRM LLC (c) 2004-2016                                |
+ | Copyright CiviCRM LLC (c) 2004-2017                                |
  +--------------------------------------------------------------------+
  | This file is a part of CiviCRM.                                    |
  |                                                                    |
@@ -27,7 +27,7 @@
 
  /**
   * @package CRM
-  * @copyright CiviCRM LLC (c) 2004-2016
+  * @copyright CiviCRM LLC (c) 2004-2017
   */
 
 /**
@@ -165,6 +165,67 @@ class CRM_Utils_QueryFormatter {
     return $text;
   }
 
+  /**
+   * Create a SQL WHERE expression for matching against a list of
+   * text columns.
+   *
+   * @param string $table
+   *   Eg "civicrm_note" or "civicrm_note mynote".
+   * @param array|string $columns
+   *   List of columns to search against.
+   *   Eg "first_name" or "activity_details".
+   * @param string $queryText
+   * @return string
+   *   SQL, eg "MATCH (col1) AGAINST (queryText)" or "col1 LIKE '%queryText%'"
+   */
+  public function formatSql($table, $columns, $queryText) {
+    if ($queryText === '*' || $queryText === '%' || empty($queryText)) {
+      return '(1)';
+    }
+
+    $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
+
+    if (strpos($table, ' ') === FALSE) {
+      $tableName = $tableAlias = $table;
+    }
+    else {
+      list ($tableName, $tableAlias) = explode(' ', $table);
+    }
+    if (is_scalar($columns)) {
+      $columns = array($columns);
+    }
+
+    $clauses = array();
+    if (CRM_Core_InnoDBIndexer::singleton()
+      ->hasDeclaredIndex($tableName, $columns)
+    ) {
+      $formattedQuery = $this->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL);
+
+      $prefixedFieldNames = array();
+      foreach ($columns as $fieldName) {
+        $prefixedFieldNames[] = "$tableAlias.$fieldName";
+      }
+
+      $clauses[] = sprintf("MATCH (%s) AGAINST ('%s' IN BOOLEAN MODE)",
+        implode(',', $prefixedFieldNames),
+        $strtolower(CRM_Core_DAO::escapeString($formattedQuery))
+      );
+    }
+    else {
+      //CRM_Core_Session::setStatus(ts('Cannot use FTS for %1 (%2)', array(
+      //  1 => $table,
+      //  2 => implode(', ', $fullTextFields),
+      //)));
+
+      $formattedQuery = $this->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_LIKE);
+      $escapedText = $strtolower(CRM_Core_DAO::escapeString($formattedQuery));
+      foreach ($columns as $fieldName) {
+        $clauses[] = "$tableAlias.$fieldName LIKE '{$escapedText}'";
+      }
+    }
+    return implode(' OR ', $clauses);
+  }
+
   /**
    * Format Fts.
    *
@@ -226,6 +287,20 @@ class CRM_Utils_QueryFormatter {
    */
   protected function _formatFtsBool($text, $mode) {
     $result = NULL;
+    $operators = array('+', '-', '~', '(', ')');
+    $wildCards = array('@', '%', '*');
+    $expression = preg_quote(implode('', array_merge($operators, $wildCards)), '/');
+
+    //Return if searched string ends with an unsupported operator.
+    //Or if the string contains an invalid joint occurrence of operators.
+    foreach ($operators as $val) {
+      if ($text == '@' || CRM_Utils_String::endsWith($text, $val) || preg_match("/[{$expression}]{2,}/", $text)) {
+        $csid = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionValue', 'CRM_Contact_Form_Search_Custom_FullText', 'value', 'name');
+        $url = CRM_Utils_System::url("civicrm/contact/search/custom", "csid={$csid}&reset=1");
+        $operators = implode("', '", $operators);
+        CRM_Core_Error::statusBounce("Full-Text Search does not support the use of a search with two attached operators or string ending with any of these operators ('{$operators}' or a single '@'). Please adjust your search term and try again.", $url, 'Invalid Search String');
+      }
+    }
 
     // normalize user-inputted wildcards
     $text = str_replace('%', '*', $text);
@@ -248,7 +323,7 @@ class CRM_Utils_QueryFormatter {
     else {
       switch ($mode) {
         case self::MODE_NONE:
-          $result = $this->mapWords($text, '+word');
+          $result = $this->mapWords($text, '+word', TRUE);
           break;
 
         case self::MODE_PHRASE:
@@ -319,11 +394,13 @@ class CRM_Utils_QueryFormatter {
    *   User-supplied query string.
    * @param string $template
    *   A prototypical description of each word, eg "word%" or "word*" or "*word*".
+   * @param bool $quotes
+   *   True if each searched keyword need to be surrounded with quotes.
    * @return string
    */
-  protected function mapWords($text, $template) {
+  protected function mapWords($text, $template, $quotes = FALSE) {
     $result = array();
-    foreach ($this->parseWords($text) as $word) {
+    foreach ($this->parseWords($text, $quotes) as $word) {
       $result[] = str_replace('word', $word, $template);
     }
     return implode(' ', $result);
@@ -331,9 +408,10 @@ class CRM_Utils_QueryFormatter {
 
   /**
    * @param $text
+   * @bool $quotes
    * @return array
    */
-  protected function parseWords($text) {
+  protected function parseWords($text, $quotes) {
     //NYSS 9692 special handling for emails
     if (preg_match('/^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$/', $text)) {
       $parts = explode('@', $text);
@@ -342,7 +420,19 @@ class CRM_Utils_QueryFormatter {
     }
 
     //NYSS also replace other occurrences of @
-    return explode(' ', preg_replace('/[ \r\n\t\@]+/', ' ', trim($text)));
+    $replacedText = preg_replace('/[ \r\n\t\@]+/', ' ', trim($text));
+    //filter empty values if any
+    $keywords = array_filter(explode(' ', $replacedText));
+
+    //Ensure each searched keywords are wrapped in double quotes.
+    if ($quotes) {
+      foreach ($keywords as &$val) {
+        if (!is_numeric($val)) {
+          $val = "\"{$val}\"";
+        }
+      }
+    }
+    return $keywords;
   }
 
   /**