CRM/Contact/BAO/Query.php

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