OpenCart Tax Zones

For a customer I had to configure OpenCart zones (aka GeoZones) for all countries of the world, some 230+ in OpenCart by default, once you include the really pesky unmemorable ones. This meant matching countries to zones and making sure none got duplicated or missed.

First off, I made my GeoZones: New Zealand (home ground), Australia, UK & Europe, South Pacific, East Asia & North America, and Rest of World (ROW).  Yes of course East Asia is not near North America… I think this is because the shipping cost is roughly the same.

Then I could have had a long slog by hand, but in PhpMydamin I used this Sql to provisionally put all countries into Rest of World (ROW):

INSERT INTO cart_zone_to_geo_zone (country_id, zone_id, geo_zone_id, date_added)   SELECT country_id,0, 6, ‘2012-10-23 20:45:00’ FROM cart_country WHERE country_id NOT IN(1,2,153) ORDER BY country_id ;

My geozone id for ROW is 6. The NOT IN 1,2,153 is because I had manually added countries 1 and 2 already, as a test, and 153 is New Zealand.

Then by hand, with Wikipedia by my side, I added Europe countries to my Euro Geozone, then removed them from ROW.

Then in the same way, I added Pacific, N America and Asia countries to their geozones, and removed them from ROW.

At this point I suppose you might wonder why I did that initial INSERT – well I wanted to make sure nothing slipped through and in no geozone at all. But it’s optional.

Finally you want to check that no countries are in two geozones, and none are omitted.

The first check uses this SQL:

SELECT cart_zone_to_geo_zone.country_id, name, zone_id, count(cart_zone_to_geo_zone.country_id) as ccount 
FROM `cart_zone_to_geo_zone` 
LEFT JOIN cart_country ON cart_country.country_id = cart_zone_to_geo_zone.country_id
GROUP BY cart_zone_to_geo_zone.country_id
HAVING ccount > 1

(By the way, your table names may have a different prefix, rather than “cart_”).

Any countries which appear are in two or more geozones (unfortunately this doesn’t show you which zones, you have to do the gruntwork for that). Fix stuff then repeat the query, until you get an empty set.And of course you will want to check if any are missing, using:

SELECT cart_zone_to_geo_zone.country_id, name, zone_id, count(cart_zone_to_geo_zone.country_id) as ccount FROM `cart_zone_to_geo_zone` LEFT JOIN cart_country ON cart_country.country_id = cart_zone_to_geo_zone.country_idGROUP BY cart_zone_to_geo_zone.country_idHAVING ccount =0