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