From 4488b6c2eb1a3ddf20e190ebd28953e4f3f01042 Mon Sep 17 00:00:00 2001 From: Anders Betts Date: Mon, 21 Sep 2026 09:56:01 +0200 Subject: payroll: lönebesked PDF and mail (schema v11) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- src/cmd_employees.c | 41 ++++- src/cmd_payroll.c | 445 ++++++++++++++++++++++++++++++++++++++++++++++++++++ src/db.c | 37 ++++- src/db.h | 2 +- src/mail.c | 168 ++++++++++++++++++++ src/mail.h | 26 +++ src/payslip.c | 298 +++++++++++++++++++++++++++++++++++ src/payslip.h | 34 ++++ 8 files changed, 1039 insertions(+), 12 deletions(-) create mode 100644 src/mail.c create mode 100644 src/mail.h create mode 100644 src/payslip.c create mode 100644 src/payslip.h (limited to 'src') diff --git a/src/cmd_employees.c b/src/cmd_employees.c index 788dd83..952bd2b 100644 --- a/src/cmd_employees.c +++ b/src/cmd_employees.c @@ -17,7 +17,7 @@ #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,'')" + "created_at,COALESCE(updated_at,''),email" static int personal_no_valid(const char *s) { @@ -90,6 +90,8 @@ static yyjson_mut_val *employee_json(struct req *r, sqlite3_stmt *st) sq(sqlite3_column_text(st, 12))); yyjson_mut_obj_add_strcpy(r->rdoc, o, "updated_at", sq(sqlite3_column_text(st, 13))); + yyjson_mut_obj_add_strcpy(r->rdoc, o, "email", + sq(sqlite3_column_text(st, 14))); return o; } @@ -129,6 +131,7 @@ struct employee_input { const char *city; const char *bank_account; const char *salary_account; + const char *email; int64_t monthly_salary_ore; int have_salary; int64_t tax_table; @@ -149,6 +152,7 @@ static void employee_input_read(struct req *r, struct employee_input *in) 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->email = arg_str(r->args, "email"); 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); @@ -210,6 +214,21 @@ static int employee_input_validate(struct req *r, fail(r, "INVALID_ARGS", "tax_column must be between 1 and 6"); return -1; } + if (in->email) { + size_t len = strlen(in->email); + if (len > 254) { + fail(r, "INVALID_ARGS", "email is too long"); + return -1; + } + for (const char *p = in->email; *p; p++) { + unsigned char ch = (unsigned char)*p; + if (ch < 32 || ch == 127) { + fail(r, "INVALID_ARGS", + "email must not contain control characters"); + return -1; + } + } + } return 0; } @@ -346,6 +365,7 @@ static yyjson_mut_val *h_employee_create(struct req *r) 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 : ""; + const char *email = in.email ? in.email : ""; if (r->dry_run) { free(stored); @@ -365,6 +385,7 @@ static yyjson_mut_val *h_employee_create(struct req *r) 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_strcpy(r->rdoc, o, "email", email); yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true); return o; } @@ -376,8 +397,8 @@ static yyjson_mut_val *h_employee_create(struct req *r) 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)", + "tax_table,tax_column,created_at,email)" + " VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13)", -1, &st, NULL) != SQLITE_OK) { free(stored); return db_error(r); @@ -394,6 +415,7 @@ static yyjson_mut_val *h_employee_create(struct req *r) 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); + sqlite3_bind_text(st, 13, email, -1, SQLITE_TRANSIENT); int rc = sqlite3_step(st); sqlite3_finalize(st); free(stored); @@ -427,7 +449,7 @@ static yyjson_mut_val *h_employee_update(struct req *r) 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.city && !in.bank_account && !in.salary_account && !in.email && !in.have_salary && !in.have_table && !in.have_column && !in.have_active) return fail(r, "INVALID_ARGS", "nothing to update"); @@ -457,7 +479,7 @@ static yyjson_mut_val *h_employee_update(struct req *r) "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,'')" + "created_at,COALESCE(updated_at,''),COALESCE(?14,email)" " FROM employees WHERE org_id=?1 AND id=?2", -1, &st, NULL) != SQLITE_OK) { free(stored); @@ -477,6 +499,7 @@ static yyjson_mut_val *h_employee_update(struct req *r) 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); + bind_text_or_null(st, 14, in.email); yyjson_mut_val *o = NULL; if (sqlite3_step(st) == SQLITE_ROW) o = employee_json(r, st); @@ -506,7 +529,8 @@ static yyjson_mut_val *h_employee_update(struct req *r) " 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", + " email=COALESCE(?14,email)," + " updated_at=?15 WHERE org_id=?1 AND id=?2", -1, &st, NULL) != SQLITE_OK) { free(stored); return db_error(r); @@ -524,7 +548,8 @@ static yyjson_mut_val *h_employee_update(struct req *r) 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); + bind_text_or_null(st, 14, in.email); + sqlite3_bind_text(st, 15, ts, -1, SQLITE_TRANSIENT); int rc = sqlite3_step(st); sqlite3_finalize(st); free(stored); @@ -617,6 +642,7 @@ static const struct cmd_arg args_employee_create[] = { { "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" }, + { "email", ARG_STR, 0, NULL, NULL, "E-mail for lönebesked" }, { "salary_account", ARG_STR, 0, NULL, NULL, "Salary account, digits only; defaults to payroll_salary_account" }, { "monthly_salary_ore", ARG_INT, 0, "0", NULL, @@ -634,6 +660,7 @@ static const struct cmd_arg args_employee_update[] = { { "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" }, + { "email", ARG_STR, 0, NULL, NULL, "E-mail for lönebesked" }, { "salary_account", ARG_STR, 0, NULL, NULL, "Salary account, digits only" }, { "monthly_salary_ore", ARG_INT, 0, NULL, NULL, "Monthly gross salary in öre" }, diff --git a/src/cmd_payroll.c b/src/cmd_payroll.c index e7cafa3..b5db4f1 100644 --- a/src/cmd_payroll.c +++ b/src/cmd_payroll.c @@ -9,6 +9,8 @@ #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" @@ -737,6 +739,431 @@ static yyjson_mut_val *h_payroll_run_get(struct req *r) 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 */ /* ------------------------------------------------------------------ */ @@ -1206,6 +1633,20 @@ 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" }, }; @@ -1239,6 +1680,10 @@ const struct command g_cmd_payroll[] = { 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, diff --git a/src/db.c b/src/db.c index f77d9e5..95ecf3f 100644 --- a/src/db.c +++ b/src/db.c @@ -413,10 +413,11 @@ 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. */ +/* v10/v11: 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; v11 adds the + employee e-mail column. */ static const char SCHEMA_PAYROLL[] = "CREATE TABLE IF NOT EXISTS employees (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," @@ -437,6 +438,7 @@ static const char SCHEMA_PAYROLL[] = " active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1))," " created_at TEXT NOT NULL," " updated_at TEXT," + " email TEXT NOT NULL DEFAULT ''," " UNIQUE (org_id, id)" ") STRICT;\n" @@ -886,6 +888,29 @@ static int db_upgrade_v10(sqlite3 *db, char **err) err); } +/* v11: the employee e-mail address for lönebesked delivery. Fresh + databases already carry the column, so the ALTER is skipped when a + downgraded database (the migration test replays v3) still has it. */ +static int db_upgrade_v11(sqlite3 *db, char **err) +{ + sqlite3_stmt *st = NULL; + if (sqlite3_prepare_v2(db, + "SELECT count(*) FROM pragma_table_info('employees')" + " WHERE name='email'", + -1, &st, NULL) != SQLITE_OK) { + set_err(err, "database error"); + return -1; + } + int have = sqlite3_step(st) == SQLITE_ROW && sqlite3_column_int(st, 0) > 0; + sqlite3_finalize(st); + if (have) + return 0; + return db_exec(db, + "ALTER TABLE employees ADD COLUMN email TEXT NOT NULL" + " DEFAULT ''", + err); +} + static int db_upgrade(sqlite3 *db, int from, char **err) { if (db_exec(db, "BEGIN IMMEDIATE", err) != 0) @@ -926,6 +951,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err) db_exec(db, "ROLLBACK", NULL); return -1; } + if (from < 11 && db_upgrade_v11(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); diff --git a/src/db.h b/src/db.h index d4cca6b..4e16f62 100644 --- a/src/db.h +++ b/src/db.h @@ -4,7 +4,7 @@ #include #include -#define BOKF_SCHEMA_VERSION 10 +#define BOKF_SCHEMA_VERSION 11 int db_open(const char *path, sqlite3 **out, char **err); int db_migrate(sqlite3 *db, char **err); diff --git a/src/mail.c b/src/mail.c new file mode 100644 index 0000000..52d429c --- /dev/null +++ b/src/mail.c @@ -0,0 +1,168 @@ +#include "mail.h" + +#include +#include +#include + +#include "db.h" +#include "secret.h" +#include "smtp.h" +#include "util.h" + +struct mail_cfg { + char *host; + char *port; + char *from; + char *reply_to; + char *security; + char *user; + char *password; + char from_name[256]; +}; + +static void mail_cfg_free(struct mail_cfg *c) +{ + free(c->host); + free(c->port); + free(c->from); + free(c->reply_to); + free(c->security); + free(c->user); + free(c->password); + memset(c, 0, sizeof *c); +} + +static void set_err(char *err, size_t errlen, const char *msg) +{ + if (err && errlen) + snprintf(err, errlen, "%s", msg); +} + +static int mail_cfg_load(sqlite3 *db, int64_t org_id, struct mail_cfg *c, + char *err, size_t errlen) +{ + memset(c, 0, sizeof *c); + c->host = db_setting(db, org_id, "smtp_host"); + c->port = db_setting(db, org_id, "smtp_port"); + c->from = db_setting(db, org_id, "smtp_from"); + c->reply_to = db_setting(db, org_id, "smtp_reply_to"); + c->security = db_setting(db, org_id, "smtp_security"); + c->user = db_setting(db, org_id, "smtp_user"); + c->password = db_setting(db, org_id, "smtp_password"); + + if (!c->host || !*c->host || !c->from || !*c->from) { + set_err(err, errlen, "smtp_host and smtp_from must be set"); + goto fail; + } + if (c->user && *c->user) { + if (!c->password || !*c->password) { + set_err(err, errlen, + "smtp_user is set but smtp_password is missing"); + goto fail; + } + if (!secret_available()) { + set_err(err, errlen, + "smtp_password is set but BOKFD_SECRET_KEY is missing or" + " invalid"); + goto fail; + } + char *plain = NULL; + if (secret_decrypt(c->password, &plain) != 0 || !plain) { + free(plain); + set_err(err, errlen, + "cannot decrypt smtp_password, check BOKFD_SECRET_KEY"); + goto fail; + } + free(c->password); + c->password = plain; + } + + sqlite3_stmt *st = NULL; + int ok = 0; + if (sqlite3_prepare_v2(db, "SELECT name FROM orgs WHERE id=?1", -1, &st, + NULL) == SQLITE_OK) { + sqlite3_bind_int64(st, 1, org_id); + if (sqlite3_step(st) == SQLITE_ROW) { + const unsigned char *name = sqlite3_column_text(st, 0); + snprintf(c->from_name, sizeof c->from_name, "%s", + name ? (const char *)name : ""); + ok = 1; + } + } + sqlite3_finalize(st); + if (!ok) { + set_err(err, errlen, "org not found"); + goto fail; + } + return 0; + +fail: + mail_cfg_free(c); + return -1; +} + +static int mail_cfg_port(const char *s) +{ + if (s && *s) { + long v = strtol(s, NULL, 10); + if (v >= 1 && v <= 65535) + return (int)v; + } + return 587; +} + +int mail_config_check(sqlite3 *db, int64_t org_id, char *err, size_t errlen) +{ + struct mail_cfg c; + if (err && errlen) + err[0] = '\0'; + if (!db) { + set_err(err, errlen, "no database"); + return -1; + } + if (mail_cfg_load(db, org_id, &c, err, errlen) != 0) + return -1; + mail_cfg_free(&c); + return 0; +} + +int mail_send_pdf(sqlite3 *db, int64_t org_id, const struct mail_message *m, + char *err, size_t errlen) +{ + if (err && errlen) + err[0] = '\0'; + if (!m || !m->to || !*m->to) { + set_err(err, errlen, "no recipient"); + return -2; + } + struct mail_cfg c; + if (mail_cfg_load(db, org_id, &c, err, errlen) != 0) + return -1; + + struct smtp_message sm; + memset(&sm, 0, sizeof sm); + sm.host = c.host; + sm.port = mail_cfg_port(c.port); + sm.security = c.security && *c.security ? c.security : "starttls"; + sm.user = c.user && *c.user ? c.user : ""; + sm.password = c.password ? c.password : ""; + sm.from = c.from; + sm.from_name = c.from_name; + sm.to = m->to; + sm.subject = m->subject; + sm.body = m->body; + sm.attach_name = m->attach_name; + sm.attach = m->attach; + sm.attach_len = m->attach_len; + + char send_err[512]; + send_err[0] = '\0'; + int rc = smtp_send(&sm, send_err, sizeof send_err); + mail_cfg_free(&c); + if (rc != 0) { + set_err(err, errlen, + send_err[0] ? send_err : "smtp send failed"); + return -2; + } + return 0; +} diff --git a/src/mail.h b/src/mail.h new file mode 100644 index 0000000..5efade5 --- /dev/null +++ b/src/mail.h @@ -0,0 +1,26 @@ +#ifndef BOKF_MAIL_H +#define BOKF_MAIL_H + +#include +#include +#include + +struct mail_message { + const char *to; + const char *subject; + const char *body; + const char *attach_name; + const unsigned char *attach; + size_t attach_len; +}; + +/* Checks the org's SMTP settings (including decrypting the stored + smtp_password) without sending. 0 configured, -1 not configured. */ +int mail_config_check(sqlite3 *db, int64_t org_id, char *err, size_t errlen); + +/* Sends m as a PDF attachment. 0 sent, -1 not configured, -2 send failed. + err receives an English message; secrets are never included. */ +int mail_send_pdf(sqlite3 *db, int64_t org_id, const struct mail_message *m, + char *err, size_t errlen); + +#endif diff --git a/src/payslip.c b/src/payslip.c new file mode 100644 index 0000000..81ebded --- /dev/null +++ b/src/payslip.c @@ -0,0 +1,298 @@ +#include "payslip.h" + +#include +#include +#include + +#include "pdf.h" +#include "util.h" +#include "wordmark.h" + +#define INK "#314c59" +#define WHITE "#ffffff" +#define RULE "#000000" + +#define PAGE_LEFT 17.300 +#define PAGE_RIGHT_X 577.700 +#define BAR_HEADER_Y 53.300 +#define BAR_HEADER_H 22.416 +#define RULE_HEADER_Y 90.000 +#define RULE_W 0.7005 + +#define SIZE_VALUE 9.1065 +#define SIZE_LABEL 7.2852 +#define SIZE_FOOT 6.3746 +#define SIZE_PAGE 9.75 +#define SIZE_TITLE 12.0 + +#define ROW_STEP 14.7105 +#define INFO_LABEL_RIGHT 150.000 +#define INFO_VALUE_X 155.000 +#define INFO_BASE_Y 112.0000 +#define LABEL_DY 0.3874 + +#define BAR_TABLE_Y 238.000 +#define BAR_TABLE_H 15.410 +#define TABLE_HEADER_Y 249.3786 +#define ROW_FIRST_Y 264.0891 +#define AMOUNT_LABEL_X 20.1015 +#define AMOUNT_RIGHT 574.892 +#define AMOUNT_RULE_Y (ROW_FIRST_Y + 2 * ROW_STEP + 10.0) +#define NOTE_DY 7.5000 + +#define RULE_FOOTER_Y 621.756 +#define FOOT_LABEL_Y 642.4721 +#define FOOT_NAME_Y 656.6028 +#define FOOT_ADDR_Y 671.3133 +#define FOOT_EMAIL_Y 671.3133 +#define FOOT_CITY_Y 686.0238 +#define FOOT_ORG_LABEL_X 263.8759 +#define FOOT_ORG_X 334.6266 +#define FOOT_ORG_Y 641.8922 +#define FOOT_PAGE_Y 819.7954 +#define FOOT_PAGE_RIGHT 567.4275 + +#define WORDMARK_X 21.742 +#define TITLE_RIGHT 574.892 +#define TITLE_BASE_Y 69.1358 + +static void text_at(struct pdf *p, double x, double base, const char *font, + double size, const char *rgb, const char *s) +{ + if (s && *s) + pdf_text(p, x, base, font, size, rgb, s); +} + +static void text_right(struct pdf *p, double right, double base, + const char *font, double size, const char *rgb, + const char *s) +{ + if (s && *s) + pdf_text(p, right - pdf_text_width(font, size, s), base, font, size, + rgb, s); +} + +/* pdf_path consumes SVG-style y-down paths; wordmark.h stores y-up outline + coordinates, so negate the y of every point before drawing. */ +static void draw_wordmark(struct pdf *p, const char *path, double x, double y) +{ + size_t n = strlen(path); + char *flipped = xmalloc(2 * n + 2); + char op = 0; + int num = 0, in_num = 0, y_coord = 0; + size_t o = 0; + + for (size_t i = 0; i < n; i++) { + char c = path[i]; + + if ((c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z')) { + if (c == 'M' || c == 'L' || c == 'C') { + op = c; + num = 0; + } else if (c == 'Z') { + op = 0; + } + flipped[o++] = c; + in_num = 0; + continue; + } + if (!in_num && (c == '-' || c == '+' || c == '.' || + (c >= '0' && c <= '9'))) { + in_num = 1; + y_coord = op && (num % 2 == 1); + num++; + if (y_coord) { + if (c == '-') { + i++; + c = path[i]; + } else { + flipped[o++] = '-'; + } + } + } else if (!(c == '-' || c == '+' || c == '.' || + (c >= '0' && c <= '9'))) { + in_num = 0; + } + flipped[o++] = c; + } + flipped[o] = '\0'; + pdf_path(p, flipped, x, y, WHITE); + free(flipped); +} + +static void group_digits(char *out, size_t n, uint64_t v) +{ + char digits[24]; + int len = snprintf(digits, sizeof digits, "%llu", + (unsigned long long)v); + size_t o = 0; + + if (len < 0) + len = 0; + for (int i = 0; i < len && o + 1 < n; i++) { + if (i > 0 && (len - i) % 3 == 0) + out[o++] = ' '; + out[o++] = digits[i]; + } + out[o] = '\0'; +} + +static uint64_t ore_abs(int64_t v) +{ + return v < 0 ? (uint64_t)(-(v + 1)) + 1 : (uint64_t)v; +} + +static void fmt_kronor(int64_t ore, char *out, size_t n, int grouped) +{ + char whole[32]; + uint64_t kr = ore_abs(ore) / 100; + uint64_t rest = ore_abs(ore) % 100; + const char *sign = ore < 0 ? "-" : ""; + + if (grouped) + group_digits(whole, sizeof whole, kr); + else + snprintf(whole, sizeof whole, "%llu", (unsigned long long)kr); + snprintf(out, n, "%s%s,%02llu", sign, whole, (unsigned long long)rest); +} + +static void put_address(struct pdf *p, double x, double y, const char *rgb, + double size, const char *postal, const char *city) +{ + char buf[128]; + + if (!postal) + postal = ""; + if (!city) + city = ""; + if (*postal && *city) + snprintf(buf, sizeof buf, "%s %s", postal, city); + else + snprintf(buf, sizeof buf, "%s%s", postal, city); + if (*buf) + pdf_text(p, x, y, "H", size, rgb, buf); +} + +static void draw_info(struct pdf *p, const struct payslip_data *d) +{ + static const char *const labels[5] = { + "Anställd", "Personnummer", "Period", "Utbetalningsdatum", + "Skattetabell", + }; + char table[16]; + const char *values[5]; + + snprintf(table, sizeof table, "%d kol %d", d->tax_table, d->tax_column); + values[0] = d->employee_name; + values[1] = d->personal_no_masked; + values[2] = d->period; + values[3] = d->pay_date; + values[4] = table; + + for (size_t i = 0; i < 5; i++) { + double y = INFO_BASE_Y + (double)i * ROW_STEP; + + text_right(p, INFO_LABEL_RIGHT, y + LABEL_DY, "HB", SIZE_LABEL, INK, + labels[i]); + text_at(p, INFO_VALUE_X, y, "H", SIZE_VALUE, INK, values[i]); + } +} + +static void draw_amount_table(struct pdf *p, const struct payslip_data *d) +{ + char bruttolon[48], skatt[48], netto[48]; + + fmt_kronor(d->gross_ore, bruttolon, sizeof bruttolon, 1); + fmt_kronor(-d->tax_ore, skatt, sizeof skatt, 1); + fmt_kronor(d->net_ore, netto, sizeof netto, 1); + + pdf_fill_rect(p, PAGE_LEFT, BAR_TABLE_Y, PAGE_RIGHT_X - PAGE_LEFT, + BAR_TABLE_H, INK); + text_at(p, AMOUNT_LABEL_X, TABLE_HEADER_Y, "HB", SIZE_VALUE, WHITE, + "Specifikation"); + text_right(p, AMOUNT_RIGHT, TABLE_HEADER_Y, "H", SIZE_VALUE, WHITE, + d->run_ref); + + text_at(p, AMOUNT_LABEL_X, ROW_FIRST_Y, "H", SIZE_VALUE, INK, "Bruttolön"); + text_right(p, AMOUNT_RIGHT, ROW_FIRST_Y, "H", SIZE_VALUE, INK, bruttolon); + text_at(p, AMOUNT_LABEL_X, ROW_FIRST_Y + ROW_STEP, "H", SIZE_VALUE, INK, + "Preliminärskatt"); + text_right(p, AMOUNT_RIGHT, ROW_FIRST_Y + ROW_STEP, "H", SIZE_VALUE, INK, + skatt); + + pdf_line(p, PAGE_LEFT, AMOUNT_RULE_Y, PAGE_RIGHT_X, AMOUNT_RULE_Y, RULE_W, + RULE); + + text_at(p, AMOUNT_LABEL_X, ROW_FIRST_Y + 3 * ROW_STEP, "HB", SIZE_VALUE, + INK, "Nettolön"); + text_right(p, AMOUNT_RIGHT, ROW_FIRST_Y + 3 * ROW_STEP, "HB", SIZE_VALUE, + INK, netto); + + { + char note[96]; + int64_t whole = d->rate_bp / 100; + int64_t frac = d->rate_bp % 100; + + if (frac) + snprintf(note, sizeof note, + "Arbetsgivaravgifter %lld,%02lld %% betalas av" + " arbetsgivaren.", + (long long)whole, (long long)frac); + else + snprintf(note, sizeof note, + "Arbetsgivaravgifter %lld %% betalas av arbetsgivaren.", + (long long)whole); + text_at(p, AMOUNT_LABEL_X, ROW_FIRST_Y + 4 * ROW_STEP + NOTE_DY, "H", + SIZE_VALUE, INK, note); + } +} + +static void draw_footer(struct pdf *p, const struct payslip_data *d) +{ + pdf_line(p, PAGE_LEFT, RULE_FOOTER_Y, PAGE_RIGHT_X, RULE_FOOTER_Y, RULE_W, + RULE); + text_at(p, AMOUNT_LABEL_X, FOOT_LABEL_Y, "HB", SIZE_FOOT, INK, "Adress"); + text_at(p, INFO_VALUE_X, FOOT_LABEL_Y, "HB", SIZE_FOOT, INK, "Kontakt"); + text_at(p, FOOT_ORG_LABEL_X, FOOT_LABEL_Y, "HB", SIZE_FOOT, INK, "Orgnr"); + text_at(p, AMOUNT_LABEL_X, FOOT_NAME_Y, "H", SIZE_VALUE, INK, + d->employer.name); + text_at(p, INFO_VALUE_X, FOOT_NAME_Y, "H", SIZE_VALUE, INK, + d->employer.phone); + text_at(p, AMOUNT_LABEL_X, FOOT_ADDR_Y, "H", SIZE_VALUE, INK, + d->employer.address); + text_at(p, INFO_VALUE_X, FOOT_EMAIL_Y, "H", SIZE_VALUE, INK, + d->employer.email); + put_address(p, AMOUNT_LABEL_X, FOOT_CITY_Y, INK, SIZE_VALUE, + d->employer.postal_code, d->employer.city); + text_at(p, FOOT_ORG_X, FOOT_ORG_Y, "H", SIZE_VALUE, INK, + d->employer.org_nr); + text_at(p, FOOT_PAGE_RIGHT, FOOT_PAGE_Y, "H", SIZE_PAGE, INK, "1"); +} + +int payslip_render(const struct payslip_data *d, unsigned char **out, + size_t *len) +{ + struct pdf *p; + + if (out) + *out = NULL; + if (len) + *len = 0; + if (!d || !out || !len) + return -1; + + p = pdf_new(); + pdf_page(p); + pdf_fill_rect(p, PAGE_LEFT, BAR_HEADER_Y, PAGE_RIGHT_X - PAGE_LEFT, + BAR_HEADER_H, INK); + draw_wordmark(p, WORDMARK_MAKANDRA, WORDMARK_X, TITLE_BASE_Y); + text_right(p, TITLE_RIGHT, TITLE_BASE_Y, "HB", SIZE_TITLE, WHITE, + "LÖNEBESKED"); + pdf_line(p, PAGE_LEFT, RULE_HEADER_Y, PAGE_RIGHT_X, RULE_HEADER_Y, RULE_W, + RULE); + draw_info(p, d); + draw_amount_table(p, d); + draw_footer(p, d); + *out = pdf_finish(p, len); + return *out ? 0 : -1; +} diff --git a/src/payslip.h b/src/payslip.h new file mode 100644 index 0000000..291b35d --- /dev/null +++ b/src/payslip.h @@ -0,0 +1,34 @@ +#ifndef BOKF_PAYSLIP_H +#define BOKF_PAYSLIP_H + +#include +#include + +struct payslip_data { + struct { + const char *name; + const char *address; + const char *postal_code; + const char *city; + const char *org_nr; + const char *phone; + const char *email; + } employer; + const char *employee_name; + const char *personal_no_masked; + const char *period; + const char *pay_date; + int tax_table; + int tax_column; + int64_t gross_ore; + int64_t tax_ore; + int64_t avgifter_ore; + int64_t net_ore; + const char *run_ref; + int64_t rate_bp; +}; + +int payslip_render(const struct payslip_data *d, unsigned char **out, + size_t *len); + +#endif -- cgit v1.3