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