CRM/Contact/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  * $Id$
  33  *
  34  */
  35
  36 /**
  37  * This is the heart of the search query building mechanism.
  38  */
  39 class CRM_Contact_BAO_Query {
  40
  41   /**
  42    * The various search modes.
  43    *
  44    * @var int
  45    */
  46   const
  47     MODE_CONTACTS = 1,
  48     MODE_CONTRIBUTE = 2,
  49     MODE_MEMBER = 8,
  50     MODE_EVENT = 16,
  51     MODE_GRANT = 128,
  52     MODE_PLEDGEBANK = 256,
  53     MODE_PLEDGE = 512,
  54     MODE_CASE = 2048,
  55     MODE_ALL = 17407,
  56     MODE_ACTIVITY = 4096,
  57     MODE_CAMPAIGN = 8192,
  58     MODE_MAILING = 16384;
  59
  60   /**
  61    * The default set of return properties.
  62    *
  63    * @var array
  64    */
  65   static $_defaultReturnProperties = NULL;
  66
  67   /**
  68    * The default set of hier return properties.
  69    *
  70    * @var array
  71    */
  72   static $_defaultHierReturnProperties;
  73
  74   /**
  75    * The set of input params.
  76    *
  77    * @var array
  78    */
  79   public $_params;
  80
  81   public $_cfIDs;
  82
  83   public $_paramLookup;
  84
  85   public $_sort;
  86   /**
  87    * The set of output params
  88    *
  89    * @var array
  90    */
  91   public $_returnProperties;
  92
  93   /**
  94    * The select clause
  95    *
  96    * @var array
  97    */
  98   public $_select;
  99
 100   /**
 101    * The name of the elements that are in the select clause
 102    * used to extract the values
 103    *
 104    * @var array
 105    */
 106   public $_element;
 107
 108   /**
 109    * The tables involved in the query
 110    *
 111    * @var array
 112    */
 113   public $_tables;
 114
 115   /**
 116    * The table involved in the where clause
 117    *
 118    * @var array
 119    */
 120   public $_whereTables;
 121
 122   /**
 123    * The where clause
 124    *
 125    * @var array
 126    */
 127   public $_where;
 128
 129   /**
 130    * The where string
 131    *
 132    * @var string
 133    */
 134   public $_whereClause;
 135
 136   /**
 137    * Additional permission Where Clause
 138    *
 139    * @var string
 140    */
 141   public $_permissionWhereClause;
 142
 143   /**
 144    * The from string
 145    *
 146    * @var string
 147    */
 148   public $_fromClause;
 149
 150   /**
 151    * Additional permission from clause
 152    *
 153    * @var string
 154    */
 155   public $_permissionFromClause;
 156
 157   /**
 158    * The from clause for the simple select and alphabetical
 159    * select
 160    *
 161    * @var string
 162    */
 163   public $_simpleFromClause;
 164
 165   /**
 166    * The having values
 167    *
 168    * @var string
 169    */
 170   public $_having;
 171
 172   /**
 173    * The english language version of the query
 174    *
 175    * @var array
 176    */
 177   public $_qill;
 178
 179   /**
 180    * All the fields that could potentially be involved in
 181    * this query
 182    *
 183    * @var array
 184    */
 185   public $_fields;
 186
 187   /**
 188    * The cache to translate the option values into labels.
 189    *
 190    * @var array
 191    */
 192   public $_options;
 193
 194   /**
 195    * Are we in search mode.
 196    *
 197    * @var boolean
 198    */
 199   public $_search = TRUE;
 200
 201   /**
 202    * Should we skip permission checking.
 203    *
 204    * @var boolean
 205    */
 206   public $_skipPermission = FALSE;
 207
 208   /**
 209    * Should we skip adding of delete clause.
 210    *
 211    * @var boolean
 212    */
 213   public $_skipDeleteClause = FALSE;
 214
 215   /**
 216    * Are we in strict mode (use equality over LIKE)
 217    *
 218    * @var boolean
 219    */
 220   public $_strict = FALSE;
 221
 222   /**
 223    * What operator to use to group the clauses.
 224    *
 225    * @var string
 226    */
 227   public $_operator = 'AND';
 228
 229   public $_mode = 1;
 230
 231   /**
 232    * Should we only search on primary location.
 233    *
 234    * @var boolean
 235    */
 236   public $_primaryLocation = TRUE;
 237
 238   /**
 239    * Are contact ids part of the query.
 240    *
 241    * @var boolean
 242    */
 243   public $_includeContactIds = FALSE;
 244
 245   /**
 246    * Should we use the smart group cache.
 247    *
 248    * @var boolean
 249    */
 250   public $_smartGroupCache = TRUE;
 251
 252   /**
 253    * Should we display contacts with a specific relationship type.
 254    *
 255    * @var string
 256    */
 257   public $_displayRelationshipType = NULL;
 258
 259   /**
 260    * Reference to the query object for custom values.
 261    *
 262    * @var Object
 263    */
 264   public $_customQuery;
 265
 266   /**
 267    * Should we enable the distinct clause, used if we are including
 268    * more than one group
 269    *
 270    * @var boolean
 271    */
 272   public $_useDistinct = FALSE;
 273
 274   /**
 275    * Should we just display one contact record
 276    */
 277   public $_useGroupBy = FALSE;
 278
 279   /**
 280    * The relationship type direction
 281    *
 282    * @var array
 283    */
 284   static $_relType;
 285
 286   /**
 287    * The activity role
 288    *
 289    * @var array
 290    */
 291   static $_activityRole;
 292
 293   /**
 294    * Consider the component activity type
 295    * during activity search.
 296    *
 297    * @var array
 298    */
 299   static $_considerCompActivities;
 300
 301   /**
 302    * Consider with contact activities only,
 303    * during activity search.
 304    *
 305    * @var array
 306    */
 307   static $_withContactActivitiesOnly;
 308
 309   /**
 310    * Use distinct component clause for component searches
 311    *
 312    * @var string
 313    */
 314   public $_distinctComponentClause;
 315
 316   public $_rowCountClause;
 317
 318   /**
 319    * Use groupBy component clause for component searches
 320    *
 321    * @var string
 322    */
 323   public $_groupByComponentClause;
 324
 325   /**
 326    * Track open panes, useful in advance search
 327    *
 328    * @var array
 329    */
 330   public static $_openedPanes = array();
 331
 332   /**
 333    * For search builder - which custom fields are location-dependent
 334    * @var array
 335    */
 336   public $_locationSpecificCustomFields = array();
 337
 338   /**
 339    * The tables which have a dependency on location and/or address
 340    *
 341    * @var array
 342    */
 343   static $_dependencies = array(
 344     'civicrm_state_province' => 1,
 345     'civicrm_country' => 1,
 346     'civicrm_county' => 1,
 347     'civicrm_address' => 1,
 348     'civicrm_location_type' => 1,
 349   );
 350
 351   /**
 352    * List of location specific fields.
 353    */
 354   static $_locationSpecificFields = array(
 355     'street_address',
 356     'street_number',
 357     'street_name',
 358     'street_unit',
 359     'supplemental_address_1',
 360     'supplemental_address_2',
 361     'city',
 362     'postal_code',
 363     'postal_code_suffix',
 364     'geo_code_1',
 365     'geo_code_2',
 366     'state_province',
 367     'country',
 368     'county',
 369     'phone',
 370     'email',
 371     'im',
 372     'address_name',
 373   );
 374
 375   /**
 376    * Remember if we handle either end of a number or date range
 377    * so we can skip the other
 378    */
 379   protected $_rangeCache = array();
 380   /**
 381    * Set to true when $this->relationship is run to avoid adding twice
 382    * @var Boolean
 383    */
 384   protected $_relationshipValuesAdded = FALSE;
 385
 386   /**
 387    * Set to the name of the temp table if one has been created
 388    * @var String
 389    */
 390   static $_relationshipTempTable = NULL;
 391
 392   public $_pseudoConstantsSelect = array();
 393
 394   /**
 395    * Class constructor which also does all the work.
 396    *
 397    * @param array $params
 398    * @param array $returnProperties
 399    * @param array $fields
 400    * @param bool $includeContactIds
 401    * @param bool $strict
 402    * @param bool|int $mode - mode the search is operating on
 403    *
 404    * @param bool $skipPermission
 405    * @param bool $searchDescendentGroups
 406    * @param bool $smartGroupCache
 407    * @param null $displayRelationshipType
 408    * @param string $operator
 409    *
 410    * @return \CRM_Contact_BAO_Query
 411    */
 412   public function __construct(
 413     $params = NULL, $returnProperties = NULL, $fields = NULL,
 414     $includeContactIds = FALSE, $strict = FALSE, $mode = 1,
 415     $skipPermission = FALSE, $searchDescendentGroups = TRUE,
 416     $smartGroupCache = TRUE, $displayRelationshipType = NULL,
 417     $operator = 'AND'
 418   ) {
 419     $this->_params = &$params;
 420     if ($this->_params == NULL) {
 421       $this->_params = array();
 422     }
 423
 424     if (empty($returnProperties)) {
 425       $this->_returnProperties = self::defaultReturnProperties($mode);
 426     }
 427     else {
 428       $this->_returnProperties = &$returnProperties;
 429     }
 430
 431     $this->_includeContactIds = $includeContactIds;
 432     $this->_strict = $strict;
 433     $this->_mode = $mode;
 434     $this->_skipPermission = $skipPermission;
 435     $this->_smartGroupCache = $smartGroupCache;
 436     $this->_displayRelationshipType = $displayRelationshipType;
 437     $this->setOperator($operator);
 438
 439     if ($fields) {
 440       $this->_fields = &$fields;
 441       $this->_search = FALSE;
 442       $this->_skipPermission = TRUE;
 443     }
 444     else {
 445       $this->_fields = CRM_Contact_BAO_Contact::exportableFields('All', FALSE, TRUE, TRUE);
 446
 447       $fields = CRM_Core_Component::getQueryFields();
 448       unset($fields['note']);
 449       $this->_fields = array_merge($this->_fields, $fields);
 450
 451       // add activity fields
 452       $fields = CRM_Activity_BAO_Activity::exportableFields();
 453       $this->_fields = array_merge($this->_fields, $fields);
 454
 455       // add any fields provided by hook implementers
 456       $extFields = CRM_Contact_BAO_Query_Hook::singleton()->getFields();
 457       $this->_fields = array_merge($this->_fields, $extFields);
 458     }
 459
 460     // basically do all the work once, and then reuse it
 461     $this->initialize();
 462   }
 463
 464   /**
 465    * Function which actually does all the work for the constructor.
 466    *
 467    * @return void
 468    */
 469   public function initialize() {
 470     $this->_select = array();
 471     $this->_element = array();
 472     $this->_tables = array();
 473     $this->_whereTables = array();
 474     $this->_where = array();
 475     $this->_qill = array();
 476     $this->_options = array();
 477     $this->_cfIDs = array();
 478     $this->_paramLookup = array();
 479     $this->_having = array();
 480
 481     $this->_customQuery = NULL;
 482
 483     // reset cached static variables - CRM-5803
 484     self::$_activityRole = NULL;
 485     self::$_considerCompActivities = NULL;
 486     self::$_withContactActivitiesOnly = NULL;
 487
 488     $this->_select['contact_id'] = 'contact_a.id as contact_id';
 489     $this->_element['contact_id'] = 1;
 490     $this->_tables['civicrm_contact'] = 1;
 491
 492     if (!empty($this->_params)) {
 493       $this->buildParamsLookup();
 494     }
 495
 496     $this->_whereTables = $this->_tables;
 497
 498     $this->selectClause();
 499     $this->_whereClause = $this->whereClause();
 500
 501     $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
 502     $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
 503
 504     $this->openedSearchPanes(TRUE);
 505   }
 506
 507   public function buildParamsLookup() {
 508     // first fix and handle contact deletion nicely
 509     // this code is primarily for search builder use case
 510     // where different clauses can specify if they want deleted
 511     // contacts or not
 512     // CRM-11971
 513     $trashParamExists = FALSE;
 514     $paramByGroup = array();
 515     foreach ($this->_params as $k => $param) {
 516       if (!empty($param[0]) && $param[0] == 'contact_is_deleted') {
 517         $trashParamExists = TRUE;
 518       }
 519       if (!empty($param[3])) {
 520         $paramByGroup[$param[3]][$k] = $param;
 521       }
 522     }
 523
 524     if ($trashParamExists) {
 525       $this->_skipDeleteClause = TRUE;
 526
 527       //cycle through group sets and explicitly add trash param if not set
 528       foreach ($paramByGroup as $setID => $set) {
 529         if (
 530           !in_array(array('contact_is_deleted', '=', '1', $setID, '0'), $this->_params) &&
 531           !in_array(array('contact_is_deleted', '=', '0', $setID, '0'), $this->_params)
 532         ) {
 533           $this->_params[] = array(
 534             'contact_is_deleted',
 535             '=',
 536             '0',
 537             $setID,
 538             '0',
 539           );
 540         }
 541       }
 542     }
 543
 544     foreach ($this->_params as $value) {
 545       if (empty($value[0])) {
 546         continue;
 547       }
 548       $cfID = CRM_Core_BAO_CustomField::getKeyID($value[0]);
 549       if ($cfID) {
 550         if (!array_key_exists($cfID, $this->_cfIDs)) {
 551           $this->_cfIDs[$cfID] = array();
 552         }
 553         // Set wildcard value based on "and/or" selection
 554         foreach ($this->_params as $key => $param) {
 555           if ($param[0] == $value[0] . '_operator') {
 556             $value[4] = $param[2] == 'or';
 557             break;
 558           }
 559         }
 560         $this->_cfIDs[$cfID][] = $value;
 561       }
 562
 563       if (!array_key_exists($value[0], $this->_paramLookup)) {
 564         $this->_paramLookup[$value[0]] = array();
 565       }
 566       $this->_paramLookup[$value[0]][] = $value;
 567     }
 568   }
 569
 570   /**
 571    * Some composite fields do not appear in the fields array
 572    * hack to make them part of the query
 573    *
 574    * @return void
 575    */
 576   public function addSpecialFields() {
 577     static $special = array('contact_type', 'contact_sub_type', 'sort_name', 'display_name');
 578     foreach ($special as $name) {
 579       if (!empty($this->_returnProperties[$name])) {
 580         $this->_select[$name] = "contact_a.{$name} as $name";
 581         $this->_element[$name] = 1;
 582       }
 583     }
 584   }
 585
 586   /**
 587    * Given a list of conditions in params and a list of desired
 588    * return Properties generate the required select and from
 589    * clauses. Note that since the where clause introduces new
 590    * tables, the initial attempt also retrieves all variables used
 591    * in the params list
 592    *
 593    * @return void
 594    */
 595   public function selectClause() {
 596
 597     $this->addSpecialFields();
 598
 599     foreach ($this->_fields as $name => $field) {
 600       // skip component fields
 601       // there are done by the alter query below
 602       // and need not be done on every field
 603       if (
 604         (substr($name, 0, 12) == 'participant_') ||
 605         (substr($name, 0, 7) == 'pledge_') ||
 606         (substr($name, 0, 5) == 'case_') ||
 607         (substr($name, 0, 13) == 'contribution_' &&
 608           (strpos($name, 'source') !== FALSE && strpos($name, 'recur') !== FALSE)) ||
 609         (substr($name, 0, 8) == 'payment_')
 610       ) {
 611         continue;
 612       }
 613
 614       // redirect to activity select clause
 615       if (
 616         (substr($name, 0, 9) == 'activity_') ||
 617         ($name == 'parent_id')
 618       ) {
 619         CRM_Activity_BAO_Query::select($this);
 620         continue;
 621       }
 622
 623       // if this is a hierarchical name, we ignore it
 624       $names = explode('-', $name);
 625       if (count($names) > 1 && isset($names[1]) && is_numeric($names[1])) {
 626         continue;
 627       }
 628
 629       // make an exception for special cases, to add the field in select clause
 630       $makeException = FALSE;
 631
 632       //special handling for groups/tags
 633       if (in_array($name, array('groups', 'tags', 'notes'))
 634         && isset($this->_returnProperties[substr($name, 0, -1)])
 635       ) {
 636         $makeException = TRUE;
 637       }
 638
 639       // since note has 3 different options we need special handling
 640       // note / note_subject / note_body
 641       if ($name == 'notes') {
 642         foreach (array('note', 'note_subject', 'note_body') as $noteField) {
 643           if (isset($this->_returnProperties[$noteField])) {
 644             $makeException = TRUE;
 645             break;
 646           }
 647         }
 648       }
 649
 650       if (in_array($name, array('prefix_id', 'suffix_id', 'gender_id', 'communication_style_id'))) {
 651         if (CRM_Utils_Array::value($field['pseudoconstant']['optionGroupName'], $this->_returnProperties)) {
 652           $makeException = TRUE;
 653         }
 654       }
 655
 656       $cfID = CRM_Core_BAO_CustomField::getKeyID($name);
 657       if (!empty($this->_paramLookup[$name]) || !empty($this->_returnProperties[$name]) ||
 658         $makeException
 659       ) {
 660         if ($cfID) {
 661           // add to cfIDs array if not present
 662           if (!array_key_exists($cfID, $this->_cfIDs)) {
 663             $this->_cfIDs[$cfID] = array();
 664           }
 665         }
 666         elseif (isset($field['where'])) {
 667           list($tableName, $fieldName) = explode('.', $field['where'], 2);
 668           if (isset($tableName)) {
 669             if (CRM_Utils_Array::value($tableName, self::$_dependencies)) {
 670               $this->_tables['civicrm_address'] = 1;
 671               $this->_select['address_id'] = 'civicrm_address.id as address_id';
 672               $this->_element['address_id'] = 1;
 673             }
 674
 675             if ($tableName == 'im_provider' || $tableName == 'email_greeting' ||
 676               $tableName == 'postal_greeting' || $tableName == 'addressee'
 677             ) {
 678               if ($tableName == 'im_provider') {
 679                 CRM_Core_OptionValue::select($this);
 680               }
 681
 682               if (in_array($tableName,
 683                 array('email_greeting', 'postal_greeting', 'addressee'))) {
 684                 $this->_element["{$name}_id"] = 1;
 685                 $this->_select["{$name}_id"] = "contact_a.{$name}_id as {$name}_id";
 686                 $this->_pseudoConstantsSelect[$name] = array('pseudoField' => $tableName, 'idCol' => "{$name}_id");
 687                 $this->_pseudoConstantsSelect[$name]['select'] = "{$name}.{$fieldName} as $name";
 688                 $this->_pseudoConstantsSelect[$name]['element'] = $name;
 689
 690                 if ($tableName == 'email_greeting') {
 691                   $this->_pseudoConstantsSelect[$name]['join']
 692                     = " LEFT JOIN civicrm_option_group option_group_email_greeting ON (option_group_email_greeting.name = 'email_greeting')";
 693                   $this->_pseudoConstantsSelect[$name]['join'] .=
 694                     " 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 ) ";
 695                 }
 696                 elseif ($tableName == 'postal_greeting') {
 697                   $this->_pseudoConstantsSelect[$name]['join']
 698                     = " LEFT JOIN civicrm_option_group option_group_postal_greeting ON (option_group_postal_greeting.name = 'postal_greeting')";
 699                   $this->_pseudoConstantsSelect[$name]['join'] .=
 700                     " 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 ) ";
 701                 }
 702                 elseif ($tableName == 'addressee') {
 703                   $this->_pseudoConstantsSelect[$name]['join']
 704                     = " LEFT JOIN civicrm_option_group option_group_addressee ON (option_group_addressee.name = 'addressee')";
 705                   $this->_pseudoConstantsSelect[$name]['join'] .=
 706                     " LEFT JOIN civicrm_option_value addressee ON (contact_a.addressee_id = addressee.value AND option_group_addressee.id = addressee.option_group_id ) ";
 707                 }
 708                 $this->_pseudoConstantsSelect[$name]['table'] = $tableName;
 709
 710                 //get display
 711                 $greetField = "{$name}_display";
 712                 $this->_select[$greetField] = "contact_a.{$greetField} as {$greetField}";
 713                 $this->_element[$greetField] = 1;
 714                 //get custom
 715                 $greetField = "{$name}_custom";
 716                 $this->_select[$greetField] = "contact_a.{$greetField} as {$greetField}";
 717                 $this->_element[$greetField] = 1;
 718               }
 719             }
 720             else {
 721               if (!in_array($tableName, array('civicrm_state_province', 'civicrm_country', 'civicrm_county'))) {
 722                 $this->_tables[$tableName] = 1;
 723               }
 724
 725               // also get the id of the tableName
 726               $tName = substr($tableName, 8);
 727               if (in_array($tName, array('country', 'state_province', 'county'))) {
 728                 if ($tName == 'state_province') {
 729                   $this->_pseudoConstantsSelect['state_province_name'] = array(
 730                     'pseudoField' => "{$tName}",
 731                     'idCol' => "{$tName}_id",
 732                     'bao' => 'CRM_Core_BAO_Address',
 733                     'table' => "civicrm_{$tName}",
 734                     'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ",
 735                   );
 736
 737                   $this->_pseudoConstantsSelect[$tName] = array(
 738                     'pseudoField' => 'state_province_abbreviation',
 739                     'idCol' => "{$tName}_id",
 740                     'table' => "civicrm_{$tName}",
 741                     'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ",
 742                   );
 743                 }
 744                 else {
 745                   $this->_pseudoConstantsSelect[$name] = array(
 746                     'pseudoField' => "{$tName}_id",
 747                     'idCol' => "{$tName}_id",
 748                     'bao' => 'CRM_Core_BAO_Address',
 749                     'table' => "civicrm_{$tName}",
 750                     'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ",
 751                   );
 752                 }
 753
 754                 $this->_select["{$tName}_id"] = "civicrm_address.{$tName}_id as {$tName}_id";
 755                 $this->_element["{$tName}_id"] = 1;
 756               }
 757               elseif ($tName != 'contact') {
 758                 $this->_select["{$tName}_id"] = "{$tableName}.id as {$tName}_id";
 759                 $this->_element["{$tName}_id"] = 1;
 760               }
 761
 762               //special case for phone
 763               if ($name == 'phone') {
 764                 $this->_select['phone_type_id'] = "civicrm_phone.phone_type_id as phone_type_id";
 765                 $this->_element['phone_type_id'] = 1;
 766               }
 767
 768               // if IM then select provider_id also
 769               // to get "IM Service Provider" in a file to be exported, CRM-3140
 770               if ($name == 'im') {
 771                 $this->_select['provider_id'] = "civicrm_im.provider_id as provider_id";
 772                 $this->_element['provider_id'] = 1;
 773               }
 774
 775               if ($tName == 'contact') {
 776                 // special case, when current employer is set for Individual contact
 777                 if ($fieldName == 'organization_name') {
 778                   $this->_select[$name] = "IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name";
 779                 }
 780                 elseif ($fieldName != 'id') {
 781                   if ($fieldName == 'prefix_id') {
 782                     $this->_pseudoConstantsSelect['individual_prefix'] = array(
 783                       'pseudoField' => 'prefix_id',
 784                       'idCol' => "prefix_id",
 785                       'bao' => 'CRM_Contact_BAO_Contact',
 786                     );
 787                   }
 788                   if ($fieldName == 'suffix_id') {
 789                     $this->_pseudoConstantsSelect['individual_suffix'] = array(
 790                       'pseudoField' => 'suffix_id',
 791                       'idCol' => "suffix_id",
 792                       'bao' => 'CRM_Contact_BAO_Contact',
 793                     );
 794                   }
 795                   if ($fieldName == 'gender_id') {
 796                     $this->_pseudoConstantsSelect['gender'] = array(
 797                       'pseudoField' => 'gender_id',
 798                       'idCol' => "gender_id",
 799                       'bao' => 'CRM_Contact_BAO_Contact',
 800                     );
 801                   }
 802                   if ($name == 'communication_style_id') {
 803                     $this->_pseudoConstantsSelect['communication_style'] = array(
 804                       'pseudoField' => 'communication_style_id',
 805                       'idCol' => "communication_style_id",
 806                       'bao' => 'CRM_Contact_BAO_Contact',
 807                     );
 808                   }
 809                   $this->_select[$name] = "contact_a.{$fieldName}  as `$name`";
 810                 }
 811               }
 812               elseif (in_array($tName, array('country', 'county'))) {
 813                 $this->_pseudoConstantsSelect[$name]['select'] = "{$field['where']} as `$name`";
 814                 $this->_pseudoConstantsSelect[$name]['element'] = $name;
 815               }
 816               elseif ($tName == 'state_province') {
 817                 $this->_pseudoConstantsSelect[$tName]['select'] = "{$field['where']} as `$name`";
 818                 $this->_pseudoConstantsSelect[$tName]['element'] = $name;
 819               }
 820               else {
 821                 $this->_select[$name] = "{$field['where']} as `$name`";
 822               }
 823               if (!in_array($tName, array('state_province', 'country', 'county'))) {
 824                 $this->_element[$name] = 1;
 825               }
 826             }
 827           }
 828         }
 829         elseif ($name === 'tags') {
 830           $this->_useGroupBy = TRUE;
 831           $this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_tag.name)) as tags";
 832           $this->_element[$name] = 1;
 833           $this->_tables['civicrm_tag'] = 1;
 834           $this->_tables['civicrm_entity_tag'] = 1;
 835         }
 836         elseif ($name === 'groups') {
 837           $this->_useGroupBy = TRUE;
 838           $this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_group.title)) as groups";
 839           $this->_element[$name] = 1;
 840           $this->_tables['civicrm_group'] = 1;
 841         }
 842         elseif ($name === 'notes') {
 843           // if note field is subject then return subject else body of the note
 844           $noteColumn = 'note';
 845           if (isset($noteField) && $noteField == 'note_subject') {
 846             $noteColumn = 'subject';
 847           }
 848
 849           $this->_useGroupBy = TRUE;
 850           $this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_note.$noteColumn)) as notes";
 851           $this->_element[$name] = 1;
 852           $this->_tables['civicrm_note'] = 1;
 853         }
 854         elseif ($name === 'current_employer') {
 855           $this->_select[$name] = "IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer";
 856           $this->_element[$name] = 1;
 857         }
 858       }
 859
 860       if ($cfID && !empty($field['is_search_range'])) {
 861         // this is a custom field with range search enabled, so we better check for two/from values
 862         if (!empty($this->_paramLookup[$name . '_from'])) {
 863           if (!array_key_exists($cfID, $this->_cfIDs)) {
 864             $this->_cfIDs[$cfID] = array();
 865           }
 866           foreach ($this->_paramLookup[$name . '_from'] as $pID => $p) {
 867             // search in the cdID array for the same grouping
 868             $fnd = FALSE;
 869             foreach ($this->_cfIDs[$cfID] as $cID => $c) {
 870               if ($c[3] == $p[3]) {
 871                 $this->_cfIDs[$cfID][$cID][2]['from'] = $p[2];
 872                 $fnd = TRUE;
 873               }
 874             }
 875             if (!$fnd) {
 876               $p[2] = array('from' => $p[2]);
 877               $this->_cfIDs[$cfID][] = $p;
 878             }
 879           }
 880         }
 881         if (!empty($this->_paramLookup[$name . '_to'])) {
 882           if (!array_key_exists($cfID, $this->_cfIDs)) {
 883             $this->_cfIDs[$cfID] = array();
 884           }
 885           foreach ($this->_paramLookup[$name . '_to'] as $pID => $p) {
 886             // search in the cdID array for the same grouping
 887             $fnd = FALSE;
 888             foreach ($this->_cfIDs[$cfID] as $cID => $c) {
 889               if ($c[4] == $p[4]) {
 890                 $this->_cfIDs[$cfID][$cID][2]['to'] = $p[2];
 891                 $fnd = TRUE;
 892               }
 893             }
 894             if (!$fnd) {
 895               $p[2] = array('to' => $p[2]);
 896               $this->_cfIDs[$cfID][] = $p;
 897             }
 898           }
 899         }
 900       }
 901     }
 902
 903     // add location as hierarchical elements
 904     $this->addHierarchicalElements();
 905
 906     // add multiple field like website
 907     $this->addMultipleElements();
 908
 909     //fix for CRM-951
 910     CRM_Core_Component::alterQuery($this, 'select');
 911
 912     CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'select');
 913
 914     if (!empty($this->_cfIDs)) {
 915       $this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE, $this->_locationSpecificCustomFields);
 916       $this->_customQuery->query();
 917       $this->_select = array_merge($this->_select, $this->_customQuery->_select);
 918       $this->_element = array_merge($this->_element, $this->_customQuery->_element);
 919       $this->_tables = array_merge($this->_tables, $this->_customQuery->_tables);
 920       $this->_whereTables = array_merge($this->_whereTables, $this->_customQuery->_whereTables);
 921       $this->_options = $this->_customQuery->_options;
 922     }
 923   }
 924
 925   /**
 926    * If the return Properties are set in a hierarchy, traverse the hierarchy to get
 927    * the return values
 928    *
 929    * @return void
 930    */
 931   public function addHierarchicalElements() {
 932     if (empty($this->_returnProperties['location'])) {
 933       return;
 934     }
 935     if (!is_array($this->_returnProperties['location'])) {
 936       return;
 937     }
 938
 939     $locationTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
 940     $processed = array();
 941     $index = 0;
 942
 943     $addressCustomFields = CRM_Core_BAO_CustomField::getFieldsForImport('Address');
 944     $addressCustomFieldIds = array();
 945
 946     foreach ($this->_returnProperties['location'] as $name => $elements) {
 947       $lCond = self::getPrimaryCondition($name);
 948
 949       if (!$lCond) {
 950         $locationTypeId = array_search($name, $locationTypes);
 951         if ($locationTypeId === FALSE) {
 952           continue;
 953         }
 954         $lCond = "location_type_id = $locationTypeId";
 955         $this->_useDistinct = TRUE;
 956
 957         //commented for CRM-3256
 958         $this->_useGroupBy = TRUE;
 959       }
 960
 961       $name = str_replace(' ', '_', $name);
 962
 963       $tName = "$name-location_type";
 964       $ltName = "`$name-location_type`";
 965       $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
 966       $this->_select["{$tName}"] = "`$tName`.name as `{$tName}`";
 967       $this->_element["{$tName}_id"] = 1;
 968       $this->_element["{$tName}"] = 1;
 969
 970       $locationTypeName = $tName;
 971       $locationTypeJoin = array();
 972
 973       $addAddress = FALSE;
 974       $addWhereCount = 0;
 975       foreach ($elements as $elementFullName => $dontCare) {
 976         $index++;
 977         $elementName = $elementCmpName = $elementFullName;
 978
 979         if (substr($elementCmpName, 0, 5) == 'phone') {
 980           $elementCmpName = 'phone';
 981         }
 982
 983         if (in_array($elementCmpName, array_keys($addressCustomFields))) {
 984           if ($cfID = CRM_Core_BAO_CustomField::getKeyID($elementCmpName)) {
 985             $addressCustomFieldIds[$cfID][$name] = 1;
 986           }
 987         }
 988         //add address table only once
 989         if ((in_array($elementCmpName, self::$_locationSpecificFields) || !empty($addressCustomFieldIds))
 990           && !$addAddress
 991           && !in_array($elementCmpName, array('email', 'phone', 'im', 'openid'))
 992         ) {
 993           $tName = "$name-address";
 994           $aName = "`$name-address`";
 995           $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
 996           $this->_element["{$tName}_id"] = 1;
 997           $addressJoin = "\nLEFT JOIN civicrm_address $aName ON ($aName.contact_id = contact_a.id AND $aName.$lCond)";
 998           $this->_tables[$tName] = $addressJoin;
 999           $locationTypeJoin[$tName] = " ( $aName.location_type_id = $ltName.id ) ";
1000           $processed[$aName] = 1;
1001           $addAddress = TRUE;
1002         }
1003
1004         $cond = $elementType = '';
1005         if (strpos($elementName, '-') !== FALSE) {
1006           // this is either phone, email or IM
1007           list($elementName, $elementType) = explode('-', $elementName);
1008
1009           if (($elementName != 'phone') && ($elementName != 'im')) {
1010             $cond = self::getPrimaryCondition($elementType);
1011           }
1012           // CRM-13011 : If location type is primary, do not restrict search to the phone
1013           // type id - we want the primary phone, regardless of what type it is.
1014           // Otherwise, restrict to the specified phone type for the given field.
1015           if ((!$cond) && ($elementName == 'phone')) {
1016             $cond = "phone_type_id = '$elementType'";
1017           }
1018           elseif ((!$cond) && ($elementName == 'im')) {
1019             // IM service provider id, CRM-3140
1020             $cond = "provider_id = '$elementType'";
1021           }
1022           $elementType = '-' . $elementType;
1023         }
1024
1025         $field = CRM_Utils_Array::value($elementName, $this->_fields);
1026
1027         // hack for profile, add location id
1028         if (!$field) {
1029           if ($elementType &&
1030             // fix for CRM-882( to handle phone types )
1031             !is_numeric($elementType)
1032           ) {
1033             if (is_numeric($name)) {
1034               $field = CRM_Utils_Array::value($elementName . "-Primary$elementType", $this->_fields);
1035             }
1036             else {
1037               $field = CRM_Utils_Array::value($elementName . "-$locationTypeId$elementType", $this->_fields);
1038             }
1039           }
1040           elseif (is_numeric($name)) {
1041             //this for phone type to work
1042             if (in_array($elementName, array('phone', 'phone_ext'))) {
1043               $field = CRM_Utils_Array::value($elementName . "-Primary" . $elementType, $this->_fields);
1044             }
1045             else {
1046               $field = CRM_Utils_Array::value($elementName . "-Primary", $this->_fields);
1047             }
1048           }
1049           else {
1050             //this is for phone type to work for profile edit
1051             if (in_array($elementName, array('phone', 'phone_ext'))) {
1052               $field = CRM_Utils_Array::value($elementName . "-$locationTypeId$elementType", $this->_fields);
1053             }
1054             else {
1055               $field = CRM_Utils_Array::value($elementName . "-$locationTypeId", $this->_fields);
1056             }
1057           }
1058         }
1059
1060         // Check if there is a value, if so also add to where Clause
1061         $addWhere = FALSE;
1062         if ($this->_params) {
1063           $nm = $elementName;
1064           if (isset($locationTypeId)) {
1065             $nm .= "-$locationTypeId";
1066           }
1067           if (!is_numeric($elementType)) {
1068             $nm .= "$elementType";
1069           }
1070
1071           foreach ($this->_params as $id => $values) {
1072             if ((is_array($values) && $values[0] == $nm) ||
1073               (in_array($elementName, array('phone', 'im'))
1074                 && (strpos($values[0], $nm) !== FALSE)
1075               )
1076             ) {
1077               $addWhere = TRUE;
1078               $addWhereCount++;
1079               break;
1080             }
1081           }
1082         }
1083
1084         if ($field && isset($field['where'])) {
1085           list($tableName, $fieldName) = explode('.', $field['where'], 2);
1086           $pf = substr($tableName, 8);
1087           $tName = $name . '-' . $pf . $elementType;
1088           if (isset($tableName)) {
1089             if ($tableName == 'civicrm_state_province' || $tableName == 'civicrm_country' || $tableName == 'civicrm_county') {
1090               $this->_select["{$tName}_id"] = "{$aName}.{$pf}_id as `{$tName}_id`";
1091             }
1092             else {
1093               $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
1094             }
1095
1096             $this->_element["{$tName}_id"] = 1;
1097             if (substr($tName, -15) == '-state_province') {
1098               // FIXME: hack to fix CRM-1900
1099               $a = CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME,
1100                 'address_format'
1101               );
1102
1103               if (substr_count($a, 'state_province_name') > 0) {
1104                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"] = array(
1105                   'pseudoField' => "{$pf}_id",
1106                   'idCol' => "{$tName}_id",
1107                   'bao' => 'CRM_Core_BAO_Address',
1108                 );
1109                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['select'] = "`$tName`.name as `{$name}-{$elementFullName}`";
1110               }
1111               else {
1112                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"] = array(
1113                   'pseudoField' => 'state_province_abbreviation',
1114                   'idCol' => "{$tName}_id",
1115                 );
1116                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['select'] = "`$tName`.abbreviation as `{$name}-{$elementFullName}`";
1117               }
1118             }
1119             else {
1120               if (substr($elementFullName, 0, 2) == 'im') {
1121                 $provider = "{$name}-{$elementFullName}-provider_id";
1122                 $this->_select[$provider] = "`$tName`.provider_id as `{$name}-{$elementFullName}-provider_id`";
1123                 $this->_element[$provider] = 1;
1124               }
1125               if ($pf == 'country' || $pf == 'county') {
1126                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"] = array(
1127                   'pseudoField' => "{$pf}_id",
1128                   'idCol' => "{$tName}_id",
1129                   'bao' => 'CRM_Core_BAO_Address',
1130                 );
1131                 $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['select'] = "`$tName`.$fieldName as `{$name}-{$elementFullName}`";
1132               }
1133               else {
1134                 $this->_select["{$name}-{$elementFullName}"] = "`$tName`.$fieldName as `{$name}-{$elementFullName}`";
1135               }
1136             }
1137
1138             if (in_array($pf, array('state_province', 'country', 'county'))) {
1139               $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['element'] = "{$name}-{$elementFullName}";
1140             }
1141             else {
1142               $this->_element["{$name}-{$elementFullName}"] = 1;
1143             }
1144
1145             if (empty($processed["`$tName`"])) {
1146               $processed["`$tName`"] = 1;
1147               $newName = $tableName . '_' . $index;
1148               switch ($tableName) {
1149                 case 'civicrm_phone':
1150                 case 'civicrm_email':
1151                 case 'civicrm_im':
1152                 case 'civicrm_openid':
1153
1154                   $this->_tables[$tName] = "\nLEFT JOIN $tableName `$tName` ON contact_a.id = `$tName`.contact_id";
1155                   if ($tableName != 'civicrm_phone') {
1156                     $this->_tables[$tName] .= " AND `$tName`.$lCond";
1157                   }
1158                   elseif (is_numeric($name)) {
1159                     $this->_select[$tName] = "IF (`$tName`.is_primary = $name, `$tName`.phone, NULL) as `$tName`";
1160                   }
1161
1162                   // this special case to add phone type
1163                   if ($cond) {
1164                     $phoneTypeCondition = " AND `$tName`.$cond ";
1165                     //gross hack to pickup corrupted data also, CRM-7603
1166                     if (strpos($cond, 'phone_type_id') !== FALSE) {
1167                       $phoneTypeCondition = " AND ( `$tName`.$cond OR `$tName`.phone_type_id IS NULL ) ";
1168                     }
1169                     $this->_tables[$tName] .= $phoneTypeCondition;
1170                   }
1171
1172                   //build locationType join
1173                   $locationTypeJoin[$tName] = " ( `$tName`.location_type_id = $ltName.id )";
1174
1175                   if ($addWhere) {
1176                     $this->_whereTables[$tName] = $this->_tables[$tName];
1177                   }
1178                   break;
1179
1180                 case 'civicrm_state_province':
1181                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['table'] = $tName;
1182                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['join']
1183                     = "\nLEFT JOIN $tableName `$tName` ON `$tName`.id = $aName.state_province_id";
1184                   if ($addWhere) {
1185                     $this->_whereTables["{$name}-address"] = $addressJoin;
1186                   }
1187                   break;
1188
1189                 case 'civicrm_country':
1190                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['table'] = $newName;
1191                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['join']
1192                     = "\nLEFT JOIN $tableName `$tName` ON `$tName`.id = $aName.country_id";
1193                   if ($addWhere) {
1194                     $this->_whereTables["{$name}-address"] = $addressJoin;
1195                   }
1196                   break;
1197
1198                 case 'civicrm_county':
1199                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['table'] = $newName;
1200                   $this->_pseudoConstantsSelect["{$name}-{$elementFullName}"]['join']
1201                     = "\nLEFT JOIN $tableName `$tName` ON `$tName`.id = $aName.county_id";
1202                   if ($addWhere) {
1203                     $this->_whereTables["{$name}-address"] = $addressJoin;
1204                   }
1205                   break;
1206
1207                 default:
1208                   if ($addWhere) {
1209                     $this->_whereTables["{$name}-address"] = $addressJoin;
1210                   }
1211                   break;
1212               }
1213             }
1214           }
1215         }
1216       }
1217
1218       // add location type  join
1219       $ltypeJoin = "\nLEFT JOIN civicrm_location_type $ltName ON ( " . implode('OR', $locationTypeJoin) . " )";
1220       $this->_tables[$locationTypeName] = $ltypeJoin;
1221
1222       // table should be present in $this->_whereTables,
1223       // to add its condition in location type join, CRM-3939.
1224       if ($addWhereCount) {
1225         $locClause = array();
1226         foreach ($this->_whereTables as $tableName => $clause) {
1227           if (!empty($locationTypeJoin[$tableName])) {
1228             $locClause[] = $locationTypeJoin[$tableName];
1229           }
1230         }
1231
1232         if (!empty($locClause)) {
1233           $this->_whereTables[$locationTypeName] = "\nLEFT JOIN civicrm_location_type $ltName ON ( " . implode('OR', $locClause) . " )";
1234         }
1235       }
1236     }
1237
1238     if (!empty($addressCustomFieldIds)) {
1239       $customQuery = new CRM_Core_BAO_CustomQuery($addressCustomFieldIds);
1240       foreach ($addressCustomFieldIds as $cfID => $locTypeName) {
1241         foreach ($locTypeName as $name => $dnc) {
1242           $this->_locationSpecificCustomFields[$cfID] = array($name, array_search($name, $locationTypes));
1243           $fieldName = "$name-custom_{$cfID}";
1244           $tName = "$name-address-custom-{$cfID}";
1245           $aName = "`$name-address-custom-{$cfID}`";
1246           $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
1247           $this->_element["{$tName}_id"] = 1;
1248           $this->_select[$fieldName] = "`$tName`.{$customQuery->_fields[$cfID]['column_name']} as `{$fieldName}`";
1249           $this->_element[$fieldName] = 1;
1250           $this->_tables[$tName] = "\nLEFT JOIN {$customQuery->_fields[$cfID]['table_name']} $aName ON ($aName.entity_id = `$name-address`.id)";
1251         }
1252       }
1253     }
1254   }
1255
1256   /**
1257    * If the return Properties are set in a hierarchy, traverse the hierarchy to get
1258    * the return values
1259    *
1260    * @return void
1261    */
1262   public function addMultipleElements() {
1263     if (empty($this->_returnProperties['website'])) {
1264       return;
1265     }
1266     if (!is_array($this->_returnProperties['website'])) {
1267       return;
1268     }
1269
1270     foreach ($this->_returnProperties['website'] as $key => $elements) {
1271       foreach ($elements as $elementFullName => $dontCare) {
1272         $tName = "website-{$key}-{$elementFullName}";
1273         $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
1274         $this->_select["{$tName}"] = "`$tName`.url as `{$tName}`";
1275         $this->_element["{$tName}_id"] = 1;
1276         $this->_element["{$tName}"] = 1;
1277
1278         $type = "website-{$key}-website_type_id";
1279         $this->_select[$type] = "`$tName`.website_type_id as `{$type}`";
1280         $this->_element[$type] = 1;
1281         $this->_tables[$tName] = "\nLEFT JOIN civicrm_website `$tName` ON (`$tName`.contact_id = contact_a.id AND `$tName`.website_type_id = $key )";
1282       }
1283     }
1284   }
1285
1286   /**
1287    * Generate the query based on what type of query we need.
1288    *
1289    * @param bool $count
1290    * @param bool $sortByChar
1291    * @param bool $groupContacts
1292    * @param bool $onlyDeleted
1293    *
1294    * @return array
1295    *   sql query parts as an array
1296    */
1297   public function query($count = FALSE, $sortByChar = FALSE, $groupContacts = FALSE, $onlyDeleted = FALSE) {
1298     // build permission clause
1299     $this->generatePermissionClause($onlyDeleted, $count);
1300
1301     if ($count) {
1302       if (isset($this->_rowCountClause)) {
1303         $select = "SELECT {$this->_rowCountClause}";
1304       }
1305       elseif (isset($this->_distinctComponentClause)) {
1306         // we add distinct to get the right count for components
1307         // for the more complex result set, we use GROUP BY the same id
1308         // CRM-9630
1309         $select = "SELECT count( DISTINCT {$this->_distinctComponentClause} )";
1310       }
1311       else {
1312         $select = 'SELECT count(DISTINCT contact_a.id) as rowCount';
1313       }
1314       $from = $this->_simpleFromClause;
1315       if ($this->_useDistinct) {
1316         $this->_useGroupBy = TRUE;
1317       }
1318     }
1319     elseif ($sortByChar) {
1320       $select = 'SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name';
1321       $from = $this->_simpleFromClause;
1322     }
1323     elseif ($groupContacts) {
1324       $select = 'SELECT contact_a.id as id';
1325       if ($this->_useDistinct) {
1326         $this->_useGroupBy = TRUE;
1327       }
1328       $from = $this->_simpleFromClause;
1329     }
1330     else {
1331       if (!empty($this->_paramLookup['group'])) {
1332         // make sure there is only one element
1333         // this is used when we are running under smog and need to know
1334         // how the contact was added (CRM-1203)
1335         if ((count($this->_paramLookup['group']) == 1) &&
1336           (count($this->_paramLookup['group'][0][2]) == 1)
1337         ) {
1338           $groups = array_keys($this->_paramLookup['group'][0][2]);
1339           $groupId = $groups[0];
1340
1341           //check if group is saved search
1342           $group = new CRM_Contact_BAO_Group();
1343           $group->id = $groupId;
1344           $group->find(TRUE);
1345
1346           if (!isset($group->saved_search_id)) {
1347             $tbName = "`civicrm_group_contact-{$groupId}`";
1348             $this->_select['group_contact_id'] = "$tbName.id as group_contact_id";
1349             $this->_element['group_contact_id'] = 1;
1350             $this->_select['status'] = "$tbName.status as status";
1351             $this->_element['status'] = 1;
1352           }
1353         }
1354         $this->_useGroupBy = TRUE;
1355       }
1356       if ($this->_useDistinct && !isset($this->_distinctComponentClause)) {
1357         if (!($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY)) {
1358           // CRM-5954
1359           $this->_select['contact_id'] = 'contact_a.id as contact_id';
1360           $this->_useDistinct = FALSE;
1361           $this->_useGroupBy = TRUE;
1362         }
1363       }
1364
1365       $select = "SELECT ";
1366       if (isset($this->_distinctComponentClause)) {
1367         $select .= "{$this->_distinctComponentClause}, ";
1368       }
1369       $select .= implode(', ', $this->_select);
1370       $from = $this->_fromClause;
1371     }
1372
1373     $where = '';
1374     if (!empty($this->_whereClause)) {
1375       $where = "WHERE {$this->_whereClause}";
1376     }
1377
1378     if (!empty($this->_permissionWhereClause) && empty($this->_displayRelationshipType)) {
1379       if (empty($where)) {
1380         $where = "WHERE $this->_permissionWhereClause";
1381       }
1382       else {
1383         $where = "$where AND $this->_permissionWhereClause";
1384       }
1385     }
1386
1387     $having = '';
1388     if (!empty($this->_having)) {
1389       foreach ($this->_having as $havingSets) {
1390         foreach ($havingSets as $havingSet) {
1391           $havingValue[] = $havingSet;
1392         }
1393       }
1394       $having = ' HAVING ' . implode(' AND ', $havingValue);
1395     }
1396
1397     // if we are doing a transform, do it here
1398     // use the $from, $where and $having to get the contact ID
1399     if ($this->_displayRelationshipType) {
1400       $this->filterRelatedContacts($from, $where, $having);
1401     }
1402
1403     return array($select, $from, $where, $having);
1404   }
1405
1406   /**
1407    * @param string $name
1408    * @param $grouping
1409    *
1410    * @return null
1411    */
1412   public function &getWhereValues($name, $grouping) {
1413     $result = NULL;
1414     foreach ($this->_params as $values) {
1415       if ($values[0] == $name && $values[3] == $grouping) {
1416         return $values;
1417       }
1418     }
1419
1420     return $result;
1421   }
1422
1423   /**
1424    * @param $relative
1425    * @param $from
1426    * @param $to
1427    */
1428   public static function fixDateValues($relative, &$from, &$to) {
1429     if ($relative) {
1430       list($from, $to) = CRM_Utils_Date::getFromTo($relative, $from, $to);
1431     }
1432   }
1433
1434   /**
1435    * @param $formValues
1436    * @param int $wildcard
1437    * @param bool $useEquals
1438    *
1439    * @param string $apiEntity
1440    *
1441    * @return array
1442    */
1443   public static function convertFormValues(&$formValues, $wildcard = 0, $useEquals = FALSE, $apiEntity = NULL) {
1444     $params = array();
1445     if (empty($formValues)) {
1446       return $params;
1447     }
1448
1449     foreach ($formValues as $id => $values) {
1450       if ($id == 'privacy') {
1451         if (is_array($formValues['privacy'])) {
1452           $op = !empty($formValues['privacy']['do_not_toggle']) ? '=' : '!=';
1453           foreach ($formValues['privacy'] as $key => $value) {
1454             if ($value) {
1455               $params[] = array($key, $op, $value, 0, 0);
1456             }
1457           }
1458         }
1459       }
1460       elseif ($id == 'email_on_hold') {
1461         if ($formValues['email_on_hold']['on_hold']) {
1462           $params[] = array('on_hold', '=', $formValues['email_on_hold']['on_hold'], 0, 0);
1463         }
1464       }
1465       elseif (preg_match('/_date_relative$/', $id) ||
1466         $id == 'event_relative' ||
1467         $id == 'case_from_relative' ||
1468         $id == 'case_to_relative'
1469       ) {
1470         if ($id == 'event_relative') {
1471           $fromRange = 'event_start_date_low';
1472           $toRange = 'event_end_date_high';
1473         }
1474         elseif ($id == 'case_from_relative') {
1475           $fromRange = 'case_from_start_date_low';
1476           $toRange = 'case_from_start_date_high';
1477         }
1478         elseif ($id == 'case_to_relative') {
1479           $fromRange = 'case_to_end_date_low';
1480           $toRange = 'case_to_end_date_high';
1481         }
1482         else {
1483           $dateComponent = explode('_date_relative', $id);
1484           $fromRange = "{$dateComponent[0]}_date_low";
1485           $toRange = "{$dateComponent[0]}_date_high";
1486         }
1487
1488         if (array_key_exists($fromRange, $formValues) && array_key_exists($toRange, $formValues)) {
1489           CRM_Contact_BAO_Query::fixDateValues($formValues[$id], $formValues[$fromRange], $formValues[$toRange]);
1490           continue;
1491         }
1492       }
1493       else {
1494         $values = CRM_Contact_BAO_Query::fixWhereValues($id, $values, $wildcard, $useEquals, $apiEntity);
1495
1496         if (!$values) {
1497           continue;
1498         }
1499         $params[] = $values;
1500       }
1501     }
1502     return $params;
1503   }
1504
1505   /**
1506    * @param int $id
1507    * @param $values
1508    * @param int $wildcard
1509    * @param bool $useEquals
1510    *
1511    * @param string $apiEntity
1512    *
1513    * @return array|null
1514    */
1515   public static function &fixWhereValues($id, &$values, $wildcard = 0, $useEquals = FALSE, $apiEntity = NULL) {
1516     // skip a few search variables
1517     static $skipWhere = NULL;
1518     static $likeNames = NULL;
1519     $result = NULL;
1520     // Change camelCase EntityName to lowercase with underscores
1521     $apiEntity = _civicrm_api_get_entity_name_from_camel($apiEntity);
1522
1523     if (CRM_Utils_System::isNull($values)) {
1524       return $result;
1525     }
1526
1527     if (!$skipWhere) {
1528       $skipWhere = array(
1529         'task',
1530         'radio_ts',
1531         'uf_group_id',
1532         'component_mode',
1533         'qfKey',
1534         'operator',
1535         'display_relationship_type',
1536       );
1537     }
1538
1539     if (in_array($id, $skipWhere) ||
1540       substr($id, 0, 4) == '_qf_' ||
1541       substr($id, 0, 7) == 'hidden_'
1542     ) {
1543       return $result;
1544     }
1545
1546     if ($apiEntity &&
1547       (substr($id, 0, strlen($apiEntity)) != $apiEntity) &&
1548       (substr($id, 0, 10) != 'financial_' && substr($id, 0, 8) != 'payment_')
1549     ) {
1550       $id = $apiEntity . '_' . $id;
1551     }
1552
1553     if (!$likeNames) {
1554       $likeNames = array('sort_name', 'email', 'note', 'display_name');
1555     }
1556
1557     // email comes in via advanced search
1558     // so use wildcard always
1559     if ($id == 'email') {
1560       $wildcard = 1;
1561     }
1562
1563     if (!$useEquals && in_array($id, $likeNames)) {
1564       $result = array($id, 'LIKE', $values, 0, 1);
1565     }
1566     elseif (is_string($values) && strpos($values, '%') !== FALSE) {
1567       $result = array($id, 'LIKE', $values, 0, 0);
1568     }
1569     elseif ($id == 'group') {
1570       if (is_array($values)) {
1571         foreach ($values as $groupIds => $val) {
1572           $matches = array();
1573           if (preg_match('/-(\d+)$/', $groupIds, $matches)) {
1574             if (strlen($matches[1]) > 0) {
1575               $values[$matches[1]] = 1;
1576               unset($values[$groupIds]);
1577             }
1578           }
1579         }
1580       }
1581       else {
1582         $groupIds = explode(',', $values);
1583         unset($values);
1584         foreach ($groupIds as $groupId) {
1585           $values[$groupId] = 1;
1586         }
1587       }
1588
1589       $result = array($id, 'IN', $values, 0, 0);
1590     }
1591     elseif ($id == 'contact_tags' || $id == 'tag') {
1592       if (!is_array($values)) {
1593         $tagIds = explode(',', $values);
1594         unset($values);
1595         foreach ($tagIds as $tagId) {
1596           $values[$tagId] = 1;
1597         }
1598       }
1599       $result = array($id, 'IN', $values, 0, 0);
1600     }
1601     elseif ($id == 'contact_type') {
1602       $result = array($id, 'IN', $values, 0, $wildcard);
1603     }
1604     else {
1605       $result = array($id, '=', $values, 0, $wildcard);
1606     }
1607
1608     return $result;
1609   }
1610
1611   /**
1612    * @param $values
1613    */
1614   public function whereClauseSingle(&$values) {
1615     // do not process custom fields or prefixed contact ids or component params
1616     if (CRM_Core_BAO_CustomField::getKeyID($values[0]) ||
1617       (substr($values[0], 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) ||
1618       (substr($values[0], 0, 13) == 'contribution_') ||
1619       (substr($values[0], 0, 6) == 'event_') ||
1620       (substr($values[0], 0, 12) == 'participant_') ||
1621       (substr($values[0], 0, 7) == 'member_') ||
1622       (substr($values[0], 0, 6) == 'grant_') ||
1623       (substr($values[0], 0, 7) == 'pledge_') ||
1624       (substr($values[0], 0, 5) == 'case_') ||
1625       (substr($values[0], 0, 10) == 'financial_') ||
1626       (substr($values[0], 0, 8) == 'payment_') ||
1627       (substr($values[0], 0, 11) == 'membership_')
1628     ) {
1629       return;
1630     }
1631
1632     // skip for hook injected fields / params
1633     $extFields = CRM_Contact_BAO_Query_Hook::singleton()->getFields();
1634     if (array_key_exists($values[0], $extFields)) {
1635       return;
1636     }
1637
1638     switch ($values[0]) {
1639       case 'deleted_contacts':
1640         $this->deletedContacts($values);
1641         return;
1642
1643       case 'contact_type':
1644         $this->contactType($values);
1645         return;
1646
1647       case 'contact_sub_type':
1648         $this->contactSubType($values);
1649         return;
1650
1651       case 'group':
1652         $this->group($values);
1653         return;
1654
1655       case 'group_type':
1656         // so we resolve this into a list of groups & proceed as if they had been
1657         // handed in
1658         list($name, $op, $value, $grouping, $wildcard) = $values;
1659         $values[0] = 'group';
1660         $values[1] = 'IN';
1661         $this->_paramLookup['group'][0][0] = 'group';
1662         $this->_paramLookup['group'][0][1] = 'IN';
1663         $this->_paramLookup['group'][0][2] = $values[2] = $this->getGroupsFromTypeCriteria($value);
1664         $this->group($values);
1665         return;
1666
1667       // case tag comes from find contacts
1668       case 'tag_search':
1669         $this->tagSearch($values);
1670         return;
1671
1672       case 'tag':
1673       case 'contact_tags':
1674         $this->tag($values);
1675         return;
1676
1677       case 'note':
1678       case 'note_body':
1679       case 'note_subject':
1680         $this->notes($values);
1681         return;
1682
1683       case 'uf_user':
1684         $this->ufUser($values);
1685         return;
1686
1687       case 'sort_name':
1688       case 'display_name':
1689         $this->sortName($values);
1690         return;
1691
1692       case 'addressee':
1693       case 'postal_greeting':
1694       case 'email_greeting':
1695         $this->greetings($values);
1696         return;
1697
1698       case 'email':
1699         $this->email($values);
1700         return;
1701
1702       case 'phone_numeric':
1703         $this->phone_numeric($values);
1704         return;
1705
1706       case 'phone_phone_type_id':
1707       case 'phone_location_type_id':
1708         $this->phone_option_group($values);
1709         return;
1710
1711       case 'street_address':
1712         $this->street_address($values);
1713         return;
1714
1715       case 'street_number':
1716         $this->street_number($values);
1717         return;
1718
1719       case 'sortByCharacter':
1720         $this->sortByCharacter($values);
1721         return;
1722
1723       case 'location_type':
1724         $this->locationType($values);
1725         return;
1726
1727       case 'county':
1728         $this->county($values);
1729         return;
1730
1731       case 'state_province':
1732         $this->stateProvince($values);
1733         return;
1734
1735       case 'country':
1736         $this->country($values, FALSE);
1737         return;
1738
1739       case 'postal_code':
1740       case 'postal_code_low':
1741       case 'postal_code_high':
1742         $this->postalCode($values);
1743         return;
1744
1745       case 'activity_date':
1746       case 'activity_date_low':
1747       case 'activity_date_high':
1748       case 'activity_role':
1749       case 'activity_status_id':
1750       case 'activity_status':
1751       case 'followup_parent_id':
1752       case 'parent_id':
1753       case 'source_contact_id':
1754       case 'activity_subject':
1755       case 'test_activities':
1756       case 'activity_type_id':
1757       case 'activity_type':
1758       case 'activity_survey_id':
1759       case 'activity_tags':
1760       case 'activity_taglist':
1761       case 'activity_test':
1762       case 'activity_campaign_id':
1763       case 'activity_engagement_level':
1764       case 'activity_id':
1765       case 'activity_result':
1766       case 'source_contact':
1767         CRM_Activity_BAO_Query::whereClauseSingle($values, $this);
1768         return;
1769
1770       case 'birth_date_low':
1771       case 'birth_date_high':
1772       case 'deceased_date_low':
1773       case 'deceased_date_high':
1774         $this->demographics($values);
1775         return;
1776
1777       case 'log_date_low':
1778       case 'log_date_high':
1779         $this->modifiedDates($values);
1780         return;
1781
1782       case 'changed_by':
1783         $this->changeLog($values);
1784         return;
1785
1786       case 'do_not_phone':
1787       case 'do_not_email':
1788       case 'do_not_mail':
1789       case 'do_not_sms':
1790       case 'do_not_trade':
1791       case 'is_opt_out':
1792         $this->privacy($values);
1793         return;
1794
1795       case 'privacy_options':
1796         $this->privacyOptions($values);
1797         return;
1798
1799       case 'privacy_operator':
1800       case 'privacy_toggle':
1801         // these are handled by privacy options
1802         return;
1803
1804       case 'preferred_communication_method':
1805         $this->preferredCommunication($values);
1806         return;
1807
1808       case 'relation_type_id':
1809       case 'relation_start_date_high':
1810       case 'relation_start_date_low':
1811       case 'relation_end_date_high':
1812       case 'relation_end_date_low':
1813       case 'relation_target_name':
1814       case 'relation_status':
1815       case 'relation_date_low':
1816       case 'relation_date_high':
1817         $this->relationship($values);
1818         $this->_relationshipValuesAdded = TRUE;
1819         return;
1820
1821       case 'task_status_id':
1822         $this->task($values);
1823         return;
1824
1825       case 'task_id':
1826         // since this case is handled with the above
1827         return;
1828
1829       case 'prox_distance':
1830         CRM_Contact_BAO_ProximityQuery::process($this, $values);
1831         return;
1832
1833       case 'prox_street_address':
1834       case 'prox_city':
1835       case 'prox_postal_code':
1836       case 'prox_state_province_id':
1837       case 'prox_country_id':
1838         // handled by the proximity_distance clause
1839         return;
1840
1841       default:
1842         $this->restWhere($values);
1843         return;
1844     }
1845   }
1846
1847   /**
1848    * Given a list of conditions in params generate the required.
1849    * where clause
1850    *
1851    * @return string
1852    */
1853   public function whereClause() {
1854     $this->_where[0] = array();
1855     $this->_qill[0] = array();
1856
1857     $this->includeContactIds();
1858     if (!empty($this->_params)) {
1859       foreach (array_keys($this->_params) as $id) {
1860         if (empty($this->_params[$id][0])) {
1861           continue;
1862         }
1863         // check for both id and contact_id
1864         if ($this->_params[$id][0] == 'id' || $this->_params[$id][0] == 'contact_id') {
1865           if (
1866             $this->_params[$id][1] == 'IS NULL' ||
1867             $this->_params[$id][1] == 'IS NOT NULL'
1868           ) {
1869             $this->_where[0][] = "contact_a.id {$this->_params[$id][1]}";
1870           }
1871           elseif (is_array($this->_params[$id][2])) {
1872             $idList = implode("','", $this->_params[$id][2]);
1873             //why on earth do they put ' in the middle & not on the outside? We have to assume it's
1874             //to support 'something' so lets add them conditionally to support the api (which is a tested flow
1875             // so if you are looking to alter this check api test results
1876             if (strpos(trim($idList), "'") > 0) {
1877               $idList = "'" . $idList . "'";
1878             }
1879
1880             $this->_where[0][] = "contact_a.id IN ({$idList})";
1881           }
1882           else {
1883             $this->_where[0][] = self::buildClause("contact_a.id", "{$this->_params[$id][1]}", "{$this->_params[$id][2]}");
1884           }
1885         }
1886         else {
1887           $this->whereClauseSingle($this->_params[$id]);
1888         }
1889       }
1890
1891       CRM_Core_Component::alterQuery($this, 'where');
1892
1893       CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'where');
1894     }
1895
1896     if ($this->_customQuery) {
1897       // Added following if condition to avoid the wrong value diplay for 'myaccount' / any UF info.
1898       // Hope it wont affect the other part of civicrm.. if it does please remove it.
1899       if (!empty($this->_customQuery->_where)) {
1900         $this->_where = CRM_Utils_Array::crmArrayMerge($this->_where, $this->_customQuery->_where);
1901       }
1902
1903       $this->_qill = CRM_Utils_Array::crmArrayMerge($this->_qill, $this->_customQuery->_qill);
1904     }
1905
1906     $clauses = array();
1907     $andClauses = array();
1908
1909     $validClauses = 0;
1910     if (!empty($this->_where)) {
1911       foreach ($this->_where as $grouping => $values) {
1912         if ($grouping > 0 && !empty($values)) {
1913           $clauses[$grouping] = ' ( ' . implode(" {$this->_operator} ", $values) . ' ) ';
1914           $validClauses++;
1915         }
1916       }
1917
1918       if (!empty($this->_where[0])) {
1919         $andClauses[] = ' ( ' . implode(" {$this->_operator} ", $this->_where[0]) . ' ) ';
1920       }
1921       if (!empty($clauses)) {
1922         $andClauses[] = ' ( ' . implode(' OR ', $clauses) . ' ) ';
1923       }
1924
1925       if ($validClauses > 1) {
1926         $this->_useDistinct = TRUE;
1927       }
1928     }
1929
1930     return implode(' AND ', $andClauses);
1931   }
1932
1933   /**
1934    * @param $values
1935    *
1936    * @throws Exception
1937    */
1938   public function restWhere(&$values) {
1939     $name = CRM_Utils_Array::value(0, $values);
1940     $op = CRM_Utils_Array::value(1, $values);
1941     $value = CRM_Utils_Array::value(2, $values);
1942     $grouping = CRM_Utils_Array::value(3, $values);
1943     $wildcard = CRM_Utils_Array::value(4, $values);
1944
1945     if (isset($grouping) && empty($this->_where[$grouping])) {
1946       $this->_where[$grouping] = array();
1947     }
1948
1949     $multipleFields = array('url');
1950
1951     //check if the location type exits for fields
1952     $lType = '';
1953     $locType = explode('-', $name);
1954
1955     if (!in_array($locType[0], $multipleFields)) {
1956       //add phone type if exists
1957       if (isset($locType[2]) && $locType[2]) {
1958         $locType[2] = CRM_Core_DAO::escapeString($locType[2]);
1959       }
1960     }
1961
1962     $field = CRM_Utils_Array::value($name, $this->_fields);
1963
1964     if (!$field) {
1965       $field = CRM_Utils_Array::value($locType[0], $this->_fields);
1966
1967       if (!$field) {
1968         return;
1969       }
1970     }
1971
1972     $setTables = TRUE;
1973
1974     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
1975     $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
1976
1977     if (substr($name, 0, 14) === 'state_province') {
1978       if (isset($locType[1]) && is_numeric($locType[1])) {
1979         $setTables = FALSE;
1980         $aName = "{$locationType[$locType[1]]}-address";
1981         $where = "`$aName`.state_province_id";
1982       }
1983       else {
1984         $where = "civicrm_address.state_province_id";
1985       }
1986
1987       $states = CRM_Core_PseudoConstant::stateProvince();
1988       if (is_numeric($value)) {
1989         $this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
1990         $value = $states[(int ) $value];
1991       }
1992       else {
1993         $intVal = CRM_Utils_Array::key($value, $states);
1994         $this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
1995       }
1996       if (!$lType) {
1997         $this->_qill[$grouping][] = ts('State') . " $op '$value'";
1998       }
1999       else {
2000         $this->_qill[$grouping][] = ts('State') . " ($lType) $op '$value'";
2001       }
2002     }
2003     elseif (!empty($field['pseudoconstant'])) {
2004       $this->optionValueQuery(
2005         $name, $op, $value, $grouping,
2006         CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', $field['name']),
2007         $field,
2008         $field['title'],
2009         'String',
2010         TRUE
2011       );
2012       if ($name == 'gender_id') {
2013         self::$_openedPanes[ts('Demographics')] = TRUE;
2014       }
2015     }
2016     elseif (substr($name, 0, 7) === 'country') {
2017       if (isset($locType[1]) && is_numeric($locType[1])) {
2018         $setTables = FALSE;
2019         $aName = "{$locationType[$locType[1]]}-address";
2020         $where = "`$aName`.country_id";
2021       }
2022       else {
2023         $where = "civicrm_address.country_id";
2024       }
2025
2026       $countries = CRM_Core_PseudoConstant::country();
2027       if (is_numeric($value)) {
2028         $this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
2029         $value = $countries[(int ) $value];
2030       }
2031       else {
2032         $intVal = CRM_Utils_Array::key($value, $countries);
2033         $this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
2034       }
2035
2036       if (!$lType) {
2037         $this->_qill[$grouping][] = ts('Country') . " $op '$value'";
2038       }
2039       else {
2040         $this->_qill[$grouping][] = ts('Country') . " ($lType) $op '$value'";
2041       }
2042     }
2043     elseif (substr($name, 0, 6) === 'county') {
2044       if (isset($locType[1]) && is_numeric($locType[1])) {
2045         $setTables = FALSE;
2046         $aName = "{$locationType[$locType[1]]}-address";
2047         $where = "`$aName`.county_id";
2048       }
2049       else {
2050         $where = "civicrm_address.county_id";
2051       }
2052
2053       $counties = CRM_Core_PseudoConstant::county();
2054       if (is_numeric($value)) {
2055         $this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
2056         $value = $counties[(int ) $value];
2057       }
2058       else {
2059         $intVal = CRM_Utils_Array::key($value, $counties);
2060         $this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
2061       }
2062
2063       if (!$lType) {
2064         $this->_qill[$grouping][] = ts('County') . " $op '$value'";
2065       }
2066       else {
2067         $this->_qill[$grouping][] = ts('County') . " ($lType) $op '$value'";
2068       }
2069     }
2070     elseif ($name === 'world_region') {
2071       $field['where'] = 'civicrm_worldregion.id';
2072       $this->optionValueQuery(
2073         $name, $op, $value, $grouping,
2074         CRM_Core_PseudoConstant::worldRegion(),
2075         $field,
2076         ts('World Region'),
2077         'Positive',
2078         TRUE
2079       );
2080     }
2081     elseif ($name === 'birth_date') {
2082       $date = CRM_Utils_Date::processDate($value);
2083       $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
2084
2085       if ($date) {
2086         $date = CRM_Utils_Date::customFormat($date);
2087         $this->_qill[$grouping][] = "$field[title] $op \"$date\"";
2088       }
2089       else {
2090         $this->_qill[$grouping][] = "$field[title] $op";
2091       }
2092       self::$_openedPanes[ts('Demographics')] = TRUE;
2093     }
2094     elseif ($name === 'deceased_date') {
2095       $date = CRM_Utils_Date::processDate($value);
2096       $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
2097       if ($date) {
2098         $date = CRM_Utils_Date::customFormat($date);
2099         $this->_qill[$grouping][] = "$field[title] $op \"$date\"";
2100       }
2101       else {
2102         $this->_qill[$grouping][] = "$field[title] $op";
2103       }
2104       self::$_openedPanes[ts('Demographics')] = TRUE;
2105     }
2106     elseif ($name === 'is_deceased') {
2107       $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
2108       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2109       self::$_openedPanes[ts('Demographics')] = TRUE;
2110     }
2111     elseif ($name === 'contact_id') {
2112       if (is_int($value)) {
2113         $this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
2114         $this->_qill[$grouping][] = "$field[title] $op $value";
2115       }
2116     }
2117     elseif ($name === 'name') {
2118       $value = $strtolower(CRM_Core_DAO::escapeString($value));
2119       if ($wildcard) {
2120         $value = "%$value%";
2121         $op = 'LIKE';
2122       }
2123       $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
2124       $this->_where[$grouping][] = self::buildClause($wc, $op, "'$value'");
2125       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2126     }
2127     elseif ($name === 'current_employer') {
2128       $value = $strtolower(CRM_Core_DAO::escapeString($value));
2129       if ($wildcard) {
2130         $value = "%$value%";
2131         $op = 'LIKE';
2132       }
2133       $wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
2134       $ceWhereClause = self::buildClause($wc, $op,
2135         $value
2136       );
2137       $ceWhereClause .= " AND contact_a.contact_type = 'Individual'";
2138       $this->_where[$grouping][] = $ceWhereClause;
2139       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2140     }
2141     elseif ($name === 'email_greeting') {
2142       $filterCondition = array('greeting_type' => 'email_greeting');
2143       $this->optionValueQuery(
2144         $name, $op, $value, $grouping,
2145         CRM_Core_PseudoConstant::greeting($filterCondition),
2146         $field,
2147         ts('Email Greeting')
2148       );
2149     }
2150     elseif ($name === 'postal_greeting') {
2151       $filterCondition = array('greeting_type' => 'postal_greeting');
2152       $this->optionValueQuery(
2153         $name, $op, $value, $grouping,
2154         CRM_Core_PseudoConstant::greeting($filterCondition),
2155         $field,
2156         ts('Postal Greeting')
2157       );
2158     }
2159     elseif ($name === 'addressee') {
2160       $filterCondition = array('greeting_type' => 'addressee');
2161       $this->optionValueQuery(
2162         $name, $op, $value, $grouping,
2163         CRM_Core_PseudoConstant::greeting($filterCondition),
2164         $field,
2165         ts('Addressee')
2166       );
2167     }
2168     elseif (substr($name, 0, 4) === 'url-') {
2169       $tName = 'civicrm_website';
2170       $this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
2171       $value = $strtolower(CRM_Core_DAO::escapeString($value));
2172       if ($wildcard) {
2173         $value = "%$value%";
2174         $op = 'LIKE';
2175       }
2176
2177       $wc = 'civicrm_website.url';
2178       $this->_where[$grouping][] = $d = self::buildClause($wc, $op, $value);
2179       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2180     }
2181     elseif ($name === 'contact_is_deleted') {
2182       $this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
2183       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2184     }
2185     else {
2186       if (is_array($value)) {
2187         // traditionally an array being passed has been a fatal error. We can take advantage of this to add support
2188         // for api style operators for functions that hit this point without worrying about regression
2189         // (the previous comments indicated the condition for hitting this point were unknown
2190         // per CRM-14743 we are adding modified_date & created_date operator support
2191         $operations = array_keys($value);
2192         foreach ($operations as $operator) {
2193           if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) {
2194             // we don't know when this might happen
2195             CRM_Core_Error::fatal();
2196           }
2197         }
2198         $this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($name, $value, NULL);
2199         //since this is not currently being called by the form layer we can skip worrying about the 'qill' for now
2200         return;
2201       }
2202
2203       if (!empty($field['where'])) {
2204         if ($op != 'IN') {
2205           $value = $strtolower($value);
2206         }
2207         if ($wildcard) {
2208           $value = "%$value%";
2209           $op = 'LIKE';
2210         }
2211
2212         if (isset($locType[1]) &&
2213           is_numeric($locType[1])
2214         ) {
2215           $setTables = FALSE;
2216
2217           //get the location name
2218           list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
2219
2220           $where = "`$tName`.$fldName";
2221
2222           $this->_where[$grouping][] = self::buildClause("LOWER($where)", $op, $value);
2223           // we set both _tables & whereTables because whereTables doesn't seem to do what the name implies it should
2224           $this->_tables[$tName] = $this->_whereTables[$tName] = 1;
2225           $this->_qill[$grouping][] = "$field[title] $op '$value'";
2226         }
2227         else {
2228           list($tableName, $fieldName) = explode('.', $field['where'], 2);
2229           if ($tableName == 'civicrm_contact') {
2230             $fieldName = "LOWER(contact_a.{$fieldName})";
2231           }
2232           else {
2233             if ($op != 'IN' && !is_numeric($value)) {
2234               $fieldName = "LOWER({$field['where']})";
2235             }
2236             else {
2237               $fieldName = "{$field['where']}";
2238             }
2239           }
2240
2241           $type = NULL;
2242           if (!empty($field['type'])) {
2243             $type = CRM_Utils_Type::typeToString($field['type']);
2244           }
2245
2246           $this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
2247           $this->_qill[$grouping][] = "$field[title] $op $value";
2248         }
2249       }
2250     }
2251
2252     if ($setTables && isset($field['where'])) {
2253       list($tableName, $fieldName) = explode('.', $field['where'], 2);
2254       if (isset($tableName)) {
2255         $this->_tables[$tableName] = 1;
2256         $this->_whereTables[$tableName] = 1;
2257       }
2258     }
2259   }
2260
2261
2262   /**
2263    * @param $where
2264    * @param $locType
2265    *
2266    * @return array
2267    * @throws Exception
2268    */
2269   public static function getLocationTableName(&$where, &$locType) {
2270     if (isset($locType[1]) && is_numeric($locType[1])) {
2271       list($tbName, $fldName) = explode(".", $where);
2272
2273       //get the location name
2274       $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
2275       $specialFields = array('email', 'im', 'phone', 'openid', 'phone_ext');
2276       if (in_array($locType[0], $specialFields)) {
2277         //hack to fix / special handing for phone_ext
2278         if ($locType[0] == 'phone_ext') {
2279           $locType[0] = 'phone';
2280         }
2281         if (isset($locType[2]) && $locType[2]) {
2282           $tName = "{$locationType[$locType[1]]}-{$locType[0]}-{$locType[2]}";
2283         }
2284         else {
2285           $tName = "{$locationType[$locType[1]]}-{$locType[0]}";
2286         }
2287       }
2288       elseif (in_array($locType[0],
2289         array(
2290           'address_name',
2291           'street_address',
2292           'supplemental_address_1',
2293           'supplemental_address_2',
2294           'city',
2295           'postal_code',
2296           'postal_code_suffix',
2297           'geo_code_1',
2298           'geo_code_2',
2299         )
2300       )) {
2301         //fix for search by profile with address fields.
2302         $tName = "{$locationType[$locType[1]]}-address";
2303       }
2304       elseif ($locType[0] == 'on_hold') {
2305         $tName = "{$locationType[$locType[1]]}-email";
2306       }
2307       else {
2308         $tName = "{$locationType[$locType[1]]}-{$locType[0]}";
2309       }
2310       $tName = str_replace(' ', '_', $tName);
2311       return array($tName, $fldName);
2312     }
2313     CRM_Core_Error::fatal();
2314   }
2315
2316   /**
2317    * Given a result dao, extract the values and return that array
2318    *
2319    * @param CRM_Core_DAO $dao
2320    *
2321    * @return array
2322    *   values for this query
2323    */
2324   public function store($dao) {
2325     $value = array();
2326
2327     foreach ($this->_element as $key => $dontCare) {
2328       if (property_exists($dao, $key)) {
2329         if (strpos($key, '-') !== FALSE) {
2330           $values = explode('-', $key);
2331           $lastElement = array_pop($values);
2332           $current = &$value;
2333           $cnt = count($values);
2334           $count = 1;
2335           foreach ($values as $v) {
2336             if (!array_key_exists($v, $current)) {
2337               $current[$v] = array();
2338             }
2339             //bad hack for im_provider
2340             if ($lastElement == 'provider_id') {
2341               if ($count < $cnt) {
2342                 $current = &$current[$v];
2343               }
2344               else {
2345                 $lastElement = "{$v}_{$lastElement}";
2346               }
2347             }
2348             else {
2349               $current = &$current[$v];
2350             }
2351             $count++;
2352           }
2353
2354           $current[$lastElement] = $dao->$key;
2355         }
2356         else {
2357           $value[$key] = $dao->$key;
2358         }
2359       }
2360     }
2361     return $value;
2362   }
2363
2364   /**
2365    * Getter for tables array.
2366    *
2367    * @return array
2368    */
2369   public function tables() {
2370     return $this->_tables;
2371   }
2372
2373   /**
2374    * Where tables is sometimes used to create the from clause, but, not reliably, set this AND set tables
2375    * It's unclear the intent - there is a 'simpleFrom' clause which takes whereTables into account & a fromClause which doesn't
2376    * logic may have eroded
2377    * @return array
2378    */
2379   public function whereTables() {
2380     return $this->_whereTables;
2381   }
2382
2383   /**
2384    * Generate the where clause (used in match contacts and permissions)
2385    *
2386    * @param array $params
2387    * @param array $fields
2388    * @param array $tables
2389    * @param $whereTables
2390    * @param bool $strict
2391    *
2392    * @return string
2393    */
2394   public static function getWhereClause($params, $fields, &$tables, &$whereTables, $strict = FALSE) {
2395     $query = new CRM_Contact_BAO_Query($params, NULL, $fields,
2396       FALSE, $strict
2397     );
2398
2399     $tables = array_merge($query->tables(), $tables);
2400     $whereTables = array_merge($query->whereTables(), $whereTables);
2401
2402     return $query->_whereClause;
2403   }
2404
2405   /**
2406    * Create the from clause.
2407    *
2408    * @param array $tables
2409    *   Tables that need to be included in this from clause.
2410    *                      if null, return mimimal from clause (i.e. civicrm_contact)
2411    * @param array $inner
2412    *   Tables that should be inner-joined.
2413    * @param array $right
2414    *   Tables that should be right-joined.
2415    *
2416    * @param bool $primaryLocation
2417    * @param int $mode
2418    *
2419    * @return string
2420    *   the from clause
2421    */
2422   public static function fromClause(&$tables, $inner = NULL, $right = NULL, $primaryLocation = TRUE, $mode = 1) {
2423
2424     $from = ' FROM civicrm_contact contact_a';
2425     if (empty($tables)) {
2426       return $from;
2427     }
2428
2429     if (!empty($tables['civicrm_worldregion'])) {
2430       $tables = array_merge(array('civicrm_country' => 1), $tables);
2431     }
2432
2433     if ((!empty($tables['civicrm_state_province']) || !empty($tables['civicrm_country']) ||
2434         CRM_Utils_Array::value('civicrm_county', $tables)
2435       ) && empty($tables['civicrm_address'])
2436     ) {
2437       $tables = array_merge(array('civicrm_address' => 1),
2438         $tables
2439       );
2440     }
2441
2442     // add group_contact table if group table is present
2443     if (!empty($tables['civicrm_group']) && empty($tables['civicrm_group_contact'])) {
2444       $tables['civicrm_group_contact'] = " LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id AND civicrm_group_contact.status = 'Added'";
2445     }
2446
2447     // add group_contact and group table is subscription history is present
2448     if (!empty($tables['civicrm_subscription_history']) && empty($tables['civicrm_group'])) {
2449       $tables = array_merge(array(
2450           'civicrm_group' => 1,
2451           'civicrm_group_contact' => 1,
2452         ),
2453         $tables
2454       );
2455     }
2456
2457     // to handle table dependencies of components
2458     CRM_Core_Component::tableNames($tables);
2459     // to handle table dependencies of hook injected tables
2460     CRM_Contact_BAO_Query_Hook::singleton()->setTableDependency($tables);
2461
2462     //format the table list according to the weight
2463     $info = CRM_Core_TableHierarchy::info();
2464
2465     foreach ($tables as $key => $value) {
2466       $k = 99;
2467       if (strpos($key, '-') !== FALSE) {
2468         $keyArray = explode('-', $key);
2469         $k = CRM_Utils_Array::value('civicrm_' . $keyArray[1], $info, 99);
2470       }
2471       elseif (strpos($key, '_') !== FALSE) {
2472         $keyArray = explode('_', $key);
2473         if (is_numeric(array_pop($keyArray))) {
2474           $k = CRM_Utils_Array::value(implode('_', $keyArray), $info, 99);
2475         }
2476         else {
2477           $k = CRM_Utils_Array::value($key, $info, 99);
2478         }
2479       }
2480       else {
2481         $k = CRM_Utils_Array::value($key, $info, 99);
2482       }
2483       $tempTable[$k . ".$key"] = $key;
2484     }
2485     ksort($tempTable);
2486     $newTables = array();
2487     foreach ($tempTable as $key) {
2488       $newTables[$key] = $tables[$key];
2489     }
2490
2491     $tables = $newTables;
2492
2493     foreach ($tables as $name => $value) {
2494       if (!$value) {
2495         continue;
2496       }
2497
2498       if (!empty($inner[$name])) {
2499         $side = 'INNER';
2500       }
2501       elseif (!empty($right[$name])) {
2502         $side = 'RIGHT';
2503       }
2504       else {
2505         $side = 'LEFT';
2506       }
2507
2508       if ($value != 1) {
2509         // if there is already a join statement in value, use value itself
2510         if (strpos($value, 'JOIN')) {
2511           $from .= " $value ";
2512         }
2513         else {
2514           $from .= " $side JOIN $name ON ( $value ) ";
2515         }
2516         continue;
2517       }
2518       switch ($name) {
2519         case 'civicrm_address':
2520           if ($primaryLocation) {
2521             $from .= " $side JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )";
2522           }
2523           else {
2524             //CRM-14263 further handling of address joins further down...
2525             $from .= " $side JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) ";
2526           }
2527           continue;
2528
2529         case 'civicrm_phone':
2530           $from .= " $side JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) ";
2531           continue;
2532
2533         case 'civicrm_email':
2534           $from .= " $side JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) ";
2535           continue;
2536
2537         case 'civicrm_im':
2538           $from .= " $side JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) ";
2539           continue;
2540
2541         case 'im_provider':
2542           $from .= " $side JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id) ";
2543           $from .= " $side JOIN civicrm_option_group option_group_imProvider ON option_group_imProvider.name = 'instant_messenger_service'";
2544           $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)";
2545           continue;
2546
2547         case 'civicrm_openid':
2548           $from .= " $side JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id AND civicrm_openid.is_primary = 1 )";
2549           continue;
2550
2551         case 'civicrm_worldregion':
2552           $from .= " $side JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id ";
2553           $from .= " $side JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ";
2554           continue;
2555
2556         case 'civicrm_location_type':
2557           $from .= " $side JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id ";
2558           continue;
2559
2560         case 'civicrm_group':
2561           $from .= " $side JOIN civicrm_group ON civicrm_group.id =  civicrm_group_contact.group_id ";
2562           continue;
2563
2564         case 'civicrm_group_contact':
2565           $from .= " $side JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id ";
2566           continue;
2567
2568         case 'civicrm_activity':
2569         case 'civicrm_activity_tag':
2570         case 'activity_type':
2571         case 'activity_status':
2572         case 'parent_id':
2573         case 'civicrm_activity_contact':
2574         case 'source_contact':
2575           $from .= CRM_Activity_BAO_Query::from($name, $mode, $side);
2576           continue;
2577
2578         case 'civicrm_entity_tag':
2579           $from .= " $side JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
2580                                                               civicrm_entity_tag.entity_id = contact_a.id ) ";
2581           continue;
2582
2583         case 'civicrm_note':
2584           $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND
2585                                                         contact_a.id = civicrm_note.entity_id ) ";
2586           continue;
2587
2588         case 'civicrm_subscription_history':
2589           $from .= " $side JOIN civicrm_subscription_history
2590                                    ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id
2591                                   AND civicrm_group_contact.group_id =  civicrm_subscription_history.group_id";
2592           continue;
2593
2594         case 'civicrm_relationship':
2595           if (self::$_relType == 'reciprocal') {
2596             if (self::$_relationshipTempTable) {
2597               // we have a temptable to join on
2598               $tbl = self::$_relationshipTempTable;
2599               $from .= " INNER JOIN {$tbl} civicrm_relationship ON civicrm_relationship.contact_id = contact_a.id";
2600             }
2601             else {
2602               $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id)";
2603               $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)";
2604             }
2605           }
2606           elseif (self::$_relType == 'b') {
2607             $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id )";
2608             $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )";
2609           }
2610           else {
2611             $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id )";
2612             $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )";
2613           }
2614           continue;
2615
2616         case 'civicrm_log':
2617           $from .= " INNER JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact')";
2618           $from .= " INNER JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id)";
2619           continue;
2620
2621         case 'civicrm_tag':
2622           $from .= " $side  JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id ";
2623           continue;
2624
2625         case 'civicrm_grant':
2626           $from .= CRM_Grant_BAO_Query::from($name, $mode, $side);
2627           continue;
2628
2629         case 'civicrm_website':
2630           $from .= " $side JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id ";
2631           continue;
2632
2633         default:
2634           $locationTypeName = '';
2635           if (strpos($name, '-address') != 0) {
2636             $locationTypeName = 'address';
2637           }
2638           elseif (strpos($name, '-phone') != 0) {
2639             $locationTypeName = 'phone';
2640           }
2641           elseif (strpos($name, '-email') != 0) {
2642             $locationTypeName = 'email';
2643           }
2644           if ($locationTypeName) {
2645             //we have a join on an location table - possibly in conjunction with search builder - CRM-14263
2646             $parts = explode('-', $name);
2647             $locationID = array_search($parts[0], CRM_Core_BAO_Address::buildOptions('location_type_id', 'get', array('name' => $parts[0])));
2648             $from .= " $side JOIN civicrm_{$locationTypeName} `{$name}` ON ( contact_a.id = `{$name}`.contact_id ) and `{$name}`.location_type_id = $locationID ";
2649           }
2650           else {
2651             $from .= CRM_Core_Component::from($name, $mode, $side);
2652           }
2653           $from .= CRM_Contact_BAO_Query_Hook::singleton()->buildSearchfrom($name, $mode, $side);
2654
2655           continue;
2656       }
2657     }
2658     return $from;
2659   }
2660
2661   /**
2662    * WHERE / QILL clause for deleted_contacts
2663    *
2664    * @param $values
2665    *
2666    * @return void
2667    */
2668   public function deletedContacts($values) {
2669     list($_, $_, $value, $grouping, $_) = $values;
2670     if ($value) {
2671       // *prepend* to the relevant grouping as this is quite an important factor
2672       array_unshift($this->_qill[$grouping], ts('Search in Trash'));
2673     }
2674   }
2675
2676   /**
2677    * Where / qill clause for contact_type
2678    *
2679    * @param $values
2680    *
2681    * @return void
2682    */
2683   public function contactType(&$values) {
2684     list($name, $op, $value, $grouping, $wildcard) = $values;
2685
2686     $subTypes = array();
2687     $clause = array();
2688
2689     // account for search builder mapping multiple values
2690     if (!is_array($value)) {
2691       $values = self::parseSearchBuilderString($value, 'String');
2692       if (is_array($values)) {
2693         $value = array_flip($values);
2694       }
2695     }
2696
2697     if (is_array($value)) {
2698       foreach ($value as $k => $v) {
2699         // fix for CRM-771
2700         if ($k) {
2701           $subType = NULL;
2702           $contactType = $k;
2703           if (strpos($k, CRM_Core_DAO::VALUE_SEPARATOR)) {
2704             list($contactType, $subType) = explode(CRM_Core_DAO::VALUE_SEPARATOR, $k, 2);
2705           }
2706
2707           if (!empty($subType)) {
2708             $subTypes[$subType] = 1;
2709           }
2710           $clause[$contactType] = "'" . CRM_Utils_Type::escape($contactType, 'String') . "'";
2711         }
2712       }
2713     }
2714     else {
2715       $contactTypeANDSubType = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value, 2);
2716       $contactType = $contactTypeANDSubType[0];
2717       $subType = CRM_Utils_Array::value(1, $contactTypeANDSubType);
2718       if (!empty($subType)) {
2719         $subTypes[$subType] = 1;
2720       }
2721       $clause[$contactType] = "'" . CRM_Utils_Type::escape($contactType, 'String') . "'";
2722     }
2723
2724     // fix for CRM-771
2725     if (!empty($clause)) {
2726       $quill = $clause;
2727       if ($op == 'IN' || $op == 'NOT IN') {
2728         $this->_where[$grouping][] = "contact_a.contact_type $op (" . implode(',', $clause) . ')';
2729       }
2730       else {
2731         $type = array_pop($clause);
2732         $this->_where[$grouping][] = self::buildClause("contact_a.contact_type", $op, $contactType);
2733       }
2734
2735       $this->_qill[$grouping][] = ts('Contact Type') . ' - ' . implode(' ' . ts('or') . ' ', $quill);
2736
2737       if (!empty($subTypes)) {
2738         $this->includeContactSubTypes($subTypes, $grouping);
2739       }
2740     }
2741   }
2742
2743   /**
2744    * Where / qill clause for contact_sub_type
2745    *
2746    * @param $values
2747    *
2748    * @return void
2749    */
2750   public function contactSubType(&$values) {
2751     list($name, $op, $value, $grouping, $wildcard) = $values;
2752     $this->includeContactSubTypes($value, $grouping, $op);
2753   }
2754
2755   /**
2756    * @param $value
2757    * @param $grouping
2758    * @param string $op
2759    */
2760   public function includeContactSubTypes($value, $grouping, $op = 'LIKE') {
2761
2762     $clause = array();
2763     $alias = "contact_a.contact_sub_type";
2764     $qillOperators = array('NOT LIKE' => ts('Not Like')) + CRM_Core_SelectValues::getSearchBuilderOperators();
2765
2766     $op = str_replace('IN', 'LIKE', $op);
2767     $op = str_replace('=', 'LIKE', $op);
2768     $op = str_replace('!', 'NOT ', $op);
2769
2770     if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) {
2771       $this->_where[$grouping][] = self::buildClause($alias, $op, $value, 'String');
2772     }
2773     elseif (is_array($value)) {
2774       foreach ($value as $k => $v) {
2775         if (!empty($k)) {
2776           $clause[$k] = "($alias $op '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($k, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')";
2777         }
2778       }
2779     }
2780     else {
2781       $clause[$value] = "($alias $op '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($value, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')";
2782     }
2783
2784     if (!empty($clause)) {
2785       $this->_where[$grouping][] = "( " . implode(' OR ', $clause) . " )";
2786     }
2787     $this->_qill[$grouping][] = ts('Contact Subtype %1 ', array(1 => $qillOperators[$op])) . implode(' ' . ts('or') . ' ', array_keys($clause));
2788   }
2789
2790   /**
2791    * Where / qill clause for groups
2792    *
2793    * @param $values
2794    *
2795    * @return void
2796    */
2797   public function group(&$values) {
2798     list($name, $op, $value, $grouping, $wildcard) = $values;
2799
2800     if (count($value) > 1) {
2801       $this->_useDistinct = TRUE;
2802     }
2803
2804     $groupNames = CRM_Core_PseudoConstant::group();
2805     $groupIds = implode(',', array_keys($value));
2806
2807     $names = array();
2808     foreach ($value as $id => $dontCare) {
2809       if (array_key_exists($id, $groupNames) && $dontCare) {
2810         $names[] = $groupNames[$id];
2811       }
2812     }
2813
2814     $statii = array();
2815     $in = FALSE;
2816     $gcsValues = &$this->getWhereValues('group_contact_status', $grouping);
2817     if ($gcsValues &&
2818       is_array($gcsValues[2])
2819     ) {
2820       foreach ($gcsValues[2] as $k => $v) {
2821         if ($v) {
2822           if ($k == 'Added') {
2823             $in = TRUE;
2824           }
2825           $statii[] = "'" . CRM_Utils_Type::escape($k, 'String') . "'";
2826         }
2827       }
2828     }
2829     else {
2830       $statii[] = '"Added"';
2831       $in = TRUE;
2832     }
2833
2834     $skipGroup = FALSE;
2835     if (count($value) == 1 &&
2836       count($statii) == 1 &&
2837       $statii[0] == '"Added"'
2838     ) {
2839       // check if smart group, if so we can get rid of that one additional
2840       // left join
2841       $groupIDs = array_keys($value);
2842
2843       if (!empty($groupIDs[0]) &&
2844         CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Group',
2845           $groupIDs[0],
2846           'saved_search_id'
2847         )
2848       ) {
2849         $skipGroup = TRUE;
2850       }
2851     }
2852
2853     if (!$skipGroup) {
2854       $gcTable = "`civicrm_group_contact-{$groupIds}`";
2855       $this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON ( contact_a.id = {$gcTable}.contact_id AND {$gcTable}.group_id $op ( $groupIds ) )";
2856     }
2857
2858     $qill = ts('Contacts %1', array(1 => $op));
2859     $qill .= ' ' . implode(' ' . ts('or') . ' ', $names);
2860
2861     $groupClause = NULL;
2862
2863     if (!$skipGroup) {
2864       $groupClause = "{$gcTable}.group_id $op ( $groupIds )";
2865       if (!empty($statii)) {
2866         $groupClause .= " AND {$gcTable}.status IN (" . implode(', ', $statii) . ")";
2867         $qill .= " " . ts('AND') . " " . ts('Group Status') . ' - ' . implode(' ' . ts('or') . ' ', $statii);
2868       }
2869     }
2870
2871     if ($in) {
2872       $ssClause = $this->savedSearch($values);
2873       if ($ssClause) {
2874         if ($groupClause) {
2875           $groupClause = "( ( $groupClause ) OR ( $ssClause ) )";
2876         }
2877         else {
2878           $groupClause = $ssClause;
2879         }
2880       }
2881     }
2882
2883     $this->_where[$grouping][] = $groupClause;
2884     $this->_qill[$grouping][] = $qill;
2885   }
2886
2887   /**
2888    * Function translates selection of group type into a list of groups.
2889    * @param $value
2890    *
2891    * @return array
2892    */
2893   public function getGroupsFromTypeCriteria($value) {
2894     $groupIds = array();
2895     foreach ($value as $groupTypeValue) {
2896       $groupList = CRM_Core_PseudoConstant::group($groupTypeValue);
2897       $groupIds = ($groupIds + $groupList);
2898     }
2899     return $groupIds;
2900   }
2901
2902   /**
2903    * Where / qill clause for smart groups
2904    *
2905    * @param $values
2906    *
2907    * @return string|NULL
2908    */
2909   public function savedSearch(&$values) {
2910     list($name, $op, $value, $grouping, $wildcard) = $values;
2911     return $this->addGroupContactCache(array_keys($value));
2912   }
2913
2914   /**
2915    * @param array $groups
2916    * @param string $tableAlias
2917    * @param string $joinTable
2918    *
2919    * @return null|string
2920    */
2921   public function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a") {
2922     $config = CRM_Core_Config::singleton();
2923
2924     // Find all the groups that are part of a saved search.
2925     $groupIDs = implode(',', $groups);
2926     if (empty($groupIDs)) {
2927       return NULL;
2928     }
2929
2930     $sql = "
2931 SELECT id, cache_date, saved_search_id, children
2932 FROM   civicrm_group
2933 WHERE  id IN ( $groupIDs )
2934   AND  ( saved_search_id != 0
2935    OR    saved_search_id IS NOT NULL
2936    OR    children IS NOT NULL )
2937 ";
2938
2939     $group = CRM_Core_DAO::executeQuery($sql);
2940     $groupsFiltered = array();
2941
2942     while ($group->fetch()) {
2943       $groupsFiltered[] = $group->id;
2944
2945       $this->_useDistinct = TRUE;
2946
2947       if (!$this->_smartGroupCache || $group->cache_date == NULL) {
2948         CRM_Contact_BAO_GroupContactCache::load($group);
2949       }
2950     }
2951
2952     if (count($groupsFiltered)) {
2953       $groupIDsFiltered = implode(',', $groupsFiltered);
2954
2955       if ($tableAlias == NULL) {
2956         $tableAlias = "`civicrm_group_contact_cache_{$groupIDsFiltered}`";
2957       }
2958
2959       $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.id = {$tableAlias}.contact_id ";
2960
2961       return "{$tableAlias}.group_id IN (" . $groupIDsFiltered . ")";
2962     }
2963
2964     return NULL;
2965   }
2966
2967   /**
2968    * Where / qill clause for cms users
2969    *
2970    * @param $values
2971    *
2972    * @return void
2973    */
2974   public function ufUser(&$values) {
2975     list($name, $op, $value, $grouping, $wildcard) = $values;
2976
2977     if ($value == 1) {
2978       $this->_tables['civicrm_uf_match'] = $this->_whereTables['civicrm_uf_match'] = ' INNER JOIN civicrm_uf_match ON civicrm_uf_match.contact_id = contact_a.id ';
2979
2980       $this->_qill[$grouping][] = ts('CMS User');
2981     }
2982     elseif ($value == 0) {
2983       $this->_tables['civicrm_uf_match'] = $this->_whereTables['civicrm_uf_match'] = ' LEFT JOIN civicrm_uf_match ON civicrm_uf_match.contact_id = contact_a.id ';
2984
2985       $this->_where[$grouping][] = " civicrm_uf_match.contact_id IS NULL";
2986       $this->_qill[$grouping][] = ts('Not a CMS User');
2987     }
2988   }
2989
2990   /**
2991    * All tag search specific.
2992    *
2993    * @param $values
2994    *
2995    * @return void
2996    */
2997   public function tagSearch(&$values) {
2998     list($name, $op, $value, $grouping, $wildcard) = $values;
2999
3000     $op = "LIKE";
3001     $value = "%{$value}%";
3002
3003     $useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
3004     $tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
3005
3006     $etTable = "`civicrm_entity_tag-" . $value . "`";
3007     $tTable = "`civicrm_tag-" . $value . "`";
3008
3009     if ($useAllTagTypes[2]) {
3010       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3011         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id)
3012             LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id  )";
3013
3014       // search tag in cases
3015       $etCaseTable = "`civicrm_entity_case_tag-" . $value . "`";
3016       $tCaseTable = "`civicrm_case_tag-" . $value . "`";
3017       $this->_tables[$etCaseTable] = $this->_whereTables[$etCaseTable]
3018         = " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
3019             LEFT JOIN civicrm_case
3020             ON (civicrm_case_contact.case_id = civicrm_case.id
3021                 AND civicrm_case.is_deleted = 0 )
3022             LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id )
3023             LEFT JOIN civicrm_tag {$tCaseTable} ON ( {$etCaseTable}.tag_id = {$tCaseTable}.id  )";
3024       // search tag in activities
3025       $etActTable = "`civicrm_entity_act_tag-" . $value . "`";
3026       $tActTable = "`civicrm_act_tag-" . $value . "`";
3027       $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
3028       $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
3029
3030       $this->_tables[$etActTable] = $this->_whereTables[$etActTable]
3031         = " LEFT JOIN civicrm_activity_contact
3032             ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
3033             LEFT JOIN civicrm_activity
3034             ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
3035             AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )
3036             LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id )
3037             LEFT JOIN civicrm_tag {$tActTable} ON ( {$etActTable}.tag_id = {$tActTable}.id  )";
3038
3039       $this->_where[$grouping][] = "({$tTable}.name $op '" . $value . "' OR {$tCaseTable}.name $op '" . $value . "' OR {$tActTable}.name $op '" . $value . "')";
3040       $this->_qill[$grouping][] = ts('Tag %1 %2', array(1 => $tagTypesText[2], 2 => $op)) . ' ' . $value;
3041     }
3042     else {
3043       $etTable = "`civicrm_entity_tag-" . $value . "`";
3044       $tTable = "`civicrm_tag-" . $value . "`";
3045       $this->_tables[$etTable] = $this->_whereTables[$etTable] = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND
3046       {$etTable}.entity_table = 'civicrm_contact' )
3047                 LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id  ) ";
3048
3049       $this->_where[$grouping][] = self::buildClause("{$tTable}.name", $op, $value, 'String');
3050       $this->_qill[$grouping][] = ts('Tagged %1', array(1 => $op)) . ' ' . $value;
3051     }
3052   }
3053
3054   /**
3055    * Where / qill clause for tag
3056    *
3057    * @param $values
3058    *
3059    * @return void
3060    */
3061   public function tag(&$values) {
3062     list($name, $op, $value, $grouping, $wildcard) = $values;
3063
3064     $tagNames = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
3065     if (is_array($value)) {
3066       if (count($value) > 1) {
3067         $this->_useDistinct = TRUE;
3068       }
3069       foreach ($value as $id => $dontCare) {
3070         $names[] = CRM_Utils_Array::value($id, $tagNames);
3071       }
3072       $names = implode(' ' . ts('or') . ' ', $names);
3073       $value = implode(',', array_keys($value));
3074     }
3075     else {
3076       $names = CRM_Utils_Array::value($value, $tagNames);
3077     }
3078
3079     $useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
3080     $tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
3081
3082     $etTable = "`civicrm_entity_tag-" . $value . "`";
3083
3084     if ($useAllTagTypes[2]) {
3085       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3086         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND {$etTable}.entity_table = 'civicrm_contact') ";
3087
3088       // search tag in cases
3089       $etCaseTable = "`civicrm_entity_case_tag-" . $value . "`";
3090       $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
3091       $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
3092
3093       $this->_tables[$etCaseTable] = $this->_whereTables[$etCaseTable]
3094         = " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
3095             LEFT JOIN civicrm_case
3096             ON (civicrm_case_contact.case_id = civicrm_case.id
3097                 AND civicrm_case.is_deleted = 0 )
3098             LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id ) ";
3099       // search tag in activities
3100       $etActTable = "`civicrm_entity_act_tag-" . $value . "`";
3101       $this->_tables[$etActTable] = $this->_whereTables[$etActTable]
3102         = " LEFT JOIN civicrm_activity_contact
3103             ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
3104             LEFT JOIN civicrm_activity
3105             ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
3106             AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )
3107             LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id ) ";
3108
3109       // CRM-10338
3110       if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3111         $this->_where[$grouping][] = "({$etTable}.tag_id $op OR {$etCaseTable}.tag_id $op OR {$etActTable}.tag_id $op)";
3112       }
3113       else {
3114         $this->_where[$grouping][] = "({$etTable}.tag_id $op (" . $value . ") OR {$etCaseTable}.tag_id $op (" . $value . ") OR {$etActTable}.tag_id $op (" . $value . "))";
3115       }
3116       $this->_qill[$grouping][] = ts('Tag %1 %2', array(1 => $op, 2 => $tagTypesText[2])) . ' ' . $names;
3117     }
3118     else {
3119       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3120         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND {$etTable}.entity_table = 'civicrm_contact') ";
3121
3122       // CRM-10338
3123       if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3124         // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3125         $op = str_replace('EMPTY', 'NULL', $op);
3126         $this->_where[$grouping][] = "{$etTable}.tag_id $op";
3127       }
3128       else {
3129         $this->_where[$grouping][] = "{$etTable}.tag_id $op (" . $value . ')';
3130       }
3131       $this->_qill[$grouping][] = ts('Tagged %1', array(1 => $op)) . ' ' . $names;
3132     }
3133
3134   }
3135
3136   /**
3137    * Where/qill clause for notes
3138    *
3139    * @param $values
3140    *
3141    * @return void
3142    */
3143   public function notes(&$values) {
3144     list($name, $op, $value, $grouping, $wildcard) = $values;
3145
3146     $noteOptionValues = $this->getWhereValues('note_option', $grouping);
3147     $noteOption = CRM_Utils_Array::value('2', $noteOptionValues, '6');
3148     $noteOption = ($name == 'note_body') ? 2 : (($name == 'note_subject') ? 3 : $noteOption);
3149
3150     $this->_useDistinct = TRUE;
3151
3152     $this->_tables['civicrm_note'] = $this->_whereTables['civicrm_note']
3153       = " LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id ) ";
3154
3155     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
3156     $n = trim($value);
3157     $value = $strtolower(CRM_Core_DAO::escapeString($n));
3158     if ($wildcard || $op == 'LIKE') {
3159       if (strpos($value, '%') === FALSE) {
3160         $value = "%$value%";
3161       }
3162       $op = 'LIKE';
3163     }
3164     elseif ($op == 'IS NULL' || $op == 'IS NOT NULL') {
3165       $value = NULL;
3166     }
3167
3168     $label = NULL;
3169     $clauses = array();
3170     if ($noteOption % 2 == 0) {
3171       $clauses[] = self::buildClause('civicrm_note.note', $op, $value, 'String');
3172       $label = ts('Note: Body Only');
3173     }
3174     if ($noteOption % 3 == 0) {
3175       $clauses[] = self::buildClause('civicrm_note.subject', $op, $value, 'String');
3176       $label = $label ? ts('Note: Body and Subject') : ts('Note: Subject Only');
3177     }
3178     $this->_where[$grouping][] = "( " . implode(' OR ', $clauses) . " )";
3179     $this->_qill[$grouping][] = $label . " $op - '$n'";
3180   }
3181
3182   /**
3183    * @param string $name
3184    * @param $op
3185    * @param $grouping
3186    *
3187    * @return bool
3188    */
3189   public function nameNullOrEmptyOp($name, $op, $grouping) {
3190     switch ($op) {
3191       case 'IS NULL':
3192       case 'IS NOT NULL':
3193         $this->_where[$grouping][] = "contact_a.$name $op";
3194         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3195         return TRUE;
3196
3197       case 'IS EMPTY':
3198         $this->_where[$grouping][] = "(contact_a.$name IS NULL OR contact_a.$name = '')";
3199         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3200         return TRUE;
3201
3202       case 'IS NOT EMPTY':
3203         $this->_where[$grouping][] = "(contact_a.$name IS NOT NULL AND contact_a.$name <> '')";
3204         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3205         return TRUE;
3206
3207       default:
3208         return FALSE;
3209     }
3210   }
3211
3212   /**
3213    * Where / qill clause for sort_name
3214    *
3215    * @param $values
3216    *
3217    * @return void
3218    */
3219   public function sortName(&$values) {
3220     list($fieldName, $op, $value, $grouping, $wildcard) = $values;
3221
3222     // handle IS NULL / IS NOT NULL / IS EMPTY / IS NOT EMPTY
3223     if ($this->nameNullOrEmptyOp($fieldName, $op, $grouping)) {
3224       return;
3225     }
3226
3227     $input = $value = trim($value);
3228
3229     if (!strlen($value)) {
3230       return;
3231     }
3232
3233     $config = CRM_Core_Config::singleton();
3234
3235     $sub = array();
3236
3237     //By default, $sub elements should be joined together with OR statements (don't change this variable).
3238     $subGlue = ' OR ';
3239
3240     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
3241
3242     $firstChar = substr($value, 0, 1);
3243     $lastChar = substr($value, -1, 1);
3244     $quotes = array("'", '"');
3245     // If string is quoted, strip quotes and otherwise don't alter it
3246     if ((strlen($value) > 2) && in_array($firstChar, $quotes) && in_array($lastChar, $quotes)) {
3247       $value = trim($value, implode('', $quotes));
3248     }
3249     // Replace spaces with wildcards for a LIKE operation
3250     // UNLESS string contains a comma (this exception is a tiny bit questionable)
3251     elseif ($op == 'LIKE' && strpos($value, ',') === FALSE) {
3252       $value = str_replace(' ', '%', $value);
3253     }
3254     $value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));
3255     if (strlen($value)) {
3256       $fieldsub = array();
3257       if ($wildcard && $op == 'LIKE') {
3258         if ($config->includeWildCardInName) {
3259           $value = "'%$value%'";
3260         }
3261         else {
3262           $value = "'$value%'";
3263         }
3264         $op = 'LIKE';
3265       }
3266       else {
3267         $value = "'$value'";
3268       }
3269       if ($fieldName == 'sort_name') {
3270         $wc = self::caseImportant($op) ? "LOWER(contact_a.sort_name)" : "contact_a.sort_name";
3271       }
3272       else {
3273         $wc = self::caseImportant($op) ? "LOWER(contact_a.display_name)" : "contact_a.display_name";
3274       }
3275       $fieldsub[] = " ( $wc $op $value )";
3276       if ($config->includeNickNameInName) {
3277         $wc = self::caseImportant($op) ? "LOWER(contact_a.nick_name)" : "contact_a.nick_name";
3278         $fieldsub[] = " ( $wc $op $value )";
3279       }
3280       if ($config->includeEmailInName) {
3281         $fieldsub[] = " ( civicrm_email.email $op $value ) ";
3282       }
3283       $sub[] = ' ( ' . implode(' OR ', $fieldsub) . ' ) ';
3284     }
3285
3286     $sub = ' ( ' . implode($subGlue, $sub) . ' ) ';
3287
3288     $this->_where[$grouping][] = $sub;
3289     if ($config->includeEmailInName) {
3290       $this->_tables['civicrm_email'] = $this->_whereTables['civicrm_email'] = 1;
3291       $this->_qill[$grouping][] = ts('Name or Email') . " $op - '$input'";
3292     }
3293     else {
3294       $this->_qill[$grouping][] = ts('Name') . " $op - '$input'";
3295     }
3296   }
3297
3298   /**
3299    * Where/qill clause for greeting fields.
3300    *
3301    * @param array $values
3302    */
3303   public function greetings(&$values) {
3304     list($name, $op, $value, $grouping, $wildcard) = $values;
3305     $name .= '_display';
3306
3307     $this->_qill[$grouping][] = ts('Greeting %1 %2', array(1 => $op, 2 => $value));
3308     $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", 'LIKE', "$value", 'String');
3309   }
3310
3311   /**
3312    * Where / qill clause for email
3313    *
3314    * @param $values
3315    *
3316    * @return void
3317    */
3318   public function email(&$values) {
3319     list($name, $op, $value, $grouping, $wildcard) = $values;
3320
3321     $n = trim($value);
3322     if ($n) {
3323       $config = CRM_Core_Config::singleton();
3324
3325       if (substr($n, 0, 1) == '"' &&
3326         substr($n, -1, 1) == '"'
3327       ) {
3328         $n = substr($n, 1, -1);
3329         $value = strtolower(CRM_Core_DAO::escapeString($n));
3330         $value = "'$value'";
3331         $op = '=';
3332       }
3333       else {
3334         $value = strtolower($n);
3335         if ($wildcard) {
3336           if (strpos($value, '%') === FALSE) {
3337             $value = "%{$value}%";
3338           }
3339           $op = 'LIKE';
3340         }
3341       }
3342       $this->_qill[$grouping][] = ts('Email') . " $op '$n'";
3343       $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, $value, 'String');
3344     }
3345     else {
3346       $this->_qill[$grouping][] = ts('Email') . " $op ";
3347       $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, NULL, 'String');
3348     }
3349
3350     $this->_tables['civicrm_email'] = $this->_whereTables['civicrm_email'] = 1;
3351   }
3352
3353   /**
3354    * Where / qill clause for phone number
3355    *
3356    * @param $values
3357    *
3358    * @return void
3359    */
3360   public function phone_numeric(&$values) {
3361     list($name, $op, $value, $grouping, $wildcard) = $values;
3362     // Strip non-numeric characters; allow wildcards
3363     $number = preg_replace('/[^\d%]/', '', $value);
3364     if ($number) {
3365       if (strpos($number, '%') === FALSE) {
3366         $number = "%$number%";
3367       }
3368
3369       $this->_qill[$grouping][] = ts('Phone number contains') . " $number";
3370       $this->_where[$grouping][] = self::buildClause('civicrm_phone.phone_numeric', 'LIKE', "$number", 'String');
3371       $this->_tables['civicrm_phone'] = $this->_whereTables['civicrm_phone'] = 1;
3372     }
3373   }
3374
3375   /**
3376    * Where / qill clause for phone type/location
3377    *
3378    * @param $values
3379    *
3380    * @return void
3381    */
3382   public function phone_option_group($values) {
3383     list($name, $op, $value, $grouping, $wildcard) = $values;
3384     $option = ($name == 'phone_phone_type_id' ? 'phone_type_id' : 'location_type_id');
3385     $options = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', $option);
3386     $optionName = $options[$value];
3387     $this->_qill[$grouping][] = ts('Phone') . ' ' . ($name == 'phone_phone_type_id' ? ts('type') : ('location')) . " $op $optionName";
3388     $this->_where[$grouping][] = self::buildClause('civicrm_phone.' . substr($name, 6), $op, $value, 'Integer');
3389     $this->_tables['civicrm_phone'] = $this->_whereTables['civicrm_phone'] = 1;
3390   }
3391
3392   /**
3393    * Where / qill clause for street_address
3394    *
3395    * @param $values
3396    *
3397    * @return void
3398    */
3399   public function street_address(&$values) {
3400     list($name, $op, $value, $grouping, $wildcard) = $values;
3401
3402     if (!$op) {
3403       $op = 'LIKE';
3404     }
3405
3406     $n = trim($value);
3407
3408     if ($n) {
3409       $value = strtolower($n);
3410       if (strpos($value, '%') === FALSE) {
3411         // only add wild card if not there
3412         $value = "%{$value}%";
3413       }
3414       $op = 'LIKE';
3415       $this->_where[$grouping][] = self::buildClause('LOWER(civicrm_address.street_address)', $op, $value, 'String');
3416       $this->_qill[$grouping][] = ts('Street') . " $op '$n'";
3417     }
3418     else {
3419       $this->_where[$grouping][] = self::buildClause('civicrm_address.street_address', $op, NULL, 'String');
3420       $this->_qill[$grouping][] = ts('Street') . " $op ";
3421     }
3422
3423     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3424   }
3425
3426   /**
3427    * Where / qill clause for street_unit
3428    *
3429    * @param $values
3430    *
3431    * @return void
3432    */
3433   public function street_number(&$values) {
3434     list($name, $op, $value, $grouping, $wildcard) = $values;
3435
3436     if (!$op) {
3437       $op = '=';
3438     }
3439
3440     $n = trim($value);
3441
3442     if (strtolower($n) == 'odd') {
3443       $this->_where[$grouping][] = " ( civicrm_address.street_number % 2 = 1 )";
3444       $this->_qill[$grouping][] = ts('Street Number is odd');
3445     }
3446     elseif (strtolower($n) == 'even') {
3447       $this->_where[$grouping][] = " ( civicrm_address.street_number % 2 = 0 )";
3448       $this->_qill[$grouping][] = ts('Street Number is even');
3449     }
3450     else {
3451       $value = strtolower($n);
3452
3453       $this->_where[$grouping][] = self::buildClause('LOWER(civicrm_address.street_number)', $op, $value, 'String');
3454       $this->_qill[$grouping][] = ts('Street Number') . " $op '$n'";
3455     }
3456
3457     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3458   }
3459
3460   /**
3461    * Where / qill clause for sorting by character
3462    *
3463    * @param $values
3464    *
3465    * @return void
3466    */
3467   public function sortByCharacter(&$values) {
3468     list($name, $op, $value, $grouping, $wildcard) = $values;
3469
3470     $name = trim($value);
3471     $cond = " contact_a.sort_name LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($name)) . "%'";
3472     $this->_where[$grouping][] = $cond;
3473     $this->_qill[$grouping][] = ts('Showing only Contacts starting with: \'%1\'', array(1 => $name));
3474   }
3475
3476   /**
3477    * Where / qill clause for including contact ids
3478    *
3479    * @return void
3480    */
3481   public function includeContactIDs() {
3482     if (!$this->_includeContactIds || empty($this->_params)) {
3483       return;
3484     }
3485
3486     $contactIds = array();
3487     foreach ($this->_params as $id => $values) {
3488       if (substr($values[0], 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) {
3489         $contactIds[] = substr($values[0], CRM_Core_Form::CB_PREFIX_LEN);
3490       }
3491     }
3492     if (!empty($contactIds)) {
3493       $this->_where[0][] = " ( contact_a.id IN (" . implode(',', $contactIds) . " ) ) ";
3494     }
3495   }
3496
3497   /**
3498    * Where / qill clause for postal code
3499    *
3500    * @param $values
3501    *
3502    * @return void
3503    */
3504   public function postalCode(&$values) {
3505     // skip if the fields dont have anything to do with postal_code
3506     if (empty($this->_fields['postal_code'])) {
3507       return;
3508     }
3509
3510     list($name, $op, $value, $grouping, $wildcard) = $values;
3511
3512     // Handle numeric postal code range searches properly by casting the column as numeric
3513     if (is_numeric($value)) {
3514       $field = 'ROUND(civicrm_address.postal_code)';
3515       $val = CRM_Utils_Type::escape($value, 'Integer');
3516     }
3517     else {
3518       $field = 'civicrm_address.postal_code';
3519       $val = CRM_Utils_Type::escape($value, 'String');
3520     }
3521
3522     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3523
3524     if ($name == 'postal_code') {
3525       $this->_where[$grouping][] = self::buildClause($field, $op, $val, 'String');
3526       $this->_qill[$grouping][] = ts('Postal code') . " {$op} {$value}";
3527     }
3528     elseif ($name == 'postal_code_low') {
3529       $this->_where[$grouping][] = " ( $field >= '$val' ) ";
3530       $this->_qill[$grouping][] = ts('Postal code greater than or equal to \'%1\'', array(1 => $value));
3531     }
3532     elseif ($name == 'postal_code_high') {
3533       $this->_where[$grouping][] = " ( $field <= '$val' ) ";
3534       $this->_qill[$grouping][] = ts('Postal code less than or equal to \'%1\'', array(1 => $value));
3535     }
3536   }
3537
3538   /**
3539    * Where / qill clause for location type
3540    *
3541    * @param $values
3542    * @param null $status
3543    *
3544    * @return void
3545    */
3546   public function locationType(&$values, $status = NULL) {
3547     list($name, $op, $value, $grouping, $wildcard) = $values;
3548
3549     if (is_array($value)) {
3550       $this->_where[$grouping][] = 'civicrm_address.location_type_id IN (' . implode(',', $value) . ')';
3551       $this->_tables['civicrm_address'] = 1;
3552       $this->_whereTables['civicrm_address'] = 1;
3553
3554       $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
3555       $names = array();
3556       foreach ($value as $id) {
3557         $names[] = $locationType[$id];
3558       }
3559
3560       $this->_primaryLocation = FALSE;
3561
3562       if (!$status) {
3563         $this->_qill[$grouping][] = ts('Location Type') . ' - ' . implode(' ' . ts('or') . ' ', $names);
3564       }
3565       else {
3566         return implode(' ' . ts('or') . ' ', $names);
3567       }
3568     }
3569   }
3570
3571   /**
3572    * @param $values
3573    * @param bool $fromStateProvince
3574    *
3575    * @return array|NULL
3576    */
3577   public function country(&$values, $fromStateProvince = TRUE) {
3578     list($name, $op, $value, $grouping, $wildcard) = $values;
3579
3580     if (!$fromStateProvince) {
3581       $stateValues = $this->getWhereValues('state_province', $grouping);
3582       if (!empty($stateValues)) {
3583         // return back to caller if there are state province values
3584         // since that handles this case
3585         return NULL;
3586       }
3587     }
3588
3589     $countryClause = $countryQill = NULL;
3590     if (
3591       $values &&
3592       !empty($value)
3593     ) {
3594
3595       $this->_tables['civicrm_address'] = 1;
3596       $this->_whereTables['civicrm_address'] = 1;
3597
3598       $countries = CRM_Core_PseudoConstant::country();
3599       if (is_numeric($value)) {
3600         $countryClause = self::buildClause(
3601           'civicrm_address.country_id',
3602           $op,
3603           $value,
3604           'Positive'
3605         );
3606         $countryName = $countries[(int ) $value];
3607       }
3608
3609       else {
3610         $intValues = self::parseSearchBuilderString($value);
3611         if ($intValues && ($op == 'IN' || $op == 'NOT IN')) {
3612           $countryClause = self::buildClause(
3613             'civicrm_address.country_id',
3614             $op,
3615             $intValues,
3616             'Positive'
3617           );
3618           $countryNames = array();
3619           foreach ($intValues as $v) {
3620             $countryNames[] = $countries[$v];
3621           }
3622           $countryName = implode(',', $countryNames);
3623         }
3624         else {
3625           $countries = CRM_Core_PseudoConstant::country();
3626           $intVal = CRM_Utils_Array::key($value, $countries);
3627           $countryClause = self::buildClause(
3628             'civicrm_address.country_id',
3629             $op,
3630             $intVal,
3631             'Integer'
3632           );
3633           $countryName = $value;
3634         }
3635       }
3636       $countryQill = ts('Country') . " {$op} '$countryName'";
3637
3638       if (!$fromStateProvince) {
3639         $this->_where[$grouping][] = $countryClause;
3640         $this->_qill[$grouping][] = $countryQill;
3641       }
3642     }
3643
3644     if ($fromStateProvince) {
3645       if (!empty($countryClause)) {
3646         return array(
3647           $countryClause,
3648           " ...AND... " . $countryQill,
3649         );
3650       }
3651       else {
3652         return array(NULL, NULL);
3653       }
3654     }
3655   }
3656
3657   /**
3658    * Where / qill clause for county (if present)
3659    *
3660    * @param $values
3661    * @param null $status
3662    *
3663    * @return void
3664    */
3665   public function county(&$values, $status = NULL) {
3666     list($name, $op, $value, $grouping, $wildcard) = $values;
3667
3668     if (!is_array($value)) {
3669       // force the county to be an array
3670       $value = array($value);
3671     }
3672
3673     // check if the values are ids OR names of the counties
3674     $inputFormat = 'id';
3675     foreach ($value as $v) {
3676       if (!is_numeric($v)) {
3677         $inputFormat = 'name';
3678         break;
3679       }
3680     }
3681     $names = array();
3682     if ($op == '=') {
3683       $op = 'IN';
3684     }
3685     elseif ($op == '!=') {
3686       $op = 'NOT IN';
3687     }
3688     else {
3689       // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3690       $op = str_replace('EMPTY', 'NULL', $op);
3691     }
3692
3693     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3694       $clause = "civicrm_address.county_id $op";
3695     }
3696     elseif ($inputFormat == 'id') {
3697       $clause = 'civicrm_address.county_id IN (' . implode(',', $value) . ')';
3698
3699       $county = CRM_Core_PseudoConstant::county();
3700       foreach ($value as $id) {
3701         $names[] = CRM_Utils_Array::value($id, $county);
3702       }
3703     }
3704     else {
3705       $inputClause = array();
3706       $county = CRM_Core_PseudoConstant::county();
3707       foreach ($value as $name) {
3708         $name = trim($name);
3709         $inputClause[] = CRM_Utils_Array::key($name, $county);
3710       }
3711       $clause = 'civicrm_address.county_id IN (' . implode(',', $inputClause) . ')';
3712       $names = $value;
3713     }
3714     $this->_tables['civicrm_address'] = 1;
3715     $this->_whereTables['civicrm_address'] = 1;
3716
3717     $this->_where[$grouping][] = $clause;
3718     if (!$status) {
3719       $this->_qill[$grouping][] = ts('County') . ' - ' . implode(' ' . ts('or') . ' ', $names);
3720     }
3721     else {
3722       return implode(' ' . ts('or') . ' ', $names);
3723     }
3724   }
3725
3726   /**
3727    * Where / qill clause for state/province AND country (if present)
3728    *
3729    * @param $values
3730    * @param null $status
3731    *
3732    * @return void
3733    */
3734   public function stateProvince(&$values, $status = NULL) {
3735     list($name, $op, $value, $grouping, $wildcard) = $values;
3736
3737     // quick escape for IS NULL
3738     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3739       $value = NULL;
3740     }
3741     elseif (!is_array($value)) {
3742       // force the state to be an array
3743       // check if its in the mapper format!
3744       $values = self::parseSearchBuilderString($value);
3745       if (is_array($values)) {
3746         $value = $values;
3747       }
3748       else {
3749         $value = array($value);
3750       }
3751     }
3752
3753     // check if the values are ids OR names of the states
3754     $inputFormat = 'id';
3755     if ($value) {
3756       foreach ($value as $v) {
3757         if (!is_numeric($v)) {
3758           $inputFormat = 'name';
3759           break;
3760         }
3761       }
3762     }
3763
3764     $names = array();
3765     if ($op == '=') {
3766       $op = 'IN';
3767     }
3768     elseif ($op == '!=') {
3769       $op = 'NOT IN';
3770     }
3771     else {
3772       // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3773       $op = str_replace('EMPTY', 'NULL', $op);
3774     }
3775     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3776       $stateClause = "civicrm_address.state_province_id $op";
3777     }
3778     elseif ($inputFormat == 'id') {
3779       if ($op != 'NOT IN') {
3780         $op = 'IN';
3781       }
3782       $stateClause = "civicrm_address.state_province_id $op (" . implode(',', $value) . ')';
3783
3784       foreach ($value as $id) {
3785         $names[] = CRM_Core_PseudoConstant::stateProvince($id, FALSE);
3786       }
3787     }
3788     else {
3789       $inputClause = array();
3790       $stateProvince = CRM_Core_PseudoConstant::stateProvince();
3791       foreach ($value as $name) {
3792         $name = trim($name);
3793         $inputClause[] = CRM_Utils_Array::key($name, $stateProvince);
3794       }
3795       $stateClause = "civicrm_address.state_province_id $op (" . implode(',', $inputClause) . ')';
3796       $names = $value;
3797     }
3798     $this->_tables['civicrm_address'] = 1;
3799     $this->_whereTables['civicrm_address'] = 1;
3800
3801     $countryValues = $this->getWhereValues('country', $grouping);
3802     list($countryClause, $countryQill) = $this->country($countryValues, TRUE);
3803
3804     if ($countryClause) {
3805       $clause = "( $stateClause AND $countryClause )";
3806     }
3807     else {
3808       $clause = $stateClause;
3809     }
3810
3811     $this->_where[$grouping][] = $clause;
3812     if (!$status) {
3813       $this->_qill[$grouping][] = ts('State/Province') . " $op " . implode(' ' . ts('or') . ' ', $names) . $countryQill;
3814     }
3815     else {
3816       return implode(' ' . ts('or') . ' ', $names) . $countryQill;
3817     }
3818   }
3819
3820   /**
3821    * Where / qill clause for change log
3822    *
3823    * @param $values
3824    *
3825    * @return void
3826    */
3827   public function changeLog(&$values) {
3828     list($name, $op, $value, $grouping, $wildcard) = $values;
3829
3830     $targetName = $this->getWhereValues('changed_by', $grouping);
3831     if (!$targetName) {
3832       return;
3833     }
3834
3835     $name = trim($targetName[2]);
3836     $name = strtolower(CRM_Core_DAO::escapeString($name));
3837     $name = $targetName[4] ? "%$name%" : $name;
3838     $this->_where[$grouping][] = "contact_b_log.sort_name LIKE '%$name%'";
3839     $this->_tables['civicrm_log'] = $this->_whereTables['civicrm_log'] = 1;
3840     $this->_qill[$grouping][] = ts('Modified By') . " $name";
3841   }
3842
3843   /**
3844    * @param $values
3845    */
3846   public function modifiedDates($values) {
3847     $this->_useDistinct = TRUE;
3848
3849     // CRM-11281, default to added date if not set
3850     $fieldTitle = ts('Added Date');
3851     $fieldName = 'created_date';
3852     foreach (array_keys($this->_params) as $id) {
3853       if ($this->_params[$id][0] == 'log_date') {
3854         if ($this->_params[$id][2] == 2) {
3855           $fieldTitle = ts('Modified Date');
3856           $fieldName = 'modified_date';
3857         }
3858       }
3859     }
3860
3861     $this->dateQueryBuilder($values, 'contact_a', 'log_date', $fieldName, $fieldTitle);
3862
3863     self::$_openedPanes[ts('Change Log')] = TRUE;
3864   }
3865
3866   /**
3867    * @param $values
3868    */
3869   public function demographics(&$values) {
3870     list($name, $op, $value, $grouping, $wildcard) = $values;
3871
3872     if (($name == 'birth_date_low') || ($name == 'birth_date_high')) {
3873
3874       $this->dateQueryBuilder($values,
3875         'contact_a', 'birth_date', 'birth_date', ts('Birth Date')
3876       );
3877     }
3878     elseif (($name == 'deceased_date_low') || ($name == 'deceased_date_high')) {
3879
3880       $this->dateQueryBuilder($values,
3881         'contact_a', 'deceased_date', 'deceased_date', ts('Deceased Date')
3882       );
3883     }
3884
3885     self::$_openedPanes[ts('Demographics')] = TRUE;
3886   }
3887
3888   /**
3889    * @param $values
3890    */
3891   public function privacy(&$values) {
3892     list($name, $op, $value, $grouping, $wildcard) = $values;
3893     //fixed for profile search listing CRM-4633
3894     if (strpbrk($value, "[")) {
3895       $value = "'{$value}'";
3896       $op = "!{$op}";
3897       $this->_where[$grouping][] = "contact_a.{$name} $op $value";
3898     }
3899     else {
3900       $this->_where[$grouping][] = "contact_a.{$name} $op $value";
3901     }
3902     $field = CRM_Utils_Array::value($name, $this->_fields);
3903     $title = $field ? $field['title'] : $name;
3904     $this->_qill[$grouping][] = "$title $op $value";
3905   }
3906
3907   /**
3908    * @param $values
3909    */
3910   public function privacyOptions($values) {
3911     list($name, $op, $value, $grouping, $wildcard) = $values;
3912
3913     if (empty($value) || !is_array($value)) {
3914       return;
3915     }
3916
3917     // get the operator and toggle values
3918     $opValues = $this->getWhereValues('privacy_operator', $grouping);
3919     $operator = 'OR';
3920     if ($opValues &&
3921       strtolower($opValues[2] == 'AND')
3922     ) {
3923       $operator = 'AND';
3924     }
3925
3926     $toggleValues = $this->getWhereValues('privacy_toggle', $grouping);
3927     $compareOP = '!';
3928     if ($toggleValues &&
3929       $toggleValues[2] == 2
3930     ) {
3931       $compareOP = '';
3932     }
3933
3934     $clauses = array();
3935     $qill = array();
3936     foreach ($value as $dontCare => $pOption) {
3937       $clauses[] = " ( contact_a.{$pOption} = 1 ) ";
3938       $field = CRM_Utils_Array::value($pOption, $this->_fields);
3939       $title = $field ? $field['title'] : $pOption;
3940       $qill[] = " $title = 1 ";
3941     }
3942
3943     $this->_where[$grouping][] = $compareOP . '( ' . implode($operator, $clauses) . ' )';
3944     $this->_qill[$grouping][] = $compareOP . '( ' . implode($operator, $qill) . ' )';
3945   }
3946
3947   /**
3948    * @param $values
3949    */
3950   public function preferredCommunication(&$values) {
3951     list($name, $op, $value, $grouping, $wildcard) = $values;
3952
3953     $pref = array();
3954     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3955       $value = NULL;
3956     }
3957     elseif (!is_array($value)) {
3958       $v = array();
3959       $value = trim($value, ' ()');
3960       if (strpos($value, CRM_Core_DAO::VALUE_SEPARATOR) !== FALSE) {
3961         $v = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
3962       }
3963       else {
3964         $v = explode(",", $value);
3965       }
3966
3967       foreach ($v as $item) {
3968         if ($item) {
3969           $pref[] = $item;
3970         }
3971       }
3972     }
3973     else {
3974       foreach ($value as $key => $checked) {
3975         if ($checked) {
3976           $pref[] = $key;
3977         }
3978       }
3979     }
3980
3981     $commPref = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'preferred_communication_method');
3982
3983     $sqlValue = array();
3984     $showValue = array();
3985     $sql = "contact_a.preferred_communication_method";
3986     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3987       $sqlValue[] = "{$sql} {$op}";
3988     }
3989     else {
3990       foreach ($pref as $val) {
3991         $sqlValue[] = "( $sql like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $val . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
3992         $showValue[] = $commPref[$val];
3993       }
3994     }
3995     $this->_where[$grouping][] = "( " . implode(' OR ', $sqlValue) . " )";
3996     $this->_qill[$grouping][] = ts('Preferred Communication Method') . " $op " . implode(' ' . ts('or') . ' ', $showValue);
3997   }
3998
3999   /**
4000    * Where / qill clause for relationship
4001    *
4002    * @param $values
4003    *
4004    * @return void
4005    */
4006   public function relationship(&$values) {
4007     list($name, $op, $value, $grouping, $wildcard) = $values;
4008     if ($this->_relationshipValuesAdded) {
4009       return;
4010     }
4011     // also get values array for relation_target_name
4012     // for relationship search we always do wildcard
4013     $relationType = $this->getWhereValues('relation_type_id', $grouping);
4014     $targetName = $this->getWhereValues('relation_target_name', $grouping);
4015     $relStatus = $this->getWhereValues('relation_status', $grouping);
4016     $relPermission = $this->getWhereValues('relation_permission', $grouping);
4017     $targetGroup = $this->getWhereValues('relation_target_group', $grouping);
4018
4019     $nameClause = $name = NULL;
4020     if ($targetName) {
4021       $name = trim($targetName[2]);
4022       if (substr($name, 0, 1) == '"' &&
4023         substr($name, -1, 1) == '"'
4024       ) {
4025         $name = substr($name, 1, -1);
4026         $name = strtolower(CRM_Core_DAO::escapeString($name));
4027         $nameClause = "= '$name'";
4028       }
4029       else {
4030         $name = strtolower(CRM_Core_DAO::escapeString($name));
4031         $nameClause = "LIKE '%{$name}%'";
4032       }
4033     }
4034
4035     $rTypeValues = array();
4036     if (!empty($relationType)) {
4037       $rel = explode('_', $relationType[2]);
4038       self::$_relType = $rel[1];
4039       $params = array('id' => $rel[0]);
4040       $rType = CRM_Contact_BAO_RelationshipType::retrieve($params, $rTypeValues);
4041     }
4042     if (!empty($rTypeValues) && $rTypeValues['name_a_b'] == $rTypeValues['name_b_a']) {
4043       // if we don't know which end of the relationship we are dealing with we'll create a temp table
4044       //@todo unless we are dealing with a target group
4045       self::$_relType = 'reciprocal';
4046     }
4047     // if we are creating a temp table we build our own where for the relationship table
4048     $relationshipTempTable = NULL;
4049     if (self::$_relType == 'reciprocal' && empty($targetGroup)) {
4050       $where = array();
4051       self::$_relationshipTempTable = $relationshipTempTable = CRM_Core_DAO::createTempTableName('civicrm_rel');
4052       if ($nameClause) {
4053         $where[$grouping][] = " sort_name $nameClause ";
4054       }
4055     }
4056     else {
4057       $where = &$this->_where;
4058       if ($nameClause) {
4059         $where[$grouping][] = "( contact_b.sort_name $nameClause AND contact_b.id != contact_a.id )";
4060       }
4061     }
4062
4063     $relTypeInd = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Individual');
4064     $relTypeOrg = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Organization');
4065     $relTypeHou = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Household');
4066     $allRelationshipType = array();
4067     $allRelationshipType = array_merge($relTypeInd, $relTypeOrg);
4068     $allRelationshipType = array_merge($allRelationshipType, $relTypeHou);
4069
4070     if ($nameClause || !$targetGroup) {
4071       if (!empty($relationType)) {
4072         $this->_qill[$grouping][] = $allRelationshipType[$relationType[2]] . " $name";
4073       }
4074       else {
4075         $this->_qill[$grouping][] = $name;
4076       }
4077     }
4078
4079     //check to see if the target contact is in specified group
4080     if ($targetGroup) {
4081       //add contacts from static groups
4082       $this->_tables['civicrm_relationship_group_contact'] = $this->_whereTables['civicrm_relationship_group_contact']
4083         = " LEFT JOIN civicrm_group_contact civicrm_relationship_group_contact ON civicrm_relationship_group_contact.contact_id = contact_b.id AND civicrm_relationship_group_contact.status = 'Added'";
4084       $groupWhere[] = "( civicrm_relationship_group_contact.group_id IN  (" .
4085         implode(",", $targetGroup[2]) . ") ) ";
4086
4087       //add contacts from saved searches
4088       $ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", "contact_b");
4089
4090       //set the group where clause
4091       if ($ssWhere) {
4092         $groupWhere[] = "( " . $ssWhere . " )";
4093       }
4094       $this->_where[$grouping][] = "( " . implode(" OR ", $groupWhere) . " )";
4095
4096       //Get the names of the target groups for the qill
4097       $groupNames = CRM_Core_PseudoConstant::group();
4098       $qillNames = array();
4099       foreach ($targetGroup[2] as $groupId) {
4100         if (array_key_exists($groupId, $groupNames)) {
4101           $qillNames[] = $groupNames[$groupId];
4102         }
4103       }
4104       if (!empty($relationType)) {
4105         $this->_qill[$grouping][] = $allRelationshipType[$relationType[2]] . " ( " . implode(", ", $qillNames) . " )";
4106       }
4107       else {
4108         $this->_qill[$grouping][] = implode(", ", $qillNames);
4109       }
4110     }
4111
4112     // Note we do not currently set mySql to handle timezones, so doing this the old-fashioned way
4113     $today = date('Ymd');
4114     //check for active, inactive and all relation status
4115     if ($relStatus[2] == 0) {
4116       $where[$grouping][] = "(
4117 civicrm_relationship.is_active = 1 AND
4118 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
4119 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
4120 )";
4121       $this->_qill[$grouping][] = ts('Relationship - Active and Current');
4122     }
4123     elseif ($relStatus[2] == 1) {
4124       $where[$grouping][] = "(
4125 civicrm_relationship.is_active = 0 OR
4126 civicrm_relationship.end_date < {$today} OR
4127 civicrm_relationship.start_date > {$today}
4128 )";
4129       $this->_qill[$grouping][] = ts('Relationship - Inactive or not Current');
4130     }
4131
4132     $onlyDeleted = 0;
4133     if (in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params)) {
4134       $onlyDeleted = 1;
4135     }
4136     $where[$grouping][] = "(contact_b.is_deleted = {$onlyDeleted})";
4137
4138     //check for permissioned, non-permissioned and all permissioned relations
4139     if ($relPermission[2] == 1) {
4140       $where[$grouping][] = "(
4141 civicrm_relationship.is_permission_a_b = 1
4142 )";
4143       $this->_qill[$grouping][] = ts('Relationship - Permissioned');
4144     }
4145     elseif ($relPermission[2] == 2) {
4146       //non-allowed permission relationship.
4147       $where[$grouping][] = "(
4148 civicrm_relationship.is_permission_a_b = 0
4149 )";
4150       $this->_qill[$grouping][] = ts('Relationship - Non-permissioned');
4151     }
4152
4153     $this->addRelationshipDateClauses($grouping, $where);
4154     if (!empty($relationType) && !empty($rType) && isset($rType->id)) {
4155       $where[$grouping][] = 'civicrm_relationship.relationship_type_id = ' . $rType->id;
4156     }
4157     $this->_tables['civicrm_relationship'] = $this->_whereTables['civicrm_relationship'] = 1;
4158     $this->_useDistinct = TRUE;
4159     $this->_relationshipValuesAdded = TRUE;
4160     // it could be a or b, using an OR creates an unindexed join - better to create a temp table &
4161     // join on that,
4162     // @todo creating a temp table could be expanded to group filter
4163     // as even creating a temp table of all relationships is much much more efficient than
4164     // an OR in the join
4165     if ($relationshipTempTable) {
4166       $whereClause = '';
4167       if (!empty($where[$grouping])) {
4168         $whereClause = ' WHERE ' . implode(' AND ', $where[$grouping]);
4169       }
4170       $sql = "
4171         CREATE TEMPORARY TABLE {$relationshipTempTable}
4172           (SELECT contact_id_b as contact_id, civicrm_relationship.id
4173             FROM civicrm_relationship
4174             INNER JOIN  civicrm_contact c ON civicrm_relationship.contact_id_a = c.id
4175             $whereClause )
4176           UNION
4177             (SELECT contact_id_a as contact_id, civicrm_relationship.id
4178             FROM civicrm_relationship
4179             INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_b = c.id
4180             $whereClause )
4181       ";
4182       CRM_Core_DAO::executeQuery($sql);
4183     }
4184
4185   }
4186
4187   /**
4188    * Add start & end date criteria in
4189    * @param string $grouping
4190    * @param array $where
4191    *   = array to add where clauses to, in case you are generating a temp table.
4192    * not the main query.
4193    */
4194   public function addRelationshipDateClauses($grouping, &$where) {
4195     $dateValues = array();
4196     $dateTypes = array(
4197       'start_date',
4198       'end_date',
4199     );
4200
4201     foreach ($dateTypes as $dateField) {
4202       $dateValueLow = $this->getWhereValues('relation_' . $dateField . '_low', $grouping);
4203       $dateValueHigh = $this->getWhereValues('relation_' . $dateField . '_high', $grouping);
4204       if (!empty($dateValueLow)) {
4205         $date = date('Ymd', strtotime($dateValueLow[2]));
4206         $where[$grouping][] = "civicrm_relationship.$dateField >= $date";
4207         $this->_qill[$grouping][] = ($dateField == 'end_date' ? ts('Relationship Ended on or After') : ts('Relationship Recorded Start Date On or Before')) . " " . CRM_Utils_Date::customFormat($date);
4208       }
4209       if (!empty($dateValueHigh)) {
4210         $date = date('Ymd', strtotime($dateValueHigh[2]));
4211         $where[$grouping][] = "civicrm_relationship.$dateField <= $date";
4212         $this->_qill[$grouping][] = ($dateField == 'end_date' ? ts('Relationship Ended on or Before') : ts('Relationship Recorded Start Date On or After')) . " " . CRM_Utils_Date::customFormat($date);
4213       }
4214     }
4215   }
4216
4217   /**
4218    * Default set of return properties.
4219    *
4220    * @param int $mode
4221    *
4222    * @return array
4223    *   derault return properties
4224    */
4225   public static function &defaultReturnProperties($mode = 1) {
4226     if (!isset(self::$_defaultReturnProperties)) {
4227       self::$_defaultReturnProperties = array();
4228     }
4229
4230     if (!isset(self::$_defaultReturnProperties[$mode])) {
4231       // add activity return properties
4232       if ($mode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
4233         self::$_defaultReturnProperties[$mode] = CRM_Activity_BAO_Query::defaultReturnProperties($mode, FALSE);
4234       }
4235       else {
4236         self::$_defaultReturnProperties[$mode] = CRM_Core_Component::defaultReturnProperties($mode, FALSE);
4237       }
4238
4239       if (empty(self::$_defaultReturnProperties[$mode])) {
4240         self::$_defaultReturnProperties[$mode] = array(
4241           'home_URL' => 1,
4242           'image_URL' => 1,
4243           'legal_identifier' => 1,
4244           'external_identifier' => 1,
4245           'contact_type' => 1,
4246           'contact_sub_type' => 1,
4247           'sort_name' => 1,
4248           'display_name' => 1,
4249           'preferred_mail_format' => 1,
4250           'nick_name' => 1,
4251           'first_name' => 1,
4252           'middle_name' => 1,
4253           'last_name' => 1,
4254           'prefix_id' => 1,
4255           'suffix_id' => 1,
4256           'formal_title' => 1,
4257           'communication_style_id' => 1,
4258           'birth_date' => 1,
4259           'gender_id' => 1,
4260           'street_address' => 1,
4261           'supplemental_address_1' => 1,
4262           'supplemental_address_2' => 1,
4263           'city' => 1,
4264           'postal_code' => 1,
4265           'postal_code_suffix' => 1,
4266           'state_province' => 1,
4267           'country' => 1,
4268           'world_region' => 1,
4269           'geo_code_1' => 1,
4270           'geo_code_2' => 1,
4271           'email' => 1,
4272           'on_hold' => 1,
4273           'phone' => 1,
4274           'im' => 1,
4275           'household_name' => 1,
4276           'organization_name' => 1,
4277           'deceased_date' => 1,
4278           'is_deceased' => 1,
4279           'job_title' => 1,
4280           'legal_name' => 1,
4281           'sic_code' => 1,
4282           'current_employer' => 1,
4283           // FIXME: should we use defaultHierReturnProperties() for the below?
4284           'do_not_email' => 1,
4285           'do_not_mail' => 1,
4286           'do_not_sms' => 1,
4287           'do_not_phone' => 1,
4288           'do_not_trade' => 1,
4289           'is_opt_out' => 1,
4290           'contact_is_deleted' => 1,
4291           'preferred_communication_method' => 1,
4292           'preferred_language' => 1,
4293         );
4294       }
4295     }
4296     return self::$_defaultReturnProperties[$mode];
4297   }
4298
4299   /**
4300    * Get primary condition for a sql clause.
4301    *
4302    * @param int $value
4303    *
4304    * @return string|NULL
4305    */
4306   public static function getPrimaryCondition($value) {
4307     if (is_numeric($value)) {
4308       $value = (int ) $value;
4309       return ($value == 1) ? 'is_primary = 1' : 'is_primary = 0';
4310     }
4311     return NULL;
4312   }
4313
4314   /**
4315    * Wrapper for a simple search query.
4316    *
4317    * @param array $params
4318    * @param array $returnProperties
4319    * @param bool $count
4320    *
4321    * @return string
4322    */
4323   public static function getQuery($params = NULL, $returnProperties = NULL, $count = FALSE) {
4324     $query = new CRM_Contact_BAO_Query($params, $returnProperties);
4325     list($select, $from, $where, $having) = $query->query();
4326
4327     return "$select $from $where $having";
4328   }
4329
4330   /**
4331    * These are stub comments as this function needs more explanation - particularly in terms of how it
4332    * relates to $this->searchQuery and why it replicates rather than calles $this->searchQuery.
4333    *
4334    * This function was originally written as a wrapper for the api query but is called from multiple places
4335    * in the core code directly so the name is misleading. This function does not use the searchQuery function
4336    * but it is unclear as to whehter that is historical or there is a reason
4337    *  CRM-11290 led to the permissioning action being extracted from searchQuery & shared with this function
4338    *
4339    * @param array $params
4340    * @param array $returnProperties
4341    * @param null $fields
4342    * @param string $sort
4343    * @param int $offset
4344    * @param int $row_count
4345    * @param bool $smartGroupCache
4346    *   ?? update smart group cache?.
4347    * @param bool $count
4348    *   Return count obnly.
4349    * @param bool $skipPermissions
4350    *   Should permissions be ignored or should the logged in user's permissions be applied.
4351    *
4352    *
4353    * @return array
4354    */
4355   public static function apiQuery(
4356     $params = NULL,
4357     $returnProperties = NULL,
4358     $fields = NULL,
4359     $sort = NULL,
4360     $offset = 0,
4361     $row_count = 25,
4362     $smartGroupCache = TRUE,
4363     $count = FALSE,
4364     $skipPermissions = TRUE
4365   ) {
4366
4367     $query = new CRM_Contact_BAO_Query(
4368       $params, $returnProperties,
4369       NULL, TRUE, FALSE, 1,
4370       $skipPermissions,
4371       TRUE, $smartGroupCache
4372     );
4373
4374     //this should add a check for view deleted if permissions are enabled
4375     if ($skipPermissions) {
4376       $query->_skipDeleteClause = TRUE;
4377     }
4378     $query->generatePermissionClause(FALSE, $count);
4379
4380     // note : this modifies _fromClause and _simpleFromClause
4381     $query->includePseudoFieldsJoin($sort);
4382
4383     list($select, $from, $where, $having) = $query->query($count);
4384
4385     $options = $query->_options;
4386     if (!empty($query->_permissionWhereClause)) {
4387       if (empty($where)) {
4388         $where = "WHERE $query->_permissionWhereClause";
4389       }
4390       else {
4391         $where = "$where AND $query->_permissionWhereClause";
4392       }
4393     }
4394
4395     $sql = "$select $from $where $having";
4396
4397     // add group by
4398     if ($query->_useGroupBy) {
4399       $sql .= ' GROUP BY contact_a.id';
4400     }
4401     if (!empty($sort)) {
4402       $sort = CRM_Utils_Type::escape($sort, 'String');
4403       $sql .= " ORDER BY $sort ";
4404     }
4405     if ($row_count > 0 && $offset >= 0) {
4406       $offset = CRM_Utils_Type::escape($offset, 'Int');
4407       $rowCount = CRM_Utils_Type::escape($row_count, 'Int');
4408       $sql .= " LIMIT $offset, $row_count ";
4409     }
4410
4411     $dao = CRM_Core_DAO::executeQuery($sql);
4412
4413     $values = array();
4414     while ($dao->fetch()) {
4415       if ($count) {
4416         $noRows = $dao->rowCount;
4417         $dao->free();
4418         return array($noRows, NULL);
4419       }
4420       $val = $query->store($dao);
4421       $convertedVals = $query->convertToPseudoNames($dao, TRUE);
4422
4423       if (!empty($convertedVals)) {
4424         $val = array_replace_recursive($val, $convertedVals);
4425       }
4426       $values[$dao->contact_id] = $val;
4427     }
4428     $dao->free();
4429     return array($values, $options);
4430   }
4431
4432   /**
4433    * Create and query the db for an contact search.
4434    *
4435    * @param int $offset
4436    *   The offset for the query.
4437    * @param int $rowCount
4438    *   The number of rows to return.
4439    * @param string $sort
4440    *   The order by string.
4441    * @param bool $count
4442    *   Is this a count only query ?.
4443    * @param bool $includeContactIds
4444    *   Should we include contact ids?.
4445    * @param bool $sortByChar
4446    *   If true returns the distinct array of first characters for search results.
4447    * @param bool $groupContacts
4448    *   If true, return only the contact ids.
4449    * @param bool $returnQuery
4450    *   Should we return the query as a string.
4451    * @param string $additionalWhereClause
4452    *   If the caller wants to further restrict the search (used for components).
4453    * @param null $sortOrder
4454    * @param string $additionalFromClause
4455    *   Should be clause with proper joins, effective to reduce where clause load.
4456    *
4457    * @param bool $skipOrderAndLimit
4458    *
4459    * @return CRM_Core_DAO
4460    */
4461   public function searchQuery(
4462     $offset = 0, $rowCount = 0, $sort = NULL,
4463     $count = FALSE, $includeContactIds = FALSE,
4464     $sortByChar = FALSE, $groupContacts = FALSE,
4465     $returnQuery = FALSE,
4466     $additionalWhereClause = NULL, $sortOrder = NULL,
4467     $additionalFromClause = NULL, $skipOrderAndLimit = FALSE
4468   ) {
4469
4470     if ($includeContactIds) {
4471       $this->_includeContactIds = TRUE;
4472       $this->_whereClause = $this->whereClause();
4473     }
4474
4475     $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params);
4476
4477     // if we’re explicitly looking for a certain contact’s contribs, events, etc.
4478     // and that contact happens to be deleted, set $onlyDeleted to true
4479     foreach ($this->_params as $values) {
4480       $name = CRM_Utils_Array::value(0, $values);
4481       $op = CRM_Utils_Array::value(1, $values);
4482       $value = CRM_Utils_Array::value(2, $values);
4483       if ($name == 'contact_id' and $op == '=') {
4484         if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) {
4485           $onlyDeleted = TRUE;
4486         }
4487         break;
4488       }
4489     }
4490
4491     // building the query string
4492     $groupBy = NULL;
4493     if (!$count) {
4494       if (isset($this->_groupByComponentClause)) {
4495         $groupBy = $this->_groupByComponentClause;
4496       }
4497       elseif ($this->_useGroupBy) {
4498         $groupBy = ' GROUP BY contact_a.id';
4499       }
4500     }
4501     if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) {
4502       $groupBy = 'GROUP BY civicrm_activity.id ';
4503     }
4504
4505     $order = $orderBy = $limit = '';
4506     if (!$count) {
4507       $config = CRM_Core_Config::singleton();
4508       if ($config->includeOrderByClause ||
4509         isset($this->_distinctComponentClause)
4510       ) {
4511         if ($sort) {
4512           if (is_string($sort)) {
4513             $orderBy = $sort;
4514           }
4515           else {
4516             $orderBy = trim($sort->orderBy());
4517           }
4518           if (!empty($orderBy)) {
4519             // this is special case while searching for
4520             // change log CRM-1718
4521             if (preg_match('/sort_name/i', $orderBy)) {
4522               $orderBy = str_replace('sort_name', 'contact_a.sort_name', $orderBy);
4523             }
4524
4525             $orderBy = CRM_Utils_Type::escape($orderBy, 'String');
4526             $order = " ORDER BY $orderBy";
4527
4528             if ($sortOrder) {
4529               $sortOrder = CRM_Utils_Type::escape($sortOrder, 'String');
4530               $order .= " $sortOrder";
4531             }
4532
4533             // always add contact_a.id to the ORDER clause
4534             // so the order is deterministic
4535             if (strpos('contact_a.id', $order) === FALSE) {
4536               $order .= ", contact_a.id";
4537             }
4538           }
4539         }
4540         elseif ($sortByChar) {
4541           $order = " ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc";
4542         }
4543         else {
4544           $order = " ORDER BY contact_a.sort_name asc, contact_a.id";
4545         }
4546       }
4547
4548       // hack for order clause
4549       if ($order) {
4550         $fieldStr = trim(str_replace('ORDER BY', '', $order));
4551         $fieldOrder = explode(' ', $fieldStr);
4552         $field = $fieldOrder[0];
4553
4554         if ($field) {
4555           switch ($field) {
4556             case 'city':
4557             case 'postal_code':
4558               $this->_whereTables["civicrm_address"] = 1;
4559               $order = str_replace($field, "civicrm_address.{$field}", $order);
4560               break;
4561
4562             case 'country':
4563             case 'state_province':
4564               $this->_whereTables["civicrm_{$field}"] = 1;
4565               $order = str_replace($field, "civicrm_{$field}.name", $order);
4566               break;
4567
4568             case 'email':
4569               $this->_whereTables["civicrm_email"] = 1;
4570               $order = str_replace($field, "civicrm_email.{$field}", $order);
4571               break;
4572
4573             default:
4574               //CRM-12565 add "`" around $field if it is a pseudo constant
4575               foreach ($this->_pseudoConstantsSelect as $key => $value) {
4576                 if (!empty($value['element']) && $value['element'] == $field) {
4577                   $order = str_replace($field, "`{$field}`", $order);
4578                 }
4579               }
4580           }
4581           $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4582           $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4583         }
4584       }
4585
4586       if ($rowCount > 0 && $offset >= 0) {
4587         $offset = CRM_Utils_Type::escape($offset, 'Int');
4588         $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
4589         $limit = " LIMIT $offset, $rowCount ";
4590       }
4591     }
4592
4593     // CRM-15231
4594     $this->_sort = $sort;
4595
4596     list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted);
4597
4598     if ($additionalWhereClause) {
4599       $where = $where . ' AND ' . $additionalWhereClause;
4600     }
4601
4602     //additional from clause should be w/ proper joins.
4603     if ($additionalFromClause) {
4604       $from .= "\n" . $additionalFromClause;
4605     }
4606
4607     // if we are doing a transform, do it here
4608     // use the $from, $where and $having to get the contact ID
4609     if ($this->_displayRelationshipType) {
4610       $this->filterRelatedContacts($from, $where, $having);
4611     }
4612
4613     if ($skipOrderAndLimit) {
4614       $query = "$select $from $where $having $groupBy";
4615     }
4616     else {
4617       $query = "$select $from $where $having $groupBy $order $limit";
4618     }
4619
4620     if ($returnQuery) {
4621       return $query;
4622     }
4623     if ($count) {
4624       return CRM_Core_DAO::singleValueQuery($query);
4625     }
4626
4627     $dao = CRM_Core_DAO::executeQuery($query);
4628     if ($groupContacts) {
4629       $ids = array();
4630       while ($dao->fetch()) {
4631         $ids[] = $dao->id;
4632       }
4633       return implode(',', $ids);
4634     }
4635
4636     return $dao;
4637   }
4638
4639   /**
4640    * Fetch a list of contacts from the prev/next cache for displaying a search results page
4641    *
4642    * @param string $cacheKey
4643    * @param int $offset
4644    * @param int $rowCount
4645    * @param bool $includeContactIds
4646    * @return CRM_Core_DAO
4647    */
4648   public function getCachedContacts($cacheKey, $offset, $rowCount, $includeContactIds) {
4649     $this->_includeContactIds = $includeContactIds;
4650     $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params);
4651     list($select, $from, $where) = $this->query(FALSE, FALSE, FALSE, $onlyDeleted);
4652     $from = " FROM civicrm_prevnext_cache pnc INNER JOIN civicrm_contact contact_a ON contact_a.id = pnc.entity_id1 AND pnc.cacheKey = '$cacheKey' " . substr($from, 31);
4653     $order = " ORDER BY pnc.id";
4654     $groupBy = " GROUP BY contact_a.id";
4655     $limit = " LIMIT $offset, $rowCount";
4656     $query = "$select $from $where $groupBy $order $limit";
4657
4658     return CRM_Core_DAO::executeQuery($query);
4659   }
4660
4661   /**
4662    * Populate $this->_permissionWhereClause with permission related clause and update other
4663    * query related properties.
4664    *
4665    * Function calls ACL permission class and hooks to filter the query appropriately
4666    *
4667    * Note that these 2 params were in the code when extracted from another function
4668    * and a second round extraction would be to make them properties of the class
4669    *
4670    * @param bool $onlyDeleted
4671    *   Only get deleted contacts.
4672    * @param bool $count
4673    *   Return Count only.
4674    */
4675   public function generatePermissionClause($onlyDeleted = FALSE, $count = FALSE) {
4676     if (!$this->_skipPermission) {
4677       $this->_permissionWhereClause = CRM_ACL_API::whereClause(
4678         CRM_Core_Permission::VIEW,
4679         $this->_tables,
4680         $this->_whereTables,
4681         NULL,
4682         $onlyDeleted,
4683         $this->_skipDeleteClause
4684       );
4685
4686       // regenerate fromClause since permission might have added tables
4687       if ($this->_permissionWhereClause) {
4688         //fix for row count in qill (in contribute/membership find)
4689         if (!$count) {
4690           $this->_useDistinct = TRUE;
4691         }
4692         //CRM-15231
4693         $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4694         $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4695         // note : this modifies _fromClause and _simpleFromClause
4696         $this->includePseudoFieldsJoin($this->_sort);
4697       }
4698     }
4699     else {
4700       // add delete clause if needed even if we are skipping permission
4701       // CRM-7639
4702       if (!$this->_skipDeleteClause) {
4703         if (CRM_Core_Permission::check('access deleted contacts') and $onlyDeleted) {
4704           $this->_permissionWhereClause = '(contact_a.is_deleted)';
4705         }
4706         else {
4707           // CRM-6181
4708           $this->_permissionWhereClause = '(contact_a.is_deleted = 0)';
4709         }
4710       }
4711     }
4712   }
4713
4714   /**
4715    * @param $val
4716    */
4717   public function setSkipPermission($val) {
4718     $this->_skipPermission = $val;
4719   }
4720
4721   /**
4722    * @param null $context
4723    *
4724    * @return array
4725    */
4726   public function &summaryContribution($context = NULL) {
4727     list($innerselect, $from, $where, $having) = $this->query(TRUE);
4728
4729     // hack $select
4730     $select = "
4731 SELECT COUNT( conts.total_amount ) as total_count,
4732        SUM(   conts.total_amount ) as total_amount,
4733        AVG(   conts.total_amount ) as total_avg,
4734        conts.currency              as currency";
4735     if ($this->_permissionWhereClause) {
4736       $where .= " AND " . $this->_permissionWhereClause;
4737     }
4738     if ($context == 'search') {
4739       $where .= " AND contact_a.is_deleted = 0 ";
4740     }
4741
4742     // make sure contribution is completed - CRM-4989
4743     $completedWhere = $where . " AND civicrm_contribution.contribution_status_id = 1 ";
4744
4745     $summary = array();
4746     $summary['total'] = array();
4747     $summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a";
4748
4749     $query = "$select FROM (
4750       SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $completedWhere
4751       GROUP BY civicrm_contribution.id
4752     ) as conts
4753     GROUP BY currency";
4754
4755     $dao = CRM_Core_DAO::executeQuery($query);
4756
4757     $summary['total']['count'] = 0;
4758     $summary['total']['amount'] = $summary['total']['avg'] = array();
4759     while ($dao->fetch()) {
4760       $summary['total']['count'] += $dao->total_count;
4761       $summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
4762       $summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
4763     }
4764     if (!empty($summary['total']['amount'])) {
4765       $summary['total']['amount'] = implode(',&nbsp;', $summary['total']['amount']);
4766       $summary['total']['avg'] = implode(',&nbsp;', $summary['total']['avg']);
4767     }
4768     else {
4769       $summary['total']['amount'] = $summary['total']['avg'] = 0;
4770     }
4771
4772     // soft credit summary
4773     if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
4774       $softCreditWhere = "{$completedWhere} AND civicrm_contribution_soft.id IS NOT NULL";
4775       $query = "
4776         $select FROM (
4777           SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency $from $softCreditWhere
4778           GROUP BY civicrm_contribution_soft.id
4779         ) as conts
4780         GROUP BY currency";
4781       $dao = CRM_Core_DAO::executeQuery($query);
4782       $summary['soft_credit']['count'] = 0;
4783       $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = array();
4784       while ($dao->fetch()) {
4785         $summary['soft_credit']['count'] += $dao->total_count;
4786         $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
4787         $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
4788       }
4789       if (!empty($summary['soft_credit']['amount'])) {
4790         $summary['soft_credit']['amount'] = implode(',&nbsp;', $summary['soft_credit']['amount']);
4791         $summary['soft_credit']['avg'] = implode(',&nbsp;', $summary['soft_credit']['avg']);
4792       }
4793       else {
4794         $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0;
4795       }
4796     }
4797
4798     // hack $select
4799     //@todo  - this could be one query using the IF in mysql - eg
4800     //  SELECT sum(total_completed), sum(count_completed), sum(count_cancelled), sum(total_cancelled) FROM (
4801     //   SELECT civicrm_contribution.total_amount, civicrm_contribution.currency  ,
4802     //  IF(civicrm_contribution.contribution_status_id = 1, 1, 0 ) as count_completed,
4803     //  IF(civicrm_contribution.contribution_status_id = 1, total_amount, 0 ) as total_completed,
4804     //  IF(civicrm_contribution.cancel_date IS NOT NULL = 1, 1, 0 ) as count_cancelled,
4805     //  IF(civicrm_contribution.cancel_date IS NOT NULL = 1, total_amount, 0 ) as total_cancelled
4806     // FROM civicrm_contact contact_a
4807     //  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
4808     // WHERE  ( ... where clause....
4809     // AND (civicrm_contribution.cancel_date IS NOT NULL OR civicrm_contribution.contribution_status_id = 1)
4810     //  ) as conts
4811
4812     $select = "
4813 SELECT COUNT( conts.total_amount ) as cancel_count,
4814        SUM(   conts.total_amount ) as cancel_amount,
4815        AVG(   conts.total_amount ) as cancel_avg,
4816        conts.currency              as currency";
4817
4818     $where .= " AND civicrm_contribution.cancel_date IS NOT NULL ";
4819     if ($context == 'search') {
4820       $where .= " AND contact_a.is_deleted = 0 ";
4821     }
4822
4823     $query = "$select FROM (
4824       SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where
4825       GROUP BY civicrm_contribution.id
4826     ) as conts
4827     GROUP BY currency";
4828
4829     $dao = CRM_Core_DAO::executeQuery($query);
4830
4831     if ($dao->N <= 1) {
4832       if ($dao->fetch()) {
4833         $summary['cancel']['count'] = $dao->cancel_count;
4834         $summary['cancel']['amount'] = $dao->cancel_amount;
4835         $summary['cancel']['avg'] = $dao->cancel_avg;
4836       }
4837     }
4838     else {
4839       $summary['cancel']['count'] = 0;
4840       $summary['cancel']['amount'] = $summary['cancel']['avg'] = array();
4841       while ($dao->fetch()) {
4842         $summary['cancel']['count'] += $dao->cancel_count;
4843         $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
4844         $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
4845       }
4846       $summary['cancel']['amount'] = implode(',&nbsp;', $summary['cancel']['amount']);
4847       $summary['cancel']['avg'] = implode(',&nbsp;', $summary['cancel']['avg']);
4848     }
4849
4850     return $summary;
4851   }
4852
4853   /**
4854    * Getter for the qill object.
4855    *
4856    * @return string
4857    */
4858   public function qill() {
4859     return $this->_qill;
4860   }
4861
4862   /**
4863    * Default set of return default hier return properties.
4864    *
4865    * @return array
4866    */
4867   public static function &defaultHierReturnProperties() {
4868     if (!isset(self::$_defaultHierReturnProperties)) {
4869       self::$_defaultHierReturnProperties = array(
4870         'home_URL' => 1,
4871         'image_URL' => 1,
4872         'legal_identifier' => 1,
4873         'external_identifier' => 1,
4874         'contact_type' => 1,
4875         'contact_sub_type' => 1,
4876         'sort_name' => 1,
4877         'display_name' => 1,
4878         'nick_name' => 1,
4879         'first_name' => 1,
4880         'middle_name' => 1,
4881         'last_name' => 1,
4882         'prefix_id' => 1,
4883         'suffix_id' => 1,
4884         'formal_title' => 1,
4885         'communication_style_id' => 1,
4886         'email_greeting' => 1,
4887         'postal_greeting' => 1,
4888         'addressee' => 1,
4889         'birth_date' => 1,
4890         'gender_id' => 1,
4891         'preferred_communication_method' => 1,
4892         'do_not_phone' => 1,
4893         'do_not_email' => 1,
4894         'do_not_mail' => 1,
4895         'do_not_sms' => 1,
4896         'do_not_trade' => 1,
4897         'location' => array(
4898           '1' => array(
4899             'location_type' => 1,
4900             'street_address' => 1,
4901             'city' => 1,
4902             'state_province' => 1,
4903             'postal_code' => 1,
4904             'postal_code_suffix' => 1,
4905             'country' => 1,
4906             'phone-Phone' => 1,
4907             'phone-Mobile' => 1,
4908             'phone-Fax' => 1,
4909             'phone-1' => 1,
4910             'phone-2' => 1,
4911             'phone-3' => 1,
4912             'im-1' => 1,
4913             'im-2' => 1,
4914             'im-3' => 1,
4915             'email-1' => 1,
4916             'email-2' => 1,
4917             'email-3' => 1,
4918           ),
4919           '2' => array(
4920             'location_type' => 1,
4921             'street_address' => 1,
4922             'city' => 1,
4923             'state_province' => 1,
4924             'postal_code' => 1,
4925             'postal_code_suffix' => 1,
4926             'country' => 1,
4927             'phone-Phone' => 1,
4928             'phone-Mobile' => 1,
4929             'phone-1' => 1,
4930             'phone-2' => 1,
4931             'phone-3' => 1,
4932             'im-1' => 1,
4933             'im-2' => 1,
4934             'im-3' => 1,
4935             'email-1' => 1,
4936             'email-2' => 1,
4937             'email-3' => 1,
4938           ),
4939         ),
4940       );
4941     }
4942     return self::$_defaultHierReturnProperties;
4943   }
4944
4945   /**
4946    * @param $values
4947    * @param string $tableName
4948    * @param string $fieldName
4949    * @param string $dbFieldName
4950    * @param $fieldTitle
4951    * @param bool $appendTimeStamp
4952    */
4953   public function dateQueryBuilder(
4954     &$values, $tableName, $fieldName,
4955     $dbFieldName, $fieldTitle,
4956     $appendTimeStamp = TRUE
4957   ) {
4958     list($name, $op, $value, $grouping, $wildcard) = $values;
4959
4960     if (!$value) {
4961       return;
4962     }
4963
4964     if ($name == "{$fieldName}_low" ||
4965       $name == "{$fieldName}_high"
4966     ) {
4967       if (isset($this->_rangeCache[$fieldName])) {
4968         return;
4969       }
4970       $this->_rangeCache[$fieldName] = 1;
4971
4972       $secondOP = $secondPhrase = $secondValue = $secondDate = $secondDateFormat = NULL;
4973
4974       if ($name == $fieldName . '_low') {
4975         $firstOP = '>=';
4976         $firstPhrase = ts('greater than or equal to');
4977         $firstDate = CRM_Utils_Date::processDate($value);
4978
4979         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
4980         if (!empty($secondValues) && $secondValues[2]) {
4981           $secondOP = '<=';
4982           $secondPhrase = ts('less than or equal to');
4983           $secondValue = $secondValues[2];
4984
4985           if ($appendTimeStamp && strlen($secondValue) == 10) {
4986             $secondValue .= ' 23:59:59';
4987           }
4988           $secondDate = CRM_Utils_Date::processDate($secondValue);
4989         }
4990       }
4991       elseif ($name == $fieldName . '_high') {
4992         $firstOP = '<=';
4993         $firstPhrase = ts('less than or equal to');
4994
4995         if ($appendTimeStamp && strlen($value) == 10) {
4996           $value .= ' 23:59:59';
4997         }
4998         $firstDate = CRM_Utils_Date::processDate($value);
4999
5000         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5001         if (!empty($secondValues) && $secondValues[2]) {
5002           $secondOP = '>=';
5003           $secondPhrase = ts('greater than or equal to');
5004           $secondValue = $secondValues[2];
5005           $secondDate = CRM_Utils_Date::processDate($secondValue);
5006         }
5007       }
5008
5009       if (!$appendTimeStamp) {
5010         $firstDate = substr($firstDate, 0, 8);
5011       }
5012       $firstDateFormat = CRM_Utils_Date::customFormat($firstDate);
5013
5014       if ($secondDate) {
5015         if (!$appendTimeStamp) {
5016           $secondDate = substr($secondDate, 0, 8);
5017         }
5018         $secondDateFormat = CRM_Utils_Date::customFormat($secondDate);
5019       }
5020
5021       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5022       if ($secondDate) {
5023         $this->_where[$grouping][] = "
5024 ( {$tableName}.{$dbFieldName} $firstOP '$firstDate' ) AND
5025 ( {$tableName}.{$dbFieldName} $secondOP '$secondDate' )
5026 ";
5027         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$firstDateFormat\" " . ts('AND') . " $secondPhrase \"$secondDateFormat\"";
5028       }
5029       else {
5030         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP '$firstDate'";
5031         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$firstDateFormat\"";
5032       }
5033     }
5034
5035     if ($name == $fieldName) {
5036       // $op = '=';
5037       $phrase = $op;
5038
5039       $date = CRM_Utils_Date::processDate($value);
5040
5041       if (!$appendTimeStamp) {
5042         $date = substr($date, 0, 8);
5043       }
5044
5045       $format = CRM_Utils_Date::customFormat($date);
5046
5047       if ($date) {
5048         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op '$date'";
5049       }
5050       else {
5051         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op";
5052       }
5053       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5054       $this->_qill[$grouping][] = "$fieldTitle - $phrase \"$format\"";
5055     }
5056   }
5057
5058   /**
5059    * @param $values
5060    * @param string $tableName
5061    * @param string $fieldName
5062    * @param string $dbFieldName
5063    * @param $fieldTitle
5064    * @param null $options
5065    */
5066   public function numberRangeBuilder(
5067     &$values,
5068     $tableName, $fieldName,
5069     $dbFieldName, $fieldTitle,
5070     $options = NULL
5071   ) {
5072     list($name, $op, $value, $grouping, $wildcard) = $values;
5073
5074     if ($name == "{$fieldName}_low" ||
5075       $name == "{$fieldName}_high"
5076     ) {
5077       if (isset($this->_rangeCache[$fieldName])) {
5078         return;
5079       }
5080       $this->_rangeCache[$fieldName] = 1;
5081
5082       $secondOP = $secondPhrase = $secondValue = NULL;
5083
5084       if ($name == "{$fieldName}_low") {
5085         $firstOP = '>=';
5086         $firstPhrase = ts('greater than');
5087
5088         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5089         if (!empty($secondValues)) {
5090           $secondOP = '<=';
5091           $secondPhrase = ts('less than');
5092           $secondValue = $secondValues[2];
5093         }
5094       }
5095       else {
5096         $firstOP = '<=';
5097         $firstPhrase = ts('less than');
5098
5099         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5100         if (!empty($secondValues)) {
5101           $secondOP = '>=';
5102           $secondPhrase = ts('greater than');
5103           $secondValue = $secondValues[2];
5104         }
5105       }
5106
5107       if ($secondOP) {
5108         $this->_where[$grouping][] = "
5109 ( {$tableName}.{$dbFieldName} $firstOP {$value} ) AND
5110 ( {$tableName}.{$dbFieldName} $secondOP {$secondValue} )
5111 ";
5112         $displayValue = $options ? $options[$value] : $value;
5113         $secondDisplayValue = $options ? $options[$secondValue] : $secondValue;
5114
5115         $this->_qill[$grouping][]
5116           = "$fieldTitle - $firstPhrase \"$displayValue\" " . ts('AND') . " $secondPhrase \"$secondDisplayValue\"";
5117       }
5118       else {
5119         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP {$value}";
5120         $displayValue = $options ? $options[$value] : $value;
5121         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$displayValue\"";
5122       }
5123       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5124
5125       return;
5126     }
5127
5128     if ($name == $fieldName) {
5129       $op = '=';
5130       $phrase = '=';
5131
5132       $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op {$value}";
5133
5134       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5135       $displayValue = $options ? $options[$value] : $value;
5136       $this->_qill[$grouping][] = "$fieldTitle - $phrase \"$displayValue\"";
5137     }
5138   }
5139
5140   /**
5141    * Given the field name, operator, value & its data type
5142    * builds the where Clause for the query
5143    * used for handling 'IS NULL'/'IS NOT NULL' operators
5144    *
5145    * @param string $field
5146    *   Fieldname.
5147    * @param string $op
5148    *   Operator.
5149    * @param string $value
5150    *   Value.
5151    * @param string $dataType
5152    *   Data type of the field.
5153    *
5154    * @return string
5155    *   Where clause for the query.
5156    */
5157   public static function buildClause($field, $op, $value = NULL, $dataType = NULL) {
5158     $op = trim($op);
5159     $clause = "$field $op";
5160
5161     switch ($op) {
5162       case 'IS NULL':
5163       case 'IS NOT NULL':
5164         return $clause;
5165
5166       case 'IS EMPTY':
5167         $clause = " (NULLIF($field, '') IS NULL) ";
5168         return $clause;
5169
5170       case 'IS NOT EMPTY':
5171         $clause = " (NULLIF($field, '') IS NOT NULL) ";
5172         return $clause;
5173
5174       case 'IN':
5175       case 'NOT IN':
5176         if (isset($dataType)) {
5177           if (is_array($value)) {
5178             $values = $value;
5179           }
5180           else {
5181             $value = CRM_Utils_Type::escape($value, "String");
5182             $values = explode(',', CRM_Utils_Array::value(0, explode(')', CRM_Utils_Array::value(1, explode('(', $value)))));
5183           }
5184           // supporting multiple values in IN clause
5185           $val = array();
5186           foreach ($values as $v) {
5187             $v = trim($v);
5188             $val[] = "'" . CRM_Utils_Type::escape($v, $dataType) . "'";
5189           }
5190           $value = "(" . implode($val, ",") . ")";
5191         }
5192         return "$clause $value";
5193
5194       default:
5195         if (empty($dataType)) {
5196           $dataType = 'String';
5197         }
5198         if (is_array($value)) {
5199           //this could have come from the api - as in the restWhere section we potentially use the api operator syntax which is becoming more
5200           // widely used and consistent across the codebase
5201           // adding this here won't accept the search functions which don't submit an array
5202           if (($queryString = CRM_Core_DAO::createSqlFilter($field, $value, $dataType)) != FALSE) {
5203             return $queryString;
5204           }
5205         }
5206         $value = CRM_Utils_Type::escape($value, $dataType);
5207         // if we don't have a dataType we should assume
5208         if ($dataType == 'String' || $dataType == 'Text') {
5209           $value = "'" . strtolower($value) . "'";
5210         }
5211         return "$clause $value";
5212     }
5213   }
5214
5215   /**
5216    * @param bool $reset
5217    *
5218    * @return array
5219    */
5220   public function openedSearchPanes($reset = FALSE) {
5221     if (!$reset || empty($this->_whereTables)) {
5222       return self::$_openedPanes;
5223     }
5224
5225     // pane name to table mapper
5226     $panesMapper = array(
5227       ts('Contributions') => 'civicrm_contribution',
5228       ts('Memberships') => 'civicrm_membership',
5229       ts('Events') => 'civicrm_participant',
5230       ts('Relationships') => 'civicrm_relationship',
5231       ts('Activities') => 'civicrm_activity',
5232       ts('Pledges') => 'civicrm_pledge',
5233       ts('Cases') => 'civicrm_case',
5234       ts('Grants') => 'civicrm_grant',
5235       ts('Address Fields') => 'civicrm_address',
5236       ts('Notes') => 'civicrm_note',
5237       ts('Change Log') => 'civicrm_log',
5238       ts('Mailings') => 'civicrm_mailing_event_queue',
5239     );
5240     CRM_Contact_BAO_Query_Hook::singleton()->getPanesMapper($panesMapper);
5241
5242     foreach (array_keys($this->_whereTables) as $table) {
5243       if ($panName = array_search($table, $panesMapper)) {
5244         self::$_openedPanes[$panName] = TRUE;
5245       }
5246     }
5247
5248     return self::$_openedPanes;
5249   }
5250
5251   /**
5252    * @param $operator
5253    */
5254   public function setOperator($operator) {
5255     $validOperators = array('AND', 'OR');
5256     if (!in_array($operator, $validOperators)) {
5257       $operator = 'AND';
5258     }
5259     $this->_operator = $operator;
5260   }
5261
5262   /**
5263    * @return string
5264    */
5265   public function getOperator() {
5266     return $this->_operator;
5267   }
5268
5269   /**
5270    * @param $from
5271    * @param $where
5272    * @param $having
5273    */
5274   public function filterRelatedContacts(&$from, &$where, &$having) {
5275     static $_rTypeProcessed = NULL;
5276     static $_rTypeFrom = NULL;
5277     static $_rTypeWhere = NULL;
5278
5279     if (!$_rTypeProcessed) {
5280       $_rTypeProcessed = TRUE;
5281
5282       // create temp table with contact ids
5283       $tableName = CRM_Core_DAO::createTempTableName('civicrm_transform', TRUE);
5284       $sql = "CREATE TEMPORARY TABLE $tableName ( contact_id int primary key) ENGINE=HEAP";
5285       CRM_Core_DAO::executeQuery($sql);
5286
5287       $sql = "
5288 REPLACE INTO $tableName ( contact_id )
5289 SELECT contact_a.id
5290        $from
5291        $where
5292        $having
5293 ";
5294       CRM_Core_DAO::executeQuery($sql);
5295
5296       $qillMessage = ts('Contacts with a Relationship Type of: ');
5297       $rTypes = CRM_Core_PseudoConstant::relationshipType();
5298
5299       if (is_numeric($this->_displayRelationshipType)) {
5300         $relationshipTypeLabel = $rTypes[$this->_displayRelationshipType]['label_a_b'];
5301         $_rTypeFrom = "
5302 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id OR displayRelType.contact_id_b = contact_a.id )
5303 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a OR transform_temp.contact_id = displayRelType.contact_id_b )
5304 ";
5305         $_rTypeWhere = "
5306 WHERE displayRelType.relationship_type_id = {$this->_displayRelationshipType}
5307 AND   displayRelType.is_active = 1
5308 ";
5309       }
5310       else {
5311         list($relType, $dirOne, $dirTwo) = explode('_', $this->_displayRelationshipType);
5312         if ($dirOne == 'a') {
5313           $relationshipTypeLabel = $rTypes[$relType]['label_a_b'];
5314           $_rTypeFrom .= "
5315 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id )
5316 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_b )
5317 ";
5318         }
5319         else {
5320           $relationshipTypeLabel = $rTypes[$relType]['label_b_a'];
5321           $_rTypeFrom .= "
5322 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_b = contact_a.id )
5323 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a )
5324 ";
5325         }
5326         $_rTypeWhere = "
5327 WHERE displayRelType.relationship_type_id = $relType
5328 AND   displayRelType.is_active = 1
5329 ";
5330       }
5331       $this->_qill[0][] = $qillMessage . "'" . $relationshipTypeLabel . "'";
5332     }
5333
5334     if (!empty($this->_permissionWhereClause)) {
5335       $_rTypeWhere .= "AND $this->_permissionWhereClause";
5336     }
5337
5338     if (strpos($from, $_rTypeFrom) === FALSE) {
5339       // lets replace all the INNER JOIN's in the $from so we dont exclude other data
5340       // this happens when we have an event_type in the quert (CRM-7969)
5341       $from = str_replace("INNER JOIN", "LEFT JOIN", $from);
5342       $from .= $_rTypeFrom;
5343       $where = $_rTypeWhere;
5344     }
5345
5346     $having = NULL;
5347   }
5348
5349   /**
5350    * @param $op
5351    *
5352    * @return bool
5353    */
5354   public static function caseImportant($op) {
5355     return
5356       in_array($op, array('LIKE', 'IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY')) ? FALSE : TRUE;
5357   }
5358
5359   /**
5360    * @param $returnProperties
5361    * @param $prefix
5362    *
5363    * @return bool
5364    */
5365   public static function componentPresent(&$returnProperties, $prefix) {
5366     foreach ($returnProperties as $name => $dontCare) {
5367       if (substr($name, 0, strlen($prefix)) == $prefix) {
5368         return TRUE;
5369       }
5370     }
5371     return FALSE;
5372   }
5373
5374   /**
5375    * Builds the necessary structures for all fields that are similar to option value lookups.
5376    *
5377    * @param string $name
5378    *   the name of the field.
5379    * @param string $op
5380    *   the sql operator, this function should handle ALL SQL operators.
5381    * @param string $value
5382    *   depends on the operator and who's calling the query builder.
5383    * @param int $grouping
5384    *   the index where to place the where clause.
5385    * @param $selectValues
5386    *   The key value pairs for this element. This allows us to use this function for things besides option-value pairs.
5387    * @param array $field
5388    *   an array that contains various properties of the field identified by $name.
5389    * @param string $label
5390    *   The label for this field element.
5391    * @param string $dataType
5392    *   The data type for this element.
5393    * @param bool $useIDsOnly
5394    *
5395    * @return void
5396    *   adds the where clause and qill to the query object
5397    */
5398   public function optionValueQuery(
5399     $name,
5400     $op,
5401     $value,
5402     $grouping,
5403     $selectValues,
5404     $field,
5405     $label,
5406     $dataType = 'String',
5407     $useIDsOnly = FALSE
5408   ) {
5409
5410     if (!empty($selectValues) && !is_array($value) && !empty($selectValues[$value])) {
5411       $qill = $selectValues[$value];
5412     }
5413     else {
5414       $qill = $value;
5415     }
5416
5417     $pseudoFields = array(
5418       'email_greeting',
5419       'postal_greeting',
5420       'addressee',
5421       'gender_id',
5422       'prefix_id',
5423       'suffix_id',
5424       'communication_style_id',
5425     );
5426
5427     if (is_numeric($value)) {
5428       $qill = $selectValues[(int ) $value];
5429     }
5430     elseif ($op == 'IN' || $op == 'NOT IN') {
5431       if (is_array($value)) {
5432         $intVals = array();
5433         $newValues = array();
5434         foreach ($value as $k => $v) {
5435           $intVals[$k] = (int) $k;
5436           $newValues[] = $selectValues[(int) $k];
5437         }
5438
5439         $value = (in_array($name, $pseudoFields)) ? $intVals : $newValues;
5440         $qill = implode(', ', $newValues);
5441       }
5442     }
5443     elseif (!array_key_exists($value, $selectValues)) {
5444       // its a string, lets get the int value
5445       $value = array_search($value, $selectValues);
5446     }
5447     if ($useIDsOnly) {
5448       list($tableName, $fieldName) = explode('.', $field['where'], 2);
5449       if ($tableName == 'civicrm_contact') {
5450         $wc = "contact_a.$fieldName";
5451       }
5452       else {
5453         $wc = "$tableName.id";
5454       }
5455     }
5456     else {
5457       $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
5458     }
5459
5460     if (in_array($name, $pseudoFields)) {
5461       if (!in_array($name, array('gender_id', 'prefix_id', 'suffix_id', 'communication_style_id'))) {
5462         $wc = "contact_a.{$name}_id";
5463       }
5464       $dataType = 'Positive';
5465       $value = (!$value) ? 0 : $value;
5466     }
5467
5468     $this->_qill[$grouping][] = $label . " $op '$qill'";
5469     $op = (in_array($name, $pseudoFields) && ($op == 'LIKE' || $op == 'RLIKE')) ? '=' : $op;
5470     $this->_where[$grouping][] = self::buildClause($wc, $op, $value, $dataType);
5471   }
5472
5473   /**
5474    * Check and explode a user defined numeric string into an array
5475    * this was the protocol used by search builder in the old old days before we had
5476    * super nice js widgets to do the hard work
5477    *
5478    * @param string $string
5479    * @param string $dataType
5480    *   The dataType we should check for the values, default integer.
5481    *
5482    * @return bool|array
5483    *   false if string does not match the pattern
5484    *   array of numeric values if string does match the pattern
5485    */
5486   public static function parseSearchBuilderString($string, $dataType = 'Integer') {
5487     $string = trim($string);
5488     if (substr($string, 0, 1) != '(' || substr($string, -1, 1) != ')') {
5489       Return FALSE;
5490     }
5491
5492     $string = substr($string, 1, -1);
5493     $values = explode(',', $string);
5494     if (empty($values)) {
5495       return FALSE;
5496     }
5497
5498     $returnValues = array();
5499     foreach ($values as $v) {
5500       if ($dataType == 'Integer' && !is_numeric($v)) {
5501         return FALSE;
5502       }
5503       elseif ($dataType == 'String' && !is_string($v)) {
5504         return FALSE;
5505       }
5506       $returnValues[] = trim($v);
5507     }
5508
5509     if (empty($returnValues)) {
5510       return FALSE;
5511     }
5512
5513     return $returnValues;
5514   }
5515
5516   /**
5517    * Convert the pseudo constants id's to their names
5518    *
5519    * @param CRM_Core_DAO $dao
5520    * @param bool $return
5521    *
5522    * @return array|NULL
5523    */
5524   public function convertToPseudoNames(&$dao, $return = FALSE) {
5525     if (empty($this->_pseudoConstantsSelect)) {
5526       return NULL;
5527     }
5528     $values = array();
5529     foreach ($this->_pseudoConstantsSelect as $key => $value) {
5530       if (!empty($this->_pseudoConstantsSelect[$key]['sorting'])) {
5531         continue;
5532       }
5533
5534       if (property_exists($dao, $value['idCol'])) {
5535         $val = $dao->$value['idCol'];
5536
5537         if (CRM_Utils_System::isNull($val)) {
5538           $dao->$key = NULL;
5539         }
5540         elseif ($baoName = CRM_Utils_Array::value('bao', $value, NULL)) {
5541           //preserve id value
5542           $idColumn = "{$key}_id";
5543           $dao->$idColumn = $val;
5544
5545           if ($key == 'state_province_name') {
5546             $dao->$value['pseudoField'] = $dao->$key = CRM_Core_PseudoConstant::stateProvinceAbbreviation($val);
5547           }
5548           else {
5549             $dao->$value['pseudoField'] = $dao->$key = CRM_Core_PseudoConstant::getLabel($baoName, $value['pseudoField'], $val);
5550           }
5551         }
5552         elseif ($value['pseudoField'] == 'state_province_abbreviation') {
5553           $dao->$key = CRM_Core_PseudoConstant::stateProvinceAbbreviation($val);
5554         }
5555         // FIX ME: we should potentially move this to component Query and write a wrapper function that
5556         // handles pseudoconstant fixes for all component
5557         elseif (in_array($value['pseudoField'], array('participant_status', 'participant_role'))) {
5558           $pseudoOptions = $viewValues = array();
5559           $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', $value['pseudoField'], array('flip' => 1));
5560           foreach (explode(CRM_Core_DAO::VALUE_SEPARATOR, $val) as $k => $v) {
5561             $viewValues[] = $pseudoOptions[$v];
5562           }
5563           $dao->$key = implode(', ', $viewValues);
5564         }
5565         else {
5566           $labels = CRM_Core_OptionGroup::values($value['pseudoField']);
5567           $dao->$key = $labels[$val];
5568         }
5569
5570         // return converted values in array format
5571         if ($return) {
5572           if (strpos($key, '-') !== FALSE) {
5573             $keyVal = explode('-', $key);
5574             $current = &$values;
5575             $lastElement = array_pop($keyVal);
5576             foreach ($keyVal as $v) {
5577               if (!array_key_exists($v, $current)) {
5578                 $current[$v] = array();
5579               }
5580               $current = &$current[$v];
5581             }
5582             $current[$lastElement] = $dao->$key;
5583           }
5584           else {
5585             $values[$key] = $dao->$key;
5586           }
5587         }
5588       }
5589     }
5590     return $values;
5591   }
5592
5593   /**
5594    * Include pseudo fields LEFT JOIN.
5595    * @param string|array $sort can be a object or string
5596    *
5597    * @return array|NULL
5598    */
5599   public function includePseudoFieldsJoin($sort) {
5600     if (!$sort || empty($this->_pseudoConstantsSelect)) {
5601       return NULL;
5602     }
5603     $sort = is_string($sort) ? $sort : $sort->orderBy();
5604     $present = array();
5605
5606     foreach ($this->_pseudoConstantsSelect as $name => $value) {
5607       if (!empty($value['table'])) {
5608         $regex = "/({$value['table']}\.|{$name})/";
5609         if (preg_match($regex, $sort)) {
5610           $this->_elemnt[$value['element']] = 1;
5611           $this->_select[$value['element']] = $value['select'];
5612           $this->_pseudoConstantsSelect[$name]['sorting'] = 1;
5613           $present[$value['table']] = $value['join'];
5614         }
5615       }
5616     }
5617     $presentSimpleFrom = $present;
5618
5619     if (array_key_exists('civicrm_worldregion', $this->_whereTables) &&
5620       array_key_exists('civicrm_country', $presentSimpleFrom)
5621     ) {
5622       unset($presentSimpleFrom['civicrm_country']);
5623     }
5624     if (array_key_exists('civicrm_worldregion', $this->_tables) &&
5625       array_key_exists('civicrm_country', $present)
5626     ) {
5627       unset($present['civicrm_country']);
5628     }
5629
5630     $presentClause = $presentSimpleFromClause = NULL;
5631     if (!empty($present)) {
5632       $presentClause = implode(' ', $present);
5633     }
5634     if (!empty($presentSimpleFrom)) {
5635       $presentSimpleFromClause = implode(' ', $presentSimpleFrom);
5636     }
5637
5638     $this->_fromClause = $this->_fromClause . $presentClause;
5639     $this->_simpleFromClause = $this->_simpleFromClause . $presentSimpleFromClause;
5640
5641     return array($presentClause, $presentSimpleFromClause);
5642   }
5643
5644   /**
5645    * Build qill for field.
5646    *
5647    * Qill refers to the query detail visible on the UI.
5648    *
5649    * @param $daoName
5650    * @param $fieldName
5651    * @param $fieldValue
5652    * @param $op
5653    * @param array $pseduoExtraParam
5654    *
5655    * @return array
5656    */
5657   public static function buildQillForFieldValue($daoName, $fieldName, $fieldValue, $op, $pseduoExtraParam = array()) {
5658     $pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array());
5659     $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
5660     if ($fieldName == 'activity_type_id') {
5661       $pseduoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
5662     }
5663
5664     //For those $fieldName which don't have any associated pseudoconstant defined
5665     if (empty($pseduoOptions)) {
5666       if (is_array($fieldValue)) {
5667         $op = key($fieldValue);
5668         $fieldValue = $fieldValue[$op];
5669         if (is_array($fieldValue)) {
5670           $fieldValue = implode(', ', $fieldValue);
5671         }
5672       }
5673     }
5674     elseif (is_array($fieldValue)) {
5675       $qillString = array();
5676       if (in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
5677         $op = key($fieldValue);
5678         $fieldValue = $fieldValue[$op];
5679       }
5680       foreach ((array) $fieldValue as $val) {
5681         $qillString[] = $pseduoOptions[$val];
5682       }
5683       $fieldValue = implode(', ', $qillString);
5684     }
5685     else {
5686       if (array_key_exists($fieldValue, $pseduoOptions)) {
5687         $fieldValue = $pseduoOptions[$fieldValue];
5688       }
5689     }
5690
5691     return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue);
5692   }
5693
5694 }