From ddc4c64239397960c7c95aad9153009b986038bb Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Tue, 28 Sep 2010 23:22:29 +0200 Subject: [PATCH] Updates and improves code for Xorg/AX directory merge. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- modules/fusionax.php | 347 ++++---- modules/fusionax/Activites.sql | 8 +- modules/fusionax/Adresses.sql | 24 +- modules/fusionax/Anciens.sql | 69 +- modules/fusionax/Entreprises.sql | 8 +- modules/fusionax/Formations.sql | 18 +- modules/fusionax/formation.pl | 4 +- modules/fusionax/import-ax.sh | 32 - templates/fusionax/deceased.tpl | 10 +- templates/fusionax/ids.tpl | 39 +- templates/fusionax/idsMissingInAx.tpl | 6 +- templates/fusionax/idsMissingInXorg.tpl | 6 +- templates/fusionax/idswrongInXorg.tpl | 6 +- templates/fusionax/import.tpl | 32 +- templates/fusionax/index.tpl | 27 +- templates/fusionax/listFusion.tpl | 30 +- templates/fusionax/promo.tpl | 4 +- templates/fusionax/view.tpl | 2 +- upgrade/1.0.1/14_ax_id.sql | 1090 ++++++++++++++++++++++++ upgrade/merge-0.0.1/000_1920.sql | 9 - upgrade/merge-0.0.1/00_names.sql | 8 - upgrade/merge-0.0.1/04_nationalities.sql | 28 - upgrade/merge-0.0.1/05_check_nationalities.sql | 10 - 23 files changed, 1455 insertions(+), 362 deletions(-) delete mode 100755 modules/fusionax/import-ax.sh create mode 100644 upgrade/1.0.1/14_ax_id.sql delete mode 100644 upgrade/merge-0.0.1/000_1920.sql delete mode 100644 upgrade/merge-0.0.1/00_names.sql delete mode 100644 upgrade/merge-0.0.1/04_nationalities.sql delete mode 100644 upgrade/merge-0.0.1/05_check_nationalities.sql diff --git a/modules/fusionax.php b/modules/fusionax.php index ac22a02..e5d853d 100644 --- a/modules/fusionax.php +++ b/modules/fusionax.php @@ -20,21 +20,17 @@ ***************************************************************************/ /** - * @brief Module to merge data from AX database + * Module to merge data from AX database: this will only be used once on + * production site and should be removed afterwards. * * Module to import data from another database of alumni that had * different schemas. The organization that used this db is called AX * hence the name of this module. * - * Datas are stored in an external server and you need a private key - * to connect to their server. + * Datas are stored in an export file. */ class FusionAxModule extends PLModule { - function __construct() - { - } - function handlers() { return array( @@ -50,68 +46,63 @@ class FusionAxModule extends PLModule function handler_index(&$page) { - $globals = Platal::globals(); - $page->changeTpl('fusionax/index.tpl'); $page->assign('xorg_title', 'Polytechnique.org - Fusion des annuaires'); - if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) { - $page->assign('lastimport', date("d-m-Y", $globals->fusionax->LastUpdate)); - } } /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */ - function handler_import(&$page, $action = 'index', $fileSQL = '') + function handler_import(&$page, $action = 'index', $file = '') { - $globals = Platal::globals(); - if ($action == 'index') { $page->changeTpl('fusionax/import.tpl'); - if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) { - $page->assign( - 'lastimport', - "le " . date("d/m/Y à H:i", $globals->fusionax->LastUpdate)); - } return; } // toutes les actions sont faites en ajax en utilisant jquery - header("Content-type: text/javascript; charset=utf-8"); + header('Content-type: text/javascript; charset=utf-8'); // log des actions $report = array(); - // création d'un fichier temporaire si nécessaire - if (Env::has('tmpdir')) { - $tmpdir = Env::v('tmpdir'); - } else { - $tmpdir = tempnam('/tmp', 'fusionax'); - unlink($tmpdir); - mkdir($tmpdir); - chmod($tmpdir, 0700); - } - $modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/'; - $olddir = getcwd(); - chdir($tmpdir); + $spoolpath = realpath(dirname(__FILE__) . '/../spool/fusionax/') . '/'; if ($action == 'launch') { - // séparation de l'archive en fichiers par tables - exec($modulepath . 'import-ax.sh', $report); - $report[] = 'Fichier parsé.'; - $report[] = 'Import dans la base en cours...'; - $next = 'integrateSQL'; + if ($file == '') { + $report[] = 'Nom de fichier non renseigné.'; + } elseif (!file_exists(dirname(__FILE__) . '/../spool/fusionax/' . $file)) { + $report[] = 'Le fichier ne se situe pas au bon endroit.'; + } 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" ' . $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'); + $report[] = 'Fichier parsé.'; + $report[] = 'Import dans la base en cours...'; + $next = 'integrateSQL'; + } } elseif ($action == 'integrateSQL') { // intégration des données dans la base MySQL // liste des fichiers sql à exécuter $filesSQL = array( - 'Activites.sql', - 'Adresses.sql', - 'Anciens.sql', - 'Formations.sql', - 'Entreprises.sql'); - if ($fileSQL != '') { + 0 => 'Activites.sql', + 1 => 'Adresses.sql', + 2 => 'Anciens.sql', + 3 => 'Formations.sql', + 4 => 'Entreprises.sql' + ); + if ($file != '') { // récupère le contenu du fichier sql - $queries = explode(';', file_get_contents($modulepath . $fileSQL)); + $queries = explode(';', file_get_contents($modulepath . $filesSQL[$file])); foreach ($queries as $q) { if (trim($q)) { // coupe le fichier en requêtes individuelles @@ -122,74 +113,139 @@ class FusionAxModule extends PLModule $report[] = addslashes($l); } // exécute la requête - XDB::execute($q); + XDB::execute(str_replace('{?}', $spoolpath, $q)); } } // trouve le prochain fichier à exécuter - $trans = array_flip($filesSQL); - $nextfile = $trans[$fileSQL] + 1; + $nextfile = $file + 1; } else { $nextfile = 0; } - if (!isset($filesSQL[$nextfile])) { - // tous les fichiers ont été exécutés, on passe à l'étape - // suivante - $next = 'clean'; + if ($nextfile > 4) { + // tous les fichiers ont été exécutés, on passe à l'étape suivante + $next = 'adds1920'; } else { // on passe au fichier suivant - $next = 'integrateSQL/' . $filesSQL[$nextfile]; + $next = 'integrateSQL/' . $nextfile; + } + } elseif ($action == 'adds1920') { + // Adds promotion 1920 from AX db. + $report[] = 'Ajout de la promotion 1920'; + $res = XDB::iterator('SELECT prenom, Nom_complet, ax_id + FROM fusionax_anciens + WHERE promotion_etude = 1920;'); + + $nameTypes = DirEnum::getOptions(DirEnum::NAMETYPES); + $nameTypes = array_flip($nameTypes); + $eduSchools = DirEnum::getOptions(DirEnum::EDUSCHOOLS); + $eduSchools = array_flip($eduSchools); + $eduDegrees = DirEnum::getOptions(DirEnum::EDUDEGREES); + $eduDegrees = array_flip($eduDegrees); + $degreeid = $eduDegrees[Profile::DEGREE_X]; + $entry_year = 1920; + $grad_year = 1923; + $promo = 'X1920'; + $sex = PlUser::GENDER_MALE; + $xorgId = 19200000; + $type = 'x'; + + while (list($firstname, $lastname, $ax_id) = $res->next()) { + $hrid = self::getHrid($firstname, $lastname, $promo); + $res1 = XDB::query('SELECT COUNT(*) + FROM accounts + WHERE hruid = {?}', $hrid); + $res2 = XDB::query('SELECT COUNT(*) + FROM profiles + WHERE hrpid = {?}', $hrid); + if (is_null($hrid) || $res1->fetchOneCell() > 0 || $res2->fetchOneCell() > 0) { + $report[] = $ax_id . ' non ajouté'; + } + $fullName = $firstname . ' ' . $lastname; + $directoryName = $lastname . ' ' . $firstname; + ++$xorgId; + + XDB::execute('REPLACE INTO profiles (hrpid, xorg_id, ax_id, sex) + VALUES ({?}, {?}, {?}, {?})', + $hrid, $xorgId, $ax_id, $sex); + $pid = XDB::insertId(); + XDB::execute('REPLACE INTO profile_name (pid, name, typeid) + VALUES ({?}, {?}, {?})', + $pid, $lastname, $nameTypes['name_ini']); + XDB::execute('REPLACE INTO profile_name (pid, name, typeid) + VALUES ({?}, {?}, {?})', + $pid, $firstname, $nameTypes['firstname_ini']); + XDB::execute('REPLACE INTO profile_display (pid, yourself, public_name, private_name, + directory_name, short_name, sort_name, promo) + VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})', + $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo); + XDB::execute('REPLACE INTO profile_education (pid, eduid, degreeid, entry_year, grad_year, flags) + VALUES ({?}, {?}, {?}, {?}, {?}, {?})', + $pid, $eduSchools[Profile::EDU_X], $degreeid, $entry_year, $grad_year, 'primary'); + XDB::execute('REPLACE INTO accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, sex) + VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?})', + $hrid, $type, 0, 'active', $fullName, $directoryName, $lastname, $sex); + $uid = XDB::insertId(); + XDB::execute('REPLACE INTO account_profiles (uid, pid, perms) + VALUES ({?}, {?}, {?})', + $uid, $pid, 'owner'); } + $report[] = 'Promo 1920 ajoutée.'; + $next = 'view'; + } elseif ($action == 'view') { + XDB::execute('CREATE OR REPLACE ALGORITHM=MERGE VIEW fusionax_xorg_anciens AS + SELECT p.pid, p.ax_id, pd.promo, pd.private_name, pd.public_name, + pd.sort_name, pd.short_name, pd.directory_name + FROM profiles AS p + INNER JOIN profile_display AS pd USING(pid)'); + $next = 'clean'; } elseif ($action == 'clean') { // nettoyage du fichier temporaire - chdir($olddir); - exec("rm -Rf $tmpdir", $report); - $report[] = "Fin de l\'import"; - // met à jour la date de dernier import - //$globals->change_dynamic_config(array('LastUpdate' => time()), 'FusionAx'); + exec('rm -Rf ' . $spoolpath); + $report[] = 'Import finit.'; } foreach($report as $t) { // affiche les lignes de report - echo "$('#fusionax_import').append('" . $t . "
');\n"; + echo "$('#fusionax').append('" . $t . "
');\n"; } if (isset($next)) { - $tmpdir = getcwd(); - chdir($olddir); // lance le prochain script s'il y en a un - echo "$.getScript('fusionax/import/" . $next . "?tmpdir=" . urlencode($tmpdir) . "');"; + echo "$.getScript('fusionax/import/" . $next . "');"; } // exit pour ne pas afficher la page template par défaut exit; } - /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */ function handler_view(&$page, $action = '') { - $globals = Platal::globals(); - $page->changeTpl('fusionax/view.tpl'); if ($action == 'create') { XDB::execute('DROP VIEW IF EXISTS fusionax_deceased'); XDB::execute('CREATE VIEW fusionax_deceased AS - SELECT u.user_id, a.id_ancien, u.nom, u.prenom, u.promo, u.deces AS deces_xorg, a.Date_deces AS deces_ax - FROM auth_user_md5 AS u - INNER JOIN fusionax_anciens AS a ON (a.id_ancien = u.matricule_ax) - WHERE u.deces != a.Date_deces'); + SELECT p.pid, a.ax_id, pd.private_name, pd.promo, p.deathdate AS deces_xorg, a.Date_deces AS deces_ax + FROM profiles AS p + INNER JOIN profile_display AS pd ON (p.pid = pd.pid) + INNER JOIN fusionax_anciens AS a ON (a.ax_id = p.ax_id) + WHERE p.deathdate != a.Date_deces'); XDB::execute('DROP VIEW IF EXISTS fusionax_promo'); XDB::execute('CREATE VIEW fusionax_promo AS - SELECT u.user_id, u.matricule_ax, CONCAT(u.nom, " ", u.prenom) AS display_name, u.promo AS promo_etude_xorg, - f.promotion_etude AS promo_etude_ax, u.promo_sortie AS promo_sortie_xorg - FROM auth_user_md5 AS u - INNER JOIN fusionax_anciens AS f ON (u.matricule_ax = f.id_ancien) - WHERE u.promo != f.promotion_etude AND !(f.promotion_etude = u.promo + 1 AND u.promo_sortie = u.promo + 4)'); + SELECT p.pid, p.ax_id, pd.private_name, pd.promo, pe.entry_year AS promo_etude_xorg, + f.promotion_etude AS promo_etude_ax, pe.grad_year AS promo_sortie_xorg + FROM profiles AS p + INNER JOIN profile_display AS pd ON (p.pid = pd.pid) + INNER JOIN profile_education AS pe ON (p.pid = pe.pid) + INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id) + WHERE pd.promo != CONCAT(\'X\', f.promotion_etude) + AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)'); + $page->trigSuccess('Les VIEW ont bien été créées.'); } } /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */ - private static function clear_wrong_in_xorg($user_id) + private static function clear_wrong_in_xorg($pid) { - $res = XDB::execute("UPDATE fusionax_xorg_anciens - SET matricule_ax = NULL - WHERE user_id = {?}", $user_id); + $res = XDB::execute('UPDATE fusionax_xorg_anciens + SET ax_id = NULL + WHERE pid = {?}', $pid); if (!$res) { return 0; } @@ -200,12 +256,12 @@ class FusionAxModule extends PLModule * (mises à part les promo 1921 et 1923 qui ne figurent pas dans les données de l'AX)*/ private static function find_wrong_in_xorg($limit = 10) { - return XDB::iterator("SELECT u.promo, u.user_id, u.display_name + return XDB::iterator('SELECT u.promo, u.pid, u.private_name FROM fusionax_xorg_anciens AS u WHERE NOT EXISTS (SELECT * FROM fusionax_anciens AS f - WHERE f.id_ancien = u.matricule_ax) - AND u.matricule_ax IS NOT NULL AND promo != 1921 AND promo != 1923"); + WHERE f.ax_id = u.ax_id) + AND u.ax_id IS NOT NULL AND promo != \'X1921\' AND promo != \'X1923\''); } /** Lier les identifiants d'un ancien dans les deux annuaires @@ -213,17 +269,17 @@ class FusionAxModule extends PLModule * @param matricule_ax identifiant dans l'annuaire de l'AX * @return 0 si la liaison a échoué, 1 sinon */ - private static function link_by_ids($user_id, $matricule_ax) + private static function link_by_ids($pid, $ax_id) { - $res = XDB::execute("UPDATE fusionax_import AS i + $res = XDB::execute('UPDATE fusionax_import AS i INNER JOIN fusionax_xorg_anciens AS u - SET u.matricule_ax = i.id_ancien, - i.user_id = u.user_id, + SET u.ax_id = i.ax_id, + i.pid = u.pid, i.date_match_id = NOW() - WHERE i.id_ancien = {?} AND u.user_id = {?} - AND (u.matricule_ax != {?} OR u.matricule_ax IS NULL - OR i.user_id != {?} OR i.user_id IS NULL)", - $matricule_ax, $user_id, $matricule_ax, $user_id); + WHERE i.ax_id = {?} AND u.pid = {?} + AND (u.ax_id != {?} OR u.ax_id IS NULL + OR i.pid != {?} OR i.pid IS NULL)', + $ax_id, $pid, $ax_id, $pid); if (!$res) { return 0; } @@ -234,68 +290,65 @@ class FusionAxModule extends PLModule * @param limit nombre d'anciens à trouver au max * @param sure si true, ne trouve que des anciens qui sont quasi sûrs * @return un XOrgDBIterator sur les entrées avec display_name, promo, - * user_id, id_ancien et display_name_ax + * pid, ax_id et display_name_ax */ private static function find_easy_to_link($limit = 10, $sure = false) { $easy_to_link = XDB::iterator(" - SELECT u.display_name, u.promo, u.user_id, ax.id_ancien, - CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax, + SELECT u.private_name, u.promo, u.pid, ax.ax_id, + CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X', ax.promotion_etude, ')') AS display_name_ax, COUNT(*) AS nbMatches FROM fusionax_anciens AS ax - INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL) - LEFT JOIN fusionax_xorg_anciens AS u ON (u.matricule_ax IS NULL - AND ax.Nom_patronymique = u.nom - AND ax.prenom = u.prenom - AND u.promo = ax.promotion_etude) - GROUP BY u.user_id - HAVING u.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : '')); + INNER JOIN fusionax_import AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL) + LEFT JOIN fusionax_xorg_anciens AS u ON (u.ax_id IS NULL + AND u.promo = CONCAT('X', ax.promotion_etude) + AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name + OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name + OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name)) + GROUP BY u.pid + HAVING u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : '')); if ($easy_to_link->total() > 0 || $sure) { return $easy_to_link; } return XDB::iterator(" - SELECT u.display_name, u.promo, u.user_id, ax.id_ancien, - CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax, + SELECT u.private_name, u.promo, u.pid, ax.ax_id, + CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X', ax.promotion_etude, ')') AS display_name_ax, COUNT(*) AS nbMatches FROM fusionax_anciens AS ax - INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL) - LEFT JOIN fusionax_xorg_anciens AS u ON (u.matricule_ax IS NULL - AND (ax.Nom_patronymique = u.nom - OR ax.Nom_patronymique LIKE CONCAT(u.nom, ' %') - OR ax.Nom_patronymique LIKE CONCAT(u.nom, '-%') - OR ax.Nom_usuel = u.nom - OR u.nom LIKE CONCAT('% ', ax.Nom_patronymique)) - AND u.promo < ax.promotion_etude + 2 - AND u.promo > ax.promotion_etude - 2) - GROUP BY u.user_id - HAVING u.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : '')); + INNER JOIN fusionax_import AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL) + LEFT JOIN fusionax_xorg_anciens AS u ON (u.ax_id IS NULL + AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name + OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name + OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name) + AND u.promo < CONCAT('X', ax.promotion_etude + 2) + AND u.promo > CONCAT('X', ax.promotion_etude - 2)) + GROUP BY u.pid + HAVING u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : '')); } /** Module de mise en correspondance les ids */ - function handler_ids(&$page, $part = 'main', $user_id = null, $matricule_ax = null) + function handler_ids(&$page, $part = 'main', $pid = null, $ax_id = null) { - $globals = Platal::globals(); $nbToLink = 100; - $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple'); + if ($part == 'missingInAX') { // locate all persons from this database that are not in AX's $page->changeTpl('fusionax/idsMissingInAx.tpl'); - $missingInAX = XDB::iterator("SELECT promo, user_id, display_name + $missingInAX = XDB::iterator('SELECT promo, pid, private_name FROM fusionax_xorg_anciens - WHERE matricule_ax IS NULL"); + WHERE ax_id IS NULL'); $page->assign('missingInAX', $missingInAX); return; } if ($part == 'missingInXorg') { // locate all persons from AX's database that are not here $page->changeTpl('fusionax/idsMissingInXorg.tpl'); - $missingInXorg = XDB::iterator("SELECT a.promotion_etude AS promo, - CONCAT(a.prenom, ' ', a.Nom_usuel) AS display_name, - a.id_ancien + $missingInXorg = XDB::iterator("SELECT CONCAT(a.prenom, ' ', a.Nom_usuel) AS private_name, + a.promotion_etude AS promo, a.ax_id FROM fusionax_import - INNER JOIN fusionax_anciens AS a USING (id_ancien) - WHERE fusionax_import.user_id IS NULL"); + INNER JOIN fusionax_anciens AS a USING (ax_id) + WHERE fusionax_import.pid IS NULL"); $page->assign('missingInXorg', $missingInXorg); return; } @@ -309,43 +362,43 @@ class FusionAxModule extends PLModule if ($part == 'cleanwronginxorg') { $linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink); while ($l = $linksToDo->next()) { - FusionAxModule::clear_wrong_in_xorg($l['user_id']); + FusionAxModule::clear_wrong_in_xorg($l['pid']); } pl_redirect('fusionax/ids/wrongInXorg'); } if ($part == 'lier') { if (Post::has('user_id') && Post::has('matricule_ax')) { - FusionAxModule::link_by_ids(Post::i('user_id'), Post::v('matricule_ax')); + FusionAxModule::link_by_ids(Post::i('pid'), Post::v('ax_id')); } } if ($part == 'link') { - FusionAxModule::link_by_ids($user_id, $matricule_ax); + FusionAxModule::link_by_ids($pid, $ax_id); exit; } if ($part == 'linknext') { $linksToDo = FusionAxModule::find_easy_to_link($nbToLink); while ($l = $linksToDo->next()) { - FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']); + FusionAxModule::link_by_ids($l['pid'], $l['ax_id']); } pl_redirect('fusionax/ids#autolink'); } if ($part == 'linkall') { $linksToDo = FusionAxModule::find_easy_to_link(0); while ($l = $linksToDo->next()) { - FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']); + FusionAxModule::link_by_ids($l['pid'], $l['ax_id']); } } { $page->changeTpl('fusionax/ids.tpl'); $missingInAX = XDB::query('SELECT COUNT(*) - FROM fusionax_xorg_anciens AS u - WHERE u.matricule_ax IS NULL'); + FROM fusionax_xorg_anciens + WHERE ax_id IS NULL'); if ($missingInAX) { $page->assign('nbMissingInAX', $missingInAX->fetchOneCell()); } $missingInXorg = XDB::query('SELECT COUNT(*) - FROM fusionax_import AS i - WHERE i.user_id IS NULL'); + FROM fusionax_import + WHERE pid IS NULL'); if ($missingInXorg) { $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell()); } @@ -374,36 +427,32 @@ class FusionAxModule extends PLModule WHERE deces_ax = "0000-00-00"'); } if ($action == 'update') { - if (Post::has('user_id') && Post::has('date')) { + if (Post::has('pid') && Post::has('date')) { XDB::execute('UPDATE fusionax_deceased SET deces_ax = {?}, deces_xorg = {?} - WHERE user_id = {?}', - Post::v('date'), Post::v('date'), Post::i('user_id')); + WHERE pid = {?}', + Post::v('date'), Post::v('date'), Post::i('pid')); } } $page->changeTpl('fusionax/deceased.tpl'); // deceased $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased'); $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell()); - $res = XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_ax, - CONCAT(d.prenom, " ", d.nom) AS display_name - FROM fusionax_deceased AS d - WHERE d.deces_xorg = "0000-00-00" + $res = XDB::iterator('SELECT pid, ax_id, promo, private_name, deces_ax + FROM fusionax_deceased + WHERE deces_xorg = "0000-00-00" LIMIT 10'); $page->assign('nbDeceasedMissingInXorg', $res->total()); $page->assign('deceasedMissingInXorg', $res); - $res = XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_xorg, - CONCAT(d.prenom, " ", d.nom) AS display_name - FROM fusionax_deceased AS d - WHERE d.deces_ax = "0000-00-00" + $res = XDB::iterator('SELECT pid, ax_id, promo, private_name, deces_xorg + FROM fusionax_deceased + WHERE deces_ax = "0000-00-00" LIMIT 10'); $page->assign('nbDeceasedMissingInAX', $res->total()); $page->assign('deceasedMissingInAX', $res); - $res = XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, - d.deces_ax, d.deces_xorg, - CONCAT(d.prenom, " ", d.nom, " ", d.user_id) AS display_name - FROM fusionax_deceased AS d - WHERE d.deces_xorg != "0000-00-00" AND d.deces_ax != "0000-00-00"'); + $res = XDB::iterator('SELECT pid, ax_id, promo, private_name, deces_xorg, deces_ax + FROM fusionax_deceased + WHERE deces_xorg != "0000-00-00" AND deces_ax != "0000-00-00"'); $page->assign('nbDeceasedDifferent', $res->total()); $page->assign('deceasedDifferent', $res); } @@ -411,13 +460,13 @@ class FusionAxModule extends PLModule function handler_promo(&$page, $action = '') { $page->changeTpl('fusionax/promo.tpl'); - $res = XDB::iterator('SELECT user_id, display_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax + $res = XDB::iterator('SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo FROM fusionax_promo WHERE !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg)'); $nbMissmatchingPromos = $res->total(); $page->assign('nbMissmatchingPromos1', $res->total()); $page->assign('missmatchingPromos1', $res); - $res = XDB::iterator('SELECT user_id, display_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax + $res = XDB::iterator('SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo FROM fusionax_promo WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg'); $nbMissmatchingPromos += $res->total(); diff --git a/modules/fusionax/Activites.sql b/modules/fusionax/Activites.sql index 33ddbdc..a09de5b 100644 --- a/modules/fusionax/Activites.sql +++ b/modules/fusionax/Activites.sql @@ -7,14 +7,14 @@ CREATE TABLE IF NOT EXISTS `fusionax_activites` ( `id_ancien` VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien', `hash_activite` BINARY(5) NOT NULL COMMENT 'Hash de cette ligne pour la lier à l''adresse', `Code_etab` BIGINT(10) NOT NULL COMMENT 'Code de l''établissement', - `Raison_sociale` VARCHAR(255) collate utf8_general_ci NOT NULL COMMENT 'Raison sociale de l''établissement', - `Libelle_fonctio` VARCHAR(255) collate utf8_general_ci NOT NULL COMMENT 'Libéllé de la fonction', + `Raison_sociale` VARCHAR(255) NOT NULL COMMENT 'Raison sociale de l''établissement', + `Libelle_fonctio` VARCHAR(255) NOT NULL COMMENT 'Libéllé de la fonction', `Annuaire` BOOLEAN NOT NULL COMMENT 'publiable dans l''annuaire papier', `Date_maj` DATE NOT NULL COMMENT 'Date de mise à jour de ces informations', PRIMARY KEY( `id_ancien` , `hash_activite` ) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -LOAD DATA LOCAL INFILE 'Activites.txt' INTO TABLE `fusionax_activites` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' +LOAD DATA LOCAL INFILE '{?}Activites.txt' INTO TABLE `fusionax_activites` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (AC, id_ancien, Code_etab, Raison_sociale, Libelle_fonctio, Annuaire, @Ligne1, @Ligne2, @Ligne3, @code_postal, @ville, @zip_cedex, @etat_distr, @pays, @tel, @fax, @StringDate_maj) SET diff --git a/modules/fusionax/Adresses.sql b/modules/fusionax/Adresses.sql index 172e071..f7d31a8 100644 --- a/modules/fusionax/Adresses.sql +++ b/modules/fusionax/Adresses.sql @@ -7,28 +7,28 @@ CREATE TABLE IF NOT EXISTS fusionax_adresses ( id_ancien VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien', Type_adr ENUM('E', 'P') character set binary NOT NULL DEFAULT 'P' COMMENT 'Type d''adresse : E pour Entreprise, P pour Personnelle', hash_adresse BINARY(5) NOT NULL COMMENT 'Hash pour différencier les diverses adresses', - Ligne1 VARCHAR(90) character set utf8 NOT NULL, - Ligne2 VARCHAR(90) character set utf8 NOT NULL, - Ligne3 VARCHAR(90) character set utf8 NOT NULL, - code_postal VARCHAR(20) character set utf8 NOT NULL, - ville VARCHAR(80) character set utf8 NOT NULL, - zip_cedex VARCHAR(20) character set utf8 NOT NULL, - etat_distr VARCHAR(20) character set utf8 NOT NULL, - pays VARCHAR(50) character set utf8 NOT NULL, + Ligne1 VARCHAR(90) NOT NULL, + Ligne2 VARCHAR(90) NOT NULL, + Ligne3 VARCHAR(90) NOT NULL, + code_postal VARCHAR(20) NOT NULL, + ville VARCHAR(80) NOT NULL, + zip_cedex VARCHAR(20) NOT NULL, + etat_distr VARCHAR(20) NOT NULL, + pays VARCHAR(50) NOT NULL, tel VARCHAR(30) NOT NULL, fax VARCHAR(30) NOT NULL, Date_maj DATE NOT NULL, PRIMARY KEY (id_ancien, hash_adresse) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; +) ENGINE=InnoDB, CHARSET=utf8; -LOAD DATA LOCAL INFILE 'Adresses.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' +LOAD DATA LOCAL INFILE '{?}Adresses.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (provenance, id_ancien, @Type_adr, Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj) SET `Type_adr` = IF(@Type_adr = 'E', 'E', IF(@Type_adr = '', '', 'P')), `hash_adresse` = SUBSTRING( MD5( @Type_adr ), 1, 5), `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); -LOAD DATA LOCAL INFILE 'Anciens.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' +LOAD DATA LOCAL INFILE '{?}Anciens.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (provenance, id_ancien, @Login, @Password, @promotion_etude, @Groupe_promo, @Nom_patronymique, @partic_patro, @prenom, @Nom_usuel, @partic_nom, @Nom_complet, @Civilite, @Code_nationalite, @Type_membre, @corps_sortie, @StringDate_deces, @grade, @Mel_usage, @Mel_publiable, @tel_mobile, @annee_dernCot, @Representant, @Type_adr, Ligne1, Ligne2, Ligne3, code_postal, ville, @@ -38,7 +38,7 @@ SET `hash_adresse` = SUBSTRING( MD5( @Type_adr ), 1, 5), `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); -LOAD DATA LOCAL INFILE 'Activites.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' +LOAD DATA LOCAL INFILE '{?}Activites.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (provenance, id_ancien, @Code_etab, @Raison_sociale, @Libelle_fonctio, @Annuaire, Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj) SET diff --git a/modules/fusionax/Anciens.sql b/modules/fusionax/Anciens.sql index 7964f43..54eda32 100644 --- a/modules/fusionax/Anciens.sql +++ b/modules/fusionax/Anciens.sql @@ -4,21 +4,21 @@ DROP TABLE IF EXISTS fusionax_anciens; CREATE TABLE IF NOT EXISTS fusionax_anciens ( AN CHAR(2) NOT NULL COMMENT 'Vaut toujours AN pour cette table', - id_ancien VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien', + ax_id VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien', Login VARCHAR(15) NOT NULL COMMENT 'Login sur le site de l''AX', Password INT(11) NOT NULL COMMENT 'Mot de passe sur le site AX', promotion_etude SMALLINT(4) NOT NULL COMMENT 'Promotion avec laquelle il/elle a fait ses études', Groupe_promo ENUM('', 'A', 'B', 'C', 'N', 'S') character set binary NOT NULL COMMENT 'Groupe de promotion (code compris seulement par AX)', - Nom_patronymique VARCHAR(255) character set utf8 NOT NULL COMMENT 'Nom patronymique (nom de jeune fille) sans la particule', - partic_patro VARCHAR(5) character set utf8 NOT NULL COMMENT 'Particule du nom patronymique', - prenom VARCHAR(30) character set utf8 NOT NULL COMMENT 'Prénom', - Nom_usuel VARCHAR(255) character set utf8 NOT NULL COMMENT 'Nom usuel (nom marital par exemple) sans la particule', - partic_nom VARCHAR(5) character set utf8 NOT NULL COMMENT 'Particule du nom usuel', - Nom_complet VARCHAR(255) character set utf8 NOT NULL COMMENT 'Nom patronymique complet (avec la particule)', - Civilite ENUM('', '.', 'M', 'MME', 'MLLE') character set utf8 NOT NULL COMMENT 'Civilité', + Nom_patronymique VARCHAR(255) NOT NULL COMMENT 'Nom patronymique (nom de jeune fille) sans la particule', + partic_patro VARCHAR(5) NOT NULL COMMENT 'Particule du nom patronymique', + prenom VARCHAR(30) NOT NULL COMMENT 'Prénom', + Nom_usuel VARCHAR(255) NOT NULL COMMENT 'Nom usuel (nom marital par exemple) sans la particule', + partic_nom VARCHAR(5) NOT NULL COMMENT 'Particule du nom usuel', + Nom_complet VARCHAR(255) NOT NULL COMMENT 'Nom patronymique complet (avec la particule)', + Civilite ENUM('', '.', 'M', 'MME', 'MLLE') NOT NULL COMMENT 'Civilité', Code_nationalite CHAR(4) NOT NULL COMMENT 'Nationalité (code)', Type_membre ENUM('', '*', 'F', 'FB', 'P', 'PB', 'T', 'TB', 'TA') character set binary NOT NULL COMMENT 'Type de membre (code compris seulement par AX)', - corps_sortie VARCHAR(50) character set utf8 NOT NULL COMMENT 'Corps de sortie (ou D si aucun)', + corps_sortie VARCHAR(50) NOT NULL COMMENT 'Corps de sortie (ou D si aucun)', Date_deces DATE COMMENT 'Date de décès', grade VARCHAR(50) NOT NULL COMMENT 'Grade actuel dans son corps', Mel_usage VARCHAR(255) NOT NULL COMMENT 'Adresse e-mail d''usage', @@ -28,11 +28,11 @@ CREATE TABLE IF NOT EXISTS fusionax_anciens ( Representant ENUM('', 'K', 'DE') character set binary NOT NULL COMMENT 'Représentant de promotion', hash_adr_defaut BINARY(5) NOT NULL COMMENT 'Hash de l''adresse par défaut', Date_maj DATE NOT NULL, - PRIMARY KEY (id_ancien) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; + PRIMARY KEY (ax_id) +) ENGINE=InnoDB, CHARSET=utf8; -LOAD DATA LOCAL INFILE 'Anciens.txt' INTO TABLE `fusionax_anciens` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' -(`AN`, `id_ancien`, `Login`, `Password`, `promotion_etude`, `Groupe_promo`, `Nom_patronymique`, `partic_patro`, `prenom`, Nom_usuel, partic_nom, +LOAD DATA LOCAL INFILE '{?}Anciens.txt' INTO TABLE `fusionax_anciens` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' +(`AN`, `ax_id`, `Login`, `Password`, `promotion_etude`, `Groupe_promo`, `Nom_patronymique`, `partic_patro`, `prenom`, Nom_usuel, partic_nom, Nom_complet, Civilite, Code_nationalite, Type_membre, corps_sortie, @StringDate_deces, grade, Mel_usage, Mel_publiable, tel_mobile, annee_dernCot, Representant, @Type_adr_defaut, @AdrC_Ligne1, @AdrC_Ligne2, @AdrC_Ligne3, @AdrC_code_postal, @AdrC_ville, @AdrC_zip_cedex, @AdrC_etat_distr, @AdrC_pays, @tel, @fax, @StringDate_maj) @@ -41,18 +41,45 @@ SET `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)), `Date_deces` = CONCAT(SUBSTRING(@StringDate_deces,7),'-',SUBSTRING(@StringDate_deces,4,2),'-',SUBSTRING(@StringDate_deces,1,2)); -ALTER TABLE fusionax_anciens ADD INDEX (id_ancien); +ALTER TABLE fusionax_anciens ADD INDEX (ax_id); -- Correspondances entre fiches X.org et fiches AX DROP TABLE IF EXISTS `fusionax_import`; CREATE TABLE IF NOT EXISTS `fusionax_import` ( - `id_ancien` VARCHAR(8) NOT NULL COMMENT 'identifiant AX de l''ancien', - `user_id` INT(11) DEFAULT NULL COMMENT 'identifiant x.org de l''ancien si on l''a trouvé', + `ax_id` VARCHAR(8) NOT NULL COMMENT 'identifiant AX de l''ancien', + `pid` INT(11) DEFAULT NULL COMMENT 'identifiant du profil x.org de l''ancien si on l''a trouvé', `date_match_id` TIMESTAMP NULL DEFAULT NULL COMMENT 'date de mise en correspondance des identifiants', - PRIMARY KEY (`id_ancien`), - KEY `user_id` (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; + PRIMARY KEY (`ax_id`), + KEY `pid` (`pid`) +) ENGINE=InnoDB, CHARSET=utf8; -INSERT INTO `fusionax_import` ( SELECT `id_ancien`, NULL, NULL FROM `fusionax_anciens` ); -REPLACE INTO `fusionax_import` ( SELECT `matricule_ax`, `user_id`, NOW() FROM `auth_user_md5` ); +INSERT INTO `fusionax_import` ( SELECT `ax_id`, NULL, NULL FROM `fusionax_anciens` ); +REPLACE INTO `fusionax_import` ( SELECT `ax_id`, `pid`, NOW() FROM `profiles` ); + +CREATE TEMPORARY TABLE IF NOT EXISTS tmp_update_fusionax_anciens ( + good CHAR(4) DEFAULT NULL, + bad CHAR(4) DEFAULT NULL, + PRIMARY KEY(bad), + UNIQUE KEY(good) +) CHARSET=utf8; + +INSERT IGNORE INTO tmp_update_fusionax_anciens (bad, good) + VALUES ('TC', 'CAM'), + ('SH', 'CN'), + ('R', 'RO'), + ('TW', 'RC'), + ('TG', 'RT'), + ('U', 'ROU'), + ('KP', 'ROK'), + ('CRO', 'HR'), + ('UKR', 'UA'), + ('AM', 'ARM'), + ('CS', 'CZ'), + ('SU', 'RUS'), + ('LET', 'LV'), + ('MDA', 'MD'); + + UPDATE fusionax_anciens AS f +INNER JOIN tmp_update_fusionax_anciens AS t ON (f.Code_nationalite = t.bad) + SET f.Code_nationalite = t.good; diff --git a/modules/fusionax/Entreprises.sql b/modules/fusionax/Entreprises.sql index a14ccd1..63de3bb 100644 --- a/modules/fusionax/Entreprises.sql +++ b/modules/fusionax/Entreprises.sql @@ -5,13 +5,13 @@ DROP TABLE IF EXISTS `fusionax_entreprises`; CREATE TABLE IF NOT EXISTS `fusionax_entreprises` ( `EN` CHAR(2) NOT NULL COMMENT 'Vaut toujours EN pour cette table', `Code_etab` BIGINT(10) NOT NULL COMMENT 'Code de l''établissement', - `Raison_sociale` VARCHAR(255) collate utf8_general_ci NOT NULL COMMENT 'Raison sociale de l''établissement', - `Sigle` VARCHAR(50) collate utf8_general_ci NOT NULL COMMENT 'Sigle de l''établissement', + `Raison_sociale` VARCHAR(255) NOT NULL COMMENT 'Raison sociale de l''établissement', + `Sigle` VARCHAR(50) NOT NULL COMMENT 'Sigle de l''établissement', `Date_maj` DATE NOT NULL COMMENT 'Date de mise à jour de ces informations', PRIMARY KEY(`Code_etab`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; +) ENGINE=InnoDB, CHARSET=utf8; -LOAD DATA LOCAL INFILE 'Entreprises.txt' INTO TABLE `fusionax_entreprises` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' +LOAD DATA LOCAL INFILE '{?}Entreprises.txt' INTO TABLE `fusionax_entreprises` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (EN, Code_etab, Raison_sociale, Sigle, @Inconnu, @StringDate_maj) SET `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); diff --git a/modules/fusionax/Formations.sql b/modules/fusionax/Formations.sql index c2b09f6..3906862 100644 --- a/modules/fusionax/Formations.sql +++ b/modules/fusionax/Formations.sql @@ -5,16 +5,16 @@ DROP TABLE IF EXISTS `fusionax_formations`; CREATE TABLE IF NOT EXISTS `fusionax_formations` ( FO CHAR(2) NOT NULL COMMENT 'Vaut toujours FO pour cette table', id_ancien VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien', - Intitule_diplome VARCHAR(60) collate utf8_general_ci NOT NULL COMMENT 'Intitulé du diplôme', - Intitule_formation VARCHAR(60) collate utf8_general_ci NOT NULL COMMENT 'Intitulé de la formation', - Descr_formation VARCHAR(60) collate utf8_general_ci NOT NULL COMMENT 'Description de la formation', - tmp_1 VARCHAR(60) collate utf8_general_ci NOT NULL, - tmp_2 VARCHAR(60) collate utf8_general_ci NOT NULL, - tmp_3 VARCHAR(60) collate utf8_general_ci NOT NULL, - tmp_4 VARCHAR(60) collate utf8_general_ci NOT NULL, + Intitule_diplome VARCHAR(60) NOT NULL COMMENT 'Intitulé du diplôme', + Intitule_formation VARCHAR(60) NOT NULL COMMENT 'Intitulé de la formation', + Descr_formation VARCHAR(60) NOT NULL COMMENT 'Description de la formation', + tmp_1 VARCHAR(60) NOT NULL, + tmp_2 VARCHAR(60) NOT NULL, + tmp_3 VARCHAR(60) NOT NULL, + tmp_4 VARCHAR(60) NOT NULL, PRIMARY KEY (id_ancien, Intitule_diplome, Intitule_formation) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; +) ENGINE=InnoDB, CHARSET=utf8; - LOAD DATA LOCAL INFILE 'Formations.txt' + LOAD DATA LOCAL INFILE '{?}Formations.txt' INTO TABLE fusionax_formations FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (FO, id_ancien, Intitule_diplome, Intitule_formation, Descr_formation, tmp_1, tmp_2, tmp_3, tmp_4); diff --git a/modules/fusionax/formation.pl b/modules/fusionax/formation.pl index 14b72dc..c6eb6a7 100755 --- a/modules/fusionax/formation.pl +++ b/modules/fusionax/formation.pl @@ -1,7 +1,7 @@ #!/usr/bin/perl use utf8; -open(FILE, "<:encoding(UTF-8)", "Formations.txt") || die ("Erreur d'ouverture"); -open(OUT, ">:encoding(UTF-8)", "Formations_out.txt") || die ("Erreur d'ouverture"); +open(FILE, "<:encoding(UTF-8)", "../../spool/fusionax/Formations.txt") || die ("Erreur d'ouverture"); +open(OUT, ">:encoding(UTF-8)", "../../spool/fusionax/Formations_out.txt") || die ("Erreur d'ouverture"); while () { diff --git a/modules/fusionax/import-ax.sh b/modules/fusionax/import-ax.sh deleted file mode 100755 index 999c16f..0000000 --- a/modules/fusionax/import-ax.sh +++ /dev/null @@ -1,32 +0,0 @@ -#! /bin/bash - -# import des données -#scp -i ax_xorg_rsa xorg@polytechniciens.com:/home/axasso/ax-import/export_4D.txt.rar . -#unrar e -inul export_4D.txt.rar -cp /home/x2004jacob/export*utf8.TXT . - -# séparation en fichiers de tables -cat export_total* | grep ^AD > Adresses.txt -cat export_total* | grep ^AN > Anciens.txt -cat export_total* | grep ^FO > Formations.txt -cat export_total* | grep ^AC > Activites.txt -cat export_total* | grep ^EN > Entreprises.txt - -exit 1 - -# intégration dans notre bdd -echo intégration dans notre bdd -$MYSQL x4dat < Activites.sql -$MYSQL x4dat < Adresses.sql -$MYSQL x4dat < Anciens.sql -$MYSQL x4dat < Entreprises.sql -./formation.pl -cat Formations_out.txt > Formations.txt -rm -f Formations_out.txt -$MYSQL x4dat < Formations.sql - - -# nettoyage -echo nettoyage -#rm Adresses.txt Anciens.txt Formations.txt Activites.txt Entreprises.txt export_4D.txt.rar export-total* -rm Adresses.txt Anciens.txt Formations.txt Activites.txt Entreprises.txt export-total* diff --git a/templates/fusionax/deceased.tpl b/templates/fusionax/deceased.tpl index 8a0aa9d..9fad56d 100644 --- a/templates/fusionax/deceased.tpl +++ b/templates/fusionax/deceased.tpl @@ -20,7 +20,7 @@ {* *} {**************************************************************************} -

Fusion des annuaires X.org - AX / Décès

+

Fusion des annuaires X.org - AX / Décès

{if $deceasedErrors}

Voici les {$deceasedErrors} différences entre les deux annuaires pour les renseignements de @@ -46,9 +46,11 @@ décès.

Mettre en correspondance

- User ID X.org :
- Date de décès :
- +

+ PID X.org :
+ Date de décès :
+ +

{/if} diff --git a/templates/fusionax/ids.tpl b/templates/fusionax/ids.tpl index a2bb9fd..a26788c 100644 --- a/templates/fusionax/ids.tpl +++ b/templates/fusionax/ids.tpl @@ -22,39 +22,40 @@

Fusion des annuaires X.org - AX / Identifiants

-

Le préalable à toute fusion de renseignements pour une personne entre ce +

+Le préalable à toute fusion de renseignements pour une personne entre ce que contient la base AX et ce que contient ce site est bien évidemment de -trouver une correspondance entre les personnes renseignés dans ces annuaires.

- -{if $nbMissingInAX} -

Anciens manquants à l'AX

+trouver une correspondance entre les personnes renseignés dans ces annuaires.

-

{$nbMissingInAX} ancien{if $nbMissingInAX > 1}s{/if}.

+{if t($nbMissingInAX)} +Anciens manquants à l'AX : +{$nbMissingInAX} ancien{if $nbMissingInAX > 1}s{/if}.
{/if} -{if $nbMissingInXorg > 0} -

Anciens manquants à x.org

- -

{$nbMissingInXorg} ancien{if $nbMissingInXorg > 1}s{/if}.

+{if t($nbMissingInXorg)} +Anciens manquants à x.org : +{$nbMissingInXorg} ancien{if $nbMissingInXorg > 1}s{/if}.
{/if} -{if $wrongInXorg > 0} -

Anciens ayant un matricule_ax sur Xorg ne correspondant à rien dans la base de l'AX

- -

{$wrongInXorg} ancien{if $wrongInXorg > 1}s{/if}.

+{if t($wrongInXorg)} +Anciens ayant un ax_id sur Xorg ne correspondant à rien dans la base de l'AX : +{$wrongInXorg} ancien{if $wrongInXorg > 1}s{/if}. {/if} +

Mettre en correspondance

- Matricule AX :
- User ID X.org :
- +

+ Matricule AX :
+ User ID X.org :
+ +

-