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     $this->setUpContactExportData();
 378     $householdID = $this->householdCreate(['api.Address.create' => ['city' => 'Portland', 'state_province_id' => 'Maine', 'location_type_id' => 'Home']]);
 379
 380     $relationshipTypes = $this->callAPISuccess('RelationshipType', 'get', [])['values'];
 381     $houseHoldTypeID = NULL;
 382     foreach ($relationshipTypes as $id => $relationshipType) {
 383       if ($relationshipType['name_a_b'] === 'Household Member of') {
 384         $houseHoldTypeID = $relationshipType['id'];
 385       }
 386     }
 387     $this->callAPISuccess('Relationship', 'create', [
 388       'contact_id_a' => $this->contactIDs[0],
 389       'contact_id_b' => $householdID,
 390       'relationship_type_id' => $houseHoldTypeID,
 391     ]);
 392     $this->callAPISuccess('Relationship', 'create', [
 393       'contact_id_a' => $this->contactIDs[1],
 394       'contact_id_b' => $householdID,
 395       'relationship_type_id' => $houseHoldTypeID,
 396     ]);
 397
 398     $selectedFields = [
 399       ['Individual', $houseHoldTypeID . '_a_b', 'state_province', ''],
 400       ['Individual', $houseHoldTypeID . '_a_b', 'city', ''],
 401       ['Individual', 'city', ''],
 402       ['Individual', 'state_province', ''],
 403     ];
 404     list($tableName) = CRM_Export_BAO_Export::exportComponents(
 405       FALSE,
 406       $this->contactIDs,
 407       [],
 408       NULL,
 409       $selectedFields,
 410       NULL,
 411       CRM_Export_Form_Select::CONTACT_EXPORT,
 412       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 413       NULL,
 414       FALSE,
 415       TRUE,
 416       [
 417         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 418         'suppress_csv_for_testing' => TRUE,
 419       ]
 420     );
 421     $dao = CRM_Core_DAO::executeQuery("SELECT * FROM {$tableName}");
 422     while ($dao->fetch()) {
 423       $this->assertEquals('Portland', $dao->city);
 424       $this->assertEquals('ME', $dao->state_province);
 425       $this->assertEquals($householdID, $dao->civicrm_primary_id);
 426       $this->assertEquals($householdID, $dao->civicrm_primary_id);
 427     }
 428
 429   }
 430
 431   /**
 432    * Test master_address_id field.
 433    */
 434   public function testExportMasterAddress() {
 435     $this->setUpContactExportData();
 436
 437     //export the master address for contact B
 438     $selectedFields = array(
 439       array('Individual', 'master_id', 1),
 440     );
 441     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 442       TRUE,
 443       array($this->contactIDs[1]),
 444       array(),
 445       NULL,
 446       $selectedFields,
 447       NULL,
 448       CRM_Export_Form_Select::CONTACT_EXPORT,
 449       "contact_a.id IN ({$this->contactIDs[1]})",
 450       NULL,
 451       FALSE,
 452       FALSE,
 453       array(
 454         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 455         'suppress_csv_for_testing' => TRUE,
 456       )
 457     );
 458     $field = key($sqlColumns);
 459
 460     //assert the exported result
 461     $masterName = CRM_Core_DAO::singleValueQuery("SELECT {$field} FROM {$tableName}");
 462     $displayName = CRM_Contact_BAO_Contact::getMasterDisplayName($this->masterAddressID);
 463     $this->assertEquals($displayName, $masterName);
 464
 465     // delete the export temp table and component table
 466     $sql = "DROP TABLE IF EXISTS {$tableName}";
 467     CRM_Core_DAO::executeQuery($sql);
 468   }
 469
 470   /**
 471    * Test that deceased and do not mail contacts are removed from contacts before
 472    */
 473   public function testExportDeceasedDoNotMail() {
 474     $contactA = $this->callAPISuccess('contact', 'create', array(
 475       'first_name' => 'John',
 476       'last_name' => 'Doe',
 477       'contact_type' => 'Individual',
 478     ));
 479
 480     $contactB = $this->callAPISuccess('contact', 'create', array(
 481       'first_name' => 'Jane',
 482       'last_name' => 'Doe',
 483       'contact_type' => 'Individual',
 484       'is_deceased' => 1,
 485     ));
 486
 487     //create address for contact A
 488     $result = $this->callAPISuccess('address', 'create', array(
 489       'contact_id' => $contactA['id'],
 490       'location_type_id' => 'Home',
 491       'street_address' => 'ABC 12',
 492       'postal_code' => '123 AB',
 493       'country_id' => '1152',
 494       'city' => 'ABC',
 495       'is_primary' => 1,
 496     ));
 497
 498     //create address for contact B
 499     $result = $this->callAPISuccess('address', 'create', array(
 500       'contact_id' => $contactB['id'],
 501       'location_type_id' => 'Home',
 502       'street_address' => 'ABC 12',
 503       'postal_code' => '123 AB',
 504       'country_id' => '1152',
 505       'city' => 'ABC',
 506       'is_primary' => 1,
 507     ));
 508
 509     //export and merge contacts with same address
 510     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 511       TRUE,
 512       array($contactA['id'], $contactB['id']),
 513       array(),
 514       NULL,
 515       NULL,
 516       NULL,
 517       CRM_Export_Form_Select::CONTACT_EXPORT,
 518       "contact_a.id IN ({$contactA['id']}, {$contactB['id']})",
 519       NULL,
 520       TRUE,
 521       FALSE,
 522       array(
 523         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 524         'mergeOption' => TRUE,
 525         'suppress_csv_for_testing' => TRUE,
 526         'postal_mailing_export' => array(
 527           'postal_mailing_export' => TRUE,
 528         ),
 529       )
 530     );
 531
 532     $greeting = CRM_Core_DAO::singleValueQuery("SELECT email_greeting FROM {$tableName}");
 533
 534     //Assert email_greeting is not merged
 535     $this->assertNotContains(',', (string) $greeting);
 536
 537     // delete the export temp table and component table
 538     $sql = "DROP TABLE IF EXISTS {$tableName}";
 539     CRM_Core_DAO::executeQuery($sql);
 540   }
 541
 542 }