/**
* Returns the number of entries
*
* @return integer
*/
public function getNumberOfEntries()
{
$query = sprintf('
SELECT
id
FROM
%sfaqadminlog', SQLPREFIX);
return $this->db->numRows($this->db->query($query));
}
/**
* The main search function for the full text search
*
* @param string $searchterm Text/Number (solution id)
* @param boolean $allLanguages true to search over all languages
* @param boolean $hasMore true to disable the results paging
* @param boolean $instantRespnse true to use it for Instant Response
* @return array
*/
public function search($searchterm, $allLanguages = true, $hasMore = false, $instantResponse = false)
{
$fdTable = SQLPREFIX . 'faqdata';
$fcrTable = SQLPREFIX . 'faqcategoryrelations';
$condition = array($fdTable . '.active' => "'yes'");
// Search in all or one category?
if (!is_null($this->categoryId)) {
$selectedCategory = array($fcrTable . '.category_id' => $searchcategory);
$condition = array_merge($selectedCategory, $condition);
}
if (!$allLanguages && !is_numeric($searchterm)) {
$selectedLanguage = array($fdTable . '.lang' => "'" . $this->language . "'");
$condition = array_merge($selectedLanguage, $condition);
}
if (is_numeric($searchterm)) {
// search for the solution_id
$result = $this->db->search($fdTable, array($fdTable . '.id AS id', $fdTable . '.lang AS lang', $fdTable . '.solution_id AS solution_id', $fcrTable . '.category_id AS category_id', $fdTable . '.thema AS question', $fdTable . '.content AS answer'), $fcrTable, array($fdTable . '.id = ' . $fcrTable . '.record_id', $fdTable . '.lang = ' . $fcrTable . '.record_lang'), array($fdTable . '.solution_id'), $searchterm, $condition);
} else {
$result = $this->db->search($fdTable, array($fdTable . '.id AS id', $fdTable . '.lang AS lang', $fcrTable . '.category_id AS category_id', $fdTable . '.thema AS question', $fdTable . '.content AS answer'), $fcrTable, array($fdTable . '.id = ' . $fcrTable . '.record_id', $fdTable . '.lang = ' . $fcrTable . '.record_lang'), array($fdTable . '.thema', $fdTable . '.content', $fdTable . '.keywords'), $searchterm, $condition);
}
if ($result) {
$num = $this->db->numRows($result);
}
if ($num == 0) {
return array();
} else {
return $this->db->fetchAll($result);
}
}
/**
* Returns all tags
*
* @param string $search Move the returned result set to be the result of a start-with search
* @param boolean $limit Limit the returned result set
* @return array
*/
public function getAllTags($search = null, $limit = false)
{
global $DB;
$tags = $allTags = array();
// Hack: LIKE is case sensitive under PostgreSQL
switch ($DB['type']) {
case 'pgsql':
$like = 'ILIKE';
break;
default:
$like = 'LIKE';
break;
}
$query = sprintf("\n SELECT\n tagging_id, tagging_name\n FROM\n %sfaqtags\n %s\n ORDER BY tagging_name", SQLPREFIX, isset($search) && $search != '' ? "WHERE tagging_name " . $like . " '" . $search . "%'" : '');
$result = $this->db->query($query);
if ($result) {
while ($row = $this->db->fetchObject($result)) {
$allTags[$row->tagging_id] = $row->tagging_name;
}
}
$numberOfItems = $limit ? PMF_TAGS_CLOUD_RESULT_SET_SIZE : $this->db->numRows($result);
if (isset($allTags) && $numberOfItems < count($allTags)) {
$keys = array_keys($allTags);
shuffle($keys);
foreach ($keys as $current_key) {
$tags[$current_key] = $allTags[$current_key];
}
$tags = array_slice($tags, 0, $numberOfItems);
} else {
$tags = PMF_Utils::shuffleData($allTags);
}
return $tags;
}
/**
* Match a word against the stop words dictionary
*
* @param string $word
*
* @return boolean
*/
public function match($word)
{
$sql = "SELECT id FROM {$this->table_name} WHERE LOWER(stopword) = LOWER('%s') AND lang = '%s'";
$sql = sprintf($sql, $word, $this->language);
$result = $this->db->query($sql);
return $this->db->numRows($result) > 0;
}
/**
* Retrieve faq records according to the constraints provided
*
* @param $QueryType
* @param $nCatid
* @param $bDownwards
* @param $lang
* @param $date
* @return array
* @access public
* @since 2005-11-02
* @author Matteo Scaramuccia <*****@*****.**>
*/
function get($QueryType = FAQ_QUERY_TYPE_DEFAULT, $nCatid = 0, $bDownwards = true, $lang = '', $date = '')
{
$faqs = array();
$result = $this->db->query($this->_getSQLQuery($QueryType, $nCatid, $bDownwards, $lang, $date));
if ($this->db->numRows($result) > 0) {
$i = 0;
while ($row = $this->db->fetchObject($result)) {
$faq = array();
$faq['id'] = $row->id;
$faq['solution_id'] = $row->solution_id;
$faq['revision_id'] = $row->revision_id;
$faq['lang'] = $row->lang;
$faq['category_id'] = $row->category_id;
$faq['active'] = $row->active;
$faq['sticky'] = $row->sticky;
$faq['keywords'] = $row->keywords;
$faq['topic'] = $row->thema;
$faq['content'] = $row->content;
$faq['author_name'] = $row->author;
$faq['author_email'] = $row->email;
$faq['comment_enable'] = $row->comment;
$faq['lastmodified'] = $row->datum;
$faq['hits'] = $row->visits;
$faq['hits_last'] = $row->last_visit;
$faqs[$i] = $faq;
$i++;
}
}
return $faqs;
}
/**
* The main search function for the full text search
*
* @param string $searchterm Text/Number (solution id)
* @param boolean $allLanguages true to search over all languages
*
* @return array
*/
public function search($searchterm, $allLanguages = true)
{
$fdTable = SQLPREFIX . 'faqdata';
$fcrTable = SQLPREFIX . 'faqcategoryrelations';
$condition = array($fdTable . '.active' => "'yes'");
$search = PMF_Search_Factory::create($this->language, array('database' => PMF_Db::getType()));
// Search in all or one category?
if (!is_null($this->categoryId) && 0 < $this->categoryId) {
$selectedCategory = array($fcrTable . '.category_id' => $this->categoryId);
$condition = array_merge($selectedCategory, $condition);
}
if (!$allLanguages && !is_numeric($searchterm)) {
$selectedLanguage = array($fdTable . '.lang' => "'" . $this->language->getLanguage() . "'");
$condition = array_merge($selectedLanguage, $condition);
}
$search->setDatabaseHandle($this->db)->setTable($fdTable)->setResultColumns(array($fdTable . '.id AS id', $fdTable . '.lang AS lang', $fdTable . '.solution_id AS solution_id', $fcrTable . '.category_id AS category_id', $fdTable . '.thema AS question', $fdTable . '.content AS answer'))->setJoinedTable($fcrTable)->setJoinedColumns(array($fdTable . '.id = ' . $fcrTable . '.record_id', $fdTable . '.lang = ' . $fcrTable . '.record_lang'))->setConditions($condition);
if (is_numeric($searchterm)) {
$search->setMatchingColumns(array($fdTable . '.solution_id'));
} else {
$search->setMatchingColumns(array($fdTable . '.thema', $fdTable . '.content', $fdTable . '.keywords'));
}
$result = $search->search($searchterm);
if ($result) {
$num = $this->db->numRows($result);
}
if ($num == 0) {
return array();
} else {
return $this->db->fetchAll($result);
}
}
/**
* 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;
}
/**
* Checks the Session ID
*
* @param integer $sessionId Session ID
* @param string $ip IP
*
* @return void
*/
public function checkSessionId($sessionId, $ip)
{
global $sid, $user;
$query = sprintf("\n SELECT\n sid\n FROM\n %sfaqsessions\n WHERE\n sid = %d\n AND\n ip = '%s'\n AND\n time > %d", SQLPREFIX, $sessionId, $ip, $_SERVER['REQUEST_TIME'] - 86400);
$result = $this->db->query($query);
if ($this->db->numRows($result) == 0) {
$this->userTracking('old_session', $sessionId);
} else {
// Update global session id
$sid = $sessionId;
// Update db tracking
$query = sprintf("\n UPDATE\n %sfaqsessions\n SET\n time = %d,\n user_id = %d\n WHERE\n sid = %d\n AND ip = '%s'", SQLPREFIX, $_SERVER['REQUEST_TIME'], $user ? $user->getUserId() : '-1', $sessionId, $ip);
$this->db->query($query);
}
}
