#include "commands.h" #include "cmd_util.h" #include #include #include #include #include "audit.h" #include "db.h" #include "ledger.h" #include "mail.h" #include "payslip.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); char series[16]; db_setting_copy(r->db, r->org_id, "series_payroll", "L", series, sizeof series); 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.series = series; 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; } /* ------------------------------------------------------------------ */ /* lönebesked */ /* ------------------------------------------------------------------ */ struct payslip_ctx { struct payslip_data d; char employee_name[200]; char personal_no[32]; char email[256]; char period[16]; char pay_date[16]; char run_ref[64]; char employer_name[256]; char employer_address[1024]; char employer_postal[64]; char employer_city[128]; char employer_org_nr[64]; char employer_phone[64]; char employer_email[256]; char filename[320]; int64_t voucher_id; int64_t employee_id; }; static void payslip_mask(const char *pn, char *out, size_t n) { size_t len = pn ? strlen(pn) : 0; if (len + 1 > n) len = n - 1; for (size_t i = 0; i < len; i++) out[i] = (i + 4 >= len || pn[i] == '-') ? pn[i] : '*'; out[len] = '\0'; } static int payslip_prepare(struct req *r, struct payslip_ctx *c) { memset(c, 0, sizeof *c); int64_t run_id = 0; if (!arg_int(r->args, "run_id", &run_id) || run_id <= 0) { fail(r, "INVALID_ARGS", "run_id is required"); return -1; } int64_t employee_id = 0; int have_employee = arg_int(r->args, "employee_id", &employee_id); if (have_employee && employee_id <= 0) { fail(r, "INVALID_ARGS", "employee_id must be a positive id"); return -1; } sqlite3_stmt *st = NULL; if (sqlite3_prepare_v2( r->db, "SELECT period,pay_date,COALESCE(voucher_id,0) FROM payroll_runs" " 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, run_id); if (sqlite3_step(st) != SQLITE_ROW) { sqlite3_finalize(st); fail(r, "NOT_FOUND", "payroll run not found"); return -1; } snprintf(c->period, sizeof c->period, "%s", sq(sqlite3_column_text(st, 0))); snprintf(c->pay_date, sizeof c->pay_date, "%s", sq(sqlite3_column_text(st, 1))); c->voucher_id = sqlite3_column_int64(st, 2); sqlite3_finalize(st); st = NULL; if (!have_employee) { int64_t lines = 0; if (sqlite3_prepare_v2(r->db, "SELECT count(*) FROM payroll_run_lines" " WHERE org_id=?1 AND run_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, run_id); if (sqlite3_step(st) == SQLITE_ROW) lines = sqlite3_column_int64(st, 0); sqlite3_finalize(st); st = NULL; if (lines == 0) { fail(r, "NOT_FOUND", "payroll run has no lines"); return -1; } if (lines > 1) { fail(r, "INVALID_ARGS", "employee_id is required when the run has more than one" " line"); return -1; } } char sql[512]; snprintf(sql, sizeof sql, "SELECT l.employee_id,e.name,e.personal_no_enc,e.email," "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%s", have_employee ? " AND l.employee_id=?3" : ""); if (sqlite3_prepare_v2(r->db, sql, -1, &st, NULL) != SQLITE_OK) { db_error(r); return -1; } sqlite3_bind_int64(st, 1, r->org_id); sqlite3_bind_int64(st, 2, run_id); if (have_employee) sqlite3_bind_int64(st, 3, employee_id); if (sqlite3_step(st) != SQLITE_ROW) { sqlite3_finalize(st); if (have_employee) fail(r, "NOT_FOUND", "employee has no line in this payroll run"); else fail(r, "NOT_FOUND", "payroll run has no lines"); return -1; } c->employee_id = sqlite3_column_int64(st, 0); snprintf(c->employee_name, sizeof c->employee_name, "%s", sq(sqlite3_column_text(st, 1))); const char *enc = sq(sqlite3_column_text(st, 2)); char *plain = NULL; if (secret_decrypt(enc, &plain) == 0 && plain && *plain) { payslip_mask(plain, c->personal_no, sizeof c->personal_no); free(plain); } else { free(plain); snprintf(c->personal_no, sizeof c->personal_no, "********"); } snprintf(c->email, sizeof c->email, "%s", sq(sqlite3_column_text(st, 3))); c->d.gross_ore = sqlite3_column_int64(st, 4); c->d.tax_ore = sqlite3_column_int64(st, 5); c->d.avgifter_ore = sqlite3_column_int64(st, 6); c->d.net_ore = sqlite3_column_int64(st, 7); c->d.tax_table = (int)sqlite3_column_int64(st, 8); c->d.tax_column = (int)sqlite3_column_int64(st, 9); sqlite3_finalize(st); st = NULL; if (sqlite3_prepare_v2( r->db, "SELECT COALESCE(name,''),COALESCE(address,'')," "COALESCE(postal_code,''),COALESCE(city,''),COALESCE(org_nr,'')," "COALESCE(phone,''),COALESCE(email,'') FROM orgs WHERE id=?1", -1, &st, NULL) != SQLITE_OK) { db_error(r); return -1; } sqlite3_bind_int64(st, 1, r->org_id); if (sqlite3_step(st) != SQLITE_ROW) { sqlite3_finalize(st); fail(r, "INTERNAL", "org not found"); return -1; } snprintf(c->employer_name, sizeof c->employer_name, "%s", sq(sqlite3_column_text(st, 0))); snprintf(c->employer_address, sizeof c->employer_address, "%s", sq(sqlite3_column_text(st, 1))); snprintf(c->employer_postal, sizeof c->employer_postal, "%s", sq(sqlite3_column_text(st, 2))); snprintf(c->employer_city, sizeof c->employer_city, "%s", sq(sqlite3_column_text(st, 3))); snprintf(c->employer_org_nr, sizeof c->employer_org_nr, "%s", sq(sqlite3_column_text(st, 4))); snprintf(c->employer_phone, sizeof c->employer_phone, "%s", sq(sqlite3_column_text(st, 5))); snprintf(c->employer_email, sizeof c->employer_email, "%s", sq(sqlite3_column_text(st, 6))); sqlite3_finalize(st); struct payroll_cfg cfg; payroll_cfg_load(r, &cfg); snprintf(c->run_ref, sizeof c->run_ref, "Lönekörning %s", c->period); char safe_name[200]; snprintf(safe_name, sizeof safe_name, "%s", c->employee_name); for (char *p = safe_name; *p; p++) if (*p == '/') *p = '-'; snprintf(c->filename, sizeof c->filename, "Lönebesked %s %s.pdf", c->period, safe_name); c->d.employer.name = c->employer_name; c->d.employer.address = c->employer_address; c->d.employer.postal_code = c->employer_postal; c->d.employer.city = c->employer_city; c->d.employer.org_nr = c->employer_org_nr; c->d.employer.phone = c->employer_phone; c->d.employer.email = c->employer_email; c->d.employee_name = c->employee_name; c->d.personal_no_masked = c->personal_no; c->d.period = c->period; c->d.pay_date = c->pay_date; c->d.run_ref = c->run_ref; c->d.rate_bp = cfg.rate_bp; return 0; } static yyjson_mut_val *h_payroll_payslip(struct req *r) { struct payslip_ctx ctx; if (payslip_prepare(r, &ctx) != 0) return NULL; unsigned char *pdf = NULL; size_t len = 0; if (payslip_render(&ctx.d, &pdf, &len) != 0) return fail(r, "INTERNAL", "could not render the payslip"); char *b64 = util_b64(pdf, len); free(pdf); if (!b64) return fail(r, "INTERNAL", "could not encode the payslip"); yyjson_mut_val *o = yyjson_mut_obj(r->rdoc); yyjson_mut_obj_add_strcpy(r->rdoc, o, "content_base64", b64); yyjson_mut_obj_add_strcpy(r->rdoc, o, "filename", ctx.filename); free(b64); return o; } /* Whole kronor with a space separator and two decimals, e.g. "20 792,00". */ static void payroll_fmt_amount(int64_t ore, char *out, size_t n) { uint64_t abs = ore < 0 ? (uint64_t)(-(ore + 1)) + 1 : (uint64_t)ore; char digits[24]; snprintf(digits, sizeof digits, "%llu", (unsigned long long)(abs / 100)); size_t len = strlen(digits), o = 0; if (ore < 0 && o + 1 < n) out[o++] = '-'; for (size_t i = 0; i < len && o + 1 < n; i++) { if (i > 0 && (len - i) % 3 == 0) out[o++] = ' '; out[o++] = digits[i]; } snprintf(out + o, n - o, ",%02llu", (unsigned long long)(abs % 100)); } static int payroll_store_payslip(struct req *r, const struct payslip_ctx *c, const unsigned char *pdf, size_t len, int64_t *out_id) { if (c->voucher_id <= 0) { fail(r, "INTERNAL", "payroll run has no voucher"); return -1; } if (db_exec(r->db, "BEGIN IMMEDIATE", NULL) != 0) { fail(r, "DB_BUSY", "could not start transaction"); return -1; } unsigned char hash[32]; util_sha256(pdf, len, hash); char ts[32]; util_iso8601(util_now(), ts, sizeof ts); sqlite3_stmt *st = NULL; if (sqlite3_prepare_v2( r->db, "INSERT INTO attachments(org_id,sha256,filename,mime,size_bytes," "content,created_at,created_by)" " VALUES(?1,?2,?3,'application/pdf',?4,?5,?6,?7)" " ON CONFLICT DO NOTHING", -1, &st, NULL) != SQLITE_OK) { db_error(r); goto done; } sqlite3_bind_int64(st, 1, r->org_id); sqlite3_bind_blob(st, 2, hash, 32, SQLITE_TRANSIENT); sqlite3_bind_text(st, 3, c->filename, -1, SQLITE_TRANSIENT); sqlite3_bind_int64(st, 4, (int64_t)len); sqlite3_bind_blob(st, 5, pdf, (int)len, SQLITE_TRANSIENT); sqlite3_bind_text(st, 6, ts, -1, SQLITE_TRANSIENT); sqlite3_bind_int64(st, 7, r->sess->user_id); int step = sqlite3_step(st); sqlite3_finalize(st); st = NULL; if (step != SQLITE_DONE) { db_sqlite_error(r); goto done; } if (sqlite3_prepare_v2( r->db, "SELECT id FROM attachments WHERE org_id=?1 AND sha256=?2" " AND filename=?3", -1, &st, NULL) != SQLITE_OK) { db_error(r); goto done; } sqlite3_bind_int64(st, 1, r->org_id); sqlite3_bind_blob(st, 2, hash, 32, SQLITE_TRANSIENT); sqlite3_bind_text(st, 3, c->filename, -1, SQLITE_TRANSIENT); if (sqlite3_step(st) != SQLITE_ROW) { sqlite3_finalize(st); st = NULL; fail(r, "INTERNAL", "could not store the payslip attachment"); goto done; } *out_id = sqlite3_column_int64(st, 0); sqlite3_finalize(st); st = NULL; if (sqlite3_prepare_v2( r->db, "INSERT OR IGNORE INTO voucher_attachments(org_id,voucher_id," "attachment_id,created_at) VALUES(?1,?2,?3,?4)", -1, &st, NULL) != SQLITE_OK) { db_error(r); goto done; } sqlite3_bind_int64(st, 1, r->org_id); sqlite3_bind_int64(st, 2, c->voucher_id); sqlite3_bind_int64(st, 3, *out_id); sqlite3_bind_text(st, 4, ts, -1, SQLITE_TRANSIENT); step = sqlite3_step(st); sqlite3_finalize(st); st = NULL; if (step != SQLITE_DONE) { db_sqlite_error(r); goto done; } if (db_exec(r->db, "COMMIT", NULL) != 0) { sqlite3_exec(r->db, "ROLLBACK", NULL, NULL, NULL); fail(r, "DB_BUSY", "commit failed"); return -1; } return 0; done: sqlite3_finalize(st); sqlite3_exec(r->db, "ROLLBACK", NULL, NULL, NULL); return -1; } static yyjson_mut_val *h_payroll_payslip_mail(struct req *r) { struct payslip_ctx ctx; if (payslip_prepare(r, &ctx) != 0) return NULL; const char *to = arg_str(r->args, "to"); if (!to || !*to) to = ctx.email; if (!to || !*to) return fail(r, "INVALID_ARGS", "employee has no e-mail address"); unsigned char *pdf = NULL; size_t pdf_len = 0; if (payslip_render(&ctx.d, &pdf, &pdf_len) != 0) return fail(r, "INTERNAL", "could not render the payslip"); char errbuf[512]; errbuf[0] = '\0'; if (mail_config_check(r->db, r->org_id, errbuf, sizeof errbuf) != 0) { free(pdf); return fail(r, "SMTP_NOT_CONFIGURED", errbuf[0] ? errbuf : "smtp is not configured"); } if (r->dry_run) { free(pdf); yyjson_mut_val *o = yyjson_mut_obj(r->rdoc); yyjson_mut_obj_add_strcpy(r->rdoc, o, "sent_to", to); yyjson_mut_obj_add_strcpy(r->rdoc, o, "filename", ctx.filename); yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true); return o; } int64_t attachment_id = 0; if (payroll_store_payslip(r, &ctx, pdf, pdf_len, &attachment_id) != 0) { free(pdf); return NULL; } char subject[64]; snprintf(subject, sizeof subject, "Lönebesked %s", ctx.period); char amount[48]; payroll_fmt_amount(ctx.d.net_ore, amount, sizeof amount); size_t body_len = strlen(ctx.employee_name) + strlen(ctx.period) + strlen(amount) + strlen(ctx.pay_date) + strlen(ctx.employer_name) + 160; char *body = xmalloc(body_len); snprintf(body, body_len, "Hej %s,\n\nBifogat finner du lönebesked för %s." " Nettolön: %s kr.\nUtbetalningsdatum: %s.\n\n" "Med vänlig hälsning\n%s\n", ctx.employee_name, ctx.period, amount, ctx.pay_date, ctx.employer_name); struct mail_message m; memset(&m, 0, sizeof m); m.to = to; m.subject = subject; m.body = body; m.attach_name = ctx.filename; m.attach = pdf; m.attach_len = pdf_len; errbuf[0] = '\0'; int mailed = mail_send_pdf(r->db, r->org_id, &m, errbuf, sizeof errbuf); free(body); free(pdf); if (mailed == -1) return fail(r, "SMTP_NOT_CONFIGURED", errbuf[0] ? errbuf : "smtp is not configured"); if (mailed != 0) return fail(r, "SMTP_FAILED", errbuf[0] ? errbuf : "smtp send failed"); char ts[32]; util_iso8601(util_now(), ts, sizeof ts); char *reqjson = audit_args_json(r->args); audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id, "payroll.payslip_mail", reqjson, "OK", NULL); free(reqjson); yyjson_mut_val *o = yyjson_mut_obj(r->rdoc); yyjson_mut_obj_add_strcpy(r->rdoc, o, "sent_to", to); yyjson_mut_obj_add_strcpy(r->rdoc, o, "at", ts); yyjson_mut_obj_add_int(r->rdoc, o, "attachment_id", attachment_id); yyjson_mut_obj_add_strcpy(r->rdoc, o, "filename", ctx.filename); 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; char series[16]; db_setting_copy(r->db, r->org_id, "series_payroll", "L", series, sizeof series); 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.series = series; 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_payslip[] = { { "run_id", ARG_INT, 1, NULL, NULL, "Payroll run id" }, { "employee_id", ARG_INT, 0, NULL, NULL, "Employee line; required when the run has more than one" }, }; static const struct cmd_arg args_payslip_mail[] = { { "run_id", ARG_INT, 1, NULL, NULL, "Payroll run id" }, { "employee_id", ARG_INT, 0, NULL, NULL, "Employee line; required when the run has more than one" }, { "to", ARG_STR, 0, NULL, NULL, "Recipient; defaults to the employee's e-mail address" }, }; 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.payslip", "Render one employee's payslip PDF", PERM_READ, 1, 0, 0, h_payroll_payslip, CMD_ARGS(args_payslip) }, { "payroll.payslip_mail", "E-mail an employee's payslip PDF", PERM_WRITE, 1, 1, 1, h_payroll_payslip_mail, CMD_ARGS(args_payslip_mail) }, { "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] };