projects / platal.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
2010.
[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
PC		 22/**
23 * @brief Module to merge data from AX database
24 *
25 * Module to import data from another database of alumni that had
26 * different schemas. The organization that used this db is called AX
27 * hence the name of this module.
28 *
29 * Datas are stored in an external server and you need a private key
30 * to connect to their server.
31 */
73be4434
SJ		 32class FusionAxModule extends PLModule
33{
c15c0053
PC		 34 function __construct()
35 {
c15c0053 36 }
9cb8882b 37
b9ad0878
PC		 38 function handlers()
39 {
40 return array(
29459c49
SJ		 41 'fusionax' => $this->make_hook('index', AUTH_MDP, 'admin'),
42 'fusionax/import' => $this->make_hook('import', AUTH_MDP, 'admin'),
43 'fusionax/view' => $this->make_hook('view', AUTH_MDP, 'admin'),
44 'fusionax/ids' => $this->make_hook('ids', AUTH_MDP, 'admin'),
45 'fusionax/deceased' => $this->make_hook('deceased', AUTH_MDP, 'admin'),
c7eac294 46 'fusionax/promo' => $this->make_hook('promo', AUTH_MDP, 'admin'),
b9ad0878
PC		 47 );
48 }
c15c0053
PC		 49
50
b9ad0878
PC		 51 function handler_index(&$page)
52 {
73be4434
SJ		 53 $globals = Platal::globals();
54
b9ad0878 55 $page->changeTpl('fusionax/index.tpl');
73be4434
SJ		 56 $page->assign('xorg_title', 'Polytechnique.org - Fusion des annuaires');
57 if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
58 $page->assign('lastimport', date("d-m-Y", $globals->fusionax->LastUpdate));
59 }
b9ad0878 60 }
22f043e4 61
73be4434 62 /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
b9ad0878
PC		 63 function handler_import(&$page, $action = 'index', $fileSQL = '')
64 {
73be4434
SJ		 65 $globals = Platal::globals();
66
b9ad0878
PC		 67 if ($action == 'index') {
68 $page->changeTpl('fusionax/import.tpl');
73be4434 69 if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
e8591429
PC		 70 $page->assign(
71 'lastimport',
73be4434 72 "le " . date("d/m/Y à H:i", $globals->fusionax->LastUpdate));
b9ad0878 73 }
b9ad0878
PC		 74 return;
75 }
22f043e4 76
cc8ea8b2 77 // toutes les actions sont faites en ajax en utilisant jquery
b9ad0878 78 header("Content-type: text/javascript; charset=utf-8");
22f043e4 79
cc8ea8b2
PC		 80 // log des actions
81 $report = array();
22f043e4 82
cc8ea8b2 83 // création d'un fichier temporaire si nécessaire
b9ad0878
PC		 84 if (Env::has('tmpdir')) {
85 $tmpdir = Env::v('tmpdir');
86 } else {
b9ad0878
PC		 87 $tmpdir = tempnam('/tmp', 'fusionax');
88 unlink($tmpdir);
89 mkdir($tmpdir);
cc8ea8b2 90 chmod($tmpdir, 0700);
b9ad0878 91 }
22f043e4 92
73be4434 93 $modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/';
b9ad0878
PC		 94 $olddir = getcwd();
95 chdir($tmpdir);
22f043e4 96
b9ad0878 97 if ($action == 'launch') {
73be4434
SJ		 98 // séparation de l'archive en fichiers par tables
99 exec($modulepath . 'import-ax.sh', $report);
100 $report[] = 'Fichier parsé.';
b9ad0878
PC		 101 $report[] = 'Import dans la base en cours...';
102 $next = 'integrateSQL';
73be4434 103 } elseif ($action == 'integrateSQL') {
cc8ea8b2
PC		 104 // intégration des données dans la base MySQL
105 // liste des fichiers sql à exécuter
106 $filesSQL = array(
107 'Activites.sql',
108 'Adresses.sql',
109 'Anciens.sql',
110 'Formations.sql',
111 'Entreprises.sql');
b9ad0878 112 if ($fileSQL != '') {
cc8ea8b2 113 // récupère le contenu du fichier sql
73be4434
SJ		 114 $queries = explode(';', file_get_contents($modulepath . $fileSQL));
115 foreach ($queries as $q) {
116 if (trim($q)) {
117 // coupe le fichier en requêtes individuelles
118 if (substr($q, 0, 2) == '--') {
119 // affiche les commentaires dans le report
120 $lines = explode("\n", $q);
121 $l = $lines[0];
122 $report[] = addslashes($l);
123 }
124 // exécute la requête
125 XDB::execute($q);
b9ad0878 126 }
b9ad0878 127 }
cc8ea8b2
PC		 128 // trouve le prochain fichier à exécuter
129 $trans = array_flip($filesSQL);
130 $nextfile = $trans[$fileSQL] + 1;
b9ad0878
PC		 131 } else {
132 $nextfile = 0;
133 }
134 if (!isset($filesSQL[$nextfile])) {
e8591429
PC		 135 // tous les fichiers ont été exécutés, on passe à l'étape
136 // suivante
b9ad0878
PC		 137 $next = 'clean';
138 } else {
cc8ea8b2 139 // on passe au fichier suivant
73be4434 140 $next = 'integrateSQL/' . $filesSQL[$nextfile];
b9ad0878 141 }
73be4434 142 } elseif ($action == 'clean') {
cc8ea8b2 143 // nettoyage du fichier temporaire
b9ad0878 144 chdir($olddir);
73be4434
SJ		 145 exec("rm -Rf $tmpdir", $report);
146 $report[] = "Fin de l\'import";
cc8ea8b2 147 // met à jour la date de dernier import
73be4434 148 //$globals->change_dynamic_config(array('LastUpdate' => time()), 'FusionAx');
b9ad0878 149 }
73be4434 150 foreach($report as $t) {
cc8ea8b2 151 // affiche les lignes de report
73be4434
SJ		 152 echo "$('#fusionax_import').append('" . $t . "<br/>');\n";
153 }
b9ad0878 154 if (isset($next)) {
73be4434
SJ		 155 $tmpdir = getcwd();
156 chdir($olddir);
cc8ea8b2 157 // lance le prochain script s'il y en a un
73be4434 158 echo "$.getScript('fusionax/import/" . $next . "?tmpdir=" . urlencode($tmpdir) . "');";
b9ad0878 159 }
cc8ea8b2 160 // exit pour ne pas afficher la page template par défaut
b9ad0878
PC		 161 exit;
162 }
22f043e4 163
29459c49
SJ		 164 /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
165 function handler_view(&$page, $action = '')
166 {
167 $globals = Platal::globals();
168
169 $page->changeTpl('fusionax/view.tpl');
170 if ($action == 'create') {
171 XDB::execute('DROP VIEW IF EXISTS fusionax_deceased');
172 XDB::execute('CREATE VIEW fusionax_deceased AS
173 SELECT u.user_id, a.id_ancien, u.nom, u.prenom, u.promo, u.deces AS deces_xorg, a.Date_deces AS deces_ax
c7eac294 174 FROM auth_user_md5 AS u
29459c49
SJ		 175 INNER JOIN fusionax_anciens AS a ON (a.id_ancien = u.matricule_ax)
176 WHERE u.deces != a.Date_deces');
177 XDB::execute('DROP VIEW IF EXISTS fusionax_promo');
178 XDB::execute('CREATE VIEW fusionax_promo AS
c7eac294 179 SELECT u.user_id, u.matricule_ax, CONCAT(u.nom, " ", u.prenom) AS display_name, u.promo AS promo_etude_xorg,
29459c49
SJ		 180 f.promotion_etude AS promo_etude_ax, u.promo_sortie AS promo_sortie_xorg
181 FROM auth_user_md5 AS u
182 INNER JOIN fusionax_anciens AS f ON (u.matricule_ax = f.id_ancien)
183 WHERE u.promo != f.promotion_etude AND !(f.promotion_etude = u.promo + 1 AND u.promo_sortie = u.promo + 4)');
184 }
185 }
186
187 /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */
188 private static function clear_wrong_in_xorg($user_id)
189 {
190 $res = XDB::execute("UPDATE fusionax_xorg_anciens
191 SET matricule_ax = NULL
192 WHERE user_id = {?}", $user_id);
193 if (!$res) {
194 return 0;
195 }
196 return XDB::affectedRows() / 2;
197 }
198
199 /* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX
200 * (mises à part les promo 1921 et 1923 qui ne figurent pas dans les données de l'AX)*/
201 private static function find_wrong_in_xorg($limit = 10)
202 {
203 return XDB::iterator("SELECT u.promo, u.user_id, u.display_name
204 FROM fusionax_xorg_anciens AS u
205 WHERE NOT EXISTS (SELECT *
206 FROM fusionax_anciens AS f
207 WHERE f.id_ancien = u.matricule_ax)
208 AND u.matricule_ax IS NOT NULL AND promo != 1921 AND promo != 1923");
209 }
210
b65beb64
PC		 211 /** Lier les identifiants d'un ancien dans les deux annuaires
212 * @param user_id identifiant dans l'annuaire X.org
213 * @param matricule_ax identifiant dans l'annuaire de l'AX
214 * @return 0 si la liaison a échoué, 1 sinon
22f043e4 215 */
b9ad0878
PC		 216 private static function link_by_ids($user_id, $matricule_ax)
217 {
29459c49 218 $res = XDB::execute("UPDATE fusionax_import AS i
73be4434
SJ		 219 INNER JOIN fusionax_xorg_anciens AS u
220 SET u.matricule_ax = i.id_ancien,
221 i.user_id = u.user_id,
222 i.date_match_id = NOW()
223 WHERE i.id_ancien = {?} AND u.user_id = {?}
224 AND (u.matricule_ax != {?} OR u.matricule_ax IS NULL
225 OR i.user_id != {?} OR i.user_id IS NULL)",
226 $matricule_ax, $user_id, $matricule_ax, $user_id);
227 if (!$res) {
b9ad0878
PC		 228 return 0;
229 }
73be4434 230 return XDB::affectedRows() / 2;
b9ad0878 231 }
22f043e4 232
b65beb64
PC		 233 /** Recherche automatique d'anciens à lier entre les deux annuaires
234 * @param limit nombre d'anciens à trouver au max
235 * @param sure si true, ne trouve que des anciens qui sont quasi sûrs
e8591429
PC		 236 * @return un XOrgDBIterator sur les entrées avec display_name, promo,
237 * user_id, id_ancien et display_name_ax
22f043e4 238 */
b65beb64 239 private static function find_easy_to_link($limit = 10, $sure = false)
b9ad0878 240 {
e8591429 241 $easy_to_link = XDB::iterator("
29459c49 242 SELECT u.display_name, u.promo, u.user_id, ax.id_ancien,
73be4434
SJ		 243 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
244 COUNT(*) AS nbMatches
29459c49
SJ		 245 FROM fusionax_anciens AS ax
246 INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL)
247 LEFT JOIN fusionax_xorg_anciens AS u ON (u.matricule_ax IS NULL
248 AND ax.Nom_patronymique = u.nom
249 AND ax.prenom = u.prenom
250 AND u.promo = ax.promotion_etude)
251 GROUP BY u.user_id
252 HAVING u.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : ''));
b65beb64
PC		 253 if ($easy_to_link->total() > 0 || $sure) {
254 return $easy_to_link;
255 }
73be4434 256 return XDB::iterator("
29459c49 257 SELECT u.display_name, u.promo, u.user_id, ax.id_ancien,
73be4434
SJ		 258 CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
259 COUNT(*) AS nbMatches
29459c49
SJ		 260 FROM fusionax_anciens AS ax
261 INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL)
262 LEFT JOIN fusionax_xorg_anciens AS u ON (u.matricule_ax IS NULL
263 AND (ax.Nom_patronymique = u.nom
264 OR ax.Nom_patronymique LIKE CONCAT(u.nom, ' %')
265 OR ax.Nom_patronymique LIKE CONCAT(u.nom, '-%')
266 OR ax.Nom_usuel = u.nom
267 OR u.nom LIKE CONCAT('% ', ax.Nom_patronymique))
268 AND u.promo < ax.promotion_etude + 2
269 AND u.promo > ax.promotion_etude - 2)
270 GROUP BY u.user_id
271 HAVING u.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : ''));
b9ad0878 272 }
22f043e4 273
b65beb64 274 /** Module de mise en correspondance les ids */
73be4434 275 function handler_ids(&$page, $part = 'main', $user_id = null, $matricule_ax = null)
b9ad0878 276 {
73be4434 277 $globals = Platal::globals();
29459c49 278 $nbToLink = 100;
22f043e4 279
29459c49 280 $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple');
73be4434 281 if ($part == 'missingInAX') {
b9ad0878
PC		 282 // locate all persons from this database that are not in AX's
283 $page->changeTpl('fusionax/idsMissingInAx.tpl');
29459c49
SJ		 284 $missingInAX = XDB::iterator("SELECT promo, user_id, display_name
285 FROM fusionax_xorg_anciens
286 WHERE matricule_ax IS NULL");
b9ad0878
PC		 287 $page->assign('missingInAX', $missingInAX);
288 return;
289 }
73be4434 290 if ($part == 'missingInXorg') {
b9ad0878
PC		 291 // locate all persons from AX's database that are not here
292 $page->changeTpl('fusionax/idsMissingInXorg.tpl');
73be4434
SJ		 293 $missingInXorg = XDB::iterator("SELECT a.promotion_etude AS promo,
294 CONCAT(a.prenom, ' ', a.Nom_usuel) AS display_name,
295 a.id_ancien
296 FROM fusionax_import
297 INNER JOIN fusionax_anciens AS a USING (id_ancien)
29459c49 298 WHERE fusionax_import.user_id IS NULL");
b9ad0878
PC		 299 $page->assign('missingInXorg', $missingInXorg);
300 return;
301 }
29459c49
SJ		 302 if ($part == 'wrongInXorg') {
303 // locate all persons from Xorg database that have a bad AX id
304 $page->changeTpl('fusionax/idswrongInXorg.tpl');
305 $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
306 $page->assign('wrongInXorg', $wrongInXorg);
307 return;
308 }
309 if ($part == 'cleanwronginxorg') {
310 $linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink);
311 while ($l = $linksToDo->next()) {
312 FusionAxModule::clear_wrong_in_xorg($l['user_id']);
313 }
314 pl_redirect('fusionax/ids/wrongInXorg');
315 }
316 if ($part == 'lier') {
317 if (Post::has('user_id') && Post::has('matricule_ax')) {
318 FusionAxModule::link_by_ids(Post::i('user_id'), Post::v('matricule_ax'));
319 }
320 }
73be4434
SJ		 321 if ($part == 'link') {
322 FusionAxModule::link_by_ids($user_id, $matricule_ax);
b65beb64 323 exit;
b9ad0878 324 }
73be4434 325 if ($part == 'linknext') {
29459c49 326 $linksToDo = FusionAxModule::find_easy_to_link($nbToLink);
73be4434
SJ		 327 while ($l = $linksToDo->next()) {
328 FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
b9ad0878 329 }
b65beb64 330 pl_redirect('fusionax/ids#autolink');
b9ad0878 331 }
73be4434 332 if ($part == 'linkall') {
b9ad0878 333 $linksToDo = FusionAxModule::find_easy_to_link(0);
73be4434
SJ		 334 while ($l = $linksToDo->next()) {
335 FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
b9ad0878
PC		 336 }
337 }
338 {
339 $page->changeTpl('fusionax/ids.tpl');
73be4434
SJ		 340 $missingInAX = XDB::query('SELECT COUNT(*)
341 FROM fusionax_xorg_anciens AS u
342 WHERE u.matricule_ax IS NULL');
343 if ($missingInAX) {
b9ad0878
PC		 344 $page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
345 }
73be4434
SJ		 346 $missingInXorg = XDB::query('SELECT COUNT(*)
347 FROM fusionax_import AS i
348 WHERE i.user_id IS NULL');
349 if ($missingInXorg) {
350 $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
b9ad0878 351 }
29459c49
SJ		 352 $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
353 if ($wrongInXorg->total() > 0) {
354 $page->assign('wrongInXorg', $wrongInXorg->total());
355 }
356 $easyToLink = FusionAxModule::find_easy_to_link($nbToLink);
357 if ($easyToLink->total() > 0) {
358 $page->assign('nbMatch', $easyToLink->total());
b9ad0878
PC		 359 $page->assign('easyToLink', $easyToLink);
360 }
361 }
362 }
22f043e4 363
29459c49 364 function handler_deceased(&$page, $action = '')
b9ad0878 365 {
29459c49
SJ		 366 if ($action == 'updateXorg') {
367 XDB::execute('UPDATE fusionax_deceased
368 SET deces_xorg = deces_ax
369 WHERE deces_xorg = "0000-00-00"');
370 }
371 if ($action == 'updateAX') {
372 XDB::execute('UPDATE fusionax_deceased
373 SET deces_ax = deces_xorg
374 WHERE deces_ax = "0000-00-00"');
375 }
376 if ($action == 'update') {
377 if (Post::has('user_id') && Post::has('date')) {
378 XDB::execute('UPDATE fusionax_deceased
379 SET deces_ax = {?}, deces_xorg = {?}
380 WHERE user_id = {?}',
381 Post::v('date'), Post::v('date'), Post::i('user_id'));
382 }
383 }
384 $page->changeTpl('fusionax/deceased.tpl');
b9ad0878 385 // deceased
73be4434
SJ		 386 $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
387 $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
29459c49
SJ		 388 $res = XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_ax,
389 CONCAT(d.prenom, " ", d.nom) AS display_name
390 FROM fusionax_deceased AS d
391 WHERE d.deces_xorg = "0000-00-00"
392 LIMIT 10');
393 $page->assign('nbDeceasedMissingInXorg', $res->total());
394 $page->assign('deceasedMissingInXorg', $res);
395 $res = XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_xorg,
396 CONCAT(d.prenom, " ", d.nom) AS display_name
397 FROM fusionax_deceased AS d
398 WHERE d.deces_ax = "0000-00-00"
399 LIMIT 10');
400 $page->assign('nbDeceasedMissingInAX', $res->total());
401 $page->assign('deceasedMissingInAX', $res);
402 $res = XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo,
403 d.deces_ax, d.deces_xorg,
404 CONCAT(d.prenom, " ", d.nom, " ", d.user_id) AS display_name
405 FROM fusionax_deceased AS d
406 WHERE d.deces_xorg != "0000-00-00" AND d.deces_ax != "0000-00-00"');
407 $page->assign('nbDeceasedDifferent', $res->total());
408 $page->assign('deceasedDifferent', $res);
b9ad0878 409 }
c7eac294
SJ		 410
411 function handler_promo(&$page, $action = '')
412 {
413 $page->changeTpl('fusionax/promo.tpl');
414 $res = XDB::iterator('SELECT user_id, display_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax
415 FROM fusionax_promo
416 WHERE !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg)');
417 $nbMissmatchingPromos = $res->total();
418 $page->assign('nbMissmatchingPromos1', $res->total());
419 $page->assign('missmatchingPromos1', $res);
420 $res = XDB::iterator('SELECT user_id, display_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax
421 FROM fusionax_promo
422 WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg');
423 $nbMissmatchingPromos += $res->total();
424 $page->assign('nbMissmatchingPromos2', $res->total());
425 $page->assign('missmatchingPromos2', $res);
426 $page->assign('nbMissmatchingPromos', $nbMissmatchingPromos);
427 }
b9ad0878 428}
e8591429 429
b9ad0878 430// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:?>
plat/al