CRM/Export/BAO/Export.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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-2018
  32  */
  33
  34 /**
  35  * This class contains the functions for Component export
  36  *
  37  */
  38 class CRM_Export_BAO_Export {
  39   // increase this number a lot to avoid making too many queries
  40   // LIMIT is not much faster than a no LIMIT query
  41   // CRM-7675
  42   const EXPORT_ROW_COUNT = 100000;
  43
  44   protected static $relationshipReturnProperties = [];
  45
  46   /**
  47    * Key representing the head of household in the relationship array.
  48    *
  49    * e.g. 8_a_b.
  50    *
  51    * @var string
  52    */
  53   protected static $headOfHouseholdRelationshipKey;
  54
  55   /**
  56    * Key representing the head of household in the relationship array.
  57    *
  58    * e.g. 8_a_b.
  59    *
  60    * @var string
  61    */
  62   protected static $memberOfHouseholdRelationshipKey;
  63
  64   /**
  65    * Key representing the head of household in the relationship array.
  66    *
  67    * e.g. ['8_b_a' => 'Household Member Is', '8_a_b = 'Household Member Of'.....]
  68    *
  69    * @var
  70    */
  71   protected static $relationshipTypes = [];
  72
  73   /**
  74    * @param $value
  75    * @param $locationTypeFields
  76    * @param $relationshipTypes
  77    *
  78    * @return array
  79    */
  80   protected static function setRelationshipReturnProperties($value, $locationTypeFields, $relationshipTypes) {
  81     $locationTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
  82     $relPhoneTypeId = $relIMProviderId = NULL;
  83     if (!empty($value[2])) {
  84       $relationField = CRM_Utils_Array::value(2, $value);
  85       if (trim(CRM_Utils_Array::value(3, $value))) {
  86         $relLocTypeId = CRM_Utils_Array::value(3, $value);
  87       }
  88       else {
  89         $relLocTypeId = 'Primary';
  90       }
  91
  92       if ($relationField == 'phone') {
  93         $relPhoneTypeId = CRM_Utils_Array::value(4, $value);
  94       }
  95       elseif ($relationField == 'im') {
  96         $relIMProviderId = CRM_Utils_Array::value(4, $value);
  97       }
  98     }
  99     elseif (!empty($value[4])) {
 100       $relationField = CRM_Utils_Array::value(4, $value);
 101       $relLocTypeId = CRM_Utils_Array::value(5, $value);
 102       if ($relationField == 'phone') {
 103         $relPhoneTypeId = CRM_Utils_Array::value(6, $value);
 104       }
 105       elseif ($relationField == 'im') {
 106         $relIMProviderId = CRM_Utils_Array::value(6, $value);
 107       }
 108     }
 109     if (in_array($relationField, $locationTypeFields) && is_numeric($relLocTypeId)) {
 110       if ($relPhoneTypeId) {
 111         self::$relationshipReturnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]]['phone-' . $relPhoneTypeId] = 1;
 112       }
 113       elseif ($relIMProviderId) {
 114         self::$relationshipReturnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]]['im-' . $relIMProviderId] = 1;
 115       }
 116       else {
 117         self::$relationshipReturnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]][$relationField] = 1;
 118       }
 119     }
 120     else {
 121       self::$relationshipReturnProperties[$relationshipTypes][$relationField] = 1;
 122     }
 123     return array($relationField);
 124   }
 125
 126   /**
 127    * @return array
 128    */
 129   public static function getRelationshipReturnProperties() {
 130     return self::relationshipReturnProperties;
 131   }
 132
 133   /**
 134    * Get default return property for export based on mode
 135    *
 136    * @param int $exportMode
 137    *   Export mode.
 138    *
 139    * @return string $property
 140    *   Default Return property
 141    */
 142   public static function defaultReturnProperty($exportMode) {
 143     // hack to add default return property based on export mode
 144     $property = NULL;
 145     if ($exportMode == CRM_Export_Form_Select::CONTRIBUTE_EXPORT) {
 146       $property = 'contribution_id';
 147     }
 148     elseif ($exportMode == CRM_Export_Form_Select::EVENT_EXPORT) {
 149       $property = 'participant_id';
 150     }
 151     elseif ($exportMode == CRM_Export_Form_Select::MEMBER_EXPORT) {
 152       $property = 'membership_id';
 153     }
 154     elseif ($exportMode == CRM_Export_Form_Select::PLEDGE_EXPORT) {
 155       $property = 'pledge_id';
 156     }
 157     elseif ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) {
 158       $property = 'case_id';
 159     }
 160     elseif ($exportMode == CRM_Export_Form_Select::GRANT_EXPORT) {
 161       $property = 'grant_id';
 162     }
 163     elseif ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) {
 164       $property = 'activity_id';
 165     }
 166     return $property;
 167   }
 168
 169   /**
 170    * Get Export component
 171    *
 172    * @param int $exportMode
 173    *   Export mode.
 174    *
 175    * @return string $component
 176    *   CiviCRM Export Component
 177    */
 178   public static function exportComponent($exportMode) {
 179     switch ($exportMode) {
 180       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
 181         $component = 'civicrm_contribution';
 182         break;
 183
 184       case CRM_Export_Form_Select::EVENT_EXPORT:
 185         $component = 'civicrm_participant';
 186         break;
 187
 188       case CRM_Export_Form_Select::MEMBER_EXPORT:
 189         $component = 'civicrm_membership';
 190         break;
 191
 192       case CRM_Export_Form_Select::PLEDGE_EXPORT:
 193         $component = 'civicrm_pledge';
 194         break;
 195
 196       case CRM_Export_Form_Select::GRANT_EXPORT:
 197         $component = 'civicrm_grant';
 198         break;
 199     }
 200     return $component;
 201   }
 202
 203   /**
 204    * Get Query Group By Clause
 205    * @param int $exportMode
 206    *   Export Mode
 207    * @param string $queryMode
 208    *   Query Mode
 209    * @param array $returnProperties
 210    *   Return Properties
 211    * @param object $query
 212    *   CRM_Contact_BAO_Query
 213    *
 214    * @return string $groupBy
 215    *   Group By Clause
 216    */
 217   public static function getGroupBy($exportMode, $queryMode, $returnProperties, $query) {
 218     $groupBy = '';
 219     if (!empty($returnProperties['tags']) || !empty($returnProperties['groups']) ||
 220       CRM_Utils_Array::value('notes', $returnProperties) ||
 221       // CRM-9552
 222       ($queryMode & CRM_Contact_BAO_Query::MODE_CONTACTS && $query->_useGroupBy)
 223     ) {
 224       $groupBy = "contact_a.id";
 225     }
 226
 227     switch ($exportMode) {
 228       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
 229         $groupBy = 'civicrm_contribution.id';
 230         if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
 231           // especial group by  when soft credit columns are included
 232           $groupBy = array('contribution_search_scredit_combined.id', 'contribution_search_scredit_combined.scredit_id');
 233         }
 234         break;
 235
 236       case CRM_Export_Form_Select::EVENT_EXPORT:
 237         $groupBy = 'civicrm_participant.id';
 238         break;
 239
 240       case CRM_Export_Form_Select::MEMBER_EXPORT:
 241         $groupBy = "civicrm_membership.id";
 242         break;
 243     }
 244
 245     if ($queryMode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
 246       $groupBy = "civicrm_activity.id ";
 247     }
 248
 249     if (!empty($groupBy)) {
 250       if (!Civi::settings()->get('searchPrimaryDetailsOnly')) {
 251         CRM_Core_DAO::disableFullGroupByMode();
 252       }
 253       $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($query->_select, $groupBy);
 254     }
 255
 256     return $groupBy;
 257   }
 258
 259   /**
 260    * Define extra properties for the export based on query mode
 261    *
 262    * @param string $queryMode
 263    *   Query Mode
 264    * @return array $extraProperties
 265    *   Extra Properties
 266    */
 267   public static function defineExtraProperties($queryMode) {
 268     switch ($queryMode) {
 269       case CRM_Contact_BAO_Query::MODE_EVENT:
 270         $paymentFields = TRUE;
 271         $paymentTableId = 'participant_id';
 272         $extraReturnProperties = array();
 273         break;
 274
 275       case CRM_Contact_BAO_Query::MODE_MEMBER:
 276         $paymentFields = TRUE;
 277         $paymentTableId = 'membership_id';
 278         $extraReturnProperties = array();
 279         break;
 280
 281       case CRM_Contact_BAO_Query::MODE_PLEDGE:
 282         $extraReturnProperties = CRM_Pledge_BAO_Query::extraReturnProperties($queryMode);
 283         $paymentFields = TRUE;
 284         $paymentTableId = 'pledge_payment_id';
 285         break;
 286
 287       case CRM_Contact_BAO_Query::MODE_CASE:
 288         $extraReturnProperties = CRM_Case_BAO_Query::extraReturnProperties($queryMode);
 289         $paymentFields = FALSE;
 290         $paymentTableId = '';
 291         break;
 292
 293       default:
 294         $paymentFields = FALSE;
 295         $paymentTableId = '';
 296         $extraReturnProperties = array();
 297     }
 298     $extraProperties = array(
 299       'paymentFields' => $paymentFields,
 300       'paymentTableId' => $paymentTableId,
 301       'extraReturnProperties' => $extraReturnProperties,
 302     );
 303     return $extraProperties;
 304   }
 305
 306   /**
 307    * Get the list the export fields.
 308    *
 309    * @param int $selectAll
 310    *   User preference while export.
 311    * @param array $ids
 312    *   Contact ids.
 313    * @param array $params
 314    *   Associated array of fields.
 315    * @param string $order
 316    *   Order by clause.
 317    * @param array $fields
 318    *   Associated array of fields.
 319    * @param array $moreReturnProperties
 320    *   Additional return fields.
 321    * @param int $exportMode
 322    *   Export mode.
 323    * @param string $componentClause
 324    *   Component clause.
 325    * @param string $componentTable
 326    *   Component table.
 327    * @param bool $mergeSameAddress
 328    *   Merge records if they have same address.
 329    * @param bool $mergeSameHousehold
 330    *   Merge records if they belong to the same household.
 331    *
 332    * @param array $exportParams
 333    * @param string $queryOperator
 334    *
 335    * @return array|null
 336    *   An array can be requested from within a unit test.
 337    *
 338    * @throws \CRM_Core_Exception
 339    */
 340   public static function exportComponents(
 341     $selectAll,
 342     $ids,
 343     $params,
 344     $order = NULL,
 345     $fields = NULL,
 346     $moreReturnProperties = NULL,
 347     $exportMode = CRM_Export_Form_Select::CONTACT_EXPORT,
 348     $componentClause = NULL,
 349     $componentTable = NULL,
 350     $mergeSameAddress = FALSE,
 351     $mergeSameHousehold = FALSE,
 352     $exportParams = array(),
 353     $queryOperator = 'AND'
 354   ) {
 355
 356     $processor = new CRM_Export_BAO_ExportProcessor($exportMode, $queryOperator);
 357     $returnProperties = array();
 358     $paymentFields = $selectedPaymentFields = FALSE;
 359
 360     $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id');
 361     // Warning - this imProviders var is used in a somewhat fragile way - don't rename it
 362     // without manually testing the export of IM provider still works.
 363     $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id');
 364     self::$relationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(
 365       NULL,
 366       NULL,
 367       NULL,
 368       NULL,
 369       TRUE,
 370       'name',
 371       FALSE
 372     );
 373     //also merge Head of Household
 374     self::$memberOfHouseholdRelationshipKey = CRM_Utils_Array::key('Household Member of', self::$relationshipTypes);
 375     self::$headOfHouseholdRelationshipKey = CRM_Utils_Array::key('Head of Household for', self::$relationshipTypes);
 376
 377     $queryMode = $processor->getQueryMode();
 378
 379     if ($fields) {
 380       //construct return properties
 381       $locationTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
 382       $locationTypeFields = array(
 383         'street_address',
 384         'supplemental_address_1',
 385         'supplemental_address_2',
 386         'supplemental_address_3',
 387         'city',
 388         'postal_code',
 389         'postal_code_suffix',
 390         'geo_code_1',
 391         'geo_code_2',
 392         'state_province',
 393         'country',
 394         'phone',
 395         'email',
 396         'im',
 397       );
 398
 399       foreach ($fields as $key => $value) {
 400         $fieldName = CRM_Utils_Array::value(1, $value);
 401         if (!$fieldName) {
 402           continue;
 403         }
 404
 405         if (array_key_exists($fieldName, self::$relationshipTypes) && (!empty($value[2]) || !empty($value[4]))) {
 406           self::setRelationshipReturnProperties($value, $locationTypeFields, $fieldName);
 407           // @todo we can later not add this to this array but maintain a separate array.
 408           $returnProperties = array_merge($returnProperties, self::$relationshipReturnProperties);
 409         }
 410         elseif (is_numeric(CRM_Utils_Array::value(2, $value))) {
 411           $locTypeId = $value[2];
 412           if ($fieldName == 'phone') {
 413             $returnProperties['location'][$locationTypes[$locTypeId]]['phone-' . CRM_Utils_Array::value(3, $value)] = 1;
 414           }
 415           elseif ($fieldName == 'im') {
 416             $returnProperties['location'][$locationTypes[$locTypeId]]['im-' . CRM_Utils_Array::value(3, $value)] = 1;
 417           }
 418           else {
 419             $returnProperties['location'][$locationTypes[$locTypeId]][$fieldName] = 1;
 420           }
 421         }
 422         else {
 423           //hack to fix component fields
 424           //revert mix of event_id and title
 425           if ($fieldName == 'event_id') {
 426             $returnProperties['event_id'] = 1;
 427           }
 428           elseif (
 429             $exportMode == CRM_Export_Form_Select::EVENT_EXPORT &&
 430             array_key_exists($fieldName, self::componentPaymentFields())
 431           ) {
 432             $selectedPaymentFields = TRUE;
 433             $paymentTableId = 'participant_id';
 434             $returnProperties[$fieldName] = 1;
 435           }
 436           else {
 437             $returnProperties[$fieldName] = 1;
 438           }
 439         }
 440       }
 441       $defaultExportMode = self::defaultReturnProperty($exportMode);
 442       if ($defaultExportMode) {
 443         $returnProperties[$defaultExportMode] = 1;
 444       }
 445     }
 446     else {
 447       $returnProperties = [
 448         'location_type' => 1,
 449         'im_provider' => 1,
 450         'phone_type_id' => 1,
 451         'provider_id' => 1,
 452         'current_employer' => 1,
 453        ];
 454       $fields = CRM_Contact_BAO_Contact::exportableFields('All', TRUE, TRUE);
 455       foreach ($fields as $key => $var) {
 456         if ($key && (substr($key, 0, 6) != 'custom')) {
 457           //for CRM=952
 458           $returnProperties[$key] = 1;
 459         }
 460       }
 461
 462       $extraProperties = self::defineExtraProperties($queryMode);
 463       $paymentFields = $extraProperties['paymentFields'];
 464       $extraReturnProperties = $extraProperties['extraReturnProperties'];
 465       $paymentTableId = $extraProperties['paymentTableId'];
 466
 467       if ($queryMode != CRM_Contact_BAO_Query::MODE_CONTACTS) {
 468         $componentReturnProperties = CRM_Contact_BAO_Query::defaultReturnProperties($queryMode);
 469         if ($queryMode == CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
 470           // soft credit columns are not automatically populated, because contribution search doesn't require them by default
 471           $componentReturnProperties = array_merge(
 472               $componentReturnProperties,
 473               CRM_Contribute_BAO_Query::softCreditReturnProperties(TRUE));
 474         }
 475         $returnProperties = array_merge($returnProperties, $componentReturnProperties);
 476
 477         if (!empty($extraReturnProperties)) {
 478           $returnProperties = array_merge($returnProperties, $extraReturnProperties);
 479         }
 480
 481         // unset non exportable fields for components
 482         $nonExpoFields = array(
 483           'groups',
 484           'tags',
 485           'notes',
 486           'contribution_status_id',
 487           'pledge_status_id',
 488           'pledge_payment_status_id',
 489         );
 490         foreach ($nonExpoFields as $value) {
 491           unset($returnProperties[$value]);
 492         }
 493       }
 494     }
 495
 496     if ($mergeSameAddress) {
 497       //make sure the addressee fields are selected
 498       //while using merge same address feature
 499       $returnProperties['addressee'] = 1;
 500       $returnProperties['postal_greeting'] = 1;
 501       $returnProperties['email_greeting'] = 1;
 502       $returnProperties['street_name'] = 1;
 503       $returnProperties['household_name'] = 1;
 504       $returnProperties['street_address'] = 1;
 505       $returnProperties['city'] = 1;
 506       $returnProperties['state_province'] = 1;
 507
 508       // some columns are required for assistance incase they are not already present
 509       $exportParams['merge_same_address']['temp_columns'] = array();
 510       $tempColumns = array('id', 'master_id', 'state_province_id', 'postal_greeting_id', 'addressee_id');
 511       foreach ($tempColumns as $column) {
 512         if (!array_key_exists($column, $returnProperties)) {
 513           $returnProperties[$column] = 1;
 514           $column = $column == 'id' ? 'civicrm_primary_id' : $column;
 515           $exportParams['merge_same_address']['temp_columns'][$column] = 1;
 516         }
 517       }
 518     }
 519
 520     if (!$selectAll && $componentTable && !empty($exportParams['additional_group'])) {
 521       // If an Additional Group is selected, then all contacts in that group are
 522       // added to the export set (filtering out duplicates).
 523       $query = "
 524 INSERT INTO {$componentTable} SELECT distinct gc.contact_id FROM civicrm_group_contact gc WHERE gc.group_id = {$exportParams['additional_group']} ON DUPLICATE KEY UPDATE {$componentTable}.contact_id = gc.contact_id";
 525       CRM_Core_DAO::executeQuery($query);
 526     }
 527
 528     if ($moreReturnProperties) {
 529       // fix for CRM-7066
 530       if (!empty($moreReturnProperties['group'])) {
 531         unset($moreReturnProperties['group']);
 532         $moreReturnProperties['groups'] = 1;
 533       }
 534       $returnProperties = array_merge($returnProperties, $moreReturnProperties);
 535     }
 536
 537     $exportParams['postal_mailing_export']['temp_columns'] = array();
 538     if ($exportParams['exportOption'] == 2 &&
 539       isset($exportParams['postal_mailing_export']) &&
 540       CRM_Utils_Array::value('postal_mailing_export', $exportParams['postal_mailing_export']) == 1
 541     ) {
 542       $postalColumns = array('is_deceased', 'do_not_mail', 'street_address', 'supplemental_address_1');
 543       foreach ($postalColumns as $column) {
 544         if (!array_key_exists($column, $returnProperties)) {
 545           $returnProperties[$column] = 1;
 546           $exportParams['postal_mailing_export']['temp_columns'][$column] = 1;
 547         }
 548       }
 549     }
 550
 551     // rectify params to what proximity search expects if there is a value for prox_distance
 552     // CRM-7021
 553     if (!empty($params)) {
 554       CRM_Contact_BAO_ProximityQuery::fixInputParams($params);
 555     }
 556
 557     list($query, $select, $from, $where, $having) = $processor->runQuery($params, $order, $returnProperties);
 558
 559     if ($mergeSameHousehold == 1) {
 560       if (empty($returnProperties['id'])) {
 561         $returnProperties['id'] = 1;
 562       }
 563
 564       foreach ($returnProperties as $key => $value) {
 565         if (!array_key_exists($key, self::$relationshipTypes)) {
 566           $returnProperties[self::$memberOfHouseholdRelationshipKey][$key] = $value;
 567           $returnProperties[self::$headOfHouseholdRelationshipKey][$key] = $value;
 568         }
 569       }
 570
 571       unset($returnProperties[self::$memberOfHouseholdRelationshipKey]['location_type']);
 572       unset($returnProperties[self::$memberOfHouseholdRelationshipKey]['im_provider']);
 573       unset($returnProperties[self::$headOfHouseholdRelationshipKey]['location_type']);
 574       unset($returnProperties[self::$headOfHouseholdRelationshipKey]['im_provider']);
 575     }
 576
 577     list($relationQuery, $allRelContactArray) = self::buildRelatedContactArray($selectAll, $ids, $exportMode, $componentTable, $returnProperties, $queryMode);
 578
 579     // make sure the groups stuff is included only if specifically specified
 580     // by the fields param (CRM-1969), else we limit the contacts outputted to only
 581     // ones that are part of a group
 582     if (!empty($returnProperties['groups'])) {
 583       $oldClause = "( contact_a.id = civicrm_group_contact.contact_id )";
 584       $newClause = " ( $oldClause AND ( civicrm_group_contact.status = 'Added' OR civicrm_group_contact.status IS NULL ) )";
 585       // total hack for export, CRM-3618
 586       $from = str_replace($oldClause,
 587         $newClause,
 588         $from
 589       );
 590     }
 591
 592     if (!$selectAll && $componentTable) {
 593       $from .= " INNER JOIN $componentTable ctTable ON ctTable.contact_id = contact_a.id ";
 594     }
 595     elseif ($componentClause) {
 596       if (empty($where)) {
 597         $where = "WHERE $componentClause";
 598       }
 599       else {
 600         $where .= " AND $componentClause";
 601       }
 602     }
 603
 604     // CRM-13982 - check if is deleted
 605     $excludeTrashed = TRUE;
 606     foreach ($params as $value) {
 607       if ($value[0] == 'contact_is_deleted') {
 608         $excludeTrashed = FALSE;
 609       }
 610     }
 611     $trashClause = $excludeTrashed ? "contact_a.is_deleted != 1" : "( 1 )";
 612
 613     if (empty($where)) {
 614       $where = "WHERE $trashClause";
 615     }
 616     else {
 617       $where .= " AND $trashClause";
 618     }
 619
 620     $queryString = "$select $from $where $having";
 621
 622     $groupBy = self::getGroupBy($exportMode, $queryMode, $returnProperties, $query);
 623
 624     $queryString .= $groupBy;
 625
 626     if ($order) {
 627       // always add contact_a.id to the ORDER clause
 628       // so the order is deterministic
 629       //CRM-15301
 630       if (strpos('contact_a.id', $order) === FALSE) {
 631         $order .= ", contact_a.id";
 632       }
 633
 634       list($field, $dir) = explode(' ', $order, 2);
 635       $field = trim($field);
 636       if (!empty($returnProperties[$field])) {
 637         //CRM-15301
 638         $queryString .= " ORDER BY $order";
 639       }
 640     }
 641
 642     $addPaymentHeader = FALSE;
 643
 644     $paymentDetails = array();
 645     if ($paymentFields || $selectedPaymentFields) {
 646
 647       // get payment related in for event and members
 648       $paymentDetails = CRM_Contribute_BAO_Contribution::getContributionDetails($exportMode, $ids);
 649       //get all payment headers.
 650       // If we haven't selected specific payment fields, load in all the
 651       // payment headers.
 652       if (!$selectedPaymentFields) {
 653         $paymentHeaders = self::componentPaymentFields();
 654         if (!empty($paymentDetails)) {
 655           $addPaymentHeader = TRUE;
 656         }
 657       }
 658       // If we have selected specific payment fields, leave the payment headers
 659       // as an empty array; the headers for each selected field will be added
 660       // elsewhere.
 661       else {
 662         $paymentHeaders = array();
 663       }
 664       $nullContributionDetails = array_fill_keys(array_keys($paymentHeaders), NULL);
 665     }
 666
 667     $componentDetails = array();
 668     $setHeader = TRUE;
 669
 670     $rowCount = self::EXPORT_ROW_COUNT;
 671     $offset = 0;
 672     // we write to temp table often to avoid using too much memory
 673     $tempRowCount = 100;
 674
 675     $count = -1;
 676
 677     // for CRM-3157 purposes
 678     $i18n = CRM_Core_I18n::singleton();
 679
 680     list($outputColumns, $headerRows, $sqlColumns, $metadata) = self::getExportStructureArrays($returnProperties, $processor, $relationQuery, $selectedPaymentFields);
 681
 682     $limitReached = FALSE;
 683     while (!$limitReached) {
 684       $limitQuery = "{$queryString} LIMIT {$offset}, {$rowCount}";
 685       $iterationDAO = CRM_Core_DAO::executeQuery($limitQuery);
 686       // If this is less than our limit by the end of the iteration we do not need to run the query again to
 687       // check if some remain.
 688       $rowsThisIteration = 0;
 689
 690       while ($iterationDAO->fetch()) {
 691         $count++;
 692         $rowsThisIteration++;
 693         $row = array();
 694         $query->convertToPseudoNames($iterationDAO);
 695
 696         //first loop through output columns so that we return what is required, and in same order.
 697         foreach ($outputColumns as $field => $value) {
 698
 699           // add im_provider to $dao object
 700           if ($field == 'im_provider' && property_exists($iterationDAO, 'provider_id')) {
 701             $iterationDAO->im_provider = $iterationDAO->provider_id;
 702           }
 703
 704           //build row values (data)
 705           $fieldValue = NULL;
 706           if (property_exists($iterationDAO, $field)) {
 707             $fieldValue = $iterationDAO->$field;
 708             // to get phone type from phone type id
 709             if ($field == 'phone_type_id' && isset($phoneTypes[$fieldValue])) {
 710               $fieldValue = $phoneTypes[$fieldValue];
 711             }
 712             elseif ($field == 'provider_id' || $field == 'im_provider') {
 713               $fieldValue = CRM_Utils_Array::value($fieldValue, $imProviders);
 714             }
 715             elseif (strstr($field, 'master_id')) {
 716               $masterAddressId = NULL;
 717               if (isset($iterationDAO->$field)) {
 718                 $masterAddressId = $iterationDAO->$field;
 719               }
 720               // get display name of contact that address is shared.
 721               $fieldValue = CRM_Contact_BAO_Contact::getMasterDisplayName($masterAddressId);
 722             }
 723           }
 724
 725           if ($field == 'id') {
 726             $row[$field] = $iterationDAO->contact_id;
 727             // special case for calculated field
 728           }
 729           elseif ($field == 'source_contact_id') {
 730             $row[$field] = $iterationDAO->contact_id;
 731           }
 732           elseif ($field == 'pledge_balance_amount') {
 733             $row[$field] = $iterationDAO->pledge_amount - $iterationDAO->pledge_total_paid;
 734             // special case for calculated field
 735           }
 736           elseif ($field == 'pledge_next_pay_amount') {
 737             $row[$field] = $iterationDAO->pledge_next_pay_amount + $iterationDAO->pledge_outstanding_amount;
 738           }
 739           elseif (array_key_exists($field, self::$relationshipTypes)) {
 740             $relDAO = CRM_Utils_Array::value($iterationDAO->contact_id, $allRelContactArray[$field]);
 741             $relationQuery[$field]->convertToPseudoNames($relDAO);
 742             self::fetchRelationshipDetails($relDAO, $value, $field, $row);
 743           }
 744           elseif (isset($fieldValue) &&
 745             $fieldValue != ''
 746           ) {
 747             //check for custom data
 748             if ($cfID = CRM_Core_BAO_CustomField::getKeyID($field)) {
 749               $row[$field] = CRM_Core_BAO_CustomField::displayValue($fieldValue, $cfID);
 750             }
 751
 752             elseif (in_array($field, array(
 753               'email_greeting',
 754               'postal_greeting',
 755               'addressee',
 756             ))) {
 757               //special case for greeting replacement
 758               $fldValue = "{$field}_display";
 759               $row[$field] = $iterationDAO->$fldValue;
 760             }
 761             else {
 762               //normal fields with a touch of CRM-3157
 763               switch ($field) {
 764                 case 'country':
 765                 case 'world_region':
 766                   $row[$field] = $i18n->crm_translate($fieldValue, array('context' => 'country'));
 767                   break;
 768
 769                 case 'state_province':
 770                   $row[$field] = $i18n->crm_translate($fieldValue, array('context' => 'province'));
 771                   break;
 772
 773                 case 'gender':
 774                 case 'preferred_communication_method':
 775                 case 'preferred_mail_format':
 776                 case 'communication_style':
 777                   $row[$field] = $i18n->crm_translate($fieldValue);
 778                   break;
 779
 780                 default:
 781                   if (isset($metadata[$field])) {
 782                     // No I don't know why we do it this way & whether we could
 783                     // make better use of pseudoConstants.
 784                     if (!empty($metadata[$field]['context'])) {
 785                       $row[$field] = $i18n->crm_translate($fieldValue, $metadata[$field]);
 786                       break;
 787                     }
 788                     if (!empty($metadata[$field]['pseudoconstant'])) {
 789                       // This is not our normal syntax for pseudoconstants but I am a bit loath to
 790                       // call an external function until sure it is not increasing php processing given this
 791                       // may be iterated 100,000 times & we already have the $imProvider var loaded.
 792                       // That can be next refactor...
 793                       // Yes - definitely feeling hatred for this bit of code - I know you will beat me up over it's awfulness
 794                       // but I have to reach a stable point....
 795                       $varName = $metadata[$field]['pseudoconstant']['var'];
 796                       $labels = $$varName;
 797                       $row[$field] = $labels[$fieldValue];
 798                       break;
 799                     }
 800
 801                   }
 802                   $row[$field] = $fieldValue;
 803                   break;
 804               }
 805             }
 806           }
 807           elseif ($selectedPaymentFields && array_key_exists($field, self::componentPaymentFields())) {
 808             $paymentData = CRM_Utils_Array::value($iterationDAO->$paymentTableId, $paymentDetails);
 809             $payFieldMapper = array(
 810               'componentPaymentField_total_amount' => 'total_amount',
 811               'componentPaymentField_contribution_status' => 'contribution_status',
 812               'componentPaymentField_payment_instrument' => 'pay_instru',
 813               'componentPaymentField_transaction_id' => 'trxn_id',
 814               'componentPaymentField_received_date' => 'receive_date',
 815             );
 816             $row[$field] = CRM_Utils_Array::value($payFieldMapper[$field], $paymentData, '');
 817           }
 818           else {
 819             // if field is empty or null
 820             $row[$field] = '';
 821           }
 822         }
 823
 824         // add payment headers if required
 825         if ($addPaymentHeader && $paymentFields) {
 826           // @todo rather than do this for every single row do it before the loop starts.
 827           // where other header definitions take place.
 828           $headerRows = array_merge($headerRows, $paymentHeaders);
 829           foreach (array_keys($paymentHeaders) as $paymentHdr) {
 830             self::sqlColumnDefn($processor, $sqlColumns, $paymentHdr);
 831           }
 832         }
 833
 834         if ($setHeader) {
 835           $exportTempTable = self::createTempTable($sqlColumns);
 836         }
 837
 838         //build header only once
 839         $setHeader = FALSE;
 840
 841         // If specific payment fields have been selected for export, payment
 842         // data will already be in $row. Otherwise, add payment related
 843         // information, if appropriate.
 844         if ($addPaymentHeader) {
 845           if (!$selectedPaymentFields) {
 846             if ($paymentFields) {
 847               $paymentData = CRM_Utils_Array::value($row[$paymentTableId], $paymentDetails);
 848               if (!is_array($paymentData) || empty($paymentData)) {
 849                 $paymentData = $nullContributionDetails;
 850               }
 851               $row = array_merge($row, $paymentData);
 852             }
 853             elseif (!empty($paymentDetails)) {
 854               $row = array_merge($row, $nullContributionDetails);
 855             }
 856           }
 857         }
 858         //remove organization name for individuals if it is set for current employer
 859         if (!empty($row['contact_type']) &&
 860           $row['contact_type'] == 'Individual' && array_key_exists('organization_name', $row)
 861         ) {
 862           $row['organization_name'] = '';
 863         }
 864
 865         // add component info
 866         // write the row to a file
 867         $componentDetails[] = $row;
 868
 869         // output every $tempRowCount rows
 870         if ($count % $tempRowCount == 0) {
 871           self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns);
 872           $componentDetails = array();
 873         }
 874       }
 875       if ($rowsThisIteration < self::EXPORT_ROW_COUNT) {
 876         $limitReached = TRUE;
 877       }
 878       $offset += $rowCount;
 879     }
 880
 881     if ($exportTempTable) {
 882       self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns);
 883
 884       // if postalMailing option is checked, exclude contacts who are deceased, have
 885       // "Do not mail" privacy setting, or have no street address
 886       if (isset($exportParams['postal_mailing_export']['postal_mailing_export']) &&
 887         $exportParams['postal_mailing_export']['postal_mailing_export'] == 1
 888       ) {
 889         self::postalMailingFormat($exportTempTable, $headerRows, $sqlColumns, $exportMode);
 890       }
 891
 892       // do merge same address and merge same household processing
 893       if ($mergeSameAddress) {
 894         self::mergeSameAddress($exportTempTable, $headerRows, $sqlColumns, $exportParams);
 895       }
 896
 897       // merge the records if they have corresponding households
 898       if ($mergeSameHousehold) {
 899         self::mergeSameHousehold($exportTempTable, $headerRows, $sqlColumns, self::$memberOfHouseholdRelationshipKey);
 900         self::mergeSameHousehold($exportTempTable, $headerRows, $sqlColumns, self::$headOfHouseholdRelationshipKey);
 901       }
 902
 903       // call export hook
 904       CRM_Utils_Hook::export($exportTempTable, $headerRows, $sqlColumns, $exportMode);
 905
 906       // In order to be able to write a unit test against this function we need to suppress
 907       // the csv writing. In future hopefully the csv writing & the main processing will be in separate functions.
 908       if (empty($exportParams['suppress_csv_for_testing'])) {
 909         self::writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $exportMode);
 910       }
 911       else {
 912         // return tableName and sqlColumns in test context
 913         return array($exportTempTable, $sqlColumns);
 914       }
 915
 916       // delete the export temp table and component table
 917       $sql = "DROP TABLE IF EXISTS {$exportTempTable}";
 918       CRM_Core_DAO::executeQuery($sql);
 919       CRM_Core_DAO::reenableFullGroupByMode();
 920       CRM_Utils_System::civiExit();
 921     }
 922     else {
 923       CRM_Core_DAO::reenableFullGroupByMode();
 924       throw new CRM_Core_Exception(ts('No records to export'));
 925     }
 926   }
 927
 928   /**
 929    * Name of the export file based on mode.
 930    *
 931    * @param string $output
 932    *   Type of output.
 933    * @param int $mode
 934    *   Export mode.
 935    *
 936    * @return string
 937    *   name of the file
 938    */
 939   public static function getExportFileName($output = 'csv', $mode = CRM_Export_Form_Select::CONTACT_EXPORT) {
 940     switch ($mode) {
 941       case CRM_Export_Form_Select::CONTACT_EXPORT:
 942         return ts('CiviCRM Contact Search');
 943
 944       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
 945         return ts('CiviCRM Contribution Search');
 946
 947       case CRM_Export_Form_Select::MEMBER_EXPORT:
 948         return ts('CiviCRM Member Search');
 949
 950       case CRM_Export_Form_Select::EVENT_EXPORT:
 951         return ts('CiviCRM Participant Search');
 952
 953       case CRM_Export_Form_Select::PLEDGE_EXPORT:
 954         return ts('CiviCRM Pledge Search');
 955
 956       case CRM_Export_Form_Select::CASE_EXPORT:
 957         return ts('CiviCRM Case Search');
 958
 959       case CRM_Export_Form_Select::GRANT_EXPORT:
 960         return ts('CiviCRM Grant Search');
 961
 962       case CRM_Export_Form_Select::ACTIVITY_EXPORT:
 963         return ts('CiviCRM Activity Search');
 964     }
 965   }
 966
 967   /**
 968    * Handle import error file creation.
 969    */
 970   public static function invoke() {
 971     $type = CRM_Utils_Request::retrieve('type', 'Positive');
 972     $parserName = CRM_Utils_Request::retrieve('parser', 'String');
 973     if (empty($parserName) || empty($type)) {
 974       return;
 975     }
 976
 977     // clean and ensure parserName is a valid string
 978     $parserName = CRM_Utils_String::munge($parserName);
 979     $parserClass = explode('_', $parserName);
 980
 981     // make sure parserClass is in the CRM namespace and
 982     // at least 3 levels deep
 983     if ($parserClass[0] == 'CRM' &&
 984       count($parserClass) >= 3
 985     ) {
 986       require_once str_replace('_', DIRECTORY_SEPARATOR, $parserName) . ".php";
 987       // ensure the functions exists
 988       if (method_exists($parserName, 'errorFileName') &&
 989         method_exists($parserName, 'saveFileName')
 990       ) {
 991         $errorFileName = $parserName::errorFileName($type);
 992         $saveFileName = $parserName::saveFileName($type);
 993         if (!empty($errorFileName) && !empty($saveFileName)) {
 994           CRM_Utils_System::setHttpHeader('Cache-Control', 'must-revalidate, post-check=0, pre-check=0');
 995           CRM_Utils_System::setHttpHeader('Content-Description', 'File Transfer');
 996           CRM_Utils_System::setHttpHeader('Content-Type', 'text/csv');
 997           CRM_Utils_System::setHttpHeader('Content-Length', filesize($errorFileName));
 998           CRM_Utils_System::setHttpHeader('Content-Disposition', 'attachment; filename=' . $saveFileName);
 999
1000           readfile($errorFileName);
1001         }
1002       }
1003     }
1004     CRM_Utils_System::civiExit();
1005   }
1006
1007   /**
1008    * @param $customSearchClass
1009    * @param $formValues
1010    * @param $order
1011    */
1012   public static function exportCustom($customSearchClass, $formValues, $order) {
1013     $ext = CRM_Extension_System::singleton()->getMapper();
1014     if (!$ext->isExtensionClass($customSearchClass)) {
1015       require_once str_replace('_', DIRECTORY_SEPARATOR, $customSearchClass) . '.php';
1016     }
1017     else {
1018       require_once $ext->classToPath($customSearchClass);
1019     }
1020     $search = new $customSearchClass($formValues);
1021
1022     $includeContactIDs = FALSE;
1023     if ($formValues['radio_ts'] == 'ts_sel') {
1024       $includeContactIDs = TRUE;
1025     }
1026
1027     $sql = $search->all(0, 0, $order, $includeContactIDs);
1028
1029     $columns = $search->columns();
1030
1031     $header = array_keys($columns);
1032     $fields = array_values($columns);
1033
1034     $rows = array();
1035     $dao = CRM_Core_DAO::executeQuery($sql);
1036     $alterRow = FALSE;
1037     if (method_exists($search, 'alterRow')) {
1038       $alterRow = TRUE;
1039     }
1040     while ($dao->fetch()) {
1041       $row = array();
1042
1043       foreach ($fields as $field) {
1044         $unqualified_field = CRM_Utils_Array::First(array_slice(explode('.', $field), -1));
1045         $row[$field] = $dao->$unqualified_field;
1046       }
1047       if ($alterRow) {
1048         $search->alterRow($row);
1049       }
1050       $rows[] = $row;
1051     }
1052
1053     CRM_Core_Report_Excel::writeCSVFile(self::getExportFileName(), $header, $rows);
1054     CRM_Utils_System::civiExit();
1055   }
1056
1057   /**
1058    * @param \CRM_Export_BAO_ExportProcessor $processor
1059    * @param $sqlColumns
1060    * @param $field
1061    */
1062   public static function sqlColumnDefn($processor, &$sqlColumns, $field) {
1063     if (substr($field, -4) == '_a_b' || substr($field, -4) == '_b_a') {
1064       return;
1065     }
1066     $queryFields = $processor->getQueryFields();
1067
1068     $fieldName = CRM_Utils_String::munge(strtolower($field), '_', 64);
1069     if ($fieldName == 'id') {
1070       $fieldName = 'civicrm_primary_id';
1071     }
1072
1073     // early exit for master_id, CRM-12100
1074     // in the DB it is an ID, but in the export, we retrive the display_name of the master record
1075     // also for current_employer, CRM-16939
1076     if ($fieldName == 'master_id' || $fieldName == 'current_employer') {
1077       $sqlColumns[$fieldName] = "$fieldName varchar(128)";
1078       return;
1079     }
1080
1081     if (substr($fieldName, -11) == 'campaign_id') {
1082       // CRM-14398
1083       $sqlColumns[$fieldName] = "$fieldName varchar(128)";
1084       return;
1085     }
1086
1087     $lookUp = array('prefix_id', 'suffix_id');
1088     // set the sql columns
1089     if (isset($queryFields[$field]['type'])) {
1090       switch ($queryFields[$field]['type']) {
1091         case CRM_Utils_Type::T_INT:
1092         case CRM_Utils_Type::T_BOOLEAN:
1093           if (in_array($field, $lookUp)) {
1094             $sqlColumns[$fieldName] = "$fieldName varchar(255)";
1095           }
1096           else {
1097             $sqlColumns[$fieldName] = "$fieldName varchar(16)";
1098           }
1099           break;
1100
1101         case CRM_Utils_Type::T_STRING:
1102           if (isset($queryFields[$field]['maxlength'])) {
1103             $sqlColumns[$fieldName] = "$fieldName varchar({$queryFields[$field]['maxlength']})";
1104           }
1105           else {
1106             $sqlColumns[$fieldName] = "$fieldName varchar(255)";
1107           }
1108           break;
1109
1110         case CRM_Utils_Type::T_TEXT:
1111         case CRM_Utils_Type::T_LONGTEXT:
1112         case CRM_Utils_Type::T_BLOB:
1113         case CRM_Utils_Type::T_MEDIUMBLOB:
1114           $sqlColumns[$fieldName] = "$fieldName longtext";
1115           break;
1116
1117         case CRM_Utils_Type::T_FLOAT:
1118         case CRM_Utils_Type::T_ENUM:
1119         case CRM_Utils_Type::T_DATE:
1120         case CRM_Utils_Type::T_TIME:
1121         case CRM_Utils_Type::T_TIMESTAMP:
1122         case CRM_Utils_Type::T_MONEY:
1123         case CRM_Utils_Type::T_EMAIL:
1124         case CRM_Utils_Type::T_URL:
1125         case CRM_Utils_Type::T_CCNUM:
1126         default:
1127           $sqlColumns[$fieldName] = "$fieldName varchar(32)";
1128           break;
1129       }
1130     }
1131     else {
1132       if (substr($fieldName, -3, 3) == '_id') {
1133         $sqlColumns[$fieldName] = "$fieldName varchar(255)";
1134       }
1135       elseif (substr($fieldName, -5, 5) == '_note') {
1136         $sqlColumns[$fieldName] = "$fieldName text";
1137       }
1138       else {
1139         $changeFields = array(
1140           'groups',
1141           'tags',
1142           'notes',
1143         );
1144
1145         if (in_array($fieldName, $changeFields)) {
1146           $sqlColumns[$fieldName] = "$fieldName text";
1147         }
1148         else {
1149           // set the sql columns for custom data
1150           if (isset($queryFields[$field]['data_type'])) {
1151
1152             switch ($queryFields[$field]['data_type']) {
1153               case 'String':
1154                 // May be option labels, which could be up to 512 characters
1155                 $length = max(512, CRM_Utils_Array::value('text_length', $queryFields[$field]));
1156                 $sqlColumns[$fieldName] = "$fieldName varchar($length)";
1157                 break;
1158
1159               case 'Country':
1160               case 'StateProvince':
1161               case 'Link':
1162                 $sqlColumns[$fieldName] = "$fieldName varchar(255)";
1163                 break;
1164
1165               case 'Memo':
1166                 $sqlColumns[$fieldName] = "$fieldName text";
1167                 break;
1168
1169               default:
1170                 $sqlColumns[$fieldName] = "$fieldName varchar(255)";
1171                 break;
1172             }
1173           }
1174           else {
1175             $sqlColumns[$fieldName] = "$fieldName text";
1176           }
1177         }
1178       }
1179     }
1180   }
1181
1182   /**
1183    * @param string $tableName
1184    * @param $details
1185    * @param $sqlColumns
1186    */
1187   public static function writeDetailsToTable($tableName, &$details, &$sqlColumns) {
1188     if (empty($details)) {
1189       return;
1190     }
1191
1192     $sql = "
1193 SELECT max(id)
1194 FROM   $tableName
1195 ";
1196
1197     $id = CRM_Core_DAO::singleValueQuery($sql);
1198     if (!$id) {
1199       $id = 0;
1200     }
1201
1202     $sqlClause = array();
1203
1204     foreach ($details as $dontCare => $row) {
1205       $id++;
1206       $valueString = array($id);
1207       foreach ($row as $dontCare => $value) {
1208         if (empty($value)) {
1209           $valueString[] = "''";
1210         }
1211         else {
1212           $valueString[] = "'" . CRM_Core_DAO::escapeString($value) . "'";
1213         }
1214       }
1215       $sqlClause[] = '(' . implode(',', $valueString) . ')';
1216     }
1217
1218     $sqlColumnString = '(id, ' . implode(',', array_keys($sqlColumns)) . ')';
1219
1220     $sqlValueString = implode(",\n", $sqlClause);
1221
1222     $sql = "
1223 INSERT INTO $tableName $sqlColumnString
1224 VALUES $sqlValueString
1225 ";
1226     CRM_Core_DAO::executeQuery($sql);
1227   }
1228
1229   /**
1230    * @param $sqlColumns
1231    *
1232    * @return string
1233    */
1234   public static function createTempTable(&$sqlColumns) {
1235     //creating a temporary table for the search result that need be exported
1236     $exportTempTable = CRM_Utils_SQL_TempTable::build()->setDurable()->setCategory('export')->getName();
1237
1238     // also create the sql table
1239     $sql = "DROP TABLE IF EXISTS {$exportTempTable}";
1240     CRM_Core_DAO::executeQuery($sql);
1241
1242     $sql = "
1243 CREATE TABLE {$exportTempTable} (
1244      id int unsigned NOT NULL AUTO_INCREMENT,
1245 ";
1246     $sql .= implode(",\n", array_values($sqlColumns));
1247
1248     $sql .= ",
1249   PRIMARY KEY ( id )
1250 ";
1251     // add indexes for street_address and household_name if present
1252     $addIndices = array(
1253       'street_address',
1254       'household_name',
1255       'civicrm_primary_id',
1256     );
1257
1258     foreach ($addIndices as $index) {
1259       if (isset($sqlColumns[$index])) {
1260         $sql .= ",
1261   INDEX index_{$index}( $index )
1262 ";
1263       }
1264     }
1265
1266     $sql .= "
1267 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
1268 ";
1269
1270     CRM_Core_DAO::executeQuery($sql);
1271     return $exportTempTable;
1272   }
1273
1274   /**
1275    * @param string $tableName
1276    * @param $headerRows
1277    * @param $sqlColumns
1278    * @param array $exportParams
1279    */
1280   public static function mergeSameAddress($tableName, &$headerRows, &$sqlColumns, $exportParams) {
1281     // check if any records are present based on if they have used shared address feature,
1282     // and not based on if city / state .. matches.
1283     $sql = "
1284 SELECT    r1.id                 as copy_id,
1285           r1.civicrm_primary_id as copy_contact_id,
1286           r1.addressee          as copy_addressee,
1287           r1.addressee_id       as copy_addressee_id,
1288           r1.postal_greeting    as copy_postal_greeting,
1289           r1.postal_greeting_id as copy_postal_greeting_id,
1290           r2.id                 as master_id,
1291           r2.civicrm_primary_id as master_contact_id,
1292           r2.postal_greeting    as master_postal_greeting,
1293           r2.postal_greeting_id as master_postal_greeting_id,
1294           r2.addressee          as master_addressee,
1295           r2.addressee_id       as master_addressee_id
1296 FROM      $tableName r1
1297 INNER JOIN civicrm_address adr ON r1.master_id   = adr.id
1298 INNER JOIN $tableName      r2  ON adr.contact_id = r2.civicrm_primary_id
1299 ORDER BY  r1.id";
1300     $linkedMerge = self::_buildMasterCopyArray($sql, $exportParams, TRUE);
1301
1302     // find all the records that have the same street address BUT not in a household
1303     // require match on city and state as well
1304     $sql = "
1305 SELECT    r1.id                 as master_id,
1306           r1.civicrm_primary_id as master_contact_id,
1307           r1.postal_greeting    as master_postal_greeting,
1308           r1.postal_greeting_id as master_postal_greeting_id,
1309           r1.addressee          as master_addressee,
1310           r1.addressee_id       as master_addressee_id,
1311           r2.id                 as copy_id,
1312           r2.civicrm_primary_id as copy_contact_id,
1313           r2.postal_greeting    as copy_postal_greeting,
1314           r2.postal_greeting_id as copy_postal_greeting_id,
1315           r2.addressee          as copy_addressee,
1316           r2.addressee_id       as copy_addressee_id
1317 FROM      $tableName r1
1318 LEFT JOIN $tableName r2 ON ( r1.street_address = r2.street_address AND
1319                r1.city = r2.city AND
1320                r1.state_province_id = r2.state_province_id )
1321 WHERE     ( r1.household_name IS NULL OR r1.household_name = '' )
1322 AND       ( r2.household_name IS NULL OR r2.household_name = '' )
1323 AND       ( r1.street_address != '' )
1324 AND       r2.id > r1.id
1325 ORDER BY  r1.id
1326 ";
1327     $merge = self::_buildMasterCopyArray($sql, $exportParams);
1328
1329     // unset ids from $merge already present in $linkedMerge
1330     foreach ($linkedMerge as $masterID => $values) {
1331       $keys = array($masterID);
1332       $keys = array_merge($keys, array_keys($values['copy']));
1333       foreach ($merge as $mid => $vals) {
1334         if (in_array($mid, $keys)) {
1335           unset($merge[$mid]);
1336         }
1337         else {
1338           foreach ($values['copy'] as $copyId) {
1339             if (in_array($copyId, $keys)) {
1340               unset($merge[$mid]['copy'][$copyId]);
1341             }
1342           }
1343         }
1344       }
1345     }
1346     $merge = $merge + $linkedMerge;
1347
1348     foreach ($merge as $masterID => $values) {
1349       $sql = "
1350 UPDATE $tableName
1351 SET    addressee = %1, postal_greeting = %2, email_greeting = %3
1352 WHERE  id = %4
1353 ";
1354       $params = array(
1355         1 => array($values['addressee'], 'String'),
1356         2 => array($values['postalGreeting'], 'String'),
1357         3 => array($values['emailGreeting'], 'String'),
1358         4 => array($masterID, 'Integer'),
1359       );
1360       CRM_Core_DAO::executeQuery($sql, $params);
1361
1362       // delete all copies
1363       $deleteIDs = array_keys($values['copy']);
1364       $deleteIDString = implode(',', $deleteIDs);
1365       $sql = "
1366 DELETE FROM $tableName
1367 WHERE  id IN ( $deleteIDString )
1368 ";
1369       CRM_Core_DAO::executeQuery($sql);
1370     }
1371
1372     // unset temporary columns that were added for postal mailing format
1373     if (!empty($exportParams['merge_same_address']['temp_columns'])) {
1374       $unsetKeys = array_keys($sqlColumns);
1375       foreach ($unsetKeys as $headerKey => $sqlColKey) {
1376         if (array_key_exists($sqlColKey, $exportParams['merge_same_address']['temp_columns'])) {
1377           unset($sqlColumns[$sqlColKey], $headerRows[$headerKey]);
1378         }
1379       }
1380     }
1381   }
1382
1383   /**
1384    * @param int $contactId
1385    * @param array $exportParams
1386    *
1387    * @return array
1388    */
1389   public static function _replaceMergeTokens($contactId, $exportParams) {
1390     $greetings = array();
1391     $contact = NULL;
1392
1393     $greetingFields = array(
1394       'postal_greeting',
1395       'addressee',
1396     );
1397     foreach ($greetingFields as $greeting) {
1398       if (!empty($exportParams[$greeting])) {
1399         $greetingLabel = $exportParams[$greeting];
1400         if (empty($contact)) {
1401           $values = array(
1402             'id' => $contactId,
1403             'version' => 3,
1404           );
1405           $contact = civicrm_api('contact', 'get', $values);
1406
1407           if (!empty($contact['is_error'])) {
1408             return $greetings;
1409           }
1410           $contact = $contact['values'][$contact['id']];
1411         }
1412
1413         $tokens = array('contact' => $greetingLabel);
1414         $greetings[$greeting] = CRM_Utils_Token::replaceContactTokens($greetingLabel, $contact, NULL, $tokens);
1415       }
1416     }
1417     return $greetings;
1418   }
1419
1420   /**
1421    * The function unsets static part of the string, if token is the dynamic part.
1422    *
1423    * Example: 'Hello {contact.first_name}' => converted to => '{contact.first_name}'
1424    * i.e 'Hello Alan' => converted to => 'Alan'
1425    *
1426    * @param string $parsedString
1427    * @param string $defaultGreeting
1428    * @param bool $addressMergeGreetings
1429    * @param string $greetingType
1430    *
1431    * @return mixed
1432    */
1433   public static function _trimNonTokens(
1434     &$parsedString, $defaultGreeting,
1435     $addressMergeGreetings, $greetingType = 'postal_greeting'
1436   ) {
1437     if (!empty($addressMergeGreetings[$greetingType])) {
1438       $greetingLabel = $addressMergeGreetings[$greetingType];
1439     }
1440     $greetingLabel = empty($greetingLabel) ? $defaultGreeting : $greetingLabel;
1441
1442     $stringsToBeReplaced = preg_replace('/(\{[a-zA-Z._ ]+\})/', ';;', $greetingLabel);
1443     $stringsToBeReplaced = explode(';;', $stringsToBeReplaced);
1444     foreach ($stringsToBeReplaced as $key => $string) {
1445       // to keep one space
1446       $stringsToBeReplaced[$key] = ltrim($string);
1447     }
1448     $parsedString = str_replace($stringsToBeReplaced, "", $parsedString);
1449
1450     return $parsedString;
1451   }
1452
1453   /**
1454    * @param $sql
1455    * @param array $exportParams
1456    * @param bool $sharedAddress
1457    *
1458    * @return array
1459    */
1460   public static function _buildMasterCopyArray($sql, $exportParams, $sharedAddress = FALSE) {
1461     static $contactGreetingTokens = array();
1462
1463     $addresseeOptions = CRM_Core_OptionGroup::values('addressee');
1464     $postalOptions = CRM_Core_OptionGroup::values('postal_greeting');
1465
1466     $merge = $parents = array();
1467     $dao = CRM_Core_DAO::executeQuery($sql);
1468
1469     while ($dao->fetch()) {
1470       $masterID = $dao->master_id;
1471       $copyID = $dao->copy_id;
1472       $masterPostalGreeting = $dao->master_postal_greeting;
1473       $masterAddressee = $dao->master_addressee;
1474       $copyAddressee = $dao->copy_addressee;
1475
1476       if (!$sharedAddress) {
1477         if (!isset($contactGreetingTokens[$dao->master_contact_id])) {
1478           $contactGreetingTokens[$dao->master_contact_id] = self::_replaceMergeTokens($dao->master_contact_id, $exportParams);
1479         }
1480         $masterPostalGreeting = CRM_Utils_Array::value('postal_greeting',
1481           $contactGreetingTokens[$dao->master_contact_id], $dao->master_postal_greeting
1482         );
1483         $masterAddressee = CRM_Utils_Array::value('addressee',
1484           $contactGreetingTokens[$dao->master_contact_id], $dao->master_addressee
1485         );
1486
1487         if (!isset($contactGreetingTokens[$dao->copy_contact_id])) {
1488           $contactGreetingTokens[$dao->copy_contact_id] = self::_replaceMergeTokens($dao->copy_contact_id, $exportParams);
1489         }
1490         $copyPostalGreeting = CRM_Utils_Array::value('postal_greeting',
1491           $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_postal_greeting
1492         );
1493         $copyAddressee = CRM_Utils_Array::value('addressee',
1494           $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_addressee
1495         );
1496       }
1497
1498       if (!isset($merge[$masterID])) {
1499         // check if this is an intermediate child
1500         // this happens if there are 3 or more matches a,b, c
1501         // the above query will return a, b / a, c / b, c
1502         // we might be doing a bit more work, but for now its ok, unless someone
1503         // knows how to fix the query above
1504         if (isset($parents[$masterID])) {
1505           $masterID = $parents[$masterID];
1506         }
1507         else {
1508           $merge[$masterID] = array(
1509             'addressee' => $masterAddressee,
1510             'copy' => array(),
1511             'postalGreeting' => $masterPostalGreeting,
1512           );
1513           $merge[$masterID]['emailGreeting'] = &$merge[$masterID]['postalGreeting'];
1514         }
1515       }
1516       $parents[$copyID] = $masterID;
1517
1518       if (!$sharedAddress && !array_key_exists($copyID, $merge[$masterID]['copy'])) {
1519
1520         if (!empty($exportParams['postal_greeting_other']) &&
1521           count($merge[$masterID]['copy']) >= 1
1522         ) {
1523           // use static greetings specified if no of contacts > 2
1524           $merge[$masterID]['postalGreeting'] = $exportParams['postal_greeting_other'];
1525         }
1526         elseif ($copyPostalGreeting) {
1527           self::_trimNonTokens($copyPostalGreeting,
1528             $postalOptions[$dao->copy_postal_greeting_id],
1529             $exportParams
1530           );
1531           $merge[$masterID]['postalGreeting'] = "{$merge[$masterID]['postalGreeting']}, {$copyPostalGreeting}";
1532           // if there happens to be a duplicate, remove it
1533           $merge[$masterID]['postalGreeting'] = str_replace(" {$copyPostalGreeting},", "", $merge[$masterID]['postalGreeting']);
1534         }
1535
1536         if (!empty($exportParams['addressee_other']) &&
1537           count($merge[$masterID]['copy']) >= 1
1538         ) {
1539           // use static greetings specified if no of contacts > 2
1540           $merge[$masterID]['addressee'] = $exportParams['addressee_other'];
1541         }
1542         elseif ($copyAddressee) {
1543           self::_trimNonTokens($copyAddressee,
1544             $addresseeOptions[$dao->copy_addressee_id],
1545             $exportParams, 'addressee'
1546           );
1547           $merge[$masterID]['addressee'] = "{$merge[$masterID]['addressee']}, " . trim($copyAddressee);
1548         }
1549       }
1550       $merge[$masterID]['copy'][$copyID] = $copyAddressee;
1551     }
1552
1553     return $merge;
1554   }
1555
1556   /**
1557    * Merge household record into the individual record
1558    * if exists
1559    *
1560    * @param string $exportTempTable
1561    *   Temporary temp table that stores the records.
1562    * @param array $headerRows
1563    *   Array of headers for the export file.
1564    * @param array $sqlColumns
1565    *   Array of names of the table columns of the temp table.
1566    * @param string $prefix
1567    *   Name of the relationship type that is prefixed to the table columns.
1568    */
1569   public static function mergeSameHousehold($exportTempTable, &$headerRows, &$sqlColumns, $prefix) {
1570     $prefixColumn = $prefix . '_';
1571     $allKeys = array_keys($sqlColumns);
1572     $replaced = array();
1573     $headerRows = array_values($headerRows);
1574
1575     // name map of the non standard fields in header rows & sql columns
1576     $mappingFields = array(
1577       'civicrm_primary_id' => 'id',
1578       'contact_source' => 'source',
1579       'current_employer_id' => 'employer_id',
1580       'contact_is_deleted' => 'is_deleted',
1581       'name' => 'address_name',
1582       'provider_id' => 'im_service_provider',
1583       'phone_type_id' => 'phone_type',
1584     );
1585
1586     //figure out which columns are to be replaced by which ones
1587     foreach ($sqlColumns as $columnNames => $dontCare) {
1588       if ($rep = CRM_Utils_Array::value($columnNames, $mappingFields)) {
1589         $replaced[$columnNames] = CRM_Utils_String::munge($prefixColumn . $rep, '_', 64);
1590       }
1591       else {
1592         $householdColName = CRM_Utils_String::munge($prefixColumn . $columnNames, '_', 64);
1593
1594         if (!empty($sqlColumns[$householdColName])) {
1595           $replaced[$columnNames] = $householdColName;
1596         }
1597       }
1598     }
1599     $query = "UPDATE $exportTempTable SET ";
1600
1601     $clause = array();
1602     foreach ($replaced as $from => $to) {
1603       $clause[] = "$from = $to ";
1604       unset($sqlColumns[$to]);
1605       if ($key = CRM_Utils_Array::key($to, $allKeys)) {
1606         unset($headerRows[$key]);
1607       }
1608     }
1609     $query .= implode(",\n", $clause);
1610     $query .= " WHERE {$replaced['civicrm_primary_id']} != ''";
1611
1612     CRM_Core_DAO::executeQuery($query);
1613
1614     //drop the table columns that store redundant household info
1615     $dropQuery = "ALTER TABLE $exportTempTable ";
1616     foreach ($replaced as $householdColumns) {
1617       $dropClause[] = " DROP $householdColumns ";
1618     }
1619     $dropQuery .= implode(",\n", $dropClause);
1620
1621     CRM_Core_DAO::executeQuery($dropQuery);
1622
1623     // also drop the temp table if exists
1624     $sql = "DROP TABLE IF EXISTS {$exportTempTable}_temp";
1625     CRM_Core_DAO::executeQuery($sql);
1626
1627     // clean up duplicate records
1628     $query = "
1629 CREATE TABLE {$exportTempTable}_temp SELECT *
1630 FROM {$exportTempTable}
1631 GROUP BY civicrm_primary_id ";
1632
1633     CRM_Core_DAO::executeQuery($query);
1634
1635     $query = "DROP TABLE $exportTempTable";
1636     CRM_Core_DAO::executeQuery($query);
1637
1638     $query = "ALTER TABLE {$exportTempTable}_temp RENAME TO {$exportTempTable}";
1639     CRM_Core_DAO::executeQuery($query);
1640   }
1641
1642   /**
1643    * @param $exportTempTable
1644    * @param $headerRows
1645    * @param $sqlColumns
1646    * @param $exportMode
1647    * @param null $saveFile
1648    * @param string $batchItems
1649    */
1650   public static function writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $exportMode, $saveFile = NULL, $batchItems = '') {
1651     $writeHeader = TRUE;
1652     $offset = 0;
1653     $limit = self::EXPORT_ROW_COUNT;
1654
1655     $query = "SELECT * FROM $exportTempTable";
1656
1657     while (1) {
1658       $limitQuery = $query . "
1659 LIMIT $offset, $limit
1660 ";
1661       $dao = CRM_Core_DAO::executeQuery($limitQuery);
1662
1663       if ($dao->N <= 0) {
1664         break;
1665       }
1666
1667       $componentDetails = array();
1668       while ($dao->fetch()) {
1669         $row = array();
1670
1671         foreach ($sqlColumns as $column => $dontCare) {
1672           $row[$column] = $dao->$column;
1673         }
1674         $componentDetails[] = $row;
1675       }
1676       if ($exportMode == 'financial') {
1677         $getExportFileName = 'CiviCRM Contribution Search';
1678       }
1679       else {
1680         $getExportFileName = self::getExportFileName('csv', $exportMode);
1681       }
1682       $csvRows = CRM_Core_Report_Excel::writeCSVFile($getExportFileName,
1683         $headerRows,
1684         $componentDetails,
1685         NULL,
1686         $writeHeader,
1687         $saveFile);
1688
1689       if ($saveFile && !empty($csvRows)) {
1690         $batchItems .= $csvRows;
1691       }
1692
1693       $writeHeader = FALSE;
1694       $offset += $limit;
1695     }
1696   }
1697
1698   /**
1699    * Manipulate header rows for relationship fields.
1700    *
1701    * @param $headerRows
1702    */
1703   public static function manipulateHeaderRows(&$headerRows) {
1704     foreach ($headerRows as & $header) {
1705       $split = explode('-', $header);
1706       if ($relationTypeName = CRM_Utils_Array::value($split[0], self::$relationshipTypes)) {
1707         $split[0] = $relationTypeName;
1708         $header = implode('-', $split);
1709       }
1710     }
1711   }
1712
1713   /**
1714    * Exclude contacts who are deceased, have "Do not mail" privacy setting,
1715    * or have no street address
1716    * @param $exportTempTable
1717    * @param $headerRows
1718    * @param $sqlColumns
1719    * @param $exportParams
1720    */
1721   public static function postalMailingFormat($exportTempTable, &$headerRows, &$sqlColumns, $exportParams) {
1722     $whereClause = array();
1723
1724     if (array_key_exists('is_deceased', $sqlColumns)) {
1725       $whereClause[] = 'is_deceased = 1';
1726     }
1727
1728     if (array_key_exists('do_not_mail', $sqlColumns)) {
1729       $whereClause[] = 'do_not_mail = 1';
1730     }
1731
1732     if (array_key_exists('street_address', $sqlColumns)) {
1733       $addressWhereClause = " ( (street_address IS NULL) OR (street_address = '') ) ";
1734
1735       // check for supplemental_address_1
1736       if (array_key_exists('supplemental_address_1', $sqlColumns)) {
1737         $addressOptions = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME,
1738           'address_options', TRUE, NULL, TRUE
1739         );
1740         if (!empty($addressOptions['supplemental_address_1'])) {
1741           $addressWhereClause .= " AND ( (supplemental_address_1 IS NULL) OR (supplemental_address_1 = '') ) ";
1742           // enclose it again, since we are doing an AND in between a set of ORs
1743           $addressWhereClause = "( $addressWhereClause )";
1744         }
1745       }
1746
1747       $whereClause[] = $addressWhereClause;
1748     }
1749
1750     if (!empty($whereClause)) {
1751       $whereClause = implode(' OR ', $whereClause);
1752       $query = "
1753 DELETE
1754 FROM   $exportTempTable
1755 WHERE  {$whereClause}";
1756       CRM_Core_DAO::singleValueQuery($query);
1757     }
1758
1759     // unset temporary columns that were added for postal mailing format
1760     if (!empty($exportParams['postal_mailing_export']['temp_columns'])) {
1761       $unsetKeys = array_keys($sqlColumns);
1762       foreach ($unsetKeys as $headerKey => $sqlColKey) {
1763         if (array_key_exists($sqlColKey, $exportParams['postal_mailing_export']['temp_columns'])) {
1764           unset($sqlColumns[$sqlColKey], $headerRows[$headerKey]);
1765         }
1766       }
1767     }
1768   }
1769
1770   /**
1771    * Build componentPayment fields.
1772    */
1773   public static function componentPaymentFields() {
1774     static $componentPaymentFields;
1775     if (!isset($componentPaymentFields)) {
1776       $componentPaymentFields = array(
1777         'componentPaymentField_total_amount' => ts('Total Amount'),
1778         'componentPaymentField_contribution_status' => ts('Contribution Status'),
1779         'componentPaymentField_received_date' => ts('Date Received'),
1780         'componentPaymentField_payment_instrument' => ts('Payment Method'),
1781         'componentPaymentField_transaction_id' => ts('Transaction ID'),
1782       );
1783     }
1784     return $componentPaymentFields;
1785   }
1786
1787   /**
1788    * Set the definition for the header rows and sql columns based on the field to output.
1789    *
1790    * @param string $field
1791    * @param array $headerRows
1792    * @param array $sqlColumns
1793    *   Columns to go in the temp table.
1794    * @param \CRM_Export_BAO_ExportProcessor $processor
1795    * @param array|string $value
1796    * @param array $phoneTypes
1797    * @param array $imProviders
1798    * @param string $relationQuery
1799    * @param array $selectedPaymentFields
1800    * @return array
1801    */
1802   public static function setHeaderRows($field, $headerRows, $sqlColumns, $processor, $value, $phoneTypes, $imProviders, $relationQuery, $selectedPaymentFields) {
1803
1804     $queryFields = $processor->getQueryFields();
1805     // Split campaign into 2 fields for id and title
1806     if (substr($field, -14) == 'campaign_title') {
1807       $headerRows[] = ts('Campaign Title');
1808     }
1809     elseif (substr($field, -11) == 'campaign_id') {
1810       $headerRows[] = ts('Campaign ID');
1811     }
1812     elseif (isset($queryFields[$field]['title'])) {
1813       $headerRows[] = $queryFields[$field]['title'];
1814     }
1815     elseif ($field == 'phone_type_id') {
1816       $headerRows[] = ts('Phone Type');
1817     }
1818     elseif ($field == 'provider_id') {
1819       $headerRows[] = ts('IM Service Provider');
1820     }
1821     elseif (substr($field, 0, 5) == 'case_' && $queryFields['case'][$field]['title']) {
1822       $headerRows[] = $queryFields['case'][$field]['title'];
1823     }
1824     elseif (array_key_exists($field, self::$relationshipTypes)) {
1825       foreach ($value as $relationField => $relationValue) {
1826         // below block is same as primary block (duplicate)
1827         if (isset($relationQuery[$field]->_fields[$relationField]['title'])) {
1828           if ($relationQuery[$field]->_fields[$relationField]['name'] == 'name') {
1829             $headerName = $field . '-' . $relationField;
1830           }
1831           else {
1832             if ($relationField == 'current_employer') {
1833               $headerName = $field . '-' . 'current_employer';
1834             }
1835             else {
1836               $headerName = $field . '-' . $relationQuery[$field]->_fields[$relationField]['name'];
1837             }
1838           }
1839
1840           $headerRows[] = $headerName;
1841
1842           self::sqlColumnDefn($processor, $sqlColumns, $headerName);
1843         }
1844         elseif ($relationField == 'phone_type_id') {
1845           $headerName = $field . '-' . 'Phone Type';
1846           $headerRows[] = $headerName;
1847           self::sqlColumnDefn($processor, $sqlColumns, $headerName);
1848         }
1849         elseif ($relationField == 'provider_id') {
1850           $headerName = $field . '-' . 'Im Service Provider';
1851           $headerRows[] = $headerName;
1852           self::sqlColumnDefn($processor, $sqlColumns, $headerName);
1853         }
1854         elseif ($relationField == 'state_province_id') {
1855           $headerName = $field . '-' . 'state_province_id';
1856           $headerRows[] = $headerName;
1857           self::sqlColumnDefn($processor, $sqlColumns, $headerName);
1858         }
1859         elseif (is_array($relationValue) && $relationField == 'location') {
1860           // fix header for location type case
1861           foreach ($relationValue as $ltype => $val) {
1862             foreach (array_keys($val) as $fld) {
1863               $type = explode('-', $fld);
1864
1865               $hdr = "{$ltype}-" . $relationQuery[$field]->_fields[$type[0]]['title'];
1866
1867               if (!empty($type[1])) {
1868                 if (CRM_Utils_Array::value(0, $type) == 'phone') {
1869                   $hdr .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes);
1870                 }
1871                 elseif (CRM_Utils_Array::value(0, $type) == 'im') {
1872                   $hdr .= "-" . CRM_Utils_Array::value($type[1], $imProviders);
1873                 }
1874               }
1875               $headerName = $field . '-' . $hdr;
1876               $headerRows[] = $headerName;
1877               self::sqlColumnDefn($processor, $sqlColumns, $headerName);
1878             }
1879           }
1880         }
1881       }
1882       self::manipulateHeaderRows($headerRows);
1883     }
1884     elseif ($selectedPaymentFields && array_key_exists($field, self::componentPaymentFields())) {
1885       $headerRows[] = CRM_Utils_Array::value($field, self::componentPaymentFields());
1886     }
1887     else {
1888       $headerRows[] = $field;
1889     }
1890
1891     self::sqlColumnDefn($processor, $sqlColumns, $field);
1892
1893     return array($headerRows, $sqlColumns);
1894   }
1895
1896   /**
1897    * Get the various arrays that we use to structure our output.
1898    *
1899    * The extraction of these has been moved to a separate function for clarity and so that
1900    * tests can be added - in particular on the $outputHeaders array.
1901    *
1902    * However it still feels a bit like something that I'm too polite to write down and this should be seen
1903    * as a step on the refactoring path rather than how it should be.
1904    *
1905    * @param array $returnProperties
1906    * @param \CRM_Export_BAO_ExportProcessor $processor
1907    * @param string $relationQuery
1908    * @param array $selectedPaymentFields
1909    * @return array
1910    *   - outputColumns Array of columns to be exported. The values don't matter but the key must match the
1911    *   alias for the field generated by BAO_Query object.
1912    *   - headerRows Array of the column header strings to put in the csv header - non-associative.
1913    *   - sqlColumns Array of column names for the temp table. Not too sure why outputColumns can't be used here.
1914    *   - metadata Array of fields with specific parameters to pass to the translate function or another hacky nasty solution
1915    *    I'm too embarassed to discuss here.
1916    *    The keys need
1917    *    - to match the outputColumns keys (yes, the fact we ignore the output columns values & then pass another array with values
1918    *    we could use does suggest further refactors. However, you future improver, do remember that every check you do
1919    *    in the main DAO loop is done once per row & that coule be 100,000 times.)
1920    *    Finally a pop quiz: We need the translate context because we use a function other than ts() - is this because
1921    *    - a) the function used is more efficient or
1922    *    - b) this code is old & outdated. Submit your answers to circular bin or better
1923    *       yet find a way to comment them for posterity.
1924    */
1925   public static function getExportStructureArrays($returnProperties, $processor, $relationQuery, $selectedPaymentFields) {
1926     $metadata = $headerRows = $outputColumns = $sqlColumns = array();
1927     $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id');
1928     $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id');
1929
1930     $queryFields = $processor->getQueryFields();
1931     foreach ($returnProperties as $key => $value) {
1932       if ($key != 'location' || !is_array($value)) {
1933         $outputColumns[$key] = $value;
1934         list($headerRows, $sqlColumns) = self::setHeaderRows($key, $headerRows, $sqlColumns, $processor, $value, $phoneTypes, $imProviders, $relationQuery, $selectedPaymentFields);
1935       }
1936       else {
1937         foreach ($value as $locationType => $locationFields) {
1938           foreach (array_keys($locationFields) as $locationFieldName) {
1939             $type = explode('-', $locationFieldName);
1940
1941             $actualDBFieldName = $type[0];
1942             $outputFieldName = $locationType . '-' . $queryFields[$actualDBFieldName]['title'];
1943             $daoFieldName = CRM_Utils_String::munge($locationType) . '-' . $actualDBFieldName;
1944
1945             if (!empty($type[1])) {
1946               $daoFieldName .= "-" . $type[1];
1947               if ($actualDBFieldName == 'phone') {
1948                 $outputFieldName .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes);
1949               }
1950               elseif ($actualDBFieldName == 'im') {
1951                 $outputFieldName .= "-" . CRM_Utils_Array::value($type[1], $imProviders);
1952               }
1953             }
1954             if ($type[0] == 'im_provider') {
1955               // Warning: shame inducing hack.
1956               $metadata[$daoFieldName]['pseudoconstant']['var'] = 'imProviders';
1957             }
1958             self::sqlColumnDefn($processor, $sqlColumns, $outputFieldName);
1959             list($headerRows, $sqlColumns) = self::setHeaderRows($outputFieldName, $headerRows, $sqlColumns, $processor, $value, $phoneTypes, $imProviders, $relationQuery, $selectedPaymentFields);
1960             if ($actualDBFieldName == 'country' || $actualDBFieldName == 'world_region') {
1961               $metadata[$daoFieldName] = array('context' => 'country');
1962             }
1963             if ($actualDBFieldName == 'state_province') {
1964               $metadata[$daoFieldName] = array('context' => 'province');
1965             }
1966             $outputColumns[$daoFieldName] = TRUE;
1967           }
1968         }
1969       }
1970     }
1971     return array($outputColumns, $headerRows, $sqlColumns, $metadata);
1972   }
1973
1974   /**
1975    * Get the values of linked household contact.
1976    *
1977    * @param CRM_Core_DAO $relDAO
1978    * @param array $value
1979    * @param string $field
1980    * @param array $row
1981    */
1982   private static function fetchRelationshipDetails($relDAO, $value, $field, &$row) {
1983     $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id');
1984     $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id');
1985     $i18n = CRM_Core_I18n::singleton();
1986     foreach ($value as $relationField => $relationValue) {
1987       if (is_object($relDAO) && property_exists($relDAO, $relationField)) {
1988         $fieldValue = $relDAO->$relationField;
1989         if ($relationField == 'phone_type_id') {
1990           $fieldValue = $phoneTypes[$relationValue];
1991         }
1992         elseif ($relationField == 'provider_id') {
1993           $fieldValue = CRM_Utils_Array::value($relationValue, $imProviders);
1994         }
1995         // CRM-13995
1996         elseif (is_object($relDAO) && in_array($relationField, array(
1997             'email_greeting',
1998             'postal_greeting',
1999             'addressee',
2000           ))
2001         ) {
2002           //special case for greeting replacement
2003           $fldValue = "{$relationField}_display";
2004           $fieldValue = $relDAO->$fldValue;
2005         }
2006       }
2007       elseif (is_object($relDAO) && $relationField == 'state_province') {
2008         $fieldValue = CRM_Core_PseudoConstant::stateProvince($relDAO->state_province_id);
2009       }
2010       elseif (is_object($relDAO) && $relationField == 'country') {
2011         $fieldValue = CRM_Core_PseudoConstant::country($relDAO->country_id);
2012       }
2013       else {
2014         $fieldValue = '';
2015       }
2016       $field = $field . '_';
2017       $relPrefix = $field . $relationField;
2018
2019       if (is_object($relDAO) && $relationField == 'id') {
2020         $row[$relPrefix] = $relDAO->contact_id;
2021       }
2022       elseif (is_array($relationValue) && $relationField == 'location') {
2023         foreach ($relationValue as $ltype => $val) {
2024           foreach (array_keys($val) as $fld) {
2025             $type = explode('-', $fld);
2026             $fldValue = "{$ltype}-" . $type[0];
2027             if (!empty($type[1])) {
2028               $fldValue .= "-" . $type[1];
2029             }
2030             // CRM-3157: localise country, region (both have ‘country’ context)
2031             // and state_province (‘province’ context)
2032             switch (TRUE) {
2033               case (!is_object($relDAO)):
2034                 $row[$field . '_' . $fldValue] = '';
2035                 break;
2036
2037               case in_array('country', $type):
2038               case in_array('world_region', $type):
2039                 $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue,
2040                   array('context' => 'country')
2041                 );
2042                 break;
2043
2044               case in_array('state_province', $type):
2045                 $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue,
2046                   array('context' => 'province')
2047                 );
2048                 break;
2049
2050               default:
2051                 $row[$field . '_' . $fldValue] = $relDAO->$fldValue;
2052                 break;
2053             }
2054           }
2055         }
2056       }
2057       elseif (isset($fieldValue) && $fieldValue != '') {
2058         //check for custom data
2059         if ($cfID = CRM_Core_BAO_CustomField::getKeyID($relationField)) {
2060           $row[$relPrefix] = CRM_Core_BAO_CustomField::displayValue($fieldValue, $cfID);
2061         }
2062         else {
2063           //normal relationship fields
2064           // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context)
2065           switch ($relationField) {
2066             case 'country':
2067             case 'world_region':
2068               $row[$relPrefix] = $i18n->crm_translate($fieldValue, array('context' => 'country'));
2069               break;
2070
2071             case 'state_province':
2072               $row[$relPrefix] = $i18n->crm_translate($fieldValue, array('context' => 'province'));
2073               break;
2074
2075             default:
2076               $row[$relPrefix] = $fieldValue;
2077               break;
2078           }
2079         }
2080       }
2081       else {
2082         // if relation field is empty or null
2083         $row[$relPrefix] = '';
2084       }
2085     }
2086   }
2087
2088   /**
2089    * Get the ids that we want to get related contact details for.
2090    *
2091    * @param array $ids
2092    * @param int $exportMode
2093    *
2094    * @return array
2095    */
2096   protected static function getIDsForRelatedContact($ids, $exportMode) {
2097     if ($exportMode == CRM_Export_Form_Select::CONTACT_EXPORT) {
2098       return $ids;
2099     }
2100     if ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) {
2101       $relIDs = [];
2102       $sourceID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_ActivityContact', 'record_type_id', 'Activity Source');
2103       $dao = CRM_Core_DAO::executeQuery("
2104             SELECT contact_id FROM civicrm_activity_contact
2105             WHERE activity_id IN ( " . implode(',', $ids) . ") AND
2106             record_type_id = {$sourceID}
2107           ");
2108
2109       while ($dao->fetch()) {
2110         $relIDs[] = $dao->contact_id;
2111       }
2112       return $relIDs;
2113     }
2114     $component = self::exportComponent($exportMode);
2115
2116     if ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) {
2117       return CRM_Case_BAO_Case::retrieveContactIdsByCaseId($ids);
2118     }
2119     else {
2120       return CRM_Core_DAO::getContactIDsFromComponent($ids, $component);
2121     }
2122   }
2123
2124   /**
2125    * @param $selectAll
2126    * @param $ids
2127    * @param $exportMode
2128    * @param $componentTable
2129    * @param $returnProperties
2130    * @param $queryMode
2131    * @return array
2132    */
2133   protected static function buildRelatedContactArray($selectAll, $ids, $exportMode, $componentTable, $returnProperties, $queryMode) {
2134     $allRelContactArray = $relationQuery = array();
2135
2136     foreach (self::$relationshipTypes as $rel => $dnt) {
2137       if ($relationReturnProperties = CRM_Utils_Array::value($rel, $returnProperties)) {
2138         $allRelContactArray[$rel] = array();
2139         // build Query for each relationship
2140         $relationQuery[$rel] = new CRM_Contact_BAO_Query(NULL, $relationReturnProperties,
2141           NULL, FALSE, FALSE, $queryMode
2142         );
2143         list($relationSelect, $relationFrom, $relationWhere, $relationHaving) = $relationQuery[$rel]->query();
2144
2145         list($id, $direction) = explode('_', $rel, 2);
2146         // identify the relationship direction
2147         $contactA = 'contact_id_a';
2148         $contactB = 'contact_id_b';
2149         if ($direction == 'b_a') {
2150           $contactA = 'contact_id_b';
2151           $contactB = 'contact_id_a';
2152         }
2153         $relIDs = self::getIDsForRelatedContact($ids, $exportMode);
2154
2155         $relationshipJoin = $relationshipClause = '';
2156         if (!$selectAll && $componentTable) {
2157           $relationshipJoin = " INNER JOIN {$componentTable} ctTable ON ctTable.contact_id = {$contactA}";
2158         }
2159         elseif (!empty($relIDs)) {
2160           $relID = implode(',', $relIDs);
2161           $relationshipClause = " AND crel.{$contactA} IN ( {$relID} )";
2162         }
2163
2164         $relationFrom = " {$relationFrom}
2165                 INNER JOIN civicrm_relationship crel ON crel.{$contactB} = contact_a.id AND crel.relationship_type_id = {$id}
2166                 {$relationshipJoin} ";
2167
2168         //check for active relationship status only
2169         $today = date('Ymd');
2170         $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )";
2171         $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause} {$relationActive}";
2172         $relationGroupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($relationQuery[$rel]->_select, "crel.{$contactA}");
2173         $relationSelect = "{$relationSelect}, {$contactA} as refContact ";
2174         $relationQueryString = "$relationSelect $relationFrom $relationWhere $relationHaving $relationGroupBy";
2175
2176         $allRelContactDAO = CRM_Core_DAO::executeQuery($relationQueryString);
2177         while ($allRelContactDAO->fetch()) {
2178           //FIX Me: Migrate this to table rather than array
2179           // build the array of all related contacts
2180           $allRelContactArray[$rel][$allRelContactDAO->refContact] = clone($allRelContactDAO);
2181         }
2182         $allRelContactDAO->free();
2183       }
2184     }
2185     return array($relationQuery, $allRelContactArray);
2186   }
2187
2188 }