X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=classes%2Fuserfilter.php;h=85f89b919fa8e56b1ed30807659a0bdbf7e50551;hb=a7f8e48ad83d360e4f4f4cfd94167f4337e6ae72;hp=3bebf33361544288453fd48865d6cacf985ce5a7;hpb=5d2e55c7740ad21f9ddf2c71b6abe9b2ef94e6eb;p=platal.git diff --git a/classes/userfilter.php b/classes/userfilter.php index 3bebf33..85f89b9 100644 --- a/classes/userfilter.php +++ b/classes/userfilter.php @@ -25,155 +25,17 @@ ******************/ // {{{ interface UserFilterCondition -interface UserFilterCondition -{ - const COND_TRUE = 'TRUE'; - const COND_FALSE = 'FALSE'; - - /** Check that the given user matches the rule. - */ - public function buildCondition(UserFilter &$uf); -} -// }}} - -// {{{ class UFC_OneChild -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; - } -} -// }}} - -// {{{ class UFC_NChildren -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 -class UFC_True implements UserFilterCondition -{ - public function buildCondition(UserFilter &$uf) - { - return self::COND_TRUE; - } -} -// }}} - -// {{{ class UFC_False -class UFC_False implements UserFilterCondition -{ - public function buildCondition(UserFilter &$uf) - { - return self::COND_FALSE; - } -} -// }}} - -// {{{ class UFC_Not -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 -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 -class UFC_Or extends UFC_NChildren +/** 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 extends PlFilterCondition { - 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); - } - } } // }}} @@ -182,7 +44,7 @@ class UFC_Or extends UFC_NChildren */ class UFC_Profile implements UserFilterCondition { - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { return '$PID IS NOT NULL'; } @@ -212,11 +74,11 @@ class UFC_Promo implements UserFilterCondition } } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { 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); @@ -226,6 +88,30 @@ class UFC_Promo implements UserFilterCondition } // }}} +// {{{ class UFC_Formation +class UFC_Formation implements UserFilterCondition +{ + private $eduid; + private $edutext; + + public function __construct($eduid = null, $edutext = null) + { + $this->eduid = $eduid; + $this->edutext = $edutext; + } + + public function buildCondition(PlFilter &$uf) + { + $sub = $uf->addEducationFilter(); + if ($this->eduid != null) { + return 'pe' . $sub . '.eduid = ' . XDB::format('{?}', $this->eduid); + } else { + return 'pee' . $sub . '.name LIKE ' . XDB::format('CONCAT( \'%\', {?}, \'%\')', $this->edutext); + } + } +} +// }}} + // {{{ class UFC_Name /** Filters users based on name * @param $type Type of name field on which filtering is done (firstname, lastname...) @@ -257,7 +143,7 @@ class UFC_Name implements UserFilterCondition return str_replace('$ME', 'pn' . $sub, $where); } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $left = '$ME.name'; $op = ' LIKE '; @@ -286,6 +172,93 @@ class UFC_Name implements UserFilterCondition } // }}} +// {{{ class UFC_NameTokens +/** Selects users based on tokens in their name (for quicksearch) + * @param $tokens An array of tokens to search + * @param $flags Flags the tokens must have (e.g 'public' for public search) + * @param $soundex (bool) Whether those tokens are fulltext or soundex + */ +class UFC_NameTokens implements UserFilterCondition +{ + /* Flags */ + const FLAG_PUBLIC = 'public'; + + private $tokens; + private $flags; + private $soundex; + private $exact; + + public function __construct($tokens, $flags = array(), $soundex = false, $exact = false) + { + $this->tokens = $tokens; + if (is_array($flags)) { + $this->flags = $flags; + } else { + $this->flags = array($flags); + } + $this->soundex = $soundex; + $this->exact = $exact; + } + + 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 LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $token); + } + $conds[] = implode(' OR ', $tokconds); + } + + if ($this->flags != null) { + $conds[] = $sub . '.flags IN ' . XDB::formatArray($this->flags); + } + + return implode(' AND ', $conds); + } +} +// }}} + +// {{{ class UFC_Nationality +class UFC_Nationality implements UserFilterCondition +{ + private $nid; + private $ntext; + + public function __construct($nid = null, $ntext = null) + { + $this->nid = $nid; + $this->ntext = $ntext; + } + + public function buildCondition(PlFilter &$uf) + { + if ($this->nid != null) { + $uf->requireProfiles(); + $nat = XDB::format('{?}', $this->nid); + $conds = array( + 'p.nationality1 = ' . $nat, + 'p.nationality2 = ' . $nat, + 'p.nationality3 = ' . $nat, + ); + return implode(' OR ', $conds); + } else { + $sub = $uf->addNationalityJoins(); + $nat = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->ntext); + $cond = $sub . '.nationality LIKE ' . $nat . ' OR ' . + $sub . '.nationalityFR LIKE ' . $nat; + return $cond; + } + } +} +// }}} + // {{{ class UFC_Dead /** Filters users based on death date * @param $comparison Comparison operator @@ -302,7 +275,7 @@ class UFC_Dead implements UserFilterCondition $this->date = $date; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $str = 'p.deathdate IS NOT NULL'; if (!is_null($this->comparison)) { @@ -327,12 +300,12 @@ 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; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { if ($this->active) { $date = 'a.uid IS NOT NULL AND a.state = \'active\''; @@ -363,7 +336,7 @@ class UFC_ProfileUpdated implements UserFilterCondition $this->date = $date; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { return 'p.last_change ' . $this->comparison . XDB::format(' {?}', date('Y-m-d H:i:s', $this->date)); } @@ -386,7 +359,7 @@ class UFC_Birthday implements UserFilterCondition $this->date = $date; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { return 'p.next_birthday ' . $this->comparison . XDB::format(' {?}', date('Y-m-d', $this->date)); } @@ -405,7 +378,7 @@ class UFC_Sex implements UserFilterCondition $this->sex = $sex; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { if ($this->sex != User::GENDER_MALE && $this->sex != User::GENDER_FEMALE) { return self::COND_FALSE; @@ -431,7 +404,7 @@ class UFC_Group implements UserFilterCondition $this->anim = $anim; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $sub = $uf->addGroupFilter($this->group); $where = 'gpm' . $sub . '.perms IS NOT NULL'; @@ -443,6 +416,49 @@ class UFC_Group implements UserFilterCondition } // }}} +// {{{ class UFC_Binet +class UFC_Binet implements UserFilterCondition +{ + private $binetid; + private $binetname; + + public function __construct($binetid = null, $binetname = null) + { + $this->binetid = $binetid; + $this->binetname = $binetname; + } + + public function buildCondition(PlFilter &$uf) + { + if ($this->binetid != null) { + $sub = $uf->addBinetsFilter(); + return $sub . 'binet_id = ' . XDB::format('{?}', $this->binetid); + } else { + $sub = $uf->addBinetsFilter(true); + return $sub . 'test LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->binetname); + } + } +} +// }}} + +// {{{ class UFC_Section +class UFC_Section implements UserFilterCondition +{ + private $section; + + public function __construct($section) + { + $this->section = $section; + } + + public function buildCondition(PlFilter &$uf) + { + $uf->requireProfiles(); + return 'p.section = ' . XDB::format('{?}', $this->section); + } +} +// }}} + // {{{ class UFC_Email /** Filters users based on email address * @param $email Email whose owner we are looking for @@ -455,7 +471,7 @@ class UFC_Email implements UserFilterCondition $this->email = $email; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { if (User::isForeignEmailAddress($this->email)) { $sub = $uf->addEmailRedirectFilter($this->email); @@ -484,7 +500,7 @@ class UFC_EmailList implements UserFilterCondition $this->emails = $emails; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $email = null; $virtual = null; @@ -492,7 +508,7 @@ class UFC_EmailList implements UserFilterCondition $cond = array(); if (count($this->emails) == 0) { - return UserFilterCondition::COND_TRUE; + return PlFilterCondition::COND_TRUE; } foreach ($this->emails as $entry) { @@ -520,33 +536,112 @@ class UFC_EmailList implements UserFilterCondition // }}} // {{{ class UFC_Address -/** Filters users based on their address - * @param $field Field of the address used for filtering (city, street, ...) - * @param $text Text for filter - * @param $mode Mode for search (PREFIX, SUFFIX, ...) +abstract class UFC_Address implements UserFilterCondition +{ + /** Valid address type ('hq' is reserved for company addresses) + */ + const TYPE_HOME = 1; + const TYPE_PRO = 2; + const TYPE_ANY = 3; + + /** Text for these types + */ + protected static $typetexts = array( + self::TYPE_HOME => 'home', + self::TYPE_PRO => 'pro', + ); + + protected $type; + + /** 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 + */ + protected static $flagtexts = array( + self::FLAG_CURRENT => 'current', + self::FLAG_TEMP => 'temporary', + self::FLAG_SECOND => 'secondary', + self::FLAG_MAIL => 'mail', + self::FLAG_CEDEX => 'cedex', + ); + + protected $flags; + + public function __construct($type = null, $flags = null) + { + $this->type = $type; + $this->flags = $flags; + } + + protected function initConds($sub) + { + $conds = array(); + $types = array(); + foreach (self::$typetexts as $flag => $type) { + if ($flag & $this->type) { + $types[] = $type; + } + } + if (count($types)) { + $conds[] = $sub . '.type IN ' . XDB::formatArray($types); + } + + 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)'; + } + } + } + return $conds; + } + +} +// }}} + +// {{{ class UFC_AddressText +/** Select users based on their address, using full text search + * @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 $country Filter on address country + * @param $locality Filter on address locality */ -class UFC_Address implements UserFilterCondition +class UFC_AddressText extends UFC_Address { - const PREFIX = 1; - const SUFFIX = 2; - const CONTAINS = 3; + /** Flags for text search + */ + const PREFIX = 0x0001; + const SUFFIX = 0x0002; + const CONTAINS = 0x0003; - private $field; private $text; - private $mode; + private $textSearchMode; - public function __construct($field, $text, $mode) + public function __construct($text = null, $textSearchMode = self::CONTAINS, + $type = null, $flags = self::FLAG_ANY, $country = null, $locality = null) { - $this->field = $field; - $this->text = $text; - $this->mode = $mode; + parent::__construct($type, $flags); + $this->text = $text; + $this->textSearchMode = $textSearchMode; + $this->country = $country; + $this->locality = $locality; } - public function buildCondition(UserFilter &$uf) + private function mkMatch($txt) { - $left = 'pa.' . $field; - $op = ' LIKE '; - if (($this->mode & self::CONTAINS) == 0) { + $op = ' LIKE '; + if (($this->textSearchMode & self::CONTAINS) == 0) { $right = XDB::format('{?}', $this->text); $op = ' = '; } else if (($this->mode & self::CONTAINS) == self::PREFIX) { @@ -556,9 +651,88 @@ class UFC_Address implements UserFilterCondition } else { $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text); } - $cond = $left . $op . $right; - $uf->addAddressFilter(); - return $cond; + return $op . $right; + } + + public function buildCondition(PlFilter &$uf) + { + $sub = $uf->addAddressFilter(); + $conds = $this->initConds($sub); + if ($this->text != null) { + $conds[] = $sub . '.text' . $this->mkMatch($this->text); + } + + if ($this->country != null) { + $subc = $uf->addAddressCountryFilter(); + $subconds = array(); + $subconds[] = $subc . '.country' . $this->mkMatch($this->country); + $subconds[] = $subc . '.countryFR' . $this->mkMatch($this->country); + $conds[] = implode(' OR ', $subconds); + } + + if ($this->locality != null) { + $subl = $uf->addAddressLocalityFilter(); + $conds[] = $subl . '.name' . $this->mkMatch($this->locality); + } + + return implode(' AND ', $conds); + } +} +// }}} + +// {{{ class UFC_AddressFields +/** Filters users based on their address, + * @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_AddressFields extends UFC_Address +{ + /** Data of the filter + */ + private $countryId; + private $administrativeAreaId; + private $subAdministrativeAreaId; + private $localityId; + private $postalCode; + + public function __construct($type = null, $flags = self::FLAG_ANY, $countryId = null, $administrativeAreaId = null, + $subAdministrativeAreaId = null, $localityId = null, $postalCode = null) + { + parent::__construct($type, $flags); + $this->countryId = $countryId; + $this->administrativeAreaId = $administrativeAreaId; + $this->subAdministrativeAreaId = $subAdministrativeAreaId; + $this->localityId = $localityId; + $this->postalCode = $postalCode; + } + + public function buildCondition(PlFilter &$uf) + { + $sub = $uf->addAddressFilter(); + $conds = $this->initConds(); + + 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); } } // }}} @@ -582,7 +756,7 @@ class UFC_Corps implements UserFilterCondition $this->type = $type; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { /** Tables shortcuts: * pc for profile_corps, @@ -608,7 +782,7 @@ class UFC_Corps_Rank implements UserFilterCondition $this->rank = $rank; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { /** Tables shortcuts: * pcr for profile_corps_rank @@ -625,7 +799,7 @@ class UFC_Corps_Rank implements UserFilterCondition * @param $type The field being searched (self::JOBID, self::JOBNAME or self::JOBACRONYM) * @param $value The searched value */ -class UFC_Job_Company extends UserFilterCondition +class UFC_Job_Company implements UserFilterCondition { const JOBID = 'id'; const JOBNAME = 'name'; @@ -648,7 +822,7 @@ class UFC_Job_Company extends UserFilterCondition } } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $sub = $uf->addJobCompanyFilter(); $cond = $sub . '.' . $this->type . ' = ' . XDB::format('{?}', $this->value); @@ -663,7 +837,7 @@ class UFC_Job_Company extends UserFilterCondition * @param $subsector The subsector * @param $subsubsector The subsubsector */ -class UFC_Job_Sectorization extends UserFilterCondition +class UFC_Job_Sectorization implements UserFilterCondition { private $sector; @@ -677,7 +851,7 @@ class UFC_Job_Sectorization extends UserFilterCondition $this->subsubsector = $subsubsector; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { // No need to add the JobFilter, it will be done by addJobSectorizationFilter $conds = array(); @@ -703,31 +877,35 @@ class UFC_Job_Sectorization extends UserFilterCondition * @param $description The text being searched for * @param $fields The fields to search for (user-defined, ((sub|)sub|)sector) */ -class UFC_Job_Description extends UserFilterCondition +class UFC_Job_Description implements UserFilterCondition { /** Meta-filters * Built with binary OR on UserFilter::JOB_* */ - const ANY = 31; + const ANY = 63; const SECTORIZATION = 15; private $description; private $fields; - public function __construct($description) + public function __construct($description, $fields) { $this->fields = $fields; $this->description = $description; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$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_CV) { + $uf->requireProfiles(); + $conds[] = 'p.cv 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); @@ -752,7 +930,7 @@ class UFC_Job_Description extends UserFilterCondition * @param $type Type of network (-1 for any) * @param $value Value to search */ -class UFC_Networking extends UserFilterCondition +class UFC_Networking implements UserFilterCondition { private $type; private $value; @@ -763,7 +941,7 @@ class UFC_Networking extends UserFilterCondition $this->value = $value; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $sub = $uf->addNetworkingFilter(); $conds = array(); @@ -782,7 +960,7 @@ class UFC_Networking extends UserFilterCondition * @param $phone_type Type of phone (fixed/mobile/fax) * @param $number Phone number */ -class UFC_Phone extends UserFilterCondition +class UFC_Phone implements UserFilterCondition { const NUM_PRO = 'pro'; const NUM_USER = 'user'; @@ -800,13 +978,13 @@ class UFC_Phone extends UserFilterCondition public function __construct($number, $num_type = self::NUM_ANY, $phone_type = self::PHONE_ANY) { - require_once('profil.inc.php'); + require_once('profil.func.inc.php'); $this->number = $number; $this->num_type = $num_type; $this->phone_type = format_phone_number($phone_type); } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $sub = $uf->addPhoneFilter(); $conds = array(); @@ -827,7 +1005,7 @@ class UFC_Phone extends UserFilterCondition * @param $medal ID of the medal * @param $grade Grade of the medal (null for 'any') */ -class UFC_Medal extends UserFilterCondition +class UFC_Medal implements UserFilterCondition { private $medal; private $grade; @@ -838,7 +1016,7 @@ class UFC_Medal extends UserFilterCondition $this->grade = $grade; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $conds = array(); $sub = $uf->addMedalFilter(); @@ -855,7 +1033,7 @@ class UFC_Medal extends UserFilterCondition /** Filters users by mentoring expertise * @param $expertise Domain of expertise */ -class UFC_Mentor_Expertise extends UserFilterCondition +class UFC_Mentor_Expertise implements UserFilterCondition { private $expertise; @@ -864,7 +1042,7 @@ class UFC_Mentor_Expertise extends UserFilterCondition $this->expertise = $expertise; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $sub = $uf->addMentorFilter(UserFilter::MENTOR_EXPERTISE); return $sub . '.expertise LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\'', $this->expertise); @@ -876,7 +1054,7 @@ class UFC_Mentor_Expertise extends UserFilterCondition /** Filters users by mentoring country * @param $country Two-letters code of country being searched */ -class UFC_Mentor_Country extends UserFilterCondition +class UFC_Mentor_Country implements UserFilterCondition { private $country; @@ -885,7 +1063,7 @@ class UFC_Mentor_Country extends UserFilterCondition $this->country = $country; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $sub = $uf->addMentorFilter(UserFilter::MENTOR_COUNTRY); return $sub . '.country = ' . XDB::format('{?}', $this->country); @@ -898,7 +1076,7 @@ class UFC_Mentor_Country extends UserFilterCondition * @param $sector ID of sector * @param $subsector Subsector (null for any) */ -class UFC_Mentor_Sectorization extends UserFilterCondition +class UFC_Mentor_Sectorization implements UserFilterCondition { private $sector; private $subsector; @@ -909,7 +1087,7 @@ class UFC_Mentor_Sectorization extends UserFilterCondition $this->subsubsector = $subsector; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $sub = $uf->addMentorFilter(UserFilter::MENTOR_SECTOR); $conds = array(); @@ -941,7 +1119,7 @@ abstract class UFC_UserRelated implements UserFilterCondition */ class UFC_Contact extends UFC_UserRelated { - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $sub = $uf->addContactFilter($this->user->id()); return 'c' . $sub . '.contact IS NOT NULL'; @@ -954,14 +1132,14 @@ class UFC_Contact extends UFC_UserRelated */ class UFC_WatchRegistration extends UFC_UserRelated { - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { if (!$this->user->watch('registration')) { - return UserFilterCondition::COND_FALSE; + return PlFilterCondition::COND_FALSE; } $uids = $this->user->watchUsers(); if (count($uids) == 0) { - return UserFilterCondition::COND_FALSE; + return PlFilterCondition::COND_FALSE; } else { return '$UID IN ' . XDB::formatArray($uids); } @@ -983,11 +1161,11 @@ class UFC_WatchPromo extends UFC_UserRelated $this->grade = $grade; } - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { $promos = $this->user->watchPromos(); if (count($promos) == 0) { - return UserFilterCondition::COND_FALSE; + return PlFilterCondition::COND_FALSE; } else { $sube = $uf->addEducationFilter(true, $this->grade); $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade); @@ -1002,10 +1180,10 @@ class UFC_WatchPromo extends UFC_UserRelated */ class UFC_WatchContact extends UFC_Contact { - public function buildCondition(UserFilter &$uf) + public function buildCondition(PlFilter &$uf) { if (!$this->user->watchContacts()) { - return UserFilterCondition::COND_FALSE; + return PlFilterCondition::COND_FALSE; } return parent::buildCondition($uf); } @@ -1018,28 +1196,17 @@ class UFC_WatchContact extends UFC_Contact ******************/ // {{{ class UserFilterOrder -abstract 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 __construct($desc = false) - { - $this->desc = $desc; - } - - 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); } // }}} @@ -1110,6 +1277,17 @@ class UFO_Name extends UserFilterOrder } // }}} +// {{{ class UFO_Score +class UFO_Score extends UserFilterOrder +{ + protected function getSortTokens(UserFilter &$uf) + { + $sub = $uf->addNameTokensFilter(); + return 'SUM(' . $sub . '.score)'; + } +} +// }}} + // {{{ class UFO_Registration /** Sorts users based on registration date */ @@ -1166,9 +1344,53 @@ class UFO_Death extends UserFilterOrder ***********************************/ // {{{ class UserFilter -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 extends PlFilter { - static private $joinMethods = array(); + protected $joinMethods = array(); + + protected $joinMetas = array('$PID' => 'p.pid', + '$UID' => 'a.uid', + ); private $root; private $sort = array(); @@ -1179,17 +1401,17 @@ class UserFilter public function __construct($cond = null, $sort = null) { - if (empty(self::$joinMethods)) { + if (empty($this->joinMethods)) { $class = new ReflectionClass('UserFilter'); foreach ($class->getMethods() as $method) { $name = $method->getName(); if (substr($name, -5) == 'Joins' && $name != 'buildJoins') { - self::$joinMethods[] = $name; + $this->joinMethods[] = $name; } } } if (!is_null($cond)) { - if ($cond instanceof UserFilterCondition) { + if ($cond instanceof PlFilterCondition) { $this->setCondition($cond); } } @@ -1219,57 +1441,27 @@ class UserFilter } 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) { + $from = 'accounts AS a'; + } else { + $this->requireProfiles(); + $from = 'profiles AS p'; + } $joins = $this->buildJoins(); - $this->query = 'FROM accounts AS a - LEFT JOIN account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms)) - LEFT JOIN profiles AS p ON (p.pid = ap.pid) + $this->query = 'FROM ' . $from . ' ' . $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->requireAccounts(); $this->buildQuery(); - $limit = ''; - if (!is_null($count)) { - if (!is_null($offset)) { - $limit = XDB::format('LIMIT {?}, {?}', (int)$offset, (int)$count); - } else { - $limit = XDB::format('LIMIT {?}', (int)$count); - } - } + $lim = $limit->getSql(); $cond = ''; if (!is_null($uids)) { $cond = ' AND a.uid IN ' . XDB::formatArray($uids); @@ -1278,7 +1470,25 @@ class UserFilter ' . $this->query . $cond . ' GROUP BY a.uid ' . $this->orderby . ' - ' . $limit); + ' . $lim); + $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()'); + return $fetched; + } + + private function getPIDList($pids = null, PlLimit &$limit) + { + $this->requireProfiles(); + $this->buildQuery(); + $lim = $limit->getSql(); + $cond = ''; + if (!is_null($pids)) { + $cond = ' AND p.pid IN ' . XDB::formatArray($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()'); return $fetched; } @@ -1287,16 +1497,36 @@ class UserFilter */ public function checkUser(PlUser &$user) { + $this->requireAccounts(); $this->buildQuery(); $count = (int)XDB::fetchOneCell('SELECT COUNT(*) ' . $this->query . XDB::format(' AND a.uid = {?}', $user->id())); return $count == 1; } - /** Filter a list of user to extract the users matching the rule. + /** Check that the profile match the given rule. + */ + public function checkProfile(Profile &$profile) + { + $this->requireProfiles(); + $this->buildQuery(); + $count = (int)XDB::fetchOneCell('SELECT COUNT(*) + ' . $this->query . XDB::format(' AND p.pid = {?}', $profile->id())); + return $count == 1; + } + + /** Default filter is on users + */ + public function filter(array $users, PlLimit &$limit) + { + return $this->filterUsers($users, $limit); + } + + /** Filter a list of users to extract the users matching the rule. */ - public function filter(array $users, $count = null, $offset = null) + public function filterUsers(array $users, PlLimit &$limit) { + $this->requireAccounts(); $this->buildQuery(); $table = array(); $uids = array(); @@ -1309,7 +1539,7 @@ class UserFilter $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]; @@ -1317,34 +1547,79 @@ class UserFilter return $output; } - public function getUIDs($count = null, $offset = null) + /** Filter a list of profiles to extract the users matching the rule. + */ + public function filterProfiles(array $profiles, PlLimit &$limit) + { + $this->requireProfiles(); + $this->buildQuery(); + $table = array(); + $pids = array(); + foreach ($profiles as $profile) { + if ($profile instanceof Profile) { + $pid = $profile->id(); + } else { + $pid = $profile; + } + $pids[] = $pid; + $table[$pid] = $profile; + } + $fetched = $this->getPIDList($pids, $limit); + $output = array(); + foreach ($fetched as $pid) { + $output[] = $table[$pid]; + } + return $output; + } + + public function getUIDs(PlLimit &$limit) + { + return $this->getUIDList(null, $limit); + } + + public function getPIDs(PlLimit &$limit) + { + return $this->getPIDList(null, $limit); + } + + public function getUsers(PlLimit &$limit) + { + return User::getBulkUsersWithUIDs($this->getUIDs($limit)); + } + + public function getProfiles(PlLimit &$limit) { - return $this->getUIDList(null, $count, $offset); + return Profile::getBulkProfilesWithPIDs($this->getPIDs($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)) { $this->buildQuery(); - return (int)XDB::fetchOneCell('SELECT COUNT(DISTINCT a.uid) + if ($this->with_accounts) { + $field = 'a.uid'; + } else { + $field = 'p.pid'; + } + return (int)XDB::fetchOneCell('SELECT COUNT(DISTINCT ' . $field . ') ' . $this->query); } else { return $this->lastcount; } } - public function setCondition(UserFilterCondition &$cond) + public function setCondition(PlFilterCondition &$cond) { $this->root =& $cond; $this->query = null; } - public function addSort(UserFilterOrder &$sort) + public function addSort(PlFilterOrder &$sort) { $this->sort[] = $sort; $this->orderby = null; @@ -1362,7 +1637,7 @@ class UserFilter } else { $max = new UFC_True(); } - return new UserFilter(new UFC_And($min, $max)); + return new UserFilter(new PFC_And($min, $max)); } static public function sortByName() @@ -1381,6 +1656,11 @@ class UserFilter } + /** 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) { @@ -1396,20 +1676,59 @@ class UserFilter return $sub; } + /** PROFILE VS ACCOUNT + */ + private $with_profiles = false; + private $with_accounts = false; + private $with_forced_sn = false; + public function requireAccounts() + { + $this->with_accounts = true; + } + + public function requireProfiles() + { + $this->with_profiles = true; + } + + /** Forces the "FROM" to use search_name instead of accounts or profiles */ + public function forceSearchName() + { + $this->with_forced_sn = true; + } + + 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'); + } + return $joins; + } + /** DISPLAY */ const DISPLAY = 'display'; private $pd = false; public function addDisplayFilter() { + $this->requireProfiles(); $this->pd = true; return ''; } - private function displayJoins() + protected function displayJoins() { if ($this->pd) { - return array('pd' => array('left', 'profile_display', '$ME.pid = $PID')); + return array('pd' => new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_display', '$ME.pid = $PID')); } else { return array(); } @@ -1446,7 +1765,7 @@ class UserFilter static public function assertName($name) { if (!Profile::getNameTypeId($name)) { - Platal::page()->kill('Invalid name type'); + Platal::page()->kill('Invalid name type: ' . $name); } } @@ -1461,6 +1780,7 @@ class UserFilter private $pn = array(); public function addNameFilter($type, $variant = null) { + $this->requireProfiles(); if (!is_null($variant)) { $ft = $type . '_' . $variant; } else { @@ -1476,15 +1796,57 @@ class UserFilter return $sub; } - private function nameJoins() + protected function nameJoins() { $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; } + /** NAMETOKENS + */ + private $with_sn = false; + // Set $doingQuickSearch to true if you wish to optimize the query + public function addNameTokensFilter($doingQuickSearch = false) + { + $this->requireProfiles(); + $this->with_forced_sn = ($this->with_forced_sn || $doingQuickSearch); + $this->with_sn = true; + return 'sn'; + } + + 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(); + } + } + + /** NATIONALITY + */ + + private $with_nat = false; + public function addNationalityFilter() + { + $this->with_nat = true; + return 'ngc'; + } + + protected function nationalityJoins() + { + $joins = array(); + if ($this->with_nat) { + $joins['ngc'] = 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'); + } + return $joins; + } /** EDUCATION */ @@ -1513,6 +1875,7 @@ class UserFilter private $with_pee = false; public function addEducationFilter($x = false, $grade = null) { + $this->requireProfiles(); if (!$x) { $index = $this->option; $sub = $this->option++; @@ -1527,21 +1890,21 @@ class UserFilter return $sub; } - private function educationJoins() + protected function educationJoins() { $joins = array(); if ($this->with_pee) { - $joins['pee'] = array('inner', 'profile_education_enum', 'pee.abbreviation = \'X\''); + $joins['pee'] = 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; @@ -1553,6 +1916,7 @@ class UserFilter private $gpm = array(); public function addGroupFilter($group = null) { + $this->requireAccounts(); if (!is_null($group)) { if (ctype_digit($group)) { $index = $sub = $group; @@ -1569,28 +1933,58 @@ class UserFilter return $sub; } - private function groupJoins() + protected function groupJoins() { $joins = array(); foreach ($this->gpm as $sub => $key) { if (is_null($key)) { - $joins['gpa' . $sub] = array('inner', 'groupex.asso'); - $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id'); + $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] = array('inner', 'groupex.asso', XDB::format('$ME.diminutif = {?}', $key)); - $joins['gpm' . $sub] = array('left', 'groupex.membres', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id'); + $joins['gpa' . $sub] = 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; } + /** BINETS + */ + + private $with_bi = false; + private $with_bd = false; + public function addBinetsFilter($with_enum = false;) + { + $this->requireProfiles(); + $this->with_bi = true; + if ($with_enum) { + $this->with_bd = true; + return 'bd'; + } else { + return 'bi'; + } + } + + protected function binetsJoins() + { + $joins = array(); + if ($this->with_bi) { + $joins['bi'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'binets_ins', '$ME.uid = $PID'); + } + if ($this->with_bd) { + $joins['bd'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'binets_def', '$ME.id = bi.binet_id'); + } + return $joins; + } + /** EMAILS */ private $e = array(); public function addEmailRedirectFilter($email = null) { + $this->requireAccounts(); return $this->register_optional($this->e, $email); } @@ -1606,38 +2000,39 @@ class UserFilter private $al = array(); public function addAliasFilter($alias = null) { + $this->requireAccounts(); return $this->register_optional($this->al, $alias); } - private function emailJoins() + protected function emailJoins() { global $globals; $joins = array(); foreach ($this->e as $sub=>$key) { if (is_null($key)) { - $joins['e' . $sub] = array('left', 'emails', '$ME.uid = $UID AND $ME.flags != \'filter\''); + $joins['e' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'emails', '$ME.uid = $UID AND $ME.flags != \'filter\''); } else { - $joins['e' . $sub] = array('left', 'emails', XDB::format('$ME.uid = $UID AND $ME.flags != \'filter\' AND $ME.email = {?}', $key)); + $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] = array('left', 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\')'); + $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] = array('left', 'aliases', '$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND FIND_IN_SET(\'bestalias\', $ME.flags)'); + $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] = array('left', 'aliases', '$ME.id = $UID AND $ME.type = \'a_vie\''); + $joins['al' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'aliases', '$ME.id = $UID AND $ME.type = \'a_vie\''); } else { - $joins['al' . $sub] = array('left', 'aliases', XDB::format('$ME.id = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND $ME.alias = {?}', $key)); + $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] = array('left', 'virtual', '$ME.type = \'user\''); + $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', '$ME.type = \'user\''); } else { - $joins['v' . $sub] = array('left', 'virtual', XDB::format('$ME.type = \'user\' AND $ME.alias = {?}', $key)); + $joins['v' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'virtual', XDB::format('$ME.type = \'user\' AND $ME.alias = {?}', $key)); } - $joins['vr' . $sub] = array('left', 'virtual_redirect', XDB::format('$ME.vid = v' . $sub . '.vid + $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))', @@ -1649,17 +2044,43 @@ class UserFilter /** ADDRESSES */ - private $pa = false; + private $with_pa = false; public function addAddressFilter() { - $this->pa = true; + $this->requireProfiles(); + $this->with_pa = true; + return 'pa'; + } + + private $with_pac = false; + public function addAddressCountryFilter() + { + $this->requireProfiles(); + $this->addAddressFilter(); + $this->with_pac = true; + return 'gc'; } - private function addressJoins() + private $with_pal = true; + public function addAddressLocalityFilter() + { + $this->requireProfiles(); + $this->addAddressFilter(); + $this->with_pal = true; + return 'gl'; + } + + protected function addressJoins() { $joins = array(); - if ($this->pa) { - $joins['pa'] = array('left', 'profile_address', '$ME.pid = $PID'); + if ($this->with_pa) { + $joins['pa'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_address', '$ME.pid = $PID'); + } + if ($this->with_pac) { + $joins['gc'] = new PlSqlJoin(PlSqlJoin::MODE_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'); } return $joins; } @@ -1673,6 +2094,7 @@ class UserFilter private $pcr = false; public function addCorpsFilter($type) { + $this->requireProfiles(); $this->pc = true; if ($type == UFC_Corps::CURRENT) { $pce['pcec'] = 'current_corpsid'; @@ -1685,22 +2107,23 @@ class UserFilter public function addCorpsRankFilter() { + $this->requireProfiles(); $this->pc = true; $this->pcr = true; return 'pcr'; } - private function corpsJoins() + protected function corpsJoins() { $joins = array(); if ($this->pc) { - $joins['pc'] = array('left', 'profile_corps', '$ME.uid = $UID'); + $joins['pc'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps', '$ME.uid = $UID'); } if ($this->pcr) { - $joins['pcr'] = array('left', 'profile_corps_rank_enum', '$ME.id = pc.rankid'); + $joins['pcr'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_rank_enum', '$ME.id = pc.rankid'); } foreach($this->pce as $sub => $field) { - $joins[$sub] = array('left', 'profile_corps_enum', '$ME.id = pc.' . $field); + $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_corps_enum', '$ME.id = pc.' . $field); } return $joins; } @@ -1713,6 +2136,7 @@ class UserFilter const JOB_SUBSUBSECTOR = 4; const JOB_ALTERNATES = 8; const JOB_USERDEFINED = 16; + const JOB_CV = 32; /** Joins : * pj => profile_job @@ -1731,6 +2155,7 @@ class UserFilter public function addJobFilter() { + $this->requireProfiles(); $this->with_pj = true; return 'pj'; } @@ -1760,26 +2185,26 @@ class UserFilter } } - private function jobJoins() + protected function jobJoins() { $joins = array(); if ($this->with_pj) { - $joins['pj'] = array('left', 'profile_job', '$ME.uid = $UID'); + $joins['pj'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job', '$ME.uid = $UID'); } if ($this->with_pje) { - $joins['pje'] = array('left', 'profile_job_enum', '$ME.id = pj.jobid'); + $joins['pje'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_enum', '$ME.id = pj.jobid'); } if ($this->with_pjse) { - $joins['pjse'] = array('left', 'profile_job_sector_enum', '$ME.id = pj.sectorid'); + $joins['pjse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_sector_enum', '$ME.id = pj.sectorid'); } if ($this->with_pjsse) { - $joins['pjsse'] = array('left', 'profile_job_subsector_enum', '$ME.id = pj.subsectorid'); + $joins['pjsse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_subsector_enum', '$ME.id = pj.subsectorid'); } if ($this->with_pjssse) { - $joins['pjssse'] = array('left', 'profile_job_subsubsector_enum', '$ME.id = pj.subsubsectorid'); + $joins['pjssse'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_subsubsector_enum', '$ME.id = pj.subsubsectorid'); } if ($this->with_pja) { - $joins['pja'] = array('left', 'profile_job_alternates', '$ME.subsubsectorid = pj.subsubsectorid'); + $joins['pja'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_job_alternates', '$ME.subsubsectorid = pj.subsubsectorid'); } return $joins; } @@ -1790,15 +2215,16 @@ class UserFilter private $with_pnw = false; public function addNetworkingFilter() { + $this->requireAccounts(); $this->with_pnw = true; return 'pnw'; } - private function networkingJoins() + protected function networkingJoins() { $joins = array(); if ($this->with_pnw) { - $joins['pnw'] = array('left', 'profile_networking', '$ME.uid = $UID'); + $joins['pnw'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_networking', '$ME.uid = $UID'); } return $joins; } @@ -1806,19 +2232,20 @@ class UserFilter /** PHONE */ - private $with_phone = false; + private $with_ptel = false; public function addPhoneFilter() { - $this->with_phone = true; + $this->requireAccounts(); + $this->with_ptel = true; return 'ptel'; } - private function phoneJoins() + protected function phoneJoins() { $joins = array(); - if ($this->with_phone) { - $joins['ptel'] = array('left', 'profile_phone', '$ME.uid = $UID'); + if ($this->with_ptel) { + $joins['ptel'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_phones', '$ME.uid = $UID'); } return $joins; } @@ -1826,18 +2253,19 @@ class UserFilter /** MEDALS */ - private $with_medals = false; + private $with_pmed = false; public function addMedalFilter() { - $this->with_medals = true; + $this->requireProfiles(); + $this->with_pmed = true; return 'pmed'; } - private function medalJoins() + protected function medalJoins() { $joins = array(); - if ($this->with_medals) { - $joins['pmed'] = array('left', 'profile_medals_sub', '$ME.uid = $UID'); + if ($this->with_pmed) { + $joins['pmed'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_medals_sub', '$ME.uid = $UID'); } return $joins; } @@ -1852,6 +2280,7 @@ class UserFilter public function addMentorFilter($type) { + $this->requireAccounts(); switch($type) { case MENTOR_EXPERTISE: $pms['pme'] = 'profile_mentor'; @@ -1867,11 +2296,11 @@ class UserFilter } } - private function mentorJoins() + protected function mentorJoins() { $joins = array(); foreach ($this->pms as $sub => $tab) { - $joins[$sub] = array('left', $tab, '$ME.uid = $UID'); + $joins[$sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, $tab, '$ME.uid = $UID'); } return $joins; } @@ -1881,17 +2310,18 @@ class UserFilter private $cts = array(); public function addContactFilter($uid = null) { + $this->requireAccounts(); return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid); } - private function contactJoins() + protected function contactJoins() { $joins = array(); foreach ($this->cts as $sub=>$key) { if (is_null($key)) { - $joins['c' . $sub] = array('left', 'contacts', '$ME.contact = $UID'); + $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', '$ME.contact = $UID'); } else { - $joins['c' . $sub] = array('left', 'contacts', XDB::format('$ME.uid = {?} AND $ME.contact = $UID', substr($key, 5))); + $joins['c' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'contacts', XDB::format('$ME.uid = {?} AND $ME.contact = $UID', substr($key, 5))); } } return $joins; @@ -1903,50 +2333,53 @@ class UserFilter private $wn = array(); public function addWatchRegistrationFilter($uid = null) { + $this->requireAccounts(); return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid); } private $wp = array(); public function addWatchPromoFilter($uid = null) { + $this->requireAccounts(); return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid); } private $w = array(); public function addWatchFilter($uid = null) { + $this->requireAccounts(); return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid); } - private function watchJoins() + protected function watchJoins() { $joins = array(); foreach ($this->w as $sub=>$key) { if (is_null($key)) { - $joins['w' . $sub] = array('left', 'watch'); + $joins['w' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch'); } else { - $joins['w' . $sub] = array('left', 'watch', XDB::format('$ME.uid = {?}', substr($key, 5))); + $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] = array('left', 'watch_nonins', '$ME.ni_id = $UID'); + $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', '$ME.ni_id = $UID'); } else { - $joins['wn' . $sub] = array('left', 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5))); + $joins['wn' . $sub] = 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] = array('left', 'watch_nonins', '$ME.ni_id = $UID'); + $joins['wn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_nonins', '$ME.ni_id = $UID'); } else { - $joins['wn' . $sub] = array('left', 'watch_nonins', XDB::format('$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5))); + $joins['wn' . $sub] = 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] = array('left', 'watch_promo'); + $joins['wp' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_promo'); } else { - $joins['wp' . $sub] = array('left', 'watch_promo', XDB::format('$ME.uid = {?}', substr($key, 5))); + $joins['wp' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'watch_promo', XDB::format('$ME.uid = {?}', substr($key, 5))); } } return $joins;