/** * 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->fetch_object($result)) { $data[$row->id] = array('time' => $row->time, 'usr' => $row->usr, 'text' => $row->text, 'ip' => $row->ip); } return $data; }
/** * 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, $PMF_CONF; $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->fetch_object($result)) && $i < $PMF_CONF['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 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->fetch_object($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; }
/** <<<<<<< HEAD ======= * Prints the open questions as a XHTML table * * @return string * @access public * @since 2002-09-17 * @author Thorsten Rinne <*****@*****.**> */ function printOpenQuestions() { global $sids, $category; if ($this->groupSupport) { $permPart = sprintf("AND ( fdg.group_id IN (%s))", implode(', ', $this->groups), $this->user, implode(', ', $this->groups)); $permPartLeftJoin = sprintf("LEFT JOIN %sfaqcategory_group AS fdg ON fq.ask_rubrik = fdg.category_id", $SQLPREFIX); } else { $permPart = ''; $permPartLeftJoin = ''; } $query = sprintf("\n SELECT\n COUNT(*) AS num\n FROM\n %sfaqquestions \n\t\t\t%s\n WHERE\n is_visible != 'Y'\n\t\t\t%s ", SQLPREFIX, $permPartLeftJoin, $permPart); $result = $this->db->query($query); $row = $this->db->fetch_object($result); $numOfInvisibles = $row->num; if ($numOfInvisibles > 0) { $extraout = sprintf('<tr><td colspan="3"><hr />%s%s</td></tr>', $this->pmf_lang['msgQuestionsWaiting'], $numOfInvisibles); } else { $extraout = ''; } $query = sprintf("\n SELECT\n fq.id,\n fq.ask_username,\n fq.ask_usermail,\n fq.ask_rubrik,\n fq.ask_content,\n fq.ask_date\n FROM\n %sfaqquestions as fq\n %s\n\t\t\tWHERE\n fq.is_visible = 'Y'\n\t\t\t %s\n\t\t\tORDER BY\n ask_date ASC", SQLPREFIX, $permPartLeftJoin, $permPart); $result = $this->db->query($query); $output = ''; if ($this->db->num_rows($result) > 0) { while ($row = $this->db->fetch_object($result)) { $output .= '<tr class="openquestions">'; $output .= sprintf('<td valign="top" nowrap="nowrap">%s<br /><a href="mailto:%s">%s</a></td>', PMF_Date::createIsoDate($row->created), PMF_Mail::safeEmail($row->email), $row->username); $output .= sprintf('<td valign="top"><strong>%s:</strong><br />%s</td>', isset($category->categoryName[$row->category_id]['name']) ? $category->categoryName[$row->category_id]['name'] : '', strip_tags($row->question)); $output .= sprintf('<td valign="top"><a href="?%saction=add&question=%d&cat=%d">%s</a></td>', $sids, $row->id, $row->category_id, $this->pmf_lang['msg2answer']); $output .= '</tr>'; } } else { $output = sprintf('<tr><td colspan="3">%s</td></tr>', $this->pmf_lang['msgNoQuestionsAvailable']); } return $output . $extraout; }
/** * Returns all session from a date * * @param integer $timestamp Timestamp * @return array * @since 2007-03-31 * @author Thorsten Rinne <*****@*****.**> */ 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->fetch_object($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->num_rows($result) > 0) { while ($row = $this->db->fetch_object($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->fetch_object($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->fetch_object($result)) == true) { $retval[] = $row->stopword; } } else { return $this->db->fetchAll($result); } return $retval; }
/** * Calculates the rating of the user votings * * @param integer $id * @return string * @access public * @since 2002-08-29 * @author Thorsten Rinne <*****@*****.**> */ function getVotingResult($id) { $query = sprintf('SELECT (vote/usr) as voting, usr FROM %sfaqvoting WHERE artikel = %d', SQLPREFIX, $id); $result = $this->db->query($query); if ($this->db->num_rows($result) > 0) { $row = $this->db->fetch_object($result); return sprintf(' %s %s 5 (' . $this->plr->GetMsg('plmsgVotes', $row->usr) . ')', round($row->voting, 2), $this->pmf_lang['msgVoteFrom']); } else { return sprintf(' 0 %s 5 (' . $this->plr->GetMsg('plmsgVotes', 0) . ')', $this->pmf_lang['msgVoteFrom']); } }
/** * Returns the most popular searches * * @param integer $numResults Number of Results, default: 7 * @param boolean weither language must be included into result * @return array */ public function getMostPopularSearches($numResults = 7, $withLang = false) { $searchResult = array(); $byLang = $withLang ? ', lang' : ''; $query = sprintf("\n SELECT \n searchterm, COUNT(searchterm) AS number %s\n FROM\n %s\n GROUP BY\n searchterm %s\n ORDER BY\n number\n DESC", $byLang, $this->_table, $byLang); $result = $this->db->query($query); if ($result) { $i = 0; while ($row = $this->db->fetch_object($result)) { if ($i < $numResults) { $searchResult[] = (array) $row; } $i++; } } return $searchResult; }
/** * 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->fetch_object($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->fetch_object($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->escape_string(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", 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->fetch_object($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 = empty($writeMap) ? '' : '<ul>' . $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->fetch_object($result)->count; }