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