CRM/Contact/BAO/Query.php

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