CRM/Contact/BAO/Query.php

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