X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=classes%2Fuserfilter.php;h=050c6630b4f60a73c7dca834c30fee7ee2ca79e2;hb=2381efd034b0efc9a0a839d45086b47ccefccf89;hp=fa155f2c0ec639da3a373ed57c745a442c5a89cb;hpb=658b4c83a68ef3bee04742128743bf0687e9600e;p=platal.git diff --git a/classes/userfilter.php b/classes/userfilter.php index fa155f2..050c663 100644 --- a/classes/userfilter.php +++ b/classes/userfilter.php @@ -39,13 +39,14 @@ interface UserFilterCondition extends PlFilterCondition } // }}} -// {{{ class UFC_Profile +// {{{ class UFC_HasProfile /** Filters users who have a profile */ -class UFC_Profile implements UserFilterCondition +class UFC_HasProfile implements UserFilterCondition { public function buildCondition(PlFilter &$uf) { + $uf->requireProfiles(); return '$PID IS NOT NULL'; } } @@ -59,19 +60,36 @@ class UFC_Hruid implements UserFilterCondition { private $hruids; - public function __construct($val) + public function __construct() { - if (!is_array($val)) { - $val = array($val); - } - $this->hruids = $val; + $this->hruids = pl_flatten(func_get_args()); } public function buildCondition(PlFilter &$uf) { - $ufc->requireAccounts(); + $uf->requireAccounts(); + return XDB::format('a.hruid IN {?}', $this->hruids); + } +} +// }}} + +// {{{ class UFC_Hrpid +/** Filters users based on the hrpid of their profiles + * @param $val Either an hrpid, or a list of those + */ +class UFC_Hrpid implements UserFilterCondition +{ + private $hrpids; - return 'a.hruid IN ' . XDB::formatArray($this->hruids); + public function __construct() + { + $this->hrpids = pl_flatten(func_get_args()); + } + + public function buildCondition(PlFilter &$uf) + { + $uf->requireProfiles(); + return XDB::format('p.hrpid IN {?}', $this->hrpids); } } // }}} @@ -111,7 +129,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); } } // }}} @@ -137,6 +155,10 @@ class UFC_Promo implements UserFilterCondition if ($this->grade != UserFilter::DISPLAY) { UserFilter::assertGrade($this->grade); } + if ($this->grade == UserFilter::DISPLAY && $this->comparison != '=') { + // XXX: we might try to guess the grade from the first char of the promo and forbid only '<= 2004', but allow '<= X2004' + Platal::page()->killError("Il n'est pas possible d'appliquer la comparaison '" . $this->comparison . "' aux promotions sans spécifier de formation (X/M/D)"); + } } public function buildCondition(PlFilter &$uf) @@ -153,6 +175,48 @@ class UFC_Promo implements UserFilterCondition } // }}} +// {{{ class UFC_SchoolId +/** Filters users based on their shoold identifier + * @param type Parameter type (Xorg, AX, School) + * @param value School id value + */ +class UFC_SchoolId implements UserFilterCondition +{ + const AX = 'ax'; + const Xorg = 'xorg'; + const School = 'school'; + + private $type; + private $id; + + static public function assertType($type) + { + if ($type != self::AX && $type != self::Xorg && $type != self::School) { + Platal::page()->killError("Type de matricule invalide: $type"); + } + } + + public function __construct($type, $id) + { + $this->type = $type; + $this->id = $id; + self::assertType($type); + } + + public function buildCondition(PlFilter &$uf) + { + $uf->requireProfiles(); + $id = $this->id; + $type = $this->type; + if ($type == self::School) { + $type = self::Xorg; + $id = Profile::getXorgId($id); + } + return XDB::format('p.' . $type . '_id = {?}', $id); + } +} +// }}} + // {{{ class UFC_EducationSchool /** Filters users by formation * @param $val The formation to search (either ID or array of IDs) @@ -161,18 +225,15 @@ class UFC_EducationSchool implements UserFilterCondition { private $val; - public function __construct($val) + public function __construct() { - if (!is_array($val)) { - $val = array($val); - } - $this->val = $val; + $this->val = pl_flatten(func_get_args()); } public function buildCondition(PlFilter &$uf) { $sub = $uf->addEducationFilter(); - return 'pe' . $sub . '.eduid IN ' . XDB::formatArray($this->val); + return XDB::format('pe' . $sub . '.eduid IN {?}', $this->val); } } // }}} @@ -182,18 +243,15 @@ class UFC_EducationDegree implements UserFilterCondition { private $diploma; - public function __construct($diploma) + public function __construct() { - if (! is_array($diploma)) { - $diploma = array($diploma); - } - $this->diploma = $diploma; + $this->diploma = pl_flatten(func_get_args()); } public function buildCondition(PlFilter &$uf) { $sub = $uf->addEducationFilter(); - return 'pee' . $sub . '.degreeid IN ' . XDB::formatArray($this->val); + return XDB::format('pe' . $sub . '.degreeid IN {?}', $this->diploma); } } // }}} @@ -203,18 +261,15 @@ class UFC_EducationField implements UserFilterCondition { private $val; - public function __construct($val) + public function __construct() { - if (!is_array($val)) { - $val = array($val); - } - $this->val = $val; + $this->val = pl_flatten(func_get_args()); } public function buildCondition(PlFilter &$uf) { $sub = $uf->addEducationFilter(); - return 'pee' . $sub . '.fieldid IN ' . XDB::formatArray($this->val); + return XDB::format('pe' . $sub . '.fieldid IN {?}', $this->val); } } // }}} @@ -227,10 +282,11 @@ class UFC_EducationField implements UserFilterCondition */ class UFC_Name implements UserFilterCondition { - const PREFIX = XDB::WILDCARD_PREFIX; // 0x001 - const SUFFIX = XDB::WILDCARD_SUFFIX; // 0x002 + const EXACT = XDB::WILDCARD_EXACT; // 0x000 + const PREFIX = XDB::WILDCARD_PREFIX; // 0x001 + const SUFFIX = XDB::WILDCARD_SUFFIX; // 0x002 const CONTAINS = XDB::WILDCARD_CONTAINS; // 0x003 - const PARTICLE = 0x007; // self::CONTAINS | 0x004 + const PARTICLE = 0x004; const VARIANTS = 0x008; private $type; @@ -288,7 +344,11 @@ class UFC_NameTokens implements UserFilterCondition public function __construct($tokens, $flags = array(), $soundex = false, $exact = false) { - $this->tokens = $tokens; + if (is_array($tokens)) { + $this->tokens = $tokens; + } else { + $this->tokens = array($tokens); + } if (is_array($flags)) { $this->flags = $flags; } else { @@ -300,22 +360,20 @@ class UFC_NameTokens implements UserFilterCondition public function buildCondition(PlFilter &$uf) { - $sub = $uf->addNameTokensFilter(!($this->exact || $this->soundex)); $conds = array(); - if ($this->soundex) { - $conds[] = $sub . '.soundex IN ' . XDB::formatArray($this->tokens); - } else if ($this->exact) { - $conds[] = $sub . '.token IN ' . XDB::formatArray($this->tokens); - } else { - $tokconds = array(); - foreach ($this->tokens as $token) { - $tokconds[] = $sub . '.token ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $token); + foreach ($this->tokens as $i => $token) { + $sub = $uf->addNameTokensFilter($token); + if ($this->soundex) { + $c = XDB::format($sub . '.soundex = {?}', $token); + } else if ($this->exact) { + $c = XDB::format($sub . '.token = {?}', $token); + } else { + $c = $sub . '.token ' . XDB::formatWildcards(XDB::WILDCARD_PREFIX, $token); } - $conds[] = implode(' OR ', $tokconds); - } - - if ($this->flags != null) { - $conds[] = $sub . '.flags IN ' . XDB::formatArray($this->flags); + if ($this->flags != null) { + $c .= XDB::format(' AND ' . $sub . '.flags IN {?}', $this->flags); + } + $conds[] = $c; } return implode(' AND ', $conds); @@ -328,12 +386,9 @@ class UFC_Nationality implements UserFilterCondition { private $val; - public function __construct($val) + public function __construct() { - if (!is_array($val)) { - $val = array($val); - } - $this->val = $val; + $this->val = pl_flatten(func_get_args()); } public function buildCondition(PlFilter &$uf) @@ -353,7 +408,7 @@ class UFC_Nationality implements UserFilterCondition // {{{ class UFC_Dead /** Filters users based on death date * @param $comparison Comparison operator - * @param $date Date to which death date should be compared + * @param $date Date to which death date should be compared (DateTime object, string or timestamp) */ class UFC_Dead implements UserFilterCondition { @@ -363,7 +418,7 @@ class UFC_Dead implements UserFilterCondition public function __construct($comparison = null, $date = null) { $this->comparison = $comparison; - $this->date = $date; + $this->date = make_datetime($date); } public function buildCondition(PlFilter &$uf) @@ -371,7 +426,7 @@ class UFC_Dead implements UserFilterCondition $uf->requireProfiles(); $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)); + $str .= ' AND p.deathdate ' . $this->comparison . ' ' . XDB::format('{?}', $this->date->format('Y-m-d')); } return $str; } @@ -394,19 +449,19 @@ class UFC_Registered implements UserFilterCondition { $this->active = $active; $this->comparison = $comparison; - $this->date = $date; + $this->date = make_datetime($date); } public function buildCondition(PlFilter &$uf) { $uf->requireAccounts(); if ($this->active) { - $date = 'a.uid IS NOT NULL AND a.state = \'active\''; + $date = '$UID IS NOT NULL AND a.state = \'active\''; } else { - $date = 'a.uid IS NOT NULL AND a.state != \'pending\''; + $date = '$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)); + $date .= ' AND a.registration_date != \'0000-00-00 00:00:00\' AND a.registration_date ' . $this->comparison . ' ' . XDB::format('{?}', $this->date->format('Y-m-d')); } return $date; } @@ -502,6 +557,10 @@ class UFC_Group implements UserFilterCondition public function buildCondition(PlFilter &$uf) { + // Groups have AX visibility. + if ($uf->getVisibilityLevel() == ProfileVisibility::VIS_PUBLIC) { + return PlFilter::COND_TRUE; + } $sub = $uf->addGroupFilter($this->group); $where = 'gpm' . $sub . '.perms IS NOT NULL'; if ($this->anim) { @@ -520,18 +579,19 @@ class UFC_Binet implements UserFilterCondition { private $val; - public function __construct($val) + public function __construct() { - if (!is_array($val)) { - $val = array($val); - } - $this->val = $val; + $this->val = pl_flatten(func_get_args()); } public function buildCondition(PlFilter &$uf) { + // Binets are private. + if ($uf->getVisibilityLevel() != ProfileVisibility::VIS_PRIVATE) { + return PlFilter::COND_TRUE; + } $sub = $uf->addBinetsFilter(); - return $sub . '.binet_id IN ' . XDB::formatArray($this->val); + return XDB::format($sub . '.binet_id IN {?}', $this->val); } } // }}} @@ -544,65 +604,40 @@ class UFC_Section implements UserFilterCondition { private $section; - public function __construct($section) + public function __construct() { - $this->section = $section; + $this->section = pl_flatten(func_get_args()); } public function buildCondition(PlFilter &$uf) { + // Sections are private. + if ($uf->getVisibilityLevel() != ProfileVisibility::VIS_PRIVATE) { + return PlFilter::COND_TRUE; + } $uf->requireProfiles(); - return 'p.section = ' . XDB::format('{?}', $this->section); + return XDB::format('p.section IN {?}', $this->section); } } // }}} // {{{ class UFC_Email -/** Filters users based on email address - * @param $email Email whose owner we are looking for - */ -class UFC_Email implements UserFilterCondition -{ - private $email; - public function __construct($email) - { - $this->email = $email; - } - - public function buildCondition(PlFilter &$uf) - { - 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'; - } - } -} -// }}} - -// {{{ class UFC_EmailList -/** Filters users based on an email list +/** Filters users based on an email or a list of emails * @param $emails List of emails whose owner must be selected */ -class UFC_EmailList implements UserFilterCondition +class UFC_Email implements UserFilterCondition { private $emails; - public function __construct($emails) + public function __construct() { - $this->emails = $emails; + $this->emails = pl_flatten(func_get_args()); } public function buildCondition(PlFilter &$uf) { - $email = null; - $virtual = null; - $alias = null; + $foreign = array(); + $virtual = array(); + $aliases = array(); $cond = array(); if (count($this->emails) == 0) { @@ -611,23 +646,27 @@ class UFC_EmailList implements UserFilterCondition 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); + $foreign[] = $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); + $virtual[] = $entry; } else { - if (is_null($alias)) { - $alias = $uf->addAliasFilter(); - } @list($user, $domain) = explode('@', $entry); - $cond[] = XDB::format('al' . $alias . '.alias = {?}', $user); + $aliases[] = $user; } } + + if (count($foreign) > 0) { + $sub = $uf->addEmailRedirectFilter($foreign); + $cond[] = XDB::format('e' . $sub . '.email IS NOT NULL OR a.email IN {?}', $foreign); + } + if (count($virtual) > 0) { + $sub = $uf->addVirtualEmailFilter($virtual); + $cond[] = 'vr' . $sub . '.redirect IS NOT NULL'; + } + if (count($aliases) > 0) { + $sub = $uf->addAliasFilter($aliases); + $cond[] = 'al' . $sub . '.alias IS NOT NULL'; + } return '(' . implode(') OR (', $cond) . ')'; } } @@ -680,9 +719,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) { @@ -690,7 +730,7 @@ abstract class UFC_Address implements UserFilterCondition } } if (count($types)) { - $conds[] = $sub . '.type IN ' . XDB::formatArray($types); + $conds[] = XDB::format($sub . '.type IN {?}', $types); } if ($this->flags != self::FLAG_ANY) { @@ -739,7 +779,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); } @@ -796,7 +836,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: @@ -817,7 +857,7 @@ class UFC_AddressField extends UFC_Address default: Platal::page()->killError('Invalid address field type: ' . $this->fieldtype); } - $conds[] = $sub . '.' . $field . ' IN ' . XDB::formatArray($this->val); + $conds[] = XDB::format($sub . '.' . $field . ' IN {?}', $this->val); return implode(' AND ', $conds); } @@ -852,6 +892,7 @@ class UFC_Corps implements UserFilterCondition */ $sub = $uf->addCorpsFilter($this->type); $cond = $sub . '.abbreviation = ' . $corps; + $cond .= ' AND ' . $uf->getVisibilityCondition($sub . '.corps_pub'); return $cond; } } @@ -872,10 +913,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; } } @@ -912,7 +957,9 @@ class UFC_Job_Company implements UserFilterCondition public function buildCondition(PlFilter &$uf) { $sub = $uf->addJobCompanyFilter(); - $cond = $sub . '.' . $this->type . ' = ' . XDB::format('{?}', $this->value); + $cond = $sub . '.' . $this->type . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->value); + $jsub = $uf->addJobFilter(); + $cond .= ' AND ' . $uf->getVisibilityCondition($jsub . '.pub'); return $cond; } } @@ -948,7 +995,41 @@ class UFC_Job_Sectorization implements UserFilterCondition public function buildCondition(PlFilter &$uf) { $sub = $uf->addJobSectorizationFilter($this->type); - return $sub . '.id = ' . XDB::format('{?}', $this->val); + $cond = $sub . '.id = ' . XDB::format('{?}', $this->val); + $jsub = $uf->addJobFilter(); + $cond .= ' AND ' . $uf->getVisibilityCondition($jsub . '.pub'); + return $cond; + } +} +// }}} + +// {{{ 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_Terms implements UserFilterCondition +{ + private $val; + + public function __construct($val) + { + if (!is_array($val)) { + $val = array($val); + } + $this->val = $val; + } + + public function buildCondition(PlFilter &$uf) + { + $sub = $uf->addJobTermsFilter(count($this->val)); + $conditions = array(); + foreach ($this->val as $i => $jtid) { + $conditions[] = $sub[$i] . ' = ' . XDB::escape($jtid); + } + $jsub = $uf->addJobFilter(); + $conditions[] = $uf->getVisibilityCondition($jsub . '.pub'); + return implode(' AND ', $conditions); } } // }}} @@ -973,11 +1054,22 @@ 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 PlFilter::COND_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); } @@ -1020,9 +1112,10 @@ 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 . '.network_type = ' . XDB::format('{?}', $this->type); + $conds[] = $sub . '.nwid = ' . XDB::format('{?}', $this->type); } return implode(' AND ', $conds); } @@ -1053,16 +1146,20 @@ class UFC_Phone implements UserFilterCondition public function __construct($number, $num_type = self::NUM_ANY, $phone_type = self::PHONE_ANY) { - require_once('profil.func.inc.php'); - $this->number = $number; + $phone = new Phone(array('display' => $number)); + $phone->format(); + $this->number = $phone->search(); $this->num_type = $num_type; - $this->phone_type = format_phone_number($phone_type); + $this->phone_type = $phone_type; } public function buildCondition(PlFilter &$uf) { $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); @@ -1094,7 +1191,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); @@ -1104,6 +1206,31 @@ class UFC_Medal implements UserFilterCondition } // }}} +// {{{ class UFC_Photo +/** Filters profiles with photo + */ +class UFC_Photo implements UserFilterCondition +{ + public function buildCondition(PlFilter &$uf) + { + $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 @@ -1133,15 +1260,36 @@ class UFC_Mentor_Country implements UserFilterCondition { private $country; - public function __construct($country) + public function __construct() { - $this->country = $country; + $this->country = pl_flatten(func_get_args()); } public function buildCondition(PlFilter &$uf) { $sub = $uf->addMentorFilter(UserFilter::MENTOR_COUNTRY); - return $sub . '.country = ' . XDB::format('{?}', $this->country); + return $sub . '.country IN ' . XDB::format('{?}', $this->country); + } +} +// }}} + +// {{{ 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_Terms implements UserFilterCondition +{ + private $val; + + public function __construct($val) + { + $this->val = $val; + } + + public function buildCondition(PlFilter &$uf) + { + $sub = $uf->addMentorFilter(UserFilter::MENTOR_TERM); + return $sub . '.jtid_1 = ' . XDB::escape($this->val); } } // }}} @@ -1211,14 +1359,14 @@ class UFC_WatchRegistration extends UFC_UserRelated { public function buildCondition(PlFilter &$uf) { - if (!$this->user->watch('registration')) { + if (!$this->user->watchType('registration')) { return PlFilterCondition::COND_FALSE; } $uids = $this->user->watchUsers(); if (count($uids) == 0) { return PlFilterCondition::COND_FALSE; } else { - return '$UID IN ' . XDB::formatArray($uids); + return XDB::format('$UID IN {?}', $uids); } } } @@ -1246,7 +1394,7 @@ class UFC_WatchPromo extends UFC_UserRelated } else { $sube = $uf->addEducationFilter(true, $this->grade); $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade); - return $field . ' IN ' . XDB::formatArray($promos); + return XDB::format($field . ' IN {?}', $promos); } } } @@ -1267,33 +1415,37 @@ class UFC_WatchContact extends UFC_Contact } // }}} - -/****************** - * ORDERS - ******************/ - -// {{{ class UserFilterOrder -/** Base class for ordering results of a query. - * Parameters for the ordering must be given to the constructor ($desc for a - * descending order). - * The getSortTokens function is used to get actual ordering part of the query. +// {{{ class UFC_MarketingHash +/** Filters users using the hash generated + * to send marketing emails to him. */ -abstract class UserFilterOrder extends PlFilterOrder +class UFC_MarketingHash implements UserFilterCondition { - /** This function must return the tokens to use for ordering - * @param &$uf The UserFilter whose results must be ordered - * @return The name of the field to use for ordering results - */ -// abstract protected function getSortTokens(UserFilter &$uf); + private $hash; + + public function __construct($hash) + { + $this->hash = $hash; + } + + public function buildCondition(PlFilter &$uf) + { + $table = $uf->addMarketingHash(); + return XDB::format('rm.hash = {?}', $this->hash); + } } // }}} +/****************** + * ORDERS + ******************/ + // {{{ class UFO_Promo /** Orders users by promotion * @param $grade Formation whose promotion users should be sorted by (restricts results to users of that formation) * @param $desc Whether sort is descending */ -class UFO_Promo extends UserFilterOrder +class UFO_Promo extends PlFilterGroupableOrder { private $grade; @@ -1323,7 +1475,7 @@ class UFO_Promo extends UserFilterOrder * @param $particle Set to true if particles should be included in the sorting order * @param $desc If sort order should be descending */ -class UFO_Name extends UserFilterOrder +class UFO_Name extends PlFilterOrder { private $type; private $variant; @@ -1341,7 +1493,13 @@ class UFO_Name extends UserFilterOrder { if (Profile::isDisplayName($this->type)) { $sub = $uf->addDisplayFilter(); - return 'pd' . $sub . '.' . $this->type; + $token = 'pd' . $sub . '.' . $this->type; + if ($uf->accountsRequired()) { + $account_token = Profile::getAccountEquivalentName($this->type); + return 'IFNULL(' . $token . ', a.' . $account_token . ')'; + } else { + return $token; + } } else { $sub = $uf->addNameFilter($this->type, $this->variant); if ($this->particle) { @@ -1355,12 +1513,22 @@ class UFO_Name extends UserFilterOrder // }}} // {{{ class UFO_Score -class UFO_Score extends UserFilterOrder +class UFO_Score extends PlFilterOrder { protected function getSortTokens(PlFilter &$uf) { - $sub = $uf->addNameTokensFilter(); - return 'SUM(' . $sub . '.score)'; + $toks = $uf->getNameTokens(); + $scores = array(); + + // If there weren't any sort tokens, we shouldn't sort by score, sort by NULL instead + if (count($toks) == 0) { + return 'NULL'; + } + + foreach ($toks as $sub => $token) { + $scores[] = XDB::format('SUM(' . $sub . '.score + IF (' . $sub . '.token = {?}, 5, 0) )', $token); + } + return implode(' + ', $scores); } } // }}} @@ -1368,10 +1536,11 @@ class UFO_Score extends UserFilterOrder // {{{ class UFO_Registration /** Sorts users based on registration date */ -class UFO_Registration extends UserFilterOrder +class UFO_Registration extends PlFilterOrder { protected function getSortTokens(PlFilter &$uf) { + $uf->requireAccounts(); return 'a.registration_date'; } } @@ -1380,10 +1549,11 @@ class UFO_Registration extends UserFilterOrder // {{{ class UFO_Birthday /** Sorts users based on next birthday date */ -class UFO_Birthday extends UserFilterOrder +class UFO_Birthday extends PlFilterOrder { protected function getSortTokens(PlFilter &$uf) { + $uf->requireProfiles(); return 'p.next_birthday'; } } @@ -1392,10 +1562,11 @@ class UFO_Birthday extends UserFilterOrder // {{{ class UFO_ProfileUpdate /** Sorts users based on last profile update */ -class UFO_ProfileUpdate extends UserFilterOrder +class UFO_ProfileUpdate extends PlFilterOrder { protected function getSortTokens(PlFilter &$uf) { + $uf->requireProfiles(); return 'p.last_change'; } } @@ -1404,15 +1575,68 @@ class UFO_ProfileUpdate extends UserFilterOrder // {{{ class UFO_Death /** Sorts users based on death date */ -class UFO_Death extends UserFilterOrder +class UFO_Death extends PlFilterOrder { protected function getSortTokens(PlFilter &$uf) { + $uf->requireProfiles(); return 'p.deathdate'; } } // }}} +// {{{ class UFO_Uid +/** Sorts users based on their uid + */ +class UFO_Uid extends PlFilterOrder +{ + protected function getSortTokens(PlFilter &$uf) + { + $uf->requireAccounts(); + return '$UID'; + } +} +// }}} + +// {{{ class UFO_Hruid +/** Sorts users based on their hruid + */ +class UFO_Hruid extends PlFilterOrder +{ + protected function getSortTokens(PlFilter &$uf) + { + $uf->requireAccounts(); + return 'a.hruid'; + } +} +// }}} + +// {{{ class UFO_Pid +/** Sorts users based on their pid + */ +class UFO_Pid extends PlFilterOrder +{ + protected function getSortTokens(PlFilter &$uf) + { + $uf->requireProfiles(); + return '$PID'; + } +} +// }}} + +// {{{ class UFO_Hrpid +/** Sorts users based on their hrpid + */ +class UFO_Hrpid extends PlFilterOrder +{ + protected function getSortTokens(PlFilter &$uf) + { + $uf->requireProfiles(); + return 'p.hrpid'; + } +} +// }}} + /*********************************** ********************************* @@ -1452,7 +1676,7 @@ class UFO_Death extends UserFilterOrder * 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 + * 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: @@ -1472,10 +1696,15 @@ class UserFilter extends PlFilter private $root; private $sort = array(); + private $grouper = null; private $query = null; private $orderby = null; - private $lastcount = null; + // Store the current 'search' visibility. + private $profile_visibility = null; + + private $lastusercount = null; + private $lastprofilecount = null; public function __construct($cond = null, $sort = null) { @@ -1494,7 +1723,7 @@ class UserFilter extends PlFilter } } if (!is_null($sort)) { - if ($sort instanceof UserFilterOrder) { + if ($sort instanceof PlFilterOrder) { $this->addSort($sort); } else if (is_array($sort)) { foreach ($sort as $s) { @@ -1502,10 +1731,41 @@ 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() { + // 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) { @@ -1516,13 +1776,12 @@ class UserFilter extends PlFilter } else { $this->orderby = 'ORDER BY ' . implode(', ', $orders); } + $this->orderby = str_replace(array_keys($this->joinMetas), + $this->joinMetas, + $this->orderby); } if (is_null($this->query)) { - $where = $this->root->buildCondition($this); - if ($this->with_forced_sn) { - $this->requireProfiles(); - $from = 'search_name AS sn'; - } else if ($this->with_accounts) { + if ($this->with_accounts) { $from = 'accounts AS a'; } else { $this->requireProfiles(); @@ -1535,21 +1794,57 @@ class UserFilter extends PlFilter } } + public function hasGroups() + { + return $this->grouper != null; + } + + public function getGroups() + { + return $this->getUIDGroups(); + } + + public function getUIDGroups() + { + $this->requireAccounts(); + $this->buildQuery(); + $token = $this->grouper->getGroupToken($this); + + $groups = XDB::fetchAllRow('SELECT ' . $token . ', COUNT(a.uid) + ' . $this->query . ' + GROUP BY ' . $token, + 0); + return $groups; + } + + public function getPIDGroups() + { + $this->requireProfiles(); + $this->buildQuery(); + $token = $this->grouper->getGroupToken($this); + + $groups = XDB::fetchAllRow('SELECT ' . $token . ', COUNT(p.pid) + ' . $this->query . ' + GROUP BY ' . $token, + 0); + return $groups; + } + private function getUIDList($uids = null, PlLimit &$limit) { $this->requireAccounts(); $this->buildQuery(); $lim = $limit->getSql(); $cond = ''; - if (!is_null($uids)) { - $cond = ' AND a.uid IN ' . XDB::formatArray($uids); + 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); - $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()'); + $this->lastusercount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()'); return $fetched; } @@ -1560,14 +1855,14 @@ class UserFilter extends PlFilter $lim = $limit->getSql(); $cond = ''; if (!is_null($pids)) { - $cond = ' AND p.pid IN ' . XDB::formatArray($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); - $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()'); + $this->lastprofilecount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()'); return $fetched; } @@ -1666,30 +1961,70 @@ class UserFilter extends PlFilter return $this->getUIDList(null, $limit); } + 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 getPIDs($limit = null) { $limit = self::defaultLimit($limit); return $this->getPIDList(null, $limit); } + public function getPID($pos = 0) + { + $pids =$this->getPIDList(null, new PlLimit(1, $pos)); + if (count($pids) == 0) { + return null; + } else { + return $pids[0]; + } + } + public function getUsers($limit = null) { return User::getBulkUsersWithUIDs($this->getUIDs($limit)); } + public function getUser($pos = 0) + { + $uid = $this->getUID($pos); + if ($uid == null) { + return null; + } else { + return User::getWithUID($uid); + } + } + public function iterUsers($limit = null) { return User::iterOverUIDs($this->getUIDs($limit)); } - public function getProfiles($limit = null) + public function getProfiles($limit = null, $fields = 0x0000, $visibility = null) + { + return Profile::getBulkProfilesWithPIDs($this->getPIDs($limit), $fields, $visibility); + } + + public function getProfile($pos = 0, $fields = 0x0000, $visibility = null) { - return Profile::getBulkProfilesWithPIDs($this->getPIDs($limit)); + $pid = $this->getPID($pos); + if ($pid == null) { + return null; + } else { + return Profile::get($pid, $fields, $visibility); + } } - public function iterProfiles($limit = null) + public function iterProfiles($limit = null, $fields = 0x0000, $visibility = null) { - return Profile::iterOverPIDs($this->getPIDs($limit)); + return Profile::iterOverPIDs($this->getPIDs($limit), true, $fields, $visibility); } public function get($limit = null) @@ -1697,19 +2032,33 @@ class UserFilter extends PlFilter return $this->getUsers($limit); } + public function getTotalCount() { - if (is_null($this->lastcount)) { + return $this->getTotalUserCount(); + } + + public function getTotalUserCount() + { + if (is_null($this->lastusercount)) { + $this->requireAccounts(); $this->buildQuery(); - if ($this->with_accounts) { - $field = 'a.uid'; - } else { - $field = 'p.pid'; - } - return (int)XDB::fetchOneCell('SELECT COUNT(DISTINCT ' . $field . ') + return (int)XDB::fetchOneCell('SELECT COUNT(DISTINCT a.uid) ' . $this->query); } else { - return $this->lastcount; + return $this->lastusercount; + } + } + + public function getTotalProfileCount() + { + if (is_null($this->lastprofilecount)) { + $this->requireProfiles(); + $this->buildQuery(); + return (int)XDB::fetchOneCell('SELECT COUNT(DISTINCT p.pid) + ' . $this->query); + } else { + return $this->lastprofilecount; } } @@ -1721,6 +2070,10 @@ class UserFilter extends PlFilter public function addSort(PlFilterOrder &$sort) { + if (count($this->sort) == 0 && $sort instanceof PlFilterGroupableOrder) + { + $this->grouper = $sort; + } $this->sort[] = $sort; $this->orderby = null; } @@ -1730,12 +2083,12 @@ class UserFilter extends PlFilter 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 PFC_And($min, $max)); } @@ -1752,7 +2105,14 @@ class UserFilter extends PlFilter 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); + } } @@ -1780,36 +2140,32 @@ class UserFilter extends PlFilter */ private $with_profiles = false; private $with_accounts = false; - private $with_forced_sn = false; public function requireAccounts() { $this->with_accounts = true; } + public function accountsRequired() + { + return $this->with_accounts; + } + public function requireProfiles() { $this->with_profiles = true; } - /** Forces the "FROM" to use search_name instead of accounts or profiles */ - public function forceSearchName() + public function profilesRequired() { - $this->with_forced_sn = true; + return $this->with_profiles; } protected function accountJoins() { $joins = array(); - /** Quick search is much more efficient with sn first and PID second */ - if ($this->with_forced_sn) { - $joins['p'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profiles', '$PID = sn.uid'); - if ($this->with_accounts) { - $joins['ap'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'account_profiles', '$ME.pid = $PID'); - $joins['a'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'accounts', '$UID = ap.uid'); - } - } else if ($this->with_profiles && $this->with_accounts) { - $joins['ap'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'account_profiles', '$ME.uid = $UID AND FIND_IN_SET(\'owner\', ap.perms)'); - $joins['p'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profiles', '$PID = ap.pid'); + 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; } @@ -1828,7 +2184,7 @@ class UserFilter extends PlFilter protected function displayJoins() { if ($this->pd) { - return array('pd' => new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_display', '$ME.pid = $PID')); + return array('pd' => PlSqlJoin::left('profile_display', '$ME.pid = $PID')); } else { return array(); } @@ -1848,7 +2204,7 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_logger) { - $joins['ls'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'log_sessions', '$ME.uid = $UID'); + $joins['ls'] = PlSqlJoin::left('log_sessions', '$ME.uid = $UID'); } return $joins; } @@ -1858,7 +2214,7 @@ class UserFilter extends PlFilter static public function assertName($name) { - if (!Profile::getNameTypeId($name)) { + if (!DirEnum::getID(DirEnum::NAMETYPES, $name)) { Platal::page()->kill('Invalid name type: ' . $name); } } @@ -1878,7 +2234,7 @@ class UserFilter extends PlFilter if (!is_null($variant) && $variant == 'other') { $sub .= $this->option++; } - $this->pn[$sub] = Profile::getNameTypeId($ft); + $this->pn[$sub] = DirEnum::getID(DirEnum::NAMETYPES, $ft); return $sub; } @@ -1886,33 +2242,38 @@ class UserFilter extends PlFilter { $joins = array(); foreach ($this->pn as $sub => $type) { - $joins['pn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_name', '$ME.pid = $PID AND $ME.typeid = ' . $type); + $joins['pn' . $sub] = PlSqlJoin::left('profile_name', '$ME.pid = $PID AND $ME.typeid = {?}', $type); } return $joins; } /** NAMETOKENS */ - private $with_sn = false; - // Set $doingQuickSearch to true if you wish to optimize the query - public function addNameTokensFilter($doingQuickSearch = false) + private $name_tokens = array(); + private $nb_tokens = 0; + + public function addNameTokensFilter($token) { $this->requireProfiles(); - $this->with_forced_sn = ($this->with_forced_sn || $doingQuickSearch); - $this->with_sn = true; - return 'sn'; + $sub = 'sn' . (1 + $this->nb_tokens); + $this->nb_tokens++; + $this->name_tokens[$sub] = $token; + return $sub; } protected function nameTokensJoins() { /* We don't return joins, since with_sn forces the SELECT to run on search_name first */ - if ($this->with_sn && !$this->with_forced_sn) { - return array( - 'sn' => new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'search_name', '$ME.uid = $PID') - ); - } else { - return array(); + $joins = array(); + 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 @@ -1929,7 +2290,7 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_nat) { - $joins['ngc'] = new PlSqlJoin(PlSqlJoin::MODE_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'); + $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; } @@ -1941,13 +2302,13 @@ class UserFilter extends PlFilter const GRADE_MST = '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"); } } @@ -1980,17 +2341,16 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_pee) { - $joins['pee'] = new PlSqlJoin(PlSqlJoin::MODE_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] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.eduid = pee.id AND $ME.uid = $PID'); - $joins['pede' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_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.abbreviation LIKE {?}', $grade); } else { - $joins['pe' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_education', '$ME.uid = $PID'); - $joins['pee' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'profile_education_enum', '$ME.id = pe' . $sub . '.eduid'); - $joins['pede' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_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; @@ -2004,7 +2364,7 @@ class UserFilter extends PlFilter { $this->requireAccounts(); if (!is_null($group)) { - if (ctype_digit($group)) { + if (is_int($group) || ctype_digit($group)) { $index = $sub = $group; } else { $index = $group; @@ -2024,13 +2384,13 @@ class UserFilter extends PlFilter $joins = array(); foreach ($this->gpm as $sub => $key) { if (is_null($key)) { - $joins['gpa' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'groups'); - $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id'); - } else if (ctype_digit($key)) { - $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'group_members', '$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] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'groups', XDB::format('$ME.diminutif = {?}', $key)); - $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'group_members', '$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'); } } return $joins; @@ -2057,10 +2417,10 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_bi) { - $joins['bi'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'binets_ins', '$ME.user_id = $PID'); + $joins['bi'] = PlSqlJoin::left('profile_binets', '$ME.pid = $PID'); } if ($this->with_bd) { - $joins['bd'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'binets_def', '$ME.id = bi.binet_id'); + $joins['bd'] = PlSqlJoin::left('profile_binet_enum', '$ME.id = bi.binet_id'); } return $joins; } @@ -2096,33 +2456,45 @@ class UserFilter extends PlFilter $joins = array(); foreach ($this->e as $sub=>$key) { if (is_null($key)) { - $joins['e' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'emails', '$ME.uid = $UID AND $ME.flags != \'filter\''); + $joins['e' . $sub] = PlSqlJoin::left('emails', '$ME.uid = $UID AND $ME.flags != \'filter\''); } else { - $joins['e' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'emails', XDB::format('$ME.uid = $UID AND $ME.flags != \'filter\' AND $ME.email = {?}', $key)); + if (!is_array($key)) { + $key = array($key); + } + $joins['e' . $sub] = PlSqlJoin::left('emails', '$ME.uid = $UID AND $ME.flags != \'filter\' + AND $ME.email IN {?}', $key); } } foreach ($this->al as $sub=>$key) { if (is_null($key)) { - $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\')'); + $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\')'); } else if ($key == self::ALIAS_BEST) { - $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND FIND_IN_SET(\'bestalias\', $ME.flags)'); + $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND FIND_IN_SET(\'bestalias\', $ME.flags)'); } else if ($key == self::ALIAS_FORLIFE) { - $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type = \'a_vie\''); + $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type = \'a_vie\''); } else { - $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', XDB::format('$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND $ME.alias = {?}', $key)); + if (!is_array($key)) { + $key = array($key); + } + $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\') + AND $ME.alias IN {?}', $key); } } foreach ($this->ve as $sub=>$key) { if (is_null($key)) { - $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', '$ME.type = \'user\''); + $joins['v' . $sub] = PlSqlJoin::left('virtual', '$ME.type = \'user\''); } else { - $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', XDB::format('$ME.type = \'user\' AND $ME.alias = {?}', $key)); + if (!is_array($key)) { + $key = array($key); + } + $joins['v' . $sub] = PlSqlJoin::left('virtual', '$ME.type = \'user\' AND $ME.alias IN {?}', $key); } - $joins['vr' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_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)); + $joins['vr' . $sub] = PlSqlJoin::left('virtual_redirect', + '$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; } @@ -2160,13 +2532,13 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_pa) { - $joins['pa'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_addresses', '$ME.pid = $PID'); + $joins['pa'] = PlSqlJoin::left('profile_addresses', '$ME.pid = $PID'); } if ($this->with_pac) { - $joins['gc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'geoloc_countries', '$ME.iso_3166_1_a2 = pa.countryID'); + $joins['gc'] = PlSqlJoin::left('geoloc_countries', '$ME.iso_3166_1_a2 = pa.countryID'); } if ($this->with_pal) { - $joins['gl'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'geoloc_localities', '$ME.id = pa.localityID'); + $joins['gl'] = PlSqlJoin::left('geoloc_localities', '$ME.id = pa.localityID'); } return $joins; } @@ -2203,13 +2575,13 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->pc) { - $joins['pc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps', '$ME.uid = $UID'); + $joins['pc'] = PlSqlJoin::left('profile_corps', '$ME.pid = $PID'); } if ($this->pcr) { - $joins['pcr'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_rank_enum', '$ME.id = pc.rankid'); + $joins['pcr'] = PlSqlJoin::left('profile_corps_rank_enum', '$ME.id = pc.rankid'); } foreach($this->pce as $sub => $field) { - $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_enum', '$ME.id = pc.' . $field); + $joins[$sub] = PlSqlJoin::left('profile_corps_enum', '$ME.id = pc.' . $field); } return $joins; } @@ -2234,6 +2606,7 @@ class UserFilter extends PlFilter * pjsse => profile_job_subsector_enum * pjssse => profile_job_subsubsector_enum * pja => profile_job_alternates + * pjt => profile_job_terms */ private $with_pj = false; private $with_pje = false; @@ -2241,6 +2614,7 @@ class UserFilter extends PlFilter private $with_pjsse = false; private $with_pjssse = false; private $with_pja = false; + private $with_pjt = 0; public function addJobFilter() { @@ -2274,26 +2648,48 @@ class UserFilter extends PlFilter } } + /** + * 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). + * Code using this function should used returned field as is (contains table and field name). + */ + public function addJobTermsFilter($nb = 1) + { + $this->with_pjt = $nb; + $jobtermstable = array(); + for ($i = 1; $i <= $nb; ++$i) { + $jobtermstable[] = 'pjtr_'.$i.'.jtid_1'; + } + return $jobtermstable; + } + protected function jobJoins() { $joins = array(); if ($this->with_pj) { - $joins['pj'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job', '$ME.uid = $UID'); + $joins['pj'] = PlSqlJoin::left('profile_job', '$ME.pid = $PID'); } if ($this->with_pje) { - $joins['pje'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_enum', '$ME.id = pj.jobid'); + $joins['pje'] = PlSqlJoin::left('profile_job_enum', '$ME.id = pj.jobid'); } if ($this->with_pjse) { - $joins['pjse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_sector_enum', '$ME.id = pj.sectorid'); + $joins['pjse'] = PlSqlJoin::left('profile_job_sector_enum', '$ME.id = pj.sectorid'); } if ($this->with_pjsse) { - $joins['pjsse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_subsector_enum', '$ME.id = pj.subsectorid'); + $joins['pjsse'] = PlSqlJoin::left('profile_job_subsector_enum', '$ME.id = pj.subsectorid'); } if ($this->with_pjssse) { - $joins['pjssse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_subsubsector_enum', '$ME.id = pj.subsubsectorid'); + $joins['pjssse'] = PlSqlJoin::left('profile_job_subsubsector_enum', '$ME.id = pj.subsubsectorid'); } if ($this->with_pja) { - $joins['pja'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_alternates', '$ME.subsubsectorid = pj.subsubsectorid'); + $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; } @@ -2313,7 +2709,7 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_pnw) { - $joins['pnw'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_networking', '$ME.uid = $UID'); + $joins['pnw'] = PlSqlJoin::left('profile_networking', '$ME.pid = $PID'); } return $joins; } @@ -2334,7 +2730,7 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_ptel) { - $joins['ptel'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_phones', '$ME.uid = $UID'); + $joins['ptel'] = PlSqlJoin::left('profile_phones', '$ME.pid = $PID'); } return $joins; } @@ -2354,7 +2750,7 @@ class UserFilter extends PlFilter { $joins = array(); if ($this->with_pmed) { - $joins['pmed'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_medals_sub', '$ME.uid = $UID'); + $joins['pmed'] = PlSqlJoin::left('profile_medals', '$ME.pid = $PID'); } return $joins; } @@ -2363,14 +2759,20 @@ 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_SECTOR = 4; + const MENTOR_TERM = 5; 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'; @@ -2380,6 +2782,10 @@ class UserFilter extends PlFilter 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."); } @@ -2389,7 +2795,10 @@ class UserFilter extends PlFilter { $joins = array(); foreach ($this->pms as $sub => $tab) { - $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, $tab, '$ME.uid = $UID'); + $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; } @@ -2399,7 +2808,7 @@ class UserFilter extends PlFilter private $cts = array(); public function addContactFilter($uid = null) { - $this->requireAccounts(); + $this->requireProfiles(); return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid); } @@ -2408,9 +2817,9 @@ class UserFilter extends PlFilter $joins = array(); foreach ($this->cts as $sub=>$key) { if (is_null($key)) { - $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', '$ME.contact = $UID'); + $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.contact = $PID'); } else { - $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_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; @@ -2445,34 +2854,73 @@ class UserFilter extends PlFilter $joins = array(); foreach ($this->w as $sub=>$key) { if (is_null($key)) { - $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch'); + $joins['w' . $sub] = PlSqlJoin::left('watch'); } else { - $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_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] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', '$ME.ni_id = $UID'); + $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID'); } else { - $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_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] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', '$ME.ni_id = $UID'); + $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID'); } else { - $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_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] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_promo'); + $joins['wp' . $sub] = PlSqlJoin::left('watch_promo'); } else { - $joins['wp' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_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(); + } + } } // }}} @@ -2483,6 +2931,21 @@ class ProfileFilter extends UserFilter { return $this->getProfiles($limit); } + + 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(); + } } // }}}