463 lines
13 KiB
JavaScript
463 lines
13 KiB
JavaScript
const APP_VERSION = 'v0.41.1';
|
|
|
|
const fileInput = document.getElementById('csvFile');
|
|
const dropZone = document.getElementById('dropZone');
|
|
const targetTimeInput = document.getElementById('targetTime');
|
|
const federalStateInput = document.getElementById('federalState');
|
|
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');
|
|
const appVersion = document.getElementById('appVersion');
|
|
|
|
if (appVersion) appVersion.textContent = APP_VERSION;
|
|
|
|
let lastResult = null;
|
|
let lastFileText = null;
|
|
|
|
fileInput.addEventListener('change', event => {
|
|
const file = event.target.files[0];
|
|
if (file) readFile(file);
|
|
});
|
|
|
|
targetTimeInput.addEventListener('change', rerunAnalysis);
|
|
federalStateInput.addEventListener('change', rerunAnalysis);
|
|
|
|
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 rerunAnalysis() {
|
|
if (lastFileText) analyseCsv(lastFileText);
|
|
}
|
|
|
|
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) {
|
|
hideError();
|
|
|
|
const rows = parseCsv(text);
|
|
if (rows.length < 2) {
|
|
showError('Die CSV-Datei enthält keine auswertbaren Daten.');
|
|
return;
|
|
}
|
|
|
|
const headers = rows[0].map(normalizeHeader);
|
|
const dataRows = rows.slice(1).filter(row => row.some(cell => String(cell || '').trim() !== ''));
|
|
|
|
const targetMinutes = timeToMinutes(targetTimeInput.value || '08:00');
|
|
const state = federalStateInput.value || 'NW';
|
|
|
|
const dailyRows = analyseDailyRows(headers, dataRows, targetMinutes, state);
|
|
if (!dailyRows.length) {
|
|
showError('Es wurden keine Arbeitstage mit auswertbarer Dauer gefunden.');
|
|
return;
|
|
}
|
|
|
|
const monthlyRows = groupByMonth(dailyRows);
|
|
const totals = dailyRows.reduce((acc, row) => {
|
|
acc.days += row.workMinutes > 0 ? 1 : 0;
|
|
acc.work += row.workMinutes;
|
|
acc.target += row.targetMinutes;
|
|
acc.saldo += row.saldoMinutes;
|
|
acc.holidays += row.isHoliday ? 1 : 0;
|
|
return acc;
|
|
}, { days: 0, work: 0, target: 0, saldo: 0, holidays: 0 });
|
|
|
|
lastResult = { dailyRows, monthlyRows, totals, state, version: APP_VERSION };
|
|
renderResult(lastResult);
|
|
}
|
|
|
|
function analyseDailyRows(headers, dataRows, targetMinutes, state) {
|
|
const tagIndex = headers.indexOf('tag');
|
|
const beginnIndex = headers.indexOf('arbeitstag beginn');
|
|
const endeIndex = headers.indexOf('arbeitstag ende');
|
|
const dauerIndex = headers.indexOf('dauer arbeitstag');
|
|
const pauseIndex = headers.indexOf('ruhepause eingehalten');
|
|
const warningIndex = headers.indexOf('warnungen');
|
|
|
|
if (tagIndex === -1 || dauerIndex === -1) {
|
|
throwCsvError('Das Tagesformat wurde nicht erkannt. Erwartet werden mindestens die Spalten "Tag" und "Dauer Arbeitstag".');
|
|
}
|
|
|
|
formatHint.textContent = `Erkanntes Format: Tagesauswertung | Feiertagsregel: ${stateLabel(state)} | Tool-Version: ${APP_VERSION}`;
|
|
|
|
return dataRows.map(row => {
|
|
const dateText = cleanCell(row[tagIndex]);
|
|
const date = parseGermanDate(dateText);
|
|
if (!date) return null;
|
|
|
|
const durationText = cleanCell(row[dauerIndex]);
|
|
const workMinutes = extractDurationMinutes(durationText);
|
|
if (workMinutes === null) return null;
|
|
|
|
const weekdayNumber = date.getDay();
|
|
const isWeekend = weekdayNumber === 0 || weekdayNumber === 6;
|
|
const holidayName = getHolidayName(date, state);
|
|
const isHoliday = Boolean(holidayName);
|
|
const dayTargetMinutes = isWeekend || isHoliday ? 0 : targetMinutes;
|
|
const saldoMinutes = workMinutes - dayTargetMinutes;
|
|
|
|
return {
|
|
date,
|
|
dateText,
|
|
monthKey: `${date.getFullYear()}-${String(date.getMonth() + 1).padStart(2, '0')}`,
|
|
monthLabel: date.toLocaleDateString('de-DE', { month: 'long', year: 'numeric' }),
|
|
weekday: weekdayLabel(date),
|
|
begin: cleanCell(row[beginnIndex]) || '-',
|
|
end: cleanCell(row[endeIndex]) || '-',
|
|
workMinutes,
|
|
targetMinutes: dayTargetMinutes,
|
|
saldoMinutes,
|
|
holidayName: holidayName || '-',
|
|
isHoliday,
|
|
isWeekend,
|
|
pause: cleanCell(row[pauseIndex]) || '-',
|
|
warnings: cleanCell(row[warningIndex]) || '-'
|
|
};
|
|
}).filter(Boolean);
|
|
}
|
|
|
|
function groupByMonth(dailyRows) {
|
|
const months = new Map();
|
|
|
|
dailyRows.forEach(row => {
|
|
if (!months.has(row.monthKey)) {
|
|
months.set(row.monthKey, {
|
|
key: row.monthKey,
|
|
label: row.monthLabel,
|
|
days: 0,
|
|
holidays: 0,
|
|
work: 0,
|
|
target: 0,
|
|
saldo: 0
|
|
});
|
|
}
|
|
|
|
const month = months.get(row.monthKey);
|
|
month.days += row.workMinutes > 0 ? 1 : 0;
|
|
month.holidays += row.isHoliday ? 1 : 0;
|
|
month.work += row.workMinutes;
|
|
month.target += row.targetMinutes;
|
|
month.saldo += row.saldoMinutes;
|
|
});
|
|
|
|
return [...months.values()].sort((a, b) => a.key.localeCompare(b.key));
|
|
}
|
|
|
|
function renderResult(result) {
|
|
summary.classList.remove('hidden');
|
|
results.classList.remove('hidden');
|
|
|
|
document.getElementById('totalDays').textContent = String(result.totals.days);
|
|
document.getElementById('totalWork').textContent = minutesToHours(result.totals.work);
|
|
document.getElementById('totalTarget').textContent = minutesToHours(result.totals.target);
|
|
|
|
const saldoElement = document.getElementById('totalSaldo');
|
|
saldoElement.textContent = minutesToSignedHours(result.totals.saldo);
|
|
saldoElement.className = saldoClass(result.totals.saldo);
|
|
|
|
monthTable.innerHTML = result.monthlyRows.map(month => `
|
|
<tr>
|
|
<td>${escapeHtml(month.label)}</td>
|
|
<td>${month.days}</td>
|
|
<td>${month.holidays}</td>
|
|
<td>${minutesToHours(month.work)}</td>
|
|
<td>${minutesToHours(month.target)}</td>
|
|
<td class="${saldoClass(month.saldo)}">${minutesToSignedHours(month.saldo)}</td>
|
|
</tr>
|
|
`).join('');
|
|
|
|
dayTable.innerHTML = result.dailyRows.map(row => `
|
|
<tr class="${row.isHoliday ? 'holiday-row' : row.isWeekend ? 'weekend-row' : ''}">
|
|
<td>${escapeHtml(row.dateText)}</td>
|
|
<td>${escapeHtml(row.weekday)}</td>
|
|
<td>${escapeHtml(row.begin)}</td>
|
|
<td>${escapeHtml(row.end)}</td>
|
|
<td>${minutesToHours(row.workMinutes)}</td>
|
|
<td>${minutesToHours(row.targetMinutes)}</td>
|
|
<td class="${saldoClass(row.saldoMinutes)}">${minutesToSignedHours(row.saldoMinutes)}</td>
|
|
<td>${escapeHtml(row.holidayName)}</td>
|
|
<td>${escapeHtml(row.pause)}</td>
|
|
<td>${escapeHtml(row.warnings)}</td>
|
|
</tr>
|
|
`).join('');
|
|
}
|
|
|
|
function exportResultCsv(result) {
|
|
const lines = [
|
|
['Tool-Version', APP_VERSION],
|
|
['Bundesland', stateLabel(result.state)],
|
|
[],
|
|
['Datum', 'Wochentag', 'Beginn', 'Ende', 'Arbeitszeit', 'Sollzeit', 'Saldo', 'Feiertag', 'Ruhepause', 'Warnungen']
|
|
];
|
|
|
|
result.dailyRows.forEach(row => {
|
|
lines.push([
|
|
row.dateText,
|
|
row.weekday,
|
|
row.begin,
|
|
row.end,
|
|
minutesToHours(row.workMinutes),
|
|
minutesToHours(row.targetMinutes),
|
|
minutesToSignedHours(row.saldoMinutes),
|
|
row.holidayName,
|
|
row.pause,
|
|
row.warnings
|
|
]);
|
|
});
|
|
|
|
const csv = lines.map(line => line.map(csvEscape).join(';')).join('\n');
|
|
const blob = new Blob([`\ufeff${csv}`], { type: 'text/csv;charset=utf-8' });
|
|
const url = URL.createObjectURL(blob);
|
|
const a = document.createElement('a');
|
|
a.href = url;
|
|
a.download = `qplanner-arbeitszeit-auswertung-${APP_VERSION}.csv`;
|
|
a.click();
|
|
URL.revokeObjectURL(url);
|
|
}
|
|
|
|
function parseCsv(text) {
|
|
const rows = [];
|
|
let row = [];
|
|
let cell = '';
|
|
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 === '"') {
|
|
cell += '"';
|
|
i++;
|
|
} else {
|
|
inQuotes = !inQuotes;
|
|
}
|
|
continue;
|
|
}
|
|
|
|
if (char === ',' && !inQuotes) {
|
|
row.push(cell);
|
|
cell = '';
|
|
continue;
|
|
}
|
|
|
|
if ((char === '\n' || char === '\r') && !inQuotes) {
|
|
if (char === '\r' && next === '\n') i++;
|
|
row.push(cell);
|
|
rows.push(row);
|
|
row = [];
|
|
cell = '';
|
|
continue;
|
|
}
|
|
|
|
cell += char;
|
|
}
|
|
|
|
if (cell.length || row.length) {
|
|
row.push(cell);
|
|
rows.push(row);
|
|
}
|
|
|
|
return rows;
|
|
}
|
|
|
|
function extractDurationMinutes(text) {
|
|
if (!text || text === '-') return null;
|
|
const match = text.match(/(?:=|^)(\d{1,3}):(\d{2})/);
|
|
if (!match) return null;
|
|
return Number(match[1]) * 60 + Number(match[2]);
|
|
}
|
|
|
|
function parseGermanDate(value) {
|
|
const match = String(value || '').trim().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]);
|
|
const date = new Date(year, month, day);
|
|
if (date.getFullYear() !== year || date.getMonth() !== month || date.getDate() !== day) return null;
|
|
return date;
|
|
}
|
|
|
|
function timeToMinutes(time) {
|
|
const [hours, minutes] = String(time || '08:00').split(':').map(Number);
|
|
return (hours || 0) * 60 + (minutes || 0);
|
|
}
|
|
|
|
function minutesToHours(minutes) {
|
|
const abs = Math.abs(minutes);
|
|
const hours = Math.floor(abs / 60);
|
|
const mins = abs % 60;
|
|
return `${hours}:${String(mins).padStart(2, '0')}`;
|
|
}
|
|
|
|
function minutesToSignedHours(minutes) {
|
|
if (minutes === 0) return '±0:00';
|
|
return `${minutes > 0 ? '+' : '-'}${minutesToHours(minutes)}`;
|
|
}
|
|
|
|
function saldoClass(minutes) {
|
|
if (minutes > 0) return 'positive';
|
|
if (minutes < 0) return 'negative';
|
|
return 'neutral';
|
|
}
|
|
|
|
function cleanCell(value) {
|
|
const text = String(value ?? '').trim();
|
|
return text === '' ? '-' : text;
|
|
}
|
|
|
|
function normalizeHeader(value) {
|
|
return String(value || '').trim().toLowerCase();
|
|
}
|
|
|
|
function weekdayLabel(date) {
|
|
return date.toLocaleDateString('de-DE', { weekday: 'long' });
|
|
}
|
|
|
|
function stateLabel(state) {
|
|
const labels = {
|
|
NW: 'Nordrhein-Westfalen',
|
|
BY: 'Bayern',
|
|
BY_MARIA: 'Bayern inkl. Mariä Himmelfahrt',
|
|
NI: 'Niedersachsen',
|
|
HH: 'Hamburg'
|
|
};
|
|
return labels[state] || state;
|
|
}
|
|
|
|
function getHolidayName(date, state) {
|
|
const year = date.getFullYear();
|
|
const key = dateKey(date);
|
|
const easter = easterSunday(year);
|
|
|
|
const holidays = new Map([
|
|
[`${year}-01-01`, 'Neujahr'],
|
|
[`${year}-05-01`, 'Tag der Arbeit'],
|
|
[`${year}-10-03`, 'Tag der Deutschen Einheit'],
|
|
[`${year}-12-25`, '1. Weihnachtstag'],
|
|
[`${year}-12-26`, '2. Weihnachtstag'],
|
|
[dateKey(addDays(easter, -2)), 'Karfreitag'],
|
|
[dateKey(addDays(easter, 1)), 'Ostermontag'],
|
|
[dateKey(addDays(easter, 39)), 'Christi Himmelfahrt'],
|
|
[dateKey(addDays(easter, 50)), 'Pfingstmontag']
|
|
]);
|
|
|
|
if (state === 'BY' || state === 'BY_MARIA') {
|
|
holidays.set(`${year}-01-06`, 'Heilige Drei Könige');
|
|
holidays.set(dateKey(addDays(easter, 60)), 'Fronleichnam');
|
|
holidays.set(`${year}-11-01`, 'Allerheiligen');
|
|
if (state === 'BY_MARIA') holidays.set(`${year}-08-15`, 'Mariä Himmelfahrt');
|
|
}
|
|
|
|
if (state === 'NW') {
|
|
holidays.set(dateKey(addDays(easter, 60)), 'Fronleichnam');
|
|
holidays.set(`${year}-11-01`, 'Allerheiligen');
|
|
}
|
|
|
|
if (state === 'NI' || state === 'HH') {
|
|
holidays.set(`${year}-10-31`, 'Reformationstag');
|
|
}
|
|
|
|
return holidays.get(key) || null;
|
|
}
|
|
|
|
function easterSunday(year) {
|
|
const a = year % 19;
|
|
const b = Math.floor(year / 100);
|
|
const c = year % 100;
|
|
const d = Math.floor(b / 4);
|
|
const e = b % 4;
|
|
const f = Math.floor((b + 8) / 25);
|
|
const g = Math.floor((b - f + 1) / 3);
|
|
const h = (19 * a + b - d - g + 15) % 30;
|
|
const i = Math.floor(c / 4);
|
|
const k = c % 4;
|
|
const l = (32 + 2 * e + 2 * i - h - k) % 7;
|
|
const m = Math.floor((a + 11 * h + 22 * l) / 451);
|
|
const month = Math.floor((h + l - 7 * m + 114) / 31) - 1;
|
|
const day = ((h + l - 7 * m + 114) % 31) + 1;
|
|
return new Date(year, month, day);
|
|
}
|
|
|
|
function addDays(date, days) {
|
|
const result = new Date(date);
|
|
result.setDate(result.getDate() + days);
|
|
return result;
|
|
}
|
|
|
|
function dateKey(date) {
|
|
return `${date.getFullYear()}-${String(date.getMonth() + 1).padStart(2, '0')}-${String(date.getDate()).padStart(2, '0')}`;
|
|
}
|
|
|
|
function csvEscape(value) {
|
|
const text = String(value ?? '');
|
|
return `"${text.replaceAll('"', '""')}"`;
|
|
}
|
|
|
|
function escapeHtml(value) {
|
|
return String(value ?? '').replace(/[&<>'"]/g, char => ({
|
|
'&': '&',
|
|
'<': '<',
|
|
'>': '>',
|
|
"'": ''',
|
|
'"': '"'
|
|
}[char]));
|
|
}
|
|
|
|
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 throwCsvError(message) {
|
|
showError(message);
|
|
throw new Error(message);
|
|
}
|