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