<?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, \'&nbsp;-- \', \'\'), IF(cat.level=3, \'&nbsp;&nbsp;&nbsp;&nbsp;-- \', \'\'), 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;
		}
	}
}