Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
7e9e8871 | 4 | | CiviCRM version 4.7 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
0f03f337 | 6 | | Copyright CiviCRM LLC (c) 2004-2017 | |
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 | |
0f03f337 | 31 | * @copyright CiviCRM LLC (c) 2004-2017 |
6a488035 TO |
32 | * $Id$ |
33 | * | |
34 | */ | |
35 | ||
36 | /** | |
748450ad | 37 | * This class contains the functions for Component export |
6a488035 TO |
38 | * |
39 | */ | |
40 | class CRM_Export_BAO_Export { | |
41 | // increase this number a lot to avoid making too many queries | |
42 | // LIMIT is not much faster than a no LIMIT query | |
43 | // CRM-7675 | |
24c7dffa | 44 | const EXPORT_ROW_COUNT = 100000; |
6a488035 | 45 | |
f34f5143 SL |
46 | /** |
47 | * Get Querymode based on ExportMode | |
48 | * | |
49 | * @param int $exportMode | |
50 | * Export mode. | |
51 | * | |
748450ad SL |
52 | * @return string $Querymode |
53 | * Query Mode | |
f34f5143 | 54 | */ |
919eddec | 55 | public static function getQueryMode($exportMode) { |
b72702ac SL |
56 | $queryMode = CRM_Contact_BAO_Query::MODE_CONTACTS; |
57 | ||
f34f5143 SL |
58 | switch ($exportMode) { |
59 | case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: | |
60 | $queryMode = CRM_Contact_BAO_Query::MODE_CONTRIBUTE; | |
61 | break; | |
62 | ||
63 | case CRM_Export_Form_Select::EVENT_EXPORT: | |
64 | $queryMode = CRM_Contact_BAO_Query::MODE_EVENT; | |
65 | break; | |
66 | ||
67 | case CRM_Export_Form_Select::MEMBER_EXPORT: | |
68 | $queryMode = CRM_Contact_BAO_Query::MODE_MEMBER; | |
69 | break; | |
70 | ||
71 | case CRM_Export_Form_Select::PLEDGE_EXPORT: | |
72 | $queryMode = CRM_Contact_BAO_Query::MODE_PLEDGE; | |
73 | break; | |
74 | ||
75 | case CRM_Export_Form_Select::CASE_EXPORT: | |
76 | $queryMode = CRM_Contact_BAO_Query::MODE_CASE; | |
77 | break; | |
78 | ||
79 | case CRM_Export_Form_Select::GRANT_EXPORT: | |
80 | $queryMode = CRM_Contact_BAO_Query::MODE_GRANT; | |
81 | break; | |
82 | ||
83 | case CRM_Export_Form_Select::ACTIVITY_EXPORT: | |
84 | $queryMode = CRM_Contact_BAO_Query::MODE_ACTIVITY; | |
85 | break; | |
86 | } | |
87 | return $queryMode; | |
88 | } | |
89 | ||
90 | /** | |
91 | * Get default return property for export based on mode | |
92 | * | |
93 | * @param int $exportMode | |
94 | * Export mode. | |
919eddec | 95 | * |
748450ad SL |
96 | * @return string $property |
97 | * Default Return property | |
f34f5143 | 98 | */ |
2e28a8b9 | 99 | public static function defaultReturnProperty($exportMode) { |
748450ad | 100 | // hack to add default return property based on export mode |
0dc29086 | 101 | $property = NULL; |
f34f5143 | 102 | if ($exportMode == CRM_Export_Form_Select::CONTRIBUTE_EXPORT) { |
919eddec | 103 | $property = 'contribution_id'; |
f34f5143 SL |
104 | } |
105 | elseif ($exportMode == CRM_Export_Form_Select::EVENT_EXPORT) { | |
919eddec | 106 | $property = 'participant_id'; |
f34f5143 SL |
107 | } |
108 | elseif ($exportMode == CRM_Export_Form_Select::MEMBER_EXPORT) { | |
919eddec | 109 | $property = 'membership_id'; |
f34f5143 SL |
110 | } |
111 | elseif ($exportMode == CRM_Export_Form_Select::PLEDGE_EXPORT) { | |
919eddec | 112 | $property = 'pledge_id'; |
f34f5143 SL |
113 | } |
114 | elseif ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) { | |
919eddec | 115 | $property = 'case_id'; |
f34f5143 SL |
116 | } |
117 | elseif ($exportMode == CRM_Export_Form_Select::GRANT_EXPORT) { | |
919eddec | 118 | $property = 'grant_id'; |
f34f5143 SL |
119 | } |
120 | elseif ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) { | |
919eddec | 121 | $property = 'activity_id'; |
f34f5143 | 122 | } |
919eddec | 123 | return $property; |
f34f5143 SL |
124 | } |
125 | ||
126 | /** | |
127 | * Get Export component | |
128 | * | |
129 | * @param int $exportMode | |
130 | * Export mode. | |
131 | * | |
748450ad SL |
132 | * @return string $component |
133 | * CiviCRM Export Component | |
f34f5143 | 134 | */ |
7bf9f91e | 135 | public static function exportComponent($exportMode) { |
f34f5143 SL |
136 | switch ($exportMode) { |
137 | case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: | |
138 | $component = 'civicrm_contribution'; | |
139 | break; | |
140 | ||
141 | case CRM_Export_Form_Select::EVENT_EXPORT: | |
142 | $component = 'civicrm_participant'; | |
143 | break; | |
144 | ||
145 | case CRM_Export_Form_Select::MEMBER_EXPORT: | |
146 | $component = 'civicrm_membership'; | |
147 | break; | |
148 | ||
149 | case CRM_Export_Form_Select::PLEDGE_EXPORT: | |
150 | $component = 'civicrm_pledge'; | |
151 | break; | |
152 | ||
153 | case CRM_Export_Form_Select::GRANT_EXPORT: | |
154 | $component = 'civicrm_grant'; | |
155 | break; | |
156 | } | |
157 | return $component; | |
158 | } | |
159 | ||
748450ad | 160 | /** |
f1ff3965 | 161 | * Get Query Group By Clause |
748450ad SL |
162 | * @param int $exportMode |
163 | * Export Mode | |
164 | * @param string $queryMode | |
165 | * Query Mode | |
166 | * @param array $returnProperties | |
167 | * Return Properties | |
0cc9927d SL |
168 | * @param object $query |
169 | * CRM_Contact_BAO_Query | |
170 | * | |
748450ad SL |
171 | * @return string $groupBy |
172 | * Group By Clause | |
173 | */ | |
0cc9927d | 174 | public static function getGroupBy($exportMode, $queryMode, $returnProperties, $query) { |
3636b520 | 175 | $groupBy = ''; |
748450ad SL |
176 | if (!empty($returnProperties['tags']) || !empty($returnProperties['groups']) || |
177 | CRM_Utils_Array::value('notes', $returnProperties) || | |
178 | // CRM-9552 | |
179 | ($queryMode & CRM_Contact_BAO_Query::MODE_CONTACTS && $query->_useGroupBy) | |
180 | ) { | |
3636b520 | 181 | $groupBy = "contact_a.id"; |
748450ad SL |
182 | } |
183 | ||
184 | switch ($exportMode) { | |
185 | case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: | |
3636b520 | 186 | $groupBy = 'civicrm_contribution.id'; |
748450ad SL |
187 | if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) { |
188 | // especial group by when soft credit columns are included | |
3636b520 | 189 | $groupBy = array('contribution_search_scredit_combined.id', 'contribution_search_scredit_combined.scredit_id'); |
748450ad SL |
190 | } |
191 | break; | |
192 | ||
193 | case CRM_Export_Form_Select::EVENT_EXPORT: | |
3636b520 | 194 | $groupBy = 'civicrm_participant.id'; |
748450ad SL |
195 | break; |
196 | ||
197 | case CRM_Export_Form_Select::MEMBER_EXPORT: | |
3636b520 | 198 | $groupBy = "civicrm_membership.id"; |
748450ad SL |
199 | break; |
200 | } | |
201 | ||
202 | if ($queryMode & CRM_Contact_BAO_Query::MODE_ACTIVITY) { | |
3636b520 | 203 | $groupBy = "civicrm_activity.id "; |
748450ad | 204 | } |
ee027e3b | 205 | |
3636b520 | 206 | if (!empty($groupBy)) { |
207 | $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($query->_select, $groupBy); | |
208 | } | |
ee027e3b | 209 | |
748450ad SL |
210 | return $groupBy; |
211 | } | |
212 | ||
213 | /** | |
214 | * Define extra properties for the export based on query mode | |
215 | * | |
216 | * @param string $queryMode | |
217 | * Query Mode | |
218 | * @return array $extraProperties | |
219 | * Extra Properties | |
220 | */ | |
221 | public static function defineExtraProperties($queryMode) { | |
222 | switch ($queryMode) { | |
223 | case CRM_Contact_BAO_Query::MODE_EVENT: | |
224 | $paymentFields = TRUE; | |
225 | $paymentTableId = 'participant_id'; | |
226 | $extraReturnProperties = array(); | |
227 | break; | |
228 | ||
229 | case CRM_Contact_BAO_Query::MODE_MEMBER: | |
230 | $paymentFields = TRUE; | |
231 | $paymentTableId = 'membership_id'; | |
232 | $extraReturnProperties = array(); | |
233 | break; | |
234 | ||
235 | case CRM_Contact_BAO_Query::MODE_PLEDGE: | |
236 | $extraReturnProperties = CRM_Pledge_BAO_Query::extraReturnProperties($queryMode); | |
237 | $paymentFields = TRUE; | |
238 | $paymentTableId = 'pledge_payment_id'; | |
239 | break; | |
240 | ||
241 | case CRM_Contact_BAO_Query::MODE_CASE: | |
242 | $extraReturnProperties = CRM_Case_BAO_Query::extraReturnProperties($queryMode); | |
243 | $paymentFields = FALSE; | |
ee027e3b | 244 | $paymentTableId = ''; |
748450ad SL |
245 | break; |
246 | ||
247 | default: | |
248 | $paymentFields = FALSE; | |
ee027e3b | 249 | $paymentTableId = ''; |
748450ad SL |
250 | $extraReturnProperties = array(); |
251 | } | |
252 | $extraProperties = array( | |
253 | 'paymentFields' => $paymentFields, | |
ee027e3b | 254 | 'paymentTableId' => $paymentTableId, |
748450ad SL |
255 | 'extraReturnProperties' => $extraReturnProperties, |
256 | ); | |
257 | return $extraProperties; | |
258 | } | |
259 | ||
6a488035 | 260 | /** |
fe482240 | 261 | * Get the list the export fields. |
6a488035 | 262 | * |
b9add4b3 TO |
263 | * @param int $selectAll |
264 | * User preference while export. | |
265 | * @param array $ids | |
266 | * Contact ids. | |
267 | * @param array $params | |
268 | * Associated array of fields. | |
269 | * @param string $order | |
270 | * Order by clause. | |
271 | * @param array $fields | |
272 | * Associated array of fields. | |
273 | * @param array $moreReturnProperties | |
274 | * Additional return fields. | |
275 | * @param int $exportMode | |
276 | * Export mode. | |
277 | * @param string $componentClause | |
278 | * Component clause. | |
279 | * @param string $componentTable | |
280 | * Component table. | |
281 | * @param bool $mergeSameAddress | |
282 | * Merge records if they have same address. | |
283 | * @param bool $mergeSameHousehold | |
284 | * Merge records if they belong to the same household. | |
6c8f6e67 EM |
285 | * |
286 | * @param array $exportParams | |
287 | * @param string $queryOperator | |
6a488035 | 288 | * |
6a488035 | 289 | */ |
317fceb4 | 290 | public static function exportComponents( |
97f6897c | 291 | $selectAll, |
6a488035 TO |
292 | $ids, |
293 | $params, | |
294 | $order = NULL, | |
295 | $fields = NULL, | |
296 | $moreReturnProperties = NULL, | |
297 | $exportMode = CRM_Export_Form_Select::CONTACT_EXPORT, | |
298 | $componentClause = NULL, | |
299 | $componentTable = NULL, | |
300 | $mergeSameAddress = FALSE, | |
301 | $mergeSameHousehold = FALSE, | |
302 | $exportParams = array(), | |
303 | $queryOperator = 'AND' | |
304 | ) { | |
ef51caa8 | 305 | |
12a36993 | 306 | $returnProperties = array(); |
36f38fe7 | 307 | $paymentFields = $selectedPaymentFields = FALSE; |
6a488035 | 308 | |
b4f964d9 | 309 | $phoneTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Phone', 'phone_type_id'); |
12a36993 | 310 | // Warning - this imProviders var is used in a somewhat fragile way - don't rename it |
311 | // without manually testing the export of IM provider still works. | |
e7e657f0 | 312 | $imProviders = CRM_Core_PseudoConstant::get('CRM_Core_DAO_IM', 'provider_id'); |
6a488035 TO |
313 | $contactRelationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType( |
314 | NULL, | |
315 | NULL, | |
316 | NULL, | |
317 | NULL, | |
318 | TRUE, | |
545285b8 | 319 | 'name', |
6a488035 TO |
320 | FALSE |
321 | ); | |
6a488035 | 322 | |
f34f5143 | 323 | $queryMode = self::getQueryMode($exportMode); |
919eddec | 324 | |
6a488035 TO |
325 | if ($fields) { |
326 | //construct return properties | |
b2b0530a | 327 | $locationTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id'); |
6a488035 TO |
328 | $locationTypeFields = array( |
329 | 'street_address', | |
330 | 'supplemental_address_1', | |
331 | 'supplemental_address_2', | |
332 | 'city', | |
333 | 'postal_code', | |
334 | 'postal_code_suffix', | |
335 | 'geo_code_1', | |
336 | 'geo_code_2', | |
337 | 'state_province', | |
338 | 'country', | |
339 | 'phone', | |
340 | 'email', | |
341 | 'im', | |
342 | ); | |
343 | ||
344 | foreach ($fields as $key => $value) { | |
5862825b | 345 | $phoneTypeId = $imProviderId = $relationField = NULL; |
6a488035 TO |
346 | $relationshipTypes = $fieldName = CRM_Utils_Array::value(1, $value); |
347 | if (!$fieldName) { | |
348 | continue; | |
349 | } | |
b44e3f84 | 350 | // get phoneType id and IM service provider id separately |
6a488035 TO |
351 | if ($fieldName == 'phone') { |
352 | $phoneTypeId = CRM_Utils_Array::value(3, $value); | |
353 | } | |
354 | elseif ($fieldName == 'im') { | |
355 | $imProviderId = CRM_Utils_Array::value(3, $value); | |
356 | } | |
6a488035 TO |
357 | |
358 | if (array_key_exists($relationshipTypes, $contactRelationshipTypes)) { | |
a7488080 | 359 | if (!empty($value[2])) { |
6a488035 TO |
360 | $relationField = CRM_Utils_Array::value(2, $value); |
361 | if (trim(CRM_Utils_Array::value(3, $value))) { | |
362 | $relLocTypeId = CRM_Utils_Array::value(3, $value); | |
363 | } | |
364 | else { | |
365 | $relLocTypeId = 'Primary'; | |
366 | } | |
367 | ||
368 | if ($relationField == 'phone') { | |
369 | $relPhoneTypeId = CRM_Utils_Array::value(4, $value); | |
370 | } | |
371 | elseif ($relationField == 'im') { | |
372 | $relIMProviderId = CRM_Utils_Array::value(4, $value); | |
373 | } | |
374 | } | |
a7488080 | 375 | elseif (!empty($value[4])) { |
6a488035 TO |
376 | $relationField = CRM_Utils_Array::value(4, $value); |
377 | $relLocTypeId = CRM_Utils_Array::value(5, $value); | |
378 | if ($relationField == 'phone') { | |
379 | $relPhoneTypeId = CRM_Utils_Array::value(6, $value); | |
380 | } | |
381 | elseif ($relationField == 'im') { | |
382 | $relIMProviderId = CRM_Utils_Array::value(6, $value); | |
383 | } | |
384 | } | |
385 | } | |
386 | ||
387 | $contactType = CRM_Utils_Array::value(0, $value); | |
388 | $locTypeId = CRM_Utils_Array::value(2, $value); | |
389 | ||
390 | if ($relationField) { | |
391 | if (in_array($relationField, $locationTypeFields) && is_numeric($relLocTypeId)) { | |
392 | if ($relPhoneTypeId) { | |
393 | $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]]['phone-' . $relPhoneTypeId] = 1; | |
394 | } | |
395 | elseif ($relIMProviderId) { | |
396 | $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]]['im-' . $relIMProviderId] = 1; | |
397 | } | |
398 | else { | |
399 | $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]][$relationField] = 1; | |
400 | } | |
401 | $relPhoneTypeId = $relIMProviderId = NULL; | |
402 | } | |
403 | else { | |
404 | $returnProperties[$relationshipTypes][$relationField] = 1; | |
405 | } | |
406 | } | |
407 | elseif (is_numeric($locTypeId)) { | |
408 | if ($phoneTypeId) { | |
409 | $returnProperties['location'][$locationTypes[$locTypeId]]['phone-' . $phoneTypeId] = 1; | |
410 | } | |
411 | elseif ($imProviderId) { | |
412 | $returnProperties['location'][$locationTypes[$locTypeId]]['im-' . $imProviderId] = 1; | |
413 | } | |
414 | else { | |
415 | $returnProperties['location'][$locationTypes[$locTypeId]][$fieldName] = 1; | |
416 | } | |
417 | } | |
418 | else { | |
419 | //hack to fix component fields | |
8b8fa582 | 420 | //revert mix of event_id and title |
6a488035 | 421 | if ($fieldName == 'event_id') { |
8b8fa582 | 422 | $returnProperties['event_id'] = 1; |
6a488035 | 423 | } |
4c9b6178 | 424 | elseif ( |
545285b8 | 425 | $exportMode == CRM_Export_Form_Select::EVENT_EXPORT && |
97f6897c | 426 | array_key_exists($fieldName, self::componentPaymentFields()) |
545285b8 | 427 | ) { |
97f6897c TO |
428 | $selectedPaymentFields = TRUE; |
429 | $paymentTableId = 'participant_id'; | |
430 | $returnProperties[$fieldName] = 1; | |
d77aba4b | 431 | } |
6a488035 TO |
432 | else { |
433 | $returnProperties[$fieldName] = 1; | |
6a488035 TO |
434 | } |
435 | } | |
436 | } | |
f06b9233 | 437 | $defaultExportMode = self::defaultReturnProperty($exportMode); |
92a99d2b | 438 | if ($defaultExportMode) { |
f06b9233 | 439 | $returnProperties[$defaultExportMode] = 1; |
0dc29086 | 440 | } |
919eddec | 441 | } |
6a488035 TO |
442 | else { |
443 | $primary = TRUE; | |
444 | $fields = CRM_Contact_BAO_Contact::exportableFields('All', TRUE, TRUE); | |
445 | foreach ($fields as $key => $var) { | |
446 | if ($key && (substr($key, 0, 6) != 'custom')) { | |
447 | //for CRM=952 | |
448 | $returnProperties[$key] = 1; | |
449 | } | |
450 | } | |
451 | ||
452 | if ($primary) { | |
453 | $returnProperties['location_type'] = 1; | |
454 | $returnProperties['im_provider'] = 1; | |
455 | $returnProperties['phone_type_id'] = 1; | |
456 | $returnProperties['provider_id'] = 1; | |
457 | $returnProperties['current_employer'] = 1; | |
458 | } | |
459 | ||
f1ff3965 | 460 | $extraProperties = self::defineExtraProperties($queryMode); |
748450ad SL |
461 | $paymentFields = $extraProperties['paymentFields']; |
462 | $extraReturnProperties = $extraProperties['extraReturnProperties']; | |
ee027e3b | 463 | $paymentTableId = $extraProperties['paymentTableId']; |
6a488035 TO |
464 | |
465 | if ($queryMode != CRM_Contact_BAO_Query::MODE_CONTACTS) { | |
466 | $componentReturnProperties = CRM_Contact_BAO_Query::defaultReturnProperties($queryMode); | |
81ec6180 | 467 | if ($queryMode == CRM_Contact_BAO_Query::MODE_CONTRIBUTE) { |
4f3846df | 468 | // soft credit columns are not automatically populated, because contribution search doesn't require them by default |
317fceb4 | 469 | $componentReturnProperties = array_merge( |
6c8f6e67 | 470 | $componentReturnProperties, |
526bdd5e | 471 | CRM_Contribute_BAO_Query::softCreditReturnProperties(TRUE)); |
81ec6180 | 472 | } |
6a488035 TO |
473 | $returnProperties = array_merge($returnProperties, $componentReturnProperties); |
474 | ||
475 | if (!empty($extraReturnProperties)) { | |
476 | $returnProperties = array_merge($returnProperties, $extraReturnProperties); | |
477 | } | |
478 | ||
479 | // unset non exportable fields for components | |
480 | $nonExpoFields = array( | |
97f6897c TO |
481 | 'groups', |
482 | 'tags', | |
483 | 'notes', | |
484 | 'contribution_status_id', | |
485 | 'pledge_status_id', | |
486 | 'pledge_payment_status_id', | |
6a488035 TO |
487 | ); |
488 | foreach ($nonExpoFields as $value) { | |
489 | unset($returnProperties[$value]); | |
490 | } | |
491 | } | |
492 | } | |
493 | ||
494 | if ($mergeSameAddress) { | |
495 | //make sure the addressee fields are selected | |
496 | //while using merge same address feature | |
497 | $returnProperties['addressee'] = 1; | |
498 | $returnProperties['postal_greeting'] = 1; | |
499 | $returnProperties['email_greeting'] = 1; | |
500 | $returnProperties['street_name'] = 1; | |
501 | $returnProperties['household_name'] = 1; | |
502 | $returnProperties['street_address'] = 1; | |
503 | $returnProperties['city'] = 1; | |
504 | $returnProperties['state_province'] = 1; | |
505 | ||
506 | // some columns are required for assistance incase they are not already present | |
507 | $exportParams['merge_same_address']['temp_columns'] = array(); | |
508 | $tempColumns = array('id', 'master_id', 'state_province_id', 'postal_greeting_id', 'addressee_id'); | |
509 | foreach ($tempColumns as $column) { | |
510 | if (!array_key_exists($column, $returnProperties)) { | |
511 | $returnProperties[$column] = 1; | |
512 | $column = $column == 'id' ? 'civicrm_primary_id' : $column; | |
513 | $exportParams['merge_same_address']['temp_columns'][$column] = 1; | |
514 | } | |
515 | } | |
516 | } | |
517 | ||
8cc574cf | 518 | if (!$selectAll && $componentTable && !empty($exportParams['additional_group'])) { |
6a488035 TO |
519 | // If an Additional Group is selected, then all contacts in that group are |
520 | // added to the export set (filtering out duplicates). | |
521 | $query = " | |
522 | 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"; | |
523 | CRM_Core_DAO::executeQuery($query); | |
524 | } | |
525 | ||
526 | if ($moreReturnProperties) { | |
527 | // fix for CRM-7066 | |
a7488080 | 528 | if (!empty($moreReturnProperties['group'])) { |
6a488035 TO |
529 | unset($moreReturnProperties['group']); |
530 | $moreReturnProperties['groups'] = 1; | |
531 | } | |
532 | $returnProperties = array_merge($returnProperties, $moreReturnProperties); | |
533 | } | |
534 | ||
535 | $exportParams['postal_mailing_export']['temp_columns'] = array(); | |
536 | if ($exportParams['exportOption'] == 2 && | |
537 | isset($exportParams['postal_mailing_export']) && | |
538 | CRM_Utils_Array::value('postal_mailing_export', $exportParams['postal_mailing_export']) == 1 | |
539 | ) { | |
540 | $postalColumns = array('is_deceased', 'do_not_mail', 'street_address', 'supplemental_address_1'); | |
541 | foreach ($postalColumns as $column) { | |
542 | if (!array_key_exists($column, $returnProperties)) { | |
543 | $returnProperties[$column] = 1; | |
544 | $exportParams['postal_mailing_export']['temp_columns'][$column] = 1; | |
545 | } | |
546 | } | |
547 | } | |
548 | ||
9e49a7b8 PJ |
549 | // rectify params to what proximity search expects if there is a value for prox_distance |
550 | // CRM-7021 | |
551 | if (!empty($params)) { | |
552 | CRM_Contact_BAO_ProximityQuery::fixInputParams($params); | |
553 | } | |
554 | ||
fdb9cf51 | 555 | $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL, |
6a488035 TO |
556 | FALSE, FALSE, $queryMode, |
557 | FALSE, TRUE, TRUE, NULL, $queryOperator | |
558 | ); | |
559 | ||
33a5a53d | 560 | //sort by state |
561 | //CRM-15301 | |
562 | $query->_sort = $order; | |
6a488035 TO |
563 | list($select, $from, $where, $having) = $query->query(); |
564 | ||
565 | if ($mergeSameHousehold == 1) { | |
0dc29086 | 566 | if (empty($returnProperties['id'])) { |
6a488035 TO |
567 | $returnProperties['id'] = 1; |
568 | } | |
569 | ||
570 | //also merge Head of Household | |
571 | $relationKeyMOH = CRM_Utils_Array::key('Household Member of', $contactRelationshipTypes); | |
572 | $relationKeyHOH = CRM_Utils_Array::key('Head of Household for', $contactRelationshipTypes); | |
573 | ||
574 | foreach ($returnProperties as $key => $value) { | |
575 | if (!array_key_exists($key, $contactRelationshipTypes)) { | |
576 | $returnProperties[$relationKeyMOH][$key] = $value; | |
577 | $returnProperties[$relationKeyHOH][$key] = $value; | |
578 | } | |
579 | } | |
580 | ||
581 | unset($returnProperties[$relationKeyMOH]['location_type']); | |
582 | unset($returnProperties[$relationKeyMOH]['im_provider']); | |
583 | unset($returnProperties[$relationKeyHOH]['location_type']); | |
584 | unset($returnProperties[$relationKeyHOH]['im_provider']); | |
585 | } | |
586 | ||
587 | $allRelContactArray = $relationQuery = array(); | |
588 | ||
589 | foreach ($contactRelationshipTypes as $rel => $dnt) { | |
590 | if ($relationReturnProperties = CRM_Utils_Array::value($rel, $returnProperties)) { | |
591 | $allRelContactArray[$rel] = array(); | |
592 | // build Query for each relationship | |
593 | $relationQuery[$rel] = new CRM_Contact_BAO_Query(NULL, $relationReturnProperties, | |
594 | NULL, FALSE, FALSE, $queryMode | |
595 | ); | |
596 | list($relationSelect, $relationFrom, $relationWhere, $relationHaving) = $relationQuery[$rel]->query(); | |
597 | ||
598 | list($id, $direction) = explode('_', $rel, 2); | |
599 | // identify the relationship direction | |
600 | $contactA = 'contact_id_a'; | |
601 | $contactB = 'contact_id_b'; | |
602 | if ($direction == 'b_a') { | |
603 | $contactA = 'contact_id_b'; | |
604 | $contactB = 'contact_id_a'; | |
605 | } | |
606 | if ($exportMode == CRM_Export_Form_Select::CONTACT_EXPORT) { | |
607 | $relIDs = $ids; | |
608 | } | |
609 | elseif ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) { | |
ad7695c2 | 610 | $sourceID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_ActivityContact', 'record_type_id', 'Activity Source'); |
b4eac1e9 | 611 | $query = "SELECT contact_id FROM civicrm_activity_contact |
612 | WHERE activity_id IN ( " . implode(',', $ids) . ") AND | |
613 | record_type_id = {$sourceID}"; | |
6a488035 TO |
614 | $dao = CRM_Core_DAO::executeQuery($query); |
615 | while ($dao->fetch()) { | |
b4eac1e9 | 616 | $relIDs[] = $dao->contact_id; |
6a488035 TO |
617 | } |
618 | } | |
619 | else { | |
919eddec | 620 | $component = self::exportComponent($exportMode); |
040898fe | 621 | |
622 | if ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) { | |
623 | $relIDs = CRM_Case_BAO_Case::retrieveContactIdsByCaseId($ids); | |
624 | } | |
625 | else { | |
626 | $relIDs = CRM_Core_DAO::getContactIDsFromComponent($ids, $component); | |
627 | } | |
6a488035 TO |
628 | } |
629 | ||
630 | $relationshipJoin = $relationshipClause = ''; | |
34b773b7 | 631 | if (!$selectAll && $componentTable) { |
632 | $relationshipJoin = " INNER JOIN {$componentTable} ctTable ON ctTable.contact_id = {$contactA}"; | |
6a488035 | 633 | } |
34b773b7 | 634 | elseif (!empty($relIDs)) { |
6a488035 TO |
635 | $relID = implode(',', $relIDs); |
636 | $relationshipClause = " AND crel.{$contactA} IN ( {$relID} )"; | |
637 | } | |
638 | ||
639 | $relationFrom = " {$relationFrom} | |
640 | INNER JOIN civicrm_relationship crel ON crel.{$contactB} = contact_a.id AND crel.relationship_type_id = {$id} | |
641 | {$relationshipJoin} "; | |
642 | ||
643 | //check for active relationship status only | |
97f6897c TO |
644 | $today = date('Ymd'); |
645 | $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )"; | |
646 | $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause} {$relationActive}"; | |
0ee5581e | 647 | $relationGroupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($relationQuery[$rel]->_select, "crel.{$contactA}"); |
97f6897c | 648 | $relationSelect = "{$relationSelect}, {$contactA} as refContact "; |
6a488035 TO |
649 | $relationQueryString = "$relationSelect $relationFrom $relationWhere $relationHaving $relationGroupBy"; |
650 | ||
651 | $allRelContactDAO = CRM_Core_DAO::executeQuery($relationQueryString); | |
652 | while ($allRelContactDAO->fetch()) { | |
653 | //FIX Me: Migrate this to table rather than array | |
654 | // build the array of all related contacts | |
655 | $allRelContactArray[$rel][$allRelContactDAO->refContact] = clone($allRelContactDAO); | |
656 | } | |
657 | $allRelContactDAO->free(); | |
658 | } | |
659 | } | |
660 | ||
661 | // make sure the groups stuff is included only if specifically specified | |
662 | // by the fields param (CRM-1969), else we limit the contacts outputted to only | |
663 | // ones that are part of a group | |
a7488080 | 664 | if (!empty($returnProperties['groups'])) { |
6a488035 TO |
665 | $oldClause = "( contact_a.id = civicrm_group_contact.contact_id )"; |
666 | $newClause = " ( $oldClause AND ( civicrm_group_contact.status = 'Added' OR civicrm_group_contact.status IS NULL ) )"; | |
667 | // total hack for export, CRM-3618 | |
668 | $from = str_replace($oldClause, | |
669 | $newClause, | |
670 | $from | |
671 | ); | |
672 | } | |
673 | ||
34b773b7 | 674 | if (!$selectAll && $componentTable) { |
6a488035 TO |
675 | $from .= " INNER JOIN $componentTable ctTable ON ctTable.contact_id = contact_a.id "; |
676 | } | |
677 | elseif ($componentClause) { | |
678 | if (empty($where)) { | |
679 | $where = "WHERE $componentClause"; | |
680 | } | |
681 | else { | |
682 | $where .= " AND $componentClause"; | |
683 | } | |
684 | } | |
685 | ||
14a08308 | 686 | // CRM-13982 - check if is deleted |
687 | $excludeTrashed = TRUE; | |
688 | foreach ($params as $value) { | |
689 | if ($value[0] == 'contact_is_deleted') { | |
690 | $excludeTrashed = FALSE; | |
691 | } | |
692 | } | |
17bc4f1b SL |
693 | $trashClause = $excludeTrashed ? "contact_a.is_deleted != 1" : "( 1 )"; |
694 | ||
0b02cdf2 | 695 | if (empty($where)) { |
17bc4f1b | 696 | $where = "WHERE $trashClause"; |
14a08308 | 697 | } |
17bc4f1b SL |
698 | else { |
699 | $where .= " AND $trashClause"; | |
14a08308 | 700 | } |
701 | ||
6a488035 TO |
702 | $queryString = "$select $from $where $having"; |
703 | ||
0cc9927d | 704 | $groupBy = self::getGroupBy($exportMode, $queryMode, $returnProperties, $query); |
8a13a745 | 705 | |
6a488035 | 706 | $queryString .= $groupBy; |
33a5a53d | 707 | |
6a488035 | 708 | if ($order) { |
7f5cc737 | 709 | // always add contact_a.id to the ORDER clause |
710 | // so the order is deterministic | |
711 | //CRM-15301 | |
712 | if (strpos('contact_a.id', $order) === FALSE) { | |
713 | $order .= ", contact_a.id"; | |
714 | } | |
5a6afd32 | 715 | |
6a488035 TO |
716 | list($field, $dir) = explode(' ', $order, 2); |
717 | $field = trim($field); | |
a7488080 | 718 | if (!empty($returnProperties[$field])) { |
33a5a53d | 719 | //CRM-15301 |
720 | $queryString .= " ORDER BY $order"; | |
6a488035 TO |
721 | } |
722 | } | |
723 | ||
724 | $multipleSelectFields = array('preferred_communication_method' => 1); | |
725 | ||
726 | $addPaymentHeader = FALSE; | |
727 | ||
728 | $paymentDetails = array(); | |
d77aba4b | 729 | if ($paymentFields || $selectedPaymentFields) { |
6a488035 TO |
730 | |
731 | // get payment related in for event and members | |
732 | $paymentDetails = CRM_Contribute_BAO_Contribution::getContributionDetails($exportMode, $ids); | |
d77aba4b | 733 | //get all payment headers. |
7460c131 AS |
734 | // If we haven't selected specific payment fields, load in all the |
735 | // payment headers. | |
d77aba4b AS |
736 | if (!$selectedPaymentFields) { |
737 | $paymentHeaders = self::componentPaymentFields(); | |
738 | if (!empty($paymentDetails)) { | |
739 | $addPaymentHeader = TRUE; | |
740 | } | |
6a488035 | 741 | } |
55f37718 | 742 | // If we have selected specific payment fields, leave the payment headers |
7460c131 AS |
743 | // as an empty array; the headers for each selected field will be added |
744 | // elsewhere. | |
745 | else { | |
746 | $paymentHeaders = array(); | |
747 | } | |
6a488035 TO |
748 | $nullContributionDetails = array_fill_keys(array_keys($paymentHeaders), NULL); |
749 | } | |
750 | ||
12a36993 | 751 | $componentDetails = array(); |
6a488035 TO |
752 | $setHeader = TRUE; |
753 | ||
754 | $rowCount = self::EXPORT_ROW_COUNT; | |
755 | $offset = 0; | |
756 | // we write to temp table often to avoid using too much memory | |
757 | $tempRowCount = 100; | |
758 | ||
759 | $count = -1; | |
760 | ||
761 | // for CRM-3157 purposes | |
762 | $i18n = CRM_Core_I18n::singleton(); | |
24c7dffa | 763 | |
12a36993 | 764 | list($outputColumns, $headerRows, $sqlColumns, $metadata) = self::getExportStructureArrays($returnProperties, $query, $phoneTypes, $imProviders, $contactRelationshipTypes, $relationQuery, $selectedPaymentFields); |
765 | ||
24c7dffa | 766 | $limitReached = FALSE; |
767 | while (!$limitReached) { | |
6a488035 | 768 | $limitQuery = "{$queryString} LIMIT {$offset}, {$rowCount}"; |
c2a768c7 | 769 | $dao = CRM_Core_DAO::executeQuery($limitQuery); |
24c7dffa | 770 | // If this is less than our limit by the end of the iteration we do not need to run the query again to |
771 | // check if some remain. | |
772 | $rowsThisIteration = 0; | |
6a488035 TO |
773 | |
774 | while ($dao->fetch()) { | |
775 | $count++; | |
24c7dffa | 776 | $rowsThisIteration++; |
6a488035 TO |
777 | $row = array(); |
778 | ||
b23943b8 | 779 | //convert the pseudo constants |
62835cac EM |
780 | // CRM-14398 there is problem in this architecture that is not easily solved. For now we are using the cloned |
781 | // temporary iterationDAO object to get around it. | |
782 | // the issue is that the convertToPseudoNames function is adding additional properties (e.g for campaign) to the DAO object | |
783 | // these additional properties are NOT reset when the $dao cycles through the while loop | |
784 | // nor are they overwritten as they are not in the loop | |
785 | // the convertToPseudoNames will not adequately over-write them either as it doesn't 'kick-in' unless the | |
786 | // relevant property is set. | |
787 | // It may be that a long-term fix could be introduced there - however, it's probably necessary to figure out how to test the | |
788 | // export class before tackling a better architectural fix | |
789 | $iterationDAO = clone $dao; | |
790 | $query->convertToPseudoNames($iterationDAO); | |
d9ab802d | 791 | |
55f37718 | 792 | //first loop through output columns so that we return what is required, and in same order. |
6a488035 | 793 | $relationshipField = 0; |
55f37718 | 794 | foreach ($outputColumns as $field => $value) { |
6a488035 TO |
795 | |
796 | // add im_provider to $dao object | |
62835cac EM |
797 | if ($field == 'im_provider' && property_exists($iterationDAO, 'provider_id')) { |
798 | $iterationDAO->im_provider = $iterationDAO->provider_id; | |
6a488035 TO |
799 | } |
800 | ||
801 | //build row values (data) | |
802 | $fieldValue = NULL; | |
62835cac EM |
803 | if (property_exists($iterationDAO, $field)) { |
804 | $fieldValue = $iterationDAO->$field; | |
6a488035 TO |
805 | // to get phone type from phone type id |
806 | if ($field == 'phone_type_id' && isset($phoneTypes[$fieldValue])) { | |
807 | $fieldValue = $phoneTypes[$fieldValue]; | |
808 | } | |
809 | elseif ($field == 'provider_id' || $field == 'im_provider') { | |
810 | $fieldValue = CRM_Utils_Array::value($fieldValue, $imProviders); | |
811 | } | |
994a070c | 812 | elseif (strstr($field, 'master_id')) { |
6a488035 | 813 | $masterAddressId = NULL; |
994a070c | 814 | if (isset($iterationDAO->$field)) { |
815 | $masterAddressId = $iterationDAO->$field; | |
6a488035 TO |
816 | } |
817 | // get display name of contact that address is shared. | |
62835cac | 818 | $fieldValue = CRM_Contact_BAO_Contact::getMasterDisplayName($masterAddressId, $iterationDAO->contact_id); |
6a488035 TO |
819 | } |
820 | } | |
821 | ||
822 | if ($field == 'id') { | |
62835cac | 823 | $row[$field] = $iterationDAO->contact_id; |
6a488035 TO |
824 | // special case for calculated field |
825 | } | |
d4a280d8 | 826 | elseif ($field == 'source_contact_id') { |
62835cac | 827 | $row[$field] = $iterationDAO->contact_id; |
d4a280d8 | 828 | } |
6a488035 | 829 | elseif ($field == 'pledge_balance_amount') { |
62835cac | 830 | $row[$field] = $iterationDAO->pledge_amount - $iterationDAO->pledge_total_paid; |
6a488035 TO |
831 | // special case for calculated field |
832 | } | |
833 | elseif ($field == 'pledge_next_pay_amount') { | |
62835cac | 834 | $row[$field] = $iterationDAO->pledge_next_pay_amount + $iterationDAO->pledge_outstanding_amount; |
6a488035 | 835 | } |
6a488035 | 836 | elseif (array_key_exists($field, $contactRelationshipTypes)) { |
62835cac | 837 | $relDAO = CRM_Utils_Array::value($iterationDAO->contact_id, $allRelContactArray[$field]); |
02709b11 | 838 | $relationQuery[$field]->convertToPseudoNames($relDAO); |
6a488035 TO |
839 | foreach ($value as $relationField => $relationValue) { |
840 | if (is_object($relDAO) && property_exists($relDAO, $relationField)) { | |
841 | $fieldValue = $relDAO->$relationField; | |
842 | if ($relationField == 'phone_type_id') { | |
843 | $fieldValue = $phoneTypes[$relationValue]; | |
844 | } | |
845 | elseif ($relationField == 'provider_id') { | |
846 | $fieldValue = CRM_Utils_Array::value($relationValue, $imProviders); | |
847 | } | |
36ae0094 JL |
848 | // CRM-13995 |
849 | elseif (is_object($relDAO) && in_array($relationField, array( | |
850 | 'email_greeting', | |
851 | 'postal_greeting', | |
852 | 'addressee', | |
853 | )) | |
854 | ) { | |
855 | //special case for greeting replacement | |
856 | $fldValue = "{$relationField}_display"; | |
857 | $fieldValue = $relDAO->$fldValue; | |
858 | } | |
2bf55c30 | 859 | } |
97f6897c | 860 | elseif (is_object($relDAO) && $relationField == 'state_province') { |
2bf55c30 BS |
861 | $fieldValue = CRM_Core_PseudoConstant::stateProvince($relDAO->state_province_id); |
862 | } | |
97f6897c | 863 | elseif (is_object($relDAO) && $relationField == 'country') { |
b07c1873 DG |
864 | $fieldValue = CRM_Core_PseudoConstant::country($relDAO->country_id); |
865 | } | |
6a488035 TO |
866 | else { |
867 | $fieldValue = ''; | |
868 | } | |
869 | $field = $field . '_'; | |
2bf55c30 | 870 | |
02709b11 | 871 | if (array_key_exists($relationField, $multipleSelectFields)) { |
872 | $param = array($relationField => $fieldValue); | |
873 | $names = array($relationField => array('newName' => $relationField, 'groupName' => $relationField)); | |
874 | CRM_Core_OptionGroup::lookupValues($param, $names, FALSE); | |
875 | $fieldValue = $param[$relationField]; | |
876 | } | |
6a488035 TO |
877 | if (is_object($relDAO) && $relationField == 'id') { |
878 | $row[$field . $relationField] = $relDAO->contact_id; | |
2bf55c30 | 879 | } |
97f6897c | 880 | elseif (is_array($relationValue) && $relationField == 'location') { |
6a488035 TO |
881 | foreach ($relationValue as $ltype => $val) { |
882 | foreach (array_keys($val) as $fld) { | |
883 | $type = explode('-', $fld); | |
884 | $fldValue = "{$ltype}-" . $type[0]; | |
a7488080 | 885 | if (!empty($type[1])) { |
6a488035 TO |
886 | $fldValue .= "-" . $type[1]; |
887 | } | |
888 | // CRM-3157: localise country, region (both have ‘country’ context) | |
889 | // and state_province (‘province’ context) | |
890 | switch (TRUE) { | |
97f6897c | 891 | case (!is_object($relDAO)): |
6a488035 TO |
892 | $row[$field . '_' . $fldValue] = ''; |
893 | break; | |
894 | ||
895 | case in_array('country', $type): | |
896 | case in_array('world_region', $type): | |
897 | $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue, | |
898 | array('context' => 'country') | |
899 | ); | |
900 | break; | |
901 | ||
902 | case in_array('state_province', $type): | |
903 | $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue, | |
904 | array('context' => 'province') | |
905 | ); | |
906 | break; | |
907 | ||
908 | default: | |
909 | $row[$field . '_' . $fldValue] = $relDAO->$fldValue; | |
910 | break; | |
911 | } | |
912 | } | |
913 | } | |
914 | } | |
915 | elseif (isset($fieldValue) && $fieldValue != '') { | |
916 | //check for custom data | |
917 | if ($cfID = CRM_Core_BAO_CustomField::getKeyID($relationField)) { | |
8cee0c70 | 918 | $row[$field . $relationField] = CRM_Core_BAO_CustomField::displayValue($fieldValue, $cfID); |
6a488035 | 919 | } |
6a488035 TO |
920 | else { |
921 | //normal relationship fields | |
922 | // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context) | |
923 | switch ($relationField) { | |
924 | case 'country': | |
925 | case 'world_region': | |
926 | $row[$field . $relationField] = $i18n->crm_translate($fieldValue, array('context' => 'country')); | |
927 | break; | |
928 | ||
929 | case 'state_province': | |
930 | $row[$field . $relationField] = $i18n->crm_translate($fieldValue, array('context' => 'province')); | |
931 | break; | |
932 | ||
933 | default: | |
934 | $row[$field . $relationField] = $fieldValue; | |
935 | break; | |
936 | } | |
937 | } | |
938 | } | |
939 | else { | |
940 | // if relation field is empty or null | |
941 | $row[$field . $relationField] = ''; | |
942 | } | |
943 | } | |
944 | } | |
945 | elseif (isset($fieldValue) && | |
946 | $fieldValue != '' | |
947 | ) { | |
948 | //check for custom data | |
949 | if ($cfID = CRM_Core_BAO_CustomField::getKeyID($field)) { | |
8cee0c70 | 950 | $row[$field] = CRM_Core_BAO_CustomField::displayValue($fieldValue, $cfID); |
6a488035 TO |
951 | } |
952 | elseif (array_key_exists($field, $multipleSelectFields)) { | |
953 | //option group fixes | |
954 | $paramsNew = array($field => $fieldValue); | |
955 | if ($field == 'test_tutoring') { | |
956 | $name = array($field => array('newName' => $field, 'groupName' => 'test')); | |
957 | // for readers group | |
958 | } | |
959 | elseif (substr($field, 0, 4) == 'cmr_') { | |
960 | $name = array($field => array('newName' => $field, 'groupName' => substr($field, 0, -3))); | |
961 | } | |
962 | else { | |
963 | $name = array($field => array('newName' => $field, 'groupName' => $field)); | |
964 | } | |
965 | CRM_Core_OptionGroup::lookupValues($paramsNew, $name, FALSE); | |
966 | $row[$field] = $paramsNew[$field]; | |
967 | } | |
55f37718 | 968 | |
6a488035 | 969 | elseif (in_array($field, array( |
97f6897c TO |
970 | 'email_greeting', |
971 | 'postal_greeting', | |
21dfd5f5 | 972 | 'addressee', |
97f6897c | 973 | ))) { |
6a488035 TO |
974 | //special case for greeting replacement |
975 | $fldValue = "{$field}_display"; | |
62835cac | 976 | $row[$field] = $iterationDAO->$fldValue; |
6a488035 TO |
977 | } |
978 | else { | |
979 | //normal fields with a touch of CRM-3157 | |
980 | switch ($field) { | |
981 | case 'country': | |
982 | case 'world_region': | |
983 | $row[$field] = $i18n->crm_translate($fieldValue, array('context' => 'country')); | |
984 | break; | |
985 | ||
986 | case 'state_province': | |
987 | $row[$field] = $i18n->crm_translate($fieldValue, array('context' => 'province')); | |
988 | break; | |
989 | ||
990 | case 'gender': | |
991 | case 'preferred_communication_method': | |
992 | case 'preferred_mail_format': | |
aa62b355 | 993 | case 'communication_style': |
6a488035 TO |
994 | $row[$field] = $i18n->crm_translate($fieldValue); |
995 | break; | |
996 | ||
997 | default: | |
12a36993 | 998 | if (isset($metadata[$field])) { |
999 | // No I don't know why we do it this way & whether we could | |
1000 | // make better use of pseudoConstants. | |
1001 | if (!empty($metadata[$field]['context'])) { | |
1002 | $row[$field] = $i18n->crm_translate($fieldValue, $metadata[$field]); | |
1003 | break; | |
1004 | } | |
1005 | if (!empty($metadata[$field]['pseudoconstant'])) { | |
1006 | // This is not our normal syntax for pseudoconstants but I am a bit loath to | |
1007 | // call an external function until sure it is not increasing php processing given this | |
1008 | // may be iterated 100,000 times & we already have the $imProvider var loaded. | |
1009 | // That can be next refactor... | |
1010 | // Yes - definitely feeling hatred for this bit of code - I know you will beat me up over it's awfulness | |
1011 | // but I have to reach a stable point.... | |
1012 | $varName = $metadata[$field]['pseudoconstant']['var']; | |
1013 | $labels = $$varName; | |
1014 | $row[$field] = $labels[$fieldValue]; | |
1015 | break; | |
1016 | } | |
1017 | ||
1018 | } | |
6a488035 TO |
1019 | $row[$field] = $fieldValue; |
1020 | break; | |
1021 | } | |
1022 | } | |
1023 | } | |
d77aba4b | 1024 | elseif ($selectedPaymentFields && array_key_exists($field, self::componentPaymentFields())) { |
62835cac | 1025 | $paymentData = CRM_Utils_Array::value($iterationDAO->$paymentTableId, $paymentDetails); |
d77aba4b | 1026 | $payFieldMapper = array( |
97f6897c | 1027 | 'componentPaymentField_total_amount' => 'total_amount', |
d77aba4b | 1028 | 'componentPaymentField_contribution_status' => 'contribution_status', |
97f6897c TO |
1029 | 'componentPaymentField_payment_instrument' => 'pay_instru', |
1030 | 'componentPaymentField_transaction_id' => 'trxn_id', | |
1031 | 'componentPaymentField_received_date' => 'receive_date', | |
d77aba4b AS |
1032 | ); |
1033 | $row[$field] = CRM_Utils_Array::value($payFieldMapper[$field], $paymentData, ''); | |
1034 | } | |
6a488035 TO |
1035 | else { |
1036 | // if field is empty or null | |
1037 | $row[$field] = ''; | |
1038 | } | |
1039 | } | |
1040 | ||
1041 | // add payment headers if required | |
1042 | if ($addPaymentHeader && $paymentFields) { | |
12a36993 | 1043 | // @todo rather than do this for every single row do it before the loop starts. |
1044 | // where other header definitions take place. | |
6a488035 TO |
1045 | $headerRows = array_merge($headerRows, $paymentHeaders); |
1046 | foreach (array_keys($paymentHeaders) as $paymentHdr) { | |
1047 | self::sqlColumnDefn($query, $sqlColumns, $paymentHdr); | |
1048 | } | |
6a488035 TO |
1049 | } |
1050 | ||
1051 | if ($setHeader) { | |
1052 | $exportTempTable = self::createTempTable($sqlColumns); | |
1053 | } | |
1054 | ||
1055 | //build header only once | |
1056 | $setHeader = FALSE; | |
1057 | ||
e026db3c | 1058 | // If specific payment fields have been selected for export, payment |
545285b8 | 1059 | // data will already be in $row. Otherwise, add payment related |
e026db3c | 1060 | // information, if appropriate. |
fc33177b | 1061 | if ($addPaymentHeader) { |
1062 | if (!$selectedPaymentFields) { | |
1063 | if ($paymentFields) { | |
1064 | $paymentData = CRM_Utils_Array::value($row[$paymentTableId], $paymentDetails); | |
1065 | if (!is_array($paymentData) || empty($paymentData)) { | |
1066 | $paymentData = $nullContributionDetails; | |
1067 | } | |
1068 | $row = array_merge($row, $paymentData); | |
1069 | } | |
1070 | elseif (!empty($paymentDetails)) { | |
1071 | $row = array_merge($row, $nullContributionDetails); | |
e026db3c | 1072 | } |
e026db3c | 1073 | } |
6a488035 | 1074 | } |
6a488035 | 1075 | //remove organization name for individuals if it is set for current employer |
a7488080 | 1076 | if (!empty($row['contact_type']) && |
6a488035 TO |
1077 | $row['contact_type'] == 'Individual' && array_key_exists('organization_name', $row) |
1078 | ) { | |
1079 | $row['organization_name'] = ''; | |
1080 | } | |
1081 | ||
1082 | // add component info | |
1083 | // write the row to a file | |
1084 | $componentDetails[] = $row; | |
1085 | ||
1086 | // output every $tempRowCount rows | |
1087 | if ($count % $tempRowCount == 0) { | |
1088 | self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); | |
1089 | $componentDetails = array(); | |
1090 | } | |
1091 | } | |
1092 | $dao->free(); | |
24c7dffa | 1093 | if ($rowsThisIteration < self::EXPORT_ROW_COUNT) { |
1094 | $limitReached = TRUE; | |
1095 | } | |
6a488035 TO |
1096 | $offset += $rowCount; |
1097 | } | |
1098 | ||
1099 | if ($exportTempTable) { | |
1100 | self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); | |
1101 | ||
1102 | // do merge same address and merge same household processing | |
1103 | if ($mergeSameAddress) { | |
1104 | self::mergeSameAddress($exportTempTable, $headerRows, $sqlColumns, $exportParams); | |
1105 | } | |
1106 | ||
1107 | // merge the records if they have corresponding households | |
1108 | if ($mergeSameHousehold) { | |
1109 | self::mergeSameHousehold($exportTempTable, $headerRows, $sqlColumns, $relationKeyMOH); | |
1110 | self::mergeSameHousehold($exportTempTable, $headerRows, $sqlColumns, $relationKeyHOH); | |
1111 | } | |
1112 | ||
6a488035 TO |
1113 | // if postalMailing option is checked, exclude contacts who are deceased, have |
1114 | // "Do not mail" privacy setting, or have no street address | |
1115 | if (isset($exportParams['postal_mailing_export']['postal_mailing_export']) && | |
1116 | $exportParams['postal_mailing_export']['postal_mailing_export'] == 1 | |
1117 | ) { | |
1118 | self::postalMailingFormat($exportTempTable, $headerRows, $sqlColumns, $exportMode); | |
1119 | } | |
1120 | ||
1121 | // call export hook | |
1122 | CRM_Utils_Hook::export($exportTempTable, $headerRows, $sqlColumns, $exportMode); | |
1123 | ||
ef51caa8 | 1124 | // In order to be able to write a unit test against this function we need to suppress |
1125 | // the csv writing. In future hopefully the csv writing & the main processing will be in separate functions. | |
1126 | if (empty($exportParams['suppress_csv_for_testing'])) { | |
1127 | self::writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $exportMode); | |
1128 | } | |
994a070c | 1129 | else { |
1130 | // return tableName and sqlColumns in test context | |
1131 | return array($exportTempTable, $sqlColumns); | |
1132 | } | |
6a488035 TO |
1133 | |
1134 | // delete the export temp table and component table | |
1135 | $sql = "DROP TABLE IF EXISTS {$exportTempTable}"; | |
1136 | CRM_Core_DAO::executeQuery($sql); | |
994a070c | 1137 | |
1138 | CRM_Utils_System::civiExit(); | |
6a488035 TO |
1139 | } |
1140 | else { | |
1141 | CRM_Core_Error::fatal(ts('No records to export')); | |
1142 | } | |
1143 | } | |
1144 | ||
1145 | /** | |
fe482240 | 1146 | * Name of the export file based on mode. |
6a488035 | 1147 | * |
b9add4b3 TO |
1148 | * @param string $output |
1149 | * Type of output. | |
1150 | * @param int $mode | |
1151 | * Export mode. | |
6a488035 | 1152 | * |
a6c01b45 CW |
1153 | * @return string |
1154 | * name of the file | |
6a488035 | 1155 | */ |
00be9182 | 1156 | public static function getExportFileName($output = 'csv', $mode = CRM_Export_Form_Select::CONTACT_EXPORT) { |
6a488035 TO |
1157 | switch ($mode) { |
1158 | case CRM_Export_Form_Select::CONTACT_EXPORT: | |
1159 | return ts('CiviCRM Contact Search'); | |
1160 | ||
1161 | case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: | |
1162 | return ts('CiviCRM Contribution Search'); | |
1163 | ||
1164 | case CRM_Export_Form_Select::MEMBER_EXPORT: | |
1165 | return ts('CiviCRM Member Search'); | |
1166 | ||
1167 | case CRM_Export_Form_Select::EVENT_EXPORT: | |
1168 | return ts('CiviCRM Participant Search'); | |
1169 | ||
1170 | case CRM_Export_Form_Select::PLEDGE_EXPORT: | |
1171 | return ts('CiviCRM Pledge Search'); | |
1172 | ||
1173 | case CRM_Export_Form_Select::CASE_EXPORT: | |
1174 | return ts('CiviCRM Case Search'); | |
1175 | ||
1176 | case CRM_Export_Form_Select::GRANT_EXPORT: | |
1177 | return ts('CiviCRM Grant Search'); | |
1178 | ||
1179 | case CRM_Export_Form_Select::ACTIVITY_EXPORT: | |
1180 | return ts('CiviCRM Activity Search'); | |
1181 | } | |
1182 | } | |
1183 | ||
1184 | /** | |
100fef9d | 1185 | * Handle import error file creation. |
6a488035 | 1186 | */ |
00be9182 | 1187 | public static function invoke() { |
a3d827a7 CW |
1188 | $type = CRM_Utils_Request::retrieve('type', 'Positive'); |
1189 | $parserName = CRM_Utils_Request::retrieve('parser', 'String'); | |
6a488035 TO |
1190 | if (empty($parserName) || empty($type)) { |
1191 | return; | |
1192 | } | |
1193 | ||
1194 | // clean and ensure parserName is a valid string | |
1195 | $parserName = CRM_Utils_String::munge($parserName); | |
1196 | $parserClass = explode('_', $parserName); | |
1197 | ||
1198 | // make sure parserClass is in the CRM namespace and | |
1199 | // at least 3 levels deep | |
1200 | if ($parserClass[0] == 'CRM' && | |
1201 | count($parserClass) >= 3 | |
1202 | ) { | |
d3e86119 | 1203 | require_once str_replace('_', DIRECTORY_SEPARATOR, $parserName) . ".php"; |
6a488035 TO |
1204 | // ensure the functions exists |
1205 | if (method_exists($parserName, 'errorFileName') && | |
1206 | method_exists($parserName, 'saveFileName') | |
1207 | ) { | |
1208 | $errorFileName = $parserName::errorFileName($type); | |
1209 | $saveFileName = $parserName::saveFileName($type); | |
1210 | if (!empty($errorFileName) && !empty($saveFileName)) { | |
d42a224c CW |
1211 | CRM_Utils_System::setHttpHeader('Cache-Control', 'must-revalidate, post-check=0, pre-check=0'); |
1212 | CRM_Utils_System::setHttpHeader('Content-Description', 'File Transfer'); | |
1213 | CRM_Utils_System::setHttpHeader('Content-Type', 'text/csv'); | |
1214 | CRM_Utils_System::setHttpHeader('Content-Length', filesize($errorFileName)); | |
1215 | CRM_Utils_System::setHttpHeader('Content-Disposition', 'attachment; filename=' . $saveFileName); | |
6a488035 TO |
1216 | |
1217 | readfile($errorFileName); | |
1218 | } | |
1219 | } | |
1220 | } | |
1221 | CRM_Utils_System::civiExit(); | |
1222 | } | |
1223 | ||
e0ef6999 EM |
1224 | /** |
1225 | * @param $customSearchClass | |
1226 | * @param $formValues | |
1227 | * @param $order | |
1228 | */ | |
00be9182 | 1229 | public static function exportCustom($customSearchClass, $formValues, $order) { |
6a488035 TO |
1230 | $ext = CRM_Extension_System::singleton()->getMapper(); |
1231 | if (!$ext->isExtensionClass($customSearchClass)) { | |
d3e86119 | 1232 | require_once str_replace('_', DIRECTORY_SEPARATOR, $customSearchClass) . '.php'; |
6a488035 TO |
1233 | } |
1234 | else { | |
d3e86119 | 1235 | require_once $ext->classToPath($customSearchClass); |
6a488035 TO |
1236 | } |
1237 | $search = new $customSearchClass($formValues); | |
1238 | ||
1239 | $includeContactIDs = FALSE; | |
1240 | if ($formValues['radio_ts'] == 'ts_sel') { | |
1241 | $includeContactIDs = TRUE; | |
1242 | } | |
1243 | ||
1244 | $sql = $search->all(0, 0, $order, $includeContactIDs); | |
1245 | ||
1246 | $columns = $search->columns(); | |
1247 | ||
1248 | $header = array_keys($columns); | |
1249 | $fields = array_values($columns); | |
1250 | ||
97f6897c TO |
1251 | $rows = array(); |
1252 | $dao = CRM_Core_DAO::executeQuery($sql); | |
6a488035 TO |
1253 | $alterRow = FALSE; |
1254 | if (method_exists($search, 'alterRow')) { | |
1255 | $alterRow = TRUE; | |
1256 | } | |
1257 | while ($dao->fetch()) { | |
1258 | $row = array(); | |
1259 | ||
1260 | foreach ($fields as $field) { | |
37990ecd JV |
1261 | $unqualified_field = CRM_Utils_Array::First(array_slice(explode('.', $field), -1)); |
1262 | $row[$field] = $dao->$unqualified_field; | |
6a488035 TO |
1263 | } |
1264 | if ($alterRow) { | |
1265 | $search->alterRow($row); | |
1266 | } | |
1267 | $rows[] = $row; | |
1268 | } | |
1269 | ||
1270 | CRM_Core_Report_Excel::writeCSVFile(self::getExportFileName(), $header, $rows); | |
1271 | CRM_Utils_System::civiExit(); | |
1272 | } | |
1273 | ||
e0ef6999 EM |
1274 | /** |
1275 | * @param $query | |
1276 | * @param $sqlColumns | |
1277 | * @param $field | |
1278 | */ | |
12a36993 | 1279 | public static function sqlColumnDefn($query, &$sqlColumns, $field) { |
0944820d | 1280 | if (substr($field, -4) == '_a_b' || substr($field, -4) == '_b_a') { |
6a488035 TO |
1281 | return; |
1282 | } | |
1283 | ||
1284 | $fieldName = CRM_Utils_String::munge(strtolower($field), '_', 64); | |
1285 | if ($fieldName == 'id') { | |
1286 | $fieldName = 'civicrm_primary_id'; | |
1287 | } | |
1288 | ||
0944820d DL |
1289 | // early exit for master_id, CRM-12100 |
1290 | // in the DB it is an ID, but in the export, we retrive the display_name of the master record | |
86eef7b6 J |
1291 | // also for current_employer, CRM-16939 |
1292 | if ($fieldName == 'master_id' || $fieldName == 'current_employer') { | |
0944820d DL |
1293 | $sqlColumns[$fieldName] = "$fieldName varchar(128)"; |
1294 | return; | |
1295 | } | |
1296 | ||
55f37718 EM |
1297 | if (substr($fieldName, -11) == 'campaign_id') { |
1298 | // CRM-14398 | |
1299 | $sqlColumns[$fieldName] = "$fieldName varchar(128)"; | |
55f37718 EM |
1300 | return; |
1301 | } | |
1302 | ||
4f2997e2 | 1303 | $lookUp = array('prefix_id', 'suffix_id'); |
6a488035 TO |
1304 | // set the sql columns |
1305 | if (isset($query->_fields[$field]['type'])) { | |
1306 | switch ($query->_fields[$field]['type']) { | |
1307 | case CRM_Utils_Type::T_INT: | |
6a488035 | 1308 | case CRM_Utils_Type::T_BOOLEAN: |
a6332b16 | 1309 | if (in_array($field, $lookUp)) { |
4f2997e2 | 1310 | $sqlColumns[$fieldName] = "$fieldName varchar(255)"; |
1311 | } | |
1312 | else { | |
1313 | $sqlColumns[$fieldName] = "$fieldName varchar(16)"; | |
1314 | } | |
6a488035 TO |
1315 | break; |
1316 | ||
1317 | case CRM_Utils_Type::T_STRING: | |
1318 | if (isset($query->_fields[$field]['maxlength'])) { | |
1319 | $sqlColumns[$fieldName] = "$fieldName varchar({$query->_fields[$field]['maxlength']})"; | |
1320 | } | |
1321 | else { | |
fe66d6ea | 1322 | $sqlColumns[$fieldName] = "$fieldName varchar(255)"; |
6a488035 TO |
1323 | } |
1324 | break; | |
1325 | ||
1326 | case CRM_Utils_Type::T_TEXT: | |
1327 | case CRM_Utils_Type::T_LONGTEXT: | |
1328 | case CRM_Utils_Type::T_BLOB: | |
1329 | case CRM_Utils_Type::T_MEDIUMBLOB: | |
1330 | $sqlColumns[$fieldName] = "$fieldName longtext"; | |
1331 | break; | |
1332 | ||
1333 | case CRM_Utils_Type::T_FLOAT: | |
1334 | case CRM_Utils_Type::T_ENUM: | |
1335 | case CRM_Utils_Type::T_DATE: | |
1336 | case CRM_Utils_Type::T_TIME: | |
1337 | case CRM_Utils_Type::T_TIMESTAMP: | |
1338 | case CRM_Utils_Type::T_MONEY: | |
1339 | case CRM_Utils_Type::T_EMAIL: | |
1340 | case CRM_Utils_Type::T_URL: | |
1341 | case CRM_Utils_Type::T_CCNUM: | |
1342 | default: | |
1343 | $sqlColumns[$fieldName] = "$fieldName varchar(32)"; | |
1344 | break; | |
1345 | } | |
1346 | } | |
1347 | else { | |
1348 | if (substr($fieldName, -3, 3) == '_id') { | |
1338f0b1 | 1349 | // for trxn_id and its variants use a longer buffer |
b44e3f84 | 1350 | // to accommodate different systems - CRM-13739 |
1338f0b1 DL |
1351 | static $notRealIDFields = NULL; |
1352 | if ($notRealIDFields == NULL) { | |
f506227f | 1353 | $notRealIDFields = array('trxn_id', 'componentpaymentfield_transaction_id', 'phone_type_id'); |
1338f0b1 DL |
1354 | } |
1355 | ||
1356 | if (in_array($fieldName, $notRealIDFields)) { | |
1357 | $sqlColumns[$fieldName] = "$fieldName varchar(255)"; | |
1358 | } | |
1359 | else { | |
1360 | $sqlColumns[$fieldName] = "$fieldName varchar(16)"; | |
1361 | } | |
6a488035 | 1362 | } |
c05a91c9 | 1363 | elseif (substr($fieldName, -5, 5) == '_note') { |
1364 | $sqlColumns[$fieldName] = "$fieldName text"; | |
1365 | } | |
6a488035 TO |
1366 | else { |
1367 | $changeFields = array( | |
1368 | 'groups', | |
1369 | 'tags', | |
21dfd5f5 | 1370 | 'notes', |
6a488035 TO |
1371 | ); |
1372 | ||
1373 | if (in_array($fieldName, $changeFields)) { | |
1374 | $sqlColumns[$fieldName] = "$fieldName text"; | |
1375 | } | |
1376 | else { | |
1377 | // set the sql columns for custom data | |
1378 | if (isset($query->_fields[$field]['data_type'])) { | |
1379 | ||
1380 | switch ($query->_fields[$field]['data_type']) { | |
26509325 | 1381 | case 'String': |
f31fa3c9 | 1382 | // May be option labels, which could be up to 512 characters |
1383 | $length = max(512, CRM_Utils_Array::value('text_length', $query->_fields[$field])); | |
26509325 CW |
1384 | $sqlColumns[$fieldName] = "$fieldName varchar($length)"; |
1385 | break; | |
1386 | ||
6a488035 TO |
1387 | case 'Country': |
1388 | case 'StateProvince': | |
1389 | case 'Link': | |
6a488035 TO |
1390 | $sqlColumns[$fieldName] = "$fieldName varchar(255)"; |
1391 | break; | |
1392 | ||
1393 | case 'Memo': | |
1394 | $sqlColumns[$fieldName] = "$fieldName text"; | |
1395 | break; | |
1396 | ||
1397 | default: | |
fe4afc7b | 1398 | $sqlColumns[$fieldName] = "$fieldName varchar(255)"; |
6a488035 TO |
1399 | break; |
1400 | } | |
1401 | } | |
1402 | else { | |
e9f85685 | 1403 | $sqlColumns[$fieldName] = "$fieldName text"; |
6a488035 TO |
1404 | } |
1405 | } | |
1406 | } | |
1407 | } | |
1408 | } | |
1409 | ||
e0ef6999 | 1410 | /** |
100fef9d | 1411 | * @param string $tableName |
e0ef6999 EM |
1412 | * @param $details |
1413 | * @param $sqlColumns | |
1414 | */ | |
00be9182 | 1415 | public static function writeDetailsToTable($tableName, &$details, &$sqlColumns) { |
6a488035 TO |
1416 | if (empty($details)) { |
1417 | return; | |
1418 | } | |
1419 | ||
1420 | $sql = " | |
1421 | SELECT max(id) | |
1422 | FROM $tableName | |
1423 | "; | |
1424 | ||
1425 | $id = CRM_Core_DAO::singleValueQuery($sql); | |
1426 | if (!$id) { | |
1427 | $id = 0; | |
1428 | } | |
1429 | ||
1430 | $sqlClause = array(); | |
1431 | ||
1432 | foreach ($details as $dontCare => $row) { | |
1433 | $id++; | |
1434 | $valueString = array($id); | |
1435 | foreach ($row as $dontCare => $value) { | |
1436 | if (empty($value)) { | |
1437 | $valueString[] = "''"; | |
1438 | } | |
1439 | else { | |
1440 | $valueString[] = "'" . CRM_Core_DAO::escapeString($value) . "'"; | |
1441 | } | |
1442 | } | |
1443 | $sqlClause[] = '(' . implode(',', $valueString) . ')'; | |
1444 | } | |
1445 | ||
1446 | $sqlColumnString = '(id, ' . implode(',', array_keys($sqlColumns)) . ')'; | |
1447 | ||
1448 | $sqlValueString = implode(",\n", $sqlClause); | |
1449 | ||
1450 | $sql = " | |
1451 | INSERT INTO $tableName $sqlColumnString | |
1452 | VALUES $sqlValueString | |
1453 | "; | |
1454 | ||
1455 | CRM_Core_DAO::executeQuery($sql); | |
1456 | } | |
1457 | ||
e0ef6999 EM |
1458 | /** |
1459 | * @param $sqlColumns | |
1460 | * | |
1461 | * @return string | |
1462 | */ | |
00be9182 | 1463 | public static function createTempTable(&$sqlColumns) { |
6a488035 TO |
1464 | //creating a temporary table for the search result that need be exported |
1465 | $exportTempTable = CRM_Core_DAO::createTempTableName('civicrm_export', TRUE); | |
1466 | ||
1467 | // also create the sql table | |
1468 | $sql = "DROP TABLE IF EXISTS {$exportTempTable}"; | |
1469 | CRM_Core_DAO::executeQuery($sql); | |
1470 | ||
1471 | $sql = " | |
1472 | CREATE TABLE {$exportTempTable} ( | |
1473 | id int unsigned NOT NULL AUTO_INCREMENT, | |
1474 | "; | |
1475 | $sql .= implode(",\n", array_values($sqlColumns)); | |
1476 | ||
1477 | $sql .= ", | |
1478 | PRIMARY KEY ( id ) | |
1479 | "; | |
1480 | // add indexes for street_address and household_name if present | |
1481 | $addIndices = array( | |
1482 | 'street_address', | |
1483 | 'household_name', | |
1484 | 'civicrm_primary_id', | |
1485 | ); | |
1486 | ||
1487 | foreach ($addIndices as $index) { | |
1488 | if (isset($sqlColumns[$index])) { | |
1489 | $sql .= ", | |
1490 | INDEX index_{$index}( $index ) | |
1491 | "; | |
1492 | } | |
1493 | } | |
1494 | ||
1495 | $sql .= " | |
75f1cd78 | 1496 | ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci |
6a488035 TO |
1497 | "; |
1498 | ||
1499 | CRM_Core_DAO::executeQuery($sql); | |
1500 | return $exportTempTable; | |
1501 | } | |
1502 | ||
e0ef6999 | 1503 | /** |
100fef9d | 1504 | * @param string $tableName |
e0ef6999 EM |
1505 | * @param $headerRows |
1506 | * @param $sqlColumns | |
100fef9d | 1507 | * @param array $exportParams |
e0ef6999 | 1508 | */ |
00be9182 | 1509 | public static function mergeSameAddress($tableName, &$headerRows, &$sqlColumns, $exportParams) { |
6a488035 TO |
1510 | // check if any records are present based on if they have used shared address feature, |
1511 | // and not based on if city / state .. matches. | |
1512 | $sql = " | |
1513 | SELECT r1.id as copy_id, | |
1514 | r1.civicrm_primary_id as copy_contact_id, | |
1515 | r1.addressee as copy_addressee, | |
1516 | r1.addressee_id as copy_addressee_id, | |
1517 | r1.postal_greeting as copy_postal_greeting, | |
1518 | r1.postal_greeting_id as copy_postal_greeting_id, | |
1519 | r2.id as master_id, | |
1520 | r2.civicrm_primary_id as master_contact_id, | |
1521 | r2.postal_greeting as master_postal_greeting, | |
1522 | r2.postal_greeting_id as master_postal_greeting_id, | |
1523 | r2.addressee as master_addressee, | |
1524 | r2.addressee_id as master_addressee_id | |
1525 | FROM $tableName r1 | |
1526 | INNER JOIN civicrm_address adr ON r1.master_id = adr.id | |
1527 | INNER JOIN $tableName r2 ON adr.contact_id = r2.civicrm_primary_id | |
1528 | ORDER BY r1.id"; | |
1529 | $linkedMerge = self::_buildMasterCopyArray($sql, $exportParams, TRUE); | |
1530 | ||
1531 | // find all the records that have the same street address BUT not in a household | |
1532 | // require match on city and state as well | |
1533 | $sql = " | |
1534 | SELECT r1.id as master_id, | |
1535 | r1.civicrm_primary_id as master_contact_id, | |
1536 | r1.postal_greeting as master_postal_greeting, | |
1537 | r1.postal_greeting_id as master_postal_greeting_id, | |
1538 | r1.addressee as master_addressee, | |
1539 | r1.addressee_id as master_addressee_id, | |
1540 | r2.id as copy_id, | |
1541 | r2.civicrm_primary_id as copy_contact_id, | |
1542 | r2.postal_greeting as copy_postal_greeting, | |
1543 | r2.postal_greeting_id as copy_postal_greeting_id, | |
1544 | r2.addressee as copy_addressee, | |
1545 | r2.addressee_id as copy_addressee_id | |
1546 | FROM $tableName r1 | |
1547 | LEFT JOIN $tableName r2 ON ( r1.street_address = r2.street_address AND | |
1548 | r1.city = r2.city AND | |
1549 | r1.state_province_id = r2.state_province_id ) | |
1550 | WHERE ( r1.household_name IS NULL OR r1.household_name = '' ) | |
1551 | AND ( r2.household_name IS NULL OR r2.household_name = '' ) | |
1552 | AND ( r1.street_address != '' ) | |
1553 | AND r2.id > r1.id | |
1554 | ORDER BY r1.id | |
1555 | "; | |
1556 | $merge = self::_buildMasterCopyArray($sql, $exportParams); | |
1557 | ||
1558 | // unset ids from $merge already present in $linkedMerge | |
1559 | foreach ($linkedMerge as $masterID => $values) { | |
1560 | $keys = array($masterID); | |
1561 | $keys = array_merge($keys, array_keys($values['copy'])); | |
1562 | foreach ($merge as $mid => $vals) { | |
1563 | if (in_array($mid, $keys)) { | |
1564 | unset($merge[$mid]); | |
1565 | } | |
1566 | else { | |
1567 | foreach ($values['copy'] as $copyId) { | |
1568 | if (in_array($copyId, $keys)) { | |
1569 | unset($merge[$mid]['copy'][$copyId]); | |
1570 | } | |
1571 | } | |
1572 | } | |
1573 | } | |
1574 | } | |
1575 | $merge = $merge + $linkedMerge; | |
1576 | ||
1577 | foreach ($merge as $masterID => $values) { | |
1578 | $sql = " | |
1579 | UPDATE $tableName | |
1580 | SET addressee = %1, postal_greeting = %2, email_greeting = %3 | |
1581 | WHERE id = %4 | |
1582 | "; | |
97f6897c TO |
1583 | $params = array( |
1584 | 1 => array($values['addressee'], 'String'), | |
6a488035 TO |
1585 | 2 => array($values['postalGreeting'], 'String'), |
1586 | 3 => array($values['emailGreeting'], 'String'), | |
1587 | 4 => array($masterID, 'Integer'), | |
1588 | ); | |
1589 | CRM_Core_DAO::executeQuery($sql, $params); | |
1590 | ||
1591 | // delete all copies | |
97f6897c | 1592 | $deleteIDs = array_keys($values['copy']); |
6a488035 | 1593 | $deleteIDString = implode(',', $deleteIDs); |
97f6897c | 1594 | $sql = " |
6a488035 TO |
1595 | DELETE FROM $tableName |
1596 | WHERE id IN ( $deleteIDString ) | |
1597 | "; | |
1598 | CRM_Core_DAO::executeQuery($sql); | |
1599 | } | |
1600 | ||
1601 | // unset temporary columns that were added for postal mailing format | |
1602 | if (!empty($exportParams['merge_same_address']['temp_columns'])) { | |
1603 | $unsetKeys = array_keys($sqlColumns); | |
1604 | foreach ($unsetKeys as $headerKey => $sqlColKey) { | |
1605 | if (array_key_exists($sqlColKey, $exportParams['merge_same_address']['temp_columns'])) { | |
1606 | unset($sqlColumns[$sqlColKey], $headerRows[$headerKey]); | |
1607 | } | |
1608 | } | |
1609 | } | |
1610 | } | |
1611 | ||
e0ef6999 | 1612 | /** |
100fef9d CW |
1613 | * @param int $contactId |
1614 | * @param array $exportParams | |
e0ef6999 EM |
1615 | * |
1616 | * @return array | |
1617 | */ | |
00be9182 | 1618 | public static function _replaceMergeTokens($contactId, $exportParams) { |
6a488035 TO |
1619 | $greetings = array(); |
1620 | $contact = NULL; | |
1621 | ||
1622 | $greetingFields = array( | |
1623 | 'postal_greeting', | |
1624 | 'addressee', | |
1625 | ); | |
1626 | foreach ($greetingFields as $greeting) { | |
a7488080 | 1627 | if (!empty($exportParams[$greeting])) { |
6a488035 TO |
1628 | $greetingLabel = $exportParams[$greeting]; |
1629 | if (empty($contact)) { | |
1630 | $values = array( | |
1631 | 'id' => $contactId, | |
1632 | 'version' => 3, | |
1633 | ); | |
1634 | $contact = civicrm_api('contact', 'get', $values); | |
1635 | ||
a7488080 | 1636 | if (!empty($contact['is_error'])) { |
6a488035 TO |
1637 | return $greetings; |
1638 | } | |
1639 | $contact = $contact['values'][$contact['id']]; | |
1640 | } | |
1641 | ||
1642 | $tokens = array('contact' => $greetingLabel); | |
1643 | $greetings[$greeting] = CRM_Utils_Token::replaceContactTokens($greetingLabel, $contact, NULL, $tokens); | |
1644 | } | |
1645 | } | |
1646 | return $greetings; | |
1647 | } | |
1648 | ||
1649 | /** | |
1650 | * The function unsets static part of the string, if token is the dynamic part. | |
54957108 | 1651 | * |
6a488035 TO |
1652 | * Example: 'Hello {contact.first_name}' => converted to => '{contact.first_name}' |
1653 | * i.e 'Hello Alan' => converted to => 'Alan' | |
54957108 | 1654 | * |
1655 | * @param string $parsedString | |
1656 | * @param string $defaultGreeting | |
1657 | * @param bool $addressMergeGreetings | |
1658 | * @param string $greetingType | |
1659 | * | |
1660 | * @return mixed | |
6a488035 | 1661 | */ |
317fceb4 | 1662 | public static function _trimNonTokens( |
97f6897c | 1663 | &$parsedString, $defaultGreeting, |
353ffa53 | 1664 | $addressMergeGreetings, $greetingType = 'postal_greeting' |
6a488035 | 1665 | ) { |
a7488080 | 1666 | if (!empty($addressMergeGreetings[$greetingType])) { |
6a488035 TO |
1667 | $greetingLabel = $addressMergeGreetings[$greetingType]; |
1668 | } | |
1669 | $greetingLabel = empty($greetingLabel) ? $defaultGreeting : $greetingLabel; | |
1670 | ||
1671 | $stringsToBeReplaced = preg_replace('/(\{[a-zA-Z._ ]+\})/', ';;', $greetingLabel); | |
1672 | $stringsToBeReplaced = explode(';;', $stringsToBeReplaced); | |
1673 | foreach ($stringsToBeReplaced as $key => $string) { | |
1674 | // to keep one space | |
1675 | $stringsToBeReplaced[$key] = ltrim($string); | |
1676 | } | |
1677 | $parsedString = str_replace($stringsToBeReplaced, "", $parsedString); | |
1678 | ||
1679 | return $parsedString; | |
1680 | } | |
1681 | ||
e0ef6999 EM |
1682 | /** |
1683 | * @param $sql | |
100fef9d | 1684 | * @param array $exportParams |
e0ef6999 EM |
1685 | * @param bool $sharedAddress |
1686 | * | |
1687 | * @return array | |
1688 | */ | |
00be9182 | 1689 | public static function _buildMasterCopyArray($sql, $exportParams, $sharedAddress = FALSE) { |
6a488035 TO |
1690 | static $contactGreetingTokens = array(); |
1691 | ||
1692 | $addresseeOptions = CRM_Core_OptionGroup::values('addressee'); | |
1693 | $postalOptions = CRM_Core_OptionGroup::values('postal_greeting'); | |
1694 | ||
1695 | $merge = $parents = array(); | |
1696 | $dao = CRM_Core_DAO::executeQuery($sql); | |
1697 | ||
1698 | while ($dao->fetch()) { | |
1699 | $masterID = $dao->master_id; | |
1700 | $copyID = $dao->copy_id; | |
1701 | $masterPostalGreeting = $dao->master_postal_greeting; | |
1702 | $masterAddressee = $dao->master_addressee; | |
1703 | $copyAddressee = $dao->copy_addressee; | |
1704 | ||
1705 | if (!$sharedAddress) { | |
1706 | if (!isset($contactGreetingTokens[$dao->master_contact_id])) { | |
1707 | $contactGreetingTokens[$dao->master_contact_id] = self::_replaceMergeTokens($dao->master_contact_id, $exportParams); | |
1708 | } | |
1709 | $masterPostalGreeting = CRM_Utils_Array::value('postal_greeting', | |
1710 | $contactGreetingTokens[$dao->master_contact_id], $dao->master_postal_greeting | |
1711 | ); | |
1712 | $masterAddressee = CRM_Utils_Array::value('addressee', | |
1713 | $contactGreetingTokens[$dao->master_contact_id], $dao->master_addressee | |
1714 | ); | |
1715 | ||
1716 | if (!isset($contactGreetingTokens[$dao->copy_contact_id])) { | |
1717 | $contactGreetingTokens[$dao->copy_contact_id] = self::_replaceMergeTokens($dao->copy_contact_id, $exportParams); | |
1718 | } | |
1719 | $copyPostalGreeting = CRM_Utils_Array::value('postal_greeting', | |
1720 | $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_postal_greeting | |
1721 | ); | |
1722 | $copyAddressee = CRM_Utils_Array::value('addressee', | |
1723 | $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_addressee | |
1724 | ); | |
1725 | } | |
1726 | ||
1727 | if (!isset($merge[$masterID])) { | |
1728 | // check if this is an intermediate child | |
1729 | // this happens if there are 3 or more matches a,b, c | |
1730 | // the above query will return a, b / a, c / b, c | |
1731 | // we might be doing a bit more work, but for now its ok, unless someone | |
1732 | // knows how to fix the query above | |
1733 | if (isset($parents[$masterID])) { | |
1734 | $masterID = $parents[$masterID]; | |
1735 | } | |
1736 | else { | |
1737 | $merge[$masterID] = array( | |
1738 | 'addressee' => $masterAddressee, | |
1739 | 'copy' => array(), | |
1740 | 'postalGreeting' => $masterPostalGreeting, | |
1741 | ); | |
1742 | $merge[$masterID]['emailGreeting'] = &$merge[$masterID]['postalGreeting']; | |
1743 | } | |
1744 | } | |
1745 | $parents[$copyID] = $masterID; | |
1746 | ||
1747 | if (!$sharedAddress && !array_key_exists($copyID, $merge[$masterID]['copy'])) { | |
1748 | ||
a7488080 | 1749 | if (!empty($exportParams['postal_greeting_other']) && |
6a488035 TO |
1750 | count($merge[$masterID]['copy']) >= 1 |
1751 | ) { | |
1752 | // use static greetings specified if no of contacts > 2 | |
1753 | $merge[$masterID]['postalGreeting'] = $exportParams['postal_greeting_other']; | |
1754 | } | |
1755 | elseif ($copyPostalGreeting) { | |
1756 | self::_trimNonTokens($copyPostalGreeting, | |
1757 | $postalOptions[$dao->copy_postal_greeting_id], | |
1758 | $exportParams | |
1759 | ); | |
1760 | $merge[$masterID]['postalGreeting'] = "{$merge[$masterID]['postalGreeting']}, {$copyPostalGreeting}"; | |
1761 | // if there happens to be a duplicate, remove it | |
1762 | $merge[$masterID]['postalGreeting'] = str_replace(" {$copyPostalGreeting},", "", $merge[$masterID]['postalGreeting']); | |
1763 | } | |
1764 | ||
a7488080 | 1765 | if (!empty($exportParams['addressee_other']) && |
6a488035 TO |
1766 | count($merge[$masterID]['copy']) >= 1 |
1767 | ) { | |
1768 | // use static greetings specified if no of contacts > 2 | |
1769 | $merge[$masterID]['addressee'] = $exportParams['addressee_other']; | |
1770 | } | |
1771 | elseif ($copyAddressee) { | |
1772 | self::_trimNonTokens($copyAddressee, | |
1773 | $addresseeOptions[$dao->copy_addressee_id], | |
1774 | $exportParams, 'addressee' | |
1775 | ); | |
1776 | $merge[$masterID]['addressee'] = "{$merge[$masterID]['addressee']}, " . trim($copyAddressee); | |
1777 | } | |
1778 | } | |
1779 | $merge[$masterID]['copy'][$copyID] = $copyAddressee; | |
1780 | } | |
1781 | ||
1782 | return $merge; | |
1783 | } | |
1784 | ||
1785 | /** | |
100fef9d | 1786 | * Merge household record into the individual record |
6a488035 TO |
1787 | * if exists |
1788 | * | |
b9add4b3 TO |
1789 | * @param string $exportTempTable |
1790 | * Temporary temp table that stores the records. | |
1791 | * @param array $headerRows | |
1792 | * Array of headers for the export file. | |
1793 | * @param array $sqlColumns | |
1794 | * Array of names of the table columns of the temp table. | |
1795 | * @param string $prefix | |
1796 | * Name of the relationship type that is prefixed to the table columns. | |
6a488035 | 1797 | */ |
00be9182 | 1798 | public static function mergeSameHousehold($exportTempTable, &$headerRows, &$sqlColumns, $prefix) { |
6a488035 | 1799 | $prefixColumn = $prefix . '_'; |
97f6897c TO |
1800 | $allKeys = array_keys($sqlColumns); |
1801 | $replaced = array(); | |
1802 | $headerRows = array_values($headerRows); | |
6a488035 TO |
1803 | |
1804 | // name map of the non standard fields in header rows & sql columns | |
1805 | $mappingFields = array( | |
1806 | 'civicrm_primary_id' => 'id', | |
1807 | 'contact_source' => 'source', | |
1808 | 'current_employer_id' => 'employer_id', | |
1809 | 'contact_is_deleted' => 'is_deleted', | |
1810 | 'name' => 'address_name', | |
1811 | 'provider_id' => 'im_service_provider', | |
21dfd5f5 | 1812 | 'phone_type_id' => 'phone_type', |
6a488035 TO |
1813 | ); |
1814 | ||
1815 | //figure out which columns are to be replaced by which ones | |
1816 | foreach ($sqlColumns as $columnNames => $dontCare) { | |
1817 | if ($rep = CRM_Utils_Array::value($columnNames, $mappingFields)) { | |
1818 | $replaced[$columnNames] = CRM_Utils_String::munge($prefixColumn . $rep, '_', 64); | |
1819 | } | |
1820 | else { | |
1821 | $householdColName = CRM_Utils_String::munge($prefixColumn . $columnNames, '_', 64); | |
1822 | ||
a7488080 | 1823 | if (!empty($sqlColumns[$householdColName])) { |
6a488035 TO |
1824 | $replaced[$columnNames] = $householdColName; |
1825 | } | |
1826 | } | |
1827 | } | |
1828 | $query = "UPDATE $exportTempTable SET "; | |
1829 | ||
1830 | $clause = array(); | |
1831 | foreach ($replaced as $from => $to) { | |
1832 | $clause[] = "$from = $to "; | |
1833 | unset($sqlColumns[$to]); | |
1834 | if ($key = CRM_Utils_Array::key($to, $allKeys)) { | |
1835 | unset($headerRows[$key]); | |
1836 | } | |
1837 | } | |
1838 | $query .= implode(",\n", $clause); | |
1839 | $query .= " WHERE {$replaced['civicrm_primary_id']} != ''"; | |
1840 | ||
1841 | CRM_Core_DAO::executeQuery($query); | |
1842 | ||
1843 | //drop the table columns that store redundant household info | |
1844 | $dropQuery = "ALTER TABLE $exportTempTable "; | |
1845 | foreach ($replaced as $householdColumns) { | |
1846 | $dropClause[] = " DROP $householdColumns "; | |
1847 | } | |
1848 | $dropQuery .= implode(",\n", $dropClause); | |
1849 | ||
1850 | CRM_Core_DAO::executeQuery($dropQuery); | |
1851 | ||
1852 | // also drop the temp table if exists | |
1853 | $sql = "DROP TABLE IF EXISTS {$exportTempTable}_temp"; | |
1854 | CRM_Core_DAO::executeQuery($sql); | |
1855 | ||
1856 | // clean up duplicate records | |
1857 | $query = " | |
1858 | CREATE TABLE {$exportTempTable}_temp SELECT * | |
1859 | FROM {$exportTempTable} | |
1860 | GROUP BY civicrm_primary_id "; | |
1861 | ||
1862 | CRM_Core_DAO::executeQuery($query); | |
1863 | ||
1864 | $query = "DROP TABLE $exportTempTable"; | |
1865 | CRM_Core_DAO::executeQuery($query); | |
1866 | ||
1867 | $query = "ALTER TABLE {$exportTempTable}_temp RENAME TO {$exportTempTable}"; | |
1868 | CRM_Core_DAO::executeQuery($query); | |
1869 | } | |
1870 | ||
e0ef6999 EM |
1871 | /** |
1872 | * @param $exportTempTable | |
1873 | * @param $headerRows | |
1874 | * @param $sqlColumns | |
1875 | * @param $exportMode | |
1876 | * @param null $saveFile | |
1877 | * @param string $batchItems | |
1878 | */ | |
97f6897c | 1879 | public static function writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $exportMode, $saveFile = NULL, $batchItems = '') { |
6a488035 | 1880 | $writeHeader = TRUE; |
97f6897c TO |
1881 | $offset = 0; |
1882 | $limit = self::EXPORT_ROW_COUNT; | |
6a488035 TO |
1883 | |
1884 | $query = "SELECT * FROM $exportTempTable"; | |
1885 | ||
1886 | while (1) { | |
1887 | $limitQuery = $query . " | |
1888 | LIMIT $offset, $limit | |
1889 | "; | |
1890 | $dao = CRM_Core_DAO::executeQuery($limitQuery); | |
1891 | ||
1892 | if ($dao->N <= 0) { | |
1893 | break; | |
1894 | } | |
1895 | ||
1896 | $componentDetails = array(); | |
1897 | while ($dao->fetch()) { | |
1898 | $row = array(); | |
1899 | ||
1900 | foreach ($sqlColumns as $column => $dontCare) { | |
1901 | $row[$column] = $dao->$column; | |
1902 | } | |
1903 | $componentDetails[] = $row; | |
1904 | } | |
97f6897c | 1905 | if ($exportMode == 'financial') { |
6a488035 TO |
1906 | $getExportFileName = 'CiviCRM Contribution Search'; |
1907 | } | |
1908 | else { | |
97f6897c | 1909 | $getExportFileName = self::getExportFileName('csv', $exportMode); |
6a488035 | 1910 | } |
97f6897c | 1911 | $csvRows = CRM_Core_Report_Excel::writeCSVFile($getExportFileName, |
6a488035 TO |
1912 | $headerRows, |
1913 | $componentDetails, | |
97f6897c | 1914 | NULL, |
6a488035 | 1915 | $writeHeader, |
97f6897c | 1916 | $saveFile); |
6a488035 TO |
1917 | |
1918 | if ($saveFile && !empty($csvRows)) { | |
1919 | $batchItems .= $csvRows; | |
1920 | } | |
1921 | ||
97f6897c | 1922 | $writeHeader = FALSE; |
6a488035 TO |
1923 | $offset += $limit; |
1924 | } | |
1925 | } | |
1926 | ||
1927 | /** | |
fe482240 | 1928 | * Manipulate header rows for relationship fields. |
ca87146b EM |
1929 | * |
1930 | * @param $headerRows | |
1931 | * @param $contactRelationshipTypes | |
6a488035 | 1932 | */ |
1571c7e7 | 1933 | public static function manipulateHeaderRows(&$headerRows, $contactRelationshipTypes) { |
6a488035 TO |
1934 | foreach ($headerRows as & $header) { |
1935 | $split = explode('-', $header); | |
1936 | if ($relationTypeName = CRM_Utils_Array::value($split[0], $contactRelationshipTypes)) { | |
1937 | $split[0] = $relationTypeName; | |
1938 | $header = implode('-', $split); | |
1939 | } | |
1940 | } | |
1941 | } | |
1942 | ||
1943 | /** | |
100fef9d | 1944 | * Exclude contacts who are deceased, have "Do not mail" privacy setting, |
6a488035 | 1945 | * or have no street address |
ca87146b EM |
1946 | * @param $exportTempTable |
1947 | * @param $headerRows | |
1948 | * @param $sqlColumns | |
1949 | * @param $exportParams | |
6a488035 | 1950 | */ |
00be9182 | 1951 | public static function postalMailingFormat($exportTempTable, &$headerRows, &$sqlColumns, $exportParams) { |
6a488035 TO |
1952 | $whereClause = array(); |
1953 | ||
1954 | if (array_key_exists('is_deceased', $sqlColumns)) { | |
1955 | $whereClause[] = 'is_deceased = 1'; | |
1956 | } | |
1957 | ||
1958 | if (array_key_exists('do_not_mail', $sqlColumns)) { | |
1959 | $whereClause[] = 'do_not_mail = 1'; | |
1960 | } | |
1961 | ||
1962 | if (array_key_exists('street_address', $sqlColumns)) { | |
1963 | $addressWhereClause = " ( (street_address IS NULL) OR (street_address = '') ) "; | |
1964 | ||
1965 | // check for supplemental_address_1 | |
1966 | if (array_key_exists('supplemental_address_1', $sqlColumns)) { | |
1967 | $addressOptions = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, | |
1968 | 'address_options', TRUE, NULL, TRUE | |
1969 | ); | |
a7488080 | 1970 | if (!empty($addressOptions['supplemental_address_1'])) { |
6a488035 TO |
1971 | $addressWhereClause .= " AND ( (supplemental_address_1 IS NULL) OR (supplemental_address_1 = '') ) "; |
1972 | // enclose it again, since we are doing an AND in between a set of ORs | |
1973 | $addressWhereClause = "( $addressWhereClause )"; | |
1974 | } | |
1975 | } | |
1976 | ||
1977 | $whereClause[] = $addressWhereClause; | |
1978 | } | |
1979 | ||
1980 | if (!empty($whereClause)) { | |
1981 | $whereClause = implode(' OR ', $whereClause); | |
1982 | $query = " | |
1983 | DELETE | |
1984 | FROM $exportTempTable | |
1985 | WHERE {$whereClause}"; | |
1986 | CRM_Core_DAO::singleValueQuery($query); | |
1987 | } | |
1988 | ||
1989 | // unset temporary columns that were added for postal mailing format | |
1990 | if (!empty($exportParams['postal_mailing_export']['temp_columns'])) { | |
1991 | $unsetKeys = array_keys($sqlColumns); | |
1992 | foreach ($unsetKeys as $headerKey => $sqlColKey) { | |
1993 | if (array_key_exists($sqlColKey, $exportParams['postal_mailing_export']['temp_columns'])) { | |
1994 | unset($sqlColumns[$sqlColKey], $headerRows[$headerKey]); | |
1995 | } | |
1996 | } | |
1997 | } | |
1998 | } | |
d77aba4b AS |
1999 | |
2000 | /** | |
2001 | * Build componentPayment fields. | |
2002 | */ | |
00be9182 | 2003 | public static function componentPaymentFields() { |
d77aba4b | 2004 | static $componentPaymentFields; |
97f6897c | 2005 | if (!isset($componentPaymentFields)) { |
d77aba4b | 2006 | $componentPaymentFields = array( |
97f6897c | 2007 | 'componentPaymentField_total_amount' => ts('Total Amount'), |
d77aba4b | 2008 | 'componentPaymentField_contribution_status' => ts('Contribution Status'), |
7bc6b5bb | 2009 | 'componentPaymentField_received_date' => ts('Date Received'), |
536f0e02 | 2010 | 'componentPaymentField_payment_instrument' => ts('Payment Method'), |
97f6897c | 2011 | 'componentPaymentField_transaction_id' => ts('Transaction ID'), |
d77aba4b AS |
2012 | ); |
2013 | } | |
2014 | return $componentPaymentFields; | |
2015 | } | |
96025800 | 2016 | |
12a36993 | 2017 | /** |
2018 | * Set the definition for the header rows and sql columns based on the field to output. | |
2019 | * | |
2020 | * @param string $field | |
2021 | * @param array $headerRows | |
2022 | * @param array $sqlColumns | |
2023 | * Columns to go in the temp table. | |
2024 | * @param CRM_Contact_BAO_Query $query | |
2025 | * @param array|string $value | |
2026 | * @param array $phoneTypes | |
2027 | * @param array $imProviders | |
2028 | * @param array $contactRelationshipTypes | |
2029 | * @param string $relationQuery | |
2030 | * @param array $selectedPaymentFields | |
2031 | * @return array | |
2032 | */ | |
2033 | public static function setHeaderRows($field, $headerRows, $sqlColumns, $query, $value, $phoneTypes, $imProviders, $contactRelationshipTypes, $relationQuery, $selectedPaymentFields) { | |
2034 | ||
2035 | // Split campaign into 2 fields for id and title | |
2036 | if (substr($field, -14) == 'campaign_title') { | |
2037 | $headerRows[] = ts('Campaign Title'); | |
2038 | } | |
2039 | elseif (substr($field, -11) == 'campaign_id') { | |
2040 | $headerRows[] = ts('Campaign ID'); | |
2041 | } | |
2042 | elseif (isset($query->_fields[$field]['title'])) { | |
2043 | $headerRows[] = $query->_fields[$field]['title']; | |
2044 | } | |
2045 | elseif ($field == 'phone_type_id') { | |
2046 | $headerRows[] = ts('Phone Type'); | |
2047 | } | |
2048 | elseif ($field == 'provider_id') { | |
2049 | $headerRows[] = ts('IM Service Provider'); | |
2050 | } | |
2051 | elseif (substr($field, 0, 5) == 'case_') { | |
2052 | if ($query->_fields['case'][$field]['title']) { | |
2053 | $headerRows[] = $query->_fields['case'][$field]['title']; | |
2054 | } | |
2055 | elseif ($query->_fields['activity'][$field]['title']) { | |
2056 | $headerRows[] = $query->_fields['activity'][$field]['title']; | |
2057 | } | |
2058 | } | |
2059 | elseif (array_key_exists($field, $contactRelationshipTypes)) { | |
12a36993 | 2060 | foreach ($value as $relationField => $relationValue) { |
2061 | // below block is same as primary block (duplicate) | |
2062 | if (isset($relationQuery[$field]->_fields[$relationField]['title'])) { | |
2063 | if ($relationQuery[$field]->_fields[$relationField]['name'] == 'name') { | |
2064 | $headerName = $field . '-' . $relationField; | |
2065 | } | |
2066 | else { | |
2067 | if ($relationField == 'current_employer') { | |
2068 | $headerName = $field . '-' . 'current_employer'; | |
2069 | } | |
2070 | else { | |
2071 | $headerName = $field . '-' . $relationQuery[$field]->_fields[$relationField]['name']; | |
2072 | } | |
2073 | } | |
2074 | ||
2075 | $headerRows[] = $headerName; | |
2076 | ||
2077 | self::sqlColumnDefn($query, $sqlColumns, $headerName); | |
2078 | } | |
2079 | elseif ($relationField == 'phone_type_id') { | |
2080 | $headerName = $field . '-' . 'Phone Type'; | |
2081 | $headerRows[] = $headerName; | |
2082 | self::sqlColumnDefn($query, $sqlColumns, $headerName); | |
2083 | } | |
2084 | elseif ($relationField == 'provider_id') { | |
2085 | $headerName = $field . '-' . 'Im Service Provider'; | |
2086 | $headerRows[] = $headerName; | |
2087 | self::sqlColumnDefn($query, $sqlColumns, $headerName); | |
2088 | } | |
2089 | elseif ($relationField == 'state_province_id') { | |
2090 | $headerName = $field . '-' . 'state_province_id'; | |
2091 | $headerRows[] = $headerName; | |
2092 | self::sqlColumnDefn($query, $sqlColumns, $headerName); | |
2093 | } | |
2094 | elseif (is_array($relationValue) && $relationField == 'location') { | |
2095 | // fix header for location type case | |
2096 | foreach ($relationValue as $ltype => $val) { | |
2097 | foreach (array_keys($val) as $fld) { | |
2098 | $type = explode('-', $fld); | |
2099 | ||
2100 | $hdr = "{$ltype}-" . $relationQuery[$field]->_fields[$type[0]]['title']; | |
2101 | ||
2102 | if (!empty($type[1])) { | |
2103 | if (CRM_Utils_Array::value(0, $type) == 'phone') { | |
2104 | $hdr .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes); | |
2105 | } | |
2106 | elseif (CRM_Utils_Array::value(0, $type) == 'im') { | |
2107 | $hdr .= "-" . CRM_Utils_Array::value($type[1], $imProviders); | |
2108 | } | |
2109 | } | |
2110 | $headerName = $field . '-' . $hdr; | |
2111 | $headerRows[] = $headerName; | |
2112 | self::sqlColumnDefn($query, $sqlColumns, $headerName); | |
2113 | } | |
2114 | } | |
2115 | } | |
2116 | } | |
74045414 | 2117 | self::manipulateHeaderRows($headerRows, $contactRelationshipTypes); |
12a36993 | 2118 | } |
2119 | elseif ($selectedPaymentFields && array_key_exists($field, self::componentPaymentFields())) { | |
2120 | $headerRows[] = CRM_Utils_Array::value($field, self::componentPaymentFields()); | |
2121 | } | |
2122 | else { | |
2123 | $headerRows[] = $field; | |
2124 | } | |
2125 | ||
2126 | self::sqlColumnDefn($query, $sqlColumns, $field); | |
2127 | ||
2128 | return array($headerRows, $sqlColumns); | |
2129 | } | |
2130 | ||
2131 | /** | |
2132 | * Get the various arrays that we use to structure our output. | |
2133 | * | |
2134 | * The extraction of these has been moved to a separate function for clarity and so that | |
2135 | * tests can be added - in particular on the $outputHeaders array. | |
2136 | * | |
2137 | * However it still feels a bit like something that I'm too polite to write down and this should be seen | |
2138 | * as a step on the refactoring path rather than how it should be. | |
2139 | * | |
2140 | * @param array $returnProperties | |
2141 | * @param CRM_Contact_BAO_Contact $query | |
2142 | * @param array $phoneTypes | |
2143 | * @param array $imProviders | |
2144 | * @param array $contactRelationshipTypes | |
2145 | * @param string $relationQuery | |
2146 | * @param array $selectedPaymentFields | |
2147 | * @return array | |
2148 | * - outputColumns Array of columns to be exported. The values don't matter but the key must match the | |
2149 | * alias for the field generated by BAO_Query object. | |
2150 | * - headerRows Array of the column header strings to put in the csv header - non-associative. | |
2151 | * - sqlColumns Array of column names for the temp table. Not too sure why outputColumns can't be used here. | |
2152 | * - metadata Array of fields with specific parameters to pass to the translate function or another hacky nasty solution | |
2153 | * I'm too embarassed to discuss here. | |
2154 | * The keys need | |
2155 | * - to match the outputColumns keys (yes, the fact we ignore the output columns values & then pass another array with values | |
2156 | * we could use does suggest further refactors. However, you future improver, do remember that every check you do | |
2157 | * in the main DAO loop is done once per row & that coule be 100,000 times.) | |
2158 | * Finally a pop quiz: We need the translate context because we use a function other than ts() - is this because | |
2159 | * - a) the function used is more efficient or | |
2160 | * - b) this code is old & outdated. Submit your answers to circular bin or better | |
2161 | * yet find a way to comment them for posterity. | |
2162 | */ | |
2163 | public static function getExportStructureArrays($returnProperties, $query, $phoneTypes, $imProviders, $contactRelationshipTypes, $relationQuery, $selectedPaymentFields) { | |
2164 | $metadata = $headerRows = $outputColumns = $sqlColumns = array(); | |
2165 | ||
2166 | foreach ($returnProperties as $key => $value) { | |
2167 | if ($key != 'location' || !is_array($value)) { | |
2168 | $outputColumns[$key] = $value; | |
2169 | list($headerRows, $sqlColumns) = self::setHeaderRows($key, $headerRows, $sqlColumns, $query, $value, $phoneTypes, $imProviders, $contactRelationshipTypes, $relationQuery, $selectedPaymentFields); | |
2170 | } | |
2171 | else { | |
2172 | foreach ($value as $locationType => $locationFields) { | |
2173 | foreach (array_keys($locationFields) as $locationFieldName) { | |
2174 | $type = explode('-', $locationFieldName); | |
2175 | ||
2176 | $actualDBFieldName = $type[0]; | |
2177 | $outputFieldName = $locationType . '-' . $query->_fields[$actualDBFieldName]['title']; | |
2178 | $daoFieldName = CRM_Utils_String::munge($locationType) . '-' . $actualDBFieldName; | |
2179 | ||
2180 | if (!empty($type[1])) { | |
2181 | $daoFieldName .= "-" . $type[1]; | |
2182 | if ($actualDBFieldName == 'phone') { | |
2183 | $outputFieldName .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes); | |
2184 | } | |
2185 | elseif ($actualDBFieldName == 'im') { | |
2186 | $outputFieldName .= "-" . CRM_Utils_Array::value($type[1], $imProviders); | |
2187 | } | |
2188 | } | |
2189 | if ($type[0] == 'im_provider') { | |
2190 | // Warning: shame inducing hack. | |
2191 | $metadata[$daoFieldName]['pseudoconstant']['var'] = 'imProviders'; | |
2192 | } | |
2193 | self::sqlColumnDefn($query, $sqlColumns, $outputFieldName); | |
2194 | list($headerRows, $sqlColumns) = self::setHeaderRows($outputFieldName, $headerRows, $sqlColumns, $query, $value, $phoneTypes, $imProviders, $contactRelationshipTypes, $relationQuery, $selectedPaymentFields); | |
2195 | if ($actualDBFieldName == 'country' || $actualDBFieldName == 'world_region') { | |
2196 | $metadata[$daoFieldName] = array('context' => 'country'); | |
2197 | } | |
2198 | if ($actualDBFieldName == 'state_province') { | |
2199 | $metadata[$daoFieldName] = array('context' => 'province'); | |
2200 | } | |
2201 | $outputColumns[$daoFieldName] = TRUE; | |
2202 | } | |
2203 | } | |
2204 | } | |
2205 | } | |
2206 | return array($outputColumns, $headerRows, $sqlColumns, $metadata); | |
2207 | } | |
2208 | ||
6a488035 | 2209 | } |