CRM/Core/BAO/CustomQuery.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2020                                |
   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  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2020
  33  */
  34 class CRM_Core_BAO_CustomQuery {
  35   const PREFIX = 'custom_value_';
  36
  37   /**
  38    * The set of custom field ids.
  39    *
  40    * @var array
  41    */
  42   protected $_ids;
  43
  44   /**
  45    * The select clause.
  46    *
  47    * @var array
  48    */
  49   public $_select;
  50
  51   /**
  52    * The name of the elements that are in the select clause.
  53    * used to extract the values
  54    *
  55    * @var array
  56    */
  57   public $_element;
  58
  59   /**
  60    * The tables involved in the query.
  61    *
  62    * @var array
  63    */
  64   public $_tables;
  65   public $_whereTables;
  66
  67   /**
  68    * The where clause.
  69    *
  70    * @var array
  71    */
  72   public $_where;
  73
  74   /**
  75    * The english language version of the query.
  76    *
  77    * @var array
  78    */
  79   public $_qill;
  80
  81   /**
  82    * No longer needed due to CRM-17646 refactoring, but still used in some places
  83    *
  84    * @var array
  85    * @deprecated
  86    */
  87   public $_options;
  88
  89   /**
  90    * The custom fields information.
  91    *
  92    * @var array
  93    */
  94   public $_fields;
  95
  96   /**
  97    * @return array
  98    */
  99   public function getFields() {
 100     return $this->_fields;
 101   }
 102
 103   /**
 104    * Searching for contacts?
 105    *
 106    * @var bool
 107    */
 108   protected $_contactSearch;
 109
 110   protected $_locationSpecificCustomFields;
 111
 112   /**
 113    * This stores custom data group types and tables that it extends.
 114    *
 115    * @var array
 116    */
 117   public static $extendsMap = [
 118     'Contact' => 'civicrm_contact',
 119     'Individual' => 'civicrm_contact',
 120     'Household' => 'civicrm_contact',
 121     'Organization' => 'civicrm_contact',
 122     'Contribution' => 'civicrm_contribution',
 123     'ContributionRecur' => 'civicrm_contribution_recur',
 124     'Membership' => 'civicrm_membership',
 125     'Participant' => 'civicrm_participant',
 126     'Group' => 'civicrm_group',
 127     'Relationship' => 'civicrm_relationship',
 128     'Event' => 'civicrm_event',
 129     'Case' => 'civicrm_case',
 130     'Activity' => 'civicrm_activity',
 131     'Pledge' => 'civicrm_pledge',
 132     'Grant' => 'civicrm_grant',
 133     'Address' => 'civicrm_address',
 134     'Campaign' => 'civicrm_campaign',
 135     'Survey' => 'civicrm_survey',
 136   ];
 137
 138   /**
 139    * Class constructor.
 140    *
 141    * Takes in a set of custom field ids andsets up the data structures to
 142    * generate a query
 143    *
 144    * @param array $ids
 145    *   The set of custom field ids.
 146    *
 147    * @param bool $contactSearch
 148    * @param array $locationSpecificFields
 149    */
 150   public function __construct($ids, $contactSearch = FALSE, $locationSpecificFields = []) {
 151     $this->_ids = $ids;
 152     $this->_locationSpecificCustomFields = $locationSpecificFields;
 153
 154     $this->_select = [];
 155     $this->_element = [];
 156     $this->_tables = [];
 157     $this->_whereTables = [];
 158     $this->_where = [];
 159     $this->_qill = [];
 160     $this->_options = [];
 161
 162     $this->_contactSearch = $contactSearch;
 163     $this->_fields = CRM_Core_BAO_CustomField::getFields('ANY', FALSE, FALSE, NULL, NULL, FALSE, FALSE, FALSE);
 164
 165     if (empty($this->_ids)) {
 166       return;
 167     }
 168
 169     // initialize the field array
 170     $tmpArray = array_keys($this->_ids);
 171     $idString = implode(',', $tmpArray);
 172     $query = "
 173 SELECT f.id, f.label, f.data_type,
 174        f.html_type, f.is_search_range,
 175        f.option_group_id, f.custom_group_id,
 176        f.column_name, g.table_name,
 177        f.date_format,f.time_format
 178   FROM civicrm_custom_field f,
 179        civicrm_custom_group g
 180  WHERE f.custom_group_id = g.id
 181    AND g.is_active = 1
 182    AND f.is_active = 1
 183    AND f.id IN ( $idString )";
 184
 185     $dao = CRM_Core_DAO::executeQuery($query);
 186     while ($dao->fetch()) {
 187       // Deprecated (and poorly named) cache of field attributes
 188       $this->_options[$dao->id] = [
 189         'attributes' => [
 190           'label' => $dao->label,
 191           'data_type' => $dao->data_type,
 192           'html_type' => $dao->html_type,
 193         ],
 194       ];
 195
 196       $options = CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $dao->id, [], 'search');
 197       if ($options) {
 198         $this->_options[$dao->id] += $options;
 199       }
 200
 201       if ($dao->html_type == 'Select Date') {
 202         $this->_options[$dao->id]['attributes']['date_format'] = $dao->date_format;
 203         $this->_options[$dao->id]['attributes']['time_format'] = $dao->time_format;
 204       }
 205     }
 206   }
 207
 208   /**
 209    * Generate the select clause and the associated tables.
 210    */
 211   public function select() {
 212     if (empty($this->_fields)) {
 213       return;
 214     }
 215
 216     foreach (array_keys($this->_ids) as $id) {
 217       $field = $this->_fields[$id];
 218
 219       if ($this->_contactSearch && $field['search_table'] === 'contact_a') {
 220         CRM_Contact_BAO_Query::$_openedPanes[ts('Custom Fields')] = TRUE;
 221       }
 222
 223       $name = $field['table_name'];
 224       $fieldName = 'custom_' . $field['id'];
 225       $this->_select["{$name}_id"] = "{$name}.id as {$name}_id";
 226       $this->_element["{$name}_id"] = 1;
 227       $this->_select[$fieldName] = "{$field['table_name']}.{$field['column_name']} as $fieldName";
 228       $this->_element[$fieldName] = 1;
 229
 230       $this->joinCustomTableForField($field);
 231     }
 232   }
 233
 234   /**
 235    * Generate the where clause and also the english language equivalent.
 236    *
 237    * @throws \CRM_Core_Exception
 238    */
 239   public function where() {
 240     foreach ($this->_ids as $id => $values) {
 241
 242       // Fixed for Issue CRM 607
 243       if (CRM_Utils_Array::value($id, $this->_fields) === NULL ||
 244         !$values
 245       ) {
 246         continue;
 247       }
 248
 249       foreach ($values as $tuple) {
 250         list($name, $op, $value, $grouping, $wildcard) = $tuple;
 251
 252         $field = $this->_fields[$id];
 253
 254         $fieldName = "{$field['table_name']}.{$field['column_name']}";
 255
 256         $isSerialized = CRM_Core_BAO_CustomField::isSerialized($field);
 257
 258         // fix $value here to escape sql injection attacks
 259         $qillValue = NULL;
 260         if (!is_array($value)) {
 261           $value = CRM_Core_DAO::escapeString(trim($value));
 262           $qillValue = CRM_Core_BAO_CustomField::displayValue($value, $id);
 263         }
 264         elseif (count($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
 265           $op = key($value);
 266           $qillValue = strstr($op, 'NULL') ? NULL : CRM_Core_BAO_CustomField::displayValue($value[$op], $id);
 267         }
 268         else {
 269           $op = strstr($op, 'IN') ? $op : 'IN';
 270           $qillValue = CRM_Core_BAO_CustomField::displayValue($value, $id);
 271         }
 272
 273         $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
 274
 275         // Ensure the table is joined in (eg if in where but not select).
 276         $this->joinCustomTableForField($field);
 277         switch ($field['data_type']) {
 278           case 'String':
 279           case 'StateProvince':
 280           case 'Country':
 281
 282             if ($field['is_search_range'] && is_array($value)) {
 283               //didn't found any field under any of these three data-types as searchable by range
 284             }
 285             else {
 286               // fix $value here to escape sql injection attacks
 287               if (!is_array($value)) {
 288                 if ($field['data_type'] == 'String') {
 289                   $value = CRM_Utils_Type::escape($value, 'String');
 290                 }
 291                 elseif ($value) {
 292                   $value = CRM_Utils_Type::escape($value, 'Integer');
 293                 }
 294                 $value = str_replace(['[', ']', ','], ['\[', '\]', '[:comma:]'], $value);
 295                 $value = str_replace('|', '[:separator:]', $value);
 296               }
 297               elseif ($isSerialized) {
 298                 if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
 299                   $op = key($value);
 300                   $value = $value[$op];
 301                 }
 302                 // CRM-19006: escape characters like comma, | before building regex pattern
 303                 $value = (array) $value;
 304                 foreach ($value as $key => $val) {
 305                   $value[$key] = str_replace(['[', ']', ','], ['\[', '\]', '[:comma:]'], $val);
 306                   $value[$key] = str_replace('|', '[:separator:]', $value[$key]);
 307                   if ($field['data_type'] == 'String') {
 308                     $value[$key] = CRM_Utils_Type::escape($value[$key], 'String');
 309                   }
 310                   elseif ($value) {
 311                     $value[$key] = CRM_Utils_Type::escape($value[$key], 'Integer');
 312                   }
 313                 }
 314                 $value = implode(',', $value);
 315               }
 316
 317               // CRM-14563,CRM-16575 : Special handling of multi-select custom fields
 318               if ($isSerialized && !CRM_Utils_System::isNull($value) && !strstr($op, 'NULL') && !strstr($op, 'LIKE')) {
 319                 $sp = CRM_Core_DAO::VALUE_SEPARATOR;
 320                 $value = str_replace(",", "$sp|$sp", $value);
 321                 $value = str_replace(['[:comma:]', '(', ')'], [',', '[(]', '[)]'], $value);
 322
 323                 $op = (strstr($op, '!') || strstr($op, 'NOT')) ? 'NOT RLIKE' : 'RLIKE';
 324                 $value = $sp . $value . $sp;
 325                 if (!$wildcard) {
 326                   foreach (explode("|", $value) as $val) {
 327                     $val = str_replace('[:separator:]', '\|', $val);
 328                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $val, 'String');
 329                   }
 330                 }
 331                 else {
 332                   $value = str_replace('[:separator:]', '\|', $value);
 333                   $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 334                 }
 335               }
 336               else {
 337                 //FIX for custom data query fired against no value(NULL/NOT NULL)
 338                 $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 339               }
 340               $this->_qill[$grouping][] = $field['label'] . " $qillOp $qillValue";
 341             }
 342             break;
 343
 344           case 'ContactReference':
 345             $label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
 346             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 347             $this->_qill[$grouping][] = $field['label'] . " $qillOp $label";
 348             break;
 349
 350           case 'Int':
 351             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
 352             $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]);;
 353             break;
 354
 355           case 'Boolean':
 356             if (!is_array($value)) {
 357               if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) {
 358                 $value = 1;
 359               }
 360               else {
 361                 $value = (int) $value;
 362               }
 363               $value = ($value == 1) ? 1 : 0;
 364               $qillValue = $value ? 'Yes' : 'No';
 365             }
 366             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
 367             $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]);
 368             break;
 369
 370           case 'Link':
 371           case 'Memo':
 372             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 373             $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]);
 374             break;
 375
 376           case 'Money':
 377             $value = CRM_Utils_Array::value($op, (array) $value, $value);
 378             if (is_array($value)) {
 379               foreach ($value as $key => $val) {
 380                 // @todo - this clean money should be in the form layer - it's highly likely to be doing more harm than good here
 381                 // Note the only place I can find that this code is reached by is searching a custom money field in advanced search.
 382                 // with euro style comma separators this doesn't work - with or without this cleanMoney.
 383                 // So this should be removed but is not increasing the brokeness IMHO
 384                 $value[$op][$key] = CRM_Utils_Rule::cleanMoney($value[$key]);
 385               }
 386             }
 387             else {
 388               // @todo - this clean money should be in the form layer - it's highly likely to be doing more harm than good here
 389               // comments per above apply. cleanMoney
 390               $value = CRM_Utils_Rule::cleanMoney($value);
 391             }
 392
 393           case 'Float':
 394             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
 395             $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $field['label'], 2 => $qillOp, 3 => $qillValue]);
 396             break;
 397
 398           case 'Date':
 399             if (substr($name, -9, 9) !== '_relative'
 400               && substr($name, -4, 4) !== '_low'
 401               && substr($name, -5, 5) !== '_high') {
 402               // Relative dates are handled in the buildRelativeDateQuery function.
 403               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Date');
 404               list($qillOp, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $field['label'], $value, $op, [], CRM_Utils_Type::T_DATE);
 405               $this->_qill[$grouping][] = "{$field['label']} $qillOp '$qillVal'";
 406             }
 407             break;
 408
 409           case 'File':
 410             if ($op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY') {
 411               switch ($op) {
 412                 case 'IS EMPTY':
 413                   $op = 'IS NULL';
 414                   break;
 415
 416                 case 'IS NOT EMPTY':
 417                   $op = 'IS NOT NULL';
 418                   break;
 419               }
 420               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op);
 421               $this->_qill[$grouping][] = $field['label'] . " {$qillOp} ";
 422             }
 423             break;
 424         }
 425       }
 426     }
 427   }
 428
 429   /**
 430    * Function that does the actual query generation.
 431    * basically ties all the above functions together
 432    *
 433    * @return array
 434    *   array of strings
 435    */
 436   public function query() {
 437     $this->select();
 438
 439     $this->where();
 440
 441     $whereStr = NULL;
 442     if (!empty($this->_where)) {
 443       $clauses = [];
 444       foreach ($this->_where as $grouping => $values) {
 445         if (!empty($values)) {
 446           $clauses[] = ' ( ' . implode(' AND ', $values) . ' ) ';
 447         }
 448       }
 449       if (!empty($clauses)) {
 450         $whereStr = ' ( ' . implode(' OR ', $clauses) . ' ) ';
 451       }
 452     }
 453
 454     return [
 455       implode(' , ', $this->_select),
 456       implode(' ', $this->_tables),
 457       $whereStr,
 458     ];
 459   }
 460
 461   /**
 462    * Join the custom table for the field in (if not already in the query).
 463    *
 464    * @param array $field
 465    */
 466   protected function joinCustomTableForField($field) {
 467     $name = $field['table_name'];
 468     $join = "\nLEFT JOIN $name ON $name.entity_id = {$field['search_table']}.id";
 469     $this->_tables[$name] = $this->_tables[$name] ?? $join;
 470     $this->_whereTables[$name] = $this->_whereTables[$name] ?? $join;
 471
 472     $joinTable = $field['search_table'];
 473     if ($joinTable) {
 474       $joinClause = 1;
 475       $joinTableAlias = $joinTable;
 476       // Set location-specific query
 477       if (isset($this->_locationSpecificCustomFields[$field['id']])) {
 478         list($locationType, $locationTypeId) = $this->_locationSpecificCustomFields[$field['id']];
 479         $joinTableAlias = "$locationType-address";
 480         $joinClause = "\nLEFT JOIN $joinTable `$locationType-address` ON (`$locationType-address`.contact_id = contact_a.id AND `$locationType-address`.location_type_id = $locationTypeId)";
 481       }
 482       $this->_tables[$name] = "\nLEFT JOIN $name ON $name.entity_id = `$joinTableAlias`.id";
 483       if (!empty($this->_ids[$field['id']])) {
 484         $this->_whereTables[$name] = $this->_tables[$name];
 485       }
 486       if ($joinTable !== 'contact_a') {
 487         $this->_whereTables[$joinTableAlias] = $this->_tables[$joinTableAlias] = $joinClause;
 488       }
 489     }
 490   }
 491
 492 }