classes/userfilter.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2011 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     public function getIds($limit = null)
 422     {
 423         return $this->getUIDs();
 424     }
 425
 426     public function getTotalCount()
 427     {
 428         return $this->getTotalUserCount();
 429     }
 430
 431     public function getTotalUserCount()
 432     {
 433         if (is_null($this->lastusercount)) {
 434             $this->requireAccounts();
 435             $this->buildQuery();
 436             return (int)XDB::rawFetchOneCell('SELECT  COUNT(DISTINCT a.uid)
 437                                           ' . $this->query);
 438         } else {
 439             return $this->lastusercount;
 440         }
 441     }
 442
 443     public function getTotalProfileCount()
 444     {
 445         if (is_null($this->lastprofilecount)) {
 446             $this->requireProfiles();
 447             $this->buildQuery();
 448             return (int)XDB::rawFetchOneCell('SELECT  COUNT(DISTINCT p.pid)
 449                                           ' . $this->query);
 450         } else {
 451             return $this->lastprofilecount;
 452         }
 453     }
 454
 455     public function setCondition(PlFilterCondition $cond)
 456     {
 457         $this->root =& $cond;
 458         $this->query = null;
 459     }
 460
 461     public function addSort(PlFilterOrder $sort)
 462     {
 463         if (count($this->sort) == 0 && $sort instanceof PlFilterGroupableOrder)
 464         {
 465             $this->grouper = $sort;
 466         }
 467         $this->sort[] = $sort;
 468         $this->orderby = null;
 469     }
 470
 471     public function export()
 472     {
 473         $export = array('conditions' => $this->root->export());
 474         if (!empty($this->sort)) {
 475             $export['sorts'] = array();
 476             foreach ($this->sort as $sort) {
 477                 $export['sorts'][] = $sort->export();
 478             }
 479         }
 480         return $export;
 481     }
 482
 483     public function exportConditions()
 484     {
 485         return $this->root->export();
 486     }
 487
 488     public static function fromExport(array $export)
 489     {
 490         $export = new PlDict($export);
 491         if (!$export->has('conditions')) {
 492             throw new Exception("Cannot build a user filter without conditions");
 493         }
 494         $cond = UserFilterCondition::fromExport($export->v('conditions'));
 495         $sorts = null;
 496         if ($export->has('sorts')) {
 497             $sorts = array();
 498             foreach ($export->v('sorts') as $sort) {
 499                 $sorts[] = UserFilterOrder::fromExport($sort);
 500             }
 501         }
 502         return new UserFilter($cond, $sorts);
 503     }
 504
 505     public static function fromJSon($json)
 506     {
 507         $export = json_decode($json, true);
 508         if (is_null($export)) {
 509             throw new Exception("Invalid json: $json");
 510         }
 511         return self::fromExport($json);
 512     }
 513
 514     public static function fromExportedConditions(array $export)
 515     {
 516         $cond = UserFilterCondition::fromExport($export);
 517         return new UserFilter($cond);
 518     }
 519
 520     public static function fromJSonConditions($json)
 521     {
 522         $export = json_decode($json, true);
 523         if (is_null($export)) {
 524             throw new Exception("Invalid json: $json");
 525         }
 526         return self::fromExportedConditions($json);
 527     }
 528
 529     static public function getLegacy($promo_min, $promo_max)
 530     {
 531         if ($promo_min != 0) {
 532             $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min));
 533         } else {
 534             $min = new PFC_True();
 535         }
 536         if ($promo_max != 0) {
 537             $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max));
 538         } else {
 539             $max = new PFC_True();
 540         }
 541         return new UserFilter(new PFC_And($min, $max));
 542     }
 543
 544     static public function sortByName()
 545     {
 546         return array(new UFO_Name());
 547     }
 548
 549     static public function sortByPromo()
 550     {
 551         return array(new UFO_Promo(), new UFO_Name());
 552     }
 553
 554     static private function getDBSuffix($string)
 555     {
 556         if (is_array($string)) {
 557             if (count($string) == 1) {
 558                 return self::getDBSuffix(array_pop($string));
 559             }
 560             return md5(implode('|', $string));
 561         } else {
 562             return preg_replace('/[^a-z0-9]/i', '', $string);
 563         }
 564     }
 565
 566
 567     /** Stores a new (and unique) table alias in the &$table table
 568      * @param   &$table Array in which the table alias must be stored
 569      * @param   $val    Value which will then be used to build the join
 570      * @return          Name of the newly created alias
 571      */
 572     private $option = 0;
 573     private function register_optional(array &$table, $val)
 574     {
 575         if (is_null($val)) {
 576             $sub   = $this->option++;
 577             $index = null;
 578         } else {
 579             $sub   = self::getDBSuffix($val);
 580             $index = $val;
 581         }
 582         $sub = '_' . $sub;
 583         $table[$sub] = $index;
 584         return $sub;
 585     }
 586
 587     /** PROFILE VS ACCOUNT
 588      */
 589     private $with_profiles  = false;
 590     private $with_accounts  = false;
 591     public function requireAccounts()
 592     {
 593         $this->with_accounts = true;
 594     }
 595
 596     public function accountsRequired()
 597     {
 598         return $this->with_accounts;
 599     }
 600
 601     public function requireProfiles()
 602     {
 603         $this->with_profiles = true;
 604     }
 605
 606     public function profilesRequired()
 607     {
 608         return $this->with_profiles;
 609     }
 610
 611     protected function accountJoins()
 612     {
 613         $joins = array();
 614         if ($this->with_profiles && $this->with_accounts) {
 615             $joins['ap'] = PlSqlJoin::left('account_profiles', '$ME.uid = $UID AND FIND_IN_SET(\'owner\', ap.perms)');
 616             $joins['p'] = PlSqlJoin::left('profiles', '$PID = ap.pid');
 617         }
 618         return $joins;
 619     }
 620
 621     /** PERMISSIONS
 622      */
 623     private $at = false;
 624     public function requirePerms()
 625     {
 626         $this->requireAccounts();
 627         $this->at = true;
 628         return 'at';
 629     }
 630
 631     protected function permJoins()
 632     {
 633         if ($this->at) {
 634             return array('at' => PlSqlJoin::left('account_types', '$ME.type = a.type'));
 635         } else {
 636             return array();
 637         }
 638     }
 639
 640     /** DISPLAY
 641      */
 642     const DISPLAY = 'display';
 643     private $pd = false;
 644     public function addDisplayFilter()
 645     {
 646         $this->requireProfiles();
 647         $this->pd = true;
 648         return '';
 649     }
 650
 651     protected function displayJoins()
 652     {
 653         if ($this->pd) {
 654             return array('pd' => PlSqlJoin::left('profile_display', '$ME.pid = $PID'));
 655         } else {
 656             return array();
 657         }
 658     }
 659
 660     /** LOGGER
 661      */
 662
 663     private $with_logger = false;
 664     public function addLoggerFilter()
 665     {
 666         $this->with_logger = true;
 667         $this->requireAccounts();
 668         return 'ls';
 669     }
 670     protected function loggerJoins()
 671     {
 672         $joins = array();
 673         if ($this->with_logger) {
 674             $joins['ls'] = PlSqlJoin::left('log_sessions', '$ME.uid = $UID');
 675         }
 676         return $joins;
 677     }
 678
 679     /** NAMETOKENS
 680      */
 681     private $name_tokens = array();
 682     private $nb_tokens = 0;
 683
 684     public function addNameTokensFilter($token)
 685     {
 686         $this->requireProfiles();
 687         $sub = 'sn' . (1 + $this->nb_tokens);
 688         $this->nb_tokens++;
 689         $this->name_tokens[$sub] = $token;
 690         return $sub;
 691     }
 692
 693     protected function nameTokensJoins()
 694     {
 695         /* We don't return joins, since with_sn forces the SELECT to run on search_name first */
 696         $joins = array();
 697         foreach ($this->name_tokens as $sub => $token) {
 698             $joins[$sub] = PlSqlJoin::left('search_name', '$ME.pid = $PID');
 699         }
 700         return $joins;
 701     }
 702
 703     public function getNameTokens()
 704     {
 705         return $this->name_tokens;
 706     }
 707
 708     /** NATIONALITY
 709      */
 710
 711     private $with_nat = false;
 712     public function addNationalityFilter()
 713     {
 714         $this->with_nat = true;
 715         return 'ngc';
 716     }
 717
 718     protected function nationalityJoins()
 719     {
 720         $joins = array();
 721         if ($this->with_nat) {
 722             $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');
 723         }
 724         return $joins;
 725     }
 726
 727     /** EDUCATION
 728      */
 729     const GRADE_ING = Profile::DEGREE_X;
 730     const GRADE_PHD = Profile::DEGREE_D;
 731     const GRADE_MST = Profile::DEGREE_M;
 732     static public function isGrade($grade)
 733     {
 734         return ($grade !== 0) && ($grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST);
 735     }
 736
 737     static public function assertGrade($grade)
 738     {
 739         if (!self::isGrade($grade)) {
 740             Platal::page()->killError("Diplôme non valide: $grade");
 741         }
 742     }
 743
 744     static public function promoYear($grade)
 745     {
 746         // XXX: Definition of promotion for phds and masters might change in near future.
 747         return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year';
 748     }
 749
 750     private $pepe     = array();
 751     private $with_pee = false;
 752     public function addEducationFilter($x = false, $grade = null)
 753     {
 754         $this->requireProfiles();
 755         if (!$x) {
 756             $index = $this->option;
 757             $sub   = $this->option++;
 758         } else {
 759             self::assertGrade($grade);
 760             $index = $grade;
 761             $sub   = $grade[0];
 762             $this->with_pee = true;
 763         }
 764         $sub = '_' . $sub;
 765         $this->pepe[$index] = $sub;
 766         return $sub;
 767     }
 768
 769     protected function educationJoins()
 770     {
 771         $joins = array();
 772         if ($this->with_pee) {
 773             $joins['pee'] = PlSqlJoin::inner('profile_education_enum', 'pee.abbreviation = \'X\'');
 774         }
 775         foreach ($this->pepe as $grade => $sub) {
 776             if ($this->isGrade($grade)) {
 777                 $joins['pe' . $sub] = PlSqlJoin::left('profile_education', '$ME.eduid = pee.id AND $ME.pid = $PID');
 778                 $joins['pede' . $sub] = PlSqlJoin::inner('profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.degree LIKE {?}', $grade);
 779             } else {
 780                 $joins['pe' . $sub] = PlSqlJoin::left('profile_education', '$ME.pid = $PID');
 781                 $joins['pee' . $sub] = PlSqlJoin::inner('profile_education_enum', '$ME.id = pe' . $sub . '.eduid');
 782                 $joins['pede' . $sub] = PlSqlJoin::inner('profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid');
 783             }
 784         }
 785         return $joins;
 786     }
 787
 788
 789     /** GROUPS
 790      */
 791     private $gpm = array();
 792     public function addGroupFilter($group = null)
 793     {
 794         $this->requireAccounts();
 795         if (!is_null($group)) {
 796             if (is_int($group) || ctype_digit($group)) {
 797                 $index = $sub = $group;
 798             } else {
 799                 $index = $group;
 800                 $sub   = self::getDBSuffix($group);
 801             }
 802         } else {
 803             $sub = 'group_' . $this->option++;
 804             $index = null;
 805         }
 806         $sub = '_' . $sub;
 807         $this->gpm[$sub] = $index;
 808         return $sub;
 809     }
 810
 811     private $gpfm = array();
 812     public function addGroupFormerMemberFilter()
 813     {
 814         $this->requireAccounts();
 815         $sub = '_' . $this->option++;
 816         $this->gpfm[] = $sub;
 817         return $sub;
 818     }
 819
 820     protected function groupJoins()
 821     {
 822         $joins = array();
 823         foreach ($this->gpm as $sub => $key) {
 824             if (is_null($key)) {
 825                 $joins['gpa' . $sub] = PlSqlJoin::inner('groups');
 826                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
 827             } else if (is_int($key) || ctype_digit($key)) {
 828                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = ' . $key);
 829             } else {
 830                 $joins['gpa' . $sub] = PlSqlJoin::inner('groups', '$ME.diminutif = {?}', $key);
 831                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
 832             }
 833         }
 834         foreach ($this->gpfm as $sub) {
 835             $joins['gpfm' . $sub] = PlSqlJoin::left('group_former_members', '$ME.uid = $UID');
 836         }
 837         return $joins;
 838     }
 839
 840     /** NLS
 841      */
 842     private $nls = array();
 843     public function addNewsLetterFilter($nlid)
 844     {
 845         $this->requireAccounts();
 846         $sub = 'nl_' . $nlid;
 847         $this->nls[$nlid] = $sub;
 848         return $sub;
 849     }
 850
 851     protected function newsLetterJoins()
 852     {
 853         $joins = array();
 854         foreach ($this->nls as $key => $sub) {
 855             $joins[$sub] = PlSqlJoin::left('newsletter_ins', '$ME.nlid = {?} AND $ME.uid = $UID', $key);
 856         }
 857         return $joins;
 858     }
 859
 860     /** BINETS
 861      */
 862
 863     private $with_bi = false;
 864     private $with_bd = false;
 865     public function addBinetsFilter($with_enum = false)
 866     {
 867         $this->requireProfiles();
 868         $this->with_bi = true;
 869         if ($with_enum) {
 870             $this->with_bd = true;
 871             return 'bd';
 872         } else {
 873             return 'bi';
 874         }
 875     }
 876
 877     protected function binetsJoins()
 878     {
 879         $joins = array();
 880         if ($this->with_bi) {
 881             $joins['bi'] = PlSqlJoin::left('profile_binets', '$ME.pid = $PID');
 882         }
 883         if ($this->with_bd) {
 884             $joins['bd'] = PlSqlJoin::left('profile_binet_enum', '$ME.id = bi.binet_id');
 885         }
 886         return $joins;
 887     }
 888
 889     /** EMAILS
 890      */
 891     private $ra = array();
 892     /** Allows filtering by redirection.
 893      * @param $email If null, enable a left join on the email redirection table
 894      *  (email_redirect_account); otherwise, perform a left join on users having
 895      *  that email as a redirection.
 896      * @return Suffix to use to access the adequate table.
 897      */
 898     public function addEmailRedirectFilter($email = null)
 899     {
 900         $this->requireAccounts();
 901         return $this->register_optional($this->ra, $email);
 902     }
 903
 904     const ALIAS_BEST      = 'bestalias';
 905     const ALIAS_FORLIFE   = 'forlife';
 906     const ALIAS_AUXILIARY = 'alias_aux';
 907     private $sa = array();
 908     /** Allows filtering by source email.
 909      * @param $email If null, enable a left join on the email source table
 910      *  (email_source_account); otherwise, perform a left join on users having
 911      *  that email as a source email.
 912      * @return Suffix to use to access the adequate table.
 913      */
 914     public function addAliasFilter($email = null)
 915     {
 916         $this->requireAccounts();
 917         return $this->register_optional($this->sa, $email);
 918     }
 919
 920     private $with_rf = false;
 921     /** Allows filtering by active redirection.
 922      * @return Suffix to use to access the adequate table.
 923      */
 924     public function addActiveEmailRedirectFilter($email = null)
 925     {
 926         $this->requireAccounts();
 927         $this->with_rf = true;
 928     }
 929
 930     protected function emailJoins()
 931     {
 932         global $globals;
 933         $joins = array();
 934         foreach ($this->ra as $sub => $redirections) {
 935             if (is_null($redirections)) {
 936                 $joins['ra' . $sub] = PlSqlJoin::left('email_redirect_account', '$ME.uid = $UID AND $ME.type != \'imap\'');
 937             } else {
 938                 if (!is_array($redirections)) {
 939                     $key = array($redirections);
 940                 }
 941                 $joins['ra' . $sub] = PlSqlJoin::left('email_redirect_account', '$ME.uid = $UID AND $ME.type != \'imap\'
 942                                                                                  AND $ME.redirect IN {?}', $redirections);
 943             }
 944         }
 945         foreach ($this->sa as $sub => $emails) {
 946             if (is_null($emails)) {
 947                 $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID');
 948             } else if ($sub == self::ALIAS_BEST) {
 949                 $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID AND FIND_IN_SET(\'bestalias\', $ME.flags)');
 950             } else if ($sub == self::ALIAS_FORLIFE) {
 951                 $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID AND $ME.type = \'forlife\'');
 952             } else if ($sub == self::ALIAS_AUXILIARY) {
 953                 $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID AND $ME.type = \'alias_aux\'');
 954             } else {
 955                 if (!is_array($emails)) {
 956                     $key = array($emails);
 957                 }
 958                 $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID AND $ME.email IN {?}', $emails);
 959             }
 960         }
 961         if ($this->with_rf) {
 962             $joins['rf'] = PlSqlJoin::left('email_redirect_account', '$ME.uid = $UID AND $ME.type != \'imap\' AND $ME.flags = \'active\'');;
 963         }
 964         return $joins;
 965     }
 966
 967
 968     /** ADDRESSES
 969      */
 970     private $types = array();
 971     public function addAddressFilter($type)
 972     {
 973         $this->requireProfiles();
 974         $this->with_pa = true;
 975
 976         $sub = '_' . $this->option++;
 977         $this->types[$type] = $sub;
 978         return $sub;
 979     }
 980
 981     protected function addressJoins()
 982     {
 983         $joins = array();
 984         foreach ($this->types as $type => $sub) {
 985             $joins['pa' . $sub] = PlSqlJoin::inner('profile_addresses', '$ME.pid = $PID');
 986             $joins['pac' . $sub] = PlSqlJoin::inner('profile_addresses_components',
 987                                                     '$ME.pid = pa' . $sub . '.pid AND $ME.jobid = pa' . $sub . '.jobid AND $ME.groupid = pa' . $sub . '.groupid AND $ME.type = pa' . $sub . '.type AND $ME.id = pa' . $sub . '.id');
 988             $joins['pace' . $sub] = PlSqlJoin::inner('profile_addresses_components_enum',
 989                                                      '$ME.id = pac' . $sub . '.component_id AND FIND_IN_SET({?}, $ME.types)', $type);
 990         }
 991
 992         return $joins;
 993     }
 994
 995
 996     /** CORPS
 997      */
 998
 999     private $pc = false;
1000     private $pce = array();
1001     private $pcr = false;
1002     public function addCorpsFilter($type)
1003     {
1004         $this->requireProfiles();
1005         $this->pc = true;
1006         if ($type == UFC_Corps::CURRENT) {
1007             $this->pce['pcec'] = 'current_corpsid';
1008             return 'pcec';
1009         } else if ($type == UFC_Corps::ORIGIN) {
1010             $this->pce['pceo'] = 'original_corpsid';
1011             return 'pceo';
1012         }
1013     }
1014
1015     public function addCorpsRankFilter()
1016     {
1017         $this->requireProfiles();
1018         $this->pc = true;
1019         $this->pcr = true;
1020         return 'pcr';
1021     }
1022
1023     protected function corpsJoins()
1024     {
1025         $joins = array();
1026         if ($this->pc) {
1027             $joins['pc'] = PlSqlJoin::left('profile_corps', '$ME.pid = $PID');
1028         }
1029         if ($this->pcr) {
1030             $joins['pcr'] = PlSqlJoin::left('profile_corps_rank_enum', '$ME.id = pc.rankid');
1031         }
1032         foreach($this->pce as $sub => $field) {
1033             $joins[$sub] = PlSqlJoin::left('profile_corps_enum', '$ME.id = pc.' . $field);
1034         }
1035         return $joins;
1036     }
1037
1038     /** JOBS
1039      */
1040
1041     const JOB_USERDEFINED = 0x0001;
1042     const JOB_CV          = 0x0002;
1043     const JOB_ANY         = 0x0003;
1044
1045     /** Joins :
1046      * pj => profile_job
1047      * pje => profile_job_enum
1048      * pjt => profile_job_terms
1049      */
1050     private $with_pj  = false;
1051     private $with_pje = false;
1052     private $with_pjt = 0;
1053
1054     public function addJobFilter()
1055     {
1056         $this->requireProfiles();
1057         $this->with_pj = true;
1058         return 'pj';
1059     }
1060
1061     public function addJobCompanyFilter()
1062     {
1063         $this->addJobFilter();
1064         $this->with_pje = true;
1065         return 'pje';
1066     }
1067
1068     /**
1069      * Adds a filter on job terms of profile.
1070      * @param $nb the number of job terms to use
1071      * @return an array of the fields to filter (one for each term).
1072      */
1073     public function addJobTermsFilter($nb = 1)
1074     {
1075         $this->with_pjt = $nb;
1076         $jobtermstable = array();
1077         for ($i = 1; $i <= $nb; ++$i) {
1078             $jobtermstable[] = 'pjtr_'.$i;
1079         }
1080         return $jobtermstable;
1081     }
1082
1083     protected function jobJoins()
1084     {
1085         $joins = array();
1086         if ($this->with_pj) {
1087             $joins['pj'] = PlSqlJoin::left('profile_job', '$ME.pid = $PID');
1088         }
1089         if ($this->with_pje) {
1090             $joins['pje'] = PlSqlJoin::left('profile_job_enum', '$ME.id = pj.jobid');
1091         }
1092         if ($this->with_pjt > 0) {
1093             for ($i = 1; $i <= $this->with_pjt; ++$i) {
1094                 $joins['pjt_'.$i] = PlSqlJoin::left('profile_job_term', '$ME.pid = $PID');
1095                 $joins['pjtr_'.$i] = PlSqlJoin::left('profile_job_term_relation', '$ME.jtid_2 = pjt_'.$i.'.jtid');
1096             }
1097         }
1098         return $joins;
1099     }
1100
1101     /** NETWORKING
1102      */
1103
1104     private $with_pnw = false;
1105     public function addNetworkingFilter()
1106     {
1107         $this->requireAccounts();
1108         $this->with_pnw = true;
1109         return 'pnw';
1110     }
1111
1112     protected function networkingJoins()
1113     {
1114         $joins = array();
1115         if ($this->with_pnw) {
1116             $joins['pnw'] = PlSqlJoin::left('profile_networking', '$ME.pid = $PID');
1117         }
1118         return $joins;
1119     }
1120
1121     /** PHONE
1122      */
1123
1124     private $with_ptel = false;
1125
1126     public function addPhoneFilter()
1127     {
1128         $this->requireAccounts();
1129         $this->with_ptel = true;
1130         return 'ptel';
1131     }
1132
1133     protected function phoneJoins()
1134     {
1135         $joins = array();
1136         if ($this->with_ptel) {
1137             $joins['ptel'] = PlSqlJoin::left('profile_phones', '$ME.pid = $PID');
1138         }
1139         return $joins;
1140     }
1141
1142     /** MEDALS
1143      */
1144
1145     private $with_pmed = false;
1146     public function addMedalFilter()
1147     {
1148         $this->requireProfiles();
1149         $this->with_pmed = true;
1150         return 'pmed';
1151     }
1152
1153     protected function medalJoins()
1154     {
1155         $joins = array();
1156         if ($this->with_pmed) {
1157             $joins['pmed'] = PlSqlJoin::left('profile_medals', '$ME.pid = $PID');
1158         }
1159         return $joins;
1160     }
1161
1162     /** DELTATEN
1163      */
1164     private $dts = array();
1165     const DELTATEN = 1;
1166     const DELTATEN_MESSAGE = 2;
1167     // TODO: terms
1168
1169     public function addDeltaTenFilter($type)
1170     {
1171         $this->requireProfiles();
1172         switch ($type) {
1173         case self::DELTATEN:
1174             $this->dts['pdt'] = 'profile_deltaten';
1175             return 'pdt';
1176         case self::DELTATEN_MESSAGE:
1177             $this->dts['pdtm'] = 'profile_deltaten';
1178             return 'pdtm';
1179         default:
1180             Platal::page()->killError("Undefined DeltaTen filter.");
1181         }
1182     }
1183
1184     protected function deltatenJoins()
1185     {
1186         $joins = array();
1187         foreach ($this->dts as $sub => $tab) {
1188             $joins[$sub] = PlSqlJoin::left($tab, '$ME.pid = $PID');
1189         }
1190         return $joins;
1191     }
1192
1193     /** MENTORING
1194      */
1195
1196     private $pms = array();
1197     private $mjtr = false;
1198     const MENTOR = 1;
1199     const MENTOR_EXPERTISE = 2;
1200     const MENTOR_COUNTRY = 3;
1201     const MENTOR_TERM = 4;
1202
1203     public function addMentorFilter($type)
1204     {
1205         $this->requireProfiles();
1206         switch($type) {
1207         case self::MENTOR:
1208             $this->pms['pm'] = 'profile_mentor';
1209             return 'pm';
1210         case self::MENTOR_EXPERTISE:
1211             $this->pms['pme'] = 'profile_mentor';
1212             return 'pme';
1213         case self::MENTOR_COUNTRY:
1214             $this->pms['pmc'] = 'profile_mentor_country';
1215             return 'pmc';
1216         case self::MENTOR_TERM:
1217             $this->pms['pmt'] = 'profile_mentor_term';
1218             $this->mjtr = true;
1219             return 'mjtr';
1220         default:
1221             Platal::page()->killError("Undefined mentor filter.");
1222         }
1223     }
1224
1225     protected function mentorJoins()
1226     {
1227         $joins = array();
1228         foreach ($this->pms as $sub => $tab) {
1229             $joins[$sub] = PlSqlJoin::left($tab, '$ME.pid = $PID');
1230         }
1231         if ($this->mjtr) {
1232             $joins['mjtr'] = PlSqlJoin::left('profile_job_term_relation', '$ME.jtid_2 = pmt.jtid');
1233         }
1234         return $joins;
1235     }
1236
1237     /** CONTACTS
1238      */
1239     private $cts = array();
1240     public function addContactFilter($uid = null)
1241     {
1242         $this->requireProfiles();
1243         return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid);
1244     }
1245
1246     protected function contactJoins()
1247     {
1248         $joins = array();
1249         foreach ($this->cts as $sub=>$key) {
1250             if (is_null($key)) {
1251                 $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.contact = $PID');
1252             } else {
1253                 $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.uid = {?} AND $ME.contact = $PID', substr($key, 5));
1254             }
1255         }
1256         return $joins;
1257     }
1258
1259
1260     /** CARNET
1261      */
1262     private $wn = array();
1263     public function addWatchRegistrationFilter($uid = null)
1264     {
1265         $this->requireAccounts();
1266         return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid);
1267     }
1268
1269     private $wp = array();
1270     public function addWatchPromoFilter($uid = null)
1271     {
1272         $this->requireAccounts();
1273         return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid);
1274     }
1275
1276     private $w = array();
1277     public function addWatchFilter($uid = null)
1278     {
1279         $this->requireAccounts();
1280         return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid);
1281     }
1282
1283     protected function watchJoins()
1284     {
1285         $joins = array();
1286         foreach ($this->w as $sub=>$key) {
1287             if (is_null($key)) {
1288                 $joins['w' . $sub] = PlSqlJoin::left('watch');
1289             } else {
1290                 $joins['w' . $sub] = PlSqlJoin::left('watch', '$ME.uid = {?}', substr($key, 5));
1291             }
1292         }
1293         foreach ($this->wn as $sub=>$key) {
1294             if (is_null($key)) {
1295                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID');
1296             } else {
1297                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5));
1298             }
1299         }
1300         foreach ($this->wn as $sub=>$key) {
1301             if (is_null($key)) {
1302                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID');
1303             } else {
1304                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5));
1305             }
1306         }
1307         foreach ($this->wp as $sub=>$key) {
1308             if (is_null($key)) {
1309                 $joins['wp' . $sub] = PlSqlJoin::left('watch_promo');
1310             } else {
1311                 $joins['wp' . $sub] = PlSqlJoin::left('watch_promo', '$ME.uid = {?}', substr($key, 5));
1312             }
1313         }
1314         return $joins;
1315     }
1316
1317
1318     /** PHOTOS
1319      */
1320     private $with_photo;
1321     public function addPhotoFilter()
1322     {
1323         $this->requireProfiles();
1324         $this->with_photo = true;
1325         return 'photo';
1326     }
1327
1328     protected function photoJoins()
1329     {
1330         if ($this->with_photo) {
1331             return array('photo' => PlSqlJoin::left('profile_photos', '$ME.pid = $PID'));
1332         } else {
1333             return array();
1334         }
1335     }
1336
1337
1338     /** MARKETING
1339      */
1340     private $with_rm;
1341     public function addMarketingHash()
1342     {
1343         $this->requireAccounts();
1344         $this->with_rm = true;
1345     }
1346
1347     protected function marketingJoins()
1348     {
1349         if ($this->with_rm) {
1350             return array('rm' => PlSqlJoin::left('register_marketing', '$ME.uid = $UID'));
1351         } else {
1352             return array();
1353         }
1354     }
1355 }
1356 // }}}
1357 // {{{ class ProfileFilter
1358 class ProfileFilter extends UserFilter
1359 {
1360     public function get($limit = null)
1361     {
1362         return $this->getProfiles($limit);
1363     }
1364
1365     public function getIds($limit = null)
1366     {
1367         return $this->getPIDs();
1368     }
1369
1370     public function filter(array $profiles, $limit = null)
1371     {
1372         return $this->filterProfiles($profiles, self::defaultLimit($limit));
1373     }
1374
1375     public function getTotalCount()
1376     {
1377         return $this->getTotalProfileCount();
1378     }
1379
1380     public function getGroups()
1381     {
1382         return $this->getPIDGroups();
1383     }
1384 }
1385 // }}}
1386
1387 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
1388 ?>