278 lines
13 KiB
TypeScript
278 lines
13 KiB
TypeScript
import pg from "pg";
|
|
|
|
import { decryptSecret } from "../backend/src/secrets.js";
|
|
|
|
const apply = process.argv.includes("--apply");
|
|
|
|
if (!process.env.DATABASE_URL) {
|
|
throw new Error("DATABASE_URL fehlt");
|
|
}
|
|
|
|
type JsonPosition = {
|
|
apiProjectId?: string | number | null;
|
|
minutes?: number;
|
|
billableMinutes?: number;
|
|
entryCount?: number;
|
|
days?: Array<{ day?: string; minutes?: number; billableMinutes?: number; entryCount?: number }>;
|
|
};
|
|
|
|
type RemoteProject = {
|
|
apiProjectId: number;
|
|
mainApiProjectId: number;
|
|
projectId: string;
|
|
name: string;
|
|
customerApiId: number | null;
|
|
customerName: string | null;
|
|
url: string | null;
|
|
};
|
|
|
|
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
|
|
|
|
function positiveInteger(value: unknown) {
|
|
const parsed = Number(value);
|
|
return Number.isInteger(parsed) && parsed > 0 ? parsed : null;
|
|
}
|
|
|
|
function text(value: unknown) {
|
|
return typeof value === "string" && value.trim() ? value.trim() : null;
|
|
}
|
|
|
|
async function main() {
|
|
const settingsResult = await pool.query<{ key: string; value: string }>(
|
|
"SELECT key, value FROM app_settings WHERE key = ANY($1::text[]);",
|
|
[["teamspace.base_url", "teamspace.admin_credentials_user_id"]]
|
|
);
|
|
const settings = new Map(settingsResult.rows.map((row) => [row.key, row.value]));
|
|
const baseUrl = settings.get("teamspace.base_url")?.replace(/\/$/, "");
|
|
const credentialsUserId = settings.get("teamspace.admin_credentials_user_id");
|
|
|
|
if (!baseUrl || !credentialsUserId) {
|
|
throw new Error("Teamspace-Adminzugang ist nicht vollständig konfiguriert");
|
|
}
|
|
|
|
const credentialsResult = await pool.query<{ teamspace_device_id: string | null; teamspace_device_password: string | null }>(
|
|
"SELECT teamspace_device_id, teamspace_device_password FROM users WHERE id = $1;",
|
|
[credentialsUserId]
|
|
);
|
|
const deviceId = decryptSecret(credentialsResult.rows[0]?.teamspace_device_id);
|
|
const devicePassword = decryptSecret(credentialsResult.rows[0]?.teamspace_device_password);
|
|
|
|
if (!deviceId || !devicePassword) {
|
|
throw new Error("Teamspace-Adminzugang enthält keine Geräte-Credentials");
|
|
}
|
|
|
|
const authorization = `Basic ${Buffer.from(`${deviceId}:${devicePassword}`).toString("base64")}`;
|
|
const fetchJson = async (path: string) => {
|
|
const response = await fetch(`${baseUrl}${path}`, { headers: { Authorization: authorization, Accept: "application/json" } });
|
|
|
|
if (!response.ok) {
|
|
throw new Error(`Teamspace ${path} antwortet mit HTTP ${response.status}`);
|
|
}
|
|
|
|
return response.json() as Promise<Record<string, unknown>>;
|
|
};
|
|
|
|
const [entryResult, resourceResult, exclusionResult, userActualResult, entryActualResult] = await Promise.all([
|
|
pool.query<{ id: string; api_id: string }>("SELECT id::text, teamspace_api_project_id::text AS api_id FROM project_teamspace_entries WHERE teamspace_api_project_id IS NOT NULL;"),
|
|
pool.query<{ id: string; api_id: string }>("SELECT id::text, teamspace_api_project_id::text AS api_id FROM teamspace_project_resources WHERE teamspace_api_project_id IS NOT NULL;"),
|
|
pool.query<{ teamspace_project: string; api_id: string | null; api_ids: string[] }>(`
|
|
SELECT teamspace_project,
|
|
teamspace_api_project_id::text AS api_id,
|
|
ARRAY(SELECT value::text FROM unnest(teamspace_api_project_ids) AS value) AS api_ids
|
|
FROM teamspace_project_exclusions;
|
|
`),
|
|
pool.query<{ user_id: string; month: string; positions: JsonPosition[] }>("SELECT user_id::text, month::text, positions FROM teamspace_monthly_user_actuals;"),
|
|
pool.query<{ user_id: string; project_teamspace_entry_id: string; month: string; positions: JsonPosition[] }>(
|
|
"SELECT user_id::text, project_teamspace_entry_id::text, month::text, positions FROM project_teamspace_monthly_actuals;"
|
|
)
|
|
]);
|
|
|
|
const apiIds = new Set<number>();
|
|
const addApiId = (value: unknown) => {
|
|
const apiId = positiveInteger(value);
|
|
if (apiId) apiIds.add(apiId);
|
|
};
|
|
|
|
entryResult.rows.forEach((row) => addApiId(row.api_id));
|
|
resourceResult.rows.forEach((row) => addApiId(row.api_id));
|
|
exclusionResult.rows.forEach((row) => {
|
|
addApiId(row.api_id);
|
|
row.api_ids.forEach(addApiId);
|
|
});
|
|
[...userActualResult.rows, ...entryActualResult.rows].forEach((row) => row.positions.forEach((position) => addApiId(position.apiProjectId)));
|
|
|
|
const rawProjects = new Map<number, Record<string, unknown>>();
|
|
const ids = [...apiIds].sort((left, right) => left - right);
|
|
|
|
for (let offset = 0; offset < ids.length; offset += 8) {
|
|
const projects = await Promise.all(ids.slice(offset, offset + 8).map(async (apiId) => [apiId, await fetchJson(`/api/project/${apiId}`)] as const));
|
|
projects.forEach(([apiId, project]) => rawProjects.set(apiId, project));
|
|
}
|
|
|
|
const mainIds = new Set<number>();
|
|
for (const [apiId, project] of rawProjects) {
|
|
mainIds.add(positiveInteger((project.mainProject as Record<string, unknown> | undefined)?.value) ?? apiId);
|
|
}
|
|
|
|
const missingMainIds = [...mainIds].filter((apiId) => !rawProjects.has(apiId));
|
|
for (let offset = 0; offset < missingMainIds.length; offset += 8) {
|
|
const projects = await Promise.all(missingMainIds.slice(offset, offset + 8).map(async (apiId) => [apiId, await fetchJson(`/api/project/${apiId}`)] as const));
|
|
projects.forEach(([apiId, project]) => rawProjects.set(apiId, project));
|
|
}
|
|
|
|
const customerIds = new Set<number>();
|
|
for (const apiId of mainIds) {
|
|
const customerApiId = positiveInteger((rawProjects.get(apiId)?.customer as Record<string, unknown> | undefined)?.value);
|
|
if (customerApiId) customerIds.add(customerApiId);
|
|
}
|
|
const customerNames = new Map<number, string>();
|
|
|
|
for (let offset = 0; offset < customerIds.size; offset += 8) {
|
|
const chunk = [...customerIds].slice(offset, offset + 8);
|
|
const customers = await Promise.all(chunk.map(async (apiId) => [apiId, await fetchJson(`/api/organization/${apiId}`)] as const));
|
|
customers.forEach(([apiId, customer]) => {
|
|
const name = text(customer.name) ?? text(customer.companyName) ?? text(customer.displayName);
|
|
if (name) customerNames.set(apiId, name);
|
|
});
|
|
}
|
|
|
|
const projectMap = new Map<number, RemoteProject>();
|
|
for (const sourceApiId of apiIds) {
|
|
const source = rawProjects.get(sourceApiId);
|
|
if (!source) throw new Error(`Teamspace API-ID ${sourceApiId} konnte nicht aufgelöst werden`);
|
|
const mainApiProjectId = positiveInteger((source.mainProject as Record<string, unknown> | undefined)?.value) ?? sourceApiId;
|
|
const project = rawProjects.get(mainApiProjectId);
|
|
if (!project) throw new Error(`Teamspace Hauptprojekt ${mainApiProjectId} konnte nicht aufgelöst werden`);
|
|
const customerApiId = positiveInteger((project.customer as Record<string, unknown> | undefined)?.value);
|
|
const projectId = text(project.projectId);
|
|
if (!projectId) throw new Error(`Teamspace Hauptprojekt ${mainApiProjectId} hat keine Projektnummer`);
|
|
|
|
projectMap.set(sourceApiId, {
|
|
apiProjectId: mainApiProjectId,
|
|
mainApiProjectId,
|
|
projectId,
|
|
name: text(project.name) ?? projectId,
|
|
customerApiId,
|
|
customerName: customerApiId ? customerNames.get(customerApiId) ?? null : null,
|
|
url: text(project._url)
|
|
});
|
|
}
|
|
|
|
const normalizePositions = (positions: JsonPosition[]) => {
|
|
const grouped = new Map<number, {
|
|
project: RemoteProject;
|
|
minutes: number;
|
|
billableMinutes: number;
|
|
entryCount: number;
|
|
days: Map<string, { minutes: number; billableMinutes: number; entryCount: number }>;
|
|
}>();
|
|
|
|
for (const position of positions) {
|
|
const sourceApiId = positiveInteger(position.apiProjectId);
|
|
if (!sourceApiId) continue;
|
|
const project = projectMap.get(sourceApiId);
|
|
if (!project) throw new Error(`Keine Hauptprojekt-Zuordnung für API-ID ${sourceApiId}`);
|
|
const current = grouped.get(project.mainApiProjectId) ?? { project, minutes: 0, billableMinutes: 0, entryCount: 0, days: new Map() };
|
|
current.minutes += Number(position.minutes ?? 0);
|
|
current.billableMinutes += Number(position.billableMinutes ?? 0);
|
|
current.entryCount += Number(position.entryCount ?? 0);
|
|
|
|
for (const sourceDay of position.days ?? []) {
|
|
const dayLabel = text(sourceDay.day);
|
|
if (!dayLabel) continue;
|
|
const day = current.days.get(dayLabel) ?? { minutes: 0, billableMinutes: 0, entryCount: 0 };
|
|
day.minutes += Number(sourceDay.minutes ?? 0);
|
|
day.billableMinutes += Number(sourceDay.billableMinutes ?? 0);
|
|
day.entryCount += Number(sourceDay.entryCount ?? 0);
|
|
current.days.set(dayLabel, day);
|
|
}
|
|
|
|
grouped.set(project.mainApiProjectId, current);
|
|
}
|
|
|
|
return [...grouped.values()].sort((left, right) => left.project.mainApiProjectId - right.project.mainApiProjectId).map((value) => ({
|
|
apiProjectId: String(value.project.mainApiProjectId),
|
|
projectId: value.project.projectId,
|
|
mainProjectId: value.project.projectId,
|
|
label: value.project.name,
|
|
customerName: value.project.customerName,
|
|
isSubentry: false,
|
|
billingType: null,
|
|
minutes: value.minutes,
|
|
billableMinutes: value.billableMinutes,
|
|
entryCount: value.entryCount,
|
|
days: [...value.days.entries()].sort(([left], [right]) => left.localeCompare(right)).map(([day, totals]) => ({ day, ...totals }))
|
|
}));
|
|
};
|
|
|
|
const normalizedUserActuals = userActualResult.rows.map((row) => ({ ...row, positions: normalizePositions(row.positions) }));
|
|
const normalizedEntryActuals = entryActualResult.rows.map((row) => ({ ...row, positions: normalizePositions(row.positions) }));
|
|
const subentryCountResult = await pool.query<{ count: number }>("SELECT count(*)::int AS count FROM project_teamspace_subentries;");
|
|
const changedSourceIds = [...projectMap.entries()].filter(([sourceApiId, project]) => sourceApiId !== project.mainApiProjectId).length;
|
|
|
|
console.log(JSON.stringify({
|
|
mode: apply ? "apply" : "dry-run",
|
|
apiIdsChecked: apiIds.size,
|
|
subprojectIdsNormalized: changedSourceIds,
|
|
monthlyUserActuals: normalizedUserActuals.length,
|
|
monthlyProjectActuals: normalizedEntryActuals.length,
|
|
legacySubentriesToDelete: subentryCountResult.rows[0]?.count ?? 0
|
|
}, null, 2));
|
|
|
|
if (!apply) return;
|
|
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query("BEGIN");
|
|
|
|
for (const row of entryResult.rows) {
|
|
const project = projectMap.get(Number(row.api_id));
|
|
if (!project) continue;
|
|
await client.query("UPDATE project_teamspace_entries SET teamspace_api_project_id=$2, teamspace_api_project_url=$3, updated_at=now() WHERE id=$1;", [row.id, project.mainApiProjectId, project.url]);
|
|
}
|
|
for (const row of resourceResult.rows) {
|
|
const project = projectMap.get(Number(row.api_id));
|
|
if (!project) continue;
|
|
await client.query("UPDATE teamspace_project_resources SET teamspace_api_project_id=$2, teamspace_api_project_url=$3, updated_at=now() WHERE id=$1;", [row.id, project.mainApiProjectId, project.url]);
|
|
}
|
|
for (const row of exclusionResult.rows) {
|
|
const sourceApiId = positiveInteger(row.api_id) ?? row.api_ids.map(positiveInteger).find((value): value is number => value !== null);
|
|
const project = sourceApiId ? projectMap.get(sourceApiId) : null;
|
|
await client.query(`
|
|
UPDATE teamspace_project_exclusions
|
|
SET teamspace_api_project_id=$2,
|
|
teamspace_api_project_ids=CASE WHEN $2::bigint IS NULL THEN '{}'::bigint[] ELSE ARRAY[$2::bigint] END,
|
|
teamspace_name=COALESCE($3, teamspace_name),
|
|
customer_name=COALESCE($4, customer_name),
|
|
updated_at=now()
|
|
WHERE teamspace_project=$1;
|
|
`, [row.teamspace_project, project?.mainApiProjectId ?? null, project?.name ?? null, project?.customerName ?? null]);
|
|
}
|
|
for (const row of normalizedUserActuals) {
|
|
const minutes = row.positions.reduce((sum, position) => sum + position.minutes, 0);
|
|
const billableMinutes = row.positions.reduce((sum, position) => sum + position.billableMinutes, 0);
|
|
const entryCount = row.positions.reduce((sum, position) => sum + position.entryCount, 0);
|
|
await client.query(`UPDATE teamspace_monthly_user_actuals SET positions=$3::jsonb, teamspace_minutes=$4, teamspace_billable_minutes=$5, teamspace_time_entry_count=$6, updated_at=now() WHERE user_id=$1 AND month=$2::date;`, [row.user_id, row.month, JSON.stringify(row.positions), minutes, billableMinutes, entryCount]);
|
|
}
|
|
for (const row of normalizedEntryActuals) {
|
|
const minutes = row.positions.reduce((sum, position) => sum + position.minutes, 0);
|
|
const billableMinutes = row.positions.reduce((sum, position) => sum + position.billableMinutes, 0);
|
|
const entryCount = row.positions.reduce((sum, position) => sum + position.entryCount, 0);
|
|
await client.query(`UPDATE project_teamspace_monthly_actuals SET positions=$4::jsonb, teamspace_minutes=$5, teamspace_billable_minutes=$6, teamspace_time_entry_count=$7, updated_at=now() WHERE user_id=$1 AND project_teamspace_entry_id=$2 AND month=$3::date;`, [row.user_id, row.project_teamspace_entry_id, row.month, JSON.stringify(row.positions), minutes, billableMinutes, entryCount]);
|
|
}
|
|
await client.query("DELETE FROM project_teamspace_subentries;");
|
|
await client.query("COMMIT");
|
|
} catch (error) {
|
|
await client.query("ROLLBACK");
|
|
throw error;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
|
|
try {
|
|
await main();
|
|
} finally {
|
|
await pool.end();
|
|
}
|