From 6d52bfe5704ff03b0a45f0d8292a1bf523df2b2d Mon Sep 17 00:00:00 2001 From: eileen Date: Tue, 4 Dec 2018 01:41:54 +1300 Subject: [PATCH] Exclude unpostallable contacts rather than write & delete --- CRM/Contact/BAO/Query.php | 4 +- CRM/Export/BAO/Export.php | 71 ++------------------- CRM/Export/BAO/ExportProcessor.php | 59 ++++++++++++++++- tests/phpunit/CRM/Export/BAO/ExportTest.php | 31 ++++++--- 4 files changed, 88 insertions(+), 77 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 354d871558..983110d854 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -3528,7 +3528,7 @@ WHERE $smartGroupClause * @param array $values */ public function street_address(&$values) { - list($name, $op, $value, $grouping, $wildcard) = $values; + list($name, $op, $value, $grouping) = $values; if (!$op) { $op = 'LIKE'; @@ -3921,7 +3921,7 @@ WHERE $smartGroupClause * @param $values */ public function privacy(&$values) { - list($name, $op, $value, $grouping, $wildcard) = $values; + list($name, $op, $value, $grouping) = $values; //fixed for profile search listing CRM-4633 if (strpbrk($value, "[")) { $value = "'{$value}'"; diff --git a/CRM/Export/BAO/Export.php b/CRM/Export/BAO/Export.php index 8a7679904e..565838b385 100644 --- a/CRM/Export/BAO/Export.php +++ b/CRM/Export/BAO/Export.php @@ -219,7 +219,12 @@ class CRM_Export_BAO_Export { $queryOperator = 'AND' ) { - $processor = new CRM_Export_BAO_ExportProcessor($exportMode, $fields, $queryOperator, $mergeSameHousehold); + $isPostalOnly = ( + isset($exportParams['postal_mailing_export']['postal_mailing_export']) && + $exportParams['postal_mailing_export']['postal_mailing_export'] == 1 + ); + + $processor = new CRM_Export_BAO_ExportProcessor($exportMode, $fields, $queryOperator, $mergeSameHousehold, $isPostalOnly); $returnProperties = array(); self::$relationshipTypes = $processor->getRelationshipTypes(); @@ -490,14 +495,6 @@ INSERT INTO {$componentTable} SELECT distinct gc.contact_id FROM civicrm_group_c if ($exportTempTable) { self::writeDetailsToTable($exportTempTable, $componentDetails, $sqlColumns); - // if postalMailing option is checked, exclude contacts who are deceased, have - // "Do not mail" privacy setting, or have no street address - if (isset($exportParams['postal_mailing_export']['postal_mailing_export']) && - $exportParams['postal_mailing_export']['postal_mailing_export'] == 1 - ) { - self::postalMailingFormat($exportTempTable, $sqlColumns, $exportMode); - } - // do merge same address and merge same household processing if ($mergeSameAddress) { self::mergeSameAddress($exportTempTable, $sqlColumns, $exportParams); @@ -1120,62 +1117,6 @@ LIMIT $offset, $limit } } - /** - * Exclude contacts who are deceased, have "Do not mail" privacy setting, - * or have no street address - * @param $exportTempTable - * @param $sqlColumns - * @param $exportParams - */ - public static function postalMailingFormat($exportTempTable, &$sqlColumns, $exportParams) { - $whereClause = array(); - - if (array_key_exists('is_deceased', $sqlColumns)) { - $whereClause[] = 'is_deceased = 1'; - } - - if (array_key_exists('do_not_mail', $sqlColumns)) { - $whereClause[] = 'do_not_mail = 1'; - } - - if (array_key_exists('street_address', $sqlColumns)) { - $addressWhereClause = " ( (street_address IS NULL) OR (street_address = '') ) "; - - // check for supplemental_address_1 - if (array_key_exists('supplemental_address_1', $sqlColumns)) { - $addressOptions = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, - 'address_options', TRUE, NULL, TRUE - ); - if (!empty($addressOptions['supplemental_address_1'])) { - $addressWhereClause .= " AND ( (supplemental_address_1 IS NULL) OR (supplemental_address_1 = '') ) "; - // enclose it again, since we are doing an AND in between a set of ORs - $addressWhereClause = "( $addressWhereClause )"; - } - } - - $whereClause[] = $addressWhereClause; - } - - if (!empty($whereClause)) { - $whereClause = implode(' OR ', $whereClause); - $query = " -DELETE -FROM $exportTempTable -WHERE {$whereClause}"; - CRM_Core_DAO::singleValueQuery($query); - } - - // unset temporary columns that were added for postal mailing format - if (!empty($exportParams['postal_mailing_export']['temp_columns'])) { - $unsetKeys = array_keys($sqlColumns); - foreach ($unsetKeys as $headerKey => $sqlColKey) { - if (array_key_exists($sqlColKey, $exportParams['postal_mailing_export']['temp_columns'])) { - unset($sqlColumns[$sqlColKey]); - } - } - } - } - /** * Build componentPayment fields. * diff --git a/CRM/Export/BAO/ExportProcessor.php b/CRM/Export/BAO/ExportProcessor.php index 7b1f8cf3bc..87daea1cba 100644 --- a/CRM/Export/BAO/ExportProcessor.php +++ b/CRM/Export/BAO/ExportProcessor.php @@ -79,6 +79,15 @@ class CRM_Export_BAO_ExportProcessor { */ protected $isMergeSameHousehold; + /** + * Only export contacts that can receive postal mail. + * + * Includes being alive, having an address & not having do_not_mail. + * + * @var bool + */ + protected $isPostalableOnly; + /** * Key representing the head of household in the relationship array. * @@ -127,16 +136,31 @@ class CRM_Export_BAO_ExportProcessor { * @param array|NULL $requestedFields * @param string $queryOperator * @param bool $isMergeSameHousehold + * @param bool $isPostalableOnly */ - public function __construct($exportMode, $requestedFields, $queryOperator, $isMergeSameHousehold = FALSE) { + public function __construct($exportMode, $requestedFields, $queryOperator, $isMergeSameHousehold = FALSE, $isPostalableOnly = FALSE) { $this->setExportMode($exportMode); $this->setQueryMode(); $this->setQueryOperator($queryOperator); $this->setRequestedFields($requestedFields); $this->setRelationshipTypes(); $this->setIsMergeSameHousehold($isMergeSameHousehold); + $this->setisPostalableOnly($isPostalableOnly); } + /** + * @return bool + */ + public function isPostalableOnly() { + return $this->isPostalableOnly; + } + + /** + * @param bool $isPostalableOnly + */ + public function setIsPostalableOnly($isPostalableOnly) { + $this->isPostalableOnly = $isPostalableOnly; + } /** * @return array|null */ @@ -434,6 +458,23 @@ class CRM_Export_BAO_ExportProcessor { * @return array */ public function runQuery($params, $order, $returnProperties) { + $addressWhere = ''; + $params = array_merge($params, $this->getWhereParams()); + if ($this->isPostalableOnly) { + if (array_key_exists('street_address', $returnProperties)) { + $addressWhere = " civicrm_address.street_address <> ''"; + if (array_key_exists('supplemental_address_1', $returnProperties)) { + // We need this to be an OR rather than AND on the street_address so, hack it in. + $addressOptions = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, + 'address_options', TRUE, NULL, TRUE + ); + if (!empty($addressOptions['supplemental_address_1'])) { + $addressWhere .= " OR civicrm_address.supplemental_address_1 <> ''"; + } + } + $addressWhere = ' AND (' . $addressWhere . ')'; + } + } $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL, FALSE, FALSE, $this->getQueryMode(), FALSE, TRUE, TRUE, NULL, $this->getQueryOperator() @@ -444,7 +485,7 @@ class CRM_Export_BAO_ExportProcessor { $query->_sort = $order; list($select, $from, $where, $having) = $query->query(); $this->setQueryFields($query->_fields); - return array($query, $select, $from, $where, $having); + return array($query, $select, $from, $where . $addressWhere, $having); } /** @@ -1217,4 +1258,18 @@ class CRM_Export_BAO_ExportProcessor { return $fieldKey; } + /** + * Get params for the where criteria. + * + * @return mixed + */ + public function getWhereParams() { + if (!$this->isPostalableOnly()) { + return []; + } + $params['is_deceased'] = ['is_deceased', '=', 0, CRM_Contact_BAO_Query::MODE_CONTACTS]; + $params['do_not_mail'] = ['do_not_mail', '=', 0, CRM_Contact_BAO_Query::MODE_CONTACTS]; + return $params; + } + } diff --git a/tests/phpunit/CRM/Export/BAO/ExportTest.php b/tests/phpunit/CRM/Export/BAO/ExportTest.php index 7f385b9ff2..7f8b4d7452 100644 --- a/tests/phpunit/CRM/Export/BAO/ExportTest.php +++ b/tests/phpunit/CRM/Export/BAO/ExportTest.php @@ -983,23 +983,27 @@ class CRM_Export_BAO_ExportTest extends CiviUnitTestCase { /** * Test that deceased and do not mail contacts are removed from contacts before + * + * @dataProvider getReasonsNotToMail + * + * @param array $reason + * @param array $addressReason */ - public function testExportDeceasedDoNotMail() { + public function testExportDeceasedDoNotMail($reason, $addressReason) { $contactA = $this->callAPISuccess('contact', 'create', array( 'first_name' => 'John', 'last_name' => 'Doe', 'contact_type' => 'Individual', )); - $contactB = $this->callAPISuccess('contact', 'create', array( + $contactB = $this->callAPISuccess('contact', 'create', array_merge([ 'first_name' => 'Jane', 'last_name' => 'Doe', 'contact_type' => 'Individual', - 'is_deceased' => 1, - )); + ], $reason)); //create address for contact A - $this->callAPISuccess('address', 'create', array( + $this->callAPISuccess('address', 'create', [ 'contact_id' => $contactA['id'], 'location_type_id' => 'Home', 'street_address' => 'ABC 12', @@ -1007,10 +1011,10 @@ class CRM_Export_BAO_ExportTest extends CiviUnitTestCase { 'country_id' => '1152', 'city' => 'ABC', 'is_primary' => 1, - )); + ]); //create address for contact B - $this->callAPISuccess('address', 'create', array( + $this->callAPISuccess('address', 'create', array_merge([ 'contact_id' => $contactB['id'], 'location_type_id' => 'Home', 'street_address' => 'ABC 12', @@ -1018,7 +1022,7 @@ class CRM_Export_BAO_ExportTest extends CiviUnitTestCase { 'country_id' => '1152', 'city' => 'ABC', 'is_primary' => 1, - )); + ], $addressReason)); //export and merge contacts with same address list($tableName, $sqlColumns, $headerRows, $processor) = CRM_Export_BAO_Export::exportComponents( @@ -1054,6 +1058,17 @@ class CRM_Export_BAO_ExportTest extends CiviUnitTestCase { CRM_Core_DAO::executeQuery($sql); } + /** + * Get reasons that a contact is not postalable. + * @return array + */ + public function getReasonsNotToMail() { + return [ + [['is_deceased' => 1], []], + [['do_not_mail' => 1], []], + [[], ['street_address' => '']], + ]; + } /** * @return array */ -- 2.25.1