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 testExportCustomData() {
 452     $this->setUpContactExportData();
 453
 454     $customData = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTest.php');
 455
 456     $this->callAPISuccess('Contact', 'create', [
 457       'id' => $this->contactIDs[1],
 458       'custom_' . $customData['custom_field_id'] => 'BlahdeBlah',
 459       'api.Address.create' => ['location_type_id' => 'Billing', 'city' => 'Waipu'],
 460     ]);
 461     $selectedFields = [
 462       ['Individual', 'city', CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Address', 'location_type_id', 'Billing')],
 463       ['Individual', 'custom_1'],
 464     ];
 465
 466     list($tableName, $sqlColumns) = $this->doExport($selectedFields, $this->contactIDs[1]);
 467     $this->assertEquals([
 468       'billing_city' => 'billing_city text',
 469       'custom_1' => 'custom_1 varchar(255)',
 470     ], $sqlColumns);
 471
 472     $dao = CRM_Core_DAO::executeQuery('SELECT * FROM ' . $tableName);
 473     while ($dao->fetch()) {
 474       $this->assertEquals('BlahdeBlah', $dao->custom_1);
 475       $this->assertEquals('Waipu', $dao->billing_city);
 476     }
 477   }
 478
 479   /**
 480    * Attempt to do a fairly full export of location data.
 481    */
 482   public function testExportIMData() {
 483     // Use default providers.
 484     $providers = ['AIM', 'GTalk', 'Jabber', 'MSN', 'Skype', 'Yahoo'];
 485     $locationTypes = ['Billing', 'Home', 'Main', 'Other'];
 486
 487     $this->contactIDs[] = $this->individualCreate();
 488     $this->contactIDs[] = $this->individualCreate();
 489     $this->contactIDs[] = $this->householdCreate();
 490     $this->contactIDs[] = $this->organizationCreate();
 491     foreach ($this->contactIDs as $contactID) {
 492       foreach ($providers as $provider) {
 493         foreach ($locationTypes as $locationType) {
 494           $this->callAPISuccess('IM', 'create', [
 495             'contact_id' => $contactID,
 496             'location_type_id' => $locationType,
 497             'provider_id' => $provider,
 498             'name' => $locationType . $provider . $contactID,
 499           ]);
 500         }
 501       }
 502     }
 503
 504     $relationships = [
 505       $this->contactIDs[1] => ['label' => 'Spouse of'],
 506       $this->contactIDs[2] => ['label' => 'Household Member of'],
 507       $this->contactIDs[3] => ['label' => 'Employee of']
 508     ];
 509
 510     foreach ($relationships as $contactID => $relationshipType) {
 511       $relationshipTypeID = $this->callAPISuccess('RelationshipType', 'getvalue', ['label_a_b' => $relationshipType['label'], 'return' => 'id']);
 512       $result = $this->callAPISuccess('Relationship', 'create', [
 513         'contact_id_a' => $this->contactIDs[0],
 514         'relationship_type_id' => $relationshipTypeID,
 515         'contact_id_b' => $contactID
 516       ]);
 517       $relationships[$contactID]['id'] = $result['id'];
 518       $relationships[$contactID]['relationship_type_id'] = $relationshipTypeID;
 519     }
 520
 521     $fields = [['Individual', 'contact_id']];
 522     // ' ' denotes primary location type.
 523     foreach (array_merge($locationTypes, [' ']) as $locationType) {
 524       $fields[] = [
 525         'Individual',
 526         'im_provider',
 527         CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
 528       ];
 529       foreach ($relationships as $contactID => $relationship) {
 530         $fields[] = [
 531           'Individual',
 532           $relationship['relationship_type_id'] . '_a_b',
 533           'im_provider',
 534           CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
 535         ];
 536       }
 537       foreach ($providers as $provider) {
 538         $fields[] = [
 539           'Individual',
 540           'im',
 541           CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
 542           CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'provider_id', $provider),
 543         ];
 544         foreach ($relationships as $contactID => $relationship) {
 545           $fields[] = [
 546             'Individual',
 547             $relationship['relationship_type_id'] . '_a_b',
 548             'im',
 549             CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
 550             CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'provider_id', $provider),
 551           ];
 552         }
 553       }
 554     }
 555     list($tableName, $sqlColumns) = $this->doExport($fields, $this->contactIDs[0]);
 556
 557     $dao = CRM_Core_DAO::executeQuery('SELECT * FROM ' . $tableName);
 558     while ($dao->fetch()) {
 559       $id = $dao->contact_id;
 560       $this->assertEquals('AIM', $dao->billing_im_provider);
 561       $this->assertEquals('BillingJabber' . $id, $dao->billing_im_screen_name_jabber);
 562       $this->assertEquals('BillingSkype' . $id, $dao->billing_im_screen_name_skype);
 563       foreach ($relationships as $relatedContactID => $relationship) {
 564         $relationshipString = $field = $relationship['relationship_type_id'] . '_a_b';
 565         $field = $relationshipString . '_billing_im_screen_name_yahoo';
 566         $this->assertEquals('BillingYahoo' . $relatedContactID, $dao->$field);
 567         // @todo efforts to output 'im_provider' for related contacts seem to be giving a blank field.
 568       }
 569     }
 570
 571     // early return for now until we solve a leakage issue.
 572     return;
 573
 574     $this->assertEquals([
 575       'billing_im_provider' => 'billing_im_provider text',
 576       'billing_im_screen_name' => 'billing_im_screen_name text',
 577       'billing_im_screen_name_jabber' => 'billing_im_screen_name_jabber text',
 578       'billing_im_screen_name_skype' => 'billing_im_screen_name_skype text',
 579       'billing_im_screen_name_yahoo' => 'billing_im_screen_name_yahoo text',
 580       'home_im_provider' => 'home_im_provider text',
 581       'home_im_screen_name' => 'home_im_screen_name text',
 582       'home_im_screen_name_jabber' => 'home_im_screen_name_jabber text',
 583       'home_im_screen_name_skype' => 'home_im_screen_name_skype text',
 584       'home_im_screen_name_yahoo' => 'home_im_screen_name_yahoo text',
 585       'main_im_provider' => 'main_im_provider text',
 586       'main_im_screen_name' => 'main_im_screen_name text',
 587       'main_im_screen_name_jabber' => 'main_im_screen_name_jabber text',
 588       'main_im_screen_name_skype' => 'main_im_screen_name_skype text',
 589       'main_im_screen_name_yahoo' => 'main_im_screen_name_yahoo text',
 590       'other_im_provider' => 'other_im_provider text',
 591       'other_im_screen_name' => 'other_im_screen_name text',
 592       'other_im_screen_name_jabber' => 'other_im_screen_name_jabber text',
 593       'other_im_screen_name_skype' => 'other_im_screen_name_skype text',
 594       'other_im_screen_name_yahoo' => 'other_im_screen_name_yahoo text',
 595       'im_provider' => 'im_provider text',
 596       'im' => 'im varchar(64)',
 597       'contact_id' => 'contact_id varchar(255)',
 598       '2_a_b_im_provider' => '2_a_b_im_provider text',
 599       '2_a_b_billing_im_screen_name' => '2_a_b_billing_im_screen_name text',
 600       '2_a_b_billing_im_screen_name_jabber' => '2_a_b_billing_im_screen_name_jabber text',
 601       '2_a_b_billing_im_screen_name_skype' => '2_a_b_billing_im_screen_name_skype text',
 602       '2_a_b_billing_im_screen_name_yahoo' => '2_a_b_billing_im_screen_name_yahoo text',
 603       '2_a_b_home_im_screen_name' => '2_a_b_home_im_screen_name text',
 604       '2_a_b_home_im_screen_name_jabber' => '2_a_b_home_im_screen_name_jabber text',
 605       '2_a_b_home_im_screen_name_skype' => '2_a_b_home_im_screen_name_skype text',
 606       '2_a_b_home_im_screen_name_yahoo' => '2_a_b_home_im_screen_name_yahoo text',
 607       '2_a_b_main_im_screen_name' => '2_a_b_main_im_screen_name text',
 608       '2_a_b_main_im_screen_name_jabber' => '2_a_b_main_im_screen_name_jabber text',
 609       '2_a_b_main_im_screen_name_skype' => '2_a_b_main_im_screen_name_skype text',
 610       '2_a_b_main_im_screen_name_yahoo' => '2_a_b_main_im_screen_name_yahoo text',
 611       '2_a_b_other_im_screen_name' => '2_a_b_other_im_screen_name text',
 612       '2_a_b_other_im_screen_name_jabber' => '2_a_b_other_im_screen_name_jabber text',
 613       '2_a_b_other_im_screen_name_skype' => '2_a_b_other_im_screen_name_skype text',
 614       '2_a_b_other_im_screen_name_yahoo' => '2_a_b_other_im_screen_name_yahoo text',
 615       '2_a_b_im' => '2_a_b_im text',
 616       '8_a_b_im_provider' => '8_a_b_im_provider text',
 617       '8_a_b_billing_im_screen_name' => '8_a_b_billing_im_screen_name text',
 618       '8_a_b_billing_im_screen_name_jabber' => '8_a_b_billing_im_screen_name_jabber text',
 619       '8_a_b_billing_im_screen_name_skype' => '8_a_b_billing_im_screen_name_skype text',
 620       '8_a_b_billing_im_screen_name_yahoo' => '8_a_b_billing_im_screen_name_yahoo text',
 621       '8_a_b_home_im_screen_name' => '8_a_b_home_im_screen_name text',
 622       '8_a_b_home_im_screen_name_jabber' => '8_a_b_home_im_screen_name_jabber text',
 623       '8_a_b_home_im_screen_name_skype' => '8_a_b_home_im_screen_name_skype text',
 624       '8_a_b_home_im_screen_name_yahoo' => '8_a_b_home_im_screen_name_yahoo text',
 625       '8_a_b_main_im_screen_name' => '8_a_b_main_im_screen_name text',
 626       '8_a_b_main_im_screen_name_jabber' => '8_a_b_main_im_screen_name_jabber text',
 627       '8_a_b_main_im_screen_name_skype' => '8_a_b_main_im_screen_name_skype text',
 628       '8_a_b_main_im_screen_name_yahoo' => '8_a_b_main_im_screen_name_yahoo text',
 629       '8_a_b_other_im_screen_name' => '8_a_b_other_im_screen_name text',
 630       '8_a_b_other_im_screen_name_jabber' => '8_a_b_other_im_screen_name_jabber text',
 631       '8_a_b_other_im_screen_name_skype' => '8_a_b_other_im_screen_name_skype text',
 632       '8_a_b_other_im_screen_name_yahoo' => '8_a_b_other_im_screen_name_yahoo text',
 633       '8_a_b_im' => '8_a_b_im text',
 634       '5_a_b_im_provider' => '5_a_b_im_provider text',
 635       '5_a_b_billing_im_screen_name' => '5_a_b_billing_im_screen_name text',
 636       '5_a_b_billing_im_screen_name_jabber' => '5_a_b_billing_im_screen_name_jabber text',
 637       '5_a_b_billing_im_screen_name_skype' => '5_a_b_billing_im_screen_name_skype text',
 638       '5_a_b_billing_im_screen_name_yahoo' => '5_a_b_billing_im_screen_name_yahoo text',
 639       '5_a_b_home_im_screen_name' => '5_a_b_home_im_screen_name text',
 640       '5_a_b_home_im_screen_name_jabber' => '5_a_b_home_im_screen_name_jabber text',
 641       '5_a_b_home_im_screen_name_skype' => '5_a_b_home_im_screen_name_skype text',
 642       '5_a_b_home_im_screen_name_yahoo' => '5_a_b_home_im_screen_name_yahoo text',
 643       '5_a_b_main_im_screen_name' => '5_a_b_main_im_screen_name text',
 644       '5_a_b_main_im_screen_name_jabber' => '5_a_b_main_im_screen_name_jabber text',
 645       '5_a_b_main_im_screen_name_skype' => '5_a_b_main_im_screen_name_skype text',
 646       '5_a_b_main_im_screen_name_yahoo' => '5_a_b_main_im_screen_name_yahoo text',
 647       '5_a_b_other_im_screen_name' => '5_a_b_other_im_screen_name text',
 648       '5_a_b_other_im_screen_name_jabber' => '5_a_b_other_im_screen_name_jabber text',
 649       '5_a_b_other_im_screen_name_skype' => '5_a_b_other_im_screen_name_skype text',
 650       '5_a_b_other_im_screen_name_yahoo' => '5_a_b_other_im_screen_name_yahoo text',
 651       '5_a_b_im' => '5_a_b_im text',
 652     ], $sqlColumns);
 653
 654   }
 655
 656   /**
 657    * Test master_address_id field.
 658    */
 659   public function testExportMasterAddress() {
 660     $this->setUpContactExportData();
 661
 662     //export the master address for contact B
 663     $selectedFields = array(
 664       array('Individual', 'master_id', 1),
 665     );
 666     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 667       TRUE,
 668       array($this->contactIDs[1]),
 669       array(),
 670       NULL,
 671       $selectedFields,
 672       NULL,
 673       CRM_Export_Form_Select::CONTACT_EXPORT,
 674       "contact_a.id IN ({$this->contactIDs[1]})",
 675       NULL,
 676       FALSE,
 677       FALSE,
 678       array(
 679         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 680         'suppress_csv_for_testing' => TRUE,
 681       )
 682     );
 683     $field = key($sqlColumns);
 684
 685     //assert the exported result
 686     $masterName = CRM_Core_DAO::singleValueQuery("SELECT {$field} FROM {$tableName}");
 687     $displayName = CRM_Contact_BAO_Contact::getMasterDisplayName($this->masterAddressID);
 688     $this->assertEquals($displayName, $masterName);
 689
 690     // delete the export temp table and component table
 691     $sql = "DROP TABLE IF EXISTS {$tableName}";
 692     CRM_Core_DAO::executeQuery($sql);
 693   }
 694
 695   /**
 696    * Test that deceased and do not mail contacts are removed from contacts before
 697    */
 698   public function testExportDeceasedDoNotMail() {
 699     $contactA = $this->callAPISuccess('contact', 'create', array(
 700       'first_name' => 'John',
 701       'last_name' => 'Doe',
 702       'contact_type' => 'Individual',
 703     ));
 704
 705     $contactB = $this->callAPISuccess('contact', 'create', array(
 706       'first_name' => 'Jane',
 707       'last_name' => 'Doe',
 708       'contact_type' => 'Individual',
 709       'is_deceased' => 1,
 710     ));
 711
 712     //create address for contact A
 713     $this->callAPISuccess('address', 'create', array(
 714       'contact_id' => $contactA['id'],
 715       'location_type_id' => 'Home',
 716       'street_address' => 'ABC 12',
 717       'postal_code' => '123 AB',
 718       'country_id' => '1152',
 719       'city' => 'ABC',
 720       'is_primary' => 1,
 721     ));
 722
 723     //create address for contact B
 724     $this->callAPISuccess('address', 'create', array(
 725       'contact_id' => $contactB['id'],
 726       'location_type_id' => 'Home',
 727       'street_address' => 'ABC 12',
 728       'postal_code' => '123 AB',
 729       'country_id' => '1152',
 730       'city' => 'ABC',
 731       'is_primary' => 1,
 732     ));
 733
 734     //export and merge contacts with same address
 735     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 736       TRUE,
 737       array($contactA['id'], $contactB['id']),
 738       array(),
 739       NULL,
 740       NULL,
 741       NULL,
 742       CRM_Export_Form_Select::CONTACT_EXPORT,
 743       "contact_a.id IN ({$contactA['id']}, {$contactB['id']})",
 744       NULL,
 745       TRUE,
 746       FALSE,
 747       array(
 748         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 749         'mergeOption' => TRUE,
 750         'suppress_csv_for_testing' => TRUE,
 751         'postal_mailing_export' => array(
 752           'postal_mailing_export' => TRUE,
 753         ),
 754       )
 755     );
 756
 757     $greeting = CRM_Core_DAO::singleValueQuery("SELECT email_greeting FROM {$tableName}");
 758
 759     //Assert email_greeting is not merged
 760     $this->assertNotContains(',', (string) $greeting);
 761
 762     // delete the export temp table and component table
 763     $sql = "DROP TABLE IF EXISTS {$tableName}";
 764     CRM_Core_DAO::executeQuery($sql);
 765   }
 766
 767   /**
 768    * @return array
 769    */
 770   protected function setUpHousehold() {
 771     $this->setUpContactExportData();
 772     $householdID = $this->householdCreate([
 773       'api.Address.create' => [
 774         'city' => 'Portland',
 775         'state_province_id' => 'Maine',
 776         'location_type_id' => 'Home'
 777       ]
 778     ]);
 779
 780     $relationshipTypes = $this->callAPISuccess('RelationshipType', 'get', [])['values'];
 781     $houseHoldTypeID = NULL;
 782     foreach ($relationshipTypes as $id => $relationshipType) {
 783       if ($relationshipType['name_a_b'] === 'Household Member of') {
 784         $houseHoldTypeID = $relationshipType['id'];
 785       }
 786     }
 787     $this->callAPISuccess('Relationship', 'create', [
 788       'contact_id_a' => $this->contactIDs[0],
 789       'contact_id_b' => $householdID,
 790       'relationship_type_id' => $houseHoldTypeID,
 791     ]);
 792     $this->callAPISuccess('Relationship', 'create', [
 793       'contact_id_a' => $this->contactIDs[1],
 794       'contact_id_b' => $householdID,
 795       'relationship_type_id' => $houseHoldTypeID,
 796     ]);
 797     return array($householdID, $houseHoldTypeID);
 798   }
 799
 800   /**
 801    * @param $selectedFields
 802    * @return array
 803    */
 804   protected function doExport($selectedFields, $id) {
 805     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 806       TRUE,
 807       array($id),
 808       array(),
 809       NULL,
 810       $selectedFields,
 811       NULL,
 812       CRM_Export_Form_Select::CONTACT_EXPORT,
 813       "contact_a.id IN ({$id})",
 814       NULL,
 815       FALSE,
 816       FALSE,
 817       array(
 818         'exportOption' => CRM_Export_Form_Select::CONTACT_EXPORT,
 819         'suppress_csv_for_testing' => TRUE,
 820       )
 821     );
 822     return array($tableName, $sqlColumns);
 823   }
 824
 825 }