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_Formation
 135 /** Filters users by formation
 136  * @param $val The formation to search (either ID or array of IDs)
 137  */
 138 class UFC_Formation 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_Diploma
 159 class UFC_Diploma 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_StudyField
 180 class UFC_StudyField 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 sector
1155  * @param $subsector Subsector (null for any)
1156  */
1157 class UFC_Mentor_Sectorization implements UserFilterCondition
1158 {
1159     private $sector;
1160     private $subsector;
1161
1162     public function __construct($sector, $subsector = null)
1163     {
1164         $this->sector = $sector;
1165         $this->subsubsector = $subsector;
1166     }
1167
1168     public function buildCondition(PlFilter &$uf)
1169     {
1170         $sub = $uf->addMentorFilter(UserFilter::MENTOR_SECTOR);
1171         $conds = array();
1172         $conds[] = $sub . '.sectorid = ' . XDB::format('{?}', $this->sector);
1173         if ($this->subsector != null) {
1174             $conds[] = $sub . '.subsectorid = ' . XDB::format('{?}', $this->subsector);
1175         }
1176         return implode(' AND ', $conds);
1177     }
1178 }
1179 // }}}
1180
1181 // {{{ class UFC_UserRelated
1182 /** Filters users based on a relation toward a user
1183  * @param $user User to which searched users are related
1184  */
1185 abstract class UFC_UserRelated implements UserFilterCondition
1186 {
1187     protected $user;
1188     public function __construct(PlUser &$user)
1189     {
1190         $this->user =& $user;
1191     }
1192 }
1193 // }}}
1194
1195 // {{{ class UFC_Contact
1196 /** Filters users who belong to selected user's contacts
1197  */
1198 class UFC_Contact extends UFC_UserRelated
1199 {
1200     public function buildCondition(PlFilter &$uf)
1201     {
1202         $sub = $uf->addContactFilter($this->user->id());
1203         return 'c' . $sub . '.contact IS NOT NULL';
1204     }
1205 }
1206 // }}}
1207
1208 // {{{ class UFC_WatchRegistration
1209 /** Filters users being watched by selected user
1210  */
1211 class UFC_WatchRegistration extends UFC_UserRelated
1212 {
1213     public function buildCondition(PlFilter &$uf)
1214     {
1215         if (!$this->user->watch('registration')) {
1216             return PlFilterCondition::COND_FALSE;
1217         }
1218         $uids = $this->user->watchUsers();
1219         if (count($uids) == 0) {
1220             return PlFilterCondition::COND_FALSE;
1221         } else {
1222             return '$UID IN ' . XDB::formatArray($uids);
1223         }
1224     }
1225 }
1226 // }}}
1227
1228 // {{{ class UFC_WatchPromo
1229 /** Filters users belonging to a promo watched by selected user
1230  * @param $user Selected user (the one watching promo)
1231  * @param $grade Formation the user is watching
1232  */
1233 class UFC_WatchPromo extends UFC_UserRelated
1234 {
1235     private $grade;
1236     public function __construct(PlUser &$user, $grade = UserFilter::GRADE_ING)
1237     {
1238         parent::__construct($user);
1239         $this->grade = $grade;
1240     }
1241
1242     public function buildCondition(PlFilter &$uf)
1243     {
1244         $promos = $this->user->watchPromos();
1245         if (count($promos) == 0) {
1246             return PlFilterCondition::COND_FALSE;
1247         } else {
1248             $sube = $uf->addEducationFilter(true, $this->grade);
1249             $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade);
1250             return $field . ' IN ' . XDB::formatArray($promos);
1251         }
1252     }
1253 }
1254 // }}}
1255
1256 // {{{ class UFC_WatchContact
1257 /** Filters users watched by selected user
1258  */
1259 class UFC_WatchContact extends UFC_Contact
1260 {
1261     public function buildCondition(PlFilter &$uf)
1262     {
1263         if (!$this->user->watchContacts()) {
1264             return PlFilterCondition::COND_FALSE;
1265         }
1266         return parent::buildCondition($uf);
1267     }
1268 }
1269 // }}}
1270
1271
1272 /******************
1273  * ORDERS
1274  ******************/
1275
1276 // {{{ class UserFilterOrder
1277 /** Base class for ordering results of a query.
1278  * Parameters for the ordering must be given to the constructor ($desc for a
1279  *     descending order).
1280  * The getSortTokens function is used to get actual ordering part of the query.
1281  */
1282 abstract class UserFilterOrder extends PlFilterOrder
1283 {
1284     /** This function must return the tokens to use for ordering
1285      * @param &$uf The UserFilter whose results must be ordered
1286      * @return The name of the field to use for ordering results
1287      */
1288 //    abstract protected function getSortTokens(UserFilter &$uf);
1289 }
1290 // }}}
1291
1292 // {{{ class UFO_Promo
1293 /** Orders users by promotion
1294  * @param $grade Formation whose promotion users should be sorted by (restricts results to users of that formation)
1295  * @param $desc Whether sort is descending
1296  */
1297 class UFO_Promo extends UserFilterOrder
1298 {
1299     private $grade;
1300
1301     public function __construct($grade = null, $desc = false)
1302     {
1303         parent::__construct($desc);
1304         $this->grade = $grade;
1305     }
1306
1307     protected function getSortTokens(PlFilter &$uf)
1308     {
1309         if (UserFilter::isGrade($this->grade)) {
1310             $sub = $uf->addEducationFilter($this->grade);
1311             return 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
1312         } else {
1313             $sub = $uf->addDisplayFilter();
1314             return 'pd' . $sub . '.promo';
1315         }
1316     }
1317 }
1318 // }}}
1319
1320 // {{{ class UFO_Name
1321 /** Sorts users by name
1322  * @param $type Type of name on which to sort (firstname...)
1323  * @param $variant Variant of that name to use (marital, ordinary...)
1324  * @param $particle Set to true if particles should be included in the sorting order
1325  * @param $desc If sort order should be descending
1326  */
1327 class UFO_Name extends UserFilterOrder
1328 {
1329     private $type;
1330     private $variant;
1331     private $particle;
1332
1333     public function __construct($type, $variant = null, $particle = false, $desc = false)
1334     {
1335         parent::__construct($desc);
1336         $this->type = $type;
1337         $this->variant = $variant;
1338         $this->particle = $particle;
1339     }
1340
1341     protected function getSortTokens(PlFilter &$uf)
1342     {
1343         if (UserFilter::isDisplayName($this->type)) {
1344             $sub = $uf->addDisplayFilter();
1345             return 'pd' . $sub . '.' . $this->type;
1346         } else {
1347             $sub = $uf->addNameFilter($this->type, $this->variant);
1348             if ($this->particle) {
1349                 return 'CONCAT(pn' . $sub . '.particle, \' \', pn' . $sub . '.name)';
1350             } else {
1351                 return 'pn' . $sub . '.name';
1352             }
1353         }
1354     }
1355 }
1356 // }}}
1357
1358 // {{{ class UFO_Score
1359 class UFO_Score extends UserFilterOrder
1360 {
1361     protected function getSortTokens(PlFilter &$uf)
1362     {
1363         $sub = $uf->addNameTokensFilter();
1364         return 'SUM(' . $sub . '.score)';
1365     }
1366 }
1367 // }}}
1368
1369 // {{{ class UFO_Registration
1370 /** Sorts users based on registration date
1371  */
1372 class UFO_Registration extends UserFilterOrder
1373 {
1374     protected function getSortTokens(PlFilter &$uf)
1375     {
1376         return 'a.registration_date';
1377     }
1378 }
1379 // }}}
1380
1381 // {{{ class UFO_Birthday
1382 /** Sorts users based on next birthday date
1383  */
1384 class UFO_Birthday extends UserFilterOrder
1385 {
1386     protected function getSortTokens(PlFilter &$uf)
1387     {
1388         return 'p.next_birthday';
1389     }
1390 }
1391 // }}}
1392
1393 // {{{ class UFO_ProfileUpdate
1394 /** Sorts users based on last profile update
1395  */
1396 class UFO_ProfileUpdate extends UserFilterOrder
1397 {
1398     protected function getSortTokens(PlFilter &$uf)
1399     {
1400         return 'p.last_change';
1401     }
1402 }
1403 // }}}
1404
1405 // {{{ class UFO_Death
1406 /** Sorts users based on death date
1407  */
1408 class UFO_Death extends UserFilterOrder
1409 {
1410     protected function getSortTokens(PlFilter &$uf)
1411     {
1412         return 'p.deathdate';
1413     }
1414 }
1415 // }}}
1416
1417
1418 /***********************************
1419   *********************************
1420           USER FILTER CLASS
1421   *********************************
1422  ***********************************/
1423
1424 // {{{ class UserFilter
1425 /** This class provides a convenient and centralized way of filtering users.
1426  *
1427  * Usage:
1428  * $uf = new UserFilter(new UFC_Blah($x, $y), new UFO_Coin($z, $t));
1429  *
1430  * Resulting UserFilter can be used to:
1431  * - get a list of User objects matching the filter
1432  * - get a list of UIDs matching the filter
1433  * - get the number of users matching the filter
1434  * - check whether a given User matches the filter
1435  * - filter a list of User objects depending on whether they match the filter
1436  *
1437  * Usage for UFC and UFO objects:
1438  * A UserFilter will call all private functions named XXXJoins.
1439  * These functions must return an array containing the list of join
1440  * required by the various UFC and UFO associated to the UserFilter.
1441  * Entries in those returned array are of the following form:
1442  *   'join_tablealias' => array('join_type', 'joined_table', 'join_criter')
1443  * which will be translated into :
1444  *   join_type JOIN joined_table AS join_tablealias ON (join_criter)
1445  * in the final query.
1446  *
1447  * In the join_criter text, $ME is replaced with 'join_tablealias', $PID with
1448  * profile.pid, and $UID with auth_user_md5.user_id.
1449  *
1450  * For each kind of "JOIN" needed, a function named addXXXFilter() should be defined;
1451  * its parameter will be used to set various private vars of the UserFilter describing
1452  * the required joins ; such a function shall return the "join_tablealias" to use
1453  * when referring to the joined table.
1454  *
1455  * For example, if data from profile_job must be available to filter results,
1456  * the UFC object will call $uf-addJobFilter(), which will set the 'with_pj' var and
1457  * return 'pj', the short name to use when referring to profile_job; when building
1458  * the query, calling the jobJoins function will return an array containing a single
1459  * row:
1460  *   'pj' => array('left', 'profile_job', '$ME.pid = $UID');
1461  *
1462  * The 'register_optional' function can be used to generate unique table aliases when
1463  * the same table has to be joined several times with different aliases.
1464  */
1465 class UserFilter extends PlFilter
1466 {
1467     protected $joinMethods = array();
1468
1469     protected $joinMetas = array(
1470                                 '$PID' => 'p.pid',
1471                                 '$UID' => 'a.uid',
1472                                 );
1473
1474     private $root;
1475     private $sort = array();
1476     private $query = null;
1477     private $orderby = null;
1478
1479     private $lastcount = null;
1480
1481     public function __construct($cond = null, $sort = null)
1482     {
1483         if (empty($this->joinMethods)) {
1484             $class = new ReflectionClass('UserFilter');
1485             foreach ($class->getMethods() as $method) {
1486                 $name = $method->getName();
1487                 if (substr($name, -5) == 'Joins' && $name != 'buildJoins') {
1488                     $this->joinMethods[] = $name;
1489                 }
1490             }
1491         }
1492         if (!is_null($cond)) {
1493             if ($cond instanceof PlFilterCondition) {
1494                 $this->setCondition($cond);
1495             }
1496         }
1497         if (!is_null($sort)) {
1498             if ($sort instanceof UserFilterOrder) {
1499                 $this->addSort($sort);
1500             } else if (is_array($sort)) {
1501                 foreach ($sort as $s) {
1502                     $this->addSort($s);
1503                 }
1504             }
1505         }
1506     }
1507
1508     private function buildQuery()
1509     {
1510         if (is_null($this->orderby)) {
1511             $orders = array();
1512             foreach ($this->sort as $sort) {
1513                 $orders = array_merge($orders, $sort->buildSort($this));
1514             }
1515             if (count($orders) == 0) {
1516                 $this->orderby = '';
1517             } else {
1518                 $this->orderby = 'ORDER BY  ' . implode(', ', $orders);
1519             }
1520         }
1521         if (is_null($this->query)) {
1522             $where = $this->root->buildCondition($this);
1523             if ($this->with_forced_sn) {
1524                 $this->requireProfiles();
1525                 $from = 'search_name AS sn';
1526             } else if ($this->with_accounts) {
1527                 $from = 'accounts AS a';
1528             } else {
1529                 $this->requireProfiles();
1530                 $from = 'profiles AS p';
1531             }
1532             $joins = $this->buildJoins();
1533             $this->query = 'FROM  ' . $from . '
1534                                ' . $joins . '
1535                            WHERE  (' . $where . ')';
1536         }
1537     }
1538
1539     private function getUIDList($uids = null, PlLimit &$limit)
1540     {
1541         $this->requireAccounts();
1542         $this->buildQuery();
1543         $lim = $limit->getSql();
1544         $cond = '';
1545         if (!is_null($uids)) {
1546             $cond = ' AND a.uid IN ' . XDB::formatArray($uids);
1547         }
1548         $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS  a.uid
1549                                     ' . $this->query . $cond . '
1550                                    GROUP BY  a.uid
1551                                     ' . $this->orderby . '
1552                                     ' . $lim);
1553         $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
1554         return $fetched;
1555     }
1556
1557     private function getPIDList($pids = null, PlLimit &$limit)
1558     {
1559         $this->requireProfiles();
1560         $this->buildQuery();
1561         $lim = $limit->getSql();
1562         $cond = '';
1563         if (!is_null($pids)) {
1564             $cond = ' AND p.pid IN ' . XDB::formatArray($pids);
1565         }
1566         $fetched = XDB::fetchColumn('SELECT  SQL_CALC_FOUND_ROWS  p.pid
1567                                     ' . $this->query . $cond . '
1568                                    GROUP BY  p.pid
1569                                     ' . $this->orderby . '
1570                                     ' . $lim);
1571         $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
1572         return $fetched;
1573     }
1574
1575     /** Check that the user match the given rule.
1576      */
1577     public function checkUser(PlUser &$user)
1578     {
1579         $this->requireAccounts();
1580         $this->buildQuery();
1581         $count = (int)XDB::fetchOneCell('SELECT  COUNT(*)
1582                                         ' . $this->query . XDB::format(' AND a.uid = {?}', $user->id()));
1583         return $count == 1;
1584     }
1585
1586     /** Check that the profile match the given rule.
1587      */
1588     public function checkProfile(Profile &$profile)
1589     {
1590         $this->requireProfiles();
1591         $this->buildQuery();
1592         $count = (int)XDB::fetchOneCell('SELECT  COUNT(*)
1593                                         ' . $this->query . XDB::format(' AND p.pid = {?}', $profile->id()));
1594         return $count == 1;
1595     }
1596
1597     /** Default filter is on users
1598      */
1599     public function filter(array $users, PlLimit &$limit)
1600     {
1601         return $this->filterUsers($users, $limit);
1602     }
1603
1604     /** Filter a list of users to extract the users matching the rule.
1605      */
1606     public function filterUsers(array $users, PlLimit &$limit)
1607     {
1608         $this->requireAccounts();
1609         $this->buildQuery();
1610         $table = array();
1611         $uids  = array();
1612         foreach ($users as $user) {
1613             if ($user instanceof PlUser) {
1614                 $uid = $user->id();
1615             } else {
1616                 $uid = $user;
1617             }
1618             $uids[] = $uid;
1619             $table[$uid] = $user;
1620         }
1621         $fetched = $this->getUIDList($uids, $limit);
1622         $output = array();
1623         foreach ($fetched as $uid) {
1624             $output[] = $table[$uid];
1625         }
1626         return $output;
1627     }
1628
1629     /** Filter a list of profiles to extract the users matching the rule.
1630      */
1631     public function filterProfiles(array $profiles, PlLimit &$limit)
1632     {
1633         $this->requireProfiles();
1634         $this->buildQuery();
1635         $table = array();
1636         $pids  = array();
1637         foreach ($profiles as $profile) {
1638             if ($profile instanceof Profile) {
1639                 $pid = $profile->id();
1640             } else {
1641                 $pid = $profile;
1642             }
1643             $pids[] = $pid;
1644             $table[$pid] = $profile;
1645         }
1646         $fetched = $this->getPIDList($pids, $limit);
1647         $output = array();
1648         foreach ($fetched as $pid) {
1649             $output[] = $table[$pid];
1650         }
1651         return $output;
1652     }
1653
1654     public function getUIDs(PlLimit &$limit)
1655     {
1656         return $this->getUIDList(null, $limit);
1657     }
1658
1659     public function getPIDs(PlLimit &$limit)
1660     {
1661         return $this->getPIDList(null, $limit);
1662     }
1663
1664     public function getUsers(PlLimit &$limit)
1665     {
1666         return User::getBulkUsersWithUIDs($this->getUIDs($limit));
1667     }
1668
1669     public function getProfiles(PlLimit &$limit)
1670     {
1671         return Profile::getBulkProfilesWithPIDs($this->getPIDs($limit));
1672     }
1673
1674     public function get(PlLimit &$limit)
1675     {
1676         return $this->getUsers($limit);
1677     }
1678
1679     public function getTotalCount()
1680     {
1681         if (is_null($this->lastcount)) {
1682             $this->buildQuery();
1683             if ($this->with_accounts) {
1684                 $field = 'a.uid';
1685             } else {
1686                 $field = 'p.pid';
1687             }
1688             return (int)XDB::fetchOneCell('SELECT  COUNT(DISTINCT ' . $field . ')
1689                                           ' . $this->query);
1690         } else {
1691             return $this->lastcount;
1692         }
1693     }
1694
1695     public function setCondition(PlFilterCondition &$cond)
1696     {
1697         $this->root =& $cond;
1698         $this->query = null;
1699     }
1700
1701     public function addSort(PlFilterOrder &$sort)
1702     {
1703         $this->sort[] = $sort;
1704         $this->orderby = null;
1705     }
1706
1707     static public function getLegacy($promo_min, $promo_max)
1708     {
1709         if ($promo_min != 0) {
1710             $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min));
1711         } else {
1712             $min = new UFC_True();
1713         }
1714         if ($promo_max != 0) {
1715             $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max));
1716         } else {
1717             $max = new UFC_True();
1718         }
1719         return new UserFilter(new PFC_And($min, $max));
1720     }
1721
1722     static public function sortByName()
1723     {
1724         return array(new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME));
1725     }
1726
1727     static public function sortByPromo()
1728     {
1729         return array(new UFO_Promo(), new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME));
1730     }
1731
1732     static private function getDBSuffix($string)
1733     {
1734         return preg_replace('/[^a-z0-9]/i', '', $string);
1735     }
1736
1737
1738     /** Stores a new (and unique) table alias in the &$table table
1739      * @param   &$table Array in which the table alias must be stored
1740      * @param   $val    Value which will then be used to build the join
1741      * @return          Name of the newly created alias
1742      */
1743     private $option = 0;
1744     private function register_optional(array &$table, $val)
1745     {
1746         if (is_null($val)) {
1747             $sub   = $this->option++;
1748             $index = null;
1749         } else {
1750             $sub   = self::getDBSuffix($val);
1751             $index = $val;
1752         }
1753         $sub = '_' . $sub;
1754         $table[$sub] = $index;
1755         return $sub;
1756     }
1757
1758     /** PROFILE VS ACCOUNT
1759      */
1760     private $with_profiles  = false;
1761     private $with_accounts  = false;
1762     private $with_forced_sn = false;
1763     public function requireAccounts()
1764     {
1765         $this->with_accounts = true;
1766     }
1767
1768     public function requireProfiles()
1769     {
1770         $this->with_profiles = true;
1771     }
1772
1773     /** Forces the "FROM" to use search_name instead of accounts or profiles */
1774     public function forceSearchName()
1775     {
1776         $this->with_forced_sn = true;
1777     }
1778
1779     protected function accountJoins()
1780     {
1781         $joins = array();
1782         /** Quick search is much more efficient with sn first and PID second */
1783         if ($this->with_forced_sn) {
1784             $joins['p'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profiles', '$PID = sn.uid');
1785             if ($this->with_accounts) {
1786                 $joins['ap'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'account_profiles', '$ME.pid = $PID');
1787                 $joins['a'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'accounts', '$UID = ap.uid');
1788             }
1789         } else if ($this->with_profiles && $this->with_accounts) {
1790             $joins['ap'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'account_profiles', '$ME.uid = $UID AND FIND_IN_SET(\'owner\', ap.perms)');
1791             $joins['p'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profiles', '$PID = ap.pid');
1792         }
1793         return $joins;
1794     }
1795
1796     /** DISPLAY
1797      */
1798     const DISPLAY = 'display';
1799     private $pd = false;
1800     public function addDisplayFilter()
1801     {
1802         $this->requireProfiles();
1803         $this->pd = true;
1804         return '';
1805     }
1806
1807     protected function displayJoins()
1808     {
1809         if ($this->pd) {
1810             return array('pd' => new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_display', '$ME.pid = $PID'));
1811         } else {
1812             return array();
1813         }
1814     }
1815
1816     /** NAMES
1817      */
1818     /* name tokens */
1819     const LASTNAME  = 'lastname';
1820     const FIRSTNAME = 'firstname';
1821     const NICKNAME  = 'nickname';
1822     const PSEUDONYM = 'pseudonym';
1823     const NAME      = 'name';
1824     /* name variants */
1825     const VN_MARITAL  = 'marital';
1826     const VN_ORDINARY = 'ordinary';
1827     const VN_OTHER    = 'other';
1828     const VN_INI      = 'ini';
1829     /* display names */
1830     const DN_FULL      = 'directory_name';
1831     const DN_DISPLAY   = 'yourself';
1832     const DN_YOURSELF  = 'yourself';
1833     const DN_DIRECTORY = 'directory_name';
1834     const DN_PRIVATE   = 'private_name';
1835     const DN_PUBLIC    = 'public_name';
1836     const DN_SHORT     = 'short_name';
1837     const DN_SORT      = 'sort_name';
1838
1839     static public $name_variants = array(
1840         self::LASTNAME => array(self::VN_MARITAL, self::VN_ORDINARY),
1841         self::FIRSTNAME => array(self::VN_ORDINARY, self::VN_INI, self::VN_OTHER)
1842     );
1843
1844     static public function assertName($name)
1845     {
1846         if (!Profile::getNameTypeId($name)) {
1847             Platal::page()->kill('Invalid name type: ' . $name);
1848         }
1849     }
1850
1851     static public function isDisplayName($name)
1852     {
1853         return $name == self::DN_FULL || $name == self::DN_DISPLAY
1854             || $name == self::DN_YOURSELF || $name == self::DN_DIRECTORY
1855             || $name == self::DN_PRIVATE || $name == self::DN_PUBLIC
1856             || $name == self::DN_SHORT || $name == self::DN_SORT;
1857     }
1858
1859     private $pn  = array();
1860     public function addNameFilter($type, $variant = null)
1861     {
1862         $this->requireProfiles();
1863         if (!is_null($variant)) {
1864             $ft  = $type . '_' . $variant;
1865         } else {
1866             $ft = $type;
1867         }
1868         $sub = '_' . $ft;
1869         self::assertName($ft);
1870
1871         if (!is_null($variant) && $variant == 'other') {
1872             $sub .= $this->option++;
1873         }
1874         $this->pn[$sub] = Profile::getNameTypeId($ft);
1875         return $sub;
1876     }
1877
1878     protected function nameJoins()
1879     {
1880         $joins = array();
1881         foreach ($this->pn as $sub => $type) {
1882             $joins['pn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_name', '$ME.pid = $PID AND $ME.typeid = ' . $type);
1883         }
1884         return $joins;
1885     }
1886
1887     /** NAMETOKENS
1888      */
1889     private $with_sn = false;
1890     // Set $doingQuickSearch to true if you wish to optimize the query
1891     public function addNameTokensFilter($doingQuickSearch = false)
1892     {
1893         $this->requireProfiles();
1894         $this->with_forced_sn = ($this->with_forced_sn || $doingQuickSearch);
1895         $this->with_sn = true;
1896         return 'sn';
1897     }
1898
1899     protected function nameTokensJoins()
1900     {
1901         /* We don't return joins, since with_sn forces the SELECT to run on search_name first */
1902         if ($this->with_sn && !$this->with_forced_sn) {
1903             return array(
1904                 'sn' => new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'search_name', '$ME.uid = $PID')
1905             );
1906         } else {
1907             return array();
1908         }
1909     }
1910
1911     /** NATIONALITY
1912      */
1913
1914     private $with_nat = false;
1915     public function addNationalityFilter()
1916     {
1917         $this->with_nat = true;
1918         return 'ngc';
1919     }
1920
1921     protected function nationalityJoins()
1922     {
1923         $joins = array();
1924         if ($this->with_nat) {
1925             $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');
1926         }
1927         return $joins;
1928     }
1929
1930     /** EDUCATION
1931      */
1932     const GRADE_ING = 'Ing.';
1933     const GRADE_PHD = 'PhD';
1934     const GRADE_MST = 'M%';
1935     static public function isGrade($grade)
1936     {
1937         return $grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST;
1938     }
1939
1940     static public function assertGrade($grade)
1941     {
1942         if (!self::isGrade($grade)) {
1943             Platal::page()->killError("Diplôme non valide");
1944         }
1945     }
1946
1947     static public function promoYear($grade)
1948     {
1949         // XXX: Definition of promotion for phds and masters might change in near future.
1950         return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year';
1951     }
1952
1953     private $pepe     = array();
1954     private $with_pee = false;
1955     public function addEducationFilter($x = false, $grade = null)
1956     {
1957         $this->requireProfiles();
1958         if (!$x) {
1959             $index = $this->option;
1960             $sub   = $this->option++;
1961         } else {
1962             self::assertGrade($grade);
1963             $index = $grade;
1964             $sub   = $grade[0];
1965             $this->with_pee = true;
1966         }
1967         $sub = '_' . $sub;
1968         $this->pepe[$index] = $sub;
1969         return $sub;
1970     }
1971
1972     protected function educationJoins()
1973     {
1974         $joins = array();
1975         if ($this->with_pee) {
1976             $joins['pee'] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_enum', 'pee.abbreviation = \'X\'');
1977         }
1978         foreach ($this->pepe as $grade => $sub) {
1979             if ($this->isGrade($grade)) {
1980                 $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.eduid = pee.id AND $ME.uid = $PID');
1981                 $joins['pede' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.abbreviation LIKE ' .
1982                                                   XDB::format('{?}', $grade));
1983             } else {
1984                 $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.uid = $PID');
1985                 $joins['pee' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_enum', '$ME.id = pe' . $sub . '.eduid');
1986                 $joins['pede' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid');
1987             }
1988         }
1989         return $joins;
1990     }
1991
1992
1993     /** GROUPS
1994      */
1995     private $gpm = array();
1996     public function addGroupFilter($group = null)
1997     {
1998         $this->requireAccounts();
1999         if (!is_null($group)) {
2000             if (ctype_digit($group)) {
2001                 $index = $sub = $group;
2002             } else {
2003                 $index = $group;
2004                 $sub   = self::getDBSuffix($group);
2005             }
2006         } else {
2007             $sub = 'group_' . $this->option++;
2008             $index = null;
2009         }
2010         $sub = '_' . $sub;
2011         $this->gpm[$sub] = $index;
2012         return $sub;
2013     }
2014
2015     protected function groupJoins()
2016     {
2017         $joins = array();
2018         foreach ($this->gpm as $sub => $key) {
2019             if (is_null($key)) {
2020                 $joins['gpa' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'groupex.asso');
2021                 $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
2022             } else if (ctype_digit($key)) {
2023                 $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = ' . $key);
2024             } else {
2025                 $joins['gpa' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'groupex.asso', XDB::format('$ME.diminutif = {?}', $key));
2026                 $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
2027             }
2028         }
2029         return $joins;
2030     }
2031
2032     /** BINETS
2033      */
2034
2035     private $with_bi = false;
2036     private $with_bd = false;
2037     public function addBinetsFilter($with_enum = false)
2038     {
2039         $this->requireProfiles();
2040         $this->with_bi = true;
2041         if ($with_enum) {
2042             $this->with_bd = true;
2043             return 'bd';
2044         } else {
2045             return 'bi';
2046         }
2047     }
2048
2049     protected function binetsJoins()
2050     {
2051         $joins = array();
2052         if ($this->with_bi) {
2053             $joins['bi'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'binets_ins', '$ME.user_id = $PID');
2054         }
2055         if ($this->with_bd) {
2056             $joins['bd'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'binets_def', '$ME.id = bi.binet_id');
2057         }
2058         return $joins;
2059     }
2060
2061     /** EMAILS
2062      */
2063     private $e = array();
2064     public function addEmailRedirectFilter($email = null)
2065     {
2066         $this->requireAccounts();
2067         return $this->register_optional($this->e, $email);
2068     }
2069
2070     private $ve = array();
2071     public function addVirtualEmailFilter($email = null)
2072     {
2073         $this->addAliasFilter(self::ALIAS_FORLIFE);
2074         return $this->register_optional($this->ve, $email);
2075     }
2076
2077     const ALIAS_BEST    = 'bestalias';
2078     const ALIAS_FORLIFE = 'forlife';
2079     private $al = array();
2080     public function addAliasFilter($alias = null)
2081     {
2082         $this->requireAccounts();
2083         return $this->register_optional($this->al, $alias);
2084     }
2085
2086     protected function emailJoins()
2087     {
2088         global $globals;
2089         $joins = array();
2090         foreach ($this->e as $sub=>$key) {
2091             if (is_null($key)) {
2092                 $joins['e' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'emails', '$ME.uid = $UID AND $ME.flags != \'filter\'');
2093             } else {
2094                 $joins['e' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'emails', XDB::format('$ME.uid = $UID AND $ME.flags != \'filter\' AND $ME.email = {?}', $key));
2095             }
2096         }
2097         foreach ($this->al as $sub=>$key) {
2098             if (is_null($key)) {
2099                 $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\')');
2100             } else if ($key == self::ALIAS_BEST) {
2101                 $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)');
2102             } else if ($key == self::ALIAS_FORLIFE) {
2103                 $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type = \'a_vie\'');
2104             } else {
2105                 $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));
2106             }
2107         }
2108         foreach ($this->ve as $sub=>$key) {
2109             if (is_null($key)) {
2110                 $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', '$ME.type = \'user\'');
2111             } else {
2112                 $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', XDB::format('$ME.type = \'user\' AND $ME.alias = {?}', $key));
2113             }
2114             $joins['vr' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual_redirect', XDB::format('$ME.vid = v' . $sub . '.vid
2115                                                                                  AND ($ME.redirect IN (CONCAT(al_forlife.alias, \'@\', {?}),
2116                                                                                                        CONCAT(al_forlife.alias, \'@\', {?}),
2117                                                                                                        a.email))',
2118                                                                                 $globals->mail->domain, $globals->mail->domain2));
2119         }
2120         return $joins;
2121     }
2122
2123
2124     /** ADDRESSES
2125      */
2126     private $with_pa = false;
2127     public function addAddressFilter()
2128     {
2129         $this->requireProfiles();
2130         $this->with_pa = true;
2131         return 'pa';
2132     }
2133
2134     private $with_pac = false;
2135     public function addAddressCountryFilter()
2136     {
2137         $this->requireProfiles();
2138         $this->addAddressFilter();
2139         $this->with_pac = true;
2140         return 'gc';
2141     }
2142
2143     private $with_pal = false;
2144     public function addAddressLocalityFilter()
2145     {
2146         $this->requireProfiles();
2147         $this->addAddressFilter();
2148         $this->with_pal = true;
2149         return 'gl';
2150     }
2151
2152     protected function addressJoins()
2153     {
2154         $joins = array();
2155         if ($this->with_pa) {
2156             $joins['pa'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_addresses', '$ME.pid = $PID');
2157         }
2158         if ($this->with_pac) {
2159             $joins['gc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'geoloc_countries', '$ME.iso_3166_1_a2 = pa.countryID');
2160         }
2161         if ($this->with_pal) {
2162             $joins['gl'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'geoloc_localities', '$ME.id = pa.localityID');
2163         }
2164         return $joins;
2165     }
2166
2167
2168     /** CORPS
2169      */
2170
2171     private $pc = false;
2172     private $pce = array();
2173     private $pcr = false;
2174     public function addCorpsFilter($type)
2175     {
2176         $this->requireProfiles();
2177         $this->pc = true;
2178         if ($type == UFC_Corps::CURRENT) {
2179             $pce['pcec'] = 'current_corpsid';
2180             return 'pcec';
2181         } else if ($type == UFC_Corps::ORIGIN) {
2182             $pce['pceo'] = 'original_corpsid';
2183             return 'pceo';
2184         }
2185     }
2186
2187     public function addCorpsRankFilter()
2188     {
2189         $this->requireProfiles();
2190         $this->pc = true;
2191         $this->pcr = true;
2192         return 'pcr';
2193     }
2194
2195     protected function corpsJoins()
2196     {
2197         $joins = array();
2198         if ($this->pc) {
2199             $joins['pc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps', '$ME.uid = $UID');
2200         }
2201         if ($this->pcr) {
2202             $joins['pcr'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_rank_enum', '$ME.id = pc.rankid');
2203         }
2204         foreach($this->pce as $sub => $field) {
2205             $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_enum', '$ME.id = pc.' . $field);
2206         }
2207         return $joins;
2208     }
2209
2210     /** JOBS
2211      */
2212
2213     const JOB_SECTOR        = 0x0001;
2214     const JOB_SUBSECTOR     = 0x0002;
2215     const JOB_SUBSUBSECTOR  = 0x0004;
2216     const JOB_ALTERNATES    = 0x0008;
2217     const JOB_USERDEFINED   = 0x0010;
2218     const JOB_CV            = 0x0020;
2219
2220     const JOB_SECTORIZATION = 0x000F;
2221     const JOB_ANY           = 0x003F;
2222
2223     /** Joins :
2224      * pj => profile_job
2225      * pje => profile_job_enum
2226      * pjse => profile_job_sector_enum
2227      * pjsse => profile_job_subsector_enum
2228      * pjssse => profile_job_subsubsector_enum
2229      * pja => profile_job_alternates
2230      */
2231     private $with_pj = false;
2232     private $with_pje = false;
2233     private $with_pjse = false;
2234     private $with_pjsse = false;
2235     private $with_pjssse = false;
2236     private $with_pja = false;
2237
2238     public function addJobFilter()
2239     {
2240         $this->requireProfiles();
2241         $this->with_pj = true;
2242         return 'pj';
2243     }
2244
2245     public function addJobCompanyFilter()
2246     {
2247         $this->addJobFilter();
2248         $this->with_pje = true;
2249         return 'pje';
2250     }
2251
2252     public function addJobSectorizationFilter($type)
2253     {
2254         $this->addJobFilter();
2255         if ($type == self::JOB_SECTOR) {
2256             $this->with_pjse = true;
2257             return 'pjse';
2258         } else if ($type == self::JOB_SUBSECTOR) {
2259             $this->with_pjsse = true;
2260             return 'pjsse';
2261         } else if ($type == self::JOB_SUBSUBSECTOR) {
2262             $this->with_pjssse = true;
2263             return 'pjssse';
2264         } else if ($type == self::JOB_ALTERNATES) {
2265             $this->with_pja = true;
2266             return 'pja';
2267         }
2268     }
2269
2270     protected function jobJoins()
2271     {
2272         $joins = array();
2273         if ($this->with_pj) {
2274             $joins['pj'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job', '$ME.uid = $UID');
2275         }
2276         if ($this->with_pje) {
2277             $joins['pje'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_enum', '$ME.id = pj.jobid');
2278         }
2279         if ($this->with_pjse) {
2280             $joins['pjse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_sector_enum', '$ME.id = pj.sectorid');
2281         }
2282         if ($this->with_pjsse) {
2283             $joins['pjsse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_subsector_enum', '$ME.id = pj.subsectorid');
2284         }
2285         if ($this->with_pjssse) {
2286             $joins['pjssse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_subsubsector_enum', '$ME.id = pj.subsubsectorid');
2287         }
2288         if ($this->with_pja) {
2289             $joins['pja'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_alternates', '$ME.subsubsectorid = pj.subsubsectorid');
2290         }
2291         return $joins;
2292     }
2293
2294     /** NETWORKING
2295      */
2296
2297     private $with_pnw = false;
2298     public function addNetworkingFilter()
2299     {
2300         $this->requireAccounts();
2301         $this->with_pnw = true;
2302         return 'pnw';
2303     }
2304
2305     protected function networkingJoins()
2306     {
2307         $joins = array();
2308         if ($this->with_pnw) {
2309             $joins['pnw'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_networking', '$ME.uid = $UID');
2310         }
2311         return $joins;
2312     }
2313
2314     /** PHONE
2315      */
2316
2317     private $with_ptel = false;
2318
2319     public function addPhoneFilter()
2320     {
2321         $this->requireAccounts();
2322         $this->with_ptel = true;
2323         return 'ptel';
2324     }
2325
2326     protected function phoneJoins()
2327     {
2328         $joins = array();
2329         if ($this->with_ptel) {
2330             $joins['ptel'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_phones', '$ME.uid = $UID');
2331         }
2332         return $joins;
2333     }
2334
2335     /** MEDALS
2336      */
2337
2338     private $with_pmed = false;
2339     public function addMedalFilter()
2340     {
2341         $this->requireProfiles();
2342         $this->with_pmed = true;
2343         return 'pmed';
2344     }
2345
2346     protected function medalJoins()
2347     {
2348         $joins = array();
2349         if ($this->with_pmed) {
2350             $joins['pmed'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_medals_sub', '$ME.uid = $UID');
2351         }
2352         return $joins;
2353     }
2354
2355     /** MENTORING
2356      */
2357
2358     private $pms = array();
2359     const MENTOR_EXPERTISE  = 1;
2360     const MENTOR_COUNTRY    = 2;
2361     const MENTOR_SECTOR     = 3;
2362
2363     public function addMentorFilter($type)
2364     {
2365         $this->requireAccounts();
2366         switch($type) {
2367         case MENTOR_EXPERTISE:
2368             $pms['pme'] = 'profile_mentor';
2369             return 'pme';
2370         case MENTOR_COUNTRY:
2371             $pms['pmc'] = 'profile_mentor_country';
2372             return 'pmc';
2373         case MENTOR_SECTOR:
2374             $pms['pms'] =  'profile_mentor_sector';
2375             return 'pms';
2376         default:
2377             Platal::page()->killError("Undefined mentor filter.");
2378         }
2379     }
2380
2381     protected function mentorJoins()
2382     {
2383         $joins = array();
2384         foreach ($this->pms as $sub => $tab) {
2385             $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, $tab, '$ME.uid = $UID');
2386         }
2387         return $joins;
2388     }
2389
2390     /** CONTACTS
2391      */
2392     private $cts = array();
2393     public function addContactFilter($uid = null)
2394     {
2395         $this->requireAccounts();
2396         return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid);
2397     }
2398
2399     protected function contactJoins()
2400     {
2401         $joins = array();
2402         foreach ($this->cts as $sub=>$key) {
2403             if (is_null($key)) {
2404                 $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', '$ME.contact = $UID');
2405             } else {
2406                 $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', XDB::format('$ME.uid = {?} AND $ME.contact = $UID', substr($key, 5)));
2407             }
2408         }
2409         return $joins;
2410     }
2411
2412
2413     /** CARNET
2414      */
2415     private $wn = array();
2416     public function addWatchRegistrationFilter($uid = null)
2417     {
2418         $this->requireAccounts();
2419         return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid);
2420     }
2421
2422     private $wp = array();
2423     public function addWatchPromoFilter($uid = null)
2424     {
2425         $this->requireAccounts();
2426         return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid);
2427     }
2428
2429     private $w = array();
2430     public function addWatchFilter($uid = null)
2431     {
2432         $this->requireAccounts();
2433         return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid);
2434     }
2435
2436     protected function watchJoins()
2437     {
2438         $joins = array();
2439         foreach ($this->w as $sub=>$key) {
2440             if (is_null($key)) {
2441                 $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch');
2442             } else {
2443                 $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch', XDB::format('$ME.uid = {?}', substr($key, 5)));
2444             }
2445         }
2446         foreach ($this->wn as $sub=>$key) {
2447             if (is_null($key)) {
2448                 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', '$ME.ni_id = $UID');
2449             } else {
2450                 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5)));
2451             }
2452         }
2453         foreach ($this->wn as $sub=>$key) {
2454             if (is_null($key)) {
2455                 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', '$ME.ni_id = $UID');
2456             } else {
2457                 $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5)));
2458             }
2459         }
2460         foreach ($this->wp as $sub=>$key) {
2461             if (is_null($key)) {
2462                 $joins['wp' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_promo');
2463             } else {
2464                 $joins['wp' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_promo', XDB::format('$ME.uid = {?}', substr($key, 5)));
2465             }
2466         }
2467         return $joins;
2468     }
2469 }
2470 // }}}
2471
2472 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
2473 ?>