808 lines
38 KiB
JavaScript
808 lines
38 KiB
JavaScript
// User API Routes
|
||
|
||
const { db } = require('../database');
|
||
const { hasRole, getCurrentDate } = require('../helpers/utils');
|
||
const { requireAuth } = require('../middleware/auth');
|
||
const { getHolidaysForDateRange } = require('../services/feiertage-service');
|
||
|
||
// Routes registrieren
|
||
function registerUserRoutes(app) {
|
||
// API: Letzte bearbeitete Woche abrufen
|
||
app.get('/api/user/last-week', requireAuth, (req, res) => {
|
||
const userId = req.session.userId;
|
||
|
||
db.get('SELECT last_week_start FROM users WHERE id = ?', [userId], (err, user) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der letzten Woche' });
|
||
}
|
||
|
||
res.json({ last_week_start: user?.last_week_start || null });
|
||
});
|
||
});
|
||
|
||
// API: Letzte bearbeitete Woche speichern
|
||
app.post('/api/user/last-week', requireAuth, (req, res) => {
|
||
const userId = req.session.userId;
|
||
const { week_start } = req.body;
|
||
|
||
if (!week_start) {
|
||
return res.status(400).json({ error: 'week_start ist erforderlich' });
|
||
}
|
||
|
||
db.run('UPDATE users SET last_week_start = ? WHERE id = ?',
|
||
[week_start, userId],
|
||
(err) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Speichern der letzten Woche' });
|
||
}
|
||
res.json({ success: true });
|
||
});
|
||
});
|
||
|
||
// API: Wochenend-Prozentsätze abrufen
|
||
app.get('/api/user/weekend-percentages', requireAuth, (req, res) => {
|
||
db.get('SELECT saturday_percentage, sunday_percentage FROM system_options WHERE id = 1', (err, options) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der Wochenend-Prozentsätze' });
|
||
}
|
||
// Wenn keine Optionen vorhanden, Standardwerte zurückgeben
|
||
res.json({
|
||
saturday_percentage: options?.saturday_percentage || 100,
|
||
sunday_percentage: options?.sunday_percentage || 100
|
||
});
|
||
});
|
||
});
|
||
|
||
// API: User-Daten abrufen (Wochenstunden)
|
||
app.get('/api/user/data', requireAuth, (req, res) => {
|
||
const userId = req.session.userId;
|
||
|
||
db.get('SELECT wochenstunden, arbeitstage, default_break_minutes FROM users WHERE id = ?', [userId], (err, user) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der User-Daten' });
|
||
}
|
||
|
||
res.json({
|
||
wochenstunden: user?.wochenstunden || 0,
|
||
arbeitstage: user?.arbeitstage || 5,
|
||
default_break_minutes: user?.default_break_minutes ?? 30
|
||
});
|
||
});
|
||
});
|
||
|
||
// API: Client-IP abrufen
|
||
app.get('/api/user/client-ip', requireAuth, (req, res) => {
|
||
// Versuche verschiedene Methoden, um die Client-IP zu erhalten
|
||
const clientIp = req.ip ||
|
||
req.connection.remoteAddress ||
|
||
req.socket.remoteAddress ||
|
||
(req.headers['x-forwarded-for'] ? req.headers['x-forwarded-for'].split(',')[0].trim() : null) ||
|
||
req.headers['x-real-ip'] ||
|
||
'unknown';
|
||
|
||
// Entferne IPv6-Präfix falls vorhanden (::ffff:192.168.1.1 -> 192.168.1.1)
|
||
const cleanIp = clientIp.replace(/^::ffff:/, '');
|
||
|
||
res.json({ client_ip: cleanIp });
|
||
});
|
||
|
||
// API: Ping-IP abrufen
|
||
app.get('/api/user/ping-ip', requireAuth, (req, res) => {
|
||
const userId = req.session.userId;
|
||
|
||
db.get('SELECT ping_ip FROM users WHERE id = ?', [userId], (err, user) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der IP-Adresse' });
|
||
}
|
||
|
||
res.json({ ping_ip: user?.ping_ip || null });
|
||
});
|
||
});
|
||
|
||
// API: Ping-IP speichern
|
||
app.post('/api/user/ping-ip', requireAuth, (req, res) => {
|
||
const userId = req.session.userId;
|
||
const { ping_ip } = req.body;
|
||
|
||
// Validierung: IPv4 Format (einfache Prüfung)
|
||
const ipv4Regex = /^(\d{1,3}\.){3}\d{1,3}$/;
|
||
if (ping_ip && ping_ip.trim() !== '' && !ipv4Regex.test(ping_ip.trim())) {
|
||
return res.status(400).json({ error: 'Ungültige IP-Adresse. Bitte geben Sie eine gültige IPv4-Adresse ein.' });
|
||
}
|
||
|
||
// Normalisiere: Leere Strings werden zu null
|
||
const normalizedPingIp = (ping_ip && ping_ip.trim() !== '') ? ping_ip.trim() : null;
|
||
|
||
db.run('UPDATE users SET ping_ip = ? WHERE id = ?', [normalizedPingIp, userId], (err) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Speichern der IP-Adresse' });
|
||
}
|
||
|
||
// Wenn IP entfernt wurde, lösche auch den Ping-Status für heute
|
||
if (!normalizedPingIp) {
|
||
const currentDate = getCurrentDate();
|
||
db.run('DELETE FROM ping_status WHERE user_id = ? AND date = ?', [userId, currentDate], (err) => {
|
||
// Fehler ignorieren
|
||
});
|
||
}
|
||
|
||
res.json({ success: true, ping_ip: normalizedPingIp });
|
||
});
|
||
});
|
||
|
||
// API: Rollenwechsel
|
||
app.post('/api/user/switch-role', requireAuth, (req, res) => {
|
||
const { role } = req.body;
|
||
|
||
if (!role) {
|
||
return res.status(400).json({ error: 'Rolle ist erforderlich' });
|
||
}
|
||
|
||
// Prüfe ob User diese Rolle hat
|
||
if (!hasRole(req, role)) {
|
||
return res.status(403).json({ error: 'Sie haben keine Berechtigung für diese Rolle' });
|
||
}
|
||
|
||
// Validiere dass die Rolle eine gültige Rolle ist
|
||
const validRoles = ['mitarbeiter', 'verwaltung', 'admin'];
|
||
if (!validRoles.includes(role)) {
|
||
return res.status(400).json({ error: 'Ungültige Rolle' });
|
||
}
|
||
|
||
// Setze aktuelle Rolle
|
||
req.session.currentRole = role;
|
||
|
||
res.json({ success: true, currentRole: role });
|
||
});
|
||
|
||
// API: Verplante Urlaubstage (nur nicht-eingereichte Wochen)
|
||
app.get('/api/user/planned-vacation', requireAuth, (req, res) => {
|
||
const userId = req.session.userId;
|
||
const { getCalendarWeek, getWeekStart } = require('../helpers/utils');
|
||
|
||
// Zuerst alle eingereichten Wochen abrufen
|
||
db.all(`SELECT DISTINCT week_start FROM weekly_timesheets
|
||
WHERE user_id = ? AND status = 'eingereicht'`,
|
||
[userId],
|
||
(err, submittedWeeks) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der eingereichten Wochen' });
|
||
}
|
||
|
||
// Set für schnelle Suche nach eingereichten Wochen
|
||
const submittedWeekStarts = new Set(
|
||
(submittedWeeks || []).map(w => w.week_start)
|
||
);
|
||
|
||
// Alle Urlaubseinträge abrufen
|
||
db.all(`SELECT date, vacation_type FROM timesheet_entries
|
||
WHERE user_id = ? AND vacation_type IS NOT NULL AND vacation_type != ''`,
|
||
[userId],
|
||
(err, entries) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der verplanten Tage' });
|
||
}
|
||
|
||
let plannedDays = 0;
|
||
const weeksMap = {}; // { KW: { year: YYYY, week: KW, days: X } }
|
||
|
||
entries.forEach(entry => {
|
||
// Berechne week_start für diesen Eintrag
|
||
const weekStart = getWeekStart(entry.date);
|
||
|
||
// Überspringe Einträge aus eingereichten Wochen
|
||
if (submittedWeekStarts.has(weekStart)) {
|
||
return;
|
||
}
|
||
|
||
const dayValue = entry.vacation_type === 'full' ? 1 : 0.5;
|
||
plannedDays += dayValue;
|
||
|
||
// Berechne Kalenderwoche
|
||
const date = new Date(entry.date);
|
||
const year = date.getFullYear();
|
||
const week = getCalendarWeek(entry.date);
|
||
const weekKey = `${year}-KW${week}`;
|
||
|
||
if (!weeksMap[weekKey]) {
|
||
weeksMap[weekKey] = { year, week, days: 0 };
|
||
}
|
||
weeksMap[weekKey].days += dayValue;
|
||
});
|
||
|
||
// Konvertiere zu sortiertem Array
|
||
const weeks = Object.values(weeksMap).sort((a, b) => {
|
||
if (a.year !== b.year) return a.year - b.year;
|
||
return a.week - b.week;
|
||
});
|
||
|
||
res.json({
|
||
plannedVacationDays: plannedDays,
|
||
weeks: weeks
|
||
});
|
||
}
|
||
);
|
||
}
|
||
);
|
||
});
|
||
|
||
// API: Gesamtstatistiken für Mitarbeiter (Überstunden und Urlaubstage)
|
||
app.get('/api/user/stats', requireAuth, (req, res) => {
|
||
const userId = req.session.userId;
|
||
|
||
// Wochenend-Prozentsätze laden
|
||
db.get('SELECT saturday_percentage, sunday_percentage FROM system_options WHERE id = 1', (err, options) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Laden der Optionen' });
|
||
}
|
||
|
||
const saturdayPercentage = options?.saturday_percentage || 100;
|
||
const sundayPercentage = options?.sunday_percentage || 100;
|
||
|
||
// Hilfsfunktion: Prüft ob ein Datum ein Wochenendtag ist und gibt den Prozentsatz zurück
|
||
function getWeekendPercentage(dateStr) {
|
||
const date = new Date(dateStr);
|
||
const day = date.getDay();
|
||
if (day === 6) { // Samstag
|
||
return saturdayPercentage;
|
||
} else if (day === 0) { // Sonntag
|
||
return sundayPercentage;
|
||
}
|
||
return 100; // Kein Wochenende = 100% (normal)
|
||
}
|
||
|
||
// User-Daten abrufen
|
||
db.get('SELECT wochenstunden, urlaubstage, overtime_offset_hours, vacation_offset_days, arbeitstage FROM users WHERE id = ?', [userId], (err, user) => {
|
||
if (err || !user) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der User-Daten' });
|
||
}
|
||
|
||
const wochenstunden = user.wochenstunden || 0;
|
||
const arbeitstage = user.arbeitstage || 5;
|
||
const urlaubstage = user.urlaubstage || 0;
|
||
const overtimeOffsetHours = user.overtime_offset_hours ? parseFloat(user.overtime_offset_hours) : 0;
|
||
const vacationOffsetDays = user.vacation_offset_days ? parseFloat(user.vacation_offset_days) : 0;
|
||
|
||
// Verplante Urlaubstage berechnen (nur nicht-eingereichte Wochen)
|
||
const { getCalendarWeek, getWeekStart } = require('../helpers/utils');
|
||
|
||
// Zuerst alle eingereichten Wochen abrufen
|
||
db.all(`SELECT DISTINCT week_start, week_end
|
||
FROM weekly_timesheets
|
||
WHERE user_id = ? AND status = 'eingereicht'
|
||
ORDER BY week_start`,
|
||
[userId],
|
||
(err, weeks) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der Wochen' });
|
||
}
|
||
|
||
// Set für schnelle Suche nach eingereichten Wochen
|
||
const submittedWeekStarts = new Set(
|
||
(weeks || []).map(w => w.week_start)
|
||
);
|
||
|
||
// Alle Urlaubseinträge abrufen
|
||
db.all(`SELECT date, vacation_type FROM timesheet_entries
|
||
WHERE user_id = ? AND vacation_type IS NOT NULL AND vacation_type != ''`,
|
||
[userId],
|
||
(err, allVacationEntries) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der verplanten Tage' });
|
||
}
|
||
|
||
let plannedVacationDays = 0;
|
||
const weeksMap = {}; // { KW: { year: YYYY, week: KW, days: X } }
|
||
|
||
(allVacationEntries || []).forEach(entry => {
|
||
// Berechne week_start für diesen Eintrag
|
||
const weekStart = getWeekStart(entry.date);
|
||
|
||
// Überspringe Einträge aus eingereichten Wochen
|
||
if (submittedWeekStarts.has(weekStart)) {
|
||
return;
|
||
}
|
||
|
||
const dayValue = entry.vacation_type === 'full' ? 1 : 0.5;
|
||
plannedVacationDays += dayValue;
|
||
|
||
// Berechne Kalenderwoche
|
||
const date = new Date(entry.date);
|
||
const year = date.getFullYear();
|
||
const week = getCalendarWeek(entry.date);
|
||
const weekKey = `${year}-KW${week}`;
|
||
|
||
if (!weeksMap[weekKey]) {
|
||
weeksMap[weekKey] = { year, week, days: 0 };
|
||
}
|
||
weeksMap[weekKey].days += dayValue;
|
||
});
|
||
|
||
// Konvertiere zu sortiertem Array
|
||
const plannedWeeks = Object.values(weeksMap).sort((a, b) => {
|
||
if (a.year !== b.year) return a.year - b.year;
|
||
return a.week - b.week;
|
||
});
|
||
|
||
// Weiter mit der Verarbeitung der eingereichten Wochen (weeks ist bereits verfügbar)
|
||
// Wenn keine Wochen vorhanden
|
||
if (!weeks || weeks.length === 0) {
|
||
return res.json({
|
||
currentOvertime: overtimeOffsetHours,
|
||
remainingVacation: urlaubstage + vacationOffsetDays,
|
||
totalOvertimeHours: 0,
|
||
totalOvertimeTaken: 0,
|
||
totalVacationDays: 0,
|
||
plannedVacationDays: plannedVacationDays,
|
||
plannedWeeks: plannedWeeks,
|
||
urlaubstage: urlaubstage,
|
||
overtimeOffsetHours: overtimeOffsetHours,
|
||
vacationOffsetDays: vacationOffsetDays
|
||
});
|
||
}
|
||
|
||
let totalOvertimeHours = 0;
|
||
let totalOvertimeTaken = 0;
|
||
let totalVacationDays = 0;
|
||
let processedWeeks = 0;
|
||
let hasError = false;
|
||
|
||
// Für jede Woche die Statistiken berechnen
|
||
weeks.forEach((week) => {
|
||
// Einträge für diese Woche abrufen (nur neueste pro Tag)
|
||
db.all(`SELECT id, date, total_hours, overtime_taken_hours, vacation_type, sick_status, start_time, end_time, updated_at
|
||
FROM timesheet_entries
|
||
WHERE user_id = ? AND date >= ? AND date <= ?
|
||
ORDER BY date, updated_at DESC, id DESC`,
|
||
[userId, week.week_start, week.week_end],
|
||
(err, allEntries) => {
|
||
if (hasError) return; // Wenn bereits ein Fehler aufgetreten ist, ignoriere weitere Ergebnisse
|
||
|
||
if (err) {
|
||
hasError = true;
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der Einträge' });
|
||
}
|
||
|
||
// Filtere auf neuesten Eintrag pro Tag
|
||
const entriesByDate = {};
|
||
(allEntries || []).forEach(entry => {
|
||
const existing = entriesByDate[entry.date];
|
||
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;
|
||
}
|
||
}
|
||
});
|
||
|
||
// Konvertiere zurück zu Array
|
||
const entries = Object.values(entriesByDate);
|
||
|
||
// Prüfe ob Woche vollständig ausgefüllt ist (alle 5 Werktage)
|
||
|
||
// Feiertage für die Woche laden (Feiertag zählt als ausgefüllt)
|
||
getHolidaysForDateRange(week.week_start, week.week_end)
|
||
.catch(() => new Set())
|
||
.then((holidaySet) => {
|
||
// Prüfe alle 5 Werktage (Montag-Freitag)
|
||
const startDate = new Date(week.week_start);
|
||
const endDate = new Date(week.week_end);
|
||
let workdays = 0;
|
||
let filledWorkdays = 0;
|
||
const fullDayHoursForCheck = wochenstunden > 0 && arbeitstage > 0 ? wochenstunden / arbeitstage : 8;
|
||
|
||
for (let d = new Date(startDate); d <= endDate; d.setDate(d.getDate() + 1)) {
|
||
const day = d.getDay();
|
||
if (day >= 1 && day <= 5) { // Montag bis Freitag
|
||
workdays++;
|
||
const dateStr = d.toISOString().split('T')[0];
|
||
if (holidaySet.has(dateStr)) {
|
||
filledWorkdays++;
|
||
continue;
|
||
}
|
||
const entry = entriesByDate[dateStr];
|
||
|
||
// Tag gilt als ausgefüllt wenn:
|
||
// - Ganzer Tag Urlaub (vacation_type = 'full')
|
||
// - Krank (sick_status = 1)
|
||
// - Ganzer Tag Überstunden (overtime_taken_hours = fullDayHours)
|
||
// - ODER Start- und End-Zeit vorhanden sind
|
||
if (entry) {
|
||
const isFullDayVacation = entry.vacation_type === 'full';
|
||
const isSick = entry.sick_status === 1 || entry.sick_status === true;
|
||
const overtimeValue = entry.overtime_taken_hours ? parseFloat(entry.overtime_taken_hours) : 0;
|
||
const isFullDayOvertime = overtimeValue > 0 && Math.abs(overtimeValue - fullDayHoursForCheck) < 0.01;
|
||
const hasStartAndEnd = entry.start_time && entry.end_time &&
|
||
entry.start_time.toString().trim() !== '' &&
|
||
entry.end_time.toString().trim() !== '';
|
||
|
||
if (isFullDayVacation || isSick || isFullDayOvertime || hasStartAndEnd) {
|
||
filledWorkdays++;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
// Nur berechnen wenn alle Werktage ausgefüllt sind
|
||
if (filledWorkdays < workdays) {
|
||
// Woche nicht vollständig - überspringe diese Woche
|
||
processedWeeks++;
|
||
if (processedWeeks === weeks.length && !hasError) {
|
||
const currentOvertime = (totalOvertimeHours - totalOvertimeTaken) + overtimeOffsetHours;
|
||
const remainingVacation = urlaubstage - totalVacationDays + vacationOffsetDays;
|
||
|
||
res.json({
|
||
currentOvertime: currentOvertime,
|
||
remainingVacation: remainingVacation,
|
||
totalOvertimeHours: totalOvertimeHours,
|
||
totalOvertimeTaken: totalOvertimeTaken,
|
||
totalVacationDays: totalVacationDays,
|
||
plannedVacationDays: plannedVacationDays,
|
||
plannedWeeks: plannedWeeks,
|
||
urlaubstage: urlaubstage,
|
||
overtimeOffsetHours: overtimeOffsetHours,
|
||
vacationOffsetDays: vacationOffsetDays
|
||
});
|
||
}
|
||
return; // Überspringe diese Woche
|
||
}
|
||
|
||
// Berechnungen für diese Woche (nur wenn vollständig ausgefüllt)
|
||
let weekTotalHours = 0;
|
||
let weekOvertimeTaken = 0;
|
||
let weekVacationDays = 0;
|
||
let weekVacationHours = 0;
|
||
|
||
const fullDayHours = wochenstunden > 0 && arbeitstage > 0 ? wochenstunden / arbeitstage : 8;
|
||
let fullDayOvertimeDays = 0; // Anzahl Tage mit 8 Überstunden
|
||
|
||
entries.forEach(entry => {
|
||
// Prüfe ob 8 Überstunden (ganzer Tag) eingetragen sind
|
||
const overtimeValue = entry.overtime_taken_hours ? parseFloat(entry.overtime_taken_hours) : 0;
|
||
const isFullDayOvertime = overtimeValue > 0 && Math.abs(overtimeValue - fullDayHours) < 0.01;
|
||
|
||
if (entry.overtime_taken_hours) {
|
||
weekOvertimeTaken += parseFloat(entry.overtime_taken_hours) || 0;
|
||
}
|
||
|
||
// Wenn 8 Überstunden eingetragen sind, zählt der Tag als 0 Stunden
|
||
// Diese Tage werden separat gezählt, um die Sollstunden anzupassen
|
||
if (isFullDayOvertime) {
|
||
fullDayOvertimeDays++;
|
||
}
|
||
|
||
// Urlaub hat Priorität - wenn Urlaub, zähle nur Urlaubsstunden, nicht zusätzlich Arbeitsstunden
|
||
if (entry.vacation_type === 'full') {
|
||
weekVacationDays += 1;
|
||
weekVacationHours += fullDayHours; // Ganzer Tag = (Wochenarbeitszeit / Arbeitstage) Stunden
|
||
// Bei vollem Tag Urlaub werden keine Arbeitsstunden gezählt
|
||
} else if (entry.vacation_type === 'half') {
|
||
weekVacationDays += 0.5;
|
||
weekVacationHours += fullDayHours / 2; // Halber Tag = (Wochenarbeitszeit / Arbeitstage) / 2 Stunden
|
||
// Bei halbem Tag Urlaub können noch Arbeitsstunden vorhanden sein
|
||
// WICHTIG: total_hours enthält bereits Wochenend-Prozentsätze (aus timesheet.js)
|
||
if (entry.total_hours && !isFullDayOvertime) {
|
||
weekTotalHours += parseFloat(entry.total_hours) || 0;
|
||
}
|
||
} else {
|
||
// Kein Urlaub - zähle nur Arbeitsstunden (wenn nicht 8 Überstunden)
|
||
// WICHTIG: total_hours enthält bereits Wochenend-Prozentsätze (aus timesheet.js)
|
||
if (entry.total_hours && !isFullDayOvertime) {
|
||
weekTotalHours += parseFloat(entry.total_hours) || 0;
|
||
}
|
||
}
|
||
});
|
||
|
||
// Feiertagsstunden: (Wochenarbeitszeit / Arbeitstage) pro Werktag der ein Feiertag ist
|
||
let holidayHours = 0;
|
||
for (let d = new Date(startDate); d <= endDate; d.setDate(d.getDate() + 1)) {
|
||
const day = d.getDay();
|
||
if (day >= 1 && day <= 5) {
|
||
const dateStr = d.toISOString().split('T')[0];
|
||
if (holidaySet.has(dateStr)) holidayHours += fullDayHours;
|
||
}
|
||
}
|
||
|
||
// Sollstunden berechnen (Variante B: immer vertraglich, nicht reduziert durch „genommen“)
|
||
const sollStunden = (wochenstunden / arbeitstage) * workdays;
|
||
|
||
const weekTotalHoursWithVacation = weekTotalHours + weekVacationHours + holidayHours;
|
||
// Überstunden/Fehlstunden = Gesamt − Soll (kann negativ sein)
|
||
const weekOvertimeHours = weekTotalHoursWithVacation - sollStunden;
|
||
|
||
// Kumulativ addieren
|
||
totalOvertimeHours += weekOvertimeHours;
|
||
totalOvertimeTaken += weekOvertimeTaken;
|
||
totalVacationDays += weekVacationDays;
|
||
|
||
processedWeeks++;
|
||
|
||
// Wenn alle Wochen verarbeitet wurden, Antwort senden
|
||
if (processedWeeks === weeks.length && !hasError) {
|
||
// Variante B: Verbleibend = Summe Wochen-Überstunden + Offset („genommen“ nur Anzeige)
|
||
const currentOvertime = totalOvertimeHours + overtimeOffsetHours;
|
||
const remainingVacation = urlaubstage - totalVacationDays + vacationOffsetDays;
|
||
|
||
res.json({
|
||
currentOvertime: currentOvertime,
|
||
remainingVacation: remainingVacation,
|
||
totalOvertimeHours: totalOvertimeHours,
|
||
totalOvertimeTaken: totalOvertimeTaken,
|
||
totalVacationDays: totalVacationDays,
|
||
plannedVacationDays: plannedVacationDays,
|
||
plannedWeeks: plannedWeeks,
|
||
urlaubstage: urlaubstage,
|
||
overtimeOffsetHours: overtimeOffsetHours,
|
||
vacationOffsetDays: vacationOffsetDays
|
||
});
|
||
}
|
||
}); // getHolidaysForDateRange.then
|
||
}); // db.all (allEntries)
|
||
}); // weeks.forEach
|
||
}); // db.all (weeks)
|
||
}); // db.all (allVacationEntries)
|
||
}); // db.get (user)
|
||
}); // db.get (options)
|
||
}); // app.get
|
||
|
||
// API: Wöchentliche Überstunden-Aufschlüsselung
|
||
app.get('/api/user/overtime-breakdown', requireAuth, (req, res) => {
|
||
const userId = req.session.userId;
|
||
|
||
// Wochenend-Prozentsätze laden
|
||
db.get('SELECT saturday_percentage, sunday_percentage FROM system_options WHERE id = 1', (err, options) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Laden der Optionen' });
|
||
}
|
||
|
||
const saturdayPercentage = options?.saturday_percentage || 100;
|
||
const sundayPercentage = options?.sunday_percentage || 100;
|
||
|
||
// Hilfsfunktion: Prüft ob ein Datum ein Wochenendtag ist und gibt den Prozentsatz zurück
|
||
function getWeekendPercentage(dateStr) {
|
||
const date = new Date(dateStr);
|
||
const day = date.getDay();
|
||
if (day === 6) { // Samstag
|
||
return saturdayPercentage;
|
||
} else if (day === 0) { // Sonntag
|
||
return sundayPercentage;
|
||
}
|
||
return 100; // Kein Wochenende = 100% (normal)
|
||
}
|
||
|
||
// User-Daten abrufen
|
||
db.get('SELECT wochenstunden, overtime_offset_hours, arbeitstage FROM users WHERE id = ?', [userId], (err, user) => {
|
||
if (err || !user) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der User-Daten' });
|
||
}
|
||
|
||
const wochenstunden = user.wochenstunden || 0;
|
||
const arbeitstage = user.arbeitstage || 5;
|
||
const overtimeOffsetHours = user.overtime_offset_hours ? parseFloat(user.overtime_offset_hours) : 0;
|
||
|
||
// Korrekturen durch die Verwaltung (Historie) laden
|
||
db.all(
|
||
`SELECT correction_hours, corrected_at, reason
|
||
FROM overtime_corrections
|
||
WHERE user_id = ?
|
||
ORDER BY corrected_at DESC`,
|
||
[userId],
|
||
(correctionsErr, corrections) => {
|
||
// Falls Tabelle noch nicht existiert (z. B. alte DB), nicht hart fehlschlagen
|
||
const overtimeCorrections = correctionsErr ? [] : (corrections || []);
|
||
|
||
// Alle eingereichten Wochen abrufen
|
||
db.all(`SELECT DISTINCT week_start, week_end
|
||
FROM weekly_timesheets
|
||
WHERE user_id = ? AND status = 'eingereicht'
|
||
ORDER BY week_start DESC`,
|
||
[userId],
|
||
(err, weeks) => {
|
||
if (err) {
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der Wochen' });
|
||
}
|
||
|
||
// Wenn keine Wochen vorhanden
|
||
if (!weeks || weeks.length === 0) {
|
||
return res.json({
|
||
weeks: [],
|
||
overtime_offset_hours: overtimeOffsetHours,
|
||
overtime_corrections: overtimeCorrections
|
||
});
|
||
}
|
||
|
||
const { getCalendarWeek } = require('../helpers/utils');
|
||
const weekData = [];
|
||
let processedWeeks = 0;
|
||
let hasError = false;
|
||
|
||
// Für jede Woche die Statistiken berechnen
|
||
weeks.forEach((week) => {
|
||
// Einträge für diese Woche abrufen (nur neueste pro Tag)
|
||
db.all(`SELECT id, date, total_hours, overtime_taken_hours, vacation_type, sick_status, start_time, end_time, updated_at
|
||
FROM timesheet_entries
|
||
WHERE user_id = ? AND date >= ? AND date <= ?
|
||
ORDER BY date, updated_at DESC, id DESC`,
|
||
[userId, week.week_start, week.week_end],
|
||
(err, allEntries) => {
|
||
if (hasError) return;
|
||
|
||
if (err) {
|
||
hasError = true;
|
||
return res.status(500).json({ error: 'Fehler beim Abrufen der Einträge' });
|
||
}
|
||
|
||
// Filtere auf neuesten Eintrag pro Tag
|
||
const entriesByDate = {};
|
||
(allEntries || []).forEach(entry => {
|
||
const existing = entriesByDate[entry.date];
|
||
if (!existing) {
|
||
entriesByDate[entry.date] = entry;
|
||
} else {
|
||
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;
|
||
}
|
||
}
|
||
});
|
||
|
||
// Konvertiere zurück zu Array
|
||
const entries = Object.values(entriesByDate);
|
||
|
||
// Feiertage für die Woche laden
|
||
getHolidaysForDateRange(week.week_start, week.week_end)
|
||
.catch(() => new Set())
|
||
.then((holidaySet) => {
|
||
// Prüfe alle 5 Werktage (Montag-Freitag)
|
||
const startDate = new Date(week.week_start);
|
||
const endDate = new Date(week.week_end);
|
||
let workdays = 0;
|
||
let filledWorkdays = 0;
|
||
const fullDayHoursForCheck = wochenstunden > 0 && arbeitstage > 0 ? wochenstunden / arbeitstage : 8;
|
||
|
||
for (let d = new Date(startDate); d <= endDate; d.setDate(d.getDate() + 1)) {
|
||
const day = d.getDay();
|
||
if (day >= 1 && day <= 5) { // Montag bis Freitag
|
||
workdays++;
|
||
const dateStr = d.toISOString().split('T')[0];
|
||
if (holidaySet.has(dateStr)) {
|
||
filledWorkdays++;
|
||
continue;
|
||
}
|
||
const entry = entriesByDate[dateStr];
|
||
|
||
if (entry) {
|
||
const isFullDayVacation = entry.vacation_type === 'full';
|
||
const isSick = entry.sick_status === 1 || entry.sick_status === true;
|
||
const overtimeValue = entry.overtime_taken_hours ? parseFloat(entry.overtime_taken_hours) : 0;
|
||
const isFullDayOvertime = overtimeValue > 0 && Math.abs(overtimeValue - fullDayHoursForCheck) < 0.01;
|
||
const hasStartAndEnd = entry.start_time && entry.end_time &&
|
||
entry.start_time.toString().trim() !== '' &&
|
||
entry.end_time.toString().trim() !== '';
|
||
|
||
if (isFullDayVacation || isSick || isFullDayOvertime || hasStartAndEnd) {
|
||
filledWorkdays++;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
// Nur berechnen wenn alle Werktage ausgefüllt sind
|
||
if (filledWorkdays < workdays) {
|
||
processedWeeks++;
|
||
if (processedWeeks === weeks.length && !hasError) {
|
||
res.json({
|
||
weeks: weekData,
|
||
overtime_offset_hours: overtimeOffsetHours,
|
||
overtime_corrections: overtimeCorrections
|
||
});
|
||
}
|
||
return;
|
||
}
|
||
|
||
// Berechnungen für diese Woche
|
||
let weekTotalHours = 0;
|
||
let weekOvertimeTaken = 0;
|
||
let weekVacationDays = 0;
|
||
let weekVacationHours = 0;
|
||
|
||
const fullDayHours = wochenstunden > 0 && arbeitstage > 0 ? wochenstunden / arbeitstage : 8;
|
||
let fullDayOvertimeDays = 0;
|
||
|
||
entries.forEach(entry => {
|
||
const overtimeValue = entry.overtime_taken_hours ? parseFloat(entry.overtime_taken_hours) : 0;
|
||
const isFullDayOvertime = overtimeValue > 0 && Math.abs(overtimeValue - fullDayHours) < 0.01;
|
||
|
||
if (entry.overtime_taken_hours) {
|
||
weekOvertimeTaken += parseFloat(entry.overtime_taken_hours) || 0;
|
||
}
|
||
|
||
if (isFullDayOvertime) {
|
||
fullDayOvertimeDays++;
|
||
}
|
||
|
||
if (entry.vacation_type === 'full') {
|
||
weekVacationDays += 1;
|
||
weekVacationHours += fullDayHours;
|
||
} else if (entry.vacation_type === 'half') {
|
||
weekVacationDays += 0.5;
|
||
weekVacationHours += fullDayHours / 2;
|
||
// WICHTIG: total_hours enthält bereits Wochenend-Prozentsätze (aus timesheet.js)
|
||
if (entry.total_hours && !isFullDayOvertime) {
|
||
weekTotalHours += parseFloat(entry.total_hours) || 0;
|
||
}
|
||
} else {
|
||
// WICHTIG: total_hours enthält bereits Wochenend-Prozentsätze (aus timesheet.js)
|
||
if (entry.total_hours && !isFullDayOvertime) {
|
||
weekTotalHours += parseFloat(entry.total_hours) || 0;
|
||
}
|
||
}
|
||
});
|
||
|
||
// Feiertagsstunden: (Wochenarbeitszeit / Arbeitstage) pro Werktag der ein Feiertag ist
|
||
let holidayHours = 0;
|
||
for (let d = new Date(startDate); d <= endDate; d.setDate(d.getDate() + 1)) {
|
||
const day = d.getDay();
|
||
if (day >= 1 && day <= 5) {
|
||
const dateStr = d.toISOString().split('T')[0];
|
||
if (holidaySet.has(dateStr)) holidayHours += fullDayHours;
|
||
}
|
||
}
|
||
|
||
// Sollstunden berechnen (Variante B: immer vertraglich)
|
||
const sollStunden = (wochenstunden / arbeitstage) * workdays;
|
||
const weekTotalHoursWithVacation = weekTotalHours + weekVacationHours + holidayHours;
|
||
const weekOvertimeHours = weekTotalHoursWithVacation - sollStunden;
|
||
|
||
// Kalenderwoche berechnen
|
||
const calendarWeek = getCalendarWeek(week.week_start);
|
||
const year = new Date(week.week_start).getFullYear();
|
||
|
||
// Wochen-Daten hinzufügen
|
||
weekData.push({
|
||
week_start: week.week_start,
|
||
week_end: week.week_end,
|
||
calendar_week: calendarWeek,
|
||
year: year,
|
||
overtime_hours: parseFloat(weekOvertimeHours.toFixed(2)),
|
||
overtime_taken: parseFloat(weekOvertimeTaken.toFixed(2)),
|
||
total_hours: parseFloat(weekTotalHoursWithVacation.toFixed(2)),
|
||
soll_stunden: parseFloat(sollStunden.toFixed(2)),
|
||
vacation_days: parseFloat(weekVacationDays.toFixed(1)),
|
||
workdays: workdays
|
||
});
|
||
|
||
processedWeeks++;
|
||
|
||
// Wenn alle Wochen verarbeitet wurden, Antwort senden
|
||
if (processedWeeks === weeks.length && !hasError) {
|
||
// Sortiere nach Datum (neueste zuerst)
|
||
weekData.sort((a, b) => {
|
||
if (a.year !== b.year) return b.year - a.year;
|
||
if (a.calendar_week !== b.calendar_week) return b.calendar_week - a.calendar_week;
|
||
return new Date(b.week_start) - new Date(a.week_start);
|
||
});
|
||
res.json({
|
||
weeks: weekData,
|
||
overtime_offset_hours: overtimeOffsetHours,
|
||
overtime_corrections: overtimeCorrections
|
||
});
|
||
}
|
||
}); // getHolidaysForDateRange.then
|
||
}); // db.all (allEntries)
|
||
}); // weeks.forEach
|
||
}); // db.all (weeks)
|
||
}
|
||
);
|
||
}); // db.get (user)
|
||
}); // db.get (options)
|
||
}); // app.get
|
||
}
|
||
|
||
module.exports = registerUserRoutes;
|