include/directory.enums.inc.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 // {{{ class DirEnum
  23 /** This class stores all data for the different kinds of fields.
  24  * It is only a dispatcher for the various DirEnum_XXX classes.
  25  */
  26 class DirEnum
  27 {
  28     /** Name of availables Enumerations
  29      * Each of these consts contains the basename of the class (its full name
  30      * being DE_$basename).
  31      */
  32     const BINETS         = 'binets';
  33     const GROUPESX       = 'groupesx';
  34     const SECTIONS       = 'sections';
  35
  36     const SCHOOLS        = 'schools';
  37     const DEGREES        = 'degrees';
  38     const STUDIESDOMAINS = 'studiesdomains';
  39
  40     const NATIONALITIES  = 'nationalities';
  41     const COUNTRIES      = 'countries';
  42     const ADMINAREAS     = 'adminareas';
  43     const LOCALITIES     = 'localities';
  44
  45     const COMPANIES      = 'companies';
  46     const SECTORS        = 'sectors';
  47     const JOBDESCRIPTION = 'jobdescription';
  48
  49     const NETWORKS       = 'networking';
  50
  51     static private $enumerations = array();
  52
  53     static private function init($type)
  54     {
  55         $cls = "DE_" . ucfirst($type);
  56         self::$enumerations[$type] = new $cls();
  57     }
  58
  59     /** Retrieves all options for a given type
  60      * @param $type Type of enum for which options are requested
  61      * @return XorgDbIterator over the results
  62      * TODO : Find a way to get either an array, or the adequate PlIterator
  63      */
  64     static public function getOptions()
  65     {
  66         $args = func_get_args();
  67         $type = array_shift($args);
  68         if (!array_key_exists($type, self::$enumerations)) {
  69             self::init($type);
  70         }
  71         $obj = self::$enumerations[$type];
  72         return call_user_func_array(array($obj, 'getOptions'), $args);
  73     }
  74
  75     /** Retrieves all options with number of profiles for autocompletion
  76      * @param $type Type of enum for which options are requested
  77      * @param $text Text to autocomplete
  78      * @return XorgDbIterator over the results
  79      */
  80     static public function getAutoComplete()
  81     {
  82         $args = func_get_args();
  83         $type = array_shift($args);
  84         if (!array_key_exists($type, self::$enumerations)) {
  85             self::init($type);
  86         }
  87         $obj = self::$enumerations[$type];
  88         return call_user_func_array(array($obj, 'getAutoComplete'), $args);
  89     }
  90
  91     /** Retrieves a list of IDs for a given type
  92      * @param $type Type of enum for which IDs are requested
  93      * @param $text Text to search in enum valuees
  94      * @param $mode Mode of search for those IDs (prefix/suffix/infix)
  95      */
  96     static public function getIDs()
  97     {
  98         $args = func_get_args();
  99         $type = array_shift($args);
 100         if (!array_key_exists($type, self::$enumerations)) {
 101             self::init($type);
 102         }
 103         $obj = self::$enumerations[$type];
 104         return call_user_func_array(array($obj, 'getIDs'), $args);
 105     }
 106 }
 107 // }}}
 108
 109 // {{{ class DirEnumeration
 110 abstract class DirEnumeration
 111 {
 112     /** Modes for LIKE searches
 113      */
 114     const MODE_EXACT    = 0x000;
 115     const MODE_PREFIX   = 0x001;
 116     const MODE_SUFFIX   = 0x002;
 117     const MODE_CONTAINS = 0x003;
 118
 119     /** An internal array of ID => optionTxt
 120      */
 121     protected $options = null;
 122
 123     /** Description of the MySQL storage of the fields
 124      */
 125     protected $idfield  = 'id';
 126     protected $valfield = 'text';
 127     protected $valfield2 = null;
 128     protected $from;
 129     protected $join = '';
 130     protected $where = '';
 131
 132     /** Fields for autocompletion
 133      */
 134     protected $ac_join = ''; // Additional joins
 135     protected $ac_where = null; // Additional where
 136     protected $ac_beginwith = true; // Whether to search for 'x%' or for '%x%'
 137     protected $ac_unique; // Which field is to be taken as unique
 138     protected $ac_distinct = true; // Whether we want to keep only distinct valfield value
 139     protected $ac_withid = true; // Do we want to fetch id too ?
 140
 141     protected function _fetchOptions()
 142     {
 143         if (is_null($this->options)) {
 144             $this->loadOptions();
 145         }
 146     }
 147
 148     public function getOptions()
 149     {
 150         $this->_fetchOptions();
 151         return $this->options;
 152     }
 153
 154     // {{{ function getIDs
 155     /** Retrieves possible IDs for given text
 156      * @param $text Text to search for IDs
 157      * @param $mode Mode of search (PREFIX, SUFFIX, CONTAINS)
 158      * @return An array of matching IDs ; if empty, input should be considered invalid
 159      */
 160     public function getIDs($text, $mode)
 161     {
 162         if ($mode == self::MODE_EXACT) {
 163             $options = $this->getOptions();
 164             return array_keys($options, $text);
 165         } else {
 166             if ($this->where == null) {
 167                 $where = 'WHERE ';
 168             } else {
 169                 $where = $this->where . ' AND ';
 170             }
 171             $conds = array();
 172             $conds[] = $this->valfield . self::makeSqlConcat($text, $mode);
 173             if ($this->valfield2 != null) {
 174                 $conds[] = $this->valfield2 . self::makeSqlConcat($text, $mode);
 175             }
 176             $where .= '(' . implode(' OR ', $conds) . ')';
 177
 178             return XDB::fetchColumn('SELECT ' . $this->idfield . '
 179                                        FROM ' . $this->from . '
 180                                             ' . $this->join . '
 181                                             ' . $where . '
 182                                    GROUP BY ' . $this->idfield);
 183         }
 184     }
 185     // }}}
 186
 187     private function mkTests($field, $text)
 188     {
 189         $tests = array();
 190         $tests[] = $field . self::makeSqlConcat($text, self::MODE_PREFIX);
 191         if (!$this->ac_beginwith) {
 192             $tests[] = $field . self::makeSqlConcat(' ' . $text, self::MODE_CONTAINS);
 193             $tests[] = $field . self::makeSqlConcat('-' . $text, self::MODE_CONTAINS);
 194         }
 195         return $tests;
 196     }
 197
 198     // {{{ function getAutoComplete
 199     public function getAutoComplete($text)
 200     {
 201         $text = str_replace(array('%', '_'), '', $text);
 202
 203         if (is_null($this->ac_where) || $this->ac_where == '') {
 204             $where = '';
 205         } else {
 206             $where = $this->ac_where . ' AND ';
 207         }
 208
 209         $tests = $this->mkTests($this->valfield, $text);
 210         if (!is_null($this->valfield2)) {
 211             $tests = array_merge($tests, $this->mkTests($this->valfield2, $text));
 212         }
 213
 214         $where .= '(' . implode(' OR ', $tests) . ')';
 215
 216         return XDB::iterator('SELECT ' . $this->valfield . ' AS field'
 217                                        . ($this->ac_distinct ? (', COUNT(DISTINCT ' . $this->ac_unique . ') AS nb') : '')
 218                                        . ($this->ac_withid ? (', ' . $this->idfield . ' AS id') : '') . '
 219                                 FROM ' . $this->from . '
 220                                      ' . $this->ac_join . '
 221                                WHERE ' . $where . '
 222                             GROUP BY ' . $this->valfield . '
 223                             ORDER BY ' . ($this->ac_distinct ? 'nb DESC' : $this->valfield) . '
 224                                LIMIT 11');
 225     }
 226     // }}}
 227
 228     // {{{ function makeSqlConcat
 229     static protected function makeSqlConcat($text, $mode)
 230     {
 231         if ($mode == self::MODE_EXACT) {
 232             return ' = ' . XDB::format('{?}', $text);
 233         }
 234         if ($mode == self::MODE_PREFIX) {
 235             $right = XDB::format('CONCAT({?}, \'%\')', $text);
 236         } else if ($mode == self::MODE_SUFFIX) {
 237             $right = XDB::format('CONCAT(\'%\', {?})', $text);
 238         } else {
 239             $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $text);
 240         }
 241         return ' LIKE ' . $right;
 242     }
 243     // }}}
 244
 245     // {{{ function loadOptions
 246     /** The function used to load options
 247      */
 248     protected function loadOptions()
 249     {
 250         $this->options = XDB::iterator('SELECT ' . $this->valfield . ' AS field,
 251                                                ' . $this->idfield . ' AS id
 252                                           FROM ' . $this->from . '
 253                                                ' . $this->join . '
 254                                                ' . $this->where . '
 255                                       GROUP BY ' . $this->valfield . '
 256                                       ORDER BY ' . $this->valfield);
 257     }
 258     // }}}
 259 }
 260 // }}}
 261
 262 /** GROUPS
 263  */
 264 // {{{ class DE_Binets
 265 class DE_Binets extends DirEnumeration
 266 {
 267     protected $from = 'binets_def';
 268
 269     protected $ac_join = 'INNER JOIN binets_ins ON (binets_def.id = binets_ins.binet_id)';
 270     protected $ac_unique = 'binets_ins.user_id';
 271 }
 272 // }}}
 273
 274 // {{{ class DE_Sections
 275 class DE_Sections extends DirEnumeration
 276 {
 277     protected $from = 'sections';
 278
 279     protected $ac_join = 'INNER JOIN profiles ON (profiles.section = sections.id)';
 280     protected $ac_unique = 'profiles.pid';
 281 }
 282 // }}}
 283
 284 // {{{ class DE_GroupesX
 285 class DE_GroupesX extends DirEnumeration
 286 {
 287     protected $idfield   = 'asso.id';
 288     protected $valfield  = 'asso.nom';
 289     protected $valfield2 = 'asso.diminutif';
 290     protected $from      = '#groupex#.asso AS asso';
 291     protected $where     = 'WHERE (cat = \'GroupesX\' OR cat = \'Institutions\') AND pub = \'public\'';
 292
 293     protected $ac_join   = "INNER JOIN #groupex#.membres AS memb ON (asso.id = memb.asso_id
 294                                     AND (asso.cat = 'GroupesX' OR asso.cat = 'Institutions')
 295                                     AND asso.pub = 'public')";
 296     protected $ac_unique = 'memb.uid';
 297 }
 298 // }}}
 299
 300 /** EDUCATION
 301  */
 302 // {{{ class DE_Schools
 303 class DE_Schools extends DirEnumeration
 304 {
 305     protected $valfield  = 'name';
 306     protected $valfield2 = 'abbreviation';
 307     protected $from      = 'profile_education_enum';
 308
 309     protected $ac_join   = 'INNER JOIN profile_education ON (profile_education.eduid = profile_education_enum.id)';
 310     protected $ac_unique = 'profile_education.uid';
 311 }
 312 // }}}
 313
 314 // {{{ class DE_Degrees
 315 class DE_Degrees extends DirEnumeration
 316 {
 317     protected $from = 'profile_education_degree_enum';
 318     protected $valfield = 'degree';
 319
 320     protected $suboptions = array();
 321
 322     protected function loadOptions()
 323     {
 324         $res = XDB::query('SELECT ped.eduid, ped.degreeid, pede.degree
 325                              FROM profile_education_enum AS pee
 326                         LEFT JOIN profile_education_degree AS ped ON (pee.id = ped.eduid)
 327                         LEFT JOIN profile_education_degree_enum AS pede ON (ped.degreeid = pede.id)
 328                          ORDER BY pede.degree');
 329         foreach($res->fetchAllRow() as $row) {
 330             list($eduid, $degreeid, $name) = $row;
 331             $this->options[$degreeid] = array('id' => $degreeid, 'field' => $name);
 332             if (!array_key_exists($eduid, $this->suboptions)) {
 333                 $this->suboptions[$eduid] = array();
 334             }
 335             $this->suboptions[$eduid][] = array('id' => $degreeid, 'field' => $name);
 336         }
 337     }
 338
 339     public function getOptions($eduid = null)
 340     {
 341         $this->_fetchOptions();
 342         if ($eduid == null) {
 343             return PlIteratorUtils::fromArray($this->options, 1, true);
 344         }
 345         if (array_key_exists($eduid, $this->suboptions)) {
 346             return PlIteratorUtils::fromArray($this->suboptions[$eduid], 1, true);
 347         } else {
 348             return array();
 349         }
 350     }
 351
 352     public function getIDs($text, $mode, $eduid = null)
 353     {
 354         if ($eduid == null) {
 355             return XDB::fetchColumn('SELECT id
 356                                        FROM profile_education_degree_enum
 357                                        WHERE degree ' . self::makeSqlConcat($text, $mode));
 358         } else {
 359             return XDB::fetchColumn('SELECT pede.id
 360                                        FROM profile_education_degree AS ped
 361                                   LEFT JOIN profile_education_degree_enum AS pede ON (ped.degreeid = pede.id)
 362                                       WHERE ped.eduid = {?} AND pede.degree ' . self::makeSqlConcat($text, $mode), $eduid);
 363         }
 364     }
 365 }
 366 // }}}
 367
 368 // {{{ class DE_StudiesSector
 369 class DE_StudiesSector extends DirEnumeration
 370 {
 371     protected $valfield = 'field';
 372     protected $from     = 'profile_education_field_enum';
 373
 374     protected $ac_join   = 'INNER JOIN profile_education ON (profile_education.fieldid = profile_education_field_enum.id)';
 375     protected $ac_unique = 'profile_education.uid';
 376 }
 377 // }}}
 378
 379 /** GEOLOC
 380  */
 381 // {{{ class DE_Nationalities
 382 class DE_Nationalities extends DirEnumeration
 383 {
 384     protected $idfield   = 'iso_3166_1_a2';
 385     protected $valfield  = 'nationalityFR';
 386     protected $valfield2 = 'nationality';
 387     protected $from      = 'geoloc_countries AS gc';
 388     protected $join      = 'INNER JOIN profiles AS p ON (gc.iso_3166_1_a2 IN (p.nationality1, p.nationality2, p.nationality3))';
 389
 390     protected $ac_join   = 'INNER JOIN profiles AS p ON (gc.iso_3166_1_a2 IN (p.nationality1, p.nationality2, p.nationality3))';
 391     protected $ac_unique = 'profiles.pid';
 392 }
 393 // }}}
 394
 395 // {{{ class DE_Countries
 396 class DE_Countries extends DirEnumeration
 397 {
 398     protected $idfield   = 'iso_3166_1_a2';
 399     protected $valfield  = 'countryFR';
 400     protected $valfield2 = 'country';
 401     protected $from      = 'geoloc_countries';
 402
 403     protected $ac_join   = 'INNER JOIN profile_addresses ON (geoloc_countries.iso_3166_1_a2 = profile_addresses.countryFR';
 404     protected $ac_unique = 'profile_addresses.pid';
 405 }
 406 // }}}
 407
 408 // {{{ class DE_AdminAreas
 409 class DE_AdminAreas extends DirEnumeration
 410 {
 411     protected $suboptions = array();
 412
 413     protected function loadOptions()
 414     {
 415         $res = XDB::query('SELECT id, name AS field, country
 416                              FROM geoloc_administrativeareas
 417                          GROUP BY name
 418                          ORDER BY name');
 419         foreach($res->fetchAllRow() as $row) {
 420             list($id, $field, $country) = $row;
 421             $this->options[] = array('id' => $id, 'field' => $field);
 422             if (!array_key_exists($country, $this->suboptions)) {
 423                 $this->suboptions[$country] = array();
 424             }
 425             $this->suboptions[$country][] = array('id' => $id, 'field' => $field);
 426         }
 427     }
 428
 429     public function getOptions($country = null)
 430     {
 431         $this->_fetchOptions();
 432
 433         if ($country == null) {
 434             return PlIteratorUtils::fromArray($this->options, 1, true);
 435         }
 436         if (array_key_exists($country, $this->suboptions)) {
 437             return PlIteratorUtils::fromArray($this->suboptions[$country], 1, true);
 438         } else {
 439             return array();
 440         }
 441     }
 442
 443     public function getIDs($text, $mode, $country = null)
 444     {
 445         if ($country == null) {
 446             return XDB::fetchColumn('SELECT id
 447                                        FROM geoloc_administrativeareas
 448                                        WHERE name ' . self::makeSqlConcat($text, $mode));
 449         } else {
 450             return XDB::fetchColumn('SELECT id
 451                                        FROM geoloc_administrativeareas
 452                                       WHERE country = {?} AND name' . self::makeSqlConcat($text, $mode), $country);
 453         }
 454     }
 455 }
 456 // }}}
 457
 458 // {{{ class DE_Localities
 459 class DE_Localities extends DirEnumeration
 460 {
 461     protected $valfield  = 'gl.name';
 462     protected $from      = 'geoloc_localities AS gl';
 463
 464     protected $ac_join   = 'profile_addresses AS pa ON (pa.localityID = gl.id)';
 465     protected $ac_unique = 'pa.pid';
 466 }
 467 // }}}
 468
 469 /** JOBS
 470  */
 471 // {{{ class DE_Companies
 472 class DE_Companies extends DirEnumeration
 473 {
 474     protected $valfield  = 'pje.name';
 475     protected $valfield2 = 'pje.acronym';
 476     protected $from      = 'profile_job_enum AS pje';
 477
 478     protected $ac_join   = 'INNER JOIN profile_job AS pj ON (pj.jobid = pje.id)';
 479     protected $ac_unique = 'pj.uid';
 480 }
 481 // }}}
 482
 483 // {{{ class DE_Sectors
 484 class DE_Sectors extends DirEnumeration
 485 {
 486     protected $valfield  = 'name';
 487     protected $from      = 'profile_job_sector_enum';
 488
 489     protected $ac_join   = 'INNER JOIN profile_job ON (profile_job_sector_enum.id = profile_job.sectorid)';
 490     protected $ac_unique = 'profile_job.uid';
 491 }
 492 // }}}
 493
 494 // {{{ class DE_JobDescription
 495 class DE_JobDescription
 496 {
 497     protected $valfield = 'pj.description';
 498     protected $from     = 'profile_job AS pj';
 499     protected $idfield  = 'pj.pid';
 500
 501     protected $ac_unique = 'pj.pid';
 502 }
 503 // }}}
 504
 505 /** NETWORKING
 506  */
 507 // {{{ class DE_Networking
 508 class DE_Networking extends DirEnumeration
 509 {
 510     protected $idfield  = 'profile_networking_enum.network_type';
 511     protected $valfield = 'profile_networking_enum.name';
 512     protected $from     = 'profile_networking_enum';
 513
 514
 515     protected $ac_join   = 'INNER JOIN profile_networking ON (profile_networking.network_type = profile_networking_enum.network_type';
 516     protected $ac_unique = 'profile_networking.uid';
 517 }
 518 // }}}
 519 ?>