classes/userfilter.php

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