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