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 som inte ingår i ruta 06, 07 eller 08" },
    { "06", "Momspliktiga uttag" },
    { "07", "Beskattningsunderlag vid vinstmarginalbeskattning" },
    { "10", "Utgående moms 25%" },
    { "11", "Utgående moms 12%" },
    { "12", "Utgående moms 6%" },
    { "20", "Inköp av varor från annat EU-land" },
    { "21", "Inköp av tjänster från annat EU-land enligt huvudregeln" },
    { "30", "Utgående moms 25%" },
    { "31", "Utgående moms 12%" },
    { "32", "Utgående moms 6%" },
    { "41", "Försäljning när köparen är betalningsskyldig i Sverige" },
    { "48", "Ingående moms att dra av" },
    { "49", "Moms att betala eller få tillbaka" },
};

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

/* Boxes that affect box 49; every other box is only a base (underlag). */
static int vat_box_payable(const char *box)
{
    static const char *const boxes[] = { "10", "11", "12", "30", "31",
                                         "32", "48", "60", "61", "62" };
    for (size_t i = 0; i < sizeof boxes / sizeof boxes[0]; i++)
        if (strcmp(box, boxes[i]) == 0)
            return 1;
    return 0;
}

#define VAT_MAX_BOXES 64

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;
    }
    struct {
        char box[8];
        int64_t amount;
    } acc[VAT_MAX_BOXES];
    size_t nacc = 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);
        size_t i = 0;
        for (; i < nacc; i++) {
            if (strcmp(acc[i].box, box) == 0) {
                acc[i].amount += amount;
                break;
            }
        }
        if (i == nacc && nacc < VAT_MAX_BOXES) {
            snprintf(acc[nacc].box, sizeof acc[nacc].box, "%s", box);
            acc[nacc].amount = amount;
            nacc++;
        }
    }
    sqlite3_finalize(st);

    yyjson_mut_val *boxes = yyjson_mut_arr(doc);
    int64_t payable = 0;
    for (size_t i = 0; i < nacc; i++) {
        yyjson_mut_val *o = yyjson_mut_arr_add_obj(doc, boxes);
        yyjson_mut_obj_add_strcpy(doc, o, "box", acc[i].box);
        yyjson_mut_obj_add_strcpy(doc, o, "label", box_label(acc[i].box));
        yyjson_mut_obj_add_int(doc, o, "amount_ore", acc[i].amount);
        if (vat_box_payable(acc[i].box))
            payable += acc[i].amount;
    }
    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", payable);

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

/* ---------------- huvudbok (general ledger) ---------------- */

static int str_in_arr(yyjson_val *arr, const char *s)
{
    if (!arr)
        return 1;
    size_t n = yyjson_arr_size(arr);
    for (size_t i = 0; i < n; i++) {
        yyjson_val *v = yyjson_arr_get(arr, i);
        if (v && yyjson_is_str(v) && strcmp(yyjson_get_str(v), s) == 0)
            return 1;
    }
    return 0;
}

static yyjson_mut_val *last_voucher_json(yyjson_mut_doc *doc, sqlite3 *db,
                                         int64_t org_id, int64_t fy_id)
{
    sqlite3_stmt *st = NULL;
    if (sqlite3_prepare_v2(
            db,
            "SELECT series,number FROM vouchers"
            " WHERE org_id=?1 AND fiscal_year_id=?2"
            " ORDER BY series DESC, number DESC LIMIT 1",
            -1, &st, NULL) != SQLITE_OK)
        return NULL;
    sqlite3_bind_int64(st, 1, org_id);
    sqlite3_bind_int64(st, 2, fy_id);
    yyjson_mut_val *o = NULL;
    if (sqlite3_step(st) == SQLITE_ROW) {
        o = yyjson_mut_obj(doc);
        yyjson_mut_obj_add_strcpy(doc, o, "series",
                                  (const char *)sqlite3_column_text(st, 0));
        yyjson_mut_obj_add_int(doc, o, "number", sqlite3_column_int64(st, 1));
    }
    sqlite3_finalize(st);
    return o;
}

yyjson_mut_val *report_general_ledger(yyjson_mut_doc *doc, sqlite3 *db,
                                      int64_t org_id, int64_t fy_id,
                                      const char *from, const char *to,
                                      yyjson_val *accounts, 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 *bal =
        balance_query(doc, db, org_id, fy.id, from, to, NULL, err);
    if (!bal)
        return NULL;
    sqlite3_stmt *rs = NULL;
    if (sqlite3_prepare_v2(
            db,
            "SELECT v.series,v.number,v.date,COALESCE(v.description,''),"
            "r.debit_ore,r.credit_ore,COALESCE(r.description,'')"
            " 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.fiscal_year_id=?3"
            " AND v.series <> 'IB' AND v.date BETWEEN ?4 AND ?5"
            " ORDER BY v.date,v.series,v.number,r.id",
            -1, &rs, NULL) != SQLITE_OK) {
        set_err(err, "database error");
        return NULL;
    }
    yyjson_mut_val *out = yyjson_mut_arr(doc);
    size_t nbal = yyjson_mut_arr_size(bal);
    for (size_t i = 0; i < nbal; i++) {
        yyjson_mut_val *row = yyjson_mut_arr_get(bal, i);
        const char *acc =
            yyjson_mut_get_str(yyjson_mut_obj_get(row, "account"));
        if (!acc || !*acc || !str_in_arr(accounts, acc))
            continue;
        int64_t ib = yyjson_mut_get_int(yyjson_mut_obj_get(row, "ib_ore"));
        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 (ib == 0 && d == 0 && c == 0)
            continue;
        const char *name =
            yyjson_mut_get_str(yyjson_mut_obj_get(row, "name"));
        yyjson_mut_val *ao = yyjson_mut_arr_add_obj(doc, out);
        yyjson_mut_obj_add_strcpy(doc, ao, "account", acc);
        yyjson_mut_obj_add_strcpy(doc, ao, "name", name ? name : "");
        yyjson_mut_obj_add_int(doc, ao, "ib_ore", ib);
        yyjson_mut_val *rows = yyjson_mut_arr(doc);
        int64_t saldo = ib;
        sqlite3_bind_int64(rs, 1, org_id);
        sqlite3_bind_text(rs, 2, acc, -1, SQLITE_TRANSIENT);
        sqlite3_bind_int64(rs, 3, fy.id);
        sqlite3_bind_text(rs, 4, from, -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(rs, 5, to, -1, SQLITE_TRANSIENT);
        while (sqlite3_step(rs) == SQLITE_ROW) {
            int64_t rd = sqlite3_column_int64(rs, 4);
            int64_t rc = sqlite3_column_int64(rs, 5);
            saldo += rd - rc;
            yyjson_mut_val *ro = yyjson_mut_arr_add_obj(doc, rows);
            yyjson_mut_obj_add_strcpy(
                doc, ro, "series",
                (const char *)sqlite3_column_text(rs, 0));
            yyjson_mut_obj_add_int(doc, ro, "number",
                                   sqlite3_column_int64(rs, 1));
            yyjson_mut_obj_add_strcpy(
                doc, ro, "date",
                (const char *)sqlite3_column_text(rs, 2));
            yyjson_mut_obj_add_strcpy(
                doc, ro, "description",
                (const char *)sqlite3_column_text(rs, 3));
            yyjson_mut_obj_add_int(doc, ro, "debit_ore", rd);
            yyjson_mut_obj_add_int(doc, ro, "credit_ore", rc);
            yyjson_mut_obj_add_strcpy(
                doc, ro, "row_description",
                (const char *)sqlite3_column_text(rs, 6));
            yyjson_mut_obj_add_int(doc, ro, "saldo_ore", saldo);
        }
        sqlite3_reset(rs);
        yyjson_mut_obj_add_val(doc, ao, "rows", rows);
        yyjson_mut_obj_add_int(doc, ao, "debit_ore", d);
        yyjson_mut_obj_add_int(doc, ao, "credit_ore", c);
        yyjson_mut_obj_add_int(doc, ao, "ub_ore", ib + d - c);
    }
    sqlite3_finalize(rs);

    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 *lv = last_voucher_json(doc, db, org_id, fy_id);
    if (lv)
        yyjson_mut_obj_add_val(doc, o, "last_voucher", lv);
    yyjson_mut_obj_add_val(doc, o, "accounts", out);
    return o;
}

/* ---------------- verifikationslista (voucher list) ---------------- */

yyjson_mut_val *report_voucher_list(yyjson_mut_doc *doc, sqlite3 *db,
                                    int64_t org_id, int64_t fy_id,
                                    const char *series, char **err)
{
    struct fy_info fy;
    if (load_fy(db, org_id, fy_id, &fy, err) != 0)
        return NULL;
    char sql[256];
    snprintf(sql, sizeof sql,
             "SELECT id,series,number,date,COALESCE(description,'')"
             " FROM vouchers WHERE org_id=?1 AND fiscal_year_id=?2%s"
             " ORDER BY series,number",
             series ? " AND series=?3" : "");
    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_int64(st, 2, fy.id);
    if (series)
        sqlite3_bind_text(st, 3, series, -1, SQLITE_TRANSIENT);
    sqlite3_stmt *rs = NULL;
    if (sqlite3_prepare_v2(
            db,
            "SELECT a.number,a.name,r.debit_ore,r.credit_ore,"
            "COALESCE(r.description,'') 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 ORDER BY r.id",
            -1, &rs, NULL) != SQLITE_OK) {
        sqlite3_finalize(st);
        set_err(err, "database error");
        return NULL;
    }
    yyjson_mut_val *vouchers = yyjson_mut_arr(doc);
    int64_t t_debit = 0, t_credit = 0;
    while (sqlite3_step(st) == SQLITE_ROW) {
        int64_t vid = sqlite3_column_int64(st, 0);
        yyjson_mut_val *vo = yyjson_mut_arr_add_obj(doc, vouchers);
        yyjson_mut_obj_add_int(doc, vo, "id", vid);
        yyjson_mut_obj_add_strcpy(doc, vo, "series",
                                  (const char *)sqlite3_column_text(st, 1));
        yyjson_mut_obj_add_int(doc, vo, "number", sqlite3_column_int64(st, 2));
        yyjson_mut_obj_add_strcpy(doc, vo, "date",
                                  (const char *)sqlite3_column_text(st, 3));
        yyjson_mut_obj_add_strcpy(doc, vo, "description",
                                  (const char *)sqlite3_column_text(st, 4));
        yyjson_mut_val *vrows = yyjson_mut_arr(doc);
        sqlite3_bind_int64(rs, 1, org_id);
        sqlite3_bind_int64(rs, 2, vid);
        while (sqlite3_step(rs) == SQLITE_ROW) {
            yyjson_mut_val *ro = yyjson_mut_arr_add_obj(doc, vrows);
            yyjson_mut_obj_add_strcpy(
                doc, ro, "account",
                (const char *)sqlite3_column_text(rs, 0));
            yyjson_mut_obj_add_strcpy(
                doc, ro, "name",
                (const char *)sqlite3_column_text(rs, 1));
            int64_t rd = sqlite3_column_int64(rs, 2);
            int64_t rc = sqlite3_column_int64(rs, 3);
            yyjson_mut_obj_add_int(doc, ro, "debit_ore", rd);
            yyjson_mut_obj_add_int(doc, ro, "credit_ore", rc);
            yyjson_mut_obj_add_strcpy(
                doc, ro, "description",
                (const char *)sqlite3_column_text(rs, 4));
            t_debit += rd;
            t_credit += rc;
        }
        sqlite3_reset(rs);
        yyjson_mut_obj_add_val(doc, vo, "rows", vrows);
    }
    sqlite3_finalize(rs);
    sqlite3_finalize(st);

    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", fy.start);
    yyjson_mut_obj_add_strcpy(doc, o, "to", fy.end);
    yyjson_mut_val *lv = last_voucher_json(doc, db, org_id, fy_id);
    if (lv)
        yyjson_mut_obj_add_val(doc, o, "last_voucher", lv);
    yyjson_mut_obj_add_val(doc, o, "vouchers", vouchers);
    yyjson_mut_val *tot = yyjson_mut_obj(doc);
    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_val(doc, o, "totals", tot);
    return o;
}