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/ids'      => $this->make_hook('ids', AUTH_MDP, 'admin'),
  44             'fusionax/misc'     => $this->make_hook('misc', AUTH_MDP, 'admin'),
  45         );
  46     }
  47
  48
  49     function handler_index(&$page)
  50     {
  51         $globals = Platal::globals();
  52
  53         $page->changeTpl('fusionax/index.tpl');
  54         $page->assign('xorg_title', 'Polytechnique.org - Fusion des annuaires');
  55         if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
  56             $page->assign('lastimport', date("d-m-Y", $globals->fusionax->LastUpdate));
  57         }
  58     }
  59
  60     /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
  61     function handler_import(&$page, $action = 'index', $fileSQL = '')
  62     {
  63         $globals = Platal::globals();
  64
  65         if ($action == 'index') {
  66             $page->changeTpl('fusionax/import.tpl');
  67             $page->addJsLink('jquery.js');
  68             if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
  69                 $page->assign(
  70                     'lastimport',
  71                     "le " . date("d/m/Y à H:i", $globals->fusionax->LastUpdate));
  72             }
  73             return;
  74         }
  75
  76         // toutes les actions sont faites en ajax en utilisant jquery
  77         header("Content-type: text/javascript; charset=utf-8");
  78
  79         // log des actions
  80         $report = array();
  81
  82         // création d'un fichier temporaire si nécessaire
  83         if (Env::has('tmpdir')) {
  84             $tmpdir = Env::v('tmpdir');
  85         } else {
  86             $tmpdir = tempnam('/tmp', 'fusionax');
  87             unlink($tmpdir);
  88             mkdir($tmpdir);
  89             chmod($tmpdir, 0700);
  90         }
  91
  92         $modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/';
  93         $olddir = getcwd();
  94         chdir($tmpdir);
  95
  96         if ($action == 'launch') {
  97             // séparation de l'archive en fichiers par tables
  98             exec($modulepath . 'import-ax.sh', $report);
  99             $report[] = 'Fichier parsé.';
 100             $report[] = 'Import dans la base en cours...';
 101             $next = 'integrateSQL';
 102         } elseif ($action == 'integrateSQL') {
 103             // intégration des données dans la base MySQL
 104             // liste des fichiers sql à exécuter
 105             $filesSQL = array(
 106                 'Activites.sql',
 107                 'Adresses.sql',
 108                 'Anciens.sql',
 109                 'Formations.sql',
 110                 'Entreprises.sql');
 111             if ($fileSQL != '') {
 112                 // récupère le contenu du fichier sql
 113                 $queries = explode(';', file_get_contents($modulepath . $fileSQL));
 114                 foreach ($queries as $q) {
 115                     if (trim($q)) {
 116                         // coupe le fichier en requêtes individuelles
 117                         if (substr($q, 0, 2) == '--') {
 118                             // affiche les commentaires dans le report
 119                             $lines = explode("\n", $q);
 120                             $l = $lines[0];
 121                             $report[] = addslashes($l);
 122                         }
 123                         // exécute la requête
 124                         XDB::execute($q);
 125                     }
 126                 }
 127                 // trouve le prochain fichier à exécuter
 128                 $trans = array_flip($filesSQL);
 129                 $nextfile = $trans[$fileSQL] + 1;
 130             } else {
 131                 $nextfile = 0;
 132             }
 133             if (!isset($filesSQL[$nextfile])) {
 134                 // tous les fichiers ont été exécutés, on passe à l'étape
 135                 // suivante
 136                 $next = 'clean';
 137             } else {
 138                 // on passe au fichier suivant
 139                 $next = 'integrateSQL/' . $filesSQL[$nextfile];
 140             }
 141         } elseif ($action == 'clean') {
 142             // nettoyage du fichier temporaire
 143             chdir($olddir);
 144             exec("rm -Rf $tmpdir", $report);
 145             $report[] = "Fin de l\'import";
 146             // met à jour la date de dernier import
 147             //$globals->change_dynamic_config(array('LastUpdate' => time()), 'FusionAx');
 148         }
 149         foreach($report as $t) {
 150             // affiche les lignes de report
 151             echo "$('#fusionax_import').append('" . $t . "<br/>');\n";
 152         }
 153         if (isset($next)) {
 154             $tmpdir = getcwd();
 155             chdir($olddir);
 156             // lance le prochain script s'il y en a un
 157             echo "$.getScript('fusionax/import/" . $next . "?tmpdir=" . urlencode($tmpdir) . "');";
 158         }
 159         // exit pour ne pas afficher la page template par défaut
 160         exit;
 161     }
 162
 163     /** Lier les identifiants d'un ancien dans les deux annuaires
 164      * @param user_id identifiant dans l'annuaire X.org
 165      * @param matricule_ax identifiant dans l'annuaire de l'AX
 166      * @return 0 si la liaison a échoué, 1 sinon
 167      */
 168     private static function link_by_ids($user_id, $matricule_ax)
 169     {
 170         $res = XDB::execute("UPDATE  fusionax_import AS i
 171                          INNER JOIN  fusionax_xorg_anciens AS u
 172                                 SET  u.matricule_ax = i.id_ancien,
 173                                      i.user_id = u.user_id,
 174                                      i.date_match_id = NOW()
 175                               WHERE  i.id_ancien = {?} AND u.user_id = {?}
 176                                      AND (u.matricule_ax != {?} OR u.matricule_ax IS NULL
 177                                           OR i.user_id != {?} OR i.user_id IS NULL)",
 178                             $matricule_ax, $user_id, $matricule_ax, $user_id);
 179         if (!$res) {
 180             return 0;
 181         }
 182         return XDB::affectedRows() / 2;
 183     }
 184
 185     /** Recherche automatique d'anciens à lier entre les deux annuaires
 186      * @param limit nombre d'anciens à trouver au max
 187      * @param sure si true, ne trouve que des anciens qui sont quasi sûrs
 188      * @return un XOrgDBIterator sur les entrées avec display_name, promo,
 189      * user_id, id_ancien et display_name_ax
 190      */
 191     private static function find_easy_to_link($limit = 10, $sure = false)
 192     {
 193         $easy_to_link = XDB::iterator("
 194         SELECT  xorg.display_name, xorg.promo, xorg.user_id, ax.id_ancien,
 195                 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
 196                 COUNT(*) AS nbMatches
 197           FROM  fusionax_anciens AS ax
 198     INNER JOIN  fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL)
 199      LEFT JOIN  fusionax_xorg_anciens AS xorg ON (xorg.matricule_ax IS NULL
 200                                                   AND ax.Nom_complet = xorg.nom
 201                                                   AND ax.prenom = xorg.prenom
 202                                                   AND xorg.promo = ax.promotion_etude)
 203       GROUP BY  xorg.user_id
 204         HAVING  xorg.user_id IS NOT NULL AND nbMatches = 1" . ($limit ? ('LIMIT ' . $limit) : ''));
 205         if ($easy_to_link->total() > 0 || $sure) {
 206             return $easy_to_link;
 207         }
 208         return XDB::iterator("
 209         SELECT  xorg.display_name, xorg.promo, xorg.user_id, ax.id_ancien,
 210                 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
 211                 COUNT(*) AS nbMatches
 212           FROM  fusionax_anciens AS ax
 213     INNER JOIN  fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL)
 214      LEFT JOIN  fusionax_xorg_anciens AS xorg ON (xorg.matricule_ax IS NULL
 215                                                   AND (ax.Nom_complet = xorg.nom
 216                                                        OR ax.Nom_complet LIKE CONCAT(xorg.nom,' %')
 217                                                        OR ax.Nom_complet LIKE CONCAT(xorg.nom,'-%')
 218                                                        OR ax.Nom_usuel = xorg.nom
 219                                                        OR xorg.nom LIKE CONCAT('% ',ax.Nom_complet))
 220                                                   AND xorg.promo < ax.promotion_etude + 2
 221                                                   AND xorg.promo > ax.promotion_etude - 2)
 222       GROUP BY  xorg.user_id
 223         HAVING  xorg.user_id IS NOT NULL AND nbMatches = 1" . ($limit ? ('LIMIT ' . $limit) : ''));
 224     }
 225
 226     /** Module de mise en correspondance les ids */
 227     function handler_ids(&$page, $part = 'main', $user_id = null, $matricule_ax = null)
 228     {
 229         $globals = Platal::globals();
 230
 231         $page->addJsLink('jquery.js');
 232         $page->assign(
 233             'xorg_title',
 234             'Polytechnique.org - Fusion - Mise en correspondance simple');
 235         if ($part == 'missingInAX') {
 236             // locate all persons from this database that are not in AX's
 237             $page->changeTpl('fusionax/idsMissingInAx.tpl');
 238             $missingInAX = XDB::iterator("SELECT  u.promo, u.user_id, u.display_name
 239                                             FROM  fusionax_xorg_anciens AS u
 240                                            WHERE  u.matricule_ax IS NULL
 241                                            LIMIT  20");
 242             $page->assign('missingInAX', $missingInAX);
 243             return;
 244         }
 245         if ($part == 'missingInXorg') {
 246             // locate all persons from AX's database that are not here
 247             $page->changeTpl('fusionax/idsMissingInXorg.tpl');
 248             $missingInXorg = XDB::iterator("SELECT  a.promotion_etude AS promo,
 249                                                     CONCAT(a.prenom, ' ', a.Nom_usuel) AS display_name,
 250                                                     a.id_ancien
 251                                               FROM  fusionax_import
 252                                         INNER JOIN  fusionax_anciens AS a USING (id_ancien)
 253                                              WHERE  fusionax_import.user_id IS NULL
 254                                              LIMIT  20");
 255             $page->assign('missingInXorg', $missingInXorg);
 256             return;
 257         }
 258         if ($part == 'link') {
 259             FusionAxModule::link_by_ids($user_id, $matricule_ax);
 260             exit;
 261         }
 262         if ($part == 'linknext') {
 263             $linksToDo = FusionAxModule::find_easy_to_link(10);
 264             while ($l = $linksToDo->next()) {
 265                 FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
 266             }
 267             pl_redirect('fusionax/ids#autolink');
 268         }
 269         if ($part == 'linkall') {
 270             $linksToDo = FusionAxModule::find_easy_to_link(0);
 271             while ($l = $linksToDo->next()) {
 272                 FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
 273             }
 274         }
 275         {
 276             $page->changeTpl('fusionax/ids.tpl');
 277             $missingInAX = XDB::query('SELECT  COUNT(*)
 278                                          FROM  fusionax_xorg_anciens AS u
 279                                         WHERE  u.matricule_ax IS NULL');
 280             if ($missingInAX) {
 281                 $page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
 282             }
 283             $missingInXorg = XDB::query('SELECT  COUNT(*)
 284                                            FROM  fusionax_import AS i
 285                                           WHERE  i.user_id IS NULL');
 286             if ($missingInXorg) {
 287                 $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
 288             }
 289             $easyToLink = FusionAxModule::find_easy_to_link(10);
 290             if ($easyToLink->total() > 0) {
 291                 $page->assign('easyToLink', $easyToLink);
 292             }
 293         }
 294     }
 295
 296     function handler_misc(&$page)
 297     {
 298         $page->changeTpl('fusionax/misc.tpl');
 299         // deceased
 300         $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
 301         $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
 302         $page->assign('deceasedMissingInXorg',
 303                       XDB::iterator('SELECT  d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_ax,
 304                                              CONCAT(d.prenom, " ", d.nom) AS display_name
 305                                        FROM  fusionax_deceased AS d
 306                                       WHERE  d.deces_xorg = "0000-00-00"
 307                                       LIMIT  10'));
 308         $page->assign('deceasedMissingInAX',
 309                       XDB::iterator('SELECT  d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_xorg,
 310                                              CONCAT(d.prenom, " ", d.nom) AS display_name
 311                                        FROM  fusionax_deceased AD d
 312                                       WHERE  d.deces_ax = "0000-00-00"
 313                                       LIMIT  10'));
 314         $page->assign('deceasedDifferent',
 315                       XDB::iterator('SELECT  d.user_id, d.id_ancien, d.nom, d.prenom, d.promo,
 316                                              d.deces_ax, d.deces_xorg,
 317                                              CONCAT(d.prenom, " ", d.nom) AS display_name
 318                                        FROM  fusionax_deceased AS d
 319                                       WHERE  d.deces_xorg != "0000-00-00" AND d.deces_ax != "0000-00-00"
 320                                       LIMIT  10'));
 321     }
 322 }
 323
 324 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:?>