From 5fecdf6d91a56c64f44d06464faff9628e6d5a05 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Tue, 9 Dec 2008 18:07:54 +0100 Subject: [PATCH] Adapts mentoring to the new sectors. --- htdocs/javascript/profile.js | 12 +++---- include/user.func.inc.php | 6 ++-- include/userset.inc.php | 8 ++--- modules/profile.php | 55 +++++++++++++++-------------- modules/profile/mentor.inc.php | 28 +++++++-------- templates/profile/jobs.secteur.tpl | 3 +- upgrade/newdirectory-0.0.1/13_mentoring.sql | 33 +++++++++++++++++ 7 files changed, 91 insertions(+), 54 deletions(-) create mode 100644 upgrade/newdirectory-0.0.1/13_mentoring.sql diff --git a/htdocs/javascript/profile.js b/htdocs/javascript/profile.js index f8c3fbc..e26164a 100644 --- a/htdocs/javascript/profile.js +++ b/htdocs/javascript/profile.js @@ -640,13 +640,13 @@ function updateSecteur() document.getElementById('ss_secteur_sel').innerHTML = ''; return; } - $.get(platal_baseurl + 'profile/ajax/secteur/-1/' + secteur, + $.get(platal_baseurl + 'profile/ajax/secteur/-1/0/0/' + secteur, function(data) { - data = '' + data; - document.getElementById('ss_secteur_sel').innerHTML = data; - attachEvent(document.forms.prof_annu['jobs[-1][ss_secteur]'], 'change', updateSSecteur); + data = '' + data; + document.getElementById('ss_secteur_sel').innerHTML = data; + $(document.forms.prof_annu['jobs[-1][ss_secteur]']).change(updateSSecteur); }); } diff --git a/include/user.func.inc.php b/include/user.func.inc.php index 549d811..6067593 100644 --- a/include/user.func.inc.php +++ b/include/user.func.inc.php @@ -36,8 +36,8 @@ function user_clear_all_subs($user_id, $really_del=true) $user = User::getSilent($uid); list($alias) = explode('@', $user->forlifeEmail()); - $tables_to_clear = array('uid' => array('competences_ins', 'entreprises', 'langues_ins', 'mentor_pays', - 'mentor_secteurs', 'mentor', 'perte_pass', 'watch_sub'), + $tables_to_clear = array('uid' => array('competences_ins', 'entreprises', 'langues_ins', 'profile_mentor_country', + 'profile_mentor_sector', 'profile_mentor', 'perte_pass', 'watch_sub'), 'user_id' => array('requests', 'user_changes')); if ($really_del) { @@ -282,7 +282,7 @@ function &get_user_details($login, $from_uid = '', $view = 'private') LEFT JOIN geoloc_pays AS gp3 ON (gp3.a2 = u.nationalite3) INNER JOIN sections AS s ON (s.id = u.section) LEFT JOIN photo AS p ON (p.uid = u.user_id) - LEFT JOIN mentor AS m ON (m.uid = u.user_id) + LEFT JOIN profile_mentor AS m ON (m.uid = u.user_id) LEFT JOIN emails AS e ON (e.uid = u.user_id AND e.flags='active') INNER JOIN profile_names_display AS nd ON (nd.user_id = u.user_id) INNER JOIN profile_display AS d ON (d.uid = u.user_id) diff --git a/include/userset.inc.php b/include/userset.inc.php index c7d09f4..ed173de 100644 --- a/include/userset.inc.php +++ b/include/userset.inc.php @@ -27,7 +27,7 @@ global $globals; 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 entreprises AS e ON (e.entrid = 0 AND e.uid = u.user_id) - LEFT JOIN emploi_secteur AS es ON (e.secteur = es.id) + LEFT JOIN profile_job_sector_enum AS es ON (e.secteur = es.id) 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) @@ -176,7 +176,7 @@ class MinificheView extends MultipageView u.perms != 'pending' AS wasinscrit, u.deces != 0 AS dcd, u.deces, u.matricule_ax, FIND_IN_SET('femme', u.flags) AS sexe, - e.entreprise, e.web AS job_web, es.label AS secteur, ef.fonction_fr AS fonction, + e.entreprise, e.web 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, @@ -201,7 +201,7 @@ class MinificheView extends MultipageView public function joins() { return "LEFT JOIN entreprises AS e ON (e.entrid = 0 AND e.uid = u.user_id".(S::logged() ? "" : " AND e.pub = 'public'").") - LEFT JOIN emploi_secteur AS es ON (e.secteur = es.id) + LEFT JOIN profile_job_sector_enum AS es ON (e.secteur = es.id) 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) @@ -272,7 +272,7 @@ class MentorView extends MultipageView public function fields() { return "m.uid, d.promo_display, u.hruid, - m.expertise, mp.pid, ms.secteur, ms.ss_secteur, + m.expertise, mp.country, ms.sectorid, ms.subsectorid, nd.display AS name_display, nd.tooltip AS name_tooltip, nd.sort AS name_sort"; } diff --git a/modules/profile.php b/modules/profile.php index 6fe8ad3..da843b8 100644 --- a/modules/profile.php +++ b/modules/profile.php @@ -485,8 +485,11 @@ class ProfileModule extends PLModule $page->assign('id', $id); $page->assign('ssecteurs', $res); $page->assign('sel', $ssect); - $page->assign('jobid', $jobid); - $page->assign('jobpref', $jobpref); + if ($id != -1) { + $page->assign('change', 1); + $page->assign('jobid', $jobid); + $page->assign('jobpref', $jobpref); + } } function handler_ajax_ssecteur(&$page, $id, $ssect, $sssect = -1) @@ -603,16 +606,16 @@ class ProfileModule extends PLModule ///// recuperations infos referent //expertise - $res = XDB::query("SELECT expertise FROM mentor WHERE uid = {?}", $user->id()); + $res = XDB::query("SELECT expertise FROM profile_mentor WHERE uid = {?}", $user->id()); $page->assign('expertise', $res->fetchOneCell()); //secteurs $secteurs = $ss_secteurs = Array(); $res = XDB::iterRow( "SELECT s.label, ss.label - FROM mentor_secteurs AS m - LEFT JOIN emploi_secteur AS s ON(m.secteur = s.id) - LEFT JOIN emploi_ss_secteur AS ss ON(m.secteur = ss.secteur AND m.ss_secteur = ss.id) + FROM profile_mentor_sector AS m + LEFT JOIN profile_job_sector_enum AS s ON(m.sectorid = s.id) + LEFT JOIN profile_job_subsector_enum AS ss ON(m.sectorid = ss.sectorid AND m.subsectorid = ss.id) WHERE uid = {?}", $user->id()); while (list($sec, $ssec) = $res->next()) { $secteurs[] = $sec; @@ -624,8 +627,8 @@ class ProfileModule extends PLModule //pays $res = XDB::query( "SELECT gp.pays - FROM mentor_pays AS m - LEFT JOIN geoloc_pays AS gp ON(m.pid = gp.a2) + FROM profile_mentor_country AS m + LEFT JOIN geoloc_pays AS gp ON (m.country = gp.a2) WHERE uid = {?}", $user->id()); $page->assign('pays', $res->fetchColumn()); @@ -640,7 +643,7 @@ class ProfileModule extends PLModule $page->setTitle('Conseil Pro'); //recuperation des noms de secteurs - $res = XDB::iterRow("SELECT id, label FROM emploi_secteur"); + $res = XDB::iterRow("SELECT id, name AS label FROM profile_job_sector_enum"); $secteurs[''] = ''; while (list($tmp_id, $tmp_label) = $res->next()) { $secteurs[$tmp_id] = $tmp_label; @@ -648,7 +651,7 @@ class ProfileModule extends PLModule $page->assign_by_ref('secteurs', $secteurs); // nb de mentors - $res = XDB::query("SELECT count(*) FROM mentor"); + $res = XDB::query("SELECT count(*) FROM profile_mentor"); $page->assign('mentors_number', $res->fetchOneCell()); // On vient d'un formulaire @@ -659,12 +662,12 @@ class ProfileModule extends PLModule $expertise_champ = XDB::escape(Env::v('expertise')); if ($pays_sel != "''") { - $where[] = "mp.pid = $pays_sel"; + $where[] = "mp.country = $pays_sel"; } if ($secteur_sel != "''") { - $where[] = "ms.secteur = $secteur_sel"; + $where[] = "ms.sectorid = $secteur_sel"; if ($ss_secteur_sel != "''") { - $where[] = "ms.ss_secteur = $ss_secteur_sel"; + $where[] = "ms.subsectorid = $ss_secteur_sel"; } } if ($expertise_champ != "''") { @@ -674,9 +677,9 @@ class ProfileModule extends PLModule if ($where) { $where = join(' AND ', $where); - $set = new UserSet("INNER JOIN mentor AS m ON (m.uid = u.user_id) - LEFT JOIN mentor_pays AS mp ON (mp.uid = m.uid) - LEFT JOIN mentor_secteurs AS ms ON (ms.uid = m.uid)", + $set = new UserSet("INNER JOIN profile_mentor AS m ON (m.uid = u.user_id) + LEFT JOIN profile_mentor_country AS mp ON (mp.uid = m.uid) + LEFT JOIN profile_mentor_sector AS ms ON (ms.uid = m.uid)", $where); $set->addMod('mentor', 'Référents'); $set->apply('referent/search', $page, $action, $subaction); @@ -694,9 +697,9 @@ class ProfileModule extends PLModule $page->assign('onchange', 'setSSecteurs()'); $page->assign('id', 'ssect_field'); $page->assign('name', 'ss_secteur'); - $it = XDB::iterator("SELECT id,label AS field - FROM emploi_ss_secteur - WHERE secteur = {?}", $sect); + $it = XDB::iterator("SELECT id, name AS field + FROM profile_job_subsector_enum + WHERE sectorid = {?}", $sect); $page->assign('list', $it); } @@ -705,14 +708,14 @@ class ProfileModule extends PLModule header('Content-Type: text/html; charset=utf-8'); $page->changeTpl('include/field.select.tpl', NO_SKIN); $page->assign('name', 'pays_sel'); - $where = ($ssect ? ' AND ms.ss_secteur = {?}' : ''); + $where = ($ssect ? ' AND ms.subsectorid = {?}' : ''); $it = XDB::iterator("SELECT a2 AS id, pays AS field - FROM geoloc_pays AS g - INNER JOIN mentor_pays AS mp ON (mp.pid = g.a2) - INNER JOIN mentor_secteurs AS ms ON (ms.uid = mp.uid) - WHERE ms.secteur = {?} $where - GROUP BY a2 - ORDER BY pays", $sect, $ssect); + FROM geoloc_pays AS g + INNER JOIN profile_mentor_country AS mp ON (mp.country = g.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); $page->assign('list', $it); } diff --git a/modules/profile/mentor.inc.php b/modules/profile/mentor.inc.php index bf577d9..d8579e4 100644 --- a/modules/profile/mentor.inc.php +++ b/modules/profile/mentor.inc.php @@ -26,10 +26,10 @@ class ProfileSecteurs implements ProfileSetting $success = true; if (is_null($value)) { $value = array(); - $res = XDB::iterRow("SELECT m.secteur, m.ss_secteur, ss.label - FROM mentor_secteurs AS m - INNER JOIN emploi_secteur AS s ON(m.secteur = s.id) - INNER JOIN emploi_ss_secteur AS ss ON(s.id = ss.secteur AND m.ss_secteur = ss.id) + $res = XDB::iterRow("SELECT m.sectorid, m.subsectorid, ss.name + FROM profile_mentor_sector AS m + INNER JOIN profile_job_sector_enum AS s ON (m.sectorid = s.id) + INNER JOIN profile_job_subsector_enum AS ss ON (s.id = ss.sectorid AND m.subsectorid = ss.id) WHERE m.uid = {?}", S::i('uid')); while (list($s, $ss, $ssname) = $res->next()) { @@ -42,7 +42,7 @@ class ProfileSecteurs implements ProfileSetting } else if (!is_array($value)) { $value = array(); } else if (count($value) > 10) { - Platal::page()->trigError("Le nombre de secteurs d'expertise est limité à 10"); + Platal::page()->trigError("Le nombre de secteurs d'expertise est limité à 10."); $success = false; } ksort($value); @@ -55,7 +55,7 @@ class ProfileSecteurs implements ProfileSetting public function save(ProfilePage &$page, $field, $value) { - XDB::execute("DELETE FROM mentor_secteurs + XDB::execute("DELETE FROM profile_mentor_sector WHERE uid = {?}", S::i('uid')); if (!count($value)) { @@ -63,7 +63,7 @@ class ProfileSecteurs implements ProfileSetting } foreach ($value as $id=>&$sect) { foreach ($sect as $sid=>&$name) { - XDB::execute("INSERT INTO mentor_secteurs (uid, secteur, ss_secteur) + XDB::execute("INSERT INTO profile_mentor_sector (uid, sectorid, subsectorid) VALUES ({?}, {?}, {?})", S::i('uid'), $id, $sid); } @@ -78,9 +78,9 @@ class ProfileCountry implements ProfileSetting $success = true; if (is_null($value)) { $value = array(); - $res = XDB::iterRow("SELECT m.pid, p.pays - FROM mentor_pays AS m - INNER JOIN geoloc_pays AS p ON(m.pid = p.a2) + $res = XDB::iterRow("SELECT m.country, p.pays + FROM profile_mentor_country AS m + INNER JOIN geoloc_pays AS p ON (m.country = p.a2) WHERE m.uid = {?}", S::i('uid')); while (list($id, $name) = $res->next()) { @@ -98,11 +98,11 @@ class ProfileCountry implements ProfileSetting public function save(ProfilePage &$page, $field, $value) { - XDB::execute("DELETE FROM mentor_pays + XDB::execute("DELETE FROM profile_mentor_country WHERE uid = {?}", S::i('uid')); foreach ($value as $id=>&$name) { - XDB::execute("INSERT INTO mentor_pays (uid, pid) + XDB::execute("INSERT INTO profile_mentor_country (uid, country) VALUES ({?}, {?})", S::i('uid'), $id); } @@ -151,8 +151,8 @@ class ProfileMentor extends ProfilePage public function _prepare(PlPage &$page, $id) { - $page->assign('secteurs_sel', XDB::iterator("SELECT id, label - FROM emploi_secteur")); + $page->assign('secteurs_sel', XDB::iterator("SELECT id, name AS label + FROM profile_job_sector_enum")); } } diff --git a/templates/profile/jobs.secteur.tpl b/templates/profile/jobs.secteur.tpl index fddea2e..1b5e31a 100644 --- a/templates/profile/jobs.secteur.tpl +++ b/templates/profile/jobs.secteur.tpl @@ -21,7 +21,8 @@ {**************************************************************************} - {iterate from=$ssecteurs item=ssecteur} {if $ssecteur.optgroup} diff --git a/upgrade/newdirectory-0.0.1/13_mentoring.sql b/upgrade/newdirectory-0.0.1/13_mentoring.sql new file mode 100644 index 0000000..dbd8127 --- /dev/null +++ b/upgrade/newdirectory-0.0.1/13_mentoring.sql @@ -0,0 +1,33 @@ +DROP TABLE IF EXISTS profile_mentor; +DROP TABLE IF EXISTS profile_mentor_country; +DROP TABLE IF EXISTS profile_mentor_sector; + +CREATE TABLE IF NOT EXISTS profile_mentor ( + uid INT(11) NOT NULL DEFAULT 0, + expertise TEXT NOT NULL, + PRIMARY KEY (uid), + FULLTEXT INDEX (expertise) +) CHARSET=utf8; + +INSERT INTO profile_mentor (uid, expertise) + SELECT uid, expertise + FROM mentor; + +CREATE TABLE IF NOT EXISTS profile_mentor_country ( + uid INT(11) NOT NULL DEFAULT 0, + country CHAR(2) NOT NULL DEFAULT "FR", + PRIMARY KEY (uid, country) +) CHARSET=utf8; + +INSERT INTO profile_mentor_country (uid, country) + SELECT uid, pid + FROM mentor_pays; + +CREATE TABLE IF NOT EXISTS profile_mentor_sector ( + uid INT(11) NOT NULL DEFAULT 0, + sectorid TINYINT(2) UNSIGNED NOT NULL, + subsectorid TINYINT(3) UNSIGNED NOT NULL, + PRIMARY KEY (uid, sectorid, subsectorid) +) CHARSET=utf8; + +-- vim:set syntax=mysql: -- 2.1.4