2 /***************************************************************************
3 * Copyright (C) 2003-2011 Polytechnique.org *
4 * http://opensource.polytechnique.org/ *
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. *
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. *
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 *
19 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *
20 ***************************************************************************/
23 * Module to merge data from AX database: this will only be used once on
24 * production site and should be removed afterwards.
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.
30 * Datas are stored in an export file.
32 class FusionAxModule
extends PLModule
36 if (Platal
::globals()->merge
->state
== 'pending') {
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 'fusionax/edu' => $this->make_hook('edu', AUTH_MDP
, 'admin'),
46 'fusionax/corps' => $this->make_hook('corps', AUTH_MDP
, 'admin')
48 } elseif (Platal
::globals()->merge
->state
== 'done') {
50 'fusionax' => $this->make_hook('index', AUTH_MDP
, 'admin,edit_directory'),
51 'fusionax/issues' => $this->make_hook('issues', AUTH_MDP
, 'admin,edit_directory'),
52 'fusionax/issues/deathdate' => $this->make_hook('issues_deathdate', AUTH_MDP
, 'admin,edit_directory'),
53 'fusionax/issues/promo' => $this->make_hook('issues_promo', AUTH_MDP
, 'admin,edit_directory'),
59 function handler_index(&$page)
61 if (Platal
::globals()->merge
->state
== 'pending') {
62 $page->changeTpl('fusionax/index.tpl');
63 } elseif (Platal
::globals()->merge
->state
== 'done') {
67 'address' => 'adresses',
68 'promo' => 'promotions',
69 'deathdate' => 'dates de décès',
70 'phone' => 'téléphones',
71 'education' => 'formations',
73 $issues = XDB
::rawFetchOneAssoc("SELECT COUNT(*) AS total,
74 SUM(FIND_IN_SET('name', issues)) DIV 1 AS name,
75 SUM(FIND_IN_SET('job', issues)) DIV 2 AS job,
76 SUM(FIND_IN_SET('address', issues)) DIV 3 AS address,
77 SUM(FIND_IN_SET('promo', issues)) DIV 4 AS promo,
78 SUM(FIND_IN_SET('deathdate', issues)) DIV 5 AS deathdate,
79 SUM(FIND_IN_SET('phone', issues)) DIV 6 AS phone,
80 SUM(FIND_IN_SET('education', issues)) DIV 7 AS education
81 FROM profile_merge_issues
82 WHERE issues IS NOT NULL OR issues != ''");
83 $page->changeTpl('fusionax/issues.tpl');
84 $page->assign('issues', $issues);
85 $page->assign('issueList', $issueList);
89 /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
90 function handler_import(&$page, $action = 'index', $file = '')
92 if ($action == 'index') {
93 $page->changeTpl('fusionax/import.tpl');
97 // toutes les actions sont faites en ajax en utilisant jquery
98 header('Content-type: text/javascript; charset=utf-8');
103 $modulepath = realpath(dirname(__FILE__
) . '/fusionax/') . '/';
104 $spoolpath = realpath(dirname(__FILE__
) . '/../spool/fusionax/') . '/';
106 if ($action == 'launch') {
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.';
112 // séparation de l'archive en fichiers par tables
113 $file = $spoolpath . $file;
114 // Removes master and doctorate students
115 exec('grep -v "^[A-Z]\{2\}.[0-9]\{4\}[MD][0-9]\{3\}" ' . $file . ' > ' . $file . '.tmp');
116 exec('mv -f ' . $file . '.tmp ' . $file);
117 // Split export into specialised files
118 exec('grep "^AD" ' . $file . ' > ' . $spoolpath . 'Adresses.txt');
119 exec('grep "^AN" ' . $file . ' > ' . $spoolpath . 'Anciens.txt');
120 exec('grep "^FO" ' . $file . ' > ' . $spoolpath . 'Formations.txt');
121 exec('grep "^AC" ' . $file . ' > ' . $spoolpath . 'Activites.txt');
122 exec('grep "^EN" ' . $file . ' > ' . $spoolpath . 'Entreprises.txt');
123 exec($modulepath . 'formation.pl');
124 exec('mv -f ' . $spoolpath . 'Formations_out.txt ' . $spoolpath . 'Formations.txt');
125 $report[] = 'Fichier parsé.';
126 $report[] = 'Import dans la base en cours...';
127 $next = 'integrateSQL';
129 } elseif ($action == 'integrateSQL') {
130 // intégration des données dans la base MySQL
131 // liste des fichiers sql à exécuter
133 0 => 'Activites.sql',
136 3 => 'Formations.sql',
137 4 => 'Entreprises.sql'
140 // récupère le contenu du fichier sql
141 $queries = explode(';', file_get_contents($modulepath . $filesSQL[$file]));
142 foreach ($queries as $q) {
144 // coupe le fichier en requêtes individuelles
145 if (substr($q, 0, 2) == '--') {
146 // affiche les commentaires dans le report
147 $lines = explode("\n", $q);
149 $report[] = addslashes($l);
151 // exécute la requête
152 XDB
::execute(str_replace('{?}', $spoolpath, $q));
155 // trouve le prochain fichier à exécuter
156 $nextfile = $file +
1;
161 // tous les fichiers ont été exécutés, on passe à l'étape suivante
164 // on passe au fichier suivant
165 $next = 'integrateSQL/' . $nextfile;
167 } elseif ($action == 'adds1920') {
168 // Adds promotion 1920 from AX db.
169 $report[] = 'Ajout de la promotion 1920';
170 $res = XDB
::iterator('SELECT prenom, Nom_complet, ax_id
171 FROM fusionax_anciens
172 WHERE promotion_etude = 1920;');
174 $nameTypes = DirEnum
::getOptions(DirEnum
::NAMETYPES
);
175 $nameTypes = array_flip($nameTypes);
176 $eduSchools = DirEnum
::getOptions(DirEnum
::EDUSCHOOLS
);
177 $eduSchools = array_flip($eduSchools);
178 $eduDegrees = DirEnum
::getOptions(DirEnum
::EDUDEGREES
);
179 $eduDegrees = array_flip($eduDegrees);
180 $degreeid = $eduDegrees[Profile
::DEGREE_X
];
184 $sex = PlUser
::GENDER_MALE
;
188 while (list($firstname, $lastname, $ax_id) = $res->next()) {
189 $hrid = self
::getHrid($firstname, $lastname, $promo);
190 $res1 = XDB
::query('SELECT COUNT(*)
192 WHERE hruid = {?}', $hrid);
193 $res2 = XDB
::query('SELECT COUNT(*)
195 WHERE hrpid = {?}', $hrid);
196 if (is_null($hrid) ||
$res1->fetchOneCell() > 0 ||
$res2->fetchOneCell() > 0) {
197 $report[] = $ax_id . ' non ajouté';
199 $fullName = $firstname . ' ' . $lastname;
200 $directoryName = $lastname . ' ' . $firstname;
203 XDB
::execute('INSERT INTO profiles (hrpid, xorg_id, ax_id, sex)
204 VALUES ({?}, {?}, {?}, {?})',
205 $hrid, $xorgId, $ax_id, $sex);
206 $pid = XDB
::insertId();
207 XDB
::execute('INSERT INTO profile_name (pid, name, typeid)
208 VALUES ({?}, {?}, {?})',
209 $pid, $lastname, $nameTypes['name_ini']);
210 XDB
::execute('INSERT INTO profile_name (pid, name, typeid)
211 VALUES ({?}, {?}, {?})',
212 $pid, $firstname, $nameTypes['firstname_ini']);
213 XDB
::execute('INSERT INTO profile_display (pid, yourself, public_name, private_name,
214 directory_name, short_name, sort_name, promo)
215 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
216 $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo);
217 XDB
::execute('INSERT INTO profile_education (pid, eduid, degreeid, entry_year, grad_year, flags)
218 VALUES ({?}, {?}, {?}, {?}, {?}, {?})',
219 $pid, $eduSchools[Profile
::EDU_X
], $degreeid, $entry_year, $grad_year, 'primary');
220 XDB
::execute('INSERT INTO accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, sex)
221 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?})',
222 $hrid, $type, 0, 'active', $fullName, $directoryName, $lastname, $sex);
223 $uid = XDB
::insertId();
224 XDB
::execute('INSERT INTO account_profiles (uid, pid, perms)
225 VALUES ({?}, {?}, {?})',
226 $uid, $pid, 'owner');
228 $report[] = 'Promo 1920 ajoutée.';
230 } elseif ($action == 'view') {
231 XDB
::execute('CREATE OR REPLACE ALGORITHM=MERGE VIEW fusionax_xorg_anciens AS
232 SELECT p.pid, p.ax_id, pd.promo, pd.private_name, pd.public_name,
233 pd.sort_name, pd.short_name, pd.directory_name
235 INNER JOIN profile_display AS pd USING(pid)');
237 } elseif ($action == 'clean') {
238 // nettoyage du fichier temporaire
239 exec('rm -Rf ' . $spoolpath);
240 $report[] = 'Import finit.';
242 foreach($report as $t) {
243 // affiche les lignes de report
244 echo "$('#fusionax').append('" . $t . "<br/>');\n";
247 // lance le prochain script s'il y en a un
248 echo "$.getScript('fusionax/import/" . $next . "');";
250 // exit pour ne pas afficher la page template par défaut
254 function handler_view(&$page, $action = '')
256 $page->changeTpl('fusionax/view.tpl');
257 if ($action == 'create') {
258 XDB
::execute('DROP VIEW IF EXISTS fusionax_deceased');
259 XDB
::execute("CREATE VIEW fusionax_deceased AS
260 SELECT p.pid, a.ax_id, pd.private_name, pd.promo, p.deathdate AS deces_xorg, a.Date_deces AS deces_ax
262 INNER JOIN profile_display AS pd ON (p.pid = pd.pid)
263 INNER JOIN fusionax_anciens AS a ON (a.ax_id = p.ax_id)
264 WHERE p.deathdate != a.Date_deces OR (p.deathdate IS NULL AND a.Date_deces != '0000-00-00')");
265 XDB
::execute('DROP VIEW IF EXISTS fusionax_promo');
266 XDB
::execute('CREATE VIEW fusionax_promo AS
267 SELECT p.pid, p.ax_id, pd.private_name, pd.promo, pe.entry_year AS promo_etude_xorg,
268 f.promotion_etude AS promo_etude_ax, pe.grad_year AS promo_sortie_xorg
270 INNER JOIN profile_display AS pd ON (p.pid = pd.pid)
271 INNER JOIN profile_education AS pe ON (p.pid = pe.pid)
272 INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id)
273 WHERE pd.promo != CONCAT(\'X\', f.promotion_etude)
274 AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)');
275 $page->trigSuccess('Les VIEW ont bien été créées.');
279 /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */
280 private static function clear_wrong_in_xorg($pid)
282 $res = XDB
::execute('UPDATE fusionax_xorg_anciens
284 WHERE pid = {?}', $pid);
288 return XDB
::affectedRows() / 2;
291 /* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX
292 * (mises à part les promo 1921 et 1923 qui ne figurent pas dans les données de l'AX)*/
293 private static function find_wrong_in_xorg($limit = 10)
295 return XDB
::iterator('SELECT u.promo, u.pid, u.private_name
296 FROM fusionax_xorg_anciens AS u
297 WHERE NOT EXISTS (SELECT *
298 FROM fusionax_anciens AS f
299 WHERE f.ax_id = u.ax_id)
300 AND u.ax_id IS NOT NULL AND promo != \'X1921\' AND promo != \'X1923\'');
303 /** Lier les identifiants d'un ancien dans les deux annuaires
304 * @param user_id identifiant dans l'annuaire X.org
305 * @param matricule_ax identifiant dans l'annuaire de l'AX
306 * @return 0 si la liaison a échoué, 1 sinon
308 private static function link_by_ids($pid, $ax_id)
310 $res = XDB
::execute('UPDATE fusionax_import AS i
311 INNER JOIN fusionax_xorg_anciens AS u
312 SET u.ax_id = i.ax_id,
314 i.date_match_id = NOW()
315 WHERE i.ax_id = {?} AND u.pid = {?}
316 AND (u.ax_id != {?} OR u.ax_id IS NULL
317 OR i.pid != {?} OR i.pid IS NULL)',
318 $ax_id, $pid, $ax_id, $pid);
322 return XDB
::affectedRows() / 2;
325 /** Recherche automatique d'anciens à lier entre les deux annuaires
326 * @param limit nombre d'anciens à trouver au max
327 * @param sure si true, ne trouve que des anciens qui sont quasi sûrs
328 * @return un XOrgDBIterator sur les entrées avec display_name, promo,
329 * pid, ax_id et display_name_ax
331 private static function find_easy_to_link($limit = 10, $sure = false
)
333 $easy_to_link = XDB
::iterator("
334 SELECT u.private_name, u.promo, u.pid, ax.ax_id,
335 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X', ax.promotion_etude, ')') AS display_name_ax,
336 COUNT(*) AS nbMatches
337 FROM fusionax_anciens AS ax
338 INNER JOIN fusionax_import AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
339 LEFT JOIN fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
340 AND u.promo = CONCAT('X', ax.promotion_etude)
341 AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
342 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
343 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name))
345 HAVING u.pid IS NOT NULL AND nbMatches = 1" . ($limit ?
(' LIMIT ' . $limit) : ''));
346 if ($easy_to_link->total() > 0 ||
$sure) {
347 return $easy_to_link;
349 return XDB
::iterator("
350 SELECT u.private_name, u.promo, u.pid, ax.ax_id,
351 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X', ax.promotion_etude, ')') AS display_name_ax,
352 COUNT(*) AS nbMatches
353 FROM fusionax_anciens AS ax
354 INNER JOIN fusionax_import AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
355 LEFT JOIN fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
356 AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
357 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
358 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name)
359 AND u.promo < CONCAT('X', ax.promotion_etude + 2)
360 AND u.promo > CONCAT('X', ax.promotion_etude - 2))
362 HAVING u.pid IS NOT NULL AND nbMatches = 1" . ($limit ?
(' LIMIT ' . $limit) : ''));
365 /** Module de mise en correspondance les ids */
366 function handler_ids(&$page, $part = 'main', $pid = null
, $ax_id = null
)
369 $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple');
371 if ($part == 'missingInAX') {
372 // locate all persons from this database that are not in AX's
373 $page->changeTpl('fusionax/idsMissingInAx.tpl');
374 $missingInAX = XDB
::iterator('SELECT promo, pid, private_name
375 FROM fusionax_xorg_anciens
376 WHERE ax_id IS NULL');
377 $page->assign('missingInAX', $missingInAX);
380 if ($part == 'missingInXorg') {
381 // locate all persons from AX's database that are not here
382 $page->changeTpl('fusionax/idsMissingInXorg.tpl');
383 $missingInXorg = XDB
::iterator("SELECT CONCAT(a.prenom, ' ', a.Nom_usuel) AS private_name,
384 a.promotion_etude AS promo, a.ax_id
386 INNER JOIN fusionax_anciens AS a USING (ax_id)
387 WHERE fusionax_import.pid IS NULL");
388 $page->assign('missingInXorg', $missingInXorg);
391 if ($part == 'wrongInXorg') {
392 // locate all persons from Xorg database that have a bad AX id
393 $page->changeTpl('fusionax/idswrongInXorg.tpl');
394 $wrongInXorg = FusionAxModule
::find_wrong_in_xorg($nbToLink);
395 $page->assign('wrongInXorg', $wrongInXorg);
398 if ($part == 'cleanwronginxorg') {
399 $linksToDo = FusionAxModule
::find_wrong_in_xorg($nbToLink);
400 while ($l = $linksToDo->next()) {
401 FusionAxModule
::clear_wrong_in_xorg($l['pid']);
403 pl_redirect('fusionax/ids/wrongInXorg');
405 if ($part == 'lier') {
406 if (Post
::has('user_id') && Post
::has('matricule_ax')) {
407 FusionAxModule
::link_by_ids(Post
::i('pid'), Post
::v('ax_id'));
410 if ($part == 'link') {
411 FusionAxModule
::link_by_ids($pid, $ax_id);
414 if ($part == 'linknext') {
415 $linksToDo = FusionAxModule
::find_easy_to_link($nbToLink);
416 while ($l = $linksToDo->next()) {
417 FusionAxModule
::link_by_ids($l['pid'], $l['ax_id']);
419 pl_redirect('fusionax/ids#autolink');
421 if ($part == 'linkall') {
422 $linksToDo = FusionAxModule
::find_easy_to_link(0);
423 while ($l = $linksToDo->next()) {
424 FusionAxModule
::link_by_ids($l['pid'], $l['ax_id']);
428 $page->changeTpl('fusionax/ids.tpl');
429 $missingInAX = XDB
::query('SELECT COUNT(*)
430 FROM fusionax_xorg_anciens
431 WHERE ax_id IS NULL');
433 $page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
435 $missingInXorg = XDB
::query('SELECT COUNT(*)
438 if ($missingInXorg) {
439 $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
441 $wrongInXorg = FusionAxModule
::find_wrong_in_xorg($nbToLink);
442 if ($wrongInXorg->total() > 0) {
443 $page->assign('wrongInXorg', $wrongInXorg->total());
445 $easyToLink = FusionAxModule
::find_easy_to_link($nbToLink);
446 if ($easyToLink->total() > 0) {
447 $page->assign('nbMatch', $easyToLink->total());
448 $page->assign('easyToLink', $easyToLink);
453 function handler_deceased(&$page, $action = '')
455 if ($action == 'updateXorg') {
456 XDB
::execute('UPDATE fusionax_deceased
457 SET deces_xorg = deces_ax
458 WHERE deces_xorg IS NULL');
460 if ($action == 'updateAX') {
461 XDB
::execute('UPDATE fusionax_deceased
462 SET deces_ax = deces_xorg
463 WHERE deces_ax = "0000-00-00"');
465 if ($action == 'update') {
466 if (Post
::has('pid') && Post
::has('date')) {
467 XDB
::execute('UPDATE fusionax_deceased
468 SET deces_ax = {?}, deces_xorg = {?}
470 Post
::v('date'), Post
::v('date'), Post
::i('pid'));
473 $page->changeTpl('fusionax/deceased.tpl');
475 $deceasedErrorsSql = XDB
::query('SELECT COUNT(*) FROM fusionax_deceased');
476 $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
477 $res = XDB
::iterator('SELECT pid, ax_id, promo, private_name, deces_ax
478 FROM fusionax_deceased
479 WHERE deces_xorg IS NULL
481 $page->assign('nbDeceasedMissingInXorg', $res->total());
482 $page->assign('deceasedMissingInXorg', $res);
483 $res = XDB
::iterator('SELECT pid, ax_id, promo, private_name, deces_xorg
484 FROM fusionax_deceased
485 WHERE deces_ax = "0000-00-00"
487 $page->assign('nbDeceasedMissingInAX', $res->total());
488 $page->assign('deceasedMissingInAX', $res);
489 $res = XDB
::iterator('SELECT pid, ax_id, promo, private_name, deces_xorg, deces_ax
490 FROM fusionax_deceased
491 WHERE deces_xorg != "0000-00-00" AND deces_ax != "0000-00-00"');
492 $page->assign('nbDeceasedDifferent', $res->total());
493 $page->assign('deceasedDifferent', $res);
496 function handler_promo(&$page, $action = '')
498 $page->changeTpl('fusionax/promo.tpl');
499 $res = XDB
::iterator('SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
501 WHERE !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg)
502 AND !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg)
503 AND !(promo_etude_ax = promo_etude_xorg + 1)
504 ORDER BY promo_etude_xorg');
505 $nbMissmatchingPromos = $res->total();
506 $page->assign('nbMissmatchingPromos', $res->total());
507 $page->assign('missmatchingPromos', $res);
509 $res = XDB
::iterator('SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
511 WHERE promo_etude_ax = promo_etude_xorg + 1
512 ORDER BY promo_etude_xorg');
513 $nbMissmatchingPromos +
= $res->total();
514 $page->assign('nbMissmatchingPromos1', $res->total());
515 $page->assign('missmatchingPromos1', $res);
517 $res = XDB
::iterator('SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
519 WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg
520 ORDER BY promo_etude_xorg');
521 $nbMissmatchingPromos +
= $res->total();
522 $page->assign('nbMissmatchingPromos2', $res->total());
523 $page->assign('missmatchingPromos2', $res);
525 $res = XDB
::iterator('SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
527 WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg
528 ORDER BY promo_etude_xorg');
529 $nbMissmatchingPromos +
= $res->total();
530 $page->assign('nbMissmatchingPromos3', $res->total());
531 $page->assign('missmatchingPromos3', $res);
533 $page->assign('nbMissmatchingPromosTotal', $nbMissmatchingPromos);
536 function handler_names(&$page, $action = '')
538 $page->changeTpl('fusionax/names.tpl');
540 $res = XDB
::query('SELECT COUNT(*)
541 FROM fusionax_anciens AS f
542 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)');
543 $page->assign('total', $res->fetchOneCell());
547 // | lastname_marital | 2 |
548 // | lastname_ordinary | 3 |
550 // | firstname_ordinary | 7 |
551 // | firstname_other | 8 |
552 // | name_other | 9 |
554 // | firstname_ini | 11 |
555 $res = XDB
::query("SELECT COUNT(*)
556 FROM fusionax_anciens AS f
557 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
558 LEFT JOIN profile_name AS pnp ON (p.pid = pnp.pid AND pnp.typeid = 1)
559 LEFT JOIN profile_name AS pnm ON (p.pid = pnm.pid AND pnm.typeid = 2)
560 LEFT JOIN profile_name AS pno ON (p.pid = pno.pid AND pno.typeid = 3)
561 LEFT JOIN profile_name AS pne ON (p.pid = pne.pid AND pne.typeid = 9)
562 LEFT JOIN profile_name AS pni ON (p.pid = pni.pid AND pni.typeid = 10)
563 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)
564 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)
565 OR f.Nom_complet NOT IN (pnp.name, pno.name, pnm.name, pne.name, pni.name)");
566 $page->assign('lastnameIssues', $res->fetchOneCell());
568 $res = XDB
::query('SELECT COUNT(*)
569 FROM fusionax_anciens AS f
570 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
571 LEFT JOIN profile_name AS pnf ON (p.pid = pnf.pid AND pnf.typeid = 4)
572 LEFT JOIN profile_name AS pno ON (p.pid = pno.pid AND pno.typeid = 7)
573 LEFT JOIN profile_name AS pne ON (p.pid = pne.pid AND pne.typeid = 8)
574 LEFT JOIN profile_name AS pni ON (p.pid = pni.pid AND pni.typeid = 11)
575 WHERE f.prenom NOT IN (pnf.name, pno.name, pne.name, pni.name)');
576 $page->assign('firstnameIssues', $res->fetchOneCell());
580 function handler_edu(&$page, $action = '')
582 $page->changeTpl('fusionax/education.tpl');
584 $missingEducation = XDB
::rawIterator("SELECT DISTINCT(f.Intitule_formation)
585 FROM fusionax_formations AS f
586 WHERE f.Intitule_formation != '' AND NOT EXISTS (SELECT *
587 FROM profile_education_enum AS e
588 WHERE f.Intitule_formation = e.name)");
589 $missingDegree = XDB
::rawIterator("SELECT DISTINCT(f.Intitule_diplome)
590 FROM fusionax_formations AS f
591 WHERE f.Intitule_diplome != '' AND NOT EXISTS (SELECT *
592 FROM profile_education_degree_enum AS e
593 WHERE f.Intitule_diplome = e.abbreviation)");
594 $missingCouple = XDB
::rawIterator("SELECT DISTINCT(f.Intitule_formation) AS edu, f.Intitule_diplome AS degree, ee.id AS eduid, de.id AS degreeid
595 FROM fusionax_formations AS f
596 INNER JOIN profile_education_enum AS ee ON (f.Intitule_formation = ee.name)
597 INNER JOIN profile_education_degree_enum AS de ON (f.Intitule_diplome = de.abbreviation)
598 WHERE f.Intitule_diplome != '' AND f.Intitule_formation != ''
599 AND NOT EXISTS (SELECT *
600 FROM profile_education_degree AS d
601 WHERE ee.id = d.eduid AND de.id = d.degreeid)");
603 $page->assign('missingEducation', $missingEducation);
604 $page->assign('missingDegree', $missingDegree);
605 $page->assign('missingCouple', $missingCouple);
606 $page->assign('missingEducationCount', $missingEducation->total());
607 $page->assign('missingDegreeCount', $missingDegree->total());
608 $page->assign('missingCoupleCount', $missingCouple->total());
611 function handler_corps(&$page)
613 $page->changeTpl('fusionax/corps.tpl');
615 $missingCorps = XDB
::rawIterator('SELECT DISTINCT(f.corps_sortie) AS name
616 FROM fusionax_anciens AS f
617 WHERE NOT EXISTS (SELECT *
618 FROM profile_corps_enum AS c
619 WHERE f.corps_sortie = c.abbreviation)');
620 $missingGrade = XDB
::rawIterator('SELECT DISTINCT(f.grade) AS name
621 FROM fusionax_anciens AS f
622 WHERE NOT EXISTS (SELECT *
623 FROM profile_corps_rank_enum AS c
624 WHERE f.grade = c.name)');
626 $page->assign('missingCorps', $missingCorps);
627 $page->assign('missingGrade', $missingGrade);
628 $page->assign('missingCorpsCount', $missingCorps->total());
629 $page->assign('missingGradeCount', $missingGrade->total());
632 function handler_issues_deathdate(&$page, $action = '')
634 $page->changeTpl('fusionax/deathdate_issues.tpl');
635 if ($action == 'edit') {
636 S
::assert_xsrf_token();
638 $issues = XDB
::rawIterRow('SELECT p.pid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate
639 FROM profile_merge_issues AS pm
640 INNER JOIN profiles AS p ON (pm.pid = p.pid)
641 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
642 WHERE FIND_IN_SET(\'deathdate\', pm.issues)
643 ORDER BY pd.directory_name');
644 while (list($pid, $name, $promo, $deathAX, $deathXorg) = $issues->next()) {
645 $choiceAX = Post
::has('AX_' . $pid);
646 $choiceXorg = Post
::has('XORG_' . $pid);
647 if (!($choiceAX ||
$choiceXorg)) {
652 XDB
::execute('UPDATE profiles AS p
653 INNER JOIN profile_merge_issues AS pm ON (pm.pid = p.pid)
654 SET p.deathdate = pm.deathdate_ax, p.deathdate_rec = NOW()
655 WHERE p.pid = {?}', $pid);
657 XDB
::execute("UPDATE profile_merge_issues
658 SET issues = REPLACE(issues, 'deathdate', '')
659 WHERE pid = {?}", $pid());
660 $page->trigSuccess("La date de décès de $name ($promo) a bien été corrigée.");
664 $issues = XDB
::rawFetchAllAssoc('SELECT p.pid, p.hrpid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate
665 FROM profile_merge_issues AS pm
666 INNER JOIN profiles AS p ON (pm.pid = p.pid)
667 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
668 WHERE FIND_IN_SET(\'deathdate\', pm.issues)
669 ORDER BY pd.directory_name');
670 $page->assign('issues', $issues);
671 $page->assign('total', count($issues));
674 function handler_issues_promo(&$page, $action = '')
676 $page->changeTpl('fusionax/promo_issues.tpl');
677 if ($action == 'edit') {
678 S
::assert_xsrf_token();
680 $issues = XDB
::rawIterRow('SELECT p.pid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year
681 FROM profile_merge_issues AS pm
682 INNER JOIN profiles AS p ON (pm.pid = p.pid)
683 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
684 INNER JOIN profile_education AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags))
685 WHERE FIND_IN_SET(\'promo\', pm.issues)
686 ORDER BY pd.directory_name');
687 while (list($pid, $name, $promo, $deathAX, $deathXorgEntry, $deathXorgGrad) = $issues->next()) {
688 $choiceXorg = Post
::has('XORG_' . $pid);
689 if (!(Post
::has('display_' . $pid) && Post
::has('entry_' . $pid) && Post
::has('grad_' . $pid))) {
693 $display = Post
::i('display_' . $pid);
694 $entry = Post
::i('entry_' . $pid);
695 $grad = Post
::i('grad_' . $pid);
696 if (!(($grad <= $entry +
5 && $grad >= $entry +
3) && ($display >= $entry && $display <= $grad - 3))) {
697 $page->trigError("La promotion de $name n'a pas été corrigée.");
700 XDB
::execute('UPDATE profile_display
702 WHERE pid = {?}', 'X' . $display, $pid);
703 XDB
::execute('UPDATE profile_education
704 SET entry_year = {?}, grad_year = {?}
705 WHERE pid = {?} AND FIND_IN_SET(\'primary\', flags)', $entry, $grad, $pid);
706 $page->trigSuccess("La promotion de $name a bien été corrigée.");
710 $issues = XDB
::rawFetchAllAssoc('SELECT p.pid, p.hrpid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year
711 FROM profile_merge_issues AS pm
712 INNER JOIN profiles AS p ON (pm.pid = p.pid)
713 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
714 INNER JOIN profile_education AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags))
715 WHERE FIND_IN_SET(\'promo\', pm.issues)
716 ORDER BY pd.directory_name');
717 $page->assign('issues', $issues);
718 $page->assign('total', count($issues));
721 function handler_issues(&$page, $action = '')
723 static $issueList = array(
725 'phone' => 'téléphones',
726 'education' => 'formations',
727 'address' => 'adresses',
730 static $typeList = array(
732 'phone' => 'general',
733 'education' => 'general',
734 'address' => 'adresses',
738 if (!array_key_exists($action, $issueList)) {
739 pl_redirect('fusionax');
741 $total = XDB
::fetchOneCell('SELECT COUNT(*)
742 FROM profile_merge_issues
743 WHERE FIND_IN_SET({?}, issues)', $action);
745 pl_redirect('fusionax');
748 $issues = XDB
::fetchAllAssoc('SELECT p.hrpid, pd.directory_name, pd.promo
749 FROM profile_merge_issues AS pm
750 INNER JOIN profiles AS p ON (pm.pid = p.pid)
751 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
752 WHERE FIND_IN_SET({?}, pm.issues)
753 ORDER BY pd.directory_name
754 LIMIT 100', $action);
756 $page->changeTpl('fusionax/other_issues.tpl');
757 $page->assign('issues', $issues);
758 $page->assign('issue', $issueList[$action]);
759 $page->assign('type', $typeList[$action]);
760 $page->assign('total', $total);
765 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: