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       require_once str_replace('_', DIRECTORY_SEPARATOR, $parserName) . ".php";
 251       // ensure the functions exists
 252       if (method_exists($parserName, 'errorFileName') &&
 253         method_exists($parserName, 'saveFileName')
 254       ) {
 255         $errorFileName = $parserName::errorFileName($type);
 256         $saveFileName = $parserName::saveFileName($type);
 257         if (!empty($errorFileName) && !empty($saveFileName)) {
 258           CRM_Utils_System::setHttpHeader('Cache-Control', 'must-revalidate, post-check=0, pre-check=0');
 259           CRM_Utils_System::setHttpHeader('Content-Description', 'File Transfer');
 260           CRM_Utils_System::setHttpHeader('Content-Type', 'text/csv');
 261           CRM_Utils_System::setHttpHeader('Content-Length', filesize($errorFileName));
 262           CRM_Utils_System::setHttpHeader('Content-Disposition', 'attachment; filename=' . $saveFileName);
 263
 264           readfile($errorFileName);
 265         }
 266       }
 267     }
 268     CRM_Utils_System::civiExit();
 269   }
 270
 271   /**
 272    * @param $customSearchClass
 273    * @param $formValues
 274    * @param $order
 275    */
 276   public static function exportCustom($customSearchClass, $formValues, $order) {
 277     $ext = CRM_Extension_System::singleton()->getMapper();
 278     if (!$ext->isExtensionClass($customSearchClass)) {
 279       require_once str_replace('_', DIRECTORY_SEPARATOR, $customSearchClass) . '.php';
 280     }
 281     else {
 282       require_once $ext->classToPath($customSearchClass);
 283     }
 284     $search = new $customSearchClass($formValues);
 285
 286     $includeContactIDs = FALSE;
 287     if ($formValues['radio_ts'] == 'ts_sel') {
 288       $includeContactIDs = TRUE;
 289     }
 290
 291     $sql = $search->all(0, 0, $order, $includeContactIDs);
 292
 293     $columns = $search->columns();
 294
 295     $header = array_keys($columns);
 296     $fields = array_values($columns);
 297
 298     $rows = [];
 299     $dao = CRM_Core_DAO::executeQuery($sql);
 300     $alterRow = FALSE;
 301     if (method_exists($search, 'alterRow')) {
 302       $alterRow = TRUE;
 303     }
 304     while ($dao->fetch()) {
 305       $row = [];
 306
 307       foreach ($fields as $field) {
 308         $unqualified_field = CRM_Utils_Array::First(array_slice(explode('.', $field), -1));
 309         $row[$field] = $dao->$unqualified_field;
 310       }
 311       if ($alterRow) {
 312         $search->alterRow($row);
 313       }
 314       $rows[] = $row;
 315     }
 316
 317     CRM_Core_Report_Excel::writeCSVFile(ts('CiviCRM Contact Search'), $header, $rows);
 318     CRM_Utils_System::civiExit();
 319   }
 320
 321   /**
 322    * @param \CRM_Export_BAO_ExportProcessor $processor
 323    * @param $details
 324    */
 325   public static function writeDetailsToTable($processor, $details) {
 326     $tableName = $processor->getTemporaryTable();
 327     if (empty($details)) {
 328       return;
 329     }
 330
 331     $sql = "
 332 SELECT max(id)
 333 FROM   $tableName
 334 ";
 335
 336     $id = CRM_Core_DAO::singleValueQuery($sql);
 337     if (!$id) {
 338       $id = 0;
 339     }
 340
 341     $sqlClause = [];
 342
 343     foreach ($details as $row) {
 344       $id++;
 345       $valueString = [$id];
 346       foreach ($row as $value) {
 347         if (empty($value)) {
 348           $valueString[] = "''";
 349         }
 350         else {
 351           $valueString[] = "'" . CRM_Core_DAO::escapeString($value) . "'";
 352         }
 353       }
 354       $sqlClause[] = '(' . implode(',', $valueString) . ')';
 355     }
 356     $sqlColumns = array_merge(['id' => 1], $processor->getSQLColumns());
 357     $sqlColumnString = '(`' . implode('`, `', array_keys($sqlColumns)) . '`)';
 358
 359     $sqlValueString = implode(",\n", $sqlClause);
 360
 361     $sql = "
 362 INSERT INTO $tableName $sqlColumnString
 363 VALUES $sqlValueString
 364 ";
 365     CRM_Core_DAO::executeQuery($sql);
 366   }
 367
 368   /**
 369    * Get the ids that we want to get related contact details for.
 370    *
 371    * @param array $ids
 372    * @param int $exportMode
 373    *
 374    * @return array
 375    */
 376   protected static function getIDsForRelatedContact($ids, $exportMode) {
 377     if ($exportMode == CRM_Export_Form_Select::CONTACT_EXPORT) {
 378       return $ids;
 379     }
 380     if ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) {
 381       $relIDs = [];
 382       $sourceID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_ActivityContact', 'record_type_id', 'Activity Source');
 383       $dao = CRM_Core_DAO::executeQuery("
 384             SELECT contact_id FROM civicrm_activity_contact
 385             WHERE activity_id IN ( " . implode(',', $ids) . ") AND
 386             record_type_id = {$sourceID}
 387           ");
 388
 389       while ($dao->fetch()) {
 390         $relIDs[] = $dao->contact_id;
 391       }
 392       return $relIDs;
 393     }
 394     $componentMapping = [
 395       CRM_Export_Form_Select::CONTRIBUTE_EXPORT => 'civicrm_contribution',
 396       CRM_Export_Form_Select::EVENT_EXPORT => 'civicrm_participant',
 397       CRM_Export_Form_Select::MEMBER_EXPORT => 'civicrm_membership',
 398       CRM_Export_Form_Select::PLEDGE_EXPORT => 'civicrm_pledge',
 399       CRM_Export_Form_Select::GRANT_EXPORT => 'civicrm_grant',
 400     ];
 401
 402     if ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) {
 403       return CRM_Case_BAO_Case::retrieveContactIdsByCaseId($ids);
 404     }
 405     else {
 406       return CRM_Core_DAO::getContactIDsFromComponent($ids, $componentMapping[$exportMode]);
 407     }
 408   }
 409
 410   /**
 411    * @param $selectAll
 412    * @param $ids
 413    * @param \CRM_Export_BAO_ExportProcessor $processor
 414    * @param $componentTable
 415    */
 416   protected static function buildRelatedContactArray($selectAll, $ids, $processor, $componentTable) {
 417     $allRelContactArray = $relationQuery = [];
 418     $queryMode = $processor->getQueryMode();
 419     $exportMode = $processor->getExportMode();
 420
 421     foreach ($processor->getRelationshipReturnProperties() as $relationshipKey => $relationReturnProperties) {
 422       $allRelContactArray[$relationshipKey] = [];
 423       // build Query for each relationship
 424       $relationQuery = new CRM_Contact_BAO_Query(NULL, $relationReturnProperties,
 425         NULL, FALSE, FALSE, $queryMode
 426       );
 427       list($relationSelect, $relationFrom, $relationWhere, $relationHaving) = $relationQuery->query();
 428
 429       list($id, $direction) = explode('_', $relationshipKey, 2);
 430       // identify the relationship direction
 431       $contactA = 'contact_id_a';
 432       $contactB = 'contact_id_b';
 433       if ($direction == 'b_a') {
 434         $contactA = 'contact_id_b';
 435         $contactB = 'contact_id_a';
 436       }
 437       $relIDs = self::getIDsForRelatedContact($ids, $exportMode);
 438
 439       $relationshipJoin = $relationshipClause = '';
 440       if (!$selectAll && $componentTable) {
 441         $field = 'contact_id';
 442         if ($componentTable === 'civicrm_contact') {
 443           $field = 'id';
 444         }
 445         $relationshipJoin = " INNER JOIN {$componentTable} ctTable ON ctTable.$field = {$contactA}";
 446       }
 447       elseif (!empty($relIDs)) {
 448         $relID = implode(',', $relIDs);
 449         $relationshipClause = " AND crel.{$contactA} IN ( {$relID} )";
 450       }
 451
 452       $relationFrom = " {$relationFrom}
 453               INNER JOIN civicrm_relationship crel ON crel.{$contactB} = contact_a.id AND crel.relationship_type_id = {$id}
 454               {$relationshipJoin} ";
 455
 456       //check for active relationship status only
 457       $today = date('Ymd');
 458       $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )";
 459       $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause} {$relationActive}";
 460       CRM_Core_DAO::disableFullGroupByMode();
 461       $relationSelect = "{$relationSelect}, {$contactA} as refContact ";
 462       $relationQueryString = "$relationSelect $relationFrom $relationWhere $relationHaving GROUP BY crel.{$contactA}";
 463
 464       $allRelContactDAO = CRM_Core_DAO::executeQuery($relationQueryString);
 465       CRM_Core_DAO::reenableFullGroupByMode();
 466
 467       while ($allRelContactDAO->fetch()) {
 468         $relationQuery->convertToPseudoNames($allRelContactDAO);
 469         $row = [];
 470         // @todo pass processor to fetchRelationshipDetails and set fields directly within it.
 471         $processor->fetchRelationshipDetails($allRelContactDAO, $relationReturnProperties, $relationshipKey, $row);
 472         foreach (array_keys($relationReturnProperties) as $property) {
 473           if ($property === 'location') {
 474             // @todo - simplify location in fetchRelationshipDetails - remove handling here. Or just call
 475             // $processor->setRelationshipValue from fetchRelationshipDetails
 476             foreach ($relationReturnProperties['location'] as $locationName => $locationValues) {
 477               foreach (array_keys($locationValues) as $locationValue) {
 478                 $key = str_replace(' ', '_', $locationName) . '-' . $locationValue;
 479                 $processor->setRelationshipValue($relationshipKey, $allRelContactDAO->refContact, $key, $row[$relationshipKey . '__' . $key]);
 480               }
 481             }
 482           }
 483           else {
 484             $processor->setRelationshipValue($relationshipKey, $allRelContactDAO->refContact, $property, $row[$relationshipKey . '_' . $property]);
 485           }
 486         }
 487       }
 488     }
 489   }
 490
 491 }