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