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       if (self::isAlreadyProcessedForQueryFormat($values)) {
1511         $params[] = $values;
1512         continue;
1513       }
1514
1515       self::legacyConvertFormValues($id, $values);
1516
1517       // The form uses 1 field to represent two db fields
1518       if ($id == 'contact_type' && $values && (!is_array($values) || !array_intersect(array_keys($values), CRM_Core_DAO::acceptedSQLOperators()))) {
1519         $contactType = array();
1520         $subType = array();
1521         foreach ((array) $values as $key => $type) {
1522           $types = explode('__', is_numeric($type) ? $key : $type);
1523           $contactType[$types[0]] = $types[0];
1524           // Add sub-type if specified
1525           if (!empty($types[1])) {
1526             $subType[$types[1]] = $types[1];
1527           }
1528         }
1529         $params[] = array('contact_type', 'IN', $contactType, 0, 0);
1530         if ($subType) {
1531           $params[] = array('contact_sub_type', 'IN', $subType, 0, 0);
1532         }
1533       }
1534       elseif ($id == 'privacy') {
1535         if (is_array($formValues['privacy'])) {
1536           $op = !empty($formValues['privacy']['do_not_toggle']) ? '=' : '!=';
1537           foreach ($formValues['privacy'] as $key => $value) {
1538             if ($value) {
1539               $params[] = array($key, $op, $value, 0, 0);
1540             }
1541           }
1542         }
1543       }
1544       elseif ($id == 'email_on_hold') {
1545         if ($formValues['email_on_hold']['on_hold']) {
1546           $params[] = array('on_hold', '=', $formValues['email_on_hold']['on_hold'], 0, 0);
1547         }
1548       }
1549       elseif (substr($id, 0, 7) == 'custom_'
1550           &&  (
1551             substr($id, -9, 9) == '_relative'
1552             || substr($id, -5, 5) == '_from'
1553             || substr($id, -3, 3) == '_to'
1554           )
1555       ) {
1556         self::convertCustomDateRelativeFields($formValues, $params, $values, $id);
1557       }
1558       elseif (preg_match('/_date_relative$/', $id) ||
1559         $id == 'event_relative' ||
1560         $id == 'case_from_relative' ||
1561         $id == 'case_to_relative' ||
1562         $id == 'participant_relative'
1563       ) {
1564         if ($id == 'event_relative') {
1565           $fromRange = 'event_start_date_low';
1566           $toRange = 'event_end_date_high';
1567         }
1568         elseif ($id == 'participant_relative') {
1569           $fromRange = 'participant_register_date_low';
1570           $toRange = 'participant_register_date_high';
1571         }
1572         elseif ($id == 'case_from_relative') {
1573           $fromRange = 'case_from_start_date_low';
1574           $toRange = 'case_from_start_date_high';
1575         }
1576         elseif ($id == 'case_to_relative') {
1577           $fromRange = 'case_to_end_date_low';
1578           $toRange = 'case_to_end_date_high';
1579         }
1580         else {
1581           $dateComponent = explode('_date_relative', $id);
1582           $fromRange = "{$dateComponent[0]}_date_low";
1583           $toRange = "{$dateComponent[0]}_date_high";
1584         }
1585
1586         if (array_key_exists($fromRange, $formValues) && array_key_exists($toRange, $formValues)) {
1587           CRM_Contact_BAO_Query::fixDateValues($formValues[$id], $formValues[$fromRange], $formValues[$toRange]);
1588           continue;
1589         }
1590       }
1591       elseif (in_array($id, $entityReferenceFields) && !empty($values) && is_string($values) && (strpos($values, ',') !=
1592         FALSE)) {
1593         $params[] = array($id, 'IN', explode(',', $values), 0, 0);
1594       }
1595       else {
1596         $values = CRM_Contact_BAO_Query::fixWhereValues($id, $values, $wildcard, $useEquals, $apiEntity);
1597
1598         if (!$values) {
1599           continue;
1600         }
1601         $params[] = $values;
1602       }
1603     }
1604     return $params;
1605   }
1606
1607   /**
1608    * Function to support legacy format for groups and tags.
1609    *
1610    * @param string $id
1611    * @param array|int $values
1612    *
1613    */
1614   public static function legacyConvertFormValues($id, &$values) {
1615     $legacyElements = array(
1616       'group',
1617       'tag',
1618       'contact_tags',
1619       'contact_type',
1620       'membership_type_id',
1621       'membership_status_id',
1622     );
1623     if (in_array($id, $legacyElements) && is_array($values)) {
1624       // prior to 4.7, formValues for some attributes (e.g. group, tag) are stored in array(id1 => 1, id2 => 1),
1625       // as per the recent Search fixes $values need to be in standard array(id1, id2) format
1626       $ids = array_keys($values, 1);
1627       if (count($ids) > 1 ||
1628         (count($ids) == 1 &&
1629           (key($values) > 1 ||
1630             is_string(key($values)) ||
1631             (key($values) == 1 && $values[1] == 1) // handle (0 => 4), (1 => 1)
1632           )
1633         )
1634       ) {
1635         $values = $ids;
1636       }
1637     }
1638   }
1639
1640   /**
1641    * Fix values from query from/to something no-one cared enough to document.
1642    *
1643    * @param int $id
1644    * @param array $values
1645    * @param int $wildcard
1646    * @param bool $useEquals
1647    *
1648    * @param string $apiEntity
1649    *
1650    * @return array|null
1651    */
1652   public static function fixWhereValues($id, &$values, $wildcard = 0, $useEquals = FALSE, $apiEntity = NULL) {
1653     // skip a few search variables
1654     static $skipWhere = NULL;
1655     static $likeNames = NULL;
1656     $result = NULL;
1657     // Change camelCase EntityName to lowercase with underscores
1658     $apiEntity = _civicrm_api_get_entity_name_from_camel($apiEntity);
1659
1660     if (CRM_Utils_System::isNull($values)) {
1661       return $result;
1662     }
1663
1664     if (!$skipWhere) {
1665       $skipWhere = array(
1666         'task',
1667         'radio_ts',
1668         'uf_group_id',
1669         'component_mode',
1670         'qfKey',
1671         'operator',
1672         'display_relationship_type',
1673       );
1674     }
1675
1676     if (in_array($id, $skipWhere) ||
1677       substr($id, 0, 4) == '_qf_' ||
1678       substr($id, 0, 7) == 'hidden_'
1679     ) {
1680       return $result;
1681     }
1682
1683     if ($apiEntity &&
1684       (substr($id, 0, strlen($apiEntity)) != $apiEntity) &&
1685       (substr($id, 0, 10) != 'financial_' && substr($id, 0, 8) != 'payment_')
1686     ) {
1687       $id = $apiEntity . '_' . $id;
1688     }
1689
1690     if (!$likeNames) {
1691       $likeNames = array('sort_name', 'email', 'note', 'display_name');
1692     }
1693
1694     // email comes in via advanced search
1695     // so use wildcard always
1696     if ($id == 'email') {
1697       $wildcard = 1;
1698     }
1699
1700     if (!$useEquals && in_array($id, $likeNames)) {
1701       $result = array($id, 'LIKE', $values, 0, 1);
1702     }
1703     elseif (is_string($values) && strpos($values, '%') !== FALSE) {
1704       $result = array($id, 'LIKE', $values, 0, 0);
1705     }
1706     elseif ($id == 'contact_type' ||
1707       (!empty($values) && is_array($values) && !in_array(key($values), CRM_Core_DAO::acceptedSQLOperators(), TRUE))
1708     ) {
1709       $result = array($id, 'IN', $values, 0, $wildcard);
1710     }
1711     else {
1712       $result = array($id, '=', $values, 0, $wildcard);
1713     }
1714
1715     return $result;
1716   }
1717
1718   /**
1719    * Get the where clause for a single field.
1720    *
1721    * @param array $values
1722    */
1723   public function whereClauseSingle(&$values) {
1724     // do not process custom fields or prefixed contact ids or component params
1725     if (CRM_Core_BAO_CustomField::getKeyID($values[0]) ||
1726       (substr($values[0], 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) ||
1727       (substr($values[0], 0, 13) == 'contribution_') ||
1728       (substr($values[0], 0, 6) == 'event_') ||
1729       (substr($values[0], 0, 12) == 'participant_') ||
1730       (substr($values[0], 0, 7) == 'member_') ||
1731       (substr($values[0], 0, 6) == 'grant_') ||
1732       (substr($values[0], 0, 7) == 'pledge_') ||
1733       (substr($values[0], 0, 5) == 'case_') ||
1734       (substr($values[0], 0, 10) == 'financial_') ||
1735       (substr($values[0], 0, 8) == 'payment_') ||
1736       (substr($values[0], 0, 11) == 'membership_')
1737     ) {
1738       return;
1739     }
1740
1741     // skip for hook injected fields / params
1742     $extFields = CRM_Contact_BAO_Query_Hook::singleton()->getFields();
1743     if (array_key_exists($values[0], $extFields)) {
1744       return;
1745     }
1746
1747     switch ($values[0]) {
1748       case 'deleted_contacts':
1749         $this->deletedContacts($values);
1750         return;
1751
1752       case 'contact_sub_type':
1753         $this->contactSubType($values);
1754         return;
1755
1756       case 'group':
1757         $this->group($values);
1758         return;
1759
1760       case 'group_type':
1761         // so we resolve this into a list of groups & proceed as if they had been
1762         // handed in
1763         list($name, $op, $value, $grouping, $wildcard) = $values;
1764         $values[0] = 'group';
1765         $values[1] = 'IN';
1766         $this->_paramLookup['group'][0][0] = 'group';
1767         $this->_paramLookup['group'][0][1] = 'IN';
1768         $this->_paramLookup['group'][0][2] = $values[2] = $this->getGroupsFromTypeCriteria($value);
1769         $this->group($values);
1770         return;
1771
1772       // case tag comes from find contacts
1773       case 'tag_search':
1774         $this->tagSearch($values);
1775         return;
1776
1777       case 'tag':
1778       case 'contact_tags':
1779         $this->tag($values);
1780         return;
1781
1782       case 'note':
1783       case 'note_body':
1784       case 'note_subject':
1785         $this->notes($values);
1786         return;
1787
1788       case 'uf_user':
1789         $this->ufUser($values);
1790         return;
1791
1792       case 'sort_name':
1793       case 'display_name':
1794         $this->sortName($values);
1795         return;
1796
1797       case 'addressee':
1798       case 'postal_greeting':
1799       case 'email_greeting':
1800         $this->greetings($values);
1801         return;
1802
1803       case 'email':
1804         $this->email($values);
1805         return;
1806
1807       case 'phone_numeric':
1808         $this->phone_numeric($values);
1809         return;
1810
1811       case 'phone_phone_type_id':
1812       case 'phone_location_type_id':
1813         $this->phone_option_group($values);
1814         return;
1815
1816       case 'street_address':
1817         $this->street_address($values);
1818         return;
1819
1820       case 'street_number':
1821         $this->street_number($values);
1822         return;
1823
1824       case 'sortByCharacter':
1825         $this->sortByCharacter($values);
1826         return;
1827
1828       case 'location_type':
1829         $this->locationType($values);
1830         return;
1831
1832       case 'county':
1833         $this->county($values);
1834         return;
1835
1836       case 'state_province':
1837         $this->stateProvince($values);
1838         return;
1839
1840       case 'country':
1841         $this->country($values, FALSE);
1842         return;
1843
1844       case 'postal_code':
1845       case 'postal_code_low':
1846       case 'postal_code_high':
1847         $this->postalCode($values);
1848         return;
1849
1850       case 'activity_date':
1851       case 'activity_date_low':
1852       case 'activity_date_high':
1853       case 'activity_role':
1854       case 'activity_status_id':
1855       case 'activity_status':
1856       case 'followup_parent_id':
1857       case 'parent_id':
1858       case 'source_contact_id':
1859       case 'activity_subject':
1860       case 'test_activities':
1861       case 'activity_type_id':
1862       case 'activity_type':
1863       case 'activity_survey_id':
1864       case 'activity_tags':
1865       case 'activity_taglist':
1866       case 'activity_test':
1867       case 'activity_campaign_id':
1868       case 'activity_engagement_level':
1869       case 'activity_id':
1870       case 'activity_result':
1871       case 'source_contact':
1872         CRM_Activity_BAO_Query::whereClauseSingle($values, $this);
1873         return;
1874
1875       case 'age_low':
1876       case 'age_high':
1877       case 'birth_date_low':
1878       case 'birth_date_high':
1879       case 'deceased_date_low':
1880       case 'deceased_date_high':
1881         $this->demographics($values);
1882         return;
1883
1884       case 'age_asof_date':
1885         // handled by demographics
1886         return;
1887
1888       case 'log_date_low':
1889       case 'log_date_high':
1890         $this->modifiedDates($values);
1891         return;
1892
1893       case 'changed_by':
1894         $this->changeLog($values);
1895         return;
1896
1897       case 'do_not_phone':
1898       case 'do_not_email':
1899       case 'do_not_mail':
1900       case 'do_not_sms':
1901       case 'do_not_trade':
1902       case 'is_opt_out':
1903         $this->privacy($values);
1904         return;
1905
1906       case 'privacy_options':
1907         $this->privacyOptions($values);
1908         return;
1909
1910       case 'privacy_operator':
1911       case 'privacy_toggle':
1912         // these are handled by privacy options
1913         return;
1914
1915       case 'preferred_communication_method':
1916         $this->preferredCommunication($values);
1917         return;
1918
1919       case 'relation_type_id':
1920       case 'relation_start_date_high':
1921       case 'relation_start_date_low':
1922       case 'relation_end_date_high':
1923       case 'relation_end_date_low':
1924       case 'relation_target_name':
1925       case 'relation_status':
1926       case 'relation_date_low':
1927       case 'relation_date_high':
1928         $this->relationship($values);
1929         $this->_relationshipValuesAdded = TRUE;
1930         return;
1931
1932       case 'task_status_id':
1933         $this->task($values);
1934         return;
1935
1936       case 'task_id':
1937         // since this case is handled with the above
1938         return;
1939
1940       case 'prox_distance':
1941         CRM_Contact_BAO_ProximityQuery::process($this, $values);
1942         return;
1943
1944       case 'prox_street_address':
1945       case 'prox_city':
1946       case 'prox_postal_code':
1947       case 'prox_state_province_id':
1948       case 'prox_country_id':
1949         // handled by the proximity_distance clause
1950         return;
1951
1952       default:
1953         $this->restWhere($values);
1954         return;
1955     }
1956   }
1957
1958   /**
1959    * Given a list of conditions in params generate the required where clause.
1960    *
1961    * @return string
1962    */
1963   public function whereClause() {
1964     $this->_where[0] = array();
1965     $this->_qill[0] = array();
1966
1967     $this->includeContactIds();
1968     if (!empty($this->_params)) {
1969       foreach (array_keys($this->_params) as $id) {
1970         if (empty($this->_params[$id][0])) {
1971           continue;
1972         }
1973         // check for both id and contact_id
1974         if ($this->_params[$id][0] == 'id' || $this->_params[$id][0] == 'contact_id') {
1975           $this->_where[0][] = self::buildClause("contact_a.id", $this->_params[$id][1], $this->_params[$id][2]);
1976         }
1977         else {
1978           $this->whereClauseSingle($this->_params[$id]);
1979         }
1980       }
1981
1982       CRM_Core_Component::alterQuery($this, 'where');
1983
1984       CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'where');
1985     }
1986
1987     if ($this->_customQuery) {
1988       // Added following if condition to avoid the wrong value display for 'my account' / any UF info.
1989       // Hope it wont affect the other part of civicrm.. if it does please remove it.
1990       if (!empty($this->_customQuery->_where)) {
1991         $this->_where = CRM_Utils_Array::crmArrayMerge($this->_where, $this->_customQuery->_where);
1992       }
1993
1994       $this->_qill = CRM_Utils_Array::crmArrayMerge($this->_qill, $this->_customQuery->_qill);
1995     }
1996
1997     $clauses = array();
1998     $andClauses = array();
1999
2000     $validClauses = 0;
2001     if (!empty($this->_where)) {
2002       foreach ($this->_where as $grouping => $values) {
2003         if ($grouping > 0 && !empty($values)) {
2004           $clauses[$grouping] = ' ( ' . implode(" {$this->_operator} ", $values) . ' ) ';
2005           $validClauses++;
2006         }
2007       }
2008
2009       if (!empty($this->_where[0])) {
2010         $andClauses[] = ' ( ' . implode(" {$this->_operator} ", $this->_where[0]) . ' ) ';
2011       }
2012       if (!empty($clauses)) {
2013         $andClauses[] = ' ( ' . implode(' OR ', $clauses) . ' ) ';
2014       }
2015
2016       if ($validClauses > 1) {
2017         $this->_useDistinct = TRUE;
2018       }
2019     }
2020
2021     return implode(' AND ', $andClauses);
2022   }
2023
2024   /**
2025    * Generate where clause for any parameters not already handled.
2026    *
2027    * @param array $values
2028    *
2029    * @throws Exception
2030    */
2031   public function restWhere(&$values) {
2032     $name = CRM_Utils_Array::value(0, $values);
2033     $op = CRM_Utils_Array::value(1, $values);
2034     $value = CRM_Utils_Array::value(2, $values);
2035     $grouping = CRM_Utils_Array::value(3, $values);
2036     $wildcard = CRM_Utils_Array::value(4, $values);
2037
2038     if (isset($grouping) && empty($this->_where[$grouping])) {
2039       $this->_where[$grouping] = array();
2040     }
2041
2042     $multipleFields = array('url');
2043
2044     //check if the location type exits for fields
2045     $lType = '';
2046     $locType = explode('-', $name);
2047
2048     if (!in_array($locType[0], $multipleFields)) {
2049       //add phone type if exists
2050       if (isset($locType[2]) && $locType[2]) {
2051         $locType[2] = CRM_Core_DAO::escapeString($locType[2]);
2052       }
2053     }
2054
2055     $field = CRM_Utils_Array::value($name, $this->_fields);
2056
2057     if (!$field) {
2058       $field = CRM_Utils_Array::value($locType[0], $this->_fields);
2059
2060       if (!$field) {
2061         return;
2062       }
2063     }
2064
2065     $setTables = TRUE;
2066
2067     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
2068     $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
2069
2070     if (substr($name, 0, 14) === 'state_province') {
2071       if (isset($locType[1]) && is_numeric($locType[1])) {
2072         $setTables = FALSE;
2073         $aName = "{$locationType[$locType[1]]}-address";
2074         $where = "`$aName`.state_province_id";
2075       }
2076       else {
2077         $where = "civicrm_address.state_province_id";
2078       }
2079
2080       $this->_where[$grouping][] = self::buildClause($where, $op, $value);
2081       list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_Address', "state_province_id", $value, $op);
2082       $this->_qill[$grouping][] = ts("State %1 %2", array(1 => $qillop, 2 => $qillVal));
2083     }
2084     elseif (!empty($field['pseudoconstant'])) {
2085       $this->optionValueQuery(
2086         $name, $op, $value, $grouping,
2087         'CRM_Contact_DAO_Contact',
2088         $field,
2089         $field['title'],
2090         'String',
2091         TRUE
2092       );
2093       if ($name == 'gender_id') {
2094         self::$_openedPanes[ts('Demographics')] = TRUE;
2095       }
2096     }
2097     elseif (substr($name, 0, 7) === 'country' || substr($name, 0, 6) === 'county') {
2098       $name = (substr($name, 0, 7) === 'country') ? "country_id" : "county_id";
2099       if (isset($locType[1]) && is_numeric($locType[1])) {
2100         $setTables = FALSE;
2101         $aName = "{$locationType[$locType[1]]}-address";
2102         $where = "`$aName`.$name";
2103       }
2104       else {
2105         $where = "civicrm_address.$name";
2106       }
2107
2108       $this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
2109
2110       if ($lType) {
2111         $field['title'] .= " ($lType)";
2112       }
2113       list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
2114       $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['title'], 2 => $qillop, 3 => $qillVal));
2115     }
2116     elseif ($name === 'world_region') {
2117       $this->optionValueQuery(
2118         $name, $op, $value, $grouping,
2119         NULL,
2120         $field,
2121         ts('World Region'),
2122         'Positive',
2123         TRUE
2124       );
2125     }
2126     elseif ($name === 'is_deceased') {
2127       $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
2128       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2129       self::$_openedPanes[ts('Demographics')] = TRUE;
2130     }
2131     elseif ($name === 'created_date' || $name === 'modified_date' || $name === 'deceased_date' || $name === 'birth_date') {
2132       $appendDateTime = TRUE;
2133       if ($name === 'deceased_date' || $name === 'birth_date') {
2134         $appendDateTime = FALSE;
2135         self::$_openedPanes[ts('Demographics')] = TRUE;
2136       }
2137       $this->dateQueryBuilder($values, 'contact_a', $name, $name, $field['title'], $appendDateTime);
2138     }
2139     elseif ($name === 'contact_id') {
2140       if (is_int($value)) {
2141         $this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
2142         $this->_qill[$grouping][] = "$field[title] $op $value";
2143       }
2144     }
2145     elseif ($name === 'name') {
2146       $value = $strtolower(CRM_Core_DAO::escapeString($value));
2147       if ($wildcard) {
2148         $value = "%$value%";
2149         $op = 'LIKE';
2150       }
2151       $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
2152       $this->_where[$grouping][] = self::buildClause($wc, $op, "'$value'");
2153       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2154     }
2155     elseif ($name === 'current_employer') {
2156       $value = $strtolower(CRM_Core_DAO::escapeString($value));
2157       if ($wildcard) {
2158         $value = "%$value%";
2159         $op = 'LIKE';
2160       }
2161       $wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
2162       $ceWhereClause = self::buildClause($wc, $op,
2163         $value
2164       );
2165       $ceWhereClause .= " AND contact_a.contact_type = 'Individual'";
2166       $this->_where[$grouping][] = $ceWhereClause;
2167       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2168     }
2169     elseif ($name === 'email_greeting') {
2170       $filterCondition = array('greeting_type' => 'email_greeting');
2171       $this->optionValueQuery(
2172         $name, $op, $value, $grouping,
2173         CRM_Core_PseudoConstant::greeting($filterCondition),
2174         $field,
2175         ts('Email Greeting')
2176       );
2177     }
2178     elseif ($name === 'postal_greeting') {
2179       $filterCondition = array('greeting_type' => 'postal_greeting');
2180       $this->optionValueQuery(
2181         $name, $op, $value, $grouping,
2182         CRM_Core_PseudoConstant::greeting($filterCondition),
2183         $field,
2184         ts('Postal Greeting')
2185       );
2186     }
2187     elseif ($name === 'addressee') {
2188       $filterCondition = array('greeting_type' => 'addressee');
2189       $this->optionValueQuery(
2190         $name, $op, $value, $grouping,
2191         CRM_Core_PseudoConstant::greeting($filterCondition),
2192         $field,
2193         ts('Addressee')
2194       );
2195     }
2196     elseif (substr($name, 0, 4) === 'url-') {
2197       $tName = 'civicrm_website';
2198       $this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
2199       $value = $strtolower(CRM_Core_DAO::escapeString($value));
2200       if ($wildcard) {
2201         $value = "%$value%";
2202         $op = 'LIKE';
2203       }
2204
2205       $wc = 'civicrm_website.url';
2206       $this->_where[$grouping][] = $d = self::buildClause($wc, $op, $value);
2207       $this->_qill[$grouping][] = "$field[title] $op \"$value\"";
2208     }
2209     elseif ($name === 'contact_is_deleted') {
2210       $this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
2211       list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
2212       $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['title'], 2 => $qillop, 3 => $qillVal));
2213     }
2214     elseif (!empty($field['where'])) {
2215       $type = NULL;
2216       if (!empty($field['type'])) {
2217         $type = CRM_Utils_Type::typeToString($field['type']);
2218       }
2219
2220       list($tableName, $fieldName) = explode('.', $field['where'], 2);
2221
2222       if (isset($locType[1]) &&
2223         is_numeric($locType[1])
2224       ) {
2225         $setTables = FALSE;
2226
2227         //get the location name
2228         list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
2229
2230         $fieldName = "LOWER(`$tName`.$fldName)";
2231
2232         // we set both _tables & whereTables because whereTables doesn't seem to do what the name implies it should
2233         $this->_tables[$tName] = $this->_whereTables[$tName] = 1;
2234
2235       }
2236       else {
2237         if ($tableName == 'civicrm_contact') {
2238           $fieldName = "LOWER(contact_a.{$fieldName})";
2239         }
2240         else {
2241           if ($op != 'IN' && !is_numeric($value)) {
2242             $fieldName = "LOWER({$field['where']})";
2243           }
2244           else {
2245             $fieldName = "{$field['where']}";
2246           }
2247         }
2248       }
2249
2250       list($qillop, $qillVal) = self::buildQillForFieldValue(NULL, $field['title'], $value, $op);
2251       $this->_qill[$grouping][] = ts("%1 %2 %3", array(
2252         1 => $field['title'],
2253         2 => $qillop,
2254         3 => (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) ? $qillVal : "'$qillVal'"));
2255
2256       if (is_array($value)) {
2257         // traditionally an array being passed has been a fatal error. We can take advantage of this to add support
2258         // for api style operators for functions that hit this point without worrying about regression
2259         // (the previous comments indicated the condition for hitting this point were unknown
2260         // per CRM-14743 we are adding modified_date & created_date operator support
2261         $operations = array_keys($value);
2262         foreach ($operations as $operator) {
2263           if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) {
2264             //Via Contact get api value is not in array(operator => array(values)) format ONLY for IN/NOT IN operators
2265             //so this condition will satisfy the search for now
2266             if (strpos($op, 'IN') !== FALSE) {
2267               $value = array($op => $value);
2268             }
2269             // we don't know when this might happen
2270             else {
2271               CRM_Core_Error::fatal(ts("%1 is not a valid operator", array(1 => $operator)));
2272             }
2273           }
2274         }
2275         $this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($fieldName, $value, $type);
2276       }
2277       else {
2278         if (!strpos($op, 'IN')) {
2279           $value = $strtolower($value);
2280         }
2281         if ($wildcard) {
2282           $value = "%$value%";
2283           $op = 'LIKE';
2284         }
2285
2286         $this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
2287       }
2288     }
2289
2290     if ($setTables && isset($field['where'])) {
2291       list($tableName, $fieldName) = explode('.', $field['where'], 2);
2292       if (isset($tableName)) {
2293         $this->_tables[$tableName] = 1;
2294         $this->_whereTables[$tableName] = 1;
2295       }
2296     }
2297   }
2298
2299
2300   /**
2301    * @param $where
2302    * @param $locType
2303    *
2304    * @return array
2305    * @throws Exception
2306    */
2307   public static function getLocationTableName(&$where, &$locType) {
2308     if (isset($locType[1]) && is_numeric($locType[1])) {
2309       list($tbName, $fldName) = explode(".", $where);
2310
2311       //get the location name
2312       $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
2313       $specialFields = array('email', 'im', 'phone', 'openid', 'phone_ext');
2314       if (in_array($locType[0], $specialFields)) {
2315         //hack to fix / special handing for phone_ext
2316         if ($locType[0] == 'phone_ext') {
2317           $locType[0] = 'phone';
2318         }
2319         if (isset($locType[2]) && $locType[2]) {
2320           $tName = "{$locationType[$locType[1]]}-{$locType[0]}-{$locType[2]}";
2321         }
2322         else {
2323           $tName = "{$locationType[$locType[1]]}-{$locType[0]}";
2324         }
2325       }
2326       elseif (in_array($locType[0],
2327         array(
2328           'address_name',
2329           'street_address',
2330           'street_name',
2331           'street_number_suffix',
2332           'street_unit',
2333           'supplemental_address_1',
2334           'supplemental_address_2',
2335           'city',
2336           'postal_code',
2337           'postal_code_suffix',
2338           'geo_code_1',
2339           'geo_code_2',
2340           'master_id',
2341         )
2342       )) {
2343         //fix for search by profile with address fields.
2344         $tName = "{$locationType[$locType[1]]}-address";
2345       }
2346       elseif (in_array($locType[0],
2347           array(
2348             'on_hold',
2349             'signature_html',
2350             'signature_text',
2351             'is_bulkmail',
2352           )
2353         )) {
2354         $tName = "{$locationType[$locType[1]]}-email";
2355       }
2356       elseif ($locType[0] == 'provider_id') {
2357         $tName = "{$locationType[$locType[1]]}-im";
2358       }
2359       elseif ($locType[0] == 'openid') {
2360         $tName = "{$locationType[$locType[1]]}-openid";
2361       }
2362       else {
2363         $tName = "{$locationType[$locType[1]]}-{$locType[0]}";
2364       }
2365       $tName = str_replace(' ', '_', $tName);
2366       return array($tName, $fldName);
2367     }
2368     CRM_Core_Error::fatal();
2369   }
2370
2371   /**
2372    * Given a result dao, extract the values and return that array
2373    *
2374    * @param CRM_Core_DAO $dao
2375    *
2376    * @return array
2377    *   values for this query
2378    */
2379   public function store($dao) {
2380     $value = array();
2381
2382     foreach ($this->_element as $key => $dontCare) {
2383       if (property_exists($dao, $key)) {
2384         if (strpos($key, '-') !== FALSE) {
2385           $values = explode('-', $key);
2386           $lastElement = array_pop($values);
2387           $current = &$value;
2388           $cnt = count($values);
2389           $count = 1;
2390           foreach ($values as $v) {
2391             if (!array_key_exists($v, $current)) {
2392               $current[$v] = array();
2393             }
2394             //bad hack for im_provider
2395             if ($lastElement == 'provider_id') {
2396               if ($count < $cnt) {
2397                 $current = &$current[$v];
2398               }
2399               else {
2400                 $lastElement = "{$v}_{$lastElement}";
2401               }
2402             }
2403             else {
2404               $current = &$current[$v];
2405             }
2406             $count++;
2407           }
2408
2409           $current[$lastElement] = $dao->$key;
2410         }
2411         else {
2412           $value[$key] = $dao->$key;
2413         }
2414       }
2415     }
2416     return $value;
2417   }
2418
2419   /**
2420    * Getter for tables array.
2421    *
2422    * @return array
2423    */
2424   public function tables() {
2425     return $this->_tables;
2426   }
2427
2428   /**
2429    * Where tables is sometimes used to create the from clause, but, not reliably, set this AND set tables
2430    * It's unclear the intent - there is a 'simpleFrom' clause which takes whereTables into account & a fromClause which doesn't
2431    * logic may have eroded
2432    * @return array
2433    */
2434   public function whereTables() {
2435     return $this->_whereTables;
2436   }
2437
2438   /**
2439    * Generate the where clause (used in match contacts and permissions)
2440    *
2441    * @param array $params
2442    * @param array $fields
2443    * @param array $tables
2444    * @param $whereTables
2445    * @param bool $strict
2446    *
2447    * @return string
2448    */
2449   public static function getWhereClause($params, $fields, &$tables, &$whereTables, $strict = FALSE) {
2450     $query = new CRM_Contact_BAO_Query($params, NULL, $fields,
2451       FALSE, $strict
2452     );
2453
2454     $tables = array_merge($query->tables(), $tables);
2455     $whereTables = array_merge($query->whereTables(), $whereTables);
2456
2457     return $query->_whereClause;
2458   }
2459
2460   /**
2461    * Create the from clause.
2462    *
2463    * @param array $tables
2464    *   Tables that need to be included in this from clause.
2465    *                      if null, return mimimal from clause (i.e. civicrm_contact)
2466    * @param array $inner
2467    *   Tables that should be inner-joined.
2468    * @param array $right
2469    *   Tables that should be right-joined.
2470    *
2471    * @param bool $primaryLocation
2472    * @param int $mode
2473    *
2474    * @return string
2475    *   the from clause
2476    */
2477   public static function fromClause(&$tables, $inner = NULL, $right = NULL, $primaryLocation = TRUE, $mode = 1) {
2478
2479     $from = ' FROM civicrm_contact contact_a';
2480     if (empty($tables)) {
2481       return $from;
2482     }
2483
2484     if (!empty($tables['civicrm_worldregion'])) {
2485       $tables = array_merge(array('civicrm_country' => 1), $tables);
2486     }
2487
2488     if ((!empty($tables['civicrm_state_province']) || !empty($tables['civicrm_country']) ||
2489         CRM_Utils_Array::value('civicrm_county', $tables)
2490       ) && empty($tables['civicrm_address'])
2491     ) {
2492       $tables = array_merge(array('civicrm_address' => 1),
2493         $tables
2494       );
2495     }
2496
2497     // add group_contact and group_contact_cache table if group table is present
2498     if (!empty($tables['civicrm_group'])) {
2499       if (empty($tables['civicrm_group_contact'])) {
2500         $tables['civicrm_group_contact'] = " LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id AND civicrm_group_contact.status = 'Added' ";
2501       }
2502       if (empty($tables['civicrm_group_contact_cache'])) {
2503         $tables['civicrm_group_contact_cache'] = " LEFT JOIN civicrm_group_contact_cache ON civicrm_group_contact_cache.contact_id = contact_a.id ";
2504       }
2505     }
2506
2507     // add group_contact and group table is subscription history is present
2508     if (!empty($tables['civicrm_subscription_history']) && empty($tables['civicrm_group'])) {
2509       $tables = array_merge(array(
2510           'civicrm_group' => 1,
2511           'civicrm_group_contact' => 1,
2512         ),
2513         $tables
2514       );
2515     }
2516
2517     // to handle table dependencies of components
2518     CRM_Core_Component::tableNames($tables);
2519     // to handle table dependencies of hook injected tables
2520     CRM_Contact_BAO_Query_Hook::singleton()->setTableDependency($tables);
2521
2522     //format the table list according to the weight
2523     $info = CRM_Core_TableHierarchy::info();
2524
2525     foreach ($tables as $key => $value) {
2526       $k = 99;
2527       if (strpos($key, '-') !== FALSE) {
2528         $keyArray = explode('-', $key);
2529         $k = CRM_Utils_Array::value('civicrm_' . $keyArray[1], $info, 99);
2530       }
2531       elseif (strpos($key, '_') !== FALSE) {
2532         $keyArray = explode('_', $key);
2533         if (is_numeric(array_pop($keyArray))) {
2534           $k = CRM_Utils_Array::value(implode('_', $keyArray), $info, 99);
2535         }
2536         else {
2537           $k = CRM_Utils_Array::value($key, $info, 99);
2538         }
2539       }
2540       else {
2541         $k = CRM_Utils_Array::value($key, $info, 99);
2542       }
2543       $tempTable[$k . ".$key"] = $key;
2544     }
2545     ksort($tempTable);
2546     $newTables = array();
2547     foreach ($tempTable as $key) {
2548       $newTables[$key] = $tables[$key];
2549     }
2550
2551     $tables = $newTables;
2552
2553     foreach ($tables as $name => $value) {
2554       if (!$value) {
2555         continue;
2556       }
2557
2558       if (!empty($inner[$name])) {
2559         $side = 'INNER';
2560       }
2561       elseif (!empty($right[$name])) {
2562         $side = 'RIGHT';
2563       }
2564       else {
2565         $side = 'LEFT';
2566       }
2567
2568       if ($value != 1) {
2569         // if there is already a join statement in value, use value itself
2570         if (strpos($value, 'JOIN')) {
2571           $from .= " $value ";
2572         }
2573         else {
2574           $from .= " $side JOIN $name ON ( $value ) ";
2575         }
2576         continue;
2577       }
2578       switch ($name) {
2579         case 'civicrm_address':
2580           if ($primaryLocation) {
2581             $from .= " $side JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )";
2582           }
2583           else {
2584             //CRM-14263 further handling of address joins further down...
2585             $from .= " $side JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) ";
2586           }
2587           continue;
2588
2589         case 'civicrm_phone':
2590           $from .= " $side JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) ";
2591           continue;
2592
2593         case 'civicrm_email':
2594           $from .= " $side JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) ";
2595           continue;
2596
2597         case 'civicrm_im':
2598           $from .= " $side JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) ";
2599           continue;
2600
2601         case 'im_provider':
2602           $from .= " $side JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id) ";
2603           $from .= " $side JOIN civicrm_option_group option_group_imProvider ON option_group_imProvider.name = 'instant_messenger_service'";
2604           $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)";
2605           continue;
2606
2607         case 'civicrm_openid':
2608           $from .= " $side JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id AND civicrm_openid.is_primary = 1 )";
2609           continue;
2610
2611         case 'civicrm_worldregion':
2612           $from .= " $side JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id ";
2613           $from .= " $side JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ";
2614           continue;
2615
2616         case 'civicrm_location_type':
2617           $from .= " $side JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id ";
2618           continue;
2619
2620         case 'civicrm_group':
2621           $from .= " $side JOIN civicrm_group ON (civicrm_group.id = civicrm_group_contact.group_id OR civicrm_group.id = civicrm_group_contact_cache.group_id) ";
2622           continue;
2623
2624         case 'civicrm_group_contact':
2625           $from .= " $side JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id ";
2626           continue;
2627
2628         case 'civicrm_group_contact_cache':
2629           $from .= " $side JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id ";
2630           continue;
2631
2632         case 'civicrm_activity':
2633         case 'civicrm_activity_tag':
2634         case 'activity_type':
2635         case 'activity_status':
2636         case 'parent_id':
2637         case 'civicrm_activity_contact':
2638         case 'source_contact':
2639           $from .= CRM_Activity_BAO_Query::from($name, $mode, $side);
2640           continue;
2641
2642         case 'civicrm_entity_tag':
2643           $from .= " $side JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
2644                                                               civicrm_entity_tag.entity_id = contact_a.id ) ";
2645           continue;
2646
2647         case 'civicrm_note':
2648           $from .= " $side JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND
2649                                                         contact_a.id = civicrm_note.entity_id ) ";
2650           continue;
2651
2652         case 'civicrm_subscription_history':
2653           $from .= " $side JOIN civicrm_subscription_history
2654                                    ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id
2655                                   AND civicrm_group_contact.group_id =  civicrm_subscription_history.group_id";
2656           continue;
2657
2658         case 'civicrm_relationship':
2659           if (self::$_relType == 'reciprocal') {
2660             if (self::$_relationshipTempTable) {
2661               // we have a temptable to join on
2662               $tbl = self::$_relationshipTempTable;
2663               $from .= " INNER JOIN {$tbl} civicrm_relationship ON civicrm_relationship.contact_id = contact_a.id";
2664             }
2665             else {
2666               $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id)";
2667               $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)";
2668             }
2669           }
2670           elseif (self::$_relType == 'b') {
2671             $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id )";
2672             $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )";
2673           }
2674           else {
2675             $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id )";
2676             $from .= " $side JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )";
2677           }
2678           continue;
2679
2680         case 'civicrm_log':
2681           $from .= " INNER JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact')";
2682           $from .= " INNER JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id)";
2683           continue;
2684
2685         case 'civicrm_tag':
2686           $from .= " $side  JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id ";
2687           continue;
2688
2689         case 'civicrm_grant':
2690           $from .= CRM_Grant_BAO_Query::from($name, $mode, $side);
2691           continue;
2692
2693         case 'civicrm_website':
2694           $from .= " $side JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id ";
2695           continue;
2696
2697         default:
2698           $locationTypeName = '';
2699           if (strpos($name, '-address') != 0) {
2700             $locationTypeName = 'address';
2701           }
2702           elseif (strpos($name, '-phone') != 0) {
2703             $locationTypeName = 'phone';
2704           }
2705           elseif (strpos($name, '-email') != 0) {
2706             $locationTypeName = 'email';
2707           }
2708           elseif (strpos($name, '-im') != 0) {
2709             $locationTypeName = 'im';
2710           }
2711           elseif (strpos($name, '-openid') != 0) {
2712             $locationTypeName = 'openid';
2713           }
2714
2715           if ($locationTypeName) {
2716             //we have a join on an location table - possibly in conjunction with search builder - CRM-14263
2717             $parts = explode('-', $name);
2718             $locationID = array_search($parts[0], CRM_Core_BAO_Address::buildOptions('location_type_id', 'get', array('name' => $parts[0])));
2719             $from .= " $side JOIN civicrm_{$locationTypeName} `{$name}` ON ( contact_a.id = `{$name}`.contact_id ) and `{$name}`.location_type_id = $locationID ";
2720           }
2721           else {
2722             $from .= CRM_Core_Component::from($name, $mode, $side);
2723           }
2724           $from .= CRM_Contact_BAO_Query_Hook::singleton()->buildSearchfrom($name, $mode, $side);
2725
2726           continue;
2727       }
2728     }
2729     return $from;
2730   }
2731
2732   /**
2733    * WHERE / QILL clause for deleted_contacts
2734    *
2735    * @param array $values
2736    */
2737   public function deletedContacts($values) {
2738     list($_, $_, $value, $grouping, $_) = $values;
2739     if ($value) {
2740       // *prepend* to the relevant grouping as this is quite an important factor
2741       array_unshift($this->_qill[$grouping], ts('Search in Trash'));
2742     }
2743   }
2744
2745   /**
2746    * Where / qill clause for contact_type
2747    *
2748    * @param $values
2749    */
2750   public function contactType(&$values) {
2751     list($name, $op, $value, $grouping, $wildcard) = $values;
2752
2753     $subTypes = array();
2754     $clause = array();
2755
2756     // account for search builder mapping multiple values
2757     if (!is_array($value)) {
2758       $values = self::parseSearchBuilderString($value, 'String');
2759       if (is_array($values)) {
2760         $value = array_flip($values);
2761       }
2762     }
2763
2764     if (is_array($value)) {
2765       foreach ($value as $k => $v) {
2766         // fix for CRM-771
2767         if ($k) {
2768           $subType = NULL;
2769           $contactType = $k;
2770           if (strpos($k, CRM_Core_DAO::VALUE_SEPARATOR)) {
2771             list($contactType, $subType) = explode(CRM_Core_DAO::VALUE_SEPARATOR, $k, 2);
2772           }
2773
2774           if (!empty($subType)) {
2775             $subTypes[$subType] = 1;
2776           }
2777           $clause[$contactType] = "'" . CRM_Utils_Type::escape($contactType, 'String') . "'";
2778         }
2779       }
2780     }
2781     else {
2782       $contactTypeANDSubType = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value, 2);
2783       $contactType = $contactTypeANDSubType[0];
2784       $subType = CRM_Utils_Array::value(1, $contactTypeANDSubType);
2785       if (!empty($subType)) {
2786         $subTypes[$subType] = 1;
2787       }
2788       $clause[$contactType] = "'" . CRM_Utils_Type::escape($contactType, 'String') . "'";
2789     }
2790
2791     // fix for CRM-771
2792     if (!empty($clause)) {
2793       $quill = $clause;
2794       if ($op == 'IN' || $op == 'NOT IN') {
2795         $this->_where[$grouping][] = "contact_a.contact_type $op (" . implode(',', $clause) . ')';
2796       }
2797       else {
2798         $type = array_pop($clause);
2799         $this->_where[$grouping][] = self::buildClause("contact_a.contact_type", $op, $contactType);
2800       }
2801
2802       $this->_qill[$grouping][] = ts('Contact Type') . " $op " . implode(' ' . ts('or') . ' ', $quill);
2803
2804       if (!empty($subTypes)) {
2805         $this->includeContactSubTypes($subTypes, $grouping);
2806       }
2807     }
2808   }
2809
2810   /**
2811    * Where / qill clause for contact_sub_type
2812    *
2813    * @param $values
2814    */
2815   public function contactSubType(&$values) {
2816     list($name, $op, $value, $grouping, $wildcard) = $values;
2817     $this->includeContactSubTypes($value, $grouping, $op);
2818   }
2819
2820   /**
2821    * @param $value
2822    * @param $grouping
2823    * @param string $op
2824    */
2825   public function includeContactSubTypes($value, $grouping, $op = 'LIKE') {
2826
2827     $clause = array();
2828     $alias = "contact_a.contact_sub_type";
2829     $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
2830
2831     $op = str_replace('IN', 'LIKE', $op);
2832     $op = str_replace('=', 'LIKE', $op);
2833     $op = str_replace('!', 'NOT ', $op);
2834
2835     if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) {
2836       $this->_where[$grouping][] = self::buildClause($alias, $op, $value, 'String');
2837     }
2838     elseif (is_array($value)) {
2839       foreach ($value as $k => $v) {
2840         if (!empty($k)) {
2841           $clause[$k] = "($alias $op '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($k, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')";
2842         }
2843       }
2844     }
2845     else {
2846       $clause[$value] = "($alias $op '%" . CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($value, 'String') . CRM_Core_DAO::VALUE_SEPARATOR . "%')";
2847     }
2848
2849     if (!empty($clause)) {
2850       $this->_where[$grouping][] = "( " . implode(' OR ', $clause) . " )";
2851     }
2852     $this->_qill[$grouping][] = ts('Contact Subtype %1 ', array(1 => $qillOperators[$op])) . implode(' ' . ts('or') . ' ', array_keys($clause));
2853   }
2854
2855   /**
2856    * Where / qill clause for groups
2857    *
2858    * @param $values
2859    */
2860   public function group(&$values) {
2861     list($name, $op, $value, $grouping, $wildcard) = $values;
2862
2863     // If the $value is in OK (operator as key) array format we need to extract the key as operator and value first
2864     if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
2865       $op = key($value);
2866       $value = $value[$op];
2867     }
2868
2869     // Replace pseudo operators from search builder
2870     $op = str_replace('EMPTY', 'NULL', $op);
2871
2872     if (strpos($op, 'NULL')) {
2873       $value = NULL;
2874     }
2875
2876     if (count($value) > 1) {
2877       if (strpos($op, 'IN') === FALSE && strpos($op, 'NULL') === FALSE) {
2878         CRM_Core_Error::fatal(ts("%1 is not a valid operator", array(1 => $op)));
2879       }
2880       $this->_useDistinct = TRUE;
2881     }
2882
2883     if (isset($value)) {
2884       $value = CRM_Utils_Array::value($op, $value, $value);
2885     }
2886
2887     $groupIds = NULL;
2888     $names = array();
2889     $isSmart = FALSE;
2890     $isNotOp = ($op == 'NOT IN' || $op == '!=');
2891
2892     $statii = array();
2893     $gcsValues = $this->getWhereValues('group_contact_status', $grouping);
2894     if ($gcsValues &&
2895       is_array($gcsValues[2])
2896     ) {
2897       foreach ($gcsValues[2] as $k => $v) {
2898         if ($v) {
2899           $statii[] = "'" . CRM_Utils_Type::escape($k, 'String') . "'";
2900         }
2901       }
2902     }
2903     else {
2904       $statii[] = '"Added"';
2905     }
2906
2907     $skipGroup = FALSE;
2908     if (!is_array($value) &&
2909       count($statii) == 1 &&
2910       $statii[0] == '"Added"' &&
2911       !$isNotOp
2912     ) {
2913       if (!empty($value) && CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Group', $value, 'saved_search_id')) {
2914         $isSmart = TRUE;
2915       }
2916     }
2917
2918     $ssClause = $this->addGroupContactCache($value, NULL, "contact_a", $op);
2919     $isSmart = (!$ssClause) ? FALSE : $isSmart;
2920     $groupClause = NULL;
2921
2922     if (!$isSmart) {
2923       $groupIds = implode(',', (array) $value);
2924       $gcTable = "`civicrm_group_contact-{$groupIds}`";
2925       $joinClause = array("contact_a.id = {$gcTable}.contact_id");
2926       if ($statii) {
2927         $joinClause[] = "{$gcTable}.status IN (" . implode(', ', $statii) . ")";
2928       }
2929       $this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")";
2930       if (strpos($op, 'IN') !== FALSE) {
2931         $groupClause = "{$gcTable}.group_id $op ( $groupIds )";
2932       }
2933       elseif ($op == '!=') {
2934         $groupClause = "{$gcTable}.contact_id NOT IN (SELECT contact_id FROM civicrm_group_contact cgc WHERE cgc.group_id = $groupIds)";
2935       }
2936       else {
2937         $groupClause = "{$gcTable}.group_id $op $groupIds";
2938       }
2939     }
2940
2941     if ($ssClause) {
2942       $and = ($op == 'IS NULL') ? 'AND' : 'OR';
2943       if ($groupClause) {
2944         $groupClause = "( ( $groupClause ) $and ( $ssClause ) )";
2945       }
2946       else {
2947         $groupClause = $ssClause;
2948       }
2949     }
2950
2951     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contact_DAO_Group', 'id', $value, $op);
2952     $this->_qill[$grouping][] = ts("Group(s) %1 %2", array(1 => $qillop, 2 => $qillVal));
2953     if (strpos($op, 'NULL') === FALSE) {
2954       $this->_qill[$grouping][] = ts("Group Status %1", array(1 => implode(' ' . ts('or') . ' ', $statii)));
2955     }
2956     if ($groupClause) {
2957       $this->_where[$grouping][] = $groupClause;
2958     }
2959   }
2960
2961   /**
2962    * Function translates selection of group type into a list of groups.
2963    * @param $value
2964    *
2965    * @return array
2966    */
2967   public function getGroupsFromTypeCriteria($value) {
2968     $groupIds = array();
2969     foreach ((array) $value as $groupTypeValue) {
2970       $groupList = CRM_Core_PseudoConstant::group($groupTypeValue);
2971       $groupIds = ($groupIds + $groupList);
2972     }
2973     return $groupIds;
2974   }
2975
2976   /**
2977    * @param array $groups
2978    * @param string $tableAlias
2979    * @param string $joinTable
2980    * @param string $op
2981    *
2982    * @return null|string
2983    */
2984   public function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a", $op) {
2985     $isNullOp = (strpos($op, 'NULL') !== FALSE);
2986     $groupsIds = $groups;
2987     if (!$isNullOp && !$groups) {
2988       return NULL;
2989     }
2990     elseif (strpos($op, 'IN') !== FALSE) {
2991       $groups = array($op => $groups);
2992     }
2993     elseif (is_array($groups) && count($groups)) {
2994       $groups = array('IN' => $groups);
2995     }
2996
2997     // Find all the groups that are part of a saved search.
2998     $smartGroupClause = self::buildClause("id", $op, $groups, 'Int');
2999     $sql = "
3000 SELECT id, cache_date, saved_search_id, children
3001 FROM   civicrm_group
3002 WHERE  $smartGroupClause
3003   AND  ( saved_search_id != 0
3004    OR    saved_search_id IS NOT NULL
3005    OR    children IS NOT NULL )
3006 ";
3007
3008     $group = CRM_Core_DAO::executeQuery($sql);
3009
3010     while ($group->fetch()) {
3011       $this->_useDistinct = TRUE;
3012       if (!$this->_smartGroupCache || $group->cache_date == NULL) {
3013         CRM_Contact_BAO_GroupContactCache::load($group);
3014       }
3015     }
3016
3017     if (!$tableAlias) {
3018       $tableAlias = "`civicrm_group_contact_cache_";
3019       $tableAlias .= ($isNullOp) ? "a`" : implode(',', (array) $groupsIds) . "`";
3020     }
3021
3022     $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.id = {$tableAlias}.contact_id ";
3023     return self::buildClause("{$tableAlias}.group_id", $op, $groups, 'Int');
3024   }
3025
3026   /**
3027    * Where / qill clause for cms users
3028    *
3029    * @param $values
3030    */
3031   public function ufUser(&$values) {
3032     list($name, $op, $value, $grouping, $wildcard) = $values;
3033
3034     if ($value == 1) {
3035       $this->_tables['civicrm_uf_match'] = $this->_whereTables['civicrm_uf_match'] = ' INNER JOIN civicrm_uf_match ON civicrm_uf_match.contact_id = contact_a.id ';
3036
3037       $this->_qill[$grouping][] = ts('CMS User');
3038     }
3039     elseif ($value == 0) {
3040       $this->_tables['civicrm_uf_match'] = $this->_whereTables['civicrm_uf_match'] = ' LEFT JOIN civicrm_uf_match ON civicrm_uf_match.contact_id = contact_a.id ';
3041
3042       $this->_where[$grouping][] = " civicrm_uf_match.contact_id IS NULL";
3043       $this->_qill[$grouping][] = ts('Not a CMS User');
3044     }
3045   }
3046
3047   /**
3048    * All tag search specific.
3049    *
3050    * @param array $values
3051    */
3052   public function tagSearch(&$values) {
3053     list($name, $op, $value, $grouping, $wildcard) = $values;
3054
3055     $op = "LIKE";
3056     $value = "%{$value}%";
3057
3058     $useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
3059     $tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
3060
3061     $etTable = "`civicrm_entity_tag-" . $value . "`";
3062     $tTable = "`civicrm_tag-" . $value . "`";
3063
3064     if ($useAllTagTypes[2]) {
3065       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3066         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id)
3067             LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id  )";
3068
3069       // search tag in cases
3070       $etCaseTable = "`civicrm_entity_case_tag-" . $value . "`";
3071       $tCaseTable = "`civicrm_case_tag-" . $value . "`";
3072       $this->_tables[$etCaseTable] = $this->_whereTables[$etCaseTable]
3073         = " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
3074             LEFT JOIN civicrm_case
3075             ON (civicrm_case_contact.case_id = civicrm_case.id
3076                 AND civicrm_case.is_deleted = 0 )
3077             LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id )
3078             LEFT JOIN civicrm_tag {$tCaseTable} ON ( {$etCaseTable}.tag_id = {$tCaseTable}.id  )";
3079       // search tag in activities
3080       $etActTable = "`civicrm_entity_act_tag-" . $value . "`";
3081       $tActTable = "`civicrm_act_tag-" . $value . "`";
3082       $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
3083       $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
3084
3085       $this->_tables[$etActTable] = $this->_whereTables[$etActTable]
3086         = " LEFT JOIN civicrm_activity_contact
3087             ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
3088             LEFT JOIN civicrm_activity
3089             ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
3090             AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )
3091             LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id )
3092             LEFT JOIN civicrm_tag {$tActTable} ON ( {$etActTable}.tag_id = {$tActTable}.id  )";
3093
3094       $this->_where[$grouping][] = "({$tTable}.name $op '" . $value . "' OR {$tCaseTable}.name $op '" . $value . "' OR {$tActTable}.name $op '" . $value . "')";
3095       $this->_qill[$grouping][] = ts('Tag %1 %2', array(1 => $tagTypesText[2], 2 => $op)) . ' ' . $value;
3096     }
3097     else {
3098       $etTable = "`civicrm_entity_tag-" . $value . "`";
3099       $tTable = "`civicrm_tag-" . $value . "`";
3100       $this->_tables[$etTable] = $this->_whereTables[$etTable] = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND
3101       {$etTable}.entity_table = 'civicrm_contact' )
3102                 LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id  ) ";
3103
3104       $this->_where[$grouping][] = self::buildClause("{$tTable}.name", $op, $value, 'String');
3105       $this->_qill[$grouping][] = ts('Tagged %1', array(1 => $op)) . ' ' . $value;
3106     }
3107   }
3108
3109   /**
3110    * Where / qill clause for tag
3111    *
3112    * @param array $values
3113    */
3114   public function tag(&$values) {
3115     list($name, $op, $value, $grouping, $wildcard) = $values;
3116
3117     list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_EntityTag', "tag_id", $value, $op, array('onlyActive' => FALSE));
3118     // API/Search Builder format array(operator => array(values))
3119     if (is_array($value)) {
3120       if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
3121         $op = key($value);
3122         $value = $value[$op];
3123       }
3124       if (count($value) > 1) {
3125         $this->_useDistinct = TRUE;
3126       }
3127       $value = implode(',', (array) $value);
3128     }
3129
3130     $useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
3131     $tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
3132
3133     $etTable = "`civicrm_entity_tag-" . $value . "`";
3134
3135     if ($useAllTagTypes[2]) {
3136       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3137         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND {$etTable}.entity_table = 'civicrm_contact') ";
3138
3139       // search tag in cases
3140       $etCaseTable = "`civicrm_entity_case_tag-" . $value . "`";
3141       $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
3142       $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
3143
3144       $this->_tables[$etCaseTable] = $this->_whereTables[$etCaseTable]
3145         = " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
3146             LEFT JOIN civicrm_case
3147             ON (civicrm_case_contact.case_id = civicrm_case.id
3148                 AND civicrm_case.is_deleted = 0 )
3149             LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id ) ";
3150       // search tag in activities
3151       $etActTable = "`civicrm_entity_act_tag-" . $value . "`";
3152       $this->_tables[$etActTable] = $this->_whereTables[$etActTable]
3153         = " LEFT JOIN civicrm_activity_contact
3154             ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
3155             LEFT JOIN civicrm_activity
3156             ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
3157             AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )
3158             LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id ) ";
3159
3160       // CRM-10338
3161       if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3162         $this->_where[$grouping][] = "({$etTable}.tag_id $op OR {$etCaseTable}.tag_id $op OR {$etActTable}.tag_id $op)";
3163       }
3164       else {
3165         $this->_where[$grouping][] = "({$etTable}.tag_id $op (" . $value . ") OR {$etCaseTable}.tag_id $op (" . $value . ") OR {$etActTable}.tag_id $op (" . $value . "))";
3166       }
3167     }
3168     else {
3169       $this->_tables[$etTable] = $this->_whereTables[$etTable]
3170         = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id  AND {$etTable}.entity_table = 'civicrm_contact') ";
3171
3172       // CRM-10338
3173       if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3174         // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3175         $op = str_replace('EMPTY', 'NULL', $op);
3176         $this->_where[$grouping][] = "{$etTable}.tag_id $op";
3177       }
3178       // CRM-16941: for tag tried with != operator we don't show contact who don't have given $value AND also in other tag
3179       elseif ($op == '!=') {
3180         $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') . ")";
3181       }
3182       elseif ($op == '=' || strstr($op, 'IN')) {
3183         $op = ($op == '=') ? 'IN' : $op;
3184         $this->_where[$grouping][] = "{$etTable}.tag_id $op ( $value )";
3185       }
3186     }
3187     $this->_qill[$grouping][] = ts('Tagged %1 %2', array(1 => $qillop, 2 => $qillVal));
3188   }
3189
3190   /**
3191    * Where/qill clause for notes
3192    *
3193    * @param array $values
3194    */
3195   public function notes(&$values) {
3196     list($name, $op, $value, $grouping, $wildcard) = $values;
3197
3198     $noteOptionValues = $this->getWhereValues('note_option', $grouping);
3199     $noteOption = CRM_Utils_Array::value('2', $noteOptionValues, '6');
3200     $noteOption = ($name == 'note_body') ? 2 : (($name == 'note_subject') ? 3 : $noteOption);
3201
3202     $this->_useDistinct = TRUE;
3203
3204     $this->_tables['civicrm_note'] = $this->_whereTables['civicrm_note']
3205       = " LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id ) ";
3206
3207     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
3208     $n = trim($value);
3209     $value = $strtolower(CRM_Core_DAO::escapeString($n));
3210     if ($wildcard) {
3211       if (strpos($value, '%') === FALSE) {
3212         $value = "%$value%";
3213       }
3214       $op = 'LIKE';
3215     }
3216     elseif ($op == 'IS NULL' || $op == 'IS NOT NULL') {
3217       $value = NULL;
3218     }
3219
3220     $label = NULL;
3221     $clauses = array();
3222     if ($noteOption % 2 == 0) {
3223       $clauses[] = self::buildClause('civicrm_note.note', $op, $value, 'String');
3224       $label = ts('Note: Body Only');
3225     }
3226     if ($noteOption % 3 == 0) {
3227       $clauses[] = self::buildClause('civicrm_note.subject', $op, $value, 'String');
3228       $label = $label ? ts('Note: Body and Subject') : ts('Note: Subject Only');
3229     }
3230     $this->_where[$grouping][] = "( " . implode(' OR ', $clauses) . " )";
3231     list($qillOp, $qillVal) = self::buildQillForFieldValue(NULL, $name, $n, $op);
3232     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $label, 2 => $qillOp, 3 => $qillVal));
3233   }
3234
3235   /**
3236    * @param string $name
3237    * @param $op
3238    * @param $grouping
3239    *
3240    * @return bool
3241    */
3242   public function nameNullOrEmptyOp($name, $op, $grouping) {
3243     switch ($op) {
3244       case 'IS NULL':
3245       case 'IS NOT NULL':
3246         $this->_where[$grouping][] = "contact_a.$name $op";
3247         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3248         return TRUE;
3249
3250       case 'IS EMPTY':
3251         $this->_where[$grouping][] = "(contact_a.$name IS NULL OR contact_a.$name = '')";
3252         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3253         return TRUE;
3254
3255       case 'IS NOT EMPTY':
3256         $this->_where[$grouping][] = "(contact_a.$name IS NOT NULL AND contact_a.$name <> '')";
3257         $this->_qill[$grouping][] = ts('Name') . ' ' . $op;
3258         return TRUE;
3259
3260       default:
3261         return FALSE;
3262     }
3263   }
3264
3265   /**
3266    * Where / qill clause for sort_name
3267    *
3268    * @param array $values
3269    */
3270   public function sortName(&$values) {
3271     list($fieldName, $op, $value, $grouping, $wildcard) = $values;
3272
3273     // handle IS NULL / IS NOT NULL / IS EMPTY / IS NOT EMPTY
3274     if ($this->nameNullOrEmptyOp($fieldName, $op, $grouping)) {
3275       return;
3276     }
3277
3278     $input = $value = trim($value);
3279
3280     if (!strlen($value)) {
3281       return;
3282     }
3283
3284     $config = CRM_Core_Config::singleton();
3285
3286     $sub = array();
3287
3288     //By default, $sub elements should be joined together with OR statements (don't change this variable).
3289     $subGlue = ' OR ';
3290
3291     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
3292
3293     $firstChar = substr($value, 0, 1);
3294     $lastChar = substr($value, -1, 1);
3295     $quotes = array("'", '"');
3296     // If string is quoted, strip quotes and otherwise don't alter it
3297     if ((strlen($value) > 2) && in_array($firstChar, $quotes) && in_array($lastChar, $quotes)) {
3298       $value = trim($value, implode('', $quotes));
3299     }
3300     // Replace spaces with wildcards for a LIKE operation
3301     // UNLESS string contains a comma (this exception is a tiny bit questionable)
3302     elseif ($op == 'LIKE' && strpos($value, ',') === FALSE) {
3303       $value = str_replace(' ', '%', $value);
3304     }
3305     $value = $strtolower(CRM_Core_DAO::escapeString(trim($value)));
3306     if (strlen($value)) {
3307       $fieldsub = array();
3308       if ($wildcard && $op == 'LIKE') {
3309         if ($config->includeWildCardInName) {
3310           $value = "'%$value%'";
3311         }
3312         else {
3313           $value = "'$value%'";
3314         }
3315         $op = 'LIKE';
3316       }
3317       else {
3318         $value = "'$value'";
3319       }
3320       if ($fieldName == 'sort_name') {
3321         $wc = self::caseImportant($op) ? "LOWER(contact_a.sort_name)" : "contact_a.sort_name";
3322       }
3323       else {
3324         $wc = self::caseImportant($op) ? "LOWER(contact_a.display_name)" : "contact_a.display_name";
3325       }
3326       $fieldsub[] = " ( $wc $op $value )";
3327       if ($config->includeNickNameInName) {
3328         $wc = self::caseImportant($op) ? "LOWER(contact_a.nick_name)" : "contact_a.nick_name";
3329         $fieldsub[] = " ( $wc $op $value )";
3330       }
3331       if ($config->includeEmailInName) {
3332         $fieldsub[] = " ( civicrm_email.email $op $value ) ";
3333       }
3334       $sub[] = ' ( ' . implode(' OR ', $fieldsub) . ' ) ';
3335     }
3336
3337     $sub = ' ( ' . implode($subGlue, $sub) . ' ) ';
3338
3339     $this->_where[$grouping][] = $sub;
3340     if ($config->includeEmailInName) {
3341       $this->_tables['civicrm_email'] = $this->_whereTables['civicrm_email'] = 1;
3342       $this->_qill[$grouping][] = ts('Name or Email') . " $op - '$input'";
3343     }
3344     else {
3345       $this->_qill[$grouping][] = ts('Name') . " $op - '$input'";
3346     }
3347   }
3348
3349   /**
3350    * Where/qill clause for greeting fields.
3351    *
3352    * @param array $values
3353    */
3354   public function greetings(&$values) {
3355     list($name, $op, $value, $grouping, $wildcard) = $values;
3356     $name .= '_display';
3357
3358     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
3359     $this->_qill[$grouping][] = ts('Greeting %1 %2', array(1 => $qillop, 2 => $qillVal));
3360     $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value, 'String');
3361   }
3362
3363   /**
3364    * Where / qill clause for email
3365    *
3366    * @param array $values
3367    */
3368   public function email(&$values) {
3369     list($name, $op, $value, $grouping, $wildcard) = $values;
3370
3371     $n = trim($value);
3372     if ($n) {
3373       $config = CRM_Core_Config::singleton();
3374
3375       if (substr($n, 0, 1) == '"' &&
3376         substr($n, -1, 1) == '"'
3377       ) {
3378         $n = substr($n, 1, -1);
3379         $value = strtolower(CRM_Core_DAO::escapeString($n));
3380         $value = "'$value'";
3381         $op = '=';
3382       }
3383       else {
3384         $value = strtolower($n);
3385         if ($wildcard) {
3386           if (strpos($value, '%') === FALSE) {
3387             $value = "%{$value}%";
3388           }
3389           $op = 'LIKE';
3390         }
3391       }
3392       $this->_qill[$grouping][] = ts('Email') . " $op '$n'";
3393       $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, $value, 'String');
3394     }
3395     else {
3396       $this->_qill[$grouping][] = ts('Email') . " $op ";
3397       $this->_where[$grouping][] = self::buildClause('civicrm_email.email', $op, NULL, 'String');
3398     }
3399
3400     $this->_tables['civicrm_email'] = $this->_whereTables['civicrm_email'] = 1;
3401   }
3402
3403   /**
3404    * Where / qill clause for phone number
3405    *
3406    * @param array $values
3407    */
3408   public function phone_numeric(&$values) {
3409     list($name, $op, $value, $grouping, $wildcard) = $values;
3410     // Strip non-numeric characters; allow wildcards
3411     $number = preg_replace('/[^\d%]/', '', $value);
3412     if ($number) {
3413       if (strpos($number, '%') === FALSE) {
3414         $number = "%$number%";
3415       }
3416
3417       $this->_qill[$grouping][] = ts('Phone number contains') . " $number";
3418       $this->_where[$grouping][] = self::buildClause('civicrm_phone.phone_numeric', 'LIKE', "$number", 'String');
3419       $this->_tables['civicrm_phone'] = $this->_whereTables['civicrm_phone'] = 1;
3420     }
3421   }
3422
3423   /**
3424    * Where / qill clause for phone type/location
3425    *
3426    * @param array $values
3427    */
3428   public function phone_option_group($values) {
3429     list($name, $op, $value, $grouping, $wildcard) = $values;
3430     $option = ($name == 'phone_phone_type_id' ? 'phone_type_id' : 'location_type_id');
3431     $options = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', $option);
3432     $optionName = $options[$value];
3433     $this->_qill[$grouping][] = ts('Phone') . ' ' . ($name == 'phone_phone_type_id' ? ts('type') : ('location')) . " $op $optionName";
3434     $this->_where[$grouping][] = self::buildClause('civicrm_phone.' . substr($name, 6), $op, $value, 'Integer');
3435     $this->_tables['civicrm_phone'] = $this->_whereTables['civicrm_phone'] = 1;
3436   }
3437
3438   /**
3439    * Where / qill clause for street_address.
3440    *
3441    * @param array $values
3442    */
3443   public function street_address(&$values) {
3444     list($name, $op, $value, $grouping, $wildcard) = $values;
3445
3446     if (!$op) {
3447       $op = 'LIKE';
3448     }
3449
3450     $n = trim($value);
3451
3452     if ($n) {
3453       $value = strtolower($n);
3454       if (strpos($value, '%') === FALSE) {
3455         // only add wild card if not there
3456         $value = "%{$value}%";
3457       }
3458       $op = 'LIKE';
3459       $this->_where[$grouping][] = self::buildClause('LOWER(civicrm_address.street_address)', $op, $value, 'String');
3460       $this->_qill[$grouping][] = ts('Street') . " $op '$n'";
3461     }
3462     else {
3463       $this->_where[$grouping][] = self::buildClause('civicrm_address.street_address', $op, NULL, 'String');
3464       $this->_qill[$grouping][] = ts('Street') . " $op ";
3465     }
3466
3467     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3468   }
3469
3470   /**
3471    * Where / qill clause for street_unit.
3472    *
3473    * @param array $values
3474    */
3475   public function street_number(&$values) {
3476     list($name, $op, $value, $grouping, $wildcard) = $values;
3477
3478     if (!$op) {
3479       $op = '=';
3480     }
3481
3482     $n = trim($value);
3483
3484     if (strtolower($n) == 'odd') {
3485       $this->_where[$grouping][] = " ( civicrm_address.street_number % 2 = 1 )";
3486       $this->_qill[$grouping][] = ts('Street Number is odd');
3487     }
3488     elseif (strtolower($n) == 'even') {
3489       $this->_where[$grouping][] = " ( civicrm_address.street_number % 2 = 0 )";
3490       $this->_qill[$grouping][] = ts('Street Number is even');
3491     }
3492     else {
3493       $value = strtolower($n);
3494
3495       $this->_where[$grouping][] = self::buildClause('LOWER(civicrm_address.street_number)', $op, $value, 'String');
3496       $this->_qill[$grouping][] = ts('Street Number') . " $op '$n'";
3497     }
3498
3499     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3500   }
3501
3502   /**
3503    * Where / qill clause for sorting by character.
3504    *
3505    * @param array $values
3506    */
3507   public function sortByCharacter(&$values) {
3508     list($name, $op, $value, $grouping, $wildcard) = $values;
3509
3510     $name = trim($value);
3511     $cond = " contact_a.sort_name LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($name)) . "%'";
3512     $this->_where[$grouping][] = $cond;
3513     $this->_qill[$grouping][] = ts('Showing only Contacts starting with: \'%1\'', array(1 => $name));
3514   }
3515
3516   /**
3517    * Where / qill clause for including contact ids.
3518    */
3519   public function includeContactIDs() {
3520     if (!$this->_includeContactIds || empty($this->_params)) {
3521       return;
3522     }
3523
3524     $contactIds = array();
3525     foreach ($this->_params as $id => $values) {
3526       if (substr($values[0], 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) {
3527         $contactIds[] = substr($values[0], CRM_Core_Form::CB_PREFIX_LEN);
3528       }
3529     }
3530     if (!empty($contactIds)) {
3531       $this->_where[0][] = " ( contact_a.id IN (" . implode(',', $contactIds) . " ) ) ";
3532     }
3533   }
3534
3535   /**
3536    * Where / qill clause for postal code.
3537    *
3538    * @param array $values
3539    */
3540   public function postalCode(&$values) {
3541     // skip if the fields dont have anything to do with postal_code
3542     if (empty($this->_fields['postal_code'])) {
3543       return;
3544     }
3545
3546     list($name, $op, $value, $grouping, $wildcard) = $values;
3547
3548     // Handle numeric postal code range searches properly by casting the column as numeric
3549     if (is_numeric($value)) {
3550       $field = "IF (civicrm_address.postal_code REGEXP '^[0-9]+$', CAST(civicrm_address.postal_code AS UNSIGNED), 0)";
3551       $val = CRM_Utils_Type::escape($value, 'Integer');
3552     }
3553     else {
3554       $field = 'civicrm_address.postal_code';
3555       // Per CRM-17060 we might be looking at an 'IN' syntax so don't case arrays to string.
3556       if (!is_array($value)) {
3557         $val = CRM_Utils_Type::escape($value, 'String');
3558       }
3559       else {
3560         // Do we need to escape values here? I would expect buildClause does.
3561         $val = $value;
3562       }
3563     }
3564
3565     $this->_tables['civicrm_address'] = $this->_whereTables['civicrm_address'] = 1;
3566
3567     if ($name == 'postal_code') {
3568       $this->_where[$grouping][] = self::buildClause($field, $op, $val, 'String');
3569       $this->_qill[$grouping][] = ts('Postal code') . " {$op} {$value}";
3570     }
3571     elseif ($name == 'postal_code_low') {
3572       $this->_where[$grouping][] = " ( $field >= '$val' ) ";
3573       $this->_qill[$grouping][] = ts('Postal code greater than or equal to \'%1\'', array(1 => $value));
3574     }
3575     elseif ($name == 'postal_code_high') {
3576       $this->_where[$grouping][] = " ( $field <= '$val' ) ";
3577       $this->_qill[$grouping][] = ts('Postal code less than or equal to \'%1\'', array(1 => $value));
3578     }
3579   }
3580
3581   /**
3582    * Where / qill clause for location type.
3583    *
3584    * @param array $values
3585    * @param null $status
3586    *
3587    * @return string
3588    */
3589   public function locationType(&$values, $status = NULL) {
3590     list($name, $op, $value, $grouping, $wildcard) = $values;
3591
3592     if (is_array($value)) {
3593       $this->_where[$grouping][] = 'civicrm_address.location_type_id IN (' . implode(',', $value) . ')';
3594       $this->_tables['civicrm_address'] = 1;
3595       $this->_whereTables['civicrm_address'] = 1;
3596
3597       $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
3598       $names = array();
3599       foreach ($value as $id) {
3600         $names[] = $locationType[$id];
3601       }
3602
3603       $this->_primaryLocation = FALSE;
3604
3605       if (!$status) {
3606         $this->_qill[$grouping][] = ts('Location Type') . ' - ' . implode(' ' . ts('or') . ' ', $names);
3607       }
3608       else {
3609         return implode(' ' . ts('or') . ' ', $names);
3610       }
3611     }
3612   }
3613
3614   /**
3615    * @param $values
3616    * @param bool $fromStateProvince
3617    *
3618    * @return array|NULL
3619    */
3620   public function country(&$values, $fromStateProvince = TRUE) {
3621     list($name, $op, $value, $grouping, $wildcard) = $values;
3622
3623     if (!$fromStateProvince) {
3624       $stateValues = $this->getWhereValues('state_province', $grouping);
3625       if (!empty($stateValues)) {
3626         // return back to caller if there are state province values
3627         // since that handles this case
3628         return NULL;
3629       }
3630     }
3631
3632     $countryClause = $countryQill = NULL;
3633     if (
3634       $values &&
3635       !empty($value)
3636     ) {
3637
3638       $this->_tables['civicrm_address'] = 1;
3639       $this->_whereTables['civicrm_address'] = 1;
3640
3641       $countryClause = self::buildClause('civicrm_address.country_id', $op, $value, 'Positive');
3642       list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, 'country_id', $value, $op);
3643       $countryQill = ts("%1 %2 %3", array(1 => 'Country', 2 => $qillop, 3 => $qillVal));
3644
3645       if (!$fromStateProvince) {
3646         $this->_where[$grouping][] = $countryClause;
3647         $this->_qill[$grouping][] = $countryQill;
3648       }
3649     }
3650
3651     if ($fromStateProvince) {
3652       if (!empty($countryClause)) {
3653         return array(
3654           $countryClause,
3655           " ...AND... " . $countryQill,
3656         );
3657       }
3658       else {
3659         return array(NULL, NULL);
3660       }
3661     }
3662   }
3663
3664   /**
3665    * Where / qill clause for county (if present).
3666    *
3667    * @param array $values
3668    * @param null $status
3669    *
3670    * @return string
3671    */
3672   public function county(&$values, $status = NULL) {
3673     list($name, $op, $value, $grouping, $wildcard) = $values;
3674
3675     if (!is_array($value)) {
3676       // force the county to be an array
3677       $value = array($value);
3678     }
3679
3680     // check if the values are ids OR names of the counties
3681     $inputFormat = 'id';
3682     foreach ($value as $v) {
3683       if (!is_numeric($v)) {
3684         $inputFormat = 'name';
3685         break;
3686       }
3687     }
3688     $names = array();
3689     if ($op == '=') {
3690       $op = 'IN';
3691     }
3692     elseif ($op == '!=') {
3693       $op = 'NOT IN';
3694     }
3695     else {
3696       // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3697       $op = str_replace('EMPTY', 'NULL', $op);
3698     }
3699
3700     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3701       $clause = "civicrm_address.county_id $op";
3702     }
3703     elseif ($inputFormat == 'id') {
3704       $clause = 'civicrm_address.county_id IN (' . implode(',', $value) . ')';
3705
3706       $county = CRM_Core_PseudoConstant::county();
3707       foreach ($value as $id) {
3708         $names[] = CRM_Utils_Array::value($id, $county);
3709       }
3710     }
3711     else {
3712       $inputClause = array();
3713       $county = CRM_Core_PseudoConstant::county();
3714       foreach ($value as $name) {
3715         $name = trim($name);
3716         $inputClause[] = CRM_Utils_Array::key($name, $county);
3717       }
3718       $clause = 'civicrm_address.county_id IN (' . implode(',', $inputClause) . ')';
3719       $names = $value;
3720     }
3721     $this->_tables['civicrm_address'] = 1;
3722     $this->_whereTables['civicrm_address'] = 1;
3723
3724     $this->_where[$grouping][] = $clause;
3725     if (!$status) {
3726       $this->_qill[$grouping][] = ts('County') . ' - ' . implode(' ' . ts('or') . ' ', $names);
3727     }
3728     else {
3729       return implode(' ' . ts('or') . ' ', $names);
3730     }
3731   }
3732
3733   /**
3734    * Where / qill clause for state/province AND country (if present).
3735    *
3736    * @param array $values
3737    * @param null $status
3738    *
3739    * @return string
3740    */
3741   public function stateProvince(&$values, $status = NULL) {
3742     list($name, $op, $value, $grouping, $wildcard) = $values;
3743
3744     // quick escape for IS NULL
3745     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3746       $value = NULL;
3747     }
3748     elseif (!is_array($value)) {
3749       // force the state to be an array
3750       // check if its in the mapper format!
3751       $values = self::parseSearchBuilderString($value);
3752       if (is_array($values)) {
3753         $value = $values;
3754       }
3755       else {
3756         $value = array($value);
3757       }
3758     }
3759
3760     // check if the values are ids OR names of the states
3761     $inputFormat = 'id';
3762     if ($value) {
3763       foreach ($value as $v) {
3764         if (!is_numeric($v)) {
3765           $inputFormat = 'name';
3766           break;
3767         }
3768       }
3769     }
3770
3771     $names = array();
3772     if ($op == '=') {
3773       $op = 'IN';
3774     }
3775     elseif ($op == '!=') {
3776       $op = 'NOT IN';
3777     }
3778     else {
3779       // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
3780       $op = str_replace('EMPTY', 'NULL', $op);
3781     }
3782     if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
3783       $stateClause = "civicrm_address.state_province_id $op";
3784     }
3785     elseif ($inputFormat == 'id') {
3786       if ($op != 'NOT IN') {
3787         $op = 'IN';
3788       }
3789       $stateClause = "civicrm_address.state_province_id $op (" . implode(',', $value) . ')';
3790
3791       foreach ($value as $id) {
3792         $names[] = CRM_Core_PseudoConstant::stateProvince($id, FALSE);
3793       }
3794     }
3795     else {
3796       $inputClause = array();
3797       $stateProvince = CRM_Core_PseudoConstant::stateProvince();
3798       foreach ($value as $name) {
3799         $name = trim($name);
3800         $inputClause[] = CRM_Utils_Array::key($name, $stateProvince);
3801       }
3802       $stateClause = "civicrm_address.state_province_id $op (" . implode(',', $inputClause) . ')';
3803       $names = $value;
3804     }
3805     $this->_tables['civicrm_address'] = 1;
3806     $this->_whereTables['civicrm_address'] = 1;
3807
3808     $countryValues = $this->getWhereValues('country', $grouping);
3809     list($countryClause, $countryQill) = $this->country($countryValues, TRUE);
3810
3811     if ($countryClause) {
3812       $clause = "( $stateClause AND $countryClause )";
3813     }
3814     else {
3815       $clause = $stateClause;
3816     }
3817
3818     $this->_where[$grouping][] = $clause;
3819     if (!$status) {
3820       $this->_qill[$grouping][] = ts('State/Province') . " $op " . implode(' ' . ts('or') . ' ', $names) . $countryQill;
3821     }
3822     else {
3823       return implode(' ' . ts('or') . ' ', $names) . $countryQill;
3824     }
3825   }
3826
3827   /**
3828    * Where / qill clause for change log.
3829    *
3830    * @param array $values
3831    */
3832   public function changeLog(&$values) {
3833     list($name, $op, $value, $grouping, $wildcard) = $values;
3834
3835     $targetName = $this->getWhereValues('changed_by', $grouping);
3836     if (!$targetName) {
3837       return;
3838     }
3839
3840     $name = trim($targetName[2]);
3841     $name = strtolower(CRM_Core_DAO::escapeString($name));
3842     $name = $targetName[4] ? "%$name%" : $name;
3843     $this->_where[$grouping][] = "contact_b_log.sort_name LIKE '%$name%'";
3844     $this->_tables['civicrm_log'] = $this->_whereTables['civicrm_log'] = 1;
3845     $this->_qill[$grouping][] = ts('Modified By') . " $name";
3846   }
3847
3848   /**
3849    * @param $values
3850    */
3851   public function modifiedDates($values) {
3852     $this->_useDistinct = TRUE;
3853
3854     // CRM-11281, default to added date if not set
3855     $fieldTitle = ts('Added Date');
3856     $fieldName = 'created_date';
3857     foreach (array_keys($this->_params) as $id) {
3858       if ($this->_params[$id][0] == 'log_date') {
3859         if ($this->_params[$id][2] == 2) {
3860           $fieldTitle = ts('Modified Date');
3861           $fieldName = 'modified_date';
3862         }
3863       }
3864     }
3865
3866     $this->dateQueryBuilder($values, 'contact_a', 'log_date', $fieldName, $fieldTitle);
3867
3868     self::$_openedPanes[ts('Change Log')] = TRUE;
3869   }
3870
3871   /**
3872    * @param $values
3873    */
3874   public function demographics(&$values) {
3875     list($name, $op, $value, $grouping, $wildcard) = $values;
3876
3877     if (($name == 'age_low') || ($name == 'age_high')) {
3878       $this->ageRangeQueryBuilder($values,
3879         'contact_a', 'age', 'birth_date', ts('Age')
3880       );
3881     }
3882     elseif (($name == 'birth_date_low') || ($name == 'birth_date_high')) {
3883
3884       $this->dateQueryBuilder($values,
3885         'contact_a', 'birth_date', 'birth_date', ts('Birth Date')
3886       );
3887     }
3888     elseif (($name == 'deceased_date_low') || ($name == 'deceased_date_high')) {
3889
3890       $this->dateQueryBuilder($values,
3891         'contact_a', 'deceased_date', 'deceased_date', ts('Deceased Date')
3892       );
3893     }
3894
3895     self::$_openedPanes[ts('Demographics')] = TRUE;
3896   }
3897
3898   /**
3899    * @param $values
3900    */
3901   public function privacy(&$values) {
3902     list($name, $op, $value, $grouping, $wildcard) = $values;
3903     //fixed for profile search listing CRM-4633
3904     if (strpbrk($value, "[")) {
3905       $value = "'{$value}'";
3906       $op = "!{$op}";
3907       $this->_where[$grouping][] = "contact_a.{$name} $op $value";
3908     }
3909     else {
3910       $this->_where[$grouping][] = "contact_a.{$name} $op $value";
3911     }
3912     $field = CRM_Utils_Array::value($name, $this->_fields);
3913     $op = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
3914     $title = $field ? $field['title'] : $name;
3915     $this->_qill[$grouping][] = "$title $op $value";
3916   }
3917
3918   /**
3919    * @param $values
3920    */
3921   public function privacyOptions($values) {
3922     list($name, $op, $value, $grouping, $wildcard) = $values;
3923
3924     if (empty($value) || !is_array($value)) {
3925       return;
3926     }
3927
3928     // get the operator and toggle values
3929     $opValues = $this->getWhereValues('privacy_operator', $grouping);
3930     $operator = 'OR';
3931     if ($opValues &&
3932       strtolower($opValues[2] == 'AND')
3933     ) {
3934       $operator = 'AND';
3935     }
3936
3937     $toggleValues = $this->getWhereValues('privacy_toggle', $grouping);
3938     $compareOP = '!';
3939     if ($toggleValues &&
3940       $toggleValues[2] == 2
3941     ) {
3942       $compareOP = '';
3943     }
3944
3945     $clauses = array();
3946     $qill = array();
3947     foreach ($value as $dontCare => $pOption) {
3948       $clauses[] = " ( contact_a.{$pOption} = 1 ) ";
3949       $field = CRM_Utils_Array::value($pOption, $this->_fields);
3950       $title = $field ? $field['title'] : $pOption;
3951       $qill[] = " $title = 1 ";
3952     }
3953
3954     $this->_where[$grouping][] = $compareOP . '( ' . implode($operator, $clauses) . ' )';
3955     $this->_qill[$grouping][] = $compareOP . '( ' . implode($operator, $qill) . ' )';
3956   }
3957
3958   /**
3959    * @param $values
3960    */
3961   public function preferredCommunication(&$values) {
3962     list($name, $op, $value, $grouping, $wildcard) = $values;
3963
3964     if (!is_array($value)) {
3965       $value = str_replace(array('(', ')'), '', explode(",", $value));
3966     }
3967     elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
3968       $op = key($value);
3969       $value = $value[$op];
3970     }
3971     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contact_DAO_Contact', $name, $value, $op);
3972
3973     if (self::caseImportant($op)) {
3974       $value = implode("[[:cntrl:]]|[[:cntrl:]]", (array) $value);
3975       $op = (strstr($op, '!') || strstr($op, 'NOT')) ? 'NOT RLIKE' : 'RLIKE';
3976       $value = "[[:cntrl:]]" . $value . "[[:cntrl:]]";
3977     }
3978
3979     $this->_where[$grouping][] = self::buildClause("contact_a.preferred_communication_method", $op, $value);
3980     $this->_qill[$grouping][] = ts('Preferred Communication Method %1 %2', array(1 => $qillop, 2 => $qillVal));
3981   }
3982
3983   /**
3984    * Where / qill clause for relationship.
3985    *
3986    * @param array $values
3987    */
3988   public function relationship(&$values) {
3989     list($name, $op, $value, $grouping, $wildcard) = $values;
3990     if ($this->_relationshipValuesAdded) {
3991       return;
3992     }
3993     // also get values array for relation_target_name
3994     // for relationship search we always do wildcard
3995     $relationType = $this->getWhereValues('relation_type_id', $grouping);
3996     $targetName = $this->getWhereValues('relation_target_name', $grouping);
3997     $relStatus = $this->getWhereValues('relation_status', $grouping);
3998     $relPermission = $this->getWhereValues('relation_permission', $grouping);
3999     $targetGroup = $this->getWhereValues('relation_target_group', $grouping);
4000
4001     $nameClause = $name = NULL;
4002     if ($targetName) {
4003       $name = trim($targetName[2]);
4004       if (substr($name, 0, 1) == '"' &&
4005         substr($name, -1, 1) == '"'
4006       ) {
4007         $name = substr($name, 1, -1);
4008         $name = strtolower(CRM_Core_DAO::escapeString($name));
4009         $nameClause = "= '$name'";
4010       }
4011       else {
4012         $name = strtolower(CRM_Core_DAO::escapeString($name));
4013         $nameClause = "LIKE '%{$name}%'";
4014       }
4015     }
4016
4017     $rTypeValues = array();
4018     if (!empty($relationType)) {
4019       $rel = explode('_', $relationType[2]);
4020       self::$_relType = $rel[1];
4021       $params = array('id' => $rel[0]);
4022       $rType = CRM_Contact_BAO_RelationshipType::retrieve($params, $rTypeValues);
4023     }
4024     if (!empty($rTypeValues) && $rTypeValues['name_a_b'] == $rTypeValues['name_b_a']) {
4025       // if we don't know which end of the relationship we are dealing with we'll create a temp table
4026       //@todo unless we are dealing with a target group
4027       self::$_relType = 'reciprocal';
4028     }
4029     // if we are creating a temp table we build our own where for the relationship table
4030     $relationshipTempTable = NULL;
4031     if (self::$_relType == 'reciprocal' && empty($targetGroup)) {
4032       $where = array();
4033       self::$_relationshipTempTable = $relationshipTempTable = CRM_Core_DAO::createTempTableName('civicrm_rel');
4034       if ($nameClause) {
4035         $where[$grouping][] = " sort_name $nameClause ";
4036       }
4037     }
4038     else {
4039       $where = &$this->_where;
4040       if ($nameClause) {
4041         $where[$grouping][] = "( contact_b.sort_name $nameClause AND contact_b.id != contact_a.id )";
4042       }
4043     }
4044
4045     $allRelationshipType = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, NULL, TRUE);
4046
4047     if ($nameClause || !$targetGroup) {
4048       if (!empty($relationType)) {
4049         $this->_qill[$grouping][] = $allRelationshipType[$relationType[2]] . " $name";
4050       }
4051       else {
4052         $this->_qill[$grouping][] = $name;
4053       }
4054     }
4055
4056     //check to see if the target contact is in specified group
4057     if ($targetGroup) {
4058       //add contacts from static groups
4059       $this->_tables['civicrm_relationship_group_contact'] = $this->_whereTables['civicrm_relationship_group_contact']
4060         = " 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'";
4061       $groupWhere[] = "( civicrm_relationship_group_contact.group_id IN  (" .
4062         implode(",", $targetGroup[2]) . ") ) ";
4063
4064       //add contacts from saved searches
4065       $ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", "contact_b", $op);
4066
4067       //set the group where clause
4068       if ($ssWhere) {
4069         $groupWhere[] = "( " . $ssWhere . " )";
4070       }
4071       $this->_where[$grouping][] = "( " . implode(" OR ", $groupWhere) . " )";
4072
4073       //Get the names of the target groups for the qill
4074       $groupNames = CRM_Core_PseudoConstant::group();
4075       $qillNames = array();
4076       foreach ($targetGroup[2] as $groupId) {
4077         if (array_key_exists($groupId, $groupNames)) {
4078           $qillNames[] = $groupNames[$groupId];
4079         }
4080       }
4081       if (!empty($relationType)) {
4082         $this->_qill[$grouping][] = $allRelationshipType[$relationType[2]] . " ( " . implode(", ", $qillNames) . " )";
4083       }
4084       else {
4085         $this->_qill[$grouping][] = implode(", ", $qillNames);
4086       }
4087     }
4088
4089     // Note we do not currently set mySql to handle timezones, so doing this the old-fashioned way
4090     $today = date('Ymd');
4091     //check for active, inactive and all relation status
4092     if ($relStatus[2] == 0) {
4093       $where[$grouping][] = "(
4094 civicrm_relationship.is_active = 1 AND
4095 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
4096 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
4097 )";
4098       $this->_qill[$grouping][] = ts('Relationship - Active and Current');
4099     }
4100     elseif ($relStatus[2] == 1) {
4101       $where[$grouping][] = "(
4102 civicrm_relationship.is_active = 0 OR
4103 civicrm_relationship.end_date < {$today} OR
4104 civicrm_relationship.start_date > {$today}
4105 )";
4106       $this->_qill[$grouping][] = ts('Relationship - Inactive or not Current');
4107     }
4108
4109     $onlyDeleted = 0;
4110     if (in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params)) {
4111       $onlyDeleted = 1;
4112     }
4113     $where[$grouping][] = "(contact_b.is_deleted = {$onlyDeleted})";
4114
4115     //check for permissioned, non-permissioned and all permissioned relations
4116     if ($relPermission[2] == 1) {
4117       $where[$grouping][] = "(
4118 civicrm_relationship.is_permission_a_b = 1
4119 )";
4120       $this->_qill[$grouping][] = ts('Relationship - Permissioned');
4121     }
4122     elseif ($relPermission[2] == 2) {
4123       //non-allowed permission relationship.
4124       $where[$grouping][] = "(
4125 civicrm_relationship.is_permission_a_b = 0
4126 )";
4127       $this->_qill[$grouping][] = ts('Relationship - Non-permissioned');
4128     }
4129
4130     $this->addRelationshipDateClauses($grouping, $where);
4131     if (!empty($relationType) && !empty($rType) && isset($rType->id)) {
4132       $where[$grouping][] = 'civicrm_relationship.relationship_type_id = ' . $rType->id;
4133     }
4134     $this->_tables['civicrm_relationship'] = $this->_whereTables['civicrm_relationship'] = 1;
4135     $this->_useDistinct = TRUE;
4136     $this->_relationshipValuesAdded = TRUE;
4137     // it could be a or b, using an OR creates an unindexed join - better to create a temp table &
4138     // join on that,
4139     // @todo creating a temp table could be expanded to group filter
4140     // as even creating a temp table of all relationships is much much more efficient than
4141     // an OR in the join
4142     if ($relationshipTempTable) {
4143       $whereClause = '';
4144       if (!empty($where[$grouping])) {
4145         $whereClause = ' WHERE ' . implode(' AND ', $where[$grouping]);
4146         $whereClause = str_replace('contact_b', 'c', $whereClause);
4147       }
4148       $sql = "
4149         CREATE TEMPORARY TABLE {$relationshipTempTable}
4150           (SELECT contact_id_b as contact_id, civicrm_relationship.id
4151             FROM civicrm_relationship
4152             INNER JOIN  civicrm_contact c ON civicrm_relationship.contact_id_a = c.id
4153             $whereClause )
4154           UNION
4155             (SELECT contact_id_a as contact_id, civicrm_relationship.id
4156             FROM civicrm_relationship
4157             INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_b = c.id
4158             $whereClause )
4159       ";
4160       CRM_Core_DAO::executeQuery($sql);
4161     }
4162
4163   }
4164
4165   /**
4166    * Add start & end date criteria in
4167    * @param string $grouping
4168    * @param array $where
4169    *   = array to add where clauses to, in case you are generating a temp table.
4170    * not the main query.
4171    */
4172   public function addRelationshipDateClauses($grouping, &$where) {
4173     $dateValues = array();
4174     $dateTypes = array(
4175       'start_date',
4176       'end_date',
4177     );
4178
4179     foreach ($dateTypes as $dateField) {
4180       $dateValueLow = $this->getWhereValues('relation_' . $dateField . '_low', $grouping);
4181       $dateValueHigh = $this->getWhereValues('relation_' . $dateField . '_high', $grouping);
4182       if (!empty($dateValueLow)) {
4183         $date = date('Ymd', strtotime($dateValueLow[2]));
4184         $where[$grouping][] = "civicrm_relationship.$dateField >= $date";
4185         $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);
4186       }
4187       if (!empty($dateValueHigh)) {
4188         $date = date('Ymd', strtotime($dateValueHigh[2]));
4189         $where[$grouping][] = "civicrm_relationship.$dateField <= $date";
4190         $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);
4191       }
4192     }
4193   }
4194
4195   /**
4196    * Default set of return properties.
4197    *
4198    * @param int $mode
4199    *
4200    * @return array
4201    *   derault return properties
4202    */
4203   public static function &defaultReturnProperties($mode = 1) {
4204     if (!isset(self::$_defaultReturnProperties)) {
4205       self::$_defaultReturnProperties = array();
4206     }
4207
4208     if (!isset(self::$_defaultReturnProperties[$mode])) {
4209       // add activity return properties
4210       if ($mode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
4211         self::$_defaultReturnProperties[$mode] = CRM_Activity_BAO_Query::defaultReturnProperties($mode, FALSE);
4212       }
4213       else {
4214         self::$_defaultReturnProperties[$mode] = CRM_Core_Component::defaultReturnProperties($mode, FALSE);
4215       }
4216
4217       if (empty(self::$_defaultReturnProperties[$mode])) {
4218         self::$_defaultReturnProperties[$mode] = array(
4219           'home_URL' => 1,
4220           'image_URL' => 1,
4221           'legal_identifier' => 1,
4222           'external_identifier' => 1,
4223           'contact_type' => 1,
4224           'contact_sub_type' => 1,
4225           'sort_name' => 1,
4226           'display_name' => 1,
4227           'preferred_mail_format' => 1,
4228           'nick_name' => 1,
4229           'first_name' => 1,
4230           'middle_name' => 1,
4231           'last_name' => 1,
4232           'prefix_id' => 1,
4233           'suffix_id' => 1,
4234           'formal_title' => 1,
4235           'communication_style_id' => 1,
4236           'birth_date' => 1,
4237           'gender_id' => 1,
4238           'street_address' => 1,
4239           'supplemental_address_1' => 1,
4240           'supplemental_address_2' => 1,
4241           'city' => 1,
4242           'postal_code' => 1,
4243           'postal_code_suffix' => 1,
4244           'state_province' => 1,
4245           'country' => 1,
4246           'world_region' => 1,
4247           'geo_code_1' => 1,
4248           'geo_code_2' => 1,
4249           'email' => 1,
4250           'on_hold' => 1,
4251           'phone' => 1,
4252           'im' => 1,
4253           'household_name' => 1,
4254           'organization_name' => 1,
4255           'deceased_date' => 1,
4256           'is_deceased' => 1,
4257           'job_title' => 1,
4258           'legal_name' => 1,
4259           'sic_code' => 1,
4260           'current_employer' => 1,
4261           // FIXME: should we use defaultHierReturnProperties() for the below?
4262           'do_not_email' => 1,
4263           'do_not_mail' => 1,
4264           'do_not_sms' => 1,
4265           'do_not_phone' => 1,
4266           'do_not_trade' => 1,
4267           'is_opt_out' => 1,
4268           'contact_is_deleted' => 1,
4269           'preferred_communication_method' => 1,
4270           'preferred_language' => 1,
4271         );
4272       }
4273     }
4274     return self::$_defaultReturnProperties[$mode];
4275   }
4276
4277   /**
4278    * Get primary condition for a sql clause.
4279    *
4280    * @param int $value
4281    *
4282    * @return string|NULL
4283    */
4284   public static function getPrimaryCondition($value) {
4285     if (is_numeric($value)) {
4286       $value = (int ) $value;
4287       return ($value == 1) ? 'is_primary = 1' : 'is_primary = 0';
4288     }
4289     return NULL;
4290   }
4291
4292   /**
4293    * Wrapper for a simple search query.
4294    *
4295    * @param array $params
4296    * @param array $returnProperties
4297    * @param bool $count
4298    *
4299    * @return string
4300    */
4301   public static function getQuery($params = NULL, $returnProperties = NULL, $count = FALSE) {
4302     $query = new CRM_Contact_BAO_Query($params, $returnProperties);
4303     list($select, $from, $where, $having) = $query->query();
4304
4305     return "$select $from $where $having";
4306   }
4307
4308   /**
4309    * These are stub comments as this function needs more explanation - particularly in terms of how it
4310    * relates to $this->searchQuery and why it replicates rather than calles $this->searchQuery.
4311    *
4312    * This function was originally written as a wrapper for the api query but is called from multiple places
4313    * in the core code directly so the name is misleading. This function does not use the searchQuery function
4314    * but it is unclear as to whehter that is historical or there is a reason
4315    *  CRM-11290 led to the permissioning action being extracted from searchQuery & shared with this function
4316    *
4317    * @param array $params
4318    * @param array $returnProperties
4319    * @param null $fields
4320    * @param string $sort
4321    * @param int $offset
4322    * @param int $row_count
4323    * @param bool $smartGroupCache
4324    *   ?? update smart group cache?.
4325    * @param bool $count
4326    *   Return count obnly.
4327    * @param bool $skipPermissions
4328    *   Should permissions be ignored or should the logged in user's permissions be applied.
4329    *
4330    *
4331    * @return array
4332    */
4333   public static function apiQuery(
4334     $params = NULL,
4335     $returnProperties = NULL,
4336     $fields = NULL,
4337     $sort = NULL,
4338     $offset = 0,
4339     $row_count = 25,
4340     $smartGroupCache = TRUE,
4341     $count = FALSE,
4342     $skipPermissions = TRUE
4343   ) {
4344
4345     $query = new CRM_Contact_BAO_Query(
4346       $params, $returnProperties,
4347       NULL, TRUE, FALSE, 1,
4348       $skipPermissions,
4349       TRUE, $smartGroupCache
4350     );
4351
4352     //this should add a check for view deleted if permissions are enabled
4353     if ($skipPermissions) {
4354       $query->_skipDeleteClause = TRUE;
4355     }
4356     $query->generatePermissionClause(FALSE, $count);
4357
4358     // note : this modifies _fromClause and _simpleFromClause
4359     $query->includePseudoFieldsJoin($sort);
4360
4361     list($select, $from, $where, $having) = $query->query($count);
4362
4363     $options = $query->_options;
4364     if (!empty($query->_permissionWhereClause)) {
4365       if (empty($where)) {
4366         $where = "WHERE $query->_permissionWhereClause";
4367       }
4368       else {
4369         $where = "$where AND $query->_permissionWhereClause";
4370       }
4371     }
4372
4373     $sql = "$select $from $where $having";
4374
4375     // add group by
4376     if ($query->_useGroupBy) {
4377       $sql .= ' GROUP BY contact_a.id';
4378     }
4379     if (!empty($sort)) {
4380       $sort = CRM_Utils_Type::escape($sort, 'String');
4381       $sql .= " ORDER BY $sort ";
4382     }
4383     if ($row_count > 0 && $offset >= 0) {
4384       $offset = CRM_Utils_Type::escape($offset, 'Int');
4385       $rowCount = CRM_Utils_Type::escape($row_count, 'Int');
4386       $sql .= " LIMIT $offset, $row_count ";
4387     }
4388
4389     $dao = CRM_Core_DAO::executeQuery($sql);
4390
4391     $values = array();
4392     while ($dao->fetch()) {
4393       if ($count) {
4394         $noRows = $dao->rowCount;
4395         $dao->free();
4396         return array($noRows, NULL);
4397       }
4398       $val = $query->store($dao);
4399       $convertedVals = $query->convertToPseudoNames($dao, TRUE);
4400
4401       if (!empty($convertedVals)) {
4402         $val = array_replace_recursive($val, $convertedVals);
4403       }
4404       $values[$dao->contact_id] = $val;
4405     }
4406     $dao->free();
4407     return array($values, $options);
4408   }
4409
4410   /**
4411    * Get the actual custom field name by stripping off the appended string.
4412    *
4413    * The string could be _relative, _from, or _to
4414    *
4415    * @todo use metadata rather than convention to do this.
4416    *
4417    * @param string $parameterName
4418    *   The name of the parameter submitted to the form.
4419    *   e.g
4420    *   custom_3_relative
4421    *   custom_3_from
4422    *
4423    * @return string
4424    */
4425   public static function getCustomFieldName($parameterName) {
4426     if (substr($parameterName, -5, 5) == '_from') {
4427       return substr($parameterName, 0, strpos($parameterName, '_from'));
4428     }
4429     if (substr($parameterName, -9, 9) == '_relative') {
4430       return substr($parameterName, 0, strpos($parameterName, '_relative'));
4431     }
4432     if (substr($parameterName, -3, 3) == '_to') {
4433       return substr($parameterName, 0, strpos($parameterName, '_to'));
4434     }
4435   }
4436
4437   /**
4438    * Convert submitted values for relative custom date fields to query object format.
4439    *
4440    * The query will support the sqlOperator format so convert to that format.
4441    *
4442    * @param array $formValues
4443    *   Submitted values.
4444    * @param array $params
4445    *   Converted parameters for the query object.
4446    * @param string $values
4447    *   Submitted value.
4448    * @param string $fieldName
4449    *   Submitted field name. (Matches form field not DB field.)
4450    */
4451   protected static function convertCustomDateRelativeFields(&$formValues, &$params, $values, $fieldName) {
4452     if (empty($values)) {
4453       // e.g we might have relative set & from & to empty. The form flow is a bit funky &
4454       // this function gets called again after they fields have been converted which can get ugly.
4455       return;
4456     }
4457     $customFieldName = self::getCustomFieldName($fieldName);
4458
4459     if (substr($fieldName, -9, 9) == '_relative') {
4460       list($from, $to) = CRM_Utils_Date::getFromTo($values, NULL, NULL);
4461     }
4462     else {
4463       if ($fieldName == $customFieldName . '_to' && CRM_Utils_Array::value($customFieldName . '_from', $formValues)) {
4464         // Both to & from are set. We only need to acton one, choosing from.
4465         return;
4466       }
4467
4468       list($from, $to) = CRM_Utils_Date::getFromTo(
4469         NULL,
4470         (empty($formValues[$customFieldName . '_from']) ? NULL : $formValues[$customFieldName . '_from']),
4471         CRM_Utils_Array::value($customFieldName . '_to', $formValues)
4472       );
4473     }
4474
4475     if ($from) {
4476       if ($to) {
4477         $relativeFunction = array('BETWEEN' => array($from, $to));
4478       }
4479       else {
4480         $relativeFunction = array('>=' => $from);
4481       }
4482     }
4483     else {
4484       $relativeFunction = array('<=' => $to);
4485     }
4486     $params[] = array(
4487       $customFieldName,
4488       '=',
4489       $relativeFunction,
4490       0,
4491       0,
4492     );
4493   }
4494
4495   /**
4496    * Has this field already been reformatting to Query object syntax.
4497    *
4498    * The form layer passed formValues to this function in preProcess & postProcess. Reason unknown. This seems
4499    * to come with associated double queries & is possibly damaging performance.
4500    *
4501    * However, here we add a tested function to ensure convertFormValues identifies pre-processed fields & returns
4502    * them as they are.
4503    *
4504    * @param mixed $values
4505    *   Value in formValues for the field.
4506    *
4507    * @return bool;
4508    */
4509   public static function isAlreadyProcessedForQueryFormat($values) {
4510     if (!is_array($values)) {
4511       return FALSE;
4512     }
4513     if (($operator = CRM_Utils_Array::value(1, $values)) == FALSE) {
4514       return FALSE;
4515     }
4516     return in_array($operator, CRM_Core_DAO::acceptedSQLOperators());
4517   }
4518
4519   /**
4520    * Create and query the db for an contact search.
4521    *
4522    * @param int $offset
4523    *   The offset for the query.
4524    * @param int $rowCount
4525    *   The number of rows to return.
4526    * @param string $sort
4527    *   The order by string.
4528    * @param bool $count
4529    *   Is this a count only query ?.
4530    * @param bool $includeContactIds
4531    *   Should we include contact ids?.
4532    * @param bool $sortByChar
4533    *   If true returns the distinct array of first characters for search results.
4534    * @param bool $groupContacts
4535    *   If true, return only the contact ids.
4536    * @param bool $returnQuery
4537    *   Should we return the query as a string.
4538    * @param string $additionalWhereClause
4539    *   If the caller wants to further restrict the search (used for components).
4540    * @param null $sortOrder
4541    * @param string $additionalFromClause
4542    *   Should be clause with proper joins, effective to reduce where clause load.
4543    *
4544    * @param bool $skipOrderAndLimit
4545    *
4546    * @return CRM_Core_DAO
4547    */
4548   public function searchQuery(
4549     $offset = 0, $rowCount = 0, $sort = NULL,
4550     $count = FALSE, $includeContactIds = FALSE,
4551     $sortByChar = FALSE, $groupContacts = FALSE,
4552     $returnQuery = FALSE,
4553     $additionalWhereClause = NULL, $sortOrder = NULL,
4554     $additionalFromClause = NULL, $skipOrderAndLimit = FALSE
4555   ) {
4556
4557     if ($includeContactIds) {
4558       $this->_includeContactIds = TRUE;
4559       $this->_whereClause = $this->whereClause();
4560     }
4561
4562     $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params);
4563
4564     // if we’re explicitly looking for a certain contact’s contribs, events, etc.
4565     // and that contact happens to be deleted, set $onlyDeleted to true
4566     foreach ($this->_params as $values) {
4567       $name = CRM_Utils_Array::value(0, $values);
4568       $op = CRM_Utils_Array::value(1, $values);
4569       $value = CRM_Utils_Array::value(2, $values);
4570       if ($name == 'contact_id' and $op == '=') {
4571         if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) {
4572           $onlyDeleted = TRUE;
4573         }
4574         break;
4575       }
4576     }
4577
4578     // building the query string
4579     $groupBy = NULL;
4580     if (!$count) {
4581       if (isset($this->_groupByComponentClause)) {
4582         $groupBy = $this->_groupByComponentClause;
4583       }
4584       elseif ($this->_useGroupBy) {
4585         $groupBy = ' GROUP BY contact_a.id';
4586       }
4587     }
4588     if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) {
4589       $groupBy = 'GROUP BY civicrm_activity.id ';
4590     }
4591
4592     $order = $orderBy = $limit = '';
4593     if (!$count) {
4594       $config = CRM_Core_Config::singleton();
4595       if ($config->includeOrderByClause ||
4596         isset($this->_distinctComponentClause)
4597       ) {
4598         if ($sort) {
4599           if (is_string($sort)) {
4600             $orderBy = $sort;
4601           }
4602           else {
4603             $orderBy = trim($sort->orderBy());
4604           }
4605           if (!empty($orderBy)) {
4606             // this is special case while searching for
4607             // change log CRM-1718
4608             if (preg_match('/sort_name/i', $orderBy)) {
4609               $orderBy = str_replace('sort_name', 'contact_a.sort_name', $orderBy);
4610             }
4611
4612             $orderBy = CRM_Utils_Type::escape($orderBy, 'String');
4613             $order = " ORDER BY $orderBy";
4614
4615             if ($sortOrder) {
4616               $sortOrder = CRM_Utils_Type::escape($sortOrder, 'String');
4617               $order .= " $sortOrder";
4618             }
4619
4620             // always add contact_a.id to the ORDER clause
4621             // so the order is deterministic
4622             if (strpos('contact_a.id', $order) === FALSE) {
4623               $order .= ", contact_a.id";
4624             }
4625           }
4626         }
4627         elseif ($sortByChar) {
4628           $order = " ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc";
4629         }
4630         else {
4631           $order = " ORDER BY contact_a.sort_name asc, contact_a.id";
4632         }
4633       }
4634
4635       // hack for order clause
4636       if ($order) {
4637         $fieldStr = trim(str_replace('ORDER BY', '', $order));
4638         $fieldOrder = explode(' ', $fieldStr);
4639         $field = $fieldOrder[0];
4640
4641         if ($field) {
4642           switch ($field) {
4643             case 'city':
4644             case 'postal_code':
4645               $this->_whereTables["civicrm_address"] = 1;
4646               $order = str_replace($field, "civicrm_address.{$field}", $order);
4647               break;
4648
4649             case 'country':
4650             case 'state_province':
4651               $this->_whereTables["civicrm_{$field}"] = 1;
4652               $order = str_replace($field, "civicrm_{$field}.name", $order);
4653               break;
4654
4655             case 'email':
4656               $this->_whereTables["civicrm_email"] = 1;
4657               $order = str_replace($field, "civicrm_email.{$field}", $order);
4658               break;
4659
4660             default:
4661               //CRM-12565 add "`" around $field if it is a pseudo constant
4662               foreach ($this->_pseudoConstantsSelect as $key => $value) {
4663                 if (!empty($value['element']) && $value['element'] == $field) {
4664                   $order = str_replace($field, "`{$field}`", $order);
4665                 }
4666               }
4667           }
4668           $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4669           $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4670         }
4671       }
4672
4673       if ($rowCount > 0 && $offset >= 0) {
4674         $offset = CRM_Utils_Type::escape($offset, 'Int');
4675         $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
4676         $limit = " LIMIT $offset, $rowCount ";
4677       }
4678     }
4679
4680     // CRM-15231
4681     $this->_sort = $sort;
4682
4683     list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted);
4684
4685     if ($additionalWhereClause) {
4686       $where = $where . ' AND ' . $additionalWhereClause;
4687     }
4688
4689     //additional from clause should be w/ proper joins.
4690     if ($additionalFromClause) {
4691       $from .= "\n" . $additionalFromClause;
4692     }
4693
4694     // if we are doing a transform, do it here
4695     // use the $from, $where and $having to get the contact ID
4696     if ($this->_displayRelationshipType) {
4697       $this->filterRelatedContacts($from, $where, $having);
4698     }
4699
4700     if ($skipOrderAndLimit) {
4701       $query = "$select $from $where $having $groupBy";
4702     }
4703     else {
4704       $query = "$select $from $where $having $groupBy $order $limit";
4705     }
4706
4707     if ($returnQuery) {
4708       return $query;
4709     }
4710     if ($count) {
4711       return CRM_Core_DAO::singleValueQuery($query);
4712     }
4713
4714     $dao = CRM_Core_DAO::executeQuery($query);
4715     if ($groupContacts) {
4716       $ids = array();
4717       while ($dao->fetch()) {
4718         $ids[] = $dao->id;
4719       }
4720       return implode(',', $ids);
4721     }
4722
4723     return $dao;
4724   }
4725
4726   /**
4727    * Fetch a list of contacts from the prev/next cache for displaying a search results page
4728    *
4729    * @param string $cacheKey
4730    * @param int $offset
4731    * @param int $rowCount
4732    * @param bool $includeContactIds
4733    * @return CRM_Core_DAO
4734    */
4735   public function getCachedContacts($cacheKey, $offset, $rowCount, $includeContactIds) {
4736     $this->_includeContactIds = $includeContactIds;
4737     $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params);
4738     list($select, $from, $where) = $this->query(FALSE, FALSE, FALSE, $onlyDeleted);
4739     $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);
4740     $order = " ORDER BY pnc.id";
4741     $groupBy = " GROUP BY contact_a.id";
4742     $limit = " LIMIT $offset, $rowCount";
4743     $query = "$select $from $where $groupBy $order $limit";
4744
4745     return CRM_Core_DAO::executeQuery($query);
4746   }
4747
4748   /**
4749    * Populate $this->_permissionWhereClause with permission related clause and update other
4750    * query related properties.
4751    *
4752    * Function calls ACL permission class and hooks to filter the query appropriately
4753    *
4754    * Note that these 2 params were in the code when extracted from another function
4755    * and a second round extraction would be to make them properties of the class
4756    *
4757    * @param bool $onlyDeleted
4758    *   Only get deleted contacts.
4759    * @param bool $count
4760    *   Return Count only.
4761    */
4762   public function generatePermissionClause($onlyDeleted = FALSE, $count = FALSE) {
4763     if (!$this->_skipPermission) {
4764       $this->_permissionWhereClause = CRM_ACL_API::whereClause(
4765         CRM_Core_Permission::VIEW,
4766         $this->_tables,
4767         $this->_whereTables,
4768         NULL,
4769         $onlyDeleted,
4770         $this->_skipDeleteClause
4771       );
4772
4773       // regenerate fromClause since permission might have added tables
4774       if ($this->_permissionWhereClause) {
4775         //fix for row count in qill (in contribute/membership find)
4776         if (!$count) {
4777           $this->_useDistinct = TRUE;
4778         }
4779         //CRM-15231
4780         $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4781         $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
4782         // note : this modifies _fromClause and _simpleFromClause
4783         $this->includePseudoFieldsJoin($this->_sort);
4784       }
4785     }
4786     else {
4787       // add delete clause if needed even if we are skipping permission
4788       // CRM-7639
4789       if (!$this->_skipDeleteClause) {
4790         if (CRM_Core_Permission::check('access deleted contacts') and $onlyDeleted) {
4791           $this->_permissionWhereClause = '(contact_a.is_deleted)';
4792         }
4793         else {
4794           // CRM-6181
4795           $this->_permissionWhereClause = '(contact_a.is_deleted = 0)';
4796         }
4797       }
4798     }
4799   }
4800
4801   /**
4802    * @param $val
4803    */
4804   public function setSkipPermission($val) {
4805     $this->_skipPermission = $val;
4806   }
4807
4808   /**
4809    * @param null $context
4810    *
4811    * @return array
4812    */
4813   public function &summaryContribution($context = NULL) {
4814     list($innerselect, $from, $where, $having) = $this->query(TRUE);
4815
4816     // hack $select
4817     $select = "
4818 SELECT COUNT( conts.total_amount ) as total_count,
4819        SUM(   conts.total_amount ) as total_amount,
4820        AVG(   conts.total_amount ) as total_avg,
4821        conts.total_amount          as amount,
4822        conts.currency              as currency";
4823     if ($this->_permissionWhereClause) {
4824       $where .= " AND " . $this->_permissionWhereClause;
4825     }
4826     if ($context == 'search') {
4827       $where .= " AND contact_a.is_deleted = 0 ";
4828     }
4829     CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes($financialTypes);
4830     if (!empty($financialTypes)) {
4831       $where .= " AND civicrm_contribution.financial_type_id IN (" . implode(',', array_keys($financialTypes)) . ") AND li.id IS NULL";
4832       $from .= " LEFT JOIN civicrm_line_item li
4833                       ON civicrm_contribution.id = li.contribution_id AND
4834                          li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN (" . implode(',', array_keys($financialTypes)) . ") ";
4835     }
4836     else {
4837       $where .= " AND civicrm_contribution.financial_type_id IN (0)";
4838     }
4839
4840     // make sure contribution is completed - CRM-4989
4841     $completedWhere = $where . " AND civicrm_contribution.contribution_status_id = 1 ";
4842
4843     $summary = array();
4844     $summary['total'] = array();
4845     $summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a";
4846     $innerQuery = "SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
4847       civicrm_contribution.currency $from $completedWhere";
4848     $query = "$select FROM (
4849       $innerQuery GROUP BY civicrm_contribution.id
4850     ) as conts
4851     GROUP BY currency";
4852
4853     $dao = CRM_Core_DAO::executeQuery($query);
4854
4855     $summary['total']['count'] = 0;
4856     $summary['total']['amount'] = $summary['total']['avg'] = array();
4857     while ($dao->fetch()) {
4858       $summary['total']['count'] += $dao->total_count;
4859       $summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
4860       $summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
4861     }
4862
4863     $orderBy = 'ORDER BY civicrm_contribution_total_amount_count DESC';
4864     $groupBy = 'GROUP BY currency, civicrm_contribution.total_amount';
4865     $modeSQL = "$select, conts.civicrm_contribution_total_amount_count as civicrm_contribution_total_amount_count FROM ($innerQuery
4866     $groupBy $orderBy) as conts
4867     GROUP BY currency";
4868
4869     $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL);
4870
4871     $medianSQL = "{$from} {$completedWhere}";
4872     $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution');
4873     $summary['total']['currencyCount'] = count($summary['total']['median']);
4874
4875     if (!empty($summary['total']['amount'])) {
4876       $summary['total']['amount'] = implode(',&nbsp;', $summary['total']['amount']);
4877       $summary['total']['avg'] = implode(',&nbsp;', $summary['total']['avg']);
4878       $summary['total']['mode'] = implode(',&nbsp;', $summary['total']['mode']);
4879       $summary['total']['median'] = implode(',&nbsp;', $summary['total']['median']);
4880     }
4881     else {
4882       $summary['total']['amount'] = $summary['total']['avg'] = $summary['total']['median'] = 0;
4883     }
4884
4885     // soft credit summary
4886     if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
4887       $softCreditWhere = "{$completedWhere} AND civicrm_contribution_soft.id IS NOT NULL";
4888       $query = "
4889         $select FROM (
4890           SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency $from $softCreditWhere
4891           GROUP BY civicrm_contribution_soft.id
4892         ) as conts
4893         GROUP BY currency";
4894       $dao = CRM_Core_DAO::executeQuery($query);
4895       $summary['soft_credit']['count'] = 0;
4896       $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = array();
4897       while ($dao->fetch()) {
4898         $summary['soft_credit']['count'] += $dao->total_count;
4899         $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
4900         $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
4901       }
4902       if (!empty($summary['soft_credit']['amount'])) {
4903         $summary['soft_credit']['amount'] = implode(',&nbsp;', $summary['soft_credit']['amount']);
4904         $summary['soft_credit']['avg'] = implode(',&nbsp;', $summary['soft_credit']['avg']);
4905       }
4906       else {
4907         $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0;
4908       }
4909     }
4910
4911     // hack $select
4912     //@todo  - this could be one query using the IF in mysql - eg
4913     //  SELECT sum(total_completed), sum(count_completed), sum(count_cancelled), sum(total_cancelled) FROM (
4914     //   SELECT civicrm_contribution.total_amount, civicrm_contribution.currency  ,
4915     //  IF(civicrm_contribution.contribution_status_id = 1, 1, 0 ) as count_completed,
4916     //  IF(civicrm_contribution.contribution_status_id = 1, total_amount, 0 ) as total_completed,
4917     //  IF(civicrm_contribution.cancel_date IS NOT NULL = 1, 1, 0 ) as count_cancelled,
4918     //  IF(civicrm_contribution.cancel_date IS NOT NULL = 1, total_amount, 0 ) as total_cancelled
4919     // FROM civicrm_contact contact_a
4920     //  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
4921     // WHERE  ( ... where clause....
4922     // AND (civicrm_contribution.cancel_date IS NOT NULL OR civicrm_contribution.contribution_status_id = 1)
4923     //  ) as conts
4924
4925     $select = "
4926 SELECT COUNT( conts.total_amount ) as cancel_count,
4927        SUM(   conts.total_amount ) as cancel_amount,
4928        AVG(   conts.total_amount ) as cancel_avg,
4929        conts.currency              as currency";
4930
4931     $where .= " AND civicrm_contribution.cancel_date IS NOT NULL ";
4932     if ($context == 'search') {
4933       $where .= " AND contact_a.is_deleted = 0 ";
4934     }
4935
4936     $query = "$select FROM (
4937       SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where
4938       GROUP BY civicrm_contribution.id
4939     ) as conts
4940     GROUP BY currency";
4941
4942     $dao = CRM_Core_DAO::executeQuery($query);
4943
4944     if ($dao->N <= 1) {
4945       if ($dao->fetch()) {
4946         $summary['cancel']['count'] = $dao->cancel_count;
4947         $summary['cancel']['amount'] = $dao->cancel_amount;
4948         $summary['cancel']['avg'] = $dao->cancel_avg;
4949       }
4950     }
4951     else {
4952       $summary['cancel']['count'] = 0;
4953       $summary['cancel']['amount'] = $summary['cancel']['avg'] = array();
4954       while ($dao->fetch()) {
4955         $summary['cancel']['count'] += $dao->cancel_count;
4956         $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency);
4957         $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency);
4958       }
4959       $summary['cancel']['amount'] = implode(',&nbsp;', $summary['cancel']['amount']);
4960       $summary['cancel']['avg'] = implode(',&nbsp;', $summary['cancel']['avg']);
4961     }
4962
4963     return $summary;
4964   }
4965
4966   /**
4967    * Getter for the qill object.
4968    *
4969    * @return string
4970    */
4971   public function qill() {
4972     return $this->_qill;
4973   }
4974
4975   /**
4976    * Default set of return default hier return properties.
4977    *
4978    * @return array
4979    */
4980   public static function &defaultHierReturnProperties() {
4981     if (!isset(self::$_defaultHierReturnProperties)) {
4982       self::$_defaultHierReturnProperties = array(
4983         'home_URL' => 1,
4984         'image_URL' => 1,
4985         'legal_identifier' => 1,
4986         'external_identifier' => 1,
4987         'contact_type' => 1,
4988         'contact_sub_type' => 1,
4989         'sort_name' => 1,
4990         'display_name' => 1,
4991         'nick_name' => 1,
4992         'first_name' => 1,
4993         'middle_name' => 1,
4994         'last_name' => 1,
4995         'prefix_id' => 1,
4996         'suffix_id' => 1,
4997         'formal_title' => 1,
4998         'communication_style_id' => 1,
4999         'email_greeting' => 1,
5000         'postal_greeting' => 1,
5001         'addressee' => 1,
5002         'birth_date' => 1,
5003         'gender_id' => 1,
5004         'preferred_communication_method' => 1,
5005         'do_not_phone' => 1,
5006         'do_not_email' => 1,
5007         'do_not_mail' => 1,
5008         'do_not_sms' => 1,
5009         'do_not_trade' => 1,
5010         'location' => array(
5011           '1' => array(
5012             'location_type' => 1,
5013             'street_address' => 1,
5014             'city' => 1,
5015             'state_province' => 1,
5016             'postal_code' => 1,
5017             'postal_code_suffix' => 1,
5018             'country' => 1,
5019             'phone-Phone' => 1,
5020             'phone-Mobile' => 1,
5021             'phone-Fax' => 1,
5022             'phone-1' => 1,
5023             'phone-2' => 1,
5024             'phone-3' => 1,
5025             'im-1' => 1,
5026             'im-2' => 1,
5027             'im-3' => 1,
5028             'email-1' => 1,
5029             'email-2' => 1,
5030             'email-3' => 1,
5031           ),
5032           '2' => array(
5033             'location_type' => 1,
5034             'street_address' => 1,
5035             'city' => 1,
5036             'state_province' => 1,
5037             'postal_code' => 1,
5038             'postal_code_suffix' => 1,
5039             'country' => 1,
5040             'phone-Phone' => 1,
5041             'phone-Mobile' => 1,
5042             'phone-1' => 1,
5043             'phone-2' => 1,
5044             'phone-3' => 1,
5045             'im-1' => 1,
5046             'im-2' => 1,
5047             'im-3' => 1,
5048             'email-1' => 1,
5049             'email-2' => 1,
5050             'email-3' => 1,
5051           ),
5052         ),
5053       );
5054     }
5055     return self::$_defaultHierReturnProperties;
5056   }
5057
5058   /**
5059    * Build query for a date field.
5060    *
5061    * @param array $values
5062    * @param string $tableName
5063    * @param string $fieldName
5064    * @param string $dbFieldName
5065    * @param string $fieldTitle
5066    * @param bool $appendTimeStamp
5067    */
5068   public function dateQueryBuilder(
5069     &$values, $tableName, $fieldName,
5070     $dbFieldName, $fieldTitle,
5071     $appendTimeStamp = TRUE
5072   ) {
5073     list($name, $op, $value, $grouping, $wildcard) = $values;
5074
5075     if ($name == "{$fieldName}_low" ||
5076       $name == "{$fieldName}_high"
5077     ) {
5078       if (isset($this->_rangeCache[$fieldName]) || !$value) {
5079         return;
5080       }
5081       $this->_rangeCache[$fieldName] = 1;
5082
5083       $secondOP = $secondPhrase = $secondValue = $secondDate = $secondDateFormat = NULL;
5084
5085       if ($name == $fieldName . '_low') {
5086         $firstOP = '>=';
5087         $firstPhrase = ts('greater than or equal to');
5088         $firstDate = CRM_Utils_Date::processDate($value);
5089
5090         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5091         if (!empty($secondValues) && $secondValues[2]) {
5092           $secondOP = '<=';
5093           $secondPhrase = ts('less than or equal to');
5094           $secondValue = $secondValues[2];
5095
5096           if ($appendTimeStamp && strlen($secondValue) == 10) {
5097             $secondValue .= ' 23:59:59';
5098           }
5099           $secondDate = CRM_Utils_Date::processDate($secondValue);
5100         }
5101       }
5102       elseif ($name == $fieldName . '_high') {
5103         $firstOP = '<=';
5104         $firstPhrase = ts('less than or equal to');
5105
5106         if ($appendTimeStamp && strlen($value) == 10) {
5107           $value .= ' 23:59:59';
5108         }
5109         $firstDate = CRM_Utils_Date::processDate($value);
5110
5111         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5112         if (!empty($secondValues) && $secondValues[2]) {
5113           $secondOP = '>=';
5114           $secondPhrase = ts('greater than or equal to');
5115           $secondValue = $secondValues[2];
5116           $secondDate = CRM_Utils_Date::processDate($secondValue);
5117         }
5118       }
5119
5120       if (!$appendTimeStamp) {
5121         $firstDate = substr($firstDate, 0, 8);
5122       }
5123       $firstDateFormat = CRM_Utils_Date::customFormat($firstDate);
5124
5125       if ($secondDate) {
5126         if (!$appendTimeStamp) {
5127           $secondDate = substr($secondDate, 0, 8);
5128         }
5129         $secondDateFormat = CRM_Utils_Date::customFormat($secondDate);
5130       }
5131
5132       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5133       if ($secondDate) {
5134         $this->_where[$grouping][] = "
5135 ( {$tableName}.{$dbFieldName} $firstOP '$firstDate' ) AND
5136 ( {$tableName}.{$dbFieldName} $secondOP '$secondDate' )
5137 ";
5138         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$firstDateFormat\" " . ts('AND') . " $secondPhrase \"$secondDateFormat\"";
5139       }
5140       else {
5141         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP '$firstDate'";
5142         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$firstDateFormat\"";
5143       }
5144     }
5145
5146     if ($name == $fieldName) {
5147       //In Get API, for operators other then '=' the $value is in array(op => value) format
5148       if (is_array($value) && !empty($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
5149         $op = key($value);
5150         $value = $value[$op];
5151       }
5152
5153       $date = $format = NULL;
5154       if (strstr($op, 'IN')) {
5155         $format = array();
5156         foreach ($value as &$date) {
5157           $date = CRM_Utils_Date::processDate($date);
5158           if (!$appendTimeStamp) {
5159             $date = substr($date, 0, 8);
5160           }
5161           $format[] = CRM_Utils_Date::customFormat($date);
5162         }
5163         $date = "('" . implode("','", $value) . "')";
5164         $format = implode(', ', $format);
5165       }
5166       elseif ($value && (!strstr($op, 'NULL') && !strstr($op, 'EMPTY'))) {
5167         $date = CRM_Utils_Date::processDate($value);
5168         if (!$appendTimeStamp) {
5169           $date = substr($date, 0, 8);
5170         }
5171         $format = CRM_Utils_Date::customFormat($date);
5172         $date = "'$date'";
5173       }
5174
5175       if ($date) {
5176         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op $date";
5177       }
5178       else {
5179         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op";
5180       }
5181
5182       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5183
5184       $op = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
5185       $this->_qill[$grouping][] = "$fieldTitle $op $format";
5186     }
5187   }
5188
5189   /**
5190    * @param $values
5191    * @param string $tableName
5192    * @param string $fieldName
5193    * @param string $dbFieldName
5194    * @param $fieldTitle
5195    * @param null $options
5196    */
5197   public function numberRangeBuilder(
5198     &$values,
5199     $tableName, $fieldName,
5200     $dbFieldName, $fieldTitle,
5201     $options = NULL
5202   ) {
5203     list($name, $op, $value, $grouping, $wildcard) = $values;
5204
5205     if ($name == "{$fieldName}_low" ||
5206       $name == "{$fieldName}_high"
5207     ) {
5208       if (isset($this->_rangeCache[$fieldName])) {
5209         return;
5210       }
5211       $this->_rangeCache[$fieldName] = 1;
5212
5213       $secondOP = $secondPhrase = $secondValue = NULL;
5214
5215       if ($name == "{$fieldName}_low") {
5216         $firstOP = '>=';
5217         $firstPhrase = ts('greater than');
5218
5219         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5220         if (!empty($secondValues)) {
5221           $secondOP = '<=';
5222           $secondPhrase = ts('less than');
5223           $secondValue = $secondValues[2];
5224         }
5225       }
5226       else {
5227         $firstOP = '<=';
5228         $firstPhrase = ts('less than');
5229
5230         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5231         if (!empty($secondValues)) {
5232           $secondOP = '>=';
5233           $secondPhrase = ts('greater than');
5234           $secondValue = $secondValues[2];
5235         }
5236       }
5237
5238       if ($secondOP) {
5239         $this->_where[$grouping][] = "
5240 ( {$tableName}.{$dbFieldName} $firstOP {$value} ) AND
5241 ( {$tableName}.{$dbFieldName} $secondOP {$secondValue} )
5242 ";
5243         $displayValue = $options ? $options[$value] : $value;
5244         $secondDisplayValue = $options ? $options[$secondValue] : $secondValue;
5245
5246         $this->_qill[$grouping][]
5247           = "$fieldTitle - $firstPhrase \"$displayValue\" " . ts('AND') . " $secondPhrase \"$secondDisplayValue\"";
5248       }
5249       else {
5250         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP {$value}";
5251         $displayValue = $options ? $options[$value] : $value;
5252         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$displayValue\"";
5253       }
5254       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5255
5256       return;
5257     }
5258
5259     if ($name == $fieldName) {
5260       $op = '=';
5261       $phrase = '=';
5262
5263       $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $op {$value}";
5264
5265       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5266       $displayValue = $options ? $options[$value] : $value;
5267       $this->_qill[$grouping][] = "$fieldTitle - $phrase \"$displayValue\"";
5268     }
5269   }
5270
5271
5272   /**
5273    * @param $values
5274    * @param string $tableName
5275    * @param string $fieldName
5276    * @param string $dbFieldName
5277    * @param $fieldTitle
5278    * @param null $options
5279    */
5280   public function ageRangeQueryBuilder(
5281     &$values,
5282     $tableName, $fieldName,
5283     $dbFieldName, $fieldTitle,
5284     $options = NULL
5285   ) {
5286     list($name, $op, $value, $grouping, $wildcard) = $values;
5287
5288     $asofDateValues = $this->getWhereValues("{$fieldName}_asof_date", $grouping);
5289     $asofDate = NULL;  // will be treated as current day
5290     if ($asofDateValues) {
5291       $asofDate = CRM_Utils_Date::processDate($asofDateValues[2]);
5292       $asofDateFormat = CRM_Utils_Date::customFormat(substr($asofDate, 0, 8));
5293       $fieldTitle .= ' ' . ts('as of') . ' ' . $asofDateFormat;
5294     }
5295
5296     if ($name == "{$fieldName}_low" ||
5297       $name == "{$fieldName}_high"
5298     ) {
5299       if (isset($this->_rangeCache[$fieldName])) {
5300         return;
5301       }
5302       $this->_rangeCache[$fieldName] = 1;
5303
5304       $secondOP = $secondPhrase = $secondValue = NULL;
5305
5306       if ($name == "{$fieldName}_low") {
5307         $firstPhrase = ts('greater than or equal to');
5308         // NB: age > X means date of birth < Y
5309         $firstOP = '<=';
5310         $firstDate = self::calcDateFromAge($asofDate, $value, 'min');
5311
5312         $secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
5313         if (!empty($secondValues)) {
5314           $secondOP = '>=';
5315           $secondPhrase = ts('less than or equal to');
5316           $secondValue = $secondValues[2];
5317           $secondDate = self::calcDateFromAge($asofDate, $secondValue, 'max');
5318         }
5319       }
5320       else {
5321         $firstOP = '>=';
5322         $firstPhrase = ts('less than or equal to');
5323         $firstDate = self::calcDateFromAge($asofDate, $value, 'max');
5324
5325         $secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
5326         if (!empty($secondValues)) {
5327           $secondOP = '<=';
5328           $secondPhrase = ts('greater than or equal to');
5329           $secondValue = $secondValues[2];
5330           $secondDate = self::calcDateFromAge($asofDate, $secondValue, 'min');
5331         }
5332       }
5333
5334       if ($secondOP) {
5335         $this->_where[$grouping][] = "
5336 ( {$tableName}.{$dbFieldName} $firstOP '$firstDate' ) AND
5337 ( {$tableName}.{$dbFieldName} $secondOP '$secondDate' )
5338 ";
5339         $displayValue = $options ? $options[$value] : $value;
5340         $secondDisplayValue = $options ? $options[$secondValue] : $secondValue;
5341
5342         $this->_qill[$grouping][]
5343           = "$fieldTitle - $firstPhrase \"$displayValue\" " . ts('AND') . " $secondPhrase \"$secondDisplayValue\"";
5344       }
5345       else {
5346         $this->_where[$grouping][] = "{$tableName}.{$dbFieldName} $firstOP '$firstDate'";
5347         $displayValue = $options ? $options[$value] : $value;
5348         $this->_qill[$grouping][] = "$fieldTitle - $firstPhrase \"$displayValue\"";
5349       }
5350       $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
5351       return;
5352     }
5353   }
5354
5355   public static function calcDateFromAge($asofDate, $age, $type) {
5356     $date = new DateTime($asofDate);
5357     if ($type == "min") {
5358       // minimum age is $age: dob <= date - age "235959"
5359       $date->sub(new DateInterval("P" . $age . "Y"));
5360       return $date->format('Ymd') . "235959";
5361     }
5362     else {
5363       // max age is $age: dob >= date - (age + 1y) + 1d "000000"
5364       $date->sub(new DateInterval("P" . ($age + 1) . "Y"))->add(new DateInterval("P1D"));
5365       return $date->format('Ymd') . "000000";
5366     }
5367   }
5368
5369   /**
5370    * Given the field name, operator, value & its data type
5371    * builds the where Clause for the query
5372    * used for handling 'IS NULL'/'IS NOT NULL' operators
5373    *
5374    * @param string $field
5375    *   Fieldname.
5376    * @param string $op
5377    *   Operator.
5378    * @param string $value
5379    *   Value.
5380    * @param string $dataType
5381    *   Data type of the field.
5382    *
5383    * @return string
5384    *   Where clause for the query.
5385    */
5386   public static function buildClause($field, $op, $value = NULL, $dataType = NULL) {
5387     $op = trim($op);
5388     $clause = "$field $op";
5389
5390     switch ($op) {
5391       case 'IS NULL':
5392       case 'IS NOT NULL':
5393         return $clause;
5394
5395       case 'IS EMPTY':
5396         $clause = " (NULLIF($field, '') IS NULL) ";
5397         return $clause;
5398
5399       case 'IS NOT EMPTY':
5400         $clause = " (NULLIF($field, '') IS NOT NULL) ";
5401         return $clause;
5402
5403       case 'IN':
5404       case 'NOT IN':
5405         // I feel like this would be escaped properly if passed through $queryString = CRM_Core_DAO::createSqlFilter.
5406         if (!empty($value) && (!is_array($value) || !array_key_exists($op, $value))) {
5407           $value = array($op => (array) $value);
5408         }
5409
5410       default:
5411         if (empty($dataType)) {
5412           $dataType = 'String';
5413         }
5414         if (is_array($value)) {
5415           //this could have come from the api - as in the restWhere section we potentially use the api operator syntax which is becoming more
5416           // widely used and consistent across the codebase
5417           // adding this here won't accept the search functions which don't submit an array
5418           if (($queryString = CRM_Core_DAO::createSqlFilter($field, $value, $dataType)) != FALSE) {
5419
5420             return $queryString;
5421           }
5422
5423           // 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))
5424           // but we got only array(2,5) from the form.
5425           // We could get away with keeping this in 4.6 if we make it such that it throws an enotice in 4.7 so
5426           // people have to de-slopify it.
5427           if (!empty($value[0])) {
5428             $dragonPlace = $iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReport;
5429             if (($queryString = CRM_Core_DAO::createSqlFilter($field, array($op => $value), $dataType)) != FALSE) {
5430               return $queryString;
5431             }
5432           }
5433           else {
5434             $op = 'IN';
5435             $dragonPlace = $iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReportUsingOldFormat;
5436             if (($queryString = CRM_Core_DAO::createSqlFilter($field, array($op => array_keys($value)), $dataType)) != FALSE) {
5437               return $queryString;
5438             }
5439           }
5440         }
5441
5442         $value = CRM_Utils_Type::escape($value, $dataType);
5443         // if we don't have a dataType we should assume
5444         if ($dataType == 'String' || $dataType == 'Text') {
5445           $value = "'" . strtolower($value) . "'";
5446         }
5447         return "$clause $value";
5448     }
5449   }
5450
5451   /**
5452    * @param bool $reset
5453    *
5454    * @return array
5455    */
5456   public function openedSearchPanes($reset = FALSE) {
5457     if (!$reset || empty($this->_whereTables)) {
5458       return self::$_openedPanes;
5459     }
5460
5461     // pane name to table mapper
5462     $panesMapper = array(
5463       ts('Contributions') => 'civicrm_contribution',
5464       ts('Memberships') => 'civicrm_membership',
5465       ts('Events') => 'civicrm_participant',
5466       ts('Relationships') => 'civicrm_relationship',
5467       ts('Activities') => 'civicrm_activity',
5468       ts('Pledges') => 'civicrm_pledge',
5469       ts('Cases') => 'civicrm_case',
5470       ts('Grants') => 'civicrm_grant',
5471       ts('Address Fields') => 'civicrm_address',
5472       ts('Notes') => 'civicrm_note',
5473       ts('Change Log') => 'civicrm_log',
5474       ts('Mailings') => 'civicrm_mailing_event_queue',
5475     );
5476     CRM_Contact_BAO_Query_Hook::singleton()->getPanesMapper($panesMapper);
5477
5478     foreach (array_keys($this->_whereTables) as $table) {
5479       if ($panName = array_search($table, $panesMapper)) {
5480         self::$_openedPanes[$panName] = TRUE;
5481       }
5482     }
5483
5484     return self::$_openedPanes;
5485   }
5486
5487   /**
5488    * @param $operator
5489    */
5490   public function setOperator($operator) {
5491     $validOperators = array('AND', 'OR');
5492     if (!in_array($operator, $validOperators)) {
5493       $operator = 'AND';
5494     }
5495     $this->_operator = $operator;
5496   }
5497
5498   /**
5499    * @return string
5500    */
5501   public function getOperator() {
5502     return $this->_operator;
5503   }
5504
5505   /**
5506    * @param $from
5507    * @param $where
5508    * @param $having
5509    */
5510   public function filterRelatedContacts(&$from, &$where, &$having) {
5511     static $_rTypeProcessed = NULL;
5512     static $_rTypeFrom = NULL;
5513     static $_rTypeWhere = NULL;
5514
5515     if (!$_rTypeProcessed) {
5516       $_rTypeProcessed = TRUE;
5517
5518       // create temp table with contact ids
5519       $tableName = CRM_Core_DAO::createTempTableName('civicrm_transform', TRUE);
5520       $sql = "CREATE TEMPORARY TABLE $tableName ( contact_id int primary key) ENGINE=HEAP";
5521       CRM_Core_DAO::executeQuery($sql);
5522
5523       $sql = "
5524 REPLACE INTO $tableName ( contact_id )
5525 SELECT contact_a.id
5526        $from
5527        $where
5528        $having
5529 ";
5530       CRM_Core_DAO::executeQuery($sql);
5531
5532       $qillMessage = ts('Contacts with a Relationship Type of: ');
5533       $rTypes = CRM_Core_PseudoConstant::relationshipType();
5534
5535       if (is_numeric($this->_displayRelationshipType)) {
5536         $relationshipTypeLabel = $rTypes[$this->_displayRelationshipType]['label_a_b'];
5537         $_rTypeFrom = "
5538 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id OR displayRelType.contact_id_b = contact_a.id )
5539 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a OR transform_temp.contact_id = displayRelType.contact_id_b )
5540 ";
5541         $_rTypeWhere = "
5542 WHERE displayRelType.relationship_type_id = {$this->_displayRelationshipType}
5543 AND   displayRelType.is_active = 1
5544 ";
5545       }
5546       else {
5547         list($relType, $dirOne, $dirTwo) = explode('_', $this->_displayRelationshipType);
5548         if ($dirOne == 'a') {
5549           $relationshipTypeLabel = $rTypes[$relType]['label_a_b'];
5550           $_rTypeFrom .= "
5551 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id )
5552 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_b )
5553 ";
5554         }
5555         else {
5556           $relationshipTypeLabel = $rTypes[$relType]['label_b_a'];
5557           $_rTypeFrom .= "
5558 INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_b = contact_a.id )
5559 INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a )
5560 ";
5561         }
5562         $_rTypeWhere = "
5563 WHERE displayRelType.relationship_type_id = $relType
5564 AND   displayRelType.is_active = 1
5565 ";
5566       }
5567       $this->_qill[0][] = $qillMessage . "'" . $relationshipTypeLabel . "'";
5568     }
5569
5570     if (!empty($this->_permissionWhereClause)) {
5571       $_rTypeWhere .= "AND $this->_permissionWhereClause";
5572     }
5573
5574     if (strpos($from, $_rTypeFrom) === FALSE) {
5575       // lets replace all the INNER JOIN's in the $from so we dont exclude other data
5576       // this happens when we have an event_type in the quert (CRM-7969)
5577       $from = str_replace("INNER JOIN", "LEFT JOIN", $from);
5578       $from .= $_rTypeFrom;
5579       $where = $_rTypeWhere;
5580     }
5581
5582     $having = NULL;
5583   }
5584
5585   /**
5586    * @param $op
5587    *
5588    * @return bool
5589    */
5590   public static function caseImportant($op) {
5591     return
5592       in_array($op, array('LIKE', 'IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY')) ? FALSE : TRUE;
5593   }
5594
5595   /**
5596    * @param $returnProperties
5597    * @param $prefix
5598    *
5599    * @return bool
5600    */
5601   public static function componentPresent(&$returnProperties, $prefix) {
5602     foreach ($returnProperties as $name => $dontCare) {
5603       if (substr($name, 0, strlen($prefix)) == $prefix) {
5604         return TRUE;
5605       }
5606     }
5607     return FALSE;
5608   }
5609
5610   /**
5611    * Builds the necessary structures for all fields that are similar to option value look-ups.
5612    *
5613    * @param string $name
5614    *   the name of the field.
5615    * @param string $op
5616    *   the sql operator, this function should handle ALL SQL operators.
5617    * @param string $value
5618    *   depends on the operator and who's calling the query builder.
5619    * @param int $grouping
5620    *   the index where to place the where clause.
5621    * @param $selectValues
5622    *   The key value pairs for this element. This allows us to use this function for things besides option-value pairs.
5623    * @param array $field
5624    *   an array that contains various properties of the field identified by $name.
5625    * @param string $label
5626    *   The label for this field element.
5627    * @param string $dataType
5628    *   The data type for this element.
5629    * @param bool $useIDsOnly
5630    */
5631   public function optionValueQuery(
5632     $name,
5633     $op,
5634     $value,
5635     $grouping,
5636     $daoName = NULL,
5637     $field,
5638     $label,
5639     $dataType = 'String',
5640     $useIDsOnly = FALSE
5641   ) {
5642
5643     $pseudoFields = array(
5644       'email_greeting',
5645       'postal_greeting',
5646       'addressee',
5647       'gender_id',
5648       'prefix_id',
5649       'suffix_id',
5650       'communication_style_id',
5651     );
5652
5653     if ($useIDsOnly) {
5654       list($tableName, $fieldName) = explode('.', $field['where'], 2);
5655       if ($tableName == 'civicrm_contact') {
5656         $wc = "contact_a.$fieldName";
5657       }
5658       else {
5659         $wc = "$tableName.id";
5660       }
5661     }
5662     else {
5663       $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
5664     }
5665     if (in_array($name, $pseudoFields)) {
5666       if (!in_array($name, array('gender_id', 'prefix_id', 'suffix_id', 'communication_style_id'))) {
5667         $wc = "contact_a.{$name}_id";
5668       }
5669       $dataType = 'Positive';
5670       $value = (!$value) ? 0 : $value;
5671     }
5672     if ($name == "world_region") {
5673       $field['name'] = $name;
5674     }
5675
5676     list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue($daoName, $field['name'], $value, $op);
5677     $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $label, 2 => $qillop, 3 => $qillVal));
5678     $this->_where[$grouping][] = self::buildClause($wc, $op, $value, $dataType);
5679   }
5680
5681   /**
5682    * Check and explode a user defined numeric string into an array
5683    * this was the protocol used by search builder in the old old days before we had
5684    * super nice js widgets to do the hard work
5685    *
5686    * @param string $string
5687    * @param string $dataType
5688    *   The dataType we should check for the values, default integer.
5689    *
5690    * @return bool|array
5691    *   false if string does not match the pattern
5692    *   array of numeric values if string does match the pattern
5693    */
5694   public static function parseSearchBuilderString($string, $dataType = 'Integer') {
5695     $string = trim($string);
5696     if (substr($string, 0, 1) != '(' || substr($string, -1, 1) != ')') {
5697       Return FALSE;
5698     }
5699
5700     $string = substr($string, 1, -1);
5701     $values = explode(',', $string);
5702     if (empty($values)) {
5703       return FALSE;
5704     }
5705
5706     $returnValues = array();
5707     foreach ($values as $v) {
5708       if ($dataType == 'Integer' && !is_numeric($v)) {
5709         return FALSE;
5710       }
5711       elseif ($dataType == 'String' && !is_string($v)) {
5712         return FALSE;
5713       }
5714       $returnValues[] = trim($v);
5715     }
5716
5717     if (empty($returnValues)) {
5718       return FALSE;
5719     }
5720
5721     return $returnValues;
5722   }
5723
5724   /**
5725    * Convert the pseudo constants id's to their names
5726    *
5727    * @param CRM_Core_DAO $dao
5728    * @param bool $return
5729    * @param bool $usedForAPI
5730    *
5731    * @return array|NULL
5732    */
5733   public function convertToPseudoNames(&$dao, $return = FALSE, $usedForAPI = FALSE) {
5734     if (empty($this->_pseudoConstantsSelect)) {
5735       return NULL;
5736     }
5737     $values = array();
5738     foreach ($this->_pseudoConstantsSelect as $key => $value) {
5739       if (!empty($this->_pseudoConstantsSelect[$key]['sorting'])) {
5740         continue;
5741       }
5742
5743       if (is_object($dao) && property_exists($dao, $value['idCol'])) {
5744         $val = $dao->$value['idCol'];
5745
5746         if (CRM_Utils_System::isNull($val)) {
5747           $dao->$key = NULL;
5748         }
5749         elseif ($baoName = CRM_Utils_Array::value('bao', $value, NULL)) {
5750           //preserve id value
5751           $idColumn = "{$key}_id";
5752           $dao->$idColumn = $val;
5753
5754           if ($key == 'state_province_name') {
5755             $dao->$value['pseudoField'] = $dao->$key = CRM_Core_PseudoConstant::stateProvinceAbbreviation($val);
5756           }
5757           else {
5758             $dao->$value['pseudoField'] = $dao->$key = CRM_Core_PseudoConstant::getLabel($baoName, $value['pseudoField'], $val);
5759           }
5760         }
5761         elseif ($value['pseudoField'] == 'state_province_abbreviation') {
5762           $dao->$key = CRM_Core_PseudoConstant::stateProvinceAbbreviation($val);
5763         }
5764         // FIX ME: we should potentially move this to component Query and write a wrapper function that
5765         // handles pseudoconstant fixes for all component
5766         elseif (in_array($value['pseudoField'], array('participant_role_id', 'participant_role'))) {
5767           $viewValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $val);
5768
5769           if ($value['pseudoField'] == 'participant_role') {
5770             $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', 'role_id');
5771             foreach ($viewValues as $k => $v) {
5772               $viewValues[$k] = $pseudoOptions[$v];
5773             }
5774           }
5775           $dao->$key = ($usedForAPI && count($viewValues) > 1) ? $viewValues : implode(', ', $viewValues);
5776         }
5777         else {
5778           $labels = CRM_Core_OptionGroup::values($value['pseudoField']);
5779           $dao->$key = $labels[$val];
5780         }
5781
5782         // return converted values in array format
5783         if ($return) {
5784           if (strpos($key, '-') !== FALSE) {
5785             $keyVal = explode('-', $key);
5786             $current = &$values;
5787             $lastElement = array_pop($keyVal);
5788             foreach ($keyVal as $v) {
5789               if (!array_key_exists($v, $current)) {
5790                 $current[$v] = array();
5791               }
5792               $current = &$current[$v];
5793             }
5794             $current[$lastElement] = $dao->$key;
5795           }
5796           else {
5797             $values[$key] = $dao->$key;
5798           }
5799         }
5800       }
5801     }
5802     return $values;
5803   }
5804
5805   /**
5806    * Include pseudo fields LEFT JOIN.
5807    * @param string|array $sort can be a object or string
5808    *
5809    * @return array|NULL
5810    */
5811   public function includePseudoFieldsJoin($sort) {
5812     if (!$sort || empty($this->_pseudoConstantsSelect)) {
5813       return NULL;
5814     }
5815     $sort = is_string($sort) ? $sort : $sort->orderBy();
5816     $present = array();
5817
5818     foreach ($this->_pseudoConstantsSelect as $name => $value) {
5819       if (!empty($value['table'])) {
5820         $regex = "/({$value['table']}\.|{$name})/";
5821         if (preg_match($regex, $sort)) {
5822           $this->_elemnt[$value['element']] = 1;
5823           $this->_select[$value['element']] = $value['select'];
5824           $this->_pseudoConstantsSelect[$name]['sorting'] = 1;
5825           $present[$value['table']] = $value['join'];
5826         }
5827       }
5828     }
5829     $presentSimpleFrom = $present;
5830
5831     if (array_key_exists('civicrm_worldregion', $this->_whereTables) &&
5832       array_key_exists('civicrm_country', $presentSimpleFrom)
5833     ) {
5834       unset($presentSimpleFrom['civicrm_country']);
5835     }
5836     if (array_key_exists('civicrm_worldregion', $this->_tables) &&
5837       array_key_exists('civicrm_country', $present)
5838     ) {
5839       unset($present['civicrm_country']);
5840     }
5841
5842     $presentClause = $presentSimpleFromClause = NULL;
5843     if (!empty($present)) {
5844       $presentClause = implode(' ', $present);
5845     }
5846     if (!empty($presentSimpleFrom)) {
5847       $presentSimpleFromClause = implode(' ', $presentSimpleFrom);
5848     }
5849
5850     $this->_fromClause = $this->_fromClause . $presentClause;
5851     $this->_simpleFromClause = $this->_simpleFromClause . $presentSimpleFromClause;
5852
5853     return array($presentClause, $presentSimpleFromClause);
5854   }
5855
5856   /**
5857    * Build qill for field.
5858    *
5859    * Qill refers to the query detail visible on the UI.
5860    *
5861    * @param string $daoName
5862    * @param string $fieldName
5863    * @param mixed $fieldValue
5864    * @param string $op
5865    * @param array $pseudoExtraParam
5866    * @param int $type
5867    *   Type of the field per CRM_Utils_Type
5868    *
5869    * @return array
5870    */
5871   public static function buildQillForFieldValue(
5872     $daoName,
5873     $fieldName,
5874     $fieldValue,
5875     $op,
5876     $pseudoExtraParam = array(),
5877     $type = CRM_Utils_Type::T_STRING
5878   ) {
5879     $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
5880
5881     // if Operator chosen is NULL/EMPTY then
5882     if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) {
5883       return array(CRM_Utils_Array::value($op, $qillOperators, $op), '');
5884     }
5885
5886     if ($fieldName == 'activity_type_id') {
5887       $pseudoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
5888     }
5889     elseif ($fieldName == 'country_id') {
5890       $pseduoOptions = CRM_Core_PseudoConstant::country();
5891     }
5892     elseif ($fieldName == 'county_id') {
5893       $pseduoOptions = CRM_Core_PseudoConstant::county();
5894     }
5895     elseif ($fieldName == 'world_region') {
5896       $pseduoOptions = CRM_Core_PseudoConstant::worldRegion();
5897     }
5898     elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
5899       $pseudoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
5900     }
5901     elseif ($fieldName == 'contribution_product_id') {
5902       $pseudoOptions = CRM_Contribute_PseudoConstant::products();
5903     }
5904     elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') {
5905       $pseudoOptions = CRM_Core_PseudoConstant::group();
5906     }
5907     elseif ($fieldName == 'country_id') {
5908       $pseudoOptions = CRM_Core_PseudoConstant::country();
5909     }
5910     elseif ($daoName) {
5911       $pseudoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseudoExtraParam);
5912     }
5913
5914     //API usually have fieldValue format as array(operator => array(values)),
5915     //so we need to separate operator out of fieldValue param
5916     if (is_array($fieldValue) && in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
5917       $op = key($fieldValue);
5918       $fieldValue = $fieldValue[$op];
5919     }
5920
5921     if (is_array($fieldValue)) {
5922       $qillString = array();
5923       if (!empty($pseudoOptions)) {
5924         foreach ((array) $fieldValue as $val) {
5925           $qillString[] = CRM_Utils_Array::value($val, $pseudoOptions, $val);
5926         }
5927         $fieldValue = implode(', ', $qillString);
5928       }
5929       else {
5930         if ($type == CRM_Utils_Type::T_DATE) {
5931           foreach ($fieldValue as $index => $value) {
5932             $fieldValue[$index] = CRM_Utils_Date::customFormat($value);
5933           }
5934         }
5935         $separator = ', ';
5936         // @todo - this is a bit specific (one operator).
5937         // However it is covered by a unit test so can be altered later with
5938         // some confidence.
5939         if ($op == 'BETWEEN') {
5940           $separator = ' AND ';
5941         }
5942         $fieldValue = implode($separator, $fieldValue);
5943       }
5944     }
5945     elseif (!empty($pseudoOptions) && array_key_exists($fieldValue, $pseudoOptions)) {
5946       $fieldValue = $pseudoOptions[$fieldValue];
5947     }
5948     elseif ($type === CRM_Utils_Type::T_DATE) {
5949       $fieldValue = CRM_Utils_Date::customFormat($fieldValue);
5950     }
5951
5952     return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue);
5953   }
5954
5955 }