392 lines
13 KiB
JavaScript
392 lines
13 KiB
JavaScript
const fileInput = document.getElementById('csvFile');
|
|
const dropZone = document.getElementById('dropZone');
|
|
const targetTimeInput = document.getElementById('targetTime');
|
|
const errorBox = document.getElementById('errorBox');
|
|
const summary = document.getElementById('summary');
|
|
const results = document.getElementById('results');
|
|
const monthTable = document.getElementById('monthTable');
|
|
const dayTable = document.getElementById('dayTable');
|
|
const exportButton = document.getElementById('exportCsv');
|
|
const formatHint = document.getElementById('formatHint');
|
|
|
|
let lastResult = null;
|
|
let lastFileText = null;
|
|
|
|
fileInput.addEventListener('change', event => {
|
|
const file = event.target.files[0];
|
|
if (file) readFile(file);
|
|
});
|
|
|
|
targetTimeInput.addEventListener('change', () => {
|
|
if (lastFileText) analyseCsv(lastFileText);
|
|
});
|
|
|
|
exportButton.addEventListener('click', () => {
|
|
if (lastResult) exportResultCsv(lastResult);
|
|
});
|
|
|
|
['dragenter', 'dragover'].forEach(eventName => {
|
|
dropZone.addEventListener(eventName, event => {
|
|
event.preventDefault();
|
|
dropZone.classList.add('dragover');
|
|
});
|
|
});
|
|
|
|
['dragleave', 'drop'].forEach(eventName => {
|
|
dropZone.addEventListener(eventName, event => {
|
|
event.preventDefault();
|
|
dropZone.classList.remove('dragover');
|
|
});
|
|
});
|
|
|
|
dropZone.addEventListener('drop', event => {
|
|
const file = event.dataTransfer.files[0];
|
|
if (file) readFile(file);
|
|
});
|
|
|
|
function readFile(file) {
|
|
hideError();
|
|
|
|
if (!file.name.toLowerCase().endsWith('.csv')) {
|
|
showError('Bitte eine CSV-Datei auswählen.');
|
|
return;
|
|
}
|
|
|
|
const reader = new FileReader();
|
|
reader.onload = () => {
|
|
lastFileText = String(reader.result || '');
|
|
analyseCsv(lastFileText);
|
|
};
|
|
reader.onerror = () => showError('Die Datei konnte nicht gelesen werden.');
|
|
reader.readAsText(file, 'utf-8');
|
|
}
|
|
|
|
function analyseCsv(text) {
|
|
try {
|
|
const rows = parseCsv(text);
|
|
if (rows.length < 2) throw new Error('Die CSV enthält keine auswertbaren Daten.');
|
|
|
|
const headers = rows[0].map(h => h.trim().replace(/^\uFEFF/, ''));
|
|
const dataRows = rows.slice(1).filter(row => row.some(cell => String(cell).trim() !== ''));
|
|
|
|
const targetMinutes = timeToMinutes(targetTimeInput.value || '08:00');
|
|
const detectedFormat = detectFormat(headers);
|
|
|
|
let days;
|
|
if (detectedFormat === 'daily') {
|
|
days = analyseDailyFormat(headers, dataRows, targetMinutes);
|
|
formatHint.textContent = 'Format erkannt: Tagesauswertung. Montag bis Freitag haben Sollzeit, Samstag und Sonntag werden mit 0:00 Sollzeit berechnet. Zeilen ohne Arbeitszeit werden ignoriert.';
|
|
} else if (detectedFormat === 'monthly') {
|
|
days = analyseMonthlyFormat(headers, dataRows, targetMinutes);
|
|
formatHint.textContent = 'Format erkannt: Monatsauswertung. Arbeitszeiten werden aus dem Monatsblock gelesen.';
|
|
} else {
|
|
throw new Error('Das CSV-Format wurde nicht erkannt. Benötigt wird entweder die Spalte "Tag" oder "Monat" plus "Dauer Arbeitstag".');
|
|
}
|
|
|
|
if (!days.length) throw new Error('Es wurden keine Arbeitszeiten in "Dauer Arbeitstag" gefunden.');
|
|
|
|
const months = groupByMonth(days);
|
|
const totals = {
|
|
days: days.length,
|
|
workMinutes: days.reduce((sum, day) => sum + day.workMinutes, 0),
|
|
targetMinutes: days.reduce((sum, day) => sum + day.targetMinutes, 0),
|
|
saldoMinutes: days.reduce((sum, day) => sum + day.saldoMinutes, 0)
|
|
};
|
|
|
|
lastResult = { months, days, totals };
|
|
render(lastResult);
|
|
} catch (error) {
|
|
showError(error.message);
|
|
}
|
|
}
|
|
|
|
function detectFormat(headers) {
|
|
const hasDuration = headers.includes('Dauer Arbeitstag');
|
|
if (!hasDuration) return 'unknown';
|
|
if (headers.includes('Tag')) return 'daily';
|
|
if (headers.includes('Monat')) return 'monthly';
|
|
return 'unknown';
|
|
}
|
|
|
|
function analyseDailyFormat(headers, dataRows, targetMinutes) {
|
|
const dateIndex = headers.indexOf('Tag');
|
|
const startIndex = headers.indexOf('Arbeitstag Beginn');
|
|
const endIndex = headers.indexOf('Arbeitstag Ende');
|
|
const durationIndex = headers.indexOf('Dauer Arbeitstag');
|
|
const breakIndex = headers.indexOf('Ruhepause eingehalten');
|
|
const warningIndex = headers.indexOf('Warnungen');
|
|
|
|
const days = [];
|
|
for (const row of dataRows) {
|
|
const date = (row[dateIndex] || '').trim();
|
|
const durationText = row[durationIndex] || '';
|
|
const workMinutes = extractSingleDuration(durationText);
|
|
|
|
// Wochenenden, Feiertage und nicht gearbeitete Tage stehen meist als "-" in Dauer Arbeitstag.
|
|
if (workMinutes === null) continue;
|
|
|
|
days.push({
|
|
date,
|
|
month: monthNameFromDate(date),
|
|
start: cleanCell(row[startIndex]),
|
|
end: cleanCell(row[endIndex]),
|
|
workMinutes,
|
|
weekday: weekdayNameFromDate(date),
|
|
targetMinutes: targetMinutesForDate(date, targetMinutes),
|
|
saldoMinutes: workMinutes - targetMinutesForDate(date, targetMinutes),
|
|
breakOk: cleanCell(row[breakIndex]),
|
|
warnings: cleanCell(row[warningIndex])
|
|
});
|
|
}
|
|
return days;
|
|
}
|
|
|
|
function analyseMonthlyFormat(headers, dataRows, targetMinutes) {
|
|
const monthIndex = headers.indexOf('Monat');
|
|
const durationIndex = headers.indexOf('Dauer Arbeitstag');
|
|
const stampIndex = headers.indexOf('Stempelzeit');
|
|
|
|
const days = [];
|
|
for (const row of dataRows) {
|
|
const month = row[monthIndex] || 'Unbekannter Monat';
|
|
const durations = extractDurations(row[durationIndex] || '');
|
|
const dates = stampIndex >= 0 ? extractDates(row[stampIndex] || '') : [];
|
|
|
|
durations.forEach((workMinutes, index) => {
|
|
const date = dates[index] || `Tag ${index + 1}`;
|
|
days.push({
|
|
date,
|
|
month,
|
|
start: '',
|
|
end: '',
|
|
workMinutes,
|
|
weekday: weekdayNameFromDate(date),
|
|
targetMinutes: targetMinutesForDate(date, targetMinutes),
|
|
saldoMinutes: workMinutes - targetMinutesForDate(date, targetMinutes),
|
|
breakOk: '',
|
|
warnings: ''
|
|
});
|
|
});
|
|
}
|
|
return days;
|
|
}
|
|
|
|
|
|
function targetMinutesForDate(date, weekdayTargetMinutes) {
|
|
const weekday = getWeekdayIndex(date);
|
|
// 0 = Sonntag, 6 = Samstag. Rufbereitschafts-/Wochenendarbeit zählt komplett als Pluszeit.
|
|
if (weekday === 0 || weekday === 6) return 0;
|
|
return weekdayTargetMinutes;
|
|
}
|
|
|
|
function getWeekdayIndex(date) {
|
|
const match = String(date).match(/^(\d{2})\.(\d{2})\.(\d{4})$/);
|
|
if (!match) return null;
|
|
const day = Number(match[1]);
|
|
const month = Number(match[2]) - 1;
|
|
const year = Number(match[3]);
|
|
return new Date(year, month, day).getDay();
|
|
}
|
|
|
|
function weekdayNameFromDate(date) {
|
|
const names = ['Sonntag', 'Montag', 'Dienstag', 'Mittwoch', 'Donnerstag', 'Freitag', 'Samstag'];
|
|
const index = getWeekdayIndex(date);
|
|
return index === null ? '' : names[index];
|
|
}
|
|
|
|
function groupByMonth(days) {
|
|
const map = new Map();
|
|
for (const day of days) {
|
|
const key = day.month || 'Unbekannt';
|
|
if (!map.has(key)) {
|
|
map.set(key, { month: key, days: 0, workMinutes: 0, targetMinutes: 0, saldoMinutes: 0 });
|
|
}
|
|
const month = map.get(key);
|
|
month.days += 1;
|
|
month.workMinutes += day.workMinutes;
|
|
month.targetMinutes += day.targetMinutes;
|
|
month.saldoMinutes += day.saldoMinutes;
|
|
}
|
|
return [...map.values()];
|
|
}
|
|
|
|
function parseCsv(text) {
|
|
const rows = [];
|
|
let row = [];
|
|
let value = '';
|
|
let inQuotes = false;
|
|
|
|
for (let i = 0; i < text.length; i++) {
|
|
const char = text[i];
|
|
const next = text[i + 1];
|
|
|
|
if (char === '"') {
|
|
if (inQuotes && next === '"') {
|
|
value += '"';
|
|
i++;
|
|
} else {
|
|
inQuotes = !inQuotes;
|
|
}
|
|
continue;
|
|
}
|
|
|
|
if (char === ',' && !inQuotes) {
|
|
row.push(value);
|
|
value = '';
|
|
continue;
|
|
}
|
|
|
|
if ((char === '\n' || char === '\r') && !inQuotes) {
|
|
if (char === '\r' && next === '\n') i++;
|
|
row.push(value);
|
|
rows.push(row);
|
|
row = [];
|
|
value = '';
|
|
continue;
|
|
}
|
|
|
|
value += char;
|
|
}
|
|
|
|
if (value.length || row.length) {
|
|
row.push(value);
|
|
rows.push(row);
|
|
}
|
|
|
|
return rows;
|
|
}
|
|
|
|
function extractDurations(text) {
|
|
const matches = [...text.matchAll(/=(\d{1,3}):(\d{2})/g)];
|
|
return matches.map(match => Number(match[1]) * 60 + Number(match[2]));
|
|
}
|
|
|
|
function extractSingleDuration(text) {
|
|
const match = String(text).match(/=(\d{1,3}):(\d{2})/);
|
|
if (!match) return null;
|
|
return Number(match[1]) * 60 + Number(match[2]);
|
|
}
|
|
|
|
function extractDates(text) {
|
|
return [...text.matchAll(/(\d{2}\.\d{2}\.\d{4})\s+\d{2}:\d{2}\s+-/g)].map(match => match[1]);
|
|
}
|
|
|
|
function monthNameFromDate(date) {
|
|
const match = String(date).match(/^(\d{2})\.(\d{2})\.(\d{4})$/);
|
|
if (!match) return 'Unbekannt';
|
|
const names = ['Januar', 'Februar', 'März', 'April', 'Mai', 'Juni', 'Juli', 'August', 'September', 'Oktober', 'November', 'Dezember'];
|
|
const monthIndex = Number(match[2]) - 1;
|
|
return `${names[monthIndex] || 'Unbekannt'} ${match[3]}`;
|
|
}
|
|
|
|
function cleanCell(value) {
|
|
const text = String(value ?? '').trim();
|
|
return text === '-' ? '' : text;
|
|
}
|
|
|
|
function timeToMinutes(time) {
|
|
const [hours, minutes] = time.split(':').map(Number);
|
|
return hours * 60 + minutes;
|
|
}
|
|
|
|
function formatDuration(minutes, signed = false) {
|
|
const sign = minutes < 0 ? '-' : signed && minutes > 0 ? '+' : signed ? '±' : '';
|
|
const abs = Math.abs(minutes);
|
|
const hours = Math.floor(abs / 60);
|
|
const mins = abs % 60;
|
|
return `${sign}${hours}:${String(mins).padStart(2, '0')}`;
|
|
}
|
|
|
|
function saldoClass(minutes) {
|
|
if (minutes > 0) return 'positive';
|
|
if (minutes < 0) return 'negative';
|
|
return 'neutral';
|
|
}
|
|
|
|
function render(result) {
|
|
hideError();
|
|
summary.classList.remove('hidden');
|
|
results.classList.remove('hidden');
|
|
|
|
document.getElementById('totalDays').textContent = result.totals.days;
|
|
document.getElementById('totalWork').textContent = formatDuration(result.totals.workMinutes);
|
|
document.getElementById('totalTarget').textContent = formatDuration(result.totals.targetMinutes);
|
|
|
|
const totalSaldo = document.getElementById('totalSaldo');
|
|
totalSaldo.textContent = formatDuration(result.totals.saldoMinutes, true);
|
|
totalSaldo.className = saldoClass(result.totals.saldoMinutes);
|
|
|
|
monthTable.innerHTML = result.months.map(month => `
|
|
<tr>
|
|
<td>${escapeHtml(month.month)}</td>
|
|
<td>${month.days}</td>
|
|
<td>${formatDuration(month.workMinutes)}</td>
|
|
<td>${formatDuration(month.targetMinutes)}</td>
|
|
<td class="${saldoClass(month.saldoMinutes)}">${formatDuration(month.saldoMinutes, true)}</td>
|
|
</tr>
|
|
`).join('');
|
|
|
|
dayTable.innerHTML = result.days.map(day => `
|
|
<tr>
|
|
<td>${escapeHtml(day.date)}</td>
|
|
<td>${escapeHtml(day.weekday || '')}</td>
|
|
<td>${escapeHtml(day.start || '')}</td>
|
|
<td>${escapeHtml(day.end || '')}</td>
|
|
<td>${formatDuration(day.workMinutes)}</td>
|
|
<td>${formatDuration(day.targetMinutes)}</td>
|
|
<td class="${saldoClass(day.saldoMinutes)}">${formatDuration(day.saldoMinutes, true)}</td>
|
|
<td>${escapeHtml(day.breakOk || '')}</td>
|
|
<td>${escapeHtml(day.warnings || '')}</td>
|
|
</tr>
|
|
`).join('');
|
|
}
|
|
|
|
function exportResultCsv(result) {
|
|
const rows = [
|
|
['Bereich', 'Monat/Datum', 'Arbeitstage', 'Arbeitszeit', 'Sollzeit', 'Saldo'],
|
|
['Gesamt', '', result.totals.days, formatDuration(result.totals.workMinutes), formatDuration(result.totals.targetMinutes), formatDuration(result.totals.saldoMinutes, true)],
|
|
[],
|
|
['Monat', 'Monat/Datum', 'Arbeitstage', 'Arbeitszeit', 'Sollzeit', 'Saldo'],
|
|
...result.months.map(m => ['Monat', m.month, m.days, formatDuration(m.workMinutes), formatDuration(m.targetMinutes), formatDuration(m.saldoMinutes, true)]),
|
|
[],
|
|
['Tag', 'Datum', 'Wochentag', 'Beginn', 'Ende', 'Arbeitszeit', 'Sollzeit', 'Saldo', 'Ruhepause', 'Warnungen'],
|
|
...result.days.map(d => ['Tag', d.date, d.weekday || '', d.start, d.end, formatDuration(d.workMinutes), formatDuration(d.targetMinutes), formatDuration(d.saldoMinutes, true), d.breakOk, d.warnings])
|
|
];
|
|
|
|
const csv = rows.map(row => row.map(csvEscape).join(';')).join('\n');
|
|
const blob = new Blob([csv], { type: 'text/csv;charset=utf-8' });
|
|
const url = URL.createObjectURL(blob);
|
|
const link = document.createElement('a');
|
|
link.href = url;
|
|
link.download = 'arbeitszeit-auswertung.csv';
|
|
link.click();
|
|
URL.revokeObjectURL(url);
|
|
}
|
|
|
|
function csvEscape(value) {
|
|
const text = String(value ?? '');
|
|
if (/[;"\n\r]/.test(text)) return `"${text.replace(/"/g, '""')}"`;
|
|
return text;
|
|
}
|
|
|
|
function showError(message) {
|
|
errorBox.textContent = message;
|
|
errorBox.classList.remove('hidden');
|
|
summary.classList.add('hidden');
|
|
results.classList.add('hidden');
|
|
}
|
|
|
|
function hideError() {
|
|
errorBox.textContent = '';
|
|
errorBox.classList.add('hidden');
|
|
}
|
|
|
|
function escapeHtml(value) {
|
|
return String(value)
|
|
.replace(/&/g, '&')
|
|
.replace(/</g, '<')
|
|
.replace(/>/g, '>')
|
|
.replace(/"/g, '"')
|
|
.replace(/'/g, ''');
|
|
}
|