<?php
/**
* @package RSFeedback!
* @copyright (C) 2010-2018 www.rsjoomla.com
* @license GPL, http://www.gnu.org/copyleft/gpl.html
*/

defined('_JEXEC') or die('Restricted access');

use Joomla\CMS\MVC\Model\ListModel;
use Joomla\CMS\Factory;

class RSFeedbackModelFeedbacks extends ListModel
{
	public function __construct($config = array()) {
		if (empty($config['filter_fields'])) {
			$config['filter_fields'] = array(
				'f.id', 'f.title', 'c.name', 's.name', 'f.created',  
				'nrvotes', 'f.published', 'f.hits', 'published', 'category', 'status'
			);
		}
		parent::__construct($config);
	}

	protected function getListQuery() {
		$db			= Factory::getDbo();
		$query		= $db->getQuery(true);
		$columns	= array();

	    $columns[] 	= $db->qn('f.id');
		$columns[] 	= $db->qn('f.title');
		$columns[] 	= $db->qn('f.cat_id');
		$columns[] 	= $db->qn('f.status_id');
		$columns[] 	= $db->qn('f.user_id');
		$columns[] 	= $db->qn('f.name','user_f_name');
		$columns[] 	= $db->qn('f.created');
		$columns[] 	= $db->qn('f.hits');
		$columns[] 	= $db->qn('f.published');
		$columns[] 	= $db->qn('u.username');
		$columns[] 	= $db->qn('u.name','user_name');
		$columns[] 	= $db->qn('s.name','status_name');
		$columns[] 	= $db->qn('c.name','category_name');
		$columns[] 	= "COALESCE(v.flag_count, 0) as flag_count";
		$columns[] 	= "COALESCE(SUM(uv.number_points),0) as nrvotes";

		$query->select($columns)
			  ->from($db->qn('#__rsfeedback_feedbacks').' AS f')
			  ->join('LEFT', $db->qn('#__rsfeedback_categories').' c ON f.cat_id = c.id')
			  ->join('LEFT', $db->qn('#__rsfeedback_user_votes').' uv ON f.id = uv.feedback_id')
			  ->join('LEFT', "(SELECT uf.feedback_id, COUNT(uf.id) as flag_count FROM ".$db->qn('#__rsfeedback_user_flags')." as uf GROUP BY uf.feedback_id) as v ON v.feedback_id = f.id ")
			  ->join('LEFT', $db->qn('#__rsfeedback_statuses')."   s ON f.status_id = s.id ")
			  ->join('LEFT', $db->qn('#__users')." u ON f.user_id = u.ID");

		if ($category = $this->getState('filter.category'))
			$query->where("c.id = ".$db->q($category));

		if ($status	= $this->getState('filter.status'))
			$query->where("s.id = ".$db->q($status));

		$state = $this->getState('filter.state');
		if (is_numeric($state)) 
			$query->where("f.published = ".$db->q($state));

		if ($search = $this->getState('filter.search'))
			$query->where("(f.title LIKE ".$db->q("%".$search."%")." OR u.username LIKE ".$db->q("%".$search."%")." OR f.text LIKE ".$db->q("%".$search."%").")");

		$query->group('f.id');

		$listOrdering  	= $this->getState('list.ordering', 'f.created');
		$listDirection 	= $this->getState('list.direction', 'DESC');

		$query->order($db->qn($listOrdering).' '.$listDirection);

		return $query;
	}
	
	protected function populateState($ordering = null, $direction = null) {
		parent::populateState('f.created', 'desc');
    }
}