/** * 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; }