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 require_once dirname(__FILE__) . '/userfilter/conditions.inc.php';
  23 require_once dirname(__FILE__) . '/userfilter/orders.inc.php';
  24
  25 /***********************************
  26   *********************************
  27           USER FILTER CLASS
  28   *********************************
  29  ***********************************/
  30
  31 // {{{ class UserFilter
  32 /** This class provides a convenient and centralized way of filtering users.
  33  *
  34  * Usage:
  35  * $uf = new UserFilter(new UFC_Blah($x, $y), new UFO_Coin($z, $t));
  36  *
  37  * Resulting UserFilter can be used to:
  38  * - get a list of User objects matching the filter
  39  * - get a list of UIDs matching the filter
  40  * - get the number of users matching the filter
  41  * - check whether a given User matches the filter
  42  * - filter a list of User objects depending on whether they match the filter
  43  *
  44  * Usage for UFC and UFO objects:
  45  * A UserFilter will call all private functions named XXXJoins.
  46  * These functions must return an array containing the list of join
  47  * required by the various UFC and UFO associated to the UserFilter.
  48  * Entries in those returned array are of the following form:
  49  *   'join_tablealias' => array('join_type', 'joined_table', 'join_criter')
  50  * which will be translated into :
  51  *   join_type JOIN joined_table AS join_tablealias ON (join_criter)
  52  * in the final query.
  53  *
  54  * In the join_criter text, $ME is replaced with 'join_tablealias', $PID with
  55  * profile.pid, and $UID with accounts.uid.
  56  *
  57  * For each kind of "JOIN" needed, a function named addXXXFilter() should be defined;
  58  * its parameter will be used to set various private vars of the UserFilter describing
  59  * the required joins ; such a function shall return the "join_tablealias" to use
  60  * when referring to the joined table.
  61  *
  62  * For example, if data from profile_job must be available to filter results,
  63  * the UFC object will call $uf-addJobFilter(), which will set the 'with_pj' var and
  64  * return 'pj', the short name to use when referring to profile_job; when building
  65  * the query, calling the jobJoins function will return an array containing a single
  66  * row:
  67  *   'pj' => array('left', 'profile_job', '$ME.pid = $UID');
  68  *
  69  * The 'register_optional' function can be used to generate unique table aliases when
  70  * the same table has to be joined several times with different aliases.
  71  */
  72 class UserFilter extends PlFilter
  73 {
  74     protected $joinMethods = array();
  75
  76     protected $joinMetas = array(
  77                                 '$PID' => 'p.pid',
  78                                 '$UID' => 'a.uid',
  79                                 );
  80
  81     private $root;
  82     private $sort = array();
  83     private $grouper = null;
  84     private $query = null;
  85     private $orderby = null;
  86
  87     // Store the current 'search' visibility.
  88     private $profile_visibility = null;
  89
  90     private $lastusercount = null;
  91     private $lastprofilecount = null;
  92
  93     public function __construct($cond = null, $sort = null)
  94     {
  95         if (empty($this->joinMethods)) {
  96             $class = new ReflectionClass('UserFilter');
  97             foreach ($class->getMethods() as $method) {
  98                 $name = $method->getName();
  99                 if (substr($name, -5) == 'Joins' && $name != 'buildJoins') {
 100                     $this->joinMethods[] = $name;
 101                 }
 102             }
 103         }
 104         if (!is_null($cond)) {
 105             if ($cond instanceof PlFilterCondition) {
 106                 $this->setCondition($cond);
 107             }
 108         }
 109         if (!is_null($sort)) {
 110             if ($sort instanceof PlFilterOrder) {
 111                 $this->addSort($sort);
 112             } else if (is_array($sort)) {
 113                 foreach ($sort as $s) {
 114                     $this->addSort($s);
 115                 }
 116             }
 117         }
 118
 119         // This will set the visibility to the default correct level.
 120         $this->profile_visibility = new ProfileVisibility();
 121     }
 122
 123     public function getVisibilityLevels()
 124     {
 125         return $this->profile_visibility->levels();
 126     }
 127
 128     public function getVisibilityLevel()
 129     {
 130         return $this->profile_visibility->level();
 131     }
 132
 133     public function restrictVisibilityTo($level)
 134     {
 135         $this->profile_visibility->setLevel($level);
 136     }
 137
 138     public function getVisibilityCondition($field)
 139     {
 140         return $field . ' IN ' . XDB::formatArray($this->getVisibilityLevels());
 141     }
 142
 143     private function buildQuery()
 144     {
 145         // The root condition is built first because some orders need info
 146         // available only once all UFC have set their conditions (UFO_Score)
 147         if (is_null($this->query)) {
 148             $where = $this->root->buildCondition($this);
 149             $where = str_replace(array_keys($this->joinMetas),
 150                                  $this->joinMetas,
 151                                  $where);
 152         }
 153         if (is_null($this->orderby)) {
 154             $orders = array();
 155             foreach ($this->sort as $sort) {
 156                 $orders = array_merge($orders, $sort->buildSort($this));
 157             }
 158             if (count($orders) == 0) {
 159                 $this->orderby = '';
 160             } else {
 161                 $this->orderby = 'ORDER BY  ' . implode(', ', $orders);
 162             }
 163             $this->orderby = str_replace(array_keys($this->joinMetas),
 164                                          $this->joinMetas,
 165                                          $this->orderby);
 166         }
 167         if (is_null($this->query)) {
 168             if ($this->with_accounts) {
 169                 $from = 'accounts AS a';
 170             } else {
 171                 $this->requireProfiles();
 172                 $from = 'profiles AS p';
 173             }
 174             $joins = $this->buildJoins();
 175             $this->query = 'FROM  ' . $from . '
 176                                ' . $joins . '
 177                            WHERE  (' . $where . ')';
 178         }
 179     }
 180
 181     public function hasGroups()
 182     {
 183         return $this->grouper != null;
 184     }
 185
 186     public function getGroups()
 187     {
 188         return $this->getUIDGroups();
 189     }
 190
 191     public function getUIDGroups()
 192     {
 193         $this->requireAccounts();
 194         $this->buildQuery();
 195         $token = $this->grouper->getGroupToken($this);
 196
 197         $groups = XDB::rawFetchAllRow('SELECT ' . $token . ', COUNT(a.uid)
 198                                       ' . $this->query . '
 199                                       GROUP BY  ' . $token,
 200                                       0);
 201         return $groups;
 202     }
 203
 204     public function getPIDGroups()
 205     {
 206         $this->requireProfiles();
 207         $this->buildQuery();
 208         $token = $this->grouper->getGroupToken($this);
 209
 210         $groups = XDB::rawFetchAllRow('SELECT ' . $token . ', COUNT(p.pid)
 211                                       ' . $this->query . '
 212                                       GROUP BY  ' . $token,
 213                                       0);
 214         return $groups;
 215     }
 216
 217     private function getUIDList($uids = null, PlLimit &$limit)
 218     {
 219         $this->requireAccounts();
 220         $this->buildQuery();
 221         $lim = $limit->getSql();
 222         $cond = '';
 223         if (!empty($uids)) {
 224             $cond = XDB::format(' AND a.uid IN {?}', $uids);
 225         }
 226         $fetched = XDB::rawFetchColumn('SELECT SQL_CALC_FOUND_ROWS  a.uid
 227                                        ' . $this->query . $cond . '
 228                                        GROUP BY  a.uid
 229                                        ' . $this->orderby . '
 230                                        ' . $lim);
 231         $this->lastusercount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
 232         return $fetched;
 233     }
 234
 235     private function getPIDList($pids = null, PlLimit &$limit)
 236     {
 237         $this->requireProfiles();
 238         $this->buildQuery();
 239         $lim = $limit->getSql();
 240         $cond = '';
 241         if (!is_null($pids)) {
 242             $cond = XDB::format(' AND p.pid IN {?}', $pids);
 243         }
 244         $fetched = XDB::rawFetchColumn('SELECT  SQL_CALC_FOUND_ROWS  p.pid
 245                                        ' . $this->query . $cond . '
 246                                        GROUP BY  p.pid
 247                                        ' . $this->orderby . '
 248                                        ' . $lim);
 249         $this->lastprofilecount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
 250         return $fetched;
 251     }
 252
 253     private static function defaultLimit($limit) {
 254         if ($limit == null) {
 255             return new PlLimit();
 256         } else {
 257             return $limit;
 258         }
 259     }
 260
 261     /** Check that the user match the given rule.
 262      */
 263     public function checkUser(PlUser &$user)
 264     {
 265         $this->requireAccounts();
 266         $this->buildQuery();
 267         $count = (int)XDB::rawFetchOneCell('SELECT  COUNT(*)
 268                                            ' . $this->query
 269                                              . XDB::format(' AND a.uid = {?}', $user->id()));
 270         return $count == 1;
 271     }
 272
 273     /** Check that the profile match the given rule.
 274      */
 275     public function checkProfile(Profile &$profile)
 276     {
 277         $this->requireProfiles();
 278         $this->buildQuery();
 279         $count = (int)XDB::rawFetchOneCell('SELECT  COUNT(*)
 280                                            ' . $this->query
 281                                              . XDB::format(' AND p.pid = {?}', $profile->id()));
 282         return $count == 1;
 283     }
 284
 285     /** Default filter is on users
 286      */
 287     public function filter(array $users, $limit = null)
 288     {
 289         return $this->filterUsers($users, self::defaultLimit($limit));
 290     }
 291
 292     /** Filter a list of users to extract the users matching the rule.
 293      */
 294     public function filterUsers(array $users, $limit = null)
 295     {
 296         $limit = self::defaultLimit($limit);
 297         $this->requireAccounts();
 298         $this->buildQuery();
 299         $table = array();
 300         $uids  = array();
 301         foreach ($users as $user) {
 302             if ($user instanceof PlUser) {
 303                 $uid = $user->id();
 304             } else {
 305                 $uid = $user;
 306             }
 307             $uids[] = $uid;
 308             $table[$uid] = $user;
 309         }
 310         $fetched = $this->getUIDList($uids, $limit);
 311         $output = array();
 312         foreach ($fetched as $uid) {
 313             $output[] = $table[$uid];
 314         }
 315         return $output;
 316     }
 317
 318     /** Filter a list of profiles to extract the users matching the rule.
 319      */
 320     public function filterProfiles(array $profiles, $limit = null)
 321     {
 322         $limit = self::defaultLimit($limit);
 323         $this->requireProfiles();
 324         $this->buildQuery();
 325         $table = array();
 326         $pids  = array();
 327         foreach ($profiles as $profile) {
 328             if ($profile instanceof Profile) {
 329                 $pid = $profile->id();
 330             } else {
 331                 $pid = $profile;
 332             }
 333             $pids[] = $pid;
 334             $table[$pid] = $profile;
 335         }
 336         $fetched = $this->getPIDList($pids, $limit);
 337         $output = array();
 338         foreach ($fetched as $pid) {
 339             $output[] = $table[$pid];
 340         }
 341         return $output;
 342     }
 343
 344     public function getUIDs($limit = null)
 345     {
 346         $limit = self::defaultLimit($limit);
 347         return $this->getUIDList(null, $limit);
 348     }
 349
 350     public function getUID($pos = 0)
 351     {
 352         $uids =$this->getUIDList(null, new PlLimit(1, $pos));
 353         if (count($uids) == 0) {
 354             return null;
 355         } else {
 356             return $uids[0];
 357         }
 358     }
 359
 360     public function getPIDs($limit = null)
 361     {
 362         $limit = self::defaultLimit($limit);
 363         return $this->getPIDList(null, $limit);
 364     }
 365
 366     public function getPID($pos = 0)
 367     {
 368         $pids =$this->getPIDList(null, new PlLimit(1, $pos));
 369         if (count($pids) == 0) {
 370             return null;
 371         } else {
 372             return $pids[0];
 373         }
 374     }
 375
 376     public function getUsers($limit = null)
 377     {
 378         return User::getBulkUsersWithUIDs($this->getUIDs($limit));
 379     }
 380
 381     public function getUser($pos = 0)
 382     {
 383         $uid = $this->getUID($pos);
 384         if ($uid == null) {
 385             return null;
 386         } else {
 387             return User::getWithUID($uid);
 388         }
 389     }
 390
 391     public function iterUsers($limit = null)
 392     {
 393         return User::iterOverUIDs($this->getUIDs($limit));
 394     }
 395
 396     public function getProfiles($limit = null, $fields = 0x0000, $visibility = null)
 397     {
 398         return Profile::getBulkProfilesWithPIDs($this->getPIDs($limit), $fields, $visibility);
 399     }
 400
 401     public function getProfile($pos = 0, $fields = 0x0000, $visibility = null)
 402     {
 403         $pid = $this->getPID($pos);
 404         if ($pid == null) {
 405             return null;
 406         } else {
 407             return Profile::get($pid, $fields, $visibility);
 408         }
 409     }
 410
 411     public function iterProfiles($limit = null, $fields = 0x0000, $visibility = null)
 412     {
 413         return Profile::iterOverPIDs($this->getPIDs($limit), true, $fields, $visibility);
 414     }
 415
 416     public function get($limit = null)
 417     {
 418         return $this->getUsers($limit);
 419     }
 420
 421
 422     public function getTotalCount()
 423     {
 424         return $this->getTotalUserCount();
 425     }
 426
 427     public function getTotalUserCount()
 428     {
 429         if (is_null($this->lastusercount)) {
 430             $this->requireAccounts();
 431             $this->buildQuery();
 432             return (int)XDB::rawFetchOneCell('SELECT  COUNT(DISTINCT a.uid)
 433                                           ' . $this->query);
 434         } else {
 435             return $this->lastusercount;
 436         }
 437     }
 438
 439     public function getTotalProfileCount()
 440     {
 441         if (is_null($this->lastprofilecount)) {
 442             $this->requireProfiles();
 443             $this->buildQuery();
 444             return (int)XDB::rawFetchOneCell('SELECT  COUNT(DISTINCT p.pid)
 445                                           ' . $this->query);
 446         } else {
 447             return $this->lastprofilecount;
 448         }
 449     }
 450
 451     public function setCondition(PlFilterCondition $cond)
 452     {
 453         $this->root =& $cond;
 454         $this->query = null;
 455     }
 456
 457     public function addSort(PlFilterOrder $sort)
 458     {
 459         if (count($this->sort) == 0 && $sort instanceof PlFilterGroupableOrder)
 460         {
 461             $this->grouper = $sort;
 462         }
 463         $this->sort[] = $sort;
 464         $this->orderby = null;
 465     }
 466
 467     public function export()
 468     {
 469         $export = array('conditions' => $this->root->export());
 470         if (!empty($this->sort)) {
 471             $export['sorts'] = array();
 472             foreach ($this->sort as $sort) {
 473                 $export['sorts'][] = $sort->export();
 474             }
 475         }
 476         return $export;
 477     }
 478
 479     public function exportConditions()
 480     {
 481         return $this->root->export();
 482     }
 483
 484     public static function fromExport(array $export)
 485     {
 486         $export = new PlDict($export);
 487         if (!$export->has('conditions')) {
 488             throw new Exception("Cannot build a user filter without conditions");
 489         }
 490         $cond = UserFilterCondition::fromExport($export->v('conditions'));
 491         $sorts = null;
 492         if ($export->has('sorts')) {
 493             $sorts = array();
 494             foreach ($export->v('sorts') as $sort) {
 495                 $sorts[] = UserFilterOrder::fromExport($sort);
 496             }
 497         }
 498         return new UserFilter($cond, $sorts);
 499     }
 500
 501     public static function fromJSon($json)
 502     {
 503         $export = json_decode($json, true);
 504         if (is_null($export)) {
 505             throw new Exception("Invalid json: $json");
 506         }
 507         return self::fromExport($json);
 508     }
 509
 510     public static function fromExportedConditions(array $export)
 511     {
 512         $cond = UserFilterCondition::fromExport($export);
 513         return new UserFilter($cond);
 514     }
 515
 516     public static function fromJSonConditions($json)
 517     {
 518         $export = json_decode($json, true);
 519         if (is_null($export)) {
 520             throw new Exception("Invalid json: $json");
 521         }
 522         return self::fromExportedConditions($json);
 523     }
 524
 525     static public function getLegacy($promo_min, $promo_max)
 526     {
 527         if ($promo_min != 0) {
 528             $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min));
 529         } else {
 530             $min = new PFC_True();
 531         }
 532         if ($promo_max != 0) {
 533             $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max));
 534         } else {
 535             $max = new PFC_True();
 536         }
 537         return new UserFilter(new PFC_And($min, $max));
 538     }
 539
 540     static public function sortByName()
 541     {
 542         return array(new UFO_Name(Profile::LASTNAME), new UFO_Name(Profile::FIRSTNAME));
 543     }
 544
 545     static public function sortByPromo()
 546     {
 547         return array(new UFO_Promo(), new UFO_Name(Profile::LASTNAME), new UFO_Name(Profile::FIRSTNAME));
 548     }
 549
 550     static private function getDBSuffix($string)
 551     {
 552         if (is_array($string)) {
 553             if (count($string) == 1) {
 554                 return self::getDBSuffix(array_pop($string));
 555             }
 556             return md5(implode('|', $string));
 557         } else {
 558             return preg_replace('/[^a-z0-9]/i', '', $string);
 559         }
 560     }
 561
 562
 563     /** Stores a new (and unique) table alias in the &$table table
 564      * @param   &$table Array in which the table alias must be stored
 565      * @param   $val    Value which will then be used to build the join
 566      * @return          Name of the newly created alias
 567      */
 568     private $option = 0;
 569     private function register_optional(array &$table, $val)
 570     {
 571         if (is_null($val)) {
 572             $sub   = $this->option++;
 573             $index = null;
 574         } else {
 575             $sub   = self::getDBSuffix($val);
 576             $index = $val;
 577         }
 578         $sub = '_' . $sub;
 579         $table[$sub] = $index;
 580         return $sub;
 581     }
 582
 583     /** PROFILE VS ACCOUNT
 584      */
 585     private $with_profiles  = false;
 586     private $with_accounts  = false;
 587     public function requireAccounts()
 588     {
 589         $this->with_accounts = true;
 590     }
 591
 592     public function accountsRequired()
 593     {
 594         return $this->with_accounts;
 595     }
 596
 597     public function requireProfiles()
 598     {
 599         $this->with_profiles = true;
 600     }
 601
 602     public function profilesRequired()
 603     {
 604         return $this->with_profiles;
 605     }
 606
 607     protected function accountJoins()
 608     {
 609         $joins = array();
 610         if ($this->with_profiles && $this->with_accounts) {
 611             $joins['ap'] = PlSqlJoin::left('account_profiles', '$ME.uid = $UID AND FIND_IN_SET(\'owner\', ap.perms)');
 612             $joins['p'] = PlSqlJoin::left('profiles', '$PID = ap.pid');
 613         }
 614         return $joins;
 615     }
 616
 617     /** PERMISSIONS
 618      */
 619     private $at = false;
 620     public function requirePerms()
 621     {
 622         $this->requireAccounts();
 623         $this->at = true;
 624         return 'at';
 625     }
 626
 627     protected function permJoins()
 628     {
 629         if ($this->at) {
 630             return array('at' => PlSqlJoin::left('account_types', '$ME.type = a.type'));
 631         } else {
 632             return array();
 633         }
 634     }
 635
 636     /** DISPLAY
 637      */
 638     const DISPLAY = 'display';
 639     private $pd = false;
 640     public function addDisplayFilter()
 641     {
 642         $this->requireProfiles();
 643         $this->pd = true;
 644         return '';
 645     }
 646
 647     protected function displayJoins()
 648     {
 649         if ($this->pd) {
 650             return array('pd' => PlSqlJoin::left('profile_display', '$ME.pid = $PID'));
 651         } else {
 652             return array();
 653         }
 654     }
 655
 656     /** LOGGER
 657      */
 658
 659     private $with_logger = false;
 660     public function addLoggerFilter()
 661     {
 662         $this->with_logger = true;
 663         $this->requireAccounts();
 664         return 'ls';
 665     }
 666     protected function loggerJoins()
 667     {
 668         $joins = array();
 669         if ($this->with_logger) {
 670             $joins['ls'] = PlSqlJoin::left('log_sessions', '$ME.uid = $UID');
 671         }
 672         return $joins;
 673     }
 674
 675     /** NAMES
 676      */
 677
 678     static public function assertName($name)
 679     {
 680         if (!DirEnum::getID(DirEnum::NAMETYPES, $name)) {
 681             Platal::page()->kill('Invalid name type: ' . $name);
 682         }
 683     }
 684
 685     private $pn  = array();
 686     public function addNameFilter($type, $variant = null)
 687     {
 688         $this->requireProfiles();
 689         if (!is_null($variant)) {
 690             $ft  = $type . '_' . $variant;
 691         } else {
 692             $ft = $type;
 693         }
 694         $sub = '_' . $ft;
 695         self::assertName($ft);
 696
 697         if (!is_null($variant) && $variant == 'other') {
 698             $sub .= $this->option++;
 699         }
 700         $this->pn[$sub] = DirEnum::getID(DirEnum::NAMETYPES, $ft);
 701         return $sub;
 702     }
 703
 704     protected function nameJoins()
 705     {
 706         $joins = array();
 707         foreach ($this->pn as $sub => $type) {
 708             $joins['pn' . $sub] = PlSqlJoin::left('profile_name', '$ME.pid = $PID AND $ME.typeid = {?}', $type);
 709         }
 710         return $joins;
 711     }
 712
 713     /** NAMETOKENS
 714      */
 715     private $name_tokens = array();
 716     private $nb_tokens = 0;
 717
 718     public function addNameTokensFilter($token)
 719     {
 720         $this->requireProfiles();
 721         $sub = 'sn' . (1 + $this->nb_tokens);
 722         $this->nb_tokens++;
 723         $this->name_tokens[$sub] = $token;
 724         return $sub;
 725     }
 726
 727     protected function nameTokensJoins()
 728     {
 729         /* We don't return joins, since with_sn forces the SELECT to run on search_name first */
 730         $joins = array();
 731         foreach ($this->name_tokens as $sub => $token) {
 732             $joins[$sub] = PlSqlJoin::left('search_name', '$ME.pid = $PID');
 733         }
 734         return $joins;
 735     }
 736
 737     public function getNameTokens()
 738     {
 739         return $this->name_tokens;
 740     }
 741
 742     /** NATIONALITY
 743      */
 744
 745     private $with_nat = false;
 746     public function addNationalityFilter()
 747     {
 748         $this->with_nat = true;
 749         return 'ngc';
 750     }
 751
 752     protected function nationalityJoins()
 753     {
 754         $joins = array();
 755         if ($this->with_nat) {
 756             $joins['ngc'] = PlSqlJoin::left('geoloc_countries', '$ME.iso_3166_1_a2 = p.nationality1 OR $ME.iso_3166_1_a2 = p.nationality2 OR $ME.iso_3166_1_a2 = p.nationality3');
 757         }
 758         return $joins;
 759     }
 760
 761     /** EDUCATION
 762      */
 763     const GRADE_ING = 'Ing.';
 764     const GRADE_PHD = 'PhD';
 765     const GRADE_MST = 'M%';
 766     static public function isGrade($grade)
 767     {
 768         return ($grade !== 0) && ($grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST);
 769     }
 770
 771     static public function assertGrade($grade)
 772     {
 773         if (!self::isGrade($grade)) {
 774             Platal::page()->killError("Diplôme non valide: $grade");
 775         }
 776     }
 777
 778     static public function promoYear($grade)
 779     {
 780         // XXX: Definition of promotion for phds and masters might change in near future.
 781         return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year';
 782     }
 783
 784     private $pepe     = array();
 785     private $with_pee = false;
 786     public function addEducationFilter($x = false, $grade = null)
 787     {
 788         $this->requireProfiles();
 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     protected function educationJoins()
 804     {
 805         $joins = array();
 806         if ($this->with_pee) {
 807             $joins['pee'] = PlSqlJoin::inner('profile_education_enum', 'pee.abbreviation = \'X\'');
 808         }
 809         foreach ($this->pepe as $grade => $sub) {
 810             if ($this->isGrade($grade)) {
 811                 $joins['pe' . $sub] = PlSqlJoin::left('profile_education', '$ME.eduid = pee.id AND $ME.pid = $PID');
 812                 $joins['pede' . $sub] = PlSqlJoin::inner('profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.abbreviation LIKE {?}', $grade);
 813             } else {
 814                 $joins['pe' . $sub] = PlSqlJoin::left('profile_education', '$ME.pid = $PID');
 815                 $joins['pee' . $sub] = PlSqlJoin::inner('profile_education_enum', '$ME.id = pe' . $sub . '.eduid');
 816                 $joins['pede' . $sub] = PlSqlJoin::inner('profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid');
 817             }
 818         }
 819         return $joins;
 820     }
 821
 822
 823     /** GROUPS
 824      */
 825     private $gpm = array();
 826     public function addGroupFilter($group = null)
 827     {
 828         $this->requireAccounts();
 829         if (!is_null($group)) {
 830             if (is_int($group) || 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     protected function groupJoins()
 846     {
 847         $joins = array();
 848         foreach ($this->gpm as $sub => $key) {
 849             if (is_null($key)) {
 850                 $joins['gpa' . $sub] = PlSqlJoin::inner('groups');
 851                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
 852             } else if (is_int($key) || ctype_digit($key)) {
 853                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = ' . $key);
 854             } else {
 855                 $joins['gpa' . $sub] = PlSqlJoin::inner('groups', '$ME.diminutif = {?}', $key);
 856                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
 857             }
 858         }
 859         return $joins;
 860     }
 861
 862     /** BINETS
 863      */
 864
 865     private $with_bi = false;
 866     private $with_bd = false;
 867     public function addBinetsFilter($with_enum = false)
 868     {
 869         $this->requireProfiles();
 870         $this->with_bi = true;
 871         if ($with_enum) {
 872             $this->with_bd = true;
 873             return 'bd';
 874         } else {
 875             return 'bi';
 876         }
 877     }
 878
 879     protected function binetsJoins()
 880     {
 881         $joins = array();
 882         if ($this->with_bi) {
 883             $joins['bi'] = PlSqlJoin::left('profile_binets', '$ME.pid = $PID');
 884         }
 885         if ($this->with_bd) {
 886             $joins['bd'] = PlSqlJoin::left('profile_binet_enum', '$ME.id = bi.binet_id');
 887         }
 888         return $joins;
 889     }
 890
 891     /** EMAILS
 892      */
 893     private $e = array();
 894     public function addEmailRedirectFilter($email = null)
 895     {
 896         $this->requireAccounts();
 897         return $this->register_optional($this->e, $email);
 898     }
 899
 900     private $ve = array();
 901     public function addVirtualEmailFilter($email = null)
 902     {
 903         $this->addAliasFilter(self::ALIAS_FORLIFE);
 904         return $this->register_optional($this->ve, $email);
 905     }
 906
 907     const ALIAS_BEST    = 'bestalias';
 908     const ALIAS_FORLIFE = 'forlife';
 909     private $al = array();
 910     public function addAliasFilter($alias = null)
 911     {
 912         $this->requireAccounts();
 913         return $this->register_optional($this->al, $alias);
 914     }
 915
 916     protected function emailJoins()
 917     {
 918         global $globals;
 919         $joins = array();
 920         foreach ($this->e as $sub=>$key) {
 921             if (is_null($key)) {
 922                 $joins['e' . $sub] = PlSqlJoin::left('emails', '$ME.uid = $UID AND $ME.flags != \'filter\'');
 923             } else {
 924                 if (!is_array($key)) {
 925                     $key = array($key);
 926                 }
 927                 $joins['e' . $sub] = PlSqlJoin::left('emails', '$ME.uid = $UID AND $ME.flags != \'filter\'
 928                                                                AND $ME.email IN {?}', $key);
 929             }
 930         }
 931         foreach ($this->al as $sub=>$key) {
 932             if (is_null($key)) {
 933                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\')');
 934             } else if ($key == self::ALIAS_BEST) {
 935                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND  FIND_IN_SET(\'bestalias\', $ME.flags)');
 936             } else if ($key == self::ALIAS_FORLIFE) {
 937                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type = \'a_vie\'');
 938             } else {
 939                 if (!is_array($key)) {
 940                     $key = array($key);
 941                 }
 942                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\')
 943                                                                   AND $ME.alias IN {?}', $key);
 944             }
 945         }
 946         foreach ($this->ve as $sub=>$key) {
 947             if (is_null($key)) {
 948                 $joins['v' . $sub] = PlSqlJoin::left('virtual', '$ME.type = \'user\'');
 949             } else {
 950                 if (!is_array($key)) {
 951                     $key = array($key);
 952                 }
 953                 $joins['v' . $sub] = PlSqlJoin::left('virtual', '$ME.type = \'user\' AND $ME.alias IN {?}', $key);
 954             }
 955             $joins['vr' . $sub] = PlSqlJoin::left('virtual_redirect',
 956                                                   '$ME.vid = v' . $sub . '.vid
 957                                                    AND ($ME.redirect IN (CONCAT(al_forlife.alias, \'@\', {?}),
 958                                                                          CONCAT(al_forlife.alias, \'@\', {?}),
 959                                                                          a.email))',
 960                                                   $globals->mail->domain, $globals->mail->domain2);
 961         }
 962         return $joins;
 963     }
 964
 965
 966     /** ADDRESSES
 967      */
 968     private $with_pa = false;
 969     public function addAddressFilter()
 970     {
 971         $this->requireProfiles();
 972         $this->with_pa = true;
 973         return 'pa';
 974     }
 975
 976     private $with_pac = false;
 977     public function addAddressCountryFilter()
 978     {
 979         $this->requireProfiles();
 980         $this->addAddressFilter();
 981         $this->with_pac = true;
 982         return 'gc';
 983     }
 984
 985     private $with_pal = false;
 986     public function addAddressLocalityFilter()
 987     {
 988         $this->requireProfiles();
 989         $this->addAddressFilter();
 990         $this->with_pal = true;
 991         return 'gl';
 992     }
 993
 994     protected function addressJoins()
 995     {
 996         $joins = array();
 997         if ($this->with_pa) {
 998             $joins['pa'] = PlSqlJoin::left('profile_addresses', '$ME.pid = $PID');
 999         }
1000         if ($this->with_pac) {
1001             $joins['gc'] = PlSqlJoin::left('geoloc_countries', '$ME.iso_3166_1_a2 = pa.countryID');
1002         }
1003         if ($this->with_pal) {
1004             $joins['gl'] = PlSqlJoin::left('geoloc_localities', '$ME.id = pa.localityID');
1005         }
1006         return $joins;
1007     }
1008
1009
1010     /** CORPS
1011      */
1012
1013     private $pc = false;
1014     private $pce = array();
1015     private $pcr = false;
1016     public function addCorpsFilter($type)
1017     {
1018         $this->requireProfiles();
1019         $this->pc = true;
1020         if ($type == UFC_Corps::CURRENT) {
1021             $pce['pcec'] = 'current_corpsid';
1022             return 'pcec';
1023         } else if ($type == UFC_Corps::ORIGIN) {
1024             $pce['pceo'] = 'original_corpsid';
1025             return 'pceo';
1026         }
1027     }
1028
1029     public function addCorpsRankFilter()
1030     {
1031         $this->requireProfiles();
1032         $this->pc = true;
1033         $this->pcr = true;
1034         return 'pcr';
1035     }
1036
1037     protected function corpsJoins()
1038     {
1039         $joins = array();
1040         if ($this->pc) {
1041             $joins['pc'] = PlSqlJoin::left('profile_corps', '$ME.pid = $PID');
1042         }
1043         if ($this->pcr) {
1044             $joins['pcr'] = PlSqlJoin::left('profile_corps_rank_enum', '$ME.id = pc.rankid');
1045         }
1046         foreach($this->pce as $sub => $field) {
1047             $joins[$sub] = PlSqlJoin::left('profile_corps_enum', '$ME.id = pc.' . $field);
1048         }
1049         return $joins;
1050     }
1051
1052     /** JOBS
1053      */
1054
1055     const JOB_USERDEFINED = 0x0001;
1056     const JOB_CV          = 0x0002;
1057     const JOB_ANY         = 0x0003;
1058
1059     /** Joins :
1060      * pj => profile_job
1061      * pje => profile_job_enum
1062      * pjt => profile_job_terms
1063      */
1064     private $with_pj  = false;
1065     private $with_pje = false;
1066     private $with_pjt = 0;
1067
1068     public function addJobFilter()
1069     {
1070         $this->requireProfiles();
1071         $this->with_pj = true;
1072         return 'pj';
1073     }
1074
1075     public function addJobCompanyFilter()
1076     {
1077         $this->addJobFilter();
1078         $this->with_pje = true;
1079         return 'pje';
1080     }
1081
1082     /**
1083      * Adds a filter on job terms of profile.
1084      * @param $nb the number of job terms to use
1085      * @return an array of the fields to filter (one for each term).
1086      */
1087     public function addJobTermsFilter($nb = 1)
1088     {
1089         $this->with_pjt = $nb;
1090         $jobtermstable = array();
1091         for ($i = 1; $i <= $nb; ++$i) {
1092             $jobtermstable[] = 'pjtr_'.$i;
1093         }
1094         return $jobtermstable;
1095     }
1096
1097     protected function jobJoins()
1098     {
1099         $joins = array();
1100         if ($this->with_pj) {
1101             $joins['pj'] = PlSqlJoin::left('profile_job', '$ME.pid = $PID');
1102         }
1103         if ($this->with_pje) {
1104             $joins['pje'] = PlSqlJoin::left('profile_job_enum', '$ME.id = pj.jobid');
1105         }
1106         if ($this->with_pjt > 0) {
1107             for ($i = 1; $i <= $this->with_pjt; ++$i) {
1108                 $joins['pjt_'.$i] = PlSqlJoin::left('profile_job_term', '$ME.pid = $PID');
1109                 $joins['pjtr_'.$i] = PlSqlJoin::left('profile_job_term_relation', '$ME.jtid_2 = pjt_'.$i.'.jtid');
1110             }
1111         }
1112         return $joins;
1113     }
1114
1115     /** NETWORKING
1116      */
1117
1118     private $with_pnw = false;
1119     public function addNetworkingFilter()
1120     {
1121         $this->requireAccounts();
1122         $this->with_pnw = true;
1123         return 'pnw';
1124     }
1125
1126     protected function networkingJoins()
1127     {
1128         $joins = array();
1129         if ($this->with_pnw) {
1130             $joins['pnw'] = PlSqlJoin::left('profile_networking', '$ME.pid = $PID');
1131         }
1132         return $joins;
1133     }
1134
1135     /** PHONE
1136      */
1137
1138     private $with_ptel = false;
1139
1140     public function addPhoneFilter()
1141     {
1142         $this->requireAccounts();
1143         $this->with_ptel = true;
1144         return 'ptel';
1145     }
1146
1147     protected function phoneJoins()
1148     {
1149         $joins = array();
1150         if ($this->with_ptel) {
1151             $joins['ptel'] = PlSqlJoin::left('profile_phones', '$ME.pid = $PID');
1152         }
1153         return $joins;
1154     }
1155
1156     /** MEDALS
1157      */
1158
1159     private $with_pmed = false;
1160     public function addMedalFilter()
1161     {
1162         $this->requireProfiles();
1163         $this->with_pmed = true;
1164         return 'pmed';
1165     }
1166
1167     protected function medalJoins()
1168     {
1169         $joins = array();
1170         if ($this->with_pmed) {
1171             $joins['pmed'] = PlSqlJoin::left('profile_medals', '$ME.pid = $PID');
1172         }
1173         return $joins;
1174     }
1175
1176     /** MENTORING
1177      */
1178
1179     private $pms = array();
1180     private $mjtr = false;
1181     const MENTOR = 1;
1182     const MENTOR_EXPERTISE = 2;
1183     const MENTOR_COUNTRY = 3;
1184     const MENTOR_TERM = 4;
1185
1186     public function addMentorFilter($type)
1187     {
1188         $this->requireAccounts();
1189         switch($type) {
1190         case self::MENTOR:
1191             $this->pms['pm'] = 'profile_mentor';
1192             return 'pm';
1193         case self::MENTOR_EXPERTISE:
1194             $this->pms['pme'] = 'profile_mentor';
1195             return 'pme';
1196         case self::MENTOR_COUNTRY:
1197             $this->pms['pmc'] = 'profile_mentor_country';
1198             return 'pmc';
1199         case self::MENTOR_TERM:
1200             $this->pms['pmt'] = 'profile_mentor_term';
1201             $this->mjtr = true;
1202             return 'mjtr';
1203         default:
1204             Platal::page()->killError("Undefined mentor filter.");
1205         }
1206     }
1207
1208     protected function mentorJoins()
1209     {
1210         $joins = array();
1211         foreach ($this->pms as $sub => $tab) {
1212             $joins[$sub] = PlSqlJoin::left($tab, '$ME.pid = $PID');
1213         }
1214         if ($this->mjtr) {
1215             $joins['mjtr'] = PlSqlJoin::left('profile_job_term_relation', '$ME.jtid_2 = pmt.jtid');
1216         }
1217         return $joins;
1218     }
1219
1220     /** CONTACTS
1221      */
1222     private $cts = array();
1223     public function addContactFilter($uid = null)
1224     {
1225         $this->requireProfiles();
1226         return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid);
1227     }
1228
1229     protected function contactJoins()
1230     {
1231         $joins = array();
1232         foreach ($this->cts as $sub=>$key) {
1233             if (is_null($key)) {
1234                 $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.contact = $PID');
1235             } else {
1236                 $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.uid = {?} AND $ME.contact = $PID', substr($key, 5));
1237             }
1238         }
1239         return $joins;
1240     }
1241
1242
1243     /** CARNET
1244      */
1245     private $wn = array();
1246     public function addWatchRegistrationFilter($uid = null)
1247     {
1248         $this->requireAccounts();
1249         return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid);
1250     }
1251
1252     private $wp = array();
1253     public function addWatchPromoFilter($uid = null)
1254     {
1255         $this->requireAccounts();
1256         return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid);
1257     }
1258
1259     private $w = array();
1260     public function addWatchFilter($uid = null)
1261     {
1262         $this->requireAccounts();
1263         return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid);
1264     }
1265
1266     protected function watchJoins()
1267     {
1268         $joins = array();
1269         foreach ($this->w as $sub=>$key) {
1270             if (is_null($key)) {
1271                 $joins['w' . $sub] = PlSqlJoin::left('watch');
1272             } else {
1273                 $joins['w' . $sub] = PlSqlJoin::left('watch', '$ME.uid = {?}', substr($key, 5));
1274             }
1275         }
1276         foreach ($this->wn as $sub=>$key) {
1277             if (is_null($key)) {
1278                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID');
1279             } else {
1280                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5));
1281             }
1282         }
1283         foreach ($this->wn as $sub=>$key) {
1284             if (is_null($key)) {
1285                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID');
1286             } else {
1287                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5));
1288             }
1289         }
1290         foreach ($this->wp as $sub=>$key) {
1291             if (is_null($key)) {
1292                 $joins['wp' . $sub] = PlSqlJoin::left('watch_promo');
1293             } else {
1294                 $joins['wp' . $sub] = PlSqlJoin::left('watch_promo', '$ME.uid = {?}', substr($key, 5));
1295             }
1296         }
1297         return $joins;
1298     }
1299
1300
1301     /** PHOTOS
1302      */
1303     private $with_photo;
1304     public function addPhotoFilter()
1305     {
1306         $this->requireProfiles();
1307         $this->with_photo = true;
1308         return 'photo';
1309     }
1310
1311     protected function photoJoins()
1312     {
1313         if ($this->with_photo) {
1314             return array('photo' => PlSqlJoin::left('profile_photos', '$ME.pid = $PID'));
1315         } else {
1316             return array();
1317         }
1318     }
1319
1320
1321     /** MARKETING
1322      */
1323     private $with_rm;
1324     public function addMarketingHash()
1325     {
1326         $this->requireAccounts();
1327         $this->with_rm = true;
1328     }
1329
1330     protected function marketingJoins()
1331     {
1332         if ($this->with_rm) {
1333             return array('rm' => PlSqlJoin::left('register_marketing', '$ME.uid = $UID'));
1334         } else {
1335             return array();
1336         }
1337     }
1338 }
1339 // }}}
1340 // {{{ class ProfileFilter
1341 class ProfileFilter extends UserFilter
1342 {
1343     public function get($limit = null)
1344     {
1345         return $this->getProfiles($limit);
1346     }
1347
1348     public function filter(array $profiles, $limit = null)
1349     {
1350         return $this->filterProfiles($profiles, self::defaultLimit($limit));
1351     }
1352
1353     public function getTotalCount()
1354     {
1355         return $this->getTotalProfileCount();
1356     }
1357
1358     public function getGroups()
1359     {
1360         return $this->getPIDGroups();
1361     }
1362 }
1363 // }}}
1364
1365 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
1366 ?>