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