261 lines
9.4 KiB
JavaScript
261 lines
9.4 KiB
JavaScript
import { randomUUID } from 'crypto';
|
||
import db from '../../db.js';
|
||
import { requireCrmEdit } from '../../middleware/auth.js';
|
||
import { badRequest, UUID } from '../../lib/http.js';
|
||
import { mergeAttachmentEventsForApi } from '../../lib/ticket-events-merge.js';
|
||
import {
|
||
mapTicket,
|
||
ticketJoinSelect,
|
||
ticketLastActivityExpr,
|
||
ticketSlaDueExpr,
|
||
} from '../../lib/mappers.js';
|
||
|
||
function parseSlaDaysForCreate(v) {
|
||
if (v === undefined || v === null || v === '') return null;
|
||
const n = Number(v);
|
||
if (!Number.isInteger(n) || n < 1 || n > 5) return undefined;
|
||
return n;
|
||
}
|
||
|
||
/** Nur wenn Key gesetzt: null = Standard (2 Tage), 1–5 = Tage. */
|
||
function parseSlaDaysForUpdate(v) {
|
||
if (v === null || v === '') return null;
|
||
const n = Number(v);
|
||
if (!Number.isInteger(n) || n < 1 || n > 5) return undefined;
|
||
return n;
|
||
}
|
||
|
||
function slaDaysEqual(a, b) {
|
||
const na = a == null || a === '' ? null : Number(a);
|
||
const nb = b == null || b === '' ? null : Number(b);
|
||
return na === nb;
|
||
}
|
||
|
||
const ticketListOrderBy = `
|
||
ORDER BY
|
||
CASE WHEN t.status IN ('OPEN','WAITING') AND datetime('now') > ${ticketSlaDueExpr} THEN 0 ELSE 1 END ASC,
|
||
CASE WHEN t.status IN ('OPEN','WAITING') AND datetime('now') > ${ticketSlaDueExpr} THEN ${ticketSlaDueExpr} ELSE '9999-12-31' END ASC,
|
||
${ticketLastActivityExpr} DESC`;
|
||
|
||
function userLabelFromRow(row) {
|
||
if (!row) return '—';
|
||
const fn = [row.firstname, row.lastname].filter(Boolean).join(' ').trim();
|
||
return fn || row.username || row.id;
|
||
}
|
||
|
||
export function registerTicketRoutes(api) {
|
||
api.get('/tickets', (req, res) => {
|
||
const { status, priority, machineId, open, assignedTo } = req.query;
|
||
const cond = ['1=1'];
|
||
const params = [];
|
||
const openFilter = open === '1' || open === 'true';
|
||
if (openFilter) {
|
||
cond.push("t.status IN ('OPEN', 'WAITING')");
|
||
} else if (status) {
|
||
cond.push('t.status = ?');
|
||
params.push(status);
|
||
}
|
||
if (priority) {
|
||
cond.push('t.priority = ?');
|
||
params.push(priority);
|
||
}
|
||
if (machineId) {
|
||
cond.push('t.machine_id = ?');
|
||
params.push(machineId);
|
||
}
|
||
if (assignedTo === 'me' && req.session?.userId) {
|
||
cond.push('t.assigned_user_id = ?');
|
||
params.push(req.session.userId);
|
||
} else if (assignedTo === 'not_me' && req.session?.userId) {
|
||
cond.push(
|
||
'(t.assigned_user_id IS NULL OR t.assigned_user_id <> ?)',
|
||
);
|
||
params.push(req.session.userId);
|
||
}
|
||
const sql = `${ticketJoinSelect} WHERE ${cond.join(' AND ')} ${ticketListOrderBy}`;
|
||
const rows = db.prepare(sql).all(...params);
|
||
res.json(rows.map(mapTicket));
|
||
});
|
||
|
||
api.post('/tickets', requireCrmEdit, (req, res) => {
|
||
const { machineId, title, description, status, priority, slaDays } =
|
||
req.body || {};
|
||
if (!machineId || !title || !description) {
|
||
return badRequest(res, 'Pflichtfelder fehlen.');
|
||
}
|
||
const m = db
|
||
.prepare('SELECT 1 AS ok FROM machines WHERE id = ?')
|
||
.get(machineId);
|
||
if (!m) return res.status(404).json({ message: 'Nicht gefunden' });
|
||
const st = status || 'OPEN';
|
||
const pr = priority || 'MEDIUM';
|
||
const sd = parseSlaDaysForCreate(slaDays);
|
||
if (sd === undefined) {
|
||
return badRequest(res, 'slaDays ungültig (1–5 oder weglassen für Standard).');
|
||
}
|
||
const tid = randomUUID();
|
||
db.prepare(
|
||
`INSERT INTO tickets (id, machine_id, title, description, status, priority, sla_days, sla_anchor_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))`,
|
||
).run(tid, machineId, title, description, st, pr, sd);
|
||
const full = db
|
||
.prepare(`${ticketJoinSelect} WHERE t.id = ?`)
|
||
.get(tid);
|
||
res.status(201).json(mapTicket(full));
|
||
});
|
||
|
||
api.get('/tickets/:id/events', (req, res) => {
|
||
const { id } = req.params;
|
||
if (!UUID.test(id)) return res.status(404).json({ message: 'Nicht gefunden' });
|
||
const ex = db.prepare('SELECT 1 AS ok FROM tickets WHERE id = ?').get(id);
|
||
if (!ex) return res.status(404).json({ message: 'Nicht gefunden' });
|
||
const rows = db
|
||
.prepare(
|
||
`SELECT * FROM events WHERE ticket_id = ?
|
||
ORDER BY CASE WHEN type = 'ATTACHMENT' THEN 1 ELSE 0 END ASC,
|
||
created_at DESC`,
|
||
)
|
||
.all(id);
|
||
const eventIds = rows.map((r) => r.id);
|
||
const byEvent = new Map();
|
||
if (eventIds.length > 0) {
|
||
const ph = eventIds.map(() => '?').join(',');
|
||
const attRows = db
|
||
.prepare(
|
||
`SELECT * FROM ticket_attachments WHERE event_id IN (${ph}) ORDER BY created_at ASC`,
|
||
)
|
||
.all(...eventIds);
|
||
for (const a of attRows) {
|
||
if (!byEvent.has(a.event_id)) byEvent.set(a.event_id, []);
|
||
byEvent.get(a.event_id).push(a);
|
||
}
|
||
}
|
||
res.json(mergeAttachmentEventsForApi(rows, byEvent, db));
|
||
});
|
||
|
||
api.get('/tickets/:id', (req, res) => {
|
||
const { id } = req.params;
|
||
if (!UUID.test(id)) return res.status(404).json({ message: 'Nicht gefunden' });
|
||
const row = db.prepare(`${ticketJoinSelect} WHERE t.id = ?`).get(id);
|
||
if (!row) return res.status(404).json({ message: 'Nicht gefunden' });
|
||
res.json(mapTicket(row));
|
||
});
|
||
|
||
api.put('/tickets/:id', requireCrmEdit, (req, res) => {
|
||
const { id } = req.params;
|
||
if (!UUID.test(id)) return res.status(404).json({ message: 'Nicht gefunden' });
|
||
const cur = db.prepare('SELECT * FROM tickets WHERE id = ?').get(id);
|
||
if (!cur) return res.status(404).json({ message: 'Nicht gefunden' });
|
||
const b = req.body || {};
|
||
const next = {
|
||
title: b.title ?? cur.title,
|
||
description: b.description ?? cur.description,
|
||
status: b.status ?? cur.status,
|
||
priority: b.priority ?? cur.priority,
|
||
};
|
||
|
||
let nextSlaDays = cur.sla_days != null ? cur.sla_days : null;
|
||
let resetSlaAnchor = false;
|
||
if (Object.prototype.hasOwnProperty.call(b, 'slaDays')) {
|
||
const parsed = parseSlaDaysForUpdate(b.slaDays);
|
||
if (parsed === undefined) {
|
||
return badRequest(
|
||
res,
|
||
'slaDays ungültig (1–5 oder leer für Standard).',
|
||
);
|
||
}
|
||
nextSlaDays = parsed;
|
||
resetSlaAnchor = !slaDaysEqual(parsed, cur.sla_days);
|
||
}
|
||
|
||
const lines = [];
|
||
if (b.status !== undefined && b.status !== cur.status) {
|
||
lines.push(`Status: ${cur.status} → ${b.status}`);
|
||
}
|
||
if (b.priority !== undefined && b.priority !== cur.priority) {
|
||
lines.push(`Priorität: ${cur.priority} → ${b.priority}`);
|
||
}
|
||
if (b.title !== undefined && b.title !== cur.title) lines.push('Titel geändert');
|
||
if (b.description !== undefined && b.description !== cur.description) {
|
||
lines.push('Beschreibung geändert');
|
||
}
|
||
if (
|
||
Object.prototype.hasOwnProperty.call(b, 'slaDays') &&
|
||
!slaDaysEqual(nextSlaDays, cur.sla_days)
|
||
) {
|
||
const label = (d) =>
|
||
d == null ? 'Standard (2 Tage)' : `${d} Tag(e)`;
|
||
lines.push(
|
||
`Fälligkeit: ${label(cur.sla_days)} → ${label(nextSlaDays)}`,
|
||
);
|
||
}
|
||
|
||
let nextAssignedUserId = cur.assigned_user_id ?? null;
|
||
if (Object.prototype.hasOwnProperty.call(b, 'assignedUserId')) {
|
||
const raw = b.assignedUserId;
|
||
if (raw === null || raw === undefined || raw === '') {
|
||
nextAssignedUserId = null;
|
||
} else if (!UUID.test(String(raw))) {
|
||
return badRequest(res, 'assignedUserId ungültig.');
|
||
} else {
|
||
const u = db
|
||
.prepare(
|
||
'SELECT id, username, firstname, lastname FROM users WHERE id = ? AND active = 1',
|
||
)
|
||
.get(String(raw));
|
||
if (!u) {
|
||
return badRequest(res, 'Zugewiesener Benutzer nicht gefunden oder inaktiv.');
|
||
}
|
||
nextAssignedUserId = u.id;
|
||
}
|
||
}
|
||
if (nextAssignedUserId !== (cur.assigned_user_id ?? null)) {
|
||
const prevU = cur.assigned_user_id
|
||
? db
|
||
.prepare(
|
||
'SELECT id, username, firstname, lastname FROM users WHERE id = ?',
|
||
)
|
||
.get(cur.assigned_user_id)
|
||
: null;
|
||
const nextU = nextAssignedUserId
|
||
? db
|
||
.prepare(
|
||
'SELECT id, username, firstname, lastname FROM users WHERE id = ?',
|
||
)
|
||
.get(nextAssignedUserId)
|
||
: null;
|
||
const fromLabel = prevU ? userLabelFromRow(prevU) : 'nicht zugewiesen';
|
||
const toLabel = nextU ? userLabelFromRow(nextU) : 'nicht zugewiesen';
|
||
lines.push(`Zuweisung: ${fromLabel} → ${toLabel}`);
|
||
}
|
||
|
||
if (lines.length > 0) {
|
||
const eid = randomUUID();
|
||
db.prepare(
|
||
`INSERT INTO events (id, ticket_id, type, description, callback_number, teamviewer_id, article_number, remote_duration_seconds, teamviewer_notes)
|
||
VALUES (?, ?, 'SYSTEM', ?, NULL, NULL, NULL, NULL, NULL)`,
|
||
).run(eid, id, lines.join('; '));
|
||
}
|
||
|
||
db.prepare(
|
||
`UPDATE tickets SET title = ?, description = ?, status = ?, priority = ?, sla_days = ?,
|
||
sla_anchor_at = CASE WHEN ? THEN datetime('now') ELSE sla_anchor_at END,
|
||
assigned_user_id = ?,
|
||
updated_at = datetime('now')
|
||
WHERE id = ?`,
|
||
).run(
|
||
next.title,
|
||
next.description,
|
||
next.status,
|
||
next.priority,
|
||
nextSlaDays,
|
||
resetSlaAnchor ? 1 : 0,
|
||
nextAssignedUserId,
|
||
id,
|
||
);
|
||
|
||
const row = db.prepare(`${ticketJoinSelect} WHERE t.id = ?`).get(id);
|
||
res.json(mapTicket(row));
|
||
});
|
||
}
|