modules/fusionax.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2007 Polytechnique.org                              *
   4  *  http://opensource.polytechnique.org/                                   *
   5  *                                                                         *
   6  *  This program is free software; you can redistribute it and/or modify   *
   7  *  it under the terms of the GNU General Public License as published by   *
   8  *  the Free Software Foundation; either version 2 of the License, or      *
   9  *  (at your option) any later version.                                    *
  10  *                                                                         *
  11  *  This program is distributed in the hope that it will be useful,        *
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14  *  GNU General Public License for more details.                           *
  15  *                                                                         *
  16  *  You should have received a copy of the GNU General Public License      *
  17  *  along with this program; if not, write to the Free Software            *
  18  *  Foundation, Inc.,                                                      *
  19  *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20  ***************************************************************************/
  21
  22 /**
  23  * @brief Module to merge data from AX database
  24  *
  25  * Module to import data from another database of alumni that had
  26  * different schemas. The organization that used this db is called AX
  27  * hence the name of this module.
  28  *
  29  * Datas are stored in an external server and you need a private key
  30  * to connect to their server.
  31  */
  32 class FusionAxModule extends PLModule
  33 {
  34     function __construct()
  35     {
  36     }
  37
  38     function handlers()
  39     {
  40         return array(
  41             'fusionax'          => $this->make_hook('index',    AUTH_MDP, 'admin'),
  42             'fusionax/import'   => $this->make_hook('import',   AUTH_MDP, 'admin'),
  43             'fusionax/view'     => $this->make_hook('view',     AUTH_MDP, 'admin'),
  44             'fusionax/ids'      => $this->make_hook('ids',      AUTH_MDP, 'admin'),
  45             'fusionax/deceased' => $this->make_hook('deceased', AUTH_MDP, 'admin'),
  46             'fusionax/promo'    => $this->make_hook('promo',    AUTH_MDP, 'admin'),
  47         );
  48     }
  49
  50
  51     function handler_index(&$page)
  52     {
  53         $globals = Platal::globals();
  54
  55         $page->changeTpl('fusionax/index.tpl');
  56         $page->assign('xorg_title', 'Polytechnique.org - Fusion des annuaires');
  57         if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
  58             $page->assign('lastimport', date("d-m-Y", $globals->fusionax->LastUpdate));
  59         }
  60     }
  61
  62     /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
  63     function handler_import(&$page, $action = 'index', $fileSQL = '')
  64     {
  65         $globals = Platal::globals();
  66
  67         if ($action == 'index') {
  68             $page->changeTpl('fusionax/import.tpl');
  69             $page->addJsLink('jquery.js');
  70             if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
  71                 $page->assign(
  72                     'lastimport',
  73                     "le " . date("d/m/Y à H:i", $globals->fusionax->LastUpdate));
  74             }
  75             return;
  76         }
  77
  78         // toutes les actions sont faites en ajax en utilisant jquery
  79         header("Content-type: text/javascript; charset=utf-8");
  80
  81         // log des actions
  82         $report = array();
  83
  84         // création d'un fichier temporaire si nécessaire
  85         if (Env::has('tmpdir')) {
  86             $tmpdir = Env::v('tmpdir');
  87         } else {
  88             $tmpdir = tempnam('/tmp', 'fusionax');
  89             unlink($tmpdir);
  90             mkdir($tmpdir);
  91             chmod($tmpdir, 0700);
  92         }
  93
  94         $modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/';
  95         $olddir = getcwd();
  96         chdir($tmpdir);
  97
  98         if ($action == 'launch') {
  99             // séparation de l'archive en fichiers par tables
 100             exec($modulepath . 'import-ax.sh', $report);
 101             $report[] = 'Fichier parsé.';
 102             $report[] = 'Import dans la base en cours...';
 103             $next = 'integrateSQL';
 104         } elseif ($action == 'integrateSQL') {
 105             // intégration des données dans la base MySQL
 106             // liste des fichiers sql à exécuter
 107             $filesSQL = array(
 108                 'Activites.sql',
 109                 'Adresses.sql',
 110                 'Anciens.sql',
 111                 'Formations.sql',
 112                 'Entreprises.sql');
 113             if ($fileSQL != '') {
 114                 // récupère le contenu du fichier sql
 115                 $queries = explode(';', file_get_contents($modulepath . $fileSQL));
 116                 foreach ($queries as $q) {
 117                     if (trim($q)) {
 118                         // coupe le fichier en requêtes individuelles
 119                         if (substr($q, 0, 2) == '--') {
 120                             // affiche les commentaires dans le report
 121                             $lines = explode("\n", $q);
 122                             $l = $lines[0];
 123                             $report[] = addslashes($l);
 124                         }
 125                         // exécute la requête
 126                         XDB::execute($q);
 127                     }
 128                 }
 129                 // trouve le prochain fichier à exécuter
 130                 $trans = array_flip($filesSQL);
 131                 $nextfile = $trans[$fileSQL] + 1;
 132             } else {
 133                 $nextfile = 0;
 134             }
 135             if (!isset($filesSQL[$nextfile])) {
 136                 // tous les fichiers ont été exécutés, on passe à l'étape
 137                 // suivante
 138                 $next = 'clean';
 139             } else {
 140                 // on passe au fichier suivant
 141                 $next = 'integrateSQL/' . $filesSQL[$nextfile];
 142             }
 143         } elseif ($action == 'clean') {
 144             // nettoyage du fichier temporaire
 145             chdir($olddir);
 146             exec("rm -Rf $tmpdir", $report);
 147             $report[] = "Fin de l\'import";
 148             // met à jour la date de dernier import
 149             //$globals->change_dynamic_config(array('LastUpdate' => time()), 'FusionAx');
 150         }
 151         foreach($report as $t) {
 152             // affiche les lignes de report
 153             echo "$('#fusionax_import').append('" . $t . "<br/>');\n";
 154         }
 155         if (isset($next)) {
 156             $tmpdir = getcwd();
 157             chdir($olddir);
 158             // lance le prochain script s'il y en a un
 159             echo "$.getScript('fusionax/import/" . $next . "?tmpdir=" . urlencode($tmpdir) . "');";
 160         }
 161         // exit pour ne pas afficher la page template par défaut
 162         exit;
 163     }
 164
 165     /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
 166     function handler_view(&$page, $action = '')
 167     {
 168         $globals = Platal::globals();
 169
 170         $page->changeTpl('fusionax/view.tpl');
 171         if ($action == 'create') {
 172             XDB::execute('DROP VIEW IF EXISTS fusionax_deceased');
 173             XDB::execute('CREATE VIEW  fusionax_deceased AS
 174                                SELECT  u.user_id, a.id_ancien, u.nom, u.prenom, u.promo, u.deces AS deces_xorg, a.Date_deces AS deces_ax
 175                                  FROM  auth_user_md5    AS u
 176                            INNER JOIN  fusionax_anciens AS a ON (a.id_ancien = u.matricule_ax)
 177                                 WHERE  u.deces != a.Date_deces');
 178             XDB::execute('DROP VIEW IF EXISTS fusionax_promo');
 179             XDB::execute('CREATE VIEW  fusionax_promo AS
 180                                SELECT  u.user_id, u.matricule_ax, CONCAT(u.nom, " ", u.prenom) AS display_name, u.promo AS promo_etude_xorg,
 181                                        f.promotion_etude AS promo_etude_ax, u.promo_sortie AS promo_sortie_xorg
 182                                  FROM  auth_user_md5    AS u
 183                            INNER JOIN  fusionax_anciens AS f ON (u.matricule_ax = f.id_ancien)
 184                                 WHERE  u.promo != f.promotion_etude AND !(f.promotion_etude = u.promo + 1 AND u.promo_sortie = u.promo + 4)');
 185         }
 186     }
 187
 188     /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */
 189     private static function clear_wrong_in_xorg($user_id)
 190     {
 191         $res = XDB::execute("UPDATE  fusionax_xorg_anciens
 192                                 SET  matricule_ax = NULL
 193                               WHERE  user_id = {?}", $user_id);
 194         if (!$res) {
 195             return 0;
 196         }
 197         return XDB::affectedRows() / 2;
 198     }
 199
 200     /* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX
 201      * (mises à part les promo 1921 et 1923 qui ne figurent pas dans les données de l'AX)*/
 202     private static function find_wrong_in_xorg($limit = 10)
 203     {
 204         return XDB::iterator("SELECT  u.promo, u.user_id, u.display_name
 205                                 FROM  fusionax_xorg_anciens AS u
 206                                WHERE  NOT EXISTS (SELECT  *
 207                                                     FROM  fusionax_anciens AS f
 208                                                    WHERE  f.id_ancien = u.matricule_ax)
 209                                       AND u.matricule_ax IS NOT NULL AND promo != 1921 AND promo != 1923");
 210     }
 211
 212     /** Lier les identifiants d'un ancien dans les deux annuaires
 213      * @param user_id identifiant dans l'annuaire X.org
 214      * @param matricule_ax identifiant dans l'annuaire de l'AX
 215      * @return 0 si la liaison a échoué, 1 sinon
 216      */
 217     private static function link_by_ids($user_id, $matricule_ax)
 218     {
 219         $res = XDB::execute("UPDATE  fusionax_import       AS i
 220                          INNER JOIN  fusionax_xorg_anciens AS u
 221                                 SET  u.matricule_ax = i.id_ancien,
 222                                      i.user_id = u.user_id,
 223                                      i.date_match_id = NOW()
 224                               WHERE  i.id_ancien = {?} AND u.user_id = {?}
 225                                      AND (u.matricule_ax != {?} OR u.matricule_ax IS NULL
 226                                           OR i.user_id != {?} OR i.user_id IS NULL)",
 227                             $matricule_ax, $user_id, $matricule_ax, $user_id);
 228         if (!$res) {
 229             return 0;
 230         }
 231         return XDB::affectedRows() / 2;
 232     }
 233
 234     /** Recherche automatique d'anciens à lier entre les deux annuaires
 235      * @param limit nombre d'anciens à trouver au max
 236      * @param sure si true, ne trouve que des anciens qui sont quasi sûrs
 237      * @return un XOrgDBIterator sur les entrées avec display_name, promo,
 238      * user_id, id_ancien et display_name_ax
 239      */
 240     private static function find_easy_to_link($limit = 10, $sure = false)
 241     {
 242         $easy_to_link = XDB::iterator("
 243         SELECT  u.display_name, u.promo, u.user_id, ax.id_ancien,
 244                 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
 245                 COUNT(*) AS nbMatches
 246           FROM  fusionax_anciens      AS ax
 247     INNER JOIN  fusionax_import       AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL)
 248      LEFT JOIN  fusionax_xorg_anciens AS u ON (u.matricule_ax IS NULL
 249                                                AND ax.Nom_patronymique = u.nom
 250                                                AND ax.prenom = u.prenom
 251                                                AND u.promo = ax.promotion_etude)
 252       GROUP BY  u.user_id
 253         HAVING  u.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : ''));
 254         if ($easy_to_link->total() > 0 || $sure) {
 255             return $easy_to_link;
 256         }
 257         return XDB::iterator("
 258         SELECT  u.display_name, u.promo, u.user_id, ax.id_ancien,
 259                 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
 260                 COUNT(*) AS nbMatches
 261           FROM  fusionax_anciens      AS ax
 262     INNER JOIN  fusionax_import       AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL)
 263      LEFT JOIN  fusionax_xorg_anciens AS u ON (u.matricule_ax IS NULL
 264                                                AND (ax.Nom_patronymique = u.nom
 265                                                     OR ax.Nom_patronymique LIKE CONCAT(u.nom, ' %')
 266                                                     OR ax.Nom_patronymique LIKE CONCAT(u.nom, '-%')
 267                                                     OR ax.Nom_usuel = u.nom
 268                                                     OR u.nom LIKE CONCAT('% ', ax.Nom_patronymique))
 269                                                AND u.promo < ax.promotion_etude + 2
 270                                                AND u.promo > ax.promotion_etude - 2)
 271       GROUP BY  u.user_id
 272         HAVING  u.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : ''));
 273     }
 274
 275     /** Module de mise en correspondance les ids */
 276     function handler_ids(&$page, $part = 'main', $user_id = null, $matricule_ax = null)
 277     {
 278         $globals = Platal::globals();
 279         $nbToLink = 100;
 280
 281         $page->addJsLink('jquery.js');
 282         $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple');
 283         if ($part == 'missingInAX') {
 284             // locate all persons from this database that are not in AX's
 285             $page->changeTpl('fusionax/idsMissingInAx.tpl');
 286             $missingInAX = XDB::iterator("SELECT  promo, user_id, display_name
 287                                             FROM  fusionax_xorg_anciens
 288                                            WHERE  matricule_ax IS NULL");
 289             $page->assign('missingInAX', $missingInAX);
 290             return;
 291         }
 292         if ($part == 'missingInXorg') {
 293             // locate all persons from AX's database that are not here
 294             $page->changeTpl('fusionax/idsMissingInXorg.tpl');
 295             $missingInXorg = XDB::iterator("SELECT  a.promotion_etude AS promo,
 296                                                     CONCAT(a.prenom, ' ', a.Nom_usuel) AS display_name,
 297                                                     a.id_ancien
 298                                               FROM  fusionax_import
 299                                         INNER JOIN  fusionax_anciens AS a USING (id_ancien)
 300                                              WHERE  fusionax_import.user_id IS NULL");
 301             $page->assign('missingInXorg', $missingInXorg);
 302             return;
 303         }
 304         if ($part == 'wrongInXorg') {
 305             // locate all persons from Xorg database that have a bad AX id
 306             $page->changeTpl('fusionax/idswrongInXorg.tpl');
 307             $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
 308             $page->assign('wrongInXorg', $wrongInXorg);
 309             return;
 310         }
 311         if ($part == 'cleanwronginxorg') {
 312             $linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink);
 313             while ($l = $linksToDo->next()) {
 314                 FusionAxModule::clear_wrong_in_xorg($l['user_id']);
 315             }
 316             pl_redirect('fusionax/ids/wrongInXorg');
 317         }
 318         if ($part == 'lier') {
 319             if (Post::has('user_id') && Post::has('matricule_ax')) {
 320                 FusionAxModule::link_by_ids(Post::i('user_id'), Post::v('matricule_ax'));
 321             }
 322         }
 323         if ($part == 'link') {
 324             FusionAxModule::link_by_ids($user_id, $matricule_ax);
 325             exit;
 326         }
 327         if ($part == 'linknext') {
 328             $linksToDo = FusionAxModule::find_easy_to_link($nbToLink);
 329             while ($l = $linksToDo->next()) {
 330                 FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
 331             }
 332             pl_redirect('fusionax/ids#autolink');
 333         }
 334         if ($part == 'linkall') {
 335             $linksToDo = FusionAxModule::find_easy_to_link(0);
 336             while ($l = $linksToDo->next()) {
 337                 FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
 338             }
 339         }
 340         {
 341             $page->changeTpl('fusionax/ids.tpl');
 342             $missingInAX = XDB::query('SELECT  COUNT(*)
 343                                          FROM  fusionax_xorg_anciens AS u
 344                                         WHERE  u.matricule_ax IS NULL');
 345             if ($missingInAX) {
 346                 $page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
 347             }
 348             $missingInXorg = XDB::query('SELECT  COUNT(*)
 349                                            FROM  fusionax_import AS i
 350                                           WHERE  i.user_id IS NULL');
 351             if ($missingInXorg) {
 352                 $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
 353             }
 354             $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
 355             if ($wrongInXorg->total() > 0) {
 356                 $page->assign('wrongInXorg', $wrongInXorg->total());
 357             }
 358             $easyToLink = FusionAxModule::find_easy_to_link($nbToLink);
 359             if ($easyToLink->total() > 0) {
 360                 $page->assign('nbMatch', $easyToLink->total());
 361                 $page->assign('easyToLink', $easyToLink);
 362             }
 363         }
 364     }
 365
 366     function handler_deceased(&$page, $action = '')
 367     {
 368         if ($action == 'updateXorg') {
 369             XDB::execute('UPDATE  fusionax_deceased
 370                              SET  deces_xorg = deces_ax
 371                            WHERE  deces_xorg = "0000-00-00"');
 372         }
 373         if ($action == 'updateAX') {
 374             XDB::execute('UPDATE  fusionax_deceased
 375                              SET  deces_ax = deces_xorg
 376                            WHERE  deces_ax = "0000-00-00"');
 377         }
 378         if ($action == 'update') {
 379             if (Post::has('user_id') && Post::has('date')) {
 380                 XDB::execute('UPDATE  fusionax_deceased
 381                                  SET  deces_ax = {?}, deces_xorg = {?}
 382                                WHERE  user_id = {?}',
 383                              Post::v('date'), Post::v('date'), Post::i('user_id'));
 384             }
 385         }
 386         $page->changeTpl('fusionax/deceased.tpl');
 387         // deceased
 388         $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
 389         $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
 390         $res = XDB::iterator('SELECT  d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_ax,
 391                                       CONCAT(d.prenom, " ", d.nom) AS display_name
 392                                 FROM  fusionax_deceased AS d
 393                                WHERE  d.deces_xorg = "0000-00-00"
 394                                LIMIT  10');
 395         $page->assign('nbDeceasedMissingInXorg', $res->total());
 396         $page->assign('deceasedMissingInXorg', $res);
 397         $res = XDB::iterator('SELECT  d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_xorg,
 398                                       CONCAT(d.prenom, " ", d.nom) AS display_name
 399                                 FROM  fusionax_deceased AS d
 400                                WHERE  d.deces_ax = "0000-00-00"
 401                                LIMIT  10');
 402         $page->assign('nbDeceasedMissingInAX', $res->total());
 403         $page->assign('deceasedMissingInAX', $res);
 404         $res = XDB::iterator('SELECT  d.user_id, d.id_ancien, d.nom, d.prenom, d.promo,
 405                                       d.deces_ax, d.deces_xorg,
 406                                       CONCAT(d.prenom, " ", d.nom, " ", d.user_id) AS display_name
 407                                 FROM  fusionax_deceased AS d
 408                                WHERE  d.deces_xorg != "0000-00-00" AND d.deces_ax != "0000-00-00"');
 409         $page->assign('nbDeceasedDifferent', $res->total());
 410         $page->assign('deceasedDifferent', $res);
 411     }
 412
 413     function handler_promo(&$page, $action = '')
 414     {
 415         $page->changeTpl('fusionax/promo.tpl');
 416         $res = XDB::iterator('SELECT  user_id, display_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax
 417                                 FROM  fusionax_promo
 418                                WHERE  !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg)');
 419         $nbMissmatchingPromos = $res->total();
 420         $page->assign('nbMissmatchingPromos1', $res->total());
 421         $page->assign('missmatchingPromos1', $res);
 422         $res = XDB::iterator('SELECT  user_id, display_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax
 423                                 FROM  fusionax_promo
 424                                WHERE  promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg');
 425         $nbMissmatchingPromos += $res->total();
 426         $page->assign('nbMissmatchingPromos2', $res->total());
 427         $page->assign('missmatchingPromos2', $res);
 428         $page->assign('nbMissmatchingPromos', $nbMissmatchingPromos);
 429     }
 430 }
 431
 432 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:?>