diff options
Diffstat (limited to 'src/reports.c')
| -rw-r--r-- | src/reports.c | 434 |
1 files changed, 434 insertions, 0 deletions
diff --git a/src/reports.c b/src/reports.c new file mode 100644 index 0000000..1bfdcfb --- /dev/null +++ b/src/reports.c @@ -0,0 +1,434 @@ +#include "reports.h" + +#include <stdio.h> +#include <stdlib.h> +#include <string.h> + +#include "util.h" + +struct fy_info { + int64_t id; + char label[64]; + char start[16]; + char end[16]; +}; + +static void set_err(char **err, const char *msg) +{ + if (err && !*err) + *err = xstrdup(msg); +} + +static int load_fy(sqlite3 *db, int64_t org_id, int64_t fy_id, + struct fy_info *fy, char **err) +{ + sqlite3_stmt *st = NULL; + int rc = sqlite3_prepare_v2( + db, + "SELECT id,label,start_date,end_date FROM fiscal_years" + " WHERE org_id=?1 AND id=?2", + -1, &st, NULL); + if (rc != SQLITE_OK) { + set_err(err, "database error"); + return -1; + } + sqlite3_bind_int64(st, 1, org_id); + sqlite3_bind_int64(st, 2, fy_id); + if (sqlite3_step(st) != SQLITE_ROW) { + sqlite3_finalize(st); + set_err(err, "fiscal year not found"); + return -1; + } + fy->id = sqlite3_column_int64(st, 0); + snprintf(fy->label, sizeof fy->label, "%s", sqlite3_column_text(st, 1)); + snprintf(fy->start, sizeof fy->start, "%s", sqlite3_column_text(st, 2)); + snprintf(fy->end, sizeof fy->end, "%s", sqlite3_column_text(st, 3)); + sqlite3_finalize(st); + return 0; +} + +static yyjson_mut_val *fy_json(yyjson_mut_doc *doc, const struct fy_info *fy) +{ + yyjson_mut_val *o = yyjson_mut_obj(doc); + yyjson_mut_obj_add_int(doc, o, "id", fy->id); + yyjson_mut_obj_add_strcpy(doc, o, "label", fy->label); + yyjson_mut_obj_add_strcpy(doc, o, "start_date", fy->start); + yyjson_mut_obj_add_strcpy(doc, o, "end_date", fy->end); + 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. */ +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) +{ + char sql[1280]; + snprintf(sql, sizeof sql, + "SELECT a.number,a.name,a.type," + " COALESCE(SUM(CASE WHEN v.series <> 'IB'" + " AND v.date BETWEEN ?2 AND ?3 THEN r.debit_ore END),0)," + " COALESCE(SUM(CASE WHEN v.series <> 'IB'" + " 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)" + " 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" + " WHERE a.org_id=?1 AND a.active=1%s" + " GROUP BY a.id ORDER BY a.number", + types_filter ? types_filter : ""); + sqlite3_stmt *st = NULL; + if (sqlite3_prepare_v2(db, sql, -1, &st, NULL) != SQLITE_OK) { + set_err(err, "database error"); + return NULL; + } + sqlite3_bind_int64(st, 1, org_id); + 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); + yyjson_mut_val *arr = yyjson_mut_arr(doc); + 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); + const char *type = (const char *)sqlite3_column_text(st, 2); + int64_t debit = sqlite3_column_int64(st, 3); + int64_t credit = sqlite3_column_int64(st, 4); + int64_t ib = sqlite3_column_int64(st, 5) - sqlite3_column_int64(st, 6); + yyjson_mut_val *o = yyjson_mut_arr_add_obj(doc, arr); + yyjson_mut_obj_add_strcpy(doc, o, "account", number ? number : ""); + yyjson_mut_obj_add_strcpy(doc, o, "name", name ? name : ""); + yyjson_mut_obj_add_strcpy(doc, o, "type", type ? type : ""); + yyjson_mut_obj_add_int(doc, o, "ib_ore", ib); + yyjson_mut_obj_add_int(doc, o, "debit_ore", debit); + yyjson_mut_obj_add_int(doc, o, "credit_ore", credit); + yyjson_mut_obj_add_int(doc, o, "ub_ore", ib + debit - credit); + } + sqlite3_finalize(st); + return arr; +} + +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, + int include_zero, char **err) +{ + struct fy_info fy; + if (load_fy(db, org_id, fy_id, &fy, err) != 0) + return NULL; + if (!from) + from = fy.start; + if (!to) + to = fy.end; + + yyjson_mut_val *rows = balance_query(doc, db, org_id, fy.id, from, to, NULL, err); + if (!rows) + return NULL; + + yyjson_mut_val *filtered = include_zero ? rows : yyjson_mut_arr(doc); + int64_t t_debit = 0, t_credit = 0, t_ib = 0, t_ub = 0; + if (!include_zero) { + size_t n = yyjson_mut_arr_size(rows); + for (size_t i = 0; i < n; i++) { + yyjson_mut_val *row = yyjson_mut_arr_get(rows, i); + yyjson_mut_val *ibv = yyjson_mut_obj_get(row, "ib_ore"); + yyjson_mut_val *dv = yyjson_mut_obj_get(row, "debit_ore"); + yyjson_mut_val *cv = yyjson_mut_obj_get(row, "credit_ore"); + int64_t ib = ibv ? yyjson_mut_get_int(ibv) : 0; + int64_t d = dv ? yyjson_mut_get_int(dv) : 0; + int64_t c = cv ? yyjson_mut_get_int(cv) : 0; + if (ib == 0 && d == 0 && c == 0) + continue; + yyjson_mut_arr_add_val(filtered, yyjson_mut_val_mut_copy(doc, row)); + } + } + size_t n = yyjson_mut_arr_size(filtered); + for (size_t i = 0; i < n; i++) { + yyjson_mut_val *row = yyjson_mut_arr_get(filtered, i); + t_ib += yyjson_mut_get_int(yyjson_mut_obj_get(row, "ib_ore")); + t_debit += yyjson_mut_get_int(yyjson_mut_obj_get(row, "debit_ore")); + t_credit += yyjson_mut_get_int(yyjson_mut_obj_get(row, "credit_ore")); + t_ub += yyjson_mut_get_int(yyjson_mut_obj_get(row, "ub_ore")); + } + + yyjson_mut_val *o = yyjson_mut_obj(doc); + yyjson_mut_obj_add_val(doc, o, "fiscal_year", fy_json(doc, &fy)); + yyjson_mut_obj_add_strcpy(doc, o, "from", from); + yyjson_mut_obj_add_strcpy(doc, o, "to", to); + yyjson_mut_obj_add_val(doc, o, "accounts", filtered); + yyjson_mut_val *tot = yyjson_mut_obj(doc); + yyjson_mut_obj_add_int(doc, tot, "ib_ore", t_ib); + yyjson_mut_obj_add_int(doc, tot, "debit_ore", t_debit); + yyjson_mut_obj_add_int(doc, tot, "credit_ore", t_credit); + yyjson_mut_obj_add_int(doc, tot, "ub_ore", t_ub); + yyjson_mut_obj_add_val(doc, o, "totals", tot); + return o; +} + +yyjson_mut_val *report_income_statement(yyjson_mut_doc *doc, sqlite3 *db, + int64_t org_id, int64_t fy_id, + const char *from, const char *to, + char **err) +{ + struct fy_info fy; + if (load_fy(db, org_id, fy_id, &fy, err) != 0) + return NULL; + if (!from) + from = fy.start; + if (!to) + to = fy.end; + + yyjson_mut_val *rows = balance_query( + doc, db, org_id, fy.id, from, to, + " AND a.type IN ('revenue','expense')", err); + if (!rows) + return NULL; + + yyjson_mut_val *rev = yyjson_mut_arr(doc); + yyjson_mut_val *exp = yyjson_mut_arr(doc); + int64_t rev_total = 0, exp_total = 0; + size_t n = yyjson_mut_arr_size(rows); + for (size_t i = 0; i < n; i++) { + yyjson_mut_val *row = yyjson_mut_arr_get(rows, i); + const char *type = + yyjson_mut_get_str(yyjson_mut_obj_get(row, "type")); + int64_t d = yyjson_mut_get_int(yyjson_mut_obj_get(row, "debit_ore")); + int64_t c = yyjson_mut_get_int(yyjson_mut_obj_get(row, "credit_ore")); + if (d == 0 && c == 0) + continue; + int is_rev = type && strcmp(type, "revenue") == 0; + int64_t amount = is_rev ? c - d : d - c; + yyjson_mut_val *copy = yyjson_mut_val_mut_copy(doc, row); + yyjson_mut_obj_add_int(doc, copy, "amount_ore", amount); + if (is_rev) { + yyjson_mut_arr_add_val(rev, copy); + rev_total += amount; + } else { + yyjson_mut_arr_add_val(exp, copy); + exp_total += amount; + } + } + + yyjson_mut_val *o = yyjson_mut_obj(doc); + yyjson_mut_obj_add_val(doc, o, "fiscal_year", fy_json(doc, &fy)); + yyjson_mut_obj_add_strcpy(doc, o, "from", from); + yyjson_mut_obj_add_strcpy(doc, o, "to", to); + yyjson_mut_val *rsec = yyjson_mut_obj(doc); + yyjson_mut_obj_add_val(doc, rsec, "accounts", rev); + yyjson_mut_obj_add_int(doc, rsec, "total_ore", rev_total); + yyjson_mut_obj_add_val(doc, o, "revenue", rsec); + yyjson_mut_val *esec = yyjson_mut_obj(doc); + yyjson_mut_obj_add_val(doc, esec, "accounts", exp); + yyjson_mut_obj_add_int(doc, esec, "total_ore", exp_total); + yyjson_mut_obj_add_val(doc, o, "expenses", esec); + yyjson_mut_obj_add_int(doc, o, "result_ore", rev_total - exp_total); + return o; +} + +yyjson_mut_val *report_balance_sheet(yyjson_mut_doc *doc, sqlite3 *db, + int64_t org_id, int64_t fy_id, + const char *to, char **err) +{ + struct fy_info fy; + if (load_fy(db, org_id, fy_id, &fy, err) != 0) + return NULL; + if (!to) + to = fy.end; + + yyjson_mut_val *rows = balance_query( + doc, db, org_id, fy.id, fy.start, to, + " AND a.type IN ('asset','liability','equity')", err); + if (!rows) + return NULL; + + yyjson_mut_val *assets = yyjson_mut_arr(doc); + yyjson_mut_val *liab = yyjson_mut_arr(doc); + yyjson_mut_val *equity = yyjson_mut_arr(doc); + int64_t a_total = 0, l_total = 0, e_total = 0; + size_t n = yyjson_mut_arr_size(rows); + for (size_t i = 0; i < n; i++) { + yyjson_mut_val *row = yyjson_mut_arr_get(rows, i); + const char *type = + yyjson_mut_get_str(yyjson_mut_obj_get(row, "type")); + int64_t ub = yyjson_mut_get_int(yyjson_mut_obj_get(row, "ub_ore")); + if (ub == 0) + continue; + int64_t amount; + yyjson_mut_val **target; + if (type && strcmp(type, "asset") == 0) { + amount = ub; + target = &assets; + a_total += amount; + } else if (type && strcmp(type, "liability") == 0) { + amount = -ub; + target = &liab; + l_total += amount; + } else { + amount = -ub; + target = &equity; + e_total += amount; + } + yyjson_mut_val *copy = yyjson_mut_val_mut_copy(doc, row); + yyjson_mut_obj_add_int(doc, copy, "amount_ore", amount); + yyjson_mut_arr_add_val(*target, copy); + } + + /* current year result belongs to equity */ + yyjson_mut_val *inc = balance_query( + doc, db, org_id, fy.id, fy.start, to, + " AND a.type IN ('revenue','expense')", err); + int64_t result = 0; + if (inc) { + size_t m = yyjson_mut_arr_size(inc); + for (size_t i = 0; i < m; i++) { + yyjson_mut_val *row = yyjson_mut_arr_get(inc, i); + int64_t d = + yyjson_mut_get_int(yyjson_mut_obj_get(row, "debit_ore")); + int64_t c = + yyjson_mut_get_int(yyjson_mut_obj_get(row, "credit_ore")); + const char *type = + yyjson_mut_get_str(yyjson_mut_obj_get(row, "type")); + if (type && strcmp(type, "revenue") == 0) + result += c - d; + else + result -= d - c; + } + } + e_total += result; + + yyjson_mut_val *o = yyjson_mut_obj(doc); + yyjson_mut_obj_add_val(doc, o, "fiscal_year", fy_json(doc, &fy)); + yyjson_mut_obj_add_strcpy(doc, o, "to", to); + yyjson_mut_val *asec = yyjson_mut_obj(doc); + yyjson_mut_obj_add_val(doc, asec, "accounts", assets); + yyjson_mut_obj_add_int(doc, asec, "total_ore", a_total); + yyjson_mut_obj_add_val(doc, o, "assets", asec); + yyjson_mut_val *lsec = yyjson_mut_obj(doc); + yyjson_mut_obj_add_val(doc, lsec, "accounts", liab); + yyjson_mut_obj_add_int(doc, lsec, "total_ore", l_total); + yyjson_mut_obj_add_val(doc, o, "liabilities", lsec); + yyjson_mut_val *esec = yyjson_mut_obj(doc); + yyjson_mut_obj_add_val(doc, esec, "accounts", equity); + yyjson_mut_obj_add_int(doc, esec, "total_ore", e_total); + yyjson_mut_obj_add_int(doc, esec, "result_ore", result); + yyjson_mut_obj_add_val(doc, o, "equity", esec); + return o; +} + +struct box_label { + const char *box; + const char *label; +}; + +static const struct box_label BOX_LABELS[] = { + { "05", "Momspliktig försäljning 25%" }, + { "06", "Momspliktig försäljning 12%" }, + { "07", "Momspliktig försäljning 6%" }, + { "10", "Utgående moms 25%" }, + { "11", "Utgående moms 12%" }, + { "12", "Utgående moms 6%" }, + { "48", "Ingående moms" }, +}; + +static const char *box_label(const char *box) +{ + for (size_t i = 0; i < sizeof BOX_LABELS / sizeof BOX_LABELS[0]; i++) + if (strcmp(BOX_LABELS[i].box, box) == 0) + return BOX_LABELS[i].label; + return "Ruta"; +} + +yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, + const char *from, const char *to, char **err) +{ + if (!util_parse_iso_date(from) || !util_parse_iso_date(to)) { + set_err(err, "from and to must be YYYY-MM-DD"); + return NULL; + } + yyjson_mut_val *boxes = yyjson_mut_arr(doc); + int64_t out_total = 0, in_total = 0; + sqlite3_stmt *st = NULL; + if (sqlite3_prepare_v2( + db, + "SELECT box,match_type,pattern,sign FROM report_rules" + " WHERE org_id=?1 AND report='vat' ORDER BY sort_order", + -1, &st, NULL) != SQLITE_OK) { + set_err(err, "database error"); + return NULL; + } + sqlite3_bind_int64(st, 1, org_id); + while (sqlite3_step(st) == SQLITE_ROW) { + const char *box = (const char *)sqlite3_column_text(st, 0); + const char *mt = (const char *)sqlite3_column_text(st, 1); + const char *pattern = (const char *)sqlite3_column_text(st, 2); + int sign = sqlite3_column_int(st, 3); + int64_t amount = 0; + sqlite3_stmt *qs = NULL; + const char *sql = + strcmp(mt, "account") == 0 + ? "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.number=?2 AND v.series <> 'IB'" + " AND v.date BETWEEN ?3 AND ?4" + : "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"; + if (sqlite3_prepare_v2(db, sql, -1, &qs, NULL) != SQLITE_OK) + break; + sqlite3_bind_int64(qs, 1, org_id); + if (strcmp(mt, "account") == 0) { + 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); + } else { + long lo = 0, hi = 0; + if (sscanf(pattern, "%ld-%ld", &lo, &hi) != 2) { + sqlite3_finalize(qs); + continue; + } + sqlite3_bind_int64(qs, 2, lo); + sqlite3_bind_int64(qs, 3, hi); + sqlite3_bind_text(qs, 4, from, -1, SQLITE_TRANSIENT); + sqlite3_bind_text(qs, 5, to, -1, SQLITE_TRANSIENT); + } + if (sqlite3_step(qs) == SQLITE_ROW) + amount = (sqlite3_column_int64(qs, 0) - + sqlite3_column_int64(qs, 1)) * + sign; + sqlite3_finalize(qs); + if (strcmp(box, "48") == 0) + in_total += amount; + else + out_total += amount; + yyjson_mut_val *o = yyjson_mut_arr_add_obj(doc, boxes); + yyjson_mut_obj_add_strcpy(doc, o, "box", box); + yyjson_mut_obj_add_strcpy(doc, o, "label", box_label(box)); + yyjson_mut_obj_add_int(doc, o, "amount_ore", amount); + } + sqlite3_finalize(st); + yyjson_mut_val *o = yyjson_mut_arr_add_obj(doc, boxes); + yyjson_mut_obj_add_strcpy(doc, o, "box", "49"); + yyjson_mut_obj_add_strcpy(doc, o, "label", "Moms att betala eller få tillbaka"); + yyjson_mut_obj_add_int(doc, o, "amount_ore", out_total - in_total); + + yyjson_mut_val *res = yyjson_mut_obj(doc); + yyjson_mut_obj_add_strcpy(doc, res, "from", from); + yyjson_mut_obj_add_strcpy(doc, res, "to", to); + yyjson_mut_obj_add_val(doc, res, "boxes", boxes); + yyjson_mut_obj_add_strcpy(doc, res, "note", + "starter mapping; review against Skatteverket" + " before filing"); + return res; +} |