Author Topic: UK Specific Country Counties  (Read 11215 times)

chris.t.uk

  • Contributing Developer
  • Jr. Member
  • *
  • Posts: 98
UK Specific Country Counties
« on: November 20, 2007, 15:55:54 pm »
I've used the following to enable consumers in the UK to select their country and then select their county, the modifications allow the follow through to the Address Labels to show correct and full postal addresses, (not an abbreviated county code).

i.e.
Cust Name
123 The Road
Line 2
Their Town
County
Country
Postcode

Database alteration

1) Need to make some alterations to the database (enlarging fields, saves messing with the core Virtuemart code)

•   alter table jos_vm_state change state_2_code state_2_code VARCHAR(64);
•   alter table jos_vm_country change country_2_code country_2_code varchar(64);
•   alter table jos_vm_country change country_3_code country_3_code varchar(64);

2) Now insert the country names:

INSERT INTO jos_vm_country (country_name, country_2_code, country_3_code) VALUES
(‘England’, ‘England’, ‘England’),
(‘Scotland’, ‘Scotland’, ‘Scotland’),
(‘Wales’, ‘Wales’, ‘Wales’),
(‘Northern Ireland’, ‘Northern Ireland’, ‘Northern Ireland’);

3) Check the insert worked ok and use the data to modify provided queries if needed

SELECT * FROM jos_vm_country WHERE country_name = ‘England’;
SELECT * FROM jos_vm_country WHERE country_name = ‘Scotland’;
SELECT * FROM jos_vm_country WHERE country_name = ‘Wales’;
SELECT * FROM jos_vm_country WHERE country_name = ‘Northern Ireland’;

Make sure the values above return 240, 241, 242 & 243 respectively, if not then  you will need to change the numbers below.

4) Run the inserts under County Inserts below.

5) After insert run following

•   UPDATE jos_vm_state SET state_2_code = state_name, state_3_code = state_name WHERE country_id >= 240;

This just sets all fields to the county name.

6) Into the admin of Virtuemart and

Admin->Configuration->Global->Front End Features-> Customers can select a state/region? TICK

7) You can then go in to,
Admin->Country->List Countries->Select & Delete all of the other countries or not as you wish. Or maybe just delete United Kingdom

County Inserts

Note: Stolen from the Paypal payment page

INSERT INTO jos_vm_state (country_id, state_name) VALUES

(240, ‘Avon’),
(240, ‘Bedfordshire’),
(240, ‘Berkshire’), 
(240, ‘Bristol’),
(240, ‘Buckinghamshire’),
(240, ‘Cambridgeshire’),
(240, ‘Cheshire’),
(240, ‘Cleveland’),
(240, ‘Cornwall’),
(240, ‘Cumbria’),
(240, ‘Derbyshire’),
(240, ‘Devon’),
(240, ‘Dorset’),
(240, ‘Durham’),
(240, ‘East Riding of Yorkshire’),
(240, ‘East Sussex’),
(240, ‘Essex’),
(240, ‘Gloucestershire’),
(240, ‘Greater Manchester’),
(240, ‘Hampshire’),
(240, ‘Herefordshire’),
(240, ‘Hertfordshire’),
(240, ‘Humberside’),
(240, ‘Isle of Wight’),
(240, ‘Isles of Scilly’),
(240, ‘Kent’),
(240, ‘Lancashire’),
(240, ‘Leicestershire’),
(240, ‘Lincolnshire’),
(240, ‘London’),
(240, ‘Merseyside’),
(240, ‘Middlesex’),
(240, ‘Norfolk’),
(240, ‘North Yorkshire’),
(240, ‘Northamptonshire’),
(240, ‘Northumberland’),
(240, ‘Nottinghamshire’),
(240, ‘Oxfordshire’),
(240, ‘Rutland’),
(240, ‘Shropshire’),
(240, ‘Somerset’),
(240, ‘South Yorkshire’),
(240, ‘Staffordshire’),
(240, ‘Suffolk’),
(240, ‘Surrey’),
(240, ‘Tyne and Wear’),
(240, ‘Warwickshire’),
(240, ‘West Midlands’),
(240, ‘West Sussex’),
(240, ‘West Yorkshire’),
(240, ‘Wiltshire’),
(240, ‘Worcestershire’);

INSERT INTO jos_vm_state (country_id, state_name) VALUES

(241, ‘Aberdeen City’),
(241, ‘Aberdeenshire’),
(241, ‘Angus’),
(241, ‘Argyll and Bute’),
(241, ‘Borders’),
(241, ‘Clackmannan’),
(241, ‘Dumfries and Galloway’),
(241, ‘East Ayrshire’),
(241, ‘East Dunbartonshire’),
(241, ‘East Lothian’),
(241, ‘East Renfrewshire’),
(241, ‘Edinburgh City’),
(241, ‘Falkirk’),
(241, ‘Fife’),
(241, ‘Glasgow (City of)’),
(241, ‘Highland’),
(241, ‘Inverclyde’),
(241, ‘Midlothian’),
(241, ‘Moray’),
(241, ‘North Ayrshire’),
(241, ‘North Lanarkshire’),
(241, ‘Orkney’),
(241, ‘Perthshire and Kinross’),
(241, ‘Renfrewshire’),
(241, ‘Roxburghshire’),
(241, ‘Shetland’),
(241, ‘South Ayrshire’),
(241, ‘South Lanarkshire’),
(241, ‘Stirling’),
(241, ‘West Dunbartonshire’),
(241, ‘West Lothian’),
(241, ‘Western Isles’);


INSERT INTO jos_vm_state (country_id, state_name) VALUES

(242, ‘Blaenau Gwent’),
(242, ‘Bridgend’),
(242, ‘Caerphilly’),
(242, ‘Cardiff’),
(242, ‘Carmarthenshire’),
(242, ‘Ceredigion’),
(242, ‘Conwy’),
(242, ‘Denbighshire’),
(242, ‘Flintshire’),
(242, ‘Gwynedd’),
(242, ‘Isle of Anglesey’),
(242, ‘Merthyr Tydfil’),
(242, ‘Monmouthshire’),
(242, ‘Neath Port Talbot’),
(242, ‘Newport’),
(242, ‘Pembrokeshire’),
(242, ‘Powys’),
(242, ‘Rhondda Cynon Taff’),
(242, ‘Swansea’),
(242, ‘Torfaen’),
(242, ‘The Vale of Glamorgan’),
(242, ‘Wrexham’);

INSERT INTO jos_vm_state (country_id, state_name) VALUES
(243, ‘Antrim’),
(243, ‘Armagh’),
(243, ‘Down’),
(243, ‘Fermanagh’),
(243, ‘Londonderry’),
(243, ‘Tyrone’);
Hacks
Export Orders & Batch Update -> http://forum.virtuemart.net/index.php?topic=52215.0
UK Counties -> http://forum.virtuemart.net/index.php?topic=33968.0
Sites running Virtuemart
http://www.ladders247.co.uk http://www.tools247.co.uk http://www.mackay.co.uk
Synchronization
Synchronized with our instore system via PrePrepSQL and missing Image Remote Retrieval system ;-)

chris.t.uk

  • Contributing Developer
  • Jr. Member
  • *
  • Posts: 98
Re: UK Specific Country Counties
« Reply #1 on: October 10, 2008, 12:51:08 pm »
Version 1.1.2 Slight Change

Database alteration

1) Need to make some alterations to the database (enlarging fields, saves messing with the core Virtuemart code)

•   alter table jos_vm_state change state_2_code state_2_code VARCHAR(64);
•   alter table jos_vm_state change state_3_code state_3_code VARCHAR(64);
•   alter table jos_vm_country change country_2_code country_2_code varchar(64);
•   alter table jos_vm_country change country_3_code country_3_code varchar(64);

2) Now insert the country names:

INSERT INTO jos_vm_country (country_name, country_2_code, country_3_code) VALUES
('England', 'England', 'England'),
('Scotland', 'Scotland', 'Scotland'),
('Wales', 'Wales', 'Wales'),
('Northern Ireland', 'Northern Ireland', 'Northern Ireland');


3) Check the insert worked ok and use the data to modify provided queries if needed

SELECT * FROM jos_vm_country WHERE country_name = ‘England’;
SELECT * FROM jos_vm_country WHERE country_name = ‘Scotland’;
SELECT * FROM jos_vm_country WHERE country_name = ‘Wales’;
SELECT * FROM jos_vm_country WHERE country_name = ‘Northern Ireland’;

Make sure the values above return 245, 246, 247 & 248 respectively, if not then  you will need to change the numbers below.

4) Run the inserts below.

Note: Stolen from the Paypal payment page

INSERT INTO jos_vm_state (country_id, state_name) VALUES

(245, 'Avon'),
(245, 'Bedfordshire'),
(245, 'Berkshire'),
(245, 'Bristol'),
(245, 'Buckinghamshire'),
(245, 'Cambridgeshire'),
(245, 'Cheshire'),
(245, 'Cleveland'),
(245, 'Cornwall'),
(245, 'Cumbria'),
(245, 'Derbyshire'),
(245, 'Devon'),
(245, 'Dorset'),
(245, 'Durham'),
(245, 'East Riding of Yorkshire'),
(245, 'East Sussex'),
(245, 'Essex'),
(245, 'Gloucestershire'),
(245, 'Greater Manchester'),
(245, 'Hampshire'),
(245, 'Herefordshire'),
(245, 'Hertfordshire'),
(245, 'Humberside'),
(245, 'Isle of Wight'),
(245, 'Isles of Scilly'),
(245, 'Kent'),
(245, 'Lancashire'),
(245, 'Leicestershire'),
(245, 'Lincolnshire'),
(245, 'London'),
(245, 'Merseyside'),
(245, 'Middlesex'),
(245, 'Norfolk'),
(245, 'North Yorkshire'),
(245, 'Northamptonshire'),
(245, 'Northumberland'),
(245, 'Nottinghamshire'),
(245, 'Oxfordshire'),
(245, 'Rutland'),
(245, 'Shropshire'),
(245, 'Somerset'),
(245, 'South Yorkshire'),
(245, 'Staffordshire'),
(245, 'Suffolk'),
(245, 'Surrey'),
(245, 'Tyne and Wear'),
(245, 'Warwickshire'),
(245, 'West Midlands'),
(245, 'West Sussex'),
(245, 'West Yorkshire'),
(245, 'Wiltshire'),
(245, 'Worcestershire');

INSERT INTO jos_vm_state (country_id, state_name) VALUES

(246, 'Aberdeen City'),
(246, 'Aberdeenshire'),
(246, 'Angus'),
(246, 'Argyll and Bute'),
(246, 'Borders'),
(246, 'Clackmannan'),
(246, 'Dumfries and Galloway'),
(246, 'East Ayrshire'),
(246, 'East Dunbartonshire'),
(246, 'East Lothian'),
(246, 'East Renfrewshire'),
(246, 'Edinburgh City'),
(246, 'Falkirk'),
(246, 'Fife'),
(246, 'Glasgow (City of)'),
(246, 'Highland'),
(246, 'Inverclyde'),
(246, 'Midlothian'),
(246, 'Moray'),
(246, 'North Ayrshire'),
(246, 'North Lanarkshire'),
(246, 'Orkney'),
(246, 'Perthshire and Kinross'),
(246, 'Renfrewshire'),
(246, 'Roxburghshire'),
(246, 'Shetland'),
(246, 'South Ayrshire'),
(246, 'South Lanarkshire'),
(246, 'Stirling'),
(246, 'West Dunbartonshire'),
(246, 'West Lothian'),
(246, 'Western Isles');


INSERT INTO jos_vm_state (country_id, state_name) VALUES

(247, 'Blaenau Gwent'),
(247, 'Bridgend'),
(247, 'Caerphilly'),
(247, 'Cardiff'),
(247, 'Carmarthenshire'),
(247, 'Ceredigion'),
(247, 'Conwy'),
(247, 'Denbighshire'),
(247, 'Flintshire'),
(247, 'Gwynedd'),
(247, 'Isle of Anglesey'),
(247, 'Merthyr Tydfil'),
(247, 'Monmouthshire'),
(247, 'Neath Port Talbot'),
(247, 'Newport'),
(247, 'Pembrokeshire'),
(247, 'Powys'),
(247, 'Rhondda Cynon Taff'),
(247, 'Swansea'),
(247, 'Torfaen'),
(247, 'The Vale of Glamorgan'),
(247, 'Wrexham');

INSERT INTO jos_vm_state (country_id, state_name) VALUES
(248, 'Antrim'),
(248, 'Armagh'),
(248, 'Down'),
(248, 'Fermanagh'),
(248, 'Londonderry'),
(248, 'Tyrone');


5) After insert run following

•   UPDATE jos_vm_state SET state_2_code = state_name, state_3_code = state_name WHERE country_id >= 245;

This just sets all fields to the county name.

6) Into the admin of Virtuemart and

Admin->Configuration->Global->Front End Features-> Customers can select a state/region? TICK

7) You can then go in to,
Admin->Country->List Countries->Select & Delete all of the other countries or not as you wish. Or maybe just delete United Kingdom

8: File Update
/administrator/components/com_virtuemart/classes/ps_country.php

function &get_country_by_code( $code ) {

In switch ($country_code_type)

   default:
      $country_code_type_field = 'country_2_code';
      //return false;
Hacks
Export Orders & Batch Update -> http://forum.virtuemart.net/index.php?topic=52215.0
UK Counties -> http://forum.virtuemart.net/index.php?topic=33968.0
Sites running Virtuemart
http://www.ladders247.co.uk http://www.tools247.co.uk http://www.mackay.co.uk
Synchronization
Synchronized with our instore system via PrePrepSQL and missing Image Remote Retrieval system ;-)

Datatonic

  • Jr. Member
  • **
  • Posts: 95
    • Datatonic
Re: UK Specific Country Counties
« Reply #2 on: February 01, 2009, 04:42:21 am »
I've taken this a step further see this post for details:-

http://forum.virtuemart.net/index.php?topic=51071.0

Mizpah

  • Beginner
  • *
  • Posts: 29
Re: UK Specific Country Counties
« Reply #3 on: April 09, 2009, 05:03:28 am »
Two issues;

(Am using 1.1.3 and J1.5.10)

Step 6, it says;

Into the admin of Virtuemart and

Admin->Configuration->Global->Front End Features-> Customers can select a state/region? TICK

I can find the front end features, but I dont have a state/region option ?



Also Step 8)

After the edit I now have the following;

Code: [Select]
function &get_country_by_code( $code ) {
$db = new ps_DB();
$country_code_type = strlen( $code );
switch ($country_code_type) {
case 2:
$country_code_type_field = 'country_2_code';
break;
case 3:
$country_code_type_field = 'country_3_code';
break;
default:
$country_code-type_field = 'country_2_code';
//return false;


However upon completing a purchase, I see the following issue on the PO ?

Country:
Parse error: syntax error, unexpected '=' in /data03/jobeky/public_html/administrator/components/com_virtuemart/classes/ps_country.php on line 44

Line 44 of course refers to the edit to the default case statement above!

Thanks for any help!

RCheesley

  • Jr. Member
  • **
  • Posts: 81
Re: UK Specific Country Counties
« Reply #4 on: November 01, 2010, 16:49:08 pm »
Not sure if you fixed this but I assume the problem is your syntax

Code: [Select]
default:
$country_code-type_field = 'country_2_code';
//return false;

Should be _ not - between code & type :)

RCheesley

  • Jr. Member
  • **
  • Posts: 81
Re: UK Specific Country Counties
« Reply #5 on: February 21, 2011, 12:51:01 pm »
Not sure if anyone came across this but doing the changes above screws up the tax rates - as they are country/region specific.  It's now saying it can't find the country so am looking into a workaround!