mirror of
https://github.com/itflow-org/itflow
synced 2026-11-11 15:07:24 +00:00
177 lines
10 KiB
PHP
177 lines
10 KiB
PHP
<?php
|
||
|
||
/*
|
||
* Ticket Templates - for each template in the period: tickets raised from it
|
||
* and how (by agents, from the client portal's request forms, by recurring
|
||
* tickets or project templates), resolved, still open now, how long they
|
||
* typically take to resolve and the time logged on them; against the period
|
||
* before. Every current template is listed, so the ones not used show too.
|
||
* One client's or one agent's tickets only when picked. Tickets remember
|
||
* their template from version 3.1.9 of the database on.
|
||
*/
|
||
|
||
require_once "includes/inc_report.php";
|
||
reportEnforce('ticket_templates');
|
||
|
||
$period = reportPeriod('last_90');
|
||
$client_id = reportClientId();
|
||
$agent_id = reportAgentId();
|
||
$filter = reportTicketSql($client_id, $agent_id) . " AND tickets.ticket_template_id > 0";
|
||
$opened_in = reportRangeSql('ticket_created_at', $period);
|
||
|
||
// Every current template, and archived ones with tickets in the period
|
||
$templates = [];
|
||
$sql = mysqli_query($mysqli, "SELECT ticket_template_id, ticket_template_name, ticket_template_archived_at, ticket_template_portal FROM ticket_templates
|
||
WHERE ticket_template_archived_at IS NULL OR ticket_template_id IN (SELECT tickets.ticket_template_id FROM tickets WHERE $filter AND $opened_in)");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
$templates[intval($row['ticket_template_id'])] = ['id' => intval($row['ticket_template_id']), 'template' => $row['ticket_template_name'], 'archived' => !empty($row['ticket_template_archived_at']),
|
||
'portal_form' => intval($row['ticket_template_portal']) === 1, 'raised' => 0, 'agent' => 0, 'portal' => 0, 'recurring' => 0, 'project' => 0,
|
||
'resolved' => 0, 'open_now' => 0, 'seconds' => 0, 'resolve' => null, 'prev' => 0, 'last' => null];
|
||
}
|
||
|
||
// Raised in the period, by how
|
||
$sql = mysqli_query($mysqli, "SELECT ticket_template_id AS id, ticket_source AS source, COUNT(ticket_id) AS n FROM tickets WHERE $filter AND $opened_in GROUP BY ticket_template_id, ticket_source");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
$id = intval($row['id']);
|
||
if (!isset($templates[$id])) {
|
||
continue;
|
||
}
|
||
$how = ['Portal' => 'portal', 'Recurring' => 'recurring', 'Project' => 'project'][$row['source']] ?? 'agent';
|
||
$templates[$id][$how] += intval($row['n']);
|
||
$templates[$id]['raised'] += intval($row['n']);
|
||
}
|
||
$counts = function ($sql_where, $key) use ($mysqli, $filter, &$templates) {
|
||
$sql = mysqli_query($mysqli, "SELECT ticket_template_id AS id, COUNT(ticket_id) AS n FROM tickets WHERE $filter AND $sql_where GROUP BY ticket_template_id");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
if (isset($templates[intval($row['id'])])) {
|
||
$templates[intval($row['id'])][$key] = intval($row['n']);
|
||
}
|
||
}
|
||
};
|
||
$counts(reportRangeSql('ticket_resolved_at', $period), 'resolved');
|
||
$counts(TICKET_OPEN_SQL, 'open_now');
|
||
$counts(reportRangeSql('ticket_created_at', $period, true), 'prev');
|
||
|
||
// Time logged on them in the period, and when each was last used
|
||
$sql = mysqli_query($mysqli, "SELECT tickets.ticket_template_id AS id, SUM(TIME_TO_SEC(ticket_reply_time_worked)) AS s
|
||
FROM ticket_replies JOIN tickets ON ticket_reply_ticket_id = ticket_id
|
||
WHERE $filter AND ticket_reply_archived_at IS NULL AND " . reportRangeSql('ticket_reply_created_at', $period) . " GROUP BY tickets.ticket_template_id");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
if (isset($templates[intval($row['id'])])) {
|
||
$templates[intval($row['id'])]['seconds'] = intval($row['s']);
|
||
}
|
||
}
|
||
$sql = mysqli_query($mysqli, "SELECT ticket_template_id AS id, MAX(ticket_created_at) AS last FROM tickets WHERE $filter GROUP BY ticket_template_id");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
if (isset($templates[intval($row['id'])])) {
|
||
$templates[intval($row['id'])]['last'] = $row['last'];
|
||
}
|
||
}
|
||
|
||
// The typical (middle) time to resolve
|
||
$times = [];
|
||
$sql = mysqli_query($mysqli, "SELECT ticket_template_id AS id, TIMESTAMPDIFF(SECOND, ticket_created_at, ticket_resolved_at) AS s FROM tickets
|
||
WHERE $filter AND " . reportRangeSql('ticket_resolved_at', $period));
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
$times[intval($row['id'])][] = floatval($row['s']);
|
||
}
|
||
foreach ($templates as $id => &$row) {
|
||
$row['resolve'] = reportMedian($times[$id] ?? []);
|
||
$row['change'] = reportChange($row['raised'], $row['prev']);
|
||
}
|
||
unset($row);
|
||
uasort($templates, function ($a, $b) {
|
||
return $b['raised'] <=> $a['raised'] ?: $b['open_now'] <=> $a['open_now'] ?: strcasecmp($a['template'], $b['template']);
|
||
});
|
||
$templates = array_values($templates);
|
||
|
||
$admin = !empty($session_is_admin);
|
||
$columns = [
|
||
'template' => ['label' => 'Template', 'html' => function ($row) use ($admin) {
|
||
$name = escapeHtml($row['template']);
|
||
return ($admin ? '<a href="/admin/ticket_template.php?ticket_template_id=' . $row['id'] . '">' . $name . '</a>' : $name)
|
||
. ($row['archived'] ? ' <span class="badge text-bg-light border fw-normal">Archived</span>' : '')
|
||
. ($row['portal_form'] ? ' <i class="fas fa-door-open text-body-tertiary ms-1" title="Offered in the client portal"></i>' : '');
|
||
}],
|
||
'raised' => ['label' => 'Raised', 'type' => 'count', 'class' => 'fw-semibold'],
|
||
'change' => ['label' => 'Change', 'type' => 'percent', 'csv' => false, 'html' => function ($row) {
|
||
return $row['change'] === null ? '<span class="itflow-report-zero">–</span>' : '<span class="small">' . reportChangeHtml($row['raised'], $row['prev'], 'up') . '</span>';
|
||
}],
|
||
'agent' => ['label' => 'By agents', 'type' => 'count'],
|
||
'portal' => ['label' => 'From the portal', 'type' => 'count'],
|
||
'recurring' => ['label' => 'Recurring', 'type' => 'count'],
|
||
'project' => ['label' => 'Projects', 'type' => 'count'],
|
||
'resolved' => ['label' => 'Resolved', 'type' => 'count'],
|
||
'open_now' => ['label' => 'Open now', 'type' => 'count'],
|
||
'resolve' => ['label' => 'To resolve', 'type' => 'duration', 'title' => 'Typical (middle) time to resolve'],
|
||
'seconds' => ['label' => 'Time logged', 'type' => 'hours'],
|
||
'last' => ['label' => 'Last used', 'type' => 'date', 'html' => function ($row) {
|
||
return $row['last'] ? '<span class="small" title="' . escapeHtml($row['last']) . '">' . timeAgo($row['last']) . '</span>' : '<span class="itflow-report-zero">Never</span>';
|
||
}],
|
||
];
|
||
|
||
$totals = ['template' => 'Total'];
|
||
foreach (['raised', 'agent', 'portal', 'recurring', 'project', 'resolved', 'open_now', 'seconds'] as $key) {
|
||
$totals[$key] = array_sum(array_column($templates, $key));
|
||
}
|
||
$totals['resolve'] = reportMedian($times ? array_merge(...array_values($times)) : []);
|
||
|
||
if (reportExporting()) {
|
||
reportCsv('Ticket Templates ' . $period['start'] . ' to ' . $period['end'], $columns, $templates, $totals);
|
||
}
|
||
|
||
require_once "includes/inc_report_layout.php";
|
||
|
||
// All the tickets raised in the period - from templates or not - for the share
|
||
$all_raised = intval(mysqli_fetch_row(mysqli_query($mysqli, "SELECT COUNT(ticket_id) FROM tickets WHERE " . reportTicketSql($client_id, $agent_id) . " AND $opened_in"))[0]);
|
||
$current = array_filter($templates, function ($t) { return !$t['archived']; });
|
||
$used = count(array_filter($current, function ($t) { return $t['raised'] > 0; }));
|
||
$unused = count($current) - $used;
|
||
$top = array_slice(array_filter($templates, function ($t) { return $t['raised'] > 0; }), 0, 12);
|
||
|
||
?>
|
||
|
||
<div class="itflow-report">
|
||
|
||
<?php reportHeader('ticket_templates', [
|
||
'period' => $period,
|
||
'years' => reportPastYears("SELECT DISTINCT YEAR(ticket_created_at) AS y FROM tickets WHERE ticket_template_id > 0"),
|
||
'client' => $client_id,
|
||
'agent' => $agent_id,
|
||
'print_meta' => $client_id ? reportClientName($client_id) : '',
|
||
]); ?>
|
||
|
||
<?= reportStats([
|
||
['label' => 'Raised from templates', 'value' => reportCount($totals['raised']), 'sub' => $all_raised ? reportPercent(reportShare($totals['raised'], $all_raised)) . ' <span class="text-body-tertiary">of all tickets raised</span>' : ''],
|
||
['label' => 'From the client portal', 'value' => reportCount($totals['portal']), 'sub' => '<span class="text-body-tertiary">Asked for on its request forms</span>'],
|
||
['label' => 'Templates used', 'value' => $used . ' of ' . count($current), 'sub' => $unused ? $unused . ' <span class="text-body-tertiary">not used in this period</span>' : '<span class="text-body-tertiary">Every one</span>'],
|
||
['label' => 'Most used', 'value' => $top ? $top[0]['template'] : '-', 'sub' => $top ? $top[0]['raised'] . ' <span class="text-body-tertiary">tickets · ' . reportPercent(reportShare($top[0]['raised'], $totals['raised'])) . ' of them</span>' : ''],
|
||
]) ?>
|
||
|
||
<?php if ($top) { ?>
|
||
<?= reportSectionOpen('Most used', 'How they were raised') ?>
|
||
<div class="card-body">
|
||
<?php
|
||
$series = [];
|
||
foreach (['agent' => ['By agents', 'accent'], 'portal' => ['From the portal', 'success'], 'recurring' => ['Recurring', 'info'], 'project' => ['Projects', 'purple']] as $key => [$label, $tone]) {
|
||
$series[] = ['label' => $label, 'tone' => $tone, 'stack' => 'how', 'data' => array_column($top, $key)];
|
||
}
|
||
echo reportChart(['type' => 'bar', 'horizontal' => true, 'stacked' => true, 'labels' => array_column($top, 'template'), 'series' => $series, 'format' => 'count'], '', 'Tickets raised per template, by how');
|
||
?>
|
||
</div>
|
||
</div>
|
||
<?php } ?>
|
||
|
||
<?= reportSectionOpen('By template', 'Most used first, then the ones not used', 'itflow-report-long') ?>
|
||
<?= reportTable($columns, $templates, ['empty' => 'There are no ticket templates yet.', 'totals' => $totals]) ?>
|
||
<div class="card-footer small text-body-secondary">
|
||
Raised counts tickets raised in the period from each template; resolved counts those resolved in it, whenever raised. To resolve is the middle ticket's time, so a few long ones do not skew it.
|
||
Time logged is the time on their replies in the period. Tickets remember their template from the update that brought ticket templates' setup in - older ones are not counted.
|
||
</div>
|
||
</div>
|
||
|
||
</div>
|
||
|
||
<?php require_once "../../includes/footer.php"; ?>
|
||
<script src="<?= staticFileUrl('/js/reports.js') ?>"></script>
|