mirror of
https://github.com/itflow-org/itflow
synced 2026-11-11 15:07:24 +00:00
194 lines
12 KiB
PHP
194 lines
12 KiB
PHP
<?php
|
||
|
||
/*
|
||
* Tickets by Client - for each client in the period: tickets opened (and how
|
||
* many were urgent or high), resolved, still open now, time logged, how
|
||
* quickly they typically got a first reply and a resolution, SLA response
|
||
* compliance and how they rated their tickets. Busiest first; one agent's
|
||
* tickets only when an agent is picked.
|
||
*/
|
||
|
||
require_once "includes/inc_report.php";
|
||
reportEnforce('ticket_by_client');
|
||
|
||
$period = reportPeriod('last_90');
|
||
$agent_id = reportAgentId();
|
||
$filter = reportTicketSql(0, $agent_id);
|
||
$opened_in = reportRangeSql('ticket_created_at', $period);
|
||
|
||
$clients = [];
|
||
$client = function ($row) use (&$clients) {
|
||
$id = intval($row['client_id']);
|
||
$clients[$id] = $clients[$id] ?? ['client_id' => $id, 'client' => $row['client_name'], 'abbr' => $row['client_abbreviation'],
|
||
'opened' => 0, 'urgent' => 0, 'resolved' => 0, 'open_now' => 0, 'seconds' => 0, 'response' => null, 'resolve' => null,
|
||
'sla_met' => 0, 'sla_missed' => 0, 'good' => 0, 'bad' => 0, 'prev' => 0];
|
||
return $id;
|
||
};
|
||
|
||
$sql = mysqli_query($mysqli, "SELECT client_id, client_name, client_abbreviation, COUNT(ticket_id) AS opened,
|
||
SUM(ticket_priority IN ('Urgent', 'High')) AS urgent,
|
||
SUM(ticket_response_sla_met = 1) AS sla_met, SUM(ticket_response_sla_met = 0) AS sla_missed
|
||
FROM tickets JOIN clients ON ticket_client_id = client_id
|
||
WHERE $filter AND $opened_in GROUP BY client_id, client_name, client_abbreviation");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
$id = $client($row);
|
||
$clients[$id]['opened'] = intval($row['opened']);
|
||
$clients[$id]['urgent'] = intval($row['urgent']);
|
||
$clients[$id]['sla_met'] = intval($row['sla_met']);
|
||
$clients[$id]['sla_missed'] = intval($row['sla_missed']);
|
||
}
|
||
$sql = mysqli_query($mysqli, "SELECT client_id, client_name, client_abbreviation, COUNT(ticket_id) AS n
|
||
FROM tickets JOIN clients ON ticket_client_id = client_id
|
||
WHERE $filter AND " . reportRangeSql('ticket_resolved_at', $period) . " GROUP BY client_id, client_name, client_abbreviation");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
$clients[$client($row)]['resolved'] = intval($row['n']);
|
||
}
|
||
$sql = mysqli_query($mysqli, "SELECT client_id, client_name, client_abbreviation, COUNT(ticket_id) AS n
|
||
FROM tickets JOIN clients ON ticket_client_id = client_id
|
||
WHERE $filter AND " . TICKET_OPEN_SQL . " GROUP BY client_id, client_name, client_abbreviation");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
$clients[$client($row)]['open_now'] = intval($row['n']);
|
||
}
|
||
$sql = mysqli_query($mysqli, "SELECT client_id, client_name, client_abbreviation, SUM(TIME_TO_SEC(ticket_reply_time_worked)) AS s
|
||
FROM ticket_replies JOIN tickets ON ticket_reply_ticket_id = ticket_id JOIN clients ON ticket_client_id = client_id
|
||
WHERE $filter AND ticket_reply_archived_at IS NULL AND " . reportRangeSql('ticket_reply_created_at', $period) . " GROUP BY client_id, client_name, client_abbreviation");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
$clients[$client($row)]['seconds'] = intval($row['s']);
|
||
}
|
||
$sql = mysqli_query($mysqli, "SELECT ticket_client_id AS client_id, SUM(ticket_feedback = 'Good') AS good, SUM(ticket_feedback = 'Bad') AS bad
|
||
FROM tickets WHERE $filter AND " . reportRangeSql('ticket_closed_at', $period) . " GROUP BY ticket_client_id");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
if (isset($clients[intval($row['client_id'])])) {
|
||
$clients[intval($row['client_id'])]['good'] = intval($row['good']);
|
||
$clients[intval($row['client_id'])]['bad'] = intval($row['bad']);
|
||
}
|
||
}
|
||
$sql = mysqli_query($mysqli, "SELECT ticket_client_id AS client_id, COUNT(ticket_id) AS n FROM tickets WHERE $filter AND " . reportRangeSql('ticket_created_at', $period, true) . " GROUP BY ticket_client_id");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
if (isset($clients[intval($row['client_id'])])) {
|
||
$clients[intval($row['client_id'])]['prev'] = intval($row['n']);
|
||
}
|
||
}
|
||
|
||
// The typical (middle) first reply and resolution, per client
|
||
$times = ['response' => [], 'resolve' => []];
|
||
$sql = mysqli_query($mysqli, "SELECT ticket_client_id AS client_id, TIMESTAMPDIFF(SECOND, ticket_created_at, ticket_first_response_at) AS s
|
||
FROM tickets WHERE $filter AND $opened_in AND ticket_first_response_at IS NOT NULL");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
$times['response'][intval($row['client_id'])][] = floatval($row['s']);
|
||
}
|
||
$sql = mysqli_query($mysqli, "SELECT ticket_client_id AS client_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['resolve'][intval($row['client_id'])][] = floatval($row['s']);
|
||
}
|
||
foreach ($clients as $id => &$row) {
|
||
$row['response'] = reportMedian($times['response'][$id] ?? []);
|
||
$row['resolve'] = reportMedian($times['resolve'][$id] ?? []);
|
||
$row['sla'] = reportShare($row['sla_met'], $row['sla_met'] + $row['sla_missed']);
|
||
$row['csat'] = reportShare($row['good'], $row['good'] + $row['bad']);
|
||
$row['change'] = reportChange($row['opened'], $row['prev']);
|
||
}
|
||
unset($row);
|
||
uasort($clients, function ($a, $b) { return $b['opened'] <=> $a['opened'] ?: $b['open_now'] <=> $a['open_now']; });
|
||
$clients = array_values($clients);
|
||
|
||
$columns = [
|
||
'client' => ['label' => 'Client', 'html' => function ($row) {
|
||
return reportClientCell($row['client_id'], $row['client'], $row['abbr'], 'tickets.php');
|
||
}],
|
||
'opened' => ['label' => 'Opened', '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['opened'], $row['prev'], 'down') . '</span>';
|
||
}],
|
||
'urgent' => ['label' => 'Urgent or high', 'type' => 'count', 'html' => function ($row) {
|
||
return $row['urgent'] ? '<span class="text-danger-emphasis">' . $row['urgent'] . '</span>' : '<span class="itflow-report-zero">–</span>';
|
||
}],
|
||
'resolved' => ['label' => 'Resolved', 'type' => 'count'],
|
||
'open_now' => ['label' => 'Open now', 'type' => 'count', 'html' => function ($row) {
|
||
return $row['open_now'] ? '<a class="itflow-report-amount" href="/agent/tickets.php?client_id=' . $row['client_id'] . '">' . $row['open_now'] . '</a>' : '<span class="itflow-report-zero">–</span>';
|
||
}],
|
||
'seconds' => ['label' => 'Time logged', 'type' => 'hours'],
|
||
'response' => ['label' => 'First reply', 'type' => 'duration', 'title' => 'Typical (middle) time to the first reply'],
|
||
'resolve' => ['label' => 'To resolve', 'type' => 'duration', 'title' => 'Typical (middle) time to resolve'],
|
||
'sla' => ['label' => 'SLA response', 'type' => 'percent', 'html' => function ($row) {
|
||
return reportRateHtml($row['sla']);
|
||
}],
|
||
'csat' => ['label' => 'Satisfaction', 'type' => 'percent', 'title' => 'Good ratings out of all good and bad', 'html' => function ($row) {
|
||
return $row['csat'] === null ? '<span class="itflow-report-zero">–</span>' : reportRateHtml($row['csat'], 90, 75) . ' <span class="small text-body-tertiary">' . ($row['good'] + $row['bad']) . '</span>';
|
||
}],
|
||
];
|
||
|
||
$totals = ['client' => 'Total'];
|
||
foreach (['opened', 'urgent', 'resolved', 'open_now', 'seconds'] as $key) {
|
||
$totals[$key] = array_sum(array_column($clients, $key));
|
||
}
|
||
$all_response = reportMedian(array_merge(...array_values($times['response'] ?: [[]])));
|
||
$all_resolve = reportMedian(array_merge(...array_values($times['resolve'] ?: [[]])));
|
||
$totals['response'] = $all_response;
|
||
$totals['resolve'] = $all_resolve;
|
||
$totals['sla'] = reportShare(array_sum(array_column($clients, 'sla_met')), array_sum(array_column($clients, 'sla_met')) + array_sum(array_column($clients, 'sla_missed')));
|
||
$totals['csat'] = reportShare(array_sum(array_column($clients, 'good')), array_sum(array_column($clients, 'good')) + array_sum(array_column($clients, 'bad')));
|
||
|
||
if (reportExporting()) {
|
||
reportCsv('Tickets by Client ' . $period['start'] . ' to ' . $period['end'], $columns, $clients, $totals);
|
||
}
|
||
|
||
require_once "includes/inc_report_layout.php";
|
||
|
||
$top = array_slice(array_filter($clients, function ($c) { return $c['opened'] > 0; }), 0, 12);
|
||
$busiest = $clients[0] ?? null;
|
||
$with_tickets = count(array_filter($clients, function ($c) { return $c['opened'] > 0; }));
|
||
|
||
?>
|
||
|
||
<div class="itflow-report">
|
||
|
||
<?php reportHeader('ticket_by_client', [
|
||
'period' => $period,
|
||
'years' => reportPastYears("SELECT DISTINCT YEAR(ticket_created_at) AS y FROM tickets"),
|
||
'agent' => $agent_id,
|
||
]); ?>
|
||
|
||
<?= reportStats([
|
||
['label' => 'Tickets opened', 'value' => reportCount($totals['opened']), 'sub' => reportCount($with_tickets) . ' <span class="text-body-tertiary">clients raised them</span>'],
|
||
['label' => 'Average per client', 'value' => $with_tickets ? number_format($totals['opened'] / $with_tickets, 1) : '-', 'sub' => '<span class="text-body-tertiary">Of those who raised any</span>'],
|
||
['label' => 'Busiest', 'value' => $busiest && $busiest['opened'] ? $busiest['client'] : '-', 'sub' => $busiest && $busiest['opened'] ? $busiest['opened'] . ' <span class="text-body-tertiary">tickets · ' . reportPercent(reportShare($busiest['opened'], $totals['opened'])) . ' of all</span>' : ''],
|
||
['label' => 'Time logged', 'value' => reportHours($totals['seconds']) . ' h', 'sub' => $with_tickets ? reportHours($totals['seconds'] / $with_tickets) . ' h <span class="text-body-tertiary">per client</span>' : ''],
|
||
]) ?>
|
||
|
||
<?php if ($top) { ?>
|
||
<?= reportSectionOpen('Clients raising the most', 'By priority') ?>
|
||
<div class="card-body">
|
||
<?php
|
||
// Each client's tickets split by priority
|
||
$ids = array_column($top, 'client_id');
|
||
$split = [];
|
||
$sql = mysqli_query($mysqli, "SELECT ticket_client_id AS client_id, ticket_priority AS p, COUNT(ticket_id) AS n FROM tickets
|
||
WHERE $filter AND $opened_in AND ticket_client_id IN (" . implode(',', array_map('intval', $ids)) . ") GROUP BY ticket_client_id, ticket_priority");
|
||
while ($row = mysqli_fetch_assoc($sql)) {
|
||
$split[$row['p']][intval($row['client_id'])] = intval($row['n']);
|
||
}
|
||
$series = [];
|
||
foreach (['Urgent' => 'dark', 'High' => 'danger', 'Medium' => 'warning', 'Low' => 'info'] as $priority => $tone) {
|
||
$series[] = ['label' => $priority, 'tone' => $tone, 'stack' => 'p', 'data' => array_map(function ($id) use ($split, $priority) { return $split[$priority][$id] ?? 0; }, $ids)];
|
||
}
|
||
echo reportChart(['type' => 'bar', 'horizontal' => true, 'stacked' => true, 'labels' => array_column($top, 'client'), 'series' => $series, 'format' => 'count'], '', 'Tickets opened per client, by priority');
|
||
?>
|
||
</div>
|
||
</div>
|
||
<?php } ?>
|
||
|
||
<?= reportSectionOpen('By client', 'Busiest first', 'itflow-report-long') ?>
|
||
<?= reportTable($columns, $clients, ['empty' => 'No tickets in this period.', 'totals' => $totals, 'totals_html' => ['sla' => reportRateHtml($totals['sla']), 'csat' => $totals['csat'] === null ? '' : reportRateHtml($totals['csat'], 90, 75)]]) ?>
|
||
<div class="card-footer small text-body-secondary">
|
||
Opened counts tickets raised in the period; resolved counts those resolved in it, whenever raised. Times run on the clock and are the middle ticket's, so a few long ones do not skew them.
|
||
SLA response is the share of first-reply targets met. Satisfaction is from client ratings on tickets closed in the period.
|
||
</div>
|
||
</div>
|
||
|
||
</div>
|
||
|
||
<?php require_once "../../includes/footer.php"; ?>
|
||
<script src="<?= staticFileUrl('/js/reports.js') ?>"></script>
|