From e692f0fbe16342195297048534ad1be227493107 Mon Sep 17 00:00:00 2001 From: Anders Betts Date: Mon, 21 Sep 2026 09:09:39 +0200 Subject: payroll: employees, tax tables and the monthly run (schema v10) --- Makefile | 4 +- docs/PROTOCOL.md | 109 ++++- docs/SCHEMA.md | 135 +++++- src/cmd_employees.c | 665 +++++++++++++++++++++++++++ src/cmd_payroll.c | 1254 +++++++++++++++++++++++++++++++++++++++++++++++++++ src/commands.c | 4 +- src/db.c | 152 ++++++- src/db.h | 2 +- src/tax_table.c | 745 ++++++++++++++++++++++++++++++ src/tax_table.h | 52 +++ tests/test_core.c | 618 +++++++++++++++++++++++++ 11 files changed, 3724 insertions(+), 16 deletions(-) create mode 100644 src/cmd_employees.c create mode 100644 src/cmd_payroll.c create mode 100644 src/tax_table.c create mode 100644 src/tax_table.h 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`. + ## 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!` | ## 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::`, `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 +#include +#include + +#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 +#include +#include +#include + +#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 #include -#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 +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include +#include +#include + +#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, "&")) != 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 +#include +#include + +#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, "" }, -- cgit v1.3