CRM/Contact/BAO/Query.php

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