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