3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2019
35 * Class CRM_Export_BAO_ExportProcessor
37 * Class to handle logic of export.
39 class CRM_Export_BAO_ExportProcessor
{
49 protected $exportMode;
52 * Array of fields in the main query.
56 protected $queryFields = [];
63 protected $queryOperator;
66 * Requested output fields.
68 * If set to NULL then it is 'primary fields only'
69 * which actually means pretty close to all fields!
73 protected $requestedFields;
76 * Is the contact being merged into a single household.
80 protected $isMergeSameHousehold;
83 * Should contacts with the same address be merged.
87 protected $isMergeSameAddress = FALSE;
90 * Fields that need to be retrieved for address merge purposes but should not be in output.
94 protected $additionalFieldsForSameAddressMerge = [];
97 * Fields used for merging same contacts.
101 protected $contactGreetingFields = [];
104 * Get additional non-visible fields for address merge purposes.
108 public function getAdditionalFieldsForSameAddressMerge(): array {
109 return $this->additionalFieldsForSameAddressMerge
;
113 * Set additional non-visible fields for address merge purposes.
115 public function setAdditionalFieldsForSameAddressMerge() {
116 if ($this->isMergeSameAddress
) {
117 $fields = ['id', 'master_id', 'state_province_id', 'postal_greeting_id', 'addressee_id'];
118 foreach ($fields as $index => $field) {
119 if (!empty($this->getReturnProperties()[$field])) {
120 unset($fields[$index]);
123 $this->additionalFieldsForSameAddressMerge
= array_fill_keys($fields, 1);
128 * Should contacts with the same address be merged.
132 public function isMergeSameAddress(): bool {
133 return $this->isMergeSameAddress
;
137 * Set same address is to be merged.
139 * @param bool $isMergeSameAddress
141 public function setIsMergeSameAddress(bool $isMergeSameAddress) {
142 $this->isMergeSameAddress
= $isMergeSameAddress;
146 * Additional fields required to export postal fields.
150 protected $additionalFieldsForPostalExport = [];
153 * Get additional fields required to do a postal export.
157 public function getAdditionalFieldsForPostalExport() {
158 return $this->additionalFieldsForPostalExport
;
162 * Set additional fields required for a postal export.
164 public function setAdditionalFieldsForPostalExport() {
165 if ($this->getRequestedFields() && $this->isPostalableOnly()) {
166 $fields = ['is_deceased', 'do_not_mail', 'street_address', 'supplemental_address_1'];
167 foreach ($fields as $index => $field) {
168 if (!empty($this->getReturnProperties()[$field])) {
169 unset($field[$index]);
172 $this->additionalFieldsForPostalExport
= array_fill_keys($fields, 1);
177 * Only export contacts that can receive postal mail.
179 * Includes being alive, having an address & not having do_not_mail.
183 protected $isPostalableOnly;
186 * Key representing the head of household in the relationship array.
188 * e.g. ['8_b_a' => 'Household Member Is', '8_a_b = 'Household Member Of'.....]
192 protected $relationshipTypes = [];
195 * Array of properties to retrieve for relationships.
199 protected $relationshipReturnProperties = [];
202 * IDs of households that have already been exported.
206 protected $exportedHouseholds = [];
209 * Households to skip during export as they will be exported via their relationships anyway.
213 protected $householdsToSkip = [];
216 * Additional fields to return.
218 * This doesn't make much sense when we have a fields set but search build add it's own onto
219 * the 'Primary fields' (all) option.
223 protected $additionalRequestedReturnProperties = [];
226 * Get additional return properties.
230 public function getAdditionalRequestedReturnProperties() {
231 return $this->additionalRequestedReturnProperties
;
235 * Set additional return properties.
237 * @param array $value
239 public function setAdditionalRequestedReturnProperties($value) {
241 if (!empty($value['group'])) {
242 unset($value['group']);
243 $value['groups'] = 1;
245 $this->additionalRequestedReturnProperties
= $value;
249 * Get return properties by relationship.
252 public function getRelationshipReturnProperties() {
253 return $this->relationshipReturnProperties
;
257 * Export values for related contacts.
261 protected $relatedContactValues = [];
266 protected $returnProperties = [];
271 protected $outputSpecification = [];
276 protected $componentTable = '';
281 public function getComponentTable() {
282 return $this->componentTable
;
286 * Set the component table (if any).
288 * @param string $componentTable
290 public function setComponentTable($componentTable) {
291 $this->componentTable
= $componentTable;
295 * Clause from component search.
299 protected $componentClause = '';
304 public function getComponentClause() {
305 return $this->componentClause
;
309 * @param string $componentClause
311 public function setComponentClause($componentClause) {
312 $this->componentClause
= $componentClause;
316 * Name of a temporary table created to hold the results.
318 * Current decision making on when to create a temp table is kinda bad so this might change
319 * a bit as it is reviewed but basically we need a temp table or similar to calculate merging
320 * addresses. Merging households is handled in php. We create a temp table even when we don't need them.
324 protected $temporaryTable;
329 public function getTemporaryTable(): string {
330 return $this->temporaryTable
;
334 * @param string $temporaryTable
336 public function setTemporaryTable(string $temporaryTable) {
337 $this->temporaryTable
= $temporaryTable;
340 protected $postalGreetingTemplate;
345 public function getPostalGreetingTemplate() {
346 return $this->postalGreetingTemplate
;
350 * @param mixed $postalGreetingTemplate
352 public function setPostalGreetingTemplate($postalGreetingTemplate) {
353 $this->postalGreetingTemplate
= $postalGreetingTemplate;
359 public function getAddresseeGreetingTemplate() {
360 return $this->addresseeGreetingTemplate
;
364 * @param mixed $addresseeGreetingTemplate
366 public function setAddresseeGreetingTemplate($addresseeGreetingTemplate) {
367 $this->addresseeGreetingTemplate
= $addresseeGreetingTemplate;
370 protected $addresseeGreetingTemplate;
373 * CRM_Export_BAO_ExportProcessor constructor.
375 * @param int $exportMode
376 * @param array|null $requestedFields
377 * @param string $queryOperator
378 * @param bool $isMergeSameHousehold
379 * @param bool $isPostalableOnly
380 * @param bool $isMergeSameAddress
381 * @param array $formValues
382 * Values from the export options form on contact export. We currently support these keys
385 * - addresee_greeting
388 public function __construct($exportMode, $requestedFields, $queryOperator, $isMergeSameHousehold = FALSE, $isPostalableOnly = FALSE, $isMergeSameAddress = FALSE, $formValues = []) {
389 $this->setExportMode($exportMode);
390 $this->setQueryMode();
391 $this->setQueryOperator($queryOperator);
392 $this->setRequestedFields($requestedFields);
393 $this->setRelationshipTypes();
394 $this->setIsMergeSameHousehold($isMergeSameHousehold);
395 $this->setIsPostalableOnly($isPostalableOnly);
396 $this->setIsMergeSameAddress($isMergeSameAddress);
397 $this->setReturnProperties($this->determineReturnProperties());
398 $this->setAdditionalFieldsForSameAddressMerge();
399 $this->setAdditionalFieldsForPostalExport();
400 $this->setHouseholdMergeReturnProperties();
401 $this->setGreetingStringsForSameAddressMerge($formValues);
407 public function isPostalableOnly() {
408 return $this->isPostalableOnly
;
412 * @param bool $isPostalableOnly
414 public function setIsPostalableOnly($isPostalableOnly) {
415 $this->isPostalableOnly
= $isPostalableOnly;
421 public function getRequestedFields() {
422 return empty($this->requestedFields
) ?
NULL : $this->requestedFields
;
426 * @param array|null $requestedFields
428 public function setRequestedFields($requestedFields) {
429 $this->requestedFields
= $requestedFields;
435 public function getReturnProperties() {
436 return array_merge($this->returnProperties
, $this->getAdditionalRequestedReturnProperties(), $this->getAdditionalFieldsForSameAddressMerge(), $this->getAdditionalFieldsForPostalExport());
440 * @param array $returnProperties
442 public function setReturnProperties($returnProperties) {
443 $this->returnProperties
= $returnProperties;
449 public function getRelationshipTypes() {
450 return $this->relationshipTypes
;
455 public function setRelationshipTypes() {
456 $this->relationshipTypes
= CRM_Contact_BAO_Relationship
::getContactRelationshipType(
468 * Set the value for a relationship type field.
470 * In this case we are building up an array of properties for a related contact.
472 * These may be used for direct exporting or for merge to household depending on the
475 * @param string $relationshipType
476 * @param int $contactID
477 * @param string $field
478 * @param string $value
480 public function setRelationshipValue($relationshipType, $contactID, $field, $value) {
481 $this->relatedContactValues
[$relationshipType][$contactID][$field] = $value;
482 if ($field === 'id') {
483 $this->householdsToSkip
[] = $value;
488 * Get the value for a relationship type field.
490 * In this case we are building up an array of properties for a related contact.
492 * These may be used for direct exporting or for merge to household depending on the
495 * @param string $relationshipType
496 * @param int $contactID
497 * @param string $field
501 public function getRelationshipValue($relationshipType, $contactID, $field) {
502 return isset($this->relatedContactValues
[$relationshipType][$contactID][$field]) ?
$this->relatedContactValues
[$relationshipType][$contactID][$field] : '';
506 * Get the id of the related household.
508 * @param int $contactID
509 * @param string $relationshipType
513 public function getRelatedHouseholdID($contactID, $relationshipType) {
514 return $this->relatedContactValues
[$relationshipType][$contactID]['id'];
518 * Has the household already been exported.
520 * @param int $housholdContactID
524 public function isHouseholdExported($housholdContactID) {
525 return isset($this->exportedHouseholds
[$housholdContactID]);
532 public function isMergeSameHousehold() {
533 return $this->isMergeSameHousehold
;
537 * @param bool $isMergeSameHousehold
539 public function setIsMergeSameHousehold($isMergeSameHousehold) {
540 $this->isMergeSameHousehold
= $isMergeSameHousehold;
544 * Return relationship types for household merge.
548 public function getHouseholdRelationshipTypes() {
549 if (!$this->isMergeSameHousehold()) {
553 CRM_Utils_Array
::key('Household Member of', $this->getRelationshipTypes()),
554 CRM_Utils_Array
::key('Head of Household for', $this->getRelationshipTypes()),
562 public function isRelationshipTypeKey($fieldName) {
563 return array_key_exists($fieldName, $this->relationshipTypes
);
570 public function isHouseholdMergeRelationshipTypeKey($fieldName) {
571 return in_array($fieldName, $this->getHouseholdRelationshipTypes());
577 public function getQueryOperator() {
578 return $this->queryOperator
;
582 * @param string $queryOperator
584 public function setQueryOperator($queryOperator) {
585 $this->queryOperator
= $queryOperator;
591 public function getQueryFields() {
594 $this->getComponentPaymentFields()
599 * @param array $queryFields
601 public function setQueryFields($queryFields) {
602 // legacy hacks - we add these to queryFields because this
603 // pseudometadata is currently required.
604 $queryFields['im_provider']['pseudoconstant']['var'] = 'imProviders';
605 $queryFields['country']['context'] = 'country';
606 $queryFields['world_region']['context'] = 'country';
607 $queryFields['state_province']['context'] = 'province';
608 $queryFields['contact_id'] = ['title' => ts('Contact ID'), 'type' => CRM_Utils_Type
::T_INT
];
609 $this->queryFields
= $queryFields;
615 public function getQueryMode() {
616 return $this->queryMode
;
620 * Set the query mode based on the export mode.
622 public function setQueryMode() {
624 switch ($this->getExportMode()) {
625 case CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
:
626 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
;
629 case CRM_Export_Form_Select
::EVENT_EXPORT
:
630 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_EVENT
;
633 case CRM_Export_Form_Select
::MEMBER_EXPORT
:
634 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_MEMBER
;
637 case CRM_Export_Form_Select
::PLEDGE_EXPORT
:
638 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_PLEDGE
;
641 case CRM_Export_Form_Select
::CASE_EXPORT
:
642 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_CASE
;
645 case CRM_Export_Form_Select
::GRANT_EXPORT
:
646 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_GRANT
;
649 case CRM_Export_Form_Select
::ACTIVITY_EXPORT
:
650 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_ACTIVITY
;
654 $this->queryMode
= CRM_Contact_BAO_Query
::MODE_CONTACTS
;
661 public function getExportMode() {
662 return $this->exportMode
;
666 * @param int $exportMode
668 public function setExportMode($exportMode) {
669 $this->exportMode
= $exportMode;
673 * Get the name for the export file.
677 public function getExportFileName() {
678 switch ($this->getExportMode()) {
679 case CRM_Export_Form_Select
::CONTACT_EXPORT
:
680 return ts('CiviCRM Contact Search');
682 case CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
:
683 return ts('CiviCRM Contribution Search');
685 case CRM_Export_Form_Select
::MEMBER_EXPORT
:
686 return ts('CiviCRM Member Search');
688 case CRM_Export_Form_Select
::EVENT_EXPORT
:
689 return ts('CiviCRM Participant Search');
691 case CRM_Export_Form_Select
::PLEDGE_EXPORT
:
692 return ts('CiviCRM Pledge Search');
694 case CRM_Export_Form_Select
::CASE_EXPORT
:
695 return ts('CiviCRM Case Search');
697 case CRM_Export_Form_Select
::GRANT_EXPORT
:
698 return ts('CiviCRM Grant Search');
700 case CRM_Export_Form_Select
::ACTIVITY_EXPORT
:
701 return ts('CiviCRM Activity Search');
704 // Legacy code suggests the value could be 'financial' - ie. something
705 // other than what should be accepted. However, I suspect that this line is
707 return ts('CiviCRM Search');
712 * Get the label for the header row based on the field to output.
714 * @param string $field
718 public function getHeaderForRow($field) {
719 if (substr($field, -11) == 'campaign_id') {
720 // @todo - set this correctly in the xml rather than here.
721 // This will require a generalised handling cleanup
722 return ts('Campaign ID');
724 if ($this->isMergeSameHousehold() && $field === 'id') {
725 return ts('Household ID');
727 elseif (isset($this->getQueryFields()[$field]['title'])) {
728 return $this->getQueryFields()[$field]['title'];
730 elseif ($this->isExportPaymentFields() && array_key_exists($field, $this->getcomponentPaymentFields())) {
731 return CRM_Utils_Array
::value($field, $this->getcomponentPaymentFields())['title'];
744 public function runQuery($params, $order) {
745 $returnProperties = $this->getReturnProperties();
746 $params = array_merge($params, $this->getWhereParams());
748 $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL,
749 FALSE, FALSE, $this->getQueryMode(),
750 FALSE, TRUE, TRUE, NULL, $this->getQueryOperator()
755 $query->_sort
= $order;
756 list($select, $from, $where, $having) = $query->query();
757 $this->setQueryFields($query->_fields
);
758 $whereClauses = ['trash_clause' => "contact_a.is_deleted != 1"];
759 if ($this->getRequestedFields() && ($this->getComponentTable())) {
760 $from .= " INNER JOIN " . $this->getComponentTable() . " ctTable ON ctTable.contact_id = contact_a.id ";
762 elseif ($this->getComponentClause()) {
763 $whereClauses[] = $this->getComponentClause();
766 // CRM-13982 - check if is deleted
767 foreach ($params as $value) {
768 if ($value[0] == 'contact_is_deleted') {
769 unset($whereClauses['trash_clause']);
773 if ($this->isPostalableOnly
) {
774 if (array_key_exists('street_address', $returnProperties)) {
775 $addressWhere = " civicrm_address.street_address <> ''";
776 if (array_key_exists('supplemental_address_1', $returnProperties)) {
777 // We need this to be an OR rather than AND on the street_address so, hack it in.
778 $addressOptions = CRM_Core_BAO_Setting
::valueOptions(CRM_Core_BAO_Setting
::SYSTEM_PREFERENCES_NAME
,
779 'address_options', TRUE, NULL, TRUE
781 if (!empty($addressOptions['supplemental_address_1'])) {
782 $addressWhere .= " OR civicrm_address.supplemental_address_1 <> ''";
785 $whereClauses['address'] = $addressWhere;
790 $where = "WHERE " . implode(' AND ', $whereClauses);
793 $where .= " AND " . implode(' AND ', $whereClauses);
796 $groupBy = $this->getGroupBy($query);
797 $queryString = "$select $from $where $having $groupBy";
799 // always add contact_a.id to the ORDER clause
800 // so the order is deterministic
802 if (strpos('contact_a.id', $order) === FALSE) {
803 $order .= ", contact_a.id";
806 list($field, $dir) = explode(' ', $order, 2);
807 $field = trim($field);
808 if (!empty($this->getReturnProperties()[$field])) {
810 $queryString .= " ORDER BY $order";
813 return [$query, $queryString];
817 * Add a row to the specification for how to output data.
820 * @param string $relationshipType
821 * @param string $locationType
822 * @param int $entityTypeID phone_type_id or provider_id for phone or im fields.
824 public function addOutputSpecification($key, $relationshipType = NULL, $locationType = NULL, $entityTypeID = NULL) {
827 if ($key === 'phone') {
828 $entityLabel = CRM_Core_PseudoConstant
::getLabel('CRM_Core_BAO_Phone', 'phone_type_id', $entityTypeID);
831 $entityLabel = CRM_Core_PseudoConstant
::getLabel('CRM_Core_BAO_IM', 'provider_id', $entityTypeID);
835 // These oddly constructed keys are for legacy reasons. Altering them will affect test success
836 // but in time it may be good to rationalise them.
837 $label = $this->getOutputSpecificationLabel($key, $relationshipType, $locationType, $entityLabel);
838 $index = $this->getOutputSpecificationIndex($key, $relationshipType, $locationType, $entityLabel);
839 $fieldKey = $this->getOutputSpecificationFieldKey($key, $relationshipType, $locationType, $entityLabel);
841 $this->outputSpecification
[$index]['header'] = $label;
842 $this->outputSpecification
[$index]['sql_columns'] = $this->getSqlColumnDefinition($fieldKey, $key);
844 if ($relationshipType && $this->isHouseholdMergeRelationshipTypeKey($relationshipType)) {
845 $this->setColumnAsCalculationOnly($index);
847 $this->outputSpecification
[$index]['metadata'] = $this->getMetaDataForField($key);
851 * Get the metadata for the given field.
857 public function getMetaDataForField($key) {
858 $mappings = ['contact_id' => 'id'];
859 if (isset($this->getQueryFields()[$key])) {
860 return $this->getQueryFields()[$key];
862 if (isset($mappings[$key])) {
863 return $this->getQueryFields()[$mappings[$key]];
871 public function setSqlColumnDefn($key) {
872 $this->outputSpecification
[$this->getMungedFieldName($key)]['sql_columns'] = $this->getSqlColumnDefinition($key, $this->getMungedFieldName($key));
876 * Mark a column as only required for calculations.
878 * Do not include the row with headers.
880 * @param string $column
882 public function setColumnAsCalculationOnly($column) {
883 $this->outputSpecification
[$column]['do_not_output_to_csv'] = TRUE;
889 public function getHeaderRows() {
891 foreach ($this->outputSpecification
as $key => $spec) {
892 if (empty($spec['do_not_output_to_csv'])) {
893 $headerRows[] = $spec['header'];
902 public function getSQLColumns() {
904 foreach ($this->outputSpecification
as $key => $spec) {
905 if (empty($spec['do_not_output_to_sql'])) {
906 $sqlColumns[$key] = $spec['sql_columns'];
915 public function getMetadata() {
917 foreach ($this->outputSpecification
as $key => $spec) {
918 $metadata[$key] = $spec['metadata'];
924 * Build the row for output.
926 * @param \CRM_Contact_BAO_Query $query
927 * @param CRM_Core_DAO $iterationDAO
928 * @param array $outputColumns
930 * @param $paymentDetails
931 * @param $addPaymentHeader
935 public function buildRow($query, $iterationDAO, $outputColumns, $metadata, $paymentDetails, $addPaymentHeader) {
936 $paymentTableId = $this->getPaymentTableID();
937 if ($this->isHouseholdToSkip($iterationDAO->contact_id
)) {
940 $phoneTypes = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_Phone', 'phone_type_id');
941 $imProviders = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_IM', 'provider_id');
944 $householdMergeRelationshipType = $this->getHouseholdMergeTypeForRow($iterationDAO->contact_id
);
945 if ($householdMergeRelationshipType) {
946 $householdID = $this->getRelatedHouseholdID($iterationDAO->contact_id
, $householdMergeRelationshipType);
947 if ($this->isHouseholdExported($householdID)) {
950 foreach (array_keys($outputColumns) as $column) {
951 $row[$column] = $this->getRelationshipValue($householdMergeRelationshipType, $iterationDAO->contact_id
, $column);
953 $this->markHouseholdExported($householdID);
957 $query->convertToPseudoNames($iterationDAO);
959 //first loop through output columns so that we return what is required, and in same order.
960 foreach ($outputColumns as $field => $value) {
961 // add im_provider to $dao object
962 if ($field == 'im_provider' && property_exists($iterationDAO, 'provider_id')) {
963 $iterationDAO->im_provider
= $iterationDAO->provider_id
;
966 //build row values (data)
968 if (property_exists($iterationDAO, $field)) {
969 $fieldValue = $iterationDAO->$field;
970 // to get phone type from phone type id
971 if ($field == 'phone_type_id' && isset($phoneTypes[$fieldValue])) {
972 $fieldValue = $phoneTypes[$fieldValue];
974 elseif ($field == 'provider_id' ||
$field == 'im_provider') {
975 $fieldValue = CRM_Utils_Array
::value($fieldValue, $imProviders);
977 elseif (strstr($field, 'master_id')) {
978 // @todo - why not just $field === 'master_id' - what else would it be?
979 $masterAddressId = $iterationDAO->$field ??
NULL;
980 // get display name of contact that address is shared.
981 $fieldValue = CRM_Contact_BAO_Contact
::getMasterDisplayName($masterAddressId);
985 if ($this->isRelationshipTypeKey($field)) {
986 $this->buildRelationshipFieldsForRow($row, $iterationDAO->contact_id
, $value, $field);
989 $row[$field] = $this->getTransformedFieldValue($field, $iterationDAO, $fieldValue, $metadata, $paymentDetails);
993 // If specific payment fields have been selected for export, payment
994 // data will already be in $row. Otherwise, add payment related
995 // information, if appropriate.
996 if ($addPaymentHeader) {
997 if (!$this->isExportSpecifiedPaymentFields()) {
998 $nullContributionDetails = array_fill_keys(array_keys($this->getPaymentHeaders()), NULL);
999 if ($this->isExportPaymentFields()) {
1000 $paymentData = CRM_Utils_Array
::value($row[$paymentTableId], $paymentDetails);
1001 if (!is_array($paymentData) ||
empty($paymentData)) {
1002 $paymentData = $nullContributionDetails;
1004 $row = array_merge($row, $paymentData);
1006 elseif (!empty($paymentDetails)) {
1007 $row = array_merge($row, $nullContributionDetails);
1011 //remove organization name for individuals if it is set for current employer
1012 if (!empty($row['contact_type']) &&
1013 $row['contact_type'] == 'Individual' && array_key_exists('organization_name', $row)
1015 $row['organization_name'] = '';
1021 * If this row has a household whose details we should use get the relationship type key.
1027 public function getHouseholdMergeTypeForRow($contactID) {
1028 if (!$this->isMergeSameHousehold()) {
1031 foreach ($this->getHouseholdRelationshipTypes() as $relationshipType) {
1032 if (isset($this->relatedContactValues
[$relationshipType][$contactID])) {
1033 return $relationshipType;
1039 * Mark the given household as already exported.
1041 * @param $householdID
1043 public function markHouseholdExported($householdID) {
1044 $this->exportedHouseholds
[$householdID] = $householdID;
1049 * @param $iterationDAO
1050 * @param $fieldValue
1052 * @param $paymentDetails
1056 public function getTransformedFieldValue($field, $iterationDAO, $fieldValue, $metadata, $paymentDetails) {
1058 $i18n = CRM_Core_I18n
::singleton();
1059 if ($field == 'id') {
1060 return $iterationDAO->contact_id
;
1061 // special case for calculated field
1063 elseif ($field == 'source_contact_id') {
1064 return $iterationDAO->contact_id
;
1066 elseif ($field == 'pledge_balance_amount') {
1067 return $iterationDAO->pledge_amount
- $iterationDAO->pledge_total_paid
;
1068 // special case for calculated field
1070 elseif ($field == 'pledge_next_pay_amount') {
1071 return $iterationDAO->pledge_next_pay_amount +
$iterationDAO->pledge_outstanding_amount
;
1073 elseif (isset($fieldValue) &&
1076 //check for custom data
1077 if ($cfID = CRM_Core_BAO_CustomField
::getKeyID($field)) {
1078 return CRM_Core_BAO_CustomField
::displayValue($fieldValue, $cfID);
1081 elseif (in_array($field, [
1086 //special case for greeting replacement
1087 $fldValue = "{$field}_display";
1088 return $iterationDAO->$fldValue;
1091 //normal fields with a touch of CRM-3157
1094 case 'world_region':
1095 return $i18n->crm_translate($fieldValue, ['context' => 'country']);
1097 case 'state_province':
1098 return $i18n->crm_translate($fieldValue, ['context' => 'province']);
1101 case 'preferred_communication_method':
1102 case 'preferred_mail_format':
1103 case 'communication_style':
1104 return $i18n->crm_translate($fieldValue);
1107 if (isset($metadata[$field])) {
1108 // No I don't know why we do it this way & whether we could
1109 // make better use of pseudoConstants.
1110 if (!empty($metadata[$field]['context'])) {
1111 return $i18n->crm_translate($fieldValue, $metadata[$field]);
1113 if (!empty($metadata[$field]['pseudoconstant'])) {
1114 if (!empty($metadata[$field]['bao'])) {
1115 return CRM_Core_PseudoConstant
::getLabel($metadata[$field]['bao'], $metadata[$field]['name'], $fieldValue);
1117 // This is not our normal syntax for pseudoconstants but I am a bit loath to
1118 // call an external function until sure it is not increasing php processing given this
1119 // may be iterated 100,000 times & we already have the $imProvider var loaded.
1120 // That can be next refactor...
1121 // Yes - definitely feeling hatred for this bit of code - I know you will beat me up over it's awfulness
1122 // but I have to reach a stable point....
1123 $varName = $metadata[$field]['pseudoconstant']['var'];
1124 if ($varName === 'imProviders') {
1125 return CRM_Core_PseudoConstant
::getLabel('CRM_Core_DAO_IM', 'provider_id', $fieldValue);
1127 if ($varName === 'phoneTypes') {
1128 return CRM_Core_PseudoConstant
::getLabel('CRM_Core_DAO_Phone', 'phone_type_id', $fieldValue);
1137 elseif ($this->isExportSpecifiedPaymentFields() && array_key_exists($field, $this->getcomponentPaymentFields())) {
1138 $paymentTableId = $this->getPaymentTableID();
1139 $paymentData = CRM_Utils_Array
::value($iterationDAO->$paymentTableId, $paymentDetails);
1141 'componentPaymentField_total_amount' => 'total_amount',
1142 'componentPaymentField_contribution_status' => 'contribution_status',
1143 'componentPaymentField_payment_instrument' => 'pay_instru',
1144 'componentPaymentField_transaction_id' => 'trxn_id',
1145 'componentPaymentField_received_date' => 'receive_date',
1147 return CRM_Utils_Array
::value($payFieldMapper[$field], $paymentData, '');
1150 // if field is empty or null
1156 * Get array of fields to return, over & above those defined in the main contact exportable fields.
1158 * These include export mode specific fields & some fields apparently required as 'exportableFields'
1159 * but not returned by the function of the same name.
1162 * Array of fields to return in the format ['field_name' => 1,...]
1164 public function getAdditionalReturnProperties() {
1165 if ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
1166 $componentSpecificFields = [];
1169 $componentSpecificFields = CRM_Contact_BAO_Query
::defaultReturnProperties($this->getQueryMode());
1171 if ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_PLEDGE
) {
1172 $componentSpecificFields = array_merge($componentSpecificFields, CRM_Pledge_BAO_Query
::extraReturnProperties($this->getQueryMode()));
1173 unset($componentSpecificFields['contribution_status_id']);
1174 unset($componentSpecificFields['pledge_status_id']);
1175 unset($componentSpecificFields['pledge_payment_status_id']);
1177 if ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_CASE
) {
1178 $componentSpecificFields = array_merge($componentSpecificFields, CRM_Case_BAO_Query
::extraReturnProperties($this->getQueryMode()));
1180 if ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_CONTRIBUTE
) {
1181 $componentSpecificFields = array_merge($componentSpecificFields, CRM_Contribute_BAO_Query
::softCreditReturnProperties(TRUE));
1182 unset($componentSpecificFields['contribution_status_id']);
1184 return $componentSpecificFields;
1188 * Should payment fields be appended to the export.
1190 * (This is pretty hacky so hopefully this function won't last long - notice
1191 * how obviously it should be part of the above function!).
1193 public function isExportPaymentFields() {
1194 if ($this->getRequestedFields() === NULL
1195 && in_array($this->getQueryMode(), [
1196 CRM_Contact_BAO_Query
::MODE_EVENT
,
1197 CRM_Contact_BAO_Query
::MODE_MEMBER
,
1198 CRM_Contact_BAO_Query
::MODE_PLEDGE
,
1202 elseif ($this->isExportSpecifiedPaymentFields()) {
1209 * Has specific payment fields been requested (as opposed to via all fields).
1211 * If specific fields have been requested then they get added at various points.
1215 public function isExportSpecifiedPaymentFields() {
1216 if ($this->getRequestedFields() !== NULL && $this->hasRequestedComponentPaymentFields()) {
1222 * Get the name of the id field in the table that connects contributions to the export entity.
1224 public function getPaymentTableID() {
1225 if ($this->getRequestedFields() === NULL) {
1227 CRM_Contact_BAO_Query
::MODE_EVENT
=> 'participant_id',
1228 CRM_Contact_BAO_Query
::MODE_MEMBER
=> 'membership_id',
1229 CRM_Contact_BAO_Query
::MODE_PLEDGE
=> 'pledge_payment_id',
1231 return isset($mapping[$this->getQueryMode()]) ?
$mapping[$this->getQueryMode()] : '';
1233 elseif ($this->hasRequestedComponentPaymentFields()) {
1234 return 'participant_id';
1240 * Have component payment fields been requested.
1244 protected function hasRequestedComponentPaymentFields() {
1245 if ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_EVENT
) {
1246 $participantPaymentFields = array_intersect_key($this->getComponentPaymentFields(), $this->getReturnProperties());
1247 if (!empty($participantPaymentFields)) {
1255 * Get fields that indicate payment fields have been requested for a component.
1257 * Ideally this should be protected but making it temporarily public helps refactoring..
1261 public function getComponentPaymentFields() {
1263 'componentPaymentField_total_amount' => ['title' => ts('Total Amount'), 'type' => CRM_Utils_Type
::T_MONEY
],
1264 'componentPaymentField_contribution_status' => ['title' => ts('Contribution Status'), 'type' => CRM_Utils_Type
::T_STRING
],
1265 'componentPaymentField_received_date' => ['title' => ts('Date Received'), 'type' => CRM_Utils_Type
::T_DATE + CRM_Utils_Type
::T_TIME
],
1266 'componentPaymentField_payment_instrument' => ['title' => ts('Payment Method'), 'type' => CRM_Utils_Type
::T_STRING
],
1267 'componentPaymentField_transaction_id' => ['title' => ts('Transaction ID'), 'type' => CRM_Utils_Type
::T_STRING
],
1272 * Get headers for payment fields.
1274 * Returns an array of contribution fields when the entity supports payment fields and specific fields
1275 * are not specified. This is a transitional function for refactoring legacy code.
1277 public function getPaymentHeaders() {
1278 if ($this->isExportPaymentFields() && !$this->isExportSpecifiedPaymentFields()) {
1279 return CRM_Utils_Array
::collect('title', $this->getcomponentPaymentFields());
1285 * Get the default properties when not specified.
1287 * In the UI this appears as 'Primary fields only' but in practice it's
1288 * most of the kitchen sink and the hallway closet thrown in.
1290 * Since CRM-952 custom fields are excluded, but no other form of mercy is shown.
1294 public function getDefaultReturnProperties() {
1295 $returnProperties = [];
1296 $fields = CRM_Contact_BAO_Contact
::exportableFields('All', TRUE, TRUE);
1297 $skippedFields = ($this->getQueryMode() === CRM_Contact_BAO_Query
::MODE_CONTACTS
) ?
[] : [
1303 foreach ($fields as $key => $var) {
1304 if ($key && (substr($key, 0, 6) != 'custom') && !in_array($key, $skippedFields)) {
1305 $returnProperties[$key] = 1;
1308 $returnProperties = array_merge($returnProperties, $this->getAdditionalReturnProperties());
1309 return $returnProperties;
1313 * Add the field to relationship return properties & return it.
1315 * This function is doing both setting & getting which is yuck but it is an interim
1318 * @param array $value
1319 * @param string $relationshipKey
1323 public function setRelationshipReturnProperties($value, $relationshipKey) {
1324 $relationField = $value['name'];
1325 $relIMProviderId = NULL;
1326 $relLocTypeId = CRM_Utils_Array
::value('location_type_id', $value);
1327 $locationName = CRM_Core_PseudoConstant
::getName('CRM_Core_BAO_Address', 'location_type_id', $relLocTypeId);
1328 $relPhoneTypeId = CRM_Utils_Array
::value('phone_type_id', $value, ($locationName ?
'Primary' : NULL));
1329 $relIMProviderId = CRM_Utils_Array
::value('im_provider_id', $value, ($locationName ?
'Primary' : NULL));
1330 if (in_array($relationField, $this->getValidLocationFields()) && $locationName) {
1331 if ($relationField === 'phone') {
1332 $this->relationshipReturnProperties
[$relationshipKey]['location'][$locationName]['phone-' . $relPhoneTypeId] = 1;
1334 elseif ($relationField === 'im') {
1335 $this->relationshipReturnProperties
[$relationshipKey]['location'][$locationName]['im-' . $relIMProviderId] = 1;
1338 $this->relationshipReturnProperties
[$relationshipKey]['location'][$locationName][$relationField] = 1;
1342 $this->relationshipReturnProperties
[$relationshipKey][$relationField] = 1;
1344 return $this->relationshipReturnProperties
[$relationshipKey];
1348 * Add the main return properties to the household merge properties if needed for merging.
1350 * If we are using household merge we need to add these to the relationship properties to
1353 public function setHouseholdMergeReturnProperties() {
1354 if ($this->isMergeSameHousehold()) {
1355 $returnProperties = $this->getReturnProperties();
1356 $returnProperties = array_diff_key($returnProperties, array_fill_keys(['location_type', 'im_provider'], 1));
1357 foreach ($this->getHouseholdRelationshipTypes() as $householdRelationshipType) {
1358 $this->relationshipReturnProperties
[$householdRelationshipType] = $returnProperties;
1364 * Get the default location fields to request.
1368 public function getValidLocationFields() {
1371 'supplemental_address_1',
1372 'supplemental_address_2',
1373 'supplemental_address_3',
1376 'postal_code_suffix',
1388 * Get the sql column definition for the given field.
1390 * @param string $fieldName
1391 * @param string $columnName
1395 public function getSqlColumnDefinition($fieldName, $columnName) {
1397 // early exit for master_id, CRM-12100
1398 // in the DB it is an ID, but in the export, we retrive the display_name of the master record
1399 // also for current_employer, CRM-16939
1400 if ($columnName == 'master_id' ||
$columnName == 'current_employer') {
1401 return "$fieldName varchar(128)";
1404 $queryFields = $this->getQueryFields();
1405 // @todo remove the enotice avoidance here, ensure all columns are declared.
1406 // tests will fail on the enotices until they all are & then all the 'else'
1408 $fieldSpec = $queryFields[$columnName] ??
[];
1410 // set the sql columns
1411 if (isset($fieldSpec['type'])) {
1412 switch ($fieldSpec['type']) {
1413 case CRM_Utils_Type
::T_INT
:
1414 case CRM_Utils_Type
::T_BOOLEAN
:
1415 if (in_array(CRM_Utils_Array
::value('data_type', $fieldSpec), ['Country', 'StateProvince', 'ContactReference'])) {
1416 return "$fieldName varchar(255)";
1418 return "$fieldName varchar(16)";
1420 case CRM_Utils_Type
::T_STRING
:
1421 if (isset($queryFields[$columnName]['maxlength'])) {
1422 return "$fieldName varchar({$queryFields[$columnName]['maxlength']})";
1425 return "$fieldName varchar(255)";
1428 case CRM_Utils_Type
::T_TEXT
:
1429 case CRM_Utils_Type
::T_LONGTEXT
:
1430 case CRM_Utils_Type
::T_BLOB
:
1431 case CRM_Utils_Type
::T_MEDIUMBLOB
:
1432 return "$fieldName longtext";
1434 case CRM_Utils_Type
::T_FLOAT
:
1435 case CRM_Utils_Type
::T_ENUM
:
1436 case CRM_Utils_Type
::T_DATE
:
1437 case CRM_Utils_Type
::T_TIME
:
1438 case CRM_Utils_Type
::T_TIMESTAMP
:
1439 case CRM_Utils_Type
::T_MONEY
:
1440 case CRM_Utils_Type
::T_EMAIL
:
1441 case CRM_Utils_Type
::T_URL
:
1442 case CRM_Utils_Type
::T_CCNUM
:
1444 return "$fieldName varchar(32)";
1448 if (substr($fieldName, -3, 3) == '_id') {
1449 return "$fieldName varchar(255)";
1451 elseif (substr($fieldName, -5, 5) == '_note') {
1452 return "$fieldName text";
1461 if (in_array($fieldName, $changeFields)) {
1462 return "$fieldName text";
1465 // set the sql columns for custom data
1466 if (isset($queryFields[$columnName]['data_type'])) {
1468 switch ($queryFields[$columnName]['data_type']) {
1470 // May be option labels, which could be up to 512 characters
1471 $length = max(512, CRM_Utils_Array
::value('text_length', $queryFields[$columnName]));
1472 return "$fieldName varchar($length)";
1475 return "$fieldName varchar(255)";
1478 return "$fieldName text";
1481 return "$fieldName varchar(255)";
1485 return "$fieldName text";
1493 * Get the munged field name.
1495 * @param string $field
1498 public function getMungedFieldName($field) {
1499 $fieldName = CRM_Utils_String
::munge(strtolower($field), '_', 64);
1500 if ($fieldName == 'id') {
1501 $fieldName = 'civicrm_primary_id';
1507 * In order to respect the history of this class we need to index kinda illogically.
1509 * On the bright side - this stuff is tested within a nano-byte of it's life.
1511 * e.g '2-a-b_Home-City'
1513 * @param string $key
1514 * @param string $relationshipType
1515 * @param string $locationType
1516 * @param $entityLabel
1520 protected function getOutputSpecificationIndex($key, $relationshipType, $locationType, $entityLabel) {
1521 if ($entityLabel ||
$key === 'im') {
1522 // Just cos that's the history...
1523 if ($key !== 'master_id') {
1524 $key = $this->getHeaderForRow($key);
1527 if (!$relationshipType ||
$key !== 'id') {
1528 $key = $this->getMungedFieldName($key);
1530 return $this->getMungedFieldName(
1531 ($relationshipType ?
($relationshipType . '_') : '')
1532 . ($locationType ?
($locationType . '_') : '')
1534 . ($entityLabel ?
('_' . $entityLabel) : '')
1539 * Get the compiled label for the column.
1541 * e.g 'Gender', 'Employee Of-Home-city'
1543 * @param string $key
1544 * @param string $relationshipType
1545 * @param string $locationType
1546 * @param string $entityLabel
1550 protected function getOutputSpecificationLabel($key, $relationshipType, $locationType, $entityLabel) {
1551 return ($relationshipType ?
$this->getRelationshipTypes()[$relationshipType] . '-' : '')
1552 . ($locationType ?
$locationType . '-' : '')
1553 . $this->getHeaderForRow($key)
1554 . ($entityLabel ?
'-' . $entityLabel : '');
1558 * Get the mysql field name key.
1560 * This key is locked in by tests but the reasons for the specific conventions -
1561 * ie. headings are used for keying fields in some cases, are likely
1562 * accidental rather than deliberate.
1564 * This key is used for the output sql array.
1566 * @param string $key
1567 * @param $relationshipType
1568 * @param $locationType
1569 * @param $entityLabel
1573 protected function getOutputSpecificationFieldKey($key, $relationshipType, $locationType, $entityLabel) {
1574 if ($entityLabel ||
$key === 'im') {
1575 if ($key !== 'state_province' && $key !== 'id') {
1576 // @todo - test removing this - indexing by $key should be fine...
1577 $key = $this->getHeaderForRow($key);
1580 if (!$relationshipType ||
$key !== 'id') {
1581 $key = $this->getMungedFieldName($key);
1583 $fieldKey = $this->getMungedFieldName(
1584 ($relationshipType ?
($relationshipType . '_') : '')
1585 . ($locationType ?
($locationType . '_') : '')
1587 . ($entityLabel ?
('_' . $entityLabel) : '')
1593 * Get params for the where criteria.
1597 public function getWhereParams() {
1598 if (!$this->isPostalableOnly()) {
1601 $params['is_deceased'] = ['is_deceased', '=', 0, CRM_Contact_BAO_Query
::MODE_CONTACTS
];
1602 $params['do_not_mail'] = ['do_not_mail', '=', 0, CRM_Contact_BAO_Query
::MODE_CONTACTS
];
1612 protected function buildRelationshipFieldsForRow(&$row, $contactID, $value, $field) {
1613 foreach (array_keys($value) as $property) {
1614 if ($property === 'location') {
1615 // @todo just undo all this nasty location wrangling!
1616 foreach ($value['location'] as $locationKey => $locationFields) {
1617 foreach (array_keys($locationFields) as $locationField) {
1618 $fieldKey = str_replace(' ', '_', $locationKey . '-' . $locationField);
1619 $row[$field . '_' . $fieldKey] = $this->getRelationshipValue($field, $contactID, $fieldKey);
1624 $row[$field . '_' . $property] = $this->getRelationshipValue($field, $contactID, $property);
1630 * Is this contact a household that is already set to be exported by virtue of it's household members.
1632 * @param int $contactID
1636 protected function isHouseholdToSkip($contactID) {
1637 return in_array($contactID, $this->householdsToSkip
);
1641 * Get the various arrays that we use to structure our output.
1643 * The extraction of these has been moved to a separate function for clarity and so that
1644 * tests can be added - in particular on the $outputHeaders array.
1646 * However it still feels a bit like something that I'm too polite to write down and this should be seen
1647 * as a step on the refactoring path rather than how it should be.
1650 * - outputColumns Array of columns to be exported. The values don't matter but the key must match the
1651 * alias for the field generated by BAO_Query object.
1652 * - headerRows Array of the column header strings to put in the csv header - non-associative.
1653 * - sqlColumns Array of column names for the temp table. Not too sure why outputColumns can't be used here.
1654 * - metadata Array of fields with specific parameters to pass to the translate function or another hacky nasty solution
1655 * I'm too embarassed to discuss here.
1657 * - to match the outputColumns keys (yes, the fact we ignore the output columns values & then pass another array with values
1658 * we could use does suggest further refactors. However, you future improver, do remember that every check you do
1659 * in the main DAO loop is done once per row & that coule be 100,000 times.)
1660 * Finally a pop quiz: We need the translate context because we use a function other than ts() - is this because
1661 * - a) the function used is more efficient or
1662 * - b) this code is old & outdated. Submit your answers to circular bin or better
1663 * yet find a way to comment them for posterity.
1665 public function getExportStructureArrays() {
1666 $outputColumns = $metadata = [];
1667 $queryFields = $this->getQueryFields();
1668 foreach ($this->getReturnProperties() as $key => $value) {
1669 if (($key != 'location' ||
!is_array($value)) && !$this->isRelationshipTypeKey($key)) {
1670 $outputColumns[$key] = $value;
1671 $this->addOutputSpecification($key);
1673 elseif ($this->isRelationshipTypeKey($key)) {
1674 $outputColumns[$key] = $value;
1675 foreach ($value as $relationField => $relationValue) {
1676 // below block is same as primary block (duplicate)
1677 if (isset($queryFields[$relationField]['title'])) {
1678 $this->addOutputSpecification($relationField, $key);
1680 elseif (is_array($relationValue) && $relationField == 'location') {
1681 // fix header for location type case
1682 foreach ($relationValue as $ltype => $val) {
1683 foreach (array_keys($val) as $fld) {
1684 $type = explode('-', $fld);
1685 $this->addOutputSpecification($type[0], $key, $ltype, CRM_Utils_Array
::value(1, $type));
1692 foreach ($value as $locationType => $locationFields) {
1693 foreach (array_keys($locationFields) as $locationFieldName) {
1694 $type = explode('-', $locationFieldName);
1696 $actualDBFieldName = $type[0];
1697 $daoFieldName = CRM_Utils_String
::munge($locationType) . '-' . $actualDBFieldName;
1699 if (!empty($type[1])) {
1700 $daoFieldName .= "-" . $type[1];
1702 $this->addOutputSpecification($actualDBFieldName, NULL, $locationType, CRM_Utils_Array
::value(1, $type));
1703 $metadata[$daoFieldName] = $this->getMetaDataForField($actualDBFieldName);
1704 $outputColumns[$daoFieldName] = TRUE;
1709 return [$outputColumns, $metadata];
1713 * Get default return property for export based on mode
1716 * Default Return property
1718 public function defaultReturnProperty() {
1719 // hack to add default return property based on export mode
1721 $exportMode = $this->getExportMode();
1722 if ($exportMode == CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
) {
1723 $property = 'contribution_id';
1725 elseif ($exportMode == CRM_Export_Form_Select
::EVENT_EXPORT
) {
1726 $property = 'participant_id';
1728 elseif ($exportMode == CRM_Export_Form_Select
::MEMBER_EXPORT
) {
1729 $property = 'membership_id';
1731 elseif ($exportMode == CRM_Export_Form_Select
::PLEDGE_EXPORT
) {
1732 $property = 'pledge_id';
1734 elseif ($exportMode == CRM_Export_Form_Select
::CASE_EXPORT
) {
1735 $property = 'case_id';
1737 elseif ($exportMode == CRM_Export_Form_Select
::GRANT_EXPORT
) {
1738 $property = 'grant_id';
1740 elseif ($exportMode == CRM_Export_Form_Select
::ACTIVITY_EXPORT
) {
1741 $property = 'activity_id';
1747 * Determine the required return properties from the input parameters.
1751 public function determineReturnProperties() {
1752 if ($this->getRequestedFields()) {
1753 $returnProperties = [];
1754 foreach ($this->getRequestedFields() as $key => $value) {
1755 $fieldName = $value['name'];
1756 $locationName = !empty($value['location_type_id']) ? CRM_Core_PseudoConstant
::getName('CRM_Core_BAO_Address', 'location_type_id', $value['location_type_id']) : NULL;
1757 $relationshipTypeKey = !empty($value['relationship_type_id']) ?
$value['relationship_type_id'] . '_' . $value['relationship_direction'] : NULL;
1758 if (!$fieldName ||
$this->isHouseholdMergeRelationshipTypeKey($relationshipTypeKey)) {
1762 if ($this->isRelationshipTypeKey($relationshipTypeKey)) {
1763 $returnProperties[$relationshipTypeKey] = $this->setRelationshipReturnProperties($value, $relationshipTypeKey);
1765 elseif ($locationName) {
1766 if ($fieldName === 'phone') {
1767 $returnProperties['location'][$locationName]['phone-' . $value['phone_type_id'] ??
NULL] = 1;
1769 elseif ($fieldName === 'im') {
1770 $returnProperties['location'][$locationName]['im-' . $value['im_provider_id'] ??
NULL] = 1;
1773 $returnProperties['location'][$locationName][$fieldName] = 1;
1777 //hack to fix component fields
1778 //revert mix of event_id and title
1779 if ($fieldName == 'event_id') {
1780 $returnProperties['event_id'] = 1;
1783 $returnProperties[$fieldName] = 1;
1787 $defaultExportMode = $this->defaultReturnProperty();
1788 if ($defaultExportMode) {
1789 $returnProperties[$defaultExportMode] = 1;
1793 $returnProperties = $this->getDefaultReturnProperties();
1795 if ($this->isMergeSameHousehold()) {
1796 $returnProperties['id'] = 1;
1798 if ($this->isMergeSameAddress()) {
1799 $returnProperties['addressee'] = 1;
1800 $returnProperties['postal_greeting'] = 1;
1801 $returnProperties['email_greeting'] = 1;
1802 $returnProperties['street_name'] = 1;
1803 $returnProperties['household_name'] = 1;
1804 $returnProperties['street_address'] = 1;
1805 $returnProperties['city'] = 1;
1806 $returnProperties['state_province'] = 1;
1809 return $returnProperties;
1813 * @param object $query
1814 * CRM_Contact_BAO_Query
1819 public function getGroupBy($query) {
1821 $returnProperties = $this->getReturnProperties();
1822 $exportMode = $this->getExportMode();
1823 $queryMode = $this->getQueryMode();
1824 if (!empty($returnProperties['tags']) ||
!empty($returnProperties['groups']) ||
1825 CRM_Utils_Array
::value('notes', $returnProperties) ||
1827 ($queryMode & CRM_Contact_BAO_Query
::MODE_CONTACTS
&& $query->_useGroupBy
)
1829 $groupBy = "contact_a.id";
1832 switch ($exportMode) {
1833 case CRM_Export_Form_Select
::CONTRIBUTE_EXPORT
:
1834 $groupBy = 'civicrm_contribution.id';
1835 if (CRM_Contribute_BAO_Query
::isSoftCreditOptionEnabled()) {
1836 // especial group by when soft credit columns are included
1837 $groupBy = ['contribution_search_scredit_combined.id', 'contribution_search_scredit_combined.scredit_id'];
1841 case CRM_Export_Form_Select
::EVENT_EXPORT
:
1842 $groupBy = 'civicrm_participant.id';
1845 case CRM_Export_Form_Select
::MEMBER_EXPORT
:
1846 $groupBy = "civicrm_membership.id";
1850 if ($queryMode & CRM_Contact_BAO_Query
::MODE_ACTIVITY
) {
1851 $groupBy = "civicrm_activity.id ";
1854 return $groupBy ?
' GROUP BY ' . implode(', ', (array) $groupBy) : '';
1858 * @param int $contactId
1862 public function replaceMergeTokens($contactId) {
1867 'postal_greeting' => $this->getPostalGreetingTemplate(),
1868 'addressee' => $this->getAddresseeGreetingTemplate(),
1870 foreach ($greetingFields as $greeting => $greetingLabel) {
1871 $tokens = CRM_Utils_Token
::getTokens($greetingLabel);
1872 if (!empty($tokens)) {
1873 if (empty($contact)) {
1878 $contact = civicrm_api('contact', 'get', $values);
1880 if (!empty($contact['is_error'])) {
1883 $contact = $contact['values'][$contact['id']];
1886 $tokens = ['contact' => $greetingLabel];
1887 $greetings[$greeting] = CRM_Utils_Token
::replaceContactTokens($greetingLabel, $contact, NULL, $tokens);
1894 * Build array for merging same addresses.
1897 * @param bool $sharedAddress
1901 public function buildMasterCopyArray($sql, $sharedAddress = FALSE) {
1903 $addresseeOptions = CRM_Core_OptionGroup
::values('addressee');
1904 $postalOptions = CRM_Core_OptionGroup
::values('postal_greeting');
1906 $merge = $parents = [];
1907 $dao = CRM_Core_DAO
::executeQuery($sql);
1909 while ($dao->fetch()) {
1910 $masterID = $dao->master_id
;
1911 $copyID = $dao->copy_id
;
1912 $masterPostalGreeting = $dao->master_postal_greeting
;
1913 $masterAddressee = $dao->master_addressee
;
1914 $copyAddressee = $dao->copy_addressee
;
1916 if (!$sharedAddress) {
1917 if (!isset($this->contactGreetingFields
[$dao->master_contact_id
])) {
1918 $this->contactGreetingFields
[$dao->master_contact_id
] = $this->replaceMergeTokens($dao->master_contact_id
);
1920 $masterPostalGreeting = CRM_Utils_Array
::value('postal_greeting',
1921 $this->contactGreetingFields
[$dao->master_contact_id
], $dao->master_postal_greeting
1923 $masterAddressee = CRM_Utils_Array
::value('addressee',
1924 $this->contactGreetingFields
[$dao->master_contact_id
], $dao->master_addressee
1927 if (!isset($contactGreetingTokens[$dao->copy_contact_id
])) {
1928 $this->contactGreetingFields
[$dao->copy_contact_id
] = $this->replaceMergeTokens($dao->copy_contact_id
);
1930 $copyPostalGreeting = CRM_Utils_Array
::value('postal_greeting',
1931 $this->contactGreetingFields
[$dao->copy_contact_id
], $dao->copy_postal_greeting
1933 $copyAddressee = CRM_Utils_Array
::value('addressee',
1934 $this->contactGreetingFields
[$dao->copy_contact_id
], $dao->copy_addressee
1938 if (!isset($merge[$masterID])) {
1939 // check if this is an intermediate child
1940 // this happens if there are 3 or more matches a,b, c
1941 // the above query will return a, b / a, c / b, c
1942 // we might be doing a bit more work, but for now its ok, unless someone
1943 // knows how to fix the query above
1944 if (isset($parents[$masterID])) {
1945 $masterID = $parents[$masterID];
1948 $merge[$masterID] = [
1949 'addressee' => $masterAddressee,
1951 'postalGreeting' => $masterPostalGreeting,
1953 $merge[$masterID]['emailGreeting'] = &$merge[$masterID]['postalGreeting'];
1956 $parents[$copyID] = $masterID;
1958 if (!$sharedAddress && !array_key_exists($copyID, $merge[$masterID]['copy'])) {
1960 if ($copyPostalGreeting) {
1961 $this->trimNonTokensFromAddressString($copyPostalGreeting,
1962 $postalOptions[$dao->copy_postal_greeting_id
],
1963 $this->getPostalGreetingTemplate()
1965 $merge[$masterID]['postalGreeting'] = "{$merge[$masterID]['postalGreeting']}, {$copyPostalGreeting}";
1966 // if there happens to be a duplicate, remove it
1967 $merge[$masterID]['postalGreeting'] = str_replace(" {$copyPostalGreeting},", "", $merge[$masterID]['postalGreeting']);
1970 if ($copyAddressee) {
1971 $this->trimNonTokensFromAddressString($copyAddressee,
1972 $addresseeOptions[$dao->copy_addressee_id
],
1973 $this->getAddresseeGreetingTemplate()
1975 $merge[$masterID]['addressee'] = "{$merge[$masterID]['addressee']}, " . trim($copyAddressee);
1978 $merge[$masterID]['copy'][$copyID] = $copyAddressee;
1985 * Merge contacts with the same address.
1987 public function mergeSameAddress() {
1989 $tableName = $this->getTemporaryTable();
1990 // check if any records are present based on if they have used shared address feature,
1991 // and not based on if city / state .. matches.
1993 SELECT r1.id as copy_id,
1994 r1.civicrm_primary_id as copy_contact_id,
1995 r1.addressee as copy_addressee,
1996 r1.addressee_id as copy_addressee_id,
1997 r1.postal_greeting as copy_postal_greeting,
1998 r1.postal_greeting_id as copy_postal_greeting_id,
2000 r2.civicrm_primary_id as master_contact_id,
2001 r2.postal_greeting as master_postal_greeting,
2002 r2.postal_greeting_id as master_postal_greeting_id,
2003 r2.addressee as master_addressee,
2004 r2.addressee_id as master_addressee_id
2006 INNER JOIN civicrm_address adr ON r1.master_id = adr.id
2007 INNER JOIN $tableName r2 ON adr.contact_id = r2.civicrm_primary_id
2009 $linkedMerge = $this->buildMasterCopyArray($sql, TRUE);
2011 // find all the records that have the same street address BUT not in a household
2012 // require match on city and state as well
2014 SELECT r1.id as master_id,
2015 r1.civicrm_primary_id as master_contact_id,
2016 r1.postal_greeting as master_postal_greeting,
2017 r1.postal_greeting_id as master_postal_greeting_id,
2018 r1.addressee as master_addressee,
2019 r1.addressee_id as master_addressee_id,
2021 r2.civicrm_primary_id as copy_contact_id,
2022 r2.postal_greeting as copy_postal_greeting,
2023 r2.postal_greeting_id as copy_postal_greeting_id,
2024 r2.addressee as copy_addressee,
2025 r2.addressee_id as copy_addressee_id
2027 LEFT JOIN $tableName r2 ON ( r1.street_address = r2.street_address AND
2028 r1.city = r2.city AND
2029 r1.state_province_id = r2.state_province_id )
2030 WHERE ( r1.household_name IS NULL OR r1.household_name = '' )
2031 AND ( r2.household_name IS NULL OR r2.household_name = '' )
2032 AND ( r1.street_address != '' )
2036 $merge = $this->buildMasterCopyArray($sql);
2038 // unset ids from $merge already present in $linkedMerge
2039 foreach ($linkedMerge as $masterID => $values) {
2040 $keys = [$masterID];
2041 $keys = array_merge($keys, array_keys($values['copy']));
2042 foreach ($merge as $mid => $vals) {
2043 if (in_array($mid, $keys)) {
2044 unset($merge[$mid]);
2047 foreach ($values['copy'] as $copyId) {
2048 if (in_array($copyId, $keys)) {
2049 unset($merge[$mid]['copy'][$copyId]);
2055 $merge = $merge +
$linkedMerge;
2057 foreach ($merge as $masterID => $values) {
2060 SET addressee = %1, postal_greeting = %2, email_greeting = %3
2064 1 => [$values['addressee'], 'String'],
2065 2 => [$values['postalGreeting'], 'String'],
2066 3 => [$values['emailGreeting'], 'String'],
2067 4 => [$masterID, 'Integer'],
2069 CRM_Core_DAO
::executeQuery($sql, $params);
2071 // delete all copies
2072 $deleteIDs = array_keys($values['copy']);
2073 $deleteIDString = implode(',', $deleteIDs);
2075 DELETE FROM $tableName
2076 WHERE id IN ( $deleteIDString )
2078 CRM_Core_DAO
::executeQuery($sql);
2083 * The function unsets static part of the string, if token is the dynamic part.
2085 * Example: 'Hello {contact.first_name}' => converted to => '{contact.first_name}'
2086 * i.e 'Hello Alan' => converted to => 'Alan'
2088 * @param string $parsedString
2089 * @param string $defaultGreeting
2090 * @param string $greetingLabel
2094 public function trimNonTokensFromAddressString(
2095 &$parsedString, $defaultGreeting,
2098 $greetingLabel = empty($greetingLabel) ?
$defaultGreeting : $greetingLabel;
2100 $stringsToBeReplaced = preg_replace('/(\{[a-zA-Z._ ]+\})/', ';;', $greetingLabel);
2101 $stringsToBeReplaced = explode(';;', $stringsToBeReplaced);
2102 foreach ($stringsToBeReplaced as $key => $string) {
2103 // to keep one space
2104 $stringsToBeReplaced[$key] = ltrim($string);
2106 $parsedString = str_replace($stringsToBeReplaced, "", $parsedString);
2108 return $parsedString;
2112 * Preview export output.
2117 public function getPreview($limit) {
2119 list($outputColumns, $metadata) = $this->getExportStructureArrays();
2120 $query = $this->runQuery([], '');
2121 CRM_Core_DAO
::disableFullGroupByMode();
2122 $result = CRM_Core_DAO
::executeQuery($query[1] . ' LIMIT ' . (int) $limit);
2123 CRM_Core_DAO
::reenableFullGroupByMode();
2124 while ($result->fetch()) {
2125 $rows[] = $this->buildRow($query[0], $result, $outputColumns, $metadata, [], []);
2131 * Set the template strings to be used when merging two contacts with the same address.
2133 * @param array $formValues
2134 * Values from first form. In this case we care about the keys
2137 * - address_greeting
2142 protected function setGreetingStringsForSameAddressMerge($formValues) {
2143 $greetingOptions = CRM_Export_Form_Select
::getGreetingOptions();
2145 if (!empty($greetingOptions)) {
2146 // Greeting options is keyed by 'postal_greeting' or 'addressee'.
2147 foreach ($greetingOptions as $key => $value) {
2148 $option = CRM_Utils_Array
::value($key, $formValues);
2150 if ($greetingOptions[$key][$option] == ts('Other')) {
2151 $formValues[$key] = $formValues["{$key}_other"];
2153 elseif ($greetingOptions[$key][$option] == ts('List of names')) {
2154 $formValues[$key] = '';
2157 $formValues[$key] = $greetingOptions[$key][$option];
2162 if (!empty($formValues['postal_greeting'])) {
2163 $this->setPostalGreetingTemplate($formValues['postal_greeting']);
2165 if (!empty($formValues['addressee'])) {
2166 $this->setAddresseeGreetingTemplate($formValues['addressee']);
2171 * Create the temporary table for output.
2173 public function createTempTable() {
2174 //creating a temporary table for the search result that need be exported
2175 $exportTempTable = CRM_Utils_SQL_TempTable
::build()->setDurable()->setCategory('export');
2176 $sqlColumns = $this->getSQLColumns();
2177 // also create the sql table
2178 $exportTempTable->drop();
2180 $sql = " id int unsigned NOT NULL AUTO_INCREMENT, ";
2181 if (!empty($sqlColumns)) {
2182 $sql .= implode(",\n", array_values($sqlColumns)) . ',';
2185 $sql .= "\n PRIMARY KEY ( id )";
2187 // add indexes for street_address and household_name if present
2191 'civicrm_primary_id',
2194 foreach ($addIndices as $index) {
2195 if (isset($sqlColumns[$index])) {
2197 INDEX index_{$index}( $index )
2202 $exportTempTable->createWithColumns($sql);
2203 $this->setTemporaryTable($exportTempTable->getName());
2207 * Get the values of linked household contact.
2209 * @param CRM_Core_DAO $relDAO
2210 * @param array $value
2211 * @param string $field
2214 * @throws \Exception
2216 public function fetchRelationshipDetails($relDAO, $value, $field, &$row) {
2217 $phoneTypes = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_Phone', 'phone_type_id');
2218 $imProviders = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_IM', 'provider_id');
2219 $i18n = CRM_Core_I18n
::singleton();
2220 $field = $field . '_';
2222 foreach ($value as $relationField => $relationValue) {
2223 if (is_object($relDAO) && property_exists($relDAO, $relationField)) {
2224 $fieldValue = $relDAO->$relationField;
2225 if ($relationField == 'phone_type_id') {
2226 $fieldValue = $phoneTypes[$relationValue];
2228 elseif ($relationField == 'provider_id') {
2229 $fieldValue = CRM_Utils_Array
::value($relationValue, $imProviders);
2232 elseif (is_object($relDAO) && in_array($relationField, [
2237 //special case for greeting replacement
2238 $fldValue = "{$relationField}_display";
2239 $fieldValue = $relDAO->$fldValue;
2242 elseif (is_object($relDAO) && $relationField == 'state_province') {
2243 $fieldValue = CRM_Core_PseudoConstant
::stateProvince($relDAO->state_province_id
);
2245 elseif (is_object($relDAO) && $relationField == 'country') {
2246 $fieldValue = CRM_Core_PseudoConstant
::country($relDAO->country_id
);
2251 $relPrefix = $field . $relationField;
2253 if (is_object($relDAO) && $relationField == 'id') {
2254 $row[$relPrefix] = $relDAO->contact_id
;
2256 elseif (is_array($relationValue) && $relationField == 'location') {
2257 foreach ($relationValue as $ltype => $val) {
2258 // If the location name has a space in it the we need to handle that. This
2259 // is kinda hacky but specifically covered in the ExportTest so later efforts to
2260 // improve it should be secure in the knowled it will be caught.
2261 $ltype = str_replace(' ', '_', $ltype);
2262 foreach (array_keys($val) as $fld) {
2263 $type = explode('-', $fld);
2264 $fldValue = "{$ltype}-" . $type[0];
2265 if (!empty($type[1])) {
2266 $fldValue .= "-" . $type[1];
2268 // CRM-3157: localise country, region (both have ‘country’ context)
2269 // and state_province (‘province’ context)
2271 case (!is_object($relDAO)):
2272 $row[$field . '_' . $fldValue] = '';
2275 case in_array('country', $type):
2276 case in_array('world_region', $type):
2277 $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue,
2278 ['context' => 'country']
2282 case in_array('state_province', $type):
2283 $row[$field . '_' . $fldValue] = $i18n->crm_translate($relDAO->$fldValue,
2284 ['context' => 'province']
2289 $row[$field . '_' . $fldValue] = $relDAO->$fldValue;
2295 elseif (isset($fieldValue) && $fieldValue != '') {
2296 //check for custom data
2297 if ($cfID = CRM_Core_BAO_CustomField
::getKeyID($relationField)) {
2298 $row[$relPrefix] = CRM_Core_BAO_CustomField
::displayValue($fieldValue, $cfID);
2301 //normal relationship fields
2302 // CRM-3157: localise country, region (both have ‘country’ context) and state_province (‘province’ context)
2303 switch ($relationField) {
2305 case 'world_region':
2306 $row[$relPrefix] = $i18n->crm_translate($fieldValue, ['context' => 'country']);
2309 case 'state_province':
2310 $row[$relPrefix] = $i18n->crm_translate($fieldValue, ['context' => 'province']);
2314 $row[$relPrefix] = $fieldValue;
2320 // if relation field is empty or null
2321 $row[$relPrefix] = '';
2327 * Write to the csv from the temp table.
2329 public function writeCSVFromTable() {
2331 $headerRows = $this->getHeaderRows();
2332 $exportTempTable = $this->getTemporaryTable();
2333 CRM_Utils_Hook
::export($exportTempTable, $headerRows, $sqlColumns, $exportMode, $componentTable, $ids);
2334 if ($exportTempTable !== $this->getTemporaryTable()) {
2335 CRM_Core_Error
::deprecatedFunctionWarning('altering the export table in the hook is deprecated (in some flows the table itself will be)');
2336 $this->setTemporaryTable($exportTempTable);
2338 $exportTempTable = $this->getTemporaryTable();
2339 $writeHeader = TRUE;
2341 // increase this number a lot to avoid making too many queries
2342 // LIMIT is not much faster than a no LIMIT query
2346 $query = "SELECT * FROM $exportTempTable";
2349 $limitQuery = $query . "
2350 LIMIT $offset, $limit
2352 $dao = CRM_Core_DAO
::executeQuery($limitQuery);
2358 $componentDetails = [];
2359 while ($dao->fetch()) {
2362 foreach (array_keys($this->getSQLColumns()) as $column) {
2363 $row[$column] = $dao->$column;
2365 $componentDetails[] = $row;
2367 CRM_Core_Report_Excel
::writeCSVFile($this->getExportFileName(),
2374 $writeHeader = FALSE;