classes/userfilter.php

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