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