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 class UFC_Promo implements UserFilterCondition
 165 {
 166
 167     private $grade;
 168     private $promo;
 169     private $comparison;
 170
 171     public function __construct($comparison, $grade, $promo)
 172     {
 173         $this->grade = $grade;
 174         $this->comparison = $comparison;
 175         $this->promo = $promo;
 176         if ($this->grade != UserFilter::DISPLAY) {
 177             UserFilter::assertGrade($this->grade);
 178         }
 179     }
 180
 181     public function buildCondition(UserFilter &$uf)
 182     {
 183         if ($this->grade == UserFilter::DISPLAY) {
 184             $sub = $uf->addDisplayFilter();
 185             return XDB::format('pd' . $sub . '.promo = {?}', $this->promo);
 186         } else {
 187             $sub = $uf->addEducationFilter(true, $this->grade);
 188             $field = 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
 189             return $field . ' IS NOT NULL AND ' . $field . ' ' . $this->comparison . ' ' . XDB::format('{?}', $this->promo);
 190         }
 191     }
 192 }
 193
 194 class UFC_Name implements UserFilterCondition
 195 {
 196     const PREFIX   = 1;
 197     const SUFFIX   = 2;
 198     const PARTICLE = 7;
 199     const VARIANTS = 8;
 200     const CONTAINS = 3;
 201
 202     private $type;
 203     private $text;
 204     private $mode;
 205
 206     public function __construct($type, $text, $mode)
 207     {
 208         $this->type = $type;
 209         $this->text = $text;
 210         $this->mode = $mode;
 211     }
 212
 213     private function buildNameQuery($type, $variant, $where, UserFilter &$uf)
 214     {
 215         $sub = $uf->addNameFilter($type, $variant);
 216         return str_replace('$ME', 'pn' . $sub, $where);
 217     }
 218
 219     public function buildCondition(UserFilter &$uf)
 220     {
 221         $left = '$ME.name';
 222         $op   = ' LIKE ';
 223         if (($this->mode & self::PARTICLE) == self::PARTICLE) {
 224             $left = 'CONCAT($ME.particle, \' \', $ME.name)';
 225         }
 226         if (($this->mode & self::CONTAINS) == 0) {
 227             $right = XDB::format('{?}', $this->text);
 228             $op    = ' = ';
 229         } else if (($this->mode & self::CONTAINS) == self::PREFIX) {
 230             $right = XDB::format('CONCAT({?}, \'%\')', $this->text);
 231         } else if (($this->mode & self::CONTAINS) == self::SUFFIX) {
 232             $right = XDB::format('CONCAT(\'%\', {?})', $this->text);
 233         } else {
 234             $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text);
 235         }
 236         $cond = $left . $op . $right;
 237         $conds = array($this->buildNameQuery($this->type, null, $cond, $uf));
 238         if (($this->mode & self::VARIANTS) != 0 && isset(UserFilter::$name_variants[$this->type])) {
 239             foreach (UserFilter::$name_variants[$this->type] as $var) {
 240                 $conds[] = $this->buildNameQuery($this->type, $var, $cond, $uf);
 241             }
 242         }
 243         return implode(' OR ', $conds);
 244     }
 245 }
 246
 247 class UFC_Dead implements UserFilterCondition
 248 {
 249     private $comparison;
 250     private $date;
 251
 252     public function __construct($comparison = null, $date = null)
 253     {
 254         $this->comparison = $comparison;
 255         $this->date = $date;
 256     }
 257
 258     public function buildCondition(UserFilter &$uf)
 259     {
 260         $str = 'p.deathdate IS NOT NULL';
 261         if (!is_null($this->comparison)) {
 262             $str .= ' AND p.deathdate ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date));
 263         }
 264         return $str;
 265     }
 266 }
 267
 268 class UFC_Registered implements UserFilterCondition
 269 {
 270     private $active;
 271     private $comparison;
 272     private $date;
 273
 274     public function __construct($active = false, $comparison = null, $date = null)
 275     {
 276         $this->only_active = $active;
 277         $this->comparison = $comparison;
 278         $this->date = $date;
 279     }
 280
 281     public function buildCondition(UserFilter &$uf)
 282     {
 283         if ($this->active) {
 284             $date = 'a.uid IS NOT NULL AND a.state = \'active\'';
 285         } else {
 286             $date = 'a.uid IS NOT NULL AND a.state != \'pending\'';
 287         }
 288         if (!is_null($this->comparison)) {
 289             $date .= ' AND a.registration_date ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date));
 290         }
 291         return $date;
 292     }
 293 }
 294
 295 class UFC_Sex implements UserFilterCondition
 296 {
 297     private $sex;
 298     public function __construct($sex)
 299     {
 300         $this->sex = $sex;
 301     }
 302
 303     public function buildCondition(UserFilter &$uf)
 304     {
 305         if ($this->sex != User::GENDER_MALE && $this->sex != User::GENDER_FEMALE) {
 306             return self::COND_FALSE;
 307         } else {
 308             return XDB::format('p.sex = {?}', $this->sex == User::GENDER_FEMALE ? 'female' : 'male');
 309         }
 310     }
 311 }
 312
 313 class UFC_Group implements UserFilterCondition
 314 {
 315     private $group;
 316     private $admin;
 317     public function __construct($group, $admin = false)
 318     {
 319         $this->group = $group;
 320         $this->admin = $admin;
 321     }
 322
 323     public function buildCondition(UserFilter &$uf)
 324     {
 325         $sub = $uf->addGroupFilter($this->group);
 326         $where = 'gpm' . $sub . '.perms IS NOT NULL';
 327         if ($this->admin) {
 328             $where .= ' AND gpm' . $sub . '.perms = \'admin\'';
 329         }
 330         return $where;
 331     }
 332 }
 333
 334 class UFC_Email implements UserFilterCondition
 335 {
 336     private $email;
 337     public function __construct($email)
 338     {
 339         $this->email = $email;
 340     }
 341
 342     public function buildCondition(UserFilter &$uf)
 343     {
 344         if (User::isForeignEmailAddress($this->email)) {
 345             $sub = $uf->addEmailRedirectFilter($this->email);
 346             return XDB::format('e' . $sub . '.email IS NOT NULL OR a.email = {?}', $this->email);
 347         } else {
 348             if (User::isVirtualEmailAddress($this->email)) {
 349                 $sub = $uf->addVirtualEmailFilter($this->email);
 350             } else {
 351                 list($user, $domain) = explode('@', $this->email);
 352                 $sub = $uf->addAliasFilter($user);
 353             }
 354             return 'al' . $sub . '.alias IS NOT NULL';
 355         }
 356     }
 357 }
 358
 359
 360 /******************
 361  * ORDERS
 362  ******************/
 363
 364 abstract class UserFilterOrder
 365 {
 366     protected $desc = false;
 367
 368     public function buildSort(UserFilter &$uf)
 369     {
 370         $sel = $this->getSortTokens($uf);
 371         if (!is_array($sel)) {
 372             $sel = array($sel);
 373         }
 374         if ($this->desc) {
 375             foreach ($sel as $k=>$s) {
 376                 $sel[$k] = $s . ' DESC';
 377             }
 378         }
 379         return $sel;
 380     }
 381
 382     abstract protected function getSortTokens(UserFilter &$uf);
 383 }
 384
 385 class UFO_Promo extends UserFilterOrder
 386 {
 387     private $grade;
 388
 389     public function __construct($grade = null, $desc = false)
 390     {
 391         $this->grade = $grade;
 392         $this->desc  = $desc;
 393     }
 394
 395     protected function getSortTokens(UserFilter &$uf)
 396     {
 397         if (UserFilter::isGrade($this->grade)) {
 398             $sub = $uf->addEducationFilter($this->grade);
 399             return 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
 400         } else {
 401             $sub = $uf->addDisplayFilter();
 402             return 'pd' . $sub . '.promo';
 403         }
 404     }
 405 }
 406
 407 class UFO_Name extends UserFilterOrder
 408 {
 409     private $type;
 410     private $variant;
 411     private $particle;
 412
 413     public function __construct($type, $variant = null, $particle = false, $desc = false)
 414     {
 415         $this->type = $type;
 416         $this->variant = $variant;
 417         $this->particle = $particle;
 418         $this->desc = $desc;
 419     }
 420
 421     protected function getSortTokens(UserFilter &$uf)
 422     {
 423         if (UserFilter::isDisplayName($this->type)) {
 424             $sub = $uf->addDisplayFilter();
 425             return 'pd' . $sub . '.' . $this->type;
 426         } else {
 427             $sub = $uf->addNameFilter($this->type, $this->variant);
 428             if ($this->particle) {
 429                 return 'CONCAT(pn' . $sub . '.particle, \' \', pn' . $sub . '.name)';
 430             } else {
 431                 return 'pn' . $sub . '.name';
 432             }
 433         }
 434     }
 435 }
 436
 437 class UFO_Registration extends UserFilterOrder
 438 {
 439     public function __construct($desc = false)
 440     {
 441         $this->desc = $desc;
 442     }
 443
 444     protected function getSortTokens(UserFilter &$uf)
 445     {
 446         return 'a.registration_date';
 447     }
 448 }
 449
 450 /***********************************
 451   *********************************
 452           USER FILTER CLASS
 453   *********************************
 454  ***********************************/
 455
 456 class UserFilter
 457 {
 458     static private $joinMethods = array();
 459
 460     private $root;
 461     private $sort = array();
 462     private $query = null;
 463     private $orderby = null;
 464
 465     private $lastcount = null;
 466
 467     public function __construct($cond = null, $sort = null)
 468     {
 469         if (empty(self::$joinMethods)) {
 470             $class = new ReflectionClass('UserFilter');
 471             foreach ($class->getMethods() as $method) {
 472                 $name = $method->getName();
 473                 if (substr($name, -5) == 'Joins' && $name != 'buildJoins') {
 474                     self::$joinMethods[] = $name;
 475                 }
 476             }
 477         }
 478         if (!is_null($cond)) {
 479             if ($cond instanceof UserFilterCondition) {
 480                 $this->setCondition($cond);
 481             }
 482         }
 483         if (!is_null($sort)) {
 484             if ($sort instanceof UserFilterOrder) {
 485                 $this->addSort($sort);
 486             } else if (is_array($sort)) {
 487                 foreach ($sort as $s) {
 488                     $this->addSort($s);
 489                 }
 490             }
 491         }
 492     }
 493
 494     private function buildQuery()
 495     {
 496         if (is_null($this->orderby)) {
 497             $orders = array();
 498             foreach ($this->sort as $sort) {
 499                 $orders = array_merge($orders, $sort->buildSort($this));
 500             }
 501             if (count($orders) == 0) {
 502                 $this->orderby = '';
 503             } else {
 504                 $this->orderby = 'ORDER BY  ' . implode(', ', $orders);
 505             }
 506         }
 507         if (is_null($this->query)) {
 508             $where = $this->root->buildCondition($this);
 509             $joins = $this->buildJoins();
 510             $this->query = 'FROM  accounts AS a
 511                       INNER JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms))
 512                       INNER JOIN  profiles AS p ON (p.pid = ap.pid)
 513                                ' . $joins . '
 514                            WHERE  (' . $where . ')';
 515         }
 516     }
 517
 518     private function formatJoin(array $joins)
 519     {
 520         $str = '';
 521         foreach ($joins as $key => $infos) {
 522             $mode  = $infos[0];
 523             $table = $infos[1];
 524             if ($mode == 'inner') {
 525                 $str .= 'INNER JOIN ';
 526             } else if ($mode == 'left') {
 527                 $str .= 'LEFT JOIN ';
 528             } else {
 529                 Platal::page()->kill("Join mode error");
 530             }
 531             $str .= $table . ' AS ' . $key;
 532             if (isset($infos[2])) {
 533                 $str .= ' ON (' . str_replace(array('$ME', '$PID', '$UID'), array($key, 'p.pid', 'a.uid'), $infos[2]) . ')';
 534             }
 535             $str .= "\n";
 536         }
 537         return $str;
 538     }
 539
 540     private function buildJoins()
 541     {
 542         $joins = array();
 543         foreach (self::$joinMethods as $method) {
 544             $joins = array_merge($joins, $this->$method());
 545         }
 546         return $this->formatJoin($joins);
 547     }
 548
 549     private function getUIDList($uids = null, $count = null, $offset = null)
 550     {
 551         $this->buildQuery();
 552         $limit = '';
 553         if (!is_null($count)) {
 554             if (!is_null($offset)) {
 555                 $limit = XDB::format('LIMIT {?}, {?}', $offset, $count);
 556             } else {
 557                 $limit = XDB::format('LIMIT {?}', $count);
 558             }
 559         }
 560         $cond = '';
 561         if (!is_null($uids)) {
 562             $cond = ' AND a.uid IN (' . implode(', ', $uids) . ')';
 563         }
 564         $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS  a.uid
 565                                     ' . $this->query . $cond . '
 566                                    GROUP BY  a.uid
 567                                     ' . $this->orderby . '
 568                                     ' . $limit);
 569         $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
 570         return $fetched;
 571     }
 572
 573     /** Check that the user match the given rule.
 574      */
 575     public function checkUser(PlUser &$user)
 576     {
 577         $this->buildQuery();
 578         $count = (int)XDB::fetchOneCell('SELECT  COUNT(*)
 579                                         ' . $this->query . XDB::format(' AND a.uid = {?}', $user->id()));
 580         return $count == 1;
 581     }
 582
 583     /** Filter a list of user to extract the users matching the rule.
 584      */
 585     public function filter(array $users, $count = null, $offset = null)
 586     {
 587         $this->buildQuery();
 588         $table = array();
 589         $uids  = array();
 590         foreach ($users as $user) {
 591             $uids[] = $user->id();
 592             $table[$user->id()] = $user;
 593         }
 594         $fetched = $this->getUIDList($uids, $count, $offset);
 595         $output = array();
 596         foreach ($fetched as $uid) {
 597             $output[] = $table[$uid];
 598         }
 599         return $output;
 600     }
 601
 602     public function getUIDs($count = null, $offset = null)
 603     {
 604         return $this->getUIDList(null, $count, $offset);
 605     }
 606
 607     public function getUsers($count = null, $offset = null)
 608     {
 609         return User::getBulkUsersWithUIDs($this->getUIDs($count, $offset));
 610     }
 611
 612     public function getTotalCount()
 613     {
 614         if (is_null($this->lastcount)) {
 615             $this->buildQuery();
 616             return (int)XDB::fetchOneCell('SELECT  COUNT(*)
 617                                           ' . $this->query . '
 618                                          GROUP BY  a.uid');
 619         } else {
 620             return $this->lastcount;
 621         }
 622     }
 623
 624     public function setCondition(UserFilterCondition &$cond)
 625     {
 626         $this->root =& $cond;
 627         $this->query = null;
 628     }
 629
 630     public function addSort(UserFilterOrder &$sort)
 631     {
 632         $this->sort[] = $sort;
 633         $this->orderby = null;
 634     }
 635
 636     static public function getLegacy($promo_min, $promo_max)
 637     {
 638         if ($promo_min != 0) {
 639             $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min));
 640         } else {
 641             $min = new UFC_True();
 642         }
 643         if ($promo_max != 0) {
 644             $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max));
 645         } else {
 646             $max = new UFC_True();
 647         }
 648         return new UserFilter(new UFC_And($min, $max));
 649     }
 650
 651     static private function getDBSuffix($string)
 652     {
 653         return preg_replace('/[^a-z0-9]/i', '', $string);
 654     }
 655
 656
 657     private $option = 0;
 658     private function register_optional(array &$table, $val)
 659     {
 660         if (is_null($val)) {
 661             $sub   = $this->option++;
 662             $index = null;
 663         } else {
 664             $sub   = self::getDBSuffix($val);
 665             $index = $val;
 666         }
 667         $sub = '_' . $sub;
 668         $table[$sub] = $index;
 669         return $sub;
 670     }
 671
 672     /** DISPLAY
 673      */
 674     const DISPLAY = 'display';
 675     private $pd = false;
 676     public function addDisplayFilter()
 677     {
 678         $this->pd = true;
 679         return '';
 680     }
 681
 682     private function displayJoins()
 683     {
 684         if ($this->pd) {
 685             return array('pd' => array('left', 'profile_display', '$ME.pid = $PID'));
 686         } else {
 687             return array();
 688         }
 689     }
 690
 691     /** NAMES
 692      */
 693     /* name tokens */
 694     const LASTNAME  = 'lastname';
 695     const FIRSTNAME = 'firstname';
 696     const NICKNAME  = 'nickname';
 697     const PSEUDONYM = 'pseudonym';
 698     const NAME      = 'name';
 699     /* name variants */
 700     const VN_MARITAL  = 'marital';
 701     const VN_ORDINARY = 'ordinary';
 702     const VN_OTHER    = 'other';
 703     const VN_INI      = 'ini';
 704     /* display names */
 705     const DN_FULL      = 'directory_name';
 706     const DN_DISPLAY   = 'yourself';
 707     const DN_YOURSELF  = 'yourself';
 708     const DN_DIRECTORY = 'directory_name';
 709     const DN_PRIVATE   = 'private_name';
 710     const DN_PUBLIC    = 'public_name';
 711     const DN_SHORT     = 'short_name';
 712     const DN_SORT      = 'sort_name';
 713
 714     static public $name_variants = array(
 715         self::LASTNAME => array(self::VN_MARITAL, self::VN_ORDINARY),
 716         self::FIRSTNAME => array(self::VN_ORDINARY, self::VN_INI, self::VN_OTHER)
 717     );
 718
 719     static public function assertName($name)
 720     {
 721         if (!Profile::getNameTypeId($name)) {
 722             Platal::page()->kill('Invalid name type');
 723         }
 724     }
 725
 726     static public function isDisplayName($name)
 727     {
 728         return $name == self::DN_FULL || $name == self::DN_DISPLAY
 729             || $name == self::DN_YOURSELF || $name == self::DN_DIRECTORY
 730             || $name == self::DN_PRIVATE || $name == self::DN_PUBLIC
 731             || $name == self::DN_SHORT || $name == self::DN_SORT;
 732     }
 733
 734     private $pn  = array();
 735     public function addNameFilter($type, $variant = null)
 736     {
 737         if (!is_null($variant)) {
 738             $ft  = $type . '_' . $variant;
 739         } else {
 740             $ft = $type;
 741         }
 742         $sub = '_' . $ft;
 743         self::assertName($ft);
 744
 745         if (!is_null($variant) && $variant == 'other') {
 746             $sub .= $this->option++;
 747         }
 748         $this->pn[$sub] = Profile::getNameTypeId($ft);
 749         return $sub;
 750     }
 751
 752     private function nameJoins()
 753     {
 754         $joins = array();
 755         foreach ($this->pn as $sub => $type) {
 756             $joins['pn' . $sub] = array('left', 'profile_name', '$ME.pid = $PID AND $ME.typeid = ' . $type);
 757         }
 758         return $joins;
 759     }
 760
 761
 762     /** EDUCATION
 763      */
 764     const GRADE_ING = 'Ing.';
 765     const GRADE_PHD = 'PhD';
 766     const GRADE_MST = 'M%';
 767     static public function isGrade($grade)
 768     {
 769         return $grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST;
 770     }
 771
 772     static public function assertGrade($grade)
 773     {
 774         if (!self::isGrade($grade)) {
 775             Platal::page()->killError("Diplôme non valide");
 776         }
 777     }
 778
 779     static public function promoYear($grade)
 780     {
 781         // XXX: Definition of promotion for phds and masters might change in near future.
 782         return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year';
 783     }
 784
 785     private $pepe     = array();
 786     private $with_pee = false;
 787     public function addEducationFilter($x = false, $grade = null)
 788     {
 789         if (!$x) {
 790             $index = $this->option;
 791             $sub   = $this->option++;
 792         } else {
 793             self::assertGrade($grade);
 794             $index = $grade;
 795             $sub   = $grade[0];
 796             $this->with_pee = true;
 797         }
 798         $sub = '_' . $sub;
 799         $this->pepe[$index] = $sub;
 800         return $sub;
 801     }
 802
 803     private function educationJoins()
 804     {
 805         $joins = array();
 806         if ($this->with_pee) {
 807             $joins['pee'] = array('inner', 'profile_education_enum', 'pee.abbreviation = \'X\'');
 808         }
 809         foreach ($this->pepe as $grade => $sub) {
 810             if ($this->isGrade($grade)) {
 811                 $joins['pe' . $sub] = array('left', 'profile_education', '$ME.eduid = pee.id AND $ME.uid = $PID');
 812                 $joins['pede' . $sub] = array('inner', 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.abbreviation LIKE ' .
 813                                                   XDB::format('{?}', $grade));
 814             } else {
 815                 $joins['pe' . $sub] = array('left', 'profile_education', '$ME.uid = $PID');
 816                 $joins['pee' . $sub] = array('inner', 'profile_education_enum', '$ME.id = pe' . $sub . '.eduid');
 817                 $joins['pede' . $sub] = array('inner', 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid');
 818             }
 819         }
 820         return $joins;
 821     }
 822
 823
 824     /** GROUPS
 825      */
 826     private $gpm = array();
 827     public function addGroupFilter($group = null)
 828     {
 829         if (!is_null($group)) {
 830             if (ctype_digit($group)) {
 831                 $index = $sub = $group;
 832             } else {
 833                 $index = $group;
 834                 $sub   = self::getDBSuffix($group);
 835             }
 836         } else {
 837             $sub = 'group_' . $this->option++;
 838             $index = null;
 839         }
 840         $sub = '_' . $sub;
 841         $this->gpm[$sub] = $index;
 842         return $sub;
 843     }
 844
 845     private function groupJoins()
 846     {
 847         $joins = array();
 848         foreach ($this->gpm as $sub => $key) {
 849             if (is_null($key)) {
 850                 $joins['gpa' . $sub] = array('inner', 'groupex.asso');
 851                 $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
 852             } else if (ctype_digit($key)) {
 853                 $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = ' . $key);
 854             } else {
 855                 $joins['gpa' . $sub] = array('inner', 'groupex.asso', XDB::format('$ME.diminutif = {?}', $key));
 856                 $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
 857             }
 858         }
 859         return $joins;
 860     }
 861
 862     /** EMAILS
 863      */
 864     private $e = array();
 865     public function addEmailRedirectFilter($email = null)
 866     {
 867         return $this->register_optional($this->e, $email);
 868     }
 869
 870     private $ve = array();
 871     public function addVirtualEmailFilter($email = null)
 872     {
 873         return $this->register_optional($this->ve, $email);
 874     }
 875
 876     private $al = array();
 877     public function addAliasFilter($alias = null)
 878     {
 879         return $this->register_optional($this->al, $alias);
 880     }
 881
 882     private function emailJoins()
 883     {
 884         global $globals;
 885         $joins = array();
 886         foreach ($this->e as $sub=>$key) {
 887             if (is_null($key)) {
 888                 $joins['e' . $sub] = array('left', 'emails', '$ME.uid = $UID AND $ME.flags != \'filter\'');
 889             } else {
 890                 $joins['e' . $sub] = array('left', 'emails', XDB::format('$ME.uid = $UID AND $ME.flags != \'filter\' AND $ME.email = {?}', $key));
 891             }
 892         }
 893         foreach ($this->ve as $sub=>$key) {
 894             if (is_null($key)) {
 895                 $joins['v' . $sub] = array('left', 'virtual', '$ME.type = \'user\'');
 896             } else {
 897                 $joins['v' . $sub] = array('left', 'virtual', XDB::format('$ME.type = \'user\' AND $ME.alias = {?}', $key));
 898             }
 899             $joins['vr' . $sub] = array('inner', 'virtual_redirect', '$ME.vid = v' . $sub . '.vid');
 900             $joins['al' . $sub] = array('left', 'aliases', XDB::format('$ME.id = $UID AND (CONCAT($ME.alias, \'@\', {?}) = vr'. $sub . '.redirect
 901                                                                        OR CONCAT($ME.alias, \'@\', {?}) = vr'. $sub . '.redirect)',
 902                                                                        $globals->mail->domain, $globals->mail->domain2));
 903         }
 904         foreach ($this->al as $sub=>$key) {
 905             if (is_null($key)) {
 906                 $joins['al' . $sub] = array('left', 'aliases', '$ME.id = $UID');
 907             } else {
 908                 $joins['al' . $sub] = array('left', 'aliases', XDB::format('$ME.id = $UID AND $ME.alias = {?}', $key));
 909             }
 910         }
 911         return $joins;
 912     }
 913 }
 914
 915 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 916 ?>