16aa4882cfed4666b517d31207aa49bbd13ae554
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_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')
48 } elseif (Platal
::globals()->merge
->state
== 'done') {
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'),
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 // Split export into specialised files
115 exec('grep "^AD" ' . $file . ' > ' . $spoolpath . 'Adresses.txt');
116 exec('grep "^AN" ' . $file . ' > ' . $spoolpath . 'Anciens.txt');
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');
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');
124 exec('mv -f ' . $spoolpath . 'Formations_MD_out.txt ' . $spoolpath . 'Formations_MD.txt');
125 $report[] = 'Fichier parsé.';
126 $report[] = 'Import dans la base en cours...';
127 XDB
::execute("UPDATE profiles
130 $next = 'integrateSQL';
132 } elseif ($action == 'integrateSQL') {
133 // intégration des données dans la base MySQL
134 // liste des fichiers sql à exécuter
136 0 => 'Activites.sql',
139 3 => 'Formations.sql',
140 4 => 'Entreprises.sql',
141 5 => 'Formations_MD.sql'
144 // récupère le contenu du fichier sql
145 $queries = explode(';', file_get_contents($modulepath . $filesSQL[$file]));
146 foreach ($queries as $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);
153 $report[] = addslashes($l);
155 // exécute la requête
156 XDB
::execute(str_replace('{?}', $spoolpath, $q));
159 // trouve le prochain fichier à exécuter
160 $nextfile = $file +
1;
165 // tous les fichiers ont été exécutés, on passe à l'étape suivante
168 // on passe au fichier suivant
169 $next = 'integrateSQL/' . $nextfile;
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;');
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
];
191 while ($new = $res->next()) {
192 $firstname = $new['prenom'];
193 $lastname = $new['Nom_complet'];
194 $ax_id = $new['ax_id'];
195 $hrid = User
::makeHrid($firstname, $lastname, $hrpromo);
196 $res1 = XDB
::query('SELECT COUNT(*)
198 WHERE hruid = {?}', $hrid);
199 $res2 = XDB
::query('SELECT COUNT(*)
201 WHERE hrpid = {?}', $hrid);
202 if (is_null($hrid) ||
$res1->fetchOneCell() > 0 ||
$res2->fetchOneCell() > 0) {
203 $report[] = $ax_id . ' non ajouté';
205 $fullName = $firstname . ' ' . $lastname;
206 $directoryName = $lastname . ' ' . $firstname;
209 XDB
::execute('INSERT INTO profiles (hrpid, xorg_id, ax_id, sex)
210 VALUES ({?}, {?}, {?}, {?})',
211 $hrid, $xorgId, $ax_id, $sex);
212 $pid = XDB
::insertId();
213 XDB
::execute('INSERT INTO profile_public_names (pid, lastname_initial, firstname_initial, lastname_main, firstname_main)
214 VALUES ({?}, {?}, {?}, {?}, {?})',
215 $pid, $lastname, $firstname, $lastname, $firstname);
216 XDB
::execute('INSERT INTO profile_display (pid, yourself, public_name, private_name,
217 directory_name, short_name, sort_name, promo)
218 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
219 $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo);
220 XDB
::execute('INSERT INTO profile_education (pid, eduid, degreeid, entry_year, grad_year, flags)
221 VALUES ({?}, {?}, {?}, {?}, {?}, {?})',
222 $pid, $eduSchools[Profile
::EDU_X
], $degreeid, $entry_year, $grad_year, 'primary');
223 XDB
::execute('INSERT INTO accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, lastname, firstname, sex)
224 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
225 $hrid, $type, 0, 'pending', $fullName, $directoryName, $firstname, $lastname, $firstname, $sex);
226 $uid = XDB
::insertId();
227 XDB
::execute('INSERT INTO account_profiles (uid, pid, perms)
228 VALUES ({?}, {?}, {?})',
229 $uid, $pid, 'owner');
231 $report[] = 'Promo 1920 ajoutée.';
233 } elseif ($action == 'adds2011') {
234 // Adds promotion 2011 from AX db.
235 $report[] = 'Ajout des élèves manquant de la promotion 2011';
236 $res = XDB
::iterator("SELECT prenom, Nom_complet, ax_id, Civilite
237 FROM fusionax_anciens
238 WHERE promotion_etude = 2011 AND groupe_promo = 'X'
239 AND NOT EXISTS (SELECT 1
241 WHERE profiles.ax_id = fusionax_anciens.ax_id)");
243 $eduSchools = DirEnum
::getOptions(DirEnum
::EDUSCHOOLS
);
244 $eduSchools = array_flip($eduSchools);
245 $eduDegrees = DirEnum
::getOptions(DirEnum
::EDUDEGREES
);
246 $eduDegrees = array_flip($eduDegrees);
247 $degreeid = $eduDegrees[Profile
::DEGREE_X
];
254 while ($new = $res->next()) {
255 $firstname = $new['prenom'];
256 $lastname = $new['Nom_complet'];
257 $ax_id = $new['ax_id'];
258 $civilite = $new['Civilite'];
259 $hrid = User
::makeHrid($firstname, $lastname, $hrpromo);
260 $res1 = XDB
::query('SELECT COUNT(*)
262 WHERE hruid = {?}', $hrid);
263 $res2 = XDB
::query('SELECT COUNT(*)
265 WHERE hrpid = {?}', $hrid);
266 if (is_null($hrid) ||
$res1->fetchOneCell() > 0 ||
$res2->fetchOneCell() > 0) {
267 $report[] = $ax_id . ' non ajouté';
269 $fullName = $firstname . ' ' . $lastname;
270 $directoryName = $lastname . ' ' . $firstname;
271 if ($civilite == 'M') {
277 XDB
::execute('INSERT INTO profiles (hrpid, ax_id, sex, title)
278 VALUES ({?}, {?}, {?}, {?})',
279 $hrid, $ax_id, $sex, $civilite);
280 $pid = XDB
::insertId();
281 XDB
::execute('INSERT INTO profile_public_names (pid, lastname_initial, firstname_initial, lastname_main, firstname_main)
282 VALUES ({?}, {?}, {?}, {?}, {?})',
283 $pid, $lastname, $firstname, $lastname, $firstname);
284 XDB
::execute('INSERT INTO profile_display (pid, yourself, public_name, private_name,
285 directory_name, short_name, sort_name, promo)
286 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
287 $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo);
288 XDB
::execute('INSERT INTO profile_education (pid, eduid, degreeid, entry_year, grad_year, flags)
289 VALUES ({?}, {?}, {?}, {?}, {?}, {?})',
290 $pid, $eduSchools[Profile
::EDU_X
], $degreeid, $entry_year, $grad_year, 'primary');
291 XDB
::execute('INSERT INTO accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, lastname, firstname, sex)
292 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
293 $hrid, $type, 0, 'pending', $fullName, $directoryName, $firstname, $lastname, $firstname, $sex);
294 $uid = XDB
::insertId();
295 XDB
::execute('INSERT INTO account_profiles (uid, pid, perms)
296 VALUES ({?}, {?}, {?})',
297 $uid, $pid, 'owner');
299 $report[] = 'Promo 2011 ajoutée.';
302 } elseif ($action == 'view') {
303 XDB
::execute('CREATE OR REPLACE ALGORITHM=MERGE VIEW fusionax_xorg_anciens AS
304 SELECT p.pid, p.ax_id, pd.promo, pd.private_name, pd.public_name,
305 pd.sort_name, pd.short_name, pd.directory_name
307 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)');
309 } elseif ($action == 'clean') {
310 // nettoyage du fichier temporaire
311 //exec('rm -Rf ' . $spoolpath);
312 $report[] = 'Import finit.';
314 foreach($report as $t) {
315 // affiche les lignes de report
316 echo "$('#fusionax').append('" . $t . "<br/>');\n";
319 // lance le prochain script s'il y en a un
320 echo "$.getScript('fusionax/import/" . $next . "');";
322 // exit pour ne pas afficher la page template par défaut
326 function handler_view($page, $action = '')
328 $page->changeTpl('fusionax/view.tpl');
329 if ($action == 'create') {
330 XDB
::execute('DROP VIEW IF EXISTS fusionax_deceased');
331 XDB
::execute("CREATE VIEW fusionax_deceased AS
332 SELECT p.pid, a.ax_id, pd.private_name, pd.promo, p.deathdate AS deces_xorg, a.Date_deces AS deces_ax
334 INNER JOIN profile_display AS pd ON (p.pid = pd.pid)
335 INNER JOIN fusionax_anciens AS a ON (a.ax_id = p.ax_id)
336 WHERE p.deathdate != a.Date_deces OR (p.deathdate IS NULL AND a.Date_deces != '0000-00-00')");
337 XDB
::execute('DROP VIEW IF EXISTS fusionax_promo');
338 XDB
::execute("CREATE VIEW fusionax_promo AS
339 SELECT p.pid, p.ax_id, pd.private_name, pd.promo, pe.entry_year AS promo_etude_xorg, f.groupe_promo,
340 f.promotion_etude AS promo_etude_ax, pe.grad_year AS promo_sortie_xorg
342 INNER JOIN profile_display AS pd ON (p.pid = pd.pid)
343 INNER JOIN profile_education AS pe ON (p.pid = pe.pid AND FIND_IN_SET('primary', pe.flags))
344 INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id)
345 WHERE (f.groupe_promo = 'X' AND pd.promo != CONCAT('X', f.promotion_etude)
346 AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)
347 AND !(f.promotion_etude = pe.entry_year + 2 AND pe.grad_year = pe.entry_year + 5)
348 AND f.promotion_etude != 0)
349 OR (f.groupe_promo = 'D' AND f.promotion_etude != pe.grad_year)
350 OR (f.groupe_promo = 'M' AND f.promotion_etude != pe.entry_year)
352 $page->trigSuccess('Les VIEW ont bien été créées.');
356 /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */
357 private static function clear_wrong_in_xorg($pid)
359 $res = XDB
::execute('UPDATE fusionax_xorg_anciens
361 WHERE pid = {?}', $pid);
365 return XDB
::affectedRows() / 2;
368 /* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX
369 * (mises à part les promo 1921, 1922, 1923, 1924, 1925, 1927 qui ne figurent pas dans les données de l'AX)*/
370 private static function find_wrong_in_xorg($limit = 10)
372 return XDB
::iterator('SELECT u.promo, u.pid, u.private_name
373 FROM fusionax_xorg_anciens AS u
374 WHERE NOT EXISTS (SELECT *
375 FROM fusionax_anciens AS f
376 WHERE f.ax_id = u.ax_id)
377 AND u.ax_id IS NOT NULL AND promo NOT IN (\'X1921\', \'X1922\', \'X1923\', \'X1924\', \'X1925\', \'X1927\')');
380 /** Lier les identifiants d'un ancien dans les deux annuaires
381 * @param user_id identifiant dans l'annuaire X.org
382 * @param matricule_ax identifiant dans l'annuaire de l'AX
383 * @return 0 si la liaison a échoué, 1 sinon
385 private static function link_by_ids($pid, $ax_id)
387 $res = XDB
::execute('UPDATE fusionax_import AS i
388 INNER JOIN fusionax_xorg_anciens AS u
389 SET u.ax_id = i.ax_id,
391 i.date_match_id = NOW()
392 WHERE i.ax_id = {?} AND u.pid = {?}
393 AND (u.ax_id != {?} OR u.ax_id IS NULL
394 OR i.pid != {?} OR i.pid IS NULL)',
395 $ax_id, $pid, $ax_id, $pid);
399 return XDB
::affectedRows() / 2;
402 /** Recherche automatique d'anciens à lier entre les deux annuaires
403 * @param limit nombre d'anciens à trouver au max
404 * @param sure si true, ne trouve que des anciens qui sont quasi sûrs
405 * @return un XOrgDBIterator sur les entrées avec display_name, promo,
406 * pid, ax_id et display_name_ax
408 private static function find_easy_to_link($limit = 10, $sure = false
)
410 $easy_to_link = XDB
::iterator("
411 SELECT u.private_name, u.promo, u.pid, ax.ax_id,
412 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (', ax.groupe_promo, ax.promotion_etude, ')') AS display_name_ax,
413 COUNT(*) AS nbMatches
414 FROM fusionax_anciens AS ax
415 INNER JOIN fusionax_import AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
416 LEFT JOIN fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
417 AND u.promo = CONCAT(ax.groupe_promo, ax.promotion_etude)
418 AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
419 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
420 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name))
422 HAVING u.pid IS NOT NULL AND nbMatches = 1" . ($limit ?
(' LIMIT ' . $limit) : ''));
423 if ($easy_to_link->total() > 0 ||
$sure) {
424 return $easy_to_link;
426 return XDB
::iterator("
427 SELECT u.private_name, u.promo, u.pid, ax.ax_id,
428 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (', ax.groupe_promo, ax.promotion_etude, ')') AS display_name_ax,
429 COUNT(*) AS nbMatches
430 FROM fusionax_anciens AS ax
431 INNER JOIN fusionax_import AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
432 LEFT JOIN fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
433 AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
434 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
435 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name)
436 AND u.promo < CONCAT(ax.groupe_promo, ax.promotion_etude + 2)
437 AND u.promo > CONCAT(ax.groupe_promo, ax.promotion_etude - 2))
439 HAVING u.pid IS NOT NULL AND nbMatches = 1" . ($limit ?
(' LIMIT ' . $limit) : ''));
442 /** Module de mise en correspondance les ids */
443 function handler_ids($page, $part = 'main', $pid = null
, $ax_id = null
)
446 $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple');
448 if ($part == 'missingInAX') {
449 // locate all persons from this database that are not in AX's
450 $page->changeTpl('fusionax/idsMissingInAx.tpl');
451 $missingInAX = XDB
::iterator('SELECT promo, pid, private_name
452 FROM fusionax_xorg_anciens
455 $page->assign('missingInAX', $missingInAX);
458 if ($part == 'missingInXorg') {
459 // locate all persons from AX's database that are not here
460 $page->changeTpl('fusionax/idsMissingInXorg.tpl');
461 $missingInXorg = XDB
::iterator("SELECT CONCAT(a.prenom, ' ', a.Nom_usuel) AS private_name,
462 CONCAT(a.groupe_promo, a.promotion_etude) AS promo, a.ax_id
464 INNER JOIN fusionax_anciens AS a USING (ax_id)
465 WHERE fusionax_import.pid IS NULL
467 $page->assign('missingInXorg', $missingInXorg);
470 if ($part == 'wrongInXorg') {
471 // locate all persons from Xorg database that have a bad AX id
472 $page->changeTpl('fusionax/idswrongInXorg.tpl');
473 $wrongInXorg = FusionAxModule
::find_wrong_in_xorg($nbToLink);
474 $page->assign('wrongInXorg', $wrongInXorg);
477 if ($part == 'cleanwronginxorg') {
478 $linksToDo = FusionAxModule
::find_wrong_in_xorg($nbToLink);
479 while ($l = $linksToDo->next()) {
480 FusionAxModule
::clear_wrong_in_xorg($l['pid']);
482 pl_redirect('fusionax/ids/wrongInXorg');
484 if ($part == 'lier') {
485 if (Post
::has('user_id') && Post
::has('matricule_ax')) {
486 FusionAxModule
::link_by_ids(Post
::i('pid'), Post
::v('ax_id'));
489 if ($part == 'link') {
490 FusionAxModule
::link_by_ids($pid, $ax_id);
493 if ($part == 'linknext') {
494 $linksToDo = FusionAxModule
::find_easy_to_link($nbToLink);
495 while ($l = $linksToDo->next()) {
496 FusionAxModule
::link_by_ids($l['pid'], $l['ax_id']);
498 pl_redirect('fusionax/ids#autolink');
500 if ($part == 'linkall') {
501 $linksToDo = FusionAxModule
::find_easy_to_link(0);
502 while ($l = $linksToDo->next()) {
503 FusionAxModule
::link_by_ids($l['pid'], $l['ax_id']);
507 $page->changeTpl('fusionax/ids.tpl');
508 $missingInAX = XDB
::query('SELECT COUNT(*)
509 FROM fusionax_xorg_anciens
510 WHERE ax_id IS NULL');
512 $page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
514 $missingInXorg = XDB
::query('SELECT COUNT(*)
517 if ($missingInXorg) {
518 $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
520 $wrongInXorg = FusionAxModule
::find_wrong_in_xorg($nbToLink);
521 if ($wrongInXorg->total() > 0) {
522 $page->assign('wrongInXorg', $wrongInXorg->total());
524 $easyToLink = FusionAxModule
::find_easy_to_link($nbToLink);
525 if ($easyToLink->total() > 0) {
526 $page->assign('nbMatch', $easyToLink->total());
527 $page->assign('easyToLink', $easyToLink);
532 function handler_deceased($page, $action = '')
534 if ($action == 'updateXorg') {
535 XDB
::execute('UPDATE fusionax_deceased
536 SET deces_xorg = deces_ax
537 WHERE deces_xorg IS NULL');
539 if ($action == 'updateAX') {
540 XDB
::execute('UPDATE fusionax_deceased
541 SET deces_ax = deces_xorg
542 WHERE deces_ax = "0000-00-00"');
544 if ($action == 'update') {
545 if (Post
::has('pid') && Post
::has('date')) {
546 XDB
::execute('UPDATE fusionax_deceased
547 SET deces_ax = {?}, deces_xorg = {?}
549 Post
::v('date'), Post
::v('date'), Post
::i('pid'));
552 $page->changeTpl('fusionax/deceased.tpl');
554 $deceasedErrorsSql = XDB
::query('SELECT COUNT(*) FROM fusionax_deceased');
555 $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
556 $res = XDB
::iterator('SELECT pid, ax_id, promo, private_name, deces_ax
557 FROM fusionax_deceased
558 WHERE deces_xorg IS NULL
560 $page->assign('nbDeceasedMissingInXorg', $res->total());
561 $page->assign('deceasedMissingInXorg', $res);
562 $res = XDB
::iterator('SELECT pid, ax_id, promo, private_name, deces_xorg
563 FROM fusionax_deceased
564 WHERE deces_ax = "0000-00-00"
566 $page->assign('nbDeceasedMissingInAX', $res->total());
567 $page->assign('deceasedMissingInAX', $res);
568 $res = XDB
::iterator('SELECT pid, ax_id, promo, private_name, deces_xorg, deces_ax
569 FROM fusionax_deceased
570 WHERE deces_xorg != "0000-00-00" AND deces_ax != "0000-00-00"');
571 $page->assign('nbDeceasedDifferent', $res->total());
572 $page->assign('deceasedDifferent', $res);
575 function handler_promo($page, $action = '')
577 $page->changeTpl('fusionax/promo.tpl');
578 $res = XDB
::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
580 WHERE !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg)
581 AND !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg)
582 AND !(promo_etude_ax = promo_etude_xorg + 1) AND groupe_promo = 'X'
583 ORDER BY promo_etude_xorg");
584 $nbMissmatchingPromos = $res->total();
585 $page->assign('nbMissmatchingPromos', $res->total());
586 $page->assign('missmatchingPromos', $res);
588 $res = XDB
::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
590 WHERE promo_etude_ax = promo_etude_xorg + 1 AND groupe_promo = 'X'
591 ORDER BY promo_etude_xorg");
592 $nbMissmatchingPromos +
= $res->total();
593 $page->assign('nbMissmatchingPromos1', $res->total());
594 $page->assign('missmatchingPromos1', $res);
596 $res = XDB
::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
598 WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg AND groupe_promo = 'X'
599 ORDER BY promo_etude_xorg");
600 $nbMissmatchingPromos +
= $res->total();
601 $page->assign('nbMissmatchingPromos2', $res->total());
602 $page->assign('missmatchingPromos2', $res);
604 $res = XDB
::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
606 WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg AND groupe_promo = 'X'
607 ORDER BY promo_etude_xorg");
608 $nbMissmatchingPromos +
= $res->total();
609 $page->assign('nbMissmatchingPromos3', $res->total());
610 $page->assign('missmatchingPromos3', $res);
612 $res = XDB
::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
614 WHERE groupe_promo = 'M'
615 ORDER BY promo_etude_xorg");
616 $nbMissmatchingPromos +
= $res->total();
617 $page->assign('nbMissmatchingPromosM', $res->total());
618 $page->assign('missmatchingPromosM', $res);
621 $res = XDB
::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
623 WHERE groupe_promo = 'D'
624 ORDER BY promo_etude_xorg");
625 $nbMissmatchingPromos +
= $res->total();
626 $page->assign('nbMissmatchingPromosD', $res->total());
627 $page->assign('missmatchingPromosD', $res);
629 $page->assign('nbMissmatchingPromosTotal', $nbMissmatchingPromos);
632 private function format($string)
634 return preg_replace('/(\s+|\-)/', '', $string);
637 private function retrieve_firstnames()
639 $res = XDB
::rawFetchAllAssoc('SELECT p.pid, p.ax_id, p.hrpid,
640 f.prenom, ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary
641 FROM fusionax_anciens AS f
642 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
643 INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid)
644 WHERE f.prenom NOT IN (ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary)');
647 foreach ($res as $item) {
648 if (!($item['firstname_ordinary'] != '' ||
$item['firstname_main'] != $item['firstname_initial'])) {
649 $ax = $this->format(mb_strtolower(replace_accent($item['prenom'])));
650 $xorg = $this->format(mb_strtolower(replace_accent($item['firstname_main'])));
660 function handler_names($page, $action = '', $csv = false
)
662 $page->changeTpl('fusionax/names.tpl');
664 if ($action == 'first') {
665 $res = $this->retrieve_firstnames();
667 pl_cached_content_headers('text/x-csv', 'utf-8', 1, 'firstnames.csv');
669 $csv = fopen('php://output', 'w');
670 fputcsv($csv, array('pid', 'ax_id', 'hrpid', 'AX', 'initial', 'principal', 'ordinaire'), ';');
671 foreach ($res as $item) {
672 fputcsv($csv, $item, ';');
677 $page->assign('firstnameIssues', $res);
679 } elseif ($action == 'last' ||
$action == 'last3' ||
$action == 'last2' ||
$action == 'last1') {
680 $ax_patro = "(IF(f.partic_patro, CONCAT(f.partic_patro, CONCAT(' ', f.Nom_patronymique)), f.Nom_patronymique) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))";
681 $ax_ordinary = "(IF(f.partic_nom, CONCAT(f.partic_nom, CONCAT(' ', f.Nom_usuel)), f.Nom_usuel) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))";
682 $ax_full = "(f.Nom_complet NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))";
686 $where = $ax_patro . ' OR ' . $ax_ordinary . ' OR ' . $ax_full;
689 $where = $ax_patro . ' AND ' . $ax_ordinary . ' AND ' . $ax_full;
692 $where = '(' . $ax_patro . ' AND ' . $ax_ordinary . ' AND NOT ' . $ax_full . ') OR ('
693 . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND ' . $ax_full . ') OR ('
694 . 'NOT ' . $ax_patro . ' AND ' . $ax_ordinary . ' AND ' . $ax_full . ')';
697 $where = '(' . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND NOT ' . $ax_full . ') OR ('
698 . 'NOT ' . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND ' . $ax_full . ') OR ('
699 . 'NOT ' . $ax_patro . ' AND ' . $ax_ordinary . ' AND NOT ' . $ax_full . ')';
703 $res = XDB
::rawFetchAllAssoc('SELECT p.pid, p.ax_id, p.hrpid,
704 f.Nom_patronymique, f.Nom_usuel, f.Nom_complet,
705 ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary
706 FROM fusionax_anciens AS f
707 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
708 INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid)
712 function format($string)
714 $string = preg_replace('/\-/', ' ', $string);
715 return preg_replace('/\s+/', ' ', $string);
719 pl_cached_content_headers('text/x-csv', 'utf-8', 1, 'lastnames.csv');
721 $csv = fopen('php://output', 'w');
722 fputcsv($csv, array('pid', 'ax_id', 'hrpid', 'AX patro', 'AX usuel', 'AX complet', 'initial', 'principal', 'marital', 'ordinaire'), ';');
723 foreach ($res as $item) {
725 'Nom_patronymique' => format(mb_strtolower(replace_accent($item['Nom_patronymique']))),
726 'Nom_usuel' => format(mb_strtolower(replace_accent($item['Nom_usuel']))),
727 'Nom_complet' => format(mb_strtolower(replace_accent($item['Nom_complet'])))
730 'lastname_initial' => format(mb_strtolower(replace_accent($item['lastname_initial']))),
731 'lastname_main' => format(mb_strtolower(replace_accent($item['lastname_main']))),
732 'lastname_ordinary' => format(mb_strtolower(replace_accent($item['lastname_ordinary'])))
735 if (!in_array($ax['Nom_patronymique'], $xorg) ||
!in_array($ax['Nom_usuel'], $xorg) ||
!in_array($ax['Nom_complet'], $xorg)) {
736 fputcsv($csv, $item, ';');
742 $page->assign('lastnameIssues', $res);
743 $page->assign('total', count($res));
744 $page->assign('issuesTypes', array(
745 'last' => "1, 2 ou 3 noms de l'AX manquant",
746 'last1' => "1 nom de l'AX manquant",
747 'last2' => "2 noms de l'AX manquant",
748 'last3' => "3 noms de l'AX manquant"
752 $res = XDB
::query('SELECT COUNT(*)
753 FROM fusionax_anciens AS f
754 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)');
755 $page->assign('total', $res->fetchOneCell());
757 $res = XDB
::rawFetchOneCell("SELECT COUNT(*)
758 FROM fusionax_anciens AS f
759 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
760 INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid)
761 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)
762 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)
763 OR f.Nom_complet NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary)");
764 $page->assign('lastnameIssues', $res);
766 $res = XDB
::rawFetchOneCell('SELECT COUNT(*)
767 FROM fusionax_anciens AS f
768 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
769 INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid)
770 WHERE f.prenom NOT IN (ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary)');
771 $page->assign('firstnameIssues', count($this->retrieve_firstnames()));
773 $page->assign('action', $action);
776 function handler_edu($page, $action = '')
778 $page->changeTpl('fusionax/education.tpl');
780 $missingEducation = XDB
::rawIterator("SELECT DISTINCT(f.Intitule_formation)
781 FROM fusionax_formations AS f
782 WHERE f.Intitule_formation != '' AND NOT EXISTS (SELECT *
783 FROM profile_education_enum AS e
784 WHERE f.Intitule_formation = e.name)");
785 $missingDegree = XDB
::rawIterator("SELECT DISTINCT(f.Intitule_diplome)
786 FROM fusionax_formations AS f
787 WHERE f.Intitule_diplome != '' AND NOT EXISTS (SELECT *
788 FROM profile_education_degree_enum AS e
789 WHERE f.Intitule_diplome = e.abbreviation)");
790 $missingCouple = XDB
::rawIterator("SELECT DISTINCT(f.Intitule_formation) AS edu, f.Intitule_diplome AS degree, ee.id AS eduid, de.id AS degreeid
791 FROM fusionax_formations AS f
792 INNER JOIN profile_education_enum AS ee ON (f.Intitule_formation = ee.name)
793 INNER JOIN profile_education_degree_enum AS de ON (f.Intitule_diplome = de.abbreviation)
794 WHERE f.Intitule_diplome != '' AND f.Intitule_formation != ''
795 AND NOT EXISTS (SELECT *
796 FROM profile_education_degree AS d
797 WHERE ee.id = d.eduid AND de.id = d.degreeid)");
799 $page->assign('missingEducation', $missingEducation);
800 $page->assign('missingDegree', $missingDegree);
801 $page->assign('missingCouple', $missingCouple);
802 $page->assign('missingEducationCount', $missingEducation->total());
803 $page->assign('missingDegreeCount', $missingDegree->total());
804 $page->assign('missingCoupleCount', $missingCouple->total());
807 function handler_corps($page)
809 $page->changeTpl('fusionax/corps.tpl');
811 $missingCorps = XDB
::rawIterator('SELECT DISTINCT(f.corps_sortie) AS name
812 FROM fusionax_anciens AS f
813 WHERE NOT EXISTS (SELECT *
814 FROM profile_corps_enum AS c
815 WHERE f.corps_sortie = c.abbreviation)');
816 $missingGrade = XDB
::rawIterator('SELECT DISTINCT(f.grade) AS name
817 FROM fusionax_anciens AS f
818 WHERE NOT EXISTS (SELECT *
819 FROM profile_corps_rank_enum AS c
820 WHERE f.grade = c.name)');
822 $page->assign('missingCorps', $missingCorps);
823 $page->assign('missingGrade', $missingGrade);
824 $page->assign('missingCorpsCount', $missingCorps->total());
825 $page->assign('missingGradeCount', $missingGrade->total());
828 function handler_issues_deathdate($page, $action = '')
830 $page->changeTpl('fusionax/deathdate_issues.tpl');
831 if ($action == 'edit') {
832 S
::assert_xsrf_token();
834 $issues = XDB
::rawIterRow('SELECT p.pid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate
835 FROM profile_merge_issues AS pm
836 INNER JOIN profiles AS p ON (pm.pid = p.pid)
837 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
838 WHERE FIND_IN_SET(\'deathdate\', pm.issues)
839 ORDER BY pd.directory_name');
840 while (list($pid, $name, $promo, $deathAX, $deathXorg) = $issues->next()) {
841 $choiceAX = Post
::has('AX_' . $pid);
842 $choiceXorg = Post
::has('XORG_' . $pid);
843 if (!($choiceAX ||
$choiceXorg)) {
848 XDB
::execute('UPDATE profiles AS p
849 INNER JOIN profile_merge_issues AS pm ON (pm.pid = p.pid)
850 SET p.deathdate = pm.deathdate_ax, p.deathdate_rec = NOW()
851 WHERE p.pid = {?}', $pid);
853 XDB
::execute("UPDATE profile_merge_issues
854 SET issues = REPLACE(issues, 'deathdate', '')
855 WHERE pid = {?}", $pid());
856 $page->trigSuccess("La date de décès de $name ($promo) a bien été corrigée.");
860 $issues = XDB
::rawFetchAllAssoc('SELECT p.pid, p.hrpid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate
861 FROM profile_merge_issues AS pm
862 INNER JOIN profiles AS p ON (pm.pid = p.pid)
863 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
864 WHERE FIND_IN_SET(\'deathdate\', pm.issues)
865 ORDER BY pd.directory_name');
866 $page->assign('issues', $issues);
867 $page->assign('total', count($issues));
870 function handler_issues_promo($page, $action = '')
872 $page->changeTpl('fusionax/promo_issues.tpl');
873 if ($action == 'edit') {
874 S
::assert_xsrf_token();
876 $issues = XDB
::rawIterRow('SELECT p.pid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year
877 FROM profile_merge_issues AS pm
878 INNER JOIN profiles AS p ON (pm.pid = p.pid)
879 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
880 INNER JOIN profile_education AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags))
881 WHERE FIND_IN_SET(\'promo\', pm.issues)
882 ORDER BY pd.directory_name');
883 while (list($pid, $name, $promo, $deathAX, $deathXorgEntry, $deathXorgGrad) = $issues->next()) {
884 $choiceXorg = Post
::has('XORG_' . $pid);
885 if (!(Post
::has('display_' . $pid) && Post
::has('entry_' . $pid) && Post
::has('grad_' . $pid))) {
889 $display = Post
::i('display_' . $pid);
890 $entry = Post
::i('entry_' . $pid);
891 $grad = Post
::i('grad_' . $pid);
892 if (!(($grad <= $entry +
5 && $grad >= $entry +
3) && ($display >= $entry && $display <= $grad - 3))) {
893 $page->trigError("La promotion de $name n'a pas été corrigée.");
896 XDB
::execute('UPDATE profile_display
898 WHERE pid = {?}', 'X' . $display, $pid);
899 XDB
::execute('UPDATE profile_education
900 SET entry_year = {?}, grad_year = {?}
901 WHERE pid = {?} AND FIND_IN_SET(\'primary\', flags)', $entry, $grad, $pid);
902 $page->trigSuccess("La promotion de $name a bien été corrigée.");
906 $issues = XDB
::rawFetchAllAssoc('SELECT p.pid, p.hrpid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year
907 FROM profile_merge_issues AS pm
908 INNER JOIN profiles AS p ON (pm.pid = p.pid)
909 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
910 INNER JOIN profile_education AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags))
911 WHERE FIND_IN_SET(\'promo\', pm.issues)
912 ORDER BY pd.directory_name');
913 $page->assign('issues', $issues);
914 $page->assign('total', count($issues));
917 function handler_issues($page, $action = '')
919 static $issueList = array(
921 'phone' => 'téléphones',
922 'education' => 'formations',
923 'address' => 'adresses',
926 static $typeList = array(
928 'phone' => 'general',
929 'education' => 'general',
930 'address' => 'adresses',
934 if (!array_key_exists($action, $issueList)) {
935 pl_redirect('fusionax');
937 $total = XDB
::fetchOneCell('SELECT COUNT(*)
938 FROM profile_merge_issues
939 WHERE FIND_IN_SET({?}, issues)', $action);
941 pl_redirect('fusionax');
944 $issues = XDB
::fetchAllAssoc('SELECT p.hrpid, pd.directory_name, pd.promo
945 FROM profile_merge_issues AS pm
946 INNER JOIN profiles AS p ON (pm.pid = p.pid)
947 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
948 WHERE FIND_IN_SET({?}, pm.issues)
949 ORDER BY pd.directory_name
950 LIMIT 100', $action);
952 $page->changeTpl('fusionax/other_issues.tpl');
953 $page->assign('issues', $issues);
954 $page->assign('issue', $issueList[$action]);
955 $page->assign('type', $typeList[$action]);
956 $page->assign('total', $total);
961 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: