modules/fusionax.php

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