CRM/Contact/BAO/Query.php

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