1417 lines
65 KiB
JavaScript
1417 lines
65 KiB
JavaScript
/*
|
||
* Express-Server der Projektübersicht: liefert die statischen Frontend-Dateien aus
|
||
* public/ (Test.html unter "/") und stellt den Frontend-Modulen unter public/js/
|
||
* eine JSON-API zum Laden und Speichern der Sitzungsdaten bereit. Die Daten liegen
|
||
* in einer SQLite-Datenbank (node:sqlite, Node >= 22.13) unter sessions/projektuebersicht.db.
|
||
*
|
||
* Das Schema ist voll relational normalisiert: statt eines grossen JSON-Dokuments
|
||
* pro Woche gibt es eigene Tabellen für Wochen-Metadaten, Wochen-Personen, Rollen,
|
||
* Anwesenheit, Projekte, LPH-Werte, Projekt-Zuordnungen, Projektpläne, TODOs,
|
||
* Meilensteine, Abhängigkeiten sowie die globalen Personen inkl. Rollen und
|
||
* Abwesenheiten. Die HTTP-API bleibt davon unberührt: Das Frontend sendet und
|
||
* erhält weiterhin komplette Wochen- und Personen-Objekte (JSON); der Server
|
||
* verteilt sie beim Speichern auf die Tabellen und setzt sie beim Laden wieder
|
||
* Zusammen (Serializer/Deserializer).
|
||
*
|
||
* Eine bestehende Datenbank aus dem alten Ein-Dokument-pro-Woche-Format wird
|
||
* automatisch auf das relationale Schema umgestellt.
|
||
*
|
||
* Standardport ist 3003, überschreibbar über die Umgebungsvariable PORT.
|
||
* Endpunkte: GET /, GET /health, GET /load-all, POST /save-week, POST /save-people,
|
||
* POST /delete-week, GET /export-csv (ZIP mit CSV je logischer Entität je Abteilung),
|
||
* GET /backup (SQLite-Snapshot als .db-Download; zusätzlich automatische Backups
|
||
* nach sessions/backups/ beim Serverstart), POST /import (Backup-Wiederherstellung,
|
||
* roher Binär-Body, schema-validiert).
|
||
* Ungültige Abteilungen fallen auf ELT zurück.
|
||
*/
|
||
const express = require('express');
|
||
const fs = require('fs');
|
||
const path = require('path');
|
||
const os = require('os');
|
||
const zlib = require('zlib');
|
||
const { DatabaseSync } = require('node:sqlite');
|
||
const app = express();
|
||
|
||
// Wichtige Konstanten: Serverport (PORT-Env) und die gültigen Abteilungsordner
|
||
const PORT = process.env.PORT || 3003;
|
||
const VALID_DEPARTMENTS = new Set(['ELT','HLS','ARCH']);
|
||
|
||
// Globale Middleware: CORS, JSON-Body-Limit (2 MB) und statisches Serving von public/ ohne Cache für Skripte/Styles
|
||
app.use(require('cors')());
|
||
app.use(express.json({ limit: '2mb' }));
|
||
app.use(express.static(path.join(__dirname, 'public'), {
|
||
setHeaders: (res, path) => {
|
||
if(path.endsWith('.js') || path.endsWith('.css') || path.endsWith('.html')){
|
||
res.set('Cache-Control', 'no-cache, no-store, must-revalidate');
|
||
res.set('Pragma', 'no-cache');
|
||
res.set('Expires', '0');
|
||
}
|
||
}
|
||
}));
|
||
|
||
// Datenverzeichnis sessions/ bei Bedarf anlegen (bind-mounted in Docker, enthält die DB)
|
||
const SESSIONS_DIR = path.join(__dirname, 'sessions');
|
||
if(!fs.existsSync(SESSIONS_DIR)) fs.mkdirSync(SESSIONS_DIR, { recursive: true });
|
||
|
||
// SQLite-Datenbank öffnen (wird angelegt, falls nicht vorhanden). Der Pfad ist
|
||
// über die Umgebungsvariable DB_PATH übersteuerbar (für Tests/Backups), sonst
|
||
// standardmäßig sessions/projektuebersicht.db.
|
||
const DB_PATH = process.env.DB_PATH ? path.resolve(process.env.DB_PATH) : path.join(SESSIONS_DIR, 'projektuebersicht.db');
|
||
const db = new DatabaseSync(DB_PATH);
|
||
db.exec('PRAGMA journal_mode = WAL;');
|
||
db.exec('PRAGMA foreign_keys = ON;');
|
||
db.exec('PRAGMA busy_timeout = 5000;'); // 5 s warten statt SQLITE_BUSY-Fehlern bei parallelen Schreibern
|
||
|
||
// ---------- Schema (voll relational normalisiert) ----------
|
||
|
||
// Der serialisierte Datenbestand einer Woche besteht aus folgenden Entitäten:
|
||
// - weeks: Metadaten (id, label, date, locked)
|
||
// - week_people / week_people_roles: personen-Kopien pro Woche
|
||
// - attendance: Anwesenheit pro Woche/Person
|
||
// - projects: Projekt-Metadaten pro Woche
|
||
// - project_lph: LPH-Fortschrittswerte pro Projekt
|
||
// - project_assign: Zuordnungen (Haupt/Optional) pro Projekt/Person/Rolle
|
||
// - project_plan: Plan-Kopf (start, end, progress) pro Projekt
|
||
// - plan_people: im Plan beteiligte Personen
|
||
// - plan_todos / plan_todo_assignees / plan_todo_deps: TODO-Listen
|
||
// - plan_milestones / plan_milestone_deps: Meilensteine
|
||
// - people / person_roles / person_absences: globale Personen pro Abteilung
|
||
|
||
db.exec(`CREATE TABLE IF NOT EXISTS weeks (
|
||
department TEXT NOT NULL,
|
||
label TEXT NOT NULL,
|
||
week_id TEXT NOT NULL DEFAULT '',
|
||
date TEXT NOT NULL DEFAULT '',
|
||
locked INTEGER NOT NULL DEFAULT 0,
|
||
updated_at TEXT NOT NULL DEFAULT '',
|
||
PRIMARY KEY (department, label)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS week_people (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
person_id TEXT NOT NULL,
|
||
name TEXT NOT NULL DEFAULT '',
|
||
full_name TEXT NOT NULL DEFAULT '',
|
||
array_order INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, person_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS week_people_roles (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
person_id TEXT NOT NULL,
|
||
role TEXT NOT NULL,
|
||
role_order INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, person_id, role)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS attendance (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
person_id TEXT NOT NULL,
|
||
present INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, person_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS projects (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
nr TEXT NOT NULL DEFAULT '',
|
||
name TEXT NOT NULL DEFAULT '',
|
||
status TEXT NOT NULL DEFAULT '',
|
||
"group" TEXT NOT NULL DEFAULT '',
|
||
array_order INTEGER NOT NULL DEFAULT 0,
|
||
order_value INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS project_lph (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
lph TEXT NOT NULL,
|
||
value TEXT NOT NULL DEFAULT '',
|
||
PRIMARY KEY (department, week_label, project_id, lph)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS project_assign (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
person_id TEXT NOT NULL,
|
||
role TEXT NOT NULL DEFAULT '',
|
||
state INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id, person_id, role)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS project_aufwand (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
lph TEXT NOT NULL,
|
||
role TEXT NOT NULL DEFAULT '',
|
||
value REAL NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id, lph, role)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS project_aktuelle_lp (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
lph TEXT NOT NULL,
|
||
PRIMARY KEY (department, week_label, project_id, lph)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS project_plan (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
start TEXT NOT NULL DEFAULT '',
|
||
end TEXT NOT NULL DEFAULT '',
|
||
progress INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_people (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
person_id TEXT NOT NULL,
|
||
array_order INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id, person_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_todos (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
todo_id TEXT NOT NULL,
|
||
title TEXT NOT NULL DEFAULT '',
|
||
start TEXT NOT NULL DEFAULT '',
|
||
end TEXT NOT NULL DEFAULT '',
|
||
done INTEGER NOT NULL DEFAULT 0,
|
||
progress INTEGER NOT NULL DEFAULT 0,
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id, todo_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_todo_assignees (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
todo_id TEXT NOT NULL,
|
||
person_id TEXT NOT NULL,
|
||
PRIMARY KEY (department, week_label, project_id, todo_id, person_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_todo_deps (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
todo_id TEXT NOT NULL,
|
||
depends_on_id TEXT NOT NULL,
|
||
PRIMARY KEY (department, week_label, project_id, todo_id, depends_on_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_milestones (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
milestone_id TEXT NOT NULL,
|
||
name TEXT NOT NULL DEFAULT '',
|
||
date TEXT NOT NULL DEFAULT '',
|
||
position TEXT NOT NULL DEFAULT '',
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id, milestone_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_milestone_deps (
|
||
department TEXT NOT NULL,
|
||
week_label TEXT NOT NULL,
|
||
project_id TEXT NOT NULL,
|
||
milestone_id TEXT NOT NULL,
|
||
depends_on_id TEXT NOT NULL,
|
||
PRIMARY KEY (department, week_label, project_id, milestone_id, depends_on_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS people (
|
||
department TEXT NOT NULL,
|
||
person_id TEXT NOT NULL,
|
||
name TEXT NOT NULL DEFAULT '',
|
||
full_name TEXT NOT NULL DEFAULT '',
|
||
email TEXT NOT NULL DEFAULT '',
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, person_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS person_roles (
|
||
department TEXT NOT NULL,
|
||
person_id TEXT NOT NULL,
|
||
role TEXT NOT NULL,
|
||
role_order INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, person_id, role)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS person_absences (
|
||
department TEXT NOT NULL,
|
||
person_id TEXT NOT NULL,
|
||
absence TEXT NOT NULL,
|
||
PRIMARY KEY (department, person_id, absence)
|
||
)`);
|
||
|
||
// Erlaubt ausschliesslich die gültigen Abteilungen (ELT|HLS|ARCH, sonst ELT)
|
||
function departmentKey(department){
|
||
return (typeof department === 'string' && VALID_DEPARTMENTS.has(department)) ? department : 'ELT';
|
||
}
|
||
|
||
// ---------- Migration: altes Ein-Dokument-Format -> relationales Schema ----------
|
||
|
||
// hasLegacyWeeksTable: erkennt das alte Schema an der Daten-Spalte in `weeks`.
|
||
function hasLegacyWeeksTable(){
|
||
const cols = db.prepare("SELECT name FROM pragma_table_info('weeks') WHERE name = 'data'").all();
|
||
return cols.length > 0;
|
||
}
|
||
|
||
// serializeWeek: zerlegt ein Wochen-Objekt (JSON vom Frontend) in die relationalen
|
||
// Tabellen. Wird in einer Transaktion vom Aufrufer verwendet (deleteWeekData +
|
||
// serializeWeek). Ersetzt alle Zeilen der Woche.
|
||
function serializeWeek(department, week){
|
||
const weekLabel = String(week.label || '');
|
||
const nowIso = new Date().toISOString(); // Revision: wird bei jedem Speichern aktualisiert
|
||
const upsertWeek = db.prepare(`INSERT INTO weeks (department, label, week_id, date, locked, updated_at) VALUES (?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(department, label) DO UPDATE SET week_id = excluded.week_id, date = excluded.date, locked = excluded.locked, updated_at = excluded.updated_at`);
|
||
upsertWeek.run(department, weekLabel, String(week.id || ''), String(week.date || ''), week.locked ? 1 : 0, nowIso);
|
||
|
||
const insWeekPerson = db.prepare(`INSERT INTO week_people (department, week_label, person_id, name, full_name, array_order)
|
||
VALUES (?, ?, ?, ?, ?, ?)`);
|
||
const insWeekPersonRole = db.prepare(`INSERT OR IGNORE INTO week_people_roles (department, week_label, person_id, role, role_order)
|
||
VALUES (?, ?, ?, ?, ?)`);
|
||
const insAttendance = db.prepare(`INSERT OR REPLACE INTO attendance (department, week_label, person_id, present)
|
||
VALUES (?, ?, ?, ?)`);
|
||
|
||
(Array.isArray(week.people) ? week.people : []).forEach((p, idx) => {
|
||
insWeekPerson.run(department, weekLabel, String(p.id || ''), String(p.name || ''), String(p.fullName || ''), idx);
|
||
(Array.isArray(p.role) ? p.role : []).forEach((r, ridx) => insWeekPersonRole.run(department, weekLabel, String(p.id || ''), String(r || ''), ridx));
|
||
});
|
||
const att = week.attendance || {};
|
||
Object.keys(att).forEach(pid => {
|
||
insAttendance.run(department, weekLabel, pid, att[pid] ? 1 : 0);
|
||
});
|
||
|
||
const insProject = db.prepare(`INSERT INTO projects (department, week_label, project_id, nr, name, status, "group", array_order, order_value)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`);
|
||
const insLph = db.prepare(`INSERT OR REPLACE INTO project_lph (department, week_label, project_id, lph, value)
|
||
VALUES (?, ?, ?, ?, ?)`);
|
||
const insAssign = db.prepare(`INSERT OR REPLACE INTO project_assign (department, week_label, project_id, person_id, role, state)
|
||
VALUES (?, ?, ?, ?, ?, ?)`);
|
||
const insAufwand = db.prepare(`INSERT OR REPLACE INTO project_aufwand (department, week_label, project_id, lph, role, value)
|
||
VALUES (?, ?, ?, ?, ?, ?)`);
|
||
const insAktuelleLp = db.prepare(`INSERT OR IGNORE INTO project_aktuelle_lp (department, week_label, project_id, lph)
|
||
VALUES (?, ?, ?, ?)`);
|
||
const insPlan = db.prepare(`INSERT OR REPLACE INTO project_plan (department, week_label, project_id, start, end, progress)
|
||
VALUES (?, ?, ?, ?, ?, ?)`);
|
||
const insPlanPerson = db.prepare(`INSERT OR IGNORE INTO plan_people (department, week_label, project_id, person_id, array_order)
|
||
VALUES (?, ?, ?, ?, ?)`);
|
||
const insTodo = db.prepare(`INSERT INTO plan_todos (department, week_label, project_id, todo_id, title, start, end, done, progress, sort_order)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
|
||
const insTodoAssignee = db.prepare(`INSERT OR IGNORE INTO plan_todo_assignees (department, week_label, project_id, todo_id, person_id)
|
||
VALUES (?, ?, ?, ?, ?)`);
|
||
const insTodoDep = db.prepare(`INSERT OR IGNORE INTO plan_todo_deps (department, week_label, project_id, todo_id, depends_on_id)
|
||
VALUES (?, ?, ?, ?, ?)`);
|
||
const insMilestone = db.prepare(`INSERT INTO plan_milestones (department, week_label, project_id, milestone_id, name, date, position, sort_order)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?)`);
|
||
const insMilestoneDep = db.prepare(`INSERT OR IGNORE INTO plan_milestone_deps (department, week_label, project_id, milestone_id, depends_on_id)
|
||
VALUES (?, ?, ?, ?, ?)`);
|
||
|
||
(Array.isArray(week.projects) ? week.projects : []).forEach((p, idx) => {
|
||
const projId = String(p.id || '');
|
||
insProject.run(department, weekLabel, projId, String(p.nr || ''), String(p.name || ''),
|
||
String(p.status || ''), String(p.group || ''), idx, typeof p.order === 'number' ? p.order : idx);
|
||
const lph = p.lph || {};
|
||
Object.keys(lph).forEach(l => insLph.run(department, weekLabel, projId, String(l || ''), String(lph[l] === undefined ? '' : lph[l])));
|
||
const assign = p.assign || {};
|
||
Object.keys(assign).forEach(key => {
|
||
// Key-Format: "personId|Rolle"
|
||
const sep = key.indexOf('|');
|
||
const pid = sep >= 0 ? key.slice(0, sep) : key;
|
||
const role = sep >= 0 ? key.slice(sep + 1) : '';
|
||
insAssign.run(department, weekLabel, projId, pid, role, Number(assign[key]) || 0);
|
||
});
|
||
const aufwand = p.aufwandProLP || {};
|
||
Object.keys(aufwand).forEach(lph => {
|
||
const roles = aufwand[lph] || {};
|
||
Object.keys(roles).forEach(role => {
|
||
const v = Number(roles[role]);
|
||
if(!isNaN(v)) insAufwand.run(department, weekLabel, projId, String(lph || ''), String(role || ''), v);
|
||
});
|
||
});
|
||
(Array.isArray(p.aktuelleLP) ? p.aktuelleLP : []).forEach(lph => {
|
||
insAktuelleLp.run(department, weekLabel, projId, String(lph || ''));
|
||
});
|
||
const plan = p.plan || null;
|
||
if(plan){
|
||
insPlan.run(department, weekLabel, projId, String(plan.start || ''), String(plan.end || ''), Number(plan.progress) || 0);
|
||
(Array.isArray(plan.people) ? plan.people : []).forEach((pid, pidx) => insPlanPerson.run(department, weekLabel, projId, String(pid || ''), pidx));
|
||
(Array.isArray(plan.todos) ? plan.todos : []).forEach((t, tidx) => {
|
||
insTodo.run(department, weekLabel, projId, String(t.id || ''), String(t.title || ''),
|
||
String(t.start || ''), String(t.end || ''), t.done ? 1 : 0, Number(t.progress) || 0, tidx);
|
||
(Array.isArray(t.assignees) ? t.assignees : []).forEach(pid => insTodoAssignee.run(department, weekLabel, projId, String(t.id || ''), String(pid || '')));
|
||
(Array.isArray(t.dependsOn) ? t.dependsOn : []).forEach(did => insTodoDep.run(department, weekLabel, projId, String(t.id || ''), String(did || '')));
|
||
});
|
||
(Array.isArray(plan.milestones) ? plan.milestones : []).forEach((m, midx) => {
|
||
insMilestone.run(department, weekLabel, projId, String(m.id || ''), String(m.name || ''), String(m.date || ''), String(m.position || ''), midx);
|
||
(Array.isArray(m.dependsOn) ? m.dependsOn : []).forEach(did => insMilestoneDep.run(department, weekLabel, projId, String(m.id || ''), String(did || '')));
|
||
});
|
||
}
|
||
});
|
||
// Rückgabe der Revision für den Aufrufer (saveWeekRelational -> Route)
|
||
return nowIso;
|
||
}
|
||
|
||
// deleteWeekData: entfernt alle relationalen Zeilen einer Woche (Abteilung + Label)
|
||
function deleteWeekData(department, weekLabel){
|
||
const tables = ['week_people_roles','week_people','attendance','project_lph','project_assign',
|
||
'project_aufwand','project_aktuelle_lp',
|
||
'project_plan','plan_people','plan_todos','plan_todo_assignees','plan_todo_deps',
|
||
'plan_milestones','plan_milestone_deps','projects'];
|
||
tables.forEach(t => {
|
||
db.prepare(`DELETE FROM ${t} WHERE department = ? AND week_label = ?`).run(department, weekLabel);
|
||
});
|
||
// Die Wochen-Tabelle selbst nutzt `label` statt `week_label`
|
||
db.prepare('DELETE FROM weeks WHERE department = ? AND label = ?').run(department, weekLabel);
|
||
}
|
||
|
||
// saveWeekRelational: speichert eine Woche vollständig relational (Transaktion).
|
||
// Rückgabe: die neu gesetzte Revision `updated_at` (ISO-Zeitstempel), damit der
|
||
// Client seine lokale Basis aktualisieren kann (sonst würde er beim nächsten
|
||
// Speichern einen falschen Konflikt mit sich selbst melden).
|
||
function saveWeekRelational(department, week){
|
||
const weekLabel = String(week.label || '');
|
||
const tx = db.exec('BEGIN');
|
||
try {
|
||
deleteWeekData(department, weekLabel);
|
||
const updatedAt = serializeWeek(department, week);
|
||
db.exec('COMMIT');
|
||
return updatedAt;
|
||
} catch(err){
|
||
db.exec('ROLLBACK');
|
||
throw err;
|
||
}
|
||
}
|
||
|
||
// serializeGlobalPeople: zerlegt die globale Personenliste (JSON-Array) in die
|
||
// Tabellen people/person_roles/person_absences. Ersetzt die gesamte Abteilungsliste.
|
||
function serializeGlobalPeople(department, people){
|
||
const del = db.prepare(`DELETE FROM people WHERE department = ?`);
|
||
const delRoles = db.prepare(`DELETE FROM person_roles WHERE department = ?`);
|
||
const delAbsences = db.prepare(`DELETE FROM person_absences WHERE department = ?`);
|
||
const tx = db.exec('BEGIN');
|
||
try {
|
||
del.run(department); delRoles.run(department); delAbsences.run(department);
|
||
const insPerson = db.prepare(`INSERT INTO people (department, person_id, name, full_name, email, sort_order)
|
||
VALUES (?, ?, ?, ?, ?, ?)`);
|
||
const insRole = db.prepare(`INSERT OR IGNORE INTO person_roles (department, person_id, role, role_order) VALUES (?, ?, ?, ?)`);
|
||
const insAbsence = db.prepare(`INSERT OR IGNORE INTO person_absences (department, person_id, absence) VALUES (?, ?, ?)`);
|
||
people.forEach((p, idx) => {
|
||
insPerson.run(department, String(p.id || ''), String(p.name || ''), String(p.fullName || ''), String(p.email || ''), idx);
|
||
(Array.isArray(p.role) ? p.role : []).forEach((r, ridx) => insRole.run(department, String(p.id || ''), String(r || ''), ridx));
|
||
(Array.isArray(p.absences) ? p.absences : []).forEach(a => insAbsence.run(department, String(p.id || ''), String(a || '')));
|
||
});
|
||
db.exec('COMMIT');
|
||
} catch(err){
|
||
db.exec('ROLLBACK');
|
||
throw err;
|
||
}
|
||
}
|
||
|
||
// loadGlobalPeople: setzt die globale Personenliste einer Abteilung aus den
|
||
// Tabellen people/person_roles/person_absences wieder zusammen. Rückgabe: Array.
|
||
function loadGlobalPeople(department){
|
||
const people = db.prepare('SELECT * FROM people WHERE department = ? ORDER BY sort_order').all(department);
|
||
const roles = db.prepare('SELECT * FROM person_roles WHERE department = ? ORDER BY role_order').all(department);
|
||
const absences = db.prepare('SELECT * FROM person_absences WHERE department = ?').all(department);
|
||
const rolesByPerson = {};
|
||
const absByPerson = {};
|
||
roles.forEach(r => { (rolesByPerson[r.person_id] = rolesByPerson[r.person_id] || []).push(r.role); });
|
||
absences.forEach(a => { (absByPerson[a.person_id] = absByPerson[a.person_id] || []).push(a.absence); });
|
||
return people.map(p => ({
|
||
id: p.person_id,
|
||
name: p.name,
|
||
fullName: p.full_name,
|
||
email: p.email,
|
||
role: rolesByPerson[p.person_id] || [],
|
||
absences: absByPerson[p.person_id] || [],
|
||
}));
|
||
}
|
||
|
||
// loadWeeks: setzt alle Wochen einer Abteilung aus den relationalen Tabellen
|
||
// wieder zu kompletten Wochen-Objekten zusammen (Format wie vom Frontend erwartet).
|
||
// Rückgabe: Array von Wochen-Objekten, sortiert nach Label.
|
||
function loadWeeks(department){
|
||
const weeks = db.prepare('SELECT * FROM weeks WHERE department = ? ORDER BY label').all(department);
|
||
const weekPeople = db.prepare('SELECT * FROM week_people WHERE department = ? ORDER BY array_order').all(department);
|
||
const weekRoles = db.prepare('SELECT * FROM week_people_roles WHERE department = ? ORDER BY role_order').all(department);
|
||
const attendance = db.prepare('SELECT * FROM attendance WHERE department = ?').all(department);
|
||
const projects = db.prepare('SELECT * FROM projects WHERE department = ? ORDER BY array_order').all(department);
|
||
const lph = db.prepare('SELECT * FROM project_lph WHERE department = ?').all(department);
|
||
const assign = db.prepare('SELECT * FROM project_assign WHERE department = ?').all(department);
|
||
const aufwand = db.prepare('SELECT * FROM project_aufwand WHERE department = ?').all(department);
|
||
const aktuelleLp = db.prepare('SELECT * FROM project_aktuelle_lp WHERE department = ?').all(department);
|
||
const plans = db.prepare('SELECT * FROM project_plan WHERE department = ?').all(department);
|
||
const planPeople = db.prepare('SELECT * FROM plan_people WHERE department = ? ORDER BY array_order').all(department);
|
||
const todos = db.prepare('SELECT * FROM plan_todos WHERE department = ? ORDER BY sort_order').all(department);
|
||
const todoAssignees = db.prepare('SELECT * FROM plan_todo_assignees WHERE department = ?').all(department);
|
||
const todoDeps = db.prepare('SELECT * FROM plan_todo_deps WHERE department = ?').all(department);
|
||
const milestones = db.prepare('SELECT * FROM plan_milestones WHERE department = ? ORDER BY sort_order').all(department);
|
||
const milestoneDeps = db.prepare('SELECT * FROM plan_milestone_deps WHERE department = ?').all(department);
|
||
|
||
// Index-Karten für schnellen Zugriff
|
||
const weekKey = (label) => department + '|' + label;
|
||
const projKey = (label, pid) => department + '|' + label + '|' + pid;
|
||
const todoKey = (label, pid, tid) => department + '|' + label + '|' + pid + '|' + tid;
|
||
const msKey = (label, pid, mid) => department + '|' + label + '|' + pid + '|' + mid;
|
||
|
||
const weekPeopleMap = {};
|
||
const weekRolesMap = {};
|
||
const attendanceMap = {};
|
||
weekPeople.forEach(r => {
|
||
const k = weekKey(r.week_label);
|
||
(weekPeopleMap[k] = weekPeopleMap[k] || [])[r.array_order] = { id: r.person_id, name: r.name, fullName: r.full_name };
|
||
});
|
||
weekRoles.forEach(r => {
|
||
const k = weekKey(r.week_label);
|
||
const arr = (weekRolesMap[k] = weekRolesMap[k] || {});
|
||
(arr[r.person_id] = arr[r.person_id] || []).push(r.role);
|
||
});
|
||
attendance.forEach(r => {
|
||
attendanceMap[weekKey(r.week_label) + '|' + r.person_id] = !!r.present;
|
||
});
|
||
|
||
const projectsMap = {};
|
||
projects.forEach(r => {
|
||
projectsMap[projKey(r.week_label, r.project_id)] = {
|
||
id: r.project_id, nr: r.nr, name: r.name, status: r.status, group: r.group,
|
||
lph: {}, assign: {}, order: r.order_value, plan: null,
|
||
_arrayOrder: r.array_order,
|
||
};
|
||
});
|
||
lph.forEach(r => { const p = projectsMap[projKey(r.week_label, r.project_id)]; if(p) p.lph[r.lph] = r.value; });
|
||
assign.forEach(r => {
|
||
const p = projectsMap[projKey(r.week_label, r.project_id)];
|
||
if(p) p.assign[r.person_id + (r.role ? '|' + r.role : '')] = r.state;
|
||
});
|
||
aufwand.forEach(r => {
|
||
const p = projectsMap[projKey(r.week_label, r.project_id)];
|
||
if(p){ p.aufwandProLP = p.aufwandProLP || {}; (p.aufwandProLP[r.lph] = p.aufwandProLP[r.lph] || {})[r.role] = r.value; }
|
||
});
|
||
aktuelleLp.forEach(r => {
|
||
const p = projectsMap[projKey(r.week_label, r.project_id)];
|
||
if(p){ p.aktuelleLP = p.aktuelleLP || []; if(!p.aktuelleLP.includes(r.lph)) p.aktuelleLP.push(r.lph); }
|
||
});
|
||
|
||
const planMap = {};
|
||
plans.forEach(r => { planMap[projKey(r.week_label, r.project_id)] = { start: r.start, end: r.end, progress: r.progress, people: [], todos: [], milestones: [] }; });
|
||
planPeople.forEach(r => {
|
||
const plan = planMap[projKey(r.week_label, r.project_id)];
|
||
if(plan) plan.people.push(r.person_id);
|
||
});
|
||
|
||
const todoMap = {};
|
||
const todoAssigneeMap = {};
|
||
const todoDepMap = {};
|
||
todos.forEach(r => {
|
||
const k = todoKey(r.week_label, r.project_id, r.todo_id);
|
||
todoMap[k] = { id: r.todo_id, title: r.title, start: r.start, end: r.end, done: !!r.done, dependsOn: [], assignees: [], progress: r.progress };
|
||
});
|
||
todoAssignees.forEach(r => {
|
||
const t = todoMap[todoKey(r.week_label, r.project_id, r.todo_id)];
|
||
if(t) t.assignees.push(r.person_id);
|
||
});
|
||
todoDeps.forEach(r => {
|
||
const t = todoMap[todoKey(r.week_label, r.project_id, r.todo_id)];
|
||
if(t) t.dependsOn.push(r.depends_on_id);
|
||
});
|
||
|
||
const msMap = {};
|
||
const msDepMap = {};
|
||
milestones.forEach(r => {
|
||
const k = msKey(r.week_label, r.project_id, r.milestone_id);
|
||
msMap[k] = { id: r.milestone_id, name: r.name, date: r.date, position: r.position, dependsOn: [] };
|
||
});
|
||
milestoneDeps.forEach(r => {
|
||
const m = msMap[msKey(r.week_label, r.project_id, r.milestone_id)];
|
||
if(m) m.dependsOn.push(r.depends_on_id);
|
||
});
|
||
|
||
// TODO- und Meilenstein-Objekte in die Projekt-Pläne einsortieren
|
||
Object.keys(todoMap).forEach(k => {
|
||
const parts = k.split('|');
|
||
const plan = planMap[department + '|' + parts[1] + '|' + parts[2]];
|
||
if(plan) plan.todos.push(todoMap[k]);
|
||
});
|
||
Object.keys(msMap).forEach(k => {
|
||
const parts = k.split('|');
|
||
const plan = planMap[department + '|' + parts[1] + '|' + parts[2]];
|
||
if(plan) plan.milestones.push(msMap[k]);
|
||
});
|
||
|
||
// Wochen-Objekte zusammensetzen
|
||
return weeks.map(w => {
|
||
const wkey = weekKey(w.label);
|
||
const people = (weekPeopleMap[wkey] || []).filter(Boolean).map(p => {
|
||
const roles = (weekRolesMap[wkey] || {})[p.id] || [];
|
||
const full = Object.assign({}, p);
|
||
if(roles.length) full.role = roles;
|
||
return full;
|
||
});
|
||
const att = {};
|
||
(weekPeopleMap[wkey] || []).filter(Boolean).forEach(p => {
|
||
const present = attendanceMap[wkey + '|' + p.id];
|
||
if(present !== undefined) att[p.id] = present;
|
||
});
|
||
const weekProjects = Object.keys(projectsMap)
|
||
.filter(k => k.startsWith(department + '|' + w.label + '|'))
|
||
.map(k => {
|
||
const p = projectsMap[k];
|
||
const plan = planMap[k];
|
||
if(plan) p.plan = plan;
|
||
return p;
|
||
})
|
||
.sort((a,b) => a._arrayOrder - b._arrayOrder)
|
||
.map(({ _arrayOrder, ...rest }) => rest);
|
||
return {
|
||
id: w.week_id, label: w.label, date: w.date,
|
||
people, projects: weekProjects, attendance: att, locked: !!w.locked,
|
||
updatedAt: w.updated_at || '',
|
||
};
|
||
});
|
||
}
|
||
|
||
// ---------- CSV-/ZIP-Export ----------
|
||
//
|
||
// GET /export-csv?department=<ELT|HLS|ARCH> liefert einen ZIP, der pro logischer
|
||
// Entität eine übersichtliche CSV-Datei enthält (Wochen, Personen, Projekte,
|
||
// Zuordnungen, Leistungsphasen, Pläne/TODOs/Meilensteine). Zielgruppe ist vor
|
||
// allem Excel; deshalb sind die CSVs UTF-8 mit BOM, CRLF-Zeilenenden und
|
||
// Semikolon als Trenner (MS-Excel-de standardmäßig). Das Trennzeichen lässt
|
||
// sich über das Query-Parameter `sep` übersteuern (z. B. `?sep=,`).
|
||
|
||
// CSV_TABLE: CRC32-Prüftabelle für den ZIP-Build (ZIP verlangt pro Datei eine
|
||
// CRC32-Summe; zlib.crc32 existiert erst ab neueren Node-Versionen, daher hier
|
||
// manuell).
|
||
const CSV_TABLE = (() => {
|
||
const t = new Uint32Array(256);
|
||
for(let n = 0; n < 256; n++){
|
||
let c = n;
|
||
for(let k = 0; k < 8; k++) c = (c & 1) ? (0xEDB88320 ^ (c >>> 1)) : (c >>> 1);
|
||
t[n] = c;
|
||
}
|
||
return t;
|
||
})();
|
||
|
||
function crc32(buf){
|
||
let c = 0xFFFFFFFF;
|
||
for(let i = 0; i < buf.length; i++) c = CSV_TABLE[(c ^ buf[i]) & 0xFF] ^ (c >>> 8);
|
||
return (c ^ 0xFFFFFFFF) >>> 0;
|
||
}
|
||
|
||
// dosDateTime: kodiert einen Zeitpunkt als DOS-Datum/-Uhrzeit (FAT-Format des
|
||
// ZIP-Headers). Rückgabe: {time, date}.
|
||
function dosDateTime(d){
|
||
const time = (d.getHours() << 11) | (d.getMinutes() << 5) | Math.floor(d.getSeconds() / 2);
|
||
const date = ((d.getFullYear() - 1980) << 9) | ((d.getMonth() + 1) << 5) | d.getDate();
|
||
return { time, date };
|
||
}
|
||
|
||
// buildZip: packt mehrere Dateien ohne externe Bibliothek in ein ZIP-Archiv
|
||
// (Deflate-Kompression, UTF-8-Dateinamen). Parameter: files = Array von
|
||
// {name, data}, wobei data Buffer oder String ist. Rückgabe: Buffer.
|
||
function buildZip(files){
|
||
const localParts = [];
|
||
const centralParts = [];
|
||
let offset = 0;
|
||
files.forEach(f => {
|
||
const nameBuf = Buffer.from(f.name, 'utf8');
|
||
const raw = Buffer.isBuffer(f.data) ? f.data : Buffer.from(f.data, 'utf8');
|
||
const compressed = zlib.deflateRawSync(raw);
|
||
const crc = crc32(raw);
|
||
const dt = dosDateTime(new Date());
|
||
const csize = compressed.length;
|
||
const usize = raw.length;
|
||
const nlen = nameBuf.length;
|
||
|
||
// Lokaler Datei-Header (30 Byte) + Name + Daten
|
||
const lh = Buffer.alloc(30);
|
||
lh.writeUInt32LE(0x04034b50, 0);
|
||
lh.writeUInt16LE(20, 4); // Version benötigt
|
||
lh.writeUInt16LE(0x0800, 6); // Flag: UTF-8-Namen
|
||
lh.writeUInt16LE(8, 8); // Methode: Deflate
|
||
lh.writeUInt16LE(dt.time, 10);
|
||
lh.writeUInt16LE(dt.date, 12);
|
||
lh.writeUInt32LE(crc, 14);
|
||
lh.writeUInt32LE(csize, 18);
|
||
lh.writeUInt32LE(usize, 22);
|
||
lh.writeUInt16LE(nlen, 26);
|
||
lh.writeUInt16LE(0, 28); // Extra-Länge
|
||
const local = Buffer.concat([lh, nameBuf, compressed]);
|
||
localParts.push(local);
|
||
|
||
// Zentralverzeichnis-Eintrag (46 Byte) + Name
|
||
const ch = Buffer.alloc(46);
|
||
ch.writeUInt32LE(0x02014b50, 0);
|
||
ch.writeUInt16LE(20, 4); // Version erstellt
|
||
ch.writeUInt16LE(20, 6); // Version benötigt
|
||
ch.writeUInt16LE(0x0800, 8);
|
||
ch.writeUInt16LE(8, 10);
|
||
ch.writeUInt16LE(dt.time, 12);
|
||
ch.writeUInt16LE(dt.date, 14);
|
||
ch.writeUInt32LE(crc, 16);
|
||
ch.writeUInt32LE(csize, 20);
|
||
ch.writeUInt32LE(usize, 24);
|
||
ch.writeUInt16LE(nlen, 28);
|
||
ch.writeUInt16LE(0, 30); // Extra
|
||
ch.writeUInt16LE(0, 32); // Kommentar
|
||
ch.writeUInt16LE(0, 34); // Datenträger
|
||
ch.writeUInt16LE(0, 36); // Interne Attribute
|
||
ch.writeUInt32LE(0, 38); // Externe Attribute
|
||
ch.writeUInt32LE(offset, 42); // Offset des lokalen Headers
|
||
centralParts.push(Buffer.concat([ch, nameBuf]));
|
||
offset += local.length;
|
||
});
|
||
|
||
const centralBuf = Buffer.concat(centralParts);
|
||
const end = Buffer.alloc(22);
|
||
end.writeUInt32LE(0x06054b50, 0);
|
||
end.writeUInt16LE(0, 4); // Datenträger
|
||
end.writeUInt16LE(0, 6); // Datenträger mit CD
|
||
end.writeUInt16LE(files.length, 8); // Einträge auf Datenträger
|
||
end.writeUInt16LE(files.length, 10); // Einträge gesamt
|
||
end.writeUInt32LE(centralBuf.length, 12);
|
||
end.writeUInt32LE(offset, 16);
|
||
end.writeUInt16LE(0, 20); // Kommentarlänge
|
||
return Buffer.concat([...localParts, centralBuf, end]);
|
||
}
|
||
|
||
// CSV_SEP: Trennzeichen der Export-CSVs. Standard Semikolon, damit die Dateien
|
||
// in der deutschen Excel-Version direkt sauber öffnen; über `?sep=` übersteuerbar.
|
||
let CSV_SEP = ';';
|
||
|
||
// csvCell: escaped eine Zelle für CSV (Anführungszeichen verdoppeln, Zellen
|
||
// mit Trenner/Quote/Zeilenumbruch in Anführungszeichen setzen).
|
||
function csvCell(v){
|
||
const s = v === undefined || v === null ? '' : String(v);
|
||
if(s.includes(CSV_SEP) || s.includes('"') || s.includes('\n') || s.includes('\r')){
|
||
return '"' + s.replace(/"/g, '""') + '"';
|
||
}
|
||
return s;
|
||
}
|
||
|
||
// csvFile: erzeugt aus Spaltenüberschriften (headers) und Zeilen (rows, je
|
||
// Array von Werten) eine fertige CSV-Datei-Beschreibung {name, data} für den
|
||
// ZIP-Build. Daten werden mit BOM + CRLF (Excel-kompatibel) erzeugt.
|
||
// name: Dateiname inkl. Unterordner (z. B. "ELT/projekte.csv").
|
||
function csvFile(name, headers, rows){
|
||
const lines = [csvRow(headers)];
|
||
rows.forEach(r => lines.push(csvRow(r)));
|
||
return { name, data: '\uFEFF' + lines.join('') };
|
||
}
|
||
|
||
// csvRow: eine CSV-Zeile als String (inkl. Zeilenende CRLF).
|
||
function csvRow(values){
|
||
return (values || []).map(csvCell).join(CSV_SEP) + '\r\n';
|
||
}
|
||
|
||
// csvStatusText: reduziert ein Status-HTML-Feld auf reinen Text für CSV
|
||
// (<br>/<p>/<div> werden zu Zeilenumbrüchen, alle übrigen Tags entfernt).
|
||
function csvStatusText(html){
|
||
if(!html) return '';
|
||
return String(html)
|
||
.replace(/<br\s*\/?>/gi, '\n')
|
||
.replace(/<\/(p|div)>/gi, '\n')
|
||
.replace(/<[^>]+>/g, '')
|
||
.replace(/\u00A0/g, ' ')
|
||
.replace(/\n{3,}/g, '\n\n')
|
||
.trim();
|
||
}
|
||
|
||
// personNameById: Namen der Wochen-Personen nachschlagen (Kurzname bevorzugt).
|
||
function weekPersonName(week, id){
|
||
const p = (week.people || []).find(e => e.id === id);
|
||
return p ? (p.name || p.fullName || id) : id;
|
||
}
|
||
|
||
// buildExportCsvs: erzeugt alle CSV-Dateien für eine Abteilung auf Basis der
|
||
// auch fürs Frontend verwendeten Deserialisierer (loadWeeks/loadGlobalPeople).
|
||
// Rückgabe: Array von {name, data} für buildZip.
|
||
function buildExportCsvs(department){
|
||
const weeks = loadWeeks(department);
|
||
const people = loadGlobalPeople(department);
|
||
const dir = department + '/';
|
||
const files = [];
|
||
|
||
// 1) Wochen
|
||
files.push(csvFile(dir + 'wochen.csv',
|
||
['Abteilung', 'Woche (KW)', 'ID', 'Datum', 'Gesperrt'],
|
||
weeks.map(w => [department, w.label || '', String(w.id || ''), w.date || '', w.locked ? 'Ja' : 'Nein'])));
|
||
|
||
// 2) Globale Personen
|
||
files.push(csvFile(dir + 'personen.csv',
|
||
['Abteilung', 'Kürzel', 'Voller Name', 'E-Mail', 'Rollen', 'Abwesenheiten'],
|
||
people.map(p => [
|
||
department, p.name || '', p.fullName || '', p.email || '',
|
||
Array.isArray(p.role) ? p.role.join(', ') : '',
|
||
Array.isArray(p.absences) ? p.absences.join(', ') : '',
|
||
])));
|
||
|
||
// 3) Sitzungsteilnehmer inkl. Anwesenheit
|
||
const teilnehmer = [];
|
||
weeks.forEach(w => {
|
||
(w.people || []).forEach(p => {
|
||
const att = w.attendance || {};
|
||
teilnehmer.push([
|
||
w.label || '', p.name || '', p.fullName || '',
|
||
Array.isArray(p.role) ? p.role.join(', ') : '',
|
||
att[p.id] === undefined ? '' : (att[p.id] ? 'Ja' : 'Nein'),
|
||
]);
|
||
});
|
||
});
|
||
files.push(csvFile(dir + 'sitzungsteilnehmer.csv',
|
||
['Woche (KW)', 'Kürzel', 'Voller Name', 'Rollen', 'Anwesend'],
|
||
teilnehmer));
|
||
|
||
// 4) Projekte
|
||
files.push(csvFile(dir + 'projekte.csv',
|
||
['Woche (KW)', 'Projekt-Nr.', 'Bezeichnung', 'Gruppe', 'Reihenfolge', 'Status'],
|
||
weeks.flatMap(w => (w.projects || []).map(p => [
|
||
w.label || '', p.nr || '', p.name || '', p.group || '',
|
||
typeof p.order === 'number' ? p.order : '', csvStatusText(p.status),
|
||
]))));
|
||
|
||
// 5) Projekt-Zuordnungen (Haupt/Optional je Person/Rolle)
|
||
const zuordnungen = [];
|
||
weeks.forEach(w => {
|
||
(w.projects || []).forEach(p => {
|
||
Object.keys(p.assign || {}).forEach(aKey => {
|
||
const sep = aKey.indexOf('|');
|
||
const pid = sep >= 0 ? aKey.slice(0, sep) : aKey;
|
||
const role = sep >= 0 ? aKey.slice(sep + 1) : '';
|
||
const state = Number(p.assign[aKey]) || 0;
|
||
zuordnungen.push([
|
||
w.label || '', p.nr || '', p.name || '', weekPersonName(w, pid), role,
|
||
state === 1 ? 'Haupt' : state === 2 ? 'Optional' : String(state),
|
||
]);
|
||
});
|
||
});
|
||
});
|
||
files.push(csvFile(dir + 'zuordnungen.csv',
|
||
['Woche (KW)', 'Projekt-Nr.', 'Bezeichnung', 'Kürzel', 'Rolle', 'Art'],
|
||
zuordnungen));
|
||
|
||
// 6) Leistungsphasen (Fortschrittswerte je Projekt/LPH)
|
||
const lphRows = [];
|
||
weeks.forEach(w => {
|
||
(w.projects || []).forEach(p => {
|
||
(p.lph || {}) && Object.keys(p.lph || {}).forEach(l =>
|
||
lphRows.push([w.label || '', p.nr || '', p.name || '', l, p.lph[l]]));
|
||
});
|
||
});
|
||
files.push(csvFile(dir + 'leistungsphasen.csv',
|
||
['Woche (KW)', 'Projekt-Nr.', 'Bezeichnung', 'Leistungsphase', 'Wert'],
|
||
lphRows));
|
||
|
||
// 7) Aktuelle Leistungsphasen je Projekt
|
||
const aktLpRows = [];
|
||
weeks.forEach(w => {
|
||
(w.projects || []).forEach(p => {
|
||
(Array.isArray(p.aktuelleLP) ? p.aktuelleLP : []).forEach(l =>
|
||
aktLpRows.push([w.label || '', p.nr || '', p.name || '', l]));
|
||
});
|
||
});
|
||
files.push(csvFile(dir + 'aktuelle-leistungsphasen.csv',
|
||
['Woche (KW)', 'Projekt-Nr.', 'Bezeichnung', 'Leistungsphase'],
|
||
aktLpRows));
|
||
|
||
// 8) Aufwand je Leistungsphase/Rolle (Gewichtungsfaktoren)
|
||
const aufwandRows = [];
|
||
weeks.forEach(w => {
|
||
(w.projects || []).forEach(p => {
|
||
(p.aufwandProLP || {}) && Object.keys(p.aufwandProLP || {}).forEach(l => {
|
||
Object.keys(p.aufwandProLP[l] || {}).forEach(role =>
|
||
aufwandRows.push([w.label || '', p.nr || '', p.name || '', l, role, p.aufwandProLP[l][role]]));
|
||
});
|
||
});
|
||
});
|
||
files.push(csvFile(dir + 'aufwand-je-leistungsphase.csv',
|
||
['Woche (KW)', 'Projekt-Nr.', 'Bezeichnung', 'Leistungsphase', 'Rolle', 'Faktor'],
|
||
aufwandRows));
|
||
|
||
// 9) Projektpläne (GANTT-Kopf)
|
||
const planRows = [];
|
||
weeks.forEach(w => {
|
||
(w.projects || []).forEach(p => {
|
||
const plan = p.plan;
|
||
if(!plan) return;
|
||
planRows.push([
|
||
w.label || '', p.nr || '', p.name || '', plan.start || '', plan.end || '',
|
||
Number(plan.progress) || 0,
|
||
(plan.people || []).map(pid => weekPersonName(w, pid)).join(', '),
|
||
]);
|
||
});
|
||
});
|
||
files.push(csvFile(dir + 'projektplaene.csv',
|
||
['Woche (KW)', 'Projekt-Nr.', 'Bezeichnung', 'Start', 'Ende', 'Fortschritt (%)', 'Beteiligte'],
|
||
planRows));
|
||
|
||
// 10) TODOs der Projektpläne (Zuständige und Abhängigkeiten als lesbare Namen)
|
||
const todoRows = [];
|
||
weeks.forEach(w => {
|
||
(w.projects || []).forEach(p => {
|
||
const plan = p.plan;
|
||
if(!plan || !Array.isArray(plan.todos)) return;
|
||
const titleById = {};
|
||
(plan.todos || []).forEach(t => { titleById[t.id] = t.title; });
|
||
(plan.milestones || []).forEach(m => { titleById[m.id] = m.name; });
|
||
(plan.todos || []).forEach(t => {
|
||
todoRows.push([
|
||
w.label || '', p.nr || '', p.name || '', t.title || '', t.start || '',
|
||
t.end || '', t.done ? 'Ja' : 'Nein', Number(t.progress) || 0,
|
||
(t.assignees || []).map(pid => weekPersonName(w, pid)).join(', '),
|
||
(t.dependsOn || []).map(did => titleById[did] || did).join(', '),
|
||
]);
|
||
});
|
||
});
|
||
});
|
||
files.push(csvFile(dir + 'todos.csv',
|
||
['Woche (KW)', 'Projekt-Nr.', 'Bezeichnung', 'TODO', 'Start', 'Ende', 'Erledigt', 'Fortschritt (%)', 'Zuständige', 'Hängt ab von'],
|
||
todoRows));
|
||
|
||
// 11) Meilensteine der Projektpläne
|
||
const msRows = [];
|
||
weeks.forEach(w => {
|
||
(w.projects || []).forEach(p => {
|
||
const plan = p.plan;
|
||
if(!plan || !Array.isArray(plan.milestones)) return;
|
||
const titleById = {};
|
||
(plan.todos || []).forEach(t => { titleById[t.id] = t.title; });
|
||
(plan.milestones || []).forEach(m => { titleById[m.id] = m.name; });
|
||
(plan.milestones || []).forEach(m => {
|
||
msRows.push([
|
||
w.label || '', p.nr || '', p.name || '', m.name || '', m.date || '',
|
||
m.position || '',
|
||
(m.dependsOn || []).map(did => titleById[did] || did).join(', '),
|
||
]);
|
||
});
|
||
});
|
||
});
|
||
files.push(csvFile(dir + 'meilensteine.csv',
|
||
['Woche (KW)', 'Projekt-Nr.', 'Bezeichnung', 'Meilenstein', 'Datum', 'Position', 'Hängt ab von'],
|
||
msRows));
|
||
|
||
return files;
|
||
}
|
||
|
||
// ---------- Datenbank-Backup (Ergänzung zum CSV-Export) ----------
|
||
//
|
||
// Zwei Wege: (1) Beim Serverstart wird automatisch eine Kopie der SQLite-DB
|
||
// als zeitgestempelte Datei in sessions/backups/ abgelegt (nur die letzten
|
||
// MAX_BACKUPS werden behalten). (2) GET /backup liefert einen konsistenten
|
||
// Snapshot der DB als herunterladbare .db-Datei (z. B. über den Button
|
||
// "DB Backup" im Menü der Hauptseite). Beide nutzen `VACUUM INTO`, das auch
|
||
// bei aktivem WAL-Mode eine konsistente Einzeldatei erzeugt (WAL-Inhalte
|
||
// werden einbezogen).
|
||
|
||
// BACKUP_DIR: Zielordner der automatischen Backups (sessions/backups, Docker-
|
||
// bind-mounted → bleibt persistent).
|
||
const BACKUP_DIR = path.join(SESSIONS_DIR, 'backups');
|
||
// MAX_BACKUPS: Anzahl der automatisch behaltenen Backups (Älteste werden verworfen)
|
||
const MAX_BACKUPS = 10;
|
||
|
||
// createDbSnapshot: erzeugt einen konsistenten Snapshot der SQLite-DB in die
|
||
// angegebene Zieldatei (VACUUM INTO; funktioniert auch bei WAL-Mode).
|
||
function createDbSnapshot(targetPath){
|
||
const quoted = String(targetPath).replace(/'/g, "''");
|
||
db.exec(`VACUUM INTO '${quoted}'`);
|
||
}
|
||
|
||
// runAutoBackup: legt ein zeitgestempeltes Backup in BACKUP_DIR ab und
|
||
// verwirft ältere, sobald MAX_BACKUPS überschritten ist. Fehler werden nur
|
||
// geloggt (Backup darf den Serverstart nicht verhindern).
|
||
function runAutoBackup(){
|
||
try {
|
||
if(!fs.existsSync(BACKUP_DIR)) fs.mkdirSync(BACKUP_DIR, { recursive: true });
|
||
// Zeitstempel mit Millisekunden, damit auch zwei Backups innerhalb einer
|
||
// Sekunde (z. B. direkt nach dem Import) eindeutige Dateinamen erhalten.
|
||
const base = 'projektuebersicht_' + new Date().toISOString().replace(/[:.]/g, '-').slice(0, 23);
|
||
let file = path.join(BACKUP_DIR, base + '.db');
|
||
let suffix = 1;
|
||
while(fs.existsSync(file)) file = path.join(BACKUP_DIR, base + '-' + (suffix++) + '.db');
|
||
createDbSnapshot(file);
|
||
console.log('Datenbank-Backup erstellt:', file);
|
||
|
||
// Nur die letzten MAX_BACKUPS behalten
|
||
const backups = fs.readdirSync(BACKUP_DIR)
|
||
.filter(f => /^projektuebersicht_.+\.db$/.test(f))
|
||
.map(f => ({ name: f, mtime: fs.statSync(path.join(BACKUP_DIR, f)).mtimeMs }))
|
||
.sort((a, b) => b.mtime - a.mtime);
|
||
backups.slice(MAX_BACKUPS).forEach(b => {
|
||
fs.unlinkSync(path.join(BACKUP_DIR, b.name));
|
||
});
|
||
} catch(err){
|
||
console.error('Automatisches Backup fehlgeschlagen:', err.message);
|
||
}
|
||
}
|
||
|
||
// ---------- Datenbank-Import (Wiederherstellung aus Backup) ----------
|
||
//
|
||
// POST /import nimmt eine mit "DB Backup" erzeugte .db-Datei als rohen
|
||
// Binär-Body entgegen, validiert das Schema und ersetzt den gesamten
|
||
// Datenbestand der laufenden Datenbank durch den Inhalt der Backup-Datei.
|
||
// Das Aufspielen erfolgt per ATTACH + DELETE/INSERT-SELECT in einer
|
||
// Transaktion: Die Live-Verbindung bleibt stabil, bei Fehlern wird
|
||
// zurückgerollt. Vor dem Import wird der Zustand vor dem Import als frisches
|
||
// Backup gesichert (runAutoBackup), damit er rückgängig gemacht werden kann.
|
||
|
||
// IMPORT_TABLES: alle Tabellen des relationalen Schemas; Grundlage für
|
||
// Validierung, Löschvorgang und Datentransfer. Die Reihenfolge ist ohne
|
||
// Bedeutung, da das Schema keine Foreign-Key-Constraints deklariert.
|
||
const IMPORT_TABLES = ['weeks','week_people','week_people_roles','attendance','projects',
|
||
'project_lph','project_assign','project_aufwand','project_aktuelle_lp',
|
||
'project_plan','plan_people','plan_todos','plan_todo_assignees','plan_todo_deps',
|
||
'plan_milestones','plan_milestone_deps','people','person_roles','person_absences'];
|
||
|
||
// dbTableColumns: liefert die Spaltennamen einer Tabelle (funktioniert gegen
|
||
// jede DatabaseSync-Instanz, auch read-only Backups) via pragma_table_info.
|
||
function dbTableColumns(conn, table){
|
||
return conn.prepare('SELECT name FROM pragma_table_info(?)').all(table).map(r => r.name);
|
||
}
|
||
|
||
// IMPORT_OPTIONAL_COLS: Spalten, die in älteren Backups fehlen dürfen (sie
|
||
// werden beim Import mit dem Default-Wert der Live-Tabelle neu belegt).
|
||
const IMPORT_OPTIONAL_COLS = ['updated_at'];
|
||
|
||
// restoreDbFromBackup: validiert die Backup-Datei (muss das bekannte Schema
|
||
// mit identischen Spalten wie die Live-DB besitzen) und ersetzt den
|
||
// Datenbestand der Live-DB durch den Snapshot. Parameter: backupPath (Pfad).
|
||
// Wirft bei ungültigem Schema oder Transferfehler (Transaktion wird gerollt).
|
||
function restoreDbFromBackup(backupPath){
|
||
const backupConn = new DatabaseSync(backupPath, { readOnly: true });
|
||
try {
|
||
// 1) Schema-Validierung: Tabelle muss existieren und jede Spalte der
|
||
// Live-DB (außer den als optional markierten) enthalten. Zusätzliche
|
||
// Spalten im Backup (z. B. aus einer neueren Version) werden ignoriert.
|
||
for(const t of IMPORT_TABLES){
|
||
const liveCols = dbTableColumns(db, t);
|
||
const backCols = dbTableColumns(backupConn, t);
|
||
if(liveCols.length === 0) throw new Error('Live-Schema defekt (Tabelle fehlt): ' + t);
|
||
if(backCols.length === 0) throw new Error('Ungültiges Backup (Tabelle fehlt): ' + t);
|
||
const missing = liveCols.filter(c => !backCols.includes(c) && !IMPORT_OPTIONAL_COLS.includes(c));
|
||
if(missing.length > 0) throw new Error('Backup-Schema passt nicht zur Live-DB (Tabelle ' + t + ', fehlt: ' + missing.join(', ') + ')');
|
||
}
|
||
|
||
// 2) Datentransfer: Backup anbinden, Bestand löschen, Snapshot übernehmen.
|
||
db.exec(`ATTACH DATABASE '${String(backupPath).replace(/'/g, "''")}' AS import_backup`);
|
||
db.exec('BEGIN');
|
||
try {
|
||
IMPORT_TABLES.forEach(t => db.exec('DELETE FROM ' + t));
|
||
for(const t of IMPORT_TABLES){
|
||
const liveCols = dbTableColumns(db, t);
|
||
const backCols = dbTableColumns(backupConn, t);
|
||
// Nur Spalten übertragen, die BEIDE Schemas enthalten (alte Backups
|
||
// ohne updated_at bleiben so importierbar).
|
||
const common = liveCols.filter(c => backCols.includes(c));
|
||
// Anführungszeichen um jede Spalte: `projects` enthält die
|
||
// SQL-Schlüsselwort-Spalte "group", die sonst einen Syntaxfehler wirft.
|
||
const quoted = common.map(c => '"' + c + '"').join(', ');
|
||
const marks = common.map(() => '?').join(', ');
|
||
const rows = backupConn.prepare('SELECT ' + quoted + ' FROM ' + t).all();
|
||
const ins = db.prepare('INSERT INTO ' + t + ' (' + quoted + ') VALUES (' + marks + ')');
|
||
rows.forEach(r => ins.run(...common.map(c => r[c])));
|
||
}
|
||
db.exec('COMMIT');
|
||
} catch(err){
|
||
db.exec('ROLLBACK');
|
||
throw err;
|
||
} finally {
|
||
db.exec('DETACH DATABASE import_backup');
|
||
}
|
||
} finally {
|
||
backupConn.close();
|
||
}
|
||
}
|
||
|
||
// ---------- Migration: altes Ein-Dokument-Format -> relationales Schema ----------
|
||
|
||
// migrateLegacySchema: überführt eine bestehende Datenbank aus dem alten Format
|
||
// (eine `weeks`-Tabelle mit JSON-`data`-Spalte sowie `people` mit JSON-`data`)
|
||
// in das relationale Schema. Wird einmalig beim Start ausgeführt.
|
||
function migrateLegacySchema(){
|
||
if(!hasLegacyWeeksTable()) return;
|
||
console.log('Migriere altes Wochen-Dokument-Format in relationales Schema …');
|
||
|
||
// Alte Wochen-Dokumente lesen
|
||
const legacyWeeks = db.prepare('SELECT department, label, data FROM weeks').all();
|
||
// Alte globale Personen-Dokumente lesen (eine JSON-Array-Zeile pro Abteilung)
|
||
const legacyPeopleRows = [];
|
||
const hasLegacyPeople = db.prepare("SELECT COUNT(*) AS c FROM sqlite_master WHERE type='table' AND name='people' AND sql LIKE '%data%'").get().c > 0;
|
||
if(hasLegacyPeople){
|
||
legacyPeopleRows.push(...db.prepare('SELECT department, data FROM people').all());
|
||
}
|
||
const tx = db.exec('BEGIN');
|
||
try {
|
||
// Alte Tabellen entfernen (Reihenfolge: erst Kind-Tabellen, dann Stamm)
|
||
db.exec(`DROP TABLE IF EXISTS week_people_roles; DROP TABLE IF EXISTS week_people;
|
||
DROP TABLE IF EXISTS attendance; DROP TABLE IF EXISTS project_lph;
|
||
DROP TABLE IF EXISTS project_assign; DROP TABLE IF EXISTS project_aufwand;
|
||
DROP TABLE IF EXISTS project_aktuelle_lp; DROP TABLE IF EXISTS project_plan;
|
||
DROP TABLE IF EXISTS plan_people; DROP TABLE IF EXISTS plan_todos;
|
||
DROP TABLE IF EXISTS plan_todo_assignees; DROP TABLE IF EXISTS plan_todo_deps;
|
||
DROP TABLE IF EXISTS plan_milestones; DROP TABLE IF EXISTS plan_milestone_deps;
|
||
DROP TABLE IF EXISTS projects; DROP TABLE IF EXISTS weeks;
|
||
DROP TABLE IF EXISTS people; DROP TABLE IF EXISTS person_roles; DROP TABLE IF EXISTS person_absences;`);
|
||
|
||
// Neue Tabellen anlegen (Schema-DDL oben ausführen)
|
||
db.exec(`CREATE TABLE IF NOT EXISTS weeks (
|
||
department TEXT NOT NULL, label TEXT NOT NULL, week_id TEXT NOT NULL DEFAULT '',
|
||
date TEXT NOT NULL DEFAULT '', locked INTEGER NOT NULL DEFAULT 0,
|
||
updated_at TEXT NOT NULL DEFAULT '',
|
||
PRIMARY KEY (department, label)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS week_people (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, person_id TEXT NOT NULL,
|
||
name TEXT NOT NULL DEFAULT '', full_name TEXT NOT NULL DEFAULT '',
|
||
array_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, week_label, person_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS week_people_roles (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, person_id TEXT NOT NULL,
|
||
role TEXT NOT NULL, role_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, week_label, person_id, role)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS attendance (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, person_id TEXT NOT NULL,
|
||
present INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, week_label, person_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS projects (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
nr TEXT NOT NULL DEFAULT '', name TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT '',
|
||
"group" TEXT NOT NULL DEFAULT '', array_order INTEGER NOT NULL DEFAULT 0,
|
||
order_value INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS project_lph (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
lph TEXT NOT NULL, value TEXT NOT NULL DEFAULT '',
|
||
PRIMARY KEY (department, week_label, project_id, lph)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS project_assign (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
person_id TEXT NOT NULL, role TEXT NOT NULL DEFAULT '', state INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id, person_id, role)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS project_aufwand (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
lph TEXT NOT NULL, role TEXT NOT NULL DEFAULT '', value REAL NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id, lph, role)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS project_aktuelle_lp (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
lph TEXT NOT NULL, PRIMARY KEY (department, week_label, project_id, lph)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS project_plan (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
start TEXT NOT NULL DEFAULT '', end TEXT NOT NULL DEFAULT '', progress INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_people (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
person_id TEXT NOT NULL, array_order INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id, person_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_todos (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
todo_id TEXT NOT NULL, title TEXT NOT NULL DEFAULT '', start TEXT NOT NULL DEFAULT '',
|
||
end TEXT NOT NULL DEFAULT '', done INTEGER NOT NULL DEFAULT 0, progress INTEGER NOT NULL DEFAULT 0,
|
||
sort_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, week_label, project_id, todo_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_todo_assignees (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
todo_id TEXT NOT NULL, person_id TEXT NOT NULL,
|
||
PRIMARY KEY (department, week_label, project_id, todo_id, person_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_todo_deps (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
todo_id TEXT NOT NULL, depends_on_id TEXT NOT NULL,
|
||
PRIMARY KEY (department, week_label, project_id, todo_id, depends_on_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_milestones (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
milestone_id TEXT NOT NULL, name TEXT NOT NULL DEFAULT '', date TEXT NOT NULL DEFAULT '',
|
||
position TEXT NOT NULL DEFAULT '', sort_order INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (department, week_label, project_id, milestone_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS plan_milestone_deps (
|
||
department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
|
||
milestone_id TEXT NOT NULL, depends_on_id TEXT NOT NULL,
|
||
PRIMARY KEY (department, week_label, project_id, milestone_id, depends_on_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS people (
|
||
department TEXT NOT NULL, person_id TEXT NOT NULL, name TEXT NOT NULL DEFAULT '',
|
||
full_name TEXT NOT NULL DEFAULT '', email TEXT NOT NULL DEFAULT '',
|
||
sort_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, person_id)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS person_roles (
|
||
department TEXT NOT NULL, person_id TEXT NOT NULL, role TEXT NOT NULL,
|
||
role_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, person_id, role)
|
||
)`);
|
||
db.exec(`CREATE TABLE IF NOT EXISTS person_absences (
|
||
department TEXT NOT NULL, person_id TEXT NOT NULL, absence TEXT NOT NULL,
|
||
PRIMARY KEY (department, person_id, absence)
|
||
)`);
|
||
|
||
// Alte Dokumente einlesen und relational speichern. Die Tabellen wurden
|
||
// gerade neu angelegt und sind leer; `serializeWeek` genügt (kein eigener
|
||
// Transaktions-Wrapper, da die äußere BEGIN/COMMIT der Migration aktiv ist).
|
||
legacyWeeks.forEach(row => {
|
||
let week;
|
||
try { week = JSON.parse(row.data); } catch(e){ console.error('Kaputte Woche bei Migration:', row.label, e.message); return; }
|
||
if(!week || !week.label) return;
|
||
serializeWeek(row.department, week);
|
||
});
|
||
// Globale Personen aus dem alten Dokument-Format migrieren. serializeGlobalPeople
|
||
// startet selbst eine Transaktion, daher werden die Zeilen hier direkt in die
|
||
// neuen Tabellen eingefügt (serializeGlobalPeople ist in Transaktionen nicht
|
||
// aufrufbar, weil BEGIN innerhalb BEGIN nicht erlaubt ist).
|
||
legacyPeopleRows.forEach(row => {
|
||
let people;
|
||
try { people = JSON.parse(row.data); } catch(e){ console.error('Kaputte Personen-Liste bei Migration:', row.department, e.message); return; }
|
||
if(!Array.isArray(people)) return;
|
||
const insPerson = db.prepare(`INSERT INTO people (department, person_id, name, full_name, email, sort_order)
|
||
VALUES (?, ?, ?, ?, ?, ?)`);
|
||
const insRole = db.prepare(`INSERT OR IGNORE INTO person_roles (department, person_id, role, role_order) VALUES (?, ?, ?, ?)`);
|
||
const insAbsence = db.prepare(`INSERT OR IGNORE INTO person_absences (department, person_id, absence) VALUES (?, ?, ?)`);
|
||
people.forEach((p, idx) => {
|
||
insPerson.run(row.department, String(p.id || ''), String(p.name || ''), String(p.fullName || ''), String(p.email || ''), idx);
|
||
(Array.isArray(p.role) ? p.role : []).forEach((r, ridx) => insRole.run(row.department, String(p.id || ''), String(r || ''), ridx));
|
||
(Array.isArray(p.absences) ? p.absences : []).forEach(a => insAbsence.run(row.department, String(p.id || ''), String(a || '')));
|
||
});
|
||
});
|
||
db.exec('COMMIT');
|
||
console.log('Migration abgeschlossen (' + legacyWeeks.length + ' Wochen).');
|
||
} catch(err){
|
||
db.exec('ROLLBACK');
|
||
console.error('Migration fehlgeschlagen:', err.message);
|
||
throw err;
|
||
}
|
||
}
|
||
|
||
// migrateWeeksUpdatedAt: ergänzt die `updated_at`-Spalte in bestehenden
|
||
// relationalen Datenbanken (idempotent; neue DBs haben die Spalte bereits).
|
||
function migrateWeeksUpdatedAt(){
|
||
const cols = db.prepare("SELECT name FROM pragma_table_info('weeks')").all().map(r => r.name);
|
||
if(!cols.includes('updated_at')){
|
||
db.exec("ALTER TABLE weeks ADD COLUMN updated_at TEXT NOT NULL DEFAULT ''");
|
||
console.log('Wochen-Tabelle um updated_at ergänzt.');
|
||
}
|
||
}
|
||
|
||
// Migration beim Serverstart ausführen
|
||
migrateLegacySchema();
|
||
migrateWeeksUpdatedAt();
|
||
|
||
// Route: Speichert eine komplette Woche (als JSON-Objekt) für eine Abteilung
|
||
app.post('/save-week', (req, res) => {
|
||
const { department, week, baseUpdatedAt } = req.body || {};
|
||
if(!week || typeof week.label !== 'string' || !week.label) return res.status(400).json({ ok: false, error: 'Missing week or label' });
|
||
const dept = departmentKey(department);
|
||
try {
|
||
// Revisions-/Konflikt-Check: Mit `baseUpdatedAt` meldet der Client die
|
||
// Version, auf der seine lokalen Daten beruhen. Ist die Woche auf dem
|
||
// Server zwischenzeitlich von jemand anderem gespeichert worden, wird
|
||
// NICHT überschrieben, sondern ein Konflikt gemeldet (HTTP 409).
|
||
// Wichtig: Auch eine leere Basis (updated_at='' direkt nach der Migration)
|
||
// wird geprüft – nur Clients OHNE baseUpdatedAt-Feld (alte Versionen)
|
||
// überspringen die Prüfung. Der Check läuft synchron vor dem Schreiben,
|
||
// daher gibt es im Prozess keine Race-Condition zwischen Prüfung und
|
||
// Transaktion.
|
||
if(typeof baseUpdatedAt === 'string'){
|
||
const row = db.prepare('SELECT updated_at FROM weeks WHERE department = ? AND label = ?').get(dept, String(week.label));
|
||
if(row){
|
||
if(baseUpdatedAt !== row.updated_at){
|
||
return res.status(409).json({
|
||
ok: false,
|
||
conflict: true,
|
||
error: 'Die Sitzung "' + week.label + '" wurde zwischenzeitlich von einer anderen Person geändert.',
|
||
updatedAt: row.updated_at
|
||
});
|
||
}
|
||
} else if(baseUpdatedAt !== ''){
|
||
// Die Woche existiert am Server nicht mehr (vermutlich gelöscht). Ein
|
||
// Save mit nicht-leerer, also älterer Revision darf eine gelöschte
|
||
// Sitzung nicht wiederbeleben; nur neue Wochen (leere/fehlende Basis)
|
||
// dürfen angelegt werden.
|
||
return res.status(409).json({
|
||
ok: false,
|
||
conflict: true,
|
||
error: 'Die Sitzung "' + week.label + '" wurde zwischenzeitlich gelöscht.',
|
||
updatedAt: ''
|
||
});
|
||
}
|
||
}
|
||
// `updatedAt` zurückgeben, damit der Client seine lokale Revisions-Basis
|
||
// aktualisieren kann (verhindert falsche Eigen-Konflikte beim nächsten Save).
|
||
const updatedAt = saveWeekRelational(dept, week);
|
||
console.log('Saved week:', dept + ' / ' + week.label);
|
||
return res.json({ ok: true, updatedAt: updatedAt || new Date().toISOString() });
|
||
} catch(err){
|
||
console.error('Save week error', err);
|
||
return res.status(500).json({ ok: false, error: String(err) });
|
||
}
|
||
});
|
||
|
||
// Route: Speichert die globale Personenliste einer Abteilung (als JSON-Array)
|
||
app.post('/save-people', (req, res) => {
|
||
const { department, people } = req.body || {};
|
||
if(!Array.isArray(people)) return res.status(400).json({ ok: false, error: 'Missing people array' });
|
||
const dept = departmentKey(department);
|
||
try {
|
||
serializeGlobalPeople(dept, people);
|
||
console.log('Saved people:', dept, '(' + people.length + ' Personen)');
|
||
return res.json({ ok: true });
|
||
} catch(err){
|
||
console.error('Save people error', err);
|
||
return res.status(500).json({ ok: false, error: String(err) });
|
||
}
|
||
});
|
||
|
||
// Route: Löscht eine Woche einer Abteilung (fehlende Woche wird toleriert)
|
||
app.post('/delete-week', (req, res) => {
|
||
const { department, label } = req.body || {};
|
||
if(!label) return res.status(400).json({ ok: false, error: 'Missing label' });
|
||
const dept = departmentKey(department);
|
||
try {
|
||
const row = db.prepare('SELECT 1 FROM weeks WHERE department = ? AND label = ?').get(dept, String(label));
|
||
const existed = !!row;
|
||
deleteWeekData(dept, String(label));
|
||
console.log('Deleted week:', dept + ' / ' + label, existed ? '' : '(nicht vorhanden)');
|
||
return res.json({ ok: true, deleted: existed });
|
||
} catch(err){
|
||
console.error('Delete week error', err);
|
||
return res.status(500).json({ ok: false, error: String(err) });
|
||
}
|
||
});
|
||
|
||
// Route: Lädt alle Wochen plus globale Personenliste einer Abteilung.
|
||
// Liefert strukturierte JSON-Objekte (aus den relationalen Tabellen
|
||
// zusammengesetzt); nach Label sortiert.
|
||
app.get('/load-all', (req, res) => {
|
||
try {
|
||
const dept = departmentKey(req.query.department);
|
||
const weeks = loadWeeks(dept);
|
||
const people = loadGlobalPeople(dept);
|
||
res.json({ ok: true, weeks, people });
|
||
} catch(err){
|
||
console.error('Load error', err);
|
||
res.status(500).json({ ok: false, error: String(err) });
|
||
}
|
||
});
|
||
|
||
// Route: CSV-Export. Erzeugt einen ZIP mit einer übersichtlichen CSV je
|
||
// logischer Entität (Wochen, Personen, Sitzungsteilnehmer, Projekte,
|
||
// Zuordnungen, Leistungsphasen, Pläne/TODOs/Meilensteine) der angegebenen
|
||
// Abteilung. Das Trennzeichen kann über `?sep=` gesetzt werden (Standard: ';').
|
||
app.get('/export-csv', (req, res) => {
|
||
try {
|
||
const dept = departmentKey(req.query.department);
|
||
const sep = String(req.query.sep || ';');
|
||
CSV_SEP = (sep.length === 1) ? sep : ';';
|
||
const files = buildExportCsvs(dept);
|
||
const zip = buildZip(files);
|
||
const filename = 'Projektuebersicht_' + dept + '_' + new Date().toISOString().slice(0,10) + '.zip';
|
||
res.setHeader('Content-Type', 'application/zip');
|
||
res.setHeader('Content-Disposition', 'attachment; filename="' + filename + '"');
|
||
res.setHeader('Content-Length', String(zip.length));
|
||
res.send(zip);
|
||
console.log('CSV-Export:', dept, '(' + files.length + ' Dateien, ' + zip.length + ' Bytes)');
|
||
} catch(err){
|
||
console.error('CSV-Export fehlgeschlagen:', err);
|
||
if(!res.headersSent) res.status(500).json({ ok: false, error: String(err) });
|
||
}
|
||
});
|
||
|
||
// Route: DB-Snapshot-Download. Erzeugt einen konsistenten Snapshot der
|
||
// SQLite-Datenbank (auch mit WAL) und stellt ihn als .db-Datei zum Download
|
||
// bereit (Backup für Archivierung/Wiederherstellung, z. B. per "DB Backup"-
|
||
// Button im Menü). Die temporäre Datei wird nach dem Senden gelöscht.
|
||
app.get('/backup', (req, res) => {
|
||
const tmp = path.join(os.tmpdir(), 'projektuebersicht_backup_' + Date.now() + '.db');
|
||
try {
|
||
createDbSnapshot(tmp);
|
||
const filename = 'projektuebersicht_backup_' + new Date().toISOString().slice(0, 10) + '.db';
|
||
res.download(tmp, filename, () => fs.unlink(tmp, () => {}));
|
||
console.log('DB-Backup-Download:', filename);
|
||
} catch(err){
|
||
console.error('DB-Backup fehlgeschlagen:', err);
|
||
fs.unlink(tmp, () => {});
|
||
if(!res.headersSent) res.status(500).json({ ok: false, error: String(err) });
|
||
}
|
||
});
|
||
|
||
// Route: DB-Import (Backup-Wiederherstellung). Erwartet eine .db-Datei aus
|
||
// GET /backup bzw. "DB Backup" als rohen Binär-Body (application/octet-stream);
|
||
// validiert das Schema und ersetzt den gesamten Datenbestand. VOR dem Import
|
||
// wird der Zustand vor dem Import als frisches Backup gesichert, damit ein
|
||
// versehentlicher Restore rückgängig gemacht werden kann.
|
||
app.post('/import', express.raw({ type: '*/*', limit: '100mb' }), (req, res) => {
|
||
const buf = req.body;
|
||
if(!Buffer.isBuffer(buf) || buf.length === 0){
|
||
return res.status(400).json({ ok: false, error: 'Keine Datei empfangen' });
|
||
}
|
||
const tmp = path.join(os.tmpdir(), 'projektuebersicht_import_' + Date.now() + '.db');
|
||
try {
|
||
fs.writeFileSync(tmp, buf);
|
||
// Zustand VOR dem Restore sichern (danach wäre der Snapshot bereits der
|
||
// importierte Stand und die Wiederherstellung unmöglich).
|
||
runAutoBackup();
|
||
restoreDbFromBackup(tmp);
|
||
console.log('Datenbank aus Backup wiederhergestellt (' + buf.length + ' Bytes)');
|
||
return res.json({ ok: true });
|
||
} catch(err){
|
||
console.error('Datenbank-Import fehlgeschlagen:', err);
|
||
if(!res.headersSent) res.status(500).json({ ok: false, error: String(err && err.message ? err.message : err) });
|
||
} finally {
|
||
fs.unlink(tmp, () => {});
|
||
}
|
||
});
|
||
|
||
// Route: Healthcheck-Endpunkt für Docker/Überwachung
|
||
app.get('/health', (req,res)=> res.json({ok:true}));
|
||
|
||
// Route: Liefert die Hauptseite Test.html
|
||
app.get('/', (req,res)=>{
|
||
res.sendFile(path.join(__dirname, 'public', 'Test.html'));
|
||
});
|
||
|
||
// Gültige Tab-Slugs für Deep-Link-Routen (muss zu TAB_SLUGS in js/globals.js passen)
|
||
const VALID_TAB_SLUGS = new Set(['Projektübersicht','Auslastung','Projekte','Projektbeteiligte']);
|
||
// Slug der Projektplan-Seite (muss zu PLAN_SLUG in js/globals.js passen)
|
||
const PLAN_SLUG = 'Projektplan';
|
||
|
||
// Route: Deep-Links je Abteilung + Tab, z. B. "/HLS/Auslastung" oder
|
||
// "/ELT/Projektübersicht". Liefert wie "/" die Hauptseite; unbekannte
|
||
// Abteilungen/Tabs werden abgelehnt (404), damit keine Test.html auf beliebigen
|
||
// Pfaden ausgeliefert wird.
|
||
app.get('/:department/:tab', (req, res) => {
|
||
const { department, tab } = req.params;
|
||
if(!VALID_DEPARTMENTS.has(department)) return res.status(404).send('Nicht gefunden');
|
||
if(tab === PLAN_SLUG){
|
||
// Projektplan-Seite (GANTT), z. B. "/HLS/Projektplan?weekLabel=..."
|
||
return res.sendFile(path.join(__dirname, 'public', 'project-plan.html'));
|
||
}
|
||
if(!VALID_TAB_SLUGS.has(tab)) return res.status(404).send('Nicht gefunden');
|
||
res.sendFile(path.join(__dirname, 'public', 'Test.html'));
|
||
});
|
||
|
||
// Route: Projektplan-Seite mit Projektname im Pfad, z. B.
|
||
// "/HLS/Projektplan/Gymnasium%20Hemer?weekLabel=..." – das Pfad-Segment dient
|
||
// nur der lesbaren Adresse, die Identifikation erfolgt über weekLabel/projNr.
|
||
app.get('/:department/Projektplan/:projName', (req, res) => {
|
||
if(!VALID_DEPARTMENTS.has(req.params.department)) return res.status(404).send('Nicht gefunden');
|
||
res.sendFile(path.join(__dirname, 'public', 'project-plan.html'));
|
||
});
|
||
|
||
// Serverstart mit Fehlerbehandlung (z.B. Port bereits belegt)
|
||
const server = app.listen(PORT, () => {
|
||
console.log(`Projektübersicht server running on http://localhost:${PORT}`);
|
||
// Automatisches Datenbank-Backup beim Start (session/backups/, letzte MAX_BACKUPS)
|
||
runAutoBackup();
|
||
});
|
||
server.on('error', (err) => {
|
||
console.error('Server konnte nicht starten:', err.message);
|
||
if(err.code === 'EADDRINUSE'){
|
||
console.error(`Port ${PORT} ist bereits belegt. Bitte einen anderen PORT verwenden, z.B. PORT=3004 npm start`);
|
||
}
|
||
process.exit(1);
|
||
});
|