/**
* This function builds the the queries for the backup
*
* @param string $query
* @param string $table
*
* @return array
*/
public function buildInsertQueries($query, $table)
{
if (!($result = $this->_config->getDb()->query($query))) {
[];
}
$ret = [];
$ret[] = "\r\n-- Table: " . $table;
while ($row = $this->_config->getDb()->fetchArray($result)) {
$p1 = [];
$p2 = [];
foreach ($row as $key => $val) {
$p1[] = $key;
if ('rights' != $key && is_numeric($val)) {
$p2[] = $val;
} else {
if (is_null($val)) {
$p2[] = 'NULL';
} else {
$p2[] = sprintf("'%s'", $this->_config->getDb()->escape($val));
}
}
}
$ret[] = "INSERT INTO " . $table . " (" . implode(",", $p1) . ") VALUES (" . implode(",", $p2) . ");";
}
return $ret;
}
/**
* 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);
}
/**
* Get an array with minimalistic attachment meta data
*
* @return array
*/
public function getBreadcrumbs()
{
$retval = array();
$query = sprintf("\n SELECT\n fa.id AS ID,\n fa.record_id AS record_id,\n fa.record_lang AS record_lang,\n fa.filename AS filename,\n fa.filesize AS filesize,\n fa.mime_type AS mime_type,\n fd.thema AS thema\n FROM\n %s fa\n JOIN\n %s fd\n ON\n fa.record_id = fd.id\n GROUP BY\n fa.id", PMF_Db::getTablePrefix() . 'faqattachment', PMF_Db::getTablePrefix() . 'faqdata');
$result = $this->config->getDb()->query($query);
if ($result) {
$retval = $this->config->getDb()->fetchAll($result);
}
return $retval;
}
/**
* Create a matrix for representing categories and faq records
*
* @return array
*/
public function getCategoryRecordsMatrix()
{
$matrix = [];
$query = sprintf('
SELECT
fcr.category_id AS id_cat,
fd.id AS id
FROM
%sfaqdata fd
INNER JOIN
%sfaqcategoryrelations fcr
ON
fd.id = fcr.record_id
AND
fd.lang = fcr.category_lang
ORDER BY
fcr.category_id, fd.id', PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix());
$result = $this->_config->getDb()->query($query);
if ($this->_config->getDb()->numRows($result) > 0) {
while ($row = $this->_config->getDb()->fetchObject($result)) {
$matrix[$row->id_cat][$row->id] = true;
}
}
return $matrix;
}
/**
* 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);
}
/**
* Generates a huge array for the report
* @return array
*/
public function getReportingData()
{
$report = [];
$query = sprintf("\n SELECT\n fd.id AS id,\n fd.lang AS lang,\n fcr.category_id AS category_id,\n c.name as category_name,\n c.parent_id as parent_id,\n fd.sticky AS sticky,\n fd.thema AS question,\n fd.author AS original_author,\n fd.datum AS creation_date,\n fv.visits AS visits,\n u.display_name AS last_author\n FROM\n %sfaqdata fd\n LEFT JOIN\n %sfaqcategoryrelations fcr\n ON\n (fd.id = fcr.record_id AND fd.lang = fcr.record_lang)\n LEFT JOIN\n %sfaqvisits fv\n ON\n (fd.id = fv.id AND fd.lang = fv.lang)\n LEFT JOIN\n %sfaqchanges as fc\n ON\n (fd.id = fc.id AND fd.lang = fc.lang)\n LEFT JOIN\n %sfaquserdata as u\n ON\n (u.user_id = fc.usr)\n LEFT JOIN\n %sfaqcategories as c\n ON\n (c.id = fcr.category_id AND c.lang = fcr.record_lang)\n ORDER BY\n fd.id\n ASC", PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix());
$result = $this->_config->getDb()->query($query);
$lastId = 0;
while ($row = $this->_config->getDb()->fetchObject($result)) {
if ($row->id == $lastId) {
$report[$row->id]['faq_translations'] += 1;
} else {
$report[$row->id] = array('faq_id' => $row->id, 'faq_language' => $row->lang, 'category_id' => $row->category_id, 'category_parent' => $row->parent_id, 'category_name' => $row->category_name, 'faq_translations' => 0, 'faq_sticky' => $row->sticky, 'faq_question' => $row->question, 'faq_org_author' => $row->original_author, 'faq_creation' => PMF_Date::createIsoDate($row->creation_date), 'faq_visits' => $row->visits, 'faq_last_author' => $row->last_author);
}
$lastId = $row->id;
}
return $report;
}
/**
* 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;
}
/**
* 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 logging data older than 30 days
*
* @return boolean
*/
public function delete()
{
$query = sprintf("DELETE FROM\n %sfaqadminlog\n WHERE\n time < %d", PMF_Db::getTablePrefix(), $_SERVER['REQUEST_TIME'] - 30 * 86400);
if ($this->_config->getDb()->query($query)) {
return true;
}
return false;
}
/**
* Get all the entries from the table faqvisits
*
* @return array
*/
public function getAllData()
{
$data = [];
$query = sprintf("\n SELECT\n *\n FROM\n %sfaqvisits\n ORDER BY\n visits DESC", PMF_Db::getTablePrefix());
$result = $this->_config->getDb()->query($query);
while ($row = $this->_config->getDb()->fetchObject($result)) {
$data[] = array('id' => $row->id, 'lang' => $row->lang, 'visits' => $row->visits, 'last_visit' => $row->last_visit);
}
return $data;
}
/**
* Returns all comments with their categories
*
* @param string $type Type of comment: faq or news
* @return array
*/
public function getAllComments($type = self::COMMENT_TYPE_FAQ)
{
$comments = [];
$query = sprintf("\n SELECT\n fc.id_comment AS comment_id,\n fc.id AS record_id,\n %s\n fc.usr AS username,\n fc.email AS email,\n fc.comment AS comment,\n fc.datum AS comment_date\n FROM\n %sfaqcomments fc\n %s\n WHERE\n type = '%s'", $type == self::COMMENT_TYPE_FAQ ? "fcg.category_id,\n" : '', PMF_Db::getTablePrefix(), $type == self::COMMENT_TYPE_FAQ ? "LEFT JOIN\n " . PMF_Db::getTablePrefix() . "faqcategoryrelations fcg\n ON\n fc.id = fcg.record_id\n" : '', $type);
$result = $this->config->getDb()->query($query);
if ($this->config->getDb()->numRows($result) > 0) {
while ($row = $this->config->getDb()->fetchObject($result)) {
$comments[] = array('comment_id' => $row->comment_id, 'record_id' => $row->record_id, 'category_id' => isset($row->category_id) ? $row->category_id : null, 'content' => $row->comment, 'date' => $row->comment_date, 'username' => $row->username, 'email' => $row->email);
}
}
return $comments;
}
/**
* Check if at least one faq has been tagged with a tag
*
* @return boolean
*/
public function existTagRelations()
{
$query = sprintf('
SELECT
COUNT(record_id) AS n
FROM
%sfaqdata_tags', PMF_Db::getTablePrefix());
$result = $this->_config->getDb()->query($query);
if ($row = $this->_config->getDb()->fetchObject($result)) {
return $row->n > 0;
}
return false;
}
/**
* Deletes the user-data entry for the given user-ID $userId.
* Returns true on success, otherwise false.
*
* @param integer $userId User ID
* @return bool
*/
public function delete($userId)
{
$userId = (int) $userId;
if ($userId <= 0 && $userId != -1) {
return false;
}
$this->userId = $userId;
$delete = sprintf("\n DELETE FROM\n %sfaquserdata\n WHERE\n user_id = %d", PMF_Db::getTablePrefix(), $this->userId);
$res = $this->config->getDb()->query($delete);
if (!$res) {
return false;
}
$this->data = array();
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;
}
/**
* @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;
}
/**
* Calculates the rating of the user votings
*
* @param integer $id
*
* @return string
*/
function getVotingResult($id)
{
$query = sprintf('
SELECT
(vote/usr) as voting, usr
FROM
%sfaqvoting
WHERE
artikel = %d', PMF_Db::getTablePrefix(), $id);
$result = $this->_config->getDb()->query($query);
if ($this->_config->getDb()->numRows($result) > 0) {
$row = $this->_config->getDb()->fetchObject($result);
return sprintf(' %s (' . $this->plr->GetMsg('plmsgVotes', $row->usr) . ')', round($row->voting, 2));
} else {
return '0 (' . $this->plr->GetMsg('plmsgVotes', 0) . ')';
}
}
/**
* Calculates the number of visits per day the last 30 days
*
* @returns array
*/
public function getLast30DaysVisits()
{
$stats = $visits = array();
$startDate = strtotime('-1 month');
$endDate = $_SERVER['REQUEST_TIME'];
$query = sprintf("\n SELECT\n time\n FROM\n %sfaqsessions\n WHERE\n time > %d\n AND\n time < %d;", PMF_Db::getTablePrefix(), $startDate, $endDate);
$result = $this->config->getDb()->query($query);
while ($row = $this->config->getDb()->fetchObject($result)) {
$visits[] = $row->time;
}
for ($date = $startDate; $date <= $endDate; $date += 86400) {
$stats[date('Y-m-d', $date)] = 0;
}
foreach ($visits as $visitDate) {
$stats[date('Y-m-d', $visitDate)]++;
}
return $stats;
}
/**
* Returns an array of country codes for a specific FAQ record ID,
* specific category ID or all languages used by FAQ records , categories
*
* @param integer $id ID
* @param string $table Specifies table
*
* @return array
*/
public function languageAvailable($id, $table = 'faqdata')
{
$output = [];
if (isset($id)) {
if ($id == 0) {
// get languages for all ids
$distinct = ' DISTINCT ';
$where = '';
} else {
// get languages for specified id
$distinct = '';
$where = " WHERE id = " . $id;
}
$query = sprintf("\n SELECT %s\n lang\n FROM\n %s%s\n %s", $distinct, PMF_Db::getTablePrefix(), $table, $where);
$result = $this->config->getDb()->query($query);
if ($this->config->getDb()->numRows($result) > 0) {
while ($row = $this->config->getDb()->fetchObject($result)) {
$output[] = $row->lang;
}
}
}
return $output;
}
/**
* 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;
}
/**
* Adds a configuration item for the database
*
* @param string $name
* @param mixed $value
*
* @return boolean
*/
public function addConfig($name, $value)
{
$insert = sprintf("INSERT INTO\n %sfaqinstances_config\n VALUES\n (%d, '%s', '%s')", PMF_Db::getTablePrefix(), $this->getId(), $this->config->getDb()->escape(trim($name)), $this->config->getDb()->escape(trim($value)));
return $this->config->getDb()->query($insert);
}
/**
* 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);
}
/**
* Updates field answer_id in faqquestion
*
* @param integer $openQuestionId
* @param integer $faqId
* @param integer $categoryId
*
* @return boolean
*/
public function updateQuestionAnswer($openQuestionId, $faqId, $categoryId)
{
$query = sprintf('UPDATE %sfaqquestions SET answer_id = %d, category_id= %d, WHERE id= %d', PMF_Db::getTablePrefix(), $faqId, $categoryId, $openQuestionId);
return $this->_config->getDb()->query($query);
}
/**
* Fetch all record attachments
*
* @param PMF_Configuration $config
* @param integer $recordId ID of the record
*
* @return array
*/
public static function fetchByRecordId(PMF_Configuration $config, $recordId)
{
$retval = array();
$sql = sprintf("\n SELECT\n id\n FROM\n %sfaqattachment\n WHERE\n record_id = %d\n AND\n record_lang = '%s'", PMF_Db::getTablePrefix(), $recordId, PMF_Language::$language);
$result = $config->getDb()->fetchAll($config->getDb()->query($sql));
if ($result) {
foreach ($result as $item) {
$retval[] = self::create($item->id);
}
}
reset($retval);
return $retval;
}
/**
* retrieves stored link state and validates timestamp
*
* @param int $id
* @param string $artlang
* @param boolean $checkDate
*
* @return boolean|string
*/
public function getEntryState($id = 0, $artlang = '', $checkDate = false)
{
$interval = $this->getURLValidateInterval();
$query = sprintf("\n SELECT \n links_state, links_check_date \n FROM \n %sfaqdata \n WHERE \n id = %d \n AND \n lang = '%s'", PMF_Db::getTablePrefix(), $id, $this->_config->getDb()->escape($artlang));
if ($result = $this->_config->getDb()->query($query)) {
while ($row = $this->_config->getDb()->fetchObject($result)) {
$_linkState = $row->links_state;
if (trim($_linkState) == "") {
$_linkState = true;
}
if ($row->links_check_date > $interval) {
return $_linkState;
} else {
if ($checkDate == false) {
return $_linkState;
} else {
return true;
}
}
}
} else {
return false;
}
}
/**
* Returns an array of all users found in the database. By default, the
* anonymous User will not be returned. The returned array contains the
* user ID as key, the values are login name, account status, authentication
* source and the user creation date.
*
* @param boolean $withoutAnonymous Without anonymous?
* @return array
*/
public function getAllUserData($withoutAnonymous = true)
{
$select = sprintf("\n SELECT\n user_id, login, account_status, auth_source, member_since\n FROM\n %sfaquser\n %s\n ORDER BY\n login ASC", PMF_Db::getTablePrefix(), $withoutAnonymous ? 'WHERE user_id <> -1' : '');
$res = $this->config->getDb()->query($select);
if (!$res) {
return [];
}
$result = [];
while ($row = $this->config->getDb()->fetchArray($res)) {
$result[$row['user_id']] = $row;
}
return $result;
}
/**
* Returns row count from the "faqsearches" table
*
* @return integer
*/
public function getSearchesCount()
{
$sql = sprintf("SELECT COUNT(1) AS count FROM %s", $this->_table);
$result = $this->_config->getDb()->query($sql);
return (int) $this->_config->getDb()->fetchObject($result)->count;
}
