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     elseif ($id == 'contact_type') {
1536       $result = array($id, 'IN', $values, 0, $wildcard);
1537     }
1538     else {
1539       $result = array($id, '=', $values, 0, $wildcard);
1540     }
1541
1542     return $result;
1543   }
1544
1545   /**
1546    * @param $values
1547    */
1548   function whereClauseSingle(&$values) {
1549     // do not process custom fields or prefixed contact ids or component params
1550     if (CRM_Core_BAO_CustomField::getKeyID($values[0]) ||
1551       (substr($values[0], 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) ||
1552       (substr($values[0], 0, 13) == 'contribution_') ||
1553       (substr($values[0], 0, 6) == 'event_') ||
1554       (substr($values[0], 0, 12) == 'participant_') ||
1555       (substr($values[0], 0, 7) == 'member_') ||
1556       (substr($values[0], 0, 6) == 'grant_') ||
1557       (substr($values[0], 0, 7) == 'pledge_') ||
1558       (substr($values[0], 0, 5) == 'case_') ||
1559       (substr($values[0], 0, 10) == 'financial_') ||
1560       (substr($values[0], 0, 11) == 'membership_')
1561     ) {
1562       return;
1563     }
1564
1565     // skip for hook injected fields / params
1566     $extFields = CRM_Contact_BAO_Query_Hook::singleton()->getFields();
1567     if (array_key_exists($values[0], $extFields)) {
1568       return;
1569     }
1570
1571     switch ($values[0]) {
1572       case 'deleted_contacts':
1573         $this->deletedContacts($values);
1574         return;
1575
1576       case 'contact_type':
1577         $this->contactType($values);
1578         return;
1579
1580       case 'contact_sub_type':
1581         $this->contactSubType($values);
1582         return;
1583
1584       case 'group':
1585         $this->group($values);
1586         return;
1587
1588       case 'group_type':
1589         // so we resolve this into a list of groups & proceed as if they had been
1590         // handed in
1591         list($name, $op, $value, $grouping, $wildcard) = $values;
1592         $values[0] = 'group';
1593         $values[1] = 'IN';
1594         $this->_paramLookup['group'][0][0] ='group';
1595         $this->_paramLookup['group'][0][1] = 'IN';
1596         $this->_paramLookup['group'][0][2] = $values[2] = $this->getGroupsFromTypeCriteria($value);
1597         $this->group($values);
1598         return;
1599
1600       // case tag comes from find contacts
1601       case 'tag_search':
1602         $this->tagSearch($values);
1603         return;
1604
1605       case 'tag':
1606       case 'contact_tags':
1607         $this->tag($values);
1608         return;
1609
1610       case 'note':
1611       case 'note_body':
1612       case 'note_subject':
1613         $this->notes($values);
1614         return;
1615
1616       case 'uf_user':
1617         $this->ufUser($values);
1618         return;
1619
1620       case 'sort_name':
1621       case 'display_name':
1622         $this->sortName($values);
1623         return;
1624
1625       case 'email':
1626         $this->email($values);
1627         return;
1628
1629       case 'phone_numeric':
1630         $this->phone_numeric($values);
1631         return;
1632
1633       case 'phone_phone_type_id':
1634       case 'phone_location_type_id':
1635         $this->phone_option_group($values);
1636         return;
1637
1638       case 'street_address':
1639         $this->street_address($values);
1640         return;
1641
1642       case 'street_number':
1643         $this->street_number($values);
1644         return;
1645
1646       case 'sortByCharacter':
1647         $this->sortByCharacter($values);
1648         return;
1649
1650       case 'location_type':
1651         $this->locationType($values);
1652         return;
1653
1654       case 'county':
1655         $this->county($values);
1656         return;
1657
1658       case 'state_province':
1659         $this->stateProvince($values);
1660         return;
1661
1662       case 'country':
1663         $this->country($values, FALSE);
1664         return;
1665
1666       case 'postal_code':
1667       case 'postal_code_low':
1668       case 'postal_code_high':
1669         $this->postalCode($values);
1670         return;
1671
1672       case 'activity_date':
1673       case 'activity_date_low':
1674       case 'activity_date_high':
1675       case 'activity_role':
1676       case 'activity_status':
1677       case 'followup_parent_id':
1678       case 'parent_id':
1679       case 'activity_subject':
1680       case 'test_activities':
1681       case 'activity_type_id':
1682       case 'activity_type':
1683       case 'activity_survey_id':
1684       case 'activity_tags':
1685       case 'activity_taglist':
1686       case 'activity_test':
1687       case 'activity_campaign_id':
1688       case 'activity_engagement_level':
1689       case 'activity_id':
1690       case 'activity_result':
1691       case 'source_contact':
1692         CRM_Activity_BAO_Query::whereClauseSingle($values, $this);
1693         return;
1694
1695       case 'birth_date_low':
1696       case 'birth_date_high':
1697       case 'deceased_date_low':
1698       case 'deceased_date_high':
1699         $this->demographics($values);
1700         return;
1701
1702       case 'log_date_low':
1703       case 'log_date_high':
1704         $this->modifiedDates($values);
1705         return;
1706
1707       case 'changed_by':
1708         $this->changeLog($values);
1709         return;
1710
1711       case 'do_not_phone':
1712       case 'do_not_email':
1713       case 'do_not_mail':
1714       case 'do_not_sms':
1715       case 'do_not_trade':
1716       case 'is_opt_out':
1717         $this->privacy($values);
1718         return;
1719
1720       case 'privacy_options':
1721         $this->privacyOptions($values);
1722         return;
1723
1724       case 'privacy_operator':
1725       case 'privacy_toggle':
1726         // these are handled by privacy options
1727         return;
1728
1729       case 'preferred_communication_method':
1730         $this->preferredCommunication($values);
1731         return;
1732
1733       case 'relation_type_id':
1734       case 'relation_start_date_high':
1735       case 'relation_start_date_low':
1736       case 'relation_end_date_high':
1737       case 'relation_end_date_low':
1738       case 'relation_target_name':
1739       case 'relation_status':
1740       case 'relation_date_low':
1741       case 'relation_date_high':
1742         $this->relationship($values);
1743         $this->_relationshipValuesAdded = TRUE;
1744         return;
1745
1746       case 'task_status_id':
1747         $this->task($values);
1748         return;
1749
1750       case 'task_id':
1751         // since this case is handled with the above
1752         return;
1753
1754       case 'prox_distance':
1755         CRM_Contact_BAO_ProximityQuery::process($this, $values);
1756         return;
1757
1758       case 'prox_street_address':
1759       case 'prox_city':
1760       case 'prox_postal_code':
1761       case 'prox_state_province_id':
1762       case 'prox_country_id':
1763         // handled by the proximity_distance clause
1764         return;
1765
1766       default:
1767         $this->restWhere($values);
1768         return;
1769     }
1770   }
1771
1772   /**
1773    * Given a list of conditions in params generate the required
1774    * where clause
1775    *
1776    * @return string
1777    * @access public
1778    */
1779   function whereClause() {
1780     $this->_where[0] = array();
1781     $this->_qill[0] = array();
1782
1783     $this->includeContactIds();
1784     if (!empty($this->_params)) {
1785       foreach (array_keys($this->_params) as $id) {
1786         if (empty($this->_params[$id][0])) {
1787           continue;
1788         }
1789         // check for both id and contact_id
1790         if ($this->_params[$id][0] == 'id' || $this->_params[$id][0] == 'contact_id') {
1791           if (
1792             $this->_params[$id][1] == 'IS NULL' ||
1793             $this->_params[$id][1] == 'IS NOT NULL'
1794           ) {
1795             $this->_where[0][] = "contact_a.id {$this->_params[$id][1]}";
1796           }
1797           elseif (is_array($this->_params[$id][2])) {
1798             $idList = implode("','", $this->_params[$id][2]);
1799             //why on earth do they put ' in the middle & not on the outside? We have to assume it's
1800             //to support 'something' so lets add them conditionally to support the api (which is a tested flow
1801             // so if you are looking to alter this check api test results
1802             if(strpos(trim($idList), "'") > 0) {
1803               $idList = "'" . $idList . "'";
1804             }
1805
1806             $this->_where[0][] = "contact_a.id IN ({$idList})";
1807           }
1808           else {
1809             $this->_where[0][] = "contact_a.id {$this->_params[$id][1]} {$this->_params[$id][2]}";
1810           }
1811         }
1812         else {
1813           $this->whereClauseSingle($this->_params[$id]);
1814         }
1815       }
1816
1817       CRM_Core_Component::alterQuery($this, 'where');
1818
1819       CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'where');
1820     }
1821
1822     if ($this->_customQuery) {
1823       // Added following if condition to avoid the wrong value diplay for 'myaccount' / any UF info.
1824       // Hope it wont affect the other part of civicrm.. if it does please remove it.
1825       if (!empty($this->_customQuery->_where)) {
1826         $this->_where = CRM_Utils_Array::crmArrayMerge($this->_where, $this->_customQuery->_where);
1827       }
1828
1829       $this->_qill = CRM_Utils_Array::crmArrayMerge($this->_qill, $this->_customQuery->_qill);
1830     }
1831
1832     $clauses = array();
1833     $andClauses = array();
1834
1835     $validClauses = 0;
1836     if (!empty($this->_where)) {
1837       foreach ($this->_where as $grouping => $values) {
1838         if ($grouping > 0 && !empty($values)) {
1839           $clauses[$grouping] = ' ( ' . implode(" {$this->_operator} ", $values) . ' ) ';
1840           $validClauses++;
1841         }
1842       }
1843
1844       if (!empty($this->_where[0])) {
1845         $andClauses[] = ' ( ' . implode(" {$this->_operator} ", $this->_where[0]) . ' ) ';
1846       }
1847       if (!empty($clauses)) {
1848         $andClauses[] = ' ( ' . implode(' OR ', $clauses) . ' ) ';
1849       }
1850
1851       if ($validClauses > 1) {
1852         $this->_useDistinct = TRUE;
1853       }
1854     }
1855
1856     return implode(' AND ', $andClauses);
1857   }
1858
1859   /**
1860    * @param $values
1861    *
1862    * @throws Exception
1863    */
1864   function restWhere(&$values) {
1865     $name = CRM_Utils_Array::value(0, $values);
1866     $op = CRM_Utils_Array::value(1, $values);
1867     $value = CRM_Utils_Array::value(2, $values);
1868     $grouping = CRM_Utils_Array::value(3, $values);
1869     $wildcard = CRM_Utils_Array::value(4, $values);
1870
1871     if (isset($grouping) && empty($this->_where[$grouping])) {
1872       $this->_where[$grouping] = array();
1873     }
1874
1875     $multipleFields = array('url');
1876
1877     //check if the location type exits for fields
1878     $lType = '';
1879     $locType = explode('-', $name);
1880
1881     if (!in_array($locType[0], $multipleFields)) {
1882       //add phone type if exists
1883       if (isset($locType[2]) && $locType[2]) {
1884         $locType[2] = CRM_Core_DAO::escapeString($locType[2]);
1885       }
1886     }
1887
1888     $field = CRM_Utils_Array::value($name, $this->_fields);
1889
1890     if (!$field) {
1891       $field = CRM_Utils_Array::value($locType[0], $this->_fields);
1892
1893       if (!$field) {
1894         return;
1895       }
1896     }
1897
1898     $setTables = TRUE;
1899
1900     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
1901     $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
1902
1903     if (substr($name, 0, 14) === 'state_province') {
1904       if (isset($locType[1]) && is_numeric($locType[1])) {
1905         $setTables = FALSE;
1906         $aName = "{$locationType[$locType[1]]}-address";
1907         $where = "`$aName`.state_province_id";
1908       }
1909       else {
1910         $where = "civicrm_address.state_province_id";
1911       }
1912
1913       $states = CRM_Core_PseudoConstant::stateProvince();
1914       if (is_numeric($value)) {
1915         $this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
1916         $value = $states[(int ) $value];
1917       }
1918       else {
1919         $intVal = CRM_Utils_Array::key($value, $states);
1920         $this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
1921       }
1922       if (!$lType) {
1923         $this->_qill[$grouping][] = ts('State') . " $op '$value'";
1924       }
1925       else {
1926         $this->_qill[$grouping][] = ts('State') . " ($lType) $op '$value'";
1927       }
1928     }
1929     elseif (!empty($field['pseudoconstant'])) {
1930       $this->optionValueQuery(
1931         $name, $op, $value, $grouping,
1932         CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', $field['name']),
1933         $field,
1934         $field['title'],
1935         'String',
1936         TRUE
1937       );
1938       if ($name == 'gender_id') {
1939         self::$_openedPanes[ts('Demographics')] = TRUE;
1940       }
1941     }
1942     elseif (substr($name, 0, 7) === 'country') {
1943       if (isset($locType[1]) && is_numeric($locType[1])) {
1944         $setTables = FALSE;
1945         $aName = "{$locationType[$locType[1]]}-address";
1946         $where = "`$aName`.country_id";
1947       }
1948       else {
1949         $where = "civicrm_address.country_id";
1950       }
1951
1952       $countries = CRM_Core_PseudoConstant::country();
1953       if (is_numeric($value)) {
1954         $this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
1955         $value = $countries[(int ) $value];
1956       }
1957       else {
1958         $intVal = CRM_Utils_Array::key($value, $countries);
1959         $this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
1960       }
1961
1962       if (!$lType) {
1963         $this->_qill[$grouping][] = ts('Country') . " $op '$value'";
1964       }
1965       else {
1966         $this->_qill[$grouping][] = ts('Country') . " ($lType) $op '$value'";
1967       }
1968     }
1969     elseif (substr($name, 0, 6) === 'county') {
1970       if (isset($locType[1]) && is_numeric($locType[1])) {
1971         $setTables = FALSE;
1972         $aName = "{$locationType[$locType[1]]}-address";
1973         $where = "`$aName`.county_id";
1974       }
1975       else {
1976         $where = "civicrm_address.county_id";
1977       }
1978
1979       $counties = CRM_Core_PseudoConstant::county();
1980       if (is_numeric($value)) {
1981         $this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
1982         $value = $counties[(int ) $value];
1983       }
1984       else {
1985         $intVal = CRM_Utils_Array::key($value, $counties);
1986         $this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
1987       }
1988
1989       if (!$lType) {
1990         $this->_qill[$grouping][] = ts('County') . " $op '$value'";
1991       }
1992       else {
1993         $this->_qill[$grouping][] = ts('County') . " ($lType) $op '$value'";
1994       }
1995     }
1996     elseif ($name === 'world_region') {
1997       $field['where'] = 'civicrm_worldregion.id';
1998       $this->optionValueQuery(
1999         $name, $op, $value, $grouping,
2000         CRM_Core_PseudoConstant::worldRegion(),
2001         $field,
2002         ts('World Region')
2003       );
2004     }
2005     elseif ($name === 'birth_date') {
2006       $date = CRM_Utils_Date::processDate($value);
2007       $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
2008
2009       if ($date) {
2010         $date = CRM_Utils_Date::customFormat($date);
2011         $this->_qill[$grouping][] = "$field[title] $op \"$date\"";
2012       }
2013       else {
2014         $this->_qill[$grouping][] = "$field[title] $op";
2015       }
2016       self::$_openedPanes[ts('Demographics')] = TRUE;
2017     }
2018     elseif ($name === 'deceased_date') {
2019       $date = CRM_Utils_Date::processDate($value);
2020       $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
2021       if ($date) {
2022         $date = CRM_Utils_Date::customFormat($date);
2023         $this->_qill[$grouping][] = "$field[title] $op \"$date\"";
2024       }
2025       else {
2026         $this->_qill[$grouping][] = "$field[title] $op";
2027       }
2028       self::$_openedPanes[ts('Demographics')] = TRUE;
2029     }
2030     elseif ($name === 'is_deceased') {
2031       $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
2032       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2033       self::$_openedPanes[ts('Demographics')] = TRUE;
2034     }
2035     elseif ($name === 'contact_id') {
2036       if (is_int($value)) {
2037         $this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
2038         $this->_qill[$grouping][] = "$field[title] $op $value";
2039       }
2040     }
2041     elseif ($name === 'name') {
2042       $value = $strtolower(CRM_Core_DAO::escapeString($value));
2043       if ($wildcard) {
2044         $value = "%$value%";
2045         $op = 'LIKE';
2046       }
2047       $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
2048       $this->_where[$grouping][] = self::buildClause($wc, $op, "'$value'");
2049       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2050     }
2051     elseif ($name === 'current_employer') {
2052       $value = $strtolower(CRM_Core_DAO::escapeString($value));
2053       if ($wildcard) {
2054         $value = "%$value%";
2055         $op = 'LIKE';
2056       }
2057       $wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
2058       $ceWhereClause = self::buildClause($wc, $op,
2059         $value
2060       );
2061       $ceWhereClause .= " AND contact_a.contact_type = 'Individual'";
2062       $this->_where[$grouping][] = $ceWhereClause;
2063       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2064     }
2065     elseif ($name === 'email_greeting') {
2066       $filterCondition = array('greeting_type' => 'email_greeting');
2067       $this->optionValueQuery(
2068         $name, $op, $value, $grouping,
2069         CRM_Core_PseudoConstant::greeting($filterCondition),
2070         $field,
2071         ts('Email Greeting')
2072       );
2073     }
2074     elseif ($name === 'postal_greeting') {
2075       $filterCondition = array('greeting_type' => 'postal_greeting');
2076       $this->optionValueQuery(
2077         $name, $op, $value, $grouping,
2078         CRM_Core_PseudoConstant::greeting($filterCondition),
2079         $field,
2080         ts('Postal Greeting')
2081       );
2082     }
2083     elseif ($name === 'addressee') {
2084       $filterCondition = array('greeting_type' => 'addressee');
2085       $this->optionValueQuery(
2086         $name, $op, $value, $grouping,
2087         CRM_Core_PseudoConstant::greeting($filterCondition),
2088         $field,
2089         ts('Addressee')
2090       );
2091     }
2092     elseif (substr($name, 0, 4) === 'url-') {
2093       $tName = 'civicrm_website';
2094       $this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
2095       $value = $strtolower(CRM_Core_DAO::escapeString($value));
2096       if ($wildcard) {
2097         $value = "%$value%";
2098         $op = 'LIKE';
2099       }
2100
2101       $wc = 'civicrm_website.url';
2102       $this->_where[$grouping][] = $d = self::buildClause($wc, $op, $value);
2103       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2104     }
2105     elseif ($name === 'contact_is_deleted') {
2106       $this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
2107       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2108     }
2109     else {
2110       if (is_array($value)) {
2111         // traditionally an array being passed has been a fatal error. We can take advantage of this to add support
2112         // for api style operators for functions that hit this point without worrying about regression
2113         // (the previous comments indicated the condition for hitting this point were unknown
2114         // per CRM-14743 we are adding modified_date & created_date operator support
2115         $operations = array_keys($value);
2116         foreach ($operations as $operator) {
2117           if(!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) {
2118             // we don't know when this might happen
2119             CRM_Core_Error::fatal();
2120           }
2121         }
2122         $this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($name, $value, NULL);
2123         //since this is not currently being called by the form layer we can skip worrying about the 'qill' for now
2124         return;
2125       }
2126
2127       if (!empty($field['where'])) {
2128         if ($op != 'IN') {
2129           $value = $strtolower($value);
2130         }
2131         if ($wildcard) {
2132           $value = "%$value%";
2133           $op = 'LIKE';
2134         }
2135
2136         if (isset($locType[1]) &&
2137           is_numeric($locType[1])
2138         ) {
2139           $setTables = FALSE;
2140
2141           //get the location name
2142           list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
2143
2144           $where = "`$tName`.$fldName";
2145
2146           $this->_where[$grouping][] = self::buildClause("LOWER($where)", $op, $value);
2147           // we set both _tables & whereTables because whereTables doesn't seem to do what the name implies it should
2148           $this->_tables[$tName] = $this->_whereTables[$tName] = 1;
2149           $this->_qill[$grouping][] = "$field[title] $op '$value'";
2150         }
2151         else {
2152           list($tableName, $fieldName) = explode('.', $field['where'], 2);
2153           if ($tableName == 'civicrm_contact') {
2154             $fieldName = "LOWER(contact_a.{$fieldName})";
2155           }
2156           else {
2157             if ($op != 'IN' && !is_numeric($value)) {
2158               $fieldName = "LOWER({$field['where']})";
2159             }
2160             else {
2161               $fieldName = "{$field['where']}";
2162             }
2163           }
2164
2165           $type = NULL;
2166           if (!empty($field['type'])) {
2167             $type = CRM_Utils_Type::typeToString($field['type']);
2168           }
2169
2170           $this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
2171           $this->_qill[$grouping][] = "$field[title] $op $value";
2172         }
2173       }
2174     }
2175
2176     if ($setTables && isset($field['where'])) {
2177       list($tableName, $fieldName) = explode('.', $field['where'], 2);
2178       if (isset($tableName)) {
2179         $this->_tables[$tableName] = 1;
2180         $this->_whereTables[$tableName] = 1;
2181       }
2182     }
2183   }
2184
2185
2186   /**
2187    * @param $where
2188    * @param $locType
2189    *
2190    * @return array
2191    * @throws Exception
2192    */
2193   static function getLocationTableName(&$where, &$locType) {
2194     if (isset($locType[1]) && is_numeric($locType[1])) {
2195       list($tbName, $fldName) = explode(".", $where);
2196
2197       //get the location name
2198       $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
2199       $specialFields = array('email', 'im', 'phone', 'openid', 'phone_ext');
2200       if (in_array($locType[0], $specialFields)) {
2201         //hack to fix / special handing for phone_ext
2202         if ($locType[0] == 'phone_ext') {
2203           $locType[0] = 'phone';
2204         }
2205         if (isset($locType[2]) && $locType[2]) {
2206           $tName = "{$locationType[$locType[1]]}-{$locType[0]}-{$locType[2]}";
2207         }
2208         else {
2209           $tName = "{$locationType[$locType[1]]}-{$locType[0]}";
2210         }
2211       }
2212       elseif (in_array($locType[0],
2213           array(
2214             'address_name', 'street_address', 'supplemental_address_1', 'supplemental_address_2',
2215             'city', 'postal_code', 'postal_code_suffix', 'geo_code_1', 'geo_code_2',
2216           )
2217         )) {
2218         //fix for search by profile with address fields.
2219         $tName = "{$locationType[$locType[1]]}-address";
2220       }
2221       elseif ($locType[0] == 'on_hold') {
2222         $tName = "{$locationType[$locType[1]]}-email";
2223       }
2224       else {
2225         $tName = "{$locationType[$locType[1]]}-{$locType[0]}";
2226       }
2227       $tName = str_replace(' ', '_', $tName);
2228       return array($tName, $fldName);
2229     }
2230     CRM_Core_Error::fatal();
2231   }
2232
2233   /**
2234    * Given a result dao, extract the values and return that array
2235    *
2236    * @param Object $dao
2237    *
2238    * @return array values for this query
2239    */
2240   function store($dao) {
2241     $value = array();
2242
2243     foreach ($this->_element as $key => $dontCare) {
2244       if (property_exists($dao, $key)) {
2245         if (strpos($key, '-') !== FALSE) {
2246           $values = explode('-', $key);
2247           $lastElement = array_pop($values);
2248           $current = &$value;
2249           $cnt = count($values);
2250           $count = 1;
2251           foreach ($values as $v) {
2252             if (!array_key_exists($v, $current)) {
2253               $current[$v] = array();
2254             }
2255             //bad hack for im_provider
2256             if ($lastElement == 'provider_id') {
2257               if ($count < $cnt) {
2258                 $current = &$current[$v];
2259               }
2260               else {
2261                 $lastElement = "{$v}_{$lastElement}";
2262               }
2263             }
2264             else {
2265               $current = &$current[$v];
2266             }
2267             $count++;
2268           }
2269
2270           $current[$lastElement] = $dao->$key;
2271         }
2272         else {
2273           $value[$key] = $dao->$key;
2274         }
2275       }
2276     }
2277     return $value;
2278   }
2279
2280   /**
2281    * getter for tables array
2282    *
2283    * @return array
2284    * @access public
2285    */
2286   function tables() {
2287     return $this->_tables;
2288   }
2289
2290   /**
2291    * Where tables is sometimes used to create the from clause, but, not reliably, set this AND set tables
2292    * It's unclear the intent - there is a 'simpleFrom' clause which takes whereTables into account & a fromClause which doesn't
2293    * logic may have eroded
2294    * @return array
2295    */
2296   function whereTables() {
2297     return $this->_whereTables;
2298   }
2299
2300   /**
2301    * generate the where clause (used in match contacts and permissions)
2302    *
2303    * @param array $params
2304    * @param array $fields
2305    * @param array $tables
2306    * @param $whereTables
2307    * @param boolean $strict
2308    *
2309    * @return string
2310    * @access public
2311    * @static
2312    */
2313   static function getWhereClause($params, $fields, &$tables, &$whereTables, $strict = FALSE) {
2314     $query = new CRM_Contact_BAO_Query($params, NULL, $fields,
2315       FALSE, $strict
2316     );
2317
2318     $tables = array_merge($query->tables(), $tables);
2319     $whereTables = array_merge($query->whereTables(), $whereTables);
2320
2321     return $query->_whereClause;
2322   }
2323
2324   /**
2325    * create the from clause
2326    *
2327    * @param array $tables tables that need to be included in this from clause
2328    *                      if null, return mimimal from clause (i.e. civicrm_contact)
2329    * @param array $inner tables that should be inner-joined
2330    * @param array $right tables that should be right-joined
2331    *
2332    * @param bool $primaryLocation
2333    * @param int $mode
2334    *
2335    * @return string the from clause
2336    * @access public
2337    * @static
2338    */
2339   static function fromClause(&$tables, $inner = NULL, $right = NULL, $primaryLocation = TRUE, $mode = 1) {
2340
2341     $from = ' FROM civicrm_contact contact_a';
2342     if (empty($tables)) {
2343       return $from;
2344     }
2345
2346     if (!empty($tables['civicrm_worldregion'])) {
2347       $tables = array_merge(array('civicrm_country' => 1), $tables);
2348     }
2349
2350     if ((!empty($tables['civicrm_state_province']) || !empty($tables['civicrm_country']) ||
2351         CRM_Utils_Array::value('civicrm_county', $tables)
2352       ) && empty($tables['civicrm_address'])) {
2353       $tables = array_merge(array('civicrm_address' => 1),
2354         $tables
2355       );
2356     }
2357
2358     // add group_contact table if group table is present
2359     if (!empty($tables['civicrm_group']) && empty($tables['civicrm_group_contact'])) {
2360       $tables['civicrm_group_contact'] = " LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id AND civicrm_group_contact.status = 'Added'";
2361     }
2362
2363     // add group_contact and group table is subscription history is present
2364     if (!empty($tables['civicrm_subscription_history']) && empty($tables['civicrm_group'])) {
2365       $tables = array_merge(array(
2366         'civicrm_group' => 1,
2367           'civicrm_group_contact' => 1,
2368         ),
2369         $tables
2370       );
2371     }
2372
2373     // to handle table dependencies of components
2374     CRM_Core_Component::tableNames($tables);
2375     // to handle table dependencies of hook injected tables
2376     CRM_Contact_BAO_Query_Hook::singleton()->setTableDependency($tables);
2377
2378     //format the table list according to the weight
2379     $info = CRM_Core_TableHierarchy::info();
2380
2381     foreach ($tables as $key => $value) {
2382       $k = 99;
2383       if (strpos($key, '-') !== FALSE) {
2384         $keyArray = explode('-', $key);
2385         $k = CRM_Utils_Array::value('civicrm_' . $keyArray[1], $info, 99);
2386       }
2387       elseif (strpos($key, '_') !== FALSE) {
2388         $keyArray = explode('_', $key);
2389         if (is_numeric(array_pop($keyArray))) {
2390           $k = CRM_Utils_Array::value(implode('_', $keyArray), $info, 99);
2391         }
2392         else {
2393           $k = CRM_Utils_Array::value($key, $info, 99);
2394         }
2395       }
2396       else {
2397         $k = CRM_Utils_Array::value($key, $info, 99);
2398       }
2399       $tempTable[$k . ".$key"] = $key;
2400     }
2401     ksort($tempTable);
2402     $newTables = array();
2403     foreach ($tempTable as $key) {
2404       $newTables[$key] = $tables[$key];
2405     }
2406
2407     $tables = $newTables;
2408
2409     foreach ($tables as $name => $value) {
2410       if (!$value) {
2411         continue;
2412       }
2413
2414       if (!empty($inner[$name])) {
2415         $side = 'INNER';
2416       }
2417       elseif (!empty($right[$name])) {
2418         $side = 'RIGHT';
2419       }
2420       else {
2421         $side = 'LEFT';
2422       }
2423
2424       if ($value != 1) {
2425         // if there is already a join statement in value, use value itself
2426         if (strpos($value, 'JOIN')) {
2427           $from .= " $value ";
2428         }
2429         else {
2430           $from .= " $side JOIN $name ON ( $value ) ";
2431         }
2432         continue;
2433       }
2434       switch ($name) {
2435         case 'civicrm_address':
2436           if ($primaryLocation) {
2437             $from .= " $side JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )";
2438           }
2439           else {
2440             //CRM-14263 further handling of address joins further down...
2441             $from .= " $side JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) ";
2442           }
2443           continue;
2444
2445         case 'civicrm_phone':
2446           $from .= " $side JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) ";
2447           continue;
2448
2449         case 'civicrm_email':
2450           $from .= " $side JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) ";
2451           continue;
2452
2453         case 'civicrm_im':
2454           $from .= " $side JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) ";
2455           continue;
2456
2457         case 'im_provider':
2458           $from .= " $side JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id) ";
2459           $from .= " $side JOIN civicrm_option_group option_group_imProvider ON option_group_imProvider.name = 'instant_messenger_service'";
2460           $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)";
2461           continue;
2462
2463         case 'civicrm_openid':
2464           $from .= " $side JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id AND civicrm_openid.is_primary = 1 )";
2465           continue;
2466
2467         case 'civicrm_worldregion':
2468           $from .= " $side JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id ";
2469           $from .= " $side JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ";
2470           continue;
2471
2472         case 'civicrm_location_type':
2473           $from .= " $side JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id ";
2474           continue;
2475
2476         case 'civicrm_group':
2477           $from .= " $side JOIN civicrm_group ON civicrm_group.id =  civicrm_group_contact.group_id ";
2478           continue;
2479
2480         case 'civicrm_group_contact':
2481           $from .= " $side JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id ";
2482           continue;
2483
2484         case 'civicrm_activity':
2485         case 'civicrm_activity_tag':
2486         case 'activity_type':
2487         case 'activity_status':
2488         case 'parent_id':
2489         case 'civicrm_activity_contact':
2490         case 'source_contact':
2491           $from .= CRM_Activity_BAO_Query::from($name, $mode, $side);
2492           continue;
2493
2494         case 'civicrm_entity_tag':
2495           $from .= " $side JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
2496                                                               civicrm_entity_tag.entity_id = contact_a.id ) ";
2497           continue;
2498
2499         case 'civicrm_note':
2500           $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND
2501                                                         contact_a.id = civicrm_note.entity_id ) ";
2502           continue;
2503
2504         case 'civicrm_subscription_history':
2505           $from .= " $side JOIN civicrm_subscription_history
2506                                    ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id
2507                                   AND civicrm_group_contact.group_id =  civicrm_subscription_history.group_id";
2508           continue;
2509
2510         case 'civicrm_relationship':
2511           if (self::$_relType == 'reciprocal') {
2512             if(self::$_relationshipTempTable) {
2513               // we have a temptable to join on
2514               $tbl = self::$_relationshipTempTable;
2515               $from .= " INNER JOIN {$tbl} civicrm_relationship ON civicrm_relationship.contact_id = contact_a.id";
2516             }
2517             else {
2518               $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id)";
2519               $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)";
2520             }
2521           }
2522           elseif (self::$_relType == 'b') {
2523             $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id )";
2524             $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )";
2525           }
2526           else {
2527             $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id )";
2528             $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )";
2529           }
2530           continue;
2531
2532         case 'civicrm_log':
2533           $from .= " INNER JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact')";
2534           $from .= " INNER JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id)";
2535           continue;
2536
2537         case 'civicrm_tag':
2538           $from .= " $side  JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id ";
2539           continue;
2540
2541         case 'civicrm_grant':
2542           $from .= CRM_Grant_BAO_Query::from($name, $mode, $side);
2543           continue;
2544
2545         case 'civicrm_website':
2546           $from .= " $side JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id ";
2547           continue;
2548
2549         default:
2550           $locationTypeName = '';
2551           if (strpos($name, '-address') != 0) {
2552             $locationTypeName = 'address';
2553           }
2554           elseif (strpos($name, '-phone') != 0) {
2555             $locationTypeName = 'phone';
2556           }
2557           elseif (strpos($name, '-email') != 0) {
2558             $locationTypeName = 'email';
2559           }
2560           if($locationTypeName) {
2561             //we have a join on an location table - possibly in conjunction with search builder - CRM-14263
2562             $parts = explode('-', $name);
2563             $locationID = array_search($parts[0], CRM_Core_BAO_Address::buildOptions('location_type_id', 'get', array('name' => $parts[0])));
2564             $from .= " $side JOIN civicrm_{$locationTypeName} `{$name}` ON ( contact_a.id = `{$name}`.contact_id ) and `{$name}`.location_type_id = $locationID ";
2565           }
2566           else {
2567             $from .= CRM_Core_Component::from($name, $mode, $side);
2568           }
2569           $from .= CRM_Contact_BAO_Query_Hook::singleton()->buildSearchfrom($name, $mode, $side);
2570
2571           continue;
2572       }
2573     }
2574     return $from;
2575   }
2576
2577   /**
2578    * WHERE / QILL clause for deleted_contacts
2579    *
2580    * @param $values
2581    *
2582    * @return void
2583    */
2584   function deletedContacts($values) {
2585     list($_, $_, $value, $grouping, $_) = $values;
2586     if ($value) {
2587       // *prepend* to the relevant grouping as this is quite an important factor
2588       array_unshift($this->_qill[$grouping], ts('Search in Trash'));
2589     }
2590   }
2591
2592   /**
2593    * where / qill clause for contact_type
2594    *
2595    * @param $values
2596    *
2597    * @return void
2598    * @access public
2599    */
2600   function contactType(&$values) {
2601     list($name, $op, $value, $grouping, $wildcard) = $values;
2602
2603     $subTypes = array();
2604     $clause = array();
2605
2606     // account for search builder mapping multiple values
2607     if (!is_array($value)) {
2608       $values = self::parseSearchBuilderString($value, 'String');
2609       if (is_array($values)) {
2610         $value = array_flip($values);
2611       }
2612     }
2613
2614     if (is_array($value)) {
2615       foreach ($value as $k => $v) {
2616         // fix for CRM-771
2617         if ($k) {
2618           $subType = NULL;
2619           $contactType = $k;
2620           if (strpos($k, CRM_Core_DAO::VALUE_SEPARATOR)) {
2621             list($contactType, $subType) = explode(CRM_Core_DAO::VALUE_SEPARATOR, $k, 2);
2622           }
2623
2624           if (!empty($subType)) {
2625             $subTypes[$subType] = 1;
2626           }
2627           $clause[$contactType] = "'" . CRM_Utils_Type::escape($contactType, 'String') . "'";
2628         }
2629       }
2630     }
2631     else {
2632       $contactTypeANDSubType = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value, 2);
2633       $contactType = $contactTypeANDSubType[0];
2634       $subType = CRM_Utils_Array::value(1, $contactTypeANDSubType);
2635       if (!empty($subType)) {
2636         $subTypes[$subType] = 1;
2637       }
2638       $clause[$contactType] = "'" . CRM_Utils_Type::escape($contactType, 'String') . "'";
2639     }
2640
2641     // fix for CRM-771
2642     if (!empty($clause)) {
2643       $quill = $clause;
2644       if ($op == 'IN' || $op == 'NOT IN') {
2645         $this->_where[$grouping][] = "contact_a.contact_type $op (" . implode(',', $clause) . ')';
2646       }
2647       else {
2648         $type = array_pop($clause);
2649         $this->_where[$grouping][] = "contact_a.contact_type $op $type";
2650       }
2651
2652       $this->_qill[$grouping][] = ts('Contact Type') . ' - ' . implode(' ' . ts('or') . ' ', $quill);
2653
2654       if (!empty($subTypes)) {
2655         $this->includeContactSubTypes($subTypes, $grouping);
2656       }
2657     }
2658   }
2659
2660   /**
2661    * where / qill clause for contact_sub_type
2662    *
2663    * @param $values
2664    *
2665    * @return void
2666    * @access public
2667    */
2668   function contactSubType(&$values) {
2669     list($name, $op, $value, $grouping, $wildcard) = $values;
2670     $this->includeContactSubTypes($value, $grouping);
2671   }
2672
2673   /**
2674    * @param $value
2675    * @param $grouping
2676    */
2677   function includeContactSubTypes($value, $grouping) {
2678
2679     $clause = array();
2680     $alias = "contact_a.contact_sub_type";
2681
2682     if (is_array($value)) {
2683       foreach ($value as $k => $v) {
2684         if (!empty($k)) {
2685           $clause[$k] = "($alias like '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($k, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')";
2686         }
2687       }
2688     }
2689     else {
2690       $clause[$value] = "($alias like '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($value, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')";
2691     }
2692
2693     if (!empty($clause)) {
2694       $this->_where[$grouping][] = "( " . implode(' OR ', $clause) . " )";
2695       $this->_qill[$grouping][] = ts('Contact Subtype') . ' - ' . implode(' ' . ts('or') . ' ', array_keys($clause));
2696     }
2697   }
2698
2699   /**
2700    * where / qill clause for groups
2701    *
2702    * @param $values
2703    *
2704    * @return void
2705    * @access public
2706    */
2707   function group(&$values) {
2708     list($name, $op, $value, $grouping, $wildcard) = $values;
2709
2710     if (count($value) > 1) {
2711       $this->_useDistinct = TRUE;
2712     }
2713
2714     $groupNames = CRM_Core_PseudoConstant::group();
2715     $groupIds = implode(',', array_keys($value));
2716
2717     $names = array();
2718     foreach ($value as $id => $dontCare) {
2719       if (array_key_exists($id, $groupNames) && $dontCare) {
2720         $names[] = $groupNames[$id];
2721       }
2722     }
2723
2724     $statii = array();
2725     $in = FALSE;
2726     $gcsValues = &$this->getWhereValues('group_contact_status', $grouping);
2727     if ($gcsValues &&
2728       is_array($gcsValues[2])
2729     ) {
2730       foreach ($gcsValues[2] as $k => $v) {
2731         if ($v) {
2732           if ($k == 'Added') {
2733             $in = TRUE;
2734           }
2735           $statii[] = "'" . CRM_Utils_Type::escape($k, 'String') . "'";
2736         }
2737       }
2738     }
2739     else {
2740       $statii[] = '"Added"';
2741       $in = TRUE;
2742     }
2743
2744     $skipGroup = FALSE;
2745     if (count($value) == 1 &&
2746       count($statii) == 1 &&
2747       $statii[0] == '"Added"'
2748     ) {
2749       // check if smart group, if so we can get rid of that one additional
2750       // left join
2751       $groupIDs = array_keys($value);
2752
2753       if (!empty($groupIDs[0]) &&
2754         CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Group',
2755           $groupIDs[0],
2756           'saved_search_id'
2757         )
2758       ) {
2759         $skipGroup = TRUE;
2760       }
2761     }
2762
2763     if (!$skipGroup) {
2764       $gcTable = "`civicrm_group_contact-{$groupIds}`";
2765       $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 ) )";
2766     }
2767
2768     $qill = ts('Contacts %1', array(1 => $op));
2769     $qill .= ' ' . implode(' ' . ts('or') . ' ', $names);
2770
2771     $groupClause = NULL;
2772
2773     if (!$skipGroup) {
2774       $groupClause = "{$gcTable}.group_id $op ( $groupIds )";
2775       if (!empty($statii)) {
2776         $groupClause .= " AND {$gcTable}.status IN (" . implode(', ', $statii) . ")";
2777         $qill .= " " . ts('AND') . " " . ts('Group Status') . ' - ' . implode(' ' . ts('or') . ' ', $statii);
2778       }
2779     }
2780
2781     if ($in) {
2782       $ssClause = $this->savedSearch($values);
2783       if ($ssClause) {
2784         if ($groupClause) {
2785           $groupClause = "( ( $groupClause ) OR ( $ssClause ) )";
2786         }
2787         else {
2788           $groupClause = $ssClause;
2789         }
2790       }
2791     }
2792
2793     $this->_where[$grouping][] = $groupClause;
2794     $this->_qill[$grouping][] = $qill;
2795   }
2796   /*
2797    * Function translates selection of group type into a list of groups
2798    */
2799   /**
2800    * @param $value
2801    *
2802    * @return array
2803    */
2804   function getGroupsFromTypeCriteria($value){
2805     $groupIds = array();
2806     foreach ($value as $groupTypeValue) {
2807       $groupList = CRM_Core_PseudoConstant::group($groupTypeValue);
2808       $groupIds = ($groupIds + $groupList);
2809     }
2810     return $groupIds;
2811   }
2812
2813   /**
2814    * where / qill clause for smart groups
2815    *
2816    * @param $values
2817    *
2818    * @return string|NULL
2819    * @access public
2820    */
2821   function savedSearch(&$values) {
2822     list($name, $op, $value, $grouping, $wildcard) = $values;
2823     return $this->addGroupContactCache(array_keys($value));
2824   }
2825
2826   /**
2827    * @param array  $groups
2828    * @param string $tableAlias
2829    * @param string $joinTable
2830    *
2831    * @return null|string
2832    */
2833   function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a") {
2834     $config = CRM_Core_Config::singleton();
2835
2836     // Find all the groups that are part of a saved search.
2837     $groupIDs = implode(',', $groups);
2838     if (empty($groupIDs)) {
2839       return NULL;
2840     }
2841
2842     $sql = "
2843 SELECT id, cache_date, saved_search_id, children
2844 FROM   civicrm_group
2845 WHERE  id IN ( $groupIDs )
2846   AND  ( saved_search_id != 0
2847    OR    saved_search_id IS NOT NULL
2848    OR    children IS NOT NULL )
2849 ";
2850
2851     $group          = CRM_Core_DAO::executeQuery($sql);
2852     $groupsFiltered = array();
2853
2854     while ($group->fetch()) {
2855       $groupsFiltered[] = $group->id;
2856
2857       $this->_useDistinct = TRUE;
2858
2859       if (!$this->_smartGroupCache || $group->cache_date == NULL) {
2860         CRM_Contact_BAO_GroupContactCache::load($group);
2861       }
2862     }
2863
2864     if (count($groupsFiltered)) {
2865       $groupIDsFiltered = implode(',', $groupsFiltered);
2866
2867       if ($tableAlias == NULL) {
2868         $tableAlias = "civicrm_group_contact_cache_{$groupIDsFiltered}";
2869       }
2870
2871       $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache `{$tableAlias}` ON {$joinTable}.id = `{$tableAlias}`.contact_id ";
2872
2873       return "`{$tableAlias}`.group_id IN (" . $groupIDsFiltered . ")";
2874     }
2875
2876     return NULL;
2877   }
2878
2879   /**
2880    * where / qill clause for cms users
2881    *
2882    * @param $values
2883    *
2884    * @return void
2885    * @access public
2886    */
2887   function ufUser(&$values) {
2888     list($name, $op, $value, $grouping, $wildcard) = $values;
2889
2890     if ($value == 1) {
2891       $this->_tables['civicrm_uf_match'] = $this->_whereTables['civicrm_uf_match'] = ' INNER JOIN civicrm_uf_match ON civicrm_uf_match.contact_id = contact_a.id ';
2892
2893       $this->_qill[$grouping][] = ts('CMS User');
2894     }
2895     elseif ($value == 0) {
2896       $this->_tables['civicrm_uf_match'] = $this->_whereTables['civicrm_uf_match'] = ' LEFT JOIN civicrm_uf_match ON civicrm_uf_match.contact_id = contact_a.id ';
2897
2898       $this->_where[$grouping][] = " civicrm_uf_match.contact_id IS NULL";
2899       $this->_qill[$grouping][] = ts('Not a CMS User');
2900     }
2901   }
2902
2903   /**
2904    * all tag search specific
2905    *
2906    * @param $values
2907    *
2908    * @return void
2909    * @access public
2910    */
2911   function tagSearch(&$values) {
2912     list($name, $op, $value, $grouping, $wildcard) = $values;
2913
2914     $op = "LIKE";
2915     $value = "%{$value}%";
2916
2917
2918     $useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
2919     $tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
2920
2921     $etTable = "`civicrm_entity_tag-" . $value . "`";
2922     $tTable = "`civicrm_tag-" . $value . "`";
2923
2924     if ($useAllTagTypes[2]) {
2925       $this->_tables[$etTable] =
2926         $this->_whereTables[$etTable] =
2927         " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id)
2928           LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id  )";
2929
2930       // search tag in cases
2931       $etCaseTable = "`civicrm_entity_case_tag-" . $value . "`";
2932       $tCaseTable = "`civicrm_case_tag-" . $value . "`";
2933       $this->_tables[$etCaseTable] =
2934         $this->_whereTables[$etCaseTable] =
2935         " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
2936           LEFT JOIN civicrm_case
2937             ON (civicrm_case_contact.case_id = civicrm_case.id
2938                 AND civicrm_case.is_deleted = 0 )
2939           LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id )
2940           LEFT JOIN civicrm_tag {$tCaseTable} ON ( {$etCaseTable}.tag_id = {$tCaseTable}.id  )";
2941       // search tag in activities
2942       $etActTable = "`civicrm_entity_act_tag-" . $value . "`";
2943       $tActTable = "`civicrm_act_tag-" . $value . "`";
2944       $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
2945       $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
2946
2947       $this->_tables[$etActTable] =
2948         $this->_whereTables[$etActTable] =
2949         " LEFT JOIN civicrm_activity_contact
2950             ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
2951           LEFT JOIN civicrm_activity
2952             ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
2953             AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )
2954           LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id )
2955           LEFT JOIN civicrm_tag {$tActTable} ON ( {$etActTable}.tag_id = {$tActTable}.id  )";
2956
2957       $this->_where[$grouping][] = "({$tTable}.name $op '". $value . "' OR {$tCaseTable}.name $op '". $value . "' OR {$tActTable}.name $op '". $value . "')";
2958       $this->_qill[$grouping][] = ts('Tag %1 %2 ', array(1 => $tagTypesText[2], 2 => $op)) . ' ' . $value;
2959     } else {
2960       $etTable = "`civicrm_entity_tag-" . $value . "`";
2961       $tTable = "`civicrm_tag-" . $value . "`";
2962       $this->_tables[$etTable] = $this->_whereTables[$etTable] = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND
2963       {$etTable}.entity_table = 'civicrm_contact' )
2964                 LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id  ) ";
2965
2966       $this->_where[$grouping][] = self::buildClause("{$tTable}.name", $op, $value, 'String');
2967       $this->_qill[$grouping][] = ts('Tagged %1', array(1 => $op)) . ' ' . $value;
2968     }
2969   }
2970
2971   /**
2972    * where / qill clause for tag
2973    *
2974    * @param $values
2975    *
2976    * @return void
2977    * @access public
2978    */
2979   function tag(&$values) {
2980     list($name, $op, $value, $grouping, $wildcard) = $values;
2981
2982     $tagNames = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
2983     if (is_array($value)) {
2984       if (count($value) > 1) {
2985         $this->_useDistinct = TRUE;
2986       }
2987       foreach ($value as $id => $dontCare) {
2988         $names[] = CRM_Utils_Array::value($id, $tagNames);
2989       }
2990       $names = implode(' ' . ts('or') . ' ', $names);
2991       $value = implode(',', array_keys($value));
2992     }
2993     else {
2994       $names = CRM_Utils_Array::value($value, $tagNames);
2995     }
2996
2997
2998     $useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
2999     $tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
3000
3001     $etTable = "`civicrm_entity_tag-" . $value . "`";
3002
3003     if ($useAllTagTypes[2]) {
3004       $this->_tables[$etTable] =
3005         $this->_whereTables[$etTable] =
3006         " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND {$etTable}.entity_table = 'civicrm_contact') ";
3007
3008       // search tag in cases
3009       $etCaseTable = "`civicrm_entity_case_tag-" . $value . "`";
3010       $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
3011       $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
3012
3013       $this->_tables[$etCaseTable] =
3014         $this->_whereTables[$etCaseTable] =
3015         " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
3016           LEFT JOIN civicrm_case
3017             ON (civicrm_case_contact.case_id = civicrm_case.id
3018                 AND civicrm_case.is_deleted = 0 )
3019           LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id ) ";
3020       // search tag in activities
3021       $etActTable = "`civicrm_entity_act_tag-" . $value . "`";
3022       $this->_tables[$etActTable] =
3023         $this->_whereTables[$etActTable] =
3024         " LEFT JOIN civicrm_activity_contact
3025             ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
3026           LEFT JOIN civicrm_activity
3027             ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
3028             AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )
3029           LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id ) ";
3030
3031       // CRM-10338
3032       if ( in_array( $op, array( 'IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY' ) ) ) {
3033         $this->_where[$grouping][] = "({$etTable}.tag_id $op OR {$etCaseTable}.tag_id $op OR {$etActTable}.tag_id $op)";
3034       }
3035       else {
3036         $this->_where[$grouping][] = "({$etTable}.tag_id $op (". $value . ") OR {$etCaseTable}.tag_id $op (". $value . ") OR {$etActTable}.tag_id $op (". $value . "))";
3037       }
3038       $this->_qill[$grouping][] = ts('Tag %1 %2', array(1 => $op, 2 => $tagTypesText[2])) . ' ' . $names;
3039     } else {
3040       $this->_tables[$etTable] =
3041         $this->_whereTables[$etTable] =
3042         " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND {$etTable}.entity_table = 'civicrm_contact') ";
3043
3044       // CRM-10338
3045       if ( in_array( $op, array( 'IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY' ) ) ) {
3046         // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3047         $op = str_replace('EMPTY', 'NULL', $op);
3048         $this->_where[$grouping][] = "{$etTable}.tag_id $op";
3049       }
3050       else {
3051         $this->_where[$grouping][] = "{$etTable}.tag_id $op (" . $value . ')';
3052       }
3053       $this->_qill[$grouping][] = ts('Tagged %1', array( 1 => $op)) . ' ' . $names;
3054     }
3055
3056   }
3057
3058   /**
3059    * where/qill clause for notes
3060    *
3061    * @param $values
3062    *
3063    * @return void
3064    * @access public
3065    */
3066   function notes(&$values) {
3067     list($name, $op, $value, $grouping, $wildcard) = $values;
3068
3069     $noteOptionValues = $this->getWhereValues('note_option', $grouping);
3070     $noteOption = CRM_Utils_Array::value('2', $noteOptionValues, '6');
3071     $noteOption = ($name == 'note_body') ? 2 : (($name == 'note_subject') ? 3 : $noteOption);
3072
3073     $this->_useDistinct = TRUE;
3074
3075     $this->_tables['civicrm_note'] =
3076       $this->_whereTables['civicrm_note'] =
3077       " LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id ) ";
3078
3079     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
3080     $n = trim($value);
3081     $value = $strtolower(CRM_Core_DAO::escapeString($n));
3082     if ($wildcard || $op == 'LIKE') {
3083       if (strpos($value, '%') === FALSE) {
3084         $value = "%$value%";
3085       }
3086       $op = 'LIKE';
3087     }
3088     elseif ($op == 'IS NULL' || $op == 'IS NOT NULL') {
3089       $value = NULL;
3090     }
3091
3092     $label = NULL;
3093     $clauses = array();
3094     if ( $noteOption % 2 ==  0 ) {
3095       $clauses[] = self::buildClause('civicrm_note.note', $op, $value, 'String');
3096       $label = ts('Note: Body Only');
3097     }
3098     if ( $noteOption % 3 ==  0 ) {
3099       $clauses[] = self::buildClause('civicrm_note.subject', $op, $value, 'String');
3100       $label = $label ? ts('Note: Body and Subject') : ts('Note: Subject Only');
3101     }
3102     $this->_where[$grouping][] = "( " . implode(' OR ', $clauses) . " )";
3103     $this->_qill[$grouping][] = $label . " $op - '$n'";
3104   }
3105
3106   /**
3107    * @param $name
3108    * @param $op
3109    * @param $grouping
3110    *
3111    * @return bool
3112    */
3113   function nameNullOrEmptyOp($name, $op, $grouping) {
3114     switch ( $op ) {
3115       case 'IS NULL':
3116       case 'IS NOT NULL':
3117         $this->_where[$grouping][] = "contact_a.$name $op";
3118         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3119         return true;
3120
3121       case 'IS EMPTY':
3122         $this->_where[$grouping][] = "(contact_a.$name IS NULL OR contact_a.$name = '')";
3123         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3124         return true;
3125
3126       case 'IS NOT EMPTY':
3127         $this->_where[$grouping][] = "(contact_a.$name IS NOT NULL AND contact_a.$name <> '')";
3128         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3129         return true;
3130
3131       default:
3132         return false;
3133     }
3134   }
3135
3136   /**
3137    * where / qill clause for sort_name
3138    *
3139    * @param $values
3140    *
3141    * @return void
3142    * @access public
3143    */
3144   function sortName(&$values) {
3145     list($name, $op, $value, $grouping, $wildcard) = $values;
3146
3147     // handle IS NULL / IS NOT NULL / IS EMPTY / IS NOT EMPTY
3148     if ( $this->nameNullOrEmptyOp( $name, $op, $grouping ) ) {
3149       return;
3150     }
3151
3152     $newName = $name;
3153     $name = trim($value);
3154
3155     if (empty($name)) {
3156       return;
3157     }
3158
3159     $config = CRM_Core_Config::singleton();
3160
3161     $sub = array();
3162
3163     //By default, $sub elements should be joined together with OR statements (don't change this variable).
3164     $subGlue = ' OR ';
3165
3166     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
3167     $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
3168
3169     if (substr($name, 0, 1) == '"' &&
3170       substr($name, -1, 1) == '"'
3171     ) {
3172       //If name is encased in double quotes, the value should be taken to be the string in entirety and the
3173       $value = substr($name, 1, -1);
3174       $value = $strtolower(CRM_Core_DAO::escapeString($value));
3175       $wc = ($newName == 'sort_name') ? 'LOWER(contact_a.sort_name)' : 'LOWER(contact_a.display_name)';
3176       $sub[] = " ( $wc = '$value' ) ";
3177       if ($config->includeEmailInName) {
3178         $sub[] = " ( civicrm_email.email = '$value' ) ";
3179       }
3180     }
3181     elseif (strpos($name, ',') !== FALSE) {
3182       // if we have a comma in the string, search for the entire string
3183       $value = $strtolower(CRM_Core_DAO::escapeString($name));
3184       if ($wildcard) {
3185         if ($config->includeWildCardInName) {
3186           $value = "'%$value%'";
3187         }
3188         else {
3189           $value = "'$value%'";
3190         }
3191         $op = 'LIKE';
3192       }
3193       else {
3194         $value = "'$value'";
3195       }
3196       if ($newName == 'sort_name') {
3197         $wc = self::caseImportant($op) ? "LOWER(contact_a.sort_name)" : "contact_a.sort_name";
3198       }
3199       else {
3200         $wc = self::caseImportant($op) ? "LOWER(contact_a.display_name)" : "contact_a.display_name";
3201       }
3202       $sub[] = " ( $wc $op $value )";
3203       if ($config->includeNickNameInName) {
3204         $wc = self::caseImportant($op) ? "LOWER(contact_a.nick_name)" : "contact_a.nick_name";
3205         $sub[] = " ( $wc $op $value )";
3206       }
3207       if ($config->includeEmailInName) {
3208         $sub[] = " ( civicrm_email.email $op $value ) ";
3209       }
3210     }
3211     else {
3212       // the string should be treated as a series of keywords to be matched with match ANY OR
3213       // match ALL depending on Civi config settings (see CiviAdmin)
3214
3215       // The Civi configuration setting can be overridden if the string *starts* with the case
3216       // insenstive strings 'AND:' or 'OR:'TO THINK ABOUT: what happens when someone searches
3217       // for the following "AND: 'a string in quotes'"? - probably nothing - it would make the
3218       // AND OR variable reduntant because there is only one search string?
3219
3220       // Check to see if the $subGlue is overridden in the search text
3221       if (strtolower(substr($name, 0, 4)) == 'and:') {
3222         $name = substr($name, 4);
3223         $subGlue = ' AND ';
3224       }
3225       if (strtolower(substr($name, 0, 3)) == 'or:') {
3226         $name = substr($name, 3);
3227         $subGlue = ' OR ';
3228       }
3229
3230       $firstChar = substr($name, 0, 1);
3231       $lastChar = substr($name, -1, 1);
3232       $quotes = array("'", '"');
3233       if ((strlen($name) > 2) && in_array($firstChar, $quotes) &&
3234         in_array($lastChar, $quotes)
3235       ) {
3236         $name = substr($name, 1);
3237         $name = substr($name, 0, -1);
3238         $pieces = array($name);
3239       }
3240       else {
3241         $pieces = explode(' ', $name);
3242       }
3243       foreach ($pieces as $piece) {
3244         $value = $strtolower(CRM_Core_DAO::escapeString(trim($piece)));
3245         if (strlen($value)) {
3246           // Added If as a sanitization - without it, when you do an OR search, any string with
3247           // double spaces (i.e. "  ") or that has a space after the keyword (e.g. "OR: ") will
3248           // return all contacts because it will include a condition similar to "OR contact
3249           // name LIKE '%'".  It might be better to replace this with array_filter.
3250           $fieldsub = array();
3251           if ($wildcard) {
3252             if ($config->includeWildCardInName) {
3253               $value = "'%$value%'";
3254             }
3255             else {
3256               $value = "'$value%'";
3257             }
3258             $op = 'LIKE';
3259           }
3260           else {
3261             $value = "'$value'";
3262           }
3263           if ($newName == 'sort_name') {
3264             $wc = self::caseImportant($op) ? "LOWER(contact_a.sort_name)" : "contact_a.sort_name";
3265           }
3266           else {
3267             $wc = self::caseImportant($op) ? "LOWER(contact_a.display_name)" : "contact_a.display_name";
3268           }
3269           $fieldsub[] = " ( $wc $op $value )";
3270           if ($config->includeNickNameInName) {
3271             $wc = self::caseImportant($op) ? "LOWER(contact_a.nick_name)" : "contact_a.nick_name";
3272             $fieldsub[] = " ( $wc $op $value )";
3273           }
3274           if ($config->includeEmailInName) {
3275             $fieldsub[] = " ( civicrm_email.email $op $value ) ";
3276           }
3277           $sub[] = ' ( ' . implode(' OR ', $fieldsub) . ' ) ';
3278           // I seperated the glueing in two.  The first stage should always be OR because we are searching for matches in *ANY* of these fields
3279         }
3280       }
3281     }
3282
3283     $sub = ' ( ' . implode($subGlue, $sub) . ' ) ';
3284
3285     $this->_where[$grouping][] = $sub;
3286     if ($config->includeEmailInName) {
3287       $this->_tables['civicrm_email'] = $this->_whereTables['civicrm_email'] = 1;
3288       $this->_qill[$grouping][] = ts('Name or Email ') . "$op - '$name'";
3289     }
3290     else {
3291       $this->_qill[$grouping][] = ts('Name like') . " - '$name'";
3292     }
3293   }
3294
3295   /**
3296    * where / qill clause for email
3297    *
3298    * @param $values
3299    *
3300    * @return void
3301    * @access public
3302    */
3303   function email(&$values) {
3304     list($name, $op, $value, $grouping, $wildcard) = $values;
3305
3306     $n = trim($value);
3307     if ($n) {
3308       $config = CRM_Core_Config::singleton();
3309
3310       if (substr($n, 0, 1) == '"' &&
3311         substr($n, -1, 1) == '"'
3312       ) {
3313         $n = substr($n, 1, -1);
3314         $value = strtolower(CRM_Core_DAO::escapeString($n));
3315         $value = "'$value'";
3316         $op = '=';
3317       }
3318       else {
3319         $value = strtolower($n);
3320         if ($wildcard) {
3321           if (strpos($value, '%') === FALSE) {
3322             $value = "%{$value}%";
3323           }
3324           $op = 'LIKE';
3325         }
3326       }
3327       $this->_qill[$grouping][] = ts('Email') . " $op '$n'";
3328       $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, $value, 'String');
3329     }
3330     else {
3331       $this->_qill[$grouping][] = ts('Email') . " $op ";
3332       $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, NULL, 'String');
3333     }
3334
3335     $this->_tables['civicrm_email'] = $this->_whereTables['civicrm_email'] = 1;
3336   }
3337
3338   /**
3339    * where / qill clause for phone number
3340    *
3341    * @param $values
3342    *
3343    * @return void
3344    * @access public
3345    */
3346   function phone_numeric(&$values) {
3347     list($name, $op, $value, $grouping, $wildcard) = $values;
3348     // Strip non-numeric characters; allow wildcards
3349     $number = preg_replace('/[^\d%]/', '', $value);
3350     if ($number) {
3351       if ( strpos($number, '%') === FALSE ) {
3352         $number = "%$number%";
3353       }
3354
3355       $this->_qill[$grouping][] = ts('Phone number contains') . " $number";
3356       $this->_where[$grouping][] = self::buildClause('civicrm_phone.phone_numeric', 'LIKE', "$number", 'String');
3357       $this->_tables['civicrm_phone'] = $this->_whereTables['civicrm_phone'] = 1;
3358     }
3359   }
3360
3361   /**
3362    * where / qill clause for phone type/location
3363    *
3364    * @param $values
3365    *
3366    * @return void
3367    * @access public
3368    */
3369   function phone_option_group($values) {
3370     list($name, $op, $value, $grouping, $wildcard) = $values;
3371     $option = ($name == 'phone_phone_type_id' ? 'phone_type_id' : 'location_type_id');
3372     $options = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', $option);
3373     $optionName = $options[$value];
3374     $this->_qill[$grouping][] = ts('Phone') . ' ' . ($name == 'phone_phone_type_id' ? ts('type') : ('location')) . " $op $optionName";
3375     $this->_where[$grouping][] = self::buildClause('civicrm_phone.' . substr($name, 6), $op, $value, 'Integer');
3376     $this->_tables['civicrm_phone'] = $this->_whereTables['civicrm_phone'] = 1;
3377   }
3378
3379   /**
3380    * where / qill clause for street_address
3381    *
3382    * @param $values
3383    *
3384    * @return void
3385    * @access public
3386    */
3387   function street_address(&$values) {
3388     list($name, $op, $value, $grouping, $wildcard) = $values;
3389
3390     if (!$op) {
3391       $op = 'LIKE';
3392     }
3393
3394     $n = trim($value);
3395
3396     if ($n) {
3397       $value = strtolower($n);
3398       if (strpos($value, '%') === FALSE) {
3399         // only add wild card if not there
3400         $value = "%{$value}%";
3401       }
3402       $op = 'LIKE';
3403       $this->_where[$grouping][] = self::buildClause('LOWER(civicrm_address.street_address)', $op, $value, 'String');
3404       $this->_qill[$grouping][] = ts('Street') . " $op '$n'";
3405     }
3406     else {
3407       $this->_where[$grouping][] = self::buildClause('civicrm_address.street_address', $op, NULL, 'String');
3408       $this->_qill[$grouping][] = ts('Street') . " $op ";
3409     }
3410
3411     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3412   }
3413
3414   /**
3415    * where / qill clause for street_unit
3416    *
3417    * @param $values
3418    *
3419    * @return void
3420    * @access public
3421    */
3422   function street_number(&$values) {
3423     list($name, $op, $value, $grouping, $wildcard) = $values;
3424
3425     if (!$op) {
3426       $op = '=';
3427     }
3428
3429     $n = trim($value);
3430
3431     if (strtolower($n) == 'odd') {
3432       $this->_where[$grouping][] = " ( civicrm_address.street_number % 2 = 1 )";
3433       $this->_qill[$grouping][] = ts('Street Number is odd');
3434     }
3435     elseif (strtolower($n) == 'even') {
3436       $this->_where[$grouping][] = " ( civicrm_address.street_number % 2 = 0 )";
3437       $this->_qill[$grouping][] = ts('Street Number is even');
3438     }
3439     else {
3440       $value = strtolower($n);
3441
3442       $this->_where[$grouping][] = self::buildClause('LOWER(civicrm_address.street_number)', $op, $value, 'String');
3443       $this->_qill[$grouping][] = ts('Street Number') . " $op '$n'";
3444     }
3445
3446     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3447   }
3448
3449   /**
3450    * where / qill clause for sorting by character
3451    *
3452    * @param $values
3453    *
3454    * @return void
3455    * @access public
3456    */
3457   function sortByCharacter(&$values) {
3458     list($name, $op, $value, $grouping, $wildcard) = $values;
3459
3460     $name = trim($value);
3461     $cond = " contact_a.sort_name LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($name)) . "%'";
3462     $this->_where[$grouping][] = $cond;
3463     $this->_qill[$grouping][] = ts('Showing only Contacts starting with: \'%1\'', array(1 => $name));
3464   }
3465
3466   /**
3467    * where / qill clause for including contact ids
3468    *
3469    * @return void
3470    * @access public
3471    */
3472   function includeContactIDs() {
3473     if (!$this->_includeContactIds || empty($this->_params)) {
3474       return;
3475     }
3476
3477     $contactIds = array();
3478     foreach ($this->_params as $id => $values) {
3479       if (substr($values[0], 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) {
3480         $contactIds[] = substr($values[0], CRM_Core_Form::CB_PREFIX_LEN);
3481       }
3482     }
3483     if (!empty($contactIds)) {
3484       $this->_where[0][] = " ( contact_a.id IN (" . implode(',', $contactIds) . " ) ) ";
3485     }
3486   }
3487
3488   /**
3489    * where / qill clause for postal code
3490    *
3491    * @param $values
3492    *
3493    * @return void
3494    * @access public
3495    */
3496   function postalCode(&$values) {
3497     // skip if the fields dont have anything to do with postal_code
3498     if (empty($this->_fields['postal_code'])) {
3499       return;
3500     }
3501
3502     list($name, $op, $value, $grouping, $wildcard) = $values;
3503
3504     // Handle numeric postal code range searches properly by casting the column as numeric
3505     if (is_numeric($value)) {
3506       $field = 'ROUND(civicrm_address.postal_code)';
3507       $val = CRM_Utils_Type::escape($value, 'Integer');
3508     }
3509     else {
3510       $field = 'civicrm_address.postal_code';
3511       $val = CRM_Utils_Type::escape($value, 'String');
3512     }
3513
3514     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3515
3516     if ($name == 'postal_code') {
3517       $this->_where[$grouping][] = self::buildClause($field, $op, $val, 'String');
3518       $this->_qill[$grouping][] = ts('Postal code') . " {$op} {$value}";
3519     }
3520     elseif ($name == 'postal_code_low') {
3521       $this->_where[$grouping][] = " ( $field >= '$val' ) ";
3522       $this->_qill[$grouping][] = ts('Postal code greater than or equal to \'%1\'', array(1 => $value));
3523     }
3524     elseif ($name == 'postal_code_high') {
3525       $this->_where[$grouping][] = " ( $field <= '$val' ) ";
3526       $this->_qill[$grouping][] = ts('Postal code less than or equal to \'%1\'', array(1 => $value));
3527     }
3528   }
3529
3530   /**
3531    * where / qill clause for location type
3532    *
3533    * @param $values
3534    * @param null $status
3535    *
3536    * @return void
3537    * @access public
3538    */
3539   function locationType(&$values, $status = NULL) {
3540     list($name, $op, $value, $grouping, $wildcard) = $values;
3541
3542     if (is_array($value)) {
3543       $this->_where[$grouping][] = 'civicrm_address.location_type_id IN (' . implode(',', array_keys($value)) . ')';
3544       $this->_tables['civicrm_address'] = 1;
3545       $this->_whereTables['civicrm_address'] = 1;
3546
3547       $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
3548       $names = array();
3549       foreach (array_keys($value) as $id) {
3550         $names[] = $locationType[$id];
3551       }
3552
3553       $this->_primaryLocation = FALSE;
3554
3555       if (!$status) {
3556         $this->_qill[$grouping][] = ts('Location Type') . ' - ' . implode(' ' . ts('or') . ' ', $names);
3557       }
3558       else {
3559         return implode(' ' . ts('or') . ' ', $names);
3560       }
3561     }
3562   }
3563
3564   /**
3565    * @param $values
3566    * @param bool $fromStateProvince
3567    *
3568    * @return array
3569    */
3570   function country(&$values, $fromStateProvince = TRUE) {
3571     list($name, $op, $value, $grouping, $wildcard) = $values;
3572
3573     if (!$fromStateProvince) {
3574       $stateValues = $this->getWhereValues('state_province', $grouping);
3575       if (!empty($stateValues)) {
3576         // return back to caller if there are state province values
3577         // since that handles this case
3578         return;
3579       }
3580     }
3581
3582     $countryClause = $countryQill = NULL;
3583     if (
3584       $values &&
3585       !empty($value)
3586     ) {
3587
3588       $this->_tables['civicrm_address'] = 1;
3589       $this->_whereTables['civicrm_address'] = 1;
3590
3591       $countries = CRM_Core_PseudoConstant::country();
3592       if (is_numeric($value)) {
3593         $countryClause = self::buildClause(
3594           'civicrm_address.country_id',
3595           $op,
3596           $value,
3597           'Positive'
3598         );
3599         $countryName = $countries[(int ) $value];
3600       }
3601
3602       else {
3603         $intValues = self::parseSearchBuilderString($value);
3604         if ($intValues && ($op == 'IN' || $op == 'NOT IN')) {
3605           $countryClause = self::buildClause(
3606             'civicrm_address.country_id',
3607             $op,
3608             $intValues,
3609             'Positive'
3610           );
3611           $countryNames = array();
3612           foreach ($intValues as $v) {
3613             $countryNames[] = $countries[$v];
3614           }
3615           $countryName = implode(',', $countryNames);
3616         }
3617         else {
3618           $countries = CRM_Core_PseudoConstant::country();
3619           $intVal = CRM_Utils_Array::key($value, $countries);
3620           $countryClause = self::buildClause(
3621             'civicrm_address.country_id',
3622             $op,
3623             $intVal,
3624             'Integer'
3625           );
3626           $countryName = $value;
3627         }
3628       }
3629       $countryQill = ts('Country') . " {$op} '$countryName'";
3630
3631       if (!$fromStateProvince) {
3632         $this->_where[$grouping][] = $countryClause;
3633         $this->_qill[$grouping][] = $countryQill;
3634       }
3635     }
3636
3637     if ($fromStateProvince) {
3638       if (!empty($countryClause)) {
3639         return array(
3640           $countryClause,
3641           " ...AND... " . $countryQill,
3642         );
3643       }
3644       else {
3645         return array(NULL, NULL);
3646       }
3647     }
3648   }
3649
3650   /**
3651    * where / qill clause for county (if present)
3652    *
3653    * @param $values
3654    * @param null $status
3655    *
3656    * @return void
3657    * @access public
3658    */
3659   function county(&$values, $status = null) {
3660     list($name, $op, $value, $grouping, $wildcard) = $values;
3661
3662     if (! is_array($value)) {
3663       // force the county to be an array
3664       $value = array($value);
3665     }
3666
3667     // check if the values are ids OR names of the counties
3668     $inputFormat = 'id';
3669     foreach ($value as $v) {
3670       if (!is_numeric($v)) {
3671         $inputFormat = 'name';
3672         break;
3673       }
3674     }
3675     $names = array();
3676     if ($op == '=') {
3677       $op = 'IN';
3678     }
3679     else if ($op == '!=') {
3680       $op = 'NOT IN';
3681     }
3682     else {
3683       // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3684       $op = str_replace('EMPTY', 'NULL', $op);
3685     }
3686
3687     if (in_array( $op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3688       $clause = "civicrm_address.county_id $op";
3689     }
3690     elseif ($inputFormat == 'id') {
3691       $clause = 'civicrm_address.county_id IN (' . implode(',', $value) . ')';
3692
3693       $county = CRM_Core_PseudoConstant::county();
3694       foreach ($value as $id) {
3695         $names[] = CRM_Utils_Array::value($id, $county);
3696       }
3697     }
3698     else {
3699       $inputClause = array();
3700       $county = CRM_Core_PseudoConstant::county();
3701       foreach ($value as $name) {
3702         $name = trim($name);
3703         $inputClause[] = CRM_Utils_Array::key($name, $county);
3704       }
3705       $clause = 'civicrm_address.county_id IN (' . implode(',', $inputClause) . ')';
3706       $names = $value;
3707     }
3708     $this->_tables['civicrm_address'] = 1;
3709     $this->_whereTables['civicrm_address'] = 1;
3710
3711     $this->_where[$grouping][] = $clause;
3712     if (!$status) {
3713       $this->_qill[$grouping][] = ts('County') . ' - ' . implode(' ' . ts('or') . ' ', $names);
3714     } else {
3715       return implode(' ' . ts('or') . ' ', $names);
3716     }
3717   }
3718
3719   /**
3720    * where / qill clause for state/province AND country (if present)
3721    *
3722    * @param $values
3723    * @param null $status
3724    *
3725    * @return void
3726    * @access public
3727    */
3728   function stateProvince(&$values, $status = NULL) {
3729     list($name, $op, $value, $grouping, $wildcard) = $values;
3730
3731     // quick escape for IS NULL
3732     if ( in_array( $op, array( 'IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY' ) ) ) {
3733       $value = NULL;
3734     }
3735     else if (!is_array($value)) {
3736       // force the state to be an array
3737       // check if its in the mapper format!
3738       $values = self::parseSearchBuilderString($value);
3739       if (is_array($values)) {
3740         $value = $values;
3741       }
3742       else {
3743         $value = array($value);
3744       }
3745     }
3746
3747     // check if the values are ids OR names of the states
3748     $inputFormat = 'id';
3749     if ($value) {
3750       foreach ($value as $v) {
3751         if (!is_numeric($v)) {
3752           $inputFormat = 'name';
3753           break;
3754         }
3755       }
3756     }
3757
3758     $names = array();
3759     if ($op == '=') {
3760       $op = 'IN';
3761     }
3762     else if ($op == '!=') {
3763       $op = 'NOT IN';
3764     }
3765     else {
3766       // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3767       $op = str_replace('EMPTY', 'NULL', $op);
3768     }
3769     if ( in_array( $op, array( 'IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY' ) ) ) {
3770       $stateClause = "civicrm_address.state_province_id $op";
3771     }
3772     else if ($inputFormat == 'id') {
3773       if ($op != 'NOT IN') {
3774         $op = 'IN';
3775       }
3776       $stateClause = "civicrm_address.state_province_id $op (" . implode(',', $value) . ')';
3777
3778       $stateProvince = CRM_Core_PseudoConstant::stateProvince();
3779       foreach ($value as $id) {
3780         $names[] = CRM_Utils_Array::value($id, $stateProvince);
3781       }
3782     }
3783     else {
3784       $inputClause = array();
3785       $stateProvince = CRM_Core_PseudoConstant::stateProvince();
3786       foreach ($value as $name) {
3787         $name = trim($name);
3788         $inputClause[] = CRM_Utils_Array::key($name, $stateProvince);
3789       }
3790       $stateClause = "civicrm_address.state_province_id $op (" . implode(',', $inputClause) . ')';
3791       $names = $value;
3792     }
3793     $this->_tables['civicrm_address'] = 1;
3794     $this->_whereTables['civicrm_address'] = 1;
3795
3796     $countryValues = $this->getWhereValues('country', $grouping);
3797     list($countryClause, $countryQill) = $this->country($countryValues, TRUE);
3798
3799     if ($countryClause) {
3800       $clause = "( $stateClause AND $countryClause )";
3801     }
3802     else {
3803       $clause = $stateClause;
3804     }
3805
3806     $this->_where[$grouping][] = $clause;
3807     if (!$status) {
3808       $this->_qill[$grouping][] = ts('State/Province') . " $op " . implode(' ' . ts('or') . ' ', $names) . $countryQill;
3809     }
3810     else {
3811       return implode(' ' . ts('or') . ' ', $names) . $countryQill;
3812     }
3813   }
3814
3815   /**
3816    * where / qill clause for change log
3817    *
3818    * @param $values
3819    *
3820    * @return void
3821    * @access public
3822    */
3823   function changeLog(&$values) {
3824     list($name, $op, $value, $grouping, $wildcard) = $values;
3825
3826     $targetName = $this->getWhereValues('changed_by', $grouping);
3827     if (!$targetName) {
3828       return;
3829     }
3830
3831     $name = trim($targetName[2]);
3832     $name = strtolower(CRM_Core_DAO::escapeString($name));
3833     $name = $targetName[4] ? "%$name%" : $name;
3834     $this->_where[$grouping][] = "contact_b_log.sort_name LIKE '%$name%'";
3835     $this->_tables['civicrm_log'] = $this->_whereTables['civicrm_log'] = 1;
3836     $this->_qill[$grouping][] = ts('Modified by') . ": $name";
3837   }
3838
3839   /**
3840    * @param $values
3841    */
3842   function modifiedDates($values) {
3843     $this->_useDistinct = TRUE;
3844
3845     // CRM-11281, default to added date if not set
3846     $fieldTitle = ts('Added Date');
3847     $fieldName = 'created_date';
3848     foreach (array_keys($this->_params) as $id) {
3849       if ($this->_params[$id][0] == 'log_date') {
3850         if ($this->_params[$id][2] == 2) {
3851           $fieldTitle = ts('Modified Date');
3852           $fieldName = 'modified_date';
3853         }
3854       }
3855     }
3856
3857
3858     $this->dateQueryBuilder($values, 'contact_a', 'log_date', $fieldName, $fieldTitle);
3859
3860     self::$_openedPanes[ts('Change Log')] = TRUE;
3861   }
3862
3863   /**
3864    * @param $values
3865    */
3866   function demographics(&$values) {
3867     list($name, $op, $value, $grouping, $wildcard) = $values;
3868
3869     if (($name == 'birth_date_low') || ($name == 'birth_date_high')) {
3870
3871       $this->dateQueryBuilder($values,
3872         'contact_a', 'birth_date', 'birth_date', ts('Birth Date')
3873       );
3874     }
3875     elseif (($name == 'deceased_date_low') || ($name == 'deceased_date_high')) {
3876
3877       $this->dateQueryBuilder($values,
3878         'contact_a', 'deceased_date', 'deceased_date', ts('Deceased Date')
3879       );
3880     }
3881
3882     self::$_openedPanes[ts('Demographics')] = TRUE;
3883   }
3884
3885   /**
3886    * @param $values
3887    */
3888   function privacy(&$values) {
3889     list($name, $op, $value, $grouping, $wildcard) = $values;
3890     //fixed for profile search listing CRM-4633
3891     if (strpbrk($value, "[")) {
3892       $value = "'{$value}'";
3893       $op = "!{$op}";
3894       $this->_where[$grouping][] = "contact_a.{$name} $op $value";
3895     }
3896     else {
3897       $this->_where[$grouping][] = "contact_a.{$name} $op $value";
3898     }
3899     $field = CRM_Utils_Array::value($name, $this->_fields);
3900     $title = $field ? $field['title'] : $name;
3901     $this->_qill[$grouping][] = "$title $op $value";
3902   }
3903
3904   /**
3905    * @param $values
3906    */
3907   function privacyOptions($values) {
3908     list($name, $op, $value, $grouping, $wildcard) = $values;
3909
3910     if (empty($value) || !is_array($value)) {
3911       return;
3912     }
3913
3914     // get the operator and toggle values
3915     $opValues = $this->getWhereValues('privacy_operator', $grouping);
3916     $operator = 'OR';
3917     if ($opValues &&
3918       strtolower($opValues[2] == 'AND')
3919     ) {
3920       $operator = 'AND';
3921     }
3922
3923     $toggleValues = $this->getWhereValues('privacy_toggle', $grouping);
3924     $compareOP = '!=';
3925     if ($toggleValues &&
3926       $toggleValues[2] == 2
3927     ) {
3928       $compareOP = '=';
3929     }
3930
3931     $clauses = array();
3932     $qill = array();
3933     foreach ($value as $dontCare => $pOption) {
3934       $clauses[] = " ( contact_a.{$pOption} $compareOP 1 ) ";
3935       $field = CRM_Utils_Array::value($pOption, $this->_fields);
3936       $title = $field ? $field['title'] : $pOption;
3937       $qill[] = " $title $compareOP 1 ";
3938     }
3939
3940     $this->_where[$grouping][] = '( ' . implode($operator, $clauses) . ' )';
3941     $this->_qill[$grouping][] = implode($operator, $qill);
3942   }
3943
3944   /**
3945    * @param $values
3946    */
3947   function preferredCommunication(&$values) {
3948     list($name, $op, $value, $grouping, $wildcard) = $values;
3949
3950     $pref = array();
3951     if (in_array($op, array( 'IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3952       $value = NULL;
3953     }
3954     elseif (!is_array($value)) {
3955       $v = array();
3956       $value = trim($value, ' ()');
3957       if (strpos($value, CRM_Core_DAO::VALUE_SEPARATOR) !== FALSE) {
3958         $v = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
3959       }
3960       else {
3961         $v = explode(",", $value);
3962       }
3963
3964       foreach ($v as $item) {
3965         if ($item) {
3966           $pref[] = $item;
3967         }
3968       }
3969     }
3970     else {
3971       foreach ($value as $key => $checked) {
3972         if ($checked) {
3973           $pref[] = $key;
3974         }
3975       }
3976     }
3977
3978     $commPref = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'preferred_communication_method');
3979
3980     $sqlValue = array();
3981     $showValue = array();
3982     $sql = "contact_a.preferred_communication_method";
3983     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3984       $sqlValue[] = "{$sql} {$op}";
3985     }
3986     else {
3987       foreach ($pref as $val) {
3988         $sqlValue[] = "( $sql like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $val . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
3989         $showValue[] = $commPref[$val];
3990       }
3991     }
3992     $this->_where[$grouping][] = "( " . implode(' OR ', $sqlValue) . " )";
3993     $this->_qill[$grouping][] = ts('Preferred Communication Method') . " $op " . implode(' ' . ts('or') . ' ', $showValue);
3994   }
3995
3996   /**
3997    * where / qill clause for relationship
3998    *
3999    * @param $values
4000    *
4001    * @return void
4002    * @access public
4003    */
4004   function relationship(&$values) {
4005     list($name, $op, $value, $grouping, $wildcard) = $values;
4006     if($this->_relationshipValuesAdded){
4007        return;
4008     }
4009     // also get values array for relation_target_name
4010     // for relatinship search we always do wildcard
4011     $targetName = $this->getWhereValues('relation_target_name', $grouping);
4012     $relStatus = $this->getWhereValues('relation_status', $grouping);
4013     $relPermission = $this->getWhereValues('relation_permission', $grouping);
4014     $targetGroup = $this->getWhereValues('relation_target_group', $grouping);
4015
4016     $nameClause = $name = NULL;
4017     if ($targetName) {
4018       $name = trim($targetName[2]);
4019       if (substr($name, 0, 1) == '"' &&
4020         substr($name, -1, 1) == '"'
4021       ) {
4022         $name = substr($name, 1, -1);
4023         $name = strtolower(CRM_Core_DAO::escapeString($name));
4024         $nameClause = "= '$name'";
4025       }
4026       else {
4027         $name = strtolower(CRM_Core_DAO::escapeString($name));
4028         $nameClause = "LIKE '%{$name}%'";
4029       }
4030     }
4031
4032     $rel = explode('_', $value);
4033
4034     self::$_relType = $rel[1];
4035     $params = array('id' => $rel[0]);
4036     $rTypeValues = array();
4037     $rType = CRM_Contact_BAO_RelationshipType::retrieve($params, $rTypeValues);
4038     if ($rTypeValues['name_a_b'] == $rTypeValues['name_b_a']) {
4039       // if we don't know which end of the relationship we are dealing with we'll create a temp table
4040       //@todo unless we are dealing with a target group
4041       self::$_relType = 'reciprocal';
4042     }
4043     // if we are creating a temp table we build our own where for the relationship table
4044     $relationshipTempTable = NULL;
4045     if(self::$_relType == 'reciprocal' && empty($targetGroup)) {
4046       $where = array();
4047       self::$_relationshipTempTable =
4048         $relationshipTempTable =
4049         CRM_Core_DAO::createTempTableName( 'civicrm_rel');
4050       if($nameClause) {
4051         $where[$grouping][] = " sort_name $nameClause ";
4052       }
4053     }
4054     else {
4055       $where = &$this->_where;
4056       if ($nameClause) {
4057         $where[$grouping][] = "( contact_b.sort_name $nameClause AND contact_b.id != contact_a.id )";
4058       }
4059     }
4060
4061
4062     $relTypeInd = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Individual');
4063     $relTypeOrg = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Organization');
4064     $relTypeHou = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Household');
4065     $allRelationshipType = array();
4066     $allRelationshipType = array_merge($relTypeInd, $relTypeOrg);
4067     $allRelationshipType = array_merge($allRelationshipType, $relTypeHou);
4068
4069     if ($nameClause || !$targetGroup) {
4070       $this->_qill[$grouping][] = "$allRelationshipType[$value]  $name";
4071     }
4072
4073
4074     //check to see if the target contact is in specified group
4075     if ($targetGroup) {
4076       //add contacts from static groups
4077       $this->_tables['civicrm_relationship_group_contact'] =
4078         $this->_whereTables['civicrm_relationship_group_contact'] =
4079         " 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'";
4080       $groupWhere[] =
4081         "( civicrm_relationship_group_contact.group_id IN  (" .
4082         implode(",", $targetGroup[2]) .  ") ) ";
4083
4084       //add contacts from saved searches
4085       $ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", "contact_b");
4086
4087       //set the group where clause
4088       if ($ssWhere) {
4089         $groupWhere[] = "( " . $ssWhere . " )";
4090       }
4091       $this->_where[$grouping][] = "( " . implode(" OR ", $groupWhere) . " )";
4092
4093       //Get the names of the target groups for the qill
4094       $groupNames = CRM_Core_PseudoConstant::group();
4095       $qillNames = array();
4096       foreach ($targetGroup[2] as $groupId) {
4097         if (array_key_exists($groupId, $groupNames)) {
4098           $qillNames[] = $groupNames[$groupId];
4099         }
4100       }
4101       $this->_qill[$grouping][] = "$allRelationshipType[$value]  ( " . implode(", ", $qillNames) . " )";
4102     }
4103
4104     // Note we do not currently set mySql to handle timezones, so doing this the old-fashioned way
4105     $today = date('Ymd');
4106     //check for active, inactive and all relation status
4107     if ($relStatus[2] == 0) {
4108       $where[$grouping][] = "(
4109 civicrm_relationship.is_active = 1 AND
4110 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
4111 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
4112 )";
4113       $this->_qill[$grouping][] = ts('Relationship - Active and Current');
4114     }
4115     elseif ($relStatus[2] == 1) {
4116       $where[$grouping][] = "(
4117 civicrm_relationship.is_active = 0 OR
4118 civicrm_relationship.end_date < {$today} OR
4119 civicrm_relationship.start_date > {$today}
4120 )";
4121       $this->_qill[$grouping][] = ts('Relationship - Inactive or not Current');
4122     }
4123
4124     //check for permissioned, non-permissioned and all permissioned relations
4125     if ($relPermission[2] == 1) {
4126       $this->_where[$grouping][] = "(
4127 civicrm_relationship.is_permission_a_b = 1
4128 )";
4129       $this->_qill[$grouping][] = ts('Relationship - Permissioned');
4130     } elseif ($relPermission[2] == 2) {
4131       //non-allowed permission relationship.
4132       $this->_where[$grouping][] = "(
4133 civicrm_relationship.is_permission_a_b = 0
4134 )";
4135       $this->_qill[$grouping][] = ts('Relationship - Non-permissioned');
4136     }
4137
4138     $this->addRelationshipDateClauses($grouping, $where);
4139     if(!empty($rType) && isset($rType->id)){
4140       $where[$grouping][] = 'civicrm_relationship.relationship_type_id = ' . $rType->id;
4141     }
4142     $this->_tables['civicrm_relationship'] = $this->_whereTables['civicrm_relationship'] = 1;
4143     $this->_useDistinct = TRUE;
4144     $this->_relationshipValuesAdded = TRUE;
4145     // it could be a or b, using an OR creates an unindexed join - better to create a temp table &
4146     // join on that,
4147     // @todo creating a temp table could be expanded to group filter
4148     // as even creating a temp table of all relationships is much much more efficient than
4149     // an OR in the join
4150     if($relationshipTempTable) {
4151       $whereClause = ' WHERE ' . implode(' AND ', $where[$grouping]);
4152       $sql = "
4153         CREATE TEMPORARY TABLE {$relationshipTempTable}
4154           (SELECT contact_id_b as contact_id, civicrm_relationship.id
4155             FROM civicrm_relationship
4156             INNER JOIN  civicrm_contact c ON civicrm_relationship.contact_id_a = c.id
4157             $whereClause )
4158           UNION
4159             (SELECT contact_id_a as contact_id, civicrm_relationship.id
4160             FROM civicrm_relationship
4161             INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_b = c.id
4162             $whereClause )
4163       ";
4164       CRM_Core_DAO::executeQuery($sql);
4165     }
4166
4167   }
4168 /**
4169  * Add start & end date criteria in
4170  * @param string $grouping
4171  * @param array $where = array to add where clauses to, in case you are generating a temp table
4172  * not the main query.
4173  */
4174   function addRelationshipDateClauses($grouping, &$where){
4175     $dateValues = array();
4176     $dateTypes = array(
4177         'start_date',
4178         'end_date',
4179     );
4180
4181     foreach ($dateTypes as $dateField){
4182       $dateValueLow = $this->getWhereValues('relation_'. $dateField .'_low', $grouping);
4183       $dateValueHigh= $this->getWhereValues('relation_'. $dateField .'_high', $grouping);
4184       if(!empty($dateValueLow)){
4185         $date = date('Ymd', strtotime($dateValueLow[2]));
4186         $where[$grouping][] = "civicrm_relationship.$dateField >= $date";
4187         $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);
4188       }
4189       if(!empty($dateValueHigh)){
4190         $date = date('Ymd', strtotime($dateValueHigh[2]));
4191         $where[$grouping][] = "civicrm_relationship.$dateField <= $date";
4192         $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);
4193       }
4194     }
4195   }
4196
4197   /**
4198    * default set of return properties
4199    *
4200    * @param int $mode
4201    *
4202    * @return array derault return properties
4203    * @access public
4204    */
4205   static function &defaultReturnProperties($mode = 1) {
4206     if (!isset(self::$_defaultReturnProperties)) {
4207       self::$_defaultReturnProperties = array();
4208     }
4209
4210     if (!isset(self::$_defaultReturnProperties[$mode])) {
4211       // add activity return properties
4212       if ($mode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
4213         self::$_defaultReturnProperties[$mode] = CRM_Activity_BAO_Query::defaultReturnProperties($mode, FALSE);
4214       }
4215       else {
4216         self::$_defaultReturnProperties[$mode] = CRM_Core_Component::defaultReturnProperties($mode, FALSE);
4217       }
4218
4219       if (empty(self::$_defaultReturnProperties[$mode])) {
4220         self::$_defaultReturnProperties[$mode] = array(
4221           'home_URL' => 1,
4222           'image_URL' => 1,
4223           'legal_identifier' => 1,
4224           'external_identifier' => 1,
4225           'contact_type' => 1,
4226           'contact_sub_type' => 1,
4227           'sort_name' => 1,
4228           'display_name' => 1,
4229           'preferred_mail_format' => 1,
4230           'nick_name' => 1,
4231           'first_name' => 1,
4232           'middle_name' => 1,
4233           'last_name' => 1,
4234           'prefix_id' => 1,
4235           'suffix_id' => 1,
4236           'formal_title' => 1,
4237           'communication_style_id' => 1,
4238           'birth_date' => 1,
4239           'gender_id' => 1,
4240           'street_address' => 1,
4241           'supplemental_address_1' => 1,
4242           'supplemental_address_2' => 1,
4243           'city' => 1,
4244           'postal_code' => 1,
4245           'postal_code_suffix' => 1,
4246           'state_province' => 1,
4247           'country' => 1,
4248           'world_region' => 1,
4249           'geo_code_1' => 1,
4250           'geo_code_2' => 1,
4251           'email' => 1,
4252           'on_hold' => 1,
4253           'phone' => 1,
4254           'im' => 1,
4255           'household_name' => 1,
4256           'organization_name' => 1,
4257           'deceased_date' => 1,
4258           'is_deceased' => 1,
4259           'job_title' => 1,
4260           'legal_name' => 1,
4261           'sic_code' => 1,
4262           'current_employer' => 1,
4263           // FIXME: should we use defaultHierReturnProperties() for the below?
4264           'do_not_email' => 1,
4265           'do_not_mail' => 1,
4266           'do_not_sms' => 1,
4267           'do_not_phone' => 1,
4268           'do_not_trade' => 1,
4269           'is_opt_out' => 1,
4270           'contact_is_deleted' => 1,
4271           'preferred_communication_method' => 1,
4272           'preferred_language' => 1,
4273         );
4274       }
4275     }
4276     return self::$_defaultReturnProperties[$mode];
4277   }
4278
4279   /**
4280    * get primary condition for a sql clause
4281    *
4282    * @param int $value
4283    *
4284    * @return string|NULL
4285    * @access public
4286    */
4287   static function getPrimaryCondition($value) {
4288     if (is_numeric($value)) {
4289       $value = (int ) $value;
4290       return ($value == 1) ? 'is_primary = 1' : 'is_primary = 0';
4291     }
4292     return NULL;
4293   }
4294
4295   /**
4296    * wrapper for a simple search query
4297    *
4298    * @param array $params
4299    * @param array $returnProperties
4300    * @param bool $count
4301    *
4302    * @return string
4303    * @access public
4304    */
4305   static function getQuery($params = NULL, $returnProperties = NULL, $count = FALSE) {
4306     $query = new CRM_Contact_BAO_Query($params, $returnProperties);
4307     list($select, $from, $where, $having) = $query->query();
4308
4309     return "$select $from $where $having";
4310   }
4311
4312   /**
4313    * These are stub comments as this function needs more explanation - particularly in terms of how it
4314    * relates to $this->searchQuery and why it replicates rather than calles $this->searchQuery.
4315    *
4316    * This function was originally written as a wrapper for the api query but is called from multiple places
4317    * in the core code directly so the name is misleading. This function does not use the searchQuery function
4318    * but it is unclear as to whehter that is historical or there is a reason
4319    *  CRM-11290 led to the permissioning action being extracted from searchQuery & shared with this function
4320    *
4321    * @param array $params
4322    * @param array $returnProperties
4323    * @param null $fields
4324    * @param string $sort
4325    * @param int $offset
4326    * @param int $row_count
4327    * @param bool $smartGroupCache
4328    * @param bool $count return count obnly
4329    * @param bool $skipPermissions Should permissions be ignored or should the logged in user's permissions be applied
4330    *
4331    * @params bool $smartGroupCache ?? update smart group cache?
4332    *
4333    * @return array
4334    * @access public
4335    */
4336   static function apiQuery(
4337     $params = NULL,
4338     $returnProperties = NULL,
4339     $fields = NULL,
4340     $sort = NULL,
4341     $offset = 0,
4342     $row_count = 25,
4343     $smartGroupCache = TRUE,
4344     $count = FALSE,
4345     $skipPermissions = TRUE
4346   ) {
4347
4348     $query = new CRM_Contact_BAO_Query(
4349       $params, $returnProperties,
4350       NULL, TRUE, FALSE, 1,
4351       $skipPermissions,
4352       TRUE, $smartGroupCache
4353     );
4354
4355    //this should add a check for view deleted if permissions are enabled
4356    if ($skipPermissions){
4357      $query->_skipDeleteClause = TRUE;
4358    }
4359     $query->generatePermissionClause(FALSE, $count);
4360
4361     // note : this modifies _fromClause and _simpleFromClause
4362     $query->includePseudoFieldsJoin($sort);
4363
4364     list($select, $from, $where, $having) = $query->query($count);
4365
4366     $options = $query->_options;
4367     if(!empty($query->_permissionWhereClause)){
4368       if (empty($where)) {
4369         $where = "WHERE $query->_permissionWhereClause";
4370       }
4371       else {
4372         $where = "$where AND $query->_permissionWhereClause";
4373       }
4374     }
4375
4376     $sql = "$select $from $where $having";
4377
4378     // add group by
4379     if ($query->_useGroupBy) {
4380       $sql .= ' GROUP BY contact_a.id';
4381     }
4382     if (!empty($sort)) {
4383       $sort = CRM_Utils_Type::escape($sort, 'String');
4384       $sql .= " ORDER BY $sort ";
4385     }
4386     if ($row_count > 0 && $offset >= 0) {
4387       $offset = CRM_Utils_Type::escape($offset, 'Int');
4388       $rowCount = CRM_Utils_Type::escape($row_count, 'Int');
4389       $sql .= " LIMIT $offset, $row_count ";
4390     }
4391
4392     $dao = CRM_Core_DAO::executeQuery($sql);
4393
4394     $values = array();
4395     while ($dao->fetch()) {
4396       if ($count) {
4397         $noRows = $dao->rowCount;
4398         $dao->free();
4399         return array($noRows,NULL);
4400       }
4401       $val = $query->store($dao);
4402       $convertedVals = $query->convertToPseudoNames($dao, TRUE);
4403
4404       if (!empty($convertedVals)) {
4405         $val = array_replace_recursive($val, $convertedVals);
4406       }
4407       $values[$dao->contact_id] = $val;
4408     }
4409     $dao->free();
4410     return array($values, $options);
4411   }
4412
4413   /**
4414    * create and query the db for an contact search
4415    *
4416    * @param int $offset the offset for the query
4417    * @param int $rowCount the number of rows to return
4418    * @param string $sort the order by string
4419    * @param boolean $count is this a count only query ?
4420    * @param boolean $includeContactIds should we include contact ids?
4421    * @param boolean $sortByChar if true returns the distinct array of first characters for search results
4422    * @param boolean $groupContacts if true, return only the contact ids
4423    * @param boolean $returnQuery should we return the query as a string
4424    * @param string $additionalWhereClause if the caller wants to further restrict the search (used for components)
4425    * @param null $sortOrder
4426    * @param string $additionalFromClause should be clause with proper joins, effective to reduce where clause load.
4427    *
4428    * @param bool $skipOrderAndLimit
4429    *
4430    * @return CRM_Core_DAO
4431    * @access public
4432    */
4433   function searchQuery(
4434     $offset = 0, $rowCount = 0, $sort = NULL,
4435     $count = FALSE, $includeContactIds = FALSE,
4436     $sortByChar = FALSE, $groupContacts = FALSE,
4437     $returnQuery = FALSE,
4438     $additionalWhereClause = NULL, $sortOrder = NULL,
4439     $additionalFromClause = NULL, $skipOrderAndLimit = FALSE
4440   ) {
4441
4442     if ($includeContactIds) {
4443       $this->_includeContactIds = TRUE;
4444       $this->_whereClause = $this->whereClause();
4445     }
4446
4447     $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params);
4448
4449     // if we’re explicitly looking for a certain contact’s contribs, events, etc.
4450     // and that contact happens to be deleted, set $onlyDeleted to true
4451     foreach ($this->_params as $values) {
4452       $name = CRM_Utils_Array::value(0, $values);
4453       $op = CRM_Utils_Array::value(1, $values);
4454       $value = CRM_Utils_Array::value(2, $values);
4455       if ($name == 'contact_id' and $op == '=') {
4456         if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) {
4457           $onlyDeleted = TRUE;
4458         }
4459         break;
4460       }
4461     }
4462     $this->generatePermissionClause($onlyDeleted, $count);
4463
4464     // building the query string
4465     $groupBy = NULL;
4466     if (!$count) {
4467       if (isset($this->_groupByComponentClause)) {
4468         $groupBy = $this->_groupByComponentClause;
4469       }
4470       elseif ($this->_useGroupBy) {
4471         $groupBy = ' GROUP BY contact_a.id';
4472       }
4473     }
4474     if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) {
4475       $groupBy = 'GROUP BY civicrm_activity.id ';
4476     }
4477
4478     $order = $orderBy = $limit = '';
4479     if (!$count) {
4480       $config = CRM_Core_Config::singleton();
4481       if ($config->includeOrderByClause ||
4482         isset($this->_distinctComponentClause)
4483       ) {
4484         if ($sort) {
4485           if (is_string($sort)) {
4486             $orderBy = $sort;
4487           }
4488           else {
4489             $orderBy = trim($sort->orderBy());
4490           }
4491           if (!empty($orderBy)) {
4492             // this is special case while searching for
4493             // change log CRM-1718
4494             if (preg_match('/sort_name/i', $orderBy)) {
4495               $orderBy = str_replace('sort_name', 'contact_a.sort_name', $orderBy);
4496             }
4497
4498             $orderBy = CRM_Utils_Type::escape($orderBy, 'String');
4499             $order = " ORDER BY $orderBy";
4500
4501             if ($sortOrder) {
4502               $sortOrder = CRM_Utils_Type::escape($sortOrder, 'String');
4503               $order .= " $sortOrder";
4504             }
4505
4506             // always add contact_a.id to the ORDER clause
4507             // so the order is deterministic
4508             if (strpos('contact_a.id', $order) === FALSE) {
4509               $order .= ", contact_a.id";
4510             }
4511           }
4512         }
4513         elseif ($sortByChar) {
4514           $order = " ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc";
4515         }
4516         else {
4517           $order = " ORDER BY contact_a.sort_name asc, contact_a.id";
4518         }
4519       }
4520
4521       // hack for order clause
4522       if ($order) {
4523         $fieldStr = trim(str_replace('ORDER BY', '', $order));
4524         $fieldOrder = explode(' ', $fieldStr);
4525         $field = $fieldOrder[0];
4526
4527         if ($field) {
4528           switch ($field) {
4529             case 'city':
4530             case 'postal_code':
4531               $this->_whereTables["civicrm_address"] = 1;
4532               $order = str_replace($field, "civicrm_address.{$field}", $order);
4533               break;
4534
4535             case 'country':
4536             case 'state_province':
4537               $this->_whereTables["civicrm_{$field}"] = 1;
4538               $order = str_replace($field, "civicrm_{$field}.name", $order);
4539               break;
4540
4541             case 'email':
4542               $this->_whereTables["civicrm_email"] = 1;
4543               $order = str_replace($field, "civicrm_email.{$field}", $order);
4544               break;
4545
4546             default:
4547               //CRM-12565 add "`" around $field if it is a pseudo constant
4548               foreach ($this->_pseudoConstantsSelect as $key => $value) {
4549                 if (!empty($value['element']) && $value['element'] == $field) {
4550                   $order = str_replace($field, "`{$field}`", $order);
4551                 }
4552               }
4553           }
4554           $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4555           $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4556         }
4557       }
4558
4559       if ($rowCount > 0 && $offset >= 0) {
4560         $offset = CRM_Utils_Type::escape($offset, 'Int');
4561         $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
4562         $limit = " LIMIT $offset, $rowCount ";
4563       }
4564     }
4565
4566     // note : this modifies _fromClause and _simpleFromClause
4567     $this->includePseudoFieldsJoin($sort);
4568
4569     list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts);
4570
4571     if(!empty($this->_permissionWhereClause)){
4572       if (empty($where)) {
4573         $where = "WHERE $this->_permissionWhereClause";
4574       }
4575       else {
4576         $where = "$where AND $this->_permissionWhereClause";
4577       }
4578     }
4579
4580     if ($additionalWhereClause) {
4581       $where = $where . ' AND ' . $additionalWhereClause;
4582     }
4583
4584      //additional from clause should be w/ proper joins.
4585     if ($additionalFromClause) {
4586       $from .= "\n" . $additionalFromClause;
4587     }
4588
4589     // if we are doing a transform, do it here
4590     // use the $from, $where and $having to get the contact ID
4591     if ($this->_displayRelationshipType) {
4592       $this->filterRelatedContacts($from, $where, $having);
4593     }
4594
4595     if ($skipOrderAndLimit) {
4596       $query = "$select $from $where $having $groupBy";
4597     }
4598     else {
4599       $query = "$select $from $where $having $groupBy $order $limit";
4600     }
4601
4602     if ($returnQuery) {
4603       return $query;
4604     }
4605     if ($count) {
4606       return CRM_Core_DAO::singleValueQuery($query);
4607     }
4608
4609     $dao = CRM_Core_DAO::executeQuery($query);
4610     if ($groupContacts) {
4611       $ids = array();
4612       while ($dao->fetch()) {
4613         $ids[] = $dao->id;
4614       }
4615       return implode(',', $ids);
4616     }
4617
4618     return $dao;
4619   }
4620
4621   /**
4622    * Fetch a list of contacts from the prev/next cache for displaying a search results page
4623    *
4624    * @param string $cacheKey
4625    * @param int    $offset
4626    * @param int    $rowCount
4627    * @param bool   $includeContactIds
4628    * @return CRM_Core_DAO
4629    */
4630   function getCachedContacts($cacheKey, $offset, $rowCount, $includeContactIds) {
4631     $this->_includeContactIds = $includeContactIds;
4632     list($select, $from, $where) = $this->query();
4633     $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);
4634     $order = " ORDER BY pnc.id";
4635     $groupBy = " GROUP BY contact_a.id";
4636     $limit = " LIMIT $offset, $rowCount";
4637     $query = "$select $from $where $groupBy $order $limit";
4638
4639     return CRM_Core_DAO::executeQuery($query);
4640   }
4641
4642   /**
4643    * Populate $this->_permissionWhereClause with permission related clause and update other
4644    * query related properties.
4645    *
4646    * Function calls ACL permission class and hooks to filter the query appropriately
4647    *
4648    * Note that these 2 params were in the code when extracted from another function
4649    * and a second round extraction would be to make them properties of the class
4650    *
4651    * @param bool $onlyDeleted Only get deleted contacts
4652    * @param bool $count Return Count only
4653    *
4654    * @return null
4655    */
4656   function generatePermissionClause($onlyDeleted = FALSE, $count = FALSE) {
4657     if (!$this->_skipPermission) {
4658       $this->_permissionWhereClause = CRM_ACL_API::whereClause(
4659         CRM_Core_Permission::VIEW,
4660         $this->_tables,
4661         $this->_whereTables,
4662         NULL,
4663         $onlyDeleted,
4664         $this->_skipDeleteClause
4665       );
4666
4667       // regenerate fromClause since permission might have added tables
4668       if ($this->_permissionWhereClause) {
4669         //fix for row count in qill (in contribute/membership find)
4670         if (!$count) {
4671           $this->_useDistinct = TRUE;
4672         }
4673         $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4674         $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4675       }
4676     }
4677     else {
4678       // add delete clause if needed even if we are skipping permission
4679       // CRM-7639
4680       if (!$this->_skipDeleteClause) {
4681         if (CRM_Core_Permission::check('access deleted contacts') and $onlyDeleted) {
4682           $this->_permissionWhereClause = '(contact_a.is_deleted)';
4683         }
4684         else {
4685           // CRM-6181
4686           $this->_permissionWhereClause = '(contact_a.is_deleted = 0)';
4687         }
4688       }
4689     }
4690   }
4691
4692   /**
4693    * @param $val
4694    */
4695   function setSkipPermission($val) {
4696     $this->_skipPermission = $val;
4697   }
4698
4699   /**
4700    * @param null $context
4701    *
4702    * @return array
4703    */
4704   function &summaryContribution($context = NULL) {
4705     list($innerselect, $from, $where, $having) = $this->query(TRUE);
4706
4707     // hack $select
4708     $select = "
4709 SELECT COUNT( conts.total_amount ) as total_count,
4710        SUM(   conts.total_amount ) as total_amount,
4711        AVG(   conts.total_amount ) as total_avg,
4712        conts.currency              as currency";
4713     if($this->_permissionWhereClause) {
4714       $where .= " AND " . $this->_permissionWhereClause;
4715     }
4716     if ($context == 'search') {
4717       $where .= " AND contact_a.is_deleted = 0 ";
4718     }
4719
4720     // make sure contribution is completed - CRM-4989
4721     $completedWhere =  $where . " AND civicrm_contribution.contribution_status_id = 1 ";
4722
4723
4724     $summary = array();
4725     $summary['total'] = array();
4726     $summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a";
4727
4728     $query = "$select FROM (
4729       SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $completedWhere
4730       GROUP BY civicrm_contribution.id
4731     ) as conts
4732     GROUP BY currency";
4733
4734     $dao = CRM_Core_DAO::executeQuery($query);
4735
4736     $summary['total']['count'] = 0;
4737     $summary['total']['amount'] = $summary['total']['avg'] = array();
4738     while ($dao->fetch()) {
4739       $summary['total']['count'] += $dao->total_count;
4740       $summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
4741       $summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
4742     }
4743     if (!empty($summary['total']['amount'])) {
4744       $summary['total']['amount'] = implode(',&nbsp;', $summary['total']['amount']);
4745       $summary['total']['avg'] = implode(',&nbsp;', $summary['total']['avg']);
4746     }
4747     else {
4748       $summary['total']['amount'] = $summary['total']['avg'] = 0;
4749     }
4750
4751     // soft credit summary
4752     if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
4753       $softCreditWhere = "{$completedWhere} AND civicrm_contribution_soft.id IS NOT NULL";
4754       $query = "
4755         $select FROM (
4756           SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency $from $softCreditWhere
4757           GROUP BY civicrm_contribution_soft.id
4758         ) as conts
4759         GROUP BY currency";
4760       $dao = CRM_Core_DAO::executeQuery($query);
4761       $summary['soft_credit']['count'] = 0;
4762       $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = array();
4763       while ($dao->fetch()) {
4764         $summary['soft_credit']['count'] += $dao->total_count;
4765         $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
4766         $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
4767       }
4768       if (!empty($summary['soft_credit']['amount'])) {
4769         $summary['soft_credit']['amount'] = implode(',&nbsp;', $summary['soft_credit']['amount']);
4770         $summary['soft_credit']['avg'] = implode(',&nbsp;', $summary['soft_credit']['avg']);
4771       }
4772       else {
4773         $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0;
4774       }
4775     }
4776
4777     // hack $select
4778     //@todo  - this could be one query using the IF in mysql - eg
4779     //  SELECT sum(total_completed), sum(count_completed), sum(count_cancelled), sum(total_cancelled) FROM (
4780     //   SELECT civicrm_contribution.total_amount, civicrm_contribution.currency  ,
4781     //  IF(civicrm_contribution.contribution_status_id = 1, 1, 0 ) as count_completed,
4782     //  IF(civicrm_contribution.contribution_status_id = 1, total_amount, 0 ) as total_completed,
4783     //  IF(civicrm_contribution.cancel_date IS NOT NULL = 1, 1, 0 ) as count_cancelled,
4784     //  IF(civicrm_contribution.cancel_date IS NOT NULL = 1, total_amount, 0 ) as total_cancelled
4785     // FROM civicrm_contact contact_a
4786     //  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
4787     // WHERE  ( ... where clause....
4788     // AND (civicrm_contribution.cancel_date IS NOT NULL OR civicrm_contribution.contribution_status_id = 1)
4789     //  ) as conts
4790
4791     $select = "
4792 SELECT COUNT( conts.total_amount ) as cancel_count,
4793        SUM(   conts.total_amount ) as cancel_amount,
4794        AVG(   conts.total_amount ) as cancel_avg,
4795        conts.currency              as currency";
4796
4797     $where .= " AND civicrm_contribution.cancel_date IS NOT NULL ";
4798     if ($context == 'search') {
4799       $where .= " AND contact_a.is_deleted = 0 ";
4800     }
4801
4802     $query = "$select FROM (
4803       SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where
4804       GROUP BY civicrm_contribution.id
4805     ) as conts
4806     GROUP BY currency";
4807
4808     $dao = CRM_Core_DAO::executeQuery($query);
4809
4810     if ($dao->N <= 1) {
4811       if ($dao->fetch()) {
4812         $summary['cancel']['count'] = $dao->cancel_count;
4813         $summary['cancel']['amount'] = $dao->cancel_amount;
4814         $summary['cancel']['avg'] = $dao->cancel_avg;
4815       }
4816     }
4817     else {
4818       $summary['cancel']['count'] = 0;
4819       $summary['cancel']['amount'] = $summary['cancel']['avg'] = array();
4820       while ($dao->fetch()) {
4821         $summary['cancel']['count'] += $dao->cancel_count;
4822         $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
4823         $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
4824       }
4825       $summary['cancel']['amount'] = implode(',&nbsp;', $summary['cancel']['amount']);
4826       $summary['cancel']['avg'] = implode(',&nbsp;', $summary['cancel']['avg']);
4827     }
4828
4829     return $summary;
4830   }
4831
4832   /**
4833    * getter for the qill object
4834    *
4835    * @return string
4836    * @access public
4837    */
4838   function qill() {
4839     return $this->_qill;
4840   }
4841
4842   /**
4843    * default set of return default hier return properties
4844    *
4845    * @return array
4846    * @access public
4847    */
4848   static function &defaultHierReturnProperties() {
4849     if (!isset(self::$_defaultHierReturnProperties)) {
4850       self::$_defaultHierReturnProperties = array(
4851         'home_URL' => 1,
4852         'image_URL' => 1,
4853         'legal_identifier' => 1,
4854         'external_identifier' => 1,
4855         'contact_type' => 1,
4856         'contact_sub_type' => 1,
4857         'sort_name' => 1,
4858         'display_name' => 1,
4859         'nick_name' => 1,
4860         'first_name' => 1,
4861         'middle_name' => 1,
4862         'last_name' => 1,
4863         'prefix_id' => 1,
4864         'suffix_id' => 1,
4865         'formal_title' => 1,
4866         'communication_style_id' => 1,
4867         'email_greeting' => 1,
4868         'postal_greeting' => 1,
4869         'addressee' => 1,
4870         'birth_date' => 1,
4871         'gender_id' => 1,
4872         'preferred_communication_method' => 1,
4873         'do_not_phone' => 1,
4874         'do_not_email' => 1,
4875         'do_not_mail' => 1,
4876         'do_not_sms' => 1,
4877         'do_not_trade' => 1,
4878         'location' =>
4879         array(
4880           '1' => array('location_type' => 1,
4881             'street_address' => 1,
4882             'city' => 1,
4883             'state_province' => 1,
4884             'postal_code' => 1,
4885             'postal_code_suffix' => 1,
4886             'country' => 1,
4887             'phone-Phone' => 1,
4888             'phone-Mobile' => 1,
4889             'phone-Fax' => 1,
4890             'phone-1' => 1,
4891             'phone-2' => 1,
4892             'phone-3' => 1,
4893             'im-1' => 1,
4894             'im-2' => 1,
4895             'im-3' => 1,
4896             'email-1' => 1,
4897             'email-2' => 1,
4898             'email-3' => 1,
4899           ),
4900           '2' => array(
4901             'location_type' => 1,
4902             'street_address' => 1,
4903             'city' => 1,
4904             'state_province' => 1,
4905             'postal_code' => 1,
4906             'postal_code_suffix' => 1,
4907             'country' => 1,
4908             'phone-Phone' => 1,
4909             'phone-Mobile' => 1,
4910             'phone-1' => 1,
4911             'phone-2' => 1,
4912             'phone-3' => 1,
4913             'im-1' => 1,
4914             'im-2' => 1,
4915             'im-3' => 1,
4916             'email-1' => 1,
4917             'email-2' => 1,
4918             'email-3' => 1,
4919           ),
4920         ),
4921       );
4922     }
4923     return self::$_defaultHierReturnProperties;
4924   }
4925
4926   /**
4927    * @param $values
4928    * @param $tableName
4929    * @param $fieldName
4930    * @param $dbFieldName
4931    * @param $fieldTitle
4932    * @param bool $appendTimeStamp
4933    */
4934   function dateQueryBuilder(
4935     &$values, $tableName, $fieldName,
4936     $dbFieldName, $fieldTitle,
4937     $appendTimeStamp = TRUE
4938   ) {
4939     list($name, $op, $value, $grouping, $wildcard) = $values;
4940
4941     if (!$value) {
4942       return;
4943     }
4944
4945     if ($name == "{$fieldName}_low" ||
4946       $name == "{$fieldName}_high"
4947     ) {
4948       if (isset($this->_rangeCache[$fieldName])) {
4949         return;
4950       }
4951       $this->_rangeCache[$fieldName] = 1;
4952
4953       $secondOP = $secondPhrase = $secondValue = $secondDate = $secondDateFormat = NULL;
4954
4955       if ($name == $fieldName . '_low') {
4956         $firstOP = '>=';
4957         $firstPhrase = ts('greater than or equal to');
4958         $firstDate = CRM_Utils_Date::processDate($value);
4959
4960         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
4961         if (!empty($secondValues) && $secondValues[2]) {
4962           $secondOP = '<=';
4963           $secondPhrase = ts('less than or equal to');
4964           $secondValue = $secondValues[2];
4965
4966           if ($appendTimeStamp && strlen($secondValue) == 10) {
4967             $secondValue .= ' 23:59:59';
4968           }
4969           $secondDate = CRM_Utils_Date::processDate($secondValue);
4970         }
4971       }
4972       elseif ($name == $fieldName . '_high') {
4973         $firstOP = '<=';
4974         $firstPhrase = ts('less than or equal to');
4975
4976         if ($appendTimeStamp && strlen($value) == 10) {
4977           $value .= ' 23:59:59';
4978         }
4979         $firstDate = CRM_Utils_Date::processDate($value);
4980
4981         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
4982         if (!empty($secondValues) && $secondValues[2]) {
4983           $secondOP = '>=';
4984           $secondPhrase = ts('greater than or equal to');
4985           $secondValue = $secondValues[2];
4986           $secondDate = CRM_Utils_Date::processDate($secondValue);
4987         }
4988       }
4989
4990       if (!$appendTimeStamp) {
4991         $firstDate = substr($firstDate, 0, 8);
4992       }
4993       $firstDateFormat = CRM_Utils_Date::customFormat($firstDate);
4994
4995       if ($secondDate) {
4996         if (!$appendTimeStamp) {
4997           $secondDate = substr($secondDate, 0, 8);
4998         }
4999         $secondDateFormat = CRM_Utils_Date::customFormat($secondDate);
5000       }
5001
5002       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5003       if ($secondDate) {
5004         $this->_where[$grouping][] = "
5005 ( {$tableName}.{$dbFieldName} $firstOP '$firstDate' ) AND
5006 ( {$tableName}.{$dbFieldName} $secondOP '$secondDate' )
5007 ";
5008         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$firstDateFormat\" " . ts('AND') . " $secondPhrase \"$secondDateFormat\"";
5009       }
5010       else {
5011         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP '$firstDate'";
5012         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$firstDateFormat\"";
5013       }
5014     }
5015
5016     if ($name == $fieldName) {
5017       // $op = '=';
5018       $phrase = $op;
5019
5020       $date = CRM_Utils_Date::processDate($value);
5021
5022       if (!$appendTimeStamp) {
5023         $date = substr($date, 0, 8);
5024       }
5025
5026       $format = CRM_Utils_Date::customFormat($date);
5027
5028       if ($date) {
5029         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op '$date'";
5030       }
5031       else {
5032         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op";
5033       }
5034       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5035       $this->_qill[$grouping][] = "$fieldTitle - $phrase \"$format\"";
5036     }
5037   }
5038
5039   /**
5040    * @param $values
5041    * @param $tableName
5042    * @param $fieldName
5043    * @param $dbFieldName
5044    * @param $fieldTitle
5045    * @param null $options
5046    */
5047   function numberRangeBuilder(&$values,
5048     $tableName, $fieldName,
5049     $dbFieldName, $fieldTitle,
5050     $options = NULL
5051   ) {
5052     list($name, $op, $value, $grouping, $wildcard) = $values;
5053
5054     if ($name == "{$fieldName}_low" ||
5055       $name == "{$fieldName}_high"
5056     ) {
5057       if (isset($this->_rangeCache[$fieldName])) {
5058         return;
5059       }
5060       $this->_rangeCache[$fieldName] = 1;
5061
5062       $secondOP = $secondPhrase = $secondValue = NULL;
5063
5064       if ($name == "{$fieldName}_low") {
5065         $firstOP = '>=';
5066         $firstPhrase = ts('greater than');
5067
5068         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5069         if (!empty($secondValues)) {
5070           $secondOP = '<=';
5071           $secondPhrase = ts('less than');
5072           $secondValue = $secondValues[2];
5073         }
5074       }
5075       else {
5076         $firstOP = '<=';
5077         $firstPhrase = ts('less than');
5078
5079         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5080         if (!empty($secondValues)) {
5081           $secondOP = '>=';
5082           $secondPhrase = ts('greater than');
5083           $secondValue = $secondValues[2];
5084         }
5085       }
5086
5087       if ($secondOP) {
5088         $this->_where[$grouping][] = "
5089 ( {$tableName}.{$dbFieldName} $firstOP {$value} ) AND
5090 ( {$tableName}.{$dbFieldName} $secondOP {$secondValue} )
5091 ";
5092         $displayValue = $options ? $options[$value] : $value;
5093         $secondDisplayValue = $options ? $options[$secondValue] : $secondValue;
5094
5095         $this->_qill[$grouping][] =
5096           "$fieldTitle - $firstPhrase \"$displayValue\" " . ts('AND') . " $secondPhrase \"$secondDisplayValue\"";
5097       }
5098       else {
5099         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP {$value}";
5100         $displayValue = $options ? $options[$value] : $value;
5101         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$displayValue\"";
5102       }
5103       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5104
5105       return;
5106     }
5107
5108     if ($name == $fieldName) {
5109       $op = '=';
5110       $phrase = '=';
5111
5112       $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op {$value}";
5113
5114       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5115       $displayValue = $options ? $options[$value] : $value;
5116       $this->_qill[$grouping][] = "$fieldTitle - $phrase \"$displayValue\"";
5117     }
5118
5119     return;
5120   }
5121
5122   /**
5123    * Given the field name, operator, value & its data type
5124    * builds the where Clause for the query
5125    * used for handling 'IS NULL'/'IS NOT NULL' operators
5126    *
5127    * @param string  $field       fieldname
5128    * @param string  $op          operator
5129    * @param string  $value       value
5130    * @param string  $dataType    data type of the field
5131    *
5132    * @return string where clause for the query
5133    * @access public
5134    */
5135   static function buildClause($field, $op, $value = NULL, $dataType = NULL) {
5136     $op = trim($op);
5137     $clause = "$field $op";
5138
5139     switch ($op) {
5140       case 'IS NULL':
5141       case 'IS NOT NULL':
5142         return $clause;
5143
5144       case 'IS EMPTY':
5145         $clause = " ( $field IS NULL OR $field = '' ) ";
5146         return $clause;
5147
5148       case 'IS NOT EMPTY':
5149         $clause = " ( $field IS NOT NULL AND $field <> '' ) ";
5150         return $clause;
5151
5152       case 'IN':
5153       case 'NOT IN':
5154         if (isset($dataType)) {
5155           if (is_array($value)) {
5156             $values = $value;
5157           }
5158           else {
5159             $value = CRM_Utils_Type::escape($value, "String");
5160             $values = explode(',', CRM_Utils_Array::value(0, explode(')', CRM_Utils_Array::value(1, explode('(', $value)))));
5161           }
5162           // supporting multiple values in IN clause
5163           $val = array();
5164           foreach ($values as $v) {
5165             $v = trim($v);
5166             $val[] = "'" . CRM_Utils_Type::escape($v, $dataType) . "'";
5167           }
5168           $value = "(" . implode($val, ",") . ")";
5169         }
5170         return "$clause $value";
5171
5172       default:
5173         if (empty($dataType)) {
5174           $dataType = 'String';
5175         }
5176
5177         $value = CRM_Utils_Type::escape($value, $dataType);
5178         // if we don't have a dataType we should assume
5179         if ($dataType == 'String' || $dataType == 'Text') {
5180           $value = "'" . strtolower($value) . "'";
5181         }
5182         return "$clause $value";
5183     }
5184   }
5185
5186   /**
5187    * @param bool $reset
5188    *
5189    * @return array
5190    */
5191   function openedSearchPanes($reset = FALSE) {
5192     if (!$reset || empty($this->_whereTables)) {
5193       return self::$_openedPanes;
5194     }
5195
5196     // pane name to table mapper
5197     $panesMapper = array(
5198       ts('Contributions') => 'civicrm_contribution',
5199       ts('Memberships') => 'civicrm_membership',
5200       ts('Events') => 'civicrm_participant',
5201       ts('Relationships') => 'civicrm_relationship',
5202       ts('Activities') => 'civicrm_activity',
5203       ts('Pledges') => 'civicrm_pledge',
5204       ts('Cases') => 'civicrm_case',
5205       ts('Grants') => 'civicrm_grant',
5206       ts('Address Fields') => 'civicrm_address',
5207       ts('Notes') => 'civicrm_note',
5208       ts('Change Log') => 'civicrm_log',
5209       ts('Mailings') => 'civicrm_mailing_event_queue',
5210     );
5211     CRM_Contact_BAO_Query_Hook::singleton()->getPanesMapper($panesMapper);
5212
5213     foreach (array_keys($this->_whereTables) as $table) {
5214       if ($panName = array_search($table, $panesMapper)) {
5215         self::$_openedPanes[$panName] = TRUE;
5216       }
5217     }
5218
5219     return self::$_openedPanes;
5220   }
5221
5222   /**
5223    * @param $operator
5224    */
5225   function setOperator($operator) {
5226     $validOperators = array('AND', 'OR');
5227     if (!in_array($operator, $validOperators)) {
5228       $operator = 'AND';
5229     }
5230     $this->_operator = $operator;
5231   }
5232
5233   /**
5234    * @return string
5235    */
5236   function getOperator() {
5237     return $this->_operator;
5238   }
5239
5240   /**
5241    * @param $from
5242    * @param $where
5243    * @param $having
5244    */
5245   function filterRelatedContacts(&$from, &$where, &$having) {
5246     static $_rTypeProcessed = NULL;
5247     static $_rTypeFrom = NULL;
5248     static $_rTypeWhere = NULL;
5249
5250     if (!$_rTypeProcessed) {
5251       $_rTypeProcessed = TRUE;
5252
5253       // create temp table with contact ids
5254       $tableName = CRM_Core_DAO::createTempTableName('civicrm_transform', TRUE);
5255       $sql = "CREATE TEMPORARY TABLE $tableName ( contact_id int primary key) ENGINE=HEAP";
5256       CRM_Core_DAO::executeQuery($sql);
5257
5258       $sql = "
5259 REPLACE INTO $tableName ( contact_id )
5260 SELECT contact_a.id
5261        $from
5262        $where
5263        $having
5264 ";
5265       CRM_Core_DAO::executeQuery($sql);
5266
5267       $qillMessage = ts('Contacts with a Relationship Type of: ');
5268       $rTypes = CRM_Core_PseudoConstant::relationshipType();
5269
5270       if (is_numeric($this->_displayRelationshipType)) {
5271         $relationshipTypeLabel = $rTypes[$this->_displayRelationshipType]['label_a_b'];
5272         $_rTypeFrom = "
5273 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id OR displayRelType.contact_id_b = contact_a.id )
5274 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a OR transform_temp.contact_id = displayRelType.contact_id_b )
5275 ";
5276         $_rTypeWhere = "
5277 WHERE displayRelType.relationship_type_id = {$this->_displayRelationshipType}
5278 AND   displayRelType.is_active = 1
5279 ";
5280       }
5281       else {
5282         list($relType, $dirOne, $dirTwo) = explode('_', $this->_displayRelationshipType);
5283         if ($dirOne == 'a') {
5284           $relationshipTypeLabel = $rTypes[$relType]['label_a_b'];
5285           $_rTypeFrom .= "
5286 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id )
5287 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_b )
5288 ";
5289         }
5290         else {
5291           $relationshipTypeLabel = $rTypes[$relType]['label_b_a'];
5292           $_rTypeFrom .= "
5293 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_b = contact_a.id )
5294 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a )
5295 ";
5296         }
5297         $_rTypeWhere = "
5298 WHERE displayRelType.relationship_type_id = $relType
5299 AND   displayRelType.is_active = 1
5300 ";
5301       }
5302       $this->_qill[0][] = $qillMessage . "'" . $relationshipTypeLabel . "'";
5303     }
5304
5305     if (strpos($from, $_rTypeFrom) === FALSE) {
5306       // lets replace all the INNER JOIN's in the $from so we dont exclude other data
5307       // this happens when we have an event_type in the quert (CRM-7969)
5308       $from = str_replace("INNER JOIN", "LEFT JOIN", $from);
5309       $from .= $_rTypeFrom;
5310       $where = $_rTypeWhere;
5311     }
5312
5313     $having = NULL;
5314   }
5315
5316   /**
5317    * @param $op
5318    *
5319    * @return bool
5320    */
5321   static function caseImportant( $op ) {
5322     return
5323       in_array($op, array('LIKE', 'IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY')) ? FALSE : TRUE;
5324   }
5325
5326   /**
5327    * @param $returnProperties
5328    * @param $prefix
5329    *
5330    * @return bool
5331    */
5332   static function componentPresent( &$returnProperties, $prefix ) {
5333     foreach ($returnProperties as $name => $dontCare ) {
5334       if (substr($name, 0, strlen($prefix)) == $prefix) {
5335         return TRUE;
5336       }
5337     }
5338     return FALSE;
5339   }
5340
5341   /**
5342    * Builds the necessary structures for all fields that are similar to option value lookups
5343    *
5344    * @param $name     string the name of the field
5345    * @param $op       string the sql operator, this function should handle ALL SQL operators
5346    * @param $value string|integer|array depends on the operator and who's calling the query builder
5347    * @param $grouping int    the index where to place the where clause
5348    * @param $selectValues
5349    * @param $field    array  an array that contains various properties of the field identified by $name
5350    * @param $label    string The label for this field element
5351    * @param $dataType string The data type for this element
5352    *
5353    * @param bool $useIDsOnly
5354    *
5355    * @internal param array $selectValue the key value pairs for this element. This allows us to use this function for things besides option-value pairs
5356    * @return void     adds the where clause and qill to the query object
5357    */
5358   function optionValueQuery(
5359     $name,
5360     $op,
5361     $value,
5362     $grouping,
5363     $selectValues,
5364     $field,
5365     $label,
5366     $dataType = 'String',
5367     $useIDsOnly = FALSE
5368   ) {
5369
5370     if (!empty($selectValues) && !empty($selectValues[$value])) {
5371       $qill = $selectValues[$value];
5372     }
5373     else {
5374       $qill = $value;
5375     }
5376
5377     $pseudoFields = array('email_greeting', 'postal_greeting', 'addressee', 'gender_id', 'prefix_id', 'suffix_id', 'communication_style_id');
5378
5379     if (is_numeric($value)) {
5380       $qill = $selectValues[(int ) $value];
5381     }
5382     elseif ($op == 'IN' || $op == 'NOT IN') {
5383       $values = self::parseSearchBuilderString($value);
5384       if (is_array($values)) {
5385         $intVals = array();
5386         $newValues = array();
5387         foreach ($values as $v) {
5388           $intVals[] = (int) $v;
5389           $newValues[] = $selectValues[(int ) $v];
5390         }
5391
5392         $value = (in_array($name, $pseudoFields)) ? $intVals : $newValues;
5393         $qill = implode(', ', $newValues);
5394       }
5395     }
5396     elseif (!array_key_exists($value, $selectValues)) {
5397       // its a string, lets get the int value
5398       $value = array_search($value, $selectValues);
5399     }
5400     if ($useIDsOnly) {
5401       list($tableName, $fieldName) = explode('.', $field['where'], 2);
5402       if ($tableName == 'civicrm_contact') {
5403         $wc = "contact_a.$fieldName";
5404       }
5405     }
5406     else {
5407       $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
5408     }
5409
5410     if (in_array($name, $pseudoFields)) {
5411       if (!in_array($name, array('gender_id', 'prefix_id', 'suffix_id', 'communication_style_id'))) {
5412         $wc = "contact_a.{$name}_id";
5413       }
5414       $dataType = 'Positive';
5415       $value = (!$value) ? 0 : $value;
5416     }
5417
5418     $this->_qill[$grouping][] = $label . " $op '$qill'";
5419     $op = (in_array($name, $pseudoFields) && ($op == 'LIKE' || $op == 'RLIKE')) ? '=' : $op;
5420     $this->_where[$grouping][] = self::buildClause($wc, $op, $value, $dataType);
5421   }
5422
5423   /**
5424    * function to check and explode a user defined numeric string into an array
5425    * this was the protocol used by search builder in the old old days before we had
5426    * super nice js widgets to do the hard work
5427    *
5428    * @param string $string
5429    * @param string $dataType the dataType we should check for the values, default integer
5430    *
5431    * @return bool|array if string does not match the patter
5432    *         array of numeric values if string does match the pattern
5433    * @static
5434    */
5435   static function parseSearchBuilderString($string, $dataType = 'Integer') {
5436     $string = trim($string);
5437     if (substr($string, 0, 1) != '(' || substr($string, -1, 1) != ')') {
5438       Return FALSE;
5439     }
5440
5441     $string = substr($string, 1, -1);
5442     $values = explode(',', $string);
5443     if (empty($values)) {
5444       return FALSE;
5445     }
5446
5447     $returnValues = array();
5448     foreach ($values as $v) {
5449       if ($dataType == 'Integer' && ! is_numeric($v)) {
5450         return FALSE;
5451       }
5452       else if ($dataType == 'String' && ! is_string($v)) {
5453         return FALSE;
5454       }
5455       $returnValues[] = trim($v);
5456     }
5457
5458     if (empty($returnValues)) {
5459       return FALSE;
5460     }
5461
5462     return $returnValues;
5463   }
5464
5465   /**
5466    * convert the pseudo constants id's to their names
5467    *
5468    * @param CRM_Core_DAO dao
5469    * @param bool $return
5470    *
5471    * @return array
5472    */
5473   function convertToPseudoNames(&$dao, $return = FALSE) {
5474     if (empty($this->_pseudoConstantsSelect)) {
5475       return;
5476     }
5477     $values = array();
5478     foreach ($this->_pseudoConstantsSelect as $key => $value) {
5479       if (!empty($this->_pseudoConstantsSelect[$key]['sorting'])) {
5480         continue;
5481       }
5482
5483       if (property_exists($dao, $value['idCol'])) {
5484         $val = $dao->$value['idCol'];
5485
5486         if (CRM_Utils_System::isNull($val)) {
5487           $dao->$key = NULL;
5488         }
5489         elseif ($baoName = CRM_Utils_Array::value('bao', $value, NULL)) {
5490           //preserve id value
5491           $idColumn = "{$key}_id";
5492           $dao->$idColumn = $val;
5493           $dao->$value['pseudoField'] = $dao->$key = CRM_Core_PseudoConstant::getLabel($baoName, $value['pseudoField'], $val);
5494         }
5495         elseif ($value['pseudoField'] == 'state_province_abbreviation') {
5496           $dao->$key = CRM_Core_PseudoConstant::stateProvinceAbbreviation($val);
5497         }
5498         // FIX ME: we should potentially move this to component Query and write a wrapper function that
5499         // handles pseudoconstant fixes for all component
5500         elseif ($value['pseudoField'] == 'participant_role') {
5501           $viewRoles = array();
5502           foreach (explode(CRM_Core_DAO::VALUE_SEPARATOR, $val) as $k => $v) {
5503             $viewRoles[] = CRM_Event_PseudoConstant::participantRole($v);
5504           }
5505           $dao->$key = implode(', ', $viewRoles);
5506         }
5507         else {
5508           $labels = CRM_Core_OptionGroup::values($value['pseudoField']);
5509           $dao->$key = $labels[$val];
5510         }
5511
5512         // return converted values in array format
5513         if ($return) {
5514           if (strpos($key, '-') !== FALSE) {
5515             $keyVal = explode('-', $key);
5516             $current = &$values;
5517             $lastElement = array_pop($keyVal);
5518             foreach ($keyVal as $v) {
5519               if (!array_key_exists($v, $current)) {
5520                 $current[$v] = array();
5521               }
5522               $current = &$current[$v];
5523             }
5524             $current[$lastElement] = $dao->$key;
5525           }
5526           else {
5527             $values[$key] = $dao->$key;
5528           }
5529         }
5530       }
5531     }
5532     return $values;
5533   }
5534
5535   /**
5536    * include pseudo fields LEFT JOIN
5537    * @param string|array $sort  can be a object or string
5538    *
5539    * @return array
5540    */
5541   function includePseudoFieldsJoin($sort) {
5542     if (!$sort || empty($this->_pseudoConstantsSelect)) {
5543       return;
5544     }
5545     $sort = is_string($sort) ? $sort : $sort->orderBy();
5546     $present = array();
5547
5548     foreach ($this->_pseudoConstantsSelect as $name => $value) {
5549       if (!empty($value['table'])) {
5550         $regex = "/({$value['table']}\.|{$name})/";
5551         if (preg_match($regex, $sort)) {
5552           $this->_elemnt[$value['element']] = 1;
5553           $this->_select[$value['element']] = $value['select'];
5554           $this->_pseudoConstantsSelect[$name]['sorting'] = 1;
5555           $present[$value['table']] = $value['join'];
5556         }
5557       }
5558     }
5559     $presentSimpleFrom = $present;
5560
5561     if (array_key_exists('civicrm_worldregion', $this->_whereTables) &&
5562       array_key_exists('civicrm_country', $presentSimpleFrom)) {
5563       unset($presentSimpleFrom['civicrm_country']);
5564     }
5565     if (array_key_exists('civicrm_worldregion', $this->_tables) &&
5566       array_key_exists('civicrm_country', $present)) {
5567       unset($present['civicrm_country']);
5568     }
5569
5570     $presentClause = $presentSimpleFromClause = NULL;
5571     if (!empty($present)) {
5572       $presentClause = implode(' ', $present);
5573     }
5574     if (!empty($presentSimpleFrom)) {
5575       $presentSimpleFromClause = implode(' ', $presentSimpleFrom);
5576     }
5577
5578     $this->_fromClause = $this->_fromClause . $presentClause;
5579     $this->_simpleFromClause = $this->_simpleFromClause . $presentSimpleFromClause;
5580
5581     return array($presentClause, $presentSimpleFromClause);
5582   }
5583 }