Hi
I am setting up zen cart for a store in Northern Ireland who want to offer the option of store pick up to customers from Northern Ireland only, my problem a is the store pick up is being offered to customers from the UK mainland as well which is not practical. I guess its to do with zones but cant figure out what I need to do. At preset I have my zones set up as in the Mutual Advantage UK template (The SQL listing is included below).
Any ideas on how to do this.
Thanks in advance
Steven
Zones set by the following SQL
# This next line adds the UK VAT zone
INSERT INTO geo_zones (geo_zone_name,geo_zone_description) VALUES ('UKVAT', 'UK VAT');
# Then you need some UK Tax classes
INSERT INTO tax_class (tax_class_title,tax_class_description) VALUES ('VAT', 'UK Value Added Tax (VAT)');
INSERT INTO tax_class (tax_class_title,tax_class_description) VALUES ('VAT Exempt', 'VAT Exempt');
# link tax rates to the correct tax class courtesy of DrByte - thanks
SET @t1=0;
SELECT (@t1:=geo_zone_id) as t1
FROM geo_zones
WHERE geo_zone_name = 'UKVAT' limit 1;
SET @t2=0;
SELECT (@t2:=tax_class_id) as t2
FROM tax_class
WHERE tax_class_title = 'VAT' limit 1;
INSERT INTO tax_rates (tax_zone_id,tax_class_id,tax_priority,tax_rate,tax_description) VALUES (@t1, @t2, 1, '17.5000', 'VAT @ 17.5%');
SET @t1=0;
SELECT (@t1:=geo_zone_id) as t1
FROM geo_zones
WHERE geo_zone_name = 'UKVAT' limit 1;
SET @t2=0;
SELECT (@t2:=tax_class_id) as t2
FROM tax_class
WHERE tax_class_title = 'VAT Exempt' limit 1;
INSERT INTO tax_rates (tax_zone_id,tax_class_id,tax_priority,tax_rate,tax_description) VALUES (@t1, @t2, 1, '0.0000', 'VAT Exempt');
#add a new address format for UK addresses
#Thanks to Hareslade - Paul H for the ideas.
INSERT INTO address_format (address_summary, address_format) VALUES ('$postcode / $country', '$firstname $lastname$cr$streets$cr$city$cr$state$cr$postcode$cr$country' );
# Add these new countries for a number of reasons
# 1. Non discrimination - allowing people to choose better where they live.
# 2. To assist with Royal Mail Shipping modules
# 3. Deal with increasing size of Europe
# Add Northern Ireland with UK address Format
SET @t1=0;
SELECT (@t1:= address_format_id) as t1
FROM address_format
WHERE address_format = '$firstname $lastname$cr$streets$cr$city$cr$state$cr$postcode$cr$country' limit 1;
INSERT INTO countries (countries_name,countries_iso_code_2,countries_iso_code_3,address_format_id) VALUES ('Northern Ireland','GB','GBR',@t1);
#modify country Ireland to Republic of Ireland
UPDATE countries SET countries_name = 'ROI Republic of Ireland' WHERE countries_name = 'Ireland';
# Add Scotland with UK address Format
SET @t1=0;
SELECT (@t1:= address_format_id) as t1
FROM address_format
WHERE address_format = '$firstname $lastname$cr$streets$cr$city$cr$state$cr$postcode$cr$country' limit 1;
INSERT INTO countries (countries_name,countries_iso_code_2,countries_iso_code_3,address_format_id) VALUES ('Scotland','GB','GBR',@t1);
# Add Wales with UK address Format
SET @t1=0;
SELECT (@t1:= address_format_id) as t1
FROM address_format
WHERE address_format = '$firstname $lastname$cr$streets$cr$city$cr$state$cr$postcode$cr$country' limit 1;
INSERT INTO countries (countries_name,countries_iso_code_2,countries_iso_code_3,address_format_id) VALUES ('Wales','GB','GBR',@t1);
# Add England with UK address Format
SET @t1=0;
SELECT (@t1:= address_format_id) as t1
FROM address_format
WHERE address_format = '$firstname $lastname$cr$streets$cr$city$cr$state$cr$postcode$cr$country' limit 1;
INSERT INTO countries (countries_name,countries_iso_code_2,countries_iso_code_3,address_format_id) VALUES ('England','GB','GBR',@t1);
#Add Isle of Man with UK format address
SET @t2=0;
SELECT (@t2:= address_format_id) as t2
FROM address_format
WHERE address_format = '$firstname $lastname$cr$streets$cr$city$cr$state$cr$postcode$cr$country' limit 1;
INSERT INTO countries (countries_name,countries_iso_code_2,countries_iso_code_3,address_format_id) VALUES ('Isle Of Man','GB','IMN',@t2);
# Add the Chanel Islands
INSERT INTO countries (countries_name,countries_iso_code_2,countries_iso_code_3,address_format_id) VALUES ('Channel Islands', 'CB','CBI','1');
# Add these countries in case you ship to them and use the Post Office
INSERT INTO countries (countries_name,countries_iso_code_2,countries_iso_code_3,address_format_id) VALUES ('Kosovo','CS','SCG','1');
INSERT INTO countries (countries_name,countries_iso_code_2,countries_iso_code_3,address_format_id) VALUES ('Montenegro','CS','SCG','1');
INSERT INTO countries (countries_name,countries_iso_code_2,countries_iso_code_3,address_format_id) VALUES ('Serbia','CS','SCG','1');
# modify country GB United Kingdom for new address type which is better visually
SET @t3=0;
SELECT (@t3:= address_format_id) as t3
FROM address_format
WHERE address_format = '$firstname $lastname$cr$streets$cr$city$cr$state$cr$postcode$cr$country' limit 1;
UPDATE countries SET address_format_id = @t3 WHERE countries_name = 'United Kingdom';
# The UK counties contribution below was originally developed and modified by
#
# Add all UK Counties.
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Aberdeenshire', 'Aberdeenshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Anglesey (Ynys Môn)', 'Anglesey (Ynys Môn)');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Angus', 'Angus');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Antrim', 'Antrim');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Argyll', 'Argyll');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Armagh', 'Armagh');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Avon', 'Avon');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Ayrshire', 'Ayrshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Banffshire', 'Banffshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Bedfordshire', 'Bedfordshire');
................................
Some counties removed from this post as it was too long but are in the actual SQL
.................................
'Worcestershire', 'Worcestershire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Wrexham', 'Wrexham');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Yorkshire', 'Yorkshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (222, 'Zetland', 'Zetland');
# Now split the UK counties into UK regions - Scotland, Wales, NI, CI & England.
# This 'may' help with localised delivery and customer bases - It does *not* affect the 'standard' UK options!
# Add Northern Ireland
SET @t1=0;
SELECT (@t1:=countries_id) as t1
FROM countries
WHERE countries_name = 'Northern Ireland' limit 1;
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES(@t1,'Antrim','Antrim');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES(@t1,"Armagh",'Armagh');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES(@t1,'Derry','Derry');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES(@t1,'Down','Down');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES(@t1,'Fermanagh','Fermanagh');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES(@t1,'Londonderry','Londonderry');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES(@t1,'Tyrone','Tyrone');
# Add Scotland
SET @t1=0;
SELECT (@t1:=countries_id) as t1
FROM countries
WHERE countries_name = 'Scotland' limit 1;
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Aberdeenshire','Aberdeenshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Angus','Angus');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Argyll','Argyll');
................................
Some counties removed from this post as it was too long but are in the actual SQL
.................................
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Western Isles','Western Isles');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Wigtownshire','Wigtownshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Zetland','Zetland');
# Add Wales
SET @t1=0;
SELECT (@t1:=countries_id) as t1
FROM countries
WHERE countries_name = 'Wales' limit 1;
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Merthyr Tydfil ','Merthyr Tydfil ');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Caerphilly ','Caerphilly ');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Blaenau Gwent','Blaenau Gwent');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Torfaen','Torfaen');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Monmouthshire','Monmouthshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Newport','Newport');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Cardiff','Cardiff');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Vale of Glamorgan','Vale of Glamorgan');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Bridgend','Bridgend');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Rhondda Cynon Taf','Rhondda Cynon Taf');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Neath Port Talbot','Neath Port Talbot');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Swansea','Swansea');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Carmarthenshire','Carmarthenshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Ceredigion','Ceredigion');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Powys','Powys');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Wrexham','Wrexham');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Flintshire','Flintshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Denbighshire','Denbighshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Conwy','Conwy');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Gwynedd','Gwynedd');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Anglesey (Ynys Môn)','Anglesey (Ynys Môn)');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Pembrokeshire','Pembrokeshire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Clwyd','Clwyd');
# Add England
SET @t1=0;
SELECT (@t1:=countries_id) as t1
FROM countries
WHERE countries_name = 'England' limit 1;
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Avon','Avon');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Bedfordshire','Bedfordshire');
................................
Some counties removed from this post as it was too long but are in the actual SQL
.................................
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Worcestershire','Worcestershire');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Yorkshire','Yorkshire');
# Add Channel Islands
SET @t1=0;
SELECT (@t1:=countries_id) as t1
FROM countries
WHERE countries_name = 'Channel Islands' limit 1;
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Jersey','Jersey');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Guernsey','Guernsey');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Alderney','Alderney');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Sark','Sark');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Herm','Herm');
# Add Isle Of Man
SET @t1=0;
SELECT (@t1:=countries_id) as t1
FROM countries
WHERE countries_name = 'Isle Of Man' limit 1;
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Isle of Man','Isle of Man');
# Add ROI Republic of Ireland
SET @t1=0;
SELECT (@t1:=countries_id) as t1
FROM countries
WHERE countries_name = 'ROI Republic of Ireland' limit 1;
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Carlow - Ceatharlach','Carlow - Ceatharlach');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Cavan - Cabhán','Cavan - Cabhán');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Clare - Clár','Clare - Clár');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Cork - Corcaigh','Cork - Corcaigh');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Donegal -Dún na nGall','Donegal -Dún na nGall');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Dublin - Baile Átha Cliath','Dublin - Baile Átha Cliath');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Galway - Gaillimh','Galway - Gaillimh');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Kerry - Ciarrai','Kerry - Ciarrai');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Kildare - Cill Dara','Kildare - Cill Dara');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Kilkenny - Cill Chainnigh','Kilkenny - Cill Chainnigh');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Laois - Laois','Laois - Laois');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Leitrim - Leitroim','Leitrim - Leitroim');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Limerick - Luimneach','Limerick - Luimneach');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Longford - Longfort','Longford - Longfort');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Louth - Lú','Louth - Lú');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Mayo - Maigh Eo','Mayo - Maigh Eo');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Meath - Mi','Meath - Mi');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Monaghan - Muineacháin','Monaghan - Muineacháin');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Offaly - Ua Fáilghe','Offaly - Ua Fáilghe');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Roscommon - Ros Comán','Roscommon - Ros Comán');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Sligo - Sligheach','Sligo - Sligheach');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Tipperary - Tiobraid Arainn','Tipperary - Tiobraid Arainn');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Waterford - Port Lairge','Waterford - Port Lairge');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Westmeath - Iarmhí','Westmeath - Iarmhí');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Wexford - Loch Garman','Wexford - Loch Garman');
INSERT INTO zones (zone_country_id,zone_code,zone_name) VALUES (@t1, 'Wicklow - Cill Mhantáin','Wicklow - Cill Mhantáin');
# Assign the UKVAT class to the whole of the UK
# Including the various counties and 'new' countries we've included that make up the UK.
#Assign the UKVAT class to Whole of UK.
SET @t1=0;
SELECT (@t1:=geo_zone_id) as t1
FROM geo_zones
WHERE geo_zone_name = 'UKVAT' limit 1;
INSERT INTO zones_to_geo_zones (zone_country_id,zone_id,geo_zone_id) VALUES (222,0,@t1);
#Assign the UKVAT class to Northern Ireland.
SET @t1=0;
SELECT (@t1:=geo_zone_id) as t1
FROM geo_zones
WHERE geo_zone_name = 'UKVAT' limit 1;
SET @t2=0;
SELECT (@t2:=countries_id) as t2
FROM countries
WHERE countries_name = 'Northern Ireland' limit 1;
INSERT INTO zones_to_geo_zones (zone_country_id,zone_id,geo_zone_id) VALUES (@t2,0,@t1);
#Assign the UKVAT class to Scotland.
SET @t1=0;
SELECT (@t1:=geo_zone_id) as t1
FROM geo_zones
WHERE geo_zone_name = 'UKVAT' limit 1;
SET @t2=0;
SELECT (@t2:=countries_id) as t2
FROM countries
WHERE countries_name = 'Scotland' limit 1;
INSERT INTO zones_to_geo_zones (zone_country_id,zone_id,geo_zone_id) VALUES (@t2,0,@t1);
#Assign the UKVAT class to Wales.
SET @t1=0;
SELECT (@t1:=geo_zone_id) as t1
FROM geo_zones
WHERE geo_zone_name = 'UKVAT' limit 1;
SET @t2=0;
SELECT (@t2:=countries_id) as t2
FROM countries
WHERE countries_name = 'Wales' limit 1;
INSERT INTO zones_to_geo_zones (zone_country_id,zone_id,geo_zone_id) VALUES (@t2,0,@t1);
#Assign the UKVAT class to England.
SET @t1=0;
SELECT (@t1:=geo_zone_id) as t1
FROM geo_zones
WHERE geo_zone_name = 'UKVAT' limit 1;
SET @t2=0;
SELECT (@t2:=countries_id) as t2
FROM countries
WHERE countries_name = 'England' limit 1;
INSERT INTO zones_to_geo_zones (zone_country_id,zone_id,geo_zone_id) VALUES (@t2,0,@t1);
#Assign NO UKVAT class to the Channel Islands - VAT Exempt.
#Assign the UKVAT class to the New Country of Isle of Man.
SET @t1=0;
SELECT (@t1:=geo_zone_id) as t1
FROM geo_zones
WHERE geo_zone_name = 'UKVAT' limit 1;
SET @t2=0;
SELECT (@t2:=countries_id) as t2
FROM countries
WHERE countries_name = 'Isle of Man' limit 1;
INSERT INTO zones_to_geo_zones (zone_country_id,zone_id,geo_zone_id) VALUES (@t2,0,@t1);
Bookmarks