From b9ad087851ce4a09236f64f67d15fdaf29e38cf0 Mon Sep 17 00:00:00 2001 From: Pascal Corpet Date: Sat, 3 Nov 2007 13:26:46 +0100 Subject: [PATCH] Fusion AX : import --- htdocs/xorg.php | 2 +- modules/fusionax.php | 228 ++++++++++++++++++++++++++++++++ modules/fusionax/Activites.sql | 22 +++ modules/fusionax/Adresses.sql | 47 +++++++ modules/fusionax/Anciens.sql | 62 +++++++++ modules/fusionax/Entreprises.sql | 17 +++ modules/fusionax/Formations.sql | 16 +++ modules/fusionax/import-ax.sh | 26 ++++ templates/fusionax/ids.tpl | 56 ++++++++ templates/fusionax/idsMissingInAx.tpl | 29 ++++ templates/fusionax/idsMissingInXorg.tpl | 29 ++++ templates/fusionax/import.tpl | 50 +++++++ templates/fusionax/index.tpl | 35 +++++ templates/fusionax/listFusion.tpl | 57 ++++++++ templates/fusionax/misc.tpl | 48 +++++++ 15 files changed, 723 insertions(+), 1 deletion(-) create mode 100644 modules/fusionax.php create mode 100644 modules/fusionax/Activites.sql create mode 100644 modules/fusionax/Adresses.sql create mode 100644 modules/fusionax/Anciens.sql create mode 100644 modules/fusionax/Entreprises.sql create mode 100644 modules/fusionax/Formations.sql create mode 100755 modules/fusionax/import-ax.sh create mode 100644 templates/fusionax/ids.tpl create mode 100644 templates/fusionax/idsMissingInAx.tpl create mode 100644 templates/fusionax/idsMissingInXorg.tpl create mode 100644 templates/fusionax/import.tpl create mode 100644 templates/fusionax/index.tpl create mode 100644 templates/fusionax/listFusion.tpl create mode 100644 templates/fusionax/misc.tpl diff --git a/htdocs/xorg.php b/htdocs/xorg.php index c52959d..406c4a1 100644 --- a/htdocs/xorg.php +++ b/htdocs/xorg.php @@ -27,7 +27,7 @@ if (!($path = Env::v('n')) || ($path{0} < 'A' || $path{0} > 'Z')) { $platal = new Platal('auth', 'banana', 'carnet', 'email', 'events', 'geoloc', 'lists', 'marketing', 'payment', 'platal', 'profile', 'register', 'search', 'stats', 'admin', - 'newsletter', 'axletter', 'bandeau', 'survey'); + 'newsletter', 'axletter', 'bandeau', 'survey', 'fusionax'); $platal->run(); exit; diff --git a/modules/fusionax.php b/modules/fusionax.php new file mode 100644 index 0000000..e3d267a --- /dev/null +++ b/modules/fusionax.php @@ -0,0 +1,228 @@ + $this->make_hook('index', AUTH_MDP, 'admin'), + 'fusionax/import' => $this->make_hook('import', AUTH_MDP, 'admin'), + 'fusionax/ids' => $this->make_hook('ids', AUTH_MDP, 'admin'), + 'fusionax/misc' => $this->make_hook('misc', AUTH_MDP, 'admin'), + ); + } + + function handler_index(&$page) + { + global $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)); + } + } + + function handler_import(&$page, $action = 'index', $fileSQL = '') + { + if ($action == 'index') { + $page->changeTpl('fusionax/import.tpl'); + $page->addJsLink('jquery.js'); + global $globals; + if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) { + $page->assign('lastimport', "le ".date("d/m/Y à H:i",$globals->fusionax->LastUpdate)); + } + if (!file_exists(dirname(__FILE__).'/../configs/ax_xorg_rsa')) { + $page->assign('keymissing', realpath(dirname(__FILE__).'/../configs/').'/ax_xorg_rsa'); + } + return; + } + $report = array(); + header("Content-type: text/javascript; charset=utf-8"); + if (Env::has('tmpdir')) { + $tmpdir = Env::v('tmpdir'); + } else { + exec('rm /tmp/fusionax* -rf'); + $tmpdir = tempnam('/tmp', 'fusionax'); + unlink($tmpdir); + mkdir($tmpdir); + chmod($tmpdir, 0777); + if (!copy(dirname(__FILE__).'/../configs/ax_xorg_rsa',$tmpdir.'/ax_xorg_rsa')) + $report[] = 'Impossible de copier la clef pour se logger sur le serveur AX'; + chmod($tmpdir.'/ax_xorg_rsa', 0600); + } + $modulepath = realpath(dirname(__FILE__).'/fusionax/').'/'; + $olddir = getcwd(); + chdir($tmpdir); + if ($action == 'launch') { + exec($modulepath.'import-ax.sh', $report); + $report[] = utf8_decode('Récupération du fichier terminé.'); + $report[] = 'Import dans la base en cours...'; + $next = 'integrateSQL'; + } else if ($action == 'integrateSQL') { + $filesSQL = array('Activites.sql', 'Adresses.sql', 'Anciens.sql', 'Formations.sql', 'Entreprises.sql'); + if ($fileSQL != '') { + $trans = array_flip($filesSQL); + $nextfile = $trans[$fileSQL] + 1; + $queries = explode(';',file_get_contents($modulepath.$fileSQL)); + foreach ($queries as $q) if (trim($q)) { + if (substr($q,0,2) == '--') { + $lines = explode("\n",$q); + $l = $lines[0]; + $report[] = addslashes(utf8_decode($l)); + } + XDB::execute($q); + } + } else { + $nextfile = 0; + } + if (!isset($filesSQL[$nextfile])) { + $next = 'clean'; + } else { + $next = 'integrateSQL/'.$filesSQL[$nextfile]; + } + } else if ($action == 'clean') { + chdir($olddir); + exec("rm -rf $tmpdir", $report); + $report[] = 'Fin de l\'import'; + global $globals; + $globals->change_dynamic_config(array('LastUpdate' => time()), 'FusionAx'); + } + $tmpdir = getcwd(); + chdir($olddir); + foreach($report as $t) + echo "$('#fusionax_import').append('".utf8_encode($t)."
');\n"; + if (isset($next)) { + echo "$.getScript('fusionax/import/".$next."?tmpdir=".urlencode($tmpdir)."');"; + } + exit; + } + + private static function link_by_ids($user_id, $matricule_ax) + { + if (!XDB::execute("UPDATE fusionax_import AS i INNER JOIN auth_user_md5 AS u + SET u.matricule_ax = i.id_ancien, i.user_id = u.user_id, 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)) + { + return 0; + } + return XDB::affectedRows() / 2; + } + + private static function find_easy_to_link($limit = 10) + { + return XDB::iterator("SELECT + xorg.prenom, xorg.nom, xorg.promo, xorg.user_id, ax.id_ancien + FROM fusionax_anciens AS ax + INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL) + INNER JOIN auth_user_md5 AS xorg + WHERE + xorg.matricule_ax IS NULL AND + xorg.promo = ax.promotion_etude AND + xorg.prenom LIKE ax.prenom AND + xorg.nom LIKE ax.Nom_complet + ".($limit?('LIMIT '.$limit):'')); + } + + function handler_ids(&$page, $part = 'main') + { + global $globals; + $globals->change_dynamic_config(array('LastUpdate' => time()), 'FusionAX'); + + $page->assign('xorg_title','Polytechnique.org - Fusion des annuaires - 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 * + FROM auth_user_md5 AS u + LEFT JOIN aliases AS a ON(a.id = u.user_id AND FIND_IN_SET('bestalias', a.flags)) + WHERE u.matricule_ax IS NULL + LIMIT 20"); + $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 promotion_etude AS promo, prenom, Nom_usuel AS nom, id_ancien + FROM fusionax_import + INNER JOIN fusionax_anciens AS a USING (id_ancien) + WHERE fusionax_import.user_id IS NULL + LIMIT 20"); + $page->assign('missingInXorg', $missingInXorg); + return; + } + if ($part == 'link') + { + FusionAxModule::link_by_ids(Env::i('user_id'),Env::v('matricule_ax')); + } + if ($part == 'linknext') + { + $linksToDo = FusionAxModule::find_easy_to_link(10); + while ($l = $linksToDo->next()) + { + FusionAxModule::link_by_ids($l['user_id'],$l['id_ancien']); + } + } + if ($part == 'linkall') + { + $linksToDo = FusionAxModule::find_easy_to_link(0); + while ($l = $linksToDo->next()) + { + FusionAxModule::link_by_ids($l['user_id'],$l['id_ancien']); + } + } + { + $page->changeTpl('fusionax/ids.tpl'); + $missingInAX = XDB::query("SELECT COUNT(*) FROM auth_user_md5 WHERE matricule_ax IS NULL"); + if ($missingInAX) + { + $page->assign('nbMissingInAX', $missingInAX->fetchOneCell()); + } + $missingInXorg = XDB::query("SELECT COUNT(*) FROM fusionax_import WHERE user_id IS NULL"); + if ($missingInXorg) + { + $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell()); + } + $easyToLink = FusionAxModule::find_easy_to_link(10); + if ($easyToLink->total() > 0) + { + $page->assign('easyToLink', $easyToLink); + } + } + } + function handler_misc(&$page) + { + $page->changeTpl('fusionax/misc.tpl'); + // deceased + $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased'); + $page->assign('deceasedErrors',$deceasedErrorsSql->fetchOneCell()); + $page->assign('deceasedMissingInXorg',XDB::iterator('SELECT user_id,id_ancien,nom,prenom,promo,Date_décès FROM fusionax_deceased WHERE deces = "0000-00-00" LIMIT 10')); + $page->assign('deceasedMissingInAX',XDB::iterator('SELECT user_id,id_ancien,nom,prenom,promo,deces FROM fusionax_deceased WHERE Date_décès = "0000-00-00" LIMIT 10')); + $page->assign('deceasedDifferent',XDB::iterator('SELECT user_id,id_ancien,nom,prenom,promo,Date_décès,deces FROM fusionax_deceased WHERE deces != "0000-00-00" AND Date_décès != "0000-00-00" LIMIT 10')); + } +} +// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:?> diff --git a/modules/fusionax/Activites.sql b/modules/fusionax/Activites.sql new file mode 100644 index 0000000..7db895a --- /dev/null +++ b/modules/fusionax/Activites.sql @@ -0,0 +1,22 @@ +-- Import complet des activités professionnelles + +DROP TABLE IF EXISTS `fusionax_activites`; + +CREATE TABLE IF NOT EXISTS `fusionax_activites` ( + `AC` varbinary(2) NOT NULL COMMENT 'Vaut toujours AC pour cette table', + `id_ancien` varbinary(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` varbinary(10) NOT NULL COMMENT 'Code de l''établissement', + `Raison_sociale` varchar(100) collate utf8_unicode_ci NOT NULL COMMENT 'Raison sociale de l''établissement', + `Libelle_fonctio` varchar(100) collate utf8_unicode_ci 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_unicode_ci; + +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 +`hash_activite` = SUBSTRING( MD5( CONCAT(Code_etab, Libelle_fonctio) ), 1, 5), +`Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); diff --git a/modules/fusionax/Adresses.sql b/modules/fusionax/Adresses.sql new file mode 100644 index 0000000..1b9ed47 --- /dev/null +++ b/modules/fusionax/Adresses.sql @@ -0,0 +1,47 @@ +-- Import complet des adresses + +DROP TABLE IF EXISTS `fusionax_adresses`; + +CREATE TABLE IF NOT EXISTS `fusionax_adresses` ( + `provenance` varbinary(2) NOT NULL COMMENT 'Vaut AC, AD ou AN selon la provenance de l''info', + `id_ancien` varbinary(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(60) character set utf8 NOT NULL, + `Ligne2` varchar(60) character set utf8 NOT NULL, + `Ligne3` varchar(60) character set utf8 NOT NULL, + `code_postal` varchar(20) character set utf8 NOT NULL, + `ville` varchar(40) 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, + `tel` varbinary(30) NOT NULL, + `fax` varbinary(30) NOT NULL, + `Date_maj` date NOT NULL, + PRIMARY KEY (`id_ancien`, `hash_adresse`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +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' +(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, + 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 '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 +`Type_adr` = 'E', +`hash_adresse` = SUBSTRING( MD5( CONCAT(@Code_etab, @Libelle_fonctio) ), 1, 5), +`Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); diff --git a/modules/fusionax/Anciens.sql b/modules/fusionax/Anciens.sql new file mode 100644 index 0000000..523f030 --- /dev/null +++ b/modules/fusionax/Anciens.sql @@ -0,0 +1,62 @@ +-- Import complet des anciens + +DROP TABLE IF EXISTS `fusionax_anciens`; + +CREATE TABLE IF NOT EXISTS `fusionax_anciens` ( + `AN` varbinary(2) NOT NULL COMMENT 'Vaut toujours AN pour cette table', + `id_ancien` varbinary(8) NOT NULL COMMENT 'Id unique de l''ancien', + `Login` varbinary(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` int(11) 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(50) 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(50) 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(80) character set utf8 NOT NULL COMMENT 'Nom patronymique complet (avec la particule)', + `Civilite` enum('','.','M','MME','MLLE') character set utf8 NOT NULL COMMENT 'Civilité', + `Code_nationalite` varbinary(2) 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(10) character set utf8 NOT NULL COMMENT 'Corps de sortie (ou D si aucun)', + `Date_deces` DATE COMMENT 'Date de décès', + `grade` varbinary(20) NOT NULL COMMENT 'Grade actuel dans son corps', + `Mel_usage` varbinary(150) NOT NULL COMMENT 'Adresse e-mail d''usage', + `Mel_publiable` tinyint(4) NOT NULL COMMENT 'Autorisation d''utiliser le mail', + `tel_mobile` varbinary(30) NOT NULL COMMENT 'Numéro de téléphone mobile', + `annee_dernCot` int(11) NOT NULL COMMENT 'Année de dernière cotisation AX', + `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_unicode_ci; + +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, + 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) +SET + `hash_adr_defaut` = SUBSTRING( MD5( @Type_adr_defaut ), 1, 5), + `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)); + +DROP TABLE IF EXISTS `fusionax_import`; + +CREATE TABLE IF NOT EXISTS `fusionax_import` ( + `id_ancien` binary(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é', + `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_unicode_ci; + +INSERT INTO `fusionax_import` ( SELECT `id_ancien`, NULL, NULL FROM `fusionax_anciens` ); + +DROP VIEW IF EXISTS `fusionax_deceased`; + +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; diff --git a/modules/fusionax/Entreprises.sql b/modules/fusionax/Entreprises.sql new file mode 100644 index 0000000..651d24b --- /dev/null +++ b/modules/fusionax/Entreprises.sql @@ -0,0 +1,17 @@ +-- Import complet des entreprises + +DROP TABLE IF EXISTS `fusionax_entreprises`; + +CREATE TABLE IF NOT EXISTS `fusionax_entreprises` ( + `EN` varbinary(2) NOT NULL COMMENT 'Vaut toujours EN pour cette table', + `Code_etab` varbinary(10) NOT NULL COMMENT 'Code de l''établissement', + `Raison_sociale` varchar(100) collate utf8_unicode_ci NOT NULL COMMENT 'Raison sociale de l''établissement', + `Sigle` varchar(50) collate utf8_unicode_ci 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_unicode_ci; + +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 new file mode 100644 index 0000000..7d2854a --- /dev/null +++ b/modules/fusionax/Formations.sql @@ -0,0 +1,16 @@ +-- Import complet des formations + +DROP TABLE IF EXISTS `fusionax_formations`; + +CREATE TABLE IF NOT EXISTS `fusionax_formations` ( + `FO` varbinary(2) NOT NULL COMMENT 'Vaut toujours FO pour cette table', + `id_ancien` varbinary(8) NOT NULL COMMENT 'Id unique de l''ancien', + `Intitule_formation` varchar(60) collate utf8_unicode_ci NOT NULL COMMENT 'Intitulé de la formation', + `Date_maj` DATE NOT NULL COMMENT 'Date de mise à jour de ces informations', + PRIMARY KEY (`id_ancien`, `Intitule_formation`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +LOAD DATA LOCAL INFILE 'Formations.txt' INTO TABLE `fusionax_formations` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' +(FO, id_ancien, Intitule_formation, @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/import-ax.sh b/modules/fusionax/import-ax.sh new file mode 100755 index 0000000..35b4b6a --- /dev/null +++ b/modules/fusionax/import-ax.sh @@ -0,0 +1,26 @@ +#! /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 + +# 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 < Formations.sql +$MYSQL x4dat < Entreprises.sql + +# nettoyage +echo nettoyage +rm Adresses.txt Anciens.txt Formations.txt Activites.txt Entreprises.txt export_4D.txt.rar export-total* diff --git a/templates/fusionax/ids.tpl b/templates/fusionax/ids.tpl new file mode 100644 index 0000000..7df18a7 --- /dev/null +++ b/templates/fusionax/ids.tpl @@ -0,0 +1,56 @@ +{**************************************************************************} +{* *} +{* Copyright (C) 2003-2007 Polytechnique.org *} +{* http://opensource.polytechnique.org/ *} +{* *} +{* This program is free software; you can redistribute it and/or modify *} +{* it under the terms of the GNU General Public License as published by *} +{* the Free Software Foundation; either version 2 of the License, or *} +{* (at your option) any later version. *} +{* *} +{* This program is distributed in the hope that it will be useful, *} +{* but WITHOUT ANY WARRANTY; without even the implied warranty of *} +{* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *} +{* GNU General Public License for more details. *} +{* *} +{* You should have received a copy of the GNU General Public License *} +{* along with this program; if not, write to the Free Software *} +{* Foundation, Inc., *} +{* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *} +{* *} +{**************************************************************************} + +

Fusion des annuaires X.org - AX / Identifiants

+ +

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

+ +

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

+{/if} + +{if $nbMissingInXorg > 0} +

Anciens manquants à x.org

+ +

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

+{/if} + +

Mettre en correspondance

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

+ +{if $easyToLink} +

Ces anciens sont probablement les mêmes (mêmes nom, prénom, promo)

+{include file="fusionax/listFusion.tpl" fusionList=$easyToLink fusionAction="fusionax/ids/link" name="lier"} +

Lier toutes les fiches affichées

+{else} +

Aucune correspondance automatique n'a été trouvée (mêmes nom, prénom, promo d'étude).

+{/if} diff --git a/templates/fusionax/idsMissingInAx.tpl b/templates/fusionax/idsMissingInAx.tpl new file mode 100644 index 0000000..8d5ba8c --- /dev/null +++ b/templates/fusionax/idsMissingInAx.tpl @@ -0,0 +1,29 @@ +{**************************************************************************} +{* *} +{* Copyright (C) 2003-2007 Polytechnique.org *} +{* http://opensource.polytechnique.org/ *} +{* *} +{* This program is free software; you can redistribute it and/or modify *} +{* it under the terms of the GNU General Public License as published by *} +{* the Free Software Foundation; either version 2 of the License, or *} +{* (at your option) any later version. *} +{* *} +{* This program is distributed in the hope that it will be useful, *} +{* but WITHOUT ANY WARRANTY; without even the implied warranty of *} +{* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *} +{* GNU General Public License for more details. *} +{* *} +{* You should have received a copy of the GNU General Public License *} +{* along with this program; if not, write to the Free Software *} +{* Foundation, Inc., *} +{* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *} +{* *} +{**************************************************************************} + +

Fusion des annuaires X.org - AX / Identifiants / Manquants dans l'annuaire de l'AX

+ +

+ +{if $missingInAX} +{include file='fusionax/listFusion.tpl' fusionList=$missingInAX field1='user_id' namefield1='ID X.org'} +{/if} diff --git a/templates/fusionax/idsMissingInXorg.tpl b/templates/fusionax/idsMissingInXorg.tpl new file mode 100644 index 0000000..036d6d4 --- /dev/null +++ b/templates/fusionax/idsMissingInXorg.tpl @@ -0,0 +1,29 @@ +{**************************************************************************} +{* *} +{* Copyright (C) 2003-2007 Polytechnique.org *} +{* http://opensource.polytechnique.org/ *} +{* *} +{* This program is free software; you can redistribute it and/or modify *} +{* it under the terms of the GNU General Public License as published by *} +{* the Free Software Foundation; either version 2 of the License, or *} +{* (at your option) any later version. *} +{* *} +{* This program is distributed in the hope that it will be useful, *} +{* but WITHOUT ANY WARRANTY; without even the implied warranty of *} +{* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *} +{* GNU General Public License for more details. *} +{* *} +{* You should have received a copy of the GNU General Public License *} +{* along with this program; if not, write to the Free Software *} +{* Foundation, Inc., *} +{* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *} +{* *} +{**************************************************************************} + +

Fusion des annuaires X.org - AX / Identifiants / Manquants dans l'annuaire d'X.org

+ +

+ +{if $missingInXorg} +{include file='fusionax/listFusion.tpl' fusionList=$missingInXorg field1='id_ancien' namefield1='matricule AX'} +{/if} diff --git a/templates/fusionax/import.tpl b/templates/fusionax/import.tpl new file mode 100644 index 0000000..f719dde --- /dev/null +++ b/templates/fusionax/import.tpl @@ -0,0 +1,50 @@ +{**************************************************************************} +{* *} +{* Copyright (C) 2003-2007 Polytechnique.org *} +{* http://opensource.polytechnique.org/ *} +{* *} +{* This program is free software; you can redistribute it and/or modify *} +{* it under the terms of the GNU General Public License as published by *} +{* the Free Software Foundation; either version 2 of the License, or *} +{* (at your option) any later version. *} +{* *} +{* This program is distributed in the hope that it will be useful, *} +{* but WITHOUT ANY WARRANTY; without even the implied warranty of *} +{* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *} +{* GNU General Public License for more details. *} +{* *} +{* You should have received a copy of the GNU General Public License *} +{* along with this program; if not, write to the Free Software *} +{* Foundation, Inc., *} +{* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *} +{* *} +{**************************************************************************} + + +

Fusion des annuaires X.org - AX

+ +

Import de l'annuaire AX

+{if $lastimport} +

Dernier import {$lastimport}

+{/if} + +{if $keymissing} +

Impossible de faire l'import, il manque la clef d'authentification :

+
{$keymissing}
+{else} +
+ +
+{/if} diff --git a/templates/fusionax/index.tpl b/templates/fusionax/index.tpl new file mode 100644 index 0000000..ec652b4 --- /dev/null +++ b/templates/fusionax/index.tpl @@ -0,0 +1,35 @@ +{**************************************************************************} +{* *} +{* Copyright (C) 2003-2007 Polytechnique.org *} +{* http://opensource.polytechnique.org/ *} +{* *} +{* This program is free software; you can redistribute it and/or modify *} +{* it under the terms of the GNU General Public License as published by *} +{* the Free Software Foundation; either version 2 of the License, or *} +{* (at your option) any later version. *} +{* *} +{* This program is distributed in the hope that it will be useful, *} +{* but WITHOUT ANY WARRANTY; without even the implied warranty of *} +{* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *} +{* GNU General Public License for more details. *} +{* *} +{* You should have received a copy of the GNU General Public License *} +{* along with this program; if not, write to the Free Software *} +{* Foundation, Inc., *} +{* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *} +{* *} +{**************************************************************************} + +

Fusion des annuaires X.org - AX

+ + +{* vim:set et sw=2 sts=2 sws=2 enc=utf-8: *} diff --git a/templates/fusionax/listFusion.tpl b/templates/fusionax/listFusion.tpl new file mode 100644 index 0000000..b922cdc --- /dev/null +++ b/templates/fusionax/listFusion.tpl @@ -0,0 +1,57 @@ +{**************************************************************************} +{* *} +{* Copyright (C) 2003-2007 Polytechnique.org *} +{* http://opensource.polytechnique.org/ *} +{* *} +{* This program is free software; you can redistribute it and/or modify *} +{* it under the terms of the GNU General Public License as published by *} +{* the Free Software Foundation; either version 2 of the License, or *} +{* (at your option) any later version. *} +{* *} +{* This program is distributed in the hope that it will be useful, *} +{* but WITHOUT ANY WARRANTY; without even the implied warranty of *} +{* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *} +{* GNU General Public License for more details. *} +{* *} +{* You should have received a copy of the GNU General Public License *} +{* along with this program; if not, write to the Free Software *} +{* Foundation, Inc., *} +{* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *} +{* *} +{**************************************************************************} + + + + + + {if $field1} + + {/if} + {if $field2} + + {/if} + {if $fusionAction} + + {/if} + +{if $fusionList} +{iterate from=$fusionList item=c} + + + + {if $field1} + + {/if} + {if $field2} + + {/if} + {if $fusionAction} + + {/if} + +{/iterate} +{/if} +
AncienFiches{$namefield1}{$namefield2}Action
{$c.prenom} {$c.nom} (X {$c.promo}) + {if $c.user_id}{icon name="user_suit" title="Administrer utilisateur"}{/if} + {if $c.id_ancien}{icon name="user_gray" title="fiche AX"}{/if} + {$c.$field1}{$c.$field2}{$name}
diff --git a/templates/fusionax/misc.tpl b/templates/fusionax/misc.tpl new file mode 100644 index 0000000..f2ff9c2 --- /dev/null +++ b/templates/fusionax/misc.tpl @@ -0,0 +1,48 @@ +{**************************************************************************} +{* *} +{* Copyright (C) 2003-2007 Polytechnique.org *} +{* http://opensource.polytechnique.org/ *} +{* *} +{* This program is free software; you can redistribute it and/or modify *} +{* it under the terms of the GNU General Public License as published by *} +{* the Free Software Foundation; either version 2 of the License, or *} +{* (at your option) any later version. *} +{* *} +{* This program is distributed in the hope that it will be useful, *} +{* but WITHOUT ANY WARRANTY; without even the implied warranty of *} +{* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *} +{* GNU General Public License for more details. *} +{* *} +{* You should have received a copy of the GNU General Public License *} +{* along with this program; if not, write to the Free Software *} +{* Foundation, Inc., *} +{* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *} +{* *} +{**************************************************************************} + +

Fusion des annuaires X.org - AX / Divers

+ +

Décès

+ +{if $deceasedErrors} + +

Voici les {$deceasedErrors} différences entre les deux annuaires pour les renseignements de +décès.

+ +

Anciens déclarés décédés dans l'annuaire AX mais pas sur Xorg

+ +{include file='fusionax/listFusion.tpl' fusionList=$deceasedMissingInXorg field1='Date_décès' namefield1='Décès AX'} + +

Anciens déclarés décédés dans l'annuaire Xorg mais pas chez l'AX

+ +{include file='fusionax/listFusion.tpl' fusionList=$deceasedMissingInAX field1='deces' namefield1='Décès X.org'} + +

Anciens déclarés décédés dans les deux annuaires mais pas avec la même date

+ +{include file='fusionax/listFusion.tpl' fusionList=$deceasedDifferent field1='deces' field2='Date_décès' namefield1='Décès X.org' namefield2='Décès AX'} + +{else} + +

Aucune différence pour les renseignements de décès entre les deux annuaires.

+ +{/if} -- 2.1.4