/**
* Returns all data from the adminlog
*
* @return array
*/
public function getAll()
{
$data = array();
$query = sprintf('
SELECT
id, time, usr, text, ip
FROM
%sfaqadminlog
ORDER BY id DESC', SQLPREFIX);
$result = $this->db->query($query);
while ($row = $this->db->fetchObject($result)) {
$data[$row->id] = array('time' => $row->time, 'usr' => $row->usr, 'text' => $row->text, 'ip' => $row->ip);
}
return $data;
}
/**
* Returns the sticky records with URL and Title
*
* @return array
*/
private function getStickyRecordsData()
{
global $sids;
if ($this->groupSupport) {
$permPart = sprintf("AND\n ( 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("AND\n ( fdu.user_id = %d OR fdu.user_id = -1 )", $this->user);
}
$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", SQLPREFIX, SQLPREFIX, SQLPREFIX, SQLPREFIX, $this->language, $now, $now, $permPart);
$result = $this->db->query($query);
$sticky = array();
$data = array();
$oldId = 0;
while ($row = $this->db->fetchObject($result)) {
if ($oldId != $row->id) {
$data['thema'] = $row->thema;
$title = $row->thema;
$url = sprintf('%saction=artikel&cat=%d&id=%d&artlang=%s', $sids, $row->category_id, $row->id, $row->lang);
$oLink = new PMF_Link(PMF_Link::getSystemRelativeUri() . '?' . $url);
$oLink->itemTitle = $row->thema;
$oLink->tooltip = $title;
$data['url'] = $oLink->toString();
$sticky[] = $data;
}
$oldId = $row->id;
}
return $sticky;
}
/**
* Returns all relevant Articles for a FAQ record
*
* @param integer $record_id
* @param string $thema
* @return string
* @since 2006-08-29
* @author Thomas Zeithaml <*****@*****.**>
*/
public function getAllRelatedById($record_id, $article_name, $keywords)
{
global $sids;
$relevantslisting = '';
$begriffe = str_replace('-', ' ', $article_name) . $keywords;
$i = $last_id = 0;
$result = $this->db->search(SQLPREFIX . "faqdata", array(SQLPREFIX . "faqdata.id AS id", SQLPREFIX . "faqdata.lang AS lang", SQLPREFIX . "faqcategoryrelations.category_id AS category_id", SQLPREFIX . "faqdata.thema AS thema", SQLPREFIX . "faqdata.content AS content"), SQLPREFIX . "faqcategoryrelations", array(SQLPREFIX . "faqdata.id = " . SQLPREFIX . "faqcategoryrelations.record_id", SQLPREFIX . "faqdata.lang = " . SQLPREFIX . "faqcategoryrelations.record_lang"), array(SQLPREFIX . "faqdata.thema", SQLPREFIX . "faqdata.content", SQLPREFIX . "faqdata.keywords"), $begriffe, array(SQLPREFIX . "faqdata.active" => "'yes'"));
while (($row = $this->db->fetchObject($result)) && $i < PMF_Configuration::getInstance()->get('records.numberOfRelatedArticles')) {
if ($row->id == $record_id || $row->id == $last_id) {
continue;
}
$relevantslisting .= '' == $relevantslisting ? '<ul>' : '';
$relevantslisting .= '<li>';
$url = sprintf('%saction=artikel&cat=%d&id=%d&artlang=%s', $sids, $row->category_id, $row->id, $row->lang);
$oLink = new PMF_Link(PMF_Link::getSystemRelativeUri() . '?' . $url);
$oLink->itemTitle = $row->thema;
$oLink->text = $row->thema;
$oLink->tooltip = $row->thema;
$relevantslisting .= $oLink->toHtmlAnchor() . '</li>';
$i++;
$last_id = $row->id;
}
$relevantslisting .= $i > 0 ? '</ul>' : '';
return '' == $relevantslisting ? '-' : $relevantslisting;
}
/**
* Returns all session from a date
*
* @param integer $timestamp Timestamp
*
* @return array
*/
public function getSessionsbyDate($firstHour, $lastHour)
{
$sessions = array();
$query = sprintf("\n SELECT\n sid, ip, time\n FROM\n %sfaqsessions\n WHERE\n time > %d\n AND\n time < %d\n ORDER BY\n time", SQLPREFIX, $firstHour, $lastHour);
$result = $this->db->query($query);
while ($row = $this->db->fetchObject($result)) {
$sessions[$row->sid] = array('ip' => $row->ip, 'time' => $row->time);
}
return $sessions;
}
/**
* 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 = array();
$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" : '', SQLPREFIX, $type == self::COMMENT_TYPE_FAQ ? "LEFT JOIN\n " . SQLPREFIX . "faqcategoryrelations fcg\n ON\n fc.id = fcg.record_id\n" : '', $type);
$result = $this->db->query($query);
if ($this->db->numRows($result) > 0) {
while ($row = $this->db->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', SQLPREFIX);
$result = $this->db->query($query);
if ($row = $this->db->fetchObject($result)) {
return $row->n > 0;
}
return false;
}
/**
* 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->language : $lang;
$sql = sprintf("SELECT id, lang, LOWER(stopword) AS stopword FROM {$this->table_name} WHERE lang = '%s'", $lang);
$result = $this->db->query($sql);
$retval = array();
if ($wordsOnly) {
while (($row = $this->db->fetchObject($result)) == true) {
$retval[] = $row->stopword;
}
} else {
return $this->db->fetchAll($result);
}
return $retval;
}
/**
* Returns all ratings of FAQ records
*
* @return array
* @access public
* @since 2007-03-31
* @author Thorsten Rinne <*****@*****.**>
*/
public function getAllRatings()
{
$ratings = array();
switch ($this->type) {
case 'mssql':
// In order to remove this MS SQL 2000/2005 "limit" below:
// The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
// we'll cast faqdata.thema datatype from text to char(2000)
// Note: the char length is simply an heuristic value
// Doing so we'll also need to trim $row->thema to remove blank chars when it is shorter than 2000 chars
$query = sprintf("\n SELECT\n fd.id AS id,\n fd.lang AS lang,\n fcr.category_id AS category_id,\n CAST(fd.thema as char(2000)) AS question,\n (fv.vote / fv.usr) AS num,\n fv.usr AS usr\n FROM\n %sfaqvoting fv,\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 WHERE\n fd.id = fv.artikel\n GROUP BY\n fd.id,\n fd.lang,\n fd.active,\n fcr.category_id,\n CAST(fd.thema as char(2000)),\n fv.vote,\n fv.usr\n ORDER BY\n fcr.category_id", SQLPREFIX, SQLPREFIX, SQLPREFIX);
break;
default:
$query = sprintf("\n SELECT\n fd.id AS id,\n fd.lang AS lang,\n fcr.category_id AS category_id,\n fd.thema AS question,\n (fv.vote / fv.usr) AS num,\n fv.usr AS usr\n FROM\n %sfaqvoting fv,\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 WHERE\n fd.id = fv.artikel\n GROUP BY\n fd.id,\n fd.lang,\n fd.active,\n fcr.category_id,\n fd.thema,\n fv.vote,\n fv.usr\n ORDER BY\n fcr.category_id", SQLPREFIX, SQLPREFIX, SQLPREFIX);
break;
}
$result = $this->db->query($query);
while ($row = $this->db->fetchObject($result)) {
$ratings[] = array('id' => $row->id, 'lang' => $row->lang, 'category_id' => $row->category_id, 'question' => $row->question, 'num' => $row->num, 'usr' => $row->usr);
}
return $ratings;
}
/**
* Returns the number of anonymous users and registered ones.
* These are the numbers of unique users who have perfomed
* some activities within the last five minutes
*
* @param integer $activityTimeWindow Optionally set the time window size in sec.
* Default: 300sec, 5 minutes
*
* @return array
*/
public function getUsersOnline($activityTimeWindow = 300)
{
$users = array(0, 0);
if (PMF_Configuration::getInstance()->get('main.enableUserTracking')) {
$timeNow = $_SERVER['REQUEST_TIME'] - $activityTimeWindow;
// Count all sids within the time window
// TODO: add a new field in faqsessions in order to find out only sids of anonymous users
$query = sprintf("\n SELECT\n count(sid) AS anonymous_users\n FROM\n %sfaqsessions\n WHERE\n user_id = -1\n AND \n time > %d", SQLPREFIX, $timeNow);
$result = $this->db->query($query);
if (isset($result)) {
$row = $this->db->fetchObject($result);
$users[0] = $row->anonymous_users;
}
// Count all faquser records within the time window
$query = sprintf("\n SELECT\n count(session_id) AS registered_users\n FROM\n %sfaquser\n WHERE\n session_timestamp > %d", SQLPREFIX, $timeNow);
$result = $this->db->query($query);
if (isset($result)) {
$row = $this->db->fetchObject($result);
$users[1] = $row->registered_users;
}
}
return $users;
}
/**
* 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->db->escapeString(PMF_String::substr($letter, 0, 1)));
$writeMap = '<ul>';
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", SQLPREFIX, SQLPREFIX, SQLPREFIX, SQLPREFIX, $letter, $this->language, $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", SQLPREFIX, SQLPREFIX, SQLPREFIX, SQLPREFIX, $letter, $this->language, $permPart);
break;
}
$result = $this->db->query($query);
$oldId = 0;
while ($row = $this->db->fetchObject($result)) {
if ($oldId != $row->id) {
$title = PMF_String::htmlspecialchars($row->thema, ENT_QUOTES, 'utf-8');
$url = sprintf('%saction=artikel&cat=%d&id=%d&artlang=%s', $sids, $row->category_id, $row->id, $row->lang);
$oLink = new PMF_Link(PMF_Link::getSystemRelativeUri() . '?' . $url);
$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 .= '</ul>';
return $writeMap;
}
/**
* Returns row count from the faqsearches table
*
* @return integer
*/
public function getSearchesCount()
{
$sql = sprintf("\n \t SELECT \n \t COUNT(1) AS count \n \t FROM \n \t %s", $this->_table);
$result = $this->db->query($sql);
return (int) $this->db->fetchObject($result)->count;
}
