modules/fusionax.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2014 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         if (Platal::globals()->merge->state == 'pending') {
  37             return array(
  38                 'fusionax'                  => $this->make_hook('index',    AUTH_PASSWD, 'admin'),
  39                 'fusionax/import'           => $this->make_hook('import',   AUTH_PASSWD, 'admin'),
  40                 'fusionax/view'             => $this->make_hook('view',     AUTH_PASSWD, 'admin'),
  41                 'fusionax/ids'              => $this->make_hook('ids',      AUTH_PASSWD, 'admin'),
  42                 'fusionax/deceased'         => $this->make_hook('deceased', AUTH_PASSWD, 'admin'),
  43                 'fusionax/promo'            => $this->make_hook('promo',    AUTH_PASSWD, 'admin'),
  44                 'fusionax/names'            => $this->make_hook('names',    AUTH_PASSWD, 'admin'),
  45                 'fusionax/edu'              => $this->make_hook('edu',      AUTH_PASSWD, 'admin'),
  46                 'fusionax/corps'            => $this->make_hook('corps',    AUTH_PASSWD, 'admin')
  47             );
  48         } elseif (Platal::globals()->merge->state == 'done') {
  49             return array(
  50                 'fusionax'                  => $this->make_hook('index',            AUTH_PASSWD, 'admin,edit_directory'),
  51                 'fusionax/issues'           => $this->make_hook('issues',           AUTH_PASSWD, 'admin,edit_directory'),
  52                 'fusionax/issues/deathdate' => $this->make_hook('issues_deathdate', AUTH_PASSWD, 'admin,edit_directory'),
  53                 'fusionax/issues/promo'     => $this->make_hook('issues_promo',     AUTH_PASSWD, 'admin,edit_directory'),
  54             );
  55         }
  56     }
  57
  58
  59     function handler_index($page)
  60     {
  61         if (Platal::globals()->merge->state == 'pending') {
  62             $page->changeTpl('fusionax/index.tpl');
  63         } elseif (Platal::globals()->merge->state == 'done') {
  64             $issueList = array(
  65                 'name'      => 'noms',
  66                 'job'       => 'emplois',
  67                 'address'   => 'adresses',
  68                 'promo'     => 'promotions',
  69                 'deathdate' => 'dates de décès',
  70                 'phone'     => 'téléphones',
  71                 'education' => 'formations',
  72             );
  73             $issues = XDB::rawFetchOneAssoc("SELECT  COUNT(*) AS total,
  74                                                      SUM(FIND_IN_SET('name', issues))      DIV 1 AS name,
  75                                                      SUM(FIND_IN_SET('job', issues))       DIV 2 AS job,
  76                                                      SUM(FIND_IN_SET('address', issues))   DIV 3 AS address,
  77                                                      SUM(FIND_IN_SET('promo', issues))     DIV 4 AS promo,
  78                                                      SUM(FIND_IN_SET('deathdate', issues)) DIV 5 AS deathdate,
  79                                                      SUM(FIND_IN_SET('phone', issues))     DIV 6 AS phone,
  80                                                      SUM(FIND_IN_SET('education', issues)) DIV 7 AS education
  81                                                FROM  profile_merge_issues
  82                                               WHERE  issues IS NOT NULL OR issues != ''");
  83             $page->changeTpl('fusionax/issues.tpl');
  84             $page->assign('issues', $issues);
  85             $page->assign('issueList', $issueList);
  86         }
  87     }
  88
  89     /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
  90     function handler_import($page, $action = 'index', $file = '')
  91     {
  92         global $globals;
  93         if ($action == 'index') {
  94             $page->changeTpl('fusionax/import.tpl');
  95             return;
  96         }
  97
  98         // toutes les actions sont faites en ajax en utilisant jquery
  99         header('Content-type: text/javascript; charset=utf-8');
 100
 101         // log des actions
 102         $report = array();
 103
 104         $modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/';
 105         $spoolpath = realpath(dirname(__FILE__) . '/../spool/fusionax/') . '/';
 106
 107         if ($action == 'launch') {
 108             if ($file == '') {
 109                 $report[] = 'Nom de fichier non renseigné.';
 110             } elseif (!file_exists(dirname(__FILE__) . '/../spool/fusionax/' . $file)) {
 111                 $report[] = 'Le fichier ne se situe pas au bon endroit.';
 112             } else {
 113                 // séparation de l'archive en fichiers par tables
 114                 $file = $spoolpath . $file;
 115                 // Split export into specialised files
 116                 exec('grep "^AD" ' . $file . ' > ' . $spoolpath . 'Adresses.txt');
 117                 exec('grep "^AN" ' . $file . ' > ' . $spoolpath . 'Anciens.txt');
 118                 exec('grep "^FO.[0-9]\{4\}[MD][0-9]\{3\}.Etudiant" ' . $file . ' > ' . $spoolpath . 'Formations_MD.txt');
 119                 exec('grep "^FO.[0-9]\{4\}[MD][0-9]\{3\}.Doct. de" ' . $file . ' >> ' . $spoolpath . 'Formations_MD.txt');
 120                 exec('grep "^FO" ' . $file . ' > ' . $spoolpath . 'Formations.txt');
 121                 exec('grep "^AC" ' . $file . ' > ' . $spoolpath . 'Activites.txt');
 122                 exec('grep "^EN" ' . $file . ' > ' . $spoolpath . 'Entreprises.txt');
 123                 exec($modulepath . 'formation.pl');
 124                 exec('mv -f ' . $spoolpath . 'Formations_out.txt ' . $spoolpath . 'Formations.txt');
 125                 exec('mv -f ' . $spoolpath . 'Formations_MD_out.txt ' . $spoolpath . 'Formations_MD.txt');
 126                 $report[] = 'Fichier parsé.';
 127                 $report[] = 'Import dans la base en cours...';
 128                 XDB::execute("UPDATE  profiles
 129                                  SET  ax_id = NULL
 130                                WHERE  ax_id = ''");
 131                 $next = 'integrateSQL';
 132             }
 133         } elseif ($action == 'integrateSQL') {
 134             // intégration des données dans la base MySQL
 135             // liste des fichiers sql à exécuter
 136             $filesSQL = array(
 137                 0 => 'Activites.sql',
 138                 1 => 'Adresses.sql',
 139                 2 => 'Anciens.sql',
 140                 3 => 'Formations.sql',
 141                 4 => 'Entreprises.sql',
 142                 5 => 'Formations_MD.sql'
 143             );
 144             if ($file != '') {
 145                 // récupère le contenu du fichier sql
 146                 $queries = explode(';', file_get_contents($modulepath . $filesSQL[$file]));
 147                 $db = mysqli_init();
 148                 $db->options(MYSQLI_OPT_LOCAL_INFILE, true);
 149                 $db->real_connect($globals->dbhost, $globals->dbuser, $globals->dbpwd, $globals->dbdb);
 150                 $db->autocommit(true);
 151                 $db->set_charset($globals->dbcharset);
 152                 foreach ($queries as $q) {
 153                     if (trim($q)) {
 154                         // coupe le fichier en requêtes individuelles
 155                         if (substr($q, 0, 2) == '--') {
 156                             // affiche les commentaires dans le report
 157                             $lines = explode("\n", $q);
 158                             $l = $lines[0];
 159                             $report[] = addslashes($l);
 160                         }
 161                         // exécute la requête
 162                         $res = $db->query(str_replace('{?}', $spoolpath, $q));
 163                         if ($res === false) {
 164                             throw new XDBException($q, $db->error);
 165                         }
 166                     }
 167                 }
 168                 $db->close();
 169                 // trouve le prochain fichier à exécuter
 170                 $nextfile = $file + 1;
 171             } else {
 172                 $nextfile = 0;
 173             }
 174             if ($nextfile > 5) {
 175                 // tous les fichiers ont été exécutés, on passe à l'étape suivante
 176                 $next = 'adds1920';
 177             } else {
 178                 // on passe au fichier suivant
 179                 $next = 'integrateSQL/' . $nextfile;
 180             }
 181         } elseif ($action == 'adds1920') {
 182             // Adds promotion 1920 from AX db.
 183             $report[] = 'Ajout de la promotion 1920';
 184             $res = XDB::iterator('SELECT  prenom, Nom_complet, ax_id
 185                                     FROM  fusionax_anciens
 186                                    WHERE  promotion_etude = 1920;');
 187
 188             $eduSchools = DirEnum::getOptions(DirEnum::EDUSCHOOLS);
 189             $eduSchools = array_flip($eduSchools);
 190             $eduDegrees = DirEnum::getOptions(DirEnum::EDUDEGREES);
 191             $eduDegrees = array_flip($eduDegrees);
 192             $degreeid = $eduDegrees[Profile::DEGREE_X];
 193             $entry_year = 1920;
 194             $grad_year = 1923;
 195             $promo = 'X1920';
 196             $hrpromo = '1920';
 197             $sex = 'male';
 198             $xorgId = 19200000;
 199             $type = 'x';
 200
 201             while ($new = $res->next()) {
 202                 $firstname = $new['prenom'];
 203                 $lastname = $new['Nom_complet'];
 204                 $ax_id = $new['ax_id'];
 205                 $hrid = User::makeHrid($firstname, $lastname, $hrpromo);
 206                 $res1 = XDB::query('SELECT  COUNT(*)
 207                                       FROM  accounts
 208                                      WHERE  hruid = {?}', $hrid);
 209                 $res2 = XDB::query('SELECT  COUNT(*)
 210                                       FROM  profiles
 211                                      WHERE  hrpid = {?}', $hrid);
 212                 if (is_null($hrid) || $res1->fetchOneCell() > 0 || $res2->fetchOneCell() > 0) {
 213                     $report[] = $ax_id . ' non ajouté';
 214                 }
 215                 $fullName = $firstname . ' ' . $lastname;
 216                 $directoryName = $lastname . ' ' . $firstname;
 217                 ++$xorgId;
 218
 219                 XDB::execute('INSERT INTO  profiles (hrpid, xorg_id, ax_id, sex)
 220                                    VALUES  ({?}, {?}, {?}, {?})',
 221                              $hrid, $xorgId, $ax_id, $sex);
 222                 $pid = XDB::insertId();
 223                 XDB::execute('INSERT INTO  profile_public_names (pid, lastname_initial, firstname_initial, lastname_main, firstname_main)
 224                                    VALUES  ({?}, {?}, {?}, {?}, {?})',
 225                              $pid, $lastname, $firstname, $lastname, $firstname);
 226                 XDB::execute('INSERT INTO  profile_display (pid, yourself, public_name, private_name,
 227                                                             directory_name, short_name, sort_name, promo)
 228                                    VALUES  ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
 229                              $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo);
 230                 XDB::execute('INSERT INTO  profile_education (pid, eduid, degreeid, entry_year, grad_year, flags)
 231                                    VALUES  ({?}, {?}, {?}, {?}, {?}, {?})',
 232                              $pid, $eduSchools[Profile::EDU_X], $degreeid, $entry_year, $grad_year, 'primary');
 233                 XDB::execute('INSERT INTO  accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, lastname, firstname, sex)
 234                                    VALUES  ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
 235                              $hrid, $type, 0, 'pending', $fullName, $directoryName, $firstname, $lastname, $firstname, $sex);
 236                 $uid = XDB::insertId();
 237                 XDB::execute('INSERT INTO  account_profiles (uid, pid, perms)
 238                                    VALUES  ({?}, {?}, {?})',
 239                              $uid, $pid, 'owner');
 240             }
 241             $report[] = 'Promo 1920 ajoutée.';
 242             $next = 'adds2011';
 243         } elseif ($action == 'adds2011') {
 244             // Adds promotion 2011 from AX db.
 245             $report[] = 'Ajout des élèves manquant de la promotion 2011';
 246             $res = XDB::iterator("SELECT  prenom, Nom_complet, ax_id, Civilite
 247                                     FROM  fusionax_anciens
 248                                    WHERE  promotion_etude = 2011 AND groupe_promo = 'X'
 249                                           AND NOT EXISTS (SELECT  1
 250                                                             FROM  profiles
 251                                                            WHERE  profiles.ax_id = fusionax_anciens.ax_id)");
 252
 253             $eduSchools = DirEnum::getOptions(DirEnum::EDUSCHOOLS);
 254             $eduSchools = array_flip($eduSchools);
 255             $eduDegrees = DirEnum::getOptions(DirEnum::EDUDEGREES);
 256             $eduDegrees = array_flip($eduDegrees);
 257             $degreeid = $eduDegrees[Profile::DEGREE_X];
 258             $entry_year = 2011;
 259             $grad_year = 2014;
 260             $promo = 'X2011';
 261             $hrpromo = '2011';
 262             $type = 'x';
 263
 264             while ($new = $res->next()) {
 265                 $firstname = $new['prenom'];
 266                 $lastname = $new['Nom_complet'];
 267                 $ax_id = $new['ax_id'];
 268                 $civilite = $new['Civilite'];
 269                 $hrid = User::makeHrid($firstname, $lastname, $hrpromo);
 270                 $res1 = XDB::query('SELECT  COUNT(*)
 271                                       FROM  accounts
 272                                      WHERE  hruid = {?}', $hrid);
 273                 $res2 = XDB::query('SELECT  COUNT(*)
 274                                       FROM  profiles
 275                                      WHERE  hrpid = {?}', $hrid);
 276                 if (is_null($hrid) || $res1->fetchOneCell() > 0 || $res2->fetchOneCell() > 0) {
 277                     $report[] = $ax_id . ' non ajouté';
 278                 }
 279                 $fullName = $firstname . ' ' . $lastname;
 280                 $directoryName = $lastname . ' ' . $firstname;
 281                 if ($civilite == 'M') {
 282                     $sex = 'male';
 283                 } else {
 284                     $sex = 'female';
 285                 }
 286
 287                 XDB::execute('INSERT INTO  profiles (hrpid, ax_id, sex, title)
 288                                    VALUES  ({?}, {?}, {?}, {?})',
 289                              $hrid, $ax_id, $sex, $civilite);
 290                 $pid = XDB::insertId();
 291                 XDB::execute('INSERT INTO  profile_public_names (pid, lastname_initial, firstname_initial, lastname_main, firstname_main)
 292                                    VALUES  ({?}, {?}, {?}, {?}, {?})',
 293                              $pid, $lastname, $firstname, $lastname, $firstname);
 294                 XDB::execute('INSERT INTO  profile_display (pid, yourself, public_name, private_name,
 295                                                             directory_name, short_name, sort_name, promo)
 296                                    VALUES  ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
 297                              $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo);
 298                 XDB::execute('INSERT INTO  profile_education (pid, eduid, degreeid, entry_year, grad_year, flags)
 299                                    VALUES  ({?}, {?}, {?}, {?}, {?}, {?})',
 300                              $pid, $eduSchools[Profile::EDU_X], $degreeid, $entry_year, $grad_year, 'primary');
 301                 XDB::execute('INSERT INTO  accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, lastname, firstname, sex)
 302                                    VALUES  ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
 303                              $hrid, $type, 0, 'pending', $fullName, $directoryName, $firstname, $lastname, $firstname, $sex);
 304                 $uid = XDB::insertId();
 305                 XDB::execute('INSERT INTO  account_profiles (uid, pid, perms)
 306                                    VALUES  ({?}, {?}, {?})',
 307                              $uid, $pid, 'owner');
 308             }
 309             $report[] = 'Promo 2011 ajoutée.';
 310
 311             $next = 'view';
 312         } elseif ($action == 'view') {
 313             XDB::execute('CREATE OR REPLACE ALGORITHM=MERGE VIEW  fusionax_xorg_anciens AS
 314                                                           SELECT  p.pid, p.ax_id, pd.promo, pd.private_name, pd.public_name,
 315                                                                   pd.sort_name, pd.short_name, pd.directory_name
 316                                                             FROM  profiles        AS p
 317                                                       INNER JOIN  profile_display AS pd ON (pd.pid = p.pid)');
 318             $next = 'clean';
 319         } elseif ($action == 'clean') {
 320             // nettoyage du fichier temporaire
 321             //exec('rm -Rf ' . $spoolpath);
 322             $report[] = 'Import finit.';
 323         }
 324         foreach($report as $t) {
 325             // affiche les lignes de report
 326             echo "$('#fusionax').append('" . $t . "<br/>');\n";
 327         }
 328         if (isset($next)) {
 329             // lance le prochain script s'il y en a un
 330             echo "$.getScript('fusionax/import/" . $next . "');";
 331         }
 332         // exit pour ne pas afficher la page template par défaut
 333         exit;
 334     }
 335
 336     function handler_view($page, $action = '')
 337     {
 338         $page->changeTpl('fusionax/view.tpl');
 339         if ($action == 'create') {
 340             XDB::execute('DROP VIEW IF EXISTS fusionax_deceased');
 341             XDB::execute("CREATE VIEW  fusionax_deceased AS
 342                                SELECT  p.pid, a.ax_id, pd.private_name, pd.promo, p.deathdate AS deces_xorg, a.Date_deces AS deces_ax
 343                                  FROM  profiles         AS p
 344                            INNER JOIN  profile_display  AS pd ON (p.pid = pd.pid)
 345                            INNER JOIN  fusionax_anciens AS a ON (a.ax_id = p.ax_id)
 346                                 WHERE  p.deathdate != a.Date_deces OR (p.deathdate IS NULL AND a.Date_deces != '0000-00-00')");
 347             XDB::execute('DROP VIEW IF EXISTS fusionax_promo');
 348             XDB::execute("CREATE VIEW  fusionax_promo AS
 349                                SELECT  p.pid, p.ax_id, pd.private_name, pd.promo, pe.entry_year AS promo_etude_xorg, f.groupe_promo,
 350                                        f.promotion_etude AS promo_etude_ax, pe.grad_year AS promo_sortie_xorg
 351                                  FROM  profiles          AS p
 352                            INNER JOIN  profile_display   AS pd ON (p.pid = pd.pid)
 353                            INNER JOIN  profile_education AS pe ON (p.pid = pe.pid AND FIND_IN_SET('primary', pe.flags))
 354                            INNER JOIN  fusionax_anciens  AS f  ON (p.ax_id = f.ax_id)
 355                                 WHERE  (f.groupe_promo = 'X' AND pd.promo != CONCAT('X', f.promotion_etude)
 356                                        AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)
 357                                        AND !(f.promotion_etude = pe.entry_year + 2 AND pe.grad_year = pe.entry_year + 5)
 358                                        AND f.promotion_etude != 0)
 359                                        OR (f.groupe_promo = 'D' AND f.promotion_etude != pe.grad_year)
 360                                        OR (f.groupe_promo = 'M' AND f.promotion_etude != pe.entry_year)
 361                              GROUP BY  p.pid");
 362             $page->trigSuccess('Les VIEW ont bien été créées.');
 363         }
 364     }
 365
 366     /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */
 367     private static function clear_wrong_in_xorg($pid)
 368     {
 369         $res = XDB::execute('UPDATE  fusionax_xorg_anciens
 370                                 SET  ax_id = NULL
 371                               WHERE  pid = {?}', $pid);
 372         if (!$res) {
 373             return 0;
 374         }
 375         return XDB::affectedRows() / 2;
 376     }
 377
 378     /* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX
 379      * (mises à part les promo 1921, 1922, 1923, 1924, 1925, 1927 qui ne figurent pas dans les données de l'AX)*/
 380     private static function find_wrong_in_xorg($limit = 10)
 381     {
 382         return XDB::iterator('SELECT  u.promo, u.pid, u.private_name
 383                                 FROM  fusionax_xorg_anciens AS u
 384                                WHERE  NOT EXISTS (SELECT  *
 385                                                     FROM  fusionax_anciens AS f
 386                                                    WHERE  f.ax_id = u.ax_id)
 387                                       AND u.ax_id IS NOT NULL AND promo NOT IN (\'X1921\', \'X1922\', \'X1923\', \'X1924\', \'X1925\', \'X1927\')');
 388     }
 389
 390     /** Lier les identifiants d'un ancien dans les deux annuaires
 391      * @param user_id identifiant dans l'annuaire X.org
 392      * @param matricule_ax identifiant dans l'annuaire de l'AX
 393      * @return 0 si la liaison a échoué, 1 sinon
 394      */
 395     private static function link_by_ids($pid, $ax_id)
 396     {
 397         $res = XDB::execute('UPDATE  fusionax_import       AS i
 398                          INNER JOIN  fusionax_xorg_anciens AS u
 399                                 SET  u.ax_id = i.ax_id,
 400                                      i.pid = u.pid,
 401                                      i.date_match_id = NOW()
 402                               WHERE  i.ax_id = {?} AND u.pid = {?}
 403                                      AND (u.ax_id != {?} OR u.ax_id IS NULL
 404                                           OR i.pid != {?} OR i.pid IS NULL)',
 405                             $ax_id, $pid, $ax_id, $pid);
 406         if (!$res) {
 407             return 0;
 408         }
 409         return XDB::affectedRows() / 2;
 410     }
 411
 412     /** Recherche automatique d'anciens à lier entre les deux annuaires
 413      * @param limit nombre d'anciens à trouver au max
 414      * @param sure si true, ne trouve que des anciens qui sont quasi sûrs
 415      * @return un XOrgDBIterator sur les entrées avec display_name, promo,
 416      *   pid, ax_id et display_name_ax
 417      */
 418     private static function find_easy_to_link($limit = 10, $sure = false)
 419     {
 420         $easy_to_link = XDB::iterator("
 421         SELECT  u.private_name, u.promo, u.pid, ax.ax_id,
 422                 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (', ax.groupe_promo, ax.promotion_etude, ')') AS display_name_ax,
 423                 COUNT(*) AS nbMatches
 424           FROM  fusionax_anciens      AS ax
 425     INNER JOIN  fusionax_import       AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
 426      LEFT JOIN  fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
 427                                                AND u.promo = CONCAT(ax.groupe_promo, ax.promotion_etude)
 428                                                AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
 429                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
 430                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name))
 431       GROUP BY  u.pid
 432         HAVING  u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : ''));
 433         if ($easy_to_link->total() > 0 || $sure) {
 434             return $easy_to_link;
 435         }
 436         return XDB::iterator("
 437         SELECT  u.private_name, u.promo, u.pid, ax.ax_id,
 438                 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (', ax.groupe_promo, ax.promotion_etude, ')') AS display_name_ax,
 439                 COUNT(*) AS nbMatches
 440           FROM  fusionax_anciens      AS ax
 441     INNER JOIN  fusionax_import       AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
 442      LEFT JOIN  fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
 443                                                AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
 444                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
 445                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name)
 446                                                AND u.promo < CONCAT(ax.groupe_promo, ax.promotion_etude + 2)
 447                                                AND u.promo > CONCAT(ax.groupe_promo, ax.promotion_etude - 2))
 448       GROUP BY  u.pid
 449         HAVING  u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : ''));
 450     }
 451
 452     /** Module de mise en correspondance les ids */
 453     function handler_ids($page, $part = 'main', $pid = null, $ax_id = null)
 454     {
 455         $nbToLink = 100;
 456         $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple');
 457
 458         if ($part == 'missingInAX') {
 459             // locate all persons from this database that are not in AX's
 460             $page->changeTpl('fusionax/idsMissingInAx.tpl');
 461             $missingInAX = XDB::iterator('SELECT  promo, pid, private_name
 462                                             FROM  fusionax_xorg_anciens
 463                                            WHERE  ax_id IS NULL
 464                                         ORDER BY  promo');
 465             $page->assign('missingInAX', $missingInAX);
 466             return;
 467         }
 468         if ($part == 'missingInXorg') {
 469             // locate all persons from AX's database that are not here
 470             $page->changeTpl('fusionax/idsMissingInXorg.tpl');
 471             $missingInXorg = XDB::iterator("SELECT  CONCAT(a.prenom, ' ', a.Nom_usuel) AS private_name,
 472                                                     CONCAT(a.groupe_promo, a.promotion_etude) AS promo, a.ax_id
 473                                               FROM  fusionax_import
 474                                         INNER JOIN  fusionax_anciens AS a USING (ax_id)
 475                                              WHERE  fusionax_import.pid IS NULL
 476                                           ORDER BY  promo");
 477             $page->assign('missingInXorg', $missingInXorg);
 478             return;
 479         }
 480         if ($part == 'wrongInXorg') {
 481             // locate all persons from Xorg database that have a bad AX id
 482             $page->changeTpl('fusionax/idswrongInXorg.tpl');
 483             $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
 484             $page->assign('wrongInXorg', $wrongInXorg);
 485             return;
 486         }
 487         if ($part == 'cleanwronginxorg') {
 488             $linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink);
 489             while ($l = $linksToDo->next()) {
 490                 FusionAxModule::clear_wrong_in_xorg($l['pid']);
 491             }
 492             pl_redirect('fusionax/ids/wrongInXorg');
 493         }
 494         if ($part == 'lier') {
 495             if (Post::has('user_id') && Post::has('matricule_ax')) {
 496                 FusionAxModule::link_by_ids(Post::i('pid'), Post::v('ax_id'));
 497             }
 498         }
 499         if ($part == 'link') {
 500             FusionAxModule::link_by_ids($pid, $ax_id);
 501             exit;
 502         }
 503         if ($part == 'linknext') {
 504             $linksToDo = FusionAxModule::find_easy_to_link($nbToLink);
 505             while ($l = $linksToDo->next()) {
 506                 FusionAxModule::link_by_ids($l['pid'], $l['ax_id']);
 507             }
 508             pl_redirect('fusionax/ids#autolink');
 509         }
 510         if ($part == 'linkall') {
 511             $linksToDo = FusionAxModule::find_easy_to_link(0);
 512             while ($l = $linksToDo->next()) {
 513                 FusionAxModule::link_by_ids($l['pid'], $l['ax_id']);
 514             }
 515         }
 516         {
 517             $page->changeTpl('fusionax/ids.tpl');
 518             $missingInAX = XDB::query('SELECT  COUNT(*)
 519                                          FROM  fusionax_xorg_anciens
 520                                         WHERE  ax_id IS NULL');
 521             if ($missingInAX) {
 522                 $page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
 523             }
 524             $missingInXorg = XDB::query('SELECT  COUNT(*)
 525                                            FROM  fusionax_import
 526                                           WHERE  pid IS NULL');
 527             if ($missingInXorg) {
 528                 $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
 529             }
 530             $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
 531             if ($wrongInXorg->total() > 0) {
 532                 $page->assign('wrongInXorg', $wrongInXorg->total());
 533             }
 534             $easyToLink = FusionAxModule::find_easy_to_link($nbToLink);
 535             if ($easyToLink->total() > 0) {
 536                 $page->assign('nbMatch', $easyToLink->total());
 537                 $page->assign('easyToLink', $easyToLink);
 538             }
 539         }
 540     }
 541
 542     function handler_deceased($page, $action = '')
 543     {
 544         if ($action == 'updateXorg') {
 545             XDB::execute('UPDATE  fusionax_deceased
 546                              SET  deces_xorg = deces_ax
 547                            WHERE  deces_xorg IS NULL');
 548         }
 549         if ($action == 'updateAX') {
 550             XDB::execute('UPDATE  fusionax_deceased
 551                              SET  deces_ax = deces_xorg
 552                            WHERE  deces_ax = "0000-00-00"');
 553         }
 554         if ($action == 'update') {
 555             if (Post::has('pid') && Post::has('date')) {
 556                 XDB::execute('UPDATE  fusionax_deceased
 557                                  SET  deces_ax = {?}, deces_xorg = {?}
 558                                WHERE  pid = {?}',
 559                              Post::v('date'), Post::v('date'), Post::i('pid'));
 560             }
 561         }
 562         $page->changeTpl('fusionax/deceased.tpl');
 563         // deceased
 564         $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
 565         $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
 566         $res = XDB::iterator('SELECT  pid, ax_id, promo, private_name, deces_ax
 567                                 FROM  fusionax_deceased
 568                                WHERE  deces_xorg IS NULL
 569                                LIMIT  10');
 570         $page->assign('nbDeceasedMissingInXorg', $res->total());
 571         $page->assign('deceasedMissingInXorg', $res);
 572         $res = XDB::iterator('SELECT  pid, ax_id, promo, private_name, deces_xorg
 573                                 FROM  fusionax_deceased
 574                                WHERE  deces_ax = "0000-00-00"
 575                                LIMIT  10');
 576         $page->assign('nbDeceasedMissingInAX', $res->total());
 577         $page->assign('deceasedMissingInAX', $res);
 578         $res = XDB::iterator('SELECT  pid, ax_id, promo, private_name, deces_xorg, deces_ax
 579                                 FROM  fusionax_deceased
 580                                WHERE  deces_xorg != "0000-00-00" AND deces_ax != "0000-00-00"');
 581         $page->assign('nbDeceasedDifferent', $res->total());
 582         $page->assign('deceasedDifferent', $res);
 583     }
 584
 585     function handler_promo($page, $action = '')
 586     {
 587         $page->changeTpl('fusionax/promo.tpl');
 588         $res = XDB::iterator("SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 589                                 FROM  fusionax_promo
 590                                WHERE  !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg)
 591                                       AND !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg)
 592                                       AND !(promo_etude_ax = promo_etude_xorg + 1) AND groupe_promo = 'X'
 593                             ORDER BY  promo_etude_xorg");
 594         $nbMissmatchingPromos = $res->total();
 595         $page->assign('nbMissmatchingPromos', $res->total());
 596         $page->assign('missmatchingPromos', $res);
 597
 598         $res = XDB::iterator("SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 599                                 FROM  fusionax_promo
 600                                WHERE  promo_etude_ax = promo_etude_xorg + 1 AND groupe_promo = 'X'
 601                             ORDER BY  promo_etude_xorg");
 602         $nbMissmatchingPromos += $res->total();
 603         $page->assign('nbMissmatchingPromos1', $res->total());
 604         $page->assign('missmatchingPromos1', $res);
 605
 606         $res = XDB::iterator("SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 607                                 FROM  fusionax_promo
 608                                WHERE  promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg AND groupe_promo = 'X'
 609                             ORDER BY  promo_etude_xorg");
 610         $nbMissmatchingPromos += $res->total();
 611         $page->assign('nbMissmatchingPromos2', $res->total());
 612         $page->assign('missmatchingPromos2', $res);
 613
 614         $res = XDB::iterator("SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 615                                 FROM  fusionax_promo
 616                                WHERE  promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg AND groupe_promo = 'X'
 617                             ORDER BY  promo_etude_xorg");
 618         $nbMissmatchingPromos += $res->total();
 619         $page->assign('nbMissmatchingPromos3', $res->total());
 620         $page->assign('missmatchingPromos3', $res);
 621
 622         $res = XDB::iterator("SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 623                                 FROM  fusionax_promo
 624                                WHERE  groupe_promo = 'M'
 625                             ORDER BY  promo_etude_xorg");
 626         $nbMissmatchingPromos += $res->total();
 627         $page->assign('nbMissmatchingPromosM', $res->total());
 628         $page->assign('missmatchingPromosM', $res);
 629
 630
 631         $res = XDB::iterator("SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 632                                 FROM  fusionax_promo
 633                                WHERE  groupe_promo = 'D'
 634                             ORDER BY  promo_etude_xorg");
 635         $nbMissmatchingPromos += $res->total();
 636         $page->assign('nbMissmatchingPromosD', $res->total());
 637         $page->assign('missmatchingPromosD', $res);
 638
 639         $page->assign('nbMissmatchingPromosTotal', $nbMissmatchingPromos);
 640     }
 641
 642     private function format($string)
 643     {
 644         return preg_replace('/(\s+|\-)/', '', $string);
 645     }
 646
 647     private function retrieve_firstnames()
 648     {
 649         $res = XDB::rawFetchAllAssoc('SELECT  p.pid, p.ax_id, p.hrpid,
 650                                               f.prenom, ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary
 651                                         FROM  fusionax_anciens     AS f
 652                                   INNER JOIN  profiles             AS p   ON (f.ax_id = p.ax_id)
 653                                   INNER JOIN  profile_public_names AS ppn ON (p.pid = ppn.pid)
 654                                        WHERE  f.prenom NOT IN (ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary)');
 655
 656         $issues = array();
 657         foreach ($res as $item) {
 658             if (!($item['firstname_ordinary'] != '' || $item['firstname_main'] != $item['firstname_initial'])) {
 659                 $ax = $this->format(mb_strtolower(replace_accent($item['prenom'])));
 660                 $xorg = $this->format(mb_strtolower(replace_accent($item['firstname_main'])));
 661                 if ($ax != $xorg) {
 662                     $issues[] = $item;
 663                 }
 664             }
 665         }
 666
 667         return $issues;
 668     }
 669
 670     function handler_names($page, $action = '', $csv = false)
 671     {
 672         $page->changeTpl('fusionax/names.tpl');
 673
 674         if ($action == 'first') {
 675             $res = $this->retrieve_firstnames();
 676             if ($csv) {
 677                 pl_cached_content_headers('text/x-csv', 'utf-8', 1, 'firstnames.csv');
 678
 679                 $csv = fopen('php://output', 'w');
 680                 fputcsv($csv,  array('pid', 'ax_id', 'hrpid', 'AX', 'initial', 'principal', 'ordinaire'), ';');
 681                 foreach ($res as $item) {
 682                     fputcsv($csv, $item, ';');
 683                 }
 684                 fclose($csv);
 685                 exit();
 686             } else {
 687                 $page->assign('firstnameIssues', $res);
 688             }
 689         } elseif ($action == 'last' || $action == 'last3' || $action == 'last2' || $action == 'last1') {
 690             $ax_patro = "(IF(f.partic_patro, CONCAT(f.partic_patro, CONCAT(' ', f.Nom_patronymique)), f.Nom_patronymique) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))";
 691             $ax_ordinary = "(IF(f.partic_nom, CONCAT(f.partic_nom, CONCAT(' ', f.Nom_usuel)), f.Nom_usuel) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))";
 692             $ax_full = "(f.Nom_complet NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))";
 693
 694             switch ($action) {
 695               case 'last':
 696                 $where = $ax_patro . ' OR ' . $ax_ordinary . ' OR ' . $ax_full;
 697                 break;
 698               case 'last3':
 699                 $where = $ax_patro . ' AND ' . $ax_ordinary . ' AND ' . $ax_full;
 700                 break;
 701               case 'last2':
 702                 $where = '(' . $ax_patro . ' AND ' . $ax_ordinary . ' AND NOT ' . $ax_full . ') OR ('
 703                        . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND ' . $ax_full . ') OR ('
 704                        . 'NOT ' . $ax_patro . ' AND ' . $ax_ordinary . ' AND ' . $ax_full . ')';
 705                 break;
 706               case 'last1':
 707                 $where = '(' . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND NOT ' . $ax_full . ') OR ('
 708                        . 'NOT ' . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND ' . $ax_full . ') OR ('
 709                        . 'NOT ' . $ax_patro . ' AND ' . $ax_ordinary . ' AND NOT ' . $ax_full . ')';
 710                 break;
 711             }
 712
 713             $res = XDB::rawFetchAllAssoc('SELECT  p.pid, p.ax_id, p.hrpid,
 714                                                   f.Nom_patronymique, f.Nom_usuel, f.Nom_complet,
 715                                                   ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary
 716                                             FROM  fusionax_anciens     AS f
 717                                       INNER JOIN  profiles             AS p   ON (f.ax_id = p.ax_id)
 718                                       INNER JOIN  profile_public_names AS ppn ON (p.pid = ppn.pid)
 719                                            WHERE  ' . $where);
 720
 721             if ($csv) {
 722                 function format($string)
 723                 {
 724                     $string = preg_replace('/\-/', ' ', $string);
 725                     return preg_replace('/\s+/', ' ', $string);
 726                 }
 727
 728
 729                 pl_cached_content_headers('text/x-csv', 'utf-8', 1, 'lastnames.csv');
 730
 731                 $csv = fopen('php://output', 'w');
 732                 fputcsv($csv,  array('pid', 'ax_id', 'hrpid', 'AX patro', 'AX usuel', 'AX complet', 'initial', 'principal', 'marital', 'ordinaire'), ';');
 733                 foreach ($res as $item) {
 734                     $ax = array(
 735                         'Nom_patronymique' => format(mb_strtolower(replace_accent($item['Nom_patronymique']))),
 736                         'Nom_usuel'        => format(mb_strtolower(replace_accent($item['Nom_usuel']))),
 737                         'Nom_complet'      => format(mb_strtolower(replace_accent($item['Nom_complet'])))
 738                     );
 739                     $xorg = array(
 740                         'lastname_initial'  => format(mb_strtolower(replace_accent($item['lastname_initial']))),
 741                         'lastname_main'     => format(mb_strtolower(replace_accent($item['lastname_main']))),
 742                         'lastname_ordinary' => format(mb_strtolower(replace_accent($item['lastname_ordinary'])))
 743                     );
 744
 745                     if (!in_array($ax['Nom_patronymique'], $xorg) || !in_array($ax['Nom_usuel'], $xorg) || !in_array($ax['Nom_complet'], $xorg)) {
 746                         fputcsv($csv, $item, ';');
 747                     }
 748                 }
 749                 fclose($csv);
 750                 exit();
 751             } else {
 752                 $page->assign('lastnameIssues', $res);
 753                 $page->assign('total', count($res));
 754                 $page->assign('issuesTypes', array(
 755                         'last'  => "1, 2 ou 3 noms de l'AX manquant",
 756                         'last1' => "1 nom de l'AX manquant",
 757                         'last2' => "2 noms de l'AX manquant",
 758                         'last3' => "3 noms de l'AX manquant"
 759                 ));
 760             }
 761         } else {
 762             $res = XDB::query('SELECT  COUNT(*)
 763                                  FROM  fusionax_anciens AS f
 764                            INNER JOIN  profiles         AS p    ON (f.ax_id = p.ax_id)');
 765             $page->assign('total', $res->fetchOneCell());
 766
 767             $res = XDB::rawFetchOneCell("SELECT  COUNT(*)
 768                                            FROM  fusionax_anciens     AS f
 769                                      INNER JOIN  profiles             AS p   ON (f.ax_id = p.ax_id)
 770                                      INNER JOIN  profile_public_names AS ppn ON (p.pid = ppn.pid)
 771                                           WHERE  IF(f.partic_patro, CONCAT(f.partic_patro, CONCAT(' ', f.Nom_patronymique)), f.Nom_patronymique) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary)
 772                                                  OR IF(f.partic_nom, CONCAT(f.partic_nom, CONCAT(' ', f.Nom_usuel)), f.Nom_usuel) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary)
 773                                                  OR f.Nom_complet NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary)");
 774             $page->assign('lastnameIssues', $res);
 775
 776             $res = XDB::rawFetchOneCell('SELECT  COUNT(*)
 777                                            FROM  fusionax_anciens     AS f
 778                                      INNER JOIN  profiles             AS p   ON (f.ax_id = p.ax_id)
 779                                      INNER JOIN  profile_public_names AS ppn ON (p.pid = ppn.pid)
 780                                           WHERE  f.prenom NOT IN (ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary)');
 781             $page->assign('firstnameIssues', count($this->retrieve_firstnames()));
 782         }
 783         $page->assign('action', $action);
 784     }
 785
 786     function handler_edu($page, $action = '')
 787     {
 788         $page->changeTpl('fusionax/education.tpl');
 789
 790         $missingEducation = XDB::rawIterator("SELECT  DISTINCT(f.Intitule_formation)
 791                                                 FROM  fusionax_formations AS f
 792                                                WHERE  f.Intitule_formation != '' AND NOT EXISTS (SELECT  *
 793                                                                                                    FROM  profile_education_enum AS e
 794                                                                                                   WHERE  f.Intitule_formation = e.name)");
 795         $missingDegree = XDB::rawIterator("SELECT  DISTINCT(f.Intitule_diplome)
 796                                              FROM  fusionax_formations AS f
 797                                             WHERE  f.Intitule_diplome != '' AND NOT EXISTS (SELECT  *
 798                                                                                               FROM  profile_education_degree_enum AS e
 799                                                                                              WHERE  f.Intitule_diplome = e.abbreviation)");
 800         $missingCouple = XDB::rawIterator("SELECT  DISTINCT(f.Intitule_formation) AS edu, f.Intitule_diplome AS degree, ee.id AS eduid, de.id AS degreeid
 801                                              FROM  fusionax_formations           AS f
 802                                        INNER JOIN  profile_education_enum        AS ee ON (f.Intitule_formation = ee.name)
 803                                        INNER JOIN  profile_education_degree_enum AS de ON (f.Intitule_diplome = de.abbreviation)
 804                                             WHERE  f.Intitule_diplome != '' AND f.Intitule_formation != ''
 805                                                    AND NOT EXISTS (SELECT  *
 806                                                                      FROM  profile_education_degree AS d
 807                                                                     WHERE  ee.id = d.eduid AND de.id = d.degreeid)");
 808
 809         $page->assign('missingEducation', $missingEducation);
 810         $page->assign('missingDegree', $missingDegree);
 811         $page->assign('missingCouple', $missingCouple);
 812         $page->assign('missingEducationCount', $missingEducation->total());
 813         $page->assign('missingDegreeCount', $missingDegree->total());
 814         $page->assign('missingCoupleCount', $missingCouple->total());
 815     }
 816
 817     function handler_corps($page)
 818     {
 819         $page->changeTpl('fusionax/corps.tpl');
 820
 821         $missingCorps = XDB::rawIterator('SELECT  DISTINCT(f.corps_sortie) AS name
 822                                             FROM  fusionax_anciens AS f
 823                                            WHERE  NOT EXISTS (SELECT  *
 824                                                                 FROM  profile_corps_enum AS c
 825                                                                WHERE  f.corps_sortie = c.abbreviation)');
 826         $missingGrade = XDB::rawIterator('SELECT  DISTINCT(f.grade) AS name
 827                                             FROM  fusionax_anciens AS f
 828                                            WHERE  NOT EXISTS (SELECT  *
 829                                                                 FROM  profile_corps_rank_enum AS c
 830                                                                WHERE  f.grade = c.name)');
 831
 832         $page->assign('missingCorps', $missingCorps);
 833         $page->assign('missingGrade', $missingGrade);
 834         $page->assign('missingCorpsCount', $missingCorps->total());
 835         $page->assign('missingGradeCount', $missingGrade->total());
 836     }
 837
 838     function handler_issues_deathdate($page, $action = '')
 839     {
 840         $page->changeTpl('fusionax/deathdate_issues.tpl');
 841         if ($action == 'edit') {
 842             S::assert_xsrf_token();
 843
 844             $issues = XDB::rawIterRow('SELECT  p.pid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate
 845                                          FROM  profile_merge_issues AS pm
 846                                    INNER JOIN  profiles             AS p  ON (pm.pid = p.pid)
 847                                    INNER JOIN  profile_display      AS pd ON (pd.pid = p.pid)
 848                                         WHERE  FIND_IN_SET(\'deathdate\', pm.issues)
 849                                      ORDER BY  pd.directory_name');
 850             while (list($pid, $name, $promo, $deathAX, $deathXorg) = $issues->next()) {
 851                 $choiceAX = Post::has('AX_' . $pid);
 852                 $choiceXorg = Post::has('XORG_' . $pid);
 853                 if (!($choiceAX || $choiceXorg)) {
 854                     continue;
 855                 }
 856
 857                 if ($choiceAX) {
 858                     XDB::execute('UPDATE  profiles             AS p
 859                               INNER JOIN  profile_merge_issues AS pm ON (pm.pid = p.pid)
 860                                      SET  p.deathdate = pm.deathdate_ax, p.deathdate_rec = NOW()
 861                                    WHERE  p.pid = {?}', $pid);
 862                 }
 863                 XDB::execute("UPDATE  profile_merge_issues
 864                                  SET  issues = REPLACE(issues, 'deathdate', '')
 865                                WHERE  pid = {?}", $pid());
 866                 $page->trigSuccess("La date de décès de $name ($promo) a bien été corrigée.");
 867             }
 868         }
 869
 870         $issues = XDB::rawFetchAllAssoc('SELECT  p.pid, p.hrpid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate
 871                                            FROM  profile_merge_issues AS pm
 872                                      INNER JOIN  profiles             AS p  ON (pm.pid = p.pid)
 873                                      INNER JOIN  profile_display      AS pd ON (pd.pid = p.pid)
 874                                           WHERE  FIND_IN_SET(\'deathdate\', pm.issues)
 875                                        ORDER BY  pd.directory_name');
 876         $page->assign('issues', $issues);
 877         $page->assign('total', count($issues));
 878     }
 879
 880     function handler_issues_promo($page, $action = '')
 881     {
 882         $page->changeTpl('fusionax/promo_issues.tpl');
 883         if ($action == 'edit') {
 884             S::assert_xsrf_token();
 885
 886             $issues = XDB::rawIterRow('SELECT  p.pid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year
 887                                          FROM  profile_merge_issues AS pm
 888                                    INNER JOIN  profiles             AS p  ON (pm.pid = p.pid)
 889                                    INNER JOIN  profile_display      AS pd ON (pd.pid = p.pid)
 890                                    INNER JOIN  profile_education    AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags))
 891                                         WHERE  FIND_IN_SET(\'promo\', pm.issues)
 892                                      ORDER BY  pd.directory_name');
 893             while (list($pid, $name, $promo, $deathAX, $deathXorgEntry, $deathXorgGrad) = $issues->next()) {
 894                 $choiceXorg = Post::has('XORG_' . $pid);
 895                 if (!(Post::has('display_' . $pid) && Post::has('entry_' . $pid) && Post::has('grad_' . $pid))) {
 896                     continue;
 897                 }
 898
 899                 $display = Post::i('display_' . $pid);
 900                 $entry = Post::i('entry_' . $pid);
 901                 $grad = Post::i('grad_' . $pid);
 902                 if (!(($grad <= $entry + 5 && $grad >= $entry + 3) && ($display >= $entry && $display <= $grad - 3))) {
 903                     $page->trigError("La promotion de $name n'a pas été corrigée.");
 904                     continue;
 905                 }
 906                 XDB::execute('UPDATE  profile_display
 907                                  SET  promo = {?}
 908                                WHERE  pid = {?}', 'X' . $display, $pid);
 909                 XDB::execute('UPDATE  profile_education
 910                                  SET  entry_year = {?}, grad_year = {?}
 911                                WHERE  pid = {?} AND FIND_IN_SET(\'primary\', flags)', $entry, $grad, $pid);
 912                 $page->trigSuccess("La promotion de $name a bien été corrigée.");
 913             }
 914         }
 915
 916         $issues = XDB::rawFetchAllAssoc('SELECT  p.pid, p.hrpid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year
 917                                            FROM  profile_merge_issues AS pm
 918                                      INNER JOIN  profiles             AS p  ON (pm.pid = p.pid)
 919                                      INNER JOIN  profile_display      AS pd ON (pd.pid = p.pid)
 920                                      INNER JOIN  profile_education    AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags))
 921                                           WHERE  FIND_IN_SET(\'promo\', pm.issues)
 922                                        ORDER BY  pd.directory_name');
 923         $page->assign('issues', $issues);
 924         $page->assign('total', count($issues));
 925     }
 926
 927     function handler_issues($page, $action = '')
 928     {
 929         static $issueList = array(
 930             'name'      => 'noms',
 931             'phone'     => 'téléphones',
 932             'education' => 'formations',
 933             'address'   => 'adresses',
 934             'job'       => 'emplois'
 935         );
 936         static $typeList = array(
 937             'name'      => 'general',
 938             'phone'     => 'general',
 939             'education' => 'general',
 940             'address'   => 'adresses',
 941             'job'       => 'emploi'
 942         );
 943
 944         if (!array_key_exists($action, $issueList)) {
 945             pl_redirect('fusionax');
 946         } else {
 947             $total = XDB::fetchOneCell('SELECT  COUNT(*)
 948                                           FROM  profile_merge_issues
 949                                          WHERE  FIND_IN_SET({?}, issues)', $action);
 950             if ($total == 0) {
 951                 pl_redirect('fusionax');
 952             }
 953
 954             $issues = XDB::fetchAllAssoc('SELECT  p.hrpid, pd.directory_name, pd.promo
 955                                             FROM  profile_merge_issues AS pm
 956                                       INNER JOIN  profiles             AS p  ON (pm.pid = p.pid)
 957                                       INNER JOIN  profile_display      AS pd ON (pd.pid = p.pid)
 958                                            WHERE  FIND_IN_SET({?}, pm.issues)
 959                                         ORDER BY  pd.directory_name
 960                                            LIMIT  100', $action);
 961
 962             $page->changeTpl('fusionax/other_issues.tpl');
 963             $page->assign('issues', $issues);
 964             $page->assign('issue', $issueList[$action]);
 965             $page->assign('type', $typeList[$action]);
 966             $page->assign('total', $total);
 967         }
 968     }
 969 }
 970
 971 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker fenc=utf-8:
 972 ?>