From df22acd7b583f3e94b5e1356b646be434dd3f192 Mon Sep 17 00:00:00 2001 From: Jamie McClelland Date: Mon, 5 Dec 2022 16:49:21 -0500 Subject: [PATCH] ensure counties are properly imported even if ambiguous If a county has a unique name, you can import it by name. But, if there is one or more counties with the same name, the county is not properly converted to an id, and we get a foreign constraint error and/or a invalid value error. --- CRM/Contact/Import/Parser/Contact.php | 49 ++++++++++++++++++- ...dual_country_state_county_with_related.csv | 2 +- .../CRM/Contact/Import/Parser/ContactTest.php | 9 ++++ 3 files changed, 57 insertions(+), 3 deletions(-) diff --git a/CRM/Contact/Import/Parser/Contact.php b/CRM/Contact/Import/Parser/Contact.php index 2cc086a0c7..2b38376013 100644 --- a/CRM/Contact/Import/Parser/Contact.php +++ b/CRM/Contact/Import/Parser/Contact.php @@ -10,6 +10,7 @@ */ use Civi\Api4\Contact; +use Civi\Api4\County; use Civi\Api4\RelationshipType; use Civi\Api4\StateProvince; use Civi\Api4\DedupeRuleGroup; @@ -1621,16 +1622,21 @@ class CRM_Contact_Import_Parser_Contact extends CRM_Import_Parser { if ($key === 'address') { foreach ($value as $index => $address) { $stateProvinceID = $address['state_province_id'] ?? NULL; + $countyID = $address['county_id'] ?? NULL; + $countryID = $address['country_id'] ?? NULL; if ($stateProvinceID) { if (!is_numeric($stateProvinceID)) { - $params['address'][$index]['state_province_id'] = $this->tryToResolveStateProvince($stateProvinceID, $address['country_id'] ?? NULL); + $params['address'][$index]['state_province_id'] = $stateProvinceID = $this->tryToResolveStateProvince($stateProvinceID, $countryID); } - elseif (!empty($address['country_id']) && is_numeric($address['country_id'])) { + elseif ($countryID && is_numeric($countryID)) { if (!$this->checkStatesForCountry((int) $address['country_id'], [$stateProvinceID])) { $params['address'][$index]['state_province_id'] = 'invalid_import_value'; } } } + if ($countyID && !is_numeric($countyID)) { + $params['address'][$index]['county_id'] = $this->tryToResolveCounty($countyID, $stateProvinceID, $countryID); + } } } elseif (is_array($value) && !in_array($key, ['email', 'phone', 'im', 'website', 'openid'], TRUE)) { @@ -1709,4 +1715,43 @@ class CRM_Contact_Import_Parser_Contact extends CRM_Import_Parser { } } + /** + * @param string $countyID + * @param string|int|null $stateProvinceID + * @param string|int|null $countryID + * + * @return string|int + * @throws \CRM_Core_Exception + */ + private function tryToResolveCounty(string $countyID, $stateProvinceID, $countryID) { + $cacheString = $countryID . '_' . $stateProvinceID . '_' . $countyID; + if (!isset(\Civi::$statics[$cacheString])) { + $possibleCounties = $this->ambiguousOptions['county_id'][mb_strtolower($countyID)] ?? NULL; + if (!$possibleCounties || $countyID === 'invalid_import_value') { + \Civi::$statics[$cacheString] = $countyID; + } + else { + if ($stateProvinceID === NULL && $countryID === NULL) { + $countryID = \Civi::settings()->get('defaultContactCountry'); + } + $countyLookUp = County::get(FALSE) + ->addWhere('id', 'IN', $possibleCounties); + if ($countryID && is_numeric($countryID)) { + $countyLookUp->addWhere('state_province_id.country_id', '=', $countryID); + } + if ($stateProvinceID && is_numeric($stateProvinceID)) { + $countyLookUp->addWhere('state_province_id', '=', $stateProvinceID); + } + $county = $countyLookUp->execute(); + if (count($county) === 1) { + \Civi::$statics[$cacheString] = $county->first()['id']; + } + else { + \Civi::$statics[$cacheString] = 'invalid_import_value'; + } + } + } + return \Civi::$statics[$cacheString]; + } + } diff --git a/tests/phpunit/CRM/Contact/Import/Form/data/individual_country_state_county_with_related.csv b/tests/phpunit/CRM/Contact/Import/Form/data/individual_country_state_county_with_related.csv index c6b5a3e5a9..3d2398c2dd 100644 --- a/tests/phpunit/CRM/Contact/Import/Form/data/individual_country_state_county_with_related.csv +++ b/tests/phpunit/CRM/Contact/Import/Form/data/individual_country_state_county_with_related.csv @@ -4,6 +4,6 @@ Susie,Jones,susie@example.com,,,,,,,,Mum,Jones,mum@example.com,NSW,ABC,Farnell,, Susie,Jones,susie@example.com,farnell,Australia,NSW,NSW,Australia,Australia,NSW,Mum,Jones,mum@example.com,NSW,Australia,Farnell,Australia,NSW,Australia,NSW,Valid, Susie,Jones,susie@example.com,farnell,AU,New South Wales,New South Wales,AU,AU,New South Wales,Mum,Jones,mum@example.com,New South Wales,AU,Farnell,AU,New South Wales,Australia,New South Wales,Valid, Susie,Jones,susie@example.com,FARNELL,1013,New South Wales,,1013,1013,New South Wales,Mum,Jones,mum@example.com,New South Wales,1013,Farnell,1013,New South Wales,1013,New South Wales,Valid, -Susie,Jones,susie@example.com,Farnell,AUSTRALIA,,,,,,Mum,Jones,mum@example.com,,austRalia,Farnell,,,,,Valid, +Susie,Jones,susie@example.com,Farnell,AUSTRALIA,NSW,,,,,Mum,Jones,mum@example.com,NSW,austRalia,Farnell,,,,,Valid, Susie,Jones,susie@example.com,Farnell,AU,NEW South Wales,NEW South Wales,AU,AU,NEW South Wales,Mum,Jones,mum@example.com,NEW South Wales,AU,Farnell,AU,NEW South Wales,Australia,NEW South Wales,Valid, Susie,Jones,susie@example.com,,AU,My own personal fiefdom,My own personal fiefdom,AU,AU,My own personal fiefdom,Mum,Jones,mum@example.com,My own personal fiefdom,AU,Farnell,AU,My own personal fiefdom,Australia,My own personal fiefdom,Invalid,rando diff --git a/tests/phpunit/CRM/Contact/Import/Parser/ContactTest.php b/tests/phpunit/CRM/Contact/Import/Parser/ContactTest.php index 2646c6396c..dea5fbd02b 100644 --- a/tests/phpunit/CRM/Contact/Import/Parser/ContactTest.php +++ b/tests/phpunit/CRM/Contact/Import/Parser/ContactTest.php @@ -65,6 +65,7 @@ class CRM_Contact_Import_Parser_ContactTest extends CiviUnitTestCase { $this->quickCleanup(['civicrm_address', 'civicrm_phone', 'civicrm_openid', 'civicrm_email', 'civicrm_user_job', 'civicrm_relationship', 'civicrm_im', 'civicrm_website', 'civicrm_queue', 'civicrm_queue_item'], TRUE); RelationshipType::delete()->addWhere('name_a_b', '=', 'Dad to')->execute(); ContactType::delete()->addWhere('name', '=', 'baby')->execute(); + CRM_Core_DAO::executeQuery('DELETE FROM civicrm_setting WHERE name = "defaultContactCountry"'); parent::tearDown(); } @@ -1348,11 +1349,19 @@ class CRM_Contact_Import_Parser_ContactTest extends CiviUnitTestCase { * @throws \CRM_Core_Exception */ public function testImportCountryStateCounty(): void { + \Civi::settings()->set('defaultContactCountry', 1013); $countyID = County::create()->setValues([ 'name' => 'Farnell', 'abbreviation' => '', 'state_province_id' => 1640, ])->execute()->first()['id']; + // What if there are two counties with the same name? + County::create()->setValues([ + 'name' => 'Farnell', + 'abbreviation' => '', + 'state_province_id' => 1641, + ])->execute()->first()['id']; + $childKey = $this->getRelationships()['Child of']['id'] . '_a_b'; $addressCustomGroupID = $this->createCustomGroup(['extends' => 'Address', 'name' => 'Address']); $contactCustomGroupID = $this->createCustomGroup(['extends' => 'Contact', 'name' => 'Contact']); -- 2.25.1