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

   1 <?php
   2
   3 /**
   4  * Class CRM_Core_DAOTest
   5  *
   6  * @group headless
   7  */
   8 class CRM_Export_BAO_ExportTest extends CiviUnitTestCase {
   9
  10   /**
  11    * Contact IDs created for testing.
  12    *
  13    * @var array
  14    */
  15   protected $contactIDs = [];
  16
  17   /**
  18    * Contribution IDs created for testing.
  19    *
  20    * @var array
  21    */
  22   protected $contributionIDs = [];
  23
  24   /**
  25    * Contribution IDs created for testing.
  26    *
  27    * @var array
  28    */
  29   protected $activityIDs = [];
  30
  31   /**
  32    * Contribution IDs created for testing.
  33    *
  34    * @var array
  35    */
  36   protected $membershipIDs = [];
  37
  38   /**
  39    * Master Address ID created for testing.
  40    *
  41    * @var int
  42    */
  43   protected $masterAddressID;
  44
  45   protected $locationTypes = [];
  46
  47   /**
  48    * Processor generated in test.
  49    *
  50    * @var \CRM_Export_BAO_ExportProcessor
  51    */
  52   protected $processor;
  53
  54   /**
  55    * Csv output from export.
  56    *
  57    * @var \League\Csv\Reader
  58    */
  59   protected $csv;
  60
  61   /**
  62    * Cleanup data.
  63    *
  64    * @throws \Exception
  65    */
  66   public function tearDown() {
  67     $this->quickCleanUpFinancialEntities();
  68     $this->quickCleanup([
  69       'civicrm_contact',
  70       'civicrm_email',
  71       'civicrm_phone',
  72       'civicrm_im',
  73       'civicrm_website',
  74       'civicrm_address',
  75       'civicrm_relationship',
  76       'civicrm_membership',
  77       'civicrm_case',
  78       'civicrm_case_contact',
  79       'civicrm_case_activity',
  80     ]);
  81
  82     if (!empty($this->locationTypes)) {
  83       $this->callAPISuccess('LocationType', 'delete', ['id' => $this->locationTypes['Whare Kai']['id']]);
  84       $this->callAPISuccess('LocationType', 'create', ['id' => $this->locationTypes['Main']['id'], 'name' => 'Main']);
  85     }
  86     if ($this->processor && $this->processor->getTemporaryTable()) {
  87       // delete the export temp table
  88       CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS " . $this->processor->getTemporaryTable());
  89     }
  90     parent::tearDown();
  91   }
  92
  93   /**
  94    * Basic test to ensure the exportComponents function completes without error.
  95    *
  96    * @throws \CRM_Core_Exception
  97    * @throws \League\Csv\Exception
  98    */
  99   public function testExportComponentsNull() {
 100     $this->doExportTest([]);
 101   }
 102
 103   /**
 104    * Basic test to ensure the exportComponents function can export selected fields for contribution.
 105    *
 106    * @throws \CRM_Core_Exception
 107    * @throws \League\Csv\Exception
 108    */
 109   public function testExportComponentsContribution() {
 110     $this->setUpContributionExportData();
 111     $selectedFields = [
 112       ['Individual', 'first_name'],
 113       ['Individual', 'last_name'],
 114       ['Contribution', 'receive_date'],
 115       ['Contribution', 'contribution_source'],
 116       ['Individual', 'street_address', 1],
 117       ['Individual', 'city', 1],
 118       ['Individual', 'country', 1],
 119       ['Individual', 'email', 1],
 120       ['Contribution', 'trxn_id'],
 121     ];
 122
 123     $this->doExportTest([
 124       'ids' => $this->contributionIDs,
 125       'order' => 'receive_date desc',
 126       'fields' => $selectedFields,
 127       'exportMode' => CRM_Export_Form_Select::CONTRIBUTE_EXPORT,
 128       'componentClause' => 'civicrm_contribution.id IN ( ' . implode(',', $this->contributionIDs) . ')',
 129     ]);
 130   }
 131
 132   /**
 133    * Basic test to ensure the exportComponents function can export with soft credits enabled.
 134    *
 135    * @throws \CRM_Core_Exception
 136    * @throws \League\Csv\Exception
 137    */
 138   public function testExportComponentsContributionSoftCredits() {
 139     $this->setUpContributionExportData();
 140     $this->callAPISuccess('ContributionSoft', 'create', ['contact_id' => $this->contactIDs[1], 'contribution_id' => $this->contributionIDs[0], 'amount' => 5]);
 141     $params = [
 142       ['receive_date_low', '=', '20160101000000', 0, 0],
 143       ['receive_date_high', '=', '20191231235959', 0, 0],
 144       ['contribution_amount_low', '=', '1', 0, 0],
 145       ['contribution_amount_high', '=', '10000000', 0, 0],
 146       ['contribution_test', '=', '0', 0, 0],
 147       ['contribution_or_softcredits', '=', 'both', 0, 0],
 148     ];
 149
 150     $this->doExportTest([
 151       'selectAll' => FALSE,
 152       'ids' => $this->contributionIDs,
 153       'params' => $params,
 154       'order' => 'receive_date desc',
 155       'exportMode' => CRM_Export_Form_Select::CONTRIBUTE_EXPORT,
 156       'componentClause' => 'civicrm_contribution.id IN ( ' . implode(',', $this->contributionIDs) . ')',
 157     ]);
 158
 159     $this->assertEquals(array_merge($this->getBasicHeaderDefinition(FALSE), $this->getContributeHeaderDefinition()), $this->csv->getHeader());
 160     $this->assertCount(3, $this->csv);
 161     $row = $this->csv->fetchOne(0);
 162     $this->assertEquals(95, $row['Net Amount']);
 163     $this->assertEquals('', $row['Soft Credit Amount']);
 164     $row = $this->csv->fetchOne(1);
 165     $this->assertEquals(95, $row['Net Amount']);
 166     $this->assertEquals(5, $row['Soft Credit Amount']);
 167     $this->assertEquals('Anderson, Anthony', $row['Soft Credit For']);
 168     $this->assertEquals($this->contributionIDs[0], $row['Soft Credit For Contribution ID']);
 169
 170     // Ideally we would use a randomised temp table name & use generic temp cleanup for cleanup - but
 171     // for now just make sure we don't leave a mess.
 172     CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS contribution_search_scredit_combined');
 173
 174   }
 175
 176   /**
 177    * Basic test to ensure the exportComponents function can export selected fields for contribution.
 178    */
 179   public function testExportComponentsMembership() {
 180     $this->setUpMembershipExportData();
 181     list($tableName) = CRM_Export_BAO_Export::exportComponents(
 182       TRUE,
 183       $this->membershipIDs,
 184       [],
 185       NULL,
 186       NULL,
 187       NULL,
 188       CRM_Export_Form_Select::MEMBER_EXPORT,
 189       'civicrm_membership.id IN ( ' . implode(',', $this->membershipIDs) . ')',
 190       NULL,
 191       FALSE,
 192       FALSE,
 193       [
 194         'exportOption' => CRM_Export_Form_Select::MEMBER_EXPORT,
 195         'suppress_csv_for_testing' => TRUE,
 196       ]
 197     );
 198
 199     $dao = CRM_Core_DAO::executeQuery('SELECT * from ' . $tableName);
 200     $dao->fetch();
 201     $this->assertEquals('100.00', $dao->componentpaymentfield_total_amount);
 202     $this->assertEquals('Completed', $dao->componentpaymentfield_contribution_status);
 203     $this->assertEquals('Credit Card', $dao->componentpaymentfield_payment_instrument);
 204     $this->assertEquals(1, $dao->N);
 205
 206     // delete the export temp table and component table
 207     $sql = "DROP TABLE IF EXISTS {$tableName}";
 208     CRM_Core_DAO::executeQuery($sql);
 209   }
 210
 211   /**
 212    * Basic test to ensure the exportComponents function can export selected fields for contribution.
 213    */
 214   public function testExportComponentsActivity() {
 215     $this->setUpActivityExportData();
 216     $selectedFields = [
 217       ['contact_type' => 'Individual', 'name' => 'display_name'],
 218       ['contact_type' => 'Individual', 'relationship_type_id' => '5', 'relationship_direction' => 'a_b', 'name' => 'display_name'],
 219     ];
 220
 221     list($tableName) = CRM_Export_BAO_Export::exportComponents(
 222       FALSE,
 223       $this->activityIDs,
 224       [],
 225       '`activity_date_time` desc',
 226       $selectedFields,
 227       NULL,
 228       CRM_Export_Form_Select::ACTIVITY_EXPORT,
 229       'civicrm_activity.id IN ( ' . implode(',', $this->activityIDs) . ')',
 230       NULL,
 231       FALSE,
 232       FALSE,
 233       [
 234         'exportOption' => CRM_Export_Form_Select::ACTIVITY_EXPORT,
 235         'suppress_csv_for_testing' => TRUE,
 236       ]
 237     );
 238
 239     // delete the export temp table and component table
 240     $sql = "DROP TABLE IF EXISTS {$tableName}";
 241     CRM_Core_DAO::executeQuery($sql);
 242   }
 243
 244   /**
 245    * Test the function that extracts the arrays used to structure the output.
 246    *
 247    * The keys in the output fields array should by matched by field aliases in the sql query (with
 248    * exceptions of course - currently country is one - although maybe a future refactor can change that!).
 249    *
 250    * We are trying to move towards simpler processing in the per row iteration as that may be
 251    * repeated 100,000 times and in general we should simply be able to match the query fields to
 252    * our expected rows & do a little pseudoconstant mapping.
 253    */
 254   public function testGetExportStructureArrays() {
 255     // This is how return properties are formatted internally within the function for passing to the BAO query.
 256     $returnProperties = [
 257       'first_name' => 1,
 258       'last_name' => 1,
 259       'receive_date' => 1,
 260       'contribution_source' => 1,
 261       'location' => [
 262         'Home' => [
 263           'street_address' => 1,
 264           'city' => 1,
 265           'country' => 1,
 266           'email' => 1,
 267           'im-1' => 1,
 268           'im_provider' => 1,
 269           'phone-1' => 1,
 270         ],
 271       ],
 272       'phone' => 1,
 273       'trxn_id' => 1,
 274       'contribution_id' => 1,
 275     ];
 276
 277     $query = new CRM_Contact_BAO_Query([], $returnProperties, NULL,
 278       FALSE, FALSE, CRM_Contact_BAO_Query::MODE_CONTRIBUTE,
 279       FALSE, TRUE, TRUE, NULL, 'AND'
 280     );
 281
 282     list($select) = $query->query();
 283     $pattern = '/as `?([^`,]*)/';
 284     $queryFieldAliases = [];
 285     preg_match_all($pattern, $select, $queryFieldAliases, PREG_PATTERN_ORDER);
 286     $processor = new CRM_Export_BAO_ExportProcessor(CRM_Contact_BAO_Query::MODE_CONTRIBUTE, NULL, 'AND');
 287     $processor->setQueryFields($query->_fields);
 288     $processor->setReturnProperties($returnProperties);
 289
 290     list($outputFields) = CRM_Export_BAO_Export::getExportStructureArrays($processor);
 291     foreach (array_keys($outputFields) as $fieldAlias) {
 292       if ($fieldAlias === 'Home-country') {
 293         $this->assertTrue(in_array($fieldAlias . '_id', $queryFieldAliases[1]), 'Country is subject to some funky translate so we make sure country id is present');
 294       }
 295       else {
 296         $this->assertTrue(in_array($fieldAlias, $queryFieldAliases[1]), 'looking for field ' . $fieldAlias . ' in generaly the alias fields need to match the outputfields');
 297       }
 298     }
 299
 300   }
 301
 302   /**
 303    * Set up some data for us to do testing on.
 304    *
 305    * @throws \CRM_Core_Exception
 306    */
 307   public function setUpContributionExportData() {
 308     $this->setUpContactExportData();
 309     $this->contributionIDs[] = $this->contributionCreate(['contact_id' => $this->contactIDs[0], 'trxn_id' => 'null', 'invoice_id' => 'null', 'receive_date' => '2019-07-25 07:34:23']);
 310     $this->contributionIDs[] = $this->contributionCreate(['contact_id' => $this->contactIDs[1], 'trxn_id' => 'null', 'invoice_id' => 'null', 'receive_date' => '2018-12-01 00:00:00']);
 311   }
 312
 313   /**
 314    * Set up some data for us to do testing on.
 315    *
 316    * @throws \CRM_Core_Exception
 317    */
 318   public function setUpMembershipExportData() {
 319     $this->setUpContactExportData();
 320     // Create an extra so we don't get false passes due to 1
 321     $this->contactMembershipCreate(['contact_id' => $this->contactIDs[0]]);
 322     $this->membershipIDs[] = $this->contactMembershipCreate(['contact_id' => $this->contactIDs[0]]);
 323     $this->setUpContributionExportData();
 324     $this->callAPISuccess('membership_payment', 'create', [
 325       'contribution_id' => $this->contributionIDs[0],
 326       'membership_id' => $this->membershipIDs[0],
 327     ]);
 328     $this->callAPISuccess('LineItem', 'get', [
 329       'entity_table' => 'civicrm_membership',
 330       'membership_id' => $this->membershipIDs[0],
 331       'api.LineItem.create' => ['contribution_id' => $this->contributionIDs[0]],
 332     ]);
 333   }
 334
 335   /**
 336    * Set up data to test case export.
 337    */
 338   public function setupCaseExportData() {
 339     $contactID1 = $this->individualCreate();
 340     $contactID2 = $this->individualCreate([], 1);
 341
 342     $case = $this->callAPISuccess('case', 'create', [
 343       'case_type_id' => 1,
 344       'subject' => 'blah',
 345       'contact_id' => $contactID1,
 346     ]);
 347     $this->callAPISuccess('CaseContact', 'create', [
 348       'case_id' => $case['id'],
 349       'contact_id' => $contactID2,
 350     ]);
 351   }
 352
 353   /**
 354    * Set up some data for us to do testing on.
 355    */
 356   public function setUpActivityExportData() {
 357     $this->setUpContactExportData();
 358     $this->activityIDs[] = $this->activityCreate(['contact_id' => $this->contactIDs[0]])['id'];
 359   }
 360
 361   /**
 362    * Set up some data for us to do testing on.
 363    *
 364    * @throws \CRM_Core_Exception
 365    */
 366   public function setUpContactExportData() {
 367     $this->contactIDs[] = $contactA = $this->individualCreate(['gender_id' => 'Female']);
 368     // Create address for contact A.
 369     $params = [
 370       'contact_id' => $contactA,
 371       'location_type_id' => 'Home',
 372       'street_address' => 'Ambachtstraat 23',
 373       'postal_code' => '6971 BN',
 374       'country_id' => '1152',
 375       'city' => 'Brummen',
 376       'is_primary' => 1,
 377     ];
 378     $result = $this->callAPISuccess('address', 'create', $params);
 379     $addressId = $result['id'];
 380
 381     $this->callAPISuccess('email', 'create', [
 382       'id' => $this->callAPISuccessGetValue('Email', ['contact_id' => $params['contact_id'], 'return' => 'id']),
 383       'location_type_id' => 'Home',
 384       'email' => 'home@example.com',
 385       'is_primary' => 1,
 386     ]);
 387     $this->callAPISuccess('email', 'create', ['contact_id' => $params['contact_id'], 'location_type_id' => 'Work', 'email' => 'work@example.com', 'is_primary' => 0]);
 388
 389     $params['is_primary'] = 0;
 390     $params['location_type_id'] = 'Work';
 391     $this->callAPISuccess('address', 'create', $params);
 392     $this->contactIDs[] = $contactB = $this->individualCreate();
 393
 394     $this->callAPISuccess('address', 'create', [
 395       'contact_id' => $contactB,
 396       'location_type_id' => "Home",
 397       'master_id' => $addressId,
 398     ]);
 399     $this->masterAddressID = $addressId;
 400
 401   }
 402
 403   /**
 404    * Test variants of primary address exporting.
 405    *
 406    * @param int $isPrimaryOnly
 407    *
 408    * @dataProvider getBooleanDataProvider
 409    * @throws \CRM_Core_Exception
 410    * @throws \League\Csv\Exception
 411    */
 412   public function testExportPrimaryAddress($isPrimaryOnly) {
 413     \Civi::settings()->set('searchPrimaryDetailsOnly', $isPrimaryOnly);
 414     $this->setUpContactExportData();
 415
 416     $selectedFields = [['Individual', 'email', ' '], ['Individual', 'email', '1'], ['Individual', 'email', '2']];
 417     $this->doExportTest([
 418       'ids' => [],
 419       'params' => [['email', 'LIKE', 'c', 0, 1]],
 420       'fields' => $selectedFields,
 421       'componentClause' => "contact_a.id IN ({$this->contactIDs[0]}, {$this->contactIDs[1]})",
 422       'selectAll' => TRUE,
 423     ]);
 424
 425     $row = $this->csv->fetchOne();
 426     $this->assertEquals([
 427       'Email' => 'home@example.com',
 428       'Home-Email' => 'home@example.com',
 429       'Work-Email' => 'work@example.com',
 430     ], $row);
 431     $this->assertEquals(2, count($this->csv));
 432     \Civi::settings()->set('searchPrimaryDetailsOnly', FALSE);
 433   }
 434
 435   /**
 436    * Test that when exporting a pseudoField it is reset for NULL entries.
 437    *
 438    * ie. we have a contact WITH a gender & one without - make sure the latter one
 439    * does NOT retain the gender of the former.
 440    *
 441    * @throws \CRM_Core_Exception
 442    */
 443   public function testExportPseudoField() {
 444     $this->setUpContactExportData();
 445     $selectedFields = [['Individual', 'gender_id']];
 446     list($tableName, $sqlColumns) = $this->doExport($selectedFields, $this->contactIDs);
 447     $this->assertEquals('Female,', CRM_Core_DAO::singleValueQuery("SELECT GROUP_CONCAT(gender_id) FROM {$tableName}"));
 448   }
 449
 450   /**
 451    * Test that when exporting a pseudoField it is reset for NULL entries.
 452    *
 453    * This is specific to the example in CRM-14398
 454    */
 455   public function testExportPseudoFieldCampaign() {
 456     $this->setUpContributionExportData();
 457     $campaign = $this->callAPISuccess('Campaign', 'create', ['title' => 'Big campaign']);
 458     $this->callAPISuccess('Contribution', 'create', ['campaign_id' => 'Big_campaign', 'id' => $this->contributionIDs[0]]);
 459     $selectedFields = [
 460       ['contact_type' => 'Individual', 'name' => 'gender_id'],
 461       ['contact_type' => 'Contribution', 'name' => 'contribution_campaign_title'],
 462     ];
 463     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 464       TRUE,
 465       $this->contactIDs[1],
 466       [],
 467       NULL,
 468       $selectedFields,
 469       NULL,
 470       CRM_Export_Form_Select::CONTRIBUTE_EXPORT,
 471       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 472       NULL,
 473       FALSE,
 474       FALSE,
 475       [
 476         'suppress_csv_for_testing' => TRUE,
 477       ]
 478     );
 479     $this->assertEquals('Big campaign,', CRM_Core_DAO::singleValueQuery("SELECT GROUP_CONCAT(contribution_campaign_title) FROM {$tableName}"));
 480   }
 481
 482   /**
 483    * Test exporting relationships.
 484    *
 485    * @throws \CRM_Core_Exception
 486    * @throws \League\Csv\Exception
 487    */
 488   public function testExportRelationships() {
 489     $organization1 = $this->organizationCreate(['organization_name' => 'Org 1', 'legal_name' => 'pretty legal', 'contact_source' => 'friend who took a law paper once']);
 490     $organization2 = $this->organizationCreate(['organization_name' => 'Org 2', 'legal_name' => 'well dodgey']);
 491     $contact1 = $this->individualCreate(['employer_id' => $organization1, 'first_name' => 'one']);
 492     $contact2 = $this->individualCreate(['employer_id' => $organization2, 'first_name' => 'one']);
 493     $employerRelationshipTypeID = $this->callAPISuccessGetValue('RelationshipType', ['return' => 'id', 'label_a_b' => 'Employee of']);
 494     $selectedFields = [
 495       ['Individual', 'first_name', ''],
 496       ['Individual', $employerRelationshipTypeID . '_a_b', 'organization_name', ''],
 497       ['Individual', $employerRelationshipTypeID . '_a_b', 'legal_name', ''],
 498       ['Individual', $employerRelationshipTypeID . '_a_b', 'contact_source', ''],
 499     ];
 500     $this->doExportTest([
 501       'ids' => [$contact1, $contact2],
 502       'componentClause' => "contact_a.id IN ( $contact1, $contact2 )",
 503       'fields' => $selectedFields,
 504     ]);
 505
 506     $row = $this->csv->fetchOne();
 507     $this->assertEquals('one', $row['First Name']);
 508     $this->assertEquals('Org 1', $row['Employee of-Organization Name']);
 509     $this->assertEquals('pretty legal', $row['Employee of-Legal Name']);
 510     $this->assertEquals('friend who took a law paper once', $row['Employee of-Contact Source']);
 511
 512     $row = $this->csv->fetchOne(1);
 513     $this->assertEquals('Org 2', $row['Employee of-Organization Name']);
 514     $this->assertEquals('well dodgey', $row['Employee of-Legal Name']);
 515   }
 516
 517   /**
 518    * Test exporting relationships.
 519    *
 520    * This is to ensure that CRM-13995 remains fixed.
 521    *
 522    * @dataProvider getBooleanDataProvider
 523    *
 524    * @param bool $includeHouseHold
 525    *
 526    * @throws \CRM_Core_Exception
 527    */
 528   public function testExportRelationshipsMergeToHousehold($includeHouseHold) {
 529     list($householdID, $houseHoldTypeID) = $this->setUpHousehold();
 530
 531     if ($includeHouseHold) {
 532       $this->contactIDs[] = $householdID;
 533     }
 534     $selectedFields = [
 535       ['contact_type' => 'Individual', 'relationship_type_id' => $houseHoldTypeID, 'relationship_direction' => 'a_b', 'name' => 'state_province', 'location_type_id' => ''],
 536       ['contact_type' => 'Individual', 'relationship_type_id' => $houseHoldTypeID, 'relationship_direction' => 'a_b', 'name' => 'city', 'location_type_id' => ''],
 537       ['contact_type' => 'Individual', 'name' => 'city', 'location_type_id' => ''],
 538       ['contact_type' => 'Individual', 'name' => 'state_province', 'location_type_id' => ''],
 539       ['contact_type' => 'Individual', 'name' => 'contact_source', 'location_type_id' => ''],
 540     ];
 541     list($tableName, $sqlColumns, $headerRows) = CRM_Export_BAO_Export::exportComponents(
 542       FALSE,
 543       $this->contactIDs,
 544       [],
 545       NULL,
 546       $selectedFields,
 547       NULL,
 548       CRM_Export_Form_Select::CONTACT_EXPORT,
 549       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 550       NULL,
 551       FALSE,
 552       TRUE,
 553       [
 554         'suppress_csv_for_testing' => TRUE,
 555       ]
 556     );
 557     $dao = CRM_Core_DAO::executeQuery("SELECT * FROM {$tableName}");
 558     while ($dao->fetch()) {
 559       $this->assertEquals(1, $dao->N);
 560       $this->assertEquals('Portland', $dao->city);
 561       $this->assertEquals('ME', $dao->state_province);
 562       $this->assertEquals($householdID, $dao->civicrm_primary_id);
 563       $this->assertEquals($householdID, $dao->civicrm_primary_id);
 564       $this->assertEquals('household sauce', $dao->contact_source);
 565     }
 566
 567     $this->assertEquals([
 568       0 => 'City',
 569       1 => 'State',
 570       2 => 'Contact Source',
 571       3 => 'Household ID',
 572     ], $headerRows);
 573     $this->assertEquals(
 574       [
 575         'city' => 'city varchar(64)',
 576         'state_province' => 'state_province varchar(64)',
 577         'civicrm_primary_id' => 'civicrm_primary_id varchar(16)',
 578         'contact_source' => 'contact_source varchar(255)',
 579       ], $sqlColumns);
 580   }
 581
 582   /**
 583    * Test exporting relationships.
 584    */
 585   public function testExportRelationshipsMergeToHouseholdAllFields() {
 586     list($householdID) = $this->setUpHousehold();
 587     list($tableName) = CRM_Export_BAO_Export::exportComponents(
 588       FALSE,
 589       $this->contactIDs,
 590       [],
 591       NULL,
 592       NULL,
 593       NULL,
 594       CRM_Export_Form_Select::CONTACT_EXPORT,
 595       "contact_a.id IN (" . implode(",", $this->contactIDs) . ")",
 596       NULL,
 597       FALSE,
 598       TRUE,
 599       [
 600         'suppress_csv_for_testing' => TRUE,
 601       ]
 602     );
 603     $dao = CRM_Core_DAO::executeQuery("SELECT * FROM {$tableName}");
 604     while ($dao->fetch()) {
 605       $this->assertEquals('Unit Test household', $dao->display_name);
 606       $this->assertEquals('Portland', $dao->city);
 607       $this->assertEquals('ME', $dao->state_province);
 608       $this->assertEquals($householdID, $dao->civicrm_primary_id);
 609       $this->assertEquals($householdID, $dao->civicrm_primary_id);
 610       $this->assertEquals('Unit Test household', $dao->addressee);
 611       $this->assertEquals(1, $dao->N);
 612     }
 613   }
 614
 615   /**
 616    * Test master_address_id field.
 617    */
 618   public function testExportCustomData() {
 619     $this->setUpContactExportData();
 620
 621     $customData = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTest.php');
 622
 623     $this->callAPISuccess('Contact', 'create', [
 624       'id' => $this->contactIDs[1],
 625       'custom_' . $customData['custom_field_id'] => 'BlahdeBlah',
 626       'api.Address.create' => ['location_type_id' => 'Billing', 'city' => 'Waipu'],
 627     ]);
 628     $selectedFields = [
 629       ['Individual', 'city', CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Address', 'location_type_id', 'Billing')],
 630       ['Individual', 'custom_1'],
 631     ];
 632
 633     list($tableName, $sqlColumns) = $this->doExport($selectedFields, $this->contactIDs[1]);
 634     $this->assertEquals([
 635       'billing_city' => 'billing_city varchar(64)',
 636       'custom_1' => 'custom_1 varchar(255)',
 637     ], $sqlColumns);
 638
 639     $dao = CRM_Core_DAO::executeQuery('SELECT * FROM ' . $tableName);
 640     while ($dao->fetch()) {
 641       $this->assertEquals('BlahdeBlah', $dao->custom_1);
 642       $this->assertEquals('Waipu', $dao->billing_city);
 643     }
 644   }
 645
 646   /**
 647    * Attempt to do a fairly full export of location data.
 648    *
 649    * @throws \CRM_Core_Exception
 650    * @throws \League\Csv\Exception
 651    */
 652   public function testExportIMData() {
 653     // Use default providers.
 654     $providers = ['AIM', 'GTalk', 'Jabber', 'MSN', 'Skype', 'Yahoo'];
 655     // Main sure labels are not all anglo chars.
 656     $this->diversifyLocationTypes();
 657
 658     $locationTypes = ['Billing' => 'Billing', 'Home' => 'Home', 'Main' => 'Méin', 'Other' => 'Other', 'Whare Kai' => 'Whare Kai'];
 659
 660     $this->contactIDs[] = $this->individualCreate();
 661     $this->contactIDs[] = $this->individualCreate();
 662     $this->contactIDs[] = $this->householdCreate();
 663     $this->contactIDs[] = $this->organizationCreate();
 664     foreach ($this->contactIDs as $contactID) {
 665       foreach ($providers as $provider) {
 666         foreach ($locationTypes as $locationName => $locationLabel) {
 667           $this->callAPISuccess('IM', 'create', [
 668             'contact_id' => $contactID,
 669             'location_type_id' => $locationName,
 670             'provider_id' => $provider,
 671             'name' => $locationName . $provider . $contactID,
 672           ]);
 673         }
 674       }
 675     }
 676
 677     $relationships = [
 678       $this->contactIDs[1] => ['label' => 'Spouse of'],
 679       $this->contactIDs[2] => ['label' => 'Household Member of'],
 680       $this->contactIDs[3] => ['label' => 'Employee of'],
 681     ];
 682
 683     foreach ($relationships as $contactID => $relationshipType) {
 684       $relationshipTypeID = $this->callAPISuccess('RelationshipType', 'getvalue', ['label_a_b' => $relationshipType['label'], 'return' => 'id']);
 685       $result = $this->callAPISuccess('Relationship', 'create', [
 686         'contact_id_a' => $this->contactIDs[0],
 687         'relationship_type_id' => $relationshipTypeID,
 688         'contact_id_b' => $contactID,
 689       ]);
 690       $relationships[$contactID]['id'] = $result['id'];
 691       $relationships[$contactID]['relationship_type_id'] = $relationshipTypeID;
 692     }
 693
 694     $fields = [['Individual', 'contact_id']];
 695     // ' ' denotes primary location type.
 696     foreach (array_keys(array_merge($locationTypes, [' ' => ['Primary']])) as $locationType) {
 697       $fields[] = [
 698         'Individual',
 699         'im_provider',
 700         CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
 701       ];
 702       foreach ($relationships as $contactID => $relationship) {
 703         $fields[] = [
 704           'Individual',
 705           $relationship['relationship_type_id'] . '_a_b',
 706           'im_provider',
 707           CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
 708         ];
 709       }
 710       foreach ($providers as $provider) {
 711         $fields[] = [
 712           'Individual',
 713           'im',
 714           CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
 715           CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'provider_id', $provider),
 716         ];
 717         foreach ($relationships as $contactID => $relationship) {
 718           $fields[] = [
 719             'Individual',
 720             $relationship['relationship_type_id'] . '_a_b',
 721             'im',
 722             CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
 723             CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'provider_id', $provider),
 724           ];
 725         }
 726       }
 727     }
 728     $this->doExportTest(['fields' => $fields, 'ids' => [$this->contactIDs[0]]]);
 729
 730     foreach ($this->csv->getRecords() as $row) {
 731       $id = $row['contact_id'];
 732       $this->assertEquals('AIM', $row['Billing-IM Provider']);
 733       $this->assertEquals('AIM', $row['Whare Kai-IM Provider']);
 734       $this->assertEquals('BillingJabber' . $id, $row['Billing-IM Screen Name-Jabber']);
 735       $this->assertEquals('Whare KaiJabber' . $id, $row['Whare Kai-IM Screen Name-Jabber']);
 736       $this->assertEquals('BillingSkype' . $id, $row['Billing-IM Screen Name-Skype']);
 737       foreach ($relationships as $relatedContactID => $relationship) {
 738         $this->assertEquals('BillingYahoo' . $relatedContactID, $row[$relationship['label'] . '-Billing-IM Screen Name-Yahoo']);
 739         $this->assertEquals('Whare KaiJabber' . $relatedContactID, $row[$relationship['label'] . '-Whare Kai-IM Screen Name-Jabber']);
 740       }
 741     }
 742   }
 743
 744   /**
 745    * Test phone data export.
 746    *
 747    * Less over the top complete than the im test.
 748    *
 749    * @throws \CRM_Core_Exception
 750    * @throws \League\Csv\Exception
 751    */
 752   public function testExportPhoneData() {
 753     $this->contactIDs[] = $this->individualCreate();
 754     $this->contactIDs[] = $this->individualCreate();
 755     $locationTypes = ['Billing' => 'Billing', 'Home' => 'Home'];
 756     $phoneTypes = ['Mobile', 'Phone'];
 757     foreach ($this->contactIDs as $contactID) {
 758       $this->callAPISuccess('Phone', 'create', [
 759         'contact_id' => $contactID,
 760         'location_type_id' => 'Billing',
 761         'phone_type_id' => 'Mobile',
 762         'phone' => 'Billing' . 'Mobile' . $contactID,
 763         'is_primary' => 1,
 764       ]);
 765       $this->callAPISuccess('Phone', 'create', [
 766         'contact_id' => $contactID,
 767         'location_type_id' => 'Home',
 768         'phone_type_id' => 'Phone',
 769         'phone' => 'Home' . 'Phone' . $contactID,
 770       ]);
 771     }
 772
 773     $relationships = [
 774       $this->contactIDs[1] => ['label' => 'Spouse of'],
 775     ];
 776
 777     foreach ($relationships as $contactID => $relationshipType) {
 778       $relationshipTypeID = $this->callAPISuccess('RelationshipType', 'getvalue', ['label_a_b' => $relationshipType['label'], 'return' => 'id']);
 779       $result = $this->callAPISuccess('Relationship', 'create', [
 780         'contact_id_a' => $this->contactIDs[0],
 781         'relationship_type_id' => $relationshipTypeID,
 782         'contact_id_b' => $contactID,
 783       ]);
 784       $relationships[$contactID]['id'] = $result['id'];
 785       $relationships[$contactID]['relationship_type_id'] = $relationshipTypeID;
 786     }
 787
 788     $fields = [['Individual', 'contact_id']];
 789     // ' ' denotes primary location type.
 790     foreach (array_keys(array_merge($locationTypes, [' ' => ['Primary']])) as $locationType) {
 791       $fields[] = [
 792         'Individual',
 793         'phone',
 794         CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Phone', 'location_type_id', $locationType),
 795       ];
 796       $fields[] = [
 797         'Individual',
 798         'phone_type_id',
 799         CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Phone', 'location_type_id', $locationType),
 800       ];
 801       foreach ($relationships as $contactID => $relationship) {
 802         $fields[] = [
 803           'Individual',
 804           $relationship['relationship_type_id'] . '_a_b',
 805           'phone_type_id',
 806           CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Phone', 'location_type_id', $locationType),
 807         ];
 808       }
 809       foreach ($phoneTypes as $phoneType) {
 810         $fields[] = [
 811           'Individual',
 812           'phone',
 813           CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Phone', 'location_type_id', $locationType),
 814           CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Phone', 'phone_type_id', $phoneType),
 815         ];
 816         foreach ($relationships as $contactID => $relationship) {
 817           $fields[] = [
 818             'Individual',
 819             $relationship['relationship_type_id'] . '_a_b',
 820             'phone_type_id',
 821             CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Phone', 'location_type_id', $locationType),
 822             CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Phone', 'phone_type_id', $phoneType),
 823           ];
 824         }
 825       }
 826     }
 827     $this->doExportTest(['fields' => $fields, 'ids' => [$this->contactIDs[0]]]);
 828     foreach ($this->csv->getRecords() as $row) {
 829       $this->assertEquals('BillingMobile3', $row['Billing-Phone-Mobile']);
 830       $this->assertEquals('', $row['Billing-Phone-Phone']);
 831       $this->assertEquals('Phone', $row['Spouse of-Phone Type']);
 832       $this->assertEquals('Mobile', $row['Phone Type']);
 833       $this->assertEquals('Mobile', $row['Billing-Phone Type']);
 834     }
 835   }
 836
 837   /**
 838    * Export City against multiple location types.
 839    */
 840   public function testExportAddressData() {
 841     $this->diversifyLocationTypes();
 842
 843     $locationTypes = ['Billing' => 'Billing', 'Home' => 'Home', 'Main' => 'Méin', 'Other' => 'Other', 'Whare Kai' => 'Whare Kai'];
 844
 845     $this->contactIDs[] = $this->individualCreate();
 846     $this->contactIDs[] = $this->individualCreate();
 847     $this->contactIDs[] = $this->householdCreate();
 848     $this->contactIDs[] = $this->organizationCreate();
 849     $fields = [['Individual', 'contact_id']];
 850     foreach ($this->contactIDs as $contactID) {
 851       foreach ($locationTypes as $locationName => $locationLabel) {
 852         $this->callAPISuccess('Address', 'create', [
 853           'contact_id' => $contactID,
 854           'location_type_id' => $locationName,
 855           'street_address' => $locationLabel . $contactID . 'street_address',
 856           'city' => $locationLabel . $contactID . 'city',
 857           'postal_code' => $locationLabel . $contactID . 'postal_code',
 858         ]);
 859         $fields[] = ['Individual', 'city', CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Address', 'location_type_id', $locationName)];
 860         $fields[] = ['Individual', 'street_address', CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Address', 'location_type_id', $locationName)];
 861         $fields[] = ['Individual', 'postal_code', CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_Address', 'location_type_id', $locationName)];
 862       }
 863     }
 864
 865     $relationships = [
 866       $this->contactIDs[1] => ['label' => 'Spouse of'],
 867       $this->contactIDs[2] => ['label' => 'Household Member of'],
 868       $this->contactIDs[3] => ['label' => 'Employee of'],
 869     ];
 870
 871     foreach ($relationships as $contactID => $relationshipType) {
 872       $relationshipTypeID = $this->callAPISuccess('RelationshipType', 'getvalue', ['label_a_b' => $relationshipType['label'], 'return' => 'id']);
 873       $result = $this->callAPISuccess('Relationship', 'create', [
 874         'contact_id_a' => $this->contactIDs[0],
 875         'relationship_type_id' => $relationshipTypeID,
 876         'contact_id_b' => $contactID,
 877       ]);
 878       $relationships[$contactID]['id'] = $result['id'];
 879       $relationships[$contactID]['relationship_type_id'] = $relationshipTypeID;
 880     }
 881
 882     // ' ' denotes primary location type.
 883     foreach (array_keys(array_merge($locationTypes, [' ' => ['Primary']])) as $locationType) {
 884       foreach ($relationships as $contactID => $relationship) {
 885         $fields[] = [
 886           'Individual',
 887           $relationship['relationship_type_id'] . '_a_b',
 888           'city',
 889           CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_IM', 'location_type_id', $locationType),
 890         ];
 891       }
 892     }
 893     list($tableName, $sqlColumns) = $this->doExport($fields, $this->contactIDs[0]);
 894
 895     $dao = CRM_Core_DAO::executeQuery('SELECT * FROM ' . $tableName);
 896     while ($dao->fetch()) {
 897       $id = $dao->contact_id;
 898       $this->assertEquals('Méin' . $id . 'city', $dao->main_city);
 899       $this->assertEquals('Billing' . $id . 'street_address', $dao->billing_street_address);
 900       $this->assertEquals('Whare Kai' . $id . 'postal_code', $dao->whare_kai_postal_code);
 901       foreach ($relationships as $relatedContactID => $relationship) {
 902         $relationshipString = $field = $relationship['relationship_type_id'] . '_a_b';
 903         $field = $relationshipString . '_main_city';
 904         $this->assertEquals('Méin' . $relatedContactID . 'city', $dao->$field);
 905       }
 906     }
 907
 908     $this->assertEquals([
 909       'contact_id' => 'contact_id varchar(255)',
 910       'billing_city' => 'billing_city varchar(64)',
 911       'billing_street_address' => 'billing_street_address varchar(96)',
 912       'billing_postal_code' => 'billing_postal_code varchar(64)',
 913       'home_city' => 'home_city varchar(64)',
 914       'home_street_address' => 'home_street_address varchar(96)',
 915       'home_postal_code' => 'home_postal_code varchar(64)',
 916       'main_city' => 'main_city varchar(64)',
 917       'main_street_address' => 'main_street_address varchar(96)',
 918       'main_postal_code' => 'main_postal_code varchar(64)',
 919       'other_city' => 'other_city varchar(64)',
 920       'other_street_address' => 'other_street_address varchar(96)',
 921       'other_postal_code' => 'other_postal_code varchar(64)',
 922       'whare_kai_city' => 'whare_kai_city varchar(64)',
 923       'whare_kai_street_address' => 'whare_kai_street_address varchar(96)',
 924       'whare_kai_postal_code' => 'whare_kai_postal_code varchar(64)',
 925       '2_a_b_billing_city' => '2_a_b_billing_city varchar(64)',
 926       '2_a_b_home_city' => '2_a_b_home_city varchar(64)',
 927       '2_a_b_main_city' => '2_a_b_main_city varchar(64)',
 928       '2_a_b_other_city' => '2_a_b_other_city varchar(64)',
 929       '2_a_b_whare_kai_city' => '2_a_b_whare_kai_city varchar(64)',
 930       '2_a_b_city' => '2_a_b_city varchar(64)',
 931       '8_a_b_billing_city' => '8_a_b_billing_city varchar(64)',
 932       '8_a_b_home_city' => '8_a_b_home_city varchar(64)',
 933       '8_a_b_main_city' => '8_a_b_main_city varchar(64)',
 934       '8_a_b_other_city' => '8_a_b_other_city varchar(64)',
 935       '8_a_b_whare_kai_city' => '8_a_b_whare_kai_city varchar(64)',
 936       '8_a_b_city' => '8_a_b_city varchar(64)',
 937       '5_a_b_billing_city' => '5_a_b_billing_city varchar(64)',
 938       '5_a_b_home_city' => '5_a_b_home_city varchar(64)',
 939       '5_a_b_main_city' => '5_a_b_main_city varchar(64)',
 940       '5_a_b_other_city' => '5_a_b_other_city varchar(64)',
 941       '5_a_b_whare_kai_city' => '5_a_b_whare_kai_city varchar(64)',
 942       '5_a_b_city' => '5_a_b_city varchar(64)',
 943     ], $sqlColumns);
 944   }
 945
 946   /**
 947    * Test master_address_id field.
 948    */
 949   public function testExportMasterAddress() {
 950     $this->setUpContactExportData();
 951
 952     //export the master address for contact B
 953     $selectedFields = [
 954       ['contact_type' => 'Individual', 'name' => 'master_id', 'location_type_id' => 1],
 955     ];
 956     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
 957       TRUE,
 958       [$this->contactIDs[1]],
 959       [],
 960       NULL,
 961       $selectedFields,
 962       NULL,
 963       CRM_Export_Form_Select::CONTACT_EXPORT,
 964       "contact_a.id IN ({$this->contactIDs[1]})",
 965       NULL,
 966       FALSE,
 967       FALSE,
 968       [
 969         'suppress_csv_for_testing' => TRUE,
 970       ]
 971     );
 972     $field = key($sqlColumns);
 973
 974     //assert the exported result
 975     $masterName = CRM_Core_DAO::singleValueQuery("SELECT {$field} FROM {$tableName}");
 976     $displayName = CRM_Contact_BAO_Contact::getMasterDisplayName($this->masterAddressID);
 977     $this->assertEquals($displayName, $masterName);
 978
 979     // delete the export temp table and component table
 980     $sql = "DROP TABLE IF EXISTS {$tableName}";
 981     CRM_Core_DAO::executeQuery($sql);
 982   }
 983
 984   /**
 985    * Test the merge same address option.
 986    *
 987    * @throws \CRM_Core_Exception
 988    * @throws \League\Csv\Exception
 989    */
 990   public function testMergeSameAddress() {
 991     $this->setUpContactExportData();
 992     $this->contactIDs[] = $contact3 = $this->individualCreate(['first_name' => 'Sarah', 'last_name' => 'Smith', 'prefix_id' => 'Dr.']);
 993     // Create address for contact A.
 994     $params = [
 995       'contact_id' => $contact3,
 996       'location_type_id' => 'Home',
 997       'street_address' => 'Ambachtstraat 23',
 998       'postal_code' => '6971 BN',
 999       'country_id' => '1152',
1000       'city' => 'Brummen',
1001       'is_primary' => 1,
1002     ];
1003     $this->callAPISuccess('address', 'create', $params);
1004     $this->doExportTest(['mergeSameAddress' => TRUE, 'ids' => $this->contactIDs]);
1005     // ie 2 merged, one extra.
1006     $this->assertCount(2, $this->csv);
1007     $expected = [
1008       'Contact ID' => $this->contactIDs[0],
1009       'Contact Type' => 'Individual',
1010       'Contact Subtype' => '',
1011       'Do Not Email' => '',
1012       'Do Not Phone' => '',
1013       'Do Not Mail' => '',
1014       'Do Not Sms' => '',
1015       'Do Not Trade' => '',
1016       'No Bulk Emails (User Opt Out)' => '',
1017       'Legal Identifier' => '',
1018       'External Identifier' => '',
1019       'Sort Name' => 'Anderson, Anthony',
1020       'Display Name' => 'Mr. Anthony Anderson II',
1021       'Nickname' => '',
1022       'Legal Name' => '',
1023       'Image Url' => '',
1024       'Preferred Communication Method' => '',
1025       'Preferred Language' => 'en_US',
1026       'Preferred Mail Format' => 'Both',
1027       'Contact Hash' => 'e9bd0913cc05cc5aeae69ba04ee3be84',
1028       'Contact Source' => '',
1029       'First Name' => 'Anthony',
1030       'Middle Name' => 'J.',
1031       'Last Name' => 'Anderson',
1032       'Individual Prefix' => 'Mr.',
1033       'Individual Suffix' => 'II',
1034       'Formal Title' => '',
1035       'Communication Style' => 'Formal',
1036       'Email Greeting ID' => '1',
1037       'Postal Greeting ID' => '1',
1038       'Addressee ID' => '1',
1039       'Job Title' => '',
1040       'Gender' => 'Female',
1041       'Birth Date' => '',
1042       'Deceased' => '',
1043       'Deceased Date' => '',
1044       'Household Name' => '',
1045       'Organization Name' => '',
1046       'Sic Code' => '',
1047       'Unique ID (OpenID)' => '',
1048       'Current Employer ID' => '',
1049       'Contact is in Trash' => '',
1050       'Created Date' => '2019-07-11 10:28:15',
1051       'Modified Date' => '2019-07-11 10:28:15',
1052       'Addressee' => 'Mr. Anthony J. Anderson II, Dr. Sarah J. Smith II',
1053       'Email Greeting' => 'Dear Anthony, Sarah',
1054       'Postal Greeting' => 'Dear Anthony, Sarah',
1055       'Current Employer' => '',
1056       'Location Type' => 'Home',
1057       'Street Address' => 'Ambachtstraat 23',
1058       'Street Number' => '',
1059       'Street Number Suffix' => '',
1060       'Street Name' => '',
1061       'Street Unit' => '',
1062       'Supplemental Address 1' => '',
1063       'Supplemental Address 2' => '',
1064       'Supplemental Address 3' => '',
1065       'City' => 'Brummen',
1066       'Postal Code Suffix' => '',
1067       'Postal Code' => '6971 BN',
1068       'Latitude' => '',
1069       'Longitude' => '',
1070       'Address Name' => '',
1071       'Master Address Belongs To' => '',
1072       'County' => '',
1073       'State' => '',
1074       'Country' => 'Netherlands',
1075       'Phone' => '',
1076       'Phone Extension' => '',
1077       'Phone Type' => '',
1078       'Email' => 'home@example.com',
1079       'On Hold' => '',
1080       'Use for Bulk Mail' => '',
1081       'Signature Text' => '',
1082       'Signature Html' => '',
1083       'IM Provider' => '',
1084       'IM Screen Name' => '',
1085       'OpenID' => '',
1086       'World Region' => 'Europe and Central Asia',
1087       'Website' => '',
1088       'Group(s)' => '',
1089       'Tag(s)' => '',
1090       'Note(s)' => '',
1091     ];
1092     $this->assertExpectedOutput($expected, $this->csv->fetchOne());
1093   }
1094
1095   /**
1096    * Test that deceased and do not mail contacts are removed from contacts before
1097    *
1098    * @dataProvider getReasonsNotToMail
1099    *
1100    * @param array $reason
1101    * @param array $addressReason
1102    *
1103    * @throws \CRM_Core_Exception
1104    */
1105   public function testExportDeceasedDoNotMail($reason, $addressReason) {
1106     $contactA = $this->callAPISuccess('contact', 'create', [
1107       'first_name' => 'John',
1108       'last_name' => 'Doe',
1109       'contact_type' => 'Individual',
1110     ]);
1111
1112     $contactB = $this->callAPISuccess('contact', 'create', array_merge([
1113       'first_name' => 'Jane',
1114       'last_name' => 'Doe',
1115       'contact_type' => 'Individual',
1116     ], $reason));
1117
1118     //create address for contact A
1119     $this->callAPISuccess('address', 'create', [
1120       'contact_id' => $contactA['id'],
1121       'location_type_id' => 'Home',
1122       'street_address' => 'ABC 12',
1123       'postal_code' => '123 AB',
1124       'country_id' => '1152',
1125       'city' => 'ABC',
1126       'is_primary' => 1,
1127     ]);
1128
1129     //create address for contact B
1130     $this->callAPISuccess('address', 'create', array_merge([
1131       'contact_id' => $contactB['id'],
1132       'location_type_id' => 'Home',
1133       'street_address' => 'ABC 12',
1134       'postal_code' => '123 AB',
1135       'country_id' => '1152',
1136       'city' => 'ABC',
1137       'is_primary' => 1,
1138     ], $addressReason));
1139
1140     //export and merge contacts with same address
1141     list($tableName, $sqlColumns, $headerRows, $processor) = CRM_Export_BAO_Export::exportComponents(
1142       TRUE,
1143       [$contactA['id'], $contactB['id']],
1144       [],
1145       NULL,
1146       NULL,
1147       NULL,
1148       CRM_Export_Form_Select::CONTACT_EXPORT,
1149       "contact_a.id IN ({$contactA['id']}, {$contactB['id']})",
1150       NULL,
1151       TRUE,
1152       FALSE,
1153       [
1154         'mergeOption' => TRUE,
1155         'suppress_csv_for_testing' => TRUE,
1156         'postal_mailing_export' => [
1157           'postal_mailing_export' => TRUE,
1158         ],
1159       ]
1160     );
1161
1162     $this->assertTrue(!in_array('state_province_id', $processor->getHeaderRows()));
1163     $greeting = CRM_Core_DAO::singleValueQuery("SELECT email_greeting FROM {$tableName}");
1164
1165     //Assert email_greeting is not merged
1166     $this->assertNotContains(',', (string) $greeting);
1167
1168     // delete the export temp table and component table
1169     $sql = "DROP TABLE IF EXISTS {$tableName}";
1170     CRM_Core_DAO::executeQuery($sql);
1171   }
1172
1173   /**
1174    * Get reasons that a contact is not postalable.
1175    *
1176    * @return array
1177    */
1178   public function getReasonsNotToMail() {
1179     return [
1180       [['is_deceased' => 1], []],
1181       [['do_not_mail' => 1], []],
1182       [[], ['street_address' => '']],
1183     ];
1184   }
1185
1186   /**
1187    * Set up household for tests.
1188    *
1189    * @return array
1190    * @throws \Exception
1191    */
1192   protected function setUpHousehold() {
1193     $this->setUpContactExportData();
1194     $householdID = $this->householdCreate([
1195       'source' => 'household sauce',
1196       'api.Address.create' => [
1197         'city' => 'Portland',
1198         'state_province_id' => 'Maine',
1199         'location_type_id' => 'Home',
1200       ],
1201     ]);
1202
1203     $relationshipTypes = $this->callAPISuccess('RelationshipType', 'get', [])['values'];
1204     $houseHoldTypeID = NULL;
1205     foreach ($relationshipTypes as $id => $relationshipType) {
1206       if ($relationshipType['name_a_b'] === 'Household Member of') {
1207         $houseHoldTypeID = $relationshipType['id'];
1208       }
1209     }
1210     $this->callAPISuccess('Relationship', 'create', [
1211       'contact_id_a' => $this->contactIDs[0],
1212       'contact_id_b' => $householdID,
1213       'relationship_type_id' => $houseHoldTypeID,
1214     ]);
1215     $this->callAPISuccess('Relationship', 'create', [
1216       'contact_id_a' => $this->contactIDs[1],
1217       'contact_id_b' => $householdID,
1218       'relationship_type_id' => $houseHoldTypeID,
1219     ]);
1220     return [$householdID, $houseHoldTypeID];
1221   }
1222
1223   /**
1224    * Do a CiviCRM export.
1225    *
1226    * @param array $selectedFields
1227    * @param int $id
1228    *
1229    * @param int $exportMode
1230    *
1231    * @return array
1232    * @throws \CRM_Core_Exception
1233    */
1234   protected function doExport($selectedFields, $id, $exportMode = CRM_Export_Form_Select::CONTACT_EXPORT) {
1235     $ids = (array) $id;
1236     $mappedFields = [];
1237     foreach ((array) $selectedFields as $field) {
1238       $mappedFields[] = CRM_Core_BAO_Mapping::getMappingParams([], $field);
1239     }
1240     list($tableName, $sqlColumns) = CRM_Export_BAO_Export::exportComponents(
1241       TRUE,
1242       $ids,
1243       [],
1244       NULL,
1245       $mappedFields,
1246       NULL,
1247       $exportMode,
1248       "contact_a.id IN (" . implode(',', $ids) . ")",
1249       NULL,
1250       FALSE,
1251       FALSE,
1252       [
1253         'suppress_csv_for_testing' => TRUE,
1254       ]
1255     );
1256     return [$tableName, $sqlColumns];
1257   }
1258
1259   /**
1260    * Ensure component is enabled.
1261    *
1262    * @param int $exportMode
1263    */
1264   public function ensureComponentIsEnabled($exportMode) {
1265     if ($exportMode === CRM_Export_Form_Select::CASE_EXPORT) {
1266       CRM_Core_BAO_ConfigSetting::enableComponent('CiviCase');
1267     }
1268   }
1269
1270   /**
1271    * Test our export all field metadata retrieval.
1272    *
1273    * @dataProvider additionalFieldsDataProvider
1274    *
1275    * @param int $exportMode
1276    * @param $expected
1277    */
1278   public function testAdditionalReturnProperties($exportMode, $expected) {
1279     $this->ensureComponentIsEnabled($exportMode);
1280     $processor = new CRM_Export_BAO_ExportProcessor($exportMode, NULL, 'AND');
1281     $metadata = $processor->getAdditionalReturnProperties();
1282     $this->assertEquals($expected, $metadata);
1283   }
1284
1285   /**
1286    * Test our export all field metadata retrieval.
1287    *
1288    * @dataProvider allFieldsDataProvider
1289    *
1290    * @param int $exportMode
1291    * @param $expected
1292    */
1293   public function testDefaultReturnProperties($exportMode, $expected) {
1294     $this->ensureComponentIsEnabled($exportMode);
1295     $processor = new CRM_Export_BAO_ExportProcessor($exportMode, NULL, 'AND');
1296     $metadata = $processor->getDefaultReturnProperties();
1297     $this->assertEquals($expected, $metadata);
1298   }
1299
1300   /**
1301    * Get fields returned from additionalFields function.
1302    *
1303    * @return array
1304    */
1305   public function additionalFieldsDataProvider() {
1306     return [
1307       [
1308         'anything that will then be defaulting ton contact',
1309         $this->getExtraReturnProperties(),
1310       ],
1311       [
1312         CRM_Export_Form_Select::ACTIVITY_EXPORT,
1313         array_merge($this->getExtraReturnProperties(), $this->getActivityReturnProperties()),
1314       ],
1315       [
1316         CRM_Export_Form_Select::CASE_EXPORT,
1317         array_merge($this->getExtraReturnProperties(), $this->getCaseReturnProperties()),
1318       ],
1319       [
1320         CRM_Export_Form_Select::CONTRIBUTE_EXPORT,
1321         array_merge($this->getExtraReturnProperties(), $this->getContributionReturnProperties()),
1322       ],
1323       [
1324         CRM_Export_Form_Select::EVENT_EXPORT,
1325         array_merge($this->getExtraReturnProperties(), $this->getEventReturnProperties()),
1326       ],
1327       [
1328         CRM_Export_Form_Select::MEMBER_EXPORT,
1329         array_merge($this->getExtraReturnProperties(), $this->getMembershipReturnProperties()),
1330       ],
1331       [
1332         CRM_Export_Form_Select::PLEDGE_EXPORT,
1333         array_merge($this->getExtraReturnProperties(), $this->getPledgeReturnProperties()),
1334       ],
1335
1336     ];
1337   }
1338
1339   /**
1340    * get data for testing field metadata by query mode.
1341    */
1342   public function allFieldsDataProvider() {
1343     return [
1344       [
1345         'anything that will then be defaulting ton contact',
1346         $this->getBasicReturnProperties(TRUE),
1347       ],
1348       [
1349         CRM_Export_Form_Select::ACTIVITY_EXPORT,
1350         array_merge($this->getBasicReturnProperties(FALSE), $this->getActivityReturnProperties()),
1351       ],
1352       [
1353         CRM_Export_Form_Select::CASE_EXPORT,
1354         array_merge($this->getBasicReturnProperties(FALSE), $this->getCaseReturnProperties()),
1355       ],
1356       [
1357         CRM_Export_Form_Select::CONTRIBUTE_EXPORT,
1358         array_merge($this->getBasicReturnProperties(FALSE), $this->getContributionReturnProperties()),
1359       ],
1360       [
1361         CRM_Export_Form_Select::EVENT_EXPORT,
1362         array_merge($this->getBasicReturnProperties(FALSE), $this->getEventReturnProperties()),
1363       ],
1364       [
1365         CRM_Export_Form_Select::MEMBER_EXPORT,
1366         array_merge($this->getBasicReturnProperties(FALSE), $this->getMembershipReturnProperties()),
1367       ],
1368       [
1369         CRM_Export_Form_Select::PLEDGE_EXPORT,
1370         array_merge($this->getBasicReturnProperties(FALSE), $this->getPledgeReturnProperties()),
1371       ],
1372     ];
1373   }
1374
1375   /**
1376    * Get return properties manually added in.
1377    */
1378   public function getExtraReturnProperties() {
1379     return [];
1380   }
1381
1382   /**
1383    * Get basic return properties.
1384    *
1385    * @param bool $isContactMode
1386    *   Are we in contact mode or not
1387    *
1388    * @return array
1389    */
1390   protected function getBasicReturnProperties($isContactMode) {
1391     $returnProperties = [
1392       'id' => 1,
1393       'contact_type' => 1,
1394       'contact_sub_type' => 1,
1395       'do_not_email' => 1,
1396       'do_not_phone' => 1,
1397       'do_not_mail' => 1,
1398       'do_not_sms' => 1,
1399       'do_not_trade' => 1,
1400       'is_opt_out' => 1,
1401       'legal_identifier' => 1,
1402       'external_identifier' => 1,
1403       'sort_name' => 1,
1404       'display_name' => 1,
1405       'nick_name' => 1,
1406       'legal_name' => 1,
1407       'image_URL' => 1,
1408       'preferred_communication_method' => 1,
1409       'preferred_language' => 1,
1410       'preferred_mail_format' => 1,
1411       'hash' => 1,
1412       'contact_source' => 1,
1413       'first_name' => 1,
1414       'middle_name' => 1,
1415       'last_name' => 1,
1416       'prefix_id' => 1,
1417       'suffix_id' => 1,
1418       'formal_title' => 1,
1419       'communication_style_id' => 1,
1420       'email_greeting_id' => 1,
1421       'postal_greeting_id' => 1,
1422       'addressee_id' => 1,
1423       'job_title' => 1,
1424       'gender_id' => 1,
1425       'birth_date' => 1,
1426       'is_deceased' => 1,
1427       'deceased_date' => 1,
1428       'household_name' => 1,
1429       'organization_name' => 1,
1430       'sic_code' => 1,
1431       'user_unique_id' => 1,
1432       'current_employer_id' => 1,
1433       'contact_is_deleted' => 1,
1434       'created_date' => 1,
1435       'modified_date' => 1,
1436       'addressee' => 1,
1437       'email_greeting' => 1,
1438       'postal_greeting' => 1,
1439       'current_employer' => 1,
1440       'location_type' => 1,
1441       'street_address' => 1,
1442       'street_number' => 1,
1443       'street_number_suffix' => 1,
1444       'street_name' => 1,
1445       'street_unit' => 1,
1446       'supplemental_address_1' => 1,
1447       'supplemental_address_2' => 1,
1448       'supplemental_address_3' => 1,
1449       'city' => 1,
1450       'postal_code_suffix' => 1,
1451       'postal_code' => 1,
1452       'geo_code_1' => 1,
1453       'geo_code_2' => 1,
1454       'address_name' => 1,
1455       'master_id' => 1,
1456       'county' => 1,
1457       'state_province' => 1,
1458       'country' => 1,
1459       'phone' => 1,
1460       'phone_ext' => 1,
1461       'email' => 1,
1462       'on_hold' => 1,
1463       'is_bulkmail' => 1,
1464       'signature_text' => 1,
1465       'signature_html' => 1,
1466       'im_provider' => 1,
1467       'im' => 1,
1468       'openid' => 1,
1469       'world_region' => 1,
1470       'url' => 1,
1471       'groups' => 1,
1472       'tags' => 1,
1473       'notes' => 1,
1474       'phone_type_id' => 1,
1475     ];
1476     if (!$isContactMode) {
1477       unset($returnProperties['groups']);
1478       unset($returnProperties['tags']);
1479       unset($returnProperties['notes']);
1480     }
1481     return $returnProperties;
1482   }
1483
1484   /**
1485    * Get return properties for pledges.
1486    *
1487    * @return array
1488    */
1489   public function getPledgeReturnProperties() {
1490     return [
1491       'contact_type' => 1,
1492       'contact_sub_type' => 1,
1493       'sort_name' => 1,
1494       'display_name' => 1,
1495       'pledge_id' => 1,
1496       'pledge_amount' => 1,
1497       'pledge_total_paid' => 1,
1498       'pledge_create_date' => 1,
1499       'pledge_start_date' => 1,
1500       'pledge_next_pay_date' => 1,
1501       'pledge_next_pay_amount' => 1,
1502       'pledge_status' => 1,
1503       'pledge_is_test' => 1,
1504       'pledge_contribution_page_id' => 1,
1505       'pledge_financial_type' => 1,
1506       'pledge_frequency_interval' => 1,
1507       'pledge_frequency_unit' => 1,
1508       'pledge_currency' => 1,
1509       'pledge_campaign_id' => 1,
1510       'pledge_balance_amount' => 1,
1511       'pledge_payment_id' => 1,
1512       'pledge_payment_scheduled_amount' => 1,
1513       'pledge_payment_scheduled_date' => 1,
1514       'pledge_payment_paid_amount' => 1,
1515       'pledge_payment_paid_date' => 1,
1516       'pledge_payment_reminder_date' => 1,
1517       'pledge_payment_reminder_count' => 1,
1518       'pledge_payment_status' => 1,
1519     ];
1520   }
1521
1522   /**
1523    * Get membership return properties.
1524    *
1525    * @return array
1526    */
1527   public function getMembershipReturnProperties() {
1528     return [
1529       'contact_type' => 1,
1530       'contact_sub_type' => 1,
1531       'sort_name' => 1,
1532       'display_name' => 1,
1533       'membership_type' => 1,
1534       'member_is_test' => 1,
1535       'member_is_pay_later' => 1,
1536       'join_date' => 1,
1537       'membership_start_date' => 1,
1538       'membership_end_date' => 1,
1539       'membership_source' => 1,
1540       'membership_status' => 1,
1541       'membership_id' => 1,
1542       'owner_membership_id' => 1,
1543       'max_related' => 1,
1544       'membership_recur_id' => 1,
1545       'member_campaign_id' => 1,
1546       'member_is_override' => 1,
1547       'member_auto_renew' => 1,
1548     ];
1549   }
1550
1551   /**
1552    * Get return properties for events.
1553    *
1554    * @return array
1555    */
1556   public function getEventReturnProperties() {
1557     return [
1558       'contact_type' => 1,
1559       'contact_sub_type' => 1,
1560       'sort_name' => 1,
1561       'display_name' => 1,
1562       'event_id' => 1,
1563       'event_title' => 1,
1564       'event_start_date' => 1,
1565       'event_end_date' => 1,
1566       'event_type' => 1,
1567       'participant_id' => 1,
1568       'participant_status' => 1,
1569       'participant_status_id' => 1,
1570       'participant_role' => 1,
1571       'participant_role_id' => 1,
1572       'participant_note' => 1,
1573       'participant_register_date' => 1,
1574       'participant_source' => 1,
1575       'participant_fee_level' => 1,
1576       'participant_is_test' => 1,
1577       'participant_is_pay_later' => 1,
1578       'participant_fee_amount' => 1,
1579       'participant_discount_name' => 1,
1580       'participant_fee_currency' => 1,
1581       'participant_registered_by_id' => 1,
1582       'participant_campaign_id' => 1,
1583     ];
1584   }
1585
1586   /**
1587    * Get return properties for activities.
1588    *
1589    * @return array
1590    */
1591   public function getActivityReturnProperties() {
1592     return [
1593       'activity_id' => 1,
1594       'contact_type' => 1,
1595       'contact_sub_type' => 1,
1596       'sort_name' => 1,
1597       'display_name' => 1,
1598       'activity_type' => 1,
1599       'activity_type_id' => 1,
1600       'activity_subject' => 1,
1601       'activity_date_time' => 1,
1602       'activity_duration' => 1,
1603       'activity_location' => 1,
1604       'activity_details' => 1,
1605       'activity_status' => 1,
1606       'activity_priority' => 1,
1607       'source_contact' => 1,
1608       'source_record_id' => 1,
1609       'activity_is_test' => 1,
1610       'activity_campaign_id' => 1,
1611       'result' => 1,
1612       'activity_engagement_level' => 1,
1613       'parent_id' => 1,
1614     ];
1615   }
1616
1617   /**
1618    * Get return properties for Case.
1619    *
1620    * @return array
1621    */
1622   public function getCaseReturnProperties() {
1623     return [
1624       'contact_type' => 1,
1625       'contact_sub_type' => 1,
1626       'sort_name' => 1,
1627       'display_name' => 1,
1628       'phone' => 1,
1629       'case_start_date' => 1,
1630       'case_end_date' => 1,
1631       'case_subject' => 1,
1632       'case_source_contact_id' => 1,
1633       'case_activity_status' => 1,
1634       'case_activity_duration' => 1,
1635       'case_activity_medium_id' => 1,
1636       'case_activity_details' => 1,
1637       'case_activity_is_auto' => 1,
1638       'contact_id' => 1,
1639       'case_id' => 1,
1640       'case_activity_subject' => 1,
1641       'case_status' => 1,
1642       'case_type' => 1,
1643       'case_role' => 1,
1644       'case_deleted' => 1,
1645       'case_recent_activity_date' => 1,
1646       'case_recent_activity_type' => 1,
1647       'case_scheduled_activity_date' => 1,
1648     ];
1649   }
1650
1651   /**
1652    * Get return properties for contribution.
1653    *
1654    * @return array
1655    */
1656   public function getContributionReturnProperties() {
1657     return [
1658       'contact_type' => 1,
1659       'contact_sub_type' => 1,
1660       'sort_name' => 1,
1661       'display_name' => 1,
1662       'financial_type' => 1,
1663       'contribution_source' => 1,
1664       'receive_date' => 1,
1665       'thankyou_date' => 1,
1666       'contribution_cancel_date' => 1,
1667       'total_amount' => 1,
1668       'accounting_code' => 1,
1669       'payment_instrument' => 1,
1670       'payment_instrument_id' => 1,
1671       'contribution_check_number' => 1,
1672       'non_deductible_amount' => 1,
1673       'fee_amount' => 1,
1674       'net_amount' => 1,
1675       'trxn_id' => 1,
1676       'invoice_id' => 1,
1677       'invoice_number' => 1,
1678       'currency' => 1,
1679       'cancel_reason' => 1,
1680       'receipt_date' => 1,
1681       'is_test' => 1,
1682       'is_pay_later' => 1,
1683       'contribution_status' => 1,
1684       'contribution_recur_id' => 1,
1685       'amount_level' => 1,
1686       'contribution_note' => 1,
1687       'contribution_batch' => 1,
1688       'contribution_campaign_title' => 1,
1689       'contribution_campaign_id' => 1,
1690       'contribution_soft_credit_name' => 1,
1691       'contribution_soft_credit_amount' => 1,
1692       'contribution_soft_credit_type' => 1,
1693       'contribution_soft_credit_contact_id' => 1,
1694       'contribution_soft_credit_contribution_id' => 1,
1695     ];
1696   }
1697
1698   /**
1699    * Test the column definition when 'all' fields defined.
1700    *
1701    * @param int $exportMode
1702    * @param array $expected
1703    * @param array $expectedHeaders
1704    *
1705    * @throws \CRM_Core_Exception
1706    * @dataProvider getSqlColumnsOutput
1707    */
1708   public function testGetSQLColumnsAndHeaders($exportMode, $expected, $expectedHeaders) {
1709     $this->ensureComponentIsEnabled($exportMode);
1710     // We need some data so that we can get to the end of the export
1711     // function. Hopefully one day that won't be required to get metadata info out.
1712     // eventually aspire to call $provider->getSQLColumns straight after it
1713     // is intiated.
1714     $this->setupBaseExportData($exportMode);
1715
1716     $result = CRM_Export_BAO_Export::exportComponents(
1717       TRUE,
1718       [1],
1719       [],
1720       NULL,
1721       NULL,
1722       NULL,
1723       $exportMode,
1724       NULL,
1725       NULL,
1726       FALSE,
1727       FALSE,
1728       [
1729         'suppress_csv_for_testing' => TRUE,
1730       ]
1731     );
1732     $this->assertEquals($expected, $result[1]);
1733     $this->assertEquals($expectedHeaders, $result[2]);
1734   }
1735
1736   /**
1737    * Test exported with data entry mis-fire.
1738    *
1739    * Not fatal error if data incomplete.
1740    *
1741    * https://lab.civicrm.org/dev/core/issues/819
1742    */
1743   public function testExportIncompleteSubmission() {
1744     $this->setUpContactExportData();
1745     $this->doExport([['Individual', '']], $this->contactIDs[1]);
1746   }
1747
1748   /**
1749    * Test exported with fields to output specified.
1750    *
1751    * @dataProvider getAllSpecifiableReturnFields
1752    *
1753    * @param int $exportMode
1754    * @param array $selectedFields
1755    * @param array $expected
1756    */
1757   public function testExportSpecifyFields($exportMode, $selectedFields, $expected) {
1758     $this->ensureComponentIsEnabled($exportMode);
1759     $this->setUpContributionExportData();
1760     list($tableName, $sqlColumns) = $this->doExport($selectedFields, $this->contactIDs[1], $exportMode);
1761     $this->assertEquals($expected, $sqlColumns);
1762   }
1763
1764   /**
1765    * Test export fields when no payment fields to be exported.
1766    */
1767   public function textExportParticipantSpecifyFieldsNoPayment() {
1768     $selectedFields = $this->getAllSpecifiableParticipantReturnFields();
1769     foreach ($selectedFields as $index => $field) {
1770       if (substr($field[1], 0, 22) === 'componentPaymentField_') {
1771         unset($selectedFields[$index]);
1772       }
1773     }
1774
1775     $expected = $this->getAllSpecifiableParticipantReturnFields();
1776     foreach ($expected as $index => $field) {
1777       if (substr($index, 0, 22) === 'componentPaymentField_') {
1778         unset($expected[$index]);
1779       }
1780     }
1781
1782     list($tableName, $sqlColumns) = $this->doExport($selectedFields, $this->contactIDs[1], CRM_Export_Form_Select::EVENT_EXPORT);
1783     $this->assertEquals($expected, $sqlColumns);
1784   }
1785
1786   /**
1787    * Get all return fields (@return array
1788    *
1789    * @todo - still being built up.
1790    *
1791    */
1792   public function getAllSpecifiableReturnFields() {
1793     return [
1794       [
1795         CRM_Export_Form_Select::EVENT_EXPORT,
1796         $this->getAllSpecifiableParticipantReturnFields(),
1797         $this->getAllSpecifiableParticipantReturnColumns(),
1798       ],
1799     ];
1800   }
1801
1802   /**
1803    * Get expected return column output for participant mode return all columns.
1804    *
1805    * @return array
1806    */
1807   public function getAllSpecifiableParticipantReturnColumns() {
1808     return [
1809       'participant_campaign_id' => 'participant_campaign_id varchar(128)',
1810       'participant_contact_id' => 'participant_contact_id varchar(16)',
1811       'componentpaymentfield_contribution_status' => 'componentpaymentfield_contribution_status text',
1812       'currency' => 'currency varchar(3)',
1813       'componentpaymentfield_received_date' => 'componentpaymentfield_received_date text',
1814       'default_role_id' => 'default_role_id varchar(16)',
1815       'participant_discount_name' => 'participant_discount_name varchar(16)',
1816       'event_id' => 'event_id varchar(16)',
1817       'event_end_date' => 'event_end_date varchar(32)',
1818       'event_start_date' => 'event_start_date varchar(32)',
1819       'template_title' => 'template_title varchar(255)',
1820       'event_title' => 'event_title varchar(255)',
1821       'participant_fee_amount' => 'participant_fee_amount varchar(32)',
1822       'participant_fee_currency' => 'participant_fee_currency varchar(3)',
1823       'fee_label' => 'fee_label varchar(255)',
1824       'participant_fee_level' => 'participant_fee_level longtext',
1825       'participant_is_pay_later' => 'participant_is_pay_later varchar(16)',
1826       'participant_id' => 'participant_id varchar(16)',
1827       'participant_note' => 'participant_note text',
1828       'participant_role_id' => 'participant_role_id varchar(128)',
1829       'participant_role' => 'participant_role varchar(255)',
1830       'participant_source' => 'participant_source varchar(128)',
1831       'participant_status_id' => 'participant_status_id varchar(16)',
1832       'participant_status' => 'participant_status varchar(255)',
1833       'participant_register_date' => 'participant_register_date varchar(32)',
1834       'participant_registered_by_id' => 'participant_registered_by_id varchar(16)',
1835       'participant_is_test' => 'participant_is_test varchar(16)',
1836       'componentpaymentfield_total_amount' => 'componentpaymentfield_total_amount text',
1837       'componentpaymentfield_transaction_id' => 'componentpaymentfield_transaction_id varchar(255)',
1838       'transferred_to_contact_id' => 'transferred_to_contact_id varchar(16)',
1839     ];
1840   }
1841
1842   /**
1843    * @return array
1844    */
1845   public function getAllSpecifiableParticipantReturnFields() {
1846     return [
1847       0 =>
1848         [
1849           0 => 'Participant',
1850           1 => '',
1851         ],
1852       1 =>
1853         [
1854           0 => 'Participant',
1855           1 => 'participant_campaign_id',
1856         ],
1857       2 =>
1858         [
1859           0 => 'Participant',
1860           1 => 'participant_contact_id',
1861         ],
1862       3 =>
1863         [
1864           0 => 'Participant',
1865           1 => 'componentPaymentField_contribution_status',
1866         ],
1867       4 =>
1868         [
1869           0 => 'Participant',
1870           1 => 'currency',
1871         ],
1872       5 =>
1873         [
1874           0 => 'Participant',
1875           1 => 'componentPaymentField_received_date',
1876         ],
1877       6 =>
1878         [
1879           0 => 'Participant',
1880           1 => 'default_role_id',
1881         ],
1882       7 =>
1883         [
1884           0 => 'Participant',
1885           1 => 'participant_discount_name',
1886         ],
1887       8 =>
1888         [
1889           0 => 'Participant',
1890           1 => 'event_id',
1891         ],
1892       9 =>
1893         [
1894           0 => 'Participant',
1895           1 => 'event_end_date',
1896         ],
1897       10 =>
1898         [
1899           0 => 'Participant',
1900           1 => 'event_start_date',
1901         ],
1902       11 =>
1903         [
1904           0 => 'Participant',
1905           1 => 'template_title',
1906         ],
1907       12 =>
1908         [
1909           0 => 'Participant',
1910           1 => 'event_title',
1911         ],
1912       13 =>
1913         [
1914           0 => 'Participant',
1915           1 => 'participant_fee_amount',
1916         ],
1917       14 =>
1918         [
1919           0 => 'Participant',
1920           1 => 'participant_fee_currency',
1921         ],
1922       15 =>
1923         [
1924           0 => 'Participant',
1925           1 => 'fee_label',
1926         ],
1927       16 =>
1928         [
1929           0 => 'Participant',
1930           1 => 'participant_fee_level',
1931         ],
1932       17 =>
1933         [
1934           0 => 'Participant',
1935           1 => 'participant_is_pay_later',
1936         ],
1937       18 =>
1938         [
1939           0 => 'Participant',
1940           1 => 'participant_id',
1941         ],
1942       19 =>
1943         [
1944           0 => 'Participant',
1945           1 => 'participant_note',
1946         ],
1947       20 =>
1948         [
1949           0 => 'Participant',
1950           1 => 'participant_role_id',
1951         ],
1952       21 =>
1953         [
1954           0 => 'Participant',
1955           1 => 'participant_role',
1956         ],
1957       22 =>
1958         [
1959           0 => 'Participant',
1960           1 => 'participant_source',
1961         ],
1962       23 =>
1963         [
1964           0 => 'Participant',
1965           1 => 'participant_status_id',
1966         ],
1967       24 =>
1968         [
1969           0 => 'Participant',
1970           1 => 'participant_status',
1971         ],
1972       25 =>
1973         [
1974           0 => 'Participant',
1975           1 => 'participant_status',
1976         ],
1977       26 =>
1978         [
1979           0 => 'Participant',
1980           1 => 'participant_register_date',
1981         ],
1982       27 =>
1983         [
1984           0 => 'Participant',
1985           1 => 'participant_registered_by_id',
1986         ],
1987       28 =>
1988         [
1989           0 => 'Participant',
1990           1 => 'participant_is_test',
1991         ],
1992       29 =>
1993         [
1994           0 => 'Participant',
1995           1 => 'componentPaymentField_total_amount',
1996         ],
1997       30 =>
1998         [
1999           0 => 'Participant',
2000           1 => 'componentPaymentField_transaction_id',
2001         ],
2002       31 =>
2003         [
2004           0 => 'Participant',
2005           1 => 'transferred_to_contact_id',
2006         ],
2007     ];
2008   }
2009
2010   /**
2011    * @param string $exportMode
2012    */
2013   public function setupBaseExportData($exportMode) {
2014     $this->createLoggedInUser();
2015     if ($exportMode === CRM_Export_Form_Select::CASE_EXPORT) {
2016       $this->setupCaseExportData();
2017     }
2018     if ($exportMode === CRM_Export_Form_Select::CONTRIBUTE_EXPORT) {
2019       $this->setUpContributionExportData();
2020     }
2021     if ($exportMode === CRM_Export_Form_Select::MEMBER_EXPORT) {
2022       $this->setUpMembershipExportData();
2023     }
2024     if ($exportMode === CRM_Export_Form_Select::ACTIVITY_EXPORT) {
2025       $this->setUpActivityExportData();
2026     }
2027   }
2028
2029   /**
2030    * Get comprehensive sql columns output.
2031    *
2032    * @return array
2033    */
2034   public function getSqlColumnsOutput() {
2035     return [
2036       [
2037         'anything that will then be defaulting ton contact',
2038         $this->getBasicSqlColumnDefinition(TRUE),
2039         $this->getBasicHeaderDefinition(TRUE),
2040       ],
2041       [
2042         CRM_Export_Form_Select::ACTIVITY_EXPORT,
2043         array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getActivitySqlColumns()),
2044         array_merge($this->getBasicHeaderDefinition(FALSE), $this->getActivityHeaderDefinition()),
2045       ],
2046       [
2047         CRM_Export_Form_Select::CASE_EXPORT,
2048         array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getCaseSqlColumns()),
2049         array_merge($this->getBasicHeaderDefinition(FALSE), $this->getCaseHeaderDefinition()),
2050       ],
2051       [
2052         CRM_Export_Form_Select::CONTRIBUTE_EXPORT,
2053         array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getContributionSqlColumns()),
2054         array_merge($this->getBasicHeaderDefinition(FALSE), $this->getContributeHeaderDefinition()),
2055       ],
2056       [
2057         CRM_Export_Form_Select::EVENT_EXPORT,
2058         array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getParticipantSqlColumns()),
2059         array_merge($this->getBasicHeaderDefinition(FALSE), $this->getParticipantHeaderDefinition()),
2060       ],
2061       [
2062         CRM_Export_Form_Select::MEMBER_EXPORT,
2063         array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getMembershipSqlColumns()),
2064         array_merge($this->getBasicHeaderDefinition(FALSE), $this->getMemberHeaderDefinition()),
2065       ],
2066       [
2067         CRM_Export_Form_Select::PLEDGE_EXPORT,
2068         array_merge($this->getBasicSqlColumnDefinition(FALSE), $this->getPledgeSqlColumns()),
2069         array_merge($this->getBasicHeaderDefinition(FALSE), $this->getPledgeHeaderDefinition()),
2070       ],
2071
2072     ];
2073   }
2074
2075   /**
2076    * Get the header definition for exports.
2077    *
2078    * @param bool $isContactExport
2079    *
2080    * @return array
2081    */
2082   protected function getBasicHeaderDefinition($isContactExport) {
2083     $headers = [
2084       0 => 'Contact ID',
2085       1 => 'Contact Type',
2086       2 => 'Contact Subtype',
2087       3 => 'Do Not Email',
2088       4 => 'Do Not Phone',
2089       5 => 'Do Not Mail',
2090       6 => 'Do Not Sms',
2091       7 => 'Do Not Trade',
2092       8 => 'No Bulk Emails (User Opt Out)',
2093       9 => 'Legal Identifier',
2094       10 => 'External Identifier',
2095       11 => 'Sort Name',
2096       12 => 'Display Name',
2097       13 => 'Nickname',
2098       14 => 'Legal Name',
2099       15 => 'Image Url',
2100       16 => 'Preferred Communication Method',
2101       17 => 'Preferred Language',
2102       18 => 'Preferred Mail Format',
2103       19 => 'Contact Hash',
2104       20 => 'Contact Source',
2105       21 => 'First Name',
2106       22 => 'Middle Name',
2107       23 => 'Last Name',
2108       24 => 'Individual Prefix',
2109       25 => 'Individual Suffix',
2110       26 => 'Formal Title',
2111       27 => 'Communication Style',
2112       28 => 'Email Greeting ID',
2113       29 => 'Postal Greeting ID',
2114       30 => 'Addressee ID',
2115       31 => 'Job Title',
2116       32 => 'Gender',
2117       33 => 'Birth Date',
2118       34 => 'Deceased',
2119       35 => 'Deceased Date',
2120       36 => 'Household Name',
2121       37 => 'Organization Name',
2122       38 => 'Sic Code',
2123       39 => 'Unique ID (OpenID)',
2124       40 => 'Current Employer ID',
2125       41 => 'Contact is in Trash',
2126       42 => 'Created Date',
2127       43 => 'Modified Date',
2128       44 => 'Addressee',
2129       45 => 'Email Greeting',
2130       46 => 'Postal Greeting',
2131       47 => 'Current Employer',
2132       48 => 'Location Type',
2133       49 => 'Street Address',
2134       50 => 'Street Number',
2135       51 => 'Street Number Suffix',
2136       52 => 'Street Name',
2137       53 => 'Street Unit',
2138       54 => 'Supplemental Address 1',
2139       55 => 'Supplemental Address 2',
2140       56 => 'Supplemental Address 3',
2141       57 => 'City',
2142       58 => 'Postal Code Suffix',
2143       59 => 'Postal Code',
2144       60 => 'Latitude',
2145       61 => 'Longitude',
2146       62 => 'Address Name',
2147       63 => 'Master Address Belongs To',
2148       64 => 'County',
2149       65 => 'State',
2150       66 => 'Country',
2151       67 => 'Phone',
2152       68 => 'Phone Extension',
2153       69 => 'Phone Type',
2154       70 => 'Email',
2155       71 => 'On Hold',
2156       72 => 'Use for Bulk Mail',
2157       73 => 'Signature Text',
2158       74 => 'Signature Html',
2159       75 => 'IM Provider',
2160       76 => 'IM Screen Name',
2161       77 => 'OpenID',
2162       78 => 'World Region',
2163       79 => 'Website',
2164       80 => 'Group(s)',
2165       81 => 'Tag(s)',
2166       82 => 'Note(s)',
2167     ];
2168     if (!$isContactExport) {
2169       unset($headers[80]);
2170       unset($headers[81]);
2171       unset($headers[82]);
2172     }
2173     return $headers;
2174   }
2175
2176   /**
2177    * Get the definition for activity headers.
2178    *
2179    * @return array
2180    */
2181   protected function getActivityHeaderDefinition() {
2182     return [
2183       81 => 'Activity ID',
2184       82 => 'Activity Type',
2185       83 => 'Activity Type ID',
2186       84 => 'Subject',
2187       85 => 'Activity Date',
2188       86 => 'Duration',
2189       87 => 'Location',
2190       88 => 'Details',
2191       89 => 'Activity Status',
2192       90 => 'Activity Priority',
2193       91 => 'Source Contact',
2194       92 => 'source_record_id',
2195       93 => 'Test',
2196       94 => 'Campaign ID',
2197       95 => 'result',
2198       96 => 'Engagement Index',
2199       97 => 'parent_id',
2200     ];
2201   }
2202
2203   /**
2204    * Get the definition for case headers.
2205    *
2206    * @return array
2207    */
2208   protected function getCaseHeaderDefinition() {
2209     return [
2210       81 => 'contact_id',
2211       82 => 'Case ID',
2212       83 => 'case_activity_subject',
2213       84 => 'Case Subject',
2214       85 => 'Case Status',
2215       86 => 'Case Type',
2216       87 => 'Role in Case',
2217       88 => 'Case is in the Trash',
2218       89 => 'case_recent_activity_date',
2219       90 => 'case_recent_activity_type',
2220       91 => 'case_scheduled_activity_date',
2221       92 => 'Case Start Date',
2222       93 => 'Case End Date',
2223       94 => 'case_source_contact_id',
2224       95 => 'case_activity_status',
2225       96 => 'case_activity_duration',
2226       97 => 'case_activity_medium_id',
2227       98 => 'case_activity_details',
2228       99 => 'case_activity_is_auto',
2229     ];
2230   }
2231
2232   /**
2233    * Get the definition for contribute headers.
2234    *
2235    * @return array
2236    */
2237   protected function getContributeHeaderDefinition() {
2238     return [
2239       81 => 'Financial Type',
2240       82 => 'Contribution Source',
2241       83 => 'Date Received',
2242       84 => 'Thank-you Date',
2243       85 => 'Cancelled / Refunded Date',
2244       86 => 'Total Amount',
2245       87 => 'Accounting Code',
2246       88 => 'Payment Methods',
2247       89 => 'Payment Method ID',
2248       90 => 'Check Number',
2249       91 => 'Non-deductible Amount',
2250       92 => 'Fee Amount',
2251       93 => 'Net Amount',
2252       94 => 'Transaction ID',
2253       95 => 'Invoice Reference',
2254       96 => 'Invoice Number',
2255       97 => 'Currency',
2256       98 => 'Cancellation / Refund Reason',
2257       99 => 'Receipt Date',
2258       106 => 'Test',
2259       107 => 'Is Pay Later',
2260       108 => 'Contribution Status',
2261       109 => 'Recurring Contribution ID',
2262       110 => 'Amount Label',
2263       111 => 'Contribution Note',
2264       112 => 'Batch Name',
2265       113 => 'Campaign Title',
2266       114 => 'Campaign ID',
2267       116 => 'Soft Credit For',
2268       117 => 'Soft Credit Amount',
2269       118 => 'Soft Credit Type',
2270       119 => 'Soft Credit For Contact ID',
2271       120 => 'Soft Credit For Contribution ID',
2272     ];
2273   }
2274
2275   /**
2276    * Get the definition for event headers.
2277    *
2278    * @return array
2279    */
2280   protected function getParticipantHeaderDefinition() {
2281     return [
2282       81 => 'Event',
2283       82 => 'Event Title',
2284       83 => 'Event Start Date',
2285       84 => 'Event End Date',
2286       85 => 'Event Type',
2287       86 => 'Participant ID',
2288       87 => 'Participant Status',
2289       88 => 'Participant Status Id',
2290       89 => 'Participant Role',
2291       90 => 'Participant Role Id',
2292       91 => 'Participant Note',
2293       92 => 'Register date',
2294       93 => 'Participant Source',
2295       94 => 'Fee level',
2296       95 => 'Test',
2297       96 => 'Is Pay Later',
2298       97 => 'Fee Amount',
2299       98 => 'Discount Name',
2300       99 => 'Fee Currency',
2301       100 => 'Registered By ID',
2302       101 => 'Campaign ID',
2303     ];
2304   }
2305
2306   /**
2307    * Get the definition for member headers.
2308    *
2309    * @return array
2310    */
2311   protected function getMemberHeaderDefinition() {
2312     return [
2313       81 => 'Membership Type',
2314       82 => 'Test',
2315       83 => 'Is Pay Later',
2316       84 => 'Member Since',
2317       85 => 'Membership Start Date',
2318       86 => 'Membership Expiration Date',
2319       87 => 'Source',
2320       88 => 'Membership Status',
2321       89 => 'Membership ID',
2322       90 => 'Primary Member ID',
2323       91 => 'max_related',
2324       92 => 'membership_recur_id',
2325       93 => 'Campaign ID',
2326       94 => 'member_is_override',
2327       95 => 'member_auto_renew',
2328     ];
2329   }
2330
2331   /**
2332    * Get the definition for pledge headers.
2333    *
2334    * @return array
2335    */
2336   protected function getPledgeHeaderDefinition() {
2337     return [
2338       81 => 'Pledge ID',
2339       82 => 'Total Pledged',
2340       83 => 'Total Paid',
2341       84 => 'Pledge Made',
2342       85 => 'pledge_start_date',
2343       86 => 'Next Payment Date',
2344       87 => 'Next Payment Amount',
2345       88 => 'Pledge Status',
2346       89 => 'Test',
2347       90 => 'Pledge Contribution Page Id',
2348       91 => 'pledge_financial_type',
2349       92 => 'Pledge Frequency Interval',
2350       93 => 'Pledge Frequency Unit',
2351       94 => 'pledge_currency',
2352       95 => 'Campaign ID',
2353       96 => 'Balance Amount',
2354       97 => 'Payment ID',
2355       98 => 'Scheduled Amount',
2356       99 => 'Scheduled Date',
2357       100 => 'Paid Amount',
2358       101 => 'Paid Date',
2359       102 => 'Last Reminder',
2360       103 => 'Reminders Sent',
2361       104 => 'Pledge Payment Status',
2362     ];
2363   }
2364
2365   /**
2366    * Get the column definition for exports.
2367    *
2368    * @param bool $isContactExport
2369    *
2370    * @return array
2371    */
2372   protected function getBasicSqlColumnDefinition($isContactExport) {
2373     $columns = [
2374       'civicrm_primary_id' => 'civicrm_primary_id varchar(16)',
2375       'contact_type' => 'contact_type varchar(64)',
2376       'contact_sub_type' => 'contact_sub_type varchar(255)',
2377       'do_not_email' => 'do_not_email varchar(16)',
2378       'do_not_phone' => 'do_not_phone varchar(16)',
2379       'do_not_mail' => 'do_not_mail varchar(16)',
2380       'do_not_sms' => 'do_not_sms varchar(16)',
2381       'do_not_trade' => 'do_not_trade varchar(16)',
2382       'is_opt_out' => 'is_opt_out varchar(16)',
2383       'legal_identifier' => 'legal_identifier varchar(32)',
2384       'external_identifier' => 'external_identifier varchar(64)',
2385       'sort_name' => 'sort_name varchar(128)',
2386       'display_name' => 'display_name varchar(128)',
2387       'nick_name' => 'nick_name varchar(128)',
2388       'legal_name' => 'legal_name varchar(128)',
2389       'image_url' => 'image_url longtext',
2390       'preferred_communication_method' => 'preferred_communication_method varchar(255)',
2391       'preferred_language' => 'preferred_language varchar(5)',
2392       'preferred_mail_format' => 'preferred_mail_format varchar(8)',
2393       'hash' => 'hash varchar(32)',
2394       'contact_source' => 'contact_source varchar(255)',
2395       'first_name' => 'first_name varchar(64)',
2396       'middle_name' => 'middle_name varchar(64)',
2397       'last_name' => 'last_name varchar(64)',
2398       'prefix_id' => 'prefix_id varchar(255)',
2399       'suffix_id' => 'suffix_id varchar(255)',
2400       'formal_title' => 'formal_title varchar(64)',
2401       'communication_style_id' => 'communication_style_id varchar(16)',
2402       'email_greeting_id' => 'email_greeting_id varchar(16)',
2403       'postal_greeting_id' => 'postal_greeting_id varchar(16)',
2404       'addressee_id' => 'addressee_id varchar(16)',
2405       'job_title' => 'job_title varchar(255)',
2406       'gender_id' => 'gender_id varchar(16)',
2407       'birth_date' => 'birth_date varchar(32)',
2408       'is_deceased' => 'is_deceased varchar(16)',
2409       'deceased_date' => 'deceased_date varchar(32)',
2410       'household_name' => 'household_name varchar(128)',
2411       'organization_name' => 'organization_name varchar(128)',
2412       'sic_code' => 'sic_code varchar(8)',
2413       'user_unique_id' => 'user_unique_id varchar(255)',
2414       'current_employer_id' => 'current_employer_id varchar(16)',
2415       'contact_is_deleted' => 'contact_is_deleted varchar(16)',
2416       'created_date' => 'created_date varchar(32)',
2417       'modified_date' => 'modified_date varchar(32)',
2418       'addressee' => 'addressee varchar(255)',
2419       'email_greeting' => 'email_greeting varchar(255)',
2420       'postal_greeting' => 'postal_greeting varchar(255)',
2421       'current_employer' => 'current_employer varchar(128)',
2422       'location_type' => 'location_type text',
2423       'street_address' => 'street_address varchar(96)',
2424       'street_number' => 'street_number varchar(16)',
2425       'street_number_suffix' => 'street_number_suffix varchar(8)',
2426       'street_name' => 'street_name varchar(64)',
2427       'street_unit' => 'street_unit varchar(16)',
2428       'supplemental_address_1' => 'supplemental_address_1 varchar(96)',
2429       'supplemental_address_2' => 'supplemental_address_2 varchar(96)',
2430       'supplemental_address_3' => 'supplemental_address_3 varchar(96)',
2431       'city' => 'city varchar(64)',
2432       'postal_code_suffix' => 'postal_code_suffix varchar(12)',
2433       'postal_code' => 'postal_code varchar(64)',
2434       'geo_code_1' => 'geo_code_1 varchar(32)',
2435       'geo_code_2' => 'geo_code_2 varchar(32)',
2436       'address_name' => 'address_name varchar(255)',
2437       'master_id' => 'master_id varchar(128)',
2438       'county' => 'county varchar(64)',
2439       'state_province' => 'state_province varchar(64)',
2440       'country' => 'country varchar(64)',
2441       'phone' => 'phone varchar(32)',
2442       'phone_ext' => 'phone_ext varchar(16)',
2443       'phone_type_id' => 'phone_type_id varchar(16)',
2444       'email' => 'email varchar(254)',
2445       'on_hold' => 'on_hold varchar(16)',
2446       'is_bulkmail' => 'is_bulkmail varchar(16)',
2447       'signature_text' => 'signature_text longtext',
2448       'signature_html' => 'signature_html longtext',
2449       'im_provider' => 'im_provider text',
2450       'im_screen_name' => 'im_screen_name varchar(64)',
2451       'openid' => 'openid varchar(255)',
2452       'world_region' => 'world_region varchar(128)',
2453       'url' => 'url varchar(128)',
2454       'groups' => 'groups text',
2455       'tags' => 'tags text',
2456       'notes' => 'notes text',
2457     ];
2458     if (!$isContactExport) {
2459       unset($columns['groups']);
2460       unset($columns['tags']);
2461       unset($columns['notes']);
2462     }
2463     return $columns;
2464   }
2465
2466   /**
2467    * Get Case SQL columns.
2468    *
2469    * @return array
2470    */
2471   protected function getCaseSqlColumns() {
2472     return [
2473       'case_start_date' => 'case_start_date varchar(32)',
2474       'case_end_date' => 'case_end_date varchar(32)',
2475       'case_subject' => 'case_subject varchar(128)',
2476       'case_source_contact_id' => 'case_source_contact_id varchar(255)',
2477       'case_activity_status' => 'case_activity_status text',
2478       'case_activity_duration' => 'case_activity_duration text',
2479       'case_activity_medium_id' => 'case_activity_medium_id varchar(255)',
2480       'case_activity_details' => 'case_activity_details text',
2481       'case_activity_is_auto' => 'case_activity_is_auto text',
2482       'contact_id' => 'contact_id varchar(255)',
2483       'case_id' => 'case_id varchar(16)',
2484       'case_activity_subject' => 'case_activity_subject text',
2485       'case_status' => 'case_status text',
2486       'case_type' => 'case_type text',
2487       'case_role' => 'case_role text',
2488       'case_deleted' => 'case_deleted varchar(16)',
2489       'case_recent_activity_date' => 'case_recent_activity_date text',
2490       'case_recent_activity_type' => 'case_recent_activity_type text',
2491       'case_scheduled_activity_date' => 'case_scheduled_activity_date text',
2492     ];
2493   }
2494
2495   /**
2496    * Get activity sql columns.
2497    *
2498    * @return array
2499    */
2500   protected function getActivitySqlColumns() {
2501     return [
2502       'activity_id' => 'activity_id varchar(16)',
2503       'activity_type' => 'activity_type varchar(255)',
2504       'activity_type_id' => 'activity_type_id varchar(16)',
2505       'activity_subject' => 'activity_subject varchar(255)',
2506       'activity_date_time' => 'activity_date_time varchar(32)',
2507       'activity_duration' => 'activity_duration varchar(16)',
2508       'activity_location' => 'activity_location varchar(255)',
2509       'activity_details' => 'activity_details longtext',
2510       'activity_status' => 'activity_status varchar(255)',
2511       'activity_priority' => 'activity_priority varchar(255)',
2512       'source_contact' => 'source_contact varchar(255)',
2513       'source_record_id' => 'source_record_id varchar(255)',
2514       'activity_is_test' => 'activity_is_test varchar(16)',
2515       'activity_campaign_id' => 'activity_campaign_id varchar(128)',
2516       'result' => 'result text',
2517       'activity_engagement_level' => 'activity_engagement_level varchar(16)',
2518       'parent_id' => 'parent_id varchar(255)',
2519     ];
2520   }
2521
2522   /**
2523    * Get participant sql columns.
2524    *
2525    * @return array
2526    */
2527   protected function getParticipantSqlColumns() {
2528     return [
2529       'event_id' => 'event_id varchar(16)',
2530       'event_title' => 'event_title varchar(255)',
2531       'event_start_date' => 'event_start_date varchar(32)',
2532       'event_end_date' => 'event_end_date varchar(32)',
2533       'event_type' => 'event_type varchar(255)',
2534       'participant_id' => 'participant_id varchar(16)',
2535       'participant_status' => 'participant_status varchar(255)',
2536       'participant_status_id' => 'participant_status_id varchar(16)',
2537       'participant_role' => 'participant_role varchar(255)',
2538       'participant_role_id' => 'participant_role_id varchar(128)',
2539       'participant_note' => 'participant_note text',
2540       'participant_register_date' => 'participant_register_date varchar(32)',
2541       'participant_source' => 'participant_source varchar(128)',
2542       'participant_fee_level' => 'participant_fee_level longtext',
2543       'participant_is_test' => 'participant_is_test varchar(16)',
2544       'participant_is_pay_later' => 'participant_is_pay_later varchar(16)',
2545       'participant_fee_amount' => 'participant_fee_amount varchar(32)',
2546       'participant_discount_name' => 'participant_discount_name varchar(16)',
2547       'participant_fee_currency' => 'participant_fee_currency varchar(3)',
2548       'participant_registered_by_id' => 'participant_registered_by_id varchar(16)',
2549       'participant_campaign_id' => 'participant_campaign_id varchar(128)',
2550     ];
2551   }
2552
2553   /**
2554    * Get contribution sql columns.
2555    *
2556    * @return array
2557    */
2558   public function getContributionSqlColumns() {
2559     return [
2560       'civicrm_primary_id' => 'civicrm_primary_id varchar(16)',
2561       'contact_type' => 'contact_type varchar(64)',
2562       'contact_sub_type' => 'contact_sub_type varchar(255)',
2563       'do_not_email' => 'do_not_email varchar(16)',
2564       'do_not_phone' => 'do_not_phone varchar(16)',
2565       'do_not_mail' => 'do_not_mail varchar(16)',
2566       'do_not_sms' => 'do_not_sms varchar(16)',
2567       'do_not_trade' => 'do_not_trade varchar(16)',
2568       'is_opt_out' => 'is_opt_out varchar(16)',
2569       'legal_identifier' => 'legal_identifier varchar(32)',
2570       'external_identifier' => 'external_identifier varchar(64)',
2571       'sort_name' => 'sort_name varchar(128)',
2572       'display_name' => 'display_name varchar(128)',
2573       'nick_name' => 'nick_name varchar(128)',
2574       'legal_name' => 'legal_name varchar(128)',
2575       'image_url' => 'image_url longtext',
2576       'preferred_communication_method' => 'preferred_communication_method varchar(255)',
2577       'preferred_language' => 'preferred_language varchar(5)',
2578       'preferred_mail_format' => 'preferred_mail_format varchar(8)',
2579       'hash' => 'hash varchar(32)',
2580       'contact_source' => 'contact_source varchar(255)',
2581       'first_name' => 'first_name varchar(64)',
2582       'middle_name' => 'middle_name varchar(64)',
2583       'last_name' => 'last_name varchar(64)',
2584       'prefix_id' => 'prefix_id varchar(255)',
2585       'suffix_id' => 'suffix_id varchar(255)',
2586       'formal_title' => 'formal_title varchar(64)',
2587       'communication_style_id' => 'communication_style_id varchar(16)',
2588       'email_greeting_id' => 'email_greeting_id varchar(16)',
2589       'postal_greeting_id' => 'postal_greeting_id varchar(16)',
2590       'addressee_id' => 'addressee_id varchar(16)',
2591       'job_title' => 'job_title varchar(255)',
2592       'gender_id' => 'gender_id varchar(16)',
2593       'birth_date' => 'birth_date varchar(32)',
2594       'is_deceased' => 'is_deceased varchar(16)',
2595       'deceased_date' => 'deceased_date varchar(32)',
2596       'household_name' => 'household_name varchar(128)',
2597       'organization_name' => 'organization_name varchar(128)',
2598       'sic_code' => 'sic_code varchar(8)',
2599       'user_unique_id' => 'user_unique_id varchar(255)',
2600       'current_employer_id' => 'current_employer_id varchar(16)',
2601       'contact_is_deleted' => 'contact_is_deleted varchar(16)',
2602       'created_date' => 'created_date varchar(32)',
2603       'modified_date' => 'modified_date varchar(32)',
2604       'addressee' => 'addressee varchar(255)',
2605       'email_greeting' => 'email_greeting varchar(255)',
2606       'postal_greeting' => 'postal_greeting varchar(255)',
2607       'current_employer' => 'current_employer varchar(128)',
2608       'location_type' => 'location_type text',
2609       'street_address' => 'street_address varchar(96)',
2610       'street_number' => 'street_number varchar(16)',
2611       'street_number_suffix' => 'street_number_suffix varchar(8)',
2612       'street_name' => 'street_name varchar(64)',
2613       'street_unit' => 'street_unit varchar(16)',
2614       'supplemental_address_1' => 'supplemental_address_1 varchar(96)',
2615       'supplemental_address_2' => 'supplemental_address_2 varchar(96)',
2616       'supplemental_address_3' => 'supplemental_address_3 varchar(96)',
2617       'city' => 'city varchar(64)',
2618       'postal_code_suffix' => 'postal_code_suffix varchar(12)',
2619       'postal_code' => 'postal_code varchar(64)',
2620       'geo_code_1' => 'geo_code_1 varchar(32)',
2621       'geo_code_2' => 'geo_code_2 varchar(32)',
2622       'address_name' => 'address_name varchar(255)',
2623       'master_id' => 'master_id varchar(128)',
2624       'county' => 'county varchar(64)',
2625       'state_province' => 'state_province varchar(64)',
2626       'country' => 'country varchar(64)',
2627       'phone' => 'phone varchar(32)',
2628       'phone_ext' => 'phone_ext varchar(16)',
2629       'email' => 'email varchar(254)',
2630       'on_hold' => 'on_hold varchar(16)',
2631       'is_bulkmail' => 'is_bulkmail varchar(16)',
2632       'signature_text' => 'signature_text longtext',
2633       'signature_html' => 'signature_html longtext',
2634       'im_provider' => 'im_provider text',
2635       'im_screen_name' => 'im_screen_name varchar(64)',
2636       'openid' => 'openid varchar(255)',
2637       'world_region' => 'world_region varchar(128)',
2638       'url' => 'url varchar(128)',
2639       'phone_type_id' => 'phone_type_id varchar(16)',
2640       'financial_type' => 'financial_type varchar(255)',
2641       'contribution_source' => 'contribution_source varchar(255)',
2642       'receive_date' => 'receive_date varchar(32)',
2643       'thankyou_date' => 'thankyou_date varchar(32)',
2644       'contribution_cancel_date' => 'contribution_cancel_date varchar(32)',
2645       'total_amount' => 'total_amount varchar(32)',
2646       'accounting_code' => 'accounting_code varchar(64)',
2647       'payment_instrument' => 'payment_instrument varchar(255)',
2648       'payment_instrument_id' => 'payment_instrument_id varchar(16)',
2649       'contribution_check_number' => 'contribution_check_number varchar(255)',
2650       'non_deductible_amount' => 'non_deductible_amount varchar(32)',
2651       'fee_amount' => 'fee_amount varchar(32)',
2652       'net_amount' => 'net_amount varchar(32)',
2653       'trxn_id' => 'trxn_id varchar(255)',
2654       'invoice_id' => 'invoice_id varchar(255)',
2655       'invoice_number' => 'invoice_number varchar(255)',
2656       'currency' => 'currency varchar(3)',
2657       'cancel_reason' => 'cancel_reason longtext',
2658       'receipt_date' => 'receipt_date varchar(32)',
2659       'is_test' => 'is_test varchar(16)',
2660       'is_pay_later' => 'is_pay_later varchar(16)',
2661       'contribution_status' => 'contribution_status varchar(255)',
2662       'contribution_recur_id' => 'contribution_recur_id varchar(16)',
2663       'amount_level' => 'amount_level longtext',
2664       'contribution_note' => 'contribution_note text',
2665       'contribution_batch' => 'contribution_batch text',
2666       'contribution_campaign_title' => 'contribution_campaign_title varchar(255)',
2667       'contribution_campaign_id' => 'contribution_campaign_id varchar(128)',
2668       'contribution_soft_credit_name' => 'contribution_soft_credit_name varchar(255)',
2669       'contribution_soft_credit_amount' => 'contribution_soft_credit_amount varchar(255)',
2670       'contribution_soft_credit_type' => 'contribution_soft_credit_type varchar(255)',
2671       'contribution_soft_credit_contact_id' => 'contribution_soft_credit_contact_id varchar(255)',
2672       'contribution_soft_credit_contribution_id' => 'contribution_soft_credit_contribution_id varchar(255)',
2673     ];
2674   }
2675
2676   /**
2677    * Get pledge sql columns.
2678    *
2679    * @return array
2680    */
2681   public function getPledgeSqlColumns() {
2682     return [
2683       'pledge_id' => 'pledge_id varchar(16)',
2684       'pledge_amount' => 'pledge_amount varchar(32)',
2685       'pledge_total_paid' => 'pledge_total_paid text',
2686       'pledge_create_date' => 'pledge_create_date varchar(32)',
2687       'pledge_start_date' => 'pledge_start_date text',
2688       'pledge_next_pay_date' => 'pledge_next_pay_date text',
2689       'pledge_next_pay_amount' => 'pledge_next_pay_amount text',
2690       'pledge_status' => 'pledge_status varchar(255)',
2691       'pledge_is_test' => 'pledge_is_test varchar(16)',
2692       'pledge_contribution_page_id' => 'pledge_contribution_page_id varchar(255)',
2693       'pledge_financial_type' => 'pledge_financial_type text',
2694       'pledge_frequency_interval' => 'pledge_frequency_interval varchar(255)',
2695       'pledge_frequency_unit' => 'pledge_frequency_unit varchar(255)',
2696       'pledge_currency' => 'pledge_currency text',
2697       'pledge_campaign_id' => 'pledge_campaign_id varchar(128)',
2698       'pledge_balance_amount' => 'pledge_balance_amount text',
2699       'pledge_payment_id' => 'pledge_payment_id varchar(16)',
2700       'pledge_payment_scheduled_amount' => 'pledge_payment_scheduled_amount varchar(32)',
2701       'pledge_payment_scheduled_date' => 'pledge_payment_scheduled_date varchar(32)',
2702       'pledge_payment_paid_amount' => 'pledge_payment_paid_amount text',
2703       'pledge_payment_paid_date' => 'pledge_payment_paid_date text',
2704       'pledge_payment_reminder_date' => 'pledge_payment_reminder_date varchar(32)',
2705       'pledge_payment_reminder_count' => 'pledge_payment_reminder_count varchar(16)',
2706       'pledge_payment_status' => 'pledge_payment_status varchar(255)',
2707     ];
2708   }
2709
2710   /**
2711    * Get membership sql columns.
2712    *
2713    * @return array
2714    */
2715   public function getMembershipSqlColumns() {
2716     return [
2717       'membership_type' => 'membership_type varchar(128)',
2718       'member_is_test' => 'member_is_test varchar(16)',
2719       'member_is_pay_later' => 'member_is_pay_later varchar(16)',
2720       'join_date' => 'join_date varchar(32)',
2721       'membership_start_date' => 'membership_start_date varchar(32)',
2722       'membership_end_date' => 'membership_end_date varchar(32)',
2723       'membership_source' => 'membership_source varchar(128)',
2724       'membership_status' => 'membership_status varchar(255)',
2725       'membership_id' => 'membership_id varchar(16)',
2726       'owner_membership_id' => 'owner_membership_id varchar(16)',
2727       'max_related' => 'max_related text',
2728       'membership_recur_id' => 'membership_recur_id varchar(255)',
2729       'member_campaign_id' => 'member_campaign_id varchar(128)',
2730       'member_is_override' => 'member_is_override text',
2731       'member_auto_renew' => 'member_auto_renew text',
2732     ];
2733   }
2734
2735   /**
2736    * Change our location types so we have some edge cases in the mix.
2737    *
2738    * - a space in the name
2739    * - name differs from label
2740    * - non-anglo char in the label (not valid in the name).
2741    */
2742   protected function diversifyLocationTypes() {
2743     $this->locationTypes['Main'] = $this->callAPISuccess('Location_type', 'get', [
2744       'name' => 'Main',
2745       'return' => 'id',
2746       'api.LocationType.Create' => ['display_name' => 'Méin'],
2747     ]);
2748     $this->locationTypes['Whare Kai'] = $this->callAPISuccess('Location_type', 'create', [
2749       'name' => 'Whare Kai',
2750       'display_name' => 'Whare Kai',
2751     ]);
2752   }
2753
2754   /**
2755    * Test export components.
2756    *
2757    * Tests the exportComponents function with the provided parameters.
2758    *
2759    * This exportComponents will export a csv but it will also throw a prematureExitException
2760    * which we catch & grab the processor from.
2761    *
2762    * $this->processor is set to the export processor.
2763    *
2764    * @param $params
2765    *
2766    * @throws \CRM_Core_Exception
2767    * @throws \League\Csv\Exception
2768    */
2769   protected function doExportTest($params) {
2770     $this->startCapturingOutput();
2771     try {
2772       $defaultClause = (empty($params['ids']) ? NULL : "contact_a.id IN (" . implode(',', $params['ids']) . ")");
2773       $mappedFields = [];
2774       foreach (CRM_Utils_Array::value('fields', $params, []) as $field) {
2775         $mappedFields[] = CRM_Core_BAO_Mapping::getMappingParams([], $field);
2776       }
2777       CRM_Export_BAO_Export::exportComponents(
2778         CRM_Utils_Array::value('selectAll', $params, (empty($params['fields']))),
2779         CRM_Utils_Array::value('ids', $params, []),
2780         CRM_Utils_Array::value('params', $params, []),
2781         CRM_Utils_Array::value('order', $params),
2782         $mappedFields,
2783         CRM_Utils_Array::value('moreReturnProperties', $params),
2784         CRM_Utils_Array::value('exportMode', $params, CRM_Export_Form_Select::CONTACT_EXPORT),
2785         CRM_Utils_Array::value('componentClause', $params, $defaultClause),
2786         CRM_Utils_Array::value('componentTable', $params),
2787         CRM_Utils_Array::value('mergeSameAddress', $params, FALSE),
2788         CRM_Utils_Array::value('mergeSameHousehold', $params, FALSE)
2789       );
2790     }
2791     catch (CRM_Core_Exception_PrematureExitException $e) {
2792       $this->processor = $e->errorData['processor'];
2793       $this->csv = $this->captureOutputToCSV();
2794       return;
2795     }
2796     $this->fail('We expected a premature exit exception');
2797   }
2798
2799   /**
2800    * Assert that the received array matches the expected, ignoring time sensitive fields.
2801    *
2802    * @param array $expected
2803    * @param array $row
2804    */
2805   protected function assertExpectedOutput(array $expected, array $row) {
2806     $variableFields = ['Created Date', 'Modified Date', 'Contact Hash'];
2807     foreach ($expected as $key => $value) {
2808       if (in_array($key, $variableFields)) {
2809         $this->assertTrue(!empty($row[$key]));
2810       }
2811       else {
2812         $this->assertEquals($value, $row[$key]);
2813       }
2814     }
2815   }
2816
2817 }