diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/commands.c | 891 | ||||
| -rw-r--r-- | src/db.c | 81 | ||||
| -rw-r--r-- | src/db.h | 2 |
3 files changed, 969 insertions, 5 deletions
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); |