<commit_before><?php
/**
 * HUBzero CMS
 *
 * Copyright 2005-2015 HUBzero Foundation, LLC.
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 *
 * HUBzero is a registered trademark of Purdue University.
 *
 * @package   hubzero-cms
 * @author    <NAME> <<EMAIL>>
 * @copyright Copyright 2005-2015 HUBzero Foundation, LLC.
 * @license   http://opensource.org/licenses/MIT MIT
 */

namespace Components\Support\Site\Controllers;

use Components\Support\Helpers\ACL;
use Components\Support\Helpers\Utilities;
use Components\Support\Models\Ticket;
use Components\Support\Models\Comment;
use Components\Support\Models\Tags;
use Components\Support\Models\Attachment;
use Components\Support\Models\QueryFolder;
use Components\Support\Models\Watching;
use Components\Support\Models\Category;
use Components\Support\Models\Message;
use Hubzero\Component\SiteController;
use Hubzero\Browser\Detector;
use Hubzero\Content\Server;
use Hubzero\Utility\Validate;
use Filesystem;
use Exception;
use Request;
use Pathway;
use Config;
use Event;
use Route;
use Lang;
use User;
use Date;
use App;

include_once dirname(dirname(__DIR__)) . DS . 'models' . DS . 'ticket.php';

/**
 * Manage support tickets
 */
class Tickets extends SiteController
{
	/**
	 * Determine task and execute it
	 *
	 * @return  void
	 */
	public function execute()
	{
		$this->acl = ACL::getACL();

		parent::execute();
	}

	/**
	 * Method to set the document path
	 *
	 * @param   object  $ticket
	 * @return  void
	 */
	protected function _buildPathway($ticket=null)
	{
		if (Pathway::count() <= 0)
		{
			Pathway::append(
				Lang::txt('COM_SUPPORT'),
				'index.php?option=' . $this->_option . '&controller=index'
			);
		}
		if (Pathway::count() == 1  && $this->_task)
		{
			$task = $this->_task;
			if (in_array($this->_task, array('ticket', 'new', 'display', 'save')))
			{
				$task = 'tickets';
			}
			if ($task == 'update')
			{
				$task = 'ticket';
			}
			Pathway::append(
				Lang::txt('COM_SUPPORT_' . strtoupper($task)),
				'index.php?option=' . $this->_option . '&controller=' . $this->_controller . '&task=' . $task
			);
			if ($this->_task == 'new')
			{
				Pathway::append(
					Lang::txt('COM_SUPPORT_' . strtoupper($this->_task)),
					'index.php?option=' . $this->_option . '&controller=' . $this->_controller . '&task=' . $this->_task
				);
			}
		}
		if (is_object($ticket) && $ticket->get('id'))
		{
			Pathway::append(
				'#' . $ticket->get('id'),
				$ticket->link()
			);
		}
	}

	/**
	 * Method to build and set the document title
	 *
	 * @param   object  $ticket
	 * @return  void
	 */
	protected function _buildTitle($ticket=null)
	{
		$this->_title = Lang::txt(strtoupper($this->_option));
		if ($this->_task)
		{
			if ($this->_task == 'new' || $this->_task == 'display')
			{
				$this->_title .= ': ' . Lang::txt('COM_SUPPORT_TICKETS');
			}
			if ($this->_task != 'display')
			{
				if ($this->_task == 'update')
				{
					$this->_title .= ': ' . Lang::txt('COM_SUPPORT_TiCKET');
				}
				else
				{
					$this->_title .= ': ' . Lang::txt('COM_SUPPORT_' . strtoupper($this->_task));
				}
			}
		}
		if (is_object($ticket) && $ticket->get('id'))
		{
			$this->_title .= ' #' . $ticket->get('id');
		}

		App::get('document')->setTitle($this->_title);
	}

	/**
	 * Displays a list of tickets
	 *
	 * @return	void
	 */
	public function statsTask()
	{
		// Check authorization
		if (User::isGuest())
		{
			$return = base64_encode(Request::getVar('REQUEST_URI', Route::url('index.php?option=' . $this->_option . '&controller=' . $this->_controller . '&task=' . $this->_task, false, true), 'server'));
			App::redirect(
				Route::url('index.php?option=com_users&view=login&return=' . $return, false)
			);
			return;
		}

		if (!$this->acl->check('read', 'tickets'))
		{
			$this->_return = Route::url('index.php?option=' . $this->_option . '&controller=' . $this->_controller . '&task=tickets');
		}

		// Set the page title
		$this->_buildTitle();

		$this->view->title = $this->_title;

		// Set the pathway
		$this->_buildPathway();

		$type = Request::getWord('type', 'submitted');
		$this->view->type = ($type == 'automatic') ? 1 : 0;

		$this->view->group = preg_replace('/[^0-9a-zA-Z_\-]/', '', Request::getVar('group', '_none_'));

		// Set up some dates
		$this->offset = Config::get('offset');

		$year  = Request::getInt('year', strftime("%Y", time()+($this->offset*60*60)));
		$month = strftime("%m", time()+($this->offset*60*60));

		$this->view->year = $year;
		$this->view->opened = array();
		$this->view->closed = array();

		$sql = "SELECT DISTINCT(g.`cn`), g.description
				FROM `#__support_tickets` AS s
				LEFT JOIN `#__xgroups` AS g ON g.gidNumber=s.`group_id`
				WHERE s.`group_id` > 0
				AND s.type=" . $this->database->quote($this->view->type) . "
				ORDER BY g.description ASC";
		$this->database->setQuery($sql);
		$this->view->groups = $this->database->loadObjectList();

		// Users
		$this->view->users = null;

		if ($this->view->group == '_none_')
		{
			$query = "SELECT DISTINCT a.username, a.name, a.id
					FROM `#__users` AS a
					INNER JOIN `#__support_tickets` AS s ON s.owner = a.id
					WHERE a.block = '0' AND s.type=" . $this->database->quote($this->view->type) . " AND s.group_id=0
					ORDER BY a.name";
		}
		else if ($this->view->group)
		{
			$query = "SELECT a.username, a.name, a.id
					FROM `#__users` AS a
					INNER JOIN `#__xgroups_members` AS gm ON gm.uidNumber=a.id
					INNER JOIN `#__xgroups` AS g ON g.gidNumber=gm.gidNumber
					WHERE g.cn=" . $this->database->quote($this->view->group) ."
					ORDER BY a.name";
		}
		else
		{
			$query = "SELECT DISTINCT a.username, a.name, a.id
					FROM `#__users` AS a
					INNER JOIN `#__support_tickets` AS s ON s.owner = a.id
					WHERE a.block = '0' AND s.type=" . $this->database->quote($this->view->type) . "
					ORDER BY a.name";
		}

		$this->database->setQuery($query);
		$usrs = $this->database->loadObjectList();

		$users = array();
		if ($usrs)
		{
			foreach ($usrs as $j => $user)
			{
				if (!isset($user->total))
				{
					$user->total = 0;
				}
				if (!isset($user->tickets))
				{
					$user->tickets = array();
				}
				if (!isset($user->closed))
				{
					$user->closed = array();
				}
				$users[$user->id] = $user;
			}
		}

		// Get avgerage lifetime
		//$this->view->lifetime = $st->getAverageLifeOfTicket($this->view->type, $year, $this->view->group);

		// First ticket
		$sql = "SELECT YEAR(created)
				FROM `#__support_tickets`
				WHERE report!=''
				AND type='{$this->view->type}' ORDER BY created ASC LIMIT 1";
		$this->database->setQuery($sql);
		$first = intval($this->database->loadResult());

		$startyear  = $first;
		$startmonth = 1;

		$this->view->start = Request::getVar('start', $first . '-01');
		if ($this->view->start != $first . '-01')
		{
			if (!preg_match("/^([0-9]{4})-([0-9]{2})$/", $this->view->start))
			{
				$this->view->start = $first . '-01';
			}

			if (preg_match("/([0-9]{4})-([0-9]{2})/", $this->view->start, $regs))
			{
				$startmonth = date("m", mktime(0, 0, 0, $regs[2], 1, $regs[1]));
				$startyear = $first = date("Y", mktime(0, 0, 0, $regs[2], 1, $regs[1]));
				//$end = $year . '-' . $month;
			}
		}

		$this->view->end   = Request::getVar('end', '');

		$endmonth = $month;
		$endyear = date("Y");
		$endyear++;

		$end = '';
		if ($this->view->end)
		{
			if (!preg_match("/^([0-9]{4})-([0-9]{2})$/", $this->view->end))
			{
				$this->view->end = '';
			}

			// We need to get the NEXT month. This is so that for a time range
			// of 2013-01 to 2013-12 will display data for all of 2013-12.
			// So, the actual time range is 2013-01-01 00:00:00 to 2014-01-01 00:00:00
			if (preg_match("/([0-9]{4})-([0-9]{2})/", $this->view->end, $regs))
			{
				$endmonth = intval($regs[2]);
				$endyear  = intval($regs[1]);
				$endyear++;

				$month = date("m", mktime(0, 0, 0, ($endmonth+1), 1, $regs[1]));
				$year  = date("Y", mktime(0, 0, 0, ($endmonth+1), 1, $regs[1]));
				$end   = $year . '-' . $month;
			}
		}
		else
		{
			$this->view->end = $year . '-' . $month;
		}

		// Opened tickets
		$sql = "SELECT id, created, YEAR(created) AS `year`, MONTH(created) AS `month`, open, status, owner
				FROM `#__support_tickets`
				WHERE report!=''
				AND type=" . $this->view->type; // . " AND open=1";
		if ($this->view->group == '_none_')
		{
			$sql .= " AND `group_id`=0";
		}
		else if ($this->view->group)
		{
			$gidNumber = 0;
			if ($group = \Hubzero\User\Group::getInstance($this->view->group))
			{
				$gidNumber = $group->get('gidNumber');
			}
			$sql .= " AND `group_id`=" . $this->database->quote($gidNumber);
		}
		if ($this->view->start && $end)
		{
			$sql .= " AND created >= " . $this->database->quote($this->view->start . '-01 00:00:00') . " AND created < " . $this->database->quote($end . '-01 00:00:00');
		}
		$sql .= " ORDER BY created ASC";
		$this->database->setQuery($sql);
		$openTickets = $this->database->loadObjectList();

		$owners = array();

		$open = array();
		$this->view->opened['open'] = 0;
		$this->view->opened['new'] = 0;
		$this->view->opened['unassigned'] = 0;
		foreach ($openTickets as $o)
		{
			if (!isset($open[$o->year]))
			{
				$open[$o->year] = array();
			}
			if (!isset($open[$o->year][$o->month]))
			{
				$open[$o->year][$o->month] = 0;
			}
			$open[$o->year][$o->month]++;

			$this->view->opened['open']++;

			if ($o->open)
			{
				if (!$o->status)
				{
					$this->view->opened['new']++;
				}
				if (!$o->owner)
				{
					$this->view->opened['unassigned']++;
				}
				else
				{
					if (!isset($owners[$o->owner]))
					{
						$owners[$o->owner] = 0;
					}
					$owners[$o->owner]++;
				}
			}
		}

		// Closed tickets
		$sql = "SELECT t.id AS ticket, t.owner AS created_by, t.closed AS created, YEAR(t.closed) AS `year`, MONTH(t.closed) AS `month`, UNIX_TIMESTAMP(t.created) AS opened, UNIX_TIMESTAMP(t.closed) AS closed
				FROM `#__support_tickets` AS t
				WHERE t.report!=''
				AND t.type=" . $this->database->quote($this->view->type) . " AND t.open=0";
		if ($this->view->group == '_none_')
		{
			$sql .= " AND t.`group_id`=0";
		}
		else if ($this->view->group)
		{
			$gidNumber = 0;
			if ($group = \Hubzero\User\Group::getInstance($this->view->group))
			{
				$gidNumber = $group->get('gidNumber');
			}
			$sql .= " AND t.`group_id`=" . $this->database->quote($gidNumber);
		}
		if ($this->view->start && $end)
		{
			$sql .= " AND t.closed >= " . $this->database->quote($this->view->start . '-01 00:00:00') . " AND t.closed < " . $this->database->quote($end . '-01 00:00:00');
		}
		$sql .= " ORDER BY t.closed ASC";

		$this->database->setQuery($sql);
		$clsd = $this->database->loadObjectList();

		$this->view->opened['closed'] = 0;
		// First we need to loop through all the entries and remove some potential duplicates
		$closedTickets = array();
		foreach ($clsd as $closed)
		{
			if (!isset($closedTickets[$closed->ticket]))
			{
				$closedTickets[$closed->ticket] = $closed;
			}
			else
			{
				if ($closedTickets[$closed->ticket]->created < $closed->created)
				{
					$closedTickets[$closed->ticket] = $closed;
				}
			}
		}
		$this->view->closedTickets = $closedTickets;
		// Loop through info and divide into years/months
		$closed = array();
		foreach ($closedTickets as $o)
		{
			if (!isset($closed[$o->year]))
			{
				$closed[$o->year] = array();
			}
			if (!isset($closed[$o->year][$o->month]))
			{
				$closed[$o->year][$o->month] = 0;
			}
			$closed[$o->year][$o->month]++;
			$this->view->opened['closed']++;
		}

		// Group data by year and gather some info for each user

		$this->view->closedmonths = array();
		$this->view->openedmonths = array();

		for ($k = $startyear, $n = $endyear; $k < $n; $k++)
		{
			$this->view->closedmonths[$k] = array();
			$this->view->openedmonths[$k] = array();
			if ($k == $startyear && intval($startmonth) > 1)
			{
				$i = intval($startmonth) - 1;
			}
			else
			{
				$i = 1;
			}

			for ($i; $i <= 12; $i++)
			{
				if ($k == $year && $i > $endmonth)
				{
					break;
				}
				else
				{
					$this->view->closedmonths[$k][$i] = (isset($closed[$k]) && isset($closed[$k][$i])) ? $closed[$k][$i] : 0;
					$this->view->openedmonths[$k][$i] = (isset($open[$k]) && isset($open[$k][$i]))     ? $open[$k][$i]   : 0;
				}

				foreach ($users as $j => $user)
				{
					if (!isset($user->closed[$k]))
					{
						$user->closed[$k] = array();
					}

					/*if ($i <= "9"&preg_match("#(^[1-9]{1})#",$i))
					{
						$month = "0$i";
					}*/
					if ($k == $year && $i > $month)
					{
						$user->closed[$k][$i] = 'null';
					}
					else
					{
						$user->closed[$k][$i] = 0;
					}

					$users[$j] = $user;
				}
			}
		}

		foreach ($closedTickets as $c)
		{
			if (isset($users[$c->created_by]))
			{
				$y = intval($c->year);
				$m = intval($c->month);

				if (!$y && !$m)
				{
					continue;
				}

				if (!isset($users[$c->created_by]->closed[$y]))
				{
					$users[$c->created_by]->closed[$y] = array();
				}
				if (!isset($users[$c->created_by]->closed[$y][$m]))
				{
					$users[$c->created_by]->closed[$y][$m] = 0;
				}
				$users[$c->created_by]->closed[$y][$m]++;
				$users[$c->created_by]->total++;
				$users[$c->created_by]->tickets[] = $c;
			}
		}

		// Sort users by number of tickets closed
		$u = array();
		foreach ($users as $k => $user)
		{
			$user->assigned = 0;
			if (isset($owners[$user->id]))
			{
				$user->assigned = $owners[$user->id];
			}
			$key = (string) $user->total;
			if (isset($u[$key]))
			{
				$key .= '.' . $k;
			}
			$u[$key] = $user;
		}
		krsort($u);
		$this->view->users = $u;

		$this->view
			->set('config', $this->config)
			->set('first', $first)
			->set('month', $month)
			->setErrors($this->getErrors())
			->display();
	}

	/**
	 * Displays a list of support tickets
	 *
	 * @return  void
	 */
	public function displayTask()
	{
		if (User::isGuest())
		{
			$return = base64_encode(Request::getVar('REQUEST_URI', Route::url('index.php?option=' . $this->_option . '&controller=' . $this->_controller . '&task=' . $this->_task, false, true), 'server'));
			App::redirect(
				Route::url('index.php?option=com_users&view=login&return=' . $return, false)
			);
			return;
		}

		$filters = $this->_getFilters();

		// Paging
		$filters['limit'] = Request::getState(
			$this->_option . '.' . $this->_controller . '.limit',
			'limit',
			Config::get('list_limit'),
			'int'
		);
		$filters['start'] = Request::getState(
			$this->_option . '.' . $this->_controller . '.limitstart',
			'limitstart',
			0,
			'int'
		);
		// Query to filter by
		$filters['show'] = Request::getState(
			$this->_option . '.' . $this->_controller . '.show',
			'show',
			0,
			'int'
		);
		// Search
		$search       = urldecode(Request::getState(
			$this->_option . '.' . $this->_controller . '.search',
			'search',
			''
		));

		// Get query list
		if (!$this->acl->check('read', 'tickets'))
		{
			$folders = QueryFolder::all()
				->whereEquals('user_id', 0)
				->whereEquals('iscore', 2)
				->order('ordering', 'asc')
				->rows();
		}
		else
		{
			$folders = QueryFolder::all()
				->whereEquals('user_id', User::get('id'))
				->order('ordering', 'asc')
				->rows();

			// Does the user have any folders?
			if (!count($folders))
			{
				// Get all the default folders
				$folders = QueryFolder::cloneCore(User::get('id'));
			}
		}

		$filters['search']  = '';
		$filters['sort']    = 'id';
		$filters['sortdir'] = 'DESC';

		foreach ($folders as $folder)
		{
			foreach ($folder->queries->sort('ordering') as $query)
			{
				if ($query->get('id') != $filters['show'])
				{
					$filters['search'] = '';
				}

				$query->set('count', Ticket::countWithQuery($query, $filters));

				if ($query->get('id') == $filters['show'])
				{
					if ($search)
					{
						$filters['search'] = $search;
					}

					$total = ($search) ? Ticket::countWithQuery($query, $filters) : $query->get('count');

					// Incoming sort
					$filters['sort']         = trim(Request::getState(
						$this->_option . '.' . $this->_controller . '.sort',
						'sort',
						$query->get('sort')
					));

					$filters['sortdir']     = trim(Request::getState(
						$this->_option . '.' . $this->_controller . '.sortdir',
						'sortdir',
						$query->get('sort_dir')
					));

					// Get the records
					$tickets = Ticket::allWithQuery($query, $filters);
				}
			}
		}

		$filters['search'] = $search;

		if (!$filters['show'])
		{
			// Jump back to the beginning of the folders list
			// and try to find the first query available
			// to make it the current "active" query
			foreach ($folders as $folder)
			{
				if (count($folder->queries) > 0)
				{
					$query = $folder->queries->first();
					$filters['show'] = $query->get('id');
					break;
				}
				else
				{	// for no custom queries.
					$query = Query::blank();
					$query->set('count', 0);
				}
			}

			// Set the total for the pagination
			$total = ($search) ? Ticket::countWithQuery($query, $filters) : $query->get('count');

			// Incoming sort
			$filters['sort']   = trim(Request::getState(
				$this->_option . '.' . $this->_controller . '.sort',
				'filter_order',
				$query->sort
			));
			$filters['sortdir'] = trim(Request::getState(
				$this->_option . '.' . $this->_controller . '.sortdir',
				'filter_order_Dir',
				$query->sort_dir
			));

			// Get the records
			$tickets = Ticket::allWithQuery($query, $filters);
		}

		$watching = Watching::all()
			->whereEquals('user_id', User::get('id'))
			->rows()
			->fieldsByKey('ticket_id');

		$watch = array(
			'open'   => Ticket::all()->whereEquals('open', 1)->whereIn('id', $watching)->total(),
			'closed' => Ticket::all()->whereEquals('open', 0)->whereIn('id', $watching)->total()
		);

		if ($filters['show'] < 0)
		{
			$total = $watch[($filters['show'] == -1 ? 'open' : 'closed')];

			$tickets = Ticket::all()
				->whereEquals('open', ($filters['show'] == -1 ? 1 : 0))
				->whereIn('id', $watching)
				->order('created', 'desc')
				->start($filters['start'])
				->limit($filters['limit'])
				->rows();
		}

		// Set the page title
		$this->_buildTitle();

		// Set the pathway
		$this->_buildPathway();<commit_msg>[fix] Don't crash for bad/missing query<commit_after><?php
/**
 * HUBzero CMS
 *
 * Copyright 2005-2015 HUBzero Foundation, LLC.
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 *
 * HUBzero is a registered trademark of Purdue University.
 *
 * @package   hubzero-cms
 * @author    <NAME> <<EMAIL>>
 * @copyright Copyright 2005-2015 HUBzero Foundation, LLC.
 * @license   http://opensource.org/licenses/MIT MIT
 */

namespace Components\Support\Site\Controllers;

use Components\Support\Helpers\ACL;
use Components\Support\Helpers\Utilities;
use Components\Support\Models\Ticket;
use Components\Support\Models\Comment;
use Components\Support\Models\Tags;
use Components\Support\Models\Attachment;
use Components\Support\Models\QueryFolder;
use Components\Support\Models\Watching;
use Components\Support\Models\Category;
use Components\Support\Models\Message;
use Hubzero\Component\SiteController;
use Hubzero\Browser\Detector;
use Hubzero\Content\Server;
use Hubzero\Utility\Validate;
use Filesystem;
use Exception;
use Request;
use Pathway;
use Config;
use Event;
use Route;
use Lang;
use User;
use Date;
use App;

include_once dirname(dirname(__DIR__)) . DS . 'models' . DS . 'ticket.php';

/**
 * Manage support tickets
 */
class Tickets extends SiteController
{
	/**
	 * Determine task and execute it
	 *
	 * @return  void
	 */
	public function execute()
	{
		$this->acl = ACL::getACL();

		parent::execute();
	}

	/**
	 * Method to set the document path
	 *
	 * @param   object  $ticket
	 * @return  void
	 */
	protected function _buildPathway($ticket=null)
	{
		if (Pathway::count() <= 0)
		{
			Pathway::append(
				Lang::txt('COM_SUPPORT'),
				'index.php?option=' . $this->_option . '&controller=index'
			);
		}
		if (Pathway::count() == 1  && $this->_task)
		{
			$task = $this->_task;
			if (in_array($this->_task, array('ticket', 'new', 'display', 'save')))
			{
				$task = 'tickets';
			}
			if ($task == 'update')
			{
				$task = 'ticket';
			}
			Pathway::append(
				Lang::txt('COM_SUPPORT_' . strtoupper($task)),
				'index.php?option=' . $this->_option . '&controller=' . $this->_controller . '&task=' . $task
			);
			if ($this->_task == 'new')
			{
				Pathway::append(
					Lang::txt('COM_SUPPORT_' . strtoupper($this->_task)),
					'index.php?option=' . $this->_option . '&controller=' . $this->_controller . '&task=' . $this->_task
				);
			}
		}
		if (is_object($ticket) && $ticket->get('id'))
		{
			Pathway::append(
				'#' . $ticket->get('id'),
				$ticket->link()
			);
		}
	}

	/**
	 * Method to build and set the document title
	 *
	 * @param   object  $ticket
	 * @return  void
	 */
	protected function _buildTitle($ticket=null)
	{
		$this->_title = Lang::txt(strtoupper($this->_option));
		if ($this->_task)
		{
			if ($this->_task == 'new' || $this->_task == 'display')
			{
				$this->_title .= ': ' . Lang::txt('COM_SUPPORT_TICKETS');
			}
			if ($this->_task != 'display')
			{
				if ($this->_task == 'update')
				{
					$this->_title .= ': ' . Lang::txt('COM_SUPPORT_TiCKET');
				}
				else
				{
					$this->_title .= ': ' . Lang::txt('COM_SUPPORT_' . strtoupper($this->_task));
				}
			}
		}
		if (is_object($ticket) && $ticket->get('id'))
		{
			$this->_title .= ' #' . $ticket->get('id');
		}

		App::get('document')->setTitle($this->_title);
	}

	/**
	 * Displays a list of tickets
	 *
	 * @return	void
	 */
	public function statsTask()
	{
		// Check authorization
		if (User::isGuest())
		{
			$return = base64_encode(Request::getVar('REQUEST_URI', Route::url('index.php?option=' . $this->_option . '&controller=' . $this->_controller . '&task=' . $this->_task, false, true), 'server'));
			App::redirect(
				Route::url('index.php?option=com_users&view=login&return=' . $return, false)
			);
			return;
		}

		if (!$this->acl->check('read', 'tickets'))
		{
			$this->_return = Route::url('index.php?option=' . $this->_option . '&controller=' . $this->_controller . '&task=tickets');
		}

		// Set the page title
		$this->_buildTitle();

		$this->view->title = $this->_title;

		// Set the pathway
		$this->_buildPathway();

		$type = Request::getWord('type', 'submitted');
		$this->view->type = ($type == 'automatic') ? 1 : 0;

		$this->view->group = preg_replace('/[^0-9a-zA-Z_\-]/', '', Request::getVar('group', '_none_'));

		// Set up some dates
		$this->offset = Config::get('offset');

		$year  = Request::getInt('year', strftime("%Y", time()+($this->offset*60*60)));
		$month = strftime("%m", time()+($this->offset*60*60));

		$this->view->year = $year;
		$this->view->opened = array();
		$this->view->closed = array();

		$sql = "SELECT DISTINCT(g.`cn`), g.description
				FROM `#__support_tickets` AS s
				LEFT JOIN `#__xgroups` AS g ON g.gidNumber=s.`group_id`
				WHERE s.`group_id` > 0
				AND s.type=" . $this->database->quote($this->view->type) . "
				ORDER BY g.description ASC";
		$this->database->setQuery($sql);
		$this->view->groups = $this->database->loadObjectList();

		// Users
		$this->view->users = null;

		if ($this->view->group == '_none_')
		{
			$query = "SELECT DISTINCT a.username, a.name, a.id
					FROM `#__users` AS a
					INNER JOIN `#__support_tickets` AS s ON s.owner = a.id
					WHERE a.block = '0' AND s.type=" . $this->database->quote($this->view->type) . " AND s.group_id=0
					ORDER BY a.name";
		}
		else if ($this->view->group)
		{
			$query = "SELECT a.username, a.name, a.id
					FROM `#__users` AS a
					INNER JOIN `#__xgroups_members` AS gm ON gm.uidNumber=a.id
					INNER JOIN `#__xgroups` AS g ON g.gidNumber=gm.gidNumber
					WHERE g.cn=" . $this->database->quote($this->view->group) ."
					ORDER BY a.name";
		}
		else
		{
			$query = "SELECT DISTINCT a.username, a.name, a.id
					FROM `#__users` AS a
					INNER JOIN `#__support_tickets` AS s ON s.owner = a.id
					WHERE a.block = '0' AND s.type=" . $this->database->quote($this->view->type) . "
					ORDER BY a.name";
		}

		$this->database->setQuery($query);
		$usrs = $this->database->loadObjectList();

		$users = array();
		if ($usrs)
		{
			foreach ($usrs as $j => $user)
			{
				if (!isset($user->total))
				{
					$user->total = 0;
				}
				if (!isset($user->tickets))
				{
					$user->tickets = array();
				}
				if (!isset($user->closed))
				{
					$user->closed = array();
				}
				$users[$user->id] = $user;
			}
		}

		// Get avgerage lifetime
		//$this->view->lifetime = $st->getAverageLifeOfTicket($this->view->type, $year, $this->view->group);

		// First ticket
		$sql = "SELECT YEAR(created)
				FROM `#__support_tickets`
				WHERE report!=''
				AND type='{$this->view->type}' ORDER BY created ASC LIMIT 1";
		$this->database->setQuery($sql);
		$first = intval($this->database->loadResult());

		$startyear  = $first;
		$startmonth = 1;

		$this->view->start = Request::getVar('start', $first . '-01');
		if ($this->view->start != $first . '-01')
		{
			if (!preg_match("/^([0-9]{4})-([0-9]{2})$/", $this->view->start))
			{
				$this->view->start = $first . '-01';
			}

			if (preg_match("/([0-9]{4})-([0-9]{2})/", $this->view->start, $regs))
			{
				$startmonth = date("m", mktime(0, 0, 0, $regs[2], 1, $regs[1]));
				$startyear = $first = date("Y", mktime(0, 0, 0, $regs[2], 1, $regs[1]));
				//$end = $year . '-' . $month;
			}
		}

		$this->view->end   = Request::getVar('end', '');

		$endmonth = $month;
		$endyear = date("Y");
		$endyear++;

		$end = '';
		if ($this->view->end)
		{
			if (!preg_match("/^([0-9]{4})-([0-9]{2})$/", $this->view->end))
			{
				$this->view->end = '';
			}

			// We need to get the NEXT month. This is so that for a time range
			// of 2013-01 to 2013-12 will display data for all of 2013-12.
			// So, the actual time range is 2013-01-01 00:00:00 to 2014-01-01 00:00:00
			if (preg_match("/([0-9]{4})-([0-9]{2})/", $this->view->end, $regs))
			{
				$endmonth = intval($regs[2]);
				$endyear  = intval($regs[1]);
				$endyear++;

				$month = date("m", mktime(0, 0, 0, ($endmonth+1), 1, $regs[1]));
				$year  = date("Y", mktime(0, 0, 0, ($endmonth+1), 1, $regs[1]));
				$end   = $year . '-' . $month;
			}
		}
		else
		{
			$this->view->end = $year . '-' . $month;
		}

		// Opened tickets
		$sql = "SELECT id, created, YEAR(created) AS `year`, MONTH(created) AS `month`, open, status, owner
				FROM `#__support_tickets`
				WHERE report!=''
				AND type=" . $this->view->type; // . " AND open=1";
		if ($this->view->group == '_none_')
		{
			$sql .= " AND `group_id`=0";
		}
		else if ($this->view->group)
		{
			$gidNumber = 0;
			if ($group = \Hubzero\User\Group::getInstance($this->view->group))
			{
				$gidNumber = $group->get('gidNumber');
			}
			$sql .= " AND `group_id`=" . $this->database->quote($gidNumber);
		}
		if ($this->view->start && $end)
		{
			$sql .= " AND created >= " . $this->database->quote($this->view->start . '-01 00:00:00') . " AND created < " . $this->database->quote($end . '-01 00:00:00');
		}
		$sql .= " ORDER BY created ASC";
		$this->database->setQuery($sql);
		$openTickets = $this->database->loadObjectList();

		$owners = array();

		$open = array();
		$this->view->opened['open'] = 0;
		$this->view->opened['new'] = 0;
		$this->view->opened['unassigned'] = 0;
		foreach ($openTickets as $o)
		{
			if (!isset($open[$o->year]))
			{
				$open[$o->year] = array();
			}
			if (!isset($open[$o->year][$o->month]))
			{
				$open[$o->year][$o->month] = 0;
			}
			$open[$o->year][$o->month]++;

			$this->view->opened['open']++;

			if ($o->open)
			{
				if (!$o->status)
				{
					$this->view->opened['new']++;
				}
				if (!$o->owner)
				{
					$this->view->opened['unassigned']++;
				}
				else
				{
					if (!isset($owners[$o->owner]))
					{
						$owners[$o->owner] = 0;
					}
					$owners[$o->owner]++;
				}
			}
		}

		// Closed tickets
		$sql = "SELECT t.id AS ticket, t.owner AS created_by, t.closed AS created, YEAR(t.closed) AS `year`, MONTH(t.closed) AS `month`, UNIX_TIMESTAMP(t.created) AS opened, UNIX_TIMESTAMP(t.closed) AS closed
				FROM `#__support_tickets` AS t
				WHERE t.report!=''
				AND t.type=" . $this->database->quote($this->view->type) . " AND t.open=0";
		if ($this->view->group == '_none_')
		{
			$sql .= " AND t.`group_id`=0";
		}
		else if ($this->view->group)
		{
			$gidNumber = 0;
			if ($group = \Hubzero\User\Group::getInstance($this->view->group))
			{
				$gidNumber = $group->get('gidNumber');
			}
			$sql .= " AND t.`group_id`=" . $this->database->quote($gidNumber);
		}
		if ($this->view->start && $end)
		{
			$sql .= " AND t.closed >= " . $this->database->quote($this->view->start . '-01 00:00:00') . " AND t.closed < " . $this->database->quote($end . '-01 00:00:00');
		}
		$sql .= " ORDER BY t.closed ASC";

		$this->database->setQuery($sql);
		$clsd = $this->database->loadObjectList();

		$this->view->opened['closed'] = 0;
		// First we need to loop through all the entries and remove some potential duplicates
		$closedTickets = array();
		foreach ($clsd as $closed)
		{
			if (!isset($closedTickets[$closed->ticket]))
			{
				$closedTickets[$closed->ticket] = $closed;
			}
			else
			{
				if ($closedTickets[$closed->ticket]->created < $closed->created)
				{
					$closedTickets[$closed->ticket] = $closed;
				}
			}
		}
		$this->view->closedTickets = $closedTickets;
		// Loop through info and divide into years/months
		$closed = array();
		foreach ($closedTickets as $o)
		{
			if (!isset($closed[$o->year]))
			{
				$closed[$o->year] = array();
			}
			if (!isset($closed[$o->year][$o->month]))
			{
				$closed[$o->year][$o->month] = 0;
			}
			$closed[$o->year][$o->month]++;
			$this->view->opened['closed']++;
		}

		// Group data by year and gather some info for each user

		$this->view->closedmonths = array();
		$this->view->openedmonths = array();

		for ($k = $startyear, $n = $endyear; $k < $n; $k++)
		{
			$this->view->closedmonths[$k] = array();
			$this->view->openedmonths[$k] = array();
			if ($k == $startyear && intval($startmonth) > 1)
			{
				$i = intval($startmonth) - 1;
			}
			else
			{
				$i = 1;
			}

			for ($i; $i <= 12; $i++)
			{
				if ($k == $year && $i > $endmonth)
				{
					break;
				}
				else
				{
					$this->view->closedmonths[$k][$i] = (isset($closed[$k]) && isset($closed[$k][$i])) ? $closed[$k][$i] : 0;
					$this->view->openedmonths[$k][$i] = (isset($open[$k]) && isset($open[$k][$i]))     ? $open[$k][$i]   : 0;
				}

				foreach ($users as $j => $user)
				{
					if (!isset($user->closed[$k]))
					{
						$user->closed[$k] = array();
					}

					/*if ($i <= "9"&preg_match("#(^[1-9]{1})#",$i))
					{
						$month = "0$i";
					}*/
					if ($k == $year && $i > $month)
					{
						$user->closed[$k][$i] = 'null';
					}
					else
					{
						$user->closed[$k][$i] = 0;
					}

					$users[$j] = $user;
				}
			}
		}

		foreach ($closedTickets as $c)
		{
			if (isset($users[$c->created_by]))
			{
				$y = intval($c->year);
				$m = intval($c->month);

				if (!$y && !$m)
				{
					continue;
				}

				if (!isset($users[$c->created_by]->closed[$y]))
				{
					$users[$c->created_by]->closed[$y] = array();
				}
				if (!isset($users[$c->created_by]->closed[$y][$m]))
				{
					$users[$c->created_by]->closed[$y][$m] = 0;
				}
				$users[$c->created_by]->closed[$y][$m]++;
				$users[$c->created_by]->total++;
				$users[$c->created_by]->tickets[] = $c;
			}
		}

		// Sort users by number of tickets closed
		$u = array();
		foreach ($users as $k => $user)
		{
			$user->assigned = 0;
			if (isset($owners[$user->id]))
			{
				$user->assigned = $owners[$user->id];
			}
			$key = (string) $user->total;
			if (isset($u[$key]))
			{
				$key .= '.' . $k;
			}
			$u[$key] = $user;
		}
		krsort($u);
		$this->view->users = $u;

		$this->view
			->set('config', $this->config)
			->set('first', $first)
			->set('month', $month)
			->setErrors($this->getErrors())
			->display();
	}

	/**
	 * Displays a list of support tickets
	 *
	 * @return  void
	 */
	public function displayTask()
	{
		if (User::isGuest())
		{
			$return = base64_encode(Request::getVar('REQUEST_URI', Route::url('index.php?option=' . $this->_option . '&controller=' . $this->_controller . '&task=' . $this->_task, false, true), 'server'));
			App::redirect(
				Route::url('index.php?option=com_users&view=login&return=' . $return, false)
			);
			return;
		}

		$filters = $this->_getFilters();

		// Paging
		$filters['limit'] = Request::getState(
			$this->_option . '.' . $this->_controller . '.limit',
			'limit',
			Config::get('list_limit'),
			'int'
		);
		$filters['start'] = Request::getState(
			$this->_option . '.' . $this->_controller . '.limitstart',
			'limitstart',
			0,
			'int'
		);
		// Query to filter by
		$filters['show'] = Request::getState(
			$this->_option . '.' . $this->_controller . '.show',
			'show',
			0,
			'int'
		);
		// Search
		$search       = urldecode(Request::getState(
			$this->_option . '.' . $this->_controller . '.search',
			'search',
			''
		));

		// Get query list
		if (!$this->acl->check('read', 'tickets'))
		{
			$folders = QueryFolder::all()
				->whereEquals('user_id', 0)
				->whereEquals('iscore', 2)
				->order('ordering', 'asc')
				->rows();
		}
		else
		{
			$folders = QueryFolder::all()
				->whereEquals('user_id', User::get('id'))
				->order('ordering', 'asc')
				->rows();

			// Does the user have any folders?
			if (!count($folders))
			{
				// Get all the default folders
				$folders = QueryFolder::cloneCore(User::get('id'));
			}
		}

		$filters['search']  = '';
		$filters['sort']    = 'id';
		$filters['sortdir'] = 'DESC';

		$matchQuery = false;

		foreach ($folders as $folder)
		{
			foreach ($folder->queries->sort('ordering') as $query)
			{
				if ($query->get('id') != $filters['show'])
				{
					$filters['search'] = '';
				}

				$query->set('count', Ticket::countWithQuery($query, $filters));

				if ($query->get('id') == $filters['show'])
				{
					$matchQuery = true;
					if ($search)
					{
						$filters['search'] = $search;
					}

					$total = ($search) ? Ticket::countWithQuery($query, $filters) : $query->get('count');

					// Incoming sort
					$filters['sort']         = trim(Request::getState(
						$this->_option . '.' . $this->_controller . '.sort',
						'sort',
						$query->get('sort')
					));

					$filters['sortdir']     = trim(Request::getState(
						$this->_option . '.' . $this->_controller . '.sortdir',
						'sortdir',
						$query->get('sort_dir')
					));

					// Get the records
					$tickets = Ticket::allWithQuery($query, $filters);
				}
			}
		}

		$filters['search'] = $search;

		if ($filters['show'] < 0)
		{
			$total = $watch[($filters['show'] == -1 ? 'open' : 'closed')];

			$tickets = Ticket::all()
				->whereEquals('open', ($filters['show'] == -1 ? 1 : 0))
				->whereIn('id', $watching)
				->order('created', 'desc')
				->start($filters['start'])
				->limit($filters['limit'])
				->rows();
		}

		if (!$filters['show'] || ($filters['show'] > 0 && !$matchQuery))
		{
			// Jump back to the beginning of the folders list
			// and try to find the first query available
			// to make it the current "active" query
			foreach ($folders as $folder)
			{
				if (count($folder->queries) > 0)
				{
					$query = $folder->queries->first();
					$filters['show'] = $query->get('id');
					break;
				}
				else
				{	// for no custom queries.
					// TODO - not sure this works, pretty edge case though
					$query = Query::blank();
					$query->set('count', 0);
				}
			}

			// Set the total for the pagination
			$total = ($search) ? Ticket::countWithQuery($query, $filters) : $query->get('count');

			// Incoming sort
			$filters['sort']   = trim(Request::getState(
				$this->_option . '.' . $this->_controller . '.sort',
				'filter_order',
				$query->sort
			));
			$filters['sortdir'] = trim(Request::getState(
				$this->_option . '.' . $this->_controller . '.sortdir',
				'filter_order_Dir',
				$query->sort_dir
			));

			// Get the records
			$tickets = Ticket::allWithQuery($query, $filters);
		}

		$watching = Watching::all()
			->whereEquals('user_id', User::get('id'))
			->rows()
			->fieldsByKey('ticket_id');

		$watch = array(
			'open'   => Ticket::all()->whereEquals('open', 1)->whereIn('id', $watching)->total(),
			'closed' => Ticket::all()->whereEquals('open', 0)->whereIn('id', $watching)->total()
		);

		// Set the page title
		$this->_buildTitle();

		// Set the pathway
		$this->_buildPathway();
{'fraction_non_alphanumeric': 0.2171739411993265, 'fraction_numerical': 0.011216163709364072, 'mean_word_length': 2.96040213377103, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 704, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3830939', 'n_tokens_mistral': 16875, 'n_tokens_neox': 13737, 'n_words': 2910}