aboutsummaryrefslogtreecommitdiff
path: root/database.cpp
diff options
context:
space:
mode:
Diffstat (limited to 'database.cpp')
-rw-r--r--database.cpp364
1 files changed, 364 insertions, 0 deletions
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 <QSqlDatabase>
+#include <QSqlQuery>
+#include <QSqlError>
+#include <QDebug>
+
+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<Transaction> Database::getTransactions(const QDate &startDate, const QDate &endDate) {
+ QList<Transaction> 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<Transaction> Database::getAllTransactions() {
+ QList<Transaction> 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<RecurringRule> Database::getAllRecurringRules() {
+ QList<RecurringRule> 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;
+}
generated by cgit v1.2.3 (git 2.39.1) at 2026-01-31 05:57:49 +0000