tests/phpunit/CRM/Export/BAO/ExportTest.php

   1 <?php
   2
   3 /**
   4  * Class CRM_Core_DAOTest
   5  * @group headless
   6  */
   7 class CRM_Export_BAO_ExportTest extends CiviUnitTestCase {
   8
   9   /**
  10    * Contact IDs created for testing.
  11    *
  12    * @var array
  13    */
  14   protected $contactIDs = [];
  15
  16   /**
  17    * Contribution IDs created for testing.
  18    *
  19    * @var array
  20    */
  21   protected $contributionIDs = [];
  22
  23   /**
  24    * Contribution IDs created for testing.
  25    *
  26    * @var array
  27    */
  28   protected $activityIDs = [];
  29
  30   /**
  31    * Master Address ID created for testing.
  32    *
  33    * @var int
  34    */
  35   protected $masterAddressID;
  36
  37   public function tearDown() {
  38     $this->quickCleanup(['civicrm_contact', 'civicrm_email', 'civicrm_address', 'civicrm_relationship']);
  39     $this->quickCleanUpFinancialEntities();
  40     parent::tearDown();
  41   }
  42
  43   /**
  44    * Basic test to ensure the exportComponents function completes without error.
  45    */
  46   public function testExportComponentsNull() {
  47     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
  48       TRUE,
  49       array(),
  50       array(),
  51       NULL,
  52       NULL,
  53       NULL,
  54       CRM_Export_Form_Select::CONTACT_EXPORT,
  55       NULL,
  56       NULL,
  57       FALSE,
  58       FALSE,
  59       array(
  60         'exportOption' => 1,
  61         'suppress_csv_for_testing' => TRUE,
  62       )
  63     );
  64
  65     // delete the export temp table and component table
  66     $sql = "DROP TABLE IF EXISTS {$tableName}";
  67     CRM_Core_DAO::executeQuery($sql);
  68   }
  69
  70   /**
  71    * Basic test to ensure the exportComponents function can export selected fields for contribution.
  72    */
  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'),
  85     );
  86
  87     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
  88       TRUE,
  89       $this->contributionIDs,
  90       array(),
  91       'receive_date desc',
  92       $selectedFields,
  93       NULL,
  94       CRM_Export_Form_Select::CONTRIBUTE_EXPORT,
  95       'civicrm_contribution.id IN ( ' . implode(',', $this->contributionIDs) . ')',
  96       NULL,
  97       FALSE,
  98       FALSE,
  99       array(
 100         'exportOption' => CRM_Export_Form_Select::CONTRIBUTE_EXPORT,
 101         'suppress_csv_for_testing' => TRUE,
 102       )
 103     );
 104
 105     // delete the export temp table and component table
 106     $sql = "DROP TABLE IF EXISTS {$tableName}";
 107     CRM_Core_DAO::executeQuery($sql);
 108   }
 109
 110   /**
 111    * Basic test to ensure the exportComponents function can export selected fields for contribution.
 112    */
 113   public function testExportComponentsActivity() {
 114     $this->setUpActivityExportData();
 115     $selectedFields = array(
 116       array('Individual', 'display_name'),
 117       array('Individual', '5_a_b', 'display_name'),
 118     );
 119
 120     list($tableName) = CRM_Export_BAO_Export::exportComponents(
 121       FALSE,
 122       $this->activityIDs,
 123       array(),
 124       '`activity_date_time` desc',
 125       $selectedFields,
 126       NULL,
 127       CRM_Export_Form_Select::ACTIVITY_EXPORT,
 128       'civicrm_activity.id IN ( ' . implode(',', $this->activityIDs) . ')',
 129       NULL,
 130       FALSE,
 131       FALSE,
 132       array(
 133         'exportOption' => CRM_Export_Form_Select::ACTIVITY_EXPORT,
 134         'suppress_csv_for_testing' => TRUE,
 135       )
 136     );
 137
 138     // delete the export temp table and component table
 139     $sql = "DROP TABLE IF EXISTS {$tableName}";
 140     CRM_Core_DAO::executeQuery($sql);
 141   }
 142
 143   /**
 144    * Test the function that extracts the arrays used to structure the output.
 145    *
 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!).
 148    *
 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.
 152    */
 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(
 156       'first_name' => 1,
 157       'last_name' => 1,
 158       'receive_date' => 1,
 159       'contribution_source' => 1,
 160       'location' => array(
 161         'Home' => array(
 162           'street_address' => 1,
 163           'city' => 1,
 164           'country' => 1,
 165           'email' => 1,
 166           'im-1' => 1,
 167           'im_provider' => 1,
 168           'phone-1' => 1,
 169         ),
 170       ),
 171       'phone' => 1,
 172       'trxn_id' => 1,
 173       'contribution_id' => 1,
 174     );
 175
 176     $contactRelationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(
 177       NULL,
 178       NULL,
 179       NULL,
 180       NULL,
 181       TRUE,
 182       'name',
 183       FALSE
 184     );
 185
 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'
 189     );
 190
 191     list($select) = $query->query();
 192     $pattern = '/as `?([^`,]*)/';
 193     $queryFieldAliases = array();
 194     preg_match_all($pattern, $select, $queryFieldAliases, PREG_PATTERN_ORDER);
 195
 196     list($outputFields) = CRM_Export_BAO_Export::getExportStructureArrays($returnProperties, $query, $contactRelationshipTypes, '', array());
 197     foreach (array_keys($outputFields) as $fieldAlias) {
 198       if ($fieldAlias == 'Home-country') {
 199         $this->assertTrue(in_array($fieldAlias . '_id', $queryFieldAliases[1]), 'Country is subject to some funky translate so we make sure country id is present');
 200       }
 201       else {
 202         $this->assertTrue(in_array($fieldAlias, $queryFieldAliases[1]), 'looking for field ' . $fieldAlias . ' in generaly the alias fields need to match the outputfields');
 203       }
 204     }
 205
 206   }
 207
 208   /**
 209    * Set up some data for us to do testing on.
 210    */
 211   public function setUpContributionExportData() {
 212     $this->setUpContactExportData();
 213     $this->contributionIDs[] = $this->contributionCreate(array('contact_id' => $this->contactIDs[0], 'trxn_id' => 'null', 'invoice_id' => 'null'));
 214     $this->contributionIDs[] = $this->contributionCreate(array('contact_id' => $this->contactIDs[1], 'trxn_id' => 'null', 'invoice_id' => 'null'));
 215   }
 216
 217   /**
 218    * Set up some data for us to do testing on.
 219    */
 220   public function setUpActivityExportData() {
 221     $this->setUpContactExportData();
 222     $this->activityIDs[] = $this->activityCreate(array('contact_id' => $this->contactIDs[0]))['id'];
 223   }
 224
 225   /**
 226    * Set up some data for us to do testing on.
 227    */
 228   public function setUpContactExportData() {
 229     $this->contactIDs[] = $contactA = $this->individualCreate(['gender_id' => 'Female']);
 230     // Create address for contact A.
 231     $params = array(
 232       'contact_id' => $contactA,
 233       'location_type_id' => 'Home',
 234       'street_address' => 'Ambachtstraat 23',
 235       'postal_code' => '6971 BN',
 236       'country_id' => '1152',
 237       'city' => 'Brummen',
 238       'is_primary' => 1,
 239     );
 240     $result = $this->callAPISuccess('address', 'create', $params);
 241     $addressId = $result['id'];
 242
 243     $this->callAPISuccess('email', 'create', array(
 244       'id' => $this->callAPISuccessGetValue('Email', ['contact_id' => $params['contact_id'], 'return' => 'id']),
 245       'location_type_id' => 'Home',
 246       'email' => 'home@example.com',
 247       'is_primary' => 1,
 248     ));
 249     $this->callAPISuccess('email', 'create', array('contact_id' => $params['contact_id'], 'location_type_id' => 'Work', 'email' => 'work@example.com', 'is_primary' => 0));
 250
 251     $params['is_primary'] = 0;
 252     $params['location_type_id'] = 'Work';
 253     $this->callAPISuccess('address', 'create', $params);
 254     $this->contactIDs[] = $contactB = $this->individualCreate();
 255
 256     $this->callAPISuccess('address', 'create', array(
 257       'contact_id' => $contactB,
 258       'location_type_id' => "Home",
 259       'master_id' => $addressId,
 260     ));
 261     $this->masterAddressID = $addressId;
 262
 263   }
 264
 265   /**
 266    * Test variants of primary address exporting.
 267    *
 268    * @param int $isPrimaryOnly
 269    *
 270    * @dataProvider getPrimarySearchOptions
 271    */
 272   public function testExportPrimaryAddress($isPrimaryOnly) {
 273     \Civi::settings()->set('searchPrimaryDetailsOnly', $isPrimaryOnly);
 274     $this->setUpContactExportData();
 275
 276     $selectedFields = [['Individual', 'email', ' '], ['Individual', 'email', '1'], ['Individual', 'email', '2']];
 277     list($tableName) = CRM_Export_BAO_Export::exportComponents(
 278       TRUE,
 279       [],
 280       [['email', 'LIKE', 'c', 0, 1]],
 281       NULL,
 282       $selectedFields,
 283       NULL,
 284       CRM_Export_Form_Select::CONTACT_EXPORT,
 285       "contact_a.id IN ({$this->contactIDs[0]}, {$this->contactIDs[1]})",
 286       NULL,
 287       FALSE,
 288       FALSE,
 289       array(
 290         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 291         'suppress_csv_for_testing' => TRUE,
 292       )
 293     );
 294
 295     $dao = CRM_Core_DAO::executeQuery('SELECT * from ' . $tableName);
 296     $dao->fetch();
 297     $this->assertEquals('home@example.com', $dao->email);
 298     $this->assertEquals('work@example.com', $dao->work_email);
 299     $this->assertEquals('home@example.com', $dao->home_email);
 300     $this->assertEquals(2, $dao->N);
 301     \Civi::settings()->set('searchPrimaryDetailsOnly', FALSE);
 302   }
 303
 304   /**
 305    * Get the options for the primary search setting field.
 306    * @return array
 307    */
 308   public function getPrimarySearchOptions() {
 309     return [[TRUE], [FALSE]];
 310   }
 311
 312   /**
 313    * Test that when exporting a pseudoField it is reset for NULL entries.
 314    *
 315    * ie. we have a contact WITH a gender & one without - make sure the latter one
 316    * does NOT retain the gender of the former.
 317    */
 318   public function testExportPseudoField() {
 319     $this->setUpContactExportData();
 320     $selectedFields = [['Individual', 'gender_id']];
 321     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 322       TRUE,
 323       $this->contactIDs[1],
 324       array(),
 325       NULL,
 326       $selectedFields,
 327       NULL,
 328       CRM_Export_Form_Select::CONTACT_EXPORT,
 329       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 330       NULL,
 331       FALSE,
 332       FALSE,
 333       array(
 334         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 335         'suppress_csv_for_testing' => TRUE,
 336       )
 337     );
 338     $this->assertEquals('Female,', CRM_Core_DAO::singleValueQuery("SELECT GROUP_CONCAT(gender_id) FROM {$tableName}"));
 339   }
 340
 341   /**
 342    * Test that when exporting a pseudoField it is reset for NULL entries.
 343    *
 344    * This is specific to the example in CRM-14398
 345    */
 346   public function testExportPseudoFieldCampaign() {
 347     $this->setUpContributionExportData();
 348     $campaign = $this->callAPISuccess('Campaign', 'create', ['title' => 'Big campaign']);
 349     $this->callAPISuccess('Contribution', 'create', ['campaign_id' => 'Big_campaign', 'id' => $this->contributionIDs[0]]);
 350     $selectedFields = [['Individual', 'gender_id'], ['Contribution', 'contribution_campaign_title']];
 351     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 352       TRUE,
 353       $this->contactIDs[1],
 354       array(),
 355       NULL,
 356       $selectedFields,
 357       NULL,
 358       CRM_Export_Form_Select::CONTRIBUTE_EXPORT,
 359       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 360       NULL,
 361       FALSE,
 362       FALSE,
 363       array(
 364         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 365         'suppress_csv_for_testing' => TRUE,
 366       )
 367     );
 368     $this->assertEquals('Big campaign,', CRM_Core_DAO::singleValueQuery("SELECT GROUP_CONCAT(contribution_campaign_title) FROM {$tableName}"));
 369   }
 370
 371   /**
 372    * Test exporting relationships.
 373    *
 374    * This is to ensure that CRM-13995 remains fixed.
 375    */
 376   public function testExportRelationshipsMergeToHousehold() {
 377     list($householdID, $houseHoldTypeID) = $this->setUpHousehold();
 378
 379     $selectedFields = [
 380       ['Individual', $houseHoldTypeID . '_a_b', 'state_province', ''],
 381       ['Individual', $houseHoldTypeID . '_a_b', 'city', ''],
 382       ['Individual', 'city', ''],
 383       ['Individual', 'state_province', ''],
 384     ];
 385     list($tableName) = CRM_Export_BAO_Export::exportComponents(
 386       FALSE,
 387       $this->contactIDs,
 388       [],
 389       NULL,
 390       $selectedFields,
 391       NULL,
 392       CRM_Export_Form_Select::CONTACT_EXPORT,
 393       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 394       NULL,
 395       FALSE,
 396       TRUE,
 397       [
 398         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 399         'suppress_csv_for_testing' => TRUE,
 400       ]
 401     );
 402     $dao = CRM_Core_DAO::executeQuery("SELECT * FROM {$tableName}");
 403     while ($dao->fetch()) {
 404       $this->assertEquals('Portland', $dao->city);
 405       $this->assertEquals('ME', $dao->state_province);
 406       $this->assertEquals($householdID, $dao->civicrm_primary_id);
 407       $this->assertEquals($householdID, $dao->civicrm_primary_id);
 408     }
 409
 410   }
 411
 412   /**
 413    * Test exporting relationships.
 414    */
 415   public function testExportRelationshipsMergeToHouseholdAllFields() {
 416     $this->markTestIncomplete('Does not yet work under CI due to mysql limitation (number of columns in table). Works on some boxes');
 417     list($householdID) = $this->setUpHousehold();
 418     list($tableName) = CRM_Export_BAO_Export::exportComponents(
 419       FALSE,
 420       $this->contactIDs,
 421       [],
 422       NULL,
 423       NULL,
 424       NULL,
 425       CRM_Export_Form_Select::CONTACT_EXPORT,
 426       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 427       NULL,
 428       FALSE,
 429       TRUE,
 430       [
 431         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 432         'suppress_csv_for_testing' => TRUE,
 433       ]
 434     );
 435     $dao = CRM_Core_DAO::executeQuery("SELECT * FROM {$tableName}");
 436     while ($dao->fetch()) {
 437       $this->assertEquals('Portland', $dao->city);
 438       $this->assertEquals('ME', $dao->state_province);
 439       $this->assertEquals($householdID, $dao->civicrm_primary_id);
 440       $this->assertEquals($householdID, $dao->civicrm_primary_id);
 441       $this->assertEquals('Unit Test Household', $dao->addressee);
 442       $this->assertEquals('Unit Test Household', $dao->display_name);
 443     }
 444   }
 445
 446   /**
 447    * Test master_address_id field.
 448    */
 449   public function testExportMasterAddress() {
 450     $this->setUpContactExportData();
 451
 452     //export the master address for contact B
 453     $selectedFields = array(
 454       array('Individual', 'master_id', 1),
 455     );
 456     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 457       TRUE,
 458       array($this->contactIDs[1]),
 459       array(),
 460       NULL,
 461       $selectedFields,
 462       NULL,
 463       CRM_Export_Form_Select::CONTACT_EXPORT,
 464       "contact_a.id IN ({$this->contactIDs[1]})",
 465       NULL,
 466       FALSE,
 467       FALSE,
 468       array(
 469         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 470         'suppress_csv_for_testing' => TRUE,
 471       )
 472     );
 473     $field = key($sqlColumns);
 474
 475     //assert the exported result
 476     $masterName = CRM_Core_DAO::singleValueQuery("SELECT {$field} FROM {$tableName}");
 477     $displayName = CRM_Contact_BAO_Contact::getMasterDisplayName($this->masterAddressID);
 478     $this->assertEquals($displayName, $masterName);
 479
 480     // delete the export temp table and component table
 481     $sql = "DROP TABLE IF EXISTS {$tableName}";
 482     CRM_Core_DAO::executeQuery($sql);
 483   }
 484
 485   /**
 486    * Test that deceased and do not mail contacts are removed from contacts before
 487    */
 488   public function testExportDeceasedDoNotMail() {
 489     $contactA = $this->callAPISuccess('contact', 'create', array(
 490       'first_name' => 'John',
 491       'last_name' => 'Doe',
 492       'contact_type' => 'Individual',
 493     ));
 494
 495     $contactB = $this->callAPISuccess('contact', 'create', array(
 496       'first_name' => 'Jane',
 497       'last_name' => 'Doe',
 498       'contact_type' => 'Individual',
 499       'is_deceased' => 1,
 500     ));
 501
 502     //create address for contact A
 503     $result = $this->callAPISuccess('address', 'create', array(
 504       'contact_id' => $contactA['id'],
 505       'location_type_id' => 'Home',
 506       'street_address' => 'ABC 12',
 507       'postal_code' => '123 AB',
 508       'country_id' => '1152',
 509       'city' => 'ABC',
 510       'is_primary' => 1,
 511     ));
 512
 513     //create address for contact B
 514     $result = $this->callAPISuccess('address', 'create', array(
 515       'contact_id' => $contactB['id'],
 516       'location_type_id' => 'Home',
 517       'street_address' => 'ABC 12',
 518       'postal_code' => '123 AB',
 519       'country_id' => '1152',
 520       'city' => 'ABC',
 521       'is_primary' => 1,
 522     ));
 523
 524     //export and merge contacts with same address
 525     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 526       TRUE,
 527       array($contactA['id'], $contactB['id']),
 528       array(),
 529       NULL,
 530       NULL,
 531       NULL,
 532       CRM_Export_Form_Select::CONTACT_EXPORT,
 533       "contact_a.id IN ({$contactA['id']}, {$contactB['id']})",
 534       NULL,
 535       TRUE,
 536       FALSE,
 537       array(
 538         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 539         'mergeOption' => TRUE,
 540         'suppress_csv_for_testing' => TRUE,
 541         'postal_mailing_export' => array(
 542           'postal_mailing_export' => TRUE,
 543         ),
 544       )
 545     );
 546
 547     $greeting = CRM_Core_DAO::singleValueQuery("SELECT email_greeting FROM {$tableName}");
 548
 549     //Assert email_greeting is not merged
 550     $this->assertNotContains(',', (string) $greeting);
 551
 552     // delete the export temp table and component table
 553     $sql = "DROP TABLE IF EXISTS {$tableName}";
 554     CRM_Core_DAO::executeQuery($sql);
 555   }
 556
 557   /**
 558    * @return array
 559    */
 560   protected function setUpHousehold() {
 561     $this->setUpContactExportData();
 562     $householdID = $this->householdCreate([
 563       'api.Address.create' => [
 564         'city' => 'Portland',
 565         'state_province_id' => 'Maine',
 566         'location_type_id' => 'Home'
 567       ]
 568     ]);
 569
 570     $relationshipTypes = $this->callAPISuccess('RelationshipType', 'get', [])['values'];
 571     $houseHoldTypeID = NULL;
 572     foreach ($relationshipTypes as $id => $relationshipType) {
 573       if ($relationshipType['name_a_b'] === 'Household Member of') {
 574         $houseHoldTypeID = $relationshipType['id'];
 575       }
 576     }
 577     $this->callAPISuccess('Relationship', 'create', [
 578       'contact_id_a' => $this->contactIDs[0],
 579       'contact_id_b' => $householdID,
 580       'relationship_type_id' => $houseHoldTypeID,
 581     ]);
 582     $this->callAPISuccess('Relationship', 'create', [
 583       'contact_id_a' => $this->contactIDs[1],
 584       'contact_id_b' => $householdID,
 585       'relationship_type_id' => $houseHoldTypeID,
 586     ]);
 587     return array($householdID, $houseHoldTypeID);
 588   }
 589
 590 }