/**
* Returns all relevant articles for a FAQ record with the same language
*
* @param integer $recordId FAQ ID
* @param string $question FAQ title
* @param string $keywords FAQ keywords
*
* @return array
*/
public function getAllRelatedById($recordId, $question, $keywords)
{
$terms = str_replace('-', ' ', $question) . $keywords;
$search = PMF_Search_Factory::create($this->_config, array('database' => PMF_Db::getType()));
$search->setTable(PMF_Db::getTablePrefix() . 'faqdata AS fd')->setResultColumns(array('fd.id AS id', 'fd.lang AS lang', 'fcr.category_id AS category_id', 'fd.thema AS question', 'fd.content AS answer'))->setJoinedTable(PMF_Db::getTablePrefix() . 'faqcategoryrelations AS fcr')->setJoinedColumns(array('fd.id = fcr.record_id', 'fd.lang = fcr.record_lang'))->setConditions(array('fd.active' => "'yes'", 'fd.lang' => "'" . $this->_config->getLanguage()->getLanguage() . "'"))->setMatchingColumns(array('fd.thema', 'fd.content', 'fd.keywords'));
$result = $search->search($terms);
return $this->_config->getDb()->fetchAll($result);
}
/**
* Delete old captcha records.
*
* During normal use the <b>faqcaptcha</b> table would be empty, on average:
* each record is created when a captcha image is showed to the user
* and deleted upon a successful matching, so, on average, a record
* in this table is probably related to a spam attack.
*
* @param int $time The time (sec) to define a captcha code old and ready
* to be deleted (default: 1 week)
* @return void
*/
private function garbageCollector($time = 604800)
{
$delete = sprintf("\n DELETE FROM \n %sfaqcaptcha \n WHERE \n captcha_time < %d", PMF_Db::getTablePrefix(), $_SERVER['REQUEST_TIME'] - $time);
$this->_config->getDb()->query($delete);
$delete = sprintf("\n DELETE FROM\n %sfaqcaptcha\n WHERE\n useragent = '%s' AND language = '%s' AND ip = '%s'", PMF_Db::getTablePrefix(), $this->userAgent, $this->_config->getLanguage()->getLanguage(), $this->ip);
$this->_config->getDb()->query($delete);
}
/**
* Deletes an item and definition into the database
*
* @param integer $id Glossary ID
*
* @return boolean
*/
public function deleteGlossaryItem($id)
{
$query = sprintf("\n DELETE FROM\n %sfaqglossary\n WHERE\n id = %d AND lang = '%s'", PMF_Db::getTablePrefix(), (int) $id, $this->config->getLanguage()->getLanguage());
if ($this->config->getDb()->query($query)) {
return true;
}
return false;
}
/**
* Deletes a news entry identified by its ID
*
* @todo check if there are comments attached to the deleted news
*
* @param integer $id News ID
*
* @return boolean
*/
function deleteNews($id)
{
$query = sprintf("DELETE FROM\n %sfaqnews\n WHERE\n id = %d\n AND\n lang = '%s'", PMF_Db::getTablePrefix(), $id, $this->_config->getLanguage()->getLanguage());
if (!$this->_config->getDb()->query($query)) {
return false;
}
return true;
}
/**
* Logging of search terms for improvements
*
* @param string $searchTerm Search term
* @return void
*/
public function logSearchTerm($searchTerm)
{
if (PMF_String::strlen($searchTerm) == 0) {
return;
}
$date = new DateTime();
$query = sprintf("\n INSERT INTO\n %s\n (id, lang, searchterm, searchdate)\n VALUES\n (%d, '%s', '%s', '%s')", $this->_table, $this->_config->getDb()->nextId($this->_table, 'id'), $this->_config->getLanguage()->getLanguage(), $this->_config->getDb()->escape($searchTerm), $date->format('Y-m-d H:i:s'));
$this->_config->getDb()->query($query);
}
/**
* Updates an entry in the table faqvisits
*
* @param integer $id id
* @return boolean
*/
private function update($id)
{
if (!is_numeric($id)) {
return false;
}
$query = sprintf("\n UPDATE\n %sfaqvisits\n SET\n visits = visits+1,\n last_visit = %d\n WHERE\n id = %d AND lang = '%s'", PMF_Db::getTablePrefix(), $_SERVER['REQUEST_TIME'], $id, $this->_config->getLanguage()->getLanguage());
$this->_config->getDb()->query($query);
return true;
}
/**
* Retrieve all the stop words by a certain language
*
* @param string $lang Language to retrieve stop words by
* @param boolean $wordsOnly
*
* @return array
*/
public function getByLang($lang = null, $wordsOnly = false)
{
$lang = is_null($lang) ? $this->_config->getLanguage()->getLanguage() : $lang;
$sql = sprintf("SELECT id, lang, LOWER(stopword) AS stopword FROM {$this->table_name} WHERE lang = '%s'", $lang);
$result = $this->_config->getDb()->query($sql);
$retval = [];
if ($wordsOnly) {
while (($row = $this->_config->getDb()->fetchObject($result)) == true) {
$retval[] = $row->stopword;
}
} else {
return $this->_config->getDb()->fetchAll($result);
}
return $retval;
}
/**
* Create all languagess which can be used for translation as <option>
*
* @param integer $category_id Category id
* @param string $selected_lang Selected language
*
* @return string
*/
public function getCategoryLanguagesToTranslate($category_id, $selected_lang)
{
$output = '';
$existcatlang = $this->_config->getLanguage()->languageAvailable($category_id, 'faqcategories');
foreach (PMF_Language::getAvailableLanguages() as $lang => $langname) {
if (!in_array(strtolower($lang), $existcatlang)) {
$output .= "\t<option value=\"" . strtolower($lang) . "\"";
if ($lang == $selected_lang) {
$output .= " selected=\"selected\"";
}
$output .= ">" . $langname . "</option>\n";
}
}
return $output;
}
/**
* @param integer $limit Specify the maximum amount of records to return
*
* @return Array $tagId => $tagFrequency
*/
public function getPopularTags($limit = 0)
{
$tags = [];
$query = sprintf("\n SELECT\n COUNT(record_id) as freq, tagging_id\n FROM\n %sfaqdata_tags\n JOIN\n %sfaqdata ON id = record_id\n WHERE\n lang = '%s'\n GROUP BY tagging_id\n ORDER BY freq DESC", PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), $this->_config->getLanguage()->getLanguage());
$result = $this->_config->getDb()->query($query);
if ($result) {
while ($row = $this->_config->getDb()->fetchObject($result)) {
$tags[$row->tagging_id] = $row->freq;
if (--$limit === 0) {
break;
}
}
}
return $tags;
}
/**
* Save the Captcha
*
* @return boolean
*/
private function saveCaptcha()
{
$select = sprintf("\n SELECT \n id \n FROM \n %sfaqcaptcha \n WHERE \n id = '%s'", PMF_Db::getTablePrefix(), $this->code);
$result = $this->_config->getDb()->query($select);
if ($result) {
$num = $this->_config->getDb()->numRows($result);
if ($num > 0) {
return false;
} else {
$insert = sprintf("\n INSERT INTO \n %sfaqcaptcha \n (id, useragent, language, ip, captcha_time) \n VALUES \n ('%s', '%s', '%s', '%s', %d)", PMF_Db::getTablePrefix(), $this->code, $this->userAgent, $this->_config->getLanguage()->getLanguage(), $this->ip, $this->timestamp);
$this->_config->getDb()->query($insert);
return true;
}
}
return false;
}
/**
* Returns all records from the current first letter
*
* @param string $letter Letter
* @return array
* @since 2007-03-30
* @author Thorsten Rinne <*****@*****.**>
*/
public function getRecordsFromLetter($letter = 'A')
{
global $sids, $PMF_LANG;
if ($this->groupSupport) {
$permPart = sprintf("( fdg.group_id IN (%s)\n OR\n (fdu.user_id = %d AND fdg.group_id IN (%s)))", implode(', ', $this->groups), $this->user, implode(', ', $this->groups));
} else {
$permPart = sprintf("( fdu.user_id = %d OR fdu.user_id = -1 )", $this->user);
}
$letter = PMF_String::strtoupper($this->_config->getDb()->escape(PMF_String::substr($letter, 0, 1)));
$writeMap = '';
switch ($this->type) {
case 'db2':
case 'sqlite':
$query = sprintf("\n SELECT\n fd.thema AS thema,\n fd.id AS id,\n fd.lang AS lang,\n fcr.category_id AS category_id,\n fd.content AS snap\n FROM\n %sfaqcategoryrelations fcr,\n %sfaqdata fd\n LEFT JOIN\n %sfaqdata_group AS fdg\n ON\n fd.id = fdg.record_id\n LEFT JOIN\n %sfaqdata_user AS fdu\n ON\n fd.id = fdu.record_id\n WHERE\n fd.id = fcr.record_id\n AND\n SUBSTR(fd.thema, 1, 1) = '%s'\n AND\n fd.lang = '%s'\n AND\n fd.active = 'yes'\n AND\n %s", PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), $letter, $this->_config->getLanguage()->getLanguage(), $permPart);
break;
default:
$query = sprintf("\n SELECT\n fd.thema AS thema,\n fd.id AS id,\n fd.lang AS lang,\n fcr.category_id AS category_id,\n fd.content AS snap\n FROM\n %sfaqcategoryrelations fcr,\n %sfaqdata fd\n LEFT JOIN\n %sfaqdata_group AS fdg\n ON\n fd.id = fdg.record_id\n LEFT JOIN\n %sfaqdata_user AS fdu\n ON\n fd.id = fdu.record_id\n WHERE\n fd.id = fcr.record_id\n AND\n SUBSTRING(fd.thema, 1, 1) = '%s'\n AND\n fd.lang = '%s'\n AND\n fd.active = 'yes'\n AND\n %s", PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), $letter, $this->_config->getLanguage()->getLanguage(), $permPart);
break;
}
$result = $this->_config->getDb()->query($query);
$oldId = 0;
while ($row = $this->_config->getDb()->fetchObject($result)) {
if ($oldId != $row->id) {
$title = PMF_String::htmlspecialchars($row->thema, ENT_QUOTES, 'utf-8');
$url = sprintf('%s?%saction=artikel&cat=%d&id=%d&artlang=%s', PMF_Link::getSystemRelativeUri(), $sids, $row->category_id, $row->id, $row->lang);
$oLink = new PMF_Link($url, $this->_config);
$oLink->itemTitle = $row->thema;
$oLink->text = $title;
$oLink->tooltip = $title;
$writeMap .= '<li>' . $oLink->toHtmlAnchor() . '<br />' . "\n";
$writeMap .= PMF_Utils::chopString(strip_tags($row->snap), 25) . " ...</li>\n";
}
$oldId = $row->id;
}
$writeMap = empty($writeMap) ? '' : '<ul>' . $writeMap . '</ul>';
return $writeMap;
}
/**
* Returns the sticky records with URL and Title
*
* @return array
*/
private function getStickyRecordsData()
{
global $sids;
$now = date('YmdHis');
$query = sprintf("\n SELECT\n fd.id AS id,\n fd.lang AS lang,\n fd.thema AS thema,\n fcr.category_id AS category_id\n FROM\n %sfaqdata fd\n LEFT JOIN\n %sfaqcategoryrelations fcr\n ON\n fd.id = fcr.record_id\n AND\n fd.lang = fcr.record_lang\n LEFT JOIN\n %sfaqdata_group AS fdg\n ON\n fd.id = fdg.record_id\n LEFT JOIN\n %sfaqdata_user AS fdu\n ON\n fd.id = fdu.record_id\n WHERE\n fd.lang = '%s'\n AND \n fd.date_start <= '%s'\n AND \n fd.date_end >= '%s'\n AND \n fd.active = 'yes'\n AND \n fd.sticky = 1\n %s", PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), $this->_config->getLanguage()->getLanguage(), $now, $now, $this->queryPermission($this->groupSupport));
$result = $this->_config->getDb()->query($query);
$sticky = array();
$data = array();
$oldId = 0;
while ($row = $this->_config->getDb()->fetchObject($result)) {
if ($oldId != $row->id) {
$data['thema'] = $row->thema;
$title = $row->thema;
$url = sprintf('%s?%saction=artikel&cat=%d&id=%d&artlang=%s', PMF_Link::getSystemRelativeUri(), $sids, $row->category_id, $row->id, $row->lang);
$oLink = new PMF_Link($url, $this->_config);
$oLink->itemTitle = $row->thema;
$oLink->tooltip = $title;
$data['url'] = $oLink->toString();
$sticky[] = $data;
}
$oldId = $row->id;
}
return $sticky;
}
