X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2Fnewdirectory-0.0.1%2F11_jobs.sql;h=b10dbc64ac5bf44d4fe4a1a50da97ea949ef228e;hb=c12cc82e1d97b51e19b294942ea9450a34d929cd;hp=c096a42ca820c41ce64230db539270f6ef49cd51;hpb=2504be33ed7e7436c428f188bdafa9e72248131d;p=platal.git

diff --git a/upgrade/newdirectory-0.0.1/11_jobs.sql b/upgrade/newdirectory-0.0.1/11_jobs.sql
index c096a42..b10dbc6 100644
--- a/upgrade/newdirectory-0.0.1/11_jobs.sql
+++ b/upgrade/newdirectory-0.0.1/11_jobs.sql
@@ -19,7 +19,6 @@ CREATE TABLE IF NOT EXISTS profile_job (
   id TINYINT(1) UNSIGNED NOT NULL,
   uid INT(11) NOT NULL DEFAULT 0,
   jobid INT(6) UNSIGNED NOT NULL DEFAULT 0,
-  functionid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0,
   sectorid TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
   subsectorid TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
   subsubsectorid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0,
@@ -28,17 +27,22 @@ CREATE TABLE IF NOT EXISTS profile_job (
   email VARCHAR(255) NOT NULL DEFAULT '',
   pub ENUM('private', 'ax', 'public') DEFAULT 'private',
   email_pub ENUM('private', 'ax', 'public') DEFAULT 'private',
-  PRIMARY KEY (uid, id)
+  PRIMARY KEY (uid, id),
+  INDEX uid (uid)
 ) CHARSET=utf8;
 
 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)
-     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);
-
+INSERT INTO  profile_job (id, uid, jobid, email, pub, email_pub, description)
+     SELECT  e.entrid, e.uid, j.id, e.email, e.pub, e.email_pub,
+             CONCAT_WS(', ', IF(e.poste = '', NULL, e.poste), IF(e.fonction = 0, NULL, f.fonction_fr),
+                       IF(e.ss_secteur IS NULL , IF(e.secteur IS NULL, NULL, s.label), ss.label))
+       FROM  entreprises       AS e
+ INNER JOIN  profile_job_enum  AS j  ON (e.entreprise = j.name)
+  LEFT JOIN  fonctions_def     AS f  ON (f.id = e.fonction)
+  LEFT JOIN  emploi_ss_secteur AS ss ON (ss.id = e.ss_secteur)
+  LEFT JOIN  emploi_secteur    AS s  ON (s.id = e.secteur);
 
 -- vim:set syntax=mysql: