From 88b069141faafd1c5aefda1573b2285a38885ce4 Mon Sep 17 00:00:00 2001 From: Calvin Morrison Date: Sat, 27 Dec 2025 14:19:21 -0500 Subject: initial commit --- database.cpp | 364 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 364 insertions(+) create mode 100644 database.cpp (limited to 'database.cpp') diff --git a/database.cpp b/database.cpp new file mode 100644 index 0000000..5f9d99c --- /dev/null +++ b/database.cpp @@ -0,0 +1,364 @@ +#include "database.h" +#include +#include +#include +#include + +Database::Database() { + db = QSqlDatabase::addDatabase("QSQLITE"); +} + +Database::~Database() { + if (db.isOpen()) { + db.close(); + } +} + +bool Database::open(const QString &path) { + db.setDatabaseName(path); + if (!db.open()) { + errorMsg = db.lastError().text(); + return false; + } + return createTables(); +} + +bool Database::createTables() { + QSqlQuery query; + + // Create transactions table + QString createTransactions = R"( + CREATE TABLE IF NOT EXISTS transactions ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + date TEXT NOT NULL, + amount REAL NOT NULL, + account TEXT NOT NULL, + category TEXT, + description TEXT, + type TEXT NOT NULL, + recurring_id INTEGER DEFAULT -1, + sort_order INTEGER DEFAULT 0 + ) + )"; + + if (!query.exec(createTransactions)) { + errorMsg = query.lastError().text(); + return false; + } + + // Create recurring_rules table + QString createRecurring = R"( + CREATE TABLE IF NOT EXISTS recurring_rules ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name TEXT NOT NULL, + frequency TEXT NOT NULL, + start_date TEXT NOT NULL, + end_date TEXT, + amount REAL NOT NULL, + account TEXT NOT NULL, + category TEXT, + description TEXT, + day_of_week INTEGER DEFAULT -1, + day_of_month INTEGER DEFAULT -1, + occurrences INTEGER DEFAULT -1, + sort_order INTEGER DEFAULT 0 + ) + )"; + + if (!query.exec(createRecurring)) { + errorMsg = query.lastError().text(); + return false; + } + + // Create settings table + QString createSettings = R"( + CREATE TABLE IF NOT EXISTS settings ( + setting_name TEXT PRIMARY KEY, + setting_value TEXT + ) + )"; + + if (!query.exec(createSettings)) { + errorMsg = query.lastError().text(); + return false; + } + + // Migrate existing tables - add category column if missing + query.exec("ALTER TABLE transactions ADD COLUMN category TEXT"); + query.exec("ALTER TABLE recurring_rules ADD COLUMN category TEXT"); + + // Migrate existing tables - add sort_order column if missing + query.exec("ALTER TABLE transactions ADD COLUMN sort_order INTEGER DEFAULT 0"); + query.exec("ALTER TABLE recurring_rules ADD COLUMN sort_order INTEGER DEFAULT 0"); + + return true; +} + +bool Database::addTransaction(const Transaction &transaction) { + QSqlQuery query; + query.prepare("INSERT INTO transactions (date, amount, account, category, description, type, recurring_id, sort_order) " + "VALUES (:date, :amount, :account, :category, :description, :type, :recurring_id, :sort_order)"); + query.bindValue(":date", transaction.date.toString(Qt::ISODate)); + query.bindValue(":amount", transaction.amount); + query.bindValue(":account", transaction.account); + query.bindValue(":category", transaction.category); + query.bindValue(":description", transaction.description); + query.bindValue(":type", transaction.type == TransactionType::Actual ? "actual" : "estimated"); + query.bindValue(":recurring_id", transaction.recurringId); + query.bindValue(":sort_order", transaction.sortOrder); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return false; + } + return true; +} + +bool Database::updateTransaction(const Transaction &transaction) { + QSqlQuery query; + query.prepare("UPDATE transactions SET date=:date, amount=:amount, account=:account, category=:category, " + "description=:description, type=:type, recurring_id=:recurring_id, sort_order=:sort_order WHERE id=:id"); + query.bindValue(":id", transaction.id); + query.bindValue(":date", transaction.date.toString(Qt::ISODate)); + query.bindValue(":amount", transaction.amount); + query.bindValue(":account", transaction.account); + query.bindValue(":category", transaction.category); + query.bindValue(":description", transaction.description); + query.bindValue(":type", transaction.type == TransactionType::Actual ? "actual" : "estimated"); + query.bindValue(":recurring_id", transaction.recurringId); + query.bindValue(":sort_order", transaction.sortOrder); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return false; + } + return true; +} + +bool Database::deleteTransaction(int id) { + QSqlQuery query; + query.prepare("DELETE FROM transactions WHERE id=:id"); + query.bindValue(":id", id); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return false; + } + return true; +} + +QList Database::getTransactions(const QDate &startDate, const QDate &endDate) { + QList transactions; + QSqlQuery query; + query.prepare("SELECT * FROM transactions WHERE date >= :start AND date <= :end ORDER BY date"); + query.bindValue(":start", startDate.toString(Qt::ISODate)); + query.bindValue(":end", endDate.toString(Qt::ISODate)); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return transactions; + } + + while (query.next()) { + transactions.append(queryToTransaction(query)); + } + return transactions; +} + +QList Database::getAllTransactions() { + QList transactions; + QSqlQuery query("SELECT * FROM transactions ORDER BY date"); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return transactions; + } + + while (query.next()) { + transactions.append(queryToTransaction(query)); + } + return transactions; +} + +bool Database::addRecurringRule(const RecurringRule &rule) { + QSqlQuery query; + query.prepare("INSERT INTO recurring_rules (name, frequency, start_date, end_date, amount, " + "account, category, description, day_of_week, day_of_month, occurrences, sort_order) " + "VALUES (:name, :frequency, :start_date, :end_date, :amount, :account, " + ":category, :description, :day_of_week, :day_of_month, :occurrences, :sort_order)"); + query.bindValue(":name", rule.name); + + QString freq; + switch (rule.frequency) { + case RecurrenceFrequency::Daily: freq = "daily"; break; + case RecurrenceFrequency::Weekly: freq = "weekly"; break; + case RecurrenceFrequency::BiWeekly: freq = "biweekly"; break; + case RecurrenceFrequency::Monthly: freq = "monthly"; break; + case RecurrenceFrequency::Yearly: freq = "yearly"; break; + default: freq = "none"; break; + } + query.bindValue(":frequency", freq); + query.bindValue(":start_date", rule.startDate.toString(Qt::ISODate)); + query.bindValue(":end_date", rule.endDate.isValid() ? rule.endDate.toString(Qt::ISODate) : QVariant()); + query.bindValue(":amount", rule.amount); + query.bindValue(":account", rule.account); + query.bindValue(":category", rule.category); + query.bindValue(":description", rule.description); + query.bindValue(":day_of_week", rule.dayOfWeek); + query.bindValue(":day_of_month", rule.dayOfMonth); + query.bindValue(":occurrences", rule.occurrences); + query.bindValue(":sort_order", rule.sortOrder); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return false; + } + return true; +} + +bool Database::updateRecurringRule(const RecurringRule &rule) { + QSqlQuery query; + query.prepare("UPDATE recurring_rules SET name=:name, frequency=:frequency, start_date=:start_date, " + "end_date=:end_date, amount=:amount, account=:account, category=:category, description=:description, " + "day_of_week=:day_of_week, day_of_month=:day_of_month, occurrences=:occurrences, sort_order=:sort_order WHERE id=:id"); + query.bindValue(":id", rule.id); + query.bindValue(":name", rule.name); + + QString freq; + switch (rule.frequency) { + case RecurrenceFrequency::Daily: freq = "daily"; break; + case RecurrenceFrequency::Weekly: freq = "weekly"; break; + case RecurrenceFrequency::BiWeekly: freq = "biweekly"; break; + case RecurrenceFrequency::Monthly: freq = "monthly"; break; + case RecurrenceFrequency::Yearly: freq = "yearly"; break; + default: freq = "none"; break; + } + query.bindValue(":frequency", freq); + query.bindValue(":start_date", rule.startDate.toString(Qt::ISODate)); + query.bindValue(":end_date", rule.endDate.isValid() ? rule.endDate.toString(Qt::ISODate) : QVariant()); + query.bindValue(":amount", rule.amount); + query.bindValue(":account", rule.account); + query.bindValue(":category", rule.category); + query.bindValue(":description", rule.description); + query.bindValue(":day_of_week", rule.dayOfWeek); + query.bindValue(":day_of_month", rule.dayOfMonth); + query.bindValue(":occurrences", rule.occurrences); + query.bindValue(":sort_order", rule.sortOrder); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return false; + } + return true; +} + +bool Database::deleteRecurringRule(int id) { + QSqlQuery query; + query.prepare("DELETE FROM recurring_rules WHERE id=:id"); + query.bindValue(":id", id); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return false; + } + return true; +} + +QList Database::getAllRecurringRules() { + QList rules; + QSqlQuery query("SELECT * FROM recurring_rules"); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return rules; + } + + while (query.next()) { + rules.append(queryToRecurringRule(query)); + } + return rules; +} + +bool Database::convertToActual(int transactionId, double actualAmount) { + QSqlQuery query; + query.prepare("UPDATE transactions SET type='actual', amount=:amount WHERE id=:id"); + query.bindValue(":amount", actualAmount); + query.bindValue(":id", transactionId); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return false; + } + return true; +} + +QString Database::lastError() const { + return errorMsg; +} + +Transaction Database::queryToTransaction(QSqlQuery &query) { + Transaction t; + t.id = query.value("id").toInt(); + t.date = QDate::fromString(query.value("date").toString(), Qt::ISODate); + t.amount = query.value("amount").toDouble(); + t.account = query.value("account").toString(); + t.category = query.value("category").toString(); + t.description = query.value("description").toString(); + t.type = query.value("type").toString() == "actual" ? TransactionType::Actual : TransactionType::Estimated; + t.recurringId = query.value("recurring_id").toInt(); + t.sortOrder = query.value("sort_order").toInt(); + return t; +} + +RecurringRule Database::queryToRecurringRule(QSqlQuery &query) { + RecurringRule r; + r.id = query.value("id").toInt(); + r.name = query.value("name").toString(); + + QString freq = query.value("frequency").toString(); + if (freq == "daily") r.frequency = RecurrenceFrequency::Daily; + else if (freq == "weekly") r.frequency = RecurrenceFrequency::Weekly; + else if (freq == "biweekly") r.frequency = RecurrenceFrequency::BiWeekly; + else if (freq == "monthly") r.frequency = RecurrenceFrequency::Monthly; + else if (freq == "yearly") r.frequency = RecurrenceFrequency::Yearly; + else r.frequency = RecurrenceFrequency::None; + + r.startDate = QDate::fromString(query.value("start_date").toString(), Qt::ISODate); + QString endDateStr = query.value("end_date").toString(); + if (!endDateStr.isEmpty()) { + r.endDate = QDate::fromString(endDateStr, Qt::ISODate); + } + r.amount = query.value("amount").toDouble(); + r.account = query.value("account").toString(); + r.category = query.value("category").toString(); + r.description = query.value("description").toString(); + r.dayOfWeek = query.value("day_of_week").toInt(); + r.dayOfMonth = query.value("day_of_month").toInt(); + r.occurrences = query.value("occurrences").toInt(); + r.sortOrder = query.value("sort_order").toInt(); + return r; +} + +QString Database::getSetting(const QString &name, const QString &defaultValue) { + QSqlQuery query; + query.prepare("SELECT setting_value FROM settings WHERE setting_name = :name"); + query.bindValue(":name", name); + + if (query.exec() && query.next()) { + return query.value(0).toString(); + } + return defaultValue; +} + +bool Database::setSetting(const QString &name, const QString &value) { + QSqlQuery query; + query.prepare("INSERT OR REPLACE INTO settings (setting_name, setting_value) VALUES (:name, :value)"); + query.bindValue(":name", name); + query.bindValue(":value", value); + + if (!query.exec()) { + errorMsg = query.lastError().text(); + return false; + } + return true; +} -- cgit v1.2.3