1 -- Import complet des formations
3 DROP TABLE IF EXISTS `fusionax_formations`
;
5 CREATE TABLE IF NOT EXISTS `fusionax_formations`
(
6 Date_maj
DATE NOT NULL COMMENT 'Date de mise à jour de ces informations',
7 FO
CHAR(2) NOT NULL COMMENT 'Vaut toujours FO pour cette table',
8 ax_id
VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien',
9 Intitule_formation
VARCHAR(255) NOT NULL DEFAULT 0 COMMENT 'Intitulé de la formation',
10 Intitule_diplome
VARCHAR(255) NOT NULL DEFAULT 0 COMMENT 'Intitulé du diplôme',
11 Descr_formation
VARCHAR(255) NOT NULL COMMENT 'Description de la formation',
12 pid
INT(11) UNSIGNED
DEFAULT NULL,
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
),
17 INDEX (Intitule_diplome(60)),
18 INDEX (Intitule_formation(60)),
19 INDEX (Descr_formation(60)),
24 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
26 LOAD DATA LOCAL INFILE
'{?}Formations.txt' INTO TABLE fusionax_formations
CHARACTER SET utf8 FIELDS TERMINATED
BY '\t' LINES TERMINATED
BY '\n'
27 (@StringDate_maj
, FO
, ax_id
, Intitule_formation
, Intitule_diplome
, Descr_formation
)
29 Date_maj
= CONCAT(SUBSTRING(@StringDate_maj
,7),'-',SUBSTRING(@StringDate_maj
,4,2),'-',SUBSTRING(@StringDate_maj
,1,2));
32 UPDATE fusionax_formations
AS f
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
!= '';