I am setting up a web shop using Drupal and Ubercart. As shipping module I am using Ubercart Global Quote. This module allows me to set up shipping zones. For each shipping zone I can configure a shipping rate based on the weight of the products.
Shipping zones for PostNL are, the Netherlands, Europe 1, Europe 2, Europe 3 and World. This means, if a country is not the Netherlands or listed in one of the Europe zones, the World rate applies. The problem is, to create a shipping zone I have to select all the countries in this shipping zone. There is no “unlisted countries” zone.
I have encountered similar problems with other e-commerce software. In this post I will describe what I have done to fix it in Ubercart. Technically spoken this is not a fix. I have not touched any Ubercart code, but I have created a shipping zone with the “unlisted countries” by code. The following code is supposed to be executed after configuring the known shipping zones.
First, we obtain the already configured shipping zones. Shipping zones are stored as a string in the database, in the following format: country_name,country_id, separated by a pipe sign. The country id is the numerical ISO country code.
$country_array = array();
$query1 = "select countries from uc_shipping_zones";
$countriylists = $pdo->query($query1);
foreach ($countriylists as $countrylist) {
$crt = explode("|",$countrylist['countries']);
$country_array = array_merge($country_array,$crt);
}
After running this code, we have all countries (in country_name,country_id format) for all configured shipping zones, stored in an array. At this point I would like to elaborate on how countries are stored in Ubercart. Ubercart has a table with country names and their ISO codes, which includes the numerical value.
What we want to obtain is a list of countries not included in the country list we have obtained in the previous step. We will solve this problem is SQL. First, we extract the numerical ids from the array created in previous step. Then we generate a query that selects all countries but the ones we have in our list. For this we generate a subquery that consist of literal selects and unions them. Basically, we generate a table with the country values already listed.
$first = true;
foreach ($country_array as $country) {
$crt=explode(",",$country);
if (!$first) {
$query2_part2 .= " UNION select ";
} else {
$first = false;
$query2_part2 .= "select ";
}
// Some country names may include a comma
// "Moldovoa, Republic of"
$query2_part2 .= "(" . $crt[sizeof($crt)-1] .")";
}
As some country names in the Ubercart country list contain a comma, we need to select the last value of our array, to make sure we have the number. Then we generate a select and union of all our numbers. Then we include this subquery in a query that will select the country list.
$query2 = "select concat_ws(',',country_name, country_id) from uc_countries where country_id not in ($query2_part2 )";
// for non-ascii country names:
// we get "c3856c616e642049736c616e64732c323438"
// should be "Åland Islands,248"
// so we should do the concat in php in stead of mariadb
$query2 = "select country_name, country_id from uc_countries where country_id not in ($query2_part2 )";
$othercountries = $pdo->query($query2);
MariaDB can perform string manipulations. We need to obtain a string country_name,country_id, so the way to do this in MariaDB would be concat_ws(',',country_name, country_id. However, this gives incorrect results if a country name contains non-ascii characters. Therefore, this concatenation is done in PHP rather then in MariaDB.
$first = true;
foreach ($othercountries as $othercountry) {
if (!$first) {
$otherstring .= "|";
} else {
$first = false;
}
$otherstring .= $othercountry['country_name'] . "," . $othercountry['country_id'] ;
}
This code takes the result of the previous query, an array of all the country names and id’s unlisted. This list is put together is the desired format, country_name,country_id separated by a pipe sign.
Finally we enter this string into the database
$pq = $pdo->prepare("INSERT INTO uc_shipping_zones (name,countries,regions) VALUES (:name, :countries, :regions )");
$pq->execute(array(":name" => "Other countries", ":countries" => $otherstring, ":regions" => ""));
Now, the shipping rates can be configured to ship to any country.
This might not be the optimal solution, but I suppose this is a way to do it without touching the Ubercart Global Quote code.
