#!/usr/bin/php5 <?php require_once 'connect.db.inc.php'; $globals->debug = 0; //do not store backtraces $terms = XDB::iterator('SELECT `jtid`, `name` FROM `profile_job_term_enum`'); while ($term = $terms->next()) { $tokens = array_unique(JobTerms::tokenize($term['name'])); if (!count($tokens)) { continue; } $values = array(); foreach ($tokens as $t) { $values[] = '(' . XDB::escape($t) . ',' . XDB::escape($term['jtid']) . ')'; } XDB::execute('INSERT IGNORE INTO `profile_job_term_search` (`search`,`jtid`) VALUES ' . implode(',', $values)); } /* vim:set et sw=4 sts=4 ts=4: */
/** * Page for url "profile/jobterms" (function also used for "referent/autocomplete" @see * handler_ref_autocomplete). Displays an "autocomplete" page (plain text with values * separated by "|" chars) for jobterms to add in profile. * @param $page the Platal page * @param $type set to 'mentor' to display the number of mentors for each term and order * by descending number of mentors. * * @param Env::v('q') the text that has been typed and to complete automatically */ function handler_jobterms($page, $type = 'nomentor') { pl_content_headers("text/plain"); $q = Env::v('term') . '%'; $tokens = JobTerms::tokenize($q); if (count($tokens) == 0) { exit; } sort($tokens); $q_normalized = implode(' ', $tokens); // Try to look in cached results. $cached = false; $cache = XDB::query('SELECT result FROM search_autocomplete WHERE name = {?} AND query = {?} AND generated > NOW() - INTERVAL 1 DAY', $type, $q_normalized); if ($cache->numRows() > 0) { $cached = true; $data = explode("\n", $cache->fetchOneCell()); $list = array(); foreach ($data as $line) { if ($line != '') { $aux = explode("\t", $line); if ($type == 'mentor') { $item = array('field' => $aux[0], 'nb' => $aux[1], 'id' => $aux[2]); $item['value'] = SearchModule::format_autocomplete($item); } else { $item = array('value' => $aux[0], 'id' => $aux[1]); } array_push($list, $item); } } } else { $joins = JobTerms::token_join_query($tokens, 'e'); if ($type == 'mentor') { $count = ', COUNT(DISTINCT pid) AS nb'; $countjoin = ' INNER JOIN profile_job_term_relation AS r ON(r.jtid_1 = e.jtid) INNER JOIN profile_mentor_term AS m ON(r.jtid_2 = m.jtid)'; $countorder = 'nb DESC, '; } else { $count = $countjoin = $countorder = ''; } $list = XDB::fetchAllAssoc('SELECT e.jtid AS id, e.full_name AS field' . $count . ' FROM profile_job_term_enum AS e ' . $joins . $countjoin . ' GROUP BY e.jtid ORDER BY ' . $countorder . 'field LIMIT ' . DirEnumeration::AUTOCOMPLETE_LIMIT); $to_cache = ''; if ($type == 'mentor') { foreach ($list as &$item) { $to_cache .= $item['field'] . "\t" . $item['nb'] . "\t" . $item['id'] . "\n"; $item['value'] = SearchModule::format_autocomplete($item); } } else { foreach ($list as &$item) { $to_cache .= $item['field'] . "\t" . $item['id'] . "\n"; $item['value'] = $item['field']; } } } if (count($list) == DirEnumeration::AUTOCOMPLETE_LIMIT && $type == 'nomentor') { $list[] = array('value' => '… parcourir les résultats dans un arbre …', 'field' => '', 'id' => -1); } if (!$cached) { XDB::query('INSERT INTO search_autocomplete (name, query, result, generated) VALUES ({?}, {?}, {?}, NOW()) ON DUPLICATE KEY UPDATE result = VALUES(result), generated = VALUES(generated)', $type, $q_normalized, $to_cache); } echo json_encode($list); exit; }
public function getAutoComplete($text, $sub_id = null) { $tokens = JobTerms::tokenize($text . '%'); if (count($tokens) == 0) { return array(); } $token_join = JobTerms::token_join_query($tokens, 'e'); return XDB::fetchAllAssoc('SELECT e.jtid AS id, e.full_name AS field, COUNT(DISTINCT p.pid) AS nb FROM profile_job_term_enum AS e INNER JOIN profile_job_term_relation AS r ON (r.jtid_1 = e.jtid) INNER JOIN profile_job_term AS p ON (r.jtid_2 = p.jtid) ' . $token_join . ' GROUP BY e.jtid ORDER BY nb DESC, field LIMIT ' . self::AUTOCOMPLETE_LIMIT); }
function handler_list($page, $type = null, $idVal = null) { $page->assign('name', $type); $page->assign('with_text_value', true); $page->assign('onchange', "document.forms.recherche.{$type}Txt.value = this.options[this.selectedIndex].text"); // Give the list of all values possible of type and builds a select input for it $ids = null; switch ($type) { case 'binet': $ids = DirEnum::getOptionsIter(DirEnum::BINETS); break; case 'networking_type': $ids = DirEnum::getOptionsIter(DirEnum::NETWORKS); break; case 'country': $ids = DirEnum::getOptionsIter(DirEnum::COUNTRIES); $page->assign('onchange', 'changeAddressComponents(\'' . $type . '\', this.value)'); break; case 'administrative_area_level_1': case 'administrative_area_level_2': case 'locality': $page->assign('onchange', 'changeAddressComponents(\'' . $type . '\', this.value)'); case 'postal_code': $ids = XDB::iterator("SELECT pace1.id, pace1.long_name AS field\n FROM profile_addresses_components_enum AS pace1\n INNER JOIN profile_addresses_components AS pac1 ON (pac1.component_id = pace1.id)\n INNER JOIN profile_addresses_components AS pac2 ON (pac1.pid = pac2.pid AND pac1.jobid = pac2.jobid AND pac1.id = pac2.id\n AND pac1.groupid = pac2.groupid AND pac1.type = pac2.type)\n INNER JOIN profile_addresses_components_enum AS pace2 ON (pac2.component_id = pace2.id AND FIND_IN_SET({?}, pace2.types))\n WHERE pace2.id = {?} AND FIND_IN_SET({?}, pace1.types) AND pac1.type = 'home'\n GROUP BY pace1.long_name", Env::v('previous'), Env::v('value'), $type); break; case 'diploma': if (Env::has('school') && Env::i('school') != 0) { $ids = DirEnum::getOptionsIter(DirEnum::EDUDEGREES, Env::i('school')); } else { $ids = DirEnum::getOptionsIter(DirEnum::EDUDEGREES); } break; case 'groupex': $ids = DirEnum::getOptionsIter(DirEnum::GROUPESX); break; case 'nationalite': $ids = DirEnum::getOptionsIter(DirEnum::NATIONALITIES); break; case 'school': $ids = DirEnum::getOptionsIter(DirEnum::EDUSCHOOLS); $page->assign('onchange', 'changeSchool(this.value)'); break; case 'section': $ids = DirEnum::getOptionsIter(DirEnum::SECTIONS); break; case 'jobterm': if (Env::has('jtid')) { JobTerms::ajaxGetBranch($page, JobTerms::ONLY_JOBS); return; } else { pl_content_headers('text/xml'); echo '<div>'; // global container so that response is valid xml echo '<input name="jobtermTxt" type="text" style="display:none" size="32" />'; echo '<input name="jobterm" type="hidden"/>'; echo '<div class="term_tree"></div>'; // container where to create the tree echo '<script type="text/javascript" src="javascript/jquery.jstree.js"></script>'; echo '<script type="text/javascript" src="javascript/jobtermstree.js"></script>'; echo '<script type="text/javascript">createJobTermsTree(".term_tree", "search/list/jobterm", "search", "searchForJobTerm");</script>'; echo '</div>'; exit; } default: exit; } if (isset($idVal)) { pl_content_headers("text/plain"); echo $ids[$idVal]; exit; } pl_content_headers("text/xml"); $page->changeTpl('include/field.select.tpl', NO_SKIN); $page->assign('list', $ids); }