classes/userfilter.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2009 Polytechnique.org                              *
   4  *  http://opensource.polytechnique.org/                                   *
   5  *                                                                         *
   6  *  This program is free software; you can redistribute it and/or modify   *
   7  *  it under the terms of the GNU General Public License as published by   *
   8  *  the Free Software Foundation; either version 2 of the License, or      *
   9  *  (at your option) any later version.                                    *
  10  *                                                                         *
  11  *  This program is distributed in the hope that it will be useful,        *
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14  *  GNU General Public License for more details.                           *
  15  *                                                                         *
  16  *  You should have received a copy of the GNU General Public License      *
  17  *  along with this program; if not, write to the Free Software            *
  18  *  Foundation, Inc.,                                                      *
  19  *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20  ***************************************************************************/
  21
  22
  23 /******************
  24  * CONDITIONS
  25  ******************/
  26
  27 interface UserFilterCondition
  28 {
  29     const COND_TRUE  = 'TRUE';
  30     const COND_FALSE = 'FALSE';
  31
  32     /** Check that the given user matches the rule.
  33      */
  34     public function buildCondition(UserFilter &$uf);
  35 }
  36
  37 abstract class UFC_OneChild implements UserFilterCondition
  38 {
  39     protected $child;
  40
  41     public function __construct($child = null)
  42     {
  43         if (!is_null($child) && ($child instanceof UserFilterCondition)) {
  44             $this->setChild($child);
  45         }
  46     }
  47
  48     public function setChild(UserFilterCondition &$cond)
  49     {
  50         $this->child =& $cond;
  51     }
  52 }
  53
  54 abstract class UFC_NChildren implements UserFilterCondition
  55 {
  56     protected $children = array();
  57
  58     public function __construct()
  59     {
  60         $children = func_get_args();
  61         foreach ($children as &$child) {
  62             if (!is_null($child) && ($child instanceof UserFilterCondition)) {
  63                 $this->addChild($child);
  64             }
  65         }
  66     }
  67
  68     public function addChild(UserFilterCondition &$cond)
  69     {
  70         $this->children[] =& $cond;
  71     }
  72
  73     protected function catConds(array $cond, $op, $fallback)
  74     {
  75         if (count($cond) == 0) {
  76             return $fallback;
  77         } else if (count($cond) == 1) {
  78             return $cond[0];
  79         } else {
  80             return '(' . implode(') ' . $op . ' (', $cond) . ')';
  81         }
  82     }
  83 }
  84
  85 class UFC_True implements UserFilterCondition
  86 {
  87     public function buildCondition(UserFilter &$uf)
  88     {
  89         return self::COND_TRUE;
  90     }
  91 }
  92
  93 class UFC_False implements UserFilterCondition
  94 {
  95     public function buildCondition(UserFilter &$uf)
  96     {
  97         return self::COND_FALSE;
  98     }
  99 }
 100
 101 class UFC_Not extends UFC_OneChild
 102 {
 103     public function buildCondition(UserFilter &$uf)
 104     {
 105         $val = $this->child->buildCondition($uf);
 106         if ($val == self::COND_TRUE) {
 107             return self::COND_FALSE;
 108         } else if ($val == self::COND_FALSE) {
 109             return self::COND_TRUE;
 110         } else {
 111             return 'NOT (' . $val . ')';
 112         }
 113     }
 114 }
 115
 116 class UFC_And extends UFC_NChildren
 117 {
 118     public function buildCondition(UserFilter &$uf)
 119     {
 120         if (empty($this->children)) {
 121             return self::COND_FALSE;
 122         } else {
 123             $true = self::COND_FALSE;
 124             $conds = array();
 125             foreach ($this->children as &$child) {
 126                 $val = $child->buildCondition($uf);
 127                 if ($val == self::COND_TRUE) {
 128                     $true = self::COND_TRUE;
 129                 } else if ($val == self::COND_FALSE) {
 130                     return self::COND_FALSE;
 131                 } else {
 132                     $conds[] = $val;
 133                 }
 134             }
 135             return $this->catConds($conds, 'AND', $true);
 136         }
 137     }
 138 }
 139
 140 class UFC_Or extends UFC_NChildren
 141 {
 142     public function buildCondition(UserFilter &$uf)
 143     {
 144         if (empty($this->children)) {
 145             return self::COND_TRUE;
 146         } else {
 147             $true = self::COND_TRUE;
 148             $conds = array();
 149             foreach ($this->children as &$child) {
 150                 $val = $child->buildCondition($uf);
 151                 if ($val == self::COND_TRUE) {
 152                     return self::COND_TRUE;
 153                 } else if ($val == self::COND_FALSE) {
 154                     $true = self::COND_FALSE;
 155                 } else {
 156                     $conds[] = $val;
 157                 }
 158             }
 159             return $this->catConds($conds, 'OR', $true);
 160         }
 161     }
 162 }
 163
 164 /** Filters users who have a profile
 165  */
 166 class UFC_Profile implements UserFilterCondition
 167 {
 168     public function buildCondition(UserFilter &$uf)
 169     {
 170         return '$PID IS NOT NULL';
 171     }
 172 }
 173
 174 /** Filters users based on promo
 175  * @param $comparison Comparison operator (>, =, ...)
 176  * @param $grade Formation on which to restrict, UserFilter::DISPLAY for "any formation"
 177  * @param $promo Promo on which the filter is based
 178  */
 179 class UFC_Promo implements UserFilterCondition
 180 {
 181
 182     private $grade;
 183     private $promo;
 184     private $comparison;
 185
 186     public function __construct($comparison, $grade, $promo)
 187     {
 188         $this->grade = $grade;
 189         $this->comparison = $comparison;
 190         $this->promo = $promo;
 191         if ($this->grade != UserFilter::DISPLAY) {
 192             UserFilter::assertGrade($this->grade);
 193         }
 194     }
 195
 196     public function buildCondition(UserFilter &$uf)
 197     {
 198         if ($this->grade == UserFilter::DISPLAY) {
 199             $sub = $uf->addDisplayFilter();
 200             return XDB::format('pd' . $sub . '.promo = {?}', $this->promo);
 201         } else {
 202             $sub = $uf->addEducationFilter(true, $this->grade);
 203             $field = 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
 204             return $field . ' IS NOT NULL AND ' . $field . ' ' . $this->comparison . ' ' . XDB::format('{?}', $this->promo);
 205         }
 206     }
 207 }
 208
 209 /** Filters users based on name
 210  * @param $type Type of name field on which filtering is done (firstname, lastname, ...)
 211  * @param $text Text on which to filter
 212  * @param $mode flag indicating search type (prefix, suffix, with particule, ...)
 213  */
 214 class UFC_Name implements UserFilterCondition
 215 {
 216     const PREFIX   = 1;
 217     const SUFFIX   = 2;
 218     const PARTICLE = 7;
 219     const VARIANTS = 8;
 220     const CONTAINS = 3;
 221
 222     private $type;
 223     private $text;
 224     private $mode;
 225
 226     public function __construct($type, $text, $mode)
 227     {
 228         $this->type = $type;
 229         $this->text = $text;
 230         $this->mode = $mode;
 231     }
 232
 233     private function buildNameQuery($type, $variant, $where, UserFilter &$uf)
 234     {
 235         $sub = $uf->addNameFilter($type, $variant);
 236         return str_replace('$ME', 'pn' . $sub, $where);
 237     }
 238
 239     public function buildCondition(UserFilter &$uf)
 240     {
 241         $left = '$ME.name';
 242         $op   = ' LIKE ';
 243         if (($this->mode & self::PARTICLE) == self::PARTICLE) {
 244             $left = 'CONCAT($ME.particle, \' \', $ME.name)';
 245         }
 246         if (($this->mode & self::CONTAINS) == 0) {
 247             $right = XDB::format('{?}', $this->text);
 248             $op    = ' = ';
 249         } else if (($this->mode & self::CONTAINS) == self::PREFIX) {
 250             $right = XDB::format('CONCAT({?}, \'%\')', $this->text);
 251         } else if (($this->mode & self::CONTAINS) == self::SUFFIX) {
 252             $right = XDB::format('CONCAT(\'%\', {?})', $this->text);
 253         } else {
 254             $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text);
 255         }
 256         $cond = $left . $op . $right;
 257         $conds = array($this->buildNameQuery($this->type, null, $cond, $uf));
 258         if (($this->mode & self::VARIANTS) != 0 && isset(UserFilter::$name_variants[$this->type])) {
 259             foreach (UserFilter::$name_variants[$this->type] as $var) {
 260                 $conds[] = $this->buildNameQuery($this->type, $var, $cond, $uf);
 261             }
 262         }
 263         return implode(' OR ', $conds);
 264     }
 265 }
 266
 267 /** Filters users based on death date
 268  * @param $comparison Comparison operator
 269  * @param $date Date to which death date should be compared
 270  */
 271 class UFC_Dead implements UserFilterCondition
 272 {
 273     private $comparison;
 274     private $date;
 275
 276     public function __construct($comparison = null, $date = null)
 277     {
 278         $this->comparison = $comparison;
 279         $this->date = $date;
 280     }
 281
 282     public function buildCondition(UserFilter &$uf)
 283     {
 284         $str = 'p.deathdate IS NOT NULL';
 285         if (!is_null($this->comparison)) {
 286             $str .= ' AND p.deathdate ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date));
 287         }
 288         return $str;
 289     }
 290 }
 291
 292 /** Filters users based on registration state
 293  * @param $active Whether we want to use only "active" users (i.e with a valid redirection)
 294  * @param $comparison Comparison operator
 295  * @param $date Date to which users registration date should be compared
 296  */
 297 class UFC_Registered implements UserFilterCondition
 298 {
 299     private $active;
 300     private $comparison;
 301     private $date;
 302
 303     public function __construct($active = false, $comparison = null, $date = null)
 304     {
 305         $this->only_active = $active;
 306         $this->comparison = $comparison;
 307         $this->date = $date;
 308     }
 309
 310     public function buildCondition(UserFilter &$uf)
 311     {
 312         if ($this->active) {
 313             $date = 'a.uid IS NOT NULL AND a.state = \'active\'';
 314         } else {
 315             $date = 'a.uid IS NOT NULL AND a.state != \'pending\'';
 316         }
 317         if (!is_null($this->comparison)) {
 318             $date .= ' AND a.registration_date ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date));
 319         }
 320         return $date;
 321     }
 322 }
 323
 324 /** Filters users based on profile update date
 325  * @param $comparison Comparison operator
 326  * @param $date Date to which profile update date must be compared
 327  */
 328 class UFC_ProfileUpdated implements UserFilterCondition
 329 {
 330     private $comparison;
 331     private $date;
 332
 333     public function __construct($comparison = null, $date = null)
 334     {
 335         $this->comparison = $comparison;
 336         $this->date = $date;
 337     }
 338
 339     public function buildCondition(UserFilter &$uf)
 340     {
 341         return 'p.last_change ' . $this->comparison . XDB::format(' {?}', date('Y-m-d H:i:s', $this->date));
 342     }
 343 }
 344
 345 /** Filters users based on next birthday date
 346  * @param $comparison Comparison operator
 347  * @param $date Date to which users next birthday date should be compared
 348  */
 349 class UFC_Birthday implements UserFilterCondition
 350 {
 351     private $comparison;
 352     private $date;
 353
 354     public function __construct($comparison = null, $date = null)
 355     {
 356         $this->comparison = $comparison;
 357         $this->date = $date;
 358     }
 359
 360     public function buildCondition(UserFilter &$uf)
 361     {
 362         return 'p.next_birthday ' . $this->comparison . XDB::format(' {?}', date('Y-m-d', $this->date));
 363     }
 364 }
 365
 366 /** Filters users based on sex
 367  * @parm $sex One of User::GENDER_MALE or User::GENDER_FEMALE, for selecting users
 368  */
 369 class UFC_Sex implements UserFilterCondition
 370 {
 371     private $sex;
 372     public function __construct($sex)
 373     {
 374         $this->sex = $sex;
 375     }
 376
 377     public function buildCondition(UserFilter &$uf)
 378     {
 379         if ($this->sex != User::GENDER_MALE && $this->sex != User::GENDER_FEMALE) {
 380             return self::COND_FALSE;
 381         } else {
 382             return XDB::format('p.sex = {?}', $this->sex == User::GENDER_FEMALE ? 'female' : 'male');
 383         }
 384     }
 385 }
 386
 387 /** Filters users based on group membership
 388  * @param $group Group whose member we are selecting
 389  * @param $admin Whether to restrict selection to admins of that group
 390  */
 391 class UFC_Group implements UserFilterCondition
 392 {
 393     private $group;
 394     private $admin;
 395     public function __construct($group, $admin = false)
 396     {
 397         $this->group = $group;
 398         $this->admin = $admin;
 399     }
 400
 401     public function buildCondition(UserFilter &$uf)
 402     {
 403         $sub = $uf->addGroupFilter($this->group);
 404         $where = 'gpm' . $sub . '.perms IS NOT NULL';
 405         if ($this->admin) {
 406             $where .= ' AND gpm' . $sub . '.perms = \'admin\'';
 407         }
 408         return $where;
 409     }
 410 }
 411
 412 /** Filters users based on email address
 413  * @param $email Email whose owner we are looking for
 414  */
 415 class UFC_Email implements UserFilterCondition
 416 {
 417     private $email;
 418     public function __construct($email)
 419     {
 420         $this->email = $email;
 421     }
 422
 423     public function buildCondition(UserFilter &$uf)
 424     {
 425         if (User::isForeignEmailAddress($this->email)) {
 426             $sub = $uf->addEmailRedirectFilter($this->email);
 427             return XDB::format('e' . $sub . '.email IS NOT NULL OR a.email = {?}', $this->email);
 428         } else if (User::isVirtualEmailAddress($this->email)) {
 429             $sub = $uf->addVirtualEmailFilter($this->email);
 430             return 'vr' . $sub . '.redirect IS NOT NULL';
 431         } else {
 432             @list($user, $domain) = explode('@', $this->email);
 433             $sub = $uf->addAliasFilter($user);
 434             return 'al' . $sub . '.alias IS NOT NULL';
 435         }
 436     }
 437 }
 438
 439 /** Filters users base on an email list
 440  * @param $emails List of emails whose owner must be selected
 441  */
 442 class UFC_EmailList implements UserFilterCondition
 443 {
 444     private $emails;
 445     public function __construct($emails)
 446     {
 447         $this->emails = $emails;
 448     }
 449
 450     public function buildCondition(UserFilter &$uf)
 451     {
 452         $email   = null;
 453         $virtual = null;
 454         $alias   = null;
 455         $cond = array();
 456
 457         if (count($this->emails) == 0) {
 458             return UserFilterCondition::COND_TRUE;
 459         }
 460
 461         foreach ($this->emails as $entry) {
 462             if (User::isForeignEmailAddress($entry)) {
 463                 if (is_null($email)) {
 464                     $email = $uf->addEmailRedirectFilter();
 465                 }
 466                 $cond[] = XDB::format('e' . $email . '.email = {?} OR a.email = {?}', $entry, $entry);
 467             } else if (User::isVirtualEmailAddress($entry)) {
 468                 if (is_null($virtual)) {
 469                     $virtual = $uf->addVirtualEmailFilter();
 470                 }
 471                 $cond[] = XDB::format('vr' . $virtual . '.redirect IS NOT NULL AND v' . $virtual . '.alias = {?}', $entry);
 472             } else {
 473                 if (is_null($alias)) {
 474                     $alias = $uf->addAliasFilter();
 475                 }
 476                 @list($user, $domain) = explode('@', $entry);
 477                 $cond[] = XDB::format('al' . $alias . '.alias = {?}', $user);
 478             }
 479         }
 480         return '(' . implode(') OR (', $cond) . ')';
 481     }
 482 }
 483
 484 /** Filters users based on a relation toward on user
 485  * @param $user User to which searched users are related
 486  */
 487 abstract class UFC_UserRelated implements UserFilterCondition
 488 {
 489     protected $user;
 490     public function __construct(PlUser &$user)
 491     {
 492         $this->user =& $user;
 493     }
 494 }
 495
 496 /** Filters users who belongs to selected user's contacts
 497  */
 498 class UFC_Contact extends UFC_UserRelated
 499 {
 500     public function buildCondition(UserFilter &$uf)
 501     {
 502         $sub = $uf->addContactFilter($this->user->id());
 503         return 'c' . $sub . '.contact IS NOT NULL';
 504     }
 505 }
 506
 507 /** Filters users being watched by selected user
 508  */
 509 class UFC_WatchRegistration extends UFC_UserRelated
 510 {
 511     public function buildCondition(UserFilter &$uf)
 512     {
 513         if (!$this->user->watch('registration')) {
 514             return UserFilterCondition::COND_FALSE;
 515         }
 516         $uids = $this->user->watchUsers();
 517         if (count($uids) == 0) {
 518             return UserFilterCondition::COND_FALSE;
 519         } else {
 520             return '$UID IN ' . XDB::formatArray($uids);
 521         }
 522     }
 523 }
 524
 525 /** Filters users belonging to a promo watched by selected user
 526  * @param $user Selected user (the one watching promo)
 527  * @param $grade Formation the user is watching
 528  */
 529 class UFC_WatchPromo extends UFC_UserRelated
 530 {
 531     private $grade;
 532     public function __construct(PlUser &$user, $grade = UserFilter::GRADE_ING)
 533     {
 534         parent::__construct($user);
 535         $this->grade = $grade;
 536     }
 537
 538     public function buildCondition(UserFilter &$uf)
 539     {
 540         $promos = $this->user->watchPromos();
 541         if (count($promos) == 0) {
 542             return UserFilterCondition::COND_FALSE;
 543         } else {
 544             $sube = $uf->addEducationFilter(true, $this->grade);
 545             $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade);
 546             return $field . ' IN ' . XDB::formatArray($promos);
 547         }
 548     }
 549 }
 550
 551 /** Filters users watched by selected user
 552  */
 553 class UFC_WatchContact extends UFC_Contact
 554 {
 555     public function buildCondition(UserFilter &$uf)
 556     {
 557         if (!$this->user->watchContacts()) {
 558             return UserFilterCondition::COND_FALSE;
 559         }
 560         return parent::buildCondition($uf);
 561     }
 562 }
 563
 564
 565 /******************
 566  * ORDERS
 567  ******************/
 568
 569 abstract class UserFilterOrder
 570 {
 571     protected $desc = false;
 572     public function __construct($desc = false)
 573     {
 574         $this->desc = $desc;
 575     }
 576
 577     public function buildSort(UserFilter &$uf)
 578     {
 579         $sel = $this->getSortTokens($uf);
 580         if (!is_array($sel)) {
 581             $sel = array($sel);
 582         }
 583         if ($this->desc) {
 584             foreach ($sel as $k=>$s) {
 585                 $sel[$k] = $s . ' DESC';
 586             }
 587         }
 588         return $sel;
 589     }
 590
 591     abstract protected function getSortTokens(UserFilter &$uf);
 592 }
 593
 594 /** Orders users by promo
 595  * @param $grade Formation whose promo users should be sorted by (restricts results to users of that formation)
 596  * @param $desc Whether sort is descending
 597  */
 598 class UFO_Promo extends UserFilterOrder
 599 {
 600     private $grade;
 601
 602     public function __construct($grade = null, $desc = false)
 603     {
 604         parent::__construct($desc);
 605         $this->grade = $grade;
 606     }
 607
 608     protected function getSortTokens(UserFilter &$uf)
 609     {
 610         if (UserFilter::isGrade($this->grade)) {
 611             $sub = $uf->addEducationFilter($this->grade);
 612             return 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
 613         } else {
 614             $sub = $uf->addDisplayFilter();
 615             return 'pd' . $sub . '.promo';
 616         }
 617     }
 618 }
 619
 620 /** Sorts users by name
 621  * @param $type Type of name on which to sort (firstname, ...)
 622  * @param $variant Variant of that name to user (marital, ordinary, ...)
 623  * @param $particle Set to true if particles should be included in the sorting order
 624  * @param $desc If sort order should be descending
 625  */
 626 class UFO_Name extends UserFilterOrder
 627 {
 628     private $type;
 629     private $variant;
 630     private $particle;
 631
 632     public function __construct($type, $variant = null, $particle = false, $desc = false)
 633     {
 634         parent::__construct($desc);
 635         $this->type = $type;
 636         $this->variant = $variant;
 637         $this->particle = $particle;
 638     }
 639
 640     protected function getSortTokens(UserFilter &$uf)
 641     {
 642         if (UserFilter::isDisplayName($this->type)) {
 643             $sub = $uf->addDisplayFilter();
 644             return 'pd' . $sub . '.' . $this->type;
 645         } else {
 646             $sub = $uf->addNameFilter($this->type, $this->variant);
 647             if ($this->particle) {
 648                 return 'CONCAT(pn' . $sub . '.particle, \' \', pn' . $sub . '.name)';
 649             } else {
 650                 return 'pn' . $sub . '.name';
 651             }
 652         }
 653     }
 654 }
 655
 656 /** Sorts users based on registration date
 657  */
 658 class UFO_Registration extends UserFilterOrder
 659 {
 660     protected function getSortTokens(UserFilter &$uf)
 661     {
 662         return 'a.registration_date';
 663     }
 664 }
 665
 666 /** Sorts users based on next birthday date
 667  */
 668 class UFO_Birthday extends UserFilterOrder
 669 {
 670     protected function getSortTokens(UserFilter &$uf)
 671     {
 672         return 'p.next_birthday';
 673     }
 674 }
 675
 676 /** Sorts users based on last profile update
 677  */
 678 class UFO_ProfileUpdate extends UserFilterOrder
 679 {
 680     protected function getSortTokens(UserFilter &$uf)
 681     {
 682         return 'p.last_change';
 683     }
 684 }
 685
 686 /** Sorts users based on death date
 687  */
 688 class UFO_Death extends UserFilterOrder
 689 {
 690     protected function getSortTokens(UserFilter &$uf)
 691     {
 692         return 'p.deathdate';
 693     }
 694 }
 695
 696
 697 /***********************************
 698   *********************************
 699           USER FILTER CLASS
 700   *********************************
 701  ***********************************/
 702
 703 class UserFilter
 704 {
 705     static private $joinMethods = array();
 706
 707     private $root;
 708     private $sort = array();
 709     private $query = null;
 710     private $orderby = null;
 711
 712     private $lastcount = null;
 713
 714     public function __construct($cond = null, $sort = null)
 715     {
 716         if (empty(self::$joinMethods)) {
 717             $class = new ReflectionClass('UserFilter');
 718             foreach ($class->getMethods() as $method) {
 719                 $name = $method->getName();
 720                 if (substr($name, -5) == 'Joins' && $name != 'buildJoins') {
 721                     self::$joinMethods[] = $name;
 722                 }
 723             }
 724         }
 725         if (!is_null($cond)) {
 726             if ($cond instanceof UserFilterCondition) {
 727                 $this->setCondition($cond);
 728             }
 729         }
 730         if (!is_null($sort)) {
 731             if ($sort instanceof UserFilterOrder) {
 732                 $this->addSort($sort);
 733             } else if (is_array($sort)) {
 734                 foreach ($sort as $s) {
 735                     $this->addSort($s);
 736                 }
 737             }
 738         }
 739     }
 740
 741     private function buildQuery()
 742     {
 743         if (is_null($this->orderby)) {
 744             $orders = array();
 745             foreach ($this->sort as $sort) {
 746                 $orders = array_merge($orders, $sort->buildSort($this));
 747             }
 748             if (count($orders) == 0) {
 749                 $this->orderby = '';
 750             } else {
 751                 $this->orderby = 'ORDER BY  ' . implode(', ', $orders);
 752             }
 753         }
 754         if (is_null($this->query)) {
 755             $where = $this->root->buildCondition($this);
 756             $joins = $this->buildJoins();
 757             $this->query = 'FROM  accounts AS a
 758                        LEFT JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms))
 759                        LEFT JOIN  profiles AS p ON (p.pid = ap.pid)
 760                                ' . $joins . '
 761                            WHERE  (' . $where . ')';
 762         }
 763     }
 764
 765     private function formatJoin(array $joins)
 766     {
 767         $str = '';
 768         foreach ($joins as $key => $infos) {
 769             $mode  = $infos[0];
 770             $table = $infos[1];
 771             if ($mode == 'inner') {
 772                 $str .= 'INNER JOIN ';
 773             } else if ($mode == 'left') {
 774                 $str .= 'LEFT JOIN ';
 775             } else {
 776                 Platal::page()->kill("Join mode error");
 777             }
 778             $str .= $table . ' AS ' . $key;
 779             if (isset($infos[2])) {
 780                 $str .= ' ON (' . str_replace(array('$ME', '$PID', '$UID'), array($key, 'p.pid', 'a.uid'), $infos[2]) . ')';
 781             }
 782             $str .= "\n";
 783         }
 784         return $str;
 785     }
 786
 787     private function buildJoins()
 788     {
 789         $joins = array();
 790         foreach (self::$joinMethods as $method) {
 791             $joins = array_merge($joins, $this->$method());
 792         }
 793         return $this->formatJoin($joins);
 794     }
 795
 796     private function getUIDList($uids = null, $count = null, $offset = null)
 797     {
 798         $this->buildQuery();
 799         $limit = '';
 800         if (!is_null($count)) {
 801             if (!is_null($offset)) {
 802                 $limit = XDB::format('LIMIT {?}, {?}', (int)$offset, (int)$count);
 803             } else {
 804                 $limit = XDB::format('LIMIT {?}', (int)$count);
 805             }
 806         }
 807         $cond = '';
 808         if (!is_null($uids)) {
 809             $cond = ' AND a.uid IN ' . XDB::formatArray($uids);
 810         }
 811         $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS  a.uid
 812                                     ' . $this->query . $cond . '
 813                                    GROUP BY  a.uid
 814                                     ' . $this->orderby . '
 815                                     ' . $limit);
 816         $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
 817         return $fetched;
 818     }
 819
 820     /** Check that the user match the given rule.
 821      */
 822     public function checkUser(PlUser &$user)
 823     {
 824         $this->buildQuery();
 825         $count = (int)XDB::fetchOneCell('SELECT  COUNT(*)
 826                                         ' . $this->query . XDB::format(' AND a.uid = {?}', $user->id()));
 827         return $count == 1;
 828     }
 829
 830     /** Filter a list of user to extract the users matching the rule.
 831      */
 832     public function filter(array $users, $count = null, $offset = null)
 833     {
 834         $this->buildQuery();
 835         $table = array();
 836         $uids  = array();
 837         foreach ($users as $user) {
 838             if ($user instanceof PlUser) {
 839                 $uid = $user->id();
 840             } else {
 841                 $uid = $user;
 842             }
 843             $uids[] = $uid;
 844             $table[$uid] = $user;
 845         }
 846         $fetched = $this->getUIDList($uids, $count, $offset);
 847         $output = array();
 848         foreach ($fetched as $uid) {
 849             $output[] = $table[$uid];
 850         }
 851         return $output;
 852     }
 853
 854     public function getUIDs($count = null, $offset = null)
 855     {
 856         return $this->getUIDList(null, $count, $offset);
 857     }
 858
 859     public function getUsers($count = null, $offset = null)
 860     {
 861         return User::getBulkUsersWithUIDs($this->getUIDs($count, $offset));
 862     }
 863
 864     public function getTotalCount()
 865     {
 866         if (is_null($this->lastcount)) {
 867             $this->buildQuery();
 868             return (int)XDB::fetchOneCell('SELECT  COUNT(DISTINCT a.uid)
 869                                           ' . $this->query);
 870         } else {
 871             return $this->lastcount;
 872         }
 873     }
 874
 875     public function setCondition(UserFilterCondition &$cond)
 876     {
 877         $this->root =& $cond;
 878         $this->query = null;
 879     }
 880
 881     public function addSort(UserFilterOrder &$sort)
 882     {
 883         $this->sort[] = $sort;
 884         $this->orderby = null;
 885     }
 886
 887     static public function getLegacy($promo_min, $promo_max)
 888     {
 889         if ($promo_min != 0) {
 890             $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min));
 891         } else {
 892             $min = new UFC_True();
 893         }
 894         if ($promo_max != 0) {
 895             $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max));
 896         } else {
 897             $max = new UFC_True();
 898         }
 899         return new UserFilter(new UFC_And($min, $max));
 900     }
 901
 902     static public function sortByName()
 903     {
 904         return array(new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME));
 905     }
 906
 907     static public function sortByPromo()
 908     {
 909         return array(new UFO_Promo(), new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME));
 910     }
 911
 912     static private function getDBSuffix($string)
 913     {
 914         return preg_replace('/[^a-z0-9]/i', '', $string);
 915     }
 916
 917
 918     private $option = 0;
 919     private function register_optional(array &$table, $val)
 920     {
 921         if (is_null($val)) {
 922             $sub   = $this->option++;
 923             $index = null;
 924         } else {
 925             $sub   = self::getDBSuffix($val);
 926             $index = $val;
 927         }
 928         $sub = '_' . $sub;
 929         $table[$sub] = $index;
 930         return $sub;
 931     }
 932
 933     /** DISPLAY
 934      */
 935     const DISPLAY = 'display';
 936     private $pd = false;
 937     public function addDisplayFilter()
 938     {
 939         $this->pd = true;
 940         return '';
 941     }
 942
 943     private function displayJoins()
 944     {
 945         if ($this->pd) {
 946             return array('pd' => array('left', 'profile_display', '$ME.pid = $PID'));
 947         } else {
 948             return array();
 949         }
 950     }
 951
 952     /** NAMES
 953      */
 954     /* name tokens */
 955     const LASTNAME  = 'lastname';
 956     const FIRSTNAME = 'firstname';
 957     const NICKNAME  = 'nickname';
 958     const PSEUDONYM = 'pseudonym';
 959     const NAME      = 'name';
 960     /* name variants */
 961     const VN_MARITAL  = 'marital';
 962     const VN_ORDINARY = 'ordinary';
 963     const VN_OTHER    = 'other';
 964     const VN_INI      = 'ini';
 965     /* display names */
 966     const DN_FULL      = 'directory_name';
 967     const DN_DISPLAY   = 'yourself';
 968     const DN_YOURSELF  = 'yourself';
 969     const DN_DIRECTORY = 'directory_name';
 970     const DN_PRIVATE   = 'private_name';
 971     const DN_PUBLIC    = 'public_name';
 972     const DN_SHORT     = 'short_name';
 973     const DN_SORT      = 'sort_name';
 974
 975     static public $name_variants = array(
 976         self::LASTNAME => array(self::VN_MARITAL, self::VN_ORDINARY),
 977         self::FIRSTNAME => array(self::VN_ORDINARY, self::VN_INI, self::VN_OTHER)
 978     );
 979
 980     static public function assertName($name)
 981     {
 982         if (!Profile::getNameTypeId($name)) {
 983             Platal::page()->kill('Invalid name type');
 984         }
 985     }
 986
 987     static public function isDisplayName($name)
 988     {
 989         return $name == self::DN_FULL || $name == self::DN_DISPLAY
 990             || $name == self::DN_YOURSELF || $name == self::DN_DIRECTORY
 991             || $name == self::DN_PRIVATE || $name == self::DN_PUBLIC
 992             || $name == self::DN_SHORT || $name == self::DN_SORT;
 993     }
 994
 995     private $pn  = array();
 996     public function addNameFilter($type, $variant = null)
 997     {
 998         if (!is_null($variant)) {
 999             $ft  = $type . '_' . $variant;
1000         } else {
1001             $ft = $type;
1002         }
1003         $sub = '_' . $ft;
1004         self::assertName($ft);
1005
1006         if (!is_null($variant) && $variant == 'other') {
1007             $sub .= $this->option++;
1008         }
1009         $this->pn[$sub] = Profile::getNameTypeId($ft);
1010         return $sub;
1011     }
1012
1013     private function nameJoins()
1014     {
1015         $joins = array();
1016         foreach ($this->pn as $sub => $type) {
1017             $joins['pn' . $sub] = array('left', 'profile_name', '$ME.pid = $PID AND $ME.typeid = ' . $type);
1018         }
1019         return $joins;
1020     }
1021
1022
1023     /** EDUCATION
1024      */
1025     const GRADE_ING = 'Ing.';
1026     const GRADE_PHD = 'PhD';
1027     const GRADE_MST = 'M%';
1028     static public function isGrade($grade)
1029     {
1030         return $grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST;
1031     }
1032
1033     static public function assertGrade($grade)
1034     {
1035         if (!self::isGrade($grade)) {
1036             Platal::page()->killError("Diplôme non valide");
1037         }
1038     }
1039
1040     static public function promoYear($grade)
1041     {
1042         // XXX: Definition of promotion for phds and masters might change in near future.
1043         return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year';
1044     }
1045
1046     private $pepe     = array();
1047     private $with_pee = false;
1048     public function addEducationFilter($x = false, $grade = null)
1049     {
1050         if (!$x) {
1051             $index = $this->option;
1052             $sub   = $this->option++;
1053         } else {
1054             self::assertGrade($grade);
1055             $index = $grade;
1056             $sub   = $grade[0];
1057             $this->with_pee = true;
1058         }
1059         $sub = '_' . $sub;
1060         $this->pepe[$index] = $sub;
1061         return $sub;
1062     }
1063
1064     private function educationJoins()
1065     {
1066         $joins = array();
1067         if ($this->with_pee) {
1068             $joins['pee'] = array('inner', 'profile_education_enum', 'pee.abbreviation = \'X\'');
1069         }
1070         foreach ($this->pepe as $grade => $sub) {
1071             if ($this->isGrade($grade)) {
1072                 $joins['pe' . $sub] = array('left', 'profile_education', '$ME.eduid = pee.id AND $ME.uid = $PID');
1073                 $joins['pede' . $sub] = array('inner', 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.abbreviation LIKE ' .
1074                                                   XDB::format('{?}', $grade));
1075             } else {
1076                 $joins['pe' . $sub] = array('left', 'profile_education', '$ME.uid = $PID');
1077                 $joins['pee' . $sub] = array('inner', 'profile_education_enum', '$ME.id = pe' . $sub . '.eduid');
1078                 $joins['pede' . $sub] = array('inner', 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid');
1079             }
1080         }
1081         return $joins;
1082     }
1083
1084
1085     /** GROUPS
1086      */
1087     private $gpm = array();
1088     public function addGroupFilter($group = null)
1089     {
1090         if (!is_null($group)) {
1091             if (ctype_digit($group)) {
1092                 $index = $sub = $group;
1093             } else {
1094                 $index = $group;
1095                 $sub   = self::getDBSuffix($group);
1096             }
1097         } else {
1098             $sub = 'group_' . $this->option++;
1099             $index = null;
1100         }
1101         $sub = '_' . $sub;
1102         $this->gpm[$sub] = $index;
1103         return $sub;
1104     }
1105
1106     private function groupJoins()
1107     {
1108         $joins = array();
1109         foreach ($this->gpm as $sub => $key) {
1110             if (is_null($key)) {
1111                 $joins['gpa' . $sub] = array('inner', 'groupex.asso');
1112                 $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
1113             } else if (ctype_digit($key)) {
1114                 $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = ' . $key);
1115             } else {
1116                 $joins['gpa' . $sub] = array('inner', 'groupex.asso', XDB::format('$ME.diminutif = {?}', $key));
1117                 $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
1118             }
1119         }
1120         return $joins;
1121     }
1122
1123     /** EMAILS
1124      */
1125     private $e = array();
1126     public function addEmailRedirectFilter($email = null)
1127     {
1128         return $this->register_optional($this->e, $email);
1129     }
1130
1131     private $ve = array();
1132     public function addVirtualEmailFilter($email = null)
1133     {
1134         $this->addAliasFilter(self::ALIAS_FORLIFE);
1135         return $this->register_optional($this->ve, $email);
1136     }
1137
1138     const ALIAS_BEST    = 'bestalias';
1139     const ALIAS_FORLIFE = 'forlife';
1140     private $al = array();
1141     public function addAliasFilter($alias = null)
1142     {
1143         return $this->register_optional($this->al, $alias);
1144     }
1145
1146     private function emailJoins()
1147     {
1148         global $globals;
1149         $joins = array();
1150         foreach ($this->e as $sub=>$key) {
1151             if (is_null($key)) {
1152                 $joins['e' . $sub] = array('left', 'emails', '$ME.uid = $UID AND $ME.flags != \'filter\'');
1153             } else {
1154                 $joins['e' . $sub] = array('left', 'emails', XDB::format('$ME.uid = $UID AND $ME.flags != \'filter\' AND $ME.email = {?}', $key));
1155             }
1156         }
1157         foreach ($this->al as $sub=>$key) {
1158             if (is_null($key)) {
1159                 $joins['al' . $sub] = array('left', 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\')');
1160             } else if ($key == self::ALIAS_BEST) {
1161                 $joins['al' . $sub] = array('left', 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND  FIND_IN_SET(\'bestalias\', $ME.flags)');
1162             } else if ($key == self::ALIAS_FORLIFE) {
1163                 $joins['al' . $sub] = array('left', 'aliases', '$ME.id = $UID AND $ME.type = \'a_vie\'');
1164             } else {
1165                 $joins['al' . $sub] = array('left', 'aliases', XDB::format('$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND $ME.alias = {?}', $key));
1166             }
1167         }
1168         foreach ($this->ve as $sub=>$key) {
1169             if (is_null($key)) {
1170                 $joins['v' . $sub] = array('left', 'virtual', '$ME.type = \'user\'');
1171             } else {
1172                 $joins['v' . $sub] = array('left', 'virtual', XDB::format('$ME.type = \'user\' AND $ME.alias = {?}', $key));
1173             }
1174             $joins['vr' . $sub] = array('left', 'virtual_redirect', XDB::format('$ME.vid = v' . $sub . '.vid
1175                                                                                  AND ($ME.redirect IN (CONCAT(al_forlife.alias, \'@\', {?}),
1176                                                                                                        CONCAT(al_forlife.alias, \'@\', {?}),
1177                                                                                                        a.email))',
1178                                                                                 $globals->mail->domain, $globals->mail->domain2));
1179         }
1180         return $joins;
1181     }
1182
1183
1184     /** CONTACTS
1185      */
1186     private $cts = array();
1187     public function addContactFilter($uid = null)
1188     {
1189         return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid);
1190     }
1191
1192     private function contactJoins()
1193     {
1194         $joins = array();
1195         foreach ($this->cts as $sub=>$key) {
1196             if (is_null($key)) {
1197                 $joins['c' . $sub] = array('left', 'contacts', '$ME.contact = $UID');
1198             } else {
1199                 $joins['c' . $sub] = array('left', 'contacts', XDB::format('$ME.uid = {?} AND $ME.contact = $UID', substr($key, 5)));
1200             }
1201         }
1202         return $joins;
1203     }
1204
1205
1206     /** CARNET
1207      */
1208     private $wn = array();
1209     public function addWatchRegistrationFilter($uid = null)
1210     {
1211         return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid);
1212     }
1213
1214     private $wp = array();
1215     public function addWatchPromoFilter($uid = null)
1216     {
1217         return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid);
1218     }
1219
1220     private $w = array();
1221     public function addWatchFilter($uid = null)
1222     {
1223         return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid);
1224     }
1225
1226     private function watchJoins()
1227     {
1228         $joins = array();
1229         foreach ($this->w as $sub=>$key) {
1230             if (is_null($key)) {
1231                 $joins['w' . $sub] = array('left', 'watch');
1232             } else {
1233                 $joins['w' . $sub] = array('left', 'watch', XDB::format('$ME.uid = {?}', substr($key, 5)));
1234             }
1235         }
1236         foreach ($this->wn as $sub=>$key) {
1237             if (is_null($key)) {
1238                 $joins['wn' . $sub] = array('left', 'watch_nonins', '$ME.ni_id = $UID');
1239             } else {
1240                 $joins['wn' . $sub] = array('left', 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5)));
1241             }
1242         }
1243         foreach ($this->wn as $sub=>$key) {
1244             if (is_null($key)) {
1245                 $joins['wn' . $sub] = array('left', 'watch_nonins', '$ME.ni_id = $UID');
1246             } else {
1247                 $joins['wn' . $sub] = array('left', 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5)));
1248             }
1249         }
1250         foreach ($this->wp as $sub=>$key) {
1251             if (is_null($key)) {
1252                 $joins['wp' . $sub] = array('left', 'watch_promo');
1253             } else {
1254                 $joins['wp' . $sub] = array('left', 'watch_promo', XDB::format('$ME.uid = {?}', substr($key, 5)));
1255             }
1256         }
1257         return $joins;
1258     }
1259 }
1260
1261
1262 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
1263 ?>