Commit | Line | Data |
---|---|---|
0337d704 | 1 | <?php |
2 | /*************************************************************************** | |
179afa7f | 3 | * Copyright (C) 2003-2008 Polytechnique.org * |
0337d704 | 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 | ||
0337d704 | 22 | // {{{ Global variables used for the search Queries |
23 | ||
35fa92e8 | 24 | @$globals->search->result_fields = ' |
94f6c381 | 25 | u.user_id, u.promo, u.matricule, u.matricule_ax, |
26 | if(u.nom_usage=\'\', u.nom, u.nom_usage) AS NomSortKey, | |
27 | u.nom_usage,u.date, | |
28 | u.deces!=0 AS dcd,u.deces, | |
29 | u.perms IN (\'admin\',\'user\', \'disabled\') AS inscrit, | |
30 | u.perms != \'pending\' AS wasinscrit, | |
31 | FIND_IN_SET(\'femme\', u.flags) AS sexe, | |
32 | a.alias AS forlife, | |
33 | ad0.text AS app0text, ad0.url AS app0url, ai0.type AS app0type, | |
34 | ad1.text AS app1text, ad1.url AS app1url, ai1.type AS app1type, | |
35 | es.label AS secteur, ef.fonction_fr AS fonction, | |
f781871c SJ |
36 | IF(n1.nat=\'\',n1.pays,n1.nat) AS nat1, n1.a2 AS iso3166_1, |
37 | IF(n2.nat=\'\',n2.pays,n2.nat) AS nat2, n2.a2 AS iso3166_2, | |
38 | IF(n3.nat=\'\',n3.pays,n3.nat) AS nat3, n3.a2 AS iso3166_3, | |
0e5ec860 | 39 | (COUNT(em.email) > 0 OR FIND_IN_SET("googleapps", u.mail_storage) > 0) AS actif,'; |
0337d704 | 40 | // hide private information if not logged |
eaf30d86 | 41 | if (S::logged()) |
94f6c381 | 42 | $globals->search->result_fields .=' |
94f6c381 | 43 | q.profile_freetext AS freetext, |
44 | adr.city, gp.pays AS countrytxt, gr.name AS region, | |
14256b08 GB |
45 | e.entreprise, |
46 | nw.address AS networking_address, | |
47 | nwe.name AS networking_name,'; | |
0337d704 | 48 | else |
94f6c381 | 49 | $globals->search->result_fields .=" |
94f6c381 | 50 | IF(q.profile_freetext_pub='public', q.profile_freetext, '') AS freetext, |
51 | IF(adr.pub='public', adr.city, '') AS city, | |
52 | IF(adr.pub='public', gp.pays, '') AS countrytxt, | |
53 | IF(adr.pub='public', gr.name, '') AS region, | |
14256b08 GB |
54 | IF(e.pub='public', e.entreprise, '') AS entreprise, |
55 | IF(nw.pub='public', nw.address, '') AS networking_address, | |
56 | IF(nw.pub='public', nwe.name, '') AS networking_name,"; | |
35fa92e8 | 57 | @$globals->search->result_where_statement = ' |
14256b08 GB |
58 | LEFT JOIN applis_ins AS ai0 ON (u.user_id = ai0.uid AND ai0.ordre = 0) |
59 | LEFT JOIN applis_def AS ad0 ON (ad0.id = ai0.aid) | |
60 | LEFT JOIN applis_ins AS ai1 ON (u.user_id = ai1.uid AND ai1.ordre = 1) | |
61 | LEFT JOIN applis_def AS ad1 ON (ad1.id = ai1.aid) | |
62 | LEFT JOIN entreprises AS e ON (e.entrid = 0 AND e.uid = u.user_id) | |
63 | LEFT JOIN emploi_secteur AS es ON (e.secteur = es.id) | |
64 | LEFT JOIN fonctions_def AS ef ON (e.fonction = ef.id) | |
f781871c SJ |
65 | LEFT JOIN geoloc_pays AS n1 ON (u.nationalite = n1.a2) |
66 | LEFT JOIN geoloc_pays AS n2 ON (u.nationalite2 = n2.a2) | |
67 | LEFT JOIN geoloc_pays AS n3 ON (u.nationalite3 = n3.a2) | |
14256b08 GB |
68 | LEFT JOIN adresses AS adr ON (u.user_id = adr.uid AND FIND_IN_SET(\'active\',adr.statut)) |
69 | LEFT JOIN geoloc_pays AS gp ON (adr.country = gp.a2) | |
70 | LEFT JOIN geoloc_region AS gr ON (adr.country = gr.a2 AND adr.region = gr.region) | |
71 | LEFT JOIN emails AS em ON (em.uid = u.user_id AND em.flags = \'active\') | |
72 | LEFT JOIN profile_networking AS nw ON (nw.uid = u.user_id) | |
73 | LEFT JOIN profile_networking_enum AS nwe ON (nwe.network_type = nw.network_type)'; | |
0337d704 | 74 | |
75 | // }}} | |
0337d704 | 76 | // {{{ class ThrowError |
77 | ||
78 | /** handle errors for end-users queries | |
79 | * assign the error message and runs the templates | |
80 | * | |
81 | * @author Jean-Sebastien Bedo | |
82 | */ | |
83 | class ThrowError | |
84 | { | |
a2aa8436 | 85 | public static $throwHook = array('ThrowError', 'defaultHandler'); |
86 | ||
0337d704 | 87 | /** constuctor |
88 | * @param $explain string the error (in natural language) | |
89 | */ | |
92432704 | 90 | public function __construct($explain) |
0337d704 | 91 | { |
a2aa8436 | 92 | call_user_func(ThrowError::$throwHook, $explain); |
93 | } | |
94 | ||
95 | /** defaut error handler | |
96 | */ | |
97 | private static function defaultHandler($explain) | |
98 | { | |
d7610c35 FB |
99 | global $globals; |
100 | $page =& Platal::page(); | |
bc67c37c | 101 | $page->changeTpl('search/index.tpl'); |
143ba7c9 | 102 | $page->setTitle('Polytechnique.org - Annuaire'); |
bc67c37c | 103 | $page->assign('baseurl', $globals->baseurl); |
a7d35093 | 104 | $page->trigError($explain); |
c9110c6c | 105 | $page->run(); |
0337d704 | 106 | } |
107 | } | |
108 | ||
109 | // }}} | |
110 | // {{{ class SField [Base class] | |
111 | ||
a7de4ef7 | 112 | /** classe de base représentant un champ de recherche |
113 | * (correspond à un champ du formulaire mais peut être à plusieurs champs de la bdd) | |
114 | * interface étendue pour chaque type de champ particulier | |
0337d704 | 115 | */ |
116 | class SField | |
117 | { | |
118 | // {{{ properties | |
94f6c381 | 119 | |
0337d704 | 120 | /** le nom du champ dans le formulaire HTML */ |
121 | var $fieldFormName; | |
a7de4ef7 | 122 | /** champs de la bdd correspondant à ce champ sous forme d'un tableau */ |
0337d704 | 123 | var $fieldDbName; |
a7de4ef7 | 124 | /** champ résultat dans la requête MySQL correspondant à ce champ |
125 | * (alias utilisé pour la clause ORDER BY) */ | |
0337d704 | 126 | var $fieldResultName; |
a7de4ef7 | 127 | /** valeur du champ instanciée par l'utilisateur */ |
0337d704 | 128 | var $value; |
129 | ||
130 | // }}} | |
131 | // {{{ constructor | |
132 | ||
133 | /** constructeur | |
a7de4ef7 | 134 | * (récupère la requête de l'utilisateur pour ce champ) */ |
0337d704 | 135 | function SField($_fieldFormName, $_fieldDbName='', $_fieldResultName='') |
136 | { | |
137 | $this->fieldFormName = $_fieldFormName; | |
138 | $this->fieldDbName = $_fieldDbName; | |
139 | $this->fieldResultName = $_fieldResultName; | |
140 | $this->get_request(); | |
141 | } | |
142 | ||
143 | // }}} | |
144 | // {{{ function get_request() | |
145 | ||
eaf30d86 | 146 | /** récupérer la requête de l'utilisateur |
a7de4ef7 | 147 | * on met une chaîne vide si le champ n'a pas été complété */ |
0337d704 | 148 | function get_request() |
149 | { | |
5e2307dc | 150 | $this->value = trim(Env::v($this->fieldFormName)); |
0337d704 | 151 | } |
152 | ||
153 | // }}} | |
154 | // {{{ function get_where_statement() | |
94f6c381 | 155 | |
a7de4ef7 | 156 | /** récupérer la clause correspondant au champ dans la clause WHERE de la requête |
eaf30d86 | 157 | * on parcourt l'ensemble des champs de la bdd de $fieldDbName et on associe |
a7de4ef7 | 158 | * à chacun d'entre eux une clause spécifique |
159 | * la clause totale et la disjonction de ces clauses spécifiques */ | |
0337d704 | 160 | function get_where_statement() |
161 | { | |
162 | if ($this->value=='') { | |
163 | return false; | |
164 | } | |
165 | $res = implode(' OR ', array_filter(array_map(array($this, 'get_single_where_statement'), $this->fieldDbName))); | |
166 | return empty($res) ? '' : "($res)"; | |
167 | } | |
168 | ||
169 | // }}} | |
170 | // {{{ function get_order_statement() | |
94f6c381 | 171 | |
a7de4ef7 | 172 | /** récupérer la clause correspondant au champ dans la clause ORDER BY de la requête |
173 | * utilisé par exemple pour placer d'abord le nom égal à la requête avant les approximations */ | |
0337d704 | 174 | function get_order_statement() |
175 | { | |
176 | return false; | |
177 | } | |
178 | ||
179 | // }}} | |
180 | // {{{ function get_select_statement() | |
181 | ||
182 | function get_select_statement() | |
183 | { | |
184 | return false; | |
185 | } | |
186 | ||
187 | // }}} | |
188 | // {{{ function get_url() | |
189 | ||
a7de4ef7 | 190 | /** récupérer le bout d'URL correspondant aux paramètres permettant d'imiter une requête d'un |
191 | * utilisateur assignant la valeur $this->value à ce champ */ | |
0337d704 | 192 | function get_url() |
193 | { | |
194 | if (empty($this->value)) { | |
195 | return false; | |
196 | } else { | |
197 | return $this->fieldFormName.'='.urlencode($this->value); | |
198 | } | |
199 | } | |
200 | ||
201 | // }}} | |
202 | } | |
203 | ||
204 | // }}} | |
205 | // {{{ class QuickSearch [Google Like] | |
206 | ||
207 | class QuickSearch extends SField | |
208 | { | |
209 | // {{{ properties | |
94f6c381 | 210 | |
0337d704 | 211 | /** stores tokens */ |
212 | var $strings; | |
213 | /** stores numerical ranges */ | |
214 | var $ranges; | |
bbf610b8 | 215 | /** stores admin searches */ |
216 | var $email; | |
217 | var $ip; | |
9e7e21fc GB |
218 | /** stores phone number */ |
219 | var $phone; | |
0337d704 | 220 | |
221 | // }}} | |
222 | // {{{ constructor | |
94f6c381 | 223 | |
0337d704 | 224 | function QuickSearch($_fieldFormName) |
225 | { | |
226 | $this->fieldFormName = $_fieldFormName; | |
227 | $this->get_request(); | |
a14159bf | 228 | if (preg_match(":[\]\[{}~/§_`|%$^=+]|\*\*:u", $this->value)) { |
a7de4ef7 | 229 | new ThrowError('Un champ contient un caractère interdit rendant la recherche impossible.'); |
0337d704 | 230 | } |
231 | } | |
232 | ||
233 | // }}} | |
234 | // {{{ function isempty() | |
235 | ||
236 | function isempty() | |
237 | { | |
9e7e21fc | 238 | return empty($this->strings) && empty($this->ranges) && empty($this->email) && empty($this->ip) && empty($this->phone); |
0337d704 | 239 | } |
240 | ||
241 | // }}} | |
242 | // {{{ function get_request() | |
94f6c381 | 243 | |
0337d704 | 244 | function get_request() |
245 | { | |
94f6c381 | 246 | parent::get_request(); |
247 | $s = replace_accent(trim($this->value)); | |
bbf610b8 | 248 | $r = $s = str_replace('*','%',$s); |
249 | ||
250 | if (S::has_perms() && strpos($s, '@') !== false) { | |
251 | $this->email = $s; | |
252 | } else if (S::has_perms() && preg_match('/[0-9]+\.([0-9]+|%)\.([0-9]+|%)\.([0-9]+|%)/', $s)) { | |
253 | $this->ip = $s; | |
254 | } | |
255 | if ($this->email || $this->ip) { | |
256 | $this->strings = $this->ranges = array(); | |
257 | return; | |
258 | } | |
259 | ||
94f6c381 | 260 | $s = preg_replace('!\d+!', ' ', $s); |
94f6c381 | 261 | $this->strings = preg_split("![^a-zA-Z%]+!",$s, -1, PREG_SPLIT_NO_EMPTY); |
8b035281 | 262 | if (count($this->strings) > 5) { |
d7610c35 | 263 | Platal::page()->trigWarning("Tu as indiqué trop d'éléments dans ta recherche, seuls les 5 premiers seront pris en compte"); |
8b035281 FB |
264 | $this->strings = array_slice($this->strings, 0, 5); |
265 | } | |
94f6c381 | 266 | |
bbf610b8 | 267 | $s = preg_replace('! *- *!', '-', $r); |
94f6c381 | 268 | $s = preg_replace('!([<>]) *!', ' \1', $s); |
269 | $s = preg_replace('![^0-9\-><]!', ' ', $s); | |
270 | $s = preg_replace('![<>\-] !', '', $s); | |
271 | $ranges = preg_split('! +!', $s, -1, PREG_SPLIT_NO_EMPTY); | |
272 | $this->ranges=Array(); | |
273 | foreach ($ranges as $r) { | |
274 | if (preg_match('!^([<>]\d{4}|\d{4}(-\d{4})?)$!', $r)) $this->ranges[] = $r; | |
275 | } | |
9e7e21fc GB |
276 | |
277 | $t = preg_replace('!(\d{4}-\d{4}|>\d{4}|<\d{4})!', '', $s); | |
278 | $t = preg_replace('![<>\- ]!', '', $t); | |
279 | if (strlen($t) > 4) { | |
280 | $this->phone = $t; | |
281 | } | |
0337d704 | 282 | } |
283 | ||
284 | // }}} | |
285 | // {{{ function get_where_statement() | |
94f6c381 | 286 | |
0337d704 | 287 | function get_where_statement() |
288 | { | |
94f6c381 | 289 | $where = Array(); |
290 | foreach ($this->strings as $i => $s) { | |
c16b5562 | 291 | if (Env::i('with_soundex') && strlen($s) > 1) { |
94f6c381 | 292 | $t = soundex_fr($s); |
293 | $where[] = "sn$i.soundex = '$t'"; | |
294 | } else { | |
295 | $t = str_replace('*', '%', $s).'%'; | |
296 | $t = str_replace('%%', '%', $t); | |
297 | $where[] = "sn$i.token LIKE '$t'"; | |
298 | } | |
299 | } | |
300 | ||
301 | $wherep = Array(); | |
302 | foreach ($this->ranges as $r) { | |
303 | if (preg_match('!^\d{4}$!', $r)) { | |
304 | $wherep[] = "u.promo=$r"; | |
305 | } elseif (preg_match('!^(\d{4})-(\d{4})$!', $r, $matches)) { | |
306 | $p1=min(intval($matches[1]), intval($matches[2])); | |
307 | $p2=max(intval($matches[1]), intval($matches[2])); | |
308 | $wherep[] = "(u.promo>=$p1 AND u.promo<=$p2)"; | |
309 | } elseif (preg_match('!^<(\d{4})!', $r, $matches)) { | |
310 | $wherep[] = "u.promo<={$matches[1]}"; | |
311 | } elseif (preg_match('!^>(\d{4})!', $r, $matches)) { | |
312 | $wherep[] = "u.promo>={$matches[1]}"; | |
313 | } | |
314 | } | |
315 | if (!empty($wherep)) { | |
0337d704 | 316 | $where[] = '('.join(' OR ',$wherep).')'; |
317 | } | |
bbf610b8 | 318 | if (!empty($this->email)) { |
319 | $where[] = 'ems.email = ' . XDB::escape($this->email); | |
320 | } | |
321 | if (!empty($this->ip)) { | |
9797734d | 322 | $ip = ip_to_uint($this->ip); |
2dc1c17f | 323 | $where[] = "( ls.ip = $ip OR ls.forward_ip = $ip ) AND ls.suid = 0"; |
bbf610b8 | 324 | } |
9e7e21fc GB |
325 | if (!empty($this->phone)){ |
326 | require_once("profil.func.inc.php"); | |
327 | $phone = format_phone_number($this->phone) . "%"; | |
328 | $where[] = 't.search_tel LIKE ' . XDB::escape($phone); | |
329 | } | |
3b2f9d11 | 330 | |
94f6c381 | 331 | return join(" AND ", $where); |
0337d704 | 332 | } |
333 | ||
334 | // }}} | |
335 | // {{{ get_select_statement | |
336 | function get_select_statement() | |
337 | { | |
338 | $join = ""; | |
c0c9f772 | 339 | $and = ''; |
c16b5562 | 340 | $uniq = ''; |
94f6c381 | 341 | foreach ($this->strings as $i => $s) { |
c0c9f772 | 342 | if (!S::logged()) { |
343 | $and = "AND FIND_IN_SET('public', sn$i.flags)"; | |
c16b5562 | 344 | } |
345 | $myu = str_replace('snv', "sn$i", $uniq); | |
346 | $join .= "INNER JOIN search_name AS sn$i ON (u.user_id = sn$i.uid $and$myu)\n"; | |
347 | $uniq .= " AND sn$i.token != snv.token"; | |
0337d704 | 348 | } |
bbf610b8 | 349 | if (!empty($this->email)) { |
350 | $join .= "LEFT JOIN emails AS ems ON (ems.uid = u.user_id)"; | |
351 | } | |
352 | if (!empty($this->ip)) { | |
353 | $join .= "INNER JOIN logger.sessions AS ls ON (ls.uid = u.user_id)\n"; | |
354 | } | |
9e7e21fc GB |
355 | if (!empty($this->phone)) { |
356 | if (!S::logged()) { | |
b235d980 | 357 | $join .= "INNER JOIN profile_phones AS t ON (t.uid = u.user_id AND t.pub = 'public')"; |
9e7e21fc | 358 | } else { |
b235d980 | 359 | $join .= "INNER JOIN profile_phones AS t ON (t.uid = u.user_id)"; |
9e7e21fc GB |
360 | } |
361 | } | |
0337d704 | 362 | return $join; |
363 | } | |
364 | // }}} | |
365 | // {{{ function get_order_statement() | |
94f6c381 | 366 | |
0337d704 | 367 | function get_order_statement() |
368 | { | |
369 | return false; | |
370 | } | |
371 | ||
372 | // }}} | |
373 | // {{{ function get_score_statement | |
94f6c381 | 374 | |
0337d704 | 375 | function get_score_statement() |
376 | { | |
377 | $sum = array('0'); | |
94f6c381 | 378 | foreach ($this->strings as $i => $s) { |
0337d704 | 379 | $sum[] .= "SUM(sn$i.score + IF('$s'=sn$i.token,5,0))"; |
380 | } | |
381 | return join('+', $sum).' AS score'; | |
382 | } | |
383 | ||
384 | // }}} | |
385 | } | |
386 | ||
387 | // }}} | |
388 | // {{{ class NumericSField [Integer fields] | |
389 | ||
a7de4ef7 | 390 | /** classe de champ numérique entier (offset par exemple) |
0337d704 | 391 | */ |
392 | class NumericSField extends SField | |
393 | { | |
394 | // {{{ constructor | |
94f6c381 | 395 | |
0337d704 | 396 | /** constructeur |
a7de4ef7 | 397 | * (récupère la requête de l'utilisateur pour ce champ) */ |
0337d704 | 398 | function NumericSField($_fieldFormName) |
399 | { | |
400 | $this->fieldFormName = $_fieldFormName; | |
401 | $this->get_request(); | |
402 | } | |
403 | ||
404 | // }}} | |
405 | // {{{ function get_request() | |
94f6c381 | 406 | |
a7de4ef7 | 407 | /** récupère la requête de l'utilisateur et échoue s'il ne s'agit pas d'un entier */ |
0337d704 | 408 | function get_request() |
409 | { | |
410 | parent::get_request(); | |
411 | if (empty($this->value)) { | |
412 | $this->value = 0; | |
413 | } | |
414 | if (!preg_match("/^[0-9]+$/", $this->value)) { | |
a7de4ef7 | 415 | new ThrowError('Un champ numérique contient des caractères alphanumériques.'); |
0337d704 | 416 | } |
417 | } | |
94f6c381 | 418 | |
0337d704 | 419 | // }}} |
420 | } | |
421 | ||
422 | // }}} | |
423 | // {{{ class RefSField [ ??? ] | |
424 | ||
425 | class RefSField extends SField | |
426 | { | |
427 | // {{{ properties | |
94f6c381 | 428 | |
0337d704 | 429 | var $refTable; |
430 | var $refAlias; | |
431 | var $refCondition; | |
432 | var $exact = true; | |
433 | ||
434 | // }}} | |
435 | // {{{ constructor | |
436 | ||
437 | function RefSField($_fieldFormName, $_fieldDbName='', $_refTable, $_refAlias, $_refCondition, $_exact=true) | |
438 | { | |
439 | $this->fieldFormName = $_fieldFormName; | |
440 | $this->fieldDbName = $_fieldDbName; | |
441 | $this->refTable = $_refTable; | |
442 | $this->refAlias = $_refAlias; | |
443 | $this->refCondition = $_refCondition; | |
444 | $this->exact = $_exact; | |
445 | $this->get_request(); | |
446 | } | |
447 | ||
448 | // }}} | |
449 | // {{{ function get_request() | |
94f6c381 | 450 | |
0337d704 | 451 | function get_request() { |
452 | parent::get_request(); | |
453 | if ($this->value=='00' || $this->value=='0') { | |
454 | $this->value=''; | |
455 | } | |
456 | } | |
457 | ||
458 | // }}} | |
459 | // {{{ function too_large() | |
460 | ||
461 | function too_large() | |
462 | { | |
463 | return ($this->value==''); | |
464 | } | |
465 | ||
466 | // }}} | |
467 | // {{{ function compare() | |
468 | ||
469 | function compare() | |
470 | { | |
471 | $val = addslashes($this->value); | |
472 | return $this->exact ? "='$val'" : " LIKE '%$val%'"; | |
473 | } | |
474 | ||
475 | // }}} | |
476 | // {{{ function get_single_match_statement() | |
477 | ||
478 | function get_single_match_statement($field) | |
479 | { | |
480 | return $field.$this->compare(); | |
481 | } | |
482 | ||
483 | // }}} | |
484 | // {{{ function get_single_where_statement() | |
485 | ||
486 | function get_single_where_statement($field) | |
487 | { | |
488 | return $this->refTable=='' ? $this->get_single_match_statement($field) : false; | |
489 | } | |
490 | ||
491 | // }}} | |
492 | // {{{ function get_select_statement() | |
493 | ||
494 | function get_select_statement() | |
495 | { | |
496 | if ($this->value=='' || $this->refTable=='') { | |
497 | return false; | |
498 | } | |
499 | $res = implode(' OR ', array_filter(array_map(array($this, 'get_single_match_statement'), $this->fieldDbName))); | |
137e819f FB |
500 | if (is_array($this->refTable)) { |
501 | foreach ($this->refTable as $i => $refT) | |
502 | $last = $i; | |
503 | $inner = ""; | |
504 | foreach ($this->refTable as $i => $refT) | |
505 | $inner .= " INNER JOIN {$refT} AS {$this->refAlias[$i]} ON ({$this->refCondition[$i]} ".(($i == $last)?"AND ($res) ":"").")\n"; | |
506 | return $inner; | |
507 | } else { | |
508 | return "INNER JOIN {$this->refTable} AS {$this->refAlias} ON ({$this->refCondition} AND ($res) )"; | |
509 | } | |
0337d704 | 510 | } |
511 | ||
512 | // }}} | |
513 | } | |
514 | ||
515 | // }}} | |
56670b6a | 516 | |
517 | // {{{ class RefSFieldMultipleTable | |
93f3f260 GB |
518 | class PhoneSField extends RefSField |
519 | { | |
520 | function PhoneSField($_fieldFormName, $_fieldDbName='', $_refTable, $_refAlias, $_refCondition) | |
521 | { | |
522 | $this->RefSField($_fieldFormName, $_fieldDbName, $_refTable, $_refAlias, $_refCondition, true); | |
523 | } | |
524 | ||
525 | function get_request() | |
526 | { | |
527 | require_once("profil.func.inc.php"); | |
528 | $this->value = trim(Env::v($this->fieldFormName)); | |
529 | $this->value = format_phone_number($this->value); | |
530 | } | |
531 | ||
532 | function compare() | |
533 | { | |
534 | return " LIKE '" . addslashes($this->value) . "%'"; | |
535 | } | |
536 | } | |
537 | ||
92c3f9e5 GB |
538 | class IndexSField extends RefSField |
539 | { | |
540 | function IndexSField($_fieldFormName, $_fieldDbName='', $_refTable, $_refAlias, $_refCondition) | |
541 | { | |
542 | $this->RefSField($_fieldFormName, $_fieldDbName, $_refTable, $_refAlias, $_refCondition, true); | |
543 | } | |
544 | ||
545 | function get_request() | |
546 | { | |
547 | $this->value = trim(Env::v($this->fieldFormName)); | |
548 | } | |
549 | } | |
550 | ||
56670b6a | 551 | class MapSField extends RefSField |
552 | { | |
94f6c381 | 553 | var $mapId; |
554 | ||
56670b6a | 555 | function MapSField($_fieldFormName, $_fieldDbName='', $_refTable, $_refAlias, $_refCondition, $_mapId=false) |
556 | { | |
350e6926 | 557 | if ($_mapId === false) |
5e2307dc | 558 | $this->mapId = Env::v($_fieldFormName, ''); |
350e6926 | 559 | else |
560 | $this->mapId = $_mapId; | |
a2aa8436 | 561 | $this->value = $this->mapId; |
350e6926 | 562 | $this->RefSField($_fieldFormName, $_fieldDbName, $_refTable, $_refAlias, $_refCondition, true, false); |
56670b6a | 563 | } |
eaf30d86 | 564 | |
56670b6a | 565 | function get_select_statement() |
566 | { | |
350e6926 | 567 | if ($this->mapId === '') return false; |
56670b6a | 568 | $res = implode(' OR ', array_filter(array_map(array($this, 'get_single_match_statement'), $this->fieldDbName))); |
569 | foreach ($this->refTable as $i => $refT) | |
570 | $last = $i; | |
571 | $inner = ""; | |
572 | foreach ($this->refTable as $i => $refT) | |
573 | $inner .= " INNER JOIN {$refT} AS {$this->refAlias[$i]} ON ({$this->refCondition[$i]} ".(($i == $last)?"AND ($res) ":"").")"; | |
574 | return $inner; | |
575 | } | |
576 | function get_request() | |
577 | { | |
350e6926 | 578 | $this->value = $this->mapId; |
56670b6a | 579 | } |
580 | } | |
581 | ||
0337d704 | 582 | // {{{ class RefWithSoundexSField [ ??? ] |
583 | ||
584 | class RefWithSoundexSField extends RefSField | |
585 | { | |
586 | // {{{ function compare() | |
94f6c381 | 587 | |
0337d704 | 588 | function compare() |
589 | { | |
94f6c381 | 590 | return "='".soundex_fr($this->value)."'"; |
0337d704 | 591 | } |
592 | ||
593 | // }}} | |
594 | } | |
595 | ||
596 | // }}} | |
597 | // {{{ class StringSField [String fields] | |
598 | ||
599 | /** classe de champ texte (nom par exemple) | |
600 | */ | |
601 | class StringSField extends SField | |
602 | { | |
603 | // {{{ function get_request() | |
94f6c381 | 604 | |
a7de4ef7 | 605 | /** récupère la requête de l'utilisateur et échoue si la chaîne contient des caractères |
0337d704 | 606 | * interdits */ |
607 | function get_request() | |
608 | { | |
609 | parent::get_request(); | |
a14159bf | 610 | if (preg_match(":[\]\[<>{}~/§_`|%$^=+]|\*\*:u", $this->value)) { |
a7de4ef7 | 611 | new ThrowError('Un champ contient un caractère interdit rendant la recherche impossible.'); |
0337d704 | 612 | } |
613 | } | |
614 | ||
615 | // }}} | |
616 | // {{{ function length() | |
617 | ||
a7de4ef7 | 618 | /** donne la longueur de la requête de l'utilisateur |
619 | * (au sens strict i.e. pas d'* ni d'espace ou de trait d'union -> les contraintes réellement | |
620 | * imposées par l'utilisateur) */ | |
0337d704 | 621 | function length() |
622 | { | |
a14159bf | 623 | $cleaned = replace_accent(strtolower($this->value)); |
624 | $length = strlen(ereg_replace('[a-z0-9]', '', $cleaned)); | |
625 | return strlen($this->value) - $length; | |
0337d704 | 626 | } |
627 | ||
628 | // }}} | |
629 | // {{{ function too_large() | |
630 | ||
631 | function too_large() | |
632 | { | |
633 | return ($this->length()<2); | |
634 | } | |
635 | ||
636 | // }}} | |
637 | // {{{ function get_single_where_statement() | |
638 | ||
a7de4ef7 | 639 | /** clause WHERE correspondant à un champ de la bdd et à ce champ de formulaire |
640 | * @param field nom de champ de la bdd concerné par la clause */ | |
0337d704 | 641 | function get_single_where_statement($field) |
642 | { | |
643 | $regexp = strtr(addslashes($this->value), '-*', '_%'); | |
644 | return "$field LIKE '$regexp%'"; | |
645 | } | |
646 | ||
647 | // }}} | |
648 | // {{{ function get_order_statement() | |
649 | ||
a7de4ef7 | 650 | /** clause ORDER BY correspondant à ce champ de formulaire */ |
0337d704 | 651 | function get_order_statement() |
652 | { | |
653 | if ($this->value!='' && $this->fieldResultName!='') { | |
654 | return "{$this->fieldResultName}!='".addslashes($this->value)."'"; | |
655 | } else { | |
656 | return false; | |
657 | } | |
658 | } | |
659 | ||
660 | // }}} | |
661 | } | |
662 | ||
663 | // }}} | |
664 | // {{{ class NameSField [Names : serach 'n%' + '% b'] | |
665 | ||
666 | /** classe pour les noms : on cherche en plus du like 'foo%' le like '% foo' (particules) | |
667 | +*/ | |
668 | class NameSField extends StringSField | |
669 | { | |
670 | // {{{ function get_single_where_statement() | |
94f6c381 | 671 | |
0337d704 | 672 | function get_single_where_statement($field) |
673 | { | |
674 | $regexp = strtr(addslashes($this->value), '-*', '_%'); | |
675 | return "$field LIKE '$regexp%' OR $field LIKE '% $regexp%' OR $field LIKE '%-$regexp%'"; | |
676 | } | |
677 | ||
678 | // }}} | |
679 | // {{{ function get_order_statement() | |
94f6c381 | 680 | |
0337d704 | 681 | function get_order_statement() |
682 | { | |
683 | if ($this->value!='' && $this->fieldResultName!='') { | |
684 | return "{$this->fieldResultName} NOT LIKE '".addslashes($this->value)."'"; | |
685 | } else { | |
686 | return false; | |
687 | } | |
688 | } | |
689 | ||
690 | // }}} | |
691 | } | |
692 | ||
693 | // }}} | |
694 | // {{{ class StringWithSoundexSField [Strings + soundex] | |
695 | ||
696 | /** classe de champ texte avec soundex (nom par exemple) | |
697 | */ | |
698 | class StringWithSoundexSField extends StringSField | |
699 | { | |
700 | // {{{ function get_single_where_statement() | |
701 | ||
a7de4ef7 | 702 | /** clause WHERE correspondant à un champ de la bdd et à ce champ de formulaire |
703 | * @param field nom de champ de la bdd concerné par la clause */ | |
0337d704 | 704 | function get_single_where_statement($field) { |
705 | return $field.'="'.soundex_fr($this->value).'"'; | |
706 | } | |
707 | ||
708 | // }}} | |
709 | } | |
710 | ||
711 | // }}} | |
712 | // {{{ class PromoSField [Prom field] | |
713 | ||
714 | /** classe de champ de promotion */ | |
715 | class PromoSField extends SField | |
716 | { | |
717 | // {{{ properties | |
94f6c381 | 718 | |
a7de4ef7 | 719 | /** opérateur de comparaison (<,>,=) de la promo utilisé pour ce champ de formulaire */ |
0337d704 | 720 | var $compareField; |
721 | ||
722 | // }}} | |
723 | // {{{ constructor | |
724 | ||
eaf30d86 | 725 | /** constructeur |
a7de4ef7 | 726 | * compareField est un champ de formulaire très simple qui ne sert qu'à la construction de la |
0337d704 | 727 | * clause WHERE de la promo */ |
728 | function PromoSField($_fieldFormName, $_compareFieldFormName, $_fieldDbName, $_fieldResultName) | |
729 | { | |
730 | parent::SField($_fieldFormName, $_fieldDbName, $_fieldResultName); | |
731 | $this->compareField = new SField($_compareFieldFormName); | |
732 | } | |
733 | ||
734 | // }}} | |
735 | // {{{ function get_request() | |
736 | ||
a7de4ef7 | 737 | /** récupère la requête utilisateur et échoue si le champ du formulaire ne représente pas une |
738 | * promotion (nombre à 4 chiffres) */ | |
0337d704 | 739 | function get_request() |
740 | { | |
741 | parent::get_request(); | |
742 | if (preg_match('/^[0-9]{2}$/', $this->value)){ | |
743 | $this->value = intval($this->value) + 1900; | |
744 | } | |
745 | if (!(empty($this->value) or preg_match('/^[0-9]{4}$/', $this->value))) { | |
a7de4ef7 | 746 | new ThrowError('La promotion est une année à quatre chiffres.'); |
0337d704 | 747 | } |
748 | } | |
749 | ||
750 | // }}} | |
751 | // {{{ function is_a_single_promo() | |
752 | ||
a7de4ef7 | 753 | /** teste si la requête est de la forme =promotion -> contrainte forte imposée -> elle suffit |
754 | * pour autoriser un affichage des résultats alors que <promotion est insuffisant */ | |
0337d704 | 755 | function is_a_single_promo() |
756 | { | |
757 | return ($this->compareField->value=='=' && $this->value!=''); | |
758 | } | |
759 | ||
760 | // }}} | |
761 | // {{{ function too_large() | |
762 | ||
763 | function too_large() | |
764 | { | |
765 | return !$this->is_a_single_promo(); | |
766 | } | |
767 | ||
768 | // }}} | |
769 | // {{{ function get_single_where_statement() | |
770 | ||
a7de4ef7 | 771 | /** clause WHERE correspondant à ce champ */ |
0337d704 | 772 | function get_single_where_statement($field) |
773 | { | |
774 | return $field.$this->compareField->value.$this->value; | |
775 | } | |
776 | ||
777 | // }}} | |
778 | // {{{ function get_url() | |
779 | ||
a7de4ef7 | 780 | /** récupérer le bout d'URL correspondant aux paramètres permettant d'imiter une requête |
781 | * d'un utilisateur assignant la valeur $this->value à ce champ et assignant l'opérateur de | |
782 | * comparaison adéquat */ | |
0337d704 | 783 | function get_url() |
784 | { | |
785 | if (!($u=parent::get_url())) { | |
786 | return false; | |
787 | } | |
788 | return $u.'&'.$this->compareField->get_url(); | |
789 | } | |
790 | ||
791 | // }}} | |
792 | } | |
793 | ||
794 | // }}} | |
795 | // {{{ class SFieldGroup [Group fields] | |
796 | ||
797 | /** classe groupant des champs de formulaire de recherche */ | |
798 | class SFieldGroup | |
799 | { | |
800 | // {{{ properties | |
94f6c381 | 801 | |
a7de4ef7 | 802 | /** tableau des classes correspondant aux champs groupés */ |
0337d704 | 803 | var $fields; |
804 | /** type de groupe : ET ou OU */ | |
805 | var $and; | |
806 | ||
807 | // }}} | |
808 | // {{{ constuctor | |
809 | ||
810 | /** constructeur */ | |
811 | function SFieldGroup($_and, $_fields) | |
812 | { | |
813 | $this->fields = $_fields; | |
814 | $this->and = $_and; | |
63fac48e FB |
815 | foreach ($this->fields as $key=>&$field) { |
816 | if (is_null($field)) { | |
817 | unset($this->fields[$key]); | |
818 | } | |
819 | } | |
0337d704 | 820 | } |
821 | ||
822 | // }}} | |
823 | // {{{ function too_large() | |
824 | ||
825 | function too_large() | |
826 | { | |
827 | $b = true; | |
a2aa8436 | 828 | for ($i=0 ; $b && $i<count($this->fields) ; $i++) { |
63fac48e FB |
829 | if (!is_null($this->fields[$i])) { |
830 | $b = $b && $this->fields[$i]->too_large(); | |
831 | } | |
0337d704 | 832 | } |
833 | return $b; | |
834 | } | |
835 | ||
836 | // }}} | |
837 | // {{{ function field_get_select() | |
838 | ||
839 | function field_get_select($f) | |
840 | { | |
841 | return $f->get_select_statement(); | |
842 | } | |
843 | ||
844 | // }}} | |
845 | // {{{ function field_get_where() | |
846 | ||
a7de4ef7 | 847 | /** récupérer la clause WHERE d'un objet champ de recherche */ |
0337d704 | 848 | function field_get_where($f) |
849 | { | |
850 | return $f->get_where_statement(); | |
851 | } | |
852 | ||
853 | // }}} | |
854 | // {{{ function field_get_order() | |
855 | ||
a7de4ef7 | 856 | /** récupérer la clause ORDER BY d'un objet champ de recherche */ |
0337d704 | 857 | function field_get_order($f) |
858 | { | |
859 | return $f->get_order_statement(); | |
860 | } | |
861 | ||
862 | // }}} | |
863 | // {{{ function field_get_url() | |
864 | ||
a7de4ef7 | 865 | /** récupérer le bout d'URL correspondant à un objet champ de recherche */ |
0337d704 | 866 | function field_get_url($f) |
867 | { | |
868 | return $f->get_url(); | |
869 | } | |
94f6c381 | 870 | |
0337d704 | 871 | // }}} |
872 | // {{{ function get_select_statement() | |
873 | ||
874 | function get_select_statement() | |
875 | { | |
876 | return implode(' ', array_filter(array_map(array($this, 'field_get_select'), $this->fields))); | |
877 | } | |
878 | ||
879 | // }}} | |
880 | // {{{ function get_where_statement() | |
881 | ||
a7de4ef7 | 882 | /** récupérer la clause WHERE du groupe de champs = conjonction (ET) ou disjonction (OU) de |
883 | * clauses des champs élémentaires */ | |
0337d704 | 884 | function get_where_statement() |
885 | { | |
886 | $joinText = $this->and ? ' AND ' : ' OR '; | |
887 | $res = implode($joinText, array_filter(array_map(array($this, 'field_get_where'), $this->fields))); | |
888 | return $res == '' ? '' : "($res)"; | |
889 | } | |
890 | ||
891 | // }}} | |
892 | // {{{ function get_order_statement() | |
893 | ||
a7de4ef7 | 894 | /** récupérer la clause ORDER BY du groupe de champs = conjonction (ET) ou disjonction (OU) de |
895 | * clauses des champs élémentaires */ | |
0337d704 | 896 | function get_order_statement() |
897 | { | |
898 | $order = array_filter(array_map(array($this, 'field_get_order'), $this->fields)); | |
899 | return count($order)>0 ? implode(',', $order) : false; | |
900 | } | |
901 | ||
902 | // }}} | |
903 | // {{{ function get_url() | |
904 | ||
a7de4ef7 | 905 | /** récupérer le bout d'URL correspondant à ce groupe de champs = concaténation des bouts d'URL |
906 | * des champs élémentaires */ | |
0337d704 | 907 | function get_url($others=Array()) |
908 | { | |
909 | $url = array_filter(array_map(array($this, 'field_get_url'), $this->fields)); | |
910 | foreach ($url as $key=>$val) { | |
911 | if (empty($val)) { | |
912 | unset($url[$key]); | |
913 | } | |
914 | } | |
915 | foreach ($others as $key=>$val) { | |
916 | if (!empty($val)) { | |
917 | $url[] = "$key=$val"; | |
918 | } | |
919 | } | |
920 | return count($url)>0 ? implode('&', $url) : false; | |
921 | } | |
922 | ||
923 | // }}} | |
924 | } | |
925 | ||
926 | // }}} | |
927 | ||
a7de4ef7 | 928 | // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: |
0337d704 | 929 | ?> |