Commit | Line | Data |
---|---|---|
c7139c07 SJ |
1 | DROP TABLE IF EXISTS profile_job_enum; |
2 | ||
3 | CREATE TABLE IF NOT EXISTS 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 '', | |
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) | |
14 | ) CHARSET=utf8; | |
15 | ||
16 | DROP TABLE IF EXISTS profile_job; | |
17 | ||
18 | CREATE TABLE IF NOT EXISTS profile_job ( | |
19 | id TINYINT(1) UNSIGNED NOT NULL, | |
20 | uid INT(11) NOT NULL DEFAULT 0, | |
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) | |
c7139c07 SJ |
32 | ) CHARSET=utf8; |
33 | ||
f9cddbef SJ |
34 | INSERT IGNORE INTO profile_job_enum (name, url) |
35 | SELECT entreprise, web | |
36 | FROM entreprises; | |
c7139c07 | 37 | |
563f86f5 SJ |
38 | INSERT INTO profile_job (id, uid, jobid, email, pub, email_pub, description) |
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)) | |
42 | FROM entreprises AS e | |
43 | INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name) | |
44 | LEFT JOIN fonctions_def AS f ON (f.id = e.fonction) | |
45 | LEFT JOIN emploi_ss_secteur AS ss ON (ss.id = e.ss_secteur) | |
46 | LEFT JOIN emploi_secteur AS s ON (s.id = e.secteur); | |
c7139c07 SJ |
47 | |
48 | -- vim:set syntax=mysql: |