CRM/Core/BAO/CustomQuery.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  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    * The cache to translate the option values into labels.
  83    *
  84    * @var array
  85    */
  86   public $_options;
  87
  88   /**
  89    * The custom fields information.
  90    *
  91    * @var array
  92    */
  93   public $_fields;
  94
  95   /**
  96    * Searching for contacts?
  97    *
  98    * @var boolean
  99    */
 100   protected $_contactSearch;
 101
 102   protected $_locationSpecificCustomFields;
 103
 104   /**
 105    * This stores custom data group types and tables that it extends.
 106    *
 107    * @var array
 108    */
 109   static $extendsMap = array(
 110     'Contact' => 'civicrm_contact',
 111     'Individual' => 'civicrm_contact',
 112     'Household' => 'civicrm_contact',
 113     'Organization' => 'civicrm_contact',
 114     'Contribution' => 'civicrm_contribution',
 115     'ContributionRecur' => 'civicrm_contribution_recur',
 116     'Membership' => 'civicrm_membership',
 117     'Participant' => 'civicrm_participant',
 118     'Group' => 'civicrm_group',
 119     'Relationship' => 'civicrm_relationship',
 120     'Event' => 'civicrm_event',
 121     'Case' => 'civicrm_case',
 122     'Activity' => 'civicrm_activity',
 123     'Pledge' => 'civicrm_pledge',
 124     'Grant' => 'civicrm_grant',
 125     'Address' => 'civicrm_address',
 126     'Campaign' => 'civicrm_campaign',
 127     'Survey' => 'civicrm_survey',
 128   );
 129
 130   /**
 131    * Class constructor.
 132    *
 133    * Takes in a set of custom field ids andsets up the data structures to
 134    * generate a query
 135    *
 136    * @param array $ids
 137    *   The set of custom field ids.
 138    *
 139    * @param bool $contactSearch
 140    * @param array $locationSpecificFields
 141    */
 142   public function __construct($ids, $contactSearch = FALSE, $locationSpecificFields = array()) {
 143     $this->_ids = &$ids;
 144     $this->_locationSpecificCustomFields = $locationSpecificFields;
 145
 146     $this->_select = array();
 147     $this->_element = array();
 148     $this->_tables = array();
 149     $this->_whereTables = array();
 150     $this->_where = array();
 151     $this->_qill = array();
 152     $this->_options = array();
 153
 154     $this->_fields = array();
 155     $this->_contactSearch = $contactSearch;
 156
 157     if (empty($this->_ids)) {
 158       return;
 159     }
 160
 161     // initialize the field array
 162     $tmpArray = array_keys($this->_ids);
 163     $idString = implode(',', $tmpArray);
 164     $query = "
 165 SELECT f.id, f.label, f.data_type,
 166        f.html_type, f.is_search_range,
 167        f.option_group_id, f.custom_group_id,
 168        f.column_name, g.table_name,
 169        f.date_format,f.time_format
 170   FROM civicrm_custom_field f,
 171        civicrm_custom_group g
 172  WHERE f.custom_group_id = g.id
 173    AND g.is_active = 1
 174    AND f.is_active = 1
 175    AND f.id IN ( $idString )";
 176
 177     $dao = CRM_Core_DAO::executeQuery($query);
 178     while ($dao->fetch()) {
 179       // get the group dao to figure which class this custom field extends
 180       $extends = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $dao->custom_group_id, 'extends');
 181       if (array_key_exists($extends, self::$extendsMap)) {
 182         $extendsTable = self::$extendsMap[$extends];
 183       }
 184       elseif (in_array($extends, CRM_Contact_BAO_ContactType::subTypes())) {
 185         // if $extends is a subtype, refer contact table
 186         $extendsTable = self::$extendsMap['Contact'];
 187       }
 188       $this->_fields[$dao->id] = array(
 189         'id' => $dao->id,
 190         'label' => $dao->label,
 191         'extends' => $extendsTable,
 192         'data_type' => $dao->data_type,
 193         'html_type' => $dao->html_type,
 194         'is_search_range' => $dao->is_search_range,
 195         'column_name' => $dao->column_name,
 196         'table_name' => $dao->table_name,
 197         'option_group_id' => $dao->option_group_id,
 198       );
 199
 200       // store it in the options cache to make things easier
 201       // during option lookup
 202       $this->_options[$dao->id] = array();
 203       $this->_options[$dao->id]['attributes'] = array(
 204         'label' => $dao->label,
 205         'data_type' => $dao->data_type,
 206         'html_type' => $dao->html_type,
 207       );
 208
 209       $optionGroupID = NULL;
 210       $htmlTypes = array('CheckBox', 'Radio', 'Select', 'Multi-Select', 'AdvMulti-Select', 'Autocomplete-Select');
 211       if (in_array($dao->html_type, $htmlTypes) && $dao->data_type != 'ContactReference') {
 212         if ($dao->option_group_id) {
 213           $optionGroupID = $dao->option_group_id;
 214         }
 215         elseif ($dao->data_type != 'Boolean') {
 216           $errorMessage = ts("The custom field %1 is corrupt. Please delete and re-build the field",
 217             array(1 => $dao->label)
 218           );
 219           CRM_Core_Error::fatal($errorMessage);
 220         }
 221       }
 222       elseif ($dao->html_type == 'Select Date') {
 223         $this->_options[$dao->id]['attributes']['date_format'] = $dao->date_format;
 224         $this->_options[$dao->id]['attributes']['time_format'] = $dao->time_format;
 225       }
 226
 227       // build the cache for custom values with options (label => value)
 228       if ($optionGroupID != NULL) {
 229         $query = "
 230 SELECT label, value
 231   FROM civicrm_option_value
 232  WHERE option_group_id = $optionGroupID
 233 ";
 234
 235         $option = CRM_Core_DAO::executeQuery($query);
 236         while ($option->fetch()) {
 237           $dataType = $this->_fields[$dao->id]['data_type'];
 238           if ($dataType == 'Int' || $dataType == 'Float') {
 239             $num = round($option->value, 2);
 240             $this->_options[$dao->id]["$num"] = $option->label;
 241           }
 242           else {
 243             $this->_options[$dao->id][$option->value] = $option->label;
 244           }
 245         }
 246         $options = $this->_options[$dao->id];
 247         //unset attributes to avoid confussion
 248         unset($options['attributes']);
 249         CRM_Utils_Hook::customFieldOptions($dao->id, $options, FALSE);
 250       }
 251     }
 252   }
 253
 254   /**
 255    * Generate the select clause and the associated tables.
 256    */
 257   public function select() {
 258     if (empty($this->_fields)) {
 259       return;
 260     }
 261
 262     foreach ($this->_fields as $id => $field) {
 263       $name = $field['table_name'];
 264       $fieldName = 'custom_' . $field['id'];
 265       $this->_select["{$name}_id"] = "{$name}.id as {$name}_id";
 266       $this->_element["{$name}_id"] = 1;
 267       $this->_select[$fieldName] = "{$field['table_name']}.{$field['column_name']} as $fieldName";
 268       $this->_element[$fieldName] = 1;
 269       $joinTable = NULL;
 270       // CRM-14265
 271       if ($field['extends'] == 'civicrm_group') {
 272         return;
 273       }
 274       elseif ($field['extends'] == 'civicrm_contact') {
 275         $joinTable = 'contact_a';
 276       }
 277       elseif ($field['extends'] == 'civicrm_contribution') {
 278         $joinTable = $field['extends'];
 279       }
 280       elseif (in_array($field['extends'], self::$extendsMap)) {
 281         $joinTable = $field['extends'];
 282       }
 283       else {
 284         return;
 285       }
 286
 287       $this->_tables[$name] = "\nLEFT JOIN $name ON $name.entity_id = $joinTable.id";
 288
 289       if ($this->_ids[$id]) {
 290         $this->_whereTables[$name] = $this->_tables[$name];
 291       }
 292
 293       if ($joinTable) {
 294         $joinClause = 1;
 295         $joinTableAlias = $joinTable;
 296         // Set location-specific query
 297         if (isset($this->_locationSpecificCustomFields[$id])) {
 298           list($locationType, $locationTypeId) = $this->_locationSpecificCustomFields[$id];
 299           $joinTableAlias = "$locationType-address";
 300           $joinClause = "\nLEFT JOIN $joinTable `$locationType-address` ON (`$locationType-address`.contact_id = contact_a.id AND `$locationType-address`.location_type_id = $locationTypeId)";
 301         }
 302         $this->_tables[$name] = "\nLEFT JOIN $name ON $name.entity_id = `$joinTableAlias`.id";
 303         if ($this->_ids[$id]) {
 304           $this->_whereTables[$name] = $this->_tables[$name];
 305         }
 306         if ($joinTable != 'contact_a') {
 307           $this->_whereTables[$joinTableAlias] = $this->_tables[$joinTableAlias] = $joinClause;
 308         }
 309         elseif ($this->_contactSearch) {
 310           CRM_Contact_BAO_Query::$_openedPanes[ts('Custom Fields')] = TRUE;
 311         }
 312       }
 313     }
 314   }
 315
 316   /**
 317    * Generate the where clause and also the english language equivalent.
 318    */
 319   public function where() {
 320     foreach ($this->_ids as $id => $values) {
 321
 322       // Fixed for Issue CRM 607
 323       if (CRM_Utils_Array::value($id, $this->_fields) === NULL ||
 324         !$values
 325       ) {
 326         continue;
 327       }
 328
 329       $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 330
 331       foreach ($values as $tuple) {
 332         list($name, $op, $value, $grouping, $wildcard) = $tuple;
 333
 334         $field = $this->_fields[$id];
 335
 336         $fieldName = "{$field['table_name']}.{$field['column_name']}";
 337
 338         $isSerialized = CRM_Core_BAO_CustomField::isSerialized($field);
 339
 340         // fix $value here to escape sql injection attacks
 341         $qillValue = NULL;
 342         if (!is_array($value)) {
 343           $value = CRM_Core_DAO::escapeString(trim($value));
 344           $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options);
 345         }
 346         elseif (count($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
 347           $op = key($value);
 348           $qillValue = strstr($op, 'NULL') ? NULL : CRM_Core_BAO_CustomField::getDisplayValue($value[$op], $id, $this->_options);
 349         }
 350         else {
 351           $op = strstr($op, 'IN') ? $op : 'IN';
 352           $qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options);
 353         }
 354
 355         $qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
 356
 357         switch ($field['data_type']) {
 358           case 'String':
 359           case 'StateProvince':
 360           case 'Country':
 361
 362             if ($field['is_search_range'] && is_array($value)) {
 363               $this->searchRange($field['id'],
 364                 $field['label'],
 365                 $field['data_type'],
 366                 $fieldName,
 367                 $value,
 368                 $grouping
 369               );
 370             }
 371             else {
 372               // fix $value here to escape sql injection attacks
 373               if (!is_array($value)) {
 374                 if ($field['data_type'] == 'String') {
 375                   $value = CRM_Utils_Type::escape($strtolower($value), 'String');
 376                 }
 377                 else {
 378                   $value = CRM_Utils_Type::escape($value, 'Integer');
 379                 }
 380               }
 381               elseif ($isSerialized) {
 382                 if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
 383                   $op = key($value);
 384                   $value = $value[$op];
 385                 }
 386                 $value = implode(',', (array) $value);
 387               }
 388
 389               // CRM-14563,CRM-16575 : Special handling of multi-select custom fields
 390               if ($isSerialized && !empty($value) && !strstr($op, 'NULL') && !strstr($op, 'LIKE')) {
 391                 $sp = CRM_Core_DAO::VALUE_SEPARATOR;
 392                 if (strstr($op, 'IN')) {
 393                   $value = str_replace(",", "$sp|$sp", $value);
 394                   $value = str_replace('(', '[[.left-parenthesis.]]', $value);
 395                   $value = str_replace(')', '[[.right-parenthesis.]]', $value);
 396                 }
 397                 $op = (strstr($op, '!') || strstr($op, 'NOT')) ? 'NOT RLIKE' : 'RLIKE';
 398                 $value = $sp . $value . $sp;
 399                 if (!$wildcard) {
 400                   foreach (explode("|", $value) as $val) {
 401                     $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $val, 'String');
 402                   }
 403                 }
 404                 else {
 405                   $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 406                 }
 407               }
 408               else {
 409                 //FIX for custom data query fired against no value(NULL/NOT NULL)
 410                 $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 411               }
 412               $this->_qill[$grouping][] = "$field[label] $qillOp $qillValue";
 413             }
 414             break;
 415
 416           case 'ContactReference':
 417             $label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
 418             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 419             $this->_qill[$grouping][] = $field['label'] . " $qillOp $label";
 420             break;
 421
 422           case 'Int':
 423             if ($field['is_search_range'] && is_array($value)) {
 424               $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
 425             }
 426             else {
 427               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
 428               $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));;
 429             }
 430             break;
 431
 432           case 'Boolean':
 433             if (!is_array($value)) {
 434               if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) {
 435                 $value = 1;
 436               }
 437               else {
 438                 $value = (int) $value;
 439               }
 440               $value = ($value == 1) ? 1 : 0;
 441               $qillValue = $value ? 'Yes' : 'No';
 442             }
 443             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
 444             $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
 445             break;
 446
 447           case 'Link':
 448           case 'Memo':
 449             $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 450             $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
 451             break;
 452
 453           case 'Money':
 454             $value = CRM_Utils_Array::value($op, (array) $value, $value);
 455             if (is_array($value)) {
 456               foreach ($value as $key => $val) {
 457                 $value[$key] = CRM_Utils_Rule::cleanMoney($value[$key]);
 458               }
 459             }
 460             else {
 461               $value = CRM_Utils_Rule::cleanMoney($value);
 462             }
 463
 464           case 'Float':
 465             if ($field['is_search_range']) {
 466               $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
 467             }
 468             else {
 469               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
 470               $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
 471             }
 472             break;
 473
 474           case 'Date':
 475             if (in_array($op, CRM_Core_DAO::acceptedSQLOperators())) {
 476               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
 477               list($qillOp, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $field['label'], $value,
 478                 $op, array(), CRM_Utils_Type::T_DATE);
 479               $this->_qill[$grouping][] = "{$field['label']} $qillOp '$qillVal'";
 480               break;
 481             }
 482
 483             $fromValue = CRM_Utils_Array::value('from', $value);
 484             $toValue = CRM_Utils_Array::value('to', $value);
 485             $value = CRM_Utils_Array::value($op, $value, $value);
 486
 487             if (!$fromValue && !$toValue) {
 488               if (!is_array($value) && !CRM_Utils_Date::processDate($value) && !in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
 489                 continue;
 490               }
 491
 492               // hack to handle yy format during search
 493               if (is_numeric($value) && strlen($value) == 4) {
 494                 $value = "01-01-{$value}";
 495               }
 496
 497               if (is_array($value)) {
 498                 $date = $qillValue = array();
 499                 foreach ($value as $key => $val) {
 500                   $date[$key] = CRM_Utils_Date::processDate($val);
 501                   $qillValue[$key] = CRM_Utils_Date::customFormat($date[$key]);
 502                 }
 503               }
 504               else {
 505                 $date = CRM_Utils_Date::processDate($value);
 506                 $qillValue = CRM_Utils_Date::customFormat($date);
 507               }
 508
 509               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String');
 510               $this->_qill[$grouping][] = $field['label'] . " {$qillOp} " . implode(', ', (array) $qillValue);
 511             }
 512             else {
 513               if (is_numeric($fromValue) && strlen($fromValue) == 4) {
 514                 $fromValue = "01-01-{$fromValue}";
 515               }
 516
 517               if (is_numeric($toValue) && strlen($toValue) == 4) {
 518                 $toValue = "01-01-{$toValue}";
 519               }
 520
 521               // TO DO: add / remove time based on date parts
 522               $fromDate = CRM_Utils_Date::processDate($fromValue);
 523               $toDate = CRM_Utils_Date::processDate($toValue);
 524               if (!$fromDate && !$toDate) {
 525                 continue;
 526               }
 527               if ($fromDate) {
 528                 $this->_where[$grouping][] = "$fieldName >= $fromDate";
 529                 $this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate);
 530               }
 531               if ($toDate) {
 532                 $this->_where[$grouping][] = "$fieldName <= $toDate";
 533                 $this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate);
 534               }
 535             }
 536             break;
 537
 538           case 'File':
 539             if ($op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY') {
 540               switch ($op) {
 541                 case 'IS EMPTY':
 542                   $op = 'IS NULL';
 543                   break;
 544
 545                 case 'IS NOT EMPTY':
 546                   $op = 'IS NOT NULL';
 547                   break;
 548               }
 549               $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op);
 550               $this->_qill[$grouping][] = $field['label'] . " {$qillOp} ";
 551             }
 552             break;
 553         }
 554       }
 555     }
 556   }
 557
 558   /**
 559    * Function that does the actual query generation.
 560    * basically ties all the above functions together
 561    *
 562    * @return array
 563    *   array of strings
 564    */
 565   public function query() {
 566     $this->select();
 567
 568     $this->where();
 569
 570     $whereStr = NULL;
 571     if (!empty($this->_where)) {
 572       $clauses = array();
 573       foreach ($this->_where as $grouping => $values) {
 574         if (!empty($values)) {
 575           $clauses[] = ' ( ' . implode(' AND ', $values) . ' ) ';
 576         }
 577       }
 578       if (!empty($clauses)) {
 579         $whereStr = ' ( ' . implode(' OR ', $clauses) . ' ) ';
 580       }
 581     }
 582
 583     return array(
 584       implode(' , ', $this->_select),
 585       implode(' ', $this->_tables),
 586       $whereStr,
 587     );
 588   }
 589
 590   /**
 591    * @param int $id
 592    * @param $label
 593    * @param $type
 594    * @param string $fieldName
 595    * @param $value
 596    * @param $grouping
 597    */
 598   public function searchRange(&$id, &$label, $type, $fieldName, &$value, &$grouping) {
 599     $qill = array();
 600
 601     if (isset($value['from'])) {
 602       $val = CRM_Utils_Type::escape($value['from'], $type);
 603
 604       if ($type == 'String') {
 605         $this->_where[$grouping][] = "$fieldName >= '$val'";
 606       }
 607       else {
 608         $this->_where[$grouping][] = "$fieldName >= $val";
 609       }
 610       $qill[] = ts('greater than or equal to \'%1\'', array(1 => $value['from']));
 611     }
 612
 613     if (isset($value['to'])) {
 614       $val = CRM_Utils_Type::escape($value['to'], $type);
 615       if ($type == 'String') {
 616         $this->_where[$grouping][] = "$fieldName <= '$val'";
 617       }
 618       else {
 619         $this->_where[$grouping][] = "$fieldName <= $val";
 620       }
 621       $qill[] = ts('less than or equal to \'%1\'', array(1 => $value['to']));
 622     }
 623
 624     if (!empty($qill)) {
 625       $this->_qill[$grouping][] = $label . ' - ' . implode(' ' . ts('and') . ' ', $qill);
 626     }
 627   }
 628
 629 }