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
  38 {
  39     /** Check that the given user matches the rule.
  40      */
  41     public function buildCondition(UserFilter &$uf);
  42 }
  43 // }}}
  44
  45 // {{{ class UFC_Profile
  46 /** Filters users who have a profile
  47  */
  48 class UFC_Profile implements UserFilterCondition
  49 {
  50     public function buildCondition(UserFilter &$uf)
  51     {
  52         return '$PID IS NOT NULL';
  53     }
  54 }
  55 // }}}
  56
  57 // {{{ class UFC_Promo
  58 /** Filters users based on promotion
  59  * @param $comparison Comparison operator (>, =, ...)
  60  * @param $grade Formation on which to restrict, UserFilter::DISPLAY for "any formation"
  61  * @param $promo Promotion on which the filter is based
  62  */
  63 class UFC_Promo implements UserFilterCondition
  64 {
  65
  66     private $grade;
  67     private $promo;
  68     private $comparison;
  69
  70     public function __construct($comparison, $grade, $promo)
  71     {
  72         $this->grade = $grade;
  73         $this->comparison = $comparison;
  74         $this->promo = $promo;
  75         if ($this->grade != UserFilter::DISPLAY) {
  76             UserFilter::assertGrade($this->grade);
  77         }
  78     }
  79
  80     public function buildCondition(UserFilter &$uf)
  81     {
  82         if ($this->grade == UserFilter::DISPLAY) {
  83             $sub = $uf->addDisplayFilter();
  84             return XDB::format('pd' . $sub . '.promo ' . $this->comparison . ' {?}', $this->promo);
  85         } else {
  86             $sub = $uf->addEducationFilter(true, $this->grade);
  87             $field = 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
  88             return $field . ' IS NOT NULL AND ' . $field . ' ' . $this->comparison . ' ' . XDB::format('{?}', $this->promo);
  89         }
  90     }
  91 }
  92 // }}}
  93
  94 // {{{ class UFC_Name
  95 /** Filters users based on name
  96  * @param $type Type of name field on which filtering is done (firstname, lastname...)
  97  * @param $text Text on which to filter
  98  * @param $mode Flag indicating search type (prefix, suffix, with particule...)
  99  */
 100 class UFC_Name implements UserFilterCondition
 101 {
 102     const PREFIX   = 1;
 103     const SUFFIX   = 2;
 104     const PARTICLE = 7;
 105     const VARIANTS = 8;
 106     const CONTAINS = 3;
 107
 108     private $type;
 109     private $text;
 110     private $mode;
 111
 112     public function __construct($type, $text, $mode)
 113     {
 114         $this->type = $type;
 115         $this->text = $text;
 116         $this->mode = $mode;
 117     }
 118
 119     private function buildNameQuery($type, $variant, $where, UserFilter &$uf)
 120     {
 121         $sub = $uf->addNameFilter($type, $variant);
 122         return str_replace('$ME', 'pn' . $sub, $where);
 123     }
 124
 125     public function buildCondition(UserFilter &$uf)
 126     {
 127         $left = '$ME.name';
 128         $op   = ' LIKE ';
 129         if (($this->mode & self::PARTICLE) == self::PARTICLE) {
 130             $left = 'CONCAT($ME.particle, \' \', $ME.name)';
 131         }
 132         if (($this->mode & self::CONTAINS) == 0) {
 133             $right = XDB::format('{?}', $this->text);
 134             $op    = ' = ';
 135         } else if (($this->mode & self::CONTAINS) == self::PREFIX) {
 136             $right = XDB::format('CONCAT({?}, \'%\')', $this->text);
 137         } else if (($this->mode & self::CONTAINS) == self::SUFFIX) {
 138             $right = XDB::format('CONCAT(\'%\', {?})', $this->text);
 139         } else {
 140             $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text);
 141         }
 142         $cond = $left . $op . $right;
 143         $conds = array($this->buildNameQuery($this->type, null, $cond, $uf));
 144         if (($this->mode & self::VARIANTS) != 0 && isset(UserFilter::$name_variants[$this->type])) {
 145             foreach (UserFilter::$name_variants[$this->type] as $var) {
 146                 $conds[] = $this->buildNameQuery($this->type, $var, $cond, $uf);
 147             }
 148         }
 149         return implode(' OR ', $conds);
 150     }
 151 }
 152 // }}}
 153
 154 // {{{ class UFC_Dead
 155 /** Filters users based on death date
 156  * @param $comparison Comparison operator
 157  * @param $date Date to which death date should be compared
 158  */
 159 class UFC_Dead implements UserFilterCondition
 160 {
 161     private $comparison;
 162     private $date;
 163
 164     public function __construct($comparison = null, $date = null)
 165     {
 166         $this->comparison = $comparison;
 167         $this->date = $date;
 168     }
 169
 170     public function buildCondition(UserFilter &$uf)
 171     {
 172         $str = 'p.deathdate IS NOT NULL';
 173         if (!is_null($this->comparison)) {
 174             $str .= ' AND p.deathdate ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date));
 175         }
 176         return $str;
 177     }
 178 }
 179 // }}}
 180
 181 // {{{ class UFC_Registered
 182 /** Filters users based on registration state
 183  * @param $active Whether we want to use only "active" users (i.e with a valid redirection)
 184  * @param $comparison Comparison operator
 185  * @param $date Date to which users registration date should be compared
 186  */
 187 class UFC_Registered implements UserFilterCondition
 188 {
 189     private $active;
 190     private $comparison;
 191     private $date;
 192
 193     public function __construct($active = false, $comparison = null, $date = null)
 194     {
 195         $this->active = $active;
 196         $this->comparison = $comparison;
 197         $this->date = $date;
 198     }
 199
 200     public function buildCondition(UserFilter &$uf)
 201     {
 202         if ($this->active) {
 203             $date = 'a.uid IS NOT NULL AND a.state = \'active\'';
 204         } else {
 205             $date = 'a.uid IS NOT NULL AND a.state != \'pending\'';
 206         }
 207         if (!is_null($this->comparison)) {
 208             $date .= ' AND a.registration_date ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date));
 209         }
 210         return $date;
 211     }
 212 }
 213 // }}}
 214
 215 // {{{ class UFC_ProfileUpdated
 216 /** Filters users based on profile update date
 217  * @param $comparison Comparison operator
 218  * @param $date Date to which profile update date must be compared
 219  */
 220 class UFC_ProfileUpdated implements UserFilterCondition
 221 {
 222     private $comparison;
 223     private $date;
 224
 225     public function __construct($comparison = null, $date = null)
 226     {
 227         $this->comparison = $comparison;
 228         $this->date = $date;
 229     }
 230
 231     public function buildCondition(UserFilter &$uf)
 232     {
 233         return 'p.last_change ' . $this->comparison . XDB::format(' {?}', date('Y-m-d H:i:s', $this->date));
 234     }
 235 }
 236 // }}}
 237
 238 // {{{ class UFC_Birthday
 239 /** Filters users based on next birthday date
 240  * @param $comparison Comparison operator
 241  * @param $date Date to which users next birthday date should be compared
 242  */
 243 class UFC_Birthday implements UserFilterCondition
 244 {
 245     private $comparison;
 246     private $date;
 247
 248     public function __construct($comparison = null, $date = null)
 249     {
 250         $this->comparison = $comparison;
 251         $this->date = $date;
 252     }
 253
 254     public function buildCondition(UserFilter &$uf)
 255     {
 256         return 'p.next_birthday ' . $this->comparison . XDB::format(' {?}', date('Y-m-d', $this->date));
 257     }
 258 }
 259 // }}}
 260
 261 // {{{ class UFC_Sex
 262 /** Filters users based on sex
 263  * @parm $sex One of User::GENDER_MALE or User::GENDER_FEMALE, for selecting users
 264  */
 265 class UFC_Sex implements UserFilterCondition
 266 {
 267     private $sex;
 268     public function __construct($sex)
 269     {
 270         $this->sex = $sex;
 271     }
 272
 273     public function buildCondition(UserFilter &$uf)
 274     {
 275         if ($this->sex != User::GENDER_MALE && $this->sex != User::GENDER_FEMALE) {
 276             return self::COND_FALSE;
 277         } else {
 278             return XDB::format('p.sex = {?}', $this->sex == User::GENDER_FEMALE ? 'female' : 'male');
 279         }
 280     }
 281 }
 282 // }}}
 283
 284 // {{{ class UFC_Group
 285 /** Filters users based on group membership
 286  * @param $group Group whose members we are selecting
 287  * @param $anim Whether to restrict selection to animators of that group
 288  */
 289 class UFC_Group implements UserFilterCondition
 290 {
 291     private $group;
 292     private $anim;
 293     public function __construct($group, $anim = false)
 294     {
 295         $this->group = $group;
 296         $this->anim = $anim;
 297     }
 298
 299     public function buildCondition(UserFilter &$uf)
 300     {
 301         $sub = $uf->addGroupFilter($this->group);
 302         $where = 'gpm' . $sub . '.perms IS NOT NULL';
 303         if ($this->anim) {
 304             $where .= ' AND gpm' . $sub . '.perms = \'admin\'';
 305         }
 306         return $where;
 307     }
 308 }
 309 // }}}
 310
 311 // {{{ class UFC_Email
 312 /** Filters users based on email address
 313  * @param $email Email whose owner we are looking for
 314  */
 315 class UFC_Email implements UserFilterCondition
 316 {
 317     private $email;
 318     public function __construct($email)
 319     {
 320         $this->email = $email;
 321     }
 322
 323     public function buildCondition(UserFilter &$uf)
 324     {
 325         if (User::isForeignEmailAddress($this->email)) {
 326             $sub = $uf->addEmailRedirectFilter($this->email);
 327             return XDB::format('e' . $sub . '.email IS NOT NULL OR a.email = {?}', $this->email);
 328         } else if (User::isVirtualEmailAddress($this->email)) {
 329             $sub = $uf->addVirtualEmailFilter($this->email);
 330             return 'vr' . $sub . '.redirect IS NOT NULL';
 331         } else {
 332             @list($user, $domain) = explode('@', $this->email);
 333             $sub = $uf->addAliasFilter($user);
 334             return 'al' . $sub . '.alias IS NOT NULL';
 335         }
 336     }
 337 }
 338 // }}}
 339
 340 // {{{ class UFC_EmailList
 341 /** Filters users based on an email list
 342  * @param $emails List of emails whose owner must be selected
 343  */
 344 class UFC_EmailList implements UserFilterCondition
 345 {
 346     private $emails;
 347     public function __construct($emails)
 348     {
 349         $this->emails = $emails;
 350     }
 351
 352     public function buildCondition(UserFilter &$uf)
 353     {
 354         $email   = null;
 355         $virtual = null;
 356         $alias   = null;
 357         $cond = array();
 358
 359         if (count($this->emails) == 0) {
 360             return UserFilterCondition::COND_TRUE;
 361         }
 362
 363         foreach ($this->emails as $entry) {
 364             if (User::isForeignEmailAddress($entry)) {
 365                 if (is_null($email)) {
 366                     $email = $uf->addEmailRedirectFilter();
 367                 }
 368                 $cond[] = XDB::format('e' . $email . '.email = {?} OR a.email = {?}', $entry, $entry);
 369             } else if (User::isVirtualEmailAddress($entry)) {
 370                 if (is_null($virtual)) {
 371                     $virtual = $uf->addVirtualEmailFilter();
 372                 }
 373                 $cond[] = XDB::format('vr' . $virtual . '.redirect IS NOT NULL AND v' . $virtual . '.alias = {?}', $entry);
 374             } else {
 375                 if (is_null($alias)) {
 376                     $alias = $uf->addAliasFilter();
 377                 }
 378                 @list($user, $domain) = explode('@', $entry);
 379                 $cond[] = XDB::format('al' . $alias . '.alias = {?}', $user);
 380             }
 381         }
 382         return '(' . implode(') OR (', $cond) . ')';
 383     }
 384 }
 385 // }}}
 386
 387 // {{{ class UFC_Address
 388 /** Filters users based on their address
 389  * @param $text Text for filter in fulltext search
 390  * @param $textSearchMode Mode for search (PREFIX, SUFFIX, ...)
 391  * @param $type Filter on address type
 392  * @param $flags Filter on address flags
 393  * @param $countryId Filter on address countryId
 394  * @param $administrativeAreaId Filter on address administrativeAreaId
 395  * @param $subAdministrativeAreaId Filter on address subAdministrativeAreaId
 396  * @param $localityId Filter on address localityId
 397  * @param $postalCode Filter on address postalCode
 398  */
 399 class UFC_Address implements UserFilterCondition
 400 {
 401     /** Flags for text search
 402      */
 403     const PREFIX    = 0x0001;
 404     const SUFFIX    = 0x0002;
 405     const CONTAINS  = 0x0003;
 406
 407     /** Valid address type ('hq' is reserved for company addresses)
 408      */
 409     const TYPE_HOME = 'home';
 410     const TYPE_PRO  = 'job';
 411
 412     /** Flags for addresses
 413      */
 414     const FLAG_CURRENT = 0x0001;
 415     const FLAG_TEMP    = 0x0002;
 416     const FLAG_SECOND  = 0x0004;
 417     const FLAG_MAIL    = 0x0008;
 418     const FLAG_CEDEX   = 0x0010;
 419
 420     // Binary OR of those flags
 421     const FLAG_ANY     = 0x001F;
 422
 423     /** Text of these flags
 424      */
 425     private static $flagtexts = array(
 426         self::FLAG_CURRENT => 'current',
 427         self::FLAG_TEMP    => 'temporary',
 428         self::FLAG_SECOND  => 'secondary',
 429         self::FLAG_MAIL    => 'mail',
 430         self::FLAG_CEDEX   => 'cedex',
 431     );
 432
 433     /** Data of the filter
 434      */
 435     private $text;
 436     private $type;
 437     private $flags;
 438     private $countryId;
 439     private $administrativeAreaId;
 440     private $subAdministrativeAreaId;
 441     private $localityId;
 442     private $postalCode;
 443
 444     private $textSearchMode;
 445
 446     public function __construct($text = null, $textSearchMode = self::CONTAINS,
 447         $type = null, $flags = self::FLAG_ANY, $countryId = null, $administrativeAreaId = null,
 448         $subAdministrativeAreaId = null, $localityId = null, $postalCode = null)
 449     {
 450         $this->text                      = $text;
 451         $this->textSearchMode            = $textSearchMode;
 452         $this->type                      = $type;
 453         $this->flags                     = $flags;
 454         $this->countryId                 = $countryId;
 455         $this->administrativeAreaId      = $administrativeAreaId;
 456         $this->subAdministrativeAreaId   = $subAdministrativeAreaId;
 457         $this->localityId                = $localityId;
 458         $this->postalCode                = $postalCode;
 459     }
 460
 461     public function buildCondition(UserFilter &$uf)
 462     {
 463         $sub = $uf->addAddressFilter();
 464         $conds = array();
 465         if ($this->text != null) {
 466             $left = $sub . '.text ';
 467             $op = ' LIKE ';
 468             if (($this->textSearchMode & self::CONTAINS) == 0) {
 469                 $right = XDB::format('{?}', $this->text);
 470                 $op = ' = ';
 471             } else if (($this->mode & self::CONTAINS) == self::PREFIX) {
 472                 $right = XDB::format('CONCAT({?}, \'%\')', $this->text);
 473             } else if (($this->mode & self::CONTAINS) == self::SUFFIX) {
 474                 $right = XDB::format('CONCAT(\'%\', {?})', $this->text);
 475             } else {
 476                 $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text);
 477             }
 478             $conds[] = $left . $op . $right;
 479         }
 480
 481         if ($this->type != null) {
 482             $conds[] = $sub . '.type = ' . XDB::format('{?}', $this->type);
 483         }
 484
 485         if ($this->flags != self::FLAG_ANY) {
 486             foreach(self::$flagtexts as $flag => $text) {
 487                 if ($flag & $this->flags) {
 488                     $conds[] = 'FIND_IN_SET(' . XDB::format('{?}', $text) . ', ' . $sub . '.flags)';
 489                 }
 490             }
 491         }
 492
 493         if ($this->countryId != null) {
 494             $conds[] = $sub . '.countryId = ' . XDB::format('{?}', $this->countryId);
 495         }
 496         if ($this->administrativeAreaId != null) {
 497             $conds[] = $sub . '.administrativeAreaId = ' . XDB::format('{?}', $this->administrativeAreaId);
 498         }
 499         if ($this->subAdministrativeAreaId != null) {
 500             $conds[] = $sub . '.subAdministrativeAreaId = ' . XDB::format('{?}', $this->subAdministrativeAreaId);
 501         }
 502         if ($this->localityId != null) {
 503             $conds[] = $sub . '.localityId = ' . XDB::format('{?}', $this->localityId);
 504         }
 505         if ($this->postalCode != null) {
 506             $conds[] = $sub . '.postalCode = ' . XDB::format('{?}', $this->postalCode);
 507         }
 508
 509         return implode(' AND ', $conds);
 510     }
 511 }
 512 // }}}
 513
 514 // {{{ class UFC_Corps
 515 /** Filters users based on the corps they belong to
 516  * @param $corps Corps we are looking for (abbreviation)
 517  * @param $type Whether we search for original or current corps
 518  */
 519 class UFC_Corps implements UserFilterCondition
 520 {
 521     const CURRENT   = 1;
 522     const ORIGIN    = 2;
 523
 524     private $corps;
 525     private $type;
 526
 527     public function __construct($corps, $type = self::CURRENT)
 528     {
 529         $this->corps = $corps;
 530         $this->type  = $type;
 531     }
 532
 533     public function buildCondition(UserFilter &$uf)
 534     {
 535         /** Tables shortcuts:
 536          * pc for profile_corps,
 537          * pceo for profile_corps_enum - orginal
 538          * pcec for profile_corps_enum - current
 539          */
 540         $sub = $uf->addCorpsFilter($this->type);
 541         $cond = $sub . '.abbreviation = ' . $corps;
 542         return $cond;
 543     }
 544 }
 545 // }}}
 546
 547 // {{{ class UFC_Corps_Rank
 548 /** Filters users based on their rank in the corps
 549  * @param $rank Rank we are looking for (abbreviation)
 550  */
 551 class UFC_Corps_Rank implements UserFilterCondition
 552 {
 553     private $rank;
 554     public function __construct($rank)
 555     {
 556         $this->rank = $rank;
 557     }
 558
 559     public function buildCondition(UserFilter &$uf)
 560     {
 561         /** Tables shortcuts:
 562          * pcr for profile_corps_rank
 563          */
 564         $sub = $uf->addCorpsRankFilter();
 565         $cond = $sub . '.abbreviation = ' . $rank;
 566         return $cond;
 567     }
 568 }
 569 // }}}
 570
 571 // {{{ class UFC_Job_Company
 572 /** Filters users based on the company they belong to
 573  * @param $type The field being searched (self::JOBID, self::JOBNAME or self::JOBACRONYM)
 574  * @param $value The searched value
 575  */
 576 class UFC_Job_Company implements UserFilterCondition
 577 {
 578     const JOBID = 'id';
 579     const JOBNAME = 'name';
 580     const JOBACRONYM = 'acronym';
 581
 582     private $type;
 583     private $value;
 584
 585     public function __construct($type, $value)
 586     {
 587         $this->assertType($type);
 588         $this->type = $type;
 589         $this->value = $value;
 590     }
 591
 592     private function assertType($type)
 593     {
 594         if ($type != self::JOBID && $type != self::JOBNAME && $type != self::JOBACRONYM) {
 595             Platal::page()->killError("Type de recherche non valide.");
 596         }
 597     }
 598
 599     public function buildCondition(UserFilter &$uf)
 600     {
 601         $sub = $uf->addJobCompanyFilter();
 602         $cond  = $sub . '.' . $this->type . ' = ' . XDB::format('{?}', $this->value);
 603         return $cond;
 604     }
 605 }
 606 // }}}
 607
 608 // {{{ class UFC_Job_Sectorization
 609 /** Filters users based on the ((sub)sub)sector they work in
 610  * @param $sector The sector searched
 611  * @param $subsector The subsector
 612  * @param $subsubsector The subsubsector
 613  */
 614 class UFC_Job_Sectorization implements UserFilterCondition
 615 {
 616
 617     private $sector;
 618     private $subsector;
 619     private $subsubsector;
 620
 621     public function __construct($sector = null, $subsector = null, $subsubsector = null)
 622     {
 623         $this->sector = $sector;
 624         $this->subsector = $subsector;
 625         $this->subsubsector = $subsubsector;
 626     }
 627
 628     public function buildCondition(UserFilter &$uf)
 629     {
 630         // No need to add the JobFilter, it will be done by addJobSectorizationFilter
 631         $conds = array();
 632         if ($this->sector !== null) {
 633             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SECTOR);
 634             $conds[] = $sub . '.id = ' . XDB::format('{?}', $this->sector);
 635         }
 636         if ($this->subsector !== null) {
 637             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSECTOR);
 638             $conds[] = $sub . '.id = ' . XDB::format('{?}', $this->subsector);
 639         }
 640         if ($this->subsubsector !== null) {
 641             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSUBSECTOR);
 642             $conds[] = $sub . '.id = ' . XDB::format('{?}', $this->subsubsector);
 643         }
 644         return implode(' AND ', $conds);
 645     }
 646 }
 647 // }}}
 648
 649 // {{{ class UFC_Job_Description
 650 /** Filters users based on their job description
 651  * @param $description The text being searched for
 652  * @param $fields The fields to search for (user-defined, ((sub|)sub|)sector)
 653  */
 654 class UFC_Job_Description implements UserFilterCondition
 655 {
 656
 657     /** Meta-filters
 658      * Built with binary OR on UserFilter::JOB_*
 659      */
 660     const ANY = 31;
 661     const SECTORIZATION = 15;
 662
 663     private $description;
 664     private $fields;
 665
 666     public function __construct($description)
 667     {
 668         $this->fields = $fields;
 669         $this->description = $description;
 670     }
 671
 672     public function buildCondition(UserFilter &$uf)
 673     {
 674         $conds = array();
 675         if ($this->fields & UserFilter::JOB_USERDEFINED) {
 676             $sub = $uf->addJobFilter();
 677             $conds[] = $sub . '.description LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
 678         }
 679         if ($this->fields & UserFilter::JOB_SECTOR) {
 680             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SECTOR);
 681             $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
 682         }
 683         if ($this->fields & UserFilter::JOB_SUBSECTOR) {
 684             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSECTOR);
 685             $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
 686         }
 687         if ($this->fields & UserFilter::JOB_SUBSUBSECTOR) {
 688             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSUBSECTOR);
 689             $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
 690             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_ALTERNATES);
 691             $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description);
 692         }
 693         return implode(' OR ', $conds);
 694     }
 695 }
 696 // }}}
 697
 698 // {{{ class UFC_Networking
 699 /** Filters users based on network identity (IRC, ...)
 700  * @param $type Type of network (-1 for any)
 701  * @param $value Value to search
 702  */
 703 class UFC_Networking implements UserFilterCondition
 704 {
 705     private $type;
 706     private $value;
 707
 708     public function __construct($type, $value)
 709     {
 710         $this->type = $type;
 711         $this->value = $value;
 712     }
 713
 714     public function buildCondition(UserFilter &$uf)
 715     {
 716         $sub = $uf->addNetworkingFilter();
 717         $conds = array();
 718         $conds[] = $sub . '.address = ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->value);
 719         if ($this->type != -1) {
 720             $conds[] = $sub . '.network_type = ' . XDB::format('{?}', $this->type);
 721         }
 722         return implode(' AND ', $conds);
 723     }
 724 }
 725 // }}}
 726
 727 // {{{ class UFC_Phone
 728 /** Filters users based on their phone number
 729  * @param $num_type Type of number (pro/user/home)
 730  * @param $phone_type Type of phone (fixed/mobile/fax)
 731  * @param $number Phone number
 732  */
 733 class UFC_Phone implements UserFilterCondition
 734 {
 735     const NUM_PRO   = 'pro';
 736     const NUM_USER  = 'user';
 737     const NUM_HOME  = 'address';
 738     const NUM_ANY   = 'any';
 739
 740     const PHONE_FIXED   = 'fixed';
 741     const PHONE_MOBILE  = 'mobile';
 742     const PHONE_FAX     = 'fax';
 743     const PHONE_ANY     = 'any';
 744
 745     private $num_type;
 746     private $phone_type;
 747     private $number;
 748
 749     public function __construct($number, $num_type = self::NUM_ANY, $phone_type = self::PHONE_ANY)
 750     {
 751         require_once('profil.func.inc.php');
 752         $this->number = $number;
 753         $this->num_type = $num_type;
 754         $this->phone_type = format_phone_number($phone_type);
 755     }
 756
 757     public function buildCondition(UserFilter &$uf)
 758     {
 759         $sub = $uf->addPhoneFilter();
 760         $conds = array();
 761         $conds[] = $sub . '.search_tel = ' . XDB::format('{?}', $this->number);
 762         if ($this->num_type != self::NUM_ANY) {
 763             $conds[] = $sub . '.link_type = ' . XDB::format('{?}', $this->num_type);
 764         }
 765         if ($this->phone_type != self::PHONE_ANY) {
 766             $conds[] = $sub . '.tel_type = ' . XDB::format('{?}', $this->phone_type);
 767         }
 768         return implode(' AND ', $conds);
 769     }
 770 }
 771 // }}}
 772
 773 // {{{ class UFC_Medal
 774 /** Filters users based on their medals
 775  * @param $medal ID of the medal
 776  * @param $grade Grade of the medal (null for 'any')
 777  */
 778 class UFC_Medal implements UserFilterCondition
 779 {
 780     private $medal;
 781     private $grade;
 782
 783     public function __construct($medal, $grade = null)
 784     {
 785         $this->medal = $medal;
 786         $this->grade = $grade;
 787     }
 788
 789     public function buildCondition(UserFilter &$uf)
 790     {
 791         $conds = array();
 792         $sub = $uf->addMedalFilter();
 793         $conds[] = $sub . '.mid = ' . XDB::format('{?}', $this->medal);
 794         if ($this->grade != null) {
 795             $conds[] = $sub . '.gid = ' . XDB::format('{?}', $this->grade);
 796         }
 797         return implode(' AND ', $conds);
 798     }
 799 }
 800 // }}}
 801
 802 // {{{ class UFC_Mentor_Expertise
 803 /** Filters users by mentoring expertise
 804  * @param $expertise Domain of expertise
 805  */
 806 class UFC_Mentor_Expertise implements UserFilterCondition
 807 {
 808     private $expertise;
 809
 810     public function __construct($expertise)
 811     {
 812         $this->expertise = $expertise;
 813     }
 814
 815     public function buildCondition(UserFilter &$uf)
 816     {
 817         $sub = $uf->addMentorFilter(UserFilter::MENTOR_EXPERTISE);
 818         return $sub . '.expertise LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\'', $this->expertise);
 819     }
 820 }
 821 // }}}
 822
 823 // {{{ class UFC_Mentor_Country
 824 /** Filters users by mentoring country
 825  * @param $country Two-letters code of country being searched
 826  */
 827 class UFC_Mentor_Country implements UserFilterCondition
 828 {
 829     private $country;
 830
 831     public function __construct($country)
 832     {
 833         $this->country = $country;
 834     }
 835
 836     public function buildCondition(UserFilter &$uf)
 837     {
 838         $sub = $uf->addMentorFilter(UserFilter::MENTOR_COUNTRY);
 839         return $sub . '.country = ' . XDB::format('{?}', $this->country);
 840     }
 841 }
 842 // }}}
 843
 844 // {{{ class UFC_Mentor_Sectorization
 845 /** Filters users based on mentoring (sub|)sector
 846  * @param $sector ID of sector
 847  * @param $subsector Subsector (null for any)
 848  */
 849 class UFC_Mentor_Sectorization implements UserFilterCondition
 850 {
 851     private $sector;
 852     private $subsector;
 853
 854     public function __construct($sector, $subsector = null)
 855     {
 856         $this->sector = $sector;
 857         $this->subsubsector = $subsector;
 858     }
 859
 860     public function buildCondition(UserFilter &$uf)
 861     {
 862         $sub = $uf->addMentorFilter(UserFilter::MENTOR_SECTOR);
 863         $conds = array();
 864         $conds[] = $sub . '.sectorid = ' . XDB::format('{?}', $this->sector);
 865         if ($this->subsector != null) {
 866             $conds[] = $sub . '.subsectorid = ' . XDB::format('{?}', $this->subsector);
 867         }
 868         return implode(' AND ', $conds);
 869     }
 870 }
 871 // }}}
 872
 873 // {{{ class UFC_UserRelated
 874 /** Filters users based on a relation toward a user
 875  * @param $user User to which searched users are related
 876  */
 877 abstract class UFC_UserRelated implements UserFilterCondition
 878 {
 879     protected $user;
 880     public function __construct(PlUser &$user)
 881     {
 882         $this->user =& $user;
 883     }
 884 }
 885 // }}}
 886
 887 // {{{ class UFC_Contact
 888 /** Filters users who belong to selected user's contacts
 889  */
 890 class UFC_Contact extends UFC_UserRelated
 891 {
 892     public function buildCondition(UserFilter &$uf)
 893     {
 894         $sub = $uf->addContactFilter($this->user->id());
 895         return 'c' . $sub . '.contact IS NOT NULL';
 896     }
 897 }
 898 // }}}
 899
 900 // {{{ class UFC_WatchRegistration
 901 /** Filters users being watched by selected user
 902  */
 903 class UFC_WatchRegistration extends UFC_UserRelated
 904 {
 905     public function buildCondition(UserFilter &$uf)
 906     {
 907         if (!$this->user->watch('registration')) {
 908             return UserFilterCondition::COND_FALSE;
 909         }
 910         $uids = $this->user->watchUsers();
 911         if (count($uids) == 0) {
 912             return UserFilterCondition::COND_FALSE;
 913         } else {
 914             return '$UID IN ' . XDB::formatArray($uids);
 915         }
 916     }
 917 }
 918 // }}}
 919
 920 // {{{ class UFC_WatchPromo
 921 /** Filters users belonging to a promo watched by selected user
 922  * @param $user Selected user (the one watching promo)
 923  * @param $grade Formation the user is watching
 924  */
 925 class UFC_WatchPromo extends UFC_UserRelated
 926 {
 927     private $grade;
 928     public function __construct(PlUser &$user, $grade = UserFilter::GRADE_ING)
 929     {
 930         parent::__construct($user);
 931         $this->grade = $grade;
 932     }
 933
 934     public function buildCondition(UserFilter &$uf)
 935     {
 936         $promos = $this->user->watchPromos();
 937         if (count($promos) == 0) {
 938             return UserFilterCondition::COND_FALSE;
 939         } else {
 940             $sube = $uf->addEducationFilter(true, $this->grade);
 941             $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade);
 942             return $field . ' IN ' . XDB::formatArray($promos);
 943         }
 944     }
 945 }
 946 // }}}
 947
 948 // {{{ class UFC_WatchContact
 949 /** Filters users watched by selected user
 950  */
 951 class UFC_WatchContact extends UFC_Contact
 952 {
 953     public function buildCondition(UserFilter &$uf)
 954     {
 955         if (!$this->user->watchContacts()) {
 956             return UserFilterCondition::COND_FALSE;
 957         }
 958         return parent::buildCondition($uf);
 959     }
 960 }
 961 // }}}
 962
 963
 964 /******************
 965  * ORDERS
 966  ******************/
 967
 968 // {{{ class UserFilterOrder
 969 /** Base class for ordering results of a query.
 970  * Parameters for the ordering must be given to the constructor ($desc for a
 971  *     descending order).
 972  * The getSortTokens function is used to get actual ordering part of the query.
 973  */
 974 abstract class UserFilterOrder extends PlFilterOrder
 975 {
 976     /** This function must return the tokens to use for ordering
 977      * @param &$uf The UserFilter whose results must be ordered
 978      * @return The name of the field to use for ordering results
 979      */
 980     abstract protected function getSortTokens(UserFilter &$uf);
 981 }
 982 // }}}
 983
 984 // {{{ class UFO_Promo
 985 /** Orders users by promotion
 986  * @param $grade Formation whose promotion users should be sorted by (restricts results to users of that formation)
 987  * @param $desc Whether sort is descending
 988  */
 989 class UFO_Promo extends UserFilterOrder
 990 {
 991     private $grade;
 992
 993     public function __construct($grade = null, $desc = false)
 994     {
 995         parent::__construct($desc);
 996         $this->grade = $grade;
 997     }
 998
 999     protected function getSortTokens(UserFilter &$uf)
1000     {
1001         if (UserFilter::isGrade($this->grade)) {
1002             $sub = $uf->addEducationFilter($this->grade);
1003             return 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
1004         } else {
1005             $sub = $uf->addDisplayFilter();
1006             return 'pd' . $sub . '.promo';
1007         }
1008     }
1009 }
1010 // }}}
1011
1012 // {{{ class UFO_Name
1013 /** Sorts users by name
1014  * @param $type Type of name on which to sort (firstname...)
1015  * @param $variant Variant of that name to use (marital, ordinary...)
1016  * @param $particle Set to true if particles should be included in the sorting order
1017  * @param $desc If sort order should be descending
1018  */
1019 class UFO_Name extends UserFilterOrder
1020 {
1021     private $type;
1022     private $variant;
1023     private $particle;
1024
1025     public function __construct($type, $variant = null, $particle = false, $desc = false)
1026     {
1027         parent::__construct($desc);
1028         $this->type = $type;
1029         $this->variant = $variant;
1030         $this->particle = $particle;
1031     }
1032
1033     protected function getSortTokens(UserFilter &$uf)
1034     {
1035         if (UserFilter::isDisplayName($this->type)) {
1036             $sub = $uf->addDisplayFilter();
1037             return 'pd' . $sub . '.' . $this->type;
1038         } else {
1039             $sub = $uf->addNameFilter($this->type, $this->variant);
1040             if ($this->particle) {
1041                 return 'CONCAT(pn' . $sub . '.particle, \' \', pn' . $sub . '.name)';
1042             } else {
1043                 return 'pn' . $sub . '.name';
1044             }
1045         }
1046     }
1047 }
1048 // }}}
1049
1050 // {{{ class UFO_Registration
1051 /** Sorts users based on registration date
1052  */
1053 class UFO_Registration extends UserFilterOrder
1054 {
1055     protected function getSortTokens(UserFilter &$uf)
1056     {
1057         return 'a.registration_date';
1058     }
1059 }
1060 // }}}
1061
1062 // {{{ class UFO_Birthday
1063 /** Sorts users based on next birthday date
1064  */
1065 class UFO_Birthday extends UserFilterOrder
1066 {
1067     protected function getSortTokens(UserFilter &$uf)
1068     {
1069         return 'p.next_birthday';
1070     }
1071 }
1072 // }}}
1073
1074 // {{{ class UFO_ProfileUpdate
1075 /** Sorts users based on last profile update
1076  */
1077 class UFO_ProfileUpdate extends UserFilterOrder
1078 {
1079     protected function getSortTokens(UserFilter &$uf)
1080     {
1081         return 'p.last_change';
1082     }
1083 }
1084 // }}}
1085
1086 // {{{ class UFO_Death
1087 /** Sorts users based on death date
1088  */
1089 class UFO_Death extends UserFilterOrder
1090 {
1091     protected function getSortTokens(UserFilter &$uf)
1092     {
1093         return 'p.deathdate';
1094     }
1095 }
1096 // }}}
1097
1098
1099 /***********************************
1100   *********************************
1101           USER FILTER CLASS
1102   *********************************
1103  ***********************************/
1104
1105 // {{{ class UserFilter
1106 /** This class provides a convenient and centralized way of filtering users.
1107  *
1108  * Usage:
1109  * $uf = new UserFilter(new UFC_Blah($x, $y), new UFO_Coin($z, $t));
1110  *
1111  * Resulting UserFilter can be used to:
1112  * - get a list of User objects matching the filter
1113  * - get a list of UIDs matching the filter
1114  * - get the number of users matching the filter
1115  * - check whether a given User matches the filter
1116  * - filter a list of User objects depending on whether they match the filter
1117  *
1118  * Usage for UFC and UFO objects:
1119  * A UserFilter will call all private functions named XXXJoins.
1120  * These functions must return an array containing the list of join
1121  * required by the various UFC and UFO associated to the UserFilter.
1122  * Entries in those returned array are of the following form:
1123  *   'join_tablealias' => array('join_type', 'joined_table', 'join_criter')
1124  * which will be translated into :
1125  *   join_type JOIN joined_table AS join_tablealias ON (join_criter)
1126  * in the final query.
1127  *
1128  * In the join_criter text, $ME is replaced with 'join_tablealias', $PID with
1129  * profile.pid, and $UID with auth_user_md5.user_id.
1130  *
1131  * For each kind of "JOIN" needed, a function named addXXXFilter() should be defined;
1132  * its parameter will be used to set various private vars of the UserFilter describing
1133  * the required joins ; such a function shall return the "join_tablealias" to use
1134  * when referring to the joined table.
1135  *
1136  * For example, if data from profile_job must be available to filter results,
1137  * the UFC object will call $uf-addJobFilter(), which will set the 'with_pj' var and
1138  * return 'pj', the short name to use when referring to profile_job; when building
1139  * the query, calling the jobJoins function will return an array containing a single
1140  * row:
1141  *   'pj' => array('left', 'profile_job', '$ME.pid = $UID');
1142  *
1143  * The 'register_optional' function can be used to generate unique table aliases when
1144  * the same table has to be joined several times with different aliases.
1145  */
1146 class UserFilter extends PlFilter
1147 {
1148     protected $joinMethods = array();
1149
1150     protected $joinMetas = array('$PID' => 'p.pid',
1151                                  '$UID' => 'a.uid',
1152                                 );
1153
1154     private $root;
1155     private $sort = array();
1156     private $query = null;
1157     private $orderby = null;
1158
1159     private $lastcount = null;
1160
1161     public function __construct($cond = null, $sort = null)
1162     {
1163         if (empty($this->joinMethods)) {
1164             $class = new ReflectionClass('UserFilter');
1165             foreach ($class->getMethods() as $method) {
1166                 $name = $method->getName();
1167                 if (substr($name, -5) == 'Joins' && $name != 'buildJoins') {
1168                     $this->joinMethods[] = $name;
1169                 }
1170             }
1171         }
1172         if (!is_null($cond)) {
1173             if ($cond instanceof PlFilterCondition) {
1174                 $this->setCondition($cond);
1175             }
1176         }
1177         if (!is_null($sort)) {
1178             if ($sort instanceof UserFilterOrder) {
1179                 $this->addSort($sort);
1180             } else if (is_array($sort)) {
1181                 foreach ($sort as $s) {
1182                     $this->addSort($s);
1183                 }
1184             }
1185         }
1186     }
1187
1188     private function buildQuery()
1189     {
1190         if (is_null($this->orderby)) {
1191             $orders = array();
1192             foreach ($this->sort as $sort) {
1193                 $orders = array_merge($orders, $sort->buildSort($this));
1194             }
1195             if (count($orders) == 0) {
1196                 $this->orderby = '';
1197             } else {
1198                 $this->orderby = 'ORDER BY  ' . implode(', ', $orders);
1199             }
1200         }
1201         if (is_null($this->query)) {
1202             $where = $this->root->buildCondition($this);
1203             $joins = $this->buildJoins();
1204             if ($this->with_accounts) {
1205                 $from = 'accounts AS a';
1206             } else {
1207                 $this->requireProfiles();
1208                 $from = 'profiles AS p';
1209             }
1210             $this->query = 'FROM  ' . $from . '
1211                                ' . $joins . '
1212                            WHERE  (' . $where . ')';
1213         }
1214     }
1215
1216     private function getUIDList($uids = null, PlLimit &$limit)
1217     {
1218         $this->requireAccounts();
1219         $this->buildQuery();
1220         $lim = $limit->getSql();
1221         $cond = '';
1222         if (!is_null($uids)) {
1223             $cond = ' AND a.uid IN ' . XDB::formatArray($uids);
1224         }
1225         $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS  a.uid
1226                                     ' . $this->query . $cond . '
1227                                    GROUP BY  a.uid
1228                                     ' . $this->orderby . '
1229                                     ' . $lim);
1230         $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
1231         return $fetched;
1232     }
1233
1234     /** Check that the user match the given rule.
1235      */
1236     public function checkUser(PlUser &$user)
1237     {
1238         $this->requireAccounts();
1239         $this->buildQuery();
1240         $count = (int)XDB::fetchOneCell('SELECT  COUNT(*)
1241                                         ' . $this->query . XDB::format(' AND a.uid = {?}', $user->id()));
1242         return $count == 1;
1243     }
1244
1245     /** Filter a list of user to extract the users matching the rule.
1246      */
1247     public function filter(array $users, PlLimit &$limit)
1248     {
1249         $this->requireAccounts();
1250         $this->buildQuery();
1251         $table = array();
1252         $uids  = array();
1253         foreach ($users as $user) {
1254             if ($user instanceof PlUser) {
1255                 $uid = $user->id();
1256             } else {
1257                 $uid = $user;
1258             }
1259             $uids[] = $uid;
1260             $table[$uid] = $user;
1261         }
1262         $fetched = $this->getUIDList($uids, $limit);
1263         $output = array();
1264         foreach ($fetched as $uid) {
1265             $output[] = $table[$uid];
1266         }
1267         return $output;
1268     }
1269
1270     public function getUIDs(PlLimit &$limit)
1271     {
1272         return $this->getUIDList(null, $limit);
1273     }
1274
1275     public function getUsers(PlLimit &$limit)
1276     {
1277         return User::getBulkUsersWithUIDs($this->getUIDs($limit));
1278     }
1279
1280     public function get(PlLimit &$limit)
1281     {
1282         return $this->getUsers($limit);
1283     }
1284
1285     public function getTotalCount()
1286     {
1287         if (is_null($this->lastcount)) {
1288             $this->buildQuery();
1289             if ($this->requireAccounts()) {
1290                 $field = 'a.uid';
1291             } else {
1292                 $field = 'p.pid';
1293             }
1294             return (int)XDB::fetchOneCell('SELECT  COUNT(DISTINCT ' . $field . ')
1295                                           ' . $this->query);
1296         } else {
1297             return $this->lastcount;
1298         }
1299     }
1300
1301     public function setCondition(PlFilterCondition &$cond)
1302     {
1303         $this->root =& $cond;
1304         $this->query = null;
1305     }
1306
1307     public function addSort(PlFilterOrder &$sort)
1308     {
1309         $this->sort[] = $sort;
1310         $this->orderby = null;
1311     }
1312
1313     static public function getLegacy($promo_min, $promo_max)
1314     {
1315         if ($promo_min != 0) {
1316             $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min));
1317         } else {
1318             $min = new UFC_True();
1319         }
1320         if ($promo_max != 0) {
1321             $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max));
1322         } else {
1323             $max = new UFC_True();
1324         }
1325         return new UserFilter(new PFC_And($min, $max));
1326     }
1327
1328     static public function sortByName()
1329     {
1330         return array(new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME));
1331     }
1332
1333     static public function sortByPromo()
1334     {
1335         return array(new UFO_Promo(), new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME));
1336     }
1337
1338     static private function getDBSuffix($string)
1339     {
1340         return preg_replace('/[^a-z0-9]/i', '', $string);
1341     }
1342
1343
1344     /** Stores a new (and unique) table alias in the &$table table
1345      * @param   &$table Array in which the table alias must be stored
1346      * @param   $val    Value which will then be used to build the join
1347      * @return          Name of the newly created alias
1348      */
1349     private $option = 0;
1350     private function register_optional(array &$table, $val)
1351     {
1352         if (is_null($val)) {
1353             $sub   = $this->option++;
1354             $index = null;
1355         } else {
1356             $sub   = self::getDBSuffix($val);
1357             $index = $val;
1358         }
1359         $sub = '_' . $sub;
1360         $table[$sub] = $index;
1361         return $sub;
1362     }
1363
1364     /** PROFILE VS ACCOUNT
1365      */
1366     private $with_profiles = false;
1367     private $with_accounts = false;
1368     public function requireAccounts()
1369     {
1370         $this->with_accounts = true;
1371     }
1372
1373     public function requireProfiles()
1374     {
1375         $this->with_profiles = true;
1376     }
1377
1378     protected function accountJoins()
1379     {
1380         $joins = array();
1381         if ($this->with_profiles && $this->with_accounts) {
1382             $joins['ap'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'account_profiles', '$ME.uid = $UID AND FIND_IN_SET(\'owner\', ap.perms)');
1383             $joins['p'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profiles', '$PID = ap.pid');
1384         }
1385         return $joins;
1386     }
1387
1388     /** DISPLAY
1389      */
1390     const DISPLAY = 'display';
1391     private $pd = false;
1392     public function addDisplayFilter()
1393     {
1394         $this->requireProfiles();
1395         $this->pd = true;
1396         return '';
1397     }
1398
1399     protected function displayJoins()
1400     {
1401         if ($this->pd) {
1402             return array('pd' => new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_display', '$ME.pid = $PID'));
1403         } else {
1404             return array();
1405         }
1406     }
1407
1408     /** NAMES
1409      */
1410     /* name tokens */
1411     const LASTNAME  = 'lastname';
1412     const FIRSTNAME = 'firstname';
1413     const NICKNAME  = 'nickname';
1414     const PSEUDONYM = 'pseudonym';
1415     const NAME      = 'name';
1416     /* name variants */
1417     const VN_MARITAL  = 'marital';
1418     const VN_ORDINARY = 'ordinary';
1419     const VN_OTHER    = 'other';
1420     const VN_INI      = 'ini';
1421     /* display names */
1422     const DN_FULL      = 'directory_name';
1423     const DN_DISPLAY   = 'yourself';
1424     const DN_YOURSELF  = 'yourself';
1425     const DN_DIRECTORY = 'directory_name';
1426     const DN_PRIVATE   = 'private_name';
1427     const DN_PUBLIC    = 'public_name';
1428     const DN_SHORT     = 'short_name';
1429     const DN_SORT      = 'sort_name';
1430
1431     static public $name_variants = array(
1432         self::LASTNAME => array(self::VN_MARITAL, self::VN_ORDINARY),
1433         self::FIRSTNAME => array(self::VN_ORDINARY, self::VN_INI, self::VN_OTHER)
1434     );
1435
1436     static public function assertName($name)
1437     {
1438         if (!Profile::getNameTypeId($name)) {
1439             Platal::page()->kill('Invalid name type: ' . $name);
1440         }
1441     }
1442
1443     static public function isDisplayName($name)
1444     {
1445         return $name == self::DN_FULL || $name == self::DN_DISPLAY
1446             || $name == self::DN_YOURSELF || $name == self::DN_DIRECTORY
1447             || $name == self::DN_PRIVATE || $name == self::DN_PUBLIC
1448             || $name == self::DN_SHORT || $name == self::DN_SORT;
1449     }
1450
1451     private $pn  = array();
1452     public function addNameFilter($type, $variant = null)
1453     {
1454         $this->requireProfiles();
1455         if (!is_null($variant)) {
1456             $ft  = $type . '_' . $variant;
1457         } else {
1458             $ft = $type;
1459         }
1460         $sub = '_' . $ft;
1461         self::assertName($ft);
1462
1463         if (!is_null($variant) && $variant == 'other') {
1464             $sub .= $this->option++;
1465         }
1466         $this->pn[$sub] = Profile::getNameTypeId($ft);
1467         return $sub;
1468     }
1469
1470     protected function nameJoins()
1471     {
1472         $joins = array();
1473         foreach ($this->pn as $sub => $type) {
1474             $joins['pn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_name', '$ME.pid = $PID AND $ME.typeid = ' . $type);
1475         }
1476         return $joins;
1477     }
1478
1479     /** EDUCATION
1480      */
1481     const GRADE_ING = 'Ing.';
1482     const GRADE_PHD = 'PhD';
1483     const GRADE_MST = 'M%';
1484     static public function isGrade($grade)
1485     {
1486         return $grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST;
1487     }
1488
1489     static public function assertGrade($grade)
1490     {
1491         if (!self::isGrade($grade)) {
1492             Platal::page()->killError("Diplôme non valide");
1493         }
1494     }
1495
1496     static public function promoYear($grade)
1497     {
1498         // XXX: Definition of promotion for phds and masters might change in near future.
1499         return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year';
1500     }
1501
1502     private $pepe     = array();
1503     private $with_pee = false;
1504     public function addEducationFilter($x = false, $grade = null)
1505     {
1506         $this->requireProfiles();
1507         if (!$x) {
1508             $index = $this->option;
1509             $sub   = $this->option++;
1510         } else {
1511             self::assertGrade($grade);
1512             $index = $grade;
1513             $sub   = $grade[0];
1514             $this->with_pee = true;
1515         }
1516         $sub = '_' . $sub;
1517         $this->pepe[$index] = $sub;
1518         return $sub;
1519     }
1520
1521     protected function educationJoins()
1522     {
1523         $joins = array();
1524         if ($this->with_pee) {
1525             $joins['pee'] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_enum', 'pee.abbreviation = \'X\'');
1526         }
1527         foreach ($this->pepe as $grade => $sub) {
1528             if ($this->isGrade($grade)) {
1529                 $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.eduid = pee.id AND $ME.uid = $PID');
1530                 $joins['pede' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.abbreviation LIKE ' .
1531                                                   XDB::format('{?}', $grade));
1532             } else {
1533                 $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.uid = $PID');
1534                 $joins['pee' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_enum', '$ME.id = pe' . $sub . '.eduid');
1535                 $joins['pede' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid');
1536             }
1537         }
1538         return $joins;
1539     }
1540
1541
1542     /** GROUPS
1543      */
1544     private $gpm = array();
1545     public function addGroupFilter($group = null)
1546     {
1547         $this->requireAccounts();
1548         if (!is_null($group)) {
1549             if (ctype_digit($group)) {
1550                 $index = $sub = $group;
1551             } else {
1552                 $index = $group;
1553                 $sub   = self::getDBSuffix($group);
1554             }
1555         } else {
1556             $sub = 'group_' . $this->option++;
1557             $index = null;
1558         }
1559         $sub = '_' . $sub;
1560         $this->gpm[$sub] = $index;
1561         return $sub;
1562     }
1563
1564     protected function groupJoins()
1565     {
1566         $joins = array();
1567         foreach ($this->gpm as $sub => $key) {
1568             if (is_null($key)) {
1569                 $joins['gpa' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'groupex.asso');
1570                 $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
1571             } else if (ctype_digit($key)) {
1572                 $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = ' . $key);
1573             } else {
1574                 $joins['gpa' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'groupex.asso', XDB::format('$ME.diminutif = {?}', $key));
1575                 $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
1576             }
1577         }
1578         return $joins;
1579     }
1580
1581     /** EMAILS
1582      */
1583     private $e = array();
1584     public function addEmailRedirectFilter($email = null)
1585     {
1586         $this->requireAccounts();
1587         return $this->register_optional($this->e, $email);
1588     }
1589
1590     private $ve = array();
1591     public function addVirtualEmailFilter($email = null)
1592     {
1593         $this->addAliasFilter(self::ALIAS_FORLIFE);
1594         return $this->register_optional($this->ve, $email);
1595     }
1596
1597     const ALIAS_BEST    = 'bestalias';
1598     const ALIAS_FORLIFE = 'forlife';
1599     private $al = array();
1600     public function addAliasFilter($alias = null)
1601     {
1602         $this->requireAccounts();
1603         return $this->register_optional($this->al, $alias);
1604     }
1605
1606     protected function emailJoins()
1607     {
1608         global $globals;
1609         $joins = array();
1610         foreach ($this->e as $sub=>$key) {
1611             if (is_null($key)) {
1612                 $joins['e' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'emails', '$ME.uid = $UID AND $ME.flags != \'filter\'');
1613             } else {
1614                 $joins['e' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'emails', XDB::format('$ME.uid = $UID AND $ME.flags != \'filter\' AND $ME.email = {?}', $key));
1615             }
1616         }
1617         foreach ($this->al as $sub=>$key) {
1618             if (is_null($key)) {
1619                 $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\')');
1620             } else if ($key == self::ALIAS_BEST) {
1621                 $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)');
1622             } else if ($key == self::ALIAS_FORLIFE) {
1623                 $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type = \'a_vie\'');
1624             } else {
1625                 $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));
1626             }
1627         }
1628         foreach ($this->ve as $sub=>$key) {
1629             if (is_null($key)) {
1630                 $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', '$ME.type = \'user\'');
1631             } else {
1632                 $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', XDB::format('$ME.type = \'user\' AND $ME.alias = {?}', $key));
1633             }
1634             $joins['vr' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual_redirect', XDB::format('$ME.vid = v' . $sub . '.vid
1635                                                                                  AND ($ME.redirect IN (CONCAT(al_forlife.alias, \'@\', {?}),
1636                                                                                                        CONCAT(al_forlife.alias, \'@\', {?}),
1637                                                                                                        a.email))',
1638                                                                                 $globals->mail->domain, $globals->mail->domain2));
1639         }
1640         return $joins;
1641     }
1642
1643
1644     /** ADDRESSES
1645      */
1646     private $with_pa = false;
1647     public function addAddressFilter()
1648     {
1649         $this->requireProfiles();
1650         $this->with_pa = true;
1651         return 'pa';
1652     }
1653
1654     protected function addressJoins()
1655     {
1656         $joins = array();
1657         if ($this->with_pa) {
1658             $joins['pa'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_address', '$ME.pid = $PID');
1659         }
1660         return $joins;
1661     }
1662
1663
1664     /** CORPS
1665      */
1666
1667     private $pc = false;
1668     private $pce = array();
1669     private $pcr = false;
1670     public function addCorpsFilter($type)
1671     {
1672         $this->requireProfiles();
1673         $this->pc = true;
1674         if ($type == UFC_Corps::CURRENT) {
1675             $pce['pcec'] = 'current_corpsid';
1676             return 'pcec';
1677         } else if ($type == UFC_Corps::ORIGIN) {
1678             $pce['pceo'] = 'original_corpsid';
1679             return 'pceo';
1680         }
1681     }
1682
1683     public function addCorpsRankFilter()
1684     {
1685         $this->requireProfiles();
1686         $this->pc = true;
1687         $this->pcr = true;
1688         return 'pcr';
1689     }
1690
1691     protected function corpsJoins()
1692     {
1693         $joins = array();
1694         if ($this->pc) {
1695             $joins['pc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps', '$ME.uid = $UID');
1696         }
1697         if ($this->pcr) {
1698             $joins['pcr'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_rank_enum', '$ME.id = pc.rankid');
1699         }
1700         foreach($this->pce as $sub => $field) {
1701             $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_enum', '$ME.id = pc.' . $field);
1702         }
1703         return $joins;
1704     }
1705
1706     /** JOBS
1707      */
1708
1709     const JOB_SECTOR = 1;
1710     const JOB_SUBSECTOR = 2;
1711     const JOB_SUBSUBSECTOR = 4;
1712     const JOB_ALTERNATES = 8;
1713     const JOB_USERDEFINED = 16;
1714
1715     /** Joins :
1716      * pj => profile_job
1717      * pje => profile_job_enum
1718      * pjse => profile_job_sector_enum
1719      * pjsse => profile_job_subsector_enum
1720      * pjssse => profile_job_subsubsector_enum
1721      * pja => profile_job_alternates
1722      */
1723     private $with_pj = false;
1724     private $with_pje = false;
1725     private $with_pjse = false;
1726     private $with_pjsse = false;
1727     private $with_pjssse = false;
1728     private $with_pja = false;
1729
1730     public function addJobFilter()
1731     {
1732         $this->requireProfiles();
1733         $this->with_pj = true;
1734         return 'pj';
1735     }
1736
1737     public function addJobCompanyFilter()
1738     {
1739         $this->addJobFilter();
1740         $this->with_pje = true;
1741         return 'pje';
1742     }
1743
1744     public function addJobSectorizationFilter($type)
1745     {
1746         $this->addJobFilter();
1747         if ($type == self::JOB_SECTOR) {
1748             $this->with_pjse = true;
1749             return 'pjse';
1750         } else if ($type == self::JOB_SUBSECTOR) {
1751             $this->with_pjsse = true;
1752             return 'pjsse';
1753         } else if ($type == self::JOB_SUBSUBSECTOR) {
1754             $this->with_pjssse = true;
1755             return 'pjssse';
1756         } else if ($type == self::JOB_ALTERNATES) {
1757             $this->with_pja = true;
1758             return 'pja';
1759         }
1760     }
1761
1762     protected function jobJoins()
1763     {
1764         $joins = array();
1765         if ($this->with_pj) {
1766             $joins['pj'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job', '$ME.uid = $UID');
1767         }
1768         if ($this->with_pje) {
1769             $joins['pje'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_enum', '$ME.id = pj.jobid');
1770         }
1771         if ($this->with_pjse) {
1772             $joins['pjse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_sector_enum', '$ME.id = pj.sectorid');
1773         }
1774         if ($this->with_pjsse) {
1775             $joins['pjsse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_subsector_enum', '$ME.id = pj.subsectorid');
1776         }
1777         if ($this->with_pjssse) {
1778             $joins['pjssse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_subsubsector_enum', '$ME.id = pj.subsubsectorid');
1779         }
1780         if ($this->with_pja) {
1781             $joins['pja'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_alternates', '$ME.subsubsectorid = pj.subsubsectorid');
1782         }
1783         return $joins;
1784     }
1785
1786     /** NETWORKING
1787      */
1788
1789     private $with_pnw = false;
1790     public function addNetworkingFilter()
1791     {
1792         $this->requireAccounts();
1793         $this->with_pnw = true;
1794         return 'pnw';
1795     }
1796
1797     protected function networkingJoins()
1798     {
1799         $joins = array();
1800         if ($this->with_pnw) {
1801             $joins['pnw'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_networking', '$ME.uid = $UID');
1802         }
1803         return $joins;
1804     }
1805
1806     /** PHONE
1807      */
1808
1809     private $with_ptel = false;
1810
1811     public function addPhoneFilter()
1812     {
1813         $this->requireAccounts();
1814         $this->with_ptel = true;
1815         return 'ptel';
1816     }
1817
1818     protected function phoneJoins()
1819     {
1820         $joins = array();
1821         if ($this->with_ptel) {
1822             $joins['ptel'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_phones', '$ME.uid = $UID');
1823         }
1824         return $joins;
1825     }
1826
1827     /** MEDALS
1828      */
1829
1830     private $with_pmed = false;
1831     public function addMedalFilter()
1832     {
1833         $this->requireProfiles();
1834         $this->with_pmed = true;
1835         return 'pmed';
1836     }
1837
1838     protected function medalJoins()
1839     {
1840         $joins = array();
1841         if ($this->with_pmed) {
1842             $joins['pmed'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_medals_sub', '$ME.uid = $UID');
1843         }
1844         return $joins;
1845     }
1846
1847     /** MENTORING
1848      */
1849
1850     private $pms = array();
1851     const MENTOR_EXPERTISE  = 1;
1852     const MENTOR_COUNTRY    = 2;
1853     const MENTOR_SECTOR     = 3;
1854
1855     public function addMentorFilter($type)
1856     {
1857         $this->requireAccounts();
1858         switch($type) {
1859         case MENTOR_EXPERTISE:
1860             $pms['pme'] = 'profile_mentor';
1861             return 'pme';
1862         case MENTOR_COUNTRY:
1863             $pms['pmc'] = 'profile_mentor_country';
1864             return 'pmc';
1865         case MENTOR_SECTOR:
1866             $pms['pms'] =  'profile_mentor_sector';
1867             return 'pms';
1868         default:
1869             Platal::page()->killError("Undefined mentor filter.");
1870         }
1871     }
1872
1873     protected function mentorJoins()
1874     {
1875         $joins = array();
1876         foreach ($this->pms as $sub => $tab) {
1877             $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, $tab, '$ME.uid = $UID');
1878         }
1879         return $joins;
1880     }
1881
1882     /** CONTACTS
1883      */
1884     private $cts = array();
1885     public function addContactFilter($uid = null)
1886     {
1887         $this->requireAccounts();
1888         return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid);
1889     }
1890
1891     protected function contactJoins()
1892     {
1893         $joins = array();
1894         foreach ($this->cts as $sub=>$key) {
1895             if (is_null($key)) {
1896                 $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', '$ME.contact = $UID');
1897             } else {
1898                 $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', XDB::format('$ME.uid = {?} AND $ME.contact = $UID', substr($key, 5)));
1899             }
1900         }
1901         return $joins;
1902     }
1903
1904
1905     /** CARNET
1906      */
1907     private $wn = array();
1908     public function addWatchRegistrationFilter($uid = null)
1909     {
1910         $this->requireAccounts();
1911         return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid);
1912     }
1913
1914     private $wp = array();
1915     public function addWatchPromoFilter($uid = null)
1916     {
1917         $this->requireAccounts();
1918         return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid);
1919     }
1920
1921     private $w = array();
1922     public function addWatchFilter($uid = null)
1923     {
1924         $this->requireAccounts();
1925         return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid);
1926     }
1927
1928     protected function watchJoins()
1929     {
1930         $joins = array();
1931         foreach ($this->w as $sub=>$key) {
1932             if (is_null($key)) {
1933                 $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch');
1934             } else {
1935                 $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch', XDB::format('$ME.uid = {?}', substr($key, 5)));
1936             }
1937         }
1938         foreach ($this->wn as $sub=>$key) {
1939             if (is_null($key)) {
1940                 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', '$ME.ni_id = $UID');
1941             } else {
1942                 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5)));
1943             }
1944         }
1945         foreach ($this->wn as $sub=>$key) {
1946             if (is_null($key)) {
1947                 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', '$ME.ni_id = $UID');
1948             } else {
1949                 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5)));
1950             }
1951         }
1952         foreach ($this->wp as $sub=>$key) {
1953             if (is_null($key)) {
1954                 $joins['wp' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_promo');
1955             } else {
1956                 $joins['wp' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_promo', XDB::format('$ME.uid = {?}', substr($key, 5)));
1957             }
1958         }
1959         return $joins;
1960     }
1961 }
1962 // }}}
1963
1964 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
1965 ?>