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