CRM/Core/BAO/CustomQuery.php

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