classes/userfilter.php

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