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    * Name of a temporary table created to hold the results.
 268    *
 269    * Current decision making on when to create a temp table is kinda bad so this might change
 270    * a bit as it is reviewed but basically we need a temp table or similar to calculate merging
 271    * addresses. Merging households is handled in php. We create a temp table even when we don't need them.
 272    *
 273    * @var string
 274    */
 275   protected $temporaryTable;
 276
 277   /**
 278    * @return string
 279    */
 280   public function getTemporaryTable(): string {
 281     return $this->temporaryTable;
 282   }
 283
 284   /**
 285    * @param string $temporaryTable
 286    */
 287   public function setTemporaryTable(string $temporaryTable) {
 288     $this->temporaryTable = $temporaryTable;
 289   }
 290
 291   /**
 292    * CRM_Export_BAO_ExportProcessor constructor.
 293    *
 294    * @param int $exportMode
 295    * @param array|null $requestedFields
 296    * @param string $queryOperator
 297    * @param bool $isMergeSameHousehold
 298    * @param bool $isPostalableOnly
 299    * @param bool $isMergeSameAddress
 300    */
 301   public function __construct($exportMode, $requestedFields, $queryOperator, $isMergeSameHousehold = FALSE, $isPostalableOnly = FALSE, $isMergeSameAddress = FALSE) {
 302     $this->setExportMode($exportMode);
 303     $this->setQueryMode();
 304     $this->setQueryOperator($queryOperator);
 305     $this->setRequestedFields($requestedFields);
 306     $this->setRelationshipTypes();
 307     $this->setIsMergeSameHousehold($isMergeSameHousehold);
 308     $this->setIsPostalableOnly($isPostalableOnly);
 309     $this->setIsMergeSameAddress($isMergeSameAddress);
 310     $this->setReturnProperties($this->determineReturnProperties());
 311     $this->setAdditionalFieldsForSameAddressMerge();
 312     $this->setAdditionalFieldsForPostalExport();
 313     $this->setHouseholdMergeReturnProperties();
 314   }
 315
 316   /**
 317    * @return bool
 318    */
 319   public function isPostalableOnly() {
 320     return $this->isPostalableOnly;
 321   }
 322
 323   /**
 324    * @param bool $isPostalableOnly
 325    */
 326   public function setIsPostalableOnly($isPostalableOnly) {
 327     $this->isPostalableOnly = $isPostalableOnly;
 328   }
 329
 330   /**
 331    * @return array|null
 332    */
 333   public function getRequestedFields() {
 334     return empty($this->requestedFields) ? NULL : $this->requestedFields;
 335   }
 336
 337   /**
 338    * @param array|null $requestedFields
 339    */
 340   public function setRequestedFields($requestedFields) {
 341     $this->requestedFields = $requestedFields;
 342   }
 343
 344   /**
 345    * @return array
 346    */
 347   public function getReturnProperties() {
 348     return array_merge($this->returnProperties, $this->getAdditionalRequestedReturnProperties(), $this->getAdditionalFieldsForSameAddressMerge(), $this->getAdditionalFieldsForPostalExport());
 349   }
 350
 351   /**
 352    * @param array $returnProperties
 353    */
 354   public function setReturnProperties($returnProperties) {
 355     $this->returnProperties = $returnProperties;
 356   }
 357
 358   /**
 359    * @return array
 360    */
 361   public function getRelationshipTypes() {
 362     return $this->relationshipTypes;
 363   }
 364
 365   /**
 366    */
 367   public function setRelationshipTypes() {
 368     $this->relationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(
 369       NULL,
 370       NULL,
 371       NULL,
 372       NULL,
 373       TRUE,
 374       'name',
 375       FALSE
 376     );
 377   }
 378
 379   /**
 380    * Set the value for a relationship type field.
 381    *
 382    * In this case we are building up an array of properties for a related contact.
 383    *
 384    * These may be used for direct exporting or for merge to household depending on the
 385    * options selected.
 386    *
 387    * @param string $relationshipType
 388    * @param int $contactID
 389    * @param string $field
 390    * @param string $value
 391    */
 392   public function setRelationshipValue($relationshipType, $contactID, $field, $value) {
 393     $this->relatedContactValues[$relationshipType][$contactID][$field] = $value;
 394     if ($field === 'id') {
 395       $this->householdsToSkip[] = $value;
 396     }
 397   }
 398
 399   /**
 400    * Get the value for a relationship type field.
 401    *
 402    * In this case we are building up an array of properties for a related contact.
 403    *
 404    * These may be used for direct exporting or for merge to household depending on the
 405    * options selected.
 406    *
 407    * @param string $relationshipType
 408    * @param int $contactID
 409    * @param string $field
 410    *
 411    * @return string
 412    */
 413   public function getRelationshipValue($relationshipType, $contactID, $field) {
 414     return isset($this->relatedContactValues[$relationshipType][$contactID][$field]) ? $this->relatedContactValues[$relationshipType][$contactID][$field] : '';
 415   }
 416
 417   /**
 418    * Get the id of the related household.
 419    *
 420    * @param int $contactID
 421    * @param string $relationshipType
 422    *
 423    * @return int
 424    */
 425   public function getRelatedHouseholdID($contactID, $relationshipType) {
 426     return $this->relatedContactValues[$relationshipType][$contactID]['id'];
 427   }
 428
 429   /**
 430    * Has the household already been exported.
 431    *
 432    * @param int $housholdContactID
 433    *
 434    * @return bool
 435    */
 436   public function isHouseholdExported($housholdContactID) {
 437     return isset($this->exportedHouseholds[$housholdContactID]);
 438
 439   }
 440
 441   /**
 442    * @return bool
 443    */
 444   public function isMergeSameHousehold() {
 445     return $this->isMergeSameHousehold;
 446   }
 447
 448   /**
 449    * @param bool $isMergeSameHousehold
 450    */
 451   public function setIsMergeSameHousehold($isMergeSameHousehold) {
 452     $this->isMergeSameHousehold = $isMergeSameHousehold;
 453   }
 454
 455   /**
 456    * Return relationship types for household merge.
 457    *
 458    * @return mixed
 459    */
 460   public function getHouseholdRelationshipTypes() {
 461     if (!$this->isMergeSameHousehold()) {
 462       return [];
 463     }
 464     return [
 465       CRM_Utils_Array::key('Household Member of', $this->getRelationshipTypes()),
 466       CRM_Utils_Array::key('Head of Household for', $this->getRelationshipTypes()),
 467     ];
 468   }
 469
 470   /**
 471    * @param $fieldName
 472    * @return bool
 473    */
 474   public function isRelationshipTypeKey($fieldName) {
 475     return array_key_exists($fieldName, $this->relationshipTypes);
 476   }
 477
 478   /**
 479    * @param $fieldName
 480    * @return bool
 481    */
 482   public function isHouseholdMergeRelationshipTypeKey($fieldName) {
 483     return in_array($fieldName, $this->getHouseholdRelationshipTypes());
 484   }
 485
 486   /**
 487    * @return string
 488    */
 489   public function getQueryOperator() {
 490     return $this->queryOperator;
 491   }
 492
 493   /**
 494    * @param string $queryOperator
 495    */
 496   public function setQueryOperator($queryOperator) {
 497     $this->queryOperator = $queryOperator;
 498   }
 499
 500   /**
 501    * @return array
 502    */
 503   public function getQueryFields() {
 504     return $this->queryFields;
 505   }
 506
 507   /**
 508    * @param array $queryFields
 509    */
 510   public function setQueryFields($queryFields) {
 511     // legacy hacks - we add these to queryFields because this
 512     // pseudometadata is currently required.
 513     $queryFields['im_provider']['pseudoconstant']['var'] = 'imProviders';
 514     $queryFields['country']['context'] = 'country';
 515     $queryFields['world_region']['context'] = 'country';
 516     $queryFields['state_province']['context'] = 'province';
 517     $this->queryFields = $queryFields;
 518   }
 519
 520   /**
 521    * @return int
 522    */
 523   public function getQueryMode() {
 524     return $this->queryMode;
 525   }
 526
 527   /**
 528    * Set the query mode based on the export mode.
 529    */
 530   public function setQueryMode() {
 531
 532     switch ($this->getExportMode()) {
 533       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
 534         $this->queryMode = CRM_Contact_BAO_Query::MODE_CONTRIBUTE;
 535         break;
 536
 537       case CRM_Export_Form_Select::EVENT_EXPORT:
 538         $this->queryMode = CRM_Contact_BAO_Query::MODE_EVENT;
 539         break;
 540
 541       case CRM_Export_Form_Select::MEMBER_EXPORT:
 542         $this->queryMode = CRM_Contact_BAO_Query::MODE_MEMBER;
 543         break;
 544
 545       case CRM_Export_Form_Select::PLEDGE_EXPORT:
 546         $this->queryMode = CRM_Contact_BAO_Query::MODE_PLEDGE;
 547         break;
 548
 549       case CRM_Export_Form_Select::CASE_EXPORT:
 550         $this->queryMode = CRM_Contact_BAO_Query::MODE_CASE;
 551         break;
 552
 553       case CRM_Export_Form_Select::GRANT_EXPORT:
 554         $this->queryMode = CRM_Contact_BAO_Query::MODE_GRANT;
 555         break;
 556
 557       case CRM_Export_Form_Select::ACTIVITY_EXPORT:
 558         $this->queryMode = CRM_Contact_BAO_Query::MODE_ACTIVITY;
 559         break;
 560
 561       default:
 562         $this->queryMode = CRM_Contact_BAO_Query::MODE_CONTACTS;
 563     }
 564   }
 565
 566   /**
 567    * @return int
 568    */
 569   public function getExportMode() {
 570     return $this->exportMode;
 571   }
 572
 573   /**
 574    * @param int $exportMode
 575    */
 576   public function setExportMode($exportMode) {
 577     $this->exportMode = $exportMode;
 578   }
 579
 580   /**
 581    * Get the name for the export file.
 582    *
 583    * @return string
 584    */
 585   public function getExportFileName() {
 586     switch ($this->getExportMode()) {
 587       case CRM_Export_Form_Select::CONTACT_EXPORT:
 588         return ts('CiviCRM Contact Search');
 589
 590       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
 591         return ts('CiviCRM Contribution Search');
 592
 593       case CRM_Export_Form_Select::MEMBER_EXPORT:
 594         return ts('CiviCRM Member Search');
 595
 596       case CRM_Export_Form_Select::EVENT_EXPORT:
 597         return ts('CiviCRM Participant Search');
 598
 599       case CRM_Export_Form_Select::PLEDGE_EXPORT:
 600         return ts('CiviCRM Pledge Search');
 601
 602       case CRM_Export_Form_Select::CASE_EXPORT:
 603         return ts('CiviCRM Case Search');
 604
 605       case CRM_Export_Form_Select::GRANT_EXPORT:
 606         return ts('CiviCRM Grant Search');
 607
 608       case CRM_Export_Form_Select::ACTIVITY_EXPORT:
 609         return ts('CiviCRM Activity Search');
 610
 611       default:
 612         // Legacy code suggests the value could be 'financial' - ie. something
 613         // other than what should be accepted. However, I suspect that this line is
 614         // never hit.
 615         return ts('CiviCRM Search');
 616     }
 617   }
 618
 619   /**
 620    * Get the label for the header row based on the field to output.
 621    *
 622    * @param string $field
 623    *
 624    * @return string
 625    */
 626   public function getHeaderForRow($field) {
 627     if (substr($field, -11) == 'campaign_id') {
 628       // @todo - set this correctly in the xml rather than here.
 629       // This will require a generalised handling cleanup
 630       return ts('Campaign ID');
 631     }
 632     if ($this->isMergeSameHousehold() && $field === 'id') {
 633       return ts('Household ID');
 634     }
 635     elseif (isset($this->getQueryFields()[$field]['title'])) {
 636       return $this->getQueryFields()[$field]['title'];
 637     }
 638     elseif ($this->isExportPaymentFields() && array_key_exists($field, $this->getcomponentPaymentFields())) {
 639       return CRM_Utils_Array::value($field, $this->getcomponentPaymentFields());
 640     }
 641     else {
 642       return $field;
 643     }
 644   }
 645
 646   /**
 647    * @param $params
 648    * @param $order
 649    *
 650    * @return array
 651    */
 652   public function runQuery($params, $order) {
 653     $returnProperties = $this->getReturnProperties();
 654     $addressWhere = '';
 655     $params = array_merge($params, $this->getWhereParams());
 656     if ($this->isPostalableOnly) {
 657       if (array_key_exists('street_address', $returnProperties)) {
 658         $addressWhere = " civicrm_address.street_address <> ''";
 659         if (array_key_exists('supplemental_address_1', $returnProperties)) {
 660           // We need this to be an OR rather than AND on the street_address so, hack it in.
 661           $addressOptions = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME,
 662             'address_options', TRUE, NULL, TRUE
 663           );
 664           if (!empty($addressOptions['supplemental_address_1'])) {
 665             $addressWhere .= " OR civicrm_address.supplemental_address_1 <> ''";
 666           }
 667         }
 668         $addressWhere = ' AND (' . $addressWhere . ')';
 669       }
 670     }
 671     $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL,
 672       FALSE, FALSE, $this->getQueryMode(),
 673       FALSE, TRUE, TRUE, NULL, $this->getQueryOperator()
 674     );
 675
 676     //sort by state
 677     //CRM-15301
 678     $query->_sort = $order;
 679     list($select, $from, $where, $having) = $query->query();
 680     $this->setQueryFields($query->_fields);
 681     return [$query, $select, $from, $where . $addressWhere, $having];
 682   }
 683
 684   /**
 685    * Add a row to the specification for how to output data.
 686    *
 687    * @param string $key
 688    * @param string $relationshipType
 689    * @param string $locationType
 690    * @param int $entityTypeID phone_type_id or provider_id for phone or im fields.
 691    */
 692   public function addOutputSpecification($key, $relationshipType = NULL, $locationType = NULL, $entityTypeID = NULL) {
 693     $entityLabel = '';
 694     if ($entityTypeID) {
 695       if ($key === 'phone') {
 696         $entityLabel = CRM_Core_PseudoConstant::getLabel('CRM_Core_BAO_Phone', 'phone_type_id', $entityTypeID);
 697       }
 698       if ($key === 'im') {
 699         $entityLabel = CRM_Core_PseudoConstant::getLabel('CRM_Core_BAO_IM', 'provider_id', $entityTypeID);
 700       }
 701     }
 702
 703     // These oddly constructed keys are for legacy reasons. Altering them will affect test success
 704     // but in time it may be good to rationalise them.
 705     $label = $this->getOutputSpecificationLabel($key, $relationshipType, $locationType, $entityLabel);
 706     $index = $this->getOutputSpecificationIndex($key, $relationshipType, $locationType, $entityLabel);
 707     $fieldKey = $this->getOutputSpecificationFieldKey($key, $relationshipType, $locationType, $entityLabel);
 708
 709     $this->outputSpecification[$index]['header'] = $label;
 710     $this->outputSpecification[$index]['sql_columns'] = $this->getSqlColumnDefinition($fieldKey, $key);
 711
 712     if ($relationshipType && $this->isHouseholdMergeRelationshipTypeKey($relationshipType)) {
 713       $this->setColumnAsCalculationOnly($index);
 714     }
 715     $this->outputSpecification[$index]['metadata'] = $this->getMetaDataForField($key);
 716   }
 717
 718   /**
 719    * Get the metadata for the given field.
 720    *
 721    * @param $key
 722    *
 723    * @return array
 724    */
 725   public function getMetaDataForField($key) {
 726     $mappings = ['contact_id' => 'id'];
 727     if (isset($this->getQueryFields()[$key])) {
 728       return $this->getQueryFields()[$key];
 729     }
 730     if (isset($mappings[$key])) {
 731       return $this->getQueryFields()[$mappings[$key]];
 732     }
 733     return [];
 734   }
 735
 736   /**
 737    * @param $key
 738    */
 739   public function setSqlColumnDefn($key) {
 740     $this->outputSpecification[$this->getMungedFieldName($key)]['sql_columns'] = $this->getSqlColumnDefinition($key, $this->getMungedFieldName($key));
 741   }
 742
 743   /**
 744    * Mark a column as only required for calculations.
 745    *
 746    * Do not include the row with headers.
 747    *
 748    * @param string $column
 749    */
 750   public function setColumnAsCalculationOnly($column) {
 751     $this->outputSpecification[$column]['do_not_output_to_csv'] = TRUE;
 752   }
 753
 754   /**
 755    * @return array
 756    */
 757   public function getHeaderRows() {
 758     $headerRows = [];
 759     foreach ($this->outputSpecification as $key => $spec) {
 760       if (empty($spec['do_not_output_to_csv'])) {
 761         $headerRows[] = $spec['header'];
 762       }
 763     }
 764     return $headerRows;
 765   }
 766
 767   /**
 768    * @return array
 769    */
 770   public function getSQLColumns() {
 771     $sqlColumns = [];
 772     foreach ($this->outputSpecification as $key => $spec) {
 773       if (empty($spec['do_not_output_to_sql'])) {
 774         $sqlColumns[$key] = $spec['sql_columns'];
 775       }
 776     }
 777     return $sqlColumns;
 778   }
 779
 780   /**
 781    * @return array
 782    */
 783   public function getMetadata() {
 784     $metadata = [];
 785     foreach ($this->outputSpecification as $key => $spec) {
 786       $metadata[$key] = $spec['metadata'];
 787     }
 788     return $metadata;
 789   }
 790
 791   /**
 792    * Build the row for output.
 793    *
 794    * @param \CRM_Contact_BAO_Query $query
 795    * @param CRM_Core_DAO $iterationDAO
 796    * @param array $outputColumns
 797    * @param $metadata
 798    * @param $paymentDetails
 799    * @param $addPaymentHeader
 800    * @param $paymentTableId
 801    *
 802    * @return array|bool
 803    */
 804   public function buildRow($query, $iterationDAO, $outputColumns, $metadata, $paymentDetails, $addPaymentHeader, $paymentTableId) {
 805     if ($this->isHouseholdToSkip($iterationDAO->contact_id)) {
 806       return FALSE;
 807     }
 808     $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id');
 809     $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id');
 810
 811     $row = [];
 812     $householdMergeRelationshipType = $this->getHouseholdMergeTypeForRow($iterationDAO->contact_id);
 813     if ($householdMergeRelationshipType) {
 814       $householdID = $this->getRelatedHouseholdID($iterationDAO->contact_id, $householdMergeRelationshipType);
 815       if ($this->isHouseholdExported($householdID)) {
 816         return FALSE;
 817       }
 818       foreach (array_keys($outputColumns) as $column) {
 819         $row[$column] = $this->getRelationshipValue($householdMergeRelationshipType, $iterationDAO->contact_id, $column);
 820       }
 821       $this->markHouseholdExported($householdID);
 822       return $row;
 823     }
 824
 825     $query->convertToPseudoNames($iterationDAO);
 826
 827     //first loop through output columns so that we return what is required, and in same order.
 828     foreach ($outputColumns as $field => $value) {
 829       // add im_provider to $dao object
 830       if ($field == 'im_provider' && property_exists($iterationDAO, 'provider_id')) {
 831         $iterationDAO->im_provider = $iterationDAO->provider_id;
 832       }
 833
 834       //build row values (data)
 835       $fieldValue = NULL;
 836       if (property_exists($iterationDAO, $field)) {
 837         $fieldValue = $iterationDAO->$field;
 838         // to get phone type from phone type id
 839         if ($field == 'phone_type_id' && isset($phoneTypes[$fieldValue])) {
 840           $fieldValue = $phoneTypes[$fieldValue];
 841         }
 842         elseif ($field == 'provider_id' || $field == 'im_provider') {
 843           $fieldValue = CRM_Utils_Array::value($fieldValue, $imProviders);
 844         }
 845         elseif (strstr($field, 'master_id')) {
 846           $masterAddressId = NULL;
 847           if (isset($iterationDAO->$field)) {
 848             $masterAddressId = $iterationDAO->$field;
 849           }
 850           // get display name of contact that address is shared.
 851           $fieldValue = CRM_Contact_BAO_Contact::getMasterDisplayName($masterAddressId);
 852         }
 853       }
 854
 855       if ($this->isRelationshipTypeKey($field)) {
 856         $this->buildRelationshipFieldsForRow($row, $iterationDAO->contact_id, $value, $field);
 857       }
 858       else {
 859         $row[$field] = $this->getTransformedFieldValue($field, $iterationDAO, $fieldValue, $metadata, $paymentDetails);
 860       }
 861     }
 862
 863     // If specific payment fields have been selected for export, payment
 864     // data will already be in $row. Otherwise, add payment related
 865     // information, if appropriate.
 866     if ($addPaymentHeader) {
 867       if (!$this->isExportSpecifiedPaymentFields()) {
 868         $nullContributionDetails = array_fill_keys(array_keys($this->getPaymentHeaders()), NULL);
 869         if ($this->isExportPaymentFields()) {
 870           $paymentData = CRM_Utils_Array::value($row[$paymentTableId], $paymentDetails);
 871           if (!is_array($paymentData) || empty($paymentData)) {
 872             $paymentData = $nullContributionDetails;
 873           }
 874           $row = array_merge($row, $paymentData);
 875         }
 876         elseif (!empty($paymentDetails)) {
 877           $row = array_merge($row, $nullContributionDetails);
 878         }
 879       }
 880     }
 881     //remove organization name for individuals if it is set for current employer
 882     if (!empty($row['contact_type']) &&
 883       $row['contact_type'] == 'Individual' && array_key_exists('organization_name', $row)
 884     ) {
 885       $row['organization_name'] = '';
 886     }
 887     return $row;
 888   }
 889
 890   /**
 891    * If this row has a household whose details we should use get the relationship type key.
 892    *
 893    * @param $contactID
 894    *
 895    * @return bool
 896    */
 897   public function getHouseholdMergeTypeForRow($contactID) {
 898     if (!$this->isMergeSameHousehold()) {
 899       return FALSE;
 900     }
 901     foreach ($this->getHouseholdRelationshipTypes() as $relationshipType) {
 902       if (isset($this->relatedContactValues[$relationshipType][$contactID])) {
 903         return $relationshipType;
 904       }
 905     }
 906   }
 907
 908   /**
 909    * Mark the given household as already exported.
 910    *
 911    * @param $householdID
 912    */
 913   public function markHouseholdExported($householdID) {
 914     $this->exportedHouseholds[$householdID] = $householdID;
 915   }
 916
 917   /**
 918    * @param $field
 919    * @param $iterationDAO
 920    * @param $fieldValue
 921    * @param $metadata
 922    * @param $paymentDetails
 923    *
 924    * @return string
 925    */
 926   public function getTransformedFieldValue($field, $iterationDAO, $fieldValue, $metadata, $paymentDetails) {
 927
 928     $i18n = CRM_Core_I18n::singleton();
 929     if ($field == 'id') {
 930       return $iterationDAO->contact_id;
 931       // special case for calculated field
 932     }
 933     elseif ($field == 'source_contact_id') {
 934       return $iterationDAO->contact_id;
 935     }
 936     elseif ($field == 'pledge_balance_amount') {
 937       return $iterationDAO->pledge_amount - $iterationDAO->pledge_total_paid;
 938       // special case for calculated field
 939     }
 940     elseif ($field == 'pledge_next_pay_amount') {
 941       return $iterationDAO->pledge_next_pay_amount + $iterationDAO->pledge_outstanding_amount;
 942     }
 943     elseif (isset($fieldValue) &&
 944       $fieldValue != ''
 945     ) {
 946       //check for custom data
 947       if ($cfID = CRM_Core_BAO_CustomField::getKeyID($field)) {
 948         return CRM_Core_BAO_CustomField::displayValue($fieldValue, $cfID);
 949       }
 950
 951       elseif (in_array($field, [
 952         'email_greeting',
 953         'postal_greeting',
 954         'addressee',
 955       ])) {
 956         //special case for greeting replacement
 957         $fldValue = "{$field}_display";
 958         return $iterationDAO->$fldValue;
 959       }
 960       else {
 961         //normal fields with a touch of CRM-3157
 962         switch ($field) {
 963           case 'country':
 964           case 'world_region':
 965             return $i18n->crm_translate($fieldValue, ['context' => 'country']);
 966
 967           case 'state_province':
 968             return $i18n->crm_translate($fieldValue, ['context' => 'province']);
 969
 970           case 'gender':
 971           case 'preferred_communication_method':
 972           case 'preferred_mail_format':
 973           case 'communication_style':
 974             return $i18n->crm_translate($fieldValue);
 975
 976           default:
 977             if (isset($metadata[$field])) {
 978               // No I don't know why we do it this way & whether we could
 979               // make better use of pseudoConstants.
 980               if (!empty($metadata[$field]['context'])) {
 981                 return $i18n->crm_translate($fieldValue, $metadata[$field]);
 982               }
 983               if (!empty($metadata[$field]['pseudoconstant'])) {
 984                 if (!empty($metadata[$field]['bao'])) {
 985                   return CRM_Core_PseudoConstant::getLabel($metadata[$field]['bao'], $metadata[$field]['name'], $fieldValue);
 986                 }
 987                 // This is not our normal syntax for pseudoconstants but I am a bit loath to
 988                 // call an external function until sure it is not increasing php processing given this
 989                 // may be iterated 100,000 times & we already have the $imProvider var loaded.
 990                 // That can be next refactor...
 991                 // Yes - definitely feeling hatred for this bit of code - I know you will beat me up over it's awfulness
 992                 // but I have to reach a stable point....
 993                 $varName = $metadata[$field]['pseudoconstant']['var'];
 994                 if ($varName === 'imProviders') {
 995                   return CRM_Core_PseudoConstant::getLabel('CRM_Core_DAO_IM', 'provider_id', $fieldValue);
 996                 }
 997                 if ($varName === 'phoneTypes') {
 998                   return CRM_Core_PseudoConstant::getLabel('CRM_Core_DAO_Phone', 'phone_type_id', $fieldValue);
 999                 }
1000               }
1001
1002             }
1003             return $fieldValue;
1004         }
1005       }
1006     }
1007     elseif ($this->isExportSpecifiedPaymentFields() && array_key_exists($field, $this->getcomponentPaymentFields())) {
1008       $paymentTableId = $this->getPaymentTableID();
1009       $paymentData = CRM_Utils_Array::value($iterationDAO->$paymentTableId, $paymentDetails);
1010       $payFieldMapper = [
1011         'componentPaymentField_total_amount' => 'total_amount',
1012         'componentPaymentField_contribution_status' => 'contribution_status',
1013         'componentPaymentField_payment_instrument' => 'pay_instru',
1014         'componentPaymentField_transaction_id' => 'trxn_id',
1015         'componentPaymentField_received_date' => 'receive_date',
1016       ];
1017       return CRM_Utils_Array::value($payFieldMapper[$field], $paymentData, '');
1018     }
1019     else {
1020       // if field is empty or null
1021       return '';
1022     }
1023   }
1024
1025   /**
1026    * Get array of fields to return, over & above those defined in the main contact exportable fields.
1027    *
1028    * These include export mode specific fields & some fields apparently required as 'exportableFields'
1029    * but not returned by the function of the same name.
1030    *
1031    * @return array
1032    *   Array of fields to return in the format ['field_name' => 1,...]
1033    */
1034   public function getAdditionalReturnProperties() {
1035     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CONTACTS) {
1036       $componentSpecificFields = [];
1037     }
1038     else {
1039       $componentSpecificFields = CRM_Contact_BAO_Query::defaultReturnProperties($this->getQueryMode());
1040     }
1041     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_PLEDGE) {
1042       $componentSpecificFields = array_merge($componentSpecificFields, CRM_Pledge_BAO_Query::extraReturnProperties($this->getQueryMode()));
1043       unset($componentSpecificFields['contribution_status_id']);
1044       unset($componentSpecificFields['pledge_status_id']);
1045       unset($componentSpecificFields['pledge_payment_status_id']);
1046     }
1047     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CASE) {
1048       $componentSpecificFields = array_merge($componentSpecificFields, CRM_Case_BAO_Query::extraReturnProperties($this->getQueryMode()));
1049     }
1050     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
1051       $componentSpecificFields = array_merge($componentSpecificFields, CRM_Contribute_BAO_Query::softCreditReturnProperties(TRUE));
1052       unset($componentSpecificFields['contribution_status_id']);
1053     }
1054     return $componentSpecificFields;
1055   }
1056
1057   /**
1058    * Should payment fields be appended to the export.
1059    *
1060    * (This is pretty hacky so hopefully this function won't last long - notice
1061    * how obviously it should be part of the above function!).
1062    */
1063   public function isExportPaymentFields() {
1064     if ($this->getRequestedFields() === NULL
1065       &&  in_array($this->getQueryMode(), [
1066         CRM_Contact_BAO_Query::MODE_EVENT,
1067         CRM_Contact_BAO_Query::MODE_MEMBER,
1068         CRM_Contact_BAO_Query::MODE_PLEDGE,
1069       ])) {
1070       return TRUE;
1071     }
1072     elseif ($this->isExportSpecifiedPaymentFields()) {
1073       return TRUE;
1074     }
1075     return FALSE;
1076   }
1077
1078   /**
1079    * Has specific payment fields been requested (as opposed to via all fields).
1080    *
1081    * If specific fields have been requested then they get added at various points.
1082    *
1083    * @return bool
1084    */
1085   public function isExportSpecifiedPaymentFields() {
1086     if ($this->getRequestedFields() !== NULL && $this->hasRequestedComponentPaymentFields()) {
1087       return TRUE;
1088     }
1089   }
1090
1091   /**
1092    * Get the name of the id field in the table that connects contributions to the export entity.
1093    */
1094   public function getPaymentTableID() {
1095     if ($this->getRequestedFields() === NULL) {
1096       $mapping = [
1097         CRM_Contact_BAO_Query::MODE_EVENT => 'participant_id',
1098         CRM_Contact_BAO_Query::MODE_MEMBER => 'membership_id',
1099         CRM_Contact_BAO_Query::MODE_PLEDGE => 'pledge_payment_id',
1100       ];
1101       return isset($mapping[$this->getQueryMode()]) ? $mapping[$this->getQueryMode()] : '';
1102     }
1103     elseif ($this->hasRequestedComponentPaymentFields()) {
1104       return 'participant_id';
1105     }
1106     return FALSE;
1107   }
1108
1109   /**
1110    * Have component payment fields been requested.
1111    *
1112    * @return bool
1113    */
1114   protected function hasRequestedComponentPaymentFields() {
1115     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_EVENT) {
1116       $participantPaymentFields = array_intersect_key($this->getComponentPaymentFields(), $this->getReturnProperties());
1117       if (!empty($participantPaymentFields)) {
1118         return TRUE;
1119       }
1120     }
1121     return FALSE;
1122   }
1123
1124   /**
1125    * Get fields that indicate payment fields have been requested for a component.
1126    *
1127    * Ideally this should be protected but making it temporarily public helps refactoring..
1128    *
1129    * @return array
1130    */
1131   public function getComponentPaymentFields() {
1132     return [
1133       'componentPaymentField_total_amount' => ts('Total Amount'),
1134       'componentPaymentField_contribution_status' => ts('Contribution Status'),
1135       'componentPaymentField_received_date' => ts('Date Received'),
1136       'componentPaymentField_payment_instrument' => ts('Payment Method'),
1137       'componentPaymentField_transaction_id' => ts('Transaction ID'),
1138     ];
1139   }
1140
1141   /**
1142    * Get headers for payment fields.
1143    *
1144    * Returns an array of contribution fields when the entity supports payment fields and specific fields
1145    * are not specified. This is a transitional function for refactoring legacy code.
1146    */
1147   public function getPaymentHeaders() {
1148     if ($this->isExportPaymentFields() && !$this->isExportSpecifiedPaymentFields()) {
1149       return $this->getcomponentPaymentFields();
1150     }
1151     return [];
1152   }
1153
1154   /**
1155    * Get the default properties when not specified.
1156    *
1157    * In the UI this appears as 'Primary fields only' but in practice it's
1158    * most of the kitchen sink and the hallway closet thrown in.
1159    *
1160    * Since CRM-952 custom fields are excluded, but no other form of mercy is shown.
1161    *
1162    * @return array
1163    */
1164   public function getDefaultReturnProperties() {
1165     $returnProperties = [];
1166     $fields = CRM_Contact_BAO_Contact::exportableFields('All', TRUE, TRUE);
1167     $skippedFields = ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CONTACTS) ? [] : [
1168       'groups',
1169       'tags',
1170       'notes',
1171     ];
1172
1173     foreach ($fields as $key => $var) {
1174       if ($key && (substr($key, 0, 6) != 'custom') && !in_array($key, $skippedFields)) {
1175         $returnProperties[$key] = 1;
1176       }
1177     }
1178     $returnProperties = array_merge($returnProperties, $this->getAdditionalReturnProperties());
1179     return $returnProperties;
1180   }
1181
1182   /**
1183    * Add the field to relationship return properties & return it.
1184    *
1185    * This function is doing both setting & getting which is yuck but it is an interim
1186    * refactor.
1187    *
1188    * @param array $value
1189    * @param string $relationshipKey
1190    *
1191    * @return array
1192    */
1193   public function setRelationshipReturnProperties($value, $relationshipKey) {
1194     $relationField = $value['name'];
1195     $relIMProviderId = NULL;
1196     $relLocTypeId = CRM_Utils_Array::value('location_type_id', $value);
1197     $locationName = CRM_Core_PseudoConstant::getName('CRM_Core_BAO_Address', 'location_type_id', $relLocTypeId);
1198     $relPhoneTypeId = CRM_Utils_Array::value('phone_type_id', $value, ($locationName ? 'Primary' : NULL));
1199     $relIMProviderId = CRM_Utils_Array::value('im_provider_id', $value, ($locationName ? 'Primary' : NULL));
1200     if (in_array($relationField, $this->getValidLocationFields()) && $locationName) {
1201       if ($relationField === 'phone') {
1202         $this->relationshipReturnProperties[$relationshipKey]['location'][$locationName]['phone-' . $relPhoneTypeId] = 1;
1203       }
1204       elseif ($relationField === 'im') {
1205         $this->relationshipReturnProperties[$relationshipKey]['location'][$locationName]['im-' . $relIMProviderId] = 1;
1206       }
1207       else {
1208         $this->relationshipReturnProperties[$relationshipKey]['location'][$locationName][$relationField] = 1;
1209       }
1210     }
1211     else {
1212       $this->relationshipReturnProperties[$relationshipKey][$relationField] = 1;
1213     }
1214     return $this->relationshipReturnProperties[$relationshipKey];
1215   }
1216
1217   /**
1218    * Add the main return properties to the household merge properties if needed for merging.
1219    *
1220    * If we are using household merge we need to add these to the relationship properties to
1221    * be retrieved.
1222    */
1223   public function setHouseholdMergeReturnProperties() {
1224     if ($this->isMergeSameHousehold()) {
1225       $returnProperties = $this->getReturnProperties();
1226       $returnProperties = array_diff_key($returnProperties, array_fill_keys(['location_type', 'im_provider'], 1));
1227       foreach ($this->getHouseholdRelationshipTypes() as $householdRelationshipType) {
1228         $this->relationshipReturnProperties[$householdRelationshipType] = $returnProperties;
1229       }
1230     }
1231   }
1232
1233   /**
1234    * Get the default location fields to request.
1235    *
1236    * @return array
1237    */
1238   public function getValidLocationFields() {
1239     return [
1240       'street_address',
1241       'supplemental_address_1',
1242       'supplemental_address_2',
1243       'supplemental_address_3',
1244       'city',
1245       'postal_code',
1246       'postal_code_suffix',
1247       'geo_code_1',
1248       'geo_code_2',
1249       'state_province',
1250       'country',
1251       'phone',
1252       'email',
1253       'im',
1254     ];
1255   }
1256
1257   /**
1258    * Get the sql column definition for the given field.
1259    *
1260    * @param string $fieldName
1261    * @param string $columnName
1262    *
1263    * @return mixed
1264    */
1265   public function getSqlColumnDefinition($fieldName, $columnName) {
1266
1267     // early exit for master_id, CRM-12100
1268     // in the DB it is an ID, but in the export, we retrive the display_name of the master record
1269     // also for current_employer, CRM-16939
1270     if ($columnName == 'master_id' || $columnName == 'current_employer') {
1271       return "$fieldName varchar(128)";
1272     }
1273
1274     if (substr($fieldName, -11) == 'campaign_id') {
1275       // CRM-14398
1276       return "$fieldName varchar(128)";
1277     }
1278
1279     $queryFields = $this->getQueryFields();
1280     $lookUp = ['prefix_id', 'suffix_id'];
1281     // set the sql columns
1282     if (isset($queryFields[$columnName]['type'])) {
1283       switch ($queryFields[$columnName]['type']) {
1284         case CRM_Utils_Type::T_INT:
1285         case CRM_Utils_Type::T_BOOLEAN:
1286           if (in_array($columnName, $lookUp)) {
1287             return "$fieldName varchar(255)";
1288           }
1289           else {
1290             return "$fieldName varchar(16)";
1291           }
1292
1293         case CRM_Utils_Type::T_STRING:
1294           if (isset($queryFields[$columnName]['maxlength'])) {
1295             return "$fieldName varchar({$queryFields[$columnName]['maxlength']})";
1296           }
1297           else {
1298             return "$fieldName varchar(255)";
1299           }
1300
1301         case CRM_Utils_Type::T_TEXT:
1302         case CRM_Utils_Type::T_LONGTEXT:
1303         case CRM_Utils_Type::T_BLOB:
1304         case CRM_Utils_Type::T_MEDIUMBLOB:
1305           return "$fieldName longtext";
1306
1307         case CRM_Utils_Type::T_FLOAT:
1308         case CRM_Utils_Type::T_ENUM:
1309         case CRM_Utils_Type::T_DATE:
1310         case CRM_Utils_Type::T_TIME:
1311         case CRM_Utils_Type::T_TIMESTAMP:
1312         case CRM_Utils_Type::T_MONEY:
1313         case CRM_Utils_Type::T_EMAIL:
1314         case CRM_Utils_Type::T_URL:
1315         case CRM_Utils_Type::T_CCNUM:
1316         default:
1317           return "$fieldName varchar(32)";
1318       }
1319     }
1320     else {
1321       if (substr($fieldName, -3, 3) == '_id') {
1322         return "$fieldName varchar(255)";
1323       }
1324       elseif (substr($fieldName, -5, 5) == '_note') {
1325         return "$fieldName text";
1326       }
1327       else {
1328         $changeFields = [
1329           'groups',
1330           'tags',
1331           'notes',
1332         ];
1333
1334         if (in_array($fieldName, $changeFields)) {
1335           return "$fieldName text";
1336         }
1337         else {
1338           // set the sql columns for custom data
1339           if (isset($queryFields[$columnName]['data_type'])) {
1340
1341             switch ($queryFields[$columnName]['data_type']) {
1342               case 'String':
1343                 // May be option labels, which could be up to 512 characters
1344                 $length = max(512, CRM_Utils_Array::value('text_length', $queryFields[$columnName]));
1345                 return "$fieldName varchar($length)";
1346
1347               case 'Country':
1348               case 'StateProvince':
1349               case 'Link':
1350                 return "$fieldName varchar(255)";
1351
1352               case 'Memo':
1353                 return "$fieldName text";
1354
1355               default:
1356                 return "$fieldName varchar(255)";
1357             }
1358           }
1359           else {
1360             return "$fieldName text";
1361           }
1362         }
1363       }
1364     }
1365   }
1366
1367   /**
1368    * Get the munged field name.
1369    *
1370    * @param string $field
1371    * @return string
1372    */
1373   public function getMungedFieldName($field) {
1374     $fieldName = CRM_Utils_String::munge(strtolower($field), '_', 64);
1375     if ($fieldName == 'id') {
1376       $fieldName = 'civicrm_primary_id';
1377     }
1378     return $fieldName;
1379   }
1380
1381   /**
1382    * In order to respect the history of this class we need to index kinda illogically.
1383    *
1384    * On the bright side - this stuff is tested within a nano-byte of it's life.
1385    *
1386    * e.g '2-a-b_Home-City'
1387    *
1388    * @param string $key
1389    * @param string $relationshipType
1390    * @param string $locationType
1391    * @param $entityLabel
1392    *
1393    * @return string
1394    */
1395   protected function getOutputSpecificationIndex($key, $relationshipType, $locationType, $entityLabel) {
1396     if ($entityLabel || $key === 'im') {
1397       // Just cos that's the history...
1398       if ($key !== 'master_id') {
1399         $key = $this->getHeaderForRow($key);
1400       }
1401     }
1402     if (!$relationshipType || $key !== 'id') {
1403       $key = $this->getMungedFieldName($key);
1404     }
1405     return $this->getMungedFieldName(
1406       ($relationshipType ? ($relationshipType . '_') : '')
1407       . ($locationType ? ($locationType . '_') : '')
1408       . $key
1409       . ($entityLabel ? ('_' . $entityLabel) : '')
1410     );
1411   }
1412
1413   /**
1414    * Get the compiled label for the column.
1415    *
1416    * e.g 'Gender', 'Employee Of-Home-city'
1417    *
1418    * @param string $key
1419    * @param string $relationshipType
1420    * @param string $locationType
1421    * @param string $entityLabel
1422    *
1423    * @return string
1424    */
1425   protected function getOutputSpecificationLabel($key, $relationshipType, $locationType, $entityLabel) {
1426     return ($relationshipType ? $this->getRelationshipTypes()[$relationshipType] . '-' : '')
1427       . ($locationType ? $locationType . '-' : '')
1428       . $this->getHeaderForRow($key)
1429       . ($entityLabel ? '-' . $entityLabel : '');
1430   }
1431
1432   /**
1433    * Get the mysql field name key.
1434    *
1435    * This key is locked in by tests but the reasons for the specific conventions -
1436    * ie. headings are used for keying fields in some cases, are likely
1437    * accidental rather than deliberate.
1438    *
1439    * This key is used for the output sql array.
1440    *
1441    * @param string $key
1442    * @param $relationshipType
1443    * @param $locationType
1444    * @param $entityLabel
1445    *
1446    * @return string
1447    */
1448   protected function getOutputSpecificationFieldKey($key, $relationshipType, $locationType, $entityLabel) {
1449     if ($entityLabel || $key === 'im') {
1450       if ($key !== 'state_province' && $key !== 'id') {
1451         // @todo - test removing this - indexing by $key should be fine...
1452         $key = $this->getHeaderForRow($key);
1453       }
1454     }
1455     if (!$relationshipType || $key !== 'id') {
1456       $key = $this->getMungedFieldName($key);
1457     }
1458     $fieldKey = $this->getMungedFieldName(
1459       ($relationshipType ? ($relationshipType . '_') : '')
1460       . ($locationType ? ($locationType . '_') : '')
1461       . $key
1462       . ($entityLabel ? ('_' . $entityLabel) : '')
1463     );
1464     return $fieldKey;
1465   }
1466
1467   /**
1468    * Get params for the where criteria.
1469    *
1470    * @return mixed
1471    */
1472   public function getWhereParams() {
1473     if (!$this->isPostalableOnly()) {
1474       return [];
1475     }
1476     $params['is_deceased'] = ['is_deceased', '=', 0, CRM_Contact_BAO_Query::MODE_CONTACTS];
1477     $params['do_not_mail'] = ['do_not_mail', '=', 0, CRM_Contact_BAO_Query::MODE_CONTACTS];
1478     return $params;
1479   }
1480
1481   /**
1482    * @param $row
1483    * @param $contactID
1484    * @param $value
1485    * @param $field
1486    */
1487   protected function buildRelationshipFieldsForRow(&$row, $contactID, $value, $field) {
1488     foreach (array_keys($value) as $property) {
1489       if ($property === 'location') {
1490         // @todo just undo all this nasty location wrangling!
1491         foreach ($value['location'] as $locationKey => $locationFields) {
1492           foreach (array_keys($locationFields) as $locationField) {
1493             $fieldKey = str_replace(' ', '_', $locationKey . '-' . $locationField);
1494             $row[$field . '_' . $fieldKey] = $this->getRelationshipValue($field, $contactID, $fieldKey);
1495           }
1496         }
1497       }
1498       else {
1499         $row[$field . '_' . $property] = $this->getRelationshipValue($field, $contactID, $property);
1500       }
1501     }
1502   }
1503
1504   /**
1505    * Is this contact a household that is already set to be exported by virtue of it's household members.
1506    *
1507    * @param int $contactID
1508    *
1509    * @return bool
1510    */
1511   protected function isHouseholdToSkip($contactID) {
1512     return in_array($contactID, $this->householdsToSkip);
1513   }
1514
1515   /**
1516    * Get default return property for export based on mode
1517    *
1518    * @return string
1519    *   Default Return property
1520    */
1521   public function defaultReturnProperty() {
1522     // hack to add default return property based on export mode
1523     $property = NULL;
1524     $exportMode = $this->getExportMode();
1525     if ($exportMode == CRM_Export_Form_Select::CONTRIBUTE_EXPORT) {
1526       $property = 'contribution_id';
1527     }
1528     elseif ($exportMode == CRM_Export_Form_Select::EVENT_EXPORT) {
1529       $property = 'participant_id';
1530     }
1531     elseif ($exportMode == CRM_Export_Form_Select::MEMBER_EXPORT) {
1532       $property = 'membership_id';
1533     }
1534     elseif ($exportMode == CRM_Export_Form_Select::PLEDGE_EXPORT) {
1535       $property = 'pledge_id';
1536     }
1537     elseif ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) {
1538       $property = 'case_id';
1539     }
1540     elseif ($exportMode == CRM_Export_Form_Select::GRANT_EXPORT) {
1541       $property = 'grant_id';
1542     }
1543     elseif ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) {
1544       $property = 'activity_id';
1545     }
1546     return $property;
1547   }
1548
1549   /**
1550    * Determine the required return properties from the input parameters.
1551    *
1552    * @return array
1553    */
1554   public function determineReturnProperties() {
1555     if ($this->getRequestedFields()) {
1556       $returnProperties = [];
1557       foreach ($this->getRequestedFields() as $key => $value) {
1558         $fieldName = $value['name'];
1559         $locationName = CRM_Core_PseudoConstant::getName('CRM_Core_BAO_Address', 'location_type_id', $value['location_type_id']);
1560         $relationshipTypeKey = !empty($value['relationship_type_id']) ? $value['relationship_type_id'] . '_' . $value['relationship_direction'] : NULL;
1561         if (!$fieldName || $this->isHouseholdMergeRelationshipTypeKey($relationshipTypeKey)) {
1562           continue;
1563         }
1564
1565         if ($this->isRelationshipTypeKey($relationshipTypeKey)) {
1566           $returnProperties[$relationshipTypeKey] = $this->setRelationshipReturnProperties($value, $relationshipTypeKey);
1567         }
1568         elseif ($locationName) {
1569           if ($fieldName === 'phone') {
1570             $returnProperties['location'][$locationName]['phone-' . $value['phone_type_id'] ?? NULL] = 1;
1571           }
1572           elseif ($fieldName === 'im') {
1573             $returnProperties['location'][$locationName]['im-' . $value['im_provider_id'] ?? NULL] = 1;
1574           }
1575           else {
1576             $returnProperties['location'][$locationName][$fieldName] = 1;
1577           }
1578         }
1579         else {
1580           //hack to fix component fields
1581           //revert mix of event_id and title
1582           if ($fieldName == 'event_id') {
1583             $returnProperties['event_id'] = 1;
1584           }
1585           else {
1586             $returnProperties[$fieldName] = 1;
1587           }
1588         }
1589       }
1590       $defaultExportMode = $this->defaultReturnProperty();
1591       if ($defaultExportMode) {
1592         $returnProperties[$defaultExportMode] = 1;
1593       }
1594     }
1595     else {
1596       $returnProperties = $this->getDefaultReturnProperties();
1597     }
1598     if ($this->isMergeSameHousehold()) {
1599       $returnProperties['id'] = 1;
1600     }
1601     if ($this->isMergeSameAddress()) {
1602       $returnProperties['addressee'] = 1;
1603       $returnProperties['postal_greeting'] = 1;
1604       $returnProperties['email_greeting'] = 1;
1605       $returnProperties['street_name'] = 1;
1606       $returnProperties['household_name'] = 1;
1607       $returnProperties['street_address'] = 1;
1608       $returnProperties['city'] = 1;
1609       $returnProperties['state_province'] = 1;
1610
1611     }
1612     return $returnProperties;
1613   }
1614
1615   /**
1616    * @param int $contactId
1617    * @param array $exportParams
1618    *
1619    * @return array
1620    */
1621   public function replaceMergeTokens($contactId, $exportParams) {
1622     $greetings = [];
1623     $contact = NULL;
1624
1625     $greetingFields = [
1626       'postal_greeting',
1627       'addressee',
1628     ];
1629     foreach ($greetingFields as $greeting) {
1630       if (!empty($exportParams[$greeting])) {
1631         $greetingLabel = $exportParams[$greeting];
1632         if (empty($contact)) {
1633           $values = [
1634             'id' => $contactId,
1635             'version' => 3,
1636           ];
1637           $contact = civicrm_api('contact', 'get', $values);
1638
1639           if (!empty($contact['is_error'])) {
1640             return $greetings;
1641           }
1642           $contact = $contact['values'][$contact['id']];
1643         }
1644
1645         $tokens = ['contact' => $greetingLabel];
1646         $greetings[$greeting] = CRM_Utils_Token::replaceContactTokens($greetingLabel, $contact, NULL, $tokens);
1647       }
1648     }
1649     return $greetings;
1650   }
1651
1652   /**
1653    * Build array for merging same addresses.
1654    *
1655    * @param $sql
1656    * @param array $exportParams
1657    * @param bool $sharedAddress
1658    *
1659    * @return array
1660    */
1661   public function buildMasterCopyArray($sql, $exportParams, $sharedAddress = FALSE) {
1662     static $contactGreetingTokens = [];
1663
1664     $addresseeOptions = CRM_Core_OptionGroup::values('addressee');
1665     $postalOptions = CRM_Core_OptionGroup::values('postal_greeting');
1666
1667     $merge = $parents = [];
1668     $dao = CRM_Core_DAO::executeQuery($sql);
1669
1670     while ($dao->fetch()) {
1671       $masterID = $dao->master_id;
1672       $copyID = $dao->copy_id;
1673       $masterPostalGreeting = $dao->master_postal_greeting;
1674       $masterAddressee = $dao->master_addressee;
1675       $copyAddressee = $dao->copy_addressee;
1676
1677       if (!$sharedAddress) {
1678         if (!isset($contactGreetingTokens[$dao->master_contact_id])) {
1679           $contactGreetingTokens[$dao->master_contact_id] = $this->replaceMergeTokens($dao->master_contact_id, $exportParams);
1680         }
1681         $masterPostalGreeting = CRM_Utils_Array::value('postal_greeting',
1682           $contactGreetingTokens[$dao->master_contact_id], $dao->master_postal_greeting
1683         );
1684         $masterAddressee = CRM_Utils_Array::value('addressee',
1685           $contactGreetingTokens[$dao->master_contact_id], $dao->master_addressee
1686         );
1687
1688         if (!isset($contactGreetingTokens[$dao->copy_contact_id])) {
1689           $contactGreetingTokens[$dao->copy_contact_id] = $this->replaceMergeTokens($dao->copy_contact_id, $exportParams);
1690         }
1691         $copyPostalGreeting = CRM_Utils_Array::value('postal_greeting',
1692           $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_postal_greeting
1693         );
1694         $copyAddressee = CRM_Utils_Array::value('addressee',
1695           $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_addressee
1696         );
1697       }
1698
1699       if (!isset($merge[$masterID])) {
1700         // check if this is an intermediate child
1701         // this happens if there are 3 or more matches a,b, c
1702         // the above query will return a, b / a, c / b, c
1703         // we might be doing a bit more work, but for now its ok, unless someone
1704         // knows how to fix the query above
1705         if (isset($parents[$masterID])) {
1706           $masterID = $parents[$masterID];
1707         }
1708         else {
1709           $merge[$masterID] = [
1710             'addressee' => $masterAddressee,
1711             'copy' => [],
1712             'postalGreeting' => $masterPostalGreeting,
1713           ];
1714           $merge[$masterID]['emailGreeting'] = &$merge[$masterID]['postalGreeting'];
1715         }
1716       }
1717       $parents[$copyID] = $masterID;
1718
1719       if (!$sharedAddress && !array_key_exists($copyID, $merge[$masterID]['copy'])) {
1720
1721         if (!empty($exportParams['postal_greeting_other']) &&
1722           count($merge[$masterID]['copy']) >= 1
1723         ) {
1724           // use static greetings specified if no of contacts > 2
1725           $merge[$masterID]['postalGreeting'] = $exportParams['postal_greeting_other'];
1726         }
1727         elseif ($copyPostalGreeting) {
1728           $this->trimNonTokensFromAddressString($copyPostalGreeting,
1729             $postalOptions[$dao->copy_postal_greeting_id],
1730             $exportParams
1731           );
1732           $merge[$masterID]['postalGreeting'] = "{$merge[$masterID]['postalGreeting']}, {$copyPostalGreeting}";
1733           // if there happens to be a duplicate, remove it
1734           $merge[$masterID]['postalGreeting'] = str_replace(" {$copyPostalGreeting},", "", $merge[$masterID]['postalGreeting']);
1735         }
1736
1737         if (!empty($exportParams['addressee_other']) &&
1738           count($merge[$masterID]['copy']) >= 1
1739         ) {
1740           // use static greetings specified if no of contacts > 2
1741           $merge[$masterID]['addressee'] = $exportParams['addressee_other'];
1742         }
1743         elseif ($copyAddressee) {
1744           $this->trimNonTokensFromAddressString($copyAddressee,
1745             $addresseeOptions[$dao->copy_addressee_id],
1746             $exportParams, 'addressee'
1747           );
1748           $merge[$masterID]['addressee'] = "{$merge[$masterID]['addressee']}, " . trim($copyAddressee);
1749         }
1750       }
1751       $merge[$masterID]['copy'][$copyID] = $copyAddressee;
1752     }
1753
1754     return $merge;
1755   }
1756
1757   /**
1758    * The function unsets static part of the string, if token is the dynamic part.
1759    *
1760    * Example: 'Hello {contact.first_name}' => converted to => '{contact.first_name}'
1761    * i.e 'Hello Alan' => converted to => 'Alan'
1762    *
1763    * @param string $parsedString
1764    * @param string $defaultGreeting
1765    * @param bool $addressMergeGreetings
1766    * @param string $greetingType
1767    *
1768    * @return mixed
1769    */
1770   public function trimNonTokensFromAddressString(
1771     &$parsedString, $defaultGreeting,
1772     $addressMergeGreetings, $greetingType = 'postal_greeting'
1773   ) {
1774     if (!empty($addressMergeGreetings[$greetingType])) {
1775       $greetingLabel = $addressMergeGreetings[$greetingType];
1776     }
1777     $greetingLabel = empty($greetingLabel) ? $defaultGreeting : $greetingLabel;
1778
1779     $stringsToBeReplaced = preg_replace('/(\{[a-zA-Z._ ]+\})/', ';;', $greetingLabel);
1780     $stringsToBeReplaced = explode(';;', $stringsToBeReplaced);
1781     foreach ($stringsToBeReplaced as $key => $string) {
1782       // to keep one space
1783       $stringsToBeReplaced[$key] = ltrim($string);
1784     }
1785     $parsedString = str_replace($stringsToBeReplaced, "", $parsedString);
1786
1787     return $parsedString;
1788   }
1789
1790 }