From e4cd7a1f6c6ec37d85eb114727cc048ea17306b7 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Thu, 19 Feb 2009 13:28:50 +0100 Subject: [PATCH] Gets rid of all references to the old geoloc_* and adresses tables. --- bin/cron/checkdb.php | 31 +++++++++++-------- bin/cron/phones.check.php | 9 ++++-- include/education.func.inc.php | 12 ++++---- include/profil.func.inc.php | 6 ++-- include/userset.inc.php | 64 +++++++++++++++++++++------------------ modules/profile.php | 18 +++++------ modules/profile/jobs.inc.php | 10 +++--- modules/profile/mentor.inc.php | 4 +-- modules/search.php | 67 ++++++++++++++++++++--------------------- modules/search/classes.inc.php | 20 ++++++------ modules/search/search.inc.php | 17 ++++------- plugins/function.select_nat.php | 6 ++-- 12 files changed, 137 insertions(+), 127 deletions(-) diff --git a/bin/cron/checkdb.php b/bin/cron/checkdb.php index c4ef1b1..ea1fdb7 100755 --- a/bin/cron/checkdb.php +++ b/bin/cron/checkdb.php @@ -80,7 +80,10 @@ check("SELECT u.user_id, nom, prenom, promo, OR (profile_freetext_pub != 'private' AND profile_freetext_pub != 'public') OR (profile_medals_pub != 'private' AND profile_medals_pub != 'public')", "Utilisateur n'ayant pas de flag de publicite pour leurs donnees de profil"); -check("select uid from adresses where pub != 'private' and pub !='ax' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicite pour une adresse"); +check("SELECT pid + FROM profile_addresses + WHERE pub != 'private' AND pub !='ax' AND pub != 'public'", + "Utiliseur n'ayant pas de flag de publicité pour une adresse."); check("select uid from profile_phones where pub != 'private' and pub != 'ax' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicite pour un numero de téléphone"); check("select uid from profile_networking where pub != 'private' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicité pour une adresse de networking"); @@ -123,19 +126,23 @@ check("select g.* from groupesx_ins as g left join groupesx_def as gd on g.gid=g check("select p.* from photo as p left join auth_user_md5 as u on u.user_id=p.uid where u.prenom is null"); /* validite des formats téléphoniques */ -check("SELECT DISTINCT g.phoneprf from geoloc_pays AS g - WHERE EXISTS (SELECT h.phoneprf - FROM geoloc_pays AS h - WHERE h.phoneprf = g.phoneprf AND h.phoneformat != (SELECT i.phoneformat - FROM geoloc_pays AS i - WHERE i.phoneprf = g.phoneprf - LIMIT 1))", +check("SELECT DISTINCT g.phonePrefix + FROM geoloc_countries AS g + WHERE EXISTS (SELECT h.phonePrefix + FROM geoloc_countries AS h + WHERE h.phonePrefix = g.phonePrefix + AND h.phoneFormat != (SELECT i.phoneFormat + FROM geoloc_countries AS i + WHERE i.phonePrefix = g.phonePrefix + LIMIT 1))", "Préfixes téléphoniques qui ont des formats de numéros de téléphones différents selon les pays"); -/* validite des champ pays et region */ -check("SELECT a.uid, a.country FROM adresses AS a LEFT JOIN geoloc_pays AS gp ON a.country = gp.a2 WHERE gp.pays IS NULL","donne la liste des pays dans les profils qui n'ont pas d'entree correspondante dans geoloc_pays"); -/* les régions ne sont valides que dans les adresses pros */ -//check("SELECT e.uid, e.country, e.region FROM entreprises AS e LEFT JOIN geoloc_region AS gr ON (e.country = gr.a2 AND e.region = gr.region) WHERE e.region != '' AND gr.name IS NULL","donne la liste des regions dans les profils pros qui n'ont pas d'entree correspondante dans geoloc_region"); +/* validite des champ pays */ +check("SELECT a.pid, a.countryId + FROM profile_addresses AS a + LEFT JOIN geoloc_countries AS gc ON (a.countryId = gc.iso_3166_1_a2) + WHERE gc.countryFR IS NULL OR gc.countryFR = ''", + "donne la liste des pays dans les profils qui n'ont pas d'entree correspondante dans geoloc_countries"); /* donne la liste des emails douteux que les administrateurs n'ont pas encore traité */ check("SELECT a1.alias, a2.alias, e1.email, e2.flags diff --git a/bin/cron/phones.check.php b/bin/cron/phones.check.php index 077e2fe..62fa4e5 100755 --- a/bin/cron/phones.check.php +++ b/bin/cron/phones.check.php @@ -19,11 +19,14 @@ function do_update_by_block($values) ON DUPLICATE KEY UPDATE display_tel = VALUES(display_tel)"); } -$res = XDB::query("SELECT DISTINCT g.phoneprf FROM geoloc_pays AS g WHERE g.phoneprf IS NOT NULL"); +$res = XDB::query("SELECT DISTINCT phonePrefix + FROM geoloc_countries + WHERE phonePrefix IS NOT NULL"); $prefixes = $res->fetchColumn(); foreach ($prefixes as $i => $prefix) { - $res = XDB::query("SELECT g.phoneformat FROM geoloc_pays AS g - WHERE g.phoneprf = {?} AND g.phoneformat != '' LIMIT 1", + $res = XDB::query("SELECT phoneFormat + FROM geoloc_countries + WHERE phonePrefix = {?} AND phoneFormat != '' LIMIT 1", $prefix); if ($res->numRows() > 0) { $format = $res->fetchOneCell(); diff --git a/include/education.func.inc.php b/include/education.func.inc.php index 04a98ca..68ec9b7 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, g.pays AS country, + $res = XDB::iterator("SELECT e.id AS id, gc.countryFR AS country, IF(CHAR_LENGTH(e.name) > 76, e.abbreviation, e.name) AS name FROM profile_education_enum AS e - LEFT JOIN geoloc_pays AS g ON (e.country = g.a2) + 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 g.pays, e.name"); + ORDER BY gc.countryFR, e.name"); $country = ""; while ($arr_edu = $res->next()) { if ($arr_edu["country"] != $country) { @@ -63,9 +63,9 @@ function education_degree() $html = ""; $res = XDB::iterRow("SELECT d.eduid, d.degreeid FROM profile_education_enum AS e - INNER JOIN profile_education_degree AS d ON (e.id = d.eduid) - LEFT JOIN geoloc_pays AS g ON (e.country = g.a2) - ORDER BY g.pays, e.name"); + INNER JOIN profile_education_degree AS d ON (e.id = d.eduid) + LEFT JOIN geoloc_countries AS gc ON (e.country = gc.a2) + ORDER BY gc.countryFR, e.name"); if ($edu_degree = $res->next()) { $eduid = $edu_degree['0']; $html .= "["; diff --git a/include/profil.func.inc.php b/include/profil.func.inc.php index 4e332e2..171a616 100644 --- a/include/profil.func.inc.php +++ b/include/profil.func.inc.php @@ -314,9 +314,9 @@ function format_display_number($tel, &$error, $format = array('format'=>'','phon $ret = ''; $tel_length = strlen($tel); if((!isset($format['phoneprf'])) || ($format['phoneprf'] == '')) { - $res = XDB::query("SELECT phoneprf, phoneformat AS format - FROM geoloc_pays - WHERE phoneprf = {?} OR phoneprf = {?} OR phoneprf = {?} + $res = XDB::query("SELECT phonePrefix AS phoneprf, phoneFormat AS format + FROM geoloc_countries + WHERE phonePrefix = {?} OR phonePrefix = {?} OR phonePrefix = {?} LIMIT 1", substr($tel, 0, 1), substr($tel, 0, 2), substr($tel, 0, 3)); if ($res->numRows() == 0) { diff --git a/include/userset.inc.php b/include/userset.inc.php index be9e740..32dd544 100644 --- a/include/userset.inc.php +++ b/include/userset.inc.php @@ -23,20 +23,22 @@ require_once('user.func.inc.php'); global $globals; -@$globals->search->result_where_statement = ' - LEFT JOIN profile_education AS edu ON (u.user_id = edu.uid) - LEFT JOIN profile_education_enum AS ede ON (ede.id = edu.eduid) - LEFT JOIN profile_job AS j ON (j.id = 0 AND j.uid = u.user_id) - LEFT JOIN profile_job_enum AS je ON (je.id = j.jobid) - LEFT JOIN profile_job_sector_enum AS es ON (j.sectorid = es.id) - LEFT JOIN fonctions_def AS ef ON (j.functionid = ef.id) - LEFT JOIN geoloc_pays AS n1 ON (u.nationalite = n1.a2) - LEFT JOIN geoloc_pays AS n2 ON (u.nationalite2 = n2.a2) - LEFT JOIN geoloc_pays AS n3 ON (u.nationalite2 = n3.a2) - LEFT JOIN profile_addresses AS adr ON (u.user_id = adr.pid AND FIND_IN_SET(\'current\', adr.flags)) - LEFT JOIN geoloc_countries AS gc ON (adr.countryId = gc.iso_3166_1_a2) - '//LEFT JOIN geoloc_region AS gr ON (adr.countryId = gr.a2 AND adr.region = gr.region) - . 'LEFT JOIN emails AS em ON (em.uid = u.user_id AND em.flags = \'active\')'; +@$globals->search->result_where_statement = " + LEFT JOIN profile_education AS edu ON (u.user_id = edu.uid) + LEFT JOIN profile_education_enum AS ede ON (ede.id = edu.eduid) + LEFT JOIN profile_job AS j ON (j.id = 0 AND j.uid = u.user_id) + LEFT JOIN profile_job_enum AS je ON (je.id = j.jobid) + LEFT JOIN profile_job_sector_enum AS es ON (j.sectorid = es.id) + LEFT JOIN fonctions_def AS ef ON (j.functionid = ef.id) + LEFT JOIN geoloc_countries AS n1 ON (u.nationalite = n1.iso_3166_1_a2) + LEFT JOIN geoloc_countries AS n2 ON (u.nationalite2 = n2.iso_3166_1_a2) + LEFT JOIN geoloc_countries AS n3 ON (u.nationalite2 = n3.iso_3166_1_a2) + LEFT JOIN profile_addresses AS adr ON (u.user_id = adr.pid + AND FIND_IN_SET('current', adr.flags)) + LEFT JOIN geoloc_countries AS gc ON (adr.countryId = gc.iso_3166_1_a2) + LEFT JOIN geoloc_administrativeareas AS gr ON (adr.countryId = gr.country + AND adr.administrativeAreaId = gr.id) + LEFT JOIN emails AS em ON (em.uid = u.user_id AND em.flags = 'active')"; class UserSet extends PlSet { @@ -179,9 +181,9 @@ class MinificheView extends MultipageView u.deces != 0 AS dcd, u.deces, u.matricule_ax, FIND_IN_SET('femme', u.flags) AS sexe, je.name AS entreprise, je.url AS job_web, es.name AS secteur, ef.fonction_fr AS fonction, - IF(n1.nat = '', n1.pays, n1.nat) AS nat1, n1.a2 AS iso3166_1, - IF(n2.nat = '', n2.pays, n2.nat) AS nat2, n2.a2 AS iso3166_2, - IF(n3.nat = '', n3.pays, n3.nat) AS nat3, n3.a2 AS iso3166_3, + IF(n1.nat = '', n1.countryFR, n1.nat) AS nat1, n1.iso_3166_1_a2 AS iso3166_1, + IF(n2.nat = '', n2.countryFR, n2.nat) AS nat2, n2.iso_3166_1_a2 AS iso3166_2, + IF(n3.nat = '', n3.countryFR, n3.nat) AS nat3, n3.iso_3166_1_a2 AS iso3166_3, IF(ede0.abbreviation = '', ede0.name, ede0.abbreviation) AS eduname0, ede0.url AS eduurl0, IF(edd0.abbreviation = '', edd0.degree, edd0.abbreviation) AS edudegree0, edu0.grad_year AS edugrad_year0, f0.field AS edufield0, edu0.program AS eduprogram0, @@ -209,9 +211,9 @@ class MinificheView extends MultipageView LEFT JOIN profile_job_enum AS je ON (je.id = j.jobid) LEFT JOIN profile_job_sector_enum AS es ON (j.sectorid = es.id) LEFT JOIN fonctions_def AS ef ON (j.functionid = ef.id) - LEFT JOIN geoloc_pays AS n1 ON (u.nationalite = n1.a2) - LEFT JOIN geoloc_pays AS n2 ON (u.nationalite2 = n2.a2) - LEFT JOIN geoloc_pays AS n3 ON (u.nationalite3 = n3.a2) + LEFT JOIN geoloc_countries AS n1 ON (u.nationalite = n1.iso_3166_1_a2) + LEFT JOIN geoloc_countries AS n2 ON (u.nationalite2 = n2.iso_3166_1_a2) + LEFT JOIN geoloc_countries AS n3 ON (u.nationalite3 = n3.iso_3166_1_a2) LEFT JOIN profile_education AS edu0 ON (u.user_id = edu0.uid AND edu0.id = 0) LEFT JOIN profile_education_enum AS ede0 ON (ede0.id = edu0.eduid) LEFT JOIN profile_education_degree_enum AS edd0 ON (edd0.id = edu0.degreeid) @@ -233,8 +235,9 @@ class MinificheView extends MultipageView . (S::logged() ? "" : "AND adr.pub = 'public'") . ") LEFT JOIN geoloc_countries AS gc ON (adr.countryId = gc.iso_3166_a2) - " // LEFT JOIN geoloc_region AS gr ON (adr.country = gr.a2 AND adr.region = gr.region) - . "LEFT JOIN emails AS em ON (em.uid = u.user_id AND em.flags = 'active') + LEFT JOIN geoloc_administrativeareas AS gr ON (adr.countryId = gr.country + AND adr.administrativeAreaId = gr.id) + LEFT JOIN emails AS em ON (em.uid = u.user_id AND em.flags = 'active') INNER JOIN profile_display AS d ON (d.pid = u.user_id)" . (S::logged() ? "LEFT JOIN contacts AS c ON (c.contact = u.user_id AND c.uid = " . S::v('uid') . ")" : ""); @@ -388,14 +391,15 @@ class GadgetView implements PlView public function joins() { - return "LEFT JOIN profile_addresses AS adr ON (u.user_id = adr.pid AND - FIND_IN_SET('current', adr.flags)" - . (S::logged() ? "" : "AND adr.pub = 'public'") . ") - LEFT JOIN geoloc_countries AS gc ON (adr.countryId = gc.iso_3166_1_a2)" - // LEFT JOIN geoloc_region AS gr ON (adr.country = gr.a2 AND adr.region = gr.region) - . (S::logged() ? - "LEFT JOIN contacts AS c ON (c.contact = u.user_id AND c.uid = " . S::v('uid') . ")" - : ""); + return "LEFT JOIN profile_addresses AS adr ON (u.user_id = adr.pid AND + FIND_IN_SET('current', adr.flags)" + . (S::logged() ? "" : "AND adr.pub = 'public'") . ") + LEFT JOIN geoloc_countries AS gc ON (adr.countryId = gc.iso_3166_1_a2) + LEFT JOIN geoloc_administrativeareas AS gr ON (adr.countryId = gr.country + AND adr.administrativeAreaId = gr.id) + " . (S::logged() ? + "LEFT JOIN contacts AS c ON (c.contact = u.user_id + AND c.uid = " . S::v('uid') . ")" : ""); } public function apply(PlPage &$page) diff --git a/modules/profile.php b/modules/profile.php index 4583907..60747e5 100644 --- a/modules/profile.php +++ b/modules/profile.php @@ -650,11 +650,11 @@ class ProfileModule extends PLModule $page->assign_by_ref('secteurs', $secteurs); $page->assign_by_ref('ss_secteurs', $ss_secteurs); - //pays + // Countries. $res = XDB::query( - "SELECT gp.pays + "SELECT gc.countryFR FROM profile_mentor_country AS m - LEFT JOIN geoloc_pays AS gp ON (m.country = gp.a2) + LEFT JOIN geoloc_countries AS gc ON (m.country = gc.iso_3166_1_a2) WHERE uid = {?}", $user->id()); $page->assign('pays', $res->fetchColumn()); @@ -735,13 +735,13 @@ class ProfileModule extends PLModule $page->changeTpl('include/field.select.tpl', NO_SKIN); $page->assign('name', 'pays_sel'); $where = ($ssect ? ' AND ms.subsectorid = {?}' : ''); - $it = XDB::iterator("SELECT a2 AS id, pays AS field - FROM geoloc_pays AS g - INNER JOIN profile_mentor_country AS mp ON (mp.country = g.a2) + $it = XDB::iterator("SELECT gc.iso_3166_1_a2 AS id, gc.countryFR 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_sector AS ms ON (ms.uid = mp.uid) - WHERE ms.sectorid = {?} $where - GROUP BY a2 - ORDER BY pays", $sect, $ssect); + WHERE ms.sectorid = {?} " . $where . " + GROUP BY iso_3166_1_a2 + ORDER BY countryFR", $sect, $ssect); $page->assign('list', $it); } diff --git a/modules/profile/jobs.inc.php b/modules/profile/jobs.inc.php index 1e19fc1..a56a6bb 100644 --- a/modules/profile/jobs.inc.php +++ b/modules/profile/jobs.inc.php @@ -210,22 +210,21 @@ class ProfileJobs extends ProfilePage $res = XDB::iterRow("SELECT j.id, je.name, j.functionid, j.sectorid, j.subsectorid, j.subsubsectorid, j.description, e.adr1, e.adr2, e.adr3, e.postcode, e.city, e.cityid, e.region, e.regiontxt, - e.country, gp.pays, gp.display, - FIND_IN_SET('geoloc', flags), + e.country, gc.countryFR, pa.accuracy, j.email, j.url, j.pub, e.adr_pub, j.email_pub, e.glat, e.glng, s.name FROM profile_job AS j LEFT JOIN profile_job_enum AS je ON (j.jobid = je.id) LEFT JOIN entreprises AS e ON (j.uid = e.uid AND j.id = e.entrid) - LEFT JOIN geoloc_pays AS gp ON (gp.a2 = e.country) + LEFT JOIN geoloc_countries AS gc ON (gc.iso_3166_1_a2 = e.country) LEFT JOIN profile_job_subsubsector_enum AS s ON (s.id = j.subsubsectorid) WHERE j.uid = {?} ORDER BY entrid", S::i('uid')); $this->values['jobs'] = array(); while (list($id, $name, $function, $secteur, $ss_secteur, $sss_secteur, $description, $w_adr1, $w_adr2, $w_adr3, $w_postcode, $w_city, $w_cityid, - $w_region, $w_regiontxt, $w_country, $w_countrytxt, $w_display, + $w_region, $w_regiontxt, $w_country, $w_countrytxt, $w_checked, $w_email, $w_web, $pub, $w_adr_pub, $w_email_pub, $w_glat, $w_glng, $sss_secteur_name ) = $res->next()) { @@ -247,9 +246,8 @@ class ProfileJobs extends ProfilePage 'regiontxt' => $w_regiontxt, 'country' => $w_country, 'countrytxt' => $w_countrytxt, - 'display' => $w_display, 'pub' => $w_adr_pub, - 'checked' => $w_checked, + 'checked' => (($w_checked == 0)? true : false), 'precise_lat' => $w_glat, 'precise_lon' => $w_glng), 'w_email' => $w_email, diff --git a/modules/profile/mentor.inc.php b/modules/profile/mentor.inc.php index 5de721d..54858b9 100644 --- a/modules/profile/mentor.inc.php +++ b/modules/profile/mentor.inc.php @@ -78,9 +78,9 @@ class ProfileCountry implements ProfileSetting $success = true; if (is_null($value)) { $value = array(); - $res = XDB::iterRow("SELECT m.country, p.pays + $res = XDB::iterRow("SELECT m.country, gc.countryFR FROM profile_mentor_country AS m - INNER JOIN geoloc_pays AS p ON (m.country = p.a2) + INNER JOIN geoloc_countries AS gc ON (m.country = gc.iso_3166_1_a2) WHERE m.uid = {?}", S::i('uid')); while (list($id, $name) = $res->next()) { diff --git a/modules/search.php b/modules/search.php index 8a5710b..d7188ef 100644 --- a/modules/search.php +++ b/modules/search.php @@ -164,10 +164,12 @@ class SearchModule extends PLModule $this->form_prepare(); } else { $textFields = array( - 'country' => array('field' => 'a2', 'table' => 'geoloc_pays', 'text' => 'pays', 'exact' => false), + 'country' => array('field' => 'iso_3166_1_a2', 'table' => 'geoloc_countries', 'text' => 'countryFR', + 'exact' => false), 'fonction' => array('field' => 'id', 'table' => 'fonctions_def', 'text' => 'fonction_fr', 'exact' => true), 'secteur' => array('field' => 'id', 'table' => 'profile_job_sector_enum', 'text' => 'name', 'exact' => false), - 'nationalite' => array('field' => 'a2', 'table' => 'geoloc_pays', 'text' => 'nat', 'exact' => 'false'), + 'nationalite' => array('field' => 'iso_3166_1_a2', 'table' => 'geoloc_countries', + 'text' => 'nationalityFR', 'exact' => 'false'), 'binet' => array('field' => 'id', 'table' => 'binets_def', 'text' => 'text', 'exact' => false), 'networking_type' => array('field' => 'network_type', 'table' => 'profile_networking_enum', 'text' => 'name', 'exact' => false), @@ -176,7 +178,7 @@ class SearchModule extends PLModule 'exact' => false), 'section' => array('field' => 'id', 'table' => 'sections', 'text' => 'text', 'exact' => false), 'school' => array('field' => 'id', 'table' => 'profile_education_enum', 'text' => 'name', 'exact' => false), - 'city' => array('table' => 'geoloc_city', 'text' => 'name', 'exact' => false) + 'city' => array('table' => 'geoloc_localities', 'text' => 'name', 'exact' => false) ); if (!Env::has('page')) { S::logger()->log('search', 'adv=' . var_export($_GET, true)); @@ -270,18 +272,17 @@ class SearchModule extends PLModule $realid = '`profile_networking_enum`.`network_type`'; break; case 'city': - $db = '`geoloc_city` INNER JOIN - `adresses` ON(`geoloc_city`.`id` = `adresses`.`cityid`)'; - $unique='`uid`'; - $field='`geoloc_city`.`name`'; + $db = 'geoloc_localities INNER JOIN + profile_addresses ON (geoloc_localities.id = profile_addresses.localityId)'; + $unique = 'uid'; + $field ='geoloc_localities.name'; break; case 'countryTxt': - $db = '`geoloc_pays` INNER JOIN - `adresses` ON(`geoloc_pays`.`a2` = `adresses`.`country`)'; - $unique = '`uid`'; - $field = '`geoloc_pays`.`pays`'; - $field2 = '`geoloc_pays`.`country`'; - $realid = '`geoloc_pays`.`a2`'; + $db = 'geoloc_countries INNER JOIN + profile_addresses ON (geoloc_countries.iso_3166_1_a2 = profile_addresses.countryId)'; + $unique = 'pid'; + $field = 'geoloc_countries.countryFR'; + $realid = 'geoloc_countries.iso_3166_1_a2'; break; case 'entreprise': $db = 'profile_job_enum INNER JOIN @@ -310,14 +311,12 @@ class SearchModule extends PLModule $unique = 'm.uid'; break; case 'nationaliteTxt': - $db = '`geoloc_pays` INNER JOIN - `auth_user_md5` ON (`geoloc_pays`.`a2` = `auth_user_md5`.`nationalite` OR - `geoloc_pays`.`a2` = `auth_user_md5`.`nationalite2` OR - `geoloc_pays`.`a2` = `auth_user_md5`.`nationalite3`)'; - $field = 'IF(`geoloc_pays`.`nat`=\'\', - `geoloc_pays`.`pays`, - `geoloc_pays`.`nat`)'; - $realid = '`geoloc_pays`.`a2`'; + $db = 'geoloc_countries INNER JOIN + auth_user_md5 ON (geoloc_countries.a2 = auth_user_md5.nationalite + OR geoloc_countries.a2 = auth_user_md5.nationalite2 + OR geoloc_countries.a2 = auth_user_md5.nationalite3)'; + $field = 'geoloc_countries.nationalityFR'; + $realid = 'geoloc_countries.iso_3166_1_a2'; break; case 'description': $db = 'profile_job'; @@ -426,9 +425,9 @@ class SearchModule extends PLModule $id = '`network_type`'; break; case 'country': - $db = '`geoloc_pays`'; - $field = '`pays`'; - $id = '`a2`'; + $db = 'geoloc_countries'; + $field = 'countryFR'; + $id = 'iso_3166_1_a2'; $page->assign('onchange', 'changeCountry(this.value)'); break; case 'fonction': @@ -446,19 +445,19 @@ class SearchModule extends PLModule $field = 'nom'; break; case 'nationalite': - $db = '`geoloc_pays` INNER JOIN - `auth_user_md5` ON (`geoloc_pays`.`a2` = `auth_user_md5`.`nationalite` OR - `geoloc_pays`.`a2` = `auth_user_md5`.`nationalite2` OR - `geoloc_pays`.`a2` = `auth_user_md5`.`nationalite3`)'; - $field = 'IF(`nat`=\'\', `pays`, `nat`)'; - $id = '`a2`'; + $db = 'geoloc_countries INNER JOIN + auth_user_md5 ON (geoloc_countries.iso_3166_1_a2 = auth_user_md5.nationalite + OR geoloc_countries.iso_3166_1_a2 = auth_user_md5.nationalite2 + OR geoloc_countries.iso_3166_1_a2 = auth_user_md5.nationalite3)'; + $field = 'nationalityFR'; + $id = 'iso_3166_1_a2'; break; case 'region': - $db = '`geoloc_region`'; - $field = '`name`'; - $id = '`region`'; + $db = 'geoloc_administrativeareas'; + $field = 'name'; + $id = 'id'; if (isset($_REQUEST['country'])) { - $where .= ' WHERE `a2` = "'.$_REQUEST['country'].'"'; + $where .= ' WHERE country = "' . $_REQUEST['country'] . '"'; } break; case 'school': diff --git a/modules/search/classes.inc.php b/modules/search/classes.inc.php index a7d0360..62a3ad7 100644 --- a/modules/search/classes.inc.php +++ b/modules/search/classes.inc.php @@ -59,7 +59,7 @@ else IF(e.pub='public', je.name, '') AS entreprise, IF(nw.pub='public', nw.address, '') AS networking_address, IF(nw.pub='public', nwe.name, '') AS networking_name,"; -@$globals->search->result_where_statement = ' +@$globals->search->result_where_statement = " LEFT JOIN profile_education AS edu0 ON (u.user_id = edu0.uid AND edu0.id = 0) LEFT JOIN profile_education_enum AS ede0 ON (ede0.id = edu0.eduid) LEFT JOIN profile_education_degree_enum AS edd0 ON (edd0.id = edu0.degreeid) @@ -80,15 +80,17 @@ else LEFT JOIN profile_job_enum AS ee ON (e.jobid = ee.id) LEFT JOIN profile_job_sector_enum AS es ON (es.id = e.sectorid) LEFT JOIN fonctions_def AS ef ON (e.fonction = ef.id) - LEFT JOIN geoloc_pays AS n1 ON (u.nationalite = n1.a2) - LEFT JOIN geoloc_pays AS n2 ON (u.nationalite2 = n2.a2) - LEFT JOIN geoloc_pays AS n3 ON (u.nationalite3 = n3.a2) - LEFT JOIN adresses AS adr ON (u.user_id = adr.uid AND FIND_IN_SET(\'active\',adr.statut)) - LEFT JOIN geoloc_pays AS gp ON (adr.country = gp.a2) - LEFT JOIN geoloc_region AS gr ON (adr.country = gr.a2 AND adr.region = gr.region) - LEFT JOIN emails AS em ON (em.uid = u.user_id AND em.flags = \'active\') + LEFT JOIN geoloc_countries AS n1 ON (u.nationalite = n1.iso_3166_1_a2) + LEFT JOIN geoloc_countries AS n2 ON (u.nationalite2 = n2.iso_3166_1_a2) + LEFT JOIN geoloc_countries AS n3 ON (u.nationalite3 = n3.iso_3166_1_a2) + LEFT JOIN profile_addresses AS adr ON (u.user_id = adr.pid + AND FIND_IN_SET('current', adr.flags)) + LEFT JOIN geoloc_countries AS gp ON (adr.countryId = gp.iso_3166_1_a2) + LEFT JOIN geoloc_administrativeareas AS gr ON (adr.countryId = gr.country + AND adr.administrativeAreaId = gr.id) + LEFT JOIN emails AS em ON (em.uid = u.user_id AND em.flags = 'active') LEFT JOIN profile_networking AS nw ON (nw.uid = u.user_id) - LEFT JOIN profile_networking_enum AS nwe ON (nwe.network_type = nw.network_type)'; + LEFT JOIN profile_networking_enum AS nwe ON (nwe.network_type = nw.network_type)"; // }}} // {{{ class ThrowError diff --git a/modules/search/search.inc.php b/modules/search/search.inc.php index 95938db..ecef2cf 100644 --- a/modules/search/search.inc.php +++ b/modules/search/search.inc.php @@ -22,7 +22,7 @@ require_once dirname(__FILE__).'/classes.inc.php'; // {{{ function advancedSearchFromInput -function getadr_join($table) { +function getAddressJoin($table) { return 'u.user_id = ' . $table . '.pid' . (Env::v('only_current', false) ? ' AND FIND_IN_SET(\'current\', ' . $table . '.flags)' : ''); } function advancedSearchFromInput() @@ -45,15 +45,10 @@ function advancedSearchFromInput() $referentField = null; } - if (!Env::i('cityid')) { - $townField = new RefSField('city', array('ac.city', 'ac.postcode'), 'adresses', 'ac', getadr_join('ac'), false); - } else { - $townField = new RefSField('cityid', array('av.cityid', 'av.postcode'), 'adresses', 'av', getadr_join('av')); - } - $countryField = new RefSField('country', array('ap.country'), 'adresses', 'ap', getadr_join('ap')); - $regionField = new RefSField('region',array('ar.region'), 'adresses', 'ar', getadr_join('ar')); - $mapField = new MapSField('mapid', array('sgcim.map_id'), array('adresses', 'geoloc_city_in_maps'), - array('amp', 'sgcim'), array(getadr_join('amp'), 'amp.cityid = sgcim.city_id')); + $townField = new RefSField('city', array('av.localityId', 'av.postalCode'), 'profile_addresses', + 'av', getAddressJoin('av')); + $countryField = new RefSField('country', array('ap.countryId'), 'profile_addresses', 'ap', getAddressJoin('ap')); + $regionField = new RefSField('region',array('ar.administrativeAreaId'), 'profile_addresses', 'ar', getAddressJoin('ar')); $entrepriseField = new RefSField('entreprise', array('je.name'), '', '',''); $posteField = new RefSField('poste', array('ep.description'), 'profile_job', 'ep', 'u.user_id = ep.uid', false); @@ -85,7 +80,7 @@ function advancedSearchFromInput() return array( $nameField, $promo1Field, $promo2Field, $womanField, $subscriberField, $aliveField, - $townField, $countryField, $regionField, $mapField, $entrepriseField, + $townField, $countryField, $regionField, $entrepriseField, $posteField, $secteurField, $cvField, $natField, $binetField, $groupexField, $sectionField, $schoolField, $diplomaField, $freeField, $fonctionField, $nwAddressField, $nwTypeField, diff --git a/plugins/function.select_nat.php b/plugins/function.select_nat.php index 0d39a7b..3c9b4d0 100644 --- a/plugins/function.select_nat.php +++ b/plugins/function.select_nat.php @@ -21,8 +21,10 @@ function select_nat($valeur, $pad=false) { - $sql = "SELECT a2 AS id, IF(nat='', pays, nat) AS text FROM geoloc_pays WHERE nat IS NOT NULL ORDER BY text"; - $res = XDB::iterRow($sql); + $res = XDB::iterRow("SELECT iso_3166_1_a2 AS id, nationalityFR AS text + FROM geoloc_countries + WHERE nationalityFR IS NOT NULL + ORDER BY nationalityFR"); $sel = ' selected="selected"'; // on ajoute une entree vide si $pad est vrai -- 2.1.4