+ // default search
+ $unique = '`user_id`';
+ $db = '`auth_user_md5`';
+ $realid = false;
+ $beginwith = true;
+ $field2 = false;
+ $qsearch = str_replace(array('%', '_'), '', $q);
+ $distinct = true;
+
+ switch ($type) {
+ case 'binetTxt':
+ $db = '`binets_def` INNER JOIN
+ `binets_ins` ON(`binets_def`.`id` = `binets_ins`.`binet_id`)';
+ $field = '`binets_def`.`text`';
+ if (strlen($q) > 2)
+ $beginwith = false;
+ $realid = '`binets_def`.`id`';
+ break;
+ case 'networking_typeTxt':
+ $db = '`profile_networking_enum` INNER JOIN
+ `profile_networking` ON(`profile_networking`.`network_type` = `profile_networking_enum`.`network_type`)';
+ $field = '`profile_networking_enum`.`name`';
+ $unique = 'uid';
+ $realid = '`profile_networking_enum`.`network_type`';
+ break;
+ case 'city':
+ $db = 'geoloc_localities INNER JOIN
+ profile_addresses ON (geoloc_localities.id = profile_addresses.localityId)';
+ $unique = 'uid';
+ $field ='geoloc_localities.name';
+ break;
+ case 'countryTxt':
+ $db = 'geoloc_countries INNER JOIN
+ profile_addresses ON (geoloc_countries.iso_3166_1_a2 = profile_addresses.countryId)';
+ $unique = 'pid';
+ $field = 'geoloc_countries.countryFR';
+ $realid = 'geoloc_countries.iso_3166_1_a2';
+ break;
+ case 'entreprise':
+ $db = 'profile_job_enum INNER JOIN
+ profile_job ON (profile_job.jobid = profile_job_enum.id)';
+ $field = 'profile_job_enum.name';
+ $unique = 'profile_job.uid';
+ break;
+ case 'fonctionTxt':
+ $db = 'fonctions_def INNER JOIN
+ profile_job ON (profile_job.fonctionid = fonctions_def.id)';
+ $field = 'fonction_fr';
+ $unique = 'uid';
+ $realid = 'fonctions_def.id';
+ $beginwith = false;
+ break;
+ case 'groupexTxt':
+ $db = "groupex.asso AS a INNER JOIN
+ groupex.membres AS m ON(a.id = m.asso_id
+ AND (a.cat = 'GroupesX' OR a.cat = 'Institutions')
+ AND a.pub = 'public')";
+ $field='a.nom';
+ $field2 = 'a.diminutif';
+ if (strlen($q) > 2)
+ $beginwith = false;
+ $realid = 'a.id';
+ $unique = 'm.uid';
+ break;
+ case 'nationaliteTxt':
+ $db = 'geoloc_countries INNER JOIN
+ auth_user_md5 ON (geoloc_countries.a2 = auth_user_md5.nationalite
+ OR geoloc_countries.a2 = auth_user_md5.nationalite2
+ OR geoloc_countries.a2 = auth_user_md5.nationalite3)';
+ $field = 'geoloc_countries.nationalityFR';
+ $realid = 'geoloc_countries.iso_3166_1_a2';
+ break;
+ case 'description':
+ $db = 'profile_job';
+ $field = 'description';
+ $unique = 'uid';
+ break;
+ case 'schoolTxt':
+ $db = 'profile_education_enum INNER JOIN
+ profile_education ON (profile_education_enum.id = profile_education.eduid)';
+ $field = 'profile_education_enum.name';
+ $unique = 'uid';
+ $realid = 'profile_education_enum.id';
+ if (strlen($q) > 2)
+ $beginwith = false;
+ break;
+ case 'secteurTxt':
+ $db = 'profile_job_sector_enum INNER JOIN
+ profile_job ON (profile_job.sectorid = profile_job_sector_enum.id)';
+ $field = 'profile_job_sector_enum.name';
+ $realid = 'profile_job_sector_enum.id';
+ $unique = 'uid';
+ $beginwith = false;
+ break;
+ case 'subSubSector':
+ $db = 'profile_job_subsubsector_enum';
+ $field = 'name';
+ $beginwith = false;
+ $unique = 'name';
+ $distinct = false;
+ break;
+ case 'sectionTxt':
+ $db = '`sections` INNER JOIN
+ `auth_user_md5` ON(`auth_user_md5`.`section` = `sections`.`id`)';
+ $field = '`sections`.`text`';
+ $realid = '`sections`.`id`';
+ $beginwith = false;
+ break;
+ default: exit();
+ }
+
+ function make_field_test($fields, $beginwith) {
+ $tests = array();
+ $tests[] = $fields . ' LIKE CONCAT({?}, \'%\')';
+ if (!$beginwith) {
+ $tests[] = $fields . ' LIKE CONCAT(\'% \', {?}, \'%\')';
+ $tests[] = $fields . ' LIKE CONCAT(\'%-\', {?}, \'%\')';
+ }
+ return '(' . implode(' OR ', $tests) . ')';
+ }
+ $field_select = $field;
+ $field_t = make_field_test($field, $beginwith);
+ if ($field2) {
+ $field2_t = make_field_test($field2, $beginwith);
+ $field_select = 'IF(' . $field_t . ', ' . $field . ', ' . $field2. ')';
+ }
+ $list = XDB::iterator('SELECT ' . $field_select . ' AS field'
+ . ($distinct ? (', COUNT(DISTINCT ' . $unique . ') AS nb') : '')
+ . ($realid ? (', ' . $realid . ' AS id') : '') . '
+ FROM ' . $db . '
+ WHERE ' . $field_t .
+ ($field2 ? (' OR ' . $field2_t) : '') . '
+ GROUP BY ' . $field_select . '
+ ORDER BY ' . ($distinct ? 'nb DESC' : $field_select) . '
+ LIMIT 11',
+ $qsearch, $qsearch, $qsearch, $qsearch, $qsearch, $qsearch, $qsearch, $qsearch,
+ $qsearch, $qsearch, $qsearch, $qsearch, $qsearch, $qsearch, $qsearch, $qsearch);
+
+ $nbResults = 0;
+ $res = "";
+ while ($result = $list->next()) {
+ $nbResults++;
+ if ($nbResults == 11) {
+ $res .= $q."|-1\n";
+ } else {
+ $res .= $result['field'].'|';
+ if (isset($result['nb'])) {
+ $res .= $result['nb'];
+ }
+ if (isset($result['id'])) {
+ $res .= '|'.$result['id'];
+ }
+ $res .= "\n";
+ }
+ }
+ XDB::query('REPLACE INTO `search_autocomplete`
+ VALUES ({?}, {?}, {?}, NOW())',
+ $type, $q, $res);
+ echo $res;
+ exit();
+ }
+
+ function handler_list(&$page, $type = null, $idVal = null)
+ {
+ // Give the list of all values possible of type and builds a select input for it
+ $field = '`text`';
+ $id = '`id`';
+ $where = '';
+
+ switch ($type) {
+ case 'binet':
+ $db = '`binets_def`';
+ break;
+ case 'networking_type':
+ $db = '`profile_networking_enum`';
+ $field = '`name`';
+ $id = '`network_type`';
+ break;
+ case 'country':
+ $db = 'geoloc_countries';
+ $field = 'countryFR';
+ $id = 'iso_3166_1_a2';
+ $page->assign('onchange', 'changeCountry(this.value)');
+ break;
+ case 'fonction':
+ $db = '`fonctions_def`';
+ $field = '`fonction_fr`';
+ break;
+ case 'diploma':
+ header('Content-Type: text/xml; charset="UTF-8"');
+ $this->get_diplomas();
+ $page->changeTpl('search/adv.grade.form.tpl', NO_SKIN);
+ return;
+ case 'groupex':
+ $db = 'groupex.asso';
+ $where = " WHERE (cat = 'GroupesX' OR cat = 'Institutions') AND pub = 'public'";
+ $field = 'nom';
+ break;
+ case 'nationalite':
+ $db = 'geoloc_countries INNER JOIN
+ auth_user_md5 ON (geoloc_countries.iso_3166_1_a2 = auth_user_md5.nationalite
+ OR geoloc_countries.iso_3166_1_a2 = auth_user_md5.nationalite2
+ OR geoloc_countries.iso_3166_1_a2 = auth_user_md5.nationalite3)';
+ $field = 'nationalityFR';
+ $id = 'iso_3166_1_a2';
+ break;
+ case 'region':
+ $db = 'geoloc_administrativeareas';
+ $field = 'name';
+ $id = 'id';
+ if (isset($_REQUEST['country'])) {
+ $where .= ' WHERE country = "' . $_REQUEST['country'] . '"';
+ }
+ break;
+ case 'school':
+ $db = 'profile_education_enum';
+ $field = 'name';
+ $id = 'id';
+ $page->assign('onchange', 'changeSchool(this.value)');
+ break;
+ case 'section':
+ $db = '`sections`';
+ break;
+ case 'secteur':
+ $db = 'profile_job_sector_enum INNER JOIN
+ profile_job ON (profile_job.sectorid = profile_job_sector_enum.id)';
+ $field = 'profile_job_sector_enum.name';
+ $id = 'profile_job_sector_enum.id';
+ break;
+ default: exit();
+ }
+ if (isset($idVal)) {
+ header('Content-Type: text/plain; charset="UTF-8"');
+ $result = XDB::query('SELECT '.$field.' AS field FROM '.$db.' WHERE '.$id.' = {?} LIMIT 1',$idVal);
+ echo $result->fetchOneCell();
+ exit();
+ }
+ header('Content-Type: text/xml; charset="UTF-8"');
+ $page->changeTpl('include/field.select.tpl', NO_SKIN);
+ $page->assign('name', $type);
+ $page->assign('list', XDB::iterator('SELECT '.$field.' AS field,
+ '.$id.' AS id
+ FROM '.$db.$where.'
+ GROUP BY '.$field.'
+ ORDER BY '.$field));
+ $page->assign('with_text_value', true);
+ $page->assign('onchange', "document.forms.recherche.{$type}Txt.value = this.options[this.selectedIndex].text");