path: root/src/reports.c

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