diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/reports.c | 16 | ||||
| -rw-r--r-- | src/reports.h | 15 | ||||
| -rw-r--r-- | src/sie.c | 25 |
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, @@ -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, " "); |