CRM/Contact/BAO/Query.php

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