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