Index: branches/5.1.x/core/units/helpers/country_states_helper.php =================================================================== diff -u -N -r13151 -r13470 --- branches/5.1.x/core/units/helpers/country_states_helper.php (.../country_states_helper.php) (revision 13151) +++ branches/5.1.x/core/units/helpers/country_states_helper.php (.../country_states_helper.php) (revision 13470) @@ -1,6 +1,6 @@ Application->getUnitOption('country-state', 'TableName'); + + $sql = 'SELECT DISTINCT cname.IsoCode, cid.StateCountryId + FROM ' . $table_name . ' cid + JOIN ' . $table_name . ' cname ON cname.CountryStateId = cid.StateCountryId + WHERE cid.StateCountryId IS NOT NULL'; + $cache = $this->Conn->GetCol($sql, 'StateCountryId'); + } + + return $cache; + } + + /** + * Checks, that country with given 3symbol ISO code has states + * + * @param string $country_code + * @return bool + */ function CountryHasStates($country_code) { - return !$country_code ? false : in_array($country_code,$this->CountriesWithStates); + return $country_code ? in_array($country_code, $this->getCountriesWithStates()) : false; } /** @@ -32,93 +58,139 @@ */ function PopulateStates(&$event, $state_field, $country_field) { - static $country_states = Array (); + static $cache = Array (); $object =& $event->getObject(); - $country_abbr = $object->GetDBField($country_field); + /* @var $object kDBItem */ - if (!$country_abbr) { + $country_iso = $object->GetDBField($country_field); + + if (!$country_iso) { return ; } - if (!array_key_exists($country_abbr, $country_states)) { - $sql = 'SELECT DestId - FROM '.TABLE_PREFIX.'StdDestinations - WHERE DestType = 1 AND DestAbbr = '.$this->Conn->qstr($country_abbr); - $country_id = $this->Conn->GetOne($sql); + if (!array_key_exists($country_iso, $cache)) { + $country_id = $this->getCountryStateId($country_iso, DESTINATION_TYPE_COUNTRY); + if (!$country_id) { return ; } - $language_id = $this->Application->GetVar('m_lang'); + // don't use GetVar('m_lang') since it's always equals to default language on editing form in admin + $current_language = $this->Application->Phrases->LanguageId; + $primary_language = $this->Application->GetDefaultLanguageId(); - $sql = 'SELECT p.l' . $language_id . '_Translation as DestName, sd.DestAbbr - FROM ' . TABLE_PREFIX . 'StdDestinations AS sd - LEFT JOIN ' . TABLE_PREFIX . 'Phrase AS p ON p.Phrase = sd.DestName - WHERE DestType = 2 AND DestParentId = ' . $country_id . ' - ORDER BY l' . $language_id . '_Translation'; - $country_states[$country_abbr] = $this->Conn->GetCol($sql, 'DestAbbr'); + $sql = 'SELECT IF(l' . $current_language . '_Name = "", l' . $primary_language . '_Name, l' . $current_language . '_Name) AS Name, IsoCode + FROM ' . $this->Application->getUnitOption('country-state', 'TableName') . ' + WHERE (Type = ' . DESTINATION_TYPE_STATE . ') AND (StateCountryId = ' . $country_id . ') + ORDER BY Name ASC'; + $cache[$country_iso] = $this->Conn->GetCol($sql, 'IsoCode'); } - $object->Fields[$state_field]['options'] = $country_states[$country_abbr]; - $object->Fields[$state_field]['options'][''] = ''; + $field_options = $object->GetFieldOptions($state_field); + + $field_options['options'] = $cache[$country_iso]; + $field_options['options'][''] = ''; + + $object->SetFieldOptions($state_field, $field_options); } /** - * Returns valid state code for state name and country code passed + * Returns valid state ISO code for state name and country code passed * * @param string $state_name - * @param string $country_code + * @param string $country_iso * @return string */ - function CheckState($state_name, $country_code) + function getStateIso($state_name, $country_iso) { - if (!$this->CountryHasStates($country_code)) { + if (!$this->CountryHasStates($country_iso)) { return $state_name; } - $sql = 'SELECT sdStates.DestAbbr - FROM '.TABLE_PREFIX.'StdDestinations AS sdStates - LEFT JOIN '.TABLE_PREFIX.'Phrase AS p ON p.Phrase = sdStates.DestName - LEFT JOIN '.TABLE_PREFIX.'StdDestinations AS sdCountries ON sdStates.DestParentId = sdCountries.DestId - WHERE (sdStates.DestType = 2) AND - (sdStates.DestParentId = sdCountries.DestId) AND - (sdCountries.DestAbbr = %2$s) AND - ( - (LOWER(sdStates.DestAbbr) = %3$s) OR (LOWER(sdStates.DestAbbr2) = %3$s) OR (LOWER(sdStates.DestName) = %3$s) OR (LOWER(p.l%1$s_Translation) = %3$s) - )'; + $table_name = $this->Application->getUnitOption('country-state', 'TableName'); + $country_id = $this->getCountryStateId($country_iso, DESTINATION_TYPE_COUNTRY); - $state_name = trim( mb_strtolower($state_name) ); + // don't use GetVar('m_lang') since it's always equals to default language on editing form in admin + $current_language = $this->Application->Phrases->LanguageId; + $primary_language = $this->Application->GetDefaultLanguageId(); - $sql = sprintf($sql, (int)$this->Application->GetVar('m_lang'), $this->Conn->qstr($country_code), $this->Conn->qstr($state_name) ); + $sql = 'SELECT IsoCode + FROM ' . $table_name . ' + WHERE (Type = ' . DESTINATION_TYPE_STATE . ') AND (StateCountryId = %1$s) AND + ( + (IsoCode = %2$s) OR (UPPER(l%3$s_Name) = %2$s) OR (UPPER(l%4$s_Name) = %2$s) + )'; + $state_name = trim( mb_strtoupper($state_name) ); + $sql = sprintf($sql, $country_id, $this->Conn->qstr($state_name), $current_language, $primary_language); + return $this->Conn->GetOne($sql); } - function CheckStateField(&$event, $state_field, $country_field) + function CheckStateField(&$event, $state_field, $country_field, $auto_required = true) { + $object =& $event->getObject(); + /* @var $object kDBItem */ - $items_info = $this->Application->GetVar( $event->getPrefixSpecial(true) ); - if($items_info) - { - list($id, $field_values) = each($items_info); - if( isset($field_values[$state_field]) && isset($field_values[$country_field]) ) - { - $user_state = getArrayValue($field_values,$state_field); - $valid_state = $this->CheckState($user_state, getArrayValue($field_values,$country_field) ); - if($valid_state !== false) - { - $field_values[$state_field] = $valid_state; - $items_info[$id] = $field_values; - $this->Application->SetVar( $event->getPrefixSpecial(true), $items_info); - } - else - { - $object =& $event->getObject(); - $object->Fields[$state_field]['options'][$user_state] = $user_state; - $object->SetError($state_field, 'invalid_state', 'la_invalid_state'); - } + $country_iso = $object->GetDBField($country_field); + + if ($auto_required) { + $object->setRequired($state_field, $this->CountryHasStates($country_iso)); + } + + $state = $object->GetDBField($state_field); + + if ($country_iso && $state) { + $state_iso = $this->getStateIso($state, $country_iso); + + if ($state_iso !== false) { + // replace state name with it's ISO code + $object->SetDBField($state_field, $state_iso); } + else { + // state not found by name -> report error + $object->SetError($state_field, 'invalid_state', 'la_invalid_state'); + } } } + + /** + * Returns country/state id based on given iso code and it's type + * + * @param string $iso_code + * @param int $type + * @return int + */ + function getCountryStateId($iso_code, $type) + { + $sql = 'SELECT ' . $this->Application->getUnitOption('country-state', 'IDField') . ' + FROM ' . $this->Application->getUnitOption('country-state', 'TableName') . ' + WHERE (Type = ' . $type . ') AND (IsoCode = ' . $this->Conn->qstr($iso_code) . ')'; + + return (int)$this->Conn->GetOne($sql); + } + + /** + * Returns 3 symbols ISO code from 2 symbols ISO code or otherwise, when $from_short parameter is used + * + * @param string $iso_code + * @param bool $from_short + * @return string + */ + function getCountryIso($iso_code, $from_short = false) + { + if ($from_short) { + $sql = 'SELECT IsoCode + FROM ' . TABLE_PREFIX . 'CountryStates + WHERE ShortIsoCode = ' . $this->Conn->qstr($iso_code) . ' AND `Type` = ' . DESTINATION_TYPE_COUNTRY; + } + else { + $sql = 'SELECT ShortIsoCode + FROM ' . TABLE_PREFIX . 'CountryStates + WHERE IsoCode = ' . $this->Conn->qstr($iso_code) . ' AND `Type` = ' . DESTINATION_TYPE_COUNTRY; + } + + return $this->Conn->GetOne($sql); + } } \ No newline at end of file