CRM/Export/BAO/Export.php

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