CRM/Core/BAO/CustomQuery.php

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