Lars Nolden
dc767799bc
CSV import is now mapping-driven: N26, ING (metadata preamble, Windows-1252, German decimals) and Kontist exports are recognized locally, and any other layout can have its columns proposed by the configured model from a sample in which letters are replaced by x and digits by 0. Proposals are untrusted: every column must name a supplied header, money must come from one signed column or one debit/credit pair, and formats must be from a closed list. Uploading no longer imports. /api/import is replaced by prepare/confirm/cancel: prepare parses, deduplicates and previews the exact facts, and only confirming at the reviewed revision writes them. ING and AI-mapped facts carry no transaction reference, because repeating SEPA mandate references must never become a transaction identity.
277 lines
12 KiB
Go
277 lines
12 KiB
Go
package banking
|
|
|
|
import (
|
|
"strings"
|
|
"testing"
|
|
"unicode"
|
|
|
|
"finance-duck/internal/domain"
|
|
)
|
|
|
|
// ingExport reproduces ING's Umsatzanzeige: a metadata preamble above the
|
|
// columns, semicolons, German dates and decimals, and Windows-1252 umlauts.
|
|
const ingExport = "Umsatzanzeige;Datei erstellt am: 09.12.2025 15:23\n" +
|
|
"\nIBAN;DE89370400440532013000\nKontoname;Girokonto\nBank;ING\nKunde;Max Mustermann\n" +
|
|
"Zeitraum;17.11.2025 - 09.12.2025\nSaldo;66.331,90;EUR\n\nSortierung;Datum absteigend\n\n" +
|
|
"In der CSV-Datei finden Sie alle bereits gebuchten Ums\xe4tze, nicht die vorgemerkten.\n\n" +
|
|
"Buchung;Wertstellungsdatum;Auftraggeber/Empf\xe4nger;Buchungstext;Verwendungszweck;Betrag;W\xe4hrung\n" +
|
|
"09.12.2025;08.12.2025;VISA Firma;Lastschrift;NR XXXX 4025 KAUFUMSATZ;-13,98;EUR\n" +
|
|
"28.11.2025;28.11.2025;Rente;Gehalt/Rente;RV-RENTE 11.2025;2.647,74;EUR\n"
|
|
|
|
func readFixture(t *testing.T, text string) CSVFile {
|
|
t.Helper()
|
|
file, err := ReadCSV(strings.NewReader(text))
|
|
if err != nil {
|
|
t.Fatalf("read fixture: %v", err)
|
|
}
|
|
return file
|
|
}
|
|
|
|
func TestPresetMappingsParseKnownBankExports(t *testing.T) {
|
|
cases := []struct {
|
|
name, csv, source, label string
|
|
records int
|
|
booking, value, amount string
|
|
description, party, iban string
|
|
}{
|
|
{
|
|
name: "N26 English legacy quoted multiline", source: "n26_csv", label: "N26",
|
|
csv: "Date,Payee,Account number,Payment type,Payment reference,Amount (EUR),Amount (Foreign Currency),Type Foreign Currency,Exchange Rate\r\n2026-09-01,\"Cafe, Berlin\",DE02120300000000202051,MasterCard Payment,\"Lunch, first line\nsecond line\",-12.30,-14.50,USD,0.85\r\n",
|
|
records: 1, booking: "2026-09-01", amount: "-12.30",
|
|
description: "Lunch, first line\nsecond line", party: "Cafe, Berlin", iban: "DE02120300000000202051",
|
|
},
|
|
{
|
|
name: "N26 German decimal comma semicolon BOM", source: "n26_csv", label: "N26",
|
|
csv: "\ufeffDatum;Zahlungsempfänger;Kontonummer;Transaktionstyp;Verwendungszweck;Betrag (EUR);Betrag (Fremdwährung);Fremdwährung;Wechselkurs\n01.09.2026;Arbeitgeber;DE89 3704 0044 0532 0130 00;Überweisung;Gehalt;\"1.234,56\";;;\n",
|
|
records: 1, booking: "2026-09-01", amount: "1234.56",
|
|
description: "Gehalt", party: "Arbeitgeber", iban: "DE89370400440532013000",
|
|
},
|
|
{
|
|
name: "N26 English booking and value dates", source: "n26_csv", label: "N26",
|
|
csv: "Booking Date,Value Date,Partner Name,Partner IBAN,Type,Payment Reference,Account Name,Amount (EUR),Original Amount,Original Currency,Exchange Rate\n2026-09-01,2026-08-31,Cafe,DE02120300000000202051,Card,Lunch,Main,-12.30,-14.50,USD,0.85\n",
|
|
records: 1, booking: "2026-09-01", value: "2026-08-31", amount: "-12.30",
|
|
description: "Lunch", party: "Cafe", iban: "DE02120300000000202051",
|
|
},
|
|
{
|
|
name: "ING Umsatzanzeige with preamble and Windows-1252", source: "ing_csv", label: "ING",
|
|
csv: ingExport,
|
|
records: 2, booking: "2025-12-09", value: "2025-12-08", amount: "-13.98",
|
|
description: "NR XXXX 4025 KAUFUMSATZ", party: "VISA Firma",
|
|
},
|
|
{
|
|
name: "Kontist English month-first dates", source: "kontist_csv", label: "Kontist",
|
|
csv: "Payment Date,Name,Amount,Purpose,Currency\n01/15/2026,Client GmbH,1200.00,Invoice 2026-01,EUR\n01/16/2026,Cafe,-4.20,Espresso,EUR\n",
|
|
records: 2, booking: "2026-01-15", amount: "1200.00",
|
|
description: "Invoice 2026-01", party: "Client GmbH",
|
|
},
|
|
{
|
|
name: "Kontist German day-first dates and grouping", source: "kontist_csv", label: "Kontist",
|
|
csv: "Buchungsdatum;Name;Betrag;Verwendungszweck;Währung\n15/01/2026;Client GmbH;1.200,00;Rechnung;EUR\n",
|
|
records: 1, booking: "2026-01-15", amount: "1200.00",
|
|
description: "Rechnung", party: "Client GmbH",
|
|
},
|
|
}
|
|
for _, tt := range cases {
|
|
t.Run(tt.name, func(t *testing.T) {
|
|
file := readFixture(t, tt.csv)
|
|
mapping, source, label, ok := DetectCSVMapping(file)
|
|
if !ok || source != tt.source || label != tt.label {
|
|
t.Fatalf("detected %q/%q (ok=%t), want %q/%q", source, label, ok, tt.source, tt.label)
|
|
}
|
|
facts, err := ParseMappedCSV(file, fixtureDataset().Accounts[0], mapping, source)
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
if len(facts) != tt.records {
|
|
t.Fatalf("records: %d, want %d", len(facts), tt.records)
|
|
}
|
|
f := facts[0]
|
|
if f.BookingDate != tt.booking || f.ValueDate != tt.value || f.Amount.String() != tt.amount ||
|
|
f.RawDescription != tt.description || f.Counterparty != tt.party || f.CounterpartyIBAN != tt.iban ||
|
|
f.Currency != "EUR" || f.Source != tt.source || f.AccountID != "account_a" {
|
|
t.Fatalf("unexpected facts: %+v", f)
|
|
}
|
|
})
|
|
}
|
|
}
|
|
|
|
// ING's second record is income; a preset must not flatten the direction or the
|
|
// German grouping of larger amounts.
|
|
func TestINGRetainsDirectionAndGrouping(t *testing.T) {
|
|
file := readFixture(t, ingExport)
|
|
mapping, source, _, _ := DetectCSVMapping(file)
|
|
facts, err := ParseMappedCSV(file, fixtureDataset().Accounts[0], mapping, source)
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
if len(facts) != 2 || facts[1].Amount.String() != "2647.74" || facts[1].BookingDate != "2025-11-28" {
|
|
t.Fatalf("income record lost: %+v", facts)
|
|
}
|
|
// Mandate and customer references repeat across bookings, so no preset may
|
|
// present them as a stable per-transaction identity.
|
|
for _, f := range facts {
|
|
if f.ExternalID != "" {
|
|
t.Fatalf("ING facts must not carry a synthetic reference: %q", f.ExternalID)
|
|
}
|
|
}
|
|
}
|
|
|
|
func TestUnreadableAndUnrecognizedStatementsAreRejected(t *testing.T) {
|
|
for name, input := range map[string]string{
|
|
"empty": "",
|
|
"blank": "\n\n",
|
|
"unterminated quote": "Date,Amount (EUR)\n2026-09-01,\"unterminated\n",
|
|
"NUL byte": "Date;Betrag\n2026-09-01;-1,00\x00\n",
|
|
} {
|
|
t.Run(name, func(t *testing.T) {
|
|
if _, err := ReadCSV(strings.NewReader(input)); err == nil {
|
|
t.Fatal("accepted an unreadable statement")
|
|
}
|
|
})
|
|
}
|
|
for name, input := range map[string]string{
|
|
"no known columns": "Foo;Bar\n1;2\n",
|
|
"duplicate columns": "Datum;Datum;Betrag\n2026-09-01;2026-09-01;-1,00\n",
|
|
"foreign amount only": "Date,Payee,Amount (Foreign Currency)\n2026-09-01,Cafe,-1.00\n",
|
|
} {
|
|
t.Run(name, func(t *testing.T) {
|
|
file := readFixture(t, input)
|
|
if _, _, _, ok := DetectCSVMapping(file); ok {
|
|
t.Fatal("guessed a mapping for an unrecognized layout")
|
|
}
|
|
})
|
|
}
|
|
if _, err := readFixture(t, "A;B;C\n1;2\n").Sample(); err == nil {
|
|
t.Fatal("ragged records were offered for mapping")
|
|
}
|
|
if _, err := readFixture(t, "A;A\n1;2\n").Sample(); err == nil {
|
|
t.Fatal("duplicate columns were offered for mapping")
|
|
}
|
|
if _, err := readFixture(t, "Buchung;Betrag;Verwendungszweck;Auftraggeber/Empfänger\n").Sample(); err == nil {
|
|
t.Fatal("a statement without records was offered for mapping")
|
|
}
|
|
}
|
|
|
|
func TestMappedCSVRejectsInvalidMappingsAndRecords(t *testing.T) {
|
|
const document = "Datum;Betrag;Text;Waehrung\n2026-09-01;-1,00;Lunch;EUR\n"
|
|
valid := CSVMapping{HeaderRow: 1, BookingDateColumn: "Datum", AmountColumn: "Betrag", DescriptionColumn: "Text", DateFormat: "iso-or-german", DecimalFormat: "comma"}
|
|
with := func(change func(*CSVMapping)) CSVMapping {
|
|
mapping := valid
|
|
change(&mapping)
|
|
return mapping
|
|
}
|
|
mappings := map[string]CSVMapping{
|
|
"unknown column": with(func(m *CSVMapping) { m.AmountColumn = "Amount" }),
|
|
"missing description": with(func(m *CSVMapping) { m.DescriptionColumn = "" }),
|
|
"missing booking date": with(func(m *CSVMapping) { m.BookingDateColumn = "" }),
|
|
"both money strategies": with(func(m *CSVMapping) { m.DebitColumn = "Betrag"; m.CreditColumn = "Betrag" }),
|
|
"debit without credit": with(func(m *CSVMapping) { m.AmountColumn = ""; m.DebitColumn = "Betrag" }),
|
|
"no money strategy": with(func(m *CSVMapping) { m.AmountColumn = "" }),
|
|
"column used twice": with(func(m *CSVMapping) { m.CounterpartyColumn = "Text" }),
|
|
"unsupported date": with(func(m *CSVMapping) { m.DateFormat = "%Y-%m-%d" }),
|
|
"unsupported decimal": with(func(m *CSVMapping) { m.DecimalFormat = "german" }),
|
|
"header row before file": with(func(m *CSVMapping) { m.HeaderRow = 0 }),
|
|
"header row past file": with(func(m *CSVMapping) { m.HeaderRow = 9 }),
|
|
"invalid fixed currency": with(func(m *CSVMapping) { m.FixedCurrency = "Euro" }),
|
|
}
|
|
for name, mapping := range mappings {
|
|
t.Run(name, func(t *testing.T) {
|
|
if facts, err := ParseMappedCSV(readFixture(t, document), fixtureDataset().Accounts[0], mapping, "csv"); err == nil || facts != nil {
|
|
t.Fatalf("accepted an invalid mapping: %+v", mapping)
|
|
}
|
|
})
|
|
}
|
|
records := map[string]string{
|
|
"unparseable amount": "Datum;Betrag;Text;Waehrung\n2026-09-01;-1,00;Lunch;EUR\n2026-09-02;nope;Lunch;EUR\n",
|
|
"impossible date": "Datum;Betrag;Text;Waehrung\n2026-02-30;-1,00;Lunch;EUR\n",
|
|
"missing amount": "Datum;Betrag;Text;Waehrung\n2026-09-01;;Lunch;EUR\n",
|
|
"short record": "Datum;Betrag;Text;Waehrung\n2026-09-01;-1,00\n",
|
|
"three comma groups": "Datum;Betrag;Text;Waehrung\n2026-09-01;1,00,00;Lunch;EUR\n",
|
|
}
|
|
for name, document := range records {
|
|
t.Run(name, func(t *testing.T) {
|
|
if facts, err := ParseMappedCSV(readFixture(t, document), fixtureDataset().Accounts[0], valid, "csv"); err == nil || facts != nil {
|
|
t.Fatalf("accepted a malformed record: %q", document)
|
|
}
|
|
})
|
|
}
|
|
// A currency the account does not hold must fail the whole statement rather
|
|
// than silently booking foreign money against it.
|
|
conflicting := valid
|
|
conflicting.CurrencyColumn = "Waehrung"
|
|
if _, err := ParseMappedCSV(readFixture(t, "Datum;Betrag;Text;Waehrung\n2026-09-01;-1,00;Lunch;USD\n"), fixtureDataset().Accounts[0], conflicting, "csv"); err == nil {
|
|
t.Fatal("imported a foreign currency into a EUR account")
|
|
}
|
|
if _, err := ParseMappedCSV(readFixture(t, document), fixtureDataset().Accounts[0], valid, ""); err == nil {
|
|
t.Fatal("imported facts without an import source")
|
|
}
|
|
if _, err := ParseMappedCSV(readFixture(t, document), domain.Account{Currency: "EUR"}, valid, "csv"); err == nil {
|
|
t.Fatal("imported facts without an account")
|
|
}
|
|
}
|
|
|
|
func TestSplitDebitAndCreditColumnsCarryDirection(t *testing.T) {
|
|
const document = "Date;Debit;Credit;Text\n" +
|
|
"2026-09-01;12,30;;Lunch\n" +
|
|
"2026-09-02;;100,00;Salary\n" +
|
|
"2026-09-03;0,00;5,00;Refund\n" +
|
|
"2026-09-04;-7,50;;Signed debit\n"
|
|
mapping := CSVMapping{HeaderRow: 1, BookingDateColumn: "Date", DebitColumn: "Debit", CreditColumn: "Credit", DescriptionColumn: "Text", DateFormat: "yyyy-mm-dd", DecimalFormat: "comma"}
|
|
facts, err := ParseMappedCSV(readFixture(t, document), fixtureDataset().Accounts[0], mapping, "csv")
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
want := []string{"-12.30", "100.00", "5.00", "-7.50"}
|
|
if len(facts) != len(want) {
|
|
t.Fatalf("records: %d", len(facts))
|
|
}
|
|
for i, amount := range want {
|
|
if facts[i].Amount.String() != amount {
|
|
t.Fatalf("record %d: %s, want %s", i+1, facts[i].Amount, amount)
|
|
}
|
|
}
|
|
for name, document := range map[string]string{
|
|
"both populated": "Date;Debit;Credit;Text\n2026-09-01;12,30;5,00;Ambiguous\n",
|
|
"both empty": "Date;Debit;Credit;Text\n2026-09-01;;;Empty\n",
|
|
"negative credit": "Date;Debit;Credit;Text\n2026-09-01;;-5,00;Negative\n",
|
|
} {
|
|
t.Run(name, func(t *testing.T) {
|
|
if _, err := ParseMappedCSV(readFixture(t, document), fixtureDataset().Accounts[0], mapping, "csv"); err == nil {
|
|
t.Fatalf("accepted ambiguous split money: %q", document)
|
|
}
|
|
})
|
|
}
|
|
}
|
|
|
|
// A mapping sample must describe structure only: no account text, counterparty,
|
|
// reference, IBAN or amount digit may survive redaction.
|
|
func TestSampleRedactsValuesAndLocatesHeaderBelowPreamble(t *testing.T) {
|
|
sample, err := readFixture(t, ingExport).Sample()
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
if sample.Delimiter != ";" || len(sample.Headers) != 7 || sample.Headers[0] != "Buchung" {
|
|
t.Fatalf("header row not located: %+v", sample)
|
|
}
|
|
// Blank lines are not records: the header is the tenth parsed record.
|
|
if sample.HeaderRow != 10 || sample.RecordCount != 2 || len(sample.ShapedRows) != 2 {
|
|
t.Fatalf("unexpected sample shape: record=%d records=%d rows=%d", sample.HeaderRow, sample.RecordCount, len(sample.ShapedRows))
|
|
}
|
|
for _, row := range sample.ShapedRows {
|
|
for _, value := range row {
|
|
for _, r := range value {
|
|
if unicode.IsLetter(r) && r != 'x' || unicode.IsDigit(r) && r != '0' {
|
|
t.Fatalf("sample leaked statement content: %q", value)
|
|
}
|
|
}
|
|
}
|
|
}
|
|
for _, secret := range []string{"VISA", "Rente", "KAUFUMSATZ", "13", "98", "2647", "DE89"} {
|
|
for _, row := range sample.ShapedRows {
|
|
if strings.Contains(strings.Join(row, "|"), secret) {
|
|
t.Fatalf("sample leaked %q", secret)
|
|
}
|
|
}
|
|
}
|
|
}
|