modules/fusionax/Formations.sql

   1 -- Import complet des formations
   2
   3 DROP TABLE IF EXISTS `fusionax_formations`;
   4
   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)),
  20   INDEX (pid),
  21   INDEX (eduid),
  22   INDEX (degreeid),
  23   INDEX (fieldid)
  24 ) ENGINE=InnoDB, CHARSET=utf8;
  25
  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)
  28 SET
  29 Date_maj = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2));
  30
  31
  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 != '';