CRM/Export/BAO/Export.php

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