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