diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/cmd_bokslut.c | 9 | ||||
| -rw-r--r-- | src/cmd_invoices.c | 181 | ||||
| -rw-r--r-- | src/cmd_payroll.c | 12 | ||||
| -rw-r--r-- | src/cmd_settings.c | 64 | ||||
| -rw-r--r-- | src/db.c | 62 | ||||
| -rw-r--r-- | src/db.h | 5 | ||||
| -rw-r--r-- | src/invoice.c | 34 | ||||
| -rw-r--r-- | src/invoice.h | 2 | ||||
| -rw-r--r-- | src/ledger.c | 6 | ||||
| -rw-r--r-- | src/mail.c | 23 | ||||
| -rw-r--r-- | src/mail.h | 4 | ||||
| -rw-r--r-- | src/payslip.c | 62 | ||||
| -rw-r--r-- | src/payslip.h | 1 | ||||
| -rw-r--r-- | src/pdf.c | 36 | ||||
| -rw-r--r-- | src/pdf.h | 6 | ||||
| -rw-r--r-- | src/reports.c | 79 | ||||
| -rw-r--r-- | src/reports.h | 15 | ||||
| -rw-r--r-- | src/sie.c | 31 | ||||
| -rw-r--r-- | src/util.c | 13 | ||||
| -rw-r--r-- | src/util.h | 1 |
20 files changed, 539 insertions, 107 deletions
diff --git a/src/cmd_bokslut.c b/src/cmd_bokslut.c index c813923..f0ddee9 100644 --- a/src/cmd_bokslut.c +++ b/src/cmd_bokslut.c @@ -88,6 +88,9 @@ static yyjson_mut_val *h_bokslut_post(struct req *r) } int64_t result_before = 0; + char ib_series[16]; + db_setting_copy(r->db, r->org_id, "series_ib", "IB", ib_series, + sizeof ib_series); if (sqlite3_prepare_v2( r->db, "SELECT COALESCE(SUM(r.credit_ore)-SUM(r.debit_ore),0)" @@ -95,11 +98,13 @@ static yyjson_mut_val *h_bokslut_post(struct req *r) " JOIN vouchers v ON v.org_id=r.org_id AND v.id=r.voucher_id" " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id" " WHERE r.org_id=?1 AND v.fiscal_year_id=?2 AND v.series<>'IB'" + " AND v.series<>?3" " AND a.type IN ('revenue','expense') AND a.number NOT LIKE '89%'", -1, &st, NULL) != SQLITE_OK) return db_error(r); sqlite3_bind_int64(st, 1, r->org_id); sqlite3_bind_int64(st, 2, fy_id); + sqlite3_bind_text(st, 3, ib_series, -1, SQLITE_TRANSIENT); if (sqlite3_step(st) == SQLITE_ROW) result_before = sqlite3_column_int64(st, 0); sqlite3_finalize(st); @@ -154,6 +159,9 @@ static yyjson_mut_val *h_bokslut_post(struct req *r) plan[np++] = (typeof(plan[0])){ "Resultatdisposition", drows, 2 }; } + char series[16]; + db_setting_copy(r->db, r->org_id, "series_bokslut", "Å", series, + sizeof series); yyjson_mut_val *vouchers = yyjson_mut_arr(r->rdoc); for (size_t i = 0; i < np; i++) { struct ledger_post_opts o; @@ -165,6 +173,7 @@ static yyjson_mut_val *h_bokslut_post(struct req *r) o.description = plan[i].desc; o.rows = plan[i].rows; o.nrows = plan[i].nrows; + o.series = series; o.dry_run = r->dry_run; struct ledger_error e; char *json = NULL; diff --git a/src/cmd_invoices.c b/src/cmd_invoices.c index 0892ac8..a9d3fb1 100644 --- a/src/cmd_invoices.c +++ b/src/cmd_invoices.c @@ -11,6 +11,7 @@ #include "db.h" #include "invoice.h" #include "ledger.h" +#include "mail.h" #include "pdf.h" #include "secret.h" #include "smtp.h" @@ -139,6 +140,7 @@ struct draft_line { const char *note; const char *vat_code; char account[16]; + int is_text; }; struct invoice_draft { @@ -211,6 +213,21 @@ static int draft_line_parse(struct req *r, yyjson_val *item, size_t no, failf(r, "INVALID_ARGS", "row %zu: description is required", no); return -1; } + int is_text = 0; + arg_bool(item, "text", &is_text); + if (is_text) { + l->article_no = NULL; + l->description = description; + l->quantity_milli = 1; + l->unit = ""; + l->unit_price_ore = 0; + l->amount_ore = 0; + l->note = ""; + l->vat_code = "0"; + l->is_text = 1; + snprintf(l->account, sizeof l->account, "%s", default_account); + return 0; + } const char *qty = arg_str(item, "quantity"); int64_t quantity_milli = 0; if (parse_quantity(qty, &quantity_milli) != 0) { @@ -363,6 +380,7 @@ struct invoice_view { char seller_org_nr[64]; char seller_vat_nr[64]; char bankgiro[64]; + char header_color[8]; char customer_name[256]; char customer_address[1024]; char customer_postal[64]; @@ -450,6 +468,9 @@ static int invoice_view_fill(struct req *r, const struct invoice_draft *d, bankgiro && *bankgiro ? bankgiro : ""); free(bankgiro); + db_setting_copy(r->db, r->org_id, "document_header_color", "", + v->header_color, sizeof v->header_color); + v->lines = xcalloc(d->nlines, sizeof *v->lines); for (size_t i = 0; i < d->nlines; i++) { v->lines[i].article_no = d->lines[i].article_no; @@ -460,6 +481,7 @@ static int invoice_view_fill(struct req *r, const struct invoice_draft *d, v->lines[i].amount_ore = d->lines[i].amount_ore; v->lines[i].note = d->lines[i].note; v->lines[i].vat_code = d->lines[i].vat_code; + v->lines[i].is_text = d->lines[i].is_text; } v->doc.seller.name = v->seller_name; @@ -471,6 +493,7 @@ static int invoice_view_fill(struct req *r, const struct invoice_draft *d, v->doc.seller.org_nr = v->seller_org_nr; v->doc.seller.vat_nr = v->seller_vat_nr; v->doc.seller.bankgiro = v->bankgiro; + v->doc.header_color = v->header_color; v->doc.customer.name = v->customer_name; v->doc.customer.address = v->customer_address; v->doc.customer.postal_code = v->customer_postal; @@ -670,8 +693,8 @@ static int invoice_store_invoice(struct req *r, const struct invoice_draft *d, r->db, "INSERT INTO invoice_rows(org_id,invoice_id,line_no,article_no," "description,quantity_milli,unit,unit_price_ore,amount_ore,note," - "vat_code,account)" - " VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12)", + "vat_code,account,is_text)" + " VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13)", -1, &st, NULL) != SQLITE_OK) { db_error(r); return -1; @@ -689,6 +712,7 @@ static int invoice_store_invoice(struct req *r, const struct invoice_draft *d, sqlite3_bind_text(st, 10, l->note ? l->note : "", -1, SQLITE_TRANSIENT); sqlite3_bind_text(st, 11, l->vat_code, -1, SQLITE_TRANSIENT); sqlite3_bind_text(st, 12, l->account, -1, SQLITE_TRANSIENT); + sqlite3_bind_int(st, 13, l->is_text); rc = sqlite3_step(st); sqlite3_finalize(st); if (rc != SQLITE_DONE) { @@ -821,6 +845,9 @@ static yyjson_mut_val *h_invoice_issue(struct req *r) invoice_store_attachment(r, &v, pdf, pdf_len, &attachment_id) != 0) goto done; + char series[16]; + db_setting_copy(r->db, r->org_id, "series_invoice", "F", series, + sizeof series); struct ledger_post_opts o; memset(&o, 0, sizeof o); o.org_id = r->org_id; @@ -831,6 +858,7 @@ static yyjson_mut_val *h_invoice_issue(struct req *r) o.rows = vrows; o.nrows = vn; o.source = "invoice"; + o.series = series; o.dry_run = r->dry_run; o.already_in_tx = 1; struct ledger_error e; @@ -888,12 +916,12 @@ static yyjson_mut_val *invoice_row_json(struct req *r, sqlite3_stmt *st) return db_row_json(r->rdoc, st, "line_no:i,article_no:s,description:s," "quantity_milli:i,unit:s,unit_price_ore:i," - "amount_ore:i,note:s,vat_code:s,account:s"); + "amount_ore:i,note:s,vat_code:s,account:s,is_text:b"); } #define INVOICE_ROW_COLUMNS \ "line_no,article_no,description,quantity_milli,unit,unit_price_ore," \ - "amount_ore,note,vat_code,account" + "amount_ore,note,vat_code,account,is_text" static yyjson_mut_val *h_invoice_get(struct req *r) { @@ -906,9 +934,13 @@ static yyjson_mut_val *h_invoice_get(struct req *r) "SELECT i.id,i.customer_id,c.name,i.number,i.ocr,i.invoice_date," "i.due_date,i.delivery_date,i.your_ref,i.our_ref,i.notes,i.net_ore," "i.vat_ore,i.total_ore,i.status,i.document_id,i.voucher_id," - "i.last_sent_at,i.last_sent_to,i.created_at,i.created_by" + "i.last_sent_at,i.last_sent_to,i.created_at,i.created_by," + "i.paid_date,i.payment_voucher_id,COALESCE(pv.series,'')," + "COALESCE(pv.number,0)" " FROM invoices i JOIN customers c" " ON c.org_id=i.org_id AND c.id=i.customer_id" + " LEFT JOIN vouchers pv" + " ON pv.org_id=i.org_id AND pv.id=i.payment_voucher_id" " WHERE i.org_id=?1 AND i.id=?2", -1, &st, NULL) != SQLITE_OK) return db_error(r); @@ -971,6 +1003,17 @@ static yyjson_mut_val *h_invoice_get(struct req *r) sq(sqlite3_column_text(st, 19))); yyjson_mut_obj_add_int(r->rdoc, o, "created_by", sqlite3_column_int64(st, 20)); + yyjson_mut_obj_add_strcpy(r->rdoc, o, "paid_date", + sq(sqlite3_column_text(st, 21))); + if (sqlite3_column_type(st, 22) == SQLITE_NULL) + yyjson_mut_obj_add_null(r->rdoc, o, "payment_voucher_id"); + else + yyjson_mut_obj_add_int(r->rdoc, o, "payment_voucher_id", + sqlite3_column_int64(st, 22)); + yyjson_mut_obj_add_strcpy(r->rdoc, o, "paid_voucher_series", + sq(sqlite3_column_text(st, 23))); + yyjson_mut_obj_add_int(r->rdoc, o, "paid_voucher_number", + sqlite3_column_int64(st, 24)); sqlite3_finalize(st); yyjson_mut_val *rows = yyjson_mut_arr(r->rdoc); @@ -1006,7 +1049,7 @@ static yyjson_mut_val *h_invoice_list(struct req *r) r->db, "SELECT i.id,i.number,i.ocr,i.customer_id,c.name,i.invoice_date," "i.due_date,i.total_ore,i.status,i.document_id,i.voucher_id," - "i.last_sent_at,i.last_sent_to" + "i.last_sent_at,i.last_sent_to,i.paid_date" " FROM invoices i JOIN customers c" " ON c.org_id=i.org_id AND c.id=i.customer_id" " WHERE i.org_id=?1" @@ -1059,6 +1102,8 @@ static yyjson_mut_val *h_invoice_list(struct req *r) else yyjson_mut_obj_add_strcpy(r->rdoc, o, "last_sent_to", sq(sqlite3_column_text(st, 12))); + yyjson_mut_obj_add_strcpy(r->rdoc, o, "paid_date", + sq(sqlite3_column_text(st, 13))); } sqlite3_finalize(st); yyjson_mut_val *out = yyjson_mut_obj(r->rdoc); @@ -1211,6 +1256,10 @@ static yyjson_mut_val *h_invoice_send(struct req *r) fail(r, "SMTP_NOT_CONFIGURED", "smtp_host and smtp_from must be set"); goto done; } + if (!mail_addr_valid(smtp_from)) { + fail(r, "SMTP_NOT_CONFIGURED", "smtp_from must be an email address"); + goto done; + } const char *user = smtp_user && *smtp_user ? smtp_user : ""; if (*user) { if (!smtp_password || !*smtp_password) { @@ -1350,6 +1399,122 @@ static const struct cmd_arg args_invoice_sequence_set[] = { { "next_number", ARG_INT, 1, NULL, NULL, "Next invoice number" }, }; +static const struct cmd_arg args_invoice_pay[] = { + { "id", ARG_INT, 1, NULL, NULL, "Invoice id" }, + { "voucher_id", ARG_INT, 1, NULL, NULL, "Payment voucher id" }, +}; + +/* Marks an invoice paid and links the voucher that settles it. The voucher + must credit the invoice receivable account with the invoice total. */ +static yyjson_mut_val *h_invoice_pay(struct req *r) +{ + int64_t id = 0, voucher_id = 0; + if (!arg_int(r->args, "id", &id) || id <= 0) + return fail(r, "INVALID_ARGS", "id is required"); + if (!arg_int(r->args, "voucher_id", &voucher_id) || voucher_id <= 0) + return fail(r, "INVALID_ARGS", "voucher_id is required"); + + sqlite3_stmt *st = NULL; + if (sqlite3_prepare_v2( + r->db, + "SELECT number,total_ore,status,paid_date FROM invoices" + " 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", "invoice not found"); + } + int64_t number = sqlite3_column_int64(st, 0); + int64_t total = sqlite3_column_int64(st, 1); + char status[16], paid[16]; + snprintf(status, sizeof status, "%s", sq(sqlite3_column_text(st, 2))); + snprintf(paid, sizeof paid, "%s", sq(sqlite3_column_text(st, 3))); + sqlite3_finalize(st); + if (*paid) + return fail(r, "CONFLICT", "invoice is already paid"); + if (strcmp(status, "credited") == 0) + return fail(r, "INVALID_ARGS", "a credited invoice cannot be paid"); + + char receivable[16]; + db_setting_copy(r->db, r->org_id, "invoice_receivable_account", "1510", + receivable, sizeof receivable); + + char date[16], series[16] = ""; + int64_t vnumber = 0; + if (sqlite3_prepare_v2( + r->db, + "SELECT date,series,number FROM vouchers 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, voucher_id); + if (sqlite3_step(st) != SQLITE_ROW) { + sqlite3_finalize(st); + return fail(r, "NOT_FOUND", "voucher not found"); + } + snprintf(date, sizeof date, "%s", sq(sqlite3_column_text(st, 0))); + snprintf(series, sizeof series, "%s", sq(sqlite3_column_text(st, 1))); + vnumber = sqlite3_column_int64(st, 2); + sqlite3_finalize(st); + + if (sqlite3_prepare_v2( + r->db, + "SELECT COALESCE(SUM(r.credit_ore),0) FROM voucher_rows r" + " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id" + " WHERE r.org_id=?1 AND r.voucher_id=?2 AND a.number=?3", + -1, &st, NULL) != SQLITE_OK) + return db_error(r); + sqlite3_bind_int64(st, 1, r->org_id); + sqlite3_bind_int64(st, 2, voucher_id); + sqlite3_bind_text(st, 3, receivable, -1, SQLITE_TRANSIENT); + int64_t credited = 0; + if (sqlite3_step(st) == SQLITE_ROW) + credited = sqlite3_column_int64(st, 0); + sqlite3_finalize(st); + if (credited != total) + return failf(r, "INVALID_ARGS", + "the payment voucher must credit %s with the invoice" + " total (%lld), not (%lld)", + receivable, (long long)total, (long long)credited); + + if (!r->dry_run) { + if (sqlite3_prepare_v2( + r->db, + "UPDATE invoices SET paid_date=?3,payment_voucher_id=?4" + " WHERE org_id=?1 AND id=?2 AND paid_date=''", + -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_text(st, 3, date, -1, SQLITE_TRANSIENT); + sqlite3_bind_int64(st, 4, voucher_id); + 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, "CONFLICT", "invoice is already paid"); + char *reqjson = audit_args_json(r->args); + audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id, + "invoice.pay", 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_int(r->rdoc, o, "number", number); + yyjson_mut_obj_add_strcpy(r->rdoc, o, "paid_date", date); + yyjson_mut_obj_add_int(r->rdoc, o, "payment_voucher_id", voucher_id); + yyjson_mut_obj_add_strcpy(r->rdoc, o, "voucher_series", series); + yyjson_mut_obj_add_int(r->rdoc, o, "voucher_number", vnumber); + if (r->dry_run) + yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true); + return o; +} + static const struct cmd_arg args_invoice_draft[] = { { "customer_id", ARG_INT, 1, NULL, NULL, "Customer id" }, { "invoice_date", ARG_DATE, 1, NULL, NULL, "Invoice date (YYYY-MM-DD)" }, @@ -1360,7 +1525,7 @@ static const struct cmd_arg args_invoice_draft[] = { { "notes", ARG_STR, 0, NULL, NULL, "Free-text notes" }, { "rows", ARG_JSON, 1, NULL, NULL, "Array of {article_no,description,quantity,unit,unit_price_ore,note," - "vat_code,account}" }, + "vat_code,account,text}; text rows carry only the description" }, }; static const struct cmd_arg args_invoice_get[] = { @@ -1397,6 +1562,8 @@ const struct command g_cmd_invoices[] = { h_invoice_pdf, CMD_ARGS(args_invoice_get) }, { "invoice.send", "E-mail the stored invoice PDF to the customer", PERM_WRITE, 1, 1, 1, h_invoice_send, CMD_ARGS(args_invoice_send) }, + { "invoice.pay", "Mark an invoice paid with a payment voucher", + PERM_WRITE, 1, 1, 1, h_invoice_pay, CMD_ARGS(args_invoice_pay) }, }; const struct cmd_table g_cmd_table_invoices = { diff --git a/src/cmd_payroll.c b/src/cmd_payroll.c index b5db4f1..6dea293 100644 --- a/src/cmd_payroll.c +++ b/src/cmd_payroll.c @@ -491,6 +491,9 @@ static yyjson_mut_val *h_payroll_run_post(struct req *r) 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; @@ -501,6 +504,7 @@ static yyjson_mut_val *h_payroll_run_post(struct req *r) 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; @@ -758,6 +762,7 @@ struct payslip_ctx { char employer_org_nr[64]; char employer_phone[64]; char employer_email[256]; + char header_color[8]; char filename[320]; int64_t voucher_id; int64_t employee_id; @@ -920,6 +925,8 @@ static int payslip_prepare(struct req *r, struct payslip_ctx *c) struct payroll_cfg cfg; payroll_cfg_load(r, &cfg); snprintf(c->run_ref, sizeof c->run_ref, "Lönekörning %s", c->period); + db_setting_copy(r->db, r->org_id, "document_header_color", "", + c->header_color, sizeof c->header_color); char safe_name[200]; snprintf(safe_name, sizeof safe_name, "%s", c->employee_name); @@ -938,6 +945,7 @@ static int payslip_prepare(struct req *r, struct payslip_ctx *c) c->d.employer.email = c->employer_email; c->d.employee_name = c->employee_name; c->d.personal_no_masked = c->personal_no; + c->d.header_color = c->header_color; c->d.period = c->period; c->d.pay_date = c->pay_date; c->d.run_ref = c->run_ref; @@ -1341,6 +1349,9 @@ static yyjson_mut_val *h_payroll_pay_tax(struct req *r) 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; @@ -1351,6 +1362,7 @@ static yyjson_mut_val *h_payroll_pay_tax(struct req *r) 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) { diff --git a/src/cmd_settings.c b/src/cmd_settings.c index 066dc55..5bac3d9 100644 --- a/src/cmd_settings.c +++ b/src/cmd_settings.c @@ -8,6 +8,7 @@ #include "audit.h" #include "config.h" #include "db.h" +#include "mail.h" #include "secret.h" #include "util.h" @@ -20,6 +21,9 @@ static yyjson_mut_val *h_settings_get(struct req *r) yyjson_mut_val *o = yyjson_mut_obj(r->rdoc); int have_default = 0, have_bank = 0, have_receivable = 0, have_revenue = 0; int have_password = 0, have_security = 0; + int have_voucher = 0, have_invoice = 0, have_payroll = 0; + int have_bokslut = 0, have_ib = 0, have_header_color = 0; + char legacy_series[16] = "A"; sqlite3_stmt *st = NULL; if (sqlite3_prepare_v2( r->db, "SELECT key,value FROM settings WHERE org_id=?1", -1, &st, @@ -35,22 +39,48 @@ static yyjson_mut_val *h_settings_get(struct req *r) } yyjson_mut_obj_add(o, yyjson_mut_strcpy(r->rdoc, k), yyjson_mut_strcpy(r->rdoc, v ? v : "")); - if (strcmp(k, "default_series") == 0) + if (strcmp(k, "default_series") == 0) { have_default = 1; - if (strcmp(k, "bank_account") == 0) + if (v && *v) + snprintf(legacy_series, sizeof legacy_series, "%s", v); + } else if (strcmp(k, "series_voucher") == 0) { + have_voucher = 1; + } else if (strcmp(k, "series_invoice") == 0) { + have_invoice = 1; + } else if (strcmp(k, "series_payroll") == 0) { + have_payroll = 1; + } else if (strcmp(k, "series_bokslut") == 0) { + have_bokslut = 1; + } else if (strcmp(k, "series_ib") == 0) { + have_ib = 1; + } else if (strcmp(k, "bank_account") == 0) { have_bank = 1; - if (strcmp(k, "invoice_receivable_account") == 0) + } else if (strcmp(k, "invoice_receivable_account") == 0) { have_receivable = 1; - if (strcmp(k, "invoice_revenue_account") == 0) + } else if (strcmp(k, "invoice_revenue_account") == 0) { have_revenue = 1; - if (strcmp(k, "smtp_security") == 0) + } else if (strcmp(k, "document_header_color") == 0) { + have_header_color = 1; + } else if (strcmp(k, "smtp_security") == 0) { have_security = 1; + } } } sqlite3_finalize(st); } if (!have_default) yyjson_mut_obj_add_strcpy(r->rdoc, o, "default_series", "A"); + if (!have_voucher) + yyjson_mut_obj_add_strcpy(r->rdoc, o, "series_voucher", + legacy_series); + if (!have_invoice) + yyjson_mut_obj_add_strcpy(r->rdoc, o, "series_invoice", "F"); + if (!have_payroll) + yyjson_mut_obj_add_strcpy(r->rdoc, o, "series_payroll", "L"); + if (!have_bokslut) + yyjson_mut_obj_add_strcpy(r->rdoc, o, "series_bokslut", "Å"); + if (!have_ib) + yyjson_mut_obj_add_strcpy(r->rdoc, o, "series_ib", "IB"); if (!have_bank) yyjson_mut_obj_add_strcpy(r->rdoc, o, "bank_account", "1930"); if (!have_receivable) @@ -59,6 +89,9 @@ static yyjson_mut_val *h_settings_get(struct req *r) if (!have_revenue) yyjson_mut_obj_add_strcpy(r->rdoc, o, "invoice_revenue_account", "3001"); + if (!have_header_color) + yyjson_mut_obj_add_strcpy(r->rdoc, o, "document_header_color", + "#314c59"); if (!have_security) yyjson_mut_obj_add_strcpy(r->rdoc, o, "smtp_security", "starttls"); yyjson_mut_obj_add_bool(r->rdoc, o, "smtp_password_set", have_password); @@ -134,8 +167,13 @@ static yyjson_mut_val *h_settings_set(struct req *r) return settings_result(r, key, "[redacted]", 0); } size_t maxlen; - int digits_only = 0, bankgiro = 0, port = 0, security = 0; - if (strcmp(key, "default_series") == 0) + int digits_only = 0, bankgiro = 0, port = 0, security = 0, color = 0; + if (strcmp(key, "default_series") == 0 || + strcmp(key, "series_voucher") == 0 || + strcmp(key, "series_invoice") == 0 || + strcmp(key, "series_payroll") == 0 || + strcmp(key, "series_bokslut") == 0 || + strcmp(key, "series_ib") == 0) maxlen = 8; else if (strcmp(key, "attachment_dir") == 0 || strcmp(key, "smtp_host") == 0 || @@ -160,6 +198,9 @@ static yyjson_mut_val *h_settings_set(struct req *r) bankgiro = 1; } else if (strcmp(key, "invoice_our_ref") == 0) { maxlen = 64; + } else if (strcmp(key, "document_header_color") == 0) { + maxlen = 7; + color = 1; } else return fail(r, "UNSUPPORTED", "unknown setting"); size_t len = strlen(value); @@ -176,6 +217,8 @@ static yyjson_mut_val *h_settings_set(struct req *r) return failf(r, "INVALID_ARGS", "%s must not contain control characters", key); } + if (color && !util_hex_color_valid(value)) + return failf(r, "INVALID_ARGS", "%s must be #rrggbb", key); if (port) { long v = strtol(value, NULL, 10); if (v < 1 || v > 65535) @@ -185,6 +228,10 @@ static yyjson_mut_val *h_settings_set(struct req *r) strcmp(value, "tls") != 0 && strcmp(value, "plain") != 0) return failf(r, "INVALID_ARGS", "%s must be starttls, tls or plain", key); + if ((strcmp(key, "smtp_from") == 0 || + strcmp(key, "smtp_reply_to") == 0) && + !mail_addr_valid(value)) + return failf(r, "INVALID_ARGS", "%s must be an email address", key); if (r->dry_run) return settings_result(r, key, value, 1); sqlite3_stmt *st = NULL; @@ -215,7 +262,8 @@ static yyjson_mut_val *h_settings_set(struct req *r) static const struct cmd_arg args_settings_set[] = { { "key", ARG_STR, 1, NULL, NULL, "default_series, attachment_dir, bank_account," - " invoice_receivable_account, invoice_revenue_account, smtp_host," + " invoice_receivable_account, invoice_revenue_account," + " invoice_bankgiro, invoice_our_ref, document_header_color, smtp_host," " smtp_port, smtp_user, smtp_from, smtp_reply_to, smtp_security or" " smtp_password" }, { "value", ARG_STR, 0, NULL, NULL, @@ -351,6 +351,8 @@ static const char SCHEMA_V1[] = " CHECK (status IN ('issued','credited'))," " document_id INTEGER," " voucher_id INTEGER," + " paid_date TEXT NOT NULL DEFAULT ''," + " payment_voucher_id INTEGER," " last_sent_at TEXT," " last_sent_to TEXT," " created_at TEXT NOT NULL," @@ -359,7 +361,9 @@ static const char SCHEMA_V1[] = " UNIQUE (org_id, number)," " FOREIGN KEY (org_id, customer_id) REFERENCES customers(org_id, id)," " FOREIGN KEY (org_id, document_id) REFERENCES attachments(org_id, id)," - " FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(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 invoice_rows (" @@ -377,6 +381,7 @@ static const char SCHEMA_V1[] = " vat_code TEXT NOT NULL DEFAULT '25'" " CHECK (vat_code IN ('25','12','6','0','rc','eu'))," " account TEXT NOT NULL DEFAULT ''," + " is_text INTEGER NOT NULL DEFAULT 0," " UNIQUE (org_id, id)," " UNIQUE (org_id, invoice_id, line_no)," " FOREIGN KEY (org_id, invoice_id) REFERENCES invoices(org_id, id)" @@ -911,6 +916,49 @@ static int db_upgrade_v11(sqlite3 *db, char **err) err); } +static int db_column_exists(sqlite3 *db, const char *table, const char *col, + char **err) +{ + sqlite3_stmt *st = NULL; + char sql[160]; + snprintf(sql, sizeof sql, + "SELECT count(*) FROM pragma_table_info('%s') WHERE name='%s'", + table, col); + if (sqlite3_prepare_v2(db, sql, -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); + return have; +} + +static int db_add_column(sqlite3 *db, const char *table, const char *col, + const char *decl, char **err) +{ + int have = db_column_exists(db, table, col, err); + if (have != 0) + return have < 0 ? -1 : 0; + char sql[384]; + snprintf(sql, sizeof sql, "ALTER TABLE %s ADD COLUMN %s %s", table, col, + decl); + return db_exec(db, sql, err); +} + +/* v12: invoice text rows (is_text) and the payment link (paid_date, + payment_voucher_id). Fresh databases already carry the columns. */ +static int db_upgrade_v12(sqlite3 *db, char **err) +{ + if (db_add_column(db, "invoice_rows", "is_text", + "INTEGER NOT NULL DEFAULT 0", err) != 0) + return -1; + if (db_add_column(db, "invoices", "paid_date", + "TEXT NOT NULL DEFAULT ''", err) != 0) + return -1; + return db_add_column(db, "invoices", "payment_voucher_id", "INTEGER", + err); +} + static int db_upgrade(sqlite3 *db, int from, char **err) { if (db_exec(db, "BEGIN IMMEDIATE", err) != 0) @@ -955,6 +1003,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err) db_exec(db, "ROLLBACK", NULL); return -1; } + if (from < 12 && db_upgrade_v12(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); @@ -1215,6 +1267,14 @@ char *db_setting(sqlite3 *db, int64_t org_id, const char *key) return value; } +void db_setting_copy(sqlite3 *db, int64_t org_id, const char *key, + const char *def, char *out, size_t n) +{ + char *v = db_setting(db, org_id, key); + snprintf(out, n, "%s", v && *v ? v : def); + free(v); +} + char *db_membership_role(sqlite3 *db, int64_t org_id, int64_t user_id) { sqlite3_stmt *st = NULL; @@ -2,9 +2,10 @@ #define BOKF_DB_H #include <sqlite3.h> +#include <stddef.h> #include <stdint.h> -#define BOKF_SCHEMA_VERSION 11 +#define BOKF_SCHEMA_VERSION 12 int db_open(const char *path, sqlite3 **out, char **err); int db_migrate(sqlite3 *db, char **err); @@ -14,6 +15,8 @@ int db_create_user(sqlite3 *db, const char *username, const char *display_name, char **err); char *db_membership_role(sqlite3 *db, int64_t org_id, int64_t user_id); char *db_setting(sqlite3 *db, int64_t org_id, const char *key); +void db_setting_copy(sqlite3 *db, int64_t org_id, const char *key, + const char *def, char *out, size_t n); int64_t db_count(sqlite3 *db, const char *sql); int64_t db_last_id(sqlite3 *db); diff --git a/src/invoice.c b/src/invoice.c index 622c05a..1bdc0da 100644 --- a/src/invoice.c +++ b/src/invoice.c @@ -77,8 +77,12 @@ #define FOOT_CITY_Y 686.0238 #define FOOT_PAGE_Y 819.7954 -#define WORDMARK_X 21.742 +#define HEADER_NAME_X 21.742 #define FAKTURA_X 498.400 +#define HEADER_BASE_Y 69.1358 +#define HEADER_NAME_SIZE 16.0 +#define HEADER_NAME_MIN_SIZE 8.0 +#define HEADER_NAME_MAX_W 460.0 static void text_at(struct pdf *p, double x, double base, const char *font, double size, const char *rgb, const char *s) @@ -430,6 +434,24 @@ static void draw_table(struct pdf *p, const struct invoice_doc *d) const char *s = l->description ? l->description : ""; size_t line = 0; + if (l->is_text) { + for (;;) { + const char *nl = strchr(s, '\n'); + + if (nl) { + put_desc_line(p, y + desc_offset(line), s, + (size_t)(nl - s)); + s = nl + 1; + line++; + } else { + put_desc_line(p, y + desc_offset(line), s, strlen(s)); + break; + } + } + y += (double)(line + 2) * ROW_STEP; + continue; + } + fmt_quantity(l->quantity_milli, qty, sizeof qty); fmt_kronor(l->unit_price_ore, price, sizeof price, 0, 0); fmt_kronor(l->amount_ore, amount, sizeof amount, 0, 0); @@ -563,9 +585,13 @@ int invoice_render_pdf(const struct invoice_doc *d, unsigned char **out, 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, 69.1358); - draw_wordmark(p, WORDMARK_FAKTURA, FAKTURA_X, 69.1358); + BAR_HEADER_H, + util_hex_color_valid(d->header_color) ? d->header_color + : INK); + pdf_text_fit(p, HEADER_NAME_X, HEADER_BASE_Y, "HB", HEADER_NAME_SIZE, + HEADER_NAME_MIN_SIZE, HEADER_NAME_MAX_W, WHITE, + d->seller.name); + draw_wordmark(p, WORDMARK_FAKTURA, FAKTURA_X, HEADER_BASE_Y); draw_info(p, d); draw_customer(p, &d->customer); draw_table(p, d); diff --git a/src/invoice.h b/src/invoice.h index c92677d..55d1e3b 100644 --- a/src/invoice.h +++ b/src/invoice.h @@ -33,6 +33,7 @@ struct invoice_line { int64_t amount_ore; const char *note; const char *vat_code; + int is_text; }; struct invoice_doc { @@ -40,6 +41,7 @@ struct invoice_doc { struct invoice_customer customer; int64_t number; const char *ocr; + const char *header_color; /* "#rrggbb"; NULL or invalid = default */ const char *invoice_date; const char *due_date; const char *delivery_date; diff --git a/src/ledger.c b/src/ledger.c index a22e15e..b9aad47 100644 --- a/src/ledger.c +++ b/src/ledger.c @@ -360,7 +360,11 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o, char *series_owned = NULL; const char *series = o->series; if (!series || !*series) { - series_owned = db_setting(db, o->org_id, "default_series"); + series_owned = db_setting(db, o->org_id, "series_voucher"); + if (!series_owned || !*series_owned) { + free(series_owned); + series_owned = db_setting(db, o->org_id, "default_series"); + } series = series_owned && *series_owned ? series_owned : "A"; } if (!util_parse_iso_date(o->date)) { @@ -38,6 +38,21 @@ static void set_err(char *err, size_t errlen, const char *msg) snprintf(err, errlen, "%s", msg); } +int mail_addr_valid(const char *addr) +{ + if (!addr || !*addr) + return 0; + const char *at = strchr(addr, '@'); + if (!at || at == addr || !at[1] || strchr(at + 1, '@')) + return 0; + for (const char *p = addr; *p; p++) { + unsigned char ch = (unsigned char)*p; + if (ch <= 0x20 || ch == 0x7f || ch == '<' || ch == '>') + return 0; + } + return 1; +} + static int mail_cfg_load(sqlite3 *db, int64_t org_id, struct mail_cfg *c, char *err, size_t errlen) { @@ -54,6 +69,14 @@ static int mail_cfg_load(sqlite3 *db, int64_t org_id, struct mail_cfg *c, set_err(err, errlen, "smtp_host and smtp_from must be set"); goto fail; } + if (!mail_addr_valid(c->from)) { + set_err(err, errlen, "smtp_from must be an email address"); + goto fail; + } + if (c->reply_to && *c->reply_to && !mail_addr_valid(c->reply_to)) { + set_err(err, errlen, "smtp_reply_to must be an email address"); + goto fail; + } if (c->user && *c->user) { if (!c->password || !*c->password) { set_err(err, errlen, @@ -14,6 +14,10 @@ struct mail_message { size_t attach_len; }; +/* True for an address the SMTP client can send to: one @, no spaces or + control characters, not empty. */ +int mail_addr_valid(const char *addr); + /* 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); diff --git a/src/payslip.c b/src/payslip.c index 81ebded..ee8e008 100644 --- a/src/payslip.c +++ b/src/payslip.c @@ -6,7 +6,6 @@ #include "pdf.h" #include "util.h" -#include "wordmark.h" #define INK "#314c59" #define WHITE "#ffffff" @@ -52,9 +51,12 @@ #define FOOT_PAGE_Y 819.7954 #define FOOT_PAGE_RIGHT 567.4275 -#define WORDMARK_X 21.742 +#define HEADER_NAME_X 21.742 #define TITLE_RIGHT 574.892 #define TITLE_BASE_Y 69.1358 +#define HEADER_NAME_SIZE 16.0 +#define HEADER_NAME_MIN_SIZE 8.0 +#define HEADER_NAME_MAX_W 460.0 static void text_at(struct pdf *p, double x, double base, const char *font, double size, const char *rgb, const char *s) @@ -72,54 +74,6 @@ static void text_right(struct pdf *p, double right, double base, 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]; @@ -284,8 +238,12 @@ int payslip_render(const struct payslip_data *d, unsigned char **out, 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); + BAR_HEADER_H, + util_hex_color_valid(d->header_color) ? d->header_color + : INK); + pdf_text_fit(p, HEADER_NAME_X, TITLE_BASE_Y, "HB", HEADER_NAME_SIZE, + HEADER_NAME_MIN_SIZE, HEADER_NAME_MAX_W, WHITE, + d->employer.name); 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, diff --git a/src/payslip.h b/src/payslip.h index 291b35d..581cd81 100644 --- a/src/payslip.h +++ b/src/payslip.h @@ -16,6 +16,7 @@ struct payslip_data { } employer; const char *employee_name; const char *personal_no_masked; + const char *header_color; /* "#rrggbb"; NULL or invalid = default */ const char *period; const char *pay_date; int tax_table; @@ -353,6 +353,42 @@ double pdf_font_ascent(const char *font, double size) return size > 0 ? size * PDF_ASCENT / 1000.0 : 0.0; } +void pdf_text_fit(struct pdf *p, double x, double baseline, const char *font, + double size, double min_size, double max_w, const char *rgb, + const char *utf8) +{ + char *truncated = NULL; + const char *text = utf8; + double w; + + if (!utf8 || !*utf8 || max_w <= 0) + return; + w = pdf_text_width(font, size, utf8); + if (w > max_w) { + size = size * max_w / w; + if (size < min_size) + size = min_size; + w = pdf_text_width(font, size, utf8); + } + if (w > max_w) { + size_t n = strlen(utf8); + + truncated = xmalloc(n + 4); + memcpy(truncated, utf8, n); + while (n > 0) { + memcpy(truncated + n, "...", 4); + if (pdf_text_width(font, size, truncated) <= max_w) + break; + n--; + while (n > 0 && ((unsigned char)truncated[n] & 0xC0) == 0x80) + n--; + } + text = truncated; + } + pdf_text(p, x, baseline, font, size, rgb, text); + free(truncated); +} + static int parse_num(const char **sp, double *out) { const char *s = *sp; @@ -20,6 +20,12 @@ void pdf_text(struct pdf *p, double x, double baseline, const char *font, double size, const char *rgb, const char *utf8); double pdf_text_width(const char *font, double size, const char *utf8); double pdf_font_ascent(const char *font, double size); +/* Draws utf8 at x/baseline, shrinking the size (down to min_size) to fit + max_w; if it still does not fit, truncates at a whole UTF-8 character so + the text ends with "...". */ +void pdf_text_fit(struct pdf *p, double x, double baseline, const char *font, + double size, double min_size, double max_w, const char *rgb, + const char *utf8); void pdf_path(struct pdf *p, const char *path, double x, double y, const char *rgb); unsigned char *pdf_finish(struct pdf *p, size_t *len); diff --git a/src/reports.c b/src/reports.c index 1d4efdd..981d02c 100644 --- a/src/reports.c +++ b/src/reports.c @@ -4,6 +4,7 @@ #include <stdlib.h> #include <string.h> +#include "db.h" #include "util.h" struct fy_info { @@ -57,27 +58,36 @@ static yyjson_mut_val *fy_json(yyjson_mut_doc *doc, const struct fy_info *fy) return o; } -/* One row per account with IB and period movements. IB is the series "IB" - voucher(s) of this fiscal year plus all non-IB history before `from`. - Amounts signed: debit positive. */ +/* One row per account with IB and period movements; IB as defined by + REPORT_IB_ROW_SQL. skip_closings drops the source system's "Stäng ..." + closing vouchers (SIE-imported only) from the period movements: imported + years close the P&L accounts straight to 2099, so the year otherwise nets + to zero; the TUI årsredovisning uses the same convention. Amounts signed: + debit positive. */ static yyjson_mut_val *balance_query(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, int64_t fy_id, const char *from, const char *to, - const char *types_filter, char **err) + const char *types_filter, + int skip_closings, char **err) { - char sql[1280]; + char ib_series[16]; + db_setting_copy(db, org_id, "series_ib", "IB", ib_series, + sizeof ib_series); + char sql[2048]; snprintf(sql, sizeof sql, "SELECT a.number,a.name,a.type," - " COALESCE(SUM(CASE WHEN v.series <> 'IB'" + " COALESCE(SUM(CASE WHEN v.series <> 'IB' AND v.series <> ?5" + " AND (?6 = 0 OR v.source <> 'sie_import'" + " OR COALESCE(v.description,'') NOT LIKE 'Stäng%%')" " AND v.date BETWEEN ?2 AND ?3 THEN r.debit_ore END),0)," - " COALESCE(SUM(CASE WHEN v.series <> 'IB'" + " COALESCE(SUM(CASE WHEN v.series <> 'IB' AND v.series <> ?5" + " AND (?6 = 0 OR v.source <> 'sie_import'" + " OR COALESCE(v.description,'') NOT LIKE 'Stäng%%')" " AND v.date BETWEEN ?2 AND ?3 THEN r.credit_ore END),0)," - " COALESCE(SUM(CASE WHEN (v.series = 'IB'" - " AND v.fiscal_year_id = ?4) OR (v.series <> 'IB'" - " AND v.date < ?2) THEN r.debit_ore END),0)," - " COALESCE(SUM(CASE WHEN (v.series = 'IB'" - " AND v.fiscal_year_id = ?4) OR (v.series <> 'IB'" - " AND v.date < ?2) THEN r.credit_ore END),0)" + " COALESCE(SUM(CASE WHEN " REPORT_IB_ROW_SQL + " THEN r.debit_ore END),0)," + " COALESCE(SUM(CASE WHEN " REPORT_IB_ROW_SQL + " THEN r.credit_ore END),0)" " FROM accounts a" " LEFT JOIN (voucher_rows r JOIN vouchers v" " ON v.org_id=r.org_id AND v.id=r.voucher_id)" @@ -94,6 +104,8 @@ static yyjson_mut_val *balance_query(yyjson_mut_doc *doc, sqlite3 *db, sqlite3_bind_text(st, 2, from, -1, SQLITE_TRANSIENT); sqlite3_bind_text(st, 3, to, -1, SQLITE_TRANSIENT); sqlite3_bind_int64(st, 4, fy_id); + sqlite3_bind_text(st, 5, ib_series, -1, SQLITE_TRANSIENT); + sqlite3_bind_int(st, 6, skip_closings); yyjson_mut_val *arr = yyjson_mut_arr(doc); while (sqlite3_step(st) == SQLITE_ROW) { const char *number = (const char *)sqlite3_column_text(st, 0); @@ -128,7 +140,8 @@ yyjson_mut_val *report_trial_balance(yyjson_mut_doc *doc, sqlite3 *db, if (!to) to = fy.end; - yyjson_mut_val *rows = balance_query(doc, db, org_id, fy.id, from, to, NULL, err); + yyjson_mut_val *rows = balance_query(doc, db, org_id, fy.id, from, to, NULL, + 0, err); if (!rows) return NULL; @@ -187,7 +200,7 @@ yyjson_mut_val *report_income_statement(yyjson_mut_doc *doc, sqlite3 *db, yyjson_mut_val *rows = balance_query( doc, db, org_id, fy.id, from, to, - " AND a.type IN ('revenue','expense')", err); + " AND a.type IN ('revenue','expense')", 1, err); if (!rows) return NULL; @@ -244,7 +257,7 @@ yyjson_mut_val *report_balance_sheet(yyjson_mut_doc *doc, sqlite3 *db, yyjson_mut_val *rows = balance_query( doc, db, org_id, fy.id, fy.start, to, - " AND a.type IN ('asset','liability','equity')", err); + " AND a.type IN ('asset','liability','equity')", 0, err); if (!rows) return NULL; @@ -280,10 +293,11 @@ yyjson_mut_val *report_balance_sheet(yyjson_mut_doc *doc, sqlite3 *db, yyjson_mut_arr_add_val(*target, copy); } - /* current year result belongs to equity */ + /* current year result belongs to equity; the closings stay included so a + transferred result (2099) does not get counted twice */ yyjson_mut_val *inc = balance_query( doc, db, org_id, fy.id, fy.start, to, - " AND a.type IN ('revenue','expense')", err); + " AND a.type IN ('revenue','expense')", 0, err); int64_t result = 0; if (inc) { size_t m = yyjson_mut_arr_size(inc); @@ -365,6 +379,18 @@ static int vat_box_payable(const char *box) #define VAT_MAX_BOXES 64 +/* Vouchers left out of the momsdeklaration: the momsomföring itself (any + voucher with a 2650 row moves the period's VAT to the redovisningskonto + and would zero the boxes) and a source system's imported "Stäng ..." year + closings, which zero the P&L underlag. */ +#define VAT_VOUCHER_SQL \ + " AND NOT EXISTS (SELECT 1 FROM voucher_rows r2 JOIN accounts a2" \ + " ON a2.org_id = r2.org_id AND a2.id = r2.account_id" \ + " WHERE r2.org_id = v.org_id AND r2.voucher_id = v.id" \ + " AND a2.number = '2650')" \ + " AND NOT (v.source = 'sie_import'" \ + " AND COALESCE(v.description,'') LIKE 'Stäng%')" + yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, const char *from, const char *to, char **err) { @@ -377,6 +403,9 @@ yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, int64_t amount; } acc[VAT_MAX_BOXES]; size_t nacc = 0; + char ib_series[16]; + db_setting_copy(db, org_id, "series_ib", "IB", ib_series, + sizeof ib_series); sqlite3_stmt *st = NULL; if (sqlite3_prepare_v2( db, @@ -401,21 +430,25 @@ yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, " JOIN vouchers v ON v.org_id=r.org_id AND v.id=r.voucher_id" " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id" " WHERE r.org_id=?1 AND a.number=?2 AND v.series <> 'IB'" - " AND v.date BETWEEN ?3 AND ?4"; + " AND v.series <> ?5 AND v.date BETWEEN ?3 AND ?4" + VAT_VOUCHER_SQL; else if (strcmp(mt, "type") == 0) sql = "SELECT COALESCE(SUM(r.debit_ore),0)," "COALESCE(SUM(r.credit_ore),0) FROM voucher_rows r" " JOIN vouchers v ON v.org_id=r.org_id AND v.id=r.voucher_id" " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id" " WHERE r.org_id=?1 AND a.type=?2 AND v.series <> 'IB'" - " AND v.date BETWEEN ?3 AND ?4"; + " AND v.series <> ?5 AND v.date BETWEEN ?3 AND ?4" + VAT_VOUCHER_SQL; else sql = "SELECT COALESCE(SUM(r.debit_ore),0)," "COALESCE(SUM(r.credit_ore),0) FROM voucher_rows r" " JOIN vouchers v ON v.org_id=r.org_id AND v.id=r.voucher_id" " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id" " WHERE r.org_id=?1 AND CAST(a.number AS INTEGER) BETWEEN ?2" - " AND ?3 AND v.series <> 'IB' AND v.date BETWEEN ?4 AND ?5"; + " AND ?3 AND v.series <> 'IB' AND v.series <> ?6" + " AND v.date BETWEEN ?4 AND ?5" + VAT_VOUCHER_SQL; if (sqlite3_prepare_v2(db, sql, -1, &qs, NULL) != SQLITE_OK) break; sqlite3_bind_int64(qs, 1, org_id); @@ -423,6 +456,7 @@ yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, sqlite3_bind_text(qs, 2, pattern, -1, SQLITE_TRANSIENT); sqlite3_bind_text(qs, 3, from, -1, SQLITE_TRANSIENT); sqlite3_bind_text(qs, 4, to, -1, SQLITE_TRANSIENT); + sqlite3_bind_text(qs, 5, ib_series, -1, SQLITE_TRANSIENT); } else { long lo = 0, hi = 0; if (sscanf(pattern, "%ld-%ld", &lo, &hi) != 2) { @@ -433,6 +467,7 @@ yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, sqlite3_bind_int64(qs, 3, hi); sqlite3_bind_text(qs, 4, from, -1, SQLITE_TRANSIENT); sqlite3_bind_text(qs, 5, to, -1, SQLITE_TRANSIENT); + sqlite3_bind_text(qs, 6, ib_series, -1, SQLITE_TRANSIENT); } if (sqlite3_step(qs) == SQLITE_ROW) amount = (sqlite3_column_int64(qs, 0) - @@ -532,7 +567,7 @@ yyjson_mut_val *report_general_ledger(yyjson_mut_doc *doc, sqlite3 *db, if (!to) to = fy.end; yyjson_mut_val *bal = - balance_query(doc, db, org_id, fy.id, from, to, NULL, err); + balance_query(doc, db, org_id, fy.id, from, to, NULL, 0, err); if (!bal) return NULL; sqlite3_stmt *rs = NULL; diff --git a/src/reports.h b/src/reports.h index c4a6e4e..82c29e5 100644 --- a/src/reports.h +++ b/src/reports.h @@ -6,6 +6,21 @@ #include "yyjson.h" +/* SQL condition: voucher row `r` of voucher `v` on account `a` belongs to the + opening balance of fiscal year ?4 (org ?1) for a period starting ?2; ?5 is + the configured IB series. Balance accounts carry all earlier history, + including earlier years' IB vouchers; P&L accounts restart at the year + start, so only this year's IB vouchers and movements before ?2 count. */ +#define REPORT_IB_ROW_SQL \ + "((v.series = 'IB' OR v.series = ?5) AND (v.fiscal_year_id = ?4" \ + " OR (v.date < (SELECT f.start_date FROM fiscal_years f" \ + " WHERE f.org_id = ?1 AND f.id = ?4)" \ + " AND a.type NOT IN ('revenue','expense'))))" \ + " OR (v.series <> 'IB' AND v.series <> ?5 AND v.date < ?2" \ + " AND (a.type NOT IN ('revenue','expense') OR v.date >=" \ + " (SELECT f.start_date FROM fiscal_years f" \ + " WHERE f.org_id = ?1 AND f.id = ?4)))" + yyjson_mut_val *report_trial_balance(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, int64_t fy_id, const char *from, const char *to, @@ -8,8 +8,10 @@ #include <string.h> #include <time.h> +#include "db.h" #include "ledger.h" #include "log.h" +#include "reports.h" #include "util.h" #include "version.h" @@ -135,20 +137,21 @@ int sie_export_file(sqlite3 *db, int64_t org_id, int64_t fy_id, put_date(f, end); put(f, "\n"); - /* accounts and balances: IB = this year's series "IB" voucher(s) plus - all non-IB history before the year; UB = everything through the end; - RES = the year's non-IB movements. */ + /* accounts and balances: IB per REPORT_IB_ROW_SQL, UB = IB plus the + year's non-IB movements, RES = those movements. #IB/#UB are written + for balance accounts only, #RES for P&L accounts only. */ + char ib_series[16]; + db_setting_copy(db, org_id, "series_ib", "IB", ib_series, + sizeof ib_series); if (sqlite3_prepare_v2( db, "SELECT a.number,a.name," - " COALESCE(SUM(CASE WHEN (v.series = 'IB'" - " AND v.fiscal_year_id = ?4) OR (v.series <> 'IB'" - " AND v.date < ?2) THEN r.debit_ore - r.credit_ore END),0)," - " COALESCE(SUM(CASE WHEN v.date <= ?3" + " COALESCE(SUM(CASE WHEN " REPORT_IB_ROW_SQL " THEN r.debit_ore - r.credit_ore END),0)," - " COALESCE(SUM(CASE WHEN v.series <> 'IB'" + " COALESCE(SUM(CASE WHEN v.series <> 'IB' AND v.series <> ?5" " AND v.date >= ?2 AND v.date <= ?3" - " THEN r.debit_ore - r.credit_ore END),0)" + " THEN r.debit_ore - r.credit_ore END),0)," + " a.type IN ('revenue','expense')" " FROM accounts a LEFT JOIN (voucher_rows r JOIN vouchers v" " ON v.org_id=r.org_id AND v.id=r.voucher_id)" " ON r.org_id=a.org_id AND r.account_id=a.id" @@ -163,12 +166,18 @@ int sie_export_file(sqlite3 *db, int64_t org_id, int64_t fy_id, sqlite3_bind_text(st, 2, start, -1, SQLITE_TRANSIENT); sqlite3_bind_text(st, 3, end, -1, SQLITE_TRANSIENT); sqlite3_bind_int64(st, 4, fy_id); + sqlite3_bind_text(st, 5, ib_series, -1, SQLITE_TRANSIENT); while (sqlite3_step(st) == SQLITE_ROW) { const char *number = (const char *)sqlite3_column_text(st, 0); const char *name = (const char *)sqlite3_column_text(st, 1); int64_t ib = sqlite3_column_int64(st, 2); - int64_t ub = sqlite3_column_int64(st, 3); - int64_t res = sqlite3_column_int64(st, 4); + int64_t res = sqlite3_column_int64(st, 3); + int pl = sqlite3_column_int(st, 4); + int64_t ub = ib + res; + if (pl) + ib = ub = 0; + else + res = 0; put(f, "#KONTO "); put(f, number); put(f, " "); @@ -276,6 +276,19 @@ char *util_str_trim(char *s) return s; } +int util_hex_color_valid(const char *s) +{ + if (!s || s[0] != '#' || strlen(s) != 7) + return 0; + for (int i = 1; i < 7; i++) { + char c = s[i]; + if (!((c >= '0' && c <= '9') || (c >= 'a' && c <= 'f') || + (c >= 'A' && c <= 'F'))) + return 0; + } + return 1; +} + static int is_leap(int y) { return (y % 4 == 0 && y % 100 != 0) || y % 400 == 0; @@ -32,6 +32,7 @@ int util_const_eq(const void *a, const void *b, size_t n); int64_t util_now(void); void util_iso8601(int64_t t, char *buf, size_t n); char *util_str_trim(char *s); +int util_hex_color_valid(const char *s); int util_parse_iso_date(const char *s); /* base64 (standard alphabet, padding required) */ |