diff options
Diffstat (limited to 'src/reports.c')
| -rw-r--r-- | src/reports.c | 79 |
1 files changed, 57 insertions, 22 deletions
diff --git a/src/reports.c b/src/reports.c index 1d4efdd..981d02c 100644 --- a/src/reports.c +++ b/src/reports.c @@ -4,6 +4,7 @@ #include <stdlib.h> #include <string.h> +#include "db.h" #include "util.h" struct fy_info { @@ -57,27 +58,36 @@ 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`. - Amounts signed: debit positive. */ +/* One row per account with IB and period movements; IB as defined by + REPORT_IB_ROW_SQL. skip_closings drops the source system's "Stäng ..." + closing vouchers (SIE-imported only) from the period movements: imported + years close the P&L accounts straight to 2099, so the year otherwise nets + to zero; the TUI årsredovisning uses the same convention. 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) + const char *types_filter, + int skip_closings, char **err) { - char sql[1280]; + char ib_series[16]; + db_setting_copy(db, org_id, "series_ib", "IB", ib_series, + sizeof ib_series); + char sql[2048]; snprintf(sql, sizeof sql, "SELECT a.number,a.name,a.type," - " COALESCE(SUM(CASE WHEN v.series <> 'IB'" + " COALESCE(SUM(CASE WHEN v.series <> 'IB' AND v.series <> ?5" + " AND (?6 = 0 OR v.source <> 'sie_import'" + " OR COALESCE(v.description,'') NOT LIKE 'Stäng%%')" " AND v.date BETWEEN ?2 AND ?3 THEN r.debit_ore END),0)," - " COALESCE(SUM(CASE WHEN v.series <> 'IB'" + " COALESCE(SUM(CASE WHEN v.series <> 'IB' AND v.series <> ?5" + " AND (?6 = 0 OR v.source <> 'sie_import'" + " OR COALESCE(v.description,'') NOT LIKE 'Stäng%%')" " 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)" + " 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)" @@ -94,6 +104,8 @@ static yyjson_mut_val *balance_query(yyjson_mut_doc *doc, sqlite3 *db, 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); + sqlite3_bind_text(st, 5, ib_series, -1, SQLITE_TRANSIENT); + sqlite3_bind_int(st, 6, skip_closings); yyjson_mut_val *arr = yyjson_mut_arr(doc); while (sqlite3_step(st) == SQLITE_ROW) { const char *number = (const char *)sqlite3_column_text(st, 0); @@ -128,7 +140,8 @@ yyjson_mut_val *report_trial_balance(yyjson_mut_doc *doc, sqlite3 *db, if (!to) to = fy.end; - yyjson_mut_val *rows = balance_query(doc, db, org_id, fy.id, from, to, NULL, err); + yyjson_mut_val *rows = balance_query(doc, db, org_id, fy.id, from, to, NULL, + 0, err); if (!rows) return NULL; @@ -187,7 +200,7 @@ yyjson_mut_val *report_income_statement(yyjson_mut_doc *doc, sqlite3 *db, yyjson_mut_val *rows = balance_query( doc, db, org_id, fy.id, from, to, - " AND a.type IN ('revenue','expense')", err); + " AND a.type IN ('revenue','expense')", 1, err); if (!rows) return NULL; @@ -244,7 +257,7 @@ yyjson_mut_val *report_balance_sheet(yyjson_mut_doc *doc, sqlite3 *db, yyjson_mut_val *rows = balance_query( doc, db, org_id, fy.id, fy.start, to, - " AND a.type IN ('asset','liability','equity')", err); + " AND a.type IN ('asset','liability','equity')", 0, err); if (!rows) return NULL; @@ -280,10 +293,11 @@ yyjson_mut_val *report_balance_sheet(yyjson_mut_doc *doc, sqlite3 *db, yyjson_mut_arr_add_val(*target, copy); } - /* current year result belongs to equity */ + /* current year result belongs to equity; the closings stay included so a + transferred result (2099) does not get counted twice */ yyjson_mut_val *inc = balance_query( doc, db, org_id, fy.id, fy.start, to, - " AND a.type IN ('revenue','expense')", err); + " AND a.type IN ('revenue','expense')", 0, err); int64_t result = 0; if (inc) { size_t m = yyjson_mut_arr_size(inc); @@ -365,6 +379,18 @@ static int vat_box_payable(const char *box) #define VAT_MAX_BOXES 64 +/* Vouchers left out of the momsdeklaration: the momsomföring itself (any + voucher with a 2650 row moves the period's VAT to the redovisningskonto + and would zero the boxes) and a source system's imported "Stäng ..." year + closings, which zero the P&L underlag. */ +#define VAT_VOUCHER_SQL \ + " AND NOT EXISTS (SELECT 1 FROM voucher_rows r2 JOIN accounts a2" \ + " ON a2.org_id = r2.org_id AND a2.id = r2.account_id" \ + " WHERE r2.org_id = v.org_id AND r2.voucher_id = v.id" \ + " AND a2.number = '2650')" \ + " AND NOT (v.source = 'sie_import'" \ + " AND COALESCE(v.description,'') LIKE 'Stäng%')" + yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, const char *from, const char *to, char **err) { @@ -377,6 +403,9 @@ yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, int64_t amount; } acc[VAT_MAX_BOXES]; size_t nacc = 0; + char ib_series[16]; + db_setting_copy(db, org_id, "series_ib", "IB", ib_series, + sizeof ib_series); sqlite3_stmt *st = NULL; if (sqlite3_prepare_v2( db, @@ -401,21 +430,25 @@ yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, " 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"; + " AND v.series <> ?5 AND v.date BETWEEN ?3 AND ?4" + VAT_VOUCHER_SQL; else if (strcmp(mt, "type") == 0) sql = "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.type=?2 AND v.series <> 'IB'" - " AND v.date BETWEEN ?3 AND ?4"; + " AND v.series <> ?5 AND v.date BETWEEN ?3 AND ?4" + VAT_VOUCHER_SQL; else sql = "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"; + " AND ?3 AND v.series <> 'IB' AND v.series <> ?6" + " AND v.date BETWEEN ?4 AND ?5" + VAT_VOUCHER_SQL; if (sqlite3_prepare_v2(db, sql, -1, &qs, NULL) != SQLITE_OK) break; sqlite3_bind_int64(qs, 1, org_id); @@ -423,6 +456,7 @@ yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, 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); + sqlite3_bind_text(qs, 5, ib_series, -1, SQLITE_TRANSIENT); } else { long lo = 0, hi = 0; if (sscanf(pattern, "%ld-%ld", &lo, &hi) != 2) { @@ -433,6 +467,7 @@ yyjson_mut_val *report_vat(yyjson_mut_doc *doc, sqlite3 *db, int64_t org_id, sqlite3_bind_int64(qs, 3, hi); sqlite3_bind_text(qs, 4, from, -1, SQLITE_TRANSIENT); sqlite3_bind_text(qs, 5, to, -1, SQLITE_TRANSIENT); + sqlite3_bind_text(qs, 6, ib_series, -1, SQLITE_TRANSIENT); } if (sqlite3_step(qs) == SQLITE_ROW) amount = (sqlite3_column_int64(qs, 0) - @@ -532,7 +567,7 @@ yyjson_mut_val *report_general_ledger(yyjson_mut_doc *doc, sqlite3 *db, if (!to) to = fy.end; yyjson_mut_val *bal = - balance_query(doc, db, org_id, fy.id, from, to, NULL, err); + balance_query(doc, db, org_id, fy.id, from, to, NULL, 0, err); if (!bal) return NULL; sqlite3_stmt *rs = NULL; |