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