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