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