/**
* Remove a folder
*
* @return void
*/
public function saveorderingTask()
{
// Check for request forgeries
Request::checkToken(['get', 'post']);
// Incoming
$folders = Request::getVar('folder', array());
$queries = Request::getVar('queries', array());
if (is_array($folders)) {
foreach ($folders as $key => $folder) {
$row = new QueryFolder($this->database);
$row->load(intval($folder));
$row->ordering = $key + 1;
$row->store();
}
}
if (is_array($queries)) {
$folder = null;
$i = 0;
foreach ($queries as $query) {
$bits = explode('_', $query);
$fd = intval($bits[0]);
$id = intval($bits[1]);
if ($fd != $folder) {
$folder = $fd;
$i = 0;
}
$row = new Query($this->database);
$row->load($id);
$row->folder_id = $fd;
$row->ordering = $i + 1;
$row->store();
$i++;
}
}
if (!$no_html) {
// Output messsage and redirect
App::redirect(Route::url('index.php?option=' . $this->_option . '&controller=' . $this->_controller, false), Lang::txt('COM_SUPPORT_QUERY_FOLDER_SUCCESSFULLY_REMOVED'));
}
$response = new stdClass();
$response->success = 1;
$response->message = Lang::txt('COM_SUPPORT_QUERY_FOLDER_ORDERING_UPDATED');
echo json_encode($response);
}
/**
* Displays a list of tickets
*
* @return void
*/
public function displayTask()
{
$obj = new Tables\Ticket($this->database);
// Get filters
$this->view->total = 0;
$this->view->rows = array();
$this->view->filters = array('limit' => Request::getState($this->_option . '.' . $this->_controller . '.limit', 'limit', Config::get('list_limit'), 'int'), 'start' => Request::getState($this->_option . '.' . $this->_controller . '.limitstart', 'limitstart', 0, 'int'), 'show' => Request::getState($this->_option . '.' . $this->_controller . '.show', 'show', 0, 'int'), 'search' => '', 'sort' => 'id', 'sortdir' => 'DESC');
// Get query list
$sf = new Tables\QueryFolder($this->database);
$this->view->folders = $sf->find('list', array('user_id' => User::get('id'), 'sort' => 'ordering', 'sort_Dir' => 'asc'));
// Does the user have any folders?
if (!count($this->view->folders)) {
// Get all the default folders
$this->view->folders = $sf->cloneCore(User::get('id'));
}
$sq = new Tables\Query($this->database);
$queries = $sq->getRecords(array('user_id' => User::get('id'), 'sort' => 'ordering', 'sort_Dir' => 'asc'));
foreach ($queries as $query) {
$filters = $this->view->filters;
if ($query->id != $this->view->filters['show']) {
$filters['search'] = '';
}
$query->query = $sq->getQuery($query->conditions);
// Get a record count
$query->count = $obj->getCount($query->query, $filters);
foreach ($this->view->folders as $k => $v) {
if (!isset($this->view->folders[$k]->queries)) {
$this->view->folders[$k]->queries = array();
}
if ($query->folder_id == $v->id) {
$this->view->folders[$k]->queries[] = $query;
}
}
if ($query->id == $this->view->filters['show']) {
// Search
$this->view->filters['search'] = urldecode(Request::getState($this->_option . '.' . $this->_controller . '.search', 'search', ''));
// Set the total for the pagination
$this->view->total = $this->view->filters['search'] ? $obj->getCount($query->query, $this->view->filters) : $query->count;
// Incoming sort
$this->view->filters['sort'] = trim(Request::getState($this->_option . '.' . $this->_controller . '.sort', 'filter_order', $query->sort));
$this->view->filters['sortdir'] = trim(Request::getState($this->_option . '.' . $this->_controller . '.sortdir', 'filter_order_Dir', $query->sort_dir));
// Get the records
$this->view->rows = $obj->getRecords($query->query, $this->view->filters);
}
}
if (!$this->view->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
reset($this->view->folders);
foreach ($this->view->folders as $folder) {
if (!empty($folder->queries)) {
$query = $folder->queries[0];
$this->view->filters['show'] = $query->id;
break;
} else {
// for no custom queries.
$query = new Tables\Query($this->database);
$query->count = 0;
}
}
//$folder = reset($this->view->folders);
//$query = $folder->queries[0];
// Search
$this->view->filters['search'] = urldecode(Request::getState($this->_option . '.' . $this->_controller . '.search', 'search', ''));
// Set the total for the pagination
$this->view->total = $this->view->filters['search'] ? $obj->getCount($query->query, $this->view->filters) : $query->count;
// Incoming sort
$this->view->filters['sort'] = trim(Request::getState($this->_option . '.' . $this->_controller . '.sort', 'filter_order', $query->sort));
$this->view->filters['sortdir'] = trim(Request::getState($this->_option . '.' . $this->_controller . '.sortdir', 'filter_order_Dir', $query->sort_dir));
// Get the records
$this->view->rows = $obj->getRecords($query->query, $this->view->filters);
}
$watching = new Tables\Watching($this->database);
$this->view->watch = array('open' => $watching->count(array('user_id' => User::get('id'), 'open' => 1)), 'closed' => $watching->count(array('user_id' => User::get('id'), 'open' => 0)));
if ($this->view->filters['show'] < 0) {
if (!isset($this->view->filters['sort']) || !$this->view->filters['sort']) {
$this->view->filters['sort'] = trim(Request::getState($this->_option . '.' . $this->_controller . '.sort', 'filter_order', 'created'));
}
if (!isset($this->view->filters['sortdir']) || !$this->view->filters['sortdir']) {
$this->view->filters['sortdir'] = trim(Request::getState($this->_option . '.' . $this->_controller . '.sortdir', 'filter_order_Dir', 'DESC'));
}
$records = $watching->find(array('user_id' => User::get('id'), 'open' => $this->view->filters['show'] == -1 ? 1 : 0));
if (count($records)) {
$ids = array();
foreach ($records as $record) {
$ids[] = $record->ticket_id;
}
$this->view->rows = $obj->getRecords("(f.id IN ('" . implode("','", $ids) . "'))", $this->view->filters);
}
}
// Set any errors
foreach ($this->getErrors() as $error) {
$this->view->setError($error);
}
// Output the HTML
$this->view->display();
}
/**
* Reset default queries and folders.
* System will repopulate them.
*
* @return void
*/
public function resetTask()
{
// Check for request forgeries
Request::checkToken(['get', 'post']);
if (!User::authorise('core.admin', $this->_option)) {
return $this->cancelTask();
}
$db = App::get('db');
$query = new Query($db);
$folder = new QueryFolder($db);
$db->setQuery("DELETE FROM " . $db->quoteName($query->getTableName()));
$db->query();
$db->setQuery("DELETE FROM " . $db->quoteName($folder->getTableName()));
$db->query();
// Get all the default folders
$folders = $folder->find('list', array('user_id' => 0, 'sort' => 'ordering', 'sort_Dir' => 'asc', 'iscore' => 1));
if (count($folders) <= 0) {
$defaults = array(1 => array('Common', 'Mine', 'Custom'), 2 => array('Common', 'Mine'));
foreach ($defaults as $iscore => $fldrs) {
$i = 1;
foreach ($fldrs as $fldr) {
$f = new QueryFolder($db);
$f->iscore = $iscore;
$f->title = $fldr;
$f->check();
$f->ordering = $i;
$f->user_id = 0;
$f->store();
switch ($f->alias) {
case 'common':
$j = $iscore == 1 ? $query->populateDefaults('common', $f->id) : $query->populateDefaults('commonnotacl', $f->id);
break;
case 'mine':
$query->populateDefaults('mine', $f->id);
break;
default:
// Nothing for custom folder
break;
}
$i++;
}
}
}
$this->cancelTask();
}
/**
* Clone core folders and queries and assign
* them to a given user ID
*
* @param integer $user_id User ID
* @return array
*/
public function cloneCore($user_id = 0)
{
// Get all the default folders
$folders = $this->find('list', array('user_id' => 0, 'sort' => 'ordering', 'sort_Dir' => 'asc', 'iscore' => 1));
$sq = new Query($this->_db);
if (count($folders) <= 0) {
$defaults = array(1 => array('Common', 'Mine', 'Custom'), 2 => array('Common', 'Mine'));
foreach ($defaults as $iscore => $fldrs) {
$i = 1;
foreach ($fldrs as $fldr) {
$f = new self($this->_db);
$f->iscore = $iscore;
$f->title = $fldr;
$f->check();
$f->ordering = $i;
$f->user_id = 0;
$f->store();
switch ($f->alias) {
case 'common':
$j = $iscore == 1 ? $sq->populateDefaults('common', $f->id) : $sq->populateDefaults('commonnotacl', $f->id);
break;
case 'mine':
$sq->populateDefaults('mine', $f->id);
break;
default:
// Nothing for custom folder
break;
}
$i++;
if ($iscore == 1) {
$folders[] = $f;
}
}
}
}
$user_id = $user_id ?: User::get('id');
$fid = 0;
// Loop through each folder
foreach ($folders as $k => $folder) {
// Copy the folder for the user
$stqf = new self($this->_db);
$stqf->bind($folder);
$stqf->created_by = $user_id;
$stqf->created = Date::toSql();
$stqf->id = null;
$stqf->user_id = $user_id;
$stqf->iscore = 0;
$stqf->store();
$queries = $sq->find('list', array('folder_id' => $folder->id));
// Copy all the queries from the folder to the user
foreach ($queries as $query) {
$stq = new Query($this->_db);
$stq->bind($query);
$stq->created_by = $user_id;
$stq->created = Date::toSql();
$stq->id = null;
$stq->user_id = $user_id;
$stq->folder_id = $stqf->get('id');
$stq->iscore = 0;
$stq->store();
}
// If the folder is "custom", get its ID
if ($folder->alias == 'custom') {
$fid = $stqf->get('id');
}
$folders[$k] = $stqf;
}
if ($fid) {
$this->_db->setQuery("UPDATE `#__support_queries` SET `folder_id`=" . $this->_db->quote($fid) . " WHERE `user_id`=" . $this->_db->quote($user_id) . " AND `iscore`=0 AND `folder_id`=0");
$this->_db->query();
}
return $folders;
}
/**
* 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;
}
$this->view->database = $this->database;
// Create a Ticket object
$obj = new Tables\Ticket($this->database);
$this->view->total = 0;
$this->view->rows = array();
$this->view->filters = $this->_getFilters();
// Paging
$this->view->filters['limit'] = Request::getState($this->_option . '.' . $this->_controller . '.limit', 'limit', Config::get('list_limit'), 'int');
$this->view->filters['start'] = Request::getState($this->_option . '.' . $this->_controller . '.limitstart', 'limitstart', 0, 'int');
// Query to filter by
$this->view->filters['show'] = Request::getState($this->_option . '.' . $this->_controller . '.show', 'show', 0, 'int');
// Search
$this->view->filters['search'] = urldecode(Request::getState($this->_option . '.' . $this->_controller . '.search', 'search', ''));
// Get query list
$sf = new Tables\QueryFolder($this->database);
$sq = new Tables\Query($this->database);
if (!$this->acl->check('read', 'tickets')) {
$this->view->folders = $sf->find('list', array('user_id' => 0, 'sort' => 'ordering', 'sort_Dir' => 'asc', 'iscore' => 2));
$queries = $sq->find('list', array('user_id' => 0, 'sort' => 'ordering', 'sort_Dir' => 'asc', 'iscore' => 4));
} else {
$this->view->folders = $sf->find('list', array('user_id' => User::get('id'), 'sort' => 'ordering', 'sort_Dir' => 'asc'));
// Does the user have any folders?
if (!count($this->view->folders)) {
// Get all the default folders
$this->view->folders = $sf->cloneCore(User::get('id'));
}
$queries = $sq->find('list', array('user_id' => User::get('id'), 'sort' => 'ordering', 'sort_Dir' => 'asc'));
}
$this->view->filters['sort'] = 'id';
$this->view->filters['sortdir'] = 'DESC';
foreach ($queries as $query) {
$filters = $this->view->filters;
if ($query->id != $this->view->filters['show']) {
$filters['search'] = '';
}
$query->query = $sq->getQuery($query->conditions);
// Get a record count
$query->count = $obj->getCount($query->query, $filters);
foreach ($this->view->folders as $k => $v) {
if (!isset($this->view->folders[$k]->queries)) {
$this->view->folders[$k]->queries = array();
}
if ($query->folder_id == $v->id) {
$this->view->folders[$k]->queries[] = $query;
}
}
if ($query->id == $this->view->filters['show']) {
// Search
$this->view->filters['search'] = urldecode(Request::getState($this->_option . '.' . $this->_controller . '.search', 'search', ''));
// Set the total for the pagination
$this->view->total = $this->view->filters['search'] ? $obj->getCount($query->query, $this->view->filters) : $query->count;
// Incoming sort
$this->view->filters['sort'] = trim(Request::getState($this->_option . '.' . $this->_controller . '.sort', 'sort', $query->sort));
$this->view->filters['sortdir'] = trim(Request::getState($this->_option . '.' . $this->_controller . '.sortdir', 'sortdir', $query->sort_dir));
// Get the records
$this->view->rows = $obj->getRecords($query->query, $this->view->filters);
}
}
if (!$this->view->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
reset($this->view->folders);
foreach ($this->view->folders as $folder) {
if (!empty($folder->queries)) {
$query = $folder->queries[0];
$this->view->filters['show'] = $query->id;
break;
} else {
// for no custom queries.
$query = new Tables\Query($this->database);
$query->count = 0;
}
}
//$folder = reset($this->view->folders);
//$query = $folder->queries[0];
// Search
$this->view->filters['search'] = urldecode(Request::getState($this->_option . '.' . $this->_controller . '.search', 'search', ''));
// Set the total for the pagination
$this->view->total = $this->view->filters['search'] ? $obj->getCount($query->query, $this->view->filters) : $query->count;
// Incoming sort
$this->view->filters['sort'] = trim(Request::getState($this->_option . '.' . $this->_controller . '.sort', 'filter_order', $query->sort));
$this->view->filters['sortdir'] = trim(Request::getState($this->_option . '.' . $this->_controller . '.sortdir', 'filter_order_Dir', $query->sort_dir));
// Get the records
$this->view->rows = $obj->getRecords($query->query, $this->view->filters);
}
$watching = new Tables\Watching($this->database);
$this->view->watch = array('open' => $watching->count(array('user_id' => User::get('id'), 'open' => 1)), 'closed' => $watching->count(array('user_id' => User::get('id'), 'open' => 0)));
if ($this->view->filters['show'] < 0) {
$records = $watching->find(array('user_id' => User::get('id'), 'open' => $this->view->filters['show'] == -1 ? 1 : 0));
if (count($records)) {
$ids = array();
foreach ($records as $record) {
$ids[] = $record->ticket_id;
}
$this->view->rows = $obj->getRecords("(f.id IN ('" . implode("','", $ids) . "'))", $this->view->filters);
} else {
$this->view->rows = array();
}
}
// Set the page title
$this->_buildTitle();
$this->view->title = $this->_title;
// Set the pathway
$this->_buildPathway();
$this->view->acl = $this->acl;
// Output HTML
foreach ($this->getErrors() as $error) {
$this->view->setError($error);
}
$this->view->display();
}
/**
* Display module contents
*
* @return void
*/
public function display()
{
if (!App::isAdmin()) {
return;
}
include_once Component::path('com_support') . DS . 'tables' . DS . 'query.php';
include_once Component::path('com_support') . DS . 'tables' . DS . 'ticket.php';
$database = App::get('db');
$st = new Ticket($database);
$sq = new Query($database);
$types = array('common' => $sq->getCommon());
// Loop through each grouping
foreach ($types as $key => $queries) {
if (!is_array($queries) || count($queries) <= 0) {
$one = new stdClass();
$one->count = 0;
$one->id = 0;
$two = new stdClass();
$two->count = 0;
$two->id = 0;
$three = new stdClass();
$three->count = 0;
$three->id = 0;
$types[$key] = $queries = array($one, $two, $three);
}
// Loop through each query in a group
foreach ($queries as $k => $query) {
if ($query->id) {
// Build the query from the condition set
if (!$query->query) {
$query->query = $sq->getQuery($query->conditions);
}
// Get a record count
$types[$key][$k]->count = $st->getCount($query->query);
}
}
}
$this->topened = $types['common'];
$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->year = $year;
$this->opened = array();
$this->closed = array();
// First ticket
$sql = "SELECT YEAR(created)\n\t\t\t\tFROM `#__support_tickets`\n\t\t\t\tWHERE report!=''\n\t\t\t\tAND type='0' ORDER BY created ASC LIMIT 1";
$database->setQuery($sql);
$first = intval($database->loadResult());
// Opened tickets
$sql = "SELECT id, created, YEAR(created) AS `year`, MONTH(created) AS `month`, status, owner\n\t\t\t\tFROM `#__support_tickets`\n\t\t\t\tWHERE report!=''\n\t\t\t\tAND type=0";
$sql .= " AND (`group`='' OR `group` IS NULL)";
$sql .= " ORDER BY created ASC";
$database->setQuery($sql);
$openTickets = $database->loadObjectList();
$open = array();
$this->opened['open'] = 0;
$this->opened['new'] = 0;
$this->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->opened['open']++;
if (!$o->status) {
$this->opened['new']++;
}
if (!$o->owner) {
$this->opened['unassigned']++;
}
}
// Closed tickets
$sql = "SELECT c.ticket, c.created_by, c.created, YEAR(c.created) AS `year`, MONTH(c.created) AS `month`, UNIX_TIMESTAMP(t.created) AS opened, UNIX_TIMESTAMP(c.created) AS closed\n\t\t\t\tFROM `#__support_comments` AS c\n\t\t\t\tLEFT JOIN `#__support_tickets` AS t ON c.ticket=t.id\n\t\t\t\tWHERE t.report!=''\n\t\t\t\tAND type=0 AND open=0";
$sql .= " AND (`group`='' OR `group` IS NULL)";
$sql .= " ORDER BY c.created ASC";
$database->setQuery($sql);
$clsd = $database->loadObjectList();
$this->opened['closed'] = 0;
$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->closedTickets = $closedTickets;
$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->opened['closed']++;
}
// Group data by year and gather some info for each user
$y = date("Y");
$y++;
$this->closedmonths = array();
$this->openedmonths = array();
for ($k = $first, $n = $y; $k < $n; $k++) {
$this->closedmonths[$k] = array();
$this->openedmonths[$k] = array();
for ($i = 1; $i <= 12; $i++) {
if ($k == $year && $i > $month) {
break;
} else {
$this->closedmonths[$k][$i] = isset($closed[$k]) && isset($closed[$k][$i]) ? $closed[$k][$i] : 0;
$this->openedmonths[$k][$i] = isset($open[$k]) && isset($open[$k][$i]) ? $open[$k][$i] : 0;
}
}
}
// Get the view
parent::display();
}
