CRM/Contact/BAO/Query.php

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