X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=classes%2Fuserfilter.php;h=9dca69089565321526a345732fc2ae834ab560b6;hb=b2e8fc54686bcd9815f0a6c51439eb97ba88d37c;hp=18ae09a42a162bc63d06966554d1df55665d0370;hpb=38c6fe960bcd94f129da664c22609e24c64516eb;p=platal.git diff --git a/classes/userfilter.php b/classes/userfilter.php index 18ae09a..9dca690 100644 --- a/classes/userfilter.php +++ b/classes/userfilter.php @@ -24,143 +24,42 @@ * CONDITIONS ******************/ +// {{{ interface UserFilterCondition +/** This interface describe objects which filter users based + * on various parameters. + * The parameters of the filter must be given to the constructor. + * The buildCondition function is called by UserFilter when + * actually building the query. That function must call + * $uf->addWheteverFilter so that the UserFilter makes + * adequate joins. It must return the 'WHERE' condition to use + * with the filter. + */ interface UserFilterCondition { - const COND_TRUE = 'TRUE'; - const COND_FALSE = 'FALSE'; - /** Check that the given user matches the rule. */ public function buildCondition(UserFilter &$uf); } +// }}} -abstract class UFC_OneChild implements UserFilterCondition -{ - protected $child; - - public function __construct($child = null) - { - if (!is_null($child) && ($child instanceof UserFilterCondition)) { - $this->setChild($child); - } - } - - public function setChild(UserFilterCondition &$cond) - { - $this->child =& $cond; - } -} - -abstract class UFC_NChildren implements UserFilterCondition -{ - protected $children = array(); - - public function __construct() - { - $children = func_get_args(); - foreach ($children as &$child) { - if (!is_null($child) && ($child instanceof UserFilterCondition)) { - $this->addChild($child); - } - } - } - - 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; - } - } - return $this->catConds($conds, 'AND', $true); - } - } -} - -class UFC_Or extends UFC_NChildren +// {{{ class UFC_Profile +/** Filters users who have a profile + */ +class UFC_Profile implements UserFilterCondition { 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; - } - } - return $this->catConds($conds, 'OR', $true); - } + return '$PID IS NOT NULL'; } } - +// }}} + +// {{{ class UFC_Promo +/** Filters users based on promotion + * @param $comparison Comparison operator (>, =, ...) + * @param $grade Formation on which to restrict, UserFilter::DISPLAY for "any formation" + * @param $promo Promotion on which the filter is based + */ class UFC_Promo implements UserFilterCondition { @@ -182,7 +81,7 @@ class UFC_Promo implements UserFilterCondition { if ($this->grade == UserFilter::DISPLAY) { $sub = $uf->addDisplayFilter(); - return XDB::format('pd' . $sub . '.promo = {?}', $this->promo); + return XDB::format('pd' . $sub . '.promo ' . $this->comparison . ' {?}', $this->promo); } else { $sub = $uf->addEducationFilter(true, $this->grade); $field = 'pe' . $sub . '.' . UserFilter::promoYear($this->grade); @@ -190,7 +89,14 @@ class UFC_Promo implements UserFilterCondition } } } - +// }}} + +// {{{ class UFC_Name +/** Filters users based on name + * @param $type Type of name field on which filtering is done (firstname, lastname...) + * @param $text Text on which to filter + * @param $mode Flag indicating search type (prefix, suffix, with particule...) + */ class UFC_Name implements UserFilterCondition { const PREFIX = 1; @@ -243,7 +149,13 @@ class UFC_Name implements UserFilterCondition return implode(' OR ', $conds); } } +// }}} +// {{{ class UFC_Dead +/** Filters users based on death date + * @param $comparison Comparison operator + * @param $date Date to which death date should be compared + */ class UFC_Dead implements UserFilterCondition { private $comparison; @@ -264,7 +176,14 @@ class UFC_Dead implements UserFilterCondition return $str; } } - +// }}} + +// {{{ class UFC_Registered +/** Filters users based on registration state + * @param $active Whether we want to use only "active" users (i.e with a valid redirection) + * @param $comparison Comparison operator + * @param $date Date to which users registration date should be compared + */ class UFC_Registered implements UserFilterCondition { private $active; @@ -273,7 +192,7 @@ class UFC_Registered implements UserFilterCondition public function __construct($active = false, $comparison = null, $date = null) { - $this->only_active = $active; + $this->active = $active; $this->comparison = $comparison; $this->date = $date; } @@ -291,81 +210,790 @@ class UFC_Registered implements UserFilterCondition return $date; } } +// }}} + +// {{{ class UFC_ProfileUpdated +/** Filters users based on profile update date + * @param $comparison Comparison operator + * @param $date Date to which profile update date must be compared + */ +class UFC_ProfileUpdated implements UserFilterCondition +{ + private $comparison; + private $date; + + public function __construct($comparison = null, $date = null) + { + $this->comparison = $comparison; + $this->date = $date; + } + + public function buildCondition(UserFilter &$uf) + { + return 'p.last_change ' . $this->comparison . XDB::format(' {?}', date('Y-m-d H:i:s', $this->date)); + } +} +// }}} + +// {{{ class UFC_Birthday +/** Filters users based on next birthday date + * @param $comparison Comparison operator + * @param $date Date to which users next birthday date should be compared + */ +class UFC_Birthday implements UserFilterCondition +{ + private $comparison; + private $date; + + public function __construct($comparison = null, $date = null) + { + $this->comparison = $comparison; + $this->date = $date; + } + + public function buildCondition(UserFilter &$uf) + { + return 'p.next_birthday ' . $this->comparison . XDB::format(' {?}', date('Y-m-d', $this->date)); + } +} +// }}} +// {{{ class UFC_Sex +/** Filters users based on sex + * @parm $sex One of User::GENDER_MALE or User::GENDER_FEMALE, for selecting users + */ class UFC_Sex implements UserFilterCondition { private $sex; public function __construct($sex) { - $this->sex = $sex; + $this->sex = $sex; + } + + public function buildCondition(UserFilter &$uf) + { + if ($this->sex != User::GENDER_MALE && $this->sex != User::GENDER_FEMALE) { + return self::COND_FALSE; + } else { + return XDB::format('p.sex = {?}', $this->sex == User::GENDER_FEMALE ? 'female' : 'male'); + } + } +} +// }}} + +// {{{ class UFC_Group +/** Filters users based on group membership + * @param $group Group whose members we are selecting + * @param $anim Whether to restrict selection to animators of that group + */ +class UFC_Group implements UserFilterCondition +{ + private $group; + private $anim; + public function __construct($group, $anim = false) + { + $this->group = $group; + $this->anim = $anim; + } + + public function buildCondition(UserFilter &$uf) + { + $sub = $uf->addGroupFilter($this->group); + $where = 'gpm' . $sub . '.perms IS NOT NULL'; + if ($this->anim) { + $where .= ' AND gpm' . $sub . '.perms = \'admin\''; + } + return $where; + } +} +// }}} + +// {{{ 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(UserFilter &$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 + * @param $emails List of emails whose owner must be selected + */ +class UFC_EmailList implements UserFilterCondition +{ + private $emails; + public function __construct($emails) + { + $this->emails = $emails; + } + + public function buildCondition(UserFilter &$uf) + { + $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); + } else { + if (is_null($alias)) { + $alias = $uf->addAliasFilter(); + } + @list($user, $domain) = explode('@', $entry); + $cond[] = XDB::format('al' . $alias . '.alias = {?}', $user); + } + } + return '(' . implode(') OR (', $cond) . ')'; + } +} +// }}} + +// {{{ class UFC_Address +/** Filters users based on their address + * @param $text Text for filter in fulltext search + * @param $textSearchMode Mode for search (PREFIX, SUFFIX, ...) + * @param $type Filter on address type + * @param $flags Filter on address flags + * @param $countryId Filter on address countryId + * @param $administrativeAreaId Filter on address administrativeAreaId + * @param $subAdministrativeAreaId Filter on address subAdministrativeAreaId + * @param $localityId Filter on address localityId + * @param $postalCode Filter on address postalCode + */ +class UFC_Address implements UserFilterCondition +{ + /** Flags for text search + */ + const PREFIX = 0x0001; + const SUFFIX = 0x0002; + const CONTAINS = 0x0003; + + /** Valid address type ('hq' is reserved for company addresses) + */ + const TYPE_HOME = 'home'; + const TYPE_PRO = 'job'; + + /** Flags for addresses + */ + const FLAG_CURRENT = 0x0001; + const FLAG_TEMP = 0x0002; + const FLAG_SECOND = 0x0004; + const FLAG_MAIL = 0x0008; + const FLAG_CEDEX = 0x0010; + + // Binary OR of those flags + const FLAG_ANY = 0x001F; + + /** Text of these flags + */ + private static $flagtexts = array( + self::FLAG_CURRENT => 'current', + self::FLAG_TEMP => 'temporary', + self::FLAG_SECOND => 'secondary', + self::FLAG_MAIL => 'mail', + self::FLAG_CEDEX => 'cedex', + ); + + /** Data of the filter + */ + private $text; + private $type; + private $flags; + private $countryId; + private $administrativeAreaId; + private $subAdministrativeAreaId; + private $localityId; + private $postalCode; + + private $textSearchMode; + + public function __construct($text = null, $textSearchMode = self::CONTAINS, + $type = null, $flags = self::FLAG_ANY, $countryId = null, $administrativeAreaId = null, + $subAdministrativeAreaId = null, $localityId = null, $postalCode = null) + { + $this->text = $text; + $this->textSearchMode = $textSearchMode; + $this->type = $type; + $this->flags = $flags; + $this->countryId = $countryId; + $this->administrativeAreaId = $administrativeAreaId; + $this->subAdministrativeAreaId = $subAdministrativeAreaId; + $this->localityId = $localityId; + $this->postalCode = $postalCode; + } + + public function buildCondition(UserFilter &$uf) + { + $sub = $uf->addAddressFilter(); + $conds = array(); + if ($this->text != null) { + $left = $sub . '.text '; + $op = ' LIKE '; + if (($this->textSearchMode & 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); + } + $conds[] = $left . $op . $right; + } + + if ($this->type != null) { + $conds[] = $sub . '.type = ' . XDB::format('{?}', $this->type); + } + + if ($this->flags != self::FLAG_ANY) { + foreach(self::$flagtexts as $flag => $text) { + if ($flag & $this->flags) { + $conds[] = 'FIND_IN_SET(' . XDB::format('{?}', $text) . ', ' . $sub . '.flags)'; + } + } + } + + if ($this->countryId != null) { + $conds[] = $sub . '.countryId = ' . XDB::format('{?}', $this->countryId); + } + if ($this->administrativeAreaId != null) { + $conds[] = $sub . '.administrativeAreaId = ' . XDB::format('{?}', $this->administrativeAreaId); + } + if ($this->subAdministrativeAreaId != null) { + $conds[] = $sub . '.subAdministrativeAreaId = ' . XDB::format('{?}', $this->subAdministrativeAreaId); + } + if ($this->localityId != null) { + $conds[] = $sub . '.localityId = ' . XDB::format('{?}', $this->localityId); + } + if ($this->postalCode != null) { + $conds[] = $sub . '.postalCode = ' . XDB::format('{?}', $this->postalCode); + } + + return implode(' AND ', $conds); + } +} +// }}} + +// {{{ class UFC_Corps +/** Filters users based on the corps they belong to + * @param $corps Corps we are looking for (abbreviation) + * @param $type Whether we search for original or current corps + */ +class UFC_Corps implements UserFilterCondition +{ + const CURRENT = 1; + const ORIGIN = 2; + + private $corps; + private $type; + + public function __construct($corps, $type = self::CURRENT) + { + $this->corps = $corps; + $this->type = $type; + } + + public function buildCondition(UserFilter &$uf) + { + /** Tables shortcuts: + * pc for profile_corps, + * pceo for profile_corps_enum - orginal + * pcec for profile_corps_enum - current + */ + $sub = $uf->addCorpsFilter($this->type); + $cond = $sub . '.abbreviation = ' . $corps; + return $cond; + } +} +// }}} + +// {{{ class UFC_Corps_Rank +/** Filters users based on their rank in the corps + * @param $rank Rank we are looking for (abbreviation) + */ +class UFC_Corps_Rank implements UserFilterCondition +{ + private $rank; + public function __construct($rank) + { + $this->rank = $rank; + } + + public function buildCondition(UserFilter &$uf) + { + /** Tables shortcuts: + * pcr for profile_corps_rank + */ + $sub = $uf->addCorpsRankFilter(); + $cond = $sub . '.abbreviation = ' . $rank; + return $cond; + } +} +// }}} + +// {{{ class UFC_Job_Company +/** Filters users based on the company they belong to + * @param $type The field being searched (self::JOBID, self::JOBNAME or self::JOBACRONYM) + * @param $value The searched value + */ +class UFC_Job_Company implements UserFilterCondition +{ + const JOBID = 'id'; + const JOBNAME = 'name'; + const JOBACRONYM = 'acronym'; + + private $type; + private $value; + + public function __construct($type, $value) + { + $this->assertType($type); + $this->type = $type; + $this->value = $value; + } + + private function assertType($type) + { + if ($type != self::JOBID && $type != self::JOBNAME && $type != self::JOBACRONYM) { + Platal::page()->killError("Type de recherche non valide."); + } + } + + public function buildCondition(UserFilter &$uf) + { + $sub = $uf->addJobCompanyFilter(); + $cond = $sub . '.' . $this->type . ' = ' . XDB::format('{?}', $this->value); + return $cond; + } +} +// }}} + +// {{{ class UFC_Job_Sectorization +/** Filters users based on the ((sub)sub)sector they work in + * @param $sector The sector searched + * @param $subsector The subsector + * @param $subsubsector The subsubsector + */ +class UFC_Job_Sectorization implements UserFilterCondition +{ + + private $sector; + private $subsector; + private $subsubsector; + + public function __construct($sector = null, $subsector = null, $subsubsector = null) + { + $this->sector = $sector; + $this->subsector = $subsector; + $this->subsubsector = $subsubsector; + } + + public function buildCondition(UserFilter &$uf) + { + // No need to add the JobFilter, it will be done by addJobSectorizationFilter + $conds = array(); + if ($this->sector !== null) { + $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SECTOR); + $conds[] = $sub . '.id = ' . XDB::format('{?}', $this->sector); + } + if ($this->subsector !== null) { + $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSECTOR); + $conds[] = $sub . '.id = ' . XDB::format('{?}', $this->subsector); + } + if ($this->subsubsector !== null) { + $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSUBSECTOR); + $conds[] = $sub . '.id = ' . XDB::format('{?}', $this->subsubsector); + } + return implode(' AND ', $conds); + } +} +// }}} + +// {{{ 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) + */ +class UFC_Job_Description implements UserFilterCondition +{ + + /** Meta-filters + * Built with binary OR on UserFilter::JOB_* + */ + const ANY = 31; + const SECTORIZATION = 15; + + private $description; + private $fields; + + public function __construct($description) + { + $this->fields = $fields; + $this->description = $description; + } + + public function buildCondition(UserFilter &$uf) + { + $conds = array(); + if ($this->fields & UserFilter::JOB_USERDEFINED) { + $sub = $uf->addJobFilter(); + $conds[] = $sub . '.description LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + } + if ($this->fields & UserFilter::JOB_SECTOR) { + $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SECTOR); + $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + } + if ($this->fields & UserFilter::JOB_SUBSECTOR) { + $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSECTOR); + $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + } + if ($this->fields & UserFilter::JOB_SUBSUBSECTOR) { + $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSUBSECTOR); + $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_ALTERNATES); + $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + } + return implode(' OR ', $conds); + } +} +// }}} + +// {{{ class UFC_Networking +/** Filters users based on network identity (IRC, ...) + * @param $type Type of network (-1 for any) + * @param $value Value to search + */ +class UFC_Networking implements UserFilterCondition +{ + private $type; + private $value; + + public function __construct($type, $value) + { + $this->type = $type; + $this->value = $value; + } + + public function buildCondition(UserFilter &$uf) + { + $sub = $uf->addNetworkingFilter(); + $conds = array(); + $conds[] = $sub . '.address = ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->value); + if ($this->type != -1) { + $conds[] = $sub . '.network_type = ' . XDB::format('{?}', $this->type); + } + return implode(' AND ', $conds); + } +} +// }}} + +// {{{ class UFC_Phone +/** Filters users based on their phone number + * @param $num_type Type of number (pro/user/home) + * @param $phone_type Type of phone (fixed/mobile/fax) + * @param $number Phone number + */ +class UFC_Phone implements UserFilterCondition +{ + const NUM_PRO = 'pro'; + const NUM_USER = 'user'; + const NUM_HOME = 'address'; + const NUM_ANY = 'any'; + + const PHONE_FIXED = 'fixed'; + const PHONE_MOBILE = 'mobile'; + const PHONE_FAX = 'fax'; + const PHONE_ANY = 'any'; + + private $num_type; + private $phone_type; + private $number; + + public function __construct($number, $num_type = self::NUM_ANY, $phone_type = self::PHONE_ANY) + { + require_once('profil.inc.php'); + $this->number = $number; + $this->num_type = $num_type; + $this->phone_type = format_phone_number($phone_type); + } + + public function buildCondition(UserFilter &$uf) + { + $sub = $uf->addPhoneFilter(); + $conds = array(); + $conds[] = $sub . '.search_tel = ' . XDB::format('{?}', $this->number); + if ($this->num_type != self::NUM_ANY) { + $conds[] = $sub . '.link_type = ' . XDB::format('{?}', $this->num_type); + } + if ($this->phone_type != self::PHONE_ANY) { + $conds[] = $sub . '.tel_type = ' . XDB::format('{?}', $this->phone_type); + } + return implode(' AND ', $conds); + } +} +// }}} + +// {{{ class UFC_Medal +/** Filters users based on their medals + * @param $medal ID of the medal + * @param $grade Grade of the medal (null for 'any') + */ +class UFC_Medal implements UserFilterCondition +{ + private $medal; + private $grade; + + public function __construct($medal, $grade = null) + { + $this->medal = $medal; + $this->grade = $grade; + } + + public function buildCondition(UserFilter &$uf) + { + $conds = array(); + $sub = $uf->addMedalFilter(); + $conds[] = $sub . '.mid = ' . XDB::format('{?}', $this->medal); + if ($this->grade != null) { + $conds[] = $sub . '.gid = ' . XDB::format('{?}', $this->grade); + } + return implode(' AND ', $conds); + } +} +// }}} + +// {{{ class UFC_Mentor_Expertise +/** Filters users by mentoring expertise + * @param $expertise Domain of expertise + */ +class UFC_Mentor_Expertise implements UserFilterCondition +{ + private $expertise; + + public function __construct($expertise) + { + $this->expertise = $expertise; + } + + public function buildCondition(UserFilter &$uf) + { + $sub = $uf->addMentorFilter(UserFilter::MENTOR_EXPERTISE); + return $sub . '.expertise LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\'', $this->expertise); + } +} +// }}} + +// {{{ class UFC_Mentor_Country +/** Filters users by mentoring country + * @param $country Two-letters code of country being searched + */ +class UFC_Mentor_Country implements UserFilterCondition +{ + private $country; + + public function __construct($country) + { + $this->country = $country; + } + + public function buildCondition(UserFilter &$uf) + { + $sub = $uf->addMentorFilter(UserFilter::MENTOR_COUNTRY); + return $sub . '.country = ' . XDB::format('{?}', $this->country); + } +} +// }}} + +// {{{ class UFC_Mentor_Sectorization +/** Filters users based on mentoring (sub|)sector + * @param $sector ID of sector + * @param $subsector Subsector (null for any) + */ +class UFC_Mentor_Sectorization implements UserFilterCondition +{ + private $sector; + private $subsector; + + public function __construct($sector, $subsector = null) + { + $this->sector = $sector; + $this->subsubsector = $subsector; + } + + public function buildCondition(UserFilter &$uf) + { + $sub = $uf->addMentorFilter(UserFilter::MENTOR_SECTOR); + $conds = array(); + $conds[] = $sub . '.sectorid = ' . XDB::format('{?}', $this->sector); + if ($this->subsector != null) { + $conds[] = $sub . '.subsectorid = ' . XDB::format('{?}', $this->subsector); + } + return implode(' AND ', $conds); + } +} +// }}} + +// {{{ class UFC_UserRelated +/** Filters users based on a relation toward a user + * @param $user User to which searched users are related + */ +abstract class UFC_UserRelated implements UserFilterCondition +{ + protected $user; + public function __construct(PlUser &$user) + { + $this->user =& $user; + } +} +// }}} + +// {{{ class UFC_Contact +/** Filters users who belong to selected user's contacts + */ +class UFC_Contact extends UFC_UserRelated +{ + public function buildCondition(UserFilter &$uf) + { + $sub = $uf->addContactFilter($this->user->id()); + return 'c' . $sub . '.contact IS NOT NULL'; + } +} +// }}} + +// {{{ class UFC_WatchRegistration +/** Filters users being watched by selected user + */ +class UFC_WatchRegistration extends UFC_UserRelated +{ + public function buildCondition(UserFilter &$uf) + { + if (!$this->user->watch('registration')) { + return UserFilterCondition::COND_FALSE; + } + $uids = $this->user->watchUsers(); + if (count($uids) == 0) { + return UserFilterCondition::COND_FALSE; + } else { + return '$UID IN ' . XDB::formatArray($uids); + } + } +} +// }}} + +// {{{ class UFC_WatchPromo +/** Filters users belonging to a promo watched by selected user + * @param $user Selected user (the one watching promo) + * @param $grade Formation the user is watching + */ +class UFC_WatchPromo extends UFC_UserRelated +{ + private $grade; + public function __construct(PlUser &$user, $grade = UserFilter::GRADE_ING) + { + parent::__construct($user); + $this->grade = $grade; } public function buildCondition(UserFilter &$uf) { - if ($this->sex != User::GENDER_MALE && $this->sex != User::GENDER_FEMALE) { - return self::COND_FALSE; + $promos = $this->user->watchPromos(); + if (count($promos) == 0) { + return UserFilterCondition::COND_FALSE; } else { - return XDB::format('p.sex = {?}', $this->sex == User::GENDER_FEMALE ? 'female' : 'male'); + $sube = $uf->addEducationFilter(true, $this->grade); + $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade); + return $field . ' IN ' . XDB::formatArray($promos); } } } +// }}} -class UFC_Group implements UserFilterCondition +// {{{ class UFC_WatchContact +/** Filters users watched by selected user + */ +class UFC_WatchContact extends UFC_Contact { - private $group; - private $admin; - public function __construct($group, $admin = false) - { - $this->group = $group; - $this->admin = $admin; - } - public function buildCondition(UserFilter &$uf) { - $sub = $uf->addGroupFilter($this->group); - $where = 'gpm' . $sub . '.perms IS NOT NULL'; - if ($this->admin) { - $where .= ' AND gpm' . $sub . '.perms = \'admin\''; + if (!$this->user->watchContacts()) { + return UserFilterCondition::COND_FALSE; } - return $where; + return parent::buildCondition($uf); } } - +// }}} /****************** * ORDERS ******************/ -abstract class UserFilterOrder +// {{{ 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. + */ +abstract class UserFilterOrder extends PlFilterOrder { - protected $desc = false; - - public function buildSort(UserFilter &$uf) - { - $sel = $this->getSortTokens($uf); - if (!is_array($sel)) { - $sel = array($sel); - } - if ($this->desc) { - foreach ($sel as $k=>$s) { - $sel[$k] = $s . ' DESC'; - } - } - return $sel; - } - + /** 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); } +// }}} +// {{{ 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 { private $grade; public function __construct($grade = null, $desc = false) { + parent::__construct($desc); $this->grade = $grade; - $this->desc = $desc; } protected function getSortTokens(UserFilter &$uf) @@ -379,7 +1007,15 @@ class UFO_Promo extends UserFilterOrder } } } - +// }}} + +// {{{ class UFO_Name +/** Sorts users by name + * @param $type Type of name on which to sort (firstname...) + * @param $variant Variant of that name to use (marital, ordinary...) + * @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 { private $type; @@ -388,10 +1024,10 @@ class UFO_Name extends UserFilterOrder public function __construct($type, $variant = null, $particle = false, $desc = false) { + parent::__construct($desc); $this->type = $type; $this->variant = $variant; $this->particle = $particle; - $this->desc = $desc; } protected function getSortTokens(UserFilter &$uf) @@ -409,19 +1045,56 @@ class UFO_Name extends UserFilterOrder } } } +// }}} +// {{{ class UFO_Registration +/** Sorts users based on registration date + */ class UFO_Registration extends UserFilterOrder { - public function __construct($desc = false) + protected function getSortTokens(UserFilter &$uf) { - $this->desc = $desc; + return 'a.registration_date'; } +} +// }}} +// {{{ class UFO_Birthday +/** Sorts users based on next birthday date + */ +class UFO_Birthday extends UserFilterOrder +{ protected function getSortTokens(UserFilter &$uf) { - return 'a.registration_date'; + return 'p.next_birthday'; + } +} +// }}} + +// {{{ class UFO_ProfileUpdate +/** Sorts users based on last profile update + */ +class UFO_ProfileUpdate extends UserFilterOrder +{ + protected function getSortTokens(UserFilter &$uf) + { + return 'p.last_change'; + } +} +// }}} + +// {{{ class UFO_Death +/** Sorts users based on death date + */ +class UFO_Death extends UserFilterOrder +{ + protected function getSortTokens(UserFilter &$uf) + { + return 'p.deathdate'; } } +// }}} + /*********************************** ********************************* @@ -429,16 +1102,61 @@ class UFO_Registration extends UserFilterOrder ********************************* ***********************************/ +// {{{ class UserFilter +/** This class provides a convenient and centralized way of filtering users. + * + * Usage: + * $uf = new UserFilter(new UFC_Blah($x, $y), new UFO_Coin($z, $t)); + * + * Resulting UserFilter can be used to: + * - get a list of User objects matching the filter + * - get a list of UIDs matching the filter + * - get the number of users matching the filter + * - check whether a given User matches the filter + * - filter a list of User objects depending on whether they match the filter + * + * Usage for UFC and UFO objects: + * A UserFilter will call all private functions named XXXJoins. + * These functions must return an array containing the list of join + * required by the various UFC and UFO associated to the UserFilter. + * Entries in those returned array are of the following form: + * 'join_tablealias' => 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 auth_user_md5.user_id. + * + * 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 { - static private $joinMethods = array(); + private $joinMethods = array(); + + private $joinMetas = array('$PID' => 'p.pid', + '$UID' => 'a.uid', + ); private $root; private $sort = array(); private $query = null; private $orderby = null; - private $lastcount = 0; + private $lastcount = null; public function __construct($cond = null, $sort = null) { @@ -484,64 +1202,26 @@ class UserFilter $where = $this->root->buildCondition($this); $joins = $this->buildJoins(); $this->query = 'FROM accounts AS a - INNER JOIN account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms)) - INNER JOIN profiles AS p ON (p.pid = ap.pid) + 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 . ')'; } } - private function formatJoin(array $joins) - { - $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; - } - - private function buildJoins() - { - $joins = array(); - foreach (self::$joinMethods as $method) { - $joins = array_merge($joins, $this->$method()); - } - return $this->formatJoin($joins); - } - - private function getUIDList($uids = null, $count = null, $offset = null) + private function getUIDList($uids = null, PlLimit &$limit) { $this->buildQuery(); - $limit = ''; - if (!is_null($count)) { - if (!is_null($offset)) { - $limit = XDB::format('LIMIT {?}, {?}', $offset, $count); - } else { - $limit = XDB::format('LIMIT {?}', $count); - } - } + $lim = $limit->getSql(); $cond = ''; if (!is_null($uids)) { - $cond = ' AND a.uid IN (' . implode(', ', $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); + ' . $lim); $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()'); return $fetched; } @@ -558,16 +1238,21 @@ class UserFilter /** Filter a list of user to extract the users matching the rule. */ - public function filter(array $users, $count = null, $offset = null) + public function filter(array $users, PlLimit &$limit) { $this->buildQuery(); $table = array(); $uids = array(); foreach ($users as $user) { - $uids[] = $user->id(); - $table[$user->id()] = $user; + if ($user instanceof PlUser) { + $uid = $user->id(); + } else { + $uid = $user; + } + $uids[] = $uid; + $table[$uid] = $user; } - $fetched = $this->getUIDList($uids, $count, $offset); + $fetched = $this->getUIDList($uids, $limit); $output = array(); foreach ($fetched as $uid) { $output[] = $table[$uid]; @@ -575,22 +1260,27 @@ class UserFilter return $output; } - public function getUIDs($count = null, $offset = null) + public function getUIDs(PlLimit &$limit) + { + return $this->getUIDList(null, $limit); + } + + public function getUsers(PlLimit &$limit) { - return $this->getUIDList(null, $count, $offset); + return User::getBulkUsersWithUIDs($this->getUIDs($limit)); } - public function getUsers($count = null, $offset = null) + public function get(PlLimit &$limit) { - return User::getBulkUsersWithUIDs($this->getUIDs($count, $offset)); + return $this->getUsers($limit); } public function getTotalCount() { if (is_null($this->lastcount)) { - return (int)XDB::fetchOneCell('SELECT COUNT(*) - ' . $this->query . ' - GROUP BY a.uid'); + $this->buildQuery(); + return (int)XDB::fetchOneCell('SELECT COUNT(DISTINCT a.uid) + ' . $this->query); } else { return $this->lastcount; } @@ -623,6 +1313,41 @@ class UserFilter return new UserFilter(new UFC_And($min, $max)); } + static public function sortByName() + { + return array(new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME)); + } + + static public function sortByPromo() + { + return array(new UFO_Promo(), new UFO_Name(self::LASTNAME), new UFO_Name(self::FIRSTNAME)); + } + + static private function getDBSuffix($string) + { + return preg_replace('/[^a-z0-9]/i', '', $string); + } + + + /** Stores a new (and unique) table alias in the &$table table + * @param &$table Array in which the table alias must be stored + * @param $val Value which will then be used to build the join + * @return Name of the newly created alias + */ + private $option = 0; + private function register_optional(array &$table, $val) + { + if (is_null($val)) { + $sub = $this->option++; + $index = null; + } else { + $sub = self::getDBSuffix($val); + $index = $val; + } + $sub = '_' . $sub; + $table[$sub] = $index; + return $sub; + } /** DISPLAY */ @@ -637,7 +1362,7 @@ class UserFilter private function displayJoins() { if ($this->pd) { - return array('pd' => array('left', 'profile_display', '$ME.pid = $PID')); + return array('pd' => new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_display', '$ME.pid = $PID')); } else { return array(); } @@ -687,7 +1412,6 @@ class UserFilter } private $pn = array(); - private $pno = 0; public function addNameFilter($type, $variant = null) { if (!is_null($variant)) { @@ -699,7 +1423,7 @@ class UserFilter self::assertName($ft); if (!is_null($variant) && $variant == 'other') { - $sub .= $this->pno++; + $sub .= $this->option++; } $this->pn[$sub] = Profile::getNameTypeId($ft); return $sub; @@ -709,7 +1433,7 @@ class UserFilter { $joins = array(); foreach ($this->pn as $sub => $type) { - $joins['pn' . $sub] = array('left', 'profile_name', '$ME.pid = $PID AND $ME.typeid = ' . $type); + $joins['pn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_name', '$ME.pid = $PID AND $ME.typeid = ' . $type); } return $joins; } @@ -740,12 +1464,11 @@ class UserFilter private $pepe = array(); private $with_pee = false; - private $pe_g = 0; public function addEducationFilter($x = false, $grade = null) { if (!$x) { - $index = $this->pe_g; - $sub = $this->pe_g++; + $index = $this->option; + $sub = $this->option++; } else { self::assertGrade($grade); $index = $grade; @@ -761,17 +1484,17 @@ class UserFilter { $joins = array(); if ($this->with_pee) { - $joins['pee'] = array('inner', 'profile_education_enum', 'pee.abbreviation = \'X\''); + $joins['pee'] = new PlSqlJoin(PlSqlJoin::MODE_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 ' . + $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)); } 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] = 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'); } } return $joins; @@ -781,7 +1504,6 @@ class UserFilter /** GROUPS */ private $gpm = array(); - private $gpm_o = 0; public function addGroupFilter($group = null) { if (!is_null($group)) { @@ -789,10 +1511,10 @@ class UserFilter $index = $sub = $group; } else { $index = $group; - $sub = preg_replace('/[^a-z0-9]/i', '', $group); + $sub = self::getDBSuffix($group); } } else { - $sub = 'group_' . $this->gpm_o++; + $sub = 'group_' . $this->option++; $index = null; } $sub = '_' . $sub; @@ -805,18 +1527,386 @@ class UserFilter $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'); + $joins['gpa' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'groupex.asso'); + $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_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['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = ' . $key); + } else { + $joins['gpa' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_INNER, 'groupex.asso', XDB::format('$ME.diminutif = {?}', $key)); + $joins['gpm' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id'); + } + } + return $joins; + } + + /** EMAILS + */ + private $e = array(); + public function addEmailRedirectFilter($email = null) + { + return $this->register_optional($this->e, $email); + } + + private $ve = array(); + public function addVirtualEmailFilter($email = null) + { + $this->addAliasFilter(self::ALIAS_FORLIFE); + return $this->register_optional($this->ve, $email); + } + + const ALIAS_BEST = 'bestalias'; + const ALIAS_FORLIFE = 'forlife'; + private $al = array(); + public function addAliasFilter($alias = null) + { + return $this->register_optional($this->al, $alias); + } + + private function emailJoins() + { + global $globals; + $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\''); + } else { + $joins['e' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'emails', XDB::format('$ME.uid = $UID AND $ME.flags != \'filter\' AND $ME.email = {?}', $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\')'); + } 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)'); + } else if ($key == self::ALIAS_FORLIFE) { + $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $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)); + } + } + foreach ($this->ve as $sub=>$key) { + if (is_null($key)) { + $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', '$ME.type = \'user\''); + } else { + $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', XDB::format('$ME.type = \'user\' AND $ME.alias = {?}', $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)); + } + return $joins; + } + + + /** ADDRESSES + */ + private $with_pa = false; + public function addAddressFilter() + { + $this->with_pa = true; + return 'pa'; + } + + private function addressJoins() + { + $joins = array(); + if ($this->with_pa) { + $joins['pa'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_address', '$ME.pid = $PID'); + } + return $joins; + } + + + /** CORPS + */ + + private $pc = false; + private $pce = array(); + private $pcr = false; + public function addCorpsFilter($type) + { + $this->pc = true; + if ($type == UFC_Corps::CURRENT) { + $pce['pcec'] = 'current_corpsid'; + return 'pcec'; + } else if ($type == UFC_Corps::ORIGIN) { + $pce['pceo'] = 'original_corpsid'; + return 'pceo'; + } + } + + public function addCorpsRankFilter() + { + $this->pc = true; + $this->pcr = true; + return 'pcr'; + } + + private function corpsJoins() + { + $joins = array(); + if ($this->pc) { + $joins['pc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps', '$ME.uid = $UID'); + } + if ($this->pcr) { + $joins['pcr'] = new PlSqlJoin(PlSqlJoin::MODE_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); + } + return $joins; + } + + /** JOBS + */ + + const JOB_SECTOR = 1; + const JOB_SUBSECTOR = 2; + const JOB_SUBSUBSECTOR = 4; + const JOB_ALTERNATES = 8; + const JOB_USERDEFINED = 16; + + /** 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 + */ + private $with_pj = false; + private $with_pje = false; + private $with_pjse = false; + private $with_pjsse = false; + private $with_pjssse = false; + private $with_pja = false; + + public function addJobFilter() + { + $this->with_pj = true; + return 'pj'; + } + + public function addJobCompanyFilter() + { + $this->addJobFilter(); + $this->with_pje = true; + return 'pje'; + } + + public function addJobSectorizationFilter($type) + { + $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'; + } + } + + private function jobJoins() + { + $joins = array(); + if ($this->with_pj) { + $joins['pj'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job', '$ME.uid = $UID'); + } + if ($this->with_pje) { + $joins['pje'] = new PlSqlJoin(PlSqlJoin::MODE_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'); + } + if ($this->with_pjsse) { + $joins['pjsse'] = new PlSqlJoin(PlSqlJoin::MODE_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'); + } + if ($this->with_pja) { + $joins['pja'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_alternates', '$ME.subsubsectorid = pj.subsubsectorid'); + } + return $joins; + } + + /** NETWORKING + */ + + private $with_pnw = false; + public function addNetworkingFilter() + { + $this->with_pnw = true; + return 'pnw'; + } + + private function networkingJoins() + { + $joins = array(); + if ($this->with_pnw) { + $joins['pnw'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_networking', '$ME.uid = $UID'); + } + return $joins; + } + + /** PHONE + */ + + private $with_ptel = false; + + public function addPhoneFilter() + { + $this->with_ptel = true; + return 'ptel'; + } + + private function phoneJoins() + { + $joins = array(); + if ($this->with_ptel) { + $joins['ptel'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_phone', '$ME.uid = $UID'); + } + return $joins; + } + + /** MEDALS + */ + + private $with_pmed = false; + public function addMedalFilter() + { + $this->with_pmed = true; + return 'pmed'; + } + + private function medalJoins() + { + $joins = array(); + if ($this->with_pmed) { + $joins['pmed'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_medals_sub', '$ME.uid = $UID'); + } + return $joins; + } + + /** MENTORING + */ + + private $pms = array(); + const MENTOR_EXPERTISE = 1; + const MENTOR_COUNTRY = 2; + const MENTOR_SECTOR = 3; + + public function addMentorFilter($type) + { + switch($type) { + case MENTOR_EXPERTISE: + $pms['pme'] = 'profile_mentor'; + return 'pme'; + case MENTOR_COUNTRY: + $pms['pmc'] = 'profile_mentor_country'; + return 'pmc'; + case MENTOR_SECTOR: + $pms['pms'] = 'profile_mentor_sector'; + return 'pms'; + default: + Platal::page()->killError("Undefined mentor filter."); + } + } + + private function mentorJoins() + { + $joins = array(); + foreach ($this->pms as $sub => $tab) { + $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, $tab, '$ME.uid = $UID'); + } + return $joins; + } + + /** CONTACTS + */ + private $cts = array(); + public function addContactFilter($uid = null) + { + return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid); + } + + private function contactJoins() + { + $joins = array(); + foreach ($this->cts as $sub=>$key) { + if (is_null($key)) { + $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', '$ME.contact = $UID'); + } else { + $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', XDB::format('$ME.uid = {?} AND $ME.contact = $UID', substr($key, 5))); + } + } + return $joins; + } + + + /** CARNET + */ + private $wn = array(); + public function addWatchRegistrationFilter($uid = null) + { + return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid); + } + + private $wp = array(); + public function addWatchPromoFilter($uid = null) + { + return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid); + } + + private $w = array(); + public function addWatchFilter($uid = null) + { + return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid); + } + + private function watchJoins() + { + $joins = array(); + foreach ($this->w as $sub=>$key) { + if (is_null($key)) { + $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch'); + } else { + $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch', XDB::format('$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'); + } else { + $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', XDB::format('$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'); + } else { + $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', XDB::format('$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'); } 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['wp' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_promo', XDB::format('$ME.uid = {?}', substr($key, 5))); } } return $joins; } } +// }}} // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: ?>