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