CRM/Export/BAO/Export.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17
  18 /**
  19  * This class contains the functions for Component export
  20  *
  21  */
  22 class CRM_Export_BAO_Export {
  23   // increase this number a lot to avoid making too many queries
  24   // LIMIT is not much faster than a no LIMIT query
  25   // CRM-7675
  26   const EXPORT_ROW_COUNT = 100000;
  27
  28   /**
  29    * Returns a list of exportable entities and their associated component.
  30    *
  31    * Note: Some entities like Contact & Activity are not in components, so
  32    * the export form seems to fudge things and accept the entity name instead of
  33    * component name in those cases.
  34    *
  35    * TODO: Hardcoded list bad. Needs to support extension export pages.
  36    *
  37    * @var string[]
  38    */
  39   public static function getComponents() {
  40     return [
  41       'Contact' => 'Contact',
  42       'Contribution' => 'Contribute',
  43       'Membership' => 'Member',
  44       'Participant' => 'Event',
  45       'Pledge' => 'Pledge',
  46       'Case' => 'Case',
  47       'Grant' => 'Grant',
  48       'Activity' => 'Activity',
  49     ];
  50   }
  51
  52   /**
  53    * Get the list the export fields.
  54    *
  55    * @param int $selectAll
  56    *   User preference while export.
  57    * @param array $ids
  58    *   Contact ids.
  59    * @param array $params
  60    *   Associated array of fields.
  61    * @param string $order
  62    *   Order by clause.
  63    * @param array $fields
  64    *   Associated array of fields.
  65    * @param array $moreReturnProperties
  66    *   Additional return fields.
  67    * @param int $exportMode
  68    *   Export mode.
  69    * @param string $componentClause
  70    *   Component clause.
  71    * @param string $componentTable
  72    *   Component table.
  73    * @param bool $mergeSameAddress
  74    *   Merge records if they have same address.
  75    * @param bool $mergeSameHousehold
  76    *   Merge records if they belong to the same household.
  77    *
  78    * @param array $exportParams
  79    * @param string $queryOperator
  80    *
  81    * @throws \CRM_Core_Exception
  82    */
  83   public static function exportComponents(
  84     $selectAll,
  85     $ids,
  86     $params,
  87     $order = NULL,
  88     $fields = NULL,
  89     $moreReturnProperties = NULL,
  90     $exportMode = CRM_Export_Form_Select::CONTACT_EXPORT,
  91     $componentClause = NULL,
  92     $componentTable = NULL,
  93     $mergeSameAddress = FALSE,
  94     $mergeSameHousehold = FALSE,
  95     $exportParams = [],
  96     $queryOperator = 'AND'
  97   ) {
  98
  99     $isPostalOnly = (
 100       isset($exportParams['postal_mailing_export']['postal_mailing_export']) &&
 101       $exportParams['postal_mailing_export']['postal_mailing_export'] == 1
 102     );
 103
 104     if (!$selectAll && $componentTable && !empty($exportParams['additional_group'])) {
 105       // If an Additional Group is selected, then all contacts in that group are
 106       // added to the export set (filtering out duplicates).
 107       // Really - the calling function could do this ... just saying
 108       // @todo take a whip to the calling function.
 109       CRM_Core_DAO::executeQuery("
 110 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"
 111       );
 112     }
 113     // rectify params to what proximity search expects if there is a value for prox_distance
 114     // CRM-7021
 115     // @todo - move this back to the calling functions
 116     if (!empty($params)) {
 117       CRM_Contact_BAO_ProximityQuery::fixInputParams($params);
 118     }
 119     // @todo everything from this line up should go back to the calling functions.
 120     $processor = new CRM_Export_BAO_ExportProcessor($exportMode, $fields, $queryOperator, $mergeSameHousehold, $isPostalOnly, $mergeSameAddress, $exportParams);
 121     if ($moreReturnProperties) {
 122       $processor->setAdditionalRequestedReturnProperties($moreReturnProperties);
 123     }
 124     $processor->setComponentTable($componentTable);
 125     $processor->setComponentClause($componentClause);
 126     $processor->setIds($ids);
 127
 128     list($query, $queryString) = $processor->runQuery($params, $order);
 129
 130     // This perhaps only needs calling when $mergeSameHousehold == 1
 131     self::buildRelatedContactArray($selectAll, $ids, $processor, $componentTable);
 132
 133     $addPaymentHeader = FALSE;
 134
 135     list($outputColumns) = $processor->getExportStructureArrays();
 136
 137     if ($processor->isMergeSameAddress()) {
 138       foreach (array_keys($processor->getAdditionalFieldsForSameAddressMerge()) as $field) {
 139         if ($field === 'id') {
 140           $field = 'civicrm_primary_id';
 141         }
 142         $processor->setColumnAsCalculationOnly($field);
 143       }
 144     }
 145
 146     $paymentDetails = [];
 147     if ($processor->isExportPaymentFields()) {
 148       // get payment related in for event and members
 149       $paymentDetails = CRM_Contribute_BAO_Contribution::getContributionDetails($exportMode, $ids);
 150       //get all payment headers.
 151       // If we haven't selected specific payment fields, load in all the
 152       // payment headers.
 153       if (!$processor->isExportSpecifiedPaymentFields()) {
 154         if (!empty($paymentDetails)) {
 155           $addPaymentHeader = TRUE;
 156           foreach (array_keys($processor->getPaymentHeaders()) as $paymentField) {
 157             $processor->addOutputSpecification($paymentField);
 158           }
 159         }
 160       }
 161     }
 162
 163     $componentDetails = [];
 164
 165     $rowCount = self::EXPORT_ROW_COUNT;
 166     $offset = 0;
 167     // we write to temp table often to avoid using too much memory
 168     $tempRowCount = 100;
 169
 170     $count = -1;
 171
 172     $sqlColumns = $processor->getSQLColumns();
 173     $processor->createTempTable();
 174     $limitReached = FALSE;
 175
 176     while (!$limitReached) {
 177       $limitQuery = "{$queryString} LIMIT {$offset}, {$rowCount}";
 178       CRM_Core_DAO::disableFullGroupByMode();
 179       $iterationDAO = CRM_Core_DAO::executeQuery($limitQuery);
 180       CRM_Core_DAO::reenableFullGroupByMode();
 181       // If this is less than our limit by the end of the iteration we do not need to run the query again to
 182       // check if some remain.
 183       $rowsThisIteration = 0;
 184
 185       while ($iterationDAO->fetch()) {
 186         $count++;
 187         $rowsThisIteration++;
 188         $row = $processor->buildRow($query, $iterationDAO, $outputColumns, $paymentDetails, $addPaymentHeader);
 189         if ($row === FALSE) {
 190           continue;
 191         }
 192
 193         // add component info
 194         // write the row to a file
 195         $componentDetails[] = $row;
 196
 197         // output every $tempRowCount rows
 198         if ($count % $tempRowCount == 0) {
 199           self::writeDetailsToTable($processor, $componentDetails, $sqlColumns);
 200           $componentDetails = [];
 201         }
 202       }
 203       if ($rowsThisIteration < self::EXPORT_ROW_COUNT) {
 204         $limitReached = TRUE;
 205       }
 206       $offset += $rowCount;
 207     }
 208
 209     if ($processor->getTemporaryTable()) {
 210       self::writeDetailsToTable($processor, $componentDetails);
 211
 212       // do merge same address and merge same household processing
 213       if ($mergeSameAddress) {
 214         $processor->mergeSameAddress();
 215       }
 216
 217       $processor->writeCSVFromTable();
 218
 219       // delete the export temp table and component table
 220       $sql = "DROP TABLE IF EXISTS " . $processor->getTemporaryTable();
 221       CRM_Core_DAO::executeQuery($sql);
 222       CRM_Core_DAO::reenableFullGroupByMode();
 223       CRM_Utils_System::civiExit(0, ['processor' => $processor]);
 224     }
 225     else {
 226       CRM_Core_DAO::reenableFullGroupByMode();
 227       throw new CRM_Core_Exception(ts('No records to export'));
 228     }
 229   }
 230
 231   /**
 232    * Handle import error file creation.
 233    */
 234   public static function invoke() {
 235     $type = CRM_Utils_Request::retrieve('type', 'Positive');
 236     $parserName = CRM_Utils_Request::retrieve('parser', 'String');
 237     if (empty($parserName) || empty($type)) {
 238       return;
 239     }
 240
 241     // clean and ensure parserName is a valid string
 242     $parserName = CRM_Utils_String::munge($parserName);
 243     $parserClass = explode('_', $parserName);
 244
 245     // make sure parserClass is in the CRM namespace and
 246     // at least 3 levels deep
 247     if ($parserClass[0] == 'CRM' &&
 248       count($parserClass) >= 3
 249     ) {
 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    * Get the ids that we want to get related contact details for.
 369    *
 370    * @param array $ids
 371    * @param int $exportMode
 372    *
 373    * @return array
 374    */
 375   protected static function getIDsForRelatedContact($ids, $exportMode) {
 376     if ($exportMode == CRM_Export_Form_Select::CONTACT_EXPORT) {
 377       return $ids;
 378     }
 379     if ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) {
 380       $relIDs = [];
 381       $sourceID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_ActivityContact', 'record_type_id', 'Activity Source');
 382       $dao = CRM_Core_DAO::executeQuery("
 383             SELECT contact_id FROM civicrm_activity_contact
 384             WHERE activity_id IN ( " . implode(',', $ids) . ") AND
 385             record_type_id = {$sourceID}
 386           ");
 387
 388       while ($dao->fetch()) {
 389         $relIDs[] = $dao->contact_id;
 390       }
 391       return $relIDs;
 392     }
 393     $componentMapping = [
 394       CRM_Export_Form_Select::CONTRIBUTE_EXPORT => 'civicrm_contribution',
 395       CRM_Export_Form_Select::EVENT_EXPORT => 'civicrm_participant',
 396       CRM_Export_Form_Select::MEMBER_EXPORT => 'civicrm_membership',
 397       CRM_Export_Form_Select::PLEDGE_EXPORT => 'civicrm_pledge',
 398       CRM_Export_Form_Select::GRANT_EXPORT => 'civicrm_grant',
 399     ];
 400
 401     if ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) {
 402       return CRM_Case_BAO_Case::retrieveContactIdsByCaseId($ids);
 403     }
 404     else {
 405       return CRM_Core_DAO::getContactIDsFromComponent($ids, $componentMapping[$exportMode]);
 406     }
 407   }
 408
 409   /**
 410    * @param $selectAll
 411    * @param $ids
 412    * @param \CRM_Export_BAO_ExportProcessor $processor
 413    * @param $componentTable
 414    */
 415   protected static function buildRelatedContactArray($selectAll, $ids, $processor, $componentTable) {
 416     $allRelContactArray = $relationQuery = [];
 417     $queryMode = $processor->getQueryMode();
 418     $exportMode = $processor->getExportMode();
 419
 420     foreach ($processor->getRelationshipReturnProperties() as $relationshipKey => $relationReturnProperties) {
 421       $allRelContactArray[$relationshipKey] = [];
 422       // build Query for each relationship
 423       $relationQuery = new CRM_Contact_BAO_Query(NULL, $relationReturnProperties,
 424         NULL, FALSE, FALSE, $queryMode
 425       );
 426       list($relationSelect, $relationFrom, $relationWhere, $relationHaving) = $relationQuery->query();
 427
 428       list($id, $direction) = explode('_', $relationshipKey, 2);
 429       // identify the relationship direction
 430       $contactA = 'contact_id_a';
 431       $contactB = 'contact_id_b';
 432       if ($direction == 'b_a') {
 433         $contactA = 'contact_id_b';
 434         $contactB = 'contact_id_a';
 435       }
 436       $relIDs = self::getIDsForRelatedContact($ids, $exportMode);
 437
 438       $relationshipJoin = $relationshipClause = '';
 439       if (!$selectAll && $componentTable) {
 440         $field = 'contact_id';
 441         if ($componentTable === 'civicrm_contact') {
 442           $field = 'id';
 443         }
 444         $relationshipJoin = " INNER JOIN {$componentTable} ctTable ON ctTable.$field = {$contactA}";
 445       }
 446       elseif (!empty($relIDs)) {
 447         $relID = implode(',', $relIDs);
 448         $relationshipClause = " AND crel.{$contactA} IN ( {$relID} )";
 449       }
 450
 451       $relationFrom = " {$relationFrom}
 452               INNER JOIN civicrm_relationship crel ON crel.{$contactB} = contact_a.id AND crel.relationship_type_id = {$id}
 453               {$relationshipJoin} ";
 454
 455       //check for active relationship status only
 456       $today = date('Ymd');
 457       $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )";
 458       $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause} {$relationActive}";
 459       CRM_Core_DAO::disableFullGroupByMode();
 460       $relationSelect = "{$relationSelect}, {$contactA} as refContact ";
 461       $relationQueryString = "$relationSelect $relationFrom $relationWhere $relationHaving GROUP BY crel.{$contactA}";
 462
 463       $allRelContactDAO = CRM_Core_DAO::executeQuery($relationQueryString);
 464       CRM_Core_DAO::reenableFullGroupByMode();
 465
 466       while ($allRelContactDAO->fetch()) {
 467         $relationQuery->convertToPseudoNames($allRelContactDAO);
 468         $row = [];
 469         // @todo pass processor to fetchRelationshipDetails and set fields directly within it.
 470         $processor->fetchRelationshipDetails($allRelContactDAO, $relationReturnProperties, $relationshipKey, $row);
 471         foreach (array_keys($relationReturnProperties) as $property) {
 472           if ($property === 'location') {
 473             // @todo - simplify location in fetchRelationshipDetails - remove handling here. Or just call
 474             // $processor->setRelationshipValue from fetchRelationshipDetails
 475             foreach ($relationReturnProperties['location'] as $locationName => $locationValues) {
 476               foreach (array_keys($locationValues) as $locationValue) {
 477                 $key = str_replace(' ', '_', $locationName) . '-' . $locationValue;
 478                 $processor->setRelationshipValue($relationshipKey, $allRelContactDAO->refContact, $key, $row[$relationshipKey . '__' . $key]);
 479               }
 480             }
 481           }
 482           else {
 483             $processor->setRelationshipValue($relationshipKey, $allRelContactDAO->refContact, $property, $row[$relationshipKey . '_' . $property]);
 484           }
 485         }
 486       }
 487     }
 488   }
 489
 490 }