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