function loadCategories() { if ($this->_loaded) { return; } $this->_loaded = true; $catids = array(); foreach ($this->categories[0] as $cat) { $catids[] = $cat->id; } if (empty($catids)) { return; } $catlist = implode(',', $catids); $readlist = $this->session->readtopics; if ($this->config->shownew && $this->my->id) { $subquery = " (SELECT COUNT(DISTINCT thread) FROM #__kunena_messages AS mmm WHERE c.id=mmm.catid AND mmm.hold='0' AND mmm.time>{$this->db->Quote($this->prevCheck)} AND mmm.thread NOT IN ({$readlist})) AS new"; } else { $subquery = " 0 AS new"; } // TODO: optimize this query (just combined many queries into one) $query = "SELECT c.*, m.id AS mesid, m.thread, m.catid, t.subject AS topicsubject, m.subject, m.name AS mname, u.id AS userid, u.username, u.name AS uname,\n\t\t\t(SELECT COUNT(*) FROM #__kunena_messages AS mm WHERE m.thread=mm.thread) AS msgcount, {$subquery}\n\t\t\tFROM #__kunena_categories AS c\n\t\t\tLEFT JOIN #__kunena_messages AS m ON c.id_last_msg=m.id\n\t\t\tLEFT JOIN #__kunena_messages AS t ON m.thread=t.id\n\t\t\tLEFT JOIN #__users AS u ON u.id=m.userid\n\t\t\tWHERE c.parent IN ({$catlist}) AND c.published='1' AND c.id IN({$this->session->allowed}) ORDER BY ordering, name"; $this->db->setQuery($query); $allsubcats = $this->db->loadObjectList(); if (KunenaError::checkDatabaseError()) { return; } $this->tabclass = array("row1", "row2"); $subcats = array(); $routerlist = array(); $userlist = array(); $myprofile = KunenaFactory::getUser(); if ($myprofile->ordering != '0') { $topic_ordering = $myprofile->ordering == '1' ? true : false; } else { $topic_ordering = $this->config->default_sort == 'asc' ? false : true; } foreach ($allsubcats as $i => $subcat) { if ($subcat->mesid) { $routerlist[$subcat->thread] = $subcat->subject; } if ($topic_ordering) { $subcat->page = 1; } else { $subcat->page = ceil($subcat->msgcount / $this->config->messages_per_page); } if ($this->config->shownew && $this->my->id != 0) { if ($subcat->new) { // Check Unread Cat Images if (is_file(KUNENA_ABSCATIMAGESPATH . $subcat->id . "_on.gif")) { $allsubcats[$i]->htmlCategoryIcon = "<img src=\"" . KUNENA_URLCATIMAGES . $subcat->id . "_on.gif\" border=\"0\" class='kforum-cat-image' alt=\" \" />"; } else { $allsubcats[$i]->htmlCategoryIcon = CKunenaTools::showIcon('kunreadforum', JText::_('COM_KUNENA_GEN_FORUM_NEWPOST')); } } else { // Check Read Cat Images if (is_file(KUNENA_ABSCATIMAGESPATH . $subcat->id . "_off.gif")) { $allsubcats[$i]->htmlCategoryIcon = "<img src=\"" . KUNENA_URLCATIMAGES . $subcat->id . "_off.gif\" border=\"0\" class='kforum-cat-image' alt=\" \" />"; } else { $allsubcats[$i]->htmlCategoryIcon = CKunenaTools::showIcon('kreadforum', JText::_('COM_KUNENA_GEN_FORUM_NOTNEW')); } } } else { if (is_file(KUNENA_ABSCATIMAGESPATH . $subcat->id . "_notlogin.gif")) { $allsubcats[$i]->htmlCategoryIcon = "<img src=\"" . KUNENA_URLCATIMAGES . $subcat->id . "_notlogin.gif\" border=\"0\" class='kforum-cat-image' alt=\" \" />"; } else { $allsubcats[$i]->htmlCategoryIcon = CKunenaTools::showIcon('knotloginforum', JText::_('COM_KUNENA_GEN_FORUM_NOTNEW')); } } // collect user ids for avatar prefetch when integrated $userlist[intval($subcat->userid)] = intval($subcat->userid); } require_once KUNENA_PATH . '/router.php'; KunenaRouter::loadMessages($routerlist); $modcats = array(); foreach ($allsubcats as $subcat) { $this->categories[$subcat->parent][] = $subcat; $subcats[] = $subcat->id; if ($subcat->moderated) { $modcats[] = $subcat->id; } } // Get the childforums $this->childforums = array(); if (count($subcats)) { $subcatlist = implode(',', $subcats); if ($this->config->shownew && $this->my->id) { $subquery = " (SELECT COUNT(DISTINCT thread) FROM #__kunena_messages AS m WHERE c.id=m.catid AND m.hold='0' AND m.time>{$this->db->Quote($this->prevCheck)} AND m.thread NOT IN ({$readlist})) AS new"; } else { $subquery = "0 AS new"; } $query = "SELECT c.id, c.name, c.description, c.parent, c.numTopics, c.numPosts, {$subquery}\n\t\t\tFROM #__kunena_categories AS c\n\t\t\tWHERE c.parent IN ({$subcatlist}) AND c.published='1' AND c.id IN({$this->session->allowed}) ORDER BY c.ordering, c.name"; $this->db->setQuery($query); $childforums = $this->db->loadObjectList(); KunenaError::checkDatabaseError(); foreach ($childforums as $i => $childforum) { //Begin: parent read unread iconset if ($this->config->showchildcaticon) { if ($this->config->shownew && $this->my->id != 0) { if ($childforum->new) { // Check Unread Cat Images if (is_file(KUNENA_ABSCATIMAGESPATH . $childforum->id . "_on_childsmall.gif")) { $childforum->htmlCategoryIcon = "<img src=\"" . KUNENA_URLCATIMAGES . $childforum->id . "_on_childsmall.gif\" border=\"0\" class='kforum-cat-image' alt=\" \" />"; } else { $childforum->htmlCategoryIcon = CKunenaTools::showIcon('kunreadforum-sm', JText::_('COM_KUNENA_GEN_FORUM_NEWPOST')); } } else { // Check Read Cat Images if (is_file(KUNENA_ABSCATIMAGESPATH . $childforum->id . "_off_childsmall.gif")) { $childforum->htmlCategoryIcon = "<img src=\"" . KUNENA_URLCATIMAGES . $childforum->id . "_off_childsmall.gif\" border=\"0\" class='kforum-cat-image' alt=\" \" />"; } else { $childforum->htmlCategoryIcon = CKunenaTools::showIcon('kreadforum-sm', JText::_('COM_KUNENA_GEN_FORUM_NOTNEW')); } } } else { // Not Login Cat Images if (is_file(KUNENA_ABSCATIMAGESPATH . $childforum->id . "_notlogin_childsmall.gif")) { $childforum->htmlCategoryIcon = "<img src=\"" . KUNENA_URLCATIMAGES . $childforum->id . "_notlogin_childsmall.gif\" border=\"0\" class='kforum-cat-image' alt=\" \" />"; } else { $childforum->htmlCategoryIcon = CKunenaTools::showIcon('knotloginforum-sm', JText::_('COM_KUNENA_GEN_FORUM_NOTNEW')); } } } else { $childforum->htmlCategoryIcon = ''; } $this->childforums[$childforum->parent][] = $childforum; } } $this->modlist = array(); $this->pending = array(); if (count($modcats)) { if ($this->config->listcat_show_moderators) { $modcatlist = implode(',', $modcats); $this->db->setQuery("SELECT * FROM #__kunena_moderation AS m\n\t\t\t\t\tINNER JOIN #__users AS u ON u.id=m.userid\n\t\t\t\t\tWHERE m.catid IN ({$modcatlist}) AND u.block=0"); $modlist = $this->db->loadObjectList(); KunenaError::checkDatabaseError(); foreach ($modlist as $mod) { $this->modlist[$mod->catid][] = $mod; $userlist[intval($mod->userid)] = intval($mod->userid); } } if (CKunenaTools::isModerator($this->my->id)) { foreach ($modcats as $i => $catid) { if (!CKunenaTools::isModerator($this->my->id, $catid)) { unset($modcats[$i]); } } if (count($modcats)) { $modcatlist = implode(',', $modcats); $this->db->setQuery("SELECT catid, COUNT(*) AS count\n\t\t\t\t\tFROM #__kunena_messages\n\t\t\t\t\tWHERE catid IN ({$modcatlist}) AND hold='1'\n\t\t\t\t\tGROUP BY catid"); $pending = $this->db->loadAssocList(); KunenaError::checkDatabaseError(); foreach ($pending as $i) { if ($i['count']) { $this->pending[$i['catid']] = $i['count']; } } } } } // Prefetch all users/avatars to avoid user by user queries during template iterations kimport('user'); KunenaUser::loadUsers($userlist); }
protected function _common() { $this->totalpages = ceil($this->total / $this->threads_per_page); if (!empty($this->threadids)) { $idstr = implode(",", $this->threadids); if (empty($this->loadids)) { $loadstr = ''; } else { $loadstr = 'OR a.id IN (' . implode(",", $this->loadids) . ')'; } $query = "SELECT a.*, j.id AS userid, t.message, l.myfavorite, l.favcount, l.threadhits, l.lasttime, l.threadattachments, COUNT(aa.id) AS attachments,\n\t\t\t\tl.msgcount, l.mycount, l.lastid, l.mylastid, l.lastid AS lastread, 0 AS unread, u.avatar, j.username, j.name AS uname, c.name AS catname, c.class_sfx\n\t\t\tFROM (\n\t\t\t\tSELECT m.thread, MAX(m.hits) AS threadhits, MAX(f.userid IS NOT null AND f.userid={$this->db->Quote($this->my->id)}) AS myfavorite, COUNT(DISTINCT f.userid) AS favcount,\n\t\t\t\t\tCOUNT(DISTINCT a.id) AS threadattachments, COUNT(DISTINCT m.id) AS msgcount, COUNT(DISTINCT IF(m.userid={$this->db->Quote($this->user->id)}, m.id, NULL)) AS mycount,\n\t\t\t\t\tMAX(m.id) AS lastid, MAX(IF(m.userid={$this->db->Quote($this->user->id)}, m.id, 0)) AS mylastid, MAX(m.time) AS lasttime\n\t\t\t\tFROM #__kunena_messages AS m"; if ($this->config->allowfavorites) { $query .= " LEFT JOIN #__kunena_favorites AS f ON f.thread = m.thread"; } else { $query .= " LEFT JOIN #__kunena_favorites AS f ON f.thread = 0"; } $query .= "\n\t\t\t\tLEFT JOIN #__kunena_attachments AS a ON a.mesid = m.id\n\t\t\t\tWHERE m.hold IN ({$this->hold}) AND m.moved='0' AND m.thread IN ({$idstr})\n\t\t\t\tGROUP BY thread\n\t\t\t) AS l\n\t\t\tINNER JOIN #__kunena_messages AS a ON a.thread = l.thread\n\t\t\tINNER JOIN #__kunena_messages_text AS t ON a.id = t.mesid\n\t\t\tLEFT JOIN #__users AS j ON j.id = a.userid\n\t\t\tLEFT JOIN #__kunena_users AS u ON u.userid = j.id\n\t\t\tLEFT JOIN #__kunena_categories AS c ON c.id = a.catid\n\t\t\tLEFT JOIN #__kunena_attachments AS aa ON aa.mesid = a.id\n\t\t\tWHERE (a.parent='0' OR a.id=l.lastid {$loadstr})\n\t\t\tGROUP BY a.id\n\t\t\tORDER BY {$this->order}"; $this->db->setQuery($query); $this->messages = $this->db->loadObjectList('id'); KunenaError::checkDatabaseError(); // collect user ids for avatar prefetch when integrated $userlist = array(); foreach ($this->messages as $message) { if ($message->parent == 0) { $this->threads[$message->thread] = $message; $routerlist[$message->id] = $message->subject; if ($this->func == 'mylatest' && $message->myfavorite) { $this->highlight++; } } if ($message->id == $message->lastid) { $this->lastreply[$message->thread] = $message; } if (isset($this->loadids) && in_array($message->id, $this->loadids)) { $this->customreply[$message->id] = $message; } $userlist[intval($message->userid)] = intval($message->userid); $userlist[intval($message->modified_by)] = intval($message->modified_by); } // Load threads to Kunena router to avoid extra SQL queries if (!empty($routerlist)) { include_once KUNENA_PATH . '/router.php'; KunenaRouter::loadMessages($routerlist); } // Prefetch all users/avatars to avoid user by user queries during template iterations KunenaUser::loadUsers($userlist); if ($this->config->shownew && $this->my->id) { $readlist = $this->session->readtopics; $this->db->setQuery("SELECT thread, MIN(id) AS lastread, SUM(1) AS unread FROM #__kunena_messages " . "WHERE hold IN ({$this->hold}) AND moved='0' AND thread NOT IN ({$readlist}) AND thread IN ({$idstr}) AND time>{$this->db->Quote($this->prevCheck)} GROUP BY thread"); // TODO: check input $msgidlist = $this->db->loadObjectList(); KunenaError::checkDatabaseError(); foreach ($msgidlist as $msgid) { $this->messages[$msgid->thread]->lastread = $msgid->lastread; $this->messages[$msgid->thread]->unread = $msgid->unread; } } } }
function __construct($catid, $page = 0) { kimport('html.parser'); $this->func = 'showcat'; $this->catid = $catid; $this->page = $page; $this->hasSubCats = ''; $template = KunenaFactory::getTemplate(); $this->params = $template->params; $this->db = JFactory::getDBO(); $this->my = JFactory::getUser(); $this->myprofile = KunenaFactory::getUser(); $this->session = KunenaFactory::getSession(); $this->config = KunenaFactory::getConfig(); if (!$this->catid) { return; } if (!$this->session->canRead($this->catid)) { return; } $this->allow = 1; $this->tabclass = array("row1", "row2"); $this->prevCheck = $this->session->lasttime; $this->app =& JFactory::getApplication(); //Get the category information $query = "SELECT c.*, s.catid AS subscribeid\n\t\t\t\tFROM #__kunena_categories AS c\n\t\t\t\tLEFT JOIN #__kunena_subscriptions_categories AS s ON c.id = s.catid\n\t\t\t\tAND s.userid = '{$this->my->id}'\n\t\t\t\tWHERE c.id={$this->db->Quote($this->catid)}"; $this->db->setQuery($query); $this->objCatInfo = $this->db->loadObject(); if (KunenaError::checkDatabaseError()) { return; } //Get the Category's parent category name for breadcrumb $this->db->setQuery("SELECT name, id FROM #__kunena_categories WHERE id={$this->db->Quote($this->objCatInfo->parent)}"); $objCatParentInfo = $this->db->loadObject(); if (KunenaError::checkDatabaseError()) { return; } //check if this forum is locked $this->kunena_forum_locked = $this->objCatInfo->locked; //check if this forum is subject to review $this->kunena_forum_reviewed = $this->objCatInfo->review; $threads_per_page = $this->config->threads_per_page; $access = KunenaFactory::getAccessControl(); $hold = $access->getAllowedHold($this->myprofile, $this->catid); /*//////////////// Start selecting messages, prepare them for threading, etc... /////////////////*/ $this->page = $this->page < 1 ? 1 : $this->page; $offset = ($this->page - 1) * $threads_per_page; $row_count = $this->page * $threads_per_page; $this->db->setQuery("SELECT COUNT(*) FROM #__kunena_messages WHERE parent='0' AND catid={$this->db->Quote($this->catid)} AND hold IN ({$hold})"); $this->total = (int) $this->db->loadResult(); KunenaError::checkDatabaseError(); $this->totalpages = ceil($this->total / $threads_per_page); $query = "SELECT t.id, MAX(m.id) AS lastid FROM #__kunena_messages AS t\n\tINNER JOIN #__kunena_messages AS m ON t.id = m.thread\n\tWHERE t.parent='0' AND t.hold IN ({$hold}) AND t.catid={$this->db->Quote($this->catid)} AND m.hold IN ({$hold}) AND m.catid={$this->db->Quote($this->catid)}\n\tGROUP BY m.thread ORDER BY t.ordering DESC, lastid DESC"; $this->db->setQuery($query, $offset, $threads_per_page); $threadids = $this->db->loadResultArray(); KunenaError::checkDatabaseError(); $idstr = implode(",", $threadids); $this->messages = array(); $this->threads = array(); $this->highlight = 0; $routerlist = array(); if (count($threadids) > 0) { $query = "SELECT a.*, j.id AS userid, t.message AS message, l.myfavorite, l.favcount, l.attachments,\n\t\t\t\t\t\t\tl.msgcount, l.lastid, l.lastid AS lastread, 0 AS unread, j.username, j.name AS uname, u.avatar, c.id AS catid, c.name AS catname, c.class_sfx\n\tFROM (\n\t\tSELECT m.thread, MAX(f.userid='{$this->my->id}') AS myfavorite, COUNT(DISTINCT f.userid) AS favcount, COUNT(a.mesid) AS attachments,\n\t\t\tCOUNT(DISTINCT m.id) AS msgcount, MAX(m.id) AS lastid, MAX(m.time) AS lasttime\n\t\tFROM #__kunena_messages AS m"; if ($this->config->allowfavorites) { $query .= " LEFT JOIN #__kunena_favorites AS f ON f.thread = m.thread"; } else { $query .= " LEFT JOIN #__kunena_favorites AS f ON f.thread = 0"; } $query .= "\n\t\tLEFT JOIN #__kunena_attachments AS a ON a.mesid = m.thread\n\t\tWHERE m.hold IN ({$hold}) AND m.thread IN ({$idstr})\n\t\tGROUP BY thread\n\t) AS l\n\tINNER JOIN #__kunena_messages AS a ON a.thread = l.thread\n\tINNER JOIN #__kunena_messages_text AS t ON a.thread = t.mesid\n\tLEFT JOIN #__users AS j ON j.id = a.userid\n\tLEFT JOIN #__kunena_users AS u ON u.userid = j.id\n\tLEFT JOIN #__kunena_categories AS c ON c.id = a.catid\n\tWHERE (a.parent='0' OR a.id=l.lastid)\n\tORDER BY ordering DESC, lastid DESC"; $this->db->setQuery($query); $this->messages = $this->db->loadObjectList('id'); KunenaError::checkDatabaseError(); // collect user ids for avatar prefetch when integrated $userlist = array(); foreach ($this->messages as $message) { if ($message->parent == 0) { $this->threads[$message->thread] = $message; $routerlist[$message->id] = $message->subject; if ($message->ordering) { $this->highlight++; } } if ($message->id == $message->lastid) { $this->lastreply[$message->thread] = $message; } $userlist[intval($message->userid)] = intval($message->userid); $userlist[intval($message->modified_by)] = intval($message->modified_by); } require_once KUNENA_PATH . '/router.php'; KunenaRouter::loadMessages($routerlist); if ($this->config->shownew && $this->my->id) { $readlist = $this->session->readtopics; $this->db->setQuery("SELECT thread, MIN(id) AS lastread, SUM(1) AS unread FROM #__kunena_messages " . "WHERE hold IN ({$hold}) AND moved='0' AND thread NOT IN ({$readlist}) AND thread IN ({$idstr}) AND time>{$this->db->Quote($this->prevCheck)} GROUP BY thread"); $msgidlist = $this->db->loadObjectList(); KunenaError::checkDatabaseError(); foreach ($msgidlist as $msgid) { $this->messages[$msgid->thread]->lastread = $msgid->lastread; $this->messages[$msgid->thread]->unread = $msgid->unread; } } } //Perform subscriptions check $kunena_cansubscribecat = 0; if ($this->config->allowsubscriptions && $this->config->category_subscriptions != 'disabled' && ("" != $this->my->id || 0 != $this->my->id)) { if ($this->objCatInfo->subscribeid == '') { $kunena_cansubscribecat = 1; } } //meta description and keywords $metaKeys = kunena_htmlspecialchars(JText::_('COM_KUNENA_CATEGORIES') . ", {$objCatParentInfo->name}, {$this->objCatInfo->name}, {$this->config->board_title}, " . $this->app->getCfg('sitename')); $metaDesc = kunena_htmlspecialchars("{$this->objCatInfo->description} ({$this->page}/{$this->totalpages})"); $document =& JFactory::getDocument(); $cur = $document->get('description'); $metaDesc = $cur . '. ' . $metaDesc; $document =& JFactory::getDocument(); $document->setMetadata('keywords', $metaKeys); $document->setDescription($metaDesc); $this->headerdesc = $this->objCatInfo->headerdesc; if (CKunenaTools::isModerator($this->my->id, $this->catid) || !$this->kunena_forum_locked) { //this user is allowed to post a new topic: $this->forum_new = CKunenaLink::GetPostNewTopicLink($this->catid, CKunenaTools::showButton('newtopic', JText::_('COM_KUNENA_BUTTON_NEW_TOPIC')), 'nofollow', 'kicon-button kbuttoncomm btn-left', JText::_('COM_KUNENA_BUTTON_NEW_TOPIC_LONG')); } if ($this->my->id != 0 && $this->total) { $this->forum_markread = CKunenaLink::GetCategoryActionLink('markthisread', $this->catid, CKunenaTools::showButton('markread', JText::_('COM_KUNENA_BUTTON_MARKFORUMREAD')), 'nofollow', 'kicon-button kbuttonuser btn-left', JText::_('COM_KUNENA_BUTTON_MARKFORUMREAD_LONG')); } // Thread Subscription if ($kunena_cansubscribecat == 1) { // this user is allowed to subscribe - check performed further up to eliminate duplicate checks // for top and bottom navigation $this->thread_subscribecat = CKunenaLink::GetCategoryActionLink('subscribecat', $this->catid, CKunenaTools::showButton('subscribe', JText::_('COM_KUNENA_BUTTON_SUBSCRIBE_CATEGORY')), 'nofollow', 'kicon-button kbuttonuser btn-left', JText::_('COM_KUNENA_BUTTON_SUBSCRIBE_CATEGORY_LONG')); } if ($this->my->id != 0 && $this->config->allowsubscriptions && $this->config->category_subscriptions != 'disabled' && $kunena_cansubscribecat == 0) { // this user is allowed to unsubscribe $this->thread_subscribecat = CKunenaLink::GetCategoryActionLink('unsubscribecat', $this->catid, CKunenaTools::showButton('subscribe', JText::_('COM_KUNENA_BUTTON_UNSUBSCRIBE_CATEGORY')), 'nofollow', 'kicon-button kbuttonuser btn-left', JText::_('COM_KUNENA_BUTTON_UNSUBSCRIBE_CATEGORY_LONG')); } //get the Moderator list for display $this->db->setQuery("SELECT * FROM #__kunena_moderation AS m INNER JOIN #__users AS u ON u.id=m.userid WHERE m.catid={$this->db->Quote($this->catid)} AND u.block=0"); $this->modslist = $this->db->loadObjectList(); KunenaError::checkDatabaseError(); foreach ($this->modslist as $mod) { $userlist[intval($mod->userid)] = intval($mod->userid); } // Prefetch all users/avatars to avoid user by user queries during template iterations if (!empty($userlist)) { KunenaUser::loadUsers($userlist); } $this->columns = CKunenaTools::isModerator($this->my->id, $this->catid) ? 6 : 5; $this->showposts = 0; $this->actionDropdown[] = JHTML::_('select.option', '', JText::_('COM_KUNENA_BULK_CHOOSE_ACTION')); }