CRM/Contact/BAO/Query.php

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