projects / platal.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
Use mysqli for LOAD DATA LOCAL INFILE.
[platal.git] / modules / fusionax.php
CommitLineData
b9ad0878
PC		 1<?php
2/***************************************************************************
c441aabe 3 * Copyright (C) 2003-2014 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 91 {
85ea1310 92 global $globals;
b9ad0878
PC		 93 if ($action == 'index') {
94 $page->changeTpl('fusionax/import.tpl');
b9ad0878
PC		 95 return;
96 }
22f043e4 97
cc8ea8b2 98 // toutes les actions sont faites en ajax en utilisant jquery
ddc4c642 99 header('Content-type: text/javascript; charset=utf-8');
22f043e4 100
cc8ea8b2
PC		 101 // log des actions
102 $report = array();
22f043e4 103
73be4434 104 $modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/';
ddc4c642 105 $spoolpath = realpath(dirname(__FILE__) . '/../spool/fusionax/') . '/';
22f043e4 106
b9ad0878 107 if ($action == 'launch') {
ddc4c642
SJ		 108 if ($file == '') {
109 $report[] = 'Nom de fichier non renseigné.';
110 } elseif (!file_exists(dirname(__FILE__) . '/../spool/fusionax/' . $file)) {
111 $report[] = 'Le fichier ne se situe pas au bon endroit.';
112 } else {
113 // séparation de l'archive en fichiers par tables
114 $file = $spoolpath . $file;
ddc4c642
SJ		 115 // Split export into specialised files
116 exec('grep "^AD" ' . $file . ' > ' . $spoolpath . 'Adresses.txt');
117 exec('grep "^AN" ' . $file . ' > ' . $spoolpath . 'Anciens.txt');
0efb08e6
SJ		 118 exec('grep "^FO.[0-9]\{4\}[MD][0-9]\{3\}.Etudiant" ' . $file . ' > ' . $spoolpath . 'Formations_MD.txt');
119 exec('grep "^FO.[0-9]\{4\}[MD][0-9]\{3\}.Doct. de" ' . $file . ' >> ' . $spoolpath . 'Formations_MD.txt');
ddc4c642
SJ		 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');
0efb08e6 125 exec('mv -f ' . $spoolpath . 'Formations_MD_out.txt ' . $spoolpath . 'Formations_MD.txt');
ddc4c642
SJ		 126 $report[] = 'Fichier parsé.';
127 $report[] = 'Import dans la base en cours...';
9a2876cb
SJ		 128 XDB::execute("UPDATE profiles
129 SET ax_id = NULL
130 WHERE ax_id = ''");
149360d4 131 $next = 'integrateSQL';
ddc4c642 132 }
73be4434 133 } elseif ($action == 'integrateSQL') {
cc8ea8b2
PC		 134 // intégration des données dans la base MySQL
135 // liste des fichiers sql à exécuter
136 $filesSQL = array(
ddc4c642
SJ		 137 0 => 'Activites.sql',
138 1 => 'Adresses.sql',
139 2 => 'Anciens.sql',
140 3 => 'Formations.sql',
0efb08e6
SJ		 141 4 => 'Entreprises.sql',
142 5 => 'Formations_MD.sql'
ddc4c642
SJ		 143 );
144 if ($file != '') {
cc8ea8b2 145 // récupère le contenu du fichier sql
ddc4c642 146 $queries = explode(';', file_get_contents($modulepath . $filesSQL[$file]));
85ea1310
AL		 147 $db = mysqli_init();
148 $db->options(MYSQLI_OPT_LOCAL_INFILE, true);
149 $db->real_connect($globals->dbhost, $globals->dbuser, $globals->dbpwd, $globals->dbdb);
150 $db->autocommit(true);
151 $db->set_charset($globals->dbcharset);
73be4434
SJ		 152 foreach ($queries as $q) {
153 if (trim($q)) {
154 // coupe le fichier en requêtes individuelles
155 if (substr($q, 0, 2) == '--') {
156 // affiche les commentaires dans le report
157 $lines = explode("\n", $q);
158 $l = $lines[0];
159 $report[] = addslashes($l);
160 }
161 // exécute la requête
85ea1310
AL		 162 $res = $db->query(str_replace('{?}', $spoolpath, $q));
163 if ($res === false) {
164 throw new XDBException($q, $db->error);
165 }
b9ad0878 166 }
b9ad0878 167 }
85ea1310 168 $db->close();
cc8ea8b2 169 // trouve le prochain fichier à exécuter
ddc4c642 170 $nextfile = $file + 1;
b9ad0878
PC		 171 } else {
172 $nextfile = 0;
173 }
0efb08e6 174 if ($nextfile > 5) {
ddc4c642
SJ		 175 // tous les fichiers ont été exécutés, on passe à l'étape suivante
176 $next = 'adds1920';
b9ad0878 177 } else {
cc8ea8b2 178 // on passe au fichier suivant
ddc4c642
SJ		 179 $next = 'integrateSQL/' . $nextfile;
180 }
181 } elseif ($action == 'adds1920') {
182 // Adds promotion 1920 from AX db.
183 $report[] = 'Ajout de la promotion 1920';
184 $res = XDB::iterator('SELECT prenom, Nom_complet, ax_id
185 FROM fusionax_anciens
186 WHERE promotion_etude = 1920;');
187
ddc4c642
SJ		 188 $eduSchools = DirEnum::getOptions(DirEnum::EDUSCHOOLS);
189 $eduSchools = array_flip($eduSchools);
190 $eduDegrees = DirEnum::getOptions(DirEnum::EDUDEGREES);
191 $eduDegrees = array_flip($eduDegrees);
192 $degreeid = $eduDegrees[Profile::DEGREE_X];
193 $entry_year = 1920;
194 $grad_year = 1923;
195 $promo = 'X1920';
149360d4
SJ		 196 $hrpromo = '1920';
197 $sex = 'male';
ddc4c642
SJ		 198 $xorgId = 19200000;
199 $type = 'x';
200
149360d4
SJ		 201 while ($new = $res->next()) {
202 $firstname = $new['prenom'];
203 $lastname = $new['Nom_complet'];
204 $ax_id = $new['ax_id'];
205 $hrid = User::makeHrid($firstname, $lastname, $hrpromo);
ddc4c642
SJ		 206 $res1 = XDB::query('SELECT COUNT(*)
207 FROM accounts
208 WHERE hruid = {?}', $hrid);
209 $res2 = XDB::query('SELECT COUNT(*)
210 FROM profiles
211 WHERE hrpid = {?}', $hrid);
212 if (is_null($hrid) || $res1->fetchOneCell() > 0 || $res2->fetchOneCell() > 0) {
213 $report[] = $ax_id . ' non ajouté';
214 }
215 $fullName = $firstname . ' ' . $lastname;
216 $directoryName = $lastname . ' ' . $firstname;
217 ++$xorgId;
218
00ba8a74
SJ		 219 XDB::execute('INSERT INTO profiles (hrpid, xorg_id, ax_id, sex)
220 VALUES ({?}, {?}, {?}, {?})',
ddc4c642
SJ		 221 $hrid, $xorgId, $ax_id, $sex);
222 $pid = XDB::insertId();
149360d4
SJ		 223 XDB::execute('INSERT INTO profile_public_names (pid, lastname_initial, firstname_initial, lastname_main, firstname_main)
224 VALUES ({?}, {?}, {?}, {?}, {?})',
225 $pid, $lastname, $firstname, $lastname, $firstname);
00ba8a74
SJ		 226 XDB::execute('INSERT INTO profile_display (pid, yourself, public_name, private_name,
227 directory_name, short_name, sort_name, promo)
228 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
ddc4c642 229 $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo);
00ba8a74
SJ		 230 XDB::execute('INSERT INTO profile_education (pid, eduid, degreeid, entry_year, grad_year, flags)
231 VALUES ({?}, {?}, {?}, {?}, {?}, {?})',
ddc4c642 232 $pid, $eduSchools[Profile::EDU_X], $degreeid, $entry_year, $grad_year, 'primary');
ad1c9939 233 XDB::execute('INSERT INTO accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, lastname, firstname, sex)
149360d4
SJ		 234 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
235 $hrid, $type, 0, 'pending', $fullName, $directoryName, $firstname, $lastname, $firstname, $sex);
ddc4c642 236 $uid = XDB::insertId();
00ba8a74
SJ		 237 XDB::execute('INSERT INTO account_profiles (uid, pid, perms)
238 VALUES ({?}, {?}, {?})',
ddc4c642 239 $uid, $pid, 'owner');
b9ad0878 240 }
ddc4c642 241 $report[] = 'Promo 1920 ajoutée.';
149360d4
SJ		 242 $next = 'adds2011';
243 } elseif ($action == 'adds2011') {
244 // Adds promotion 2011 from AX db.
245 $report[] = 'Ajout des élèves manquant de la promotion 2011';
246 $res = XDB::iterator("SELECT prenom, Nom_complet, ax_id, Civilite
247 FROM fusionax_anciens
248 WHERE promotion_etude = 2011 AND groupe_promo = 'X'
249 AND NOT EXISTS (SELECT 1
250 FROM profiles
251 WHERE profiles.ax_id = fusionax_anciens.ax_id)");
252
253 $eduSchools = DirEnum::getOptions(DirEnum::EDUSCHOOLS);
254 $eduSchools = array_flip($eduSchools);
255 $eduDegrees = DirEnum::getOptions(DirEnum::EDUDEGREES);
256 $eduDegrees = array_flip($eduDegrees);
257 $degreeid = $eduDegrees[Profile::DEGREE_X];
258 $entry_year = 2011;
259 $grad_year = 2014;
260 $promo = 'X2011';
261 $hrpromo = '2011';
262 $type = 'x';
263
264 while ($new = $res->next()) {
265 $firstname = $new['prenom'];
266 $lastname = $new['Nom_complet'];
267 $ax_id = $new['ax_id'];
268 $civilite = $new['Civilite'];
269 $hrid = User::makeHrid($firstname, $lastname, $hrpromo);
270 $res1 = XDB::query('SELECT COUNT(*)
271 FROM accounts
272 WHERE hruid = {?}', $hrid);
273 $res2 = XDB::query('SELECT COUNT(*)
274 FROM profiles
275 WHERE hrpid = {?}', $hrid);
276 if (is_null($hrid) || $res1->fetchOneCell() > 0 || $res2->fetchOneCell() > 0) {
277 $report[] = $ax_id . ' non ajouté';
278 }
279 $fullName = $firstname . ' ' . $lastname;
280 $directoryName = $lastname . ' ' . $firstname;
281 if ($civilite == 'M') {
282 $sex = 'male';
283 } else {
284 $sex = 'female';
285 }
286
287 XDB::execute('INSERT INTO profiles (hrpid, ax_id, sex, title)
288 VALUES ({?}, {?}, {?}, {?})',
289 $hrid, $ax_id, $sex, $civilite);
290 $pid = XDB::insertId();
291 XDB::execute('INSERT INTO profile_public_names (pid, lastname_initial, firstname_initial, lastname_main, firstname_main)
292 VALUES ({?}, {?}, {?}, {?}, {?})',
293 $pid, $lastname, $firstname, $lastname, $firstname);
294 XDB::execute('INSERT INTO profile_display (pid, yourself, public_name, private_name,
295 directory_name, short_name, sort_name, promo)
296 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
297 $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo);
298 XDB::execute('INSERT INTO profile_education (pid, eduid, degreeid, entry_year, grad_year, flags)
299 VALUES ({?}, {?}, {?}, {?}, {?}, {?})',
300 $pid, $eduSchools[Profile::EDU_X], $degreeid, $entry_year, $grad_year, 'primary');
301 XDB::execute('INSERT INTO accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, lastname, firstname, sex)
302 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})',
303 $hrid, $type, 0, 'pending', $fullName, $directoryName, $firstname, $lastname, $firstname, $sex);
304 $uid = XDB::insertId();
305 XDB::execute('INSERT INTO account_profiles (uid, pid, perms)
306 VALUES ({?}, {?}, {?})',
307 $uid, $pid, 'owner');
308 }
309 $report[] = 'Promo 2011 ajoutée.';
310
ddc4c642
SJ		 311 $next = 'view';
312 } elseif ($action == 'view') {
313 XDB::execute('CREATE OR REPLACE ALGORITHM=MERGE VIEW fusionax_xorg_anciens AS
314 SELECT p.pid, p.ax_id, pd.promo, pd.private_name, pd.public_name,
315 pd.sort_name, pd.short_name, pd.directory_name
316 FROM profiles AS p
ad1c9939 317 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)');
ddc4c642 318 $next = 'clean';
73be4434 319 } elseif ($action == 'clean') {
cc8ea8b2 320 // nettoyage du fichier temporaire
0efb08e6 321 //exec('rm -Rf ' . $spoolpath);
ddc4c642 322 $report[] = 'Import finit.';
b9ad0878 323 }
73be4434 324 foreach($report as $t) {
cc8ea8b2 325 // affiche les lignes de report
ddc4c642 326 echo "$('#fusionax').append('" . $t . "<br/>');\n";
73be4434 327 }
b9ad0878 328 if (isset($next)) {
cc8ea8b2 329 // lance le prochain script s'il y en a un
ddc4c642 330 echo "$.getScript('fusionax/import/" . $next . "');";
b9ad0878 331 }
cc8ea8b2 332 // exit pour ne pas afficher la page template par défaut
b9ad0878
PC		 333 exit;
334 }
22f043e4 335
26ba053e 336 function handler_view($page, $action = '')
29459c49 337 {
29459c49
SJ		 338 $page->changeTpl('fusionax/view.tpl');
339 if ($action == 'create') {
340 XDB::execute('DROP VIEW IF EXISTS fusionax_deceased');
723ea674 341 XDB::execute("CREATE VIEW fusionax_deceased AS
ddc4c642
SJ		 342 SELECT p.pid, a.ax_id, pd.private_name, pd.promo, p.deathdate AS deces_xorg, a.Date_deces AS deces_ax
343 FROM profiles AS p
344 INNER JOIN profile_display AS pd ON (p.pid = pd.pid)
345 INNER JOIN fusionax_anciens AS a ON (a.ax_id = p.ax_id)
723ea674 346 WHERE p.deathdate != a.Date_deces OR (p.deathdate IS NULL AND a.Date_deces != '0000-00-00')");
29459c49 347 XDB::execute('DROP VIEW IF EXISTS fusionax_promo');
bb375055 348 XDB::execute("CREATE VIEW fusionax_promo AS
9a2876cb 349 SELECT p.pid, p.ax_id, pd.private_name, pd.promo, pe.entry_year AS promo_etude_xorg, f.groupe_promo,
ddc4c642
SJ		 350 f.promotion_etude AS promo_etude_ax, pe.grad_year AS promo_sortie_xorg
351 FROM profiles AS p
352 INNER JOIN profile_display AS pd ON (p.pid = pd.pid)
caa04db8 353 INNER JOIN profile_education AS pe ON (p.pid = pe.pid AND FIND_IN_SET('primary', pe.flags))
ddc4c642 354 INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id)
bb375055 355 WHERE (f.groupe_promo = 'X' AND pd.promo != CONCAT('X', f.promotion_etude)
caa04db8
SJ		 356 AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)
357 AND !(f.promotion_etude = pe.entry_year + 2 AND pe.grad_year = pe.entry_year + 5)
358 AND f.promotion_etude != 0)
bb375055
SJ		 359 OR (f.groupe_promo = 'D' AND f.promotion_etude != pe.grad_year)
360 OR (f.groupe_promo = 'M' AND f.promotion_etude != pe.entry_year)
361 GROUP BY p.pid");
ddc4c642 362 $page->trigSuccess('Les VIEW ont bien été créées.');
29459c49
SJ		 363 }
364 }
365
366 /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */
ddc4c642 367 private static function clear_wrong_in_xorg($pid)
29459c49 368 {
ddc4c642
SJ		 369 $res = XDB::execute('UPDATE fusionax_xorg_anciens
370 SET ax_id = NULL
371 WHERE pid = {?}', $pid);
29459c49
SJ		 372 if (!$res) {
373 return 0;
374 }
375 return XDB::affectedRows() / 2;
376 }
377
aab2ffdd 378 /* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX
9a2876cb 379 * (mises à part les promo 1921, 1922, 1923, 1924, 1925, 1927 qui ne figurent pas dans les données de l'AX)*/
29459c49
SJ		 380 private static function find_wrong_in_xorg($limit = 10)
381 {
ddc4c642 382 return XDB::iterator('SELECT u.promo, u.pid, u.private_name
29459c49
SJ		 383 FROM fusionax_xorg_anciens AS u
384 WHERE NOT EXISTS (SELECT *
385 FROM fusionax_anciens AS f
ddc4c642 386 WHERE f.ax_id = u.ax_id)
9a2876cb 387 AND u.ax_id IS NOT NULL AND promo NOT IN (\'X1921\', \'X1922\', \'X1923\', \'X1924\', \'X1925\', \'X1927\')');
29459c49
SJ		 388 }
389
b65beb64
PC		 390 /** Lier les identifiants d'un ancien dans les deux annuaires
391 * @param user_id identifiant dans l'annuaire X.org
392 * @param matricule_ax identifiant dans l'annuaire de l'AX
393 * @return 0 si la liaison a échoué, 1 sinon
22f043e4 394 */
ddc4c642 395 private static function link_by_ids($pid, $ax_id)
b9ad0878 396 {
ddc4c642 397 $res = XDB::execute('UPDATE fusionax_import AS i
73be4434 398 INNER JOIN fusionax_xorg_anciens AS u
ddc4c642
SJ		 399 SET u.ax_id = i.ax_id,
400 i.pid = u.pid,
73be4434 401 i.date_match_id = NOW()
ddc4c642
SJ		 402 WHERE i.ax_id = {?} AND u.pid = {?}
403 AND (u.ax_id != {?} OR u.ax_id IS NULL
404 OR i.pid != {?} OR i.pid IS NULL)',
405 $ax_id, $pid, $ax_id, $pid);
73be4434 406 if (!$res) {
b9ad0878
PC		 407 return 0;
408 }
73be4434 409 return XDB::affectedRows() / 2;
b9ad0878 410 }
22f043e4 411
b65beb64
PC		 412 /** Recherche automatique d'anciens à lier entre les deux annuaires
413 * @param limit nombre d'anciens à trouver au max
414 * @param sure si true, ne trouve que des anciens qui sont quasi sûrs
e8591429 415 * @return un XOrgDBIterator sur les entrées avec display_name, promo,
ddc4c642 416 * pid, ax_id et display_name_ax
22f043e4 417 */
b65beb64 418 private static function find_easy_to_link($limit = 10, $sure = false)
b9ad0878 419 {
e8591429 420 $easy_to_link = XDB::iterator("
ddc4c642 421 SELECT u.private_name, u.promo, u.pid, ax.ax_id,
ad1c9939 422 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (', ax.groupe_promo, ax.promotion_etude, ')') AS display_name_ax,
73be4434 423 COUNT(*) AS nbMatches
29459c49 424 FROM fusionax_anciens AS ax
ddc4c642
SJ		 425 INNER JOIN fusionax_import AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
426 LEFT JOIN fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
ad1c9939 427 AND u.promo = CONCAT(ax.groupe_promo, ax.promotion_etude)
ddc4c642
SJ		 428 AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
429 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
430 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name))
431 GROUP BY u.pid
432 HAVING u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : ''));
b65beb64
PC		 433 if ($easy_to_link->total() > 0 || $sure) {
434 return $easy_to_link;
435 }
73be4434 436 return XDB::iterator("
ddc4c642 437 SELECT u.private_name, u.promo, u.pid, ax.ax_id,
ad1c9939 438 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (', ax.groupe_promo, ax.promotion_etude, ')') AS display_name_ax,
73be4434 439 COUNT(*) AS nbMatches
29459c49 440 FROM fusionax_anciens AS ax
ddc4c642
SJ		 441 INNER JOIN fusionax_import AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL)
442 LEFT JOIN fusionax_xorg_anciens AS u ON (u.ax_id IS NULL
443 AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name
444 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name
445 OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name)
ad1c9939
SJ		 446 AND u.promo < CONCAT(ax.groupe_promo, ax.promotion_etude + 2)
447 AND u.promo > CONCAT(ax.groupe_promo, ax.promotion_etude - 2))
ddc4c642
SJ		 448 GROUP BY u.pid
449 HAVING u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : ''));
b9ad0878 450 }
22f043e4 451
b65beb64 452 /** Module de mise en correspondance les ids */
26ba053e 453 function handler_ids($page, $part = 'main', $pid = null, $ax_id = null)
b9ad0878 454 {
29459c49 455 $nbToLink = 100;
29459c49 456 $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple');
ddc4c642 457
73be4434 458 if ($part == 'missingInAX') {
b9ad0878
PC		 459 // locate all persons from this database that are not in AX's
460 $page->changeTpl('fusionax/idsMissingInAx.tpl');
ddc4c642 461 $missingInAX = XDB::iterator('SELECT promo, pid, private_name
29459c49 462 FROM fusionax_xorg_anciens
ad1c9939
SJ		 463 WHERE ax_id IS NULL
464 ORDER BY promo');
b9ad0878
PC		 465 $page->assign('missingInAX', $missingInAX);
466 return;
467 }
73be4434 468 if ($part == 'missingInXorg') {
b9ad0878
PC		 469 // locate all persons from AX's database that are not here
470 $page->changeTpl('fusionax/idsMissingInXorg.tpl');
ddc4c642 471 $missingInXorg = XDB::iterator("SELECT CONCAT(a.prenom, ' ', a.Nom_usuel) AS private_name,
ad1c9939 472 CONCAT(a.groupe_promo, a.promotion_etude) AS promo, a.ax_id
73be4434 473 FROM fusionax_import
ddc4c642 474 INNER JOIN fusionax_anciens AS a USING (ax_id)
ad1c9939
SJ		 475 WHERE fusionax_import.pid IS NULL
476 ORDER BY promo");
b9ad0878
PC		 477 $page->assign('missingInXorg', $missingInXorg);
478 return;
479 }
29459c49
SJ		 480 if ($part == 'wrongInXorg') {
481 // locate all persons from Xorg database that have a bad AX id
482 $page->changeTpl('fusionax/idswrongInXorg.tpl');
483 $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
484 $page->assign('wrongInXorg', $wrongInXorg);
485 return;
486 }
487 if ($part == 'cleanwronginxorg') {
488 $linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink);
489 while ($l = $linksToDo->next()) {
ddc4c642 490 FusionAxModule::clear_wrong_in_xorg($l['pid']);
29459c49
SJ		 491 }
492 pl_redirect('fusionax/ids/wrongInXorg');
493 }
494 if ($part == 'lier') {
495 if (Post::has('user_id') && Post::has('matricule_ax')) {
ddc4c642 496 FusionAxModule::link_by_ids(Post::i('pid'), Post::v('ax_id'));
29459c49
SJ		 497 }
498 }
73be4434 499 if ($part == 'link') {
ddc4c642 500 FusionAxModule::link_by_ids($pid, $ax_id);
b65beb64 501 exit;
b9ad0878 502 }
73be4434 503 if ($part == 'linknext') {
29459c49 504 $linksToDo = FusionAxModule::find_easy_to_link($nbToLink);
73be4434 505 while ($l = $linksToDo->next()) {
ddc4c642 506 FusionAxModule::link_by_ids($l['pid'], $l['ax_id']);
b9ad0878 507 }
b65beb64 508 pl_redirect('fusionax/ids#autolink');
b9ad0878 509 }
73be4434 510 if ($part == 'linkall') {
b9ad0878 511 $linksToDo = FusionAxModule::find_easy_to_link(0);
73be4434 512 while ($l = $linksToDo->next()) {
ddc4c642 513 FusionAxModule::link_by_ids($l['pid'], $l['ax_id']);
b9ad0878
PC		 514 }
515 }
516 {
517 $page->changeTpl('fusionax/ids.tpl');
73be4434 518 $missingInAX = XDB::query('SELECT COUNT(*)
ddc4c642
SJ		 519 FROM fusionax_xorg_anciens
520 WHERE ax_id IS NULL');
73be4434 521 if ($missingInAX) {
b9ad0878
PC		 522 $page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
523 }
73be4434 524 $missingInXorg = XDB::query('SELECT COUNT(*)
ddc4c642
SJ		 525 FROM fusionax_import
526 WHERE pid IS NULL');
73be4434
SJ		 527 if ($missingInXorg) {
528 $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
b9ad0878 529 }
29459c49
SJ		 530 $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
531 if ($wrongInXorg->total() > 0) {
532 $page->assign('wrongInXorg', $wrongInXorg->total());
533 }
534 $easyToLink = FusionAxModule::find_easy_to_link($nbToLink);
535 if ($easyToLink->total() > 0) {
536 $page->assign('nbMatch', $easyToLink->total());
b9ad0878
PC		 537 $page->assign('easyToLink', $easyToLink);
538 }
539 }
540 }
22f043e4 541
26ba053e 542 function handler_deceased($page, $action = '')
b9ad0878 543 {
29459c49
SJ		 544 if ($action == 'updateXorg') {
545 XDB::execute('UPDATE fusionax_deceased
546 SET deces_xorg = deces_ax
22648cc5 547 WHERE deces_xorg IS NULL');
29459c49
SJ		 548 }
549 if ($action == 'updateAX') {
550 XDB::execute('UPDATE fusionax_deceased
551 SET deces_ax = deces_xorg
552 WHERE deces_ax = "0000-00-00"');
553 }
554 if ($action == 'update') {
ddc4c642 555 if (Post::has('pid') && Post::has('date')) {
29459c49
SJ		 556 XDB::execute('UPDATE fusionax_deceased
557 SET deces_ax = {?}, deces_xorg = {?}
ddc4c642
SJ		 558 WHERE pid = {?}',
559 Post::v('date'), Post::v('date'), Post::i('pid'));
29459c49
SJ		 560 }
561 }
562 $page->changeTpl('fusionax/deceased.tpl');
b9ad0878 563 // deceased
73be4434
SJ		 564 $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
565 $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
ddc4c642
SJ		 566 $res = XDB::iterator('SELECT pid, ax_id, promo, private_name, deces_ax
567 FROM fusionax_deceased
22648cc5 568 WHERE deces_xorg IS NULL
29459c49
SJ		 569 LIMIT 10');
570 $page->assign('nbDeceasedMissingInXorg', $res->total());
571 $page->assign('deceasedMissingInXorg', $res);
ddc4c642
SJ		 572 $res = XDB::iterator('SELECT pid, ax_id, promo, private_name, deces_xorg
573 FROM fusionax_deceased
574 WHERE deces_ax = "0000-00-00"
29459c49
SJ		 575 LIMIT 10');
576 $page->assign('nbDeceasedMissingInAX', $res->total());
577 $page->assign('deceasedMissingInAX', $res);
ddc4c642
SJ		 578 $res = XDB::iterator('SELECT pid, ax_id, promo, private_name, deces_xorg, deces_ax
579 FROM fusionax_deceased
580 WHERE deces_xorg != "0000-00-00" AND deces_ax != "0000-00-00"');
29459c49
SJ		 581 $page->assign('nbDeceasedDifferent', $res->total());
582 $page->assign('deceasedDifferent', $res);
b9ad0878 583 }
c7eac294 584
26ba053e 585 function handler_promo($page, $action = '')
c7eac294
SJ		 586 {
587 $page->changeTpl('fusionax/promo.tpl');
9a2876cb 588 $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
c8ab27eb 589 FROM fusionax_promo
a3f12425
SJ		 590 WHERE !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg)
591 AND !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg)
9a2876cb
SJ		 592 AND !(promo_etude_ax = promo_etude_xorg + 1) AND groupe_promo = 'X'
593 ORDER BY promo_etude_xorg");
c7eac294 594 $nbMissmatchingPromos = $res->total();
a3f12425
SJ		 595 $page->assign('nbMissmatchingPromos', $res->total());
596 $page->assign('missmatchingPromos', $res);
597
9a2876cb 598 $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
a3f12425 599 FROM fusionax_promo
9a2876cb
SJ		 600 WHERE promo_etude_ax = promo_etude_xorg + 1 AND groupe_promo = 'X'
601 ORDER BY promo_etude_xorg");
a3f12425 602 $nbMissmatchingPromos += $res->total();
c7eac294
SJ		 603 $page->assign('nbMissmatchingPromos1', $res->total());
604 $page->assign('missmatchingPromos1', $res);
a3f12425 605
9a2876cb 606 $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
c7eac294 607 FROM fusionax_promo
9a2876cb
SJ		 608 WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg AND groupe_promo = 'X'
609 ORDER BY promo_etude_xorg");
c7eac294
SJ		 610 $nbMissmatchingPromos += $res->total();
611 $page->assign('nbMissmatchingPromos2', $res->total());
612 $page->assign('missmatchingPromos2', $res);
a3f12425 613
9a2876cb 614 $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
a3f12425 615 FROM fusionax_promo
9a2876cb
SJ		 616 WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg AND groupe_promo = 'X'
617 ORDER BY promo_etude_xorg");
a3f12425
SJ		 618 $nbMissmatchingPromos += $res->total();
619 $page->assign('nbMissmatchingPromos3', $res->total());
620 $page->assign('missmatchingPromos3', $res);
621
bb375055
SJ		 622 $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
623 FROM fusionax_promo
624 WHERE groupe_promo = 'M'
625 ORDER BY promo_etude_xorg");
626 $nbMissmatchingPromos += $res->total();
627 $page->assign('nbMissmatchingPromosM', $res->total());
628 $page->assign('missmatchingPromosM', $res);
629
630
631 $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
632 FROM fusionax_promo
633 WHERE groupe_promo = 'D'
634 ORDER BY promo_etude_xorg");
635 $nbMissmatchingPromos += $res->total();
636 $page->assign('nbMissmatchingPromosD', $res->total());
637 $page->assign('missmatchingPromosD', $res);
638
a3f12425
SJ		 639 $page->assign('nbMissmatchingPromosTotal', $nbMissmatchingPromos);
640 }
641
5bc0014e
SJ		 642 private function format($string)
643 {
644 return preg_replace('/(\s+|\-)/', '', $string);
645 }
646
647 private function retrieve_firstnames()
648 {
649 $res = XDB::rawFetchAllAssoc('SELECT p.pid, p.ax_id, p.hrpid,
650 f.prenom, ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary
651 FROM fusionax_anciens AS f
652 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
653 INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid)
654 WHERE f.prenom NOT IN (ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary)');
655
656 $issues = array();
657 foreach ($res as $item) {
658 if (!($item['firstname_ordinary'] != '' || $item['firstname_main'] != $item['firstname_initial'])) {
659 $ax = $this->format(mb_strtolower(replace_accent($item['prenom'])));
660 $xorg = $this->format(mb_strtolower(replace_accent($item['firstname_main'])));
661 if ($ax != $xorg) {
662 $issues[] = $item;
663 }
664 }
665 }
666
667 return $issues;
668 }
669
a876b1ef 670 function handler_names($page, $action = '', $csv = false)
a3f12425
SJ		 671 {
672 $page->changeTpl('fusionax/names.tpl');
673
193affc6 674 if ($action == 'first') {
5bc0014e 675 $res = $this->retrieve_firstnames();
a876b1ef
SJ		 676 if ($csv) {
677 pl_cached_content_headers('text/x-csv', 'utf-8', 1, 'firstnames.csv');
678
679 $csv = fopen('php://output', 'w');
680 fputcsv($csv, array('pid', 'ax_id', 'hrpid', 'AX', 'initial', 'principal', 'ordinaire'), ';');
681 foreach ($res as $item) {
682 fputcsv($csv, $item, ';');
683 }
684 fclose($csv);
685 exit();
686 } else {
687 $page->assign('firstnameIssues', $res);
688 }
08aaf92e
SJ		 689 } elseif ($action == 'last' || $action == 'last3' || $action == 'last2' || $action == 'last1') {
690 $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))";
691 $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))";
692 $ax_full = "(f.Nom_complet NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))";
693
694 switch ($action) {
695 case 'last':
696 $where = $ax_patro . ' OR ' . $ax_ordinary . ' OR ' . $ax_full;
697 break;
698 case 'last3':
699 $where = $ax_patro . ' AND ' . $ax_ordinary . ' AND ' . $ax_full;
700 break;
701 case 'last2':
702 $where = '(' . $ax_patro . ' AND ' . $ax_ordinary . ' AND NOT ' . $ax_full . ') OR ('
703 . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND ' . $ax_full . ') OR ('
704 . 'NOT ' . $ax_patro . ' AND ' . $ax_ordinary . ' AND ' . $ax_full . ')';
705 break;
706 case 'last1':
707 $where = '(' . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND NOT ' . $ax_full . ') OR ('
708 . 'NOT ' . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND ' . $ax_full . ') OR ('
709 . 'NOT ' . $ax_patro . ' AND ' . $ax_ordinary . ' AND NOT ' . $ax_full . ')';
710 break;
711 }
712
713 $res = XDB::rawFetchAllAssoc('SELECT p.pid, p.ax_id, p.hrpid,
193affc6
SJ		 714 f.Nom_patronymique, f.Nom_usuel, f.Nom_complet,
715 ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary
716 FROM fusionax_anciens AS f
717 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
247ca43b 718 INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid)
08aaf92e 719 WHERE ' . $where);
193affc6 720
a876b1ef 721 if ($csv) {
08aaf92e
SJ		 722 function format($string)
723 {
724 $string = preg_replace('/\-/', ' ', $string);
725 return preg_replace('/\s+/', ' ', $string);
726 }
727
728
a876b1ef
SJ		 729 pl_cached_content_headers('text/x-csv', 'utf-8', 1, 'lastnames.csv');
730
731 $csv = fopen('php://output', 'w');
732 fputcsv($csv, array('pid', 'ax_id', 'hrpid', 'AX patro', 'AX usuel', 'AX complet', 'initial', 'principal', 'marital', 'ordinaire'), ';');
733 foreach ($res as $item) {
08aaf92e
SJ		 734 $ax = array(
735 'Nom_patronymique' => format(mb_strtolower(replace_accent($item['Nom_patronymique']))),
736 'Nom_usuel' => format(mb_strtolower(replace_accent($item['Nom_usuel']))),
737 'Nom_complet' => format(mb_strtolower(replace_accent($item['Nom_complet'])))
738 );
739 $xorg = array(
740 'lastname_initial' => format(mb_strtolower(replace_accent($item['lastname_initial']))),
741 'lastname_main' => format(mb_strtolower(replace_accent($item['lastname_main']))),
742 'lastname_ordinary' => format(mb_strtolower(replace_accent($item['lastname_ordinary'])))
743 );
744
745 if (!in_array($ax['Nom_patronymique'], $xorg) || !in_array($ax['Nom_usuel'], $xorg) || !in_array($ax['Nom_complet'], $xorg)) {
746 fputcsv($csv, $item, ';');
747 }
a876b1ef
SJ		 748 }
749 fclose($csv);
750 exit();
751 } else {
752 $page->assign('lastnameIssues', $res);
08aaf92e
SJ		 753 $page->assign('total', count($res));
754 $page->assign('issuesTypes', array(
755 'last' => "1, 2 ou 3 noms de l'AX manquant",
756 'last1' => "1 nom de l'AX manquant",
757 'last2' => "2 noms de l'AX manquant",
758 'last3' => "3 noms de l'AX manquant"
759 ));
a876b1ef 760 }
193affc6
SJ		 761 } else {
762 $res = XDB::query('SELECT COUNT(*)
763 FROM fusionax_anciens AS f
764 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)');
765 $page->assign('total', $res->fetchOneCell());
766
767 $res = XDB::rawFetchOneCell("SELECT COUNT(*)
768 FROM fusionax_anciens AS f
769 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
247ca43b 770 INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid)
193affc6 771 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)
9a13e83f
SJ		 772 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)
773 OR f.Nom_complet NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary)");
193affc6
SJ		 774 $page->assign('lastnameIssues', $res);
775
776 $res = XDB::rawFetchOneCell('SELECT COUNT(*)
777 FROM fusionax_anciens AS f
778 INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
779 INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid)
780 WHERE f.prenom NOT IN (ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary)');
5bc0014e 781 $page->assign('firstnameIssues', count($this->retrieve_firstnames()));
193affc6
SJ		 782 }
783 $page->assign('action', $action);
c7eac294 784 }
12419b5b 785
26ba053e 786 function handler_edu($page, $action = '')
2fad6caa
SJ		 787 {
788 $page->changeTpl('fusionax/education.tpl');
789
95b1a6d8 790 $missingEducation = XDB::rawIterator("SELECT DISTINCT(f.Intitule_formation)
2fad6caa 791 FROM fusionax_formations AS f
95b1a6d8
SJ		 792 WHERE f.Intitule_formation != '' AND NOT EXISTS (SELECT *
793 FROM profile_education_enum AS e
794 WHERE f.Intitule_formation = e.name)");
795 $missingDegree = XDB::rawIterator("SELECT DISTINCT(f.Intitule_diplome)
2fad6caa 796 FROM fusionax_formations AS f
95b1a6d8
SJ		 797 WHERE f.Intitule_diplome != '' AND NOT EXISTS (SELECT *
798 FROM profile_education_degree_enum AS e
799 WHERE f.Intitule_diplome = e.abbreviation)");
800 $missingCouple = XDB::rawIterator("SELECT DISTINCT(f.Intitule_formation) AS edu, f.Intitule_diplome AS degree, ee.id AS eduid, de.id AS degreeid
2fad6caa 801 FROM fusionax_formations AS f
95b1a6d8
SJ		 802 INNER JOIN profile_education_enum AS ee ON (f.Intitule_formation = ee.name)
803 INNER JOIN profile_education_degree_enum AS de ON (f.Intitule_diplome = de.abbreviation)
2fad6caa
SJ		 804 WHERE f.Intitule_diplome != '' AND f.Intitule_formation != ''
805 AND NOT EXISTS (SELECT *
806 FROM profile_education_degree AS d
807 WHERE ee.id = d.eduid AND de.id = d.degreeid)");
808
809 $page->assign('missingEducation', $missingEducation);
810 $page->assign('missingDegree', $missingDegree);
811 $page->assign('missingCouple', $missingCouple);
812 $page->assign('missingEducationCount', $missingEducation->total());
813 $page->assign('missingDegreeCount', $missingDegree->total());
814 $page->assign('missingCoupleCount', $missingCouple->total());
815 }
816
26ba053e 817 function handler_corps($page)
551e00c1
SJ		 818 {
819 $page->changeTpl('fusionax/corps.tpl');
820
821 $missingCorps = XDB::rawIterator('SELECT DISTINCT(f.corps_sortie) AS name
822 FROM fusionax_anciens AS f
823 WHERE NOT EXISTS (SELECT *
824 FROM profile_corps_enum AS c
825 WHERE f.corps_sortie = c.abbreviation)');
826 $missingGrade = XDB::rawIterator('SELECT DISTINCT(f.grade) AS name
827 FROM fusionax_anciens AS f
828 WHERE NOT EXISTS (SELECT *
829 FROM profile_corps_rank_enum AS c
830 WHERE f.grade = c.name)');
831
832 $page->assign('missingCorps', $missingCorps);
833 $page->assign('missingGrade', $missingGrade);
834 $page->assign('missingCorpsCount', $missingCorps->total());
835 $page->assign('missingGradeCount', $missingGrade->total());
836 }
837
26ba053e 838 function handler_issues_deathdate($page, $action = '')
12419b5b
SJ		 839 {
840 $page->changeTpl('fusionax/deathdate_issues.tpl');
841 if ($action == 'edit') {
842 S::assert_xsrf_token();
843
844 $issues = XDB::rawIterRow('SELECT p.pid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate
845 FROM profile_merge_issues AS pm
846 INNER JOIN profiles AS p ON (pm.pid = p.pid)
847 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
848 WHERE FIND_IN_SET(\'deathdate\', pm.issues)
849 ORDER BY pd.directory_name');
850 while (list($pid, $name, $promo, $deathAX, $deathXorg) = $issues->next()) {
851 $choiceAX = Post::has('AX_' . $pid);
852 $choiceXorg = Post::has('XORG_' . $pid);
853 if (!($choiceAX || $choiceXorg)) {
854 continue;
855 }
856
857 if ($choiceAX) {
858 XDB::execute('UPDATE profiles AS p
859 INNER JOIN profile_merge_issues AS pm ON (pm.pid = p.pid)
860 SET p.deathdate = pm.deathdate_ax, p.deathdate_rec = NOW()
861 WHERE p.pid = {?}', $pid);
862 }
863 XDB::execute("UPDATE profile_merge_issues
864 SET issues = REPLACE(issues, 'deathdate', '')
865 WHERE pid = {?}", $pid());
866 $page->trigSuccess("La date de décès de $name ($promo) a bien été corrigée.");
867 }
868 }
869
870 $issues = XDB::rawFetchAllAssoc('SELECT p.pid, p.hrpid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate
871 FROM profile_merge_issues AS pm
872 INNER JOIN profiles AS p ON (pm.pid = p.pid)
873 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
874 WHERE FIND_IN_SET(\'deathdate\', pm.issues)
875 ORDER BY pd.directory_name');
876 $page->assign('issues', $issues);
877 $page->assign('total', count($issues));
878 }
879
26ba053e 880 function handler_issues_promo($page, $action = '')
12419b5b
SJ		 881 {
882 $page->changeTpl('fusionax/promo_issues.tpl');
883 if ($action == 'edit') {
884 S::assert_xsrf_token();
885
886 $issues = XDB::rawIterRow('SELECT p.pid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year
887 FROM profile_merge_issues AS pm
888 INNER JOIN profiles AS p ON (pm.pid = p.pid)
889 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
890 INNER JOIN profile_education AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags))
891 WHERE FIND_IN_SET(\'promo\', pm.issues)
892 ORDER BY pd.directory_name');
893 while (list($pid, $name, $promo, $deathAX, $deathXorgEntry, $deathXorgGrad) = $issues->next()) {
894 $choiceXorg = Post::has('XORG_' . $pid);
895 if (!(Post::has('display_' . $pid) && Post::has('entry_' . $pid) && Post::has('grad_' . $pid))) {
896 continue;
897 }
898
899 $display = Post::i('display_' . $pid);
900 $entry = Post::i('entry_' . $pid);
901 $grad = Post::i('grad_' . $pid);
902 if (!(($grad <= $entry + 5 && $grad >= $entry + 3) && ($display >= $entry && $display <= $grad - 3))) {
903 $page->trigError("La promotion de $name n'a pas été corrigée.");
904 continue;
905 }
906 XDB::execute('UPDATE profile_display
907 SET promo = {?}
908 WHERE pid = {?}', 'X' . $display, $pid);
909 XDB::execute('UPDATE profile_education
910 SET entry_year = {?}, grad_year = {?}
911 WHERE pid = {?} AND FIND_IN_SET(\'primary\', flags)', $entry, $grad, $pid);
912 $page->trigSuccess("La promotion de $name a bien été corrigée.");
913 }
914 }
915
916 $issues = XDB::rawFetchAllAssoc('SELECT p.pid, p.hrpid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year
917 FROM profile_merge_issues AS pm
918 INNER JOIN profiles AS p ON (pm.pid = p.pid)
919 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
920 INNER JOIN profile_education AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags))
921 WHERE FIND_IN_SET(\'promo\', pm.issues)
922 ORDER BY pd.directory_name');
923 $page->assign('issues', $issues);
924 $page->assign('total', count($issues));
925 }
926
26ba053e 927 function handler_issues($page, $action = '')
12419b5b
SJ		 928 {
929 static $issueList = array(
930 'name' => 'noms',
931 'phone' => 'téléphones',
932 'education' => 'formations',
933 'address' => 'adresses',
934 'job' => 'emplois'
935 );
d20f50cf
SJ		 936 static $typeList = array(
937 'name' => 'general',
938 'phone' => 'general',
939 'education' => 'general',
940 'address' => 'adresses',
941 'job' => 'emploi'
942 );
12419b5b
SJ		 943
944 if (!array_key_exists($action, $issueList)) {
945 pl_redirect('fusionax');
946 } else {
947 $total = XDB::fetchOneCell('SELECT COUNT(*)
948 FROM profile_merge_issues
949 WHERE FIND_IN_SET({?}, issues)', $action);
950 if ($total == 0) {
951 pl_redirect('fusionax');
952 }
953
954 $issues = XDB::fetchAllAssoc('SELECT p.hrpid, pd.directory_name, pd.promo
955 FROM profile_merge_issues AS pm
956 INNER JOIN profiles AS p ON (pm.pid = p.pid)
957 INNER JOIN profile_display AS pd ON (pd.pid = p.pid)
958 WHERE FIND_IN_SET({?}, pm.issues)
959 ORDER BY pd.directory_name
960 LIMIT 100', $action);
961
962 $page->changeTpl('fusionax/other_issues.tpl');
963 $page->assign('issues', $issues);
964 $page->assign('issue', $issueList[$action]);
d20f50cf 965 $page->assign('type', $typeList[$action]);
12419b5b
SJ		 966 $page->assign('total', $total);
967 }
968 }
b9ad0878 969}
e8591429 970
448c8cdc 971// vim:set et sw=4 sts=4 sws=4 foldmethod=marker fenc=utf-8:
a3f12425 972?>
plat/al