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    * Key representing the head of household in the relationship array.
  46    *
  47    * e.g. ['8_b_a' => 'Household Member Is', '8_a_b = 'Household Member Of'.....]
  48    *
  49    * @var
  50    */
  51   protected static $relationshipTypes = [];
  52
  53   /**
  54    * Get default return property for export based on mode
  55    *
  56    * @param int $exportMode
  57    *   Export mode.
  58    *
  59    * @return string $property
  60    *   Default Return property
  61    */
  62   public static function defaultReturnProperty($exportMode) {
  63     // hack to add default return property based on export mode
  64     $property = NULL;
  65     if ($exportMode == CRM_Export_Form_Select::CONTRIBUTE_EXPORT) {
  66       $property = 'contribution_id';
  67     }
  68     elseif ($exportMode == CRM_Export_Form_Select::EVENT_EXPORT) {
  69       $property = 'participant_id';
  70     }
  71     elseif ($exportMode == CRM_Export_Form_Select::MEMBER_EXPORT) {
  72       $property = 'membership_id';
  73     }
  74     elseif ($exportMode == CRM_Export_Form_Select::PLEDGE_EXPORT) {
  75       $property = 'pledge_id';
  76     }
  77     elseif ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) {
  78       $property = 'case_id';
  79     }
  80     elseif ($exportMode == CRM_Export_Form_Select::GRANT_EXPORT) {
  81       $property = 'grant_id';
  82     }
  83     elseif ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) {
  84       $property = 'activity_id';
  85     }
  86     return $property;
  87   }
  88
  89   /**
  90    * Get Export component
  91    *
  92    * @param int $exportMode
  93    *   Export mode.
  94    *
  95    * @return string $component
  96    *   CiviCRM Export Component
  97    */
  98   public static function exportComponent($exportMode) {
  99     switch ($exportMode) {
 100       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
 101         $component = 'civicrm_contribution';
 102         break;
 103
 104       case CRM_Export_Form_Select::EVENT_EXPORT:
 105         $component = 'civicrm_participant';
 106         break;
 107
 108       case CRM_Export_Form_Select::MEMBER_EXPORT:
 109         $component = 'civicrm_membership';
 110         break;
 111
 112       case CRM_Export_Form_Select::PLEDGE_EXPORT:
 113         $component = 'civicrm_pledge';
 114         break;
 115
 116       case CRM_Export_Form_Select::GRANT_EXPORT:
 117         $component = 'civicrm_grant';
 118         break;
 119     }
 120     return $component;
 121   }
 122
 123   /**
 124    * Get Query Group By Clause
 125    * @param \CRM_Export_BAO_ExportProcessor $processor
 126    *   Export Mode
 127    * @param array $returnProperties
 128    *   Return Properties
 129    * @param object $query
 130    *   CRM_Contact_BAO_Query
 131    *
 132    * @return string $groupBy
 133    *   Group By Clause
 134    */
 135   public static function getGroupBy($processor, $returnProperties, $query) {
 136     $groupBy = '';
 137     $exportMode = $processor->getExportMode();
 138     $queryMode = $processor->getQueryMode();
 139     if (!empty($returnProperties['tags']) || !empty($returnProperties['groups']) ||
 140       CRM_Utils_Array::value('notes', $returnProperties) ||
 141       // CRM-9552
 142       ($queryMode & CRM_Contact_BAO_Query::MODE_CONTACTS && $query->_useGroupBy)
 143     ) {
 144       $groupBy = "contact_a.id";
 145     }
 146
 147     switch ($exportMode) {
 148       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
 149         $groupBy = 'civicrm_contribution.id';
 150         if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
 151           // especial group by  when soft credit columns are included
 152           $groupBy = array('contribution_search_scredit_combined.id', 'contribution_search_scredit_combined.scredit_id');
 153         }
 154         break;
 155
 156       case CRM_Export_Form_Select::EVENT_EXPORT:
 157         $groupBy = 'civicrm_participant.id';
 158         break;
 159
 160       case CRM_Export_Form_Select::MEMBER_EXPORT:
 161         $groupBy = "civicrm_membership.id";
 162         break;
 163     }
 164
 165     if ($queryMode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
 166       $groupBy = "civicrm_activity.id ";
 167     }
 168
 169     if (!empty($groupBy)) {
 170       if (!Civi::settings()->get('searchPrimaryDetailsOnly')) {
 171         CRM_Core_DAO::disableFullGroupByMode();
 172       }
 173       $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($query->_select, $groupBy);
 174     }
 175
 176     return $groupBy;
 177   }
 178
 179   /**
 180    * Get the list the export fields.
 181    *
 182    * @param int $selectAll
 183    *   User preference while export.
 184    * @param array $ids
 185    *   Contact ids.
 186    * @param array $params
 187    *   Associated array of fields.
 188    * @param string $order
 189    *   Order by clause.
 190    * @param array $fields
 191    *   Associated array of fields.
 192    * @param array $moreReturnProperties
 193    *   Additional return fields.
 194    * @param int $exportMode
 195    *   Export mode.
 196    * @param string $componentClause
 197    *   Component clause.
 198    * @param string $componentTable
 199    *   Component table.
 200    * @param bool $mergeSameAddress
 201    *   Merge records if they have same address.
 202    * @param bool $mergeSameHousehold
 203    *   Merge records if they belong to the same household.
 204    *
 205    * @param array $exportParams
 206    * @param string $queryOperator
 207    *
 208    * @return array|null
 209    *   An array can be requested from within a unit test.
 210    *
 211    * @throws \CRM_Core_Exception
 212    */
 213   public static function exportComponents(
 214     $selectAll,
 215     $ids,
 216     $params,
 217     $order = NULL,
 218     $fields = NULL,
 219     $moreReturnProperties = NULL,
 220     $exportMode = CRM_Export_Form_Select::CONTACT_EXPORT,
 221     $componentClause = NULL,
 222     $componentTable = NULL,
 223     $mergeSameAddress = FALSE,
 224     $mergeSameHousehold = FALSE,
 225     $exportParams = array(),
 226     $queryOperator = 'AND'
 227   ) {
 228
 229     $processor = new CRM_Export_BAO_ExportProcessor($exportMode, $fields, $queryOperator, $mergeSameHousehold);
 230     $returnProperties = array();
 231     self::$relationshipTypes = $processor->getRelationshipTypes();
 232
 233     if ($fields) {
 234       foreach ($fields as $key => $value) {
 235         $fieldName = CRM_Utils_Array::value(1, $value);
 236         if (!$fieldName) {
 237           continue;
 238         }
 239
 240         if ($processor->isRelationshipTypeKey($fieldName) && (!empty($value[2]) || !empty($value[4]))) {
 241           $returnProperties[$fieldName] = $processor->setRelationshipReturnProperties($value, $fieldName);
 242         }
 243         elseif (is_numeric(CRM_Utils_Array::value(2, $value))) {
 244           $locationName = CRM_Core_PseudoConstant::getName('CRM_Core_BAO_Address', 'location_type_id', $value[2]);
 245           if ($fieldName == 'phone') {
 246             $returnProperties['location'][$locationName]['phone-' . CRM_Utils_Array::value(3, $value)] = 1;
 247           }
 248           elseif ($fieldName == 'im') {
 249             $returnProperties['location'][$locationName]['im-' . CRM_Utils_Array::value(3, $value)] = 1;
 250           }
 251           else {
 252             $returnProperties['location'][$locationName][$fieldName] = 1;
 253           }
 254         }
 255         else {
 256           //hack to fix component fields
 257           //revert mix of event_id and title
 258           if ($fieldName == 'event_id') {
 259             $returnProperties['event_id'] = 1;
 260           }
 261           else {
 262             $returnProperties[$fieldName] = 1;
 263           }
 264         }
 265       }
 266       $defaultExportMode = self::defaultReturnProperty($exportMode);
 267       if ($defaultExportMode) {
 268         $returnProperties[$defaultExportMode] = 1;
 269       }
 270     }
 271     else {
 272       $returnProperties = $processor->getDefaultReturnProperties();
 273     }
 274     // @todo - we are working towards this being entirely a property of the processor
 275     $processor->setReturnProperties($returnProperties);
 276     $paymentTableId = $processor->getPaymentTableID();
 277
 278     if ($mergeSameAddress) {
 279       //make sure the addressee fields are selected
 280       //while using merge same address feature
 281       $returnProperties['addressee'] = 1;
 282       $returnProperties['postal_greeting'] = 1;
 283       $returnProperties['email_greeting'] = 1;
 284       $returnProperties['street_name'] = 1;
 285       $returnProperties['household_name'] = 1;
 286       $returnProperties['street_address'] = 1;
 287       $returnProperties['city'] = 1;
 288       $returnProperties['state_province'] = 1;
 289
 290       // some columns are required for assistance incase they are not already present
 291       $exportParams['merge_same_address']['temp_columns'] = array();
 292       $tempColumns = array('id', 'master_id', 'state_province_id', 'postal_greeting_id', 'addressee_id');
 293       foreach ($tempColumns as $column) {
 294         if (!array_key_exists($column, $returnProperties)) {
 295           $returnProperties[$column] = 1;
 296           $column = $column == 'id' ? 'civicrm_primary_id' : $column;
 297           $exportParams['merge_same_address']['temp_columns'][$column] = 1;
 298         }
 299       }
 300     }
 301
 302     if (!$selectAll && $componentTable && !empty($exportParams['additional_group'])) {
 303       // If an Additional Group is selected, then all contacts in that group are
 304       // added to the export set (filtering out duplicates).
 305       $query = "
 306 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";
 307       CRM_Core_DAO::executeQuery($query);
 308     }
 309
 310     if ($moreReturnProperties) {
 311       // fix for CRM-7066
 312       if (!empty($moreReturnProperties['group'])) {
 313         unset($moreReturnProperties['group']);
 314         $moreReturnProperties['groups'] = 1;
 315       }
 316       $returnProperties = array_merge($returnProperties, $moreReturnProperties);
 317     }
 318
 319     $exportParams['postal_mailing_export']['temp_columns'] = array();
 320     if ($exportParams['exportOption'] == 2 &&
 321       isset($exportParams['postal_mailing_export']) &&
 322       CRM_Utils_Array::value('postal_mailing_export', $exportParams['postal_mailing_export']) == 1
 323     ) {
 324       $postalColumns = array('is_deceased', 'do_not_mail', 'street_address', 'supplemental_address_1');
 325       foreach ($postalColumns as $column) {
 326         if (!array_key_exists($column, $returnProperties)) {
 327           $returnProperties[$column] = 1;
 328           $exportParams['postal_mailing_export']['temp_columns'][$column] = 1;
 329         }
 330       }
 331     }
 332
 333     // rectify params to what proximity search expects if there is a value for prox_distance
 334     // CRM-7021
 335     if (!empty($params)) {
 336       CRM_Contact_BAO_ProximityQuery::fixInputParams($params);
 337     }
 338
 339     list($query, $select, $from, $where, $having) = $processor->runQuery($params, $order, $returnProperties);
 340
 341     if ($mergeSameHousehold == 1) {
 342       if (empty($returnProperties['id'])) {
 343         $returnProperties['id'] = 1;
 344       }
 345
 346       foreach ($returnProperties as $key => $value) {
 347         if (!$processor->isRelationshipTypeKey($key)) {
 348           foreach ($processor->getHouseholdRelationshipTypes() as $householdRelationshipType) {
 349             if (!in_array($key, ['location_type', 'im_provider'])) {
 350               $returnProperties[$householdRelationshipType][$key] = $value;
 351             }
 352           }
 353           // @todo - don't use returnProperties above.
 354           $processor->setHouseholdMergeReturnProperties($returnProperties[$householdRelationshipType]);
 355         }
 356       }
 357     }
 358
 359     self::buildRelatedContactArray($selectAll, $ids, $processor, $componentTable);
 360
 361     // make sure the groups stuff is included only if specifically specified
 362     // by the fields param (CRM-1969), else we limit the contacts outputted to only
 363     // ones that are part of a group
 364     if (!empty($returnProperties['groups'])) {
 365       $oldClause = "( contact_a.id = civicrm_group_contact.contact_id )";
 366       $newClause = " ( $oldClause AND ( civicrm_group_contact.status = 'Added' OR civicrm_group_contact.status IS NULL ) )";
 367       // total hack for export, CRM-3618
 368       $from = str_replace($oldClause,
 369         $newClause,
 370         $from
 371       );
 372     }
 373
 374     if (!$selectAll && $componentTable) {
 375       $from .= " INNER JOIN $componentTable ctTable ON ctTable.contact_id = contact_a.id ";
 376     }
 377     elseif ($componentClause) {
 378       if (empty($where)) {
 379         $where = "WHERE $componentClause";
 380       }
 381       else {
 382         $where .= " AND $componentClause";
 383       }
 384     }
 385
 386     // CRM-13982 - check if is deleted
 387     $excludeTrashed = TRUE;
 388     foreach ($params as $value) {
 389       if ($value[0] == 'contact_is_deleted') {
 390         $excludeTrashed = FALSE;
 391       }
 392     }
 393     $trashClause = $excludeTrashed ? "contact_a.is_deleted != 1" : "( 1 )";
 394
 395     if (empty($where)) {
 396       $where = "WHERE $trashClause";
 397     }
 398     else {
 399       $where .= " AND $trashClause";
 400     }
 401
 402     $queryString = "$select $from $where $having";
 403
 404     $groupBy = self::getGroupBy($processor, $returnProperties, $query);
 405
 406     $queryString .= $groupBy;
 407
 408     if ($order) {
 409       // always add contact_a.id to the ORDER clause
 410       // so the order is deterministic
 411       //CRM-15301
 412       if (strpos('contact_a.id', $order) === FALSE) {
 413         $order .= ", contact_a.id";
 414       }
 415
 416       list($field, $dir) = explode(' ', $order, 2);
 417       $field = trim($field);
 418       if (!empty($returnProperties[$field])) {
 419         //CRM-15301
 420         $queryString .= " ORDER BY $order";
 421       }
 422     }
 423
 424     $addPaymentHeader = FALSE;
 425
 426     $paymentDetails = array();
 427     if ($processor->isExportPaymentFields()) {
 428       // get payment related in for event and members
 429       $paymentDetails = CRM_Contribute_BAO_Contribution::getContributionDetails($exportMode, $ids);
 430       //get all payment headers.
 431       // If we haven't selected specific payment fields, load in all the
 432       // payment headers.
 433       if (!$processor->isExportSpecifiedPaymentFields()) {
 434         if (!empty($paymentDetails)) {
 435           $addPaymentHeader = TRUE;
 436         }
 437       }
 438     }
 439
 440     $componentDetails = array();
 441
 442     $rowCount = self::EXPORT_ROW_COUNT;
 443     $offset = 0;
 444     // we write to temp table often to avoid using too much memory
 445     $tempRowCount = 100;
 446
 447     $count = -1;
 448
 449     list($outputColumns, $headerRows, $sqlColumns, $metadata) = self::getExportStructureArrays($returnProperties, $processor);
 450
 451     // add payment headers if required
 452     if ($addPaymentHeader && $processor->isExportPaymentFields()) {
 453       // @todo rather than do this for every single row do it before the loop starts.
 454       // where other header definitions take place.
 455       $headerRows = array_merge($headerRows, $processor->getPaymentHeaders());
 456       foreach (array_keys($processor->getPaymentHeaders()) as $paymentHdr) {
 457         self::sqlColumnDefn($processor, $sqlColumns, $paymentHdr);
 458       }
 459     }
 460
 461     $exportTempTable = self::createTempTable($sqlColumns);
 462     $limitReached = FALSE;
 463
 464     while (!$limitReached) {
 465       $limitQuery = "{$queryString} LIMIT {$offset}, {$rowCount}";
 466       $iterationDAO = CRM_Core_DAO::executeQuery($limitQuery);
 467       // If this is less than our limit by the end of the iteration we do not need to run the query again to
 468       // check if some remain.
 469       $rowsThisIteration = 0;
 470
 471       while ($iterationDAO->fetch()) {
 472         $count++;
 473         $rowsThisIteration++;
 474         $row = $processor->buildRow($query, $iterationDAO, $outputColumns, $metadata, $paymentDetails, $addPaymentHeader, $paymentTableId);
 475
 476         // add component info
 477         // write the row to a file
 478         $componentDetails[] = $row;
 479
 480         // output every $tempRowCount rows
 481         if ($count % $tempRowCount == 0) {
 482           self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns);
 483           $componentDetails = array();
 484         }
 485       }
 486       if ($rowsThisIteration < self::EXPORT_ROW_COUNT) {
 487         $limitReached = TRUE;
 488       }
 489       $offset += $rowCount;
 490     }
 491
 492     if ($exportTempTable) {
 493       self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns);
 494
 495       // if postalMailing option is checked, exclude contacts who are deceased, have
 496       // "Do not mail" privacy setting, or have no street address
 497       if (isset($exportParams['postal_mailing_export']['postal_mailing_export']) &&
 498         $exportParams['postal_mailing_export']['postal_mailing_export'] == 1
 499       ) {
 500         self::postalMailingFormat($exportTempTable, $headerRows, $sqlColumns, $exportMode);
 501       }
 502
 503       // do merge same address and merge same household processing
 504       if ($mergeSameAddress) {
 505         self::mergeSameAddress($exportTempTable, $headerRows, $sqlColumns, $exportParams);
 506       }
 507
 508       // merge the records if they have corresponding households
 509       if ($mergeSameHousehold) {
 510         foreach ($processor->getHouseholdRelationshipTypes() as $householdRelationshipType) {
 511           self::mergeSameHousehold($exportTempTable, $sqlColumns, $householdRelationshipType);
 512         }
 513       }
 514
 515       // call export hook
 516       CRM_Utils_Hook::export($exportTempTable, $headerRows, $sqlColumns, $exportMode, $componentTable, $ids);
 517
 518       // In order to be able to write a unit test against this function we need to suppress
 519       // the csv writing. In future hopefully the csv writing & the main processing will be in separate functions.
 520       if (empty($exportParams['suppress_csv_for_testing'])) {
 521         self::writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $processor);
 522       }
 523       else {
 524         // return tableName sqlColumns headerRows in test context
 525         return array($exportTempTable, $sqlColumns, $headerRows);
 526       }
 527
 528       // delete the export temp table and component table
 529       $sql = "DROP TABLE IF EXISTS {$exportTempTable}";
 530       CRM_Core_DAO::executeQuery($sql);
 531       CRM_Core_DAO::reenableFullGroupByMode();
 532       CRM_Utils_System::civiExit();
 533     }
 534     else {
 535       CRM_Core_DAO::reenableFullGroupByMode();
 536       throw new CRM_Core_Exception(ts('No records to export'));
 537     }
 538   }
 539
 540   /**
 541    * Handle import error file creation.
 542    */
 543   public static function invoke() {
 544     $type = CRM_Utils_Request::retrieve('type', 'Positive');
 545     $parserName = CRM_Utils_Request::retrieve('parser', 'String');
 546     if (empty($parserName) || empty($type)) {
 547       return;
 548     }
 549
 550     // clean and ensure parserName is a valid string
 551     $parserName = CRM_Utils_String::munge($parserName);
 552     $parserClass = explode('_', $parserName);
 553
 554     // make sure parserClass is in the CRM namespace and
 555     // at least 3 levels deep
 556     if ($parserClass[0] == 'CRM' &&
 557       count($parserClass) >= 3
 558     ) {
 559       require_once str_replace('_', DIRECTORY_SEPARATOR, $parserName) . ".php";
 560       // ensure the functions exists
 561       if (method_exists($parserName, 'errorFileName') &&
 562         method_exists($parserName, 'saveFileName')
 563       ) {
 564         $errorFileName = $parserName::errorFileName($type);
 565         $saveFileName = $parserName::saveFileName($type);
 566         if (!empty($errorFileName) && !empty($saveFileName)) {
 567           CRM_Utils_System::setHttpHeader('Cache-Control', 'must-revalidate, post-check=0, pre-check=0');
 568           CRM_Utils_System::setHttpHeader('Content-Description', 'File Transfer');
 569           CRM_Utils_System::setHttpHeader('Content-Type', 'text/csv');
 570           CRM_Utils_System::setHttpHeader('Content-Length', filesize($errorFileName));
 571           CRM_Utils_System::setHttpHeader('Content-Disposition', 'attachment; filename=' . $saveFileName);
 572
 573           readfile($errorFileName);
 574         }
 575       }
 576     }
 577     CRM_Utils_System::civiExit();
 578   }
 579
 580   /**
 581    * @param $customSearchClass
 582    * @param $formValues
 583    * @param $order
 584    */
 585   public static function exportCustom($customSearchClass, $formValues, $order) {
 586     $ext = CRM_Extension_System::singleton()->getMapper();
 587     if (!$ext->isExtensionClass($customSearchClass)) {
 588       require_once str_replace('_', DIRECTORY_SEPARATOR, $customSearchClass) . '.php';
 589     }
 590     else {
 591       require_once $ext->classToPath($customSearchClass);
 592     }
 593     $search = new $customSearchClass($formValues);
 594
 595     $includeContactIDs = FALSE;
 596     if ($formValues['radio_ts'] == 'ts_sel') {
 597       $includeContactIDs = TRUE;
 598     }
 599
 600     $sql = $search->all(0, 0, $order, $includeContactIDs);
 601
 602     $columns = $search->columns();
 603
 604     $header = array_keys($columns);
 605     $fields = array_values($columns);
 606
 607     $rows = array();
 608     $dao = CRM_Core_DAO::executeQuery($sql);
 609     $alterRow = FALSE;
 610     if (method_exists($search, 'alterRow')) {
 611       $alterRow = TRUE;
 612     }
 613     while ($dao->fetch()) {
 614       $row = array();
 615
 616       foreach ($fields as $field) {
 617         $unqualified_field = CRM_Utils_Array::First(array_slice(explode('.', $field), -1));
 618         $row[$field] = $dao->$unqualified_field;
 619       }
 620       if ($alterRow) {
 621         $search->alterRow($row);
 622       }
 623       $rows[] = $row;
 624     }
 625
 626     CRM_Core_Report_Excel::writeCSVFile(ts('CiviCRM Contact Search'), $header, $rows);
 627     CRM_Utils_System::civiExit();
 628   }
 629
 630   /**
 631    * @param \CRM_Export_BAO_ExportProcessor $processor
 632    * @param $sqlColumns
 633    * @param $field
 634    */
 635   public static function sqlColumnDefn($processor, &$sqlColumns, $field) {
 636     $sqlColumns[$processor->getMungedFieldName($field)] = $processor->getSqlColumnDefinition($field);
 637   }
 638
 639   /**
 640    * @param string $tableName
 641    * @param $details
 642    * @param $sqlColumns
 643    */
 644   public static function writeDetailsToTable($tableName, $details, $sqlColumns) {
 645     if (empty($details)) {
 646       return;
 647     }
 648
 649     $sql = "
 650 SELECT max(id)
 651 FROM   $tableName
 652 ";
 653
 654     $id = CRM_Core_DAO::singleValueQuery($sql);
 655     if (!$id) {
 656       $id = 0;
 657     }
 658
 659     $sqlClause = array();
 660
 661     foreach ($details as $row) {
 662       $id++;
 663       $valueString = array($id);
 664       foreach ($row as $value) {
 665         if (empty($value)) {
 666           $valueString[] = "''";
 667         }
 668         else {
 669           $valueString[] = "'" . CRM_Core_DAO::escapeString($value) . "'";
 670         }
 671       }
 672       $sqlClause[] = '(' . implode(',', $valueString) . ')';
 673     }
 674
 675     $sqlColumnString = '(id, ' . implode(',', array_keys($sqlColumns)) . ')';
 676
 677     $sqlValueString = implode(",\n", $sqlClause);
 678
 679     $sql = "
 680 INSERT INTO $tableName $sqlColumnString
 681 VALUES $sqlValueString
 682 ";
 683     CRM_Core_DAO::executeQuery($sql);
 684   }
 685
 686   /**
 687    * @param $sqlColumns
 688    *
 689    * @return string
 690    */
 691   public static function createTempTable($sqlColumns) {
 692     //creating a temporary table for the search result that need be exported
 693     $exportTempTable = CRM_Utils_SQL_TempTable::build()->setDurable()->setCategory('export')->getName();
 694
 695     // also create the sql table
 696     $sql = "DROP TABLE IF EXISTS {$exportTempTable}";
 697     CRM_Core_DAO::executeQuery($sql);
 698
 699     $sql = "
 700 CREATE TABLE {$exportTempTable} (
 701      id int unsigned NOT NULL AUTO_INCREMENT,
 702 ";
 703     $sql .= implode(",\n", array_values($sqlColumns));
 704
 705     $sql .= ",
 706   PRIMARY KEY ( id )
 707 ";
 708     // add indexes for street_address and household_name if present
 709     $addIndices = array(
 710       'street_address',
 711       'household_name',
 712       'civicrm_primary_id',
 713     );
 714
 715     foreach ($addIndices as $index) {
 716       if (isset($sqlColumns[$index])) {
 717         $sql .= ",
 718   INDEX index_{$index}( $index )
 719 ";
 720       }
 721     }
 722
 723     $sql .= "
 724 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
 725 ";
 726
 727     CRM_Core_DAO::executeQuery($sql);
 728     return $exportTempTable;
 729   }
 730
 731   /**
 732    * @param string $tableName
 733    * @param $headerRows
 734    * @param $sqlColumns
 735    * @param array $exportParams
 736    */
 737   public static function mergeSameAddress($tableName, &$headerRows, &$sqlColumns, $exportParams) {
 738     // check if any records are present based on if they have used shared address feature,
 739     // and not based on if city / state .. matches.
 740     $sql = "
 741 SELECT    r1.id                 as copy_id,
 742           r1.civicrm_primary_id as copy_contact_id,
 743           r1.addressee          as copy_addressee,
 744           r1.addressee_id       as copy_addressee_id,
 745           r1.postal_greeting    as copy_postal_greeting,
 746           r1.postal_greeting_id as copy_postal_greeting_id,
 747           r2.id                 as master_id,
 748           r2.civicrm_primary_id as master_contact_id,
 749           r2.postal_greeting    as master_postal_greeting,
 750           r2.postal_greeting_id as master_postal_greeting_id,
 751           r2.addressee          as master_addressee,
 752           r2.addressee_id       as master_addressee_id
 753 FROM      $tableName r1
 754 INNER JOIN civicrm_address adr ON r1.master_id   = adr.id
 755 INNER JOIN $tableName      r2  ON adr.contact_id = r2.civicrm_primary_id
 756 ORDER BY  r1.id";
 757     $linkedMerge = self::_buildMasterCopyArray($sql, $exportParams, TRUE);
 758
 759     // find all the records that have the same street address BUT not in a household
 760     // require match on city and state as well
 761     $sql = "
 762 SELECT    r1.id                 as master_id,
 763           r1.civicrm_primary_id as master_contact_id,
 764           r1.postal_greeting    as master_postal_greeting,
 765           r1.postal_greeting_id as master_postal_greeting_id,
 766           r1.addressee          as master_addressee,
 767           r1.addressee_id       as master_addressee_id,
 768           r2.id                 as copy_id,
 769           r2.civicrm_primary_id as copy_contact_id,
 770           r2.postal_greeting    as copy_postal_greeting,
 771           r2.postal_greeting_id as copy_postal_greeting_id,
 772           r2.addressee          as copy_addressee,
 773           r2.addressee_id       as copy_addressee_id
 774 FROM      $tableName r1
 775 LEFT JOIN $tableName r2 ON ( r1.street_address = r2.street_address AND
 776                r1.city = r2.city AND
 777                r1.state_province_id = r2.state_province_id )
 778 WHERE     ( r1.household_name IS NULL OR r1.household_name = '' )
 779 AND       ( r2.household_name IS NULL OR r2.household_name = '' )
 780 AND       ( r1.street_address != '' )
 781 AND       r2.id > r1.id
 782 ORDER BY  r1.id
 783 ";
 784     $merge = self::_buildMasterCopyArray($sql, $exportParams);
 785
 786     // unset ids from $merge already present in $linkedMerge
 787     foreach ($linkedMerge as $masterID => $values) {
 788       $keys = array($masterID);
 789       $keys = array_merge($keys, array_keys($values['copy']));
 790       foreach ($merge as $mid => $vals) {
 791         if (in_array($mid, $keys)) {
 792           unset($merge[$mid]);
 793         }
 794         else {
 795           foreach ($values['copy'] as $copyId) {
 796             if (in_array($copyId, $keys)) {
 797               unset($merge[$mid]['copy'][$copyId]);
 798             }
 799           }
 800         }
 801       }
 802     }
 803     $merge = $merge + $linkedMerge;
 804
 805     foreach ($merge as $masterID => $values) {
 806       $sql = "
 807 UPDATE $tableName
 808 SET    addressee = %1, postal_greeting = %2, email_greeting = %3
 809 WHERE  id = %4
 810 ";
 811       $params = array(
 812         1 => array($values['addressee'], 'String'),
 813         2 => array($values['postalGreeting'], 'String'),
 814         3 => array($values['emailGreeting'], 'String'),
 815         4 => array($masterID, 'Integer'),
 816       );
 817       CRM_Core_DAO::executeQuery($sql, $params);
 818
 819       // delete all copies
 820       $deleteIDs = array_keys($values['copy']);
 821       $deleteIDString = implode(',', $deleteIDs);
 822       $sql = "
 823 DELETE FROM $tableName
 824 WHERE  id IN ( $deleteIDString )
 825 ";
 826       CRM_Core_DAO::executeQuery($sql);
 827     }
 828
 829     // unset temporary columns that were added for postal mailing format
 830     if (!empty($exportParams['merge_same_address']['temp_columns'])) {
 831       $unsetKeys = array_keys($sqlColumns);
 832       foreach ($unsetKeys as $headerKey => $sqlColKey) {
 833         if (array_key_exists($sqlColKey, $exportParams['merge_same_address']['temp_columns'])) {
 834           unset($sqlColumns[$sqlColKey], $headerRows[$headerKey]);
 835         }
 836       }
 837     }
 838   }
 839
 840   /**
 841    * @param int $contactId
 842    * @param array $exportParams
 843    *
 844    * @return array
 845    */
 846   public static function _replaceMergeTokens($contactId, $exportParams) {
 847     $greetings = array();
 848     $contact = NULL;
 849
 850     $greetingFields = array(
 851       'postal_greeting',
 852       'addressee',
 853     );
 854     foreach ($greetingFields as $greeting) {
 855       if (!empty($exportParams[$greeting])) {
 856         $greetingLabel = $exportParams[$greeting];
 857         if (empty($contact)) {
 858           $values = array(
 859             'id' => $contactId,
 860             'version' => 3,
 861           );
 862           $contact = civicrm_api('contact', 'get', $values);
 863
 864           if (!empty($contact['is_error'])) {
 865             return $greetings;
 866           }
 867           $contact = $contact['values'][$contact['id']];
 868         }
 869
 870         $tokens = array('contact' => $greetingLabel);
 871         $greetings[$greeting] = CRM_Utils_Token::replaceContactTokens($greetingLabel, $contact, NULL, $tokens);
 872       }
 873     }
 874     return $greetings;
 875   }
 876
 877   /**
 878    * The function unsets static part of the string, if token is the dynamic part.
 879    *
 880    * Example: 'Hello {contact.first_name}' => converted to => '{contact.first_name}'
 881    * i.e 'Hello Alan' => converted to => 'Alan'
 882    *
 883    * @param string $parsedString
 884    * @param string $defaultGreeting
 885    * @param bool $addressMergeGreetings
 886    * @param string $greetingType
 887    *
 888    * @return mixed
 889    */
 890   public static function _trimNonTokens(
 891     &$parsedString, $defaultGreeting,
 892     $addressMergeGreetings, $greetingType = 'postal_greeting'
 893   ) {
 894     if (!empty($addressMergeGreetings[$greetingType])) {
 895       $greetingLabel = $addressMergeGreetings[$greetingType];
 896     }
 897     $greetingLabel = empty($greetingLabel) ? $defaultGreeting : $greetingLabel;
 898
 899     $stringsToBeReplaced = preg_replace('/(\{[a-zA-Z._ ]+\})/', ';;', $greetingLabel);
 900     $stringsToBeReplaced = explode(';;', $stringsToBeReplaced);
 901     foreach ($stringsToBeReplaced as $key => $string) {
 902       // to keep one space
 903       $stringsToBeReplaced[$key] = ltrim($string);
 904     }
 905     $parsedString = str_replace($stringsToBeReplaced, "", $parsedString);
 906
 907     return $parsedString;
 908   }
 909
 910   /**
 911    * @param $sql
 912    * @param array $exportParams
 913    * @param bool $sharedAddress
 914    *
 915    * @return array
 916    */
 917   public static function _buildMasterCopyArray($sql, $exportParams, $sharedAddress = FALSE) {
 918     static $contactGreetingTokens = array();
 919
 920     $addresseeOptions = CRM_Core_OptionGroup::values('addressee');
 921     $postalOptions = CRM_Core_OptionGroup::values('postal_greeting');
 922
 923     $merge = $parents = array();
 924     $dao = CRM_Core_DAO::executeQuery($sql);
 925
 926     while ($dao->fetch()) {
 927       $masterID = $dao->master_id;
 928       $copyID = $dao->copy_id;
 929       $masterPostalGreeting = $dao->master_postal_greeting;
 930       $masterAddressee = $dao->master_addressee;
 931       $copyAddressee = $dao->copy_addressee;
 932
 933       if (!$sharedAddress) {
 934         if (!isset($contactGreetingTokens[$dao->master_contact_id])) {
 935           $contactGreetingTokens[$dao->master_contact_id] = self::_replaceMergeTokens($dao->master_contact_id, $exportParams);
 936         }
 937         $masterPostalGreeting = CRM_Utils_Array::value('postal_greeting',
 938           $contactGreetingTokens[$dao->master_contact_id], $dao->master_postal_greeting
 939         );
 940         $masterAddressee = CRM_Utils_Array::value('addressee',
 941           $contactGreetingTokens[$dao->master_contact_id], $dao->master_addressee
 942         );
 943
 944         if (!isset($contactGreetingTokens[$dao->copy_contact_id])) {
 945           $contactGreetingTokens[$dao->copy_contact_id] = self::_replaceMergeTokens($dao->copy_contact_id, $exportParams);
 946         }
 947         $copyPostalGreeting = CRM_Utils_Array::value('postal_greeting',
 948           $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_postal_greeting
 949         );
 950         $copyAddressee = CRM_Utils_Array::value('addressee',
 951           $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_addressee
 952         );
 953       }
 954
 955       if (!isset($merge[$masterID])) {
 956         // check if this is an intermediate child
 957         // this happens if there are 3 or more matches a,b, c
 958         // the above query will return a, b / a, c / b, c
 959         // we might be doing a bit more work, but for now its ok, unless someone
 960         // knows how to fix the query above
 961         if (isset($parents[$masterID])) {
 962           $masterID = $parents[$masterID];
 963         }
 964         else {
 965           $merge[$masterID] = array(
 966             'addressee' => $masterAddressee,
 967             'copy' => array(),
 968             'postalGreeting' => $masterPostalGreeting,
 969           );
 970           $merge[$masterID]['emailGreeting'] = &$merge[$masterID]['postalGreeting'];
 971         }
 972       }
 973       $parents[$copyID] = $masterID;
 974
 975       if (!$sharedAddress && !array_key_exists($copyID, $merge[$masterID]['copy'])) {
 976
 977         if (!empty($exportParams['postal_greeting_other']) &&
 978           count($merge[$masterID]['copy']) >= 1
 979         ) {
 980           // use static greetings specified if no of contacts > 2
 981           $merge[$masterID]['postalGreeting'] = $exportParams['postal_greeting_other'];
 982         }
 983         elseif ($copyPostalGreeting) {
 984           self::_trimNonTokens($copyPostalGreeting,
 985             $postalOptions[$dao->copy_postal_greeting_id],
 986             $exportParams
 987           );
 988           $merge[$masterID]['postalGreeting'] = "{$merge[$masterID]['postalGreeting']}, {$copyPostalGreeting}";
 989           // if there happens to be a duplicate, remove it
 990           $merge[$masterID]['postalGreeting'] = str_replace(" {$copyPostalGreeting},", "", $merge[$masterID]['postalGreeting']);
 991         }
 992
 993         if (!empty($exportParams['addressee_other']) &&
 994           count($merge[$masterID]['copy']) >= 1
 995         ) {
 996           // use static greetings specified if no of contacts > 2
 997           $merge[$masterID]['addressee'] = $exportParams['addressee_other'];
 998         }
 999         elseif ($copyAddressee) {
1000           self::_trimNonTokens($copyAddressee,
1001             $addresseeOptions[$dao->copy_addressee_id],
1002             $exportParams, 'addressee'
1003           );
1004           $merge[$masterID]['addressee'] = "{$merge[$masterID]['addressee']}, " . trim($copyAddressee);
1005         }
1006       }
1007       $merge[$masterID]['copy'][$copyID] = $copyAddressee;
1008     }
1009
1010     return $merge;
1011   }
1012
1013   /**
1014    * Merge household record into the individual record
1015    * if exists
1016    *
1017    * @param string $exportTempTable
1018    *   Temporary temp table that stores the records.
1019    * @param array $sqlColumns
1020    *   Array of names of the table columns of the temp table.
1021    * @param string $prefix
1022    *   Name of the relationship type that is prefixed to the table columns.
1023    */
1024   public static function mergeSameHousehold($exportTempTable, &$sqlColumns, $prefix) {
1025     $prefixColumn = $prefix . '_';
1026     $replaced = array();
1027
1028     // name map of the non standard fields in header rows & sql columns
1029     $mappingFields = array(
1030       'civicrm_primary_id' => 'id',
1031       'provider_id' => 'im_service_provider',
1032       'phone_type_id' => 'phone_type',
1033     );
1034
1035     //figure out which columns are to be replaced by which ones
1036     foreach ($sqlColumns as $columnNames => $dontCare) {
1037       if ($rep = CRM_Utils_Array::value($columnNames, $mappingFields)) {
1038         $replaced[$columnNames] = CRM_Utils_String::munge($prefixColumn . $rep, '_', 64);
1039       }
1040       else {
1041         $householdColName = CRM_Utils_String::munge($prefixColumn . $columnNames, '_', 64);
1042
1043         if (!empty($sqlColumns[$householdColName])) {
1044           $replaced[$columnNames] = $householdColName;
1045         }
1046       }
1047     }
1048     $query = "UPDATE $exportTempTable SET ";
1049
1050     $clause = array();
1051     foreach ($replaced as $from => $to) {
1052       $clause[] = "$from = $to ";
1053       unset($sqlColumns[$to]);
1054     }
1055     $query .= implode(",\n", $clause);
1056     $query .= " WHERE {$replaced['civicrm_primary_id']} != ''";
1057
1058     CRM_Core_DAO::executeQuery($query);
1059
1060     //drop the table columns that store redundant household info
1061     $dropQuery = "ALTER TABLE $exportTempTable ";
1062     foreach ($replaced as $householdColumns) {
1063       $dropClause[] = " DROP $householdColumns ";
1064     }
1065     $dropQuery .= implode(",\n", $dropClause);
1066
1067     CRM_Core_DAO::executeQuery($dropQuery);
1068
1069     // also drop the temp table if exists
1070     $sql = "DROP TABLE IF EXISTS {$exportTempTable}_temp";
1071     CRM_Core_DAO::executeQuery($sql);
1072
1073     // clean up duplicate records
1074     $query = "
1075 CREATE TABLE {$exportTempTable}_temp SELECT *
1076 FROM {$exportTempTable}
1077 GROUP BY civicrm_primary_id ";
1078
1079     CRM_Core_DAO::executeQuery($query);
1080
1081     $query = "DROP TABLE $exportTempTable";
1082     CRM_Core_DAO::executeQuery($query);
1083
1084     $query = "ALTER TABLE {$exportTempTable}_temp RENAME TO {$exportTempTable}";
1085     CRM_Core_DAO::executeQuery($query);
1086   }
1087
1088   /**
1089    * @param $exportTempTable
1090    * @param $headerRows
1091    * @param $sqlColumns
1092    * @param \CRM_Export_BAO_ExportProcessor $processor
1093    */
1094   public static function writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $processor) {
1095     $exportMode = $processor->getExportMode();
1096     $writeHeader = TRUE;
1097     $offset = 0;
1098     $limit = self::EXPORT_ROW_COUNT;
1099
1100     $query = "SELECT * FROM $exportTempTable";
1101
1102     while (1) {
1103       $limitQuery = $query . "
1104 LIMIT $offset, $limit
1105 ";
1106       $dao = CRM_Core_DAO::executeQuery($limitQuery);
1107
1108       if ($dao->N <= 0) {
1109         break;
1110       }
1111
1112       $componentDetails = array();
1113       while ($dao->fetch()) {
1114         $row = array();
1115
1116         foreach ($sqlColumns as $column => $dontCare) {
1117           $row[$column] = $dao->$column;
1118         }
1119         $componentDetails[] = $row;
1120       }
1121       CRM_Core_Report_Excel::writeCSVFile($processor->getExportFileName(),
1122         $headerRows,
1123         $componentDetails,
1124         NULL,
1125         $writeHeader
1126       );
1127
1128       $writeHeader = FALSE;
1129       $offset += $limit;
1130     }
1131   }
1132
1133   /**
1134    * Manipulate header rows for relationship fields.
1135    *
1136    * @param $headerRows
1137    */
1138   public static function manipulateHeaderRows(&$headerRows) {
1139     foreach ($headerRows as & $header) {
1140       $split = explode('-', $header);
1141       if ($relationTypeName = CRM_Utils_Array::value($split[0], self::$relationshipTypes)) {
1142         $split[0] = $relationTypeName;
1143         $header = implode('-', $split);
1144       }
1145     }
1146   }
1147
1148   /**
1149    * Exclude contacts who are deceased, have "Do not mail" privacy setting,
1150    * or have no street address
1151    * @param $exportTempTable
1152    * @param $headerRows
1153    * @param $sqlColumns
1154    * @param $exportParams
1155    */
1156   public static function postalMailingFormat($exportTempTable, &$headerRows, &$sqlColumns, $exportParams) {
1157     $whereClause = array();
1158
1159     if (array_key_exists('is_deceased', $sqlColumns)) {
1160       $whereClause[] = 'is_deceased = 1';
1161     }
1162
1163     if (array_key_exists('do_not_mail', $sqlColumns)) {
1164       $whereClause[] = 'do_not_mail = 1';
1165     }
1166
1167     if (array_key_exists('street_address', $sqlColumns)) {
1168       $addressWhereClause = " ( (street_address IS NULL) OR (street_address = '') ) ";
1169
1170       // check for supplemental_address_1
1171       if (array_key_exists('supplemental_address_1', $sqlColumns)) {
1172         $addressOptions = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME,
1173           'address_options', TRUE, NULL, TRUE
1174         );
1175         if (!empty($addressOptions['supplemental_address_1'])) {
1176           $addressWhereClause .= " AND ( (supplemental_address_1 IS NULL) OR (supplemental_address_1 = '') ) ";
1177           // enclose it again, since we are doing an AND in between a set of ORs
1178           $addressWhereClause = "( $addressWhereClause )";
1179         }
1180       }
1181
1182       $whereClause[] = $addressWhereClause;
1183     }
1184
1185     if (!empty($whereClause)) {
1186       $whereClause = implode(' OR ', $whereClause);
1187       $query = "
1188 DELETE
1189 FROM   $exportTempTable
1190 WHERE  {$whereClause}";
1191       CRM_Core_DAO::singleValueQuery($query);
1192     }
1193
1194     // unset temporary columns that were added for postal mailing format
1195     if (!empty($exportParams['postal_mailing_export']['temp_columns'])) {
1196       $unsetKeys = array_keys($sqlColumns);
1197       foreach ($unsetKeys as $headerKey => $sqlColKey) {
1198         if (array_key_exists($sqlColKey, $exportParams['postal_mailing_export']['temp_columns'])) {
1199           unset($sqlColumns[$sqlColKey], $headerRows[$headerKey]);
1200         }
1201       }
1202     }
1203   }
1204
1205   /**
1206    * Build componentPayment fields.
1207    *
1208    * This is no longer used by export but BAO_Mapping still calls it & we
1209    * should find a generic way to handle this or move this to that class.
1210    *
1211    * @deprecated
1212    */
1213   public static function componentPaymentFields() {
1214     static $componentPaymentFields;
1215     if (!isset($componentPaymentFields)) {
1216       $componentPaymentFields = array(
1217         'componentPaymentField_total_amount' => ts('Total Amount'),
1218         'componentPaymentField_contribution_status' => ts('Contribution Status'),
1219         'componentPaymentField_received_date' => ts('Date Received'),
1220         'componentPaymentField_payment_instrument' => ts('Payment Method'),
1221         'componentPaymentField_transaction_id' => ts('Transaction ID'),
1222       );
1223     }
1224     return $componentPaymentFields;
1225   }
1226
1227   /**
1228    * Set the definition for the header rows and sql columns based on the field to output.
1229    *
1230    * @param string $field
1231    * @param array $headerRows
1232    * @param \CRM_Export_BAO_ExportProcessor $processor
1233    *
1234    * @return array
1235    */
1236   public static function setHeaderRows($field, $headerRows, $processor) {
1237
1238     $queryFields = $processor->getQueryFields();
1239     if (substr($field, -11) == 'campaign_id') {
1240       // @todo - set this correctly in the xml rather than here.
1241       $headerRows[] = ts('Campaign ID');
1242     }
1243     elseif ($processor->isMergeSameHousehold() && $field === 'id') {
1244       $headerRows[] = ts('Household ID');
1245     }
1246     elseif (isset($queryFields[$field]['title'])) {
1247       $headerRows[] = $queryFields[$field]['title'];
1248     }
1249     elseif ($field == 'provider_id') {
1250       // @todo - set this correctly in the xml rather than here.
1251       $headerRows[] = ts('IM Service Provider');
1252     }
1253     elseif ($processor->isExportPaymentFields() && array_key_exists($field, $processor->getcomponentPaymentFields())) {
1254       $headerRows[] = CRM_Utils_Array::value($field, $processor->getcomponentPaymentFields());
1255     }
1256     else {
1257       $headerRows[] = $field;
1258     }
1259
1260     return $headerRows;
1261   }
1262
1263   /**
1264    * Get the various arrays that we use to structure our output.
1265    *
1266    * The extraction of these has been moved to a separate function for clarity and so that
1267    * tests can be added - in particular on the $outputHeaders array.
1268    *
1269    * However it still feels a bit like something that I'm too polite to write down and this should be seen
1270    * as a step on the refactoring path rather than how it should be.
1271    *
1272    * @param array $returnProperties
1273    * @param \CRM_Export_BAO_ExportProcessor $processor
1274    *
1275    * @return array
1276    *   - outputColumns Array of columns to be exported. The values don't matter but the key must match the
1277    *   alias for the field generated by BAO_Query object.
1278    *   - headerRows Array of the column header strings to put in the csv header - non-associative.
1279    *   - sqlColumns Array of column names for the temp table. Not too sure why outputColumns can't be used here.
1280    *   - metadata Array of fields with specific parameters to pass to the translate function or another hacky nasty solution
1281    *    I'm too embarassed to discuss here.
1282    *    The keys need
1283    *    - to match the outputColumns keys (yes, the fact we ignore the output columns values & then pass another array with values
1284    *    we could use does suggest further refactors. However, you future improver, do remember that every check you do
1285    *    in the main DAO loop is done once per row & that coule be 100,000 times.)
1286    *    Finally a pop quiz: We need the translate context because we use a function other than ts() - is this because
1287    *    - a) the function used is more efficient or
1288    *    - b) this code is old & outdated. Submit your answers to circular bin or better
1289    *       yet find a way to comment them for posterity.
1290    */
1291   public static function getExportStructureArrays($returnProperties, $processor) {
1292     $metadata = $headerRows = $outputColumns = $sqlColumns = array();
1293     $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id');
1294     $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id');
1295     $queryFields = $processor->getQueryFields();
1296     foreach ($returnProperties as $key => $value) {
1297       if (($key != 'location' || !is_array($value)) && !$processor->isRelationshipTypeKey($key)) {
1298         $outputColumns[$key] = $value;
1299         $headerRows = self::setHeaderRows($key, $headerRows, $processor);
1300         self::sqlColumnDefn($processor, $sqlColumns, $key);
1301       }
1302       elseif ($processor->isRelationshipTypeKey($key)) {
1303         $outputColumns[$key] = $value;
1304         $field = $key;
1305         foreach ($value as $relationField => $relationValue) {
1306           // below block is same as primary block (duplicate)
1307           if (isset($queryFields[$relationField]['title'])) {
1308             $headerName = $field . '-' . $relationField;
1309
1310             if (!$processor->isHouseholdMergeRelationshipTypeKey($field)) {
1311               // Do not add to header row if we are only generating for merge reasons.
1312               $headerRows[] = $headerName;
1313             }
1314
1315             self::sqlColumnDefn($processor, $sqlColumns, $headerName);
1316           }
1317           elseif ($relationField == 'phone_type_id') {
1318             $headerName = $field . '-' . 'Phone Type';
1319             $headerRows[] = $headerName;
1320             self::sqlColumnDefn($processor, $sqlColumns, $headerName);
1321           }
1322           elseif ($relationField == 'provider_id') {
1323             $headerName = $field . '-' . 'Im Service Provider';
1324             $headerRows[] = $headerName;
1325             self::sqlColumnDefn($processor, $sqlColumns, $headerName);
1326           }
1327           elseif ($relationField == 'state_province_id') {
1328             $headerName = $field . '-' . 'state_province_id';
1329             $headerRows[] = $headerName;
1330             self::sqlColumnDefn($processor, $sqlColumns, $headerName);
1331           }
1332           elseif (is_array($relationValue) && $relationField == 'location') {
1333             // fix header for location type case
1334             foreach ($relationValue as $ltype => $val) {
1335               foreach (array_keys($val) as $fld) {
1336                 $type = explode('-', $fld);
1337
1338                 $hdr = "{$ltype}-" . $queryFields[$type[0]]['title'];
1339
1340                 if (!empty($type[1])) {
1341                   if (CRM_Utils_Array::value(0, $type) == 'phone') {
1342                     $hdr .= "-" . CRM_Core_PseudoConstant::getLabel('CRM_Core_BAO_Phone', 'phone_type_id', $type[1]);
1343                   }
1344                   elseif (CRM_Utils_Array::value(0, $type) == 'im') {
1345                     $hdr .= "-" . CRM_Core_PseudoConstant::getLabel('CRM_Core_BAO_IM', 'provider_id', $type[1]);
1346                   }
1347                 }
1348                 $headerName = $field . '-' . $hdr;
1349                 $headerRows[] = $headerName;
1350                 self::sqlColumnDefn($processor, $sqlColumns, $headerName);
1351               }
1352             }
1353           }
1354         }
1355         self::manipulateHeaderRows($headerRows);
1356       }
1357       else {
1358         foreach ($value as $locationType => $locationFields) {
1359           foreach (array_keys($locationFields) as $locationFieldName) {
1360             $type = explode('-', $locationFieldName);
1361
1362             $actualDBFieldName = $type[0];
1363             $outputFieldName = $locationType . '-' . $queryFields[$actualDBFieldName]['title'];
1364             $daoFieldName = CRM_Utils_String::munge($locationType) . '-' . $actualDBFieldName;
1365
1366             if (!empty($type[1])) {
1367               $daoFieldName .= "-" . $type[1];
1368               if ($actualDBFieldName == 'phone') {
1369                 $outputFieldName .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes);
1370               }
1371               elseif ($actualDBFieldName == 'im') {
1372                 $outputFieldName .= "-" . CRM_Utils_Array::value($type[1], $imProviders);
1373               }
1374             }
1375             if ($type[0] == 'im_provider') {
1376               // Warning: shame inducing hack.
1377               $metadata[$daoFieldName]['pseudoconstant']['var'] = 'imProviders';
1378             }
1379             self::sqlColumnDefn($processor, $sqlColumns, $outputFieldName);
1380             $headerRows = self::setHeaderRows($outputFieldName, $headerRows, $processor);
1381             self::sqlColumnDefn($processor, $sqlColumns, $outputFieldName);
1382             if ($actualDBFieldName == 'country' || $actualDBFieldName == 'world_region') {
1383               $metadata[$daoFieldName] = array('context' => 'country');
1384             }
1385             if ($actualDBFieldName == 'state_province') {
1386               $metadata[$daoFieldName] = array('context' => 'province');
1387             }
1388             $outputColumns[$daoFieldName] = TRUE;
1389           }
1390         }
1391       }
1392     }
1393     return array($outputColumns, $headerRows, $sqlColumns, $metadata);
1394   }
1395
1396   /**
1397    * Get the values of linked household contact.
1398    *
1399    * @param CRM_Core_DAO $relDAO
1400    * @param array $value
1401    * @param string $field
1402    * @param array $row
1403    */
1404   private static function fetchRelationshipDetails($relDAO, $value, $field, &$row) {
1405     $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id');
1406     $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id');
1407     $i18n = CRM_Core_I18n::singleton();
1408     $field = $field . '_';
1409
1410     foreach ($value as $relationField => $relationValue) {
1411       if (is_object($relDAO) && property_exists($relDAO, $relationField)) {
1412         $fieldValue = $relDAO->$relationField;
1413         if ($relationField == 'phone_type_id') {
1414           $fieldValue = $phoneTypes[$relationValue];
1415         }
1416         elseif ($relationField == 'provider_id') {
1417           $fieldValue = CRM_Utils_Array::value($relationValue, $imProviders);
1418         }
1419         // CRM-13995
1420         elseif (is_object($relDAO) && in_array($relationField, array(
1421             'email_greeting',
1422             'postal_greeting',
1423             'addressee',
1424           ))
1425         ) {
1426           //special case for greeting replacement
1427           $fldValue = "{$relationField}_display";
1428           $fieldValue = $relDAO->$fldValue;
1429         }
1430       }
1431       elseif (is_object($relDAO) && $relationField == 'state_province') {
1432         $fieldValue = CRM_Core_PseudoConstant::stateProvince($relDAO->state_province_id);
1433       }
1434       elseif (is_object($relDAO) && $relationField == 'country') {
1435         $fieldValue = CRM_Core_PseudoConstant::country($relDAO->country_id);
1436       }
1437       else {
1438         $fieldValue = '';
1439       }
1440       $relPrefix = $field . $relationField;
1441
1442       if (is_object($relDAO) && $relationField == 'id') {
1443         $row[$relPrefix] = $relDAO->contact_id;
1444       }
1445       elseif (is_array($relationValue) && $relationField == 'location') {
1446         foreach ($relationValue as $ltype => $val) {
1447           // If the location name has a space in it the we need to handle that. This
1448           // is kinda hacky but specifically covered in the ExportTest so later efforts to
1449           // improve it should be secure in the knowled it will be caught.
1450           $ltype = str_replace(' ', '_', $ltype);
1451           foreach (array_keys($val) as $fld) {
1452             $type = explode('-', $fld);
1453             $fldValue = "{$ltype}-" . $type[0];
1454             if (!empty($type[1])) {
1455               $fldValue .= "-" . $type[1];
1456             }
1457             // CRM-3157: localise country, region (both have ‘country’ context)
1458             // and state_province (‘province’ context)
1459             switch (TRUE) {
1460               case (!is_object($relDAO)):
1461                 $row[$field . '_' . $fldValue] = '';
1462                 break;
1463
1464               case in_array('country', $type):
1465               case in_array('world_region', $type):
1466                 $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue,
1467                   array('context' => 'country')
1468                 );
1469                 break;
1470
1471               case in_array('state_province', $type):
1472                 $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue,
1473                   array('context' => 'province')
1474                 );
1475                 break;
1476
1477               default:
1478                 $row[$field . '_' . $fldValue] = $relDAO->$fldValue;
1479                 break;
1480             }
1481           }
1482         }
1483       }
1484       elseif (isset($fieldValue) && $fieldValue != '') {
1485         //check for custom data
1486         if ($cfID = CRM_Core_BAO_CustomField::getKeyID($relationField)) {
1487           $row[$relPrefix] = CRM_Core_BAO_CustomField::displayValue($fieldValue, $cfID);
1488         }
1489         else {
1490           //normal relationship fields
1491           // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context)
1492           switch ($relationField) {
1493             case 'country':
1494             case 'world_region':
1495               $row[$relPrefix] = $i18n->crm_translate($fieldValue, array('context' => 'country'));
1496               break;
1497
1498             case 'state_province':
1499               $row[$relPrefix] = $i18n->crm_translate($fieldValue, array('context' => 'province'));
1500               break;
1501
1502             default:
1503               $row[$relPrefix] = $fieldValue;
1504               break;
1505           }
1506         }
1507       }
1508       else {
1509         // if relation field is empty or null
1510         $row[$relPrefix] = '';
1511       }
1512     }
1513   }
1514
1515   /**
1516    * Get the ids that we want to get related contact details for.
1517    *
1518    * @param array $ids
1519    * @param int $exportMode
1520    *
1521    * @return array
1522    */
1523   protected static function getIDsForRelatedContact($ids, $exportMode) {
1524     if ($exportMode == CRM_Export_Form_Select::CONTACT_EXPORT) {
1525       return $ids;
1526     }
1527     if ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) {
1528       $relIDs = [];
1529       $sourceID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_ActivityContact', 'record_type_id', 'Activity Source');
1530       $dao = CRM_Core_DAO::executeQuery("
1531             SELECT contact_id FROM civicrm_activity_contact
1532             WHERE activity_id IN ( " . implode(',', $ids) . ") AND
1533             record_type_id = {$sourceID}
1534           ");
1535
1536       while ($dao->fetch()) {
1537         $relIDs[] = $dao->contact_id;
1538       }
1539       return $relIDs;
1540     }
1541     $component = self::exportComponent($exportMode);
1542
1543     if ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) {
1544       return CRM_Case_BAO_Case::retrieveContactIdsByCaseId($ids);
1545     }
1546     else {
1547       return CRM_Core_DAO::getContactIDsFromComponent($ids, $component);
1548     }
1549   }
1550
1551   /**
1552    * @param $selectAll
1553    * @param $ids
1554    * @param \CRM_Export_BAO_ExportProcessor $processor
1555    * @param $componentTable
1556    */
1557   protected static function buildRelatedContactArray($selectAll, $ids, $processor, $componentTable) {
1558     $allRelContactArray = $relationQuery = array();
1559     $queryMode = $processor->getQueryMode();
1560     $exportMode = $processor->getExportMode();
1561
1562     foreach ($processor->getRelationshipReturnProperties() as $relationshipKey => $relationReturnProperties) {
1563       $allRelContactArray[$relationshipKey] = array();
1564       // build Query for each relationship
1565       $relationQuery = new CRM_Contact_BAO_Query(NULL, $relationReturnProperties,
1566         NULL, FALSE, FALSE, $queryMode
1567       );
1568       list($relationSelect, $relationFrom, $relationWhere, $relationHaving) = $relationQuery->query();
1569
1570       list($id, $direction) = explode('_', $relationshipKey, 2);
1571       // identify the relationship direction
1572       $contactA = 'contact_id_a';
1573       $contactB = 'contact_id_b';
1574       if ($direction == 'b_a') {
1575         $contactA = 'contact_id_b';
1576         $contactB = 'contact_id_a';
1577       }
1578       $relIDs = self::getIDsForRelatedContact($ids, $exportMode);
1579
1580       $relationshipJoin = $relationshipClause = '';
1581       if (!$selectAll && $componentTable) {
1582         $relationshipJoin = " INNER JOIN {$componentTable} ctTable ON ctTable.contact_id = {$contactA}";
1583       }
1584       elseif (!empty($relIDs)) {
1585         $relID = implode(',', $relIDs);
1586         $relationshipClause = " AND crel.{$contactA} IN ( {$relID} )";
1587       }
1588
1589       $relationFrom = " {$relationFrom}
1590               INNER JOIN civicrm_relationship crel ON crel.{$contactB} = contact_a.id AND crel.relationship_type_id = {$id}
1591               {$relationshipJoin} ";
1592
1593       //check for active relationship status only
1594       $today = date('Ymd');
1595       $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )";
1596       $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause} {$relationActive}";
1597       $relationGroupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($relationQuery->_select, "crel.{$contactA}");
1598       $relationSelect = "{$relationSelect}, {$contactA} as refContact ";
1599       $relationQueryString = "$relationSelect $relationFrom $relationWhere $relationHaving $relationGroupBy";
1600
1601       $allRelContactDAO = CRM_Core_DAO::executeQuery($relationQueryString);
1602       while ($allRelContactDAO->fetch()) {
1603         $relationQuery->convertToPseudoNames($allRelContactDAO);
1604         $row = [];
1605         // @todo pass processor to fetchRelationshipDetails and set fields directly within it.
1606         self::fetchRelationshipDetails($allRelContactDAO, $relationReturnProperties, $relationshipKey, $row);
1607         foreach (array_keys($relationReturnProperties) as $property) {
1608           if ($property === 'location') {
1609             // @todo - simplify location in self::fetchRelationshipDetails - remove handling here. Or just call
1610             // $processor->setRelationshipValue from fetchRelationshipDetails
1611             foreach ($relationReturnProperties['location'] as $locationName => $locationValues) {
1612               foreach (array_keys($locationValues) as $locationValue) {
1613                 $key = str_replace(' ', '_', $locationName) . '-' . $locationValue;
1614                 $processor->setRelationshipValue($relationshipKey, $allRelContactDAO->refContact, $key, $row[$relationshipKey . '__' . $key]);
1615               }
1616             }
1617           }
1618           else {
1619             $processor->setRelationshipValue($relationshipKey, $allRelContactDAO->refContact, $property, $row[$relationshipKey . '_' . $property]);
1620           }
1621         }
1622       }
1623     }
1624   }
1625
1626 }