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