January 19th, 2011
The standard ‘zones’ facility of osCommerce has an attribute ‘zone_code’ which at first glance looks as though it might support a standardised shipping quotation system, but osCommerce comes with a database populated with zone codes that are not at all standard. The kinds of codes that are present in the database are what a person who likes abbreviating things might write on an envelope, such as ‘NY’ for New York or ‘KL’ for Kuala Lumpur (or Kirkby Lonsdale).
The default data is completely unsuited to automated shipping quotations, so I recommend you add the ISO standard ISO 3166-2 data to your zones table. The spider.my shipping quotation API will only work with ISO 3166 codes, so unless your data complies with that standard you won’t be able to use the spider.my API.
The changes to the back-end database shown here will (unless you make a mistake and type something else entirely) have no effect on the normal operation of your shop. The changes are purely to add extra information needed for any external or future service that requires standard ISO 3166-2 zones. Note that you cannot just change the existing ‘zone_code’ as osCommerce (and possibly others) uses the zone code to print addresses. As far as I know, the postman will not recognise ‘MY-14’ on an address label.
Add ISO 3166-2 zones to a fresh (no customer addresses) osCommerce / Zen Cart
Here is some SQL that will do the job on a freshly installed osCommerce. IMPORTANT! If your osCommerce installation already has customers using Malaysian zones you have set up yourself, I recommend you execute only the first ‘ALTER TABLE’ line and use a tool like phpMyAdmin to add the values for the iso_3166_2 column by hand. If you run the full set of commands below on an already populated database, you will corrupt your customer / delivery address book.
ALTER TABLE zones ADD COLUMN iso_3166_2 varchar(6);
SELECT @cid:=countries_id from countries where countries_name = 'Malaysia' limit 1;
DELETE FROM zones WHERE zone_country_id = @cid and iso_3166_2 is null;
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Johor','Johor','MY-01');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Kedah','Kedah','MY-02');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Kelantan','Kelantan','MY-03');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'KL','Kuala Lumpur','MY-14');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Labuan','Labuan','MY-15');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Malacca','Malacca','MY-04');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Negeri Sembilan','Negeri Sembilan','MY-05');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Pahang','Pahang','MY-06');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Penang','Penang','MY-07');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Perak','Perak','MY-08');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Perlis','Perlis','MY-09');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Putrajaya','Putrajaya','MY-16');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Sabah','Sabah','MY-12');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Sarawak','Sarawak','MY-13');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Selangor','Selangor','MY-10');
INSERT INTO zones (zone_country_id, zone_code, zone_name, iso_3166_2) VALUES (@cid,'Terengganu','Terengganu','MY-11');
That’s it. When customers enter a Malaysian address into your shop, they’ll have to choose an existing zone (state), which will enable ISO 3166 -based shipping quotations for your shop (if you install such a shipping module!).
Add ISO 3166-2 zones to osCommerce / Zen Cart when customer addresses exist
The task of adding ISO 3166-2 codes to your zones is more difficult if you already have addresses on your system. If you already have Malaysian zones configured, you can simply modify the zones’ iso_3166_2 column by hand, and your Malaysian customer addresses will qualify for shipping quotes by state.
The most difficult circumstance – one which is likely to apply for any shop that has been in business for any amount of time – is where there are lots of customer address records but no Malaysian state zone records. In this case osCommerce will have been allowing ‘free text’ input to the ‘State/Province’ field. To apply the iso_3166_2 column update in this case requires a great deal of manual effort. Take the following steps:
1. BACKUP YOUR DATABASE!
2. Add the iso_3166_2 column to your zones table:
ALTER TABLE zones ADD COLUMN iso_3166_2 varchar(6);
3. Get the id of Malaysia from the countries table:
select * from countries where countries_name like 'Malaysia%';
4. Find out what zones are already set up for Malaysia. I’m going to use [MYID] for the id found in step 3:
select * from zones where zone_country_id = [MYID];
5. If there are no such zones, use the ‘INSERT’ statements above to create them. If the zones already exist, manually update them so that they show the correct state name and correct ISO 3166-2 code, like:
update zones set zone_name='Malacca', iso_3166_2='MY-04' where zone_id = [Malacca'sZoneID];
6. Now the zones are prepared, check to see how many address entries need updating:
select count(address_book_id) from address_book left join zones on entry_zone_id = zone_id where entry_country_id = [MYID] and zone_id is null;
If this number is larger than about 20, make sure you’re not feeling drowsy before proceeding. What follows is very dull and an error could be catastrophic.
7. Print out the results from step 4, they’ll be handy for reference.
8. What needs to be done is to match up the address book records with the new ISO 3166-2 zones. If you’re lucky, there may be some data (the only shop I have tried this on with many existing customers, this field was blank) in the address book’s entry_state column – start there:
select distinct entry_state from zones where entry_country_id = [MYID];
9. It’ll be obvious which entries are useful here, they’ll be reasonable attempts at entering a state into the address, but maybe wrong case (negeri sembilan), misspelled (Johore), abbreviated (Tereng.) For each useful entry_state value, update the address book like this:
update address_book set entry_state = '[StateName]', entry_zone_id = [ZoneID] where entry_state = '[Step8State]' and entry_country_id = [MYID];
In the above SQL statement, [StateName] and [ZoneID] are read from your cheat sheet printed in step 7. [Step8State] is the distinct value from the results of step 8. So for example, you could fix all addresses whose state line was ‘K.L.’ like this:
update address_book set entry_state = 'Kuala Lumpur', entry_zone_id = [ZoneID] where entry_state = 'K.L.' and entry_country_id = [MYID];
Repeat this step as many times as you have recognisable entries from step 8. When you’ve finished, repeat step 6 to see whether you’ve finished or need a break.
10. If you’re left with only unrecognisable or blank entries in the entry_state field, you’re going to have to use other address parts to see if you can decide which state they’re in. Typing names of villages into Wikipedia can help if you’re not sure. Start with the city:
select distinct entry_city from address_book left join zones on entry_zone_id = zone_id where entry_country_id = [MYID] and zone_id is null;
11. Use your Malaysian city knowledge (and Wikipedia) and your cheat sheet to update each recognisable city similar to step 9:
update address_book left join zones on entry_zone_id = zone_id set entry_zone_id = [ZoneID], entry_state = '[StateName]' where entry_country_id = [MYID] and zone_id is null and entry_city = '[Step10City]';
Repeat this step as many times as you have recognisable entries from step 10. When you’ve finished, repeat step 6 to see whether you’ve finished or need a break.
12. The remaining addresses may be in very bad shape – don’t expect to be able to fix every one. Some may well have been created with no intention of receiving shipments from you, so don’t worry too much about bad addresses. Now to fix the really bad addresses:
select address_book_id, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state from address_book left join zones on entry_zone_id = zone_id where entry_country_id = [MYID] and zone_id is null;
13. For each one with an address you recognise in step 12 and care about linking properly so that local shipping quotations work, do this:
update address_book left join zones on entry_zone_id = zone_id set entry_zone_id = [ZoneID], entry_state = '[StateName]' where entry_country_id = [MYID] and zone_id is null and address_book_id = [AddressBookID];
[AddressBookID] is the id of a single entry in the address book, read from the first column of results from step 12.
14. You have finished a very tedious job. You can repeat step 6 if you like, to see how many irreconcilable entries are left. They’re probably bad records not intended to be delivery addresses.
Posted in FOSS, Spider.my, Useful | 1 Comment »
