settings: per-feature voucher series and console invoice number; tui: F9 hintsv0.1.60

1 files changed, 20 insertions, 9 deletions

diff --git a/src/reports.c b/src/reports.c
index 1d4efdd..2ca7e45 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 {
@@ -65,19 +66,22 @@ static yyjson_mut_val *balance_query(yyjson_mut_doc *doc, sqlite3 *db,
                                      const char *from, const char *to,
                                      const char *types_filter, char **err)
 {
+    char ib_series[16];
+    db_setting_copy(db, org_id, "series_ib", "IB", ib_series,
+                    sizeof ib_series);
     char sql[1280];
     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 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 v.date BETWEEN ?2 AND ?3 THEN r.credit_ore END),0),"
-             " COALESCE(SUM(CASE WHEN (v.series = 'IB'"
+             " COALESCE(SUM(CASE WHEN ((v.series = 'IB' OR v.series = ?5)"
              "   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.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.date < ?2) THEN r.credit_ore END),0)"
+             "   AND v.series <> ?5 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)"
@@ -94,6 +98,7 @@ 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);
     yyjson_mut_val *arr = yyjson_mut_arr(doc);
     while (sqlite3_step(st) == SQLITE_ROW) {
         const char *number = (const char *)sqlite3_column_text(st, 0);
@@ -377,6 +382,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 +409,22 @@ 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";
         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";
         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";
         if (sqlite3_prepare_v2(db, sql, -1, &qs, NULL) != SQLITE_OK)
             break;
         sqlite3_bind_int64(qs, 1, org_id);
@@ -423,6 +432,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 +443,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) -