3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
19 * Class CRM_Export_BAO_ExportProcessor
21 * Class to handle logic of export.
23 class CRM_Export_BAO_ExportProcessor
{
33 protected $exportMode;
36 * Array of fields in the main query.
40 protected $queryFields = [];
47 protected $queryOperator;
50 * Requested output fields.
52 * If set to NULL then it is 'primary fields only'
53 * which actually means pretty close to all fields!
57 protected $requestedFields;
60 * Is the contact being merged into a single household.
64 protected $isMergeSameHousehold;
67 * Should contacts with the same address be merged.
71 protected $isMergeSameAddress = FALSE;
74 * Fields that need to be retrieved for address merge purposes but should not be in output.
78 protected $additionalFieldsForSameAddressMerge = [];
81 * Fields used for merging same contacts.
85 protected $contactGreetingFields = [];
88 * An array of primary IDs of the entity being exported.
95 * Greeting options mapping to various greeting ids.
97 * This stores the option values for the addressee, postal_greeting & email_greeting
102 protected $greetingOptions = [];
105 * Get additional non-visible fields for address merge purposes.
109 public function getAdditionalFieldsForSameAddressMerge(): array {
110 return $this->additionalFieldsForSameAddressMerge
;
114 * Set additional non-visible fields for address merge purposes.
116 public function setAdditionalFieldsForSameAddressMerge() {
117 if ($this->isMergeSameAddress
) {
118 $fields = ['id', 'master_id', 'state_province_id', 'postal_greeting_id', 'addressee_id'];
119 foreach ($fields as $index => $field) {
120 if (!empty($this->getReturnProperties()[$field])) {
121 unset($fields[$index]);
124 $this->additionalFieldsForSameAddressMerge
= array_fill_keys($fields, 1);
129 * Should contacts with the same address be merged.
133 public function isMergeSameAddress(): bool {
134 return $this->isMergeSameAddress
;
138 * Set same address is to be merged.
140 * @param bool $isMergeSameAddress
142 public function setIsMergeSameAddress(bool $isMergeSameAddress) {
143 $this->isMergeSameAddress
= $isMergeSameAddress;
147 * Additional fields required to export postal fields.
151 protected $additionalFieldsForPostalExport = [];
154 * Get additional fields required to do a postal export.
158 public function getAdditionalFieldsForPostalExport() {
159 return $this->additionalFieldsForPostalExport
;
163 * Set additional fields required for a postal export.
165 public function setAdditionalFieldsForPostalExport() {
166 if ($this->getRequestedFields() && $this->isPostalableOnly()) {
167 $fields = ['is_deceased', 'do_not_mail', 'street_address', 'supplemental_address_1'];
168 foreach ($fields as $index => $field) {
169 if (!empty($this->getReturnProperties()[$field])) {
170 unset($fields[$index]);
173 $this->additionalFieldsForPostalExport
= array_fill_keys($fields, 1);
178 * Only export contacts that can receive postal mail.
180 * Includes being alive, having an address & not having do_not_mail.
184 protected $isPostalableOnly;
187 * Key representing the head of household in the relationship array.
189 * e.g. ['8_b_a' => 'Household Member Is', '8_a_b = 'Household Member Of'.....]
193 protected $relationshipTypes = [];
196 * Array of properties to retrieve for relationships.
200 protected $relationshipReturnProperties = [];
203 * IDs of households that have already been exported.
207 protected $exportedHouseholds = [];
210 * Contacts to be merged by virtue of their shared address.
214 protected $contactsToMerge = [];
217 * Households to skip during export as they will be exported via their relationships anyway.
221 protected $householdsToSkip = [];
224 * Additional fields to return.
226 * This doesn't make much sense when we have a fields set but search build add it's own onto
227 * the 'Primary fields' (all) option.
231 protected $additionalRequestedReturnProperties = [];
234 * Get additional return properties.
238 public function getAdditionalRequestedReturnProperties() {
239 return $this->additionalRequestedReturnProperties
;
243 * Set additional return properties.
245 * @param array $value
247 public function setAdditionalRequestedReturnProperties($value) {
249 if (!empty($value['group'])) {
250 unset($value['group']);
251 $value['groups'] = 1;
253 $this->additionalRequestedReturnProperties
= $value;
257 * Get return properties by relationship.
260 public function getRelationshipReturnProperties() {
261 return $this->relationshipReturnProperties
;
265 * Export values for related contacts.
269 protected $relatedContactValues = [];
274 protected $returnProperties = [];
279 protected $outputSpecification = [];
284 protected $componentTable = '';
289 public function getComponentTable() {
290 return $this->componentTable
;
294 * Set the component table (if any).
296 * @param string $componentTable
298 public function setComponentTable($componentTable) {
299 $this->componentTable
= $componentTable;
303 * Clause from component search.
307 protected $componentClause = '';
312 public function getComponentClause() {
313 return $this->componentClause
;
317 * @param string $componentClause
319 public function setComponentClause($componentClause) {
320 $this->componentClause
= $componentClause;
324 * Name of a temporary table created to hold the results.
326 * Current decision making on when to create a temp table is kinda bad so this might change
327 * a bit as it is reviewed but basically we need a temp table or similar to calculate merging
328 * addresses. Merging households is handled in php. We create a temp table even when we don't need them.
332 protected $temporaryTable;
337 public function getTemporaryTable(): string {
338 return $this->temporaryTable
;
342 * @param string $temporaryTable
344 public function setTemporaryTable(string $temporaryTable) {
345 $this->temporaryTable
= $temporaryTable;
348 protected $postalGreetingTemplate;
353 public function getPostalGreetingTemplate() {
354 return $this->postalGreetingTemplate
;
358 * @param mixed $postalGreetingTemplate
360 public function setPostalGreetingTemplate($postalGreetingTemplate) {
361 $this->postalGreetingTemplate
= $postalGreetingTemplate;
367 public function getAddresseeGreetingTemplate() {
368 return $this->addresseeGreetingTemplate
;
372 * @param mixed $addresseeGreetingTemplate
374 public function setAddresseeGreetingTemplate($addresseeGreetingTemplate) {
375 $this->addresseeGreetingTemplate
= $addresseeGreetingTemplate;
378 protected $addresseeGreetingTemplate;
381 * CRM_Export_BAO_ExportProcessor constructor.
383 * @param int $exportMode
384 * @param array|null $requestedFields
385 * @param string $queryOperator
386 * @param bool $isMergeSameHousehold
387 * @param bool $isPostalableOnly
388 * @param bool $isMergeSameAddress
389 * @param array $formValues
390 * Values from the export options form on contact export. We currently support these keys
393 * - addresee_greeting
396 public function __construct($exportMode, $requestedFields, $queryOperator, $isMergeSameHousehold = FALSE, $isPostalableOnly = FALSE, $isMergeSameAddress = FALSE, $formValues = []) {
397 $this->setExportMode($exportMode);
398 $this->setQueryMode();
399 $this->setQueryOperator($queryOperator);
400 $this->setRequestedFields($requestedFields);
401 $this->setRelationshipTypes();
402 $this->setIsMergeSameHousehold($isMergeSameHousehold ||
$isMergeSameAddress);
403 $this->setIsPostalableOnly($isPostalableOnly);
404 $this->setIsMergeSameAddress($isMergeSameAddress);
405 $this->setReturnProperties($this->determineReturnProperties());
406 $this->setAdditionalFieldsForSameAddressMerge();
407 $this->setAdditionalFieldsForPostalExport();
408 $this->setHouseholdMergeReturnProperties();
409 $this->setGreetingStringsForSameAddressMerge($formValues);
410 $this->setGreetingOptions();
414 * Set the greeting options, if relevant.
416 public function setGreetingOptions() {
417 if ($this->isMergeSameAddress()) {
418 $this->greetingOptions
['addressee'] = CRM_Core_OptionGroup
::values('addressee');
419 $this->greetingOptions
['postal_greeting'] = CRM_Core_OptionGroup
::values('postal_greeting');
426 public function isPostalableOnly() {
427 return $this->isPostalableOnly
;
431 * @param bool $isPostalableOnly
433 public function setIsPostalableOnly($isPostalableOnly) {
434 $this->isPostalableOnly
= $isPostalableOnly;
440 public function getRequestedFields() {
441 return empty($this->requestedFields
) ?
NULL : $this->requestedFields
;
445 * @param array|null $requestedFields
447 public function setRequestedFields($requestedFields) {
448 $this->requestedFields
= $requestedFields;
454 public function getReturnProperties() {
455 return array_merge($this->returnProperties
, $this->getAdditionalRequestedReturnProperties(), $this->getAdditionalFieldsForSameAddressMerge(), $this->getAdditionalFieldsForPostalExport());
459 * @param array $returnProperties
461 public function setReturnProperties($returnProperties) {
462 $this->returnProperties
= $returnProperties;
468 public function getRelationshipTypes() {
469 return $this->relationshipTypes
;
474 public function setRelationshipTypes() {
475 $this->relationshipTypes
= CRM_Contact_BAO_Relationship
::getContactRelationshipType(
487 * Set the value for a relationship type field.
489 * In this case we are building up an array of properties for a related contact.
491 * These may be used for direct exporting or for merge to household depending on the
494 * @param string $relationshipType
495 * @param int $contactID
496 * @param string $field
497 * @param string $value
499 public function setRelationshipValue($relationshipType, $contactID, $field, $value) {
500 $this->relatedContactValues
[$relationshipType][$contactID][$field] = $value;
501 if ($field === 'id' && $this->isHouseholdMergeRelationshipTypeKey($relationshipType)) {
502 $this->householdsToSkip
[] = $value;
507 * Get the value for a relationship type field.
509 * In this case we are building up an array of properties for a related contact.
511 * These may be used for direct exporting or for merge to household depending on the
514 * @param string $relationshipType
515 * @param int $contactID
516 * @param string $field
520 public function getRelationshipValue($relationshipType, $contactID, $field) {
521 return $this->relatedContactValues
[$relationshipType][$contactID][$field] ??
'';
525 * Get the id of the related household.
527 * @param int $contactID
528 * @param string $relationshipType
532 public function getRelatedHouseholdID($contactID, $relationshipType) {
533 return $this->relatedContactValues
[$relationshipType][$contactID]['id'];
537 * Has the household already been exported.
539 * @param int $housholdContactID
543 public function isHouseholdExported($housholdContactID) {
544 return isset($this->exportedHouseholds
[$housholdContactID]);
551 public function isMergeSameHousehold() {
552 return $this->isMergeSameHousehold
;
556 * @param bool $isMergeSameHousehold
558 public function setIsMergeSameHousehold($isMergeSameHousehold) {
559 $this->isMergeSameHousehold
= $isMergeSameHousehold;
563 * Return relationship types for household merge.
567 public function getHouseholdRelationshipTypes() {
568 if (!$this->isMergeSameHousehold()) {
572 CRM_Utils_Array
::key('Household Member of', $this->getRelationshipTypes()),
573 CRM_Utils_Array
::key('Head of Household for', $this->getRelationshipTypes()),
581 public function isRelationshipTypeKey($fieldName) {
582 return array_key_exists($fieldName, $this->relationshipTypes
);
589 public function isHouseholdMergeRelationshipTypeKey($fieldName) {
590 return in_array($fieldName, $this->getHouseholdRelationshipTypes());
596 public function getQueryOperator() {
597 return $this->queryOperator
;
601 * @param string $queryOperator
603 public function setQueryOperator($queryOperator) {
604 $this->queryOperator
= $queryOperator;
610 public function getIds() {
617 public function setIds($ids) {
624 public function getQueryFields() {
627 $this->getComponentPaymentFields()
632 * @param array $queryFields
634 public function setQueryFields($queryFields) {
635 // legacy hacks - we add these to queryFields because this
636 // pseudometadata is currently required.
637 $queryFields['im_provider']['pseudoconstant']['var'] = 'imProviders';
638 $queryFields['country']['context'] = 'country';
639 $queryFields['world_region']['context'] = 'country';
640 $queryFields['state_province']['context'] = 'province';
641 $queryFields['contact_id'] = ['title' => ts('Contact ID'), 'type' => CRM_Utils_Type
::T_INT
];
642 $this->queryFields
= $queryFields;
648 public function getQueryMode() {
649 return $this->queryMode
;
653 * Set the query mode based on the export mode.
655 public function setQueryMode() {
657 switch ($this->getExportMode()) {
658 case CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
:
659 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
;
662 case CRM_Export_Form_Select
::EVENT_EXPORT
:
663 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_EVENT
;
666 case CRM_Export_Form_Select
::MEMBER_EXPORT
:
667 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_MEMBER
;
670 case CRM_Export_Form_Select
::PLEDGE_EXPORT
:
671 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_PLEDGE
;
674 case CRM_Export_Form_Select
::CASE_EXPORT
:
675 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_CASE
;
678 case CRM_Export_Form_Select
::GRANT_EXPORT
:
679 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_GRANT
;
682 case CRM_Export_Form_Select
::ACTIVITY_EXPORT
:
683 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_ACTIVITY
;
687 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_CONTACTS
;
694 public function getExportMode() {
695 return $this->exportMode
;
699 * @param int $exportMode
701 public function setExportMode($exportMode) {
702 $this->exportMode
= $exportMode;
706 * Get the name for the export file.
710 public function getExportFileName() {
711 switch ($this->getExportMode()) {
712 case CRM_Export_Form_Select
::CONTACT_EXPORT
:
713 return ts('CiviCRM Contact Search');
715 case CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
:
716 return ts('CiviCRM Contribution Search');
718 case CRM_Export_Form_Select
::MEMBER_EXPORT
:
719 return ts('CiviCRM Member Search');
721 case CRM_Export_Form_Select
::EVENT_EXPORT
:
722 return ts('CiviCRM Participant Search');
724 case CRM_Export_Form_Select
::PLEDGE_EXPORT
:
725 return ts('CiviCRM Pledge Search');
727 case CRM_Export_Form_Select
::CASE_EXPORT
:
728 return ts('CiviCRM Case Search');
730 case CRM_Export_Form_Select
::GRANT_EXPORT
:
731 return ts('CiviCRM Grant Search');
733 case CRM_Export_Form_Select
::ACTIVITY_EXPORT
:
734 return ts('CiviCRM Activity Search');
737 // Legacy code suggests the value could be 'financial' - ie. something
738 // other than what should be accepted. However, I suspect that this line is
740 return ts('CiviCRM Search');
745 * Get the label for the header row based on the field to output.
747 * @param string $field
751 public function getHeaderForRow($field) {
752 if (substr($field, -11) === 'campaign_id') {
753 // @todo - set this correctly in the xml rather than here.
754 // This will require a generalised handling cleanup
755 return ts('Campaign ID');
757 if ($this->isMergeSameHousehold() && !$this->isMergeSameAddress() && $field === 'id') {
758 // This is weird - even if we are merging households not every contact in the export is a household so this would not be accurate.
759 return ts('Household ID');
761 elseif (isset($this->getQueryFields()[$field]['title'])) {
762 return $this->getQueryFields()[$field]['title'];
764 elseif ($this->isExportPaymentFields() && array_key_exists($field, $this->getcomponentPaymentFields())) {
765 return CRM_Utils_Array
::value($field, $this->getcomponentPaymentFields())['title'];
778 public function runQuery($params, $order) {
779 $returnProperties = $this->getReturnProperties();
780 $params = array_merge($params, $this->getWhereParams());
782 $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL,
783 FALSE, FALSE, $this->getQueryMode(),
784 FALSE, TRUE, TRUE, NULL, $this->getQueryOperator(),
790 $query->_sort
= $order;
791 list($select, $from, $where, $having) = $query->query();
792 $this->setQueryFields($query->_fields
);
793 $whereClauses = ['trash_clause' => "contact_a.is_deleted != 1"];
794 if ($this->getRequestedFields() && ($this->getComponentTable())) {
795 $from .= " INNER JOIN " . $this->getComponentTable() . " ctTable ON ctTable.contact_id = contact_a.id ";
797 elseif ($this->getComponentClause()) {
798 $whereClauses[] = $this->getComponentClause();
801 // CRM-13982 - check if is deleted
802 foreach ($params as $value) {
803 if ($value[0] == 'contact_is_deleted') {
804 unset($whereClauses['trash_clause']);
808 if ($this->isPostalableOnly
) {
809 if (array_key_exists('street_address', $returnProperties)) {
810 $addressWhere = " civicrm_address.street_address <> ''";
811 if (array_key_exists('supplemental_address_1', $returnProperties)) {
812 // We need this to be an OR rather than AND on the street_address so, hack it in.
813 $addressOptions = CRM_Core_BAO_Setting
::valueOptions(CRM_Core_BAO_Setting
::SYSTEM_PREFERENCES_NAME
,
814 'address_options', TRUE, NULL, TRUE
816 if (!empty($addressOptions['supplemental_address_1'])) {
817 $addressWhere .= " OR civicrm_address.supplemental_address_1 <> ''";
820 $whereClauses['address'] = '(' . $addressWhere . ')';
825 $where = "WHERE " . implode(' AND ', $whereClauses);
828 $where .= " AND " . implode(' AND ', $whereClauses);
831 $groupBy = $this->getGroupBy($query);
832 $queryString = "$select $from $where $having $groupBy";
834 // always add contact_a.id to the ORDER clause
835 // so the order is deterministic
837 if (strpos('contact_a.id', $order) === FALSE) {
838 $order .= ", contact_a.id";
841 list($field, $dir) = explode(' ', $order, 2);
842 $field = trim($field);
843 if (!empty($this->getReturnProperties()[$field])) {
845 $queryString .= " ORDER BY $order";
848 return [$query, $queryString];
852 * Add a row to the specification for how to output data.
855 * @param string $relationshipType
856 * @param string $locationType
857 * @param int $entityTypeID phone_type_id or provider_id for phone or im fields.
859 public function addOutputSpecification($key, $relationshipType = NULL, $locationType = NULL, $entityTypeID = NULL) {
862 if ($key === 'phone') {
863 $entityLabel = CRM_Core_PseudoConstant
::getLabel('CRM_Core_BAO_Phone', 'phone_type_id', $entityTypeID);
866 $entityLabel = CRM_Core_PseudoConstant
::getLabel('CRM_Core_BAO_IM', 'provider_id', $entityTypeID);
870 // These oddly constructed keys are for legacy reasons. Altering them will affect test success
871 // but in time it may be good to rationalise them.
872 $label = $this->getOutputSpecificationLabel($key, $relationshipType, $locationType, $entityLabel);
873 $index = $this->getOutputSpecificationIndex($key, $relationshipType, $locationType, $entityLabel);
874 $fieldKey = $this->getOutputSpecificationFieldKey($key, $relationshipType, $locationType, $entityLabel);
876 $this->outputSpecification
[$index]['header'] = $label;
877 $this->outputSpecification
[$index]['sql_columns'] = $this->getSqlColumnDefinition($fieldKey, $key);
879 if ($relationshipType && $this->isHouseholdMergeRelationshipTypeKey($relationshipType)) {
880 $this->setColumnAsCalculationOnly($index);
882 $this->outputSpecification
[$index]['metadata'] = $this->getMetaDataForField($key);
886 * Get the metadata for the given field.
892 public function getMetaDataForField($key) {
893 $mappings = ['contact_id' => 'id'];
894 if (isset($this->getQueryFields()[$key])) {
895 return $this->getQueryFields()[$key];
897 if (isset($mappings[$key])) {
898 return $this->getQueryFields()[$mappings[$key]];
906 public function setSqlColumnDefn($key) {
907 $this->outputSpecification
[$this->getMungedFieldName($key)]['sql_columns'] = $this->getSqlColumnDefinition($key, $this->getMungedFieldName($key));
911 * Mark a column as only required for calculations.
913 * Do not include the row with headers.
915 * @param string $column
917 public function setColumnAsCalculationOnly($column) {
918 $this->outputSpecification
[$column]['do_not_output_to_csv'] = TRUE;
924 public function getHeaderRows() {
926 foreach ($this->outputSpecification
as $key => $spec) {
927 if (empty($spec['do_not_output_to_csv'])) {
928 $headerRows[] = $spec['header'];
937 public function getSQLColumns() {
939 foreach ($this->outputSpecification
as $key => $spec) {
940 if (empty($spec['do_not_output_to_sql'])) {
941 $sqlColumns[$key] = $spec['sql_columns'];
950 public function getMetadata() {
952 foreach ($this->outputSpecification
as $key => $spec) {
953 $metadata[$key] = $spec['metadata'];
959 * Build the row for output.
961 * @param \CRM_Contact_BAO_Query $query
962 * @param CRM_Core_DAO $iterationDAO
963 * @param array $outputColumns
965 * @param $paymentDetails
966 * @param $addPaymentHeader
970 public function buildRow($query, $iterationDAO, $outputColumns, $metadata, $paymentDetails, $addPaymentHeader) {
971 $paymentTableId = $this->getPaymentTableID();
972 if ($this->isHouseholdToSkip($iterationDAO->contact_id
)) {
975 $phoneTypes = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_Phone', 'phone_type_id');
976 $imProviders = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_IM', 'provider_id');
979 $householdMergeRelationshipType = $this->getHouseholdMergeTypeForRow($iterationDAO->contact_id
);
980 if ($householdMergeRelationshipType) {
981 $householdID = $this->getRelatedHouseholdID($iterationDAO->contact_id
, $householdMergeRelationshipType);
982 if ($this->isHouseholdExported($householdID)) {
985 foreach (array_keys($outputColumns) as $column) {
986 $row[$column] = $this->getRelationshipValue($householdMergeRelationshipType, $iterationDAO->contact_id
, $column);
988 $this->markHouseholdExported($householdID);
992 $query->convertToPseudoNames($iterationDAO);
994 //first loop through output columns so that we return what is required, and in same order.
995 foreach ($outputColumns as $field => $value) {
996 // add im_provider to $dao object
997 if ($field == 'im_provider' && property_exists($iterationDAO, 'provider_id')) {
998 $iterationDAO->im_provider
= $iterationDAO->provider_id
;
1001 //build row values (data)
1003 if (property_exists($iterationDAO, $field)) {
1004 $fieldValue = $iterationDAO->$field;
1005 // to get phone type from phone type id
1006 if ($field == 'phone_type_id' && isset($phoneTypes[$fieldValue])) {
1007 $fieldValue = $phoneTypes[$fieldValue];
1009 elseif ($field == 'provider_id' ||
$field == 'im_provider') {
1010 $fieldValue = $imProviders[$fieldValue] ??
NULL;
1012 elseif (strstr($field, 'master_id')) {
1013 // @todo - why not just $field === 'master_id' - what else would it be?
1014 $masterAddressId = $iterationDAO->$field ??
NULL;
1015 // get display name of contact that address is shared.
1016 $fieldValue = CRM_Contact_BAO_Contact
::getMasterDisplayName($masterAddressId);
1020 if ($this->isRelationshipTypeKey($field)) {
1021 $this->buildRelationshipFieldsForRow($row, $iterationDAO->contact_id
, $value, $field);
1024 $row[$field] = $this->getTransformedFieldValue($field, $iterationDAO, $fieldValue, $metadata, $paymentDetails);
1028 // If specific payment fields have been selected for export, payment
1029 // data will already be in $row. Otherwise, add payment related
1030 // information, if appropriate.
1031 if ($addPaymentHeader) {
1032 if (!$this->isExportSpecifiedPaymentFields()) {
1033 $nullContributionDetails = array_fill_keys(array_keys($this->getPaymentHeaders()), NULL);
1034 if ($this->isExportPaymentFields()) {
1035 $paymentData = $paymentDetails[$row[$paymentTableId]] ??
NULL;
1036 if (!is_array($paymentData) ||
empty($paymentData)) {
1037 $paymentData = $nullContributionDetails;
1039 $row = array_merge($row, $paymentData);
1041 elseif (!empty($paymentDetails)) {
1042 $row = array_merge($row, $nullContributionDetails);
1046 //remove organization name for individuals if it is set for current employer
1047 if (!empty($row['contact_type']) &&
1048 $row['contact_type'] == 'Individual' && array_key_exists('organization_name', $row)
1050 $row['organization_name'] = '';
1056 * If this row has a household whose details we should use get the relationship type key.
1062 public function getHouseholdMergeTypeForRow($contactID) {
1063 if (!$this->isMergeSameHousehold()) {
1066 foreach ($this->getHouseholdRelationshipTypes() as $relationshipType) {
1067 if (isset($this->relatedContactValues
[$relationshipType][$contactID])) {
1068 return $relationshipType;
1074 * Mark the given household as already exported.
1076 * @param $householdID
1078 public function markHouseholdExported($householdID) {
1079 $this->exportedHouseholds
[$householdID] = $householdID;
1084 * @param $iterationDAO
1085 * @param $fieldValue
1087 * @param $paymentDetails
1091 public function getTransformedFieldValue($field, $iterationDAO, $fieldValue, $metadata, $paymentDetails) {
1093 $i18n = CRM_Core_I18n
::singleton();
1094 if ($field == 'id') {
1095 return $iterationDAO->contact_id
;
1096 // special case for calculated field
1098 elseif ($field == 'source_contact_id') {
1099 return $iterationDAO->contact_id
;
1101 elseif ($field == 'pledge_balance_amount') {
1102 return $iterationDAO->pledge_amount
- $iterationDAO->pledge_total_paid
;
1103 // special case for calculated field
1105 elseif ($field == 'pledge_next_pay_amount') {
1106 return $iterationDAO->pledge_next_pay_amount +
$iterationDAO->pledge_outstanding_amount
;
1108 elseif (isset($fieldValue) &&
1111 //check for custom data
1112 if ($cfID = CRM_Core_BAO_CustomField
::getKeyID($field)) {
1113 $html_type = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_CustomField', $cfID, 'html_type');
1115 //need to calculate the link to the file for file custom data
1116 if ($html_type === 'File' && $fieldValue) {
1117 $result = civicrm_api3('attachment', 'get', ['return' => ['url'], 'id' => $fieldValue]);
1118 return $result['values'][$result['id']]['url'];
1121 return CRM_Core_BAO_CustomField
::displayValue($fieldValue, $cfID);
1123 elseif (in_array($field, [
1128 //special case for greeting replacement
1129 $fldValue = "{$field}_display";
1130 return $iterationDAO->$fldValue;
1133 //normal fields with a touch of CRM-3157
1136 case 'world_region':
1137 return $i18n->crm_translate($fieldValue, ['context' => 'country']);
1139 case 'state_province':
1140 return $i18n->crm_translate($fieldValue, ['context' => 'province']);
1143 case 'preferred_communication_method':
1144 case 'preferred_mail_format':
1145 case 'communication_style':
1146 return $i18n->crm_translate($fieldValue);
1149 if (isset($metadata[$field])) {
1150 // No I don't know why we do it this way & whether we could
1151 // make better use of pseudoConstants.
1152 if (!empty($metadata[$field]['context'])) {
1153 return $i18n->crm_translate($fieldValue, $metadata[$field]);
1155 if (!empty($metadata[$field]['pseudoconstant'])) {
1156 if (!empty($metadata[$field]['bao'])) {
1157 return CRM_Core_PseudoConstant
::getLabel($metadata[$field]['bao'], $metadata[$field]['name'], $fieldValue);
1159 // This is not our normal syntax for pseudoconstants but I am a bit loath to
1160 // call an external function until sure it is not increasing php processing given this
1161 // may be iterated 100,000 times & we already have the $imProvider var loaded.
1162 // That can be next refactor...
1163 // Yes - definitely feeling hatred for this bit of code - I know you will beat me up over it's awfulness
1164 // but I have to reach a stable point....
1165 $varName = $metadata[$field]['pseudoconstant']['var'];
1166 if ($varName === 'imProviders') {
1167 return CRM_Core_PseudoConstant
::getLabel('CRM_Core_DAO_IM', 'provider_id', $fieldValue);
1169 if ($varName === 'phoneTypes') {
1170 return CRM_Core_PseudoConstant
::getLabel('CRM_Core_DAO_Phone', 'phone_type_id', $fieldValue);
1179 elseif ($this->isExportSpecifiedPaymentFields() && array_key_exists($field, $this->getcomponentPaymentFields())) {
1180 $paymentTableId = $this->getPaymentTableID();
1181 $paymentData = $paymentDetails[$iterationDAO->$paymentTableId] ??
NULL;
1183 'componentPaymentField_total_amount' => 'total_amount',
1184 'componentPaymentField_contribution_status' => 'contribution_status',
1185 'componentPaymentField_payment_instrument' => 'pay_instru',
1186 'componentPaymentField_transaction_id' => 'trxn_id',
1187 'componentPaymentField_received_date' => 'receive_date',
1189 return CRM_Utils_Array
::value($payFieldMapper[$field], $paymentData, '');
1192 // if field is empty or null
1198 * Get array of fields to return, over & above those defined in the main contact exportable fields.
1200 * These include export mode specific fields & some fields apparently required as 'exportableFields'
1201 * but not returned by the function of the same name.
1204 * Array of fields to return in the format ['field_name' => 1,...]
1206 public function getAdditionalReturnProperties() {
1207 if ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
1208 $componentSpecificFields = [];
1211 $componentSpecificFields = CRM_Contact_BAO_Query
::defaultReturnProperties($this->getQueryMode());
1213 if ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_PLEDGE
) {
1214 $componentSpecificFields = array_merge($componentSpecificFields, CRM_Pledge_BAO_Query
::extraReturnProperties($this->getQueryMode()));
1215 unset($componentSpecificFields['contribution_status_id']);
1216 unset($componentSpecificFields['pledge_status_id']);
1217 unset($componentSpecificFields['pledge_payment_status_id']);
1219 if ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_CASE
) {
1220 $componentSpecificFields = array_merge($componentSpecificFields, CRM_Case_BAO_Query
::extraReturnProperties($this->getQueryMode()));
1222 if ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
1223 $componentSpecificFields = array_merge($componentSpecificFields, CRM_Contribute_BAO_Query
::softCreditReturnProperties(TRUE));
1224 unset($componentSpecificFields['contribution_status_id']);
1226 return $componentSpecificFields;
1230 * Should payment fields be appended to the export.
1232 * (This is pretty hacky so hopefully this function won't last long - notice
1233 * how obviously it should be part of the above function!).
1235 public function isExportPaymentFields() {
1236 if ($this->getRequestedFields() === NULL
1237 && in_array($this->getQueryMode(), [
1238 CRM_Contact_BAO_Query
::MODE_EVENT
,
1239 CRM_Contact_BAO_Query
::MODE_MEMBER
,
1240 CRM_Contact_BAO_Query
::MODE_PLEDGE
,
1244 elseif ($this->isExportSpecifiedPaymentFields()) {
1251 * Has specific payment fields been requested (as opposed to via all fields).
1253 * If specific fields have been requested then they get added at various points.
1257 public function isExportSpecifiedPaymentFields() {
1258 if ($this->getRequestedFields() !== NULL && $this->hasRequestedComponentPaymentFields()) {
1264 * Get the name of the id field in the table that connects contributions to the export entity.
1266 public function getPaymentTableID() {
1267 if ($this->getRequestedFields() === NULL) {
1269 CRM_Contact_BAO_Query
::MODE_EVENT
=> 'participant_id',
1270 CRM_Contact_BAO_Query
::MODE_MEMBER
=> 'membership_id',
1271 CRM_Contact_BAO_Query
::MODE_PLEDGE
=> 'pledge_payment_id',
1273 return isset($mapping[$this->getQueryMode()]) ?
$mapping[$this->getQueryMode()] : '';
1275 elseif ($this->hasRequestedComponentPaymentFields()) {
1276 return 'participant_id';
1282 * Have component payment fields been requested.
1286 protected function hasRequestedComponentPaymentFields() {
1287 if ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_EVENT
) {
1288 $participantPaymentFields = array_intersect_key($this->getComponentPaymentFields(), $this->getReturnProperties());
1289 if (!empty($participantPaymentFields)) {
1297 * Get fields that indicate payment fields have been requested for a component.
1299 * Ideally this should be protected but making it temporarily public helps refactoring..
1303 public function getComponentPaymentFields() {
1305 'componentPaymentField_total_amount' => ['title' => ts('Total Amount'), 'type' => CRM_Utils_Type
::T_MONEY
],
1306 'componentPaymentField_contribution_status' => ['title' => ts('Contribution Status'), 'type' => CRM_Utils_Type
::T_STRING
],
1307 'componentPaymentField_received_date' => ['title' => ts('Date Received'), 'type' => CRM_Utils_Type
::T_DATE + CRM_Utils_Type
::T_TIME
],
1308 'componentPaymentField_payment_instrument' => ['title' => ts('Payment Method'), 'type' => CRM_Utils_Type
::T_STRING
],
1309 'componentPaymentField_transaction_id' => ['title' => ts('Transaction ID'), 'type' => CRM_Utils_Type
::T_STRING
],
1314 * Get headers for payment fields.
1316 * Returns an array of contribution fields when the entity supports payment fields and specific fields
1317 * are not specified. This is a transitional function for refactoring legacy code.
1319 public function getPaymentHeaders() {
1320 if ($this->isExportPaymentFields() && !$this->isExportSpecifiedPaymentFields()) {
1321 return CRM_Utils_Array
::collect('title', $this->getcomponentPaymentFields());
1327 * Get the default properties when not specified.
1329 * In the UI this appears as 'Primary fields only' but in practice it's
1330 * most of the kitchen sink and the hallway closet thrown in.
1332 * Since CRM-952 custom fields are excluded, but no other form of mercy is shown.
1336 public function getDefaultReturnProperties() {
1337 $returnProperties = [];
1338 $fields = CRM_Contact_BAO_Contact
::exportableFields('All', TRUE, TRUE);
1339 $skippedFields = ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_CONTACTS
) ?
[] : [
1345 foreach ($fields as $key => $var) {
1346 if ($key && (substr($key, 0, 6) != 'custom') && !in_array($key, $skippedFields)) {
1347 $returnProperties[$key] = 1;
1350 $returnProperties = array_merge($returnProperties, $this->getAdditionalReturnProperties());
1351 return $returnProperties;
1355 * Add the field to relationship return properties & return it.
1357 * This function is doing both setting & getting which is yuck but it is an interim
1360 * @param array $value
1361 * @param string $relationshipKey
1365 public function setRelationshipReturnProperties($value, $relationshipKey) {
1366 $relationField = $value['name'];
1367 $relIMProviderId = NULL;
1368 $relLocTypeId = $value['location_type_id'] ??
NULL;
1369 $locationName = CRM_Core_PseudoConstant
::getName('CRM_Core_BAO_Address', 'location_type_id', $relLocTypeId);
1370 $relPhoneTypeId = CRM_Utils_Array
::value('phone_type_id', $value, ($locationName ?
'Primary' : NULL));
1371 $relIMProviderId = CRM_Utils_Array
::value('im_provider_id', $value, ($locationName ?
'Primary' : NULL));
1372 if (in_array($relationField, $this->getValidLocationFields()) && $locationName) {
1373 if ($relationField === 'phone') {
1374 $this->relationshipReturnProperties
[$relationshipKey]['location'][$locationName]['phone-' . $relPhoneTypeId] = 1;
1376 elseif ($relationField === 'im') {
1377 $this->relationshipReturnProperties
[$relationshipKey]['location'][$locationName]['im-' . $relIMProviderId] = 1;
1380 $this->relationshipReturnProperties
[$relationshipKey]['location'][$locationName][$relationField] = 1;
1384 $this->relationshipReturnProperties
[$relationshipKey][$relationField] = 1;
1386 return $this->relationshipReturnProperties
[$relationshipKey];
1390 * Add the main return properties to the household merge properties if needed for merging.
1392 * If we are using household merge we need to add these to the relationship properties to
1395 public function setHouseholdMergeReturnProperties() {
1396 if ($this->isMergeSameHousehold()) {
1397 $returnProperties = $this->getReturnProperties();
1398 $returnProperties = array_diff_key($returnProperties, array_fill_keys(['location_type', 'im_provider'], 1));
1399 foreach ($this->getHouseholdRelationshipTypes() as $householdRelationshipType) {
1400 $this->relationshipReturnProperties
[$householdRelationshipType] = $returnProperties;
1406 * Get the default location fields to request.
1410 public function getValidLocationFields() {
1413 'supplemental_address_1',
1414 'supplemental_address_2',
1415 'supplemental_address_3',
1418 'postal_code_suffix',
1430 * Get the sql column definition for the given field.
1432 * @param string $fieldName
1433 * @param string $columnName
1437 public function getSqlColumnDefinition($fieldName, $columnName) {
1439 // early exit for master_id, CRM-12100
1440 // in the DB it is an ID, but in the export, we retrive the display_name of the master record
1441 // also for current_employer, CRM-16939
1442 if ($columnName == 'master_id' ||
$columnName == 'current_employer') {
1443 return "`$fieldName` varchar(128)";
1446 $queryFields = $this->getQueryFields();
1447 // @todo remove the enotice avoidance here, ensure all columns are declared.
1448 // tests will fail on the enotices until they all are & then all the 'else'
1450 $fieldSpec = $queryFields[$columnName] ??
[];
1452 // set the sql columns
1453 if (isset($fieldSpec['type'])) {
1454 switch ($fieldSpec['type']) {
1455 case CRM_Utils_Type
::T_INT
:
1456 case CRM_Utils_Type
::T_BOOLEAN
:
1457 if (in_array(CRM_Utils_Array
::value('data_type', $fieldSpec), ['Country', 'StateProvince', 'ContactReference'])) {
1458 return "`$fieldName` varchar(255)";
1460 return "`$fieldName` varchar(16)";
1462 case CRM_Utils_Type
::T_STRING
:
1463 if (isset($queryFields[$columnName]['maxlength'])) {
1464 return "`$fieldName` varchar({$queryFields[$columnName]['maxlength']})";
1467 return "`$fieldName` varchar(255)";
1470 case CRM_Utils_Type
::T_TEXT
:
1471 case CRM_Utils_Type
::T_LONGTEXT
:
1472 case CRM_Utils_Type
::T_BLOB
:
1473 case CRM_Utils_Type
::T_MEDIUMBLOB
:
1474 return "`$fieldName` longtext";
1476 case CRM_Utils_Type
::T_FLOAT
:
1477 case CRM_Utils_Type
::T_ENUM
:
1478 case CRM_Utils_Type
::T_DATE
:
1479 case CRM_Utils_Type
::T_TIME
:
1480 case CRM_Utils_Type
::T_TIMESTAMP
:
1481 case CRM_Utils_Type
::T_MONEY
:
1482 case CRM_Utils_Type
::T_EMAIL
:
1483 case CRM_Utils_Type
::T_URL
:
1484 case CRM_Utils_Type
::T_CCNUM
:
1486 return "`$fieldName` varchar(32)";
1490 if (substr($fieldName, -3, 3) == '_id') {
1491 return "`$fieldName` varchar(255)";
1493 elseif (substr($fieldName, -5, 5) == '_note') {
1494 return "`$fieldName` text";
1503 if (in_array($fieldName, $changeFields)) {
1504 return "`$fieldName` text";
1507 // set the sql columns for custom data
1508 if (isset($queryFields[$columnName]['data_type'])) {
1510 switch ($queryFields[$columnName]['data_type']) {
1512 // May be option labels, which could be up to 512 characters
1513 $length = max(512, CRM_Utils_Array
::value('text_length', $queryFields[$columnName]));
1514 return "`$fieldName` varchar($length)";
1517 return "`$fieldName` varchar(255)";
1520 return "`$fieldName` text";
1523 return "`$fieldName` varchar(255)";
1527 return "`$fieldName` text";
1535 * Get the munged field name.
1537 * @param string $field
1540 public function getMungedFieldName($field) {
1541 $fieldName = CRM_Utils_String
::munge(strtolower($field), '_', 64);
1542 if ($fieldName == 'id') {
1543 $fieldName = 'civicrm_primary_id';
1549 * In order to respect the history of this class we need to index kinda illogically.
1551 * On the bright side - this stuff is tested within a nano-byte of it's life.
1553 * e.g '2-a-b_Home-City'
1555 * @param string $key
1556 * @param string $relationshipType
1557 * @param string $locationType
1558 * @param $entityLabel
1562 protected function getOutputSpecificationIndex($key, $relationshipType, $locationType, $entityLabel) {
1563 if (!$relationshipType ||
$key !== 'id') {
1564 $key = $this->getMungedFieldName($key);
1566 return $this->getMungedFieldName(
1567 ($relationshipType ?
($relationshipType . '_') : '')
1568 . ($locationType ?
($locationType . '_') : '')
1570 . ($entityLabel ?
('_' . $entityLabel) : '')
1575 * Get the compiled label for the column.
1577 * e.g 'Gender', 'Employee Of-Home-city'
1579 * @param string $key
1580 * @param string $relationshipType
1581 * @param string $locationType
1582 * @param string $entityLabel
1586 protected function getOutputSpecificationLabel($key, $relationshipType, $locationType, $entityLabel) {
1587 return ($relationshipType ?
$this->getRelationshipTypes()[$relationshipType] . '-' : '')
1588 . ($locationType ?
$locationType . '-' : '')
1589 . $this->getHeaderForRow($key)
1590 . ($entityLabel ?
'-' . $entityLabel : '');
1594 * Get the mysql field name key.
1596 * This key is locked in by tests but the reasons for the specific conventions -
1597 * ie. headings are used for keying fields in some cases, are likely
1598 * accidental rather than deliberate.
1600 * This key is used for the output sql array.
1602 * @param string $key
1603 * @param $relationshipType
1604 * @param $locationType
1605 * @param $entityLabel
1609 protected function getOutputSpecificationFieldKey($key, $relationshipType, $locationType, $entityLabel) {
1610 if (!$relationshipType ||
$key !== 'id') {
1611 $key = $this->getMungedFieldName($key);
1613 $fieldKey = $this->getMungedFieldName(
1614 ($relationshipType ?
($relationshipType . '_') : '')
1615 . ($locationType ?
($locationType . '_') : '')
1617 . ($entityLabel ?
('_' . $entityLabel) : '')
1623 * Get params for the where criteria.
1627 public function getWhereParams() {
1628 if (!$this->isPostalableOnly()) {
1631 $params['is_deceased'] = ['is_deceased', '=', 0, CRM_Contact_BAO_Query
::MODE_CONTACTS
];
1632 $params['do_not_mail'] = ['do_not_mail', '=', 0, CRM_Contact_BAO_Query
::MODE_CONTACTS
];
1642 protected function buildRelationshipFieldsForRow(&$row, $contactID, $value, $field) {
1643 foreach (array_keys($value) as $property) {
1644 if ($property === 'location') {
1645 // @todo just undo all this nasty location wrangling!
1646 foreach ($value['location'] as $locationKey => $locationFields) {
1647 foreach (array_keys($locationFields) as $locationField) {
1648 $fieldKey = str_replace(' ', '_', $locationKey . '-' . $locationField);
1649 $row[$field . '_' . $fieldKey] = $this->getRelationshipValue($field, $contactID, $fieldKey);
1654 $row[$field . '_' . $property] = $this->getRelationshipValue($field, $contactID, $property);
1660 * Is this contact a household that is already set to be exported by virtue of it's household members.
1662 * @param int $contactID
1666 protected function isHouseholdToSkip($contactID) {
1667 return in_array($contactID, $this->householdsToSkip
);
1671 * Get the various arrays that we use to structure our output.
1673 * The extraction of these has been moved to a separate function for clarity and so that
1674 * tests can be added - in particular on the $outputHeaders array.
1676 * However it still feels a bit like something that I'm too polite to write down and this should be seen
1677 * as a step on the refactoring path rather than how it should be.
1680 * - outputColumns Array of columns to be exported. The values don't matter but the key must match the
1681 * alias for the field generated by BAO_Query object.
1682 * - headerRows Array of the column header strings to put in the csv header - non-associative.
1683 * - sqlColumns Array of column names for the temp table. Not too sure why outputColumns can't be used here.
1684 * - metadata Array of fields with specific parameters to pass to the translate function or another hacky nasty solution
1685 * I'm too embarassed to discuss here.
1687 * - to match the outputColumns keys (yes, the fact we ignore the output columns values & then pass another array with values
1688 * we could use does suggest further refactors. However, you future improver, do remember that every check you do
1689 * in the main DAO loop is done once per row & that coule be 100,000 times.)
1690 * Finally a pop quiz: We need the translate context because we use a function other than ts() - is this because
1691 * - a) the function used is more efficient or
1692 * - b) this code is old & outdated. Submit your answers to circular bin or better
1693 * yet find a way to comment them for posterity.
1695 public function getExportStructureArrays() {
1696 $outputColumns = $metadata = [];
1697 $queryFields = $this->getQueryFields();
1698 foreach ($this->getReturnProperties() as $key => $value) {
1699 if (($key != 'location' ||
!is_array($value)) && !$this->isRelationshipTypeKey($key)) {
1700 $outputColumns[$key] = $value;
1701 $this->addOutputSpecification($key);
1703 elseif ($this->isRelationshipTypeKey($key)) {
1704 $outputColumns[$key] = $value;
1705 foreach ($value as $relationField => $relationValue) {
1706 // below block is same as primary block (duplicate)
1707 if (isset($queryFields[$relationField]['title'])) {
1708 $this->addOutputSpecification($relationField, $key);
1710 elseif (is_array($relationValue) && $relationField == 'location') {
1711 // fix header for location type case
1712 foreach ($relationValue as $ltype => $val) {
1713 foreach (array_keys($val) as $fld) {
1714 $type = explode('-', $fld);
1715 $this->addOutputSpecification($type[0], $key, $ltype, CRM_Utils_Array
::value(1, $type));
1722 foreach ($value as $locationType => $locationFields) {
1723 foreach (array_keys($locationFields) as $locationFieldName) {
1724 $type = explode('-', $locationFieldName);
1726 $actualDBFieldName = $type[0];
1727 $daoFieldName = CRM_Utils_String
::munge($locationType) . '-' . $actualDBFieldName;
1729 if (!empty($type[1])) {
1730 $daoFieldName .= "-" . $type[1];
1732 $this->addOutputSpecification($actualDBFieldName, NULL, $locationType, CRM_Utils_Array
::value(1, $type));
1733 $metadata[$daoFieldName] = $this->getMetaDataForField($actualDBFieldName);
1734 $outputColumns[$daoFieldName] = TRUE;
1739 return [$outputColumns, $metadata];
1743 * Get default return property for export based on mode
1746 * Default Return property
1748 public function defaultReturnProperty() {
1749 // hack to add default return property based on export mode
1751 $exportMode = $this->getExportMode();
1752 if ($exportMode == CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
) {
1753 $property = 'contribution_id';
1755 elseif ($exportMode == CRM_Export_Form_Select
::EVENT_EXPORT
) {
1756 $property = 'participant_id';
1758 elseif ($exportMode == CRM_Export_Form_Select
::MEMBER_EXPORT
) {
1759 $property = 'membership_id';
1761 elseif ($exportMode == CRM_Export_Form_Select
::PLEDGE_EXPORT
) {
1762 $property = 'pledge_id';
1764 elseif ($exportMode == CRM_Export_Form_Select
::CASE_EXPORT
) {
1765 $property = 'case_id';
1767 elseif ($exportMode == CRM_Export_Form_Select
::GRANT_EXPORT
) {
1768 $property = 'grant_id';
1770 elseif ($exportMode == CRM_Export_Form_Select
::ACTIVITY_EXPORT
) {
1771 $property = 'activity_id';
1777 * Determine the required return properties from the input parameters.
1781 public function determineReturnProperties() {
1782 if ($this->getRequestedFields()) {
1783 $returnProperties = [];
1784 foreach ($this->getRequestedFields() as $key => $value) {
1785 $fieldName = $value['name'];
1786 $locationName = !empty($value['location_type_id']) ? CRM_Core_PseudoConstant
::getName('CRM_Core_BAO_Address', 'location_type_id', $value['location_type_id']) : NULL;
1787 $relationshipTypeKey = !empty($value['relationship_type_id']) ?
$value['relationship_type_id'] . '_' . $value['relationship_direction'] : NULL;
1788 if (!$fieldName ||
$this->isHouseholdMergeRelationshipTypeKey($relationshipTypeKey)) {
1792 if ($this->isRelationshipTypeKey($relationshipTypeKey)) {
1793 $returnProperties[$relationshipTypeKey] = $this->setRelationshipReturnProperties($value, $relationshipTypeKey);
1795 elseif ($locationName) {
1796 if ($fieldName === 'phone') {
1797 $returnProperties['location'][$locationName]['phone-' . $value['phone_type_id'] ??
NULL] = 1;
1799 elseif ($fieldName === 'im') {
1800 $returnProperties['location'][$locationName]['im-' . $value['im_provider_id'] ??
NULL] = 1;
1803 $returnProperties['location'][$locationName][$fieldName] = 1;
1807 //hack to fix component fields
1808 //revert mix of event_id and title
1809 if ($fieldName == 'event_id') {
1810 $returnProperties['event_id'] = 1;
1813 $returnProperties[$fieldName] = 1;
1817 $defaultExportMode = $this->defaultReturnProperty();
1818 if ($defaultExportMode) {
1819 $returnProperties[$defaultExportMode] = 1;
1823 $returnProperties = $this->getDefaultReturnProperties();
1825 if ($this->isMergeSameHousehold()) {
1826 $returnProperties['id'] = 1;
1828 if ($this->isMergeSameAddress()) {
1829 $returnProperties['addressee'] = 1;
1830 $returnProperties['postal_greeting'] = 1;
1831 $returnProperties['email_greeting'] = 1;
1832 $returnProperties['street_name'] = 1;
1833 $returnProperties['household_name'] = 1;
1834 $returnProperties['street_address'] = 1;
1835 $returnProperties['city'] = 1;
1836 $returnProperties['state_province'] = 1;
1839 return $returnProperties;
1843 * @param object $query
1844 * CRM_Contact_BAO_Query
1849 public function getGroupBy($query) {
1851 $returnProperties = $this->getReturnProperties();
1852 $exportMode = $this->getExportMode();
1853 $queryMode = $this->getQueryMode();
1854 if (!empty($returnProperties['tags']) ||
!empty($returnProperties['groups']) ||
1855 CRM_Utils_Array
::value('notes', $returnProperties) ||
1857 ($queryMode & CRM_Contact_BAO_Query
::MODE_CONTACTS
&& $query->_useGroupBy
)
1859 $groupBy = "contact_a.id";
1862 switch ($exportMode) {
1863 case CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
:
1864 $groupBy = 'civicrm_contribution.id';
1865 if (CRM_Contribute_BAO_Query
::isSoftCreditOptionEnabled()) {
1866 // especial group by when soft credit columns are included
1867 $groupBy = ['contribution_search_scredit_combined.id', 'contribution_search_scredit_combined.scredit_id'];
1871 case CRM_Export_Form_Select
::EVENT_EXPORT
:
1872 $groupBy = 'civicrm_participant.id';
1875 case CRM_Export_Form_Select
::MEMBER_EXPORT
:
1876 $groupBy = "civicrm_membership.id";
1880 if ($queryMode & CRM_Contact_BAO_Query
::MODE_ACTIVITY
) {
1881 $groupBy = "civicrm_activity.id ";
1884 return $groupBy ?
' GROUP BY ' . implode(', ', (array) $groupBy) : '';
1888 * @param int $contactId
1892 public function replaceMergeTokens($contactId) {
1897 'postal_greeting' => $this->getPostalGreetingTemplate(),
1898 'addressee' => $this->getAddresseeGreetingTemplate(),
1900 foreach ($greetingFields as $greeting => $greetingLabel) {
1901 $tokens = CRM_Utils_Token
::getTokens($greetingLabel);
1902 if (!empty($tokens)) {
1903 if (empty($contact)) {
1908 $contact = civicrm_api('contact', 'get', $values);
1910 if (!empty($contact['is_error'])) {
1913 $contact = $contact['values'][$contact['id']];
1916 $tokens = ['contact' => $greetingLabel];
1917 $greetings[$greeting] = CRM_Utils_Token
::replaceContactTokens($greetingLabel, $contact, NULL, $tokens);
1924 * Build array for merging same addresses.
1926 * @param string $sql
1928 public function buildMasterCopyArray($sql) {
1931 $dao = CRM_Core_DAO
::executeQuery($sql);
1933 while ($dao->fetch()) {
1934 $masterID = $dao->master_id
;
1935 $copyID = $dao->copy_id
;
1937 $this->cacheContactGreetings((int) $dao->master_contact_id
);
1938 $this->cacheContactGreetings((int) $dao->copy_contact_id
);
1940 if (!isset($this->contactsToMerge
[$masterID])) {
1941 // check if this is an intermediate child
1942 // this happens if there are 3 or more matches a,b, c
1943 // the above query will return a, b / a, c / b, c
1944 // we might be doing a bit more work, but for now its ok, unless someone
1945 // knows how to fix the query above
1946 if (isset($parents[$masterID])) {
1947 $masterID = $parents[$masterID];
1950 $this->contactsToMerge
[$masterID] = [
1951 'addressee' => $this->getContactGreeting((int) $dao->master_contact_id
, 'addressee', $dao->master_addressee
),
1953 'postalGreeting' => $this->getContactGreeting((int) $dao->master_contact_id
, 'postal_greeting', $dao->master_postal_greeting
),
1955 $this->contactsToMerge
[$masterID]['emailGreeting'] = &$this->contactsToMerge
[$masterID]['postalGreeting'];
1958 $parents[$copyID] = $masterID;
1960 if (!array_key_exists($copyID, $this->contactsToMerge
[$masterID]['copy'])) {
1961 $copyPostalGreeting = $this->getContactPortionOfGreeting((int) $dao->copy_contact_id
, (int) $dao->copy_postal_greeting_id
, 'postal_greeting', $dao->copy_postal_greeting
);
1962 if ($copyPostalGreeting) {
1963 $this->contactsToMerge
[$masterID]['postalGreeting'] = "{$this->contactsToMerge[$masterID]['postalGreeting']}, {$copyPostalGreeting}";
1964 // if there happens to be a duplicate, remove it
1965 $this->contactsToMerge
[$masterID]['postalGreeting'] = str_replace(" {$copyPostalGreeting},", "", $this->contactsToMerge
[$masterID]['postalGreeting']);
1968 $copyAddressee = $this->getContactPortionOfGreeting((int) $dao->copy_contact_id
, (int) $dao->copy_addressee_id
, 'addressee', $dao->copy_addressee
);
1969 if ($copyAddressee) {
1970 $this->contactsToMerge
[$masterID]['addressee'] = "{$this->contactsToMerge[$masterID]['addressee']}, " . trim($copyAddressee);
1973 if (!isset($this->contactsToMerge
[$masterID]['copy'][$copyID])) {
1974 // If it was set in the first run through - share routine, don't subsequently clobber.
1975 $this->contactsToMerge
[$masterID]['copy'][$copyID] = $copyAddressee ??
$dao->copy_addressee
;
1981 * Merge contacts with the same address.
1983 public function mergeSameAddress() {
1985 $tableName = $this->getTemporaryTable();
1987 // find all the records that have the same street address BUT not in a household
1988 // require match on city and state as well
1990 SELECT r1.id as master_id,
1991 r1.civicrm_primary_id as master_contact_id,
1992 r1.postal_greeting as master_postal_greeting,
1993 r1.postal_greeting_id as master_postal_greeting_id,
1994 r1.addressee as master_addressee,
1995 r1.addressee_id as master_addressee_id,
1997 r2.civicrm_primary_id as copy_contact_id,
1998 r2.postal_greeting as copy_postal_greeting,
1999 r2.postal_greeting_id as copy_postal_greeting_id,
2000 r2.addressee as copy_addressee,
2001 r2.addressee_id as copy_addressee_id
2003 LEFT JOIN $tableName r2 ON ( r1.street_address = r2.street_address AND
2004 r1.city = r2.city AND
2005 r1.state_province_id = r2.state_province_id )
2006 WHERE ( r1.street_address != '' )
2010 $this->buildMasterCopyArray($sql);
2012 foreach ($this->contactsToMerge
as $masterID => $values) {
2015 SET addressee = %1, postal_greeting = %2, email_greeting = %3
2019 1 => [$values['addressee'], 'String'],
2020 2 => [$values['postalGreeting'], 'String'],
2021 3 => [$values['emailGreeting'], 'String'],
2022 4 => [$masterID, 'Integer'],
2024 CRM_Core_DAO
::executeQuery($sql, $params);
2026 // delete all copies
2027 $deleteIDs = array_keys($values['copy']);
2028 $deleteIDString = implode(',', $deleteIDs);
2030 DELETE FROM $tableName
2031 WHERE id IN ( $deleteIDString )
2033 CRM_Core_DAO
::executeQuery($sql);
2038 * The function unsets static part of the string, if token is the dynamic part.
2040 * Example: 'Hello {contact.first_name}' => converted to => '{contact.first_name}'
2041 * i.e 'Hello Alan' => converted to => 'Alan'
2043 * @param string $parsedString
2044 * @param string $defaultGreeting
2045 * @param string $greetingLabel
2049 public function trimNonTokensFromAddressString(
2050 &$parsedString, $defaultGreeting,
2053 $greetingLabel = empty($greetingLabel) ?
$defaultGreeting : $greetingLabel;
2055 $stringsToBeReplaced = preg_replace('/(\{[a-zA-Z._ ]+\})/', ';;', $greetingLabel);
2056 $stringsToBeReplaced = explode(';;', $stringsToBeReplaced);
2057 foreach ($stringsToBeReplaced as $key => $string) {
2058 // to keep one space
2059 $stringsToBeReplaced[$key] = ltrim($string);
2061 $parsedString = str_replace($stringsToBeReplaced, "", $parsedString);
2063 return $parsedString;
2067 * Preview export output.
2072 public function getPreview($limit) {
2074 list($outputColumns, $metadata) = $this->getExportStructureArrays();
2075 $query = $this->runQuery([], '');
2076 CRM_Core_DAO
::disableFullGroupByMode();
2077 $result = CRM_Core_DAO
::executeQuery($query[1] . ' LIMIT ' . (int) $limit);
2078 CRM_Core_DAO
::reenableFullGroupByMode();
2079 while ($result->fetch()) {
2080 $rows[] = $this->buildRow($query[0], $result, $outputColumns, $metadata, [], []);
2086 * Set the template strings to be used when merging two contacts with the same address.
2088 * @param array $formValues
2089 * Values from first form. In this case we care about the keys
2092 * - address_greeting
2097 protected function setGreetingStringsForSameAddressMerge($formValues) {
2098 $greetingOptions = CRM_Export_Form_Select
::getGreetingOptions();
2100 if (!empty($greetingOptions)) {
2101 // Greeting options is keyed by 'postal_greeting' or 'addressee'.
2102 foreach ($greetingOptions as $key => $value) {
2103 $option = $formValues[$key] ??
NULL;
2105 if ($greetingOptions[$key][$option] == ts('Other')) {
2106 $formValues[$key] = $formValues["{$key}_other"];
2108 elseif ($greetingOptions[$key][$option] == ts('List of names')) {
2109 $formValues[$key] = '';
2112 $formValues[$key] = $greetingOptions[$key][$option];
2117 if (!empty($formValues['postal_greeting'])) {
2118 $this->setPostalGreetingTemplate($formValues['postal_greeting']);
2120 if (!empty($formValues['addressee'])) {
2121 $this->setAddresseeGreetingTemplate($formValues['addressee']);
2126 * Create the temporary table for output.
2128 public function createTempTable() {
2129 //creating a temporary table for the search result that need be exported
2130 $exportTempTable = CRM_Utils_SQL_TempTable
::build()->setDurable()->setCategory('export');
2131 $sqlColumns = $this->getSQLColumns();
2132 // also create the sql table
2133 $exportTempTable->drop();
2135 $sql = " id int unsigned NOT NULL AUTO_INCREMENT, ";
2136 if (!empty($sqlColumns)) {
2137 $sql .= implode(",\n", array_values($sqlColumns)) . ',';
2140 $sql .= "\n PRIMARY KEY ( id )";
2142 // add indexes for street_address and household_name if present
2146 'civicrm_primary_id',
2149 foreach ($addIndices as $index) {
2150 if (isset($sqlColumns[$index])) {
2152 INDEX index_{$index}( $index )
2157 $exportTempTable->createWithColumns($sql);
2158 $this->setTemporaryTable($exportTempTable->getName());
2162 * Get the values of linked household contact.
2164 * @param CRM_Core_DAO $relDAO
2165 * @param array $value
2166 * @param string $field
2169 * @throws \Exception
2171 public function fetchRelationshipDetails($relDAO, $value, $field, &$row) {
2172 $phoneTypes = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_Phone', 'phone_type_id');
2173 $imProviders = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_IM', 'provider_id');
2174 $i18n = CRM_Core_I18n
::singleton();
2175 $field = $field . '_';
2177 foreach ($value as $relationField => $relationValue) {
2178 if (is_object($relDAO) && property_exists($relDAO, $relationField)) {
2179 $fieldValue = $relDAO->$relationField;
2180 if ($relationField == 'phone_type_id') {
2181 $fieldValue = $phoneTypes[$relationValue];
2183 elseif ($relationField == 'provider_id') {
2184 $fieldValue = $imProviders[$relationValue] ??
NULL;
2187 elseif (is_object($relDAO) && in_array($relationField, [
2192 //special case for greeting replacement
2193 $fldValue = "{$relationField}_display";
2194 $fieldValue = $relDAO->$fldValue;
2197 elseif (is_object($relDAO) && $relationField == 'state_province') {
2198 $fieldValue = CRM_Core_PseudoConstant
::stateProvince($relDAO->state_province_id
);
2200 elseif (is_object($relDAO) && $relationField == 'country') {
2201 $fieldValue = CRM_Core_PseudoConstant
::country($relDAO->country_id
);
2206 $relPrefix = $field . $relationField;
2208 if (is_object($relDAO) && $relationField == 'id') {
2209 $row[$relPrefix] = $relDAO->contact_id
;
2211 elseif (is_array($relationValue) && $relationField == 'location') {
2212 foreach ($relationValue as $ltype => $val) {
2213 // If the location name has a space in it the we need to handle that. This
2214 // is kinda hacky but specifically covered in the ExportTest so later efforts to
2215 // improve it should be secure in the knowled it will be caught.
2216 $ltype = str_replace(' ', '_', $ltype);
2217 foreach (array_keys($val) as $fld) {
2218 $type = explode('-', $fld);
2219 $fldValue = "{$ltype}-" . $type[0];
2220 if (!empty($type[1])) {
2221 $fldValue .= "-" . $type[1];
2223 // CRM-3157: localise country, region (both have ‘country’ context)
2224 // and state_province (‘province’ context)
2226 case (!is_object($relDAO)):
2227 $row[$field . '_' . $fldValue] = '';
2230 case in_array('country', $type):
2231 case in_array('world_region', $type):
2232 $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue,
2233 ['context' => 'country']
2237 case in_array('state_province', $type):
2238 $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue,
2239 ['context' => 'province']
2244 $row[$field . '_' . $fldValue] = $relDAO->$fldValue;
2250 elseif (isset($fieldValue) && $fieldValue != '') {
2251 //check for custom data
2252 if ($cfID = CRM_Core_BAO_CustomField
::getKeyID($relationField)) {
2253 $row[$relPrefix] = CRM_Core_BAO_CustomField
::displayValue($fieldValue, $cfID);
2256 //normal relationship fields
2257 // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context)
2258 switch ($relationField) {
2260 case 'world_region':
2261 $row[$relPrefix] = $i18n->crm_translate($fieldValue, ['context' => 'country']);
2264 case 'state_province':
2265 $row[$relPrefix] = $i18n->crm_translate($fieldValue, ['context' => 'province']);
2269 $row[$relPrefix] = $fieldValue;
2275 // if relation field is empty or null
2276 $row[$relPrefix] = '';
2282 * Write to the csv from the temp table.
2284 public function writeCSVFromTable() {
2286 $headerRows = $this->getHeaderRows();
2287 $exportTempTable = $this->getTemporaryTable();
2288 $exportMode = $this->getExportMode();
2289 $sqlColumns = $this->getSQLColumns();
2290 $componentTable = $this->getComponentTable();
2291 $ids = $this->getIds();
2292 CRM_Utils_Hook
::export($exportTempTable, $headerRows, $sqlColumns, $exportMode, $componentTable, $ids);
2293 if ($exportMode !== $this->getExportMode() ||
$componentTable !== $this->getComponentTable()) {
2294 CRM_Core_Error
::deprecatedFunctionWarning('altering the export mode and/or component table in the hook is no longer supported.');
2296 if ($ids !== $this->getIds()) {
2297 CRM_Core_Error
::deprecatedFunctionWarning('altering the ids in the hook is no longer supported.');
2299 if ($exportTempTable !== $this->getTemporaryTable()) {
2300 CRM_Core_Error
::deprecatedFunctionWarning('altering the export table in the hook is deprecated (in some flows the table itself will be)');
2301 $this->setTemporaryTable($exportTempTable);
2303 $exportTempTable = $this->getTemporaryTable();
2304 $writeHeader = TRUE;
2306 // increase this number a lot to avoid making too many queries
2307 // LIMIT is not much faster than a no LIMIT query
2311 $query = "SELECT * FROM $exportTempTable";
2313 $this->instantiateTempTable($headerRows);
2315 $limitQuery = $query . "
2316 LIMIT $offset, $limit
2318 $dao = CRM_Core_DAO
::executeQuery($limitQuery);
2324 $componentDetails = [];
2325 while ($dao->fetch()) {
2328 foreach (array_keys($sqlColumns) as $column) {
2329 $row[$column] = $dao->$column;
2331 $componentDetails[] = $row;
2333 $this->writeRows($headerRows, $componentDetails);
2340 * Set up the temp table.
2342 * @param array $headerRows
2344 protected function instantiateTempTable(array $headerRows) {
2345 CRM_Utils_System
::download(CRM_Utils_String
::munge($this->getExportFileName()),
2347 CRM_Core_DAO
::$_nullObject,
2351 // Output UTF BOM so that MS Excel copes with diacritics. This is recommended as
2352 // the Windows variant but is tested with MS Excel for Mac (Office 365 v 16.31)
2353 // and it continues to work on Libre Office, Numbers, Notes etc.
2354 echo "\xEF\xBB\xBF";
2355 CRM_Core_Report_Excel
::makeCSVTable($headerRows, [], TRUE);
2359 * Write rows to the csv.
2361 * @param array $headerRows
2362 * @param array $rows
2364 protected function writeRows(array $headerRows, array $rows) {
2365 if (!empty($rows)) {
2366 CRM_Core_Report_Excel
::makeCSVTable($headerRows, $rows, FALSE);
2371 * Cache the greeting fields for the given contact.
2373 * @param int $contactID
2375 protected function cacheContactGreetings(int $contactID) {
2376 if (!isset($this->contactGreetingFields
[$contactID])) {
2377 $this->contactGreetingFields
[$contactID] = $this->replaceMergeTokens($contactID);
2382 * Get the greeting value for the given contact.
2384 * The values have already been cached so we are grabbing the value at this point.
2386 * @param int $contactID
2387 * @param string $type
2388 * postal_greeting|addressee|email_greeting
2389 * @param string $default
2393 protected function getContactGreeting(int $contactID, string $type, string $default) {
2394 return CRM_Utils_Array
::value($type,
2395 $this->contactGreetingFields
[$contactID], $default
2400 * Get the portion of the greeting string that relates to the contact.
2402 * For example if the greeting id 'Dear Sarah' we are going to combine it with 'Dear Mike'
2403 * so we want to strip the 'Dear ' and just get 'Sarah
2404 * @param int $contactID
2405 * @param int $greetingID
2406 * @param string $type
2407 * postal_greeting, addressee (email_greeting not currently implemented for unknown reasons.
2408 * @param string $defaultGreeting
2410 * @return mixed|string
2412 protected function getContactPortionOfGreeting(int $contactID, int $greetingID, string $type, string $defaultGreeting) {
2413 $copyPostalGreeting = $this->getContactGreeting($contactID, $type, $defaultGreeting);
2414 $template = $type === 'postal_greeting' ?
$this->getPostalGreetingTemplate() : $this->getAddresseeGreetingTemplate();
2415 if ($copyPostalGreeting) {
2416 $copyPostalGreeting = $this->trimNonTokensFromAddressString($copyPostalGreeting,
2417 $this->greetingOptions
[$type][$greetingID],
2421 return $copyPostalGreeting;