/**
* Get the categories from JoomGallery
*
* @param object $params backend parameters
* @param string $dberror database error
* @param int $module_id the id of the module
* @return object $jc_rows category objects
*/
public function getCats($params, &$dberror, $module_id)
{
// Read the parameters
$this->getParams($params, $module_id);
// Get JoomGallery styles
$this->getPageHeader();
// Create and include the dynamic css for default view
// according to backend settings
$this->renderCSS();
$rootcat = $this->getConfig('rootcat');
if ($rootcat > 1) {
$catlist = JoomHelper::getAllSubCategories($rootcat, false, true, false, $this->getConfig('showhidden') ? false : true);
}
// Category filter
$catblacklist = array();
$blacklist_cats = $this->getConfig('blacklist_cats');
if (!empty($blacklist_cats)) {
$catblacklist_cfg = explode(',', $blacklist_cats);
foreach ($catblacklist_cfg as $cat) {
if (!in_array($cat, $catblacklist)) {
$catblacklist[] = $cat;
$catblacklist = array_merge($catblacklist, JoomHelper::getAllSubCategories($cat, false, true, false, $this->getConfig('showhidden') ? false : true));
}
}
}
$authorisedViewLevels = implode(',', $this->_user->getAuthorisedViewLevels());
$query = $this->_db->getQuery(true)->select('ca.cid')->select('ca.name')->select('ca.description')->select('ca.catpath')->select('ca.alias')->select('ca.thumbnail')->select('ca.lft');
if ($this->getConfig('showrate') || $this->getConfig('catmode') == 1) {
$query->select('IF(SUM(jg.imgvotes) > 0, SUM(' . JoomHelper::getSQLRatingClause('jg') . ') / count(jg.id), 0) AS catrating, SUM(jg.imgvotes) AS catimgvotes');
}
if ($this->getConfig('showhits') || $this->getConfig('catmode') == 2) {
$query->select('SUM(jg.hits) AS cathits');
}
$query->from(_JOOM_TABLE_CATEGORIES . ' AS ca')->innerJoin(_JOOM_TABLE_IMAGES . ' AS jg ON ca.cid = jg.catid')->where('ca.published = 1')->where('ca.access IN (' . $authorisedViewLevels . ')');
// Inheritance of category access must be considered here
$allowed_cids = array_keys($this->_ambit->getCategoryStructure());
if (!empty($allowed_cids)) {
$query->where('ca.cid IN (' . implode(',', $allowed_cids) . ')');
}
if (!$this->getConfig('showhidden')) {
$query->where('ca.hidden = 0')->where('ca.in_hidden = 0');
}
// Category filter
if (count($catblacklist) > 0) {
$query->where('ca.cid NOT IN (' . implode(',', $catblacklist) . ')');
}
// Root category
if ($rootcat > 1) {
if (count($catlist) > 0) {
$query->where('ca.cid IN (' . implode(',', $catlist) . ')');
} else {
// No category will have cid = 0, just to have a query
// with no result categories
$query->where('ca.cid = 0');
}
}
$query->where('jg.published = 1')->where('jg.approved = 1')->where('jg.access IN (' . $authorisedViewLevels . ')');
if (!$this->getConfig('showhidden')) {
$query->where('jg.hidden = 0');
}
switch ($this->getConfig('catmode')) {
// Top rated
case 1:
$query->group('ca.cid')->order('catrating DESC');
break;
// Most viewed
// Most viewed
case 2:
$query->group('ca.cid')->order('cathits DESC');
break;
// Random
// Random
case 3:
$query->group('ca.cid')->order('rand()');
break;
// Alphabetical ascending
// Alphabetical ascending
case 4:
$query->group('ca.cid')->order('ca.name ASC');
break;
// Alphabetical descending
// Alphabetical descending
case 5:
$query->group('ca.cid')->order('ca.name DESC');
break;
// Ordering ascending
// Ordering ascending
case 6:
$query->group('ca.cid')->order('ca.lft ASC');
break;
// Ordering descending
// Ordering descending
case 7:
$query->group('ca.cid')->order('ca.lft DESC');
break;
// Default, last added
// Default, last added
default:
$query->group('ca.cid DESC');
break;
}
$this->_db->setQuery($query, 0, $this->getConfig('categorycount'));
$dberror = '';
try {
$cat_rows = $this->_db->loadObjectList();
} catch (RuntimeException $e) {
$dberror = JText::_('MOD_JOOMCAT_DB_ERROR_LBL') . ': ' . $e->getMessage();
}
return isset($cat_rows) ? $cat_rows : null;
}
/**
* Returns the query for loading the images
*
* @return object The query to be used to retrieve the images data from the database
* @since 1.5.5
*/
protected function _buildImagesQuery()
{
$authorisedViewLevels = implode(',', $this->_user->getAuthorisedViewLevels());
$query = $this->_db->getQuery(true)->select('*, a.owner AS owner, ' . JoomHelper::getSQLRatingClause('a') . 'AS rating');
if ($this->_config->get('jg_showcatcom')) {
$subquery = $this->_db->getQuery(true)->select('COUNT(cmtid)')->from(_JOOM_TABLE_COMMENTS)->where('cmtpic = a.id')->where('published = 1')->where('approved = 1');
$query->select('(' . $subquery . ') AS comments');
}
$query->from(_JOOM_TABLE_IMAGES . ' AS a')->leftJoin(_JOOM_TABLE_CATEGORIES . ' AS c ON c.cid = a.catid')->where('a.published = 1')->where('a.catid = ' . $this->_id)->where('a.approved = 1')->where('a.hidden = 0')->where('a.access IN (' . $authorisedViewLevels . ')')->where('c.access IN (' . $authorisedViewLevels . ')');
if ($this->_config->get('jg_firstorder')) {
$query->order('a.' . $this->_config->get('jg_firstorder'));
}
if ($this->_config->get('jg_secondorder')) {
$query->order('a.' . $this->_config->get('jg_secondorder'));
}
if ($this->_config->get('jg_thirdorder')) {
$query->order('a.' . $this->_config->get('jg_thirdorder'));
}
if ($this->_config->get('jg_usercatorder')) {
$user_orderby = $this->_mainframe->getUserStateFromRequest('joom.category.images.orderby', 'orderby');
$user_orderdir = $this->_mainframe->getUserStateFromRequest('joom.category.images.orderdir', 'orderdir');
$orderby = '';
switch ($user_orderby) {
case 'user':
$orderby = 'a.owner';
break;
case 'date':
$orderby = 'a.imgdate';
break;
case 'rating':
$orderby = 'rating';
break;
case 'title':
$orderby = 'a.imgtitle';
break;
case 'hits':
$orderby = 'a.hits';
break;
default:
// Use selected ordering above
break;
}
if ($user_orderby == 'title' || $user_orderby == 'hits' || $user_orderby == 'user' || $user_orderby == 'date' || $user_orderby == 'rating') {
if ($user_orderdir == 'desc') {
$orderby .= ' DESC';
} else {
if ($user_orderdir == 'asc') {
$orderby .= ' ASC';
}
}
}
if ($user_orderby == 'rating') {
$orderby .= ', imgvotesum DESC';
}
if (!empty($orderby)) {
$query->clear('order');
$query->order($orderby);
}
}
return $query;
}
/**
* Assemble the query for reading the image data from database
*
* @return object - image objects
*/
function getDBImages()
{
$db = JFactory::getDBo();
$query = $db->getQuery(true);
$user = JFactory::getUser();
$limit = $this->getConfig('limit');
$sorting = $this->getConfig('sorting');
$objects = array();
// Deny sorting by nametags if not activated or no permission
if (stristr($sorting, 'n.ndate')) {
if ($this->getJConfig('jg_nameshields') == 0) {
return $objects;
} else {
if ($user->guest && !$this->getJConfig('jg_nameshields_unreg')) {
return $objects;
}
}
}
if ($this->cmtTest(1)) {
if ($this->getConfig('sorting') == "commentrand") {
$sorting = 'rand()';
}
}
// *** Assemble the select part ***
$query->select('p.id AS id, p.catid, p.imgthumbname, p.imgfilename, p.imgtitle,p.imgtext');
// Show author
if ($this->getConfig('showauthor')) {
$query->select('p.imgauthor, p.owner');
}
// Show category name
if ($this->getConfig('showcategory')) {
$query->select('c.name AS cattitle');
}
// Show hits
if ($this->getConfig('showhits')) {
$query->select('p.hits');
}
// Show downloads
if ($this->getConfig('showdownloads')) {
$query->select('p.downloads');
}
// Show rating or sort by rating
if (stristr($this->getConfig('sorting'), 'rating') || $this->getConfig('showrate')) {
$query->select('p.imgvotes');
$query->select(JoomHelper::getSQLRatingClause() . ' AS rating');
}
// Show date of image
if ($this->getConfig('showimgdate') || $this->getConfig('showpicasnew')) {
$query->select('p.imgdate as imgdate');
}
$query->select('c.cid AS ccid, c.catpath AS catpath');
if (stristr($sorting, 'ntcount')) {
$query->select('COUNT(nid) FROM ' . _JOOM_TABLE_NAMESHIELDS . ' AS na' . ' WHERE p.id=na.nid) AS ntcount');
}
if ($this->cmtTest(1)) {
$cmtdate = 'co.cmtdate';
if ($sorting == 'co.cmtdate ASC') {
$cmtdate = 'MAX(co.cmtdate)';
$sorting = 'cmtdate ASC';
} else {
if ($sorting == 'co.cmtdate DESC') {
$cmtdate = 'MAX(co.cmtdate)';
$sorting = 'cmtdate DESC';
}
}
$query->select($cmtdate . ' AS cmtdate');
}
// Check if text option 'number of comments'
// or 'sorting by comments' activated
if ($this->getConfig('shownumcomments') || strstr($sorting, 'cmtcount')) {
$query->select('count(co.cmtid) AS cmtcount');
}
// *** Assemble the from part ***
if ($this->cmtTest(1)) {
$query->from(_JOOM_TABLE_COMMENTS . ' as co');
} else {
$query->from(_JOOM_TABLE_IMAGES . ' AS p');
}
// *** Assemble the join clauses ***
if ($this->cmtTest(1)) {
$query->join('RIGHT', _JOOM_TABLE_IMAGES . ' AS p ON co.cmtpic = p.id');
}
$query->join('LEFT', _JOOM_TABLE_CATEGORIES . ' AS c ON c.cid = p.catid');
// If sorting by date of nametags
if (stristr($sorting, 'n.ndate')) {
$query->join('LEFT', _JOOM_TABLE_NAMESHIELDS . ' AS n ON n.npicid = p.id');
}
// Check if any comment text options activated
if ($this->getConfig('shownumcomments') && !$this->cmtTest(1)) {
$query->join('LEFT', _JOOM_TABLE_COMMENTS . ' AS co ON co.cmtpic = p.id');
}
// *** Assemble the where clauses ***
$authorisedViewLevels = implode(",", $user->getAuthorisedViewLevels());
$query->where('c.published = 1');
$query->where('c.access IN (' . $authorisedViewLevels . ')');
$query->where('p.published = 1');
$query->where('p.approved = 1');
$query->where('p.access IN (' . $authorisedViewLevels . ')');
// Inheritance of category access must be considered here
$allowed_cids = array_keys($this->_ambit->getCategoryStructure());
if (!empty($allowed_cids)) {
$query->where('p.catid IN (' . implode(',', $allowed_cids) . ')');
}
// Check the current category shown in JoomGallery
if ($this->getConfig('dynamiccats') && ($currcat = $this->getCurrentCat()) != 0) {
$query->where('p.catid = ' . $currcat);
}
// Show or hide categories
if ($this->getConfig('cats')) {
$catinnotin = $this->getConfig('showorhidecats') == 1 ? ' IN' : ' NOT IN';
if ($this->getConfig('includesubcats')) {
// Include subcategories
$catsincsubcats = $this->getSubcategories($this->getConfig('cats'));
$query->where('p.catid' . $catinnotin . ' (' . $catsincsubcats . ')');
} else {
$query->where('p.catid' . $catinnotin . ' (' . $this->getConfig('cats') . ')');
}
}
// Timespan filter
if ($this->getConfig('resultbytime') != 0) {
$query->where($this->getSQLTimestring($this->getConfig('resultbytime')));
}
// Show only not hidden images from not hidden categories
if (!$this->getConfig('showhidden')) {
$query->where('c.hidden = 0');
$query->where('c.in_hidden = 0');
$query->where('p.hidden = 0');
}
// Show only images with votes = x
if ($this->getConfig('votesctsel') != -1) {
$query->where('p.imgvotes = ' . $this->getConfig('votesctsel'));
}
// *** Assemble the group clause ***
// Check if any comment sorting active
if ($this->cmtTest(1)) {
$query->group('p.id');
}
// *** Assemble the order clause ***
$query->order($sorting);
$limit = $this->getConfig('limit');
// Total number of images or pagination set
if ($limit != 0) {
$db->setQuery($query, 0, $limit);
} else {
$db->setQuery($query);
}
$objects = $db->loadObjectList('id');
if ($error = $db->getErrorMsg()) {
throw new Exception($error);
}
// Get the date and/or text from last comment if one of the options activated
if ($this->cmtTest(2)) {
$this->getLastComments($objects);
}
// Deactivate pagination if there are not enough images
if ($this->getConfig('pagination') && count($objects) <= $this->getConfig('paginationct')) {
$this->addConfig('pagination', 0);
}
return $objects;
}
/**
* Method to get the images searched for from the database
*
* @return boolean True on success, false otherwise
* @since 1.5.5
*/
protected function _loadSearchResults()
{
if (empty($this->_searchResults)) {
$sstring = JRequest::getString('sstring');
$searchstring = $this->_db->escape(trim($sstring));
$categories = $this->_ambit->getCategoryStructure();
$authorisedViewLevels = implode(',', $this->_user->getAuthorisedViewLevels());
// Create query object
$query = $this->_db->getQuery(true);
// Create search part of the query
$where = '(u.username LIKE ' . $this->_db->q('%' . $searchstring . '%') . '
OR a.imgtitle LIKE ' . $this->_db->q('%' . $searchstring . '%') . '
OR LOWER(a.imgtext) LIKE ' . $this->_db->q('%' . $searchstring . '%');
// Add query parts from plugins
$aliases = array('images' => 'a', 'categories' => 'ca');
$plugins = $this->_mainframe->triggerEvent('onJoomSearch', array($searchstring, $aliases, _JOOM_OPTION . '.search'));
foreach ($plugins as $plugin) {
if (isset($plugin['images.select'])) {
$query->select($plugin['images.select']);
}
if (isset($plugin['images.leftjoin'])) {
$query->leftJoin($plugin['images.leftjoin']);
}
if (isset($plugin['images.where'])) {
$query->where($plugin['images.where']);
}
if (isset($plugin['images.where.or'])) {
$where .= '
OR ' . $plugin['images.where.or'];
}
}
// Only now the search part can be finalized
$where .= ')';
// General select clause of the query
$query->select('a.*, ' . JoomHelper::getSQLRatingClause('a') . ' AS rating, u.username, ca.cid, ca.name AS name');
// Count comments of each image if required
if ($this->_config->get('jg_showcatcom')) {
$subquery = $this->_db->getQuery(true)->select('COUNT(*)')->from(_JOOM_TABLE_COMMENTS)->where('cmtpic = a.id')->where('published = 1')->where('approved = 1');
$query->select('(' . $subquery . ') As comments');
}
// Main part of the query
$query->from(_JOOM_TABLE_IMAGES . ' AS a')->innerJoin(_JOOM_TABLE_CATEGORIES . ' AS ca ON a.catid = ca.cid')->leftJoin('#__users AS u ON a.owner = u.id')->where($where)->where('a.published = 1')->where('a.approved = 1')->where('a.access IN (' . $authorisedViewLevels . ')')->where('ca.published = 1')->where('ca.access IN (' . $authorisedViewLevels . ')')->where('ca.cid IN (' . implode(',', array_keys($categories)) . ')')->where('a.hidden = 0')->where('ca.hidden = 0')->where('ca.in_hidden = 0')->where('ca.exclude_search = 0')->group('a.id')->order('a.id DESC');
$this->_db->setQuery($query);
if (!($rows = $this->_db->loadObjectList())) {
return false;
}
$this->_searchResults = $rows;
}
return true;
}
/**
* Method to load the image data from the database
*
* @return boolean True on success, false otherwise
* @since 1.5.5
*/
protected function _loadFavourites()
{
// Load the images if they don't already exist
if (empty($this->_favourites)) {
$query = $this->_db->getQuery(true)->select('*, a.catid, a.owner AS imgowner, ' . JoomHelper::getSQLRatingClause('a') . ' AS rating');
if ($this->_config->get('jg_showcatcom')) {
$subquery = $this->_db->getQuery(true)->select('COUNT(*)')->from(_JOOM_TABLE_COMMENTS)->where('cmtpic = a.id')->where('published = 1')->where('approved = 1');
$query->select('(' . $subquery . ') AS comments');
}
$query->from(_JOOM_TABLE_IMAGES . ' AS a')->from(_JOOM_TABLE_CATEGORIES . ' AS c')->where('a.catid = c.cid')->where('a.published = 1')->where('a.approved = 1')->where('c.published = 1')->where('a.access IN (' . implode(',', $this->_user->getAuthorisedViewLevels()) . ')')->where('c.access IN (' . implode(',', $this->_user->getAuthorisedViewLevels()) . ')')->where('c.hidden = 0')->where('c.in_hidden = 0');
if (is_null($this->piclist)) {
$query->where('0');
} else {
$query->where('a.id IN (' . $this->piclist . ')');
}
$query->order($this->_db->escape($this->getState('list.ordering') . ' ' . $this->getState('list.direction')));
$this->_db->setQuery($query);
$rows = $this->_db->loadObjectList();
if ($error = $this->_db->getErrorMsg()) {
$this->setError($error);
return false;
}
$categories = $this->_ambit->getCategoryStructure();
foreach ($rows as $key => $row) {
if (!isset($categories[$row->catid])) {
unset($rows[$key]);
}
}
$this->_favourites = $rows;
// The list of favourites is filtered now, so that only valid images are chosen.
// So we store this list now in order to delete invalid images from the list.
if ($this->using_database) {
$ids = '';
foreach ($rows as $row) {
$ids .= $row->id . ',';
}
$query->clear()->update(_JOOM_TABLE_USERS)->set('piclist = ' . (count($rows) ? $this->_db->q(trim($ids, ',')) : 'NULL'))->where('uuserid = ' . $this->_user->get('id'));
$this->_db->setQuery($query);
$this->_db->query();
}
return true;
}
}
/**
* Method to load the most viewd images from the database
*
* @return boolean True on success, false otherwise
* @since 1.5.5
*/
protected function _loadMostViewed()
{
if (empty($this->_mostViewed)) {
$categories = $this->_ambit->getCategoryStructure();
$authorisedViewLevels = implode(',', $this->_user->getAuthorisedViewLevels());
$query = $this->_db->getQuery(true)->select('*, a.owner AS owner, ' . JoomHelper::getSQLRatingClause('a') . ' AS rating');
if ($this->_config->get('jg_showcatcom')) {
$subquery = $this->_db->getQuery(true)->select('COUNT(*)')->from(_JOOM_TABLE_COMMENTS)->where('cmtpic = a.id')->where('published = 1')->where('approved = 1');
$query->select('(' . $subquery->__toString() . ') As comments');
}
$query->from(_JOOM_TABLE_IMAGES . ' AS a')->from(_JOOM_TABLE_CATEGORIES . ' AS ca')->where('a.hits > 0')->where('a.catid = ca.cid')->where('a.published = 1')->where('a.approved = 1')->where('a.access IN (' . $authorisedViewLevels . ')')->where('ca.published = 1')->where('ca.access IN (' . $authorisedViewLevels . ')')->where('ca.cid IN (' . implode(',', array_keys($categories)) . ')')->where('a.hidden = 0')->where('ca.hidden = 0')->where('ca.in_hidden = 0')->where('ca.exclude_toplists = 0')->order('hits DESC');
$this->_db->setQuery($query, 0, (int) $this->_config->get('jg_toplist'));
if (!($rows = $this->_db->loadObjectList())) {
return false;
}
$this->_mostViewed = $rows;
}
return true;
}
/**
* Returns the rating of an image
*
* @param string $imgid Image id to get the rating for
* @return float Rating
* @since 1.5.6
*/
public static function getRating($imgid)
{
$db = JFactory::getDBO();
$rating = 0.0;
$query = $db->getQuery(true)->select(JoomHelper::getSQLRatingClause() . ' AS rating')->from(_JOOM_TABLE_IMAGES)->where('id = ' . $imgid);
$db->setQuery($query);
if (($result = $db->loadResult()) != null) {
$rating = $result;
}
return $rating;
}
/**
* Returns a query object containing the general data for requesting images data
*
* @param array $access Array of access levels to filter with
* @return object A JDatabaseQuery object
* @since 2.0
*/
public function getImagesQuery($access = null)
{
$query = $this->_db->getQuery(true);
if ($this->getConfig('shownumcomments')) {
$query->select('(' . $this->getNumCommentsSubQuery() . ') AS cmtcount');
}
$query->select('jg.id, jg.catid, jg.imgthumbname, jg.imgfilename, jg.owner, jg.imgauthor,
jg.imgdate, jg.imgtitle, jg.imgtext, jg.hits, jg.downloads, jg.imgvotes,
' . JoomHelper::getSQLRatingClause('jg') . ' AS rating,
jgc.name AS cattitle, jgc.catpath AS catpath')->from(_JOOM_TABLE_IMAGES . ' AS jg')->leftJoin(_JOOM_TABLE_CATEGORIES . ' AS jgc ON jgc.cid = jg.catid');
if ($this->getConfig('showlastcomment')) {
$query->select('jgco.cmttext, jgco.cmtdate, jgco.userid, jgco.cmtid')->leftJoin(_JOOM_TABLE_COMMENTS . ' AS jgco ON jg.id = jgco.cmtpic')->leftJoin(_JOOM_TABLE_COMMENTS . ' AS jgco2 ON jgco.cmtpic = jgco2.cmtpic AND jgco.cmtdate < jgco2.cmtdate')->where('jgco2.cmtpic IS NULL');
}
$this->addImageWhere($query, $access);
return $query;
}
/**
* Get new rating for the image voted to refresh detail view
*
* @return object Holds the image rating data
* @since 2.1
*/
public function getRating()
{
$query = $this->_db->getQuery(true)->select('imgvotes, imgvotesum, ' . JoomHelper::getSQLRatingClause() . ' AS rating')->from(_JOOM_TABLE_IMAGES)->where('id = ' . $this->_id);
$this->_db->setQuery($query);
return $this->_db->loadObject();
}
/**
* Method to load the images data
*
* @return boolean True on success, false otherwise
* @since 1.5.5
*/
protected function _loadImages()
{
if (!$this->_id) {
return false;
}
// Load the images data if it doesn't already exist
if (empty($this->_images)) {
$authorisedViewLevels = implode(',', $this->_user->getAuthorisedViewLevels());
// First get the category the image belongs too
$query = $this->_db->getQuery(true)->select('catid')->from(_JOOM_TABLE_IMAGES)->where('id = ' . $this->_id);
$this->_db->setQuery($query);
if (!($catid = $this->_db->loadResult())) {
$this->_mainframe->redirect(JRoute::_('index.php?view=gallery', false), JText::sprintf('COM_JOOMGALLERY_COMMON_ERROR_IMAGE_NOT_FOUND', $this->_id), 'error');
}
// Get all the images data of that category
$query->clear()->select('a.*, a.owner AS imgowner, c.metadesc AS catmetadesc, c.metakey AS catmetakey')->select(JoomHelper::getSQLRatingClause('a') . ' AS rating')->from(_JOOM_TABLE_IMAGES . ' AS a')->leftJoin(_JOOM_TABLE_CATEGORIES . ' AS c ON c.cid = a.catid')->where('a.catid = ' . $catid)->where('a.published = 1')->where('a.approved = 1')->where('(a.hidden = 0 OR a.id = ' . $this->_id . ')')->where('a.access IN (' . $authorisedViewLevels . ')')->where('c.access IN (' . $authorisedViewLevels . ')')->order('a.' . $this->_config->get('jg_firstorder'));
if ($this->_config->get('jg_secondorder')) {
$query->order('a.' . $this->_config->get('jg_secondorder'));
if ($this->_config->get('jg_thirdorder')) {
$query->order('a.' . $this->_config->get('jg_thirdorder'));
}
}
$this->_db->setQuery($query);
if (!($rows = $this->_db->loadObjectList())) {
$this->_mainframe->redirect(JRoute::_('index.php?view=gallery', false), JText::sprintf('COM_JOOMGALLERY_COMMON_ERROR_IMAGE_NOT_FOUND', $this->_id), 'error');
}
$this->_images = $rows;
return true;
}
return true;
}
