| 1 | DROP TABLE IF EXISTS profile_job_enum; |
| 2 | |
| 3 | CREATE TABLE profile_job_enum ( |
| 4 | id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT, |
| 5 | name VARCHAR(255) NOT NULL DEFAULT '', |
| 6 | acronym VARCHAR(255) NOT NULL DEFAULT '', |
| 7 | url VARCHAR(255) NOT NULL DEFAULT '', |
| 8 | email VARCHAR(255) NOT NULL DEFAULT '', |
| 9 | holdingid INT(6) UNSIGNED DEFAULT NULL, |
| 10 | NAF_code CHAR(5) NOT NULL DEFAULT '', |
| 11 | AX_code BIGINT(10) UNSIGNED NOT NULL, |
| 12 | PRIMARY KEY (id), |
| 13 | UNIQUE KEY (name) |
| 14 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 15 | |
| 16 | DROP TABLE IF EXISTS profile_job; |
| 17 | |
| 18 | CREATE TABLE profile_job ( |
| 19 | id TINYINT(1) UNSIGNED NOT NULL, |
| 20 | pid INT(11) NOT NULL DEFAULT 0, |
| 21 | jobid INT(6) UNSIGNED NOT NULL DEFAULT 0, |
| 22 | sectorid TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, |
| 23 | subsectorid TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, |
| 24 | subsubsectorid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0, |
| 25 | description VARCHAR(255) NOT NULL DEFAULT '', |
| 26 | url VARCHAR(255) NOT NULL DEFAULT '', |
| 27 | email VARCHAR(255) NOT NULL DEFAULT '', |
| 28 | pub ENUM('private', 'ax', 'public') DEFAULT 'private', |
| 29 | email_pub ENUM('private', 'ax', 'public') DEFAULT 'private', |
| 30 | PRIMARY KEY (pid, id), |
| 31 | INDEX pid (pid) |
| 32 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 33 | |
| 34 | INSERT IGNORE INTO profile_job_enum (name, url) |
| 35 | SELECT entreprise, web |
| 36 | FROM #x4dat#.entreprises |
| 37 | WHERE entreprise != ''; |
| 38 | |
| 39 | INSERT INTO profile_job (id, pid, jobid, email, pub, email_pub, description) |
| 40 | SELECT e.entrid, e.uid, j.id, e.email, IF(e.pub = '', 'private', e.pub), IF(e.email_pub = '', 'private', e.email_pub), |
| 41 | CONCAT_WS(', ', IF(e.poste = '', NULL, e.poste), IF(e.fonction = 0, NULL, f.fonction_fr), |
| 42 | IF(e.ss_secteur IS NULL , IF(e.secteur IS NULL, NULL, s.label), ss.label)) |
| 43 | FROM #x4dat#.entreprises AS e |
| 44 | INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name) |
| 45 | LEFT JOIN #x4dat#.fonctions_def AS f ON (f.id = e.fonction) |
| 46 | LEFT JOIN #x4dat#.emploi_ss_secteur AS ss ON (ss.id = e.ss_secteur) |
| 47 | LEFT JOIN #x4dat#.emploi_secteur AS s ON (s.id = e.secteur) |
| 48 | WHERE e.entreprise != ''; |
| 49 | |
| 50 | -- vim:set syntax=mysql: |