X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;ds=sidebyside;f=classes%2Fuserfilter.php;h=14e95578dc5bd85fbddf6761f8a0bb3f3b9d1ba5;hb=adbcb1b5252fc261a5f2f0e49a8c2b75863472b9;hp=2a9d077a9f6640d61165c4fe820918c080026d22;hpb=ccc951d9ef4b747cf4c5b08d52e6f8938338f6de;p=platal.git diff --git a/classes/userfilter.php b/classes/userfilter.php index 2a9d077..14e9557 100644 --- a/classes/userfilter.php +++ b/classes/userfilter.php @@ -52,6 +52,56 @@ class UFC_HasProfile implements UserFilterCondition } // }}} +// {{{ class UFC_AccountType +/** Filters users who have one of the given account types + */ +class UFC_AccountType implements UserFilterCondition +{ + private $types; + + public function __construct() + { + $this->types = pl_flatten(func_get_args()); + } + + public function buildCondition(PlFilter &$uf) + { + $uf->requireAccounts(); + return XDB::format('a.type IN {?}', $this->types); + } +} +// }}} + +// {{{ class UFC_AccountPerm +/** Filters users who have one of the given permissions + */ +class UFC_AccountPerm implements UserFilterCondition +{ + private $perms; + + public function __construct() + { + $this->perms = pl_flatten(func_get_args()); + } + + public function buildCondition(PlFilter &$uf) + { + $uf->requirePerms(); + $conds = array(); + foreach ($this->perms as $perm) { + $conds[] = XDB::format('FIND_IN_SET({?}, IF(a.user_perms IS NULL, at.perms, + CONCAT(at.perms, \',\', a.user_perms)))', + $perm); + } + if (empty($conds)) { + return self::COND_TRUE; + } else { + return implode(' OR ', $conds); + } + } +} +// }}} + // {{{ class UFC_Hruid /** Filters users based on their hruid * @param $val Either an hruid, or a list of those @@ -129,7 +179,7 @@ class UFC_Comment implements UserFilterCondition public function buildCondition(PlFilter &$uf) { $uf->requireProfiles(); - return 'p.freetext ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->text); + return $uf->getVisibilityCondition('p.freetext_pub') . ' AND p.freetext ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->text); } } // }}} @@ -364,7 +414,7 @@ class UFC_NameTokens implements UserFilterCondition foreach ($this->tokens as $i => $token) { $sub = $uf->addNameTokensFilter($token); if ($this->soundex) { - $c = XDB::format($sub . '.soundex = {?}', $token); + $c = XDB::format($sub . '.soundex = {?}', soundex_fr($token)); } else if ($this->exact) { $c = XDB::format($sub . '.token = {?}', $token); } else { @@ -557,6 +607,10 @@ class UFC_Group implements UserFilterCondition public function buildCondition(PlFilter &$uf) { + // Groups have AX visibility. + if ($uf->getVisibilityLevel() == ProfileVisibility::VIS_PUBLIC) { + return self::COND_TRUE; + } $sub = $uf->addGroupFilter($this->group); $where = 'gpm' . $sub . '.perms IS NOT NULL'; if ($this->anim) { @@ -582,6 +636,10 @@ class UFC_Binet implements UserFilterCondition public function buildCondition(PlFilter &$uf) { + // Binets are private. + if ($uf->getVisibilityLevel() != ProfileVisibility::VIS_PRIVATE) { + return self::CONF_TRUE; + } $sub = $uf->addBinetsFilter(); return XDB::format($sub . '.binet_id IN {?}', $this->val); } @@ -603,6 +661,10 @@ class UFC_Section implements UserFilterCondition public function buildCondition(PlFilter &$uf) { + // Sections are private. + if ($uf->getVisibilityLevel() != ProfileVisibility::VIS_PRIVATE) { + return self::CONF_TRUE; + } $uf->requireProfiles(); return XDB::format('p.section IN {?}', $this->section); } @@ -707,9 +769,10 @@ abstract class UFC_Address implements UserFilterCondition $this->flags = $flags; } - protected function initConds($sub) + protected function initConds($sub, $vis_cond) { - $conds = array(); + $conds = array($vis_cond); + $types = array(); foreach (self::$typetexts as $flag => $type) { if ($flag & $this->type) { @@ -766,7 +829,7 @@ class UFC_AddressText extends UFC_Address public function buildCondition(PlFilter &$uf) { $sub = $uf->addAddressFilter(); - $conds = $this->initConds($sub); + $conds = $this->initConds($sub, $uf->getVisibilityCondition($sub . '.pub')); if ($this->text != null) { $conds[] = $sub . '.text' . $this->mkMatch($this->text); } @@ -823,7 +886,7 @@ class UFC_AddressField extends UFC_Address public function buildCondition(PlFilter &$uf) { $sub = $uf->addAddressFilter(); - $conds = $this->initConds($sub); + $conds = $this->initConds($sub, $uf->getVisibilityCondition($sub . '.pub')); switch ($this->fieldtype) { case self::FIELD_COUNTRY: @@ -879,6 +942,7 @@ class UFC_Corps implements UserFilterCondition */ $sub = $uf->addCorpsFilter($this->type); $cond = $sub . '.abbreviation = ' . $corps; + $cond .= ' AND ' . $uf->getVisibilityCondition($sub . '.corps_pub'); return $cond; } } @@ -899,10 +963,14 @@ class UFC_Corps_Rank implements UserFilterCondition public function buildCondition(PlFilter &$uf) { /** Tables shortcuts: + * pc for profile_corps * pcr for profile_corps_rank */ $sub = $uf->addCorpsRankFilter(); $cond = $sub . '.abbreviation = ' . $rank; + // XXX(x2006barrois): find a way to get rid of that hardcoded + // reference to 'pc'. + $cond .= ' AND ' . $uf->getVisibilityCondition('pc.corps_pub'); return $cond; } } @@ -940,42 +1008,40 @@ class UFC_Job_Company implements UserFilterCondition { $sub = $uf->addJobCompanyFilter(); $cond = $sub . '.' . $this->type . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->value); + $jsub = $uf->addJobFilter(); + $cond .= ' AND ' . $uf->getVisibilityCondition($jsub . '.pub'); return $cond; } } // }}} -// {{{ class UFC_Job_Sectorization -/** Filters users based on the ((sub)sub)sector they work in - * @param $val The ID of the sector, or an array of such IDs - * @param $type The kind of search (subsubsector/subsector/sector) +// {{{ class UFC_Job_Terms +/** Filters users based on the job terms they assigned to one of their + * jobs. + * @param $val The ID of the job term, or an array of such IDs */ -class UFC_Job_Sectorization implements UserFilterCondition +class UFC_Job_Terms implements UserFilterCondition { private $val; - private $type; - public function __construct($val, $type = UserFilter::JOB_SECTOR) + public function __construct($val) { - self::assertType($type); if (!is_array($val)) { $val = array($val); } $this->val = $val; - $this->type = $type; - } - - private static function assertType($type) - { - if ($type != UserFilter::JOB_SECTOR && $type != UserFilter::JOB_SUBSECTOR && $type != UserFilter::JOB_SUBSUBSECTOR) { - Platal::page()->killError("Type de secteur non valide."); - } } public function buildCondition(PlFilter &$uf) { - $sub = $uf->addJobSectorizationFilter($this->type); - return $sub . '.id = ' . XDB::format('{?}', $this->val); + $sub = $uf->addJobTermsFilter(count($this->val)); + $conditions = array(); + foreach ($this->val as $i => $jtid) { + $conditions[] = $sub[$i] . '.jtid_1 = ' . XDB::escape($jtid); + } + $jsub = $uf->addJobFilter(); + $conditions[] = $uf->getVisibilityCondition($jsub . '.pub'); + return implode(' AND ', $conditions); } } // }}} @@ -983,7 +1049,7 @@ class UFC_Job_Sectorization implements UserFilterCondition // {{{ class UFC_Job_Description /** Filters users based on their job description * @param $description The text being searched for - * @param $fields The fields to search for (user-defined, ((sub|)sub|)sector) + * @param $fields The fields to search for (CV, user-defined) */ class UFC_Job_Description implements UserFilterCondition { @@ -1000,28 +1066,25 @@ class UFC_Job_Description implements UserFilterCondition public function buildCondition(PlFilter &$uf) { $conds = array(); + + $jsub = $uf->addJobFilter(); + // CV is private => if only CV requested, and not private, + // don't do anything. Otherwise restrict to standard job visibility. + if ($this->fields == UserFilter::JOB_CV) { + if ($uf->getVisibilityLevel() != ProfileVisibility::VIS_PRIVATE) { + return self::CONF_TRUE; + } + } else { + $conds[] = $uf->getVisibilityCondition($jsub . '.pub'); + } + if ($this->fields & UserFilter::JOB_USERDEFINED) { - $sub = $uf->addJobFilter(); - $conds[] = $sub . '.description ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); + $conds[] = $jsub . '.description ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); } - if ($this->fields & UserFilter::JOB_CV) { + if ($this->fields & UserFilter::JOB_CV && $uf->getVisibilityLevel() == ProfileVisibility::VIS_PRIVATE) { $uf->requireProfiles(); $conds[] = 'p.cv ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); } - if ($this->fields & UserFilter::JOB_SECTOR) { - $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SECTOR); - $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); - } - if ($this->fields & UserFilter::JOB_SUBSECTOR) { - $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSECTOR); - $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); - } - if ($this->fields & UserFilter::JOB_SUBSUBSECTOR) { - $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSUBSECTOR); - $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); - $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_ALTERNATES); - $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); - } return implode(' OR ', $conds); } } @@ -1047,6 +1110,7 @@ class UFC_Networking implements UserFilterCondition { $sub = $uf->addNetworkingFilter(); $conds = array(); + $conds[] = $uf->getVisibilityCondition($sub . '.pub'); $conds[] = $sub . '.address ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->value); if ($this->type != -1) { $conds[] = $sub . '.nwid = ' . XDB::format('{?}', $this->type); @@ -1091,6 +1155,9 @@ class UFC_Phone implements UserFilterCondition { $sub = $uf->addPhoneFilter(); $conds = array(); + + $conds[] = $uf->getVisibilityCondition($sub . '.pub'); + $conds[] = $sub . '.search_tel = ' . XDB::format('{?}', $this->number); if ($this->num_type != self::NUM_ANY) { $conds[] = $sub . '.link_type = ' . XDB::format('{?}', $this->num_type); @@ -1122,7 +1189,12 @@ class UFC_Medal implements UserFilterCondition public function buildCondition(PlFilter &$uf) { $conds = array(); + + // This will require profiles => table 'p' will be available. $sub = $uf->addMedalFilter(); + + $conds[] = $uf->getVisibilityCondition('p.medals_pub'); + $conds[] = $sub . '.mid = ' . XDB::format('{?}', $this->medal); if ($this->grade != null) { $conds[] = $sub . '.gid = ' . XDB::format('{?}', $this->grade); @@ -1139,12 +1211,24 @@ class UFC_Photo implements UserFilterCondition { public function buildCondition(PlFilter &$uf) { - $uf->addPhotoFilter(); - return 'photo.attach IS NOT NULL'; + $sub = $uf->addPhotoFilter(); + return $sub . '.attach IS NOT NULL AND ' . $uf->getVisibilityCondition($sub . '.pub'); } } // }}} +// {{{ class UFC_Mentor +class UFC_Mentor implements UserFilterCondition +{ + public function buildCondition(PlFilter &$uf) + { + $sub = $uf->addMentorFilter(UserFilter::MENTOR); + return $sub . '.expertise IS NOT NULL'; + } +} +// }}} + + // {{{ class UFC_Mentor_Expertise /** Filters users by mentoring expertise * @param $expertise Domain of expertise @@ -1187,33 +1271,23 @@ class UFC_Mentor_Country implements UserFilterCondition } // }}} -// {{{ class UFC_Mentor_Sectorization -/** Filters users based on mentoring (sub|)sector - * @param $sector ID of (sub)sector - * @param $type Whether we are looking for a sector or a subsector +// {{{ class UFC_Mentor_Terms +/** Filters users based on the job terms they used in mentoring. + * @param $val The ID of the job term, or an array of such IDs */ -class UFC_Mentor_Sectorization implements UserFilterCondition +class UFC_Mentor_Terms implements UserFilterCondition { - const SECTOR = 1; - const SUBSECTOR = 2; - private $sector; - private $type; + private $val; - public function __construct($sector, $type = self::SECTOR) + public function __construct($val) { - $this->sector = $sector; - $this->type = $type; + $this->val = $val; } public function buildCondition(PlFilter &$uf) { - $sub = $uf->addMentorFilter(UserFilter::MENTOR_SECTOR); - if ($this->type == self::SECTOR) { - $field = 'sectorid'; - } else { - $field = 'subsectorid'; - } - return $sub . '.' . $field . ' = ' . XDB::format('{?}', $this->sector); + $sub = $uf->addMentorFilter(UserFilter::MENTOR_TERM); + return $sub . '.jtid_1 = ' . XDB::escape($this->val); } } // }}} @@ -1593,6 +1667,9 @@ class UserFilter extends PlFilter private $query = null; private $orderby = null; + // Store the current 'search' visibility. + private $profile_visibility = null; + private $lastusercount = null; private $lastprofilecount = null; @@ -1621,6 +1698,29 @@ class UserFilter extends PlFilter } } } + + // This will set the visibility to the default correct level. + $this->profile_visibility = new ProfileVisibility(); + } + + public function getVisibilityLevels() + { + return $this->profile_visibility->levels(); + } + + public function getVisibilityLevel() + { + return $this->profile_visibility->level(); + } + + public function restrictVisibilityTo($level) + { + $this->profile_visibility->setLevel($level); + } + + public function getVisibilityCondition($field) + { + return $field . ' IN ' . XDB::formatArray($this->getVisibilityLevels()); } private function buildQuery() @@ -1677,10 +1777,10 @@ class UserFilter extends PlFilter $this->buildQuery(); $token = $this->grouper->getGroupToken($this); - $groups = XDB::fetchAllRow('SELECT ' . $token . ', COUNT(a.uid) - ' . $this->query . ' - GROUP BY ' . $token, - 0); + $groups = XDB::rawFetchAllRow('SELECT ' . $token . ', COUNT(a.uid) + ' . $this->query . ' + GROUP BY ' . $token, + 0); return $groups; } @@ -1690,10 +1790,10 @@ class UserFilter extends PlFilter $this->buildQuery(); $token = $this->grouper->getGroupToken($this); - $groups = XDB::fetchAllRow('SELECT ' . $token . ', COUNT(p.pid) - ' . $this->query . ' - GROUP BY ' . $token, - 0); + $groups = XDB::rawFetchAllRow('SELECT ' . $token . ', COUNT(p.pid) + ' . $this->query . ' + GROUP BY ' . $token, + 0); return $groups; } @@ -1706,11 +1806,11 @@ class UserFilter extends PlFilter if (!empty($uids)) { $cond = XDB::format(' AND a.uid IN {?}', $uids); } - $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS a.uid - ' . $this->query . $cond . ' - GROUP BY a.uid - ' . $this->orderby . ' - ' . $lim); + $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; } @@ -1724,11 +1824,11 @@ class UserFilter extends PlFilter if (!is_null($pids)) { $cond = XDB::format(' AND p.pid IN {?}', $pids); } - $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS p.pid - ' . $this->query . $cond . ' - GROUP BY p.pid - ' . $this->orderby . ' - ' . $lim); + $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; } @@ -1747,8 +1847,9 @@ class UserFilter extends PlFilter { $this->requireAccounts(); $this->buildQuery(); - $count = (int)XDB::fetchOneCell('SELECT COUNT(*) - ' . $this->query . XDB::format(' AND a.uid = {?}', $user->id())); + $count = (int)XDB::rawFetchOneCell('SELECT COUNT(*) + ' . $this->query + . XDB::format(' AND a.uid = {?}', $user->id())); return $count == 1; } @@ -1758,8 +1859,9 @@ class UserFilter extends PlFilter { $this->requireProfiles(); $this->buildQuery(); - $count = (int)XDB::fetchOneCell('SELECT COUNT(*) - ' . $this->query . XDB::format(' AND p.pid = {?}', $profile->id())); + $count = (int)XDB::rawFetchOneCell('SELECT COUNT(*) + ' . $this->query + . XDB::format(' AND p.pid = {?}', $profile->id())); return $count == 1; } @@ -1910,7 +2012,7 @@ class UserFilter extends PlFilter if (is_null($this->lastusercount)) { $this->requireAccounts(); $this->buildQuery(); - return (int)XDB::fetchOneCell('SELECT COUNT(DISTINCT a.uid) + return (int)XDB::rawFetchOneCell('SELECT COUNT(DISTINCT a.uid) ' . $this->query); } else { return $this->lastusercount; @@ -1922,7 +2024,7 @@ class UserFilter extends PlFilter if (is_null($this->lastprofilecount)) { $this->requireProfiles(); $this->buildQuery(); - return (int)XDB::fetchOneCell('SELECT COUNT(DISTINCT p.pid) + return (int)XDB::rawFetchOneCell('SELECT COUNT(DISTINCT p.pid) ' . $this->query); } else { return $this->lastprofilecount; @@ -2037,6 +2139,25 @@ class UserFilter extends PlFilter 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(); + } + } + /** DISPLAY */ const DISPLAY = 'display'; @@ -2456,30 +2577,18 @@ class UserFilter extends PlFilter /** JOBS */ - const JOB_SECTOR = 0x0001; - const JOB_SUBSECTOR = 0x0002; - const JOB_SUBSUBSECTOR = 0x0004; - const JOB_ALTERNATES = 0x0008; - const JOB_USERDEFINED = 0x0010; - const JOB_CV = 0x0020; - - const JOB_SECTORIZATION = 0x000F; - const JOB_ANY = 0x003F; + const JOB_USERDEFINED = 0x0001; + const JOB_CV = 0x0002; + const JOB_ANY = 0x0003; /** Joins : * pj => profile_job * pje => profile_job_enum - * pjse => profile_job_sector_enum - * pjsse => profile_job_subsector_enum - * pjssse => profile_job_subsubsector_enum - * pja => profile_job_alternates + * pjt => profile_job_terms */ - private $with_pj = false; + private $with_pj = false; private $with_pje = false; - private $with_pjse = false; - private $with_pjsse = false; - private $with_pjssse = false; - private $with_pja = false; + private $with_pjt = 0; public function addJobFilter() { @@ -2495,22 +2604,19 @@ class UserFilter extends PlFilter return 'pje'; } - public function addJobSectorizationFilter($type) + /** + * 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->addJobFilter(); - if ($type == self::JOB_SECTOR) { - $this->with_pjse = true; - return 'pjse'; - } else if ($type == self::JOB_SUBSECTOR) { - $this->with_pjsse = true; - return 'pjsse'; - } else if ($type == self::JOB_SUBSUBSECTOR) { - $this->with_pjssse = true; - return 'pjssse'; - } else if ($type == self::JOB_ALTERNATES) { - $this->with_pja = true; - return 'pja'; + $this->with_pjt = $nb; + $jobtermstable = array(); + for ($i = 1; $i <= $nb; ++$i) { + $jobtermstable[] = 'pjtr_'.$i; } + return $jobtermstable; } protected function jobJoins() @@ -2522,17 +2628,11 @@ class UserFilter extends PlFilter if ($this->with_pje) { $joins['pje'] = PlSqlJoin::left('profile_job_enum', '$ME.id = pj.jobid'); } - if ($this->with_pjse) { - $joins['pjse'] = PlSqlJoin::left('profile_job_sector_enum', '$ME.id = pj.sectorid'); - } - if ($this->with_pjsse) { - $joins['pjsse'] = PlSqlJoin::left('profile_job_subsector_enum', '$ME.id = pj.subsectorid'); - } - if ($this->with_pjssse) { - $joins['pjssse'] = PlSqlJoin::left('profile_job_subsubsector_enum', '$ME.id = pj.subsubsectorid'); - } - if ($this->with_pja) { - $joins['pja'] = PlSqlJoin::left('profile_job_alternates', '$ME.subsubsectorid = pj.subsubsectorid'); + 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; } @@ -2602,23 +2702,29 @@ class UserFilter extends PlFilter */ private $pms = array(); - const MENTOR_EXPERTISE = 1; - const MENTOR_COUNTRY = 2; - const MENTOR_SECTOR = 3; + private $mjtr = false; + const MENTOR = 1; + const MENTOR_EXPERTISE = 2; + const MENTOR_COUNTRY = 3; + const MENTOR_TERM = 4; public function addMentorFilter($type) { $this->requireAccounts(); 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_SECTOR: - $this->pms['pms'] = 'profile_mentor_sector'; - return 'pms'; + case self::MENTOR_TERM: + $this->pms['pmt'] = 'profile_mentor_term'; + $this->mjtr = true; + return 'mjtr'; default: Platal::page()->killError("Undefined mentor filter."); } @@ -2630,6 +2736,9 @@ class UserFilter extends PlFilter 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; } @@ -2721,6 +2830,7 @@ class UserFilter extends PlFilter { $this->requireProfiles(); $this->with_photo = true; + return 'photo'; } protected function photoJoins()