CRM/Export/BAO/ExportProcessor.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17
  18 /**
  19  * Class CRM_Export_BAO_ExportProcessor
  20  *
  21  * Class to handle logic of export.
  22  */
  23 class CRM_Export_BAO_ExportProcessor {
  24
  25   /**
  26    * @var int
  27    */
  28   protected $queryMode;
  29
  30   /**
  31    * @var int
  32    */
  33   protected $exportMode;
  34
  35   /**
  36    * Array of fields in the main query.
  37    *
  38    * @var array
  39    */
  40   protected $queryFields = [];
  41
  42   /**
  43    * Either AND or OR.
  44    *
  45    * @var string
  46    */
  47   protected $queryOperator;
  48
  49   /**
  50    * Requested output fields.
  51    *
  52    * If set to NULL then it is 'primary fields only'
  53    * which actually means pretty close to all fields!
  54    *
  55    * @var array|null
  56    */
  57   protected $requestedFields;
  58
  59   /**
  60    * Is the contact being merged into a single household.
  61    *
  62    * @var bool
  63    */
  64   protected $isMergeSameHousehold;
  65
  66   /**
  67    * Should contacts with the same address be merged.
  68    *
  69    * @var bool
  70    */
  71   protected $isMergeSameAddress = FALSE;
  72
  73   /**
  74    * Fields that need to be retrieved for address merge purposes but should not be in output.
  75    *
  76    * @var array
  77    */
  78   protected $additionalFieldsForSameAddressMerge = [];
  79
  80   /**
  81    * Fields used for merging same contacts.
  82    *
  83    * @var array
  84    */
  85   protected $contactGreetingFields = [];
  86
  87   /**
  88    * An array of primary IDs of the entity being exported.
  89    *
  90    * @var array
  91    */
  92   protected $ids = [];
  93
  94   /**
  95    * Greeting options mapping to various greeting ids.
  96    *
  97    * This stores the option values for the addressee, postal_greeting & email_greeting
  98    * option groups.
  99    *
 100    * @var array
 101    */
 102   protected $greetingOptions = [];
 103
 104   /**
 105    * Get additional non-visible fields for address merge purposes.
 106    *
 107    * @return array
 108    */
 109   public function getAdditionalFieldsForSameAddressMerge(): array {
 110     return $this->additionalFieldsForSameAddressMerge;
 111   }
 112
 113   /**
 114    * Set additional non-visible fields for address merge purposes.
 115    */
 116   public function setAdditionalFieldsForSameAddressMerge() {
 117     if ($this->isMergeSameAddress) {
 118       $fields = ['id', 'master_id', 'state_province_id', 'postal_greeting_id', 'addressee_id'];
 119       foreach ($fields as $index => $field) {
 120         if (!empty($this->getReturnProperties()[$field])) {
 121           unset($fields[$index]);
 122         }
 123       }
 124       $this->additionalFieldsForSameAddressMerge = array_fill_keys($fields, 1);
 125     }
 126   }
 127
 128   /**
 129    * Should contacts with the same address be merged.
 130    *
 131    * @return bool
 132    */
 133   public function isMergeSameAddress(): bool {
 134     return $this->isMergeSameAddress;
 135   }
 136
 137   /**
 138    * Set same address is to be merged.
 139    *
 140    * @param bool $isMergeSameAddress
 141    */
 142   public function setIsMergeSameAddress(bool $isMergeSameAddress) {
 143     $this->isMergeSameAddress = $isMergeSameAddress;
 144   }
 145
 146   /**
 147    * Additional fields required to export postal fields.
 148    *
 149    * @var array
 150    */
 151   protected $additionalFieldsForPostalExport = [];
 152
 153   /**
 154    * Get additional fields required to do a postal export.
 155    *
 156    * @return array
 157    */
 158   public function getAdditionalFieldsForPostalExport() {
 159     return $this->additionalFieldsForPostalExport;
 160   }
 161
 162   /**
 163    * Set additional fields required for a postal export.
 164    */
 165   public function setAdditionalFieldsForPostalExport() {
 166     if ($this->getRequestedFields() && $this->isPostalableOnly()) {
 167       $fields = ['is_deceased', 'do_not_mail', 'street_address', 'supplemental_address_1'];
 168       foreach ($fields as $index => $field) {
 169         if (!empty($this->getReturnProperties()[$field])) {
 170           unset($fields[$index]);
 171         }
 172       }
 173       $this->additionalFieldsForPostalExport = array_fill_keys($fields, 1);
 174     }
 175   }
 176
 177   /**
 178    * Only export contacts that can receive postal mail.
 179    *
 180    * Includes being alive, having an address & not having do_not_mail.
 181    *
 182    * @var bool
 183    */
 184   protected $isPostalableOnly;
 185
 186   /**
 187    * Key representing the head of household in the relationship array.
 188    *
 189    * e.g. ['8_b_a' => 'Household Member Is', '8_a_b = 'Household Member Of'.....]
 190    *
 191    * @var array
 192    */
 193   protected $relationshipTypes = [];
 194
 195   /**
 196    * Array of properties to retrieve for relationships.
 197    *
 198    * @var array
 199    */
 200   protected $relationshipReturnProperties = [];
 201
 202   /**
 203    * IDs of households that have already been exported.
 204    *
 205    * @var array
 206    */
 207   protected $exportedHouseholds = [];
 208
 209   /**
 210    * Contacts to be merged by virtue of their shared address.
 211    *
 212    * @var array
 213    */
 214   protected $contactsToMerge = [];
 215
 216   /**
 217    * Households to skip during export as they will be exported via their relationships anyway.
 218    *
 219    * @var array
 220    */
 221   protected $householdsToSkip = [];
 222
 223   /**
 224    * Additional fields to return.
 225    *
 226    * This doesn't make much sense when we have a fields set but search build add it's own onto
 227    * the 'Primary fields' (all) option.
 228    *
 229    * @var array
 230    */
 231   protected $additionalRequestedReturnProperties = [];
 232
 233   /**
 234    * Get additional return properties.
 235    *
 236    * @return array
 237    */
 238   public function getAdditionalRequestedReturnProperties() {
 239     return $this->additionalRequestedReturnProperties;
 240   }
 241
 242   /**
 243    * Set additional return properties.
 244    *
 245    * @param array $value
 246    */
 247   public function setAdditionalRequestedReturnProperties($value) {
 248     // fix for CRM-7066
 249     if (!empty($value['group'])) {
 250       unset($value['group']);
 251       $value['groups'] = 1;
 252     }
 253     $this->additionalRequestedReturnProperties = $value;
 254   }
 255
 256   /**
 257    * Get return properties by relationship.
 258    * @return array
 259    */
 260   public function getRelationshipReturnProperties() {
 261     return $this->relationshipReturnProperties;
 262   }
 263
 264   /**
 265    * Export values for related contacts.
 266    *
 267    * @var array
 268    */
 269   protected $relatedContactValues = [];
 270
 271   /**
 272    * @var array
 273    */
 274   protected $returnProperties = [];
 275
 276   /**
 277    * @var array
 278    */
 279   protected $outputSpecification = [];
 280
 281   /**
 282    * @var string
 283    */
 284   protected $componentTable = '';
 285
 286   /**
 287    * @return string
 288    */
 289   public function getComponentTable() {
 290     return $this->componentTable;
 291   }
 292
 293   /**
 294    * Set the component table (if any).
 295    *
 296    * @param string $componentTable
 297    */
 298   public function setComponentTable($componentTable) {
 299     $this->componentTable = $componentTable;
 300   }
 301
 302   /**
 303    * Clause from component search.
 304    *
 305    * @var string
 306    */
 307   protected $componentClause = '';
 308
 309   /**
 310    * @return string
 311    */
 312   public function getComponentClause() {
 313     return $this->componentClause;
 314   }
 315
 316   /**
 317    * @param string $componentClause
 318    */
 319   public function setComponentClause($componentClause) {
 320     $this->componentClause = $componentClause;
 321   }
 322
 323   /**
 324    * Name of a temporary table created to hold the results.
 325    *
 326    * Current decision making on when to create a temp table is kinda bad so this might change
 327    * a bit as it is reviewed but basically we need a temp table or similar to calculate merging
 328    * addresses. Merging households is handled in php. We create a temp table even when we don't need them.
 329    *
 330    * @var string
 331    */
 332   protected $temporaryTable;
 333
 334   /**
 335    * @return string
 336    */
 337   public function getTemporaryTable(): string {
 338     return $this->temporaryTable;
 339   }
 340
 341   /**
 342    * @param string $temporaryTable
 343    */
 344   public function setTemporaryTable(string $temporaryTable) {
 345     $this->temporaryTable = $temporaryTable;
 346   }
 347
 348   protected $postalGreetingTemplate;
 349
 350   /**
 351    * @return mixed
 352    */
 353   public function getPostalGreetingTemplate() {
 354     return $this->postalGreetingTemplate;
 355   }
 356
 357   /**
 358    * @param mixed $postalGreetingTemplate
 359    */
 360   public function setPostalGreetingTemplate($postalGreetingTemplate) {
 361     $this->postalGreetingTemplate = $postalGreetingTemplate;
 362   }
 363
 364   /**
 365    * @return mixed
 366    */
 367   public function getAddresseeGreetingTemplate() {
 368     return $this->addresseeGreetingTemplate;
 369   }
 370
 371   /**
 372    * @param mixed $addresseeGreetingTemplate
 373    */
 374   public function setAddresseeGreetingTemplate($addresseeGreetingTemplate) {
 375     $this->addresseeGreetingTemplate = $addresseeGreetingTemplate;
 376   }
 377
 378   protected $addresseeGreetingTemplate;
 379
 380   /**
 381    * CRM_Export_BAO_ExportProcessor constructor.
 382    *
 383    * @param int $exportMode
 384    * @param array|null $requestedFields
 385    * @param string $queryOperator
 386    * @param bool $isMergeSameHousehold
 387    * @param bool $isPostalableOnly
 388    * @param bool $isMergeSameAddress
 389    * @param array $formValues
 390    *   Values from the export options form on contact export. We currently support these keys
 391    *   - postal_greeting
 392    *   - postal_other
 393    *   - addresee_greeting
 394    *   - addressee_other
 395    */
 396   public function __construct($exportMode, $requestedFields, $queryOperator, $isMergeSameHousehold = FALSE, $isPostalableOnly = FALSE, $isMergeSameAddress = FALSE, $formValues = []) {
 397     $this->setExportMode($exportMode);
 398     $this->setQueryMode();
 399     $this->setQueryOperator($queryOperator);
 400     $this->setRequestedFields($requestedFields);
 401     $this->setRelationshipTypes();
 402     $this->setIsMergeSameHousehold($isMergeSameHousehold || $isMergeSameAddress);
 403     $this->setIsPostalableOnly($isPostalableOnly);
 404     $this->setIsMergeSameAddress($isMergeSameAddress);
 405     $this->setReturnProperties($this->determineReturnProperties());
 406     $this->setAdditionalFieldsForSameAddressMerge();
 407     $this->setAdditionalFieldsForPostalExport();
 408     $this->setHouseholdMergeReturnProperties();
 409     $this->setGreetingStringsForSameAddressMerge($formValues);
 410     $this->setGreetingOptions();
 411   }
 412
 413   /**
 414    * Set the greeting options, if relevant.
 415    */
 416   public function setGreetingOptions() {
 417     if ($this->isMergeSameAddress()) {
 418       $this->greetingOptions['addressee'] = CRM_Core_OptionGroup::values('addressee');
 419       $this->greetingOptions['postal_greeting'] = CRM_Core_OptionGroup::values('postal_greeting');
 420     }
 421   }
 422
 423   /**
 424    * @return bool
 425    */
 426   public function isPostalableOnly() {
 427     return $this->isPostalableOnly;
 428   }
 429
 430   /**
 431    * @param bool $isPostalableOnly
 432    */
 433   public function setIsPostalableOnly($isPostalableOnly) {
 434     $this->isPostalableOnly = $isPostalableOnly;
 435   }
 436
 437   /**
 438    * @return array|null
 439    */
 440   public function getRequestedFields() {
 441     return empty($this->requestedFields) ? NULL : $this->requestedFields;
 442   }
 443
 444   /**
 445    * @param array|null $requestedFields
 446    */
 447   public function setRequestedFields($requestedFields) {
 448     $this->requestedFields = $requestedFields;
 449   }
 450
 451   /**
 452    * @return array
 453    */
 454   public function getReturnProperties() {
 455     return array_merge($this->returnProperties, $this->getAdditionalRequestedReturnProperties(), $this->getAdditionalFieldsForSameAddressMerge(), $this->getAdditionalFieldsForPostalExport());
 456   }
 457
 458   /**
 459    * @param array $returnProperties
 460    */
 461   public function setReturnProperties($returnProperties) {
 462     $this->returnProperties = $returnProperties;
 463   }
 464
 465   /**
 466    * @return array
 467    */
 468   public function getRelationshipTypes() {
 469     return $this->relationshipTypes;
 470   }
 471
 472   /**
 473    */
 474   public function setRelationshipTypes() {
 475     $this->relationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(
 476       NULL,
 477       NULL,
 478       NULL,
 479       NULL,
 480       TRUE,
 481       'name',
 482       FALSE
 483     );
 484   }
 485
 486   /**
 487    * Set the value for a relationship type field.
 488    *
 489    * In this case we are building up an array of properties for a related contact.
 490    *
 491    * These may be used for direct exporting or for merge to household depending on the
 492    * options selected.
 493    *
 494    * @param string $relationshipType
 495    * @param int $contactID
 496    * @param string $field
 497    * @param string $value
 498    */
 499   public function setRelationshipValue($relationshipType, $contactID, $field, $value) {
 500     $this->relatedContactValues[$relationshipType][$contactID][$field] = $value;
 501     if ($field === 'id' && $this->isHouseholdMergeRelationshipTypeKey($relationshipType)) {
 502       $this->householdsToSkip[] = $value;
 503     }
 504   }
 505
 506   /**
 507    * Get the value for a relationship type field.
 508    *
 509    * In this case we are building up an array of properties for a related contact.
 510    *
 511    * These may be used for direct exporting or for merge to household depending on the
 512    * options selected.
 513    *
 514    * @param string $relationshipType
 515    * @param int $contactID
 516    * @param string $field
 517    *
 518    * @return string
 519    */
 520   public function getRelationshipValue($relationshipType, $contactID, $field) {
 521     return $this->relatedContactValues[$relationshipType][$contactID][$field] ?? '';
 522   }
 523
 524   /**
 525    * Get the id of the related household.
 526    *
 527    * @param int $contactID
 528    * @param string $relationshipType
 529    *
 530    * @return int
 531    */
 532   public function getRelatedHouseholdID($contactID, $relationshipType) {
 533     return $this->relatedContactValues[$relationshipType][$contactID]['id'];
 534   }
 535
 536   /**
 537    * Has the household already been exported.
 538    *
 539    * @param int $housholdContactID
 540    *
 541    * @return bool
 542    */
 543   public function isHouseholdExported($housholdContactID) {
 544     return isset($this->exportedHouseholds[$housholdContactID]);
 545
 546   }
 547
 548   /**
 549    * @return bool
 550    */
 551   public function isMergeSameHousehold() {
 552     return $this->isMergeSameHousehold;
 553   }
 554
 555   /**
 556    * @param bool $isMergeSameHousehold
 557    */
 558   public function setIsMergeSameHousehold($isMergeSameHousehold) {
 559     $this->isMergeSameHousehold = $isMergeSameHousehold;
 560   }
 561
 562   /**
 563    * Return relationship types for household merge.
 564    *
 565    * @return mixed
 566    */
 567   public function getHouseholdRelationshipTypes() {
 568     if (!$this->isMergeSameHousehold()) {
 569       return [];
 570     }
 571     return [
 572       CRM_Utils_Array::key('Household Member of', $this->getRelationshipTypes()),
 573       CRM_Utils_Array::key('Head of Household for', $this->getRelationshipTypes()),
 574     ];
 575   }
 576
 577   /**
 578    * @param $fieldName
 579    * @return bool
 580    */
 581   public function isRelationshipTypeKey($fieldName) {
 582     return array_key_exists($fieldName, $this->relationshipTypes);
 583   }
 584
 585   /**
 586    * @param $fieldName
 587    * @return bool
 588    */
 589   public function isHouseholdMergeRelationshipTypeKey($fieldName) {
 590     return in_array($fieldName, $this->getHouseholdRelationshipTypes());
 591   }
 592
 593   /**
 594    * @return string
 595    */
 596   public function getQueryOperator() {
 597     return $this->queryOperator;
 598   }
 599
 600   /**
 601    * @param string $queryOperator
 602    */
 603   public function setQueryOperator($queryOperator) {
 604     $this->queryOperator = $queryOperator;
 605   }
 606
 607   /**
 608    * @return array
 609    */
 610   public function getIds() {
 611     return $this->ids;
 612   }
 613
 614   /**
 615    * @param array $ids
 616    */
 617   public function setIds($ids) {
 618     $this->ids = $ids;
 619   }
 620
 621   /**
 622    * @return array
 623    */
 624   public function getQueryFields() {
 625     return array_merge(
 626       $this->queryFields,
 627       $this->getComponentPaymentFields()
 628     );
 629   }
 630
 631   /**
 632    * @param array $queryFields
 633    */
 634   public function setQueryFields($queryFields) {
 635     // legacy hacks - we add these to queryFields because this
 636     // pseudometadata is currently required.
 637     $queryFields['im_provider']['pseudoconstant']['var'] = 'imProviders';
 638     $queryFields['country']['context'] = 'country';
 639     $queryFields['world_region']['context'] = 'country';
 640     $queryFields['state_province']['context'] = 'province';
 641     $queryFields['contact_id'] = ['title' => ts('Contact ID'), 'type' => CRM_Utils_Type::T_INT];
 642     $this->queryFields = $queryFields;
 643   }
 644
 645   /**
 646    * @return int
 647    */
 648   public function getQueryMode() {
 649     return $this->queryMode;
 650   }
 651
 652   /**
 653    * Set the query mode based on the export mode.
 654    */
 655   public function setQueryMode() {
 656
 657     switch ($this->getExportMode()) {
 658       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
 659         $this->queryMode = CRM_Contact_BAO_Query::MODE_CONTRIBUTE;
 660         break;
 661
 662       case CRM_Export_Form_Select::EVENT_EXPORT:
 663         $this->queryMode = CRM_Contact_BAO_Query::MODE_EVENT;
 664         break;
 665
 666       case CRM_Export_Form_Select::MEMBER_EXPORT:
 667         $this->queryMode = CRM_Contact_BAO_Query::MODE_MEMBER;
 668         break;
 669
 670       case CRM_Export_Form_Select::PLEDGE_EXPORT:
 671         $this->queryMode = CRM_Contact_BAO_Query::MODE_PLEDGE;
 672         break;
 673
 674       case CRM_Export_Form_Select::CASE_EXPORT:
 675         $this->queryMode = CRM_Contact_BAO_Query::MODE_CASE;
 676         break;
 677
 678       case CRM_Export_Form_Select::GRANT_EXPORT:
 679         $this->queryMode = CRM_Contact_BAO_Query::MODE_GRANT;
 680         break;
 681
 682       case CRM_Export_Form_Select::ACTIVITY_EXPORT:
 683         $this->queryMode = CRM_Contact_BAO_Query::MODE_ACTIVITY;
 684         break;
 685
 686       default:
 687         $this->queryMode = CRM_Contact_BAO_Query::MODE_CONTACTS;
 688     }
 689   }
 690
 691   /**
 692    * @return int
 693    */
 694   public function getExportMode() {
 695     return $this->exportMode;
 696   }
 697
 698   /**
 699    * @param int $exportMode
 700    */
 701   public function setExportMode($exportMode) {
 702     $this->exportMode = $exportMode;
 703   }
 704
 705   /**
 706    * Get the name for the export file.
 707    *
 708    * @return string
 709    */
 710   public function getExportFileName() {
 711     switch ($this->getExportMode()) {
 712       case CRM_Export_Form_Select::CONTACT_EXPORT:
 713         return ts('CiviCRM Contact Search');
 714
 715       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
 716         return ts('CiviCRM Contribution Search');
 717
 718       case CRM_Export_Form_Select::MEMBER_EXPORT:
 719         return ts('CiviCRM Member Search');
 720
 721       case CRM_Export_Form_Select::EVENT_EXPORT:
 722         return ts('CiviCRM Participant Search');
 723
 724       case CRM_Export_Form_Select::PLEDGE_EXPORT:
 725         return ts('CiviCRM Pledge Search');
 726
 727       case CRM_Export_Form_Select::CASE_EXPORT:
 728         return ts('CiviCRM Case Search');
 729
 730       case CRM_Export_Form_Select::GRANT_EXPORT:
 731         return ts('CiviCRM Grant Search');
 732
 733       case CRM_Export_Form_Select::ACTIVITY_EXPORT:
 734         return ts('CiviCRM Activity Search');
 735
 736       default:
 737         // Legacy code suggests the value could be 'financial' - ie. something
 738         // other than what should be accepted. However, I suspect that this line is
 739         // never hit.
 740         return ts('CiviCRM Search');
 741     }
 742   }
 743
 744   /**
 745    * Get the label for the header row based on the field to output.
 746    *
 747    * @param string $field
 748    *
 749    * @return string
 750    */
 751   public function getHeaderForRow($field) {
 752     if (substr($field, -11) == 'campaign_id') {
 753       // @todo - set this correctly in the xml rather than here.
 754       // This will require a generalised handling cleanup
 755       return ts('Campaign ID');
 756     }
 757     if ($this->isMergeSameHousehold() && !$this->isMergeSameAddress() && $field === 'id') {
 758       // This is weird - even if we are merging households not every contact in the export is a household so this would not be accurate.
 759       return ts('Household ID');
 760     }
 761     elseif (isset($this->getQueryFields()[$field]['title'])) {
 762       return $this->getQueryFields()[$field]['title'];
 763     }
 764     elseif ($this->isExportPaymentFields() && array_key_exists($field, $this->getcomponentPaymentFields())) {
 765       return CRM_Utils_Array::value($field, $this->getcomponentPaymentFields())['title'];
 766     }
 767     else {
 768       return $field;
 769     }
 770   }
 771
 772   /**
 773    * @param $params
 774    * @param $order
 775    *
 776    * @return array
 777    */
 778   public function runQuery($params, $order) {
 779     $returnProperties = $this->getReturnProperties();
 780     $params = array_merge($params, $this->getWhereParams());
 781
 782     $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL,
 783       FALSE, FALSE, $this->getQueryMode(),
 784       FALSE, TRUE, TRUE, NULL, $this->getQueryOperator()
 785     );
 786
 787     //sort by state
 788     //CRM-15301
 789     $query->_sort = $order;
 790     list($select, $from, $where, $having) = $query->query();
 791     $this->setQueryFields($query->_fields);
 792     $whereClauses = ['trash_clause' => "contact_a.is_deleted != 1"];
 793     if ($this->getRequestedFields() && ($this->getComponentTable())) {
 794       $from .= " INNER JOIN " . $this->getComponentTable() . " ctTable ON ctTable.contact_id = contact_a.id ";
 795     }
 796     elseif ($this->getComponentClause()) {
 797       $whereClauses[] = $this->getComponentClause();
 798     }
 799
 800     // CRM-13982 - check if is deleted
 801     foreach ($params as $value) {
 802       if ($value[0] == 'contact_is_deleted') {
 803         unset($whereClauses['trash_clause']);
 804       }
 805     }
 806
 807     if ($this->isPostalableOnly) {
 808       if (array_key_exists('street_address', $returnProperties)) {
 809         $addressWhere = " civicrm_address.street_address <> ''";
 810         if (array_key_exists('supplemental_address_1', $returnProperties)) {
 811           // We need this to be an OR rather than AND on the street_address so, hack it in.
 812           $addressOptions = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME,
 813             'address_options', TRUE, NULL, TRUE
 814           );
 815           if (!empty($addressOptions['supplemental_address_1'])) {
 816             $addressWhere .= " OR civicrm_address.supplemental_address_1 <> ''";
 817           }
 818         }
 819         $whereClauses['address'] = '(' . $addressWhere . ')';
 820       }
 821     }
 822
 823     if (empty($where)) {
 824       $where = "WHERE " . implode(' AND ', $whereClauses);
 825     }
 826     else {
 827       $where .= " AND " . implode(' AND ', $whereClauses);
 828     }
 829
 830     $groupBy = $this->getGroupBy($query);
 831     $queryString = "$select $from $where $having $groupBy";
 832     if ($order) {
 833       // always add contact_a.id to the ORDER clause
 834       // so the order is deterministic
 835       //CRM-15301
 836       if (strpos('contact_a.id', $order) === FALSE) {
 837         $order .= ", contact_a.id";
 838       }
 839
 840       list($field, $dir) = explode(' ', $order, 2);
 841       $field = trim($field);
 842       if (!empty($this->getReturnProperties()[$field])) {
 843         //CRM-15301
 844         $queryString .= " ORDER BY $order";
 845       }
 846     }
 847     return [$query, $queryString];
 848   }
 849
 850   /**
 851    * Add a row to the specification for how to output data.
 852    *
 853    * @param string $key
 854    * @param string $relationshipType
 855    * @param string $locationType
 856    * @param int $entityTypeID phone_type_id or provider_id for phone or im fields.
 857    */
 858   public function addOutputSpecification($key, $relationshipType = NULL, $locationType = NULL, $entityTypeID = NULL) {
 859     $entityLabel = '';
 860     if ($entityTypeID) {
 861       if ($key === 'phone') {
 862         $entityLabel = CRM_Core_PseudoConstant::getLabel('CRM_Core_BAO_Phone', 'phone_type_id', $entityTypeID);
 863       }
 864       if ($key === 'im') {
 865         $entityLabel = CRM_Core_PseudoConstant::getLabel('CRM_Core_BAO_IM', 'provider_id', $entityTypeID);
 866       }
 867     }
 868
 869     // These oddly constructed keys are for legacy reasons. Altering them will affect test success
 870     // but in time it may be good to rationalise them.
 871     $label = $this->getOutputSpecificationLabel($key, $relationshipType, $locationType, $entityLabel);
 872     $index = $this->getOutputSpecificationIndex($key, $relationshipType, $locationType, $entityLabel);
 873     $fieldKey = $this->getOutputSpecificationFieldKey($key, $relationshipType, $locationType, $entityLabel);
 874
 875     $this->outputSpecification[$index]['header'] = $label;
 876     $this->outputSpecification[$index]['sql_columns'] = $this->getSqlColumnDefinition($fieldKey, $key);
 877
 878     if ($relationshipType && $this->isHouseholdMergeRelationshipTypeKey($relationshipType)) {
 879       $this->setColumnAsCalculationOnly($index);
 880     }
 881     $this->outputSpecification[$index]['metadata'] = $this->getMetaDataForField($key);
 882   }
 883
 884   /**
 885    * Get the metadata for the given field.
 886    *
 887    * @param $key
 888    *
 889    * @return array
 890    */
 891   public function getMetaDataForField($key) {
 892     $mappings = ['contact_id' => 'id'];
 893     if (isset($this->getQueryFields()[$key])) {
 894       return $this->getQueryFields()[$key];
 895     }
 896     if (isset($mappings[$key])) {
 897       return $this->getQueryFields()[$mappings[$key]];
 898     }
 899     return [];
 900   }
 901
 902   /**
 903    * @param $key
 904    */
 905   public function setSqlColumnDefn($key) {
 906     $this->outputSpecification[$this->getMungedFieldName($key)]['sql_columns'] = $this->getSqlColumnDefinition($key, $this->getMungedFieldName($key));
 907   }
 908
 909   /**
 910    * Mark a column as only required for calculations.
 911    *
 912    * Do not include the row with headers.
 913    *
 914    * @param string $column
 915    */
 916   public function setColumnAsCalculationOnly($column) {
 917     $this->outputSpecification[$column]['do_not_output_to_csv'] = TRUE;
 918   }
 919
 920   /**
 921    * @return array
 922    */
 923   public function getHeaderRows() {
 924     $headerRows = [];
 925     foreach ($this->outputSpecification as $key => $spec) {
 926       if (empty($spec['do_not_output_to_csv'])) {
 927         $headerRows[] = $spec['header'];
 928       }
 929     }
 930     return $headerRows;
 931   }
 932
 933   /**
 934    * @return array
 935    */
 936   public function getSQLColumns() {
 937     $sqlColumns = [];
 938     foreach ($this->outputSpecification as $key => $spec) {
 939       if (empty($spec['do_not_output_to_sql'])) {
 940         $sqlColumns[$key] = $spec['sql_columns'];
 941       }
 942     }
 943     return $sqlColumns;
 944   }
 945
 946   /**
 947    * @return array
 948    */
 949   public function getMetadata() {
 950     $metadata = [];
 951     foreach ($this->outputSpecification as $key => $spec) {
 952       $metadata[$key] = $spec['metadata'];
 953     }
 954     return $metadata;
 955   }
 956
 957   /**
 958    * Build the row for output.
 959    *
 960    * @param \CRM_Contact_BAO_Query $query
 961    * @param CRM_Core_DAO $iterationDAO
 962    * @param array $outputColumns
 963    * @param $metadata
 964    * @param $paymentDetails
 965    * @param $addPaymentHeader
 966    *
 967    * @return array|bool
 968    */
 969   public function buildRow($query, $iterationDAO, $outputColumns, $metadata, $paymentDetails, $addPaymentHeader) {
 970     $paymentTableId = $this->getPaymentTableID();
 971     if ($this->isHouseholdToSkip($iterationDAO->contact_id)) {
 972       return FALSE;
 973     }
 974     $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id');
 975     $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id');
 976
 977     $row = [];
 978     $householdMergeRelationshipType = $this->getHouseholdMergeTypeForRow($iterationDAO->contact_id);
 979     if ($householdMergeRelationshipType) {
 980       $householdID = $this->getRelatedHouseholdID($iterationDAO->contact_id, $householdMergeRelationshipType);
 981       if ($this->isHouseholdExported($householdID)) {
 982         return FALSE;
 983       }
 984       foreach (array_keys($outputColumns) as $column) {
 985         $row[$column] = $this->getRelationshipValue($householdMergeRelationshipType, $iterationDAO->contact_id, $column);
 986       }
 987       $this->markHouseholdExported($householdID);
 988       return $row;
 989     }
 990
 991     $query->convertToPseudoNames($iterationDAO);
 992
 993     //first loop through output columns so that we return what is required, and in same order.
 994     foreach ($outputColumns as $field => $value) {
 995       // add im_provider to $dao object
 996       if ($field == 'im_provider' && property_exists($iterationDAO, 'provider_id')) {
 997         $iterationDAO->im_provider = $iterationDAO->provider_id;
 998       }
 999
1000       //build row values (data)
1001       $fieldValue = NULL;
1002       if (property_exists($iterationDAO, $field)) {
1003         $fieldValue = $iterationDAO->$field;
1004         // to get phone type from phone type id
1005         if ($field == 'phone_type_id' && isset($phoneTypes[$fieldValue])) {
1006           $fieldValue = $phoneTypes[$fieldValue];
1007         }
1008         elseif ($field == 'provider_id' || $field == 'im_provider') {
1009           $fieldValue = $imProviders[$fieldValue] ?? NULL;
1010         }
1011         elseif (strstr($field, 'master_id')) {
1012           // @todo - why not just $field === 'master_id'  - what else would it be?
1013           $masterAddressId = $iterationDAO->$field ?? NULL;
1014           // get display name of contact that address is shared.
1015           $fieldValue = CRM_Contact_BAO_Contact::getMasterDisplayName($masterAddressId);
1016         }
1017       }
1018
1019       if ($this->isRelationshipTypeKey($field)) {
1020         $this->buildRelationshipFieldsForRow($row, $iterationDAO->contact_id, $value, $field);
1021       }
1022       else {
1023         $row[$field] = $this->getTransformedFieldValue($field, $iterationDAO, $fieldValue, $metadata, $paymentDetails);
1024       }
1025     }
1026
1027     // If specific payment fields have been selected for export, payment
1028     // data will already be in $row. Otherwise, add payment related
1029     // information, if appropriate.
1030     if ($addPaymentHeader) {
1031       if (!$this->isExportSpecifiedPaymentFields()) {
1032         $nullContributionDetails = array_fill_keys(array_keys($this->getPaymentHeaders()), NULL);
1033         if ($this->isExportPaymentFields()) {
1034           $paymentData = $paymentDetails[$row[$paymentTableId]] ?? NULL;
1035           if (!is_array($paymentData) || empty($paymentData)) {
1036             $paymentData = $nullContributionDetails;
1037           }
1038           $row = array_merge($row, $paymentData);
1039         }
1040         elseif (!empty($paymentDetails)) {
1041           $row = array_merge($row, $nullContributionDetails);
1042         }
1043       }
1044     }
1045     //remove organization name for individuals if it is set for current employer
1046     if (!empty($row['contact_type']) &&
1047       $row['contact_type'] == 'Individual' && array_key_exists('organization_name', $row)
1048     ) {
1049       $row['organization_name'] = '';
1050     }
1051     return $row;
1052   }
1053
1054   /**
1055    * If this row has a household whose details we should use get the relationship type key.
1056    *
1057    * @param $contactID
1058    *
1059    * @return bool
1060    */
1061   public function getHouseholdMergeTypeForRow($contactID) {
1062     if (!$this->isMergeSameHousehold()) {
1063       return FALSE;
1064     }
1065     foreach ($this->getHouseholdRelationshipTypes() as $relationshipType) {
1066       if (isset($this->relatedContactValues[$relationshipType][$contactID])) {
1067         return $relationshipType;
1068       }
1069     }
1070   }
1071
1072   /**
1073    * Mark the given household as already exported.
1074    *
1075    * @param $householdID
1076    */
1077   public function markHouseholdExported($householdID) {
1078     $this->exportedHouseholds[$householdID] = $householdID;
1079   }
1080
1081   /**
1082    * @param $field
1083    * @param $iterationDAO
1084    * @param $fieldValue
1085    * @param $metadata
1086    * @param $paymentDetails
1087    *
1088    * @return string
1089    */
1090   public function getTransformedFieldValue($field, $iterationDAO, $fieldValue, $metadata, $paymentDetails) {
1091
1092     $i18n = CRM_Core_I18n::singleton();
1093     if ($field == 'id') {
1094       return $iterationDAO->contact_id;
1095       // special case for calculated field
1096     }
1097     elseif ($field == 'source_contact_id') {
1098       return $iterationDAO->contact_id;
1099     }
1100     elseif ($field == 'pledge_balance_amount') {
1101       return $iterationDAO->pledge_amount - $iterationDAO->pledge_total_paid;
1102       // special case for calculated field
1103     }
1104     elseif ($field == 'pledge_next_pay_amount') {
1105       return $iterationDAO->pledge_next_pay_amount + $iterationDAO->pledge_outstanding_amount;
1106     }
1107     elseif (isset($fieldValue) &&
1108       $fieldValue != ''
1109     ) {
1110       //check for custom data
1111       if ($cfID = CRM_Core_BAO_CustomField::getKeyID($field)) {
1112         return CRM_Core_BAO_CustomField::displayValue($fieldValue, $cfID);
1113       }
1114
1115       elseif (in_array($field, [
1116         'email_greeting',
1117         'postal_greeting',
1118         'addressee',
1119       ])) {
1120         //special case for greeting replacement
1121         $fldValue = "{$field}_display";
1122         return $iterationDAO->$fldValue;
1123       }
1124       else {
1125         //normal fields with a touch of CRM-3157
1126         switch ($field) {
1127           case 'country':
1128           case 'world_region':
1129             return $i18n->crm_translate($fieldValue, ['context' => 'country']);
1130
1131           case 'state_province':
1132             return $i18n->crm_translate($fieldValue, ['context' => 'province']);
1133
1134           case 'gender':
1135           case 'preferred_communication_method':
1136           case 'preferred_mail_format':
1137           case 'communication_style':
1138             return $i18n->crm_translate($fieldValue);
1139
1140           default:
1141             if (isset($metadata[$field])) {
1142               // No I don't know why we do it this way & whether we could
1143               // make better use of pseudoConstants.
1144               if (!empty($metadata[$field]['context'])) {
1145                 return $i18n->crm_translate($fieldValue, $metadata[$field]);
1146               }
1147               if (!empty($metadata[$field]['pseudoconstant'])) {
1148                 if (!empty($metadata[$field]['bao'])) {
1149                   return CRM_Core_PseudoConstant::getLabel($metadata[$field]['bao'], $metadata[$field]['name'], $fieldValue);
1150                 }
1151                 // This is not our normal syntax for pseudoconstants but I am a bit loath to
1152                 // call an external function until sure it is not increasing php processing given this
1153                 // may be iterated 100,000 times & we already have the $imProvider var loaded.
1154                 // That can be next refactor...
1155                 // Yes - definitely feeling hatred for this bit of code - I know you will beat me up over it's awfulness
1156                 // but I have to reach a stable point....
1157                 $varName = $metadata[$field]['pseudoconstant']['var'];
1158                 if ($varName === 'imProviders') {
1159                   return CRM_Core_PseudoConstant::getLabel('CRM_Core_DAO_IM', 'provider_id', $fieldValue);
1160                 }
1161                 if ($varName === 'phoneTypes') {
1162                   return CRM_Core_PseudoConstant::getLabel('CRM_Core_DAO_Phone', 'phone_type_id', $fieldValue);
1163                 }
1164               }
1165
1166             }
1167             return $fieldValue;
1168         }
1169       }
1170     }
1171     elseif ($this->isExportSpecifiedPaymentFields() && array_key_exists($field, $this->getcomponentPaymentFields())) {
1172       $paymentTableId = $this->getPaymentTableID();
1173       $paymentData = $paymentDetails[$iterationDAO->$paymentTableId] ?? NULL;
1174       $payFieldMapper = [
1175         'componentPaymentField_total_amount' => 'total_amount',
1176         'componentPaymentField_contribution_status' => 'contribution_status',
1177         'componentPaymentField_payment_instrument' => 'pay_instru',
1178         'componentPaymentField_transaction_id' => 'trxn_id',
1179         'componentPaymentField_received_date' => 'receive_date',
1180       ];
1181       return CRM_Utils_Array::value($payFieldMapper[$field], $paymentData, '');
1182     }
1183     else {
1184       // if field is empty or null
1185       return '';
1186     }
1187   }
1188
1189   /**
1190    * Get array of fields to return, over & above those defined in the main contact exportable fields.
1191    *
1192    * These include export mode specific fields & some fields apparently required as 'exportableFields'
1193    * but not returned by the function of the same name.
1194    *
1195    * @return array
1196    *   Array of fields to return in the format ['field_name' => 1,...]
1197    */
1198   public function getAdditionalReturnProperties() {
1199     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CONTACTS) {
1200       $componentSpecificFields = [];
1201     }
1202     else {
1203       $componentSpecificFields = CRM_Contact_BAO_Query::defaultReturnProperties($this->getQueryMode());
1204     }
1205     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_PLEDGE) {
1206       $componentSpecificFields = array_merge($componentSpecificFields, CRM_Pledge_BAO_Query::extraReturnProperties($this->getQueryMode()));
1207       unset($componentSpecificFields['contribution_status_id']);
1208       unset($componentSpecificFields['pledge_status_id']);
1209       unset($componentSpecificFields['pledge_payment_status_id']);
1210     }
1211     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CASE) {
1212       $componentSpecificFields = array_merge($componentSpecificFields, CRM_Case_BAO_Query::extraReturnProperties($this->getQueryMode()));
1213     }
1214     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
1215       $componentSpecificFields = array_merge($componentSpecificFields, CRM_Contribute_BAO_Query::softCreditReturnProperties(TRUE));
1216       unset($componentSpecificFields['contribution_status_id']);
1217     }
1218     return $componentSpecificFields;
1219   }
1220
1221   /**
1222    * Should payment fields be appended to the export.
1223    *
1224    * (This is pretty hacky so hopefully this function won't last long - notice
1225    * how obviously it should be part of the above function!).
1226    */
1227   public function isExportPaymentFields() {
1228     if ($this->getRequestedFields() === NULL
1229       &&  in_array($this->getQueryMode(), [
1230         CRM_Contact_BAO_Query::MODE_EVENT,
1231         CRM_Contact_BAO_Query::MODE_MEMBER,
1232         CRM_Contact_BAO_Query::MODE_PLEDGE,
1233       ])) {
1234       return TRUE;
1235     }
1236     elseif ($this->isExportSpecifiedPaymentFields()) {
1237       return TRUE;
1238     }
1239     return FALSE;
1240   }
1241
1242   /**
1243    * Has specific payment fields been requested (as opposed to via all fields).
1244    *
1245    * If specific fields have been requested then they get added at various points.
1246    *
1247    * @return bool
1248    */
1249   public function isExportSpecifiedPaymentFields() {
1250     if ($this->getRequestedFields() !== NULL && $this->hasRequestedComponentPaymentFields()) {
1251       return TRUE;
1252     }
1253   }
1254
1255   /**
1256    * Get the name of the id field in the table that connects contributions to the export entity.
1257    */
1258   public function getPaymentTableID() {
1259     if ($this->getRequestedFields() === NULL) {
1260       $mapping = [
1261         CRM_Contact_BAO_Query::MODE_EVENT => 'participant_id',
1262         CRM_Contact_BAO_Query::MODE_MEMBER => 'membership_id',
1263         CRM_Contact_BAO_Query::MODE_PLEDGE => 'pledge_payment_id',
1264       ];
1265       return isset($mapping[$this->getQueryMode()]) ? $mapping[$this->getQueryMode()] : '';
1266     }
1267     elseif ($this->hasRequestedComponentPaymentFields()) {
1268       return 'participant_id';
1269     }
1270     return FALSE;
1271   }
1272
1273   /**
1274    * Have component payment fields been requested.
1275    *
1276    * @return bool
1277    */
1278   protected function hasRequestedComponentPaymentFields() {
1279     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_EVENT) {
1280       $participantPaymentFields = array_intersect_key($this->getComponentPaymentFields(), $this->getReturnProperties());
1281       if (!empty($participantPaymentFields)) {
1282         return TRUE;
1283       }
1284     }
1285     return FALSE;
1286   }
1287
1288   /**
1289    * Get fields that indicate payment fields have been requested for a component.
1290    *
1291    * Ideally this should be protected but making it temporarily public helps refactoring..
1292    *
1293    * @return array
1294    */
1295   public function getComponentPaymentFields() {
1296     return [
1297       'componentPaymentField_total_amount' => ['title' => ts('Total Amount'), 'type' => CRM_Utils_Type::T_MONEY],
1298       'componentPaymentField_contribution_status' => ['title' => ts('Contribution Status'), 'type' => CRM_Utils_Type::T_STRING],
1299       'componentPaymentField_received_date' => ['title' => ts('Date Received'), 'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME],
1300       'componentPaymentField_payment_instrument' => ['title' => ts('Payment Method'), 'type' => CRM_Utils_Type::T_STRING],
1301       'componentPaymentField_transaction_id' => ['title' => ts('Transaction ID'), 'type' => CRM_Utils_Type::T_STRING],
1302     ];
1303   }
1304
1305   /**
1306    * Get headers for payment fields.
1307    *
1308    * Returns an array of contribution fields when the entity supports payment fields and specific fields
1309    * are not specified. This is a transitional function for refactoring legacy code.
1310    */
1311   public function getPaymentHeaders() {
1312     if ($this->isExportPaymentFields() && !$this->isExportSpecifiedPaymentFields()) {
1313       return CRM_Utils_Array::collect('title', $this->getcomponentPaymentFields());
1314     }
1315     return [];
1316   }
1317
1318   /**
1319    * Get the default properties when not specified.
1320    *
1321    * In the UI this appears as 'Primary fields only' but in practice it's
1322    * most of the kitchen sink and the hallway closet thrown in.
1323    *
1324    * Since CRM-952 custom fields are excluded, but no other form of mercy is shown.
1325    *
1326    * @return array
1327    */
1328   public function getDefaultReturnProperties() {
1329     $returnProperties = [];
1330     $fields = CRM_Contact_BAO_Contact::exportableFields('All', TRUE, TRUE);
1331     $skippedFields = ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CONTACTS) ? [] : [
1332       'groups',
1333       'tags',
1334       'notes',
1335     ];
1336
1337     foreach ($fields as $key => $var) {
1338       if ($key && (substr($key, 0, 6) != 'custom') && !in_array($key, $skippedFields)) {
1339         $returnProperties[$key] = 1;
1340       }
1341     }
1342     $returnProperties = array_merge($returnProperties, $this->getAdditionalReturnProperties());
1343     return $returnProperties;
1344   }
1345
1346   /**
1347    * Add the field to relationship return properties & return it.
1348    *
1349    * This function is doing both setting & getting which is yuck but it is an interim
1350    * refactor.
1351    *
1352    * @param array $value
1353    * @param string $relationshipKey
1354    *
1355    * @return array
1356    */
1357   public function setRelationshipReturnProperties($value, $relationshipKey) {
1358     $relationField = $value['name'];
1359     $relIMProviderId = NULL;
1360     $relLocTypeId = $value['location_type_id'] ?? NULL;
1361     $locationName = CRM_Core_PseudoConstant::getName('CRM_Core_BAO_Address', 'location_type_id', $relLocTypeId);
1362     $relPhoneTypeId = CRM_Utils_Array::value('phone_type_id', $value, ($locationName ? 'Primary' : NULL));
1363     $relIMProviderId = CRM_Utils_Array::value('im_provider_id', $value, ($locationName ? 'Primary' : NULL));
1364     if (in_array($relationField, $this->getValidLocationFields()) && $locationName) {
1365       if ($relationField === 'phone') {
1366         $this->relationshipReturnProperties[$relationshipKey]['location'][$locationName]['phone-' . $relPhoneTypeId] = 1;
1367       }
1368       elseif ($relationField === 'im') {
1369         $this->relationshipReturnProperties[$relationshipKey]['location'][$locationName]['im-' . $relIMProviderId] = 1;
1370       }
1371       else {
1372         $this->relationshipReturnProperties[$relationshipKey]['location'][$locationName][$relationField] = 1;
1373       }
1374     }
1375     else {
1376       $this->relationshipReturnProperties[$relationshipKey][$relationField] = 1;
1377     }
1378     return $this->relationshipReturnProperties[$relationshipKey];
1379   }
1380
1381   /**
1382    * Add the main return properties to the household merge properties if needed for merging.
1383    *
1384    * If we are using household merge we need to add these to the relationship properties to
1385    * be retrieved.
1386    */
1387   public function setHouseholdMergeReturnProperties() {
1388     if ($this->isMergeSameHousehold()) {
1389       $returnProperties = $this->getReturnProperties();
1390       $returnProperties = array_diff_key($returnProperties, array_fill_keys(['location_type', 'im_provider'], 1));
1391       foreach ($this->getHouseholdRelationshipTypes() as $householdRelationshipType) {
1392         $this->relationshipReturnProperties[$householdRelationshipType] = $returnProperties;
1393       }
1394     }
1395   }
1396
1397   /**
1398    * Get the default location fields to request.
1399    *
1400    * @return array
1401    */
1402   public function getValidLocationFields() {
1403     return [
1404       'street_address',
1405       'supplemental_address_1',
1406       'supplemental_address_2',
1407       'supplemental_address_3',
1408       'city',
1409       'postal_code',
1410       'postal_code_suffix',
1411       'geo_code_1',
1412       'geo_code_2',
1413       'state_province',
1414       'country',
1415       'phone',
1416       'email',
1417       'im',
1418     ];
1419   }
1420
1421   /**
1422    * Get the sql column definition for the given field.
1423    *
1424    * @param string $fieldName
1425    * @param string $columnName
1426    *
1427    * @return mixed
1428    */
1429   public function getSqlColumnDefinition($fieldName, $columnName) {
1430
1431     // early exit for master_id, CRM-12100
1432     // in the DB it is an ID, but in the export, we retrive the display_name of the master record
1433     // also for current_employer, CRM-16939
1434     if ($columnName == 'master_id' || $columnName == 'current_employer') {
1435       return "`$fieldName` varchar(128)";
1436     }
1437
1438     $queryFields = $this->getQueryFields();
1439     // @todo remove the enotice avoidance here, ensure all columns are declared.
1440     // tests will fail on the enotices until they all are & then all the 'else'
1441     // below can go.
1442     $fieldSpec = $queryFields[$columnName] ?? [];
1443
1444     // set the sql columns
1445     if (isset($fieldSpec['type'])) {
1446       switch ($fieldSpec['type']) {
1447         case CRM_Utils_Type::T_INT:
1448         case CRM_Utils_Type::T_BOOLEAN:
1449           if (in_array(CRM_Utils_Array::value('data_type', $fieldSpec), ['Country', 'StateProvince', 'ContactReference'])) {
1450             return "`$fieldName` varchar(255)";
1451           }
1452           return "`$fieldName` varchar(16)";
1453
1454         case CRM_Utils_Type::T_STRING:
1455           if (isset($queryFields[$columnName]['maxlength'])) {
1456             return "`$fieldName` varchar({$queryFields[$columnName]['maxlength']})";
1457           }
1458           else {
1459             return "`$fieldName` varchar(255)";
1460           }
1461
1462         case CRM_Utils_Type::T_TEXT:
1463         case CRM_Utils_Type::T_LONGTEXT:
1464         case CRM_Utils_Type::T_BLOB:
1465         case CRM_Utils_Type::T_MEDIUMBLOB:
1466           return "`$fieldName` longtext";
1467
1468         case CRM_Utils_Type::T_FLOAT:
1469         case CRM_Utils_Type::T_ENUM:
1470         case CRM_Utils_Type::T_DATE:
1471         case CRM_Utils_Type::T_TIME:
1472         case CRM_Utils_Type::T_TIMESTAMP:
1473         case CRM_Utils_Type::T_MONEY:
1474         case CRM_Utils_Type::T_EMAIL:
1475         case CRM_Utils_Type::T_URL:
1476         case CRM_Utils_Type::T_CCNUM:
1477         default:
1478           return "`$fieldName` varchar(32)";
1479       }
1480     }
1481     else {
1482       if (substr($fieldName, -3, 3) == '_id') {
1483         return "`$fieldName` varchar(255)";
1484       }
1485       elseif (substr($fieldName, -5, 5) == '_note') {
1486         return "`$fieldName` text";
1487       }
1488       else {
1489         $changeFields = [
1490           'groups',
1491           'tags',
1492           'notes',
1493         ];
1494
1495         if (in_array($fieldName, $changeFields)) {
1496           return "`$fieldName` text";
1497         }
1498         else {
1499           // set the sql columns for custom data
1500           if (isset($queryFields[$columnName]['data_type'])) {
1501
1502             switch ($queryFields[$columnName]['data_type']) {
1503               case 'String':
1504                 // May be option labels, which could be up to 512 characters
1505                 $length = max(512, CRM_Utils_Array::value('text_length', $queryFields[$columnName]));
1506                 return "`$fieldName` varchar($length)";
1507
1508               case 'Link':
1509                 return "`$fieldName` varchar(255)";
1510
1511               case 'Memo':
1512                 return "`$fieldName` text";
1513
1514               default:
1515                 return "`$fieldName` varchar(255)";
1516             }
1517           }
1518           else {
1519             return "`$fieldName` text";
1520           }
1521         }
1522       }
1523     }
1524   }
1525
1526   /**
1527    * Get the munged field name.
1528    *
1529    * @param string $field
1530    * @return string
1531    */
1532   public function getMungedFieldName($field) {
1533     $fieldName = CRM_Utils_String::munge(strtolower($field), '_', 64);
1534     if ($fieldName == 'id') {
1535       $fieldName = 'civicrm_primary_id';
1536     }
1537     return $fieldName;
1538   }
1539
1540   /**
1541    * In order to respect the history of this class we need to index kinda illogically.
1542    *
1543    * On the bright side - this stuff is tested within a nano-byte of it's life.
1544    *
1545    * e.g '2-a-b_Home-City'
1546    *
1547    * @param string $key
1548    * @param string $relationshipType
1549    * @param string $locationType
1550    * @param $entityLabel
1551    *
1552    * @return string
1553    */
1554   protected function getOutputSpecificationIndex($key, $relationshipType, $locationType, $entityLabel) {
1555     if ($entityLabel || $key === 'im') {
1556       // Just cos that's the history...
1557       if ($key !== 'master_id') {
1558         $key = $this->getHeaderForRow($key);
1559       }
1560     }
1561     if (!$relationshipType || $key !== 'id') {
1562       $key = $this->getMungedFieldName($key);
1563     }
1564     return $this->getMungedFieldName(
1565       ($relationshipType ? ($relationshipType . '_') : '')
1566       . ($locationType ? ($locationType . '_') : '')
1567       . $key
1568       . ($entityLabel ? ('_' . $entityLabel) : '')
1569     );
1570   }
1571
1572   /**
1573    * Get the compiled label for the column.
1574    *
1575    * e.g 'Gender', 'Employee Of-Home-city'
1576    *
1577    * @param string $key
1578    * @param string $relationshipType
1579    * @param string $locationType
1580    * @param string $entityLabel
1581    *
1582    * @return string
1583    */
1584   protected function getOutputSpecificationLabel($key, $relationshipType, $locationType, $entityLabel) {
1585     return ($relationshipType ? $this->getRelationshipTypes()[$relationshipType] . '-' : '')
1586       . ($locationType ? $locationType . '-' : '')
1587       . $this->getHeaderForRow($key)
1588       . ($entityLabel ? '-' . $entityLabel : '');
1589   }
1590
1591   /**
1592    * Get the mysql field name key.
1593    *
1594    * This key is locked in by tests but the reasons for the specific conventions -
1595    * ie. headings are used for keying fields in some cases, are likely
1596    * accidental rather than deliberate.
1597    *
1598    * This key is used for the output sql array.
1599    *
1600    * @param string $key
1601    * @param $relationshipType
1602    * @param $locationType
1603    * @param $entityLabel
1604    *
1605    * @return string
1606    */
1607   protected function getOutputSpecificationFieldKey($key, $relationshipType, $locationType, $entityLabel) {
1608     if ($entityLabel || $key === 'im') {
1609       if ($key !== 'state_province' && $key !== 'id') {
1610         // @todo - test removing this - indexing by $key should be fine...
1611         $key = $this->getHeaderForRow($key);
1612       }
1613     }
1614     if (!$relationshipType || $key !== 'id') {
1615       $key = $this->getMungedFieldName($key);
1616     }
1617     $fieldKey = $this->getMungedFieldName(
1618       ($relationshipType ? ($relationshipType . '_') : '')
1619       . ($locationType ? ($locationType . '_') : '')
1620       . $key
1621       . ($entityLabel ? ('_' . $entityLabel) : '')
1622     );
1623     return $fieldKey;
1624   }
1625
1626   /**
1627    * Get params for the where criteria.
1628    *
1629    * @return mixed
1630    */
1631   public function getWhereParams() {
1632     if (!$this->isPostalableOnly()) {
1633       return [];
1634     }
1635     $params['is_deceased'] = ['is_deceased', '=', 0, CRM_Contact_BAO_Query::MODE_CONTACTS];
1636     $params['do_not_mail'] = ['do_not_mail', '=', 0, CRM_Contact_BAO_Query::MODE_CONTACTS];
1637     return $params;
1638   }
1639
1640   /**
1641    * @param $row
1642    * @param $contactID
1643    * @param $value
1644    * @param $field
1645    */
1646   protected function buildRelationshipFieldsForRow(&$row, $contactID, $value, $field) {
1647     foreach (array_keys($value) as $property) {
1648       if ($property === 'location') {
1649         // @todo just undo all this nasty location wrangling!
1650         foreach ($value['location'] as $locationKey => $locationFields) {
1651           foreach (array_keys($locationFields) as $locationField) {
1652             $fieldKey = str_replace(' ', '_', $locationKey . '-' . $locationField);
1653             $row[$field . '_' . $fieldKey] = $this->getRelationshipValue($field, $contactID, $fieldKey);
1654           }
1655         }
1656       }
1657       else {
1658         $row[$field . '_' . $property] = $this->getRelationshipValue($field, $contactID, $property);
1659       }
1660     }
1661   }
1662
1663   /**
1664    * Is this contact a household that is already set to be exported by virtue of it's household members.
1665    *
1666    * @param int $contactID
1667    *
1668    * @return bool
1669    */
1670   protected function isHouseholdToSkip($contactID) {
1671     return in_array($contactID, $this->householdsToSkip);
1672   }
1673
1674   /**
1675    * Get the various arrays that we use to structure our output.
1676    *
1677    * The extraction of these has been moved to a separate function for clarity and so that
1678    * tests can be added - in particular on the $outputHeaders array.
1679    *
1680    * However it still feels a bit like something that I'm too polite to write down and this should be seen
1681    * as a step on the refactoring path rather than how it should be.
1682    *
1683    * @return array
1684    *   - outputColumns Array of columns to be exported. The values don't matter but the key must match the
1685    *   alias for the field generated by BAO_Query object.
1686    *   - headerRows Array of the column header strings to put in the csv header - non-associative.
1687    *   - sqlColumns Array of column names for the temp table. Not too sure why outputColumns can't be used here.
1688    *   - metadata Array of fields with specific parameters to pass to the translate function or another hacky nasty solution
1689    *    I'm too embarassed to discuss here.
1690    *    The keys need
1691    *    - to match the outputColumns keys (yes, the fact we ignore the output columns values & then pass another array with values
1692    *    we could use does suggest further refactors. However, you future improver, do remember that every check you do
1693    *    in the main DAO loop is done once per row & that coule be 100,000 times.)
1694    *    Finally a pop quiz: We need the translate context because we use a function other than ts() - is this because
1695    *    - a) the function used is more efficient or
1696    *    - b) this code is old & outdated. Submit your answers to circular bin or better
1697    *       yet find a way to comment them for posterity.
1698    */
1699   public function getExportStructureArrays() {
1700     $outputColumns = $metadata = [];
1701     $queryFields = $this->getQueryFields();
1702     foreach ($this->getReturnProperties() as $key => $value) {
1703       if (($key != 'location' || !is_array($value)) && !$this->isRelationshipTypeKey($key)) {
1704         $outputColumns[$key] = $value;
1705         $this->addOutputSpecification($key);
1706       }
1707       elseif ($this->isRelationshipTypeKey($key)) {
1708         $outputColumns[$key] = $value;
1709         foreach ($value as $relationField => $relationValue) {
1710           // below block is same as primary block (duplicate)
1711           if (isset($queryFields[$relationField]['title'])) {
1712             $this->addOutputSpecification($relationField, $key);
1713           }
1714           elseif (is_array($relationValue) && $relationField == 'location') {
1715             // fix header for location type case
1716             foreach ($relationValue as $ltype => $val) {
1717               foreach (array_keys($val) as $fld) {
1718                 $type = explode('-', $fld);
1719                 $this->addOutputSpecification($type[0], $key, $ltype, CRM_Utils_Array::value(1, $type));
1720               }
1721             }
1722           }
1723         }
1724       }
1725       else {
1726         foreach ($value as $locationType => $locationFields) {
1727           foreach (array_keys($locationFields) as $locationFieldName) {
1728             $type = explode('-', $locationFieldName);
1729
1730             $actualDBFieldName = $type[0];
1731             $daoFieldName = CRM_Utils_String::munge($locationType) . '-' . $actualDBFieldName;
1732
1733             if (!empty($type[1])) {
1734               $daoFieldName .= "-" . $type[1];
1735             }
1736             $this->addOutputSpecification($actualDBFieldName, NULL, $locationType, CRM_Utils_Array::value(1, $type));
1737             $metadata[$daoFieldName] = $this->getMetaDataForField($actualDBFieldName);
1738             $outputColumns[$daoFieldName] = TRUE;
1739           }
1740         }
1741       }
1742     }
1743     return [$outputColumns, $metadata];
1744   }
1745
1746   /**
1747    * Get default return property for export based on mode
1748    *
1749    * @return string
1750    *   Default Return property
1751    */
1752   public function defaultReturnProperty() {
1753     // hack to add default return property based on export mode
1754     $property = NULL;
1755     $exportMode = $this->getExportMode();
1756     if ($exportMode == CRM_Export_Form_Select::CONTRIBUTE_EXPORT) {
1757       $property = 'contribution_id';
1758     }
1759     elseif ($exportMode == CRM_Export_Form_Select::EVENT_EXPORT) {
1760       $property = 'participant_id';
1761     }
1762     elseif ($exportMode == CRM_Export_Form_Select::MEMBER_EXPORT) {
1763       $property = 'membership_id';
1764     }
1765     elseif ($exportMode == CRM_Export_Form_Select::PLEDGE_EXPORT) {
1766       $property = 'pledge_id';
1767     }
1768     elseif ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) {
1769       $property = 'case_id';
1770     }
1771     elseif ($exportMode == CRM_Export_Form_Select::GRANT_EXPORT) {
1772       $property = 'grant_id';
1773     }
1774     elseif ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) {
1775       $property = 'activity_id';
1776     }
1777     return $property;
1778   }
1779
1780   /**
1781    * Determine the required return properties from the input parameters.
1782    *
1783    * @return array
1784    */
1785   public function determineReturnProperties() {
1786     if ($this->getRequestedFields()) {
1787       $returnProperties = [];
1788       foreach ($this->getRequestedFields() as $key => $value) {
1789         $fieldName = $value['name'];
1790         $locationName = !empty($value['location_type_id']) ? CRM_Core_PseudoConstant::getName('CRM_Core_BAO_Address', 'location_type_id', $value['location_type_id']) : NULL;
1791         $relationshipTypeKey = !empty($value['relationship_type_id']) ? $value['relationship_type_id'] . '_' . $value['relationship_direction'] : NULL;
1792         if (!$fieldName || $this->isHouseholdMergeRelationshipTypeKey($relationshipTypeKey)) {
1793           continue;
1794         }
1795
1796         if ($this->isRelationshipTypeKey($relationshipTypeKey)) {
1797           $returnProperties[$relationshipTypeKey] = $this->setRelationshipReturnProperties($value, $relationshipTypeKey);
1798         }
1799         elseif ($locationName) {
1800           if ($fieldName === 'phone') {
1801             $returnProperties['location'][$locationName]['phone-' . $value['phone_type_id'] ?? NULL] = 1;
1802           }
1803           elseif ($fieldName === 'im') {
1804             $returnProperties['location'][$locationName]['im-' . $value['im_provider_id'] ?? NULL] = 1;
1805           }
1806           else {
1807             $returnProperties['location'][$locationName][$fieldName] = 1;
1808           }
1809         }
1810         else {
1811           //hack to fix component fields
1812           //revert mix of event_id and title
1813           if ($fieldName == 'event_id') {
1814             $returnProperties['event_id'] = 1;
1815           }
1816           else {
1817             $returnProperties[$fieldName] = 1;
1818           }
1819         }
1820       }
1821       $defaultExportMode = $this->defaultReturnProperty();
1822       if ($defaultExportMode) {
1823         $returnProperties[$defaultExportMode] = 1;
1824       }
1825     }
1826     else {
1827       $returnProperties = $this->getDefaultReturnProperties();
1828     }
1829     if ($this->isMergeSameHousehold()) {
1830       $returnProperties['id'] = 1;
1831     }
1832     if ($this->isMergeSameAddress()) {
1833       $returnProperties['addressee'] = 1;
1834       $returnProperties['postal_greeting'] = 1;
1835       $returnProperties['email_greeting'] = 1;
1836       $returnProperties['street_name'] = 1;
1837       $returnProperties['household_name'] = 1;
1838       $returnProperties['street_address'] = 1;
1839       $returnProperties['city'] = 1;
1840       $returnProperties['state_province'] = 1;
1841
1842     }
1843     return $returnProperties;
1844   }
1845
1846   /**
1847    * @param object $query
1848    *   CRM_Contact_BAO_Query
1849    *
1850    * @return string
1851    *   Group By Clause
1852    */
1853   public function getGroupBy($query) {
1854     $groupBy = NULL;
1855     $returnProperties = $this->getReturnProperties();
1856     $exportMode = $this->getExportMode();
1857     $queryMode = $this->getQueryMode();
1858     if (!empty($returnProperties['tags']) || !empty($returnProperties['groups']) ||
1859       CRM_Utils_Array::value('notes', $returnProperties) ||
1860       // CRM-9552
1861       ($queryMode & CRM_Contact_BAO_Query::MODE_CONTACTS && $query->_useGroupBy)
1862     ) {
1863       $groupBy = "contact_a.id";
1864     }
1865
1866     switch ($exportMode) {
1867       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
1868         $groupBy = 'civicrm_contribution.id';
1869         if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
1870           // especial group by  when soft credit columns are included
1871           $groupBy = ['contribution_search_scredit_combined.id', 'contribution_search_scredit_combined.scredit_id'];
1872         }
1873         break;
1874
1875       case CRM_Export_Form_Select::EVENT_EXPORT:
1876         $groupBy = 'civicrm_participant.id';
1877         break;
1878
1879       case CRM_Export_Form_Select::MEMBER_EXPORT:
1880         $groupBy = "civicrm_membership.id";
1881         break;
1882     }
1883
1884     if ($queryMode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
1885       $groupBy = "civicrm_activity.id ";
1886     }
1887
1888     return $groupBy ? ' GROUP BY ' . implode(', ', (array) $groupBy) : '';
1889   }
1890
1891   /**
1892    * @param int $contactId
1893    *
1894    * @return array
1895    */
1896   public function replaceMergeTokens($contactId) {
1897     $greetings = [];
1898     $contact = NULL;
1899
1900     $greetingFields = [
1901       'postal_greeting' => $this->getPostalGreetingTemplate(),
1902       'addressee' => $this->getAddresseeGreetingTemplate(),
1903     ];
1904     foreach ($greetingFields as $greeting => $greetingLabel) {
1905       $tokens = CRM_Utils_Token::getTokens($greetingLabel);
1906       if (!empty($tokens)) {
1907         if (empty($contact)) {
1908           $values = [
1909             'id' => $contactId,
1910             'version' => 3,
1911           ];
1912           $contact = civicrm_api('contact', 'get', $values);
1913
1914           if (!empty($contact['is_error'])) {
1915             return $greetings;
1916           }
1917           $contact = $contact['values'][$contact['id']];
1918         }
1919
1920         $tokens = ['contact' => $greetingLabel];
1921         $greetings[$greeting] = CRM_Utils_Token::replaceContactTokens($greetingLabel, $contact, NULL, $tokens);
1922       }
1923     }
1924     return $greetings;
1925   }
1926
1927   /**
1928    * Build array for merging same addresses.
1929    *
1930    * @param string $sql
1931    */
1932   public function buildMasterCopyArray($sql) {
1933
1934     $parents = [];
1935     $dao = CRM_Core_DAO::executeQuery($sql);
1936
1937     while ($dao->fetch()) {
1938       $masterID = $dao->master_id;
1939       $copyID = $dao->copy_id;
1940
1941       $this->cacheContactGreetings((int) $dao->master_contact_id);
1942       $this->cacheContactGreetings((int) $dao->copy_contact_id);
1943
1944       if (!isset($this->contactsToMerge[$masterID])) {
1945         // check if this is an intermediate child
1946         // this happens if there are 3 or more matches a,b, c
1947         // the above query will return a, b / a, c / b, c
1948         // we might be doing a bit more work, but for now its ok, unless someone
1949         // knows how to fix the query above
1950         if (isset($parents[$masterID])) {
1951           $masterID = $parents[$masterID];
1952         }
1953         else {
1954           $this->contactsToMerge[$masterID] = [
1955             'addressee' => $this->getContactGreeting((int) $dao->master_contact_id, 'addressee', $dao->master_addressee),
1956             'copy' => [],
1957             'postalGreeting' => $this->getContactGreeting((int) $dao->master_contact_id, 'postal_greeting', $dao->master_postal_greeting),
1958           ];
1959           $this->contactsToMerge[$masterID]['emailGreeting'] = &$this->contactsToMerge[$masterID]['postalGreeting'];
1960         }
1961       }
1962       $parents[$copyID] = $masterID;
1963
1964       if (!array_key_exists($copyID, $this->contactsToMerge[$masterID]['copy'])) {
1965         $copyPostalGreeting = $this->getContactPortionOfGreeting((int) $dao->copy_contact_id, (int) $dao->copy_postal_greeting_id, 'postal_greeting', $dao->copy_postal_greeting);
1966         if ($copyPostalGreeting) {
1967           $this->contactsToMerge[$masterID]['postalGreeting'] = "{$this->contactsToMerge[$masterID]['postalGreeting']}, {$copyPostalGreeting}";
1968           // if there happens to be a duplicate, remove it
1969           $this->contactsToMerge[$masterID]['postalGreeting'] = str_replace(" {$copyPostalGreeting},", "", $this->contactsToMerge[$masterID]['postalGreeting']);
1970         }
1971
1972         $copyAddressee = $this->getContactPortionOfGreeting((int) $dao->copy_contact_id, (int) $dao->copy_addressee_id, 'addressee', $dao->copy_addressee);
1973         if ($copyAddressee) {
1974           $this->contactsToMerge[$masterID]['addressee'] = "{$this->contactsToMerge[$masterID]['addressee']}, " . trim($copyAddressee);
1975         }
1976       }
1977       if (!isset($this->contactsToMerge[$masterID]['copy'][$copyID])) {
1978         // If it was set in the first run through - share routine, don't subsequently clobber.
1979         $this->contactsToMerge[$masterID]['copy'][$copyID] = $copyAddressee ?? $dao->copy_addressee;
1980       }
1981     }
1982   }
1983
1984   /**
1985    * Merge contacts with the same address.
1986    */
1987   public function mergeSameAddress() {
1988
1989     $tableName = $this->getTemporaryTable();
1990
1991     // find all the records that have the same street address BUT not in a household
1992     // require match on city and state as well
1993     $sql = "
1994 SELECT    r1.id                 as master_id,
1995           r1.civicrm_primary_id as master_contact_id,
1996           r1.postal_greeting    as master_postal_greeting,
1997           r1.postal_greeting_id as master_postal_greeting_id,
1998           r1.addressee          as master_addressee,
1999           r1.addressee_id       as master_addressee_id,
2000           r2.id                 as copy_id,
2001           r2.civicrm_primary_id as copy_contact_id,
2002           r2.postal_greeting    as copy_postal_greeting,
2003           r2.postal_greeting_id as copy_postal_greeting_id,
2004           r2.addressee          as copy_addressee,
2005           r2.addressee_id       as copy_addressee_id
2006 FROM      $tableName r1
2007 LEFT JOIN $tableName r2 ON ( r1.street_address = r2.street_address AND
2008           r1.city = r2.city AND
2009           r1.state_province_id = r2.state_province_id )
2010 WHERE ( r1.street_address != '' )
2011 AND       r2.id > r1.id
2012 ORDER BY  r1.id
2013 ";
2014     $this->buildMasterCopyArray($sql);
2015
2016     foreach ($this->contactsToMerge as $masterID => $values) {
2017       $sql = "
2018 UPDATE $tableName
2019 SET    addressee = %1, postal_greeting = %2, email_greeting = %3
2020 WHERE  id = %4
2021 ";
2022       $params = [
2023         1 => [$values['addressee'], 'String'],
2024         2 => [$values['postalGreeting'], 'String'],
2025         3 => [$values['emailGreeting'], 'String'],
2026         4 => [$masterID, 'Integer'],
2027       ];
2028       CRM_Core_DAO::executeQuery($sql, $params);
2029
2030       // delete all copies
2031       $deleteIDs = array_keys($values['copy']);
2032       $deleteIDString = implode(',', $deleteIDs);
2033       $sql = "
2034 DELETE FROM $tableName
2035 WHERE  id IN ( $deleteIDString )
2036 ";
2037       CRM_Core_DAO::executeQuery($sql);
2038     }
2039   }
2040
2041   /**
2042    * The function unsets static part of the string, if token is the dynamic part.
2043    *
2044    * Example: 'Hello {contact.first_name}' => converted to => '{contact.first_name}'
2045    * i.e 'Hello Alan' => converted to => 'Alan'
2046    *
2047    * @param string $parsedString
2048    * @param string $defaultGreeting
2049    * @param string $greetingLabel
2050    *
2051    * @return mixed
2052    */
2053   public function trimNonTokensFromAddressString(
2054     &$parsedString, $defaultGreeting,
2055     $greetingLabel
2056   ) {
2057     $greetingLabel = empty($greetingLabel) ? $defaultGreeting : $greetingLabel;
2058
2059     $stringsToBeReplaced = preg_replace('/(\{[a-zA-Z._ ]+\})/', ';;', $greetingLabel);
2060     $stringsToBeReplaced = explode(';;', $stringsToBeReplaced);
2061     foreach ($stringsToBeReplaced as $key => $string) {
2062       // to keep one space
2063       $stringsToBeReplaced[$key] = ltrim($string);
2064     }
2065     $parsedString = str_replace($stringsToBeReplaced, "", $parsedString);
2066
2067     return $parsedString;
2068   }
2069
2070   /**
2071    * Preview export output.
2072    *
2073    * @param int $limit
2074    * @return array
2075    */
2076   public function getPreview($limit) {
2077     $rows = [];
2078     list($outputColumns, $metadata) = $this->getExportStructureArrays();
2079     $query = $this->runQuery([], '');
2080     CRM_Core_DAO::disableFullGroupByMode();
2081     $result = CRM_Core_DAO::executeQuery($query[1] . ' LIMIT ' . (int) $limit);
2082     CRM_Core_DAO::reenableFullGroupByMode();
2083     while ($result->fetch()) {
2084       $rows[] = $this->buildRow($query[0], $result, $outputColumns, $metadata, [], []);
2085     }
2086     return $rows;
2087   }
2088
2089   /**
2090    * Set the template strings to be used when merging two contacts with the same address.
2091    *
2092    * @param array $formValues
2093    *   Values from first form. In this case we care about the keys
2094    *   - postal_greeting
2095    *   - postal_other
2096    *   - address_greeting
2097    *   - addressee_other
2098    *
2099    * @return mixed
2100    */
2101   protected function setGreetingStringsForSameAddressMerge($formValues) {
2102     $greetingOptions = CRM_Export_Form_Select::getGreetingOptions();
2103
2104     if (!empty($greetingOptions)) {
2105       // Greeting options is keyed by 'postal_greeting' or 'addressee'.
2106       foreach ($greetingOptions as $key => $value) {
2107         $option = $formValues[$key] ?? NULL;
2108         if ($option) {
2109           if ($greetingOptions[$key][$option] == ts('Other')) {
2110             $formValues[$key] = $formValues["{$key}_other"];
2111           }
2112           elseif ($greetingOptions[$key][$option] == ts('List of names')) {
2113             $formValues[$key] = '';
2114           }
2115           else {
2116             $formValues[$key] = $greetingOptions[$key][$option];
2117           }
2118         }
2119       }
2120     }
2121     if (!empty($formValues['postal_greeting'])) {
2122       $this->setPostalGreetingTemplate($formValues['postal_greeting']);
2123     }
2124     if (!empty($formValues['addressee'])) {
2125       $this->setAddresseeGreetingTemplate($formValues['addressee']);
2126     }
2127   }
2128
2129   /**
2130    * Create the temporary table for output.
2131    */
2132   public function createTempTable() {
2133     //creating a temporary table for the search result that need be exported
2134     $exportTempTable = CRM_Utils_SQL_TempTable::build()->setDurable()->setCategory('export');
2135     $sqlColumns = $this->getSQLColumns();
2136     // also create the sql table
2137     $exportTempTable->drop();
2138
2139     $sql = " id int unsigned NOT NULL AUTO_INCREMENT, ";
2140     if (!empty($sqlColumns)) {
2141       $sql .= implode(",\n", array_values($sqlColumns)) . ',';
2142     }
2143
2144     $sql .= "\n PRIMARY KEY ( id )";
2145
2146     // add indexes for street_address and household_name if present
2147     $addIndices = [
2148       'street_address',
2149       'household_name',
2150       'civicrm_primary_id',
2151     ];
2152
2153     foreach ($addIndices as $index) {
2154       if (isset($sqlColumns[$index])) {
2155         $sql .= ",
2156   INDEX index_{$index}( $index )
2157 ";
2158       }
2159     }
2160
2161     $exportTempTable->createWithColumns($sql);
2162     $this->setTemporaryTable($exportTempTable->getName());
2163   }
2164
2165   /**
2166    * Get the values of linked household contact.
2167    *
2168    * @param CRM_Core_DAO $relDAO
2169    * @param array $value
2170    * @param string $field
2171    * @param array $row
2172    *
2173    * @throws \Exception
2174    */
2175   public function fetchRelationshipDetails($relDAO, $value, $field, &$row) {
2176     $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id');
2177     $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id');
2178     $i18n = CRM_Core_I18n::singleton();
2179     $field = $field . '_';
2180
2181     foreach ($value as $relationField => $relationValue) {
2182       if (is_object($relDAO) && property_exists($relDAO, $relationField)) {
2183         $fieldValue = $relDAO->$relationField;
2184         if ($relationField == 'phone_type_id') {
2185           $fieldValue = $phoneTypes[$relationValue];
2186         }
2187         elseif ($relationField == 'provider_id') {
2188           $fieldValue = $imProviders[$relationValue] ?? NULL;
2189         }
2190         // CRM-13995
2191         elseif (is_object($relDAO) && in_array($relationField, [
2192           'email_greeting',
2193           'postal_greeting',
2194           'addressee',
2195         ])) {
2196           //special case for greeting replacement
2197           $fldValue = "{$relationField}_display";
2198           $fieldValue = $relDAO->$fldValue;
2199         }
2200       }
2201       elseif (is_object($relDAO) && $relationField == 'state_province') {
2202         $fieldValue = CRM_Core_PseudoConstant::stateProvince($relDAO->state_province_id);
2203       }
2204       elseif (is_object($relDAO) && $relationField == 'country') {
2205         $fieldValue = CRM_Core_PseudoConstant::country($relDAO->country_id);
2206       }
2207       else {
2208         $fieldValue = '';
2209       }
2210       $relPrefix = $field . $relationField;
2211
2212       if (is_object($relDAO) && $relationField == 'id') {
2213         $row[$relPrefix] = $relDAO->contact_id;
2214       }
2215       elseif (is_array($relationValue) && $relationField == 'location') {
2216         foreach ($relationValue as $ltype => $val) {
2217           // If the location name has a space in it the we need to handle that. This
2218           // is kinda hacky but specifically covered in the ExportTest so later efforts to
2219           // improve it should be secure in the knowled it will be caught.
2220           $ltype = str_replace(' ', '_', $ltype);
2221           foreach (array_keys($val) as $fld) {
2222             $type = explode('-', $fld);
2223             $fldValue = "{$ltype}-" . $type[0];
2224             if (!empty($type[1])) {
2225               $fldValue .= "-" . $type[1];
2226             }
2227             // CRM-3157: localise country, region (both have ‘country’ context)
2228             // and state_province (‘province’ context)
2229             switch (TRUE) {
2230               case (!is_object($relDAO)):
2231                 $row[$field . '_' . $fldValue] = '';
2232                 break;
2233
2234               case in_array('country', $type):
2235               case in_array('world_region', $type):
2236                 $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue,
2237                   ['context' => 'country']
2238                 );
2239                 break;
2240
2241               case in_array('state_province', $type):
2242                 $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue,
2243                   ['context' => 'province']
2244                 );
2245                 break;
2246
2247               default:
2248                 $row[$field . '_' . $fldValue] = $relDAO->$fldValue;
2249                 break;
2250             }
2251           }
2252         }
2253       }
2254       elseif (isset($fieldValue) && $fieldValue != '') {
2255         //check for custom data
2256         if ($cfID = CRM_Core_BAO_CustomField::getKeyID($relationField)) {
2257           $row[$relPrefix] = CRM_Core_BAO_CustomField::displayValue($fieldValue, $cfID);
2258         }
2259         else {
2260           //normal relationship fields
2261           // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context)
2262           switch ($relationField) {
2263             case 'country':
2264             case 'world_region':
2265               $row[$relPrefix] = $i18n->crm_translate($fieldValue, ['context' => 'country']);
2266               break;
2267
2268             case 'state_province':
2269               $row[$relPrefix] = $i18n->crm_translate($fieldValue, ['context' => 'province']);
2270               break;
2271
2272             default:
2273               $row[$relPrefix] = $fieldValue;
2274               break;
2275           }
2276         }
2277       }
2278       else {
2279         // if relation field is empty or null
2280         $row[$relPrefix] = '';
2281       }
2282     }
2283   }
2284
2285   /**
2286    * Write to the csv from the temp table.
2287    */
2288   public function writeCSVFromTable() {
2289     // call export hook
2290     $headerRows = $this->getHeaderRows();
2291     $exportTempTable = $this->getTemporaryTable();
2292     $exportMode = $this->getExportMode();
2293     $sqlColumns = $this->getSQLColumns();
2294     $componentTable = $this->getComponentTable();
2295     $ids = $this->getIds();
2296     CRM_Utils_Hook::export($exportTempTable, $headerRows, $sqlColumns, $exportMode, $componentTable, $ids);
2297     if ($exportMode !== $this->getExportMode() || $componentTable !== $this->getComponentTable()) {
2298       CRM_Core_Error::deprecatedFunctionWarning('altering the export mode and/or component table in the hook is no longer supported.');
2299     }
2300     if ($ids !== $this->getIds()) {
2301       CRM_Core_Error::deprecatedFunctionWarning('altering the ids in the hook is no longer supported.');
2302     }
2303     if ($exportTempTable !== $this->getTemporaryTable()) {
2304       CRM_Core_Error::deprecatedFunctionWarning('altering the export table in the hook is deprecated (in some flows the table itself will be)');
2305       $this->setTemporaryTable($exportTempTable);
2306     }
2307     $exportTempTable = $this->getTemporaryTable();
2308     $writeHeader = TRUE;
2309     $offset = 0;
2310     // increase this number a lot to avoid making too many queries
2311     // LIMIT is not much faster than a no LIMIT query
2312     // CRM-7675
2313     $limit = 100000;
2314
2315     $query = "SELECT * FROM $exportTempTable";
2316
2317     $this->instantiateTempTable($headerRows);
2318     while (1) {
2319       $limitQuery = $query . "
2320 LIMIT $offset, $limit
2321 ";
2322       $dao = CRM_Core_DAO::executeQuery($limitQuery);
2323
2324       if ($dao->N <= 0) {
2325         break;
2326       }
2327
2328       $componentDetails = [];
2329       while ($dao->fetch()) {
2330         $row = [];
2331
2332         foreach (array_keys($sqlColumns) as $column) {
2333           $row[$column] = $dao->$column;
2334         }
2335         $componentDetails[] = $row;
2336       }
2337       $this->writeRows($headerRows, $componentDetails);
2338
2339       $offset += $limit;
2340     }
2341   }
2342
2343   /**
2344    * Set up the temp table.
2345    *
2346    * @param array $headerRows
2347    */
2348   protected function instantiateTempTable(array $headerRows) {
2349     CRM_Utils_System::download(CRM_Utils_String::munge($this->getExportFileName()),
2350       'text/x-csv',
2351       CRM_Core_DAO::$_nullObject,
2352       'csv',
2353       FALSE
2354     );
2355     // Output UTF BOM so that MS Excel copes with diacritics. This is recommended as
2356     // the Windows variant but is tested with MS Excel for Mac (Office 365 v 16.31)
2357     // and it continues to work on Libre Office, Numbers, Notes etc.
2358     echo "\xEF\xBB\xBF";
2359     CRM_Core_Report_Excel::makeCSVTable($headerRows, [], TRUE);
2360   }
2361
2362   /**
2363    * Write rows to the csv.
2364    *
2365    * @param array $headerRows
2366    * @param array $rows
2367    */
2368   protected function writeRows(array $headerRows, array $rows) {
2369     if (!empty($rows)) {
2370       CRM_Core_Report_Excel::makeCSVTable($headerRows, $rows, FALSE);
2371     }
2372   }
2373
2374   /**
2375    * Cache the greeting fields for the given contact.
2376    *
2377    * @param int $contactID
2378    */
2379   protected function cacheContactGreetings(int $contactID) {
2380     if (!isset($this->contactGreetingFields[$contactID])) {
2381       $this->contactGreetingFields[$contactID] = $this->replaceMergeTokens($contactID);
2382     }
2383   }
2384
2385   /**
2386    * Get the greeting value for the given contact.
2387    *
2388    * The values have already been cached so we are grabbing the value at this point.
2389    *
2390    * @param int $contactID
2391    * @param string $type
2392    *   postal_greeting|addressee|email_greeting
2393    * @param string $default
2394    *
2395    * @return string
2396    */
2397   protected function getContactGreeting(int $contactID, string $type, string $default) {
2398     return CRM_Utils_Array::value($type,
2399       $this->contactGreetingFields[$contactID], $default
2400     );
2401   }
2402
2403   /**
2404    * Get the portion of the greeting string that relates to the contact.
2405    *
2406    * For example if the greeting id 'Dear Sarah' we are going to combine it with 'Dear Mike'
2407    * so we want to strip the 'Dear ' and just get 'Sarah
2408    * @param int $contactID
2409    * @param int $greetingID
2410    * @param string $type
2411    *   postal_greeting, addressee (email_greeting not currently implemented for unknown reasons.
2412    * @param string $defaultGreeting
2413    *
2414    * @return mixed|string
2415    */
2416   protected function getContactPortionOfGreeting(int $contactID, int $greetingID, string $type, string $defaultGreeting) {
2417     $copyPostalGreeting = $this->getContactGreeting($contactID, $type, $defaultGreeting);
2418     $template = $type === 'postal_greeting' ? $this->getPostalGreetingTemplate() : $this->getAddresseeGreetingTemplate();
2419     if ($copyPostalGreeting) {
2420       $copyPostalGreeting = $this->trimNonTokensFromAddressString($copyPostalGreeting,
2421         $this->greetingOptions[$type][$greetingID],
2422         $template
2423       );
2424     }
2425     return $copyPostalGreeting;
2426   }
2427
2428 }