clemensandClaude Sonnet 5
92e124753f
csvupload.php verarbeitete CSV-Zeilen bisher sofort beim Upload, ohne Vorschau, ohne Zugriffskontrolle (nur CSRF) und schrieb ausschliesslich in die global unscoped kl_Einzahlungen-Tabelle. - Neue Tabellen payment_import_batches/payment_import_rows protokollieren jeden Import: Datei, Pruefsumme, jede Zeile mit Rohwerten, erkanntem Mitglied, Status und erzeugter Ledger-Zeile. - Zweistufiger Ablauf: Hochladen zeigt nur eine Vorschau (matched/ duplicate/unmatched/invalid), erst "Import bestaetigen" bucht. - Zuordnung per paypal_name oder display_name (tenant-scoped), Dubletten- pruefung gegen bestehende nicht-stornierte Ledger-Zahlungen. - Buchung folgt dem etablierten Zweig-Muster: Default-Mandant per Dual-Write nach kl_Einzahlungen plus Spiegelung, alle anderen Mandanten direkt ueber ledger_record_payment(). - Zugriffskontrolle ergaenzt (owner/admin/treasurer + Legacy-Fallback). - Live getestet: Treffer, unbekannter Name, ungueltiger Betrag korrekt klassifiziert; Import bestaetigt mit korrektem Dual-Write; erneuter Upload derselben Datei erkennt die Zeile korrekt als Dublette. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
62 lines
2.2 KiB
SQL
62 lines
2.2 KiB
SQL
CREATE TABLE IF NOT EXISTS payment_import_batches (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id INT NOT NULL,
|
|
original_filename VARCHAR(255) NOT NULL,
|
|
checksum CHAR(64) NOT NULL,
|
|
status VARCHAR(30) NOT NULL DEFAULT 'previewed',
|
|
created_by_user_id INT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
committed_at DATETIME NULL,
|
|
KEY idx_payment_import_batches_tenant (tenant_id, created_at),
|
|
CONSTRAINT fk_payment_import_batches_tenant
|
|
FOREIGN KEY (tenant_id) REFERENCES tenants(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_payment_import_batches_created_by_user
|
|
FOREIGN KEY (created_by_user_id) REFERENCES users(id)
|
|
ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS payment_import_rows (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
batch_id INT NOT NULL,
|
|
row_num INT NOT NULL,
|
|
participant_id INT NULL,
|
|
raw_name VARCHAR(255) NOT NULL,
|
|
amount_cents INT NOT NULL,
|
|
booked_at DATETIME NOT NULL,
|
|
status VARCHAR(30) NOT NULL,
|
|
raw_json TEXT NULL,
|
|
ledger_entry_id INT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
KEY idx_payment_import_rows_batch (batch_id),
|
|
KEY idx_payment_import_rows_participant (participant_id),
|
|
CONSTRAINT fk_payment_import_rows_batch
|
|
FOREIGN KEY (batch_id) REFERENCES payment_import_batches(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_payment_import_rows_participant
|
|
FOREIGN KEY (participant_id) REFERENCES participants(id)
|
|
ON DELETE SET NULL,
|
|
CONSTRAINT fk_payment_import_rows_ledger_entry
|
|
FOREIGN KEY (ledger_entry_id) REFERENCES ledger_entries(id)
|
|
ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
SET @schema_name = DATABASE();
|
|
|
|
SET @sql = (
|
|
SELECT IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
|
|
WHERE TABLE_SCHEMA = @schema_name
|
|
AND TABLE_NAME = 'ledger_entries'
|
|
AND CONSTRAINT_NAME = 'fk_ledger_entries_import_batch'
|
|
),
|
|
'SELECT 1',
|
|
'ALTER TABLE ledger_entries ADD CONSTRAINT fk_ledger_entries_import_batch FOREIGN KEY (import_batch_id) REFERENCES payment_import_batches(id) ON DELETE SET NULL'
|
|
)
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|