Commit | Line | Data |
---|---|---|
c7139c07 SJ |
1 | DROP TABLE IF EXISTS profile_job_enum; |
2 | ||
d0293d9b | 3 | CREATE TABLE profile_job_enum ( |
c7139c07 SJ |
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 '', | |
b814a8b8 | 8 | email VARCHAR(255) NOT NULL DEFAULT '', |
c7139c07 SJ |
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) | |
950bf4f6 | 14 | ) ENGINE=InnoDB, CHARSET=utf8; |
c7139c07 SJ |
15 | |
16 | DROP TABLE IF EXISTS profile_job; | |
17 | ||
d0293d9b | 18 | CREATE TABLE profile_job ( |
c7139c07 | 19 | id TINYINT(1) UNSIGNED NOT NULL, |
ce0b2c6f | 20 | pid INT(11) NOT NULL DEFAULT 0, |
c7139c07 | 21 | jobid INT(6) UNSIGNED NOT NULL DEFAULT 0, |
c7139c07 SJ |
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', | |
aaeb5ec6 FB |
30 | PRIMARY KEY (uid, id), |
31 | INDEX uid (uid) | |
950bf4f6 | 32 | ) ENGINE=InnoDB, CHARSET=utf8; |
c7139c07 | 33 | |
f9cddbef SJ |
34 | INSERT IGNORE INTO profile_job_enum (name, url) |
35 | SELECT entreprise, web | |
100e66fc | 36 | FROM #x4dat#.entreprises; |
c7139c07 | 37 | |
ce0b2c6f | 38 | INSERT INTO profile_job (id, pid, jobid, email, pub, email_pub, description) |
563f86f5 SJ |
39 | SELECT e.entrid, e.uid, j.id, e.email, e.pub, e.email_pub, |
40 | CONCAT_WS(', ', IF(e.poste = '', NULL, e.poste), IF(e.fonction = 0, NULL, f.fonction_fr), | |
41 | IF(e.ss_secteur IS NULL , IF(e.secteur IS NULL, NULL, s.label), ss.label)) | |
100e66fc | 42 | FROM #x4dat#.entreprises AS e |
563f86f5 | 43 | INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name) |
100e66fc FB |
44 | LEFT JOIN #x4dat#.fonctions_def AS f ON (f.id = e.fonction) |
45 | LEFT JOIN #x4dat#.emploi_ss_secteur AS ss ON (ss.id = e.ss_secteur) | |
46 | LEFT JOIN #x4dat#.emploi_secteur AS s ON (s.id = e.secteur); | |
c7139c07 SJ |
47 | |
48 | -- vim:set syntax=mysql: |