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 = '';
3017       if (!empty($regularGroupIDs)) {
3018         $groupIds = CRM_Utils_Type::validate(
3019           implode(',', (array) $regularGroupIDs),
3020           'CommaSeparatedIntegers'
3021         );
3022       }
3023       $gcTable = "`civicrm_group_contact-" . uniqid() . "`";
3024       $joinClause = array("contact_a.id = {$gcTable}.contact_id");
3025
3026       if (strpos($op, 'IN') !== FALSE) {
3027         $clause = "{$gcTable}.group_id $op ( $groupIds ) ";
3028       }
3029       elseif ($op == '!=') {
3030         $clause = "{$gcTable}.contact_id NOT IN (SELECT contact_id FROM civicrm_group_contact cgc WHERE cgc.group_id = $groupIds )";
3031       }
3032       else {
3033         $clause = "{$gcTable}.group_id $op $groupIds ";
3034       }
3035       $groupClause[] = "( {$clause} )";
3036
3037       if ($statii) {
3038         $joinClause[] = "{$gcTable}.status IN (" . implode(', ', $statii) . ")";
3039       }
3040       $this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")";
3041     }
3042
3043     //CRM-19589: contact(s) removed from a Smart Group, resides in civicrm_group_contact table
3044     $groupContactCacheClause = '';
3045     if (count($smartGroupIDs) || empty($value)) {
3046       $this->_groupUniqueKey = uniqid();
3047       $gccTableAlias = "civicrm_group_contact_cache_{$this->_groupUniqueKey}";
3048       $groupContactCacheClause = $this->addGroupContactCache($smartGroupIDs, $gccTableAlias, "contact_a", $op);
3049       if (!empty($groupContactCacheClause)) {
3050         if ($isNotOp) {
3051           $groupIds = implode(',', (array) $smartGroupIDs);
3052           $gcTable = "civicrm_group_contact_{$this->_groupUniqueKey}";
3053           $joinClause = array("contact_a.id = {$gcTable}.contact_id");
3054           $this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")";
3055           if (strpos($op, 'IN') !== FALSE) {
3056             $groupClause[] = "{$gcTable}.group_id $op ( $groupIds ) AND {$gccTableAlias}.group_id IS NULL";
3057           }
3058           else {
3059             $groupClause[] = "{$gcTable}.group_id $op $groupIds AND {$gccTableAlias}.group_id IS NULL";
3060           }
3061         }
3062         $groupClause[] = " ( {$groupContactCacheClause} ) ";
3063       }
3064     }
3065
3066     $and = ($op == 'IS NULL') ? ' AND ' : ' OR ';
3067     if (!empty($groupClause)) {
3068       $this->_where[$grouping][] = ' ( ' . implode($and, $groupClause) . ' ) ';
3069     }
3070
3071     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contact_DAO_Group', 'id', $value, $op);
3072     $this->_qill[$grouping][] = ts("Group(s) %1 %2", array(1 => $qillop, 2 => $qillVal));
3073     if (strpos($op, 'NULL') === FALSE) {
3074       $this->_qill[$grouping][] = ts("Group Status %1", array(1 => implode(' ' . ts('or') . ' ', $statii)));
3075     }
3076   }
3077
3078   public function getGroupCacheTableKey() {
3079     return $this->_groupUniqueKey;
3080   }
3081
3082   /**
3083    * Function translates selection of group type into a list of groups.
3084    * @param $value
3085    *
3086    * @return array
3087    */
3088   public function getGroupsFromTypeCriteria($value) {
3089     $groupIds = array();
3090     foreach ((array) $value as $groupTypeValue) {
3091       $groupList = CRM_Core_PseudoConstant::group($groupTypeValue);
3092       $groupIds = ($groupIds + $groupList);
3093     }
3094     return $groupIds;
3095   }
3096
3097   /**
3098    * Prime smart group cache for smart groups in the search, and join
3099    * civicrm_group_contact_cache table into the query.
3100    *
3101    * @param array $groups IDs of groups specified in search criteria.
3102    * @param string $tableAlias Alias to use for civicrm_group_contact_cache table.
3103    * @param string $joinTable Table on which to join civicrm_group_contact_cache
3104    * @param string $op SQL comparison operator (NULL, IN, !=, IS NULL, etc.)
3105    * @param string $joinColumn Column in $joinTable on which to join civicrm_group_contact_cache.contact_id
3106    *
3107    * @return string WHERE clause component for smart group criteria.
3108    */
3109   public function addGroupContactCache($groups, $tableAlias, $joinTable = "contact_a", $op, $joinColumn = 'id') {
3110     $isNullOp = (strpos($op, 'NULL') !== FALSE);
3111     $groupsIds = $groups;
3112
3113     $operator = ['=' => 'IN', '!=' => 'NOT IN'];
3114     if (!empty($operator[$op]) && is_array($groups)) {
3115       $op = $operator[$op];
3116     }
3117     if (!$isNullOp && !$groups) {
3118       return NULL;
3119     }
3120     elseif (strpos($op, 'IN') !== FALSE) {
3121       $groups = array($op => $groups);
3122     }
3123     elseif (is_array($groups) && count($groups)) {
3124       $groups = array('IN' => $groups);
3125     }
3126
3127     // Find all the groups that are part of a saved search.
3128     $smartGroupClause = self::buildClause("id", $op, $groups, 'Int');
3129     $sql = "
3130 SELECT id, cache_date, saved_search_id, children
3131 FROM   civicrm_group
3132 WHERE  $smartGroupClause
3133   AND  ( saved_search_id != 0
3134    OR    saved_search_id IS NOT NULL
3135    OR    children IS NOT NULL )
3136 ";
3137
3138     $group = CRM_Core_DAO::executeQuery($sql);
3139
3140     while ($group->fetch()) {
3141       $this->_useDistinct = TRUE;
3142       if (!$this->_smartGroupCache || $group->cache_date == NULL) {
3143         CRM_Contact_BAO_GroupContactCache::load($group);
3144       }
3145     }
3146     if ($group->N == 0 && $op != 'NOT IN') {
3147       return NULL;
3148     }
3149
3150     $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.{$joinColumn} = {$tableAlias}.contact_id ";
3151
3152     if ($op == 'NOT IN') {
3153       return "{$tableAlias}.contact_id NOT IN (SELECT contact_id FROM civicrm_group_contact_cache cgcc WHERE cgcc.group_id IN ( " . implode(',', (array) $groupsIds) . " ) )";
3154     }
3155     return self::buildClause("{$tableAlias}.group_id", $op, $groups, 'Int');
3156   }
3157
3158   /**
3159    * Where / qill clause for cms users
3160    *
3161    * @param $values
3162    */
3163   public function ufUser(&$values) {
3164     list($name, $op, $value, $grouping, $wildcard) = $values;
3165
3166     if ($value == 1) {
3167       $this->_tables['civicrm_uf_match'] = $this->_whereTables['civicrm_uf_match'] = ' INNER JOIN civicrm_uf_match ON civicrm_uf_match.contact_id = contact_a.id ';
3168
3169       $this->_qill[$grouping][] = ts('CMS User');
3170     }
3171     elseif ($value == 0) {
3172       $this->_tables['civicrm_uf_match'] = $this->_whereTables['civicrm_uf_match'] = ' LEFT JOIN civicrm_uf_match ON civicrm_uf_match.contact_id = contact_a.id ';
3173
3174       $this->_where[$grouping][] = " civicrm_uf_match.contact_id IS NULL";
3175       $this->_qill[$grouping][] = ts('Not a CMS User');
3176     }
3177   }
3178
3179   /**
3180    * All tag search specific.
3181    *
3182    * @param array $values
3183    */
3184   public function tagSearch(&$values) {
3185     list($name, $op, $value, $grouping, $wildcard) = $values;
3186
3187     $op = "LIKE";
3188     $value = "%{$value}%";
3189     $escapedValue = CRM_Utils_Type::escape("%{$value}%", 'String');
3190
3191     $useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
3192     $tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
3193
3194     $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
3195     $tTable = "`civicrm_tag-" . uniqid() . "`";
3196
3197     if ($useAllTagTypes[2]) {
3198       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3199         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id)
3200             LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id  )";
3201
3202       // search tag in cases
3203       $etCaseTable = "`civicrm_entity_case_tag-" . uniqid() . "`";
3204       $tCaseTable = "`civicrm_case_tag-" . uniqid() . "`";
3205       $this->_tables[$etCaseTable] = $this->_whereTables[$etCaseTable]
3206         = " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
3207             LEFT JOIN civicrm_case
3208             ON (civicrm_case_contact.case_id = civicrm_case.id
3209                 AND civicrm_case.is_deleted = 0 )
3210             LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id )
3211             LEFT JOIN civicrm_tag {$tCaseTable} ON ( {$etCaseTable}.tag_id = {$tCaseTable}.id  )";
3212       // search tag in activities
3213       $etActTable = "`civicrm_entity_act_tag-" . uniqid() . "`";
3214       $tActTable = "`civicrm_act_tag-" . uniqid() . "`";
3215       $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
3216       $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
3217
3218       $this->_tables[$etActTable] = $this->_whereTables[$etActTable]
3219         = " LEFT JOIN civicrm_activity_contact
3220             ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
3221             LEFT JOIN civicrm_activity
3222             ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
3223             AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )
3224             LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id )
3225             LEFT JOIN civicrm_tag {$tActTable} ON ( {$etActTable}.tag_id = {$tActTable}.id  )";
3226
3227       $this->_where[$grouping][] = "({$tTable}.name $op '" . $escapedValue . "' OR {$tCaseTable}.name $op '" . $escapedValue . "' OR {$tActTable}.name $op '" . $escapedValue . "')";
3228       $this->_qill[$grouping][] = ts('Tag %1 %2', array(1 => $tagTypesText[2], 2 => $op)) . ' ' . $value;
3229     }
3230     else {
3231       $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
3232       $tTable = "`civicrm_tag-" . uniqid() . "`";
3233       $this->_tables[$etTable] = $this->_whereTables[$etTable] = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND
3234       {$etTable}.entity_table = 'civicrm_contact' )
3235                 LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id  ) ";
3236
3237       $this->_where[$grouping][] = self::buildClause("{$tTable}.name", $op, $value, 'String');
3238       $this->_qill[$grouping][] = ts('Tagged %1', array(1 => $op)) . ' ' . $value;
3239     }
3240   }
3241
3242   /**
3243    * Where / qill clause for tag.
3244    *
3245    * @param array $values
3246    */
3247   public function tag(&$values) {
3248     list($name, $op, $value, $grouping, $wildcard) = $values;
3249
3250     list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_EntityTag', "tag_id", $value, $op, array('onlyActive' => FALSE));
3251     // API/Search Builder format array(operator => array(values))
3252     if (is_array($value)) {
3253       if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
3254         $op = key($value);
3255         $value = $value[$op];
3256       }
3257       if (count($value) > 1) {
3258         $this->_useDistinct = TRUE;
3259       }
3260     }
3261
3262     // implode array, then remove all spaces
3263     $value = str_replace(' ', '', implode(',', (array) $value));
3264     if (!empty($value)) {
3265       $value = CRM_Utils_Type::validate(
3266         $value,
3267         'CommaSeparatedIntegers'
3268       );
3269     }
3270
3271     $useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
3272     $tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
3273
3274     $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
3275
3276     if ($useAllTagTypes[2]) {
3277       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3278         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND {$etTable}.entity_table = 'civicrm_contact') ";
3279
3280       // search tag in cases
3281       $etCaseTable = "`civicrm_entity_case_tag-" . uniqid() . "`";
3282       $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
3283       $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
3284
3285       $this->_tables[$etCaseTable] = $this->_whereTables[$etCaseTable]
3286         = " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
3287             LEFT JOIN civicrm_case
3288             ON (civicrm_case_contact.case_id = civicrm_case.id
3289                 AND civicrm_case.is_deleted = 0 )
3290             LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id ) ";
3291       // search tag in activities
3292       $etActTable = "`civicrm_entity_act_tag-" . uniqid() . "`";
3293       $this->_tables[$etActTable] = $this->_whereTables[$etActTable]
3294         = " LEFT JOIN civicrm_activity_contact
3295             ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
3296             LEFT JOIN civicrm_activity
3297             ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
3298             AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )
3299             LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id ) ";
3300
3301       // CRM-10338
3302       if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3303         $this->_where[$grouping][] = "({$etTable}.tag_id $op OR {$etCaseTable}.tag_id $op OR {$etActTable}.tag_id $op)";
3304       }
3305       else {
3306         $this->_where[$grouping][] = "({$etTable}.tag_id $op (" . $value . ") OR {$etCaseTable}.tag_id $op (" . $value . ") OR {$etActTable}.tag_id $op (" . $value . "))";
3307       }
3308     }
3309     else {
3310       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3311         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND {$etTable}.entity_table = 'civicrm_contact') ";
3312
3313       // CRM-10338
3314       if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3315         // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3316         $op = str_replace('EMPTY', 'NULL', $op);
3317         $this->_where[$grouping][] = "{$etTable}.tag_id $op";
3318       }
3319       // CRM-16941: for tag tried with != operator we don't show contact who don't have given $value AND also in other tag
3320       elseif ($op == '!=') {
3321         $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') . ")";
3322       }
3323       elseif ($op == '=' || strstr($op, 'IN')) {
3324         $op = ($op == '=') ? 'IN' : $op;
3325         $this->_where[$grouping][] = "{$etTable}.tag_id $op ( $value )";
3326       }
3327     }
3328     $this->_qill[$grouping][] = ts('Tagged %1 %2', array(1 => $qillop, 2 => $qillVal));
3329   }
3330
3331   /**
3332    * Where/qill clause for notes
3333    *
3334    * @param array $values
3335    */
3336   public function notes(&$values) {
3337     list($name, $op, $value, $grouping, $wildcard) = $values;
3338
3339     $noteOptionValues = $this->getWhereValues('note_option', $grouping);
3340     $noteOption = CRM_Utils_Array::value('2', $noteOptionValues, '6');
3341     $noteOption = ($name == 'note_body') ? 2 : (($name == 'note_subject') ? 3 : $noteOption);
3342
3343     $this->_useDistinct = TRUE;
3344
3345     $this->_tables['civicrm_note'] = $this->_whereTables['civicrm_note']
3346       = " LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id ) ";
3347
3348     $n = trim($value);
3349     $value = CRM_Core_DAO::escapeString($n);
3350     if ($wildcard) {
3351       if (strpos($value, '%') === FALSE) {
3352         $value = "%$value%";
3353       }
3354       $op = 'LIKE';
3355     }
3356     elseif ($op == 'IS NULL' || $op == 'IS NOT NULL') {
3357       $value = NULL;
3358     }
3359
3360     $label = NULL;
3361     $clauses = array();
3362     if ($noteOption % 2 == 0) {
3363       $clauses[] = self::buildClause('civicrm_note.note', $op, $value, 'String');
3364       $label = ts('Note: Body Only');
3365     }
3366     if ($noteOption % 3 == 0) {
3367       $clauses[] = self::buildClause('civicrm_note.subject', $op, $value, 'String');
3368       $label = $label ? ts('Note: Body and Subject') : ts('Note: Subject Only');
3369     }
3370     $this->_where[$grouping][] = "( " . implode(' OR ', $clauses) . " )";
3371     list($qillOp, $qillVal) = self::buildQillForFieldValue(NULL, $name, $n, $op);
3372     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $label, 2 => $qillOp, 3 => $qillVal));
3373   }
3374
3375   /**
3376    * @param string $name
3377    * @param $op
3378    * @param $grouping
3379    *
3380    * @return bool
3381    */
3382   public function nameNullOrEmptyOp($name, $op, $grouping) {
3383     switch ($op) {
3384       case 'IS NULL':
3385       case 'IS NOT NULL':
3386         $this->_where[$grouping][] = "contact_a.$name $op";
3387         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3388         return TRUE;
3389
3390       case 'IS EMPTY':
3391         $this->_where[$grouping][] = "(contact_a.$name IS NULL OR contact_a.$name = '')";
3392         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3393         return TRUE;
3394
3395       case 'IS NOT EMPTY':
3396         $this->_where[$grouping][] = "(contact_a.$name IS NOT NULL AND contact_a.$name <> '')";
3397         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3398         return TRUE;
3399
3400       default:
3401         return FALSE;
3402     }
3403   }
3404
3405   /**
3406    * Where / qill clause for sort_name
3407    *
3408    * @param array $values
3409    */
3410   public function sortName(&$values) {
3411     list($fieldName, $op, $value, $grouping, $wildcard) = $values;
3412
3413     // handle IS NULL / IS NOT NULL / IS EMPTY / IS NOT EMPTY
3414     if ($this->nameNullOrEmptyOp($fieldName, $op, $grouping)) {
3415       return;
3416     }
3417
3418     $input = $value = trim($value);
3419
3420     if (!strlen($value)) {
3421       return;
3422     }
3423
3424     $config = CRM_Core_Config::singleton();
3425
3426     $sub = array();
3427
3428     //By default, $sub elements should be joined together with OR statements (don't change this variable).
3429     $subGlue = ' OR ';
3430
3431     $firstChar = substr($value, 0, 1);
3432     $lastChar = substr($value, -1, 1);
3433     $quotes = array("'", '"');
3434     // If string is quoted, strip quotes and otherwise don't alter it
3435     if ((strlen($value) > 2) && in_array($firstChar, $quotes) && in_array($lastChar, $quotes)) {
3436       $value = trim($value, implode('', $quotes));
3437     }
3438     // Replace spaces with wildcards for a LIKE operation
3439     // UNLESS string contains a comma (this exception is a tiny bit questionable)
3440     elseif ($op == 'LIKE' && strpos($value, ',') === FALSE) {
3441       $value = str_replace(' ', '%', $value);
3442     }
3443     $value = CRM_Core_DAO::escapeString(trim($value));
3444     if (strlen($value)) {
3445       $fieldsub = array();
3446       $value = "'" . self::getWildCardedValue($wildcard, $op, $value) . "'";
3447       if ($fieldName == 'sort_name') {
3448         $wc = "contact_a.sort_name";
3449       }
3450       else {
3451         $wc = "contact_a.display_name";
3452       }
3453       $fieldsub[] = " ( $wc $op $value )";
3454       if ($config->includeNickNameInName) {
3455         $wc = "contact_a.nick_name";
3456         $fieldsub[] = " ( $wc $op $value )";
3457       }
3458       if ($config->includeEmailInName) {
3459         $fieldsub[] = " ( civicrm_email.email $op $value ) ";
3460       }
3461       $sub[] = ' ( ' . implode(' OR ', $fieldsub) . ' ) ';
3462     }
3463
3464     $sub = ' ( ' . implode($subGlue, $sub) . ' ) ';
3465
3466     $this->_where[$grouping][] = $sub;
3467     if ($config->includeEmailInName) {
3468       $this->_tables['civicrm_email'] = $this->_whereTables['civicrm_email'] = 1;
3469       $this->_qill[$grouping][] = ts('Name or Email') . " $op - '$input'";
3470     }
3471     else {
3472       $this->_qill[$grouping][] = ts('Name') . " $op - '$input'";
3473     }
3474   }
3475
3476   /**
3477    * Where/qill clause for greeting fields.
3478    *
3479    * @param array $values
3480    */
3481   public function greetings(&$values) {
3482     list($name, $op, $value, $grouping, $wildcard) = $values;
3483     $name .= '_display';
3484
3485     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
3486     $this->_qill[$grouping][] = ts('Greeting %1 %2', array(1 => $qillop, 2 => $qillVal));
3487     $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value, 'String');
3488   }
3489
3490   /**
3491    * Where / qill clause for email
3492    *
3493    * @param array $values
3494    * @param string $apiEntity
3495    */
3496   protected function email(&$values, $apiEntity) {
3497     list($name, $op, $value, $grouping, $wildcard) = $values;
3498     $this->_tables['civicrm_email'] = $this->_whereTables['civicrm_email'] = 1;
3499
3500     // CRM-18147: for Contact's GET API, email fieldname got appended with its entity as in {$apiEntiy}_{$name}
3501     // so following code is use build whereClause for contact's primart email id
3502     if (!empty($apiEntity)) {
3503       $dataType = 'String';
3504       if ($name == 'email_id') {
3505         $dataType = 'Integer';
3506         $name = 'id';
3507       }
3508
3509       $this->_where[$grouping][] = self::buildClause('civicrm_email.is_primary', '=', 1, 'Integer');
3510       $this->_where[$grouping][] = self::buildClause("civicrm_email.$name", $op, $value, $dataType);
3511       return;
3512     }
3513
3514     $n = trim($value);
3515     if ($n) {
3516       if (substr($n, 0, 1) == '"' &&
3517         substr($n, -1, 1) == '"'
3518       ) {
3519         $n = substr($n, 1, -1);
3520         $value = CRM_Core_DAO::escapeString($n);
3521         $op = '=';
3522       }
3523       else {
3524         $value = self::getWildCardedValue($wildcard, $op, $n);
3525       }
3526       $this->_qill[$grouping][] = ts('Email') . " $op '$n'";
3527       $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, $value, 'String');
3528     }
3529     else {
3530       $this->_qill[$grouping][] = ts('Email') . " $op ";
3531       $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, NULL, 'String');
3532     }
3533   }
3534
3535   /**
3536    * Where / qill clause for phone number
3537    *
3538    * @param array $values
3539    */
3540   public function phone_numeric(&$values) {
3541     list($name, $op, $value, $grouping, $wildcard) = $values;
3542     // Strip non-numeric characters; allow wildcards
3543     $number = preg_replace('/[^\d%]/', '', $value);
3544     if ($number) {
3545       if (strpos($number, '%') === FALSE) {
3546         $number = "%$number%";
3547       }
3548
3549       $this->_qill[$grouping][] = ts('Phone number contains') . " $number";
3550       $this->_where[$grouping][] = self::buildClause('civicrm_phone.phone_numeric', 'LIKE', "$number", 'String');
3551       $this->_tables['civicrm_phone'] = $this->_whereTables['civicrm_phone'] = 1;
3552     }
3553   }
3554
3555   /**
3556    * Where / qill clause for phone type/location
3557    *
3558    * @param array $values
3559    */
3560   public function phone_option_group($values) {
3561     list($name, $op, $value, $grouping, $wildcard) = $values;
3562     $option = ($name == 'phone_phone_type_id' ? 'phone_type_id' : 'location_type_id');
3563     $options = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', $option);
3564     $optionName = $options[$value];
3565     $this->_qill[$grouping][] = ts('Phone') . ' ' . ($name == 'phone_phone_type_id' ? ts('type') : ('location')) . " $op $optionName";
3566     $this->_where[$grouping][] = self::buildClause('civicrm_phone.' . substr($name, 6), $op, $value, 'Integer');
3567     $this->_tables['civicrm_phone'] = $this->_whereTables['civicrm_phone'] = 1;
3568   }
3569
3570   /**
3571    * Where / qill clause for street_address.
3572    *
3573    * @param array $values
3574    */
3575   public function street_address(&$values) {
3576     list($name, $op, $value, $grouping) = $values;
3577
3578     if (!$op) {
3579       $op = 'LIKE';
3580     }
3581
3582     $n = trim($value);
3583
3584     if ($n) {
3585       if (strpos($value, '%') === FALSE) {
3586         // only add wild card if not there
3587         $value = "%{$value}%";
3588       }
3589       $op = 'LIKE';
3590       $this->_where[$grouping][] = self::buildClause('civicrm_address.street_address', $op, $value, 'String');
3591       $this->_qill[$grouping][] = ts('Street') . " $op '$n'";
3592     }
3593     else {
3594       $this->_where[$grouping][] = self::buildClause('civicrm_address.street_address', $op, NULL, 'String');
3595       $this->_qill[$grouping][] = ts('Street') . " $op ";
3596     }
3597
3598     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3599   }
3600
3601   /**
3602    * Where / qill clause for street_unit.
3603    *
3604    * @param array $values
3605    */
3606   public function street_number(&$values) {
3607     list($name, $op, $value, $grouping, $wildcard) = $values;
3608
3609     if (!$op) {
3610       $op = '=';
3611     }
3612
3613     $n = trim($value);
3614
3615     if (strtolower($n) == 'odd') {
3616       $this->_where[$grouping][] = " ( civicrm_address.street_number % 2 = 1 )";
3617       $this->_qill[$grouping][] = ts('Street Number is odd');
3618     }
3619     elseif (strtolower($n) == 'even') {
3620       $this->_where[$grouping][] = " ( civicrm_address.street_number % 2 = 0 )";
3621       $this->_qill[$grouping][] = ts('Street Number is even');
3622     }
3623     else {
3624       $value = $n;
3625       $this->_where[$grouping][] = self::buildClause('civicrm_address.street_number', $op, $value, 'String');
3626       $this->_qill[$grouping][] = ts('Street Number') . " $op '$n'";
3627     }
3628
3629     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3630   }
3631
3632   /**
3633    * Where / qill clause for sorting by character.
3634    *
3635    * @param array $values
3636    */
3637   public function sortByCharacter(&$values) {
3638     list($name, $op, $value, $grouping, $wildcard) = $values;
3639
3640     $name = trim($value);
3641     $cond = " contact_a.sort_name LIKE '" . CRM_Core_DAO::escapeWildCardString($name) . "%'";
3642     $this->_where[$grouping][] = $cond;
3643     $this->_qill[$grouping][] = ts('Showing only Contacts starting with: \'%1\'', array(1 => $name));
3644   }
3645
3646   /**
3647    * Where / qill clause for including contact ids.
3648    */
3649   public function includeContactIDs() {
3650     if (!$this->_includeContactIds || empty($this->_params)) {
3651       return;
3652     }
3653
3654     $contactIds = array();
3655     foreach ($this->_params as $id => $values) {
3656       if (substr($values[0], 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) {
3657         $contactIds[] = substr($values[0], CRM_Core_Form::CB_PREFIX_LEN);
3658       }
3659     }
3660     CRM_Utils_Type::validateAll($contactIds, 'Positive');
3661     if (!empty($contactIds)) {
3662       $this->_where[0][] = " ( contact_a.id IN (" . implode(',', $contactIds) . " ) ) ";
3663     }
3664   }
3665
3666   /**
3667    * Where / qill clause for postal code.
3668    *
3669    * @param array $values
3670    */
3671   public function postalCode(&$values) {
3672     // skip if the fields dont have anything to do with postal_code
3673     if (empty($this->_fields['postal_code'])) {
3674       return;
3675     }
3676
3677     list($name, $op, $value, $grouping, $wildcard) = $values;
3678
3679     // Handle numeric postal code range searches properly by casting the column as numeric
3680     if (is_numeric($value)) {
3681       $field = "IF (civicrm_address.postal_code REGEXP '^[0-9]{1,10}$', CAST(civicrm_address.postal_code AS UNSIGNED), 0)";
3682       $val = CRM_Utils_Type::escape($value, 'Integer');
3683     }
3684     else {
3685       $field = 'civicrm_address.postal_code';
3686       // Per CRM-17060 we might be looking at an 'IN' syntax so don't case arrays to string.
3687       if (!is_array($value)) {
3688         $val = CRM_Utils_Type::escape($value, 'String');
3689       }
3690       else {
3691         // Do we need to escape values here? I would expect buildClause does.
3692         $val = $value;
3693       }
3694     }
3695
3696     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3697
3698     if ($name == 'postal_code') {
3699       $this->_where[$grouping][] = self::buildClause($field, $op, $val, 'String');
3700       $this->_qill[$grouping][] = ts('Postal code') . " {$op} {$value}";
3701     }
3702     elseif ($name == 'postal_code_low') {
3703       $this->_where[$grouping][] = " ( $field >= '$val' ) ";
3704       $this->_qill[$grouping][] = ts('Postal code greater than or equal to \'%1\'', array(1 => $value));
3705     }
3706     elseif ($name == 'postal_code_high') {
3707       $this->_where[$grouping][] = " ( $field <= '$val' ) ";
3708       $this->_qill[$grouping][] = ts('Postal code less than or equal to \'%1\'', array(1 => $value));
3709     }
3710   }
3711
3712   /**
3713    * Where / qill clause for location type.
3714    *
3715    * @param array $values
3716    * @param null $status
3717    *
3718    * @return string
3719    */
3720   public function locationType(&$values, $status = NULL) {
3721     list($name, $op, $value, $grouping, $wildcard) = $values;
3722
3723     if (is_array($value)) {
3724       $this->_where[$grouping][] = 'civicrm_address.location_type_id IN (' . implode(',', $value) . ')';
3725       $this->_tables['civicrm_address'] = 1;
3726       $this->_whereTables['civicrm_address'] = 1;
3727
3728       $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
3729       $names = array();
3730       foreach ($value as $id) {
3731         $names[] = $locationType[$id];
3732       }
3733
3734       $this->_primaryLocation = FALSE;
3735
3736       if (!$status) {
3737         $this->_qill[$grouping][] = ts('Location Type') . ' - ' . implode(' ' . ts('or') . ' ', $names);
3738       }
3739       else {
3740         return implode(' ' . ts('or') . ' ', $names);
3741       }
3742     }
3743   }
3744
3745   /**
3746    * @param $values
3747    * @param bool $fromStateProvince
3748    *
3749    * @return array|NULL
3750    */
3751   public function country(&$values, $fromStateProvince = TRUE) {
3752     list($name, $op, $value, $grouping, $wildcard) = $values;
3753
3754     if (!$fromStateProvince) {
3755       $stateValues = $this->getWhereValues('state_province', $grouping);
3756       if (!empty($stateValues)) {
3757         // return back to caller if there are state province values
3758         // since that handles this case
3759         return NULL;
3760       }
3761     }
3762
3763     $countryClause = $countryQill = NULL;
3764     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY')) || ($values && !empty($value))) {
3765       $this->_tables['civicrm_address'] = 1;
3766       $this->_whereTables['civicrm_address'] = 1;
3767
3768       $countryClause = self::buildClause('civicrm_address.country_id', $op, $value, 'Positive');
3769       list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, 'country_id', $value, $op);
3770       $countryQill = ts("%1 %2 %3", array(1 => 'Country', 2 => $qillop, 3 => $qillVal));
3771
3772       if (!$fromStateProvince) {
3773         $this->_where[$grouping][] = $countryClause;
3774         $this->_qill[$grouping][] = $countryQill;
3775       }
3776     }
3777
3778     if ($fromStateProvince) {
3779       if (!empty($countryClause)) {
3780         return array(
3781           $countryClause,
3782           " ...AND... " . $countryQill,
3783         );
3784       }
3785       else {
3786         return array(NULL, NULL);
3787       }
3788     }
3789   }
3790
3791   /**
3792    * Where / qill clause for county (if present).
3793    *
3794    * @param array $values
3795    * @param null $status
3796    *
3797    * @return string
3798    */
3799   public function county(&$values, $status = NULL) {
3800     list($name, $op, $value, $grouping, $wildcard) = $values;
3801
3802     if (!is_array($value)) {
3803       // force the county to be an array
3804       $value = array($value);
3805     }
3806
3807     // check if the values are ids OR names of the counties
3808     $inputFormat = 'id';
3809     foreach ($value as $v) {
3810       if (!is_numeric($v)) {
3811         $inputFormat = 'name';
3812         break;
3813       }
3814     }
3815     $names = array();
3816     if ($op == '=') {
3817       $op = 'IN';
3818     }
3819     elseif ($op == '!=') {
3820       $op = 'NOT IN';
3821     }
3822     else {
3823       // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3824       $op = str_replace('EMPTY', 'NULL', $op);
3825     }
3826
3827     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3828       $clause = "civicrm_address.county_id $op";
3829     }
3830     elseif ($inputFormat == 'id') {
3831       $clause = 'civicrm_address.county_id IN (' . implode(',', $value) . ')';
3832
3833       $county = CRM_Core_PseudoConstant::county();
3834       foreach ($value as $id) {
3835         $names[] = CRM_Utils_Array::value($id, $county);
3836       }
3837     }
3838     else {
3839       $inputClause = array();
3840       $county = CRM_Core_PseudoConstant::county();
3841       foreach ($value as $name) {
3842         $name = trim($name);
3843         $inputClause[] = CRM_Utils_Array::key($name, $county);
3844       }
3845       $clause = 'civicrm_address.county_id IN (' . implode(',', $inputClause) . ')';
3846       $names = $value;
3847     }
3848     $this->_tables['civicrm_address'] = 1;
3849     $this->_whereTables['civicrm_address'] = 1;
3850
3851     $this->_where[$grouping][] = $clause;
3852     if (!$status) {
3853       $this->_qill[$grouping][] = ts('County') . ' - ' . implode(' ' . ts('or') . ' ', $names);
3854     }
3855     else {
3856       return implode(' ' . ts('or') . ' ', $names);
3857     }
3858   }
3859
3860   /**
3861    * Where / qill clause for state/province AND country (if present).
3862    *
3863    * @param array $values
3864    * @param null $status
3865    *
3866    * @return string
3867    */
3868   public function stateProvince(&$values, $status = NULL) {
3869     list($name, $op, $value, $grouping, $wildcard) = $values;
3870
3871     $stateClause = self::buildClause('civicrm_address.state_province_id', $op, $value, 'Positive');
3872     $this->_tables['civicrm_address'] = 1;
3873     $this->_whereTables['civicrm_address'] = 1;
3874
3875     $countryValues = $this->getWhereValues('country', $grouping);
3876     list($countryClause, $countryQill) = $this->country($countryValues, TRUE);
3877     if ($countryClause) {
3878       $clause = "( $stateClause AND $countryClause )";
3879     }
3880     else {
3881       $clause = $stateClause;
3882     }
3883
3884     $this->_where[$grouping][] = $clause;
3885     list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_Address', "state_province_id", $value, $op);
3886     if (!$status) {
3887       $this->_qill[$grouping][] = ts("State/Province %1 %2 %3", array(1 => $qillop, 2 => $qillVal, 3 => $countryQill));
3888     }
3889     else {
3890       return implode(' ' . ts('or') . ' ', $qillVal) . $countryQill;
3891     }
3892   }
3893
3894   /**
3895    * Where / qill clause for change log.
3896    *
3897    * @param array $values
3898    */
3899   public function changeLog(&$values) {
3900     list($name, $op, $value, $grouping, $wildcard) = $values;
3901
3902     $targetName = $this->getWhereValues('changed_by', $grouping);
3903     if (!$targetName) {
3904       return;
3905     }
3906
3907     $name = trim($targetName[2]);
3908     $name = CRM_Core_DAO::escapeString($name);
3909     $name = $targetName[4] ? "%$name%" : $name;
3910     $this->_where[$grouping][] = "contact_b_log.sort_name LIKE '%$name%'";
3911     $this->_tables['civicrm_log'] = $this->_whereTables['civicrm_log'] = 1;
3912     $this->_qill[$grouping][] = ts('Modified By') . " $name";
3913   }
3914
3915   /**
3916    * @param $values
3917    */
3918   public function modifiedDates($values) {
3919     $this->_useDistinct = TRUE;
3920
3921     // CRM-11281, default to added date if not set
3922     $fieldTitle = ts('Added Date');
3923     $fieldName = 'created_date';
3924     foreach (array_keys($this->_params) as $id) {
3925       if ($this->_params[$id][0] == 'log_date') {
3926         if ($this->_params[$id][2] == 2) {
3927           $fieldTitle = ts('Modified Date');
3928           $fieldName = 'modified_date';
3929         }
3930       }
3931     }
3932
3933     $this->dateQueryBuilder($values, 'contact_a', 'log_date', $fieldName, $fieldTitle);
3934
3935     self::$_openedPanes[ts('Change Log')] = TRUE;
3936   }
3937
3938   /**
3939    * @param $values
3940    */
3941   public function demographics(&$values) {
3942     list($name, $op, $value, $grouping, $wildcard) = $values;
3943
3944     if (($name == 'age_low') || ($name == 'age_high')) {
3945       $this->ageRangeQueryBuilder($values,
3946         'contact_a', 'age', 'birth_date', ts('Age')
3947       );
3948     }
3949     elseif (($name == 'birth_date_low') || ($name == 'birth_date_high')) {
3950
3951       $this->dateQueryBuilder($values,
3952         'contact_a', 'birth_date', 'birth_date', ts('Birth Date')
3953       );
3954     }
3955     elseif (($name == 'deceased_date_low') || ($name == 'deceased_date_high')) {
3956
3957       $this->dateQueryBuilder($values,
3958         'contact_a', 'deceased_date', 'deceased_date', ts('Deceased Date')
3959       );
3960     }
3961
3962     self::$_openedPanes[ts('Demographics')] = TRUE;
3963   }
3964
3965   /**
3966    * @param $values
3967    */
3968   public function privacy(&$values) {
3969     list($name, $op, $value, $grouping) = $values;
3970     //fixed for profile search listing CRM-4633
3971     if (strpbrk($value, "[")) {
3972       $value = "'{$value}'";
3973       $op = "!{$op}";
3974       $this->_where[$grouping][] = "contact_a.{$name} $op $value";
3975     }
3976     else {
3977       $this->_where[$grouping][] = "contact_a.{$name} $op $value";
3978     }
3979     $field = CRM_Utils_Array::value($name, $this->_fields);
3980     $op = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
3981     $title = $field ? $field['title'] : $name;
3982     $this->_qill[$grouping][] = "$title $op $value";
3983   }
3984
3985   /**
3986    * @param $values
3987    */
3988   public function privacyOptions($values) {
3989     list($name, $op, $value, $grouping, $wildcard) = $values;
3990
3991     if (empty($value) || !is_array($value)) {
3992       return;
3993     }
3994
3995     // get the operator and toggle values
3996     $opValues = $this->getWhereValues('privacy_operator', $grouping);
3997     $operator = 'OR';
3998     if ($opValues &&
3999       strtolower($opValues[2] == 'AND')
4000     ) {
4001       // @todo this line is logially unreachable
4002       $operator = 'AND';
4003     }
4004
4005     $toggleValues = $this->getWhereValues('privacy_toggle', $grouping);
4006     $compareOP = '!';
4007     if ($toggleValues &&
4008       $toggleValues[2] == 2
4009     ) {
4010       $compareOP = '';
4011     }
4012
4013     $clauses = array();
4014     $qill = array();
4015     foreach ($value as $dontCare => $pOption) {
4016       $clauses[] = " ( contact_a.{$pOption} = 1 ) ";
4017       $field = CRM_Utils_Array::value($pOption, $this->_fields);
4018       $title = $field ? $field['title'] : $pOption;
4019       $qill[] = " $title = 1 ";
4020     }
4021
4022     $this->_where[$grouping][] = $compareOP . '( ' . implode($operator, $clauses) . ' )';
4023     $this->_qill[$grouping][] = $compareOP . '( ' . implode($operator, $qill) . ' )';
4024   }
4025
4026   /**
4027    * @param $values
4028    */
4029   public function preferredCommunication(&$values) {
4030     list($name, $op, $value, $grouping, $wildcard) = $values;
4031
4032     if (!is_array($value)) {
4033       $value = str_replace(array('(', ')'), '', explode(",", $value));
4034     }
4035     elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
4036       $op = key($value);
4037       $value = $value[$op];
4038     }
4039     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contact_DAO_Contact', $name, $value, $op);
4040
4041     if (self::caseImportant($op)) {
4042       $value = implode("[[:cntrl:]]|[[:cntrl:]]", (array) $value);
4043       $op = (strstr($op, '!') || strstr($op, 'NOT')) ? 'NOT RLIKE' : 'RLIKE';
4044       $value = "[[:cntrl:]]" . $value . "[[:cntrl:]]";
4045     }
4046
4047     $this->_where[$grouping][] = self::buildClause("contact_a.preferred_communication_method", $op, $value);
4048     $this->_qill[$grouping][] = ts('Preferred Communication Method %1 %2', array(1 => $qillop, 2 => $qillVal));
4049   }
4050
4051   /**
4052    * Where / qill clause for relationship.
4053    *
4054    * @param array $values
4055    */
4056   public function relationship(&$values) {
4057     list($name, $op, $value, $grouping, $wildcard) = $values;
4058     if ($this->_relationshipValuesAdded) {
4059       return;
4060     }
4061     // also get values array for relation_target_name
4062     // for relationship search we always do wildcard
4063     $relationType = $this->getWhereValues('relation_type_id', $grouping);
4064     $targetName = $this->getWhereValues('relation_target_name', $grouping);
4065     $relStatus = $this->getWhereValues('relation_status', $grouping);
4066     $targetGroup = $this->getWhereValues('relation_target_group', $grouping);
4067
4068     $nameClause = $name = NULL;
4069     if ($targetName) {
4070       $name = trim($targetName[2]);
4071       if (substr($name, 0, 1) == '"' &&
4072         substr($name, -1, 1) == '"'
4073       ) {
4074         $name = substr($name, 1, -1);
4075         $name = CRM_Core_DAO::escapeString($name);
4076         $nameClause = "= '$name'";
4077       }
4078       else {
4079         $name = CRM_Core_DAO::escapeString($name);
4080         $nameClause = "LIKE '%{$name}%'";
4081       }
4082     }
4083
4084     $relTypes = $relTypesIds = array();
4085     if (!empty($relationType)) {
4086       $relationType[2] = (array) $relationType[2];
4087       foreach ($relationType[2] as $relType) {
4088         $rel = explode('_', $relType);
4089         self::$_relType = $rel[1];
4090         $params = array('id' => $rel[0]);
4091         $typeValues = array();
4092         $rTypeValue = CRM_Contact_BAO_RelationshipType::retrieve($params, $typeValues);
4093         if (!empty($rTypeValue)) {
4094           if ($rTypeValue->name_a_b == $rTypeValue->name_b_a) {
4095             // if we don't know which end of the relationship we are dealing with we'll create a temp table
4096             self::$_relType = 'reciprocal';
4097           }
4098           $relTypesIds[] = $rel[0];
4099           $relTypes[] = $relType;
4100         }
4101       }
4102     }
4103
4104     // if we are creating a temp table we build our own where for the relationship table
4105     $relationshipTempTable = NULL;
4106     if (self::$_relType == 'reciprocal') {
4107       $where = array();
4108       self::$_relationshipTempTable = $relationshipTempTable = CRM_Core_DAO::createTempTableName('civicrm_rel');
4109       if ($nameClause) {
4110         $where[$grouping][] = " sort_name $nameClause ";
4111       }
4112       $groupJoinTable = "civicrm_relationship";
4113       $groupJoinColumn = "contact_id_alt";
4114     }
4115     else {
4116       $where = &$this->_where;
4117       if ($nameClause) {
4118         $where[$grouping][] = "( contact_b.sort_name $nameClause AND contact_b.id != contact_a.id )";
4119       }
4120       $groupJoinTable = "contact_b";
4121       $groupJoinColumn = "id";
4122     }
4123     $allRelationshipType = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, NULL, TRUE);
4124     if ($nameClause || !$targetGroup) {
4125       if (!empty($relationType)) {
4126         $relQill = '';
4127         foreach ($relTypes as $rel) {
4128           if (!empty($relQill)) {
4129             $relQill .= ' OR ';
4130           }
4131           $relQill .= $allRelationshipType[$rel];
4132         }
4133         $this->_qill[$grouping][] = 'Relationship Type(s) ' . $relQill . " $name";
4134       }
4135       else {
4136         $this->_qill[$grouping][] = $name;
4137       }
4138     }
4139
4140     //check to see if the target contact is in specified group
4141     if ($targetGroup) {
4142       //add contacts from static groups
4143       $this->_tables['civicrm_relationship_group_contact'] = $this->_whereTables['civicrm_relationship_group_contact']
4144         = " 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'";
4145       $groupWhere[] = "( civicrm_relationship_group_contact.group_id IN  (" .
4146         implode(",", $targetGroup[2]) . ") ) ";
4147
4148       //add contacts from saved searches
4149       $ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", $groupJoinTable, $op, $groupJoinColumn);
4150
4151       //set the group where clause
4152       if ($ssWhere) {
4153         $groupWhere[] = "( " . $ssWhere . " )";
4154       }
4155       $this->_where[$grouping][] = "( " . implode(" OR ", $groupWhere) . " )";
4156
4157       //Get the names of the target groups for the qill
4158       $groupNames = CRM_Core_PseudoConstant::group();
4159       $qillNames = array();
4160       foreach ($targetGroup[2] as $groupId) {
4161         if (array_key_exists($groupId, $groupNames)) {
4162           $qillNames[] = $groupNames[$groupId];
4163         }
4164       }
4165       if (!empty($relationType)) {
4166         $relQill = '';
4167         foreach ($relTypes as $rel) {
4168           if (!empty($relQill)) {
4169             $relQill .= ' OR ';
4170           }
4171           $relQill .= CRM_Utils_Array::value($rel, $allRelationshipType);
4172         }
4173         $this->_qill[$grouping][] = 'Relationship Type(s) ' . $relQill . " ( " . implode(", ", $qillNames) . " )";
4174       }
4175       else {
4176         $this->_qill[$grouping][] = implode(", ", $qillNames);
4177       }
4178     }
4179
4180     // Note we do not currently set mySql to handle timezones, so doing this the old-fashioned way
4181     $today = date('Ymd');
4182     //check for active, inactive and all relation status
4183     if ($relStatus[2] == 0) {
4184       $where[$grouping][] = "(
4185 civicrm_relationship.is_active = 1 AND
4186 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
4187 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
4188 )";
4189       $this->_qill[$grouping][] = ts('Relationship - Active and Current');
4190     }
4191     elseif ($relStatus[2] == 1) {
4192       $where[$grouping][] = "(
4193 civicrm_relationship.is_active = 0 OR
4194 civicrm_relationship.end_date < {$today} OR
4195 civicrm_relationship.start_date > {$today}
4196 )";
4197       $this->_qill[$grouping][] = ts('Relationship - Inactive or not Current');
4198     }
4199
4200     $onlyDeleted = 0;
4201     if (in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params)) {
4202       $onlyDeleted = 1;
4203     }
4204     $where[$grouping][] = "(contact_b.is_deleted = {$onlyDeleted})";
4205
4206     $this->addRelationshipPermissionClauses($grouping, $where);
4207     $this->addRelationshipDateClauses($grouping, $where);
4208     $this->addRelationshipActivePeriodClauses($grouping, $where);
4209     if (!empty($relTypes)) {
4210       $where[$grouping][] = 'civicrm_relationship.relationship_type_id IN (' . implode(',', $relTypesIds) . ')';
4211     }
4212     $this->_tables['civicrm_relationship'] = $this->_whereTables['civicrm_relationship'] = 1;
4213     $this->_useDistinct = TRUE;
4214     $this->_relationshipValuesAdded = TRUE;
4215     // it could be a or b, using an OR creates an unindexed join - better to create a temp table &
4216     // join on that,
4217     if ($relationshipTempTable) {
4218       $whereClause = '';
4219       if (!empty($where[$grouping])) {
4220         $whereClause = ' WHERE ' . implode(' AND ', $where[$grouping]);
4221         $whereClause = str_replace('contact_b', 'c', $whereClause);
4222       }
4223       $sql = "
4224         CREATE TEMPORARY TABLE {$relationshipTempTable}
4225           (
4226             `contact_id` int(10) unsigned NOT NULL DEFAULT '0',
4227             `contact_id_alt` int(10) unsigned NOT NULL DEFAULT '0',
4228             KEY `contact_id` (`contact_id`),
4229             KEY `contact_id_alt` (`contact_id_alt`)
4230           )
4231           (SELECT contact_id_b as contact_id, contact_id_a as contact_id_alt, civicrm_relationship.id
4232             FROM civicrm_relationship
4233             INNER JOIN  civicrm_contact c ON civicrm_relationship.contact_id_a = c.id
4234             $whereClause )
4235           UNION
4236             (SELECT contact_id_a as contact_id, contact_id_b as contact_id_alt, civicrm_relationship.id
4237             FROM civicrm_relationship
4238             INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_b = c.id
4239             $whereClause )
4240       ";
4241       CRM_Core_DAO::executeQuery($sql);
4242     }
4243   }
4244
4245   /**
4246    * Add relationship permission criteria to where clause.
4247    *
4248    * @param string $grouping
4249    * @param array $where Array to add "where" criteria to, in case you are generating a temp table.
4250    *   Not the main query.
4251    */
4252   public function addRelationshipPermissionClauses($grouping, &$where) {
4253     $relPermission = $this->getWhereValues('relation_permission', $grouping);
4254     if ($relPermission) {
4255       if (!is_array($relPermission[2])) {
4256         // this form value was scalar in previous versions of Civi
4257         $relPermission[2] = array($relPermission[2]);
4258       }
4259       $where[$grouping][] = "(civicrm_relationship.is_permission_a_b IN (" . implode(",", $relPermission[2]) . "))";
4260
4261       $allRelationshipPermissions = CRM_Contact_BAO_Relationship::buildOptions('is_permission_a_b');
4262
4263       $relPermNames = array_intersect_key($allRelationshipPermissions, array_flip($relPermission[2]));
4264       $this->_qill[$grouping][] = ts('Permissioned Relationships') . ' - ' . implode(' OR ', $relPermNames);
4265     }
4266   }
4267
4268   /**
4269    * Add start & end date criteria in
4270    * @param string $grouping
4271    * @param array $where
4272    *   = array to add where clauses to, in case you are generating a temp table.
4273    * not the main query.
4274    */
4275   public function addRelationshipDateClauses($grouping, &$where) {
4276     $dateValues = array();
4277     $dateTypes = array(
4278       'start_date',
4279       'end_date',
4280     );
4281
4282     foreach ($dateTypes as $dateField) {
4283       $dateValueLow = $this->getWhereValues('relation_' . $dateField . '_low', $grouping);
4284       $dateValueHigh = $this->getWhereValues('relation_' . $dateField . '_high', $grouping);
4285       if (!empty($dateValueLow)) {
4286         $date = date('Ymd', strtotime($dateValueLow[2]));
4287         $where[$grouping][] = "civicrm_relationship.$dateField >= $date";
4288         $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);
4289       }
4290       if (!empty($dateValueHigh)) {
4291         $date = date('Ymd', strtotime($dateValueHigh[2]));
4292         $where[$grouping][] = "civicrm_relationship.$dateField <= $date";
4293         $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);
4294       }
4295     }
4296   }
4297
4298   /**
4299    * Add start & end active period criteria in
4300    * @param string $grouping
4301    * @param array $where
4302    *   = array to add where clauses to, in case you are generating a temp table.
4303    * not the main query.
4304    */
4305   public function addRelationshipActivePeriodClauses($grouping, &$where) {
4306     $dateValues = array();
4307     $dateField = 'active_period_date';
4308
4309     $dateValueLow = $this->getWhereValues('relation_active_period_date_low', $grouping);
4310     $dateValueHigh = $this->getWhereValues('relation_active_period_date_high', $grouping);
4311     $dateValueLowFormated = $dateValueHighFormated = NULL;
4312     if (!empty($dateValueLow) && !empty($dateValueHigh)) {
4313       $dateValueLowFormated = date('Ymd', strtotime($dateValueLow[2]));
4314       $dateValueHighFormated = date('Ymd', strtotime($dateValueHigh[2]));
4315       $this->_qill[$grouping][] = (ts('Relationship was active between')) . " " . CRM_Utils_Date::customFormat($dateValueLowFormated) . " and " . CRM_Utils_Date::customFormat($dateValueHighFormated);
4316     }
4317     elseif (!empty($dateValueLow)) {
4318       $dateValueLowFormated = date('Ymd', strtotime($dateValueLow[2]));
4319       $this->_qill[$grouping][] = (ts('Relationship was active after')) . " " . CRM_Utils_Date::customFormat($dateValueLowFormated);
4320     }
4321     elseif (!empty($dateValueHigh)) {
4322       $dateValueHighFormated = date('Ymd', strtotime($dateValueHigh[2]));
4323       $this->_qill[$grouping][] = (ts('Relationship was active before')) . " " . CRM_Utils_Date::customFormat($dateValueHighFormated);
4324     }
4325
4326     if ($activePeriodClauses = self::getRelationshipActivePeriodClauses($dateValueLowFormated, $dateValueHighFormated, TRUE)) {
4327       $where[$grouping][] = $activePeriodClauses;
4328     }
4329   }
4330
4331   /**
4332    * Get start & end active period criteria
4333    */
4334   public static function getRelationshipActivePeriodClauses($from, $to, $forceTableName) {
4335     $tableName = $forceTableName ? 'civicrm_relationship.' : '';
4336     if (!is_null($from) && !is_null($to)) {
4337       return '(((' . $tableName . 'start_date >= ' . $from . ' AND ' . $tableName . 'start_date <= ' . $to . ') OR
4338                 (' . $tableName . 'end_date >= ' . $from . ' AND ' . $tableName . 'end_date <= ' . $to . ') OR
4339                 (' . $tableName . 'start_date <= ' . $from . ' AND ' . $tableName . 'end_date >= ' . $to . ' )) 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 . ') OR
4342                (' . $tableName . 'end_date IS NULL AND ' . $tableName . 'start_date <= ' . $to . '))';
4343     }
4344     elseif (!is_null($from)) {
4345       return '((' . $tableName . 'start_date >= ' . $from . ') OR
4346                (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date IS NULL) OR
4347                (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date >= ' . $from . '))';
4348     }
4349     elseif (!is_null($to)) {
4350       return '((' . $tableName . 'start_date <= ' . $to . ') OR
4351                (' . $tableName . 'start_date IS NULL AND ' . $tableName . 'end_date IS NULL) OR
4352                (' . $tableName . 'end_date IS NULL AND ' . $tableName . 'start_date <= ' . $to . '))';
4353     }
4354   }
4355
4356   /**
4357    * Default set of return properties.
4358    *
4359    * @param int $mode
4360    *
4361    * @return array
4362    *   derault return properties
4363    */
4364   public static function &defaultReturnProperties($mode = 1) {
4365     if (!isset(self::$_defaultReturnProperties)) {
4366       self::$_defaultReturnProperties = array();
4367     }
4368
4369     if (!isset(self::$_defaultReturnProperties[$mode])) {
4370       // add activity return properties
4371       if ($mode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
4372         self::$_defaultReturnProperties[$mode] = CRM_Activity_BAO_Query::defaultReturnProperties($mode, FALSE);
4373       }
4374       else {
4375         self::$_defaultReturnProperties[$mode] = CRM_Core_Component::defaultReturnProperties($mode, FALSE);
4376       }
4377
4378       if (empty(self::$_defaultReturnProperties[$mode])) {
4379         self::$_defaultReturnProperties[$mode] = array(
4380           'home_URL' => 1,
4381           'image_URL' => 1,
4382           'legal_identifier' => 1,
4383           'external_identifier' => 1,
4384           'contact_type' => 1,
4385           'contact_sub_type' => 1,
4386           'sort_name' => 1,
4387           'display_name' => 1,
4388           'preferred_mail_format' => 1,
4389           'nick_name' => 1,
4390           'first_name' => 1,
4391           'middle_name' => 1,
4392           'last_name' => 1,
4393           'prefix_id' => 1,
4394           'suffix_id' => 1,
4395           'formal_title' => 1,
4396           'communication_style_id' => 1,
4397           'birth_date' => 1,
4398           'gender_id' => 1,
4399           'street_address' => 1,
4400           'supplemental_address_1' => 1,
4401           'supplemental_address_2' => 1,
4402           'supplemental_address_3' => 1,
4403           'city' => 1,
4404           'postal_code' => 1,
4405           'postal_code_suffix' => 1,
4406           'state_province' => 1,
4407           'country' => 1,
4408           'world_region' => 1,
4409           'geo_code_1' => 1,
4410           'geo_code_2' => 1,
4411           'email' => 1,
4412           'on_hold' => 1,
4413           'phone' => 1,
4414           'im' => 1,
4415           'household_name' => 1,
4416           'organization_name' => 1,
4417           'deceased_date' => 1,
4418           'is_deceased' => 1,
4419           'job_title' => 1,
4420           'legal_name' => 1,
4421           'sic_code' => 1,
4422           'current_employer' => 1,
4423           // FIXME: should we use defaultHierReturnProperties() for the below?
4424           'do_not_email' => 1,
4425           'do_not_mail' => 1,
4426           'do_not_sms' => 1,
4427           'do_not_phone' => 1,
4428           'do_not_trade' => 1,
4429           'is_opt_out' => 1,
4430           'contact_is_deleted' => 1,
4431           'preferred_communication_method' => 1,
4432           'preferred_language' => 1,
4433         );
4434       }
4435     }
4436     return self::$_defaultReturnProperties[$mode];
4437   }
4438
4439   /**
4440    * Get primary condition for a sql clause.
4441    *
4442    * @param int $value
4443    *
4444    * @return string|NULL
4445    */
4446   public static function getPrimaryCondition($value) {
4447     if (is_numeric($value)) {
4448       $value = (int ) $value;
4449       return ($value == 1) ? 'is_primary = 1' : 'is_primary = 0';
4450     }
4451     return NULL;
4452   }
4453
4454   /**
4455    * Wrapper for a simple search query.
4456    *
4457    * @param array $params
4458    * @param array $returnProperties
4459    * @param bool $count
4460    *
4461    * @return string
4462    */
4463   public static function getQuery($params = NULL, $returnProperties = NULL, $count = FALSE) {
4464     $query = new CRM_Contact_BAO_Query($params, $returnProperties);
4465     list($select, $from, $where, $having) = $query->query();
4466     $groupBy = ($query->_useGroupBy) ? 'GROUP BY contact_a.id' : '';
4467
4468     $query = "$select $from $where $groupBy $having";
4469     return $query;
4470   }
4471
4472   /**
4473    * These are stub comments as this function needs more explanation - particularly in terms of how it
4474    * relates to $this->searchQuery and why it replicates rather than calles $this->searchQuery.
4475    *
4476    * This function was originally written as a wrapper for the api query but is called from multiple places
4477    * in the core code directly so the name is misleading. This function does not use the searchQuery function
4478    * but it is unclear as to whehter that is historical or there is a reason
4479    *  CRM-11290 led to the permissioning action being extracted from searchQuery & shared with this function
4480    *
4481    * @param array $params
4482    * @param array $returnProperties
4483    * @param null $fields
4484    * @param string $sort
4485    * @param int $offset
4486    * @param int $row_count
4487    * @param bool $smartGroupCache
4488    *   ?? update smart group cache?.
4489    * @param bool $count
4490    *   Return count obnly.
4491    * @param bool $skipPermissions
4492    *   Should permissions be ignored or should the logged in user's permissions be applied.
4493    * @param int $mode
4494    *   This basically correlates to the component.
4495    * @param string $apiEntity
4496    *   The api entity being called.
4497    *   This sort-of duplicates $mode in a confusing way. Probably not by design.
4498    *
4499    * @param bool|null $primaryLocationOnly
4500    * @return array
4501    */
4502   public static function apiQuery(
4503     $params = NULL,
4504     $returnProperties = NULL,
4505     $fields = NULL,
4506     $sort = NULL,
4507     $offset = 0,
4508     $row_count = 25,
4509     $smartGroupCache = TRUE,
4510     $count = FALSE,
4511     $skipPermissions = TRUE,
4512     $mode = CRM_Contact_BAO_Query::MODE_CONTACTS,
4513     $apiEntity = NULL,
4514     $primaryLocationOnly = NULL
4515   ) {
4516
4517     $query = new CRM_Contact_BAO_Query(
4518       $params, $returnProperties,
4519       NULL, TRUE, FALSE, $mode,
4520       $skipPermissions,
4521       TRUE, $smartGroupCache,
4522       NULL, 'AND',
4523       $apiEntity, $primaryLocationOnly
4524     );
4525
4526     //this should add a check for view deleted if permissions are enabled
4527     if ($skipPermissions) {
4528       $query->_skipDeleteClause = TRUE;
4529     }
4530     $query->generatePermissionClause(FALSE, $count);
4531
4532     // note : this modifies _fromClause and _simpleFromClause
4533     $query->includePseudoFieldsJoin($sort);
4534
4535     list($select, $from, $where, $having) = $query->query($count);
4536
4537     $options = $query->_options;
4538     if (!empty($query->_permissionWhereClause)) {
4539       if (empty($where)) {
4540         $where = "WHERE $query->_permissionWhereClause";
4541       }
4542       else {
4543         $where = "$where AND $query->_permissionWhereClause";
4544       }
4545     }
4546
4547     $sql = "$select $from $where $having";
4548
4549     // add group by only when API action is not getcount
4550     //  otherwise query fetches incorrect count
4551     if ($query->_useGroupBy && !$count) {
4552       $sql .= self::getGroupByFromSelectColumns($query->_select, 'contact_a.id');
4553     }
4554     if (!empty($sort)) {
4555       $sort = CRM_Utils_Type::escape($sort, 'String');
4556       $sql .= " ORDER BY $sort ";
4557     }
4558     if ($row_count > 0 && $offset >= 0) {
4559       $offset = CRM_Utils_Type::escape($offset, 'Int');
4560       $row_count = CRM_Utils_Type::escape($row_count, 'Int');
4561       $sql .= " LIMIT $offset, $row_count ";
4562     }
4563
4564     $dao = CRM_Core_DAO::executeQuery($sql);
4565
4566     // @todo derive this from the component class rather than hard-code two options.
4567     $entityIDField = ($mode == CRM_Contact_BAO_Query::MODE_CONTRIBUTE) ? 'contribution_id' : 'contact_id';
4568
4569     $values = array();
4570     while ($dao->fetch()) {
4571       if ($count) {
4572         $noRows = $dao->rowCount;
4573         return array($noRows, NULL);
4574       }
4575       $val = $query->store($dao);
4576       $convertedVals = $query->convertToPseudoNames($dao, TRUE, TRUE);
4577
4578       if (!empty($convertedVals)) {
4579         $val = array_replace_recursive($val, $convertedVals);
4580       }
4581       $values[$dao->$entityIDField] = $val;
4582     }
4583     return array($values, $options);
4584   }
4585
4586   /**
4587    * Get the actual custom field name by stripping off the appended string.
4588    *
4589    * The string could be _relative, _from, or _to
4590    *
4591    * @todo use metadata rather than convention to do this.
4592    *
4593    * @param string $parameterName
4594    *   The name of the parameter submitted to the form.
4595    *   e.g
4596    *   custom_3_relative
4597    *   custom_3_from
4598    *
4599    * @return string
4600    */
4601   public static function getCustomFieldName($parameterName) {
4602     if (substr($parameterName, -5, 5) == '_from') {
4603       return substr($parameterName, 0, strpos($parameterName, '_from'));
4604     }
4605     if (substr($parameterName, -9, 9) == '_relative') {
4606       return substr($parameterName, 0, strpos($parameterName, '_relative'));
4607     }
4608     if (substr($parameterName, -3, 3) == '_to') {
4609       return substr($parameterName, 0, strpos($parameterName, '_to'));
4610     }
4611   }
4612
4613   /**
4614    * Convert submitted values for relative custom fields to query object format.
4615    *
4616    * The query will support the sqlOperator format so convert to that format.
4617    *
4618    * @param array $formValues
4619    *   Submitted values.
4620    * @param array $params
4621    *   Converted parameters for the query object.
4622    * @param string $values
4623    *   Submitted value.
4624    * @param string $fieldName
4625    *   Submitted field name. (Matches form field not DB field.)
4626    */
4627   protected static function convertCustomRelativeFields(&$formValues, &$params, $values, $fieldName) {
4628     if (empty($values)) {
4629       // e.g we might have relative set & from & to empty. The form flow is a bit funky &
4630       // this function gets called again after they fields have been converted which can get ugly.
4631       return;
4632     }
4633     $customFieldName = self::getCustomFieldName($fieldName);
4634
4635     if (substr($fieldName, -9, 9) == '_relative') {
4636       list($from, $to) = CRM_Utils_Date::getFromTo($values, NULL, NULL);
4637     }
4638     else {
4639       if ($fieldName == $customFieldName . '_to' && CRM_Utils_Array::value($customFieldName . '_from', $formValues)) {
4640         // Both to & from are set. We only need to acton one, choosing from.
4641         return;
4642       }
4643
4644       $from = CRM_Utils_Array::value($customFieldName . '_from', $formValues, NULL);
4645       $to = CRM_Utils_Array::value($customFieldName . '_to', $formValues, NULL);
4646
4647       if (self::isCustomDateField($customFieldName)) {
4648         list($from, $to) = CRM_Utils_Date::getFromTo(NULL, $from, $to);
4649       }
4650     }
4651
4652     if ($from) {
4653       if ($to) {
4654         $relativeFunction = array('BETWEEN' => array($from, $to));
4655       }
4656       else {
4657         $relativeFunction = array('>=' => $from);
4658       }
4659     }
4660     else {
4661       $relativeFunction = array('<=' => $to);
4662     }
4663     $params[] = array(
4664       $customFieldName,
4665       '=',
4666       $relativeFunction,
4667       0,
4668       0,
4669     );
4670   }
4671
4672   /**
4673    * Are we dealing with custom field of type date.
4674    *
4675    * @param $fieldName
4676    *
4677    * @return bool
4678    */
4679   public static function isCustomDateField($fieldName) {
4680     if (($customFieldID = CRM_Core_BAO_CustomField::getKeyID($fieldName)) == FALSE) {
4681       return FALSE;
4682     }
4683     if ('Date' == civicrm_api3('CustomField', 'getvalue', array('id' => $customFieldID, 'return' => 'data_type'))) {
4684       return TRUE;
4685     }
4686     return FALSE;
4687   }
4688
4689   /**
4690    * Has this field already been reformatting to Query object syntax.
4691    *
4692    * The form layer passed formValues to this function in preProcess & postProcess. Reason unknown. This seems
4693    * to come with associated double queries & is possibly damaging performance.
4694    *
4695    * However, here we add a tested function to ensure convertFormValues identifies pre-processed fields & returns
4696    * them as they are.
4697    *
4698    * @param mixed $values
4699    *   Value in formValues for the field.
4700    *
4701    * @return bool;
4702    */
4703   public static function isAlreadyProcessedForQueryFormat($values) {
4704     if (!is_array($values)) {
4705       return FALSE;
4706     }
4707     if (($operator = CRM_Utils_Array::value(1, $values)) == FALSE) {
4708       return FALSE;
4709     }
4710     return in_array($operator, CRM_Core_DAO::acceptedSQLOperators());
4711   }
4712
4713   /**
4714    * If the state and country are passed remove state.
4715    *
4716    * Country is implicit from the state, but including both results in
4717    * a poor query as there is no combined index on state AND country.
4718    *
4719    * CRM-18125
4720    *
4721    * @param array $formValues
4722    */
4723   public static function filterCountryFromValuesIfStateExists(&$formValues) {
4724     if (!empty($formValues['country']) && !empty($formValues['state_province'])) {
4725       // The use of array map sanitises the data by ensuring we are dealing with integers.
4726       $states = implode(', ', array_map('intval', $formValues['state_province']));
4727       $countryList = CRM_Core_DAO::singleValueQuery(
4728         "SELECT GROUP_CONCAT(country_id) FROM civicrm_state_province WHERE id IN ($states)"
4729       );
4730       if ($countryList == $formValues['country']) {
4731         unset($formValues['country']);
4732       }
4733     }
4734   }
4735
4736   /**
4737    * For some special cases, grouping by subset of select fields becomes mandatory.
4738    * Hence, full_group_by mode is handled by appending any_value
4739    * keyword to select fields not present in groupBy
4740    *
4741    * @param array $selectClauses
4742    * @param array $groupBy - Columns already included in GROUP By clause.
4743    * @param string $aggregateFunction
4744    *
4745    * @return string
4746    */
4747   public static function appendAnyValueToSelect($selectClauses, $groupBy, $aggregateFunction = 'ANY_VALUE') {
4748     if (!CRM_Utils_SQL::disableFullGroupByMode()) {
4749       $groupBy = array_map('trim', (array) $groupBy);
4750       $aggregateFunctions = '/(ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN|IF)[[:blank:]]*\(/i';
4751       foreach ($selectClauses as $key => &$val) {
4752         list($selectColumn, $alias) = array_pad(preg_split('/ as /i', $val), 2, NULL);
4753         // append ANY_VALUE() keyword
4754         if (!in_array($selectColumn, $groupBy) && preg_match($aggregateFunctions, trim($selectColumn)) !== 1) {
4755           $val = ($aggregateFunction == 'GROUP_CONCAT') ?
4756             str_replace($selectColumn, "$aggregateFunction(DISTINCT {$selectColumn})", $val) :
4757             str_replace($selectColumn, "$aggregateFunction({$selectColumn})", $val);
4758         }
4759       }
4760     }
4761
4762     return "SELECT " . implode(', ', $selectClauses) . " ";
4763   }
4764
4765   /**
4766    * For some special cases, where if non-aggregate ORDER BY columns are not present in GROUP BY
4767    *  on full_group_by mode, then append the those missing columns to GROUP BY clause
4768    * keyword to select fields not present in groupBy
4769    *
4770    * @param string $groupBy - GROUP BY clause where missing ORDER BY columns will be appended if not present
4771    * @param array $orderBys - ORDER BY sub-clauses
4772    *
4773    */
4774   public static function getGroupByFromOrderBy(&$groupBy, $orderBys) {
4775     if (!CRM_Utils_SQL::disableFullGroupByMode()) {
4776       foreach ($orderBys as $orderBy) {
4777         $orderBy = str_ireplace(array(' DESC', ' ASC', '`'), '', $orderBy); // remove sort syntax from ORDER BY clauses if present
4778         // if ORDER BY column is not present in GROUP BY then append it to end
4779         if (preg_match('/(MAX|MIN)\(/i', trim($orderBy)) !== 1 && !strstr($groupBy, $orderBy)) {
4780           $groupBy .= ", {$orderBy}";
4781         }
4782       }
4783     }
4784   }
4785
4786   /**
4787    * Include Select columns in groupBy clause.
4788    *
4789    * @param array $selectClauses
4790    * @param array $groupBy - Columns already included in GROUP By clause.
4791    *
4792    * @return string
4793    */
4794   public static function getGroupByFromSelectColumns($selectClauses, $groupBy = NULL) {
4795     $groupBy = (array) $groupBy;
4796     $mysqlVersion = CRM_Core_DAO::singleValueQuery('SELECT VERSION()');
4797     $sqlMode = CRM_Core_DAO::singleValueQuery('SELECT @@sql_mode');
4798
4799     //return if ONLY_FULL_GROUP_BY is not enabled.
4800     if (CRM_Utils_SQL::supportsFullGroupBy() && !empty($sqlMode) && in_array('ONLY_FULL_GROUP_BY', explode(',', $sqlMode))) {
4801       $regexToExclude = '/(ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN|IF)[[:blank:]]*\(/i';
4802       foreach ($selectClauses as $key => $val) {
4803         $aliasArray = preg_split('/ as /i', $val);
4804         // if more than 1 alias we need to split by ','.
4805         if (count($aliasArray) > 2) {
4806           $aliasArray = preg_split('/,/', $val);
4807           foreach ($aliasArray as $key => $value) {
4808             $alias = current(preg_split('/ as /i', $value));
4809             if (!in_array($alias, $groupBy) && preg_match($regexToExclude, trim($alias)) !== 1) {
4810               $groupBy[] = $alias;
4811             }
4812           }
4813         }
4814         else {
4815           list($selectColumn, $alias) = array_pad($aliasArray, 2, NULL);
4816           $dateRegex = '/^(DATE_FORMAT|DATE_ADD|CASE)/i';
4817           $tableName = current(explode('.', $selectColumn));
4818           $primaryKey = "{$tableName}.id";
4819           // exclude columns which are already included in groupBy and aggregate functions from select
4820           // CRM-18439 - Also exclude the columns which are functionally dependent on columns in $groupBy (MySQL 5.7+)
4821           if (!in_array($selectColumn, $groupBy) && !in_array($primaryKey, $groupBy) && preg_match($regexToExclude, trim($selectColumn)) !== 1) {
4822             if (!empty($alias) && preg_match($dateRegex, trim($selectColumn))) {
4823               $groupBy[] = $alias;
4824             }
4825             else {
4826               $groupBy[] = $selectColumn;
4827             }
4828           }
4829         }
4830       }
4831     }
4832
4833     if (!empty($groupBy)) {
4834       return " GROUP BY " . implode(', ', $groupBy);
4835     }
4836     return '';
4837   }
4838
4839   /**
4840    * Create and query the db for an contact search.
4841    *
4842    * @param int $offset
4843    *   The offset for the query.
4844    * @param int $rowCount
4845    *   The number of rows to return.
4846    * @param string|CRM_Utils_Sort $sort
4847    *   The order by string.
4848    * @param bool $count
4849    *   Is this a count only query ?.
4850    * @param bool $includeContactIds
4851    *   Should we include contact ids?.
4852    * @param bool $sortByChar
4853    *   If true returns the distinct array of first characters for search results.
4854    * @param bool $groupContacts
4855    *   If true, return only the contact ids.
4856    * @param bool $returnQuery
4857    *   Should we return the query as a string.
4858    * @param string $additionalWhereClause
4859    *   If the caller wants to further restrict the search (used for components).
4860    * @param null $sortOrder
4861    * @param string $additionalFromClause
4862    *   Should be clause with proper joins, effective to reduce where clause load.
4863    *
4864    * @param bool $skipOrderAndLimit
4865    *
4866    * @return CRM_Core_DAO
4867    */
4868   public function searchQuery(
4869     $offset = 0, $rowCount = 0, $sort = NULL,
4870     $count = FALSE, $includeContactIds = FALSE,
4871     $sortByChar = FALSE, $groupContacts = FALSE,
4872     $returnQuery = FALSE,
4873     $additionalWhereClause = NULL, $sortOrder = NULL,
4874     $additionalFromClause = NULL, $skipOrderAndLimit = FALSE
4875   ) {
4876
4877     if ($includeContactIds) {
4878       $this->_includeContactIds = TRUE;
4879       $this->_whereClause = $this->whereClause();
4880     }
4881
4882     $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params);
4883
4884     // if we’re explicitly looking for a certain contact’s contribs, events, etc.
4885     // and that contact happens to be deleted, set $onlyDeleted to true
4886     foreach ($this->_params as $values) {
4887       $name = CRM_Utils_Array::value(0, $values);
4888       $op = CRM_Utils_Array::value(1, $values);
4889       $value = CRM_Utils_Array::value(2, $values);
4890       if ($name == 'contact_id' and $op == '=') {
4891         if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) {
4892           $onlyDeleted = TRUE;
4893         }
4894         break;
4895       }
4896     }
4897
4898     // building the query string
4899     $groupBy = $groupByCols = NULL;
4900     if (!$count) {
4901       if (isset($this->_groupByComponentClause)) {
4902         $groupBy = $this->_groupByComponentClause;
4903         $groupByCols = preg_replace('/^GROUP BY /', '', trim($this->_groupByComponentClause));
4904         $groupByCols = explode(', ', $groupByCols);
4905       }
4906       elseif ($this->_useGroupBy) {
4907         $groupByCols = array('contact_a.id');
4908       }
4909     }
4910     if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) {
4911       $groupByCols = array('civicrm_activity.id');
4912     }
4913
4914     $order = $orderBy = $limit = '';
4915     if (!$count) {
4916       list($order, $additionalFromClause) = $this->prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause);
4917
4918       if ($rowCount > 0 && $offset >= 0) {
4919         $offset = CRM_Utils_Type::escape($offset, 'Int');
4920         $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
4921         $limit = " LIMIT $offset, $rowCount ";
4922       }
4923     }
4924     // Two cases where we are disabling FGB (FULL_GROUP_BY_MODE):
4925     //   1. Expecting the search query to return all the first single letter characters of contacts ONLY, but when FGB is enabled
4926     //      MySQL expect the columns present in GROUP BY, must be present in SELECT clause and that results into error, needless to have other columns.
4927     //   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
4928     //     clause. This will impact the search query output.
4929     $disableFullGroupByMode = ($sortByChar || !empty($groupByCols) || $groupContacts);
4930
4931     if ($disableFullGroupByMode) {
4932       CRM_Core_DAO::disableFullGroupByMode();
4933     }
4934
4935     // CRM-15231
4936     $this->_sort = $sort;
4937
4938     //CRM-15967
4939     $this->includePseudoFieldsJoin($sort);
4940
4941     list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted);
4942
4943     if (!empty($groupByCols)) {
4944       $groupBy = " GROUP BY " . implode(', ', $groupByCols);
4945     }
4946
4947     if ($additionalWhereClause) {
4948       $where = $where . ' AND ' . $additionalWhereClause;
4949     }
4950
4951     //additional from clause should be w/ proper joins.
4952     if ($additionalFromClause) {
4953       $from .= "\n" . $additionalFromClause;
4954     }
4955
4956     // if we are doing a transform, do it here
4957     // use the $from, $where and $having to get the contact ID
4958     if ($this->_displayRelationshipType) {
4959       $this->filterRelatedContacts($from, $where, $having);
4960     }
4961
4962     if ($skipOrderAndLimit) {
4963       $query = "$select $from $where $having $groupBy";
4964     }
4965     else {
4966       $query = "$select $from $where $having $groupBy $order $limit";
4967     }
4968
4969     if ($returnQuery) {
4970       return $query;
4971     }
4972     if ($count) {
4973       return CRM_Core_DAO::singleValueQuery($query);
4974     }
4975
4976     $dao = CRM_Core_DAO::executeQuery($query);
4977
4978     if ($disableFullGroupByMode) {
4979       CRM_Core_DAO::reenableFullGroupByMode();
4980     }
4981
4982     if ($groupContacts) {
4983       $ids = array();
4984       while ($dao->fetch()) {
4985         $ids[] = $dao->id;
4986       }
4987       return implode(',', $ids);
4988     }
4989
4990     return $dao;
4991   }
4992
4993   /**
4994    * Fetch a list of contacts for displaying a search results page
4995    *
4996    * @param array $cids
4997    *   List of contact IDs
4998    * @param bool $includeContactIds
4999    * @return CRM_Core_DAO
5000    */
5001   public function getCachedContacts($cids, $includeContactIds) {
5002     CRM_Utils_Type::validateAll($cids, 'Positive');
5003     $this->_includeContactIds = $includeContactIds;
5004     $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params);
5005     list($select, $from, $where) = $this->query(FALSE, FALSE, FALSE, $onlyDeleted);
5006     $select .= sprintf(", (%s) AS _wgt", $this->createSqlCase('contact_a.id', $cids));
5007     $where .= sprintf(' AND contact_a.id IN (%s)', implode(',', $cids));
5008     $order = 'ORDER BY _wgt';
5009     $groupBy = $this->_useGroupBy ? ' GROUP BY contact_a.id' : '';
5010     $limit = '';
5011     $query = "$select $from $where $groupBy $order $limit";
5012
5013     return CRM_Core_DAO::executeQuery($query);
5014   }
5015
5016   /**
5017    * Construct a SQL CASE expression.
5018    *
5019    * @param string $idCol
5020    *   The name of a column with ID's (eg 'contact_a.id').
5021    * @param array $cids
5022    *   Array(int $weight => int $id).
5023    * @return string
5024    *   CASE WHEN id=123 THEN 1 WHEN id=456 THEN 2 END
5025    */
5026   private function createSqlCase($idCol, $cids) {
5027     $buf = "CASE\n";
5028     foreach ($cids as $weight => $cid) {
5029       $buf .= " WHEN $idCol = $cid THEN $weight \n";
5030     }
5031     $buf .= "END\n";
5032     return $buf;
5033   }
5034
5035   /**
5036    * Populate $this->_permissionWhereClause with permission related clause and update other
5037    * query related properties.
5038    *
5039    * Function calls ACL permission class and hooks to filter the query appropriately
5040    *
5041    * Note that these 2 params were in the code when extracted from another function
5042    * and a second round extraction would be to make them properties of the class
5043    *
5044    * @param bool $onlyDeleted
5045    *   Only get deleted contacts.
5046    * @param bool $count
5047    *   Return Count only.
5048    */
5049   public function generatePermissionClause($onlyDeleted = FALSE, $count = FALSE) {
5050     if (!$this->_skipPermission) {
5051       $this->_permissionWhereClause = CRM_ACL_API::whereClause(
5052         CRM_Core_Permission::VIEW,
5053         $this->_tables,
5054         $this->_whereTables,
5055         NULL,
5056         $onlyDeleted,
5057         $this->_skipDeleteClause
5058       );
5059
5060       // regenerate fromClause since permission might have added tables
5061       if ($this->_permissionWhereClause) {
5062         //fix for row count in qill (in contribute/membership find)
5063         if (!$count) {
5064           $this->_useDistinct = TRUE;
5065         }
5066         //CRM-15231
5067         $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
5068         $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
5069         // note : this modifies _fromClause and _simpleFromClause
5070         $this->includePseudoFieldsJoin($this->_sort);
5071       }
5072     }
5073     else {
5074       // add delete clause if needed even if we are skipping permission
5075       // CRM-7639
5076       if (!$this->_skipDeleteClause) {
5077         if (CRM_Core_Permission::check('access deleted contacts') and $onlyDeleted) {
5078           $this->_permissionWhereClause = '(contact_a.is_deleted)';
5079         }
5080         else {
5081           // CRM-6181
5082           $this->_permissionWhereClause = '(contact_a.is_deleted = 0)';
5083         }
5084       }
5085     }
5086   }
5087
5088   /**
5089    * @param $val
5090    */
5091   public function setSkipPermission($val) {
5092     $this->_skipPermission = $val;
5093   }
5094
5095   /**
5096    * @param null $context
5097    *
5098    * @return array
5099    */
5100   public function &summaryContribution($context = NULL) {
5101     list($innerselect, $from, $where, $having) = $this->query(TRUE);
5102
5103     // hack $select
5104     $select = "
5105 SELECT COUNT( conts.total_amount ) as total_count,
5106        SUM(   conts.total_amount ) as total_amount,
5107        AVG(   conts.total_amount ) as total_avg,
5108        conts.currency              as currency";
5109     if ($this->_permissionWhereClause) {
5110       $where .= " AND " . $this->_permissionWhereClause;
5111     }
5112     if ($context == 'search') {
5113       $where .= " AND contact_a.is_deleted = 0 ";
5114     }
5115
5116     $query = $this->appendFinancialTypeWhereAndFromToQueryStrings($where, $from);
5117
5118     // make sure contribution is completed - CRM-4989
5119     $completedWhere = $where . " AND civicrm_contribution.contribution_status_id = 1 ";
5120
5121     $summary = array();
5122     $summary['total'] = array();
5123     $summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a";
5124     $innerQuery = "SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
5125       civicrm_contribution.currency $from $completedWhere";
5126     $query = "$select FROM (
5127       $innerQuery GROUP BY civicrm_contribution.id
5128     ) as conts
5129     GROUP BY currency";
5130
5131     $dao = CRM_Core_DAO::executeQuery($query);
5132
5133     $summary['total']['count'] = 0;
5134     $summary['total']['amount'] = $summary['total']['avg'] = array();
5135     while ($dao->fetch()) {
5136       $summary['total']['count'] += $dao->total_count;
5137       $summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
5138       $summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
5139     }
5140
5141     $orderBy = 'ORDER BY civicrm_contribution_total_amount_count DESC';
5142     $groupBy = 'GROUP BY currency, civicrm_contribution.total_amount';
5143     $modeSQL = "$select, SUBSTRING_INDEX(GROUP_CONCAT(conts.total_amount
5144       ORDER BY conts.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,
5145       MAX(conts.civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count
5146       FROM ($innerQuery
5147       $groupBy $orderBy) as conts
5148       GROUP BY currency";
5149
5150     $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL);
5151
5152     $medianSQL = "{$from} {$completedWhere}";
5153     $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution');
5154     $summary['total']['currencyCount'] = count($summary['total']['median']);
5155
5156     if (!empty($summary['total']['amount'])) {
5157       $summary['total']['amount'] = implode(',&nbsp;', $summary['total']['amount']);
5158       $summary['total']['avg'] = implode(',&nbsp;', $summary['total']['avg']);
5159       $summary['total']['mode'] = implode(',&nbsp;', $summary['total']['mode']);
5160       $summary['total']['median'] = implode(',&nbsp;', $summary['total']['median']);
5161     }
5162     else {
5163       $summary['total']['amount'] = $summary['total']['avg'] = $summary['total']['median'] = 0;
5164     }
5165
5166     // soft credit summary
5167     if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
5168       $softCreditWhere = "{$completedWhere} AND civicrm_contribution_soft.id IS NOT NULL";
5169       $query = "
5170         $select FROM (
5171           SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency $from $softCreditWhere
5172           GROUP BY civicrm_contribution_soft.id
5173         ) as conts
5174         GROUP BY currency";
5175       $dao = CRM_Core_DAO::executeQuery($query);
5176       $summary['soft_credit']['count'] = 0;
5177       $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = array();
5178       while ($dao->fetch()) {
5179         $summary['soft_credit']['count'] += $dao->total_count;
5180         $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
5181         $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
5182       }
5183       if (!empty($summary['soft_credit']['amount'])) {
5184         $summary['soft_credit']['amount'] = implode(',&nbsp;', $summary['soft_credit']['amount']);
5185         $summary['soft_credit']['avg'] = implode(',&nbsp;', $summary['soft_credit']['avg']);
5186       }
5187       else {
5188         $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0;
5189       }
5190     }
5191
5192     // hack $select
5193     //@todo  - this could be one query using the IF in mysql - eg
5194     //  SELECT sum(total_completed), sum(count_completed), sum(count_cancelled), sum(total_cancelled) FROM (
5195     //   SELECT civicrm_contribution.total_amount, civicrm_contribution.currency  ,
5196     //  IF(civicrm_contribution.contribution_status_id = 1, 1, 0 ) as count_completed,
5197     //  IF(civicrm_contribution.contribution_status_id = 1, total_amount, 0 ) as total_completed,
5198     //  IF(civicrm_contribution.cancel_date IS NOT NULL = 1, 1, 0 ) as count_cancelled,
5199     //  IF(civicrm_contribution.cancel_date IS NOT NULL = 1, total_amount, 0 ) as total_cancelled
5200     // FROM civicrm_contact contact_a
5201     //  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
5202     // WHERE  ( ... where clause....
5203     // AND (civicrm_contribution.cancel_date IS NOT NULL OR civicrm_contribution.contribution_status_id = 1)
5204     //  ) as conts
5205
5206     $select = "
5207 SELECT COUNT( conts.total_amount ) as cancel_count,
5208        SUM(   conts.total_amount ) as cancel_amount,
5209        AVG(   conts.total_amount ) as cancel_avg,
5210        conts.currency              as currency";
5211
5212     $where .= " AND civicrm_contribution.cancel_date IS NOT NULL ";
5213     if ($context == 'search') {
5214       $where .= " AND contact_a.is_deleted = 0 ";
5215     }
5216
5217     $query = "$select FROM (
5218       SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where
5219       GROUP BY civicrm_contribution.id
5220     ) as conts
5221     GROUP BY currency";
5222
5223     $dao = CRM_Core_DAO::executeQuery($query);
5224
5225     if ($dao->N <= 1) {
5226       if ($dao->fetch()) {
5227         $summary['cancel']['count'] = $dao->cancel_count;
5228         $summary['cancel']['amount'] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
5229         $summary['cancel']['avg'] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
5230       }
5231     }
5232     else {
5233       $summary['cancel']['count'] = 0;
5234       $summary['cancel']['amount'] = $summary['cancel']['avg'] = array();
5235       while ($dao->fetch()) {
5236         $summary['cancel']['count'] += $dao->cancel_count;
5237         $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
5238         $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
5239       }
5240       $summary['cancel']['amount'] = implode(',&nbsp;', $summary['cancel']['amount']);
5241       $summary['cancel']['avg'] = implode(',&nbsp;', $summary['cancel']['avg']);
5242     }
5243
5244     return $summary;
5245   }
5246
5247   /**
5248    * Append financial ACL limits to the query from & where clauses, if applicable.
5249    *
5250    * @param string $where
5251    * @param string $from
5252    */
5253   public function appendFinancialTypeWhereAndFromToQueryStrings(&$where, &$from) {
5254     if (!CRM_Financial_BAO_FinancialType::isACLFinancialTypeStatus()) {
5255       return;
5256     }
5257     CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes($financialTypes);
5258     if (!empty($financialTypes)) {
5259       $where .= " AND civicrm_contribution.financial_type_id IN (" . implode(',', array_keys($financialTypes)) . ") AND li.id IS NULL";
5260       $from .= " LEFT JOIN civicrm_line_item li
5261                   ON civicrm_contribution.id = li.contribution_id AND
5262                      li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN (" . implode(',', array_keys($financialTypes)) . ") ";
5263     }
5264     else {
5265       $where .= " AND civicrm_contribution.financial_type_id IN (0)";
5266     }
5267   }
5268
5269   /**
5270    * Getter for the qill object.
5271    *
5272    * @return string
5273    */
5274   public function qill() {
5275     return $this->_qill;
5276   }
5277
5278   /**
5279    * Default set of return default hier return properties.
5280    *
5281    * @return array
5282    */
5283   public static function &defaultHierReturnProperties() {
5284     if (!isset(self::$_defaultHierReturnProperties)) {
5285       self::$_defaultHierReturnProperties = array(
5286         'home_URL' => 1,
5287         'image_URL' => 1,
5288         'legal_identifier' => 1,
5289         'external_identifier' => 1,
5290         'contact_type' => 1,
5291         'contact_sub_type' => 1,
5292         'sort_name' => 1,
5293         'display_name' => 1,
5294         'nick_name' => 1,
5295         'first_name' => 1,
5296         'middle_name' => 1,
5297         'last_name' => 1,
5298         'prefix_id' => 1,
5299         'suffix_id' => 1,
5300         'formal_title' => 1,
5301         'communication_style_id' => 1,
5302         'email_greeting' => 1,
5303         'postal_greeting' => 1,
5304         'addressee' => 1,
5305         'birth_date' => 1,
5306         'gender_id' => 1,
5307         'preferred_communication_method' => 1,
5308         'do_not_phone' => 1,
5309         'do_not_email' => 1,
5310         'do_not_mail' => 1,
5311         'do_not_sms' => 1,
5312         'do_not_trade' => 1,
5313         'location' => array(
5314           '1' => array(
5315             'location_type' => 1,
5316             'street_address' => 1,
5317             'city' => 1,
5318             'state_province' => 1,
5319             'postal_code' => 1,
5320             'postal_code_suffix' => 1,
5321             'country' => 1,
5322             'phone-Phone' => 1,
5323             'phone-Mobile' => 1,
5324             'phone-Fax' => 1,
5325             'phone-1' => 1,
5326             'phone-2' => 1,
5327             'phone-3' => 1,
5328             'im-1' => 1,
5329             'im-2' => 1,
5330             'im-3' => 1,
5331             'email-1' => 1,
5332             'email-2' => 1,
5333             'email-3' => 1,
5334           ),
5335           '2' => array(
5336             'location_type' => 1,
5337             'street_address' => 1,
5338             'city' => 1,
5339             'state_province' => 1,
5340             'postal_code' => 1,
5341             'postal_code_suffix' => 1,
5342             'country' => 1,
5343             'phone-Phone' => 1,
5344             'phone-Mobile' => 1,
5345             'phone-1' => 1,
5346             'phone-2' => 1,
5347             'phone-3' => 1,
5348             'im-1' => 1,
5349             'im-2' => 1,
5350             'im-3' => 1,
5351             'email-1' => 1,
5352             'email-2' => 1,
5353             'email-3' => 1,
5354           ),
5355         ),
5356       );
5357     }
5358     return self::$_defaultHierReturnProperties;
5359   }
5360
5361   /**
5362    * Build query for a date field.
5363    *
5364    * @param array $values
5365    * @param string $tableName
5366    * @param string $fieldName
5367    * @param string $dbFieldName
5368    * @param string $fieldTitle
5369    * @param bool $appendTimeStamp
5370    * @param string $dateFormat
5371    */
5372   public function dateQueryBuilder(
5373     &$values, $tableName, $fieldName,
5374     $dbFieldName, $fieldTitle,
5375     $appendTimeStamp = TRUE,
5376     $dateFormat = 'YmdHis'
5377   ) {
5378     list($name, $op, $value, $grouping, $wildcard) = $values;
5379
5380     if ($name == "{$fieldName}_low" ||
5381       $name == "{$fieldName}_high"
5382     ) {
5383       if (isset($this->_rangeCache[$fieldName]) || !$value) {
5384         return;
5385       }
5386       $this->_rangeCache[$fieldName] = 1;
5387
5388       $secondOP = $secondPhrase = $secondValue = $secondDate = $secondDateFormat = NULL;
5389
5390       if ($name == $fieldName . '_low') {
5391         $firstOP = '>=';
5392         $firstPhrase = ts('greater than or equal to');
5393         $firstDate = CRM_Utils_Date::processDate($value, NULL, FALSE, $dateFormat);
5394
5395         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5396         if (!empty($secondValues) && $secondValues[2]) {
5397           $secondOP = '<=';
5398           $secondPhrase = ts('less than or equal to');
5399           $secondValue = $secondValues[2];
5400
5401           if ($appendTimeStamp && strlen($secondValue) == 10) {
5402             $secondValue .= ' 23:59:59';
5403           }
5404           $secondDate = CRM_Utils_Date::processDate($secondValue, NULL, FALSE, $dateFormat);
5405         }
5406       }
5407       elseif ($name == $fieldName . '_high') {
5408         $firstOP = '<=';
5409         $firstPhrase = ts('less than or equal to');
5410
5411         if ($appendTimeStamp && strlen($value) == 10) {
5412           $value .= ' 23:59:59';
5413         }
5414         $firstDate = CRM_Utils_Date::processDate($value, NULL, FALSE, $dateFormat);
5415
5416         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5417         if (!empty($secondValues) && $secondValues[2]) {
5418           $secondOP = '>=';
5419           $secondPhrase = ts('greater than or equal to');
5420           $secondValue = $secondValues[2];
5421           $secondDate = CRM_Utils_Date::processDate($secondValue, NULL, FALSE, $dateFormat);
5422         }
5423       }
5424
5425       if (!$appendTimeStamp) {
5426         $firstDate = substr($firstDate, 0, 8);
5427       }
5428       $firstDateFormat = CRM_Utils_Date::customFormat($firstDate);
5429
5430       if ($secondDate) {
5431         if (!$appendTimeStamp) {
5432           $secondDate = substr($secondDate, 0, 8);
5433         }
5434         $secondDateFormat = CRM_Utils_Date::customFormat($secondDate);
5435       }
5436
5437       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5438       if ($secondDate) {
5439         $this->_where[$grouping][] = "
5440 ( {$tableName}.{$dbFieldName} $firstOP '$firstDate' ) AND
5441 ( {$tableName}.{$dbFieldName} $secondOP '$secondDate' )
5442 ";
5443         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$firstDateFormat\" " . ts('AND') . " $secondPhrase \"$secondDateFormat\"";
5444       }
5445       else {
5446         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP '$firstDate'";
5447         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$firstDateFormat\"";
5448       }
5449     }
5450
5451     if ($name == $fieldName) {
5452       //In Get API, for operators other then '=' the $value is in array(op => value) format
5453       if (is_array($value) && !empty($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
5454         $op = key($value);
5455         $value = $value[$op];
5456       }
5457
5458       $date = $format = NULL;
5459       if (strstr($op, 'IN')) {
5460         $format = array();
5461         foreach ($value as &$date) {
5462           $date = CRM_Utils_Date::processDate($date, NULL, FALSE, $dateFormat);
5463           if (!$appendTimeStamp) {
5464             $date = substr($date, 0, 8);
5465           }
5466           $format[] = CRM_Utils_Date::customFormat($date);
5467         }
5468         $date = "('" . implode("','", $value) . "')";
5469         $format = implode(', ', $format);
5470       }
5471       elseif ($value && (!strstr($op, 'NULL') && !strstr($op, 'EMPTY'))) {
5472         $date = CRM_Utils_Date::processDate($value, NULL, FALSE, $dateFormat);
5473         if (!$appendTimeStamp) {
5474           $date = substr($date, 0, 8);
5475         }
5476         $format = CRM_Utils_Date::customFormat($date);
5477         $date = "'$date'";
5478       }
5479
5480       if ($date) {
5481         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op $date";
5482       }
5483       else {
5484         $this->_where[$grouping][] = self::buildClause("{$tableName}.{$dbFieldName}", $op);
5485       }
5486
5487       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5488
5489       $op = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
5490       $this->_qill[$grouping][] = "$fieldTitle $op $format";
5491     }
5492   }
5493
5494   /**
5495    * @param $values
5496    * @param string $tableName
5497    * @param string $fieldName
5498    * @param string $dbFieldName
5499    * @param $fieldTitle
5500    * @param null $options
5501    */
5502   public function numberRangeBuilder(
5503     &$values,
5504     $tableName, $fieldName,
5505     $dbFieldName, $fieldTitle,
5506     $options = NULL
5507   ) {
5508     list($name, $op, $value, $grouping, $wildcard) = $values;
5509
5510     if ($name == "{$fieldName}_low" ||
5511       $name == "{$fieldName}_high"
5512     ) {
5513       if (isset($this->_rangeCache[$fieldName])) {
5514         return;
5515       }
5516       $this->_rangeCache[$fieldName] = 1;
5517
5518       $secondOP = $secondPhrase = $secondValue = NULL;
5519
5520       if ($name == "{$fieldName}_low") {
5521         $firstOP = '>=';
5522         $firstPhrase = ts('greater than');
5523
5524         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5525         if (!empty($secondValues)) {
5526           $secondOP = '<=';
5527           $secondPhrase = ts('less than');
5528           $secondValue = $secondValues[2];
5529         }
5530       }
5531       else {
5532         $firstOP = '<=';
5533         $firstPhrase = ts('less than');
5534
5535         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5536         if (!empty($secondValues)) {
5537           $secondOP = '>=';
5538           $secondPhrase = ts('greater than');
5539           $secondValue = $secondValues[2];
5540         }
5541       }
5542
5543       if ($secondOP) {
5544         $this->_where[$grouping][] = "
5545 ( {$tableName}.{$dbFieldName} $firstOP {$value} ) AND
5546 ( {$tableName}.{$dbFieldName} $secondOP {$secondValue} )
5547 ";
5548         $displayValue = $options ? $options[$value] : $value;
5549         $secondDisplayValue = $options ? $options[$secondValue] : $secondValue;
5550
5551         $this->_qill[$grouping][]
5552           = "$fieldTitle - $firstPhrase \"$displayValue\" " . ts('AND') . " $secondPhrase \"$secondDisplayValue\"";
5553       }
5554       else {
5555         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP {$value}";
5556         $displayValue = $options ? $options[$value] : $value;
5557         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$displayValue\"";
5558       }
5559       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5560
5561       return;
5562     }
5563
5564     if ($name == $fieldName) {
5565       $op = '=';
5566       $phrase = '=';
5567
5568       $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op {$value}";
5569
5570       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5571       $displayValue = $options ? $options[$value] : $value;
5572       $this->_qill[$grouping][] = "$fieldTitle - $phrase \"$displayValue\"";
5573     }
5574   }
5575
5576
5577   /**
5578    * @param $values
5579    * @param string $tableName
5580    * @param string $fieldName
5581    * @param string $dbFieldName
5582    * @param $fieldTitle
5583    * @param null $options
5584    */
5585   public function ageRangeQueryBuilder(
5586     &$values,
5587     $tableName, $fieldName,
5588     $dbFieldName, $fieldTitle,
5589     $options = NULL
5590   ) {
5591     list($name, $op, $value, $grouping, $wildcard) = $values;
5592
5593     $asofDateValues = $this->getWhereValues("{$fieldName}_asof_date", $grouping);
5594     $asofDate = NULL;  // will be treated as current day
5595     if ($asofDateValues) {
5596       $asofDate = CRM_Utils_Date::processDate($asofDateValues[2]);
5597       $asofDateFormat = CRM_Utils_Date::customFormat(substr($asofDate, 0, 8));
5598       $fieldTitle .= ' ' . ts('as of') . ' ' . $asofDateFormat;
5599     }
5600
5601     if ($name == "{$fieldName}_low" ||
5602       $name == "{$fieldName}_high"
5603     ) {
5604       if (isset($this->_rangeCache[$fieldName])) {
5605         return;
5606       }
5607       $this->_rangeCache[$fieldName] = 1;
5608
5609       $secondOP = $secondPhrase = $secondValue = NULL;
5610
5611       if ($name == "{$fieldName}_low") {
5612         $firstPhrase = ts('greater than or equal to');
5613         // NB: age > X means date of birth < Y
5614         $firstOP = '<=';
5615         $firstDate = self::calcDateFromAge($asofDate, $value, 'min');
5616
5617         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5618         if (!empty($secondValues)) {
5619           $secondOP = '>=';
5620           $secondPhrase = ts('less than or equal to');
5621           $secondValue = $secondValues[2];
5622           $secondDate = self::calcDateFromAge($asofDate, $secondValue, 'max');
5623         }
5624       }
5625       else {
5626         $firstOP = '>=';
5627         $firstPhrase = ts('less than or equal to');
5628         $firstDate = self::calcDateFromAge($asofDate, $value, 'max');
5629
5630         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5631         if (!empty($secondValues)) {
5632           $secondOP = '<=';
5633           $secondPhrase = ts('greater than or equal to');
5634           $secondValue = $secondValues[2];
5635           $secondDate = self::calcDateFromAge($asofDate, $secondValue, 'min');
5636         }
5637       }
5638
5639       if ($secondOP) {
5640         $this->_where[$grouping][] = "
5641 ( {$tableName}.{$dbFieldName} $firstOP '$firstDate' ) AND
5642 ( {$tableName}.{$dbFieldName} $secondOP '$secondDate' )
5643 ";
5644         $displayValue = $options ? $options[$value] : $value;
5645         $secondDisplayValue = $options ? $options[$secondValue] : $secondValue;
5646
5647         $this->_qill[$grouping][]
5648           = "$fieldTitle - $firstPhrase \"$displayValue\" " . ts('AND') . " $secondPhrase \"$secondDisplayValue\"";
5649       }
5650       else {
5651         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP '$firstDate'";
5652         $displayValue = $options ? $options[$value] : $value;
5653         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$displayValue\"";
5654       }
5655       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5656       return;
5657     }
5658   }
5659
5660   /**
5661    * Calculate date from age.
5662    *
5663    * @param string $asofDate
5664    * @param int $age
5665    * @param string $type
5666    *
5667    * @return string
5668    */
5669   public static function calcDateFromAge($asofDate, $age, $type) {
5670     $date = new DateTime($asofDate);
5671     if ($type == "min") {
5672       // minimum age is $age: dob <= date - age "235959"
5673       $date->sub(new DateInterval("P" . $age . "Y"));
5674       return $date->format('Ymd') . "235959";
5675     }
5676     else {
5677       // max age is $age: dob >= date - (age + 1y) + 1d "000000"
5678       $date->sub(new DateInterval("P" . ($age + 1) . "Y"))->add(new DateInterval("P1D"));
5679       return $date->format('Ymd') . "000000";
5680     }
5681   }
5682
5683   /**
5684    * Given the field name, operator, value & its data type
5685    * builds the where Clause for the query
5686    * used for handling 'IS NULL'/'IS NOT NULL' operators
5687    *
5688    * @param string $field
5689    *   Fieldname.
5690    * @param string $op
5691    *   Operator.
5692    * @param string $value
5693    *   Value.
5694    * @param string $dataType
5695    *   Data type of the field.
5696    *
5697    * @return string
5698    *   Where clause for the query.
5699    */
5700   public static function buildClause($field, $op, $value = NULL, $dataType = NULL) {
5701     $op = trim($op);
5702     $clause = "$field $op";
5703
5704     switch ($op) {
5705       case 'IS NULL':
5706       case 'IS NOT NULL':
5707         return $clause;
5708
5709       case 'IS EMPTY':
5710         $clause = ($dataType == 'Date') ? " $field IS NULL " : " (NULLIF($field, '') IS NULL) ";
5711         return $clause;
5712
5713       case 'IS NOT EMPTY':
5714         $clause = ($dataType == 'Date') ? " $field IS NOT NULL " : " (NULLIF($field, '') IS NOT NULL) ";
5715         return $clause;
5716
5717       case 'RLIKE':
5718         return " {$clause} BINARY '{$value}' ";
5719
5720       case 'IN':
5721       case 'NOT IN':
5722         // I feel like this would be escaped properly if passed through $queryString = CRM_Core_DAO::createSqlFilter.
5723         if (!empty($value) && (!is_array($value) || !array_key_exists($op, $value))) {
5724           $value = array($op => (array) $value);
5725         }
5726
5727       default:
5728         if (empty($dataType) || $dataType == 'Date') {
5729           $dataType = 'String';
5730         }
5731         if (is_array($value)) {
5732           //this could have come from the api - as in the restWhere section we potentially use the api operator syntax which is becoming more
5733           // widely used and consistent across the codebase
5734           // adding this here won't accept the search functions which don't submit an array
5735           if (($queryString = CRM_Core_DAO::createSqlFilter($field, $value, $dataType)) != FALSE) {
5736
5737             return $queryString;
5738           }
5739           if (!empty($value[0]) && $op === 'BETWEEN') {
5740             CRM_Core_Error::deprecatedFunctionWarning('Fix search input params');
5741             if (($queryString = CRM_Core_DAO::createSqlFilter($field, array($op => $value), $dataType)) != FALSE) {
5742               return $queryString;
5743             }
5744           }
5745           throw new CRM_Core_Exception(ts('Failed to interpret input for search'));
5746         }
5747
5748         $value = CRM_Utils_Type::escape($value, $dataType);
5749         // if we don't have a dataType we should assume
5750         if ($dataType == 'String' || $dataType == 'Text') {
5751           $value = "'" . $value . "'";
5752         }
5753         return "$clause $value";
5754     }
5755   }
5756
5757   /**
5758    * @param bool $reset
5759    *
5760    * @return array
5761    */
5762   public function openedSearchPanes($reset = FALSE) {
5763     if (!$reset || empty($this->_whereTables)) {
5764       return self::$_openedPanes;
5765     }
5766
5767     // pane name to table mapper
5768     $panesMapper = array(
5769       ts('Contributions') => 'civicrm_contribution',
5770       ts('Memberships') => 'civicrm_membership',
5771       ts('Events') => 'civicrm_participant',
5772       ts('Relationships') => 'civicrm_relationship',
5773       ts('Activities') => 'civicrm_activity',
5774       ts('Pledges') => 'civicrm_pledge',
5775       ts('Cases') => 'civicrm_case',
5776       ts('Grants') => 'civicrm_grant',
5777       ts('Address Fields') => 'civicrm_address',
5778       ts('Notes') => 'civicrm_note',
5779       ts('Change Log') => 'civicrm_log',
5780       ts('Mailings') => 'civicrm_mailing',
5781     );
5782     CRM_Contact_BAO_Query_Hook::singleton()->getPanesMapper($panesMapper);
5783
5784     foreach (array_keys($this->_whereTables) as $table) {
5785       if ($panName = array_search($table, $panesMapper)) {
5786         self::$_openedPanes[$panName] = TRUE;
5787       }
5788     }
5789
5790     return self::$_openedPanes;
5791   }
5792
5793   /**
5794    * @param $operator
5795    */
5796   public function setOperator($operator) {
5797     $validOperators = array('AND', 'OR');
5798     if (!in_array($operator, $validOperators)) {
5799       $operator = 'AND';
5800     }
5801     $this->_operator = $operator;
5802   }
5803
5804   /**
5805    * @return string
5806    */
5807   public function getOperator() {
5808     return $this->_operator;
5809   }
5810
5811   /**
5812    * @param $from
5813    * @param $where
5814    * @param $having
5815    */
5816   public function filterRelatedContacts(&$from, &$where, &$having) {
5817     if (!isset(Civi::$statics[__CLASS__]['related_contacts_filter'])) {
5818       Civi::$statics[__CLASS__]['related_contacts_filter'] = array();
5819     }
5820     $_rTempCache =& Civi::$statics[__CLASS__]['related_contacts_filter'];
5821     // since there only can be one instance of this filter in every query
5822     // skip if filter has already applied
5823     foreach ($_rTempCache as $acache) {
5824       foreach ($acache['queries'] as $aqcache) {
5825         if (strpos($from, $aqcache['from']) !== FALSE) {
5826           $having = NULL;
5827           return;
5828         }
5829       }
5830     }
5831     $arg_sig = sha1("$from $where $having");
5832     if (isset($_rTempCache[$arg_sig])) {
5833       $cache = $_rTempCache[$arg_sig];
5834     }
5835     else {
5836       // create temp table with contact ids
5837       $tableName = CRM_Core_DAO::createTempTableName('civicrm_transform', TRUE);
5838
5839       $sql = "CREATE TEMPORARY TABLE $tableName ( contact_id int primary key) ENGINE=HEAP";
5840       CRM_Core_DAO::executeQuery($sql);
5841
5842       $sql = "
5843 REPLACE INTO $tableName ( contact_id )
5844 SELECT contact_a.id
5845        $from
5846        $where
5847        $having
5848 ";
5849       CRM_Core_DAO::executeQuery($sql);
5850
5851       $cache = array('tableName' => $tableName, 'queries' => array());
5852       $_rTempCache[$arg_sig] = $cache;
5853     }
5854     // upsert the query depending on relationship type
5855     if (isset($cache['queries'][$this->_displayRelationshipType])) {
5856       $qcache = $cache['queries'][$this->_displayRelationshipType];
5857     }
5858     else {
5859       $tableName = $cache['tableName'];
5860       $qcache = array(
5861         "from" => "",
5862         "where" => "",
5863       );
5864       $rTypes = CRM_Core_PseudoConstant::relationshipType();
5865       if (is_numeric($this->_displayRelationshipType)) {
5866         $relationshipTypeLabel = $rTypes[$this->_displayRelationshipType]['label_a_b'];
5867         $qcache['from'] = "
5868 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id OR displayRelType.contact_id_b = contact_a.id )
5869 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a OR transform_temp.contact_id = displayRelType.contact_id_b )
5870 ";
5871         $qcache['where'] = "
5872 WHERE displayRelType.relationship_type_id = {$this->_displayRelationshipType}
5873 AND   displayRelType.is_active = 1
5874 ";
5875       }
5876       else {
5877         list($relType, $dirOne, $dirTwo) = explode('_', $this->_displayRelationshipType);
5878         if ($dirOne == 'a') {
5879           $relationshipTypeLabel = $rTypes[$relType]['label_a_b'];
5880           $qcache['from'] .= "
5881 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id )
5882 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_b )
5883 ";
5884         }
5885         else {
5886           $relationshipTypeLabel = $rTypes[$relType]['label_b_a'];
5887           $qcache['from'] .= "
5888 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_b = contact_a.id )
5889 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a )
5890 ";
5891         }
5892         $qcache['where'] = "
5893 WHERE displayRelType.relationship_type_id = $relType
5894 AND   displayRelType.is_active = 1
5895 ";
5896       }
5897       $qcache['relTypeLabel'] = $relationshipTypeLabel;
5898       $_rTempCache[$arg_sig]['queries'][$this->_displayRelationshipType] = $qcache;
5899     }
5900     $qillMessage = ts('Contacts with a Relationship Type of: ');
5901     $iqill = $qillMessage . "'" . $qcache['relTypeLabel'] . "'";
5902     if (!is_array($this->_qill[0]) || !in_array($iqill, $this->_qill[0])) {
5903       $this->_qill[0][] = $iqill;
5904     }
5905     if (strpos($from, $qcache['from']) === FALSE) {
5906       // lets replace all the INNER JOIN's in the $from so we dont exclude other data
5907       // this happens when we have an event_type in the quert (CRM-7969)
5908       $from = str_replace("INNER JOIN", "LEFT JOIN", $from);
5909       $from .= $qcache['from'];
5910       $where = $qcache['where'];
5911       if (!empty($this->_permissionWhereClause)) {
5912         $where .= "AND $this->_permissionWhereClause";
5913       }
5914     }
5915
5916     $having = NULL;
5917   }
5918
5919   /**
5920    * See CRM-19811 for why this is database hurty without apparent benefit.
5921    *
5922    * @param $op
5923    *
5924    * @return bool
5925    */
5926   public static function caseImportant($op) {
5927     return
5928       in_array($op, array('LIKE', 'IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY')) ? FALSE : TRUE;
5929   }
5930
5931   /**
5932    * @param $returnProperties
5933    * @param $prefix
5934    *
5935    * @return bool
5936    */
5937   public static function componentPresent(&$returnProperties, $prefix) {
5938     foreach ($returnProperties as $name => $dontCare) {
5939       if (substr($name, 0, strlen($prefix)) == $prefix) {
5940         return TRUE;
5941       }
5942     }
5943     return FALSE;
5944   }
5945
5946   /**
5947    * Builds the necessary structures for all fields that are similar to option value look-ups.
5948    *
5949    * @param string $name
5950    *   the name of the field.
5951    * @param string $op
5952    *   the sql operator, this function should handle ALL SQL operators.
5953    * @param string $value
5954    *   depends on the operator and who's calling the query builder.
5955    * @param int $grouping
5956    *   the index where to place the where clause.
5957    * @param string $daoName
5958    *   DAO Name.
5959    * @param array $field
5960    *   an array that contains various properties of the field identified by $name.
5961    * @param string $label
5962    *   The label for this field element.
5963    * @param string $dataType
5964    *   The data type for this element.
5965    * @param bool $useIDsOnly
5966    */
5967   public function optionValueQuery(
5968     $name,
5969     $op,
5970     $value,
5971     $grouping,
5972     $daoName = NULL,
5973     $field,
5974     $label,
5975     $dataType = 'String',
5976     $useIDsOnly = FALSE
5977   ) {
5978
5979     $pseudoFields = array(
5980       'email_greeting',
5981       'postal_greeting',
5982       'addressee',
5983       'gender_id',
5984       'prefix_id',
5985       'suffix_id',
5986       'communication_style_id',
5987     );
5988
5989     if ($useIDsOnly) {
5990       list($tableName, $fieldName) = explode('.', $field['where'], 2);
5991       if ($tableName == 'civicrm_contact') {
5992         $wc = "contact_a.$fieldName";
5993       }
5994       else {
5995         // Special handling for on_hold, so that we actually use the 'where'
5996         // property in order to limit the query by the on_hold status of the email,
5997         // instead of using email.id which would be nonsensical.
5998         if ($field['name'] == 'on_hold') {
5999           $wc = "{$field['where']}";
6000         }
6001         else {
6002           $wc = "$tableName.id";
6003         }
6004       }
6005     }
6006     else {
6007       CRM_Core_Error::deprecatedFunctionWarning('pass $ids to this method');
6008       $wc = "{$field['where']}";
6009     }
6010     if (in_array($name, $pseudoFields)) {
6011       if (!in_array($name, array('gender_id', 'prefix_id', 'suffix_id', 'communication_style_id'))) {
6012         $wc = "contact_a.{$name}_id";
6013       }
6014       $dataType = 'Positive';
6015       $value = (!$value) ? 0 : $value;
6016     }
6017     if ($name == "world_region") {
6018       $field['name'] = $name;
6019     }
6020
6021     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue($daoName, $field['name'], $value, $op);
6022     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $label, 2 => $qillop, 3 => $qillVal));
6023     $this->_where[$grouping][] = self::buildClause($wc, $op, $value, $dataType);
6024   }
6025
6026   /**
6027    * Check and explode a user defined numeric string into an array
6028    * this was the protocol used by search builder in the old old days before we had
6029    * super nice js widgets to do the hard work
6030    *
6031    * @param string $string
6032    * @param string $dataType
6033    *   The dataType we should check for the values, default integer.
6034    *
6035    * @return bool|array
6036    *   false if string does not match the pattern
6037    *   array of numeric values if string does match the pattern
6038    */
6039   public static function parseSearchBuilderString($string, $dataType = 'Integer') {
6040     $string = trim($string);
6041     if (substr($string, 0, 1) != '(' || substr($string, -1, 1) != ')') {
6042       Return FALSE;
6043     }
6044
6045     $string = substr($string, 1, -1);
6046     $values = explode(',', $string);
6047     if (empty($values)) {
6048       return FALSE;
6049     }
6050
6051     $returnValues = array();
6052     foreach ($values as $v) {
6053       if ($dataType == 'Integer' && !is_numeric($v)) {
6054         return FALSE;
6055       }
6056       elseif ($dataType == 'String' && !is_string($v)) {
6057         return FALSE;
6058       }
6059       $returnValues[] = trim($v);
6060     }
6061
6062     if (empty($returnValues)) {
6063       return FALSE;
6064     }
6065
6066     return $returnValues;
6067   }
6068
6069   /**
6070    * Convert the pseudo constants id's to their names
6071    *
6072    * @param CRM_Core_DAO $dao
6073    * @param bool $return
6074    * @param bool $usedForAPI
6075    *
6076    * @return array|NULL
6077    */
6078   public function convertToPseudoNames(&$dao, $return = FALSE, $usedForAPI = FALSE) {
6079     if (empty($this->_pseudoConstantsSelect)) {
6080       return NULL;
6081     }
6082     $values = array();
6083     foreach ($this->_pseudoConstantsSelect as $key => $value) {
6084       if (!empty($this->_pseudoConstantsSelect[$key]['sorting'])) {
6085         continue;
6086       }
6087
6088       if (is_object($dao) && property_exists($dao, $value['idCol'])) {
6089         $val = $dao->{$value['idCol']};
6090         if ($key == 'groups') {
6091           $dao->groups = $this->convertGroupIDStringToLabelString($dao, $val);
6092           continue;
6093         }
6094
6095         if (CRM_Utils_System::isNull($val)) {
6096           $dao->$key = NULL;
6097         }
6098         elseif (!empty($value['pseudoconstant'])) {
6099           // If pseudoconstant is set that is kind of defacto for 'we have a bit more info about this'
6100           // and we can use the metadata to figure it out.
6101           // ideally this bit of IF will absorb & replace all the rest in time as we move to
6102           // more metadata based choices.
6103           if (strpos($val, CRM_Core_DAO::VALUE_SEPARATOR) !== FALSE) {
6104             $dbValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, trim($val, CRM_Core_DAO::VALUE_SEPARATOR));
6105             foreach ($dbValues as $pseudoValue) {
6106               $convertedValues[] = CRM_Core_PseudoConstant::getLabel($value['bao'], $value['idCol'], $pseudoValue);
6107             }
6108
6109             $dao->$key = ($usedForAPI) ? $convertedValues : implode(', ', $convertedValues);
6110             $realFieldName = CRM_Utils_Array::value('field_name', $this->_pseudoConstantsSelect[$key]);
6111             if ($usedForAPI && $realFieldName) {
6112               // normally we would see 2 fields returned for pseudoConstants. An exception is
6113               // preferred_communication_method where there is no id-variant.
6114               // For the api we prioritise getting the real data returned.
6115               // over the resolved version
6116               $dao->$realFieldName = $dbValues;
6117             }
6118
6119           }
6120           else {
6121             // This is basically the same as the default but since we have the bao we can use
6122             // a cached function.
6123             $dao->$key = CRM_Core_PseudoConstant::getLabel($value['bao'], $value['idCol'], $val);
6124           }
6125         }
6126         elseif ($baoName = CRM_Utils_Array::value('bao', $value, NULL)) {
6127           //preserve id value
6128           $idColumn = "{$key}_id";
6129           $dao->$idColumn = $val;
6130
6131           if ($key == 'state_province_name') {
6132             $dao->{$value['pseudoField']} = $dao->$key = CRM_Core_PseudoConstant::stateProvince($val);
6133           }
6134           else {
6135             $dao->{$value['pseudoField']} = $dao->$key = CRM_Core_PseudoConstant::getLabel($baoName, $value['pseudoField'], $val);
6136           }
6137         }
6138         elseif ($value['pseudoField'] == 'state_province_abbreviation') {
6139           $dao->$key = CRM_Core_PseudoConstant::stateProvinceAbbreviation($val);
6140         }
6141         // @todo handle this in the section above for pseudoconstants.
6142         elseif (in_array($value['pseudoField'], array('participant_role_id', 'participant_role'))) {
6143           // @todo define bao on this & merge into the above condition.
6144           $viewValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $val);
6145
6146           if ($value['pseudoField'] == 'participant_role') {
6147             $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', 'role_id');
6148             foreach ($viewValues as $k => $v) {
6149               $viewValues[$k] = $pseudoOptions[$v];
6150             }
6151           }
6152           $dao->$key = ($usedForAPI && count($viewValues) > 1) ? $viewValues : implode(', ', $viewValues);
6153         }
6154         else {
6155           $labels = CRM_Core_OptionGroup::values($value['pseudoField']);
6156           $dao->$key = $labels[$val];
6157         }
6158
6159         // return converted values in array format
6160         if ($return) {
6161           if (strpos($key, '-') !== FALSE) {
6162             $keyVal = explode('-', $key);
6163             $current = &$values;
6164             $lastElement = array_pop($keyVal);
6165             foreach ($keyVal as $v) {
6166               if (!array_key_exists($v, $current)) {
6167                 $current[$v] = array();
6168               }
6169               $current = &$current[$v];
6170             }
6171             $current[$lastElement] = $dao->$key;
6172           }
6173           else {
6174             $values[$key] = $dao->$key;
6175           }
6176         }
6177       }
6178     }
6179     if (!$usedForAPI) {
6180       foreach (array(
6181          'gender_id' => 'gender',
6182           'prefix_id' => 'individual_prefix',
6183           'suffix_id' => 'individual_suffix',
6184           'communication_style_id' => 'communication_style',
6185         ) as $realField => $labelField) {
6186         // This is a temporary routine for handling these fields while
6187         // we figure out how to handled them based on metadata in
6188         /// export and search builder. CRM-19815, CRM-19830.
6189         if (isset($dao->$realField) && is_numeric($dao->$realField) && isset($dao->$labelField)) {
6190           $dao->$realField = $dao->$labelField;
6191         }
6192       }
6193     }
6194     return $values;
6195   }
6196
6197   /**
6198    * Include pseudo fields LEFT JOIN.
6199    * @param string|array $sort can be a object or string
6200    *
6201    * @return array|NULL
6202    */
6203   public function includePseudoFieldsJoin($sort) {
6204     if (!$sort || empty($this->_pseudoConstantsSelect)) {
6205       return NULL;
6206     }
6207     $sort = is_string($sort) ? $sort : $sort->orderBy();
6208     $present = array();
6209
6210     foreach ($this->_pseudoConstantsSelect as $name => $value) {
6211       if (!empty($value['table'])) {
6212         $regex = "/({$value['table']}\.|{$name})/";
6213         if (preg_match($regex, $sort)) {
6214           $this->_elemnt[$value['element']] = 1;
6215           $this->_select[$value['element']] = $value['select'];
6216           $this->_pseudoConstantsSelect[$name]['sorting'] = 1;
6217           $present[$value['table']] = $value['join'];
6218         }
6219       }
6220     }
6221     $presentSimpleFrom = $present;
6222
6223     if (array_key_exists('civicrm_worldregion', $this->_whereTables) &&
6224       array_key_exists('civicrm_country', $presentSimpleFrom)
6225     ) {
6226       unset($presentSimpleFrom['civicrm_country']);
6227     }
6228     if (array_key_exists('civicrm_worldregion', $this->_tables) &&
6229       array_key_exists('civicrm_country', $present)
6230     ) {
6231       unset($present['civicrm_country']);
6232     }
6233
6234     $presentClause = $presentSimpleFromClause = NULL;
6235     if (!empty($present)) {
6236       $presentClause = implode(' ', $present);
6237     }
6238     if (!empty($presentSimpleFrom)) {
6239       $presentSimpleFromClause = implode(' ', $presentSimpleFrom);
6240     }
6241
6242     $this->_fromClause = $this->_fromClause . $presentClause;
6243     $this->_simpleFromClause = $this->_simpleFromClause . $presentSimpleFromClause;
6244
6245     return array($presentClause, $presentSimpleFromClause);
6246   }
6247
6248   /**
6249    * Build qill for field.
6250    *
6251    * Qill refers to the query detail visible on the UI.
6252    *
6253    * @param string $daoName
6254    * @param string $fieldName
6255    * @param mixed $fieldValue
6256    * @param string $op
6257    * @param array $pseudoExtraParam
6258    * @param int $type
6259    *   Type of the field per CRM_Utils_Type
6260    *
6261    * @return array
6262    */
6263   public static function buildQillForFieldValue(
6264     $daoName,
6265     $fieldName,
6266     $fieldValue,
6267     $op,
6268     $pseudoExtraParam = array(),
6269     $type = CRM_Utils_Type::T_STRING
6270   ) {
6271     $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
6272
6273     //API usually have fieldValue format as array(operator => array(values)),
6274     //so we need to separate operator out of fieldValue param
6275     if (is_array($fieldValue) && in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
6276       $op = key($fieldValue);
6277       $fieldValue = $fieldValue[$op];
6278     }
6279
6280     // if Operator chosen is NULL/EMPTY then
6281     if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) {
6282       return array(CRM_Utils_Array::value($op, $qillOperators, $op), '');
6283     }
6284
6285     if ($fieldName == 'activity_type_id') {
6286       $pseudoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
6287     }
6288     elseif ($fieldName == 'country_id') {
6289       $pseudoOptions = CRM_Core_PseudoConstant::country();
6290     }
6291     elseif ($fieldName == 'county_id') {
6292       $pseudoOptions = CRM_Core_PseudoConstant::county();
6293     }
6294     elseif ($fieldName == 'world_region') {
6295       $pseudoOptions = CRM_Core_PseudoConstant::worldRegion();
6296     }
6297     elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
6298       $checkPermission = CRM_Utils_Array::value('check_permission', $pseudoExtraParam, TRUE);
6299       $pseudoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, $checkPermission, TRUE);
6300     }
6301     elseif ($fieldName == 'contribution_product_id') {
6302       $pseudoOptions = CRM_Contribute_PseudoConstant::products();
6303     }
6304     elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') {
6305       $pseudoOptions = CRM_Core_PseudoConstant::group();
6306     }
6307     elseif ($daoName == 'CRM_Batch_BAO_EntityBatch' && $fieldName == 'batch_id') {
6308       $pseudoOptions = CRM_Contribute_PseudoConstant::batch();
6309     }
6310     elseif ($daoName) {
6311       $pseudoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseudoExtraParam);
6312     }
6313
6314     if (is_array($fieldValue)) {
6315       $qillString = array();
6316       if (!empty($pseudoOptions)) {
6317         foreach ((array) $fieldValue as $val) {
6318           $qillString[] = CRM_Utils_Array::value($val, $pseudoOptions, $val);
6319         }
6320         $fieldValue = implode(', ', $qillString);
6321       }
6322       else {
6323         if ($type == CRM_Utils_Type::T_DATE) {
6324           foreach ($fieldValue as $index => $value) {
6325             $fieldValue[$index] = CRM_Utils_Date::customFormat($value);
6326           }
6327         }
6328         $separator = ', ';
6329         // @todo - this is a bit specific (one operator).
6330         // However it is covered by a unit test so can be altered later with
6331         // some confidence.
6332         if ($op == 'BETWEEN') {
6333           $separator = ' AND ';
6334         }
6335         $fieldValue = implode($separator, $fieldValue);
6336       }
6337     }
6338     elseif (!empty($pseudoOptions) && array_key_exists($fieldValue, $pseudoOptions)) {
6339       $fieldValue = $pseudoOptions[$fieldValue];
6340     }
6341     elseif ($type === CRM_Utils_Type::T_DATE) {
6342       $fieldValue = CRM_Utils_Date::customFormat($fieldValue);
6343     }
6344
6345     return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue);
6346   }
6347
6348   /**
6349    * Alter value to reflect wildcard settings.
6350    *
6351    * The form will have tried to guess whether this is a good field to wildcard but there is
6352    * also a site-wide setting that specifies whether it is OK to append the wild card to the beginning
6353    * or only the end of the string
6354    *
6355    * @param bool $wildcard
6356    *   This is a bool made on an assessment 'elsewhere' on whether this is a good field to wildcard.
6357    * @param string $op
6358    *   Generally '=' or 'LIKE'.
6359    * @param string $value
6360    *   The search string.
6361    *
6362    * @return string
6363    */
6364   public static function getWildCardedValue($wildcard, $op, $value) {
6365     if ($wildcard && $op == 'LIKE') {
6366       if (CRM_Core_Config::singleton()->includeWildCardInName && (substr($value, 0, 1) != '%')) {
6367         return "%$value%";
6368       }
6369       else {
6370         return "$value%";
6371       }
6372     }
6373     else {
6374       return "$value";
6375     }
6376   }
6377
6378   /**
6379    * Process special fields of Search Form in OK (Operator in Key) format
6380    *
6381    * @param array $formValues
6382    * @param array $specialFields
6383    *    Special params to be processed
6384    * @param array $changeNames
6385    *   Array of fields whose name should be changed
6386    */
6387   public static function processSpecialFormValue(&$formValues, $specialFields, $changeNames = array()) {
6388     // Array of special fields whose value are considered only for NULL or EMPTY operators
6389     $nullableFields = array('contribution_batch_id');
6390
6391     foreach ($specialFields as $element) {
6392       $value = CRM_Utils_Array::value($element, $formValues);
6393       if ($value) {
6394         if (is_array($value)) {
6395           if (in_array($element, array_keys($changeNames))) {
6396             unset($formValues[$element]);
6397             $element = $changeNames[$element];
6398           }
6399           $formValues[$element] = array('IN' => $value);
6400         }
6401         elseif (in_array($value, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
6402           $formValues[$element] = array($value => 1);
6403         }
6404         elseif (!in_array($element, $nullableFields)) {
6405           // if wildcard is already present return searchString as it is OR append and/or prepend with wildcard
6406           $isWilcard = strstr($value, '%') ? FALSE : CRM_Core_Config::singleton()->includeWildCardInName;
6407           $formValues[$element] = array('LIKE' => self::getWildCardedValue($isWilcard, 'LIKE', $value));
6408         }
6409       }
6410     }
6411   }
6412
6413   /**
6414    * Parse and assimilate the various sort options.
6415    *
6416    * Side-effect: if sorting on a common column from a related table (`city`, `postal_code`,
6417    * `email`), the related table may be joined automatically.
6418    *
6419    * At time of writing, this code is deeply flawed and should be rewritten. For the moment,
6420    * it's been extracted to a standalone function.
6421    *
6422    * @param string|CRM_Utils_Sort $sort
6423    *   The order by string.
6424    * @param bool $sortByChar
6425    *   If true returns the distinct array of first characters for search results.
6426    * @param null $sortOrder
6427    *   Who knows? Hu knows. He who knows Hu knows who.
6428    * @param string $additionalFromClause
6429    *   Should be clause with proper joins, effective to reduce where clause load.
6430    * @return array
6431    *   list(string $orderByClause, string $additionalFromClause).
6432    */
6433   protected function prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause) {
6434     $order = NULL;
6435     $orderByArray = array();
6436     $config = CRM_Core_Config::singleton();
6437     if ($config->includeOrderByClause ||
6438       isset($this->_distinctComponentClause)
6439     ) {
6440       if ($sort) {
6441         if (is_string($sort)) {
6442           $orderBy = $sort;
6443         }
6444         else {
6445           $orderBy = trim($sort->orderBy());
6446         }
6447         // Deliberately remove the backticks again, as they mess up the evil
6448         // string munging below. This balanced by re-escaping before use.
6449         $orderBy = str_replace('`', '', $orderBy);
6450
6451         if (!empty($orderBy)) {
6452           // this is special case while searching for
6453           // change log CRM-1718
6454           if (preg_match('/sort_name/i', $orderBy)) {
6455             $orderBy = str_replace('sort_name', 'contact_a.sort_name', $orderBy);
6456           }
6457
6458           $order = " ORDER BY $orderBy";
6459
6460           if ($sortOrder) {
6461             $order .= " $sortOrder";
6462           }
6463
6464           // always add contact_a.id to the ORDER clause
6465           // so the order is deterministic
6466           if (strpos('contact_a.id', $order) === FALSE) {
6467             $order .= ", contact_a.id";
6468           }
6469         }
6470       }
6471       elseif ($sortByChar) {
6472         $orderByArray = array("UPPER(LEFT(contact_a.sort_name, 1)) asc");
6473       }
6474       else {
6475         $order = " ORDER BY contact_a.sort_name ASC, contact_a.id";
6476       }
6477     }
6478     if (!$order && empty($orderByArray)) {
6479       return array($order, $additionalFromClause);
6480     }
6481     // Remove this here & add it at the end for simplicity.
6482     $order = trim(str_replace('ORDER BY', '', $order));
6483
6484     // hack for order clause
6485     if (!empty($orderByArray)) {
6486       $order = implode(', ', $orderByArray);
6487     }
6488     else {
6489       $orderByArray = explode(',', $order);
6490     }
6491     foreach ($orderByArray as $orderByClause) {
6492       $orderByClauseParts = explode(' ', trim($orderByClause));
6493       $field = $orderByClauseParts[0];
6494       $direction = isset($orderByClauseParts[1]) ? $orderByClauseParts[1] : 'asc';
6495
6496       switch ($field) {
6497         case 'city':
6498         case 'postal_code':
6499           $this->_tables["civicrm_address"] = $this->_whereTables["civicrm_address"] = 1;
6500           $order = str_replace($field, "civicrm_address.{$field}", $order);
6501           break;
6502
6503         case 'country':
6504         case 'state_province':
6505           $this->_tables["civicrm_{$field}"] = $this->_whereTables["civicrm_{$field}"] = 1;
6506           if (is_array($this->_returnProperties) && empty($this->_returnProperties)) {
6507             $additionalFromClause .= " LEFT JOIN civicrm_{$field} ON civicrm_{$field}.id = civicrm_address.{$field}_id";
6508           }
6509           $order = str_replace($field, "civicrm_{$field}.name", $order);
6510           break;
6511
6512         case 'email':
6513           $this->_tables["civicrm_email"] = $this->_whereTables["civicrm_email"] = 1;
6514           $order = str_replace($field, "civicrm_email.{$field}", $order);
6515           break;
6516
6517         default:
6518           $cfID = CRM_Core_BAO_CustomField::getKeyID($field);
6519           // add to cfIDs array if not present
6520           if (!empty($cfID) && !array_key_exists($cfID, $this->_cfIDs)) {
6521             $this->_cfIDs[$cfID] = array();
6522             $this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE, $this->_locationSpecificCustomFields);
6523             $this->_customQuery->query();
6524             $this->_select = array_merge($this->_select, $this->_customQuery->_select);
6525             $this->_tables = array_merge($this->_tables, $this->_customQuery->_tables);
6526           }
6527           foreach ($this->_pseudoConstantsSelect as $key => $pseudoConstantMetadata) {
6528             // By replacing the join to the option value table with the mysql construct
6529             // ORDER BY field('contribution_status_id', 2,1,4)
6530             // we can remove a join. In the case of the option value join it is
6531             /// a join known to cause slow queries.
6532             // @todo cover other pseudoconstant types. Limited to option group ones in the
6533             // first instance for scope reasons. They require slightly different handling as the column (label)
6534             // is not declared for them.
6535             // @todo so far only integer fields are being handled. If we add string fields we need to look at
6536             // escaping.
6537             if (isset($pseudoConstantMetadata['pseudoconstant'])
6538               && isset($pseudoConstantMetadata['pseudoconstant']['optionGroupName'])
6539               && $field === CRM_Utils_Array::value('optionGroupName', $pseudoConstantMetadata['pseudoconstant'])
6540             ) {
6541               $sortedOptions = $pseudoConstantMetadata['bao']::buildOptions($pseudoConstantMetadata['pseudoField'], NULL, array(
6542                 'orderColumn' => 'label',
6543               ));
6544               $order = str_replace("$field $direction", "field({$pseudoConstantMetadata['pseudoField']}," . implode(',', array_keys($sortedOptions)) . ") $direction", $order);
6545             }
6546             //CRM-12565 add "`" around $field if it is a pseudo constant
6547             // This appears to be for 'special' fields like locations with appended numbers or hyphens .. maybe.
6548             if (!empty($pseudoConstantMetadata['element']) && $pseudoConstantMetadata['element'] == $field) {
6549               $order = str_replace($field, "`{$field}`", $order);
6550             }
6551           }
6552       }
6553     }
6554
6555     $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
6556     $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
6557
6558     // The above code relies on crazy brittle string manipulation of a peculiarly-encoded ORDER BY
6559     // clause. But this magic helper which forgivingly reescapes ORDER BY.
6560     // Note: $sortByChar implies that $order was hard-coded/trusted, so it can do funky things.
6561     if ($sortByChar) {
6562       return array(' ORDER BY ' . $order, $additionalFromClause);
6563     }
6564     if ($order) {
6565       $order = CRM_Utils_Type::escape($order, 'MysqlOrderBy');
6566       return array(' ORDER BY ' . $order, $additionalFromClause);
6567     }
6568   }
6569
6570   /**
6571    * Convert a string of group IDs to a string of group labels.
6572    *
6573    * The original string may include duplicates and groups the user does not have
6574    * permission to see.
6575    *
6576    * @param CRM_Core_DAO $dao
6577    * @param string $val
6578    *
6579    * @return string
6580    */
6581   public function convertGroupIDStringToLabelString(&$dao, $val) {
6582     $groupIDs = explode(',', $val);
6583     // Note that groups that the user does not have permission to will be excluded (good).
6584     $groups = array_intersect_key(CRM_Core_PseudoConstant::group(), array_flip($groupIDs));
6585     return implode(', ', $groups);
6586
6587   }
6588
6589   /**
6590    * Set the qill and where properties for a field.
6591    *
6592    * This function is intended as a short-term function to encourage refactoring
6593    * & re-use - but really we should just have less special-casing.
6594    *
6595    * @param string $name
6596    * @param string $op
6597    * @param string|array $value
6598    * @param string $grouping
6599    * @param string $field
6600    */
6601   public function setQillAndWhere($name, $op, $value, $grouping, $field) {
6602     $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
6603     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
6604     $this->_qill[$grouping][] = ts("%1 %2 %3", array(
6605       1 => $field['title'],
6606       2 => $qillop,
6607       3 => $qillVal,
6608     ));
6609   }
6610
6611   /**
6612    * Has the pseudoconstant of the field been requested.
6613    *
6614    * For example if the field is payment_instrument_id then it
6615    * has been requested if either payment_instrument_id or payment_instrument
6616    * have been requested. Payment_instrument is the option groun name field value.
6617    *
6618    * @param array $field
6619    * @param string $fieldName
6620    *   The unique name of the field - ie. the one it will be aliased to in the query.
6621    *
6622    * @return bool
6623    */
6624   private function pseudoConstantNameIsInReturnProperties($field, $fieldName = NULL) {
6625     if (!isset($field['pseudoconstant']['optionGroupName'])) {
6626       return FALSE;
6627     }
6628
6629     if (CRM_Utils_Array::value($field['pseudoconstant']['optionGroupName'], $this->_returnProperties)) {
6630       return TRUE;
6631     }
6632     if (CRM_Utils_Array::value($fieldName, $this->_returnProperties)) {
6633       return TRUE;
6634     }
6635     // Is this still required - the above goes off the unique name. Test with things like
6636     // communication_prefferences & prefix_id.
6637     if (CRM_Utils_Array::value($field['name'], $this->_returnProperties)) {
6638       return TRUE;
6639     }
6640     return FALSE;
6641   }
6642
6643   /**
6644    * Get Select Clause.
6645    *
6646    * @return string
6647    */
6648   public function getSelect() {
6649     $select = "SELECT ";
6650     if (isset($this->_distinctComponentClause)) {
6651       $select .= "{$this->_distinctComponentClause}, ";
6652     }
6653     $select .= implode(', ', $this->_select);
6654     return $select;
6655   }
6656
6657 }