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