// This code will be used to count number of results
$sql_count = "SELECT COUNT(*) FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "tickets` WHERE ";
// This is common SQL for both queries
$sql = "";
// Some default settings
$archive = array(1 => 0, 2 => 0);
$s_my = array(1 => 1, 2 => 1);
$s_ot = array(1 => 1, 2 => 1);
$s_un = array(1 => 1, 2 => 1);
// --> TICKET CATEGORY
$category = intval(hesk_GET('category', 0));
// Make sure user has access to this category
if ($category && hesk_okCategory($category, 0)) {
$sql .= " `category`='{$category}' ";
} else {
$sql .= hesk_myCategories();
}
// Show only tagged tickets?
if (!empty($_GET['archive'])) {
$archive[2] = 1;
$sql .= " AND `archive`='1' ";
}
// Ticket owner preferences
$fid = 2;
require HESK_PATH . 'inc/assignment_search.inc.php';
$hesk_error_buffer = '';
$no_query = 0;
// Search query
$q = stripslashes(hesk_input(hesk_GET('q', '')));
// No query entered?
if (!strlen($q)) {
// Nothing or all selected, no need to modify the SQL code
$priority = $possible_priority;
} else {
// A custom selection of priorities
$sql .= " AND `priority` IN ('" . implode("','", array_keys($priority)) . "') ";
}
// Prepare variables used in search and forms
require_once HESK_PATH . 'inc/prepare_ticket_export.inc.php';
////////////////////////////////////////////////////////////////////////////////
// Can view tickets that are unassigned or assigned to others?
$can_view_ass_others = hesk_checkPermission('can_view_ass_others', 0);
$can_view_unassigned = hesk_checkPermission('can_view_unassigned', 0);
// Category options
$category_options = '';
$my_cat = array();
$res2 = hesk_dbQuery("SELECT `id`, `name` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "categories` WHERE " . hesk_myCategories('id') . " ORDER BY `cat_order` ASC");
while ($row = hesk_dbFetchAssoc($res2)) {
$my_cat[$row['id']] = hesk_msgToPlain($row['name'], 1);
$row['name'] = strlen($row['name']) > 50 ? substr($row['name'], 0, 50) . '...' : $row['name'];
$cat_selected = $row['id'] == $category ? 'selected="selected"' : '';
$category_options .= '<option value="' . $row['id'] . '" ' . $cat_selected . '>' . $row['name'] . '</option>';
}
// Generate export file
if (isset($_GET['w'])) {
// We'll need HH:MM:SS format for hesk_date() here
$hesk_settings['timeformat'] = 'H:i:s';
// Get staff names
$admins = array();
$result = hesk_dbQuery("SELECT `id`,`name` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "users` ORDER BY `id` ASC");
while ($row = hesk_dbFetchAssoc($result)) {
$admins[$row['id']] = $row['name'];
function hesk_ticketsByCategory()
{
global $hesk_settings, $hesklang, $date_from, $date_to, $can_run_reports_full;
/* List of categories */
$cat = array();
$res = hesk_dbQuery("SELECT `id`,`name` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "categories` WHERE " . ($can_run_reports_full ? '1' : hesk_myCategories('id')) . " ORDER BY `id` ASC");
while ($row = hesk_dbFetchAssoc($res)) {
$cat[$row['id']] = $row['name'];
}
$tickets = array();
$totals = array('num_tickets' => 0, 'resolved' => 0, 'all_replies' => 0, 'staff_replies' => 0, 'worked' => 0);
/* Populate category counts */
foreach ($cat as $id => $name) {
$tickets[$id] = array('num_tickets' => 0, 'resolved' => 0, 'all_replies' => 0, 'staff_replies' => 0, 'worked' => '');
}
/* SQL query for category stats */
$res = hesk_dbQuery("SELECT `category`, COUNT(*) AS `num_tickets`, " . ($hesk_settings['time_worked'] ? "SUM( TIME_TO_SEC(`time_worked`) ) AS `seconds_worked`," : '') . " SUM(`replies`) AS `all_replies`, SUM(staffreplies) AS `staff_replies` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "tickets` WHERE {$hesk_settings['dt_sql']} " . ($can_run_reports_full ? "" : " AND `t1`.`owner` = '" . intval($_SESSION['id']) . "'") . " GROUP BY `category`");
/* Update ticket values */
while ($row = hesk_dbFetchAssoc($res)) {
if (!$hesk_settings['time_worked']) {
$row['seconds_worked'] = 0;
}
if (isset($cat[$row['category']])) {
$tickets[$row['category']]['num_tickets'] += $row['num_tickets'];
$tickets[$row['category']]['all_replies'] += $row['all_replies'];
$tickets[$row['category']]['staff_replies'] += $row['staff_replies'];
$tickets[$row['category']]['worked'] = $hesk_settings['time_worked'] ? hesk_SecondsToHHMMSS($row['seconds_worked']) : 0;
} else {
/* Category deleted */
if (!isset($tickets[9999])) {
$cat[9999] = $hesklang['catd'];
$tickets[9999] = array('num_tickets' => $row['num_tickets'], 'resolved' => 0, 'all_replies' => $row['all_replies'], 'staff_replies' => $row['staff_replies'], 'worked' => $row['seconds_worked']);
} else {
$tickets[9999]['num_tickets'] += $row['num_tickets'];
$tickets[9999]['all_replies'] += $row['all_replies'];
$tickets[9999]['staff_replies'] += $row['staff_replies'];
$tickets[9999]['worked'] += $row['seconds_worked'];
}
}
$totals['num_tickets'] += $row['num_tickets'];
$totals['all_replies'] += $row['all_replies'];
$totals['staff_replies'] += $row['staff_replies'];
$totals['worked'] += $row['seconds_worked'];
}
// Get number of resolved tickets
$res = hesk_dbQuery("SELECT COUNT(*) AS `num_tickets` , `category` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "tickets` WHERE `status` = '3' " . ($can_run_reports_full ? "" : " AND `owner` = '" . intval($_SESSION['id']) . "'") . " AND {$hesk_settings['dt_sql']} GROUP BY `category`");
// Update number of open and resolved tickets
while ($row = hesk_dbFetchAssoc($res)) {
if (isset($cat[$row['category']])) {
$tickets[$row['category']]['resolved'] += $row['num_tickets'];
} else {
// Category deleted
$tickets[9999]['resolved'] += $row['num_tickets'];
}
$totals['resolved'] += $row['num_tickets'];
}
// Convert total seconds worked to HH:MM:SS
$totals['worked'] = $hesk_settings['time_worked'] ? hesk_SecondsToHHMMSS($totals['worked']) : 0;
if (isset($tickets[9999])) {
$tickets[9999]['worked'] = $hesk_settings['time_worked'] ? hesk_SecondsToHHMMSS($tickets[9999]['worked']) : 0;
}
?>
<table width="100%" cellpadding="5" style="text-align:justify;border-collapse:collapse;padding:10px;">
<tr style="border-bottom:1px solid #000000;">
<td><?php
echo $hesklang['category'];
?>
</td>
<td><?php
echo $hesklang['tickets'];
?>
</td>
<td><?php
echo $hesklang['topen'];
?>
</td>
<td><?php
echo $hesklang['closed'];
?>
</td>
<td><?php
echo $hesklang['replies'] . ' (' . $hesklang['all'] . ')';
?>
</td>
<td><?php
echo $hesklang['replies'] . ' (' . $hesklang['staff'] . ')';
?>
</td>
<?php
if ($hesk_settings['time_worked']) {
echo '<td>' . $hesklang['ts'] . '</td>';
}
?>
</tr>
<?php
$num_tickets = count($tickets);
if ($num_tickets > 10) {
?>
<tr style="border-bottom:1px solid #000000;">
<td><b><?php
echo $hesklang['totals'];
?>
</b></td>
<td><b><?php
echo $totals['num_tickets'];
?>
</b></td>
<td><b><?php
echo $totals['num_tickets'] - $totals['resolved'];
?>
</b></td>
<td><b><?php
echo $totals['resolved'];
?>
</b></td>
<td><b><?php
echo $totals['all_replies'];
?>
</b></td>
<td><b><?php
echo $totals['staff_replies'];
?>
</b></td>
<?php
if ($hesk_settings['time_worked']) {
echo '<td><b>' . $totals['worked'] . '</b></td>';
}
?>
</tr>
<?php
}
$cls = '';
foreach ($tickets as $k => $d) {
$cls = $cls ? '' : 'style="background:#EEEEE8;"';
?>
<tr <?php
echo $cls;
?>
>
<td><?php
echo $cat[$k];
?>
</td>
<td><?php
echo $d['num_tickets'];
?>
</td>
<td><?php
echo $d['num_tickets'] - $d['resolved'];
?>
</td>
<td><?php
echo $d['resolved'];
?>
</td>
<td><?php
echo $d['all_replies'];
?>
</td>
<td><?php
echo $d['staff_replies'];
?>
</td>
<?php
if ($hesk_settings['time_worked']) {
echo '<td>' . $d['worked'] . '</td>';
}
?>
</tr>
<?php
}
?>
<tr style="border-top:1px solid #000000;">
<td><b><?php
echo $hesklang['totals'];
?>
</b></td>
<td><b><?php
echo $totals['num_tickets'];
?>
</b></td>
<td><b><?php
echo $totals['num_tickets'] - $totals['resolved'];
?>
</b></td>
<td><b><?php
echo $totals['resolved'];
?>
</b></td>
<td><b><?php
echo $totals['all_replies'];
?>
</b></td>
<td><b><?php
echo $totals['staff_replies'];
?>
</b></td>
<?php
if ($hesk_settings['time_worked']) {
echo '<td><b>' . $totals['worked'] . '</b></td>';
}
?>
</tr>
</table>
<p> </p>
<?php
}
if (!isset($date_input)) {
$date_input = '';
}
/* Can view tickets that are unassigned or assigned to others? */
$can_view_ass_others = hesk_checkPermission('can_view_ass_others', 0);
$can_view_unassigned = hesk_checkPermission('can_view_unassigned', 0);
/* Category options */
$category_options = '';
if (isset($hesk_settings['categories']) && count($hesk_settings['categories'])) {
foreach ($hesk_settings['categories'] as $row['id'] => $row['name']) {
$row['name'] = strlen($row['name']) > 30 ? substr($row['name'], 0, 30) . '...' : $row['name'];
$selected = $row['id'] == $category ? 'selected="selected"' : '';
$category_options .= '<option value="' . $row['id'] . '" ' . $selected . '>' . $row['name'] . '</option>';
}
} else {
$res2 = hesk_dbQuery('SELECT `id`, `name` FROM `' . hesk_dbEscape($hesk_settings['db_pfix']) . 'categories` WHERE ' . hesk_myCategories('id') . ' ORDER BY `cat_order` ASC');
while ($row = hesk_dbFetchAssoc($res2)) {
$row['name'] = strlen($row['name']) > 30 ? substr($row['name'], 0, 30) . '...' : $row['name'];
$selected = $row['id'] == $category ? 'selected="selected"' : '';
$category_options .= '<option value="' . $row['id'] . '" ' . $selected . '>' . $row['name'] . '</option>';
}
}
/* List of staff */
if ($can_view_ass_others && !isset($admins)) {
$admins = array();
$res2 = hesk_dbQuery("SELECT `id`,`name` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "users` ORDER BY `name` ASC");
while ($row = hesk_dbFetchAssoc($res2)) {
$admins[$row['id']] = $row['name'];
}
}
$more = empty($_GET['more']) ? 0 : 1;
$info[$k] = $v['use'] ? $ticket[$k] : '';
}
// 3. Make sure all values are properly formatted for email
$ticket = hesk_ticketToPlain($info, 1, 0);
// Notify the customer
if (!isset($_POST['no_notify']) || intval(hesk_POST('no_notify')) != 1) {
hesk_notifyCustomer('new_reply_by_staff');
}
/* Set reply submitted message */
$_SESSION['HESK_SUCCESS'] = TRUE;
$_SESSION['HESK_MESSAGE'] = $hesklang['reply_submitted'];
if (!empty($_POST['close'])) {
$_SESSION['HESK_MESSAGE'] .= '<br /><br />' . $hesklang['ticket_marked'] . ' <span class="resolved">' . $hesklang['closed'] . '</span>';
}
/* What to do after reply? */
if ($_SESSION['afterreply'] == 1) {
header('Location: admin_main.php');
} elseif ($_SESSION['afterreply'] == 2) {
/* Get the next open ticket that needs a reply */
$res = hesk_dbQuery("SELECT * FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "tickets` WHERE `owner` IN ('0','" . intval($_SESSION['id']) . "') AND " . hesk_myCategories() . " AND `status` IN ('0','1') ORDER BY `owner` DESC, `priority` ASC LIMIT 1");
if (hesk_dbNumRows($res) == 1) {
$row = hesk_dbFetchAssoc($res);
$_SESSION['HESK_MESSAGE'] .= '<br /><br />' . $hesklang['rssn'];
header('Location: admin_ticket.php?track=' . $row['trackid'] . '&Refresh=' . rand(10000, 99999));
} else {
header('Location: admin_main.php');
}
} else {
header('Location: admin_ticket.php?track=' . $ticket['trackid'] . '&Refresh=' . rand(10000, 99999));
}
exit;
function hesk_ticketsByCategory()
{
global $hesk_settings, $hesklang, $date_from, $date_to, $can_run_reports_full;
/* List of categories */
$cat = array();
$res = hesk_dbQuery("SELECT `id`,`name` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "categories` WHERE " . ($can_run_reports_full ? '1' : hesk_myCategories('id')) . " ORDER BY `id` ASC");
while ($row = hesk_dbFetchAssoc($res)) {
$cat[$row['id']] = $row['name'];
}
$tickets = array();
$totals = array('num_tickets' => 0, 'resolved' => 0, 'all_replies' => 0, 'staff_replies' => 0, 'worked' => 0);
/* Populate category counts */
foreach ($cat as $id => $name) {
$tickets[$id] = array('num_tickets' => 0, 'resolved' => 0, 'all_replies' => 0, 'staff_replies' => 0, 'worked' => '');
}
/* SQL query for category stats */
$res = hesk_dbQuery("SELECT `category`, COUNT(*) AS `num_tickets`, " . ($hesk_settings['time_worked'] ? "SUM( TIME_TO_SEC(`time_worked`) ) AS `seconds_worked`," : '') . " SUM(`replies`) AS `all_replies`, SUM(staffreplies) AS `staff_replies` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "tickets` WHERE {$hesk_settings['dt_sql']} " . ($can_run_reports_full ? "" : " AND `t1`.`owner` = '" . intval($_SESSION['id']) . "'") . " GROUP BY `category`");
/* Update ticket values */
while ($row = hesk_dbFetchAssoc($res)) {
if (!$hesk_settings['time_worked']) {
$row['seconds_worked'] = 0;
}
if (isset($cat[$row['category']])) {
$tickets[$row['category']]['num_tickets'] += $row['num_tickets'];
$tickets[$row['category']]['all_replies'] += $row['all_replies'];
$tickets[$row['category']]['staff_replies'] += $row['staff_replies'];
$tickets[$row['category']]['worked'] = $hesk_settings['time_worked'] ? hesk_SecondsToHHMMSS($row['seconds_worked']) : 0;
} else {
/* Category deleted */
if (!isset($tickets[9999])) {
$cat[9999] = $hesklang['catd'];
$tickets[9999] = array('num_tickets' => $row['num_tickets'], 'resolved' => 0, 'all_replies' => $row['all_replies'], 'staff_replies' => $row['staff_replies'], 'worked' => $row['seconds_worked']);
} else {
$tickets[9999]['num_tickets'] += $row['num_tickets'];
$tickets[9999]['all_replies'] += $row['all_replies'];
$tickets[9999]['staff_replies'] += $row['staff_replies'];
$tickets[9999]['worked'] += $row['seconds_worked'];
}
}
$totals['num_tickets'] += $row['num_tickets'];
$totals['all_replies'] += $row['all_replies'];
$totals['staff_replies'] += $row['staff_replies'];
$totals['worked'] += $row['seconds_worked'];
}
// Get number of resolved tickets
$res = hesk_dbQuery("SELECT COUNT(*) AS `num_tickets` , `category` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "tickets` WHERE `status` IN (SELECT `ID` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "statuses` WHERE `IsClosed` = 1) " . ($can_run_reports_full ? "" : " AND `owner` = '" . intval($_SESSION['id']) . "'") . " AND {$hesk_settings['dt_sql']} GROUP BY `category`");
// Update number of open and resolved tickets
while ($row = hesk_dbFetchAssoc($res)) {
if (isset($cat[$row['category']])) {
$tickets[$row['category']]['resolved'] += $row['num_tickets'];
} else {
// Category deleted
$tickets[9999]['resolved'] += $row['num_tickets'];
}
$totals['resolved'] += $row['num_tickets'];
}
// Convert total seconds worked to HH:MM:SS
$totals['worked'] = $hesk_settings['time_worked'] ? hesk_SecondsToHHMMSS($totals['worked']) : 0;
if (isset($tickets[9999])) {
$tickets[9999]['worked'] = $hesk_settings['time_worked'] ? hesk_SecondsToHHMMSS($tickets[9999]['worked']) : 0;
}
?>
<table class="table table-striped table-condensed">
<tr>
<th><?php
echo $hesklang['category'];
?>
</th>
<th><?php
echo $hesklang['tickets'];
?>
</th>
<th><?php
echo $hesklang['topen'];
?>
</th>
<th><?php
echo $hesklang['closed_title'];
?>
</th>
<th><?php
echo $hesklang['replies'] . ' (' . $hesklang['all'] . ')';
?>
</th>
<th><?php
echo $hesklang['replies'] . ' (' . $hesklang['staff'] . ')';
?>
</th>
<?php
if ($hesk_settings['time_worked']) {
echo '<th>' . $hesklang['ts'] . '</th>';
}
?>
</tr>
<?php
$num_tickets = count($tickets);
if ($num_tickets > 10) {
?>
<tr>
<td><b><?php
echo $hesklang['totals'];
?>
</b></td>
<td><b><?php
echo $totals['num_tickets'];
?>
</b></td>
<td><b><?php
echo $totals['num_tickets'] - $totals['resolved'];
?>
</b></td>
<td><b><?php
echo $totals['resolved'];
?>
</b></td>
<td><b><?php
echo $totals['all_replies'];
?>
</b></td>
<td><b><?php
echo $totals['staff_replies'];
?>
</b></td>
<?php
if ($hesk_settings['time_worked']) {
echo '<td><b>' . $totals['worked'] . '</b></td>';
}
?>
</tr>
<?php
}
foreach ($tickets as $k => $d) {
?>
<tr>
<td><?php
echo $cat[$k];
?>
</td>
<td><?php
echo $d['num_tickets'];
?>
</td>
<td><?php
echo $d['num_tickets'] - $d['resolved'];
?>
</td>
<td><?php
echo $d['resolved'];
?>
</td>
<td><?php
echo $d['all_replies'];
?>
</td>
<td><?php
echo $d['staff_replies'];
?>
</td>
<?php
if ($hesk_settings['time_worked']) {
echo '<td>' . $d['worked'] . '</td>';
}
?>
</tr>
<?php
}
?>
<tr>
<td><b><?php
echo $hesklang['totals'];
?>
</b></td>
<td><b><?php
echo $totals['num_tickets'];
?>
</b></td>
<td><b><?php
echo $totals['num_tickets'] - $totals['resolved'];
?>
</b></td>
<td><b><?php
echo $totals['resolved'];
?>
</b></td>
<td><b><?php
echo $totals['all_replies'];
?>
</b></td>
<td><b><?php
echo $totals['staff_replies'];
?>
</b></td>
<?php
if ($hesk_settings['time_worked']) {
echo '<td><b>' . $totals['worked'] . '</b></td>';
}
?>
</tr>
</table>
<?php
}
$ticket = hesk_ticketToPlain($info, 1, 0);
// Notify the assigned staff?
if ($submit_as_customer) {
if ($ticket['owner'] && $ticket['owner'] != $_SESSION['id']) {
hesk_notifyAssignedStaff(false, 'new_reply_by_customer', 'notify_reply_my');
}
} elseif (!isset($_POST['no_notify']) || intval(hesk_POST('no_notify')) != 1) {
hesk_notifyCustomer('new_reply_by_staff');
}
// Delete any existing drafts from this owner for this ticket
hesk_dbQuery("DELETE FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "reply_drafts` WHERE `owner`=" . intval($_SESSION['id']) . " AND `ticket`=" . intval($ticket['id']) . " LIMIT 1");
/* Set reply submitted message */
$_SESSION['HESK_SUCCESS'] = TRUE;
$_SESSION['HESK_MESSAGE'] = $hesklang['reply_submitted'];
/* What to do after reply? */
if ($_SESSION['afterreply'] == 1) {
header('Location: admin_main.php');
} elseif ($_SESSION['afterreply'] == 2) {
/* Get the next open ticket that needs a reply */
$res = hesk_dbQuery("SELECT * FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "tickets` WHERE `owner` IN ('0','" . intval($_SESSION['id']) . "') AND " . hesk_myCategories() . " AND `status` IN (SELECT `ID` FROM `" . hesk_dbEscape($hesk_settings['db_pfix']) . "statuses`\n WHERE `IsNewTicketStatus` = 1 OR `IsCustomerReplyStatus` = 1 OR `IsStaffReopenedStatus` = 1) ORDER BY `owner` DESC, `priority` ASC LIMIT 1");
if (hesk_dbNumRows($res) == 1) {
$row = hesk_dbFetchAssoc($res);
$_SESSION['HESK_MESSAGE'] .= '<br /><br />' . $hesklang['rssn'];
header('Location: admin_ticket.php?track=' . $row['trackid'] . '&Refresh=' . rand(10000, 99999));
} else {
header('Location: admin_main.php');
}
} else {
header('Location: admin_ticket.php?track=' . $ticket['trackid'] . '&Refresh=' . rand(10000, 99999));
}
exit;
