CRM/Contact/BAO/Query.php

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