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