CRM/Export/BAO/ExportProcessor.php

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