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

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