finnow/finnow-api/source/analytics/data_impl_sqlite.d

module analytics.data_impl_sqlite;

import d2sqlite3;
import std.array;
import std.datetime;

import util.money;
import util.data;
import util.sqlite;
import account.model : AccountJournalEntryType, AccountType;
import analytics.modules.balances;
import analytics.data;

class SqliteAnalyticsRepository : AnalyticsRepository {
    private Database db;
    this(Database db) {
        this.db = db;
    }

    JournalEntryStub[] getJournalEntries(
        in Currency currency,
        in TimeRange timeRange
    ) {
        QueryBuilder qb = QueryBuilder("account_journal_entry je")
            .select("je.timestamp,je.account_id,je.amount,je.type,account.type")
            .join("LEFT JOIN account ON account.id = je.account_id")
            .where("UPPER(je.currency) = ?")
            .withArgBinding((ref stmt, ref idx) {
                stmt.bind(idx++, currency.codeString);
            });
        if (timeRange.fromTime) {
            qb.where("je.timestamp >= ?")
                .withArgBinding((ref stmt, ref idx) {
                    stmt.bind(idx++, timeRange.fromTime.value);
                });
        }
        if (timeRange.toTime) {
            qb.where("je.timestamp <= ?")
                .withArgBinding((ref stmt, ref idx) {
                    stmt.bind(idx++, timeRange.fromTime.value);
                });
        }
        string query = qb.build() ~ " ORDER BY je.timestamp";
        Statement stmt = db.prepare(query);
        qb.applyArgBindings(stmt);
        ResultRange result = stmt.execute();
        Appender!(JournalEntryStub[]) app;
        foreach (row; result) {
            auto journalEntryTypeStr = row.peek!(string, PeekMode.slice)(3);
            AccountJournalEntryType type;
            if (journalEntryTypeStr == AccountJournalEntryType.CREDIT) {
                type = AccountJournalEntryType.CREDIT;
            } else {
                type = AccountJournalEntryType.DEBIT;
            }
            app ~= JournalEntryStub(
                SysTime.fromISOExtString(row.peek!(string, PeekMode.slice)(0)),
                row.peek!ulong(1),
                AccountType.fromId(row.peek!(string, PeekMode.slice)(4)),
                row.peek!ulong(2),
                type
            );
        }
        return app[];
    }

    BalanceRecordStub[] getBalanceRecords(
        in Currency currency,
        in TimeRange timeRange
    ) {
        QueryBuilder qb = QueryBuilder("account_value_record")
            .select("timestamp,account_id,value,type")
            .where("UPPER(currency) = ?")
            .withArgBinding((ref stmt, ref idx) {
                stmt.bind(idx++, currency.codeString);
            })
            .where("UPPER(type) = 'BALANCE'");
        if (timeRange.fromTime) {
            qb.where("timestamp >= ?")
                .withArgBinding((ref stmt, ref idx) {
                    stmt.bind(idx++, timeRange.fromTime.value);
                });
        }
        if (timeRange.toTime) {
            qb.where("timestamp <= ?")
                .withArgBinding((ref stmt, ref idx) {
                    stmt.bind(idx++, timeRange.fromTime.value);
                });
        }
        string query = qb.build() ~ " ORDER BY timestamp";
        Statement stmt = db.prepare(query);
        qb.applyArgBindings(stmt);
        ResultRange result = stmt.execute();
        Appender!(BalanceRecordStub[]) app;
        foreach (row; result) {
            app ~= BalanceRecordStub(
                SysTime.fromISOExtString(row.peek!(string, PeekMode.slice)(0)),
                row.peek!ulong(1),
                row.peek!long(2)
            );
        }
        return app[];
    }

    CategorySpendData[] getCategorySpendData(
        in Currency currency,
        in TimeRange timeRange
    ) {
        QueryBuilder qb = QueryBuilder("transaction_category c")
            .select("c.id,c.name,c.color,c.parent_id")
            .select(`
                (
                    SUM(CASE WHEN je.type LIKE 'DEBIT' THEN je.amount ELSE 0 END)
                    - SUM(CASE WHEN je.type LIKE 'CREDIT' THEN je.amount ELSE 0 END)
                )
            `)
            .join("LEFT JOIN \"transaction\" t ON t.category_id = c.id")
            .join("LEFT JOIN account_journal_entry je ON je.transaction_id = t.id");
        qb.where("t.internal_transfer = false");
        qb.where("t.currency = ?");
        qb.withArgBinding((ref stmt, ref idx) {
            stmt.bind(idx++, currency.codeString);
        });
        if (timeRange.fromTime) {
            qb.where("t.timestamp >= ?")
                .withArgBinding((ref stmt, ref idx) {
                    stmt.bind(idx++, timeRange.fromTime.value);
                });
        }
        if (timeRange.toTime) {
            qb.where("t.timestamp <= ?")
                .withArgBinding((ref stmt, ref idx) {
                    stmt.bind(idx++, timeRange.fromTime.value);
                });
        }
        string query = qb.build() ~ " GROUP BY c.id ORDER BY c.id";
        Statement stmt = db.prepare(query);
        qb.applyArgBindings(stmt);
        ResultRange result = stmt.execute();
        Appender!(CategorySpendData[]) app;
        foreach (row; result) {
            import std.typecons : Nullable;
            app ~= CategorySpendData(
                row.peek!ulong(0),
                row.peek!string(1),
                row.peek!string(2),
                toOptional(row.peek!(Nullable!ulong)(3)),
                row.peek!long(4)
            );
        }
        return app[];
    }
}