X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=classes%2Fuserfilter.php;h=dffa8a7191f6dd15eb02152f156457825c378bcd;hb=fda78a6777a5f385537fc9f291afb310e12525e5;hp=ebe69f4c508be53d199a01e814ce1df0cec55440;hpb=483da16573717c21c7e6da3eacc094048ab7d72f;p=platal.git diff --git a/classes/userfilter.php b/classes/userfilter.php index ebe69f4..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 { - const COND_TRUE = 'TRUE'; - const COND_FALSE = 'FALSE'; + protected $joinMethods = array(); - /** Check that the given user matches the rule. - */ - public function buildCondition(UserFilter &$uf); -} + protected $joinMetas = array( + '$PID' => 'p.pid', + '$UID' => 'a.uid', + ); -abstract class UFC_OneChild implements UserFilterCondition -{ - protected $child; - - public function __construct($child = null) - { - if (!is_null($child) && ($child instanceof UserFilterCondition)) { - $this->setChild($child); - } - } + private $root; + private $sort = array(); + private $grouper = null; + private $query = null; + private $orderby = null; - public function setChild(UserFilterCondition &$cond) - { - $this->child =& $cond; - } -} + // Store the current 'search' visibility. + private $visibility = null; + // If the 'search' visibility should be based on a DB field instead. + private $visibility_field = null; -abstract class UFC_NChildren implements UserFilterCondition -{ - protected $children = array(); + private $lastusercount = null; + private $lastprofilecount = null; - public function __construct() + public function __construct($cond = null, $sort = null) { - $children = func_get_args(); - foreach ($children as &$child) { - if (!is_null($child) && ($child instanceof UserFilterCondition)) { - $this->addChild($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; + } } } - } - - public function addChild(UserFilterCondition &$cond) - { - $this->children[] =& $cond; - } - - protected function catConds(array $cond, $op, $fallback) - { - if (count($cond) == 0) { - return $fallback; - } else if (count($cond) == 1) { - return $cond[0]; - } else { - return '(' . implode(') ' . $op . ' (', $cond) . ')'; - } - } -} - -class UFC_True implements UserFilterCondition -{ - public function buildCondition(UserFilter &$uf) - { - return self::COND_TRUE; - } -} - -class UFC_False implements UserFilterCondition -{ - public function buildCondition(UserFilter &$uf) - { - return self::COND_FALSE; - } -} - -class UFC_Not extends UFC_OneChild -{ - public function buildCondition(UserFilter &$uf) - { - $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 . ')'; - } - } -} - -class UFC_And extends UFC_NChildren -{ - public function buildCondition(UserFilter &$uf) - { - if (empty($this->children)) { - return self::COND_FALSE; - } 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; - } + if (!is_null($cond)) { + if ($cond instanceof PlFilterCondition) { + $this->setCondition($cond); } - return $this->catConds($conds, 'AND', $true); } - } -} - -class UFC_Or extends UFC_NChildren -{ - public function buildCondition(UserFilter &$uf) - { - 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; + if (!is_null($sort)) { + if ($sort instanceof PlFilterOrder) { + $this->addSort($sort); + } else if (is_array($sort)) { + foreach ($sort as $s) { + $this->addSort($s); } } - return $this->catConds($conds, 'OR', $true); } - } -} -class UFC_Profile implements UserFilterCondition -{ - public function buildCondition(UserFilter &$uf) - { - return '$PID IS NOT NULL'; + // This will set the visibility to the default correct level. + $this->visibility = Visibility::defaultForRead(); } -} - -class UFC_Promo implements UserFilterCondition -{ - - private $grade; - private $promo; - private $comparison; - public function __construct($comparison, $grade, $promo) - { - $this->grade = $grade; - $this->comparison = $comparison; - $this->promo = $promo; - if ($this->grade != UserFilter::DISPLAY) { - UserFilter::assertGrade($this->grade); + /** 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) + { + 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; } } - public function buildCondition(UserFilter &$uf) + /** 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->grade == UserFilter::DISPLAY) { - $sub = $uf->addDisplayFilter(); - return XDB::format('pd' . $sub . '.promo = {?}', $this->promo); + 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 { - $sub = $uf->addEducationFilter(true, $this->grade); - $field = 'pe' . $sub . '.' . UserFilter::promoYear($this->grade); - return $field . ' IS NOT NULL AND ' . $field . ' ' . $this->comparison . ' ' . XDB::format('{?}', $this->promo); + if ($this->visibility->isVisible($level)) { + return 'TRUE'; + } else { + return 'FALSE'; + } } } -} - -class UFC_Name implements UserFilterCondition -{ - const PREFIX = 1; - const SUFFIX = 2; - const PARTICLE = 7; - const VARIANTS = 8; - const CONTAINS = 3; - - private $type; - private $text; - private $mode; - - public function __construct($type, $text, $mode) - { - $this->type = $type; - $this->text = $text; - $this->mode = $mode; - } - private function buildNameQuery($type, $variant, $where, UserFilter &$uf) - { - $sub = $uf->addNameFilter($type, $variant); - return str_replace('$ME', 'pn' . $sub, $where); - } - - public function buildCondition(UserFilter &$uf) + private function buildQuery() { - $left = '$ME.name'; - $op = ' LIKE '; - if (($this->mode & self::PARTICLE) == self::PARTICLE) { - $left = 'CONCAT($ME.particle, \' \', $ME.name)'; + // 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 (($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); - } else { - $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text); + 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); } - $cond = $left . $op . $right; - $conds = array($this->buildNameQuery($this->type, null, $cond, $uf)); - if (($this->mode & self::VARIANTS) != 0 && isset(UserFilter::$name_variants[$this->type])) { - foreach (UserFilter::$name_variants[$this->type] as $var) { - $conds[] = $this->buildNameQuery($this->type, $var, $cond, $uf); + 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 . ')'; } - return implode(' OR ', $conds); } -} - -class UFC_Dead implements UserFilterCondition -{ - private $comparison; - private $date; - public function __construct($comparison = null, $date = null) + public function hasGroups() { - $this->comparison = $comparison; - $this->date = $date; + return $this->grouper != null; } - public function buildCondition(UserFilter &$uf) + public function getGroups() { - $str = 'p.deathdate IS NOT NULL'; - if (!is_null($this->comparison)) { - $str .= ' AND p.deathdate ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date)); - } - return $str; + return $this->getUIDGroups(); } -} - -class UFC_Registered implements UserFilterCondition -{ - private $active; - private $comparison; - private $date; - public function __construct($active = false, $comparison = null, $date = null) + public function getUIDGroups() { - $this->only_active = $active; - $this->comparison = $comparison; - $this->date = $date; - } - - public function buildCondition(UserFilter &$uf) - { - if ($this->active) { - $date = 'a.uid IS NOT NULL AND a.state = \'active\''; - } else { - $date = 'a.uid IS NOT NULL AND a.state != \'pending\''; - } - if (!is_null($this->comparison)) { - $date .= ' AND a.registration_date ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date)); - } - return $date; - } -} - -class UFC_ProfileUpdated implements UserFilterCondition -{ - private $comparison; - private $date; + $this->requireAccounts(); + $this->buildQuery(); + $token = $this->grouper->getGroupToken($this); - public function __construct($comparison = null, $date = null) - { - $this->comparison = $comparison; - $this->date = $date; + $groups = XDB::rawFetchAllRow('SELECT ' . $token . ', COUNT(a.uid) + ' . $this->query . ' + GROUP BY ' . $token, + 0); + return $groups; } - public function buildCondition(UserFilter &$uf) + public function getPIDGroups() { - return 'p.last_change ' . $this->comparison . XDB::format(' {?}', date('Y-m-d H:i:s', $this->date)); - } -} - -class UFC_Birthday implements UserFilterCondition -{ - private $comparison; - private $date; + $this->requireProfiles(); + $this->buildQuery(); + $token = $this->grouper->getGroupToken($this); - public function __construct($comparison = null, $date = null) - { - $this->comparison = $comparison; - $this->date = $date; + $groups = XDB::rawFetchAllRow('SELECT ' . $token . ', COUNT(p.pid) + ' . $this->query . ' + GROUP BY ' . $token, + 0); + return $groups; } - public function buildCondition(UserFilter &$uf) + private function getUIDList($uids = null, PlLimit $limit) { - return 'p.next_birthday ' . $this->comparison . XDB::format(' {?}', date('Y-m-d', $this->date)); + $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_Sex implements UserFilterCondition -{ - private $sex; - public function __construct($sex) + private function getPIDList($pids = null, PlLimit $limit) { - $this->sex = $sex; + $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; } - public function buildCondition(UserFilter &$uf) - { - if ($this->sex != User::GENDER_MALE && $this->sex != User::GENDER_FEMALE) { - return self::COND_FALSE; + private static function defaultLimit($limit) { + if ($limit == null) { + return new PlLimit(); } else { - return XDB::format('p.sex = {?}', $this->sex == User::GENDER_FEMALE ? 'female' : 'male'); + return $limit; } } -} -class UFC_Group implements UserFilterCondition -{ - private $group; - private $admin; - public function __construct($group, $admin = false) + /** Check that the user match the given rule. + */ + public function checkUser(PlUser $user) { - $this->group = $group; - $this->admin = $admin; + $this->requireAccounts(); + $this->buildQuery(); + $count = (int)XDB::rawFetchOneCell('SELECT COUNT(*) + ' . $this->query + . XDB::format(' AND a.uid = {?}', $user->id())); + return $count == 1; } - public function buildCondition(UserFilter &$uf) + /** Check that the profile match the given rule. + */ + public function checkProfile(Profile $profile) { - $sub = $uf->addGroupFilter($this->group); - $where = 'gpm' . $sub . '.perms IS NOT NULL'; - if ($this->admin) { - $where .= ' AND gpm' . $sub . '.perms = \'admin\''; - } - return $where; + $this->requireProfiles(); + $this->buildQuery(); + $count = (int)XDB::rawFetchOneCell('SELECT COUNT(*) + ' . $this->query + . XDB::format(' AND p.pid = {?}', $profile->id())); + return $count == 1; } -} -class UFC_Email implements UserFilterCondition -{ - private $email; - public function __construct($email) + /** Default filter is on users + */ + public function filter(array $users, $limit = null) { - $this->email = $email; + return $this->filterUsers($users, self::defaultLimit($limit)); } - public function buildCondition(UserFilter &$uf) + /** Filter a list of users to extract the users matching the rule. + */ + public function filterUsers(array $users, $limit = null) { - if (User::isForeignEmailAddress($this->email)) { - $sub = $uf->addEmailRedirectFilter($this->email); - return XDB::format('e' . $sub . '.email IS NOT NULL OR a.email = {?}', $this->email); - } else if (User::isVirtualEmailAddress($this->email)) { - $sub = $uf->addVirtualEmailFilter($this->email); - return 'vr' . $sub . '.redirect IS NOT NULL'; - } else { - @list($user, $domain) = explode('@', $this->email); - $sub = $uf->addAliasFilter($user); - return 'al' . $sub . '.alias IS NOT 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; + } + $uids[] = $uid; + $table[$uid] = $user; } - } -} - -class UFC_EmailList implements UserFilterCondition -{ - private $emails; - public function __construct($emails) - { - $this->emails = $emails; + $fetched = $this->getUIDList($uids, $limit); + $output = array(); + foreach ($fetched as $uid) { + $output[] = $table[$uid]; + } + return $output; } - public function buildCondition(UserFilter &$uf) + /** Filter a list of profiles to extract the users matching the rule. + */ + public function filterProfiles(array $profiles, $limit = null) { - $email = null; - $virtual = null; - $alias = null; - $cond = array(); - - if (count($this->emails) == 0) { - return UserFilterCondition::COND_TRUE; - } - - foreach ($this->emails as $entry) { - if (User::isForeignEmailAddress($entry)) { - if (is_null($email)) { - $email = $uf->addEmailRedirectFilter(); - } - $cond[] = XDB::format('e' . $email . '.email = {?} OR a.email = {?}', $entry, $entry); - } else if (User::isVirtualEmailAddress($entry)) { - if (is_null($virtual)) { - $virtual = $uf->addVirtualEmailFilter(); - } - $cond[] = XDB::format('vr' . $virtual . '.redirect IS NOT NULL AND v' . $virtual . '.alias = {?}', $entry); + $limit = self::defaultLimit($limit); + $this->requireProfiles(); + $this->buildQuery(); + $table = array(); + $pids = array(); + foreach ($profiles as $profile) { + if ($profile instanceof Profile) { + $pid = $profile->id(); } else { - if (is_null($alias)) { - $alias = $uf->addAliasFilter(); - } - @list($user, $domain) = explode('@', $entry); - $cond[] = XDB::format('al' . $alias . '.alias = {?}', $user); + $pid = $profile; } + $pids[] = $pid; + $table[$pid] = $profile; } - return '(' . implode(') OR (', $cond) . ')'; - } -} - -abstract class UFC_UserRelated implements UserFilterCondition -{ - protected $user; - public function __construct(PlUser &$user) - { - $this->user =& $user; + $fetched = $this->getPIDList($pids, $limit); + $output = array(); + foreach ($fetched as $pid) { + $output[] = $table[$pid]; + } + return $output; } -} -class UFC_Contact extends UFC_UserRelated -{ - public function buildCondition(UserFilter &$uf) + public function getUIDs($limit = null) { - $sub = $uf->addContactFilter($this->user->id()); - return 'c' . $sub . '.contact IS NOT NULL'; + $limit = self::defaultLimit($limit); + return $this->getUIDList(null, $limit); } -} -class UFC_WatchRegistration extends UFC_UserRelated -{ - public function buildCondition(UserFilter &$uf) + public function getUID($pos = 0) { - if (!$this->user->watch('registration')) { - return UserFilterCondition::COND_FALSE; - } - $uids = $this->user->watchUsers(); + $uids =$this->getUIDList(null, new PlLimit(1, $pos)); if (count($uids) == 0) { - return UserFilterCondition::COND_FALSE; + return null; } else { - return '$UID IN ' . XDB::formatArray($uids); + return $uids[0]; } } -} -class UFC_WatchPromo extends UFC_UserRelated -{ - private $grade; - public function __construct(PlUser &$user, $grade = UserFilter::GRADE_ING) + public function getPIDs($limit = null) { - parent::__construct($user); - $this->grade = $grade; + $limit = self::defaultLimit($limit); + return $this->getPIDList(null, $limit); } - public function buildCondition(UserFilter &$uf) + public function getPID($pos = 0) { - $promos = $this->user->watchPromos(); - if (count($promos) == 0) { - return UserFilterCondition::COND_FALSE; + $pids =$this->getPIDList(null, new PlLimit(1, $pos)); + if (count($pids) == 0) { + return null; } else { - $sube = $uf->addEducationFilter(true, $this->grade); - $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade); - return $field . ' IN ' . XDB::formatArray($promos); - } - } -} - -class UFC_WatchContact extends UFC_Contact -{ - public function buildCondition(UserFilter &$uf) - { - if (!$this->user->watchContacts()) { - return UserFilterCondition::COND_FALSE; + return $pids[0]; } - return parent::buildCondition($uf); } -} - -/****************** - * ORDERS - ******************/ - -abstract class UserFilterOrder -{ - protected $desc = false; - public function __construct($desc = false) + public function getUsers($limit = null) { - $this->desc = $desc; + return User::getBulkUsersWithUIDs($this->getUIDs($limit)); } - public function buildSort(UserFilter &$uf) + public function getUser($pos = 0) { - $sel = $this->getSortTokens($uf); - if (!is_array($sel)) { - $sel = array($sel); - } - if ($this->desc) { - foreach ($sel as $k=>$s) { - $sel[$k] = $s . ' DESC'; - } + $uid = $this->getUID($pos); + if ($uid == null) { + return null; + } else { + return User::getWithUID($uid); } - return $sel; - } - - abstract protected function getSortTokens(UserFilter &$uf); -} - -class UFO_Promo extends UserFilterOrder -{ - private $grade; - - public function __construct($grade = null, $desc = false) - { - parent::__construct($desc); - $this->grade = $grade; } - protected function getSortTokens(UserFilter &$uf) + public function iterUsers($limit = null) { - if (UserFilter::isGrade($this->grade)) { - $sub = $uf->addEducationFilter($this->grade); - return 'pe' . $sub . '.' . UserFilter::promoYear($this->grade); - } else { - $sub = $uf->addDisplayFilter(); - return 'pd' . $sub . '.promo'; - } + return User::iterOverUIDs($this->getUIDs($limit)); } -} - -class UFO_Name extends UserFilterOrder -{ - private $type; - private $variant; - private $particle; - public function __construct($type, $variant = null, $particle = false, $desc = false) + public function getProfiles($limit = null, $fields = 0x0000, $visibility = null) { - parent::__construct($desc); - $this->type = $type; - $this->variant = $variant; - $this->particle = $particle; + return Profile::getBulkProfilesWithPIDs($this->getPIDs($limit), $fields, $visibility); } - protected function getSortTokens(UserFilter &$uf) + public function getProfile($pos = 0, $fields = 0x0000, $visibility = null) { - if (UserFilter::isDisplayName($this->type)) { - $sub = $uf->addDisplayFilter(); - return 'pd' . $sub . '.' . $this->type; + $pid = $this->getPID($pos); + if ($pid == null) { + return null; } else { - $sub = $uf->addNameFilter($this->type, $this->variant); - if ($this->particle) { - return 'CONCAT(pn' . $sub . '.particle, \' \', pn' . $sub . '.name)'; - } else { - return 'pn' . $sub . '.name'; - } + return Profile::get($pid, $fields, $visibility); } } -} -class UFO_Registration extends UserFilterOrder -{ - protected function getSortTokens(UserFilter &$uf) + public function iterProfiles($limit = null, $fields = 0x0000, $visibility = null) { - return 'a.registration_date'; + return Profile::iterOverPIDs($this->getPIDs($limit), true, $fields, $visibility); } -} -class UFO_Birthday extends UserFilterOrder -{ - protected function getSortTokens(UserFilter &$uf) + public function get($limit = null) { - return 'p.next_birthday'; + return $this->getUsers($limit); } -} -class UFO_ProfileUpdate extends UserFilterOrder -{ - protected function getSortTokens(UserFilter &$uf) + public function getIds($limit = null) { - return 'p.last_change'; + return $this->getUIDs(); } -} -class UFO_Death extends UserFilterOrder -{ - protected function getSortTokens(UserFilter &$uf) + public function getTotalCount() { - return 'p.deathdate'; + return $this->getTotalUserCount(); } -} - - -/*********************************** - ********************************* - USER FILTER CLASS - ********************************* - ***********************************/ - -class UserFilter -{ - static private $joinMethods = array(); - private $root; - private $sort = array(); - private $query = null; - private $orderby = null; - - private $lastcount = null; - - public function __construct($cond = null, $sort = null) + public function getTotalUserCount() { - if (empty(self::$joinMethods)) { - $class = new ReflectionClass('UserFilter'); - foreach ($class->getMethods() as $method) { - $name = $method->getName(); - if (substr($name, -5) == 'Joins' && $name != 'buildJoins') { - self::$joinMethods[] = $name; - } - } - } - if (!is_null($cond)) { - if ($cond instanceof UserFilterCondition) { - $this->setCondition($cond); - } - } - if (!is_null($sort)) { - if ($sort instanceof UserFilterOrder) { - $this->addSort($sort); - } else if (is_array($sort)) { - foreach ($sort as $s) { - $this->addSort($s); - } - } + 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->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); - } - } - if (is_null($this->query)) { - $where = $this->root->buildCondition($this); - $joins = $this->buildJoins(); - $this->query = 'FROM accounts AS a - LEFT JOIN account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms)) - LEFT 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', '$UID'), array($key, 'p.pid', 'a.uid'), $infos[2]) . ')'; - } - $str .= "\n"; - } - return $str; + $this->root =& $cond; + $this->query = null; } - private function buildJoins() + public function addSort(PlFilterOrder $sort) { - $joins = array(); - foreach (self::$joinMethods as $method) { - $joins = array_merge($joins, $this->$method()); + if (count($this->sort) == 0 && $sort instanceof PlFilterGroupableOrder) + { + $this->grouper = $sort; } - return $this->formatJoin($joins); + $this->sort[] = $sort; + $this->orderby = null; } - private function getUIDList($uids = null, $count = null, $offset = null) + public function export() { - $this->buildQuery(); - $limit = ''; - if (!is_null($count)) { - if (!is_null($offset)) { - $limit = XDB::format('LIMIT {?}, {?}', (int)$offset, (int)$count); - } else { - $limit = XDB::format('LIMIT {?}', (int)$count); + $export = array('conditions' => $this->root->export()); + if (!empty($this->sort)) { + $export['sorts'] = array(); + foreach ($this->sort as $sort) { + $export['sorts'][] = $sort->export(); } } - $cond = ''; - if (!is_null($uids)) { - $cond = ' AND a.uid IN ' . XDB::formatArray($uids); - } - $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS a.uid - ' . $this->query . $cond . ' - GROUP BY a.uid - ' . $this->orderby . ' - ' . $limit); - $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()'); - return $fetched; + 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, $count = null, $offset = null) + public static function fromExport(array $export) { - $this->buildQuery(); - $table = array(); - $uids = array(); - foreach ($users as $user) { - if ($user instanceof PlUser) { - $uid = $user->id(); - } else { - $uid = $user; - } - $uids[] = $uid; - $table[$uid] = $user; + $export = new PlDict($export); + if (!$export->has('conditions')) { + throw new Exception("Cannot build a user filter without conditions"); } - $fetched = $this->getUIDList($uids, $count, $offset); - $output = array(); - foreach ($fetched as $uid) { - $output[] = $table[$uid]; + $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 getUIDs($count = null, $offset = null) + public static function fromJSon($json) { - return $this->getUIDList(null, $count, $offset); - } - - public function getUsers($count = null, $offset = null) - { - return User::getBulkUsersWithUIDs($this->getUIDs($count, $offset)); - } - - public function getTotalCount() - { - if (is_null($this->lastcount)) { - $this->buildQuery(); - return (int)XDB::fetchOneCell('SELECT COUNT(DISTINCT a.uid) - ' . $this->query); - } else { - return $this->lastcount; + $export = json_decode($json, true); + if (is_null($export)) { + throw new Exception("Invalid json: $json"); } + return self::fromExport($json); } - public function setCondition(UserFilterCondition &$cond) + public static function fromExportedConditions(array $export) { - $this->root =& $cond; - $this->query = null; + $cond = UserFilterCondition::fromExport($export); + return new UserFilter($cond); } - public function addSort(UserFilterOrder &$sort) + public static function fromJSonConditions($json) { - $this->sort[] = $sort; - $this->orderby = null; + $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) @@ -816,32 +556,44 @@ 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(self::LASTNAME), new UFO_Name(self::FIRSTNAME)); + return array(new UFO_Name()); } static public function sortByPromo() { - return array(new UFO_Promo(), new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME)); + return array(new UFO_Promo(), new UFO_Name()); } static private function getDBSuffix($string) { - return preg_replace('/[^a-z0-9]/i', '', $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) { @@ -849,12 +601,65 @@ class UserFilter $sub = $this->option++; $index = null; } else { - $sub = self::getDBSuffix($val); - $index = $val; + $sub = self::getDBSuffix($val); + $index = $val; + } + $sub = '_' . $sub; + $table[$sub] = $index; + return $sub; + } + + /** PROFILE VS ACCOUNT + */ + 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; + } + + /** PERMISSIONS + */ + private $at = false; + public function requirePerms() + { + $this->requireAccounts(); + $this->at = true; + return 'at'; + } + + protected function permJoins() + { + if ($this->at) { + return array('at' => PlSqlJoin::left('account_types', '$ME.type = a.type')); + } else { + return array(); } - $sub = '_' . $sub; - $table[$sub] = $index; - return $sub; } /** DISPLAY @@ -863,104 +668,101 @@ class UserFilter private $pd = false; public function addDisplayFilter() { + $this->requireProfiles(); $this->pd = true; return ''; } - private function displayJoins() + protected function displayJoins() { if ($this->pd) { - return array('pd' => array('left', 'profile_display', '$ME.pid = $PID')); + return array('pd' => PlSqlJoin::left('profile_display', '$ME.pid = $PID')); } else { return array(); } } - /** NAMES + /** LOGGER */ - /* name tokens */ - const LASTNAME = 'lastname'; - const FIRSTNAME = 'firstname'; - const NICKNAME = 'nickname'; - const PSEUDONYM = 'pseudonym'; - const NAME = 'name'; - /* name variants */ - const VN_MARITAL = 'marital'; - const VN_ORDINARY = 'ordinary'; - const VN_OTHER = 'other'; - const VN_INI = 'ini'; - /* display names */ - const DN_FULL = 'directory_name'; - const DN_DISPLAY = 'yourself'; - const DN_YOURSELF = 'yourself'; - const DN_DIRECTORY = 'directory_name'; - const DN_PRIVATE = 'private_name'; - const DN_PUBLIC = 'public_name'; - const DN_SHORT = 'short_name'; - const DN_SORT = 'sort_name'; - - 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) - ); - - static public function assertName($name) - { - if (!Profile::getNameTypeId($name)) { - Platal::page()->kill('Invalid name type'); - } - } - - static public function isDisplayName($name) - { - return $name == self::DN_FULL || $name == self::DN_DISPLAY - || $name == self::DN_YOURSELF || $name == self::DN_DIRECTORY - || $name == self::DN_PRIVATE || $name == self::DN_PUBLIC - || $name == self::DN_SHORT || $name == self::DN_SORT; - } - - private $pn = array(); - public function addNameFilter($type, $variant = null) - { - if (!is_null($variant)) { - $ft = $type . '_' . $variant; - } else { - $ft = $type; - } - $sub = '_' . $ft; - self::assertName($ft); - if (!is_null($variant) && $variant == 'other') { - $sub .= $this->option++; + 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 || $grade == self::GRADE_PHD || $grade == self::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"); } } @@ -974,6 +776,7 @@ class UserFilter private $with_pee = false; public function addEducationFilter($x = false, $grade = null) { + $this->requireProfiles(); if (!$x) { $index = $this->option; $sub = $this->option++; @@ -988,21 +791,20 @@ 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; @@ -1014,8 +816,9 @@ class UserFilter private $gpm = array(); public function addGroupFilter($group = null) { + $this->requireAccounts(); if (!is_null($group)) { - if (ctype_digit($group)) { + if (is_int($group) || ctype_digit($group)) { $index = $sub = $group; } else { $index = $group; @@ -1030,100 +833,449 @@ class UserFilter return $sub; } - private function groupJoins() + 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] = array('inner', 'groupex.asso'); - $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id'); - } else if (ctype_digit($key)) { - $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = ' . $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] = array('inner', 'groupex.asso', XDB::format('$ME.diminutif = {?}', $key)); - $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id'); + $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 $e = array(); + 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) { - return $this->register_optional($this->e, $email); + $this->requireAccounts(); + return $this->register_optional($this->ra, $email); } - private $ve = array(); - public function addVirtualEmailFilter($email = null) + 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->addAliasFilter(self::ALIAS_FORLIFE); - return $this->register_optional($this->ve, $email); + $this->requireAccounts(); + return $this->register_optional($this->sa, $email); } - const ALIAS_BEST = 'bestalias'; - const ALIAS_FORLIFE = 'forlife'; - private $al = array(); - public function addAliasFilter($alias = null) + private $with_rf = false; + /** Allows filtering by active redirection. + * @return Suffix to use to access the adequate table. + */ + public function addActiveEmailRedirectFilter($email = null) { - return $this->register_optional($this->al, $alias); + $this->requireAccounts(); + $this->with_rf = true; } - private function emailJoins() + protected function emailJoins() { global $globals; $joins = array(); - foreach ($this->e as $sub=>$key) { - if (is_null($key)) { - $joins['e' . $sub] = array('left', 'emails', '$ME.uid = $UID AND $ME.flags != \'filter\''); + 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 { - $joins['e' . $sub] = array('left', 'emails', XDB::format('$ME.uid = $UID AND $ME.flags != \'filter\' AND $ME.email = {?}', $key)); + 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->al as $sub=>$key) { - if (is_null($key)) { - $joins['al' . $sub] = array('left', 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\')'); - } else if ($key == self::ALIAS_BEST) { - $joins['al' . $sub] = array('left', 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND FIND_IN_SET(\'bestalias\', $ME.flags)'); - } else if ($key == self::ALIAS_FORLIFE) { - $joins['al' . $sub] = array('left', 'aliases', '$ME.id = $UID AND $ME.type = \'a_vie\''); + 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 { - $joins['al' . $sub] = array('left', 'aliases', XDB::format('$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND $ME.alias = {?}', $key)); + if (!is_array($emails)) { + $key = array($emails); + } + $joins['sa' . $sub] = PlSqlJoin::left('email_source_account', '$ME.uid = $UID AND $ME.email IN {?}', $emails); } } - foreach ($this->ve as $sub=>$key) { - if (is_null($key)) { - $joins['v' . $sub] = array('left', 'virtual', '$ME.type = \'user\''); - } else { - $joins['v' . $sub] = array('left', 'virtual', XDB::format('$ME.type = \'user\' AND $ME.alias = {?}', $key)); + 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'); } - $joins['vr' . $sub] = array('left', 'virtual_redirect', XDB::format('$ME.vid = v' . $sub . '.vid - AND ($ME.redirect IN (CONCAT(al_forlife.alias, \'@\', {?}), - CONCAT(al_forlife.alias, \'@\', {?}), - a.email))', - $globals->mail->domain, $globals->mail->domain2)); } 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); } - private function contactJoins() + protected function contactJoins() { $joins = array(); foreach ($this->cts as $sub=>$key) { if (is_null($key)) { - $joins['c' . $sub] = array('left', 'contacts', '$ME.contact = $UID'); + $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.contact = $PID'); } else { - $joins['c' . $sub] = array('left', 'contacts', XDB::format('$ME.uid = {?} AND $ME.contact = $UID', substr($key, 5))); + $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.uid = {?} AND $ME.contact = $PID', substr($key, 5)); } } return $joins; @@ -1135,56 +1287,198 @@ class UserFilter 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); } - private function watchJoins() + protected function watchJoins() { $joins = array(); foreach ($this->w as $sub=>$key) { if (is_null($key)) { - $joins['w' . $sub] = array('left', 'watch'); + $joins['w' . $sub] = PlSqlJoin::left('watch'); } else { - $joins['w' . $sub] = array('left', 'watch', XDB::format('$ME.uid = {?}', substr($key, 5))); + $joins['w' . $sub] = PlSqlJoin::left('watch', '$ME.uid = {?}', substr($key, 5)); } } foreach ($this->wn as $sub=>$key) { if (is_null($key)) { - $joins['wn' . $sub] = array('left', 'watch_nonins', '$ME.ni_id = $UID'); + $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID'); } else { - $joins['wn' . $sub] = array('left', 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5))); + $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] = array('left', 'watch_nonins', '$ME.ni_id = $UID'); + $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID'); } else { - $joins['wn' . $sub] = array('left', 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5))); + $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] = array('left', 'watch_promo'); + $joins['wp' . $sub] = PlSqlJoin::left('watch_promo'); } else { - $joins['wp' . $sub] = array('left', 'watch_promo', XDB::format('$ME.uid = {?}', substr($key, 5))); + $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: ?>