Commit | Line | Data |
---|---|---|
b9ad0878 PC |
1 | -- Import complet des formations |
2 | ||
3 | DROP TABLE IF EXISTS `fusionax_formations`; | |
4 | ||
5 | CREATE TABLE IF NOT EXISTS `fusionax_formations` ( | |
723d4c6b | 6 | Date_maj DATE NOT NULL COMMENT 'Date de mise à jour de ces informations', |
c7eac294 | 7 | FO CHAR(2) NOT NULL COMMENT 'Vaut toujours FO pour cette table', |
e48763dc | 8 | ax_id VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien', |
53595c2a | 9 | Intitule_formation VARCHAR(255) NOT NULL DEFAULT 0 COMMENT 'Intitulé de la formation', |
1dd21852 | 10 | Intitule_diplome VARCHAR(255) NOT NULL DEFAULT 0 COMMENT 'Intitulé du diplôme', |
53595c2a | 11 | Descr_formation VARCHAR(255) NOT NULL COMMENT 'Description de la formation', |
a48b23c3 | 12 | pid INT(11) UNSIGNED DEFAULT NULL, |
325af12b SJ |
13 | eduid INT(4) DEFAULT NULL, |
14 | degreeid INT(4) DEFAULT NULL, | |
15 | fieldid INT(2) DEFAULT NULL, | |
16 | PRIMARY KEY (ax_id, Intitule_diplome, Intitule_formation, Descr_formation), | |
53595c2a SJ |
17 | INDEX (Intitule_diplome(60)), |
18 | INDEX (Intitule_formation(60)), | |
19 | INDEX (Descr_formation(60)), | |
a48b23c3 SJ |
20 | INDEX (pid), |
21 | INDEX (eduid), | |
22 | INDEX (degreeid), | |
23 | INDEX (fieldid) | |
ddc4c642 | 24 | ) ENGINE=InnoDB, CHARSET=utf8; |
b9ad0878 | 25 | |
53595c2a | 26 | LOAD DATA LOCAL INFILE '{?}Formations.txt' INTO TABLE fusionax_formations CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' |
723d4c6b SJ |
27 | (@StringDate_maj, FO, ax_id, Intitule_formation, Intitule_diplome, Descr_formation) |
28 | SET | |
29 | Date_maj = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); | |
30 | ||
53595c2a SJ |
31 | |
32 | UPDATE fusionax_formations AS f | |
1dd21852 SJ |
33 | INNER JOIN profile_education_enum AS e ON (f.Intitule_formation = e.abbreviation) |
34 | SET f.Intitule_formation = e.name | |
35 | WHERE f.Intitule_formation != ''; |