reports, sie: carry earlier IB vouchers, restart P&L at year start

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

3 files changed, 36 insertions, 20 deletions

diff --git a/src/reports.c b/src/reports.c
index 2ca7e45..d8ed0d2 100644
--- a/src/reports.c
+++ b/src/reports.c
@@ -58,8 +58,8 @@ 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`.
+/* One row per account with IB and period movements; IB as defined by
+   REPORT_IB_ROW_SQL.
    Amounts signed: debit positive. */
 static yyjson_mut_val *balance_query(yyjson_mut_doc *doc, sqlite3 *db,
                                      int64_t org_id, int64_t fy_id,
@@ -69,19 +69,17 @@ static yyjson_mut_val *balance_query(yyjson_mut_doc *doc, sqlite3 *db,
     char ib_series[16];
     db_setting_copy(db, org_id, "series_ib", "IB", ib_series,
                     sizeof ib_series);
-    char sql[1280];
+    char sql[2048];
     snprintf(sql, sizeof sql,
              "SELECT a.number,a.name,a.type,"
              " COALESCE(SUM(CASE WHEN v.series <> 'IB' AND v.series <> ?5"
              "   AND v.date BETWEEN ?2 AND ?3 THEN r.debit_ore END),0),"
              " COALESCE(SUM(CASE WHEN v.series <> 'IB' AND v.series <> ?5"
              "   AND v.date BETWEEN ?2 AND ?3 THEN r.credit_ore END),0),"
-             " COALESCE(SUM(CASE WHEN ((v.series = 'IB' OR v.series = ?5)"
-             "   AND v.fiscal_year_id = ?4) OR (v.series <> 'IB'"
-             "   AND v.series <> ?5 AND v.date < ?2) THEN r.debit_ore END),0),"
-             " COALESCE(SUM(CASE WHEN ((v.series = 'IB' OR v.series = ?5)"
-             "   AND v.fiscal_year_id = ?4) OR (v.series <> 'IB'"
-             "   AND v.series <> ?5 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)"
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,
diff --git a/src/sie.c b/src/sie.c
index 6e0eab4..c2ce19a 100644
--- a/src/sie.c
+++ b/src/sie.c
@@ -11,6 +11,7 @@
 #include "db.h"
 #include "ledger.h"
 #include "log.h"
+#include "reports.h"
 #include "util.h"
 #include "version.h"
 
@@ -136,24 +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 IB-series 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' OR v.series = ?5)"
-            "   AND v.fiscal_year_id = ?4) OR (v.series <> 'IB'"
-            "   AND v.series <> ?5 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' 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"
@@ -173,8 +171,13 @@ int sie_export_file(sqlite3 *db, int64_t org_id, int64_t fy_id,
         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, " ");