128 lines
3.6 KiB
JavaScript
Executable File
128 lines
3.6 KiB
JavaScript
Executable File
#!/usr/bin/env node
|
|
import "dotenv/config";
|
|
import pg from "pg";
|
|
|
|
const apply = process.argv.includes("--apply");
|
|
|
|
if (!process.env.DATABASE_URL) {
|
|
console.error("DATABASE_URL fehlt. Bitte .env setzen oder DATABASE_URL vor dem Aufruf exportieren.");
|
|
process.exit(1);
|
|
}
|
|
|
|
const updates = [
|
|
[
|
|
"tickets",
|
|
`
|
|
UPDATE tickets t
|
|
SET organization_id = old_org.zammad_id
|
|
FROM organizations old_org
|
|
WHERE t.organization_id = old_org.id
|
|
AND t.organization_id <> old_org.zammad_id
|
|
AND COALESCE(t.customer_name, '') = old_org.name
|
|
`
|
|
],
|
|
[
|
|
"sessions",
|
|
`
|
|
UPDATE sessions s
|
|
SET organization_id = old_org.zammad_id
|
|
FROM organizations old_org
|
|
WHERE s.organization_id = old_org.id
|
|
AND s.organization_id <> old_org.zammad_id
|
|
AND COALESCE(s.customer_name, '') = old_org.name
|
|
`
|
|
],
|
|
[
|
|
"recurring_billings",
|
|
`
|
|
UPDATE recurring_billings rb
|
|
SET organization_id = old_org.zammad_id
|
|
FROM organizations old_org
|
|
WHERE rb.organization_id = old_org.id
|
|
AND rb.organization_id <> old_org.zammad_id
|
|
AND (
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM sessions s
|
|
WHERE s.recurring_billing_id = rb.id
|
|
AND COALESCE(s.customer_name, '') = old_org.name
|
|
)
|
|
OR EXISTS (
|
|
SELECT 1
|
|
FROM tickets t
|
|
WHERE t.ticket_number = COALESCE(rb.ticket_number, 'Fix#' || rb.id)
|
|
AND COALESCE(t.customer_name, '') = old_org.name
|
|
)
|
|
)
|
|
`
|
|
]
|
|
];
|
|
|
|
const remainingSql = `
|
|
SELECT 'tickets' AS table_name, COUNT(*)::int AS repairable
|
|
FROM tickets t
|
|
JOIN organizations old_org ON old_org.id = t.organization_id
|
|
WHERE t.organization_id IS NOT NULL
|
|
AND old_org.zammad_id <> t.organization_id
|
|
AND COALESCE(t.customer_name, '') = old_org.name
|
|
UNION ALL
|
|
SELECT 'sessions' AS table_name, COUNT(*)::int AS repairable
|
|
FROM sessions s
|
|
JOIN organizations old_org ON old_org.id = s.organization_id
|
|
WHERE s.organization_id IS NOT NULL
|
|
AND old_org.zammad_id <> s.organization_id
|
|
AND COALESCE(s.customer_name, '') = old_org.name
|
|
UNION ALL
|
|
SELECT 'recurring_billings' AS table_name, COUNT(*)::int AS repairable
|
|
FROM recurring_billings rb
|
|
JOIN organizations old_org ON old_org.id = rb.organization_id
|
|
WHERE old_org.zammad_id <> rb.organization_id
|
|
AND (
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM sessions s
|
|
WHERE s.recurring_billing_id = rb.id
|
|
AND COALESCE(s.customer_name, '') = old_org.name
|
|
)
|
|
OR EXISTS (
|
|
SELECT 1
|
|
FROM tickets t
|
|
WHERE t.ticket_number = COALESCE(rb.ticket_number, 'Fix#' || rb.id)
|
|
AND COALESCE(t.customer_name, '') = old_org.name
|
|
)
|
|
);
|
|
`;
|
|
|
|
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
|
|
const client = await pool.connect();
|
|
|
|
try {
|
|
await client.query("BEGIN");
|
|
|
|
const resultRows = [];
|
|
for (const [table, sql] of updates) {
|
|
const result = await client.query(sql);
|
|
resultRows.push({ table, [apply ? "updated" : "would_update"]: result.rowCount ?? 0 });
|
|
}
|
|
|
|
if (apply) {
|
|
await client.query("COMMIT");
|
|
console.log("Organisationszuordnungen wurden korrigiert.");
|
|
} else {
|
|
await client.query("ROLLBACK");
|
|
console.log("Dry-Run: Es wurde nichts gespeichert. Mit --apply wirklich ausfuehren.");
|
|
}
|
|
|
|
console.table(resultRows);
|
|
|
|
const remaining = await pool.query(remainingSql);
|
|
console.log("Noch eindeutig reparierbare Alt-Zuordnungen:");
|
|
console.table(remaining.rows);
|
|
} catch (error) {
|
|
await client.query("ROLLBACK").catch(() => undefined);
|
|
throw error;
|
|
} finally {
|
|
client.release();
|
|
await pool.end();
|
|
}
|