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