Query.php 246 KB
Newer Older
totten's avatar
totten committed
1 2 3
<?php
/*
 +--------------------------------------------------------------------+
4
 | Copyright CiviCRM LLC. All rights reserved.                        |
totten's avatar
totten committed
5
 |                                                                    |
6 7 8
 | This work is published under the GNU AGPLv3 license with some      |
 | permitted exceptions and without any warranty. For full license    |
 | and copyright information, see https://civicrm.org/licensing       |
totten's avatar
totten committed
9
 +--------------------------------------------------------------------+
10
 */
totten's avatar
totten committed
11 12 13 14

/**
 *
 * @package CRM
15
 * @copyright CiviCRM LLC https://civicrm.org/licensing
totten's avatar
totten committed
16 17 18
 */

/**
19
 * This is the heart of the search query building mechanism.
totten's avatar
totten committed
20 21 22 23
 */
class CRM_Contact_BAO_Query {

  /**
24
   * The various search modes.
totten's avatar
totten committed
25
   *
26 27 28 29 30 31 32 33
   * As of February 2017, entries not present for 4, 32, 64, 1024.
   *
   * MODE_ALL seems to be out of sync with the available constants;
   * if this is intentionally excluding MODE_MAILING then that may
   * bear documenting?
   *
   * Likewise if there's reason for the missing modes (4, 32, 64 etc).
   *
totten's avatar
totten committed
34 35
   * @var int
   */
36
  const
37
    NO_RETURN_PROPERTIES = 'CRM_Contact_BAO_Query::NO_RETURN_PROPERTIES',
totten's avatar
totten committed
38 39
    MODE_CONTACTS = 1,
    MODE_CONTRIBUTE = 2,
40
    // There is no 4,
totten's avatar
totten committed
41 42
    MODE_MEMBER = 8,
    MODE_EVENT = 16,
43 44
    MODE_CONTACTSRELATED = 32,
    // no 64.
totten's avatar
totten committed
45 46 47
    MODE_GRANT = 128,
    MODE_PLEDGEBANK = 256,
    MODE_PLEDGE = 512,
48
    // There is no 1024,
totten's avatar
totten committed
49 50 51
    MODE_CASE = 2048,
    MODE_ACTIVITY = 4096,
    MODE_CAMPAIGN = 8192,
52 53
    MODE_MAILING = 16384,
    MODE_ALL = 17407;
totten's avatar
totten committed
54

55 56 57 58 59 60 61
  /**
   * Constants for search operators
   */
  const
    SEARCH_OPERATOR_AND = 'AND',
    SEARCH_OPERATOR_OR = 'OR';

totten's avatar
totten committed
62
  /**
63
   * The default set of return properties.
totten's avatar
totten committed
64 65 66
   *
   * @var array
   */
67
  public static $_defaultReturnProperties;
totten's avatar
totten committed
68 69

  /**
70
   * The default set of hier return properties.
totten's avatar
totten committed
71 72 73
   *
   * @var array
   */
74
  public static $_defaultHierReturnProperties;
totten's avatar
totten committed
75 76

  /**
77
   * The set of input params.
totten's avatar
totten committed
78 79 80 81 82 83 84 85 86
   *
   * @var array
   */
  public $_params;

  public $_cfIDs;

  public $_paramLookup;

87
  public $_sort;
88

totten's avatar
totten committed
89
  /**
colemanw's avatar
colemanw committed
90
   * The set of output params
totten's avatar
totten committed
91 92 93 94 95 96
   *
   * @var array
   */
  public $_returnProperties;

  /**
colemanw's avatar
colemanw committed
97
   * The select clause
totten's avatar
totten committed
98 99 100 101 102 103
   *
   * @var array
   */
  public $_select;

  /**
colemanw's avatar
colemanw committed
104
   * The name of the elements that are in the select clause
105
   * used to extract the values.
totten's avatar
totten committed
106 107 108 109 110 111
   *
   * @var array
   */
  public $_element;

  /**
112
   * The tables involved in the query.
totten's avatar
totten committed
113 114 115 116 117 118
   *
   * @var array
   */
  public $_tables;

  /**
119
   * The table involved in the where clause.
totten's avatar
totten committed
120 121 122 123 124 125
   *
   * @var array
   */
  public $_whereTables;

  /**
126
   * Array of WHERE clause components.
totten's avatar
totten committed
127 128 129 130 131 132
   *
   * @var array
   */
  public $_where;

  /**
133
   * The WHERE clause as a string.
totten's avatar
totten committed
134 135 136 137 138 139
   *
   * @var string
   */
  public $_whereClause;

  /**
140
   * Additional WHERE clause for permissions.
totten's avatar
totten committed
141 142 143 144
   *
   * @var string
   */
  public $_permissionWhereClause;
lobo's avatar
lobo committed
145

totten's avatar
totten committed
146
  /**
colemanw's avatar
colemanw committed
147
   * The from string
totten's avatar
totten committed
148 149 150 151 152 153
   *
   * @var string
   */
  public $_fromClause;

  /**
colemanw's avatar
colemanw committed
154
   * Additional permission from clause
totten's avatar
totten committed
155 156 157 158 159 160
   *
   * @var string
   */
  public $_permissionFromClause;

  /**
colemanw's avatar
colemanw committed
161
   * The from clause for the simple select and alphabetical
totten's avatar
totten committed
162 163 164 165 166 167 168
   * select
   *
   * @var string
   */
  public $_simpleFromClause;

  /**
colemanw's avatar
colemanw committed
169
   * The having values
totten's avatar
totten committed
170
   *
171
   * @var array
totten's avatar
totten committed
172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189
   */
  public $_having;

  /**
   * The english language version of the query
   *
   * @var array
   */
  public $_qill;

  /**
   * All the fields that could potentially be involved in
   * this query
   *
   * @var array
   */
  public $_fields;

190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215
  /**
   * Fields hacked for legacy reasons.
   *
   * Generally where a field has a option group defining it's options we add them to
   * the fields array as pseudofields - eg for gender we would add the key 'gender' to fields
   * using CRM_Core_DAO::appendPseudoConstantsToFields($fields);
   *
   * The rendered results would hold an id in the gender_id field and the label in the pseudo 'Gender'
   * field. The heading for the pseudofield would come form the the option group name & for the id field
   * from the xml.
   *
   * These fields are handled in a more legacy way - ie overwriting 'gender_id' with the label on output
   * via the convertToPseudoNames function. Ideally we would convert them but they would then need to be fixed
   * in some other places & there are also some issues around the name (ie. Gender currently has the label in the
   * schema 'Gender' so adding a second 'Gender' field to search builder & export would be confusing and the standard is
   * not fully agreed here.
   *
   * @var array
   */
  protected $legacyHackedFields = [
    'gender_id' => 'gender',
    'prefix_id' => 'individual_prefix',
    'suffix_id' => 'individual_suffix',
    'communication_style_id' => 'communication_style',
  ];

totten's avatar
totten committed
216
  /**
217
   * The cache to translate the option values into labels.
totten's avatar
totten committed
218 219 220 221 222 223
   *
   * @var array
   */
  public $_options;

  /**
224
   * Are we in search mode.
totten's avatar
totten committed
225
   *
226
   * @var bool
totten's avatar
totten committed
227 228 229 230
   */
  public $_search = TRUE;

  /**
231
   * Should we skip permission checking.
totten's avatar
totten committed
232
   *
233
   * @var bool
totten's avatar
totten committed
234 235 236 237
   */
  public $_skipPermission = FALSE;

  /**
238
   * Should we skip adding of delete clause.
totten's avatar
totten committed
239
   *
240
   * @var bool
totten's avatar
totten committed
241 242 243 244
   */
  public $_skipDeleteClause = FALSE;

  /**
colemanw's avatar
colemanw committed
245
   * Are we in strict mode (use equality over LIKE)
totten's avatar
totten committed
246
   *
247
   * @var bool
totten's avatar
totten committed
248 249 250 251
   */
  public $_strict = FALSE;

  /**
252
   * What operator to use to group the clauses.
totten's avatar
totten committed
253 254 255 256 257 258 259 260
   *
   * @var string
   */
  public $_operator = 'AND';

  public $_mode = 1;

  /**
261
   * Should we only search on primary location.
totten's avatar
totten committed
262
   *
263
   * @var bool
totten's avatar
totten committed
264 265 266 267
   */
  public $_primaryLocation = TRUE;

  /**
268
   * Are contact ids part of the query.
totten's avatar
totten committed
269
   *
270
   * @var bool
totten's avatar
totten committed
271 272 273 274
   */
  public $_includeContactIds = FALSE;

  /**
275
   * Should we use the smart group cache.
totten's avatar
totten committed
276
   *
277
   * @var bool
totten's avatar
totten committed
278 279 280 281
   */
  public $_smartGroupCache = TRUE;

  /**
282
   * Should we display contacts with a specific relationship type.
totten's avatar
totten committed
283 284 285
   *
   * @var string
   */
286
  public $_displayRelationshipType;
totten's avatar
totten committed
287 288

  /**
289
   * Reference to the query object for custom values.
totten's avatar
totten committed
290 291 292 293 294 295
   *
   * @var Object
   */
  public $_customQuery;

  /**
colemanw's avatar
colemanw committed
296
   * Should we enable the distinct clause, used if we are including
totten's avatar
totten committed
297 298
   * more than one group
   *
299
   * @var bool
totten's avatar
totten committed
300 301 302 303 304
   */
  public $_useDistinct = FALSE;

  /**
   * Should we just display one contact record
305
   * @var bool
totten's avatar
totten committed
306 307 308 309
   */
  public $_useGroupBy = FALSE;

  /**
colemanw's avatar
colemanw committed
310
   * The relationship type direction
totten's avatar
totten committed
311 312 313
   *
   * @var array
   */
314
  public static $_relType;
totten's avatar
totten committed
315 316

  /**
colemanw's avatar
colemanw committed
317
   * The activity role
totten's avatar
totten committed
318 319 320
   *
   * @var array
   */
321
  public static $_activityRole;
totten's avatar
totten committed
322 323 324 325 326 327 328

  /**
   * Consider the component activity type
   * during activity search.
   *
   * @var array
   */
329
  public static $_considerCompActivities;
totten's avatar
totten committed
330 331 332 333 334 335 336

  /**
   * Consider with contact activities only,
   * during activity search.
   *
   * @var array
   */
337
  public static $_withContactActivitiesOnly;
totten's avatar
totten committed
338 339

  /**
colemanw's avatar
colemanw committed
340
   * Use distinct component clause for component searches
totten's avatar
totten committed
341 342 343 344 345
   *
   * @var string
   */
  public $_distinctComponentClause;

346 347
  public $_rowCountClause;

totten's avatar
totten committed
348
  /**
colemanw's avatar
colemanw committed
349
   * Use groupBy component clause for component searches
totten's avatar
totten committed
350 351 352 353 354 355 356 357 358 359
   *
   * @var string
   */
  public $_groupByComponentClause;

  /**
   * Track open panes, useful in advance search
   *
   * @var array
   */
360
  public static $_openedPanes = [];
totten's avatar
totten committed
361

362 363 364 365
  /**
   * For search builder - which custom fields are location-dependent
   * @var array
   */
366
  public $_locationSpecificCustomFields = [];
367

totten's avatar
totten committed
368 369 370 371 372
  /**
   * The tables which have a dependency on location and/or address
   *
   * @var array
   */
373
  public static $_dependencies = [
totten's avatar
totten committed
374 375 376 377 378
    'civicrm_state_province' => 1,
    'civicrm_country' => 1,
    'civicrm_county' => 1,
    'civicrm_address' => 1,
    'civicrm_location_type' => 1,
379
  ];
totten's avatar
totten committed
380 381

  /**
382
   * List of location specific fields.
383
   * @var array
totten's avatar
totten committed
384
   */
385
  public static $_locationSpecificFields = [
totten's avatar
totten committed
386 387 388 389 390 391
    'street_address',
    'street_number',
    'street_name',
    'street_unit',
    'supplemental_address_1',
    'supplemental_address_2',
392
    'supplemental_address_3',
totten's avatar
totten committed
393 394 395 396 397 398 399 400 401 402 403 404
    'city',
    'postal_code',
    'postal_code_suffix',
    'geo_code_1',
    'geo_code_2',
    'state_province',
    'country',
    'county',
    'phone',
    'email',
    'im',
    'address_name',
405
    'master_id',
406
  ];
totten's avatar
totten committed
407 408

  /**
Eileen McNaughton's avatar
Eileen McNaughton committed
409
   * Remember if we handle either end of a number or date range
totten's avatar
totten committed
410
   * so we can skip the other
411
   * @var array
totten's avatar
totten committed
412
   */
413
  protected $_rangeCache = [];
414
  /**
415 416 417
   * Set to true when $this->relationship is run to avoid adding twice.
   *
   * @var bool
418
   */
419
  protected $_relationshipValuesAdded = FALSE;
420

421
  /**
422 423 424
   * Set to the name of the temp table if one has been created.
   *
   * @var string
425
   */
426
  public static $_relationshipTempTable;
427

428
  public $_pseudoConstantsSelect = [];
monishdeb's avatar
monishdeb committed
429

430
  public $_groupUniqueKey;
431
  public $_groupKeys = [];
432

totten's avatar
totten committed
433
  /**
434
   * Class constructor which also does all the work.
totten's avatar
totten committed
435
   *
Eileen McNaughton's avatar
Eileen McNaughton committed
436 437 438
   * @param array $params
   * @param array $returnProperties
   * @param array $fields
439 440
   * @param bool $includeContactIds
   * @param bool $strict
Eileen McNaughton's avatar
Eileen McNaughton committed
441
   * @param bool|int $mode - mode the search is operating on
totten's avatar
totten committed
442
   *
Eileen McNaughton's avatar
Eileen McNaughton committed
443 444 445 446 447
   * @param bool $skipPermission
   * @param bool $searchDescendentGroups
   * @param bool $smartGroupCache
   * @param null $displayRelationshipType
   * @param string $operator
448
   * @param string $apiEntity
eileen's avatar
eileen committed
449
   * @param bool|null $primaryLocationOnly
eileen's avatar
eileen committed
450 451
   *
   * @throws \CRM_Core_Exception
totten's avatar
totten committed
452
   */
totten's avatar
totten committed
453
  public function __construct(
totten's avatar
totten committed
454 455 456 457
    $params = NULL, $returnProperties = NULL, $fields = NULL,
    $includeContactIds = FALSE, $strict = FALSE, $mode = 1,
    $skipPermission = FALSE, $searchDescendentGroups = TRUE,
    $smartGroupCache = TRUE, $displayRelationshipType = NULL,
458
    $operator = 'AND',
459 460
    $apiEntity = NULL,
    $primaryLocationOnly = NULL
totten's avatar
totten committed
461
  ) {
462 463 464 465
    if ($primaryLocationOnly === NULL) {
      $primaryLocationOnly = Civi::settings()->get('searchPrimaryDetailsOnly');
    }
    $this->_primaryLocation = $primaryLocationOnly;
totten's avatar
totten committed
466 467
    $this->_params = &$params;
    if ($this->_params == NULL) {
468
      $this->_params = [];
totten's avatar
totten committed
469 470
    }

471
    if ($returnProperties === self::NO_RETURN_PROPERTIES) {
472
      $this->_returnProperties = [];
473 474
    }
    elseif (empty($returnProperties)) {
totten's avatar
totten committed
475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494
      $this->_returnProperties = self::defaultReturnProperties($mode);
    }
    else {
      $this->_returnProperties = &$returnProperties;
    }

    $this->_includeContactIds = $includeContactIds;
    $this->_strict = $strict;
    $this->_mode = $mode;
    $this->_skipPermission = $skipPermission;
    $this->_smartGroupCache = $smartGroupCache;
    $this->_displayRelationshipType = $displayRelationshipType;
    $this->setOperator($operator);

    if ($fields) {
      $this->_fields = &$fields;
      $this->_search = FALSE;
      $this->_skipPermission = TRUE;
    }
    else {
495
      $this->_fields = CRM_Contact_BAO_Contact::exportableFields('All', FALSE, TRUE, TRUE, FALSE, !$skipPermission);
496 497 498 499
      //  The legacy hacked fields will output as a string rather than their underlying type.
      foreach (array_keys($this->legacyHackedFields) as $fieldName) {
        $this->_fields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
      }
500 501
      $relationMetadata = CRM_Contact_BAO_Relationship::fields();
      $relationFields = array_intersect_key($relationMetadata, array_fill_keys(['relationship_start_date', 'relationship_end_date'], 1));
502 503 504 505 506 507 508 509 510 511
      // No good option other than hard-coding metadata for this 'special' field in.
      $relationFields['relation_active_period_date'] = [
        'name' => 'relation_active_period_date',
        'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
        'title' => ts('Active Period'),
        'table_name' => 'civicrm_relationship',
        'where' => 'civicrm_relationship.start_date',
        'where_end' => 'civicrm_relationship.end_date',
        'html' => ['type' => 'SelectDate', 'formatType' => 'activityDateTime'],
      ];
512
      $this->_fields = array_merge($relationFields, $this->_fields);
totten's avatar
totten committed
513

514
      $fields = CRM_Core_Component::getQueryFields(!$this->_skipPermission);
totten's avatar
totten committed
515 516 517 518
      unset($fields['note']);
      $this->_fields = array_merge($this->_fields, $fields);

      // add activity fields
519 520 521
      $this->_fields = array_merge($this->_fields, CRM_Activity_BAO_Activity::exportableFields());
      // Add hack as no unique name is defined for the field but the search form is in denial.
      $this->_fields['activity_priority_id'] = $this->_fields['priority_id'];
522

523 524
      // add any fields provided by hook implementers
      $extFields = CRM_Contact_BAO_Query_Hook::singleton()->getFields();
525
      $this->_fields = array_merge($this->_fields, $extFields);
totten's avatar
totten committed
526 527 528
    }

    // basically do all the work once, and then reuse it
529
    $this->initialize($apiEntity);
totten's avatar
totten committed
530 531 532
  }

  /**
533
   * Function which actually does all the work for the constructor.
eileen's avatar
eileen committed
534 535 536 537
   *
   * @param string $apiEntity
   *   The api entity being called.
   *   This sort-of duplicates $mode in a confusing way. Probably not by design.
eileen's avatar
eileen committed
538 539
   *
   * @throws \CRM_Core_Exception
totten's avatar
totten committed
540
   */
541
  public function initialize($apiEntity = NULL) {
542 543 544 545 546 547 548 549 550 551
    $this->_select = [];
    $this->_element = [];
    $this->_tables = [];
    $this->_whereTables = [];
    $this->_where = [];
    $this->_qill = [];
    $this->_options = [];
    $this->_cfIDs = [];
    $this->_paramLookup = [];
    $this->_having = [];
totten's avatar
totten committed
552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569

    $this->_customQuery = NULL;

    // reset cached static variables - CRM-5803
    self::$_activityRole = NULL;
    self::$_considerCompActivities = NULL;
    self::$_withContactActivitiesOnly = NULL;

    $this->_select['contact_id'] = 'contact_a.id as contact_id';
    $this->_element['contact_id'] = 1;
    $this->_tables['civicrm_contact'] = 1;

    if (!empty($this->_params)) {
      $this->buildParamsLookup();
    }

    $this->_whereTables = $this->_tables;

570
    $this->selectClause($apiEntity);
571 572 573 574 575 576 577 578 579 580
    if (!empty($this->_cfIDs)) {
      // @todo This function is the select function but instead of running 'select' it
      // is running the whole query.
      $this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE, $this->_locationSpecificCustomFields);
      $this->_customQuery->query();
      $this->_select = array_merge($this->_select, $this->_customQuery->_select);
      $this->_element = array_merge($this->_element, $this->_customQuery->_element);
      $this->_tables = array_merge($this->_tables, $this->_customQuery->_tables);
      $this->_options = $this->_customQuery->_options;
    }
581 582
    $isForcePrimaryOnly = !empty($apiEntity);
    $this->_whereClause = $this->whereClause($isForcePrimaryOnly);
583 584 585 586 587 588
    if (array_key_exists('civicrm_contribution', $this->_whereTables)) {
      $component = 'contribution';
    }
    if (array_key_exists('civicrm_membership', $this->_whereTables)) {
      $component = 'membership';
    }
589 590
    if (isset($component) && !$this->_skipPermission) {
      // Unit test coverage in api_v3_FinancialTypeACLTest::testGetACLContribution.
Edselopez's avatar
Edselopez committed
591 592
      CRM_Financial_BAO_FinancialType::buildPermissionedClause($this->_whereClause, $component);
    }
totten's avatar
totten committed
593

594
    $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode, $apiEntity);
totten's avatar
totten committed
595 596 597 598 599
    $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);

    $this->openedSearchPanes(TRUE);
  }

eileenmcnaugton's avatar
eileenmcnaugton committed
600
  /**
601 602 603 604 605 606 607 608
   * Function for same purpose as convertFormValues.
   *
   * Like convert form values this function exists to pre-Process parameters from the form.
   *
   * It is unclear why they are different functions & likely relates to advances search
   * versus search builder.
   *
   * The direction we are going is having the form convert values to a standardised format &
eileen's avatar
eileen committed
609
   * moving away from weird & wonderful where clause switches.
610
   *
eileenmcnaugton's avatar
eileenmcnaugton committed
611 612 613 614 615 616
   * Fix and handle contact deletion nicely.
   *
   * this code is primarily for search builder use case where different clauses can specify if they want deleted.
   *
   * CRM-11971
   */
617
  public function buildParamsLookup() {
totten's avatar
totten committed
618
    $trashParamExists = FALSE;
619
    $paramByGroup = [];
620 621
    foreach ($this->_params as $k => $param) {
      if (!empty($param[0]) && $param[0] == 'contact_is_deleted') {
totten's avatar
totten committed
622 623
        $trashParamExists = TRUE;
      }
yashodha's avatar
yashodha committed
624 625 626
      if (!empty($param[3])) {
        $paramByGroup[$param[3]][$k] = $param;
      }
totten's avatar
totten committed
627 628
    }

629
    if ($trashParamExists) {
totten's avatar
totten committed
630 631 632
      $this->_skipDeleteClause = TRUE;

      //cycle through group sets and explicitly add trash param if not set
633
      foreach ($paramByGroup as $setID => $set) {
totten's avatar
totten committed
634
        if (
635 636
          !in_array(['contact_is_deleted', '=', '1', $setID, '0'], $this->_params) &&
          !in_array(['contact_is_deleted', '=', '0', $setID, '0'], $this->_params)
637
        ) {
638
          $this->_params[] = [
totten's avatar
totten committed
639 640 641 642 643
            'contact_is_deleted',
            '=',
            '0',
            $setID,
            '0',
644
          ];
totten's avatar
totten committed
645 646 647 648 649
        }
      }
    }

    foreach ($this->_params as $value) {
650
      if (empty($value[0])) {
totten's avatar
totten committed
651 652
        continue;
      }
653
      $cfID = CRM_Core_BAO_CustomField::getKeyID(str_replace(['_relative', '_low', '_high', '_to', '_high'], '', $value[0]));
totten's avatar
totten committed
654 655
      if ($cfID) {
        if (!array_key_exists($cfID, $this->_cfIDs)) {
656
          $this->_cfIDs[$cfID] = [];
totten's avatar
totten committed
657
        }
658 659 660 661 662 663 664
        // Set wildcard value based on "and/or" selection
        foreach ($this->_params as $key => $param) {
          if ($param[0] == $value[0] . '_operator') {
            $value[4] = $param[2] == 'or';
            break;
          }
        }
totten's avatar
totten committed
665 666 667 668
        $this->_cfIDs[$cfID][] = $value;
      }

      if (!array_key_exists($value[0], $this->_paramLookup)) {
669
        $this->_paramLookup[$value[0]] = [];
totten's avatar
totten committed
670
      }
671
      if ($value[0] !== 'group') {
eileen's avatar
eileen committed
672
        // Just trying to unravel how group interacts here! This whole function is weird.
673 674
        $this->_paramLookup[$value[0]][] = $value;
      }
totten's avatar
totten committed
675 676 677 678
    }
  }

  /**
eileenmcnaugton's avatar
eileenmcnaugton committed
679
   * Some composite fields do not appear in the fields array hack to make them part of the query.
eileen's avatar
eileen committed
680 681 682 683
   *
   * @param $apiEntity
   *   The api entity being called.
   *   This sort-of duplicates $mode in a confusing way. Probably not by design.
totten's avatar
totten committed
684
   */
685
  public function addSpecialFields($apiEntity) {
686
    static $special = ['contact_type', 'contact_sub_type', 'sort_name', 'display_name'];
687 688 689 690
    // if get called via Contact.get API having address_id as return parameter
    if ($apiEntity == 'Contact') {
      $special[] = 'address_id';
    }
totten's avatar
totten committed
691
    foreach ($special as $name) {
692
      if (!empty($this->_returnProperties[$name])) {
693 694 695 696 697 698 699 700 701
        if ($name == 'address_id') {
          $this->_tables['civicrm_address'] = 1;
          $this->_select['address_id'] = 'civicrm_address.id as address_id';
          $this->_element['address_id'] = 1;
        }
        else {
          $this->_select[$name] = "contact_a.{$name} as $name";
          $this->_element[$name] = 1;
        }
totten's avatar
totten committed
702 703 704 705 706 707 708 709 710 711
      }
    }
  }

  /**
   * Given a list of conditions in params and a list of desired
   * return Properties generate the required select and from
   * clauses. Note that since the where clause introduces new
   * tables, the initial attempt also retrieves all variables used
   * in the params list
eileen's avatar
eileen committed
712 713 714 715
   *
   * @param string $apiEntity
   *   The api entity being called.
   *   This sort-of duplicates $mode in a confusing way. Probably not by design.
totten's avatar
totten committed
716
   */
717
  public function selectClause($apiEntity = NULL) {
totten's avatar
totten committed
718

719 720 721
    // @todo Tidy up this. This arises because 1) we are ignoring the $mode & adding a new
    // param ($apiEntity) instead - presumably an oversight & 2 because
    // contact is not implemented as a component.
722
    $this->addSpecialFields($apiEntity);
totten's avatar
totten committed
723 724 725 726 727

    foreach ($this->_fields as $name => $field) {
      // skip component fields
      // there are done by the alter query below
      // and need not be done on every field
728 729
      // @todo remove these & handle using metadata - only obscure fields
      // that are hack-added should need to be excluded from the main loop.
lobo's avatar
lobo committed
730
      if (
731 732 733
        (substr($name, 0, 12) === 'participant_') ||
        (substr($name, 0, 7) === 'pledge_') ||
        (substr($name, 0, 5) === 'case_')
totten's avatar
totten committed
734 735 736 737 738
      ) {
        continue;
      }

      // redirect to activity select clause
739
      if (
740 741
        (substr($name, 0, 9) === 'activity_') ||
        ($name === 'parent_id')
742
      ) {
totten's avatar
totten committed
743 744 745 746 747
        CRM_Activity_BAO_Query::select($this);
      }

      // if this is a hierarchical name, we ignore it
      $names = explode('-', $name);
748
      if (count($names) > 1 && isset($names[1]) && is_numeric($names[1])) {
totten's avatar
totten committed
749 750 751
        continue;
      }

752
      // make an exception for special cases, to add the field in select clause
753
      $makeException = FALSE;
754 755

      //special handling for groups/tags
756
      if (in_array($name, ['groups', 'tags', 'notes'])
757 758
        && isset($this->_returnProperties[substr($name, 0, -1)])
      ) {
eileen's avatar
eileen committed
759 760 761
        // @todo instead of setting make exception to get us into
        // an if clause that has handling for these fields buried with in it
        // move the handling to here.
762 763 764
        $makeException = TRUE;
      }

765 766
      // since note has 3 different options we need special handling
      // note / note_subject / note_body
767
      if ($name === 'notes') {
768
        foreach (['note', 'note_subject', 'note_body'] as $noteField) {
769 770 771 772 773 774
          if (isset($this->_returnProperties[$noteField])) {
            $makeException = TRUE;
            break;
          }
        }
      }
775

totten's avatar
totten committed
776
      $cfID = CRM_Core_BAO_CustomField::getKeyID($name);
777 778 779
      if (
        !empty($this->_paramLookup[$name])
        || !empty($this->_returnProperties[$name])
780
        || $this->pseudoConstantNameIsInReturnProperties($field, $name)
781
        || $makeException
totten's avatar
totten committed
782 783 784 785
      ) {
        if ($cfID) {
          // add to cfIDs array if not present
          if (!array_key_exists($cfID, $this->_cfIDs)) {
786
            $this->_cfIDs[$cfID] = [];
totten's avatar
totten committed
787 788 789 790 791
          }
        }
        elseif (isset($field['where'])) {
          list($tableName, $fieldName) = explode('.', $field['where'], 2);
          if (isset($tableName)) {
792
            if (!empty(self::$_dependencies[$tableName])) {
totten's avatar
totten committed
793 794 795 796 797
              $this->_tables['civicrm_address'] = 1;
              $this->_select['address_id'] = 'civicrm_address.id as address_id';
              $this->_element['address_id'] = 1;
            }

798 799
            if ($tableName === 'im_provider' || $tableName === 'email_greeting' ||
              $tableName === 'postal_greeting' || $tableName === 'addressee'
totten's avatar
totten committed
800
            ) {
801
              if ($tableName === 'im_provider') {
802 803 804 805
                CRM_Core_OptionValue::select($this);
              }

              if (in_array($tableName,
806
                ['email_greeting', 'postal_greeting', 'addressee'])) {
807 808
                $this->_element["{$name}_id"] = 1;
                $this->_select["{$name}_id"] = "contact_a.{$name}_id as {$name}_id";
809
                $this->_pseudoConstantsSelect[$name] = ['pseudoField' => $tableName, 'idCol' => "{$name}_id"];
810 811 812
                $this->_pseudoConstantsSelect[$name]['select'] = "{$name}.{$fieldName} as $name";
                $this->_pseudoConstantsSelect[$name]['element'] = $name;

813
                if ($tableName === 'email_greeting') {
814
                  // @todo bad join.
totten's avatar
totten committed
815 816
                  $this->_pseudoConstantsSelect[$name]['join']
                    = " LEFT JOIN civicrm_option_group option_group_email_greeting ON (option_group_email_greeting.name = 'email_greeting')";
817 818 819
                  $this->_pseudoConstantsSelect[$name]['join'] .=
                    " 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 ) ";
                }
820
                elseif ($tableName === 'postal_greeting') {
821
                  // @todo bad join.
totten's avatar
totten committed
822 823
                  $this->_pseudoConstantsSelect[$name]['join']
                    = " LEFT JOIN civicrm_option_group option_group_postal_greeting ON (option_group_postal_greeting.name = 'postal_greeting')";
824 825 826 827
                  $this->_pseudoConstantsSelect[$name]['join'] .=
                    " 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 ) ";
                }
                elseif ($tableName == 'addressee') {
828
                  // @todo bad join.
totten's avatar
totten committed
829 830
                  $this->_pseudoConstantsSelect[$name]['join']
                    = " LEFT JOIN civicrm_option_group option_group_addressee ON (option_group_addressee.name = 'addressee')";
831 832 833 834 835
                  $this->_pseudoConstantsSelect[$name]['join'] .=
                    " LEFT JOIN civicrm_option_value addressee ON (contact_a.addressee_id = addressee.value AND option_group_addressee.id = addressee.option_group_id ) ";
                }
                $this->_pseudoConstantsSelect[$name]['table'] = $tableName;

totten's avatar
totten committed
836 837 838 839 840 841 842 843 844 845 846
                //get display
                $greetField = "{$name}_display";
                $this->_select[$greetField] = "contact_a.{$greetField} as {$greetField}";
                $this->_element[$greetField] = 1;
                //get custom
                $greetField = "{$name}_custom";
                $this->_select[$greetField] = "contact_a.{$greetField} as {$greetField}";
                $this->_element[$greetField] = 1;
              }
            }
            else {
847
              if (!in_array($tableName, ['civicrm_state_province', 'civicrm_country', 'civicrm_county'])) {
848 849
                $this->_tables[$tableName] = 1;
              }
totten's avatar
totten committed
850 851 852

              // also get the id of the tableName
              $tName = substr($tableName, 8);
853
              if (in_array($tName, ['country', 'state_province', 'county'])) {
854
                if ($tName == 'state_province') {
855
                  $this->_pseudoConstantsSelect['state_province_name'] = [
totten's avatar
totten committed
856 857 858 859 860
                    'pseudoField' => "{$tName}",
                    'idCol' => "{$tName}_id",
                    'bao' => 'CRM_Core_BAO_Address',
                    'table' => "civicrm_{$tName}",
                    'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ",
861
                  ];
totten's avatar
totten committed
862

863
                  $this->_pseudoConstantsSelect[$tName] = [
totten's avatar
totten committed
864 865 866 867
                    'pseudoField' => 'state_province_abbreviation',
                    'idCol' => "{$tName}_id",
                    'table' => "civicrm_{$tName}",
                    'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ",
868
                  ];
869
                }
870
                else {
871
                  $this->_pseudoConstantsSelect[$name] = [
totten's avatar
totten committed
872 873 874 875 876
                    'pseudoField' => "{$tName}_id",
                    'idCol' => "{$tName}_id",
                    'bao' => 'CRM_Core_BAO_Address',
                    'table' => "civicrm_{$tName}",
                    'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id ",
877
                  ];
878
                }
totten's avatar
totten committed
879

880 881 882 883
                $this->_select["{$tName}_id"] = "civicrm_address.{$tName}_id as {$tName}_id";
                $this->_element["{$tName}_id"] = 1;
              }
              elseif ($tName != 'contact') {
totten's avatar
totten committed
884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900
                $this->_select["{$tName}_id"] = "{$tableName}.id as {$tName}_id";
                $this->_element["{$tName}_id"] = 1;
              }

              //special case for phone
              if ($name == 'phone') {
                $this->_select['phone_type_id'] = "civicrm_phone.phone_type_id as phone_type_id";
                $this->_element['phone_type_id'] = 1;
              }

              // if IM then select provider_id also
              // to get "IM Service Provider" in a file to be exported, CRM-3140
              if ($name == 'im') {
                $this->_select['provider_id'] = "civicrm_im.provider_id as provider_id";
                $this->_element['provider_id'] = 1;
              }

901
              if ($tName == 'contact' && $fieldName == 'organization_name') {
totten's avatar
totten committed
902
                // special case, when current employer is set for Individual contact
903 904 905 906
                $this->_select[$name] = "IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name";
              }
              elseif ($tName == 'contact' && $fieldName === 'id') {
                // Handled elsewhere, explicitly ignore. Possibly for all tables...
totten's avatar
totten committed
907
              }
908
              elseif (in_array($tName, ['country', 'county'])) {
909 910 911 912 913 914
                $this->_pseudoConstantsSelect[$name]['select'] = "{$field['where']} as `$name`";
                $this->_pseudoConstantsSelect[$name]['element'] = $name;
              }
              elseif ($tName == 'state_province') {
                $this->_pseudoConstantsSelect[$tName]['select'] = "{$field['where']} as `$name`";
                $this->_pseudoConstantsSelect[$tName]['element'] = $name;
915
              }
jitendra's avatar
jitendra committed
916
              elseif (strpos($name, 'contribution_soft_credit') !== FALSE) {
jitendra's avatar
jitendra committed
917 918 919 920
                if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled($this->_params)) {
                  $this->_select[$name] = "{$field['where']} as `$name`";
                }
              }
921 922 923
              elseif ($this->pseudoConstantNameIsInReturnProperties($field, $name)) {
                $this->addPseudoconstantFieldToSelect($name);
              }
totten's avatar
totten committed
924
              else {
925
                $this->_select[$name] = str_replace('civicrm_contact.', 'contact_a.', "{$field['where']} as `$name`");
totten's avatar
totten committed
926
              }
927
              if (!in_array($tName, ['state_province', 'country', 'county'])) {
928 929
                $this->_element[$name] = 1;
              }
totten's avatar
totten committed
930 931 932 933
            }
          }
        }
        elseif ($name === 'tags') {
eileen's avatar
eileen committed
934
          //@todo move this handling outside the big IF & ditch $makeException
totten's avatar
totten committed
935 936 937 938 939 940 941
          $this->_useGroupBy = TRUE;
          $this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_tag.name)) as tags";
          $this->_element[$name] = 1;
          $this->_tables['civicrm_tag'] = 1;
          $this->_tables['civicrm_entity_tag'] = 1;
        }
        elseif ($name === 'groups') {
eileen's avatar
eileen committed
942
          //@todo move this handling outside the big IF & ditch $makeException
totten's avatar
totten committed
943
          $this->_useGroupBy = TRUE;
944 945 946 947 948 949
          // Duplicates will be created here but better to sort them out in php land.
          $this->_select[$name] = "
            CONCAT_WS(',',
            GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
            GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
          )
950
          as `groups`";
totten's avatar
totten committed
951
          $this->_element[$name] = 1;
952 953
          $this->_tables['civicrm_group_contact'] = 1;
          $this->_tables['civicrm_group_contact_cache'] = 1;
954
          $this->_pseudoConstantsSelect["{$name}"] = [
955 956
            'pseudoField' => "groups",
            'idCol' => "groups",
957
          ];
totten's avatar
totten committed
958 959
        }
        elseif ($name === 'notes') {
eileen's avatar
eileen committed
960
          //@todo move this handling outside the big IF & ditch $makeException
961 962 963 964 965 966
          // if note field is subject then return subject else body of the note
          $noteColumn = 'note';
          if (isset($noteField) && $noteField == 'note_subject') {
            $noteColumn = 'subject';
          }

totten's avatar
totten committed
967
          $this->_useGroupBy = TRUE;
968
          $this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_note.$noteColumn)) as notes";
totten's avatar
totten committed
969 970 971 972 973 974 975 976 977
          $this->_element[$name] = 1;
          $this->_tables['civicrm_note'] = 1;
        }
        elseif ($name === 'current_employer') {
          $this->_select[$name] = "IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer";
          $this->_element[$name] = 1;
        }
      }

978
      if ($cfID && !empty($field['is_search_range'])) {
totten's avatar
totten committed
979
        // this is a custom field with range search enabled, so we better check for two/from values
980
        if (!empty($this->_paramLookup[$name . '_from'])) {
totten's avatar
totten committed
981
          if (!array_key_exists($cfID, $this->_cfIDs)) {
982
            $this->_cfIDs[$cfID] = [];
totten's avatar
totten committed
983 984 985 986 987 988 989 990 991 992 993
          }
          foreach ($this->_paramLookup[$name . '_from'] as $pID => $p) {
            // search in the cdID array for the same grouping
            $fnd = FALSE;
            foreach ($this->_cfIDs[$cfID] as $cID => $c) {
              if ($c[3] == $p[3]) {
                $this->_cfIDs[$cfID][$cID][2]['from'] = $p[2];
                $fnd = TRUE;
              }
            }
            if (!$fnd) {
994
              $p[2] = ['from' => $p[2]];
totten's avatar
totten committed
995 996 997 998
              $this->_cfIDs[$cfID][] = $p;
            }
          }
        }
999
        if (!empty($this->_paramLookup[$name . '_to'])) {
totten's avatar
totten committed
1000
          if (!array_key_exists($cfID, $this->_cfIDs)) {
1001
            $this->_cfIDs[$cfID] = [];
totten's avatar
totten committed
1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012
          }
          foreach ($this->_paramLookup[$name . '_to'] as $pID => $p) {
            // search in the cdID array for the same grouping
            $fnd = FALSE;
            foreach ($this->_cfIDs[$cfID] as $cID => $c) {
              if ($c[4] == $p[4]) {
                $this->_cfIDs[$cfID][$cID][2]['to'] = $p[2];
                $fnd = TRUE;
              }
            }
            if (!$fnd) {
1013
              $p[2] = ['to' => $p[2]];
totten's avatar
totten committed
1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029
              $this->_cfIDs[$cfID][] = $p;
            }
          }
        }
      }
    }

    // add location as hierarchical elements
    $this->addHierarchicalElements();

    // add multiple field like website
    $this->addMultipleElements();

    //fix for CRM-951
    CRM_Core_Component::alterQuery($this, 'select');

1030
    CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'select');
totten's avatar
totten committed
1031 1032 1033
  }

  /**
eileenmcnaugton's avatar
eileenmcnaugton committed
1034
   * If the return Properties are set in a hierarchy, traverse the hierarchy to get the return values.
totten's avatar
totten committed
1035
   */
1036
  public function addHierarchicalElements() {
1037
    if (empty($this->_returnProperties['location'])) {
totten's avatar
totten committed
1038 1039 1040 1041 1042 1043
      return;
    }
    if (!is_array($this->_returnProperties['location'])) {
      return;
    }

1044
    $locationTypes = CRM_Core_DAO_Address::buildOptions('location_type_id', 'validate');
1045
    $processed = [];
totten's avatar
totten committed
1046 1047 1048
    $index = 0;

    $addressCustomFields = CRM_Core_BAO_CustomField::getFieldsForImport('Address');
1049
    $addressCustomFieldIds = [];
totten's avatar
totten committed
1050 1051 1052

    foreach ($this->_returnProperties['location'] as $name => $elements) {
      $lCond = self::getPrimaryCondition($name);
1053
      $locationTypeId = is_numeric($name) ? NULL : array_search($name, $locationTypes);
totten's avatar
totten committed
1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074

      if (!$lCond) {
        if ($locationTypeId === FALSE) {
          continue;
        }
        $lCond = "location_type_id = $locationTypeId";
        $this->_useDistinct = TRUE;

        //commented for CRM-3256
        $this->_useGroupBy = TRUE;
      }

      $name = str_replace(' ', '_', $name);
      $tName = "$name-location_type";
      $ltName = "`$name-location_type`";
      $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
      $this->_select["{$tName}"] = "`$tName`.name as `{$tName}`";
      $this->_element["{$tName}_id"] = 1;
      $this->_element["{$tName}"] = 1;

      $locationTypeName = $tName;
1075
      $locationTypeJoin = [];
totten's avatar
totten committed
1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090

      $addWhereCount = 0;
      foreach ($elements as $elementFullName => $dontCare) {
        $index++;
        $elementName = $elementCmpName = $elementFullName;

        if (substr($elementCmpName, 0, 5) == 'phone') {
          $elementCmpName = 'phone';
        }

        if (in_array($elementCmpName, array_keys($addressCustomFields))) {
          if ($cfID = CRM_Core_BAO_CustomField::getKeyID($elementCmpName)) {
            $addressCustomFieldIds[$cfID][$name] = 1;
          }
        }
1091 1092
        // add address table - doesn't matter if we do it mutliple times - it's the same data
        // @todo ditch the double processing of addressJoin
totten's avatar
totten committed
1093
        if ((in_array($elementCmpName, self::$_locationSpecificFields) || !empty($addressCustomFieldIds))
1094
          && !in_array($elementCmpName, ['email', 'phone', 'im', 'openid'])
totten's avatar
totten committed
1095
        ) {
1096
          list($aName, $addressJoin) = $this->addAddressTable($name, $lCond);
totten's avatar
totten committed
1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108
          $locationTypeJoin[$tName] = " ( $aName.location_type_id = $ltName.id ) ";
          $processed[$aName] = 1;
        }

        $cond = $elementType = '';
        if (strpos($elementName, '-') !== FALSE) {
          // this is either phone, email or IM
          list($elementName, $elementType) = explode('-', $elementName);

          if (($elementName != 'phone') && ($elementName != 'im')) {
            $cond = self::getPrimaryCondition($elementType);
          }
pratik.joshi's avatar
pratik.joshi committed
1109 1110 1111
          // CRM-13011 : If location type is primary, do not restrict search to the phone
          // type id - we want the primary phone, regardless of what type it is.
          // Otherwise, restrict to the specified phone type for the given field.
1112
          if ((!$cond) && ($elementName == 'phone')) {
totten's avatar
totten committed
1113 1114 1115 1116 1117 1118 1119 1120 1121
            $cond = "phone_type_id = '$elementType'";
          }
          elseif ((!$cond) && ($elementName == 'im')) {
            // IM service provider id, CRM-3140
            $cond = "provider_id = '$elementType'";
          }
          $elementType = '-' . $elementType;
        }

1122
        $field = $this->_fields[$elementName] ?? NULL;
totten's avatar
totten committed
1123 1124 1125 1126 1127 1128 1129 1130

        // hack for profile, add location id
        if (!$field) {
          if ($elementType &&
            // fix for CRM-882( to handle phone types )
            !is_numeric($elementType)
          ) {
            if (is_numeric($name)) {
1131
              $field = $this->_fields[$elementName . "-Primary$elementType"] ?? NULL;
totten's avatar
totten committed
1132 1133
            }
            else {
1134
              $field = $this->_fields[$elementName . "-$locationTypeId$elementType"] ?? NULL;
totten's avatar
totten committed
1135 1136 1137 1138
            }
          }
          elseif (is_numeric($name)) {
            //this for phone type to work
1139
            if (in_array($elementName, ['phone', 'phone_ext'])) {
1140
              $field = $this->_fields[$elementName . "-Primary" . $elementType] ?? NULL;
totten's avatar
totten committed
1141 1142
            }
            else {
1143
              $field = $this->_fields[$elementName . "-Primary"] ?? NULL;
totten's avatar
totten committed
1144 1145 1146 1147
            }
          }
          else {
            //this is for phone type to work for profile edit
1148
            if (in_array($elementName, ['phone', 'phone_ext'])) {
1149
              $field = $this->_fields[$elementName . "-$locationTypeId$elementType"] ?? NULL;
totten's avatar
totten committed
1150 1151
            }
            else {
1152
              $field = $this->_fields[$elementName . "-$locationTypeId"] ?? NULL;
totten's avatar
totten committed
1153 1154 1155 1156
            }
          }
        }

lobo's avatar
lobo committed
1157
        // Check if there is a value, if so also add to where Clause
totten's avatar
totten committed
1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168
        $addWhere = FALSE;
        if ($this->_params) {
          $nm = $elementName;
          if (isset($locationTypeId)) {
            $nm .= "-$locationTypeId";
          }
          if (!is_numeric($elementType)) {
            $nm .= "$elementType";
          }

          foreach ($this->_params as $id => $values) {
monishdeb's avatar
monishdeb committed
1169
            if ((is_array($values) && $values[0] == $nm) ||
1170
              (in_array($elementName, ['phone', 'im'])
totten's avatar
totten committed
1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182
                && (strpos($values[0], $nm) !== FALSE)
              )
            ) {
              $addWhere = TRUE;
              $addWhereCount++;
              break;
            }
          }
        }

        if ($field && isset($field['where'])) {
          list($tableName, $fieldName) = explode('.', $field['where'], 2);
1183 1184
          $pf = substr($tableName, 8);
          $tName = $name . '-' . $pf . $elementType;
totten's avatar
totten committed
1185
          if (isset($tableName)) {
1186 1187 1188 1189 1190 1191 1192
            if ($tableName == 'civicrm_state_province' || $tableName == 'civicrm_country' || $tableName == 'civicrm_county') {
              $this->_select["{$tName}_id"] = "{$aName}.{$pf}_id as `{$tName}_id`";
            }
            else {
              $this->_select["{$tName}_id"] = "`$tName`.id as `{$tName}_id`";
            }

totten's avatar
totten committed
1193 1194 1195
            $this->_element["{$tName}_id"] = 1;
            if (substr($tName, -15) == '-state_province') {
              // FIXME: hack to fix CRM-1900
1196
              $a = Civi::settings()->get('address_format');