Initial commit: daemon, clients, docs, Docker deploy pipelinev0.1.0

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;
+}