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