Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
4 | | CiviCRM version 4.3 | | |
5 | +--------------------------------------------------------------------+ | |
6 | | Copyright CiviCRM LLC (c) 2004-2013 | | |
7 | +--------------------------------------------------------------------+ | |
8 | | This file is a part of CiviCRM. | | |
9 | | | | |
10 | | CiviCRM is free software; you can copy, modify, and distribute it | | |
11 | | under the terms of the GNU Affero General Public License | | |
12 | | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | | |
13 | | | | |
14 | | CiviCRM is distributed in the hope that it will be useful, but | | |
15 | | WITHOUT ANY WARRANTY; without even the implied warranty of | | |
16 | | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | | |
17 | | See the GNU Affero General Public License for more details. | | |
18 | | | | |
19 | | You should have received a copy of the GNU Affero General Public | | |
20 | | License and the CiviCRM Licensing Exception along | | |
21 | | with this program; if not, contact CiviCRM LLC | | |
22 | | at info[AT]civicrm[DOT]org. If you have questions about the | | |
23 | | GNU Affero General Public License or the licensing of CiviCRM, | | |
24 | | see the CiviCRM license FAQ at http://civicrm.org/licensing | | |
25 | +--------------------------------------------------------------------+ | |
26 | */ | |
27 | ||
28 | /** | |
29 | * | |
30 | * @package CRM | |
31 | * @copyright CiviCRM LLC (c) 2004-2013 | |
32 | * $Id$ | |
33 | * | |
34 | */ | |
35 | ||
36 | /** | |
37 | * This class contains the funtions for Component export | |
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 | |
44 | CONST EXPORT_ROW_COUNT = 10000; | |
45 | ||
46 | /** | |
47 | * Function to get the list the export fields | |
48 | * | |
49 | * @param int $selectAll user preference while export | |
50 | * @param array $ids contact ids | |
51 | * @param array $params associated array of fields | |
52 | * @param string $order order by clause | |
53 | * @param array $fields associated array of fields | |
54 | * @param array $moreReturnProperties additional return fields | |
55 | * @param int $exportMode export mode | |
56 | * @param string $componentClause component clause | |
57 | * @param string $componentTable component table | |
58 | * @param bool $mergeSameAddress merge records if they have same address | |
59 | * @param bool $mergeSameHousehold merge records if they belong to the same household | |
60 | * | |
61 | * @static | |
62 | * @access public | |
63 | */ | |
64 | static function exportComponents($selectAll, | |
65 | $ids, | |
66 | $params, | |
67 | $order = NULL, | |
68 | $fields = NULL, | |
69 | $moreReturnProperties = NULL, | |
70 | $exportMode = CRM_Export_Form_Select::CONTACT_EXPORT, | |
71 | $componentClause = NULL, | |
72 | $componentTable = NULL, | |
73 | $mergeSameAddress = FALSE, | |
74 | $mergeSameHousehold = FALSE, | |
75 | $exportParams = array(), | |
76 | $queryOperator = 'AND' | |
77 | ) { | |
78 | $headerRows = $returnProperties = array(); | |
d77aba4b | 79 | $primary = $paymentFields = $selectedPaymentFields = FALSE; |
6a488035 TO |
80 | $origFields = $fields; |
81 | $queryMode = $relationField = NULL; | |
82 | ||
83 | $allCampaigns = array(); | |
84 | $exportCampaign = FALSE; | |
85 | ||
86 | $phoneTypes = CRM_Core_PseudoConstant::phoneType(); | |
87 | $imProviders = CRM_Core_PseudoConstant::IMProvider(); | |
88 | $contactRelationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType( | |
89 | NULL, | |
90 | NULL, | |
91 | NULL, | |
92 | NULL, | |
93 | TRUE, | |
545285b8 | 94 | 'name', |
6a488035 TO |
95 | FALSE |
96 | ); | |
97 | $queryMode = CRM_Contact_BAO_Query::MODE_CONTACTS; | |
98 | ||
99 | switch ($exportMode) { | |
100 | case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: | |
101 | $queryMode = CRM_Contact_BAO_Query::MODE_CONTRIBUTE; | |
102 | break; | |
103 | ||
104 | case CRM_Export_Form_Select::EVENT_EXPORT: | |
105 | $queryMode = CRM_Contact_BAO_Query::MODE_EVENT; | |
106 | break; | |
107 | ||
108 | case CRM_Export_Form_Select::MEMBER_EXPORT: | |
109 | $queryMode = CRM_Contact_BAO_Query::MODE_MEMBER; | |
110 | break; | |
111 | ||
112 | case CRM_Export_Form_Select::PLEDGE_EXPORT: | |
113 | $queryMode = CRM_Contact_BAO_Query::MODE_PLEDGE; | |
114 | break; | |
115 | ||
116 | case CRM_Export_Form_Select::CASE_EXPORT: | |
117 | $queryMode = CRM_Contact_BAO_Query::MODE_CASE; | |
118 | break; | |
119 | ||
120 | case CRM_Export_Form_Select::GRANT_EXPORT: | |
121 | $queryMode = CRM_Contact_BAO_Query::MODE_GRANT; | |
122 | break; | |
123 | ||
124 | case CRM_Export_Form_Select::ACTIVITY_EXPORT: | |
125 | $queryMode = CRM_Contact_BAO_Query::MODE_ACTIVITY; | |
126 | break; | |
127 | } | |
128 | if ($fields) { | |
129 | //construct return properties | |
130 | $locationTypes = CRM_Core_PseudoConstant::locationType(); | |
131 | $locationTypeFields = array( | |
132 | 'street_address', | |
133 | 'supplemental_address_1', | |
134 | 'supplemental_address_2', | |
135 | 'city', | |
136 | 'postal_code', | |
137 | 'postal_code_suffix', | |
138 | 'geo_code_1', | |
139 | 'geo_code_2', | |
140 | 'state_province', | |
141 | 'country', | |
142 | 'phone', | |
143 | 'email', | |
144 | 'im', | |
145 | ); | |
146 | ||
147 | foreach ($fields as $key => $value) { | |
148 | $phoneTypeId = $imProviderId = NULL; | |
149 | $relationshipTypes = $fieldName = CRM_Utils_Array::value(1, $value); | |
150 | if (!$fieldName) { | |
151 | continue; | |
152 | } | |
153 | // get phoneType id and IM service provider id seperately | |
154 | if ($fieldName == 'phone') { | |
155 | $phoneTypeId = CRM_Utils_Array::value(3, $value); | |
156 | } | |
157 | elseif ($fieldName == 'im') { | |
158 | $imProviderId = CRM_Utils_Array::value(3, $value); | |
159 | } | |
160 | elseif (substr($fieldName, -8) == 'campaign') { | |
161 | $exportCampaign = TRUE; | |
162 | } | |
163 | ||
164 | if (array_key_exists($relationshipTypes, $contactRelationshipTypes)) { | |
165 | if (CRM_Utils_Array::value(2, $value)) { | |
166 | $relationField = CRM_Utils_Array::value(2, $value); | |
167 | if (trim(CRM_Utils_Array::value(3, $value))) { | |
168 | $relLocTypeId = CRM_Utils_Array::value(3, $value); | |
169 | } | |
170 | else { | |
171 | $relLocTypeId = 'Primary'; | |
172 | } | |
173 | ||
174 | if ($relationField == 'phone') { | |
175 | $relPhoneTypeId = CRM_Utils_Array::value(4, $value); | |
176 | } | |
177 | elseif ($relationField == 'im') { | |
178 | $relIMProviderId = CRM_Utils_Array::value(4, $value); | |
179 | } | |
180 | } | |
181 | elseif (CRM_Utils_Array::value(4, $value)) { | |
182 | $relationField = CRM_Utils_Array::value(4, $value); | |
183 | $relLocTypeId = CRM_Utils_Array::value(5, $value); | |
184 | if ($relationField == 'phone') { | |
185 | $relPhoneTypeId = CRM_Utils_Array::value(6, $value); | |
186 | } | |
187 | elseif ($relationField == 'im') { | |
188 | $relIMProviderId = CRM_Utils_Array::value(6, $value); | |
189 | } | |
190 | } | |
191 | } | |
192 | ||
193 | $contactType = CRM_Utils_Array::value(0, $value); | |
194 | $locTypeId = CRM_Utils_Array::value(2, $value); | |
195 | ||
196 | if ($relationField) { | |
197 | if (in_array($relationField, $locationTypeFields) && is_numeric($relLocTypeId)) { | |
198 | if ($relPhoneTypeId) { | |
199 | $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]]['phone-' . $relPhoneTypeId] = 1; | |
200 | } | |
201 | elseif ($relIMProviderId) { | |
202 | $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]]['im-' . $relIMProviderId] = 1; | |
203 | } | |
204 | else { | |
205 | $returnProperties[$relationshipTypes]['location'][$locationTypes[$relLocTypeId]][$relationField] = 1; | |
206 | } | |
207 | $relPhoneTypeId = $relIMProviderId = NULL; | |
208 | } | |
209 | else { | |
210 | $returnProperties[$relationshipTypes][$relationField] = 1; | |
211 | } | |
212 | } | |
213 | elseif (is_numeric($locTypeId)) { | |
214 | if ($phoneTypeId) { | |
215 | $returnProperties['location'][$locationTypes[$locTypeId]]['phone-' . $phoneTypeId] = 1; | |
216 | } | |
217 | elseif ($imProviderId) { | |
218 | $returnProperties['location'][$locationTypes[$locTypeId]]['im-' . $imProviderId] = 1; | |
219 | } | |
220 | else { | |
221 | $returnProperties['location'][$locationTypes[$locTypeId]][$fieldName] = 1; | |
222 | } | |
223 | } | |
224 | else { | |
225 | //hack to fix component fields | |
226 | if ($fieldName == 'event_id') { | |
227 | $returnProperties['event_title'] = 1; | |
228 | } | |
d77aba4b | 229 | else if ( |
545285b8 | 230 | $exportMode == CRM_Export_Form_Select::EVENT_EXPORT && |
d77aba4b | 231 | array_key_exists( $fieldName, self::componentPaymentFields()) |
545285b8 | 232 | ) { |
d77aba4b AS |
233 | $selectedPaymentFields = true; |
234 | $paymentTableId = 'participant_id'; | |
545285b8 | 235 | $returnProperties[$fieldName] = 1; |
d77aba4b | 236 | } |
6a488035 TO |
237 | else { |
238 | $returnProperties[$fieldName] = 1; | |
239 | ||
240 | //campaign field export. | |
241 | if (substr($fieldName, -8) == 'campaign') { | |
242 | $fldNames = explode('_', $fieldName); | |
243 | $returnProperties["{$fldNames[0]}_campaign_id"] = 1; | |
244 | } | |
245 | } | |
246 | } | |
247 | } | |
248 | ||
249 | // hack to add default returnproperty based on export mode | |
250 | if ($exportMode == CRM_Export_Form_Select::CONTRIBUTE_EXPORT) { | |
251 | $returnProperties['contribution_id'] = 1; | |
252 | } | |
253 | elseif ($exportMode == CRM_Export_Form_Select::EVENT_EXPORT) { | |
254 | $returnProperties['participant_id'] = 1; | |
255 | if (CRM_Utils_Array::value('participant_role', $returnProperties)) { | |
256 | unset($returnProperties['participant_role']); | |
257 | $returnProperties['participant_role_id'] = 1; | |
258 | } | |
259 | } | |
260 | elseif ($exportMode == CRM_Export_Form_Select::MEMBER_EXPORT) { | |
261 | $returnProperties['membership_id'] = 1; | |
262 | } | |
263 | elseif ($exportMode == CRM_Export_Form_Select::PLEDGE_EXPORT) { | |
264 | $returnProperties['pledge_id'] = 1; | |
265 | } | |
266 | elseif ($exportMode == CRM_Export_Form_Select::CASE_EXPORT) { | |
267 | $returnProperties['case_id'] = 1; | |
268 | } | |
269 | elseif ($exportMode == CRM_Export_Form_Select::GRANT_EXPORT) { | |
270 | $returnProperties['grant_id'] = 1; | |
271 | } | |
272 | elseif ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) { | |
273 | $returnProperties['activity_id'] = 1; | |
274 | } | |
275 | } | |
276 | else { | |
277 | $primary = TRUE; | |
278 | $fields = CRM_Contact_BAO_Contact::exportableFields('All', TRUE, TRUE); | |
279 | foreach ($fields as $key => $var) { | |
280 | if ($key && (substr($key, 0, 6) != 'custom')) { | |
281 | //for CRM=952 | |
282 | $returnProperties[$key] = 1; | |
283 | } | |
284 | } | |
285 | ||
286 | if ($primary) { | |
287 | $returnProperties['location_type'] = 1; | |
288 | $returnProperties['im_provider'] = 1; | |
289 | $returnProperties['phone_type_id'] = 1; | |
290 | $returnProperties['provider_id'] = 1; | |
291 | $returnProperties['current_employer'] = 1; | |
292 | } | |
293 | ||
294 | $extraReturnProperties = array(); | |
295 | $paymentFields = FALSE; | |
296 | ||
297 | switch ($queryMode) { | |
298 | case CRM_Contact_BAO_Query::MODE_EVENT: | |
299 | $paymentFields = TRUE; | |
300 | $paymentTableId = 'participant_id'; | |
301 | break; | |
302 | ||
303 | case CRM_Contact_BAO_Query::MODE_MEMBER: | |
304 | $paymentFields = TRUE; | |
305 | $paymentTableId = 'membership_id'; | |
306 | break; | |
307 | ||
308 | case CRM_Contact_BAO_Query::MODE_PLEDGE: | |
309 | $extraReturnProperties = CRM_Pledge_BAO_Query::extraReturnProperties($queryMode); | |
310 | $paymentFields = TRUE; | |
311 | $paymentTableId = 'pledge_payment_id'; | |
312 | break; | |
313 | ||
314 | case CRM_Contact_BAO_Query::MODE_CASE: | |
315 | $extraReturnProperties = CRM_Case_BAO_Query::extraReturnProperties($queryMode); | |
316 | break; | |
317 | } | |
318 | ||
319 | if ($queryMode != CRM_Contact_BAO_Query::MODE_CONTACTS) { | |
320 | $componentReturnProperties = CRM_Contact_BAO_Query::defaultReturnProperties($queryMode); | |
321 | ||
322 | $campaignReturnProperties = array();; | |
323 | foreach ($componentReturnProperties as $fld => $true) { | |
324 | $campaignReturnProperties[$fld] = $true; | |
325 | if (substr($fld, -11) == 'campaign_id') { | |
326 | $exportCampaign = TRUE; | |
327 | $campaignReturnProperties[substr($fld, 0, -3)] = 1; | |
328 | } | |
329 | } | |
330 | $componentReturnProperties = $campaignReturnProperties; | |
331 | ||
332 | $returnProperties = array_merge($returnProperties, $componentReturnProperties); | |
333 | ||
334 | if (!empty($extraReturnProperties)) { | |
335 | $returnProperties = array_merge($returnProperties, $extraReturnProperties); | |
336 | } | |
337 | ||
338 | // unset non exportable fields for components | |
339 | $nonExpoFields = array( | |
340 | 'groups', 'tags', 'notes', 'contribution_status_id', | |
341 | 'pledge_status_id', 'pledge_payment_status_id', | |
342 | ); | |
343 | foreach ($nonExpoFields as $value) { | |
344 | unset($returnProperties[$value]); | |
345 | } | |
346 | } | |
347 | } | |
348 | ||
349 | if ($mergeSameAddress) { | |
350 | //make sure the addressee fields are selected | |
351 | //while using merge same address feature | |
352 | $returnProperties['addressee'] = 1; | |
353 | $returnProperties['postal_greeting'] = 1; | |
354 | $returnProperties['email_greeting'] = 1; | |
355 | $returnProperties['street_name'] = 1; | |
356 | $returnProperties['household_name'] = 1; | |
357 | $returnProperties['street_address'] = 1; | |
358 | $returnProperties['city'] = 1; | |
359 | $returnProperties['state_province'] = 1; | |
360 | ||
361 | // some columns are required for assistance incase they are not already present | |
362 | $exportParams['merge_same_address']['temp_columns'] = array(); | |
363 | $tempColumns = array('id', 'master_id', 'state_province_id', 'postal_greeting_id', 'addressee_id'); | |
364 | foreach ($tempColumns as $column) { | |
365 | if (!array_key_exists($column, $returnProperties)) { | |
366 | $returnProperties[$column] = 1; | |
367 | $column = $column == 'id' ? 'civicrm_primary_id' : $column; | |
368 | $exportParams['merge_same_address']['temp_columns'][$column] = 1; | |
369 | } | |
370 | } | |
371 | } | |
372 | ||
373 | if ($componentTable && | |
374 | CRM_Utils_Array::value('additional_group', $exportParams) | |
375 | ) { | |
376 | // If an Additional Group is selected, then all contacts in that group are | |
377 | // added to the export set (filtering out duplicates). | |
378 | $query = " | |
379 | 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"; | |
380 | CRM_Core_DAO::executeQuery($query); | |
381 | } | |
382 | ||
383 | if ($moreReturnProperties) { | |
384 | // fix for CRM-7066 | |
385 | if (CRM_Utils_Array::value('group', $moreReturnProperties)) { | |
386 | unset($moreReturnProperties['group']); | |
387 | $moreReturnProperties['groups'] = 1; | |
388 | } | |
389 | $returnProperties = array_merge($returnProperties, $moreReturnProperties); | |
390 | } | |
391 | ||
392 | $exportParams['postal_mailing_export']['temp_columns'] = array(); | |
393 | if ($exportParams['exportOption'] == 2 && | |
394 | isset($exportParams['postal_mailing_export']) && | |
395 | CRM_Utils_Array::value('postal_mailing_export', $exportParams['postal_mailing_export']) == 1 | |
396 | ) { | |
397 | $postalColumns = array('is_deceased', 'do_not_mail', 'street_address', 'supplemental_address_1'); | |
398 | foreach ($postalColumns as $column) { | |
399 | if (!array_key_exists($column, $returnProperties)) { | |
400 | $returnProperties[$column] = 1; | |
401 | $exportParams['postal_mailing_export']['temp_columns'][$column] = 1; | |
402 | } | |
403 | } | |
404 | } | |
405 | ||
406 | $query = new CRM_Contact_BAO_Query(NULL, $returnProperties, NULL, | |
407 | FALSE, FALSE, $queryMode, | |
408 | FALSE, TRUE, TRUE, NULL, $queryOperator | |
409 | ); | |
410 | ||
411 | list($select, $from, $where, $having) = $query->query(); | |
412 | ||
413 | if ($mergeSameHousehold == 1) { | |
414 | if (!$returnProperties['id']) { | |
415 | $returnProperties['id'] = 1; | |
416 | } | |
417 | ||
418 | //also merge Head of Household | |
419 | $relationKeyMOH = CRM_Utils_Array::key('Household Member of', $contactRelationshipTypes); | |
420 | $relationKeyHOH = CRM_Utils_Array::key('Head of Household for', $contactRelationshipTypes); | |
421 | ||
422 | foreach ($returnProperties as $key => $value) { | |
423 | if (!array_key_exists($key, $contactRelationshipTypes)) { | |
424 | $returnProperties[$relationKeyMOH][$key] = $value; | |
425 | $returnProperties[$relationKeyHOH][$key] = $value; | |
426 | } | |
427 | } | |
428 | ||
429 | unset($returnProperties[$relationKeyMOH]['location_type']); | |
430 | unset($returnProperties[$relationKeyMOH]['im_provider']); | |
431 | unset($returnProperties[$relationKeyHOH]['location_type']); | |
432 | unset($returnProperties[$relationKeyHOH]['im_provider']); | |
433 | } | |
434 | ||
435 | $allRelContactArray = $relationQuery = array(); | |
436 | ||
437 | foreach ($contactRelationshipTypes as $rel => $dnt) { | |
438 | if ($relationReturnProperties = CRM_Utils_Array::value($rel, $returnProperties)) { | |
439 | $allRelContactArray[$rel] = array(); | |
440 | // build Query for each relationship | |
441 | $relationQuery[$rel] = new CRM_Contact_BAO_Query(NULL, $relationReturnProperties, | |
442 | NULL, FALSE, FALSE, $queryMode | |
443 | ); | |
444 | list($relationSelect, $relationFrom, $relationWhere, $relationHaving) = $relationQuery[$rel]->query(); | |
445 | ||
446 | list($id, $direction) = explode('_', $rel, 2); | |
447 | // identify the relationship direction | |
448 | $contactA = 'contact_id_a'; | |
449 | $contactB = 'contact_id_b'; | |
450 | if ($direction == 'b_a') { | |
451 | $contactA = 'contact_id_b'; | |
452 | $contactB = 'contact_id_a'; | |
453 | } | |
454 | if ($exportMode == CRM_Export_Form_Select::CONTACT_EXPORT) { | |
455 | $relIDs = $ids; | |
456 | } | |
457 | elseif ($exportMode == CRM_Export_Form_Select::ACTIVITY_EXPORT) { | |
458 | $query = "SELECT source_contact_id FROM civicrm_activity | |
459 | WHERE id IN ( " . implode(',', $ids) . ")"; | |
460 | $dao = CRM_Core_DAO::executeQuery($query); | |
461 | while ($dao->fetch()) { | |
462 | $relIDs[] = $dao->source_contact_id; | |
463 | } | |
464 | } | |
465 | else { | |
466 | switch ($exportMode) { | |
467 | case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: | |
468 | $component = 'civicrm_contribution'; | |
469 | break; | |
470 | ||
471 | case CRM_Export_Form_Select::EVENT_EXPORT: | |
472 | $component = 'civicrm_participant'; | |
473 | break; | |
474 | ||
475 | case CRM_Export_Form_Select::MEMBER_EXPORT: | |
476 | $component = 'civicrm_membership'; | |
477 | break; | |
478 | ||
479 | case CRM_Export_Form_Select::PLEDGE_EXPORT: | |
480 | $component = 'civicrm_pledge'; | |
481 | break; | |
482 | ||
483 | case CRM_Export_Form_Select::CASE_EXPORT: | |
484 | $component = 'civicrm_case'; | |
485 | break; | |
486 | ||
487 | case CRM_Export_Form_Select::GRANT_EXPORT: | |
488 | $component = 'civicrm_grant'; | |
489 | break; | |
490 | } | |
491 | $relIDs = CRM_Core_DAO::getContactIDsFromComponent($ids, $component); | |
492 | } | |
493 | ||
494 | $relationshipJoin = $relationshipClause = ''; | |
495 | if ($componentTable) { | |
496 | $relationshipJoin = " INNER JOIN $componentTable ctTable ON ctTable.contact_id = {$contactA}"; | |
497 | } | |
498 | else { | |
499 | $relID = implode(',', $relIDs); | |
500 | $relationshipClause = " AND crel.{$contactA} IN ( {$relID} )"; | |
501 | } | |
502 | ||
503 | $relationFrom = " {$relationFrom} | |
504 | INNER JOIN civicrm_relationship crel ON crel.{$contactB} = contact_a.id AND crel.relationship_type_id = {$id} | |
505 | {$relationshipJoin} "; | |
506 | ||
507 | //check for active relationship status only | |
508 | $today = date('Ymd'); | |
509 | $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )"; | |
510 | $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause} {$relationActive}"; | |
511 | $relationGroupBy = " GROUP BY crel.{$contactA}"; | |
512 | $relationSelect = "{$relationSelect}, {$contactA} as refContact "; | |
513 | $relationQueryString = "$relationSelect $relationFrom $relationWhere $relationHaving $relationGroupBy"; | |
514 | ||
515 | $allRelContactDAO = CRM_Core_DAO::executeQuery($relationQueryString); | |
516 | while ($allRelContactDAO->fetch()) { | |
517 | //FIX Me: Migrate this to table rather than array | |
518 | // build the array of all related contacts | |
519 | $allRelContactArray[$rel][$allRelContactDAO->refContact] = clone($allRelContactDAO); | |
520 | } | |
521 | $allRelContactDAO->free(); | |
522 | } | |
523 | } | |
524 | ||
525 | // make sure the groups stuff is included only if specifically specified | |
526 | // by the fields param (CRM-1969), else we limit the contacts outputted to only | |
527 | // ones that are part of a group | |
528 | if (CRM_Utils_Array::value('groups', $returnProperties)) { | |
529 | $oldClause = "( contact_a.id = civicrm_group_contact.contact_id )"; | |
530 | $newClause = " ( $oldClause AND ( civicrm_group_contact.status = 'Added' OR civicrm_group_contact.status IS NULL ) )"; | |
531 | // total hack for export, CRM-3618 | |
532 | $from = str_replace($oldClause, | |
533 | $newClause, | |
534 | $from | |
535 | ); | |
536 | } | |
537 | ||
538 | if ($componentTable) { | |
539 | $from .= " INNER JOIN $componentTable ctTable ON ctTable.contact_id = contact_a.id "; | |
540 | } | |
541 | elseif ($componentClause) { | |
542 | if (empty($where)) { | |
543 | $where = "WHERE $componentClause"; | |
544 | } | |
545 | else { | |
546 | $where .= " AND $componentClause"; | |
547 | } | |
548 | } | |
549 | ||
550 | $queryString = "$select $from $where $having"; | |
551 | ||
552 | $groupBy = ""; | |
553 | if (CRM_Utils_Array::value('tags', $returnProperties) || | |
554 | CRM_Utils_Array::value('groups', $returnProperties) || | |
555 | CRM_Utils_Array::value('notes', $returnProperties) || | |
556 | // CRM-9552 | |
557 | ($queryMode & CRM_Contact_BAO_Query::MODE_CONTACTS && $query->_useGroupBy) | |
558 | ) { | |
559 | $groupBy = " GROUP BY contact_a.id"; | |
560 | } | |
561 | ||
562 | if ($queryMode & CRM_Contact_BAO_Query::MODE_ACTIVITY) { | |
563 | $groupBy = " GROUP BY civicrm_activity.id "; | |
564 | } | |
565 | $queryString .= $groupBy; | |
566 | if ($order) { | |
567 | list($field, $dir) = explode(' ', $order, 2); | |
568 | $field = trim($field); | |
569 | if (CRM_Utils_Array::value($field, $returnProperties)) { | |
570 | // $queryString .= " ORDER BY $order"; | |
571 | } | |
572 | } | |
573 | ||
574 | $multipleSelectFields = array('preferred_communication_method' => 1); | |
575 | ||
576 | $addPaymentHeader = FALSE; | |
577 | ||
578 | $paymentDetails = array(); | |
d77aba4b | 579 | if ($paymentFields || $selectedPaymentFields) { |
6a488035 TO |
580 | |
581 | // get payment related in for event and members | |
582 | $paymentDetails = CRM_Contribute_BAO_Contribution::getContributionDetails($exportMode, $ids); | |
d77aba4b | 583 | //get all payment headers. |
7460c131 AS |
584 | // If we haven't selected specific payment fields, load in all the |
585 | // payment headers. | |
d77aba4b AS |
586 | if (!$selectedPaymentFields) { |
587 | $paymentHeaders = self::componentPaymentFields(); | |
588 | if (!empty($paymentDetails)) { | |
589 | $addPaymentHeader = TRUE; | |
590 | } | |
6a488035 | 591 | } |
7460c131 AS |
592 | // If we have seleted specific payment fields, leave the payment headers |
593 | // as an empty array; the headers for each selected field will be added | |
594 | // elsewhere. | |
595 | else { | |
596 | $paymentHeaders = array(); | |
597 | } | |
6a488035 TO |
598 | $nullContributionDetails = array_fill_keys(array_keys($paymentHeaders), NULL); |
599 | } | |
600 | ||
601 | //get all campaigns. | |
602 | if ($exportCampaign) { | |
603 | $allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE); | |
604 | } | |
605 | ||
606 | $componentDetails = $headerRows = $sqlColumns = array(); | |
607 | $setHeader = TRUE; | |
608 | ||
609 | $rowCount = self::EXPORT_ROW_COUNT; | |
610 | $offset = 0; | |
611 | // we write to temp table often to avoid using too much memory | |
612 | $tempRowCount = 100; | |
613 | ||
614 | $count = -1; | |
615 | ||
616 | // for CRM-3157 purposes | |
617 | $i18n = CRM_Core_I18n::singleton(); | |
618 | ||
619 | while (1) { | |
620 | $limitQuery = "{$queryString} LIMIT {$offset}, {$rowCount}"; | |
621 | $dao = CRM_Core_DAO::executeQuery($limitQuery); | |
622 | if ($dao->N <= 0) { | |
623 | break; | |
624 | } | |
625 | ||
626 | while ($dao->fetch()) { | |
627 | $count++; | |
628 | $row = array(); | |
629 | ||
630 | //first loop through returnproperties so that we return what is required, and in same order. | |
631 | $relationshipField = 0; | |
632 | foreach ($returnProperties as $field => $value) { | |
633 | //we should set header only once | |
634 | if ($setHeader) { | |
635 | $sqlDone = FALSE; | |
636 | if (isset($query->_fields[$field]['title'])) { | |
637 | $headerRows[] = $query->_fields[$field]['title']; | |
638 | } | |
639 | elseif ($field == 'phone_type_id') { | |
640 | $headerRows[] = ts('Phone Type'); | |
641 | } | |
642 | elseif ($field == 'provider_id') { | |
643 | $headerRows[] = ts('IM Service Provider'); | |
644 | } | |
645 | elseif (is_array($value) && $field == 'location') { | |
646 | // fix header for location type case | |
647 | foreach ($value as $ltype => $val) { | |
648 | foreach (array_keys($val) as $fld) { | |
649 | $type = explode('-', $fld); | |
650 | $hdr = "{$ltype}-" . $query->_fields[$type[0]]['title']; | |
651 | ||
652 | if (CRM_Utils_Array::value(1, $type)) { | |
653 | if (CRM_Utils_Array::value(0, $type) == 'phone') { | |
654 | $hdr .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes); | |
655 | } | |
656 | elseif (CRM_Utils_Array::value(0, $type) == 'im') { | |
657 | $hdr .= "-" . CRM_Utils_Array::value($type[1], $imProviders); | |
658 | } | |
659 | } | |
660 | $headerRows[] = $hdr; | |
661 | self::sqlColumnDefn($query, $sqlColumns, $hdr); | |
662 | } | |
663 | $sqlDone = TRUE; | |
664 | } | |
665 | } | |
666 | elseif (substr($field, 0, 5) == 'case_') { | |
667 | if ($query->_fields['case'][$field]['title']) { | |
668 | $headerRows[] = $query->_fields['case'][$field]['title']; | |
669 | } | |
670 | elseif ($query->_fields['activity'][$field]['title']) { | |
671 | $headerRows[] = $query->_fields['activity'][$field]['title']; | |
672 | } | |
673 | } | |
674 | elseif (array_key_exists($field, $contactRelationshipTypes)) { | |
675 | $relName = $field; | |
676 | foreach ($value as $relationField => $relationValue) { | |
677 | // below block is same as primary block (duplicate) | |
678 | if (isset($relationQuery[$field]->_fields[$relationField]['title'])) { | |
679 | if ($relationQuery[$field]->_fields[$relationField]['name'] == 'name') { | |
680 | $headerName = $field . '-' . $relationField; | |
681 | } | |
682 | else { | |
683 | if ($relationField == 'current_employer') { | |
684 | $headerName = $field . '-' . 'current_employer'; | |
685 | } | |
686 | else { | |
687 | $headerName = $field . '-' . $relationQuery[$field]->_fields[$relationField]['name']; | |
688 | } | |
689 | } | |
690 | ||
691 | $headerRows[] = $headerName; | |
692 | ||
693 | self::sqlColumnDefn($query, $sqlColumns, $headerName); | |
694 | } | |
695 | elseif ($relationField == 'phone_type_id') { | |
696 | $headerName = $field . '-' . 'Phone Type'; | |
697 | $headerRows[] = $headerName; | |
698 | self::sqlColumnDefn($query, $sqlColumns, $headerName); | |
699 | } | |
700 | elseif ($relationField == 'provider_id') { | |
701 | $headerName = $field . '-' . 'Im Service Provider'; | |
702 | $headerRows[] = $headerName; | |
703 | self::sqlColumnDefn($query, $sqlColumns, $headerName); | |
704 | } | |
705 | elseif ($relationField == 'state_province_id') { | |
706 | $headerName = $field . '-' . 'state_province_id'; | |
707 | $headerRows[] = $headerName; | |
708 | self::sqlColumnDefn($query, $sqlColumns, $headerName); | |
709 | } | |
710 | elseif (is_array($relationValue) && $relationField == 'location') { | |
711 | // fix header for location type case | |
712 | foreach ($relationValue as $ltype => $val) { | |
713 | foreach (array_keys($val) as $fld) { | |
714 | $type = explode('-', $fld); | |
715 | ||
716 | $hdr = "{$ltype}-" . $relationQuery[$field]->_fields[$type[0]]['title']; | |
717 | ||
718 | if (CRM_Utils_Array::value(1, $type)) { | |
719 | if (CRM_Utils_Array::value(0, $type) == 'phone') { | |
720 | $hdr .= "-" . CRM_Utils_Array::value($type[1], $phoneTypes); | |
721 | } | |
722 | elseif (CRM_Utils_Array::value(0, $type) == 'im') { | |
723 | $hdr .= "-" . CRM_Utils_Array::value($type[1], $imProviders); | |
724 | } | |
725 | } | |
726 | $headerName = $field . '-' . $hdr; | |
727 | $headerRows[] = $headerName; | |
728 | self::sqlColumnDefn($query, $sqlColumns, $headerName); | |
729 | } | |
730 | } | |
731 | } | |
732 | } | |
733 | } | |
d77aba4b AS |
734 | elseif ($selectedPaymentFields && array_key_exists($field, self::componentPaymentFields())) { |
735 | $headerRows[] = CRM_Utils_Array::value($field, self::componentPaymentFields()); | |
736 | } | |
6a488035 TO |
737 | else { |
738 | $headerRows[] = $field; | |
739 | } | |
740 | ||
741 | if (!$sqlDone) { | |
742 | self::sqlColumnDefn($query, $sqlColumns, $field); | |
743 | } | |
744 | } | |
745 | ||
746 | // add im_provider to $dao object | |
747 | if ($field == 'im_provider' && property_exists($dao, 'provider_id')) { | |
748 | $dao->im_provider = $dao->provider_id; | |
749 | } | |
750 | ||
751 | //build row values (data) | |
752 | $fieldValue = NULL; | |
753 | if (property_exists($dao, $field)) { | |
754 | $fieldValue = $dao->$field; | |
755 | // to get phone type from phone type id | |
756 | if ($field == 'phone_type_id' && isset($phoneTypes[$fieldValue])) { | |
757 | $fieldValue = $phoneTypes[$fieldValue]; | |
758 | } | |
759 | elseif ($field == 'provider_id' || $field == 'im_provider') { | |
760 | $fieldValue = CRM_Utils_Array::value($fieldValue, $imProviders); | |
761 | } | |
762 | elseif ($field == 'participant_role_id') { | |
763 | $participantRoles = CRM_Event_PseudoConstant::participantRole(); | |
764 | $sep = CRM_Core_DAO::VALUE_SEPARATOR; | |
765 | $viewRoles = array(); | |
766 | foreach (explode($sep, $dao->$field) as $k => $v) { | |
767 | $viewRoles[] = $participantRoles[$v]; | |
768 | } | |
769 | $fieldValue = implode(',', $viewRoles); | |
770 | } | |
771 | elseif ($field == 'master_id') { | |
772 | $masterAddressId = NULL; | |
773 | if (isset($dao->master_id)) { | |
774 | $masterAddressId = $dao->master_id; | |
775 | } | |
776 | // get display name of contact that address is shared. | |
777 | $fieldValue = CRM_Contact_BAO_Contact::getMasterDisplayName($masterAddressId, $dao->contact_id); | |
778 | } | |
779 | } | |
780 | ||
781 | if ($field == 'id') { | |
782 | $row[$field] = $dao->contact_id; | |
783 | // special case for calculated field | |
784 | } | |
785 | elseif ($field == 'pledge_balance_amount') { | |
786 | $row[$field] = $dao->pledge_amount - $dao->pledge_total_paid; | |
787 | // special case for calculated field | |
788 | } | |
789 | elseif ($field == 'pledge_next_pay_amount') { | |
790 | $row[$field] = $dao->pledge_next_pay_amount + $dao->pledge_outstanding_amount; | |
791 | } | |
792 | elseif (is_array($value) && $field == 'location') { | |
793 | // fix header for location type case | |
794 | foreach ($value as $ltype => $val) { | |
795 | foreach (array_keys($val) as $fld) { | |
796 | $type = explode('-', $fld); | |
797 | $fldValue = "{$ltype}-" . $type[0]; | |
798 | ||
799 | if (CRM_Utils_Array::value(1, $type)) { | |
800 | $fldValue .= "-" . $type[1]; | |
801 | } | |
802 | ||
803 | // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context) | |
804 | switch ($fld) { | |
805 | case 'country': | |
806 | case 'world_region': | |
807 | $row[$fldValue] = $i18n->crm_translate($dao->$fldValue, array('context' => 'country')); | |
808 | break; | |
809 | ||
810 | case 'state_province': | |
811 | $row[$fldValue] = $i18n->crm_translate($dao->$fldValue, array('context' => 'province')); | |
812 | break; | |
813 | ||
814 | case 'im_provider': | |
815 | $imFieldvalue = $fldValue . "-provider_id"; | |
816 | $row[$fldValue] = CRM_Utils_Array::value($dao->$imFieldvalue, $imProviders); | |
817 | break; | |
818 | ||
819 | default: | |
820 | $row[$fldValue] = $dao->$fldValue; | |
821 | break; | |
822 | } | |
823 | } | |
824 | } | |
825 | } | |
826 | elseif (array_key_exists($field, $contactRelationshipTypes)) { | |
827 | $relDAO = CRM_Utils_Array::value($dao->contact_id, $allRelContactArray[$field]); | |
828 | foreach ($value as $relationField => $relationValue) { | |
829 | if (is_object($relDAO) && property_exists($relDAO, $relationField)) { | |
830 | $fieldValue = $relDAO->$relationField; | |
831 | if ($relationField == 'phone_type_id') { | |
832 | $fieldValue = $phoneTypes[$relationValue]; | |
833 | } | |
834 | elseif ($relationField == 'provider_id') { | |
835 | $fieldValue = CRM_Utils_Array::value($relationValue, $imProviders); | |
836 | } | |
837 | } | |
838 | else { | |
839 | $fieldValue = ''; | |
840 | } | |
841 | $field = $field . '_'; | |
842 | if (is_object($relDAO) && $relationField == 'id') { | |
843 | $row[$field . $relationField] = $relDAO->contact_id; | |
844 | } else if ( is_object( $relDAO ) && is_array( $relationValue ) && $relationField == 'location' ) { | |
845 | foreach ($relationValue as $ltype => $val) { | |
846 | foreach (array_keys($val) as $fld) { | |
847 | $type = explode('-', $fld); | |
848 | $fldValue = "{$ltype}-" . $type[0]; | |
849 | if (CRM_Utils_Array::value(1, $type)) { | |
850 | $fldValue .= "-" . $type[1]; | |
851 | } | |
852 | // CRM-3157: localise country, region (both have ‘country’ context) | |
853 | // and state_province (‘province’ context) | |
854 | switch (TRUE) { | |
855 | case (!is_object($relDAO)): | |
856 | $row[$field . '_' . $fldValue] = ''; | |
857 | break; | |
858 | ||
859 | case in_array('country', $type): | |
860 | case in_array('world_region', $type): | |
861 | $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue, | |
862 | array('context' => 'country') | |
863 | ); | |
864 | break; | |
865 | ||
866 | case in_array('state_province', $type): | |
867 | $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue, | |
868 | array('context' => 'province') | |
869 | ); | |
870 | break; | |
871 | ||
872 | default: | |
873 | $row[$field . '_' . $fldValue] = $relDAO->$fldValue; | |
874 | break; | |
875 | } | |
876 | } | |
877 | } | |
878 | } | |
879 | elseif (isset($fieldValue) && $fieldValue != '') { | |
880 | //check for custom data | |
881 | if ($cfID = CRM_Core_BAO_CustomField::getKeyID($relationField)) { | |
882 | $row[$field . $relationField] = CRM_Core_BAO_CustomField::getDisplayValue($fieldValue, $cfID, | |
883 | $relationQuery[$field]->_options | |
884 | ); | |
885 | } | |
886 | elseif (in_array($relationField, array( | |
887 | 'email_greeting', 'postal_greeting', 'addressee'))) { | |
888 | //special case for greeting replacement | |
889 | $fldValue = "{$relationField}_display"; | |
890 | $row[$field . $relationField] = $relDAO->$fldValue; | |
891 | } | |
892 | else { | |
893 | //normal relationship fields | |
894 | // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context) | |
895 | switch ($relationField) { | |
896 | case 'country': | |
897 | case 'world_region': | |
898 | $row[$field . $relationField] = $i18n->crm_translate($fieldValue, array('context' => 'country')); | |
899 | break; | |
900 | ||
901 | case 'state_province': | |
902 | $row[$field . $relationField] = $i18n->crm_translate($fieldValue, array('context' => 'province')); | |
903 | break; | |
904 | ||
905 | default: | |
906 | $row[$field . $relationField] = $fieldValue; | |
907 | break; | |
908 | } | |
909 | } | |
910 | } | |
911 | else { | |
912 | // if relation field is empty or null | |
913 | $row[$field . $relationField] = ''; | |
914 | } | |
915 | } | |
916 | } | |
917 | elseif (isset($fieldValue) && | |
918 | $fieldValue != '' | |
919 | ) { | |
920 | //check for custom data | |
921 | if ($cfID = CRM_Core_BAO_CustomField::getKeyID($field)) { | |
922 | $row[$field] = CRM_Core_BAO_CustomField::getDisplayValue($fieldValue, $cfID, $query->_options); | |
923 | } | |
924 | elseif (array_key_exists($field, $multipleSelectFields)) { | |
925 | //option group fixes | |
926 | $paramsNew = array($field => $fieldValue); | |
927 | if ($field == 'test_tutoring') { | |
928 | $name = array($field => array('newName' => $field, 'groupName' => 'test')); | |
929 | // for readers group | |
930 | } | |
931 | elseif (substr($field, 0, 4) == 'cmr_') { | |
932 | $name = array($field => array('newName' => $field, 'groupName' => substr($field, 0, -3))); | |
933 | } | |
934 | else { | |
935 | $name = array($field => array('newName' => $field, 'groupName' => $field)); | |
936 | } | |
937 | CRM_Core_OptionGroup::lookupValues($paramsNew, $name, FALSE); | |
938 | $row[$field] = $paramsNew[$field]; | |
939 | } | |
940 | elseif (in_array($field, array( | |
941 | 'email_greeting', 'postal_greeting', 'addressee'))) { | |
942 | //special case for greeting replacement | |
943 | $fldValue = "{$field}_display"; | |
944 | $row[$field] = $dao->$fldValue; | |
945 | } | |
946 | else { | |
947 | //normal fields with a touch of CRM-3157 | |
948 | switch ($field) { | |
949 | case 'country': | |
950 | case 'world_region': | |
951 | $row[$field] = $i18n->crm_translate($fieldValue, array('context' => 'country')); | |
952 | break; | |
953 | ||
954 | case 'state_province': | |
955 | $row[$field] = $i18n->crm_translate($fieldValue, array('context' => 'province')); | |
956 | break; | |
957 | ||
958 | case 'gender': | |
959 | case 'preferred_communication_method': | |
960 | case 'preferred_mail_format': | |
961 | $row[$field] = $i18n->crm_translate($fieldValue); | |
962 | break; | |
963 | ||
964 | default: | |
965 | $row[$field] = $fieldValue; | |
966 | break; | |
967 | } | |
968 | } | |
969 | } | |
970 | elseif (substr($field, -8) == 'campaign') { | |
971 | $campIdFld = "{$field}_id"; | |
972 | $row[$field] = CRM_Utils_Array::value($dao->$campIdFld, $allCampaigns, ''); | |
973 | } | |
d77aba4b AS |
974 | elseif ($selectedPaymentFields && array_key_exists($field, self::componentPaymentFields())) { |
975 | $paymentData = CRM_Utils_Array::value($dao->$paymentTableId, $paymentDetails); | |
976 | $payFieldMapper = array( | |
977 | 'componentPaymentField_total_amount' => 'total_amount', | |
978 | 'componentPaymentField_contribution_status' => 'contribution_status', | |
979 | 'componentPaymentField_payment_instrument' => 'pay_instru', | |
980 | 'componentPaymentField_transaction_id' => 'trxn_id', | |
981 | 'componentPaymentField_received_date' => 'receive_date', | |
982 | ); | |
983 | $row[$field] = CRM_Utils_Array::value($payFieldMapper[$field], $paymentData, ''); | |
984 | } | |
6a488035 TO |
985 | else { |
986 | // if field is empty or null | |
987 | $row[$field] = ''; | |
988 | } | |
989 | } | |
990 | ||
991 | // add payment headers if required | |
992 | if ($addPaymentHeader && $paymentFields) { | |
993 | $headerRows = array_merge($headerRows, $paymentHeaders); | |
994 | foreach (array_keys($paymentHeaders) as $paymentHdr) { | |
995 | self::sqlColumnDefn($query, $sqlColumns, $paymentHdr); | |
996 | } | |
997 | $addPaymentHeader = FALSE; | |
998 | } | |
999 | ||
1000 | if ($setHeader) { | |
1001 | $exportTempTable = self::createTempTable($sqlColumns); | |
1002 | } | |
1003 | ||
1004 | //build header only once | |
1005 | $setHeader = FALSE; | |
1006 | ||
e026db3c | 1007 | // If specific payment fields have been selected for export, payment |
545285b8 | 1008 | // data will already be in $row. Otherwise, add payment related |
e026db3c AS |
1009 | // information, if appropriate. |
1010 | if (!$selectedPaymentFields) { | |
1011 | if ($paymentFields) { | |
1012 | $paymentData = CRM_Utils_Array::value($row[$paymentTableId], $paymentDetails); | |
1013 | if (!is_array($paymentData) || empty($paymentData)) { | |
1014 | $paymentData = $nullContributionDetails; | |
1015 | } | |
1016 | $row = array_merge($row, $paymentData); | |
d77aba4b | 1017 | } |
6a488035 | 1018 | elseif (!empty($paymentDetails)) { |
e026db3c AS |
1019 | $row = array_merge($row, $nullContributionDetails); |
1020 | } | |
6a488035 TO |
1021 | } |
1022 | ||
1023 | //remove organization name for individuals if it is set for current employer | |
1024 | if (CRM_Utils_Array::value('contact_type', $row) && | |
1025 | $row['contact_type'] == 'Individual' && array_key_exists('organization_name', $row) | |
1026 | ) { | |
1027 | $row['organization_name'] = ''; | |
1028 | } | |
1029 | ||
1030 | // add component info | |
1031 | // write the row to a file | |
1032 | $componentDetails[] = $row; | |
1033 | ||
1034 | // output every $tempRowCount rows | |
1035 | if ($count % $tempRowCount == 0) { | |
1036 | self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); | |
1037 | $componentDetails = array(); | |
1038 | } | |
1039 | } | |
1040 | $dao->free(); | |
1041 | $offset += $rowCount; | |
1042 | } | |
1043 | ||
1044 | if ($exportTempTable) { | |
1045 | self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); | |
1046 | ||
1047 | // do merge same address and merge same household processing | |
1048 | if ($mergeSameAddress) { | |
1049 | self::mergeSameAddress($exportTempTable, $headerRows, $sqlColumns, $exportParams); | |
1050 | } | |
1051 | ||
1052 | // merge the records if they have corresponding households | |
1053 | if ($mergeSameHousehold) { | |
1054 | self::mergeSameHousehold($exportTempTable, $headerRows, $sqlColumns, $relationKeyMOH); | |
1055 | self::mergeSameHousehold($exportTempTable, $headerRows, $sqlColumns, $relationKeyHOH); | |
1056 | } | |
1057 | ||
1058 | // fix the headers for rows with relationship type | |
1059 | if (!empty($relName)) { | |
1060 | self::manipulateHeaderRows($headerRows, $contactRelationshipTypes); | |
1061 | } | |
1062 | ||
1063 | // if postalMailing option is checked, exclude contacts who are deceased, have | |
1064 | // "Do not mail" privacy setting, or have no street address | |
1065 | if (isset($exportParams['postal_mailing_export']['postal_mailing_export']) && | |
1066 | $exportParams['postal_mailing_export']['postal_mailing_export'] == 1 | |
1067 | ) { | |
1068 | self::postalMailingFormat($exportTempTable, $headerRows, $sqlColumns, $exportMode); | |
1069 | } | |
1070 | ||
1071 | // call export hook | |
1072 | CRM_Utils_Hook::export($exportTempTable, $headerRows, $sqlColumns, $exportMode); | |
1073 | ||
1074 | // now write the CSV file | |
1075 | self::writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $exportMode); | |
1076 | ||
1077 | // delete the export temp table and component table | |
1078 | $sql = "DROP TABLE IF EXISTS {$exportTempTable}"; | |
1079 | CRM_Core_DAO::executeQuery($sql); | |
1080 | ||
1081 | CRM_Utils_System::civiExit(); | |
1082 | } | |
1083 | else { | |
1084 | CRM_Core_Error::fatal(ts('No records to export')); | |
1085 | } | |
1086 | } | |
1087 | ||
1088 | /** | |
1089 | * name of the export file based on mode | |
1090 | * | |
1091 | * @param string $output type of output | |
1092 | * @param int $mode export mode | |
1093 | * | |
1094 | * @return string name of the file | |
1095 | */ | |
1096 | function getExportFileName($output = 'csv', $mode = CRM_Export_Form_Select::CONTACT_EXPORT) { | |
1097 | switch ($mode) { | |
1098 | case CRM_Export_Form_Select::CONTACT_EXPORT: | |
1099 | return ts('CiviCRM Contact Search'); | |
1100 | ||
1101 | case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: | |
1102 | return ts('CiviCRM Contribution Search'); | |
1103 | ||
1104 | case CRM_Export_Form_Select::MEMBER_EXPORT: | |
1105 | return ts('CiviCRM Member Search'); | |
1106 | ||
1107 | case CRM_Export_Form_Select::EVENT_EXPORT: | |
1108 | return ts('CiviCRM Participant Search'); | |
1109 | ||
1110 | case CRM_Export_Form_Select::PLEDGE_EXPORT: | |
1111 | return ts('CiviCRM Pledge Search'); | |
1112 | ||
1113 | case CRM_Export_Form_Select::CASE_EXPORT: | |
1114 | return ts('CiviCRM Case Search'); | |
1115 | ||
1116 | case CRM_Export_Form_Select::GRANT_EXPORT: | |
1117 | return ts('CiviCRM Grant Search'); | |
1118 | ||
1119 | case CRM_Export_Form_Select::ACTIVITY_EXPORT: | |
1120 | return ts('CiviCRM Activity Search'); | |
1121 | } | |
1122 | } | |
1123 | ||
1124 | /** | |
1125 | * Function to handle import error file creation. | |
1126 | * | |
1127 | */ | |
1128 | function invoke() { | |
1129 | $type = CRM_Utils_Request::retrieve('type', 'Positive', CRM_Core_DAO::$_nullObject); | |
1130 | $parserName = CRM_Utils_Request::retrieve('parser', 'String', CRM_Core_DAO::$_nullObject); | |
1131 | if (empty($parserName) || empty($type)) { | |
1132 | return; | |
1133 | } | |
1134 | ||
1135 | // clean and ensure parserName is a valid string | |
1136 | $parserName = CRM_Utils_String::munge($parserName); | |
1137 | $parserClass = explode('_', $parserName); | |
1138 | ||
1139 | // make sure parserClass is in the CRM namespace and | |
1140 | // at least 3 levels deep | |
1141 | if ($parserClass[0] == 'CRM' && | |
1142 | count($parserClass) >= 3 | |
1143 | ) { | |
1144 | require_once (str_replace('_', DIRECTORY_SEPARATOR, $parserName) . ".php"); | |
1145 | // ensure the functions exists | |
1146 | if (method_exists($parserName, 'errorFileName') && | |
1147 | method_exists($parserName, 'saveFileName') | |
1148 | ) { | |
1149 | $errorFileName = $parserName::errorFileName($type); | |
1150 | $saveFileName = $parserName::saveFileName($type); | |
1151 | if (!empty($errorFileName) && !empty($saveFileName)) { | |
1152 | header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); | |
1153 | header('Content-Description: File Transfer'); | |
1154 | header('Content-Type: text/csv'); | |
1155 | header('Content-Length: ' . filesize($errorFileName)); | |
1156 | header('Content-Disposition: attachment; filename=' . $saveFileName); | |
1157 | ||
1158 | readfile($errorFileName); | |
1159 | } | |
1160 | } | |
1161 | } | |
1162 | CRM_Utils_System::civiExit(); | |
1163 | } | |
1164 | ||
1165 | function exportCustom($customSearchClass, $formValues, $order) { | |
1166 | $ext = CRM_Extension_System::singleton()->getMapper(); | |
1167 | if (!$ext->isExtensionClass($customSearchClass)) { | |
1168 | require_once (str_replace('_', DIRECTORY_SEPARATOR, $customSearchClass) . '.php'); | |
1169 | } | |
1170 | else { | |
1171 | require_once ($ext->classToPath($customSearchClass)); | |
1172 | } | |
1173 | $search = new $customSearchClass($formValues); | |
1174 | ||
1175 | $includeContactIDs = FALSE; | |
1176 | if ($formValues['radio_ts'] == 'ts_sel') { | |
1177 | $includeContactIDs = TRUE; | |
1178 | } | |
1179 | ||
1180 | $sql = $search->all(0, 0, $order, $includeContactIDs); | |
1181 | ||
1182 | $columns = $search->columns(); | |
1183 | ||
1184 | $header = array_keys($columns); | |
1185 | $fields = array_values($columns); | |
1186 | ||
1187 | $rows = array(); | |
1188 | $dao = CRM_Core_DAO::executeQuery($sql); | |
1189 | $alterRow = FALSE; | |
1190 | if (method_exists($search, 'alterRow')) { | |
1191 | $alterRow = TRUE; | |
1192 | } | |
1193 | while ($dao->fetch()) { | |
1194 | $row = array(); | |
1195 | ||
1196 | foreach ($fields as $field) { | |
1197 | $row[$field] = $dao->$field; | |
1198 | } | |
1199 | if ($alterRow) { | |
1200 | $search->alterRow($row); | |
1201 | } | |
1202 | $rows[] = $row; | |
1203 | } | |
1204 | ||
1205 | CRM_Core_Report_Excel::writeCSVFile(self::getExportFileName(), $header, $rows); | |
1206 | CRM_Utils_System::civiExit(); | |
1207 | } | |
1208 | ||
1209 | static function sqlColumnDefn(&$query, &$sqlColumns, $field) { | |
0944820d | 1210 | if (substr($field, -4) == '_a_b' || substr($field, -4) == '_b_a') { |
6a488035 TO |
1211 | return; |
1212 | } | |
1213 | ||
1214 | $fieldName = CRM_Utils_String::munge(strtolower($field), '_', 64); | |
1215 | if ($fieldName == 'id') { | |
1216 | $fieldName = 'civicrm_primary_id'; | |
1217 | } | |
1218 | ||
0944820d DL |
1219 | // early exit for master_id, CRM-12100 |
1220 | // in the DB it is an ID, but in the export, we retrive the display_name of the master record | |
1221 | if ($fieldName == 'master_id') { | |
1222 | $sqlColumns[$fieldName] = "$fieldName varchar(128)"; | |
1223 | return; | |
1224 | } | |
1225 | ||
6a488035 TO |
1226 | // set the sql columns |
1227 | if (isset($query->_fields[$field]['type'])) { | |
1228 | switch ($query->_fields[$field]['type']) { | |
1229 | case CRM_Utils_Type::T_INT: | |
1230 | case CRM_Utils_Type::T_BOOL: | |
1231 | case CRM_Utils_Type::T_BOOLEAN: | |
1232 | $sqlColumns[$fieldName] = "$fieldName varchar(16)"; | |
1233 | break; | |
1234 | ||
1235 | case CRM_Utils_Type::T_STRING: | |
1236 | if (isset($query->_fields[$field]['maxlength'])) { | |
1237 | $sqlColumns[$fieldName] = "$fieldName varchar({$query->_fields[$field]['maxlength']})"; | |
1238 | } | |
1239 | else { | |
1240 | $sqlColumns[$fieldName] = "$fieldName varchar(64)"; | |
1241 | } | |
1242 | break; | |
1243 | ||
1244 | case CRM_Utils_Type::T_TEXT: | |
1245 | case CRM_Utils_Type::T_LONGTEXT: | |
1246 | case CRM_Utils_Type::T_BLOB: | |
1247 | case CRM_Utils_Type::T_MEDIUMBLOB: | |
1248 | $sqlColumns[$fieldName] = "$fieldName longtext"; | |
1249 | break; | |
1250 | ||
1251 | case CRM_Utils_Type::T_FLOAT: | |
1252 | case CRM_Utils_Type::T_ENUM: | |
1253 | case CRM_Utils_Type::T_DATE: | |
1254 | case CRM_Utils_Type::T_TIME: | |
1255 | case CRM_Utils_Type::T_TIMESTAMP: | |
1256 | case CRM_Utils_Type::T_MONEY: | |
1257 | case CRM_Utils_Type::T_EMAIL: | |
1258 | case CRM_Utils_Type::T_URL: | |
1259 | case CRM_Utils_Type::T_CCNUM: | |
1260 | default: | |
1261 | $sqlColumns[$fieldName] = "$fieldName varchar(32)"; | |
1262 | break; | |
1263 | } | |
1264 | } | |
1265 | else { | |
1266 | if (substr($fieldName, -3, 3) == '_id') { | |
1267 | $sqlColumns[$fieldName] = "$fieldName varchar(16)"; | |
1268 | } | |
1269 | else { | |
1270 | $changeFields = array( | |
1271 | 'groups', | |
1272 | 'tags', | |
1273 | 'notes' | |
1274 | ); | |
1275 | ||
1276 | if (in_array($fieldName, $changeFields)) { | |
1277 | $sqlColumns[$fieldName] = "$fieldName text"; | |
1278 | } | |
1279 | else { | |
1280 | // set the sql columns for custom data | |
1281 | if (isset($query->_fields[$field]['data_type'])) { | |
1282 | ||
1283 | switch ($query->_fields[$field]['data_type']) { | |
1284 | case 'Country': | |
1285 | case 'StateProvince': | |
1286 | case 'Link': | |
1287 | case 'String': | |
1288 | $sqlColumns[$fieldName] = "$fieldName varchar(255)"; | |
1289 | break; | |
1290 | ||
1291 | case 'Memo': | |
1292 | $sqlColumns[$fieldName] = "$fieldName text"; | |
1293 | break; | |
1294 | ||
1295 | default: | |
1296 | $sqlColumns[$fieldName] = "$fieldName varchar(64)"; | |
1297 | break; | |
1298 | } | |
1299 | } | |
1300 | else { | |
1301 | $sqlColumns[$fieldName] = "$fieldName varchar(64)"; | |
1302 | } | |
1303 | } | |
1304 | } | |
1305 | } | |
1306 | } | |
1307 | ||
1308 | static function writeDetailsToTable($tableName, &$details, &$sqlColumns) { | |
1309 | if (empty($details)) { | |
1310 | return; | |
1311 | } | |
1312 | ||
1313 | $sql = " | |
1314 | SELECT max(id) | |
1315 | FROM $tableName | |
1316 | "; | |
1317 | ||
1318 | $id = CRM_Core_DAO::singleValueQuery($sql); | |
1319 | if (!$id) { | |
1320 | $id = 0; | |
1321 | } | |
1322 | ||
1323 | $sqlClause = array(); | |
1324 | ||
1325 | foreach ($details as $dontCare => $row) { | |
1326 | $id++; | |
1327 | $valueString = array($id); | |
1328 | foreach ($row as $dontCare => $value) { | |
1329 | if (empty($value)) { | |
1330 | $valueString[] = "''"; | |
1331 | } | |
1332 | else { | |
1333 | $valueString[] = "'" . CRM_Core_DAO::escapeString($value) . "'"; | |
1334 | } | |
1335 | } | |
1336 | $sqlClause[] = '(' . implode(',', $valueString) . ')'; | |
1337 | } | |
1338 | ||
1339 | $sqlColumnString = '(id, ' . implode(',', array_keys($sqlColumns)) . ')'; | |
1340 | ||
1341 | $sqlValueString = implode(",\n", $sqlClause); | |
1342 | ||
1343 | $sql = " | |
1344 | INSERT INTO $tableName $sqlColumnString | |
1345 | VALUES $sqlValueString | |
1346 | "; | |
1347 | ||
1348 | CRM_Core_DAO::executeQuery($sql); | |
1349 | } | |
1350 | ||
1351 | static function createTempTable(&$sqlColumns) { | |
1352 | //creating a temporary table for the search result that need be exported | |
1353 | $exportTempTable = CRM_Core_DAO::createTempTableName('civicrm_export', TRUE); | |
1354 | ||
1355 | // also create the sql table | |
1356 | $sql = "DROP TABLE IF EXISTS {$exportTempTable}"; | |
1357 | CRM_Core_DAO::executeQuery($sql); | |
1358 | ||
1359 | $sql = " | |
1360 | CREATE TABLE {$exportTempTable} ( | |
1361 | id int unsigned NOT NULL AUTO_INCREMENT, | |
1362 | "; | |
1363 | $sql .= implode(",\n", array_values($sqlColumns)); | |
1364 | ||
1365 | $sql .= ", | |
1366 | PRIMARY KEY ( id ) | |
1367 | "; | |
1368 | // add indexes for street_address and household_name if present | |
1369 | $addIndices = array( | |
1370 | 'street_address', | |
1371 | 'household_name', | |
1372 | 'civicrm_primary_id', | |
1373 | ); | |
1374 | ||
1375 | foreach ($addIndices as $index) { | |
1376 | if (isset($sqlColumns[$index])) { | |
1377 | $sql .= ", | |
1378 | INDEX index_{$index}( $index ) | |
1379 | "; | |
1380 | } | |
1381 | } | |
1382 | ||
1383 | $sql .= " | |
1384 | ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci | |
1385 | "; | |
1386 | ||
1387 | CRM_Core_DAO::executeQuery($sql); | |
1388 | return $exportTempTable; | |
1389 | } | |
1390 | ||
1391 | static function mergeSameAddress($tableName, &$headerRows, &$sqlColumns, $exportParams) { | |
1392 | // check if any records are present based on if they have used shared address feature, | |
1393 | // and not based on if city / state .. matches. | |
1394 | $sql = " | |
1395 | SELECT r1.id as copy_id, | |
1396 | r1.civicrm_primary_id as copy_contact_id, | |
1397 | r1.addressee as copy_addressee, | |
1398 | r1.addressee_id as copy_addressee_id, | |
1399 | r1.postal_greeting as copy_postal_greeting, | |
1400 | r1.postal_greeting_id as copy_postal_greeting_id, | |
1401 | r2.id as master_id, | |
1402 | r2.civicrm_primary_id as master_contact_id, | |
1403 | r2.postal_greeting as master_postal_greeting, | |
1404 | r2.postal_greeting_id as master_postal_greeting_id, | |
1405 | r2.addressee as master_addressee, | |
1406 | r2.addressee_id as master_addressee_id | |
1407 | FROM $tableName r1 | |
1408 | INNER JOIN civicrm_address adr ON r1.master_id = adr.id | |
1409 | INNER JOIN $tableName r2 ON adr.contact_id = r2.civicrm_primary_id | |
1410 | ORDER BY r1.id"; | |
1411 | $linkedMerge = self::_buildMasterCopyArray($sql, $exportParams, TRUE); | |
1412 | ||
1413 | // find all the records that have the same street address BUT not in a household | |
1414 | // require match on city and state as well | |
1415 | $sql = " | |
1416 | SELECT r1.id as master_id, | |
1417 | r1.civicrm_primary_id as master_contact_id, | |
1418 | r1.postal_greeting as master_postal_greeting, | |
1419 | r1.postal_greeting_id as master_postal_greeting_id, | |
1420 | r1.addressee as master_addressee, | |
1421 | r1.addressee_id as master_addressee_id, | |
1422 | r2.id as copy_id, | |
1423 | r2.civicrm_primary_id as copy_contact_id, | |
1424 | r2.postal_greeting as copy_postal_greeting, | |
1425 | r2.postal_greeting_id as copy_postal_greeting_id, | |
1426 | r2.addressee as copy_addressee, | |
1427 | r2.addressee_id as copy_addressee_id | |
1428 | FROM $tableName r1 | |
1429 | LEFT JOIN $tableName r2 ON ( r1.street_address = r2.street_address AND | |
1430 | r1.city = r2.city AND | |
1431 | r1.state_province_id = r2.state_province_id ) | |
1432 | WHERE ( r1.household_name IS NULL OR r1.household_name = '' ) | |
1433 | AND ( r2.household_name IS NULL OR r2.household_name = '' ) | |
1434 | AND ( r1.street_address != '' ) | |
1435 | AND r2.id > r1.id | |
1436 | ORDER BY r1.id | |
1437 | "; | |
1438 | $merge = self::_buildMasterCopyArray($sql, $exportParams); | |
1439 | ||
1440 | // unset ids from $merge already present in $linkedMerge | |
1441 | foreach ($linkedMerge as $masterID => $values) { | |
1442 | $keys = array($masterID); | |
1443 | $keys = array_merge($keys, array_keys($values['copy'])); | |
1444 | foreach ($merge as $mid => $vals) { | |
1445 | if (in_array($mid, $keys)) { | |
1446 | unset($merge[$mid]); | |
1447 | } | |
1448 | else { | |
1449 | foreach ($values['copy'] as $copyId) { | |
1450 | if (in_array($copyId, $keys)) { | |
1451 | unset($merge[$mid]['copy'][$copyId]); | |
1452 | } | |
1453 | } | |
1454 | } | |
1455 | } | |
1456 | } | |
1457 | $merge = $merge + $linkedMerge; | |
1458 | ||
1459 | foreach ($merge as $masterID => $values) { | |
1460 | $sql = " | |
1461 | UPDATE $tableName | |
1462 | SET addressee = %1, postal_greeting = %2, email_greeting = %3 | |
1463 | WHERE id = %4 | |
1464 | "; | |
1465 | $params = array(1 => array($values['addressee'], 'String'), | |
1466 | 2 => array($values['postalGreeting'], 'String'), | |
1467 | 3 => array($values['emailGreeting'], 'String'), | |
1468 | 4 => array($masterID, 'Integer'), | |
1469 | ); | |
1470 | CRM_Core_DAO::executeQuery($sql, $params); | |
1471 | ||
1472 | // delete all copies | |
1473 | $deleteIDs = array_keys($values['copy']); | |
1474 | $deleteIDString = implode(',', $deleteIDs); | |
1475 | $sql = " | |
1476 | DELETE FROM $tableName | |
1477 | WHERE id IN ( $deleteIDString ) | |
1478 | "; | |
1479 | CRM_Core_DAO::executeQuery($sql); | |
1480 | } | |
1481 | ||
1482 | // unset temporary columns that were added for postal mailing format | |
1483 | if (!empty($exportParams['merge_same_address']['temp_columns'])) { | |
1484 | $unsetKeys = array_keys($sqlColumns); | |
1485 | foreach ($unsetKeys as $headerKey => $sqlColKey) { | |
1486 | if (array_key_exists($sqlColKey, $exportParams['merge_same_address']['temp_columns'])) { | |
1487 | unset($sqlColumns[$sqlColKey], $headerRows[$headerKey]); | |
1488 | } | |
1489 | } | |
1490 | } | |
1491 | } | |
1492 | ||
1493 | static function _replaceMergeTokens($contactId, $exportParams) { | |
1494 | $greetings = array(); | |
1495 | $contact = NULL; | |
1496 | ||
1497 | $greetingFields = array( | |
1498 | 'postal_greeting', | |
1499 | 'addressee', | |
1500 | ); | |
1501 | foreach ($greetingFields as $greeting) { | |
1502 | if (CRM_Utils_Array::value($greeting, $exportParams)) { | |
1503 | $greetingLabel = $exportParams[$greeting]; | |
1504 | if (empty($contact)) { | |
1505 | $values = array( | |
1506 | 'id' => $contactId, | |
1507 | 'version' => 3, | |
1508 | ); | |
1509 | $contact = civicrm_api('contact', 'get', $values); | |
1510 | ||
1511 | if (CRM_Utils_Array::value('is_error', $contact)) { | |
1512 | return $greetings; | |
1513 | } | |
1514 | $contact = $contact['values'][$contact['id']]; | |
1515 | } | |
1516 | ||
1517 | $tokens = array('contact' => $greetingLabel); | |
1518 | $greetings[$greeting] = CRM_Utils_Token::replaceContactTokens($greetingLabel, $contact, NULL, $tokens); | |
1519 | } | |
1520 | } | |
1521 | return $greetings; | |
1522 | } | |
1523 | ||
1524 | /** | |
1525 | * The function unsets static part of the string, if token is the dynamic part. | |
1526 | * Example: 'Hello {contact.first_name}' => converted to => '{contact.first_name}' | |
1527 | * i.e 'Hello Alan' => converted to => 'Alan' | |
1528 | * | |
1529 | */ | |
1530 | static function _trimNonTokens(&$parsedString, $defaultGreeting, | |
1531 | $addressMergeGreetings, $greetingType = 'postal_greeting' | |
1532 | ) { | |
1533 | if (CRM_Utils_Array::value($greetingType, $addressMergeGreetings)) { | |
1534 | $greetingLabel = $addressMergeGreetings[$greetingType]; | |
1535 | } | |
1536 | $greetingLabel = empty($greetingLabel) ? $defaultGreeting : $greetingLabel; | |
1537 | ||
1538 | $stringsToBeReplaced = preg_replace('/(\{[a-zA-Z._ ]+\})/', ';;', $greetingLabel); | |
1539 | $stringsToBeReplaced = explode(';;', $stringsToBeReplaced); | |
1540 | foreach ($stringsToBeReplaced as $key => $string) { | |
1541 | // to keep one space | |
1542 | $stringsToBeReplaced[$key] = ltrim($string); | |
1543 | } | |
1544 | $parsedString = str_replace($stringsToBeReplaced, "", $parsedString); | |
1545 | ||
1546 | return $parsedString; | |
1547 | } | |
1548 | ||
1549 | static function _buildMasterCopyArray($sql, $exportParams, $sharedAddress = FALSE) { | |
1550 | static $contactGreetingTokens = array(); | |
1551 | ||
1552 | $addresseeOptions = CRM_Core_OptionGroup::values('addressee'); | |
1553 | $postalOptions = CRM_Core_OptionGroup::values('postal_greeting'); | |
1554 | ||
1555 | $merge = $parents = array(); | |
1556 | $dao = CRM_Core_DAO::executeQuery($sql); | |
1557 | ||
1558 | while ($dao->fetch()) { | |
1559 | $masterID = $dao->master_id; | |
1560 | $copyID = $dao->copy_id; | |
1561 | $masterPostalGreeting = $dao->master_postal_greeting; | |
1562 | $masterAddressee = $dao->master_addressee; | |
1563 | $copyAddressee = $dao->copy_addressee; | |
1564 | ||
1565 | if (!$sharedAddress) { | |
1566 | if (!isset($contactGreetingTokens[$dao->master_contact_id])) { | |
1567 | $contactGreetingTokens[$dao->master_contact_id] = self::_replaceMergeTokens($dao->master_contact_id, $exportParams); | |
1568 | } | |
1569 | $masterPostalGreeting = CRM_Utils_Array::value('postal_greeting', | |
1570 | $contactGreetingTokens[$dao->master_contact_id], $dao->master_postal_greeting | |
1571 | ); | |
1572 | $masterAddressee = CRM_Utils_Array::value('addressee', | |
1573 | $contactGreetingTokens[$dao->master_contact_id], $dao->master_addressee | |
1574 | ); | |
1575 | ||
1576 | if (!isset($contactGreetingTokens[$dao->copy_contact_id])) { | |
1577 | $contactGreetingTokens[$dao->copy_contact_id] = self::_replaceMergeTokens($dao->copy_contact_id, $exportParams); | |
1578 | } | |
1579 | $copyPostalGreeting = CRM_Utils_Array::value('postal_greeting', | |
1580 | $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_postal_greeting | |
1581 | ); | |
1582 | $copyAddressee = CRM_Utils_Array::value('addressee', | |
1583 | $contactGreetingTokens[$dao->copy_contact_id], $dao->copy_addressee | |
1584 | ); | |
1585 | } | |
1586 | ||
1587 | if (!isset($merge[$masterID])) { | |
1588 | // check if this is an intermediate child | |
1589 | // this happens if there are 3 or more matches a,b, c | |
1590 | // the above query will return a, b / a, c / b, c | |
1591 | // we might be doing a bit more work, but for now its ok, unless someone | |
1592 | // knows how to fix the query above | |
1593 | if (isset($parents[$masterID])) { | |
1594 | $masterID = $parents[$masterID]; | |
1595 | } | |
1596 | else { | |
1597 | $merge[$masterID] = array( | |
1598 | 'addressee' => $masterAddressee, | |
1599 | 'copy' => array(), | |
1600 | 'postalGreeting' => $masterPostalGreeting, | |
1601 | ); | |
1602 | $merge[$masterID]['emailGreeting'] = &$merge[$masterID]['postalGreeting']; | |
1603 | } | |
1604 | } | |
1605 | $parents[$copyID] = $masterID; | |
1606 | ||
1607 | if (!$sharedAddress && !array_key_exists($copyID, $merge[$masterID]['copy'])) { | |
1608 | ||
1609 | if (CRM_Utils_Array::value('postal_greeting_other', $exportParams) && | |
1610 | count($merge[$masterID]['copy']) >= 1 | |
1611 | ) { | |
1612 | // use static greetings specified if no of contacts > 2 | |
1613 | $merge[$masterID]['postalGreeting'] = $exportParams['postal_greeting_other']; | |
1614 | } | |
1615 | elseif ($copyPostalGreeting) { | |
1616 | self::_trimNonTokens($copyPostalGreeting, | |
1617 | $postalOptions[$dao->copy_postal_greeting_id], | |
1618 | $exportParams | |
1619 | ); | |
1620 | $merge[$masterID]['postalGreeting'] = "{$merge[$masterID]['postalGreeting']}, {$copyPostalGreeting}"; | |
1621 | // if there happens to be a duplicate, remove it | |
1622 | $merge[$masterID]['postalGreeting'] = str_replace(" {$copyPostalGreeting},", "", $merge[$masterID]['postalGreeting']); | |
1623 | } | |
1624 | ||
1625 | if (CRM_Utils_Array::value('addressee_other', $exportParams) && | |
1626 | count($merge[$masterID]['copy']) >= 1 | |
1627 | ) { | |
1628 | // use static greetings specified if no of contacts > 2 | |
1629 | $merge[$masterID]['addressee'] = $exportParams['addressee_other']; | |
1630 | } | |
1631 | elseif ($copyAddressee) { | |
1632 | self::_trimNonTokens($copyAddressee, | |
1633 | $addresseeOptions[$dao->copy_addressee_id], | |
1634 | $exportParams, 'addressee' | |
1635 | ); | |
1636 | $merge[$masterID]['addressee'] = "{$merge[$masterID]['addressee']}, " . trim($copyAddressee); | |
1637 | } | |
1638 | } | |
1639 | $merge[$masterID]['copy'][$copyID] = $copyAddressee; | |
1640 | } | |
1641 | ||
1642 | return $merge; | |
1643 | } | |
1644 | ||
1645 | /** | |
1646 | * Function to merge household record into the individual record | |
1647 | * if exists | |
1648 | * | |
1649 | * @param string $exportTempTable temporary temp table that stores the records | |
1650 | * @param array $headerRows array of headers for the export file | |
1651 | * @param array $sqlColumns array of names of the table columns of the temp table | |
1652 | * @param string $prefix name of the relationship type that is prefixed to the table columns | |
1653 | */ | |
1654 | static function mergeSameHousehold($exportTempTable, &$headerRows, &$sqlColumns, $prefix) { | |
1655 | $prefixColumn = $prefix . '_'; | |
1656 | $allKeys = array_keys($sqlColumns); | |
1657 | $replaced = array(); | |
1658 | $headerRows = array_values($headerRows); | |
1659 | ||
1660 | // name map of the non standard fields in header rows & sql columns | |
1661 | $mappingFields = array( | |
1662 | 'civicrm_primary_id' => 'id', | |
1663 | 'contact_source' => 'source', | |
1664 | 'current_employer_id' => 'employer_id', | |
1665 | 'contact_is_deleted' => 'is_deleted', | |
1666 | 'name' => 'address_name', | |
1667 | 'provider_id' => 'im_service_provider', | |
1668 | 'phone_type_id' => 'phone_type' | |
1669 | ); | |
1670 | ||
1671 | //figure out which columns are to be replaced by which ones | |
1672 | foreach ($sqlColumns as $columnNames => $dontCare) { | |
1673 | if ($rep = CRM_Utils_Array::value($columnNames, $mappingFields)) { | |
1674 | $replaced[$columnNames] = CRM_Utils_String::munge($prefixColumn . $rep, '_', 64); | |
1675 | } | |
1676 | else { | |
1677 | $householdColName = CRM_Utils_String::munge($prefixColumn . $columnNames, '_', 64); | |
1678 | ||
1679 | if (CRM_Utils_Array::value($householdColName, $sqlColumns)) { | |
1680 | $replaced[$columnNames] = $householdColName; | |
1681 | } | |
1682 | } | |
1683 | } | |
1684 | $query = "UPDATE $exportTempTable SET "; | |
1685 | ||
1686 | $clause = array(); | |
1687 | foreach ($replaced as $from => $to) { | |
1688 | $clause[] = "$from = $to "; | |
1689 | unset($sqlColumns[$to]); | |
1690 | if ($key = CRM_Utils_Array::key($to, $allKeys)) { | |
1691 | unset($headerRows[$key]); | |
1692 | } | |
1693 | } | |
1694 | $query .= implode(",\n", $clause); | |
1695 | $query .= " WHERE {$replaced['civicrm_primary_id']} != ''"; | |
1696 | ||
1697 | CRM_Core_DAO::executeQuery($query); | |
1698 | ||
1699 | //drop the table columns that store redundant household info | |
1700 | $dropQuery = "ALTER TABLE $exportTempTable "; | |
1701 | foreach ($replaced as $householdColumns) { | |
1702 | $dropClause[] = " DROP $householdColumns "; | |
1703 | } | |
1704 | $dropQuery .= implode(",\n", $dropClause); | |
1705 | ||
1706 | CRM_Core_DAO::executeQuery($dropQuery); | |
1707 | ||
1708 | // also drop the temp table if exists | |
1709 | $sql = "DROP TABLE IF EXISTS {$exportTempTable}_temp"; | |
1710 | CRM_Core_DAO::executeQuery($sql); | |
1711 | ||
1712 | // clean up duplicate records | |
1713 | $query = " | |
1714 | CREATE TABLE {$exportTempTable}_temp SELECT * | |
1715 | FROM {$exportTempTable} | |
1716 | GROUP BY civicrm_primary_id "; | |
1717 | ||
1718 | CRM_Core_DAO::executeQuery($query); | |
1719 | ||
1720 | $query = "DROP TABLE $exportTempTable"; | |
1721 | CRM_Core_DAO::executeQuery($query); | |
1722 | ||
1723 | $query = "ALTER TABLE {$exportTempTable}_temp RENAME TO {$exportTempTable}"; | |
1724 | CRM_Core_DAO::executeQuery($query); | |
1725 | } | |
1726 | ||
1727 | static function writeCSVFromTable($exportTempTable, $headerRows, $sqlColumns, $exportMode, $saveFile = null, $batchItems = '') { | |
1728 | $writeHeader = TRUE; | |
1729 | $offset = 0; | |
1730 | $limit = self::EXPORT_ROW_COUNT; | |
1731 | ||
1732 | $query = "SELECT * FROM $exportTempTable"; | |
1733 | ||
1734 | while (1) { | |
1735 | $limitQuery = $query . " | |
1736 | LIMIT $offset, $limit | |
1737 | "; | |
1738 | $dao = CRM_Core_DAO::executeQuery($limitQuery); | |
1739 | ||
1740 | if ($dao->N <= 0) { | |
1741 | break; | |
1742 | } | |
1743 | ||
1744 | $componentDetails = array(); | |
1745 | while ($dao->fetch()) { | |
1746 | $row = array(); | |
1747 | ||
1748 | foreach ($sqlColumns as $column => $dontCare) { | |
1749 | $row[$column] = $dao->$column; | |
1750 | } | |
1751 | $componentDetails[] = $row; | |
1752 | } | |
1753 | if ( $exportMode == 'financial' ) { | |
1754 | $getExportFileName = 'CiviCRM Contribution Search'; | |
1755 | } | |
1756 | else { | |
1757 | $getExportFileName =self::getExportFileName( 'csv', $exportMode ); | |
1758 | } | |
1759 | $csvRows = CRM_Core_Report_Excel::writeCSVFile( $getExportFileName, | |
1760 | $headerRows, | |
1761 | $componentDetails, | |
1762 | null, | |
1763 | $writeHeader, | |
1764 | $saveFile ); | |
1765 | ||
1766 | if ($saveFile && !empty($csvRows)) { | |
1767 | $batchItems .= $csvRows; | |
1768 | } | |
1769 | ||
1770 | $writeHeader = false; | |
1771 | $offset += $limit; | |
1772 | } | |
1773 | } | |
1774 | ||
1775 | /** | |
1776 | * Function to manipulate header rows for relationship fields | |
1777 | * | |
1778 | */ | |
1779 | function manipulateHeaderRows(&$headerRows, $contactRelationshipTypes) { | |
1780 | foreach ($headerRows as & $header) { | |
1781 | $split = explode('-', $header); | |
1782 | if ($relationTypeName = CRM_Utils_Array::value($split[0], $contactRelationshipTypes)) { | |
1783 | $split[0] = $relationTypeName; | |
1784 | $header = implode('-', $split); | |
1785 | } | |
1786 | } | |
1787 | } | |
1788 | ||
1789 | /** | |
1790 | * Function to exclude contacts who are deceased, have "Do not mail" privacy setting, | |
1791 | * or have no street address | |
1792 | * | |
1793 | */ | |
1794 | function postalMailingFormat($exportTempTable, &$headerRows, &$sqlColumns, $exportParams) { | |
1795 | $whereClause = array(); | |
1796 | ||
1797 | if (array_key_exists('is_deceased', $sqlColumns)) { | |
1798 | $whereClause[] = 'is_deceased = 1'; | |
1799 | } | |
1800 | ||
1801 | if (array_key_exists('do_not_mail', $sqlColumns)) { | |
1802 | $whereClause[] = 'do_not_mail = 1'; | |
1803 | } | |
1804 | ||
1805 | if (array_key_exists('street_address', $sqlColumns)) { | |
1806 | $addressWhereClause = " ( (street_address IS NULL) OR (street_address = '') ) "; | |
1807 | ||
1808 | // check for supplemental_address_1 | |
1809 | if (array_key_exists('supplemental_address_1', $sqlColumns)) { | |
1810 | $addressOptions = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, | |
1811 | 'address_options', TRUE, NULL, TRUE | |
1812 | ); | |
1813 | if (CRM_Utils_Array::value('supplemental_address_1', $addressOptions)) { | |
1814 | $addressWhereClause .= " AND ( (supplemental_address_1 IS NULL) OR (supplemental_address_1 = '') ) "; | |
1815 | // enclose it again, since we are doing an AND in between a set of ORs | |
1816 | $addressWhereClause = "( $addressWhereClause )"; | |
1817 | } | |
1818 | } | |
1819 | ||
1820 | $whereClause[] = $addressWhereClause; | |
1821 | } | |
1822 | ||
1823 | if (!empty($whereClause)) { | |
1824 | $whereClause = implode(' OR ', $whereClause); | |
1825 | $query = " | |
1826 | DELETE | |
1827 | FROM $exportTempTable | |
1828 | WHERE {$whereClause}"; | |
1829 | CRM_Core_DAO::singleValueQuery($query); | |
1830 | } | |
1831 | ||
1832 | // unset temporary columns that were added for postal mailing format | |
1833 | if (!empty($exportParams['postal_mailing_export']['temp_columns'])) { | |
1834 | $unsetKeys = array_keys($sqlColumns); | |
1835 | foreach ($unsetKeys as $headerKey => $sqlColKey) { | |
1836 | if (array_key_exists($sqlColKey, $exportParams['postal_mailing_export']['temp_columns'])) { | |
1837 | unset($sqlColumns[$sqlColKey], $headerRows[$headerKey]); | |
1838 | } | |
1839 | } | |
1840 | } | |
1841 | } | |
d77aba4b AS |
1842 | |
1843 | /** | |
1844 | * Build componentPayment fields. | |
1845 | */ | |
1846 | static function componentPaymentFields() { | |
1847 | static $componentPaymentFields; | |
1848 | if (!isset( $componentPaymentFields)) { | |
1849 | $componentPaymentFields = array( | |
1850 | 'componentPaymentField_total_amount' => ts('Total Amount'), | |
1851 | 'componentPaymentField_contribution_status' => ts('Contribution Status'), | |
1852 | 'componentPaymentField_received_date' => ts('Received Date'), | |
1853 | 'componentPaymentField_payment_instrument' => ts('Payment Instrument'), | |
1854 | 'componentPaymentField_transaction_id' => ts('Transaction ID'), | |
1855 | ); | |
1856 | } | |
1857 | return $componentPaymentFields; | |
1858 | } | |
6a488035 TO |
1859 | } |
1860 |