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  * Module to merge data from AX database: this will only be used once on
  24  * production site and should be removed afterwards.
  25  *
  26  * Module to import data from another database of alumni that had
  27  * different schemas. The organization that used this db is called AX
  28  * hence the name of this module.
  29  *
  30  * Datas are stored in an export file.
  31  */
  32 class FusionAxModule extends PLModule
  33 {
  34     function handlers()
  35     {
  36         return array(
  37             'fusionax'          => $this->make_hook('index',    AUTH_MDP, 'admin'),
  38             'fusionax/import'   => $this->make_hook('import',   AUTH_MDP, 'admin'),
  39             'fusionax/view'     => $this->make_hook('view',     AUTH_MDP, 'admin'),
  40             'fusionax/ids'      => $this->make_hook('ids',      AUTH_MDP, 'admin'),
  41             'fusionax/deceased' => $this->make_hook('deceased', AUTH_MDP, 'admin'),
  42             'fusionax/promo'    => $this->make_hook('promo',    AUTH_MDP, 'admin'),
  43             'fusionax/names'    => $this->make_hook('names',    AUTH_MDP, 'admin'),
  44         );
  45     }
  46
  47
  48     function handler_index(&$page)
  49     {
  50         $page->changeTpl('fusionax/index.tpl');
  51         $page->assign('xorg_title', 'Polytechnique.org - Fusion des annuaires');
  52     }
  53
  54     /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
  55     function handler_import(&$page, $action = 'index', $file = '')
  56     {
  57         if ($action == 'index') {
  58             $page->changeTpl('fusionax/import.tpl');
  59             return;
  60         }
  61
  62         // toutes les actions sont faites en ajax en utilisant jquery
  63         header('Content-type: text/javascript; charset=utf-8');
  64
  65         // log des actions
  66         $report = array();
  67
  68         $modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/';
  69         $spoolpath = realpath(dirname(__FILE__) . '/../spool/fusionax/') . '/';
  70
  71         if ($action == 'launch') {
  72             if ($file == '') {
  73                 $report[] = 'Nom de fichier non renseigné.';
  74             } elseif (!file_exists(dirname(__FILE__) . '/../spool/fusionax/' . $file)) {
  75                 $report[] = 'Le fichier ne se situe pas au bon endroit.';
  76             } else {
  77                 // séparation de l'archive en fichiers par tables
  78                 $file = $spoolpath . $file;
  79                 // Removes master and doctorate students
  80                 exec('grep -v "^[A-Z]\{2\}.[0-9]\{4\}[MD][0-9]\{3\}" ' . $file . ' > ' . $file . '.tmp');
  81                 exec('mv -f ' . $file . '.tmp ' . $file);
  82                 // Split export into specialised files
  83                 exec('grep "^AD" ' . $file . ' > ' . $spoolpath . 'Adresses.txt');
  84                 exec('grep "^AN" ' . $file . ' > ' . $spoolpath . 'Anciens.txt');
  85                 exec('grep "^FO" ' . $file . ' > ' . $spoolpath . 'Formations.txt');
  86                 exec('grep "^AC" ' . $file . ' > ' . $spoolpath . 'Activites.txt');
  87                 exec('grep "^EN" ' . $file . ' > ' . $spoolpath . 'Entreprises.txt');
  88                 exec($modulepath . 'formation.pl');
  89                 exec('mv -f ' . $spoolpath . 'Formations_out.txt ' . $spoolpath . 'Formations.txt');
  90                 $report[] = 'Fichier parsé.';
  91                 $report[] = 'Import dans la base en cours...';
  92                 $next = 'integrateSQL';
  93             }
  94         } elseif ($action == 'integrateSQL') {
  95             // intégration des données dans la base MySQL
  96             // liste des fichiers sql à exécuter
  97             $filesSQL = array(
  98                 0 => 'Activites.sql',
  99                 1 => 'Adresses.sql',
 100                 2 => 'Anciens.sql',
 101                 3 => 'Formations.sql',
 102                 4 => 'Entreprises.sql'
 103             );
 104             if ($file != '') {
 105                 // récupère le contenu du fichier sql
 106                 $queries = explode(';', file_get_contents($modulepath . $filesSQL[$file]));
 107                 foreach ($queries as $q) {
 108                     if (trim($q)) {
 109                         // coupe le fichier en requêtes individuelles
 110                         if (substr($q, 0, 2) == '--') {
 111                             // affiche les commentaires dans le report
 112                             $lines = explode("\n", $q);
 113                             $l = $lines[0];
 114                             $report[] = addslashes($l);
 115                         }
 116                         // exécute la requête
 117                         XDB::execute(str_replace('{?}', $spoolpath, $q));
 118                     }
 119                 }
 120                 // trouve le prochain fichier à exécuter
 121                 $nextfile = $file + 1;
 122             } else {
 123                 $nextfile = 0;
 124             }
 125             if ($nextfile > 4) {
 126                 // tous les fichiers ont été exécutés, on passe à l'étape suivante
 127                 $next = 'adds1920';
 128             } else {
 129                 // on passe au fichier suivant
 130                 $next = 'integrateSQL/' . $nextfile;
 131             }
 132         } elseif ($action == 'adds1920') {
 133             // Adds promotion 1920 from AX db.
 134             $report[] = 'Ajout de la promotion 1920';
 135             $res = XDB::iterator('SELECT  prenom, Nom_complet, ax_id
 136                                     FROM  fusionax_anciens
 137                                    WHERE  promotion_etude = 1920;');
 138
 139             $nameTypes = DirEnum::getOptions(DirEnum::NAMETYPES);
 140             $nameTypes = array_flip($nameTypes);
 141             $eduSchools = DirEnum::getOptions(DirEnum::EDUSCHOOLS);
 142             $eduSchools = array_flip($eduSchools);
 143             $eduDegrees = DirEnum::getOptions(DirEnum::EDUDEGREES);
 144             $eduDegrees = array_flip($eduDegrees);
 145             $degreeid = $eduDegrees[Profile::DEGREE_X];
 146             $entry_year = 1920;
 147             $grad_year = 1923;
 148             $promo = 'X1920';
 149             $sex = PlUser::GENDER_MALE;
 150             $xorgId = 19200000;
 151             $type = 'x';
 152
 153             while (list($firstname, $lastname, $ax_id) = $res->next()) {
 154                 $hrid = self::getHrid($firstname, $lastname, $promo);
 155                 $res1 = XDB::query('SELECT  COUNT(*)
 156                                       FROM  accounts
 157                                      WHERE  hruid = {?}', $hrid);
 158                 $res2 = XDB::query('SELECT  COUNT(*)
 159                                       FROM  profiles
 160                                      WHERE  hrpid = {?}', $hrid);
 161                 if (is_null($hrid) || $res1->fetchOneCell() > 0 || $res2->fetchOneCell() > 0) {
 162                     $report[] = $ax_id . ' non ajouté';
 163                 }
 164                 $fullName = $firstname . ' ' . $lastname;
 165                 $directoryName = $lastname . ' ' . $firstname;
 166                 ++$xorgId;
 167
 168                 XDB::execute('REPLACE INTO  profiles (hrpid, xorg_id, ax_id, sex)
 169                                     VALUES  ({?}, {?}, {?}, {?})',
 170                              $hrid, $xorgId, $ax_id, $sex);
 171                 $pid = XDB::insertId();
 172                 XDB::execute('REPLACE INTO  profile_name (pid, name, typeid)
 173                                     VALUES  ({?}, {?}, {?})',
 174                              $pid, $lastname, $nameTypes['name_ini']);
 175                 XDB::execute('REPLACE INTO  profile_name (pid, name, typeid)
 176                                     VALUES  ({?}, {?}, {?})',
 177                              $pid, $firstname, $nameTypes['firstname_ini']);
 178                 XDB::execute('REPLACE INTO  profile_display (pid, yourself, public_name, private_name,
 179                                                              directory_name, short_name, sort_name, promo)
 180                                     VALUES  ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
 181                              $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo);
 182                 XDB::execute('REPLACE INTO  profile_education (pid, eduid, degreeid, entry_year, grad_year, flags)
 183                                     VALUES  ({?}, {?}, {?}, {?}, {?}, {?})',
 184                              $pid, $eduSchools[Profile::EDU_X], $degreeid, $entry_year, $grad_year, 'primary');
 185                 XDB::execute('REPLACE INTO  accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, sex)
 186                                     VALUES  ({?}, {?}, {?}, {?}, {?}, {?}, {?})',
 187                              $hrid, $type, 0, 'active', $fullName, $directoryName, $lastname, $sex);
 188                 $uid = XDB::insertId();
 189                 XDB::execute('REPLACE INTO  account_profiles (uid, pid, perms)
 190                                     VALUES  ({?}, {?}, {?})',
 191                              $uid, $pid, 'owner');
 192             }
 193             $report[] = 'Promo 1920 ajoutée.';
 194             $next = 'view';
 195         } elseif ($action == 'view') {
 196             XDB::execute('CREATE OR REPLACE ALGORITHM=MERGE VIEW  fusionax_xorg_anciens AS
 197                                                           SELECT  p.pid, p.ax_id, pd.promo, pd.private_name, pd.public_name,
 198                                                                   pd.sort_name, pd.short_name, pd.directory_name
 199                                                             FROM  profiles        AS p
 200                                                       INNER JOIN  profile_display AS pd USING(pid)');
 201             $next = 'clean';
 202         } elseif ($action == 'clean') {
 203             // nettoyage du fichier temporaire
 204             exec('rm -Rf ' . $spoolpath);
 205             $report[] = 'Import finit.';
 206         }
 207         foreach($report as $t) {
 208             // affiche les lignes de report
 209             echo "$('#fusionax').append('" . $t . "<br/>');\n";
 210         }
 211         if (isset($next)) {
 212             // lance le prochain script s'il y en a un
 213             echo "$.getScript('fusionax/import/" . $next . "');";
 214         }
 215         // exit pour ne pas afficher la page template par défaut
 216         exit;
 217     }
 218
 219     function handler_view(&$page, $action = '')
 220     {
 221         $page->changeTpl('fusionax/view.tpl');
 222         if ($action == 'create') {
 223             XDB::execute('DROP VIEW IF EXISTS fusionax_deceased');
 224             XDB::execute('CREATE VIEW  fusionax_deceased AS
 225                                SELECT  p.pid, a.ax_id, pd.private_name, pd.promo, p.deathdate AS deces_xorg, a.Date_deces AS deces_ax
 226                                  FROM  profiles         AS p
 227                            INNER JOIN  profile_display  AS pd ON (p.pid = pd.pid)
 228                            INNER JOIN  fusionax_anciens AS a ON (a.ax_id = p.ax_id)
 229                                 WHERE  p.deathdate != a.Date_deces');
 230             XDB::execute('DROP VIEW IF EXISTS fusionax_promo');
 231             XDB::execute('CREATE VIEW  fusionax_promo AS
 232                                SELECT  p.pid, p.ax_id, pd.private_name, pd.promo, pe.entry_year AS promo_etude_xorg,
 233                                        f.promotion_etude AS promo_etude_ax, pe.grad_year AS promo_sortie_xorg
 234                                  FROM  profiles          AS p
 235                            INNER JOIN  profile_display   AS pd ON (p.pid = pd.pid)
 236                            INNER JOIN  profile_education AS pe ON (p.pid = pe.pid)
 237                            INNER JOIN  fusionax_anciens  AS f  ON (p.ax_id = f.ax_id)
 238                                 WHERE  pd.promo != CONCAT(\'X\', f.promotion_etude)
 239                                        AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)');
 240             $page->trigSuccess('Les VIEW ont bien été créées.');
 241         }
 242     }
 243
 244     /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */
 245     private static function clear_wrong_in_xorg($pid)
 246     {
 247         $res = XDB::execute('UPDATE  fusionax_xorg_anciens
 248                                 SET  ax_id = NULL
 249                               WHERE  pid = {?}', $pid);
 250         if (!$res) {
 251             return 0;
 252         }
 253         return XDB::affectedRows() / 2;
 254     }
 255
 256     /* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX
 257      * (mises à part les promo 1921 et 1923 qui ne figurent pas dans les données de l'AX)*/
 258     private static function find_wrong_in_xorg($limit = 10)
 259     {
 260         return XDB::iterator('SELECT  u.promo, u.pid, u.private_name
 261                                 FROM  fusionax_xorg_anciens AS u
 262                                WHERE  NOT EXISTS (SELECT  *
 263                                                     FROM  fusionax_anciens AS f
 264                                                    WHERE  f.ax_id = u.ax_id)
 265                                       AND u.ax_id IS NOT NULL AND promo != \'X1921\' AND promo != \'X1923\'');
 266     }
 267
 268     /** Lier les identifiants d'un ancien dans les deux annuaires
 269      * @param user_id identifiant dans l'annuaire X.org
 270      * @param matricule_ax identifiant dans l'annuaire de l'AX
 271      * @return 0 si la liaison a échoué, 1 sinon
 272      */
 273     private static function link_by_ids($pid, $ax_id)
 274     {
 275         $res = XDB::execute('UPDATE  fusionax_import       AS i
 276                          INNER JOIN  fusionax_xorg_anciens AS u
 277                                 SET  u.ax_id = i.ax_id,
 278                                      i.pid = u.pid,
 279                                      i.date_match_id = NOW()
 280                               WHERE  i.ax_id = {?} AND u.pid = {?}
 281                                      AND (u.ax_id != {?} OR u.ax_id IS NULL
 282                                           OR i.pid != {?} OR i.pid IS NULL)',
 283                             $ax_id, $pid, $ax_id, $pid);
 284         if (!$res) {
 285             return 0;
 286         }
 287         return XDB::affectedRows() / 2;
 288     }
 289
 290     /** Recherche automatique d'anciens à lier entre les deux annuaires
 291      * @param limit nombre d'anciens à trouver au max
 292      * @param sure si true, ne trouve que des anciens qui sont quasi sûrs
 293      * @return un XOrgDBIterator sur les entrées avec display_name, promo,
 294      *   pid, ax_id et display_name_ax
 295      */
 296     private static function find_easy_to_link($limit = 10, $sure = false)
 297     {
 298         $easy_to_link = XDB::iterator("
 299         SELECT  u.private_name, u.promo, u.pid, ax.ax_id,
 300                 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X', ax.promotion_etude, ')') AS display_name_ax,
 301                 COUNT(*) AS nbMatches
 302           FROM  fusionax_anciens      AS ax
 303     INNER JOIN  fusionax_import       AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
 304      LEFT JOIN  fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
 305                                                AND u.promo = CONCAT('X', ax.promotion_etude)
 306                                                AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
 307                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
 308                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name))
 309       GROUP BY  u.pid
 310         HAVING  u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : ''));
 311         if ($easy_to_link->total() > 0 || $sure) {
 312             return $easy_to_link;
 313         }
 314         return XDB::iterator("
 315         SELECT  u.private_name, u.promo, u.pid, ax.ax_id,
 316                 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X', ax.promotion_etude, ')') AS display_name_ax,
 317                 COUNT(*) AS nbMatches
 318           FROM  fusionax_anciens      AS ax
 319     INNER JOIN  fusionax_import       AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
 320      LEFT JOIN  fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
 321                                                AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
 322                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
 323                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name)
 324                                                AND u.promo < CONCAT('X', ax.promotion_etude + 2)
 325                                                AND u.promo > CONCAT('X', ax.promotion_etude - 2))
 326       GROUP BY  u.pid
 327         HAVING  u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : ''));
 328     }
 329
 330     /** Module de mise en correspondance les ids */
 331     function handler_ids(&$page, $part = 'main', $pid = null, $ax_id = null)
 332     {
 333         $nbToLink = 100;
 334         $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple');
 335
 336         if ($part == 'missingInAX') {
 337             // locate all persons from this database that are not in AX's
 338             $page->changeTpl('fusionax/idsMissingInAx.tpl');
 339             $missingInAX = XDB::iterator('SELECT  promo, pid, private_name
 340                                             FROM  fusionax_xorg_anciens
 341                                            WHERE  ax_id IS NULL');
 342             $page->assign('missingInAX', $missingInAX);
 343             return;
 344         }
 345         if ($part == 'missingInXorg') {
 346             // locate all persons from AX's database that are not here
 347             $page->changeTpl('fusionax/idsMissingInXorg.tpl');
 348             $missingInXorg = XDB::iterator("SELECT  CONCAT(a.prenom, ' ', a.Nom_usuel) AS private_name,
 349                                                     a.promotion_etude AS promo, a.ax_id
 350                                               FROM  fusionax_import
 351                                         INNER JOIN  fusionax_anciens AS a USING (ax_id)
 352                                              WHERE  fusionax_import.pid IS NULL");
 353             $page->assign('missingInXorg', $missingInXorg);
 354             return;
 355         }
 356         if ($part == 'wrongInXorg') {
 357             // locate all persons from Xorg database that have a bad AX id
 358             $page->changeTpl('fusionax/idswrongInXorg.tpl');
 359             $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
 360             $page->assign('wrongInXorg', $wrongInXorg);
 361             return;
 362         }
 363         if ($part == 'cleanwronginxorg') {
 364             $linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink);
 365             while ($l = $linksToDo->next()) {
 366                 FusionAxModule::clear_wrong_in_xorg($l['pid']);
 367             }
 368             pl_redirect('fusionax/ids/wrongInXorg');
 369         }
 370         if ($part == 'lier') {
 371             if (Post::has('user_id') && Post::has('matricule_ax')) {
 372                 FusionAxModule::link_by_ids(Post::i('pid'), Post::v('ax_id'));
 373             }
 374         }
 375         if ($part == 'link') {
 376             FusionAxModule::link_by_ids($pid, $ax_id);
 377             exit;
 378         }
 379         if ($part == 'linknext') {
 380             $linksToDo = FusionAxModule::find_easy_to_link($nbToLink);
 381             while ($l = $linksToDo->next()) {
 382                 FusionAxModule::link_by_ids($l['pid'], $l['ax_id']);
 383             }
 384             pl_redirect('fusionax/ids#autolink');
 385         }
 386         if ($part == 'linkall') {
 387             $linksToDo = FusionAxModule::find_easy_to_link(0);
 388             while ($l = $linksToDo->next()) {
 389                 FusionAxModule::link_by_ids($l['pid'], $l['ax_id']);
 390             }
 391         }
 392         {
 393             $page->changeTpl('fusionax/ids.tpl');
 394             $missingInAX = XDB::query('SELECT  COUNT(*)
 395                                          FROM  fusionax_xorg_anciens
 396                                         WHERE  ax_id IS NULL');
 397             if ($missingInAX) {
 398                 $page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
 399             }
 400             $missingInXorg = XDB::query('SELECT  COUNT(*)
 401                                            FROM  fusionax_import
 402                                           WHERE  pid IS NULL');
 403             if ($missingInXorg) {
 404                 $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
 405             }
 406             $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
 407             if ($wrongInXorg->total() > 0) {
 408                 $page->assign('wrongInXorg', $wrongInXorg->total());
 409             }
 410             $easyToLink = FusionAxModule::find_easy_to_link($nbToLink);
 411             if ($easyToLink->total() > 0) {
 412                 $page->assign('nbMatch', $easyToLink->total());
 413                 $page->assign('easyToLink', $easyToLink);
 414             }
 415         }
 416     }
 417
 418     function handler_deceased(&$page, $action = '')
 419     {
 420         if ($action == 'updateXorg') {
 421             XDB::execute('UPDATE  fusionax_deceased
 422                              SET  deces_xorg = deces_ax
 423                            WHERE  deces_xorg = "0000-00-00"');
 424         }
 425         if ($action == 'updateAX') {
 426             XDB::execute('UPDATE  fusionax_deceased
 427                              SET  deces_ax = deces_xorg
 428                            WHERE  deces_ax = "0000-00-00"');
 429         }
 430         if ($action == 'update') {
 431             if (Post::has('pid') && Post::has('date')) {
 432                 XDB::execute('UPDATE  fusionax_deceased
 433                                  SET  deces_ax = {?}, deces_xorg = {?}
 434                                WHERE  pid = {?}',
 435                              Post::v('date'), Post::v('date'), Post::i('pid'));
 436             }
 437         }
 438         $page->changeTpl('fusionax/deceased.tpl');
 439         // deceased
 440         $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
 441         $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
 442         $res = XDB::iterator('SELECT  pid, ax_id, promo, private_name, deces_ax
 443                                 FROM  fusionax_deceased
 444                                WHERE  deces_xorg = "0000-00-00"
 445                                LIMIT  10');
 446         $page->assign('nbDeceasedMissingInXorg', $res->total());
 447         $page->assign('deceasedMissingInXorg', $res);
 448         $res = XDB::iterator('SELECT  pid, ax_id, promo, private_name, deces_xorg
 449                                 FROM  fusionax_deceased
 450                                WHERE  deces_ax = "0000-00-00"
 451                                LIMIT  10');
 452         $page->assign('nbDeceasedMissingInAX', $res->total());
 453         $page->assign('deceasedMissingInAX', $res);
 454         $res = XDB::iterator('SELECT  pid, ax_id, promo, private_name, deces_xorg, deces_ax
 455                                 FROM  fusionax_deceased
 456                                WHERE  deces_xorg != "0000-00-00" AND deces_ax != "0000-00-00"');
 457         $page->assign('nbDeceasedDifferent', $res->total());
 458         $page->assign('deceasedDifferent', $res);
 459     }
 460
 461     function handler_promo(&$page, $action = '')
 462     {
 463         $page->changeTpl('fusionax/promo.tpl');
 464         $res = XDB::iterator('SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 465                                 FROM  fusionax_promo
 466                                WHERE  !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg)
 467                                       AND !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg)
 468                                       AND !(promo_etude_ax = promo_etude_xorg + 1)
 469                             ORDER BY  promo_etude_xorg');
 470         $nbMissmatchingPromos = $res->total();
 471         $page->assign('nbMissmatchingPromos', $res->total());
 472         $page->assign('missmatchingPromos', $res);
 473
 474         $res = XDB::iterator('SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 475                                 FROM  fusionax_promo
 476                                WHERE  promo_etude_ax = promo_etude_xorg + 1
 477                             ORDER BY  promo_etude_xorg');
 478         $nbMissmatchingPromos += $res->total();
 479         $page->assign('nbMissmatchingPromos1', $res->total());
 480         $page->assign('missmatchingPromos1', $res);
 481
 482         $res = XDB::iterator('SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 483                                 FROM  fusionax_promo
 484                                WHERE  promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg
 485                             ORDER BY  promo_etude_xorg');
 486         $nbMissmatchingPromos += $res->total();
 487         $page->assign('nbMissmatchingPromos2', $res->total());
 488         $page->assign('missmatchingPromos2', $res);
 489
 490         $res = XDB::iterator('SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 491                                 FROM  fusionax_promo
 492                                WHERE  promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg
 493                             ORDER BY  promo_etude_xorg');
 494         $nbMissmatchingPromos += $res->total();
 495         $page->assign('nbMissmatchingPromos3', $res->total());
 496         $page->assign('missmatchingPromos3', $res);
 497
 498         $page->assign('nbMissmatchingPromosTotal', $nbMissmatchingPromos);
 499     }
 500
 501     function handler_names(&$page, $action = '')
 502     {
 503         $page->changeTpl('fusionax/names.tpl');
 504
 505         $res = XDB::query('SELECT  COUNT(*)
 506                              FROM  fusionax_anciens AS f
 507                        INNER JOIN  profiles         AS p    ON (f.ax_id = p.ax_id)');
 508         $page->assign('total', $res->fetchOneCell());
 509
 510         // To be checked:
 511         // | lastname           |  1 |
 512         // | lastname_marital   |  2 |
 513         // | lastname_ordinary  |  3 |
 514         // | firstname          |  4 |
 515         // | firstname_ordinary |  7 |
 516         // | firstname_other    |  8 |
 517         // | name_other         |  9 |
 518         // | name_ini           | 10 |
 519         // | firstname_ini      | 11 |
 520         $res = XDB::query("SELECT  COUNT(*)
 521                              FROM  fusionax_anciens AS f
 522                        INNER JOIN  profiles         AS p   ON (f.ax_id = p.ax_id)
 523                         LEFT JOIN  profile_name     AS pnp ON (p.pid = pnp.pid AND pnp.typeid = 1)
 524                         LEFT JOIN  profile_name     AS pnm ON (p.pid = pnm.pid AND pnm.typeid = 2)
 525                         LEFT JOIN  profile_name     AS pno ON (p.pid = pno.pid AND pno.typeid = 3)
 526                         LEFT JOIN  profile_name     AS pne ON (p.pid = pne.pid AND pne.typeid = 9)
 527                         LEFT JOIN  profile_name     AS pni ON (p.pid = pni.pid AND pni.typeid = 10)
 528                             WHERE  IF(f.partic_patro, CONCAT(f.partic_patro, CONCAT(' ', f.Nom_patronymique)), f.Nom_patronymique) NOT IN (pnp.name, pno.name, pnm.name, pne.name, pni.name)
 529                                    OR IF(f.partic_nom, CONCAT(f.partic_nom, CONCAT(' ', f.Nom_usuel)), f.Nom_usuel) NOT IN (pnp.name, pno.name, pnm.name, pne.name, pni.name)
 530                                    OR f.Nom_complet NOT IN (pnp.name, pno.name, pnm.name, pne.name, pni.name)");
 531         $page->assign('lastnameIssues', $res->fetchOneCell());
 532
 533         $res = XDB::query('SELECT  COUNT(*)
 534                              FROM  fusionax_anciens AS f
 535                        INNER JOIN  profiles         AS p   ON (f.ax_id = p.ax_id)
 536                         LEFT JOIN  profile_name     AS pnf ON (p.pid = pnf.pid AND pnf.typeid = 4)
 537                         LEFT JOIN  profile_name     AS pno ON (p.pid = pno.pid AND pno.typeid = 7)
 538                         LEFT JOIN  profile_name     AS pne ON (p.pid = pne.pid AND pne.typeid = 8)
 539                         LEFT JOIN  profile_name     AS pni ON (p.pid = pni.pid AND pni.typeid = 11)
 540                             WHERE  f.prenom NOT IN (pnf.name, pno.name, pne.name, pni.name)');
 541         $page->assign('firstnameIssues', $res->fetchOneCell());
 542
 543     }
 544 }
 545
 546 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 547 ?>