Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
fee14197 | 4 | | CiviCRM version 5 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
6b83d5bd | 6 | | Copyright CiviCRM LLC (c) 2004-2019 | |
6a488035 TO |
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 | +--------------------------------------------------------------------+ | |
d25dd0ee | 26 | */ |
6a488035 TO |
27 | |
28 | /** | |
29 | * | |
30 | * @package CRM | |
6b83d5bd | 31 | * @copyright CiviCRM LLC (c) 2004-2019 |
6a488035 TO |
32 | */ |
33 | ||
34 | /** | |
748450ad | 35 | * This class contains the functions for Component export |
6a488035 TO |
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 | |
24c7dffa | 42 | const EXPORT_ROW_COUNT = 100000; |
6a488035 | 43 | |
f34f5143 SL |
44 | /** |
45 | * Get default return property for export based on mode | |
46 | * | |
47 | * @param int $exportMode | |
48 | * Export mode. | |
919eddec | 49 | * |
7b966967 | 50 | * @return string |
748450ad | 51 | * Default Return property |
f34f5143 | 52 | */ |
2e28a8b9 | 53 | public static function defaultReturnProperty($exportMode) { |
748450ad | 54 | // hack to add default return property based on export mode |
0dc29086 | 55 | $property = NULL; |
f34f5143 | 56 | if ($exportMode == CRM_Export_Form_Select::CONTRIBUTE_EXPORT) { |
919eddec | 57 | $property = 'contribution_id'; |
f34f5143 SL |
58 | } |
59 | elseif ($exportMode == CRM_Export_Form_Select::EVENT_EXPORT) { | |
919eddec | 60 | $property = 'participant_id'; |
f34f5143 SL |
61 | } |
62 | elseif ($exportMode == CRM_Export_Form_Select::MEMBER_EXPORT) { | |
919eddec | 63 | $property = 'membership_id'; |
f34f5143 SL |
64 | } |
65 | elseif ($exportMode == CRM_Export_Form_Select::PLEDGE_EXPORT) { | |
919eddec | 66 | $property = 'pledge_id'; |
f34f5143 SL |
67 | } |
68 | elseif ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) { | |
919eddec | 69 | $property = 'case_id'; |
f34f5143 SL |
70 | } |
71 | elseif ($exportMode == CRM_Export_Form_Select::GRANT_EXPORT) { | |
919eddec | 72 | $property = 'grant_id'; |
f34f5143 SL |
73 | } |
74 | elseif ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) { | |
919eddec | 75 | $property = 'activity_id'; |
f34f5143 | 76 | } |
919eddec | 77 | return $property; |
f34f5143 SL |
78 | } |
79 | ||
80 | /** | |
81 | * Get Export component | |
82 | * | |
83 | * @param int $exportMode | |
84 | * Export mode. | |
85 | * | |
7b966967 | 86 | * @return string |
748450ad | 87 | * CiviCRM Export Component |
f34f5143 | 88 | */ |
7bf9f91e | 89 | public static function exportComponent($exportMode) { |
f34f5143 SL |
90 | switch ($exportMode) { |
91 | case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: | |
92 | $component = 'civicrm_contribution'; | |
93 | break; | |
94 | ||
95 | case CRM_Export_Form_Select::EVENT_EXPORT: | |
96 | $component = 'civicrm_participant'; | |
97 | break; | |
98 | ||
99 | case CRM_Export_Form_Select::MEMBER_EXPORT: | |
100 | $component = 'civicrm_membership'; | |
101 | break; | |
102 | ||
103 | case CRM_Export_Form_Select::PLEDGE_EXPORT: | |
104 | $component = 'civicrm_pledge'; | |
105 | break; | |
106 | ||
107 | case CRM_Export_Form_Select::GRANT_EXPORT: | |
108 | $component = 'civicrm_grant'; | |
109 | break; | |
110 | } | |
111 | return $component; | |
112 | } | |
113 | ||
748450ad | 114 | /** |
f1ff3965 | 115 | * Get Query Group By Clause |
439355f5 | 116 | * @param \CRM_Export_BAO_ExportProcessor $processor |
748450ad | 117 | * Export Mode |
748450ad SL |
118 | * @param array $returnProperties |
119 | * Return Properties | |
0cc9927d SL |
120 | * @param object $query |
121 | * CRM_Contact_BAO_Query | |
122 | * | |
7b966967 | 123 | * @return string |
748450ad SL |
124 | * Group By Clause |
125 | */ | |
439355f5 | 126 | public static function getGroupBy($processor, $returnProperties, $query) { |
d5148d71 | 127 | $groupBy = NULL; |
439355f5 | 128 | $exportMode = $processor->getExportMode(); |
129 | $queryMode = $processor->getQueryMode(); | |
748450ad SL |
130 | if (!empty($returnProperties['tags']) || !empty($returnProperties['groups']) || |
131 | CRM_Utils_Array::value('notes', $returnProperties) || | |
132 | // CRM-9552 | |
133 | ($queryMode & CRM_Contact_BAO_Query::MODE_CONTACTS && $query->_useGroupBy) | |
134 | ) { | |
3636b520 | 135 | $groupBy = "contact_a.id"; |
748450ad SL |
136 | } |
137 | ||
138 | switch ($exportMode) { | |
139 | case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: | |
3636b520 | 140 | $groupBy = 'civicrm_contribution.id'; |
748450ad SL |
141 | if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) { |
142 | // especial group by when soft credit columns are included | |
d5148d71 | 143 | $groupBy = ['contribution_search_scredit_combined.id', 'contribution_search_scredit_combined.scredit_id']; |
748450ad SL |
144 | } |
145 | break; | |
146 | ||
147 | case CRM_Export_Form_Select::EVENT_EXPORT: | |
3636b520 | 148 | $groupBy = 'civicrm_participant.id'; |
748450ad SL |
149 | break; |
150 | ||
151 | case CRM_Export_Form_Select::MEMBER_EXPORT: | |
3636b520 | 152 | $groupBy = "civicrm_membership.id"; |
748450ad SL |
153 | break; |
154 | } | |
155 | ||
156 | if ($queryMode & CRM_Contact_BAO_Query::MODE_ACTIVITY) { | |
3636b520 | 157 | $groupBy = "civicrm_activity.id "; |
748450ad | 158 | } |
ee027e3b | 159 | |
d5148d71 | 160 | return $groupBy ? ' GROUP BY ' . implode(', ', (array) $groupBy) : ''; |
748450ad SL |
161 | } |
162 | ||
6a488035 | 163 | /** |
fe482240 | 164 | * Get the list the export fields. |
6a488035 | 165 | * |
b9add4b3 TO |
166 | * @param int $selectAll |
167 | * User preference while export. | |
168 | * @param array $ids | |
169 | * Contact ids. | |
170 | * @param array $params | |
171 | * Associated array of fields. | |
172 | * @param string $order | |
173 | * Order by clause. | |
174 | * @param array $fields | |
175 | * Associated array of fields. | |
176 | * @param array $moreReturnProperties | |
177 | * Additional return fields. | |
178 | * @param int $exportMode | |
179 | * Export mode. | |
180 | * @param string $componentClause | |
181 | * Component clause. | |
182 | * @param string $componentTable | |
183 | * Component table. | |
184 | * @param bool $mergeSameAddress | |
185 | * Merge records if they have same address. | |
186 | * @param bool $mergeSameHousehold | |
187 | * Merge records if they belong to the same household. | |
6c8f6e67 EM |
188 | * |
189 | * @param array $exportParams | |
190 | * @param string $queryOperator | |
6a488035 | 191 | * |
c7224305 | 192 | * @return array|null |
193 | * An array can be requested from within a unit test. | |
194 | * | |
195 | * @throws \CRM_Core_Exception | |
6a488035 | 196 | */ |
317fceb4 | 197 | public static function exportComponents( |
97f6897c | 198 | $selectAll, |
6a488035 TO |
199 | $ids, |
200 | $params, | |
201 | $order = NULL, | |
202 | $fields = NULL, | |
203 | $moreReturnProperties = NULL, | |
204 | $exportMode = CRM_Export_Form_Select::CONTACT_EXPORT, | |
205 | $componentClause = NULL, | |
206 | $componentTable = NULL, | |
207 | $mergeSameAddress = FALSE, | |
208 | $mergeSameHousehold = FALSE, | |
be2fb01f | 209 | $exportParams = [], |
6a488035 TO |
210 | $queryOperator = 'AND' |
211 | ) { | |
ef51caa8 | 212 | |
6d52bfe5 | 213 | $isPostalOnly = ( |
214 | isset($exportParams['postal_mailing_export']['postal_mailing_export']) && | |
215 | $exportParams['postal_mailing_export']['postal_mailing_export'] == 1 | |
216 | ); | |
217 | ||
218 | $processor = new CRM_Export_BAO_ExportProcessor($exportMode, $fields, $queryOperator, $mergeSameHousehold, $isPostalOnly); | |
be2fb01f | 219 | $returnProperties = []; |
6a488035 | 220 | |
6a488035 | 221 | if ($fields) { |
6a488035 | 222 | foreach ($fields as $key => $value) { |
81649c48 | 223 | $fieldName = CRM_Utils_Array::value(1, $value); |
136f69a8 | 224 | if (!$fieldName || $processor->isHouseholdMergeRelationshipTypeKey($fieldName)) { |
6a488035 TO |
225 | continue; |
226 | } | |
6a488035 | 227 | |
944ed388 | 228 | if ($processor->isRelationshipTypeKey($fieldName) && (!empty($value[2]) || !empty($value[4]))) { |
704e3e9a | 229 | $returnProperties[$fieldName] = $processor->setRelationshipReturnProperties($value, $fieldName); |
b0eb1d74 | 230 | } |
231 | elseif (is_numeric(CRM_Utils_Array::value(2, $value))) { | |
a16a432a | 232 | $locationName = CRM_Core_PseudoConstant::getName('CRM_Core_BAO_Address', 'location_type_id', $value[2]); |
b0eb1d74 | 233 | if ($fieldName == 'phone') { |
a16a432a | 234 | $returnProperties['location'][$locationName]['phone-' . CRM_Utils_Array::value(3, $value)] = 1; |
6a488035 | 235 | } |
b0eb1d74 | 236 | elseif ($fieldName == 'im') { |
a16a432a | 237 | $returnProperties['location'][$locationName]['im-' . CRM_Utils_Array::value(3, $value)] = 1; |
6a488035 TO |
238 | } |
239 | else { | |
a16a432a | 240 | $returnProperties['location'][$locationName][$fieldName] = 1; |
6a488035 TO |
241 | } |
242 | } | |
243 | else { | |
244 | //hack to fix component fields | |
8b8fa582 | 245 | //revert mix of event_id and title |
6a488035 | 246 | if ($fieldName == 'event_id') { |
8b8fa582 | 247 | $returnProperties['event_id'] = 1; |
6a488035 TO |
248 | } |
249 | else { | |
250 | $returnProperties[$fieldName] = 1; | |
6a488035 TO |
251 | } |
252 | } | |
253 | } | |
f06b9233 | 254 | $defaultExportMode = self::defaultReturnProperty($exportMode); |
92a99d2b | 255 | if ($defaultExportMode) { |
f06b9233 | 256 | $returnProperties[$defaultExportMode] = 1; |
0dc29086 | 257 | } |
919eddec | 258 | } |
6a488035 | 259 | else { |
d28b6cf2 | 260 | $returnProperties = $processor->getDefaultReturnProperties(); |
6a488035 | 261 | } |
c66a5741 | 262 | // @todo - we are working towards this being entirely a property of the processor |
263 | $processor->setReturnProperties($returnProperties); | |
264 | $paymentTableId = $processor->getPaymentTableID(); | |
6a488035 TO |
265 | |
266 | if ($mergeSameAddress) { | |
267 | //make sure the addressee fields are selected | |
268 | //while using merge same address feature | |
269 | $returnProperties['addressee'] = 1; | |
270 | $returnProperties['postal_greeting'] = 1; | |
271 | $returnProperties['email_greeting'] = 1; | |
272 | $returnProperties['street_name'] = 1; | |
273 | $returnProperties['household_name'] = 1; | |
274 | $returnProperties['street_address'] = 1; | |
275 | $returnProperties['city'] = 1; | |
276 | $returnProperties['state_province'] = 1; | |
277 | ||
278 | // some columns are required for assistance incase they are not already present | |
be2fb01f CW |
279 | $exportParams['merge_same_address']['temp_columns'] = []; |
280 | $tempColumns = ['id', 'master_id', 'state_province_id', 'postal_greeting_id', 'addressee_id']; | |
6a488035 TO |
281 | foreach ($tempColumns as $column) { |
282 | if (!array_key_exists($column, $returnProperties)) { | |
283 | $returnProperties[$column] = 1; | |
284 | $column = $column == 'id' ? 'civicrm_primary_id' : $column; | |
285 | $exportParams['merge_same_address']['temp_columns'][$column] = 1; | |
286 | } | |
287 | } | |
288 | } | |
289 | ||
8cc574cf | 290 | if (!$selectAll && $componentTable && !empty($exportParams['additional_group'])) { |
6a488035 TO |
291 | // If an Additional Group is selected, then all contacts in that group are |
292 | // added to the export set (filtering out duplicates). | |
293 | $query = " | |
294 | 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"; | |
295 | CRM_Core_DAO::executeQuery($query); | |
296 | } | |
297 | ||
298 | if ($moreReturnProperties) { | |
299 | // fix for CRM-7066 | |
a7488080 | 300 | if (!empty($moreReturnProperties['group'])) { |
6a488035 TO |
301 | unset($moreReturnProperties['group']); |
302 | $moreReturnProperties['groups'] = 1; | |
303 | } | |
304 | $returnProperties = array_merge($returnProperties, $moreReturnProperties); | |
305 | } | |
306 | ||
be2fb01f | 307 | $exportParams['postal_mailing_export']['temp_columns'] = []; |
6a488035 TO |
308 | if ($exportParams['exportOption'] == 2 && |
309 | isset($exportParams['postal_mailing_export']) && | |
310 | CRM_Utils_Array::value('postal_mailing_export', $exportParams['postal_mailing_export']) == 1 | |
311 | ) { | |
be2fb01f | 312 | $postalColumns = ['is_deceased', 'do_not_mail', 'street_address', 'supplemental_address_1']; |
6a488035 TO |
313 | foreach ($postalColumns as $column) { |
314 | if (!array_key_exists($column, $returnProperties)) { | |
315 | $returnProperties[$column] = 1; | |
316 | $exportParams['postal_mailing_export']['temp_columns'][$column] = 1; | |
317 | } | |
318 | } | |
319 | } | |
320 | ||
9e49a7b8 PJ |
321 | // rectify params to what proximity search expects if there is a value for prox_distance |
322 | // CRM-7021 | |
323 | if (!empty($params)) { | |
324 | CRM_Contact_BAO_ProximityQuery::fixInputParams($params); | |
325 | } | |
326 | ||
71464b73 | 327 | list($query, $select, $from, $where, $having) = $processor->runQuery($params, $order, $returnProperties); |
6a488035 TO |
328 | |
329 | if ($mergeSameHousehold == 1) { | |
0dc29086 | 330 | if (empty($returnProperties['id'])) { |
6a488035 TO |
331 | $returnProperties['id'] = 1; |
332 | } | |
333 | ||
136f69a8 | 334 | $processor->setHouseholdMergeReturnProperties(array_diff_key($returnProperties, array_fill_keys(['location_type', 'im_provider'], 1))); |
6a488035 TO |
335 | } |
336 | ||
ce12a9e0 | 337 | self::buildRelatedContactArray($selectAll, $ids, $processor, $componentTable); |
6a488035 TO |
338 | |
339 | // make sure the groups stuff is included only if specifically specified | |
340 | // by the fields param (CRM-1969), else we limit the contacts outputted to only | |
341 | // ones that are part of a group | |
a7488080 | 342 | if (!empty($returnProperties['groups'])) { |
6a488035 TO |
343 | $oldClause = "( contact_a.id = civicrm_group_contact.contact_id )"; |
344 | $newClause = " ( $oldClause AND ( civicrm_group_contact.status = 'Added' OR civicrm_group_contact.status IS NULL ) )"; | |
345 | // total hack for export, CRM-3618 | |
346 | $from = str_replace($oldClause, | |
347 | $newClause, | |
348 | $from | |
349 | ); | |
350 | } | |
351 | ||
34b773b7 | 352 | if (!$selectAll && $componentTable) { |
6a488035 TO |
353 | $from .= " INNER JOIN $componentTable ctTable ON ctTable.contact_id = contact_a.id "; |
354 | } | |
355 | elseif ($componentClause) { | |
356 | if (empty($where)) { | |
357 | $where = "WHERE $componentClause"; | |
358 | } | |
359 | else { | |
360 | $where .= " AND $componentClause"; | |
361 | } | |
362 | } | |
363 | ||
14a08308 | 364 | // CRM-13982 - check if is deleted |
365 | $excludeTrashed = TRUE; | |
366 | foreach ($params as $value) { | |
367 | if ($value[0] == 'contact_is_deleted') { | |
368 | $excludeTrashed = FALSE; | |
369 | } | |
370 | } | |
17bc4f1b SL |
371 | $trashClause = $excludeTrashed ? "contact_a.is_deleted != 1" : "( 1 )"; |
372 | ||
0b02cdf2 | 373 | if (empty($where)) { |
17bc4f1b | 374 | $where = "WHERE $trashClause"; |
14a08308 | 375 | } |
17bc4f1b SL |
376 | else { |
377 | $where .= " AND $trashClause"; | |
14a08308 | 378 | } |
379 | ||
6a488035 TO |
380 | $queryString = "$select $from $where $having"; |
381 | ||
439355f5 | 382 | $groupBy = self::getGroupBy($processor, $returnProperties, $query); |
8a13a745 | 383 | |
6a488035 | 384 | $queryString .= $groupBy; |
33a5a53d | 385 | |
6a488035 | 386 | if ($order) { |
7f5cc737 | 387 | // always add contact_a.id to the ORDER clause |
388 | // so the order is deterministic | |
389 | //CRM-15301 | |
390 | if (strpos('contact_a.id', $order) === FALSE) { | |
391 | $order .= ", contact_a.id"; | |
392 | } | |
5a6afd32 | 393 | |
6a488035 TO |
394 | list($field, $dir) = explode(' ', $order, 2); |
395 | $field = trim($field); | |
a7488080 | 396 | if (!empty($returnProperties[$field])) { |
33a5a53d | 397 | //CRM-15301 |
398 | $queryString .= " ORDER BY $order"; | |
6a488035 TO |
399 | } |
400 | } | |
401 | ||
6a488035 TO |
402 | $addPaymentHeader = FALSE; |
403 | ||
943e6943 | 404 | list($outputColumns, $metadata) = self::getExportStructureArrays($returnProperties, $processor); |
405 | ||
c8925481 | 406 | if (!empty($exportParams['merge_same_address']['temp_columns'])) { |
407 | // @todo - this is a temp fix - ideally later we don't set stuff only to unset it. | |
408 | // test exists covering this... | |
409 | foreach (array_keys($exportParams['merge_same_address']['temp_columns']) as $field) { | |
410 | $processor->setColumnAsCalculationOnly($field); | |
411 | } | |
412 | } | |
413 | ||
943e6943 | 414 | $paymentDetails = []; |
c66a5741 | 415 | if ($processor->isExportPaymentFields()) { |
6a488035 TO |
416 | // get payment related in for event and members |
417 | $paymentDetails = CRM_Contribute_BAO_Contribution::getContributionDetails($exportMode, $ids); | |
d77aba4b | 418 | //get all payment headers. |
7460c131 AS |
419 | // If we haven't selected specific payment fields, load in all the |
420 | // payment headers. | |
c66a5741 | 421 | if (!$processor->isExportSpecifiedPaymentFields()) { |
d77aba4b AS |
422 | if (!empty($paymentDetails)) { |
423 | $addPaymentHeader = TRUE; | |
943e6943 | 424 | foreach (array_keys($processor->getPaymentHeaders()) as $paymentField) { |
425 | $processor->addOutputSpecification($paymentField); | |
426 | } | |
d77aba4b | 427 | } |
6a488035 | 428 | } |
6a488035 TO |
429 | } |
430 | ||
be2fb01f | 431 | $componentDetails = []; |
6a488035 TO |
432 | |
433 | $rowCount = self::EXPORT_ROW_COUNT; | |
434 | $offset = 0; | |
435 | // we write to temp table often to avoid using too much memory | |
436 | $tempRowCount = 100; | |
437 | ||
438 | $count = -1; | |
439 | ||
28254dcb | 440 | $headerRows = $processor->getHeaderRows(); |
2cd3d767 | 441 | $sqlColumns = $processor->getSQLColumns(); |
0b94b406 | 442 | $exportTempTable = self::createTempTable($sqlColumns); |
24c7dffa | 443 | $limitReached = FALSE; |
0b94b406 | 444 | |
24c7dffa | 445 | while (!$limitReached) { |
6a488035 | 446 | $limitQuery = "{$queryString} LIMIT {$offset}, {$rowCount}"; |
6f5824c1 | 447 | CRM_Core_DAO::disableFullGroupByMode(); |
bab4f15e | 448 | $iterationDAO = CRM_Core_DAO::executeQuery($limitQuery); |
6f5824c1 | 449 | CRM_Core_DAO::reenableFullGroupByMode(); |
24c7dffa | 450 | // If this is less than our limit by the end of the iteration we do not need to run the query again to |
451 | // check if some remain. | |
452 | $rowsThisIteration = 0; | |
6a488035 | 453 | |
bab4f15e | 454 | while ($iterationDAO->fetch()) { |
6a488035 | 455 | $count++; |
24c7dffa | 456 | $rowsThisIteration++; |
6e2de55d | 457 | $row = $processor->buildRow($query, $iterationDAO, $outputColumns, $metadata, $paymentDetails, $addPaymentHeader, $paymentTableId); |
136f69a8 | 458 | if ($row === FALSE) { |
459 | continue; | |
460 | } | |
6a488035 TO |
461 | |
462 | // add component info | |
463 | // write the row to a file | |
464 | $componentDetails[] = $row; | |
465 | ||
466 | // output every $tempRowCount rows | |
467 | if ($count % $tempRowCount == 0) { | |
468 | self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); | |
be2fb01f | 469 | $componentDetails = []; |
6a488035 TO |
470 | } |
471 | } | |
24c7dffa | 472 | if ($rowsThisIteration < self::EXPORT_ROW_COUNT) { |
473 | $limitReached = TRUE; | |
474 | } | |
6a488035 TO |
475 | $offset += $rowCount; |
476 | } | |
477 | ||
478 | if ($exportTempTable) { | |
479 | self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); | |
480 | ||
481 | // do merge same address and merge same household processing | |
482 | if ($mergeSameAddress) { | |
de6ff509 | 483 | self::mergeSameAddress($exportTempTable, $sqlColumns, $exportParams); |
6a488035 TO |
484 | } |
485 | ||
6a488035 | 486 | // call export hook |
a09323e9 | 487 | CRM_Utils_Hook::export($exportTempTable, $headerRows, $sqlColumns, $exportMode, $componentTable, $ids); |
6a488035 | 488 | |
ef51caa8 | 489 | // In order to be able to write a unit test against this function we need to suppress |
490 | // the csv writing. In future hopefully the csv writing & the main processing will be in separate functions. | |
491 | if (empty($exportParams['suppress_csv_for_testing'])) { | |
70107611 | 492 | self::writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $processor); |
ef51caa8 | 493 | } |
994a070c | 494 | else { |
b7db6051 | 495 | // return tableName sqlColumns headerRows in test context |
be2fb01f | 496 | return [$exportTempTable, $sqlColumns, $headerRows, $processor]; |
994a070c | 497 | } |
6a488035 TO |
498 | |
499 | // delete the export temp table and component table | |
500 | $sql = "DROP TABLE IF EXISTS {$exportTempTable}"; | |
501 | CRM_Core_DAO::executeQuery($sql); | |
2f68ef20 | 502 | CRM_Core_DAO::reenableFullGroupByMode(); |
994a070c | 503 | CRM_Utils_System::civiExit(); |
6a488035 TO |
504 | } |
505 | else { | |
2f68ef20 | 506 | CRM_Core_DAO::reenableFullGroupByMode(); |
c7224305 | 507 | throw new CRM_Core_Exception(ts('No records to export')); |
6a488035 TO |
508 | } |
509 | } | |
510 | ||
6a488035 | 511 | /** |
100fef9d | 512 | * Handle import error file creation. |
6a488035 | 513 | */ |
00be9182 | 514 | public static function invoke() { |
a3d827a7 CW |
515 | $type = CRM_Utils_Request::retrieve('type', 'Positive'); |
516 | $parserName = CRM_Utils_Request::retrieve('parser', 'String'); | |
6a488035 TO |
517 | if (empty($parserName) || empty($type)) { |
518 | return; | |
519 | } | |
520 | ||
521 | // clean and ensure parserName is a valid string | |
522 | $parserName = CRM_Utils_String::munge($parserName); | |
523 | $parserClass = explode('_', $parserName); | |
524 | ||
525 | // make sure parserClass is in the CRM namespace and | |
526 | // at least 3 levels deep | |
527 | if ($parserClass[0] == 'CRM' && | |
528 | count($parserClass) >= 3 | |
529 | ) { | |
d3e86119 | 530 | require_once str_replace('_', DIRECTORY_SEPARATOR, $parserName) . ".php"; |
6a488035 TO |
531 | // ensure the functions exists |
532 | if (method_exists($parserName, 'errorFileName') && | |
533 | method_exists($parserName, 'saveFileName') | |
534 | ) { | |
535 | $errorFileName = $parserName::errorFileName($type); | |
536 | $saveFileName = $parserName::saveFileName($type); | |
537 | if (!empty($errorFileName) && !empty($saveFileName)) { | |
d42a224c CW |
538 | CRM_Utils_System::setHttpHeader('Cache-Control', 'must-revalidate, post-check=0, pre-check=0'); |
539 | CRM_Utils_System::setHttpHeader('Content-Description', 'File Transfer'); | |
540 | CRM_Utils_System::setHttpHeader('Content-Type', 'text/csv'); | |
541 | CRM_Utils_System::setHttpHeader('Content-Length', filesize($errorFileName)); | |
542 | CRM_Utils_System::setHttpHeader('Content-Disposition', 'attachment; filename=' . $saveFileName); | |
6a488035 TO |
543 | |
544 | readfile($errorFileName); | |
545 | } | |
546 | } | |
547 | } | |
548 | CRM_Utils_System::civiExit(); | |
549 | } | |
550 | ||
e0ef6999 EM |
551 | /** |
552 | * @param $customSearchClass | |
553 | * @param $formValues | |
554 | * @param $order | |
555 | */ | |
00be9182 | 556 | public static function exportCustom($customSearchClass, $formValues, $order) { |
6a488035 TO |
557 | $ext = CRM_Extension_System::singleton()->getMapper(); |
558 | if (!$ext->isExtensionClass($customSearchClass)) { | |
d3e86119 | 559 | require_once str_replace('_', DIRECTORY_SEPARATOR, $customSearchClass) . '.php'; |
6a488035 TO |
560 | } |
561 | else { | |
d3e86119 | 562 | require_once $ext->classToPath($customSearchClass); |
6a488035 TO |
563 | } |
564 | $search = new $customSearchClass($formValues); | |
565 | ||
566 | $includeContactIDs = FALSE; | |
567 | if ($formValues['radio_ts'] == 'ts_sel') { | |
568 | $includeContactIDs = TRUE; | |
569 | } | |
570 | ||
571 | $sql = $search->all(0, 0, $order, $includeContactIDs); | |
572 | ||
573 | $columns = $search->columns(); | |
574 | ||
575 | $header = array_keys($columns); | |
576 | $fields = array_values($columns); | |
577 | ||
be2fb01f | 578 | $rows = []; |
97f6897c | 579 | $dao = CRM_Core_DAO::executeQuery($sql); |
6a488035 TO |
580 | $alterRow = FALSE; |
581 | if (method_exists($search, 'alterRow')) { | |
582 | $alterRow = TRUE; | |
583 | } | |
584 | while ($dao->fetch()) { | |
be2fb01f | 585 | $row = []; |
6a488035 TO |
586 | |
587 | foreach ($fields as $field) { | |
37990ecd JV |
588 | $unqualified_field = CRM_Utils_Array::First(array_slice(explode('.', $field), -1)); |
589 | $row[$field] = $dao->$unqualified_field; | |
6a488035 TO |
590 | } |
591 | if ($alterRow) { | |
592 | $search->alterRow($row); | |
593 | } | |
594 | $rows[] = $row; | |
595 | } | |
596 | ||
620eae15 | 597 | CRM_Core_Report_Excel::writeCSVFile(ts('CiviCRM Contact Search'), $header, $rows); |
6a488035 TO |
598 | CRM_Utils_System::civiExit(); |
599 | } | |
600 | ||
e0ef6999 | 601 | /** |
100fef9d | 602 | * @param string $tableName |
e0ef6999 EM |
603 | * @param $details |
604 | * @param $sqlColumns | |
605 | */ | |
610f72e1 | 606 | public static function writeDetailsToTable($tableName, $details, $sqlColumns) { |
6a488035 TO |
607 | if (empty($details)) { |
608 | return; | |
609 | } | |
610 | ||
611 | $sql = " | |
612 | SELECT max(id) | |
613 | FROM $tableName | |
614 | "; | |
615 | ||
616 | $id = CRM_Core_DAO::singleValueQuery($sql); | |
617 | if (!$id) { | |
618 | $id = 0; | |
619 | } | |
620 | ||
be2fb01f | 621 | $sqlClause = []; |
6a488035 | 622 | |
610f72e1 | 623 | foreach ($details as $row) { |
6a488035 | 624 | $id++; |
be2fb01f | 625 | $valueString = [$id]; |
610f72e1 | 626 | foreach ($row as $value) { |
6a488035 TO |
627 | if (empty($value)) { |
628 | $valueString[] = "''"; | |
629 | } | |
630 | else { | |
631 | $valueString[] = "'" . CRM_Core_DAO::escapeString($value) . "'"; | |
632 | } | |
633 | } | |
634 | $sqlClause[] = '(' . implode(',', $valueString) . ')'; | |
635 | } | |
dfab581c PN |
636 | $sqlColumns = array_merge(['id' => 1], $sqlColumns); |
637 | $sqlColumnString = '(' . implode(',', array_keys($sqlColumns)) . ')'; | |
6a488035 TO |
638 | |
639 | $sqlValueString = implode(",\n", $sqlClause); | |
640 | ||
641 | $sql = " | |
642 | INSERT INTO $tableName $sqlColumnString | |
643 | VALUES $sqlValueString | |
644 | "; | |
6a488035 TO |
645 | CRM_Core_DAO::executeQuery($sql); |
646 | } | |
647 | ||
e0ef6999 EM |
648 | /** |
649 | * @param $sqlColumns | |
650 | * | |
651 | * @return string | |
652 | */ | |
610f72e1 | 653 | public static function createTempTable($sqlColumns) { |
6a488035 | 654 | //creating a temporary table for the search result that need be exported |
0033aaaf | 655 | $exportTempTable = CRM_Utils_SQL_TempTable::build()->setDurable()->setCategory('export'); |
6a488035 TO |
656 | |
657 | // also create the sql table | |
4089dea0 | 658 | $exportTempTable->drop(); |
6a488035 | 659 | |
dfab581c PN |
660 | $sql = " id int unsigned NOT NULL AUTO_INCREMENT, "; |
661 | if (!empty($sqlColumns)) { | |
662 | $sql .= implode(",\n", array_values($sqlColumns)) . ','; | |
663 | } | |
664 | ||
665 | $sql .= "\n PRIMARY KEY ( id )"; | |
6a488035 | 666 | |
6a488035 | 667 | // add indexes for street_address and household_name if present |
be2fb01f | 668 | $addIndices = [ |
6a488035 TO |
669 | 'street_address', |
670 | 'household_name', | |
671 | 'civicrm_primary_id', | |
be2fb01f | 672 | ]; |
6a488035 TO |
673 | |
674 | foreach ($addIndices as $index) { | |
675 | if (isset($sqlColumns[$index])) { | |
676 | $sql .= ", | |
677 | INDEX index_{$index}( $index ) | |
678 | "; | |
679 | } | |
680 | } | |
681 | ||
4089dea0 | 682 | $exportTempTable->createWithColumns($sql); |
683 | return $exportTempTable->getName(); | |
6a488035 TO |
684 | } |
685 | ||
e0ef6999 | 686 | /** |
100fef9d | 687 | * @param string $tableName |
e0ef6999 | 688 | * @param $sqlColumns |
100fef9d | 689 | * @param array $exportParams |
e0ef6999 | 690 | */ |
de6ff509 | 691 | public static function mergeSameAddress($tableName, &$sqlColumns, $exportParams) { |
6a488035 TO |
692 | // check if any records are present based on if they have used shared address feature, |
693 | // and not based on if city / state .. matches. | |
694 | $sql = " | |
695 | SELECT r1.id as copy_id, | |
696 | r1.civicrm_primary_id as copy_contact_id, | |
697 | r1.addressee as copy_addressee, | |
698 | r1.addressee_id as copy_addressee_id, | |
699 | r1.postal_greeting as copy_postal_greeting, | |
700 | r1.postal_greeting_id as copy_postal_greeting_id, | |
701 | r2.id as master_id, | |
702 | r2.civicrm_primary_id as master_contact_id, | |
703 | r2.postal_greeting as master_postal_greeting, | |
704 | r2.postal_greeting_id as master_postal_greeting_id, | |
705 | r2.addressee as master_addressee, | |
706 | r2.addressee_id as master_addressee_id | |
707 | FROM $tableName r1 | |
708 | INNER JOIN civicrm_address adr ON r1.master_id = adr.id | |
709 | INNER JOIN $tableName r2 ON adr.contact_id = r2.civicrm_primary_id | |
710 | ORDER BY r1.id"; | |
711 | $linkedMerge = self::_buildMasterCopyArray($sql, $exportParams, TRUE); | |
712 | ||
713 | // find all the records that have the same street address BUT not in a household | |
714 | // require match on city and state as well | |
715 | $sql = " | |
716 | SELECT r1.id as master_id, | |
717 | r1.civicrm_primary_id as master_contact_id, | |
718 | r1.postal_greeting as master_postal_greeting, | |
719 | r1.postal_greeting_id as master_postal_greeting_id, | |
720 | r1.addressee as master_addressee, | |
721 | r1.addressee_id as master_addressee_id, | |
722 | r2.id as copy_id, | |
723 | r2.civicrm_primary_id as copy_contact_id, | |
724 | r2.postal_greeting as copy_postal_greeting, | |
725 | r2.postal_greeting_id as copy_postal_greeting_id, | |
726 | r2.addressee as copy_addressee, | |
727 | r2.addressee_id as copy_addressee_id | |
728 | FROM $tableName r1 | |
729 | LEFT JOIN $tableName r2 ON ( r1.street_address = r2.street_address AND | |
730 | r1.city = r2.city AND | |
731 | r1.state_province_id = r2.state_province_id ) | |
732 | WHERE ( r1.household_name IS NULL OR r1.household_name = '' ) | |
733 | AND ( r2.household_name IS NULL OR r2.household_name = '' ) | |
734 | AND ( r1.street_address != '' ) | |
735 | AND r2.id > r1.id | |
736 | ORDER BY r1.id | |
737 | "; | |
738 | $merge = self::_buildMasterCopyArray($sql, $exportParams); | |
739 | ||
740 | // unset ids from $merge already present in $linkedMerge | |
741 | foreach ($linkedMerge as $masterID => $values) { | |
be2fb01f | 742 | $keys = [$masterID]; |
6a488035 TO |
743 | $keys = array_merge($keys, array_keys($values['copy'])); |
744 | foreach ($merge as $mid => $vals) { | |
745 | if (in_array($mid, $keys)) { | |
746 | unset($merge[$mid]); | |
747 | } | |
748 | else { | |
749 | foreach ($values['copy'] as $copyId) { | |
750 | if (in_array($copyId, $keys)) { | |
751 | unset($merge[$mid]['copy'][$copyId]); | |
752 | } | |
753 | } | |
754 | } | |
755 | } | |
756 | } | |
757 | $merge = $merge + $linkedMerge; | |
758 | ||
759 | foreach ($merge as $masterID => $values) { | |
760 | $sql = " | |
761 | UPDATE $tableName | |
762 | SET addressee = %1, postal_greeting = %2, email_greeting = %3 | |
763 | WHERE id = %4 | |
764 | "; | |
be2fb01f CW |
765 | $params = [ |
766 | 1 => [$values['addressee'], 'String'], | |
767 | 2 => [$values['postalGreeting'], 'String'], | |
768 | 3 => [$values['emailGreeting'], 'String'], | |
769 | 4 => [$masterID, 'Integer'], | |
770 | ]; | |
6a488035 TO |
771 | CRM_Core_DAO::executeQuery($sql, $params); |
772 | ||
773 | // delete all copies | |
97f6897c | 774 | $deleteIDs = array_keys($values['copy']); |
6a488035 | 775 | $deleteIDString = implode(',', $deleteIDs); |
97f6897c | 776 | $sql = " |
6a488035 TO |
777 | DELETE FROM $tableName |
778 | WHERE id IN ( $deleteIDString ) | |
779 | "; | |
780 | CRM_Core_DAO::executeQuery($sql); | |
781 | } | |
782 | ||
783 | // unset temporary columns that were added for postal mailing format | |
c8925481 | 784 | // @todo - this part is pretty close to ready to be removed.... |
6a488035 TO |
785 | if (!empty($exportParams['merge_same_address']['temp_columns'])) { |
786 | $unsetKeys = array_keys($sqlColumns); | |
787 | foreach ($unsetKeys as $headerKey => $sqlColKey) { | |
788 | if (array_key_exists($sqlColKey, $exportParams['merge_same_address']['temp_columns'])) { | |
de6ff509 | 789 | unset($sqlColumns[$sqlColKey]); |
6a488035 TO |
790 | } |
791 | } | |
792 | } | |
793 | } | |
794 | ||
e0ef6999 | 795 | /** |
100fef9d CW |
796 | * @param int $contactId |
797 | * @param array $exportParams | |
e0ef6999 EM |
798 | * |
799 | * @return array | |
800 | */ | |
00be9182 | 801 | public static function _replaceMergeTokens($contactId, $exportParams) { |
be2fb01f | 802 | $greetings = []; |
6a488035 TO |
803 | $contact = NULL; |
804 | ||
be2fb01f | 805 | $greetingFields = [ |
6a488035 TO |
806 | 'postal_greeting', |
807 | 'addressee', | |
be2fb01f | 808 | ]; |
6a488035 | 809 | foreach ($greetingFields as $greeting) { |
a7488080 | 810 | if (!empty($exportParams[$greeting])) { |
6a488035 TO |
811 | $greetingLabel = $exportParams[$greeting]; |
812 | if (empty($contact)) { | |
be2fb01f | 813 | $values = [ |
6a488035 TO |
814 | 'id' => $contactId, |
815 | 'version' => 3, | |
be2fb01f | 816 | ]; |
6a488035 TO |
817 | $contact = civicrm_api('contact', 'get', $values); |
818 | ||
a7488080 | 819 | if (!empty($contact['is_error'])) { |
6a488035 TO |
820 | return $greetings; |
821 | } | |
822 | $contact = $contact['values'][$contact['id']]; | |
823 | } | |
824 | ||
be2fb01f | 825 | $tokens = ['contact' => $greetingLabel]; |
6a488035 TO |
826 | $greetings[$greeting] = CRM_Utils_Token::replaceContactTokens($greetingLabel, $contact, NULL, $tokens); |
827 | } | |
828 | } | |
829 | return $greetings; | |
830 | } | |
831 | ||
832 | /** | |
833 | * The function unsets static part of the string, if token is the dynamic part. | |
54957108 | 834 | * |
6a488035 TO |
835 | * Example: 'Hello {contact.first_name}' => converted to => '{contact.first_name}' |
836 | * i.e 'Hello Alan' => converted to => 'Alan' | |
54957108 | 837 | * |
838 | * @param string $parsedString | |
839 | * @param string $defaultGreeting | |
840 | * @param bool $addressMergeGreetings | |
841 | * @param string $greetingType | |
842 | * | |
843 | * @return mixed | |
6a488035 | 844 | */ |
317fceb4 | 845 | public static function _trimNonTokens( |
97f6897c | 846 | &$parsedString, $defaultGreeting, |
353ffa53 | 847 | $addressMergeGreetings, $greetingType = 'postal_greeting' |
6a488035 | 848 | ) { |
a7488080 | 849 | if (!empty($addressMergeGreetings[$greetingType])) { |
6a488035 TO |
850 | $greetingLabel = $addressMergeGreetings[$greetingType]; |
851 | } | |
852 | $greetingLabel = empty($greetingLabel) ? $defaultGreeting : $greetingLabel; | |
853 | ||
854 | $stringsToBeReplaced = preg_replace('/(\{[a-zA-Z._ ]+\})/', ';;', $greetingLabel); | |
855 | $stringsToBeReplaced = explode(';;', $stringsToBeReplaced); | |
856 | foreach ($stringsToBeReplaced as $key => $string) { | |
857 | // to keep one space | |
858 | $stringsToBeReplaced[$key] = ltrim($string); | |
859 | } | |
860 | $parsedString = str_replace($stringsToBeReplaced, "", $parsedString); | |
861 | ||
862 | return $parsedString; | |
863 | } | |
864 | ||
e0ef6999 EM |
865 | /** |
866 | * @param $sql | |
100fef9d | 867 | * @param array $exportParams |
e0ef6999 EM |
868 | * @param bool $sharedAddress |
869 | * | |
870 | * @return array | |
871 | */ | |
00be9182 | 872 | public static function _buildMasterCopyArray($sql, $exportParams, $sharedAddress = FALSE) { |
be2fb01f | 873 | static $contactGreetingTokens = []; |
6a488035 TO |
874 | |
875 | $addresseeOptions = CRM_Core_OptionGroup::values('addressee'); | |
876 | $postalOptions = CRM_Core_OptionGroup::values('postal_greeting'); | |
877 | ||
be2fb01f | 878 | $merge = $parents = []; |
6a488035 TO |
879 | $dao = CRM_Core_DAO::executeQuery($sql); |
880 | ||
881 | while ($dao->fetch()) { | |
882 | $masterID = $dao->master_id; | |
883 | $copyID = $dao->copy_id; | |
884 | $masterPostalGreeting = $dao->master_postal_greeting; | |
885 | $masterAddressee = $dao->master_addressee; | |
886 | $copyAddressee = $dao->copy_addressee; | |
887 | ||
888 | if (!$sharedAddress) { | |
889 | if (!isset($contactGreetingTokens[$dao->master_contact_id])) { | |
890 | $contactGreetingTokens[$dao->master_contact_id] = self::_replaceMergeTokens($dao->master_contact_id, $exportParams); | |
891 | } | |
892 | $masterPostalGreeting = CRM_Utils_Array::value('postal_greeting', | |
893 | $contactGreetingTokens[$dao->master_contact_id], $dao->master_postal_greeting | |
894 | ); | |
895 | $masterAddressee = CRM_Utils_Array::value('addressee', | |
896 | $contactGreetingTokens[$dao->master_contact_id], $dao->master_addressee | |
897 | ); | |
898 | ||
899 | if (!isset($contactGreetingTokens[$dao->copy_contact_id])) { | |
900 | $contactGreetingTokens[$dao->copy_contact_id] = self::_replaceMergeTokens($dao->copy_contact_id, $exportParams); | |
901 | } | |
902 | $copyPostalGreeting = CRM_Utils_Array::value('postal_greeting', | |
903 | $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_postal_greeting | |
904 | ); | |
905 | $copyAddressee = CRM_Utils_Array::value('addressee', | |
906 | $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_addressee | |
907 | ); | |
908 | } | |
909 | ||
910 | if (!isset($merge[$masterID])) { | |
911 | // check if this is an intermediate child | |
912 | // this happens if there are 3 or more matches a,b, c | |
913 | // the above query will return a, b / a, c / b, c | |
914 | // we might be doing a bit more work, but for now its ok, unless someone | |
915 | // knows how to fix the query above | |
916 | if (isset($parents[$masterID])) { | |
917 | $masterID = $parents[$masterID]; | |
918 | } | |
919 | else { | |
be2fb01f | 920 | $merge[$masterID] = [ |
6a488035 | 921 | 'addressee' => $masterAddressee, |
be2fb01f | 922 | 'copy' => [], |
6a488035 | 923 | 'postalGreeting' => $masterPostalGreeting, |
be2fb01f | 924 | ]; |
6a488035 TO |
925 | $merge[$masterID]['emailGreeting'] = &$merge[$masterID]['postalGreeting']; |
926 | } | |
927 | } | |
928 | $parents[$copyID] = $masterID; | |
929 | ||
930 | if (!$sharedAddress && !array_key_exists($copyID, $merge[$masterID]['copy'])) { | |
931 | ||
a7488080 | 932 | if (!empty($exportParams['postal_greeting_other']) && |
6a488035 TO |
933 | count($merge[$masterID]['copy']) >= 1 |
934 | ) { | |
935 | // use static greetings specified if no of contacts > 2 | |
936 | $merge[$masterID]['postalGreeting'] = $exportParams['postal_greeting_other']; | |
937 | } | |
938 | elseif ($copyPostalGreeting) { | |
939 | self::_trimNonTokens($copyPostalGreeting, | |
940 | $postalOptions[$dao->copy_postal_greeting_id], | |
941 | $exportParams | |
942 | ); | |
943 | $merge[$masterID]['postalGreeting'] = "{$merge[$masterID]['postalGreeting']}, {$copyPostalGreeting}"; | |
944 | // if there happens to be a duplicate, remove it | |
945 | $merge[$masterID]['postalGreeting'] = str_replace(" {$copyPostalGreeting},", "", $merge[$masterID]['postalGreeting']); | |
946 | } | |
947 | ||
a7488080 | 948 | if (!empty($exportParams['addressee_other']) && |
6a488035 TO |
949 | count($merge[$masterID]['copy']) >= 1 |
950 | ) { | |
951 | // use static greetings specified if no of contacts > 2 | |
952 | $merge[$masterID]['addressee'] = $exportParams['addressee_other']; | |
953 | } | |
954 | elseif ($copyAddressee) { | |
955 | self::_trimNonTokens($copyAddressee, | |
956 | $addresseeOptions[$dao->copy_addressee_id], | |
957 | $exportParams, 'addressee' | |
958 | ); | |
959 | $merge[$masterID]['addressee'] = "{$merge[$masterID]['addressee']}, " . trim($copyAddressee); | |
960 | } | |
961 | } | |
962 | $merge[$masterID]['copy'][$copyID] = $copyAddressee; | |
963 | } | |
964 | ||
965 | return $merge; | |
966 | } | |
967 | ||
e0ef6999 EM |
968 | /** |
969 | * @param $exportTempTable | |
970 | * @param $headerRows | |
971 | * @param $sqlColumns | |
70107611 | 972 | * @param \CRM_Export_BAO_ExportProcessor $processor |
e0ef6999 | 973 | */ |
70107611 | 974 | public static function writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $processor) { |
975 | $exportMode = $processor->getExportMode(); | |
6a488035 | 976 | $writeHeader = TRUE; |
97f6897c TO |
977 | $offset = 0; |
978 | $limit = self::EXPORT_ROW_COUNT; | |
6a488035 TO |
979 | |
980 | $query = "SELECT * FROM $exportTempTable"; | |
981 | ||
982 | while (1) { | |
983 | $limitQuery = $query . " | |
984 | LIMIT $offset, $limit | |
985 | "; | |
986 | $dao = CRM_Core_DAO::executeQuery($limitQuery); | |
987 | ||
988 | if ($dao->N <= 0) { | |
989 | break; | |
990 | } | |
991 | ||
be2fb01f | 992 | $componentDetails = []; |
6a488035 | 993 | while ($dao->fetch()) { |
be2fb01f | 994 | $row = []; |
6a488035 TO |
995 | |
996 | foreach ($sqlColumns as $column => $dontCare) { | |
997 | $row[$column] = $dao->$column; | |
998 | } | |
999 | $componentDetails[] = $row; | |
1000 | } | |
29034a98 | 1001 | CRM_Core_Report_Excel::writeCSVFile($processor->getExportFileName(), |
6a488035 TO |
1002 | $headerRows, |
1003 | $componentDetails, | |
97f6897c | 1004 | NULL, |
70107611 | 1005 | $writeHeader |
1006 | ); | |
6a488035 | 1007 | |
97f6897c | 1008 | $writeHeader = FALSE; |
6a488035 TO |
1009 | $offset += $limit; |
1010 | } | |
1011 | } | |
1012 | ||
d77aba4b AS |
1013 | /** |
1014 | * Build componentPayment fields. | |
0e32ed68 | 1015 | * |
1016 | * This is no longer used by export but BAO_Mapping still calls it & we | |
1017 | * should find a generic way to handle this or move this to that class. | |
1018 | * | |
1019 | * @deprecated | |
d77aba4b | 1020 | */ |
00be9182 | 1021 | public static function componentPaymentFields() { |
d77aba4b | 1022 | static $componentPaymentFields; |
97f6897c | 1023 | if (!isset($componentPaymentFields)) { |
be2fb01f | 1024 | $componentPaymentFields = [ |
97f6897c | 1025 | 'componentPaymentField_total_amount' => ts('Total Amount'), |
d77aba4b | 1026 | 'componentPaymentField_contribution_status' => ts('Contribution Status'), |
7bc6b5bb | 1027 | 'componentPaymentField_received_date' => ts('Date Received'), |
536f0e02 | 1028 | 'componentPaymentField_payment_instrument' => ts('Payment Method'), |
97f6897c | 1029 | 'componentPaymentField_transaction_id' => ts('Transaction ID'), |
be2fb01f | 1030 | ]; |
d77aba4b AS |
1031 | } |
1032 | return $componentPaymentFields; | |
1033 | } | |
96025800 | 1034 | |
12a36993 | 1035 | /** |
1036 | * Get the various arrays that we use to structure our output. | |
1037 | * | |
1038 | * The extraction of these has been moved to a separate function for clarity and so that | |
1039 | * tests can be added - in particular on the $outputHeaders array. | |
1040 | * | |
1041 | * However it still feels a bit like something that I'm too polite to write down and this should be seen | |
1042 | * as a step on the refactoring path rather than how it should be. | |
1043 | * | |
1044 | * @param array $returnProperties | |
adabfa40 | 1045 | * @param \CRM_Export_BAO_ExportProcessor $processor |
c66a5741 | 1046 | * |
12a36993 | 1047 | * @return array |
1048 | * - outputColumns Array of columns to be exported. The values don't matter but the key must match the | |
1049 | * alias for the field generated by BAO_Query object. | |
1050 | * - headerRows Array of the column header strings to put in the csv header - non-associative. | |
1051 | * - sqlColumns Array of column names for the temp table. Not too sure why outputColumns can't be used here. | |
1052 | * - metadata Array of fields with specific parameters to pass to the translate function or another hacky nasty solution | |
1053 | * I'm too embarassed to discuss here. | |
1054 | * The keys need | |
1055 | * - to match the outputColumns keys (yes, the fact we ignore the output columns values & then pass another array with values | |
1056 | * we could use does suggest further refactors. However, you future improver, do remember that every check you do | |
1057 | * in the main DAO loop is done once per row & that coule be 100,000 times.) | |
1058 | * Finally a pop quiz: We need the translate context because we use a function other than ts() - is this because | |
1059 | * - a) the function used is more efficient or | |
1060 | * - b) this code is old & outdated. Submit your answers to circular bin or better | |
1061 | * yet find a way to comment them for posterity. | |
1062 | */ | |
2a48e887 | 1063 | public static function getExportStructureArrays($returnProperties, $processor) { |
be2fb01f | 1064 | $outputColumns = $metadata = []; |
adabfa40 | 1065 | $queryFields = $processor->getQueryFields(); |
12a36993 | 1066 | foreach ($returnProperties as $key => $value) { |
3883f8fb | 1067 | if (($key != 'location' || !is_array($value)) && !$processor->isRelationshipTypeKey($key)) { |
1068 | $outputColumns[$key] = $value; | |
ae5d4caf | 1069 | $processor->addOutputSpecification($key); |
3883f8fb | 1070 | } |
1071 | elseif ($processor->isRelationshipTypeKey($key)) { | |
12a36993 | 1072 | $outputColumns[$key] = $value; |
3883f8fb | 1073 | foreach ($value as $relationField => $relationValue) { |
1074 | // below block is same as primary block (duplicate) | |
1075 | if (isset($queryFields[$relationField]['title'])) { | |
5ebd7d09 | 1076 | $processor->addOutputSpecification($relationField, $key); |
3883f8fb | 1077 | } |
1078 | elseif (is_array($relationValue) && $relationField == 'location') { | |
1079 | // fix header for location type case | |
1080 | foreach ($relationValue as $ltype => $val) { | |
1081 | foreach (array_keys($val) as $fld) { | |
1082 | $type = explode('-', $fld); | |
5ebd7d09 | 1083 | $processor->addOutputSpecification($type[0], $key, $ltype, CRM_Utils_Array::value(1, $type)); |
3883f8fb | 1084 | } |
1085 | } | |
1086 | } | |
1087 | } | |
12a36993 | 1088 | } |
1089 | else { | |
1090 | foreach ($value as $locationType => $locationFields) { | |
1091 | foreach (array_keys($locationFields) as $locationFieldName) { | |
1092 | $type = explode('-', $locationFieldName); | |
1093 | ||
1094 | $actualDBFieldName = $type[0]; | |
12a36993 | 1095 | $daoFieldName = CRM_Utils_String::munge($locationType) . '-' . $actualDBFieldName; |
1096 | ||
1097 | if (!empty($type[1])) { | |
1098 | $daoFieldName .= "-" . $type[1]; | |
12a36993 | 1099 | } |
5ebd7d09 | 1100 | $processor->addOutputSpecification($actualDBFieldName, NULL, $locationType, CRM_Utils_Array::value(1, $type)); |
1101 | $metadata[$daoFieldName] = $processor->getMetaDataForField($actualDBFieldName); | |
12a36993 | 1102 | $outputColumns[$daoFieldName] = TRUE; |
1103 | } | |
1104 | } | |
1105 | } | |
1106 | } | |
be2fb01f | 1107 | return [$outputColumns, $metadata]; |
12a36993 | 1108 | } |
1109 | ||
1860fab0 | 1110 | /** |
1111 | * Get the values of linked household contact. | |
1112 | * | |
1113 | * @param CRM_Core_DAO $relDAO | |
1114 | * @param array $value | |
1115 | * @param string $field | |
1116 | * @param array $row | |
1117 | */ | |
1118 | private static function fetchRelationshipDetails($relDAO, $value, $field, &$row) { | |
1119 | $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id'); | |
1120 | $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id'); | |
1121 | $i18n = CRM_Core_I18n::singleton(); | |
ce12a9e0 | 1122 | $field = $field . '_'; |
1123 | ||
1860fab0 | 1124 | foreach ($value as $relationField => $relationValue) { |
1125 | if (is_object($relDAO) && property_exists($relDAO, $relationField)) { | |
1126 | $fieldValue = $relDAO->$relationField; | |
1127 | if ($relationField == 'phone_type_id') { | |
1128 | $fieldValue = $phoneTypes[$relationValue]; | |
1129 | } | |
1130 | elseif ($relationField == 'provider_id') { | |
1131 | $fieldValue = CRM_Utils_Array::value($relationValue, $imProviders); | |
1132 | } | |
1133 | // CRM-13995 | |
be2fb01f | 1134 | elseif (is_object($relDAO) && in_array($relationField, [ |
7b966967 SL |
1135 | 'email_greeting', |
1136 | 'postal_greeting', | |
1137 | 'addressee', | |
1138 | ])) { | |
1860fab0 | 1139 | //special case for greeting replacement |
1140 | $fldValue = "{$relationField}_display"; | |
1141 | $fieldValue = $relDAO->$fldValue; | |
1142 | } | |
1143 | } | |
1144 | elseif (is_object($relDAO) && $relationField == 'state_province') { | |
1145 | $fieldValue = CRM_Core_PseudoConstant::stateProvince($relDAO->state_province_id); | |
1146 | } | |
1147 | elseif (is_object($relDAO) && $relationField == 'country') { | |
1148 | $fieldValue = CRM_Core_PseudoConstant::country($relDAO->country_id); | |
1149 | } | |
1150 | else { | |
1151 | $fieldValue = ''; | |
1152 | } | |
f0d58350 | 1153 | $relPrefix = $field . $relationField; |
1860fab0 | 1154 | |
1155 | if (is_object($relDAO) && $relationField == 'id') { | |
f0d58350 | 1156 | $row[$relPrefix] = $relDAO->contact_id; |
1860fab0 | 1157 | } |
1158 | elseif (is_array($relationValue) && $relationField == 'location') { | |
1159 | foreach ($relationValue as $ltype => $val) { | |
a16a432a | 1160 | // If the location name has a space in it the we need to handle that. This |
1161 | // is kinda hacky but specifically covered in the ExportTest so later efforts to | |
1162 | // improve it should be secure in the knowled it will be caught. | |
1163 | $ltype = str_replace(' ', '_', $ltype); | |
1860fab0 | 1164 | foreach (array_keys($val) as $fld) { |
1165 | $type = explode('-', $fld); | |
1166 | $fldValue = "{$ltype}-" . $type[0]; | |
1167 | if (!empty($type[1])) { | |
1168 | $fldValue .= "-" . $type[1]; | |
1169 | } | |
1170 | // CRM-3157: localise country, region (both have ‘country’ context) | |
1171 | // and state_province (‘province’ context) | |
1172 | switch (TRUE) { | |
1173 | case (!is_object($relDAO)): | |
1174 | $row[$field . '_' . $fldValue] = ''; | |
1175 | break; | |
1176 | ||
1177 | case in_array('country', $type): | |
1178 | case in_array('world_region', $type): | |
1179 | $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue, | |
be2fb01f | 1180 | ['context' => 'country'] |
1860fab0 | 1181 | ); |
1182 | break; | |
1183 | ||
1184 | case in_array('state_province', $type): | |
1185 | $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue, | |
be2fb01f | 1186 | ['context' => 'province'] |
1860fab0 | 1187 | ); |
1188 | break; | |
1189 | ||
1190 | default: | |
1191 | $row[$field . '_' . $fldValue] = $relDAO->$fldValue; | |
1192 | break; | |
1193 | } | |
1194 | } | |
1195 | } | |
1196 | } | |
1197 | elseif (isset($fieldValue) && $fieldValue != '') { | |
1198 | //check for custom data | |
1199 | if ($cfID = CRM_Core_BAO_CustomField::getKeyID($relationField)) { | |
f0d58350 | 1200 | $row[$relPrefix] = CRM_Core_BAO_CustomField::displayValue($fieldValue, $cfID); |
1860fab0 | 1201 | } |
1202 | else { | |
1203 | //normal relationship fields | |
1204 | // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context) | |
1205 | switch ($relationField) { | |
1206 | case 'country': | |
1207 | case 'world_region': | |
be2fb01f | 1208 | $row[$relPrefix] = $i18n->crm_translate($fieldValue, ['context' => 'country']); |
1860fab0 | 1209 | break; |
1210 | ||
1211 | case 'state_province': | |
be2fb01f | 1212 | $row[$relPrefix] = $i18n->crm_translate($fieldValue, ['context' => 'province']); |
1860fab0 | 1213 | break; |
1214 | ||
1215 | default: | |
f0d58350 | 1216 | $row[$relPrefix] = $fieldValue; |
1860fab0 | 1217 | break; |
1218 | } | |
1219 | } | |
1220 | } | |
1221 | else { | |
1222 | // if relation field is empty or null | |
f0d58350 | 1223 | $row[$relPrefix] = ''; |
1860fab0 | 1224 | } |
1225 | } | |
1226 | } | |
1227 | ||
814065a3 | 1228 | /** |
1229 | * Get the ids that we want to get related contact details for. | |
1230 | * | |
1231 | * @param array $ids | |
1232 | * @param int $exportMode | |
1233 | * | |
1234 | * @return array | |
1235 | */ | |
1236 | protected static function getIDsForRelatedContact($ids, $exportMode) { | |
1237 | if ($exportMode == CRM_Export_Form_Select::CONTACT_EXPORT) { | |
1238 | return $ids; | |
1239 | } | |
1240 | if ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) { | |
1241 | $relIDs = []; | |
1242 | $sourceID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_ActivityContact', 'record_type_id', 'Activity Source'); | |
1243 | $dao = CRM_Core_DAO::executeQuery(" | |
1244 | SELECT contact_id FROM civicrm_activity_contact | |
1245 | WHERE activity_id IN ( " . implode(',', $ids) . ") AND | |
1246 | record_type_id = {$sourceID} | |
1247 | "); | |
1248 | ||
1249 | while ($dao->fetch()) { | |
1250 | $relIDs[] = $dao->contact_id; | |
1251 | } | |
1252 | return $relIDs; | |
1253 | } | |
1254 | $component = self::exportComponent($exportMode); | |
1255 | ||
1256 | if ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) { | |
1257 | return CRM_Case_BAO_Case::retrieveContactIdsByCaseId($ids); | |
1258 | } | |
1259 | else { | |
1260 | return CRM_Core_DAO::getContactIDsFromComponent($ids, $component); | |
1261 | } | |
1262 | } | |
1263 | ||
44f8f95c | 1264 | /** |
1265 | * @param $selectAll | |
1266 | * @param $ids | |
439355f5 | 1267 | * @param \CRM_Export_BAO_ExportProcessor $processor |
44f8f95c | 1268 | * @param $componentTable |
44f8f95c | 1269 | */ |
ce12a9e0 | 1270 | protected static function buildRelatedContactArray($selectAll, $ids, $processor, $componentTable) { |
be2fb01f | 1271 | $allRelContactArray = $relationQuery = []; |
439355f5 | 1272 | $queryMode = $processor->getQueryMode(); |
1273 | $exportMode = $processor->getExportMode(); | |
44f8f95c | 1274 | |
ce12a9e0 | 1275 | foreach ($processor->getRelationshipReturnProperties() as $relationshipKey => $relationReturnProperties) { |
be2fb01f | 1276 | $allRelContactArray[$relationshipKey] = []; |
ce12a9e0 | 1277 | // build Query for each relationship |
1278 | $relationQuery = new CRM_Contact_BAO_Query(NULL, $relationReturnProperties, | |
1279 | NULL, FALSE, FALSE, $queryMode | |
1280 | ); | |
1281 | list($relationSelect, $relationFrom, $relationWhere, $relationHaving) = $relationQuery->query(); | |
1282 | ||
1283 | list($id, $direction) = explode('_', $relationshipKey, 2); | |
1284 | // identify the relationship direction | |
1285 | $contactA = 'contact_id_a'; | |
1286 | $contactB = 'contact_id_b'; | |
1287 | if ($direction == 'b_a') { | |
1288 | $contactA = 'contact_id_b'; | |
1289 | $contactB = 'contact_id_a'; | |
1290 | } | |
1291 | $relIDs = self::getIDsForRelatedContact($ids, $exportMode); | |
1292 | ||
1293 | $relationshipJoin = $relationshipClause = ''; | |
1294 | if (!$selectAll && $componentTable) { | |
1295 | $relationshipJoin = " INNER JOIN {$componentTable} ctTable ON ctTable.contact_id = {$contactA}"; | |
1296 | } | |
1297 | elseif (!empty($relIDs)) { | |
1298 | $relID = implode(',', $relIDs); | |
1299 | $relationshipClause = " AND crel.{$contactA} IN ( {$relID} )"; | |
1300 | } | |
44f8f95c | 1301 | |
ce12a9e0 | 1302 | $relationFrom = " {$relationFrom} |
1303 | INNER JOIN civicrm_relationship crel ON crel.{$contactB} = contact_a.id AND crel.relationship_type_id = {$id} | |
1304 | {$relationshipJoin} "; | |
1305 | ||
1306 | //check for active relationship status only | |
1307 | $today = date('Ymd'); | |
1308 | $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )"; | |
1309 | $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause} {$relationActive}"; | |
6f5824c1 | 1310 | CRM_Core_DAO::disableFullGroupByMode(); |
ce12a9e0 | 1311 | $relationSelect = "{$relationSelect}, {$contactA} as refContact "; |
6f5824c1 | 1312 | $relationQueryString = "$relationSelect $relationFrom $relationWhere $relationHaving GROUP BY crel.{$contactA}"; |
ce12a9e0 | 1313 | |
1314 | $allRelContactDAO = CRM_Core_DAO::executeQuery($relationQueryString); | |
6f5824c1 | 1315 | CRM_Core_DAO::reenableFullGroupByMode(); |
1316 | ||
ce12a9e0 | 1317 | while ($allRelContactDAO->fetch()) { |
1318 | $relationQuery->convertToPseudoNames($allRelContactDAO); | |
1319 | $row = []; | |
1320 | // @todo pass processor to fetchRelationshipDetails and set fields directly within it. | |
1321 | self::fetchRelationshipDetails($allRelContactDAO, $relationReturnProperties, $relationshipKey, $row); | |
1322 | foreach (array_keys($relationReturnProperties) as $property) { | |
1323 | if ($property === 'location') { | |
1324 | // @todo - simplify location in self::fetchRelationshipDetails - remove handling here. Or just call | |
1325 | // $processor->setRelationshipValue from fetchRelationshipDetails | |
1326 | foreach ($relationReturnProperties['location'] as $locationName => $locationValues) { | |
1327 | foreach (array_keys($locationValues) as $locationValue) { | |
1328 | $key = str_replace(' ', '_', $locationName) . '-' . $locationValue; | |
1329 | $processor->setRelationshipValue($relationshipKey, $allRelContactDAO->refContact, $key, $row[$relationshipKey . '__' . $key]); | |
1330 | } | |
1331 | } | |
1332 | } | |
1333 | else { | |
1334 | $processor->setRelationshipValue($relationshipKey, $allRelContactDAO->refContact, $property, $row[$relationshipKey . '_' . $property]); | |
1335 | } | |
44f8f95c | 1336 | } |
44f8f95c | 1337 | } |
1338 | } | |
44f8f95c | 1339 | } |
1340 | ||
6a488035 | 1341 | } |