CRM/Core/BAO/CustomQuery.php

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