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