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) --- 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 +++ 7 files changed, 2870 insertions(+), 4 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 (limited to 'src') 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 -- cgit v1.3