CRM/Export/BAO/Export.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   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-2019
  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 the list the export fields.
  46    *
  47    * @param int $selectAll
  48    *   User preference while export.
  49    * @param array $ids
  50    *   Contact ids.
  51    * @param array $params
  52    *   Associated array of fields.
  53    * @param string $order
  54    *   Order by clause.
  55    * @param array $fields
  56    *   Associated array of fields.
  57    * @param array $moreReturnProperties
  58    *   Additional return fields.
  59    * @param int $exportMode
  60    *   Export mode.
  61    * @param string $componentClause
  62    *   Component clause.
  63    * @param string $componentTable
  64    *   Component table.
  65    * @param bool $mergeSameAddress
  66    *   Merge records if they have same address.
  67    * @param bool $mergeSameHousehold
  68    *   Merge records if they belong to the same household.
  69    *
  70    * @param array $exportParams
  71    * @param string $queryOperator
  72    *
  73    * @return array|null
  74    *   An array can be requested from within a unit test.
  75    *
  76    * @throws \CRM_Core_Exception
  77    */
  78   public static function exportComponents(
  79     $selectAll,
  80     $ids,
  81     $params,
  82     $order = NULL,
  83     $fields = NULL,
  84     $moreReturnProperties = NULL,
  85     $exportMode = CRM_Export_Form_Select::CONTACT_EXPORT,
  86     $componentClause = NULL,
  87     $componentTable = NULL,
  88     $mergeSameAddress = FALSE,
  89     $mergeSameHousehold = FALSE,
  90     $exportParams = [],
  91     $queryOperator = 'AND'
  92   ) {
  93
  94     $isPostalOnly = (
  95       isset($exportParams['postal_mailing_export']['postal_mailing_export']) &&
  96       $exportParams['postal_mailing_export']['postal_mailing_export'] == 1
  97     );
  98
  99     if (!$selectAll && $componentTable && !empty($exportParams['additional_group'])) {
 100       // If an Additional Group is selected, then all contacts in that group are
 101       // added to the export set (filtering out duplicates).
 102       // Really - the calling function could do this ... just saying
 103       // @todo take a whip to the calling function.
 104       CRM_Core_DAO::executeQuery("
 105 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"
 106       );
 107     }
 108     // rectify params to what proximity search expects if there is a value for prox_distance
 109     // CRM-7021
 110     // @todo - move this back to the calling functions
 111     if (!empty($params)) {
 112       CRM_Contact_BAO_ProximityQuery::fixInputParams($params);
 113     }
 114     // @todo everything from this line up should go back to the calling functions.
 115     $processor = new CRM_Export_BAO_ExportProcessor($exportMode, $fields, $queryOperator, $mergeSameHousehold, $isPostalOnly, $mergeSameAddress, $exportParams);
 116     if ($moreReturnProperties) {
 117       $processor->setAdditionalRequestedReturnProperties($moreReturnProperties);
 118     }
 119     $processor->setComponentTable($componentTable);
 120     $processor->setComponentClause($componentClause);
 121
 122     list($query, $queryString) = $processor->runQuery($params, $order);
 123
 124     // This perhaps only needs calling when $mergeSameHousehold == 1
 125     self::buildRelatedContactArray($selectAll, $ids, $processor, $componentTable);
 126
 127     $addPaymentHeader = FALSE;
 128
 129     list($outputColumns, $metadata) = $processor->getExportStructureArrays();
 130
 131     if ($processor->isMergeSameAddress()) {
 132       foreach (array_keys($processor->getAdditionalFieldsForSameAddressMerge()) as $field) {
 133         $processor->setColumnAsCalculationOnly($field);
 134       }
 135     }
 136
 137     $paymentDetails = [];
 138     if ($processor->isExportPaymentFields()) {
 139       // get payment related in for event and members
 140       $paymentDetails = CRM_Contribute_BAO_Contribution::getContributionDetails($exportMode, $ids);
 141       //get all payment headers.
 142       // If we haven't selected specific payment fields, load in all the
 143       // payment headers.
 144       if (!$processor->isExportSpecifiedPaymentFields()) {
 145         if (!empty($paymentDetails)) {
 146           $addPaymentHeader = TRUE;
 147           foreach (array_keys($processor->getPaymentHeaders()) as $paymentField) {
 148             $processor->addOutputSpecification($paymentField);
 149           }
 150         }
 151       }
 152     }
 153
 154     $componentDetails = [];
 155
 156     $rowCount = self::EXPORT_ROW_COUNT;
 157     $offset = 0;
 158     // we write to temp table often to avoid using too much memory
 159     $tempRowCount = 100;
 160
 161     $count = -1;
 162
 163     $sqlColumns = $processor->getSQLColumns();
 164     $processor->createTempTable();
 165     $limitReached = FALSE;
 166
 167     while (!$limitReached) {
 168       $limitQuery = "{$queryString} LIMIT {$offset}, {$rowCount}";
 169       CRM_Core_DAO::disableFullGroupByMode();
 170       $iterationDAO = CRM_Core_DAO::executeQuery($limitQuery);
 171       CRM_Core_DAO::reenableFullGroupByMode();
 172       // If this is less than our limit by the end of the iteration we do not need to run the query again to
 173       // check if some remain.
 174       $rowsThisIteration = 0;
 175
 176       while ($iterationDAO->fetch()) {
 177         $count++;
 178         $rowsThisIteration++;
 179         $row = $processor->buildRow($query, $iterationDAO, $outputColumns, $metadata, $paymentDetails, $addPaymentHeader);
 180         if ($row === FALSE) {
 181           continue;
 182         }
 183
 184         // add component info
 185         // write the row to a file
 186         $componentDetails[] = $row;
 187
 188         // output every $tempRowCount rows
 189         if ($count % $tempRowCount == 0) {
 190           self::writeDetailsToTable($processor, $componentDetails, $sqlColumns);
 191           $componentDetails = [];
 192         }
 193       }
 194       if ($rowsThisIteration < self::EXPORT_ROW_COUNT) {
 195         $limitReached = TRUE;
 196       }
 197       $offset += $rowCount;
 198     }
 199
 200     if ($processor->getTemporaryTable()) {
 201       self::writeDetailsToTable($processor, $componentDetails);
 202
 203       // do merge same address and merge same household processing
 204       if ($mergeSameAddress) {
 205         $processor->mergeSameAddress();
 206       }
 207
 208       // In order to be able to write a unit test against this function we need to suppress
 209       // the csv writing. In future hopefully the csv writing & the main processing will be in separate functions.
 210       if (empty($exportParams['suppress_csv_for_testing'])) {
 211         self::writeCSVFromTable($processor);
 212       }
 213       else {
 214         // return tableName sqlColumns headerRows in test context
 215         return [$processor->getTemporaryTable(), $sqlColumns, $processor->getHeaderRows(), $processor];
 216       }
 217
 218       // delete the export temp table and component table
 219       $sql = "DROP TABLE IF EXISTS " . $processor->getTemporaryTable();
 220       CRM_Core_DAO::executeQuery($sql);
 221       CRM_Core_DAO::reenableFullGroupByMode();
 222       CRM_Utils_System::civiExit(0, ['processor' => $processor]);
 223     }
 224     else {
 225       CRM_Core_DAO::reenableFullGroupByMode();
 226       throw new CRM_Core_Exception(ts('No records to export'));
 227     }
 228   }
 229
 230   /**
 231    * Handle import error file creation.
 232    */
 233   public static function invoke() {
 234     $type = CRM_Utils_Request::retrieve('type', 'Positive');
 235     $parserName = CRM_Utils_Request::retrieve('parser', 'String');
 236     if (empty($parserName) || empty($type)) {
 237       return;
 238     }
 239
 240     // clean and ensure parserName is a valid string
 241     $parserName = CRM_Utils_String::munge($parserName);
 242     $parserClass = explode('_', $parserName);
 243
 244     // make sure parserClass is in the CRM namespace and
 245     // at least 3 levels deep
 246     if ($parserClass[0] == 'CRM' &&
 247       count($parserClass) >= 3
 248     ) {
 249       require_once str_replace('_', DIRECTORY_SEPARATOR, $parserName) . ".php";
 250       // ensure the functions exists
 251       if (method_exists($parserName, 'errorFileName') &&
 252         method_exists($parserName, 'saveFileName')
 253       ) {
 254         $errorFileName = $parserName::errorFileName($type);
 255         $saveFileName = $parserName::saveFileName($type);
 256         if (!empty($errorFileName) && !empty($saveFileName)) {
 257           CRM_Utils_System::setHttpHeader('Cache-Control', 'must-revalidate, post-check=0, pre-check=0');
 258           CRM_Utils_System::setHttpHeader('Content-Description', 'File Transfer');
 259           CRM_Utils_System::setHttpHeader('Content-Type', 'text/csv');
 260           CRM_Utils_System::setHttpHeader('Content-Length', filesize($errorFileName));
 261           CRM_Utils_System::setHttpHeader('Content-Disposition', 'attachment; filename=' . $saveFileName);
 262
 263           readfile($errorFileName);
 264         }
 265       }
 266     }
 267     CRM_Utils_System::civiExit();
 268   }
 269
 270   /**
 271    * @param $customSearchClass
 272    * @param $formValues
 273    * @param $order
 274    */
 275   public static function exportCustom($customSearchClass, $formValues, $order) {
 276     $ext = CRM_Extension_System::singleton()->getMapper();
 277     if (!$ext->isExtensionClass($customSearchClass)) {
 278       require_once str_replace('_', DIRECTORY_SEPARATOR, $customSearchClass) . '.php';
 279     }
 280     else {
 281       require_once $ext->classToPath($customSearchClass);
 282     }
 283     $search = new $customSearchClass($formValues);
 284
 285     $includeContactIDs = FALSE;
 286     if ($formValues['radio_ts'] == 'ts_sel') {
 287       $includeContactIDs = TRUE;
 288     }
 289
 290     $sql = $search->all(0, 0, $order, $includeContactIDs);
 291
 292     $columns = $search->columns();
 293
 294     $header = array_keys($columns);
 295     $fields = array_values($columns);
 296
 297     $rows = [];
 298     $dao = CRM_Core_DAO::executeQuery($sql);
 299     $alterRow = FALSE;
 300     if (method_exists($search, 'alterRow')) {
 301       $alterRow = TRUE;
 302     }
 303     while ($dao->fetch()) {
 304       $row = [];
 305
 306       foreach ($fields as $field) {
 307         $unqualified_field = CRM_Utils_Array::First(array_slice(explode('.', $field), -1));
 308         $row[$field] = $dao->$unqualified_field;
 309       }
 310       if ($alterRow) {
 311         $search->alterRow($row);
 312       }
 313       $rows[] = $row;
 314     }
 315
 316     CRM_Core_Report_Excel::writeCSVFile(ts('CiviCRM Contact Search'), $header, $rows);
 317     CRM_Utils_System::civiExit();
 318   }
 319
 320   /**
 321    * @param \CRM_Export_BAO_ExportProcessor $processor
 322    * @param $details
 323    */
 324   public static function writeDetailsToTable($processor, $details) {
 325     $tableName = $processor->getTemporaryTable();
 326     if (empty($details)) {
 327       return;
 328     }
 329
 330     $sql = "
 331 SELECT max(id)
 332 FROM   $tableName
 333 ";
 334
 335     $id = CRM_Core_DAO::singleValueQuery($sql);
 336     if (!$id) {
 337       $id = 0;
 338     }
 339
 340     $sqlClause = [];
 341
 342     foreach ($details as $row) {
 343       $id++;
 344       $valueString = [$id];
 345       foreach ($row as $value) {
 346         if (empty($value)) {
 347           $valueString[] = "''";
 348         }
 349         else {
 350           $valueString[] = "'" . CRM_Core_DAO::escapeString($value) . "'";
 351         }
 352       }
 353       $sqlClause[] = '(' . implode(',', $valueString) . ')';
 354     }
 355     $sqlColumns = array_merge(['id' => 1], $processor->getSQLColumns());
 356     $sqlColumnString = '(' . implode(',', array_keys($sqlColumns)) . ')';
 357
 358     $sqlValueString = implode(",\n", $sqlClause);
 359
 360     $sql = "
 361 INSERT INTO $tableName $sqlColumnString
 362 VALUES $sqlValueString
 363 ";
 364     CRM_Core_DAO::executeQuery($sql);
 365   }
 366
 367   /**
 368    * @param \CRM_Export_BAO_ExportProcessor $processor
 369    */
 370   public static function writeCSVFromTable($processor) {
 371     // call export hook
 372     $headerRows = $processor->getHeaderRows();
 373     $exportTempTable = $processor->getTemporaryTable();
 374     CRM_Utils_Hook::export($exportTempTable, $headerRows, $sqlColumns, $exportMode, $componentTable, $ids);
 375     if ($exportTempTable !== $processor->getTemporaryTable()) {
 376       CRM_Core_Error::deprecatedFunctionWarning('altering the export table in the hook is deprecated (in some flows the table itself will be)');
 377       $processor->setTemporaryTable($exportTempTable);
 378     }
 379     $exportTempTable = $processor->getTemporaryTable();
 380     $writeHeader = TRUE;
 381     $offset = 0;
 382     $limit = self::EXPORT_ROW_COUNT;
 383
 384     $query = "SELECT * FROM $exportTempTable";
 385
 386     while (1) {
 387       $limitQuery = $query . "
 388 LIMIT $offset, $limit
 389 ";
 390       $dao = CRM_Core_DAO::executeQuery($limitQuery);
 391
 392       if ($dao->N <= 0) {
 393         break;
 394       }
 395
 396       $componentDetails = [];
 397       while ($dao->fetch()) {
 398         $row = [];
 399
 400         foreach (array_keys($processor->getSQLColumns()) as $column) {
 401           $row[$column] = $dao->$column;
 402         }
 403         $componentDetails[] = $row;
 404       }
 405       CRM_Core_Report_Excel::writeCSVFile($processor->getExportFileName(),
 406         $headerRows,
 407         $componentDetails,
 408         NULL,
 409         $writeHeader
 410       );
 411
 412       $writeHeader = FALSE;
 413       $offset += $limit;
 414     }
 415   }
 416
 417   /**
 418    * Get the ids that we want to get related contact details for.
 419    *
 420    * @param array $ids
 421    * @param int $exportMode
 422    *
 423    * @return array
 424    */
 425   protected static function getIDsForRelatedContact($ids, $exportMode) {
 426     if ($exportMode == CRM_Export_Form_Select::CONTACT_EXPORT) {
 427       return $ids;
 428     }
 429     if ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) {
 430       $relIDs = [];
 431       $sourceID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_ActivityContact', 'record_type_id', 'Activity Source');
 432       $dao = CRM_Core_DAO::executeQuery("
 433             SELECT contact_id FROM civicrm_activity_contact
 434             WHERE activity_id IN ( " . implode(',', $ids) . ") AND
 435             record_type_id = {$sourceID}
 436           ");
 437
 438       while ($dao->fetch()) {
 439         $relIDs[] = $dao->contact_id;
 440       }
 441       return $relIDs;
 442     }
 443     $componentMapping = [
 444       CRM_Export_Form_Select::CONTRIBUTE_EXPORT => 'civicrm_contribution',
 445       CRM_Export_Form_Select::EVENT_EXPORT => 'civicrm_participant',
 446       CRM_Export_Form_Select::MEMBER_EXPORT => 'civicrm_membership',
 447       CRM_Export_Form_Select::PLEDGE_EXPORT => 'civicrm_pledge',
 448       CRM_Export_Form_Select::GRANT_EXPORT => 'civicrm_grant',
 449     ];
 450
 451     if ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) {
 452       return CRM_Case_BAO_Case::retrieveContactIdsByCaseId($ids);
 453     }
 454     else {
 455       return CRM_Core_DAO::getContactIDsFromComponent($ids, $componentMapping[$exportMode]);
 456     }
 457   }
 458
 459   /**
 460    * @param $selectAll
 461    * @param $ids
 462    * @param \CRM_Export_BAO_ExportProcessor $processor
 463    * @param $componentTable
 464    */
 465   protected static function buildRelatedContactArray($selectAll, $ids, $processor, $componentTable) {
 466     $allRelContactArray = $relationQuery = [];
 467     $queryMode = $processor->getQueryMode();
 468     $exportMode = $processor->getExportMode();
 469
 470     foreach ($processor->getRelationshipReturnProperties() as $relationshipKey => $relationReturnProperties) {
 471       $allRelContactArray[$relationshipKey] = [];
 472       // build Query for each relationship
 473       $relationQuery = new CRM_Contact_BAO_Query(NULL, $relationReturnProperties,
 474         NULL, FALSE, FALSE, $queryMode
 475       );
 476       list($relationSelect, $relationFrom, $relationWhere, $relationHaving) = $relationQuery->query();
 477
 478       list($id, $direction) = explode('_', $relationshipKey, 2);
 479       // identify the relationship direction
 480       $contactA = 'contact_id_a';
 481       $contactB = 'contact_id_b';
 482       if ($direction == 'b_a') {
 483         $contactA = 'contact_id_b';
 484         $contactB = 'contact_id_a';
 485       }
 486       $relIDs = self::getIDsForRelatedContact($ids, $exportMode);
 487
 488       $relationshipJoin = $relationshipClause = '';
 489       if (!$selectAll && $componentTable) {
 490         $relationshipJoin = " INNER JOIN {$componentTable} ctTable ON ctTable.contact_id = {$contactA}";
 491       }
 492       elseif (!empty($relIDs)) {
 493         $relID = implode(',', $relIDs);
 494         $relationshipClause = " AND crel.{$contactA} IN ( {$relID} )";
 495       }
 496
 497       $relationFrom = " {$relationFrom}
 498               INNER JOIN civicrm_relationship crel ON crel.{$contactB} = contact_a.id AND crel.relationship_type_id = {$id}
 499               {$relationshipJoin} ";
 500
 501       //check for active relationship status only
 502       $today = date('Ymd');
 503       $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )";
 504       $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause} {$relationActive}";
 505       CRM_Core_DAO::disableFullGroupByMode();
 506       $relationSelect = "{$relationSelect}, {$contactA} as refContact ";
 507       $relationQueryString = "$relationSelect $relationFrom $relationWhere $relationHaving GROUP BY crel.{$contactA}";
 508
 509       $allRelContactDAO = CRM_Core_DAO::executeQuery($relationQueryString);
 510       CRM_Core_DAO::reenableFullGroupByMode();
 511
 512       while ($allRelContactDAO->fetch()) {
 513         $relationQuery->convertToPseudoNames($allRelContactDAO);
 514         $row = [];
 515         // @todo pass processor to fetchRelationshipDetails and set fields directly within it.
 516         $processor->fetchRelationshipDetails($allRelContactDAO, $relationReturnProperties, $relationshipKey, $row);
 517         foreach (array_keys($relationReturnProperties) as $property) {
 518           if ($property === 'location') {
 519             // @todo - simplify location in fetchRelationshipDetails - remove handling here. Or just call
 520             // $processor->setRelationshipValue from fetchRelationshipDetails
 521             foreach ($relationReturnProperties['location'] as $locationName => $locationValues) {
 522               foreach (array_keys($locationValues) as $locationValue) {
 523                 $key = str_replace(' ', '_', $locationName) . '-' . $locationValue;
 524                 $processor->setRelationshipValue($relationshipKey, $allRelContactDAO->refContact, $key, $row[$relationshipKey . '__' . $key]);
 525               }
 526             }
 527           }
 528           else {
 529             $processor->setRelationshipValue($relationshipKey, $allRelContactDAO->refContact, $property, $row[$relationshipKey . '_' . $property]);
 530           }
 531         }
 532       }
 533     }
 534   }
 535
 536 }