diff options
| -rw-r--r-- | docs/PROTOCOL.md | 54 | ||||
| -rw-r--r-- | docs/SCHEMA.md | 68 | ||||
| -rw-r--r-- | src/commands.c | 891 | ||||
| -rw-r--r-- | src/db.c | 81 | ||||
| -rw-r--r-- | src/db.h | 2 | ||||
| -rw-r--r-- | tests/test_core.c | 254 |
6 files changed, 1340 insertions, 10 deletions
diff --git a/docs/PROTOCOL.md b/docs/PROTOCOL.md index 6f26463..d7e70be 100644 --- a/docs/PROTOCOL.md +++ b/docs/PROTOCOL.md @@ -108,6 +108,7 @@ Scopes on a token can narrow but never widen the user's role. |---|---|---|---|---| | Read: vouchers, reports, audit, accounts | ● | ● | ● | ● | | `voucher.post`, `voucher.correct`, `attachment.put` | | ● | ● | | +| `bank.import`, `bank.match`, `bank.unmatch` | | ● | ● | | | `sie.import`, `account.create`, `account.update` | | ● | ● | | | `period.lock`, `fiscal_year.open/close/reopen`, `org.update` | | | ● | | | `org.member_*`, `token.create` for others | | | ● | | @@ -370,12 +371,14 @@ resolved rows in a dry run. | Command | Args | Notes | |---|---|---| | `settings.get` | — | effective org settings (defaults included) | -| `settings.set` | `key`, `value` | known keys: `default_series`, `attachment_dir` | +| `settings.set` | `key`, `value` | known keys: `default_series`, `attachment_dir`, `bank_account` | `default_series` (1–8 characters, e.g. `A`, `V-`, `A `) is used when `voucher.post` carries no `series` and as the default series for new templates. `attachment_dir` (a path, up to 255 characters) is the folder the -TUI file browser opens in when attaching underlag. Verification ids are the concatenation of series and number +TUI file browser opens in when attaching underlag. `bank_account` (digits +only, up to 10 characters, default `1930`) is the account `bank.import` uses +when the request carries no `account`. Verification ids are the concatenation of series and number (`V-8`), and series are free-form: only an unbroken numbering per series is required. @@ -496,6 +499,53 @@ be created. backup; `full:true` includes attachment hashes. The voucher chain is verified per org in posting order (SCHEMA.md §7.1); the audit chain globally. +### 7.9 Bank reconciliation (statement import) + +Phase 1 is mechanical reconciliation only: statements are imported as +read-only evidence and already-booked vouchers are matched against them. +**It never books anything** — no voucher is created or changed by these +commands. Suggestions are advisory; a human (or an agent) must post any +missing voucher with `voucher.post` and then match it. + +| Command | Args | Result | +|---|---|---| +| `bank.import` | `format`, `content_base64` or `path`, `account?` | `format`, `account`, `total`, `imported`, `duplicates`, `first_date`, `last_date`; `dry_run?` | +| `bank.list` | `status?`, `from?`, `to?`, `account?`, `limit` | `items[{id,account,booked_at,value_date,text,type,amount_ore,balance_ore,matches[],suggestions[]}]`, `summary{unmatched,matched,unmatched_amount_ore}` | +| `bank.match` | `transaction_id`, `voucher_id` | `transaction_id`, `voucher_id`, `difference_ore` | +| `bank.unmatch` | `transaction_id`, `voucher_id` | `transaction_id`, `voucher_id`, `unmatched:true` | + +`bank.import` takes one SEB CSV export (`format:"seb"`): UTF-8 with an +optional BOM, `;`-separated, the header exactly +`Bokförd;Valutadatum;Text;Typ;Insättningar;Uttag;Bokfört saldo`. Quoted fields +use `""` for an embedded quote; amounts use decimal comma and may group +thousands with spaces or `.`; the `Insättningar` and `Uttag` columns are +mutually exclusive and exactly one must be non-empty (a withdrawal becomes a +negative amount); the balance may be empty. Files larger than 64 MiB are +rejected with `TOO_LARGE`, an unknown account with `ACCOUNT_NOT_FOUND`, a +header mismatch or malformed row (reported with its line number) with +`INVALID_ARGS`. Each row is hashed over its canonical field encoding incl. the +account, so re-importing the same export only reports duplicates +(`imported:0`). `account` defaults to the `bank_account` setting, else `1930`. +The result's `first_date`/`last_date` span every row in the file. + +`bank.list` items carry their `matches` (`voucher_id`, `series`, `number`, +`date`, `bank_amount_ore` = the voucher's signed movement on the transaction's +account) and, for unmatched transactions, up to three advisory `suggestions` +(`voucher_id`, `series`, `number`, `date`, `amount_ore`): posted vouchers that +touch the account, whose movement on it equals the transaction amount exactly, +are dated within ±5 days and are not yet matched to any transaction. +`summary` always counts all transactions for the org (optionally narrowed by +`account`), ignoring `status`, `from` and `to`. + +`bank.match` links one transaction to one voucher. The voucher must post to +the transaction's account; matching the same pair twice is a `CONFLICT`. +Several vouchers may match one transaction (partial matching) and +`difference_ore` is the transaction amount minus the summed bank legs after +the insert, so `0` means the transaction is fully reconciled. `bank.unmatch` +removes one link and is a `NOT_FOUND` when it does not exist. Both mutate +`bank_matches` only and are audited (`bank.match`, `bank.unmatch`); +`bank.import` is audited as `bank.import`. + ## 8. The TUI is just a client `bokftui` logs in over the same socket, picks an org and issues the same diff --git a/docs/SCHEMA.md b/docs/SCHEMA.md index 2a85521..0f360d5 100644 --- a/docs/SCHEMA.md +++ b/docs/SCHEMA.md @@ -47,6 +47,7 @@ orgs ─┬─ memberships ── users ── api_tokens ├─ audit_log (global chain, org_id nullable) ├─ idempotency ├─ report_rules + ├─ bank_transactions ── bank_matches └─ settings ``` @@ -330,6 +331,64 @@ underlag can be unlinked). Unlinked attachments form the inbox the TUI shows. The 7-year archive rule means content must never be garbage-collected; deduplication by hash keeps repeated receipts cheap. +### 8.1 Bank transactions and matches (schema v8) + +Phase 1 of bank reconciliation: imported statement rows are evidence, never +ledger data, and matching only links them to already-booked vouchers. No path +in the server books, edits or deletes a voucher from here. + +```sql +CREATE TABLE bank_transactions ( + org_id INTEGER NOT NULL REFERENCES orgs(id), + id INTEGER PRIMARY KEY, + account TEXT NOT NULL, -- bank account number, e.g. "1930" + booked_at TEXT NOT NULL, -- YYYY-MM-DD + value_date TEXT NOT NULL, -- YYYY-MM-DD + text TEXT NOT NULL, + type TEXT NOT NULL, + amount_ore INTEGER NOT NULL, -- signed: deposit positive, withdrawal negative + balance_ore INTEGER, -- nullable + source TEXT NOT NULL, -- "seb-csv" + source_hash BLOB NOT NULL CHECK (length(source_hash) = 32), + imported_at TEXT NOT NULL, + imported_by INTEGER NOT NULL REFERENCES users(id), + UNIQUE (org_id, id), + UNIQUE (org_id, source_hash) +) STRICT; +CREATE INDEX idx_bank_tx_date ON bank_transactions(org_id, booked_at); + +CREATE TABLE bank_matches ( + org_id INTEGER NOT NULL, + transaction_id INTEGER NOT NULL, + voucher_id INTEGER NOT NULL, + matched_at TEXT NOT NULL, + matched_by INTEGER NOT NULL REFERENCES users(id), + kind TEXT NOT NULL DEFAULT 'manual' CHECK (kind IN ('manual','auto')), + PRIMARY KEY (org_id, transaction_id, voucher_id), + FOREIGN KEY (org_id, transaction_id) REFERENCES bank_transactions(org_id, id), + FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id) +) STRICT; +``` + +Imported rows are immutable in practice: the daemon exposes no update or +delete handler for `bank_transactions`, and re-importing the same export is a +no-op thanks to `UNIQUE (org_id, source_hash)`. `kind:'auto'` is reserved for +a later matching phase; phase 1 writes `'manual'` only. `bank_matches` stays +mutable so a wrong link can be removed (`bank.unmatch`); every link and unlink +is audited. A transaction may have several matches (partial payments), and one +voucher may reconcile several transactions. + +The `source_hash` is SHA-256 over a canonical encoding of the row including +the account: `"bokf-v1-bank-tx\0"`, then each of `account`, `booked_at`, +`value_date` as `u16len + UTF-8`, `text` and `type` as `u32len + UTF-8`, +`amount_ore` as `i64be`, and a `u8` flag followed by `i64be balance_ore` when +the balance is present. The SEB parser and this encoding live in +`src/commands.c`. + +The `settings` key `bank_account` (digits only, up to 10 characters, default +`1930`) selects the account `bank.import` uses when the request omits +`account`. See `PROTOCOL.md` §7.9. + ## 9. Audit log and idempotency ```sql @@ -388,7 +447,8 @@ Actions written to the log include: `auth.open`, `auth.fail`, `session.close`, `account.update`, `fiscal_year.open`, `fiscal_year.close`, `period.lock`, `period.unlock`, `voucher.post`, `voucher.correct`, `attachment.put`, `attachment.link`, `sie.import`, `sie.export`, `backup.snapshot`, -`settings.update`. Reads are logged only when `audit_reads = true`. +`bank.import`, `bank.match`, `bank.unmatch`, `settings.update`. Reads are +logged only when `audit_reads = true`. ## 10. Reporting rules and settings @@ -489,8 +549,10 @@ another voucher is posted in between) — clients must not persist it. ## 12. Migrations and versioning - `meta(key TEXT PRIMARY KEY, value TEXT)` holds `schema_version` (integer) - and `created_at`. Current version: **3** (v3 replaces the seeded moms rules - with the corrected mapping; v2 adds the two template tables). + and `created_at`. Current version: **8** (v8 adds the two bank + reconciliation tables, v7 makes attachments append-only, v3 replaces the + seeded moms rules with the corrected mapping; v2 adds the two template + tables). - Migrations are forward-only, applied automatically at daemon start, each in one transaction. Before the first migration statement a consistent `VACUUM INTO` snapshot is written to diff --git a/src/commands.c b/src/commands.c index 2abe0d4..06a7725 100644 --- a/src/commands.c +++ b/src/commands.c @@ -1612,6 +1612,9 @@ static const char AGENT_INSTRUCTIONS[] = "- Konteringsmallar: list with `template.list`, apply with `voucher.post`\n" " `{\"template\":\"name\",\"x\":1250}` where x is kronor; formulas use x,\n" " numbers and + - * /. Positive results debit, negative credit.\n" + "- Bank: `bank.import` stores a SEB CSV statement as read-only evidence and\n" + " `bank.list` suggests already-posted vouchers to match with `bank.match`;\n" + " reconciliation never posts vouchers by itself.\n" "\n" "## Discovery\n" "- `describe` lists every implemented command with permissions.\n" @@ -2604,7 +2607,7 @@ static int parse_attachment_ids(struct req *r, int64_t **out, size_t *out_n) static yyjson_mut_val *h_settings_get(struct req *r) { yyjson_mut_val *o = yyjson_mut_obj(r->rdoc); - int have_default = 0; + int have_default = 0, have_bank = 0; sqlite3_stmt *st = NULL; if (sqlite3_prepare_v2( r->db, "SELECT key,value FROM settings WHERE org_id=?1", -1, &st, @@ -2618,12 +2621,16 @@ static yyjson_mut_val *h_settings_get(struct req *r) yyjson_mut_strcpy(r->rdoc, v ? v : "")); if (strcmp(k, "default_series") == 0) have_default = 1; + if (strcmp(k, "bank_account") == 0) + have_bank = 1; } } sqlite3_finalize(st); } if (!have_default) yyjson_mut_obj_add_strcpy(r->rdoc, o, "default_series", "A"); + if (!have_bank) + yyjson_mut_obj_add_strcpy(r->rdoc, o, "bank_account", "1930"); return o; } @@ -2634,20 +2641,27 @@ static yyjson_mut_val *h_settings_set(struct req *r) if (!key || !value) return fail(r, "INVALID_ARGS", "key and value are required"); size_t maxlen; + int digits_only = 0; if (strcmp(key, "default_series") == 0) maxlen = 8; else if (strcmp(key, "attachment_dir") == 0) maxlen = 255; - else + else if (strcmp(key, "bank_account") == 0) { + maxlen = 10; + digits_only = 1; + } else return fail(r, "UNSUPPORTED", "unknown setting"); size_t len = strlen(value); if (len == 0 || len > maxlen) return failf(r, "INVALID_ARGS", "%s must be 1-%zu characters", key, maxlen); - for (const char *p = value; *p; p++) + for (const char *p = value; *p; p++) { + if (digits_only && (*p < '0' || *p > '9')) + return failf(r, "INVALID_ARGS", "%s must be digits only", key); if ((unsigned char)*p < 32) return failf(r, "INVALID_ARGS", "%s must not contain control characters", key); + } if (r->dry_run) { yyjson_mut_val *o = yyjson_mut_obj(r->rdoc); yyjson_mut_obj_add_strcpy(r->rdoc, o, "key", key); @@ -4975,6 +4989,842 @@ static yyjson_mut_val *h_sie_import(struct req *r) return o; } +/* ------------------------------------------------------------------ */ +/* bank reconciliation (phase 1: import and match, never book) */ +/* ------------------------------------------------------------------ */ + +struct seb_row { + const char *booked_at; + const char *value_date; + const char *text; + const char *type; + int64_t amount_ore; + int has_balance; + int64_t balance_ore; +}; + +static int csv_split(char *line, char **fields, int max) +{ + int nf = 0, in_quotes = 0; + char *dst = line; + fields[nf++] = dst; + for (char *p = line; *p; p++) { + if (*p == '"') { + if (in_quotes && p[1] == '"') { + *dst++ = '"'; + p++; + } else { + in_quotes = !in_quotes; + } + } else if (*p == ';' && !in_quotes) { + *dst++ = '\0'; + if (nf >= max) + return -1; + fields[nf++] = dst; + } else { + *dst++ = *p; + } + } + *dst = '\0'; + return in_quotes ? -1 : nf; +} + +static int seb_amount(const char *s, int64_t *out) +{ + while (*s == ' ') + s++; + int neg = 0; + if (*s == '-') { + neg = 1; + s++; + } else if (*s == '+') { + s++; + } + int64_t whole = 0, frac = 0; + int digits = 0, fdigits = 0, comma = 0; + for (; *s; s++) { + if (*s == ' ' || *s == '.') + continue; + if (*s == ',') { + if (comma) + return -1; + comma = 1; + continue; + } + if (*s < '0' || *s > '9') + return -1; + if (comma) { + if (fdigits >= 2) + return -1; + frac = frac * 10 + (*s - '0'); + fdigits++; + } else { + if (whole > (INT64_MAX - 9) / 10) + return -1; + whole = whole * 10 + (*s - '0'); + digits++; + } + } + if (digits == 0) + return -1; + while (fdigits < 2) { + frac *= 10; + fdigits++; + } + if (whole > (INT64_MAX - frac) / 100) + return -1; + int64_t v = whole * 100 + frac; + *out = neg ? -v : v; + return 0; +} + +static int seb_parse(char *csv, struct seb_row **out, size_t *out_n, + char *err, size_t errlen) +{ + static const char *header[7] = { + "Bokförd", "Valutadatum", "Text", "Typ", + "Insättningar", "Uttag", "Bokfört saldo", + }; + struct seb_row *rows = NULL; + size_t n = 0, cap = 0; + char *cur = csv; + int lineno = 0; + while (*cur) { + lineno++; + char *nl = strchr(cur, '\n'); + if (nl) + *nl = '\0'; + size_t llen = strlen(cur); + if (llen && cur[llen - 1] == '\r') + cur[--llen] = '\0'; + char *fields[8]; + int nf = csv_split(cur, fields, 8); + if (nf < 0) { + snprintf(err, errlen, "malformed SEB CSV on line %d", lineno); + goto bad; + } + if (lineno == 1) { + int ok = nf == 7; + for (int i = 0; ok && i < 7; i++) + if (strcmp(fields[i], header[i]) != 0) + ok = 0; + if (!ok) { + snprintf(err, errlen, "not a SEB CSV export: header mismatch"); + goto bad; + } + if (!nl) + break; + cur = nl + 1; + continue; + } + if (llen == 0) { + if (!nl) + break; + cur = nl + 1; + continue; + } + if (nf != 7) { + snprintf(err, errlen, "malformed SEB CSV on line %d", lineno); + goto bad; + } + struct seb_row row; + row.booked_at = fields[0]; + row.value_date = fields[1]; + row.text = fields[2]; + row.type = fields[3]; + row.has_balance = fields[6][0] != '\0'; + row.balance_ore = 0; + int has_dep = fields[4][0] != '\0'; + int has_wd = fields[5][0] != '\0'; + if (!util_date_valid(fields[0]) || !util_date_valid(fields[1]) || + has_dep + has_wd != 1) { + snprintf(err, errlen, "malformed SEB CSV on line %d", lineno); + goto bad; + } + if (has_dep) { + if (seb_amount(fields[4], &row.amount_ore) != 0) { + snprintf(err, errlen, "malformed SEB CSV on line %d", lineno); + goto bad; + } + } else { + int64_t w = 0; + if (seb_amount(fields[5], &w) != 0) { + snprintf(err, errlen, "malformed SEB CSV on line %d", lineno); + goto bad; + } + row.amount_ore = w > 0 ? -w : w; + } + if (row.has_balance && seb_amount(fields[6], &row.balance_ore) != 0) { + snprintf(err, errlen, "malformed SEB CSV on line %d", lineno); + goto bad; + } + if (n == cap) { + cap = cap ? cap * 2 : 64; + rows = xrealloc(rows, cap * sizeof *rows); + } + rows[n++] = row; + if (!nl) + break; + cur = nl + 1; + } + *out = rows; + *out_n = n; + return 0; +bad: + free(rows); + return -1; +} + +static void bank_field(struct buf *b, const char *s, int wide) +{ + size_t n = strlen(s); + if (wide) + buf_append_u32be(b, (uint32_t)n); + else + buf_append_u16be(b, (uint16_t)n); + buf_append(b, s, n); +} + +static void bank_row_hash(const char *account, const struct seb_row *row, + unsigned char out[32]) +{ + static const char tag[] = "bokf-v1-bank-tx"; + struct buf b; + buf_init(&b); + buf_append(&b, tag, sizeof tag); + bank_field(&b, account, 0); + bank_field(&b, row->booked_at, 0); + bank_field(&b, row->value_date, 0); + bank_field(&b, row->text, 1); + bank_field(&b, row->type, 1); + buf_append_u64be(&b, (uint64_t)row->amount_ore); + unsigned char flag = row->has_balance ? 1 : 0; + buf_append(&b, &flag, 1); + if (row->has_balance) + buf_append_u64be(&b, (uint64_t)row->balance_ore); + util_sha256(b.p, b.len, out); + buf_free(&b); +} + +static int bank_hash_cmp(const void *a, const void *b) +{ + return memcmp(a, b, 32); +} + +static int bank_account_exists(struct req *r, const char *account) +{ + sqlite3_stmt *st = NULL; + if (sqlite3_prepare_v2( + r->db, "SELECT 1 FROM accounts WHERE org_id=?1 AND number=?2", -1, + &st, NULL) != SQLITE_OK) + return -1; + sqlite3_bind_int64(st, 1, r->org_id); + sqlite3_bind_text(st, 2, account, -1, SQLITE_TRANSIENT); + int found = sqlite3_step(st) == SQLITE_ROW; + sqlite3_finalize(st); + return found; +} + +static yyjson_mut_val *h_bank_import(struct req *r) +{ + const char *format = arg_str(r->args, "format"); + const char *b64 = arg_str(r->args, "content_base64"); + const char *path = arg_str(r->args, "path"); + const char *account = arg_str(r->args, "account"); + if (!format || strcmp(format, "seb") != 0) + return fail(r, "INVALID_ARGS", "format must be \"seb\""); + unsigned char *content = NULL; + size_t len = 0; + if (b64) { + if (util_b64_decode(b64, strlen(b64), &content, &len) != 0) + return fail(r, "INVALID_ARGS", + "content_base64 is not valid base64"); + } else if (path) { + content = read_file(path, &len); + if (!content) + return fail(r, "NOT_FOUND", "could not read file"); + } else { + return fail(r, "INVALID_ARGS", "content_base64 or path is required"); + } + if (len > 64u * 1024u * 1024u) { + free(content); + return fail(r, "TOO_LARGE", "bank statement is too large"); + } + if (memchr(content, '\0', len) != NULL) { + free(content); + return fail(r, "INVALID_ARGS", "not a SEB CSV export: header mismatch"); + } + char *csv = xmalloc(len + 1); + memcpy(csv, content, len); + csv[len] = '\0'; + free(content); + char *start = csv; + if (len >= 3 && memcmp(start, "\xEF\xBB\xBF", 3) == 0) + start += 3; + char perr[128]; + struct seb_row *rows = NULL; + size_t nrows = 0; + if (seb_parse(start, &rows, &nrows, perr, sizeof perr) != 0) { + free(csv); + return fail(r, "INVALID_ARGS", perr); + } + + char *setting = account && *account + ? NULL + : db_setting(r->db, r->org_id, "bank_account"); + const char *acct = account && *account + ? account + : (setting ? setting : "1930"); + int found = bank_account_exists(r, acct); + if (found < 0) { + free(setting); + free(rows); + free(csv); + return fail(r, "INTERNAL", "database error"); + } + if (!found) { + yyjson_mut_val *res = + failf(r, "ACCOUNT_NOT_FOUND", "account %s not found", acct); + free(setting); + free(rows); + free(csv); + return res; + } + + unsigned char(*hashes)[32] = xmalloc((nrows ? nrows : 1) * 32); + for (size_t i = 0; i < nrows; i++) + bank_row_hash(acct, &rows[i], hashes[i]); + + int64_t imported = 0; + if (r->dry_run) { + unsigned char(*sorted)[32] = xmalloc((nrows ? nrows : 1) * 32); + memcpy(sorted, hashes, nrows * 32); + qsort(sorted, nrows, 32, bank_hash_cmp); + for (size_t i = 0; i < nrows;) { + size_t j = i + 1; + while (j < nrows && memcmp(sorted[i], sorted[j], 32) == 0) + j++; + sqlite3_stmt *q = NULL; + int exists = 0; + if (sqlite3_prepare_v2( + r->db, + "SELECT 1 FROM bank_transactions WHERE org_id=?1" + " AND source_hash=?2", + -1, &q, NULL) != SQLITE_OK) { + free(sorted); + free(hashes); + free(setting); + free(rows); + free(csv); + return fail(r, "INTERNAL", "database error"); + } + sqlite3_bind_int64(q, 1, r->org_id); + sqlite3_bind_blob(q, 2, sorted[i], 32, SQLITE_TRANSIENT); + exists = sqlite3_step(q) == SQLITE_ROW; + sqlite3_finalize(q); + if (!exists) + imported++; + i = j; + } + free(sorted); + } else { + char ts[32]; + util_iso8601(util_now(), ts, sizeof ts); + int failed = db_exec(r->db, "BEGIN IMMEDIATE", NULL) != 0; + for (size_t i = 0; i < nrows && !failed; i++) { + sqlite3_stmt *ins = NULL; + if (sqlite3_prepare_v2( + r->db, + "INSERT INTO bank_transactions(org_id,account,booked_at," + "value_date,text,type,amount_ore,balance_ore,source," + "source_hash,imported_at,imported_by)" + " VALUES(?1,?2,?3,?4,?5,?6,?7,?8,'seb-csv',?9,?10,?11)" + " ON CONFLICT(org_id,source_hash) DO NOTHING", + -1, &ins, NULL) != SQLITE_OK) { + failed = 1; + break; + } + sqlite3_bind_int64(ins, 1, r->org_id); + sqlite3_bind_text(ins, 2, acct, -1, SQLITE_TRANSIENT); + sqlite3_bind_text(ins, 3, rows[i].booked_at, -1, SQLITE_TRANSIENT); + sqlite3_bind_text(ins, 4, rows[i].value_date, -1, SQLITE_TRANSIENT); + sqlite3_bind_text(ins, 5, rows[i].text, -1, SQLITE_TRANSIENT); + sqlite3_bind_text(ins, 6, rows[i].type, -1, SQLITE_TRANSIENT); + sqlite3_bind_int64(ins, 7, rows[i].amount_ore); + if (rows[i].has_balance) + sqlite3_bind_int64(ins, 8, rows[i].balance_ore); + else + sqlite3_bind_null(ins, 8); + sqlite3_bind_blob(ins, 9, hashes[i], 32, SQLITE_TRANSIENT); + sqlite3_bind_text(ins, 10, ts, -1, SQLITE_TRANSIENT); + sqlite3_bind_int64(ins, 11, r->sess->user_id); + int rc = sqlite3_step(ins); + sqlite3_finalize(ins); + if (rc != SQLITE_DONE) { + failed = 1; + break; + } + if (sqlite3_changes(r->db) > 0) + imported++; + } + if (failed) { + db_exec(r->db, "ROLLBACK", NULL); + free(hashes); + free(setting); + free(rows); + free(csv); + return fail(r, "INTERNAL", "database error"); + } + if (db_exec(r->db, "COMMIT", NULL) != 0) { + db_exec(r->db, "ROLLBACK", NULL); + free(hashes); + free(setting); + free(rows); + free(csv); + return fail(r, "INTERNAL", "database error"); + } + } + int64_t duplicates = (int64_t)nrows - imported; + const char *first = NULL, *last = NULL; + for (size_t i = 0; i < nrows; i++) { + if (!first || strcmp(rows[i].booked_at, first) < 0) + first = rows[i].booked_at; + if (!last || strcmp(rows[i].booked_at, last) > 0) + last = rows[i].booked_at; + } + if (!r->dry_run) { + char *reqjson = audit_args_json(r->args); + audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id, + "bank.import", reqjson, "OK", NULL); + free(reqjson); + } + yyjson_mut_val *o = yyjson_mut_obj(r->rdoc); + yyjson_mut_obj_add_strcpy(r->rdoc, o, "format", "seb"); + yyjson_mut_obj_add_strcpy(r->rdoc, o, "account", acct); + yyjson_mut_obj_add_int(r->rdoc, o, "total", (int64_t)nrows); + yyjson_mut_obj_add_int(r->rdoc, o, "imported", imported); + yyjson_mut_obj_add_int(r->rdoc, o, "duplicates", duplicates); + if (first) + yyjson_mut_obj_add_strcpy(r->rdoc, o, "first_date", first); + else + yyjson_mut_obj_add_null(r->rdoc, o, "first_date"); + if (last) + yyjson_mut_obj_add_strcpy(r->rdoc, o, "last_date", last); + else + yyjson_mut_obj_add_null(r->rdoc, o, "last_date"); + if (r->dry_run) + yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true); + free(hashes); + free(setting); + free(rows); + free(csv); + return o; +} + +static yyjson_mut_val *h_bank_list(struct req *r) +{ + const char *status = arg_str(r->args, "status"); + const char *from = arg_str(r->args, "from"); + const char *to = arg_str(r->args, "to"); + const char *account = arg_str(r->args, "account"); + int64_t limit = 200; + arg_int(r->args, "limit", &limit); + if (limit < 1) + limit = 200; + if (limit > 1000) + limit = 1000; + int status_code = 0; + if (status && strcmp(status, "unmatched") == 0) + status_code = 1; + else if (status && strcmp(status, "matched") == 0) + status_code = 2; + + sqlite3_stmt *st = NULL; + if (sqlite3_prepare_v2( + r->db, + "SELECT t.id,t.account,t.booked_at,t.value_date,t.text,t.type," + "t.amount_ore,t.balance_ore FROM bank_transactions t" + " WHERE t.org_id=?1" + " AND (?2 IS NULL OR t.account=?2)" + " AND (?3 IS NULL OR t.booked_at>=?3)" + " AND (?4 IS NULL OR t.booked_at<=?4)" + " AND (?5=0" + " OR (?5=1 AND NOT EXISTS (SELECT 1 FROM bank_matches m" + " WHERE m.org_id=t.org_id AND m.transaction_id=t.id))" + " OR (?5=2 AND EXISTS (SELECT 1 FROM bank_matches m" + " WHERE m.org_id=t.org_id AND m.transaction_id=t.id)))" + " ORDER BY t.booked_at,t.id LIMIT ?6", + -1, &st, NULL) != SQLITE_OK) + return fail(r, "INTERNAL", "database error"); + sqlite3_bind_int64(st, 1, r->org_id); + if (account) + sqlite3_bind_text(st, 2, account, -1, SQLITE_TRANSIENT); + else + sqlite3_bind_null(st, 2); + if (from) + sqlite3_bind_text(st, 3, from, -1, SQLITE_TRANSIENT); + else + sqlite3_bind_null(st, 3); + if (to) + sqlite3_bind_text(st, 4, to, -1, SQLITE_TRANSIENT); + else + sqlite3_bind_null(st, 4); + sqlite3_bind_int64(st, 5, status_code); + sqlite3_bind_int64(st, 6, limit); + + yyjson_mut_val *items = yyjson_mut_arr(r->rdoc); + while (sqlite3_step(st) == SQLITE_ROW) { + int64_t tx_id = sqlite3_column_int64(st, 0); + char tx_account[16]; + snprintf(tx_account, sizeof tx_account, "%s", + sq(sqlite3_column_text(st, 1))); + const char *booked_at = sq(sqlite3_column_text(st, 2)); + yyjson_mut_val *item = yyjson_mut_arr_add_obj(r->rdoc, items); + yyjson_mut_obj_add_int(r->rdoc, item, "id", tx_id); + yyjson_mut_obj_add_strcpy(r->rdoc, item, "account", tx_account); + yyjson_mut_obj_add_strcpy(r->rdoc, item, "booked_at", booked_at); + yyjson_mut_obj_add_strcpy(r->rdoc, item, "value_date", + sq(sqlite3_column_text(st, 3))); + yyjson_mut_obj_add_strcpy(r->rdoc, item, "text", + sq(sqlite3_column_text(st, 4))); + yyjson_mut_obj_add_strcpy(r->rdoc, item, "type", + sq(sqlite3_column_text(st, 5))); + yyjson_mut_obj_add_int(r->rdoc, item, "amount_ore", + sqlite3_column_int64(st, 6)); + if (sqlite3_column_type(st, 7) == SQLITE_NULL) + yyjson_mut_obj_add_null(r->rdoc, item, "balance_ore"); + else + yyjson_mut_obj_add_int(r->rdoc, item, "balance_ore", + sqlite3_column_int64(st, 7)); + + yyjson_mut_val *matches = yyjson_mut_arr(r->rdoc); + sqlite3_stmt *ms = NULL; + if (sqlite3_prepare_v2( + r->db, + "SELECT v.id,v.series,v.number,v.date," + "COALESCE(SUM(r.debit_ore-r.credit_ore),0)" + " FROM bank_matches m" + " JOIN vouchers v ON v.org_id=m.org_id AND v.id=m.voucher_id" + " LEFT JOIN accounts a ON a.org_id=v.org_id AND a.number=?2" + " LEFT JOIN voucher_rows r ON r.org_id=v.org_id" + " AND r.voucher_id=v.id AND r.account_id=a.id" + " WHERE m.org_id=?1 AND m.transaction_id=?3" + " GROUP BY v.id,v.series,v.number,v.date ORDER BY v.id", + -1, &ms, NULL) != SQLITE_OK) { + sqlite3_finalize(st); + return fail(r, "INTERNAL", "database error"); + } + sqlite3_bind_int64(ms, 1, r->org_id); + sqlite3_bind_text(ms, 2, tx_account, -1, SQLITE_TRANSIENT); + sqlite3_bind_int64(ms, 3, tx_id); + while (sqlite3_step(ms) == SQLITE_ROW) { + yyjson_mut_val *m = yyjson_mut_arr_add_obj(r->rdoc, matches); + yyjson_mut_obj_add_int(r->rdoc, m, "voucher_id", + sqlite3_column_int64(ms, 0)); + yyjson_mut_obj_add_strcpy(r->rdoc, m, "series", + sq(sqlite3_column_text(ms, 1))); + yyjson_mut_obj_add_int(r->rdoc, m, "number", + sqlite3_column_int64(ms, 2)); + yyjson_mut_obj_add_strcpy(r->rdoc, m, "date", + sq(sqlite3_column_text(ms, 3))); + yyjson_mut_obj_add_int(r->rdoc, m, "bank_amount_ore", + sqlite3_column_int64(ms, 4)); + } + sqlite3_finalize(ms); + yyjson_mut_obj_add_val(r->rdoc, item, "matches", matches); + + yyjson_mut_val *suggestions = yyjson_mut_arr(r->rdoc); + if (yyjson_mut_arr_size(matches) == 0) { + sqlite3_stmt *ss = NULL; + if (sqlite3_prepare_v2( + r->db, + "SELECT v.id,v.series,v.number,v.date," + "SUM(r.debit_ore-r.credit_ore)" + " FROM vouchers v" + " JOIN voucher_rows r ON r.org_id=v.org_id" + " AND r.voucher_id=v.id" + " JOIN accounts a ON a.org_id=r.org_id" + " AND a.id=r.account_id" + " WHERE v.org_id=?1 AND a.number=?2" + " AND ABS(julianday(v.date)-julianday(?3))<=5" + " AND NOT EXISTS (SELECT 1 FROM bank_matches m" + " WHERE m.org_id=v.org_id AND m.voucher_id=v.id)" + " GROUP BY v.id,v.series,v.number,v.date" + " HAVING SUM(r.debit_ore-r.credit_ore)=?4" + " ORDER BY ABS(julianday(v.date)-julianday(?3)),v.date," + "v.id LIMIT 3", + -1, &ss, NULL) != SQLITE_OK) { + sqlite3_finalize(st); + return fail(r, "INTERNAL", "database error"); + } + sqlite3_bind_int64(ss, 1, r->org_id); + sqlite3_bind_text(ss, 2, tx_account, -1, SQLITE_TRANSIENT); + sqlite3_bind_text(ss, 3, booked_at, -1, SQLITE_TRANSIENT); + sqlite3_bind_int64(ss, 4, sqlite3_column_int64(st, 6)); + while (sqlite3_step(ss) == SQLITE_ROW) { + yyjson_mut_val *s = + yyjson_mut_arr_add_obj(r->rdoc, suggestions); + yyjson_mut_obj_add_int(r->rdoc, s, "voucher_id", + sqlite3_column_int64(ss, 0)); + yyjson_mut_obj_add_strcpy(r->rdoc, s, "series", + sq(sqlite3_column_text(ss, 1))); + yyjson_mut_obj_add_int(r->rdoc, s, "number", + sqlite3_column_int64(ss, 2)); + yyjson_mut_obj_add_strcpy(r->rdoc, s, "date", + sq(sqlite3_column_text(ss, 3))); + yyjson_mut_obj_add_int(r->rdoc, s, "amount_ore", + sqlite3_column_int64(ss, 4)); + } + sqlite3_finalize(ss); + } + yyjson_mut_obj_add_val(r->rdoc, item, "suggestions", suggestions); + } + sqlite3_finalize(st); + + sqlite3_stmt *sum = NULL; + int64_t unmatched = 0, matched = 0, unmatched_amount = 0; + if (sqlite3_prepare_v2( + r->db, + "SELECT COUNT(*)," + "COALESCE(SUM(EXISTS (SELECT 1 FROM bank_matches m" + " WHERE m.org_id=t.org_id AND m.transaction_id=t.id)),0)," + "COALESCE(SUM(CASE WHEN EXISTS (SELECT 1 FROM bank_matches m" + " WHERE m.org_id=t.org_id AND m.transaction_id=t.id)" + " THEN 0 ELSE t.amount_ore END),0)" + " FROM bank_transactions t" + " WHERE t.org_id=?1 AND (?2 IS NULL OR t.account=?2)", + -1, &sum, NULL) == SQLITE_OK) { + sqlite3_bind_int64(sum, 1, r->org_id); + if (account) + sqlite3_bind_text(sum, 2, account, -1, SQLITE_TRANSIENT); + else + sqlite3_bind_null(sum, 2); + if (sqlite3_step(sum) == SQLITE_ROW) { + int64_t total = sqlite3_column_int64(sum, 0); + matched = sqlite3_column_int64(sum, 1); + unmatched = total - matched; + unmatched_amount = sqlite3_column_int64(sum, 2); + } + sqlite3_finalize(sum); + } + yyjson_mut_val *res = yyjson_mut_obj(r->rdoc); + yyjson_mut_obj_add_val(r->rdoc, res, "items", items); + yyjson_mut_val *s = yyjson_mut_obj(r->rdoc); + yyjson_mut_obj_add_int(r->rdoc, s, "unmatched", unmatched); + yyjson_mut_obj_add_int(r->rdoc, s, "matched", matched); + yyjson_mut_obj_add_int(r->rdoc, s, "unmatched_amount_ore", + unmatched_amount); + yyjson_mut_obj_add_val(r->rdoc, res, "summary", s); + return res; +} + +static int bank_legs_sum(sqlite3 *db, int64_t org_id, int64_t tx_id, + const char *account, int64_t *out) +{ + sqlite3_stmt *st = NULL; + if (sqlite3_prepare_v2( + db, + "SELECT COALESCE(SUM(r.debit_ore-r.credit_ore),0)" + " FROM bank_matches m" + " JOIN voucher_rows r ON r.org_id=m.org_id" + " AND r.voucher_id=m.voucher_id" + " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id" + " WHERE m.org_id=?1 AND m.transaction_id=?2 AND a.number=?3", + -1, &st, NULL) != SQLITE_OK) + return -1; + sqlite3_bind_int64(st, 1, org_id); + sqlite3_bind_int64(st, 2, tx_id); + sqlite3_bind_text(st, 3, account, -1, SQLITE_TRANSIENT); + int rc = sqlite3_step(st); + if (rc == SQLITE_ROW) + *out = sqlite3_column_int64(st, 0); + sqlite3_finalize(st); + return rc == SQLITE_ROW ? 0 : -1; +} + +static yyjson_mut_val *h_bank_match(struct req *r) +{ + int64_t tx_id = 0, voucher_id = 0; + if (!arg_int(r->args, "transaction_id", &tx_id) || tx_id <= 0 || + !arg_int(r->args, "voucher_id", &voucher_id) || voucher_id <= 0) + return fail(r, "INVALID_ARGS", + "transaction_id and voucher_id are required"); + sqlite3_stmt *st = NULL; + char account[16]; + int64_t amount = 0; + if (sqlite3_prepare_v2( + r->db, + "SELECT account,amount_ore FROM bank_transactions" + " WHERE org_id=?1 AND id=?2", + -1, &st, NULL) != SQLITE_OK) + return fail(r, "INTERNAL", "database error"); + sqlite3_bind_int64(st, 1, r->org_id); + sqlite3_bind_int64(st, 2, tx_id); + if (sqlite3_step(st) != SQLITE_ROW) { + sqlite3_finalize(st); + return fail(r, "NOT_FOUND", "bank transaction not found"); + } + snprintf(account, sizeof account, "%s", sq(sqlite3_column_text(st, 0))); + amount = sqlite3_column_int64(st, 1); + sqlite3_finalize(st); + + int found = 0; + if (sqlite3_prepare_v2( + r->db, "SELECT 1 FROM vouchers WHERE org_id=?1 AND id=?2", -1, + &st, NULL) != SQLITE_OK) + return fail(r, "INTERNAL", "database error"); + sqlite3_bind_int64(st, 1, r->org_id); + sqlite3_bind_int64(st, 2, voucher_id); + found = sqlite3_step(st) == SQLITE_ROW; + sqlite3_finalize(st); + if (!found) + return fail(r, "NOT_FOUND", "voucher not found"); + + int touches = 0; + if (sqlite3_prepare_v2( + r->db, + "SELECT 1 FROM voucher_rows r" + " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id" + " WHERE r.org_id=?1 AND r.voucher_id=?2 AND a.number=?3", + -1, &st, NULL) != SQLITE_OK) + return fail(r, "INTERNAL", "database error"); + sqlite3_bind_int64(st, 1, r->org_id); + sqlite3_bind_int64(st, 2, voucher_id); + sqlite3_bind_text(st, 3, account, -1, SQLITE_TRANSIENT); + touches = sqlite3_step(st) == SQLITE_ROW; + sqlite3_finalize(st); + if (!touches) + return failf(r, "INVALID_ARGS", + "voucher does not post to account %s", account); + + int linked = 0; + if (sqlite3_prepare_v2( + r->db, + "SELECT 1 FROM bank_matches WHERE org_id=?1 AND transaction_id=?2" + " AND voucher_id=?3", + -1, &st, NULL) != SQLITE_OK) + return fail(r, "INTERNAL", "database error"); + sqlite3_bind_int64(st, 1, r->org_id); + sqlite3_bind_int64(st, 2, tx_id); + sqlite3_bind_int64(st, 3, voucher_id); + linked = sqlite3_step(st) == SQLITE_ROW; + sqlite3_finalize(st); + if (linked) + return fail(r, "CONFLICT", + "transaction is already matched to this voucher"); + + int64_t legs = 0; + if (bank_legs_sum(r->db, r->org_id, tx_id, account, &legs) != 0) + return fail(r, "INTERNAL", "database error"); + if (r->dry_run) { + sqlite3_stmt *vs = NULL; + int64_t voucher_leg = 0; + if (sqlite3_prepare_v2( + r->db, + "SELECT COALESCE(SUM(r.debit_ore-r.credit_ore),0)" + " FROM voucher_rows r" + " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id" + " WHERE r.org_id=?1 AND r.voucher_id=?2 AND a.number=?3", + -1, &vs, NULL) != SQLITE_OK) + return fail(r, "INTERNAL", "database error"); + sqlite3_bind_int64(vs, 1, r->org_id); + sqlite3_bind_int64(vs, 2, voucher_id); + sqlite3_bind_text(vs, 3, account, -1, SQLITE_TRANSIENT); + if (sqlite3_step(vs) == SQLITE_ROW) + voucher_leg = sqlite3_column_int64(vs, 0); + sqlite3_finalize(vs); + yyjson_mut_val *o = yyjson_mut_obj(r->rdoc); + yyjson_mut_obj_add_int(r->rdoc, o, "transaction_id", tx_id); + yyjson_mut_obj_add_int(r->rdoc, o, "voucher_id", voucher_id); + yyjson_mut_obj_add_int(r->rdoc, o, "difference_ore", + amount - (legs + voucher_leg)); + yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true); + return o; + } + + char ts[32]; + util_iso8601(util_now(), ts, sizeof ts); + if (sqlite3_prepare_v2( + r->db, + "INSERT INTO bank_matches(org_id,transaction_id,voucher_id," + "matched_at,matched_by,kind) VALUES(?1,?2,?3,?4,?5,'manual')", + -1, &st, NULL) != SQLITE_OK) + return fail(r, "INTERNAL", "database error"); + sqlite3_bind_int64(st, 1, r->org_id); + sqlite3_bind_int64(st, 2, tx_id); + sqlite3_bind_int64(st, 3, voucher_id); + sqlite3_bind_text(st, 4, ts, -1, SQLITE_TRANSIENT); + sqlite3_bind_int64(st, 5, r->sess->user_id); + int rc = sqlite3_step(st); + sqlite3_finalize(st); + if (rc != SQLITE_DONE) + return fail(r, "INTERNAL", "database error"); + + char *reqjson = audit_args_json(r->args); + audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id, + "bank.match", reqjson, "OK", NULL); + free(reqjson); + + if (bank_legs_sum(r->db, r->org_id, tx_id, account, &legs) != 0) + return fail(r, "INTERNAL", "database error"); + yyjson_mut_val *o = yyjson_mut_obj(r->rdoc); + yyjson_mut_obj_add_int(r->rdoc, o, "transaction_id", tx_id); + yyjson_mut_obj_add_int(r->rdoc, o, "voucher_id", voucher_id); + yyjson_mut_obj_add_int(r->rdoc, o, "difference_ore", amount - legs); + return o; +} + +static yyjson_mut_val *h_bank_unmatch(struct req *r) +{ + int64_t tx_id = 0, voucher_id = 0; + if (!arg_int(r->args, "transaction_id", &tx_id) || tx_id <= 0 || + !arg_int(r->args, "voucher_id", &voucher_id) || voucher_id <= 0) + return fail(r, "INVALID_ARGS", + "transaction_id and voucher_id are required"); + sqlite3_stmt *st = NULL; + if (sqlite3_prepare_v2( + r->db, + "SELECT 1 FROM bank_matches WHERE org_id=?1 AND transaction_id=?2" + " AND voucher_id=?3", + -1, &st, NULL) != SQLITE_OK) + return fail(r, "INTERNAL", "database error"); + sqlite3_bind_int64(st, 1, r->org_id); + sqlite3_bind_int64(st, 2, tx_id); + sqlite3_bind_int64(st, 3, voucher_id); + int found = sqlite3_step(st) == SQLITE_ROW; + sqlite3_finalize(st); + if (!found) + return fail(r, "NOT_FOUND", "match not found"); + if (!r->dry_run) { + if (sqlite3_prepare_v2( + r->db, + "DELETE FROM bank_matches WHERE org_id=?1" + " AND transaction_id=?2 AND voucher_id=?3", + -1, &st, NULL) != SQLITE_OK) + return fail(r, "INTERNAL", "database error"); + sqlite3_bind_int64(st, 1, r->org_id); + sqlite3_bind_int64(st, 2, tx_id); + sqlite3_bind_int64(st, 3, voucher_id); + int rc = sqlite3_step(st); + sqlite3_finalize(st); + if (rc != SQLITE_DONE) + return fail(r, "INTERNAL", "database error"); + if (sqlite3_changes(r->db) == 0) + return fail(r, "NOT_FOUND", "match not found"); + char *reqjson = audit_args_json(r->args); + audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id, + "bank.unmatch", reqjson, "OK", NULL); + free(reqjson); + } + yyjson_mut_val *o = yyjson_mut_obj(r->rdoc); + yyjson_mut_obj_add_int(r->rdoc, o, "transaction_id", tx_id); + yyjson_mut_obj_add_int(r->rdoc, o, "voucher_id", voucher_id); + yyjson_mut_obj_add_bool(r->rdoc, o, "unmatched", true); + if (r->dry_run) + yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true); + return o; +} + /* command table */ /* ------------------------------------------------------------------ */ @@ -5206,10 +6056,35 @@ static const struct cmd_arg args_bokslut_post[] = { }; static const struct cmd_arg args_settings_set[] = { - { "key", ARG_STR, 1, NULL, NULL, "default_series or attachment_dir" }, + { "key", ARG_STR, 1, NULL, NULL, + "default_series, attachment_dir or bank_account" }, { "value", ARG_STR, 1, NULL, NULL, "Setting value" }, }; +static const struct cmd_arg args_bank_import[] = { + { "format", ARG_ENUM, 1, NULL, "seb", "Statement format" }, + { "content_base64", ARG_STR, 0, NULL, NULL, + "Statement content, base64; alternative to path" }, + { "path", ARG_STR, 0, NULL, NULL, + "Server-side path; alternative to content_base64" }, + { "account", ARG_STR, 0, NULL, NULL, + "Bank account number; defaults to settings.bank_account" }, +}; + +static const struct cmd_arg args_bank_list[] = { + { "status", ARG_ENUM, 0, "all", "all,unmatched,matched", + "Match status filter" }, + { "from", ARG_DATE, 0, NULL, NULL, "Earliest booked date" }, + { "to", ARG_DATE, 0, NULL, NULL, "Latest booked date" }, + { "account", ARG_STR, 0, NULL, NULL, "Bank account number filter" }, + { "limit", ARG_INT, 0, "200", NULL, "Page size, 1-1000" }, +}; + +static const struct cmd_arg args_bank_match[] = { + { "transaction_id", ARG_INT, 1, NULL, NULL, "Bank transaction id" }, + { "voucher_id", ARG_INT, 1, NULL, NULL, "Voucher id" }, +}; + static const struct cmd_arg args_report_rule_list[] = { { "report", ARG_ENUM, 0, NULL, "vat", "Report filter; omit for all reports" }, @@ -5476,6 +6351,14 @@ const struct command g_commands[] = { h_settings_get, NULL, 0 }, { "settings.set", "Change an org setting", PERM_WRITE, 1, 1, 1, h_settings_set, CMD_ARGS(args_settings_set) }, + { "bank.import", "Import a bank statement (SEB CSV)", PERM_WRITE, 1, 1, 1, + h_bank_import, CMD_ARGS(args_bank_import) }, + { "bank.list", "List bank transactions, matches and suggestions", + PERM_READ, 1, 0, 0, h_bank_list, CMD_ARGS(args_bank_list) }, + { "bank.match", "Match a bank transaction to a voucher", PERM_WRITE, 1, 1, + 1, h_bank_match, CMD_ARGS(args_bank_match) }, + { "bank.unmatch", "Remove a transaction/voucher match", PERM_WRITE, 1, 1, + 1, h_bank_unmatch, CMD_ARGS(args_bank_match) }, { "report.rule_list", "List per-org reporting rules", PERM_READ, 1, 0, 0, h_report_rule_list, CMD_ARGS(args_report_rule_list) }, { "report.rule_create", "Create a reporting rule (owner)", PERM_OWNER, 1, 1, @@ -271,6 +271,39 @@ static const char SCHEMA_V1[] = " key TEXT NOT NULL," " value TEXT NOT NULL," " PRIMARY KEY (org_id, key)" + ") STRICT;\n" + + "CREATE TABLE bank_transactions (" + " org_id INTEGER NOT NULL REFERENCES orgs(id)," + " id INTEGER PRIMARY KEY," + " account TEXT NOT NULL," + " booked_at TEXT NOT NULL," + " value_date TEXT NOT NULL," + " text TEXT NOT NULL," + " type TEXT NOT NULL," + " amount_ore INTEGER NOT NULL," + " balance_ore INTEGER," + " source TEXT NOT NULL," + " source_hash BLOB NOT NULL CHECK (length(source_hash) = 32)," + " imported_at TEXT NOT NULL," + " imported_by INTEGER NOT NULL REFERENCES users(id)," + " UNIQUE (org_id, id)," + " UNIQUE (org_id, source_hash)" + ") STRICT;\n" + "CREATE INDEX idx_bank_tx_date ON bank_transactions(org_id, booked_at);\n" + + "CREATE TABLE bank_matches (" + " org_id INTEGER NOT NULL," + " transaction_id INTEGER NOT NULL," + " voucher_id INTEGER NOT NULL," + " matched_at TEXT NOT NULL," + " matched_by INTEGER NOT NULL REFERENCES users(id)," + " kind TEXT NOT NULL DEFAULT 'manual'" + " CHECK (kind IN ('manual','auto'))," + " PRIMARY KEY (org_id, transaction_id, voucher_id)," + " FOREIGN KEY (org_id, transaction_id)" + " REFERENCES bank_transactions(org_id, id)," + " FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id)" ") STRICT;\n"; static const char SCHEMA_V2[] = @@ -458,6 +491,50 @@ static int db_upgrade_v7(sqlite3 *db, char **err) err); } +/* v8: bank reconciliation — imported statement evidence and the mutable + transaction/voucher links (phase 1 never books anything). */ +static int db_upgrade_v8(sqlite3 *db, char **err) +{ + if (db_exec(db, + "CREATE TABLE IF NOT EXISTS bank_transactions (" + " org_id INTEGER NOT NULL REFERENCES orgs(id)," + " id INTEGER PRIMARY KEY," + " account TEXT NOT NULL," + " booked_at TEXT NOT NULL," + " value_date TEXT NOT NULL," + " text TEXT NOT NULL," + " type TEXT NOT NULL," + " amount_ore INTEGER NOT NULL," + " balance_ore INTEGER," + " source TEXT NOT NULL," + " source_hash BLOB NOT NULL CHECK (length(source_hash) = 32)," + " imported_at TEXT NOT NULL," + " imported_by INTEGER NOT NULL REFERENCES users(id)," + " UNIQUE (org_id, id)," + " UNIQUE (org_id, source_hash)" + ") STRICT;" + "CREATE INDEX IF NOT EXISTS idx_bank_tx_date" + " ON bank_transactions(org_id, booked_at);", + err) != 0) + return -1; + return db_exec(db, + "CREATE TABLE IF NOT EXISTS bank_matches (" + " org_id INTEGER NOT NULL," + " transaction_id INTEGER NOT NULL," + " voucher_id INTEGER NOT NULL," + " matched_at TEXT NOT NULL," + " matched_by INTEGER NOT NULL REFERENCES users(id)," + " kind TEXT NOT NULL DEFAULT 'manual'" + " CHECK (kind IN ('manual','auto'))," + " PRIMARY KEY (org_id, transaction_id, voucher_id)," + " FOREIGN KEY (org_id, transaction_id)" + " REFERENCES bank_transactions(org_id, id)," + " FOREIGN KEY (org_id, voucher_id)" + " REFERENCES vouchers(org_id, id)" + ") STRICT", + err); +} + static int db_upgrade(sqlite3 *db, int from, char **err) { if (db_exec(db, "BEGIN IMMEDIATE", err) != 0) @@ -486,6 +563,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err) db_exec(db, "ROLLBACK", NULL); return -1; } + if (from < 8 && db_upgrade_v8(db, err) != 0) { + db_exec(db, "ROLLBACK", NULL); + return -1; + } char *sql = sqlite3_mprintf( "UPDATE meta SET value='%d' WHERE key='schema_version'", BOKF_SCHEMA_VERSION); @@ -4,7 +4,7 @@ #include <sqlite3.h> #include <stdint.h> -#define BOKF_SCHEMA_VERSION 7 +#define BOKF_SCHEMA_VERSION 8 int db_open(const char *path, sqlite3 **out, char **err); int db_migrate(sqlite3 *db, char **err); diff --git a/tests/test_core.c b/tests/test_core.c index 70a1ab7..a78a940 100644 --- a/tests/test_core.c +++ b/tests/test_core.c @@ -192,6 +192,23 @@ static yyjson_doc *call_sie_import(const char *session, int64_t org, return d; } +static yyjson_doc *call_bank_import(const char *session, int64_t org, + const char *csv, int dry) +{ + char *b64 = util_b64((const unsigned char *)csv, strlen(csv)); + size_t len = strlen(b64) + 1024; + char *req = xmalloc(len); + snprintf(req, len, + "{\"v\":1,\"id\":\"bank\",\"cmd\":\"bank.import\",\"session\":" + "\"%s\",\"org\":%lld%s,\"args\":{\"format\":\"seb\"," + "\"content_base64\":\"%s\"}}", + session, (long long)org, dry ? ",\"dry_run\":true" : "", b64); + yyjson_doc *d = call(req); + free(req); + free(b64); + return d; +} + static int login(const char *user, const char *pass) { yyjson_doc *d = call(reqf( @@ -521,6 +538,14 @@ static void test_pre_migration_snapshot(const char *tmpdir, " AND name='attachments_no_delete'"); CHECK(v && strcmp(v, "attachments_no_delete") == 0); free(v); + v = db_text(db, "SELECT name FROM sqlite_master WHERE type='table'" + " AND name='bank_transactions'"); + CHECK(v && strcmp(v, "bank_transactions") == 0); + free(v); + v = db_text(db, "SELECT name FROM sqlite_master WHERE type='table'" + " AND name='bank_matches'"); + CHECK(v && strcmp(v, "bank_matches") == 0); + free(v); int64_t user_id = 0; CHECK(db_create_user(db, "migrated", "Migrated", "secret123", 0, &user_id, &err) == 0); @@ -1878,6 +1903,7 @@ int main(void) g_session, (int)org_id)); CHECK_OK(d); CHECK_STR(d, "result.default_series", "A"); + CHECK_STR(d, "result.bank_account", "1930"); yyjson_doc_free(d); d = call(reqf("{\"v\":1,\"id\":\"96\",\"cmd\":\"settings.set\"," @@ -1945,6 +1971,234 @@ int main(void) CHECK_STR(d, "result.attachment_dir", "/tmp/bilagor"); yyjson_doc_free(d); + /* ---------------- bank reconciliation (schema v8) ----------------- */ + + static const char seb_csv[] = + "Bokförd;Valutadatum;Text;Typ;Insättningar;Uttag;Bokfört saldo\n" + "2026-05-10;2026-05-10;\"Faktura 123; kund \"\"AB\"\"\";Insättning;" + "1250,00;;10000,00\n" + "2026-05-11;2026-05-12;Kortköp;Kort;;-2 409,00;7.591,00\n" + "2026-05-20;2026-05-21;Lön;Insättning;799376,10;;806967,10\n"; + static const char seb_one[] = + "Bokförd;Valutadatum;Text;Typ;Insättningar;Uttag;Bokfört saldo\n" + "2026-06-01;2026-06-01;Test;Insättning;10,00;;10,00\n"; + static const char seb_bad_header[] = + "Bokförd;Datum;Text;Typ;Insättningar;Uttag;Bokfört saldo\n" + "2026-06-01;2026-06-01;Test;Insättning;10,00;;10,00\n"; + static const char seb_bad_amount[] = + "Bokförd;Valutadatum;Text;Typ;Insättningar;Uttag;Bokfört saldo\n" + "2026-06-01;2026-06-01;Fel;Insättning;12,abc;;10,00\n"; + + d = call_bank_import(g_session, org_id, seb_csv, 1); + CHECK_OK(d); + CHECK(jbool(d, "result.dry_run")); + CHECK_STR(d, "result.format", "seb"); + CHECK_STR(d, "result.account", "1930"); + CHECK(jint(d, "result.total") == 3); + CHECK(jint(d, "result.imported") == 3); + CHECK(jint(d, "result.duplicates") == 0); + CHECK_STR(d, "result.first_date", "2026-05-10"); + CHECK_STR(d, "result.last_date", "2026-05-20"); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"200b\",\"cmd\":\"bank.list\"," + "\"session\":\"%s\",\"org\":%d}", + g_session, (int)org_id)); + CHECK_OK(d); + CHECK(yyjson_arr_size(jget(d, "result.items")) == 0); + CHECK(jint(d, "result.summary.unmatched") == 0); + CHECK(jint(d, "result.summary.matched") == 0); + yyjson_doc_free(d); + + d = call_bank_import(g_session, org_id, seb_csv, 0); + CHECK_OK(d); + CHECK(jint(d, "result.total") == 3); + CHECK(jint(d, "result.imported") == 3); + CHECK(jint(d, "result.duplicates") == 0); + yyjson_doc_free(d); + + d = call_bank_import(g_session, org_id, seb_csv, 0); + CHECK_OK(d); + CHECK(jint(d, "result.imported") == 0); + CHECK(jint(d, "result.duplicates") == 3); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"200d\",\"cmd\":\"bank.list\"," + "\"session\":\"%s\",\"org\":%d}", + g_session, (int)org_id)); + CHECK_OK(d); + CHECK(yyjson_arr_size(jget(d, "result.items")) == 3); + CHECK(jint(d, "result.items.0.amount_ore") == 125000); + CHECK_STR(d, "result.items.0.text", "Faktura 123; kund \"AB\""); + CHECK(jint(d, "result.items.0.balance_ore") == 1000000); + CHECK(jint(d, "result.items.1.amount_ore") == -240900); + CHECK(jint(d, "result.items.1.balance_ore") == 759100); + CHECK(jint(d, "result.items.2.amount_ore") == 79937610); + CHECK(jint(d, "result.summary.unmatched") == 3); + CHECK(jint(d, "result.summary.matched") == 0); + CHECK(jint(d, "result.summary.unmatched_amount_ore") == 79821710); + CHECK(yyjson_arr_size(jget(d, "result.items.0.suggestions")) == 0); + int64_t tx1 = jint(d, "result.items.0.id"); + CHECK(tx1 > 0); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"200e\",\"cmd\":\"bank.list\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"from\":\"2026-05-11\",\"to\":\"2026-05-19\"}}", + g_session, (int)org_id)); + CHECK_OK(d); + CHECK(yyjson_arr_size(jget(d, "result.items")) == 1); + CHECK_STR(d, "result.items.0.booked_at", "2026-05-11"); + CHECK(jint(d, "result.summary.unmatched") == 3); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"201\",\"cmd\":\"voucher.post\"," + "\"session\":\"%s\",\"org\":%d,\"args\":{\"date\":" + "\"2026-05-10\",\"description\":\"Kundbetalning\"," + "\"client_ref\":\"bankref1\",\"rows\":[" + "{\"account\":\"1930\",\"debit_ore\":125000}," + "{\"account\":\"3001\",\"credit_ore\":125000}]}}", + g_session, (int)org_id)); + CHECK_OK(d); + int64_t bank_voucher = jint(d, "result.id"); + CHECK(bank_voucher > 0); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"202\",\"cmd\":\"bank.list\"," + "\"session\":\"%s\",\"org\":%d}", + g_session, (int)org_id)); + CHECK_OK(d); + CHECK(yyjson_arr_size(jget(d, "result.items.0.suggestions")) == 1); + CHECK(jint(d, "result.items.0.suggestions.0.voucher_id") == bank_voucher); + CHECK(jint(d, "result.items.0.suggestions.0.amount_ore") == 125000); + CHECK_STR(d, "result.items.0.suggestions.0.date", "2026-05-10"); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"203\",\"cmd\":\"bank.match\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"transaction_id\":%lld,\"voucher_id\":%lld}}", + g_session, (int)org_id, (long long)tx1, + (long long)bank_voucher)); + CHECK_OK(d); + CHECK(jint(d, "result.transaction_id") == tx1); + CHECK(jint(d, "result.voucher_id") == bank_voucher); + CHECK(jint(d, "result.difference_ore") == 0); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"204\",\"cmd\":\"bank.match\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"transaction_id\":%lld,\"voucher_id\":%lld}}", + g_session, (int)org_id, (long long)tx1, + (long long)bank_voucher)); + CHECK_STR(d, "error.code", "CONFLICT"); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"205\",\"cmd\":\"bank.list\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"status\":\"matched\"}}", + g_session, (int)org_id)); + CHECK_OK(d); + CHECK(yyjson_arr_size(jget(d, "result.items")) == 1); + CHECK(jint(d, "result.items.0.id") == tx1); + CHECK(yyjson_arr_size(jget(d, "result.items.0.matches")) == 1); + CHECK(jint(d, "result.items.0.matches.0.voucher_id") == bank_voucher); + CHECK(jint(d, "result.items.0.matches.0.bank_amount_ore") == 125000); + CHECK_STR(d, "result.items.0.matches.0.series", "V-"); + CHECK_STR(d, "result.items.0.matches.0.date", "2026-05-10"); + CHECK(yyjson_arr_size(jget(d, "result.items.0.suggestions")) == 0); + CHECK(jint(d, "result.summary.matched") == 1); + CHECK(jint(d, "result.summary.unmatched") == 2); + CHECK(jint(d, "result.summary.unmatched_amount_ore") == 79696710); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"206\",\"cmd\":\"bank.unmatch\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"transaction_id\":%lld,\"voucher_id\":%lld}}", + g_session, (int)org_id, (long long)tx1, + (long long)bank_voucher)); + CHECK_OK(d); + CHECK(jbool(d, "result.unmatched")); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"207\",\"cmd\":\"bank.unmatch\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"transaction_id\":%lld,\"voucher_id\":%lld}}", + g_session, (int)org_id, (long long)tx1, + (long long)bank_voucher)); + CHECK_STR(d, "error.code", "NOT_FOUND"); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"208\",\"cmd\":\"bank.match\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"transaction_id\":%lld,\"voucher_id\":%lld}}", + g_session, (int)org_id, (long long)tx1, + (long long)bank_voucher)); + CHECK_OK(d); + CHECK(jint(d, "result.difference_ore") == 0); + yyjson_doc_free(d); + + d = call_bank_import(g_session, org_id, seb_bad_header, 1); + CHECK_STR(d, "error.code", "INVALID_ARGS"); + CHECK(jstr(d, "error.message") && + strstr(jstr(d, "error.message"), "header mismatch") != NULL); + yyjson_doc_free(d); + + d = call_bank_import(g_session, org_id, seb_bad_amount, 1); + CHECK_STR(d, "error.code", "INVALID_ARGS"); + CHECK(jstr(d, "error.message") && + strstr(jstr(d, "error.message"), "line 2") != NULL); + yyjson_doc_free(d); + + { + char *bad_b64 = + util_b64((const unsigned char *)seb_one, strlen(seb_one)); + d = call(reqf("{\"v\":1,\"id\":\"209\",\"cmd\":\"bank.import\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"format\":\"seb\",\"content_base64\":\"%s\"," + "\"account\":\"9999\"}}", + g_session, (int)org_id, bad_b64)); + CHECK_STR(d, "error.code", "ACCOUNT_NOT_FOUND"); + yyjson_doc_free(d); + free(bad_b64); + } + + d = call(reqf("{\"v\":1,\"id\":\"210\",\"cmd\":\"settings.set\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"key\":\"bank_account\",\"value\":\"19a0\"}}", + g_session, (int)org_id)); + CHECK_STR(d, "error.code", "INVALID_ARGS"); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"211\",\"cmd\":\"settings.set\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"key\":\"bank_account\",\"value\":\"1940\"}}", + g_session, (int)org_id)); + CHECK_OK(d); + CHECK_STR(d, "result.value", "1940"); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"212\",\"cmd\":\"settings.get\"," + "\"session\":\"%s\",\"org\":%d}", + g_session, (int)org_id)); + CHECK_OK(d); + CHECK_STR(d, "result.bank_account", "1940"); + yyjson_doc_free(d); + + d = call_bank_import(g_session, org_id, seb_one, 0); + CHECK_OK(d); + CHECK_STR(d, "result.account", "1940"); + CHECK(jint(d, "result.imported") == 1); + yyjson_doc_free(d); + + d = call(reqf("{\"v\":1,\"id\":\"213\",\"cmd\":\"bank.list\"," + "\"session\":\"%s\",\"org\":%d,\"args\":" + "{\"account\":\"1940\"}}", + g_session, (int)org_id)); + CHECK_OK(d); + CHECK(yyjson_arr_size(jget(d, "result.items")) == 1); + CHECK_STR(d, "result.items.0.account", "1940"); + yyjson_doc_free(d); + /* ---------------- moms rules: ranges merge per box ------------- */ d = call(reqf("{\"v\":1,\"id\":\"110\",\"cmd\":\"org.create\"," "\"session\":\"%s\",\"args\":{\"name\":\"Moms AB\"}}", |