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