classes/userfilter.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2010 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
  22
  23 /******************
  24  * CONDITIONS
  25  ******************/
  26
  27 // {{{ interface UserFilterCondition
  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  */
  37 interface UserFilterCondition extends PlFilterCondition
  38 {
  39 }
  40 // }}}
  41
  42 // {{{ class UFC_HasProfile
  43 /** Filters users who have a profile
  44  */
  45 class UFC_HasProfile implements UserFilterCondition
  46 {
  47     public function buildCondition(PlFilter &$uf)
  48     {
  49         $uf->requireProfiles();
  50         return '$PID IS NOT NULL';
  51     }
  52 }
  53 // }}}
  54
  55 // {{{ class UFC_AccountType
  56 /** Filters users who have one of the given account types
  57  */
  58 class UFC_AccountType implements UserFilterCondition
  59 {
  60     private $types;
  61
  62     public function __construct()
  63     {
  64         $this->types = pl_flatten(func_get_args());
  65     }
  66
  67     public function buildCondition(PlFilter &$uf)
  68     {
  69         $uf->requireAccounts();
  70         return XDB::format('a.type IN {?}', $this->types);
  71     }
  72 }
  73 // }}}
  74
  75 // {{{ class UFC_AccountPerm
  76 /** Filters users who have one of the given permissions
  77  */
  78 class UFC_AccountPerm implements UserFilterCondition
  79 {
  80     private $perms;
  81
  82     public function __construct()
  83     {
  84         $this->perms = pl_flatten(func_get_args());
  85     }
  86
  87     public function buildCondition(PlFilter &$uf)
  88     {
  89         $uf->requirePerms();
  90         $conds = array();
  91         foreach ($this->perms as $perm) {
  92             $conds[] = XDB::format('FIND_IN_SET({?}, IF(a.user_perms IS NULL, at.perms,
  93                                                         CONCAT(at.perms, \',\', a.user_perms)))',
  94                                    $perm);
  95         }
  96         if (empty($conds)) {
  97             return self::COND_TRUE;
  98         } else {
  99             return implode(' OR ', $conds);
 100         }
 101     }
 102 }
 103 // }}}
 104
 105 // {{{ class UFC_Hruid
 106 /** Filters users based on their hruid
 107  * @param $val Either an hruid, or a list of those
 108  */
 109 class UFC_Hruid implements UserFilterCondition
 110 {
 111     private $hruids;
 112
 113     public function __construct()
 114     {
 115         $this->hruids = pl_flatten(func_get_args());
 116     }
 117
 118     public function buildCondition(PlFilter &$uf)
 119     {
 120         $uf->requireAccounts();
 121         return XDB::format('a.hruid IN {?}', $this->hruids);
 122     }
 123 }
 124 // }}}
 125
 126 // {{{ class UFC_Hrpid
 127 /** Filters users based on the hrpid of their profiles
 128  * @param $val Either an hrpid, or a list of those
 129  */
 130 class UFC_Hrpid implements UserFilterCondition
 131 {
 132     private $hrpids;
 133
 134     public function __construct()
 135     {
 136         $this->hrpids = pl_flatten(func_get_args());
 137     }
 138
 139     public function buildCondition(PlFilter &$uf)
 140     {
 141         $uf->requireProfiles();
 142         return XDB::format('p.hrpid IN {?}', $this->hrpids);
 143     }
 144 }
 145 // }}}
 146
 147 // {{{ class UFC_Ip
 148 /** Filters users based on one of their last IPs
 149  * @param $ip IP from which connection are checked
 150  */
 151 class UFC_Ip implements UserFilterCondition
 152 {
 153     private $ip;
 154
 155     public function __construct($ip)
 156     {
 157         $this->ip = $ip;
 158     }
 159
 160     public function buildCondition(PlFilter &$uf)
 161     {
 162         $sub = $uf->addLoggerFilter();
 163         $ip = ip_to_uint($this->ip);
 164         return XDB::format($sub . '.ip = {?} OR ' . $sub . '.forward_ip = {?}', $ip, $ip);
 165     }
 166 }
 167 // }}}
 168
 169 // {{{ class UFC_Comment
 170 class UFC_Comment implements UserFilterCondition
 171 {
 172     private $text;
 173
 174     public function __construct($text)
 175     {
 176         $this->text = $text;
 177     }
 178
 179     public function buildCondition(PlFilter &$uf)
 180     {
 181         $uf->requireProfiles();
 182         return $uf->getVisibilityCondition('p.freetext_pub') . ' AND p.freetext ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->text);
 183     }
 184 }
 185 // }}}
 186
 187 // {{{ class UFC_Promo
 188 /** Filters users based on promotion
 189  * @param $comparison Comparison operator (>, =, ...)
 190  * @param $grade Formation on which to restrict, UserFilter::DISPLAY for "any formation"
 191  * @param $promo Promotion on which the filter is based
 192  */
 193 class UFC_Promo implements UserFilterCondition
 194 {
 195
 196     private $grade;
 197     private $promo;
 198     private $comparison;
 199
 200     public function __construct($comparison, $grade, $promo)
 201     {
 202         $this->grade = $grade;
 203         $this->comparison = $comparison;
 204         $this->promo = $promo;
 205         if ($this->grade != UserFilter::DISPLAY) {
 206             UserFilter::assertGrade($this->grade);
 207         }
 208         if ($this->grade == UserFilter::DISPLAY && $this->comparison != '=') {
 209             // XXX: we might try to guess the grade from the first char of the promo and forbid only '<= 2004', but allow '<= X2004'
 210             Platal::page()->killError("Il n'est pas possible d'appliquer la comparaison '" . $this->comparison . "' aux promotions sans spécifier de formation (X/M/D)");
 211         }
 212     }
 213
 214     public function buildCondition(PlFilter &$uf)
 215     {
 216         if ($this->grade == UserFilter::DISPLAY) {
 217             $sub = $uf->addDisplayFilter();
 218             return XDB::format('pd' . $sub . '.promo ' . $this->comparison . ' {?}', $this->promo);
 219         } else {
 220             $sub = $uf->addEducationFilter(true, $this->grade);
 221             $field = 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
 222             return $field . ' IS NOT NULL AND ' . $field . ' ' . $this->comparison . ' ' . XDB::format('{?}', $this->promo);
 223         }
 224     }
 225 }
 226 // }}}
 227
 228 // {{{ class UFC_SchoolId
 229 /** Filters users based on their shoold identifier
 230  * @param type Parameter type (Xorg, AX, School)
 231  * @param value School id value
 232  */
 233 class UFC_SchoolId implements UserFilterCondition
 234 {
 235     const AX     = 'ax';
 236     const Xorg   = 'xorg';
 237     const School = 'school';
 238
 239     private $type;
 240     private $id;
 241
 242     static public function assertType($type)
 243     {
 244         if ($type != self::AX && $type != self::Xorg && $type != self::School) {
 245             Platal::page()->killError("Type de matricule invalide: $type");
 246         }
 247     }
 248
 249     public function __construct($type, $id)
 250     {
 251         $this->type = $type;
 252         $this->id   = $id;
 253         self::assertType($type);
 254     }
 255
 256     public function buildCondition(PlFilter &$uf)
 257     {
 258         $uf->requireProfiles();
 259         $id = $this->id;
 260         $type = $this->type;
 261         if ($type == self::School) {
 262             $type = self::Xorg;
 263             $id   = Profile::getXorgId($id);
 264         }
 265         return XDB::format('p.' . $type . '_id = {?}', $id);
 266     }
 267 }
 268 // }}}
 269
 270 // {{{ class UFC_EducationSchool
 271 /** Filters users by formation
 272  * @param $val The formation to search (either ID or array of IDs)
 273  */
 274 class UFC_EducationSchool implements UserFilterCondition
 275 {
 276     private $val;
 277
 278     public function __construct()
 279     {
 280         $this->val = pl_flatten(func_get_args());
 281     }
 282
 283     public function buildCondition(PlFilter &$uf)
 284     {
 285         $sub = $uf->addEducationFilter();
 286         return XDB::format('pe' . $sub . '.eduid IN {?}', $this->val);
 287     }
 288 }
 289 // }}}
 290
 291 // {{{ class UFC_EducationDegree
 292 class UFC_EducationDegree implements UserFilterCondition
 293 {
 294     private $diploma;
 295
 296     public function __construct()
 297     {
 298         $this->diploma = pl_flatten(func_get_args());
 299     }
 300
 301     public function buildCondition(PlFilter &$uf)
 302     {
 303         $sub = $uf->addEducationFilter();
 304         return XDB::format('pe' . $sub . '.degreeid IN {?}', $this->diploma);
 305     }
 306 }
 307 // }}}
 308
 309 // {{{ class UFC_EducationField
 310 class UFC_EducationField implements UserFilterCondition
 311 {
 312     private $val;
 313
 314     public function __construct()
 315     {
 316         $this->val = pl_flatten(func_get_args());
 317     }
 318
 319     public function buildCondition(PlFilter &$uf)
 320     {
 321         $sub = $uf->addEducationFilter();
 322         return XDB::format('pe' . $sub . '.fieldid IN {?}', $this->val);
 323     }
 324 }
 325 // }}}
 326
 327 // {{{ class UFC_Name
 328 /** Filters users based on name
 329  * @param $type Type of name field on which filtering is done (firstname, lastname...)
 330  * @param $text Text on which to filter
 331  * @param $mode Flag indicating search type (prefix, suffix, with particule...)
 332  */
 333 class UFC_Name implements UserFilterCondition
 334 {
 335     const EXACT    = XDB::WILDCARD_EXACT;    // 0x000
 336     const PREFIX   = XDB::WILDCARD_PREFIX;   // 0x001
 337     const SUFFIX   = XDB::WILDCARD_SUFFIX;   // 0x002
 338     const CONTAINS = XDB::WILDCARD_CONTAINS; // 0x003
 339     const PARTICLE = 0x004;
 340     const VARIANTS = 0x008;
 341
 342     private $type;
 343     private $text;
 344     private $mode;
 345
 346     public function __construct($type, $text, $mode)
 347     {
 348         $this->type = $type;
 349         $this->text = $text;
 350         $this->mode = $mode;
 351     }
 352
 353     private function buildNameQuery($type, $variant, $where, UserFilter &$uf)
 354     {
 355         $sub = $uf->addNameFilter($type, $variant);
 356         return str_replace('$ME', 'pn' . $sub, $where);
 357     }
 358
 359     public function buildCondition(PlFilter &$uf)
 360     {
 361         $left = '$ME.name';
 362         if (($this->mode & self::PARTICLE) == self::PARTICLE) {
 363             $left = 'CONCAT($ME.particle, \' \', $ME.name)';
 364         }
 365         $right = XDB::formatWildcards($this->mode & self::CONTAINS, $this->text);
 366
 367         $cond = $left . $right;
 368         $conds = array($this->buildNameQuery($this->type, null, $cond, $uf));
 369         if (($this->mode & self::VARIANTS) != 0 && isset(Profile::$name_variants[$this->type])) {
 370             foreach (Profile::$name_variants[$this->type] as $var) {
 371                 $conds[] = $this->buildNameQuery($this->type, $var, $cond, $uf);
 372             }
 373         }
 374         return implode(' OR ', $conds);
 375     }
 376 }
 377 // }}}
 378
 379 // {{{ class UFC_NameTokens
 380 /** Selects users based on tokens in their name (for quicksearch)
 381  * @param $tokens An array of tokens to search
 382  * @param $flags Flags the tokens must have (e.g 'public' for public search)
 383  * @param $soundex (bool) Whether those tokens are fulltext or soundex
 384  */
 385 class UFC_NameTokens implements UserFilterCondition
 386 {
 387     /* Flags */
 388     const FLAG_PUBLIC = 'public';
 389
 390     private $tokens;
 391     private $flags;
 392     private $soundex;
 393     private $exact;
 394
 395     public function __construct($tokens, $flags = array(), $soundex = false, $exact = false)
 396     {
 397         if (is_array($tokens)) {
 398             $this->tokens = $tokens;
 399         } else {
 400             $this->tokens = array($tokens);
 401         }
 402         if (is_array($flags)) {
 403             $this->flags = $flags;
 404         } else {
 405             $this->flags = array($flags);
 406         }
 407         $this->soundex = $soundex;
 408         $this->exact = $exact;
 409     }
 410
 411     public function buildCondition(PlFilter &$uf)
 412     {
 413         $conds = array();
 414         foreach ($this->tokens as $i => $token) {
 415             $sub = $uf->addNameTokensFilter($token);
 416             if ($this->soundex) {
 417                 $c = XDB::format($sub . '.soundex = {?}', soundex_fr($token));
 418             } else if ($this->exact) {
 419                 $c = XDB::format($sub . '.token = {?}', $token);
 420             } else {
 421                 $c = $sub . '.token ' . XDB::formatWildcards(XDB::WILDCARD_PREFIX, $token);
 422             }
 423             if ($this->flags != null) {
 424                 $c .= XDB::format(' AND ' . $sub . '.flags IN {?}', $this->flags);
 425             }
 426             $conds[] = $c;
 427         }
 428
 429         return implode(' AND ', $conds);
 430     }
 431 }
 432 // }}}
 433
 434 // {{{ class UFC_Nationality
 435 class UFC_Nationality implements UserFilterCondition
 436 {
 437     private $val;
 438
 439     public function __construct()
 440     {
 441         $this->val = pl_flatten(func_get_args());
 442     }
 443
 444     public function buildCondition(PlFilter &$uf)
 445     {
 446         $uf->requireProfiles();
 447         $nat = XDB::formatArray($this->val);
 448         $conds = array(
 449             'p.nationality1 IN ' . $nat,
 450             'p.nationality2 IN ' . $nat,
 451             'p.nationality3 IN ' . $nat,
 452         );
 453         return implode(' OR ', $conds);
 454     }
 455 }
 456 // }}}
 457
 458 // {{{ class UFC_Dead
 459 /** Filters users based on death date
 460  * @param $comparison Comparison operator
 461  * @param $date Date to which death date should be compared (DateTime object, string or timestamp)
 462  */
 463 class UFC_Dead implements UserFilterCondition
 464 {
 465     private $comparison;
 466     private $date;
 467
 468     public function __construct($comparison = null, $date = null)
 469     {
 470         $this->comparison = $comparison;
 471         $this->date = make_datetime($date);
 472     }
 473
 474     public function buildCondition(PlFilter &$uf)
 475     {
 476         $uf->requireProfiles();
 477         $str = 'p.deathdate IS NOT NULL';
 478         if (!is_null($this->comparison)) {
 479             $str .= ' AND p.deathdate ' . $this->comparison . ' ' . XDB::format('{?}', $this->date->format('Y-m-d'));
 480         }
 481         return $str;
 482     }
 483 }
 484 // }}}
 485
 486 // {{{ class UFC_Registered
 487 /** Filters users based on registration state
 488  * @param $active Whether we want to use only "active" users (i.e with a valid redirection)
 489  * @param $comparison Comparison operator
 490  * @param $date Date to which users registration date should be compared
 491  */
 492 class UFC_Registered implements UserFilterCondition
 493 {
 494     private $active;
 495     private $comparison;
 496     private $date;
 497
 498     public function __construct($active = false, $comparison = null, $date = null)
 499     {
 500         $this->active = $active;
 501         $this->comparison = $comparison;
 502         $this->date = make_datetime($date);
 503     }
 504
 505     public function buildCondition(PlFilter &$uf)
 506     {
 507         $uf->requireAccounts();
 508         if ($this->active) {
 509             $date = '$UID IS NOT NULL AND a.state = \'active\'';
 510         } else {
 511             $date = '$UID IS NOT NULL AND a.state != \'pending\'';
 512         }
 513         if (!is_null($this->comparison)) {
 514             $date .= ' AND a.registration_date != \'0000-00-00 00:00:00\' AND a.registration_date ' . $this->comparison . ' ' . XDB::format('{?}', $this->date->format('Y-m-d'));
 515         }
 516         return $date;
 517     }
 518 }
 519 // }}}
 520
 521 // {{{ class UFC_ProfileUpdated
 522 /** Filters users based on profile update date
 523  * @param $comparison Comparison operator
 524  * @param $date Date to which profile update date must be compared
 525  */
 526 class UFC_ProfileUpdated implements UserFilterCondition
 527 {
 528     private $comparison;
 529     private $date;
 530
 531     public function __construct($comparison = null, $date = null)
 532     {
 533         $this->comparison = $comparison;
 534         $this->date = $date;
 535     }
 536
 537     public function buildCondition(PlFilter &$uf)
 538     {
 539         $uf->requireProfiles();
 540         return 'p.last_change ' . $this->comparison . XDB::format(' {?}', date('Y-m-d H:i:s', $this->date));
 541     }
 542 }
 543 // }}}
 544
 545 // {{{ class UFC_Birthday
 546 /** Filters users based on next birthday date
 547  * @param $comparison Comparison operator
 548  * @param $date Date to which users next birthday date should be compared
 549  */
 550 class UFC_Birthday implements UserFilterCondition
 551 {
 552     private $comparison;
 553     private $date;
 554
 555     public function __construct($comparison = null, $date = null)
 556     {
 557         $this->comparison = $comparison;
 558         $this->date = $date;
 559     }
 560
 561     public function buildCondition(PlFilter &$uf)
 562     {
 563         $uf->requireProfiles();
 564         return 'p.next_birthday ' . $this->comparison . XDB::format(' {?}', date('Y-m-d', $this->date));
 565     }
 566 }
 567 // }}}
 568
 569 // {{{ class UFC_Sex
 570 /** Filters users based on sex
 571  * @parm $sex One of User::GENDER_MALE or User::GENDER_FEMALE, for selecting users
 572  */
 573 class UFC_Sex implements UserFilterCondition
 574 {
 575     private $sex;
 576     public function __construct($sex)
 577     {
 578         $this->sex = $sex;
 579     }
 580
 581     public function buildCondition(PlFilter &$uf)
 582     {
 583         if ($this->sex != User::GENDER_MALE && $this->sex != User::GENDER_FEMALE) {
 584             return self::COND_FALSE;
 585         } else {
 586             $uf->requireProfiles();
 587             return XDB::format('p.sex = {?}', $this->sex == User::GENDER_FEMALE ? 'female' : 'male');
 588         }
 589     }
 590 }
 591 // }}}
 592
 593 // {{{ class UFC_Group
 594 /** Filters users based on group membership
 595  * @param $group Group whose members we are selecting
 596  * @param $anim Whether to restrict selection to animators of that group
 597  */
 598 class UFC_Group implements UserFilterCondition
 599 {
 600     private $group;
 601     private $anim;
 602     public function __construct($group, $anim = false)
 603     {
 604         $this->group = $group;
 605         $this->anim = $anim;
 606     }
 607
 608     public function buildCondition(PlFilter &$uf)
 609     {
 610         // Groups have AX visibility.
 611         if ($uf->getVisibilityLevel() == ProfileVisibility::VIS_PUBLIC) {
 612             return self::COND_TRUE;
 613         }
 614         $sub = $uf->addGroupFilter($this->group);
 615         $where = 'gpm' . $sub . '.perms IS NOT NULL';
 616         if ($this->anim) {
 617             $where .= ' AND gpm' . $sub . '.perms = \'admin\'';
 618         }
 619         return $where;
 620     }
 621 }
 622 // }}}
 623
 624 // {{{ class UFC_Binet
 625 /** Selects users based on their belonging to a given (list of) binet
 626  * @param $binet either a binet_id or an array of binet_ids
 627  */
 628 class UFC_Binet implements UserFilterCondition
 629 {
 630     private $val;
 631
 632     public function __construct()
 633     {
 634         $this->val = pl_flatten(func_get_args());
 635     }
 636
 637     public function buildCondition(PlFilter &$uf)
 638     {
 639         // Binets are private.
 640         if ($uf->getVisibilityLevel() != ProfileVisibility::VIS_PRIVATE) {
 641             return self::CONF_TRUE;
 642         }
 643         $sub = $uf->addBinetsFilter();
 644         return XDB::format($sub . '.binet_id IN {?}', $this->val);
 645     }
 646 }
 647 // }}}
 648
 649 // {{{ class UFC_Section
 650 /** Selects users based on section
 651  * @param $section ID of the section
 652  */
 653 class UFC_Section implements UserFilterCondition
 654 {
 655     private $section;
 656
 657     public function __construct()
 658     {
 659         $this->section = pl_flatten(func_get_args());
 660     }
 661
 662     public function buildCondition(PlFilter &$uf)
 663     {
 664         // Sections are private.
 665         if ($uf->getVisibilityLevel() != ProfileVisibility::VIS_PRIVATE) {
 666             return self::CONF_TRUE;
 667         }
 668         $uf->requireProfiles();
 669         return XDB::format('p.section IN {?}', $this->section);
 670     }
 671 }
 672 // }}}
 673
 674 // {{{ class UFC_Email
 675 /** Filters users based on an email or a list of emails
 676  * @param $emails List of emails whose owner must be selected
 677  */
 678 class UFC_Email implements UserFilterCondition
 679 {
 680     private $emails;
 681     public function __construct()
 682     {
 683         $this->emails = pl_flatten(func_get_args());
 684     }
 685
 686     public function buildCondition(PlFilter &$uf)
 687     {
 688         $foreign = array();
 689         $virtual = array();
 690         $aliases = array();
 691         $cond = array();
 692
 693         if (count($this->emails) == 0) {
 694             return PlFilterCondition::COND_TRUE;
 695         }
 696
 697         foreach ($this->emails as $entry) {
 698             if (User::isForeignEmailAddress($entry)) {
 699                 $foreign[] = $entry;
 700             } else if (User::isVirtualEmailAddress($entry)) {
 701                 $virtual[] = $entry;
 702             } else {
 703                 @list($user, $domain) = explode('@', $entry);
 704                 $aliases[] = $user;
 705             }
 706         }
 707
 708         if (count($foreign) > 0) {
 709             $sub = $uf->addEmailRedirectFilter($foreign);
 710             $cond[] = XDB::format('e' . $sub . '.email IS NOT NULL OR a.email IN {?}', $foreign);
 711         }
 712         if (count($virtual) > 0) {
 713             $sub = $uf->addVirtualEmailFilter($virtual);
 714             $cond[] = 'vr' . $sub . '.redirect IS NOT NULL';
 715         }
 716         if (count($aliases) > 0) {
 717             $sub = $uf->addAliasFilter($aliases);
 718             $cond[] = 'al' . $sub . '.alias IS NOT NULL';
 719         }
 720         return '(' . implode(') OR (', $cond) . ')';
 721     }
 722 }
 723 // }}}
 724
 725 // {{{ class UFC_Address
 726 abstract class UFC_Address implements UserFilterCondition
 727 {
 728     /** Valid address type ('hq' is reserved for company addresses)
 729      */
 730     const TYPE_HOME = 1;
 731     const TYPE_PRO  = 2;
 732     const TYPE_ANY  = 3;
 733
 734     /** Text for these types
 735      */
 736     protected static $typetexts = array(
 737         self::TYPE_HOME => 'home',
 738         self::TYPE_PRO  => 'pro',
 739     );
 740
 741     protected $type;
 742
 743     /** Flags for addresses
 744      */
 745     const FLAG_CURRENT = 0x0001;
 746     const FLAG_TEMP    = 0x0002;
 747     const FLAG_SECOND  = 0x0004;
 748     const FLAG_MAIL    = 0x0008;
 749     const FLAG_CEDEX   = 0x0010;
 750
 751     // Binary OR of those flags
 752     const FLAG_ANY     = 0x001F;
 753
 754     /** Text of these flags
 755      */
 756     protected static $flagtexts = array(
 757         self::FLAG_CURRENT => 'current',
 758         self::FLAG_TEMP    => 'temporary',
 759         self::FLAG_SECOND  => 'secondary',
 760         self::FLAG_MAIL    => 'mail',
 761         self::FLAG_CEDEX   => 'cedex',
 762     );
 763
 764     protected $flags;
 765
 766     public function __construct($type = null, $flags = null)
 767     {
 768         $this->type  = $type;
 769         $this->flags = $flags;
 770     }
 771
 772     protected function initConds($sub, $vis_cond)
 773     {
 774         $conds = array($vis_cond);
 775
 776         $types = array();
 777         foreach (self::$typetexts as $flag => $type) {
 778             if ($flag & $this->type) {
 779                 $types[] = $type;
 780             }
 781         }
 782         if (count($types)) {
 783             $conds[] = XDB::format($sub . '.type IN {?}', $types);
 784         }
 785
 786         if ($this->flags != self::FLAG_ANY) {
 787             foreach(self::$flagtexts as $flag => $text) {
 788                 if ($flag & $this->flags) {
 789                     $conds[] = 'FIND_IN_SET(' . XDB::format('{?}', $text) . ', ' . $sub . '.flags)';
 790                 }
 791             }
 792         }
 793         return $conds;
 794     }
 795
 796 }
 797 // }}}
 798
 799 // {{{ class UFC_AddressText
 800 /** Select users based on their address, using full text search
 801  * @param $text Text for filter in fulltext search
 802  * @param $textSearchMode Mode for search (one of XDB::WILDCARD_*)
 803  * @param $type Filter on address type
 804  * @param $flags Filter on address flags
 805  * @param $country Filter on address country
 806  * @param $locality Filter on address locality
 807  */
 808 class UFC_AddressText extends UFC_Address
 809 {
 810
 811     private $text;
 812     private $textSearchMode;
 813
 814     public function __construct($text = null, $textSearchMode = XDB::WILDCARD_CONTAINS,
 815         $type = null, $flags = self::FLAG_ANY, $country = null, $locality = null)
 816     {
 817         parent::__construct($type, $flags);
 818         $this->text           = $text;
 819         $this->textSearchMode = $textSearchMode;
 820         $this->country        = $country;
 821         $this->locality       = $locality;
 822     }
 823
 824     private function mkMatch($txt)
 825     {
 826         return XDB::formatWildcards($this->textSearchMode, $txt);
 827     }
 828
 829     public function buildCondition(PlFilter &$uf)
 830     {
 831         $sub = $uf->addAddressFilter();
 832         $conds = $this->initConds($sub, $uf->getVisibilityCondition($sub . '.pub'));
 833         if ($this->text != null) {
 834             $conds[] = $sub . '.text' . $this->mkMatch($this->text);
 835         }
 836
 837         if ($this->country != null) {
 838             $subc = $uf->addAddressCountryFilter();
 839             $subconds = array();
 840             $subconds[] = $subc . '.country' . $this->mkMatch($this->country);
 841             $subconds[] = $subc . '.countryFR' . $this->mkMatch($this->country);
 842             $conds[] = implode(' OR ', $subconds);
 843         }
 844
 845         if ($this->locality != null) {
 846             $subl = $uf->addAddressLocalityFilter();
 847             $conds[] = $subl . '.name' . $this->mkMatch($this->locality);
 848         }
 849
 850         return implode(' AND ', $conds);
 851     }
 852 }
 853 // }}}
 854
 855 // {{{ class UFC_AddressField
 856 /** Filters users based on their address,
 857  * @param $val Either a code for one of the fields, or an array of such codes
 858  * @param $fieldtype The type of field to look for
 859  * @param $type Filter on address type
 860  * @param $flags Filter on address flags
 861  */
 862 class UFC_AddressField extends UFC_Address
 863 {
 864     const FIELD_COUNTRY    = 1;
 865     const FIELD_ADMAREA    = 2;
 866     const FIELD_SUBADMAREA = 3;
 867     const FIELD_LOCALITY   = 4;
 868     const FIELD_ZIPCODE    = 5;
 869
 870     /** Data of the filter
 871      */
 872     private $val;
 873     private $fieldtype;
 874
 875     public function __construct($val, $fieldtype, $type = null, $flags = self::FLAG_ANY)
 876     {
 877         parent::__construct($type, $flags);
 878
 879         if (!is_array($val)) {
 880             $val = array($val);
 881         }
 882         $this->val       = $val;
 883         $this->fieldtype = $fieldtype;
 884     }
 885
 886     public function buildCondition(PlFilter &$uf)
 887     {
 888         $sub = $uf->addAddressFilter();
 889         $conds = $this->initConds($sub, $uf->getVisibilityCondition($sub . '.pub'));
 890
 891         switch ($this->fieldtype) {
 892         case self::FIELD_COUNTRY:
 893             $field = 'countryId';
 894             break;
 895         case self::FIELD_ADMAREA:
 896             $field = 'administrativeAreaId';
 897             break;
 898         case self::FIELD_SUBADMAREA:
 899             $field = 'subAdministrativeAreaId';
 900             break;
 901         case self::FIELD_LOCALITY:
 902             $field = 'localityId';
 903             break;
 904         case self::FIELD_ZIPCODE:
 905             $field = 'postalCode';
 906             break;
 907         default:
 908             Platal::page()->killError('Invalid address field type: ' . $this->fieldtype);
 909         }
 910         $conds[] = XDB::format($sub . '.' . $field . ' IN {?}', $this->val);
 911
 912         return implode(' AND ', $conds);
 913     }
 914 }
 915 // }}}
 916
 917 // {{{ class UFC_Corps
 918 /** Filters users based on the corps they belong to
 919  * @param $corps Corps we are looking for (abbreviation)
 920  * @param $type Whether we search for original or current corps
 921  */
 922 class UFC_Corps implements UserFilterCondition
 923 {
 924     const CURRENT   = 1;
 925     const ORIGIN    = 2;
 926
 927     private $corps;
 928     private $type;
 929
 930     public function __construct($corps, $type = self::CURRENT)
 931     {
 932         $this->corps = $corps;
 933         $this->type  = $type;
 934     }
 935
 936     public function buildCondition(PlFilter &$uf)
 937     {
 938         /** Tables shortcuts:
 939          * pc for profile_corps,
 940          * pceo for profile_corps_enum - orginal
 941          * pcec for profile_corps_enum - current
 942          */
 943         $sub = $uf->addCorpsFilter($this->type);
 944         $cond = $sub . '.abbreviation = ' . $corps;
 945         $cond .= ' AND ' . $uf->getVisibilityCondition($sub . '.corps_pub');
 946         return $cond;
 947     }
 948 }
 949 // }}}
 950
 951 // {{{ class UFC_Corps_Rank
 952 /** Filters users based on their rank in the corps
 953  * @param $rank Rank we are looking for (abbreviation)
 954  */
 955 class UFC_Corps_Rank implements UserFilterCondition
 956 {
 957     private $rank;
 958     public function __construct($rank)
 959     {
 960         $this->rank = $rank;
 961     }
 962
 963     public function buildCondition(PlFilter &$uf)
 964     {
 965         /** Tables shortcuts:
 966          * pc for profile_corps
 967          * pcr for profile_corps_rank
 968          */
 969         $sub = $uf->addCorpsRankFilter();
 970         $cond = $sub . '.abbreviation = ' . $rank;
 971         // XXX(x2006barrois): find a way to get rid of that hardcoded
 972         // reference to 'pc'.
 973         $cond .= ' AND ' . $uf->getVisibilityCondition('pc.corps_pub');
 974         return $cond;
 975     }
 976 }
 977 // }}}
 978
 979 // {{{ class UFC_Job_Company
 980 /** Filters users based on the company they belong to
 981  * @param $type The field being searched (self::JOBID, self::JOBNAME or self::JOBACRONYM)
 982  * @param $value The searched value
 983  */
 984 class UFC_Job_Company implements UserFilterCondition
 985 {
 986     const JOBID = 'id';
 987     const JOBNAME = 'name';
 988     const JOBACRONYM = 'acronym';
 989
 990     private $type;
 991     private $value;
 992
 993     public function __construct($type, $value)
 994     {
 995         $this->assertType($type);
 996         $this->type = $type;
 997         $this->value = $value;
 998     }
 999
1000     private function assertType($type)
1001     {
1002         if ($type != self::JOBID && $type != self::JOBNAME && $type != self::JOBACRONYM) {
1003             Platal::page()->killError("Type de recherche non valide.");
1004         }
1005     }
1006
1007     public function buildCondition(PlFilter &$uf)
1008     {
1009         $sub = $uf->addJobCompanyFilter();
1010         $cond  = $sub . '.' . $this->type . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->value);
1011         $jsub = $uf->addJobFilter();
1012         $cond .= ' AND ' . $uf->getVisibilityCondition($jsub . '.pub');
1013         return $cond;
1014     }
1015 }
1016 // }}}
1017
1018 // {{{ class UFC_Job_Terms
1019 /** Filters users based on the job terms they assigned to one of their
1020  * jobs.
1021  * @param $val The ID of the job term, or an array of such IDs
1022  */
1023 class UFC_Job_Terms implements UserFilterCondition
1024 {
1025     private $val;
1026
1027     public function __construct($val)
1028     {
1029         if (!is_array($val)) {
1030             $val = array($val);
1031         }
1032         $this->val = $val;
1033     }
1034
1035     public function buildCondition(PlFilter &$uf)
1036     {
1037         $sub = $uf->addJobTermsFilter(count($this->val));
1038         $conditions = array();
1039         foreach ($this->val as $i => $jtid) {
1040             $conditions[] = $sub[$i] . '.jtid_1 = ' . XDB::escape($jtid);
1041         }
1042         $jsub = $uf->addJobFilter();
1043         $conditions[] = $uf->getVisibilityCondition($jsub . '.pub');
1044         return implode(' AND ', $conditions);
1045     }
1046 }
1047 // }}}
1048
1049 // {{{ class UFC_Job_Description
1050 /** Filters users based on their job description
1051  * @param $description The text being searched for
1052  * @param $fields The fields to search for (CV, user-defined)
1053  */
1054 class UFC_Job_Description implements UserFilterCondition
1055 {
1056
1057     private $description;
1058     private $fields;
1059
1060     public function __construct($description, $fields)
1061     {
1062         $this->fields = $fields;
1063         $this->description = $description;
1064     }
1065
1066     public function buildCondition(PlFilter &$uf)
1067     {
1068         $conds = array();
1069
1070         $jsub = $uf->addJobFilter();
1071         // CV is private => if only CV requested, and not private,
1072         // don't do anything. Otherwise restrict to standard job visibility.
1073         if ($this->fields == UserFilter::JOB_CV) {
1074            if ($uf->getVisibilityLevel() != ProfileVisibility::VIS_PRIVATE) {
1075                return self::CONF_TRUE;
1076            }
1077         } else {
1078             $conds[] = $uf->getVisibilityCondition($jsub . '.pub');
1079         }
1080
1081         if ($this->fields & UserFilter::JOB_USERDEFINED) {
1082             $conds[] = $jsub . '.description ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description);
1083         }
1084         if ($this->fields & UserFilter::JOB_CV && $uf->getVisibilityLevel() == ProfileVisibility::VIS_PRIVATE) {
1085             $uf->requireProfiles();
1086             $conds[] = 'p.cv ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description);
1087         }
1088         return implode(' OR ', $conds);
1089     }
1090 }
1091 // }}}
1092
1093 // {{{ class UFC_Networking
1094 /** Filters users based on network identity (IRC, ...)
1095  * @param $type Type of network (-1 for any)
1096  * @param $value Value to search
1097  */
1098 class UFC_Networking implements UserFilterCondition
1099 {
1100     private $type;
1101     private $value;
1102
1103     public function __construct($type, $value)
1104     {
1105         $this->type = $type;
1106         $this->value = $value;
1107     }
1108
1109     public function buildCondition(PlFilter &$uf)
1110     {
1111         $sub = $uf->addNetworkingFilter();
1112         $conds = array();
1113         $conds[] = $uf->getVisibilityCondition($sub . '.pub');
1114         $conds[] = $sub . '.address ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->value);
1115         if ($this->type != -1) {
1116             $conds[] = $sub . '.nwid = ' . XDB::format('{?}', $this->type);
1117         }
1118         return implode(' AND ', $conds);
1119     }
1120 }
1121 // }}}
1122
1123 // {{{ class UFC_Phone
1124 /** Filters users based on their phone number
1125  * @param $num_type Type of number (pro/user/home)
1126  * @param $phone_type Type of phone (fixed/mobile/fax)
1127  * @param $number Phone number
1128  */
1129 class UFC_Phone implements UserFilterCondition
1130 {
1131     const NUM_PRO   = 'pro';
1132     const NUM_USER  = 'user';
1133     const NUM_HOME  = 'address';
1134     const NUM_ANY   = 'any';
1135
1136     const PHONE_FIXED   = 'fixed';
1137     const PHONE_MOBILE  = 'mobile';
1138     const PHONE_FAX     = 'fax';
1139     const PHONE_ANY     = 'any';
1140
1141     private $num_type;
1142     private $phone_type;
1143     private $number;
1144
1145     public function __construct($number, $num_type = self::NUM_ANY, $phone_type = self::PHONE_ANY)
1146     {
1147         $phone = new Phone(array('display' => $number));
1148         $phone->format();
1149         $this->number = $phone->search();
1150         $this->num_type = $num_type;
1151         $this->phone_type = $phone_type;
1152     }
1153
1154     public function buildCondition(PlFilter &$uf)
1155     {
1156         $sub = $uf->addPhoneFilter();
1157         $conds = array();
1158
1159         $conds[] = $uf->getVisibilityCondition($sub . '.pub');
1160
1161         $conds[] = $sub . '.search_tel = ' . XDB::format('{?}', $this->number);
1162         if ($this->num_type != self::NUM_ANY) {
1163             $conds[] = $sub . '.link_type = ' . XDB::format('{?}', $this->num_type);
1164         }
1165         if ($this->phone_type != self::PHONE_ANY) {
1166             $conds[] = $sub . '.tel_type = ' . XDB::format('{?}', $this->phone_type);
1167         }
1168         return implode(' AND ', $conds);
1169     }
1170 }
1171 // }}}
1172
1173 // {{{ class UFC_Medal
1174 /** Filters users based on their medals
1175  * @param $medal ID of the medal
1176  * @param $grade Grade of the medal (null for 'any')
1177  */
1178 class UFC_Medal implements UserFilterCondition
1179 {
1180     private $medal;
1181     private $grade;
1182
1183     public function __construct($medal, $grade = null)
1184     {
1185         $this->medal = $medal;
1186         $this->grade = $grade;
1187     }
1188
1189     public function buildCondition(PlFilter &$uf)
1190     {
1191         $conds = array();
1192
1193         // This will require profiles => table 'p' will be available.
1194         $sub = $uf->addMedalFilter();
1195
1196         $conds[] = $uf->getVisibilityCondition('p.medals_pub');
1197
1198         $conds[] = $sub . '.mid = ' . XDB::format('{?}', $this->medal);
1199         if ($this->grade != null) {
1200             $conds[] = $sub . '.gid = ' . XDB::format('{?}', $this->grade);
1201         }
1202         return implode(' AND ', $conds);
1203     }
1204 }
1205 // }}}
1206
1207 // {{{ class UFC_Photo
1208 /** Filters profiles with photo
1209  */
1210 class UFC_Photo implements UserFilterCondition
1211 {
1212     public function buildCondition(PlFilter &$uf)
1213     {
1214         $sub = $uf->addPhotoFilter();
1215         return $sub . '.attach IS NOT NULL AND ' . $uf->getVisibilityCondition($sub . '.pub');
1216     }
1217 }
1218 // }}}
1219
1220 // {{{ class UFC_Mentor
1221 class UFC_Mentor implements UserFilterCondition
1222 {
1223     public function buildCondition(PlFilter &$uf)
1224     {
1225         $sub = $uf->addMentorFilter(UserFilter::MENTOR);
1226         return $sub . '.expertise IS NOT NULL';
1227     }
1228 }
1229 // }}}
1230
1231
1232 // {{{ class UFC_Mentor_Expertise
1233 /** Filters users by mentoring expertise
1234  * @param $expertise Domain of expertise
1235  */
1236 class UFC_Mentor_Expertise implements UserFilterCondition
1237 {
1238     private $expertise;
1239
1240     public function __construct($expertise)
1241     {
1242         $this->expertise = $expertise;
1243     }
1244
1245     public function buildCondition(PlFilter &$uf)
1246     {
1247         $sub = $uf->addMentorFilter(UserFilter::MENTOR_EXPERTISE);
1248         return $sub . '.expertise ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->expertise);
1249     }
1250 }
1251 // }}}
1252
1253 // {{{ class UFC_Mentor_Country
1254 /** Filters users by mentoring country
1255  * @param $country Two-letters code of country being searched
1256  */
1257 class UFC_Mentor_Country implements UserFilterCondition
1258 {
1259     private $country;
1260
1261     public function __construct()
1262     {
1263         $this->country = pl_flatten(func_get_args());
1264     }
1265
1266     public function buildCondition(PlFilter &$uf)
1267     {
1268         $sub = $uf->addMentorFilter(UserFilter::MENTOR_COUNTRY);
1269         return $sub . '.country IN ' . XDB::format('{?}', $this->country);
1270     }
1271 }
1272 // }}}
1273
1274 // {{{ class UFC_Mentor_Terms
1275 /** Filters users based on the job terms they used in mentoring.
1276  * @param $val The ID of the job term, or an array of such IDs
1277  */
1278 class UFC_Mentor_Terms implements UserFilterCondition
1279 {
1280     private $val;
1281
1282     public function __construct($val)
1283     {
1284         $this->val = $val;
1285     }
1286
1287     public function buildCondition(PlFilter &$uf)
1288     {
1289         $sub = $uf->addMentorFilter(UserFilter::MENTOR_TERM);
1290         return $sub . '.jtid_1 = ' . XDB::escape($this->val);
1291     }
1292 }
1293 // }}}
1294
1295 // {{{ class UFC_UserRelated
1296 /** Filters users based on a relation toward a user
1297  * @param $user User to which searched users are related
1298  */
1299 abstract class UFC_UserRelated implements UserFilterCondition
1300 {
1301     protected $user;
1302     public function __construct(PlUser &$user)
1303     {
1304         $this->user =& $user;
1305     }
1306 }
1307 // }}}
1308
1309 // {{{ class UFC_Contact
1310 /** Filters users who belong to selected user's contacts
1311  */
1312 class UFC_Contact extends UFC_UserRelated
1313 {
1314     public function buildCondition(PlFilter &$uf)
1315     {
1316         $sub = $uf->addContactFilter($this->user->id());
1317         return 'c' . $sub . '.contact IS NOT NULL';
1318     }
1319 }
1320 // }}}
1321
1322 // {{{ class UFC_WatchRegistration
1323 /** Filters users being watched by selected user
1324  */
1325 class UFC_WatchRegistration extends UFC_UserRelated
1326 {
1327     public function buildCondition(PlFilter &$uf)
1328     {
1329         if (!$this->user->watchType('registration')) {
1330             return PlFilterCondition::COND_FALSE;
1331         }
1332         $uids = $this->user->watchUsers();
1333         if (count($uids) == 0) {
1334             return PlFilterCondition::COND_FALSE;
1335         } else {
1336             return XDB::format('$UID IN {?}', $uids);
1337         }
1338     }
1339 }
1340 // }}}
1341
1342 // {{{ class UFC_WatchPromo
1343 /** Filters users belonging to a promo watched by selected user
1344  * @param $user Selected user (the one watching promo)
1345  * @param $grade Formation the user is watching
1346  */
1347 class UFC_WatchPromo extends UFC_UserRelated
1348 {
1349     private $grade;
1350     public function __construct(PlUser &$user, $grade = UserFilter::GRADE_ING)
1351     {
1352         parent::__construct($user);
1353         $this->grade = $grade;
1354     }
1355
1356     public function buildCondition(PlFilter &$uf)
1357     {
1358         $promos = $this->user->watchPromos();
1359         if (count($promos) == 0) {
1360             return PlFilterCondition::COND_FALSE;
1361         } else {
1362             $sube = $uf->addEducationFilter(true, $this->grade);
1363             $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade);
1364             return XDB::format($field . ' IN {?}', $promos);
1365         }
1366     }
1367 }
1368 // }}}
1369
1370 // {{{ class UFC_WatchContact
1371 /** Filters users watched by selected user
1372  */
1373 class UFC_WatchContact extends UFC_Contact
1374 {
1375     public function buildCondition(PlFilter &$uf)
1376     {
1377         if (!$this->user->watchContacts()) {
1378             return PlFilterCondition::COND_FALSE;
1379         }
1380         return parent::buildCondition($uf);
1381     }
1382 }
1383 // }}}
1384
1385 // {{{ class UFC_MarketingHash
1386 /** Filters users using the hash generated
1387  * to send marketing emails to him.
1388  */
1389 class UFC_MarketingHash implements UserFilterCondition
1390 {
1391     private $hash;
1392
1393     public function __construct($hash)
1394     {
1395         $this->hash = $hash;
1396     }
1397
1398     public function buildCondition(PlFilter &$uf)
1399     {
1400         $table = $uf->addMarketingHash();
1401         return XDB::format('rm.hash = {?}', $this->hash);
1402     }
1403 }
1404 // }}}
1405
1406 /******************
1407  * ORDERS
1408  ******************/
1409
1410 // {{{ class UFO_Promo
1411 /** Orders users by promotion
1412  * @param $grade Formation whose promotion users should be sorted by (restricts results to users of that formation)
1413  * @param $desc Whether sort is descending
1414  */
1415 class UFO_Promo extends PlFilterGroupableOrder
1416 {
1417     private $grade;
1418
1419     public function __construct($grade = null, $desc = false)
1420     {
1421         parent::__construct($desc);
1422         $this->grade = $grade;
1423     }
1424
1425     protected function getSortTokens(PlFilter &$uf)
1426     {
1427         if (UserFilter::isGrade($this->grade)) {
1428             $sub = $uf->addEducationFilter($this->grade);
1429             return 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
1430         } else {
1431             $sub = $uf->addDisplayFilter();
1432             return 'pd' . $sub . '.promo';
1433         }
1434     }
1435 }
1436 // }}}
1437
1438 // {{{ class UFO_Name
1439 /** Sorts users by name
1440  * @param $type Type of name on which to sort (firstname...)
1441  * @param $variant Variant of that name to use (marital, ordinary...)
1442  * @param $particle Set to true if particles should be included in the sorting order
1443  * @param $desc If sort order should be descending
1444  */
1445 class UFO_Name extends PlFilterOrder
1446 {
1447     private $type;
1448     private $variant;
1449     private $particle;
1450
1451     public function __construct($type, $variant = null, $particle = false, $desc = false)
1452     {
1453         parent::__construct($desc);
1454         $this->type = $type;
1455         $this->variant = $variant;
1456         $this->particle = $particle;
1457     }
1458
1459     protected function getSortTokens(PlFilter &$uf)
1460     {
1461         if (Profile::isDisplayName($this->type)) {
1462             $sub = $uf->addDisplayFilter();
1463             $token = 'pd' . $sub . '.' . $this->type;
1464             if ($uf->accountsRequired()) {
1465                 $account_token = Profile::getAccountEquivalentName($this->type);
1466                 return 'IFNULL(' . $token . ', a.' . $account_token . ')';
1467             } else {
1468                 return $token;
1469             }
1470         } else {
1471             $sub = $uf->addNameFilter($this->type, $this->variant);
1472             if ($this->particle) {
1473                 return 'CONCAT(pn' . $sub . '.particle, \' \', pn' . $sub . '.name)';
1474             } else {
1475                 return 'pn' . $sub . '.name';
1476             }
1477         }
1478     }
1479 }
1480 // }}}
1481
1482 // {{{ class UFO_Score
1483 class UFO_Score extends PlFilterOrder
1484 {
1485     protected function getSortTokens(PlFilter &$uf)
1486     {
1487         $toks = $uf->getNameTokens();
1488         $scores = array();
1489
1490         // If there weren't any sort tokens, we shouldn't sort by score, sort by NULL instead
1491         if (count($toks) == 0) {
1492             return 'NULL';
1493         }
1494
1495         foreach ($toks as $sub => $token) {
1496             $scores[] = XDB::format('SUM(' . $sub . '.score + IF (' . $sub . '.token = {?}, 5, 0) )', $token);
1497         }
1498         return implode(' + ', $scores);
1499     }
1500 }
1501 // }}}
1502
1503 // {{{ class UFO_Registration
1504 /** Sorts users based on registration date
1505  */
1506 class UFO_Registration extends PlFilterOrder
1507 {
1508     protected function getSortTokens(PlFilter &$uf)
1509     {
1510         $uf->requireAccounts();
1511         return 'a.registration_date';
1512     }
1513 }
1514 // }}}
1515
1516 // {{{ class UFO_Birthday
1517 /** Sorts users based on next birthday date
1518  */
1519 class UFO_Birthday extends PlFilterOrder
1520 {
1521     protected function getSortTokens(PlFilter &$uf)
1522     {
1523         $uf->requireProfiles();
1524         return 'p.next_birthday';
1525     }
1526 }
1527 // }}}
1528
1529 // {{{ class UFO_ProfileUpdate
1530 /** Sorts users based on last profile update
1531  */
1532 class UFO_ProfileUpdate extends PlFilterOrder
1533 {
1534     protected function getSortTokens(PlFilter &$uf)
1535     {
1536         $uf->requireProfiles();
1537         return 'p.last_change';
1538     }
1539 }
1540 // }}}
1541
1542 // {{{ class UFO_Death
1543 /** Sorts users based on death date
1544  */
1545 class UFO_Death extends PlFilterOrder
1546 {
1547     protected function getSortTokens(PlFilter &$uf)
1548     {
1549         $uf->requireProfiles();
1550         return 'p.deathdate';
1551     }
1552 }
1553 // }}}
1554
1555 // {{{ class UFO_Uid
1556 /** Sorts users based on their uid
1557  */
1558 class UFO_Uid extends PlFilterOrder
1559 {
1560     protected function getSortTokens(PlFilter &$uf)
1561     {
1562         $uf->requireAccounts();
1563         return '$UID';
1564     }
1565 }
1566 // }}}
1567
1568 // {{{ class UFO_Hruid
1569 /** Sorts users based on their hruid
1570  */
1571 class UFO_Hruid extends PlFilterOrder
1572 {
1573     protected function getSortTokens(PlFilter &$uf)
1574     {
1575         $uf->requireAccounts();
1576         return 'a.hruid';
1577     }
1578 }
1579 // }}}
1580
1581 // {{{ class UFO_Pid
1582 /** Sorts users based on their pid
1583  */
1584 class UFO_Pid extends PlFilterOrder
1585 {
1586     protected function getSortTokens(PlFilter &$uf)
1587     {
1588         $uf->requireProfiles();
1589         return '$PID';
1590     }
1591 }
1592 // }}}
1593
1594 // {{{ class UFO_Hrpid
1595 /** Sorts users based on their hrpid
1596  */
1597 class UFO_Hrpid extends PlFilterOrder
1598 {
1599     protected function getSortTokens(PlFilter &$uf)
1600     {
1601         $uf->requireProfiles();
1602         return 'p.hrpid';
1603     }
1604 }
1605 // }}}
1606
1607
1608 /***********************************
1609   *********************************
1610           USER FILTER CLASS
1611   *********************************
1612  ***********************************/
1613
1614 // {{{ class UserFilter
1615 /** This class provides a convenient and centralized way of filtering users.
1616  *
1617  * Usage:
1618  * $uf = new UserFilter(new UFC_Blah($x, $y), new UFO_Coin($z, $t));
1619  *
1620  * Resulting UserFilter can be used to:
1621  * - get a list of User objects matching the filter
1622  * - get a list of UIDs matching the filter
1623  * - get the number of users matching the filter
1624  * - check whether a given User matches the filter
1625  * - filter a list of User objects depending on whether they match the filter
1626  *
1627  * Usage for UFC and UFO objects:
1628  * A UserFilter will call all private functions named XXXJoins.
1629  * These functions must return an array containing the list of join
1630  * required by the various UFC and UFO associated to the UserFilter.
1631  * Entries in those returned array are of the following form:
1632  *   'join_tablealias' => array('join_type', 'joined_table', 'join_criter')
1633  * which will be translated into :
1634  *   join_type JOIN joined_table AS join_tablealias ON (join_criter)
1635  * in the final query.
1636  *
1637  * In the join_criter text, $ME is replaced with 'join_tablealias', $PID with
1638  * profile.pid, and $UID with accounts.uid.
1639  *
1640  * For each kind of "JOIN" needed, a function named addXXXFilter() should be defined;
1641  * its parameter will be used to set various private vars of the UserFilter describing
1642  * the required joins ; such a function shall return the "join_tablealias" to use
1643  * when referring to the joined table.
1644  *
1645  * For example, if data from profile_job must be available to filter results,
1646  * the UFC object will call $uf-addJobFilter(), which will set the 'with_pj' var and
1647  * return 'pj', the short name to use when referring to profile_job; when building
1648  * the query, calling the jobJoins function will return an array containing a single
1649  * row:
1650  *   'pj' => array('left', 'profile_job', '$ME.pid = $UID');
1651  *
1652  * The 'register_optional' function can be used to generate unique table aliases when
1653  * the same table has to be joined several times with different aliases.
1654  */
1655 class UserFilter extends PlFilter
1656 {
1657     protected $joinMethods = array();
1658
1659     protected $joinMetas = array(
1660                                 '$PID' => 'p.pid',
1661                                 '$UID' => 'a.uid',
1662                                 );
1663
1664     private $root;
1665     private $sort = array();
1666     private $grouper = null;
1667     private $query = null;
1668     private $orderby = null;
1669
1670     // Store the current 'search' visibility.
1671     private $profile_visibility = null;
1672
1673     private $lastusercount = null;
1674     private $lastprofilecount = null;
1675
1676     public function __construct($cond = null, $sort = null)
1677     {
1678         if (empty($this->joinMethods)) {
1679             $class = new ReflectionClass('UserFilter');
1680             foreach ($class->getMethods() as $method) {
1681                 $name = $method->getName();
1682                 if (substr($name, -5) == 'Joins' && $name != 'buildJoins') {
1683                     $this->joinMethods[] = $name;
1684                 }
1685             }
1686         }
1687         if (!is_null($cond)) {
1688             if ($cond instanceof PlFilterCondition) {
1689                 $this->setCondition($cond);
1690             }
1691         }
1692         if (!is_null($sort)) {
1693             if ($sort instanceof PlFilterOrder) {
1694                 $this->addSort($sort);
1695             } else if (is_array($sort)) {
1696                 foreach ($sort as $s) {
1697                     $this->addSort($s);
1698                 }
1699             }
1700         }
1701
1702         // This will set the visibility to the default correct level.
1703         $this->profile_visibility = new ProfileVisibility();
1704     }
1705
1706     public function getVisibilityLevels()
1707     {
1708         return $this->profile_visibility->levels();
1709     }
1710
1711     public function getVisibilityLevel()
1712     {
1713         return $this->profile_visibility->level();
1714     }
1715
1716     public function restrictVisibilityTo($level)
1717     {
1718         $this->profile_visibility->setLevel($level);
1719     }
1720
1721     public function getVisibilityCondition($field)
1722     {
1723         return $field . ' IN ' . XDB::formatArray($this->getVisibilityLevels());
1724     }
1725
1726     private function buildQuery()
1727     {
1728         // The root condition is built first because some orders need info
1729         // available only once all UFC have set their conditions (UFO_Score)
1730         if (is_null($this->query)) {
1731             $where = $this->root->buildCondition($this);
1732             $where = str_replace(array_keys($this->joinMetas),
1733                                  $this->joinMetas,
1734                                  $where);
1735         }
1736         if (is_null($this->orderby)) {
1737             $orders = array();
1738             foreach ($this->sort as $sort) {
1739                 $orders = array_merge($orders, $sort->buildSort($this));
1740             }
1741             if (count($orders) == 0) {
1742                 $this->orderby = '';
1743             } else {
1744                 $this->orderby = 'ORDER BY  ' . implode(', ', $orders);
1745             }
1746             $this->orderby = str_replace(array_keys($this->joinMetas),
1747                                          $this->joinMetas,
1748                                          $this->orderby);
1749         }
1750         if (is_null($this->query)) {
1751             if ($this->with_accounts) {
1752                 $from = 'accounts AS a';
1753             } else {
1754                 $this->requireProfiles();
1755                 $from = 'profiles AS p';
1756             }
1757             $joins = $this->buildJoins();
1758             $this->query = 'FROM  ' . $from . '
1759                                ' . $joins . '
1760                            WHERE  (' . $where . ')';
1761         }
1762     }
1763
1764     public function hasGroups()
1765     {
1766         return $this->grouper != null;
1767     }
1768
1769     public function getGroups()
1770     {
1771         return $this->getUIDGroups();
1772     }
1773
1774     public function getUIDGroups()
1775     {
1776         $this->requireAccounts();
1777         $this->buildQuery();
1778         $token = $this->grouper->getGroupToken($this);
1779
1780         $groups = XDB::rawFetchAllRow('SELECT ' . $token . ', COUNT(a.uid)
1781                                       ' . $this->query . '
1782                                       GROUP BY  ' . $token,
1783                                       0);
1784         return $groups;
1785     }
1786
1787     public function getPIDGroups()
1788     {
1789         $this->requireProfiles();
1790         $this->buildQuery();
1791         $token = $this->grouper->getGroupToken($this);
1792
1793         $groups = XDB::rawFetchAllRow('SELECT ' . $token . ', COUNT(p.pid)
1794                                       ' . $this->query . '
1795                                       GROUP BY  ' . $token,
1796                                       0);
1797         return $groups;
1798     }
1799
1800     private function getUIDList($uids = null, PlLimit &$limit)
1801     {
1802         $this->requireAccounts();
1803         $this->buildQuery();
1804         $lim = $limit->getSql();
1805         $cond = '';
1806         if (!empty($uids)) {
1807             $cond = XDB::format(' AND a.uid IN {?}', $uids);
1808         }
1809         $fetched = XDB::rawFetchColumn('SELECT SQL_CALC_FOUND_ROWS  a.uid
1810                                        ' . $this->query . $cond . '
1811                                        GROUP BY  a.uid
1812                                        ' . $this->orderby . '
1813                                        ' . $lim);
1814         $this->lastusercount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
1815         return $fetched;
1816     }
1817
1818     private function getPIDList($pids = null, PlLimit &$limit)
1819     {
1820         $this->requireProfiles();
1821         $this->buildQuery();
1822         $lim = $limit->getSql();
1823         $cond = '';
1824         if (!is_null($pids)) {
1825             $cond = XDB::format(' AND p.pid IN {?}', $pids);
1826         }
1827         $fetched = XDB::rawFetchColumn('SELECT  SQL_CALC_FOUND_ROWS  p.pid
1828                                        ' . $this->query . $cond . '
1829                                        GROUP BY  p.pid
1830                                        ' . $this->orderby . '
1831                                        ' . $lim);
1832         $this->lastprofilecount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
1833         return $fetched;
1834     }
1835
1836     private static function defaultLimit($limit) {
1837         if ($limit == null) {
1838             return new PlLimit();
1839         } else {
1840             return $limit;
1841         }
1842     }
1843
1844     /** Check that the user match the given rule.
1845      */
1846     public function checkUser(PlUser &$user)
1847     {
1848         $this->requireAccounts();
1849         $this->buildQuery();
1850         $count = (int)XDB::rawFetchOneCell('SELECT  COUNT(*)
1851                                            ' . $this->query
1852                                              . XDB::format(' AND a.uid = {?}', $user->id()));
1853         return $count == 1;
1854     }
1855
1856     /** Check that the profile match the given rule.
1857      */
1858     public function checkProfile(Profile &$profile)
1859     {
1860         $this->requireProfiles();
1861         $this->buildQuery();
1862         $count = (int)XDB::rawFetchOneCell('SELECT  COUNT(*)
1863                                            ' . $this->query
1864                                              . XDB::format(' AND p.pid = {?}', $profile->id()));
1865         return $count == 1;
1866     }
1867
1868     /** Default filter is on users
1869      */
1870     public function filter(array $users, $limit = null)
1871     {
1872         return $this->filterUsers($users, self::defaultLimit($limit));
1873     }
1874
1875     /** Filter a list of users to extract the users matching the rule.
1876      */
1877     public function filterUsers(array $users, $limit = null)
1878     {
1879         $limit = self::defaultLimit($limit);
1880         $this->requireAccounts();
1881         $this->buildQuery();
1882         $table = array();
1883         $uids  = array();
1884         foreach ($users as $user) {
1885             if ($user instanceof PlUser) {
1886                 $uid = $user->id();
1887             } else {
1888                 $uid = $user;
1889             }
1890             $uids[] = $uid;
1891             $table[$uid] = $user;
1892         }
1893         $fetched = $this->getUIDList($uids, $limit);
1894         $output = array();
1895         foreach ($fetched as $uid) {
1896             $output[] = $table[$uid];
1897         }
1898         return $output;
1899     }
1900
1901     /** Filter a list of profiles to extract the users matching the rule.
1902      */
1903     public function filterProfiles(array $profiles, $limit = null)
1904     {
1905         $limit = self::defaultLimit($limit);
1906         $this->requireProfiles();
1907         $this->buildQuery();
1908         $table = array();
1909         $pids  = array();
1910         foreach ($profiles as $profile) {
1911             if ($profile instanceof Profile) {
1912                 $pid = $profile->id();
1913             } else {
1914                 $pid = $profile;
1915             }
1916             $pids[] = $pid;
1917             $table[$pid] = $profile;
1918         }
1919         $fetched = $this->getPIDList($pids, $limit);
1920         $output = array();
1921         foreach ($fetched as $pid) {
1922             $output[] = $table[$pid];
1923         }
1924         return $output;
1925     }
1926
1927     public function getUIDs($limit = null)
1928     {
1929         $limit = self::defaultLimit($limit);
1930         return $this->getUIDList(null, $limit);
1931     }
1932
1933     public function getUID($pos = 0)
1934     {
1935         $uids =$this->getUIDList(null, new PlLimit(1, $pos));
1936         if (count($uids) == 0) {
1937             return null;
1938         } else {
1939             return $uids[0];
1940         }
1941     }
1942
1943     public function getPIDs($limit = null)
1944     {
1945         $limit = self::defaultLimit($limit);
1946         return $this->getPIDList(null, $limit);
1947     }
1948
1949     public function getPID($pos = 0)
1950     {
1951         $pids =$this->getPIDList(null, new PlLimit(1, $pos));
1952         if (count($pids) == 0) {
1953             return null;
1954         } else {
1955             return $pids[0];
1956         }
1957     }
1958
1959     public function getUsers($limit = null)
1960     {
1961         return User::getBulkUsersWithUIDs($this->getUIDs($limit));
1962     }
1963
1964     public function getUser($pos = 0)
1965     {
1966         $uid = $this->getUID($pos);
1967         if ($uid == null) {
1968             return null;
1969         } else {
1970             return User::getWithUID($uid);
1971         }
1972     }
1973
1974     public function iterUsers($limit = null)
1975     {
1976         return User::iterOverUIDs($this->getUIDs($limit));
1977     }
1978
1979     public function getProfiles($limit = null, $fields = 0x0000, $visibility = null)
1980     {
1981         return Profile::getBulkProfilesWithPIDs($this->getPIDs($limit), $fields, $visibility);
1982     }
1983
1984     public function getProfile($pos = 0, $fields = 0x0000, $visibility = null)
1985     {
1986         $pid = $this->getPID($pos);
1987         if ($pid == null) {
1988             return null;
1989         } else {
1990             return Profile::get($pid, $fields, $visibility);
1991         }
1992     }
1993
1994     public function iterProfiles($limit = null, $fields = 0x0000, $visibility = null)
1995     {
1996         return Profile::iterOverPIDs($this->getPIDs($limit), true, $fields, $visibility);
1997     }
1998
1999     public function get($limit = null)
2000     {
2001         return $this->getUsers($limit);
2002     }
2003
2004     public function getIds($limit = null)
2005     {
2006         return $this->getUIDs();
2007     }
2008
2009     public function getTotalCount()
2010     {
2011         return $this->getTotalUserCount();
2012     }
2013
2014     public function getTotalUserCount()
2015     {
2016         if (is_null($this->lastusercount)) {
2017             $this->requireAccounts();
2018             $this->buildQuery();
2019             return (int)XDB::rawFetchOneCell('SELECT  COUNT(DISTINCT a.uid)
2020                                           ' . $this->query);
2021         } else {
2022             return $this->lastusercount;
2023         }
2024     }
2025
2026     public function getTotalProfileCount()
2027     {
2028         if (is_null($this->lastprofilecount)) {
2029             $this->requireProfiles();
2030             $this->buildQuery();
2031             return (int)XDB::rawFetchOneCell('SELECT  COUNT(DISTINCT p.pid)
2032                                           ' . $this->query);
2033         } else {
2034             return $this->lastprofilecount;
2035         }
2036     }
2037
2038     public function setCondition(PlFilterCondition &$cond)
2039     {
2040         $this->root =& $cond;
2041         $this->query = null;
2042     }
2043
2044     public function addSort(PlFilterOrder &$sort)
2045     {
2046         if (count($this->sort) == 0 && $sort instanceof PlFilterGroupableOrder)
2047         {
2048             $this->grouper = $sort;
2049         }
2050         $this->sort[] = $sort;
2051         $this->orderby = null;
2052     }
2053
2054     static public function getLegacy($promo_min, $promo_max)
2055     {
2056         if ($promo_min != 0) {
2057             $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min));
2058         } else {
2059             $min = new PFC_True();
2060         }
2061         if ($promo_max != 0) {
2062             $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max));
2063         } else {
2064             $max = new PFC_True();
2065         }
2066         return new UserFilter(new PFC_And($min, $max));
2067     }
2068
2069     static public function sortByName()
2070     {
2071         return array(new UFO_Name(Profile::LASTNAME), new UFO_Name(Profile::FIRSTNAME));
2072     }
2073
2074     static public function sortByPromo()
2075     {
2076         return array(new UFO_Promo(), new UFO_Name(Profile::LASTNAME), new UFO_Name(Profile::FIRSTNAME));
2077     }
2078
2079     static private function getDBSuffix($string)
2080     {
2081         if (is_array($string)) {
2082             if (count($string) == 1) {
2083                 return self::getDBSuffix(array_pop($string));
2084             }
2085             return md5(implode('|', $string));
2086         } else {
2087             return preg_replace('/[^a-z0-9]/i', '', $string);
2088         }
2089     }
2090
2091
2092     /** Stores a new (and unique) table alias in the &$table table
2093      * @param   &$table Array in which the table alias must be stored
2094      * @param   $val    Value which will then be used to build the join
2095      * @return          Name of the newly created alias
2096      */
2097     private $option = 0;
2098     private function register_optional(array &$table, $val)
2099     {
2100         if (is_null($val)) {
2101             $sub   = $this->option++;
2102             $index = null;
2103         } else {
2104             $sub   = self::getDBSuffix($val);
2105             $index = $val;
2106         }
2107         $sub = '_' . $sub;
2108         $table[$sub] = $index;
2109         return $sub;
2110     }
2111
2112     /** PROFILE VS ACCOUNT
2113      */
2114     private $with_profiles  = false;
2115     private $with_accounts  = false;
2116     public function requireAccounts()
2117     {
2118         $this->with_accounts = true;
2119     }
2120
2121     public function accountsRequired()
2122     {
2123         return $this->with_accounts;
2124     }
2125
2126     public function requireProfiles()
2127     {
2128         $this->with_profiles = true;
2129     }
2130
2131     public function profilesRequired()
2132     {
2133         return $this->with_profiles;
2134     }
2135
2136     protected function accountJoins()
2137     {
2138         $joins = array();
2139         if ($this->with_profiles && $this->with_accounts) {
2140             $joins['ap'] = PlSqlJoin::left('account_profiles', '$ME.uid = $UID AND FIND_IN_SET(\'owner\', ap.perms)');
2141             $joins['p'] = PlSqlJoin::left('profiles', '$PID = ap.pid');
2142         }
2143         return $joins;
2144     }
2145
2146     /** PERMISSIONS
2147      */
2148     private $at = false;
2149     public function requirePerms()
2150     {
2151         $this->requireAccounts();
2152         $this->at = true;
2153         return 'at';
2154     }
2155
2156     protected function permJoins()
2157     {
2158         if ($this->at) {
2159             return array('at' => PlSqlJoin::left('account_types', '$ME.type = a.type'));
2160         } else {
2161             return array();
2162         }
2163     }
2164
2165     /** DISPLAY
2166      */
2167     const DISPLAY = 'display';
2168     private $pd = false;
2169     public function addDisplayFilter()
2170     {
2171         $this->requireProfiles();
2172         $this->pd = true;
2173         return '';
2174     }
2175
2176     protected function displayJoins()
2177     {
2178         if ($this->pd) {
2179             return array('pd' => PlSqlJoin::left('profile_display', '$ME.pid = $PID'));
2180         } else {
2181             return array();
2182         }
2183     }
2184
2185     /** LOGGER
2186      */
2187
2188     private $with_logger = false;
2189     public function addLoggerFilter()
2190     {
2191         $this->with_logger = true;
2192         $this->requireAccounts();
2193         return 'ls';
2194     }
2195     protected function loggerJoins()
2196     {
2197         $joins = array();
2198         if ($this->with_logger) {
2199             $joins['ls'] = PlSqlJoin::left('log_sessions', '$ME.uid = $UID');
2200         }
2201         return $joins;
2202     }
2203
2204     /** NAMES
2205      */
2206
2207     static public function assertName($name)
2208     {
2209         if (!DirEnum::getID(DirEnum::NAMETYPES, $name)) {
2210             Platal::page()->kill('Invalid name type: ' . $name);
2211         }
2212     }
2213
2214     private $pn  = array();
2215     public function addNameFilter($type, $variant = null)
2216     {
2217         $this->requireProfiles();
2218         if (!is_null($variant)) {
2219             $ft  = $type . '_' . $variant;
2220         } else {
2221             $ft = $type;
2222         }
2223         $sub = '_' . $ft;
2224         self::assertName($ft);
2225
2226         if (!is_null($variant) && $variant == 'other') {
2227             $sub .= $this->option++;
2228         }
2229         $this->pn[$sub] = DirEnum::getID(DirEnum::NAMETYPES, $ft);
2230         return $sub;
2231     }
2232
2233     protected function nameJoins()
2234     {
2235         $joins = array();
2236         foreach ($this->pn as $sub => $type) {
2237             $joins['pn' . $sub] = PlSqlJoin::left('profile_name', '$ME.pid = $PID AND $ME.typeid = {?}', $type);
2238         }
2239         return $joins;
2240     }
2241
2242     /** NAMETOKENS
2243      */
2244     private $name_tokens = array();
2245     private $nb_tokens = 0;
2246
2247     public function addNameTokensFilter($token)
2248     {
2249         $this->requireProfiles();
2250         $sub = 'sn' . (1 + $this->nb_tokens);
2251         $this->nb_tokens++;
2252         $this->name_tokens[$sub] = $token;
2253         return $sub;
2254     }
2255
2256     protected function nameTokensJoins()
2257     {
2258         /* We don't return joins, since with_sn forces the SELECT to run on search_name first */
2259         $joins = array();
2260         foreach ($this->name_tokens as $sub => $token) {
2261             $joins[$sub] = PlSqlJoin::left('search_name', '$ME.pid = $PID');
2262         }
2263         return $joins;
2264     }
2265
2266     public function getNameTokens()
2267     {
2268         return $this->name_tokens;
2269     }
2270
2271     /** NATIONALITY
2272      */
2273
2274     private $with_nat = false;
2275     public function addNationalityFilter()
2276     {
2277         $this->with_nat = true;
2278         return 'ngc';
2279     }
2280
2281     protected function nationalityJoins()
2282     {
2283         $joins = array();
2284         if ($this->with_nat) {
2285             $joins['ngc'] = PlSqlJoin::left('geoloc_countries', '$ME.iso_3166_1_a2 = p.nationality1 OR $ME.iso_3166_1_a2 = p.nationality2 OR $ME.iso_3166_1_a2 = p.nationality3');
2286         }
2287         return $joins;
2288     }
2289
2290     /** EDUCATION
2291      */
2292     const GRADE_ING = 'Ing.';
2293     const GRADE_PHD = 'PhD';
2294     const GRADE_MST = 'M%';
2295     static public function isGrade($grade)
2296     {
2297         return ($grade !== 0) && ($grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST);
2298     }
2299
2300     static public function assertGrade($grade)
2301     {
2302         if (!self::isGrade($grade)) {
2303             Platal::page()->killError("Diplôme non valide: $grade");
2304         }
2305     }
2306
2307     static public function promoYear($grade)
2308     {
2309         // XXX: Definition of promotion for phds and masters might change in near future.
2310         return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year';
2311     }
2312
2313     private $pepe     = array();
2314     private $with_pee = false;
2315     public function addEducationFilter($x = false, $grade = null)
2316     {
2317         $this->requireProfiles();
2318         if (!$x) {
2319             $index = $this->option;
2320             $sub   = $this->option++;
2321         } else {
2322             self::assertGrade($grade);
2323             $index = $grade;
2324             $sub   = $grade[0];
2325             $this->with_pee = true;
2326         }
2327         $sub = '_' . $sub;
2328         $this->pepe[$index] = $sub;
2329         return $sub;
2330     }
2331
2332     protected function educationJoins()
2333     {
2334         $joins = array();
2335         if ($this->with_pee) {
2336             $joins['pee'] = PlSqlJoin::inner('profile_education_enum', 'pee.abbreviation = \'X\'');
2337         }
2338         foreach ($this->pepe as $grade => $sub) {
2339             if ($this->isGrade($grade)) {
2340                 $joins['pe' . $sub] = PlSqlJoin::left('profile_education', '$ME.eduid = pee.id AND $ME.pid = $PID');
2341                 $joins['pede' . $sub] = PlSqlJoin::inner('profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.abbreviation LIKE {?}', $grade);
2342             } else {
2343                 $joins['pe' . $sub] = PlSqlJoin::left('profile_education', '$ME.pid = $PID');
2344                 $joins['pee' . $sub] = PlSqlJoin::inner('profile_education_enum', '$ME.id = pe' . $sub . '.eduid');
2345                 $joins['pede' . $sub] = PlSqlJoin::inner('profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid');
2346             }
2347         }
2348         return $joins;
2349     }
2350
2351
2352     /** GROUPS
2353      */
2354     private $gpm = array();
2355     public function addGroupFilter($group = null)
2356     {
2357         $this->requireAccounts();
2358         if (!is_null($group)) {
2359             if (is_int($group) || ctype_digit($group)) {
2360                 $index = $sub = $group;
2361             } else {
2362                 $index = $group;
2363                 $sub   = self::getDBSuffix($group);
2364             }
2365         } else {
2366             $sub = 'group_' . $this->option++;
2367             $index = null;
2368         }
2369         $sub = '_' . $sub;
2370         $this->gpm[$sub] = $index;
2371         return $sub;
2372     }
2373
2374     protected function groupJoins()
2375     {
2376         $joins = array();
2377         foreach ($this->gpm as $sub => $key) {
2378             if (is_null($key)) {
2379                 $joins['gpa' . $sub] = PlSqlJoin::inner('groups');
2380                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
2381             } else if (is_int($key) || ctype_digit($key)) {
2382                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = ' . $key);
2383             } else {
2384                 $joins['gpa' . $sub] = PlSqlJoin::inner('groups', '$ME.diminutif = {?}', $key);
2385                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
2386             }
2387         }
2388         return $joins;
2389     }
2390
2391     /** BINETS
2392      */
2393
2394     private $with_bi = false;
2395     private $with_bd = false;
2396     public function addBinetsFilter($with_enum = false)
2397     {
2398         $this->requireProfiles();
2399         $this->with_bi = true;
2400         if ($with_enum) {
2401             $this->with_bd = true;
2402             return 'bd';
2403         } else {
2404             return 'bi';
2405         }
2406     }
2407
2408     protected function binetsJoins()
2409     {
2410         $joins = array();
2411         if ($this->with_bi) {
2412             $joins['bi'] = PlSqlJoin::left('profile_binets', '$ME.pid = $PID');
2413         }
2414         if ($this->with_bd) {
2415             $joins['bd'] = PlSqlJoin::left('profile_binet_enum', '$ME.id = bi.binet_id');
2416         }
2417         return $joins;
2418     }
2419
2420     /** EMAILS
2421      */
2422     private $e = array();
2423     public function addEmailRedirectFilter($email = null)
2424     {
2425         $this->requireAccounts();
2426         return $this->register_optional($this->e, $email);
2427     }
2428
2429     private $ve = array();
2430     public function addVirtualEmailFilter($email = null)
2431     {
2432         $this->addAliasFilter(self::ALIAS_FORLIFE);
2433         return $this->register_optional($this->ve, $email);
2434     }
2435
2436     const ALIAS_BEST    = 'bestalias';
2437     const ALIAS_FORLIFE = 'forlife';
2438     private $al = array();
2439     public function addAliasFilter($alias = null)
2440     {
2441         $this->requireAccounts();
2442         return $this->register_optional($this->al, $alias);
2443     }
2444
2445     protected function emailJoins()
2446     {
2447         global $globals;
2448         $joins = array();
2449         foreach ($this->e as $sub=>$key) {
2450             if (is_null($key)) {
2451                 $joins['e' . $sub] = PlSqlJoin::left('emails', '$ME.uid = $UID AND $ME.flags != \'filter\'');
2452             } else {
2453                 if (!is_array($key)) {
2454                     $key = array($key);
2455                 }
2456                 $joins['e' . $sub] = PlSqlJoin::left('emails', '$ME.uid = $UID AND $ME.flags != \'filter\'
2457                                                                AND $ME.email IN {?}', $key);
2458             }
2459         }
2460         foreach ($this->al as $sub=>$key) {
2461             if (is_null($key)) {
2462                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\')');
2463             } else if ($key == self::ALIAS_BEST) {
2464                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND  FIND_IN_SET(\'bestalias\', $ME.flags)');
2465             } else if ($key == self::ALIAS_FORLIFE) {
2466                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type = \'a_vie\'');
2467             } else {
2468                 if (!is_array($key)) {
2469                     $key = array($key);
2470                 }
2471                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\')
2472                                                                   AND $ME.alias IN {?}', $key);
2473             }
2474         }
2475         foreach ($this->ve as $sub=>$key) {
2476             if (is_null($key)) {
2477                 $joins['v' . $sub] = PlSqlJoin::left('virtual', '$ME.type = \'user\'');
2478             } else {
2479                 if (!is_array($key)) {
2480                     $key = array($key);
2481                 }
2482                 $joins['v' . $sub] = PlSqlJoin::left('virtual', '$ME.type = \'user\' AND $ME.alias IN {?}', $key);
2483             }
2484             $joins['vr' . $sub] = PlSqlJoin::left('virtual_redirect',
2485                                                   '$ME.vid = v' . $sub . '.vid
2486                                                    AND ($ME.redirect IN (CONCAT(al_forlife.alias, \'@\', {?}),
2487                                                                          CONCAT(al_forlife.alias, \'@\', {?}),
2488                                                                          a.email))',
2489                                                   $globals->mail->domain, $globals->mail->domain2);
2490         }
2491         return $joins;
2492     }
2493
2494
2495     /** ADDRESSES
2496      */
2497     private $with_pa = false;
2498     public function addAddressFilter()
2499     {
2500         $this->requireProfiles();
2501         $this->with_pa = true;
2502         return 'pa';
2503     }
2504
2505     private $with_pac = false;
2506     public function addAddressCountryFilter()
2507     {
2508         $this->requireProfiles();
2509         $this->addAddressFilter();
2510         $this->with_pac = true;
2511         return 'gc';
2512     }
2513
2514     private $with_pal = false;
2515     public function addAddressLocalityFilter()
2516     {
2517         $this->requireProfiles();
2518         $this->addAddressFilter();
2519         $this->with_pal = true;
2520         return 'gl';
2521     }
2522
2523     protected function addressJoins()
2524     {
2525         $joins = array();
2526         if ($this->with_pa) {
2527             $joins['pa'] = PlSqlJoin::left('profile_addresses', '$ME.pid = $PID');
2528         }
2529         if ($this->with_pac) {
2530             $joins['gc'] = PlSqlJoin::left('geoloc_countries', '$ME.iso_3166_1_a2 = pa.countryID');
2531         }
2532         if ($this->with_pal) {
2533             $joins['gl'] = PlSqlJoin::left('geoloc_localities', '$ME.id = pa.localityID');
2534         }
2535         return $joins;
2536     }
2537
2538
2539     /** CORPS
2540      */
2541
2542     private $pc = false;
2543     private $pce = array();
2544     private $pcr = false;
2545     public function addCorpsFilter($type)
2546     {
2547         $this->requireProfiles();
2548         $this->pc = true;
2549         if ($type == UFC_Corps::CURRENT) {
2550             $pce['pcec'] = 'current_corpsid';
2551             return 'pcec';
2552         } else if ($type == UFC_Corps::ORIGIN) {
2553             $pce['pceo'] = 'original_corpsid';
2554             return 'pceo';
2555         }
2556     }
2557
2558     public function addCorpsRankFilter()
2559     {
2560         $this->requireProfiles();
2561         $this->pc = true;
2562         $this->pcr = true;
2563         return 'pcr';
2564     }
2565
2566     protected function corpsJoins()
2567     {
2568         $joins = array();
2569         if ($this->pc) {
2570             $joins['pc'] = PlSqlJoin::left('profile_corps', '$ME.pid = $PID');
2571         }
2572         if ($this->pcr) {
2573             $joins['pcr'] = PlSqlJoin::left('profile_corps_rank_enum', '$ME.id = pc.rankid');
2574         }
2575         foreach($this->pce as $sub => $field) {
2576             $joins[$sub] = PlSqlJoin::left('profile_corps_enum', '$ME.id = pc.' . $field);
2577         }
2578         return $joins;
2579     }
2580
2581     /** JOBS
2582      */
2583
2584     const JOB_USERDEFINED = 0x0001;
2585     const JOB_CV          = 0x0002;
2586     const JOB_ANY         = 0x0003;
2587
2588     /** Joins :
2589      * pj => profile_job
2590      * pje => profile_job_enum
2591      * pjt => profile_job_terms
2592      */
2593     private $with_pj  = false;
2594     private $with_pje = false;
2595     private $with_pjt = 0;
2596
2597     public function addJobFilter()
2598     {
2599         $this->requireProfiles();
2600         $this->with_pj = true;
2601         return 'pj';
2602     }
2603
2604     public function addJobCompanyFilter()
2605     {
2606         $this->addJobFilter();
2607         $this->with_pje = true;
2608         return 'pje';
2609     }
2610
2611     /**
2612      * Adds a filter on job terms of profile.
2613      * @param $nb the number of job terms to use
2614      * @return an array of the fields to filter (one for each term).
2615      */
2616     public function addJobTermsFilter($nb = 1)
2617     {
2618         $this->with_pjt = $nb;
2619         $jobtermstable = array();
2620         for ($i = 1; $i <= $nb; ++$i) {
2621             $jobtermstable[] = 'pjtr_'.$i;
2622         }
2623         return $jobtermstable;
2624     }
2625
2626     protected function jobJoins()
2627     {
2628         $joins = array();
2629         if ($this->with_pj) {
2630             $joins['pj'] = PlSqlJoin::left('profile_job', '$ME.pid = $PID');
2631         }
2632         if ($this->with_pje) {
2633             $joins['pje'] = PlSqlJoin::left('profile_job_enum', '$ME.id = pj.jobid');
2634         }
2635         if ($this->with_pjt > 0) {
2636             for ($i = 1; $i <= $this->with_pjt; ++$i) {
2637                 $joins['pjt_'.$i] = PlSqlJoin::left('profile_job_term', '$ME.pid = $PID');
2638                 $joins['pjtr_'.$i] = PlSqlJoin::left('profile_job_term_relation', '$ME.jtid_2 = pjt_'.$i.'.jtid');
2639             }
2640         }
2641         return $joins;
2642     }
2643
2644     /** NETWORKING
2645      */
2646
2647     private $with_pnw = false;
2648     public function addNetworkingFilter()
2649     {
2650         $this->requireAccounts();
2651         $this->with_pnw = true;
2652         return 'pnw';
2653     }
2654
2655     protected function networkingJoins()
2656     {
2657         $joins = array();
2658         if ($this->with_pnw) {
2659             $joins['pnw'] = PlSqlJoin::left('profile_networking', '$ME.pid = $PID');
2660         }
2661         return $joins;
2662     }
2663
2664     /** PHONE
2665      */
2666
2667     private $with_ptel = false;
2668
2669     public function addPhoneFilter()
2670     {
2671         $this->requireAccounts();
2672         $this->with_ptel = true;
2673         return 'ptel';
2674     }
2675
2676     protected function phoneJoins()
2677     {
2678         $joins = array();
2679         if ($this->with_ptel) {
2680             $joins['ptel'] = PlSqlJoin::left('profile_phones', '$ME.pid = $PID');
2681         }
2682         return $joins;
2683     }
2684
2685     /** MEDALS
2686      */
2687
2688     private $with_pmed = false;
2689     public function addMedalFilter()
2690     {
2691         $this->requireProfiles();
2692         $this->with_pmed = true;
2693         return 'pmed';
2694     }
2695
2696     protected function medalJoins()
2697     {
2698         $joins = array();
2699         if ($this->with_pmed) {
2700             $joins['pmed'] = PlSqlJoin::left('profile_medals', '$ME.pid = $PID');
2701         }
2702         return $joins;
2703     }
2704
2705     /** MENTORING
2706      */
2707
2708     private $pms = array();
2709     private $mjtr = false;
2710     const MENTOR = 1;
2711     const MENTOR_EXPERTISE = 2;
2712     const MENTOR_COUNTRY = 3;
2713     const MENTOR_TERM = 4;
2714
2715     public function addMentorFilter($type)
2716     {
2717         $this->requireAccounts();
2718         switch($type) {
2719         case self::MENTOR:
2720             $this->pms['pm'] = 'profile_mentor';
2721             return 'pm';
2722         case self::MENTOR_EXPERTISE:
2723             $this->pms['pme'] = 'profile_mentor';
2724             return 'pme';
2725         case self::MENTOR_COUNTRY:
2726             $this->pms['pmc'] = 'profile_mentor_country';
2727             return 'pmc';
2728         case self::MENTOR_TERM:
2729             $this->pms['pmt'] = 'profile_mentor_term';
2730             $this->mjtr = true;
2731             return 'mjtr';
2732         default:
2733             Platal::page()->killError("Undefined mentor filter.");
2734         }
2735     }
2736
2737     protected function mentorJoins()
2738     {
2739         $joins = array();
2740         foreach ($this->pms as $sub => $tab) {
2741             $joins[$sub] = PlSqlJoin::left($tab, '$ME.pid = $PID');
2742         }
2743         if ($this->mjtr) {
2744             $joins['mjtr'] = PlSqlJoin::left('profile_job_term_relation', '$ME.jtid_2 = pmt.jtid');
2745         }
2746         return $joins;
2747     }
2748
2749     /** CONTACTS
2750      */
2751     private $cts = array();
2752     public function addContactFilter($uid = null)
2753     {
2754         $this->requireProfiles();
2755         return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid);
2756     }
2757
2758     protected function contactJoins()
2759     {
2760         $joins = array();
2761         foreach ($this->cts as $sub=>$key) {
2762             if (is_null($key)) {
2763                 $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.contact = $PID');
2764             } else {
2765                 $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.uid = {?} AND $ME.contact = $PID', substr($key, 5));
2766             }
2767         }
2768         return $joins;
2769     }
2770
2771
2772     /** CARNET
2773      */
2774     private $wn = array();
2775     public function addWatchRegistrationFilter($uid = null)
2776     {
2777         $this->requireAccounts();
2778         return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid);
2779     }
2780
2781     private $wp = array();
2782     public function addWatchPromoFilter($uid = null)
2783     {
2784         $this->requireAccounts();
2785         return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid);
2786     }
2787
2788     private $w = array();
2789     public function addWatchFilter($uid = null)
2790     {
2791         $this->requireAccounts();
2792         return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid);
2793     }
2794
2795     protected function watchJoins()
2796     {
2797         $joins = array();
2798         foreach ($this->w as $sub=>$key) {
2799             if (is_null($key)) {
2800                 $joins['w' . $sub] = PlSqlJoin::left('watch');
2801             } else {
2802                 $joins['w' . $sub] = PlSqlJoin::left('watch', '$ME.uid = {?}', substr($key, 5));
2803             }
2804         }
2805         foreach ($this->wn as $sub=>$key) {
2806             if (is_null($key)) {
2807                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID');
2808             } else {
2809                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5));
2810             }
2811         }
2812         foreach ($this->wn as $sub=>$key) {
2813             if (is_null($key)) {
2814                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID');
2815             } else {
2816                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5));
2817             }
2818         }
2819         foreach ($this->wp as $sub=>$key) {
2820             if (is_null($key)) {
2821                 $joins['wp' . $sub] = PlSqlJoin::left('watch_promo');
2822             } else {
2823                 $joins['wp' . $sub] = PlSqlJoin::left('watch_promo', '$ME.uid = {?}', substr($key, 5));
2824             }
2825         }
2826         return $joins;
2827     }
2828
2829
2830     /** PHOTOS
2831      */
2832     private $with_photo;
2833     public function addPhotoFilter()
2834     {
2835         $this->requireProfiles();
2836         $this->with_photo = true;
2837         return 'photo';
2838     }
2839
2840     protected function photoJoins()
2841     {
2842         if ($this->with_photo) {
2843             return array('photo' => PlSqlJoin::left('profile_photos', '$ME.pid = $PID'));
2844         } else {
2845             return array();
2846         }
2847     }
2848
2849
2850     /** MARKETING
2851      */
2852     private $with_rm;
2853     public function addMarketingHash()
2854     {
2855         $this->requireAccounts();
2856         $this->with_rm = true;
2857     }
2858
2859     protected function marketingJoins()
2860     {
2861         if ($this->with_rm) {
2862             return array('rm' => PlSqlJoin::left('register_marketing', '$ME.uid = $UID'));
2863         } else {
2864             return array();
2865         }
2866     }
2867 }
2868 // }}}
2869
2870 // {{{ class ProfileFilter
2871 class ProfileFilter extends UserFilter
2872 {
2873     public function get($limit = null)
2874     {
2875         return $this->getProfiles($limit);
2876     }
2877
2878     public function getIds($limit = null)
2879     {
2880         return $this->getPIDs();
2881     }
2882
2883     public function filter(array $profiles, $limit = null)
2884     {
2885         return $this->filterProfiles($profiles, self::defaultLimit($limit));
2886     }
2887
2888     public function getTotalCount()
2889     {
2890         return $this->getTotalProfileCount();
2891     }
2892
2893     public function getGroups()
2894     {
2895         return $this->getPIDGroups();
2896     }
2897 }
2898 // }}}
2899
2900 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
2901 ?>