<?php /* ====================================================== # Monthly Archive - Joomla! Component v4.3.3 (PRO version) # ------------------------------------------------------- # For Joomla! 3.x # Author: Yiannis Christodoulou (yiannis@web357.eu) # Copyright (©) 2009-2018 Web357. All rights reserved. # License: GNU/GPLv3, http://www.gnu.org/licenses/gpl-3.0.html # Website: https://www.web357.eu/ # Demo: http://demo.web357.eu/?item=monthlyarchive # Support: support@web357.eu # Last modified: 09 Feb 2018, 13:55:18 ========================================================= */ defined('_JEXEC') or die; class MonthlyarchiveModelArchive extends JModelList { /** * Constructor. * * @param array $config An optional associative array of configuration settings. * * @see JController * @since 1.6 */ public function __construct($config = array()) { parent::__construct($config); } /** * Method to auto-populate the model state. * * Note. Calling getState in this method will result in recursion. * * @param string $ordering Elements order * @param string $direction Order direction * * @return void * * @throws Exception * * @since 1.6 */ protected function populateState($ordering = null, $direction = null) { // Get Params $app = JFactory::getApplication('site'); $params = $app->getParams('com_monthlyarchive'); $list = $app->getUserState($this->context . '.list'); $ordering = isset($list['filter_order']) ? $list['filter_order'] : null; $direction = isset($list['filter_order_Dir']) ? $list['filter_order_Dir'] : null; $list['limit'] = (int) JFactory::getConfig()->get('list_limit', 20); $list['start'] = $app->input->getInt('start', 0); $list['ordering'] = $ordering; $list['direction'] = $direction; $app->setUserState($this->context . '.list', $list); $app->input->set('list', null); // List state information. parent::populateState($ordering, $direction); $app = JFactory::getApplication(); $ordering = $app->getUserStateFromRequest($this->context . '.ordercol', 'filter_order', $ordering); $direction = $app->getUserStateFromRequest($this->context . '.orderdirn', 'filter_order_Dir', $ordering); $this->setState('list.ordering', $ordering); $this->setState('list.direction', $direction); $start = $app->getUserStateFromRequest($this->context . '.limitstart', 'limitstart', 0, 'int'); $limit = $app->getUserStateFromRequest($this->context . '.limit', 'limit', 0, 'int'); if ($limit == 0) { $limit = $app->get('list_limit', 0); } $this->setState('list.limit', $limit); $this->setState('list.start', $start); // get type of contents $display_type = $params->get('display_type', 'list_of_months_and_years'); // Set 0 list limit for monthlyarchive list of months and years $get_year = JFactory::getApplication()->input->get('year', '', 'STRING'); $get_month = JFactory::getApplication()->input->get('month', '', 'INT'); $get_month = (is_numeric($get_month) && $get_month > 0) ? sprintf("%02d", $get_month) : $get_month; if ($display_type == 'list_of_all_articles' || (((is_numeric($get_year) && $get_year > 0) || $get_year == 'all') || (is_numeric($get_month) && $get_month > 0))) { $limit = $params->get('limit_of_articles_per_page', '30'); if ($limit == 0) { $this->setState('list.limit', 0); $this->setState('list.start', $start); }else{ $this->setState('list.limit', $limit); $this->setState('list.start', $start); } $filter_date_state = $this->state->get("filter.date"); if (empty($filter_date_state)) { if (is_numeric($get_month) && $get_month > 0) { $this->setState('filter.date', $get_year.'-'.$get_month); }else{ $this->setState('filter.date', $get_year); } } } elseif ($display_type == 'list_of_months_and_years') { $this->setState('list.limit', 0); } // set state for null vars (e.g. if you navigate from one page to another we don't want to keep states) if (empty($get_year) && empty($get_month)) { $set_filter_date = (!empty($get_year)) ? $get_year : ''; $set_filter_date .= (!empty($get_year) && !empty($get_month)) ? '-'.$get_month : ''; $this->setState('filter.date', $set_filter_date); } // Filter Category $get_category = $app->getUserStateFromRequest($this->context . '.filter.category', 'filter_category', '', 'INT'); $this->setState('filter.category', $get_category); // Filter Author $user_id = JFactory::getUser()->get('id'); $show_only_own_articles = $params->get('show_only_own_articles', '0'); if ($show_only_own_articles && $user_id > 0) { $get_author = $app->getUserStateFromRequest($this->context . '.filter.author', 'filter_author', $user_id, 'INT'); } else { $get_author = $app->getUserStateFromRequest($this->context . '.filter.author', 'filter_author', '', 'INT'); } $this->setState('filter.author', $get_author); // Filter Order $get_orderby = $app->getUserStateFromRequest($this->context . '.filter.orderby', 'filter_orderby', '', 'STRING'); $this->setState('filter.orderby', $get_orderby); // Filter Search $get_search = $app->getUserStateFromRequest($this->context . '.filter.search', 'filter_search', '', 'STRING'); $this->setState('filter.search', $get_search); } /** * Build an SQL query to load the list data. * * @return JDatabaseQuery * * @since 1.6 */ protected function getListQuery($params = '', $is_module = false) { // Get Params if (empty($params)) { $app = JFactory::getApplication('site'); $params = $app->getParams('com_monthlyarchive'); } // Get Content type $content_type = $params->get('content_type', 'default'); $date_type = $params->get('show_date_beside_articles_type', 'created'); // created or publish_up // Get SQL table's name $sql_table_name_content = ($content_type == 'k2' && $this->isActive('com_k2')) ? '`#__k2_items`' : '`#__content`'; $sql_table_name_categories = ($content_type == 'k2' && $this->isActive('com_k2')) ? '`#__k2_categories`' : '`#__categories`'; // Create a new query object. $db = JFactory::getDbo(); $query = $db->getQuery(true); // Select the required fields from the table. if ($content_type == 'k2'): $query ->select( $this->getState( 'list.select', 'DISTINCT c.id, c.title, c.alias, c.introtext, c.fulltext, c.hits, c.catid, c.created, c.publish_up, c.created_by, c.created_by_alias, c.language' ) ); else: // Get only the items you need depends of display type (1. list_of_months_and_years or 2. list_of_articles) $display_type = $params->get('display_type', 'list_of_months_and_years'); $get_year = JFactory::getApplication()->input->get('year', '', 'STRING'); $get_month = JFactory::getApplication()->input->get('month', '', 'INT'); if ($is_module || ($display_type == 'list_of_months_and_years' && $get_year != 'all' && (empty($get_year) && empty($get_month)))) { $query ->select( $this->getState( 'list.select', 'DISTINCT c.id, c.created, c.publish_up, c.publish_down' ) ); } else { $query ->select( $this->getState( 'list.select', 'DISTINCT c.id, c.id AS slug, c.state, c.title, c.alias, c.introtext, c.fulltext, c.images, c.hits, c.catid, c.created, c.publish_up, c.publish_down, c.created_by AS author, c.created_by, c.created_by_alias, c.language' ) ); } endif; // Table $query->from($sql_table_name_content.' AS c'); // Join Categories if ($content_type == 'k2' && $this->isActive('com_k2')): $query->select('categories.name AS category_title'); $query->select('categories.parent AS category_parent_id'); else: $query->select('categories.title AS category_title'); $query->select('categories.parent_id AS category_parent_id'); endif; $query->join('LEFT', $sql_table_name_categories.' AS categories ON categories.id = c.catid'); $query->where('categories.published = 1'); // Join Authors $query->select('users.name AS author_name, users.username AS author_username'); $query->join('LEFT', '#__users AS users ON users.id = c.created_by'); // Join Comments $show_count_comments = $params->get('show_count_comments', '1'); $comments_source = $params->get('comments_source', ''); $comments_source = ($show_count_comments && $content_type == 'k2') ? 'k2' : $comments_source; // jcomments if ($show_count_comments && $comments_source == 'jcomments' && $this->isActive('com_jcomments')) { $query->select('(SELECT COUNT(comments.id) FROM #__jcomments AS comments LEFT JOIN '.$sql_table_name_content.' AS content ON comments.object_id = content.id WHERE comments.published = 1 AND comments.object_id = c.id) AS comments_count'); $query->join('LEFT', '#__jcomments AS comments ON comments.object_id = c.id'); } // k2 comments if ($show_count_comments && $comments_source == 'k2' && $content_type == 'k2' && $this->isActive('com_k2')) { $query->select('(SELECT COUNT(comments.id) FROM #__k2_comments AS comments LEFT JOIN '.$sql_table_name_content.' AS content ON comments.itemID = content.id WHERE comments.published = 1 AND comments.itemID = c.id) AS comments_count'); $query->join('LEFT', '#__k2_comments AS comments ON comments.itemID = c.id'); } // Build Where $query->where($this->buildWhere('c', array(), $params, $is_module)); // Filtering orderby $filter_orderby = $this->state->get("filter.orderby"); if ($filter_orderby && !$is_module): switch ($filter_orderby): case "most_recent_first": $query->order("c.".$date_type." DESC"); break; case "oldest_first": $query->order("c.".$date_type." ASC"); break; case "most_hits": $query->order("c.hits DESC"); break; case "least_hits": $query->order("c.hits ASC"); break; default: $query->order("c.".$date_type." DESC"); endswitch; else: // Articles Ordering by parameter $primary_order = $params->get('primary_order', 'default'); if ($primary_order == "default"): $query->order("c.".$date_type." DESC"); elseif($primary_order == "title_alphabetical"): $query->order("c.title ASC"); elseif($primary_order == "title_reverse_alphabetical"): $query->order("c.title DESC"); elseif($primary_order == "author_alphabetical"): $query->order("users.name ASC"); elseif($primary_order == "author_reverse_alphabetical"): $query->order("users.name DESC"); elseif($primary_order == "oldest_first"): $query->order("c.".$date_type." ASC"); elseif($primary_order == "most_recent_first"): $query->order("c.".$date_type." DESC"); elseif($primary_order == "oldest_first_by_publish_date"): $query->order("c.publish_up ASC"); elseif($primary_order == "most_recent_first_by_publish_date"): $query->order("c.publish_up DESC"); elseif($primary_order == "most_hits"): $query->order("c.hits DESC"); elseif($primary_order == "least_hits"): $query->order("c.hits ASC"); elseif($primary_order == "order_id"): $query->order("c.id ASC"); elseif($primary_order == "order_reverse_id"): $query->order("c.id DESC"); else: $query->order("c.".$date_type." DESC"); // default endif; endif; //$query->setlimit(7500); //echo $query.'<hr>'; //die($query); return $query; } /** * Method to get a list of articles. * * @return mixed An array of objects on success, false on failure. */ public function getItems($params = '', $is_module = false) { // Get Params if (empty($params)) { $app = JFactory::getApplication('site'); $params = $app->getParams('com_monthlyarchive'); } // Get a storage key. $store = $this->getStoreId(); // Try to load the data from internal storage. if (isset($this->cache[$store])) { return $this->cache[$store]; } try { // Load the list items and add the items to the internal cache. if ($is_module) { $this->cache[$store] = $this->_getList($this->getListQuery($params, $is_module)); } else { $this->cache[$store] = $this->_getList($this->getListQuery($params, $is_module), $this->getStart(), $this->getState('list.limit')); } } catch (\RuntimeException $e) { $this->setError($e->getMessage()); return false; } return $this->cache[$store]; } public function buildWhere($tbl_prefix = 'c', $exlude = array(), $params = '', $is_module = false) { // Get Params if (empty($params)) { $app = JFactory::getApplication('site'); $params = $app->getParams('com_monthlyarchive'); } // Get Content type $content_type = $params->get('content_type', 'default'); $show_unpublished = $params->get('show_unpublished', '0'); $featured_articles = $params->get('featured_articles', 'all'); $date_type = $params->get('show_date_beside_articles_type', 'created'); // created or publish_up // Connect to DB $db = $this->getDbo(); $where_arr = array(); // Filtering access if (!in_array('access', $exlude)) { $user = JFactory::getUser(); $where_arr[] = $tbl_prefix.'.access IN ('.implode(',', $user->getAuthorisedViewLevels()).')'; } // Filtering publish date if (!in_array('publish_date', $exlude) && $show_unpublished == 0) { $where_arr[] = '('.$tbl_prefix.'.publish_up = '.$db->Quote($db->getNullDate()).' OR '.$tbl_prefix.'.publish_up <= '.$db->Quote(JFactory::getDate()->toSql()).')'; $where_arr[] = '('.$tbl_prefix.'.publish_down = '.$db->Quote($db->getNullDate()).' OR '.$tbl_prefix.'.publish_down >= '.$db->Quote(JFactory::getDate()->toSql()).')'; } // featured articles if ($featured_articles != 'all'): if ($featured_articles == 'only_featured'): $where_arr[] = $tbl_prefix.'.featured = 1'; elseif ($featured_articles == 'except_featured'): $where_arr[] = $tbl_prefix.'.featured != 1'; endif; endif; // Filtering Date if (!in_array('date', $exlude) && !$is_module) { // Filtering Month and Year from Filter State (select list) $filter_date = $this->state->get("filter.date"); $get_year = JFactory::getApplication()->input->get('year', '', 'INT'); if ((is_numeric($get_year) && strlen($get_year) == 4)) { // Filtering Month and Year from Link $where_arr[] = 'YEAR(CONVERT_TZ(DATE_FORMAT('.$tbl_prefix.'.'.$date_type.', "%Y-%m-%d %H:%i:%s"), "+00:00", @@session.time_zone)) = '.(int) $get_year; // Filtering Month $get_month = JFactory::getApplication()->input->get('month', '', 'INT'); $get_month = (is_numeric($get_month) && $get_month > 0) ? sprintf("%02d", $get_month) : $get_month; if (is_numeric($get_month) && $get_month > 0) { $where_arr[] = 'MONTH(CONVERT_TZ(DATE_FORMAT('.$tbl_prefix.'.'.$date_type.', "%Y-%m-%d %H:%i:%s"), "+00:00", @@session.time_zone)) = '.(int) $get_month; } } elseif ($filter_date) { if (strlen($filter_date) == 4) { $where_arr[] = 'YEAR(CONVERT_TZ(DATE_FORMAT('.$tbl_prefix.'.'.$date_type.', "%Y-%m-%d %H:%i:%s"), "+00:00", @@session.time_zone)) = '.$db->Quote($filter_date); } else if (strlen($filter_date) == 7) { $where_arr[] = 'DATE_FORMAT(CONVERT_TZ(DATE_FORMAT('.$tbl_prefix.'.'.$date_type.', "%Y-%m-%d %H:%i:%s"), "+00:00", @@session.time_zone), "%Y-%m") = '.$db->Quote($filter_date); } } } // Filtering Author $user_id = JFactory::getUser()->get('id'); $show_only_own_articles = $params->get('show_only_own_articles', '0'); if ($show_only_own_articles && $user_id > 0) { $where_arr[] = $tbl_prefix.'.created_by = '.(int) $user_id; } else { if (!in_array('author', $exlude) && !$is_module) { $filter_author = $this->state->get("filter.author"); if ($filter_author) { $where_arr[] = $tbl_prefix.'.created_by = '.(int) $filter_author; } } } // Filtering category if (!in_array('category', $exlude) && !$is_module) { $filter_category = $this->state->get("filter.category"); if ($filter_category) { $where_arr[] = $tbl_prefix.'.catid = '.(int) $filter_category; } } // Filtering language if (!in_array('language', $exlude)) { $filter_language = $params->get('ma_language', '*'); if (!empty($filter_language) && $filter_language != '*') { $where_arr[] = $tbl_prefix.'.language = '.$db->Quote($filter_language); } } // Filter by search in title if (!in_array('search', $exlude) && !$is_module) { $search = $this->getState('filter.search'); if (!empty($search)) { if (stripos($search, 'id:') === 0) { $where_arr[] = $tbl_prefix.'.id = ' . (int) substr($search, 3); } else { // get search $search = $db->Quote('%' . $db->escape($search, true) . '%'); // search in jcomments $show_count_comments = $params->get('show_count_comments', '1'); $comments_source = $params->get('comments_source', ''); if ($show_count_comments && $comments_source == 'jcomments' && $this->isActive('com_jcomments')) { $search_in_jcomments = ' OR comments.name LIKE ' . $search .' OR comments.username LIKE ' . $search .' OR comments.email LIKE ' . $search .' OR comments.comment LIKE ' . $search . ''; } else { $search_in_jcomments = ''; } // build where $where_arr[] = '('.$tbl_prefix.'.title LIKE ' . $search . ' OR '.$tbl_prefix.'.introtext LIKE ' . $search . ' OR '.$tbl_prefix.'.fulltext LIKE ' . $search .''.$search_in_jcomments.')'; } } } // State if (!in_array('state', $exlude)) { $where_arr[] = $this->buildWhereState($tbl_prefix); } // Include Content $include_years = $params->get('include_years', ''); $include_months = $params->get('include_months', ''); $include_months_years = $params->get('include_months_years', ''); $include_category_type = $params->get('include_category_type', 'parent_cats_only'); $include_categories_joomla = $params->get('include_categories_joomla', ''); $include_categories_k2 = $params->get('include_categories_k2', ''); $include_authors = $params->get('include_authors', ''); $include_articles = $params->get('include_articles', ''); if ($include_years) { $where_arr[] = 'YEAR(CONVERT_TZ(DATE_FORMAT('.$tbl_prefix.'.'.$date_type.', "%Y-%m-%d %H:%i:%s"), "+00:00", @@session.time_zone)) IN ('.implode(',', $db->Quote($include_years)).')'; } if ($include_months) { $where_arr[] = 'MONTH(CONVERT_TZ(DATE_FORMAT('.$tbl_prefix.'.'.$date_type.', "%Y-%m-%d %H:%i:%s"), "+00:00", @@session.time_zone)) IN ('.implode(',', $db->Quote($include_months)).')'; } if ($include_months_years) { $where_arr[] = 'DATE_FORMAT(CONVERT_TZ(DATE_FORMAT('.$tbl_prefix.'.'.$date_type.', "%Y-%m-%d %H:%i:%s"), "+00:00", @@session.time_zone), "%Y-%m") IN ('.implode(',', $db->Quote($include_months_years)).')'; } if ($content_type == 'k2' && !empty($include_categories_k2) && $this->isActive('com_k2')) { $where_cat = ''; $where_cat .= '('; $where_cat .= $tbl_prefix.'.catid IN ('.implode(',', $include_categories_k2).')'; if ($include_category_type == 'parent_cats_only') { $where_cat .= ' OR (SELECT cats.parent_id FROM #__categories AS cats WHERE '.$tbl_prefix.'.catid = cats.id) IN ('.implode(',', $include_categories_k2).')'; } $where_cat .= ')'; $where_arr[] = $where_cat; } else if ($content_type == 'default' && !empty($include_categories_joomla)) { $where_cat = ''; $where_cat .= '('; $where_cat .= $tbl_prefix.'.catid IN ('.implode(',', $include_categories_joomla).')'; if ($include_category_type == 'parent_cats_only') { $where_cat .= ' OR (SELECT cats.parent_id FROM #__categories AS cats WHERE '.$tbl_prefix.'.catid = cats.id) IN ('.implode(',', $include_categories_joomla).')'; } $where_cat .= ')'; $where_arr[] = $where_cat; } if ($include_authors) { $where_arr[] = ''.$tbl_prefix.'.created_by IN ('.implode(',', $include_authors).')'; } if ($include_articles) { $include_articles = array_map('intval', explode(',', $include_articles)); $where_arr[] = ''.$tbl_prefix.'.id IN ('.implode(',', $include_articles).')'; } // Exclude Content $exclude_years = $params->get('exclude_years', ''); $exclude_months = $params->get('exclude_months', ''); $exclude_months_years = $params->get('exclude_months_years', ''); $exclude_category_type = $params->get('exclude_category_type', 'parent_cats_only'); $exclude_categories_joomla = $params->get('exclude_categories_joomla', ''); $exclude_categories_k2 = $params->get('exclude_categories_k2', ''); $exclude_categories = ($content_type == 'k2') ? $exclude_categories_k2 : $exclude_categories_joomla; $exclude_authors = $params->get('exclude_authors', ''); $exclude_articles = $params->get('exclude_articles', ''); if ($exclude_years) { $where_arr[] = 'YEAR(CONVERT_TZ(DATE_FORMAT('.$tbl_prefix.'.'.$date_type.', "%Y-%m-%d %H:%i:%s"), "+00:00", @@session.time_zone)) NOT IN ('.implode(',', $db->Quote($exclude_years)).')'; } if ($exclude_months) { $where_arr[] = 'MONTH(CONVERT_TZ(DATE_FORMAT('.$tbl_prefix.'.'.$date_type.', "%Y-%m-%d %H:%i:%s"), "+00:00", @@session.time_zone)) NOT IN ('.implode(',', $db->Quote($exclude_months)).')'; } if ($exclude_months_years) { $where_arr[] = 'DATE_FORMAT(CONVERT_TZ(DATE_FORMAT('.$tbl_prefix.'.'.$date_type.', "%Y-%m-%d %H:%i:%s"), "+00:00", @@session.time_zone), "%Y-%m") NOT IN ('.implode(',', $db->Quote($exclude_months_years)).')'; } if ($content_type == 'k2' && !empty($exclude_categories_k2) && $this->isActive('com_k2')) { $where_cat = ''; $where_cat .= '('; $where_cat .= $tbl_prefix.'.catid NOT IN ('.implode(',', $exclude_categories_k2).')'; if ($exclude_category_type == 'parent_cats_only') { $where_cat .= ' AND (SELECT cats.parent_id FROM #__categories AS cats WHERE '.$tbl_prefix.'.catid = cats.id) NOT IN ('.implode(',', $exclude_categories_k2).')'; } $where_cat .= ')'; $where_arr[] = $where_cat; } else if ($content_type == 'default' && !empty($exclude_categories_joomla)) { $where_cat = ''; $where_cat .= '('; $where_cat .= $tbl_prefix.'.catid NOT IN ('.implode(',', $exclude_categories_joomla).')'; if ($exclude_category_type == 'parent_cats_only') { $where_cat .= ' AND (SELECT cats.parent_id FROM #__categories AS cats WHERE '.$tbl_prefix.'.catid = cats.id) NOT IN ('.implode(',', $exclude_categories_joomla).')'; } $where_cat .= ')'; $where_arr[] = $where_cat; } if ($exclude_authors) { $where_arr[] = ''.$tbl_prefix.'.created_by NOT IN ('.implode(',', $exclude_authors).')'; } if ($exclude_articles) { $exclude_articles = array_map('intval', explode(',', $exclude_articles)); $where_arr[] = ''.$tbl_prefix.'.id NOT IN ('.implode(',', $exclude_articles).')'; } // Separate where elements if (!empty($where_arr)): $where = implode(' AND ', $where_arr); else: $where = $tbl_prefix.'.'.($content_type == 'k2' && $this->isActive('com_k2') ? 'published' : 'state').' = 1'; endif; return $where; } public function buildWhereState($tbl_prefix = 'c', $exlude = array()) { // BEGIN: Get Params $app = JFactory::getApplication('site'); $params = $app->getParams('com_monthlyarchive'); // Get Content type $content_type = $params->get('content_type', 'default'); // Get state parameters $show_published = $params->get('show_published', '1'); $show_unpublished = $params->get('show_unpublished', '0'); $show_archived = $params->get('show_archived', '0'); $show_trashed = $params->get('show_trashed', '0'); // State $where_arr = array(); $where_state_arr = array(); if ($content_type == 'k2' && $this->isActive('com_k2') && ($show_published || $show_unpublished || $show_trashed)): if ($show_published): $where_state_arr[] = $tbl_prefix.'.published = 1'; endif; if ($show_unpublished): $where_state_arr[] = $tbl_prefix.'.published = 0'; endif; if ($show_trashed): $where_state_arr[] = $tbl_prefix.'.trash = 1'; endif; elseif ($content_type == 'default' && ($show_published || $show_unpublished || $show_archived || $show_trashed)): if ($show_published): $where_state_arr[] = $tbl_prefix.'.state = 1'; endif; if ($show_unpublished): $where_state_arr[] = $tbl_prefix.'.state = 0'; endif; if ($show_archived): $where_state_arr[] = $tbl_prefix.'.state = 2'; endif; if ($show_trashed): $where_state_arr[] = $tbl_prefix.'.state = -2'; endif; endif; if (!empty($where_state_arr)): $where = '('.implode(' OR ', $where_state_arr).')'; else: $where = $tbl_prefix.'.'.($content_type == 'k2' && $this->isActive('com_k2') ? 'published' : 'state').' = 1'; endif; return $where; } public function getAuthors($params = '') { // Get Params if (empty($params)) { $app = JFactory::getApplication('site'); $params = $app->getParams('com_monthlyarchive'); } // Get Content type $content_type = $params->get('content_type', 'default'); // Get SQL table's name $sql_table_name_content = ($content_type == 'k2' && $this->isActive('com_k2')) ? '`#__k2_items`' : '`#__content`'; // query $db = JFactory::getDbo(); $query = $db->getQuery(true); $query->select('u.id AS author_id, CONCAT(u.name, " (", COUNT(c.`id`), ")") AS author_name'); $query->from('#__users AS u'); $query->join('LEFT', $sql_table_name_content.' AS c ON u.id = c.created_by'); $query->where($this->buildWhere('c', array('author'), $params)); $query->group('u.id'); $query->order('u.name ASC'); $db->setQuery($query); return $db->loadObjectlist(); } public function getCategories($params = '') { // Get Params if (empty($params)) { $app = JFactory::getApplication('site'); $params = $app->getParams('com_monthlyarchive'); } // Get Content type $content_type = $params->get('content_type', 'default'); // Get SQL table's name $sql_table_name_content = ($content_type == 'k2' && $this->isActive('com_k2')) ? '`#__k2_items`' : '`#__content`'; $sql_table_name_categories = ($content_type == 'k2' && $this->isActive('com_k2')) ? '`#__k2_categories`' : '`#__categories`'; // query $db = JFactory::getDbo(); $query = $db->getQuery(true); if ($content_type == 'k2' && $this->isActive('com_k2')): $query->select('cat.id AS category_id, cat.name AS category_name, COUNT(c.`id`) AS numitems'); else: $query->select('cat.id AS category_id, CONCAT(IF(cat.level=2, \' -- \', \'\'), IF(cat.level=3, \' -- \', \'\'), cat.title, " (", COUNT(c.`id`), ")") AS category_name'); endif; $query->from($sql_table_name_categories.' AS cat'); $query->join('LEFT', $sql_table_name_content.' AS c ON cat.id = c.catid'); $author = $this->getState('filter.author'); $date = $this->getState('filter.date'); $search = $this->getState('filter.search'); if(!empty($author) || !empty($category) || !empty($search)) { $query->where($this->buildWhere('c', array('category'), $params)); } else { $query->where($this->buildWhere('c', array('author', 'category', 'search'), $params)); } $query->group('cat.id'); if ($content_type == 'k2' && $this->isActive('com_k2')): $query->order('cat.name ASC'); else: $query->order('cat.lft ASC'); endif; $db->setQuery($query); return $db->loadObjectlist(); } // Check if the component is installed and is enabled public function isActive($option) // e.g. $option = com_k2 { jimport('joomla.component.helper'); if(!JComponentHelper::isInstalled($option) || !JComponentHelper::isEnabled($option)) { return false; } else { return true; } } }