In-Portal Issue Tracker - In-Portal CMS
Viewing Issue Advanced Details
ID: Category: Type: Reproducibility: Date Submitted: Last Update:
279 [In-Portal CMS] Optimization feature request N/A 2009-09-09 07:01 2010-07-22 15:06
Reporter: alex Platform:  
Assigned To: alex OS:  
Priority: normal OS Version:  
Status: closed Product Version:  
Product Build: Resolution: fixed  
 
ETA: none Fixed in Version: 5.1.0-B1  
Reference:
Change Log Message: Refactoring Countries and States functionality and adding management options
Estimate Points: 0
Summary: 0000279: Phrases of state and country names
Description: Currently we have around 400 phrases of for countries and states used for ex. during checkout process. Translation of such kind of phrases almost never got changed and they provide additional load to each template, because they are used for Country field for users and user object is loaded on every page and these phrase with it.

I propose to create multilingual column Translation in StdDestinations table and move all current state/country translations there. Then loading of these moved values should be done using "options_sql" option in field definition in unit config.

Later we can add separate grid, where administrators could enable/disabled unneeded countries/states and change their translation.
Steps To Reproduce:
Additional Information: Same idea with currency translations.

In total we could decrease language pack by approximately 600 phrases, that never got changed.

---------

Country and state names and currencies should stay in language pack but not under <phrases> node by now. We can create special nodes for them, and language pack format will be like:

<LANGUAGES>
    <LANGUAGE PackName="English" Encoding="base64">
        <PHRASES>
            ...
        </PHRASES>
        <COUNTRIES>
            <COUNTRY iso="USA" translation=".....">
                <STATE iso="IL">...</STATE>
                ...
            </COUNTRY>
            ...
        </COUNTRIES>
        <CURRENCIES>
            ...
        </CURRENCIES>
        <EVENTS>
            ...
        </EVENTS>
    </LANGUAGE>
</LANGUAGES>

This way different language translation of countries/states/currencies could be still transferred in language packs, but won't be stored in Phrase table.
Relationships
parent of 0000773closed  (5.1.0)Dmitry New Icons for "Site Domains" and "Countries & States" configuration sections 
parent of 0000780closed  (5.1.0)alex Language pack upgrade fails, when it contains translations to non-existing email events 
related to 0000692closed  (5.0.3)alex Translatable fields are not always created during table scan 
related to 0000694closed  (5.0.3)alex SQL is partially parsed, when displayed in custom field editing form 
related to 0000640closed  (5.0.3)alex Current language is not replaced inside calculated fields, when "%2$s" is used 
related to 0001201closed  (5.2.0)alex Fatal error during Installation on States import with MySQL in Strict Mode 
Attached Files: patch country_state_refactoring_core.patch (148,485) 2010-04-11 08:34
http://tracker.in-portal.org/file_download.php?file_id=456&type=bug
patch country_state_refactoring_core_addon1.patch (14,980) 2010-04-11 08:35
http://tracker.in-portal.org/file_download.php?file_id=457&type=bug
patch country_state_refactoring_modules.patch (144,694) 2010-04-11 08:35
http://tracker.in-portal.org/file_download.php?file_id=458&type=bug
patch country_state_refactoring_themes.patch (20,008) 2010-04-11 08:35
http://tracker.in-portal.org/file_download.php?file_id=459&type=bug
patch country_state_refactoring_core_v2_(including_addon1).patch (163,836) 2010-04-27 07:26
http://tracker.in-portal.org/file_download.php?file_id=488&type=bug
patch country_state_refactoring_core_v3.patch (164,567) 2010-04-27 14:24
http://tracker.in-portal.org/file_download.php?file_id=496&type=bug
patch country_state_refactoring_modules_v2.patch (145,690) 2010-04-27 14:24
http://tracker.in-portal.org/file_download.php?file_id=497&type=bug
patch country_state_refactoring_core_v4.patch (166,718) 2010-04-28 05:47
http://tracker.in-portal.org/file_download.php?file_id=502&type=bug
patch country_state_refactoring_modules_v3.patch (146,585) 2010-04-28 05:47
http://tracker.in-portal.org/file_download.php?file_id=503&type=bug
jpg usps-fatal.jpg (487,094) 2010-05-14 18:07
http://tracker.in-portal.org/file_download.php?file_id=544&type=bug
patch in-commerce_country_fix.patch (4,113) 2010-05-15 05:17
http://tracker.in-portal.org/file_download.php?file_id=546&type=bug
patch in-commerce_country_fix_v2.patch (7,462) 2010-05-16 01:42
http://tracker.in-portal.org/file_download.php?file_id=548&type=bug
patch export_country_state_only_for_core.patch (2,360) 2010-05-18 07:46
http://tracker.in-portal.org/file_download.php?file_id=554&type=bug
Issue History
Date Modified Username Field Change
2012-02-06 04:06 alex Relationship added related to 0001201
2010-07-22 15:06 alex Note Added: 0002518
2010-07-22 15:06 alex Status resolved => closed
2010-06-15 12:25 alex Relationship added parent of 0000780
2010-06-10 14:59 Dmitry Relationship added parent of 0000773
2010-05-19 02:41 alex Note Added: 0002248
2010-05-19 02:41 alex Status reviewed and tested => resolved
2010-05-19 02:41 alex Resolution reopened => fixed
2010-05-19 02:41 alex Changeset attached 5.1.x r13583
2010-05-18 22:27 Dmitry Note Added: 0002242
2010-05-18 22:27 Dmitry Assigned To !COMMUNITY => alex
2010-05-18 22:27 Dmitry Status needs testing => reviewed and tested
2010-05-18 07:47 alex Note Added: 0002226
2010-05-18 07:47 alex Status needs feedback => needs testing
2010-05-18 07:47 alex Assigned To alex => !COMMUNITY
2010-05-18 07:47 alex Note Added: 0002225
2010-05-18 07:47 alex Status resolved => needs feedback
2010-05-18 07:47 alex Resolution fixed => reopened
2010-05-18 07:46 alex File Added: export_country_state_only_for_core.patch
2010-05-17 12:49 alex Note Added: 0002219
2010-05-17 12:49 alex Status reviewed and tested => resolved
2010-05-17 12:49 alex Resolution reopened => fixed
2010-05-17 12:49 alex Changeset attached 5.1.x r13565
2010-05-16 21:19 Dmitry Note Added: 0002216
2010-05-16 21:19 Dmitry Assigned To !COMMUNITY => alex
2010-05-16 21:19 Dmitry Status needs testing => reviewed and tested
2010-05-16 01:43 alex Note Added: 0002213
2010-05-16 01:43 alex Assigned To alex => !COMMUNITY
2010-05-16 01:43 alex Status needs work => needs testing
2010-05-16 01:42 alex File Added: in-commerce_country_fix_v2.patch
2010-05-15 20:17 Dmitry Note Added: 0002212
2010-05-15 20:17 Dmitry Assigned To !COMMUNITY => alex
2010-05-15 20:17 Dmitry Status needs testing => needs work
2010-05-15 05:20 alex Assigned To alex => !COMMUNITY
2010-05-15 05:20 alex Status needs feedback => needs testing
2010-05-15 05:19 alex Note Added: 0002209
2010-05-15 05:17 alex File Added: in-commerce_country_fix.patch
2010-05-14 18:10 Dmitry Note Edited: 0002203 bug_revision_view_page.php?bugnote_id=0002203#r482
2010-05-14 18:10 Dmitry Note Edited: 0002203 bug_revision_view_page.php?bugnote_id=0002203#r481
2010-05-14 18:07 Dmitry File Added: usps-fatal.jpg
2010-05-14 17:22 Dmitry Note Added: 0002203
2010-05-14 17:22 Dmitry Status resolved => needs feedback
2010-05-14 17:22 Dmitry Resolution fixed => reopened
2010-04-28 10:21 alex Note Added: 0002022
2010-04-28 10:21 alex Status reviewed and tested => resolved
2010-04-28 10:21 alex Fixed in Version => 5.1.0-B1
2010-04-28 10:21 alex Resolution open => fixed
2010-04-28 10:21 alex Assigned To !COMMUNITY => alex
2010-04-28 10:21 alex Changeset attached 5.1.x r13470
2010-04-28 10:11 alex Changeset attached 1.1.x r13469
2010-04-28 10:10 alex Changeset attached 1.1.x r13468
2010-04-28 10:10 alex Changeset attached 5.1.x r13467
2010-04-28 10:09 alex Changeset attached 5.1.x r13466
2010-04-28 10:08 alex Changeset attached 5.1.x r13465
2010-04-28 10:06 alex Changeset attached 5.1.x r13464
2010-04-28 10:04 alex Changeset attached 5.1.x r13463
2010-04-28 09:11 Dmitry Note Added: 0002020
2010-04-28 09:11 Dmitry Status needs testing => reviewed and tested
2010-04-28 05:50 alex Note Added: 0002002
2010-04-28 05:50 alex Assigned To alex => !COMMUNITY
2010-04-28 05:50 alex Status needs work => needs testing
2010-04-28 05:47 alex Status reviewed and tested => needs work
2010-04-28 05:47 alex File Added: country_state_refactoring_modules_v3.patch
2010-04-28 05:47 alex File Added: country_state_refactoring_core_v4.patch
2010-04-27 16:31 Dmitry Change Log Message => Refactoring Countries and States functionality and adding management options
2010-04-27 16:03 Dmitry Note Added: 0001985
2010-04-27 16:03 Dmitry Assigned To !COMMUNITY => alex
2010-04-27 16:03 Dmitry Status needs testing => reviewed and tested
2010-04-27 14:25 alex Note Added: 0001984
2010-04-27 14:25 alex Assigned To alex => !COMMUNITY
2010-04-27 14:25 alex Status needs work => needs testing
2010-04-27 14:24 alex File Added: country_state_refactoring_modules_v2.patch
2010-04-27 14:24 alex File Added: country_state_refactoring_core_v3.patch
2010-04-27 11:36 Dmitry Note Added: 0001976
2010-04-27 11:36 Dmitry Assigned To Dmitry => alex
2010-04-27 11:36 Dmitry Status needs testing => needs work
2010-04-27 07:26 alex Note Added: 0001969
2010-04-27 07:26 alex Assigned To alex => Dmitry
2010-04-27 07:26 alex Status needs work => needs testing
2010-04-27 07:26 alex File Added: country_state_refactoring_core_v2_(including_addon1).patch
2010-04-25 21:09 Dmitry Note Added: 0001945
2010-04-25 21:09 Dmitry Assigned To !COMMUNITY => alex
2010-04-25 21:09 Dmitry Status needs testing => needs work
2010-04-11 08:36 alex Time Estimate Removed 4 =>
2010-04-11 08:36 alex Note Added: 0001845
2010-04-11 08:36 alex Assigned To => !COMMUNITY
2010-04-11 08:36 alex Developer => alex
2010-04-11 08:36 alex Status active => needs testing
2010-04-11 08:35 alex File Added: country_state_refactoring_themes.patch
2010-04-11 08:35 alex File Added: country_state_refactoring_modules.patch
2010-04-11 08:35 alex File Added: country_state_refactoring_core_addon1.patch
2010-04-11 08:34 alex File Added: country_state_refactoring_core.patch
2010-04-11 08:08 alex Relationship added related to 0000640
2010-04-10 17:18 alex Relationship added related to 0000694
2010-04-10 07:26 alex Relationship added related to 0000692
2010-03-24 11:39 alex Additional Information Updated bug_revision_view_page.php?rev_id=415#r415
2010-03-24 11:30 alex Note Added: 0001744
2010-01-12 11:22 alex Time Estimate Added 4
2009-10-07 07:03 alex Additional Information Updated bug_revision_view_page.php?rev_id=259#r259
2009-09-20 15:12 alex Type bug report => feature request
2009-09-20 15:12 alex Reproducibility always => N/A
2009-09-09 15:36 Dmitry Target Version => 5.1.0
2009-09-09 07:01 alex New Issue

Notes
(0001744)
alex   
2010-03-24 11:30   
1. Rename "StdDestinations" table to "CountryStates".
2. Prefix for new unit config will be "country-state".
(0001845)
alex   
2010-04-11 08:36   
Not so much files changed, but still need to review every place where countries/states are involved. For a list of places consult changed files.

All patches from related issues should be applied for given task patches to work.
(0001945)
Dmitry   
2010-04-25 21:09   
Some issues with CORE and CORE add-on Patches (other 2 applied okay). Please review and update.
(0001969)
alex   
2010-04-27 07:26   
New patch attached.
(0001976)
Dmitry   
2010-04-27 11:36   
A few comments so far:

1. Move Country drop-down under Name field
2. Disable Country drop-down when radio-button Country is selected.

Please update and provide Patch for further tests.
(0001984)
alex   
2010-04-27 14:25   
Patch re-assembled using latest code from 5.1.x branch.
(0001985)
Dmitry   
2010-04-27 16:03   
Thanks - it's much better now.

A a few new things that I have found:

1. I think "ISO Code" should unique when adding/editing a Country, and adding/editing State in the current Country.

2. Adjust installational/upgrade SQLs so lN_Name is NULL by default

3. Clean lN_Name values when language is deleted.

4. on Add New language copy State/Country values from the Copy From language into new one.

Please commit once above completed.


TODO later:

1. Need to add IS NULL check for cases when Current language translation is NOT set and we need to use Primary (lPrimary_Name) field.

2. Create new icon
(0002002)
alex   
2010-04-28 05:50   
New patches attached.

1. I think "ISO Code" should unique when adding/editing a Country, and adding/editing State in the current Country.
Done.

2. Adjust installational/upgrade SQLs so lN_Name is NULL by default
Not done, since we could check for empty string to detect, that translation isn't available.

3. Clean lN_Name values when language is deleted.
Not done, since we haven't still decided in google groups about deleting translated data from all tables except for Phrase and Events upon language deletion.

4. on Add New language copy State/Country values from the Copy From language into new one.
Done other way, so translation from primary language is shown, when translation on current language is missing (changed 5 unit configs + 2 methods of CountryStateHelper).
(0002020)
Dmitry   
2010-04-28 09:11   
Tested good, please commit.
(0002022)
alex   
2010-04-28 10:21   
Fix committed to 5.1.x branch. Commit Message:

Fixes 0000279: Phrases of state and country names
(0002203)
Dmitry   
2010-05-14 17:22   
(edited on: 2010-05-14 18:10)
Found issues:

1. shipping_quote_engine_eh.php file, in OnBeforeUpdate is does the following code:

$from_country = $this->Application->ConfigValue('Comm_Shipping_Country');
        if (strlen($from_country) == 3) {
            $cs_helper =& $this->Application->recallObject('CountryStatesHelper');
            /* @var $cs_helper kCountryStatesHelper */

            // get 2symbol ISO code from 3symbol ISO code
            $from_country = $cs_helper->getCountryIso($from_country);
        }

which fails to return ANY ISO codes since on input "$from_country" is a number (ID).


2. Fatal error on the Shipping step when all ship. info is entered. See usps-fatal.jpg attachment. In my case I am using USPS engine, but I think it's the same for all CustomQuoteEngines.
(0002209)
alex   
2010-05-15 05:19   
1. Patch "in-commerce_country_fix.patch" fixes 1st issue from 0000279:0002203 - problem was on the other end (in configuration variable).

2. Can't replicate, since I have other code in usps.php:106 line. Are you sure you have latest file version? In mine usps.php I'm calling CountryStatesHelper and not running direct SQL to StdDestinations table.
(0002212)
Dmitry   
2010-05-15 20:17   
Thanks. I think 2. is my issue

Another quick thing, Fatal on Admin->Configuration->eCommerce->Output section:

Fatal Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT("cust_" FieldName) AS OptionValue FROM CustomField WHERE (Type = 11) AND ' at line 1 (1064)
SQL:
    SELECT Prompt AS OptionName CONCAT("cust_" FieldName) AS OptionValue
    FROM CustomField
    WHERE (Type = 11) AND (IsSystem = 0) in w:\core\kernel\application.php on line 2694


Not sure if it's related here of the task with Configuration options.
(0002213)
alex   
2010-05-16 01:43   
Patch "in-commerce_country_fix_v2.patch" fixes previous and latest problem together. Please test.

Latest found problem is related to this task, because "," was replaced with "||" inside ConfigurationValues.ValuesList field. Module "In-Commerce" had typo error in install_data.sql.
(0002216)
Dmitry   
2010-05-16 21:19   
Reviewed and tested. Please commit.
(0002219)
alex   
2010-05-17 12:49   
Fix committed to 5.1.x branch. Commit Message:

Fixes 0000279: Phrases of state and country names
(0002225)
alex   
2010-05-18 07:47   
Countries & states are exported always, even, when I export only In-Commerce language pack.
(0002226)
alex   
2010-05-18 07:47   
Patch "export_country_state_only_for_core.patch" contains fix of described before problem.
(0002242)
Dmitry   
2010-05-18 22:27   
Tested good, please commit.
(0002248)
alex   
2010-05-19 02:41   
Fix committed to 5.1.x branch. Commit Message:

Fixes 0000279: Phrases of state and country names
(0002518)
alex   
2010-07-22 15:06   
Closing, since 5.1.0 release has been released.