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