CRM/Export/BAO/Export.php

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