X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=classes%2Fuserfilter.php;h=dffa8a7191f6dd15eb02152f156457825c378bcd;hb=fda78a6777a5f385537fc9f291afb310e12525e5;hp=01c67ed7403139d872430558d1acc7bfd93e0922;hpb=5dd9d82327c44b3d5ce999c44a088f23e6b8ed8c;p=platal.git diff --git a/classes/userfilter.php b/classes/userfilter.php index 01c67ed..dffa8a7 100644 --- a/classes/userfilter.php +++ b/classes/userfilter.php @@ -1,6 +1,6 @@ array('join_type', 'joined_table', 'join_criter') + * which will be translated into : + * join_type JOIN joined_table AS join_tablealias ON (join_criter) + * in the final query. + * + * In the join_criter text, $ME is replaced with 'join_tablealias', $PID with + * profile.pid, and $UID with accounts.uid. + * + * For each kind of "JOIN" needed, a function named addXXXFilter() should be defined; + * its parameter will be used to set various private vars of the UserFilter describing + * the required joins ; such a function shall return the "join_tablealias" to use + * when referring to the joined table. + * + * For example, if data from profile_job must be available to filter results, + * the UFC object will call $uf-addJobFilter(), which will set the 'with_pj' var and + * return 'pj', the short name to use when referring to profile_job; when building + * the query, calling the jobJoins function will return an array containing a single + * row: + * 'pj' => array('left', 'profile_job', '$ME.pid = $UID'); + * + * The 'register_optional' function can be used to generate unique table aliases when + * the same table has to be joined several times with different aliases. + */ +class UserFilter extends PlFilter { - protected $child; + protected $joinMethods = array(); + + protected $joinMetas = array( + '$PID' => 'p.pid', + '$UID' => 'a.uid', + ); + + private $root; + private $sort = array(); + private $grouper = null; + private $query = null; + private $orderby = null; + + // Store the current 'search' visibility. + private $visibility = null; + // If the 'search' visibility should be based on a DB field instead. + private $visibility_field = null; - public function __construct($child = null) + private $lastusercount = null; + private $lastprofilecount = null; + + public function __construct($cond = null, $sort = null) { - if (!is_null($child) && ($child instanceof UserFilterCondition)) { - $this->setChild($child); + if (empty($this->joinMethods)) { + $class = new ReflectionClass('UserFilter'); + foreach ($class->getMethods() as $method) { + $name = $method->getName(); + if (substr($name, -5) == 'Joins' && $name != 'buildJoins') { + $this->joinMethods[] = $name; + } + } + } + if (!is_null($cond)) { + if ($cond instanceof PlFilterCondition) { + $this->setCondition($cond); + } + } + if (!is_null($sort)) { + if ($sort instanceof PlFilterOrder) { + $this->addSort($sort); + } else if (is_array($sort)) { + foreach ($sort as $s) { + $this->addSort($s); + } + } } + + // This will set the visibility to the default correct level. + $this->visibility = Visibility::defaultForRead(); } - public function setChild(UserFilterCondition &$cond) + /** Get the SQL condition to filter by visibility level for a field. + * This will return a SQL condition which evaluates to True if the given + * field display level is available from the current access level. + * @param $field Name of the field holding a display level + * @return string SQL condition, properly escaped, for that field. + */ + public function getVisibilityConditionForField($field) { - $this->child =& $cond; + if ($this->visibility_field != null) { + // Use enum 'bit' arithmetic. + // Display levels are ordered as 'hidden, private, ax, public' + // Thus ax > private. + // The $sub.display_level cell will contain the 'most private' display + // level available based on $field. If it is 'ax' and $field is + // 'private','ax' <= 'private' is false. + $sub = $this->addVisibilityFieldFilter($this->visibility_field); + return $sub . '.best_display_level + 0 <= 0 + ' . $field; + } else { + $sub = $this->addVisibilityAbsoluteFilter($this->visibility->level()); + return $sub . '.best_display_level + 0 <= 0 + ' . $field; + } } -} -abstract class UFC_NChildren implements UserFilterCondition -{ - protected $children = array(); + /** Get the SQL condition to filter by a given visibility level. + * @param $level One of Visibility::EXPORT_* + * @return string A SQL condition, properly escaped, which evaluates to 'true' if the $level can be viewed with the current access level. + */ + public function getVisibilityConditionAbsolute($level) + { + if ($this->visibility_field != null) { + // The $sub.display_levels cell will contain allowed display levels + // for an access level of $this->visibility_field. + $sub = $this->addVisibilityFieldFilter($this->visibility_field); + return XDB::format('FIND_IN_SET({?}, ' . $sub . '.display_levels)', $level); + } else { + if ($this->visibility->isVisible($level)) { + return 'TRUE'; + } else { + return 'FALSE'; + } + } + } - public function __construct() + private function buildQuery() { - $children = func_get_args(); - foreach ($children as &$child) { - if (!is_null($child) && ($child instanceof UserFilterCondition)) { - $this->addChild($child); + // The root condition is built first because some orders need info + // available only once all UFC have set their conditions (UFO_Score) + if (is_null($this->query)) { + $where = $this->root->buildCondition($this); + $where = str_replace(array_keys($this->joinMetas), + $this->joinMetas, + $where); + } + if (is_null($this->orderby)) { + $orders = array(); + foreach ($this->sort as $sort) { + $orders = array_merge($orders, $sort->buildSort($this)); + } + if (count($orders) == 0) { + $this->orderby = ''; + } else { + $this->orderby = 'ORDER BY ' . implode(', ', $orders); + } + $this->orderby = str_replace(array_keys($this->joinMetas), + $this->joinMetas, + $this->orderby); + } + if (is_null($this->query)) { + if ($this->with_accounts) { + $from = 'accounts AS a'; + } else { + $this->requireProfiles(); + $from = 'profiles AS p'; } + $joins = $this->buildJoins(); + $this->query = 'FROM ' . $from . ' + ' . $joins . ' + WHERE (' . $where . ')'; } } - public function addChild(UserFilterCondition &$cond) + public function hasGroups() { - $this->children[] =& $cond; + return $this->grouper != null; } - protected function catConds(array $cond, $op, $fallback) + public function getGroups() { - if (count($cond) == 0) { - return $fallback; - } else if (count($cond) == 1) { - return $cond[0]; - } else { - return '(' . implode(') ' . $op . ' (', $conds) . ')'; - } + return $this->getUIDGroups(); } -} -class UFC_True implements UserFilterCondition -{ - public function buildCondition(UserFilter &$uf) + public function getUIDGroups() { - return self::COND_TRUE; + $this->requireAccounts(); + $this->buildQuery(); + $token = $this->grouper->getGroupToken($this); + + $groups = XDB::rawFetchAllRow('SELECT ' . $token . ', COUNT(a.uid) + ' . $this->query . ' + GROUP BY ' . $token, + 0); + return $groups; } -} -class UFC_False implements UserFilterCondition -{ - public function buildCondition(UserFilter &$uf) + public function getPIDGroups() { - return self::COND_FALSE; + $this->requireProfiles(); + $this->buildQuery(); + $token = $this->grouper->getGroupToken($this); + + $groups = XDB::rawFetchAllRow('SELECT ' . $token . ', COUNT(p.pid) + ' . $this->query . ' + GROUP BY ' . $token, + 0); + return $groups; } -} -class UFC_Not extends UFC_OneChild -{ - public function buildCondition(UserFilter &$uf) + private function getUIDList($uids = null, PlLimit $limit) { - $val = $this->child->buildCondition($uf); - if ($val == self::COND_TRUE) { - return self::COND_FALSE; - } else if ($val == self::COND_FALSE) { - return self::COND_TRUE; - } else { - return 'NOT (' . $val . ')'; + $this->requireAccounts(); + $this->buildQuery(); + $lim = $limit->getSql(); + $cond = ''; + if (!empty($uids)) { + $cond = XDB::format(' AND a.uid IN {?}', $uids); } + $fetched = XDB::rawFetchColumn('SELECT SQL_CALC_FOUND_ROWS a.uid + ' . $this->query . $cond . ' + GROUP BY a.uid + ' . $this->orderby . ' + ' . $lim); + $this->lastusercount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()'); + return $fetched; } -} -class UFC_And extends UFC_NChildren -{ - public function buildCondition(UserFilter &$uf) + private function getPIDList($pids = null, PlLimit $limit) { - if (empty($this->children)) { - return self::COND_FALSE; + $this->requireProfiles(); + $this->buildQuery(); + $lim = $limit->getSql(); + $cond = ''; + if (!is_null($pids)) { + $cond = XDB::format(' AND p.pid IN {?}', $pids); + } + $fetched = XDB::rawFetchColumn('SELECT SQL_CALC_FOUND_ROWS p.pid + ' . $this->query . $cond . ' + GROUP BY p.pid + ' . $this->orderby . ' + ' . $lim); + $this->lastprofilecount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()'); + return $fetched; + } + + private static function defaultLimit($limit) { + if ($limit == null) { + return new PlLimit(); } else { - $true = self::COND_FALSE; - $conds = array(); - foreach ($this->children as &$child) { - $val = $child->buildCondition($uf); - if ($val == self::COND_TRUE) { - $true = self::COND_TRUE; - } else if ($val == self::COND_FALSE) { - return self::COND_FALSE; - } else { - $conds[] = $val; - } + return $limit; + } + } + + /** Check that the user match the given rule. + */ + public function checkUser(PlUser $user) + { + $this->requireAccounts(); + $this->buildQuery(); + $count = (int)XDB::rawFetchOneCell('SELECT COUNT(*) + ' . $this->query + . XDB::format(' AND a.uid = {?}', $user->id())); + return $count == 1; + } + + /** Check that the profile match the given rule. + */ + public function checkProfile(Profile $profile) + { + $this->requireProfiles(); + $this->buildQuery(); + $count = (int)XDB::rawFetchOneCell('SELECT COUNT(*) + ' . $this->query + . XDB::format(' AND p.pid = {?}', $profile->id())); + return $count == 1; + } + + /** Default filter is on users + */ + public function filter(array $users, $limit = null) + { + return $this->filterUsers($users, self::defaultLimit($limit)); + } + + /** Filter a list of users to extract the users matching the rule. + */ + public function filterUsers(array $users, $limit = null) + { + $limit = self::defaultLimit($limit); + $this->requireAccounts(); + $this->buildQuery(); + $table = array(); + $uids = array(); + foreach ($users as $user) { + if ($user instanceof PlUser) { + $uid = $user->id(); + } else { + $uid = $user; } - return $this->catConds($conds, 'AND', $true); + $uids[] = $uid; + $table[$uid] = $user; + } + $fetched = $this->getUIDList($uids, $limit); + $output = array(); + foreach ($fetched as $uid) { + $output[] = $table[$uid]; } + return $output; } -} -class UFC_Or extends UFC_NChildren -{ - public function buildCondition(UserFilter &$uf) + /** Filter a list of profiles to extract the users matching the rule. + */ + public function filterProfiles(array $profiles, $limit = null) { - if (empty($this->children)) { - return self::COND_TRUE; - } else { - $true = self::COND_TRUE; - $conds = array(); - foreach ($this->children as &$child) { - $val = $child->buildCondition($uf); - if ($val == self::COND_TRUE) { - return self::COND_TRUE; - } else if ($val == self::COND_FALSE) { - $true = self::COND_FALSE; - } else { - $conds[] = $val; - } + $limit = self::defaultLimit($limit); + $this->requireProfiles(); + $this->buildQuery(); + $table = array(); + $pids = array(); + foreach ($profiles as $profile) { + if ($profile instanceof Profile) { + $pid = $profile->id(); + } else { + $pid = $profile; } - return $this->catConds($conds, 'OR', $true); + $pids[] = $pid; + $table[$pid] = $profile; + } + $fetched = $this->getPIDList($pids, $limit); + $output = array(); + foreach ($fetched as $pid) { + $output[] = $table[$pid]; } + return $output; } -} -class UFC_Promo implements UserFilterCondition -{ + public function getUIDs($limit = null) + { + $limit = self::defaultLimit($limit); + return $this->getUIDList(null, $limit); + } - private $grade; - private $promo; - private $comparison; + public function getUID($pos = 0) + { + $uids =$this->getUIDList(null, new PlLimit(1, $pos)); + if (count($uids) == 0) { + return null; + } else { + return $uids[0]; + } + } - public function __construct($comparison, $grade, $promo) + public function getPIDs($limit = null) { - $this->grade = $grade; - $this->comparison = $comparison; - $this->promo = $promo; - UserFilter::assertGrade($this->grade); + $limit = self::defaultLimit($limit); + return $this->getPIDList(null, $limit); } - public function buildCondition(UserFilter &$uf) + public function getPID($pos = 0) { - // XXX: Definition of promotion for phds and masters might change in near future. - if ($this->grade == UserFilter::GRADE_ING) { - $promo_year = 'entry_year'; + $pids =$this->getPIDList(null, new PlLimit(1, $pos)); + if (count($pids) == 0) { + return null; } else { - $promo_year = 'grad_year'; + return $pids[0]; } - $sub = $uf->addEducationFilter(true, $this->grade); - $field = 'pe' . $sub . '.' . $promo_year; - return $field . ' IS NOT NULL AND ' . $field . ' ' . $this->comparison . ' ' . XDB::format('{?}', $this->promo); } -} -class UFC_Name implements UserFilterCondition -{ - const PREFIX = 1; - const SUFFIX = 2; - const PARTICLE = 7; - const VARIANTS = 8; - const CONTAINS = 3; + public function getUsers($limit = null) + { + return User::getBulkUsersWithUIDs($this->getUIDs($limit)); + } - private $type; - private $text; - private $mode; + public function getUser($pos = 0) + { + $uid = $this->getUID($pos); + if ($uid == null) { + return null; + } else { + return User::getWithUID($uid); + } + } - public function __construct($type, $text, $mode) + public function iterUsers($limit = null) { - $this->type = $type; - $this->text = $text; - $this->mode = $mode; + return User::iterOverUIDs($this->getUIDs($limit)); } - private function buildNameQuery($type, $variant, $where, UserFilter &$uf) + public function getProfiles($limit = null, $fields = 0x0000, $visibility = null) { - $sub = $uf->addNameFilter($type, $variant); - return str_replace('$ME', 'pn' . $sub, $where); + return Profile::getBulkProfilesWithPIDs($this->getPIDs($limit), $fields, $visibility); } - public function buildCondition(UserFilter &$uf) + public function getProfile($pos = 0, $fields = 0x0000, $visibility = null) { - $left = '$ME.name'; - $op = ' LIKE '; - if (($this->mode & self::PARTICLE) == self::PARTICLE) { - $left = 'CONCAT($ME.particle, \' \', $ME.name)'; - } - if (($this->mode & self::CONTAINS) == 0) { - $right = XDB::format('{?}', $this->text); - $op = ' = '; - } else if (($this->mode & self::CONTAINS) == self::PREFIX) { - $right = XDB::format('CONCAT({?}, \'%\')', $this->text); - } else if (($this->mode & self::CONTAINS) == self::SUFFIX) { - $right = XDB::format('CONCAT(\'%\', {?})', $this->text); + $pid = $this->getPID($pos); + if ($pid == null) { + return null; } else { - $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text); + return Profile::get($pid, $fields, $visibility); } - $cond = $left . $op . $right; - $conds = array($this->buildNameQuery($this->type, null, $cond, $uf)); - if (($this->mode & self::VARIANTS) != 0) { - foreach (UserFilter::$name_variants[$this->type] as $var) { - $conds[] = $this->buildNameQuery($this->type, $var, $cond, $uf); - } - } - return implode(' OR ', $conds); } -} -class UserFilter -{ - private $root; - private $query = null; + public function iterProfiles($limit = null, $fields = 0x0000, $visibility = null) + { + return Profile::iterOverPIDs($this->getPIDs($limit), true, $fields, $visibility); + } - public function __construct($cond = null) + public function get($limit = null) { - if (!is_null($cond)) { - if ($cond instanceof UserFilterCondition) { - $this->setCondition($cond); - } + return $this->getUsers($limit); + } + + public function getIds($limit = null) + { + return $this->getUIDs(); + } + + public function getTotalCount() + { + return $this->getTotalUserCount(); + } + + public function getTotalUserCount() + { + if (is_null($this->lastusercount)) { + $this->requireAccounts(); + $this->buildQuery(); + return (int)XDB::rawFetchOneCell('SELECT COUNT(DISTINCT a.uid) + ' . $this->query); + } else { + return $this->lastusercount; } } - private function buildQuery() + public function getTotalProfileCount() { - if (is_null($this->query)) { - $where = $this->root->buildCondition($this); - $joins = $this->buildJoins(); - $this->query = 'FROM accounts AS a - INNER JOIN account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms)) - INNER JOIN profiles AS p ON (p.pid = ap.pid) - ' . $joins . ' - WHERE (' . $where . ')'; + if (is_null($this->lastprofilecount)) { + $this->requireProfiles(); + $this->buildQuery(); + return (int)XDB::rawFetchOneCell('SELECT COUNT(DISTINCT p.pid) + ' . $this->query); + } else { + return $this->lastprofilecount; } } - private function formatJoin(array $joins) + public function setCondition(PlFilterCondition $cond) { - $str = ''; - foreach ($joins as $key => $infos) { - $mode = $infos[0]; - $table = $infos[1]; - if ($mode == 'inner') { - $str .= 'INNER JOIN '; - } else if ($mode == 'left') { - $str .= 'LEFT JOIN '; - } else { - Platal::page()->kill("Join mode error"); - } - $str .= $table . ' AS ' . $key; - if (isset($infos[2])) { - $str .= ' ON (' . str_replace(array('$ME', '$PID'), array($key, 'p.pid'), $infos[2]) . ')'; - } - $str .= "\n"; + $this->root =& $cond; + $this->query = null; + } + + public function addSort(PlFilterOrder $sort) + { + if (count($this->sort) == 0 && $sort instanceof PlFilterGroupableOrder) + { + $this->grouper = $sort; } - return $str; + $this->sort[] = $sort; + $this->orderby = null; } - private function buildJoins() + public function export() { - $joins = $this->educationJoins() + $this->nameJoins(); - return $this->formatJoin($joins); + $export = array('conditions' => $this->root->export()); + if (!empty($this->sort)) { + $export['sorts'] = array(); + foreach ($this->sort as $sort) { + $export['sorts'][] = $sort->export(); + } + } + return $export; } - /** Check that the user match the given rule. - */ - public function checkUser(PlUser &$user) + public function exportConditions() { - $this->buildQuery(); - $count = (int)XDB::fetchOneCell('SELECT COUNT(*) - ' . $this->query . XDB::format(' AND a.uid = {?}', $user->id())); - return $count == 1; + return $this->root->export(); } - /** Filter a list of user to extract the users matching the rule. - */ - public function filter(array $users) + public static function fromExport(array $export) { - $output = array(); - foreach ($users as &$user) { - if ($this->checkUser($user)) { - $output[] = $user; + $export = new PlDict($export); + if (!$export->has('conditions')) { + throw new Exception("Cannot build a user filter without conditions"); + } + $cond = UserFilterCondition::fromExport($export->v('conditions')); + $sorts = null; + if ($export->has('sorts')) { + $sorts = array(); + foreach ($export->v('sorts') as $sort) { + $sorts[] = UserFilterOrder::fromExport($sort); } } - return $output; + return new UserFilter($cond, $sorts); } - public function setCondition(UserFilterCondition &$cond) + public static function fromJSon($json) { - $this->root =& $cond; - $this->query = null; + $export = json_decode($json, true); + if (is_null($export)) { + throw new Exception("Invalid json: $json"); + } + return self::fromExport($json); + } + + public static function fromExportedConditions(array $export) + { + $cond = UserFilterCondition::fromExport($export); + return new UserFilter($cond); + } + + public static function fromJSonConditions($json) + { + $export = json_decode($json, true); + if (is_null($export)) { + throw new Exception("Invalid json: $json"); + } + return self::fromExportedConditions($json); } static public function getLegacy($promo_min, $promo_max) @@ -327,96 +556,230 @@ class UserFilter if ($promo_min != 0) { $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min)); } else { - $min = new UFC_True(); + $min = new PFC_True(); } if ($promo_max != 0) { $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max)); } else { - $max = new UFC_True(); + $max = new PFC_True(); } - return new UserFilter(new UFC_And($min, $max)); + return new UserFilter(new PFC_And($min, $max)); } + static public function sortByName() + { + return array(new UFO_Name()); + } + + static public function sortByPromo() + { + return array(new UFO_Promo(), new UFO_Name()); + } + + static private function getDBSuffix($string) + { + if (is_array($string)) { + if (count($string) == 1) { + return self::getDBSuffix(array_pop($string)); + } + return md5(implode('|', $string)); + } else { + return preg_replace('/[^a-z0-9]/i', '', $string); + } + } + + + /** Stores a new (and unique) table alias in the &$table table + * @param &$table Array in which the table alias must be stored + * @param $val Value which will then be used to build the join + * @return Name of the newly created alias + */ + private $option = 0; + private function register_optional(array &$table, $val) + { + if (is_null($val)) { + $sub = $this->option++; + $index = null; + } else { + $sub = self::getDBSuffix($val); + $index = $val; + } + $sub = '_' . $sub; + $table[$sub] = $index; + return $sub; + } - /** NAMES + /** PROFILE VS ACCOUNT */ - const LASTNAME = 'lastname'; - const FIRSTNAME = 'firstname'; - const NICKNAME = 'nickname'; - const PSEUDONYM = 'pseudonym'; - const NAME = 'name'; - const VN_MARITAL = 'marital'; - const VN_ORDINARY = 'ordinary'; - const VN_OTHER = 'other'; - const VN_INI = 'ini'; + private $with_profiles = false; + private $with_accounts = false; + public function requireAccounts() + { + $this->with_accounts = true; + } + + public function accountsRequired() + { + return $this->with_accounts; + } + + public function requireProfiles() + { + $this->with_profiles = true; + } + + public function profilesRequired() + { + return $this->with_profiles; + } + + protected function accountJoins() + { + $joins = array(); + if ($this->with_profiles && $this->with_accounts) { + $joins['ap'] = PlSqlJoin::left('account_profiles', '$ME.uid = $UID AND FIND_IN_SET(\'owner\', ap.perms)'); + $joins['p'] = PlSqlJoin::left('profiles', '$PID = ap.pid'); + } + return $joins; + } - static public $name_variants = array( - self::LASTNAME => array(self::VN_MARITAL, self::VN_ORDINARY), - self::FIRSTNAME => array(self::VN_ORDINARY, self::VN_INI, self::VN_OTHER), - self::NICKNAME => array(), self::PSEUDONYM => array(), - self::NAME => array()); + /** PERMISSIONS + */ + private $at = false; + public function requirePerms() + { + $this->requireAccounts(); + $this->at = true; + return 'at'; + } - static public function assertName($name) + protected function permJoins() { - if (!Profile::getNameTypeId($name)) { - Platal::page()->kill('Invalid name type'); + if ($this->at) { + return array('at' => PlSqlJoin::left('account_types', '$ME.type = a.type')); + } else { + return array(); } } - private $pn = array(); - private $pno = 0; - public function addNameFilter($type, $variant = null) + /** DISPLAY + */ + const DISPLAY = 'display'; + private $pd = false; + public function addDisplayFilter() + { + $this->requireProfiles(); + $this->pd = true; + return ''; + } + + protected function displayJoins() { - if (!is_null($variant)) { - $ft = $type . '_' . $variant; + if ($this->pd) { + return array('pd' => PlSqlJoin::left('profile_display', '$ME.pid = $PID')); } else { - $ft = $type; + return array(); } - $sub = '_' . $ft; - self::assertName($ft); + } + + /** LOGGER + */ - if (!is_null($variant) && $variant == 'other') { - $sub .= $this->pno++; + private $with_logger = false; + public function addLoggerFilter() + { + $this->with_logger = true; + $this->requireAccounts(); + return 'ls'; + } + protected function loggerJoins() + { + $joins = array(); + if ($this->with_logger) { + $joins['ls'] = PlSqlJoin::left('log_sessions', '$ME.uid = $UID'); } - $this->pn[$sub] = Profile::getNameTypeId($ft); + return $joins; + } + + /** NAMETOKENS + */ + private $name_tokens = array(); + private $nb_tokens = 0; + + public function addNameTokensFilter($token) + { + $this->requireProfiles(); + $sub = 'sn' . (1 + $this->nb_tokens); + $this->nb_tokens++; + $this->name_tokens[$sub] = $token; return $sub; } - private function nameJoins() + protected function nameTokensJoins() { + /* We don't return joins, since with_sn forces the SELECT to run on search_name first */ $joins = array(); - foreach ($this->pn as $sub => $type) { - $joins['pn' . $sub] = array('left', 'profile_name', '$ME.pid = $PID AND $ME.typeid = ' . $type); + foreach ($this->name_tokens as $sub => $token) { + $joins[$sub] = PlSqlJoin::left('search_name', '$ME.pid = $PID'); } return $joins; } + public function getNameTokens() + { + return $this->name_tokens; + } + + /** NATIONALITY + */ + + private $with_nat = false; + public function addNationalityFilter() + { + $this->with_nat = true; + return 'ngc'; + } + + protected function nationalityJoins() + { + $joins = array(); + if ($this->with_nat) { + $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'); + } + return $joins; + } /** EDUCATION */ - const GRADE_ING = 'Ing.'; - const GRADE_PHD = 'PhD'; - const GRADE_MST = 'M%'; + const GRADE_ING = Profile::DEGREE_X; + const GRADE_PHD = Profile::DEGREE_D; + const GRADE_MST = Profile::DEGREE_M; static public function isGrade($grade) { - return $grade == self::GRADE_ING || self::$grade == GRADE_PHD || self::$grade == GRADE_MST; + return ($grade !== 0) && ($grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST); } static public function assertGrade($grade) { if (!self::isGrade($grade)) { - Platal::page()->killError("Diplôme non valide"); + Platal::page()->killError("Diplôme non valide: $grade"); } } + static public function promoYear($grade) + { + // XXX: Definition of promotion for phds and masters might change in near future. + return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year'; + } + private $pepe = array(); private $with_pee = false; - private $pe_g = 0; public function addEducationFilter($x = false, $grade = null) { + $this->requireProfiles(); if (!$x) { - $index = $this->pe_g; - $sub = $this->pe_g++; + $index = $this->option; + $sub = $this->option++; } else { self::assertGrade($grade); $index = $grade; @@ -428,26 +791,694 @@ class UserFilter return $sub; } - private function educationJoins() + protected function educationJoins() { $joins = array(); if ($this->with_pee) { - $joins['pee'] = array('inner', 'profile_education_enum', 'pee.abbreviation = \'X\''); + $joins['pee'] = PlSqlJoin::inner('profile_education_enum', 'pee.abbreviation = \'X\''); } foreach ($this->pepe as $grade => $sub) { if ($this->isGrade($grade)) { - $joins['pe' . $sub] = array('left', 'profile_education', '$ME.eduid = pee.id AND $ME.uid = $PID'); - $joins['pede' . $sub] = array('inner', 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.abbreviation LIKE ' . - XDB::format('{?}', $grade)); + $joins['pe' . $sub] = PlSqlJoin::left('profile_education', '$ME.eduid = pee.id AND $ME.pid = $PID'); + $joins['pede' . $sub] = PlSqlJoin::inner('profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.degree LIKE {?}', $grade); } else { - $joins['pe' . $sub] = array('left', 'profile_education', '$ME.uid = $PID'); - $joins['pee' . $sub] = array('inner', 'profile_education_enum', '$ME.id = pe' . $sub . '.eduid'); - $joins['pede' . $sub] = array('inner', 'profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid'); + $joins['pe' . $sub] = PlSqlJoin::left('profile_education', '$ME.pid = $PID'); + $joins['pee' . $sub] = PlSqlJoin::inner('profile_education_enum', '$ME.id = pe' . $sub . '.eduid'); + $joins['pede' . $sub] = PlSqlJoin::inner('profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid'); } } return $joins; } + + + /** GROUPS + */ + private $gpm = array(); + public function addGroupFilter($group = null) + { + $this->requireAccounts(); + if (!is_null($group)) { + if (is_int($group) || ctype_digit($group)) { + $index = $sub = $group; + } else { + $index = $group; + $sub = self::getDBSuffix($group); + } + } else { + $sub = 'group_' . $this->option++; + $index = null; + } + $sub = '_' . $sub; + $this->gpm[$sub] = $index; + return $sub; + } + + private $gpfm = array(); + public function addGroupFormerMemberFilter() + { + $this->requireAccounts(); + $sub = '_' . $this->option++; + $this->gpfm[] = $sub; + return $sub; + } + + protected function groupJoins() + { + $joins = array(); + foreach ($this->gpm as $sub => $key) { + if (is_null($key)) { + $joins['gpa' . $sub] = PlSqlJoin::inner('groups'); + $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id'); + } else if (is_int($key) || ctype_digit($key)) { + $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = ' . $key); + } else { + $joins['gpa' . $sub] = PlSqlJoin::inner('groups', '$ME.diminutif = {?}', $key); + $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id'); + } + } + foreach ($this->gpfm as $sub) { + $joins['gpfm' . $sub] = PlSqlJoin::left('group_former_members', '$ME.uid = $UID'); + } + return $joins; + } + + /** NLS + */ + private $nls = array(); + public function addNewsLetterFilter($nlid) + { + $this->requireAccounts(); + $sub = 'nl_' . $nlid; + $this->nls[$nlid] = $sub; + return $sub; + } + + protected function newsLetterJoins() + { + $joins = array(); + foreach ($this->nls as $key => $sub) { + $joins[$sub] = PlSqlJoin::left('newsletter_ins', '$ME.nlid = {?} AND $ME.uid = $UID', $key); + } + return $joins; + } + + /** BINETS + */ + + private $with_bi = false; + private $with_bd = false; + public function addBinetsFilter($with_enum = false) + { + $this->requireProfiles(); + $this->with_bi = true; + if ($with_enum) { + $this->with_bd = true; + return 'bd'; + } else { + return 'bi'; + } + } + + protected function binetsJoins() + { + $joins = array(); + if ($this->with_bi) { + $joins['bi'] = PlSqlJoin::left('profile_binets', '$ME.pid = $PID'); + } + if ($this->with_bd) { + $joins['bd'] = PlSqlJoin::left('profile_binet_enum', '$ME.id = bi.binet_id'); + } + return $joins; + } + + /** EMAILS + */ + private $ra = array(); + /** Allows filtering by redirection. + * @param $email If null, enable a left join on the email redirection table + * (email_redirect_account); otherwise, perform a left join on users having + * that email as a redirection. + * @return Suffix to use to access the adequate table. + */ + public function addEmailRedirectFilter($email = null) + { + $this->requireAccounts(); + return $this->register_optional($this->ra, $email); + } + + const ALIAS_BEST = 'bestalias'; + const ALIAS_FORLIFE = 'forlife'; + const ALIAS_AUXILIARY = 'alias_aux'; + private $sa = array(); + /** Allows filtering by source email. + * @param $email If null, enable a left join on the email source table + * (email_source_account); otherwise, perform a left join on users having + * that email as a source email. + * @return Suffix to use to access the adequate table. + */ + public function addAliasFilter($email = null) + { + $this->requireAccounts(); + return $this->register_optional($this->sa, $email); + } + + private $with_rf = false; + /** Allows filtering by active redirection. + * @return Suffix to use to access the adequate table. + */ + public function addActiveEmailRedirectFilter($email = null) + { + $this->requireAccounts(); + $this->with_rf = true; + } + + protected function emailJoins() + { + global $globals; + $joins = array(); + foreach ($this->ra as $sub => $redirections) { + if (is_null($redirections)) { + $joins['ra' . $sub] = PlSqlJoin::left('email_redirect_account', '$ME.uid = $UID AND $ME.type != \'imap\''); + } else { + if (!is_array($redirections)) { + $key = array($redirections); + } + $joins['ra' . $sub] = PlSqlJoin::left('email_redirect_account', '$ME.uid = $UID AND $ME.type != \'imap\' + AND $ME.redirect IN {?}', $redirections); + } + } + foreach ($this->sa as $sub => $emails) { + if (is_null($emails)) { + $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID'); + } else if ($sub == self::ALIAS_BEST) { + $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID AND FIND_IN_SET(\'bestalias\', $ME.flags)'); + } else if ($sub == self::ALIAS_FORLIFE) { + $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID AND $ME.type = \'forlife\''); + } else if ($sub == self::ALIAS_AUXILIARY) { + $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID AND $ME.type = \'alias_aux\''); + } else { + if (!is_array($emails)) { + $key = array($emails); + } + $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID AND $ME.email IN {?}', $emails); + } + } + if ($this->with_rf) { + $joins['rf'] = PlSqlJoin::left('email_redirect_account', '$ME.uid = $UID AND $ME.type != \'imap\' AND $ME.flags = \'active\'');; + } + return $joins; + } + + + /** ADDRESSES + */ + private $types = array(); + public function addAddressFilter($type) + { + $this->requireProfiles(); + $this->with_pa = true; + + $sub = '_' . $this->option++; + $this->types[$type] = $sub; + return $sub; + } + + protected function addressJoins() + { + $joins = array(); + foreach ($this->types as $type => $sub) { + $joins['pa' . $sub] = PlSqlJoin::inner('profile_addresses', '$ME.pid = $PID'); + $joins['pac' . $sub] = PlSqlJoin::inner('profile_addresses_components', + '$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'); + $joins['pace' . $sub] = PlSqlJoin::inner('profile_addresses_components_enum', + '$ME.id = pac' . $sub . '.component_id AND FIND_IN_SET({?}, $ME.types)', $type); + } + + return $joins; + } + + + /** CORPS + */ + + private $pc = false; + private $pce = array(); + private $pcr = false; + public function addCorpsFilter($type) + { + $this->requireProfiles(); + $this->pc = true; + if ($type == UFC_Corps::CURRENT) { + $this->pce['pcec'] = 'current_corpsid'; + return 'pcec'; + } else if ($type == UFC_Corps::ORIGIN) { + $this->pce['pceo'] = 'original_corpsid'; + return 'pceo'; + } + } + + public function addCorpsRankFilter() + { + $this->requireProfiles(); + $this->pc = true; + $this->pcr = true; + return 'pcr'; + } + + protected function corpsJoins() + { + $joins = array(); + if ($this->pc) { + $joins['pc'] = PlSqlJoin::left('profile_corps', '$ME.pid = $PID'); + } + if ($this->pcr) { + $joins['pcr'] = PlSqlJoin::left('profile_corps_rank_enum', '$ME.id = pc.rankid'); + } + foreach($this->pce as $sub => $field) { + $joins[$sub] = PlSqlJoin::left('profile_corps_enum', '$ME.id = pc.' . $field); + } + return $joins; + } + + /** JOBS + */ + + const JOB_USERDEFINED = 0x0001; + const JOB_CV = 0x0002; + const JOB_ANY = 0x0003; + + /** Joins : + * pj => profile_job + * pje => profile_job_enum + * pjt => profile_job_terms + */ + private $with_pj = false; + private $with_pje = false; + private $with_pjt = 0; + + public function addJobFilter() + { + $this->requireProfiles(); + $this->with_pj = true; + return 'pj'; + } + + public function addJobCompanyFilter() + { + $this->addJobFilter(); + $this->with_pje = true; + return 'pje'; + } + + /** + * Adds a filter on job terms of profile. + * @param $nb the number of job terms to use + * @return an array of the fields to filter (one for each term). + */ + public function addJobTermsFilter($nb = 1) + { + $this->with_pjt = $nb; + $jobtermstable = array(); + for ($i = 1; $i <= $nb; ++$i) { + $jobtermstable[] = 'pjtr_'.$i; + } + return $jobtermstable; + } + + protected function jobJoins() + { + $joins = array(); + if ($this->with_pj) { + $joins['pj'] = PlSqlJoin::left('profile_job', '$ME.pid = $PID'); + } + if ($this->with_pje) { + $joins['pje'] = PlSqlJoin::left('profile_job_enum', '$ME.id = pj.jobid'); + } + if ($this->with_pjt > 0) { + for ($i = 1; $i <= $this->with_pjt; ++$i) { + $joins['pjt_'.$i] = PlSqlJoin::left('profile_job_term', '$ME.pid = $PID'); + $joins['pjtr_'.$i] = PlSqlJoin::left('profile_job_term_relation', '$ME.jtid_2 = pjt_'.$i.'.jtid'); + } + } + return $joins; + } + + /** NETWORKING + */ + + private $with_pnw = false; + public function addNetworkingFilter() + { + $this->requireAccounts(); + $this->with_pnw = true; + return 'pnw'; + } + + protected function networkingJoins() + { + $joins = array(); + if ($this->with_pnw) { + $joins['pnw'] = PlSqlJoin::left('profile_networking', '$ME.pid = $PID'); + } + return $joins; + } + + /** PHONE + */ + + private $with_ptel = false; + + public function addPhoneFilter() + { + $this->requireAccounts(); + $this->with_ptel = true; + return 'ptel'; + } + + protected function phoneJoins() + { + $joins = array(); + if ($this->with_ptel) { + $joins['ptel'] = PlSqlJoin::left('profile_phones', '$ME.pid = $PID'); + } + return $joins; + } + + /** MEDALS + */ + + private $with_pmed = false; + public function addMedalFilter() + { + $this->requireProfiles(); + $this->with_pmed = true; + return 'pmed'; + } + + protected function medalJoins() + { + $joins = array(); + if ($this->with_pmed) { + $joins['pmed'] = PlSqlJoin::left('profile_medals', '$ME.pid = $PID'); + } + return $joins; + } + + /** DELTATEN + */ + private $dts = array(); + const DELTATEN = 1; + const DELTATEN_MESSAGE = 2; + // TODO: terms + + public function addDeltaTenFilter($type) + { + $this->requireProfiles(); + switch ($type) { + case self::DELTATEN: + $this->dts['pdt'] = 'profile_deltaten'; + return 'pdt'; + case self::DELTATEN_MESSAGE: + $this->dts['pdtm'] = 'profile_deltaten'; + return 'pdtm'; + default: + Platal::page()->killError("Undefined DeltaTen filter."); + } + } + + protected function deltatenJoins() + { + $joins = array(); + foreach ($this->dts as $sub => $tab) { + $joins[$sub] = PlSqlJoin::left($tab, '$ME.pid = $PID'); + } + return $joins; + } + + /** MENTORING + */ + + private $pms = array(); + private $mjtr = false; + const MENTOR = 1; + const MENTOR_EXPERTISE = 2; + const MENTOR_COUNTRY = 3; + const MENTOR_TERM = 4; + + public function addMentorFilter($type) + { + $this->requireProfiles(); + switch($type) { + case self::MENTOR: + $this->pms['pm'] = 'profile_mentor'; + return 'pm'; + case self::MENTOR_EXPERTISE: + $this->pms['pme'] = 'profile_mentor'; + return 'pme'; + case self::MENTOR_COUNTRY: + $this->pms['pmc'] = 'profile_mentor_country'; + return 'pmc'; + case self::MENTOR_TERM: + $this->pms['pmt'] = 'profile_mentor_term'; + $this->mjtr = true; + return 'mjtr'; + default: + Platal::page()->killError("Undefined mentor filter."); + } + } + + protected function mentorJoins() + { + $joins = array(); + foreach ($this->pms as $sub => $tab) { + $joins[$sub] = PlSqlJoin::left($tab, '$ME.pid = $PID'); + } + if ($this->mjtr) { + $joins['mjtr'] = PlSqlJoin::left('profile_job_term_relation', '$ME.jtid_2 = pmt.jtid'); + } + return $joins; + } + + /** CONTACTS + */ + private $cts = array(); + public function addContactFilter($uid = null) + { + $this->requireProfiles(); + return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid); + } + + protected function contactJoins() + { + $joins = array(); + foreach ($this->cts as $sub=>$key) { + if (is_null($key)) { + $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.contact = $PID'); + } else { + $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.uid = {?} AND $ME.contact = $PID', substr($key, 5)); + } + } + return $joins; + } + + + /** CARNET + */ + private $wn = array(); + public function addWatchRegistrationFilter($uid = null) + { + $this->requireAccounts(); + return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid); + } + + private $wp = array(); + public function addWatchPromoFilter($uid = null) + { + $this->requireAccounts(); + return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid); + } + + private $w = array(); + public function addWatchFilter($uid = null) + { + $this->requireAccounts(); + return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid); + } + + protected function watchJoins() + { + $joins = array(); + foreach ($this->w as $sub=>$key) { + if (is_null($key)) { + $joins['w' . $sub] = PlSqlJoin::left('watch'); + } else { + $joins['w' . $sub] = PlSqlJoin::left('watch', '$ME.uid = {?}', substr($key, 5)); + } + } + foreach ($this->wn as $sub=>$key) { + if (is_null($key)) { + $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID'); + } else { + $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5)); + } + } + foreach ($this->wn as $sub=>$key) { + if (is_null($key)) { + $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID'); + } else { + $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5)); + } + } + foreach ($this->wp as $sub=>$key) { + if (is_null($key)) { + $joins['wp' . $sub] = PlSqlJoin::left('watch_promo'); + } else { + $joins['wp' . $sub] = PlSqlJoin::left('watch_promo', '$ME.uid = {?}', substr($key, 5)); + } + } + return $joins; + } + + + /** PHOTOS + */ + private $with_photo; + public function addPhotoFilter() + { + $this->requireProfiles(); + $this->with_photo = true; + return 'photo'; + } + + protected function photoJoins() + { + if ($this->with_photo) { + return array('photo' => PlSqlJoin::left('profile_photos', '$ME.pid = $PID')); + } else { + return array(); + } + } + + + /** MARKETING + */ + private $with_rm; + public function addMarketingHash() + { + $this->requireAccounts(); + $this->with_rm = true; + } + + protected function marketingJoins() + { + if ($this->with_rm) { + return array('rm' => PlSqlJoin::left('register_marketing', '$ME.uid = $UID')); + } else { + return array(); + } + } + + + /** PARTNER SHARING + */ + + // Lists partner shortnames in use, as a $partner_shortname => true map. + private $ppss = array(); + + /** Add a filter on user having settings for a given partner. + * @param $partner_id the ID of the partner + * @return the name of the table to use in joins (e.g ppss_$partner_id). + */ + public function addPartnerSharingFilter($partner_id) + { + $this->requireProfiles(); + $sub = "ppss_" . $partner_id; + $this->ppss[$sub] = $partner_id; + return $sub; + } + + protected function partnerSharingJoins() + { + $joins = array(); + foreach ($this->ppss as $sub => $partner_id) { + $joins[$sub] = PlSqlJoin::left('profile_partnersharing_settings', '$ME.pid = $PID AND $ME.partner_id = {?} AND $ME.sharing_level != \'none\'', $partner_id); + } + return $joins; + } + + public function restrictVisibilityForPartner($partner_id) + { + $sub = $this->addPartnerSharingFilter($partner_id); + $this->visibility_field = $sub . '.sharing_level'; + } + + /** VISIBILITY + */ + private $vlevels = array(); + private $vfields = array(); + public function addVisibilityAbsoluteFilter($level) + { + $sub = 'pvel_' . $level; + $this->vlevels[$level] = $sub; + return $sub; + } + + public function addVisibilityFieldFilter($field) + { + $sub = 'pvef_' . self::getDBSuffix($field); + $this->vfields[$field] = $sub; + return $sub; + } + + /** Since this method might perform inner joins on tables which have been + * joined previously (e.g when using addVisibilityFieldFilter), it has to + * come after the Joins() methods for those tables. + * This is due to the implementation logic for discovering joins and the + * ordering used by PHP introspection. + */ + protected function visibilityJoins() + { + $joins = array(); + foreach ($this->vlevels as $level => $sub) { + $joins[$sub] = PlSqlJoin::inner('profile_visibility_enum', '$ME.access_level = {?}', $level); + } + foreach ($this->vfields as $field => $sub) { + $joins[$sub] = PlSqlJoin::inner('profile_visibility_enum', '$ME.access_level = ' . $field); + } + return $joins; + } + +} +// }}} +// {{{ class ProfileFilter +class ProfileFilter extends UserFilter +{ + public function get($limit = null) + { + return $this->getProfiles($limit); + } + + public function getIds($limit = null) + { + return $this->getPIDs(); + } + + public function filter(array $profiles, $limit = null) + { + return $this->filterProfiles($profiles, self::defaultLimit($limit)); + } + + public function getTotalCount() + { + return $this->getTotalProfileCount(); + } + + public function getGroups() + { + return $this->getPIDGroups(); + } } +// }}} -// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: +// vim:set et sw=4 sts=4 sws=4 foldmethod=marker fenc=utf-8: ?>