UK counties for VM 2.0.x

[mb000000]

Having read the fora for VM1.1.x "solutions", I have taken a slightly different approach to this problem ("what problem?" - the lack of UK counties in VM2.0.x and the "unneccessary" inclusion of England, Scotland, Wales and NI).

First I simply unpublish the 4 UK "countries":

Code Select Expand


UPDATE `DB`.`jos_virtuemart_states` AS s,`DB`.`jos_virtuemart_countries` AS c SET s.published=0 WHERE s.virtuemart_country_id=c.virtuemart_country_id AND c.country_name='United Kingdom';


Next, I invented 3 and 2 letter abbreviations for the 96 counties based on the following "rules":
- for 3 letters ones, start with E, W, S, N (for England, Scotland, Wales and NI - but I've only just realised they share the same letters with compass points - seems like that ought to be on a pub quiz somewhere....)
- for 2 letter ones: if the county has two words in (e.g. North Yorkshire) use the initials (e.g. NY), otherwise use the two first letters. If there is a clash, then use the first and last letters and if there's still a clash, try something phonetic, failing that, use the first letter and effectively a random other letter.
The choice of these is not that critical since nothing makes critical use of them as far as I can determine.

The only clash that required some action was West Sussex which comes out as 2 letter code WS, but the predefined Wales 2 letter code uses that, so I change that thus:

Code Select Expand


UPDATE `DB`.`jos_virtuemart_states` AS s,`DB`.`jos_virtuemart_countries` AS c SET s.`state_2_code`='WA' WHERE s.virtuemart_country_id=c.virtuemart_country_id AND c.country_name='United Kingdom' AND s.`state_name`='Wales';


Lastly, I add in the 96 counties (if I included them all here, it exceeds the 20k posting limit, so I have attached a ZIPped .sql file with everything in):

Code Select Expand


INSERT INTO  `DB`.`jos_virtuemart_states` (`virtuemart_country_id`,`state_name`,`state_3_code`,`state_2_code`) SELECT c.virtuemart_country_id, 'Aberdeenshire', 'SAB', 'AB' FROM `DB`.`jos_virtuemart_countries` AS c WHERE c.`country_name`='United Kingdom';
<the rest>
INSERT INTO  `DB`.`jos_virtuemart_states` (`virtuemart_country_id`,`state_name`,`state_3_code`,`state_2_code`) SELECT c.virtuemart_country_id, 'Worcestershire', 'EWO', 'WO' FROM `DB`.`jos_virtuemart_countries` AS c WHERE c.`country_name`='United Kingdom';


As far as I can tell (but I don't use them, so accept there may be issues I haven't seen), there's no impact on State based tax rules.

In the examples above, replace DB is your database/schema name and jos_ with the prefix for your Joomla database

Hope this helps someone and it would be good if these mods were included in future VMs.  No one in the UK uses the four "countries" (E, S, W, NI) instead of the counties for postal purposes - in fact, a sufficient postal address in the UK is the house / building name or number and a postcode (but that's another debate).

Mark
[edit: attachment removed as I've updated it in a reply to this post]

[mb000000]

Ah, the best laid plans of mice and men.....

I noticed I seem to have left out Greater Manchester (which is embarressing really since it's local to me) and had two inconsistencies in the 3 letter names (two Welsh ones were inconsistently using N as the first letter, which by my own rules would put them in Northern Ireland - oops, sorry!)

Attached should be an updated version of the SQL file (ZIPped)

Mark
[updated to correct one more mistake - perhaps time to retire....]

[attachment cleanup by admin]