Ticket Templates - tickets ready to raise: a subject, details and
* tasks, and how the ticket is set up (category, priority, billing, agent, due
* time and tags). Each row shows what it sets up, its tasks and their time,
* and how often it has been used. A template can be changed, copied as the
* start of another, archived - kept for the recurring tickets and project
* templates that use it, not offered for new tickets - or deleted. The work is
* in functions/ticket_template.php; the handlers are admin/post/ticket_template.php.
*/
// Default Column Sortby Filter
$sort = "ticket_template_name";
$order = "ASC";
require_once "includes/inc_all_admin.php";
// ?archived=1 is how older links asked for the archived ones
$show_archived = ($_GET['show'] ?? '') === 'archived' || !empty($_GET['archived']);
// Only these sort - an unknown column would take the page down
$sortable_columns = [
'ticket_template_name' => "ticket_template_name $order",
'task_count' => "task_count $order, ticket_template_name ASC",
'used_count' => "used_count $order, ticket_template_name ASC",
'last_used' => "last_used IS NULL, last_used $order, ticket_template_name ASC",
];
if (!isset($sortable_columns[$sort])) {
$sort = 'ticket_template_name';
}
$search = "(ticket_template_name LIKE '%$q%' OR ticket_template_description LIKE '%$q%' OR ticket_template_subject LIKE '%$q%')";
// Its use is the tickets raised from it that are not archived - as ticketTemplateUsage() counts them
$sql = mysqli_query($mysqli, "SELECT SQL_CALC_FOUND_ROWS ticket_templates.*, category_name, category_color, category_icon, user_name AS assignee_name,
(SELECT COUNT(*) FROM task_templates WHERE task_template_ticket_template_id = ticket_templates.ticket_template_id) AS task_count,
(SELECT COALESCE(SUM(task_template_completion_estimate), 0) FROM task_templates WHERE task_template_ticket_template_id = ticket_templates.ticket_template_id) AS task_minutes,
(SELECT COUNT(*) FROM recurring_tickets WHERE recurring_ticket_ticket_template_id = ticket_templates.ticket_template_id) AS recurring_count,
(SELECT COUNT(*) FROM project_template_ticket_templates WHERE project_template_ticket_templates.ticket_template_id = ticket_templates.ticket_template_id) AS project_count,
COALESCE(used.used_count, 0) AS used_count, COALESCE(used.open_count, 0) AS open_count, used.last_used
FROM ticket_templates
LEFT JOIN categories ON category_id = ticket_template_category_id AND ticket_template_category_id > 0
LEFT JOIN users ON user_id = ticket_template_assigned_to AND ticket_template_assigned_to > 0
LEFT JOIN (
SELECT ticket_template_id AS used_template_id, COUNT(*) AS used_count, SUM(ticket_closed_at IS NULL AND ticket_resolved_at IS NULL) AS open_count, MAX(ticket_created_at) AS last_used
FROM tickets WHERE ticket_template_id > 0 AND ticket_archived_at IS NULL GROUP BY ticket_template_id
) used ON used.used_template_id = ticket_templates.ticket_template_id
WHERE $search AND ticket_template_archived_at IS " . ($show_archived ? 'NOT NULL' : 'NULL') . "
ORDER BY {$sortable_columns[$sort]}, ticket_templates.ticket_template_id ASC
LIMIT $record_from, $record_to");
$num_rows = mysqli_fetch_row(mysqli_query($mysqli, "SELECT FOUND_ROWS()"));
$templates = [];
while ($row = mysqli_fetch_assoc($sql)) {
$templates[intval($row['ticket_template_id'])] = $row;
}
// What each asks for - its fill-ins, from its rows and the bracketed words in its text
$template_tasks = ticketTemplateTasksFor(array_keys($templates));
$template_fill_in_rows = ticketTemplateFillInRowsFor(array_keys($templates));
$tag_rows = [];
if ($templates) {
$sql_tags = mysqli_query($mysqli, "SELECT ticket_template_id, tags.* FROM ticket_template_tags JOIN tags ON tags.tag_id = ticket_template_tags.tag_id
WHERE ticket_template_id IN (" . implode(',', array_keys($templates)) . ") ORDER BY tag_name");
while ($row = mysqli_fetch_assoc($sql_tags)) {
$tag_rows[intval($row['ticket_template_id'])][] = $row;
}
}
$counts = mysqli_fetch_row(mysqli_query($mysqli, "SELECT COALESCE(SUM(ticket_template_archived_at IS NULL), 0), COALESCE(SUM(ticket_template_archived_at IS NOT NULL), 0) FROM ticket_templates WHERE $search"));
$sort_link = function ($column, $label) use ($url_query_strings_sort, $sort, $disp, $order_icon) {
return ''
. $label . ($sort === $column ? " $order_icon" : '') . '';
};
$csrf = $_SESSION['csrf_token'];
?>
'ticket templates',
'icon' => 'fa-life-ring',
'filtered' => $q !== '',
'archived' => $show_archived,
'views' => ['show'],
'text' => 'A ticket ready to raise - its subject, details and tasks, and how it is set up. [Client], [Contact], [Month] and the like are filled in when a ticket is raised.',
'actions' => [['label' => 'New Ticket Template', 'modal' => 'modals/ticket_template/ticket_template_add.php', 'size' => 'lg']],
];
require_once "../includes/filter_footer.php";
?>