projects / platal.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
Adds UFC_Nationality and UFC_Binet
[platal.git] / classes / userfilter.php
CommitLineData
a087cc8d
FB		 1<?php
2/***************************************************************************
3 * Copyright (C) 2003-2009 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
d865c296
FB		 22
23/******************
24 * CONDITIONS
25 ******************/
26
8363588b 27// {{{ interface UserFilterCondition
2d83cac9
RB		 28/** This interface describe objects which filter users based
29 * on various parameters.
30 * The parameters of the filter must be given to the constructor.
31 * The buildCondition function is called by UserFilter when
32 * actually building the query. That function must call
33 * $uf->addWheteverFilter so that the UserFilter makes
34 * adequate joins. It must return the 'WHERE' condition to use
35 * with the filter.
36 */
dcc63ed5 37interface UserFilterCondition extends PlFilterCondition
a087cc8d 38{
a087cc8d 39}
8363588b 40// }}}
a087cc8d 41
8363588b 42// {{{ class UFC_Profile
53eae167
RB		 43/** Filters users who have a profile
44 */
eb1449b8
FB		 45class UFC_Profile implements UserFilterCondition
46{
dcc63ed5 47 public function buildCondition(PlFilter &$uf)
eb1449b8
FB		 48 {
49 return '$PID IS NOT NULL';
50 }
51}
8363588b 52// }}}
eb1449b8 53
8363588b 54// {{{ class UFC_Promo
5d2e55c7 55/** Filters users based on promotion
53eae167
RB		 56 * @param $comparison Comparison operator (>, =, ...)
57 * @param $grade Formation on which to restrict, UserFilter::DISPLAY for "any formation"
5d2e55c7 58 * @param $promo Promotion on which the filter is based
53eae167 59 */
a087cc8d
FB		 60class UFC_Promo implements UserFilterCondition
61{
a087cc8d
FB		 62
63 private $grade;
64 private $promo;
65 private $comparison;
66
67 public function __construct($comparison, $grade, $promo)
68 {
69 $this->grade = $grade;
70 $this->comparison = $comparison;
71 $this->promo = $promo;
38c6fe96
FB		 72 if ($this->grade != UserFilter::DISPLAY) {
73 UserFilter::assertGrade($this->grade);
74 }
a087cc8d
FB		 75 }
76
dcc63ed5 77 public function buildCondition(PlFilter &$uf)
a087cc8d 78 {
38c6fe96
FB		 79 if ($this->grade == UserFilter::DISPLAY) {
80 $sub = $uf->addDisplayFilter();
1a23a02b 81 return XDB::format('pd' . $sub . '.promo ' . $this->comparison . ' {?}', $this->promo);
38c6fe96
FB		 82 } else {
83 $sub = $uf->addEducationFilter(true, $this->grade);
84 $field = 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
85 return $field . ' IS NOT NULL AND ' . $field . ' ' . $this->comparison . ' ' . XDB::format('{?}', $this->promo);
86 }
784745ce
FB		 87 }
88}
8363588b 89// }}}
784745ce 90
8363588b 91// {{{ class UFC_Name
53eae167 92/** Filters users based on name
5d2e55c7 93 * @param $type Type of name field on which filtering is done (firstname, lastname...)
53eae167 94 * @param $text Text on which to filter
5d2e55c7 95 * @param $mode Flag indicating search type (prefix, suffix, with particule...)
53eae167 96 */
784745ce
FB		 97class UFC_Name implements UserFilterCondition
98{
99 const PREFIX = 1;
100 const SUFFIX = 2;
101 const PARTICLE = 7;
102 const VARIANTS = 8;
103 const CONTAINS = 3;
104
105 private $type;
106 private $text;
107 private $mode;
108
109 public function __construct($type, $text, $mode)
110 {
111 $this->type = $type;
112 $this->text = $text;
113 $this->mode = $mode;
114 }
115
116 private function buildNameQuery($type, $variant, $where, UserFilter &$uf)
117 {
118 $sub = $uf->addNameFilter($type, $variant);
119 return str_replace('$ME', 'pn' . $sub, $where);
120 }
121
dcc63ed5 122 public function buildCondition(PlFilter &$uf)
784745ce
FB		 123 {
124 $left = '$ME.name';
125 $op = ' LIKE ';
126 if (($this->mode & self::PARTICLE) == self::PARTICLE) {
127 $left = 'CONCAT($ME.particle, \' \', $ME.name)';
128 }
129 if (($this->mode & self::CONTAINS) == 0) {
130 $right = XDB::format('{?}', $this->text);
131 $op = ' = ';
132 } else if (($this->mode & self::CONTAINS) == self::PREFIX) {
133 $right = XDB::format('CONCAT({?}, \'%\')', $this->text);
134 } else if (($this->mode & self::CONTAINS) == self::SUFFIX) {
135 $right = XDB::format('CONCAT(\'%\', {?})', $this->text);
136 } else {
137 $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text);
138 }
139 $cond = $left . $op . $right;
140 $conds = array($this->buildNameQuery($this->type, null, $cond, $uf));
d865c296 141 if (($this->mode & self::VARIANTS) != 0 && isset(UserFilter::$name_variants[$this->type])) {
784745ce
FB		 142 foreach (UserFilter::$name_variants[$this->type] as $var) {
143 $conds[] = $this->buildNameQuery($this->type, $var, $cond, $uf);
144 }
145 }
146 return implode(' OR ', $conds);
a087cc8d
FB		 147 }
148}
8363588b 149// }}}
a087cc8d 150
40585144
RB		 151// {{{ class UFC_NameTokens
152/** Selects users based on tokens in their name (for quicksearch)
153 * @param $tokens An array of tokens to search
154 * @param $flags Flags the tokens must have (e.g 'public' for public search)
155 * @param $soundex (bool) Whether those tokens are fulltext or soundex
156 */
157class UFC_NameTokens implements UserFilterCondition
158{
159 /* Flags */
160 const FLAG_PUBLIC = 'public';
161
162 private $tokens;
163 private $flags;
164 private $soundex;
79a0b464 165 private $exact;
40585144 166
79a0b464 167 public function __construct($tokens, $flags = array(), $soundex = false, $exact = false)
40585144
RB		 168 {
169 $this->tokens = $tokens;
170 if (is_array($flags)) {
171 $this->flags = $flags;
172 } else {
173 $this->flags = array($flags);
174 }
175 $this->soundex = $soundex;
79a0b464 176 $this->exact = $exact;
40585144
RB		 177 }
178
dcc63ed5 179 public function buildCondition(PlFilter &$uf)
40585144 180 {
79a0b464 181 $sub = $uf->addNameTokensFilter(!($this->exact || $this->soundex));
40585144
RB		 182 $conds = array();
183 if ($this->soundex) {
184 $conds[] = $sub . '.soundex IN ' . XDB::formatArray($this->tokens);
79a0b464
RB		 185 } else if ($this->exact) {
186 $conds[] = $sub . '.token IN ' . XDB::formatArray($this->tokens);
40585144
RB		 187 } else {
188 $tokconds = array();
189 foreach ($this->tokens as $token) {
190 $tokconds[] = $sub . '.token LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $token);
191 }
192 $conds[] = implode(' OR ', $tokconds);
193 }
194
195 if ($this->flags != null) {
196 $conds[] = $sub . '.flags IN ' . XDB::formatArray($this->flags);
197 }
198
199 return implode(' AND ', $conds);
200 }
201}
202// }}}
203
0fb3713c
RB		 204// {{{ class UFC_Nationality
205class UFC_Nationality implements UserFilterCondition
206{
207 private $nationality;
208
209 public function __construct($nationality)
210 {
211 $this->nationality = $nationality;
212 }
213
214 public function buildCondition(PlFilter &$uf)
215 {
216 $uf->requireProfiles();
217 $nat = XDB::format('{?}', $this->nationality);
218 $conds = array(
219 'p.nationality1 = ' . $nat,
220 'p.nationality2 = ' . $nat,
221 'p.nationality3 = ' . $nat,
222 );
223 return implode(' OR ', $conds);
224 }
225}
226// }}}
227
8363588b 228// {{{ class UFC_Dead
53eae167
RB		 229/** Filters users based on death date
230 * @param $comparison Comparison operator
231 * @param $date Date to which death date should be compared
232 */
4927ee54
FB		 233class UFC_Dead implements UserFilterCondition
234{
38c6fe96
FB		 235 private $comparison;
236 private $date;
237
238 public function __construct($comparison = null, $date = null)
4927ee54 239 {
38c6fe96
FB		 240 $this->comparison = $comparison;
241 $this->date = $date;
4927ee54
FB		 242 }
243
dcc63ed5 244 public function buildCondition(PlFilter &$uf)
4927ee54 245 {
38c6fe96
FB		 246 $str = 'p.deathdate IS NOT NULL';
247 if (!is_null($this->comparison)) {
248 $str .= ' AND p.deathdate ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date));
4927ee54 249 }
38c6fe96 250 return $str;
4927ee54
FB		 251 }
252}
8363588b 253// }}}
4927ee54 254
8363588b 255// {{{ class UFC_Registered
53eae167
RB		 256/** Filters users based on registration state
257 * @param $active Whether we want to use only "active" users (i.e with a valid redirection)
258 * @param $comparison Comparison operator
259 * @param $date Date to which users registration date should be compared
260 */
4927ee54
FB		 261class UFC_Registered implements UserFilterCondition
262{
263 private $active;
38c6fe96
FB		 264 private $comparison;
265 private $date;
266
267 public function __construct($active = false, $comparison = null, $date = null)
4927ee54 268 {
b2e8fc54 269 $this->active = $active;
38c6fe96
FB		 270 $this->comparison = $comparison;
271 $this->date = $date;
4927ee54
FB		 272 }
273
dcc63ed5 274 public function buildCondition(PlFilter &$uf)
4927ee54
FB		 275 {
276 if ($this->active) {
38c6fe96 277 $date = 'a.uid IS NOT NULL AND a.state = \'active\'';
4927ee54 278 } else {
38c6fe96
FB		 279 $date = 'a.uid IS NOT NULL AND a.state != \'pending\'';
280 }
281 if (!is_null($this->comparison)) {
282 $date .= ' AND a.registration_date ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date));
4927ee54 283 }
38c6fe96 284 return $date;
4927ee54
FB		 285 }
286}
8363588b 287// }}}
4927ee54 288
8363588b 289// {{{ class UFC_ProfileUpdated
53eae167
RB		 290/** Filters users based on profile update date
291 * @param $comparison Comparison operator
292 * @param $date Date to which profile update date must be compared
293 */
7e735012
FB		 294class UFC_ProfileUpdated implements UserFilterCondition
295{
296 private $comparison;
297 private $date;
298
299 public function __construct($comparison = null, $date = null)
300 {
301 $this->comparison = $comparison;
302 $this->date = $date;
303 }
304
dcc63ed5 305 public function buildCondition(PlFilter &$uf)
7e735012
FB		 306 {
307 return 'p.last_change ' . $this->comparison . XDB::format(' {?}', date('Y-m-d H:i:s', $this->date));
308 }
309}
8363588b 310// }}}
7e735012 311
8363588b 312// {{{ class UFC_Birthday
53eae167
RB		 313/** Filters users based on next birthday date
314 * @param $comparison Comparison operator
315 * @param $date Date to which users next birthday date should be compared
316 */
7e735012
FB		 317class UFC_Birthday implements UserFilterCondition
318{
319 private $comparison;
320 private $date;
321
322 public function __construct($comparison = null, $date = null)
323 {
324 $this->comparison = $comparison;
325 $this->date = $date;
326 }
327
dcc63ed5 328 public function buildCondition(PlFilter &$uf)
7e735012
FB		 329 {
330 return 'p.next_birthday ' . $this->comparison . XDB::format(' {?}', date('Y-m-d', $this->date));
331 }
332}
8363588b 333// }}}
7e735012 334
8363588b 335// {{{ class UFC_Sex
53eae167
RB		 336/** Filters users based on sex
337 * @parm $sex One of User::GENDER_MALE or User::GENDER_FEMALE, for selecting users
338 */
4927ee54
FB		 339class UFC_Sex implements UserFilterCondition
340{
341 private $sex;
342 public function __construct($sex)
343 {
344 $this->sex = $sex;
345 }
346
dcc63ed5 347 public function buildCondition(PlFilter &$uf)
4927ee54
FB		 348 {
349 if ($this->sex != User::GENDER_MALE && $this->sex != User::GENDER_FEMALE) {
350 return self::COND_FALSE;
351 } else {
24e08e33 352 return XDB::format('p.sex = {?}', $this->sex == User::GENDER_FEMALE ? 'female' : 'male');
4927ee54
FB		 353 }
354 }
355}
8363588b 356// }}}
4927ee54 357
8363588b 358// {{{ class UFC_Group
53eae167 359/** Filters users based on group membership
5d2e55c7
RB		 360 * @param $group Group whose members we are selecting
361 * @param $anim Whether to restrict selection to animators of that group
53eae167 362 */
4927ee54
FB		 363class UFC_Group implements UserFilterCondition
364{
365 private $group;
5d2e55c7
RB		 366 private $anim;
367 public function __construct($group, $anim = false)
4927ee54
FB		 368 {
369 $this->group = $group;
5d2e55c7 370 $this->anim = $anim;
4927ee54
FB		 371 }
372
dcc63ed5 373 public function buildCondition(PlFilter &$uf)
4927ee54
FB		 374 {
375 $sub = $uf->addGroupFilter($this->group);
376 $where = 'gpm' . $sub . '.perms IS NOT NULL';
5d2e55c7 377 if ($this->anim) {
4927ee54
FB		 378 $where .= ' AND gpm' . $sub . '.perms = \'admin\'';
379 }
380 return $where;
381 }
382}
8363588b 383// }}}
4927ee54 384
0fb3713c
RB		 385// {{{ class UFC_Binet
386class UFC_Binet implements UserFilterCondition
387{
388 private $binet;
389
390 public function __construct($binet)
391 {
392 $this->binet = $binet;
393 }
394
395 public function buildCondition(PlFilter &$uf)
396 {
397 $sub = $uf->addBinetsFilter();
398 return $sub . 'binet_id = ' . XDB::format('{?}', $this->binet);
399 }
400}
401// }}}
402
8363588b 403// {{{ class UFC_Email
53eae167
RB		 404/** Filters users based on email address
405 * @param $email Email whose owner we are looking for
406 */
aa21c568
FB		 407class UFC_Email implements UserFilterCondition
408{
409 private $email;
410 public function __construct($email)
411 {
412 $this->email = $email;
413 }
414
dcc63ed5 415 public function buildCondition(PlFilter &$uf)
aa21c568
FB		 416 {
417 if (User::isForeignEmailAddress($this->email)) {
418 $sub = $uf->addEmailRedirectFilter($this->email);
419 return XDB::format('e' . $sub . '.email IS NOT NULL OR a.email = {?}', $this->email);
21401768
FB		 420 } else if (User::isVirtualEmailAddress($this->email)) {
421 $sub = $uf->addVirtualEmailFilter($this->email);
422 return 'vr' . $sub . '.redirect IS NOT NULL';
aa21c568 423 } else {
21401768
FB		 424 @list($user, $domain) = explode('@', $this->email);
425 $sub = $uf->addAliasFilter($user);
aa21c568
FB		 426 return 'al' . $sub . '.alias IS NOT NULL';
427 }
428 }
429}
8363588b 430// }}}
d865c296 431
8363588b 432// {{{ class UFC_EmailList
5d2e55c7 433/** Filters users based on an email list
53eae167
RB		 434 * @param $emails List of emails whose owner must be selected
435 */
21401768
FB		 436class UFC_EmailList implements UserFilterCondition
437{
438 private $emails;
439 public function __construct($emails)
440 {
441 $this->emails = $emails;
442 }
443
dcc63ed5 444 public function buildCondition(PlFilter &$uf)
21401768
FB		 445 {
446 $email = null;
447 $virtual = null;
448 $alias = null;
449 $cond = array();
450
451 if (count($this->emails) == 0) {
dcc63ed5 452 return PlFilterCondition::COND_TRUE;
21401768
FB		 453 }
454
455 foreach ($this->emails as $entry) {
456 if (User::isForeignEmailAddress($entry)) {
457 if (is_null($email)) {
458 $email = $uf->addEmailRedirectFilter();
459 }
460 $cond[] = XDB::format('e' . $email . '.email = {?} OR a.email = {?}', $entry, $entry);
461 } else if (User::isVirtualEmailAddress($entry)) {
462 if (is_null($virtual)) {
463 $virtual = $uf->addVirtualEmailFilter();
464 }
465 $cond[] = XDB::format('vr' . $virtual . '.redirect IS NOT NULL AND v' . $virtual . '.alias = {?}', $entry);
466 } else {
467 if (is_null($alias)) {
468 $alias = $uf->addAliasFilter();
469 }
470 @list($user, $domain) = explode('@', $entry);
471 $cond[] = XDB::format('al' . $alias . '.alias = {?}', $user);
472 }
473 }
474 return '(' . implode(') OR (', $cond) . ')';
475 }
476}
8363588b 477// }}}
d865c296 478
8363588b 479// {{{ class UFC_Address
2b9ca54d 480abstract class UFC_Address implements UserFilterCondition
c4b24511 481{
2b9ca54d 482 /** Valid address type ('hq' is reserved for company addresses)
036d1637 483 */
2b9ca54d
RB		 484 const TYPE_HOME = 1;
485 const TYPE_PRO = 2;
486 const TYPE_ANY = 3;
c4b24511 487
2b9ca54d 488 /** Text for these types
036d1637 489 */
2b9ca54d
RB		 490 protected static $typetexts = array(
491 self::TYPE_HOME => 'home',
492 self::TYPE_PRO => 'pro',
493 );
494
495 protected $type;
c4b24511 496
036d1637
RB		 497 /** Flags for addresses
498 */
499 const FLAG_CURRENT = 0x0001;
500 const FLAG_TEMP = 0x0002;
501 const FLAG_SECOND = 0x0004;
502 const FLAG_MAIL = 0x0008;
503 const FLAG_CEDEX = 0x0010;
504
505 // Binary OR of those flags
506 const FLAG_ANY = 0x001F;
507
508 /** Text of these flags
509 */
2b9ca54d 510 protected static $flagtexts = array(
036d1637
RB		 511 self::FLAG_CURRENT => 'current',
512 self::FLAG_TEMP => 'temporary',
513 self::FLAG_SECOND => 'secondary',
514 self::FLAG_MAIL => 'mail',
515 self::FLAG_CEDEX => 'cedex',
516 );
517
2b9ca54d
RB		 518 protected $flags;
519
520 public function __construct($type = null, $flags = null)
521 {
522 $this->type = $type;
523 $this->flags = $flags;
524 }
525
526 protected function initConds($sub)
527 {
528 $conds = array();
529 $types = array();
530 foreach (self::$typetexts as $flag => $type) {
531 if ($flag & $this->type) {
532 $types[] = $type;
533 }
534 }
535 if (count($types)) {
536 $conds[] = $sub . '.type IN ' . XDB::formatArray($types);
537 }
538
539 if ($this->flags != self::FLAG_ANY) {
540 foreach(self::$flagtexts as $flag => $text) {
541 if ($flag & $this->flags) {
542 $conds[] = 'FIND_IN_SET(' . XDB::format('{?}', $text) . ', ' . $sub . '.flags)';
543 }
544 }
545 }
546 return $conds;
547 }
548
549}
550// }}}
551
552// {{{ class UFC_AddressText
553/** Select users based on their address, using full text search
554 * @param $text Text for filter in fulltext search
555 * @param $textSearchMode Mode for search (PREFIX, SUFFIX, ...)
556 * @param $type Filter on address type
557 * @param $flags Filter on address flags
558 * @param $country Filter on address country
559 * @param $locality Filter on address locality
560 */
561class UFC_AddressText extends UFC_Address
562{
563 /** Flags for text search
036d1637 564 */
2b9ca54d
RB		 565 const PREFIX = 0x0001;
566 const SUFFIX = 0x0002;
567 const CONTAINS = 0x0003;
568
036d1637 569 private $text;
2b9ca54d
RB		 570 private $textSearchMode;
571
572 public function __construct($text = null, $textSearchMode = self::CONTAINS,
573 $type = null, $flags = self::FLAG_ANY, $country = null, $locality = null)
574 {
575 parent::__construct($type, $flags);
576 $this->text = $text;
577 $this->textSearchMode = $textSearchMode;
578 $this->country = $country;
579 $this->locality = $locality;
580 }
581
582 private function mkMatch($txt)
583 {
584 $op = ' LIKE ';
585 if (($this->textSearchMode & self::CONTAINS) == 0) {
586 $right = XDB::format('{?}', $this->text);
587 $op = ' = ';
588 } else if (($this->mode & self::CONTAINS) == self::PREFIX) {
589 $right = XDB::format('CONCAT({?}, \'%\')', $this->text);
590 } else if (($this->mode & self::CONTAINS) == self::SUFFIX) {
591 $right = XDB::format('CONCAT(\'%\', {?})', $this->text);
592 } else {
593 $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text);
594 }
595 return $op . $right;
596 }
597
598 public function buildCondition(PlFilter &$uf)
599 {
600 $sub = $uf->addAddressFilter();
601 $conds = $this->initConds($sub);
602 if ($this->text != null) {
603 $conds[] = $sub . '.text' . $this->mkMatch($this->text);
604 }
605
606 if ($this->country != null) {
607 $subc = $uf->addAddressCountryFilter();
608 $subconds = array();
609 $subconds[] = $subc . '.country' . $this->mkMatch($this->country);
610 $subconds[] = $subc . '.countryFR' . $this->mkMatch($this->country);
611 $conds[] = implode(' OR ', $subconds);
612 }
613
614 if ($this->locality != null) {
615 $subl = $uf->addAddressLocalityFilter();
616 $conds[] = $subl . '.name' . $this->mkMatch($this->locality);
617 }
618
619 return implode(' AND ', $conds);
620 }
621}
622// }}}
623
624// {{{ class UFC_AddressFields
625/** Filters users based on their address,
626 * @param $type Filter on address type
627 * @param $flags Filter on address flags
628 * @param $countryId Filter on address countryId
629 * @param $administrativeAreaId Filter on address administrativeAreaId
630 * @param $subAdministrativeAreaId Filter on address subAdministrativeAreaId
631 * @param $localityId Filter on address localityId
632 * @param $postalCode Filter on address postalCode
633 */
634class UFC_AddressFields extends UFC_Address
635{
636 /** Data of the filter
637 */
036d1637
RB		 638 private $countryId;
639 private $administrativeAreaId;
640 private $subAdministrativeAreaId;
641 private $localityId;
642 private $postalCode;
643
2b9ca54d 644 public function __construct($type = null, $flags = self::FLAG_ANY, $countryId = null, $administrativeAreaId = null,
036d1637
RB		 645 $subAdministrativeAreaId = null, $localityId = null, $postalCode = null)
646 {
2b9ca54d 647 parent::__construct($type, $flags);
036d1637
RB		 648 $this->countryId = $countryId;
649 $this->administrativeAreaId = $administrativeAreaId;
650 $this->subAdministrativeAreaId = $subAdministrativeAreaId;
651 $this->localityId = $localityId;
652 $this->postalCode = $postalCode;
c4b24511
RB		 653 }
654
dcc63ed5 655 public function buildCondition(PlFilter &$uf)
c4b24511 656 {
036d1637 657 $sub = $uf->addAddressFilter();
2b9ca54d 658 $conds = $this->initConds();
036d1637
RB		 659
660 if ($this->countryId != null) {
661 $conds[] = $sub . '.countryId = ' . XDB::format('{?}', $this->countryId);
662 }
663 if ($this->administrativeAreaId != null) {
664 $conds[] = $sub . '.administrativeAreaId = ' . XDB::format('{?}', $this->administrativeAreaId);
665 }
666 if ($this->subAdministrativeAreaId != null) {
667 $conds[] = $sub . '.subAdministrativeAreaId = ' . XDB::format('{?}', $this->subAdministrativeAreaId);
668 }
669 if ($this->localityId != null) {
670 $conds[] = $sub . '.localityId = ' . XDB::format('{?}', $this->localityId);
671 }
672 if ($this->postalCode != null) {
673 $conds[] = $sub . '.postalCode = ' . XDB::format('{?}', $this->postalCode);
674 }
675
676 return implode(' AND ', $conds);
c4b24511
RB		 677 }
678}
8363588b 679// }}}
c4b24511 680
8363588b 681// {{{ class UFC_Corps
4083b126
RB		 682/** Filters users based on the corps they belong to
683 * @param $corps Corps we are looking for (abbreviation)
684 * @param $type Whether we search for original or current corps
685 */
686class UFC_Corps implements UserFilterCondition
687{
5d2e55c7
RB		 688 const CURRENT = 1;
689 const ORIGIN = 2;
4083b126
RB		 690
691 private $corps;
692 private $type;
693
694 public function __construct($corps, $type = self::CURRENT)
695 {
696 $this->corps = $corps;
697 $this->type = $type;
698 }
699
dcc63ed5 700 public function buildCondition(PlFilter &$uf)
4083b126 701 {
5d2e55c7
RB		 702 /** Tables shortcuts:
703 * pc for profile_corps,
4083b126
RB		 704 * pceo for profile_corps_enum - orginal
705 * pcec for profile_corps_enum - current
706 */
707 $sub = $uf->addCorpsFilter($this->type);
708 $cond = $sub . '.abbreviation = ' . $corps;
709 return $cond;
710 }
711}
8363588b 712// }}}
4083b126 713
8363588b 714// {{{ class UFC_Corps_Rank
4083b126
RB		 715/** Filters users based on their rank in the corps
716 * @param $rank Rank we are looking for (abbreviation)
717 */
718class UFC_Corps_Rank implements UserFilterCondition
719{
720 private $rank;
721 public function __construct($rank)
722 {
723 $this->rank = $rank;
724 }
725
dcc63ed5 726 public function buildCondition(PlFilter &$uf)
4083b126 727 {
5d2e55c7 728 /** Tables shortcuts:
4083b126
RB		 729 * pcr for profile_corps_rank
730 */
731 $sub = $uf->addCorpsRankFilter();
732 $cond = $sub . '.abbreviation = ' . $rank;
733 return $cond;
734 }
735}
8363588b 736// }}}
4083b126 737
6a99c3ac
RB		 738// {{{ class UFC_Job_Company
739/** Filters users based on the company they belong to
740 * @param $type The field being searched (self::JOBID, self::JOBNAME or self::JOBACRONYM)
741 * @param $value The searched value
742 */
4e2f2ad2 743class UFC_Job_Company implements UserFilterCondition
6a99c3ac
RB		 744{
745 const JOBID = 'id';
746 const JOBNAME = 'name';
747 const JOBACRONYM = 'acronym';
748
749 private $type;
750 private $value;
751
752 public function __construct($type, $value)
753 {
754 $this->assertType($type);
755 $this->type = $type;
756 $this->value = $value;
757 }
758
759 private function assertType($type)
760 {
761 if ($type != self::JOBID && $type != self::JOBNAME && $type != self::JOBACRONYM) {
5d2e55c7 762 Platal::page()->killError("Type de recherche non valide.");
6a99c3ac
RB		 763 }
764 }
765
dcc63ed5 766 public function buildCondition(PlFilter &$uf)
6a99c3ac
RB		 767 {
768 $sub = $uf->addJobCompanyFilter();
769 $cond = $sub . '.' . $this->type . ' = ' . XDB::format('{?}', $this->value);
770 return $cond;
771 }
772}
773// }}}
774
775// {{{ class UFC_Job_Sectorization
776/** Filters users based on the ((sub)sub)sector they work in
777 * @param $sector The sector searched
778 * @param $subsector The subsector
779 * @param $subsubsector The subsubsector
780 */
4e2f2ad2 781class UFC_Job_Sectorization implements UserFilterCondition
6a99c3ac
RB		 782{
783
784 private $sector;
785 private $subsector;
786 private $subsubsector;
787
788 public function __construct($sector = null, $subsector = null, $subsubsector = null)
789 {
790 $this->sector = $sector;
791 $this->subsector = $subsector;
792 $this->subsubsector = $subsubsector;
793 }
794
dcc63ed5 795 public function buildCondition(PlFilter &$uf)
6a99c3ac
RB		 796 {
797 // No need to add the JobFilter, it will be done by addJobSectorizationFilter
798 $conds = array();
799 if ($this->sector !== null) {
800 $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SECTOR);
801 $conds[] = $sub . '.id = ' . XDB::format('{?}', $this->sector);
802 }
803 if ($this->subsector !== null) {
804 $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSECTOR);
805 $conds[] = $sub . '.id = ' . XDB::format('{?}', $this->subsector);
806 }
807 if ($this->subsubsector !== null) {
808 $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSUBSECTOR);
809 $conds[] = $sub . '.id = ' . XDB::format('{?}', $this->subsubsector);
810 }
811 return implode(' AND ', $conds);
812 }
813}
814// }}}
815
816// {{{ class UFC_Job_Description
817/** Filters users based on their job description
818 * @param $description The text being searched for
819 * @param $fields The fields to search for (user-defined, ((sub|)sub|)sector)
820 */
4e2f2ad2 821class UFC_Job_Description implements UserFilterCondition
6a99c3ac
RB		 822{
823
824 /** Meta-filters
825 * Built with binary OR on UserFilter::JOB_*
826 */
01cc5f9e 827 const ANY = 63;
6a99c3ac
RB		 828 const SECTORIZATION = 15;
829
830 private $description;
831 private $fields;
832
01cc5f9e 833 public function __construct($description, $fields)
6a99c3ac
RB		 834 {
835 $this->fields = $fields;
836 $this->description = $description;
837 }
838
dcc63ed5 839 public function buildCondition(PlFilter &$uf)
6a99c3ac
RB		 840 {
841 $conds = array();
842 if ($this->fields & UserFilter::JOB_USERDEFINED) {
843 $sub = $uf->addJobFilter();
844 $conds[] = $sub . '.description LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
845 }
01cc5f9e
RB		 846 if ($this->fields & UserFilter::JOB_CV) {
847 $uf->requireProfiles();
848 $conds[] = 'p.cv LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
849 }
6a99c3ac
RB		 850 if ($this->fields & UserFilter::JOB_SECTOR) {
851 $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SECTOR);
852 $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
853 }
854 if ($this->fields & UserFilter::JOB_SUBSECTOR) {
855 $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSECTOR);
856 $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
857 }
858 if ($this->fields & UserFilter::JOB_SUBSUBSECTOR) {
859 $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSUBSECTOR);
860 $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
861 $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_ALTERNATES);
862 $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
863 }
864 return implode(' OR ', $conds);
865 }
866}
867// }}}
868
0a2e9c74
RB		 869// {{{ class UFC_Networking
870/** Filters users based on network identity (IRC, ...)
871 * @param $type Type of network (-1 for any)
872 * @param $value Value to search
873 */
4e2f2ad2 874class UFC_Networking implements UserFilterCondition
0a2e9c74
RB		 875{
876 private $type;
877 private $value;
878
879 public function __construct($type, $value)
880 {
881 $this->type = $type;
882 $this->value = $value;
883 }
884
dcc63ed5 885 public function buildCondition(PlFilter &$uf)
0a2e9c74
RB		 886 {
887 $sub = $uf->addNetworkingFilter();
888 $conds = array();
889 $conds[] = $sub . '.address = ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->value);
890 if ($this->type != -1) {
891 $conds[] = $sub . '.network_type = ' . XDB::format('{?}', $this->type);
892 }
893 return implode(' AND ', $conds);
894 }
895}
896// }}}
897
6d62969e
RB		 898// {{{ class UFC_Phone
899/** Filters users based on their phone number
900 * @param $num_type Type of number (pro/user/home)
901 * @param $phone_type Type of phone (fixed/mobile/fax)
902 * @param $number Phone number
903 */
4e2f2ad2 904class UFC_Phone implements UserFilterCondition
6d62969e
RB		 905{
906 const NUM_PRO = 'pro';
907 const NUM_USER = 'user';
908 const NUM_HOME = 'address';
909 const NUM_ANY = 'any';
910
911 const PHONE_FIXED = 'fixed';
912 const PHONE_MOBILE = 'mobile';
913 const PHONE_FAX = 'fax';
914 const PHONE_ANY = 'any';
915
916 private $num_type;
917 private $phone_type;
918 private $number;
919
920 public function __construct($number, $num_type = self::NUM_ANY, $phone_type = self::PHONE_ANY)
921 {
9b8e5fb4 922 require_once('profil.func.inc.php');
6d62969e
RB		 923 $this->number = $number;
924 $this->num_type = $num_type;
925 $this->phone_type = format_phone_number($phone_type);
926 }
927
dcc63ed5 928 public function buildCondition(PlFilter &$uf)
6d62969e
RB		 929 {
930 $sub = $uf->addPhoneFilter();
931 $conds = array();
932 $conds[] = $sub . '.search_tel = ' . XDB::format('{?}', $this->number);
933 if ($this->num_type != self::NUM_ANY) {
934 $conds[] = $sub . '.link_type = ' . XDB::format('{?}', $this->num_type);
935 }
936 if ($this->phone_type != self::PHONE_ANY) {
937 $conds[] = $sub . '.tel_type = ' . XDB::format('{?}', $this->phone_type);
938 }
939 return implode(' AND ', $conds);
940 }
941}
942// }}}
943
ceb512d2
RB		 944// {{{ class UFC_Medal
945/** Filters users based on their medals
946 * @param $medal ID of the medal
947 * @param $grade Grade of the medal (null for 'any')
948 */
4e2f2ad2 949class UFC_Medal implements UserFilterCondition
ceb512d2
RB		 950{
951 private $medal;
952 private $grade;
953
954 public function __construct($medal, $grade = null)
955 {
956 $this->medal = $medal;
957 $this->grade = $grade;
958 }
959
dcc63ed5 960 public function buildCondition(PlFilter &$uf)
ceb512d2
RB		 961 {
962 $conds = array();
963 $sub = $uf->addMedalFilter();
964 $conds[] = $sub . '.mid = ' . XDB::format('{?}', $this->medal);
965 if ($this->grade != null) {
966 $conds[] = $sub . '.gid = ' . XDB::format('{?}', $this->grade);
967 }
968 return implode(' AND ', $conds);
969 }
970}
971// }}}
972
671b7073
RB		 973// {{{ class UFC_Mentor_Expertise
974/** Filters users by mentoring expertise
975 * @param $expertise Domain of expertise
976 */
4e2f2ad2 977class UFC_Mentor_Expertise implements UserFilterCondition
671b7073
RB		 978{
979 private $expertise;
980
981 public function __construct($expertise)
982 {
983 $this->expertise = $expertise;
984 }
985
dcc63ed5 986 public function buildCondition(PlFilter &$uf)
671b7073
RB		 987 {
988 $sub = $uf->addMentorFilter(UserFilter::MENTOR_EXPERTISE);
989 return $sub . '.expertise LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\'', $this->expertise);
990 }
991}
992// }}}
993
994// {{{ class UFC_Mentor_Country
995/** Filters users by mentoring country
996 * @param $country Two-letters code of country being searched
997 */
4e2f2ad2 998class UFC_Mentor_Country implements UserFilterCondition
671b7073
RB		 999{
1000 private $country;
1001
1002 public function __construct($country)
1003 {
1004 $this->country = $country;
1005 }
1006
dcc63ed5 1007 public function buildCondition(PlFilter &$uf)
671b7073
RB		 1008 {
1009 $sub = $uf->addMentorFilter(UserFilter::MENTOR_COUNTRY);
1010 return $sub . '.country = ' . XDB::format('{?}', $this->country);
1011 }
1012}
1013// }}}
1014
1015// {{{ class UFC_Mentor_Sectorization
1016/** Filters users based on mentoring (sub|)sector
1017 * @param $sector ID of sector
1018 * @param $subsector Subsector (null for any)
1019 */
4e2f2ad2 1020class UFC_Mentor_Sectorization implements UserFilterCondition
671b7073
RB		 1021{
1022 private $sector;
1023 private $subsector;
1024
1025 public function __construct($sector, $subsector = null)
1026 {
1027 $this->sector = $sector;
1028 $this->subsubsector = $subsector;
1029 }
1030
dcc63ed5 1031 public function buildCondition(PlFilter &$uf)
671b7073
RB		 1032 {
1033 $sub = $uf->addMentorFilter(UserFilter::MENTOR_SECTOR);
1034 $conds = array();
1035 $conds[] = $sub . '.sectorid = ' . XDB::format('{?}', $this->sector);
1036 if ($this->subsector != null) {
1037 $conds[] = $sub . '.subsectorid = ' . XDB::format('{?}', $this->subsector);
1038 }
1039 return implode(' AND ', $conds);
1040 }
1041}
1042// }}}
1043
8363588b 1044// {{{ class UFC_UserRelated
5d2e55c7 1045/** Filters users based on a relation toward a user
53eae167
RB		 1046 * @param $user User to which searched users are related
1047 */
4e7bf1e0 1048abstract class UFC_UserRelated implements UserFilterCondition
3f42a6ad 1049{
009b8ab7
FB		 1050 protected $user;
1051 public function __construct(PlUser &$user)
1052 {
1053 $this->user =& $user;
3f42a6ad 1054 }
4e7bf1e0 1055}
8363588b 1056// }}}
3f42a6ad 1057
8363588b 1058// {{{ class UFC_Contact
5d2e55c7 1059/** Filters users who belong to selected user's contacts
53eae167 1060 */
4e7bf1e0
FB		 1061class UFC_Contact extends UFC_UserRelated
1062{
dcc63ed5 1063 public function buildCondition(PlFilter &$uf)
3f42a6ad 1064 {
009b8ab7 1065 $sub = $uf->addContactFilter($this->user->id());
3f42a6ad
FB		 1066 return 'c' . $sub . '.contact IS NOT NULL';
1067 }
1068}
8363588b 1069// }}}
3f42a6ad 1070
8363588b 1071// {{{ class UFC_WatchRegistration
53eae167
RB		 1072/** Filters users being watched by selected user
1073 */
4e7bf1e0
FB		 1074class UFC_WatchRegistration extends UFC_UserRelated
1075{
dcc63ed5 1076 public function buildCondition(PlFilter &$uf)
4e7bf1e0 1077 {
009b8ab7 1078 if (!$this->user->watch('registration')) {
dcc63ed5 1079 return PlFilterCondition::COND_FALSE;
009b8ab7
FB		 1080 }
1081 $uids = $this->user->watchUsers();
1082 if (count($uids) == 0) {
dcc63ed5 1083 return PlFilterCondition::COND_FALSE;
009b8ab7 1084 } else {
07eb5b0e 1085 return '$UID IN ' . XDB::formatArray($uids);
009b8ab7 1086 }
4e7bf1e0
FB		 1087 }
1088}
8363588b 1089// }}}
4e7bf1e0 1090
8363588b 1091// {{{ class UFC_WatchPromo
53eae167
RB		 1092/** Filters users belonging to a promo watched by selected user
1093 * @param $user Selected user (the one watching promo)
1094 * @param $grade Formation the user is watching
1095 */
4e7bf1e0
FB		 1096class UFC_WatchPromo extends UFC_UserRelated
1097{
1098 private $grade;
009b8ab7 1099 public function __construct(PlUser &$user, $grade = UserFilter::GRADE_ING)
4e7bf1e0 1100 {
009b8ab7 1101 parent::__construct($user);
4e7bf1e0
FB		 1102 $this->grade = $grade;
1103 }
1104
dcc63ed5 1105 public function buildCondition(PlFilter &$uf)
4e7bf1e0 1106 {
009b8ab7
FB		 1107 $promos = $this->user->watchPromos();
1108 if (count($promos) == 0) {
dcc63ed5 1109 return PlFilterCondition::COND_FALSE;
009b8ab7
FB		 1110 } else {
1111 $sube = $uf->addEducationFilter(true, $this->grade);
1112 $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade);
07eb5b0e 1113 return $field . ' IN ' . XDB::formatArray($promos);
009b8ab7 1114 }
4e7bf1e0
FB		 1115 }
1116}
8363588b 1117// }}}
4e7bf1e0 1118
8363588b 1119// {{{ class UFC_WatchContact
53eae167
RB		 1120/** Filters users watched by selected user
1121 */
009b8ab7 1122class UFC_WatchContact extends UFC_Contact
4e7bf1e0 1123{
dcc63ed5 1124 public function buildCondition(PlFilter &$uf)
4e7bf1e0 1125 {
009b8ab7 1126 if (!$this->user->watchContacts()) {
dcc63ed5 1127 return PlFilterCondition::COND_FALSE;
009b8ab7
FB		 1128 }
1129 return parent::buildCondition($uf);
4e7bf1e0
FB		 1130 }
1131}
8363588b 1132// }}}
4e7bf1e0
FB		 1133
1134
d865c296
FB		 1135/******************
1136 * ORDERS
1137 ******************/
1138
8363588b 1139// {{{ class UserFilterOrder
2d83cac9
RB		 1140/** Base class for ordering results of a query.
1141 * Parameters for the ordering must be given to the constructor ($desc for a
1142 * descending order).
1143 * The getSortTokens function is used to get actual ordering part of the query.
1144 */
7ca75030 1145abstract class UserFilterOrder extends PlFilterOrder
d865c296 1146{
2d83cac9
RB		 1147 /** This function must return the tokens to use for ordering
1148 * @param &$uf The UserFilter whose results must be ordered
1149 * @return The name of the field to use for ordering results
1150 */
d865c296
FB		 1151 abstract protected function getSortTokens(UserFilter &$uf);
1152}
8363588b 1153// }}}
d865c296 1154
8363588b 1155// {{{ class UFO_Promo
5d2e55c7
RB		 1156/** Orders users by promotion
1157 * @param $grade Formation whose promotion users should be sorted by (restricts results to users of that formation)
53eae167
RB		 1158 * @param $desc Whether sort is descending
1159 */
d865c296
FB		 1160class UFO_Promo extends UserFilterOrder
1161{
1162 private $grade;
1163
1164 public function __construct($grade = null, $desc = false)
1165 {
009b8ab7 1166 parent::__construct($desc);
d865c296 1167 $this->grade = $grade;
d865c296
FB		 1168 }
1169
1170 protected function getSortTokens(UserFilter &$uf)
1171 {
1172 if (UserFilter::isGrade($this->grade)) {
1173 $sub = $uf->addEducationFilter($this->grade);
1174 return 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
1175 } else {
1176 $sub = $uf->addDisplayFilter();
1177 return 'pd' . $sub . '.promo';
1178 }
1179 }
1180}
8363588b 1181// }}}
d865c296 1182
8363588b 1183// {{{ class UFO_Name
53eae167 1184/** Sorts users by name
5d2e55c7
RB		 1185 * @param $type Type of name on which to sort (firstname...)
1186 * @param $variant Variant of that name to use (marital, ordinary...)
53eae167
RB		 1187 * @param $particle Set to true if particles should be included in the sorting order
1188 * @param $desc If sort order should be descending
1189 */
d865c296
FB		 1190class UFO_Name extends UserFilterOrder
1191{
1192 private $type;
1193 private $variant;
1194 private $particle;
1195
1196 public function __construct($type, $variant = null, $particle = false, $desc = false)
1197 {
009b8ab7 1198 parent::__construct($desc);
d865c296
FB		 1199 $this->type = $type;
1200 $this->variant = $variant;
1201 $this->particle = $particle;
d865c296
FB		 1202 }
1203
1204 protected function getSortTokens(UserFilter &$uf)
1205 {
1206 if (UserFilter::isDisplayName($this->type)) {
1207 $sub = $uf->addDisplayFilter();
1208 return 'pd' . $sub . '.' . $this->type;
1209 } else {
1210 $sub = $uf->addNameFilter($this->type, $this->variant);
1211 if ($this->particle) {
1212 return 'CONCAT(pn' . $sub . '.particle, \' \', pn' . $sub . '.name)';
1213 } else {
1214 return 'pn' . $sub . '.name';
1215 }
1216 }
1217 }
1218}
8363588b 1219// }}}
d865c296 1220
40585144
RB		 1221// {{{ class UFO_Score
1222class UFO_Score extends UserFilterOrder
1223{
1224 protected function getSortTokens(UserFilter &$uf)
1225 {
1226 $sub = $uf->addNameTokensFilter();
1227 return 'SUM(' . $sub . '.score)';
1228 }
1229}
1230// }}}
1231
8363588b 1232// {{{ class UFO_Registration
53eae167
RB		 1233/** Sorts users based on registration date
1234 */
38c6fe96
FB		 1235class UFO_Registration extends UserFilterOrder
1236{
009b8ab7 1237 protected function getSortTokens(UserFilter &$uf)
38c6fe96 1238 {
009b8ab7 1239 return 'a.registration_date';
38c6fe96 1240 }
009b8ab7 1241}
8363588b 1242// }}}
38c6fe96 1243
8363588b 1244// {{{ class UFO_Birthday
53eae167
RB		 1245/** Sorts users based on next birthday date
1246 */
009b8ab7
FB		 1247class UFO_Birthday extends UserFilterOrder
1248{
38c6fe96
FB		 1249 protected function getSortTokens(UserFilter &$uf)
1250 {
009b8ab7 1251 return 'p.next_birthday';
38c6fe96
FB		 1252 }
1253}
8363588b 1254// }}}
38c6fe96 1255
8363588b 1256// {{{ class UFO_ProfileUpdate
53eae167
RB		 1257/** Sorts users based on last profile update
1258 */
009b8ab7
FB		 1259class UFO_ProfileUpdate extends UserFilterOrder
1260{
1261 protected function getSortTokens(UserFilter &$uf)
1262 {
1263 return 'p.last_change';
1264 }
1265}
8363588b 1266// }}}
009b8ab7 1267
8363588b 1268// {{{ class UFO_Death
53eae167
RB		 1269/** Sorts users based on death date
1270 */
009b8ab7
FB		 1271class UFO_Death extends UserFilterOrder
1272{
1273 protected function getSortTokens(UserFilter &$uf)
1274 {
1275 return 'p.deathdate';
1276 }
1277}
8363588b 1278// }}}
009b8ab7
FB		 1279
1280
d865c296
FB		 1281/***********************************
1282 *********************************
1283 USER FILTER CLASS
1284 *********************************
1285 ***********************************/
1286
8363588b 1287// {{{ class UserFilter
2d83cac9
RB		 1288/** This class provides a convenient and centralized way of filtering users.
1289 *
1290 * Usage:
1291 * $uf = new UserFilter(new UFC_Blah($x, $y), new UFO_Coin($z, $t));
1292 *
1293 * Resulting UserFilter can be used to:
1294 * - get a list of User objects matching the filter
1295 * - get a list of UIDs matching the filter
1296 * - get the number of users matching the filter
1297 * - check whether a given User matches the filter
1298 * - filter a list of User objects depending on whether they match the filter
1299 *
1300 * Usage for UFC and UFO objects:
1301 * A UserFilter will call all private functions named XXXJoins.
1302 * These functions must return an array containing the list of join
1303 * required by the various UFC and UFO associated to the UserFilter.
1304 * Entries in those returned array are of the following form:
1305 * 'join_tablealias' => array('join_type', 'joined_table', 'join_criter')
1306 * which will be translated into :
1307 * join_type JOIN joined_table AS join_tablealias ON (join_criter)
1308 * in the final query.
1309 *
1310 * In the join_criter text, $ME is replaced with 'join_tablealias', $PID with
1311 * profile.pid, and $UID with auth_user_md5.user_id.
1312 *
1313 * For each kind of "JOIN" needed, a function named addXXXFilter() should be defined;
1314 * its parameter will be used to set various private vars of the UserFilter describing
1315 * the required joins ; such a function shall return the "join_tablealias" to use
1316 * when referring to the joined table.
1317 *
1318 * For example, if data from profile_job must be available to filter results,
1319 * the UFC object will call $uf-addJobFilter(), which will set the 'with_pj' var and
1320 * return 'pj', the short name to use when referring to profile_job; when building
1321 * the query, calling the jobJoins function will return an array containing a single
1322 * row:
1323 * 'pj' => array('left', 'profile_job', '$ME.pid = $UID');
1324 *
1325 * The 'register_optional' function can be used to generate unique table aliases when
1326 * the same table has to be joined several times with different aliases.
1327 */
9b8e5fb4 1328class UserFilter extends PlFilter
a087cc8d 1329{
9b8e5fb4 1330 protected $joinMethods = array();
7ca75030 1331
9b8e5fb4
RB		 1332 protected $joinMetas = array('$PID' => 'p.pid',
1333 '$UID' => 'a.uid',
1334 );
d865c296 1335
a087cc8d 1336 private $root;
24e08e33 1337 private $sort = array();
784745ce 1338 private $query = null;
24e08e33 1339 private $orderby = null;
784745ce 1340
aa21c568 1341 private $lastcount = null;
d865c296 1342
24e08e33 1343 public function __construct($cond = null, $sort = null)
5dd9d823 1344 {
06598c13 1345 if (empty($this->joinMethods)) {
d865c296
FB		 1346 $class = new ReflectionClass('UserFilter');
1347 foreach ($class->getMethods() as $method) {
1348 $name = $method->getName();
1349 if (substr($name, -5) == 'Joins' && $name != 'buildJoins') {
06598c13 1350 $this->joinMethods[] = $name;
d865c296
FB		 1351 }
1352 }
1353 }
5dd9d823 1354 if (!is_null($cond)) {
06598c13 1355 if ($cond instanceof PlFilterCondition) {
5dd9d823
FB		 1356 $this->setCondition($cond);
1357 }
1358 }
24e08e33
FB		 1359 if (!is_null($sort)) {
1360 if ($sort instanceof UserFilterOrder) {
1361 $this->addSort($sort);
d865c296
FB		 1362 } else if (is_array($sort)) {
1363 foreach ($sort as $s) {
1364 $this->addSort($s);
1365 }
24e08e33
FB		 1366 }
1367 }
5dd9d823
FB		 1368 }
1369
784745ce
FB		 1370 private function buildQuery()
1371 {
d865c296
FB		 1372 if (is_null($this->orderby)) {
1373 $orders = array();
1374 foreach ($this->sort as $sort) {
1375 $orders = array_merge($orders, $sort->buildSort($this));
1376 }
1377 if (count($orders) == 0) {
1378 $this->orderby = '';
1379 } else {
1380 $this->orderby = 'ORDER BY ' . implode(', ', $orders);
1381 }
1382 }
784745ce
FB		 1383 if (is_null($this->query)) {
1384 $where = $this->root->buildCondition($this);
f7ea7450
RB		 1385 if ($this->with_forced_sn) {
1386 $this->requireProfiles();
1387 $from = 'search_name AS sn';
1388 } else if ($this->with_accounts) {
b8dcf62d
RB		 1389 $from = 'accounts AS a';
1390 } else {
1391 $this->requireProfiles();
1392 $from = 'profiles AS p';
1393 }
f7ea7450 1394 $joins = $this->buildJoins();
b8dcf62d 1395 $this->query = 'FROM ' . $from . '
784745ce
FB		 1396 ' . $joins . '
1397 WHERE (' . $where . ')';
1398 }
1399 }
1400
7ca75030 1401 private function getUIDList($uids = null, PlLimit &$limit)
d865c296 1402 {
b8dcf62d 1403 $this->requireAccounts();
d865c296 1404 $this->buildQuery();
7ca75030 1405 $lim = $limit->getSql();
d865c296
FB		 1406 $cond = '';
1407 if (!is_null($uids)) {
07eb5b0e 1408 $cond = ' AND a.uid IN ' . XDB::formatArray($uids);
d865c296
FB		 1409 }
1410 $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS a.uid
1411 ' . $this->query . $cond . '
1412 GROUP BY a.uid
1413 ' . $this->orderby . '
7ca75030 1414 ' . $lim);
d865c296
FB		 1415 $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
1416 return $fetched;
1417 }
1418
043b104b
RB		 1419 private function getPIDList($pids = null, PlLimit &$limit)
1420 {
1421 $this->requireProfiles();
1422 $this->buildQuery();
1423 $lim = $limit->getSql();
1424 $cond = '';
1425 if (!is_null($pids)) {
1426 $cond = ' AND p.pid IN ' . XDB::formatArray($pids);
1427 }
1428 $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS p.pid
1429 ' . $this->query . $cond . '
1430 GROUP BY p.pid
1431 ' . $this->orderby . '
1432 ' . $lim);
1433 $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
1434 return $fetched;
1435 }
1436
a087cc8d
FB		 1437 /** Check that the user match the given rule.
1438 */
1439 public function checkUser(PlUser &$user)
1440 {
b8dcf62d 1441 $this->requireAccounts();
784745ce
FB		 1442 $this->buildQuery();
1443 $count = (int)XDB::fetchOneCell('SELECT COUNT(*)
1444 ' . $this->query . XDB::format(' AND a.uid = {?}', $user->id()));
1445 return $count == 1;
a087cc8d
FB		 1446 }
1447
043b104b
RB		 1448 /** Check that the profile match the given rule.
1449 */
1450 public function checkProfile(Profile &$profile)
1451 {
1452 $this->requireProfiles();
1453 $this->buildQuery();
1454 $count = (int)XDB::fetchOneCell('SELECT COUNT(*)
1455 ' . $this->query . XDB::format(' AND p.pid = {?}', $profile->id()));
1456 return $count == 1;
1457 }
1458
1459 /** Default filter is on users
a087cc8d 1460 */
7ca75030 1461 public function filter(array $users, PlLimit &$limit)
a087cc8d 1462 {
043b104b
RB		 1463 return $this->filterUsers($users, $limit);
1464 }
1465
1466 /** Filter a list of users to extract the users matching the rule.
1467 */
1468 public function filterUsers(array $users, PlLimit &$limit)
1469 {
b8dcf62d 1470 $this->requireAccounts();
4927ee54
FB		 1471 $this->buildQuery();
1472 $table = array();
1473 $uids = array();
1474 foreach ($users as $user) {
07eb5b0e
FB		 1475 if ($user instanceof PlUser) {
1476 $uid = $user->id();
1477 } else {
1478 $uid = $user;
1479 }
1480 $uids[] = $uid;
1481 $table[$uid] = $user;
4927ee54 1482 }
7ca75030 1483 $fetched = $this->getUIDList($uids, $limit);
a087cc8d 1484 $output = array();
4927ee54
FB		 1485 foreach ($fetched as $uid) {
1486 $output[] = $table[$uid];
a087cc8d
FB		 1487 }
1488 return $output;
1489 }
1490
043b104b
RB		 1491 /** Filter a list of profiles to extract the users matching the rule.
1492 */
1493 public function filterProfiles(array $profiles, PlLimit &$limit)
1494 {
1495 $this->requireProfiles();
1496 $this->buildQuery();
1497 $table = array();
1498 $pids = array();
1499 foreach ($profiles as $profile) {
1500 if ($profile instanceof Profile) {
1501 $pid = $profile->id();
1502 } else {
1503 $pid = $profile;
1504 }
1505 $pids[] = $pid;
1506 $table[$pid] = $profile;
1507 }
1508 $fetched = $this->getPIDList($pids, $limit);
1509 $output = array();
1510 foreach ($fetched as $pid) {
1511 $output[] = $table[$pid];
1512 }
1513 return $output;
1514 }
1515
7ca75030
RB		 1516 public function getUIDs(PlLimit &$limit)
1517 {
1518 return $this->getUIDList(null, $limit);
1519 }
1520
043b104b
RB		 1521 public function getPIDs(PlLimit &$limit)
1522 {
1523 return $this->getPIDList(null, $limit);
1524 }
1525
7ca75030 1526 public function getUsers(PlLimit &$limit)
4927ee54 1527 {
7ca75030 1528 return User::getBulkUsersWithUIDs($this->getUIDs($limit));
d865c296
FB		 1529 }
1530
043b104b
RB		 1531 public function getProfiles(PlLimit &$limit)
1532 {
1533 return Profile::getBulkProfilesWithPIDs($this->getPIDs($limit));
1534 }
1535
7ca75030 1536 public function get(PlLimit &$limit)
d865c296 1537 {
7ca75030 1538 return $this->getUsers($limit);
4927ee54
FB		 1539 }
1540
d865c296 1541 public function getTotalCount()
4927ee54 1542 {
38c6fe96 1543 if (is_null($this->lastcount)) {
aa21c568 1544 $this->buildQuery();
2b9ca54d 1545 if ($this->with_accounts) {
b8dcf62d
RB		 1546 $field = 'a.uid';
1547 } else {
1548 $field = 'p.pid';
1549 }
1550 return (int)XDB::fetchOneCell('SELECT COUNT(DISTINCT ' . $field . ')
7e735012 1551 ' . $this->query);
38c6fe96
FB		 1552 } else {
1553 return $this->lastcount;
1554 }
4927ee54
FB		 1555 }
1556
9b8e5fb4 1557 public function setCondition(PlFilterCondition &$cond)
a087cc8d
FB		 1558 {
1559 $this->root =& $cond;
784745ce 1560 $this->query = null;
a087cc8d
FB		 1561 }
1562
9b8e5fb4 1563 public function addSort(PlFilterOrder &$sort)
24e08e33 1564 {
d865c296
FB		 1565 $this->sort[] = $sort;
1566 $this->orderby = null;
24e08e33
FB		 1567 }
1568
a087cc8d
FB		 1569 static public function getLegacy($promo_min, $promo_max)
1570 {
a087cc8d 1571 if ($promo_min != 0) {
784745ce 1572 $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min));
5dd9d823
FB		 1573 } else {
1574 $min = new UFC_True();
a087cc8d 1575 }
a087cc8d 1576 if ($promo_max != 0) {
784745ce 1577 $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max));
a087cc8d 1578 } else {
5dd9d823 1579 $max = new UFC_True();
a087cc8d 1580 }
9b8e5fb4 1581 return new UserFilter(new PFC_And($min, $max));
a087cc8d 1582 }
784745ce 1583
07eb5b0e
FB		 1584 static public function sortByName()
1585 {
1586 return array(new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME));
1587 }
1588
1589 static public function sortByPromo()
1590 {
1591 return array(new UFO_Promo(), new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME));
1592 }
1593
aa21c568
FB		 1594 static private function getDBSuffix($string)
1595 {
1596 return preg_replace('/[^a-z0-9]/i', '', $string);
1597 }
1598
1599
2d83cac9
RB		 1600 /** Stores a new (and unique) table alias in the &$table table
1601 * @param &$table Array in which the table alias must be stored
1602 * @param $val Value which will then be used to build the join
1603 * @return Name of the newly created alias
1604 */
aa21c568
FB		 1605 private $option = 0;
1606 private function register_optional(array &$table, $val)
1607 {
1608 if (is_null($val)) {
1609 $sub = $this->option++;
1610 $index = null;
1611 } else {
1612 $sub = self::getDBSuffix($val);
1613 $index = $val;
1614 }
1615 $sub = '_' . $sub;
1616 $table[$sub] = $index;
1617 return $sub;
1618 }
784745ce 1619
b8dcf62d
RB		 1620 /** PROFILE VS ACCOUNT
1621 */
f7ea7450
RB		 1622 private $with_profiles = false;
1623 private $with_accounts = false;
1624 private $with_forced_sn = false;
b8dcf62d
RB		 1625 public function requireAccounts()
1626 {
1627 $this->with_accounts = true;
1628 }
1629
1630 public function requireProfiles()
1631 {
1632 $this->with_profiles = true;
1633 }
1634
f7ea7450
RB		 1635 /** Forces the "FROM" to use search_name instead of accounts or profiles */
1636 public function forceSearchName()
1637 {
1638 $this->with_forced_sn = true;
1639 }
1640
b8dcf62d
RB		 1641 protected function accountJoins()
1642 {
1643 $joins = array();
f7ea7450
RB		 1644 /** Quick search is much more efficient with sn first and PID second */
1645 if ($this->with_forced_sn) {
1646 $joins['p'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profiles', '$PID = sn.uid');
1647 if ($this->with_accounts) {
1648 $joins['ap'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'account_profiles', '$ME.pid = $PID');
1649 $joins['a'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'accounts', '$UID = ap.uid');
1650 }
1651 } else if ($this->with_profiles && $this->with_accounts) {
b8dcf62d
RB		 1652 $joins['ap'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'account_profiles', '$ME.uid = $UID AND FIND_IN_SET(\'owner\', ap.perms)');
1653 $joins['p'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profiles', '$PID = ap.pid');
1654 }
1655 return $joins;
1656 }
1657
d865c296
FB		 1658 /** DISPLAY
1659 */
38c6fe96 1660 const DISPLAY = 'display';
d865c296
FB		 1661 private $pd = false;
1662 public function addDisplayFilter()
1663 {
b8dcf62d 1664 $this->requireProfiles();
d865c296
FB		 1665 $this->pd = true;
1666 return '';
1667 }
1668
9b8e5fb4 1669 protected function displayJoins()
d865c296
FB		 1670 {
1671 if ($this->pd) {
7ca75030 1672 return array('pd' => new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_display', '$ME.pid = $PID'));
d865c296
FB		 1673 } else {
1674 return array();
1675 }
1676 }
1677
784745ce
FB		 1678 /** NAMES
1679 */
d865c296 1680 /* name tokens */
784745ce
FB		 1681 const LASTNAME = 'lastname';
1682 const FIRSTNAME = 'firstname';
1683 const NICKNAME = 'nickname';
1684 const PSEUDONYM = 'pseudonym';
1685 const NAME = 'name';
d865c296 1686 /* name variants */
784745ce
FB		 1687 const VN_MARITAL = 'marital';
1688 const VN_ORDINARY = 'ordinary';
1689 const VN_OTHER = 'other';
1690 const VN_INI = 'ini';
d865c296
FB		 1691 /* display names */
1692 const DN_FULL = 'directory_name';
1693 const DN_DISPLAY = 'yourself';
1694 const DN_YOURSELF = 'yourself';
1695 const DN_DIRECTORY = 'directory_name';
1696 const DN_PRIVATE = 'private_name';
1697 const DN_PUBLIC = 'public_name';
1698 const DN_SHORT = 'short_name';
1699 const DN_SORT = 'sort_name';
784745ce
FB		 1700
1701 static public $name_variants = array(
1702 self::LASTNAME => array(self::VN_MARITAL, self::VN_ORDINARY),
d865c296
FB		 1703 self::FIRSTNAME => array(self::VN_ORDINARY, self::VN_INI, self::VN_OTHER)
1704 );
784745ce
FB		 1705
1706 static public function assertName($name)
1707 {
1708 if (!Profile::getNameTypeId($name)) {
9b8e5fb4 1709 Platal::page()->kill('Invalid name type: ' . $name);
784745ce
FB		 1710 }
1711 }
1712
d865c296
FB		 1713 static public function isDisplayName($name)
1714 {
1715 return $name == self::DN_FULL || $name == self::DN_DISPLAY
1716 || $name == self::DN_YOURSELF || $name == self::DN_DIRECTORY
1717 || $name == self::DN_PRIVATE || $name == self::DN_PUBLIC
1718 || $name == self::DN_SHORT || $name == self::DN_SORT;
1719 }
1720
784745ce 1721 private $pn = array();
784745ce
FB		 1722 public function addNameFilter($type, $variant = null)
1723 {
b8dcf62d 1724 $this->requireProfiles();
784745ce
FB		 1725 if (!is_null($variant)) {
1726 $ft = $type . '_' . $variant;
1727 } else {
1728 $ft = $type;
1729 }
1730 $sub = '_' . $ft;
1731 self::assertName($ft);
1732
1733 if (!is_null($variant) && $variant == 'other') {
aa21c568 1734 $sub .= $this->option++;
784745ce
FB		 1735 }
1736 $this->pn[$sub] = Profile::getNameTypeId($ft);
1737 return $sub;
1738 }
1739
9b8e5fb4 1740 protected function nameJoins()
784745ce
FB		 1741 {
1742 $joins = array();
1743 foreach ($this->pn as $sub => $type) {
7ca75030 1744 $joins['pn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_name', '$ME.pid = $PID AND $ME.typeid = ' . $type);
784745ce
FB		 1745 }
1746 return $joins;
1747 }
1748
40585144
RB		 1749 /** NAMETOKENS
1750 */
1751 private $with_sn = false;
f7ea7450
RB		 1752 // Set $doingQuickSearch to true if you wish to optimize the query
1753 public function addNameTokensFilter($doingQuickSearch = false)
40585144
RB		 1754 {
1755 $this->requireProfiles();
f7ea7450 1756 $this->with_forced_sn = ($this->with_forced_sn || $doingQuickSearch);
40585144
RB		 1757 $this->with_sn = true;
1758 return 'sn';
1759 }
1760
1761 protected function nameTokensJoins()
1762 {
f7ea7450
RB		 1763 /* We don't return joins, since with_sn forces the SELECT to run on search_name first */
1764 if ($this->with_sn && !$this->with_forced_sn) {
1765 return array(
1766 'sn' => new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'search_name', '$ME.uid = $PID')
1767 );
1768 } else {
1769 return array();
40585144 1770 }
40585144
RB		 1771 }
1772
784745ce
FB		 1773 /** EDUCATION
1774 */
1775 const GRADE_ING = 'Ing.';
1776 const GRADE_PHD = 'PhD';
1777 const GRADE_MST = 'M%';
1778 static public function isGrade($grade)
1779 {
38c6fe96 1780 return $grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST;
784745ce
FB		 1781 }
1782
1783 static public function assertGrade($grade)
1784 {
1785 if (!self::isGrade($grade)) {
1786 Platal::page()->killError("Diplôme non valide");
1787 }
1788 }
1789
d865c296
FB		 1790 static public function promoYear($grade)
1791 {
1792 // XXX: Definition of promotion for phds and masters might change in near future.
1793 return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year';
1794 }
1795
784745ce
FB		 1796 private $pepe = array();
1797 private $with_pee = false;
784745ce
FB		 1798 public function addEducationFilter($x = false, $grade = null)
1799 {
b8dcf62d 1800 $this->requireProfiles();
784745ce 1801 if (!$x) {
aa21c568
FB		 1802 $index = $this->option;
1803 $sub = $this->option++;
784745ce
FB		 1804 } else {
1805 self::assertGrade($grade);
1806 $index = $grade;
1807 $sub = $grade[0];
1808 $this->with_pee = true;
1809 }
1810 $sub = '_' . $sub;
1811 $this->pepe[$index] = $sub;
1812 return $sub;
1813 }
1814
9b8e5fb4 1815 protected function educationJoins()
784745ce
FB		 1816 {
1817 $joins = array();
1818 if ($this->with_pee) {
7ca75030 1819 $joins['pee'] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_enum', 'pee.abbreviation = \'X\'');
784745ce
FB		 1820 }
1821 foreach ($this->pepe as $grade => $sub) {
1822 if ($this->isGrade($grade)) {
7ca75030
RB		 1823 $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.eduid = pee.id AND $ME.uid = $PID');
1824 $joins['pede' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.abbreviation LIKE ' .
784745ce
FB		 1825 XDB::format('{?}', $grade));
1826 } else {
7ca75030
RB		 1827 $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.uid = $PID');
1828 $joins['pee' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_enum', '$ME.id = pe' . $sub . '.eduid');
1829 $joins['pede' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid');
784745ce
FB		 1830 }
1831 }
1832 return $joins;
1833 }
4927ee54
FB		 1834
1835
1836 /** GROUPS
1837 */
1838 private $gpm = array();
4927ee54
FB		 1839 public function addGroupFilter($group = null)
1840 {
b8dcf62d 1841 $this->requireAccounts();
4927ee54
FB		 1842 if (!is_null($group)) {
1843 if (ctype_digit($group)) {
1844 $index = $sub = $group;
1845 } else {
1846 $index = $group;
aa21c568 1847 $sub = self::getDBSuffix($group);
4927ee54
FB		 1848 }
1849 } else {
aa21c568 1850 $sub = 'group_' . $this->option++;
4927ee54
FB		 1851 $index = null;
1852 }
1853 $sub = '_' . $sub;
1854 $this->gpm[$sub] = $index;
1855 return $sub;
1856 }
1857
9b8e5fb4 1858 protected function groupJoins()
4927ee54
FB		 1859 {
1860 $joins = array();
1861 foreach ($this->gpm as $sub => $key) {
1862 if (is_null($key)) {
7ca75030
RB		 1863 $joins['gpa' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'groupex.asso');
1864 $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
4927ee54 1865 } else if (ctype_digit($key)) {
7ca75030 1866 $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = ' . $key);
4927ee54 1867 } else {
7ca75030
RB		 1868 $joins['gpa' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'groupex.asso', XDB::format('$ME.diminutif = {?}', $key));
1869 $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
4927ee54
FB		 1870 }
1871 }
1872 return $joins;
0fb3713c
RB		 1873 }
1874
1875 /** BINETS
1876 */
1877
1878 private $with_binets = false;
1879 public function addBinetsFilter()
1880 {
1881 $this->requireProfiles();
1882 $this->with_binets = true;
1883 return 'bi';
1884 }
1885
1886 protected function binetsJoins()
1887 {
1888 $joins = array();
1889 if ($this->with_binets) {
1890 $joins['bi'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'binets_ins', '$ME.uid = $PID');
1891 }
1892 return $joins;
4927ee54 1893 }
aa21c568
FB		 1894
1895 /** EMAILS
1896 */
1897 private $e = array();
1898 public function addEmailRedirectFilter($email = null)
1899 {
b8dcf62d 1900 $this->requireAccounts();
aa21c568
FB		 1901 return $this->register_optional($this->e, $email);
1902 }
1903
1904 private $ve = array();
1905 public function addVirtualEmailFilter($email = null)
1906 {
21401768 1907 $this->addAliasFilter(self::ALIAS_FORLIFE);
aa21c568
FB		 1908 return $this->register_optional($this->ve, $email);
1909 }
1910
21401768
FB		 1911 const ALIAS_BEST = 'bestalias';
1912 const ALIAS_FORLIFE = 'forlife';
aa21c568
FB		 1913 private $al = array();
1914 public function addAliasFilter($alias = null)
1915 {
b8dcf62d 1916 $this->requireAccounts();
aa21c568
FB		 1917 return $this->register_optional($this->al, $alias);
1918 }
1919
9b8e5fb4 1920 protected function emailJoins()
aa21c568
FB		 1921 {
1922 global $globals;
1923 $joins = array();
1924 foreach ($this->e as $sub=>$key) {
1925 if (is_null($key)) {
7ca75030 1926 $joins['e' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'emails', '$ME.uid = $UID AND $ME.flags != \'filter\'');
aa21c568 1927 } else {
7ca75030 1928 $joins['e' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'emails', XDB::format('$ME.uid = $UID AND $ME.flags != \'filter\' AND $ME.email = {?}', $key));
aa21c568
FB		 1929 }
1930 }
21401768 1931 foreach ($this->al as $sub=>$key) {
aa21c568 1932 if (is_null($key)) {
7ca75030 1933 $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\')');
21401768 1934 } else if ($key == self::ALIAS_BEST) {
7ca75030 1935 $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND FIND_IN_SET(\'bestalias\', $ME.flags)');
21401768 1936 } else if ($key == self::ALIAS_FORLIFE) {
7ca75030 1937 $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type = \'a_vie\'');
aa21c568 1938 } else {
7ca75030 1939 $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', XDB::format('$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND $ME.alias = {?}', $key));
aa21c568 1940 }
aa21c568 1941 }
21401768 1942 foreach ($this->ve as $sub=>$key) {
aa21c568 1943 if (is_null($key)) {
7ca75030 1944 $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', '$ME.type = \'user\'');
aa21c568 1945 } else {
7ca75030 1946 $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', XDB::format('$ME.type = \'user\' AND $ME.alias = {?}', $key));
aa21c568 1947 }
7ca75030 1948 $joins['vr' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual_redirect', XDB::format('$ME.vid = v' . $sub . '.vid
21401768
FB		 1949 AND ($ME.redirect IN (CONCAT(al_forlife.alias, \'@\', {?}),
1950 CONCAT(al_forlife.alias, \'@\', {?}),
1951 a.email))',
1952 $globals->mail->domain, $globals->mail->domain2));
aa21c568
FB		 1953 }
1954 return $joins;
1955 }
3f42a6ad
FB		 1956
1957
c4b24511
RB		 1958 /** ADDRESSES
1959 */
036d1637 1960 private $with_pa = false;
c4b24511
RB		 1961 public function addAddressFilter()
1962 {
b8dcf62d 1963 $this->requireProfiles();
036d1637
RB		 1964 $this->with_pa = true;
1965 return 'pa';
c4b24511
RB		 1966 }
1967
2b9ca54d
RB		 1968 private $with_pac = false;
1969 public function addAddressCountryFilter()
1970 {
1971 $this->requireProfiles();
1972 $this->addAddressFilter();
1973 $this->with_pac = true;
1974 return 'gc';
1975 }
1976
1977 private $with_pal = true;
1978 public function addAddressLocalityFilter()
1979 {
1980 $this->requireProfiles();
1981 $this->addAddressFilter();
1982 $this->with_pal = true;
1983 return 'gl';
1984 }
1985
9b8e5fb4 1986 protected function addressJoins()
c4b24511
RB		 1987 {
1988 $joins = array();
036d1637 1989 if ($this->with_pa) {
7ca75030 1990 $joins['pa'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_address', '$ME.pid = $PID');
c4b24511 1991 }
2b9ca54d
RB		 1992 if ($this->with_pac) {
1993 $joins['gc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'geoloc_countries', '$ME.iso_3166_1_a2 = pa.countryID');
1994 }
1995 if ($this->with_pal) {
1996 $joins['gl'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'geoloc_localities', '$ME.id = pa.localityID');
1997 }
c4b24511
RB		 1998 return $joins;
1999 }
2000
2001
4083b126
RB		 2002 /** CORPS
2003 */
2004
2005 private $pc = false;
2006 private $pce = array();
2007 private $pcr = false;
2008 public function addCorpsFilter($type)
2009 {
b8dcf62d 2010 $this->requireProfiles();
4083b126
RB		 2011 $this->pc = true;
2012 if ($type == UFC_Corps::CURRENT) {
2013 $pce['pcec'] = 'current_corpsid';
2014 return 'pcec';
2015 } else if ($type == UFC_Corps::ORIGIN) {
2016 $pce['pceo'] = 'original_corpsid';
2017 return 'pceo';
2018 }
2019 }
2020
2021 public function addCorpsRankFilter()
2022 {
b8dcf62d 2023 $this->requireProfiles();
4083b126
RB		 2024 $this->pc = true;
2025 $this->pcr = true;
2026 return 'pcr';
2027 }
2028
9b8e5fb4 2029 protected function corpsJoins()
4083b126
RB		 2030 {
2031 $joins = array();
2032 if ($this->pc) {
7ca75030 2033 $joins['pc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps', '$ME.uid = $UID');
4083b126
RB		 2034 }
2035 if ($this->pcr) {
7ca75030 2036 $joins['pcr'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_rank_enum', '$ME.id = pc.rankid');
4083b126
RB		 2037 }
2038 foreach($this->pce as $sub => $field) {
7ca75030 2039 $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_enum', '$ME.id = pc.' . $field);
4083b126
RB		 2040 }
2041 return $joins;
2042 }
2043
6a99c3ac
RB		 2044 /** JOBS
2045 */
2046
2047 const JOB_SECTOR = 1;
2048 const JOB_SUBSECTOR = 2;
2049 const JOB_SUBSUBSECTOR = 4;
2050 const JOB_ALTERNATES = 8;
2051 const JOB_USERDEFINED = 16;
01cc5f9e 2052 const JOB_CV = 32;
6a99c3ac
RB		 2053
2054 /** Joins :
2055 * pj => profile_job
2056 * pje => profile_job_enum
2057 * pjse => profile_job_sector_enum
2058 * pjsse => profile_job_subsector_enum
2059 * pjssse => profile_job_subsubsector_enum
2060 * pja => profile_job_alternates
2061 */
2062 private $with_pj = false;
2063 private $with_pje = false;
2064 private $with_pjse = false;
2065 private $with_pjsse = false;
2066 private $with_pjssse = false;
2067 private $with_pja = false;
2068
2069 public function addJobFilter()
2070 {
b8dcf62d 2071 $this->requireProfiles();
6a99c3ac
RB		 2072 $this->with_pj = true;
2073 return 'pj';
2074 }
2075
2076 public function addJobCompanyFilter()
2077 {
2078 $this->addJobFilter();
2079 $this->with_pje = true;
2080 return 'pje';
2081 }
2082
2083 public function addJobSectorizationFilter($type)
2084 {
2085 $this->addJobFilter();
2086 if ($type == self::JOB_SECTOR) {
2087 $this->with_pjse = true;
2088 return 'pjse';
2089 } else if ($type == self::JOB_SUBSECTOR) {
2090 $this->with_pjsse = true;
2091 return 'pjsse';
2092 } else if ($type == self::JOB_SUBSUBSECTOR) {
2093 $this->with_pjssse = true;
2094 return 'pjssse';
2095 } else if ($type == self::JOB_ALTERNATES) {
2096 $this->with_pja = true;
2097 return 'pja';
2098 }
2099 }
2100
9b8e5fb4 2101 protected function jobJoins()
6a99c3ac
RB		 2102 {
2103 $joins = array();
2104 if ($this->with_pj) {
7ca75030 2105 $joins['pj'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job', '$ME.uid = $UID');
6a99c3ac
RB		 2106 }
2107 if ($this->with_pje) {
7ca75030 2108 $joins['pje'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_enum', '$ME.id = pj.jobid');
6a99c3ac
RB		 2109 }
2110 if ($this->with_pjse) {
7ca75030 2111 $joins['pjse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_sector_enum', '$ME.id = pj.sectorid');
6a99c3ac
RB		 2112 }
2113 if ($this->with_pjsse) {
7ca75030 2114 $joins['pjsse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_subsector_enum', '$ME.id = pj.subsectorid');
6a99c3ac
RB		 2115 }
2116 if ($this->with_pjssse) {
7ca75030 2117 $joins['pjssse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_subsubsector_enum', '$ME.id = pj.subsubsectorid');
6a99c3ac
RB		 2118 }
2119 if ($this->with_pja) {
7ca75030 2120 $joins['pja'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_alternates', '$ME.subsubsectorid = pj.subsubsectorid');
6a99c3ac
RB		 2121 }
2122 return $joins;
2123 }
2124
0a2e9c74
RB		 2125 /** NETWORKING
2126 */
2127
2128 private $with_pnw = false;
2129 public function addNetworkingFilter()
2130 {
b8dcf62d 2131 $this->requireAccounts();
0a2e9c74
RB		 2132 $this->with_pnw = true;
2133 return 'pnw';
2134 }
2135
9b8e5fb4 2136 protected function networkingJoins()
0a2e9c74
RB		 2137 {
2138 $joins = array();
2139 if ($this->with_pnw) {
7ca75030 2140 $joins['pnw'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_networking', '$ME.uid = $UID');
0a2e9c74
RB		 2141 }
2142 return $joins;
2143 }
2144
6d62969e
RB		 2145 /** PHONE
2146 */
2147
2d83cac9 2148 private $with_ptel = false;
6d62969e
RB		 2149
2150 public function addPhoneFilter()
2151 {
b8dcf62d 2152 $this->requireAccounts();
2d83cac9 2153 $this->with_ptel = true;
6d62969e
RB		 2154 return 'ptel';
2155 }
2156
9b8e5fb4 2157 protected function phoneJoins()
6d62969e
RB		 2158 {
2159 $joins = array();
2d83cac9 2160 if ($this->with_ptel) {
9b8e5fb4 2161 $joins['ptel'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_phones', '$ME.uid = $UID');
6d62969e
RB		 2162 }
2163 return $joins;
2164 }
2165
ceb512d2
RB		 2166 /** MEDALS
2167 */
2168
2d83cac9 2169 private $with_pmed = false;
ceb512d2
RB		 2170 public function addMedalFilter()
2171 {
b8dcf62d 2172 $this->requireProfiles();
2d83cac9 2173 $this->with_pmed = true;
ceb512d2
RB		 2174 return 'pmed';
2175 }
2176
9b8e5fb4 2177 protected function medalJoins()
ceb512d2
RB		 2178 {
2179 $joins = array();
2d83cac9 2180 if ($this->with_pmed) {
7ca75030 2181 $joins['pmed'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_medals_sub', '$ME.uid = $UID');
ceb512d2
RB		 2182 }
2183 return $joins;
2184 }
2185
671b7073
RB		 2186 /** MENTORING
2187 */
2188
2189 private $pms = array();
2190 const MENTOR_EXPERTISE = 1;
2191 const MENTOR_COUNTRY = 2;
2192 const MENTOR_SECTOR = 3;
2193
2194 public function addMentorFilter($type)
2195 {
b8dcf62d 2196 $this->requireAccounts();
671b7073
RB		 2197 switch($type) {
2198 case MENTOR_EXPERTISE:
2199 $pms['pme'] = 'profile_mentor';
2200 return 'pme';
2201 case MENTOR_COUNTRY:
2202 $pms['pmc'] = 'profile_mentor_country';
2203 return 'pmc';
2204 case MENTOR_SECTOR:
2205 $pms['pms'] = 'profile_mentor_sector';
2206 return 'pms';
2207 default:
5d2e55c7 2208 Platal::page()->killError("Undefined mentor filter.");
671b7073
RB		 2209 }
2210 }
2211
9b8e5fb4 2212 protected function mentorJoins()
671b7073
RB		 2213 {
2214 $joins = array();
2215 foreach ($this->pms as $sub => $tab) {
7ca75030 2216 $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, $tab, '$ME.uid = $UID');
671b7073
RB		 2217 }
2218 return $joins;
2219 }
2220
3f42a6ad
FB		 2221 /** CONTACTS
2222 */
2223 private $cts = array();
2224 public function addContactFilter($uid = null)
2225 {
b8dcf62d 2226 $this->requireAccounts();
3f42a6ad
FB		 2227 return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid);
2228 }
2229
9b8e5fb4 2230 protected function contactJoins()
3f42a6ad
FB		 2231 {
2232 $joins = array();
2233 foreach ($this->cts as $sub=>$key) {
2234 if (is_null($key)) {
7ca75030 2235 $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', '$ME.contact = $UID');
3f42a6ad 2236 } else {
7ca75030 2237 $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', XDB::format('$ME.uid = {?} AND $ME.contact = $UID', substr($key, 5)));
3f42a6ad
FB		 2238 }
2239 }
2240 return $joins;
2241 }
4e7bf1e0
FB		 2242
2243
2244 /** CARNET
2245 */
2246 private $wn = array();
2247 public function addWatchRegistrationFilter($uid = null)
2248 {
b8dcf62d 2249 $this->requireAccounts();
4e7bf1e0
FB		 2250 return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid);
2251 }
2252
2253 private $wp = array();
2254 public function addWatchPromoFilter($uid = null)
2255 {
b8dcf62d 2256 $this->requireAccounts();
4e7bf1e0
FB		 2257 return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid);
2258 }
2259
2260 private $w = array();
2261 public function addWatchFilter($uid = null)
2262 {
b8dcf62d 2263 $this->requireAccounts();
4e7bf1e0
FB		 2264 return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid);
2265 }
2266
9b8e5fb4 2267 protected function watchJoins()
4e7bf1e0
FB		 2268 {
2269 $joins = array();
2270 foreach ($this->w as $sub=>$key) {
2271 if (is_null($key)) {
7ca75030 2272 $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch');
4e7bf1e0 2273 } else {
7ca75030 2274 $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch', XDB::format('$ME.uid = {?}', substr($key, 5)));
4e7bf1e0
FB		 2275 }
2276 }
2277 foreach ($this->wn as $sub=>$key) {
2278 if (is_null($key)) {
7ca75030 2279 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', '$ME.ni_id = $UID');
4e7bf1e0 2280 } else {
7ca75030 2281 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5)));
4e7bf1e0
FB		 2282 }
2283 }
2284 foreach ($this->wn as $sub=>$key) {
2285 if (is_null($key)) {
7ca75030 2286 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', '$ME.ni_id = $UID');
4e7bf1e0 2287 } else {
7ca75030 2288 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5)));
4e7bf1e0
FB		 2289 }
2290 }
2291 foreach ($this->wp as $sub=>$key) {
2292 if (is_null($key)) {
7ca75030 2293 $joins['wp' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_promo');
4e7bf1e0 2294 } else {
7ca75030 2295 $joins['wp' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_promo', XDB::format('$ME.uid = {?}', substr($key, 5)));
4e7bf1e0
FB		 2296 }
2297 }
2298 return $joins;
2299 }
a087cc8d 2300}
8363588b 2301// }}}
3f42a6ad 2302
a087cc8d
FB		 2303// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
2304?>
plat/al