classes/userfilter.php

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