4 * Class CRM_Core_DAOTest
7 class CRM_Export_BAO_ExportTest
extends CiviUnitTestCase
{
10 * Contact IDs created for testing.
14 protected $contactIDs = [];
17 * Contribution IDs created for testing.
21 protected $contributionIDs = [];
24 * Contribution IDs created for testing.
28 protected $activityIDs = [];
32 * Contribution IDs created for testing.
36 protected $membershipIDs = [];
39 * Master Address ID created for testing.
43 protected $masterAddressID;
45 public function tearDown() {
50 'civicrm_relationship',
53 'civicrm_case_contact',
54 'civicrm_case_activity',
56 $this->quickCleanUpFinancialEntities();
61 * Basic test to ensure the exportComponents function completes without error.
63 public function testExportComponentsNull() {
64 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
71 CRM_Export_Form_Select
::CONTACT_EXPORT
,
78 'suppress_csv_for_testing' => TRUE,
82 // delete the export temp table and component table
83 $sql = "DROP TABLE IF EXISTS {$tableName}";
84 CRM_Core_DAO
::executeQuery($sql);
88 * Basic test to ensure the exportComponents function can export selected fields for contribution.
90 public function testExportComponentsContribution() {
91 $this->setUpContributionExportData();
92 $selectedFields = array(
93 array('Individual', 'first_name'),
94 array('Individual', 'last_name'),
95 array('Contribution', 'receive_date'),
96 array('Contribution', 'contribution_source'),
97 array('Individual', 'street_address', 1),
98 array('Individual', 'city', 1),
99 array('Individual', 'country', 1),
100 array('Individual', 'email', 1),
101 array('Contribution', 'trxn_id'),
104 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
106 $this->contributionIDs
,
111 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
112 'civicrm_contribution.id IN ( ' . implode(',', $this->contributionIDs
) . ')',
117 'exportOption' => CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
118 'suppress_csv_for_testing' => TRUE,
122 // delete the export temp table and component table
123 $sql = "DROP TABLE IF EXISTS {$tableName}";
124 CRM_Core_DAO
::executeQuery($sql);
128 * Basic test to ensure the exportComponents function can export selected fields for contribution.
130 public function testExportComponentsActivity() {
131 $this->setUpActivityExportData();
132 $selectedFields = array(
133 array('Individual', 'display_name'),
134 array('Individual', '5_a_b', 'display_name'),
137 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
141 '`activity_date_time` desc',
144 CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
145 'civicrm_activity.id IN ( ' . implode(',', $this->activityIDs
) . ')',
150 'exportOption' => CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
151 'suppress_csv_for_testing' => TRUE,
155 // delete the export temp table and component table
156 $sql = "DROP TABLE IF EXISTS {$tableName}";
157 CRM_Core_DAO
::executeQuery($sql);
161 * Test the function that extracts the arrays used to structure the output.
163 * The keys in the output fields array should by matched by field aliases in the sql query (with
164 * exceptions of course - currently country is one - although maybe a future refactor can change that!).
166 * We are trying to move towards simpler processing in the per row iteration as that may be
167 * repeated 100,000 times and in general we should simply be able to match the query fields to
168 * our expected rows & do a little pseudoconstant mapping.
170 public function testGetExportStructureArrays() {
171 // This is how return properties are formatted internally within the function for passing to the BAO query.
172 $returnProperties = array(
176 'contribution_source' => 1,
179 'street_address' => 1,
190 'contribution_id' => 1,
193 $contactRelationshipTypes = CRM_Contact_BAO_Relationship
::getContactRelationshipType(
203 $query = new CRM_Contact_BAO_Query(array(), $returnProperties, NULL,
204 FALSE, FALSE, CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
,
205 FALSE, TRUE, TRUE, NULL, 'AND'
208 list($select) = $query->query();
209 $pattern = '/as `?([^`,]*)/';
210 $queryFieldAliases = array();
211 preg_match_all($pattern, $select, $queryFieldAliases, PREG_PATTERN_ORDER
);
212 $processor = new CRM_Export_BAO_ExportProcessor(CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
, NULL, 'AND');
213 $processor->setQueryFields($query->_fields
);
215 list($outputFields) = CRM_Export_BAO_Export
::getExportStructureArrays($returnProperties, $processor, $contactRelationshipTypes, '');
216 foreach (array_keys($outputFields) as $fieldAlias) {
217 if ($fieldAlias == 'Home-country') {
218 $this->assertTrue(in_array($fieldAlias . '_id', $queryFieldAliases[1]), 'Country is subject to some funky translate so we make sure country id is present');
221 $this->assertTrue(in_array($fieldAlias, $queryFieldAliases[1]), 'looking for field ' . $fieldAlias . ' in generaly the alias fields need to match the outputfields');
228 * Set up some data for us to do testing on.
230 public function setUpContributionExportData() {
231 $this->setUpContactExportData();
232 $this->contributionIDs
[] = $this->contributionCreate(array('contact_id' => $this->contactIDs
[0], 'trxn_id' => 'null', 'invoice_id' => 'null'));
233 $this->contributionIDs
[] = $this->contributionCreate(array('contact_id' => $this->contactIDs
[1], 'trxn_id' => 'null', 'invoice_id' => 'null'));
237 * Set up some data for us to do testing on.
239 public function setUpMembershipExportData() {
240 $this->setUpContactExportData();
241 $this->membershipIDs
[] = $this->contactMembershipCreate(['contact_id' => $this->contactIDs
[0]]);
245 * Set up data to test case export.
247 public function setupCaseExportData() {
248 $contactID1 = $this->individualCreate();
249 $contactID2 = $this->individualCreate(array(), 1);
251 $case = $this->callAPISuccess('case', 'create', array(
254 'contact_id' => $contactID1,
256 $this->callAPISuccess('CaseContact', 'create', [
257 'case_id' => $case['id'],
258 'contact_id' => $contactID2,
263 * Set up some data for us to do testing on.
265 public function setUpActivityExportData() {
266 $this->setUpContactExportData();
267 $this->activityIDs
[] = $this->activityCreate(array('contact_id' => $this->contactIDs
[0]))['id'];
271 * Set up some data for us to do testing on.
273 public function setUpContactExportData() {
274 $this->contactIDs
[] = $contactA = $this->individualCreate(['gender_id' => 'Female']);
275 // Create address for contact A.
277 'contact_id' => $contactA,
278 'location_type_id' => 'Home',
279 'street_address' => 'Ambachtstraat 23',
280 'postal_code' => '6971 BN',
281 'country_id' => '1152',
285 $result = $this->callAPISuccess('address', 'create', $params);
286 $addressId = $result['id'];
288 $this->callAPISuccess('email', 'create', array(
289 'id' => $this->callAPISuccessGetValue('Email', ['contact_id' => $params['contact_id'], 'return' => 'id']),
290 'location_type_id' => 'Home',
291 'email' => 'home@example.com',
294 $this->callAPISuccess('email', 'create', array('contact_id' => $params['contact_id'], 'location_type_id' => 'Work', 'email' => 'work@example.com', 'is_primary' => 0));
296 $params['is_primary'] = 0;
297 $params['location_type_id'] = 'Work';
298 $this->callAPISuccess('address', 'create', $params);
299 $this->contactIDs
[] = $contactB = $this->individualCreate();
301 $this->callAPISuccess('address', 'create', array(
302 'contact_id' => $contactB,
303 'location_type_id' => "Home",
304 'master_id' => $addressId,
306 $this->masterAddressID
= $addressId;
311 * Test variants of primary address exporting.
313 * @param int $isPrimaryOnly
315 * @dataProvider getPrimarySearchOptions
317 public function testExportPrimaryAddress($isPrimaryOnly) {
318 \Civi
::settings()->set('searchPrimaryDetailsOnly', $isPrimaryOnly);
319 $this->setUpContactExportData();
321 $selectedFields = [['Individual', 'email', ' '], ['Individual', 'email', '1'], ['Individual', 'email', '2']];
322 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
325 [['email', 'LIKE', 'c', 0, 1]],
329 CRM_Export_Form_Select
::CONTACT_EXPORT
,
330 "contact_a.id IN ({$this->contactIDs[0]}, {$this->contactIDs[1]})",
335 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
336 'suppress_csv_for_testing' => TRUE,
340 $dao = CRM_Core_DAO
::executeQuery('SELECT * from ' . $tableName);
342 $this->assertEquals('home@example.com', $dao->email
);
343 $this->assertEquals('work@example.com', $dao->work_email
);
344 $this->assertEquals('home@example.com', $dao->home_email
);
345 $this->assertEquals(2, $dao->N
);
346 \Civi
::settings()->set('searchPrimaryDetailsOnly', FALSE);
350 * Get the options for the primary search setting field.
353 public function getPrimarySearchOptions() {
354 return [[TRUE], [FALSE]];
358 * Test that when exporting a pseudoField it is reset for NULL entries.
360 * ie. we have a contact WITH a gender & one without - make sure the latter one
361 * does NOT retain the gender of the former.
363 public function testExportPseudoField() {
364 $this->setUpContactExportData();
365 $selectedFields = [['Individual', 'gender_id']];
366 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
368 $this->contactIDs
[1],
373 CRM_Export_Form_Select
::CONTACT_EXPORT
,
374 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
379 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
380 'suppress_csv_for_testing' => TRUE,
383 $this->assertEquals('Female,', CRM_Core_DAO
::singleValueQuery("SELECT GROUP_CONCAT(gender_id) FROM {$tableName}"));
387 * Test that when exporting a pseudoField it is reset for NULL entries.
389 * This is specific to the example in CRM-14398
391 public function testExportPseudoFieldCampaign() {
392 $this->setUpContributionExportData();
393 $campaign = $this->callAPISuccess('Campaign', 'create', ['title' => 'Big campaign']);
394 $this->callAPISuccess('Contribution', 'create', ['campaign_id' => 'Big_campaign', 'id' => $this->contributionIDs
[0]]);
395 $selectedFields = [['Individual', 'gender_id'], ['Contribution', 'contribution_campaign_title']];
396 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
398 $this->contactIDs
[1],
403 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
404 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
409 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
410 'suppress_csv_for_testing' => TRUE,
413 $this->assertEquals('Big campaign,', CRM_Core_DAO
::singleValueQuery("SELECT GROUP_CONCAT(contribution_campaign_title) FROM {$tableName}"));
417 * Test exporting relationships.
419 * This is to ensure that CRM-13995 remains fixed.
421 public function testExportRelationshipsMergeToHousehold() {
422 list($householdID, $houseHoldTypeID) = $this->setUpHousehold();
425 ['Individual', $houseHoldTypeID . '_a_b', 'state_province', ''],
426 ['Individual', $houseHoldTypeID . '_a_b', 'city', ''],
427 ['Individual', 'city', ''],
428 ['Individual', 'state_province', ''],
430 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
437 CRM_Export_Form_Select
::CONTACT_EXPORT
,
438 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
443 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
444 'suppress_csv_for_testing' => TRUE,
447 $dao = CRM_Core_DAO
::executeQuery("SELECT * FROM {$tableName}");
448 while ($dao->fetch()) {
449 $this->assertEquals('Portland', $dao->city
);
450 $this->assertEquals('ME', $dao->state_province
);
451 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
452 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
458 * Test exporting relationships.
460 public function testExportRelationshipsMergeToHouseholdAllFields() {
461 $this->markTestIncomplete('Does not yet work under CI due to mysql limitation (number of columns in table). Works on some boxes');
462 list($householdID) = $this->setUpHousehold();
463 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
470 CRM_Export_Form_Select
::CONTACT_EXPORT
,
471 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
476 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
477 'suppress_csv_for_testing' => TRUE,
480 $dao = CRM_Core_DAO
::executeQuery("SELECT * FROM {$tableName}");
481 while ($dao->fetch()) {
482 $this->assertEquals('Portland', $dao->city
);
483 $this->assertEquals('ME', $dao->state_province
);
484 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
485 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
486 $this->assertEquals('Unit Test Household', $dao->addressee
);
487 $this->assertEquals('Unit Test Household', $dao->display_name
);
492 * Test master_address_id field.
494 public function testExportCustomData() {
495 $this->setUpContactExportData();
497 $customData = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTest.php');
499 $this->callAPISuccess('Contact', 'create', [
500 'id' => $this->contactIDs
[1],
501 'custom_' . $customData['custom_field_id'] => 'BlahdeBlah',
502 'api.Address.create' => ['location_type_id' => 'Billing', 'city' => 'Waipu'],
505 ['Individual', 'city', CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Address', 'location_type_id', 'Billing')],
506 ['Individual', 'custom_1'],
509 list($tableName, $sqlColumns) = $this->doExport($selectedFields, $this->contactIDs
[1]);
510 $this->assertEquals([
511 'billing_city' => 'billing_city text',
512 'custom_1' => 'custom_1 varchar(255)',
515 $dao = CRM_Core_DAO
::executeQuery('SELECT * FROM ' . $tableName);
516 while ($dao->fetch()) {
517 $this->assertEquals('BlahdeBlah', $dao->custom_1
);
518 $this->assertEquals('Waipu', $dao->billing_city
);
523 * Attempt to do a fairly full export of location data.
525 public function testExportIMData() {
526 // Use default providers.
527 $providers = ['AIM', 'GTalk', 'Jabber', 'MSN', 'Skype', 'Yahoo'];
528 $locationTypes = ['Billing', 'Home', 'Main', 'Other'];
530 $this->contactIDs
[] = $this->individualCreate();
531 $this->contactIDs
[] = $this->individualCreate();
532 $this->contactIDs
[] = $this->householdCreate();
533 $this->contactIDs
[] = $this->organizationCreate();
534 foreach ($this->contactIDs
as $contactID) {
535 foreach ($providers as $provider) {
536 foreach ($locationTypes as $locationType) {
537 $this->callAPISuccess('IM', 'create', [
538 'contact_id' => $contactID,
539 'location_type_id' => $locationType,
540 'provider_id' => $provider,
541 'name' => $locationType . $provider . $contactID,
548 $this->contactIDs
[1] => ['label' => 'Spouse of'],
549 $this->contactIDs
[2] => ['label' => 'Household Member of'],
550 $this->contactIDs
[3] => ['label' => 'Employee of']
553 foreach ($relationships as $contactID => $relationshipType) {
554 $relationshipTypeID = $this->callAPISuccess('RelationshipType', 'getvalue', ['label_a_b' => $relationshipType['label'], 'return' => 'id']);
555 $result = $this->callAPISuccess('Relationship', 'create', [
556 'contact_id_a' => $this->contactIDs
[0],
557 'relationship_type_id' => $relationshipTypeID,
558 'contact_id_b' => $contactID
560 $relationships[$contactID]['id'] = $result['id'];
561 $relationships[$contactID]['relationship_type_id'] = $relationshipTypeID;
564 $fields = [['Individual', 'contact_id']];
565 // ' ' denotes primary location type.
566 foreach (array_merge($locationTypes, [' ']) as $locationType) {
570 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
572 foreach ($relationships as $contactID => $relationship) {
575 $relationship['relationship_type_id'] . '_a_b',
577 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
580 foreach ($providers as $provider) {
584 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
585 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'provider_id', $provider),
587 foreach ($relationships as $contactID => $relationship) {
590 $relationship['relationship_type_id'] . '_a_b',
592 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
593 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'provider_id', $provider),
598 list($tableName, $sqlColumns) = $this->doExport($fields, $this->contactIDs
[0]);
600 $dao = CRM_Core_DAO
::executeQuery('SELECT * FROM ' . $tableName);
601 while ($dao->fetch()) {
602 $id = $dao->contact_id
;
603 $this->assertEquals('AIM', $dao->billing_im_provider
);
604 $this->assertEquals('BillingJabber' . $id, $dao->billing_im_screen_name_jabber
);
605 $this->assertEquals('BillingSkype' . $id, $dao->billing_im_screen_name_skype
);
606 foreach ($relationships as $relatedContactID => $relationship) {
607 $relationshipString = $field = $relationship['relationship_type_id'] . '_a_b';
608 $field = $relationshipString . '_billing_im_screen_name_yahoo';
609 $this->assertEquals('BillingYahoo' . $relatedContactID, $dao->$field);
610 // @todo efforts to output 'im_provider' for related contacts seem to be giving a blank field.
614 $this->assertEquals([
615 'billing_im_provider' => 'billing_im_provider text',
616 'billing_im_screen_name' => 'billing_im_screen_name text',
617 'billing_im_screen_name_jabber' => 'billing_im_screen_name_jabber text',
618 'billing_im_screen_name_skype' => 'billing_im_screen_name_skype text',
619 'billing_im_screen_name_yahoo' => 'billing_im_screen_name_yahoo text',
620 'home_im_provider' => 'home_im_provider text',
621 'home_im_screen_name' => 'home_im_screen_name text',
622 'home_im_screen_name_jabber' => 'home_im_screen_name_jabber text',
623 'home_im_screen_name_skype' => 'home_im_screen_name_skype text',
624 'home_im_screen_name_yahoo' => 'home_im_screen_name_yahoo text',
625 'main_im_provider' => 'main_im_provider text',
626 'main_im_screen_name' => 'main_im_screen_name text',
627 'main_im_screen_name_jabber' => 'main_im_screen_name_jabber text',
628 'main_im_screen_name_skype' => 'main_im_screen_name_skype text',
629 'main_im_screen_name_yahoo' => 'main_im_screen_name_yahoo text',
630 'other_im_provider' => 'other_im_provider text',
631 'other_im_screen_name' => 'other_im_screen_name text',
632 'other_im_screen_name_jabber' => 'other_im_screen_name_jabber text',
633 'other_im_screen_name_skype' => 'other_im_screen_name_skype text',
634 'other_im_screen_name_yahoo' => 'other_im_screen_name_yahoo text',
635 'im_provider' => 'im_provider text',
636 'im' => 'im varchar(64)',
637 'contact_id' => 'contact_id varchar(255)',
638 '2_a_b_im_provider' => '2_a_b_im_provider text',
639 '2_a_b_billing_im_screen_name' => '2_a_b_billing_im_screen_name text',
640 '2_a_b_billing_im_screen_name_jabber' => '2_a_b_billing_im_screen_name_jabber text',
641 '2_a_b_billing_im_screen_name_skype' => '2_a_b_billing_im_screen_name_skype text',
642 '2_a_b_billing_im_screen_name_yahoo' => '2_a_b_billing_im_screen_name_yahoo text',
643 '2_a_b_home_im_screen_name' => '2_a_b_home_im_screen_name text',
644 '2_a_b_home_im_screen_name_jabber' => '2_a_b_home_im_screen_name_jabber text',
645 '2_a_b_home_im_screen_name_skype' => '2_a_b_home_im_screen_name_skype text',
646 '2_a_b_home_im_screen_name_yahoo' => '2_a_b_home_im_screen_name_yahoo text',
647 '2_a_b_main_im_screen_name' => '2_a_b_main_im_screen_name text',
648 '2_a_b_main_im_screen_name_jabber' => '2_a_b_main_im_screen_name_jabber text',
649 '2_a_b_main_im_screen_name_skype' => '2_a_b_main_im_screen_name_skype text',
650 '2_a_b_main_im_screen_name_yahoo' => '2_a_b_main_im_screen_name_yahoo text',
651 '2_a_b_other_im_screen_name' => '2_a_b_other_im_screen_name text',
652 '2_a_b_other_im_screen_name_jabber' => '2_a_b_other_im_screen_name_jabber text',
653 '2_a_b_other_im_screen_name_skype' => '2_a_b_other_im_screen_name_skype text',
654 '2_a_b_other_im_screen_name_yahoo' => '2_a_b_other_im_screen_name_yahoo text',
655 '2_a_b_im' => '2_a_b_im text',
656 '8_a_b_im_provider' => '8_a_b_im_provider text',
657 '8_a_b_billing_im_screen_name' => '8_a_b_billing_im_screen_name text',
658 '8_a_b_billing_im_screen_name_jabber' => '8_a_b_billing_im_screen_name_jabber text',
659 '8_a_b_billing_im_screen_name_skype' => '8_a_b_billing_im_screen_name_skype text',
660 '8_a_b_billing_im_screen_name_yahoo' => '8_a_b_billing_im_screen_name_yahoo text',
661 '8_a_b_home_im_screen_name' => '8_a_b_home_im_screen_name text',
662 '8_a_b_home_im_screen_name_jabber' => '8_a_b_home_im_screen_name_jabber text',
663 '8_a_b_home_im_screen_name_skype' => '8_a_b_home_im_screen_name_skype text',
664 '8_a_b_home_im_screen_name_yahoo' => '8_a_b_home_im_screen_name_yahoo text',
665 '8_a_b_main_im_screen_name' => '8_a_b_main_im_screen_name text',
666 '8_a_b_main_im_screen_name_jabber' => '8_a_b_main_im_screen_name_jabber text',
667 '8_a_b_main_im_screen_name_skype' => '8_a_b_main_im_screen_name_skype text',
668 '8_a_b_main_im_screen_name_yahoo' => '8_a_b_main_im_screen_name_yahoo text',
669 '8_a_b_other_im_screen_name' => '8_a_b_other_im_screen_name text',
670 '8_a_b_other_im_screen_name_jabber' => '8_a_b_other_im_screen_name_jabber text',
671 '8_a_b_other_im_screen_name_skype' => '8_a_b_other_im_screen_name_skype text',
672 '8_a_b_other_im_screen_name_yahoo' => '8_a_b_other_im_screen_name_yahoo text',
673 '8_a_b_im' => '8_a_b_im text',
674 '5_a_b_im_provider' => '5_a_b_im_provider text',
675 '5_a_b_billing_im_screen_name' => '5_a_b_billing_im_screen_name text',
676 '5_a_b_billing_im_screen_name_jabber' => '5_a_b_billing_im_screen_name_jabber text',
677 '5_a_b_billing_im_screen_name_skype' => '5_a_b_billing_im_screen_name_skype text',
678 '5_a_b_billing_im_screen_name_yahoo' => '5_a_b_billing_im_screen_name_yahoo text',
679 '5_a_b_home_im_screen_name' => '5_a_b_home_im_screen_name text',
680 '5_a_b_home_im_screen_name_jabber' => '5_a_b_home_im_screen_name_jabber text',
681 '5_a_b_home_im_screen_name_skype' => '5_a_b_home_im_screen_name_skype text',
682 '5_a_b_home_im_screen_name_yahoo' => '5_a_b_home_im_screen_name_yahoo text',
683 '5_a_b_main_im_screen_name' => '5_a_b_main_im_screen_name text',
684 '5_a_b_main_im_screen_name_jabber' => '5_a_b_main_im_screen_name_jabber text',
685 '5_a_b_main_im_screen_name_skype' => '5_a_b_main_im_screen_name_skype text',
686 '5_a_b_main_im_screen_name_yahoo' => '5_a_b_main_im_screen_name_yahoo text',
687 '5_a_b_other_im_screen_name' => '5_a_b_other_im_screen_name text',
688 '5_a_b_other_im_screen_name_jabber' => '5_a_b_other_im_screen_name_jabber text',
689 '5_a_b_other_im_screen_name_skype' => '5_a_b_other_im_screen_name_skype text',
690 '5_a_b_other_im_screen_name_yahoo' => '5_a_b_other_im_screen_name_yahoo text',
691 '5_a_b_im' => '5_a_b_im text',
697 * Test master_address_id field.
699 public function testExportMasterAddress() {
700 $this->setUpContactExportData();
702 //export the master address for contact B
703 $selectedFields = array(
704 array('Individual', 'master_id', 1),
706 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
708 array($this->contactIDs
[1]),
713 CRM_Export_Form_Select
::CONTACT_EXPORT
,
714 "contact_a.id IN ({$this->contactIDs[1]})",
719 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
720 'suppress_csv_for_testing' => TRUE,
723 $field = key($sqlColumns);
725 //assert the exported result
726 $masterName = CRM_Core_DAO
::singleValueQuery("SELECT {$field} FROM {$tableName}");
727 $displayName = CRM_Contact_BAO_Contact
::getMasterDisplayName($this->masterAddressID
);
728 $this->assertEquals($displayName, $masterName);
730 // delete the export temp table and component table
731 $sql = "DROP TABLE IF EXISTS {$tableName}";
732 CRM_Core_DAO
::executeQuery($sql);
736 * Test that deceased and do not mail contacts are removed from contacts before
738 public function testExportDeceasedDoNotMail() {
739 $contactA = $this->callAPISuccess('contact', 'create', array(
740 'first_name' => 'John',
741 'last_name' => 'Doe',
742 'contact_type' => 'Individual',
745 $contactB = $this->callAPISuccess('contact', 'create', array(
746 'first_name' => 'Jane',
747 'last_name' => 'Doe',
748 'contact_type' => 'Individual',
752 //create address for contact A
753 $this->callAPISuccess('address', 'create', array(
754 'contact_id' => $contactA['id'],
755 'location_type_id' => 'Home',
756 'street_address' => 'ABC 12',
757 'postal_code' => '123 AB',
758 'country_id' => '1152',
763 //create address for contact B
764 $this->callAPISuccess('address', 'create', array(
765 'contact_id' => $contactB['id'],
766 'location_type_id' => 'Home',
767 'street_address' => 'ABC 12',
768 'postal_code' => '123 AB',
769 'country_id' => '1152',
774 //export and merge contacts with same address
775 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
777 array($contactA['id'], $contactB['id']),
782 CRM_Export_Form_Select
::CONTACT_EXPORT
,
783 "contact_a.id IN ({$contactA['id']}, {$contactB['id']})",
788 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
789 'mergeOption' => TRUE,
790 'suppress_csv_for_testing' => TRUE,
791 'postal_mailing_export' => array(
792 'postal_mailing_export' => TRUE,
797 $greeting = CRM_Core_DAO
::singleValueQuery("SELECT email_greeting FROM {$tableName}");
799 //Assert email_greeting is not merged
800 $this->assertNotContains(',', (string) $greeting);
802 // delete the export temp table and component table
803 $sql = "DROP TABLE IF EXISTS {$tableName}";
804 CRM_Core_DAO
::executeQuery($sql);
810 protected function setUpHousehold() {
811 $this->setUpContactExportData();
812 $householdID = $this->householdCreate([
813 'api.Address.create' => [
814 'city' => 'Portland',
815 'state_province_id' => 'Maine',
816 'location_type_id' => 'Home'
820 $relationshipTypes = $this->callAPISuccess('RelationshipType', 'get', [])['values'];
821 $houseHoldTypeID = NULL;
822 foreach ($relationshipTypes as $id => $relationshipType) {
823 if ($relationshipType['name_a_b'] === 'Household Member of') {
824 $houseHoldTypeID = $relationshipType['id'];
827 $this->callAPISuccess('Relationship', 'create', [
828 'contact_id_a' => $this->contactIDs
[0],
829 'contact_id_b' => $householdID,
830 'relationship_type_id' => $houseHoldTypeID,
832 $this->callAPISuccess('Relationship', 'create', [
833 'contact_id_a' => $this->contactIDs
[1],
834 'contact_id_b' => $householdID,
835 'relationship_type_id' => $houseHoldTypeID,
837 return array($householdID, $houseHoldTypeID);
841 * Do a CiviCRM export.
843 * @param $selectedFields
848 protected function doExport($selectedFields, $id) {
849 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
856 CRM_Export_Form_Select
::CONTACT_EXPORT
,
857 "contact_a.id IN ({$id})",
862 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
863 'suppress_csv_for_testing' => TRUE,
866 return array($tableName, $sqlColumns);
870 * Ensure component is enabled.
872 * @param int $exportMode
874 public function ensureComponentIsEnabled($exportMode) {
875 if ($exportMode === CRM_Export_Form_Select
::CASE_EXPORT
) {
876 CRM_Core_BAO_ConfigSetting
::enableComponent('CiviCase');
881 * Test our export all field metadata retrieval.
883 * @dataProvider additionalFieldsDataProvider
884 * @param int $exportMode
887 public function testAdditionalReturnProperties($exportMode, $expected) {
888 $this->ensureComponentIsEnabled($exportMode);
889 $processor = new CRM_Export_BAO_ExportProcessor($exportMode, NULL, 'AND');
890 $metadata = $processor->getAdditionalReturnProperties();
891 $this->assertEquals($expected, $metadata);
895 * Test our export all field metadata retrieval.
897 * @dataProvider allFieldsDataProvider
898 * @param int $exportMode
901 public function testDefaultReturnProperties($exportMode, $expected) {
902 $this->ensureComponentIsEnabled($exportMode);
903 $processor = new CRM_Export_BAO_ExportProcessor($exportMode, NULL, 'AND');
904 $metadata = $processor->getDefaultReturnProperties();
905 $this->assertEquals($expected, $metadata);
909 * Get fields returned from additionalFields function.
913 public function additionalFieldsDataProvider() {
916 'anything that will then be defaulting ton contact',
917 $this->getExtraReturnProperties(),
920 CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
921 array_merge($this->getExtraReturnProperties(), $this->getActivityReturnProperties()),
924 CRM_Export_Form_Select
::CASE_EXPORT
,
925 array_merge($this->getExtraReturnProperties(), $this->getCaseReturnProperties()),
928 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
929 array_merge($this->getExtraReturnProperties(), $this->getContributionReturnProperties()),
932 CRM_Export_Form_Select
::EVENT_EXPORT
,
933 array_merge($this->getExtraReturnProperties(), $this->getEventReturnProperties()),
936 CRM_Export_Form_Select
::MEMBER_EXPORT
,
937 array_merge($this->getExtraReturnProperties(), $this->getMembershipReturnProperties()),
940 CRM_Export_Form_Select
::PLEDGE_EXPORT
,
941 array_merge($this->getExtraReturnProperties(), $this->getPledgeReturnProperties()),
948 * get data for testing field metadata by query mode.
950 public function allFieldsDataProvider() {
953 'anything that will then be defaulting ton contact',
954 $this->getBasicReturnProperties(TRUE),
957 CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
958 array_merge($this->getBasicReturnProperties(FALSE), $this->getActivityReturnProperties()),
961 CRM_Export_Form_Select
::CASE_EXPORT
,
962 array_merge($this->getBasicReturnProperties(FALSE), $this->getCaseReturnProperties()),
965 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
966 array_merge($this->getBasicReturnProperties(FALSE), $this->getContributionReturnProperties()),
969 CRM_Export_Form_Select
::EVENT_EXPORT
,
970 array_merge($this->getBasicReturnProperties(FALSE), $this->getEventReturnProperties()),
973 CRM_Export_Form_Select
::MEMBER_EXPORT
,
974 array_merge($this->getBasicReturnProperties(FALSE), $this->getMembershipReturnProperties()),
977 CRM_Export_Form_Select
::PLEDGE_EXPORT
,
978 array_merge($this->getBasicReturnProperties(FALSE), $this->getPledgeReturnProperties()),
984 * Get return properties manually added in.
986 public function getExtraReturnProperties() {
988 'location_type' => 1,
990 'phone_type_id' => 1,
992 'current_employer' => 1,
997 * Get basic return properties.
999 * @param bool $isContactMode
1000 * Are we in contact mode or not
1004 protected function getBasicReturnProperties($isContactMode) {
1005 $returnProperties = [
1007 'contact_type' => 1,
1008 'contact_sub_type' => 1,
1009 'do_not_email' => 1,
1010 'do_not_phone' => 1,
1013 'do_not_trade' => 1,
1015 'legal_identifier' => 1,
1016 'external_identifier' => 1,
1018 'display_name' => 1,
1022 'preferred_communication_method' => 1,
1023 'preferred_language' => 1,
1024 'preferred_mail_format' => 1,
1026 'contact_source' => 1,
1032 'formal_title' => 1,
1033 'communication_style_id' => 1,
1034 'email_greeting_id' => 1,
1035 'postal_greeting_id' => 1,
1036 'addressee_id' => 1,
1041 'deceased_date' => 1,
1042 'household_name' => 1,
1043 'organization_name' => 1,
1045 'user_unique_id' => 1,
1046 'current_employer_id' => 1,
1047 'contact_is_deleted' => 1,
1048 'created_date' => 1,
1049 'modified_date' => 1,
1051 'email_greeting' => 1,
1052 'postal_greeting' => 1,
1053 'current_employer' => 1,
1054 'location_type' => 1,
1055 'street_address' => 1,
1056 'street_number' => 1,
1057 'street_number_suffix' => 1,
1060 'supplemental_address_1' => 1,
1061 'supplemental_address_2' => 1,
1062 'supplemental_address_3' => 1,
1064 'postal_code_suffix' => 1,
1068 'address_name' => 1,
1071 'state_province' => 1,
1078 'signature_text' => 1,
1079 'signature_html' => 1,
1083 'world_region' => 1,
1088 'phone_type_id' => 1,
1091 if (!$isContactMode) {
1092 unset($returnProperties['groups']);
1093 unset($returnProperties['tags']);
1094 unset($returnProperties['notes']);
1096 return $returnProperties;
1100 * Get return properties for pledges.
1104 public function getPledgeReturnProperties() {
1106 'contact_type' => 1,
1107 'contact_sub_type' => 1,
1109 'display_name' => 1,
1111 'pledge_amount' => 1,
1112 'pledge_total_paid' => 1,
1113 'pledge_create_date' => 1,
1114 'pledge_start_date' => 1,
1115 'pledge_next_pay_date' => 1,
1116 'pledge_next_pay_amount' => 1,
1117 'pledge_status' => 1,
1118 'pledge_is_test' => 1,
1119 'pledge_contribution_page_id' => 1,
1120 'pledge_financial_type' => 1,
1121 'pledge_frequency_interval' => 1,
1122 'pledge_frequency_unit' => 1,
1123 'pledge_currency' => 1,
1124 'pledge_campaign_id' => 1,
1125 'pledge_balance_amount' => 1,
1126 'pledge_payment_id' => 1,
1127 'pledge_payment_scheduled_amount' => 1,
1128 'pledge_payment_scheduled_date' => 1,
1129 'pledge_payment_paid_amount' => 1,
1130 'pledge_payment_paid_date' => 1,
1131 'pledge_payment_reminder_date' => 1,
1132 'pledge_payment_reminder_count' => 1,
1133 'pledge_payment_status' => 1,
1138 * Get membership return properties.
1142 public function getMembershipReturnProperties() {
1144 'location_type' => 1,
1146 'phone_type_id' => 1,
1148 'current_employer' => 1,
1149 'contact_type' => 1,
1150 'contact_sub_type' => 1,
1152 'display_name' => 1,
1153 'membership_type' => 1,
1154 'member_is_test' => 1,
1155 'member_is_pay_later' => 1,
1157 'membership_start_date' => 1,
1158 'membership_end_date' => 1,
1159 'membership_source' => 1,
1160 'membership_status' => 1,
1161 'membership_id' => 1,
1162 'owner_membership_id' => 1,
1164 'membership_recur_id' => 1,
1165 'member_campaign_id' => 1,
1166 'member_is_override' => 1,
1167 'member_auto_renew' => 1,
1172 * Get return properties for events.
1176 public function getEventReturnProperties() {
1178 'contact_type' => 1,
1179 'contact_sub_type' => 1,
1181 'display_name' => 1,
1184 'event_start_date' => 1,
1185 'event_end_date' => 1,
1187 'participant_id' => 1,
1188 'participant_status' => 1,
1189 'participant_status_id' => 1,
1190 'participant_role' => 1,
1191 'participant_role_id' => 1,
1192 'participant_note' => 1,
1193 'participant_register_date' => 1,
1194 'participant_source' => 1,
1195 'participant_fee_level' => 1,
1196 'participant_is_test' => 1,
1197 'participant_is_pay_later' => 1,
1198 'participant_fee_amount' => 1,
1199 'participant_discount_name' => 1,
1200 'participant_fee_currency' => 1,
1201 'participant_registered_by_id' => 1,
1202 'participant_campaign_id' => 1,
1207 * Get return properties for activities.
1211 public function getActivityReturnProperties() {
1214 'contact_type' => 1,
1215 'contact_sub_type' => 1,
1217 'display_name' => 1,
1218 'activity_type' => 1,
1219 'activity_type_id' => 1,
1220 'activity_subject' => 1,
1221 'activity_date_time' => 1,
1222 'activity_duration' => 1,
1223 'activity_location' => 1,
1224 'activity_details' => 1,
1225 'activity_status' => 1,
1226 'activity_priority' => 1,
1227 'source_contact' => 1,
1228 'source_record_id' => 1,
1229 'activity_is_test' => 1,
1230 'activity_campaign_id' => 1,
1232 'activity_engagement_level' => 1,
1238 * Get return properties for Case.
1242 public function getCaseReturnProperties() {
1244 'contact_type' => 1,
1245 'contact_sub_type' => 1,
1247 'display_name' => 1,
1249 'case_start_date' => 1,
1250 'case_end_date' => 1,
1251 'case_subject' => 1,
1252 'case_source_contact_id' => 1,
1253 'case_activity_status' => 1,
1254 'case_activity_duration' => 1,
1255 'case_activity_medium_id' => 1,
1256 'case_activity_details' => 1,
1257 'case_activity_is_auto' => 1,
1260 'case_activity_subject' => 1,
1264 'case_deleted' => 1,
1265 'case_recent_activity_date' => 1,
1266 'case_recent_activity_type' => 1,
1267 'case_scheduled_activity_date' => 1,
1272 * Get return properties for contribution.
1276 public function getContributionReturnProperties() {
1278 'contact_type' => 1,
1279 'contact_sub_type' => 1,
1281 'display_name' => 1,
1282 'financial_type' => 1,
1283 'contribution_source' => 1,
1284 'receive_date' => 1,
1285 'thankyou_date' => 1,
1287 'total_amount' => 1,
1288 'accounting_code' => 1,
1289 'payment_instrument' => 1,
1290 'payment_instrument_id' => 1,
1291 'contribution_check_number' => 1,
1292 'non_deductible_amount' => 1,
1297 'invoice_number' => 1,
1299 'cancel_reason' => 1,
1300 'receipt_date' => 1,
1301 'product_name' => 1,
1303 'product_option' => 1,
1304 'fulfilled_date' => 1,
1305 'contribution_start_date' => 1,
1306 'contribution_end_date' => 1,
1308 'is_pay_later' => 1,
1309 'contribution_status' => 1,
1310 'contribution_recur_id' => 1,
1311 'amount_level' => 1,
1312 'contribution_note' => 1,
1313 'contribution_batch' => 1,
1314 'contribution_campaign_title' => 1,
1315 'contribution_campaign_id' => 1,
1316 'contribution_product_id' => 1,
1317 'contribution_soft_credit_name' => 1,
1318 'contribution_soft_credit_amount' => 1,
1319 'contribution_soft_credit_type' => 1,
1320 'contribution_soft_credit_contact_id' => 1,
1321 'contribution_soft_credit_contribution_id' => 1,
1326 * Test the column definition when 'all' fields defined.
1328 * @param $exportMode
1331 * @dataProvider getSqlColumnsOutput
1333 public function testGetSQLColumns($exportMode, $expected) {
1334 $this->ensureComponentIsEnabled($exportMode);
1335 // We need some data so that we can get to the end of the export
1336 // function. Hopefully one day that won't be required to get metadata info out.
1337 // eventually aspire to call $provider->getSQLColumns straight after it
1339 $this->setupBaseExportData($exportMode);
1341 $result = CRM_Export_BAO_Export
::exportComponents(
1354 'exportOption' => CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
1355 'suppress_csv_for_testing' => TRUE,
1358 $this->assertEquals($expected, $result[1]);
1362 * @param string $exportMode
1364 public function setupBaseExportData($exportMode) {
1365 $this->createLoggedInUser();
1366 if ($exportMode === CRM_Export_Form_Select
::CASE_EXPORT
) {
1367 $this->setupCaseExportData();
1369 if ($exportMode === CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
) {
1370 $this->setUpContributionExportData();
1372 if ($exportMode === CRM_Export_Form_Select
::MEMBER_EXPORT
) {
1373 $this->setUpMembershipExportData();
1375 if ($exportMode === CRM_Export_Form_Select
::ACTIVITY_EXPORT
) {
1376 $this->setUpActivityExportData();
1381 * Get comprehensive sql columns output.
1385 public function getSqlColumnsOutput() {
1388 'anything that will then be defaulting ton contact',
1389 $this->getBasicSqlColumnDefinition(TRUE),
1392 CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
1393 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getActivitySqlColumns()),
1396 CRM_Export_Form_Select
::CASE_EXPORT
,
1397 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getCaseSqlColumns()),
1400 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
1401 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getContributionSqlColumns()),
1404 CRM_Export_Form_Select
::EVENT_EXPORT
,
1405 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getParticipantSqlColumns()),
1408 CRM_Export_Form_Select
::MEMBER_EXPORT
,
1409 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getMembershipSqlColumns()),
1412 CRM_Export_Form_Select
::PLEDGE_EXPORT
,
1413 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getPledgeSqlColumns()),
1420 * Get the column definition for exports.
1422 * @param bool $isContactExport
1426 protected function getBasicSqlColumnDefinition($isContactExport) {
1428 'civicrm_primary_id' => 'civicrm_primary_id varchar(16)',
1429 'contact_type' => 'contact_type varchar(64)',
1430 'contact_sub_type' => 'contact_sub_type varchar(255)',
1431 'do_not_email' => 'do_not_email varchar(16)',
1432 'do_not_phone' => 'do_not_phone varchar(16)',
1433 'do_not_mail' => 'do_not_mail varchar(16)',
1434 'do_not_sms' => 'do_not_sms varchar(16)',
1435 'do_not_trade' => 'do_not_trade varchar(16)',
1436 'is_opt_out' => 'is_opt_out varchar(16)',
1437 'legal_identifier' => 'legal_identifier varchar(32)',
1438 'external_identifier' => 'external_identifier varchar(64)',
1439 'sort_name' => 'sort_name varchar(128)',
1440 'display_name' => 'display_name varchar(128)',
1441 'nick_name' => 'nick_name varchar(128)',
1442 'legal_name' => 'legal_name varchar(128)',
1443 'image_url' => 'image_url longtext',
1444 'preferred_communication_method' => 'preferred_communication_method varchar(255)',
1445 'preferred_language' => 'preferred_language varchar(5)',
1446 'preferred_mail_format' => 'preferred_mail_format varchar(8)',
1447 'hash' => 'hash varchar(32)',
1448 'contact_source' => 'contact_source varchar(255)',
1449 'first_name' => 'first_name varchar(64)',
1450 'middle_name' => 'middle_name varchar(64)',
1451 'last_name' => 'last_name varchar(64)',
1452 'prefix_id' => 'prefix_id varchar(255)',
1453 'suffix_id' => 'suffix_id varchar(255)',
1454 'formal_title' => 'formal_title varchar(64)',
1455 'communication_style_id' => 'communication_style_id varchar(16)',
1456 'email_greeting_id' => 'email_greeting_id varchar(16)',
1457 'postal_greeting_id' => 'postal_greeting_id varchar(16)',
1458 'addressee_id' => 'addressee_id varchar(16)',
1459 'job_title' => 'job_title varchar(255)',
1460 'gender_id' => 'gender_id varchar(16)',
1461 'birth_date' => 'birth_date varchar(32)',
1462 'is_deceased' => 'is_deceased varchar(16)',
1463 'deceased_date' => 'deceased_date varchar(32)',
1464 'household_name' => 'household_name varchar(128)',
1465 'organization_name' => 'organization_name varchar(128)',
1466 'sic_code' => 'sic_code varchar(8)',
1467 'user_unique_id' => 'user_unique_id varchar(255)',
1468 'current_employer_id' => 'current_employer_id varchar(16)',
1469 'contact_is_deleted' => 'contact_is_deleted varchar(16)',
1470 'created_date' => 'created_date varchar(32)',
1471 'modified_date' => 'modified_date varchar(32)',
1472 'addressee' => 'addressee varchar(255)',
1473 'email_greeting' => 'email_greeting varchar(255)',
1474 'postal_greeting' => 'postal_greeting varchar(255)',
1475 'current_employer' => 'current_employer varchar(128)',
1476 'location_type' => 'location_type text',
1477 'street_address' => 'street_address varchar(96)',
1478 'street_number' => 'street_number varchar(16)',
1479 'street_number_suffix' => 'street_number_suffix varchar(8)',
1480 'street_name' => 'street_name varchar(64)',
1481 'street_unit' => 'street_unit varchar(16)',
1482 'supplemental_address_1' => 'supplemental_address_1 varchar(96)',
1483 'supplemental_address_2' => 'supplemental_address_2 varchar(96)',
1484 'supplemental_address_3' => 'supplemental_address_3 varchar(96)',
1485 'city' => 'city varchar(64)',
1486 'postal_code_suffix' => 'postal_code_suffix varchar(12)',
1487 'postal_code' => 'postal_code varchar(64)',
1488 'geo_code_1' => 'geo_code_1 varchar(32)',
1489 'geo_code_2' => 'geo_code_2 varchar(32)',
1490 'address_name' => 'address_name varchar(255)',
1491 'master_id' => 'master_id varchar(128)',
1492 'county' => 'county varchar(64)',
1493 'state_province' => 'state_province varchar(64)',
1494 'country' => 'country varchar(64)',
1495 'phone' => 'phone varchar(32)',
1496 'phone_ext' => 'phone_ext varchar(16)',
1497 'email' => 'email varchar(254)',
1498 'on_hold' => 'on_hold varchar(16)',
1499 'is_bulkmail' => 'is_bulkmail varchar(16)',
1500 'signature_text' => 'signature_text longtext',
1501 'signature_html' => 'signature_html longtext',
1502 'im_provider' => 'im_provider text',
1503 'im' => 'im varchar(64)',
1504 'openid' => 'openid varchar(255)',
1505 'world_region' => 'world_region varchar(128)',
1506 'url' => 'url varchar(128)',
1507 'groups' => 'groups text',
1508 'tags' => 'tags text',
1509 'notes' => 'notes text',
1510 'phone_type_id' => 'phone_type_id varchar(255)',
1511 'provider_id' => 'provider_id varchar(255)',
1513 if (!$isContactExport) {
1514 unset($columns['groups']);
1515 unset($columns['tags']);
1516 unset($columns['notes']);
1522 * Get Case SQL columns.
1526 protected function getCaseSqlColumns() {
1528 'case_start_date' => 'case_start_date varchar(32)',
1529 'case_end_date' => 'case_end_date varchar(32)',
1530 'case_subject' => 'case_subject varchar(128)',
1531 'case_source_contact_id' => 'case_source_contact_id varchar(255)',
1532 'case_activity_status' => 'case_activity_status text',
1533 'case_activity_duration' => 'case_activity_duration text',
1534 'case_activity_medium_id' => 'case_activity_medium_id varchar(255)',
1535 'case_activity_details' => 'case_activity_details text',
1536 'case_activity_is_auto' => 'case_activity_is_auto text',
1537 'contact_id' => 'contact_id varchar(255)',
1538 'case_id' => 'case_id varchar(16)',
1539 'case_activity_subject' => 'case_activity_subject text',
1540 'case_status' => 'case_status text',
1541 'case_type' => 'case_type text',
1542 'case_role' => 'case_role text',
1543 'case_deleted' => 'case_deleted varchar(16)',
1544 'case_recent_activity_date' => 'case_recent_activity_date text',
1545 'case_recent_activity_type' => 'case_recent_activity_type text',
1546 'case_scheduled_activity_date' => 'case_scheduled_activity_date text',
1551 * Get activity sql columns.
1555 protected function getActivitySqlColumns() {
1557 'activity_id' => 'activity_id varchar(16)',
1558 'activity_type' => 'activity_type varchar(255)',
1559 'activity_type_id' => 'activity_type_id varchar(16)',
1560 'activity_subject' => 'activity_subject varchar(255)',
1561 'activity_date_time' => 'activity_date_time varchar(32)',
1562 'activity_duration' => 'activity_duration varchar(16)',
1563 'activity_location' => 'activity_location varchar(255)',
1564 'activity_details' => 'activity_details longtext',
1565 'activity_status' => 'activity_status varchar(255)',
1566 'activity_priority' => 'activity_priority varchar(255)',
1567 'source_contact' => 'source_contact varchar(255)',
1568 'source_record_id' => 'source_record_id varchar(255)',
1569 'activity_is_test' => 'activity_is_test varchar(16)',
1570 'activity_campaign_id' => 'activity_campaign_id varchar(128)',
1571 'result' => 'result text',
1572 'activity_engagement_level' => 'activity_engagement_level varchar(16)',
1573 'parent_id' => 'parent_id varchar(255)',
1578 * Get participant sql columns.
1582 protected function getParticipantSqlColumns() {
1584 'event_id' => 'event_id varchar(16)',
1585 'event_title' => 'event_title varchar(255)',
1586 'event_start_date' => 'event_start_date varchar(32)',
1587 'event_end_date' => 'event_end_date varchar(32)',
1588 'event_type' => 'event_type varchar(255)',
1589 'participant_id' => 'participant_id varchar(16)',
1590 'participant_status' => 'participant_status varchar(255)',
1591 'participant_status_id' => 'participant_status_id varchar(16)',
1592 'participant_role' => 'participant_role varchar(255)',
1593 'participant_role_id' => 'participant_role_id varchar(128)',
1594 'participant_note' => 'participant_note text',
1595 'participant_register_date' => 'participant_register_date varchar(32)',
1596 'participant_source' => 'participant_source varchar(128)',
1597 'participant_fee_level' => 'participant_fee_level longtext',
1598 'participant_is_test' => 'participant_is_test varchar(16)',
1599 'participant_is_pay_later' => 'participant_is_pay_later varchar(16)',
1600 'participant_fee_amount' => 'participant_fee_amount varchar(32)',
1601 'participant_discount_name' => 'participant_discount_name varchar(16)',
1602 'participant_fee_currency' => 'participant_fee_currency varchar(3)',
1603 'participant_registered_by_id' => 'participant_registered_by_id varchar(16)',
1604 'participant_campaign_id' => 'participant_campaign_id varchar(128)',
1609 * Get contribution sql columns.
1613 public function getContributionSqlColumns() {
1615 'civicrm_primary_id' => 'civicrm_primary_id varchar(16)',
1616 'contact_type' => 'contact_type varchar(64)',
1617 'contact_sub_type' => 'contact_sub_type varchar(255)',
1618 'do_not_email' => 'do_not_email varchar(16)',
1619 'do_not_phone' => 'do_not_phone varchar(16)',
1620 'do_not_mail' => 'do_not_mail varchar(16)',
1621 'do_not_sms' => 'do_not_sms varchar(16)',
1622 'do_not_trade' => 'do_not_trade varchar(16)',
1623 'is_opt_out' => 'is_opt_out varchar(16)',
1624 'legal_identifier' => 'legal_identifier varchar(32)',
1625 'external_identifier' => 'external_identifier varchar(64)',
1626 'sort_name' => 'sort_name varchar(128)',
1627 'display_name' => 'display_name varchar(128)',
1628 'nick_name' => 'nick_name varchar(128)',
1629 'legal_name' => 'legal_name varchar(128)',
1630 'image_url' => 'image_url longtext',
1631 'preferred_communication_method' => 'preferred_communication_method varchar(255)',
1632 'preferred_language' => 'preferred_language varchar(5)',
1633 'preferred_mail_format' => 'preferred_mail_format varchar(8)',
1634 'hash' => 'hash varchar(32)',
1635 'contact_source' => 'contact_source varchar(255)',
1636 'first_name' => 'first_name varchar(64)',
1637 'middle_name' => 'middle_name varchar(64)',
1638 'last_name' => 'last_name varchar(64)',
1639 'prefix_id' => 'prefix_id varchar(255)',
1640 'suffix_id' => 'suffix_id varchar(255)',
1641 'formal_title' => 'formal_title varchar(64)',
1642 'communication_style_id' => 'communication_style_id varchar(16)',
1643 'email_greeting_id' => 'email_greeting_id varchar(16)',
1644 'postal_greeting_id' => 'postal_greeting_id varchar(16)',
1645 'addressee_id' => 'addressee_id varchar(16)',
1646 'job_title' => 'job_title varchar(255)',
1647 'gender_id' => 'gender_id varchar(16)',
1648 'birth_date' => 'birth_date varchar(32)',
1649 'is_deceased' => 'is_deceased varchar(16)',
1650 'deceased_date' => 'deceased_date varchar(32)',
1651 'household_name' => 'household_name varchar(128)',
1652 'organization_name' => 'organization_name varchar(128)',
1653 'sic_code' => 'sic_code varchar(8)',
1654 'user_unique_id' => 'user_unique_id varchar(255)',
1655 'current_employer_id' => 'current_employer_id varchar(16)',
1656 'contact_is_deleted' => 'contact_is_deleted varchar(16)',
1657 'created_date' => 'created_date varchar(32)',
1658 'modified_date' => 'modified_date varchar(32)',
1659 'addressee' => 'addressee varchar(255)',
1660 'email_greeting' => 'email_greeting varchar(255)',
1661 'postal_greeting' => 'postal_greeting varchar(255)',
1662 'current_employer' => 'current_employer varchar(128)',
1663 'location_type' => 'location_type text',
1664 'street_address' => 'street_address varchar(96)',
1665 'street_number' => 'street_number varchar(16)',
1666 'street_number_suffix' => 'street_number_suffix varchar(8)',
1667 'street_name' => 'street_name varchar(64)',
1668 'street_unit' => 'street_unit varchar(16)',
1669 'supplemental_address_1' => 'supplemental_address_1 varchar(96)',
1670 'supplemental_address_2' => 'supplemental_address_2 varchar(96)',
1671 'supplemental_address_3' => 'supplemental_address_3 varchar(96)',
1672 'city' => 'city varchar(64)',
1673 'postal_code_suffix' => 'postal_code_suffix varchar(12)',
1674 'postal_code' => 'postal_code varchar(64)',
1675 'geo_code_1' => 'geo_code_1 varchar(32)',
1676 'geo_code_2' => 'geo_code_2 varchar(32)',
1677 'address_name' => 'address_name varchar(255)',
1678 'master_id' => 'master_id varchar(128)',
1679 'county' => 'county varchar(64)',
1680 'state_province' => 'state_province varchar(64)',
1681 'country' => 'country varchar(64)',
1682 'phone' => 'phone varchar(32)',
1683 'phone_ext' => 'phone_ext varchar(16)',
1684 'email' => 'email varchar(254)',
1685 'on_hold' => 'on_hold varchar(16)',
1686 'is_bulkmail' => 'is_bulkmail varchar(16)',
1687 'signature_text' => 'signature_text longtext',
1688 'signature_html' => 'signature_html longtext',
1689 'im_provider' => 'im_provider text',
1690 'im' => 'im varchar(64)',
1691 'openid' => 'openid varchar(255)',
1692 'world_region' => 'world_region varchar(128)',
1693 'url' => 'url varchar(128)',
1694 'phone_type_id' => 'phone_type_id varchar(255)',
1695 'provider_id' => 'provider_id varchar(255)',
1696 'financial_type' => 'financial_type varchar(64)',
1697 'contribution_source' => 'contribution_source varchar(255)',
1698 'receive_date' => 'receive_date varchar(32)',
1699 'thankyou_date' => 'thankyou_date varchar(32)',
1700 'cancel_date' => 'cancel_date varchar(32)',
1701 'total_amount' => 'total_amount varchar(32)',
1702 'accounting_code' => 'accounting_code varchar(64)',
1703 'payment_instrument' => 'payment_instrument text',
1704 'payment_instrument_id' => 'payment_instrument_id varchar(16)',
1705 'contribution_check_number' => 'contribution_check_number varchar(255)',
1706 'non_deductible_amount' => 'non_deductible_amount varchar(32)',
1707 'fee_amount' => 'fee_amount varchar(32)',
1708 'net_amount' => 'net_amount varchar(32)',
1709 'trxn_id' => 'trxn_id varchar(255)',
1710 'invoice_id' => 'invoice_id varchar(255)',
1711 'invoice_number' => 'invoice_number varchar(255)',
1712 'currency' => 'currency varchar(3)',
1713 'cancel_reason' => 'cancel_reason longtext',
1714 'receipt_date' => 'receipt_date varchar(32)',
1715 'product_name' => 'product_name varchar(255)',
1716 'sku' => 'sku varchar(50)',
1717 'product_option' => 'product_option varchar(255)',
1718 'fulfilled_date' => 'fulfilled_date varchar(32)',
1719 'contribution_start_date' => 'contribution_start_date varchar(32)',
1720 'contribution_end_date' => 'contribution_end_date varchar(32)',
1721 'is_test' => 'is_test varchar(16)',
1722 'is_pay_later' => 'is_pay_later varchar(16)',
1723 'contribution_status' => 'contribution_status text',
1724 'contribution_recur_id' => 'contribution_recur_id varchar(16)',
1725 'amount_level' => 'amount_level longtext',
1726 'contribution_note' => 'contribution_note text',
1727 'contribution_batch' => 'contribution_batch text',
1728 'contribution_campaign_title' => 'contribution_campaign_title varchar(255)',
1729 'contribution_campaign_id' => 'contribution_campaign_id varchar(128)',
1730 'contribution_product_id' => 'contribution_product_id varchar(255)',
1731 'contribution_soft_credit_name' => 'contribution_soft_credit_name varchar(255)',
1732 'contribution_soft_credit_amount' => 'contribution_soft_credit_amount varchar(255)',
1733 'contribution_soft_credit_type' => 'contribution_soft_credit_type varchar(255)',
1734 'contribution_soft_credit_contact_id' => 'contribution_soft_credit_contact_id varchar(255)',
1735 'contribution_soft_credit_contribution_id' => 'contribution_soft_credit_contribution_id varchar(255)',
1740 * Get pledge sql columns.
1744 public function getPledgeSqlColumns() {
1746 'pledge_id' => 'pledge_id varchar(16)',
1747 'pledge_amount' => 'pledge_amount varchar(32)',
1748 'pledge_total_paid' => 'pledge_total_paid text',
1749 'pledge_create_date' => 'pledge_create_date varchar(32)',
1750 'pledge_start_date' => 'pledge_start_date text',
1751 'pledge_next_pay_date' => 'pledge_next_pay_date text',
1752 'pledge_next_pay_amount' => 'pledge_next_pay_amount text',
1753 'pledge_status' => 'pledge_status varchar(255)',
1754 'pledge_is_test' => 'pledge_is_test varchar(16)',
1755 'pledge_contribution_page_id' => 'pledge_contribution_page_id varchar(255)',
1756 'pledge_financial_type' => 'pledge_financial_type text',
1757 'pledge_frequency_interval' => 'pledge_frequency_interval varchar(255)',
1758 'pledge_frequency_unit' => 'pledge_frequency_unit varchar(255)',
1759 'pledge_currency' => 'pledge_currency text',
1760 'pledge_campaign_id' => 'pledge_campaign_id varchar(128)',
1761 'pledge_balance_amount' => 'pledge_balance_amount text',
1762 'pledge_payment_id' => 'pledge_payment_id varchar(16)',
1763 'pledge_payment_scheduled_amount' => 'pledge_payment_scheduled_amount varchar(32)',
1764 'pledge_payment_scheduled_date' => 'pledge_payment_scheduled_date varchar(32)',
1765 'pledge_payment_paid_amount' => 'pledge_payment_paid_amount text',
1766 'pledge_payment_paid_date' => 'pledge_payment_paid_date text',
1767 'pledge_payment_reminder_date' => 'pledge_payment_reminder_date varchar(32)',
1768 'pledge_payment_reminder_count' => 'pledge_payment_reminder_count varchar(16)',
1769 'pledge_payment_status' => 'pledge_payment_status varchar(255)',
1774 * Get membership sql columns.
1778 public function getMembershipSqlColumns() {
1780 'membership_type' => 'membership_type varchar(128)',
1781 'member_is_test' => 'member_is_test varchar(16)',
1782 'member_is_pay_later' => 'member_is_pay_later varchar(16)',
1783 'join_date' => 'join_date varchar(32)',
1784 'membership_start_date' => 'membership_start_date varchar(32)',
1785 'membership_end_date' => 'membership_end_date varchar(32)',
1786 'membership_source' => 'membership_source varchar(128)',
1787 'membership_status' => 'membership_status varchar(255)',
1788 'membership_id' => 'membership_id varchar(16)',
1789 'owner_membership_id' => 'owner_membership_id varchar(16)',
1790 'max_related' => 'max_related text',
1791 'membership_recur_id' => 'membership_recur_id varchar(255)',
1792 'member_campaign_id' => 'member_campaign_id varchar(128)',
1793 'member_is_override' => 'member_is_override text',
1794 'member_auto_renew' => 'member_auto_renew text',