Commit | Line | Data |
---|---|---|
b9ad0878 PC |
1 | <?php |
2 | /*************************************************************************** | |
3 | * Copyright (C) 2003-2007 Polytechnique.org * | |
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 |
32 | class FusionAxModule extends PLModule |
33 | { | |
c15c0053 PC |
34 | function __construct() |
35 | { | |
c15c0053 | 36 | } |
9cb8882b | 37 | |
b9ad0878 PC |
38 | function handlers() |
39 | { | |
40 | return array( | |
41 | 'fusionax' => $this->make_hook('index', AUTH_MDP, 'admin'), | |
e8591429 | 42 | 'fusionax/import' => $this->make_hook('import', AUTH_MDP,'admin'), |
b9ad0878 PC |
43 | 'fusionax/ids' => $this->make_hook('ids', AUTH_MDP, 'admin'), |
44 | 'fusionax/misc' => $this->make_hook('misc', AUTH_MDP, 'admin'), | |
45 | ); | |
46 | } | |
c15c0053 PC |
47 | |
48 | ||
b9ad0878 PC |
49 | function handler_index(&$page) |
50 | { | |
73be4434 SJ |
51 | $globals = Platal::globals(); |
52 | ||
b9ad0878 | 53 | $page->changeTpl('fusionax/index.tpl'); |
73be4434 SJ |
54 | $page->assign('xorg_title', 'Polytechnique.org - Fusion des annuaires'); |
55 | if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) { | |
56 | $page->assign('lastimport', date("d-m-Y", $globals->fusionax->LastUpdate)); | |
57 | } | |
b9ad0878 | 58 | } |
22f043e4 | 59 | |
73be4434 | 60 | /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */ |
b9ad0878 PC |
61 | function handler_import(&$page, $action = 'index', $fileSQL = '') |
62 | { | |
73be4434 SJ |
63 | $globals = Platal::globals(); |
64 | ||
b9ad0878 PC |
65 | if ($action == 'index') { |
66 | $page->changeTpl('fusionax/import.tpl'); | |
67 | $page->addJsLink('jquery.js'); | |
73be4434 | 68 | if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) { |
e8591429 PC |
69 | $page->assign( |
70 | 'lastimport', | |
73be4434 | 71 | "le " . date("d/m/Y à H:i", $globals->fusionax->LastUpdate)); |
b9ad0878 | 72 | } |
b9ad0878 PC |
73 | return; |
74 | } | |
22f043e4 | 75 | |
cc8ea8b2 | 76 | // toutes les actions sont faites en ajax en utilisant jquery |
b9ad0878 | 77 | header("Content-type: text/javascript; charset=utf-8"); |
22f043e4 | 78 | |
cc8ea8b2 PC |
79 | // log des actions |
80 | $report = array(); | |
22f043e4 | 81 | |
cc8ea8b2 | 82 | // création d'un fichier temporaire si nécessaire |
b9ad0878 PC |
83 | if (Env::has('tmpdir')) { |
84 | $tmpdir = Env::v('tmpdir'); | |
85 | } else { | |
b9ad0878 PC |
86 | $tmpdir = tempnam('/tmp', 'fusionax'); |
87 | unlink($tmpdir); | |
88 | mkdir($tmpdir); | |
cc8ea8b2 | 89 | chmod($tmpdir, 0700); |
b9ad0878 | 90 | } |
22f043e4 | 91 | |
73be4434 | 92 | $modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/'; |
b9ad0878 PC |
93 | $olddir = getcwd(); |
94 | chdir($tmpdir); | |
22f043e4 | 95 | |
b9ad0878 | 96 | if ($action == 'launch') { |
73be4434 SJ |
97 | // séparation de l'archive en fichiers par tables |
98 | exec($modulepath . 'import-ax.sh', $report); | |
99 | $report[] = 'Fichier parsé.'; | |
b9ad0878 PC |
100 | $report[] = 'Import dans la base en cours...'; |
101 | $next = 'integrateSQL'; | |
73be4434 | 102 | } elseif ($action == 'integrateSQL') { |
cc8ea8b2 PC |
103 | // intégration des données dans la base MySQL |
104 | // liste des fichiers sql à exécuter | |
105 | $filesSQL = array( | |
106 | 'Activites.sql', | |
107 | 'Adresses.sql', | |
108 | 'Anciens.sql', | |
109 | 'Formations.sql', | |
110 | 'Entreprises.sql'); | |
b9ad0878 | 111 | if ($fileSQL != '') { |
cc8ea8b2 | 112 | // récupère le contenu du fichier sql |
73be4434 SJ |
113 | $queries = explode(';', file_get_contents($modulepath . $fileSQL)); |
114 | foreach ($queries as $q) { | |
115 | if (trim($q)) { | |
116 | // coupe le fichier en requêtes individuelles | |
117 | if (substr($q, 0, 2) == '--') { | |
118 | // affiche les commentaires dans le report | |
119 | $lines = explode("\n", $q); | |
120 | $l = $lines[0]; | |
121 | $report[] = addslashes($l); | |
122 | } | |
123 | // exécute la requête | |
124 | XDB::execute($q); | |
b9ad0878 | 125 | } |
b9ad0878 | 126 | } |
cc8ea8b2 PC |
127 | // trouve le prochain fichier à exécuter |
128 | $trans = array_flip($filesSQL); | |
129 | $nextfile = $trans[$fileSQL] + 1; | |
b9ad0878 PC |
130 | } else { |
131 | $nextfile = 0; | |
132 | } | |
133 | if (!isset($filesSQL[$nextfile])) { | |
e8591429 PC |
134 | // tous les fichiers ont été exécutés, on passe à l'étape |
135 | // suivante | |
b9ad0878 PC |
136 | $next = 'clean'; |
137 | } else { | |
cc8ea8b2 | 138 | // on passe au fichier suivant |
73be4434 | 139 | $next = 'integrateSQL/' . $filesSQL[$nextfile]; |
b9ad0878 | 140 | } |
73be4434 | 141 | } elseif ($action == 'clean') { |
cc8ea8b2 | 142 | // nettoyage du fichier temporaire |
b9ad0878 | 143 | chdir($olddir); |
73be4434 SJ |
144 | exec("rm -Rf $tmpdir", $report); |
145 | $report[] = "Fin de l\'import"; | |
cc8ea8b2 | 146 | // met à jour la date de dernier import |
73be4434 | 147 | //$globals->change_dynamic_config(array('LastUpdate' => time()), 'FusionAx'); |
b9ad0878 | 148 | } |
73be4434 | 149 | foreach($report as $t) { |
cc8ea8b2 | 150 | // affiche les lignes de report |
73be4434 SJ |
151 | echo "$('#fusionax_import').append('" . $t . "<br/>');\n"; |
152 | } | |
b9ad0878 | 153 | if (isset($next)) { |
73be4434 SJ |
154 | $tmpdir = getcwd(); |
155 | chdir($olddir); | |
cc8ea8b2 | 156 | // lance le prochain script s'il y en a un |
73be4434 | 157 | echo "$.getScript('fusionax/import/" . $next . "?tmpdir=" . urlencode($tmpdir) . "');"; |
b9ad0878 | 158 | } |
cc8ea8b2 | 159 | // exit pour ne pas afficher la page template par défaut |
b9ad0878 PC |
160 | exit; |
161 | } | |
22f043e4 | 162 | |
b65beb64 PC |
163 | /** Lier les identifiants d'un ancien dans les deux annuaires |
164 | * @param user_id identifiant dans l'annuaire X.org | |
165 | * @param matricule_ax identifiant dans l'annuaire de l'AX | |
166 | * @return 0 si la liaison a échoué, 1 sinon | |
22f043e4 | 167 | */ |
b9ad0878 PC |
168 | private static function link_by_ids($user_id, $matricule_ax) |
169 | { | |
73be4434 SJ |
170 | $res = XDB::execute("UPDATE fusionax_import AS i |
171 | INNER JOIN fusionax_xorg_anciens AS u | |
172 | SET u.matricule_ax = i.id_ancien, | |
173 | i.user_id = u.user_id, | |
174 | i.date_match_id = NOW() | |
175 | WHERE i.id_ancien = {?} AND u.user_id = {?} | |
176 | AND (u.matricule_ax != {?} OR u.matricule_ax IS NULL | |
177 | OR i.user_id != {?} OR i.user_id IS NULL)", | |
178 | $matricule_ax, $user_id, $matricule_ax, $user_id); | |
179 | if (!$res) { | |
b9ad0878 PC |
180 | return 0; |
181 | } | |
73be4434 | 182 | return XDB::affectedRows() / 2; |
b9ad0878 | 183 | } |
22f043e4 | 184 | |
b65beb64 PC |
185 | /** Recherche automatique d'anciens à lier entre les deux annuaires |
186 | * @param limit nombre d'anciens à trouver au max | |
187 | * @param sure si true, ne trouve que des anciens qui sont quasi sûrs | |
e8591429 PC |
188 | * @return un XOrgDBIterator sur les entrées avec display_name, promo, |
189 | * user_id, id_ancien et display_name_ax | |
22f043e4 | 190 | */ |
b65beb64 | 191 | private static function find_easy_to_link($limit = 10, $sure = false) |
b9ad0878 | 192 | { |
e8591429 | 193 | $easy_to_link = XDB::iterator(" |
73be4434 SJ |
194 | SELECT xorg.display_name, xorg.promo, xorg.user_id, ax.id_ancien, |
195 | CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax, | |
196 | COUNT(*) AS nbMatches | |
197 | FROM fusionax_anciens AS ax | |
198 | INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL) | |
199 | LEFT JOIN fusionax_xorg_anciens AS xorg ON (xorg.matricule_ax IS NULL | |
200 | AND ax.Nom_complet = xorg.nom | |
201 | AND ax.prenom = xorg.prenom | |
202 | AND xorg.promo = ax.promotion_etude) | |
203 | GROUP BY xorg.user_id | |
204 | HAVING xorg.user_id IS NOT NULL AND nbMatches = 1" . ($limit ? ('LIMIT ' . $limit) : '')); | |
b65beb64 PC |
205 | if ($easy_to_link->total() > 0 || $sure) { |
206 | return $easy_to_link; | |
207 | } | |
73be4434 SJ |
208 | return XDB::iterator(" |
209 | SELECT xorg.display_name, xorg.promo, xorg.user_id, ax.id_ancien, | |
210 | CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax, | |
211 | COUNT(*) AS nbMatches | |
212 | FROM fusionax_anciens AS ax | |
213 | INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL) | |
214 | LEFT JOIN fusionax_xorg_anciens AS xorg ON (xorg.matricule_ax IS NULL | |
215 | AND (ax.Nom_complet = xorg.nom | |
216 | OR ax.Nom_complet LIKE CONCAT(xorg.nom,' %') | |
217 | OR ax.Nom_complet LIKE CONCAT(xorg.nom,'-%') | |
218 | OR ax.Nom_usuel = xorg.nom | |
219 | OR xorg.nom LIKE CONCAT('% ',ax.Nom_complet)) | |
220 | AND xorg.promo < ax.promotion_etude + 2 | |
221 | AND xorg.promo > ax.promotion_etude - 2) | |
222 | GROUP BY xorg.user_id | |
223 | HAVING xorg.user_id IS NOT NULL AND nbMatches = 1" . ($limit ? ('LIMIT ' . $limit) : '')); | |
b9ad0878 | 224 | } |
22f043e4 | 225 | |
b65beb64 | 226 | /** Module de mise en correspondance les ids */ |
73be4434 | 227 | function handler_ids(&$page, $part = 'main', $user_id = null, $matricule_ax = null) |
b9ad0878 | 228 | { |
73be4434 | 229 | $globals = Platal::globals(); |
22f043e4 | 230 | |
73be4434 | 231 | $page->addJsLink('jquery.js'); |
e8591429 PC |
232 | $page->assign( |
233 | 'xorg_title', | |
234 | 'Polytechnique.org - Fusion - Mise en correspondance simple'); | |
73be4434 | 235 | if ($part == 'missingInAX') { |
b9ad0878 PC |
236 | // locate all persons from this database that are not in AX's |
237 | $page->changeTpl('fusionax/idsMissingInAx.tpl'); | |
73be4434 SJ |
238 | $missingInAX = XDB::iterator("SELECT u.promo, u.user_id, u.display_name |
239 | FROM fusionax_xorg_anciens AS u | |
240 | WHERE u.matricule_ax IS NULL | |
241 | LIMIT 20"); | |
b9ad0878 PC |
242 | $page->assign('missingInAX', $missingInAX); |
243 | return; | |
244 | } | |
73be4434 | 245 | if ($part == 'missingInXorg') { |
b9ad0878 PC |
246 | // locate all persons from AX's database that are not here |
247 | $page->changeTpl('fusionax/idsMissingInXorg.tpl'); | |
73be4434 SJ |
248 | $missingInXorg = XDB::iterator("SELECT a.promotion_etude AS promo, |
249 | CONCAT(a.prenom, ' ', a.Nom_usuel) AS display_name, | |
250 | a.id_ancien | |
251 | FROM fusionax_import | |
252 | INNER JOIN fusionax_anciens AS a USING (id_ancien) | |
253 | WHERE fusionax_import.user_id IS NULL | |
254 | LIMIT 20"); | |
b9ad0878 PC |
255 | $page->assign('missingInXorg', $missingInXorg); |
256 | return; | |
257 | } | |
73be4434 SJ |
258 | if ($part == 'link') { |
259 | FusionAxModule::link_by_ids($user_id, $matricule_ax); | |
b65beb64 | 260 | exit; |
b9ad0878 | 261 | } |
73be4434 | 262 | if ($part == 'linknext') { |
b9ad0878 | 263 | $linksToDo = FusionAxModule::find_easy_to_link(10); |
73be4434 SJ |
264 | while ($l = $linksToDo->next()) { |
265 | FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']); | |
b9ad0878 | 266 | } |
b65beb64 | 267 | pl_redirect('fusionax/ids#autolink'); |
b9ad0878 | 268 | } |
73be4434 | 269 | if ($part == 'linkall') { |
b9ad0878 | 270 | $linksToDo = FusionAxModule::find_easy_to_link(0); |
73be4434 SJ |
271 | while ($l = $linksToDo->next()) { |
272 | FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']); | |
b9ad0878 PC |
273 | } |
274 | } | |
275 | { | |
276 | $page->changeTpl('fusionax/ids.tpl'); | |
73be4434 SJ |
277 | $missingInAX = XDB::query('SELECT COUNT(*) |
278 | FROM fusionax_xorg_anciens AS u | |
279 | WHERE u.matricule_ax IS NULL'); | |
280 | if ($missingInAX) { | |
b9ad0878 PC |
281 | $page->assign('nbMissingInAX', $missingInAX->fetchOneCell()); |
282 | } | |
73be4434 SJ |
283 | $missingInXorg = XDB::query('SELECT COUNT(*) |
284 | FROM fusionax_import AS i | |
285 | WHERE i.user_id IS NULL'); | |
286 | if ($missingInXorg) { | |
287 | $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell()); | |
b9ad0878 PC |
288 | } |
289 | $easyToLink = FusionAxModule::find_easy_to_link(10); | |
73be4434 | 290 | if ($easyToLink->total() > 0) { |
b9ad0878 PC |
291 | $page->assign('easyToLink', $easyToLink); |
292 | } | |
293 | } | |
294 | } | |
22f043e4 | 295 | |
b9ad0878 PC |
296 | function handler_misc(&$page) |
297 | { | |
298 | $page->changeTpl('fusionax/misc.tpl'); | |
299 | // deceased | |
73be4434 SJ |
300 | $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased'); |
301 | $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell()); | |
302 | $page->assign('deceasedMissingInXorg', | |
303 | XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_ax, | |
304 | CONCAT(d.prenom, " ", d.nom) AS display_name | |
305 | FROM fusionax_deceased AS d | |
306 | WHERE d.deces_xorg = "0000-00-00" | |
307 | LIMIT 10')); | |
308 | $page->assign('deceasedMissingInAX', | |
309 | XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_xorg, | |
310 | CONCAT(d.prenom, " ", d.nom) AS display_name | |
311 | FROM fusionax_deceased AD d | |
312 | WHERE d.deces_ax = "0000-00-00" | |
313 | LIMIT 10')); | |
314 | $page->assign('deceasedDifferent', | |
315 | XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, | |
316 | d.deces_ax, d.deces_xorg, | |
317 | CONCAT(d.prenom, " ", d.nom) AS display_name | |
318 | FROM fusionax_deceased AS d | |
319 | WHERE d.deces_xorg != "0000-00-00" AND d.deces_ax != "0000-00-00" | |
320 | LIMIT 10')); | |
b9ad0878 PC |
321 | } |
322 | } | |
e8591429 | 323 | |
b9ad0878 | 324 | // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:?> |