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