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