aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--Makefile4
-rw-r--r--docs/PROTOCOL.md109
-rw-r--r--docs/SCHEMA.md135
-rw-r--r--src/cmd_employees.c665
-rw-r--r--src/cmd_payroll.c1254
-rw-r--r--src/commands.c4
-rw-r--r--src/db.c152
-rw-r--r--src/db.h2
-rw-r--r--src/tax_table.c745
-rw-r--r--src/tax_table.h52
-rw-r--r--tests/test_core.c618
11 files changed, 3724 insertions, 16 deletions
diff --git a/Makefile b/Makefile
index 8d9364e..738193a 100644
--- a/Makefile
+++ b/Makefile
@@ -25,8 +25,10 @@ CORE_SRC = src/util.c src/log.c src/config.c src/db.c src/auth.c \
src/cmd_customers.c src/cmd_invoices.c src/cmd_rules.c \
src/cmd_attachments.c src/cmd_reports.c src/cmd_sru.c \
src/cmd_sie.c src/cmd_bokslut.c src/cmd_bank.c \
+ src/cmd_employees.c src/cmd_payroll.c \
src/seed.c src/ledger.c src/reports.c src/sru.c src/sie.c \
- src/formula.c src/pdf.c src/invoice.c src/secret.c src/smtp.c
+ src/formula.c src/pdf.c src/invoice.c src/secret.c src/smtp.c \
+ src/tax_table.c
VENDOR_OBJ = $(patsubst %.c,$(BUILD)/%.o,$(VENDOR_SRC))
CORE_OBJ = $(patsubst %.c,$(BUILD)/%.o,$(CORE_SRC))
diff --git a/docs/PROTOCOL.md b/docs/PROTOCOL.md
index aafa1c9..9a2a3c4 100644
--- a/docs/PROTOCOL.md
+++ b/docs/PROTOCOL.md
@@ -111,6 +111,8 @@ Scopes on a token can narrow but never widen the user's role.
| `bank.import`, `bank.match`, `bank.unmatch` | | ● | ● | |
| `sie.import`, `account.create`, `account.update` | | ● | ● | |
| `period.lock`, `fiscal_year.open/close/reopen`, `org.update` | | | ● | |
+| `employee.create/update/archive`, `payroll.run_post`, `payroll.pay_tax`, `payroll.settings_set` | | ● | ● | |
+| `payroll.agi` (decrypted personnummer), `payroll.tax_tables_fetch/import` | | | ● | |
| `org.member_*`, `token.create` for others | | | ● | |
| `user.create`, any org | | | | ● |
| `backup.snapshot` | | | ● | ● |
@@ -155,8 +157,8 @@ enforced at the database level via composite keys (see `SCHEMA.md`).
`AUTH_REQUIRED`, `AUTH_FAILED`, `SESSION_EXPIRED`, `RATE_LIMITED`,
`ORG_REQUIRED`, `ORG_FORBIDDEN`, `FORBIDDEN`, `NOT_FOUND`, `CONFLICT`,
`UNBALANCED`, `ACCOUNT_NOT_FOUND`, `ACCOUNT_INACTIVE`, `FISCAL_YEAR_NOT_FOUND`,
-`FISCAL_YEAR_CLOSED`, `PERIOD_LOCKED`, `DATE_OUT_OF_RANGE`, `IMMUTABLE`,
-`SEQUENCE_GAP`, `TOO_LARGE`, `UNSUPPORTED`, `SMTP_NOT_CONFIGURED`,
+`FISCAL_YEAR_CLOSED`, `FETCH_FAILED`, `PERIOD_LOCKED`, `DATE_OUT_OF_RANGE`,
+`IMMUTABLE`, `SEQUENCE_GAP`, `TOO_LARGE`, `UNSUPPORTED`, `SMTP_NOT_CONFIGURED`,
`SMTP_FAILED`, `DB_BUSY`, `INTERNAL`.
Codes are stable; `message` is human-readable and may change. `details` is
@@ -644,6 +646,93 @@ updated and the `invoice.send` audit entry stores `{id,to,subject}` only.
`dry_run` validates configuration, recipient and stored document and returns
the recipient and subject without sending or updating anything.
+### 7.11 Anställda (employees)
+
+The employee register. `personal_no` is checked for shape (10 or 12 digits,
+with or without a hyphen at the usual position) but not for checksum. It is
+encrypted at rest with AES-256-GCM under `BOKFD_SECRET_KEY`; a write without
+a valid key is `INTERNAL`. Every response except `payroll.agi` masks it,
+keeping only the last four characters (`********-1234`); when the key is
+missing, reads return an all-masked `********` instead of failing.
+
+| Command | Args | Result |
+|---|---|---|
+| `employee.list` | `active_only?` | `items[]` ordered by name, personnummer masked |
+| `employee.get` | `id` | one employee, personnummer masked |
+| `employee.create` | `name`, `personal_no`, `address?`, `postal_code?`, `city?`, `bank_account?`, `salary_account?`, `monthly_salary_ore?`, `tax_table?`, `tax_column?` | the employee |
+| `employee.update` | `id` plus any field (merged) | the effective employee |
+| `employee.archive` | `id`, `active` | `id`, `active` |
+
+`salary_account` (digits only) defaults to the `payroll_salary_account`
+setting (default `7210`), `tax_table` is 29–42 and `tax_column` is 1–6.
+Creating a second employee with the same personnummer is a `CONFLICT`. All
+mutations support `dry_run` and are audited (`employee.create`,
+`employee.update`, `employee.archive`); the audit entry redacts the
+personnummer.
+
+### 7.12 Lön (payroll)
+
+| Command | Args | Result |
+|---|---|---|
+| `payroll.tax_tables_fetch` | `year?` | `year`, `rows`, `bytes`, `sha256`, `source_url` (owner; audited) |
+| `payroll.tax_tables_import` | `year`, `content_base64` | same parse from a supplied file (owner; audited) |
+| `payroll.tax_tables_status` | — | `stored_years[]`, `current_year`, `stale`, `fetched_at`, `source_url` |
+| `payroll.run_preview` | `period` (YYYY-MM) | per-employee gross/tax/avgifter/net and totals; nothing written |
+| `payroll.run_post` | `period`, `pay_date`, `dry_run?` | `id`, `voucher_id`, totals; posts the run voucher |
+| `payroll.run_list` | `limit?` | runs, newest period first |
+| `payroll.run_get` | `id` | run with `lines[]` |
+| `payroll.agi` | `period` | per-employee AGI underlag incl. the personnummer in clear (owner) |
+| `payroll.pay_tax` | `run_id`, `date?`, `dry_run?` | payment voucher; marks the run `paid` |
+| `payroll.settings_get` | — | effective payroll settings (defaults included) |
+| `payroll.settings_set` | `key`, `value` | digits-only write of one `payroll_*` key |
+
+`payroll.tax_tables_fetch` GETs Skatteverket's "Specialversioner för
+programföretag" page over HTTPS (system trust store), takes the year's
+`allmanna-tabeller-manad.txt` link in document order (current year first,
+so `year` defaults to the current calendar year), downloads and parses the
+fixed-width file, and replaces the year's rows plus `tax_table_meta`
+(`source_url`, SHA-256, `fetched_at`) in one transaction. The file's B
+records (income up to 80,000 kr/month) give the withholding in whole kronor
+per table, column and income range; the % records above 80,000 kr are stored
+(`pct` is percent × 100) but wave 1 does not implement them: a lookup above
+the top B range fails with `INVALID_ARGS` and the message "income above the
+tabulated range is not supported yet". SKV 433 (Teknisk beskrivning)
+states only that the deduction for income over 80,000 kr "anges i procent"
+and that it "får beräknas på samma sätt som beskrivits ovan"; no formula
+for applying the percentage is given, so the safe reading is not guessed.
+`payroll.tax_tables_import` parses a supplied file the same way
+(`source_url` is `import`) for air-gapped installs. A network or TLS failure
+is `FETCH_FAILED`. `stale` is true when no table is stored for the current
+calendar year.
+
+`payroll.run_preview` computes, per active employee with a salary, gross =
+`monthly_salary_ore`, the table tax for their `tax_table`/`tax_column`
+(period year; `run_post` uses the pay date's year), employer contributions
+`round(gross × payroll_avgift_rate_bp / 10000)` and net = gross − tax.
+Annual income below 1,000 kr is not taxed (SKV rule). `run_post` validates
+that `pay_date` lies in a fiscal year whose months include `period`, then in
+one transaction posts the `source:"payroll"` voucher — debit each employee's
+salary account, debit `payroll_avgift_account`, credit `payroll_tax_account`,
+credit `1930` for the total net and credit `payroll_avgift_liability` — and
+inserts `payroll_runs` plus one `payroll_run_lines` row per employee. One
+run per period: a second `run_post` for the same period is a `CONFLICT`.
+Nothing is written on `dry_run`.
+
+`payroll.agi` returns the posted run's per-employee gross, tax, contributions
+and net together with the decrypted `personal_no`; it is owner-only and the
+only command that reveals the number. `payroll.pay_tax` books
+`source:"payroll_tax"` (debit `payroll_tax_account` and
+`payroll_avgift_liability`, credit `payroll_tax_payment_account`) on the
+given date (default: today) and sets the run's `status` to `paid` with
+`payment_voucher_id`; paying twice is a `CONFLICT`.
+
+The settings `payroll_salary_account` (`7210`, the default for new
+employees), `payroll_tax_account` (`2710`), `payroll_avgift_account`
+(`7510`), `payroll_avgift_liability` (`2731`),
+`payroll_tax_payment_account` (`1630`) and `payroll_avgift_rate_bp` (`3142`)
+are digits-only and are read with `payroll.settings_get` /
+`payroll.settings_set`; stored keys also appear in `settings.get`.
+
<!-- generated:commands begin -->
## Command catalogue (generated)
@@ -741,6 +830,22 @@ Args: `name:type(values)[!][=default]`, `!` = required.
| `invoice.list` | viewer | yes | no | no | `customer_id:int`, `status:enum(issued\|credited)`, `limit:int=200` |
| `invoice.pdf` | viewer | yes | no | no | `id:int!` |
| `invoice.send` | bookkeeper | yes | yes | yes | `id:int!`, `to:string` |
+| `employee.list` | viewer | yes | no | no | `active_only:bool` |
+| `employee.get` | viewer | yes | no | no | `id:int!` |
+| `employee.create` | bookkeeper | yes | yes | yes | `name:string!`, `personal_no:string!`, `address:string`, `postal_code:string`, `city:string`, `bank_account:string`, `salary_account:string`, `monthly_salary_ore:int=0`, `tax_table:int=30`, `tax_column:int=1` |
+| `employee.update` | bookkeeper | yes | yes | yes | `id:int!`, `name:string`, `personal_no:string`, `address:string`, `postal_code:string`, `city:string`, `bank_account:string`, `salary_account:string`, `monthly_salary_ore:int`, `tax_table:int`, `tax_column:int`, `active:bool` |
+| `employee.archive` | bookkeeper | yes | yes | yes | `id:int!`, `active:bool!` |
+| `payroll.tax_tables_fetch` | owner | yes | yes | yes | `year:int` |
+| `payroll.tax_tables_import` | owner | yes | yes | yes | `year:int!`, `content_base64:string!` |
+| `payroll.tax_tables_status` | viewer | yes | no | no | — |
+| `payroll.run_preview` | bookkeeper | yes | no | no | `period:string!` |
+| `payroll.run_post` | bookkeeper | yes | yes | yes | `period:string!`, `pay_date:date!` |
+| `payroll.run_list` | viewer | yes | no | no | `limit:int=100` |
+| `payroll.run_get` | viewer | yes | no | no | `id:int!` |
+| `payroll.agi` | owner | yes | no | no | `period:string!` |
+| `payroll.pay_tax` | bookkeeper | yes | yes | yes | `run_id:int!`, `date:date` |
+| `payroll.settings_get` | viewer | yes | no | no | — |
+| `payroll.settings_set` | bookkeeper | yes | yes | yes | `key:string!`, `value:string!` |
<!-- generated:commands end -->
## 8. The TUI is just a client
diff --git a/docs/SCHEMA.md b/docs/SCHEMA.md
index 15e375a..f2e93ac 100644
--- a/docs/SCHEMA.md
+++ b/docs/SCHEMA.md
@@ -48,12 +48,19 @@ orgs ─┬─ memberships ── users ── api_tokens
├─ idempotency
├─ report_rules
├─ bank_transactions ── bank_matches
+ ├─ employees ── payroll_run_lines
+ ├─ payroll_runs ── payroll_run_lines
└─ settings
+
+tax_tables / tax_table_meta (national reference data, no org_id)
```
`org_id` is present on every tenant row. `audit_log` is a global chain with a
nullable `org_id`, because user and system events (logins, org creation) are
-not org-scoped.
+not org-scoped. `tax_tables` and `tax_table_meta` are the one deliberate
+exception: they hold Skatteverket's published tables for the whole country,
+identical for every org, so they carry no `org_id` and are shared read-only
+reference data. Every command that writes them is owner-only and audited.
## 4. Identity and tenancy
@@ -203,7 +210,7 @@ CREATE TABLE vouchers (
description TEXT NOT NULL CHECK (length(description) > 0),
source TEXT NOT NULL DEFAULT 'manual'
CHECK (source IN ('manual','agent','sie_import','system','ib',
- 'invoice')),
+ 'invoice','payroll','payroll_tax')),
client_ref TEXT,
corrects_voucher_id INTEGER,
created_at TEXT NOT NULL,
@@ -448,8 +455,11 @@ 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`,
-`bank.import`, `bank.match`, `bank.unmatch`, `settings.update`. Reads are
-logged only when `audit_reads = true`.
+`bank.import`, `bank.match`, `bank.unmatch`, `settings.update`,
+`customer.create`, `invoice.issue`, `invoice.send`, `employee.create`,
+`employee.update`, `employee.archive`, `payroll.tax_tables_fetch`,
+`payroll.tax_tables_import`, `payroll.run_post`, `payroll.pay_tax`,
+`payroll.settings_set`. Reads are logged only when `audit_reads = true`.
## 10. Reporting rules and settings
@@ -550,10 +560,11 @@ 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: **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).
+ and `created_at`. Current version: **10** (v10 adds the payroll tables and
+ the `payroll`/`payroll_tax` voucher sources, v9 adds the invoicing tables
+ and `invoice`, v8 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
@@ -588,7 +599,113 @@ change afterwards. The DDL and field semantics are in `docs/INVOICING.md`
rebuilding the table in the v9 migration (foreign keys are disabled for the
migration and `PRAGMA foreign_key_check` runs before they are re-enabled).
-## 15. Seeds
+## 15. Payroll (schema v10)
+
+The employee register and the monthly runs. `personal_no_enc` holds the
+AES-256-GCM envelope (`enc:v1:<nonce>:<ciphertext>`, `src/secret.c`) under
+`BOKFD_SECRET_KEY`; the plain number never touches the database. The run
+tables are mutable configuration/business documents, not ledger data: the
+money is in the immutable voucher of each run. `vouchers.source` gained
+`'payroll'` (the monthly run) and `'payroll_tax'` (the payment to the tax
+account); widening that CHECK required rebuilding the table in the v10
+migration, exactly like v9 (foreign keys are disabled for the migration and
+`PRAGMA foreign_key_check` runs before they are re-enabled).
+
+```sql
+CREATE TABLE employees (
+ org_id INTEGER NOT NULL REFERENCES orgs(id),
+ id INTEGER PRIMARY KEY,
+ name TEXT NOT NULL,
+ personal_no_enc TEXT NOT NULL, -- enc:v1:... (AES-256-GCM)
+ address TEXT NOT NULL DEFAULT '',
+ postal_code TEXT NOT NULL DEFAULT '',
+ city TEXT NOT NULL DEFAULT '',
+ bank_account TEXT NOT NULL DEFAULT '',
+ salary_account TEXT NOT NULL DEFAULT '7210',
+ monthly_salary_ore INTEGER NOT NULL DEFAULT 0 CHECK (monthly_salary_ore >= 0),
+ tax_table INTEGER NOT NULL DEFAULT 30 CHECK (tax_table BETWEEN 29 AND 42),
+ tax_column INTEGER NOT NULL DEFAULT 1 CHECK (tax_column BETWEEN 1 AND 6),
+ active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),
+ created_at TEXT NOT NULL,
+ updated_at TEXT,
+ UNIQUE (org_id, id)
+) STRICT;
+
+CREATE TABLE payroll_runs (
+ org_id INTEGER NOT NULL REFERENCES orgs(id),
+ id INTEGER PRIMARY KEY,
+ fiscal_year_id INTEGER NOT NULL,
+ period TEXT NOT NULL, -- YYYY-MM
+ pay_date TEXT NOT NULL,
+ status TEXT NOT NULL DEFAULT 'posted'
+ CHECK (status IN ('posted','paid')),
+ gross_ore INTEGER NOT NULL,
+ tax_ore INTEGER NOT NULL,
+ avgifter_ore INTEGER NOT NULL,
+ net_ore INTEGER NOT NULL,
+ voucher_id INTEGER,
+ payment_voucher_id INTEGER,
+ created_at TEXT NOT NULL,
+ created_by INTEGER NOT NULL REFERENCES users(id),
+ UNIQUE (org_id, id),
+ UNIQUE (org_id, period, pay_date),
+ FOREIGN KEY (org_id, fiscal_year_id) REFERENCES fiscal_years(org_id, id),
+ FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id),
+ FOREIGN KEY (org_id, payment_voucher_id) REFERENCES vouchers(org_id, id)
+) STRICT;
+
+CREATE TABLE payroll_run_lines (
+ org_id INTEGER NOT NULL,
+ id INTEGER PRIMARY KEY,
+ run_id INTEGER NOT NULL,
+ employee_id INTEGER NOT NULL,
+ gross_ore INTEGER NOT NULL,
+ tax_ore INTEGER NOT NULL,
+ avgifter_ore INTEGER NOT NULL,
+ net_ore INTEGER NOT NULL,
+ tax_table INTEGER NOT NULL,
+ tax_column INTEGER NOT NULL,
+ UNIQUE (org_id, id),
+ UNIQUE (org_id, run_id, employee_id),
+ FOREIGN KEY (org_id, run_id) REFERENCES payroll_runs(org_id, id),
+ FOREIGN KEY (org_id, employee_id) REFERENCES employees(org_id, id)
+) STRICT;
+```
+
+`payroll_runs` stores the posted totals and links both vouchers; the
+one-run-per-period rule is enforced by `payroll.run_post` on top of the
+`(org_id, period, pay_date)` key, and `status` flips to `paid` when
+`payroll.pay_tax` links the payment voucher.
+
+Skatteverket's allmänna monthly tables are national reference data and the
+only tables without `org_id` (an explicit exception to principle 4):
+
+```sql
+CREATE TABLE tax_tables (
+ in_year INTEGER NOT NULL,
+ table_no INTEGER NOT NULL, -- 29..42
+ column_no INTEGER NOT NULL, -- 1..6
+ income_from_ore INTEGER NOT NULL,
+ income_to_ore INTEGER, -- NULL = open-ended top range
+ tax_ore INTEGER NOT NULL, -- whole kronor x100 for B rows
+ pct INTEGER, -- % rows: percent x100, else NULL
+ PRIMARY KEY (in_year, table_no, column_no, income_from_ore)
+) STRICT;
+
+CREATE TABLE tax_table_meta (
+ in_year INTEGER PRIMARY KEY,
+ source_url TEXT NOT NULL,
+ sha256 BLOB NOT NULL CHECK (length(sha256) = 32),
+ fetched_at TEXT NOT NULL
+) STRICT;
+```
+
+B rows (`pct IS NULL`) hold the withholding in öre; % rows above the
+tabulated 80,000 kr/month range hold the percentage ×100 in `pct` with
+`tax_ore = 0` and may have `income_to_ore IS NULL` for the open-ended top
+range. Wave 1 looks up only B ranges (see `PROTOCOL.md` §7.12).
+
+## 16. Seeds
| Data | Source | Notes |
|---|---|---|
diff --git a/src/cmd_employees.c b/src/cmd_employees.c
new file mode 100644
index 0000000..788dd83
--- /dev/null
+++ b/src/cmd_employees.c
@@ -0,0 +1,665 @@
+#include "commands.h"
+#include "cmd_util.h"
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+
+#include "audit.h"
+#include "db.h"
+#include "secret.h"
+#include "util.h"
+
+/* ------------------------------------------------------------------ */
+/* employee register */
+/* ------------------------------------------------------------------ */
+
+#define EMPLOYEE_COLUMNS \
+ "id,name,personal_no_enc,address,postal_code,city,bank_account," \
+ "salary_account,monthly_salary_ore,tax_table,tax_column,active," \
+ "created_at,COALESCE(updated_at,'')"
+
+static int personal_no_valid(const char *s)
+{
+ if (!s)
+ return 0;
+ size_t n = strlen(s);
+ if (n != 10 && n != 11 && n != 12 && n != 13)
+ return 0;
+ if (n == 11 && s[6] != '-')
+ return 0;
+ if (n == 13 && s[8] != '-')
+ return 0;
+ for (size_t i = 0; i < n; i++) {
+ if (s[i] >= '0' && s[i] <= '9')
+ continue;
+ if (s[i] == '-' && ((n == 11 && i == 6) || (n == 13 && i == 8)))
+ continue;
+ return 0;
+ }
+ return 1;
+}
+
+static char *personal_no_mask(const char *pn)
+{
+ size_t n = pn ? strlen(pn) : 0;
+ char *out = xmalloc(n + 1);
+ for (size_t i = 0; i < n; i++) {
+ if (i + 4 >= n || pn[i] == '-')
+ out[i] = pn[i];
+ else
+ out[i] = '*';
+ }
+ out[n] = '\0';
+ return out;
+}
+
+static yyjson_mut_val *employee_json(struct req *r, sqlite3_stmt *st)
+{
+ char *pn = NULL;
+ if (secret_decrypt(sq(sqlite3_column_text(st, 2)), &pn) != 0)
+ pn = xstrdup("********");
+ char *masked = personal_no_mask(pn);
+ free(pn);
+
+ yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_int(r->rdoc, o, "id", sqlite3_column_int64(st, 0));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "name",
+ sq(sqlite3_column_text(st, 1)));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "personal_no", masked);
+ free(masked);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "address",
+ sq(sqlite3_column_text(st, 3)));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "postal_code",
+ sq(sqlite3_column_text(st, 4)));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "city",
+ sq(sqlite3_column_text(st, 5)));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "bank_account",
+ sq(sqlite3_column_text(st, 6)));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "salary_account",
+ sq(sqlite3_column_text(st, 7)));
+ yyjson_mut_obj_add_int(r->rdoc, o, "monthly_salary_ore",
+ sqlite3_column_int64(st, 8));
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_table",
+ sqlite3_column_int64(st, 9));
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_column",
+ sqlite3_column_int64(st, 10));
+ yyjson_mut_obj_add_bool(r->rdoc, o, "active",
+ sqlite3_column_int(st, 11) != 0);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
+ sq(sqlite3_column_text(st, 12)));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "updated_at",
+ sq(sqlite3_column_text(st, 13)));
+ return o;
+}
+
+/* The register's audit entries must not contain the personnummer. */
+static char *employee_audit_json(yyjson_val *args)
+{
+ if (!args || !yyjson_is_obj(args))
+ return xstrdup("{}");
+ yyjson_mut_doc *doc = yyjson_mut_doc_new(NULL);
+ yyjson_mut_val *out = yyjson_mut_obj(doc);
+ yyjson_mut_doc_set_root(doc, out);
+ yyjson_obj_iter it = yyjson_obj_iter_with(args);
+ yyjson_val *key;
+ while ((key = yyjson_obj_iter_next(&it))) {
+ const char *k = yyjson_get_str(key);
+ yyjson_val *v = yyjson_obj_iter_get_val(key);
+ if (!k || !v)
+ continue;
+ if (strcmp(k, "personal_no") == 0) {
+ yyjson_mut_obj_add_strcpy(doc, out, k, "[redacted]");
+ continue;
+ }
+ yyjson_mut_val *copy = yyjson_val_mut_copy(doc, v);
+ if (copy)
+ yyjson_mut_obj_add(out, yyjson_mut_strcpy(doc, k), copy);
+ }
+ char *json = yyjson_mut_write(doc, 0, NULL);
+ yyjson_mut_doc_free(doc);
+ return json ? json : xstrdup("{}");
+}
+
+struct employee_input {
+ const char *name;
+ const char *personal_no;
+ const char *address;
+ const char *postal_code;
+ const char *city;
+ const char *bank_account;
+ const char *salary_account;
+ int64_t monthly_salary_ore;
+ int have_salary;
+ int64_t tax_table;
+ int have_table;
+ int64_t tax_column;
+ int have_column;
+ int active;
+ int have_active;
+};
+
+static void employee_input_read(struct req *r, struct employee_input *in)
+{
+ memset(in, 0, sizeof *in);
+ in->name = arg_str(r->args, "name");
+ in->personal_no = arg_str(r->args, "personal_no");
+ in->address = arg_str(r->args, "address");
+ in->postal_code = arg_str(r->args, "postal_code");
+ in->city = arg_str(r->args, "city");
+ in->bank_account = arg_str(r->args, "bank_account");
+ in->salary_account = arg_str(r->args, "salary_account");
+ in->have_salary = arg_int(r->args, "monthly_salary_ore",
+ &in->monthly_salary_ore);
+ in->have_table = arg_int(r->args, "tax_table", &in->tax_table);
+ in->have_column = arg_int(r->args, "tax_column", &in->tax_column);
+ in->have_active = arg_bool(r->args, "active", &in->active);
+}
+
+static int employee_input_validate(struct req *r,
+ const struct employee_input *in,
+ int is_create)
+{
+ if (is_create && (!in->name || !*in->name)) {
+ fail(r, "INVALID_ARGS", "name is required");
+ return -1;
+ }
+ if (in->name && !*in->name) {
+ fail(r, "INVALID_ARGS", "name cannot be empty");
+ return -1;
+ }
+ if (in->name && strlen(in->name) > 200) {
+ fail(r, "INVALID_ARGS", "name is too long");
+ return -1;
+ }
+ if (is_create && !in->personal_no) {
+ fail(r, "INVALID_ARGS", "personal_no is required");
+ return -1;
+ }
+ if (in->personal_no && !personal_no_valid(in->personal_no)) {
+ fail(r, "INVALID_ARGS",
+ "personal_no must be 10 or 12 digits, with or without a hyphen");
+ return -1;
+ }
+ static const char *const names[] = { "address", "postal_code", "city",
+ "bank_account" };
+ static const size_t maxlen[] = { 500, 32, 120, 64 };
+ const char *values[] = { in->address, in->postal_code, in->city,
+ in->bank_account };
+ for (size_t i = 0; i < sizeof maxlen / sizeof maxlen[0]; i++) {
+ if (values[i] && strlen(values[i]) > maxlen[i]) {
+ failf(r, "INVALID_ARGS", "%s is too long", names[i]);
+ return -1;
+ }
+ }
+ if (in->salary_account &&
+ (!is_digits(in->salary_account) ||
+ strlen(in->salary_account) > 10)) {
+ fail(r, "INVALID_ARGS", "salary_account must be digits only");
+ return -1;
+ }
+ if (in->have_salary && in->monthly_salary_ore < 0) {
+ fail(r, "INVALID_ARGS", "monthly_salary_ore must be >= 0");
+ return -1;
+ }
+ if (in->have_table && (in->tax_table < 29 || in->tax_table > 42)) {
+ fail(r, "INVALID_ARGS", "tax_table must be between 29 and 42");
+ return -1;
+ }
+ if (in->have_column && (in->tax_column < 1 || in->tax_column > 6)) {
+ fail(r, "INVALID_ARGS", "tax_column must be between 1 and 6");
+ return -1;
+ }
+ return 0;
+}
+
+static yyjson_mut_val *employee_lookup(struct req *r, int64_t id, int *found)
+{
+ sqlite3_stmt *st = NULL;
+ *found = 0;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT " EMPLOYEE_COLUMNS " FROM employees"
+ " WHERE org_id=?1 AND id=?2",
+ -1, &st, NULL) != SQLITE_OK) {
+ *found = -1;
+ db_error(r);
+ return NULL;
+ }
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, id);
+ if (sqlite3_step(st) != SQLITE_ROW) {
+ sqlite3_finalize(st);
+ return NULL;
+ }
+ yyjson_mut_val *o = employee_json(r, st);
+ sqlite3_finalize(st);
+ *found = 1;
+ return o;
+}
+
+/* Encrypts the personnummer and rejects a duplicate (compared on the
+ decrypted value, since the stored ciphertext has a fresh nonce). */
+static int employee_personal_no_store(struct req *r, const char *plain,
+ int64_t except_id, char **stored_out)
+{
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT personal_no_enc FROM employees WHERE org_id=?1 AND id<>?2",
+ -1, &st, NULL) != SQLITE_OK) {
+ db_error(r);
+ return -1;
+ }
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, except_id);
+ while (sqlite3_step(st) == SQLITE_ROW) {
+ char *other = NULL;
+ if (secret_decrypt(sq(sqlite3_column_text(st, 0)), &other) != 0)
+ continue;
+ int same = strcmp(other, plain) == 0;
+ free(other);
+ if (same) {
+ sqlite3_finalize(st);
+ fail(r, "CONFLICT",
+ "an employee with this personal_no already exists");
+ return -1;
+ }
+ }
+ sqlite3_finalize(st);
+ if (!secret_available()) {
+ fail(r, "INTERNAL", "BOKFD_SECRET_KEY is missing or invalid");
+ return -1;
+ }
+ char *enc = NULL;
+ if (secret_encrypt(plain, &enc) != 0) {
+ fail(r, "INTERNAL", "could not encrypt the personnummer");
+ return -1;
+ }
+ *stored_out = enc;
+ return 0;
+}
+
+static yyjson_mut_val *h_employee_list(struct req *r)
+{
+ int active_only = 0;
+ arg_bool(r->args, "active_only", &active_only);
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT " EMPLOYEE_COLUMNS " FROM employees WHERE org_id=?1"
+ " AND (?2=0 OR active=1) ORDER BY name COLLATE NOCASE, id",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int(st, 2, active_only);
+ yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
+ while (sqlite3_step(st) == SQLITE_ROW)
+ yyjson_mut_arr_add_val(items, employee_json(r, st));
+ sqlite3_finalize(st);
+ yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
+ return out;
+}
+
+static yyjson_mut_val *h_employee_get(struct req *r)
+{
+ int64_t id = 0;
+ if (!arg_int(r->args, "id", &id) || id <= 0)
+ return fail(r, "INVALID_ARGS", "id is required");
+ int found = 0;
+ yyjson_mut_val *o = employee_lookup(r, id, &found);
+ if (found < 0)
+ return NULL;
+ if (!found)
+ return fail(r, "NOT_FOUND", "employee not found");
+ return o;
+}
+
+static yyjson_mut_val *h_employee_create(struct req *r)
+{
+ struct employee_input in;
+ employee_input_read(r, &in);
+ if (employee_input_validate(r, &in, 1) != 0)
+ return NULL;
+ if (!in.have_salary)
+ in.monthly_salary_ore = 0;
+ if (!in.have_table)
+ in.tax_table = 30;
+ if (!in.have_column)
+ in.tax_column = 1;
+
+ char *stored = NULL;
+ if (employee_personal_no_store(r, in.personal_no, 0, &stored) != 0)
+ return NULL;
+
+ char account[16];
+ if (in.salary_account) {
+ snprintf(account, sizeof account, "%s", in.salary_account);
+ } else {
+ char *setting = db_setting(r->db, r->org_id, "payroll_salary_account");
+ snprintf(account, sizeof account, "%s",
+ setting && *setting ? setting : "7210");
+ free(setting);
+ }
+ const char *address = in.address ? in.address : "";
+ const char *postal = in.postal_code ? in.postal_code : "";
+ const char *city = in.city ? in.city : "";
+ const char *bank = in.bank_account ? in.bank_account : "";
+
+ if (r->dry_run) {
+ free(stored);
+ char *masked = personal_no_mask(in.personal_no);
+ yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_int(r->rdoc, o, "id", 0);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "name", in.name);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "personal_no", masked);
+ free(masked);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "address", address);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "postal_code", postal);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "city", city);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "bank_account", bank);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "salary_account", account);
+ yyjson_mut_obj_add_int(r->rdoc, o, "monthly_salary_ore",
+ in.monthly_salary_ore);
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_table", in.tax_table);
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_column", in.tax_column);
+ yyjson_mut_obj_add_bool(r->rdoc, o, "active", true);
+ yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+ return o;
+ }
+
+ char ts[32];
+ util_iso8601(util_now(), ts, sizeof ts);
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "INSERT INTO employees(org_id,name,personal_no_enc,address,"
+ "postal_code,city,bank_account,salary_account,monthly_salary_ore,"
+ "tax_table,tax_column,created_at)"
+ " VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12)",
+ -1, &st, NULL) != SQLITE_OK) {
+ free(stored);
+ return db_error(r);
+ }
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_text(st, 2, in.name, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_text(st, 3, stored, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_text(st, 4, address, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_text(st, 5, postal, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_text(st, 6, city, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_text(st, 7, bank, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_text(st, 8, account, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_int64(st, 9, in.monthly_salary_ore);
+ sqlite3_bind_int64(st, 10, in.tax_table);
+ sqlite3_bind_int64(st, 11, in.tax_column);
+ sqlite3_bind_text(st, 12, ts, -1, SQLITE_TRANSIENT);
+ int rc = sqlite3_step(st);
+ sqlite3_finalize(st);
+ free(stored);
+ if (rc != SQLITE_DONE) {
+ if ((rc & 0xff) == SQLITE_CONSTRAINT)
+ return fail(r, "CONFLICT",
+ "an employee with this personal_no already exists");
+ return db_sqlite_error(r);
+ }
+ int64_t id = db_last_id(r->db);
+ char *reqjson = employee_audit_json(r->args);
+ audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+ "employee.create", reqjson, "OK", NULL);
+ free(reqjson);
+ int found = 0;
+ yyjson_mut_val *o = employee_lookup(r, id, &found);
+ if (found < 0)
+ return NULL;
+ if (!found)
+ return fail(r, "INTERNAL", "could not read the new employee");
+ return o;
+}
+
+static yyjson_mut_val *h_employee_update(struct req *r)
+{
+ int64_t id = 0;
+ if (!arg_int(r->args, "id", &id) || id <= 0)
+ return fail(r, "INVALID_ARGS", "id is required");
+ struct employee_input in;
+ employee_input_read(r, &in);
+ if (employee_input_validate(r, &in, 0) != 0)
+ return NULL;
+ if (!in.name && !in.personal_no && !in.address && !in.postal_code &&
+ !in.city && !in.bank_account && !in.salary_account &&
+ !in.have_salary && !in.have_table && !in.have_column &&
+ !in.have_active)
+ return fail(r, "INVALID_ARGS", "nothing to update");
+
+ int found = 0;
+ yyjson_mut_val *existing = employee_lookup(r, id, &found);
+ (void)existing;
+ if (found < 0)
+ return NULL;
+ if (!found)
+ return fail(r, "NOT_FOUND", "employee not found");
+
+ char *stored = NULL;
+ if (in.personal_no &&
+ employee_personal_no_store(r, in.personal_no, id, &stored) != 0)
+ return NULL;
+
+ if (r->dry_run) {
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT id,COALESCE(?3,name),COALESCE(?4,personal_no_enc),"
+ "COALESCE(?5,address),COALESCE(?6,postal_code),"
+ "COALESCE(?7,city),COALESCE(?8,bank_account),"
+ "COALESCE(?9,salary_account),"
+ "CASE WHEN ?10<0 THEN monthly_salary_ore ELSE ?10 END,"
+ "CASE WHEN ?11<0 THEN tax_table ELSE ?11 END,"
+ "CASE WHEN ?12<0 THEN tax_column ELSE ?12 END,"
+ "CASE WHEN ?13<0 THEN active ELSE ?13 END,"
+ "created_at,COALESCE(updated_at,'')"
+ " FROM employees WHERE org_id=?1 AND id=?2",
+ -1, &st, NULL) != SQLITE_OK) {
+ free(stored);
+ return db_error(r);
+ }
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, id);
+ bind_text_or_null(st, 3, in.name);
+ bind_text_or_null(st, 4, stored);
+ bind_text_or_null(st, 5, in.address);
+ bind_text_or_null(st, 6, in.postal_code);
+ bind_text_or_null(st, 7, in.city);
+ bind_text_or_null(st, 8, in.bank_account);
+ bind_text_or_null(st, 9, in.salary_account);
+ sqlite3_bind_int64(st, 10, in.have_salary ? in.monthly_salary_ore
+ : -1);
+ sqlite3_bind_int64(st, 11, in.have_table ? in.tax_table : -1);
+ sqlite3_bind_int64(st, 12, in.have_column ? in.tax_column : -1);
+ sqlite3_bind_int64(st, 13, in.have_active ? in.active : -1);
+ yyjson_mut_val *o = NULL;
+ if (sqlite3_step(st) == SQLITE_ROW)
+ o = employee_json(r, st);
+ sqlite3_finalize(st);
+ free(stored);
+ if (!o)
+ return fail(r, "NOT_FOUND", "employee not found");
+ yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+ return o;
+ }
+
+ char ts[32];
+ util_iso8601(util_now(), ts, sizeof ts);
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "UPDATE employees SET"
+ " name=COALESCE(?3,name),"
+ " personal_no_enc=COALESCE(?4,personal_no_enc),"
+ " address=COALESCE(?5,address),"
+ " postal_code=COALESCE(?6,postal_code),"
+ " city=COALESCE(?7,city),"
+ " bank_account=COALESCE(?8,bank_account),"
+ " salary_account=COALESCE(?9,salary_account),"
+ " monthly_salary_ore=CASE WHEN ?10<0 THEN monthly_salary_ore"
+ " ELSE ?10 END,"
+ " tax_table=CASE WHEN ?11<0 THEN tax_table ELSE ?11 END,"
+ " tax_column=CASE WHEN ?12<0 THEN tax_column ELSE ?12 END,"
+ " active=CASE WHEN ?13<0 THEN active ELSE ?13 END,"
+ " updated_at=?14 WHERE org_id=?1 AND id=?2",
+ -1, &st, NULL) != SQLITE_OK) {
+ free(stored);
+ return db_error(r);
+ }
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, id);
+ bind_text_or_null(st, 3, in.name);
+ bind_text_or_null(st, 4, stored);
+ bind_text_or_null(st, 5, in.address);
+ bind_text_or_null(st, 6, in.postal_code);
+ bind_text_or_null(st, 7, in.city);
+ bind_text_or_null(st, 8, in.bank_account);
+ bind_text_or_null(st, 9, in.salary_account);
+ sqlite3_bind_int64(st, 10, in.have_salary ? in.monthly_salary_ore : -1);
+ sqlite3_bind_int64(st, 11, in.have_table ? in.tax_table : -1);
+ sqlite3_bind_int64(st, 12, in.have_column ? in.tax_column : -1);
+ sqlite3_bind_int64(st, 13, in.have_active ? in.active : -1);
+ sqlite3_bind_text(st, 14, ts, -1, SQLITE_TRANSIENT);
+ int rc = sqlite3_step(st);
+ sqlite3_finalize(st);
+ free(stored);
+ if (rc != SQLITE_DONE) {
+ if ((rc & 0xff) == SQLITE_CONSTRAINT)
+ return fail(r, "CONFLICT",
+ "an employee with this personal_no already exists");
+ return db_sqlite_error(r);
+ }
+ if (sqlite3_changes(r->db) == 0)
+ return fail(r, "NOT_FOUND", "employee not found");
+ char *reqjson = employee_audit_json(r->args);
+ audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+ "employee.update", reqjson, "OK", NULL);
+ free(reqjson);
+ found = 0;
+ yyjson_mut_val *o = employee_lookup(r, id, &found);
+ if (found < 0)
+ return NULL;
+ if (!found)
+ return fail(r, "INTERNAL", "could not read the employee");
+ return o;
+}
+
+static yyjson_mut_val *h_employee_archive(struct req *r)
+{
+ int64_t id = 0;
+ int active = 0;
+ if (!arg_int(r->args, "id", &id) || id <= 0 ||
+ !arg_bool(r->args, "active", &active))
+ return fail(r, "INVALID_ARGS", "id and active are required");
+ int found = 0;
+ yyjson_mut_val *existing = employee_lookup(r, id, &found);
+ (void)existing;
+ if (found < 0)
+ return NULL;
+ if (!found)
+ return fail(r, "NOT_FOUND", "employee not found");
+
+ if (r->dry_run) {
+ yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_int(r->rdoc, o, "id", id);
+ yyjson_mut_obj_add_bool(r->rdoc, o, "active", active != 0);
+ yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+ return o;
+ }
+
+ char ts[32];
+ util_iso8601(util_now(), ts, sizeof ts);
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "UPDATE employees SET active=?3, updated_at=?4"
+ " WHERE org_id=?1 AND id=?2",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, id);
+ sqlite3_bind_int(st, 3, active);
+ sqlite3_bind_text(st, 4, ts, -1, SQLITE_TRANSIENT);
+ int rc = sqlite3_step(st);
+ sqlite3_finalize(st);
+ if (rc != SQLITE_DONE)
+ return db_sqlite_error(r);
+ if (sqlite3_changes(r->db) == 0)
+ return fail(r, "NOT_FOUND", "employee not found");
+ char *reqjson = employee_audit_json(r->args);
+ audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+ "employee.archive", reqjson, "OK", NULL);
+ free(reqjson);
+ yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_int(r->rdoc, o, "id", id);
+ yyjson_mut_obj_add_bool(r->rdoc, o, "active", active != 0);
+ return o;
+}
+
+static const struct cmd_arg args_employee_list[] = {
+ { "active_only", ARG_BOOL, 0, NULL, NULL, "Only active employees" },
+};
+
+static const struct cmd_arg args_employee_get[] = {
+ { "id", ARG_INT, 1, NULL, NULL, "Employee id" },
+};
+
+static const struct cmd_arg args_employee_create[] = {
+ { "name", ARG_STR, 1, NULL, NULL, "Employee name" },
+ { "personal_no", ARG_STR, 1, NULL, NULL,
+ "10 or 12 digits, with or without a hyphen; encrypted at rest" },
+ { "address", ARG_STR, 0, NULL, NULL, "Street address" },
+ { "postal_code", ARG_STR, 0, NULL, NULL, "Postal code" },
+ { "city", ARG_STR, 0, NULL, NULL, "City" },
+ { "bank_account", ARG_STR, 0, NULL, NULL, "Bank account for the net pay" },
+ { "salary_account", ARG_STR, 0, NULL, NULL,
+ "Salary account, digits only; defaults to payroll_salary_account" },
+ { "monthly_salary_ore", ARG_INT, 0, "0", NULL,
+ "Monthly gross salary in öre" },
+ { "tax_table", ARG_INT, 0, "30", NULL, "Skatteverket table 29-42" },
+ { "tax_column", ARG_INT, 0, "1", NULL, "Tax column 1-6" },
+};
+
+static const struct cmd_arg args_employee_update[] = {
+ { "id", ARG_INT, 1, NULL, NULL, "Employee id" },
+ { "name", ARG_STR, 0, NULL, NULL, "Employee name" },
+ { "personal_no", ARG_STR, 0, NULL, NULL,
+ "New personnummer, encrypted at rest" },
+ { "address", ARG_STR, 0, NULL, NULL, "Street address" },
+ { "postal_code", ARG_STR, 0, NULL, NULL, "Postal code" },
+ { "city", ARG_STR, 0, NULL, NULL, "City" },
+ { "bank_account", ARG_STR, 0, NULL, NULL, "Bank account for the net pay" },
+ { "salary_account", ARG_STR, 0, NULL, NULL, "Salary account, digits only" },
+ { "monthly_salary_ore", ARG_INT, 0, NULL, NULL,
+ "Monthly gross salary in öre" },
+ { "tax_table", ARG_INT, 0, NULL, NULL, "Skatteverket table 29-42" },
+ { "tax_column", ARG_INT, 0, NULL, NULL, "Tax column 1-6" },
+ { "active", ARG_BOOL, 0, NULL, NULL, "Active flag" },
+};
+
+static const struct cmd_arg args_employee_archive[] = {
+ { "id", ARG_INT, 1, NULL, NULL, "Employee id" },
+ { "active", ARG_BOOL, 1, NULL, NULL, "false archives, true reactivates" },
+};
+
+const struct command g_cmd_employees[] = {
+ { "employee.list", "List employees ordered by name", PERM_READ, 1, 0, 0,
+ h_employee_list, CMD_ARGS(args_employee_list) },
+ { "employee.get", "Get one employee (personnummer masked)", PERM_READ, 1,
+ 0, 0, h_employee_get, CMD_ARGS(args_employee_get) },
+ { "employee.create", "Create an employee", PERM_WRITE, 1, 1, 1,
+ h_employee_create, CMD_ARGS(args_employee_create) },
+ { "employee.update", "Update an employee (merged)", PERM_WRITE, 1, 1, 1,
+ h_employee_update, CMD_ARGS(args_employee_update) },
+ { "employee.archive", "Archive or reactivate an employee", PERM_WRITE, 1,
+ 1, 1, h_employee_archive, CMD_ARGS(args_employee_archive) },
+};
+
+const struct cmd_table g_cmd_table_employees = {
+ g_cmd_employees, sizeof g_cmd_employees / sizeof g_cmd_employees[0]
+};
diff --git a/src/cmd_payroll.c b/src/cmd_payroll.c
new file mode 100644
index 0000000..e7cafa3
--- /dev/null
+++ b/src/cmd_payroll.c
@@ -0,0 +1,1254 @@
+#include "commands.h"
+#include "cmd_util.h"
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+#include <time.h>
+
+#include "audit.h"
+#include "db.h"
+#include "ledger.h"
+#include "secret.h"
+#include "tax_table.h"
+#include "util.h"
+
+/* ------------------------------------------------------------------ */
+/* payroll settings */
+/* ------------------------------------------------------------------ */
+
+#define NET_SALARY_ACCOUNT "1930"
+
+struct payroll_cfg {
+ char salary[16];
+ char tax[16];
+ char avgift[16];
+ char liability[16];
+ char payment[16];
+ int64_t rate_bp;
+};
+
+static const struct {
+ const char *key;
+ const char *def;
+} PAYROLL_SETTINGS[] = {
+ { "payroll_salary_account", "7210" },
+ { "payroll_tax_account", "2710" },
+ { "payroll_avgift_account", "7510" },
+ { "payroll_avgift_liability", "2731" },
+ { "payroll_tax_payment_account", "1630" },
+ { "payroll_avgift_rate_bp", "3142" },
+};
+
+static const size_t PAYROLL_SETTINGS_N =
+ sizeof PAYROLL_SETTINGS / sizeof PAYROLL_SETTINGS[0];
+
+static int payroll_setting_index(const char *key)
+{
+ if (!key)
+ return -1;
+ for (size_t i = 0; i < PAYROLL_SETTINGS_N; i++)
+ if (strcmp(PAYROLL_SETTINGS[i].key, key) == 0)
+ return (int)i;
+ return -1;
+}
+
+static void payroll_cfg_load(struct req *r, struct payroll_cfg *c)
+{
+ char *v = NULL;
+ v = db_setting(r->db, r->org_id, "payroll_salary_account");
+ snprintf(c->salary, sizeof c->salary, "%s",
+ v && is_digits(v) && strlen(v) <= 10 ? v : "7210");
+ free(v);
+ v = db_setting(r->db, r->org_id, "payroll_tax_account");
+ snprintf(c->tax, sizeof c->tax, "%s",
+ v && is_digits(v) && strlen(v) <= 10 ? v : "2710");
+ free(v);
+ v = db_setting(r->db, r->org_id, "payroll_avgift_account");
+ snprintf(c->avgift, sizeof c->avgift, "%s",
+ v && is_digits(v) && strlen(v) <= 10 ? v : "7510");
+ free(v);
+ v = db_setting(r->db, r->org_id, "payroll_avgift_liability");
+ snprintf(c->liability, sizeof c->liability, "%s",
+ v && is_digits(v) && strlen(v) <= 10 ? v : "2731");
+ free(v);
+ v = db_setting(r->db, r->org_id, "payroll_tax_payment_account");
+ snprintf(c->payment, sizeof c->payment, "%s",
+ v && is_digits(v) && strlen(v) <= 10 ? v : "1630");
+ free(v);
+ c->rate_bp = 3142;
+ v = db_setting(r->db, r->org_id, "payroll_avgift_rate_bp");
+ if (v && is_digits(v)) {
+ long rate = strtol(v, NULL, 10);
+ if (rate >= 1 && rate <= 10000)
+ c->rate_bp = rate;
+ }
+ free(v);
+}
+
+static yyjson_mut_val *h_payroll_settings_get(struct req *r)
+{
+ struct payroll_cfg c;
+ payroll_cfg_load(r, &c);
+ yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "payroll_salary_account", c.salary);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "payroll_tax_account", c.tax);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "payroll_avgift_account", c.avgift);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "payroll_avgift_liability",
+ c.liability);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "payroll_tax_payment_account",
+ c.payment);
+ yyjson_mut_obj_add_int(r->rdoc, o, "payroll_avgift_rate_bp", c.rate_bp);
+ return o;
+}
+
+static yyjson_mut_val *h_payroll_settings_set(struct req *r)
+{
+ const char *key = arg_str(r->args, "key");
+ const char *value = arg_str(r->args, "value");
+ if (payroll_setting_index(key) < 0)
+ return fail(r, "UNSUPPORTED", "unknown payroll setting");
+ if (!value || !*value)
+ return fail(r, "INVALID_ARGS", "value is required");
+ if (!is_digits(value))
+ return failf(r, "INVALID_ARGS", "%s must be digits only", key);
+ if (strcmp(key, "payroll_avgift_rate_bp") == 0) {
+ if (strlen(value) > 5)
+ return fail(r, "INVALID_ARGS",
+ "payroll_avgift_rate_bp must be 1-10000");
+ long rate = strtol(value, NULL, 10);
+ if (rate < 1 || rate > 10000)
+ return fail(r, "INVALID_ARGS",
+ "payroll_avgift_rate_bp must be 1-10000");
+ } else if (strlen(value) > 10) {
+ return failf(r, "INVALID_ARGS", "%s must be 1-10 digits", key);
+ }
+ if (r->dry_run) {
+ yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "key", key);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "value", value);
+ yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+ return o;
+ }
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "INSERT INTO settings(org_id,key,value) VALUES(?1,?2,?3)"
+ " ON CONFLICT(org_id,key) DO UPDATE SET value=excluded.value",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_text(st, 2, key, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_text(st, 3, value, -1, SQLITE_TRANSIENT);
+ int rc = sqlite3_step(st);
+ sqlite3_finalize(st);
+ if (rc != SQLITE_DONE)
+ return db_sqlite_error(r);
+ char *reqjson = audit_args_json(r->args);
+ audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+ "payroll.settings_set", reqjson, "OK", NULL);
+ free(reqjson);
+ yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "key", key);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "value", value);
+ return o;
+}
+
+/* ------------------------------------------------------------------ */
+/* the monthly run */
+/* ------------------------------------------------------------------ */
+
+struct payroll_line {
+ int64_t employee_id;
+ char name[200];
+ char salary_account[16];
+ int tax_table;
+ int tax_column;
+ int64_t gross_ore;
+ int64_t tax_ore;
+ int64_t avgifter_ore;
+ int64_t net_ore;
+};
+
+static int payroll_current_year(void)
+{
+ time_t t = time(NULL);
+ struct tm tm;
+ gmtime_r(&t, &tm);
+ return tm.tm_year + 1900;
+}
+
+static int payroll_period_parse(const char *p, int *year, int *month)
+{
+ if (!p || strlen(p) != 7 || p[4] != '-')
+ return -1;
+ for (int i = 0; i < 7; i++) {
+ if (i == 4)
+ continue;
+ if (p[i] < '0' || p[i] > '9')
+ return -1;
+ }
+ int y = (p[0] - '0') * 1000 + (p[1] - '0') * 100 + (p[2] - '0') * 10 +
+ (p[3] - '0');
+ int m = (p[5] - '0') * 10 + (p[6] - '0');
+ if (m < 1 || m > 12)
+ return -1;
+ *year = y;
+ *month = m;
+ return 0;
+}
+
+static int payroll_period_in_range(const char *period, const char *start,
+ const char *end)
+{
+ int y = 0, m = 0;
+ if (payroll_period_parse(period, &y, &m) != 0)
+ return 0;
+ char first[16], last[16];
+ snprintf(first, sizeof first, "%04d-%02d-01", y, m);
+ snprintf(last, sizeof last, "%04d-%02d-%02d", y, m,
+ util_days_in_month(y, m));
+ return strcmp(start, last) <= 0 && strcmp(end, first) >= 0;
+}
+
+static int64_t payroll_avgifter(int64_t gross_ore, int64_t rate_bp)
+{
+ return (gross_ore * rate_bp + 5000) / 10000;
+}
+
+static int payroll_compute(struct req *r, int table_year,
+ const struct payroll_cfg *cfg,
+ struct payroll_line **out, size_t *out_n)
+{
+ *out = NULL;
+ *out_n = 0;
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT id,name,salary_account,monthly_salary_ore,tax_table,"
+ "tax_column FROM employees WHERE org_id=?1 AND active=1"
+ " AND monthly_salary_ore>0 ORDER BY id",
+ -1, &st, NULL) != SQLITE_OK) {
+ db_error(r);
+ return -1;
+ }
+ sqlite3_bind_int64(st, 1, r->org_id);
+ struct payroll_line *lines = NULL;
+ size_t n = 0, cap = 0;
+ int rc = 0;
+ while (sqlite3_step(st) == SQLITE_ROW) {
+ if (n == cap) {
+ cap = cap ? cap * 2 : 8;
+ lines = xrealloc(lines, cap * sizeof *lines);
+ }
+ struct payroll_line *l = &lines[n];
+ memset(l, 0, sizeof *l);
+ l->employee_id = sqlite3_column_int64(st, 0);
+ snprintf(l->name, sizeof l->name, "%s",
+ sq(sqlite3_column_text(st, 1)));
+ snprintf(l->salary_account, sizeof l->salary_account, "%s",
+ sq(sqlite3_column_text(st, 2)));
+ l->gross_ore = sqlite3_column_int64(st, 3);
+ l->tax_table = (int)sqlite3_column_int64(st, 4);
+ l->tax_column = (int)sqlite3_column_int64(st, 5);
+ n++;
+ }
+ sqlite3_finalize(st);
+ for (size_t i = 0; i < n; i++) {
+ struct payroll_line *l = &lines[i];
+ int64_t tax = 0;
+ int look;
+ if (l->gross_ore * 12 < 100000) {
+ look = 0;
+ } else {
+ look = tax_table_lookup(r->db, table_year, l->tax_table,
+ l->tax_column, l->gross_ore, &tax);
+ }
+ if (look == 1) {
+ failf(r, "INVALID_ARGS",
+ "income above the tabulated range is not supported yet"
+ " (employee %s, table %d column %d)",
+ l->name, l->tax_table, l->tax_column);
+ rc = -1;
+ break;
+ }
+ if (look == 2) {
+ failf(r, "INVALID_ARGS",
+ "no stored tax table for %d table %d column %d"
+ " (employee %s)",
+ table_year, l->tax_table, l->tax_column, l->name);
+ rc = -1;
+ break;
+ }
+ if (look < 0) {
+ db_error(r);
+ rc = -1;
+ break;
+ }
+ l->tax_ore = tax;
+ l->avgifter_ore = payroll_avgifter(l->gross_ore, cfg->rate_bp);
+ l->net_ore = l->gross_ore - tax;
+ }
+ if (rc != 0) {
+ free(lines);
+ return -1;
+ }
+ *out = lines;
+ *out_n = n;
+ return 0;
+}
+
+static yyjson_mut_val *payroll_items_json(struct req *r,
+ const struct payroll_line *lines,
+ size_t n,
+ const struct payroll_cfg *cfg)
+{
+ yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
+ int64_t total_gross = 0, total_tax = 0, total_avg = 0, total_net = 0;
+ for (size_t i = 0; i < n; i++) {
+ const struct payroll_line *l = &lines[i];
+ yyjson_mut_val *o = yyjson_mut_arr_add_obj(r->rdoc, items);
+ yyjson_mut_obj_add_int(r->rdoc, o, "employee_id", l->employee_id);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "name", l->name);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "salary_account",
+ l->salary_account);
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_table", l->tax_table);
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_column", l->tax_column);
+ yyjson_mut_obj_add_int(r->rdoc, o, "gross_ore", l->gross_ore);
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_ore", l->tax_ore);
+ yyjson_mut_obj_add_int(r->rdoc, o, "avgifter_ore", l->avgifter_ore);
+ yyjson_mut_obj_add_int(r->rdoc, o, "net_ore", l->net_ore);
+ total_gross += l->gross_ore;
+ total_tax += l->tax_ore;
+ total_avg += l->avgifter_ore;
+ total_net += l->net_ore;
+ }
+ yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
+ yyjson_mut_val *totals = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_int(r->rdoc, totals, "gross_ore", total_gross);
+ yyjson_mut_obj_add_int(r->rdoc, totals, "tax_ore", total_tax);
+ yyjson_mut_obj_add_int(r->rdoc, totals, "avgifter_ore", total_avg);
+ yyjson_mut_obj_add_int(r->rdoc, totals, "net_ore", total_net);
+ yyjson_mut_obj_add_val(r->rdoc, out, "totals", totals);
+ yyjson_mut_val *accounts = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_strcpy(r->rdoc, accounts, "tax", cfg->tax);
+ yyjson_mut_obj_add_strcpy(r->rdoc, accounts, "avgift", cfg->avgift);
+ yyjson_mut_obj_add_strcpy(r->rdoc, accounts, "liability", cfg->liability);
+ yyjson_mut_obj_add_strcpy(r->rdoc, accounts, "payment", cfg->payment);
+ yyjson_mut_obj_add_strcpy(r->rdoc, accounts, "net", NET_SALARY_ACCOUNT);
+ yyjson_mut_obj_add_val(r->rdoc, out, "accounts", accounts);
+ yyjson_mut_obj_add_int(r->rdoc, out, "avgift_rate_bp", cfg->rate_bp);
+ return out;
+}
+
+static yyjson_mut_val *h_payroll_run_preview(struct req *r)
+{
+ const char *period = arg_str(r->args, "period");
+ int year = 0, month = 0;
+ if (payroll_period_parse(period, &year, &month) != 0)
+ return fail(r, "INVALID_ARGS", "period must be YYYY-MM");
+ struct payroll_cfg cfg;
+ payroll_cfg_load(r, &cfg);
+ struct payroll_line *lines = NULL;
+ size_t n = 0;
+ if (payroll_compute(r, year, &cfg, &lines, &n) != 0)
+ return NULL;
+ yyjson_mut_val *out = payroll_items_json(r, lines, n, &cfg);
+ free(lines);
+ yyjson_mut_obj_add_strcpy(r->rdoc, out, "period", period);
+ yyjson_mut_obj_add_int(r->rdoc, out, "tax_year", year);
+ return out;
+}
+
+static int payroll_run_exists(struct req *r, const char *period)
+{
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT id FROM payroll_runs WHERE org_id=?1 AND period=?2",
+ -1, &st, NULL) != SQLITE_OK) {
+ db_error(r);
+ return -1;
+ }
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_text(st, 2, period, -1, SQLITE_TRANSIENT);
+ int exists = sqlite3_step(st) == SQLITE_ROW;
+ sqlite3_finalize(st);
+ return exists;
+}
+
+static yyjson_mut_val *h_payroll_run_post(struct req *r)
+{
+ const char *period = arg_str(r->args, "period");
+ const char *pay_date = arg_str(r->args, "pay_date");
+ int year = 0, month = 0;
+ if (payroll_period_parse(period, &year, &month) != 0)
+ return fail(r, "INVALID_ARGS", "period must be YYYY-MM");
+ if (!pay_date || !util_parse_iso_date(pay_date))
+ return fail(r, "INVALID_ARGS", "pay_date must be YYYY-MM-DD");
+ int pay_year = (pay_date[0] - '0') * 1000 + (pay_date[1] - '0') * 100 +
+ (pay_date[2] - '0') * 10 + (pay_date[3] - '0');
+
+ struct payroll_cfg cfg;
+ payroll_cfg_load(r, &cfg);
+ char fy_start[16] = "", fy_end[16] = "";
+ int64_t fy_id = 0;
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT id,start_date,end_date FROM fiscal_years"
+ " WHERE org_id=?1 AND start_date<=?2 AND end_date>=?2",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_text(st, 2, pay_date, -1, SQLITE_TRANSIENT);
+ if (sqlite3_step(st) == SQLITE_ROW) {
+ fy_id = sqlite3_column_int64(st, 0);
+ snprintf(fy_start, sizeof fy_start, "%s",
+ sq(sqlite3_column_text(st, 1)));
+ snprintf(fy_end, sizeof fy_end, "%s",
+ sq(sqlite3_column_text(st, 2)));
+ }
+ sqlite3_finalize(st);
+ if (!fy_id)
+ return failf(r, "DATE_OUT_OF_RANGE",
+ "no fiscal year contains %s; open one first", pay_date);
+ if (!payroll_period_in_range(period, fy_start, fy_end))
+ return failf(r, "INVALID_ARGS",
+ "period %s is not in the fiscal year that contains %s",
+ period, pay_date);
+
+ yyjson_mut_val *res = NULL;
+ struct payroll_line *lines = NULL;
+ size_t n = 0;
+ struct ledger_row *vrows = NULL;
+ char (*descs)[256] = NULL;
+ char *voucher_json = NULL;
+ int in_tx = 0;
+
+ if (db_exec(r->db, "BEGIN IMMEDIATE", NULL) != 0)
+ return fail(r, "DB_BUSY", "could not start transaction");
+ in_tx = 1;
+ int exists = payroll_run_exists(r, period);
+ if (exists < 0)
+ goto done;
+ if (exists) {
+ failf(r, "CONFLICT", "a payroll run for period %s already exists",
+ period);
+ goto done;
+ }
+ if (payroll_compute(r, pay_year, &cfg, &lines, &n) != 0)
+ goto done;
+ if (n == 0) {
+ fail(r, "INVALID_ARGS", "no active employees with a monthly salary");
+ goto done;
+ }
+
+ size_t cap = n + 4;
+ vrows = xcalloc(cap, sizeof *vrows);
+ descs = xcalloc(n ? n : 1, 256);
+ size_t vn = 0;
+ int64_t total_gross = 0, total_tax = 0, total_avg = 0, total_net = 0;
+ for (size_t i = 0; i < n; i++) {
+ const struct payroll_line *l = &lines[i];
+ snprintf(descs[i], 256, "Lön %s", l->name);
+ vrows[vn].account = l->salary_account;
+ vrows[vn].debit_ore = l->gross_ore;
+ vrows[vn].description = descs[i];
+ vn++;
+ total_gross += l->gross_ore;
+ total_tax += l->tax_ore;
+ total_avg += l->avgifter_ore;
+ total_net += l->net_ore;
+ }
+ if (total_avg > 0) {
+ vrows[vn].account = cfg.avgift;
+ vrows[vn].debit_ore = total_avg;
+ vrows[vn].description = "Arbetsgivaravgifter";
+ vn++;
+ }
+ if (total_tax > 0) {
+ vrows[vn].account = cfg.tax;
+ vrows[vn].credit_ore = total_tax;
+ vrows[vn].description = "Personalskatt";
+ vn++;
+ }
+ if (total_net > 0) {
+ vrows[vn].account = NET_SALARY_ACCOUNT;
+ vrows[vn].credit_ore = total_net;
+ vrows[vn].description = "Nettolön";
+ vn++;
+ }
+ if (total_avg > 0) {
+ vrows[vn].account = cfg.liability;
+ vrows[vn].credit_ore = total_avg;
+ vrows[vn].description = "Avräkning sociala avgifter";
+ vn++;
+ }
+
+ char description[64];
+ snprintf(description, sizeof description, "Lönekörning %s", period);
+ struct ledger_post_opts o;
+ memset(&o, 0, sizeof o);
+ o.org_id = r->org_id;
+ o.user_id = r->sess->user_id;
+ o.token_id = r->sess->token_id;
+ o.date = pay_date;
+ o.description = description;
+ o.rows = vrows;
+ o.nrows = vn;
+ o.source = "payroll";
+ o.dry_run = r->dry_run;
+ o.already_in_tx = 1;
+ struct ledger_error e;
+ if (ledger_post(r->db, &o, &e, &voucher_json) != 0) {
+ fail(r, e.code ? e.code : "INTERNAL", e.msg);
+ goto done;
+ }
+
+ int64_t run_id = 0, voucher_id = 0;
+ char ts[32];
+ util_iso8601(util_now(), ts, sizeof ts);
+ if (!r->dry_run) {
+ if (!voucher_json) {
+ fail(r, "INTERNAL", "empty voucher result");
+ goto done;
+ }
+ yyjson_doc *vd = yyjson_read(voucher_json, strlen(voucher_json), 0);
+ if (vd) {
+ yyjson_val *idv = yyjson_obj_get(yyjson_doc_get_root(vd), "id");
+ if (idv && yyjson_is_int(idv))
+ voucher_id = yyjson_get_int(idv);
+ yyjson_doc_free(vd);
+ }
+ if (voucher_id <= 0) {
+ fail(r, "INTERNAL", "voucher id missing from the posting");
+ goto done;
+ }
+ if (sqlite3_prepare_v2(
+ r->db,
+ "INSERT INTO payroll_runs(org_id,fiscal_year_id,period,pay_date,"
+ "status,gross_ore,tax_ore,avgifter_ore,net_ore,voucher_id,"
+ "created_at,created_by)"
+ " VALUES(?1,?2,?3,?4,'posted',?5,?6,?7,?8,?9,?10,?11)",
+ -1, &st, NULL) != SQLITE_OK) {
+ db_error(r);
+ goto done;
+ }
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, fy_id);
+ sqlite3_bind_text(st, 3, period, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_text(st, 4, pay_date, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_int64(st, 5, total_gross);
+ sqlite3_bind_int64(st, 6, total_tax);
+ sqlite3_bind_int64(st, 7, total_avg);
+ sqlite3_bind_int64(st, 8, total_net);
+ sqlite3_bind_int64(st, 9, voucher_id);
+ sqlite3_bind_text(st, 10, ts, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_int64(st, 11, r->sess->user_id);
+ int rc = sqlite3_step(st);
+ sqlite3_finalize(st);
+ if (rc != SQLITE_DONE) {
+ if ((rc & 0xff) == SQLITE_CONSTRAINT)
+ failf(r, "CONFLICT",
+ "a payroll run for period %s already exists", period);
+ else
+ db_sqlite_error(r);
+ goto done;
+ }
+ run_id = db_last_id(r->db);
+ for (size_t i = 0; i < n; i++) {
+ if (sqlite3_prepare_v2(
+ r->db,
+ "INSERT INTO payroll_run_lines(org_id,run_id,employee_id,"
+ "gross_ore,tax_ore,avgifter_ore,net_ore,tax_table,"
+ "tax_column) VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9)",
+ -1, &st, NULL) != SQLITE_OK) {
+ db_error(r);
+ goto done;
+ }
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, run_id);
+ sqlite3_bind_int64(st, 3, lines[i].employee_id);
+ sqlite3_bind_int64(st, 4, lines[i].gross_ore);
+ sqlite3_bind_int64(st, 5, lines[i].tax_ore);
+ sqlite3_bind_int64(st, 6, lines[i].avgifter_ore);
+ sqlite3_bind_int64(st, 7, lines[i].net_ore);
+ sqlite3_bind_int(st, 8, lines[i].tax_table);
+ sqlite3_bind_int(st, 9, lines[i].tax_column);
+ rc = sqlite3_step(st);
+ sqlite3_finalize(st);
+ if (rc != SQLITE_DONE) {
+ db_sqlite_error(r);
+ goto done;
+ }
+ }
+ if (sqlite3_exec(r->db, "COMMIT", NULL, NULL, NULL) != SQLITE_OK) {
+ fail(r, "DB_BUSY", "commit failed");
+ goto done;
+ }
+ in_tx = 0;
+ char *reqjson = audit_args_json(r->args);
+ audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+ "payroll.run_post", reqjson, "OK", NULL);
+ free(reqjson);
+ }
+
+ res = payroll_items_json(r, lines, n, &cfg);
+ yyjson_mut_obj_add_strcpy(r->rdoc, res, "period", period);
+ yyjson_mut_obj_add_strcpy(r->rdoc, res, "pay_date", pay_date);
+ yyjson_mut_obj_add_int(r->rdoc, res, "fiscal_year_id", fy_id);
+ yyjson_mut_obj_add_int(r->rdoc, res, "tax_year", pay_year);
+ if (r->dry_run) {
+ yyjson_mut_obj_add_bool(r->rdoc, res, "dry_run", true);
+ } else {
+ yyjson_mut_obj_add_int(r->rdoc, res, "id", run_id);
+ yyjson_mut_obj_add_int(r->rdoc, res, "voucher_id", voucher_id);
+ yyjson_mut_obj_add_strcpy(r->rdoc, res, "status", "posted");
+ }
+
+done:
+ if (in_tx)
+ sqlite3_exec(r->db, "ROLLBACK", NULL, NULL, NULL);
+ free(voucher_json);
+ free(vrows);
+ free(descs);
+ free(lines);
+ return res;
+}
+
+#define RUN_COLUMNS \
+ "id,period,pay_date,status,gross_ore,tax_ore,avgifter_ore,net_ore," \
+ "COALESCE(voucher_id,0),COALESCE(payment_voucher_id,0),created_at"
+
+static yyjson_mut_val *run_json(struct req *r, sqlite3_stmt *st)
+{
+ yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_int(r->rdoc, o, "id", sqlite3_column_int64(st, 0));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "period",
+ sq(sqlite3_column_text(st, 1)));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "pay_date",
+ sq(sqlite3_column_text(st, 2)));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "status",
+ sq(sqlite3_column_text(st, 3)));
+ yyjson_mut_obj_add_int(r->rdoc, o, "gross_ore",
+ sqlite3_column_int64(st, 4));
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_ore", sqlite3_column_int64(st, 5));
+ yyjson_mut_obj_add_int(r->rdoc, o, "avgifter_ore",
+ sqlite3_column_int64(st, 6));
+ yyjson_mut_obj_add_int(r->rdoc, o, "net_ore", sqlite3_column_int64(st, 7));
+ yyjson_mut_obj_add_int(r->rdoc, o, "voucher_id",
+ sqlite3_column_int64(st, 8));
+ yyjson_mut_obj_add_int(r->rdoc, o, "payment_voucher_id",
+ sqlite3_column_int64(st, 9));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
+ sq(sqlite3_column_text(st, 10)));
+ return o;
+}
+
+static yyjson_mut_val *h_payroll_run_list(struct req *r)
+{
+ int64_t limit = 100;
+ arg_int(r->args, "limit", &limit);
+ if (limit < 1)
+ limit = 1;
+ if (limit > 1000)
+ limit = 1000;
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT " RUN_COLUMNS ",(SELECT COUNT(*) FROM payroll_run_lines l"
+ " WHERE l.org_id=payroll_runs.org_id AND l.run_id=payroll_runs.id)"
+ " FROM payroll_runs WHERE org_id=?1"
+ " ORDER BY period DESC, id DESC LIMIT ?2",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, limit);
+ yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
+ while (sqlite3_step(st) == SQLITE_ROW) {
+ yyjson_mut_val *o = run_json(r, st);
+ yyjson_mut_obj_add_int(r->rdoc, o, "line_count",
+ sqlite3_column_int64(st, 11));
+ yyjson_mut_arr_add_val(items, o);
+ }
+ sqlite3_finalize(st);
+ yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
+ return out;
+}
+
+static yyjson_mut_val *h_payroll_run_get(struct req *r)
+{
+ int64_t id = 0;
+ if (!arg_int(r->args, "id", &id) || id <= 0)
+ return fail(r, "INVALID_ARGS", "id is required");
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT " RUN_COLUMNS " FROM payroll_runs"
+ " WHERE org_id=?1 AND id=?2",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, id);
+ if (sqlite3_step(st) != SQLITE_ROW) {
+ sqlite3_finalize(st);
+ return fail(r, "NOT_FOUND", "payroll run not found");
+ }
+ yyjson_mut_val *o = run_json(r, st);
+ sqlite3_finalize(st);
+
+ yyjson_mut_val *lines = yyjson_mut_arr(r->rdoc);
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT l.employee_id,e.name,l.gross_ore,l.tax_ore,l.avgifter_ore,"
+ "l.net_ore,l.tax_table,l.tax_column"
+ " FROM payroll_run_lines l JOIN employees e"
+ " ON e.org_id=l.org_id AND e.id=l.employee_id"
+ " WHERE l.org_id=?1 AND l.run_id=?2 ORDER BY e.name COLLATE NOCASE,"
+ " l.id",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, id);
+ while (sqlite3_step(st) == SQLITE_ROW) {
+ yyjson_mut_val *lo = yyjson_mut_arr_add_obj(r->rdoc, lines);
+ yyjson_mut_obj_add_int(r->rdoc, lo, "employee_id",
+ sqlite3_column_int64(st, 0));
+ yyjson_mut_obj_add_strcpy(r->rdoc, lo, "name",
+ sq(sqlite3_column_text(st, 1)));
+ yyjson_mut_obj_add_int(r->rdoc, lo, "gross_ore",
+ sqlite3_column_int64(st, 2));
+ yyjson_mut_obj_add_int(r->rdoc, lo, "tax_ore",
+ sqlite3_column_int64(st, 3));
+ yyjson_mut_obj_add_int(r->rdoc, lo, "avgifter_ore",
+ sqlite3_column_int64(st, 4));
+ yyjson_mut_obj_add_int(r->rdoc, lo, "net_ore",
+ sqlite3_column_int64(st, 5));
+ yyjson_mut_obj_add_int(r->rdoc, lo, "tax_table",
+ sqlite3_column_int64(st, 6));
+ yyjson_mut_obj_add_int(r->rdoc, lo, "tax_column",
+ sqlite3_column_int64(st, 7));
+ }
+ sqlite3_finalize(st);
+ yyjson_mut_obj_add_val(r->rdoc, o, "lines", lines);
+ return o;
+}
+
+/* ------------------------------------------------------------------ */
+/* AGI underlag */
+/* ------------------------------------------------------------------ */
+
+static yyjson_mut_val *h_payroll_agi(struct req *r)
+{
+ const char *period = arg_str(r->args, "period");
+ int year = 0, month = 0;
+ if (payroll_period_parse(period, &year, &month) != 0)
+ return fail(r, "INVALID_ARGS", "period must be YYYY-MM");
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT id,pay_date FROM payroll_runs"
+ " WHERE org_id=?1 AND period=?2",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_text(st, 2, period, -1, SQLITE_TRANSIENT);
+ int64_t run_id = 0;
+ char pay_date[16] = "";
+ if (sqlite3_step(st) == SQLITE_ROW) {
+ run_id = sqlite3_column_int64(st, 0);
+ snprintf(pay_date, sizeof pay_date, "%s",
+ sq(sqlite3_column_text(st, 1)));
+ }
+ sqlite3_finalize(st);
+ if (!run_id)
+ return failf(r, "NOT_FOUND", "no payroll run for period %s", period);
+
+ if (!secret_available())
+ return fail(r, "INTERNAL", "BOKFD_SECRET_KEY is missing or invalid");
+ yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
+ int64_t total_gross = 0, total_tax = 0, total_avg = 0, total_net = 0;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT l.employee_id,e.name,e.personal_no_enc,l.gross_ore,"
+ "l.tax_ore,l.avgifter_ore,l.net_ore,l.tax_table,l.tax_column"
+ " FROM payroll_run_lines l JOIN employees e"
+ " ON e.org_id=l.org_id AND e.id=l.employee_id"
+ " WHERE l.org_id=?1 AND l.run_id=?2 ORDER BY e.name COLLATE NOCASE,"
+ " l.id",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, run_id);
+ int rc = 0;
+ while (sqlite3_step(st) == SQLITE_ROW) {
+ char *pn = NULL;
+ if (secret_decrypt(sq(sqlite3_column_text(st, 2)), &pn) != 0) {
+ sqlite3_finalize(st);
+ fail(r, "INTERNAL",
+ "could not decrypt a personnummer; check BOKFD_SECRET_KEY");
+ return NULL;
+ }
+ yyjson_mut_val *o = yyjson_mut_arr_add_obj(r->rdoc, items);
+ yyjson_mut_obj_add_int(r->rdoc, o, "employee_id",
+ sqlite3_column_int64(st, 0));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "name",
+ sq(sqlite3_column_text(st, 1)));
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "personal_no", pn);
+ free(pn);
+ int64_t gross = sqlite3_column_int64(st, 3);
+ int64_t tax = sqlite3_column_int64(st, 4);
+ int64_t avg = sqlite3_column_int64(st, 5);
+ int64_t net = sqlite3_column_int64(st, 6);
+ yyjson_mut_obj_add_int(r->rdoc, o, "gross_ore", gross);
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_ore", tax);
+ yyjson_mut_obj_add_int(r->rdoc, o, "avgifter_ore", avg);
+ yyjson_mut_obj_add_int(r->rdoc, o, "net_ore", net);
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_table",
+ sqlite3_column_int64(st, 7));
+ yyjson_mut_obj_add_int(r->rdoc, o, "tax_column",
+ sqlite3_column_int64(st, 8));
+ total_gross += gross;
+ total_tax += tax;
+ total_avg += avg;
+ total_net += net;
+ rc++;
+ }
+ sqlite3_finalize(st);
+ if (rc == 0)
+ return failf(r, "NOT_FOUND", "payroll run %lld has no lines",
+ (long long)run_id);
+ yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_int(r->rdoc, out, "run_id", run_id);
+ yyjson_mut_obj_add_strcpy(r->rdoc, out, "period", period);
+ yyjson_mut_obj_add_strcpy(r->rdoc, out, "pay_date", pay_date);
+ yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
+ yyjson_mut_val *totals = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_int(r->rdoc, totals, "gross_ore", total_gross);
+ yyjson_mut_obj_add_int(r->rdoc, totals, "tax_ore", total_tax);
+ yyjson_mut_obj_add_int(r->rdoc, totals, "avgifter_ore", total_avg);
+ yyjson_mut_obj_add_int(r->rdoc, totals, "net_ore", total_net);
+ yyjson_mut_obj_add_val(r->rdoc, out, "totals", totals);
+ return out;
+}
+
+/* ------------------------------------------------------------------ */
+/* paying the tax and contributions */
+/* ------------------------------------------------------------------ */
+
+static yyjson_mut_val *h_payroll_pay_tax(struct req *r)
+{
+ int64_t run_id = 0;
+ if (!arg_int(r->args, "run_id", &run_id) || run_id <= 0)
+ return fail(r, "INVALID_ARGS", "run_id is required");
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT period,pay_date,status,tax_ore,avgifter_ore,"
+ "COALESCE(payment_voucher_id,0) FROM payroll_runs"
+ " WHERE org_id=?1 AND id=?2",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, run_id);
+ if (sqlite3_step(st) != SQLITE_ROW) {
+ sqlite3_finalize(st);
+ return fail(r, "NOT_FOUND", "payroll run not found");
+ }
+ char period[16], pay_date[16], status[16];
+ snprintf(period, sizeof period, "%s", sq(sqlite3_column_text(st, 0)));
+ snprintf(pay_date, sizeof pay_date, "%s", sq(sqlite3_column_text(st, 1)));
+ snprintf(status, sizeof status, "%s", sq(sqlite3_column_text(st, 2)));
+ int64_t tax = sqlite3_column_int64(st, 3);
+ int64_t avg = sqlite3_column_int64(st, 4);
+ sqlite3_finalize(st);
+ if (strcmp(status, "paid") == 0)
+ return fail(r, "CONFLICT", "payroll run is already paid");
+ int64_t total = tax + avg;
+ if (total <= 0)
+ return fail(r, "INVALID_ARGS", "nothing to pay for this run");
+
+ const char *date = arg_str(r->args, "date");
+ char today[16];
+ if (!date) {
+ time_t t = time(NULL);
+ struct tm tm;
+ gmtime_r(&t, &tm);
+ util_date_fmt(tm.tm_year + 1900, tm.tm_mon + 1, tm.tm_mday, today,
+ sizeof today);
+ date = today;
+ }
+ struct payroll_cfg cfg;
+ payroll_cfg_load(r, &cfg);
+
+ struct ledger_row rows[3];
+ memset(rows, 0, sizeof rows);
+ size_t vn = 0;
+ if (tax > 0) {
+ rows[vn].account = cfg.tax;
+ rows[vn].debit_ore = tax;
+ rows[vn].description = "Personalskatt";
+ vn++;
+ }
+ if (avg > 0) {
+ rows[vn].account = cfg.liability;
+ rows[vn].debit_ore = avg;
+ rows[vn].description = "Avräkning sociala avgifter";
+ vn++;
+ }
+ rows[vn].account = cfg.payment;
+ rows[vn].credit_ore = total;
+ rows[vn].description = "Skattekontot";
+ vn++;
+
+ char description[64];
+ snprintf(description, sizeof description,
+ "Betalning skatt och arbetsgivaravgifter %s", period);
+ char *voucher_json = NULL;
+ struct ledger_error e;
+ yyjson_mut_val *res = NULL;
+ int in_tx = 0;
+ if (db_exec(r->db, "BEGIN IMMEDIATE", NULL) != 0)
+ return fail(r, "DB_BUSY", "could not start transaction");
+ in_tx = 1;
+ struct ledger_post_opts o;
+ memset(&o, 0, sizeof o);
+ o.org_id = r->org_id;
+ o.user_id = r->sess->user_id;
+ o.token_id = r->sess->token_id;
+ o.date = date;
+ o.description = description;
+ o.rows = rows;
+ o.nrows = vn;
+ o.source = "payroll_tax";
+ o.dry_run = r->dry_run;
+ o.already_in_tx = 1;
+ if (ledger_post(r->db, &o, &e, &voucher_json) != 0) {
+ fail(r, e.code ? e.code : "INTERNAL", e.msg);
+ goto done;
+ }
+ int64_t voucher_id = 0;
+ if (!r->dry_run) {
+ if (!voucher_json) {
+ fail(r, "INTERNAL", "empty voucher result");
+ goto done;
+ }
+ yyjson_doc *vd = yyjson_read(voucher_json, strlen(voucher_json), 0);
+ if (vd) {
+ yyjson_val *idv = yyjson_obj_get(yyjson_doc_get_root(vd), "id");
+ if (idv && yyjson_is_int(idv))
+ voucher_id = yyjson_get_int(idv);
+ yyjson_doc_free(vd);
+ }
+ if (voucher_id <= 0) {
+ fail(r, "INTERNAL", "voucher id missing from the posting");
+ goto done;
+ }
+ if (sqlite3_prepare_v2(
+ r->db,
+ "UPDATE payroll_runs SET status='paid',payment_voucher_id=?3"
+ " WHERE org_id=?1 AND id=?2 AND status='posted'",
+ -1, &st, NULL) != SQLITE_OK) {
+ db_error(r);
+ goto done;
+ }
+ sqlite3_bind_int64(st, 1, r->org_id);
+ sqlite3_bind_int64(st, 2, run_id);
+ sqlite3_bind_int64(st, 3, voucher_id);
+ int rc = sqlite3_step(st);
+ sqlite3_finalize(st);
+ if (rc != SQLITE_DONE || sqlite3_changes(r->db) == 0) {
+ fail(r, "CONFLICT", "payroll run is already paid");
+ goto done;
+ }
+ if (sqlite3_exec(r->db, "COMMIT", NULL, NULL, NULL) != SQLITE_OK) {
+ fail(r, "DB_BUSY", "commit failed");
+ goto done;
+ }
+ in_tx = 0;
+ char *reqjson = audit_args_json(r->args);
+ audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+ "payroll.pay_tax", reqjson, "OK", NULL);
+ free(reqjson);
+ }
+ res = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_int(r->rdoc, res, "run_id", run_id);
+ yyjson_mut_obj_add_strcpy(r->rdoc, res, "period", period);
+ yyjson_mut_obj_add_strcpy(r->rdoc, res, "pay_date", pay_date);
+ yyjson_mut_obj_add_strcpy(r->rdoc, res, "date", date);
+ yyjson_mut_obj_add_int(r->rdoc, res, "tax_ore", tax);
+ yyjson_mut_obj_add_int(r->rdoc, res, "avgifter_ore", avg);
+ yyjson_mut_obj_add_int(r->rdoc, res, "total_ore", total);
+ if (r->dry_run) {
+ yyjson_mut_obj_add_bool(r->rdoc, res, "dry_run", true);
+ yyjson_mut_obj_add_strcpy(r->rdoc, res, "status", "posted");
+ yyjson_mut_obj_add_int(r->rdoc, res, "payment_voucher_id", 0);
+ } else {
+ yyjson_mut_obj_add_strcpy(r->rdoc, res, "status", "paid");
+ yyjson_mut_obj_add_int(r->rdoc, res, "payment_voucher_id",
+ voucher_id);
+ }
+
+done:
+ if (in_tx)
+ sqlite3_exec(r->db, "ROLLBACK", NULL, NULL, NULL);
+ free(voucher_json);
+ return res;
+}
+
+/* ------------------------------------------------------------------ */
+/* Skatteverket tax tables */
+/* ------------------------------------------------------------------ */
+
+static yyjson_mut_val *tax_tables_store_and_respond(struct req *r, int year,
+ const unsigned char *data,
+ size_t len,
+ const char *source_url)
+{
+ unsigned char sha[32];
+ util_sha256(data, len, sha);
+ struct tax_row *rows = NULL;
+ size_t n = 0;
+ char *err = NULL;
+ if (tax_table_parse(data, len, &rows, &n, &err) != 0) {
+ yyjson_mut_val *res = failf(r, "INVALID_ARGS", "could not parse: %s",
+ err ? err : "bad table file");
+ free(err);
+ return res;
+ }
+ char ts[32];
+ util_iso8601(util_now(), ts, sizeof ts);
+ if (!r->dry_run) {
+ if (db_exec(r->db, "BEGIN IMMEDIATE", NULL) != 0) {
+ free(rows);
+ return fail(r, "DB_BUSY", "could not start transaction");
+ }
+ if (tax_table_store(r->db, year, rows, n, source_url, sha, ts,
+ &err) != 0) {
+ sqlite3_exec(r->db, "ROLLBACK", NULL, NULL, NULL);
+ yyjson_mut_val *res = failf(r, "INTERNAL", "%s",
+ err ? err : "could not store");
+ free(err);
+ free(rows);
+ return res;
+ }
+ if (sqlite3_exec(r->db, "COMMIT", NULL, NULL, NULL) != SQLITE_OK) {
+ free(rows);
+ return fail(r, "DB_BUSY", "commit failed");
+ }
+ }
+ free(rows);
+ char hex[65];
+ util_hex(sha, 32, hex);
+ yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_int(r->rdoc, o, "year", year);
+ yyjson_mut_obj_add_int(r->rdoc, o, "rows", (int64_t)n);
+ yyjson_mut_obj_add_int(r->rdoc, o, "bytes", (int64_t)len);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "sha256", hex);
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "source_url", source_url);
+ if (r->dry_run)
+ yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+ return o;
+}
+
+static yyjson_mut_val *h_tax_tables_fetch(struct req *r)
+{
+ int64_t requested = 0;
+ arg_int(r->args, "year", &requested);
+ int year = requested ? (int)requested : payroll_current_year();
+ int current = payroll_current_year();
+ if (year < 2000 || year > current)
+ return failf(r, "INVALID_ARGS", "year must be between 2000 and %d",
+ current);
+ unsigned char *data = NULL;
+ size_t len = 0;
+ char *url = NULL;
+ char *err = NULL;
+ if (tax_table_fetch_year(year, &data, &len, &url, &err) != 0) {
+ yyjson_mut_val *res = failf(r, "FETCH_FAILED", "%s",
+ err ? err : "download failed");
+ free(err);
+ return res;
+ }
+ yyjson_mut_val *o = tax_tables_store_and_respond(r, year, data, len, url);
+ free(data);
+ free(url);
+ if (!o || r->dry_run)
+ return o;
+ char *reqjson = audit_args_json(r->args);
+ audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+ "payroll.tax_tables_fetch", reqjson, "OK", NULL);
+ free(reqjson);
+ return o;
+}
+
+static yyjson_mut_val *h_tax_tables_import(struct req *r)
+{
+ int64_t year = 0;
+ const char *b64 = arg_str(r->args, "content_base64");
+ if (!arg_int(r->args, "year", &year) || year < 2000 || year > 9999)
+ return fail(r, "INVALID_ARGS", "year is required");
+ if (!b64)
+ return fail(r, "INVALID_ARGS", "content_base64 is required");
+ unsigned char *data = NULL;
+ size_t len = 0;
+ if (util_b64_decode(b64, strlen(b64), &data, &len) != 0)
+ return fail(r, "INVALID_ARGS", "content_base64 is not valid base64");
+ yyjson_mut_val *o =
+ tax_tables_store_and_respond(r, (int)year, data, len, "import");
+ free(data);
+ if (!o || r->dry_run)
+ return o;
+ char *reqjson = audit_args_json(r->args);
+ audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+ "payroll.tax_tables_import", reqjson, "OK", NULL);
+ free(reqjson);
+ return o;
+}
+
+static yyjson_mut_val *h_tax_tables_status(struct req *r)
+{
+ int current = payroll_current_year();
+ yyjson_mut_val *years = yyjson_mut_arr(r->rdoc);
+ int have_current = 0;
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ r->db, "SELECT DISTINCT in_year FROM tax_tables ORDER BY in_year",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ while (sqlite3_step(st) == SQLITE_ROW) {
+ int64_t y = sqlite3_column_int64(st, 0);
+ yyjson_mut_arr_add_int(r->rdoc, years, y);
+ if (y == current)
+ have_current = 1;
+ }
+ sqlite3_finalize(st);
+
+ char fetched_at[64] = "";
+ char source_url[1024] = "";
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT source_url,fetched_at FROM tax_table_meta"
+ " WHERE in_year=?1",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ sqlite3_bind_int(st, 1, current);
+ if (sqlite3_step(st) == SQLITE_ROW) {
+ snprintf(source_url, sizeof source_url, "%s",
+ sq(sqlite3_column_text(st, 0)));
+ snprintf(fetched_at, sizeof fetched_at, "%s",
+ sq(sqlite3_column_text(st, 1)));
+ }
+ sqlite3_finalize(st);
+ if (!fetched_at[0]) {
+ if (sqlite3_prepare_v2(
+ r->db,
+ "SELECT source_url,fetched_at FROM tax_table_meta"
+ " ORDER BY fetched_at DESC LIMIT 1",
+ -1, &st, NULL) != SQLITE_OK)
+ return db_error(r);
+ if (sqlite3_step(st) == SQLITE_ROW) {
+ snprintf(source_url, sizeof source_url, "%s",
+ sq(sqlite3_column_text(st, 0)));
+ snprintf(fetched_at, sizeof fetched_at, "%s",
+ sq(sqlite3_column_text(st, 1)));
+ }
+ sqlite3_finalize(st);
+ }
+
+ yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+ yyjson_mut_obj_add_val(r->rdoc, o, "stored_years", years);
+ yyjson_mut_obj_add_int(r->rdoc, o, "current_year", current);
+ yyjson_mut_obj_add_bool(r->rdoc, o, "stale", !have_current);
+ if (fetched_at[0])
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "fetched_at", fetched_at);
+ else
+ yyjson_mut_obj_add_null(r->rdoc, o, "fetched_at");
+ if (source_url[0])
+ yyjson_mut_obj_add_strcpy(r->rdoc, o, "source_url", source_url);
+ else
+ yyjson_mut_obj_add_null(r->rdoc, o, "source_url");
+ return o;
+}
+
+/* ------------------------------------------------------------------ */
+/* command table */
+/* ------------------------------------------------------------------ */
+
+static const struct cmd_arg args_tax_tables_fetch[] = {
+ { "year", ARG_INT, 0, NULL, NULL,
+ "Income year; defaults to the current calendar year" },
+};
+
+static const struct cmd_arg args_tax_tables_import[] = {
+ { "year", ARG_INT, 1, NULL, NULL, "Income year of the file" },
+ { "content_base64", ARG_STR, 1, NULL, NULL,
+ "allmanna-tabeller-manad.txt content, base64" },
+};
+
+static const struct cmd_arg args_run_preview[] = {
+ { "period", ARG_STR, 1, NULL, NULL, "Salary period YYYY-MM" },
+};
+
+static const struct cmd_arg args_run_post[] = {
+ { "period", ARG_STR, 1, NULL, NULL, "Salary period YYYY-MM" },
+ { "pay_date", ARG_DATE, 1, NULL, NULL, "Payment date YYYY-MM-DD" },
+};
+
+static const struct cmd_arg args_run_list[] = {
+ { "limit", ARG_INT, 0, "100", NULL, "Maximum number of runs" },
+};
+
+static const struct cmd_arg args_run_get[] = {
+ { "id", ARG_INT, 1, NULL, NULL, "Payroll run id" },
+};
+
+static const struct cmd_arg args_agi[] = {
+ { "period", ARG_STR, 1, NULL, NULL, "Salary period YYYY-MM" },
+};
+
+static const struct cmd_arg args_pay_tax[] = {
+ { "run_id", ARG_INT, 1, NULL, NULL, "Payroll run id" },
+ { "date", ARG_DATE, 0, NULL, NULL,
+ "Payment date; defaults to today" },
+};
+
+static const struct cmd_arg args_settings_set[] = {
+ { "key", ARG_STR, 1, NULL, NULL,
+ "One of the payroll_* settings" },
+ { "value", ARG_STR, 1, NULL, NULL, "Digits only" },
+};
+
+const struct command g_cmd_payroll[] = {
+ { "payroll.tax_tables_fetch", "Download a year's Skatteverket monthly"
+ " tables", PERM_OWNER, 1, 1, 1, h_tax_tables_fetch,
+ CMD_ARGS(args_tax_tables_fetch) },
+ { "payroll.tax_tables_import", "Import a monthly table file offline",
+ PERM_OWNER, 1, 1, 1, h_tax_tables_import,
+ CMD_ARGS(args_tax_tables_import) },
+ { "payroll.tax_tables_status", "Stored tax table years and staleness",
+ PERM_READ, 1, 0, 0, h_tax_tables_status, NULL, 0 },
+ { "payroll.run_preview", "Preview a monthly payroll run", PERM_WRITE, 1,
+ 0, 0, h_payroll_run_preview, CMD_ARGS(args_run_preview) },
+ { "payroll.run_post", "Post the monthly payroll voucher and run",
+ PERM_WRITE, 1, 1, 1, h_payroll_run_post, CMD_ARGS(args_run_post) },
+ { "payroll.run_list", "List payroll runs, newest first", PERM_READ, 1, 0,
+ 0, h_payroll_run_list, CMD_ARGS(args_run_list) },
+ { "payroll.run_get", "Get a payroll run with its lines", PERM_READ, 1, 0,
+ 0, h_payroll_run_get, CMD_ARGS(args_run_get) },
+ { "payroll.agi", "AGI underlag per employee (owner; personnummer in"
+ " clear)", PERM_OWNER, 1, 0, 0, h_payroll_agi, CMD_ARGS(args_agi) },
+ { "payroll.pay_tax", "Pay the run's tax and contributions", PERM_WRITE,
+ 1, 1, 1, h_payroll_pay_tax, CMD_ARGS(args_pay_tax) },
+ { "payroll.settings_get", "Read effective payroll settings", PERM_READ, 1,
+ 0, 0, h_payroll_settings_get, NULL, 0 },
+ { "payroll.settings_set", "Change a payroll setting", PERM_WRITE, 1, 1, 1,
+ h_payroll_settings_set, CMD_ARGS(args_settings_set) },
+};
+
+const struct cmd_table g_cmd_table_payroll = {
+ g_cmd_payroll, sizeof g_cmd_payroll / sizeof g_cmd_payroll[0]
+};
diff --git a/src/commands.c b/src/commands.c
index f30e82a..56e7010 100644
--- a/src/commands.c
+++ b/src/commands.c
@@ -217,6 +217,8 @@ extern const struct cmd_table g_cmd_table_sru;
extern const struct cmd_table g_cmd_table_sie;
extern const struct cmd_table g_cmd_table_bokslut;
extern const struct cmd_table g_cmd_table_bank;
+extern const struct cmd_table g_cmd_table_employees;
+extern const struct cmd_table g_cmd_table_payroll;
const struct cmd_table *const g_command_tables[] = {
&g_cmd_table_auth, &g_cmd_table_org, &g_cmd_table_users,
@@ -225,7 +227,7 @@ const struct cmd_table *const g_command_tables[] = {
&g_cmd_table_settings, &g_cmd_table_bank, &g_cmd_table_rules,
&g_cmd_table_templates, &g_cmd_table_attachments, &g_cmd_table_reports,
&g_cmd_table_sru, &g_cmd_table_sie, &g_cmd_table_customers,
- &g_cmd_table_invoices,
+ &g_cmd_table_invoices, &g_cmd_table_employees, &g_cmd_table_payroll,
};
const size_t g_command_tables_count =
diff --git a/src/db.c b/src/db.c
index 57e1efd..f77d9e5 100644
--- a/src/db.c
+++ b/src/db.c
@@ -145,7 +145,7 @@ static const char SCHEMA_V1[] =
" description TEXT NOT NULL CHECK (length(description) > 0),"
" source TEXT NOT NULL DEFAULT 'manual'"
" CHECK (source IN ('manual','agent','sie_import','system','ib',"
- " 'invoice')),"
+ " 'invoice','payroll','payroll_tax')),"
" client_ref TEXT,"
" corrects_voucher_id INTEGER,"
" created_at TEXT NOT NULL,"
@@ -413,6 +413,92 @@ static const char SCHEMA_V2[] =
"CREATE INDEX idx_template_rows ON voucher_template_rows(org_id,"
" template_id, line_no);\n";
+/* v10: payroll — the employee register, the monthly runs with their lines
+ and Skatteverket's national tax tables. tax_tables and tax_table_meta are
+ reference data, not tenant data, so they carry no org_id. Shared by the
+ fresh schema and the v10 migration. */
+static const char SCHEMA_PAYROLL[] =
+ "CREATE TABLE IF NOT EXISTS employees ("
+ " org_id INTEGER NOT NULL REFERENCES orgs(id),"
+ " id INTEGER PRIMARY KEY,"
+ " name TEXT NOT NULL,"
+ " personal_no_enc TEXT NOT NULL,"
+ " address TEXT NOT NULL DEFAULT '',"
+ " postal_code TEXT NOT NULL DEFAULT '',"
+ " city TEXT NOT NULL DEFAULT '',"
+ " bank_account TEXT NOT NULL DEFAULT '',"
+ " salary_account TEXT NOT NULL DEFAULT '7210',"
+ " monthly_salary_ore INTEGER NOT NULL DEFAULT 0"
+ " CHECK (monthly_salary_ore >= 0),"
+ " tax_table INTEGER NOT NULL DEFAULT 30"
+ " CHECK (tax_table BETWEEN 29 AND 42),"
+ " tax_column INTEGER NOT NULL DEFAULT 1"
+ " CHECK (tax_column BETWEEN 1 AND 6),"
+ " active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),"
+ " created_at TEXT NOT NULL,"
+ " updated_at TEXT,"
+ " UNIQUE (org_id, id)"
+ ") STRICT;\n"
+
+ "CREATE TABLE IF NOT EXISTS payroll_runs ("
+ " org_id INTEGER NOT NULL REFERENCES orgs(id),"
+ " id INTEGER PRIMARY KEY,"
+ " fiscal_year_id INTEGER NOT NULL,"
+ " period TEXT NOT NULL,"
+ " pay_date TEXT NOT NULL,"
+ " status TEXT NOT NULL DEFAULT 'posted'"
+ " CHECK (status IN ('posted','paid')),"
+ " gross_ore INTEGER NOT NULL,"
+ " tax_ore INTEGER NOT NULL,"
+ " avgifter_ore INTEGER NOT NULL,"
+ " net_ore INTEGER NOT NULL,"
+ " voucher_id INTEGER,"
+ " payment_voucher_id INTEGER,"
+ " created_at TEXT NOT NULL,"
+ " created_by INTEGER NOT NULL REFERENCES users(id),"
+ " UNIQUE (org_id, id),"
+ " UNIQUE (org_id, period, pay_date),"
+ " FOREIGN KEY (org_id, fiscal_year_id)"
+ " REFERENCES fiscal_years(org_id, id),"
+ " FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id),"
+ " FOREIGN KEY (org_id, payment_voucher_id) REFERENCES vouchers(org_id, id)"
+ ") STRICT;\n"
+
+ "CREATE TABLE IF NOT EXISTS payroll_run_lines ("
+ " org_id INTEGER NOT NULL,"
+ " id INTEGER PRIMARY KEY,"
+ " run_id INTEGER NOT NULL,"
+ " employee_id INTEGER NOT NULL,"
+ " gross_ore INTEGER NOT NULL,"
+ " tax_ore INTEGER NOT NULL,"
+ " avgifter_ore INTEGER NOT NULL,"
+ " net_ore INTEGER NOT NULL,"
+ " tax_table INTEGER NOT NULL,"
+ " tax_column INTEGER NOT NULL,"
+ " UNIQUE (org_id, id),"
+ " UNIQUE (org_id, run_id, employee_id),"
+ " FOREIGN KEY (org_id, run_id) REFERENCES payroll_runs(org_id, id),"
+ " FOREIGN KEY (org_id, employee_id) REFERENCES employees(org_id, id)"
+ ") STRICT;\n"
+
+ "CREATE TABLE IF NOT EXISTS tax_tables ("
+ " in_year INTEGER NOT NULL,"
+ " table_no INTEGER NOT NULL,"
+ " column_no INTEGER NOT NULL,"
+ " income_from_ore INTEGER NOT NULL,"
+ " income_to_ore INTEGER,"
+ " tax_ore INTEGER NOT NULL,"
+ " pct INTEGER,"
+ " PRIMARY KEY (in_year, table_no, column_no, income_from_ore)"
+ ") STRICT;\n"
+
+ "CREATE TABLE IF NOT EXISTS tax_table_meta ("
+ " in_year INTEGER PRIMARY KEY,"
+ " source_url TEXT NOT NULL,"
+ " sha256 BLOB NOT NULL CHECK (length(sha256) = 32),"
+ " fetched_at TEXT NOT NULL"
+ ") STRICT;\n";
+
static void set_err(char **err, const char *fmt, ...)
__attribute__((format(printf, 2, 3)));
@@ -453,6 +539,10 @@ int db_migrate(sqlite3 *db, char **err)
db_exec(db, "ROLLBACK", NULL);
return -1;
}
+ if (db_exec(db, SCHEMA_PAYROLL, err) != 0) {
+ db_exec(db, "ROLLBACK", NULL);
+ return -1;
+ }
char ts[32];
util_iso8601(util_now(), ts, sizeof ts);
char *sql = sqlite3_mprintf(
@@ -742,6 +832,60 @@ static int db_upgrade_v9(sqlite3 *db, char **err)
err);
}
+/* v10: payroll — the employee register, the monthly runs with their lines
+ and Skatteverket's national tax tables, plus 'payroll'/'payroll_tax' as
+ voucher sources. Widening that CHECK needs the same table rebuild as v9;
+ db_open disables foreign keys for the migration and re-checks them. */
+static int db_upgrade_v10(sqlite3 *db, char **err)
+{
+ if (db_exec(db, SCHEMA_PAYROLL, err) != 0)
+ return -1;
+ return db_exec(
+ db,
+ "CREATE TABLE vouchers_new ("
+ " org_id INTEGER NOT NULL REFERENCES orgs(id),"
+ " id INTEGER PRIMARY KEY,"
+ " fiscal_year_id INTEGER NOT NULL,"
+ " series TEXT NOT NULL,"
+ " number INTEGER NOT NULL CHECK (number > 0),"
+ " date TEXT NOT NULL CHECK (date LIKE '____-__-__'),"
+ " description TEXT NOT NULL CHECK (length(description) > 0),"
+ " source TEXT NOT NULL DEFAULT 'manual'"
+ " CHECK (source IN ('manual','agent','sie_import','system','ib',"
+ " 'invoice','payroll','payroll_tax')),"
+ " client_ref TEXT,"
+ " corrects_voucher_id INTEGER,"
+ " created_at TEXT NOT NULL,"
+ " created_by_user INTEGER NOT NULL REFERENCES users(id),"
+ " created_by_token INTEGER REFERENCES api_tokens(id),"
+ " hash_prev BLOB NOT NULL CHECK (length(hash_prev) = 32),"
+ " hash BLOB NOT NULL CHECK (length(hash) = 32),"
+ " UNIQUE (org_id, id),"
+ " UNIQUE (org_id, fiscal_year_id, series, number),"
+ " UNIQUE (org_id, client_ref),"
+ " FOREIGN KEY (org_id, fiscal_year_id)"
+ " REFERENCES fiscal_years(org_id, id),"
+ " FOREIGN KEY (org_id, corrects_voucher_id)"
+ " REFERENCES vouchers(org_id, id)"
+ ") STRICT;"
+ "INSERT INTO vouchers_new(org_id,id,fiscal_year_id,series,number,date,"
+ " description,source,client_ref,corrects_voucher_id,created_at,"
+ " created_by_user,created_by_token,hash_prev,hash)"
+ " SELECT org_id,id,fiscal_year_id,series,number,date,description,source,"
+ " client_ref,corrects_voucher_id,created_at,created_by_user,"
+ " created_by_token,hash_prev,hash FROM vouchers;"
+ "DROP TABLE vouchers;"
+ "ALTER TABLE vouchers_new RENAME TO vouchers;"
+ "CREATE INDEX idx_vouchers_date ON vouchers(org_id, date);"
+ "CREATE INDEX idx_vouchers_fy"
+ " ON vouchers(org_id, fiscal_year_id, series, number);"
+ "CREATE TRIGGER vouchers_no_update BEFORE UPDATE ON vouchers"
+ " BEGIN SELECT RAISE(ABORT, 'vouchers are append-only'); END;"
+ "CREATE TRIGGER vouchers_no_delete BEFORE DELETE ON vouchers"
+ " BEGIN SELECT RAISE(ABORT, 'vouchers are append-only'); END;",
+ err);
+}
+
static int db_upgrade(sqlite3 *db, int from, char **err)
{
if (db_exec(db, "BEGIN IMMEDIATE", err) != 0)
@@ -778,6 +922,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err)
db_exec(db, "ROLLBACK", NULL);
return -1;
}
+ if (from < 10 && db_upgrade_v10(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);
@@ -925,7 +1073,7 @@ int db_open(const char *path, sqlite3 **out, char **err)
sqlite3_close(db);
return -1;
}
- /* v9 rebuilds the vouchers table; foreign keys are re-checked
+ /* v9/v10 rebuild the vouchers table; foreign keys are re-checked
right after the migration and re-enabled for normal use */
if (db_exec(db, "PRAGMA foreign_keys=OFF", err) != 0) {
sqlite3_close(db);
diff --git a/src/db.h b/src/db.h
index 38fc6dc..d4cca6b 100644
--- a/src/db.h
+++ b/src/db.h
@@ -4,7 +4,7 @@
#include <sqlite3.h>
#include <stdint.h>
-#define BOKF_SCHEMA_VERSION 9
+#define BOKF_SCHEMA_VERSION 10
int db_open(const char *path, sqlite3 **out, char **err);
int db_migrate(sqlite3 *db, char **err);
diff --git a/src/tax_table.c b/src/tax_table.c
new file mode 100644
index 0000000..1cf40c1
--- /dev/null
+++ b/src/tax_table.c
@@ -0,0 +1,745 @@
+#include "tax_table.h"
+
+#include <errno.h>
+#include <limits.h>
+#include <netdb.h>
+#include <stdarg.h>
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+#include <strings.h>
+#include <sys/socket.h>
+#include <sys/time.h>
+#include <time.h>
+#include <unistd.h>
+
+#include <openssl/err.h>
+#include <openssl/ssl.h>
+#include <openssl/x509.h>
+
+#include "db.h"
+#include "version.h"
+
+#define TT_LINE_LEN 49
+#define TT_TIMEOUT_SEC 30
+#define TT_MAX_REDIRECTS 5
+#define TT_TABLE_FILE "allmanna-tabeller-manad.txt"
+#define TT_PAGE_URL \
+ "https://www.skatteverket.se/foretag/arbetsgivare/" \
+ "arbetsgivaravgifterochskatteavdrag/skattetabeller/" \
+ "specialversionerforprogramforetagmfl.4.319dc1451507f2f99e86ee.html"
+
+static void set_err(char **err, const char *fmt, ...)
+ __attribute__((format(printf, 2, 3)));
+
+static void set_err(char **err, const char *fmt, ...)
+{
+ if (!err || *err)
+ return;
+ char buf[512];
+ va_list ap;
+ va_start(ap, fmt);
+ vsnprintf(buf, sizeof buf, fmt, ap);
+ va_end(ap);
+ *err = xstrdup(buf);
+}
+
+/* ------------------------------------------------------------------ */
+/* parser */
+/* ------------------------------------------------------------------ */
+
+static int parse_int_field(const char *s, int len, int64_t *v, int *empty)
+{
+ int i = 0, j = len;
+ while (i < j && s[i] == ' ')
+ i++;
+ while (j > i && s[j - 1] == ' ')
+ j--;
+ *empty = i == j;
+ if (*empty) {
+ *v = 0;
+ return 0;
+ }
+ int64_t x = 0;
+ for (int k = i; k < j; k++) {
+ if (s[k] < '0' || s[k] > '9')
+ return -1;
+ x = x * 10 + (s[k] - '0');
+ }
+ *v = x;
+ return 0;
+}
+
+int tax_table_parse(const unsigned char *data, size_t len,
+ struct tax_row **out, size_t *out_n, char **err)
+{
+ *out = NULL;
+ *out_n = 0;
+ if (len >= 3 && data[0] == 0xef && data[1] == 0xbb && data[2] == 0xbf) {
+ data += 3;
+ len -= 3;
+ }
+ struct tax_row *rows = NULL;
+ size_t n = 0, cap = 0;
+ size_t pos = 0;
+ int line_no = 0;
+ while (pos < len) {
+ const char *line = (const char *)data + pos;
+ size_t end = pos;
+ while (end < len && data[end] != '\n')
+ end++;
+ size_t llen = end - pos;
+ pos = end + 1;
+ line_no++;
+ while (llen > 0 && (line[llen - 1] == '\r' ||
+ line[llen - 1] == ' ' || line[llen - 1] == '\t'))
+ llen--;
+ if (llen == 0)
+ continue;
+ if (llen != TT_LINE_LEN) {
+ set_err(err, "line %d: expected %d characters, got %zu", line_no,
+ TT_LINE_LEN, llen);
+ goto bad;
+ }
+ if (line[0] != '3' || line[1] != '0' ||
+ (line[2] != 'B' && line[2] != '%')) {
+ set_err(err, "line %d: not a 30B/30%% record", line_no);
+ goto bad;
+ }
+ int table_no = (line[3] - '0') * 10 + (line[4] - '0');
+ if (table_no < 29 || table_no > 42) {
+ set_err(err, "line %d: table %d is out of range", line_no,
+ table_no);
+ goto bad;
+ }
+ int is_pct = line[2] == '%';
+ int64_t from_kr = 0, to_kr = 0, col_kr = 0;
+ int empty = 0;
+ if (parse_int_field(line + 5, 7, &from_kr, &empty) != 0 || empty) {
+ set_err(err, "line %d: bad income from", line_no);
+ goto bad;
+ }
+ if (parse_int_field(line + 12, 7, &to_kr, &empty) != 0) {
+ set_err(err, "line %d: bad income to", line_no);
+ goto bad;
+ }
+ int has_to = !empty;
+ if (!has_to && !is_pct) {
+ set_err(err, "line %d: B record needs an income to", line_no);
+ goto bad;
+ }
+ if (has_to && to_kr < from_kr) {
+ set_err(err, "line %d: income range is reversed", line_no);
+ goto bad;
+ }
+ for (int c = 0; c < 6; c++) {
+ if (parse_int_field(line + 19 + 5 * c, 5, &col_kr, &empty) != 0 ||
+ empty) {
+ set_err(err, "line %d: bad column %d", line_no, c + 1);
+ goto bad;
+ }
+ if (n == cap) {
+ cap = cap ? cap * 2 : 1024;
+ rows = xrealloc(rows, cap * sizeof *rows);
+ }
+ struct tax_row *row = &rows[n++];
+ row->table_no = table_no;
+ row->column_no = c + 1;
+ row->income_from_ore = from_kr * 100;
+ row->income_to_ore = has_to ? to_kr * 100 : -1;
+ row->tax_ore = is_pct ? 0 : col_kr * 100;
+ row->pct = is_pct ? col_kr * 100 : 0;
+ row->is_pct = is_pct;
+ }
+ }
+ if (n == 0) {
+ set_err(err, "no table records found");
+ goto bad;
+ }
+ *out = rows;
+ *out_n = n;
+ return 0;
+
+bad:
+ free(rows);
+ return -1;
+}
+
+/* ------------------------------------------------------------------ */
+/* storage */
+/* ------------------------------------------------------------------ */
+
+int tax_table_store(sqlite3 *db, int year, const struct tax_row *rows,
+ size_t n, const char *source_url,
+ const unsigned char sha256[32], const char *fetched_at,
+ char **err)
+{
+ char *del = sqlite3_mprintf("DELETE FROM tax_tables WHERE in_year=%d",
+ year);
+ if (!del) {
+ set_err(err, "out of memory");
+ return -1;
+ }
+ int drc = db_exec(db, del, err);
+ sqlite3_free(del);
+ if (drc != 0)
+ return -1;
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ db,
+ "INSERT INTO tax_tables(in_year,table_no,column_no,income_from_ore,"
+ "income_to_ore,tax_ore,pct)"
+ " VALUES(?1,?2,?3,?4,?5,?6,?7)",
+ -1, &st, NULL) != SQLITE_OK) {
+ set_err(err, "database error: %s", sqlite3_errmsg(db));
+ return -1;
+ }
+ for (size_t i = 0; i < n; i++) {
+ sqlite3_bind_int(st, 1, year);
+ sqlite3_bind_int(st, 2, rows[i].table_no);
+ sqlite3_bind_int(st, 3, rows[i].column_no);
+ sqlite3_bind_int64(st, 4, rows[i].income_from_ore);
+ if (rows[i].income_to_ore < 0)
+ sqlite3_bind_null(st, 5);
+ else
+ sqlite3_bind_int64(st, 5, rows[i].income_to_ore);
+ sqlite3_bind_int64(st, 6, rows[i].tax_ore);
+ if (rows[i].is_pct)
+ sqlite3_bind_int64(st, 7, rows[i].pct);
+ else
+ sqlite3_bind_null(st, 7);
+ int rc = sqlite3_step(st);
+ sqlite3_reset(st);
+ sqlite3_clear_bindings(st);
+ if (rc != SQLITE_DONE) {
+ set_err(err, "could not store row %zu: %s", i + 1,
+ sqlite3_errmsg(db));
+ sqlite3_finalize(st);
+ return -1;
+ }
+ }
+ sqlite3_finalize(st);
+ if (sqlite3_prepare_v2(
+ db,
+ "INSERT INTO tax_table_meta(in_year,source_url,sha256,fetched_at)"
+ " VALUES(?1,?2,?3,?4)"
+ " ON CONFLICT(in_year) DO UPDATE SET"
+ " source_url=excluded.source_url, sha256=excluded.sha256,"
+ " fetched_at=excluded.fetched_at",
+ -1, &st, NULL) != SQLITE_OK) {
+ set_err(err, "database error: %s", sqlite3_errmsg(db));
+ return -1;
+ }
+ sqlite3_bind_int(st, 1, year);
+ sqlite3_bind_text(st, 2, source_url, -1, SQLITE_TRANSIENT);
+ sqlite3_bind_blob(st, 3, sha256, 32, SQLITE_TRANSIENT);
+ sqlite3_bind_text(st, 4, fetched_at, -1, SQLITE_TRANSIENT);
+ int rc = sqlite3_step(st);
+ sqlite3_finalize(st);
+ if (rc != SQLITE_DONE) {
+ set_err(err, "could not store metadata: %s", sqlite3_errmsg(db));
+ return -1;
+ }
+ return 0;
+}
+
+int tax_table_lookup(sqlite3 *db, int year, int table_no, int column_no,
+ int64_t gross_ore, int64_t *tax_ore)
+{
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(
+ db,
+ "SELECT MAX(income_to_ore) FROM tax_tables"
+ " WHERE in_year=?1 AND table_no=?2 AND column_no=?3"
+ " AND pct IS NULL",
+ -1, &st, NULL) != SQLITE_OK)
+ return -1;
+ sqlite3_bind_int(st, 1, year);
+ sqlite3_bind_int(st, 2, table_no);
+ sqlite3_bind_int(st, 3, column_no);
+ int step = sqlite3_step(st);
+ if (step != SQLITE_ROW ||
+ sqlite3_column_type(st, 0) == SQLITE_NULL) {
+ sqlite3_finalize(st);
+ return 2;
+ }
+ int64_t top = sqlite3_column_int64(st, 0);
+ sqlite3_finalize(st);
+ if (gross_ore > top)
+ return 1;
+ if (sqlite3_prepare_v2(
+ db,
+ "SELECT tax_ore FROM tax_tables"
+ " WHERE in_year=?1 AND table_no=?2 AND column_no=?3"
+ " AND pct IS NULL AND income_from_ore<=?4 AND income_to_ore>=?4",
+ -1, &st, NULL) != SQLITE_OK)
+ return -1;
+ sqlite3_bind_int(st, 1, year);
+ sqlite3_bind_int(st, 2, table_no);
+ sqlite3_bind_int(st, 3, column_no);
+ sqlite3_bind_int64(st, 4, gross_ore);
+ step = sqlite3_step(st);
+ if (step != SQLITE_ROW) {
+ sqlite3_finalize(st);
+ return 2;
+ }
+ *tax_ore = sqlite3_column_int64(st, 0);
+ sqlite3_finalize(st);
+ return 0;
+}
+
+/* ------------------------------------------------------------------ */
+/* HTTPS GET */
+/* ------------------------------------------------------------------ */
+
+struct tt_url {
+ char host[256];
+ char path[2048];
+ int port;
+};
+
+static int tt_url_parse(const char *url, struct tt_url *u, char **err)
+{
+ if (strncmp(url, "https://", 8) != 0) {
+ set_err(err, "not an https URL: %.120s", url);
+ return -1;
+ }
+ const char *p = url + 8;
+ const char *slash = strchr(p, '/');
+ const char *host_end = slash ? slash : p + strlen(p);
+ const char *colon = memchr(p, ':', (size_t)(host_end - p));
+ size_t hl = colon ? (size_t)(colon - p) : (size_t)(host_end - p);
+ if (hl == 0 || hl >= sizeof u->host) {
+ set_err(err, "bad host in URL: %.120s", url);
+ return -1;
+ }
+ memcpy(u->host, p, hl);
+ u->host[hl] = '\0';
+ u->port = 443;
+ if (colon) {
+ long port = strtol(colon + 1, NULL, 10);
+ if (port < 1 || port > 65535) {
+ set_err(err, "bad port in URL: %.120s", url);
+ return -1;
+ }
+ u->port = (int)port;
+ }
+ snprintf(u->path, sizeof u->path, "%s", slash ? slash : "/");
+ return 0;
+}
+
+static int tt_tcp_connect(const struct tt_url *u, char **err)
+{
+ char portstr[16];
+ snprintf(portstr, sizeof portstr, "%d", u->port);
+ struct addrinfo hints;
+ memset(&hints, 0, sizeof hints);
+ hints.ai_family = AF_UNSPEC;
+ hints.ai_socktype = SOCK_STREAM;
+ struct addrinfo *res = NULL;
+ int gai = getaddrinfo(u->host, portstr, &hints, &res);
+ if (gai != 0) {
+ set_err(err, "cannot resolve %s: %s", u->host, gai_strerror(gai));
+ return -1;
+ }
+ int last = 0;
+ for (struct addrinfo *ai = res; ai; ai = ai->ai_next) {
+ int fd = socket(ai->ai_family, ai->ai_socktype | SOCK_CLOEXEC,
+ ai->ai_protocol);
+ if (fd < 0) {
+ last = errno;
+ continue;
+ }
+ struct timeval tv;
+ tv.tv_sec = TT_TIMEOUT_SEC;
+ tv.tv_usec = 0;
+ (void)setsockopt(fd, SOL_SOCKET, SO_RCVTIMEO, &tv, sizeof tv);
+ (void)setsockopt(fd, SOL_SOCKET, SO_SNDTIMEO, &tv, sizeof tv);
+ if (connect(fd, ai->ai_addr, ai->ai_addrlen) == 0) {
+ freeaddrinfo(res);
+ return fd;
+ }
+ last = errno;
+ close(fd);
+ }
+ freeaddrinfo(res);
+ set_err(err, "cannot connect to %s:%d: %s", u->host, u->port,
+ last ? strerror(last) : "unknown error");
+ return -1;
+}
+
+static int tt_tls_start(SSL_CTX *ctx, SSL **ssl, int fd, const char *host,
+ char **err)
+{
+ *ssl = SSL_new(ctx);
+ if (!*ssl) {
+ set_err(err, "cannot create TLS connection");
+ return -1;
+ }
+ if (SSL_set_fd(*ssl, fd) != 1 ||
+ SSL_set_tlsext_host_name(*ssl, host) != 1 ||
+ SSL_set1_host(*ssl, host) != 1) {
+ set_err(err, "cannot set up TLS connection");
+ return -1;
+ }
+ if (SSL_connect(*ssl) != 1) {
+ long vr = SSL_get_verify_result(*ssl);
+ unsigned long ec = ERR_get_error();
+ if (vr != X509_V_OK)
+ set_err(err, "TLS certificate verification failed: %s",
+ X509_verify_cert_error_string(vr));
+ else if (ec)
+ set_err(err, "TLS handshake failed: %s",
+ ERR_error_string(ec, NULL));
+ else
+ set_err(err, "TLS handshake failed");
+ return -1;
+ }
+ if (SSL_get_verify_result(*ssl) != X509_V_OK) {
+ set_err(err, "TLS certificate verification failed: %s",
+ X509_verify_cert_error_string(SSL_get_verify_result(*ssl)));
+ return -1;
+ }
+ return 0;
+}
+
+static int tt_write(SSL *ssl, const void *data, size_t n, char **err)
+{
+ const unsigned char *p = data;
+ while (n > 0) {
+ int chunk = n > (size_t)INT_MAX ? INT_MAX : (int)n;
+ int w = SSL_write(ssl, p, chunk);
+ if (w <= 0) {
+ int e = SSL_get_error(ssl, w);
+ if (e == SSL_ERROR_WANT_READ || e == SSL_ERROR_WANT_WRITE)
+ continue;
+ set_err(err, "TLS write failed");
+ return -1;
+ }
+ p += w;
+ n -= (size_t)w;
+ }
+ return 0;
+}
+
+static int tt_read_all(SSL *ssl, struct buf *out, char **err)
+{
+ for (;;) {
+ unsigned char chunk[65536];
+ int r = SSL_read(ssl, chunk, (int)sizeof chunk);
+ if (r > 0) {
+ buf_append(out, chunk, (size_t)r);
+ continue;
+ }
+ int e = SSL_get_error(ssl, r);
+ if (e == SSL_ERROR_ZERO_RETURN)
+ return 0;
+ if (e == SSL_ERROR_WANT_READ || e == SSL_ERROR_WANT_WRITE)
+ continue;
+ if (e == SSL_ERROR_SYSCALL && r == 0)
+ return 0;
+#ifdef SSL_R_UNEXPECTED_EOF_WHILE_READING
+ if (e == SSL_ERROR_SSL) {
+ unsigned long ec = ERR_peek_last_error();
+ if (ec &&
+ ERR_GET_REASON(ec) == SSL_R_UNEXPECTED_EOF_WHILE_READING)
+ return 0;
+ }
+#endif
+ if (e == SSL_ERROR_SYSCALL &&
+ (errno == EAGAIN || errno == EWOULDBLOCK)) {
+ set_err(err, "receive timed out");
+ return -1;
+ }
+ unsigned long ec = ERR_get_error();
+ if (ec)
+ set_err(err, "TLS read failed: %s", ERR_error_string(ec, NULL));
+ else
+ set_err(err, "TLS read failed");
+ return -1;
+ }
+}
+
+/* One GET with Connection: close; returns status, Location (or NULL) and
+ the response body. */
+static int tt_get_once(const struct tt_url *u, struct buf *body, int *status,
+ char **location, char **err)
+{
+ int fd = tt_tcp_connect(u, err);
+ if (fd < 0)
+ return -1;
+ SSL_CTX *ctx = SSL_CTX_new(TLS_client_method());
+ SSL *ssl = NULL;
+ int rc = -1;
+ if (!ctx) {
+ set_err(err, "cannot create TLS context");
+ goto done;
+ }
+ SSL_CTX_set_min_proto_version(ctx, TLS1_2_VERSION);
+ SSL_CTX_set_options(ctx, SSL_OP_NO_COMPRESSION | SSL_OP_NO_RENEGOTIATION);
+ SSL_CTX_set_verify(ctx, SSL_VERIFY_PEER, NULL);
+ if (SSL_CTX_set_default_verify_paths(ctx) != 1) {
+ set_err(err, "cannot load system CA certificates");
+ goto done;
+ }
+ if (tt_tls_start(ctx, &ssl, fd, u->host, err) != 0)
+ goto done;
+
+ struct buf req;
+ buf_init(&req);
+ static const char head_start[] = "GET ";
+ static const char head_mid[] = " HTTP/1.1\r\nHost: ";
+ static const char head_end[] = "\r\nUser-Agent: bokf/" BOKF_VERSION
+ "\r\nAccept: */*\r\nConnection: close\r\n\r\n";
+ buf_append(&req, head_start, sizeof head_start - 1);
+ buf_append(&req, u->path, strlen(u->path));
+ buf_append(&req, head_mid, sizeof head_mid - 1);
+ buf_append(&req, u->host, strlen(u->host));
+ buf_append(&req, head_end, sizeof head_end - 1);
+ int ok = tt_write(ssl, req.p, req.len, err);
+ buf_free(&req);
+ if (ok != 0)
+ goto done;
+
+ struct buf raw;
+ buf_init(&raw);
+ if (tt_read_all(ssl, &raw, err) != 0) {
+ buf_free(&raw);
+ goto done;
+ }
+ size_t hdr_end = 0;
+ int found = 0;
+ for (size_t i = 0; i + 3 < raw.len; i++) {
+ if (raw.p[i] == '\r' && raw.p[i + 1] == '\n' &&
+ raw.p[i + 2] == '\r' && raw.p[i + 3] == '\n') {
+ hdr_end = i + 4;
+ found = 1;
+ break;
+ }
+ }
+ if (!found) {
+ set_err(err, "malformed HTTP response");
+ buf_free(&raw);
+ goto done;
+ }
+ *status = 0;
+ if (raw.len >= 12 && memcmp(raw.p, "HTTP/", 5) == 0)
+ *status = atoi((const char *)raw.p + 9);
+ char line[1024];
+ size_t p = 0;
+ int first = 1;
+ while (p < hdr_end) {
+ size_t e = p;
+ while (e < hdr_end && raw.p[e] != '\n')
+ e++;
+ size_t n = e - p;
+ if (n > 0 && raw.p[e - 1] == '\r')
+ n--;
+ if (n >= sizeof line)
+ n = sizeof line - 1;
+ memcpy(line, raw.p + p, n);
+ line[n] = '\0';
+ if (!first && n >= 9 && strncasecmp(line, "location:", 9) == 0) {
+ const char *v = line + 9;
+ while (*v == ' ' || *v == '\t')
+ v++;
+ free(*location);
+ *location = xstrdup(v);
+ }
+ first = 0;
+ p = e + 1;
+ }
+ for (size_t i = hdr_end; i < raw.len; i++)
+ buf_append(body, raw.p + i, 1);
+ buf_free(&raw);
+ rc = 0;
+
+done:
+ if (ssl) {
+ SSL_shutdown(ssl);
+ SSL_free(ssl);
+ }
+ if (ctx)
+ SSL_CTX_free(ctx);
+ close(fd);
+ return rc;
+}
+
+static int tt_is_absolute(const char *u)
+{
+ return strncmp(u, "https://", 8) == 0 ||
+ strncmp(u, "http://", 7) == 0;
+}
+
+static char *tt_resolve(const char *base, const char *location, char **err)
+{
+ if (tt_is_absolute(location))
+ return xstrdup(location);
+ struct tt_url u;
+ if (tt_url_parse(base, &u, err) != 0)
+ return NULL;
+ char out[2560];
+ if (location[0] == '/') {
+ if (u.port == 443)
+ snprintf(out, sizeof out, "https://%s%s", u.host, location);
+ else
+ snprintf(out, sizeof out, "https://%s:%d%s", u.host, u.port,
+ location);
+ return xstrdup(out);
+ }
+ const char *last = strrchr(u.path, '/');
+ size_t dir = last ? (size_t)(last - u.path + 1) : 0;
+ char path[2048];
+ if (dir >= sizeof path) {
+ set_err(err, "redirect path is too long");
+ return NULL;
+ }
+ memcpy(path, u.path, dir);
+ snprintf(path + dir, sizeof path - dir, "%s", location);
+ if (u.port == 443)
+ snprintf(out, sizeof out, "https://%s%s", u.host, path);
+ else
+ snprintf(out, sizeof out, "https://%s:%d%s", u.host, u.port, path);
+ return xstrdup(out);
+}
+
+int tax_table_https_get(const char *url, struct buf *out, char **err)
+{
+ char *current = xstrdup(url);
+ for (int hop = 0; hop <= TT_MAX_REDIRECTS; hop++) {
+ struct tt_url u;
+ if (tt_url_parse(current, &u, err) != 0) {
+ free(current);
+ return -1;
+ }
+ struct buf body;
+ buf_init(&body);
+ int status = 0;
+ char *location = NULL;
+ int rc = tt_get_once(&u, &body, &status, &location, err);
+ if (rc != 0) {
+ free(location);
+ buf_free(&body);
+ free(current);
+ return -1;
+ }
+ if (status >= 300 && status < 400 && location && *location) {
+ char *next = tt_resolve(current, location, err);
+ free(location);
+ buf_free(&body);
+ free(current);
+ if (!next)
+ return -1;
+ current = next;
+ continue;
+ }
+ if (status != 200) {
+ set_err(err, "HTTP %d for %.160s", status, current);
+ free(location);
+ buf_free(&body);
+ free(current);
+ return -1;
+ }
+ free(location);
+ free(current);
+ *out = body;
+ return 0;
+ }
+ set_err(err, "too many redirects for %.160s", url);
+ free(current);
+ return -1;
+}
+
+/* ------------------------------------------------------------------ */
+/* Skatteverket page and download */
+/* ------------------------------------------------------------------ */
+
+static char *find_link(const char *html, size_t len, const char *suffix,
+ int index)
+{
+ size_t slen = strlen(suffix);
+ int seen = 0;
+ size_t i = 0;
+ while (i + 6 < len) {
+ if (strncasecmp(html + i, "href=", 5) != 0) {
+ i++;
+ continue;
+ }
+ char quote = html[i + 5];
+ if (quote != '"' && quote != '\'') {
+ i += 5;
+ continue;
+ }
+ size_t start = i + 6;
+ size_t end = start;
+ while (end < len && html[end] != quote)
+ end++;
+ size_t hlen = end - start;
+ if (hlen >= slen &&
+ strncmp(html + end - slen, suffix, slen) == 0) {
+ if (seen == index) {
+ char *href = xmalloc(hlen + 1);
+ memcpy(href, html + start, hlen);
+ href[hlen] = '\0';
+ char *amp = href;
+ while ((amp = strstr(amp, "&amp;")) != NULL) {
+ *amp = '&';
+ memmove(amp + 1, amp + 5, strlen(amp + 5) + 1);
+ }
+ return href;
+ }
+ seen++;
+ }
+ i = end + 1;
+ }
+ return NULL;
+}
+
+int tax_table_fetch_year(int year, unsigned char **data, size_t *len,
+ char **source_url, char **err)
+{
+ *data = NULL;
+ *len = 0;
+ *source_url = NULL;
+ time_t now = time(NULL);
+ struct tm tm;
+ gmtime_r(&now, &tm);
+ int current_year = tm.tm_year + 1900;
+ if (year < 2000 || year > current_year) {
+ set_err(err, "no published table for year %d", year);
+ return -1;
+ }
+
+ struct buf page;
+ buf_init(&page);
+ if (tax_table_https_get(TT_PAGE_URL, &page, err) != 0) {
+ buf_free(&page);
+ return -1;
+ }
+ size_t page_len = page.len;
+ buf_append(&page, "", 1);
+ char *href = find_link((const char *)page.p, page_len, TT_TABLE_FILE,
+ current_year - year);
+ buf_free(&page);
+ if (!href) {
+ set_err(err, "no %s link for %d on Skatteverket's page", TT_TABLE_FILE,
+ year);
+ return -1;
+ }
+ char *abs = tt_resolve(TT_PAGE_URL, href, err);
+ free(href);
+ if (!abs)
+ return -1;
+
+ struct buf file;
+ buf_init(&file);
+ if (tax_table_https_get(abs, &file, err) != 0) {
+ buf_free(&file);
+ free(abs);
+ return -1;
+ }
+ *data = file.p;
+ *len = file.len;
+ *source_url = abs;
+ return 0;
+}
diff --git a/src/tax_table.h b/src/tax_table.h
new file mode 100644
index 0000000..31683ac
--- /dev/null
+++ b/src/tax_table.h
@@ -0,0 +1,52 @@
+#ifndef BOKF_TAX_TABLE_H
+#define BOKF_TAX_TABLE_H
+
+#include <sqlite3.h>
+#include <stddef.h>
+#include <stdint.h>
+
+#include "util.h"
+
+/* One record from Skatteverket's fixed-width allmanna-tabeller-manad.txt.
+ Income and tax are öre; % records carry percent x100 in pct with
+ tax_ore 0, B records carry the whole-krona tax x100 in tax_ore. */
+struct tax_row {
+ int table_no;
+ int column_no;
+ int64_t income_from_ore;
+ int64_t income_to_ore; /* < 0 = open-ended top range */
+ int64_t tax_ore;
+ int64_t pct;
+ int is_pct;
+};
+
+/* Parses the fixed-width file (UTF-8 BOM tolerated); skips blank lines.
+ Returns 0 with a malloc'd rows array, -1 with a message otherwise. */
+int tax_table_parse(const unsigned char *data, size_t len,
+ struct tax_row **out, size_t *out_n, char **err);
+
+/* Replaces year's rows and meta. The caller owns the transaction: on failure
+ the caller rolls the whole thing back. */
+int tax_table_store(sqlite3 *db, int year, const struct tax_row *rows,
+ size_t n, const char *source_url,
+ const unsigned char sha256[32], const char *fetched_at,
+ char **err);
+
+/* Looks up the B range containing gross_ore. 0 = found; 1 = gross is above
+ the tabulated B range; 2 = no B range contains it (or none stored);
+ -1 = database error. % rows exist in the table but wave 1 refuses to
+ guess how Skatteverket applies them. */
+int tax_table_lookup(sqlite3 *db, int year, int table_no, int column_no,
+ int64_t gross_ore, int64_t *tax_ore);
+
+/* HTTPS GET with the system trust store, following up to five redirects.
+ Returns 0 and the body, -1 with a message. */
+int tax_table_https_get(const char *url, struct buf *out, char **err);
+
+/* Downloads the official monthly table for year: finds the year's link on
+ Skatteverket's page in document order (current year first), downloads it
+ and returns the bytes and the absolute source URL. */
+int tax_table_fetch_year(int year, unsigned char **data, size_t *len,
+ char **source_url, char **err);
+
+#endif
diff --git a/tests/test_core.c b/tests/test_core.c
index 96361cb..945c308 100644
--- a/tests/test_core.c
+++ b/tests/test_core.c
@@ -20,6 +20,7 @@
#include "protocol.h"
#include "secret.h"
#include "sessions.h"
+#include "tax_table.h"
#include "util.h"
#include "yyjson.h"
@@ -703,6 +704,26 @@ static void test_pre_migration_snapshot(struct tctx *ctx)
" AND name='bank_matches'");
CHECK(v && strcmp(v, "bank_matches") == 0);
free(v);
+ v = db_text(db, "SELECT name FROM sqlite_master WHERE type='table'"
+ " AND name='employees'");
+ CHECK(v && strcmp(v, "employees") == 0);
+ free(v);
+ v = db_text(db, "SELECT name FROM sqlite_master WHERE type='table'"
+ " AND name='payroll_runs'");
+ CHECK(v && strcmp(v, "payroll_runs") == 0);
+ free(v);
+ v = db_text(db, "SELECT name FROM sqlite_master WHERE type='table'"
+ " AND name='payroll_run_lines'");
+ CHECK(v && strcmp(v, "payroll_run_lines") == 0);
+ free(v);
+ v = db_text(db, "SELECT name FROM sqlite_master WHERE type='table'"
+ " AND name='tax_tables'");
+ CHECK(v && strcmp(v, "tax_tables") == 0);
+ free(v);
+ v = db_text(db, "SELECT name FROM sqlite_master WHERE type='table'"
+ " AND name='tax_table_meta'");
+ CHECK(v && strcmp(v, "tax_table_meta") == 0);
+ free(v);
int64_t user_id = 0;
CHECK(db_create_user(db, "migrated", "Migrated", "secret123", 0, &user_id,
&err) == 0);
@@ -4130,6 +4151,600 @@ static void test_rate_limit(struct tctx *t)
yyjson_doc_free(d);
}
+static void test_employees(struct tctx *t)
+{
+ static const char hexkey[] =
+ "000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f";
+ yyjson_doc *d;
+
+ /* without a key the personnummer cannot be encrypted */
+ unsetenv("BOKFD_SECRET_KEY");
+ d = call(reqf("{\"v\":1,\"id\":\"e1\",\"cmd\":\"employee.create\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"name\":\"Ingen\","
+ "\"personal_no\":\"19700101-1234\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "INTERNAL");
+ yyjson_doc_free(d);
+ setenv("BOKFD_SECRET_KEY", hexkey, 1);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e2\",\"cmd\":\"employee.create\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"name\":\"Fel\","
+ "\"personal_no\":\"1970010-1234\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "INVALID_ARGS");
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e3\",\"cmd\":\"employee.create\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"name\":\"Anna Testsson\",\"personal_no\":"
+ "\"19700101-1234\",\"address\":\"Storgatan 1\","
+ "\"postal_code\":\"111 22\",\"city\":\"Stockholm\","
+ "\"bank_account\":\"5001-1234\",\"monthly_salary_ore\":"
+ "2500000,\"tax_table\":30,\"tax_column\":1}}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ int64_t emp = jint(d, "result.id");
+ CHECK(emp > 0);
+ CHECK_STR(d, "result.personal_no", "********-1234");
+ CHECK_STR(d, "result.salary_account", "7210");
+ CHECK(jint(d, "result.tax_table") == 30);
+ CHECK(jint(d, "result.tax_column") == 1);
+ yyjson_doc_free(d);
+
+ /* the register holds the AES-GCM envelope, never the number */
+ sqlite3_stmt *st = NULL;
+ CHECK(sqlite3_prepare_v2(g_db, "SELECT personal_no_enc FROM employees"
+ " WHERE org_id=?1 AND id=?2",
+ -1, &st, NULL) == SQLITE_OK);
+ sqlite3_bind_int64(st, 1, t->org_id);
+ sqlite3_bind_int64(st, 2, emp);
+ const char *enc = NULL;
+ if (sqlite3_step(st) == SQLITE_ROW)
+ enc = (const char *)sqlite3_column_text(st, 0);
+ CHECK(enc && strncmp(enc, "enc:v1:", 7) == 0);
+ CHECK(enc && strstr(enc, "19700101") == NULL);
+ sqlite3_finalize(st);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e4\",\"cmd\":\"employee.list\","
+ "\"session\":\"%s\",\"org\":%d}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ CHECK(yyjson_arr_size(jget(d, "result.items")) == 1);
+ CHECK_STR(d, "result.items.0.personal_no", "********-1234");
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e5\",\"cmd\":\"employee.get\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld}}",
+ g_session, (int)t->org_id, (long long)emp));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.name", "Anna Testsson");
+ CHECK_STR(d, "result.city", "Stockholm");
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e6\",\"cmd\":\"employee.update\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld,"
+ "\"city\":\"Göteborg\",\"monthly_salary_ore\":2600000}}",
+ g_session, (int)t->org_id, (long long)emp));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.city", "Göteborg");
+ CHECK(jint(d, "result.monthly_salary_ore") == 2600000);
+ CHECK_STR(d, "result.personal_no", "********-1234");
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e7\",\"cmd\":\"employee.update\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld}}",
+ g_session, (int)t->org_id, (long long)emp));
+ CHECK_STR(d, "error.code", "INVALID_ARGS");
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e8\",\"cmd\":\"employee.update\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":999999,"
+ "\"city\":\"X\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "NOT_FOUND");
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e9\",\"cmd\":\"employee.update\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld,"
+ "\"tax_table\":28}}",
+ g_session, (int)t->org_id, (long long)emp));
+ CHECK_STR(d, "error.code", "INVALID_ARGS");
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e10\",\"cmd\":\"employee.archive\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld,"
+ "\"active\":false}}",
+ g_session, (int)t->org_id, (long long)emp));
+ CHECK_OK(d);
+ CHECK(!jbool(d, "result.active"));
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e11\",\"cmd\":\"employee.list\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"active_only\":true}}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ CHECK(yyjson_arr_size(jget(d, "result.items")) == 0);
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"e12\",\"cmd\":\"employee.archive\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld,"
+ "\"active\":true}}",
+ g_session, (int)t->org_id, (long long)emp));
+ CHECK_OK(d);
+ CHECK(jbool(d, "result.active"));
+ yyjson_doc_free(d);
+
+ /* reads degrade to a fully masked number when the key is gone */
+ unsetenv("BOKFD_SECRET_KEY");
+ d = call(reqf("{\"v\":1,\"id\":\"e13\",\"cmd\":\"employee.get\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld}}",
+ g_session, (int)t->org_id, (long long)emp));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.personal_no", "********");
+ yyjson_doc_free(d);
+ setenv("BOKFD_SECRET_KEY", hexkey, 1);
+
+ /* leave the register quiet for the payroll tests */
+ d = call(reqf("{\"v\":1,\"id\":\"e14\",\"cmd\":\"employee.archive\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld,"
+ "\"active\":false}}",
+ g_session, (int)t->org_id, (long long)emp));
+ CHECK_OK(d);
+ yyjson_doc_free(d);
+}
+
+static void test_tax_tables(struct tctx *t)
+{
+ yyjson_doc *d;
+
+ /* nothing stored yet: the current year is stale */
+ d = call(reqf("{\"v\":1,\"id\":\"t1\",\"cmd\":"
+ "\"payroll.tax_tables_status\",\"session\":\"%s\","
+ "\"org\":%d}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ CHECK(jbool(d, "result.stale"));
+ CHECK(yyjson_arr_size(jget(d, "result.stored_years")) == 0);
+ CHECK(!jstr(d, "result.fetched_at"));
+ yyjson_doc_free(d);
+
+ /* real-looking fixed-width fixture: BOM, four B records (the values are
+ from the 2026 file for table 30 column 1) and two % records with an
+ open-ended top range */
+ static const char fixture[] =
+ "\xEF\xBB\xBF"
+ "30B30" " 1" " 2000" " 0" " 0" " 0" " 0" " 0"
+ " 0" "\n"
+ "30B30" " 24801" " 25000" " 4208" " 4108" " 2006" " 4208" " 6623"
+ " 6623" "\n"
+ "30B30" " 25001" " 25200" " 4254" " 4155" " 2025" " 4254" " 6688"
+ " 6688" "\n"
+ "30B30" " 29801" " 30000" " 5352" " 5225" " 2400" " 5352" " 8273"
+ " 8273" "\n"
+ "30%30" " 80001" " 99999" " 31" " 31" " 27" " 27" " 36"
+ " 36" "\n"
+ "30%30" " 100000" " " " 48" " 48" " 48" " 41" " 48"
+ " 48" "\n";
+
+ struct tax_row *rows = NULL;
+ size_t n = 0;
+ char *err = NULL;
+ CHECK(tax_table_parse((const unsigned char *)fixture, strlen(fixture),
+ &rows, &n, &err) == 0);
+ CHECK(n == 36);
+ CHECK(rows[0].table_no == 30);
+ CHECK(rows[0].column_no == 1);
+ CHECK(rows[0].income_from_ore == 100);
+ CHECK(rows[0].income_to_ore == 200000);
+ CHECK(rows[0].tax_ore == 0);
+ CHECK(!rows[0].is_pct);
+ CHECK(rows[6].column_no == 1);
+ CHECK(rows[6].tax_ore == 420800);
+ CHECK(rows[7].column_no == 2);
+ CHECK(rows[7].tax_ore == 410800);
+ CHECK(rows[12].income_from_ore == 2500100);
+ CHECK(rows[12].tax_ore == 425400);
+ CHECK(rows[24].is_pct);
+ CHECK(rows[24].pct == 3100);
+ CHECK(rows[24].tax_ore == 0);
+ CHECK(rows[24].income_to_ore == 9999900);
+ CHECK(rows[30].is_pct);
+ CHECK(rows[30].income_from_ore == 10000000);
+ CHECK(rows[30].income_to_ore == -1);
+ CHECK(rows[35].pct == 4800);
+ free(rows);
+
+ rows = NULL;
+ err = NULL;
+ CHECK(tax_table_parse((const unsigned char *)"30B30 1 2000\n", 14, &rows,
+ &n, &err) != 0);
+ CHECK(rows == NULL);
+ free(err);
+ err = NULL;
+ static const char bad_table[] =
+ "30B28" " 1" " 2000" " 0" " 0" " 0" " 0" " 0"
+ " 0" "\n";
+ CHECK(tax_table_parse((const unsigned char *)bad_table,
+ strlen(bad_table), &rows, &n, &err) != 0);
+ CHECK(rows == NULL);
+ free(err);
+
+ /* offline import replaces the year in one transaction */
+ char *b64 = util_b64((const unsigned char *)fixture, strlen(fixture));
+ d = call(reqf("{\"v\":1,\"id\":\"t2\",\"cmd\":"
+ "\"payroll.tax_tables_import\",\"session\":\"%s\","
+ "\"org\":%d,\"args\":{\"year\":2026,"
+ "\"content_base64\":\"%s\"}}",
+ g_session, (int)t->org_id, b64));
+ CHECK_OK(d);
+ CHECK(jint(d, "result.rows") == 36);
+ CHECK(jint(d, "result.year") == 2026);
+ CHECK_STR(d, "result.source_url", "import");
+ yyjson_doc_free(d);
+ free(b64);
+
+ CHECK(db_count(g_db, "SELECT count(*) FROM tax_tables WHERE in_year=2026")
+ == 36);
+ CHECK(db_count(g_db, "SELECT count(*) FROM tax_tables WHERE in_year=2026"
+ " AND pct=4800 AND income_to_ore IS NULL") == 5);
+ CHECK(db_count(g_db, "SELECT count(*) FROM tax_tables WHERE in_year=2026"
+ " AND pct=4100 AND income_to_ore IS NULL") == 1);
+
+ /* lookup: exact boundaries and the 80,000 kr top-range limit */
+ int64_t tax = -1;
+ CHECK(tax_table_lookup(g_db, 2026, 30, 1, 2500000, &tax) == 0);
+ CHECK(tax == 420800);
+ CHECK(tax_table_lookup(g_db, 2026, 30, 1, 2499999, &tax) == 0);
+ CHECK(tax == 420800);
+ CHECK(tax_table_lookup(g_db, 2026, 30, 1, 2500100, &tax) == 0);
+ CHECK(tax == 425400);
+ CHECK(tax_table_lookup(g_db, 2026, 30, 1, 8000000, &tax) == 1);
+ CHECK(tax_table_lookup(g_db, 2025, 30, 1, 2500000, &tax) == 2);
+
+ d = call(reqf("{\"v\":1,\"id\":\"t3\",\"cmd\":"
+ "\"payroll.tax_tables_status\",\"session\":\"%s\","
+ "\"org\":%d}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ yyjson_val *years = jget(d, "result.stored_years");
+ int found = 0;
+ for (size_t i = 0; i < yyjson_arr_size(years); i++)
+ if (yyjson_get_int(yyjson_arr_get(years, i)) == 2026)
+ found = 1;
+ CHECK(found);
+ CHECK_STR(d, "result.source_url", "import");
+ CHECK(jstr(d, "result.fetched_at") && *jstr(d, "result.fetched_at"));
+ yyjson_doc_free(d);
+
+ /* a dry run with garbage changes nothing */
+ char *junk = util_b64((const unsigned char *)"nonsense", 8);
+ d = call(reqf("{\"v\":1,\"id\":\"t4\",\"cmd\":"
+ "\"payroll.tax_tables_import\",\"session\":\"%s\","
+ "\"org\":%d,\"dry_run\":true,\"args\":{\"year\":2027,"
+ "\"content_base64\":\"%s\"}}",
+ g_session, (int)t->org_id, junk));
+ CHECK_STR(d, "error.code", "INVALID_ARGS");
+ yyjson_doc_free(d);
+ free(junk);
+ CHECK(db_count(g_db, "SELECT count(*) FROM tax_tables WHERE in_year=2027")
+ == 0);
+}
+
+static void test_payroll(struct tctx *t)
+{
+ static const char hexkey[] =
+ "000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f";
+ setenv("BOKFD_SECRET_KEY", hexkey, 1);
+ yyjson_doc *d;
+
+ /* payroll settings: effective defaults and digits-only validation */
+ d = call(reqf("{\"v\":1,\"id\":\"p1\",\"cmd\":\"payroll.settings_get\","
+ "\"session\":\"%s\",\"org\":%d}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.payroll_salary_account", "7210");
+ CHECK_STR(d, "result.payroll_tax_account", "2710");
+ CHECK(jint(d, "result.payroll_avgift_rate_bp") == 3142);
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"p2\",\"cmd\":\"payroll.settings_set\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"key\":\"payroll_tax_account\",\"value\":\"27A0\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "INVALID_ARGS");
+ yyjson_doc_free(d);
+ d = call(reqf("{\"v\":1,\"id\":\"p3\",\"cmd\":\"payroll.settings_set\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"key\":\"payroll_avgift_rate_bp\",\"value\":\"99999\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "INVALID_ARGS");
+ yyjson_doc_free(d);
+ d = call(reqf("{\"v\":1,\"id\":\"p4\",\"cmd\":\"payroll.settings_set\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"key\":\"payroll_bogus\",\"value\":\"1\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "UNSUPPORTED");
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"p5\",\"cmd\":\"payroll.settings_set\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"key\":\"payroll_salary_account\",\"value\":\"7010\"}}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.value", "7010");
+ yyjson_doc_free(d);
+
+ /* two employees; the salary account follows the setting */
+ d = call(reqf("{\"v\":1,\"id\":\"p6\",\"cmd\":\"employee.create\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"name\":\"Anna Anställd\",\"personal_no\":"
+ "\"19750505-1111\",\"monthly_salary_ore\":2500000,"
+ "\"tax_table\":30,\"tax_column\":1}}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.salary_account", "7010");
+ int64_t anna = jint(d, "result.id");
+ CHECK(anna > 0);
+ yyjson_doc_free(d);
+ d = call(reqf("{\"v\":1,\"id\":\"p7\",\"cmd\":\"employee.create\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"name\":\"Bertil Bygg\",\"personal_no\":"
+ "\"19800202-5678\",\"monthly_salary_ore\":3000000,"
+ "\"tax_table\":30,\"tax_column\":1}}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ int64_t bertil = jint(d, "result.id");
+ CHECK(bertil > 0);
+ yyjson_doc_free(d);
+
+ /* preview: gross, table tax, rounded contributions and net */
+ d = call(reqf("{\"v\":1,\"id\":\"p8\",\"cmd\":\"payroll.run_preview\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"period\":\"2026-08\"}}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.period", "2026-08");
+ CHECK(jint(d, "result.tax_year") == 2026);
+ CHECK(yyjson_arr_size(jget(d, "result.items")) == 2);
+ CHECK(jint(d, "result.totals.gross_ore") == 5500000);
+ CHECK(jint(d, "result.totals.tax_ore") == 956000);
+ CHECK(jint(d, "result.totals.avgifter_ore") == 1728100);
+ CHECK(jint(d, "result.totals.net_ore") == 4544000);
+ CHECK(jint(d, "result.items.0.gross_ore") == 2500000);
+ CHECK(jint(d, "result.items.0.tax_ore") == 420800);
+ CHECK(jint(d, "result.items.0.avgifter_ore") == 785500);
+ CHECK(jint(d, "result.items.0.net_ore") == 2079200);
+ yyjson_doc_free(d);
+ CHECK(db_count(g_db, "SELECT count(*) FROM payroll_runs") == 0);
+
+ /* dry run writes nothing */
+ d = call(reqf("{\"v\":1,\"id\":\"p9\",\"cmd\":\"payroll.run_post\","
+ "\"session\":\"%s\",\"org\":%d,\"dry_run\":true,\"args\":"
+ "{\"period\":\"2026-08\",\"pay_date\":\"2026-08-25\"}}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ CHECK(jbool(d, "result.dry_run"));
+ CHECK(jint(d, "result.totals.gross_ore") == 5500000);
+ yyjson_doc_free(d);
+ CHECK(db_count(g_db, "SELECT count(*) FROM payroll_runs") == 0);
+
+ /* period and fiscal-year validation */
+ d = call(reqf("{\"v\":1,\"id\":\"p10\",\"cmd\":\"payroll.run_post\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"period\":\"2026-13\",\"pay_date\":\"2026-08-25\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "INVALID_ARGS");
+ yyjson_doc_free(d);
+ d = call(reqf("{\"v\":1,\"id\":\"p11\",\"cmd\":\"payroll.run_post\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"period\":\"2025-12\",\"pay_date\":\"2026-01-25\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "INVALID_ARGS");
+ yyjson_doc_free(d);
+ d = call(reqf("{\"v\":1,\"id\":\"p12\",\"cmd\":\"payroll.run_post\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"period\":\"2027-05\",\"pay_date\":\"2027-05-25\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "DATE_OUT_OF_RANGE");
+ yyjson_doc_free(d);
+
+ /* post: one balanced voucher, the run and its lines */
+ d = call(reqf("{\"v\":1,\"id\":\"p13\",\"cmd\":\"payroll.run_post\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"period\":\"2026-08\",\"pay_date\":\"2026-08-25\"}}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ int64_t run = jint(d, "result.id");
+ int64_t voucher = jint(d, "result.voucher_id");
+ CHECK(run > 0);
+ CHECK(voucher > 0);
+ CHECK_STR(d, "result.status", "posted");
+ CHECK_STR(d, "result.pay_date", "2026-08-25");
+ CHECK(jint(d, "result.totals.tax_ore") == 956000);
+ yyjson_doc_free(d);
+ CHECK(db_count(g_db, "SELECT count(*) FROM payroll_runs") == 1);
+ CHECK(db_count(g_db, "SELECT count(*) FROM payroll_run_lines") == 2);
+
+ d = call(reqf("{\"v\":1,\"id\":\"p14\",\"cmd\":\"voucher.get\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld}}",
+ g_session, (int)t->org_id, (long long)voucher));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.source", "payroll");
+ yyjson_doc_free(d);
+
+ char sql[512];
+ static const struct {
+ const char *account;
+ const char *side;
+ int64_t amount;
+ } legs[] = {
+ { "7010", "debit_ore", 5500000 },
+ { "7510", "debit_ore", 1728100 },
+ { "2710", "credit_ore", 956000 },
+ { "1930", "credit_ore", 4544000 },
+ { "2731", "credit_ore", 1728100 },
+ };
+ for (size_t i = 0; i < sizeof legs / sizeof legs[0]; i++) {
+ snprintf(sql, sizeof sql,
+ "SELECT COALESCE(SUM(r.%s),0) FROM voucher_rows r"
+ " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id"
+ " WHERE r.voucher_id=%lld AND a.number='%s'",
+ legs[i].side, (long long)voucher, legs[i].account);
+ CHECK(db_count(g_db, sql) == legs[i].amount);
+ }
+ snprintf(sql, sizeof sql,
+ "SELECT count(*) FROM voucher_rows WHERE voucher_id=%lld",
+ (long long)voucher);
+ CHECK(db_count(g_db, sql) == 6);
+ snprintf(sql, sizeof sql,
+ "SELECT SUM(debit_ore)-SUM(credit_ore) FROM voucher_rows"
+ " WHERE voucher_id=%lld",
+ (long long)voucher);
+ CHECK(db_count(g_db, sql) == 0);
+ snprintf(sql, sizeof sql,
+ "SELECT count(*) FROM payroll_run_lines WHERE run_id=%lld",
+ (long long)run);
+ CHECK(db_count(g_db, sql) == 2);
+
+ /* a second run for the same period is a conflict */
+ d = call(reqf("{\"v\":1,\"id\":\"p16\",\"cmd\":\"payroll.run_post\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"period\":\"2026-08\",\"pay_date\":\"2026-08-25\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "CONFLICT");
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"p17\",\"cmd\":\"payroll.run_get\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld}}",
+ g_session, (int)t->org_id, (long long)run));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.period", "2026-08");
+ CHECK_STR(d, "result.status", "posted");
+ CHECK(yyjson_arr_size(jget(d, "result.lines")) == 2);
+ CHECK(jint(d, "result.lines.0.gross_ore") == 2500000);
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"p18\",\"cmd\":\"payroll.run_list\","
+ "\"session\":\"%s\",\"org\":%d}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ CHECK(yyjson_arr_size(jget(d, "result.items")) == 1);
+ CHECK(jint(d, "result.items.0.line_count") == 2);
+ yyjson_doc_free(d);
+
+ /* AGI: the owner gets the personnummer in clear */
+ d = call(reqf("{\"v\":1,\"id\":\"p19\",\"cmd\":\"payroll.agi\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"period\":\"2026-08\"}}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ CHECK(yyjson_arr_size(jget(d, "result.items")) == 2);
+ CHECK_STR(d, "result.items.0.personal_no", "19750505-1111");
+ CHECK_STR(d, "result.items.0.name", "Anna Anställd");
+ CHECK(jint(d, "result.items.0.tax_ore") == 420800);
+ CHECK(jint(d, "result.totals.gross_ore") == 5500000);
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"p20\",\"cmd\":\"payroll.agi\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"period\":\"2026-07\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "NOT_FOUND");
+ yyjson_doc_free(d);
+
+ /* an income above the tabulated B range is refused, per SKV 433 */
+ d = call(reqf("{\"v\":1,\"id\":\"p21\",\"cmd\":\"employee.create\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"name\":\"Cecilia Chef\",\"personal_no\":"
+ "\"19900303-9012\",\"monthly_salary_ore\":9000000,"
+ "\"tax_table\":30,\"tax_column\":1}}",
+ g_session, (int)t->org_id));
+ CHECK_OK(d);
+ int64_t cecilia = jint(d, "result.id");
+ yyjson_doc_free(d);
+ d = call(reqf("{\"v\":1,\"id\":\"p22\",\"cmd\":\"payroll.run_preview\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":"
+ "{\"period\":\"2026-09\"}}",
+ g_session, (int)t->org_id));
+ CHECK_STR(d, "error.code", "INVALID_ARGS");
+ {
+ const char *msg = jstr(d, "error.message");
+ CHECK(msg && strstr(msg,
+ "income above the tabulated range is not supported"
+ " yet") != NULL);
+ }
+ yyjson_doc_free(d);
+ d = call(reqf("{\"v\":1,\"id\":\"p23\",\"cmd\":\"employee.archive\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld,"
+ "\"active\":false}}",
+ g_session, (int)t->org_id, (long long)cecilia));
+ CHECK_OK(d);
+ yyjson_doc_free(d);
+
+ /* pay the tax and contributions: its own voucher, then paid */
+ d = call(reqf("{\"v\":1,\"id\":\"p24\",\"cmd\":\"payroll.pay_tax\","
+ "\"session\":\"%s\",\"org\":%d,\"dry_run\":true,\"args\":"
+ "{\"run_id\":%lld,\"date\":\"2026-09-12\"}}",
+ g_session, (int)t->org_id, (long long)run));
+ CHECK_OK(d);
+ CHECK(jbool(d, "result.dry_run"));
+ CHECK(jint(d, "result.total_ore") == 2684100);
+ yyjson_doc_free(d);
+ CHECK(db_count(g_db, "SELECT count(*) FROM payroll_runs"
+ " WHERE status='paid'") == 0);
+
+ d = call(reqf("{\"v\":1,\"id\":\"p25\",\"cmd\":\"payroll.pay_tax\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"run_id\":%lld,"
+ "\"date\":\"2026-09-12\"}}",
+ g_session, (int)t->org_id, (long long)run));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.status", "paid");
+ CHECK(jint(d, "result.total_ore") == 2684100);
+ int64_t payment = jint(d, "result.payment_voucher_id");
+ CHECK(payment > 0);
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"p26\",\"cmd\":\"voucher.get\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld}}",
+ g_session, (int)t->org_id, (long long)payment));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.source", "payroll_tax");
+ yyjson_doc_free(d);
+ snprintf(sql, sizeof sql,
+ "SELECT COALESCE(SUM(debit_ore),0) FROM voucher_rows r"
+ " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id"
+ " WHERE r.voucher_id=%lld AND a.number='2710'",
+ (long long)payment);
+ CHECK(db_count(g_db, sql) == 956000);
+ snprintf(sql, sizeof sql,
+ "SELECT COALESCE(SUM(debit_ore),0) FROM voucher_rows r"
+ " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id"
+ " WHERE r.voucher_id=%lld AND a.number='2731'",
+ (long long)payment);
+ CHECK(db_count(g_db, sql) == 1728100);
+ snprintf(sql, sizeof sql,
+ "SELECT COALESCE(SUM(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.voucher_id=%lld AND a.number='1630'",
+ (long long)payment);
+ CHECK(db_count(g_db, sql) == 2684100);
+
+ d = call(reqf("{\"v\":1,\"id\":\"p27\",\"cmd\":\"payroll.run_get\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"id\":%lld}}",
+ g_session, (int)t->org_id, (long long)run));
+ CHECK_OK(d);
+ CHECK_STR(d, "result.status", "paid");
+ CHECK(jint(d, "result.payment_voucher_id") == payment);
+ yyjson_doc_free(d);
+
+ d = call(reqf("{\"v\":1,\"id\":\"p28\",\"cmd\":\"payroll.pay_tax\","
+ "\"session\":\"%s\",\"org\":%d,\"args\":{\"run_id\":%lld}}",
+ g_session, (int)t->org_id, (long long)run));
+ CHECK_STR(d, "error.code", "CONFLICT");
+ yyjson_doc_free(d);
+}
+
struct ttest {
const char *name;
void (*fn)(struct tctx *);
@@ -4169,6 +4784,9 @@ static const struct ttest TESTS[] = {
{ "year_close", test_year_close, "fiscal_years" },
{ "audit_verify", test_audit_verify, "sie_import" },
{ "audit_tamper", test_audit_tamper, "audit_verify" },
+ { "employees", test_employees, "org_members" },
+ { "tax_tables", test_tax_tables, "org_members" },
+ { "payroll", test_payroll, "employees tax_tables" },
{ "transport", test_transport, "" },
{ "pre_migration_snapshot", test_pre_migration_snapshot, "" },
{ "rate_limit", test_rate_limit, "" },
generated by cgit v1.3 (git 2.53.0) at 2026-09-21 09:04:19 +0000