classes/userfilter.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2010 Polytechnique.org                              *
   4  *  http://opensource.polytechnique.org/                                   *
   5  *                                                                         *
   6  *  This program is free software; you can redistribute it and/or modify   *
   7  *  it under the terms of the GNU General Public License as published by   *
   8  *  the Free Software Foundation; either version 2 of the License, or      *
   9  *  (at your option) any later version.                                    *
  10  *                                                                         *
  11  *  This program is distributed in the hope that it will be useful,        *
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14  *  GNU General Public License for more details.                           *
  15  *                                                                         *
  16  *  You should have received a copy of the GNU General Public License      *
  17  *  along with this program; if not, write to the Free Software            *
  18  *  Foundation, Inc.,                                                      *
  19  *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20  ***************************************************************************/
  21
  22
  23 /******************
  24  * CONDITIONS
  25  ******************/
  26
  27 // {{{ interface UserFilterCondition
  28 /** This interface describe objects which filter users based
  29  *      on various parameters.
  30  * The parameters of the filter must be given to the constructor.
  31  * The buildCondition function is called by UserFilter when
  32  *     actually building the query. That function must call
  33  *     $uf->addWheteverFilter so that the UserFilter makes
  34  *     adequate joins. It must return the 'WHERE' condition to use
  35  *     with the filter.
  36  */
  37 interface UserFilterCondition extends PlFilterCondition
  38 {
  39 }
  40 // }}}
  41
  42 // {{{ class UFC_HasProfile
  43 /** Filters users who have a profile
  44  */
  45 class UFC_HasProfile implements UserFilterCondition
  46 {
  47     public function buildCondition(PlFilter &$uf)
  48     {
  49         $uf->requireProfiles();
  50         return 'p.pid IS NOT NULL';
  51     }
  52 }
  53 // }}}
  54
  55 // {{{ class UFC_Hruid
  56 /** Filters users based on their hruid
  57  * @param $val Either an hruid, or a list of those
  58  */
  59 class UFC_Hruid implements UserFilterCondition
  60 {
  61     private $hruids;
  62
  63     public function __construct()
  64     {
  65         $this->hruids = pl_flatten(func_get_args());
  66     }
  67
  68     public function buildCondition(PlFilter &$uf)
  69     {
  70         $uf->requireAccounts();
  71         return XDB::format('a.hruid IN {?}', $this->hruids);
  72     }
  73 }
  74 // }}}
  75
  76 // {{{ class UFC_Hrpid
  77 /** Filters users based on the hrpid of their profiles
  78  * @param $val Either an hrpid, or a list of those
  79  */
  80 class UFC_Hrpid implements UserFilterCondition
  81 {
  82     private $hrpids;
  83
  84     public function __construct()
  85     {
  86         $this->hrpids = pl_flatten(func_get_args());
  87     }
  88
  89     public function buildCondition(PlFilter &$uf)
  90     {
  91         $uf->requireProfiles();
  92         return XDB::format('p.hrpid IN {?}', $this->hrpids);
  93     }
  94 }
  95 // }}}
  96
  97 // {{{ class UFC_Ip
  98 /** Filters users based on one of their last IPs
  99  * @param $ip IP from which connection are checked
 100  */
 101 class UFC_Ip implements UserFilterCondition
 102 {
 103     private $ip;
 104
 105     public function __construct($ip)
 106     {
 107         $this->ip = $ip;
 108     }
 109
 110     public function buildCondition(PlFilter &$uf)
 111     {
 112         $sub = $uf->addLoggerFilter();
 113         $ip = ip_to_uint($this->ip);
 114         return XDB::format($sub . '.ip = {?} OR ' . $sub . '.forward_ip = {?}', $ip, $ip);
 115     }
 116 }
 117 // }}}
 118
 119 // {{{ class UFC_Comment
 120 class UFC_Comment implements UserFilterCondition
 121 {
 122     private $text;
 123
 124     public function __construct($text)
 125     {
 126         $this->text = $text;
 127     }
 128
 129     public function buildCondition(PlFilter &$uf)
 130     {
 131         $uf->requireProfiles();
 132         return 'p.freetext ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->text);
 133     }
 134 }
 135 // }}}
 136
 137 // {{{ class UFC_Promo
 138 /** Filters users based on promotion
 139  * @param $comparison Comparison operator (>, =, ...)
 140  * @param $grade Formation on which to restrict, UserFilter::DISPLAY for "any formation"
 141  * @param $promo Promotion on which the filter is based
 142  */
 143 class UFC_Promo implements UserFilterCondition
 144 {
 145
 146     private $grade;
 147     private $promo;
 148     private $comparison;
 149
 150     public function __construct($comparison, $grade, $promo)
 151     {
 152         $this->grade = $grade;
 153         $this->comparison = $comparison;
 154         $this->promo = $promo;
 155         if ($this->grade != UserFilter::DISPLAY) {
 156             UserFilter::assertGrade($this->grade);
 157         }
 158     }
 159
 160     public function buildCondition(PlFilter &$uf)
 161     {
 162         if ($this->grade == UserFilter::DISPLAY) {
 163             $sub = $uf->addDisplayFilter();
 164             return XDB::format('pd' . $sub . '.promo ' . $this->comparison . ' {?}', $this->promo);
 165         } else {
 166             $sub = $uf->addEducationFilter(true, $this->grade);
 167             $field = 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
 168             return $field . ' IS NOT NULL AND ' . $field . ' ' . $this->comparison . ' ' . XDB::format('{?}', $this->promo);
 169         }
 170     }
 171 }
 172 // }}}
 173
 174 // {{{ class UFC_SchoolId
 175 /** Filters users based on their shoold identifier
 176  * @param type Parameter type (Xorg, AX, School)
 177  * @param value School id value
 178  */
 179 class UFC_SchoolId implements UserFilterCondition
 180 {
 181     const AX     = 'ax';
 182     const Xorg   = 'xorg';
 183     const School = 'school';
 184
 185     private $type;
 186     private $id;
 187
 188     static public function assertType($type)
 189     {
 190         if ($type != self::AX && $type != self::Xorg && $type != self::School) {
 191             Platal::page()->killError("Type de matricule invalide: $type");
 192         }
 193     }
 194
 195     public function __construct($type, $id)
 196     {
 197         $this->type = $type;
 198         $this->id   = $id;
 199         self::assertType($type);
 200     }
 201
 202     public function buildCondition(PlFilter &$uf)
 203     {
 204         $uf->requireProfiles();
 205         $id = $this->id;
 206         $type = $this->type;
 207         if ($type == self::School) {
 208             $type = self::Xorg;
 209             $id   = Profile::getXorgId($id);
 210         }
 211         return XDB::format('p.' . $type . '_id = {?}', $id);
 212     }
 213 }
 214 // }}}
 215
 216 // {{{ class UFC_EducationSchool
 217 /** Filters users by formation
 218  * @param $val The formation to search (either ID or array of IDs)
 219  */
 220 class UFC_EducationSchool implements UserFilterCondition
 221 {
 222     private $val;
 223
 224     public function __construct()
 225     {
 226         $this->val = pl_flatten(func_get_args());
 227     }
 228
 229     public function buildCondition(PlFilter &$uf)
 230     {
 231         $sub = $uf->addEducationFilter();
 232         return XDB::format('pe' . $sub . '.eduid IN {?}', $this->val);
 233     }
 234 }
 235 // }}}
 236
 237 // {{{ class UFC_EducationDegree
 238 class UFC_EducationDegree implements UserFilterCondition
 239 {
 240     private $diploma;
 241
 242     public function __construct()
 243     {
 244         $this->diploma = pl_flatten(func_get_args());
 245     }
 246
 247     public function buildCondition(PlFilter &$uf)
 248     {
 249         $sub = $uf->addEducationFilter();
 250         return XDB::format('pe' . $sub . '.degreeid IN {?}', $this->diploma);
 251     }
 252 }
 253 // }}}
 254
 255 // {{{ class UFC_EducationField
 256 class UFC_EducationField implements UserFilterCondition
 257 {
 258     private $val;
 259
 260     public function __construct()
 261     {
 262         $this->val = pl_flatten(func_get_args());
 263     }
 264
 265     public function buildCondition(PlFilter &$uf)
 266     {
 267         $sub = $uf->addEducationFilter();
 268         return XDB::format('pe' . $sub . '.fieldid IN {?}', $this->val);
 269     }
 270 }
 271 // }}}
 272
 273 // {{{ class UFC_Name
 274 /** Filters users based on name
 275  * @param $type Type of name field on which filtering is done (firstname, lastname...)
 276  * @param $text Text on which to filter
 277  * @param $mode Flag indicating search type (prefix, suffix, with particule...)
 278  */
 279 class UFC_Name implements UserFilterCondition
 280 {
 281     const EXACT    = XDB::WILDCARD_EXACT;    // 0x000
 282     const PREFIX   = XDB::WILDCARD_PREFIX;   // 0x001
 283     const SUFFIX   = XDB::WILDCARD_SUFFIX;   // 0x002
 284     const CONTAINS = XDB::WILDCARD_CONTAINS; // 0x003
 285     const PARTICLE = 0x004;
 286     const VARIANTS = 0x008;
 287
 288     private $type;
 289     private $text;
 290     private $mode;
 291
 292     public function __construct($type, $text, $mode)
 293     {
 294         $this->type = $type;
 295         $this->text = $text;
 296         $this->mode = $mode;
 297     }
 298
 299     private function buildNameQuery($type, $variant, $where, UserFilter &$uf)
 300     {
 301         $sub = $uf->addNameFilter($type, $variant);
 302         return str_replace('$ME', 'pn' . $sub, $where);
 303     }
 304
 305     public function buildCondition(PlFilter &$uf)
 306     {
 307         $left = '$ME.name';
 308         if (($this->mode & self::PARTICLE) == self::PARTICLE) {
 309             $left = 'CONCAT($ME.particle, \' \', $ME.name)';
 310         }
 311         $right = XDB::formatWildcards($this->mode & self::CONTAINS, $this->text);
 312
 313         $cond = $left . $right;
 314         $conds = array($this->buildNameQuery($this->type, null, $cond, $uf));
 315         if (($this->mode & self::VARIANTS) != 0 && isset(Profile::$name_variants[$this->type])) {
 316             foreach (Profile::$name_variants[$this->type] as $var) {
 317                 $conds[] = $this->buildNameQuery($this->type, $var, $cond, $uf);
 318             }
 319         }
 320         return implode(' OR ', $conds);
 321     }
 322 }
 323 // }}}
 324
 325 // {{{ class UFC_NameTokens
 326 /** Selects users based on tokens in their name (for quicksearch)
 327  * @param $tokens An array of tokens to search
 328  * @param $flags Flags the tokens must have (e.g 'public' for public search)
 329  * @param $soundex (bool) Whether those tokens are fulltext or soundex
 330  */
 331 class UFC_NameTokens implements UserFilterCondition
 332 {
 333     /* Flags */
 334     const FLAG_PUBLIC = 'public';
 335
 336     private $tokens;
 337     private $flags;
 338     private $soundex;
 339     private $exact;
 340
 341     public function __construct($tokens, $flags = array(), $soundex = false, $exact = false)
 342     {
 343         if (is_array($tokens)) {
 344             $this->tokens = $tokens;
 345         } else {
 346             $this->tokens = array($tokens);
 347         }
 348         if (is_array($flags)) {
 349             $this->flags = $flags;
 350         } else {
 351             $this->flags = array($flags);
 352         }
 353         $this->soundex = $soundex;
 354         $this->exact = $exact;
 355     }
 356
 357     public function buildCondition(PlFilter &$uf)
 358     {
 359         $sub = $uf->addNameTokensFilter(!($this->exact || $this->soundex));
 360         $conds = array();
 361         if ($this->soundex) {
 362             $conds[] = XDB::format($sub . '.soundex IN {?}', $this->tokens);
 363         } else if ($this->exact) {
 364             $conds[] = XDB::format($sub . '.token IN {?}', $this->tokens);
 365         } else {
 366             $tokconds = array();
 367             foreach ($this->tokens as $token) {
 368                 $tokconds[] = $sub . '.token ' . XDB::formatWildcards(XDB::WILDCARD_PREFIX, $token);
 369             }
 370             $conds[] = implode(' OR ', $tokconds);
 371         }
 372
 373         if ($this->flags != null) {
 374             $conds[] = XDB::format($sub . '.flags IN {?}', $this->flags);
 375         }
 376
 377         return implode(' AND ', $conds);
 378     }
 379 }
 380 // }}}
 381
 382 // {{{ class UFC_Nationality
 383 class UFC_Nationality implements UserFilterCondition
 384 {
 385     private $val;
 386
 387     public function __construct()
 388     {
 389         $this->val = pl_flatten(func_get_args());
 390     }
 391
 392     public function buildCondition(PlFilter &$uf)
 393     {
 394         $uf->requireProfiles();
 395         $nat = XDB::formatArray($this->val);
 396         $conds = array(
 397             'p.nationality1 IN ' . $nat,
 398             'p.nationality2 IN ' . $nat,
 399             'p.nationality3 IN ' . $nat,
 400         );
 401         return implode(' OR ', $conds);
 402     }
 403 }
 404 // }}}
 405
 406 // {{{ class UFC_Dead
 407 /** Filters users based on death date
 408  * @param $comparison Comparison operator
 409  * @param $date Date to which death date should be compared (DateTime object, string or timestamp)
 410  */
 411 class UFC_Dead implements UserFilterCondition
 412 {
 413     private $comparison;
 414     private $date;
 415
 416     public function __construct($comparison = null, $date = null)
 417     {
 418         $this->comparison = $comparison;
 419         $this->date = make_datetime($date);
 420     }
 421
 422     public function buildCondition(PlFilter &$uf)
 423     {
 424         $uf->requireProfiles();
 425         $str = 'p.deathdate IS NOT NULL';
 426         if (!is_null($this->comparison)) {
 427             $str .= ' AND p.deathdate ' . $this->comparison . ' ' . XDB::format('{?}', $this->date->format('Y-m-d'));
 428         }
 429         return $str;
 430     }
 431 }
 432 // }}}
 433
 434 // {{{ class UFC_Registered
 435 /** Filters users based on registration state
 436  * @param $active Whether we want to use only "active" users (i.e with a valid redirection)
 437  * @param $comparison Comparison operator
 438  * @param $date Date to which users registration date should be compared
 439  */
 440 class UFC_Registered implements UserFilterCondition
 441 {
 442     private $active;
 443     private $comparison;
 444     private $date;
 445
 446     public function __construct($active = false, $comparison = null, $date = null)
 447     {
 448         $this->active = $active;
 449         $this->comparison = $comparison;
 450         $this->date = make_datetime($date);
 451     }
 452
 453     public function buildCondition(PlFilter &$uf)
 454     {
 455         $uf->requireAccounts();
 456         if ($this->active) {
 457             $date = 'a.uid IS NOT NULL AND a.state = \'active\'';
 458         } else {
 459             $date = 'a.uid IS NOT NULL AND a.state != \'pending\'';
 460         }
 461         if (!is_null($this->comparison)) {
 462             $date .= ' AND a.registration_date != \'0000-00-00 00:00:00\' AND a.registration_date ' . $this->comparison . ' ' . XDB::format('{?}', $this->date->format('Y-m-d'));
 463         }
 464         return $date;
 465     }
 466 }
 467 // }}}
 468
 469 // {{{ class UFC_ProfileUpdated
 470 /** Filters users based on profile update date
 471  * @param $comparison Comparison operator
 472  * @param $date Date to which profile update date must be compared
 473  */
 474 class UFC_ProfileUpdated implements UserFilterCondition
 475 {
 476     private $comparison;
 477     private $date;
 478
 479     public function __construct($comparison = null, $date = null)
 480     {
 481         $this->comparison = $comparison;
 482         $this->date = $date;
 483     }
 484
 485     public function buildCondition(PlFilter &$uf)
 486     {
 487         $uf->requireProfiles();
 488         return 'p.last_change ' . $this->comparison . XDB::format(' {?}', date('Y-m-d H:i:s', $this->date));
 489     }
 490 }
 491 // }}}
 492
 493 // {{{ class UFC_Birthday
 494 /** Filters users based on next birthday date
 495  * @param $comparison Comparison operator
 496  * @param $date Date to which users next birthday date should be compared
 497  */
 498 class UFC_Birthday implements UserFilterCondition
 499 {
 500     private $comparison;
 501     private $date;
 502
 503     public function __construct($comparison = null, $date = null)
 504     {
 505         $this->comparison = $comparison;
 506         $this->date = $date;
 507     }
 508
 509     public function buildCondition(PlFilter &$uf)
 510     {
 511         $uf->requireProfiles();
 512         return 'p.next_birthday ' . $this->comparison . XDB::format(' {?}', date('Y-m-d', $this->date));
 513     }
 514 }
 515 // }}}
 516
 517 // {{{ class UFC_Sex
 518 /** Filters users based on sex
 519  * @parm $sex One of User::GENDER_MALE or User::GENDER_FEMALE, for selecting users
 520  */
 521 class UFC_Sex implements UserFilterCondition
 522 {
 523     private $sex;
 524     public function __construct($sex)
 525     {
 526         $this->sex = $sex;
 527     }
 528
 529     public function buildCondition(PlFilter &$uf)
 530     {
 531         if ($this->sex != User::GENDER_MALE && $this->sex != User::GENDER_FEMALE) {
 532             return self::COND_FALSE;
 533         } else {
 534             $uf->requireProfiles();
 535             return XDB::format('p.sex = {?}', $this->sex == User::GENDER_FEMALE ? 'female' : 'male');
 536         }
 537     }
 538 }
 539 // }}}
 540
 541 // {{{ class UFC_Group
 542 /** Filters users based on group membership
 543  * @param $group Group whose members we are selecting
 544  * @param $anim Whether to restrict selection to animators of that group
 545  */
 546 class UFC_Group implements UserFilterCondition
 547 {
 548     private $group;
 549     private $anim;
 550     public function __construct($group, $anim = false)
 551     {
 552         $this->group = $group;
 553         $this->anim = $anim;
 554     }
 555
 556     public function buildCondition(PlFilter &$uf)
 557     {
 558         $sub = $uf->addGroupFilter($this->group);
 559         $where = 'gpm' . $sub . '.perms IS NOT NULL';
 560         if ($this->anim) {
 561             $where .= ' AND gpm' . $sub . '.perms = \'admin\'';
 562         }
 563         return $where;
 564     }
 565 }
 566 // }}}
 567
 568 // {{{ class UFC_Binet
 569 /** Selects users based on their belonging to a given (list of) binet
 570  * @param $binet either a binet_id or an array of binet_ids
 571  */
 572 class UFC_Binet implements UserFilterCondition
 573 {
 574     private $val;
 575
 576     public function __construct()
 577     {
 578         $this->val = pl_flatten(func_get_args());
 579     }
 580
 581     public function buildCondition(PlFilter &$uf)
 582     {
 583         $sub = $uf->addBinetsFilter();
 584         return XDB::format($sub . '.binet_id IN {?}', $this->val);
 585     }
 586 }
 587 // }}}
 588
 589 // {{{ class UFC_Section
 590 /** Selects users based on section
 591  * @param $section ID of the section
 592  */
 593 class UFC_Section implements UserFilterCondition
 594 {
 595     private $section;
 596
 597     public function __construct()
 598     {
 599         $this->section = pl_flatten(func_get_args());
 600     }
 601
 602     public function buildCondition(PlFilter &$uf)
 603     {
 604         $uf->requireProfiles();
 605         return XDB::format('p.section IN {?}', $this->section);
 606     }
 607 }
 608 // }}}
 609
 610 // {{{ class UFC_Email
 611 /** Filters users based on an email or a list of emails
 612  * @param $emails List of emails whose owner must be selected
 613  */
 614 class UFC_Email implements UserFilterCondition
 615 {
 616     private $emails;
 617     public function __construct()
 618     {
 619         $this->emails = pl_flatten(func_get_args());
 620     }
 621
 622     public function buildCondition(PlFilter &$uf)
 623     {
 624         $foreign = array();
 625         $virtual = array();
 626         $aliases = array();
 627         $cond = array();
 628
 629         if (count($this->emails) == 0) {
 630             return PlFilterCondition::COND_TRUE;
 631         }
 632
 633         foreach ($this->emails as $entry) {
 634             if (User::isForeignEmailAddress($entry)) {
 635                 $foreign[] = $entry;
 636             } else if (User::isVirtualEmailAddress($entry)) {
 637                 $virtual[] = $entry;
 638             } else {
 639                 @list($user, $domain) = explode('@', $entry);
 640                 $aliases[] = $user;
 641             }
 642         }
 643
 644         if (count($foreign) > 0) {
 645             $sub = $uf->addEmailRedirectFilter($foreign);
 646             $cond[] = XDB::format('e' . $sub . '.email IS NOT NULL OR a.email IN {?}', $foreign);
 647         }
 648         if (count($virtual) > 0) {
 649             $sub = $uf->addVirtualEmailFilter($virtual);
 650             $cond[] = 'vr' . $sub . '.redirect IS NOT NULL';
 651         }
 652         if (count($aliases) > 0) {
 653             $sub = $uf->addAliasFilter($aliases);
 654             $cond[] = 'al' . $sub . '.alias IS NOT NULL';
 655         }
 656         return '(' . implode(') OR (', $cond) . ')';
 657     }
 658 }
 659 // }}}
 660
 661 // {{{ class UFC_Address
 662 abstract class UFC_Address implements UserFilterCondition
 663 {
 664     /** Valid address type ('hq' is reserved for company addresses)
 665      */
 666     const TYPE_HOME = 1;
 667     const TYPE_PRO  = 2;
 668     const TYPE_ANY  = 3;
 669
 670     /** Text for these types
 671      */
 672     protected static $typetexts = array(
 673         self::TYPE_HOME => 'home',
 674         self::TYPE_PRO  => 'pro',
 675     );
 676
 677     protected $type;
 678
 679     /** Flags for addresses
 680      */
 681     const FLAG_CURRENT = 0x0001;
 682     const FLAG_TEMP    = 0x0002;
 683     const FLAG_SECOND  = 0x0004;
 684     const FLAG_MAIL    = 0x0008;
 685     const FLAG_CEDEX   = 0x0010;
 686
 687     // Binary OR of those flags
 688     const FLAG_ANY     = 0x001F;
 689
 690     /** Text of these flags
 691      */
 692     protected static $flagtexts = array(
 693         self::FLAG_CURRENT => 'current',
 694         self::FLAG_TEMP    => 'temporary',
 695         self::FLAG_SECOND  => 'secondary',
 696         self::FLAG_MAIL    => 'mail',
 697         self::FLAG_CEDEX   => 'cedex',
 698     );
 699
 700     protected $flags;
 701
 702     public function __construct($type = null, $flags = null)
 703     {
 704         $this->type  = $type;
 705         $this->flags = $flags;
 706     }
 707
 708     protected function initConds($sub)
 709     {
 710         $conds = array();
 711         $types = array();
 712         foreach (self::$typetexts as $flag => $type) {
 713             if ($flag & $this->type) {
 714                 $types[] = $type;
 715             }
 716         }
 717         if (count($types)) {
 718             $conds[] = XDB::format($sub . '.type IN {?}', $types);
 719         }
 720
 721         if ($this->flags != self::FLAG_ANY) {
 722             foreach(self::$flagtexts as $flag => $text) {
 723                 if ($flag & $this->flags) {
 724                     $conds[] = 'FIND_IN_SET(' . XDB::format('{?}', $text) . ', ' . $sub . '.flags)';
 725                 }
 726             }
 727         }
 728         return $conds;
 729     }
 730
 731 }
 732 // }}}
 733
 734 // {{{ class UFC_AddressText
 735 /** Select users based on their address, using full text search
 736  * @param $text Text for filter in fulltext search
 737  * @param $textSearchMode Mode for search (one of XDB::WILDCARD_*)
 738  * @param $type Filter on address type
 739  * @param $flags Filter on address flags
 740  * @param $country Filter on address country
 741  * @param $locality Filter on address locality
 742  */
 743 class UFC_AddressText extends UFC_Address
 744 {
 745
 746     private $text;
 747     private $textSearchMode;
 748
 749     public function __construct($text = null, $textSearchMode = XDB::WILDCARD_CONTAINS,
 750         $type = null, $flags = self::FLAG_ANY, $country = null, $locality = null)
 751     {
 752         parent::__construct($type, $flags);
 753         $this->text           = $text;
 754         $this->textSearchMode = $textSearchMode;
 755         $this->country        = $country;
 756         $this->locality       = $locality;
 757     }
 758
 759     private function mkMatch($txt)
 760     {
 761         return XDB::formatWildcards($this->textSearchMode, $txt);
 762     }
 763
 764     public function buildCondition(PlFilter &$uf)
 765     {
 766         $sub = $uf->addAddressFilter();
 767         $conds = $this->initConds($sub);
 768         if ($this->text != null) {
 769             $conds[] = $sub . '.text' . $this->mkMatch($this->text);
 770         }
 771
 772         if ($this->country != null) {
 773             $subc = $uf->addAddressCountryFilter();
 774             $subconds = array();
 775             $subconds[] = $subc . '.country' . $this->mkMatch($this->country);
 776             $subconds[] = $subc . '.countryFR' . $this->mkMatch($this->country);
 777             $conds[] = implode(' OR ', $subconds);
 778         }
 779
 780         if ($this->locality != null) {
 781             $subl = $uf->addAddressLocalityFilter();
 782             $conds[] = $subl . '.name' . $this->mkMatch($this->locality);
 783         }
 784
 785         return implode(' AND ', $conds);
 786     }
 787 }
 788 // }}}
 789
 790 // {{{ class UFC_AddressField
 791 /** Filters users based on their address,
 792  * @param $val Either a code for one of the fields, or an array of such codes
 793  * @param $fieldtype The type of field to look for
 794  * @param $type Filter on address type
 795  * @param $flags Filter on address flags
 796  */
 797 class UFC_AddressField extends UFC_Address
 798 {
 799     const FIELD_COUNTRY    = 1;
 800     const FIELD_ADMAREA    = 2;
 801     const FIELD_SUBADMAREA = 3;
 802     const FIELD_LOCALITY   = 4;
 803     const FIELD_ZIPCODE    = 5;
 804
 805     /** Data of the filter
 806      */
 807     private $val;
 808     private $fieldtype;
 809
 810     public function __construct($val, $fieldtype, $type = null, $flags = self::FLAG_ANY)
 811     {
 812         parent::__construct($type, $flags);
 813
 814         if (!is_array($val)) {
 815             $val = array($val);
 816         }
 817         $this->val       = $val;
 818         $this->fieldtype = $fieldtype;
 819     }
 820
 821     public function buildCondition(PlFilter &$uf)
 822     {
 823         $sub = $uf->addAddressFilter();
 824         $conds = $this->initConds($sub);
 825
 826         switch ($this->fieldtype) {
 827         case self::FIELD_COUNTRY:
 828             $field = 'countryId';
 829             break;
 830         case self::FIELD_ADMAREA:
 831             $field = 'administrativeAreaId';
 832             break;
 833         case self::FIELD_SUBADMAREA:
 834             $field = 'subAdministrativeAreaId';
 835             break;
 836         case self::FIELD_LOCALITY:
 837             $field = 'localityId';
 838             break;
 839         case self::FIELD_ZIPCODE:
 840             $field = 'postalCode';
 841             break;
 842         default:
 843             Platal::page()->killError('Invalid address field type: ' . $this->fieldtype);
 844         }
 845         $conds[] = XDB::format($sub . '.' . $field . ' IN {?}', $this->val);
 846
 847         return implode(' AND ', $conds);
 848     }
 849 }
 850 // }}}
 851
 852 // {{{ class UFC_Corps
 853 /** Filters users based on the corps they belong to
 854  * @param $corps Corps we are looking for (abbreviation)
 855  * @param $type Whether we search for original or current corps
 856  */
 857 class UFC_Corps implements UserFilterCondition
 858 {
 859     const CURRENT   = 1;
 860     const ORIGIN    = 2;
 861
 862     private $corps;
 863     private $type;
 864
 865     public function __construct($corps, $type = self::CURRENT)
 866     {
 867         $this->corps = $corps;
 868         $this->type  = $type;
 869     }
 870
 871     public function buildCondition(PlFilter &$uf)
 872     {
 873         /** Tables shortcuts:
 874          * pc for profile_corps,
 875          * pceo for profile_corps_enum - orginal
 876          * pcec for profile_corps_enum - current
 877          */
 878         $sub = $uf->addCorpsFilter($this->type);
 879         $cond = $sub . '.abbreviation = ' . $corps;
 880         return $cond;
 881     }
 882 }
 883 // }}}
 884
 885 // {{{ class UFC_Corps_Rank
 886 /** Filters users based on their rank in the corps
 887  * @param $rank Rank we are looking for (abbreviation)
 888  */
 889 class UFC_Corps_Rank implements UserFilterCondition
 890 {
 891     private $rank;
 892     public function __construct($rank)
 893     {
 894         $this->rank = $rank;
 895     }
 896
 897     public function buildCondition(PlFilter &$uf)
 898     {
 899         /** Tables shortcuts:
 900          * pcr for profile_corps_rank
 901          */
 902         $sub = $uf->addCorpsRankFilter();
 903         $cond = $sub . '.abbreviation = ' . $rank;
 904         return $cond;
 905     }
 906 }
 907 // }}}
 908
 909 // {{{ class UFC_Job_Company
 910 /** Filters users based on the company they belong to
 911  * @param $type The field being searched (self::JOBID, self::JOBNAME or self::JOBACRONYM)
 912  * @param $value The searched value
 913  */
 914 class UFC_Job_Company implements UserFilterCondition
 915 {
 916     const JOBID = 'id';
 917     const JOBNAME = 'name';
 918     const JOBACRONYM = 'acronym';
 919
 920     private $type;
 921     private $value;
 922
 923     public function __construct($type, $value)
 924     {
 925         $this->assertType($type);
 926         $this->type = $type;
 927         $this->value = $value;
 928     }
 929
 930     private function assertType($type)
 931     {
 932         if ($type != self::JOBID && $type != self::JOBNAME && $type != self::JOBACRONYM) {
 933             Platal::page()->killError("Type de recherche non valide.");
 934         }
 935     }
 936
 937     public function buildCondition(PlFilter &$uf)
 938     {
 939         $sub = $uf->addJobCompanyFilter();
 940         $cond  = $sub . '.' . $this->type . ' = ' . XDB::format('{?}', $this->value);
 941         return $cond;
 942     }
 943 }
 944 // }}}
 945
 946 // {{{ class UFC_Job_Sectorization
 947 /** Filters users based on the ((sub)sub)sector they work in
 948  * @param $val The ID of the sector, or an array of such IDs
 949  * @param $type The kind of search (subsubsector/subsector/sector)
 950  */
 951 class UFC_Job_Sectorization implements UserFilterCondition
 952 {
 953     private $val;
 954     private $type;
 955
 956     public function __construct($val, $type = UserFilter::JOB_SECTOR)
 957     {
 958         self::assertType($type);
 959         if (!is_array($val)) {
 960             $val = array($val);
 961         }
 962         $this->val = $val;
 963         $this->type = $type;
 964     }
 965
 966     private static function assertType($type)
 967     {
 968         if ($type != UserFilter::JOB_SECTOR && $type != UserFilter::JOB_SUBSECTOR && $type != UserFilter::JOB_SUBSUBSECTOR) {
 969             Platal::page()->killError("Type de secteur non valide.");
 970         }
 971     }
 972
 973     public function buildCondition(PlFilter &$uf)
 974     {
 975         $sub = $uf->addJobSectorizationFilter($this->type);
 976         return $sub . '.id = ' . XDB::format('{?}', $this->val);
 977     }
 978 }
 979 // }}}
 980
 981 // {{{ class UFC_Job_Description
 982 /** Filters users based on their job description
 983  * @param $description The text being searched for
 984  * @param $fields The fields to search for (user-defined, ((sub|)sub|)sector)
 985  */
 986 class UFC_Job_Description implements UserFilterCondition
 987 {
 988
 989     private $description;
 990     private $fields;
 991
 992     public function __construct($description, $fields)
 993     {
 994         $this->fields = $fields;
 995         $this->description = $description;
 996     }
 997
 998     public function buildCondition(PlFilter &$uf)
 999     {
1000         $conds = array();
1001         if ($this->fields & UserFilter::JOB_USERDEFINED) {
1002             $sub = $uf->addJobFilter();
1003             $conds[] = $sub . '.description ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description);
1004         }
1005         if ($this->fields & UserFilter::JOB_CV) {
1006             $uf->requireProfiles();
1007             $conds[] = 'p.cv ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description);
1008         }
1009         if ($this->fields & UserFilter::JOB_SECTOR) {
1010             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SECTOR);
1011             $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description);
1012         }
1013         if ($this->fields & UserFilter::JOB_SUBSECTOR) {
1014             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSECTOR);
1015             $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description);
1016         }
1017         if ($this->fields & UserFilter::JOB_SUBSUBSECTOR) {
1018             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSUBSECTOR);
1019             $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description);
1020             $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_ALTERNATES);
1021             $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description);
1022         }
1023         return implode(' OR ', $conds);
1024     }
1025 }
1026 // }}}
1027
1028 // {{{ class UFC_Networking
1029 /** Filters users based on network identity (IRC, ...)
1030  * @param $type Type of network (-1 for any)
1031  * @param $value Value to search
1032  */
1033 class UFC_Networking implements UserFilterCondition
1034 {
1035     private $type;
1036     private $value;
1037
1038     public function __construct($type, $value)
1039     {
1040         $this->type = $type;
1041         $this->value = $value;
1042     }
1043
1044     public function buildCondition(PlFilter &$uf)
1045     {
1046         $sub = $uf->addNetworkingFilter();
1047         $conds = array();
1048         $conds[] = $sub . '.address ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->value);
1049         if ($this->type != -1) {
1050             $conds[] = $sub . '.network_type = ' . XDB::format('{?}', $this->type);
1051         }
1052         return implode(' AND ', $conds);
1053     }
1054 }
1055 // }}}
1056
1057 // {{{ class UFC_Phone
1058 /** Filters users based on their phone number
1059  * @param $num_type Type of number (pro/user/home)
1060  * @param $phone_type Type of phone (fixed/mobile/fax)
1061  * @param $number Phone number
1062  */
1063 class UFC_Phone implements UserFilterCondition
1064 {
1065     const NUM_PRO   = 'pro';
1066     const NUM_USER  = 'user';
1067     const NUM_HOME  = 'address';
1068     const NUM_ANY   = 'any';
1069
1070     const PHONE_FIXED   = 'fixed';
1071     const PHONE_MOBILE  = 'mobile';
1072     const PHONE_FAX     = 'fax';
1073     const PHONE_ANY     = 'any';
1074
1075     private $num_type;
1076     private $phone_type;
1077     private $number;
1078
1079     public function __construct($number, $num_type = self::NUM_ANY, $phone_type = self::PHONE_ANY)
1080     {
1081         require_once('profil.func.inc.php');
1082         $this->number = $number;
1083         $this->num_type = $num_type;
1084         $this->phone_type = format_phone_number($phone_type);
1085     }
1086
1087     public function buildCondition(PlFilter &$uf)
1088     {
1089         $sub = $uf->addPhoneFilter();
1090         $conds = array();
1091         $conds[] = $sub . '.search_tel = ' . XDB::format('{?}', $this->number);
1092         if ($this->num_type != self::NUM_ANY) {
1093             $conds[] = $sub . '.link_type = ' . XDB::format('{?}', $this->num_type);
1094         }
1095         if ($this->phone_type != self::PHONE_ANY) {
1096             $conds[] = $sub . '.tel_type = ' . XDB::format('{?}', $this->phone_type);
1097         }
1098         return implode(' AND ', $conds);
1099     }
1100 }
1101 // }}}
1102
1103 // {{{ class UFC_Medal
1104 /** Filters users based on their medals
1105  * @param $medal ID of the medal
1106  * @param $grade Grade of the medal (null for 'any')
1107  */
1108 class UFC_Medal implements UserFilterCondition
1109 {
1110     private $medal;
1111     private $grade;
1112
1113     public function __construct($medal, $grade = null)
1114     {
1115         $this->medal = $medal;
1116         $this->grade = $grade;
1117     }
1118
1119     public function buildCondition(PlFilter &$uf)
1120     {
1121         $conds = array();
1122         $sub = $uf->addMedalFilter();
1123         $conds[] = $sub . '.mid = ' . XDB::format('{?}', $this->medal);
1124         if ($this->grade != null) {
1125             $conds[] = $sub . '.gid = ' . XDB::format('{?}', $this->grade);
1126         }
1127         return implode(' AND ', $conds);
1128     }
1129 }
1130 // }}}
1131
1132 // {{{ class UFC_Photo
1133 /** Filters profiles with photo
1134  */
1135 class UFC_Photo implements UserFilterCondition
1136 {
1137     public function buildCondition(PlFilter &$uf)
1138     {
1139         $uf->addPhotoFilter();
1140         return 'photo.attach IS NOT NULL';
1141     }
1142 }
1143 // }}}
1144
1145 // {{{ class UFC_Mentor_Expertise
1146 /** Filters users by mentoring expertise
1147  * @param $expertise Domain of expertise
1148  */
1149 class UFC_Mentor_Expertise implements UserFilterCondition
1150 {
1151     private $expertise;
1152
1153     public function __construct($expertise)
1154     {
1155         $this->expertise = $expertise;
1156     }
1157
1158     public function buildCondition(PlFilter &$uf)
1159     {
1160         $sub = $uf->addMentorFilter(UserFilter::MENTOR_EXPERTISE);
1161         return $sub . '.expertise ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->expertise);
1162     }
1163 }
1164 // }}}
1165
1166 // {{{ class UFC_Mentor_Country
1167 /** Filters users by mentoring country
1168  * @param $country Two-letters code of country being searched
1169  */
1170 class UFC_Mentor_Country implements UserFilterCondition
1171 {
1172     private $country;
1173
1174     public function __construct()
1175     {
1176         $this->country = pl_flatten(func_get_args());
1177     }
1178
1179     public function buildCondition(PlFilter &$uf)
1180     {
1181         $sub = $uf->addMentorFilter(UserFilter::MENTOR_COUNTRY);
1182         return $sub . '.country IN ' . XDB::format('{?}', $this->country);
1183     }
1184 }
1185 // }}}
1186
1187 // {{{ class UFC_Mentor_Sectorization
1188 /** Filters users based on mentoring (sub|)sector
1189  * @param $sector ID of (sub)sector
1190  * @param $type Whether we are looking for a sector or a subsector
1191  */
1192 class UFC_Mentor_Sectorization implements UserFilterCondition
1193 {
1194     const SECTOR    = 1;
1195     const SUBSECTOR = 2;
1196     private $sector;
1197     private $type;
1198
1199     public function __construct($sector, $type = self::SECTOR)
1200     {
1201         $this->sector = $sector;
1202         $this->type = $type;
1203     }
1204
1205     public function buildCondition(PlFilter &$uf)
1206     {
1207         $sub = $uf->addMentorFilter(UserFilter::MENTOR_SECTOR);
1208         if ($this->type == self::SECTOR) {
1209             $field = 'sectorid';
1210         } else {
1211             $field = 'subsectorid';
1212         }
1213         return $sub . '.' . $field . ' = ' . XDB::format('{?}', $this->sector);
1214     }
1215 }
1216 // }}}
1217
1218 // {{{ class UFC_UserRelated
1219 /** Filters users based on a relation toward a user
1220  * @param $user User to which searched users are related
1221  */
1222 abstract class UFC_UserRelated implements UserFilterCondition
1223 {
1224     protected $user;
1225     public function __construct(PlUser &$user)
1226     {
1227         $this->user =& $user;
1228     }
1229 }
1230 // }}}
1231
1232 // {{{ class UFC_Contact
1233 /** Filters users who belong to selected user's contacts
1234  */
1235 class UFC_Contact extends UFC_UserRelated
1236 {
1237     public function buildCondition(PlFilter &$uf)
1238     {
1239         $sub = $uf->addContactFilter($this->user->id());
1240         return 'c' . $sub . '.contact IS NOT NULL';
1241     }
1242 }
1243 // }}}
1244
1245 // {{{ class UFC_WatchRegistration
1246 /** Filters users being watched by selected user
1247  */
1248 class UFC_WatchRegistration extends UFC_UserRelated
1249 {
1250     public function buildCondition(PlFilter &$uf)
1251     {
1252         if (!$this->user->watchType('registration')) {
1253             return PlFilterCondition::COND_FALSE;
1254         }
1255         $uids = $this->user->watchUsers();
1256         if (count($uids) == 0) {
1257             return PlFilterCondition::COND_FALSE;
1258         } else {
1259             return XDB::format('$UID IN {?}', $uids);
1260         }
1261     }
1262 }
1263 // }}}
1264
1265 // {{{ class UFC_WatchPromo
1266 /** Filters users belonging to a promo watched by selected user
1267  * @param $user Selected user (the one watching promo)
1268  * @param $grade Formation the user is watching
1269  */
1270 class UFC_WatchPromo extends UFC_UserRelated
1271 {
1272     private $grade;
1273     public function __construct(PlUser &$user, $grade = UserFilter::GRADE_ING)
1274     {
1275         parent::__construct($user);
1276         $this->grade = $grade;
1277     }
1278
1279     public function buildCondition(PlFilter &$uf)
1280     {
1281         $promos = $this->user->watchPromos();
1282         if (count($promos) == 0) {
1283             return PlFilterCondition::COND_FALSE;
1284         } else {
1285             $sube = $uf->addEducationFilter(true, $this->grade);
1286             $field = 'pe' . $sube . '.' . UserFilter::promoYear($this->grade);
1287             return XDB::format($field . ' IN {?}', $promos);
1288         }
1289     }
1290 }
1291 // }}}
1292
1293 // {{{ class UFC_WatchContact
1294 /** Filters users watched by selected user
1295  */
1296 class UFC_WatchContact extends UFC_Contact
1297 {
1298     public function buildCondition(PlFilter &$uf)
1299     {
1300         if (!$this->user->watchContacts()) {
1301             return PlFilterCondition::COND_FALSE;
1302         }
1303         return parent::buildCondition($uf);
1304     }
1305 }
1306 // }}}
1307
1308 // {{{ class UFC_MarketingHash
1309 /** Filters users using the hash generated
1310  * to send marketing emails to him.
1311  */
1312 class UFC_MarketingHash implements UserFilterCondition
1313 {
1314     private $hash;
1315
1316     public function __construct($hash)
1317     {
1318         $this->hash = $hash;
1319     }
1320
1321     public function buildCondition(PlFilter &$uf)
1322     {
1323         $table = $uf->addMarketingHash();
1324         return XDB::format('rm.hash = {?}', $this->hash);
1325     }
1326 }
1327 // }}}
1328
1329 /******************
1330  * ORDERS
1331  ******************/
1332
1333 // {{{ class UserFilterOrder
1334 /** Base class for ordering results of a query.
1335  * Parameters for the ordering must be given to the constructor ($desc for a
1336  *     descending order).
1337  * The getSortTokens function is used to get actual ordering part of the query.
1338  */
1339 abstract class UserFilterOrder extends PlFilterOrder
1340 {
1341     /** This function must return the tokens to use for ordering
1342      * @param &$uf The UserFilter whose results must be ordered
1343      * @return The name of the field to use for ordering results
1344      */
1345 //    abstract protected function getSortTokens(UserFilter &$uf);
1346 }
1347 // }}}
1348
1349 // {{{ class UFO_Promo
1350 /** Orders users by promotion
1351  * @param $grade Formation whose promotion users should be sorted by (restricts results to users of that formation)
1352  * @param $desc Whether sort is descending
1353  */
1354 class UFO_Promo extends UserFilterOrder
1355 {
1356     private $grade;
1357
1358     public function __construct($grade = null, $desc = false)
1359     {
1360         parent::__construct($desc);
1361         $this->grade = $grade;
1362     }
1363
1364     protected function getSortTokens(PlFilter &$uf)
1365     {
1366         if (UserFilter::isGrade($this->grade)) {
1367             $sub = $uf->addEducationFilter($this->grade);
1368             return 'pe' . $sub . '.' . UserFilter::promoYear($this->grade);
1369         } else {
1370             $sub = $uf->addDisplayFilter();
1371             return 'pd' . $sub . '.promo';
1372         }
1373     }
1374 }
1375 // }}}
1376
1377 // {{{ class UFO_Name
1378 /** Sorts users by name
1379  * @param $type Type of name on which to sort (firstname...)
1380  * @param $variant Variant of that name to use (marital, ordinary...)
1381  * @param $particle Set to true if particles should be included in the sorting order
1382  * @param $desc If sort order should be descending
1383  */
1384 class UFO_Name extends UserFilterOrder
1385 {
1386     private $type;
1387     private $variant;
1388     private $particle;
1389
1390     public function __construct($type, $variant = null, $particle = false, $desc = false)
1391     {
1392         parent::__construct($desc);
1393         $this->type = $type;
1394         $this->variant = $variant;
1395         $this->particle = $particle;
1396     }
1397
1398     protected function getSortTokens(PlFilter &$uf)
1399     {
1400         if (Profile::isDisplayName($this->type)) {
1401             $sub = $uf->addDisplayFilter();
1402             return 'pd' . $sub . '.' . $this->type;
1403         } else {
1404             $sub = $uf->addNameFilter($this->type, $this->variant);
1405             if ($this->particle) {
1406                 return 'CONCAT(pn' . $sub . '.particle, \' \', pn' . $sub . '.name)';
1407             } else {
1408                 return 'pn' . $sub . '.name';
1409             }
1410         }
1411     }
1412 }
1413 // }}}
1414
1415 // {{{ class UFO_Score
1416 class UFO_Score extends UserFilterOrder
1417 {
1418     protected function getSortTokens(PlFilter &$uf)
1419     {
1420         $sub = $uf->addNameTokensFilter();
1421         return 'SUM(' . $sub . '.score)';
1422     }
1423 }
1424 // }}}
1425
1426 // {{{ class UFO_Registration
1427 /** Sorts users based on registration date
1428  */
1429 class UFO_Registration extends UserFilterOrder
1430 {
1431     protected function getSortTokens(PlFilter &$uf)
1432     {
1433         return 'a.registration_date';
1434     }
1435 }
1436 // }}}
1437
1438 // {{{ class UFO_Birthday
1439 /** Sorts users based on next birthday date
1440  */
1441 class UFO_Birthday extends UserFilterOrder
1442 {
1443     protected function getSortTokens(PlFilter &$uf)
1444     {
1445         return 'p.next_birthday';
1446     }
1447 }
1448 // }}}
1449
1450 // {{{ class UFO_ProfileUpdate
1451 /** Sorts users based on last profile update
1452  */
1453 class UFO_ProfileUpdate extends UserFilterOrder
1454 {
1455     protected function getSortTokens(PlFilter &$uf)
1456     {
1457         return 'p.last_change';
1458     }
1459 }
1460 // }}}
1461
1462 // {{{ class UFO_Death
1463 /** Sorts users based on death date
1464  */
1465 class UFO_Death extends UserFilterOrder
1466 {
1467     protected function getSortTokens(PlFilter &$uf)
1468     {
1469         return 'p.deathdate';
1470     }
1471 }
1472 // }}}
1473
1474
1475 /***********************************
1476   *********************************
1477           USER FILTER CLASS
1478   *********************************
1479  ***********************************/
1480
1481 // {{{ class UserFilter
1482 /** This class provides a convenient and centralized way of filtering users.
1483  *
1484  * Usage:
1485  * $uf = new UserFilter(new UFC_Blah($x, $y), new UFO_Coin($z, $t));
1486  *
1487  * Resulting UserFilter can be used to:
1488  * - get a list of User objects matching the filter
1489  * - get a list of UIDs matching the filter
1490  * - get the number of users matching the filter
1491  * - check whether a given User matches the filter
1492  * - filter a list of User objects depending on whether they match the filter
1493  *
1494  * Usage for UFC and UFO objects:
1495  * A UserFilter will call all private functions named XXXJoins.
1496  * These functions must return an array containing the list of join
1497  * required by the various UFC and UFO associated to the UserFilter.
1498  * Entries in those returned array are of the following form:
1499  *   'join_tablealias' => array('join_type', 'joined_table', 'join_criter')
1500  * which will be translated into :
1501  *   join_type JOIN joined_table AS join_tablealias ON (join_criter)
1502  * in the final query.
1503  *
1504  * In the join_criter text, $ME is replaced with 'join_tablealias', $PID with
1505  * profile.pid, and $UID with accounts.uid.
1506  *
1507  * For each kind of "JOIN" needed, a function named addXXXFilter() should be defined;
1508  * its parameter will be used to set various private vars of the UserFilter describing
1509  * the required joins ; such a function shall return the "join_tablealias" to use
1510  * when referring to the joined table.
1511  *
1512  * For example, if data from profile_job must be available to filter results,
1513  * the UFC object will call $uf-addJobFilter(), which will set the 'with_pj' var and
1514  * return 'pj', the short name to use when referring to profile_job; when building
1515  * the query, calling the jobJoins function will return an array containing a single
1516  * row:
1517  *   'pj' => array('left', 'profile_job', '$ME.pid = $UID');
1518  *
1519  * The 'register_optional' function can be used to generate unique table aliases when
1520  * the same table has to be joined several times with different aliases.
1521  */
1522 class UserFilter extends PlFilter
1523 {
1524     protected $joinMethods = array();
1525
1526     protected $joinMetas = array(
1527                                 '$PID' => 'p.pid',
1528                                 '$UID' => 'a.uid',
1529                                 );
1530
1531     private $root;
1532     private $sort = array();
1533     private $query = null;
1534     private $orderby = null;
1535
1536     private $lastusercount = null;
1537     private $lastprofilecount = null;
1538
1539     public function __construct($cond = null, $sort = null)
1540     {
1541         if (empty($this->joinMethods)) {
1542             $class = new ReflectionClass('UserFilter');
1543             foreach ($class->getMethods() as $method) {
1544                 $name = $method->getName();
1545                 if (substr($name, -5) == 'Joins' && $name != 'buildJoins') {
1546                     $this->joinMethods[] = $name;
1547                 }
1548             }
1549         }
1550         if (!is_null($cond)) {
1551             if ($cond instanceof PlFilterCondition) {
1552                 $this->setCondition($cond);
1553             }
1554         }
1555         if (!is_null($sort)) {
1556             if ($sort instanceof UserFilterOrder) {
1557                 $this->addSort($sort);
1558             } else if (is_array($sort)) {
1559                 foreach ($sort as $s) {
1560                     $this->addSort($s);
1561                 }
1562             }
1563         }
1564     }
1565
1566     private function buildQuery()
1567     {
1568         if (is_null($this->orderby)) {
1569             $orders = array();
1570             foreach ($this->sort as $sort) {
1571                 $orders = array_merge($orders, $sort->buildSort($this));
1572             }
1573             if (count($orders) == 0) {
1574                 $this->orderby = '';
1575             } else {
1576                 $this->orderby = 'ORDER BY  ' . implode(', ', $orders);
1577             }
1578         }
1579         if (is_null($this->query)) {
1580             $where = $this->root->buildCondition($this);
1581             if ($this->with_forced_sn) {
1582                 $this->requireProfiles();
1583                 $from = 'search_name AS sn';
1584             } else if ($this->with_accounts) {
1585                 $from = 'accounts AS a';
1586             } else {
1587                 $this->requireProfiles();
1588                 $from = 'profiles AS p';
1589             }
1590             $joins = $this->buildJoins();
1591             $this->query = 'FROM  ' . $from . '
1592                                ' . $joins . '
1593                            WHERE  (' . $where . ')';
1594         }
1595     }
1596
1597     private function getUIDList($uids = null, PlLimit &$limit)
1598     {
1599         $this->requireAccounts();
1600         $this->buildQuery();
1601         $lim = $limit->getSql();
1602         $cond = '';
1603         if (!is_null($uids)) {
1604             $cond = XDB::format(' AND a.uid IN {?}', $uids);
1605         }
1606         $fetched = XDB::fetchColumn('SELECT SQL_CALC_FOUND_ROWS  a.uid
1607                                     ' . $this->query . $cond . '
1608                                    GROUP BY  a.uid
1609                                     ' . $this->orderby . '
1610                                     ' . $lim);
1611         $this->lastusercount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
1612         return $fetched;
1613     }
1614
1615     private function getPIDList($pids = null, PlLimit &$limit)
1616     {
1617         $this->requireProfiles();
1618         $this->buildQuery();
1619         $lim = $limit->getSql();
1620         $cond = '';
1621         if (!is_null($pids)) {
1622             $cond = XDB::format(' AND p.pid IN {?}', $pids);
1623         }
1624         $fetched = XDB::fetchColumn('SELECT  SQL_CALC_FOUND_ROWS  p.pid
1625                                     ' . $this->query . $cond . '
1626                                    GROUP BY  p.pid
1627                                     ' . $this->orderby . '
1628                                     ' . $lim);
1629         $this->lastprofilecount = (int)XDB::fetchOneCell('SELECT FOUND_ROWS()');
1630         return $fetched;
1631     }
1632
1633     private static function defaultLimit($limit) {
1634         if ($limit == null) {
1635             return new PlLimit();
1636         } else {
1637             return $limit;
1638         }
1639     }
1640
1641     /** Check that the user match the given rule.
1642      */
1643     public function checkUser(PlUser &$user)
1644     {
1645         $this->requireAccounts();
1646         $this->buildQuery();
1647         $count = (int)XDB::fetchOneCell('SELECT  COUNT(*)
1648                                         ' . $this->query . XDB::format(' AND a.uid = {?}', $user->id()));
1649         return $count == 1;
1650     }
1651
1652     /** Check that the profile match the given rule.
1653      */
1654     public function checkProfile(Profile &$profile)
1655     {
1656         $this->requireProfiles();
1657         $this->buildQuery();
1658         $count = (int)XDB::fetchOneCell('SELECT  COUNT(*)
1659                                         ' . $this->query . XDB::format(' AND p.pid = {?}', $profile->id()));
1660         return $count == 1;
1661     }
1662
1663     /** Default filter is on users
1664      */
1665     public function filter(array $users, $limit = null)
1666     {
1667         return $this->filterUsers($users, self::defaultLimit($limit));
1668     }
1669
1670     /** Filter a list of users to extract the users matching the rule.
1671      */
1672     public function filterUsers(array $users, $limit = null)
1673     {
1674         $limit = self::defaultLimit($limit);
1675         $this->requireAccounts();
1676         $this->buildQuery();
1677         $table = array();
1678         $uids  = array();
1679         foreach ($users as $user) {
1680             if ($user instanceof PlUser) {
1681                 $uid = $user->id();
1682             } else {
1683                 $uid = $user;
1684             }
1685             $uids[] = $uid;
1686             $table[$uid] = $user;
1687         }
1688         $fetched = $this->getUIDList($uids, $limit);
1689         $output = array();
1690         foreach ($fetched as $uid) {
1691             $output[] = $table[$uid];
1692         }
1693         return $output;
1694     }
1695
1696     /** Filter a list of profiles to extract the users matching the rule.
1697      */
1698     public function filterProfiles(array $profiles, $limit = null)
1699     {
1700         $limit = self::defaultLimit($limit);
1701         $this->requireProfiles();
1702         $this->buildQuery();
1703         $table = array();
1704         $pids  = array();
1705         foreach ($profiles as $profile) {
1706             if ($profile instanceof Profile) {
1707                 $pid = $profile->id();
1708             } else {
1709                 $pid = $profile;
1710             }
1711             $pids[] = $pid;
1712             $table[$pid] = $profile;
1713         }
1714         $fetched = $this->getPIDList($pids, $limit);
1715         $output = array();
1716         foreach ($fetched as $pid) {
1717             $output[] = $table[$pid];
1718         }
1719         return $output;
1720     }
1721
1722     public function getUIDs($limit = null)
1723     {
1724         $limit = self::defaultLimit($limit);
1725         return $this->getUIDList(null, $limit);
1726     }
1727
1728     public function getUID($pos = 0)
1729     {
1730         $uids =$this->getUIDList(null, new PlFilter(1, $pos));
1731         if (count($uids) == 0) {
1732             return null;
1733         } else {
1734             return $uids[0];
1735         }
1736     }
1737
1738     public function getPIDs($limit = null)
1739     {
1740         $limit = self::defaultLimit($limit);
1741         return $this->getPIDList(null, $limit);
1742     }
1743
1744     public function getPID($pos = 0)
1745     {
1746         $pids =$this->getPIDList(null, new PlFilter(1, $pos));
1747         if (count($pids) == 0) {
1748             return null;
1749         } else {
1750             return $pids[0];
1751         }
1752     }
1753
1754     public function getUsers($limit = null)
1755     {
1756         return User::getBulkUsersWithUIDs($this->getUIDs($limit));
1757     }
1758
1759     public function getUser($pos = 0)
1760     {
1761         $uid = $this->getUID($pos);
1762         if ($uid == null) {
1763             return null;
1764         } else {
1765             return User::getWithUID($uid);
1766         }
1767     }
1768
1769     public function iterUsers($limit = null)
1770     {
1771         return User::iterOverUIDs($this->getUIDs($limit));
1772     }
1773
1774     public function getProfiles($limit = null, $fields = 0x0000, $visibility = null)
1775     {
1776         return Profile::getBulkProfilesWithPIDs($this->getPIDs($limit), $fields, $visibility);
1777     }
1778
1779     public function getProfile($pos = 0, $fields = 0x0000, $visibility = null)
1780     {
1781         $pid = $this->getPID($pos);
1782         if ($pid == null) {
1783             return null;
1784         } else {
1785             return Profile::get($pid, $fields, $visibility);
1786         }
1787     }
1788
1789     public function iterProfiles($limit = null, $fields = 0x0000, $visibility = null)
1790     {
1791         return Profile::iterOverPIDs($this->getPIDs($limit), true, $fields, $visibility);
1792     }
1793
1794     public function get($limit = null)
1795     {
1796         return $this->getUsers($limit);
1797     }
1798
1799
1800     public function getTotalCount()
1801     {
1802         return $this->getTotalUserCount();
1803     }
1804
1805     public function getTotalUserCount()
1806     {
1807         if (is_null($this->lastusercount)) {
1808             $this->requireAccounts();
1809             $this->buildQuery();
1810             return (int)XDB::fetchOneCell('SELECT  COUNT(DISTINCT a.uid)
1811                                           ' . $this->query);
1812         } else {
1813             return $this->lastusercount;
1814         }
1815     }
1816
1817     public function getTotalProfileCount()
1818     {
1819         if (is_null($this->lastprofilecount)) {
1820             $this->requireProfiles();
1821             $this->buildQuery();
1822             return (int)XDB::fetchOneCell('SELECT  COUNT(DISTINCT p.pid)
1823                                           ' . $this->query);
1824         } else {
1825             return $this->lastprofilecount;
1826         }
1827     }
1828
1829     public function setCondition(PlFilterCondition &$cond)
1830     {
1831         $this->root =& $cond;
1832         $this->query = null;
1833     }
1834
1835     public function addSort(PlFilterOrder &$sort)
1836     {
1837         $this->sort[] = $sort;
1838         $this->orderby = null;
1839     }
1840
1841     static public function getLegacy($promo_min, $promo_max)
1842     {
1843         if ($promo_min != 0) {
1844             $min = new UFC_Promo('>=', self::GRADE_ING, intval($promo_min));
1845         } else {
1846             $min = new PFC_True();
1847         }
1848         if ($promo_max != 0) {
1849             $max = new UFC_Promo('<=', self::GRADE_ING, intval($promo_max));
1850         } else {
1851             $max = new PFC_True();
1852         }
1853         return new UserFilter(new PFC_And($min, $max));
1854     }
1855
1856     static public function sortByName()
1857     {
1858         return array(new UFO_Name(Profile::LASTNAME), new UFO_Name(Profile::FIRSTNAME));
1859     }
1860
1861     static public function sortByPromo()
1862     {
1863         return array(new UFO_Promo(), new UFO_Name(Profile::LASTNAME), new UFO_Name(Profile::FIRSTNAME));
1864     }
1865
1866     static private function getDBSuffix($string)
1867     {
1868         if (is_array($string)) {
1869             if (count($string) == 1) {
1870                 return self::getDBSuffix(array_pop($string));
1871             }
1872             return md5(implode('|', $string));
1873         } else {
1874             return preg_replace('/[^a-z0-9]/i', '', $string);
1875         }
1876     }
1877
1878
1879     /** Stores a new (and unique) table alias in the &$table table
1880      * @param   &$table Array in which the table alias must be stored
1881      * @param   $val    Value which will then be used to build the join
1882      * @return          Name of the newly created alias
1883      */
1884     private $option = 0;
1885     private function register_optional(array &$table, $val)
1886     {
1887         if (is_null($val)) {
1888             $sub   = $this->option++;
1889             $index = null;
1890         } else {
1891             $sub   = self::getDBSuffix($val);
1892             $index = $val;
1893         }
1894         $sub = '_' . $sub;
1895         $table[$sub] = $index;
1896         return $sub;
1897     }
1898
1899     /** PROFILE VS ACCOUNT
1900      */
1901     private $with_profiles  = false;
1902     private $with_accounts  = false;
1903     private $with_forced_sn = false;
1904     public function requireAccounts()
1905     {
1906         $this->with_accounts = true;
1907     }
1908
1909     public function requireProfiles()
1910     {
1911         $this->with_profiles = true;
1912     }
1913
1914     /** Forces the "FROM" to use search_name instead of accounts or profiles */
1915     public function forceSearchName()
1916     {
1917         $this->with_forced_sn = true;
1918     }
1919
1920     protected function accountJoins()
1921     {
1922         $joins = array();
1923         /** Quick search is much more efficient with sn first and PID second */
1924         if ($this->with_forced_sn) {
1925             $joins['p'] = PlSqlJoin::left('profiles', '$PID = sn.pid');
1926             if ($this->with_accounts) {
1927                 $joins['ap'] = PlSqlJoin::left('account_profiles', '$ME.pid = $PID');
1928                 $joins['a'] = PlSqlJoin::left('accounts', '$UID = ap.uid');
1929             }
1930         } else if ($this->with_profiles && $this->with_accounts) {
1931             $joins['ap'] = PlSqlJoin::left('account_profiles', '$ME.uid = $UID AND FIND_IN_SET(\'owner\', ap.perms)');
1932             $joins['p'] = PlSqlJoin::left('profiles', '$PID = ap.pid');
1933         }
1934         return $joins;
1935     }
1936
1937     /** DISPLAY
1938      */
1939     const DISPLAY = 'display';
1940     private $pd = false;
1941     public function addDisplayFilter()
1942     {
1943         $this->requireProfiles();
1944         $this->pd = true;
1945         return '';
1946     }
1947
1948     protected function displayJoins()
1949     {
1950         if ($this->pd) {
1951             return array('pd' => PlSqlJoin::left('profile_display', '$ME.pid = $PID'));
1952         } else {
1953             return array();
1954         }
1955     }
1956
1957     /** LOGGER
1958      */
1959
1960     private $with_logger = false;
1961     public function addLoggerFilter()
1962     {
1963         $this->with_logger = true;
1964         $this->requireAccounts();
1965         return 'ls';
1966     }
1967     protected function loggerJoins()
1968     {
1969         $joins = array();
1970         if ($this->with_logger) {
1971             $joins['ls'] = PlSqlJoin::left('log_sessions', '$ME.uid = $UID');
1972         }
1973         return $joins;
1974     }
1975
1976     /** NAMES
1977      */
1978
1979     static public function assertName($name)
1980     {
1981         if (!DirEnum::getID(DirEnum::NAMETYPES, $name)) {
1982             Platal::page()->kill('Invalid name type: ' . $name);
1983         }
1984     }
1985
1986     private $pn  = array();
1987     public function addNameFilter($type, $variant = null)
1988     {
1989         $this->requireProfiles();
1990         if (!is_null($variant)) {
1991             $ft  = $type . '_' . $variant;
1992         } else {
1993             $ft = $type;
1994         }
1995         $sub = '_' . $ft;
1996         self::assertName($ft);
1997
1998         if (!is_null($variant) && $variant == 'other') {
1999             $sub .= $this->option++;
2000         }
2001         $this->pn[$sub] = DirEnum::getID(DirEnum::NAMETYPES, $ft);
2002         return $sub;
2003     }
2004
2005     protected function nameJoins()
2006     {
2007         $joins = array();
2008         foreach ($this->pn as $sub => $type) {
2009             $joins['pn' . $sub] = PlSqlJoin::left('profile_name', '$ME.pid = $PID AND $ME.typeid = {?}', $type);
2010         }
2011         return $joins;
2012     }
2013
2014     /** NAMETOKENS
2015      */
2016     private $with_sn = false;
2017     // Set $doingQuickSearch to true if you wish to optimize the query
2018     public function addNameTokensFilter($doingQuickSearch = false)
2019     {
2020         $this->requireProfiles();
2021         $this->with_forced_sn = ($this->with_forced_sn || $doingQuickSearch);
2022         $this->with_sn = true;
2023         return 'sn';
2024     }
2025
2026     protected function nameTokensJoins()
2027     {
2028         /* We don't return joins, since with_sn forces the SELECT to run on search_name first */
2029         if ($this->with_sn && !$this->with_forced_sn) {
2030             return array(
2031                 'sn' => PlSqlJoin::left('search_name', '$ME.pid = $PID')
2032             );
2033         } else {
2034             return array();
2035         }
2036     }
2037
2038     /** NATIONALITY
2039      */
2040
2041     private $with_nat = false;
2042     public function addNationalityFilter()
2043     {
2044         $this->with_nat = true;
2045         return 'ngc';
2046     }
2047
2048     protected function nationalityJoins()
2049     {
2050         $joins = array();
2051         if ($this->with_nat) {
2052             $joins['ngc'] = PlSqlJoin::left('geoloc_countries', '$ME.iso_3166_1_a2 = p.nationality1 OR $ME.iso_3166_1_a2 = p.nationality2 OR $ME.iso_3166_1_a2 = p.nationality3');
2053         }
2054         return $joins;
2055     }
2056
2057     /** EDUCATION
2058      */
2059     const GRADE_ING = 'Ing.';
2060     const GRADE_PHD = 'PhD';
2061     const GRADE_MST = 'M%';
2062     static public function isGrade($grade)
2063     {
2064         return ($grade !== 0) && ($grade == self::GRADE_ING || $grade == self::GRADE_PHD || $grade == self::GRADE_MST);
2065     }
2066
2067     static public function assertGrade($grade)
2068     {
2069         if (!self::isGrade($grade)) {
2070             Platal::page()->killError("Diplôme non valide: $grade");
2071         }
2072     }
2073
2074     static public function promoYear($grade)
2075     {
2076         // XXX: Definition of promotion for phds and masters might change in near future.
2077         return ($grade == UserFilter::GRADE_ING) ? 'entry_year' : 'grad_year';
2078     }
2079
2080     private $pepe     = array();
2081     private $with_pee = false;
2082     public function addEducationFilter($x = false, $grade = null)
2083     {
2084         $this->requireProfiles();
2085         if (!$x) {
2086             $index = $this->option;
2087             $sub   = $this->option++;
2088         } else {
2089             self::assertGrade($grade);
2090             $index = $grade;
2091             $sub   = $grade[0];
2092             $this->with_pee = true;
2093         }
2094         $sub = '_' . $sub;
2095         $this->pepe[$index] = $sub;
2096         return $sub;
2097     }
2098
2099     protected function educationJoins()
2100     {
2101         $joins = array();
2102         if ($this->with_pee) {
2103             $joins['pee'] = PlSqlJoin::inner('profile_education_enum', 'pee.abbreviation = \'X\'');
2104         }
2105         foreach ($this->pepe as $grade => $sub) {
2106             if ($this->isGrade($grade)) {
2107                 $joins['pe' . $sub] = PlSqlJoin::left('profile_education', '$ME.eduid = pee.id AND $ME.pid = $PID');
2108                 $joins['pede' . $sub] = PlSqlJoin::inner('profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid AND $ME.abbreviation LIKE {?}', $grade);
2109             } else {
2110                 $joins['pe' . $sub] = PlSqlJoin::left('profile_education', '$ME.pid = $PID');
2111                 $joins['pee' . $sub] = PlSqlJoin::inner('profile_education_enum', '$ME.id = pe' . $sub . '.eduid');
2112                 $joins['pede' . $sub] = PlSqlJoin::inner('profile_education_degree_enum', '$ME.id = pe' . $sub . '.degreeid');
2113             }
2114         }
2115         return $joins;
2116     }
2117
2118
2119     /** GROUPS
2120      */
2121     private $gpm = array();
2122     public function addGroupFilter($group = null)
2123     {
2124         $this->requireAccounts();
2125         if (!is_null($group)) {
2126             if (is_int($group) || ctype_digit($group)) {
2127                 $index = $sub = $group;
2128             } else {
2129                 $index = $group;
2130                 $sub   = self::getDBSuffix($group);
2131             }
2132         } else {
2133             $sub = 'group_' . $this->option++;
2134             $index = null;
2135         }
2136         $sub = '_' . $sub;
2137         $this->gpm[$sub] = $index;
2138         return $sub;
2139     }
2140
2141     protected function groupJoins()
2142     {
2143         $joins = array();
2144         foreach ($this->gpm as $sub => $key) {
2145             if (is_null($key)) {
2146                 $joins['gpa' . $sub] = PlSqlJoin::inner('groups');
2147                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
2148             } else if (is_int($key) || ctype_digit($key)) {
2149                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = ' . $key);
2150             } else {
2151                 $joins['gpa' . $sub] = PlSqlJoin::inner('groups', '$ME.diminutif = {?}', $key);
2152                 $joins['gpm' . $sub] = PlSqlJoin::left('group_members', '$ME.uid = $UID AND $ME.asso_id = gpa' . $sub . '.id');
2153             }
2154         }
2155         return $joins;
2156     }
2157
2158     /** BINETS
2159      */
2160
2161     private $with_bi = false;
2162     private $with_bd = false;
2163     public function addBinetsFilter($with_enum = false)
2164     {
2165         $this->requireProfiles();
2166         $this->with_bi = true;
2167         if ($with_enum) {
2168             $this->with_bd = true;
2169             return 'bd';
2170         } else {
2171             return 'bi';
2172         }
2173     }
2174
2175     protected function binetsJoins()
2176     {
2177         $joins = array();
2178         if ($this->with_bi) {
2179             $joins['bi'] = PlSqlJoin::left('profile_binets', '$ME.pid = $PID');
2180         }
2181         if ($this->with_bd) {
2182             $joins['bd'] = PlSqlJoin::left('profile_binet_enum', '$ME.id = bi.binet_id');
2183         }
2184         return $joins;
2185     }
2186
2187     /** EMAILS
2188      */
2189     private $e = array();
2190     public function addEmailRedirectFilter($email = null)
2191     {
2192         $this->requireAccounts();
2193         return $this->register_optional($this->e, $email);
2194     }
2195
2196     private $ve = array();
2197     public function addVirtualEmailFilter($email = null)
2198     {
2199         $this->addAliasFilter(self::ALIAS_FORLIFE);
2200         return $this->register_optional($this->ve, $email);
2201     }
2202
2203     const ALIAS_BEST    = 'bestalias';
2204     const ALIAS_FORLIFE = 'forlife';
2205     private $al = array();
2206     public function addAliasFilter($alias = null)
2207     {
2208         $this->requireAccounts();
2209         return $this->register_optional($this->al, $alias);
2210     }
2211
2212     protected function emailJoins()
2213     {
2214         global $globals;
2215         $joins = array();
2216         foreach ($this->e as $sub=>$key) {
2217             if (is_null($key)) {
2218                 $joins['e' . $sub] = PlSqlJoin::left('emails', '$ME.uid = $UID AND $ME.flags != \'filter\'');
2219             } else {
2220                 if (!is_array($key)) {
2221                     $key = array($key);
2222                 }
2223                 $joins['e' . $sub] = PlSqlJoin::left('emails', '$ME.uid = $UID AND $ME.flags != \'filter\'
2224                                                                AND $ME.email IN {?}' . $key);
2225             }
2226         }
2227         foreach ($this->al as $sub=>$key) {
2228             if (is_null($key)) {
2229                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\')');
2230             } else if ($key == self::ALIAS_BEST) {
2231                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\') AND  FIND_IN_SET(\'bestalias\', $ME.flags)');
2232             } else if ($key == self::ALIAS_FORLIFE) {
2233                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type = \'a_vie\'');
2234             } else {
2235                 if (!is_array($key)) {
2236                     $key = array($key);
2237                 }
2238                 $joins['al' . $sub] = PlSqlJoin::left('aliases', '$ME.uid = $UID AND $ME.type IN (\'alias\', \'a_vie\')
2239                                                                   AND $ME.alias IN {?}', $key);
2240             }
2241         }
2242         foreach ($this->ve as $sub=>$key) {
2243             if (is_null($key)) {
2244                 $joins['v' . $sub] = PlSqlJoin::left('virtual', '$ME.type = \'user\'');
2245             } else {
2246                 if (!is_array($key)) {
2247                     $key = array($key);
2248                 }
2249                 $joins['v' . $sub] = PlSqlJoin::left('virtual', '$ME.type = \'user\' AND $ME.alias IN {?}', $key);
2250             }
2251             $joins['vr' . $sub] = PlSqlJoin::left('virtual_redirect',
2252                                                   '$ME.vid = v' . $sub . '.vid
2253                                                    AND ($ME.redirect IN (CONCAT(al_forlife.alias, \'@\', {?}),
2254                                                                          CONCAT(al_forlife.alias, \'@\', {?}),
2255                                                                          a.email))',
2256                                                   $globals->mail->domain, $globals->mail->domain2);
2257         }
2258         return $joins;
2259     }
2260
2261
2262     /** ADDRESSES
2263      */
2264     private $with_pa = false;
2265     public function addAddressFilter()
2266     {
2267         $this->requireProfiles();
2268         $this->with_pa = true;
2269         return 'pa';
2270     }
2271
2272     private $with_pac = false;
2273     public function addAddressCountryFilter()
2274     {
2275         $this->requireProfiles();
2276         $this->addAddressFilter();
2277         $this->with_pac = true;
2278         return 'gc';
2279     }
2280
2281     private $with_pal = false;
2282     public function addAddressLocalityFilter()
2283     {
2284         $this->requireProfiles();
2285         $this->addAddressFilter();
2286         $this->with_pal = true;
2287         return 'gl';
2288     }
2289
2290     protected function addressJoins()
2291     {
2292         $joins = array();
2293         if ($this->with_pa) {
2294             $joins['pa'] = PlSqlJoin::left('profile_addresses', '$ME.pid = $PID');
2295         }
2296         if ($this->with_pac) {
2297             $joins['gc'] = PlSqlJoin::left('geoloc_countries', '$ME.iso_3166_1_a2 = pa.countryID');
2298         }
2299         if ($this->with_pal) {
2300             $joins['gl'] = PlSqlJoin::left('geoloc_localities', '$ME.id = pa.localityID');
2301         }
2302         return $joins;
2303     }
2304
2305
2306     /** CORPS
2307      */
2308
2309     private $pc = false;
2310     private $pce = array();
2311     private $pcr = false;
2312     public function addCorpsFilter($type)
2313     {
2314         $this->requireProfiles();
2315         $this->pc = true;
2316         if ($type == UFC_Corps::CURRENT) {
2317             $pce['pcec'] = 'current_corpsid';
2318             return 'pcec';
2319         } else if ($type == UFC_Corps::ORIGIN) {
2320             $pce['pceo'] = 'original_corpsid';
2321             return 'pceo';
2322         }
2323     }
2324
2325     public function addCorpsRankFilter()
2326     {
2327         $this->requireProfiles();
2328         $this->pc = true;
2329         $this->pcr = true;
2330         return 'pcr';
2331     }
2332
2333     protected function corpsJoins()
2334     {
2335         $joins = array();
2336         if ($this->pc) {
2337             $joins['pc'] = PlSqlJoin::left('profile_corps', '$ME.pid = $PID');
2338         }
2339         if ($this->pcr) {
2340             $joins['pcr'] = PlSqlJoin::left('profile_corps_rank_enum', '$ME.id = pc.rankid');
2341         }
2342         foreach($this->pce as $sub => $field) {
2343             $joins[$sub] = PlSqlJoin::left('profile_corps_enum', '$ME.id = pc.' . $field);
2344         }
2345         return $joins;
2346     }
2347
2348     /** JOBS
2349      */
2350
2351     const JOB_SECTOR        = 0x0001;
2352     const JOB_SUBSECTOR     = 0x0002;
2353     const JOB_SUBSUBSECTOR  = 0x0004;
2354     const JOB_ALTERNATES    = 0x0008;
2355     const JOB_USERDEFINED   = 0x0010;
2356     const JOB_CV            = 0x0020;
2357
2358     const JOB_SECTORIZATION = 0x000F;
2359     const JOB_ANY           = 0x003F;
2360
2361     /** Joins :
2362      * pj => profile_job
2363      * pje => profile_job_enum
2364      * pjse => profile_job_sector_enum
2365      * pjsse => profile_job_subsector_enum
2366      * pjssse => profile_job_subsubsector_enum
2367      * pja => profile_job_alternates
2368      */
2369     private $with_pj = false;
2370     private $with_pje = false;
2371     private $with_pjse = false;
2372     private $with_pjsse = false;
2373     private $with_pjssse = false;
2374     private $with_pja = false;
2375
2376     public function addJobFilter()
2377     {
2378         $this->requireProfiles();
2379         $this->with_pj = true;
2380         return 'pj';
2381     }
2382
2383     public function addJobCompanyFilter()
2384     {
2385         $this->addJobFilter();
2386         $this->with_pje = true;
2387         return 'pje';
2388     }
2389
2390     public function addJobSectorizationFilter($type)
2391     {
2392         $this->addJobFilter();
2393         if ($type == self::JOB_SECTOR) {
2394             $this->with_pjse = true;
2395             return 'pjse';
2396         } else if ($type == self::JOB_SUBSECTOR) {
2397             $this->with_pjsse = true;
2398             return 'pjsse';
2399         } else if ($type == self::JOB_SUBSUBSECTOR) {
2400             $this->with_pjssse = true;
2401             return 'pjssse';
2402         } else if ($type == self::JOB_ALTERNATES) {
2403             $this->with_pja = true;
2404             return 'pja';
2405         }
2406     }
2407
2408     protected function jobJoins()
2409     {
2410         $joins = array();
2411         if ($this->with_pj) {
2412             $joins['pj'] = PlSqlJoin::left('profile_job', '$ME.pid = $PID');
2413         }
2414         if ($this->with_pje) {
2415             $joins['pje'] = PlSqlJoin::left('profile_job_enum', '$ME.id = pj.jobid');
2416         }
2417         if ($this->with_pjse) {
2418             $joins['pjse'] = PlSqlJoin::left('profile_job_sector_enum', '$ME.id = pj.sectorid');
2419         }
2420         if ($this->with_pjsse) {
2421             $joins['pjsse'] = PlSqlJoin::left('profile_job_subsector_enum', '$ME.id = pj.subsectorid');
2422         }
2423         if ($this->with_pjssse) {
2424             $joins['pjssse'] = PlSqlJoin::left('profile_job_subsubsector_enum', '$ME.id = pj.subsubsectorid');
2425         }
2426         if ($this->with_pja) {
2427             $joins['pja'] = PlSqlJoin::left('profile_job_alternates', '$ME.subsubsectorid = pj.subsubsectorid');
2428         }
2429         return $joins;
2430     }
2431
2432     /** NETWORKING
2433      */
2434
2435     private $with_pnw = false;
2436     public function addNetworkingFilter()
2437     {
2438         $this->requireAccounts();
2439         $this->with_pnw = true;
2440         return 'pnw';
2441     }
2442
2443     protected function networkingJoins()
2444     {
2445         $joins = array();
2446         if ($this->with_pnw) {
2447             $joins['pnw'] = PlSqlJoin::left('profile_networking', '$ME.pid = $PID');
2448         }
2449         return $joins;
2450     }
2451
2452     /** PHONE
2453      */
2454
2455     private $with_ptel = false;
2456
2457     public function addPhoneFilter()
2458     {
2459         $this->requireAccounts();
2460         $this->with_ptel = true;
2461         return 'ptel';
2462     }
2463
2464     protected function phoneJoins()
2465     {
2466         $joins = array();
2467         if ($this->with_ptel) {
2468             $joins['ptel'] = PlSqlJoin::left('profile_phones', '$ME.pid = $PID');
2469         }
2470         return $joins;
2471     }
2472
2473     /** MEDALS
2474      */
2475
2476     private $with_pmed = false;
2477     public function addMedalFilter()
2478     {
2479         $this->requireProfiles();
2480         $this->with_pmed = true;
2481         return 'pmed';
2482     }
2483
2484     protected function medalJoins()
2485     {
2486         $joins = array();
2487         if ($this->with_pmed) {
2488             $joins['pmed'] = PlSqlJoin::left('profile_medals', '$ME.pid = $PID');
2489         }
2490         return $joins;
2491     }
2492
2493     /** MENTORING
2494      */
2495
2496     private $pms = array();
2497     const MENTOR_EXPERTISE  = 1;
2498     const MENTOR_COUNTRY    = 2;
2499     const MENTOR_SECTOR     = 3;
2500
2501     public function addMentorFilter($type)
2502     {
2503         $this->requireAccounts();
2504         switch($type) {
2505         case self::MENTOR_EXPERTISE:
2506             $this->pms['pme'] = 'profile_mentor';
2507             return 'pme';
2508         case self::MENTOR_COUNTRY:
2509             $this->pms['pmc'] = 'profile_mentor_country';
2510             return 'pmc';
2511         case self::MENTOR_SECTOR:
2512             $this->pms['pms'] =  'profile_mentor_sector';
2513             return 'pms';
2514         default:
2515             Platal::page()->killError("Undefined mentor filter.");
2516         }
2517     }
2518
2519     protected function mentorJoins()
2520     {
2521         $joins = array();
2522         foreach ($this->pms as $sub => $tab) {
2523             $joins[$sub] = PlSqlJoin::left($tab, '$ME.pid = $PID');
2524         }
2525         return $joins;
2526     }
2527
2528     /** CONTACTS
2529      */
2530     private $cts = array();
2531     public function addContactFilter($uid = null)
2532     {
2533         $this->requireProfiles();
2534         return $this->register_optional($this->cts, is_null($uid) ? null : 'user_' . $uid);
2535     }
2536
2537     protected function contactJoins()
2538     {
2539         $joins = array();
2540         foreach ($this->cts as $sub=>$key) {
2541             if (is_null($key)) {
2542                 $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.contact = $PID');
2543             } else {
2544                 $joins['c' . $sub] = PlSqlJoin::left('contacts', '$ME.uid = {?} AND $ME.contact = $PID', substr($key, 5));
2545             }
2546         }
2547         return $joins;
2548     }
2549
2550
2551     /** CARNET
2552      */
2553     private $wn = array();
2554     public function addWatchRegistrationFilter($uid = null)
2555     {
2556         $this->requireAccounts();
2557         return $this->register_optional($this->wn, is_null($uid) ? null : 'user_' . $uid);
2558     }
2559
2560     private $wp = array();
2561     public function addWatchPromoFilter($uid = null)
2562     {
2563         $this->requireAccounts();
2564         return $this->register_optional($this->wp, is_null($uid) ? null : 'user_' . $uid);
2565     }
2566
2567     private $w = array();
2568     public function addWatchFilter($uid = null)
2569     {
2570         $this->requireAccounts();
2571         return $this->register_optional($this->w, is_null($uid) ? null : 'user_' . $uid);
2572     }
2573
2574     protected function watchJoins()
2575     {
2576         $joins = array();
2577         foreach ($this->w as $sub=>$key) {
2578             if (is_null($key)) {
2579                 $joins['w' . $sub] = PlSqlJoin::left('watch');
2580             } else {
2581                 $joins['w' . $sub] = PlSqlJoin::left('watch', '$ME.uid = {?}', substr($key, 5));
2582             }
2583         }
2584         foreach ($this->wn as $sub=>$key) {
2585             if (is_null($key)) {
2586                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID');
2587             } else {
2588                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5));
2589             }
2590         }
2591         foreach ($this->wn as $sub=>$key) {
2592             if (is_null($key)) {
2593                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.ni_id = $UID');
2594             } else {
2595                 $joins['wn' . $sub] = PlSqlJoin::left('watch_nonins', '$ME.uid = {?} AND $ME.ni_id = $UID', substr($key, 5));
2596             }
2597         }
2598         foreach ($this->wp as $sub=>$key) {
2599             if (is_null($key)) {
2600                 $joins['wp' . $sub] = PlSqlJoin::left('watch_promo');
2601             } else {
2602                 $joins['wp' . $sub] = PlSqlJoin::left('watch_promo', '$ME.uid = {?}', substr($key, 5));
2603             }
2604         }
2605         return $joins;
2606     }
2607
2608
2609     /** PHOTOS
2610      */
2611     private $with_photo;
2612     public function addPhotoFilter()
2613     {
2614         $this->requireProfiles();
2615         $this->with_photo = true;
2616     }
2617
2618     protected function photoJoins()
2619     {
2620         if ($this->with_photo) {
2621             return array('photo' => PlSqlJoin::left('profile_photos', '$ME.pid = $PID'));
2622         } else {
2623             return array();
2624         }
2625     }
2626
2627
2628     /** MARKETING
2629      */
2630     private $with_rm;
2631     public function addMarketingHash()
2632     {
2633         $this->requireAccounts();
2634         $this->with_rm = true;
2635     }
2636
2637     protected function marketingJoins()
2638     {
2639         if ($this->with_rm) {
2640             return array('rm' => PlSqlJoin::left('register_marketing', '$ME.uid = $UID'));
2641         } else {
2642             return array();
2643         }
2644     }
2645 }
2646 // }}}
2647
2648 // {{{ class ProfileFilter
2649 class ProfileFilter extends UserFilter
2650 {
2651     public function get($limit = null)
2652     {
2653         return $this->getProfiles($limit);
2654     }
2655
2656     public function filter(array $profiles, $limit = null)
2657     {
2658         return $this->filterProfiles($profiles, self::defaultLimit($limit));
2659     }
2660
2661     public function getTotalCount()
2662     {
2663         return $this->getTotalProfileCount();
2664     }
2665 }
2666 // }}}
2667
2668 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
2669 ?>