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 protected $locationTypes = [];
47 public function tearDown() {
52 'civicrm_relationship',
55 'civicrm_case_contact',
56 'civicrm_case_activity',
58 $this->quickCleanUpFinancialEntities();
59 if (!empty($this->locationTypes
)) {
60 $this->callAPISuccess('LocationType', 'delete', ['id' => $this->locationTypes
['Whare Kai']['id']]);
61 $this->callAPISuccess('LocationType', 'create', ['id' => $this->locationTypes
['Main']['id'], 'name' => 'Main']);
67 * Basic test to ensure the exportComponents function completes without error.
69 public function testExportComponentsNull() {
70 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
77 CRM_Export_Form_Select
::CONTACT_EXPORT
,
84 'suppress_csv_for_testing' => TRUE,
88 // delete the export temp table and component table
89 $sql = "DROP TABLE IF EXISTS {$tableName}";
90 CRM_Core_DAO
::executeQuery($sql);
94 * Basic test to ensure the exportComponents function can export selected fields for contribution.
96 public function testExportComponentsContribution() {
97 $this->setUpContributionExportData();
98 $selectedFields = array(
99 array('Individual', 'first_name'),
100 array('Individual', 'last_name'),
101 array('Contribution', 'receive_date'),
102 array('Contribution', 'contribution_source'),
103 array('Individual', 'street_address', 1),
104 array('Individual', 'city', 1),
105 array('Individual', 'country', 1),
106 array('Individual', 'email', 1),
107 array('Contribution', 'trxn_id'),
110 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
112 $this->contributionIDs
,
117 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
118 'civicrm_contribution.id IN ( ' . implode(',', $this->contributionIDs
) . ')',
123 'exportOption' => CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
124 'suppress_csv_for_testing' => TRUE,
128 // delete the export temp table and component table
129 $sql = "DROP TABLE IF EXISTS {$tableName}";
130 CRM_Core_DAO
::executeQuery($sql);
134 * Basic test to ensure the exportComponents function can export selected fields for contribution.
136 public function testExportComponentsMembership() {
137 $this->setUpMembershipExportData();
138 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
140 $this->membershipIDs
,
145 CRM_Export_Form_Select
::MEMBER_EXPORT
,
146 'civicrm_membership.id IN ( ' . implode(',', $this->membershipIDs
) . ')',
151 'exportOption' => CRM_Export_Form_Select
::MEMBER_EXPORT
,
152 'suppress_csv_for_testing' => TRUE,
156 $dao = CRM_Core_DAO
::executeQuery('SELECT * from ' . $tableName);
158 $this->assertEquals('100.00', $dao->componentpaymentfield_total_amount
);
159 $this->assertEquals('Completed', $dao->componentpaymentfield_contribution_status
);
160 $this->assertEquals('Credit Card', $dao->componentpaymentfield_payment_instrument
);
161 $this->assertEquals(1, $dao->N
);
163 // delete the export temp table and component table
164 $sql = "DROP TABLE IF EXISTS {$tableName}";
165 CRM_Core_DAO
::executeQuery($sql);
169 * Basic test to ensure the exportComponents function can export selected fields for contribution.
171 public function testExportComponentsActivity() {
172 $this->setUpActivityExportData();
173 $selectedFields = array(
174 array('Individual', 'display_name'),
175 array('Individual', '5_a_b', 'display_name'),
178 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
182 '`activity_date_time` desc',
185 CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
186 'civicrm_activity.id IN ( ' . implode(',', $this->activityIDs
) . ')',
191 'exportOption' => CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
192 'suppress_csv_for_testing' => TRUE,
196 // delete the export temp table and component table
197 $sql = "DROP TABLE IF EXISTS {$tableName}";
198 CRM_Core_DAO
::executeQuery($sql);
202 * Test the function that extracts the arrays used to structure the output.
204 * The keys in the output fields array should by matched by field aliases in the sql query (with
205 * exceptions of course - currently country is one - although maybe a future refactor can change that!).
207 * We are trying to move towards simpler processing in the per row iteration as that may be
208 * repeated 100,000 times and in general we should simply be able to match the query fields to
209 * our expected rows & do a little pseudoconstant mapping.
211 public function testGetExportStructureArrays() {
212 // This is how return properties are formatted internally within the function for passing to the BAO query.
213 $returnProperties = array(
217 'contribution_source' => 1,
220 'street_address' => 1,
231 'contribution_id' => 1,
234 $contactRelationshipTypes = CRM_Contact_BAO_Relationship
::getContactRelationshipType(
244 $query = new CRM_Contact_BAO_Query(array(), $returnProperties, NULL,
245 FALSE, FALSE, CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
,
246 FALSE, TRUE, TRUE, NULL, 'AND'
249 list($select) = $query->query();
250 $pattern = '/as `?([^`,]*)/';
251 $queryFieldAliases = array();
252 preg_match_all($pattern, $select, $queryFieldAliases, PREG_PATTERN_ORDER
);
253 $processor = new CRM_Export_BAO_ExportProcessor(CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
, NULL, 'AND');
254 $processor->setQueryFields($query->_fields
);
256 list($outputFields) = CRM_Export_BAO_Export
::getExportStructureArrays($returnProperties, $processor, $contactRelationshipTypes, '');
257 foreach (array_keys($outputFields) as $fieldAlias) {
258 if ($fieldAlias == 'Home-country') {
259 $this->assertTrue(in_array($fieldAlias . '_id', $queryFieldAliases[1]), 'Country is subject to some funky translate so we make sure country id is present');
262 $this->assertTrue(in_array($fieldAlias, $queryFieldAliases[1]), 'looking for field ' . $fieldAlias . ' in generaly the alias fields need to match the outputfields');
269 * Set up some data for us to do testing on.
271 public function setUpContributionExportData() {
272 $this->setUpContactExportData();
273 $this->contributionIDs
[] = $this->contributionCreate(array('contact_id' => $this->contactIDs
[0], 'trxn_id' => 'null', 'invoice_id' => 'null'));
274 $this->contributionIDs
[] = $this->contributionCreate(array('contact_id' => $this->contactIDs
[1], 'trxn_id' => 'null', 'invoice_id' => 'null'));
278 * Set up some data for us to do testing on.
280 public function setUpMembershipExportData() {
281 $this->setUpContactExportData();
282 // Create an extra so we don't get false passes due to 1
283 $this->contactMembershipCreate(['contact_id' => $this->contactIDs
[0]]);
284 $this->membershipIDs
[] = $this->contactMembershipCreate(['contact_id' => $this->contactIDs
[0]]);
285 $this->setUpContributionExportData();
286 $this->callAPISuccess('membership_payment', 'create', array(
287 'contribution_id' => $this->contributionIDs
[0],
288 'membership_id' => $this->membershipIDs
[0],
290 $this->callAPISuccess('LineItem', 'get', [
291 'entity_table' => 'civicrm_membership',
292 'membership_id' => $this->membershipIDs
[0],
293 'api.LineItem.create' => ['contribution_id' => $this->contributionIDs
[0]],
298 * Set up data to test case export.
300 public function setupCaseExportData() {
301 $contactID1 = $this->individualCreate();
302 $contactID2 = $this->individualCreate(array(), 1);
304 $case = $this->callAPISuccess('case', 'create', array(
307 'contact_id' => $contactID1,
309 $this->callAPISuccess('CaseContact', 'create', [
310 'case_id' => $case['id'],
311 'contact_id' => $contactID2,
316 * Set up some data for us to do testing on.
318 public function setUpActivityExportData() {
319 $this->setUpContactExportData();
320 $this->activityIDs
[] = $this->activityCreate(array('contact_id' => $this->contactIDs
[0]))['id'];
324 * Set up some data for us to do testing on.
326 public function setUpContactExportData() {
327 $this->contactIDs
[] = $contactA = $this->individualCreate(['gender_id' => 'Female']);
328 // Create address for contact A.
330 'contact_id' => $contactA,
331 'location_type_id' => 'Home',
332 'street_address' => 'Ambachtstraat 23',
333 'postal_code' => '6971 BN',
334 'country_id' => '1152',
338 $result = $this->callAPISuccess('address', 'create', $params);
339 $addressId = $result['id'];
341 $this->callAPISuccess('email', 'create', array(
342 'id' => $this->callAPISuccessGetValue('Email', ['contact_id' => $params['contact_id'], 'return' => 'id']),
343 'location_type_id' => 'Home',
344 'email' => 'home@example.com',
347 $this->callAPISuccess('email', 'create', array('contact_id' => $params['contact_id'], 'location_type_id' => 'Work', 'email' => 'work@example.com', 'is_primary' => 0));
349 $params['is_primary'] = 0;
350 $params['location_type_id'] = 'Work';
351 $this->callAPISuccess('address', 'create', $params);
352 $this->contactIDs
[] = $contactB = $this->individualCreate();
354 $this->callAPISuccess('address', 'create', array(
355 'contact_id' => $contactB,
356 'location_type_id' => "Home",
357 'master_id' => $addressId,
359 $this->masterAddressID
= $addressId;
364 * Test variants of primary address exporting.
366 * @param int $isPrimaryOnly
368 * @dataProvider getPrimarySearchOptions
370 public function testExportPrimaryAddress($isPrimaryOnly) {
371 \Civi
::settings()->set('searchPrimaryDetailsOnly', $isPrimaryOnly);
372 $this->setUpContactExportData();
374 $selectedFields = [['Individual', 'email', ' '], ['Individual', 'email', '1'], ['Individual', 'email', '2']];
375 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
378 [['email', 'LIKE', 'c', 0, 1]],
382 CRM_Export_Form_Select
::CONTACT_EXPORT
,
383 "contact_a.id IN ({$this->contactIDs[0]}, {$this->contactIDs[1]})",
388 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
389 'suppress_csv_for_testing' => TRUE,
393 $dao = CRM_Core_DAO
::executeQuery('SELECT * from ' . $tableName);
395 $this->assertEquals('home@example.com', $dao->email
);
396 $this->assertEquals('work@example.com', $dao->work_email
);
397 $this->assertEquals('home@example.com', $dao->home_email
);
398 $this->assertEquals(2, $dao->N
);
399 \Civi
::settings()->set('searchPrimaryDetailsOnly', FALSE);
403 * Get the options for the primary search setting field.
406 public function getPrimarySearchOptions() {
407 return [[TRUE], [FALSE]];
411 * Test that when exporting a pseudoField it is reset for NULL entries.
413 * ie. we have a contact WITH a gender & one without - make sure the latter one
414 * does NOT retain the gender of the former.
416 public function testExportPseudoField() {
417 $this->setUpContactExportData();
418 $selectedFields = [['Individual', 'gender_id']];
419 list($tableName, $sqlColumns) = $this->doExport($selectedFields, $this->contactIDs
);
420 $this->assertEquals('Female,', CRM_Core_DAO
::singleValueQuery("SELECT GROUP_CONCAT(gender_id) FROM {$tableName}"));
424 * Test that when exporting a pseudoField it is reset for NULL entries.
426 * This is specific to the example in CRM-14398
428 public function testExportPseudoFieldCampaign() {
429 $this->setUpContributionExportData();
430 $campaign = $this->callAPISuccess('Campaign', 'create', ['title' => 'Big campaign']);
431 $this->callAPISuccess('Contribution', 'create', ['campaign_id' => 'Big_campaign', 'id' => $this->contributionIDs
[0]]);
432 $selectedFields = [['Individual', 'gender_id'], ['Contribution', 'contribution_campaign_title']];
433 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
435 $this->contactIDs
[1],
440 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
441 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
446 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
447 'suppress_csv_for_testing' => TRUE,
450 $this->assertEquals('Big campaign,', CRM_Core_DAO
::singleValueQuery("SELECT GROUP_CONCAT(contribution_campaign_title) FROM {$tableName}"));
454 * Test exporting relationships.
456 public function testExportRelationships() {
457 $organization1 = $this->organizationCreate(['organization_name' => 'Org 1', 'legal_name' => 'pretty legal', 'contact_source' => 'friend who took a law paper once']);
458 $organization2 = $this->organizationCreate(['organization_name' => 'Org 2', 'legal_name' => 'well dodgey']);
459 $contact1 = $this->individualCreate(['employer_id' => $organization1, 'first_name' => 'one']);
460 $contact2 = $this->individualCreate(['employer_id' => $organization2, 'first_name' => 'one']);
461 $employerRelationshipTypeID = $this->callAPISuccessGetValue('RelationshipType', ['return' => 'id', 'label_a_b' => 'Employee of']);
463 ['Individual', 'first_name', ''],
464 ['Individual', $employerRelationshipTypeID . '_a_b', 'organization_name', ''],
465 ['Individual', $employerRelationshipTypeID . '_a_b', 'legal_name', ''],
466 ['Individual', $employerRelationshipTypeID . '_a_b', 'contact_source', ''],
468 list($tableName, $sqlColumns, $headerRows) = CRM_Export_BAO_Export
::exportComponents(
470 [$contact1, $contact2],
475 CRM_Export_Form_Select
::CONTACT_EXPORT
,
476 "contact_a.id IN ( $contact1, $contact2 )",
481 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
482 'suppress_csv_for_testing' => TRUE,
486 $dao = CRM_Core_DAO
::executeQuery("SELECT * FROM {$tableName}");
488 $this->assertEquals('one', $dao->first_name
);
489 $this->assertEquals('Org 1', $dao->{$employerRelationshipTypeID . '_a_b_organization_name'});
490 $this->assertEquals('pretty legal', $dao->{$employerRelationshipTypeID . '_a_b_legal_name'});
491 $this->assertEquals('friend who took a law paper once', $dao->{$employerRelationshipTypeID . '_a_b_contact_source'});
494 $this->assertEquals('Org 2', $dao->{$employerRelationshipTypeID . '_a_b_organization_name'});
495 $this->assertEquals('well dodgey', $dao->{$employerRelationshipTypeID . '_a_b_legal_name'});
497 $this->assertEquals([
499 1 => 'Employee of-Organization Name',
500 2 => 'Employee of-Legal Name',
501 3 => 'Employee of-Contact Source',
506 * Test exporting relationships.
508 * This is to ensure that CRM-13995 remains fixed.
510 public function testExportRelationshipsMergeToHousehold() {
511 list($householdID, $houseHoldTypeID) = $this->setUpHousehold();
514 ['Individual', $houseHoldTypeID . '_a_b', 'state_province', ''],
515 ['Individual', $houseHoldTypeID . '_a_b', 'city', ''],
516 ['Individual', 'city', ''],
517 ['Individual', 'state_province', ''],
518 ['Individual', 'contact_source', ''],
520 list($tableName, $sqlColumns, $headerRows) = CRM_Export_BAO_Export
::exportComponents(
527 CRM_Export_Form_Select
::CONTACT_EXPORT
,
528 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
533 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
534 'suppress_csv_for_testing' => TRUE,
537 $dao = CRM_Core_DAO
::executeQuery("SELECT * FROM {$tableName}");
538 while ($dao->fetch()) {
539 $this->assertEquals('Portland', $dao->city
);
540 $this->assertEquals('ME', $dao->state_province
);
541 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
542 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
543 $this->assertEquals('household sauce', $dao->contact_source
);
546 $this->assertEquals([
549 2 => 'Contact Source',
554 'city' => 'city varchar(64)',
555 'state_province' => 'state_province varchar(64)',
556 'civicrm_primary_id' => 'civicrm_primary_id varchar(16)',
557 'contact_source' => 'contact_source varchar(255)',
562 * Test exporting relationships.
564 public function testExportRelationshipsMergeToHouseholdAllFields() {
565 list($householdID) = $this->setUpHousehold();
566 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
573 CRM_Export_Form_Select
::CONTACT_EXPORT
,
574 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
579 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
580 'suppress_csv_for_testing' => TRUE,
583 $dao = CRM_Core_DAO
::executeQuery("SELECT * FROM {$tableName}");
584 while ($dao->fetch()) {
585 $this->assertEquals('Unit Test household', $dao->display_name
);
586 $this->assertEquals('Portland', $dao->city
);
587 $this->assertEquals('ME', $dao->state_province
);
588 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
589 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
590 $this->assertEquals('Unit Test household', $dao->addressee
);
591 $this->assertEquals(1, $dao->N
);
596 * Test master_address_id field.
598 public function testExportCustomData() {
599 $this->setUpContactExportData();
601 $customData = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTest.php');
603 $this->callAPISuccess('Contact', 'create', [
604 'id' => $this->contactIDs
[1],
605 'custom_' . $customData['custom_field_id'] => 'BlahdeBlah',
606 'api.Address.create' => ['location_type_id' => 'Billing', 'city' => 'Waipu'],
609 ['Individual', 'city', CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Address', 'location_type_id', 'Billing')],
610 ['Individual', 'custom_1'],
613 list($tableName, $sqlColumns) = $this->doExport($selectedFields, $this->contactIDs
[1]);
614 $this->assertEquals([
615 'billing_city' => 'billing_city varchar(64)',
616 'custom_1' => 'custom_1 varchar(255)',
619 $dao = CRM_Core_DAO
::executeQuery('SELECT * FROM ' . $tableName);
620 while ($dao->fetch()) {
621 $this->assertEquals('BlahdeBlah', $dao->custom_1
);
622 $this->assertEquals('Waipu', $dao->billing_city
);
627 * Attempt to do a fairly full export of location data.
629 public function testExportIMData() {
630 // Use default providers.
631 $providers = ['AIM', 'GTalk', 'Jabber', 'MSN', 'Skype', 'Yahoo'];
632 // Main sure labels are not all anglo chars.
633 $this->diversifyLocationTypes();
635 $locationTypes = ['Billing' => 'Billing', 'Home' => 'Home', 'Main' => 'Méin', 'Other' => 'Other', 'Whare Kai' => 'Whare Kai'];
637 $this->contactIDs
[] = $this->individualCreate();
638 $this->contactIDs
[] = $this->individualCreate();
639 $this->contactIDs
[] = $this->householdCreate();
640 $this->contactIDs
[] = $this->organizationCreate();
641 foreach ($this->contactIDs
as $contactID) {
642 foreach ($providers as $provider) {
643 foreach ($locationTypes as $locationName => $locationLabel) {
644 $this->callAPISuccess('IM', 'create', [
645 'contact_id' => $contactID,
646 'location_type_id' => $locationName,
647 'provider_id' => $provider,
648 'name' => $locationName . $provider . $contactID,
655 $this->contactIDs
[1] => ['label' => 'Spouse of'],
656 $this->contactIDs
[2] => ['label' => 'Household Member of'],
657 $this->contactIDs
[3] => ['label' => 'Employee of']
660 foreach ($relationships as $contactID => $relationshipType) {
661 $relationshipTypeID = $this->callAPISuccess('RelationshipType', 'getvalue', ['label_a_b' => $relationshipType['label'], 'return' => 'id']);
662 $result = $this->callAPISuccess('Relationship', 'create', [
663 'contact_id_a' => $this->contactIDs
[0],
664 'relationship_type_id' => $relationshipTypeID,
665 'contact_id_b' => $contactID
667 $relationships[$contactID]['id'] = $result['id'];
668 $relationships[$contactID]['relationship_type_id'] = $relationshipTypeID;
671 $fields = [['Individual', 'contact_id']];
672 // ' ' denotes primary location type.
673 foreach (array_keys(array_merge($locationTypes, [' ' => ['Primary']])) as $locationType) {
677 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
679 foreach ($relationships as $contactID => $relationship) {
682 $relationship['relationship_type_id'] . '_a_b',
684 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
687 foreach ($providers as $provider) {
691 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
692 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'provider_id', $provider),
694 foreach ($relationships as $contactID => $relationship) {
697 $relationship['relationship_type_id'] . '_a_b',
699 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
700 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'provider_id', $provider),
705 list($tableName, $sqlColumns) = $this->doExport($fields, $this->contactIDs
[0]);
707 $dao = CRM_Core_DAO
::executeQuery('SELECT * FROM ' . $tableName);
708 while ($dao->fetch()) {
709 $id = $dao->contact_id
;
710 $this->assertEquals('AIM', $dao->billing_im_provider
);
711 $this->assertEquals('BillingJabber' . $id, $dao->billing_im_screen_name_jabber
);
712 $this->assertEquals('BillingSkype' . $id, $dao->billing_im_screen_name_skype
);
713 foreach ($relationships as $relatedContactID => $relationship) {
714 $relationshipString = $field = $relationship['relationship_type_id'] . '_a_b';
715 $field = $relationshipString . '_billing_im_screen_name_yahoo';
716 $this->assertEquals('BillingYahoo' . $relatedContactID, $dao->$field);
717 // @todo efforts to output 'im_provider' for related contacts seem to be giving a blank field.
721 $this->assertEquals([
722 'billing_im_provider' => 'billing_im_provider text',
723 'billing_im_screen_name' => 'billing_im_screen_name varchar(64)',
724 'billing_im_screen_name_jabber' => 'billing_im_screen_name_jabber varchar(64)',
725 'billing_im_screen_name_skype' => 'billing_im_screen_name_skype varchar(64)',
726 'billing_im_screen_name_yahoo' => 'billing_im_screen_name_yahoo varchar(64)',
727 'home_im_provider' => 'home_im_provider text',
728 'home_im_screen_name' => 'home_im_screen_name varchar(64)',
729 'home_im_screen_name_jabber' => 'home_im_screen_name_jabber varchar(64)',
730 'home_im_screen_name_skype' => 'home_im_screen_name_skype varchar(64)',
731 'home_im_screen_name_yahoo' => 'home_im_screen_name_yahoo varchar(64)',
732 'main_im_provider' => 'main_im_provider text',
733 'main_im_screen_name' => 'main_im_screen_name varchar(64)',
734 'main_im_screen_name_jabber' => 'main_im_screen_name_jabber varchar(64)',
735 'main_im_screen_name_skype' => 'main_im_screen_name_skype varchar(64)',
736 'main_im_screen_name_yahoo' => 'main_im_screen_name_yahoo varchar(64)',
737 'other_im_provider' => 'other_im_provider text',
738 'other_im_screen_name' => 'other_im_screen_name varchar(64)',
739 'other_im_screen_name_jabber' => 'other_im_screen_name_jabber varchar(64)',
740 'other_im_screen_name_skype' => 'other_im_screen_name_skype varchar(64)',
741 'other_im_screen_name_yahoo' => 'other_im_screen_name_yahoo varchar(64)',
742 'im_provider' => 'im_provider text',
743 'im_screen_name' => 'im_screen_name varchar(64)',
744 'contact_id' => 'contact_id varchar(255)',
745 '2_a_b_im_provider' => '2_a_b_im_provider text',
746 '2_a_b_billing_im_screen_name' => '2_a_b_billing_im_screen_name varchar(64)',
747 '2_a_b_billing_im_screen_name_jabber' => '2_a_b_billing_im_screen_name_jabber varchar(64)',
748 '2_a_b_billing_im_screen_name_skype' => '2_a_b_billing_im_screen_name_skype varchar(64)',
749 '2_a_b_billing_im_screen_name_yahoo' => '2_a_b_billing_im_screen_name_yahoo varchar(64)',
750 '2_a_b_home_im_screen_name' => '2_a_b_home_im_screen_name varchar(64)',
751 '2_a_b_home_im_screen_name_jabber' => '2_a_b_home_im_screen_name_jabber varchar(64)',
752 '2_a_b_home_im_screen_name_skype' => '2_a_b_home_im_screen_name_skype varchar(64)',
753 '2_a_b_home_im_screen_name_yahoo' => '2_a_b_home_im_screen_name_yahoo varchar(64)',
754 '2_a_b_main_im_screen_name' => '2_a_b_main_im_screen_name varchar(64)',
755 '2_a_b_main_im_screen_name_jabber' => '2_a_b_main_im_screen_name_jabber varchar(64)',
756 '2_a_b_main_im_screen_name_skype' => '2_a_b_main_im_screen_name_skype varchar(64)',
757 '2_a_b_main_im_screen_name_yahoo' => '2_a_b_main_im_screen_name_yahoo varchar(64)',
758 '2_a_b_other_im_screen_name' => '2_a_b_other_im_screen_name varchar(64)',
759 '2_a_b_other_im_screen_name_jabber' => '2_a_b_other_im_screen_name_jabber varchar(64)',
760 '2_a_b_other_im_screen_name_skype' => '2_a_b_other_im_screen_name_skype varchar(64)',
761 '2_a_b_other_im_screen_name_yahoo' => '2_a_b_other_im_screen_name_yahoo varchar(64)',
762 '2_a_b_im_screen_name' => '2_a_b_im_screen_name varchar(64)',
763 '8_a_b_im_provider' => '8_a_b_im_provider text',
764 '8_a_b_billing_im_screen_name' => '8_a_b_billing_im_screen_name varchar(64)',
765 '8_a_b_billing_im_screen_name_jabber' => '8_a_b_billing_im_screen_name_jabber varchar(64)',
766 '8_a_b_billing_im_screen_name_skype' => '8_a_b_billing_im_screen_name_skype varchar(64)',
767 '8_a_b_billing_im_screen_name_yahoo' => '8_a_b_billing_im_screen_name_yahoo varchar(64)',
768 '8_a_b_home_im_screen_name' => '8_a_b_home_im_screen_name varchar(64)',
769 '8_a_b_home_im_screen_name_jabber' => '8_a_b_home_im_screen_name_jabber varchar(64)',
770 '8_a_b_home_im_screen_name_skype' => '8_a_b_home_im_screen_name_skype varchar(64)',
771 '8_a_b_home_im_screen_name_yahoo' => '8_a_b_home_im_screen_name_yahoo varchar(64)',
772 '8_a_b_main_im_screen_name' => '8_a_b_main_im_screen_name varchar(64)',
773 '8_a_b_main_im_screen_name_jabber' => '8_a_b_main_im_screen_name_jabber varchar(64)',
774 '8_a_b_main_im_screen_name_skype' => '8_a_b_main_im_screen_name_skype varchar(64)',
775 '8_a_b_main_im_screen_name_yahoo' => '8_a_b_main_im_screen_name_yahoo varchar(64)',
776 '8_a_b_other_im_screen_name' => '8_a_b_other_im_screen_name varchar(64)',
777 '8_a_b_other_im_screen_name_jabber' => '8_a_b_other_im_screen_name_jabber varchar(64)',
778 '8_a_b_other_im_screen_name_skype' => '8_a_b_other_im_screen_name_skype varchar(64)',
779 '8_a_b_other_im_screen_name_yahoo' => '8_a_b_other_im_screen_name_yahoo varchar(64)',
780 '8_a_b_im_screen_name' => '8_a_b_im_screen_name varchar(64)',
781 '5_a_b_im_provider' => '5_a_b_im_provider text',
782 '5_a_b_billing_im_screen_name' => '5_a_b_billing_im_screen_name varchar(64)',
783 '5_a_b_billing_im_screen_name_jabber' => '5_a_b_billing_im_screen_name_jabber varchar(64)',
784 '5_a_b_billing_im_screen_name_skype' => '5_a_b_billing_im_screen_name_skype varchar(64)',
785 '5_a_b_billing_im_screen_name_yahoo' => '5_a_b_billing_im_screen_name_yahoo varchar(64)',
786 '5_a_b_home_im_screen_name' => '5_a_b_home_im_screen_name varchar(64)',
787 '5_a_b_home_im_screen_name_jabber' => '5_a_b_home_im_screen_name_jabber varchar(64)',
788 '5_a_b_home_im_screen_name_skype' => '5_a_b_home_im_screen_name_skype varchar(64)',
789 '5_a_b_home_im_screen_name_yahoo' => '5_a_b_home_im_screen_name_yahoo varchar(64)',
790 '5_a_b_main_im_screen_name' => '5_a_b_main_im_screen_name varchar(64)',
791 '5_a_b_main_im_screen_name_jabber' => '5_a_b_main_im_screen_name_jabber varchar(64)',
792 '5_a_b_main_im_screen_name_skype' => '5_a_b_main_im_screen_name_skype varchar(64)',
793 '5_a_b_main_im_screen_name_yahoo' => '5_a_b_main_im_screen_name_yahoo varchar(64)',
794 '5_a_b_other_im_screen_name' => '5_a_b_other_im_screen_name varchar(64)',
795 '5_a_b_other_im_screen_name_jabber' => '5_a_b_other_im_screen_name_jabber varchar(64)',
796 '5_a_b_other_im_screen_name_skype' => '5_a_b_other_im_screen_name_skype varchar(64)',
797 '5_a_b_other_im_screen_name_yahoo' => '5_a_b_other_im_screen_name_yahoo varchar(64)',
798 '5_a_b_im_screen_name' => '5_a_b_im_screen_name varchar(64)',
799 'whare_kai_im_provider' => 'whare_kai_im_provider text',
800 'whare_kai_im_screen_name' => 'whare_kai_im_screen_name varchar(64)',
801 'whare_kai_im_screen_name_jabber' => 'whare_kai_im_screen_name_jabber varchar(64)',
802 'whare_kai_im_screen_name_skype' => 'whare_kai_im_screen_name_skype varchar(64)',
803 'whare_kai_im_screen_name_yahoo' => 'whare_kai_im_screen_name_yahoo varchar(64)',
804 '2_a_b_whare_kai_im_screen_name' => '2_a_b_whare_kai_im_screen_name varchar(64)',
805 '2_a_b_whare_kai_im_screen_name_jabber' => '2_a_b_whare_kai_im_screen_name_jabber varchar(64)',
806 '2_a_b_whare_kai_im_screen_name_skype' => '2_a_b_whare_kai_im_screen_name_skype varchar(64)',
807 '2_a_b_whare_kai_im_screen_name_yahoo' => '2_a_b_whare_kai_im_screen_name_yahoo varchar(64)',
808 '8_a_b_whare_kai_im_screen_name' => '8_a_b_whare_kai_im_screen_name varchar(64)',
809 '8_a_b_whare_kai_im_screen_name_jabber' => '8_a_b_whare_kai_im_screen_name_jabber varchar(64)',
810 '8_a_b_whare_kai_im_screen_name_skype' => '8_a_b_whare_kai_im_screen_name_skype varchar(64)',
811 '8_a_b_whare_kai_im_screen_name_yahoo' => '8_a_b_whare_kai_im_screen_name_yahoo varchar(64)',
812 '5_a_b_whare_kai_im_screen_name' => '5_a_b_whare_kai_im_screen_name varchar(64)',
813 '5_a_b_whare_kai_im_screen_name_jabber' => '5_a_b_whare_kai_im_screen_name_jabber varchar(64)',
814 '5_a_b_whare_kai_im_screen_name_skype' => '5_a_b_whare_kai_im_screen_name_skype varchar(64)',
815 '5_a_b_whare_kai_im_screen_name_yahoo' => '5_a_b_whare_kai_im_screen_name_yahoo varchar(64)',
822 * Test phone data export.
824 * Less over the top complete than the im test.
826 public function testExportPhoneData() {
827 $this->contactIDs
[] = $this->individualCreate();
828 $this->contactIDs
[] = $this->individualCreate();
829 $locationTypes = ['Billing' => 'Billing', 'Home' => 'Home'];
830 $phoneTypes = ['Mobile', 'Phone'];
831 foreach ($this->contactIDs
as $contactID) {
832 $this->callAPISuccess('Phone', 'create', [
833 'contact_id' => $contactID,
834 'location_type_id' => 'Billing',
835 'phone_type_id' => 'Mobile',
836 'phone' => 'Billing' . 'Mobile' . $contactID,
839 $this->callAPISuccess('Phone', 'create', [
840 'contact_id' => $contactID,
841 'location_type_id' => 'Home',
842 'phone_type_id' => 'Phone',
843 'phone' => 'Home' . 'Phone' . $contactID,
848 $this->contactIDs
[1] => ['label' => 'Spouse of']
851 foreach ($relationships as $contactID => $relationshipType) {
852 $relationshipTypeID = $this->callAPISuccess('RelationshipType', 'getvalue', ['label_a_b' => $relationshipType['label'], 'return' => 'id']);
853 $result = $this->callAPISuccess('Relationship', 'create', [
854 'contact_id_a' => $this->contactIDs
[0],
855 'relationship_type_id' => $relationshipTypeID,
856 'contact_id_b' => $contactID
858 $relationships[$contactID]['id'] = $result['id'];
859 $relationships[$contactID]['relationship_type_id'] = $relationshipTypeID;
862 $fields = [['Individual', 'contact_id']];
863 // ' ' denotes primary location type.
864 foreach (array_keys(array_merge($locationTypes, [' ' => ['Primary']])) as $locationType) {
868 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Phone', 'location_type_id', $locationType),
873 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Phone', 'location_type_id', $locationType),
875 foreach ($relationships as $contactID => $relationship) {
878 $relationship['relationship_type_id'] . '_a_b',
880 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Phone', 'location_type_id', $locationType),
883 foreach ($phoneTypes as $phoneType) {
887 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Phone', 'location_type_id', $locationType),
888 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Phone', 'phone_type_id', $phoneType),
890 foreach ($relationships as $contactID => $relationship) {
893 $relationship['relationship_type_id'] . '_a_b',
895 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Phone', 'location_type_id', $locationType),
896 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Phone', 'phone_type_id', $phoneType),
901 list($tableName) = $this->doExport($fields, $this->contactIDs
[0]);
903 $dao = CRM_Core_DAO
::executeQuery('SELECT * FROM ' . $tableName);
904 while ($dao->fetch()) {
905 // note there is some chance these might be random on some mysql co
906 $this->assertEquals('BillingMobile3', $dao->billing_phone_mobile
);
907 $this->assertEquals('', $dao->billing_phone_phone
);
908 $relField = '2_a_b_phone_type_id';
909 $this->assertEquals('Phone', $dao->$relField);
910 $this->assertEquals('Mobile', $dao->phone_type_id
);
911 $this->assertEquals('Mobile', $dao->billing_phone_type_id
);
916 * Export City against multiple location types.
918 public function testExportAddressData() {
919 $this->diversifyLocationTypes();
921 $locationTypes = ['Billing' => 'Billing', 'Home' => 'Home', 'Main' => 'Méin', 'Other' => 'Other', 'Whare Kai' => 'Whare Kai'];
923 $this->contactIDs
[] = $this->individualCreate();
924 $this->contactIDs
[] = $this->individualCreate();
925 $this->contactIDs
[] = $this->householdCreate();
926 $this->contactIDs
[] = $this->organizationCreate();
927 $fields = [['Individual', 'contact_id']];
928 foreach ($this->contactIDs
as $contactID) {
929 foreach ($locationTypes as $locationName => $locationLabel) {
930 $this->callAPISuccess('Address', 'create', [
931 'contact_id' => $contactID,
932 'location_type_id' => $locationName,
933 'street_address' => $locationLabel . $contactID . 'street_address',
934 'city' => $locationLabel . $contactID . 'city',
935 'postal_code' => $locationLabel . $contactID . 'postal_code',
937 $fields[] = ['Individual', 'city', CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Address', 'location_type_id', $locationName)];
938 $fields[] = ['Individual', 'street_address', CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Address', 'location_type_id', $locationName)];
939 $fields[] = ['Individual', 'postal_code', CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_Address', 'location_type_id', $locationName)];
944 $this->contactIDs
[1] => ['label' => 'Spouse of'],
945 $this->contactIDs
[2] => ['label' => 'Household Member of'],
946 $this->contactIDs
[3] => ['label' => 'Employee of']
949 foreach ($relationships as $contactID => $relationshipType) {
950 $relationshipTypeID = $this->callAPISuccess('RelationshipType', 'getvalue', ['label_a_b' => $relationshipType['label'], 'return' => 'id']);
951 $result = $this->callAPISuccess('Relationship', 'create', [
952 'contact_id_a' => $this->contactIDs
[0],
953 'relationship_type_id' => $relationshipTypeID,
954 'contact_id_b' => $contactID
956 $relationships[$contactID]['id'] = $result['id'];
957 $relationships[$contactID]['relationship_type_id'] = $relationshipTypeID;
960 // ' ' denotes primary location type.
961 foreach (array_keys(array_merge($locationTypes, [' ' => ['Primary']])) as $locationType) {
962 foreach ($relationships as $contactID => $relationship) {
965 $relationship['relationship_type_id'] . '_a_b',
967 CRM_Core_PseudoConstant
::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
971 list($tableName, $sqlColumns) = $this->doExport($fields, $this->contactIDs
[0]);
973 $dao = CRM_Core_DAO
::executeQuery('SELECT * FROM ' . $tableName);
974 while ($dao->fetch()) {
975 $id = $dao->contact_id
;
976 $this->assertEquals('Méin' . $id . 'city', $dao->main_city
);
977 $this->assertEquals('Billing' . $id . 'street_address', $dao->billing_street_address
);
978 $this->assertEquals('Whare Kai' . $id . 'postal_code', $dao->whare_kai_postal_code
);
979 foreach ($relationships as $relatedContactID => $relationship) {
980 $relationshipString = $field = $relationship['relationship_type_id'] . '_a_b';
981 $field = $relationshipString . '_main_city';
982 $this->assertEquals('Méin' . $relatedContactID . 'city', $dao->$field);
986 $this->assertEquals([
987 'contact_id' => 'contact_id varchar(255)',
988 'billing_city' => 'billing_city varchar(64)',
989 'billing_street_address' => 'billing_street_address varchar(96)',
990 'billing_postal_code' => 'billing_postal_code varchar(64)',
991 'home_city' => 'home_city varchar(64)',
992 'home_street_address' => 'home_street_address varchar(96)',
993 'home_postal_code' => 'home_postal_code varchar(64)',
994 'main_city' => 'main_city varchar(64)',
995 'main_street_address' => 'main_street_address varchar(96)',
996 'main_postal_code' => 'main_postal_code varchar(64)',
997 'other_city' => 'other_city varchar(64)',
998 'other_street_address' => 'other_street_address varchar(96)',
999 'other_postal_code' => 'other_postal_code varchar(64)',
1000 'whare_kai_city' => 'whare_kai_city varchar(64)',
1001 'whare_kai_street_address' => 'whare_kai_street_address varchar(96)',
1002 'whare_kai_postal_code' => 'whare_kai_postal_code varchar(64)',
1003 '2_a_b_billing_city' => '2_a_b_billing_city varchar(64)',
1004 '2_a_b_home_city' => '2_a_b_home_city varchar(64)',
1005 '2_a_b_main_city' => '2_a_b_main_city varchar(64)',
1006 '2_a_b_other_city' => '2_a_b_other_city varchar(64)',
1007 '2_a_b_whare_kai_city' => '2_a_b_whare_kai_city varchar(64)',
1008 '2_a_b_city' => '2_a_b_city varchar(64)',
1009 '8_a_b_billing_city' => '8_a_b_billing_city varchar(64)',
1010 '8_a_b_home_city' => '8_a_b_home_city varchar(64)',
1011 '8_a_b_main_city' => '8_a_b_main_city varchar(64)',
1012 '8_a_b_other_city' => '8_a_b_other_city varchar(64)',
1013 '8_a_b_whare_kai_city' => '8_a_b_whare_kai_city varchar(64)',
1014 '8_a_b_city' => '8_a_b_city varchar(64)',
1015 '5_a_b_billing_city' => '5_a_b_billing_city varchar(64)',
1016 '5_a_b_home_city' => '5_a_b_home_city varchar(64)',
1017 '5_a_b_main_city' => '5_a_b_main_city varchar(64)',
1018 '5_a_b_other_city' => '5_a_b_other_city varchar(64)',
1019 '5_a_b_whare_kai_city' => '5_a_b_whare_kai_city varchar(64)',
1020 '5_a_b_city' => '5_a_b_city varchar(64)',
1025 * Test master_address_id field.
1027 public function testExportMasterAddress() {
1028 $this->setUpContactExportData();
1030 //export the master address for contact B
1031 $selectedFields = array(
1032 array('Individual', 'master_id', 1),
1034 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
1036 array($this->contactIDs
[1]),
1041 CRM_Export_Form_Select
::CONTACT_EXPORT
,
1042 "contact_a.id IN ({$this->contactIDs[1]})",
1047 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
1048 'suppress_csv_for_testing' => TRUE,
1051 $field = key($sqlColumns);
1053 //assert the exported result
1054 $masterName = CRM_Core_DAO
::singleValueQuery("SELECT {$field} FROM {$tableName}");
1055 $displayName = CRM_Contact_BAO_Contact
::getMasterDisplayName($this->masterAddressID
);
1056 $this->assertEquals($displayName, $masterName);
1058 // delete the export temp table and component table
1059 $sql = "DROP TABLE IF EXISTS {$tableName}";
1060 CRM_Core_DAO
::executeQuery($sql);
1064 * Test that deceased and do not mail contacts are removed from contacts before
1066 * @dataProvider getReasonsNotToMail
1068 * @param array $reason
1069 * @param array $addressReason
1071 public function testExportDeceasedDoNotMail($reason, $addressReason) {
1072 $contactA = $this->callAPISuccess('contact', 'create', array(
1073 'first_name' => 'John',
1074 'last_name' => 'Doe',
1075 'contact_type' => 'Individual',
1078 $contactB = $this->callAPISuccess('contact', 'create', array_merge([
1079 'first_name' => 'Jane',
1080 'last_name' => 'Doe',
1081 'contact_type' => 'Individual',
1084 //create address for contact A
1085 $this->callAPISuccess('address', 'create', [
1086 'contact_id' => $contactA['id'],
1087 'location_type_id' => 'Home',
1088 'street_address' => 'ABC 12',
1089 'postal_code' => '123 AB',
1090 'country_id' => '1152',
1095 //create address for contact B
1096 $this->callAPISuccess('address', 'create', array_merge([
1097 'contact_id' => $contactB['id'],
1098 'location_type_id' => 'Home',
1099 'street_address' => 'ABC 12',
1100 'postal_code' => '123 AB',
1101 'country_id' => '1152',
1104 ], $addressReason));
1106 //export and merge contacts with same address
1107 list($tableName, $sqlColumns, $headerRows, $processor) = CRM_Export_BAO_Export
::exportComponents(
1109 array($contactA['id'], $contactB['id']),
1114 CRM_Export_Form_Select
::CONTACT_EXPORT
,
1115 "contact_a.id IN ({$contactA['id']}, {$contactB['id']})",
1120 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
1121 'mergeOption' => TRUE,
1122 'suppress_csv_for_testing' => TRUE,
1123 'postal_mailing_export' => array(
1124 'postal_mailing_export' => TRUE,
1129 $this->assertTrue(!in_array('state_province_id', $processor->getHeaderRows()));
1130 $greeting = CRM_Core_DAO
::singleValueQuery("SELECT email_greeting FROM {$tableName}");
1132 //Assert email_greeting is not merged
1133 $this->assertNotContains(',', (string) $greeting);
1135 // delete the export temp table and component table
1136 $sql = "DROP TABLE IF EXISTS {$tableName}";
1137 CRM_Core_DAO
::executeQuery($sql);
1141 * Get reasons that a contact is not postalable.
1144 public function getReasonsNotToMail() {
1146 [['is_deceased' => 1], []],
1147 [['do_not_mail' => 1], []],
1148 [[], ['street_address' => '']],
1154 protected function setUpHousehold() {
1155 $this->setUpContactExportData();
1156 $householdID = $this->householdCreate([
1157 'source' => 'household sauce',
1158 'api.Address.create' => [
1159 'city' => 'Portland',
1160 'state_province_id' => 'Maine',
1161 'location_type_id' => 'Home'
1165 $relationshipTypes = $this->callAPISuccess('RelationshipType', 'get', [])['values'];
1166 $houseHoldTypeID = NULL;
1167 foreach ($relationshipTypes as $id => $relationshipType) {
1168 if ($relationshipType['name_a_b'] === 'Household Member of') {
1169 $houseHoldTypeID = $relationshipType['id'];
1172 $this->callAPISuccess('Relationship', 'create', [
1173 'contact_id_a' => $this->contactIDs
[0],
1174 'contact_id_b' => $householdID,
1175 'relationship_type_id' => $houseHoldTypeID,
1177 $this->callAPISuccess('Relationship', 'create', [
1178 'contact_id_a' => $this->contactIDs
[1],
1179 'contact_id_b' => $householdID,
1180 'relationship_type_id' => $houseHoldTypeID,
1182 return array($householdID, $houseHoldTypeID);
1186 * Do a CiviCRM export.
1188 * @param $selectedFields
1191 * @param int $exportMode
1195 protected function doExport($selectedFields, $id, $exportMode = CRM_Export_Form_Select
::CONTACT_EXPORT
) {
1197 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
1205 "contact_a.id IN (" . implode(',', $ids) . ")",
1210 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
1211 'suppress_csv_for_testing' => TRUE,
1214 return array($tableName, $sqlColumns);
1218 * Ensure component is enabled.
1220 * @param int $exportMode
1222 public function ensureComponentIsEnabled($exportMode) {
1223 if ($exportMode === CRM_Export_Form_Select
::CASE_EXPORT
) {
1224 CRM_Core_BAO_ConfigSetting
::enableComponent('CiviCase');
1229 * Test our export all field metadata retrieval.
1231 * @dataProvider additionalFieldsDataProvider
1232 * @param int $exportMode
1235 public function testAdditionalReturnProperties($exportMode, $expected) {
1236 $this->ensureComponentIsEnabled($exportMode);
1237 $processor = new CRM_Export_BAO_ExportProcessor($exportMode, NULL, 'AND');
1238 $metadata = $processor->getAdditionalReturnProperties();
1239 $this->assertEquals($expected, $metadata);
1243 * Test our export all field metadata retrieval.
1245 * @dataProvider allFieldsDataProvider
1246 * @param int $exportMode
1249 public function testDefaultReturnProperties($exportMode, $expected) {
1250 $this->ensureComponentIsEnabled($exportMode);
1251 $processor = new CRM_Export_BAO_ExportProcessor($exportMode, NULL, 'AND');
1252 $metadata = $processor->getDefaultReturnProperties();
1253 $this->assertEquals($expected, $metadata);
1257 * Get fields returned from additionalFields function.
1261 public function additionalFieldsDataProvider() {
1264 'anything that will then be defaulting ton contact',
1265 $this->getExtraReturnProperties(),
1268 CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
1269 array_merge($this->getExtraReturnProperties(), $this->getActivityReturnProperties()),
1272 CRM_Export_Form_Select
::CASE_EXPORT
,
1273 array_merge($this->getExtraReturnProperties(), $this->getCaseReturnProperties()),
1276 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
1277 array_merge($this->getExtraReturnProperties(), $this->getContributionReturnProperties()),
1280 CRM_Export_Form_Select
::EVENT_EXPORT
,
1281 array_merge($this->getExtraReturnProperties(), $this->getEventReturnProperties()),
1284 CRM_Export_Form_Select
::MEMBER_EXPORT
,
1285 array_merge($this->getExtraReturnProperties(), $this->getMembershipReturnProperties()),
1288 CRM_Export_Form_Select
::PLEDGE_EXPORT
,
1289 array_merge($this->getExtraReturnProperties(), $this->getPledgeReturnProperties()),
1296 * get data for testing field metadata by query mode.
1298 public function allFieldsDataProvider() {
1301 'anything that will then be defaulting ton contact',
1302 $this->getBasicReturnProperties(TRUE),
1305 CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
1306 array_merge($this->getBasicReturnProperties(FALSE), $this->getActivityReturnProperties()),
1309 CRM_Export_Form_Select
::CASE_EXPORT
,
1310 array_merge($this->getBasicReturnProperties(FALSE), $this->getCaseReturnProperties()),
1313 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
1314 array_merge($this->getBasicReturnProperties(FALSE), $this->getContributionReturnProperties()),
1317 CRM_Export_Form_Select
::EVENT_EXPORT
,
1318 array_merge($this->getBasicReturnProperties(FALSE), $this->getEventReturnProperties()),
1321 CRM_Export_Form_Select
::MEMBER_EXPORT
,
1322 array_merge($this->getBasicReturnProperties(FALSE), $this->getMembershipReturnProperties()),
1325 CRM_Export_Form_Select
::PLEDGE_EXPORT
,
1326 array_merge($this->getBasicReturnProperties(FALSE), $this->getPledgeReturnProperties()),
1332 * Get return properties manually added in.
1334 public function getExtraReturnProperties() {
1339 * Get basic return properties.
1341 * @param bool $isContactMode
1342 * Are we in contact mode or not
1346 protected function getBasicReturnProperties($isContactMode) {
1347 $returnProperties = [
1349 'contact_type' => 1,
1350 'contact_sub_type' => 1,
1351 'do_not_email' => 1,
1352 'do_not_phone' => 1,
1355 'do_not_trade' => 1,
1357 'legal_identifier' => 1,
1358 'external_identifier' => 1,
1360 'display_name' => 1,
1364 'preferred_communication_method' => 1,
1365 'preferred_language' => 1,
1366 'preferred_mail_format' => 1,
1368 'contact_source' => 1,
1374 'formal_title' => 1,
1375 'communication_style_id' => 1,
1376 'email_greeting_id' => 1,
1377 'postal_greeting_id' => 1,
1378 'addressee_id' => 1,
1383 'deceased_date' => 1,
1384 'household_name' => 1,
1385 'organization_name' => 1,
1387 'user_unique_id' => 1,
1388 'current_employer_id' => 1,
1389 'contact_is_deleted' => 1,
1390 'created_date' => 1,
1391 'modified_date' => 1,
1393 'email_greeting' => 1,
1394 'postal_greeting' => 1,
1395 'current_employer' => 1,
1396 'location_type' => 1,
1397 'street_address' => 1,
1398 'street_number' => 1,
1399 'street_number_suffix' => 1,
1402 'supplemental_address_1' => 1,
1403 'supplemental_address_2' => 1,
1404 'supplemental_address_3' => 1,
1406 'postal_code_suffix' => 1,
1410 'address_name' => 1,
1413 'state_province' => 1,
1420 'signature_text' => 1,
1421 'signature_html' => 1,
1425 'world_region' => 1,
1430 'phone_type_id' => 1,
1432 if (!$isContactMode) {
1433 unset($returnProperties['groups']);
1434 unset($returnProperties['tags']);
1435 unset($returnProperties['notes']);
1437 return $returnProperties;
1441 * Get return properties for pledges.
1445 public function getPledgeReturnProperties() {
1447 'contact_type' => 1,
1448 'contact_sub_type' => 1,
1450 'display_name' => 1,
1452 'pledge_amount' => 1,
1453 'pledge_total_paid' => 1,
1454 'pledge_create_date' => 1,
1455 'pledge_start_date' => 1,
1456 'pledge_next_pay_date' => 1,
1457 'pledge_next_pay_amount' => 1,
1458 'pledge_status' => 1,
1459 'pledge_is_test' => 1,
1460 'pledge_contribution_page_id' => 1,
1461 'pledge_financial_type' => 1,
1462 'pledge_frequency_interval' => 1,
1463 'pledge_frequency_unit' => 1,
1464 'pledge_currency' => 1,
1465 'pledge_campaign_id' => 1,
1466 'pledge_balance_amount' => 1,
1467 'pledge_payment_id' => 1,
1468 'pledge_payment_scheduled_amount' => 1,
1469 'pledge_payment_scheduled_date' => 1,
1470 'pledge_payment_paid_amount' => 1,
1471 'pledge_payment_paid_date' => 1,
1472 'pledge_payment_reminder_date' => 1,
1473 'pledge_payment_reminder_count' => 1,
1474 'pledge_payment_status' => 1,
1479 * Get membership return properties.
1483 public function getMembershipReturnProperties() {
1485 'contact_type' => 1,
1486 'contact_sub_type' => 1,
1488 'display_name' => 1,
1489 'membership_type' => 1,
1490 'member_is_test' => 1,
1491 'member_is_pay_later' => 1,
1493 'membership_start_date' => 1,
1494 'membership_end_date' => 1,
1495 'membership_source' => 1,
1496 'membership_status' => 1,
1497 'membership_id' => 1,
1498 'owner_membership_id' => 1,
1500 'membership_recur_id' => 1,
1501 'member_campaign_id' => 1,
1502 'member_is_override' => 1,
1503 'member_auto_renew' => 1,
1508 * Get return properties for events.
1512 public function getEventReturnProperties() {
1514 'contact_type' => 1,
1515 'contact_sub_type' => 1,
1517 'display_name' => 1,
1520 'event_start_date' => 1,
1521 'event_end_date' => 1,
1523 'participant_id' => 1,
1524 'participant_status' => 1,
1525 'participant_status_id' => 1,
1526 'participant_role' => 1,
1527 'participant_role_id' => 1,
1528 'participant_note' => 1,
1529 'participant_register_date' => 1,
1530 'participant_source' => 1,
1531 'participant_fee_level' => 1,
1532 'participant_is_test' => 1,
1533 'participant_is_pay_later' => 1,
1534 'participant_fee_amount' => 1,
1535 'participant_discount_name' => 1,
1536 'participant_fee_currency' => 1,
1537 'participant_registered_by_id' => 1,
1538 'participant_campaign_id' => 1,
1543 * Get return properties for activities.
1547 public function getActivityReturnProperties() {
1550 'contact_type' => 1,
1551 'contact_sub_type' => 1,
1553 'display_name' => 1,
1554 'activity_type' => 1,
1555 'activity_type_id' => 1,
1556 'activity_subject' => 1,
1557 'activity_date_time' => 1,
1558 'activity_duration' => 1,
1559 'activity_location' => 1,
1560 'activity_details' => 1,
1561 'activity_status' => 1,
1562 'activity_priority' => 1,
1563 'source_contact' => 1,
1564 'source_record_id' => 1,
1565 'activity_is_test' => 1,
1566 'activity_campaign_id' => 1,
1568 'activity_engagement_level' => 1,
1574 * Get return properties for Case.
1578 public function getCaseReturnProperties() {
1580 'contact_type' => 1,
1581 'contact_sub_type' => 1,
1583 'display_name' => 1,
1585 'case_start_date' => 1,
1586 'case_end_date' => 1,
1587 'case_subject' => 1,
1588 'case_source_contact_id' => 1,
1589 'case_activity_status' => 1,
1590 'case_activity_duration' => 1,
1591 'case_activity_medium_id' => 1,
1592 'case_activity_details' => 1,
1593 'case_activity_is_auto' => 1,
1596 'case_activity_subject' => 1,
1600 'case_deleted' => 1,
1601 'case_recent_activity_date' => 1,
1602 'case_recent_activity_type' => 1,
1603 'case_scheduled_activity_date' => 1,
1608 * Get return properties for contribution.
1612 public function getContributionReturnProperties() {
1614 'contact_type' => 1,
1615 'contact_sub_type' => 1,
1617 'display_name' => 1,
1618 'financial_type' => 1,
1619 'contribution_source' => 1,
1620 'receive_date' => 1,
1621 'thankyou_date' => 1,
1623 'total_amount' => 1,
1624 'accounting_code' => 1,
1625 'payment_instrument' => 1,
1626 'payment_instrument_id' => 1,
1627 'contribution_check_number' => 1,
1628 'non_deductible_amount' => 1,
1633 'invoice_number' => 1,
1635 'cancel_reason' => 1,
1636 'receipt_date' => 1,
1637 'product_name' => 1,
1639 'product_option' => 1,
1640 'fulfilled_date' => 1,
1641 'contribution_start_date' => 1,
1642 'contribution_end_date' => 1,
1644 'is_pay_later' => 1,
1645 'contribution_status' => 1,
1646 'contribution_recur_id' => 1,
1647 'amount_level' => 1,
1648 'contribution_note' => 1,
1649 'contribution_batch' => 1,
1650 'contribution_campaign_title' => 1,
1651 'contribution_campaign_id' => 1,
1652 'contribution_product_id' => 1,
1653 'contribution_soft_credit_name' => 1,
1654 'contribution_soft_credit_amount' => 1,
1655 'contribution_soft_credit_type' => 1,
1656 'contribution_soft_credit_contact_id' => 1,
1657 'contribution_soft_credit_contribution_id' => 1,
1662 * Test the column definition when 'all' fields defined.
1664 * @param int $exportMode
1665 * @param array $expected
1666 * @param array $expectedHeaders
1668 * @dataProvider getSqlColumnsOutput
1670 public function testGetSQLColumnsAndHeaders($exportMode, $expected, $expectedHeaders) {
1671 $this->ensureComponentIsEnabled($exportMode);
1672 // We need some data so that we can get to the end of the export
1673 // function. Hopefully one day that won't be required to get metadata info out.
1674 // eventually aspire to call $provider->getSQLColumns straight after it
1676 $this->setupBaseExportData($exportMode);
1678 $result = CRM_Export_BAO_Export
::exportComponents(
1691 'exportOption' => CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
1692 'suppress_csv_for_testing' => TRUE,
1695 $this->assertEquals($expected, $result[1]);
1696 $this->assertEquals($expectedHeaders, $result[2]);
1700 * Test exported with fields to output specified.
1702 * @dataProvider getAllSpecifiableReturnFields
1704 * @param int $exportMode
1705 * @param array $selectedFields
1706 * @param array $expected
1708 public function testExportSpecifyFields($exportMode, $selectedFields, $expected) {
1709 $this->ensureComponentIsEnabled($exportMode);
1710 $this->setUpContributionExportData();
1711 list($tableName, $sqlColumns) = $this->doExport($selectedFields, $this->contactIDs
[1], $exportMode);
1712 $this->assertEquals($expected, $sqlColumns);
1716 * Test export fields when no payment fields to be exported.
1718 public function textExportParticipantSpecifyFieldsNoPayment() {
1719 $selectedFields = $this->getAllSpecifiableParticipantReturnFields();
1720 foreach ($selectedFields as $index => $field) {
1721 if (substr($field[1], 0, 22) === 'componentPaymentField_') {
1722 unset ($selectedFields[$index]);
1726 $expected = $this->getAllSpecifiableParticipantReturnFields();
1727 foreach ($expected as $index => $field) {
1728 if (substr($index, 0, 22) === 'componentPaymentField_') {
1729 unset ($expected[$index]);
1733 list($tableName, $sqlColumns) = $this->doExport($selectedFields, $this->contactIDs
[1], CRM_Export_Form_Select
::EVENT_EXPORT
);
1734 $this->assertEquals($expected, $sqlColumns);
1737 * Get all return fields (@todo - still being built up.
1741 public function getAllSpecifiableReturnFields() {
1744 CRM_Export_Form_Select
::EVENT_EXPORT
,
1745 $this->getAllSpecifiableParticipantReturnFields(),
1746 $this->getAllSpecifiableParticipantReturnColumns(),
1752 * Get expected return column output for participant mode return all columns.
1756 public function getAllSpecifiableParticipantReturnColumns() {
1758 'participant_campaign_id' => 'participant_campaign_id varchar(128)',
1759 'participant_contact_id' => 'participant_contact_id varchar(16)',
1760 'componentpaymentfield_contribution_status' => 'componentpaymentfield_contribution_status text',
1761 'currency' => 'currency varchar(3)',
1762 'componentpaymentfield_received_date' => 'componentpaymentfield_received_date text',
1763 'default_role_id' => 'default_role_id varchar(16)',
1764 'participant_discount_name' => 'participant_discount_name varchar(16)',
1765 'event_id' => 'event_id varchar(16)',
1766 'event_end_date' => 'event_end_date varchar(32)',
1767 'event_start_date' => 'event_start_date varchar(32)',
1768 'template_title' => 'template_title varchar(255)',
1769 'event_title' => 'event_title varchar(255)',
1770 'participant_fee_amount' => 'participant_fee_amount varchar(32)',
1771 'participant_fee_currency' => 'participant_fee_currency varchar(3)',
1772 'fee_label' => 'fee_label varchar(255)',
1773 'participant_fee_level' => 'participant_fee_level longtext',
1774 'participant_is_pay_later' => 'participant_is_pay_later varchar(16)',
1775 'participant_id' => 'participant_id varchar(16)',
1776 'participant_note' => 'participant_note text',
1777 'participant_role_id' => 'participant_role_id varchar(128)',
1778 'participant_role' => 'participant_role varchar(255)',
1779 'participant_source' => 'participant_source varchar(128)',
1780 'participant_status_id' => 'participant_status_id varchar(16)',
1781 'participant_status' => 'participant_status varchar(255)',
1782 'participant_register_date' => 'participant_register_date varchar(32)',
1783 'participant_registered_by_id' => 'participant_registered_by_id varchar(16)',
1784 'participant_is_test' => 'participant_is_test varchar(16)',
1785 'componentpaymentfield_total_amount' => 'componentpaymentfield_total_amount text',
1786 'componentpaymentfield_transaction_id' => 'componentpaymentfield_transaction_id varchar(255)',
1787 'transferred_to_contact_id' => 'transferred_to_contact_id varchar(16)',
1794 public function getAllSpecifiableParticipantReturnFields() {
1804 1 => 'participant_campaign_id',
1809 1 => 'participant_contact_id',
1814 1 => 'componentPaymentField_contribution_status',
1824 1 => 'componentPaymentField_received_date',
1829 1 => 'default_role_id',
1834 1 => 'participant_discount_name',
1844 1 => 'event_end_date',
1849 1 => 'event_start_date',
1854 1 => 'template_title',
1864 1 => 'participant_fee_amount',
1869 1 => 'participant_fee_currency',
1879 1 => 'participant_fee_level',
1884 1 => 'participant_is_pay_later',
1889 1 => 'participant_id',
1894 1 => 'participant_note',
1899 1 => 'participant_role_id',
1904 1 => 'participant_role',
1909 1 => 'participant_source',
1914 1 => 'participant_status_id',
1919 1 => 'participant_status',
1924 1 => 'participant_status',
1929 1 => 'participant_register_date',
1934 1 => 'participant_registered_by_id',
1939 1 => 'participant_is_test',
1944 1 => 'componentPaymentField_total_amount',
1949 1 => 'componentPaymentField_transaction_id',
1954 1 => 'transferred_to_contact_id',
1960 * @param string $exportMode
1962 public function setupBaseExportData($exportMode) {
1963 $this->createLoggedInUser();
1964 if ($exportMode === CRM_Export_Form_Select
::CASE_EXPORT
) {
1965 $this->setupCaseExportData();
1967 if ($exportMode === CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
) {
1968 $this->setUpContributionExportData();
1970 if ($exportMode === CRM_Export_Form_Select
::MEMBER_EXPORT
) {
1971 $this->setUpMembershipExportData();
1973 if ($exportMode === CRM_Export_Form_Select
::ACTIVITY_EXPORT
) {
1974 $this->setUpActivityExportData();
1979 * Get comprehensive sql columns output.
1983 public function getSqlColumnsOutput() {
1986 'anything that will then be defaulting ton contact',
1987 $this->getBasicSqlColumnDefinition(TRUE),
1988 $this->getBasicHeaderDefinition(TRUE),
1991 CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
1992 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getActivitySqlColumns()),
1993 array_merge($this->getBasicHeaderDefinition(FALSE), $this->getActivityHeaderDefinition()),
1996 CRM_Export_Form_Select
::CASE_EXPORT
,
1997 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getCaseSqlColumns()),
1998 array_merge($this->getBasicHeaderDefinition(FALSE), $this->getCaseHeaderDefinition()),
2001 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
2002 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getContributionSqlColumns()),
2003 array_merge($this->getBasicHeaderDefinition(FALSE), $this->getContributeHeaderDefinition()),
2006 CRM_Export_Form_Select
::EVENT_EXPORT
,
2007 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getParticipantSqlColumns()),
2008 array_merge($this->getBasicHeaderDefinition(FALSE), $this->getParticipantHeaderDefinition()),
2011 CRM_Export_Form_Select
::MEMBER_EXPORT
,
2012 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getMembershipSqlColumns()),
2013 array_merge($this->getBasicHeaderDefinition(FALSE), $this->getMemberHeaderDefinition()),
2016 CRM_Export_Form_Select
::PLEDGE_EXPORT
,
2017 array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getPledgeSqlColumns()),
2018 array_merge($this->getBasicHeaderDefinition(FALSE), $this->getPledgeHeaderDefinition()),
2025 * Get the header definition for exports.
2027 * @param bool $isContactExport
2031 protected function getBasicHeaderDefinition($isContactExport) {
2034 1 => 'Contact Type',
2035 2 => 'Contact Subtype',
2036 3 => 'Do Not Email',
2037 4 => 'Do Not Phone',
2040 7 => 'Do Not Trade',
2041 8 => 'No Bulk Emails (User Opt Out)',
2042 9 => 'Legal Identifier',
2043 10 => 'External Identifier',
2045 12 => 'Display Name',
2049 16 => 'Preferred Communication Method',
2050 17 => 'Preferred Language',
2051 18 => 'Preferred Mail Format',
2052 19 => 'Contact Hash',
2053 20 => 'Contact Source',
2055 22 => 'Middle Name',
2057 24 => 'Individual Prefix',
2058 25 => 'Individual Suffix',
2059 26 => 'Formal Title',
2060 27 => 'Communication Style',
2061 28 => 'Email Greeting ID',
2062 29 => 'Postal Greeting ID',
2063 30 => 'Addressee ID',
2068 35 => 'Deceased Date',
2069 36 => 'Household Name',
2070 37 => 'Organization Name',
2072 39 => 'Unique ID (OpenID)',
2073 40 => 'Current Employer ID',
2074 41 => 'Contact is in Trash',
2075 42 => 'Created Date',
2076 43 => 'Modified Date',
2078 45 => 'Email Greeting',
2079 46 => 'Postal Greeting',
2080 47 => 'Current Employer',
2081 48 => 'Location Type',
2082 49 => 'Street Address',
2083 50 => 'Street Number',
2084 51 => 'Street Number Suffix',
2085 52 => 'Street Name',
2086 53 => 'Street Unit',
2087 54 => 'Supplemental Address 1',
2088 55 => 'Supplemental Address 2',
2089 56 => 'Supplemental Address 3',
2091 58 => 'Postal Code Suffix',
2092 59 => 'Postal Code',
2095 62 => 'Address Name',
2096 63 => 'Master Address Belongs To',
2101 68 => 'Phone Extension',
2105 72 => 'Use for Bulk Mail',
2106 73 => 'Signature Text',
2107 74 => 'Signature Html',
2108 75 => 'IM Provider',
2109 76 => 'IM Screen Name',
2111 78 => 'World Region',
2117 if (!$isContactExport) {
2118 unset($headers[80]);
2119 unset($headers[81]);
2120 unset($headers[82]);
2126 * Get the definition for activity headers.
2130 protected function getActivityHeaderDefinition() {
2132 81 => 'Activity ID',
2133 82 => 'Activity Type',
2134 83 => 'Activity Type ID',
2136 85 => 'Activity Date',
2140 89 => 'Activity Status',
2141 90 => 'Activity Priority',
2142 91 => 'Source Contact',
2143 92 => 'source_record_id',
2145 94 => 'Campaign ID',
2147 96 => 'Engagement Index',
2153 * Get the definition for case headers.
2157 protected function getCaseHeaderDefinition() {
2161 83 => 'case_activity_subject',
2162 84 => 'Case Subject',
2163 85 => 'Case Status',
2165 87 => 'Role in Case',
2166 88 => 'Case is in the Trash',
2167 89 => 'case_recent_activity_date',
2168 90 => 'case_recent_activity_type',
2169 91 => 'case_scheduled_activity_date',
2170 92 => 'Case Start Date',
2171 93 => 'Case End Date',
2172 94 => 'case_source_contact_id',
2173 95 => 'case_activity_status',
2174 96 => 'case_activity_duration',
2175 97 => 'case_activity_medium_id',
2176 98 => 'case_activity_details',
2177 99 => 'case_activity_is_auto',
2182 * Get the definition for contribute headers.
2186 protected function getContributeHeaderDefinition() {
2188 81 => 'Financial Type',
2189 82 => 'Contribution Source',
2190 83 => 'Date Received',
2191 84 => 'Thank-you Date',
2192 85 => 'Cancel Date',
2193 86 => 'Total Amount',
2194 87 => 'Accounting Code',
2195 88 => 'payment_instrument',
2196 89 => 'Payment Method ID',
2197 90 => 'Check Number',
2198 91 => 'Non-deductible Amount',
2201 94 => 'Transaction ID',
2202 95 => 'Invoice Reference',
2203 96 => 'Invoice Number',
2205 98 => 'Cancellation / Refund Reason',
2206 99 => 'Receipt Date',
2207 100 => 'Product Name',
2209 102 => 'Product Option',
2210 103 => 'Fulfilled Date',
2211 104 => 'Start date for premium',
2212 105 => 'End date for premium',
2214 107 => 'Is Pay Later',
2215 108 => 'contribution_status',
2216 109 => 'Recurring Contribution ID',
2217 110 => 'Amount Label',
2218 111 => 'Contribution Note',
2219 112 => 'Batch Name',
2220 113 => 'Campaign Title',
2221 114 => 'Campaign ID',
2223 116 => 'Soft Credit For',
2224 117 => 'Soft Credit Amount',
2225 118 => 'Soft Credit Type',
2226 119 => 'Soft Credit For Contact ID',
2227 120 => 'Soft Credit For Contribution ID',
2232 * Get the definition for event headers.
2236 protected function getParticipantHeaderDefinition() {
2239 82 => 'Event Title',
2240 83 => 'Event Start Date',
2241 84 => 'Event End Date',
2243 86 => 'Participant ID',
2244 87 => 'Participant Status',
2245 88 => 'Participant Status Id',
2246 89 => 'Participant Role',
2247 90 => 'Participant Role Id',
2248 91 => 'Participant Note',
2249 92 => 'Register date',
2250 93 => 'Participant Source',
2253 96 => 'Is Pay Later',
2255 98 => 'Discount Name',
2256 99 => 'Fee Currency',
2257 100 => 'Registered By ID',
2258 101 => 'Campaign ID',
2263 * Get the definition for member headers.
2267 protected function getMemberHeaderDefinition() {
2269 81 => 'Membership Type',
2271 83 => 'Is Pay Later',
2272 84 => 'Member Since',
2273 85 => 'Membership Start Date',
2274 86 => 'Membership Expiration Date',
2276 88 => 'Membership Status',
2277 89 => 'Membership ID',
2278 90 => 'Primary Member ID',
2279 91 => 'max_related',
2280 92 => 'membership_recur_id',
2281 93 => 'Campaign ID',
2282 94 => 'member_is_override',
2283 95 => 'member_auto_renew',
2288 * Get the definition for pledge headers.
2292 protected function getPledgeHeaderDefinition() {
2295 82 => 'Total Pledged',
2297 84 => 'Pledge Made',
2298 85 => 'pledge_start_date',
2299 86 => 'Next Payment Date',
2300 87 => 'Next Payment Amount',
2301 88 => 'Pledge Status',
2303 90 => 'Pledge Contribution Page Id',
2304 91 => 'pledge_financial_type',
2305 92 => 'Pledge Frequency Interval',
2306 93 => 'Pledge Frequency Unit',
2307 94 => 'pledge_currency',
2308 95 => 'Campaign ID',
2309 96 => 'Balance Amount',
2311 98 => 'Scheduled Amount',
2312 99 => 'Scheduled Date',
2313 100 => 'Paid Amount',
2315 102 => 'Last Reminder',
2316 103 => 'Reminders Sent',
2317 104 => 'Pledge Payment Status',
2322 * Get the column definition for exports.
2324 * @param bool $isContactExport
2328 protected function getBasicSqlColumnDefinition($isContactExport) {
2330 'civicrm_primary_id' => 'civicrm_primary_id varchar(16)',
2331 'contact_type' => 'contact_type varchar(64)',
2332 'contact_sub_type' => 'contact_sub_type varchar(255)',
2333 'do_not_email' => 'do_not_email varchar(16)',
2334 'do_not_phone' => 'do_not_phone varchar(16)',
2335 'do_not_mail' => 'do_not_mail varchar(16)',
2336 'do_not_sms' => 'do_not_sms varchar(16)',
2337 'do_not_trade' => 'do_not_trade varchar(16)',
2338 'is_opt_out' => 'is_opt_out varchar(16)',
2339 'legal_identifier' => 'legal_identifier varchar(32)',
2340 'external_identifier' => 'external_identifier varchar(64)',
2341 'sort_name' => 'sort_name varchar(128)',
2342 'display_name' => 'display_name varchar(128)',
2343 'nick_name' => 'nick_name varchar(128)',
2344 'legal_name' => 'legal_name varchar(128)',
2345 'image_url' => 'image_url longtext',
2346 'preferred_communication_method' => 'preferred_communication_method varchar(255)',
2347 'preferred_language' => 'preferred_language varchar(5)',
2348 'preferred_mail_format' => 'preferred_mail_format varchar(8)',
2349 'hash' => 'hash varchar(32)',
2350 'contact_source' => 'contact_source varchar(255)',
2351 'first_name' => 'first_name varchar(64)',
2352 'middle_name' => 'middle_name varchar(64)',
2353 'last_name' => 'last_name varchar(64)',
2354 'prefix_id' => 'prefix_id varchar(255)',
2355 'suffix_id' => 'suffix_id varchar(255)',
2356 'formal_title' => 'formal_title varchar(64)',
2357 'communication_style_id' => 'communication_style_id varchar(16)',
2358 'email_greeting_id' => 'email_greeting_id varchar(16)',
2359 'postal_greeting_id' => 'postal_greeting_id varchar(16)',
2360 'addressee_id' => 'addressee_id varchar(16)',
2361 'job_title' => 'job_title varchar(255)',
2362 'gender_id' => 'gender_id varchar(16)',
2363 'birth_date' => 'birth_date varchar(32)',
2364 'is_deceased' => 'is_deceased varchar(16)',
2365 'deceased_date' => 'deceased_date varchar(32)',
2366 'household_name' => 'household_name varchar(128)',
2367 'organization_name' => 'organization_name varchar(128)',
2368 'sic_code' => 'sic_code varchar(8)',
2369 'user_unique_id' => 'user_unique_id varchar(255)',
2370 'current_employer_id' => 'current_employer_id varchar(16)',
2371 'contact_is_deleted' => 'contact_is_deleted varchar(16)',
2372 'created_date' => 'created_date varchar(32)',
2373 'modified_date' => 'modified_date varchar(32)',
2374 'addressee' => 'addressee varchar(255)',
2375 'email_greeting' => 'email_greeting varchar(255)',
2376 'postal_greeting' => 'postal_greeting varchar(255)',
2377 'current_employer' => 'current_employer varchar(128)',
2378 'location_type' => 'location_type text',
2379 'street_address' => 'street_address varchar(96)',
2380 'street_number' => 'street_number varchar(16)',
2381 'street_number_suffix' => 'street_number_suffix varchar(8)',
2382 'street_name' => 'street_name varchar(64)',
2383 'street_unit' => 'street_unit varchar(16)',
2384 'supplemental_address_1' => 'supplemental_address_1 varchar(96)',
2385 'supplemental_address_2' => 'supplemental_address_2 varchar(96)',
2386 'supplemental_address_3' => 'supplemental_address_3 varchar(96)',
2387 'city' => 'city varchar(64)',
2388 'postal_code_suffix' => 'postal_code_suffix varchar(12)',
2389 'postal_code' => 'postal_code varchar(64)',
2390 'geo_code_1' => 'geo_code_1 varchar(32)',
2391 'geo_code_2' => 'geo_code_2 varchar(32)',
2392 'address_name' => 'address_name varchar(255)',
2393 'master_id' => 'master_id varchar(128)',
2394 'county' => 'county varchar(64)',
2395 'state_province' => 'state_province varchar(64)',
2396 'country' => 'country varchar(64)',
2397 'phone' => 'phone varchar(32)',
2398 'phone_ext' => 'phone_ext varchar(16)',
2399 'phone_type_id' => 'phone_type_id varchar(16)',
2400 'email' => 'email varchar(254)',
2401 'on_hold' => 'on_hold varchar(16)',
2402 'is_bulkmail' => 'is_bulkmail varchar(16)',
2403 'signature_text' => 'signature_text longtext',
2404 'signature_html' => 'signature_html longtext',
2405 'im_provider' => 'im_provider text',
2406 'im_screen_name' => 'im_screen_name varchar(64)',
2407 'openid' => 'openid varchar(255)',
2408 'world_region' => 'world_region varchar(128)',
2409 'url' => 'url varchar(128)',
2410 'groups' => 'groups text',
2411 'tags' => 'tags text',
2412 'notes' => 'notes text',
2414 if (!$isContactExport) {
2415 unset($columns['groups']);
2416 unset($columns['tags']);
2417 unset($columns['notes']);
2423 * Get Case SQL columns.
2427 protected function getCaseSqlColumns() {
2429 'case_start_date' => 'case_start_date varchar(32)',
2430 'case_end_date' => 'case_end_date varchar(32)',
2431 'case_subject' => 'case_subject varchar(128)',
2432 'case_source_contact_id' => 'case_source_contact_id varchar(255)',
2433 'case_activity_status' => 'case_activity_status text',
2434 'case_activity_duration' => 'case_activity_duration text',
2435 'case_activity_medium_id' => 'case_activity_medium_id varchar(255)',
2436 'case_activity_details' => 'case_activity_details text',
2437 'case_activity_is_auto' => 'case_activity_is_auto text',
2438 'contact_id' => 'contact_id varchar(255)',
2439 'case_id' => 'case_id varchar(16)',
2440 'case_activity_subject' => 'case_activity_subject text',
2441 'case_status' => 'case_status text',
2442 'case_type' => 'case_type text',
2443 'case_role' => 'case_role text',
2444 'case_deleted' => 'case_deleted varchar(16)',
2445 'case_recent_activity_date' => 'case_recent_activity_date text',
2446 'case_recent_activity_type' => 'case_recent_activity_type text',
2447 'case_scheduled_activity_date' => 'case_scheduled_activity_date text',
2452 * Get activity sql columns.
2456 protected function getActivitySqlColumns() {
2458 'activity_id' => 'activity_id varchar(16)',
2459 'activity_type' => 'activity_type varchar(255)',
2460 'activity_type_id' => 'activity_type_id varchar(16)',
2461 'activity_subject' => 'activity_subject varchar(255)',
2462 'activity_date_time' => 'activity_date_time varchar(32)',
2463 'activity_duration' => 'activity_duration varchar(16)',
2464 'activity_location' => 'activity_location varchar(255)',
2465 'activity_details' => 'activity_details longtext',
2466 'activity_status' => 'activity_status varchar(255)',
2467 'activity_priority' => 'activity_priority varchar(255)',
2468 'source_contact' => 'source_contact varchar(255)',
2469 'source_record_id' => 'source_record_id varchar(255)',
2470 'activity_is_test' => 'activity_is_test varchar(16)',
2471 'activity_campaign_id' => 'activity_campaign_id varchar(128)',
2472 'result' => 'result text',
2473 'activity_engagement_level' => 'activity_engagement_level varchar(16)',
2474 'parent_id' => 'parent_id varchar(255)',
2479 * Get participant sql columns.
2483 protected function getParticipantSqlColumns() {
2485 'event_id' => 'event_id varchar(16)',
2486 'event_title' => 'event_title varchar(255)',
2487 'event_start_date' => 'event_start_date varchar(32)',
2488 'event_end_date' => 'event_end_date varchar(32)',
2489 'event_type' => 'event_type varchar(255)',
2490 'participant_id' => 'participant_id varchar(16)',
2491 'participant_status' => 'participant_status varchar(255)',
2492 'participant_status_id' => 'participant_status_id varchar(16)',
2493 'participant_role' => 'participant_role varchar(255)',
2494 'participant_role_id' => 'participant_role_id varchar(128)',
2495 'participant_note' => 'participant_note text',
2496 'participant_register_date' => 'participant_register_date varchar(32)',
2497 'participant_source' => 'participant_source varchar(128)',
2498 'participant_fee_level' => 'participant_fee_level longtext',
2499 'participant_is_test' => 'participant_is_test varchar(16)',
2500 'participant_is_pay_later' => 'participant_is_pay_later varchar(16)',
2501 'participant_fee_amount' => 'participant_fee_amount varchar(32)',
2502 'participant_discount_name' => 'participant_discount_name varchar(16)',
2503 'participant_fee_currency' => 'participant_fee_currency varchar(3)',
2504 'participant_registered_by_id' => 'participant_registered_by_id varchar(16)',
2505 'participant_campaign_id' => 'participant_campaign_id varchar(128)',
2510 * Get contribution sql columns.
2514 public function getContributionSqlColumns() {
2516 'civicrm_primary_id' => 'civicrm_primary_id varchar(16)',
2517 'contact_type' => 'contact_type varchar(64)',
2518 'contact_sub_type' => 'contact_sub_type varchar(255)',
2519 'do_not_email' => 'do_not_email varchar(16)',
2520 'do_not_phone' => 'do_not_phone varchar(16)',
2521 'do_not_mail' => 'do_not_mail varchar(16)',
2522 'do_not_sms' => 'do_not_sms varchar(16)',
2523 'do_not_trade' => 'do_not_trade varchar(16)',
2524 'is_opt_out' => 'is_opt_out varchar(16)',
2525 'legal_identifier' => 'legal_identifier varchar(32)',
2526 'external_identifier' => 'external_identifier varchar(64)',
2527 'sort_name' => 'sort_name varchar(128)',
2528 'display_name' => 'display_name varchar(128)',
2529 'nick_name' => 'nick_name varchar(128)',
2530 'legal_name' => 'legal_name varchar(128)',
2531 'image_url' => 'image_url longtext',
2532 'preferred_communication_method' => 'preferred_communication_method varchar(255)',
2533 'preferred_language' => 'preferred_language varchar(5)',
2534 'preferred_mail_format' => 'preferred_mail_format varchar(8)',
2535 'hash' => 'hash varchar(32)',
2536 'contact_source' => 'contact_source varchar(255)',
2537 'first_name' => 'first_name varchar(64)',
2538 'middle_name' => 'middle_name varchar(64)',
2539 'last_name' => 'last_name varchar(64)',
2540 'prefix_id' => 'prefix_id varchar(255)',
2541 'suffix_id' => 'suffix_id varchar(255)',
2542 'formal_title' => 'formal_title varchar(64)',
2543 'communication_style_id' => 'communication_style_id varchar(16)',
2544 'email_greeting_id' => 'email_greeting_id varchar(16)',
2545 'postal_greeting_id' => 'postal_greeting_id varchar(16)',
2546 'addressee_id' => 'addressee_id varchar(16)',
2547 'job_title' => 'job_title varchar(255)',
2548 'gender_id' => 'gender_id varchar(16)',
2549 'birth_date' => 'birth_date varchar(32)',
2550 'is_deceased' => 'is_deceased varchar(16)',
2551 'deceased_date' => 'deceased_date varchar(32)',
2552 'household_name' => 'household_name varchar(128)',
2553 'organization_name' => 'organization_name varchar(128)',
2554 'sic_code' => 'sic_code varchar(8)',
2555 'user_unique_id' => 'user_unique_id varchar(255)',
2556 'current_employer_id' => 'current_employer_id varchar(16)',
2557 'contact_is_deleted' => 'contact_is_deleted varchar(16)',
2558 'created_date' => 'created_date varchar(32)',
2559 'modified_date' => 'modified_date varchar(32)',
2560 'addressee' => 'addressee varchar(255)',
2561 'email_greeting' => 'email_greeting varchar(255)',
2562 'postal_greeting' => 'postal_greeting varchar(255)',
2563 'current_employer' => 'current_employer varchar(128)',
2564 'location_type' => 'location_type text',
2565 'street_address' => 'street_address varchar(96)',
2566 'street_number' => 'street_number varchar(16)',
2567 'street_number_suffix' => 'street_number_suffix varchar(8)',
2568 'street_name' => 'street_name varchar(64)',
2569 'street_unit' => 'street_unit varchar(16)',
2570 'supplemental_address_1' => 'supplemental_address_1 varchar(96)',
2571 'supplemental_address_2' => 'supplemental_address_2 varchar(96)',
2572 'supplemental_address_3' => 'supplemental_address_3 varchar(96)',
2573 'city' => 'city varchar(64)',
2574 'postal_code_suffix' => 'postal_code_suffix varchar(12)',
2575 'postal_code' => 'postal_code varchar(64)',
2576 'geo_code_1' => 'geo_code_1 varchar(32)',
2577 'geo_code_2' => 'geo_code_2 varchar(32)',
2578 'address_name' => 'address_name varchar(255)',
2579 'master_id' => 'master_id varchar(128)',
2580 'county' => 'county varchar(64)',
2581 'state_province' => 'state_province varchar(64)',
2582 'country' => 'country varchar(64)',
2583 'phone' => 'phone varchar(32)',
2584 'phone_ext' => 'phone_ext varchar(16)',
2585 'email' => 'email varchar(254)',
2586 'on_hold' => 'on_hold varchar(16)',
2587 'is_bulkmail' => 'is_bulkmail varchar(16)',
2588 'signature_text' => 'signature_text longtext',
2589 'signature_html' => 'signature_html longtext',
2590 'im_provider' => 'im_provider text',
2591 'im_screen_name' => 'im_screen_name varchar(64)',
2592 'openid' => 'openid varchar(255)',
2593 'world_region' => 'world_region varchar(128)',
2594 'url' => 'url varchar(128)',
2595 'phone_type_id' => 'phone_type_id varchar(16)',
2596 'financial_type' => 'financial_type varchar(64)',
2597 'contribution_source' => 'contribution_source varchar(255)',
2598 'receive_date' => 'receive_date varchar(32)',
2599 'thankyou_date' => 'thankyou_date varchar(32)',
2600 'cancel_date' => 'cancel_date varchar(32)',
2601 'total_amount' => 'total_amount varchar(32)',
2602 'accounting_code' => 'accounting_code varchar(64)',
2603 'payment_instrument' => 'payment_instrument text',
2604 'payment_instrument_id' => 'payment_instrument_id varchar(16)',
2605 'contribution_check_number' => 'contribution_check_number varchar(255)',
2606 'non_deductible_amount' => 'non_deductible_amount varchar(32)',
2607 'fee_amount' => 'fee_amount varchar(32)',
2608 'net_amount' => 'net_amount varchar(32)',
2609 'trxn_id' => 'trxn_id varchar(255)',
2610 'invoice_id' => 'invoice_id varchar(255)',
2611 'invoice_number' => 'invoice_number varchar(255)',
2612 'currency' => 'currency varchar(3)',
2613 'cancel_reason' => 'cancel_reason longtext',
2614 'receipt_date' => 'receipt_date varchar(32)',
2615 'product_name' => 'product_name varchar(255)',
2616 'sku' => 'sku varchar(50)',
2617 'product_option' => 'product_option varchar(255)',
2618 'fulfilled_date' => 'fulfilled_date varchar(32)',
2619 'contribution_start_date' => 'contribution_start_date varchar(32)',
2620 'contribution_end_date' => 'contribution_end_date varchar(32)',
2621 'is_test' => 'is_test varchar(16)',
2622 'is_pay_later' => 'is_pay_later varchar(16)',
2623 'contribution_status' => 'contribution_status text',
2624 'contribution_recur_id' => 'contribution_recur_id varchar(16)',
2625 'amount_level' => 'amount_level longtext',
2626 'contribution_note' => 'contribution_note text',
2627 'contribution_batch' => 'contribution_batch text',
2628 'contribution_campaign_title' => 'contribution_campaign_title varchar(255)',
2629 'contribution_campaign_id' => 'contribution_campaign_id varchar(128)',
2630 'contribution_product_id' => 'contribution_product_id varchar(255)',
2631 'contribution_soft_credit_name' => 'contribution_soft_credit_name varchar(255)',
2632 'contribution_soft_credit_amount' => 'contribution_soft_credit_amount varchar(255)',
2633 'contribution_soft_credit_type' => 'contribution_soft_credit_type varchar(255)',
2634 'contribution_soft_credit_contact_id' => 'contribution_soft_credit_contact_id varchar(255)',
2635 'contribution_soft_credit_contribution_id' => 'contribution_soft_credit_contribution_id varchar(255)',
2640 * Get pledge sql columns.
2644 public function getPledgeSqlColumns() {
2646 'pledge_id' => 'pledge_id varchar(16)',
2647 'pledge_amount' => 'pledge_amount varchar(32)',
2648 'pledge_total_paid' => 'pledge_total_paid text',
2649 'pledge_create_date' => 'pledge_create_date varchar(32)',
2650 'pledge_start_date' => 'pledge_start_date text',
2651 'pledge_next_pay_date' => 'pledge_next_pay_date text',
2652 'pledge_next_pay_amount' => 'pledge_next_pay_amount text',
2653 'pledge_status' => 'pledge_status varchar(255)',
2654 'pledge_is_test' => 'pledge_is_test varchar(16)',
2655 'pledge_contribution_page_id' => 'pledge_contribution_page_id varchar(255)',
2656 'pledge_financial_type' => 'pledge_financial_type text',
2657 'pledge_frequency_interval' => 'pledge_frequency_interval varchar(255)',
2658 'pledge_frequency_unit' => 'pledge_frequency_unit varchar(255)',
2659 'pledge_currency' => 'pledge_currency text',
2660 'pledge_campaign_id' => 'pledge_campaign_id varchar(128)',
2661 'pledge_balance_amount' => 'pledge_balance_amount text',
2662 'pledge_payment_id' => 'pledge_payment_id varchar(16)',
2663 'pledge_payment_scheduled_amount' => 'pledge_payment_scheduled_amount varchar(32)',
2664 'pledge_payment_scheduled_date' => 'pledge_payment_scheduled_date varchar(32)',
2665 'pledge_payment_paid_amount' => 'pledge_payment_paid_amount text',
2666 'pledge_payment_paid_date' => 'pledge_payment_paid_date text',
2667 'pledge_payment_reminder_date' => 'pledge_payment_reminder_date varchar(32)',
2668 'pledge_payment_reminder_count' => 'pledge_payment_reminder_count varchar(16)',
2669 'pledge_payment_status' => 'pledge_payment_status varchar(255)',
2674 * Get membership sql columns.
2678 public function getMembershipSqlColumns() {
2680 'membership_type' => 'membership_type varchar(128)',
2681 'member_is_test' => 'member_is_test varchar(16)',
2682 'member_is_pay_later' => 'member_is_pay_later varchar(16)',
2683 'join_date' => 'join_date varchar(32)',
2684 'membership_start_date' => 'membership_start_date varchar(32)',
2685 'membership_end_date' => 'membership_end_date varchar(32)',
2686 'membership_source' => 'membership_source varchar(128)',
2687 'membership_status' => 'membership_status varchar(255)',
2688 'membership_id' => 'membership_id varchar(16)',
2689 'owner_membership_id' => 'owner_membership_id varchar(16)',
2690 'max_related' => 'max_related text',
2691 'membership_recur_id' => 'membership_recur_id varchar(255)',
2692 'member_campaign_id' => 'member_campaign_id varchar(128)',
2693 'member_is_override' => 'member_is_override text',
2694 'member_auto_renew' => 'member_auto_renew text',
2699 * Change our location types so we have some edge cases in the mix.
2701 * - a space in the name
2702 * - name differs from label
2703 * - non-anglo char in the label (not valid in the name).
2705 protected function diversifyLocationTypes() {
2706 $this->locationTypes
['Main'] = $this->callAPISuccess('Location_type', 'get', [
2709 'api.LocationType.Create' => ['display_name' => 'Méin'],
2711 $this->locationTypes
['Whare Kai'] = $this->callAPISuccess('Location_type', 'create', [
2712 'name' => 'Whare Kai',
2713 'display_name' => 'Whare Kai',