Improve UFC_Nationality, adds UFC_Binets, UFC_Formation

[platal.git] / classes / userfilter.php

<?php
/***************************************************************************
 *  Copyright (C) 2003-2009 Polytechnique.org                              *
 *  http://opensource.polytechnique.org/                                   *
 *                                                                         *
 *  This program is free software; you can redistribute it and/or modify   *
 *  it under the terms of the GNU General Public License as published by   *
 *  the Free Software Foundation; either version 2 of the License, or      *
 *  (at your option) any later version.                                    *
 *                                                                         *
 *  This program is distributed in the hope that it will be useful,        *
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
 *  GNU General Public License for more details.                           *
 *                                                                         *
 *  You should have received a copy of the GNU General Public License      *
 *  along with this program; if not, write to the Free Software            *
 *  Foundation, Inc.,                                                      *
 *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
 ***************************************************************************/


/******************
 * 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 extends PlFilterCondition
{
}
// }}}

// {{{ class UFC_Profile
/** Filters users who have a profile
 */
class UFC_Profile implements UserFilterCondition
{
    public function buildCondition(PlFilter &$uf)
    {
        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
{

    private $grade;
    private $promo;
    private $comparison;

    public function __construct($comparison, $grade, $promo)
    {
        $this->grade = $grade;
        $this->comparison = $comparison;
        $this->promo = $promo;
        if ($this->grade != UserFilter::DISPLAY) {
            UserFilter::assertGrade($this->grade);
        }
    }

    public function buildCondition(PlFilter &$uf)
    {
        if ($this->grade == UserFilter::DISPLAY) {
            $sub = $uf->addDisplayFilter();
            return XDB::format('pd' . $sub . '.promo ' . $this->comparison . ' {?}', $this->promo);
        } else {
            $sub = $uf->addEducationFilter(true, $this->grade);
            $field = 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
            return $field . ' IS NOT NULL AND ' . $field . ' ' . $this->comparison . ' ' . XDB::format('{?}', $this->promo);
        }
    }
}
// }}}

// {{{ 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...)
 * @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;
    const SUFFIX   = 2;
    const PARTICLE = 7;
    const VARIANTS = 8;
    const CONTAINS = 3;

    private $type;
    private $text;
    private $mode;

    public function __construct($type, $text, $mode)
    {
        $this->type = $type;
        $this->text = $text;
        $this->mode = $mode;
    }

    private function buildNameQuery($type, $variant, $where, UserFilter &$uf)
    {
        $sub = $uf->addNameFilter($type, $variant);
        return str_replace('$ME', 'pn' . $sub, $where);
    }

    public function buildCondition(PlFilter &$uf)
    {
        $left = '$ME.name';
        $op   = ' LIKE ';
        if (($this->mode & self::PARTICLE) == self::PARTICLE) {
            $left = 'CONCAT($ME.particle, \' \', $ME.name)';
        }
        if (($this->mode & self::CONTAINS) == 0) {
            $right = XDB::format('{?}', $this->text);
            $op    = ' = ';
        } else if (($this->mode & self::CONTAINS) == self::PREFIX) {
            $right = XDB::format('CONCAT({?}, \'%\')', $this->text);
        } else if (($this->mode & self::CONTAINS) == self::SUFFIX) {
            $right = XDB::format('CONCAT(\'%\', {?})', $this->text);
        } else {
            $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text);
        }
        $cond = $left . $op . $right;
        $conds = array($this->buildNameQuery($this->type, null, $cond, $uf));
        if (($this->mode & self::VARIANTS) != 0 && isset(UserFilter::$name_variants[$this->type])) {
            foreach (UserFilter::$name_variants[$this->type] as $var) {
                $conds[] = $this->buildNameQuery($this->type, $var, $cond, $uf);
            }
        }
        return implode(' OR ', $conds);
    }
}
// }}}

// {{{ 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
 * @param $date Date to which death date should be compared
 */
class UFC_Dead implements UserFilterCondition
{
    private $comparison;
    private $date;

    public function __construct($comparison = null, $date = null)
    {
        $this->comparison = $comparison;
        $this->date = $date;
    }

    public function buildCondition(PlFilter &$uf)
    {
        $str = 'p.deathdate IS NOT NULL';
        if (!is_null($this->comparison)) {
            $str .= ' AND p.deathdate ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date));
        }
        return $str;
    }
}
// }}}

// {{{ 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;
    private $comparison;
    private $date;

    public function __construct($active = false, $comparison = null, $date = null)
    {
        $this->active = $active;
        $this->comparison = $comparison;
        $this->date = $date;
    }

    public function buildCondition(PlFilter &$uf)
    {
        if ($this->active) {
            $date = 'a.uid IS NOT NULL AND a.state = \'active\'';
        } else {
            $date = 'a.uid IS NOT NULL AND a.state != \'pending\'';
        }
        if (!is_null($this->comparison)) {
            $date .= ' AND a.registration_date ' . $this->comparison . ' ' . XDB::format('{?}', date('Y-m-d', $this->date));
        }
        return $date;
    }
}
// }}}

// {{{ class UFC_ProfileUpdated
/** 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(PlFilter &$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(PlFilter &$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;
    }

    public function buildCondition(PlFilter &$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(PlFilter &$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_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
 */
class UFC_Email implements UserFilterCondition
{
    private $email;
    public function __construct($email)
    {
        $this->email = $email;
    }

    public function buildCondition(PlFilter &$uf)
    {
        if (User::isForeignEmailAddress($this->email)) {
            $sub = $uf->addEmailRedirectFilter($this->email);
            return XDB::format('e' . $sub . '.email IS NOT NULL OR a.email = {?}', $this->email);
        } else if (User::isVirtualEmailAddress($this->email)) {
            $sub = $uf->addVirtualEmailFilter($this->email);
            return 'vr' . $sub . '.redirect IS NOT NULL';
        } else {
            @list($user, $domain) = explode('@', $this->email);
            $sub = $uf->addAliasFilter($user);
            return 'al' . $sub . '.alias IS NOT NULL';
        }
    }
}
// }}}

// {{{ class UFC_EmailList
/** Filters users based on an email list
 * @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(PlFilter &$uf)
    {
        $email   = null;
        $virtual = null;
        $alias   = null;
        $cond = array();

        if (count($this->emails) == 0) {
            return PlFilterCondition::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
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_AddressText extends UFC_Address
{
    /** Flags for text search
     */
    const PREFIX    = 0x0001;
    const SUFFIX    = 0x0002;
    const CONTAINS  = 0x0003;

    private $text;
    private $textSearchMode;

    public function __construct($text = null, $textSearchMode = self::CONTAINS,
        $type = null, $flags = self::FLAG_ANY, $country = null, $locality = null)
    {
        parent::__construct($type, $flags);
        $this->text           = $text;
        $this->textSearchMode = $textSearchMode;
        $this->country        = $country;
        $this->locality       = $locality;
    }

    private function mkMatch($txt)
    {
        $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);
        }
        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);
    }
}
// }}}

// {{{ 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(PlFilter &$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(PlFilter &$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(PlFilter &$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(PlFilter &$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 = 63;
    const SECTORIZATION = 15;

    private $description;
    private $fields;

    public function __construct($description, $fields)
    {
        $this->fields = $fields;
        $this->description = $description;
    }

    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);
        }
        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(PlFilter &$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.func.inc.php');
        $this->number = $number;
        $this->num_type = $num_type;
        $this->phone_type = format_phone_number($phone_type);
    }

    public function buildCondition(PlFilter &$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(PlFilter &$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(PlFilter &$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(PlFilter &$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(PlFilter &$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(PlFilter &$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(PlFilter &$uf)
    {
        if (!$this->user->watch('registration')) {
            return PlFilterCondition::COND_FALSE;
        }
        $uids = $this->user->watchUsers();
        if (count($uids) == 0) {
            return PlFilterCondition::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(PlFilter &$uf)
    {
        $promos = $this->user->watchPromos();
        if (count($promos) == 0) {
            return PlFilterCondition::COND_FALSE;
        } else {
            $sube = $uf->addEducationFilter(true, $this->grade);
            $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade);
            return $field . ' IN ' . XDB::formatArray($promos);
        }
    }
}
// }}}

// {{{ class UFC_WatchContact
/** Filters users watched by selected user
 */
class UFC_WatchContact extends UFC_Contact
{
    public function buildCondition(PlFilter &$uf)
    {
        if (!$this->user->watchContacts()) {
            return PlFilterCondition::COND_FALSE;
        }
        return parent::buildCondition($uf);
    }
}
// }}}


/******************
 * ORDERS
 ******************/

// {{{ class UserFilterOrder
/** Base class for ordering results of a query.
 * Parameters for the ordering must be given to the constructor ($desc for a
 *     descending order).
 * The getSortTokens function is used to get actual ordering part of the query.
 */
abstract class UserFilterOrder extends PlFilterOrder
{
    /** 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;
    }

    protected function getSortTokens(UserFilter &$uf)
    {
        if (UserFilter::isGrade($this->grade)) {
            $sub = $uf->addEducationFilter($this->grade);
            return 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
        } else {
            $sub = $uf->addDisplayFilter();
            return 'pd' . $sub . '.promo';
        }
    }
}
// }}}

// {{{ 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;
    private $variant;
    private $particle;

    public function __construct($type, $variant = null, $particle = false, $desc = false)
    {
        parent::__construct($desc);
        $this->type = $type;
        $this->variant = $variant;
        $this->particle = $particle;
    }

    protected function getSortTokens(UserFilter &$uf)
    {
        if (UserFilter::isDisplayName($this->type)) {
            $sub = $uf->addDisplayFilter();
            return 'pd' . $sub . '.' . $this->type;
        } else {
            $sub = $uf->addNameFilter($this->type, $this->variant);
            if ($this->particle) {
                return 'CONCAT(pn' . $sub . '.particle, \' \', pn' . $sub . '.name)';
            } else {
                return 'pn' . $sub . '.name';
            }
        }
    }
}
// }}}

// {{{ 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
 */
class UFO_Registration extends UserFilterOrder
{
    protected function getSortTokens(UserFilter &$uf)
    {
        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 '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';
    }
}
// }}}


/***********************************
  *********************************
          USER FILTER CLASS
  *********************************
 ***********************************/

// {{{ 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
{
    protected $joinMethods = array();

    protected $joinMetas = array('$PID' => 'p.pid',
                                 '$UID' => 'a.uid',
                                );

    private $root;
    private $sort = array();
    private $query = null;
    private $orderby = null;

    private $lastcount = null;

    public function __construct($cond = null, $sort = null)
    {
        if (empty($this->joinMethods)) {
            $class = new ReflectionClass('UserFilter');
            foreach ($class->getMethods() as $method) {
                $name = $method->getName();
                if (substr($name, -5) == 'Joins' && $name != 'buildJoins') {
                    $this->joinMethods[] = $name;
                }
            }
        }
        if (!is_null($cond)) {
            if ($cond instanceof PlFilterCondition) {
                $this->setCondition($cond);
            }
        }
        if (!is_null($sort)) {
            if ($sort instanceof UserFilterOrder) {
                $this->addSort($sort);
            } else if (is_array($sort)) {
                foreach ($sort as $s) {
                    $this->addSort($s);
                }
            }
        }
    }

    private function buildQuery()
    {
        if (is_null($this->orderby)) {
            $orders = array();
            foreach ($this->sort as $sort) {
                $orders = array_merge($orders, $sort->buildSort($this));
            }
            if (count($orders) == 0) {
                $this->orderby = '';
            } else {
                $this->orderby = 'ORDER BY  ' . implode(', ', $orders);
            }
        }
        if (is_null($this->query)) {
            $where = $this->root->buildCondition($this);
            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  ' . $from . '
                               ' . $joins . '
                           WHERE  (' . $where . ')';
        }
    }

    private function getUIDList($uids = null, PlLimit &$limit)
    {
        $this->requireAccounts();
        $this->buildQuery();
        $lim = $limit->getSql();
        $cond = '';
        if (!is_null($uids)) {
            $cond = ' AND a.uid IN ' . XDB::formatArray($uids);
        }
        $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS  a.uid
                                    ' . $this->query . $cond . '
                                   GROUP BY  a.uid
                                    ' . $this->orderby . '
                                    ' . $lim);
        $this->lastcount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
        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;
    }

    /** Check that the user match the given rule.
     */
    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;
    }

    /** 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 filterUsers(array $users, PlLimit &$limit)
    {
        $this->requireAccounts();
        $this->buildQuery();
        $table = array();
        $uids  = array();
        foreach ($users as $user) {
            if ($user instanceof PlUser) {
                $uid = $user->id();
            } else {
                $uid = $user;
            }
            $uids[] = $uid;
            $table[$uid] = $user;
        }
        $fetched = $this->getUIDList($uids, $limit);
        $output = array();
        foreach ($fetched as $uid) {
            $output[] = $table[$uid];
        }
        return $output;
    }

    /** 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 Profile::getBulkProfilesWithPIDs($this->getPIDs($limit));
    }

    public function get(PlLimit &$limit)
    {
        return $this->getUsers($limit);
    }

    public function getTotalCount()
    {
        if (is_null($this->lastcount)) {
            $this->buildQuery();
            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(PlFilterCondition &$cond)
    {
        $this->root =& $cond;
        $this->query = null;
    }

    public function addSort(PlFilterOrder &$sort)
    {
        $this->sort[] = $sort;
        $this->orderby = null;
    }

    static public function getLegacy($promo_min, $promo_max)
    {
        if ($promo_min != 0) {
            $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min));
        } else {
            $min = new UFC_True();
        }
        if ($promo_max != 0) {
            $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max));
        } else {
            $max = new UFC_True();
        }
        return new UserFilter(new PFC_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;
    }

    /** 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 '';
    }

    protected function displayJoins()
    {
        if ($this->pd) {
            return array('pd' => new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_display', '$ME.pid = $PID'));
        } else {
            return array();
        }
    }

    /** NAMES
     */
    /* name tokens */
    const LASTNAME  = 'lastname';
    const FIRSTNAME = 'firstname';
    const NICKNAME  = 'nickname';
    const PSEUDONYM = 'pseudonym';
    const NAME      = 'name';
    /* name variants */
    const VN_MARITAL  = 'marital';
    const VN_ORDINARY = 'ordinary';
    const VN_OTHER    = 'other';
    const VN_INI      = 'ini';
    /* display names */
    const DN_FULL      = 'directory_name';
    const DN_DISPLAY   = 'yourself';
    const DN_YOURSELF  = 'yourself';
    const DN_DIRECTORY = 'directory_name';
    const DN_PRIVATE   = 'private_name';
    const DN_PUBLIC    = 'public_name';
    const DN_SHORT     = 'short_name';
    const DN_SORT      = 'sort_name';

    static public $name_variants = array(
        self::LASTNAME => array(self::VN_MARITAL, self::VN_ORDINARY),
        self::FIRSTNAME => array(self::VN_ORDINARY, self::VN_INI, self::VN_OTHER)
    );

    static public function assertName($name)
    {
        if (!Profile::getNameTypeId($name)) {
            Platal::page()->kill('Invalid name type: ' . $name);
        }
    }

    static public function isDisplayName($name)
    {
        return $name == self::DN_FULL || $name == self::DN_DISPLAY
            || $name == self::DN_YOURSELF || $name == self::DN_DIRECTORY
            || $name == self::DN_PRIVATE || $name == self::DN_PUBLIC
            || $name == self::DN_SHORT || $name == self::DN_SORT;
    }

    private $pn  = array();
    public function addNameFilter($type, $variant = null)
    {
        $this->requireProfiles();
        if (!is_null($variant)) {
            $ft  = $type . '_' . $variant;
        } else {
            $ft = $type;
        }
        $sub = '_' . $ft;
        self::assertName($ft);

        if (!is_null($variant) && $variant == 'other') {
            $sub .= $this->option++;
        }
        $this->pn[$sub] = Profile::getNameTypeId($ft);
        return $sub;
    }

    protected function nameJoins()
    {
        $joins = array();
        foreach ($this->pn as $sub => $type) {
            $joins['pn' . $sub] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_name', '$ME.pid = $PID AND $ME.typeid = ' . $type);
        }
        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
     */
    const GRADE_ING = 'Ing.';
    const GRADE_PHD = 'PhD';
    const GRADE_MST = 'M%';
    static public function isGrade($grade)
    {
        return $grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST;
    }

    static public function assertGrade($grade)
    {
        if (!self::isGrade($grade)) {
            Platal::page()->killError("Diplôme non valide");
        }
    }

    static public function promoYear($grade)
    {
        // XXX: Definition of promotion for phds and masters might change in near future.
        return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year';
    }

    private $pepe     = array();
    private $with_pee = false;
    public function addEducationFilter($x = false, $grade = null)
    {
        $this->requireProfiles();
        if (!$x) {
            $index = $this->option;
            $sub   = $this->option++;
        } else {
            self::assertGrade($grade);
            $index = $grade;
            $sub   = $grade[0];
            $this->with_pee = true;
        }
        $sub = '_' . $sub;
        $this->pepe[$index] = $sub;
        return $sub;
    }

    protected function educationJoins()
    {
        $joins = array();
        if ($this->with_pee) {
            $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] = 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] = 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;
    }


    /** GROUPS
     */
    private $gpm = array();
    public function addGroupFilter($group = null)
    {
        $this->requireAccounts();
        if (!is_null($group)) {
            if (ctype_digit($group)) {
                $index = $sub = $group;
            } else {
                $index = $group;
                $sub   = self::getDBSuffix($group);
            }
        } else {
            $sub = 'group_' . $this->option++;
            $index = null;
        }
        $sub = '_' . $sub;
        $this->gpm[$sub] = $index;
        return $sub;
    }

    protected function groupJoins()
    {
        $joins = array();
        foreach ($this->gpm as $sub => $key) {
            if (is_null($key)) {
                $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] = 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;
    }

    /** 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);
    }

    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)
    {
        $this->requireAccounts();
        return $this->register_optional($this->al, $alias);
    }

    protected 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->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 $with_pal = true;
    public function addAddressLocalityFilter()
    {
        $this->requireProfiles();
        $this->addAddressFilter();
        $this->with_pal = true;
        return 'gl';
    }

    protected function addressJoins()
    {
        $joins = array();
        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;
    }


    /** CORPS
     */

    private $pc = false;
    private $pce = array();
    private $pcr = false;
    public function addCorpsFilter($type)
    {
        $this->requireProfiles();
        $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->requireProfiles();
        $this->pc = true;
        $this->pcr = true;
        return 'pcr';
    }

    protected 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;
    const JOB_CV = 32;

    /** 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->requireProfiles();
        $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';
        }
    }

    protected 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->requireAccounts();
        $this->with_pnw = true;
        return 'pnw';
    }

    protected 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->requireAccounts();
        $this->with_ptel = true;
        return 'ptel';
    }

    protected function phoneJoins()
    {
        $joins = array();
        if ($this->with_ptel) {
            $joins['ptel'] = new PlSqlJoin(PlSqlJoin::MODE_LEFT, 'profile_phones', '$ME.uid = $UID');
        }
        return $joins;
    }

    /** MEDALS
     */

    private $with_pmed = false;
    public function addMedalFilter()
    {
        $this->requireProfiles();
        $this->with_pmed = true;
        return 'pmed';
    }

    protected function medalJoins()
    {
        $joins = array();
        if ($this->with_pmed) {
            $joins['pmed'] = 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)
    {
        $this->requireAccounts();
        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.");
        }
    }

    protected 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)
    {
        $this->requireAccounts();
        return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid);
    }

    protected 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)
    {
        $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);
    }

    protected 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['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:
?>