From f9cddbef7cfeeac827c8cace3375f52ae68ff940 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Wed, 10 Dec 2008 22:53:29 +0100 Subject: [PATCH] Display new jobs in the profile. --- include/emails.combobox.inc.php | 2 +- include/user.func.inc.php | 27 ++++++++++++++------------- include/userset.inc.php | 16 +++++++++------- templates/include/emploi.tpl | 3 ++- upgrade/newdirectory-0.0.1/11_jobs.sql | 10 +++++----- 5 files changed, 31 insertions(+), 27 deletions(-) diff --git a/include/emails.combobox.inc.php b/include/emails.combobox.inc.php index d91cf9b..31df31c 100644 --- a/include/emails.combobox.inc.php +++ b/include/emails.combobox.inc.php @@ -88,7 +88,7 @@ function fill_email_combobox(PlPage& $page) $res = XDB::query( "SELECT email - FROM entreprises + FROM profile_job WHERE uid = {?}", $user->id()); $res = $res->fetchAllAssoc(); $pro = array(); diff --git a/include/user.func.inc.php b/include/user.func.inc.php index 6067593..38b17ad 100644 --- a/include/user.func.inc.php +++ b/include/user.func.inc.php @@ -36,7 +36,7 @@ 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', 'profile_mentor_country', + $tables_to_clear = array('uid' => array('competences_ins', 'profile_job', 'langues_ins', 'profile_mentor_country', 'profile_mentor_sector', 'profile_mentor', 'perte_pass', 'watch_sub'), 'user_id' => array('requests', 'user_changes')); @@ -130,18 +130,19 @@ function get_not_registered_user($login, $iterator = false) function get_user_details_pro($uid, $view = 'private') { - $sql = "SELECT e.entreprise, s.label as secteur , ss.label as sous_secteur , f.fonction_fr as fonction, - e.poste, e.adr1, e.adr2, e.adr3, e.postcode, e.city, e.entrid, - gp.pays AS countrytxt, gr.name AS region, e.entrid, - e.pub, e.adr_pub, e.email, e.email_pub, e.web - FROM entreprises AS e - LEFT JOIN emploi_secteur AS s ON(e.secteur = s.id) - LEFT JOIN emploi_ss_secteur AS ss ON(e.ss_secteur = ss.id AND e.secteur = ss.secteur) - LEFT JOIN fonctions_def AS f ON(e.fonction = f.id) - LEFT JOIN geoloc_pays AS gp ON (gp.a2 = e.country) - LEFT JOIN geoloc_region AS gr ON (gr.a2 = e.country and gr.region = e.region) - WHERE e.uid = {?} - ORDER BY e.entrid"; + $sql = "SELECT en.name AS entreprise, s.name as secteur, f.fonction_fr as fonction, + j.description AS poste, gp.pays AS countrytxt, gr.name AS region, + j.id AS entrid, j.pub, j.email, j.email_pub, j.url AS w_web, en.url AS web, + e.adr1, e.adr2, e.adr3, e.postcode, e.city, e.adr_pub + FROM profile_job AS j + LEFT JOIN entreprises AS e ON (e.entrid = j.id AND e.uid = j.uid) + LEFT JOIN profile_job_enum AS en ON (j.jobid = en.id) + LEFT JOIN profile_job_subsubsector_enum AS s ON (j.subsubsectorid = s.id) + LEFT JOIN fonctions_def AS f ON (j.functionid = f.id) + LEFT JOIN geoloc_pays AS gp ON (gp.a2 = e.country) + LEFT JOIN geoloc_region AS gr ON (gr.a2 = e.country AND gr.region = e.region) + WHERE j.uid = {?} + ORDER BY j.id"; $res = XDB::query($sql, $uid); $all_pro = $res->fetchAllAssoc(); foreach ($all_pro as $i => $pro) { diff --git a/include/userset.inc.php b/include/userset.inc.php index ed173de..65e1a9f 100644 --- a/include/userset.inc.php +++ b/include/userset.inc.php @@ -26,9 +26,10 @@ 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 entreprises AS e ON (e.entrid = 0 AND e.uid = u.user_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 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) @@ -176,7 +177,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.name AS secteur, ef.fonction_fr AS fonction, + 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, @@ -200,9 +201,10 @@ 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 profile_job_sector_enum AS es ON (e.secteur = es.id) - LEFT JOIN fonctions_def AS ef ON (e.fonction = ef.id) + return "LEFT JOIN profile_job AS j ON (j.id = 0 AND j.uid = u.user_id".(S::logged() ? "" : " AND j.pub = 'public'").") + 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) diff --git a/templates/include/emploi.tpl b/templates/include/emploi.tpl index ac48047..d118934 100644 --- a/templates/include/emploi.tpl +++ b/templates/include/emploi.tpl @@ -26,7 +26,8 @@ {if $address.entreprise || $address.web} Ent/Org : - {$address.entreprise}{if $address.web} [site]{/if} + {if $address.web}{$address.entreprise}{$address.entreprise}{else}{$address.entreprise}{/if} + {if $address.w_web} [Page perso]{/if} {/if} {if $address.secteur} diff --git a/upgrade/newdirectory-0.0.1/11_jobs.sql b/upgrade/newdirectory-0.0.1/11_jobs.sql index 8505fb8..c096a42 100644 --- a/upgrade/newdirectory-0.0.1/11_jobs.sql +++ b/upgrade/newdirectory-0.0.1/11_jobs.sql @@ -31,12 +31,12 @@ CREATE TABLE IF NOT EXISTS profile_job ( PRIMARY KEY (uid, id) ) CHARSET=utf8; -INSERT INTO profile_job_enum (name) - SELECT DISTINCT(entreprise) - FROM entreprises; +INSERT IGNORE INTO profile_job_enum (name, url) + SELECT entreprise, web + FROM entreprises; -INSERT INTO profile_job (id, uid, jobid, description, email, pub, email_pub, functionid, url) - SELECT e.entrid, e.uid, j.id, e.poste, e.email, e.pub, e.email_pub, e.fonction, e.web +INSERT INTO profile_job (id, uid, jobid, description, email, pub, email_pub, functionid) + SELECT e.entrid, e.uid, j.id, e.poste, e.email, e.pub, e.email_pub, e.fonction FROM entreprises AS e INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name); -- 2.1.4