classes/userfilter.php

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