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) = 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     $processor = new CRM_Export_BAO_ExportProcessor(CRM_Contact_BAO_Query::MODE_CONTRIBUTE, 'AND');
 196     $processor->setQueryFields($query->_fields);
 197
 198     list($outputFields) = CRM_Export_BAO_Export::getExportStructureArrays($returnProperties, $processor, $contactRelationshipTypes, '');
 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');
 202       }
 203       else {
 204         $this->assertTrue(in_array($fieldAlias, $queryFieldAliases[1]), 'looking for field ' . $fieldAlias . ' in generaly the alias fields need to match the outputfields');
 205       }
 206     }
 207
 208   }
 209
 210   /**
 211    * Set up some data for us to do testing on.
 212    */
 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'));
 217   }
 218
 219   /**
 220    * Set up some data for us to do testing on.
 221    */
 222   public function setUpActivityExportData() {
 223     $this->setUpContactExportData();
 224     $this->activityIDs[] = $this->activityCreate(array('contact_id' => $this->contactIDs[0]))['id'];
 225   }
 226
 227   /**
 228    * Set up some data for us to do testing on.
 229    */
 230   public function setUpContactExportData() {
 231     $this->contactIDs[] = $contactA = $this->individualCreate(['gender_id' => 'Female']);
 232     // Create address for contact A.
 233     $params = array(
 234       'contact_id' => $contactA,
 235       'location_type_id' => 'Home',
 236       'street_address' => 'Ambachtstraat 23',
 237       'postal_code' => '6971 BN',
 238       'country_id' => '1152',
 239       'city' => 'Brummen',
 240       'is_primary' => 1,
 241     );
 242     $result = $this->callAPISuccess('address', 'create', $params);
 243     $addressId = $result['id'];
 244
 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',
 249       'is_primary' => 1,
 250     ));
 251     $this->callAPISuccess('email', 'create', array('contact_id' => $params['contact_id'], 'location_type_id' => 'Work', 'email' => 'work@example.com', 'is_primary' => 0));
 252
 253     $params['is_primary'] = 0;
 254     $params['location_type_id'] = 'Work';
 255     $this->callAPISuccess('address', 'create', $params);
 256     $this->contactIDs[] = $contactB = $this->individualCreate();
 257
 258     $this->callAPISuccess('address', 'create', array(
 259       'contact_id' => $contactB,
 260       'location_type_id' => "Home",
 261       'master_id' => $addressId,
 262     ));
 263     $this->masterAddressID = $addressId;
 264
 265   }
 266
 267   /**
 268    * Test variants of primary address exporting.
 269    *
 270    * @param int $isPrimaryOnly
 271    *
 272    * @dataProvider getPrimarySearchOptions
 273    */
 274   public function testExportPrimaryAddress($isPrimaryOnly) {
 275     \Civi::settings()->set('searchPrimaryDetailsOnly', $isPrimaryOnly);
 276     $this->setUpContactExportData();
 277
 278     $selectedFields = [['Individual', 'email', ' '], ['Individual', 'email', '1'], ['Individual', 'email', '2']];
 279     list($tableName) = CRM_Export_BAO_Export::exportComponents(
 280       TRUE,
 281       [],
 282       [['email', 'LIKE', 'c', 0, 1]],
 283       NULL,
 284       $selectedFields,
 285       NULL,
 286       CRM_Export_Form_Select::CONTACT_EXPORT,
 287       "contact_a.id IN ({$this->contactIDs[0]}, {$this->contactIDs[1]})",
 288       NULL,
 289       FALSE,
 290       FALSE,
 291       array(
 292         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 293         'suppress_csv_for_testing' => TRUE,
 294       )
 295     );
 296
 297     $dao = CRM_Core_DAO::executeQuery('SELECT * from ' . $tableName);
 298     $dao->fetch();
 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);
 304   }
 305
 306   /**
 307    * Get the options for the primary search setting field.
 308    * @return array
 309    */
 310   public function getPrimarySearchOptions() {
 311     return [[TRUE], [FALSE]];
 312   }
 313
 314   /**
 315    * Test that when exporting a pseudoField it is reset for NULL entries.
 316    *
 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.
 319    */
 320   public function testExportPseudoField() {
 321     $this->setUpContactExportData();
 322     $selectedFields = [['Individual', 'gender_id']];
 323     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 324       TRUE,
 325       $this->contactIDs[1],
 326       array(),
 327       NULL,
 328       $selectedFields,
 329       NULL,
 330       CRM_Export_Form_Select::CONTACT_EXPORT,
 331       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 332       NULL,
 333       FALSE,
 334       FALSE,
 335       array(
 336         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 337         'suppress_csv_for_testing' => TRUE,
 338       )
 339     );
 340     $this->assertEquals('Female,', CRM_Core_DAO::singleValueQuery("SELECT GROUP_CONCAT(gender_id) FROM {$tableName}"));
 341   }
 342
 343   /**
 344    * Test that when exporting a pseudoField it is reset for NULL entries.
 345    *
 346    * This is specific to the example in CRM-14398
 347    */
 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(
 354       TRUE,
 355       $this->contactIDs[1],
 356       array(),
 357       NULL,
 358       $selectedFields,
 359       NULL,
 360       CRM_Export_Form_Select::CONTRIBUTE_EXPORT,
 361       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 362       NULL,
 363       FALSE,
 364       FALSE,
 365       array(
 366         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 367         'suppress_csv_for_testing' => TRUE,
 368       )
 369     );
 370     $this->assertEquals('Big campaign,', CRM_Core_DAO::singleValueQuery("SELECT GROUP_CONCAT(contribution_campaign_title) FROM {$tableName}"));
 371   }
 372
 373   /**
 374    * Test exporting relationships.
 375    *
 376    * This is to ensure that CRM-13995 remains fixed.
 377    */
 378   public function testExportRelationshipsMergeToHousehold() {
 379     list($householdID, $houseHoldTypeID) = $this->setUpHousehold();
 380
 381     $selectedFields = [
 382       ['Individual', $houseHoldTypeID . '_a_b', 'state_province', ''],
 383       ['Individual', $houseHoldTypeID . '_a_b', 'city', ''],
 384       ['Individual', 'city', ''],
 385       ['Individual', 'state_province', ''],
 386     ];
 387     list($tableName) = CRM_Export_BAO_Export::exportComponents(
 388       FALSE,
 389       $this->contactIDs,
 390       [],
 391       NULL,
 392       $selectedFields,
 393       NULL,
 394       CRM_Export_Form_Select::CONTACT_EXPORT,
 395       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 396       NULL,
 397       FALSE,
 398       TRUE,
 399       [
 400         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 401         'suppress_csv_for_testing' => TRUE,
 402       ]
 403     );
 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);
 410     }
 411
 412   }
 413
 414   /**
 415    * Test exporting relationships.
 416    */
 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(
 421       FALSE,
 422       $this->contactIDs,
 423       [],
 424       NULL,
 425       NULL,
 426       NULL,
 427       CRM_Export_Form_Select::CONTACT_EXPORT,
 428       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 429       NULL,
 430       FALSE,
 431       TRUE,
 432       [
 433         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 434         'suppress_csv_for_testing' => TRUE,
 435       ]
 436     );
 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);
 445     }
 446   }
 447
 448   /**
 449    * Test master_address_id field.
 450    */
 451   public function testExportMasterAddress() {
 452     $this->setUpContactExportData();
 453
 454     //export the master address for contact B
 455     $selectedFields = array(
 456       array('Individual', 'master_id', 1),
 457     );
 458     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 459       TRUE,
 460       array($this->contactIDs[1]),
 461       array(),
 462       NULL,
 463       $selectedFields,
 464       NULL,
 465       CRM_Export_Form_Select::CONTACT_EXPORT,
 466       "contact_a.id IN ({$this->contactIDs[1]})",
 467       NULL,
 468       FALSE,
 469       FALSE,
 470       array(
 471         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 472         'suppress_csv_for_testing' => TRUE,
 473       )
 474     );
 475     $field = key($sqlColumns);
 476
 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);
 481
 482     // delete the export temp table and component table
 483     $sql = "DROP TABLE IF EXISTS {$tableName}";
 484     CRM_Core_DAO::executeQuery($sql);
 485   }
 486
 487   /**
 488    * Test that deceased and do not mail contacts are removed from contacts before
 489    */
 490   public function testExportDeceasedDoNotMail() {
 491     $contactA = $this->callAPISuccess('contact', 'create', array(
 492       'first_name' => 'John',
 493       'last_name' => 'Doe',
 494       'contact_type' => 'Individual',
 495     ));
 496
 497     $contactB = $this->callAPISuccess('contact', 'create', array(
 498       'first_name' => 'Jane',
 499       'last_name' => 'Doe',
 500       'contact_type' => 'Individual',
 501       'is_deceased' => 1,
 502     ));
 503
 504     //create address for contact A
 505     $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',
 511       'city' => 'ABC',
 512       'is_primary' => 1,
 513     ));
 514
 515     //create address for contact B
 516     $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',
 522       'city' => 'ABC',
 523       'is_primary' => 1,
 524     ));
 525
 526     //export and merge contacts with same address
 527     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 528       TRUE,
 529       array($contactA['id'], $contactB['id']),
 530       array(),
 531       NULL,
 532       NULL,
 533       NULL,
 534       CRM_Export_Form_Select::CONTACT_EXPORT,
 535       "contact_a.id IN ({$contactA['id']}, {$contactB['id']})",
 536       NULL,
 537       TRUE,
 538       FALSE,
 539       array(
 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,
 545         ),
 546       )
 547     );
 548
 549     $greeting = CRM_Core_DAO::singleValueQuery("SELECT email_greeting FROM {$tableName}");
 550
 551     //Assert email_greeting is not merged
 552     $this->assertNotContains(',', (string) $greeting);
 553
 554     // delete the export temp table and component table
 555     $sql = "DROP TABLE IF EXISTS {$tableName}";
 556     CRM_Core_DAO::executeQuery($sql);
 557   }
 558
 559   /**
 560    * @return array
 561    */
 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'
 569       ]
 570     ]);
 571
 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'];
 577       }
 578     }
 579     $this->callAPISuccess('Relationship', 'create', [
 580       'contact_id_a' => $this->contactIDs[0],
 581       'contact_id_b' => $householdID,
 582       'relationship_type_id' => $houseHoldTypeID,
 583     ]);
 584     $this->callAPISuccess('Relationship', 'create', [
 585       'contact_id_a' => $this->contactIDs[1],
 586       'contact_id_b' => $householdID,
 587       'relationship_type_id' => $houseHoldTypeID,
 588     ]);
 589     return array($householdID, $houseHoldTypeID);
 590   }
 591
 592 }