Merge remote branch 'origin/master' 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..f5ca0e3 100644 (file)
--- a/upgrade/newdirectory-0.0.1/11_jobs.sql
+++ b/upgrade/newdirectory-0.0.1/11_jobs.sql
@@ -1,6 +1,6 @@
 DROP TABLE IF EXISTS profile_job_enum;
 
 DROP TABLE IF EXISTS profile_job_enum;
 

-CREATE TABLE IF NOT EXISTS profile_job_enum (
+CREATE TABLE profile_job_enum (

   id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL DEFAULT '',
   acronym VARCHAR(255) NOT NULL DEFAULT '',
   id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL DEFAULT '',
   acronym VARCHAR(255) NOT NULL DEFAULT '',


@@ -11,15 +11,14 @@ CREATE TABLE IF NOT EXISTS profile_job_enum (
   AX_code BIGINT(10) UNSIGNED NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (name)
   AX_code BIGINT(10) UNSIGNED NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (name)

-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;

 
 DROP TABLE IF EXISTS profile_job;
 
 
 DROP TABLE IF EXISTS profile_job;
 

-CREATE TABLE IF NOT EXISTS profile_job (
+CREATE TABLE profile_job (

   id TINYINT(1) UNSIGNED NOT NULL,
   id TINYINT(1) UNSIGNED NOT NULL,

-  uid INT(11) NOT NULL DEFAULT 0,
+  pid INT(11) NOT NULL DEFAULT 0,

   jobid INT(6) UNSIGNED 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,


@@ -30,16 +29,20 @@ CREATE TABLE IF NOT EXISTS profile_job (
   email_pub ENUM('private', 'ax', 'public') DEFAULT 'private',
   PRIMARY KEY (uid, id),
   INDEX uid (uid)
   email_pub ENUM('private', 'ax', 'public') DEFAULT 'private',
   PRIMARY KEY (uid, id),
   INDEX uid (uid)

-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;

 
 INSERT IGNORE INTO  profile_job_enum (name, url)
             SELECT  entreprise, web
 
 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);
+              FROM  #x4dat#.entreprises;

 
 

+INSERT INTO  profile_job (id, pid, 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  #x4dat#.entreprises       AS e
+ INNER JOIN  profile_job_enum  AS j  ON (e.entreprise = j.name)
+  LEFT JOIN  #x4dat#.fonctions_def     AS f  ON (f.id = e.fonction)
+  LEFT JOIN  #x4dat#.emploi_ss_secteur AS ss ON (ss.id = e.ss_secteur)
+  LEFT JOIN  #x4dat#.emploi_secteur    AS s  ON (s.id = e.secteur);

 
 -- vim:set syntax=mysql:
 
 -- vim:set syntax=mysql: