static function &getTicketCount()
{
FST_Ticket_Helper::getAdminPermissions();
if (empty(FST_Ticket_Helper::$counts)) {
$db = JFactory::getDBO();
$query = "SELECT count( * ) AS count, ticket_status_id FROM #__fst_ticket_ticket WHERE 1 ";
$query .= FST_Ticket_Helper::$_perm_where;
$query .= " GROUP BY ticket_status_id";
$db->setQuery($query);
$rows = $db->loadAssocList();
$out = array();
FST_Ticket_Helper::GetStatusList();
foreach (FST_Ticket_Helper::$status_list as $status) {
$out[$status->id] = 0;
}
if (count($rows) > 0) {
foreach ($rows as $row) {
$out[$row['ticket_status_id']] = $row['count'];
}
}
// work out counts for allopen, closed, all, archived
$archived = FST_Ticket_Helper::GetStatusID("def_archive");
$out['archived'] = 0;
if (array_key_exists($archived, $out)) {
$out['archived'] = $out[$archived];
}
$allopen = FST_Ticket_Helper::GetStatusIDs("is_closed", true);
$out['allopen'] = 0;
foreach ($allopen as $id) {
if (array_key_exists($id, $out)) {
$out['allopen'] += $out[$id];
}
}
$allclosed = FST_Ticket_Helper::GetClosedStatus();
$out['allclosed'] = 0;
foreach ($allclosed as $id) {
if (array_key_exists($id, $out)) {
$out['allclosed'] += $out[$id];
}
}
$all = FST_Ticket_Helper::GetStatusIDs("def_archive", true);
$out['all'] = 0;
foreach ($all as $id) {
if (array_key_exists($id, $out)) {
$out['all'] += $out[$id];
}
}
FST_Ticket_Helper::$counts = $out;
}
return FST_Ticket_Helper::$counts;
}
function &getTickets()
{
FST_Ticket_Helper::getAdminPermissions();
$mainframe = JFactory::getApplication();
$limit = $mainframe->getUserStateFromRequest('global.list.limit_ticket', 'limit', FST_Helper::getUserSetting('per_page'), 'int');
$limitstart = JRequest::getVar('limitstart', 0, '', 'int');
$limitstart = $limit != 0 ? floor($limitstart / $limit) * $limit : 0;
$db = JFactory::getDBO();
if (empty($this->_tickets)) {
$query = "SELECT t.*, s.title as status, s.color, u.name, au.name as assigned, u.email as useremail, u.username as username, au.email as handleremail, au.username as handlerusername, ";
$query .= " dept.title as department, cat.title as category, prod.title as product, pri.title as priority, pri.color as pricolor, ";
$query .= " grp.groupname as groupname, grp.id as group_id ";
$query .= " , pri.translation as ptl, dept.translation as dtr, s.translation as str, cat.translation as ctr, prod.translation as prtr";
$query .= " FROM #__fst_ticket_ticket as t ";
$query .= " LEFT JOIN #__fst_ticket_status as s ON t.ticket_status_id = s.id ";
$query .= " LEFT JOIN #__users as u ON t.user_id = u.id ";
$query .= " LEFT JOIN #__fst_user as a ON t.admin_id = a.id ";
$query .= " LEFT JOIN #__users as au ON a.user_id = au.id ";
$query .= " LEFT JOIN #__fst_ticket_dept as dept ON t.ticket_dept_id = dept.id ";
$query .= " LEFT JOIN #__fst_ticket_cat as cat ON t.ticket_cat_id = cat.id ";
$query .= " LEFT JOIN #__fst_prod as prod ON t.prod_id = prod.id ";
$query .= " LEFT JOIN #__fst_ticket_pri as pri ON t.ticket_pri_id = pri.id ";
$query .= " LEFT JOIN (SELECT group_id, user_id FROM #__fst_ticket_group_members GROUP BY user_id) as mem ON t.user_id = mem.user_id ";
$query .= " LEFT JOIN #__fst_ticket_group as grp ON grp.id = mem.group_id ";
$def_open = FST_Ticket_Helper::GetStatusID('def_open');
$tickets = JRequest::getVar('tickets', $def_open);
/*if ($tickets == 'open')
$query .= " WHERE (ticket_status_id = 1) ";
elseif ($tickets == 'follow')
$query .= " WHERE (ticket_status_id = 2) ";
elseif ($tickets == 'closed')
$query .= " WHERE (ticket_status_id = 3) ";
elseif ($tickets == 'reply')
$query .= " WHERE (ticket_status_id = 4) ";
else*/
if ($tickets == "open") {
$open = FST_Ticket_Helper::GetStatusIDs("def_open");
// tickets that arent closed
$query .= " WHERE ticket_status_id IN ( " . implode(", ", $open) . ") ";
} else {
if ($tickets == 'allopen') {
$allopen = FST_Ticket_Helper::GetStatusIDs("is_closed", true);
// tickets that arent closed
$query .= " WHERE ticket_status_id IN ( " . implode(", ", $allopen) . ") ";
} elseif ($tickets == 'closed') {
$allopen = FST_Ticket_Helper::GetStatusIDs("is_closed");
// remove the archived tickets from the list to deal with
$def_archive = FST_Ticket_Helper::GetStatusID('def_archive');
foreach ($allopen as $offset => $value) {
if ($value == $def_archive) {
unset($allopen[$offset]);
}
}
// tickets that are closed
$query .= " WHERE ticket_status_id IN ( " . implode(", ", $allopen) . ") ";
} elseif ($tickets == 'all') {
// need all tickets that arent archived
$allopen = FST_Ticket_Helper::GetStatusIDs("def_archive", true);
$query .= " WHERE ticket_status_id IN ( " . implode(", ", $allopen) . " ) ";
} elseif ($tickets == 'archived') {
// need all tickets that arent archived
$allopen = FST_Ticket_Helper::GetStatusIDs("def_archive");
$query .= " WHERE ticket_status_id IN ( " . implode(", ", $allopen) . " ) ";
} else {
$query .= " WHERE ticket_status_id = " . (int) FSTJ3Helper::getEscaped($db, $tickets);
}
}
//echo $query. "<br>";
$query .= FST_Ticket_Helper::$_perm_where;
$order = array();
if (FST_Helper::getUserSetting("group_products")) {
$order[] = "prod.ordering";
}
if (FST_Helper::getUserSetting("group_departments")) {
$order[] = "dept.title";
}
if (FST_Helper::getUserSetting("group_cats")) {
$order[] = "cat.title";
}
if (FST_Helper::getUserSetting("group_pri")) {
$order[] = "pri.ordering DESC";
}
if (FST_Helper::getUserSetting("group_group")) {
$order[] = "case when grp.groupname is null then 1 else 0 end";
$order[] = "grp.groupname";
}
$order[] = "lastupdate DESC";
$query .= " ORDER BY " . implode(", ", $order);
$db->setQuery($query);
$db->query();
$this->_ticketcount = $db->getNumRows();
//echo $query . "<br>";
$db->setQuery($query, $limitstart, $limit);
$this->_tickets = $db->loadAssocList('id');
}
$result['pagination'] = new JPaginationEx($this->_ticketcount, $limitstart, $limit);
$result['count'] =& $this->_ticketcount;
$result['tickets'] =& $this->_tickets;
return $result;
}
