Merge commit 'origin/fusionax' into account

[platal.git] / upgrade / newdirectory-0.0.1 / 11_jobs.sql

diff --git a/upgrade/newdirectory-0.0.1/11_jobs.sql b/upgrade/newdirectory-0.0.1/11_jobs.sql
index 51de47f..b10dbc6 100644 (file)
--- 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,
   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,
   sectorid TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
   subsectorid TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
   subsubsectorid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0,


@@ -36,10 +35,14 @@ INSERT IGNORE INTO  profile_job_enum (name, url)
             SELECT  entreprise, web
               FROM  entreprises;
 
             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:
 
 -- vim:set syntax=mysql: