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