CRM/Contact/BAO/Query.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  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2019
  32  */
  33
  34 /**
  35  * This is the heart of the search query building mechanism.
  36  */
  37 class CRM_Contact_BAO_Query {
  38
  39   /**
  40    * The various search modes.
  41    *
  42    * As of February 2017, entries not present for 4, 32, 64, 1024.
  43    *
  44    * MODE_ALL seems to be out of sync with the available constants;
  45    * if this is intentionally excluding MODE_MAILING then that may
  46    * bear documenting?
  47    *
  48    * Likewise if there's reason for the missing modes (4, 32, 64 etc).
  49    *
  50    * @var int
  51    */
  52   const
  53     NO_RETURN_PROPERTIES = 'CRM_Contact_BAO_Query::NO_RETURN_PROPERTIES',
  54     MODE_CONTACTS = 1,
  55     MODE_CONTRIBUTE = 2,
  56     // There is no 4,
  57     MODE_MEMBER = 8,
  58     MODE_EVENT = 16,
  59     MODE_CONTACTSRELATED = 32,
  60     // no 64.
  61     MODE_GRANT = 128,
  62     MODE_PLEDGEBANK = 256,
  63     MODE_PLEDGE = 512,
  64     // There is no 1024,
  65     MODE_CASE = 2048,
  66     MODE_ACTIVITY = 4096,
  67     MODE_CAMPAIGN = 8192,
  68     MODE_MAILING = 16384,
  69     MODE_ALL = 17407;
  70
  71   /**
  72    * Constants for search operators
  73    */
  74   const
  75     SEARCH_OPERATOR_AND = 'AND',
  76     SEARCH_OPERATOR_OR = 'OR';
  77
  78   /**
  79    * The default set of return properties.
  80    *
  81    * @var array
  82    */
  83   public static $_defaultReturnProperties;
  84
  85   /**
  86    * The default set of hier return properties.
  87    *
  88    * @var array
  89    */
  90   public static $_defaultHierReturnProperties;
  91
  92   /**
  93    * The set of input params.
  94    *
  95    * @var array
  96    */
  97   public $_params;
  98
  99   public $_cfIDs;
 100
 101   public $_paramLookup;
 102
 103   public $_sort;
 104
 105   /**
 106    * The set of output params
 107    *
 108    * @var array
 109    */
 110   public $_returnProperties;
 111
 112   /**
 113    * The select clause
 114    *
 115    * @var array
 116    */
 117   public $_select;
 118
 119   /**
 120    * The name of the elements that are in the select clause
 121    * used to extract the values.
 122    *
 123    * @var array
 124    */
 125   public $_element;
 126
 127   /**
 128    * The tables involved in the query.
 129    *
 130    * @var array
 131    */
 132   public $_tables;
 133
 134   /**
 135    * The table involved in the where clause.
 136    *
 137    * @var array
 138    */
 139   public $_whereTables;
 140
 141   /**
 142    * Array of WHERE clause components.
 143    *
 144    * @var array
 145    */
 146   public $_where;
 147
 148   /**
 149    * The WHERE clause as a string.
 150    *
 151    * @var string
 152    */
 153   public $_whereClause;
 154
 155   /**
 156    * Additional WHERE clause for permissions.
 157    *
 158    * @var string
 159    */
 160   public $_permissionWhereClause;
 161
 162   /**
 163    * The from string
 164    *
 165    * @var string
 166    */
 167   public $_fromClause;
 168
 169   /**
 170    * Additional permission from clause
 171    *
 172    * @var string
 173    */
 174   public $_permissionFromClause;
 175
 176   /**
 177    * The from clause for the simple select and alphabetical
 178    * select
 179    *
 180    * @var string
 181    */
 182   public $_simpleFromClause;
 183
 184   /**
 185    * The having values
 186    *
 187    * @var array
 188    */
 189   public $_having;
 190
 191   /**
 192    * The english language version of the query
 193    *
 194    * @var array
 195    */
 196   public $_qill;
 197
 198   /**
 199    * All the fields that could potentially be involved in
 200    * this query
 201    *
 202    * @var array
 203    */
 204   public $_fields;
 205
 206   /**
 207    * Fields hacked for legacy reasons.
 208    *
 209    * Generally where a field has a option group defining it's options we add them to
 210    * the fields array as pseudofields - eg for gender we would add the key 'gender' to fields
 211    * using CRM_Core_DAO::appendPseudoConstantsToFields($fields);
 212    *
 213    * The rendered results would hold an id in the gender_id field and the label in the pseudo 'Gender'
 214    * field. The heading for the pseudofield would come form the the option group name & for the id field
 215    * from the xml.
 216    *
 217    * These fields are handled in a more legacy way - ie overwriting 'gender_id' with the label on output
 218    * via the convertToPseudoNames function. Ideally we would convert them but they would then need to be fixed
 219    * in some other places & there are also some issues around the name (ie. Gender currently has the label in the
 220    * schema 'Gender' so adding a second 'Gender' field to search builder & export would be confusing and the standard is
 221    * not fully agreed here.
 222    *
 223    * @var array
 224    */
 225   protected $legacyHackedFields = [
 226     'gender_id' => 'gender',
 227     'prefix_id' => 'individual_prefix',
 228     'suffix_id' => 'individual_suffix',
 229     'communication_style_id' => 'communication_style',
 230   ];
 231
 232   /**
 233    * The cache to translate the option values into labels.
 234    *
 235    * @var array
 236    */
 237   public $_options;
 238
 239   /**
 240    * Are we in search mode.
 241    *
 242    * @var bool
 243    */
 244   public $_search = TRUE;
 245
 246   /**
 247    * Should we skip permission checking.
 248    *
 249    * @var bool
 250    */
 251   public $_skipPermission = FALSE;
 252
 253   /**
 254    * Should we skip adding of delete clause.
 255    *
 256    * @var bool
 257    */
 258   public $_skipDeleteClause = FALSE;
 259
 260   /**
 261    * Are we in strict mode (use equality over LIKE)
 262    *
 263    * @var bool
 264    */
 265   public $_strict = FALSE;
 266
 267   /**
 268    * What operator to use to group the clauses.
 269    *
 270    * @var string
 271    */
 272   public $_operator = 'AND';
 273
 274   public $_mode = 1;
 275
 276   /**
 277    * Should we only search on primary location.
 278    *
 279    * @var bool
 280    */
 281   public $_primaryLocation = TRUE;
 282
 283   /**
 284    * Are contact ids part of the query.
 285    *
 286    * @var bool
 287    */
 288   public $_includeContactIds = FALSE;
 289
 290   /**
 291    * Should we use the smart group cache.
 292    *
 293    * @var bool
 294    */
 295   public $_smartGroupCache = TRUE;
 296
 297   /**
 298    * Should we display contacts with a specific relationship type.
 299    *
 300    * @var string
 301    */
 302   public $_displayRelationshipType;
 303
 304   /**
 305    * Reference to the query object for custom values.
 306    *
 307    * @var Object
 308    */
 309   public $_customQuery;
 310
 311   /**
 312    * Should we enable the distinct clause, used if we are including
 313    * more than one group
 314    *
 315    * @var bool
 316    */
 317   public $_useDistinct = FALSE;
 318
 319   /**
 320    * Should we just display one contact record
 321    * @var bool
 322    */
 323   public $_useGroupBy = FALSE;
 324
 325   /**
 326    * The relationship type direction
 327    *
 328    * @var array
 329    */
 330   public static $_relType;
 331
 332   /**
 333    * The activity role
 334    *
 335    * @var array
 336    */
 337   public static $_activityRole;
 338
 339   /**
 340    * Consider the component activity type
 341    * during activity search.
 342    *
 343    * @var array
 344    */
 345   public static $_considerCompActivities;
 346
 347   /**
 348    * Consider with contact activities only,
 349    * during activity search.
 350    *
 351    * @var array
 352    */
 353   public static $_withContactActivitiesOnly;
 354
 355   /**
 356    * Use distinct component clause for component searches
 357    *
 358    * @var string
 359    */
 360   public $_distinctComponentClause;
 361
 362   public $_rowCountClause;
 363
 364   /**
 365    * Use groupBy component clause for component searches
 366    *
 367    * @var string
 368    */
 369   public $_groupByComponentClause;
 370
 371   /**
 372    * Track open panes, useful in advance search
 373    *
 374    * @var array
 375    */
 376   public static $_openedPanes = [];
 377
 378   /**
 379    * For search builder - which custom fields are location-dependent
 380    * @var array
 381    */
 382   public $_locationSpecificCustomFields = [];
 383
 384   /**
 385    * The tables which have a dependency on location and/or address
 386    *
 387    * @var array
 388    */
 389   public static $_dependencies = [
 390     'civicrm_state_province' => 1,
 391     'civicrm_country' => 1,
 392     'civicrm_county' => 1,
 393     'civicrm_address' => 1,
 394     'civicrm_location_type' => 1,
 395   ];
 396
 397   /**
 398    * List of location specific fields.
 399    * @var array
 400    */
 401   public static $_locationSpecificFields = [
 402     'street_address',
 403     'street_number',
 404     'street_name',
 405     'street_unit',
 406     'supplemental_address_1',
 407     'supplemental_address_2',
 408     'supplemental_address_3',
 409     'city',
 410     'postal_code',
 411     'postal_code_suffix',
 412     'geo_code_1',
 413     'geo_code_2',
 414     'state_province',
 415     'country',
 416     'county',
 417     'phone',
 418     'email',
 419     'im',
 420     'address_name',
 421     'master_id',
 422   ];
 423
 424   /**
 425    * Remember if we handle either end of a number or date range
 426    * so we can skip the other
 427    * @var array
 428    */
 429   protected $_rangeCache = [];
 430   /**
 431    * Set to true when $this->relationship is run to avoid adding twice.
 432    *
 433    * @var bool
 434    */
 435   protected $_relationshipValuesAdded = FALSE;
 436
 437   /**
 438    * Set to the name of the temp table if one has been created.
 439    *
 440    * @var string
 441    */
 442   public static $_relationshipTempTable;
 443
 444   public $_pseudoConstantsSelect = [];
 445
 446   public $_groupUniqueKey;
 447   public $_groupKeys = [];
 448
 449   /**
 450    * Class constructor which also does all the work.
 451    *
 452    * @param array $params
 453    * @param array $returnProperties
 454    * @param array $fields
 455    * @param bool $includeContactIds
 456    * @param bool $strict
 457    * @param bool|int $mode - mode the search is operating on
 458    *
 459    * @param bool $skipPermission
 460    * @param bool $searchDescendentGroups
 461    * @param bool $smartGroupCache
 462    * @param null $displayRelationshipType
 463    * @param string $operator
 464    * @param string $apiEntity
 465    * @param bool|null $primaryLocationOnly
 466    *
 467    * @throws \CRM_Core_Exception
 468    */
 469   public function __construct(
 470     $params = NULL, $returnProperties = NULL, $fields = NULL,
 471     $includeContactIds = FALSE, $strict = FALSE, $mode = 1,
 472     $skipPermission = FALSE, $searchDescendentGroups = TRUE,
 473     $smartGroupCache = TRUE, $displayRelationshipType = NULL,
 474     $operator = 'AND',
 475     $apiEntity = NULL,
 476     $primaryLocationOnly = NULL
 477   ) {
 478     if ($primaryLocationOnly === NULL) {
 479       $primaryLocationOnly = Civi::settings()->get('searchPrimaryDetailsOnly');
 480     }
 481     $this->_primaryLocation = $primaryLocationOnly;
 482     $this->_params = &$params;
 483     if ($this->_params == NULL) {
 484       $this->_params = [];
 485     }
 486
 487     if ($returnProperties === self::NO_RETURN_PROPERTIES) {
 488       $this->_returnProperties = [];
 489     }
 490     elseif (empty($returnProperties)) {
 491       $this->_returnProperties = self::defaultReturnProperties($mode);
 492     }
 493     else {
 494       $this->_returnProperties = &$returnProperties;
 495     }
 496
 497     $this->_includeContactIds = $includeContactIds;
 498     $this->_strict = $strict;
 499     $this->_mode = $mode;
 500     $this->_skipPermission = $skipPermission;
 501     $this->_smartGroupCache = $smartGroupCache;
 502     $this->_displayRelationshipType = $displayRelationshipType;
 503     $this->setOperator($operator);
 504
 505     if ($fields) {
 506       $this->_fields = &$fields;
 507       $this->_search = FALSE;
 508       $this->_skipPermission = TRUE;
 509     }
 510     else {
 511       $this->_fields = CRM_Contact_BAO_Contact::exportableFields('All', FALSE, TRUE, TRUE, FALSE, !$skipPermission);
 512       //  The legacy hacked fields will output as a string rather than their underlying type.
 513       foreach (array_keys($this->legacyHackedFields) as $fieldName) {
 514         $this->_fields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
 515       }
 516       $relationMetadata = CRM_Contact_BAO_Relationship::fields();
 517       $relationFields = array_intersect_key($relationMetadata, array_fill_keys(['relationship_start_date', 'relationship_end_date'], 1));
 518       // No good option other than hard-coding metadata for this 'special' field in.
 519       $relationFields['relation_active_period_date'] = [
 520         'name' => 'relation_active_period_date',
 521         'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
 522         'title' => ts('Active Period'),
 523         'table_name' => 'civicrm_relationship',
 524         'where' => 'civicrm_relationship.start_date',
 525         'where_end' => 'civicrm_relationship.end_date',
 526         'html' => ['type' => 'SelectDate', 'formatType' => 'activityDateTime'],
 527       ];
 528       $this->_fields = array_merge($relationFields, $this->_fields);
 529
 530       $fields = CRM_Core_Component::getQueryFields(!$this->_skipPermission);
 531       unset($fields['note']);
 532       $this->_fields = array_merge($this->_fields, $fields);
 533
 534       // add activity fields
 535       $fields = CRM_Activity_BAO_Activity::exportableFields();
 536       $this->_fields = array_merge($this->_fields, $fields);
 537
 538       // add any fields provided by hook implementers
 539       $extFields = CRM_Contact_BAO_Query_Hook::singleton()->getFields();
 540       $this->_fields = array_merge($this->_fields, $extFields);
 541     }
 542
 543     // basically do all the work once, and then reuse it
 544     $this->initialize($apiEntity);
 545   }
 546
 547   /**
 548    * Function which actually does all the work for the constructor.
 549    *
 550    * @param string $apiEntity
 551    *   The api entity being called.
 552    *   This sort-of duplicates $mode in a confusing way. Probably not by design.
 553    *
 554    * @throws \CRM_Core_Exception
 555    */
 556   public function initialize($apiEntity = NULL) {
 557     $this->_select = [];
 558     $this->_element = [];
 559     $this->_tables = [];
 560     $this->_whereTables = [];
 561     $this->_where = [];
 562     $this->_qill = [];
 563     $this->_options = [];
 564     $this->_cfIDs = [];
 565     $this->_paramLookup = [];
 566     $this->_having = [];
 567
 568     $this->_customQuery = NULL;
 569
 570     // reset cached static variables - CRM-5803
 571     self::$_activityRole = NULL;
 572     self::$_considerCompActivities = NULL;
 573     self::$_withContactActivitiesOnly = NULL;
 574
 575     $this->_select['contact_id'] = 'contact_a.id as contact_id';
 576     $this->_element['contact_id'] = 1;
 577     $this->_tables['civicrm_contact'] = 1;
 578
 579     if (!empty($this->_params)) {
 580       $this->buildParamsLookup();
 581     }
 582
 583     $this->_whereTables = $this->_tables;
 584
 585     $this->selectClause($apiEntity);
 586     if (!empty($this->_cfIDs)) {
 587       // @todo This function is the select function but instead of running 'select' it
 588       // is running the whole query.
 589       $this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE, $this->_locationSpecificCustomFields);
 590       $this->_customQuery->query();
 591       $this->_select = array_merge($this->_select, $this->_customQuery->_select);
 592       $this->_element = array_merge($this->_element, $this->_customQuery->_element);
 593       $this->_tables = array_merge($this->_tables, $this->_customQuery->_tables);
 594       $this->_options = $this->_customQuery->_options;
 595     }
 596     $isForcePrimaryOnly = !empty($apiEntity);
 597     $this->_whereClause = $this->whereClause($isForcePrimaryOnly);
 598     if (array_key_exists('civicrm_contribution', $this->_whereTables)) {
 599       $component = 'contribution';
 600     }
 601     if (array_key_exists('civicrm_membership', $this->_whereTables)) {
 602       $component = 'membership';
 603     }
 604     if (isset($component) && !$this->_skipPermission) {
 605       // Unit test coverage in api_v3_FinancialTypeACLTest::testGetACLContribution.
 606       CRM_Financial_BAO_FinancialType::buildPermissionedClause($this->_whereClause, $component);
 607     }
 608
 609     $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode, $apiEntity);
 610     $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
 611
 612     $this->openedSearchPanes(TRUE);
 613   }
 614
 615   /**
 616    * Function for same purpose as convertFormValues.
 617    *
 618    * Like convert form values this function exists to pre-Process parameters from the form.
 619    *
 620    * It is unclear why they are different functions & likely relates to advances search
 621    * versus search builder.
 622    *
 623    * The direction we are going is having the form convert values to a standardised format &
 624    * moving away from weird & wonderful where clause switches.
 625    *
 626    * Fix and handle contact deletion nicely.
 627    *
 628    * this code is primarily for search builder use case where different clauses can specify if they want deleted.
 629    *
 630    * CRM-11971
 631    */
 632   public function buildParamsLookup() {
 633     $trashParamExists = FALSE;
 634     $paramByGroup = [];
 635     foreach ($this->_params as $k => $param) {
 636       if (!empty($param[0]) && $param[0] == 'contact_is_deleted') {
 637         $trashParamExists = TRUE;
 638       }
 639       if (!empty($param[3])) {
 640         $paramByGroup[$param[3]][$k] = $param;
 641       }
 642     }
 643
 644     if ($trashParamExists) {
 645       $this->_skipDeleteClause = TRUE;
 646
 647       //cycle through group sets and explicitly add trash param if not set
 648       foreach ($paramByGroup as $setID => $set) {
 649         if (
 650           !in_array(['contact_is_deleted', '=', '1', $setID, '0'], $this->_params) &&
 651           !in_array(['contact_is_deleted', '=', '0', $setID, '0'], $this->_params)
 652         ) {
 653           $this->_params[] = [
 654             'contact_is_deleted',
 655             '=',
 656             '0',
 657             $setID,
 658             '0',
 659           ];
 660         }
 661       }
 662     }
 663
 664     foreach ($this->_params as $value) {
 665       if (empty($value[0])) {
 666         continue;
 667       }
 668       $cfID = CRM_Core_BAO_CustomField::getKeyID(str_replace(['_relative', '_low', '_high', '_to', '_high'], '', $value[0]));
 669       if ($cfID) {
 670         if (!array_key_exists($cfID, $this->_cfIDs)) {
 671           $this->_cfIDs[$cfID] = [];
 672         }
 673         // Set wildcard value based on "and/or" selection
 674         foreach ($this->_params as $key => $param) {
 675           if ($param[0] == $value[0] . '_operator') {
 676             $value[4] = $param[2] == 'or';
 677             break;
 678           }
 679         }
 680         $this->_cfIDs[$cfID][] = $value;
 681       }
 682
 683       if (!array_key_exists($value[0], $this->_paramLookup)) {
 684         $this->_paramLookup[$value[0]] = [];
 685       }
 686       if ($value[0] !== 'group') {
 687         // Just trying to unravel how group interacts here! This whole function is weird.
 688         $this->_paramLookup[$value[0]][] = $value;
 689       }
 690     }
 691   }
 692
 693   /**
 694    * Some composite fields do not appear in the fields array hack to make them part of the query.
 695    *
 696    * @param $apiEntity
 697    *   The api entity being called.
 698    *   This sort-of duplicates $mode in a confusing way. Probably not by design.
 699    */
 700   public function addSpecialFields($apiEntity) {
 701     static $special = ['contact_type', 'contact_sub_type', 'sort_name', 'display_name'];
 702     // if get called via Contact.get API having address_id as return parameter
 703     if ($apiEntity == 'Contact') {
 704       $special[] = 'address_id';
 705     }
 706     foreach ($special as $name) {
 707       if (!empty($this->_returnProperties[$name])) {
 708         if ($name == 'address_id') {
 709           $this->_tables['civicrm_address'] = 1;
 710           $this->_select['address_id'] = 'civicrm_address.id as address_id';
 711           $this->_element['address_id'] = 1;
 712         }
 713         else {
 714           $this->_select[$name] = "contact_a.{$name} as $name";
 715           $this->_element[$name] = 1;
 716         }
 717       }
 718     }
 719   }
 720
 721   /**
 722    * Given a list of conditions in params and a list of desired
 723    * return Properties generate the required select and from
 724    * clauses. Note that since the where clause introduces new
 725    * tables, the initial attempt also retrieves all variables used
 726    * in the params list
 727    *
 728    * @param string $apiEntity
 729    *   The api entity being called.
 730    *   This sort-of duplicates $mode in a confusing way. Probably not by design.
 731    */
 732   public function selectClause($apiEntity = NULL) {
 733
 734     // @todo Tidy up this. This arises because 1) we are ignoring the $mode & adding a new
 735     // param ($apiEntity) instead - presumably an oversight & 2 because
 736     // contact is not implemented as a component.
 737     $this->addSpecialFields($apiEntity);
 738
 739     foreach ($this->_fields as $name => $field) {
 740       // skip component fields
 741       // there are done by the alter query below
 742       // and need not be done on every field
 743       // @todo remove these & handle using metadata - only obscure fields
 744       // that are hack-added should need to be excluded from the main loop.
 745       if (
 746         (substr($name, 0, 12) === 'participant_') ||
 747         (substr($name, 0, 7) === 'pledge_') ||
 748         (substr($name, 0, 5) === 'case_')
 749       ) {
 750         continue;
 751       }
 752
 753       // redirect to activity select clause
 754       if (
 755         (substr($name, 0, 9) === 'activity_') ||
 756         ($name === 'parent_id')
 757       ) {
 758         CRM_Activity_BAO_Query::select($this);
 759       }
 760
 761       // if this is a hierarchical name, we ignore it
 762       $names = explode('-', $name);
 763       if (count($names) > 1 && isset($names[1]) && is_numeric($names[1])) {
 764         continue;
 765       }
 766
 767       // make an exception for special cases, to add the field in select clause
 768       $makeException = FALSE;
 769
 770       //special handling for groups/tags
 771       if (in_array($name, ['groups', 'tags', 'notes'])
 772         && isset($this->_returnProperties[substr($name, 0, -1)])
 773       ) {
 774         // @todo instead of setting make exception to get us into
 775         // an if clause that has handling for these fields buried with in it
 776         // move the handling to here.
 777         $makeException = TRUE;
 778       }
 779
 780       // since note has 3 different options we need special handling
 781       // note / note_subject / note_body
 782       if ($name === 'notes') {
 783         foreach (['note', 'note_subject', 'note_body'] as $noteField) {
 784           if (isset($this->_returnProperties[$noteField])) {
 785             $makeException = TRUE;
 786             break;
 787           }
 788         }
 789       }
 790
 791       $cfID = CRM_Core_BAO_CustomField::getKeyID($name);
 792       if (
 793         !empty($this->_paramLookup[$name])
 794         || !empty($this->_returnProperties[$name])
 795         || $this->pseudoConstantNameIsInReturnProperties($field, $name)
 796         || $makeException
 797       ) {
 798         if ($cfID) {
 799           // add to cfIDs array if not present
 800           if (!array_key_exists($cfID, $this->_cfIDs)) {
 801             $this->_cfIDs[$cfID] = [];
 802           }
 803         }
 804         elseif (isset($field['where'])) {
 805           list($tableName, $fieldName) = explode('.', $field['where'], 2);
 806           if (isset($tableName)) {
 807             if (CRM_Utils_Array::value($tableName, self::$_dependencies)) {
 808               $this->_tables['civicrm_address'] = 1;
 809               $this->_select['address_id'] = 'civicrm_address.id as address_id';
 810               $this->_element['address_id'] = 1;
 811             }
 812
 813             if ($tableName === 'im_provider' || $tableName === 'email_greeting' ||
 814               $tableName === 'postal_greeting' || $tableName === 'addressee'
 815             ) {
 816               if ($tableName === 'im_provider') {
 817                 CRM_Core_OptionValue::select($this);
 818               }
 819
 820               if (in_array($tableName,
 821                 ['email_greeting', 'postal_greeting', 'addressee'])) {
 822                 $this->_element["{$name}_id"] = 1;
 823                 $this->_select["{$name}_id"] = "contact_a.{$name}_id as {$name}_id";
 824                 $this->_pseudoConstantsSelect[$name] = ['pseudoField' => $tableName, 'idCol' => "{$name}_id"];
 825                 $this->_pseudoConstantsSelect[$name]['select'] = "{$name}.{$fieldName} as $name";
 826                 $this->_pseudoConstantsSelect[$name]['element'] = $name;
 827
 828                 if ($tableName === 'email_greeting') {
 829                   // @todo bad join.
 830                   $this->_pseudoConstantsSelect[$name]['join']
 831                     = " LEFT JOIN civicrm_option_group option_group_email_greeting ON (option_group_email_greeting.name = 'email_greeting')";
 832                   $this->_pseudoConstantsSelect[$name]['join'] .=
 833                     " LEFT JOIN civicrm_option_value email_greeting ON (contact_a.email_greeting_id = email_greeting.value AND option_group_email_greeting.id = email_greeting.option_group_id ) ";
 834                 }
 835                 elseif ($tableName === 'postal_greeting') {
 836                   // @todo bad join.
 837                   $this->_pseudoConstantsSelect[$name]['join']
 838                     = " LEFT JOIN civicrm_option_group option_group_postal_greeting ON (option_group_postal_greeting.name = 'postal_greeting')";
 839                   $this->_pseudoConstantsSelect[$name]['join'] .=
 840                     " LEFT JOIN civicrm_option_value postal_greeting ON (contact_a.postal_greeting_id = postal_greeting.value AND option_group_postal_greeting.id = postal_greeting.option_group_id ) ";
 841                 }
 842                 elseif ($tableName == 'addressee') {
 843                   // @todo bad join.
 844                   $this->_pseudoConstantsSelect[$name]['join']
 845                     = " LEFT JOIN civicrm_option_group option_group_addressee ON (option_group_addressee.name = 'addressee')";
 846                   $this->_pseudoConstantsSelect[$name]['join'] .=
 847                     " LEFT JOIN civicrm_option_value addressee ON (contact_a.addressee_id = addressee.value AND option_group_addressee.id = addressee.option_group_id ) ";
 848                 }
 849                 $this->_pseudoConstantsSelect[$name]['table'] = $tableName;
 850
 851                 //get display
 852                 $greetField = "{$name}_display";
 853                 $this->_select[$greetField] = "contact_a.{$greetField} as {$greetField}";
 854                 $this->_element[$greetField] = 1;
 855                 //get custom
 856                 $greetField = "{$name}_custom";
 857                 $this->_select[$greetField] = "contact_a.{$greetField} as {$greetField}";
 858                 $this->_element[$greetField] = 1;
 859               }
 860             }
 861             else {
 862               if (!in_array($tableName, ['civicrm_state_province', 'civicrm_country', 'civicrm_county'])) {
 863                 $this->_tables[$tableName] = 1;
 864               }
 865
 866               // also get the id of the tableName
 867               $tName = substr($tableName, 8);
 868               if (in_array($tName, ['country', 'state_province', 'county'])) {
 869                 if ($tName == 'state_province') {
 870                   $this->_pseudoConstantsSelect['state_province_name'] = [
 871                     'pseudoField' => "{$tName}",
 872                     'idCol' => "{$tName}_id",
 873                     'bao' => 'CRM_Core_BAO_Address',
 874                     'table' => "civicrm_{$tName}",
 875                     'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ",
 876                   ];
 877
 878                   $this->_pseudoConstantsSelect[$tName] = [
 879                     'pseudoField' => 'state_province_abbreviation',
 880                     'idCol' => "{$tName}_id",
 881                     'table' => "civicrm_{$tName}",
 882                     'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ",
 883                   ];
 884                 }
 885                 else {
 886                   $this->_pseudoConstantsSelect[$name] = [
 887                     'pseudoField' => "{$tName}_id",
 888                     'idCol' => "{$tName}_id",
 889                     'bao' => 'CRM_Core_BAO_Address',
 890                     'table' => "civicrm_{$tName}",
 891                     'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ",
 892                   ];
 893                 }
 894
 895                 $this->_select["{$tName}_id"] = "civicrm_address.{$tName}_id as {$tName}_id";
 896                 $this->_element["{$tName}_id"] = 1;
 897               }
 898               elseif ($tName != 'contact') {
 899                 $this->_select["{$tName}_id"] = "{$tableName}.id as {$tName}_id";
 900                 $this->_element["{$tName}_id"] = 1;
 901               }
 902
 903               //special case for phone
 904               if ($name == 'phone') {
 905                 $this->_select['phone_type_id'] = "civicrm_phone.phone_type_id as phone_type_id";
 906                 $this->_element['phone_type_id'] = 1;
 907               }
 908
 909               // if IM then select provider_id also
 910               // to get "IM Service Provider" in a file to be exported, CRM-3140
 911               if ($name == 'im') {
 912                 $this->_select['provider_id'] = "civicrm_im.provider_id as provider_id";
 913                 $this->_element['provider_id'] = 1;
 914               }
 915
 916               if ($tName == 'contact' && $fieldName == 'organization_name') {
 917                 // special case, when current employer is set for Individual contact
 918                 $this->_select[$name] = "IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name";
 919               }
 920               elseif ($tName == 'contact' && $fieldName === 'id') {
 921                 // Handled elsewhere, explicitly ignore. Possibly for all tables...
 922               }
 923               elseif (in_array($tName, ['country', 'county'])) {
 924                 $this->_pseudoConstantsSelect[$name]['select'] = "{$field['where']} as `$name`";
 925                 $this->_pseudoConstantsSelect[$name]['element'] = $name;
 926               }
 927               elseif ($tName == 'state_province') {
 928                 $this->_pseudoConstantsSelect[$tName]['select'] = "{$field['where']} as `$name`";
 929                 $this->_pseudoConstantsSelect[$tName]['element'] = $name;
 930               }
 931               elseif (strpos($name, 'contribution_soft_credit') !== FALSE) {
 932                 if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled($this->_params)) {
 933                   $this->_select[$name] = "{$field['where']} as `$name`";
 934                 }
 935               }
 936               elseif ($this->pseudoConstantNameIsInReturnProperties($field, $name)) {
 937                 $this->addPseudoconstantFieldToSelect($name);
 938               }
 939               else {
 940                 $this->_select[$name] = str_replace('civicrm_contact.', 'contact_a.', "{$field['where']} as `$name`");
 941               }
 942               if (!in_array($tName, ['state_province', 'country', 'county'])) {
 943                 $this->_element[$name] = 1;
 944               }
 945             }
 946           }
 947         }
 948         elseif ($name === 'tags') {
 949           //@todo move this handling outside the big IF & ditch $makeException
 950           $this->_useGroupBy = TRUE;
 951           $this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_tag.name)) as tags";
 952           $this->_element[$name] = 1;
 953           $this->_tables['civicrm_tag'] = 1;
 954           $this->_tables['civicrm_entity_tag'] = 1;
 955         }
 956         elseif ($name === 'groups') {
 957           //@todo move this handling outside the big IF & ditch $makeException
 958           $this->_useGroupBy = TRUE;
 959           // Duplicates will be created here but better to sort them out in php land.
 960           $this->_select[$name] = "
 961             CONCAT_WS(',',
 962             GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
 963             GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
 964           )
 965           as groups";
 966           $this->_element[$name] = 1;
 967           $this->_tables['civicrm_group_contact'] = 1;
 968           $this->_tables['civicrm_group_contact_cache'] = 1;
 969           $this->_pseudoConstantsSelect["{$name}"] = [
 970             'pseudoField' => "groups",
 971             'idCol' => "groups",
 972           ];
 973         }
 974         elseif ($name === 'notes') {
 975           //@todo move this handling outside the big IF & ditch $makeException
 976           // if note field is subject then return subject else body of the note
 977           $noteColumn = 'note';
 978           if (isset($noteField) && $noteField == 'note_subject') {
 979             $noteColumn = 'subject';
 980           }
 981
 982           $this->_useGroupBy = TRUE;
 983           $this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_note.$noteColumn)) as notes";
 984           $this->_element[$name] = 1;
 985           $this->_tables['civicrm_note'] = 1;
 986         }
 987         elseif ($name === 'current_employer') {
 988           $this->_select[$name] = "IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer";
 989           $this->_element[$name] = 1;
 990         }
 991       }
 992
 993       if ($cfID && !empty($field['is_search_range'])) {
 994         // this is a custom field with range search enabled, so we better check for two/from values
 995         if (!empty($this->_paramLookup[$name . '_from'])) {
 996           if (!array_key_exists($cfID, $this->_cfIDs)) {
 997             $this->_cfIDs[$cfID] = [];
 998           }
 999           foreach ($this->_paramLookup[$name . '_from'] as $pID => $p) {
1000             // search in the cdID array for the same grouping
1001             $fnd = FALSE;
1002             foreach ($this->_cfIDs[$cfID] as $cID => $c) {
1003               if ($c[3] == $p[3]) {
1004                 $this->_cfIDs[$cfID][$cID][2]['from'] = $p[2];
1005                 $fnd = TRUE;
1006               }
1007             }
1008             if (!$fnd) {
1009               $p[2] = ['from' => $p[2]];
1010               $this->_cfIDs[$cfID][] = $p;
1011             }
1012           }
1013         }
1014         if (!empty($this->_paramLookup[$name . '_to'])) {
1015           if (!array_key_exists($cfID, $this->_cfIDs)) {
1016             $this->_cfIDs[$cfID] = [];
1017           }
1018           foreach ($this->_paramLookup[$name . '_to'] as $pID => $p) {
1019             // search in the cdID array for the same grouping
1020             $fnd = FALSE;
1021             foreach ($this->_cfIDs[$cfID] as $cID => $c) {
1022               if ($c[4] == $p[4]) {
1023                 $this->_cfIDs[$cfID][$cID][2]['to'] = $p[2];
1024                 $fnd = TRUE;
1025               }
1026             }
1027             if (!$fnd) {
1028               $p[2] = ['to' => $p[2]];
1029               $this->_cfIDs[$cfID][] = $p;
1030             }
1031           }
1032         }
1033       }
1034     }
1035
1036     // add location as hierarchical elements
1037     $this->addHierarchicalElements();
1038
1039     // add multiple field like website
1040     $this->addMultipleElements();
1041
1042     //fix for CRM-951
1043     CRM_Core_Component::alterQuery($this, 'select');
1044
1045     CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'select');
1046   }
1047
1048   /**
1049    * If the return Properties are set in a hierarchy, traverse the hierarchy to get the return values.
1050    */
1051   public function addHierarchicalElements() {
1052     if (empty($this->_returnProperties['location'])) {
1053       return;
1054     }
1055     if (!is_array($this->_returnProperties['location'])) {
1056       return;
1057     }
1058
1059     $locationTypes = CRM_Core_DAO_Address::buildOptions('location_type_id', 'validate');
1060     $processed = [];
1061     $index = 0;
1062
1063     $addressCustomFields = CRM_Core_BAO_CustomField::getFieldsForImport('Address');
1064     $addressCustomFieldIds = [];
1065
1066     foreach ($this->_returnProperties['location'] as $name => $elements) {
1067       $lCond = self::getPrimaryCondition($name);
1068       $locationTypeId = is_numeric($name) ? NULL : array_search($name, $locationTypes);
1069
1070       if (!$lCond) {
1071         if ($locationTypeId === FALSE) {
1072           continue;
1073         }
1074         $lCond = "location_type_id = $locationTypeId";
1075         $this->_useDistinct = TRUE;
1076
1077         //commented for CRM-3256
1078         $this->_useGroupBy = TRUE;
1079       }
1080
1081       $name = str_replace(' ', '_', $name);
1082       $tName = "$name-location_type";
1083       $ltName = "`$name-location_type`";
1084       $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
1085       $this->_select["{$tName}"] = "`$tName`.name as `{$tName}`";
1086       $this->_element["{$tName}_id"] = 1;
1087       $this->_element["{$tName}"] = 1;
1088
1089       $locationTypeName = $tName;
1090       $locationTypeJoin = [];
1091
1092       $addWhereCount = 0;
1093       foreach ($elements as $elementFullName => $dontCare) {
1094         $index++;
1095         $elementName = $elementCmpName = $elementFullName;
1096
1097         if (substr($elementCmpName, 0, 5) == 'phone') {
1098           $elementCmpName = 'phone';
1099         }
1100
1101         if (in_array($elementCmpName, array_keys($addressCustomFields))) {
1102           if ($cfID = CRM_Core_BAO_CustomField::getKeyID($elementCmpName)) {
1103             $addressCustomFieldIds[$cfID][$name] = 1;
1104           }
1105         }
1106         // add address table - doesn't matter if we do it mutliple times - it's the same data
1107         // @todo ditch the double processing of addressJoin
1108         if ((in_array($elementCmpName, self::$_locationSpecificFields) || !empty($addressCustomFieldIds))
1109           && !in_array($elementCmpName, ['email', 'phone', 'im', 'openid'])
1110         ) {
1111           list($aName, $addressJoin) = $this->addAddressTable($name, $lCond);
1112           $locationTypeJoin[$tName] = " ( $aName.location_type_id = $ltName.id ) ";
1113           $processed[$aName] = 1;
1114         }
1115
1116         $cond = $elementType = '';
1117         if (strpos($elementName, '-') !== FALSE) {
1118           // this is either phone, email or IM
1119           list($elementName, $elementType) = explode('-', $elementName);
1120
1121           if (($elementName != 'phone') && ($elementName != 'im')) {
1122             $cond = self::getPrimaryCondition($elementType);
1123           }
1124           // CRM-13011 : If location type is primary, do not restrict search to the phone
1125           // type id - we want the primary phone, regardless of what type it is.
1126           // Otherwise, restrict to the specified phone type for the given field.
1127           if ((!$cond) && ($elementName == 'phone')) {
1128             $cond = "phone_type_id = '$elementType'";
1129           }
1130           elseif ((!$cond) && ($elementName == 'im')) {
1131             // IM service provider id, CRM-3140
1132             $cond = "provider_id = '$elementType'";
1133           }
1134           $elementType = '-' . $elementType;
1135         }
1136
1137         $field = CRM_Utils_Array::value($elementName, $this->_fields);
1138
1139         // hack for profile, add location id
1140         if (!$field) {
1141           if ($elementType &&
1142             // fix for CRM-882( to handle phone types )
1143             !is_numeric($elementType)
1144           ) {
1145             if (is_numeric($name)) {
1146               $field = CRM_Utils_Array::value($elementName . "-Primary$elementType", $this->_fields);
1147             }
1148             else {
1149               $field = CRM_Utils_Array::value($elementName . "-$locationTypeId$elementType", $this->_fields);
1150             }
1151           }
1152           elseif (is_numeric($name)) {
1153             //this for phone type to work
1154             if (in_array($elementName, ['phone', 'phone_ext'])) {
1155               $field = CRM_Utils_Array::value($elementName . "-Primary" . $elementType, $this->_fields);
1156             }
1157             else {
1158               $field = CRM_Utils_Array::value($elementName . "-Primary", $this->_fields);
1159             }
1160           }
1161           else {
1162             //this is for phone type to work for profile edit
1163             if (in_array($elementName, ['phone', 'phone_ext'])) {
1164               $field = CRM_Utils_Array::value($elementName . "-$locationTypeId$elementType", $this->_fields);
1165             }
1166             else {
1167               $field = CRM_Utils_Array::value($elementName . "-$locationTypeId", $this->_fields);
1168             }
1169           }
1170         }
1171
1172         // Check if there is a value, if so also add to where Clause
1173         $addWhere = FALSE;
1174         if ($this->_params) {
1175           $nm = $elementName;
1176           if (isset($locationTypeId)) {
1177             $nm .= "-$locationTypeId";
1178           }
1179           if (!is_numeric($elementType)) {
1180             $nm .= "$elementType";
1181           }
1182
1183           foreach ($this->_params as $id => $values) {
1184             if ((is_array($values) && $values[0] == $nm) ||
1185               (in_array($elementName, ['phone', 'im'])
1186                 && (strpos($values[0], $nm) !== FALSE)
1187               )
1188             ) {
1189               $addWhere = TRUE;
1190               $addWhereCount++;
1191               break;
1192             }
1193           }
1194         }
1195
1196         if ($field && isset($field['where'])) {
1197           list($tableName, $fieldName) = explode('.', $field['where'], 2);
1198           $pf = substr($tableName, 8);
1199           $tName = $name . '-' . $pf . $elementType;
1200           if (isset($tableName)) {
1201             if ($tableName == 'civicrm_state_province' || $tableName == 'civicrm_country' || $tableName == 'civicrm_county') {
1202               $this->_select["{$tName}_id"] = "{$aName}.{$pf}_id as `{$tName}_id`";
1203             }
1204             else {
1205               $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
1206             }
1207
1208             $this->_element["{$tName}_id"] = 1;
1209             if (substr($tName, -15) == '-state_province') {
1210               // FIXME: hack to fix CRM-1900
1211               $a = Civi::settings()->get('address_format');
1212
1213               if (substr_count($a, 'state_province_name') > 0) {
1214                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"] = [
1215                   'pseudoField' => "{$pf}_id",
1216                   'idCol' => "{$tName}_id",
1217                   'bao' => 'CRM_Core_BAO_Address',
1218                 ];
1219                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['select'] = "`$tName`.name as `{$name}-{$elementFullName}`";
1220               }
1221               else {
1222                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"] = [
1223                   'pseudoField' => 'state_province_abbreviation',
1224                   'idCol' => "{$tName}_id",
1225                 ];
1226                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['select'] = "`$tName`.abbreviation as `{$name}-{$elementFullName}`";
1227               }
1228             }
1229             else {
1230               if (substr($elementFullName, 0, 2) == 'im') {
1231                 $provider = "{$name}-{$elementFullName}-provider_id";
1232                 $this->_select[$provider] = "`$tName`.provider_id as `{$name}-{$elementFullName}-provider_id`";
1233                 $this->_element[$provider] = 1;
1234               }
1235               if ($pf == 'country' || $pf == 'county') {
1236                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"] = [
1237                   'pseudoField' => "{$pf}_id",
1238                   'idCol' => "{$tName}_id",
1239                   'bao' => 'CRM_Core_BAO_Address',
1240                 ];
1241                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['select'] = "`$tName`.$fieldName as `{$name}-{$elementFullName}`";
1242               }
1243               else {
1244                 $this->_select["{$name}-{$elementFullName}"] = "`$tName`.$fieldName as `{$name}-{$elementFullName}`";
1245               }
1246             }
1247
1248             if (in_array($pf, ['state_province', 'country', 'county'])) {
1249               $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['element'] = "{$name}-{$elementFullName}";
1250             }
1251             else {
1252               $this->_element["{$name}-{$elementFullName}"] = 1;
1253             }
1254
1255             if (empty($processed["`$tName`"])) {
1256               $processed["`$tName`"] = 1;
1257               $newName = $tableName . '_' . $index;
1258               switch ($tableName) {
1259                 case 'civicrm_phone':
1260                 case 'civicrm_email':
1261                 case 'civicrm_im':
1262                 case 'civicrm_openid':
1263
1264                   $this->_tables[$tName] = "\nLEFT JOIN $tableName `$tName` ON contact_a.id = `$tName`.contact_id";
1265                   if ($tableName != 'civicrm_phone') {
1266                     $this->_tables[$tName] .= " AND `$tName`.$lCond";
1267                   }
1268                   elseif (is_numeric($name)) {
1269                     $this->_select[$tName] = "IF (`$tName`.is_primary = $name, `$tName`.phone, NULL) as `$tName`";
1270                   }
1271
1272                   // this special case to add phone type
1273                   if ($cond) {
1274                     $phoneTypeCondition = " AND `$tName`.$cond ";
1275                     //gross hack to pickup corrupted data also, CRM-7603
1276                     if (strpos($cond, 'phone_type_id') !== FALSE) {
1277                       $phoneTypeCondition = " AND ( `$tName`.$cond OR `$tName`.phone_type_id IS NULL ) ";
1278                       if (!empty($lCond)) {
1279                         $phoneTypeCondition .= " AND ( `$tName`.$lCond ) ";
1280                       }
1281                     }
1282                     $this->_tables[$tName] .= $phoneTypeCondition;
1283                   }
1284
1285                   //build locationType join
1286                   $locationTypeJoin[$tName] = " ( `$tName`.location_type_id = $ltName.id )";
1287
1288                   if ($addWhere) {
1289                     $this->_whereTables[$tName] = $this->_tables[$tName];
1290                   }
1291                   break;
1292
1293                 case 'civicrm_state_province':
1294                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['table'] = $tName;
1295                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['join']
1296                     = "\nLEFT JOIN $tableName `$tName` ON `$tName`.id = $aName.state_province_id";
1297                   if ($addWhere) {
1298                     $this->_whereTables["{$name}-address"] = $addressJoin;
1299                   }
1300                   break;
1301
1302                 case 'civicrm_country':
1303                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['table'] = $newName;
1304                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['join']
1305                     = "\nLEFT JOIN $tableName `$tName` ON `$tName`.id = $aName.country_id";
1306                   if ($addWhere) {
1307                     $this->_whereTables["{$name}-address"] = $addressJoin;
1308                   }
1309                   break;
1310
1311                 case 'civicrm_county':
1312                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['table'] = $newName;
1313                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['join']
1314                     = "\nLEFT JOIN $tableName `$tName` ON `$tName`.id = $aName.county_id";
1315                   if ($addWhere) {
1316                     $this->_whereTables["{$name}-address"] = $addressJoin;
1317                   }
1318                   break;
1319
1320                 default:
1321                   if (isset($addressCustomFields[$elementName]['custom_field_id']) && !empty($addressCustomFields[$elementName]['custom_field_id'])) {
1322                     $this->_tables[$tName] = "\nLEFT JOIN $tableName `$tName` ON `$tName`.id = $aName.id";
1323                   }
1324                   if ($addWhere) {
1325                     $this->_whereTables["{$name}-address"] = $addressJoin;
1326                   }
1327                   break;
1328               }
1329             }
1330           }
1331         }
1332       }
1333
1334       // add location type  join
1335       $ltypeJoin = "\nLEFT JOIN civicrm_location_type $ltName ON ( " . implode('OR', $locationTypeJoin) . " )";
1336       $this->_tables[$locationTypeName] = $ltypeJoin;
1337
1338       // table should be present in $this->_whereTables,
1339       // to add its condition in location type join, CRM-3939.
1340       if ($addWhereCount) {
1341         $locClause = [];
1342         foreach ($this->_whereTables as $tableName => $clause) {
1343           if (!empty($locationTypeJoin[$tableName])) {
1344             $locClause[] = $locationTypeJoin[$tableName];
1345           }
1346         }
1347
1348         if (!empty($locClause)) {
1349           $this->_whereTables[$locationTypeName] = "\nLEFT JOIN civicrm_location_type $ltName ON ( " . implode('OR', $locClause) . " )";
1350         }
1351       }
1352     }
1353
1354     if (!empty($addressCustomFieldIds)) {
1355       $customQuery = new CRM_Core_BAO_CustomQuery($addressCustomFieldIds);
1356       foreach ($addressCustomFieldIds as $cfID => $locTypeName) {
1357         foreach ($locTypeName as $name => $dnc) {
1358           $this->_locationSpecificCustomFields[$cfID] = [$name, array_search($name, $locationTypes)];
1359           $fieldName = "$name-custom_{$cfID}";
1360           $tName = "$name-address-custom-{$cfID}";
1361           $aName = "`$name-address-custom-{$cfID}`";
1362           $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
1363           $this->_element["{$tName}_id"] = 1;
1364           $this->_select[$fieldName] = "`$tName`.{$customQuery->_fields[$cfID]['column_name']} as `{$fieldName}`";
1365           $this->_element[$fieldName] = 1;
1366           $this->_tables[$tName] = "\nLEFT JOIN {$customQuery->_fields[$cfID]['table_name']} $aName ON ($aName.entity_id = `$name-address`.id)";
1367         }
1368       }
1369     }
1370   }
1371
1372   /**
1373    * If the return Properties are set in a hierarchy, traverse the hierarchy to get the return values.
1374    */
1375   public function addMultipleElements() {
1376     if (empty($this->_returnProperties['website'])) {
1377       return;
1378     }
1379     if (!is_array($this->_returnProperties['website'])) {
1380       return;
1381     }
1382
1383     foreach ($this->_returnProperties['website'] as $key => $elements) {
1384       foreach ($elements as $elementFullName => $dontCare) {
1385         $tName = "website-{$key}-{$elementFullName}";
1386         $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
1387         $this->_select["{$tName}"] = "`$tName`.url as `{$tName}`";
1388         $this->_element["{$tName}_id"] = 1;
1389         $this->_element["{$tName}"] = 1;
1390
1391         $type = "website-{$key}-website_type_id";
1392         $this->_select[$type] = "`$tName`.website_type_id as `{$type}`";
1393         $this->_element[$type] = 1;
1394         $this->_tables[$tName] = "\nLEFT JOIN civicrm_website `$tName` ON (`$tName`.contact_id = contact_a.id AND `$tName`.website_type_id = $key )";
1395       }
1396     }
1397   }
1398
1399   /**
1400    * Generate the query based on what type of query we need.
1401    *
1402    * @param bool $count
1403    * @param bool $sortByChar
1404    * @param bool $groupContacts
1405    * @param bool $onlyDeleted
1406    *
1407    * @return array
1408    *   sql query parts as an array
1409    */
1410   public function query($count = FALSE, $sortByChar = FALSE, $groupContacts = FALSE, $onlyDeleted = FALSE) {
1411     // build permission clause
1412     $this->generatePermissionClause($onlyDeleted, $count);
1413
1414     if ($count) {
1415       if (isset($this->_rowCountClause)) {
1416         $select = "SELECT {$this->_rowCountClause}";
1417       }
1418       elseif (isset($this->_distinctComponentClause)) {
1419         // we add distinct to get the right count for components
1420         // for the more complex result set, we use GROUP BY the same id
1421         // CRM-9630
1422         $select = "SELECT count( DISTINCT {$this->_distinctComponentClause} ) as rowCount";
1423       }
1424       else {
1425         $select = 'SELECT count(DISTINCT contact_a.id) as rowCount';
1426       }
1427       $from = $this->_simpleFromClause;
1428       if ($this->_useDistinct) {
1429         $this->_useGroupBy = TRUE;
1430       }
1431     }
1432     elseif ($sortByChar) {
1433       // @fixme add the deprecated warning back in (it breaks CRM_Contact_SelectorTest::testSelectorQuery)
1434       // CRM_Core_Error::deprecatedFunctionWarning('sort by char is deprecated - use alphabetQuery method');
1435       $select = 'SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name';
1436       $from = $this->_simpleFromClause;
1437     }
1438     elseif ($groupContacts) {
1439       $select = 'SELECT contact_a.id as id';
1440       if ($this->_useDistinct) {
1441         $this->_useGroupBy = TRUE;
1442       }
1443       $from = $this->_simpleFromClause;
1444     }
1445     else {
1446       if (!empty($this->_paramLookup['group'])) {
1447
1448         list($name, $op, $value, $grouping, $wildcard) = $this->_paramLookup['group'][0];
1449
1450         if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
1451           $this->_paramLookup['group'][0][1] = key($value);
1452         }
1453
1454         // Presumably the lines below come into manage groups screen.
1455         // make sure there is only one element
1456         // this is used when we are running under smog and need to know
1457         // how the contact was added (CRM-1203)
1458         $groups = (array) CRM_Utils_Array::value($this->_paramLookup['group'][0][1], $this->_paramLookup['group'][0][2], $this->_paramLookup['group'][0][2]);
1459         if ((count($this->_paramLookup['group']) == 1) &&
1460           (count($groups) == 1)
1461         ) {
1462           $groupId = $groups[0];
1463
1464           //check if group is saved search
1465           $group = new CRM_Contact_BAO_Group();
1466           $group->id = $groupId;
1467           $group->find(TRUE);
1468
1469           if (!isset($group->saved_search_id)) {
1470             $tbName = "civicrm_group_contact";
1471             // CRM-17254 don't retrieve extra fields if contact_id is specifically requested
1472             // as this will add load to an intentionally light query.
1473             // ideally this code would be removed as it appears to be to support CRM-1203
1474             // and passing in the required returnProperties from the url would
1475             // make more sense that globally applying the requirements of one form.
1476             if (($this->_returnProperties != ['contact_id'])) {
1477               $this->_select['group_contact_id'] = "$tbName.id as group_contact_id";
1478               $this->_element['group_contact_id'] = 1;
1479               $this->_select['status'] = "$tbName.status as status";
1480               $this->_element['status'] = 1;
1481             }
1482           }
1483         }
1484         $this->_useGroupBy = TRUE;
1485       }
1486       if ($this->_useDistinct && !isset($this->_distinctComponentClause)) {
1487         if (!($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY)) {
1488           // CRM-5954
1489           $this->_select['contact_id'] = 'contact_a.id as contact_id';
1490           $this->_useDistinct = FALSE;
1491           $this->_useGroupBy = TRUE;
1492         }
1493       }
1494
1495       $select = $this->getSelect();
1496       $from = $this->_fromClause;
1497     }
1498
1499     $where = '';
1500     if (!empty($this->_whereClause)) {
1501       $where = "WHERE {$this->_whereClause}";
1502     }
1503
1504     if (!empty($this->_permissionWhereClause) && empty($this->_displayRelationshipType)) {
1505       if (empty($where)) {
1506         $where = "WHERE $this->_permissionWhereClause";
1507       }
1508       else {
1509         $where = "$where AND $this->_permissionWhereClause";
1510       }
1511     }
1512
1513     $having = '';
1514     if (!empty($this->_having)) {
1515       foreach ($this->_having as $havingSets) {
1516         foreach ($havingSets as $havingSet) {
1517           $havingValue[] = $havingSet;
1518         }
1519       }
1520       $having = ' HAVING ' . implode(' AND ', $havingValue);
1521     }
1522
1523     // if we are doing a transform, do it here
1524     // use the $from, $where and $having to get the contact ID
1525     if ($this->_displayRelationshipType) {
1526       $this->filterRelatedContacts($from, $where, $having);
1527     }
1528
1529     return [$select, $from, $where, $having];
1530   }
1531
1532   /**
1533    * Get where values from the parameters.
1534    *
1535    * @param string $name
1536    * @param mixed $grouping
1537    *
1538    * @return mixed
1539    */
1540   public function getWhereValues($name, $grouping) {
1541     $result = NULL;
1542     foreach ($this->_params as $values) {
1543       if ($values[0] == $name && $values[3] == $grouping) {
1544         return $values;
1545       }
1546     }
1547
1548     return $result;
1549   }
1550
1551   /**
1552    * Fix date values.
1553    *
1554    * @param bool $relative
1555    * @param string $from
1556    * @param string $to
1557    */
1558   public static function fixDateValues($relative, &$from, &$to) {
1559     if ($relative) {
1560       list($from, $to) = CRM_Utils_Date::getFromTo($relative, $from, $to);
1561     }
1562   }
1563
1564   /**
1565    * Convert values from form-appropriate to query-object appropriate.
1566    *
1567    * The query object is increasingly supporting the sql-filter syntax which is the most flexible syntax.
1568    * So, ideally we would convert all fields to look like
1569    *  array(
1570    *   0 => $fieldName
1571    *   // Set the operator for legacy reasons, but it is ignored
1572    *   1 =>  '='
1573    *   // array in sql filter syntax
1574    *   2 => array('BETWEEN' => array(1,60),
1575    *   3 => null
1576    *   4 => null
1577    *  );
1578    *
1579    * There are some examples of the syntax in
1580    * https://github.com/civicrm/civicrm-core/tree/master/api/v3/examples/Relationship
1581    *
1582    * More notes at CRM_Core_DAO::createSQLFilter
1583    *
1584    * and a list of supported operators in CRM_Core_DAO
1585    *
1586    * @param array $formValues
1587    * @param int $wildcard
1588    * @param bool $useEquals
1589    *
1590    * @param string $apiEntity
1591    *
1592    * @param array $entityReferenceFields
1593    *   Field names of any entity reference fields (which will need reformatting to IN syntax).
1594    *
1595    * @return array
1596    */
1597   public static function convertFormValues(&$formValues, $wildcard = 0, $useEquals = FALSE, $apiEntity = NULL,
1598     $entityReferenceFields = []) {
1599     $params = [];
1600     if (empty($formValues)) {
1601       return $params;
1602     }
1603
1604     self::filterCountryFromValuesIfStateExists($formValues);
1605
1606     foreach ($formValues as $id => $values) {
1607       if (self::isAlreadyProcessedForQueryFormat($values)) {
1608         $params[] = $values;
1609         continue;
1610       }
1611
1612       self::legacyConvertFormValues($id, $values);
1613
1614       // The form uses 1 field to represent two db fields
1615       if ($id === 'contact_type' && $values && (!is_array($values) || !array_intersect(array_keys($values), CRM_Core_DAO::acceptedSQLOperators()))) {
1616         $contactType = [];
1617         $subType = [];
1618         foreach ((array) $values as $key => $type) {
1619           $types = explode('__', is_numeric($type) ? $key : $type, 2);
1620           $contactType[$types[0]] = $types[0];
1621           // Add sub-type if specified
1622           if (!empty($types[1])) {
1623             $subType[$types[1]] = $types[1];
1624           }
1625         }
1626         $params[] = ['contact_type', 'IN', $contactType, 0, 0];
1627         if ($subType) {
1628           $params[] = ['contact_sub_type', 'IN', $subType, 0, 0];
1629         }
1630       }
1631       elseif ($id === 'privacy') {
1632         if (is_array($formValues['privacy'])) {
1633           $op = !empty($formValues['privacy']['do_not_toggle']) ? '=' : '!=';
1634           foreach ($formValues['privacy'] as $key => $value) {
1635             if ($value) {
1636               $params[] = [$key, $op, $value, 0, 0];
1637             }
1638           }
1639         }
1640       }
1641       elseif ($id == 'email_on_hold') {
1642         if ($onHoldValue = CRM_Utils_Array::value('email_on_hold', $formValues)) {
1643           // onHoldValue should be 0 or 1 or an array. Some legacy groups may hold ''
1644           // so in 5.11 we have an extra if that should become redundant over time.
1645           // https://lab.civicrm.org/dev/core/issues/745
1646           // @todo this renaming of email_on_hold to on_hold needs revisiting
1647           // it precedes recent changes but causes the default not to reload.
1648           $onHoldValue = array_filter((array) $onHoldValue, 'is_numeric');
1649           if (!empty($onHoldValue)) {
1650             $params[] = ['on_hold', 'IN', $onHoldValue, 0, 0];
1651           }
1652         }
1653       }
1654       elseif (substr($id, 0, 7) == 'custom_'
1655         &&  (
1656           substr($id, -5, 5) == '_from'
1657           || substr($id, -3, 3) == '_to'
1658         )
1659       ) {
1660         self::convertCustomRelativeFields($formValues, $params, $values, $id);
1661       }
1662       elseif (in_array($id, $entityReferenceFields) && !empty($values) && is_string($values) && (strpos($values, ',') !=
1663         FALSE)) {
1664         $params[] = [$id, 'IN', explode(',', $values), 0, 0];
1665       }
1666       else {
1667         $values = CRM_Contact_BAO_Query::fixWhereValues($id, $values, $wildcard, $useEquals, $apiEntity);
1668
1669         if (!$values) {
1670           continue;
1671         }
1672         $params[] = $values;
1673       }
1674     }
1675     return $params;
1676   }
1677
1678   /**
1679    * Function to support legacy format for groups and tags.
1680    *
1681    * @param string $id
1682    * @param array|int $values
1683    *
1684    */
1685   public static function legacyConvertFormValues($id, &$values) {
1686     $legacyElements = [
1687       'group',
1688       'tag',
1689       'contact_tags',
1690       'contact_type',
1691       'membership_type_id',
1692       'membership_status_id',
1693     ];
1694     if (in_array($id, $legacyElements) && is_array($values)) {
1695       // prior to 4.7, formValues for some attributes (e.g. group, tag) are stored in array(id1 => 1, id2 => 1),
1696       // as per the recent Search fixes $values need to be in standard array(id1, id2) format
1697       $values = CRM_Utils_Array::convertCheckboxFormatToArray($values);
1698     }
1699   }
1700
1701   /**
1702    * Fix values from query from/to something no-one cared enough to document.
1703    *
1704    * @param int $id
1705    * @param array $values
1706    * @param int $wildcard
1707    * @param bool $useEquals
1708    *
1709    * @param string $apiEntity
1710    *
1711    * @return array|null
1712    */
1713   public static function fixWhereValues($id, &$values, $wildcard = 0, $useEquals = FALSE, $apiEntity = NULL) {
1714     // skip a few search variables
1715     static $skipWhere = NULL;
1716     static $likeNames = NULL;
1717     $result = NULL;
1718
1719     // Change camelCase EntityName to lowercase with underscores
1720     $apiEntity = _civicrm_api_get_entity_name_from_camel($apiEntity);
1721
1722     // check if $value is in OK (Operator as Key) format as used by Get API
1723     if (CRM_Utils_System::isNull($values)) {
1724       return $result;
1725     }
1726
1727     if (!$skipWhere) {
1728       $skipWhere = [
1729         'task',
1730         'radio_ts',
1731         'uf_group_id',
1732         'component_mode',
1733         'qfKey',
1734         'operator',
1735         'display_relationship_type',
1736       ];
1737     }
1738
1739     if (in_array($id, $skipWhere) ||
1740       substr($id, 0, 4) == '_qf_' ||
1741       substr($id, 0, 7) == 'hidden_'
1742     ) {
1743       return $result;
1744     }
1745
1746     if ($apiEntity &&
1747       (substr($id, 0, strlen($apiEntity)) != $apiEntity) &&
1748       (substr($id, 0, 10) != 'financial_' && substr($id, 0, 8) != 'payment_') &&
1749       (substr($id, 0, 7) != 'custom_')
1750     ) {
1751       $id = $apiEntity . '_' . $id;
1752     }
1753
1754     if (!$likeNames) {
1755       $likeNames = ['sort_name', 'email', 'note', 'display_name'];
1756     }
1757
1758     // email comes in via advanced search
1759     // so use wildcard always
1760     if ($id == 'email') {
1761       $wildcard = 1;
1762     }
1763
1764     if (!$useEquals && in_array($id, $likeNames)) {
1765       $result = [$id, 'LIKE', $values, 0, 1];
1766     }
1767     elseif (is_string($values) && strpos($values, '%') !== FALSE) {
1768       $result = [$id, 'LIKE', $values, 0, 0];
1769     }
1770     elseif ($id == 'contact_type' ||
1771       (!empty($values) && is_array($values) && !in_array(key($values), CRM_Core_DAO::acceptedSQLOperators(), TRUE))
1772     ) {
1773       $result = [$id, 'IN', $values, 0, $wildcard];
1774     }
1775     else {
1776       $result = [$id, '=', $values, 0, $wildcard];
1777     }
1778
1779     return $result;
1780   }
1781
1782   /**
1783    * Get the where clause for a single field.
1784    *
1785    * @param array $values
1786    * @param bool $isForcePrimaryOnly
1787    *
1788    * @throws \CRM_Core_Exception
1789    */
1790   public function whereClauseSingle(&$values, $isForcePrimaryOnly = FALSE) {
1791     if ($this->isARelativeDateField($values[0])) {
1792       $this->buildRelativeDateQuery($values);
1793       return;
1794     }
1795     // @todo also handle _low, _high generically here with if ($query->buildDateRangeQuery($values)) {return}
1796
1797     // do not process custom fields or prefixed contact ids or component params
1798     if (CRM_Core_BAO_CustomField::getKeyID($values[0]) ||
1799       (substr($values[0], 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) ||
1800       (substr($values[0], 0, 13) === 'contribution_') ||
1801       (substr($values[0], 0, 6) === 'event_') ||
1802       (substr($values[0], 0, 12) === 'participant_') ||
1803       (substr($values[0], 0, 7) === 'member_') ||
1804       (substr($values[0], 0, 6) === 'grant_') ||
1805       (substr($values[0], 0, 7) === 'pledge_') ||
1806       (substr($values[0], 0, 5) === 'case_') ||
1807       (substr($values[0], 0, 10) === 'financial_') ||
1808       (substr($values[0], 0, 8) === 'payment_') ||
1809       (substr($values[0], 0, 11) === 'membership_')
1810     ) {
1811       return;
1812     }
1813
1814     // skip for hook injected fields / params
1815     $extFields = CRM_Contact_BAO_Query_Hook::singleton()->getFields();
1816     if (array_key_exists($values[0], $extFields)) {
1817       return;
1818     }
1819
1820     switch ($values[0]) {
1821       case 'deleted_contacts':
1822         $this->deletedContacts($values);
1823         return;
1824
1825       case 'contact_sub_type':
1826         $this->contactSubType($values);
1827         return;
1828
1829       case 'group':
1830       case 'group_type':
1831         $this->group($values);
1832         return;
1833
1834       // case tag comes from find contacts
1835       case 'tag_search':
1836         $this->tagSearch($values);
1837         return;
1838
1839       case 'tag':
1840       case 'contact_tags':
1841         $this->tag($values);
1842         return;
1843
1844       case 'note':
1845       case 'note_body':
1846       case 'note_subject':
1847         $this->notes($values);
1848         return;
1849
1850       case 'uf_user':
1851         $this->ufUser($values);
1852         return;
1853
1854       case 'sort_name':
1855       case 'display_name':
1856         $this->sortName($values);
1857         return;
1858
1859       case 'addressee':
1860       case 'postal_greeting':
1861       case 'email_greeting':
1862         $this->greetings($values);
1863         return;
1864
1865       case 'email':
1866       case 'email_id':
1867         $this->email($values, $isForcePrimaryOnly);
1868         return;
1869
1870       case 'phone_numeric':
1871         $this->phone_numeric($values);
1872         return;
1873
1874       case 'phone_phone_type_id':
1875       case 'phone_location_type_id':
1876         $this->phone_option_group($values);
1877         return;
1878
1879       case 'street_address':
1880         $this->street_address($values);
1881         return;
1882
1883       case 'street_number':
1884         $this->street_number($values);
1885         return;
1886
1887       case 'sortByCharacter':
1888         $this->sortByCharacter($values);
1889         return;
1890
1891       case 'location_type':
1892         $this->locationType($values);
1893         return;
1894
1895       case 'county':
1896         $this->county($values);
1897         return;
1898
1899       case 'state_province':
1900       case 'state_province_id':
1901       case 'state_province_name':
1902         $this->stateProvince($values);
1903         return;
1904
1905       case 'country':
1906       case 'country_id':
1907         $this->country($values, FALSE);
1908         return;
1909
1910       case 'postal_code':
1911       case 'postal_code_low':
1912       case 'postal_code_high':
1913         $this->postalCode($values);
1914         return;
1915
1916       case 'activity_date':
1917       case 'activity_date_low':
1918       case 'activity_date_high':
1919       case 'activity_date_time_low':
1920       case 'activity_date_time_high':
1921       case 'activity_role':
1922       case 'activity_status_id':
1923       case 'activity_status':
1924       case 'activity_priority':
1925       case 'activity_priority_id':
1926       case 'followup_parent_id':
1927       case 'parent_id':
1928       case 'source_contact_id':
1929       case 'activity_text':
1930       case 'activity_option':
1931       case 'test_activities':
1932       case 'activity_type_id':
1933       case 'activity_type':
1934       case 'activity_survey_id':
1935       case 'activity_tags':
1936       case 'activity_taglist':
1937       case 'activity_test':
1938       case 'activity_campaign_id':
1939       case 'activity_engagement_level':
1940       case 'activity_id':
1941       case 'activity_result':
1942       case 'source_contact':
1943         CRM_Activity_BAO_Query::whereClauseSingle($values, $this);
1944         return;
1945
1946       case 'age_low':
1947       case 'age_high':
1948       case 'birth_date_low':
1949       case 'birth_date_high':
1950       case 'deceased_date_low':
1951       case 'deceased_date_high':
1952         $this->demographics($values);
1953         return;
1954
1955       case 'age_asof_date':
1956         // handled by demographics
1957         return;
1958
1959       case 'log_date_low':
1960       case 'log_date_high':
1961         $this->modifiedDates($values);
1962         return;
1963
1964       case 'changed_by':
1965         $this->changeLog($values);
1966         return;
1967
1968       case 'do_not_phone':
1969       case 'do_not_email':
1970       case 'do_not_mail':
1971       case 'do_not_sms':
1972       case 'do_not_trade':
1973       case 'is_opt_out':
1974         $this->privacy($values);
1975         return;
1976
1977       case 'privacy_options':
1978         $this->privacyOptions($values);
1979         return;
1980
1981       case 'privacy_operator':
1982       case 'privacy_toggle':
1983         // these are handled by privacy options
1984         return;
1985
1986       case 'preferred_communication_method':
1987         $this->preferredCommunication($values);
1988         return;
1989
1990       case 'relation_type_id':
1991       case 'relationship_start_date_high':
1992       case 'relationship_start_date_low':
1993       case 'relationship_end_date_high':
1994       case 'relationship_end_date_low':
1995       case 'relation_active_period_date_high':
1996       case 'relation_active_period_date_low':
1997       case 'relation_target_name':
1998       case 'relation_status':
1999       case 'relation_description':
2000       case 'relation_date_low':
2001       case 'relation_date_high':
2002         $this->relationship($values);
2003         $this->_relationshipValuesAdded = TRUE;
2004         return;
2005
2006       case 'task_status_id':
2007         $this->task($values);
2008         return;
2009
2010       case 'task_id':
2011         // since this case is handled with the above
2012         return;
2013
2014       case 'prox_distance':
2015         CRM_Contact_BAO_ProximityQuery::process($this, $values);
2016         return;
2017
2018       case 'prox_street_address':
2019       case 'prox_city':
2020       case 'prox_postal_code':
2021       case 'prox_state_province_id':
2022       case 'prox_country_id':
2023       case 'prox_geo_code_1':
2024       case 'prox_geo_code_2':
2025         // handled by the proximity_distance clause
2026         return;
2027
2028       default:
2029         $this->restWhere($values);
2030         return;
2031     }
2032   }
2033
2034   /**
2035    * Given a list of conditions in params generate the required where clause.
2036    *
2037    * @param bool $isForcePrimaryEmailOnly
2038    *
2039    * @return string
2040    * @throws \CRM_Core_Exception
2041    */
2042   public function whereClause($isForcePrimaryEmailOnly = NULL) {
2043     $this->_where[0] = [];
2044     $this->_qill[0] = [];
2045
2046     $this->includeContactIDs();
2047     if (!empty($this->_params)) {
2048       foreach (array_keys($this->_params) as $id) {
2049         if (empty($this->_params[$id][0])) {
2050           continue;
2051         }
2052         // check for both id and contact_id
2053         if ($this->_params[$id][0] == 'id' || $this->_params[$id][0] == 'contact_id') {
2054           $this->_where[0][] = self::buildClause("contact_a.id", $this->_params[$id][1], $this->_params[$id][2]);
2055           $field = CRM_Utils_Array::value('id', $this->_fields);
2056           list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(
2057             'CRM_Contact_BAO_Contact',
2058             "contact_a.id",
2059             $this->_params[$id][2],
2060             $this->_params[$id][1]
2061           );
2062           $this->_qill[0][] = ts("%1 %2 %3", [
2063             1 => $field['title'],
2064             2 => $qillop,
2065             3 => $qillVal,
2066           ]);
2067         }
2068         else {
2069           $this->whereClauseSingle($this->_params[$id], $isForcePrimaryEmailOnly);
2070         }
2071       }
2072
2073       CRM_Core_Component::alterQuery($this, 'where');
2074
2075       CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'where');
2076     }
2077
2078     if ($this->_customQuery) {
2079       $this->_whereTables = array_merge($this->_whereTables, $this->_customQuery->_whereTables);
2080       // Added following if condition to avoid the wrong value display for 'my account' / any UF info.
2081       // Hope it wont affect the other part of civicrm.. if it does please remove it.
2082       if (!empty($this->_customQuery->_where)) {
2083         $this->_where = CRM_Utils_Array::crmArrayMerge($this->_where, $this->_customQuery->_where);
2084       }
2085       $this->_qill = CRM_Utils_Array::crmArrayMerge($this->_qill, $this->_customQuery->_qill);
2086     }
2087
2088     $clauses = [];
2089     $andClauses = [];
2090
2091     $validClauses = 0;
2092     if (!empty($this->_where)) {
2093       foreach ($this->_where as $grouping => $values) {
2094         if ($grouping > 0 && !empty($values)) {
2095           $clauses[$grouping] = ' ( ' . implode(" {$this->_operator} ", $values) . ' ) ';
2096           $validClauses++;
2097         }
2098       }
2099
2100       if (!empty($this->_where[0])) {
2101         $andClauses[] = ' ( ' . implode(" {$this->_operator} ", $this->_where[0]) . ' ) ';
2102       }
2103       if (!empty($clauses)) {
2104         $andClauses[] = ' ( ' . implode(' OR ', $clauses) . ' ) ';
2105       }
2106
2107       if ($validClauses > 1) {
2108         $this->_useDistinct = TRUE;
2109       }
2110     }
2111
2112     return implode(' AND ', $andClauses);
2113   }
2114
2115   /**
2116    * Generate where clause for any parameters not already handled.
2117    *
2118    * @param array $values
2119    *
2120    * @throws Exception
2121    */
2122   public function restWhere(&$values) {
2123     $name = CRM_Utils_Array::value(0, $values);
2124     $op = CRM_Utils_Array::value(1, $values);
2125     $value = CRM_Utils_Array::value(2, $values);
2126     $grouping = CRM_Utils_Array::value(3, $values);
2127     $wildcard = CRM_Utils_Array::value(4, $values);
2128
2129     if (isset($grouping) && empty($this->_where[$grouping])) {
2130       $this->_where[$grouping] = [];
2131     }
2132
2133     $multipleFields = ['url'];
2134
2135     //check if the location type exists for fields
2136     $lType = '';
2137     $locType = explode('-', $name);
2138
2139     if (!in_array($locType[0], $multipleFields)) {
2140       //add phone type if exists
2141       if (isset($locType[2]) && $locType[2]) {
2142         $locType[2] = CRM_Core_DAO::escapeString($locType[2]);
2143       }
2144     }
2145
2146     $field = CRM_Utils_Array::value($name, $this->_fields);
2147
2148     if (!$field) {
2149       $field = CRM_Utils_Array::value($locType[0], $this->_fields);
2150
2151       if (!$field) {
2152         // Strip any trailing _high & _low that might be appended.
2153         $realFieldName = str_replace(['_high', '_low'], '', $name);
2154         if (isset($this->_fields[$realFieldName])) {
2155           $field = $this->_fields[str_replace(['_high', '_low'], '', $realFieldName)];
2156           $columnName = $field['column_name'] ?? $field['name'];
2157           $this->dateQueryBuilder($values, $field['table_name'], $realFieldName, $columnName, $field['title']);
2158         }
2159         return;
2160       }
2161     }
2162
2163     $setTables = TRUE;
2164
2165     $locationType = CRM_Core_DAO_Address::buildOptions('location_type_id', 'validate');
2166     if (isset($locType[1]) && is_numeric($locType[1])) {
2167       $lType = $locationType[$locType[1]];
2168     }
2169     if ($lType) {
2170       $field['title'] .= " ($lType)";
2171     }
2172
2173     if (substr($name, 0, 14) === 'state_province') {
2174       if (isset($locType[1]) && is_numeric($locType[1])) {
2175         $setTables = FALSE;
2176         $aName = "{$lType}-address";
2177         $where = "`$aName`.state_province_id";
2178       }
2179       else {
2180         $where = "civicrm_address.state_province_id";
2181       }
2182
2183       $this->_where[$grouping][] = self::buildClause($where, $op, $value);
2184       $this->_tables[$aName] = $this->_whereTables[$aName] = 1;
2185       list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_Address', "state_province_id", $value, $op);
2186       $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $field['title'], 2 => $qillop, 3 => $qillVal]);
2187     }
2188     elseif (!empty($field['pseudoconstant'])) {
2189       $this->optionValueQuery(
2190         $name, $op, $value, $grouping,
2191         'CRM_Contact_DAO_Contact',
2192         $field,
2193         $field['title'],
2194         'String',
2195         TRUE
2196       );
2197       if ($name == 'gender_id') {
2198         self::$_openedPanes[ts('Demographics')] = TRUE;
2199       }
2200     }
2201     elseif (substr($name, 0, 7) === 'country' || substr($name, 0, 6) === 'county') {
2202       $name = (substr($name, 0, 7) === 'country') ? "country_id" : "county_id";
2203       if (isset($locType[1]) && is_numeric($locType[1])) {
2204         $setTables = FALSE;
2205         $aName = "{$lType}-address";
2206         $where = "`$aName`.$name";
2207       }
2208       else {
2209         $where = "civicrm_address.$name";
2210       }
2211
2212       $this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
2213       $this->_tables[$aName] = $this->_whereTables[$aName] = 1;
2214
2215       list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
2216       $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $field['title'], 2 => $qillop, 3 => $qillVal]);
2217     }
2218     elseif ($name === 'world_region') {
2219       $this->optionValueQuery(
2220         $name, $op, $value, $grouping,
2221         NULL,
2222         $field,
2223         ts('World Region'),
2224         'Positive',
2225         TRUE
2226       );
2227     }
2228     elseif ($name === 'is_deceased') {
2229       $this->setQillAndWhere($name, $op, $value, $grouping, $field);
2230       self::$_openedPanes[ts('Demographics')] = TRUE;
2231     }
2232     elseif ($name === 'created_date' || $name === 'modified_date' || $name === 'deceased_date' || $name === 'birth_date') {
2233       $appendDateTime = TRUE;
2234       if ($name === 'deceased_date' || $name === 'birth_date') {
2235         $appendDateTime = FALSE;
2236         self::$_openedPanes[ts('Demographics')] = TRUE;
2237       }
2238       $this->dateQueryBuilder($values, 'contact_a', $name, $name, $field['title'], $appendDateTime);
2239     }
2240     elseif ($name === 'contact_id') {
2241       if (is_int($value)) {
2242         $this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
2243         $this->_qill[$grouping][] = "$field[title] $op $value";
2244       }
2245     }
2246     elseif ($name === 'name') {
2247       $value = CRM_Core_DAO::escapeString($value);
2248       if ($wildcard) {
2249         $op = 'LIKE';
2250         $value = self::getWildCardedValue($wildcard, $op, $value);
2251       }
2252       CRM_Core_Error::deprecatedFunctionWarning('Untested code path');
2253       // @todo it's likely this code path is obsolete / never called. It is definitely not
2254       // passed through in our test suite.
2255       $this->_where[$grouping][] = self::buildClause($field['where'], $op, "'$value'");
2256       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2257     }
2258     elseif ($name === 'current_employer') {
2259       if ($wildcard) {
2260         $op = 'LIKE';
2261         $value = self::getWildCardedValue($wildcard, $op, $value);
2262       }
2263       $ceWhereClause = self::buildClause("contact_a.organization_name", $op,
2264         $value
2265       );
2266       $ceWhereClause .= " AND contact_a.contact_type = 'Individual'";
2267       $this->_where[$grouping][] = $ceWhereClause;
2268       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2269     }
2270     elseif ($name === 'email_greeting') {
2271       CRM_Core_Error::deprecatedFunctionWarning('pass in email_greeting_id or email_greeting_display');
2272       $filterCondition = ['greeting_type' => 'email_greeting'];
2273       $this->optionValueQuery(
2274         $name, $op, $value, $grouping,
2275         CRM_Core_PseudoConstant::greeting($filterCondition),
2276         $field,
2277         ts('Email Greeting')
2278       );
2279     }
2280     elseif ($name === 'postal_greeting') {
2281       CRM_Core_Error::deprecatedFunctionWarning('pass in postal_greeting_id or postal_greeting_display');
2282       $filterCondition = ['greeting_type' => 'postal_greeting'];
2283       $this->optionValueQuery(
2284         $name, $op, $value, $grouping,
2285         CRM_Core_PseudoConstant::greeting($filterCondition),
2286         $field,
2287         ts('Postal Greeting')
2288       );
2289     }
2290     elseif ($name === 'addressee') {
2291       CRM_Core_Error::deprecatedFunctionWarning('pass in addressee_id or addressee_display');
2292       $filterCondition = ['greeting_type' => 'addressee'];
2293       $this->optionValueQuery(
2294         $name, $op, $value, $grouping,
2295         CRM_Core_PseudoConstant::greeting($filterCondition),
2296         $field,
2297         ts('Addressee')
2298       );
2299     }
2300     elseif (substr($name, 0, 4) === 'url-') {
2301       $tName = 'civicrm_website';
2302       $this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
2303       $value = CRM_Core_DAO::escapeString($value);
2304       if ($wildcard) {
2305         $op = 'LIKE';
2306         $value = self::getWildCardedValue($wildcard, $op, $value);
2307       }
2308
2309       $this->_where[$grouping][] = $d = self::buildClause('civicrm_website.url', $op, $value);
2310       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2311     }
2312     elseif ($name === 'contact_is_deleted') {
2313       $this->setQillAndWhere('is_deleted', $op, $value, $grouping, $field);
2314     }
2315     elseif (!empty($field['where'])) {
2316       $type = NULL;
2317       if (!empty($field['type'])) {
2318         $type = CRM_Utils_Type::typeToString($field['type']);
2319       }
2320
2321       list($tableName, $fieldName) = explode('.', $field['where'], 2);
2322
2323       if (isset($locType[1]) &&
2324         is_numeric($locType[1])
2325       ) {
2326         $setTables = FALSE;
2327
2328         //get the location name
2329         list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
2330         $fieldName = "`$tName`.$fldName";
2331
2332         // we set both _tables & whereTables because whereTables doesn't seem to do what the name implies it should
2333         $this->_tables[$tName] = $this->_whereTables[$tName] = 1;
2334
2335       }
2336       else {
2337         if ($tableName == 'civicrm_contact') {
2338           $fieldName = "contact_a.{$fieldName}";
2339         }
2340         else {
2341           $fieldName = $field['where'];
2342         }
2343       }
2344
2345       list($qillop, $qillVal) = self::buildQillForFieldValue(NULL, $field['title'], $value, $op);
2346       $this->_qill[$grouping][] = ts("%1 %2 %3", [
2347         1 => $field['title'],
2348         2 => $qillop,
2349         3 => (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) ? $qillVal : "'$qillVal'",
2350       ]);
2351
2352       if (is_array($value)) {
2353         // traditionally an array being passed has been a fatal error. We can take advantage of this to add support
2354         // for api style operators for functions that hit this point without worrying about regression
2355         // (the previous comments indicated the condition for hitting this point were unknown
2356         // per CRM-14743 we are adding modified_date & created_date operator support
2357         $operations = array_keys($value);
2358         foreach ($operations as $operator) {
2359           if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) {
2360             //Via Contact get api value is not in array(operator => array(values)) format ONLY for IN/NOT IN operators
2361             //so this condition will satisfy the search for now
2362             if (strpos($op, 'IN') !== FALSE) {
2363               $value = [$op => $value];
2364             }
2365             // we don't know when this might happen
2366             else {
2367               CRM_Core_Error::fatal(ts("%1 is not a valid operator", [1 => $operator]));
2368             }
2369           }
2370         }
2371         $this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($fieldName, $value, $type);
2372       }
2373       else {
2374         if ($wildcard) {
2375           $op = 'LIKE';
2376           $value = self::getWildCardedValue($wildcard, $op, $value);
2377         }
2378
2379         $this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
2380       }
2381     }
2382
2383     if ($setTables && isset($field['where'])) {
2384       list($tableName, $fieldName) = explode('.', $field['where'], 2);
2385       if (isset($tableName)) {
2386         $this->_tables[$tableName] = 1;
2387         $this->_whereTables[$tableName] = 1;
2388       }
2389     }
2390   }
2391
2392   /**
2393    * @param $where
2394    * @param $locType
2395    *
2396    * @return array
2397    * @throws Exception
2398    */
2399   public static function getLocationTableName(&$where, &$locType) {
2400     if (isset($locType[1]) && is_numeric($locType[1])) {
2401       list($tbName, $fldName) = explode(".", $where);
2402
2403       //get the location name
2404       $locationType = CRM_Core_DAO_Address::buildOptions('location_type_id', 'validate');
2405       $specialFields = ['email', 'im', 'phone', 'openid', 'phone_ext'];
2406       if (in_array($locType[0], $specialFields)) {
2407         //hack to fix / special handing for phone_ext
2408         if ($locType[0] == 'phone_ext') {
2409           $locType[0] = 'phone';
2410         }
2411         if (isset($locType[2]) && $locType[2]) {
2412           $tName = "{$locationType[$locType[1]]}-{$locType[0]}-{$locType[2]}";
2413         }
2414         else {
2415           $tName = "{$locationType[$locType[1]]}-{$locType[0]}";
2416         }
2417       }
2418       elseif (in_array($locType[0],
2419         [
2420           'address_name',
2421           'street_address',
2422           'street_name',
2423           'street_number_suffix',
2424           'street_unit',
2425           'supplemental_address_1',
2426           'supplemental_address_2',
2427           'supplemental_address_3',
2428           'city',
2429           'postal_code',
2430           'postal_code_suffix',
2431           'geo_code_1',
2432           'geo_code_2',
2433           'master_id',
2434         ]
2435       )) {
2436         //fix for search by profile with address fields.
2437         $tName = "{$locationType[$locType[1]]}-address";
2438       }
2439       elseif (in_array($locType[0],
2440           [
2441             'on_hold',
2442             'signature_html',
2443             'signature_text',
2444             'is_bulkmail',
2445           ]
2446         )) {
2447         $tName = "{$locationType[$locType[1]]}-email";
2448       }
2449       elseif ($locType[0] == 'provider_id') {
2450         $tName = "{$locationType[$locType[1]]}-im";
2451       }
2452       elseif ($locType[0] == 'openid') {
2453         $tName = "{$locationType[$locType[1]]}-openid";
2454       }
2455       else {
2456         $tName = "{$locationType[$locType[1]]}-{$locType[0]}";
2457       }
2458       $tName = str_replace(' ', '_', $tName);
2459       return [$tName, $fldName];
2460     }
2461     CRM_Core_Error::fatal();
2462   }
2463
2464   /**
2465    * Given a result dao, extract the values and return that array
2466    *
2467    * @param CRM_Core_DAO $dao
2468    *
2469    * @return array
2470    *   values for this query
2471    */
2472   public function store($dao) {
2473     $value = [];
2474
2475     foreach ($this->_element as $key => $dontCare) {
2476       if (property_exists($dao, $key)) {
2477         if (strpos($key, '-') !== FALSE) {
2478           $values = explode('-', $key);
2479           $lastElement = array_pop($values);
2480           $current = &$value;
2481           $cnt = count($values);
2482           $count = 1;
2483           foreach ($values as $v) {
2484             if (!array_key_exists($v, $current)) {
2485               $current[$v] = [];
2486             }
2487             //bad hack for im_provider
2488             if ($lastElement == 'provider_id') {
2489               if ($count < $cnt) {
2490                 $current = &$current[$v];
2491               }
2492               else {
2493                 $lastElement = "{$v}_{$lastElement}";
2494               }
2495             }
2496             else {
2497               $current = &$current[$v];
2498             }
2499             $count++;
2500           }
2501
2502           $current[$lastElement] = $dao->$key;
2503         }
2504         else {
2505           $value[$key] = $dao->$key;
2506         }
2507       }
2508     }
2509     return $value;
2510   }
2511
2512   /**
2513    * Getter for tables array.
2514    *
2515    * @return array
2516    */
2517   public function tables() {
2518     return $this->_tables;
2519   }
2520
2521   /**
2522    * Sometimes used to create the from clause, but, not reliably, set
2523    * this AND set tables.
2524    *
2525    * It's unclear the intent - there is a 'simpleFrom' clause which
2526    * takes whereTables into account & a fromClause which doesn't.
2527    *
2528    * logic may have eroded?
2529    *
2530    * @return array
2531    */
2532   public function whereTables() {
2533     return $this->_whereTables;
2534   }
2535
2536   /**
2537    * Generate the where clause (used in match contacts and permissions)
2538    *
2539    * @param array $params
2540    * @param array $fields
2541    * @param array $tables
2542    * @param $whereTables
2543    * @param bool $strict
2544    *
2545    * @return string
2546    * @throws \CRM_Core_Exception
2547    */
2548   public static function getWhereClause($params, $fields, &$tables, &$whereTables, $strict = FALSE) {
2549     $query = new CRM_Contact_BAO_Query($params, NULL, $fields,
2550       FALSE, $strict
2551     );
2552
2553     $tables = array_merge($query->tables(), $tables);
2554     $whereTables = array_merge($query->whereTables(), $whereTables);
2555
2556     return $query->_whereClause;
2557   }
2558
2559   /**
2560    * Create the from clause.
2561    *
2562    * @param array $tables
2563    *   Tables that need to be included in this from clause. If null,
2564    *   return mimimal from clause (i.e. civicrm_contact).
2565    * @param array $inner
2566    *   Tables that should be inner-joined.
2567    * @param array $right
2568    *   Tables that should be right-joined.
2569    * @param bool $primaryLocation
2570    *   Search on primary location. See note below.
2571    * @param int $mode
2572    *   Determines search mode based on bitwise MODE_* constants.
2573    * @param string|NULL $apiEntity
2574    *   Determines search mode based on entity by string.
2575    *
2576    * The $primaryLocation flag only seems to be used when
2577    * locationType() has been called. This may be a search option
2578    * exposed, or perhaps it's a "search all details" approach which
2579    * predates decoupling of location types and primary fields?
2580    *
2581    * @see https://issues.civicrm.org/jira/browse/CRM-19967
2582    *
2583    * @return string
2584    *   the from clause
2585    */
2586   public static function fromClause(&$tables, $inner = NULL, $right = NULL, $primaryLocation = TRUE, $mode = 1, $apiEntity = NULL) {
2587
2588     $from = ' FROM civicrm_contact contact_a';
2589     if (empty($tables)) {
2590       return $from;
2591     }
2592
2593     if (!empty($tables['civicrm_worldregion'])) {
2594       $tables = array_merge(['civicrm_country' => 1], $tables);
2595     }
2596
2597     if ((!empty($tables['civicrm_state_province']) || !empty($tables['civicrm_country']) ||
2598         CRM_Utils_Array::value('civicrm_county', $tables)) && empty($tables['civicrm_address'])) {
2599       $tables = array_merge(['civicrm_address' => 1],
2600         $tables
2601       );
2602     }
2603
2604     // add group_contact and group table is subscription history is present
2605     if (!empty($tables['civicrm_subscription_history']) && empty($tables['civicrm_group'])) {
2606       $tables = array_merge([
2607         'civicrm_group' => 1,
2608         'civicrm_group_contact' => 1,
2609       ],
2610         $tables
2611       );
2612     }
2613
2614     // to handle table dependencies of components
2615     CRM_Core_Component::tableNames($tables);
2616     // to handle table dependencies of hook injected tables
2617     CRM_Contact_BAO_Query_Hook::singleton()->setTableDependency($tables);
2618
2619     //format the table list according to the weight
2620     $info = CRM_Core_TableHierarchy::info();
2621
2622     foreach ($tables as $key => $value) {
2623       $k = 99;
2624       if (strpos($key, '-') !== FALSE) {
2625         $keyArray = explode('-', $key);
2626         $k = CRM_Utils_Array::value('civicrm_' . $keyArray[1], $info, 99);
2627       }
2628       elseif (strpos($key, '_') !== FALSE) {
2629         $keyArray = explode('_', $key);
2630         if (is_numeric(array_pop($keyArray))) {
2631           $k = CRM_Utils_Array::value(implode('_', $keyArray), $info, 99);
2632         }
2633         else {
2634           $k = CRM_Utils_Array::value($key, $info, 99);
2635         }
2636       }
2637       else {
2638         $k = CRM_Utils_Array::value($key, $info, 99);
2639       }
2640       $tempTable[$k . ".$key"] = $key;
2641     }
2642     ksort($tempTable);
2643     $newTables = [];
2644     foreach ($tempTable as $key) {
2645       $newTables[$key] = $tables[$key];
2646     }
2647
2648     $tables = $newTables;
2649
2650     foreach ($tables as $name => $value) {
2651       if (!$value) {
2652         continue;
2653       }
2654
2655       if (!empty($inner[$name])) {
2656         $side = 'INNER';
2657       }
2658       elseif (!empty($right[$name])) {
2659         $side = 'RIGHT';
2660       }
2661       else {
2662         $side = 'LEFT';
2663       }
2664
2665       if ($value != 1) {
2666         // if there is already a join statement in value, use value itself
2667         if (strpos($value, 'JOIN')) {
2668           $from .= " $value ";
2669         }
2670         else {
2671           $from .= " $side JOIN $name ON ( $value ) ";
2672         }
2673         continue;
2674       }
2675
2676       $from .= ' ' . trim(self::getEntitySpecificJoins($name, $mode, $side, $primaryLocation)) . ' ';
2677     }
2678     return $from;
2679   }
2680
2681   /**
2682    * Get join statements for the from clause depending on entity type
2683    *
2684    * @param string $name
2685    * @param int $mode
2686    * @param string $side
2687    * @param string $primaryLocation
2688    * @return string
2689    */
2690   protected static function getEntitySpecificJoins($name, $mode, $side, $primaryLocation) {
2691     $limitToPrimaryClause = $primaryLocation ? "AND {$name}.is_primary = 1" : '';
2692     switch ($name) {
2693       case 'civicrm_address':
2694         //CRM-14263 further handling of address joins further down...
2695         return " $side JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id {$limitToPrimaryClause} )";
2696
2697       case 'civicrm_state_province':
2698         // This is encountered when doing an export after having applied a 'sort' - it pretty much implies primary
2699         // but that will have been implied-in by the calling function.
2700         // test cover in testContactIDQuery
2701         return " $side JOIN civicrm_state_province ON ( civicrm_address.state_province_id = civicrm_state_province.id )";
2702
2703       case 'civicrm_country':
2704         // This is encountered when doing an export after having applied a 'sort' - it pretty much implies primary
2705         // but that will have been implied-in by the calling function.
2706         // test cover in testContactIDQuery
2707         return " $side JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id )";
2708
2709       case 'civicrm_phone':
2710         return " $side JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id {$limitToPrimaryClause}) ";
2711
2712       case 'civicrm_email':
2713         return " $side JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id {$limitToPrimaryClause})";
2714
2715       case 'civicrm_im':
2716         return " $side JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id {$limitToPrimaryClause}) ";
2717
2718       case 'im_provider':
2719         $from = " $side JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id) ";
2720         $from .= " $side JOIN civicrm_option_group option_group_imProvider ON option_group_imProvider.name = 'instant_messenger_service'";
2721         $from .= " $side JOIN civicrm_option_value im_provider ON (civicrm_im.provider_id = im_provider.value AND option_group_imProvider.id = im_provider.option_group_id)";
2722         return $from;
2723
2724       case 'civicrm_openid':
2725         return " $side JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id {$limitToPrimaryClause} )";
2726
2727       case 'civicrm_worldregion':
2728         // We can be sure from the calling function that country will already be joined in.
2729         // we really don't need world_region - we could use a pseudoconstant for it.
2730         return " $side JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ";
2731
2732       case 'civicrm_location_type':
2733         return " $side JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id ";
2734
2735       case 'civicrm_group':
2736         return " $side JOIN civicrm_group ON civicrm_group.id = civicrm_group_contact.group_id ";
2737
2738       case 'civicrm_group_contact':
2739         return " $side JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id ";
2740
2741       case 'civicrm_group_contact_cache':
2742         return " $side JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id ";
2743
2744       case 'civicrm_activity':
2745       case 'civicrm_activity_tag':
2746       case 'activity_type':
2747       case 'activity_status':
2748       case 'parent_id':
2749       case 'civicrm_activity_contact':
2750       case 'source_contact':
2751       case 'activity_priority':
2752         return CRM_Activity_BAO_Query::from($name, $mode, $side);
2753
2754       case 'civicrm_entity_tag':
2755         $from = " $side JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact'";
2756         return "$from AND civicrm_entity_tag.entity_id = contact_a.id ) ";
2757
2758       case 'civicrm_note':
2759         $from = " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact'";
2760         return "$from AND contact_a.id = civicrm_note.entity_id ) ";
2761
2762       case 'civicrm_subscription_history':
2763         $from = " $side JOIN civicrm_subscription_history";
2764         $from .= " ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id";
2765         return "$from AND civicrm_group_contact.group_id =  civicrm_subscription_history.group_id";
2766
2767       case 'civicrm_relationship':
2768         if (self::$_relType == 'reciprocal') {
2769           if (self::$_relationshipTempTable) {
2770             // we have a temptable to join on
2771             $tbl = self::$_relationshipTempTable;
2772             return " INNER JOIN {$tbl} civicrm_relationship ON civicrm_relationship.contact_id = contact_a.id";
2773           }
2774           else {
2775             $from = " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id)";
2776             $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.contact_id_b = contact_b.id)";
2777             return $from;
2778           }
2779         }
2780         elseif (self::$_relType == 'b') {
2781           $from = " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id )";
2782           return "$from $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )";
2783         }
2784         else {
2785           $from = " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id )";
2786           return "$from $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )";
2787         }
2788
2789       case 'civicrm_log':
2790         $from = " INNER JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact')";
2791         return "$from INNER JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id)";
2792
2793       case 'civicrm_tag':
2794         return " $side  JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id ";
2795
2796       case 'civicrm_grant':
2797         return CRM_Grant_BAO_Query::from($name, $mode, $side);
2798
2799       case 'civicrm_website':
2800         return " $side JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id ";
2801
2802       case 'civicrm_campaign':
2803         //Move to default case if not in either mode.
2804         if ($mode & CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
2805           return CRM_Contribute_BAO_Query::from($name, $mode, $side);
2806         }
2807         elseif ($mode & CRM_Contact_BAO_Query::MODE_MAILING) {
2808           return CRM_Mailing_BAO_Query::from($name, $mode, $side);
2809         }
2810         elseif ($mode & CRM_Contact_BAO_Query::MODE_CAMPAIGN) {
2811           return CRM_Campaign_BAO_Query::from($name, $mode, $side);
2812         }
2813
2814       default:
2815         $locationTypeName = '';
2816         if (strpos($name, '-address') != 0) {
2817           $locationTypeName = 'address';
2818         }
2819         elseif (strpos($name, '-phone') != 0) {
2820           $locationTypeName = 'phone';
2821         }
2822         elseif (strpos($name, '-email') != 0) {
2823           $locationTypeName = 'email';
2824         }
2825         elseif (strpos($name, '-im') != 0) {
2826           $locationTypeName = 'im';
2827         }
2828         elseif (strpos($name, '-openid') != 0) {
2829           $locationTypeName = 'openid';
2830         }
2831
2832         if ($locationTypeName) {
2833           //we have a join on an location table - possibly in conjunction with search builder - CRM-14263
2834           $parts = explode('-', $name);
2835           $locationTypes = CRM_Core_DAO_Address::buildOptions('location_type_id', 'validate');
2836           foreach ($locationTypes as $locationTypeID => $locationType) {
2837             if ($parts[0] == str_replace(' ', '_', $locationType)) {
2838               $locationID = $locationTypeID;
2839             }
2840           }
2841           $from = " $side JOIN civicrm_{$locationTypeName} `{$name}` ON ( contact_a.id = `{$name}`.contact_id ) and `{$name}`.location_type_id = $locationID ";
2842         }
2843         else {
2844           $from = CRM_Core_Component::from($name, $mode, $side);
2845         }
2846         $from .= CRM_Contact_BAO_Query_Hook::singleton()->buildSearchfrom($name, $mode, $side);
2847
2848         return $from;
2849     }
2850   }
2851
2852   /**
2853    * WHERE / QILL clause for deleted_contacts
2854    *
2855    * @param array $values
2856    */
2857   public function deletedContacts($values) {
2858     list($_, $_, $value, $grouping, $_) = $values;
2859     if ($value) {
2860       // *prepend* to the relevant grouping as this is quite an important factor
2861       array_unshift($this->_qill[$grouping], ts('Search in Trash'));
2862     }
2863   }
2864
2865   /**
2866    * Where / qill clause for contact_type
2867    *
2868    * @param $values
2869    *
2870    * @throws \CRM_Core_Exception
2871    */
2872   public function contactType(&$values) {
2873     list($name, $op, $value, $grouping, $wildcard) = $values;
2874
2875     $subTypes = [];
2876     $clause = [];
2877
2878     // account for search builder mapping multiple values
2879     if (!is_array($value)) {
2880       $values = self::parseSearchBuilderString($value, 'String');
2881       if (is_array($values)) {
2882         $value = array_flip($values);
2883       }
2884     }
2885
2886     if (is_array($value)) {
2887       foreach ($value as $k => $v) {
2888         // fix for CRM-771
2889         if ($k) {
2890           $subType = NULL;
2891           $contactType = $k;
2892           if (strpos($k, CRM_Core_DAO::VALUE_SEPARATOR)) {
2893             list($contactType, $subType) = explode(CRM_Core_DAO::VALUE_SEPARATOR, $k, 2);
2894           }
2895
2896           if (!empty($subType)) {
2897             $subTypes[$subType] = 1;
2898           }
2899           $clause[$contactType] = "'" . CRM_Utils_Type::escape($contactType, 'String') . "'";
2900         }
2901       }
2902     }
2903     else {
2904       $contactTypeANDSubType = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value, 2);
2905       $contactType = $contactTypeANDSubType[0];
2906       $subType = CRM_Utils_Array::value(1, $contactTypeANDSubType);
2907       if (!empty($subType)) {
2908         $subTypes[$subType] = 1;
2909       }
2910       $clause[$contactType] = "'" . CRM_Utils_Type::escape($contactType, 'String') . "'";
2911     }
2912
2913     // fix for CRM-771
2914     if (!empty($clause)) {
2915       $quill = $clause;
2916       if ($op == 'IN' || $op == 'NOT IN') {
2917         $this->_where[$grouping][] = "contact_a.contact_type $op (" . implode(',', $clause) . ')';
2918       }
2919       else {
2920         $type = array_pop($clause);
2921         $this->_where[$grouping][] = self::buildClause("contact_a.contact_type", $op, $contactType);
2922       }
2923
2924       $this->_qill[$grouping][] = ts('Contact Type') . " $op " . implode(' ' . ts('or') . ' ', $quill);
2925
2926       if (!empty($subTypes)) {
2927         $this->includeContactSubTypes($subTypes, $grouping);
2928       }
2929     }
2930   }
2931
2932   /**
2933    * Where / qill clause for contact_sub_type.
2934    *
2935    * @param array $values
2936    */
2937   public function contactSubType(&$values) {
2938     list($name, $op, $value, $grouping, $wildcard) = $values;
2939     $this->includeContactSubTypes($value, $grouping, $op);
2940   }
2941
2942   /**
2943    * @param $value
2944    * @param $grouping
2945    * @param string $op
2946    *
2947    * @throws \CRM_Core_Exception
2948    */
2949   public function includeContactSubTypes($value, $grouping, $op = 'LIKE') {
2950
2951     if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
2952       $op = key($value);
2953       $value = $value[$op];
2954     }
2955
2956     $clause = [];
2957     $alias = "contact_a.contact_sub_type";
2958     $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
2959
2960     $op = str_replace('IN', 'LIKE', $op);
2961     $op = str_replace('=', 'LIKE', $op);
2962     $op = str_replace('!', 'NOT ', $op);
2963
2964     if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) {
2965       $this->_where[$grouping][] = self::buildClause($alias, $op, $value, 'String');
2966     }
2967     elseif (is_array($value)) {
2968       foreach ($value as $k => $v) {
2969         $clause[$k] = "($alias $op '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($v, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')";
2970       }
2971     }
2972     else {
2973       $clause[$value] = "($alias $op '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($value, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')";
2974     }
2975
2976     if (!empty($clause)) {
2977       $this->_where[$grouping][] = "( " . implode(' OR ', $clause) . " )";
2978     }
2979     $this->_qill[$grouping][] = ts('Contact Subtype %1 ', [1 => $qillOperators[$op]]) . implode(' ' . ts('or') . ' ', array_keys($clause));
2980   }
2981
2982   /**
2983    * Where / qill clause for groups.
2984    *
2985    * @param $values
2986    *
2987    * @throws \CRM_Core_Exception
2988    * @throws \Exception
2989    */
2990   public function group($values) {
2991     list($name, $op, $value, $grouping, $wildcard) = $values;
2992
2993     // If the $value is in OK (operator as key) array format we need to extract the key as operator and value first
2994     if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
2995       $op = key($value);
2996       $value = $value[$op];
2997     }
2998     // Translate EMPTY to NULL as EMPTY is cannot be used in it's intended meaning here
2999     // so has to be 'squashed into' NULL. (ie. group membership cannot be '').
3000     // even one group might equate to multiple when looking at children so IN is simpler.
3001     // @todo - also look at != casting but there are rows below to review.
3002     $opReplacements = [
3003       'EMPTY' => 'NULL',
3004       'NOT EMPTY' => 'NOT NULL',
3005       '=' => 'IN',
3006     ];
3007     if (isset($opReplacements[$op])) {
3008       $op = $opReplacements[$op];
3009     }
3010
3011     if (strpos($op, 'NULL')) {
3012       $value = NULL;
3013     }
3014
3015     if (is_array($value) && count($value) > 1) {
3016       if (strpos($op, 'IN') === FALSE && strpos($op, 'NULL') === FALSE) {
3017         CRM_Core_Error::fatal(ts("%1 is not a valid operator", [1 => $op]));
3018       }
3019       $this->_useDistinct = TRUE;
3020     }
3021
3022     if (isset($value)) {
3023       $value = CRM_Utils_Array::value($op, $value, $value);
3024     }
3025
3026     if ($name == 'group_type') {
3027       $value = array_keys($this->getGroupsFromTypeCriteria($value));
3028     }
3029
3030     $regularGroupIDs = $smartGroupIDs = [];
3031     foreach ((array) $value as $id) {
3032       if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Group', $id, 'saved_search_id')) {
3033         $smartGroupIDs[] = (int) $id;
3034       }
3035       else {
3036         $regularGroupIDs[] = (int) trim($id);
3037       }
3038     }
3039     $hasNonSmartGroups = count($regularGroupIDs);
3040
3041     $isNotOp = ($op == 'NOT IN' || $op == '!=');
3042
3043     $statusJoinClause = $this->getGroupStatusClause($grouping);
3044     // If we are searching for 'Removed' contacts then despite it being a smart group we only care about the group_contact table.
3045     $isGroupStatusSearch = (!empty($this->getSelectedGroupStatuses($grouping)) && $this->getSelectedGroupStatuses($grouping) !== ["'Added'"]);
3046     $groupClause = [];
3047     if ($hasNonSmartGroups || empty($value) || $isGroupStatusSearch) {
3048       // include child groups IDs if any
3049       $childGroupIds = (array) CRM_Contact_BAO_Group::getChildGroupIds($regularGroupIDs);
3050       foreach ($childGroupIds as $key => $id) {
3051         if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Group', $id, 'saved_search_id')) {
3052           $smartGroupIDs[] = $id;
3053           unset($childGroupIds[$key]);
3054         }
3055       }
3056       if (count($childGroupIds)) {
3057         $regularGroupIDs = array_merge($regularGroupIDs, $childGroupIds);
3058       }
3059
3060       if (empty($regularGroupIDs)) {
3061         if ($isGroupStatusSearch) {
3062           $regularGroupIDs = $smartGroupIDs;
3063         }
3064         // If it is still empty we want a filter that blocks all results.
3065         if (empty($regularGroupIDs)) {
3066           $regularGroupIDs = [0];
3067         }
3068       }
3069
3070       $gcTable = "`civicrm_group_contact-" . uniqid() . "`";
3071       $joinClause = ["contact_a.id = {$gcTable}.contact_id"];
3072
3073       // @todo consider just casting != to NOT IN & handling both together.
3074       if ($op == '!=') {
3075         $groupIds = '';
3076         if (!empty($regularGroupIDs)) {
3077           $groupIds = CRM_Utils_Type::validate(
3078             implode(',', (array) $regularGroupIDs),
3079             'CommaSeparatedIntegers'
3080           );
3081         }
3082         $clause = "{$gcTable}.contact_id NOT IN (SELECT contact_id FROM civicrm_group_contact cgc WHERE cgc.group_id = $groupIds )";
3083       }
3084       else {
3085         $clause = self::buildClause("{$gcTable}.group_id", $op, $regularGroupIDs);
3086       }
3087       $groupClause[] = "( {$clause} )";
3088
3089       if ($statusJoinClause) {
3090         $joinClause[] = "{$gcTable}.$statusJoinClause";
3091       }
3092       $this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")";
3093     }
3094
3095     //CRM-19589: contact(s) removed from a Smart Group, resides in civicrm_group_contact table
3096     // If we are only searching for Removed or Pending contacts we don't need to resolve the smart group
3097     // as that info is in the group_contact table.
3098     if ((count($smartGroupIDs) || empty($value)) && !$isGroupStatusSearch) {
3099       $this->_groupUniqueKey = uniqid();
3100       $this->_groupKeys[] = $this->_groupUniqueKey;
3101       $gccTableAlias = "civicrm_group_contact_cache_{$this->_groupUniqueKey}";
3102       $groupContactCacheClause = $this->addGroupContactCache($smartGroupIDs, $gccTableAlias, "contact_a", $op);
3103       if (!empty($groupContactCacheClause)) {
3104         if ($isNotOp) {
3105           $groupIds = implode(',', (array) $smartGroupIDs);
3106           $gcTable = "civicrm_group_contact_{$this->_groupUniqueKey}";
3107           $joinClause = ["contact_a.id = {$gcTable}.contact_id"];
3108           $this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")";
3109           if (strpos($op, 'IN') !== FALSE) {
3110             $groupClause[] = "{$gcTable}.group_id $op ( $groupIds ) AND {$gccTableAlias}.group_id IS NULL";
3111           }
3112           else {
3113             $groupClause[] = "{$gcTable}.group_id $op $groupIds AND {$gccTableAlias}.group_id IS NULL";
3114           }
3115         }
3116         $groupClause[] = " ( {$groupContactCacheClause} ) ";
3117       }
3118     }
3119
3120     $and = ($op == 'IS NULL') ? ' AND ' : ' OR ';
3121     if (!empty($groupClause)) {
3122       $this->_where[$grouping][] = ' ( ' . implode($and, $groupClause) . ' ) ';
3123     }
3124
3125     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contact_DAO_Group', 'id', $value, $op);
3126     $this->_qill[$grouping][] = ts("Group(s) %1 %2", [1 => $qillop, 2 => $qillVal]);
3127     if (strpos($op, 'NULL') === FALSE) {
3128       $this->_qill[$grouping][] = ts("Group Status %1", [1 => implode(' ' . ts('or') . ' ', $this->getSelectedGroupStatuses($grouping))]);
3129     }
3130   }
3131
3132   /**
3133    * @return array
3134    */
3135   public function getGroupCacheTableKeys() {
3136     return $this->_groupKeys;
3137   }
3138
3139   /**
3140    * Function translates selection of group type into a list of groups.
3141    * @param $value
3142    *
3143    * @return array
3144    */
3145   public function getGroupsFromTypeCriteria($value) {
3146     $groupIds = [];
3147     foreach ((array) $value as $groupTypeValue) {
3148       $groupList = CRM_Core_PseudoConstant::group($groupTypeValue);
3149       $groupIds = ($groupIds + $groupList);
3150     }
3151     return $groupIds;
3152   }
3153
3154   /**
3155    * Prime smart group cache for smart groups in the search, and join
3156    * civicrm_group_contact_cache table into the query.
3157    *
3158    * @param array $groups IDs of groups specified in search criteria.
3159    * @param string $tableAlias Alias to use for civicrm_group_contact_cache table.
3160    * @param string $joinTable Table on which to join civicrm_group_contact_cache
3161    * @param string $op SQL comparison operator (NULL, IN, !=, IS NULL, etc.)
3162    * @param string $joinColumn Column in $joinTable on which to join civicrm_group_contact_cache.contact_id
3163    *
3164    * @return string WHERE clause component for smart group criteria.
3165    * @throws \CRM_Core_Exception
3166    */
3167   public function addGroupContactCache($groups, $tableAlias, $joinTable = "contact_a", $op, $joinColumn = 'id') {
3168     $isNullOp = (strpos($op, 'NULL') !== FALSE);
3169     $groupsIds = $groups;
3170
3171     $operator = ['=' => 'IN', '!=' => 'NOT IN'];
3172     if (!empty($operator[$op]) && is_array($groups)) {
3173       $op = $operator[$op];
3174     }
3175     if (!$isNullOp && !$groups) {
3176       return NULL;
3177     }
3178     elseif (strpos($op, 'IN') !== FALSE) {
3179       $groups = [$op => $groups];
3180     }
3181     elseif (is_array($groups) && count($groups)) {
3182       $groups = ['IN' => $groups];
3183     }
3184
3185     // Find all the groups that are part of a saved search.
3186     $smartGroupClause = self::buildClause("id", $op, $groups, 'Int');
3187     $sql = "
3188 SELECT id, cache_date, saved_search_id, children
3189 FROM   civicrm_group
3190 WHERE  $smartGroupClause
3191   AND  ( saved_search_id != 0
3192    OR    saved_search_id IS NOT NULL
3193    OR    children IS NOT NULL )
3194 ";
3195
3196     $group = CRM_Core_DAO::executeQuery($sql);
3197
3198     while ($group->fetch()) {
3199       $this->_useDistinct = TRUE;
3200       if (!$this->_smartGroupCache || $group->cache_date == NULL) {
3201         CRM_Contact_BAO_GroupContactCache::load($group);
3202       }
3203     }
3204     if ($group->N == 0 && $op != 'NOT IN') {
3205       return NULL;
3206     }
3207
3208     $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.{$joinColumn} = {$tableAlias}.contact_id ";
3209
3210     if ($op == 'NOT IN') {
3211       return "{$tableAlias}.contact_id NOT IN (SELECT contact_id FROM civicrm_group_contact_cache cgcc WHERE cgcc.group_id IN ( " . implode(',', (array) $groupsIds) . " ) )";
3212     }
3213     return self::buildClause("{$tableAlias}.group_id", $op, $groups, 'Int');
3214   }
3215
3216   /**
3217    * Where / qill clause for cms users
3218    *
3219    * @param $values
3220    */
3221   public function ufUser(&$values) {
3222     list($name, $op, $value, $grouping, $wildcard) = $values;
3223
3224     if ($value == 1) {
3225       $this->_tables['civicrm_uf_match'] = $this->_whereTables['civicrm_uf_match'] = ' INNER JOIN civicrm_uf_match ON civicrm_uf_match.contact_id = contact_a.id ';
3226
3227       $this->_qill[$grouping][] = ts('CMS User');
3228     }
3229     elseif ($value == 0) {
3230       $this->_tables['civicrm_uf_match'] = $this->_whereTables['civicrm_uf_match'] = ' LEFT JOIN civicrm_uf_match ON civicrm_uf_match.contact_id = contact_a.id ';
3231
3232       $this->_where[$grouping][] = " civicrm_uf_match.contact_id IS NULL";
3233       $this->_qill[$grouping][] = ts('Not a CMS User');
3234     }
3235   }
3236
3237   /**
3238    * All tag search specific.
3239    *
3240    * @param array $values
3241    *
3242    * @throws \CRM_Core_Exception
3243    */
3244   public function tagSearch(&$values) {
3245     list($name, $op, $value, $grouping, $wildcard) = $values;
3246
3247     $op = "LIKE";
3248     $value = "%{$value}%";
3249     $escapedValue = CRM_Utils_Type::escape("%{$value}%", 'String');
3250
3251     $useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
3252     $tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
3253
3254     $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
3255     $tTable = "`civicrm_tag-" . uniqid() . "`";
3256
3257     if ($useAllTagTypes[2]) {
3258       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3259         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id)
3260             LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id  )";
3261
3262       // search tag in cases
3263       $etCaseTable = "`civicrm_entity_case_tag-" . uniqid() . "`";
3264       $tCaseTable = "`civicrm_case_tag-" . uniqid() . "`";
3265       $this->_tables[$etCaseTable] = $this->_whereTables[$etCaseTable]
3266         = " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
3267             LEFT JOIN civicrm_case
3268             ON (civicrm_case_contact.case_id = civicrm_case.id
3269                 AND civicrm_case.is_deleted = 0 )
3270             LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id )
3271             LEFT JOIN civicrm_tag {$tCaseTable} ON ( {$etCaseTable}.tag_id = {$tCaseTable}.id  )";
3272       // search tag in activities
3273       $etActTable = "`civicrm_entity_act_tag-" . uniqid() . "`";
3274       $tActTable = "`civicrm_act_tag-" . uniqid() . "`";
3275       $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
3276       $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
3277
3278       $this->_tables[$etActTable] = $this->_whereTables[$etActTable]
3279         = " LEFT JOIN civicrm_activity_contact
3280             ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
3281             LEFT JOIN civicrm_activity
3282             ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
3283             AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )
3284             LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id )
3285             LEFT JOIN civicrm_tag {$tActTable} ON ( {$etActTable}.tag_id = {$tActTable}.id  )";
3286
3287       $this->_where[$grouping][] = "({$tTable}.name $op '" . $escapedValue . "' OR {$tCaseTable}.name $op '" . $escapedValue . "' OR {$tActTable}.name $op '" . $escapedValue . "')";
3288       $this->_qill[$grouping][] = ts('Tag %1 %2', [1 => $tagTypesText[2], 2 => $op]) . ' ' . $value;
3289     }
3290     else {
3291       $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
3292       $tTable = "`civicrm_tag-" . uniqid() . "`";
3293       $this->_tables[$etTable] = $this->_whereTables[$etTable] = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND
3294       {$etTable}.entity_table = 'civicrm_contact' )
3295                 LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id  ) ";
3296
3297       $this->_where[$grouping][] = self::buildClause("{$tTable}.name", $op, $value, 'String');
3298       $this->_qill[$grouping][] = ts('Tagged %1', [1 => $op]) . ' ' . $value;
3299     }
3300   }
3301
3302   /**
3303    * Where / qill clause for tag.
3304    *
3305    * @param array $values
3306    *
3307    * @throws \CRM_Core_Exception
3308    */
3309   public function tag(&$values) {
3310     list($name, $op, $value, $grouping, $wildcard) = $values;
3311
3312     list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_EntityTag', "tag_id", $value, $op, ['onlyActive' => FALSE]);
3313     // API/Search Builder format array(operator => array(values))
3314     if (is_array($value)) {
3315       if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
3316         $op = key($value);
3317         $value = $value[$op];
3318       }
3319       if (count($value) > 1) {
3320         $this->_useDistinct = TRUE;
3321       }
3322     }
3323
3324     // implode array, then remove all spaces
3325     $value = str_replace(' ', '', implode(',', (array) $value));
3326     if (!empty($value)) {
3327       $value = CRM_Utils_Type::validate(
3328         $value,
3329         'CommaSeparatedIntegers'
3330       );
3331     }
3332
3333     $useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
3334     $tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
3335
3336     $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
3337
3338     if ($useAllTagTypes[2]) {
3339       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3340         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND {$etTable}.entity_table = 'civicrm_contact') ";
3341
3342       // search tag in cases
3343       $etCaseTable = "`civicrm_entity_case_tag-" . uniqid() . "`";
3344       $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
3345       $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
3346
3347       $this->_tables[$etCaseTable] = $this->_whereTables[$etCaseTable]
3348         = " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
3349             LEFT JOIN civicrm_case
3350             ON (civicrm_case_contact.case_id = civicrm_case.id
3351                 AND civicrm_case.is_deleted = 0 )
3352             LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id ) ";
3353       // search tag in activities
3354       $etActTable = "`civicrm_entity_act_tag-" . uniqid() . "`";
3355       $this->_tables[$etActTable] = $this->_whereTables[$etActTable]
3356         = " LEFT JOIN civicrm_activity_contact
3357             ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
3358             LEFT JOIN civicrm_activity
3359             ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
3360             AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )
3361             LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id ) ";
3362
3363       // CRM-10338
3364       if (in_array($op, ['IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'])) {
3365         $this->_where[$grouping][] = "({$etTable}.tag_id $op OR {$etCaseTable}.tag_id $op OR {$etActTable}.tag_id $op)";
3366       }
3367       else {
3368         $this->_where[$grouping][] = "({$etTable}.tag_id $op (" . $value . ") OR {$etCaseTable}.tag_id $op (" . $value . ") OR {$etActTable}.tag_id $op (" . $value . "))";
3369       }
3370     }
3371     else {
3372       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3373         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND {$etTable}.entity_table = 'civicrm_contact') ";
3374
3375       // CRM-10338
3376       if (in_array($op, ['IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'])) {
3377         // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3378         $op = str_replace('EMPTY', 'NULL', $op);
3379         $this->_where[$grouping][] = "{$etTable}.tag_id $op";
3380       }
3381       // CRM-16941: for tag tried with != operator we don't show contact who don't have given $value AND also in other tag
3382       elseif ($op == '!=') {
3383         $this->_where[$grouping][] = "{$etTable}.entity_id NOT IN (SELECT entity_id FROM civicrm_entity_tag cet WHERE cet.entity_table = 'civicrm_contact' AND " . self::buildClause("cet.tag_id", '=', $value, 'Int') . ")";
3384       }
3385       elseif ($op == '=' || strstr($op, 'IN')) {
3386         $op = ($op == '=') ? 'IN' : $op;
3387         $this->_where[$grouping][] = "{$etTable}.tag_id $op ( $value )";
3388       }
3389     }
3390     $this->_qill[$grouping][] = ts('Tagged %1 %2', [1 => $qillop, 2 => $qillVal]);
3391   }
3392
3393   /**
3394    * Where/qill clause for notes
3395    *
3396    * @param array $values
3397    *
3398    * @throws \CRM_Core_Exception
3399    */
3400   public function notes(&$values) {
3401     list($name, $op, $value, $grouping, $wildcard) = $values;
3402
3403     $noteOptionValues = $this->getWhereValues('note_option', $grouping);
3404     $noteOption = CRM_Utils_Array::value('2', $noteOptionValues, '6');
3405     $noteOption = ($name == 'note_body') ? 2 : (($name == 'note_subject') ? 3 : $noteOption);
3406
3407     $this->_useDistinct = TRUE;
3408
3409     $this->_tables['civicrm_note'] = $this->_whereTables['civicrm_note']
3410       = " LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id ) ";
3411
3412     $n = trim($value);
3413     $value = CRM_Core_DAO::escapeString($n);
3414     if ($wildcard) {
3415       if (strpos($value, '%') === FALSE) {
3416         $value = "%$value%";
3417       }
3418       $op = 'LIKE';
3419     }
3420     elseif ($op == 'IS NULL' || $op == 'IS NOT NULL') {
3421       $value = NULL;
3422     }
3423
3424     $label = NULL;
3425     $clauses = [];
3426     if ($noteOption % 2 == 0) {
3427       $clauses[] = self::buildClause('civicrm_note.note', $op, $value, 'String');
3428       $label = ts('Note: Body Only');
3429     }
3430     if ($noteOption % 3 == 0) {
3431       $clauses[] = self::buildClause('civicrm_note.subject', $op, $value, 'String');
3432       $label = $label ? ts('Note: Body and Subject') : ts('Note: Subject Only');
3433     }
3434     $this->_where[$grouping][] = "( " . implode(' OR ', $clauses) . " )";
3435     list($qillOp, $qillVal) = self::buildQillForFieldValue(NULL, $name, $n, $op);
3436     $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $label, 2 => $qillOp, 3 => $qillVal]);
3437   }
3438
3439   /**
3440    * @param string $name
3441    * @param $op
3442    * @param $grouping
3443    *
3444    * @return bool
3445    */
3446   public function nameNullOrEmptyOp($name, $op, $grouping) {
3447     switch ($op) {
3448       case 'IS NULL':
3449       case 'IS NOT NULL':
3450         $this->_where[$grouping][] = "contact_a.$name $op";
3451         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3452         return TRUE;
3453
3454       case 'IS EMPTY':
3455         $this->_where[$grouping][] = "(contact_a.$name IS NULL OR contact_a.$name = '')";
3456         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3457         return TRUE;
3458
3459       case 'IS NOT EMPTY':
3460         $this->_where[$grouping][] = "(contact_a.$name IS NOT NULL AND contact_a.$name <> '')";
3461         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3462         return TRUE;
3463
3464       default:
3465         return FALSE;
3466     }
3467   }
3468
3469   /**
3470    * Where / qill clause for sort_name
3471    *
3472    * @param array $values
3473    */
3474   public function sortName(&$values) {
3475     list($fieldName, $op, $value, $grouping, $wildcard) = $values;
3476
3477     // handle IS NULL / IS NOT NULL / IS EMPTY / IS NOT EMPTY
3478     if ($this->nameNullOrEmptyOp($fieldName, $op, $grouping)) {
3479       return;
3480     }
3481
3482     $input = $value = is_array($value) ? trim($value['LIKE']) : trim($value);
3483
3484     if (!strlen($value)) {
3485       return;
3486     }
3487
3488     $config = CRM_Core_Config::singleton();
3489
3490     $sub = [];
3491
3492     //By default, $sub elements should be joined together with OR statements (don't change this variable).
3493     $subGlue = ' OR ';
3494
3495     $firstChar = substr($value, 0, 1);
3496     $lastChar = substr($value, -1, 1);
3497     $quotes = ["'", '"'];
3498     // If string is quoted, strip quotes and otherwise don't alter it
3499     if ((strlen($value) > 2) && in_array($firstChar, $quotes) && in_array($lastChar, $quotes)) {
3500       $value = trim($value, implode('', $quotes));
3501     }
3502     // Replace spaces with wildcards for a LIKE operation
3503     // UNLESS string contains a comma (this exception is a tiny bit questionable)
3504     elseif ($op == 'LIKE' && strpos($value, ',') === FALSE) {
3505       $value = str_replace(' ', '%', $value);
3506     }
3507     $value = CRM_Core_DAO::escapeString(trim($value));
3508     if (strlen($value)) {
3509       $fieldsub = [];
3510       $value = "'" . self::getWildCardedValue($wildcard, $op, $value) . "'";
3511       if ($fieldName == 'sort_name') {
3512         $wc = "contact_a.sort_name";
3513       }
3514       else {
3515         $wc = "contact_a.display_name";
3516       }
3517       $fieldsub[] = " ( $wc $op $value )";
3518       if ($config->includeNickNameInName) {
3519         $wc = "contact_a.nick_name";
3520         $fieldsub[] = " ( $wc $op $value )";
3521       }
3522       if ($config->includeEmailInName) {
3523         $fieldsub[] = " ( civicrm_email.email $op $value ) ";
3524       }
3525       $sub[] = ' ( ' . implode(' OR ', $fieldsub) . ' ) ';
3526     }
3527
3528     $sub = ' ( ' . implode($subGlue, $sub) . ' ) ';
3529
3530     $this->_where[$grouping][] = $sub;
3531     if ($config->includeEmailInName) {
3532       $this->_tables['civicrm_email'] = $this->_whereTables['civicrm_email'] = 1;
3533       $this->_qill[$grouping][] = ts('Name or Email') . " $op - '$input'";
3534     }
3535     else {
3536       $this->_qill[$grouping][] = ts('Name') . " $op - '$input'";
3537     }
3538   }
3539
3540   /**
3541    * Where/qill clause for greeting fields.
3542    *
3543    * @param array $values
3544    *
3545    * @throws \CRM_Core_Exception
3546    */
3547   public function greetings(&$values) {
3548     list($name, $op, $value, $grouping, $wildcard) = $values;
3549     $name .= '_display';
3550
3551     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
3552     $this->_qill[$grouping][] = ts('Greeting %1 %2', [1 => $qillop, 2 => $qillVal]);
3553     $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value, 'String');
3554   }
3555
3556   /**
3557    * Where / qill clause for email
3558    *
3559    * @param array $values
3560    * @param string $isForcePrimaryOnly
3561    *
3562    * @throws \CRM_Core_Exception
3563    */
3564   protected function email(&$values, $isForcePrimaryOnly) {
3565     list($name, $op, $value, $grouping, $wildcard) = $values;
3566     $this->_tables['civicrm_email'] = $this->_whereTables['civicrm_email'] = 1;
3567
3568     // CRM-18147: for Contact's GET API, email fieldname got appended with its entity as in {$apiEntiy}_{$name}
3569     // so following code is use build whereClause for contact's primart email id
3570     if (!empty($isForcePrimaryOnly)) {
3571       $this->_where[$grouping][] = self::buildClause('civicrm_email.is_primary', '=', 1, 'Integer');
3572     }
3573     // @todo - this should come from the $this->_fields array
3574     $dbName = $name === 'email_id' ? 'id' : $name;
3575
3576     if (is_array($value) || $name === 'email_id') {
3577       $this->_qill[$grouping][] = $this->getQillForField($name, $value, $op);
3578       $this->_where[$grouping][] = self::buildClause('civicrm_email.' . $dbName, $op, $value, 'String');
3579       return;
3580     }
3581
3582     // Is this ever hit now? Ideally ensure always an array & handle above.
3583     $n = trim($value);
3584     if ($n) {
3585       if (substr($n, 0, 1) == '"' &&
3586         substr($n, -1, 1) == '"'
3587       ) {
3588         $n = substr($n, 1, -1);
3589         $value = CRM_Core_DAO::escapeString($n);
3590         $op = '=';
3591       }
3592       else {
3593         $value = self::getWildCardedValue($wildcard, $op, $n);
3594       }
3595       $this->_qill[$grouping][] = ts('Email') . " $op '$n'";
3596       $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, $value, 'String');
3597     }
3598     else {
3599       $this->_qill[$grouping][] = ts('Email') . " $op ";
3600       $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, NULL, 'String');
3601     }
3602   }
3603
3604   /**
3605    * Where / qill clause for phone number
3606    *
3607    * @param array $values
3608    *
3609    * @throws \CRM_Core_Exception
3610    */
3611   public function phone_numeric(&$values) {
3612     list($name, $op, $value, $grouping, $wildcard) = $values;
3613     // Strip non-numeric characters; allow wildcards
3614     $number = preg_replace('/[^\d%]/', '', $value);
3615     if ($number) {
3616       if (strpos($number, '%') === FALSE) {
3617         $number = "%$number%";
3618       }
3619
3620       $this->_qill[$grouping][] = ts('Phone number contains') . " $number";
3621       $this->_where[$grouping][] = self::buildClause('civicrm_phone.phone_numeric', 'LIKE', "$number", 'String');
3622       $this->_tables['civicrm_phone'] = $this->_whereTables['civicrm_phone'] = 1;
3623     }
3624   }
3625
3626   /**
3627    * Where / qill clause for phone type/location
3628    *
3629    * @param array $values
3630    *
3631    * @throws \CRM_Core_Exception
3632    */
3633   public function phone_option_group($values) {
3634     list($name, $op, $value, $grouping, $wildcard) = $values;
3635     $option = ($name == 'phone_phone_type_id' ? 'phone_type_id' : 'location_type_id');
3636     $options = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', $option);
3637     $optionName = $options[$value];
3638     $this->_qill[$grouping][] = ts('Phone') . ' ' . ($name == 'phone_phone_type_id' ? ts('type') : ('location')) . " $op $optionName";
3639     $this->_where[$grouping][] = self::buildClause('civicrm_phone.' . substr($name, 6), $op, $value, 'Integer');
3640     $this->_tables['civicrm_phone'] = $this->_whereTables['civicrm_phone'] = 1;
3641   }
3642
3643   /**
3644    * Where / qill clause for street_address.
3645    *
3646    * @param array $values
3647    *
3648    * @throws \CRM_Core_Exception
3649    */
3650   public function street_address(&$values) {
3651     list($name, $op, $value, $grouping) = $values;
3652
3653     if (!$op) {
3654       $op = 'LIKE';
3655     }
3656
3657     $n = trim($value);
3658
3659     if ($n) {
3660       if (strpos($value, '%') === FALSE) {
3661         // only add wild card if not there
3662         $value = "%{$value}%";
3663       }
3664       $op = 'LIKE';
3665       $this->_where[$grouping][] = self::buildClause('civicrm_address.street_address', $op, $value, 'String');
3666       $this->_qill[$grouping][] = ts('Street') . " $op '$n'";
3667     }
3668     else {
3669       $this->_where[$grouping][] = self::buildClause('civicrm_address.street_address', $op, NULL, 'String');
3670       $this->_qill[$grouping][] = ts('Street') . " $op ";
3671     }
3672
3673     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3674   }
3675
3676   /**
3677    * Where / qill clause for street_unit.
3678    *
3679    * @param array $values
3680    *
3681    * @throws \CRM_Core_Exception
3682    */
3683   public function street_number(&$values) {
3684     list($name, $op, $value, $grouping, $wildcard) = $values;
3685
3686     if (!$op) {
3687       $op = '=';
3688     }
3689
3690     $n = trim($value);
3691
3692     if (strtolower($n) == 'odd') {
3693       $this->_where[$grouping][] = " ( civicrm_address.street_number % 2 = 1 )";
3694       $this->_qill[$grouping][] = ts('Street Number is odd');
3695     }
3696     elseif (strtolower($n) == 'even') {
3697       $this->_where[$grouping][] = " ( civicrm_address.street_number % 2 = 0 )";
3698       $this->_qill[$grouping][] = ts('Street Number is even');
3699     }
3700     else {
3701       $value = $n;
3702       $this->_where[$grouping][] = self::buildClause('civicrm_address.street_number', $op, $value, 'String');
3703       $this->_qill[$grouping][] = ts('Street Number') . " $op '$n'";
3704     }
3705
3706     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3707   }
3708
3709   /**
3710    * Where / qill clause for sorting by character.
3711    *
3712    * @param array $values
3713    */
3714   public function sortByCharacter(&$values) {
3715     list($name, $op, $value, $grouping, $wildcard) = $values;
3716
3717     $name = trim($value);
3718     $cond = " contact_a.sort_name LIKE '" . CRM_Core_DAO::escapeWildCardString($name) . "%'";
3719     $this->_where[$grouping][] = $cond;
3720     $this->_qill[$grouping][] = ts('Showing only Contacts starting with: \'%1\'', [1 => $name]);
3721   }
3722
3723   /**
3724    * Where / qill clause for including contact ids.
3725    */
3726   public function includeContactIDs() {
3727     if (!$this->_includeContactIds || empty($this->_params)) {
3728       return;
3729     }
3730
3731     $contactIds = [];
3732     foreach ($this->_params as $id => $values) {
3733       if (substr($values[0], 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) {
3734         $contactIds[] = substr($values[0], CRM_Core_Form::CB_PREFIX_LEN);
3735       }
3736     }
3737     CRM_Utils_Type::validateAll($contactIds, 'Positive');
3738     if (!empty($contactIds)) {
3739       $this->_where[0][] = " ( contact_a.id IN (" . implode(',', $contactIds) . " ) ) ";
3740     }
3741   }
3742
3743   /**
3744    * Where / qill clause for postal code.
3745    *
3746    * @param array $values
3747    *
3748    * @throws \CRM_Core_Exception
3749    */
3750   public function postalCode(&$values) {
3751     // skip if the fields dont have anything to do with postal_code
3752     if (empty($this->_fields['postal_code'])) {
3753       return;
3754     }
3755
3756     list($name, $op, $value, $grouping, $wildcard) = $values;
3757
3758     // Handle numeric postal code range searches properly by casting the column as numeric
3759     if (is_numeric($value)) {
3760       $field = "IF (civicrm_address.postal_code REGEXP '^[0-9]{1,10}$', CAST(civicrm_address.postal_code AS UNSIGNED), 0)";
3761       $val = CRM_Utils_Type::escape($value, 'Integer');
3762     }
3763     else {
3764       $field = 'civicrm_address.postal_code';
3765       // Per CRM-17060 we might be looking at an 'IN' syntax so don't case arrays to string.
3766       if (!is_array($value)) {
3767         $val = CRM_Utils_Type::escape($value, 'String');
3768       }
3769       else {
3770         // Do we need to escape values here? I would expect buildClause does.
3771         $val = $value;
3772       }
3773     }
3774
3775     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3776
3777     if ($name == 'postal_code') {
3778       $this->_where[$grouping][] = self::buildClause($field, $op, $val, 'String');
3779       $this->_qill[$grouping][] = ts('Postal code') . " {$op} {$value}";
3780     }
3781     elseif ($name == 'postal_code_low') {
3782       $this->_where[$grouping][] = " ( $field >= '$val' ) ";
3783       $this->_qill[$grouping][] = ts('Postal code greater than or equal to \'%1\'', [1 => $value]);
3784     }
3785     elseif ($name == 'postal_code_high') {
3786       $this->_where[$grouping][] = " ( $field <= '$val' ) ";
3787       $this->_qill[$grouping][] = ts('Postal code less than or equal to \'%1\'', [1 => $value]);
3788     }
3789   }
3790
3791   /**
3792    * Where / qill clause for location type.
3793    *
3794    * @param array $values
3795    * @param null $status
3796    *
3797    * @return string
3798    */
3799   public function locationType(&$values, $status = NULL) {
3800     list($name, $op, $value, $grouping, $wildcard) = $values;
3801
3802     if (is_array($value)) {
3803       $this->_where[$grouping][] = 'civicrm_address.location_type_id IN (' . implode(',', $value) . ')';
3804       $this->_tables['civicrm_address'] = 1;
3805       $this->_whereTables['civicrm_address'] = 1;
3806
3807       $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
3808       $names = [];
3809       foreach ($value as $id) {
3810         $names[] = $locationType[$id];
3811       }
3812
3813       $this->_primaryLocation = FALSE;
3814
3815       if (!$status) {
3816         $this->_qill[$grouping][] = ts('Location Type') . ' - ' . implode(' ' . ts('or') . ' ', $names);
3817       }
3818       else {
3819         return implode(' ' . ts('or') . ' ', $names);
3820       }
3821     }
3822   }
3823
3824   /**
3825    * @param $values
3826    * @param bool $fromStateProvince
3827    *
3828    * @return array|NULL
3829    * @throws \CRM_Core_Exception
3830    */
3831   public function country(&$values, $fromStateProvince = TRUE) {
3832     list($name, $op, $value, $grouping, $wildcard) = $values;
3833
3834     if (!$fromStateProvince) {
3835       $stateValues = $this->getWhereValues('state_province', $grouping);
3836       if (!empty($stateValues)) {
3837         // return back to caller if there are state province values
3838         // since that handles this case
3839         return NULL;
3840       }
3841     }
3842
3843     $countryClause = $countryQill = NULL;
3844     if (in_array($op, ['IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY']) || ($values && !empty($value))) {
3845       $this->_tables['civicrm_address'] = 1;
3846       $this->_whereTables['civicrm_address'] = 1;
3847
3848       $countryClause = self::buildClause('civicrm_address.country_id', $op, $value, 'Positive');
3849       list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, 'country_id', $value, $op);
3850       $countryQill = ts("%1 %2 %3", [1 => 'Country', 2 => $qillop, 3 => $qillVal]);
3851
3852       if (!$fromStateProvince) {
3853         $this->_where[$grouping][] = $countryClause;
3854         $this->_qill[$grouping][] = $countryQill;
3855       }
3856     }
3857
3858     if ($fromStateProvince) {
3859       if (!empty($countryClause)) {
3860         return [
3861           $countryClause,
3862           " ...AND... " . $countryQill,
3863         ];
3864       }
3865       else {
3866         return [NULL, NULL];
3867       }
3868     }
3869   }
3870
3871   /**
3872    * Where / qill clause for county (if present).
3873    *
3874    * @param array $values
3875    * @param null $status
3876    *
3877    * @return string
3878    */
3879   public function county(&$values, $status = NULL) {
3880     list($name, $op, $value, $grouping, $wildcard) = $values;
3881
3882     if (!is_array($value)) {
3883       // force the county to be an array
3884       $value = [$value];
3885     }
3886
3887     // check if the values are ids OR names of the counties
3888     $inputFormat = 'id';
3889     foreach ($value as $v) {
3890       if (!is_numeric($v)) {
3891         $inputFormat = 'name';
3892         break;
3893       }
3894     }
3895     $names = [];
3896     if ($op == '=') {
3897       $op = 'IN';
3898     }
3899     elseif ($op == '!=') {
3900       $op = 'NOT IN';
3901     }
3902     else {
3903       // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3904       $op = str_replace('EMPTY', 'NULL', $op);
3905     }
3906
3907     if (in_array($op, ['IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'])) {
3908       $clause = "civicrm_address.county_id $op";
3909     }
3910     elseif ($inputFormat == 'id') {
3911       $clause = 'civicrm_address.county_id IN (' . implode(',', $value) . ')';
3912
3913       $county = CRM_Core_PseudoConstant::county();
3914       foreach ($value as $id) {
3915         $names[] = CRM_Utils_Array::value($id, $county);
3916       }
3917     }
3918     else {
3919       $inputClause = [];
3920       $county = CRM_Core_PseudoConstant::county();
3921       foreach ($value as $name) {
3922         $name = trim($name);
3923         $inputClause[] = CRM_Utils_Array::key($name, $county);
3924       }
3925       $clause = 'civicrm_address.county_id IN (' . implode(',', $inputClause) . ')';
3926       $names = $value;
3927     }
3928     $this->_tables['civicrm_address'] = 1;
3929     $this->_whereTables['civicrm_address'] = 1;
3930
3931     $this->_where[$grouping][] = $clause;
3932     if (!$status) {
3933       $this->_qill[$grouping][] = ts('County') . ' - ' . implode(' ' . ts('or') . ' ', $names);
3934     }
3935     else {
3936       return implode(' ' . ts('or') . ' ', $names);
3937     }
3938   }
3939
3940   /**
3941    * Where / qill clause for state/province AND country (if present).
3942    *
3943    * @param array $values
3944    * @param null $status
3945    *
3946    * @return string
3947    * @throws \CRM_Core_Exception
3948    */
3949   public function stateProvince(&$values, $status = NULL) {
3950     list($name, $op, $value, $grouping, $wildcard) = $values;
3951
3952     $stateClause = self::buildClause('civicrm_address.state_province_id', $op, $value, 'Positive');
3953     $this->_tables['civicrm_address'] = 1;
3954     $this->_whereTables['civicrm_address'] = 1;
3955
3956     $countryValues = $this->getWhereValues('country', $grouping);
3957     list($countryClause, $countryQill) = $this->country($countryValues, TRUE);
3958     if ($countryClause) {
3959       $clause = "( $stateClause AND $countryClause )";
3960     }
3961     else {
3962       $clause = $stateClause;
3963     }
3964
3965     $this->_where[$grouping][] = $clause;
3966     list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_Address', "state_province_id", $value, $op);
3967     if (!$status) {
3968       $this->_qill[$grouping][] = ts("State/Province %1 %2 %3", [1 => $qillop, 2 => $qillVal, 3 => $countryQill]);
3969     }
3970     else {
3971       return implode(' ' . ts('or') . ' ', $qillVal) . $countryQill;
3972     }
3973   }
3974
3975   /**
3976    * Where / qill clause for change log.
3977    *
3978    * @param array $values
3979    */
3980   public function changeLog(&$values) {
3981     list($name, $op, $value, $grouping, $wildcard) = $values;
3982
3983     $targetName = $this->getWhereValues('changed_by', $grouping);
3984     if (!$targetName) {
3985       return;
3986     }
3987
3988     $name = trim($targetName[2]);
3989     $name = CRM_Core_DAO::escapeString($name);
3990     $name = $targetName[4] ? "%$name%" : $name;
3991     $this->_where[$grouping][] = "contact_b_log.sort_name LIKE '%$name%'";
3992     $this->_tables['civicrm_log'] = $this->_whereTables['civicrm_log'] = 1;
3993     $fieldTitle = ts('Altered By');
3994
3995     list($qillop, $qillVal) = self::buildQillForFieldValue(NULL, 'changed_by', $name, 'LIKE');
3996     $this->_qill[$grouping][] = ts("%1 %2 '%3'", [
3997       1 => $fieldTitle,
3998       2 => $qillop,
3999       3 => $qillVal,
4000     ]);
4001   }
4002
4003   /**
4004    * @param $values
4005    *
4006    * @throws \CRM_Core_Exception
4007    */
4008   public function modifiedDates($values) {
4009     $this->_useDistinct = TRUE;
4010
4011     // CRM-11281, default to added date if not set
4012     $fieldTitle = ts('Added Date');
4013     $fieldName = 'created_date';
4014     foreach (array_keys($this->_params) as $id) {
4015       if ($this->_params[$id][0] == 'log_date') {
4016         if ($this->_params[$id][2] == 2) {
4017           $fieldTitle = ts('Modified Date');
4018           $fieldName = 'modified_date';
4019         }
4020       }
4021     }
4022
4023     $this->dateQueryBuilder($values, 'contact_a', 'log_date', $fieldName, $fieldTitle);
4024
4025     self::$_openedPanes[ts('Change Log')] = TRUE;
4026   }
4027
4028   /**
4029    * @param $values
4030    *
4031    * @throws \CRM_Core_Exception
4032    */
4033   public function demographics(&$values) {
4034     list($name, $op, $value, $grouping, $wildcard) = $values;
4035
4036     if (($name == 'age_low') || ($name == 'age_high')) {
4037       $this->ageRangeQueryBuilder($values,
4038         'contact_a', 'age', 'birth_date', ts('Age')
4039       );
4040     }
4041     elseif (($name == 'birth_date_low') || ($name == 'birth_date_high')) {
4042
4043       $this->dateQueryBuilder($values,
4044         'contact_a', 'birth_date', 'birth_date', ts('Birth Date')
4045       );
4046     }
4047     elseif (($name == 'deceased_date_low') || ($name == 'deceased_date_high')) {
4048
4049       $this->dateQueryBuilder($values,
4050         'contact_a', 'deceased_date', 'deceased_date', ts('Deceased Date')
4051       );
4052     }
4053
4054     self::$_openedPanes[ts('Demographics')] = TRUE;
4055   }
4056
4057   /**
4058    * @param $values
4059    */
4060   public function privacy(&$values) {
4061     list($name, $op, $value, $grouping) = $values;
4062     //fixed for profile search listing CRM-4633
4063     if (strpbrk($value, "[")) {
4064       $value = "'{$value}'";
4065       $op = "!{$op}";
4066       $this->_where[$grouping][] = "contact_a.{$name} $op $value";
4067     }
4068     else {
4069       $this->_where[$grouping][] = "contact_a.{$name} $op $value";
4070     }
4071     $field = CRM_Utils_Array::value($name, $this->_fields);
4072     $op = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
4073     $title = $field ? $field['title'] : $name;
4074     $this->_qill[$grouping][] = "$title $op $value";
4075   }
4076
4077   /**
4078    * @param $values
4079    */
4080   public function privacyOptions($values) {
4081     list($name, $op, $value, $grouping, $wildcard) = $values;
4082
4083     if (empty($value) || !is_array($value)) {
4084       return;
4085     }
4086
4087     // get the operator and toggle values
4088     $opValues = $this->getWhereValues('privacy_operator', $grouping);
4089     $operator = 'OR';
4090     if ($opValues &&
4091       strtolower($opValues[2] == 'AND')
4092     ) {
4093       // @todo this line is logially unreachable
4094       $operator = 'AND';
4095     }
4096
4097     $toggleValues = $this->getWhereValues('privacy_toggle', $grouping);
4098     $compareOP = '!';
4099     if ($toggleValues &&
4100       $toggleValues[2] == 2
4101     ) {
4102       $compareOP = '';
4103     }
4104
4105     $clauses = [];
4106     $qill = [];
4107     foreach ($value as $dontCare => $pOption) {
4108       $clauses[] = " ( contact_a.{$pOption} = 1 ) ";
4109       $field = CRM_Utils_Array::value($pOption, $this->_fields);
4110       $title = $field ? $field['title'] : $pOption;
4111       $qill[] = " $title = 1 ";
4112     }
4113
4114     $this->_where[$grouping][] = $compareOP . '( ' . implode($operator, $clauses) . ' )';
4115     $this->_qill[$grouping][] = $compareOP . '( ' . implode($operator, $qill) . ' )';
4116   }
4117
4118   /**
4119    * @param $values
4120    *
4121    * @throws \CRM_Core_Exception
4122    */
4123   public function preferredCommunication(&$values) {
4124     list($name, $op, $value, $grouping, $wildcard) = $values;
4125
4126     if (!is_array($value)) {
4127       $value = str_replace(['(', ')'], '', explode(",", $value));
4128     }
4129     elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
4130       $op = key($value);
4131       $value = $value[$op];
4132     }
4133     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contact_DAO_Contact', $name, $value, $op);
4134
4135     if (self::caseImportant($op)) {
4136       $value = implode("[[:cntrl:]]|[[:cntrl:]]", (array) $value);
4137       $op = (strstr($op, '!') || strstr($op, 'NOT')) ? 'NOT RLIKE' : 'RLIKE';
4138       $value = "[[:cntrl:]]" . $value . "[[:cntrl:]]";
4139     }
4140
4141     $this->_where[$grouping][] = self::buildClause("contact_a.preferred_communication_method", $op, $value);
4142     $this->_qill[$grouping][] = ts('Preferred Communication Method %1 %2', [1 => $qillop, 2 => $qillVal]);
4143   }
4144
4145   /**
4146    * Where / qill clause for relationship.
4147    *
4148    * @param array $values
4149    */
4150   public function relationship(&$values) {
4151     list($name, $op, $value, $grouping, $wildcard) = $values;
4152     if ($this->_relationshipValuesAdded) {
4153       return;
4154     }
4155     // also get values array for relation_target_name
4156     // for relationship search we always do wildcard
4157     $relationType = $this->getWhereValues('relation_type_id', $grouping);
4158     $description = $this->getWhereValues('relation_description', $grouping);
4159     $targetName = $this->getWhereValues('relation_target_name', $grouping);
4160     $relStatus = $this->getWhereValues('relation_status', $grouping);
4161     $targetGroup = $this->getWhereValues('relation_target_group', $grouping);
4162
4163     $nameClause = $name = NULL;
4164     if ($targetName) {
4165       $name = trim($targetName[2]);
4166       if (substr($name, 0, 1) == '"' &&
4167         substr($name, -1, 1) == '"'
4168       ) {
4169         $name = substr($name, 1, -1);
4170         $name = CRM_Core_DAO::escapeString($name);
4171         $nameClause = "= '$name'";
4172       }
4173       else {
4174         $name = CRM_Core_DAO::escapeString($name);
4175         $nameClause = "LIKE '%{$name}%'";
4176       }
4177     }
4178
4179     $relTypes = $relTypesIds = [];
4180     if (!empty($relationType)) {
4181       $relationType[2] = (array) $relationType[2];
4182       foreach ($relationType[2] as $relType) {
4183         $rel = explode('_', $relType);
4184         self::$_relType = $rel[1];
4185         $params = ['id' => $rel[0]];
4186         $typeValues = [];
4187         $rTypeValue = CRM_Contact_BAO_RelationshipType::retrieve($params, $typeValues);
4188         if (!empty($rTypeValue)) {
4189           if ($rTypeValue->name_a_b == $rTypeValue->name_b_a) {
4190             // if we don't know which end of the relationship we are dealing with we'll create a temp table
4191             self::$_relType = 'reciprocal';
4192           }
4193           $relTypesIds[] = $rel[0];
4194           $relTypes[] = $relType;
4195         }
4196       }
4197     }
4198
4199     // if we are creating a temp table we build our own where for the relationship table
4200     $relationshipTempTable = NULL;
4201     if (self::$_relType == 'reciprocal') {
4202       $where = [];
4203       self::$_relationshipTempTable = $relationshipTempTable = CRM_Core_DAO::createTempTableName('civicrm_rel');
4204       if ($nameClause) {
4205         $where[$grouping][] = " sort_name $nameClause ";
4206       }
4207       $groupJoinTable = "civicrm_relationship";
4208       $groupJoinColumn = "contact_id_alt";
4209     }
4210     else {
4211       $where = &$this->_where;
4212       if ($nameClause) {
4213         $where[$grouping][] = "( contact_b.sort_name $nameClause AND contact_b.id != contact_a.id )";
4214       }
4215       $groupJoinTable = "contact_b";
4216       $groupJoinColumn = "id";
4217     }
4218     $allRelationshipType = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, NULL, TRUE);
4219     if ($nameClause || !$targetGroup) {
4220       if (!empty($relationType)) {
4221         $relQill = '';
4222         foreach ($relTypes as $rel) {
4223           if (!empty($relQill)) {
4224             $relQill .= ' OR ';
4225           }
4226           $relQill .= $allRelationshipType[$rel];
4227         }
4228         $this->_qill[$grouping][] = 'Relationship Type(s) ' . $relQill . " $name";
4229       }
4230       else {
4231         $this->_qill[$grouping][] = $name;
4232       }
4233     }
4234
4235     //check to see if the target contact is in specified group
4236     if ($targetGroup) {
4237       //add contacts from static groups
4238       $this->_tables['civicrm_relationship_group_contact'] = $this->_whereTables['civicrm_relationship_group_contact']
4239         = " LEFT JOIN civicrm_group_contact civicrm_relationship_group_contact ON civicrm_relationship_group_contact.contact_id = {$groupJoinTable}.{$groupJoinColumn} AND civicrm_relationship_group_contact.status = 'Added'";
4240       $groupWhere[] = "( civicrm_relationship_group_contact.group_id IN  (" .
4241         implode(",", $targetGroup[2]) . ") ) ";
4242
4243       //add contacts from saved searches
4244       $ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", $groupJoinTable, $op, $groupJoinColumn);
4245
4246       //set the group where clause
4247       if ($ssWhere) {
4248         $groupWhere[] = "( " . $ssWhere . " )";
4249       }
4250       $this->_where[$grouping][] = "( " . implode(" OR ", $groupWhere) . " )";
4251
4252       //Get the names of the target groups for the qill
4253       $groupNames = CRM_Core_PseudoConstant::group();
4254       $qillNames = [];
4255       foreach ($targetGroup[2] as $groupId) {
4256         if (array_key_exists($groupId, $groupNames)) {
4257           $qillNames[] = $groupNames[$groupId];
4258         }
4259       }
4260       if (!empty($relationType)) {
4261         $relQill = '';
4262         foreach ($relTypes as $rel) {
4263           if (!empty($relQill)) {
4264             $relQill .= ' OR ';
4265           }
4266           $relQill .= CRM_Utils_Array::value($rel, $allRelationshipType);
4267         }
4268         $this->_qill[$grouping][] = 'Relationship Type(s) ' . $relQill . " ( " . implode(", ", $qillNames) . " )";
4269       }
4270       else {
4271         $this->_qill[$grouping][] = implode(", ", $qillNames);
4272       }
4273     }
4274
4275     // Description
4276     if (!empty($description[2]) && trim($description[2])) {
4277       $this->_qill[$grouping][] = ts('Relationship description - ' . $description[2]);
4278       $description = CRM_Core_DAO::escapeString(trim($description[2]));
4279       $where[$grouping][] = "civicrm_relationship.description LIKE '%{$description}%'";
4280     }
4281
4282     // Note we do not currently set mySql to handle timezones, so doing this the old-fashioned way
4283     $today = date('Ymd');
4284     //check for active, inactive and all relation status
4285     if ($relStatus[2] == 0) {
4286       $where[$grouping][] = "(
4287 civicrm_relationship.is_active = 1 AND
4288 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
4289 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
4290 )";
4291       $this->_qill[$grouping][] = ts('Relationship - Active and Current');
4292     }
4293     elseif ($relStatus[2] == 1) {
4294       $where[$grouping][] = "(
4295 civicrm_relationship.is_active = 0 OR
4296 civicrm_relationship.end_date < {$today} OR
4297 civicrm_relationship.start_date > {$today}
4298 )";
4299       $this->_qill[$grouping][] = ts('Relationship - Inactive or not Current');
4300     }
4301
4302     $onlyDeleted = 0;
4303     if (in_array(['deleted_contacts', '=', '1', '0', '0'], $this->_params)) {
4304       $onlyDeleted = 1;
4305     }
4306     $where[$grouping][] = "(contact_b.is_deleted = {$onlyDeleted})";
4307
4308     $this->addRelationshipPermissionClauses($grouping, $where);
4309     $this->addRelationshipDateClauses($grouping, $where);
4310     $this->addRelationshipActivePeriodClauses($grouping, $where);
4311     if (!empty($relTypes)) {
4312       $where[$grouping][] = 'civicrm_relationship.relationship_type_id IN (' . implode(',', $relTypesIds) . ')';
4313     }
4314     $this->_tables['civicrm_relationship'] = $this->_whereTables['civicrm_relationship'] = 1;
4315     $this->_useDistinct = TRUE;
4316     $this->_relationshipValuesAdded = TRUE;
4317     // it could be a or b, using an OR creates an unindexed join - better to create a temp table &
4318     // join on that,
4319     if ($relationshipTempTable) {
4320       $whereClause = '';
4321       if (!empty($where[$grouping])) {
4322         $whereClause = ' WHERE ' . implode(' AND ', $where[$grouping]);
4323         $whereClause = str_replace('contact_b', 'c', $whereClause);
4324       }
4325       $sql = "
4326         CREATE TEMPORARY TABLE {$relationshipTempTable}
4327           (
4328             `contact_id` int(10) unsigned NOT NULL DEFAULT '0',
4329             `contact_id_alt` int(10) unsigned NOT NULL DEFAULT '0',
4330             KEY `contact_id` (`contact_id`),
4331             KEY `contact_id_alt` (`contact_id_alt`)
4332           )
4333           (SELECT contact_id_b as contact_id, contact_id_a as contact_id_alt, civicrm_relationship.id
4334             FROM civicrm_relationship
4335             INNER JOIN  civicrm_contact c ON civicrm_relationship.contact_id_a = c.id
4336             $whereClause )
4337           UNION
4338             (SELECT contact_id_a as contact_id, contact_id_b as contact_id_alt, civicrm_relationship.id
4339             FROM civicrm_relationship
4340             INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_b = c.id
4341             $whereClause )
4342       ";
4343       CRM_Core_DAO::executeQuery($sql);
4344     }
4345   }
4346
4347   /**
4348    * Add relationship permission criteria to where clause.
4349    *
4350    * @param string $grouping
4351    * @param array $where Array to add "where" criteria to, in case you are generating a temp table.
4352    *   Not the main query.
4353    */
4354   public function addRelationshipPermissionClauses($grouping, &$where) {
4355     $relPermission = $this->getWhereValues('relation_permission', $grouping);
4356     if ($relPermission) {
4357       if (!is_array($relPermission[2])) {
4358         // this form value was scalar in previous versions of Civi
4359         $relPermission[2] = [$relPermission[2]];
4360       }
4361       $where[$grouping][] = "(civicrm_relationship.is_permission_a_b IN (" . implode(",", $relPermission[2]) . "))";
4362
4363       $allRelationshipPermissions = CRM_Contact_BAO_Relationship::buildOptions('is_permission_a_b');
4364
4365       $relPermNames = array_intersect_key($allRelationshipPermissions, array_flip($relPermission[2]));
4366       $this->_qill[$grouping][] = ts('Permissioned Relationships') . ' - ' . implode(' OR ', $relPermNames);
4367     }
4368   }
4369
4370   /**
4371    * Add start & end date criteria in
4372    * @param string $grouping
4373    * @param array $where
4374    *   = array to add where clauses to, in case you are generating a temp table.
4375    * not the main query.
4376    */
4377   public function addRelationshipDateClauses($grouping, &$where) {
4378     foreach (['start_date', 'end_date'] as $dateField) {
4379       $dateValueLow = $this->getWhereValues('relationship_' . $dateField . '_low', $grouping);
4380       $dateValueHigh = $this->getWhereValues('relationship_' . $dateField . '_high', $grouping);
4381       if (!empty($dateValueLow)) {
4382         $date = date('Ymd', strtotime($dateValueLow[2]));
4383         $where[$grouping][] = "civicrm_relationship.$dateField >= $date";
4384         $this->_qill[$grouping][] = ($dateField == 'end_date' ? ts('Relationship Ended on or After') : ts('Relationship Recorded Start Date On or After')) . " " . CRM_Utils_Date::customFormat($date);
4385       }
4386       if (!empty($dateValueHigh)) {
4387         $date = date('Ymd', strtotime($dateValueHigh[2]));
4388         $where[$grouping][] = "civicrm_relationship.$dateField <= $date";
4389         $this->_qill[$grouping][] = ($dateField == 'end_date' ? ts('Relationship Ended on or Before') : ts('Relationship Recorded Start Date On or Before')) . " " . CRM_Utils_Date::customFormat($date);
4390       }
4391     }
4392   }
4393
4394   /**
4395    * Add start & end active period criteria in
4396    * @param string $grouping
4397    * @param array $where
4398    *   = array to add where clauses to, in case you are generating a temp table.
4399    * not the main query.
4400    */
4401   public function addRelationshipActivePeriodClauses($grouping, &$where) {
4402     $dateValues = [];
4403     $dateField = 'active_period_date';
4404
4405     $dateValueLow = $this->getWhereValues('relation_active_period_date_low', $grouping);
4406     $dateValueHigh = $this->getWhereValues('relation_active_period_date_high', $grouping);
4407     $dateValueLowFormated = $dateValueHighFormated = NULL;
4408     if (!empty($dateValueLow) && !empty($dateValueHigh)) {
4409       $dateValueLowFormated = date('Ymd', strtotime($dateValueLow[2]));
4410       $dateValueHighFormated = date('Ymd', strtotime($dateValueHigh[2]));
4411       $this->_qill[$grouping][] = (ts('Relationship was active between')) . " " . CRM_Utils_Date::customFormat($dateValueLowFormated) . " and " . CRM_Utils_Date::customFormat($dateValueHighFormated);
4412     }
4413     elseif (!empty($dateValueLow)) {
4414       $dateValueLowFormated = date('Ymd', strtotime($dateValueLow[2]));
4415       $this->_qill[$grouping][] = (ts('Relationship was active after')) . " " . CRM_Utils_Date::customFormat($dateValueLowFormated);
4416     }
4417     elseif (!empty($dateValueHigh)) {
4418       $dateValueHighFormated = date('Ymd', strtotime($dateValueHigh[2]));
4419       $this->_qill[$grouping][] = (ts('Relationship was active before')) . " " . CRM_Utils_Date::customFormat($dateValueHighFormated);
4420     }
4421
4422     if ($activePeriodClauses = self::getRelationshipActivePeriodClauses($dateValueLowFormated, $dateValueHighFormated, TRUE)) {
4423       $where[$grouping][] = $activePeriodClauses;
4424     }
4425   }
4426
4427   /**
4428    * Get start & end active period criteria
4429    *
4430    * @param $from
4431    * @param $to
4432    * @param $forceTableName
4433    *
4434    * @return string
4435    */
4436   public static function getRelationshipActivePeriodClauses($from, $to, $forceTableName) {
4437     $tableName = $forceTableName ? 'civicrm_relationship.' : '';
4438     if (!is_null($from) && !is_null($to)) {
4439       return '(((' . $tableName . 'start_date >= ' . $from . ' AND ' . $tableName . 'start_date <= ' . $to . ') OR
4440                 (' . $tableName . 'end_date >= ' . $from . ' AND ' . $tableName . 'end_date <= ' . $to . ') OR
4441                 (' . $tableName . 'start_date <= ' . $from . ' AND ' . $tableName . 'end_date >= ' . $to . ' )) OR
4442                (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date IS NULL) OR
4443                (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date >= ' . $from . ') OR
4444                (' . $tableName . 'end_date IS NULL AND ' . $tableName . 'start_date <= ' . $to . '))';
4445     }
4446     elseif (!is_null($from)) {
4447       return '((' . $tableName . 'start_date >= ' . $from . ') OR
4448                (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date IS NULL) OR
4449                (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date >= ' . $from . '))';
4450     }
4451     elseif (!is_null($to)) {
4452       return '((' . $tableName . 'start_date <= ' . $to . ') OR
4453                (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date IS NULL) OR
4454                (' . $tableName . 'end_date IS NULL AND ' . $tableName . 'start_date <= ' . $to . '))';
4455     }
4456   }
4457
4458   /**
4459    * Default set of return properties.
4460    *
4461    * @param int $mode
4462    *
4463    * @return array
4464    *   derault return properties
4465    */
4466   public static function &defaultReturnProperties($mode = 1) {
4467     if (!isset(self::$_defaultReturnProperties)) {
4468       self::$_defaultReturnProperties = [];
4469     }
4470
4471     if (!isset(self::$_defaultReturnProperties[$mode])) {
4472       // add activity return properties
4473       if ($mode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
4474         self::$_defaultReturnProperties[$mode] = CRM_Activity_BAO_Query::defaultReturnProperties($mode, FALSE);
4475       }
4476       else {
4477         self::$_defaultReturnProperties[$mode] = CRM_Core_Component::defaultReturnProperties($mode, FALSE);
4478       }
4479
4480       if (empty(self::$_defaultReturnProperties[$mode])) {
4481         self::$_defaultReturnProperties[$mode] = [
4482           'home_URL' => 1,
4483           'image_URL' => 1,
4484           'legal_identifier' => 1,
4485           'external_identifier' => 1,
4486           'contact_type' => 1,
4487           'contact_sub_type' => 1,
4488           'sort_name' => 1,
4489           'display_name' => 1,
4490           'preferred_mail_format' => 1,
4491           'nick_name' => 1,
4492           'first_name' => 1,
4493           'middle_name' => 1,
4494           'last_name' => 1,
4495           'prefix_id' => 1,
4496           'suffix_id' => 1,
4497           'formal_title' => 1,
4498           'communication_style_id' => 1,
4499           'birth_date' => 1,
4500           'gender_id' => 1,
4501           'street_address' => 1,
4502           'supplemental_address_1' => 1,
4503           'supplemental_address_2' => 1,
4504           'supplemental_address_3' => 1,
4505           'city' => 1,
4506           'postal_code' => 1,
4507           'postal_code_suffix' => 1,
4508           'state_province' => 1,
4509           'country' => 1,
4510           'world_region' => 1,
4511           'geo_code_1' => 1,
4512           'geo_code_2' => 1,
4513           'email' => 1,
4514           'on_hold' => 1,
4515           'phone' => 1,
4516           'im' => 1,
4517           'household_name' => 1,
4518           'organization_name' => 1,
4519           'deceased_date' => 1,
4520           'is_deceased' => 1,
4521           'job_title' => 1,
4522           'legal_name' => 1,
4523           'sic_code' => 1,
4524           'current_employer' => 1,
4525           // FIXME: should we use defaultHierReturnProperties() for the below?
4526           'do_not_email' => 1,
4527           'do_not_mail' => 1,
4528           'do_not_sms' => 1,
4529           'do_not_phone' => 1,
4530           'do_not_trade' => 1,
4531           'is_opt_out' => 1,
4532           'contact_is_deleted' => 1,
4533           'preferred_communication_method' => 1,
4534           'preferred_language' => 1,
4535         ];
4536       }
4537     }
4538     return self::$_defaultReturnProperties[$mode];
4539   }
4540
4541   /**
4542    * Get primary condition for a sql clause.
4543    *
4544    * @param int $value
4545    *
4546    * @return string|NULL
4547    */
4548   public static function getPrimaryCondition($value) {
4549     if (is_numeric($value)) {
4550       $value = (int ) $value;
4551       return ($value == 1) ? 'is_primary = 1' : 'is_primary = 0';
4552     }
4553     return NULL;
4554   }
4555
4556   /**
4557    * Wrapper for a simple search query.
4558    *
4559    * @param array $params
4560    * @param array $returnProperties
4561    * @param bool $count
4562    *
4563    * @return string
4564    * @throws \CRM_Core_Exception
4565    */
4566   public static function getQuery($params = NULL, $returnProperties = NULL, $count = FALSE) {
4567     $query = new CRM_Contact_BAO_Query($params, $returnProperties);
4568     list($select, $from, $where, $having) = $query->query();
4569     $groupBy = ($query->_useGroupBy) ? 'GROUP BY contact_a.id' : '';
4570
4571     $query = "$select $from $where $groupBy $having";
4572     return $query;
4573   }
4574
4575   /**
4576    * These are stub comments as this function needs more explanation - particularly in terms of how it
4577    * relates to $this->searchQuery and why it replicates rather than calles $this->searchQuery.
4578    *
4579    * This function was originally written as a wrapper for the api query but is called from multiple places
4580    * in the core code directly so the name is misleading. This function does not use the searchQuery function
4581    * but it is unclear as to whehter that is historical or there is a reason
4582    *  CRM-11290 led to the permissioning action being extracted from searchQuery & shared with this function
4583    *
4584    * @param array $params
4585    * @param array $returnProperties
4586    * @param null $fields
4587    * @param string $sort
4588    * @param int $offset
4589    * @param int $row_count
4590    * @param bool $smartGroupCache
4591    *   ?? update smart group cache?.
4592    * @param bool $count
4593    *   Return count obnly.
4594    * @param bool $skipPermissions
4595    *   Should permissions be ignored or should the logged in user's permissions be applied.
4596    * @param int $mode
4597    *   This basically correlates to the component.
4598    * @param string $apiEntity
4599    *   The api entity being called.
4600    *   This sort-of duplicates $mode in a confusing way. Probably not by design.
4601    *
4602    * @param bool|null $primaryLocationOnly
4603    *
4604    * @return array
4605    * @throws \CRM_Core_Exception
4606    */
4607   public static function apiQuery(
4608     $params = NULL,
4609     $returnProperties = NULL,
4610     $fields = NULL,
4611     $sort = NULL,
4612     $offset = 0,
4613     $row_count = 25,
4614     $smartGroupCache = TRUE,
4615     $count = FALSE,
4616     $skipPermissions = TRUE,
4617     $mode = CRM_Contact_BAO_Query::MODE_CONTACTS,
4618     $apiEntity = NULL,
4619     $primaryLocationOnly = NULL
4620   ) {
4621
4622     $query = new CRM_Contact_BAO_Query(
4623       $params, $returnProperties,
4624       NULL, TRUE, FALSE, $mode,
4625       $skipPermissions,
4626       TRUE, $smartGroupCache,
4627       NULL, 'AND',
4628       $apiEntity, $primaryLocationOnly
4629     );
4630
4631     //this should add a check for view deleted if permissions are enabled
4632     if ($skipPermissions) {
4633       $query->_skipDeleteClause = TRUE;
4634     }
4635     $query->generatePermissionClause(FALSE, $count);
4636
4637     // note : this modifies _fromClause and _simpleFromClause
4638     $query->includePseudoFieldsJoin($sort);
4639
4640     list($select, $from, $where, $having) = $query->query($count);
4641
4642     $options = $query->_options;
4643     if (!empty($query->_permissionWhereClause)) {
4644       if (empty($where)) {
4645         $where = "WHERE $query->_permissionWhereClause";
4646       }
4647       else {
4648         $where = "$where AND $query->_permissionWhereClause";
4649       }
4650     }
4651
4652     $sql = "$select $from $where $having";
4653
4654     // add group by only when API action is not getcount
4655     //  otherwise query fetches incorrect count
4656     if ($query->_useGroupBy && !$count) {
4657       $sql .= self::getGroupByFromSelectColumns($query->_select, 'contact_a.id');
4658     }
4659     if (!empty($sort)) {
4660       $sort = CRM_Utils_Type::escape($sort, 'String');
4661       $sql .= " ORDER BY $sort ";
4662     }
4663     if ($row_count > 0 && $offset >= 0) {
4664       $offset = CRM_Utils_Type::escape($offset, 'Int');
4665       $row_count = CRM_Utils_Type::escape($row_count, 'Int');
4666       $sql .= " LIMIT $offset, $row_count ";
4667     }
4668
4669     $dao = CRM_Core_DAO::executeQuery($sql);
4670
4671     // @todo derive this from the component class rather than hard-code two options.
4672     $entityIDField = ($mode == CRM_Contact_BAO_Query::MODE_CONTRIBUTE) ? 'contribution_id' : 'contact_id';
4673
4674     $values = [];
4675     while ($dao->fetch()) {
4676       if ($count) {
4677         $noRows = $dao->rowCount;
4678         return [$noRows, NULL];
4679       }
4680       $val = $query->store($dao);
4681       $convertedVals = $query->convertToPseudoNames($dao, TRUE, TRUE);
4682
4683       if (!empty($convertedVals)) {
4684         $val = array_replace_recursive($val, $convertedVals);
4685       }
4686       $values[$dao->$entityIDField] = $val;
4687     }
4688     return [$values, $options];
4689   }
4690
4691   /**
4692    * Get the actual custom field name by stripping off the appended string.
4693    *
4694    * The string could be _relative, _from, or _to
4695    *
4696    * @todo use metadata rather than convention to do this.
4697    *
4698    * @param string $parameterName
4699    *   The name of the parameter submitted to the form.
4700    *   e.g
4701    *   custom_3_relative
4702    *   custom_3_from
4703    *
4704    * @return string
4705    */
4706   public static function getCustomFieldName($parameterName) {
4707     if (substr($parameterName, -5, 5) == '_from') {
4708       return substr($parameterName, 0, strpos($parameterName, '_from'));
4709     }
4710     if (substr($parameterName, -9, 9) == '_relative') {
4711       return substr($parameterName, 0, strpos($parameterName, '_relative'));
4712     }
4713     if (substr($parameterName, -3, 3) == '_to') {
4714       return substr($parameterName, 0, strpos($parameterName, '_to'));
4715     }
4716   }
4717
4718   /**
4719    * Convert submitted values for relative custom fields to query object format.
4720    *
4721    * The query will support the sqlOperator format so convert to that format.
4722    *
4723    * @param array $formValues
4724    *   Submitted values.
4725    * @param array $params
4726    *   Converted parameters for the query object.
4727    * @param string $values
4728    *   Submitted value.
4729    * @param string $fieldName
4730    *   Submitted field name. (Matches form field not DB field.)
4731    */
4732   protected static function convertCustomRelativeFields(&$formValues, &$params, $values, $fieldName) {
4733     if (empty($values)) {
4734       // e.g we might have relative set & from & to empty. The form flow is a bit funky &
4735       // this function gets called again after they fields have been converted which can get ugly.
4736       return;
4737     }
4738     $customFieldName = self::getCustomFieldName($fieldName);
4739
4740     if (substr($fieldName, -9, 9) == '_relative') {
4741       list($from, $to) = CRM_Utils_Date::getFromTo($values, NULL, NULL);
4742     }
4743     else {
4744       if ($fieldName == $customFieldName . '_to' && CRM_Utils_Array::value($customFieldName . '_from', $formValues)) {
4745         // Both to & from are set. We only need to acton one, choosing from.
4746         return;
4747       }
4748
4749       $from = CRM_Utils_Array::value($customFieldName . '_from', $formValues, NULL);
4750       $to = CRM_Utils_Array::value($customFieldName . '_to', $formValues, NULL);
4751
4752       if (self::isCustomDateField($customFieldName)) {
4753         list($from, $to) = CRM_Utils_Date::getFromTo(NULL, $from, $to);
4754       }
4755     }
4756
4757     if ($from) {
4758       if ($to) {
4759         $relativeFunction = ['BETWEEN' => [$from, $to]];
4760       }
4761       else {
4762         $relativeFunction = ['>=' => $from];
4763       }
4764     }
4765     else {
4766       $relativeFunction = ['<=' => $to];
4767     }
4768     $params[] = [
4769       $customFieldName,
4770       '=',
4771       $relativeFunction,
4772       0,
4773       0,
4774     ];
4775   }
4776
4777   /**
4778    * Are we dealing with custom field of type date.
4779    *
4780    * @param $fieldName
4781    *
4782    * @return bool
4783    * @throws \CiviCRM_API3_Exception
4784    */
4785   public static function isCustomDateField($fieldName) {
4786     if (($customFieldID = CRM_Core_BAO_CustomField::getKeyID($fieldName)) == FALSE) {
4787       return FALSE;
4788     }
4789     if ('Date' == civicrm_api3('CustomField', 'getvalue', ['id' => $customFieldID, 'return' => 'data_type'])) {
4790       return TRUE;
4791     }
4792     return FALSE;
4793   }
4794
4795   /**
4796    * Has this field already been reformatting to Query object syntax.
4797    *
4798    * The form layer passed formValues to this function in preProcess & postProcess. Reason unknown. This seems
4799    * to come with associated double queries & is possibly damaging performance.
4800    *
4801    * However, here we add a tested function to ensure convertFormValues identifies pre-processed fields & returns
4802    * them as they are.
4803    *
4804    * @param mixed $values
4805    *   Value in formValues for the field.
4806    *
4807    * @return bool;
4808    */
4809   public static function isAlreadyProcessedForQueryFormat($values) {
4810     if (!is_array($values)) {
4811       return FALSE;
4812     }
4813     if (($operator = CRM_Utils_Array::value(1, $values)) == FALSE) {
4814       return FALSE;
4815     }
4816     return in_array($operator, CRM_Core_DAO::acceptedSQLOperators());
4817   }
4818
4819   /**
4820    * If the state and country are passed remove state.
4821    *
4822    * Country is implicit from the state, but including both results in
4823    * a poor query as there is no combined index on state AND country.
4824    *
4825    * CRM-18125
4826    *
4827    * @param array $formValues
4828    */
4829   public static function filterCountryFromValuesIfStateExists(&$formValues) {
4830     if (!empty($formValues['country']) && !empty($formValues['state_province'])) {
4831       // The use of array map sanitises the data by ensuring we are dealing with integers.
4832       $states = implode(', ', array_map('intval', $formValues['state_province']));
4833       $countryList = CRM_Core_DAO::singleValueQuery(
4834         "SELECT GROUP_CONCAT(country_id) FROM civicrm_state_province WHERE id IN ($states)"
4835       );
4836       if ($countryList == $formValues['country']) {
4837         unset($formValues['country']);
4838       }
4839     }
4840   }
4841
4842   /**
4843    * For some special cases, grouping by subset of select fields becomes mandatory.
4844    * Hence, full_group_by mode is handled by appending any_value
4845    * keyword to select fields not present in groupBy
4846    *
4847    * @param array $selectClauses
4848    * @param array $groupBy - Columns already included in GROUP By clause.
4849    * @param string $aggregateFunction
4850    *
4851    * @return string
4852    */
4853   public static function appendAnyValueToSelect($selectClauses, $groupBy, $aggregateFunction = 'ANY_VALUE') {
4854     if (!CRM_Utils_SQL::disableFullGroupByMode()) {
4855       $groupBy = array_map('trim', (array) $groupBy);
4856       $aggregateFunctions = '/(ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN|IF)[[:blank:]]*\(/i';
4857       foreach ($selectClauses as $key => &$val) {
4858         list($selectColumn, $alias) = array_pad(preg_split('/ as /i', $val), 2, NULL);
4859         // append ANY_VALUE() keyword
4860         if (!in_array($selectColumn, $groupBy) && preg_match($aggregateFunctions, trim($selectColumn)) !== 1) {
4861           $val = ($aggregateFunction == 'GROUP_CONCAT') ?
4862             str_replace($selectColumn, "$aggregateFunction(DISTINCT {$selectColumn})", $val) :
4863             str_replace($selectColumn, "$aggregateFunction({$selectColumn})", $val);
4864         }
4865       }
4866     }
4867
4868     return "SELECT " . implode(', ', $selectClauses) . " ";
4869   }
4870
4871   /**
4872    * For some special cases, where if non-aggregate ORDER BY columns are not present in GROUP BY
4873    *  on full_group_by mode, then append the those missing columns to GROUP BY clause
4874    * keyword to select fields not present in groupBy
4875    *
4876    * @param string $groupBy - GROUP BY clause where missing ORDER BY columns will be appended if not present
4877    * @param array $orderBys - ORDER BY sub-clauses
4878    *
4879    */
4880   public static function getGroupByFromOrderBy(&$groupBy, $orderBys) {
4881     if (!CRM_Utils_SQL::disableFullGroupByMode()) {
4882       foreach ($orderBys as $orderBy) {
4883         // remove sort syntax from ORDER BY clauses if present
4884         $orderBy = str_ireplace([' DESC', ' ASC', '`'], '', $orderBy);
4885         // if ORDER BY column is not present in GROUP BY then append it to end
4886         if (preg_match('/(MAX|MIN)\(/i', trim($orderBy)) !== 1 && !strstr($groupBy, $orderBy)) {
4887           $groupBy .= ", {$orderBy}";
4888         }
4889       }
4890     }
4891   }
4892
4893   /**
4894    * Include Select columns in groupBy clause.
4895    *
4896    * @param array $selectClauses
4897    * @param array $groupBy - Columns already included in GROUP By clause.
4898    *
4899    * @return string
4900    */
4901   public static function getGroupByFromSelectColumns($selectClauses, $groupBy = NULL) {
4902     $groupBy = (array) $groupBy;
4903     $mysqlVersion = CRM_Core_DAO::singleValueQuery('SELECT VERSION()');
4904     $sqlMode = CRM_Core_DAO::singleValueQuery('SELECT @@sql_mode');
4905
4906     //return if ONLY_FULL_GROUP_BY is not enabled.
4907     if (CRM_Utils_SQL::supportsFullGroupBy() && !empty($sqlMode) && in_array('ONLY_FULL_GROUP_BY', explode(',', $sqlMode))) {
4908       $regexToExclude = '/(ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN|IF)[[:blank:]]*\(/i';
4909       foreach ($selectClauses as $key => $val) {
4910         $aliasArray = preg_split('/ as /i', $val);
4911         // if more than 1 alias we need to split by ','.
4912         if (count($aliasArray) > 2) {
4913           $aliasArray = preg_split('/,/', $val);
4914           foreach ($aliasArray as $key => $value) {
4915             $alias = current(preg_split('/ as /i', $value));
4916             if (!in_array($alias, $groupBy) && preg_match($regexToExclude, trim($alias)) !== 1) {
4917               $groupBy[] = $alias;
4918             }
4919           }
4920         }
4921         else {
4922           list($selectColumn, $alias) = array_pad($aliasArray, 2, NULL);
4923           $dateRegex = '/^(DATE_FORMAT|DATE_ADD|CASE)/i';
4924           $tableName = current(explode('.', $selectColumn));
4925           $primaryKey = "{$tableName}.id";
4926           // exclude columns which are already included in groupBy and aggregate functions from select
4927           // CRM-18439 - Also exclude the columns which are functionally dependent on columns in $groupBy (MySQL 5.7+)
4928           if (!in_array($selectColumn, $groupBy) && !in_array($primaryKey, $groupBy) && preg_match($regexToExclude, trim($selectColumn)) !== 1) {
4929             if (!empty($alias) && preg_match($dateRegex, trim($selectColumn))) {
4930               $groupBy[] = $alias;
4931             }
4932             else {
4933               $groupBy[] = $selectColumn;
4934             }
4935           }
4936         }
4937       }
4938     }
4939
4940     if (!empty($groupBy)) {
4941       return " GROUP BY " . implode(', ', $groupBy);
4942     }
4943     return '';
4944   }
4945
4946   /**
4947    * Create and query the db for an contact search.
4948    *
4949    * @param int $offset
4950    *   The offset for the query.
4951    * @param int $rowCount
4952    *   The number of rows to return.
4953    * @param string|CRM_Utils_Sort $sort
4954    *   The order by string.
4955    * @param bool $count
4956    *   Is this a count only query ?.
4957    * @param bool $includeContactIds
4958    *   Should we include contact ids?.
4959    * @param bool $sortByChar
4960    *   If true returns the distinct array of first characters for search results.
4961    * @param bool $groupContacts
4962    *   If true, return only the contact ids.
4963    * @param bool $returnQuery
4964    *   Should we return the query as a string.
4965    * @param string $additionalWhereClause
4966    *   If the caller wants to further restrict the search (used for components).
4967    * @param null $sortOrder
4968    * @param string $additionalFromClause
4969    *   Should be clause with proper joins, effective to reduce where clause load.
4970    *
4971    * @param bool $skipOrderAndLimit
4972    *
4973    * @return CRM_Core_DAO
4974    */
4975   public function searchQuery(
4976     $offset = 0, $rowCount = 0, $sort = NULL,
4977     $count = FALSE, $includeContactIds = FALSE,
4978     $sortByChar = FALSE, $groupContacts = FALSE,
4979     $returnQuery = FALSE,
4980     $additionalWhereClause = NULL, $sortOrder = NULL,
4981     $additionalFromClause = NULL, $skipOrderAndLimit = FALSE
4982   ) {
4983
4984     $query = $this->getSearchSQL($offset, $rowCount, $sort, $count, $includeContactIds, $sortByChar, $groupContacts, $additionalWhereClause, $sortOrder, $additionalFromClause, $skipOrderAndLimit);
4985
4986     if ($returnQuery) {
4987       return $query;
4988     }
4989     if ($count) {
4990       return CRM_Core_DAO::singleValueQuery($query);
4991     }
4992
4993     $dao = CRM_Core_DAO::executeQuery($query);
4994
4995     // We can always call this - it will only re-enable if it was originally enabled.
4996     CRM_Core_DAO::reenableFullGroupByMode();
4997
4998     if ($groupContacts) {
4999       $ids = [];
5000       while ($dao->fetch()) {
5001         $ids[] = $dao->id;
5002       }
5003       return implode(',', $ids);
5004     }
5005
5006     return $dao;
5007   }
5008
5009   /**
5010    * Create and query the db for the list of all first letters used by contacts
5011    *
5012    * @return CRM_Core_DAO
5013    */
5014   public function alphabetQuery() {
5015     $sqlParts = $this->getSearchSQLParts(NULL, NULL, NULL, FALSE, FALSE, TRUE);
5016     $query = "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name
5017       {$sqlParts['from']}
5018       {$sqlParts['where']}
5019       {$sqlParts['having']}
5020       GROUP BY sort_name
5021       ORDER BY sort_name asc";
5022     $dao = CRM_Core_DAO::executeQuery($query);
5023     return $dao;
5024   }
5025
5026   /**
5027    * Fetch a list of contacts for displaying a search results page
5028    *
5029    * @param array $cids
5030    *   List of contact IDs
5031    * @param bool $includeContactIds
5032    * @return CRM_Core_DAO
5033    */
5034   public function getCachedContacts($cids, $includeContactIds) {
5035     CRM_Core_DAO::disableFullGroupByMode();
5036     CRM_Utils_Type::validateAll($cids, 'Positive');
5037     $this->_includeContactIds = $includeContactIds;
5038     $onlyDeleted = in_array(['deleted_contacts', '=', '1', '0', '0'], $this->_params);
5039     list($select, $from, $where) = $this->query(FALSE, FALSE, FALSE, $onlyDeleted);
5040     $select .= sprintf(", (%s) AS _wgt", $this->createSqlCase('contact_a.id', $cids));
5041     $where .= sprintf(' AND contact_a.id IN (%s)', implode(',', $cids));
5042     $order = 'ORDER BY _wgt';
5043     $groupBy = $this->_useGroupBy ? ' GROUP BY contact_a.id' : '';
5044     $limit = '';
5045     $query = "$select $from $where $groupBy $order $limit";
5046
5047     $result = CRM_Core_DAO::executeQuery($query);
5048     CRM_Core_DAO::reenableFullGroupByMode();
5049     return $result;
5050   }
5051
5052   /**
5053    * Construct a SQL CASE expression.
5054    *
5055    * @param string $idCol
5056    *   The name of a column with ID's (eg 'contact_a.id').
5057    * @param array $cids
5058    *   Array(int $weight => int $id).
5059    * @return string
5060    *   CASE WHEN id=123 THEN 1 WHEN id=456 THEN 2 END
5061    */
5062   private function createSqlCase($idCol, $cids) {
5063     $buf = "CASE\n";
5064     foreach ($cids as $weight => $cid) {
5065       $buf .= " WHEN $idCol = $cid THEN $weight \n";
5066     }
5067     $buf .= "END\n";
5068     return $buf;
5069   }
5070
5071   /**
5072    * Populate $this->_permissionWhereClause with permission related clause and update other
5073    * query related properties.
5074    *
5075    * Function calls ACL permission class and hooks to filter the query appropriately
5076    *
5077    * Note that these 2 params were in the code when extracted from another function
5078    * and a second round extraction would be to make them properties of the class
5079    *
5080    * @param bool $onlyDeleted
5081    *   Only get deleted contacts.
5082    * @param bool $count
5083    *   Return Count only.
5084    */
5085   public function generatePermissionClause($onlyDeleted = FALSE, $count = FALSE) {
5086     if (!$this->_skipPermission) {
5087       $this->_permissionWhereClause = CRM_ACL_API::whereClause(
5088         CRM_Core_Permission::VIEW,
5089         $this->_tables,
5090         $this->_whereTables,
5091         NULL,
5092         $onlyDeleted,
5093         $this->_skipDeleteClause
5094       );
5095
5096       // regenerate fromClause since permission might have added tables
5097       if ($this->_permissionWhereClause) {
5098         //fix for row count in qill (in contribute/membership find)
5099         if (!$count) {
5100           $this->_useDistinct = TRUE;
5101         }
5102         //CRM-15231
5103         $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
5104         $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
5105         // note : this modifies _fromClause and _simpleFromClause
5106         $this->includePseudoFieldsJoin($this->_sort);
5107       }
5108     }
5109     else {
5110       // add delete clause if needed even if we are skipping permission
5111       // CRM-7639
5112       if (!$this->_skipDeleteClause) {
5113         if (CRM_Core_Permission::check('access deleted contacts') and $onlyDeleted) {
5114           $this->_permissionWhereClause = '(contact_a.is_deleted)';
5115         }
5116         else {
5117           // CRM-6181
5118           $this->_permissionWhereClause = '(contact_a.is_deleted = 0)';
5119         }
5120       }
5121     }
5122   }
5123
5124   /**
5125    * @param $val
5126    */
5127   public function setSkipPermission($val) {
5128     $this->_skipPermission = $val;
5129   }
5130
5131   /**
5132    * @param null $context
5133    *
5134    * @return array
5135    * @throws \CRM_Core_Exception
5136    */
5137   public function summaryContribution($context = NULL) {
5138     list($innerselect, $from, $where, $having) = $this->query(TRUE);
5139     if ($this->_permissionWhereClause) {
5140       $where .= " AND " . $this->_permissionWhereClause;
5141     }
5142     if ($context == 'search') {
5143       $where .= " AND contact_a.is_deleted = 0 ";
5144     }
5145
5146     $this->appendFinancialTypeWhereAndFromToQueryStrings($where, $from);
5147
5148     $summary = ['total' => []];
5149     $this->addBasicStatsToSummary($summary, $where, $from);
5150
5151     if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
5152       $this->addBasicSoftCreditStatsToStats($summary, $where, $from);
5153     }
5154
5155     $this->addBasicCancelStatsToSummary($summary, $where, $from);
5156
5157     return $summary;
5158   }
5159
5160   /**
5161    * Append financial ACL limits to the query from & where clauses, if applicable.
5162    *
5163    * @param string $where
5164    * @param string $from
5165    */
5166   public function appendFinancialTypeWhereAndFromToQueryStrings(&$where, &$from) {
5167     if (!CRM_Financial_BAO_FinancialType::isACLFinancialTypeStatus()) {
5168       return;
5169     }
5170     CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes($financialTypes);
5171     if (!empty($financialTypes)) {
5172       $where .= " AND civicrm_contribution.financial_type_id IN (" . implode(',', array_keys($financialTypes)) . ") AND li.id IS NULL";
5173       $from .= " LEFT JOIN civicrm_line_item li
5174                   ON civicrm_contribution.id = li.contribution_id AND
5175                      li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN (" . implode(',', array_keys($financialTypes)) . ") ";
5176     }
5177     else {
5178       $where .= " AND civicrm_contribution.financial_type_id IN (0)";
5179     }
5180   }
5181
5182   /**
5183    * Getter for the qill object.
5184    *
5185    * @return array
5186    */
5187   public function qill() {
5188     return $this->_qill;
5189   }
5190
5191   /**
5192    * Default set of return default hier return properties.
5193    *
5194    * @return array
5195    */
5196   public static function &defaultHierReturnProperties() {
5197     if (!isset(self::$_defaultHierReturnProperties)) {
5198       self::$_defaultHierReturnProperties = [
5199         'home_URL' => 1,
5200         'image_URL' => 1,
5201         'legal_identifier' => 1,
5202         'external_identifier' => 1,
5203         'contact_type' => 1,
5204         'contact_sub_type' => 1,
5205         'sort_name' => 1,
5206         'display_name' => 1,
5207         'nick_name' => 1,
5208         'first_name' => 1,
5209         'middle_name' => 1,
5210         'last_name' => 1,
5211         'prefix_id' => 1,
5212         'suffix_id' => 1,
5213         'formal_title' => 1,
5214         'communication_style_id' => 1,
5215         'email_greeting' => 1,
5216         'postal_greeting' => 1,
5217         'addressee' => 1,
5218         'birth_date' => 1,
5219         'gender_id' => 1,
5220         'preferred_communication_method' => 1,
5221         'do_not_phone' => 1,
5222         'do_not_email' => 1,
5223         'do_not_mail' => 1,
5224         'do_not_sms' => 1,
5225         'do_not_trade' => 1,
5226         'location' => [
5227           '1' => [
5228             'location_type' => 1,
5229             'street_address' => 1,
5230             'city' => 1,
5231             'state_province' => 1,
5232             'postal_code' => 1,
5233             'postal_code_suffix' => 1,
5234             'country' => 1,
5235             'phone-Phone' => 1,
5236             'phone-Mobile' => 1,
5237             'phone-Fax' => 1,
5238             'phone-1' => 1,
5239             'phone-2' => 1,
5240             'phone-3' => 1,
5241             'im-1' => 1,
5242             'im-2' => 1,
5243             'im-3' => 1,
5244             'email-1' => 1,
5245             'email-2' => 1,
5246             'email-3' => 1,
5247           ],
5248           '2' => [
5249             'location_type' => 1,
5250             'street_address' => 1,
5251             'city' => 1,
5252             'state_province' => 1,
5253             'postal_code' => 1,
5254             'postal_code_suffix' => 1,
5255             'country' => 1,
5256             'phone-Phone' => 1,
5257             'phone-Mobile' => 1,
5258             'phone-1' => 1,
5259             'phone-2' => 1,
5260             'phone-3' => 1,
5261             'im-1' => 1,
5262             'im-2' => 1,
5263             'im-3' => 1,
5264             'email-1' => 1,
5265             'email-2' => 1,
5266             'email-3' => 1,
5267           ],
5268         ],
5269       ];
5270     }
5271     return self::$_defaultHierReturnProperties;
5272   }
5273
5274   /**
5275    * Build query for a date field.
5276    *
5277    * @param array $values
5278    * @param string $tableName
5279    * @param string $fieldName
5280    * @param string $dbFieldName
5281    * @param string $fieldTitle
5282    * @param bool $appendTimeStamp
5283    * @param string $dateFormat
5284    * @param string|null $highDBFieldName
5285    *   Optional field name for when the 'high' part of the calculation uses a different field than the 'low' part.
5286    *   This is an obscure situation & one we don't want to do more of but supporting them here is the only way for now.
5287    *   Examples are event date & relationship active date -in both cases we are looking for things greater than the start
5288    *   date & less than the end date.
5289    *
5290    * @throws \CRM_Core_Exception
5291    */
5292   public function dateQueryBuilder(
5293     $values, $tableName, $fieldName,
5294     $dbFieldName, $fieldTitle,
5295     $appendTimeStamp = TRUE,
5296     $dateFormat = 'YmdHis',
5297     $highDBFieldName = NULL
5298   ) {
5299     // @todo - remove dateFormat - pretty sure it's never passed in...
5300     list($name, $op, $value, $grouping, $wildcard) = $values;
5301     if ($name !== $fieldName && $name !== "{$fieldName}_low" && $name !== "{$fieldName}_high") {
5302       CRM_Core_Error::deprecatedFunctionWarning('Date query builder called unexpectedly');
5303       return;
5304     }
5305     if ($tableName === 'civicrm_contact') {
5306       // Special handling for contact table as it has a known alias in advanced search.
5307       $tableName = 'contact_a';
5308     }
5309     if ($name === "{$fieldName}_low" ||
5310       $name === "{$fieldName}_high"
5311     ) {
5312       if (isset($this->_rangeCache[$fieldName]) || !$value) {
5313         return;
5314       }
5315       $this->_rangeCache[$fieldName] = 1;
5316
5317       $secondOP = $secondPhrase = $secondValue = $secondDate = $secondDateFormat = NULL;
5318
5319       if ($name == $fieldName . '_low') {
5320         $firstOP = '>=';
5321         $firstPhrase = ts('greater than or equal to');
5322         $firstDate = CRM_Utils_Date::processDate($value, NULL, FALSE, $dateFormat);
5323
5324         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5325         if (!empty($secondValues) && $secondValues[2]) {
5326           $secondOP = '<=';
5327           $secondPhrase = ts('less than or equal to');
5328           $secondValue = $secondValues[2];
5329
5330           if ($appendTimeStamp && strlen($secondValue) == 10) {
5331             $secondValue .= ' 23:59:59';
5332           }
5333           $secondDate = CRM_Utils_Date::processDate($secondValue, NULL, FALSE, $dateFormat);
5334         }
5335       }
5336       elseif ($name == $fieldName . '_high') {
5337         $firstOP = '<=';
5338         $firstPhrase = ts('less than or equal to');
5339
5340         if ($appendTimeStamp && strlen($value) == 10) {
5341           $value .= ' 23:59:59';
5342         }
5343         $firstDate = CRM_Utils_Date::processDate($value, NULL, FALSE, $dateFormat);
5344
5345         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5346         if (!empty($secondValues) && $secondValues[2]) {
5347           $secondOP = '>=';
5348           $secondPhrase = ts('greater than or equal to');
5349           $secondValue = $secondValues[2];
5350           $secondDate = CRM_Utils_Date::processDate($secondValue, NULL, FALSE, $dateFormat);
5351         }
5352       }
5353
5354       if (!$appendTimeStamp) {
5355         $firstDate = substr($firstDate, 0, 8);
5356       }
5357       $firstDateFormat = CRM_Utils_Date::customFormat($firstDate);
5358
5359       if ($secondDate) {
5360         if (!$appendTimeStamp) {
5361           $secondDate = substr($secondDate, 0, 8);
5362         }
5363         $secondDateFormat = CRM_Utils_Date::customFormat($secondDate);
5364       }
5365
5366       if ($secondDate) {
5367         $highDBFieldName = $highDBFieldName ?? $dbFieldName;
5368         $this->_where[$grouping][] = "
5369 ( {$tableName}.{$dbFieldName} $firstOP '$firstDate' ) AND
5370 ( {$tableName}.{$highDBFieldName} $secondOP '$secondDate' )
5371 ";
5372         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$firstDateFormat\" " . ts('AND') . " $secondPhrase \"$secondDateFormat\"";
5373       }
5374       else {
5375         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP '$firstDate'";
5376         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$firstDateFormat\"";
5377       }
5378     }
5379
5380     if ($name == $fieldName) {
5381       //In Get API, for operators other then '=' the $value is in array(op => value) format
5382       if (is_array($value) && !empty($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
5383         $op = key($value);
5384         $value = $value[$op];
5385       }
5386
5387       $date = $format = NULL;
5388       if (strstr($op, 'IN')) {
5389         $format = [];
5390         foreach ($value as &$date) {
5391           $date = CRM_Utils_Date::processDate($date, NULL, FALSE, $dateFormat);
5392           if (!$appendTimeStamp) {
5393             $date = substr($date, 0, 8);
5394           }
5395           $format[] = CRM_Utils_Date::customFormat($date);
5396         }
5397         $date = "('" . implode("','", $value) . "')";
5398         $format = implode(', ', $format);
5399       }
5400       elseif ($value && (!strstr($op, 'NULL') && !strstr($op, 'EMPTY'))) {
5401         $date = CRM_Utils_Date::processDate($value, NULL, FALSE, $dateFormat);
5402         if (!$appendTimeStamp) {
5403           $date = substr($date, 0, 8);
5404         }
5405         $format = CRM_Utils_Date::customFormat($date);
5406         $date = "'$date'";
5407       }
5408
5409       if ($date) {
5410         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op $date";
5411       }
5412       else {
5413         $this->_where[$grouping][] = self::buildClause("{$tableName}.{$dbFieldName}", $op);
5414       }
5415
5416       $op = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
5417       $this->_qill[$grouping][] = "$fieldTitle $op $format";
5418     }
5419
5420     // Ensure the tables are set, but don't whomp anything.
5421     $this->_tables[$tableName] = $this->_tables[$tableName] ?? 1;
5422     $this->_whereTables[$tableName] = $this->_whereTables[$tableName] ?? 1;
5423   }
5424
5425   /**
5426    * @param $values
5427    * @param string $tableName
5428    * @param string $fieldName
5429    * @param string $dbFieldName
5430    * @param $fieldTitle
5431    * @param null $options
5432    */
5433   public function numberRangeBuilder(
5434     &$values,
5435     $tableName, $fieldName,
5436     $dbFieldName, $fieldTitle,
5437     $options = NULL
5438   ) {
5439     list($name, $op, $value, $grouping, $wildcard) = $values;
5440
5441     if ($name == "{$fieldName}_low" ||
5442       $name == "{$fieldName}_high"
5443     ) {
5444       if (isset($this->_rangeCache[$fieldName])) {
5445         return;
5446       }
5447       $this->_rangeCache[$fieldName] = 1;
5448
5449       $secondOP = $secondPhrase = $secondValue = NULL;
5450
5451       if ($name == "{$fieldName}_low") {
5452         $firstOP = '>=';
5453         $firstPhrase = ts('greater than');
5454
5455         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5456         if (!empty($secondValues)) {
5457           $secondOP = '<=';
5458           $secondPhrase = ts('less than');
5459           $secondValue = $secondValues[2];
5460         }
5461       }
5462       else {
5463         $firstOP = '<=';
5464         $firstPhrase = ts('less than');
5465
5466         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5467         if (!empty($secondValues)) {
5468           $secondOP = '>=';
5469           $secondPhrase = ts('greater than');
5470           $secondValue = $secondValues[2];
5471         }
5472       }
5473
5474       if ($secondOP) {
5475         $this->_where[$grouping][] = "
5476 ( {$tableName}.{$dbFieldName} $firstOP {$value} ) AND
5477 ( {$tableName}.{$dbFieldName} $secondOP {$secondValue} )
5478 ";
5479         $displayValue = $options ? $options[$value] : $value;
5480         $secondDisplayValue = $options ? $options[$secondValue] : $secondValue;
5481
5482         $this->_qill[$grouping][]
5483           = "$fieldTitle - $firstPhrase \"$displayValue\" " . ts('AND') . " $secondPhrase \"$secondDisplayValue\"";
5484       }
5485       else {
5486         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP {$value}";
5487         $displayValue = $options ? $options[$value] : $value;
5488         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$displayValue\"";
5489       }
5490       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5491
5492       return;
5493     }
5494
5495     if ($name == $fieldName) {
5496       $op = '=';
5497       $phrase = '=';
5498
5499       $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op {$value}";
5500
5501       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5502       $displayValue = $options ? $options[$value] : $value;
5503       $this->_qill[$grouping][] = "$fieldTitle - $phrase \"$displayValue\"";
5504     }
5505   }
5506
5507   /**
5508    * @param $values
5509    * @param string $tableName
5510    * @param string $fieldName
5511    * @param string $dbFieldName
5512    * @param $fieldTitle
5513    * @param null $options
5514    */
5515   public function ageRangeQueryBuilder(
5516     &$values,
5517     $tableName, $fieldName,
5518     $dbFieldName, $fieldTitle,
5519     $options = NULL
5520   ) {
5521     list($name, $op, $value, $grouping, $wildcard) = $values;
5522
5523     $asofDateValues = $this->getWhereValues("{$fieldName}_asof_date", $grouping);
5524     // will be treated as current day
5525     $asofDate = NULL;
5526     if ($asofDateValues) {
5527       $asofDate = CRM_Utils_Date::processDate($asofDateValues[2]);
5528       $asofDateFormat = CRM_Utils_Date::customFormat(substr($asofDate, 0, 8));
5529       $fieldTitle .= ' ' . ts('as of') . ' ' . $asofDateFormat;
5530     }
5531
5532     if ($name == "{$fieldName}_low" ||
5533       $name == "{$fieldName}_high"
5534     ) {
5535       if (isset($this->_rangeCache[$fieldName])) {
5536         return;
5537       }
5538       $this->_rangeCache[$fieldName] = 1;
5539
5540       $secondOP = $secondPhrase = $secondValue = NULL;
5541
5542       if ($name == "{$fieldName}_low") {
5543         $firstPhrase = ts('greater than or equal to');
5544         // NB: age > X means date of birth < Y
5545         $firstOP = '<=';
5546         $firstDate = self::calcDateFromAge($asofDate, $value, 'min');
5547
5548         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5549         if (!empty($secondValues)) {
5550           $secondOP = '>=';
5551           $secondPhrase = ts('less than or equal to');
5552           $secondValue = $secondValues[2];
5553           $secondDate = self::calcDateFromAge($asofDate, $secondValue, 'max');
5554         }
5555       }
5556       else {
5557         $firstOP = '>=';
5558         $firstPhrase = ts('less than or equal to');
5559         $firstDate = self::calcDateFromAge($asofDate, $value, 'max');
5560
5561         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5562         if (!empty($secondValues)) {
5563           $secondOP = '<=';
5564           $secondPhrase = ts('greater than or equal to');
5565           $secondValue = $secondValues[2];
5566           $secondDate = self::calcDateFromAge($asofDate, $secondValue, 'min');
5567         }
5568       }
5569
5570       if ($secondOP) {
5571         $this->_where[$grouping][] = "
5572 ( {$tableName}.{$dbFieldName} $firstOP '$firstDate' ) AND
5573 ( {$tableName}.{$dbFieldName} $secondOP '$secondDate' )
5574 ";
5575         $displayValue = $options ? $options[$value] : $value;
5576         $secondDisplayValue = $options ? $options[$secondValue] : $secondValue;
5577
5578         $this->_qill[$grouping][]
5579           = "$fieldTitle - $firstPhrase \"$displayValue\" " . ts('AND') . " $secondPhrase \"$secondDisplayValue\"";
5580       }
5581       else {
5582         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP '$firstDate'";
5583         $displayValue = $options ? $options[$value] : $value;
5584         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$displayValue\"";
5585       }
5586       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5587       return;
5588     }
5589   }
5590
5591   /**
5592    * Calculate date from age.
5593    *
5594    * @param string $asofDate
5595    * @param int $age
5596    * @param string $type
5597    *
5598    * @return string
5599    * @throws \Exception
5600    */
5601   public static function calcDateFromAge($asofDate, $age, $type) {
5602     $date = new DateTime($asofDate);
5603     if ($type == "min") {
5604       // minimum age is $age: dob <= date - age "235959"
5605       $date->sub(new DateInterval("P" . $age . "Y"));
5606       return $date->format('Ymd') . "235959";
5607     }
5608     else {
5609       // max age is $age: dob >= date - (age + 1y) + 1d "000000"
5610       $date->sub(new DateInterval("P" . ($age + 1) . "Y"))->add(new DateInterval("P1D"));
5611       return $date->format('Ymd') . "000000";
5612     }
5613   }
5614
5615   /**
5616    * Given the field name, operator, value & its data type
5617    * builds the where Clause for the query
5618    * used for handling 'IS NULL'/'IS NOT NULL' operators
5619    *
5620    * @param string $field
5621    *   Fieldname.
5622    * @param string $op
5623    *   Operator.
5624    * @param string $value
5625    *   Value.
5626    * @param string $dataType
5627    *   Data type of the field.
5628    *
5629    * @return string
5630    *   Where clause for the query.
5631    * @throws \CRM_Core_Exception
5632    */
5633   public static function buildClause($field, $op, $value = NULL, $dataType = NULL) {
5634     $op = trim($op);
5635     $clause = "$field $op";
5636
5637     switch ($op) {
5638       case 'IS NULL':
5639       case 'IS NOT NULL':
5640         return $clause;
5641
5642       case 'IS EMPTY':
5643         $clause = ($dataType == 'Date') ? " $field IS NULL " : " (NULLIF($field, '') IS NULL) ";
5644         return $clause;
5645
5646       case 'IS NOT EMPTY':
5647         $clause = ($dataType == 'Date') ? " $field IS NOT NULL " : " (NULLIF($field, '') IS NOT NULL) ";
5648         return $clause;
5649
5650       case 'RLIKE':
5651         return " {$clause} BINARY '{$value}' ";
5652
5653       case 'IN':
5654       case 'NOT IN':
5655         // I feel like this would be escaped properly if passed through $queryString = CRM_Core_DAO::createSqlFilter.
5656         if (!empty($value) && (!is_array($value) || !array_key_exists($op, $value))) {
5657           $value = [$op => (array) $value];
5658         }
5659
5660       default:
5661         if (empty($dataType) || $dataType == 'Date') {
5662           $dataType = 'String';
5663         }
5664         if (is_array($value)) {
5665           //this could have come from the api - as in the restWhere section we potentially use the api operator syntax which is becoming more
5666           // widely used and consistent across the codebase
5667           // adding this here won't accept the search functions which don't submit an array
5668           if (($queryString = CRM_Core_DAO::createSQLFilter($field, $value, $dataType)) != FALSE) {
5669
5670             return $queryString;
5671           }
5672           if (!empty($value[0]) && $op === 'BETWEEN') {
5673             CRM_Core_Error::deprecatedFunctionWarning('Fix search input params');
5674             if (($queryString = CRM_Core_DAO::createSQLFilter($field, [$op => $value], $dataType)) != FALSE) {
5675               return $queryString;
5676             }
5677           }
5678           throw new CRM_Core_Exception(ts('Failed to interpret input for search'));
5679         }
5680
5681         $value = CRM_Utils_Type::escape($value, $dataType);
5682         // if we don't have a dataType we should assume
5683         if ($dataType == 'String' || $dataType == 'Text') {
5684           $value = "'" . $value . "'";
5685         }
5686         return "$clause $value";
5687     }
5688   }
5689
5690   /**
5691    * @param bool $reset
5692    *
5693    * @return array
5694    */
5695   public function openedSearchPanes($reset = FALSE) {
5696     if (!$reset || empty($this->_whereTables)) {
5697       return self::$_openedPanes;
5698     }
5699
5700     // pane name to table mapper
5701     $panesMapper = [
5702       ts('Contributions') => 'civicrm_contribution',
5703       ts('Memberships') => 'civicrm_membership',
5704       ts('Events') => 'civicrm_participant',
5705       ts('Relationships') => 'civicrm_relationship',
5706       ts('Activities') => 'civicrm_activity',
5707       ts('Pledges') => 'civicrm_pledge',
5708       ts('Cases') => 'civicrm_case',
5709       ts('Grants') => 'civicrm_grant',
5710       ts('Address Fields') => 'civicrm_address',
5711       ts('Notes') => 'civicrm_note',
5712       ts('Change Log') => 'civicrm_log',
5713       ts('Mailings') => 'civicrm_mailing',
5714     ];
5715     CRM_Contact_BAO_Query_Hook::singleton()->getPanesMapper($panesMapper);
5716
5717     foreach (array_keys($this->_whereTables) as $table) {
5718       if ($panName = array_search($table, $panesMapper)) {
5719         self::$_openedPanes[$panName] = TRUE;
5720       }
5721     }
5722
5723     return self::$_openedPanes;
5724   }
5725
5726   /**
5727    * @param $operator
5728    */
5729   public function setOperator($operator) {
5730     $validOperators = ['AND', 'OR'];
5731     if (!in_array($operator, $validOperators)) {
5732       $operator = 'AND';
5733     }
5734     $this->_operator = $operator;
5735   }
5736
5737   /**
5738    * @return string
5739    */
5740   public function getOperator() {
5741     return $this->_operator;
5742   }
5743
5744   /**
5745    * @param $from
5746    * @param $where
5747    * @param $having
5748    */
5749   public function filterRelatedContacts(&$from, &$where, &$having) {
5750     if (!isset(Civi::$statics[__CLASS__]['related_contacts_filter'])) {
5751       Civi::$statics[__CLASS__]['related_contacts_filter'] = [];
5752     }
5753     $_rTempCache =& Civi::$statics[__CLASS__]['related_contacts_filter'];
5754     // since there only can be one instance of this filter in every query
5755     // skip if filter has already applied
5756     foreach ($_rTempCache as $acache) {
5757       foreach ($acache['queries'] as $aqcache) {
5758         if (strpos($from, $aqcache['from']) !== FALSE) {
5759           $having = NULL;
5760           return;
5761         }
5762       }
5763     }
5764     $arg_sig = sha1("$from $where $having");
5765     if (isset($_rTempCache[$arg_sig])) {
5766       $cache = $_rTempCache[$arg_sig];
5767     }
5768     else {
5769       // create temp table with contact ids
5770
5771       $tableName = CRM_Utils_SQL_TempTable::build()->createWithColumns('contact_id int primary key')->setMemory(TRUE)->getName();
5772
5773       $sql = "
5774 REPLACE INTO $tableName ( contact_id )
5775 SELECT contact_a.id
5776        $from
5777        $where
5778        $having
5779 ";
5780       CRM_Core_DAO::executeQuery($sql);
5781
5782       $cache = ['tableName' => $tableName, 'queries' => []];
5783       $_rTempCache[$arg_sig] = $cache;
5784     }
5785     // upsert the query depending on relationship type
5786     if (isset($cache['queries'][$this->_displayRelationshipType])) {
5787       $qcache = $cache['queries'][$this->_displayRelationshipType];
5788     }
5789     else {
5790       $tableName = $cache['tableName'];
5791       $qcache = [
5792         "from" => "",
5793         "where" => "",
5794       ];
5795       $rTypes = CRM_Core_PseudoConstant::relationshipType();
5796       if (is_numeric($this->_displayRelationshipType)) {
5797         $relationshipTypeLabel = $rTypes[$this->_displayRelationshipType]['label_a_b'];
5798         $qcache['from'] = "
5799 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id OR displayRelType.contact_id_b = contact_a.id )
5800 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a OR transform_temp.contact_id = displayRelType.contact_id_b )
5801 ";
5802         $qcache['where'] = "
5803 WHERE displayRelType.relationship_type_id = {$this->_displayRelationshipType}
5804 AND   displayRelType.is_active = 1
5805 ";
5806       }
5807       else {
5808         list($relType, $dirOne, $dirTwo) = explode('_', $this->_displayRelationshipType);
5809         if ($dirOne == 'a') {
5810           $relationshipTypeLabel = $rTypes[$relType]['label_a_b'];
5811           $qcache['from'] .= "
5812 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id )
5813 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_b )
5814 ";
5815         }
5816         else {
5817           $relationshipTypeLabel = $rTypes[$relType]['label_b_a'];
5818           $qcache['from'] .= "
5819 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_b = contact_a.id )
5820 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a )
5821 ";
5822         }
5823         $qcache['where'] = "
5824 WHERE displayRelType.relationship_type_id = $relType
5825 AND   displayRelType.is_active = 1
5826 ";
5827       }
5828       $qcache['relTypeLabel'] = $relationshipTypeLabel;
5829       $_rTempCache[$arg_sig]['queries'][$this->_displayRelationshipType] = $qcache;
5830     }
5831     $qillMessage = ts('Contacts with a Relationship Type of: ');
5832     $iqill = $qillMessage . "'" . $qcache['relTypeLabel'] . "'";
5833     if (!is_array($this->_qill[0]) || !in_array($iqill, $this->_qill[0])) {
5834       $this->_qill[0][] = $iqill;
5835     }
5836     if (strpos($from, $qcache['from']) === FALSE) {
5837       // lets replace all the INNER JOIN's in the $from so we dont exclude other data
5838       // this happens when we have an event_type in the quert (CRM-7969)
5839       $from = str_replace("INNER JOIN", "LEFT JOIN", $from);
5840       $from .= $qcache['from'];
5841       $where = $qcache['where'];
5842       if (!empty($this->_permissionWhereClause)) {
5843         $where .= "AND $this->_permissionWhereClause";
5844       }
5845     }
5846
5847     $having = NULL;
5848   }
5849
5850   /**
5851    * See CRM-19811 for why this is database hurty without apparent benefit.
5852    *
5853    * @param $op
5854    *
5855    * @return bool
5856    */
5857   public static function caseImportant($op) {
5858     return in_array($op, ['LIKE', 'IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY']) ? FALSE : TRUE;
5859   }
5860
5861   /**
5862    * @param $returnProperties
5863    * @param $prefix
5864    *
5865    * @return bool
5866    */
5867   public static function componentPresent(&$returnProperties, $prefix) {
5868     foreach ($returnProperties as $name => $dontCare) {
5869       if (substr($name, 0, strlen($prefix)) == $prefix) {
5870         return TRUE;
5871       }
5872     }
5873     return FALSE;
5874   }
5875
5876   /**
5877    * Builds the necessary structures for all fields that are similar to option value look-ups.
5878    *
5879    * @param string $name
5880    *   the name of the field.
5881    * @param string $op
5882    *   the sql operator, this function should handle ALL SQL operators.
5883    * @param string $value
5884    *   depends on the operator and who's calling the query builder.
5885    * @param int $grouping
5886    *   the index where to place the where clause.
5887    * @param string $daoName
5888    *   DAO Name.
5889    * @param array $field
5890    *   an array that contains various properties of the field identified by $name.
5891    * @param string $label
5892    *   The label for this field element.
5893    * @param string $dataType
5894    *   The data type for this element.
5895    * @param bool $useIDsOnly
5896    *
5897    * @throws \CRM_Core_Exception
5898    */
5899   public function optionValueQuery(
5900     $name,
5901     $op,
5902     $value,
5903     $grouping,
5904     $daoName = NULL,
5905     $field,
5906     $label,
5907     $dataType = 'String',
5908     $useIDsOnly = FALSE
5909   ) {
5910
5911     $pseudoFields = [
5912       'email_greeting',
5913       'postal_greeting',
5914       'addressee',
5915       'gender_id',
5916       'prefix_id',
5917       'suffix_id',
5918       'communication_style_id',
5919     ];
5920
5921     if ($useIDsOnly) {
5922       list($tableName, $fieldName) = explode('.', $field['where'], 2);
5923       if ($tableName == 'civicrm_contact') {
5924         $wc = "contact_a.$fieldName";
5925       }
5926       else {
5927         // Special handling for on_hold, so that we actually use the 'where'
5928         // property in order to limit the query by the on_hold status of the email,
5929         // instead of using email.id which would be nonsensical.
5930         if ($field['name'] == 'on_hold') {
5931           $wc = "{$field['where']}";
5932         }
5933         else {
5934           $wc = "$tableName.id";
5935         }
5936       }
5937     }
5938     else {
5939       CRM_Core_Error::deprecatedFunctionWarning('pass $ids to this method');
5940       $wc = "{$field['where']}";
5941     }
5942     if (in_array($name, $pseudoFields)) {
5943       if (!in_array($name, ['gender_id', 'prefix_id', 'suffix_id', 'communication_style_id'])) {
5944         $wc = "contact_a.{$name}_id";
5945       }
5946       $dataType = 'Positive';
5947       $value = (!$value) ? 0 : $value;
5948     }
5949     if ($name == "world_region") {
5950       $field['name'] = $name;
5951     }
5952
5953     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue($daoName, $field['name'], $value, $op);
5954     $this->_qill[$grouping][] = ts("%1 %2 %3", [1 => $label, 2 => $qillop, 3 => $qillVal]);
5955     $this->_where[$grouping][] = self::buildClause($wc, $op, $value, $dataType);
5956   }
5957
5958   /**
5959    * Check and explode a user defined numeric string into an array
5960    * this was the protocol used by search builder in the old old days before we had
5961    * super nice js widgets to do the hard work
5962    *
5963    * @param string $string
5964    * @param string $dataType
5965    *   The dataType we should check for the values, default integer.
5966    *
5967    * @return bool|array
5968    *   false if string does not match the pattern
5969    *   array of numeric values if string does match the pattern
5970    */
5971   public static function parseSearchBuilderString($string, $dataType = 'Integer') {
5972     $string = trim($string);
5973     if (substr($string, 0, 1) != '(' || substr($string, -1, 1) != ')') {
5974       return FALSE;
5975     }
5976
5977     $string = substr($string, 1, -1);
5978     $values = explode(',', $string);
5979     if (empty($values)) {
5980       return FALSE;
5981     }
5982
5983     $returnValues = [];
5984     foreach ($values as $v) {
5985       if ($dataType == 'Integer' && !is_numeric($v)) {
5986         return FALSE;
5987       }
5988       elseif ($dataType == 'String' && !is_string($v)) {
5989         return FALSE;
5990       }
5991       $returnValues[] = trim($v);
5992     }
5993
5994     if (empty($returnValues)) {
5995       return FALSE;
5996     }
5997
5998     return $returnValues;
5999   }
6000
6001   /**
6002    * Convert the pseudo constants id's to their names
6003    *
6004    * @param CRM_Core_DAO $dao
6005    * @param bool $return
6006    * @param bool $usedForAPI
6007    *
6008    * @return array|NULL
6009    */
6010   public function convertToPseudoNames(&$dao, $return = FALSE, $usedForAPI = FALSE) {
6011     if (empty($this->_pseudoConstantsSelect)) {
6012       return NULL;
6013     }
6014     $values = [];
6015     foreach ($this->_pseudoConstantsSelect as $key => $value) {
6016       if (!empty($this->_pseudoConstantsSelect[$key]['sorting'])) {
6017         continue;
6018       }
6019
6020       if (is_object($dao) && property_exists($dao, $value['idCol'])) {
6021         $val = $dao->{$value['idCol']};
6022         if ($key == 'groups') {
6023           $dao->groups = $this->convertGroupIDStringToLabelString($dao, $val);
6024           continue;
6025         }
6026
6027         if (CRM_Utils_System::isNull($val)) {
6028           $dao->$key = NULL;
6029         }
6030         elseif (!empty($value['pseudoconstant'])) {
6031           // If pseudoconstant is set that is kind of defacto for 'we have a bit more info about this'
6032           // and we can use the metadata to figure it out.
6033           // ideally this bit of IF will absorb & replace all the rest in time as we move to
6034           // more metadata based choices.
6035           if (strpos($val, CRM_Core_DAO::VALUE_SEPARATOR) !== FALSE) {
6036             $dbValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, trim($val, CRM_Core_DAO::VALUE_SEPARATOR));
6037             foreach ($dbValues as $pseudoValue) {
6038               $convertedValues[] = CRM_Core_PseudoConstant::getLabel($value['bao'], $value['idCol'], $pseudoValue);
6039             }
6040
6041             $dao->$key = ($usedForAPI) ? $convertedValues : implode(', ', $convertedValues);
6042             $realFieldName = CRM_Utils_Array::value('field_name', $this->_pseudoConstantsSelect[$key]);
6043             if ($usedForAPI && $realFieldName) {
6044               // normally we would see 2 fields returned for pseudoConstants. An exception is
6045               // preferred_communication_method where there is no id-variant.
6046               // For the api we prioritise getting the real data returned.
6047               // over the resolved version
6048               $dao->$realFieldName = $dbValues;
6049             }
6050
6051           }
6052           else {
6053             // This is basically the same as the default but since we have the bao we can use
6054             // a cached function.
6055             $dao->$key = CRM_Core_PseudoConstant::getLabel($value['bao'], $value['idCol'], $val);
6056           }
6057         }
6058         elseif ($baoName = CRM_Utils_Array::value('bao', $value, NULL)) {
6059           //preserve id value
6060           $idColumn = "{$key}_id";
6061           $dao->$idColumn = $val;
6062
6063           if ($key == 'state_province_name') {
6064             $dao->{$value['pseudoField']} = $dao->$key = CRM_Core_PseudoConstant::stateProvince($val);
6065           }
6066           else {
6067             $dao->{$value['pseudoField']} = $dao->$key = CRM_Core_PseudoConstant::getLabel($baoName, $value['pseudoField'], $val);
6068           }
6069         }
6070         elseif ($value['pseudoField'] == 'state_province_abbreviation') {
6071           $dao->$key = CRM_Core_PseudoConstant::stateProvinceAbbreviation($val);
6072         }
6073         // @todo handle this in the section above for pseudoconstants.
6074         elseif (in_array($value['pseudoField'], ['participant_role_id', 'participant_role'])) {
6075           // @todo define bao on this & merge into the above condition.
6076           $viewValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $val);
6077
6078           if ($value['pseudoField'] == 'participant_role') {
6079             $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', 'role_id');
6080             foreach ($viewValues as $k => $v) {
6081               $viewValues[$k] = $pseudoOptions[$v];
6082             }
6083           }
6084           $dao->$key = ($usedForAPI && count($viewValues) > 1) ? $viewValues : implode(', ', $viewValues);
6085         }
6086         else {
6087           $labels = CRM_Core_OptionGroup::values($value['pseudoField']);
6088           $dao->$key = $labels[$val];
6089         }
6090
6091         // return converted values in array format
6092         if ($return) {
6093           if (strpos($key, '-') !== FALSE) {
6094             $keyVal = explode('-', $key);
6095             $current = &$values;
6096             $lastElement = array_pop($keyVal);
6097             foreach ($keyVal as $v) {
6098               if (!array_key_exists($v, $current)) {
6099                 $current[$v] = [];
6100               }
6101               $current = &$current[$v];
6102             }
6103             $current[$lastElement] = $dao->$key;
6104           }
6105           else {
6106             $values[$key] = $dao->$key;
6107           }
6108         }
6109       }
6110     }
6111     if (!$usedForAPI) {
6112       foreach ($this->legacyHackedFields as $realField => $labelField) {
6113         // This is a temporary routine for handling these fields while
6114         // we figure out how to handled them based on metadata in
6115         /// export and search builder. CRM-19815, CRM-19830.
6116         if (isset($dao->$realField) && is_numeric($dao->$realField) && isset($dao->$labelField)) {
6117           $dao->$realField = $dao->$labelField;
6118         }
6119       }
6120     }
6121     return $values;
6122   }
6123
6124   /**
6125    * Include pseudo fields LEFT JOIN.
6126    * @param string|array $sort can be a object or string
6127    *
6128    * @return array|NULL
6129    */
6130   public function includePseudoFieldsJoin($sort) {
6131     if (!$sort || empty($this->_pseudoConstantsSelect)) {
6132       return NULL;
6133     }
6134     $sort = is_string($sort) ? $sort : $sort->orderBy();
6135     $present = [];
6136
6137     foreach ($this->_pseudoConstantsSelect as $name => $value) {
6138       if (!empty($value['table'])) {
6139         $regex = "/({$value['table']}\.|{$name})/";
6140         if (preg_match($regex, $sort)) {
6141           $this->_elemnt[$value['element']] = 1;
6142           $this->_select[$value['element']] = $value['select'];
6143           $this->_pseudoConstantsSelect[$name]['sorting'] = 1;
6144           $present[$value['table']] = $value['join'];
6145         }
6146       }
6147     }
6148     $presentSimpleFrom = $present;
6149
6150     if (array_key_exists('civicrm_worldregion', $this->_whereTables) &&
6151       array_key_exists('civicrm_country', $presentSimpleFrom)
6152     ) {
6153       unset($presentSimpleFrom['civicrm_country']);
6154     }
6155     if (array_key_exists('civicrm_worldregion', $this->_tables) &&
6156       array_key_exists('civicrm_country', $present)
6157     ) {
6158       unset($present['civicrm_country']);
6159     }
6160
6161     $presentClause = $presentSimpleFromClause = NULL;
6162     if (!empty($present)) {
6163       $presentClause = implode(' ', $present);
6164     }
6165     if (!empty($presentSimpleFrom)) {
6166       $presentSimpleFromClause = implode(' ', $presentSimpleFrom);
6167     }
6168
6169     $this->_fromClause = $this->_fromClause . $presentClause;
6170     $this->_simpleFromClause = $this->_simpleFromClause . $presentSimpleFromClause;
6171
6172     return [$presentClause, $presentSimpleFromClause];
6173   }
6174
6175   /**
6176    * Build qill for field.
6177    *
6178    * Qill refers to the query detail visible on the UI.
6179    *
6180    * @param string $daoName
6181    * @param string $fieldName
6182    * @param mixed $fieldValue
6183    * @param string $op
6184    * @param array $pseudoExtraParam
6185    * @param int $type
6186    *   Type of the field per CRM_Utils_Type
6187    *
6188    * @return array
6189    */
6190   public static function buildQillForFieldValue(
6191     $daoName,
6192     $fieldName,
6193     $fieldValue,
6194     $op,
6195     $pseudoExtraParam = [],
6196     $type = CRM_Utils_Type::T_STRING
6197   ) {
6198     $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
6199
6200     //API usually have fieldValue format as array(operator => array(values)),
6201     //so we need to separate operator out of fieldValue param
6202     if (is_array($fieldValue) && in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
6203       $op = key($fieldValue);
6204       $fieldValue = $fieldValue[$op];
6205     }
6206
6207     // if Operator chosen is NULL/EMPTY then
6208     if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) {
6209       return [CRM_Utils_Array::value($op, $qillOperators, $op), ''];
6210     }
6211
6212     if ($fieldName == 'activity_type_id') {
6213       $pseudoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
6214     }
6215     elseif ($fieldName == 'country_id') {
6216       $pseudoOptions = CRM_Core_PseudoConstant::country();
6217     }
6218     elseif ($fieldName == 'county_id') {
6219       $pseudoOptions = CRM_Core_PseudoConstant::county();
6220     }
6221     elseif ($fieldName == 'world_region') {
6222       $pseudoOptions = CRM_Core_PseudoConstant::worldRegion();
6223     }
6224     elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
6225       $checkPermission = CRM_Utils_Array::value('check_permission', $pseudoExtraParam, TRUE);
6226       $pseudoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, $checkPermission, TRUE);
6227     }
6228     elseif ($fieldName == 'contribution_product_id') {
6229       $pseudoOptions = CRM_Contribute_PseudoConstant::products();
6230     }
6231     elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') {
6232       $pseudoOptions = CRM_Core_PseudoConstant::group();
6233     }
6234     elseif ($daoName == 'CRM_Batch_BAO_EntityBatch' && $fieldName == 'batch_id') {
6235       $pseudoOptions = CRM_Contribute_PseudoConstant::batch();
6236     }
6237     elseif ($daoName) {
6238       $pseudoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseudoExtraParam);
6239     }
6240
6241     if (is_array($fieldValue)) {
6242       $qillString = [];
6243       if (!empty($pseudoOptions)) {
6244         foreach ((array) $fieldValue as $val) {
6245           $qillString[] = CRM_Utils_Array::value($val, $pseudoOptions, $val);
6246         }
6247         $fieldValue = implode(', ', $qillString);
6248       }
6249       else {
6250         if ($type == CRM_Utils_Type::T_DATE) {
6251           foreach ($fieldValue as $index => $value) {
6252             $fieldValue[$index] = CRM_Utils_Date::customFormat($value);
6253           }
6254         }
6255         $separator = ', ';
6256         // @todo - this is a bit specific (one operator).
6257         // However it is covered by a unit test so can be altered later with
6258         // some confidence.
6259         if ($op == 'BETWEEN') {
6260           $separator = ' AND ';
6261         }
6262         $fieldValue = implode($separator, $fieldValue);
6263       }
6264     }
6265     elseif (!empty($pseudoOptions) && array_key_exists($fieldValue, $pseudoOptions)) {
6266       $fieldValue = $pseudoOptions[$fieldValue];
6267     }
6268     elseif ($type === CRM_Utils_Type::T_DATE) {
6269       $fieldValue = CRM_Utils_Date::customFormat($fieldValue);
6270     }
6271
6272     return [CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue];
6273   }
6274
6275   /**
6276    * Get the qill (search description for field) for the specified field.
6277    *
6278    * @param string $daoName
6279    * @param string $name
6280    * @param string $value
6281    * @param string|array $op
6282    * @param string $label
6283    *
6284    * @return string
6285    */
6286   public static function getQillValue($daoName, string $name, $value, $op, string $label) {
6287     list($op, $value) = self::buildQillForFieldValue($daoName, $name, $value, $op);
6288     return ts('%1 %2 %3', [1 => $label, 2 => $op, 3 => $value]);
6289   }
6290
6291   /**
6292    * Alter value to reflect wildcard settings.
6293    *
6294    * The form will have tried to guess whether this is a good field to wildcard but there is
6295    * also a site-wide setting that specifies whether it is OK to append the wild card to the beginning
6296    * or only the end of the string
6297    *
6298    * @param bool $wildcard
6299    *   This is a bool made on an assessment 'elsewhere' on whether this is a good field to wildcard.
6300    * @param string $op
6301    *   Generally '=' or 'LIKE'.
6302    * @param string $value
6303    *   The search string.
6304    *
6305    * @return string
6306    */
6307   public static function getWildCardedValue($wildcard, $op, $value) {
6308     if ($wildcard && $op == 'LIKE') {
6309       if (CRM_Core_Config::singleton()->includeWildCardInName && (substr($value, 0, 1) != '%')) {
6310         return "%$value%";
6311       }
6312       else {
6313         return "$value%";
6314       }
6315     }
6316     else {
6317       return "$value";
6318     }
6319   }
6320
6321   /**
6322    * Process special fields of Search Form in OK (Operator in Key) format
6323    *
6324    * @param array $formValues
6325    * @param array $specialFields
6326    *    Special params to be processed
6327    * @param array $changeNames
6328    *   Array of fields whose name should be changed
6329    */
6330   public static function processSpecialFormValue(&$formValues, $specialFields, $changeNames = []) {
6331     // Array of special fields whose value are considered only for NULL or EMPTY operators
6332     $nullableFields = ['contribution_batch_id'];
6333
6334     foreach ($specialFields as $element) {
6335       $value = CRM_Utils_Array::value($element, $formValues);
6336       if ($value) {
6337         if (is_array($value)) {
6338           if (in_array($element, array_keys($changeNames))) {
6339             unset($formValues[$element]);
6340             $element = $changeNames[$element];
6341           }
6342           $formValues[$element] = ['IN' => $value];
6343         }
6344         elseif (in_array($value, ['IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'])) {
6345           $formValues[$element] = [$value => 1];
6346         }
6347         elseif (!in_array($element, $nullableFields)) {
6348           // if wildcard is already present return searchString as it is OR append and/or prepend with wildcard
6349           $isWilcard = strstr($value, '%') ? FALSE : CRM_Core_Config::singleton()->includeWildCardInName;
6350           $formValues[$element] = ['LIKE' => self::getWildCardedValue($isWilcard, 'LIKE', $value)];
6351         }
6352       }
6353     }
6354   }
6355
6356   /**
6357    * Parse and assimilate the various sort options.
6358    *
6359    * Side-effect: if sorting on a common column from a related table (`city`, `postal_code`,
6360    * `email`), the related table may be joined automatically.
6361    *
6362    * At time of writing, this code is deeply flawed and should be rewritten. For the moment,
6363    * it's been extracted to a standalone function.
6364    *
6365    * @param string|CRM_Utils_Sort $sort
6366    *   The order by string.
6367    * @param null $sortOrder
6368    *   Who knows? Hu knows. He who knows Hu knows who.
6369    *
6370    * @return string
6371    *   list(string $orderByClause, string $additionalFromClause).
6372    * @throws \CRM_Core_Exception
6373    */
6374   protected function prepareOrderBy($sort, $sortOrder) {
6375     $orderByArray = [];
6376     $orderBy = '';
6377
6378     if (CRM_Core_Config::singleton()->includeOrderByClause ||
6379       isset($this->_distinctComponentClause)
6380     ) {
6381       if ($sort) {
6382         if (is_string($sort)) {
6383           $orderBy = $sort;
6384         }
6385         else {
6386           $orderBy = trim($sort->orderBy());
6387         }
6388         // Deliberately remove the backticks again, as they mess up the evil
6389         // string munging below. This balanced by re-escaping before use.
6390         $orderBy = str_replace('`', '', $orderBy);
6391
6392         if (!empty($orderBy)) {
6393           // this is special case while searching for
6394           // change log CRM-1718
6395           if (preg_match('/sort_name/i', $orderBy)) {
6396             $orderBy = str_replace('sort_name', 'contact_a.sort_name', $orderBy);
6397           }
6398
6399           if ($sortOrder) {
6400             $orderBy .= " $sortOrder";
6401           }
6402
6403           // always add contact_a.id to the ORDER clause
6404           // so the order is deterministic
6405           if (strpos('contact_a.id', $orderBy) === FALSE) {
6406             $orderBy .= ", contact_a.id";
6407           }
6408         }
6409       }
6410       else {
6411         $orderBy = " contact_a.sort_name ASC, contact_a.id";
6412       }
6413     }
6414     if (!$orderBy) {
6415       return NULL;
6416     }
6417     // Remove this here & add it at the end for simplicity.
6418     $order = trim($orderBy);
6419     $orderByArray = explode(',', $order);
6420
6421     foreach ($orderByArray as $orderByClause) {
6422       $orderByClauseParts = explode(' ', trim($orderByClause));
6423       $field = $orderByClauseParts[0];
6424       $direction = isset($orderByClauseParts[1]) ? $orderByClauseParts[1] : 'asc';
6425       $fieldSpec = $this->getMetadataForRealField($field);
6426
6427       // This is a hacky add-in for primary address joins. Feel free to iterate as it is unit tested.
6428       // @todo much more cleanup on location handling in addHierarchical elements. Potentially
6429       // add keys to $this->fields to represent the actual keys for locations.
6430       if (empty($fieldSpec) && substr($field, 0, 2) === '1-') {
6431         $fieldSpec = $this->getMetadataForField(substr($field, 2));
6432         $this->addAddressTable('1-' . str_replace('civicrm_', '', $fieldSpec['table_name']), 'is_primary = 1');
6433       }
6434
6435       if ($this->_returnProperties === []) {
6436         if (!empty($fieldSpec['table_name']) && !isset($this->_tables[$fieldSpec['table_name']])) {
6437           $this->_tables[$fieldSpec['table_name']] = 1;
6438           $order = $fieldSpec['where'] . ' ' . $direction;
6439         }
6440
6441       }
6442       $cfID = CRM_Core_BAO_CustomField::getKeyID($field);
6443       // add to cfIDs array if not present
6444       if (!empty($cfID) && !array_key_exists($cfID, $this->_cfIDs)) {
6445         $this->_cfIDs[$cfID] = [];
6446         $this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE, $this->_locationSpecificCustomFields);
6447         $this->_customQuery->query();
6448         $this->_select = array_merge($this->_select, $this->_customQuery->_select);
6449         $this->_tables = array_merge($this->_tables, $this->_customQuery->_tables);
6450       }
6451
6452       // By replacing the join to the option value table with the mysql construct
6453       // ORDER BY field('contribution_status_id', 2,1,4)
6454       // we can remove a join. In the case of the option value join it is
6455       /// a join known to cause slow queries.
6456       // @todo cover other pseudoconstant types. Limited to option group ones  & Foreign keys
6457       // matching an id+name parrern in the
6458       // first instance for scope reasons. They require slightly different handling as the column (label)
6459       // is not declared for them.
6460       // @todo so far only integer fields are being handled. If we add string fields we need to look at
6461       // escaping.
6462       $pseudoConstantMetadata = CRM_Utils_Array::value('pseudoconstant', $fieldSpec, FALSE);
6463       if (!empty($pseudoConstantMetadata)
6464       ) {
6465         if (!empty($pseudoConstantMetadata['optionGroupName'])
6466           || $this->isPseudoFieldAnFK($fieldSpec)
6467         ) {
6468           // dev/core#1305 @todo this is not the right thing to do but for now avoid fatal error
6469           if (empty($fieldSpec['bao'])) {
6470             continue;
6471           }
6472           $sortedOptions = $fieldSpec['bao']::buildOptions($fieldSpec['name'], NULL, [
6473             'orderColumn' => CRM_Utils_Array::value('labelColumn', $pseudoConstantMetadata, 'label'),
6474           ]);
6475           $fieldIDsInOrder = implode(',', array_keys($sortedOptions));
6476           // Pretty sure this validation ALSO happens in the order clause & this can't be reached but...
6477           // this might give some early warning.
6478           CRM_Utils_Type::validate($fieldIDsInOrder, 'CommaSeparatedIntegers');
6479           $order = str_replace("$field", "field({$fieldSpec['name']},$fieldIDsInOrder)", $order);
6480         }
6481         //CRM-12565 add "`" around $field if it is a pseudo constant
6482         // This appears to be for 'special' fields like locations with appended numbers or hyphens .. maybe.
6483         if (!empty($pseudoConstantMetadata['element']) && $pseudoConstantMetadata['element'] == $field) {
6484           $order = str_replace($field, "`{$field}`", $order);
6485         }
6486       }
6487     }
6488
6489     $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
6490     $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
6491
6492     // The above code relies on crazy brittle string manipulation of a peculiarly-encoded ORDER BY
6493     // clause. But this magic helper which forgivingly reescapes ORDER BY.
6494     if ($order) {
6495       $order = CRM_Utils_Type::escape($order, 'MysqlOrderBy');
6496       return ' ORDER BY ' . $order;
6497     }
6498   }
6499
6500   /**
6501    * Convert a string of group IDs to a string of group labels.
6502    *
6503    * The original string may include duplicates and groups the user does not have
6504    * permission to see.
6505    *
6506    * @param CRM_Core_DAO $dao
6507    * @param string $val
6508    *
6509    * @return string
6510    */
6511   public function convertGroupIDStringToLabelString(&$dao, $val) {
6512     $groupIDs = explode(',', $val);
6513     // Note that groups that the user does not have permission to will be excluded (good).
6514     $groups = array_intersect_key(CRM_Core_PseudoConstant::group(), array_flip($groupIDs));
6515     return implode(', ', $groups);
6516
6517   }
6518
6519   /**
6520    * Set the qill and where properties for a field.
6521    *
6522    * This function is intended as a short-term function to encourage refactoring
6523    * & re-use - but really we should just have less special-casing.
6524    *
6525    * @param string $name
6526    * @param string $op
6527    * @param string|array $value
6528    * @param string $grouping
6529    * @param array $field
6530    *
6531    * @throws \CRM_Core_Exception
6532    */
6533   public function setQillAndWhere($name, $op, $value, $grouping, $field) {
6534     $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
6535     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
6536     $this->_qill[$grouping][] = ts("%1 %2 %3", [
6537       1 => $field['title'],
6538       2 => $qillop,
6539       3 => $qillVal,
6540     ]);
6541   }
6542
6543   /**
6544    * Has the pseudoconstant of the field been requested.
6545    *
6546    * For example if the field is payment_instrument_id then it
6547    * has been requested if either payment_instrument_id or payment_instrument
6548    * have been requested. Payment_instrument is the option groun name field value.
6549    *
6550    * @param array $field
6551    * @param string $fieldName
6552    *   The unique name of the field - ie. the one it will be aliased to in the query.
6553    *
6554    * @return bool
6555    */
6556   private function pseudoConstantNameIsInReturnProperties($field, $fieldName = NULL) {
6557     $realField = $this->getMetadataForRealField($fieldName);
6558     if (!isset($realField['pseudoconstant'])) {
6559       return FALSE;
6560     }
6561     $pseudoConstant = $realField['pseudoconstant'];
6562     if (empty($pseudoConstant['optionGroupName']) &&
6563       CRM_Utils_Array::value('labelColumn', $pseudoConstant) !== 'name') {
6564       // We are increasing our pseudoconstant handling - but still very cautiously,
6565       // hence the check for labelColumn === name
6566       return FALSE;
6567     }
6568
6569     if (!empty($pseudoConstant['optionGroupName']) && CRM_Utils_Array::value($pseudoConstant['optionGroupName'], $this->_returnProperties)) {
6570       return TRUE;
6571     }
6572     if (CRM_Utils_Array::value($fieldName, $this->_returnProperties)) {
6573       return TRUE;
6574     }
6575     // Is this still required - the above goes off the unique name. Test with things like
6576     // communication_preferences & prefix_id.
6577     if (CRM_Utils_Array::value($field['name'], $this->_returnProperties)) {
6578       return TRUE;
6579     }
6580     return FALSE;
6581   }
6582
6583   /**
6584    * Get Select Clause.
6585    *
6586    * @return string
6587    */
6588   public function getSelect() {
6589     $select = 'SELECT ';
6590     if (isset($this->_distinctComponentClause)) {
6591       $select .= "{$this->_distinctComponentClause}, ";
6592     }
6593     $select .= implode(', ', $this->_select);
6594     return $select;
6595   }
6596
6597   /**
6598    * Add basic statistics to the summary.
6599    *
6600    * @param array $summary
6601    * @param string $where
6602    * @param string $from
6603    *
6604    * @return array
6605    * @throws \CRM_Core_Exception
6606    */
6607   protected function addBasicStatsToSummary(&$summary, $where, $from) {
6608     $summary['total']['count'] = 0;
6609     $summary['total']['amount'] = $summary['total']['avg'] = [];
6610
6611     $query = "
6612       SELECT COUNT( conts.total_amount ) as total_count,
6613         SUM(   conts.total_amount ) as total_amount,
6614         AVG(   conts.total_amount ) as total_avg,
6615         conts.currency              as currency
6616       FROM (
6617         SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
6618         civicrm_contribution.currency
6619         $from
6620         $where AND civicrm_contribution.contribution_status_id = 1
6621       GROUP BY civicrm_contribution.id
6622     ) as conts
6623     GROUP BY currency";
6624
6625     $dao = CRM_Core_DAO::executeQuery($query);
6626
6627     while ($dao->fetch()) {
6628       $summary['total']['count'] += $dao->total_count;
6629       $summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
6630       $summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
6631     }
6632
6633     if (!empty($summary['total']['amount'])) {
6634       $summary['total']['amount'] = implode(',&nbsp;', $summary['total']['amount']);
6635       $summary['total']['avg'] = implode(',&nbsp;', $summary['total']['avg']);
6636     }
6637     else {
6638       $summary['total']['amount'] = $summary['total']['avg'] = 0;
6639     }
6640     return $summary;
6641   }
6642
6643   /**
6644    * Add basic soft credit statistics to summary array.
6645    *
6646    * @param array $summary
6647    * @param string $where
6648    * @param string $from
6649    *
6650    * @throws \CRM_Core_Exception
6651    */
6652   protected function addBasicSoftCreditStatsToStats(&$summary, $where, $from) {
6653     $query = "
6654       SELECT COUNT( conts.total_amount ) as total_count,
6655       SUM(   conts.total_amount ) as total_amount,
6656       AVG(   conts.total_amount ) as total_avg,
6657       conts.currency as currency
6658       FROM (
6659         SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency
6660         $from
6661         $where AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution_soft.id IS NOT NULL
6662         GROUP BY civicrm_contribution_soft.id
6663       ) as conts
6664       GROUP BY currency";
6665
6666     $dao = CRM_Core_DAO::executeQuery($query);
6667     $summary['soft_credit']['count'] = 0;
6668     $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = [];
6669     while ($dao->fetch()) {
6670       $summary['soft_credit']['count'] += $dao->total_count;
6671       $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
6672       $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
6673     }
6674     if (!empty($summary['soft_credit']['amount'])) {
6675       $summary['soft_credit']['amount'] = implode(',&nbsp;', $summary['soft_credit']['amount']);
6676       $summary['soft_credit']['avg'] = implode(',&nbsp;', $summary['soft_credit']['avg']);
6677     }
6678     else {
6679       $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0;
6680     }
6681   }
6682
6683   /**
6684    * Add basic stats about cancelled contributions to the summary.
6685    *
6686    * @param array $summary
6687    * @param string $where
6688    * @param string $from
6689    *
6690    * @throws \CRM_Core_Exception
6691    */
6692   protected function addBasicCancelStatsToSummary(&$summary, $where, $from) {
6693     $query = "
6694       SELECT COUNT( conts.total_amount ) as cancel_count,
6695        SUM(   conts.total_amount ) as cancel_amount,
6696        AVG(   conts.total_amount ) as cancel_avg,
6697        conts.currency              as currency
6698         FROM (
6699       SELECT civicrm_contribution.total_amount, civicrm_contribution.currency
6700       $from
6701       $where  AND civicrm_contribution.cancel_date IS NOT NULL
6702       GROUP BY civicrm_contribution.id
6703     ) as conts
6704     GROUP BY currency";
6705
6706     $dao = CRM_Core_DAO::executeQuery($query);
6707
6708     if ($dao->N <= 1) {
6709       if ($dao->fetch()) {
6710         $summary['cancel']['count'] = $dao->cancel_count;
6711         $summary['cancel']['amount'] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
6712         $summary['cancel']['avg'] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
6713       }
6714     }
6715     else {
6716       $summary['cancel']['count'] = 0;
6717       $summary['cancel']['amount'] = $summary['cancel']['avg'] = [];
6718       while ($dao->fetch()) {
6719         $summary['cancel']['count'] += $dao->cancel_count;
6720         $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
6721         $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
6722       }
6723       $summary['cancel']['amount'] = implode(',&nbsp;', $summary['cancel']['amount']);
6724       $summary['cancel']['avg'] = implode(',&nbsp;', $summary['cancel']['avg']);
6725     }
6726   }
6727
6728   /**
6729    * Create the sql query for an contact search.
6730    *
6731    * @param int $offset
6732    *   The offset for the query.
6733    * @param int $rowCount
6734    *   The number of rows to return.
6735    * @param string|CRM_Utils_Sort $sort
6736    *   The order by string.
6737    * @param bool $count
6738    *   Is this a count only query ?.
6739    * @param bool $includeContactIds
6740    *   Should we include contact ids?.
6741    * @param bool $sortByChar
6742    *   If true returns the distinct array of first characters for search results.
6743    * @param bool $groupContacts
6744    *   If true, return only the contact ids.
6745    * @param string $additionalWhereClause
6746    *   If the caller wants to further restrict the search (used for components).
6747    * @param null $sortOrder
6748    * @param string $additionalFromClause
6749    *   Should be clause with proper joins, effective to reduce where clause load.
6750    *
6751    * @param bool $skipOrderAndLimit
6752    * @return string
6753    */
6754   public function getSearchSQL(
6755     $offset = 0, $rowCount = 0, $sort = NULL,
6756     $count = FALSE, $includeContactIds = FALSE,
6757     $sortByChar = FALSE, $groupContacts = FALSE,
6758     $additionalWhereClause = NULL, $sortOrder = NULL,
6759     $additionalFromClause = NULL, $skipOrderAndLimit = FALSE) {
6760
6761     $sqlParts = $this->getSearchSQLParts($offset, $rowCount, $sort, $count, $includeContactIds, $sortByChar, $groupContacts, $additionalWhereClause, $sortOrder, $additionalFromClause);
6762
6763     if ($sortByChar) {
6764       CRM_Core_Error::deprecatedFunctionWarning('sort by char is deprecated - use alphabetQuery method');
6765       $sqlParts['order_by'] = 'ORDER BY sort_name asc';
6766     }
6767
6768     if ($skipOrderAndLimit) {
6769       CRM_Core_Error::deprecatedFunctionWarning('skipOrderAndLimit is deprected - call getSearchSQLParts & construct it in the calling function');
6770       $query = "{$sqlParts['select']} {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['having']} {$sqlParts['group_by']}";
6771     }
6772     else {
6773       $query = "{$sqlParts['select']} {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['having']} {$sqlParts['group_by']} {$sqlParts['order_by']} {$sqlParts['limit']}";
6774     }
6775     return $query;
6776   }
6777
6778   /**
6779    * Get the component parts of the search query as an array.
6780    *
6781    * @param int $offset
6782    *   The offset for the query.
6783    * @param int $rowCount
6784    *   The number of rows to return.
6785    * @param string|CRM_Utils_Sort $sort
6786    *   The order by string.
6787    * @param bool $count
6788    *   Is this a count only query ?.
6789    * @param bool $includeContactIds
6790    *   Should we include contact ids?.
6791    * @param bool $sortByChar
6792    *   If true returns the distinct array of first characters for search results.
6793    * @param bool $groupContacts
6794    *   If true, return only the contact ids.
6795    * @param string $additionalWhereClause
6796    *   If the caller wants to further restrict the search (used for components).
6797    * @param null $sortOrder
6798    * @param string $additionalFromClause
6799    *   Should be clause with proper joins, effective to reduce where clause load.
6800    *
6801    * @return array
6802    * @throws \CRM_Core_Exception
6803    */
6804   public function getSearchSQLParts($offset = 0, $rowCount = 0, $sort = NULL,
6805     $count = FALSE, $includeContactIds = FALSE,
6806     $sortByChar = FALSE, $groupContacts = FALSE,
6807     $additionalWhereClause = NULL, $sortOrder = NULL,
6808     $additionalFromClause = NULL) {
6809     if ($includeContactIds) {
6810       $this->_includeContactIds = TRUE;
6811       $this->_whereClause = $this->whereClause();
6812     }
6813     $onlyDeleted = in_array([
6814       'deleted_contacts',
6815       '=',
6816       '1',
6817       '0',
6818       '0',
6819     ], $this->_params);
6820
6821     // if we’re explicitly looking for a certain contact’s contribs, events, etc.
6822     // and that contact happens to be deleted, set $onlyDeleted to true
6823     foreach ($this->_params as $values) {
6824       $name = CRM_Utils_Array::value(0, $values);
6825       $op = CRM_Utils_Array::value(1, $values);
6826       $value = CRM_Utils_Array::value(2, $values);
6827       if ($name == 'contact_id' and $op == '=') {
6828         if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) {
6829           $onlyDeleted = TRUE;
6830         }
6831         break;
6832       }
6833     }
6834
6835     // building the query string
6836     $groupBy = $groupByCols = NULL;
6837     if (!$count) {
6838       if (isset($this->_groupByComponentClause)) {
6839         $groupByCols = preg_replace('/^GROUP BY /', '', trim($this->_groupByComponentClause));
6840         $groupByCols = explode(', ', $groupByCols);
6841       }
6842       elseif ($this->_useGroupBy) {
6843         $groupByCols = ['contact_a.id'];
6844       }
6845     }
6846     if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) {
6847       $groupByCols = ['civicrm_activity.id'];
6848     }
6849     if (!empty($groupByCols)) {
6850       $groupBy = " GROUP BY " . implode(', ', $groupByCols);
6851     }
6852
6853     $order = $orderBy = '';
6854     if (!$count) {
6855       if (!$sortByChar) {
6856         $order = $this->prepareOrderBy($sort, $sortOrder);
6857       }
6858     }
6859     // Cases where we are disabling FGB (FULL_GROUP_BY_MODE):
6860     //   1. When GROUP BY columns are present then disable FGB otherwise it demands to add ORDER BY columns in GROUP BY and eventually in SELECT
6861     //     clause. This will impact the search query output.
6862     $disableFullGroupByMode = (!empty($groupBy) || $groupContacts);
6863
6864     if ($disableFullGroupByMode) {
6865       CRM_Core_DAO::disableFullGroupByMode();
6866     }
6867
6868     // CRM-15231
6869     $this->_sort = $sort;
6870
6871     //CRM-15967
6872     $this->includePseudoFieldsJoin($sort);
6873
6874     list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted);
6875
6876     if ($additionalWhereClause) {
6877       $where = $where . ' AND ' . $additionalWhereClause;
6878     }
6879
6880     //additional from clause should be w/ proper joins.
6881     if ($additionalFromClause) {
6882       $from .= "\n" . $additionalFromClause;
6883     }
6884
6885     // if we are doing a transform, do it here
6886     // use the $from, $where and $having to get the contact ID
6887     if ($this->_displayRelationshipType) {
6888       $this->filterRelatedContacts($from, $where, $having);
6889     }
6890     $limit = (!$count && $rowCount) ? " LIMIT " . CRM_Utils_Type::escape($offset, 'Int') . ", " . CRM_Utils_Type::escape($rowCount, 'Int') : '';
6891
6892     return [
6893       'select' => $select,
6894       'from' => $from,
6895       'where' => $where,
6896       'order_by' => $order,
6897       'group_by' => $groupBy,
6898       'having' => $having,
6899       'limit' => $limit,
6900     ];
6901   }
6902
6903   /**
6904    * Get the metadata for a given field.
6905    *
6906    * @param string $fieldName
6907    *
6908    * @return array
6909    */
6910   protected function getMetadataForField($fieldName) {
6911     if ($fieldName === 'contact_a.id') {
6912       // This seems to be the only anomaly.
6913       $fieldName = 'id';
6914     }
6915     $pseudoField = isset($this->_pseudoConstantsSelect[$fieldName]) ? $this->_pseudoConstantsSelect[$fieldName] : [];
6916     $field = isset($this->_fields[$fieldName]) ? $this->_fields[$fieldName] : $pseudoField;
6917     $field = array_merge($field, $pseudoField);
6918     if (!empty($field) && empty($field['name'])) {
6919       // standardising field formatting here - over time we can phase out variants.
6920       // all paths using this currently unit tested
6921       $field['name'] = CRM_Utils_Array::value('field_name', $field, CRM_Utils_Array::value('idCol', $field, $fieldName));
6922     }
6923     return $field;
6924   }
6925
6926   /**
6927    * Get the metadata for a given field, returning the 'real field' if it is a pseudofield.
6928    *
6929    * @param string $fieldName
6930    *
6931    * @return array
6932    */
6933   protected function getMetadataForRealField($fieldName) {
6934     $field = $this->getMetadataForField($fieldName);
6935     if (!empty($field['is_pseudofield_for'])) {
6936       $field = $this->getMetadataForField($field['is_pseudofield_for']);
6937       $field['pseudofield_name'] = $fieldName;
6938     }
6939     elseif (!empty($field['pseudoconstant'])) {
6940       if (!empty($field['pseudoconstant']['optionGroupName'])) {
6941         $field['pseudofield_name'] = $field['pseudoconstant']['optionGroupName'];
6942         if (empty($field['table_name'])) {
6943           if (!empty($field['where'])) {
6944             $field['table_name'] = explode('.', $field['where'])[0];
6945           }
6946           else {
6947             $field['table_name'] = 'civicrm_contact';
6948           }
6949         }
6950       }
6951     }
6952     return $field;
6953   }
6954
6955   /**
6956    * If we have a field that is better rendered via the pseudoconstant handled them here.
6957    *
6958    * Rather than joining in the additional table we render the option value on output.
6959    *
6960    * @todo - so far this applies to a narrow range of pseudocontants. We are adding them
6961    * carefully with test coverage but aim to extend.
6962    *
6963    * @param string $name
6964    */
6965   protected function addPseudoconstantFieldToSelect($name) {
6966     $field = $this->getMetadataForRealField($name);
6967     $realFieldName = $field['name'];
6968     $pseudoFieldName = CRM_Utils_Array::value('pseudofield_name', $field);
6969     if ($pseudoFieldName) {
6970       // @todo - we don't really need to build this array now we have metadata more available with getMetadataForField fn.
6971       $this->_pseudoConstantsSelect[$pseudoFieldName] = [
6972         'pseudoField' => $pseudoFieldName,
6973         'idCol' => $realFieldName,
6974         'field_name' => $field['name'],
6975         'bao' => $field['bao'],
6976         'pseudoconstant' => $field['pseudoconstant'],
6977       ];
6978     }
6979
6980     $this->_tables[$field['table_name']] = 1;
6981     $this->_element[$realFieldName] = 1;
6982     $this->_select[$field['name']] = str_replace('civicrm_contact.', 'contact_a.', "{$field['where']} as `$realFieldName`");
6983   }
6984
6985   /**
6986    * Is this pseudofield a foreign key constraint.
6987    *
6988    * We are trying to cautiously expand our pseudoconstant handling. This check allows us
6989    * to extend to a narrowly defined type (and then only if the pseudofield is in the fields
6990    * array which is done for contributions which are mostly handled as pseudoconstants.
6991    *
6992    * @param $fieldSpec
6993    *
6994    * @return bool
6995    */
6996   protected function isPseudoFieldAnFK($fieldSpec) {
6997     if (empty($fieldSpec['FKClassName'])
6998       || CRM_Utils_Array::value('keyColumn', $fieldSpec['pseudoconstant']) !== 'id'
6999       || CRM_Utils_Array::value('labelColumn', $fieldSpec['pseudoconstant']) !== 'name') {
7000       return FALSE;
7001     }
7002     return TRUE;
7003   }
7004
7005   /**
7006    * Is the field a relative date field.
7007    *
7008    * @param string $fieldName
7009    *
7010    * @return bool
7011    */
7012   protected function isARelativeDateField($fieldName) {
7013     if (substr($fieldName, -9, 9) !== '_relative') {
7014       return FALSE;
7015     }
7016     $realField = substr($fieldName, 0, strlen($fieldName) - 9);
7017     return isset($this->_fields[$realField]);
7018   }
7019
7020   /**
7021    * Get the specifications for the field, if available.
7022    *
7023    * @param string $fieldName
7024    *   Fieldname as displayed on the form.
7025    *
7026    * @return array
7027    */
7028   public function getFieldSpec($fieldName) {
7029     if (isset($this->_fields[$fieldName])) {
7030       return $this->_fields[$fieldName];
7031     }
7032     $lowFieldName = str_replace('_low', '', $fieldName);
7033     if (isset($this->_fields[$lowFieldName])) {
7034       return array_merge($this->_fields[$lowFieldName], ['field_name' => $lowFieldName]);
7035     }
7036     $highFieldName = str_replace('_high', '', $fieldName);
7037     if (isset($this->_fields[$highFieldName])) {
7038       return array_merge($this->_fields[$highFieldName], ['field_name' => $highFieldName]);
7039     }
7040     return [];
7041   }
7042
7043   public function buildWhereForDate() {
7044
7045   }
7046
7047   /**
7048    * Is the field a relative date field.
7049    *
7050    * @param string $fieldName
7051    *
7052    * @return bool
7053    */
7054   protected function isADateRangeField($fieldName) {
7055     if (substr($fieldName, -4, 4) !== '_low' && substr($fieldName, -5, 5) !== '_high') {
7056       return FALSE;
7057     }
7058     return !empty($this->getFieldSpec($fieldName));
7059   }
7060
7061   /**
7062    * @param $values
7063    */
7064   protected function buildRelativeDateQuery(&$values) {
7065     $value = CRM_Utils_Array::value(2, $values);
7066     if (empty($value)) {
7067       return;
7068     }
7069     $fieldName = substr($values[0], 0, strlen($values[0]) - 9);
7070     $fieldSpec = $this->_fields[$fieldName];
7071     $tableName = $fieldSpec['table_name'];
7072     $filters = CRM_Core_OptionGroup::values('relative_date_filters');
7073     $grouping = CRM_Utils_Array::value(3, $values);
7074     // If the table value is already set for a custom field it will be more nuanced than just '1'.
7075     $this->_tables[$tableName] = $this->_tables[$tableName] ?? 1;
7076     $this->_whereTables[$tableName] = $this->_whereTables[$tableName] ?? 1;
7077
7078     $dates = CRM_Utils_Date::getFromTo($value, NULL, NULL);
7079     // Where end would be populated only if we are handling one of the weird ones with different from & to fields.
7080     $secondWhere = $fieldSpec['where_end'] ?? $fieldSpec['where'];
7081     if (empty($dates[0])) {
7082       // ie. no start date we only have end date
7083       $this->_where[$grouping][] = $secondWhere . " <= '{$dates[1]}'";
7084
7085       $this->_qill[$grouping][] = ts('%1 is ', [$fieldSpec['title']]) . $filters[$value] . ' (' . ts("to %1", [
7086         CRM_Utils_Date::customFormat($dates[1]),
7087       ]) . ')';
7088     }
7089     elseif (empty($dates[1])) {
7090
7091       // ie. no end date we only have start date
7092       $this->_where[$grouping][] = $fieldSpec['where'] . " >= '{$dates[1]}'";
7093
7094       $this->_qill[$grouping][] = ts('%1 is ', [$fieldSpec['title']]) . $filters[$value] . ' (' . ts("from %1", [
7095         CRM_Utils_Date::customFormat($dates[0]),
7096       ]) . ')';
7097     }
7098     else {
7099       // we have start and end dates.
7100       $where = $fieldSpec['where'];
7101       if ($fieldSpec['table_name'] === 'civicrm_contact') {
7102         // Special handling for contact table as it has a known alias in advanced search.
7103         $where = str_replace('civicrm_contact.', 'contact_a.', $where);
7104       }
7105       if ($secondWhere !== $fieldSpec['where']) {
7106         $this->_where[$grouping][] = $fieldSpec['where'] . ">=  '{$dates[0]}' AND $secondWhere <='{$dates[1]}'";
7107       }
7108       else {
7109         $this->_where[$grouping][] = $where . " BETWEEN '{$dates[0]}' AND '{$dates[1]}'";
7110       }
7111
7112       $this->_qill[$grouping][] = ts('%1 is ', [$fieldSpec['title']]) . $filters[$value] . ' (' . ts("between %1 and %2", [
7113         CRM_Utils_Date::customFormat($dates[0]),
7114         CRM_Utils_Date::customFormat($dates[1]),
7115       ]) . ')';
7116     }
7117   }
7118
7119   /**
7120    * Build the query for a date field if it is a _high or _low field.
7121    *
7122    * @param $values
7123    *
7124    * @return bool
7125    * @throws \CRM_Core_Exception
7126    */
7127   public function buildDateRangeQuery($values) {
7128     if ($this->isADateRangeField($values[0])) {
7129       $fieldSpec = $this->getFieldSpec($values[0]);
7130       $title = empty($fieldSpec['unique_title']) ? $fieldSpec['title'] : $fieldSpec['unique_title'];
7131       $this->dateQueryBuilder($values, $fieldSpec['table_name'], $fieldSpec['field_name'], $fieldSpec['name'], $title);
7132       return TRUE;
7133     }
7134     return FALSE;
7135   }
7136
7137   /**
7138    * Add the address table into the query.
7139    *
7140    * @param string $tableKey
7141    * @param string $joinCondition
7142    *
7143    * @return array
7144    *   - alias name
7145    *   - address join.
7146    */
7147   protected function addAddressTable($tableKey, $joinCondition) {
7148     $tName = "$tableKey-address";
7149     $aName = "`$tableKey-address`";
7150     $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
7151     $this->_element["{$tName}_id"] = 1;
7152     $addressJoin = "\nLEFT JOIN civicrm_address $aName ON ($aName.contact_id = contact_a.id AND $aName.$joinCondition)";
7153     $this->_tables[$tName] = $addressJoin;
7154
7155     return [
7156       $aName,
7157       $addressJoin,
7158     ];
7159   }
7160
7161   /**
7162    * Get the clause for group status.
7163    *
7164    * @param int $grouping
7165    *
7166    * @return string
7167    */
7168   protected function getGroupStatusClause($grouping) {
7169     $statuses = $this->getSelectedGroupStatuses($grouping);
7170     return "status IN (" . implode(', ', $statuses) . ")";
7171   }
7172
7173   /**
7174    * Get an array of the statuses that have been selected.
7175    *
7176    * @param string $grouping
7177    *
7178    * @return array
7179    *
7180    * @throws \CRM_Core_Exception
7181    */
7182   protected function getSelectedGroupStatuses($grouping) {
7183     $statuses = [];
7184     $gcsValues = $this->getWhereValues('group_contact_status', $grouping);
7185     if ($gcsValues &&
7186       is_array($gcsValues[2])
7187     ) {
7188       foreach ($gcsValues[2] as $k => $v) {
7189         if ($v) {
7190           $statuses[] = "'" . CRM_Utils_Type::escape($k, 'String') . "'";
7191         }
7192       }
7193     }
7194     else {
7195       $statuses[] = "'Added'";
7196     }
7197     return $statuses;
7198   }
7199
7200   /**
7201    * Get the qill value for the field.
7202    *
7203    * @param $name
7204    * @param array|int|string $value
7205    * @param $op
7206    *
7207    * @return string
7208    */
7209   protected function getQillForField($name, $value, $op): string {
7210     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
7211     return (string) ts("%1 %2 %3", [
7212       1 => ts('Email'),
7213       2 => $qillop,
7214       3 => $qillVal,
7215     ]);
7216   }
7217
7218 }