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