From 0efb08e688d7ba0de0a0fbf2116cd4d10791d598 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Sun, 9 Jan 2011 16:33:35 +0100 Subject: [PATCH] Also merges masters and doctorates info; XXX: to be removed if we don't get the M/D list before the merge. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- modules/fusionax.php | 13 +++++++------ modules/fusionax/Formations_MD.sql | 22 ++++++++++++++++++++++ modules/fusionax/formation.pl | 30 ++++++++++++++++++++++++++++++ upgrade/1.0.1/merge.php | 8 +++++++- 4 files changed, 66 insertions(+), 7 deletions(-) create mode 100644 modules/fusionax/Formations_MD.sql diff --git a/modules/fusionax.php b/modules/fusionax.php index d180c6d..1b07698 100644 --- a/modules/fusionax.php +++ b/modules/fusionax.php @@ -111,17 +111,17 @@ class FusionAxModule extends PLModule } else { // séparation de l'archive en fichiers par tables $file = $spoolpath . $file; - // Removes master and doctorate students - exec('grep -v "^[A-Z]\{2\}.[0-9]\{4\}[MD][0-9]\{3\}" ' . $file . ' > ' . $file . '.tmp'); - exec('mv -f ' . $file . '.tmp ' . $file); // Split export into specialised files exec('grep "^AD" ' . $file . ' > ' . $spoolpath . 'Adresses.txt'); exec('grep "^AN" ' . $file . ' > ' . $spoolpath . 'Anciens.txt'); + exec('grep "^FO.[0-9]\{4\}[MD][0-9]\{3\}.Etudiant" ' . $file . ' > ' . $spoolpath . 'Formations_MD.txt'); + exec('grep "^FO.[0-9]\{4\}[MD][0-9]\{3\}.Doct. de" ' . $file . ' >> ' . $spoolpath . 'Formations_MD.txt'); exec('grep "^FO" ' . $file . ' > ' . $spoolpath . 'Formations.txt'); exec('grep "^AC" ' . $file . ' > ' . $spoolpath . 'Activites.txt'); exec('grep "^EN" ' . $file . ' > ' . $spoolpath . 'Entreprises.txt'); exec($modulepath . 'formation.pl'); exec('mv -f ' . $spoolpath . 'Formations_out.txt ' . $spoolpath . 'Formations.txt'); + exec('mv -f ' . $spoolpath . 'Formations_MD_out.txt ' . $spoolpath . 'Formations_MD.txt'); $report[] = 'Fichier parsé.'; $report[] = 'Import dans la base en cours...'; $next = 'integrateSQL'; @@ -134,7 +134,8 @@ class FusionAxModule extends PLModule 1 => 'Adresses.sql', 2 => 'Anciens.sql', 3 => 'Formations.sql', - 4 => 'Entreprises.sql' + 4 => 'Entreprises.sql', + 5 => 'Formations_MD.sql' ); if ($file != '') { // récupère le contenu du fichier sql @@ -157,7 +158,7 @@ class FusionAxModule extends PLModule } else { $nextfile = 0; } - if ($nextfile > 4) { + if ($nextfile > 5) { // tous les fichiers ont été exécutés, on passe à l'étape suivante $next = 'adds1920'; } else { @@ -236,7 +237,7 @@ class FusionAxModule extends PLModule $next = 'clean'; } elseif ($action == 'clean') { // nettoyage du fichier temporaire - exec('rm -Rf ' . $spoolpath); + //exec('rm -Rf ' . $spoolpath); $report[] = 'Import finit.'; } foreach($report as $t) { diff --git a/modules/fusionax/Formations_MD.sql b/modules/fusionax/Formations_MD.sql new file mode 100644 index 0000000..cad2d6c --- /dev/null +++ b/modules/fusionax/Formations_MD.sql @@ -0,0 +1,22 @@ +-- Import complet des formations + +DROP TABLE IF EXISTS `fusionax_formations_md`; + +CREATE TABLE IF NOT EXISTS `fusionax_formations_md` ( + FO CHAR(2) NOT NULL COMMENT 'Vaut toujours FO pour cette table', + ax_id VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien', + field VARCHAR(255) DEFAULT NULL COMMENT 'Domaine de la formation', + pid INT(11) UNSIGNED DEFAULT NULL, + fieldid INT(2) DEFAULT NULL, + PRIMARY KEY (ax_id), + INDEX (pid), + INDEX (fieldid) +) ENGINE=InnoDB, CHARSET=utf8; + +LOAD DATA LOCAL INFILE '{?}Formations.txt' INTO TABLE fusionax_formations_md CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' +(FO, ax_id, field); + +UPDATE fusionax_formations_md SET field = TRIM(field); + UPDATE fusionax_formations_md AS f +INNER JOIN profile_education_field_enum AS e ON (f.field = e.field) + SET f.fieldid = e.id, f.field = NULL; diff --git a/modules/fusionax/formation.pl b/modules/fusionax/formation.pl index 69a8923..d10ce65 100755 --- a/modules/fusionax/formation.pl +++ b/modules/fusionax/formation.pl @@ -1632,3 +1632,33 @@ while () close(FILE); close(OUT); + +$path = $0; +$path =~ s/modules\/fusionax\/formation\.pl//; +$path .= "spool/fusionax/"; +$in = $path . "Formations_MD.txt"; +$out = $path . "Formations_MD_out.txt"; +open(FILE, "<:encoding(UTF-8)", $in) || die ("Formations_MD.txt failed to open."); +open(OUT, ">:encoding(UTF-8)", $out) || die ("Formations_MD_out.txt failed to open."); + +while () +{ + # Dates removal. + s/\r$//; + s/^(FO\t\w{8}\t(\w|\.|'|&| )+)\t.*$/\1/; + # Trailing tab, spaces and dot removal. + s/(\t| )*$//; + s/( \t|\t )/\t/g; + s/ +/ /g; + + # Removes diploma + s/Doct. de l'Ec. polytechnique//; + s/Doct. de l'Ec. Polytechnique//; + s/Etudiante en Master de l'Ec. polytechnique//; + s/Etudiant en Master de l'Ec. polytechnique//; + + print OUT $_; +} + +close(FILE); +close(OUT); diff --git a/upgrade/1.0.1/merge.php b/upgrade/1.0.1/merge.php index 9df21ee..19f82a7 100755 --- a/upgrade/1.0.1/merge.php +++ b/upgrade/1.0.1/merge.php @@ -12,7 +12,7 @@ XDB::rawExecute('DROP VIEW IF EXISTS fusionax_promo'); XDB::rawExecute('DROP TABLE IF EXISTS fusionax_import'); // Fills pid fields in all table, to avoid to many joins. -foreach (array('fusionax_activites', 'fusionax_adresses', 'fusionax_anciens', 'fusionax_formations') as $table) { +foreach (array('fusionax_activites', 'fusionax_adresses', 'fusionax_anciens', 'fusionax_formations', 'fusionax_formations_md') as $table) { XDB::rawExecute("UPDATE $table AS f INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) SET f.pid = p.pid"); @@ -289,6 +289,12 @@ echo "Addresses inclusions finished.\n"; // Retrieves education from AX database. This is the hardest part since AX only kept education as an unformated string. echo "Starts educations inclusions.\n"; +// Updates master and doctorate educational fields. +XDB::rawExecute("UPDATE profile_education AS e + INNER JOIN fusionax_formations_md AS f ON (f.pid = e.pid AND FIND_IN_SET('primary', e.flags)) + SET e.program = f.field, e.fieldid = f.fieldid"); +XDB::rawExecute('DROP TABLE IF EXISTS fusionax_formations_md'); + // Deletes empty educations. XDB::rawExecute("DELETE FROM fusionax_formations WHERE Intitule_formation = '' AND Intitule_diplome = '' AND Descr_formation = ''"); -- 2.1.4