From 1c305d4c100a77efafe3ff6d03adcc56fd97c3f6 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Thu, 23 Dec 2010 17:35:25 +0100 Subject: [PATCH] Updates geoloc_* tables and adds official languages to countries. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- classes/address.php | 6 ++-- classes/direnum.php | 4 +-- classes/geocoder.php | 16 ++++++++--- classes/gmapsgeocoder.php | 2 +- include/education.func.inc.php | 4 +-- include/profilefields.inc.php | 4 +-- modules/profile.php | 6 ++-- modules/profile/mentor.inc.php | 6 ++-- modules/search.php | 4 +-- plugins/function.select_nat.php | 6 ++-- upgrade/1.0.2/06_geocoding.sql | 42 ++++++++++++++++++++++++++++ upgrade/1.0.2/geocoding.php | 61 +++++++++++++++++++++++++++++++++++++++++ ut/checkdb.php | 8 +++--- 13 files changed, 141 insertions(+), 28 deletions(-) create mode 100644 upgrade/1.0.2/06_geocoding.sql create mode 100755 upgrade/1.0.2/geocoding.php diff --git a/classes/address.php b/classes/address.php index 70e7354..0ba8f93 100644 --- a/classes/address.php +++ b/classes/address.php @@ -858,8 +858,10 @@ class AddressIterator implements PlIterator pa.administrativeAreaId, pa.countryId, pa.latitude, pa.longitude, pa.north, pa.south, pa.east, pa.west, pa.pub, pa.comment, - gl.name AS locality, gs.name AS subAdministrativeArea, - ga.name AS administrativeArea, gc.countryFR AS country + gl.name AS locality, gl.nameLocal AS localityLocal, + gs.name AS subAdministrativeArea, gs.nameLocal AS subAdministrativeAreaLocal, + ga.name AS administrativeArea, ga.nameLocal AS administrativeAreaLocal, + gc.country, gc.countryLocal FROM profile_addresses AS pa LEFT JOIN geoloc_localities AS gl ON (gl.id = pa.localityId) LEFT JOIN geoloc_administrativeareas AS ga ON (ga.id = pa.administrativeAreaId) diff --git a/classes/direnum.php b/classes/direnum.php index 5350f0e..0268ca9 100644 --- a/classes/direnum.php +++ b/classes/direnum.php @@ -578,8 +578,8 @@ class DE_Nationalities extends DirEnumeration class DE_Countries extends DirEnumeration { protected $idfield = 'geoloc_countries.iso_3166_1_a2'; - protected $valfield = 'geoloc_countries.countryFR'; - protected $valfield2 = 'geoloc_countries.country'; + protected $valfield = 'geoloc_countries.country'; + protected $valfield2 = 'geoloc_countries.countryEn'; protected $from = 'geoloc_countries'; protected $ac_join = 'INNER JOIN profile_addresses ON (geoloc_countries.iso_3166_1_a2 = profile_addresses.countryId)'; diff --git a/classes/geocoder.php b/classes/geocoder.php index 357891e..425b017 100644 --- a/classes/geocoder.php +++ b/classes/geocoder.php @@ -48,12 +48,20 @@ abstract class Geocoder { WHERE name = {?}', $address->$areaName); if ($res->numRows() == 0) { - XDB::execute('INSERT INTO ' . $databases[$area] . ' (name, country) - VALUES ({?}, {?})', - $address->$areaName, $address->countryId); + XDB::execute('INSERT INTO ' . $databases[$area] . ' (name, nameLocal, country) + VALUES ({?}, {?}, {?})', + $address->$areaName, $address->$areaNameLocal, $address->countryId); $address->$areaId = XDB::insertId(); } else { - $address->$areaId = $res->fetchOneCell(); + // XXX: remove this once all areas have both nameLocal and name. + list($id, $name) = $res->fetchOneRow(); + if (is_null($name) && !is_null($address->$areaNameLocal)) { + XDB::execute('UPDATE ' . $databases[$area] . ' + SET nameLocal = {?} + WHERE id = {?}', + $address->$areaNameLocal, $id); + } + $address->$areaId = $id; } } elseif (empty($address->$areaId)) { $address->$areaId = null; diff --git a/classes/gmapsgeocoder.php b/classes/gmapsgeocoder.php index edae5c5..16a2764 100644 --- a/classes/gmapsgeocoder.php +++ b/classes/gmapsgeocoder.php @@ -281,7 +281,7 @@ class GMapsGeocoder extends Geocoder { // all non-country items of ISO 3166-1. private function getTextToGeocode($text) { - $res = XDB::iterator('SELECT country, countryFR + $res = XDB::iterator('SELECT countryEn, country FROM geoloc_countries WHERE belongsTo IS NOT NULL'); $countries = array(); diff --git a/include/education.func.inc.php b/include/education.func.inc.php index dbfba28..51437db 100644 --- a/include/education.func.inc.php +++ b/include/education.func.inc.php @@ -22,14 +22,14 @@ function education_options($current = 0) { $html = ''; - $res = XDB::iterator("SELECT e.id AS id, gc.countryFR AS country, + $res = XDB::iterator("SELECT e.id AS id, gc.country, IF(CHAR_LENGTH(e.name) > 76, e.abbreviation, e.name) AS name FROM profile_education_enum AS e LEFT JOIN geoloc_countries AS gc ON (e.country = gc.iso_3166_1_a2) WHERE EXISTS (SELECT * FROM profile_education_degree AS d WHERE e.id = d.eduid) - ORDER BY gc.countryFR, e.name"); + ORDER BY gc.country, e.name"); $country = ""; while ($arr_edu = $res->next()) { if ($arr_edu["country"] != $country) { diff --git a/include/profilefields.inc.php b/include/profilefields.inc.php index 968c416..b39b8f9 100644 --- a/include/profilefields.inc.php +++ b/include/profilefields.inc.php @@ -338,7 +338,7 @@ class ProfileEducation extends ProfileField $data = XDB::iterator('SELECT pe.id, pe.pid, pe.entry_year, pe.grad_year, pe.program, pe.flags, pee.name AS school, pee.abbreviation AS school_short, - pee.url AS school_url, gc.countryFR AS country, + pee.url AS school_url, gc.country, pede.degree, pede.abbreviation AS degree_short, pede.level AS degree_level, pefe.field FROM profile_education AS pe @@ -489,7 +489,7 @@ class ProfileMentoringCountries extends ProfileField public static function fetchData(array $pids, ProfileVisibility $visibility) { - $data = XDB::iterator('SELECT pmc.pid, pmc.country AS id, gc.countryFR AS name + $data = XDB::iterator('SELECT pmc.pid, pmc.country AS id, gc.country AS name FROM profile_mentor_country AS pmc LEFT JOIN geoloc_countries AS gc ON (gc.iso_3166_1_a2 = pmc.country) WHERE pmc.pid IN {?} diff --git a/modules/profile.php b/modules/profile.php index 5a23f80..a7f4b03 100644 --- a/modules/profile.php +++ b/modules/profile.php @@ -520,7 +520,7 @@ class ProfileModule extends PLModule // Retrieves referents' countries. $res = XDB::query( - "SELECT gc.countryFR + "SELECT gc.country FROM profile_mentor_country AS m LEFT JOIN geoloc_countries AS gc ON (m.country = gc.iso_3166_1_a2) WHERE pid = {?}", $pf->id()); @@ -534,11 +534,11 @@ class ProfileModule extends PLModule pl_content_headers("text/html"); $page->changeTpl('include/field.select.tpl', NO_SKIN); $page->assign('name', 'pays_sel'); - $it = XDB::iterator("SELECT gc.iso_3166_1_a2 AS id, gc.countryFR AS field + $it = XDB::iterator("SELECT gc.iso_3166_1_a2 AS id, gc.country AS field FROM geoloc_countries AS gc INNER JOIN profile_mentor_country AS mp ON (mp.country = gc.iso_3166_1_a2) GROUP BY iso_3166_1_a2 - ORDER BY countryFR"); + ORDER BY country"); $page->assign('list', $it); } diff --git a/modules/profile/mentor.inc.php b/modules/profile/mentor.inc.php index 14af014..6ad2598 100644 --- a/modules/profile/mentor.inc.php +++ b/modules/profile/mentor.inc.php @@ -97,7 +97,7 @@ class ProfileSettingCountry implements ProfileSetting $success = true; if (is_null($value)) { $value = array(); - $res = XDB::iterRow("SELECT m.country, gc.countryFR + $res = XDB::iterRow("SELECT m.country, gc.country FROM profile_mentor_country AS m INNER JOIN geoloc_countries AS gc ON (m.country = gc.iso_3166_1_a2) WHERE m.pid = {?}", @@ -175,9 +175,9 @@ class ProfilePageMentor extends ProfilePage public function _prepare(PlPage &$page, $id) { - $page->assign('countryList', XDB::iterator("SELECT iso_3166_1_a2, countryFR + $page->assign('countryList', XDB::iterator("SELECT iso_3166_1_a2, country FROM geoloc_countries - ORDER BY countryFR")); + ORDER BY country")); $page->assign('hrpid', $this->profile->hrpid); } } diff --git a/modules/search.php b/modules/search.php index ea9d21c..14383b4 100644 --- a/modules/search.php +++ b/modules/search.php @@ -403,14 +403,14 @@ class SearchModule extends PLModule pl_content_headers("text/xml"); $page->changeTpl('include/field.select.tpl', NO_SKIN); $page->assign('name', 'country'); - $it = XDB::iterator("SELECT gc.iso_3166_1_a2 AS id, gc.countryFR AS field + $it = XDB::iterator("SELECT gc.iso_3166_1_a2 AS id, gc.country AS field FROM geoloc_countries AS gc INNER JOIN profile_mentor_country AS mp ON (mp.country = gc.iso_3166_1_a2) INNER JOIN profile_mentor_term AS mt ON (mt.pid = mp.pid) INNER JOIN profile_job_term_relation AS jtr ON (jtr.jtid_2 = mt.jtid) WHERE jtr.jtid_1 = {?} GROUP BY iso_3166_1_a2 - ORDER BY countryFR", $jtid); + ORDER BY country", $jtid); $page->assign('list', $it); } } diff --git a/plugins/function.select_nat.php b/plugins/function.select_nat.php index a9720ed..551e0e6 100644 --- a/plugins/function.select_nat.php +++ b/plugins/function.select_nat.php @@ -21,10 +21,10 @@ function select_nat($valeur, $pad=false) { - $res = XDB::iterRow("SELECT iso_3166_1_a2 AS id, nationalityFR AS text + $res = XDB::iterRow("SELECT iso_3166_1_a2 AS id, nationality AS text FROM geoloc_countries - WHERE nationalityFR IS NOT NULL - ORDER BY nationalityFR"); + WHERE nationality IS NOT NULL + ORDER BY nationality"); $sel = ' selected="selected"'; // on ajoute une entree vide si $pad est vrai diff --git a/upgrade/1.0.2/06_geocoding.sql b/upgrade/1.0.2/06_geocoding.sql new file mode 100644 index 0000000..3d55e15 --- /dev/null +++ b/upgrade/1.0.2/06_geocoding.sql @@ -0,0 +1,42 @@ +ALTER TABLE geoloc_administrativeareas ADD COLUMN nameLocal VARCHAR(255) DEFAULT NULL; +ALTER TABLE geoloc_subadministrativeareas ADD COLUMN nameLocal VARCHAR(255) DEFAULT NULL; +ALTER TABLE geoloc_localities ADD COLUMN nameLocal VARCHAR(255) DEFAULT NULL; + +ALTER TABLE geoloc_countries CHANGE COLUMN country countryEn VARCHAR(255) DEFAULT NULL; +ALTER TABLE geoloc_countries CHANGE COLUMN countryFR country VARCHAR(255) DEFAULT NULL; +ALTER TABLE geoloc_countries CHANGE COLUMN nationality nationalityEn VARCHAR(255) DEFAULT NULL; +ALTER TABLE geoloc_countries CHANGE COLUMN nationalityFR nationality VARCHAR(255) DEFAULT NULL; +ALTER TABLE geoloc_countries ADD COLUMN countryLocal VARCHAR(255) DEFAULT NULL; +ALTER TABLE geoloc_countries ADD COLUMN countryPlain VARCHAR(255) DEFAULT NULL; + +INSERT INTO geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion, + country, countryEn, capital, nationality, + licensePlate, belongsTo, phonePrefix) + VALUES ('AW', 'ABW', 533, 'SA', 'Aruba', 'Aruba', 'Oranjestad', NULL, 'NL', 'NL', 297), + ('GW', 'GNB', 624, 'AF', 'Guinée-Bissau', 'Republic of Guinea-Bissau', 'Bissau', 'Bissau-Guinéen', 'GW', NULL, 245); + +-- List of supported languages: + -- ar ARABIC -- bg BULGARIAN -- bn BENGALI -- ca CATALAN -- cs CZECH -- da DANISH -- de GERMAN -- el GREEK -- en ENGLISH -- es SPANISH -- eu BASQUE -- fi FINNISH -- fil FILIPINO -- fr FRENCH -- gl GALICIAN -- gu GUJARATI -- hi HINDI -- hr CROATIAN -- sr SERBIAN -- hu HUNGARIAN -- id INDONESIAN -- it ITALIAN -- iw HEBREW -- ja JAPANESE -- kn KANNADA -- ko KOREAN -- lt LITHUANIAN -- lv LATVIAN -- ml MALAYALAM -- mr MARATHI -- nl DUTCH -- nn NORWEGIAN NYNORSK -- no NORWEGIAN -- or ORIYA -- pl POLISH -- pt PORTUGUESE -- pt-BR PORTUGUESE (BRAZIL) -- pt-PT PORTUGUESE (PORTUGAL) -- ro ROMANIAN -- ru RUSSIAN -- sk SLOVAK -- sl SLOVENIAN -- sv SWEDISH -- ta TAMIL -- te TELUGU -- th THAI -- tr TURKISH -- uk UKRAINIAN -- vi VIETNAMESE -- zh-CN CHINESE (SIMPLIFIED) -- zh-TW CHINESE (TRADITIONAL) + +DROP TABLE IF EXISTS geoloc_languages; +CREATE TABLE geoloc_languages ( + iso_3166_1_a2 CHAR(2) NOT NULL, + language CHAR(5) NOT NULL, + country VARCHAR(255) DEFAULT NULL, + countryPlain VARCHAR(255) DEFAULT NULL, + PRIMARY KEY (iso_3166_1_a2, language), + FOREIGN KEY (iso_3166_1_a2) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO geoloc_languages (language, iso_3166_1_a2) + VALUES ('ar', 'IL'), ('ar', 'JO'), ('ar', 'SD'), ('ar', 'TD'), ('ar', 'KM'), ('ar', 'DJ'), ('ar', 'LB'), ('ar', 'DZ'), ('ar', 'BH'), ('ar', 'EG'), ('ar', 'ER'), ('ar', 'IQ'), ('ar', 'KW'), ('ar', 'LY'), ('ar', 'MR'), ('ar', 'MA'), ('ar', 'OM'), ('ar', 'PS'), ('ar', 'QA'), ('ar', 'SA'), ('ar', 'SO'), ('ar', 'SY'), ('ar', 'TN'), ('ar', 'AE'), + ('bg', 'BG'), ('bn', 'BD'), ('bn', 'IN'), ('ca', 'ES'), ('cs', 'CZ'), ('da', 'DK'), ('de', 'AT'), ('de', 'DE'), ('de', 'LI'), ('de', 'BE'), ('de', 'LU'), ('de', 'CH'), ('el', 'GR'), ('el', 'CY'), + ('en', 'IN'), ('en', 'JO'), ('en', 'SD'), ('en', 'PH'), ('en', 'AG'), ('en', 'BS'), ('en', 'BW'), ('en', 'DM'), ('en', 'FJ'), ('en', 'GM'), ('en', 'GH'), ('en', 'GD'), ('en', 'GY'), ('en', 'IE'), ('en', 'JM'), ('en', 'KE'), ('en', 'KI'), ('en', 'LS'), ('en', 'LR'), ('en', 'MW'), ('en', 'MT'), ('en', 'MH'), ('en', 'MU'), ('en', 'FM'), ('en', 'NA'), ('en', 'NR'), ('en', 'NZ'), ('en', 'NG'), ('en', 'PK'), ('en', 'PG'), ('en', 'KN'), ('en', 'LC'), ('en', 'VC'), ('en', 'WS'), ('en', 'SL'), ('en', 'SB'), ('en', 'SZ'), ('en', 'TZ'), ('en', 'TO'), ('en', 'TT'), ('en', 'TV'), ('en', 'UG'), ('en', 'ZM'), ('en', 'ZW'), ('en', 'AU'), ('en', 'GB'), ('en', 'ZA'), ('en', 'BZ'), ('en', 'US'), ('en', 'HN'), ('en', 'CM'), ('en', 'RW'), ('en', 'SC'), ('en', 'CA'), ('en', 'VU'), ('en', 'PW'), ('en', 'SG'), + ('en', 'MO'), + ('es', 'BZ'), ('es', 'US'), ('es', 'MX'), ('es', 'AR'), ('es', 'CL'), ('es', 'DO'), ('es', 'NI'), ('es', 'CO'), ('es', 'PE'), ('es', 'VE'), ('es', 'EC'), ('es', 'GT'), ('es', 'CU'), ('es', 'BO'), ('es', 'SV'), ('es', 'PY'), ('es', 'CR'), ('es', 'PA'), ('es', 'AD'), ('es', 'ES'), ('es', 'UY'), ('es', 'HN'), ('es', 'GQ'), + ('eu', 'ES'), ('fi', 'FI'), ('fil', 'PH'), + ('fr', 'CM'), ('fr', 'RW'), ('fr', 'SC'), ('fr', 'CA'), ('fr', 'VU'), ('fr', 'CD'), ('fr', 'FR'), ('fr', 'MG'), ('fr', 'CI'), ('fr', 'BF'), ('fr', 'NE'), ('fr', 'SN'), ('fr', 'ML'), ('fr', 'GN'), ('fr', 'HT'), ('fr', 'BI'), ('fr', 'BJ'), ('fr', 'TG'), ('fr', 'CF'), ('fr', 'CG'), ('fr', 'GA'), ('fr', 'MC'), ('fr', 'TD'), ('fr', 'KM'), ('fr', 'DJ'), ('fr', 'LB'), ('fr', 'BE'), ('fr', 'LU'), ('fr', 'CH'), ('fr', 'GQ'), + ('gl', 'ES'), ('gu', 'IN'), ('hi', 'IN'), ('hr', 'HR'), ('hr', 'BA'), ('hu', 'HU'), ('id', 'ID'), ('it', 'CH'), ('it', 'IT'), ('it', 'SM'), ('it', 'VA'), ('it', 'MT'), ('iw', 'IL'), ('ja', 'JP'), ('ja', 'PW'), ('kn', 'IN'), ('ko', 'KP'), ('ko', 'KR'), ('lt', 'LT'), ('lv', 'LV'), ('ml', 'IN'), ('mr', 'IN'), ('nl', 'BE'), ('nl', 'AW'), ('nl', 'NL'), ('nl', 'SR'), ('no', 'NO'), ('nn', 'NO'), ('or', 'IN'), ('pl', 'PL'), ('pt', 'BR'), ('pt-BR', 'BR'), ('pt', 'PT'), ('pt-PT', 'PT'), ('pt', 'MZ'), ('pt', 'AO'), ('pt', 'GW'), ('pt', 'TL'), ('pt', 'CV'), ('pt', 'ST'), ('pt', 'UY'), ('pt', 'MO'), ('rm', 'CH'), ('ro', 'MD'), ('ro', 'RO'), ('ru', 'RU'), ('ru', 'BY'), ('ru', 'KZ'), ('ru', 'KG'), ('sk', 'CZ'), ('sk', 'SK'), ('sl', 'SI'), ('sr', 'BA'), ('sr', 'RS'), ('sv', 'FI'), ('sv', 'SE'), ('ta', 'IN'), ('ta', 'LK'), ('ta', 'SG'), ('te', 'IN'), ('th', 'TH'), ('tr', 'CY'), ('tr', 'TR'), ('ua', 'UA'), ('vi', 'VN'), + ('zh-CN', 'SG'), ('zh-CN', 'CN'), ('zh-CN', 'TW'), ('zh-CN', 'MY'), ('zh-CN', 'HK'), ('zh-CN', 'MO'), ('zh-TW', 'SG'), ('zh-TW', 'CN'), ('zh-TW', 'TW'), ('zh-TW', 'MY'), ('zh-TW', 'HK'), ('zh-TW', 'MO'); + +-- vim:set syntax=mysql: diff --git a/upgrade/1.0.2/geocoding.php b/upgrade/1.0.2/geocoding.php new file mode 100755 index 0000000..8569226 --- /dev/null +++ b/upgrade/1.0.2/geocoding.php @@ -0,0 +1,61 @@ +#!/usr/bin/php5 +debug = 0; // Do not store backtraces. + +$it = XDB::iterator('SELECT gl.language, gc.country, gc.iso_3166_1_a2 + FROM geoloc_languages AS gl + INNER JOIN geoloc_countries AS gc ON (gl.iso_3166_1_a2 = gc.iso_3166_1_a2)'); + +echo $it->total() . " pays à remplir.\n"; +while ($item = $it->next()) { + if ($item['language'] != 'fr') { + $address = new Address(array('text' => $item['country'])); + $gmapsGeocoder = new GMapsGeocoder(); + $gmapsGeocoder->getGeocodedAddress($address, $item['language'], true); + $country = $address->country; + } else { + $country = $item['country']; + } + $countryPlain = mb_strtoupper(replace_accent($country)); + XDB::execute('UPDATE geoloc_languages + SET country = {?}, countryPlain = {?} + WHERE iso_3166_1_a2 = {?} AND language = {?}', + $country, $countryPlain, $item['iso_3166_1_a2'], $item['language']); + sleep(1); +} + +$it = XDB::rawIterator('SELECT country, iso_3166_1_a2 + FROM geoloc_countries'); +echo $it->total() . " pays à simplifier.\n"; +while ($item = $it->next()) { + XDB::execute('UPDATE geoloc_countries + SET countryPlain = {?} + WHERE iso_3166_1_a2 = {?}', + mb_strtoupper(replace_accent($item['country'])), $item['iso_3166_1_a2']); +} + +// Fixes geocoding errors. +XDB::rawExecute("REPLACE INTO geoloc_languages (iso_3166_1_a2, language, country, countryPlain) + VALUES ('FM', 'en', 'Federated States of Micronesia', 'FEDERATED STATES OF MICRONESIA'), + ('MH', 'en', 'Republic of the Marshall Islands', 'REPUBLIC OF THE MARSHALL ISLANDS'), + ('PS', 'ar', 'دولة فلسطين', 'دولة فلسطين'), + ('SB', 'en', 'Solomon Islands', 'SOLOMON ISLANDS'), + ('TW', 'zh-CN', '台湾', '台湾'), + ('TW', 'zh-TW', '台灣', '台灣'), + ('CZ', 'cs', 'Česká Republika', 'CESKA REPUBLIKA'), + ('CZ', 'sk', 'Česká Republika', 'CESKA REPUBLIKA'), + ('DO', 'es', 'República Dominicana', 'REPUBLICA DOMINICANA'), + ('GD', 'en', 'Grenada', 'GRENADA'), + ('MD', 'ro', 'Republica Moldova', 'REPUBLICA MOLDOVA'), + ('RU', 'ru', 'Россия', 'Россия'), + ('SK', 'sk', 'Slovenská Republika', 'SLOVENSKA REPUBLIKA'), + ('TZ', 'en', 'United Republic of Tanzania', 'UNITED REPUBLIC OF TANZANIA')"); + +/* vim:set et sw=4 sts=4 ts=4: */ +?> diff --git a/ut/checkdb.php b/ut/checkdb.php index 47b30f2..b846b39 100644 --- a/ut/checkdb.php +++ b/ut/checkdb.php @@ -67,7 +67,7 @@ class CheckDB extends PlTestCase array('SELECT pa.pid, pa.countryId FROM profile_addresses AS pa LEFT JOIN geoloc_countries AS gc ON (pa.countryId = gc.iso_3166_1_a2) - WHERE gc.countryFR IS NULL OR gc.countryFR = \'\''), + WHERE gc.country IS NULL OR gc.country = \'\''), 'missing nationalities' => array('SELECT p.pid, p.nationality1, p.nationality2, p.nationality3 @@ -75,9 +75,9 @@ class CheckDB extends PlTestCase LEFT JOIN geoloc_countries AS g1 ON (p.nationality1 = g1.iso_3166_1_a2) LEFT JOIN geoloc_countries AS g2 ON (p.nationality2 = g2.iso_3166_1_a2) LEFT JOIN geoloc_countries AS g3 ON (p.nationality3 = g3.iso_3166_1_a2) - WHERE (p.nationality1 IS NOT NULL AND (g1.nationalityFR IS NULL OR g1.nationalityFR = \'\')) - OR (p.nationality2 IS NOT NULL AND (g2.nationalityFR IS NULL OR g2.nationalityFR = \'\')) - OR (p.nationality3 IS NOT NULL AND (g3.nationalityFR IS NULL OR g3.nationalityFR = \'\'))'), + WHERE (p.nationality1 IS NOT NULL AND (g1.nationality IS NULL OR g1.nationality = \'\')) + OR (p.nationality2 IS NOT NULL AND (g2.nationality IS NULL OR g2.nationality = \'\')) + OR (p.nationality3 IS NOT NULL AND (g3.nationality IS NULL OR g3.nationality = \'\'))'), 'ax_id' => array('SELECT pid, hrpid, ax_id, COUNT(ax_id) AS c -- 2.1.4