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 = [];
31 * Master Address ID created for testing.
35 protected $masterAddressID;
37 public function tearDown() {
38 $this->quickCleanup(['civicrm_contact', 'civicrm_email', 'civicrm_address', 'civicrm_relationship']);
39 $this->quickCleanUpFinancialEntities();
44 * Basic test to ensure the exportComponents function completes without error.
46 public function testExportComponentsNull() {
47 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
54 CRM_Export_Form_Select
::CONTACT_EXPORT
,
61 'suppress_csv_for_testing' => TRUE,
65 // delete the export temp table and component table
66 $sql = "DROP TABLE IF EXISTS {$tableName}";
67 CRM_Core_DAO
::executeQuery($sql);
71 * Basic test to ensure the exportComponents function can export selected fields for contribution.
73 public function testExportComponentsContribution() {
74 $this->setUpContributionExportData();
75 $selectedFields = array(
76 array('Individual', 'first_name'),
77 array('Individual', 'last_name'),
78 array('Contribution', 'receive_date'),
79 array('Contribution', 'contribution_source'),
80 array('Individual', 'street_address', 1),
81 array('Individual', 'city', 1),
82 array('Individual', 'country', 1),
83 array('Individual', 'email', 1),
84 array('Contribution', 'trxn_id'),
87 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
89 $this->contributionIDs
,
94 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
95 'civicrm_contribution.id IN ( ' . implode(',', $this->contributionIDs
) . ')',
100 'exportOption' => CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
101 'suppress_csv_for_testing' => TRUE,
105 // delete the export temp table and component table
106 $sql = "DROP TABLE IF EXISTS {$tableName}";
107 CRM_Core_DAO
::executeQuery($sql);
111 * Basic test to ensure the exportComponents function can export selected fields for contribution.
113 public function testExportComponentsActivity() {
114 $this->setUpActivityExportData();
115 $selectedFields = array(
116 array('Individual', 'display_name'),
117 array('Individual', '5_a_b', 'display_name'),
120 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
124 '`activity_date_time` desc',
127 CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
128 'civicrm_activity.id IN ( ' . implode(',', $this->activityIDs
) . ')',
133 'exportOption' => CRM_Export_Form_Select
::ACTIVITY_EXPORT
,
134 'suppress_csv_for_testing' => TRUE,
138 // delete the export temp table and component table
139 $sql = "DROP TABLE IF EXISTS {$tableName}";
140 CRM_Core_DAO
::executeQuery($sql);
144 * Test the function that extracts the arrays used to structure the output.
146 * The keys in the output fields array should by matched by field aliases in the sql query (with
147 * exceptions of course - currently country is one - although maybe a future refactor can change that!).
149 * We are trying to move towards simpler processing in the per row iteration as that may be
150 * repeated 100,000 times and in general we should simply be able to match the query fields to
151 * our expected rows & do a little pseudoconstant mapping.
153 public function testGetExportStructureArrays() {
154 // This is how return properties are formatted internally within the function for passing to the BAO query.
155 $returnProperties = array(
159 'contribution_source' => 1,
162 'street_address' => 1,
173 'contribution_id' => 1,
176 $contactRelationshipTypes = CRM_Contact_BAO_Relationship
::getContactRelationshipType(
186 $query = new CRM_Contact_BAO_Query(array(), $returnProperties, NULL,
187 FALSE, FALSE, CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
,
188 FALSE, TRUE, TRUE, NULL, 'AND'
191 list($select) = $query->query();
192 $pattern = '/as `?([^`,]*)/';
193 $queryFieldAliases = array();
194 preg_match_all($pattern, $select, $queryFieldAliases, PREG_PATTERN_ORDER
);
195 $processor = new CRM_Export_BAO_ExportProcessor(CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
);
196 $processor->setQueryFields($query->_fields
);
198 list($outputFields) = CRM_Export_BAO_Export
::getExportStructureArrays($returnProperties, $processor, $contactRelationshipTypes, '', array());
199 foreach (array_keys($outputFields) as $fieldAlias) {
200 if ($fieldAlias == 'Home-country') {
201 $this->assertTrue(in_array($fieldAlias . '_id', $queryFieldAliases[1]), 'Country is subject to some funky translate so we make sure country id is present');
204 $this->assertTrue(in_array($fieldAlias, $queryFieldAliases[1]), 'looking for field ' . $fieldAlias . ' in generaly the alias fields need to match the outputfields');
211 * Set up some data for us to do testing on.
213 public function setUpContributionExportData() {
214 $this->setUpContactExportData();
215 $this->contributionIDs
[] = $this->contributionCreate(array('contact_id' => $this->contactIDs
[0], 'trxn_id' => 'null', 'invoice_id' => 'null'));
216 $this->contributionIDs
[] = $this->contributionCreate(array('contact_id' => $this->contactIDs
[1], 'trxn_id' => 'null', 'invoice_id' => 'null'));
220 * Set up some data for us to do testing on.
222 public function setUpActivityExportData() {
223 $this->setUpContactExportData();
224 $this->activityIDs
[] = $this->activityCreate(array('contact_id' => $this->contactIDs
[0]))['id'];
228 * Set up some data for us to do testing on.
230 public function setUpContactExportData() {
231 $this->contactIDs
[] = $contactA = $this->individualCreate(['gender_id' => 'Female']);
232 // Create address for contact A.
234 'contact_id' => $contactA,
235 'location_type_id' => 'Home',
236 'street_address' => 'Ambachtstraat 23',
237 'postal_code' => '6971 BN',
238 'country_id' => '1152',
242 $result = $this->callAPISuccess('address', 'create', $params);
243 $addressId = $result['id'];
245 $this->callAPISuccess('email', 'create', array(
246 'id' => $this->callAPISuccessGetValue('Email', ['contact_id' => $params['contact_id'], 'return' => 'id']),
247 'location_type_id' => 'Home',
248 'email' => 'home@example.com',
251 $this->callAPISuccess('email', 'create', array('contact_id' => $params['contact_id'], 'location_type_id' => 'Work', 'email' => 'work@example.com', 'is_primary' => 0));
253 $params['is_primary'] = 0;
254 $params['location_type_id'] = 'Work';
255 $this->callAPISuccess('address', 'create', $params);
256 $this->contactIDs
[] = $contactB = $this->individualCreate();
258 $this->callAPISuccess('address', 'create', array(
259 'contact_id' => $contactB,
260 'location_type_id' => "Home",
261 'master_id' => $addressId,
263 $this->masterAddressID
= $addressId;
268 * Test variants of primary address exporting.
270 * @param int $isPrimaryOnly
272 * @dataProvider getPrimarySearchOptions
274 public function testExportPrimaryAddress($isPrimaryOnly) {
275 \Civi
::settings()->set('searchPrimaryDetailsOnly', $isPrimaryOnly);
276 $this->setUpContactExportData();
278 $selectedFields = [['Individual', 'email', ' '], ['Individual', 'email', '1'], ['Individual', 'email', '2']];
279 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
282 [['email', 'LIKE', 'c', 0, 1]],
286 CRM_Export_Form_Select
::CONTACT_EXPORT
,
287 "contact_a.id IN ({$this->contactIDs[0]}, {$this->contactIDs[1]})",
292 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
293 'suppress_csv_for_testing' => TRUE,
297 $dao = CRM_Core_DAO
::executeQuery('SELECT * from ' . $tableName);
299 $this->assertEquals('home@example.com', $dao->email
);
300 $this->assertEquals('work@example.com', $dao->work_email
);
301 $this->assertEquals('home@example.com', $dao->home_email
);
302 $this->assertEquals(2, $dao->N
);
303 \Civi
::settings()->set('searchPrimaryDetailsOnly', FALSE);
307 * Get the options for the primary search setting field.
310 public function getPrimarySearchOptions() {
311 return [[TRUE], [FALSE]];
315 * Test that when exporting a pseudoField it is reset for NULL entries.
317 * ie. we have a contact WITH a gender & one without - make sure the latter one
318 * does NOT retain the gender of the former.
320 public function testExportPseudoField() {
321 $this->setUpContactExportData();
322 $selectedFields = [['Individual', 'gender_id']];
323 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
325 $this->contactIDs
[1],
330 CRM_Export_Form_Select
::CONTACT_EXPORT
,
331 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
336 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
337 'suppress_csv_for_testing' => TRUE,
340 $this->assertEquals('Female,', CRM_Core_DAO
::singleValueQuery("SELECT GROUP_CONCAT(gender_id) FROM {$tableName}"));
344 * Test that when exporting a pseudoField it is reset for NULL entries.
346 * This is specific to the example in CRM-14398
348 public function testExportPseudoFieldCampaign() {
349 $this->setUpContributionExportData();
350 $campaign = $this->callAPISuccess('Campaign', 'create', ['title' => 'Big campaign']);
351 $this->callAPISuccess('Contribution', 'create', ['campaign_id' => 'Big_campaign', 'id' => $this->contributionIDs
[0]]);
352 $selectedFields = [['Individual', 'gender_id'], ['Contribution', 'contribution_campaign_title']];
353 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
355 $this->contactIDs
[1],
360 CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
,
361 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
366 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
367 'suppress_csv_for_testing' => TRUE,
370 $this->assertEquals('Big campaign,', CRM_Core_DAO
::singleValueQuery("SELECT GROUP_CONCAT(contribution_campaign_title) FROM {$tableName}"));
374 * Test exporting relationships.
376 * This is to ensure that CRM-13995 remains fixed.
378 public function testExportRelationshipsMergeToHousehold() {
379 list($householdID, $houseHoldTypeID) = $this->setUpHousehold();
382 ['Individual', $houseHoldTypeID . '_a_b', 'state_province', ''],
383 ['Individual', $houseHoldTypeID . '_a_b', 'city', ''],
384 ['Individual', 'city', ''],
385 ['Individual', 'state_province', ''],
387 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
394 CRM_Export_Form_Select
::CONTACT_EXPORT
,
395 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
400 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
401 'suppress_csv_for_testing' => TRUE,
404 $dao = CRM_Core_DAO
::executeQuery("SELECT * FROM {$tableName}");
405 while ($dao->fetch()) {
406 $this->assertEquals('Portland', $dao->city
);
407 $this->assertEquals('ME', $dao->state_province
);
408 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
409 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
415 * Test exporting relationships.
417 public function testExportRelationshipsMergeToHouseholdAllFields() {
418 $this->markTestIncomplete('Does not yet work under CI due to mysql limitation (number of columns in table). Works on some boxes');
419 list($householdID) = $this->setUpHousehold();
420 list($tableName) = CRM_Export_BAO_Export
::exportComponents(
427 CRM_Export_Form_Select
::CONTACT_EXPORT
,
428 "contact_a.id IN (" . implode(",", $this->contactIDs
) . ")",
433 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
434 'suppress_csv_for_testing' => TRUE,
437 $dao = CRM_Core_DAO
::executeQuery("SELECT * FROM {$tableName}");
438 while ($dao->fetch()) {
439 $this->assertEquals('Portland', $dao->city
);
440 $this->assertEquals('ME', $dao->state_province
);
441 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
442 $this->assertEquals($householdID, $dao->civicrm_primary_id
);
443 $this->assertEquals('Unit Test Household', $dao->addressee
);
444 $this->assertEquals('Unit Test Household', $dao->display_name
);
449 * Test master_address_id field.
451 public function testExportMasterAddress() {
452 $this->setUpContactExportData();
454 //export the master address for contact B
455 $selectedFields = array(
456 array('Individual', 'master_id', 1),
458 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
460 array($this->contactIDs
[1]),
465 CRM_Export_Form_Select
::CONTACT_EXPORT
,
466 "contact_a.id IN ({$this->contactIDs[1]})",
471 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
472 'suppress_csv_for_testing' => TRUE,
475 $field = key($sqlColumns);
477 //assert the exported result
478 $masterName = CRM_Core_DAO
::singleValueQuery("SELECT {$field} FROM {$tableName}");
479 $displayName = CRM_Contact_BAO_Contact
::getMasterDisplayName($this->masterAddressID
);
480 $this->assertEquals($displayName, $masterName);
482 // delete the export temp table and component table
483 $sql = "DROP TABLE IF EXISTS {$tableName}";
484 CRM_Core_DAO
::executeQuery($sql);
488 * Test that deceased and do not mail contacts are removed from contacts before
490 public function testExportDeceasedDoNotMail() {
491 $contactA = $this->callAPISuccess('contact', 'create', array(
492 'first_name' => 'John',
493 'last_name' => 'Doe',
494 'contact_type' => 'Individual',
497 $contactB = $this->callAPISuccess('contact', 'create', array(
498 'first_name' => 'Jane',
499 'last_name' => 'Doe',
500 'contact_type' => 'Individual',
504 //create address for contact A
505 $result = $this->callAPISuccess('address', 'create', array(
506 'contact_id' => $contactA['id'],
507 'location_type_id' => 'Home',
508 'street_address' => 'ABC 12',
509 'postal_code' => '123 AB',
510 'country_id' => '1152',
515 //create address for contact B
516 $result = $this->callAPISuccess('address', 'create', array(
517 'contact_id' => $contactB['id'],
518 'location_type_id' => 'Home',
519 'street_address' => 'ABC 12',
520 'postal_code' => '123 AB',
521 'country_id' => '1152',
526 //export and merge contacts with same address
527 list($tableName, $sqlColumns) = CRM_Export_BAO_Export
::exportComponents(
529 array($contactA['id'], $contactB['id']),
534 CRM_Export_Form_Select
::CONTACT_EXPORT
,
535 "contact_a.id IN ({$contactA['id']}, {$contactB['id']})",
540 'exportOption' => CRM_Export_Form_Select
::CONTACT_EXPORT
,
541 'mergeOption' => TRUE,
542 'suppress_csv_for_testing' => TRUE,
543 'postal_mailing_export' => array(
544 'postal_mailing_export' => TRUE,
549 $greeting = CRM_Core_DAO
::singleValueQuery("SELECT email_greeting FROM {$tableName}");
551 //Assert email_greeting is not merged
552 $this->assertNotContains(',', (string) $greeting);
554 // delete the export temp table and component table
555 $sql = "DROP TABLE IF EXISTS {$tableName}";
556 CRM_Core_DAO
::executeQuery($sql);
562 protected function setUpHousehold() {
563 $this->setUpContactExportData();
564 $householdID = $this->householdCreate([
565 'api.Address.create' => [
566 'city' => 'Portland',
567 'state_province_id' => 'Maine',
568 'location_type_id' => 'Home'
572 $relationshipTypes = $this->callAPISuccess('RelationshipType', 'get', [])['values'];
573 $houseHoldTypeID = NULL;
574 foreach ($relationshipTypes as $id => $relationshipType) {
575 if ($relationshipType['name_a_b'] === 'Household Member of') {
576 $houseHoldTypeID = $relationshipType['id'];
579 $this->callAPISuccess('Relationship', 'create', [
580 'contact_id_a' => $this->contactIDs
[0],
581 'contact_id_b' => $householdID,
582 'relationship_type_id' => $houseHoldTypeID,
584 $this->callAPISuccess('Relationship', 'create', [
585 'contact_id_a' => $this->contactIDs
[1],
586 'contact_id_b' => $householdID,
587 'relationship_type_id' => $houseHoldTypeID,
589 return array($householdID, $houseHoldTypeID);