339 lines
16 KiB
JavaScript
339 lines
16 KiB
JavaScript
// Timesheet API Routes
|
|
|
|
const { db } = require('../database');
|
|
const { requireAuth, requireVerwaltung } = require('../middleware/auth');
|
|
const { generatePDF } = require('../services/pdf-service');
|
|
|
|
// Routes registrieren
|
|
function registerTimesheetRoutes(app) {
|
|
// API: Stundenerfassung speichern
|
|
app.post('/api/timesheet/save', requireAuth, (req, res) => {
|
|
const {
|
|
date, start_time, end_time, break_minutes, notes,
|
|
activity1_desc, activity1_hours, activity1_project_number,
|
|
activity2_desc, activity2_hours, activity2_project_number,
|
|
activity3_desc, activity3_hours, activity3_project_number,
|
|
activity4_desc, activity4_hours, activity4_project_number,
|
|
activity5_desc, activity5_hours, activity5_project_number,
|
|
overtime_taken_hours, vacation_type, sick_status
|
|
} = req.body;
|
|
const userId = req.session.userId;
|
|
|
|
// Normalisiere end_time: Leere Strings werden zu null
|
|
const normalizedEndTime = (end_time && typeof end_time === 'string' && end_time.trim() !== '') ? end_time.trim() : (end_time || null);
|
|
const normalizedStartTime = (start_time && typeof start_time === 'string' && start_time.trim() !== '') ? start_time.trim() : (start_time || null);
|
|
|
|
// Normalisiere sick_status: Boolean oder 1/0 zu Boolean
|
|
const isSick = sick_status === true || sick_status === 1 || sick_status === 'true' || sick_status === '1';
|
|
|
|
// User-Daten laden (für Überstunden-Berechnung)
|
|
db.get('SELECT wochenstunden FROM users WHERE id = ?', [userId], (err, user) => {
|
|
if (err) {
|
|
console.error('Fehler beim Laden der User-Daten:', err);
|
|
return res.status(500).json({ error: 'Fehler beim Laden der User-Daten' });
|
|
}
|
|
|
|
const wochenstunden = user?.wochenstunden || 0;
|
|
const overtimeValue = overtime_taken_hours ? parseFloat(overtime_taken_hours) : 0;
|
|
const fullDayHours = wochenstunden > 0 ? wochenstunden / 5 : 0;
|
|
|
|
// Überstunden-Logik: Prüfe ob ganzer Tag oder weniger
|
|
let isFullDayOvertime = false;
|
|
if (overtimeValue > 0 && fullDayHours > 0 && Math.abs(overtimeValue - fullDayHours) < 0.01) {
|
|
isFullDayOvertime = true;
|
|
}
|
|
|
|
// Gesamtstunden berechnen (aus Start- und Endzeit, nicht aus Tätigkeiten)
|
|
// Wenn ganzer Tag Urlaub oder Krank, dann zählt dieser als 8 Stunden normale Arbeitszeit
|
|
let total_hours = 0;
|
|
let finalActivity1Desc = activity1_desc;
|
|
let finalActivity1Hours = parseFloat(activity1_hours) || 0;
|
|
let finalActivity2Desc = activity2_desc;
|
|
let finalActivity3Desc = activity3_desc;
|
|
let finalActivity4Desc = activity4_desc;
|
|
let finalActivity5Desc = activity5_desc;
|
|
let finalStartTime = normalizedStartTime;
|
|
let finalEndTime = normalizedEndTime;
|
|
|
|
// Überstunden-Logik: Bei vollem Tag Überstunden
|
|
if (isFullDayOvertime) {
|
|
total_hours = 0;
|
|
finalStartTime = null;
|
|
finalEndTime = null;
|
|
// Keine Tätigkeit setzen - Überstunden werden über overtime_taken_hours in der PDF angezeigt
|
|
} else if (vacation_type === 'full') {
|
|
total_hours = 8; // Ganzer Tag Urlaub = 8 Stunden normale Arbeitszeit
|
|
} else if (isSick) {
|
|
total_hours = 8; // Krank = 8 Stunden normale Arbeitszeit
|
|
finalActivity1Desc = 'Krank';
|
|
finalActivity1Hours = 8;
|
|
} else if (normalizedStartTime && normalizedEndTime) {
|
|
const start = new Date(`2000-01-01T${normalizedStartTime}`);
|
|
const end = new Date(`2000-01-01T${normalizedEndTime}`);
|
|
const diffMs = end - start;
|
|
total_hours = (diffMs / (1000 * 60 * 60)) - (break_minutes / 60);
|
|
// Bei halbem Tag Urlaub: total_hours bleibt die tatsächlich gearbeiteten Stunden
|
|
// Die 4 Stunden Urlaub werden nur in der Überstunden-Berechnung hinzugezählt
|
|
}
|
|
|
|
// Überstunden werden nicht mehr als Tätigkeit hinzugefügt
|
|
// Sie werden über overtime_taken_hours in der PDF angezeigt
|
|
|
|
// Prüfen ob Eintrag existiert - verwende den neuesten Eintrag falls mehrere existieren
|
|
db.get('SELECT id FROM timesheet_entries WHERE user_id = ? AND date = ? ORDER BY updated_at DESC, id DESC LIMIT 1',
|
|
[userId, date], (err, row) => {
|
|
if (row) {
|
|
// Update
|
|
db.run(`UPDATE timesheet_entries
|
|
SET start_time = ?, end_time = ?, break_minutes = ?, total_hours = ?, notes = ?,
|
|
activity1_desc = ?, activity1_hours = ?, activity1_project_number = ?,
|
|
activity2_desc = ?, activity2_hours = ?, activity2_project_number = ?,
|
|
activity3_desc = ?, activity3_hours = ?, activity3_project_number = ?,
|
|
activity4_desc = ?, activity4_hours = ?, activity4_project_number = ?,
|
|
activity5_desc = ?, activity5_hours = ?, activity5_project_number = ?,
|
|
overtime_taken_hours = ?, vacation_type = ?, sick_status = ?,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE id = ?`,
|
|
[
|
|
finalStartTime, finalEndTime, break_minutes, total_hours, notes,
|
|
finalActivity1Desc || null, finalActivity1Hours, activity1_project_number || null,
|
|
finalActivity2Desc || null, parseFloat(activity2_hours) || 0, activity2_project_number || null,
|
|
finalActivity3Desc || null, parseFloat(activity3_hours) || 0, activity3_project_number || null,
|
|
finalActivity4Desc || null, parseFloat(activity4_hours) || 0, activity4_project_number || null,
|
|
finalActivity5Desc || null, parseFloat(activity5_hours) || 0, activity5_project_number || null,
|
|
overtime_taken_hours ? parseFloat(overtime_taken_hours) : null,
|
|
vacation_type || null,
|
|
isSick ? 1 : 0,
|
|
row.id
|
|
],
|
|
(err) => {
|
|
if (err) {
|
|
console.error('Fehler beim Update:', err);
|
|
return res.status(500).json({ error: 'Fehler beim Speichern: ' + err.message });
|
|
}
|
|
res.json({ success: true, total_hours });
|
|
});
|
|
} else {
|
|
// Insert
|
|
db.run(`INSERT INTO timesheet_entries
|
|
(user_id, date, start_time, end_time, break_minutes, total_hours, notes,
|
|
activity1_desc, activity1_hours, activity1_project_number,
|
|
activity2_desc, activity2_hours, activity2_project_number,
|
|
activity3_desc, activity3_hours, activity3_project_number,
|
|
activity4_desc, activity4_hours, activity4_project_number,
|
|
activity5_desc, activity5_hours, activity5_project_number,
|
|
overtime_taken_hours, vacation_type, sick_status)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
[
|
|
userId, date, finalStartTime, finalEndTime, break_minutes, total_hours, notes,
|
|
finalActivity1Desc || null, finalActivity1Hours, activity1_project_number || null,
|
|
finalActivity2Desc || null, parseFloat(activity2_hours) || 0, activity2_project_number || null,
|
|
finalActivity3Desc || null, parseFloat(activity3_hours) || 0, activity3_project_number || null,
|
|
finalActivity4Desc || null, parseFloat(activity4_hours) || 0, activity4_project_number || null,
|
|
finalActivity5Desc || null, parseFloat(activity5_hours) || 0, activity5_project_number || null,
|
|
overtime_taken_hours ? parseFloat(overtime_taken_hours) : null,
|
|
vacation_type || null,
|
|
isSick ? 1 : 0
|
|
],
|
|
(err) => {
|
|
if (err) {
|
|
console.error('Fehler beim Insert:', err);
|
|
return res.status(500).json({ error: 'Fehler beim Speichern: ' + err.message });
|
|
}
|
|
res.json({ success: true, total_hours });
|
|
});
|
|
}
|
|
});
|
|
});
|
|
});
|
|
|
|
// API: Stundenerfassung für Woche laden
|
|
app.get('/api/timesheet/week/:weekStart', requireAuth, (req, res) => {
|
|
const userId = req.session.userId;
|
|
const weekStart = req.params.weekStart;
|
|
|
|
// Berechne Wochenende
|
|
const startDate = new Date(weekStart);
|
|
const endDate = new Date(startDate);
|
|
endDate.setDate(endDate.getDate() + 6);
|
|
const weekEnd = endDate.toISOString().split('T')[0];
|
|
|
|
// Prüfe ob die Woche bereits eingereicht wurde (aber ermögliche Bearbeitung)
|
|
db.get(`SELECT id, version FROM weekly_timesheets
|
|
WHERE user_id = ? AND week_start = ? AND week_end = ?
|
|
ORDER BY version DESC LIMIT 1`,
|
|
[userId, weekStart, weekEnd],
|
|
(err, weeklySheet) => {
|
|
const hasSubmittedVersion = !!weeklySheet;
|
|
const latestVersion = weeklySheet ? weeklySheet.version : 0;
|
|
|
|
// Lade alle Einträge für die Woche
|
|
db.all(`SELECT * FROM timesheet_entries
|
|
WHERE user_id = ? AND date >= ? AND date <= ?
|
|
ORDER BY date`,
|
|
[userId, weekStart, weekEnd],
|
|
(err, entries) => {
|
|
// Füge Status-Info hinzu (Bearbeitung ist immer möglich)
|
|
const entriesWithStatus = (entries || []).map(entry => ({
|
|
...entry,
|
|
week_submitted: false, // Immer false, damit Bearbeitung möglich ist
|
|
latest_version: latestVersion,
|
|
has_existing_version: latestVersion > 0
|
|
}));
|
|
res.json(entriesWithStatus);
|
|
});
|
|
});
|
|
});
|
|
|
|
// API: Woche abschicken
|
|
app.post('/api/timesheet/submit', requireAuth, (req, res) => {
|
|
const { week_start, week_end, version_reason } = req.body;
|
|
const userId = req.session.userId;
|
|
|
|
// Validierung: Prüfen ob alle 7 Tage der Woche ausgefüllt sind
|
|
db.all(`SELECT id, date, start_time, end_time, vacation_type, sick_status, updated_at FROM timesheet_entries
|
|
WHERE user_id = ? AND date >= ? AND date <= ?
|
|
ORDER BY date, updated_at DESC, id DESC`,
|
|
[userId, week_start, week_end],
|
|
(err, entries) => {
|
|
if (err) {
|
|
return res.status(500).json({ error: 'Fehler beim Prüfen der Daten' });
|
|
}
|
|
|
|
// Erstelle Set mit vorhandenen Daten
|
|
// WICHTIG: Wenn mehrere Einträge für denselben Tag existieren, nimm den neuesten
|
|
const entriesByDate = {};
|
|
entries.forEach(entry => {
|
|
const existing = entriesByDate[entry.date];
|
|
// Wenn noch kein Eintrag existiert oder dieser neuer ist, verwende ihn
|
|
if (!existing) {
|
|
entriesByDate[entry.date] = entry;
|
|
} else {
|
|
// Vergleiche updated_at (falls vorhanden) oder id (höhere ID = neuer)
|
|
const existingTime = existing.updated_at ? new Date(existing.updated_at).getTime() : 0;
|
|
const currentTime = entry.updated_at ? new Date(entry.updated_at).getTime() : 0;
|
|
if (currentTime > existingTime || (currentTime === existingTime && entry.id > existing.id)) {
|
|
entriesByDate[entry.date] = entry;
|
|
}
|
|
}
|
|
});
|
|
|
|
// Prüfe nur Werktage (Montag-Freitag, erste 5 Tage)
|
|
// Samstag und Sonntag sind optional
|
|
// Bei ganztägigem Urlaub (vacation_type = 'full') ist der Tag als ausgefüllt zu betrachten
|
|
// week_start ist bereits im Format YYYY-MM-DD
|
|
const startDateParts = week_start.split('-');
|
|
const startYear = parseInt(startDateParts[0]);
|
|
const startMonth = parseInt(startDateParts[1]) - 1; // Monat ist 0-basiert
|
|
const startDay = parseInt(startDateParts[2]);
|
|
|
|
let missingDays = [];
|
|
|
|
for (let i = 0; i < 5; i++) {
|
|
// Datum direkt berechnen ohne Zeitzonenprobleme
|
|
const date = new Date(startYear, startMonth, startDay + i);
|
|
const year = date.getFullYear();
|
|
const month = String(date.getMonth() + 1).padStart(2, '0');
|
|
const day = String(date.getDate()).padStart(2, '0');
|
|
const dateStr = `${year}-${month}-${day}`;
|
|
const entry = entriesByDate[dateStr];
|
|
|
|
// Wenn ganztägiger Urlaub oder Krank, dann ist der Tag als ausgefüllt zu betrachten
|
|
const isSick = entry && (entry.sick_status === 1 || entry.sick_status === true);
|
|
if (entry && (entry.vacation_type === 'full' || isSick)) {
|
|
continue; // Tag ist ausgefüllt
|
|
}
|
|
|
|
// Bei halbem Tag Urlaub oder keinem Urlaub müssen Start- und Endzeit vorhanden sein
|
|
// start_time und end_time könnten null, undefined oder leer strings sein
|
|
const hasStartTime = entry && entry.start_time && entry.start_time.toString().trim() !== '';
|
|
const hasEndTime = entry && entry.end_time && entry.end_time.toString().trim() !== '';
|
|
|
|
if (!entry || !hasStartTime || !hasEndTime) {
|
|
missingDays.push(dateStr);
|
|
}
|
|
}
|
|
|
|
if (missingDays.length > 0) {
|
|
return res.status(400).json({
|
|
error: `Nicht alle Werktage (Montag bis Freitag) sind ausgefüllt. Fehlende Tage: ${missingDays.join(', ')}. Bitte füllen Sie alle Werktage mit Start- und Endzeit aus. Wochenende ist optional.`
|
|
});
|
|
}
|
|
|
|
// Alle Tage ausgefüllt - Woche abschicken (immer neue Version erstellen)
|
|
// Prüfe welche Version die letzte ist
|
|
db.get(`SELECT MAX(version) as max_version FROM weekly_timesheets
|
|
WHERE user_id = ? AND week_start = ? AND week_end = ?`,
|
|
[userId, week_start, week_end],
|
|
(err, result) => {
|
|
if (err) return res.status(500).json({ error: 'Fehler beim Prüfen der Version' });
|
|
|
|
const maxVersion = result && result.max_version ? result.max_version : 0;
|
|
const newVersion = maxVersion + 1;
|
|
|
|
// Wenn bereits eine Version existiert, ist version_reason erforderlich
|
|
if (maxVersion > 0 && (!version_reason || version_reason.trim() === '')) {
|
|
return res.status(400).json({
|
|
error: 'Bitte geben Sie einen Grund für die neue Version an.'
|
|
});
|
|
}
|
|
|
|
// Neue Version erstellen (nicht überschreiben)
|
|
db.run(`INSERT INTO weekly_timesheets (user_id, week_start, week_end, version, status, version_reason)
|
|
VALUES (?, ?, ?, ?, 'eingereicht', ?)`,
|
|
[userId, week_start, week_end, newVersion, version_reason ? version_reason.trim() : null],
|
|
(err) => {
|
|
if (err) return res.status(500).json({ error: 'Fehler beim Abschicken' });
|
|
|
|
// Status der Einträge aktualisieren (optional - für Nachverfolgung)
|
|
db.run(`UPDATE timesheet_entries
|
|
SET status = 'eingereicht'
|
|
WHERE user_id = ? AND date >= ? AND date <= ?`,
|
|
[userId, week_start, week_end],
|
|
(err) => {
|
|
if (err) return res.status(500).json({ error: 'Fehler beim Aktualisieren des Status' });
|
|
res.json({ success: true, version: newVersion });
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
|
|
// API: PDF Download-Info abrufen
|
|
app.get('/api/timesheet/download-info/:id', requireVerwaltung, (req, res) => {
|
|
const timesheetId = req.params.id;
|
|
|
|
db.get(`SELECT wt.pdf_downloaded_at,
|
|
dl.firstname as downloaded_by_firstname,
|
|
dl.lastname as downloaded_by_lastname
|
|
FROM weekly_timesheets wt
|
|
LEFT JOIN users dl ON wt.pdf_downloaded_by = dl.id
|
|
WHERE wt.id = ?`, [timesheetId], (err, result) => {
|
|
|
|
if (err) {
|
|
console.error('Fehler beim Abrufen der Download-Info:', err);
|
|
return res.status(500).json({ error: 'Fehler beim Abrufen der Informationen' });
|
|
}
|
|
|
|
if (!result) {
|
|
return res.status(404).json({ error: 'Stundenzettel nicht gefunden' });
|
|
}
|
|
|
|
res.json({
|
|
downloaded: !!result.pdf_downloaded_at,
|
|
downloaded_at: result.pdf_downloaded_at,
|
|
downloaded_by_firstname: result.downloaded_by_firstname,
|
|
downloaded_by_lastname: result.downloaded_by_lastname
|
|
});
|
|
});
|
|
});
|
|
|
|
// API: PDF generieren
|
|
app.get('/api/timesheet/pdf/:id', requireVerwaltung, (req, res) => {
|
|
const timesheetId = req.params.id;
|
|
generatePDF(timesheetId, req, res);
|
|
});
|
|
}
|
|
|
|
module.exports = registerTimesheetRoutes;
|