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         if (Platal::globals()->merge->state == 'pending') {
  37             return array(
  38                 'fusionax'                  => $this->make_hook('index',    AUTH_MDP, 'admin'),
  39                 'fusionax/import'           => $this->make_hook('import',   AUTH_MDP, 'admin'),
  40                 'fusionax/view'             => $this->make_hook('view',     AUTH_MDP, 'admin'),
  41                 'fusionax/ids'              => $this->make_hook('ids',      AUTH_MDP, 'admin'),
  42                 'fusionax/deceased'         => $this->make_hook('deceased', AUTH_MDP, 'admin'),
  43                 'fusionax/promo'            => $this->make_hook('promo',    AUTH_MDP, 'admin'),
  44                 'fusionax/names'            => $this->make_hook('names',    AUTH_MDP, 'admin')
  45             );
  46         } elseif (Platal::globals()->merge->state == 'done') {
  47             return array(
  48                 'fusionax'                  => $this->make_hook('index',            AUTH_MDP, 'admin,edit_directory'),
  49                 'fusionax/issues'           => $this->make_hook('issues',           AUTH_MDP, 'admin,edit_directory'),
  50                 'fusionax/issues/deathdate' => $this->make_hook('issues_deathdate', AUTH_MDP, 'admin,edit_directory'),
  51                 'fusionax/issues/promo'     => $this->make_hook('issues_promo',     AUTH_MDP, 'admin,edit_directory'),
  52             );
  53         }
  54     }
  55
  56
  57     function handler_index(&$page)
  58     {
  59         if (Platal::globals()->merge->state == 'pending') {
  60             $page->changeTpl('fusionax/index.tpl');
  61         } elseif (Platal::globals()->merge->state == 'done') {
  62             $issueList = array(
  63                 'name'      => 'noms',
  64                 'job'       => 'emplois',
  65                 'address'   => 'adresses',
  66                 'promo'     => 'promotions',
  67                 'deathdate' => 'dates de décès',
  68                 'phone'     => 'téléphones',
  69                 'education' => 'formations',
  70             );
  71             $issues = XDB::rawFetchOneAssoc("SELECT  COUNT(*) AS total,
  72                                                      SUM(FIND_IN_SET('name', issues))      DIV 1 AS name,
  73                                                      SUM(FIND_IN_SET('job', issues))       DIV 2 AS job,
  74                                                      SUM(FIND_IN_SET('address', issues))   DIV 3 AS address,
  75                                                      SUM(FIND_IN_SET('promo', issues))     DIV 4 AS promo,
  76                                                      SUM(FIND_IN_SET('deathdate', issues)) DIV 5 AS deathdate,
  77                                                      SUM(FIND_IN_SET('phone', issues))     DIV 6 AS phone,
  78                                                      SUM(FIND_IN_SET('education', issues)) DIV 7 AS education
  79                                                FROM  profile_merge_issues
  80                                               WHERE  issues IS NOT NULL OR issues != ''");
  81             $page->changeTpl('fusionax/issues.tpl');
  82             $page->assign('issues', $issues);
  83             $page->assign('issueList', $issueList);
  84         }
  85     }
  86
  87     /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
  88     function handler_import(&$page, $action = 'index', $file = '')
  89     {
  90         if ($action == 'index') {
  91             $page->changeTpl('fusionax/import.tpl');
  92             return;
  93         }
  94
  95         // toutes les actions sont faites en ajax en utilisant jquery
  96         header('Content-type: text/javascript; charset=utf-8');
  97
  98         // log des actions
  99         $report = array();
 100
 101         $modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/';
 102         $spoolpath = realpath(dirname(__FILE__) . '/../spool/fusionax/') . '/';
 103
 104         if ($action == 'launch') {
 105             if ($file == '') {
 106                 $report[] = 'Nom de fichier non renseigné.';
 107             } elseif (!file_exists(dirname(__FILE__) . '/../spool/fusionax/' . $file)) {
 108                 $report[] = 'Le fichier ne se situe pas au bon endroit.';
 109             } else {
 110                 // séparation de l'archive en fichiers par tables
 111                 $file = $spoolpath . $file;
 112                 // Removes master and doctorate students
 113                 exec('grep -v "^[A-Z]\{2\}.[0-9]\{4\}[MD][0-9]\{3\}" ' . $file . ' > ' . $file . '.tmp');
 114                 exec('mv -f ' . $file . '.tmp ' . $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" ' . $file . ' > ' . $spoolpath . 'Formations.txt');
 119                 exec('grep "^AC" ' . $file . ' > ' . $spoolpath . 'Activites.txt');
 120                 exec('grep "^EN" ' . $file . ' > ' . $spoolpath . 'Entreprises.txt');
 121                 exec($modulepath . 'formation.pl');
 122                 exec('mv -f ' . $spoolpath . 'Formations_out.txt ' . $spoolpath . 'Formations.txt');
 123                 $report[] = 'Fichier parsé.';
 124                 $report[] = 'Import dans la base en cours...';
 125                 $next = 'integrateSQL';
 126             }
 127         } elseif ($action == 'integrateSQL') {
 128             // intégration des données dans la base MySQL
 129             // liste des fichiers sql à exécuter
 130             $filesSQL = array(
 131                 0 => 'Activites.sql',
 132                 1 => 'Adresses.sql',
 133                 2 => 'Anciens.sql',
 134                 3 => 'Formations.sql',
 135                 4 => 'Entreprises.sql'
 136             );
 137             if ($file != '') {
 138                 // récupère le contenu du fichier sql
 139                 $queries = explode(';', file_get_contents($modulepath . $filesSQL[$file]));
 140                 foreach ($queries as $q) {
 141                     if (trim($q)) {
 142                         // coupe le fichier en requêtes individuelles
 143                         if (substr($q, 0, 2) == '--') {
 144                             // affiche les commentaires dans le report
 145                             $lines = explode("\n", $q);
 146                             $l = $lines[0];
 147                             $report[] = addslashes($l);
 148                         }
 149                         // exécute la requête
 150                         XDB::execute(str_replace('{?}', $spoolpath, $q));
 151                     }
 152                 }
 153                 // trouve le prochain fichier à exécuter
 154                 $nextfile = $file + 1;
 155             } else {
 156                 $nextfile = 0;
 157             }
 158             if ($nextfile > 4) {
 159                 // tous les fichiers ont été exécutés, on passe à l'étape suivante
 160                 $next = 'adds1920';
 161             } else {
 162                 // on passe au fichier suivant
 163                 $next = 'integrateSQL/' . $nextfile;
 164             }
 165         } elseif ($action == 'adds1920') {
 166             // Adds promotion 1920 from AX db.
 167             $report[] = 'Ajout de la promotion 1920';
 168             $res = XDB::iterator('SELECT  prenom, Nom_complet, ax_id
 169                                     FROM  fusionax_anciens
 170                                    WHERE  promotion_etude = 1920;');
 171
 172             $nameTypes = DirEnum::getOptions(DirEnum::NAMETYPES);
 173             $nameTypes = array_flip($nameTypes);
 174             $eduSchools = DirEnum::getOptions(DirEnum::EDUSCHOOLS);
 175             $eduSchools = array_flip($eduSchools);
 176             $eduDegrees = DirEnum::getOptions(DirEnum::EDUDEGREES);
 177             $eduDegrees = array_flip($eduDegrees);
 178             $degreeid = $eduDegrees[Profile::DEGREE_X];
 179             $entry_year = 1920;
 180             $grad_year = 1923;
 181             $promo = 'X1920';
 182             $sex = PlUser::GENDER_MALE;
 183             $xorgId = 19200000;
 184             $type = 'x';
 185
 186             while (list($firstname, $lastname, $ax_id) = $res->next()) {
 187                 $hrid = self::getHrid($firstname, $lastname, $promo);
 188                 $res1 = XDB::query('SELECT  COUNT(*)
 189                                       FROM  accounts
 190                                      WHERE  hruid = {?}', $hrid);
 191                 $res2 = XDB::query('SELECT  COUNT(*)
 192                                       FROM  profiles
 193                                      WHERE  hrpid = {?}', $hrid);
 194                 if (is_null($hrid) || $res1->fetchOneCell() > 0 || $res2->fetchOneCell() > 0) {
 195                     $report[] = $ax_id . ' non ajouté';
 196                 }
 197                 $fullName = $firstname . ' ' . $lastname;
 198                 $directoryName = $lastname . ' ' . $firstname;
 199                 ++$xorgId;
 200
 201                 XDB::execute('REPLACE INTO  profiles (hrpid, xorg_id, ax_id, sex)
 202                                     VALUES  ({?}, {?}, {?}, {?})',
 203                              $hrid, $xorgId, $ax_id, $sex);
 204                 $pid = XDB::insertId();
 205                 XDB::execute('REPLACE INTO  profile_name (pid, name, typeid)
 206                                     VALUES  ({?}, {?}, {?})',
 207                              $pid, $lastname, $nameTypes['name_ini']);
 208                 XDB::execute('REPLACE INTO  profile_name (pid, name, typeid)
 209                                     VALUES  ({?}, {?}, {?})',
 210                              $pid, $firstname, $nameTypes['firstname_ini']);
 211                 XDB::execute('REPLACE INTO  profile_display (pid, yourself, public_name, private_name,
 212                                                              directory_name, short_name, sort_name, promo)
 213                                     VALUES  ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
 214                              $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo);
 215                 XDB::execute('REPLACE INTO  profile_education (pid, eduid, degreeid, entry_year, grad_year, flags)
 216                                     VALUES  ({?}, {?}, {?}, {?}, {?}, {?})',
 217                              $pid, $eduSchools[Profile::EDU_X], $degreeid, $entry_year, $grad_year, 'primary');
 218                 XDB::execute('REPLACE INTO  accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, sex)
 219                                     VALUES  ({?}, {?}, {?}, {?}, {?}, {?}, {?})',
 220                              $hrid, $type, 0, 'active', $fullName, $directoryName, $lastname, $sex);
 221                 $uid = XDB::insertId();
 222                 XDB::execute('REPLACE INTO  account_profiles (uid, pid, perms)
 223                                     VALUES  ({?}, {?}, {?})',
 224                              $uid, $pid, 'owner');
 225             }
 226             $report[] = 'Promo 1920 ajoutée.';
 227             $next = 'view';
 228         } elseif ($action == 'view') {
 229             XDB::execute('CREATE OR REPLACE ALGORITHM=MERGE VIEW  fusionax_xorg_anciens AS
 230                                                           SELECT  p.pid, p.ax_id, pd.promo, pd.private_name, pd.public_name,
 231                                                                   pd.sort_name, pd.short_name, pd.directory_name
 232                                                             FROM  profiles        AS p
 233                                                       INNER JOIN  profile_display AS pd USING(pid)');
 234             $next = 'clean';
 235         } elseif ($action == 'clean') {
 236             // nettoyage du fichier temporaire
 237             exec('rm -Rf ' . $spoolpath);
 238             $report[] = 'Import finit.';
 239         }
 240         foreach($report as $t) {
 241             // affiche les lignes de report
 242             echo "$('#fusionax').append('" . $t . "<br/>');\n";
 243         }
 244         if (isset($next)) {
 245             // lance le prochain script s'il y en a un
 246             echo "$.getScript('fusionax/import/" . $next . "');";
 247         }
 248         // exit pour ne pas afficher la page template par défaut
 249         exit;
 250     }
 251
 252     function handler_view(&$page, $action = '')
 253     {
 254         $page->changeTpl('fusionax/view.tpl');
 255         if ($action == 'create') {
 256             XDB::execute('DROP VIEW IF EXISTS fusionax_deceased');
 257             XDB::execute('CREATE VIEW  fusionax_deceased AS
 258                                SELECT  p.pid, a.ax_id, pd.private_name, pd.promo, p.deathdate AS deces_xorg, a.Date_deces AS deces_ax
 259                                  FROM  profiles         AS p
 260                            INNER JOIN  profile_display  AS pd ON (p.pid = pd.pid)
 261                            INNER JOIN  fusionax_anciens AS a ON (a.ax_id = p.ax_id)
 262                                 WHERE  p.deathdate != a.Date_deces');
 263             XDB::execute('DROP VIEW IF EXISTS fusionax_promo');
 264             XDB::execute('CREATE VIEW  fusionax_promo AS
 265                                SELECT  p.pid, p.ax_id, pd.private_name, pd.promo, pe.entry_year AS promo_etude_xorg,
 266                                        f.promotion_etude AS promo_etude_ax, pe.grad_year AS promo_sortie_xorg
 267                                  FROM  profiles          AS p
 268                            INNER JOIN  profile_display   AS pd ON (p.pid = pd.pid)
 269                            INNER JOIN  profile_education AS pe ON (p.pid = pe.pid)
 270                            INNER JOIN  fusionax_anciens  AS f  ON (p.ax_id = f.ax_id)
 271                                 WHERE  pd.promo != CONCAT(\'X\', f.promotion_etude)
 272                                        AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)');
 273             $page->trigSuccess('Les VIEW ont bien été créées.');
 274         }
 275     }
 276
 277     /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */
 278     private static function clear_wrong_in_xorg($pid)
 279     {
 280         $res = XDB::execute('UPDATE  fusionax_xorg_anciens
 281                                 SET  ax_id = NULL
 282                               WHERE  pid = {?}', $pid);
 283         if (!$res) {
 284             return 0;
 285         }
 286         return XDB::affectedRows() / 2;
 287     }
 288
 289     /* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX
 290      * (mises à part les promo 1921 et 1923 qui ne figurent pas dans les données de l'AX)*/
 291     private static function find_wrong_in_xorg($limit = 10)
 292     {
 293         return XDB::iterator('SELECT  u.promo, u.pid, u.private_name
 294                                 FROM  fusionax_xorg_anciens AS u
 295                                WHERE  NOT EXISTS (SELECT  *
 296                                                     FROM  fusionax_anciens AS f
 297                                                    WHERE  f.ax_id = u.ax_id)
 298                                       AND u.ax_id IS NOT NULL AND promo != \'X1921\' AND promo != \'X1923\'');
 299     }
 300
 301     /** Lier les identifiants d'un ancien dans les deux annuaires
 302      * @param user_id identifiant dans l'annuaire X.org
 303      * @param matricule_ax identifiant dans l'annuaire de l'AX
 304      * @return 0 si la liaison a échoué, 1 sinon
 305      */
 306     private static function link_by_ids($pid, $ax_id)
 307     {
 308         $res = XDB::execute('UPDATE  fusionax_import       AS i
 309                          INNER JOIN  fusionax_xorg_anciens AS u
 310                                 SET  u.ax_id = i.ax_id,
 311                                      i.pid = u.pid,
 312                                      i.date_match_id = NOW()
 313                               WHERE  i.ax_id = {?} AND u.pid = {?}
 314                                      AND (u.ax_id != {?} OR u.ax_id IS NULL
 315                                           OR i.pid != {?} OR i.pid IS NULL)',
 316                             $ax_id, $pid, $ax_id, $pid);
 317         if (!$res) {
 318             return 0;
 319         }
 320         return XDB::affectedRows() / 2;
 321     }
 322
 323     /** Recherche automatique d'anciens à lier entre les deux annuaires
 324      * @param limit nombre d'anciens à trouver au max
 325      * @param sure si true, ne trouve que des anciens qui sont quasi sûrs
 326      * @return un XOrgDBIterator sur les entrées avec display_name, promo,
 327      *   pid, ax_id et display_name_ax
 328      */
 329     private static function find_easy_to_link($limit = 10, $sure = false)
 330     {
 331         $easy_to_link = XDB::iterator("
 332         SELECT  u.private_name, u.promo, u.pid, ax.ax_id,
 333                 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X', ax.promotion_etude, ')') AS display_name_ax,
 334                 COUNT(*) AS nbMatches
 335           FROM  fusionax_anciens      AS ax
 336     INNER JOIN  fusionax_import       AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
 337      LEFT JOIN  fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
 338                                                AND u.promo = CONCAT('X', ax.promotion_etude)
 339                                                AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
 340                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
 341                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name))
 342       GROUP BY  u.pid
 343         HAVING  u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : ''));
 344         if ($easy_to_link->total() > 0 || $sure) {
 345             return $easy_to_link;
 346         }
 347         return XDB::iterator("
 348         SELECT  u.private_name, u.promo, u.pid, ax.ax_id,
 349                 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X', ax.promotion_etude, ')') AS display_name_ax,
 350                 COUNT(*) AS nbMatches
 351           FROM  fusionax_anciens      AS ax
 352     INNER JOIN  fusionax_import       AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
 353      LEFT JOIN  fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
 354                                                AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
 355                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
 356                                                     OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name)
 357                                                AND u.promo < CONCAT('X', ax.promotion_etude + 2)
 358                                                AND u.promo > CONCAT('X', ax.promotion_etude - 2))
 359       GROUP BY  u.pid
 360         HAVING  u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : ''));
 361     }
 362
 363     /** Module de mise en correspondance les ids */
 364     function handler_ids(&$page, $part = 'main', $pid = null, $ax_id = null)
 365     {
 366         $nbToLink = 100;
 367         $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple');
 368
 369         if ($part == 'missingInAX') {
 370             // locate all persons from this database that are not in AX's
 371             $page->changeTpl('fusionax/idsMissingInAx.tpl');
 372             $missingInAX = XDB::iterator('SELECT  promo, pid, private_name
 373                                             FROM  fusionax_xorg_anciens
 374                                            WHERE  ax_id IS NULL');
 375             $page->assign('missingInAX', $missingInAX);
 376             return;
 377         }
 378         if ($part == 'missingInXorg') {
 379             // locate all persons from AX's database that are not here
 380             $page->changeTpl('fusionax/idsMissingInXorg.tpl');
 381             $missingInXorg = XDB::iterator("SELECT  CONCAT(a.prenom, ' ', a.Nom_usuel) AS private_name,
 382                                                     a.promotion_etude AS promo, a.ax_id
 383                                               FROM  fusionax_import
 384                                         INNER JOIN  fusionax_anciens AS a USING (ax_id)
 385                                              WHERE  fusionax_import.pid IS NULL");
 386             $page->assign('missingInXorg', $missingInXorg);
 387             return;
 388         }
 389         if ($part == 'wrongInXorg') {
 390             // locate all persons from Xorg database that have a bad AX id
 391             $page->changeTpl('fusionax/idswrongInXorg.tpl');
 392             $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
 393             $page->assign('wrongInXorg', $wrongInXorg);
 394             return;
 395         }
 396         if ($part == 'cleanwronginxorg') {
 397             $linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink);
 398             while ($l = $linksToDo->next()) {
 399                 FusionAxModule::clear_wrong_in_xorg($l['pid']);
 400             }
 401             pl_redirect('fusionax/ids/wrongInXorg');
 402         }
 403         if ($part == 'lier') {
 404             if (Post::has('user_id') && Post::has('matricule_ax')) {
 405                 FusionAxModule::link_by_ids(Post::i('pid'), Post::v('ax_id'));
 406             }
 407         }
 408         if ($part == 'link') {
 409             FusionAxModule::link_by_ids($pid, $ax_id);
 410             exit;
 411         }
 412         if ($part == 'linknext') {
 413             $linksToDo = FusionAxModule::find_easy_to_link($nbToLink);
 414             while ($l = $linksToDo->next()) {
 415                 FusionAxModule::link_by_ids($l['pid'], $l['ax_id']);
 416             }
 417             pl_redirect('fusionax/ids#autolink');
 418         }
 419         if ($part == 'linkall') {
 420             $linksToDo = FusionAxModule::find_easy_to_link(0);
 421             while ($l = $linksToDo->next()) {
 422                 FusionAxModule::link_by_ids($l['pid'], $l['ax_id']);
 423             }
 424         }
 425         {
 426             $page->changeTpl('fusionax/ids.tpl');
 427             $missingInAX = XDB::query('SELECT  COUNT(*)
 428                                          FROM  fusionax_xorg_anciens
 429                                         WHERE  ax_id IS NULL');
 430             if ($missingInAX) {
 431                 $page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
 432             }
 433             $missingInXorg = XDB::query('SELECT  COUNT(*)
 434                                            FROM  fusionax_import
 435                                           WHERE  pid IS NULL');
 436             if ($missingInXorg) {
 437                 $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
 438             }
 439             $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
 440             if ($wrongInXorg->total() > 0) {
 441                 $page->assign('wrongInXorg', $wrongInXorg->total());
 442             }
 443             $easyToLink = FusionAxModule::find_easy_to_link($nbToLink);
 444             if ($easyToLink->total() > 0) {
 445                 $page->assign('nbMatch', $easyToLink->total());
 446                 $page->assign('easyToLink', $easyToLink);
 447             }
 448         }
 449     }
 450
 451     function handler_deceased(&$page, $action = '')
 452     {
 453         if ($action == 'updateXorg') {
 454             XDB::execute('UPDATE  fusionax_deceased
 455                              SET  deces_xorg = deces_ax
 456                            WHERE  deces_xorg = "0000-00-00"');
 457         }
 458         if ($action == 'updateAX') {
 459             XDB::execute('UPDATE  fusionax_deceased
 460                              SET  deces_ax = deces_xorg
 461                            WHERE  deces_ax = "0000-00-00"');
 462         }
 463         if ($action == 'update') {
 464             if (Post::has('pid') && Post::has('date')) {
 465                 XDB::execute('UPDATE  fusionax_deceased
 466                                  SET  deces_ax = {?}, deces_xorg = {?}
 467                                WHERE  pid = {?}',
 468                              Post::v('date'), Post::v('date'), Post::i('pid'));
 469             }
 470         }
 471         $page->changeTpl('fusionax/deceased.tpl');
 472         // deceased
 473         $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
 474         $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
 475         $res = XDB::iterator('SELECT  pid, ax_id, promo, private_name, deces_ax
 476                                 FROM  fusionax_deceased
 477                                WHERE  deces_xorg = "0000-00-00"
 478                                LIMIT  10');
 479         $page->assign('nbDeceasedMissingInXorg', $res->total());
 480         $page->assign('deceasedMissingInXorg', $res);
 481         $res = XDB::iterator('SELECT  pid, ax_id, promo, private_name, deces_xorg
 482                                 FROM  fusionax_deceased
 483                                WHERE  deces_ax = "0000-00-00"
 484                                LIMIT  10');
 485         $page->assign('nbDeceasedMissingInAX', $res->total());
 486         $page->assign('deceasedMissingInAX', $res);
 487         $res = XDB::iterator('SELECT  pid, ax_id, promo, private_name, deces_xorg, deces_ax
 488                                 FROM  fusionax_deceased
 489                                WHERE  deces_xorg != "0000-00-00" AND deces_ax != "0000-00-00"');
 490         $page->assign('nbDeceasedDifferent', $res->total());
 491         $page->assign('deceasedDifferent', $res);
 492     }
 493
 494     function handler_promo(&$page, $action = '')
 495     {
 496         $page->changeTpl('fusionax/promo.tpl');
 497         $res = XDB::iterator('SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 498                                 FROM  fusionax_promo
 499                                WHERE  !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg)
 500                                       AND !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg)
 501                                       AND !(promo_etude_ax = promo_etude_xorg + 1)
 502                             ORDER BY  promo_etude_xorg');
 503         $nbMissmatchingPromos = $res->total();
 504         $page->assign('nbMissmatchingPromos', $res->total());
 505         $page->assign('missmatchingPromos', $res);
 506
 507         $res = XDB::iterator('SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 508                                 FROM  fusionax_promo
 509                                WHERE  promo_etude_ax = promo_etude_xorg + 1
 510                             ORDER BY  promo_etude_xorg');
 511         $nbMissmatchingPromos += $res->total();
 512         $page->assign('nbMissmatchingPromos1', $res->total());
 513         $page->assign('missmatchingPromos1', $res);
 514
 515         $res = XDB::iterator('SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 516                                 FROM  fusionax_promo
 517                                WHERE  promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg
 518                             ORDER BY  promo_etude_xorg');
 519         $nbMissmatchingPromos += $res->total();
 520         $page->assign('nbMissmatchingPromos2', $res->total());
 521         $page->assign('missmatchingPromos2', $res);
 522
 523         $res = XDB::iterator('SELECT  pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
 524                                 FROM  fusionax_promo
 525                                WHERE  promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg
 526                             ORDER BY  promo_etude_xorg');
 527         $nbMissmatchingPromos += $res->total();
 528         $page->assign('nbMissmatchingPromos3', $res->total());
 529         $page->assign('missmatchingPromos3', $res);
 530
 531         $page->assign('nbMissmatchingPromosTotal', $nbMissmatchingPromos);
 532     }
 533
 534     function handler_names(&$page, $action = '')
 535     {
 536         $page->changeTpl('fusionax/names.tpl');
 537
 538         $res = XDB::query('SELECT  COUNT(*)
 539                              FROM  fusionax_anciens AS f
 540                        INNER JOIN  profiles         AS p    ON (f.ax_id = p.ax_id)');
 541         $page->assign('total', $res->fetchOneCell());
 542
 543         // To be checked:
 544         // | lastname           |  1 |
 545         // | lastname_marital   |  2 |
 546         // | lastname_ordinary  |  3 |
 547         // | firstname          |  4 |
 548         // | firstname_ordinary |  7 |
 549         // | firstname_other    |  8 |
 550         // | name_other         |  9 |
 551         // | name_ini           | 10 |
 552         // | firstname_ini      | 11 |
 553         $res = XDB::query("SELECT  COUNT(*)
 554                              FROM  fusionax_anciens AS f
 555                        INNER JOIN  profiles         AS p   ON (f.ax_id = p.ax_id)
 556                         LEFT JOIN  profile_name     AS pnp ON (p.pid = pnp.pid AND pnp.typeid = 1)
 557                         LEFT JOIN  profile_name     AS pnm ON (p.pid = pnm.pid AND pnm.typeid = 2)
 558                         LEFT JOIN  profile_name     AS pno ON (p.pid = pno.pid AND pno.typeid = 3)
 559                         LEFT JOIN  profile_name     AS pne ON (p.pid = pne.pid AND pne.typeid = 9)
 560                         LEFT JOIN  profile_name     AS pni ON (p.pid = pni.pid AND pni.typeid = 10)
 561                             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)
 562                                    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)
 563                                    OR f.Nom_complet NOT IN (pnp.name, pno.name, pnm.name, pne.name, pni.name)");
 564         $page->assign('lastnameIssues', $res->fetchOneCell());
 565
 566         $res = XDB::query('SELECT  COUNT(*)
 567                              FROM  fusionax_anciens AS f
 568                        INNER JOIN  profiles         AS p   ON (f.ax_id = p.ax_id)
 569                         LEFT JOIN  profile_name     AS pnf ON (p.pid = pnf.pid AND pnf.typeid = 4)
 570                         LEFT JOIN  profile_name     AS pno ON (p.pid = pno.pid AND pno.typeid = 7)
 571                         LEFT JOIN  profile_name     AS pne ON (p.pid = pne.pid AND pne.typeid = 8)
 572                         LEFT JOIN  profile_name     AS pni ON (p.pid = pni.pid AND pni.typeid = 11)
 573                             WHERE  f.prenom NOT IN (pnf.name, pno.name, pne.name, pni.name)');
 574         $page->assign('firstnameIssues', $res->fetchOneCell());
 575
 576     }
 577
 578     function handler_issues_deathdate(&$page, $action = '')
 579     {
 580         $page->changeTpl('fusionax/deathdate_issues.tpl');
 581         if ($action == 'edit') {
 582             S::assert_xsrf_token();
 583
 584             $issues = XDB::rawIterRow('SELECT  p.pid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate
 585                                          FROM  profile_merge_issues AS pm
 586                                    INNER JOIN  profiles             AS p  ON (pm.pid = p.pid)
 587                                    INNER JOIN  profile_display      AS pd ON (pd.pid = p.pid)
 588                                         WHERE  FIND_IN_SET(\'deathdate\', pm.issues)
 589                                      ORDER BY  pd.directory_name');
 590             while (list($pid, $name, $promo, $deathAX, $deathXorg) = $issues->next()) {
 591                 $choiceAX = Post::has('AX_' . $pid);
 592                 $choiceXorg = Post::has('XORG_' . $pid);
 593                 if (!($choiceAX || $choiceXorg)) {
 594                     continue;
 595                 }
 596
 597                 if ($choiceAX) {
 598                     XDB::execute('UPDATE  profiles             AS p
 599                               INNER JOIN  profile_merge_issues AS pm ON (pm.pid = p.pid)
 600                                      SET  p.deathdate = pm.deathdate_ax, p.deathdate_rec = NOW()
 601                                    WHERE  p.pid = {?}', $pid);
 602                 }
 603                 XDB::execute("UPDATE  profile_merge_issues
 604                                  SET  issues = REPLACE(issues, 'deathdate', '')
 605                                WHERE  pid = {?}", $pid());
 606                 $page->trigSuccess("La date de décès de $name ($promo) a bien été corrigée.");
 607             }
 608         }
 609
 610         $issues = XDB::rawFetchAllAssoc('SELECT  p.pid, p.hrpid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate
 611                                            FROM  profile_merge_issues AS pm
 612                                      INNER JOIN  profiles             AS p  ON (pm.pid = p.pid)
 613                                      INNER JOIN  profile_display      AS pd ON (pd.pid = p.pid)
 614                                           WHERE  FIND_IN_SET(\'deathdate\', pm.issues)
 615                                        ORDER BY  pd.directory_name');
 616         $page->assign('issues', $issues);
 617         $page->assign('total', count($issues));
 618     }
 619
 620     function handler_issues_promo(&$page, $action = '')
 621     {
 622         $page->changeTpl('fusionax/promo_issues.tpl');
 623         if ($action == 'edit') {
 624             S::assert_xsrf_token();
 625
 626             $issues = XDB::rawIterRow('SELECT  p.pid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year
 627                                          FROM  profile_merge_issues AS pm
 628                                    INNER JOIN  profiles             AS p  ON (pm.pid = p.pid)
 629                                    INNER JOIN  profile_display      AS pd ON (pd.pid = p.pid)
 630                                    INNER JOIN  profile_education    AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags))
 631                                         WHERE  FIND_IN_SET(\'promo\', pm.issues)
 632                                      ORDER BY  pd.directory_name');
 633             while (list($pid, $name, $promo, $deathAX, $deathXorgEntry, $deathXorgGrad) = $issues->next()) {
 634                 $choiceXorg = Post::has('XORG_' . $pid);
 635                 if (!(Post::has('display_' . $pid) && Post::has('entry_' . $pid) && Post::has('grad_' . $pid))) {
 636                     continue;
 637                 }
 638
 639                 $display = Post::i('display_' . $pid);
 640                 $entry = Post::i('entry_' . $pid);
 641                 $grad = Post::i('grad_' . $pid);
 642                 if (!(($grad <= $entry + 5 && $grad >= $entry + 3) && ($display >= $entry && $display <= $grad - 3))) {
 643                     $page->trigError("La promotion de $name n'a pas été corrigée.");
 644                     continue;
 645                 }
 646                 XDB::execute('UPDATE  profile_display
 647                                  SET  promo = {?}
 648                                WHERE  pid = {?}', 'X' . $display, $pid);
 649                 XDB::execute('UPDATE  profile_education
 650                                  SET  entry_year = {?}, grad_year = {?}
 651                                WHERE  pid = {?} AND FIND_IN_SET(\'primary\', flags)', $entry, $grad, $pid);
 652                 $page->trigSuccess("La promotion de $name a bien été corrigée.");
 653             }
 654         }
 655
 656         $issues = XDB::rawFetchAllAssoc('SELECT  p.pid, p.hrpid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year
 657                                            FROM  profile_merge_issues AS pm
 658                                      INNER JOIN  profiles             AS p  ON (pm.pid = p.pid)
 659                                      INNER JOIN  profile_display      AS pd ON (pd.pid = p.pid)
 660                                      INNER JOIN  profile_education    AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags))
 661                                           WHERE  FIND_IN_SET(\'promo\', pm.issues)
 662                                        ORDER BY  pd.directory_name');
 663         $page->assign('issues', $issues);
 664         $page->assign('total', count($issues));
 665     }
 666
 667     function handler_issues(&$page, $action = '')
 668     {
 669         static $issueList = array(
 670             'name'      => 'noms',
 671             'phone'     => 'téléphones',
 672             'education' => 'formations',
 673             'address'   => 'adresses',
 674             'job'       => 'emplois'
 675         );
 676
 677         if (!array_key_exists($action, $issueList)) {
 678             pl_redirect('fusionax');
 679         } else {
 680             $total = XDB::fetchOneCell('SELECT  COUNT(*)
 681                                           FROM  profile_merge_issues
 682                                          WHERE  FIND_IN_SET({?}, issues)', $action);
 683             if ($total == 0) {
 684                 pl_redirect('fusionax');
 685             }
 686
 687             $issues = XDB::fetchAllAssoc('SELECT  p.hrpid, pd.directory_name, pd.promo
 688                                             FROM  profile_merge_issues AS pm
 689                                       INNER JOIN  profiles             AS p  ON (pm.pid = p.pid)
 690                                       INNER JOIN  profile_display      AS pd ON (pd.pid = p.pid)
 691                                            WHERE  FIND_IN_SET({?}, pm.issues)
 692                                         ORDER BY  pd.directory_name
 693                                            LIMIT  100', $action);
 694
 695             $page->changeTpl('fusionax/other_issues.tpl');
 696             $page->assign('issues', $issues);
 697             $page->assign('issue', $issueList[$action]);
 698             $page->assign('total', $total);
 699         }
 700     }
 701 }
 702
 703 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 704 ?>