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