aboutsummaryrefslogtreecommitdiff
path: root/database.cpp
diff options
context:
space:
mode:
Diffstat (limited to 'database.cpp')
-rw-r--r--database.cpp168
1 files changed, 159 insertions, 9 deletions
diff --git a/database.cpp b/database.cpp
index 4041344..1610536 100644
--- a/database.cpp
+++ b/database.cpp
@@ -96,6 +96,11 @@ bool Database::createTables() {
query.exec("ALTER TABLE transactions ADD COLUMN occurrence_key TEXT");
query.exec("ALTER TABLE transactions ADD COLUMN expected_amount REAL");
query.exec("ALTER TABLE transactions ADD COLUMN expected_date TEXT");
+ query.exec("ALTER TABLE transactions ADD COLUMN expected_balance REAL DEFAULT 0");
+ query.exec("ALTER TABLE transactions ADD COLUMN calculated_balance REAL DEFAULT 0");
+
+ // Add projection_end_date to recurring_rules (default to 1 year from now)
+ query.exec("ALTER TABLE recurring_rules ADD COLUMN projection_end_date TEXT");
// Create reconciliation_checkpoints table
QString createCheckpoints = R"(
@@ -119,21 +124,28 @@ bool Database::createTables() {
bool Database::addTransaction(const Transaction &transaction) {
QSqlQuery query;
query.prepare("INSERT INTO transactions (date, amount, account, category, description, type, recurring_id, sort_order, "
- "reconciled, occurrence_key, expected_amount, expected_date) "
+ "reconciled, occurrence_key, expected_amount, expected_date, expected_balance, calculated_balance) "
"VALUES (:date, :amount, :account, :category, :description, :type, :recurring_id, :sort_order, "
- ":reconciled, :occurrence_key, :expected_amount, :expected_date)");
+ ":reconciled, :occurrence_key, :expected_amount, :expected_date, :expected_balance, :calculated_balance)");
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");
+
+ QString typeStr = "estimated";
+ if (transaction.type == TransactionType::Actual) typeStr = "actual";
+ else if (transaction.type == TransactionType::Reconciliation) typeStr = "reconciliation";
+ query.bindValue(":type", typeStr);
+
query.bindValue(":recurring_id", transaction.recurringId);
query.bindValue(":sort_order", transaction.sortOrder);
query.bindValue(":reconciled", transaction.reconciled ? 1 : 0);
query.bindValue(":occurrence_key", transaction.occurrenceKey);
query.bindValue(":expected_amount", transaction.expectedAmount > 0 ? transaction.expectedAmount : QVariant());
query.bindValue(":expected_date", transaction.expectedDate.isValid() ? transaction.expectedDate.toString(Qt::ISODate) : QVariant());
+ query.bindValue(":expected_balance", transaction.expectedBalance);
+ query.bindValue(":calculated_balance", transaction.calculatedBalance);
if (!query.exec()) {
errorMsg = query.lastError().text();
@@ -147,20 +159,27 @@ bool Database::updateTransaction(const Transaction &transaction) {
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, "
"reconciled=:reconciled, occurrence_key=:occurrence_key, expected_amount=:expected_amount, "
- "expected_date=:expected_date WHERE id=:id");
+ "expected_date=:expected_date, expected_balance=:expected_balance, calculated_balance=:calculated_balance 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");
+
+ QString typeStr = "estimated";
+ if (transaction.type == TransactionType::Actual) typeStr = "actual";
+ else if (transaction.type == TransactionType::Reconciliation) typeStr = "reconciliation";
+ query.bindValue(":type", typeStr);
+
query.bindValue(":recurring_id", transaction.recurringId);
query.bindValue(":sort_order", transaction.sortOrder);
query.bindValue(":reconciled", transaction.reconciled ? 1 : 0);
query.bindValue(":occurrence_key", transaction.occurrenceKey);
query.bindValue(":expected_amount", transaction.expectedAmount > 0 ? transaction.expectedAmount : QVariant());
query.bindValue(":expected_date", transaction.expectedDate.isValid() ? transaction.expectedDate.toString(Qt::ISODate) : QVariant());
+ query.bindValue(":expected_balance", transaction.expectedBalance);
+ query.bindValue(":calculated_balance", transaction.calculatedBalance);
query.bindValue(":sort_order", transaction.sortOrder);
if (!query.exec()) {
@@ -218,9 +237,9 @@ QList<Transaction> Database::getAllTransactions() {
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) "
+ "account, category, description, day_of_week, day_of_month, occurrences, sort_order, projection_end_date) "
"VALUES (:name, :frequency, :start_date, :end_date, :amount, :account, "
- ":category, :description, :day_of_week, :day_of_month, :occurrences, :sort_order)");
+ ":category, :description, :day_of_week, :day_of_month, :occurrences, :sort_order, :projection_end_date)");
query.bindValue(":name", rule.name);
QString freq;
@@ -243,6 +262,7 @@ bool Database::addRecurringRule(const RecurringRule &rule) {
query.bindValue(":day_of_month", rule.dayOfMonth);
query.bindValue(":occurrences", rule.occurrences);
query.bindValue(":sort_order", rule.sortOrder);
+ query.bindValue(":projection_end_date", rule.projectionEndDate.isValid() ? rule.projectionEndDate.toString(Qt::ISODate) : QVariant());
if (!query.exec()) {
errorMsg = query.lastError().text();
@@ -255,7 +275,8 @@ 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");
+ "day_of_week=:day_of_week, day_of_month=:day_of_month, occurrences=:occurrences, sort_order=:sort_order, "
+ "projection_end_date=:projection_end_date WHERE id=:id");
query.bindValue(":id", rule.id);
query.bindValue(":name", rule.name);
@@ -279,6 +300,7 @@ bool Database::updateRecurringRule(const RecurringRule &rule) {
query.bindValue(":day_of_month", rule.dayOfMonth);
query.bindValue(":occurrences", rule.occurrences);
query.bindValue(":sort_order", rule.sortOrder);
+ query.bindValue(":projection_end_date", rule.projectionEndDate.isValid() ? rule.projectionEndDate.toString(Qt::ISODate) : QVariant());
if (!query.exec()) {
errorMsg = query.lastError().text();
@@ -339,7 +361,12 @@ Transaction Database::queryToTransaction(QSqlQuery &query) {
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;
+
+ QString typeStr = query.value("type").toString();
+ if (typeStr == "actual") t.type = TransactionType::Actual;
+ else if (typeStr == "reconciliation") t.type = TransactionType::Reconciliation;
+ else t.type = TransactionType::Estimated;
+
t.recurringId = query.value("recurring_id").toInt();
t.sortOrder = query.value("sort_order").toInt();
t.reconciled = query.value("reconciled").toInt() == 1;
@@ -349,6 +376,8 @@ Transaction Database::queryToTransaction(QSqlQuery &query) {
if (!expectedDateStr.isEmpty()) {
t.expectedDate = QDate::fromString(expectedDateStr, Qt::ISODate);
}
+ t.expectedBalance = query.value("expected_balance").toDouble();
+ t.calculatedBalance = query.value("calculated_balance").toDouble();
return t;
}
@@ -378,6 +407,10 @@ RecurringRule Database::queryToRecurringRule(QSqlQuery &query) {
r.dayOfMonth = query.value("day_of_month").toInt();
r.occurrences = query.value("occurrences").toInt();
r.sortOrder = query.value("sort_order").toInt();
+ QString projEndDateStr = query.value("projection_end_date").toString();
+ if (!projEndDateStr.isEmpty()) {
+ r.projectionEndDate = QDate::fromString(projEndDateStr, Qt::ISODate);
+ }
return r;
}
@@ -404,3 +437,120 @@ bool Database::setSetting(const QString &name, const QString &value) {
}
return true;
}
+
+void Database::deleteProjectionsForRule(int ruleId) {
+ QSqlQuery query;
+ // Delete estimated transactions for this rule only where no actual exists for that date
+ query.prepare("DELETE FROM transactions WHERE recurring_id = :rule_id AND type = 'estimated' "
+ "AND NOT EXISTS (SELECT 1 FROM transactions t2 WHERE t2.recurring_id = :rule_id AND t2.date = transactions.date AND t2.type = 'actual')");
+ query.bindValue(":rule_id", ruleId);
+ query.exec();
+}
+
+void Database::regenerateProjectionsForRule(const RecurringRule &rule) {
+ // Delete ALL existing projections for this rule (not just future ones)
+ deleteProjectionsForRule(rule.id);
+
+ // Get projection months setting (default 3)
+ int projectionMonths = getSetting("projection_months", "3").toInt();
+
+ // Determine end date for projections
+ QDate endDate = rule.projectionEndDate.isValid() ?
+ rule.projectionEndDate :
+ QDate::currentDate().addMonths(projectionMonths);
+
+ // Start from the rule's start date (not today)
+ QDate currentDate = rule.startDate;
+
+ // Align to proper day based on frequency
+ if (rule.frequency == RecurrenceFrequency::Weekly || rule.frequency == RecurrenceFrequency::BiWeekly) {
+ while (currentDate <= endDate && currentDate.dayOfWeek() != rule.dayOfWeek) {
+ currentDate = currentDate.addDays(1);
+ }
+ } else if (rule.frequency == RecurrenceFrequency::Monthly) {
+ int targetDay = qMin(rule.dayOfMonth, currentDate.daysInMonth());
+ currentDate = QDate(currentDate.year(), currentDate.month(), targetDay);
+ }
+
+ int count = 0;
+ while (currentDate <= endDate) {
+ if (rule.occurrences != -1 && count >= rule.occurrences) {
+ break;
+ }
+
+ if (rule.endDate.isValid() && currentDate > rule.endDate) {
+ break;
+ }
+
+ // Check if an actual transaction already exists for this date and rule
+ QSqlQuery checkQuery(db);
+ checkQuery.prepare("SELECT COUNT(*) FROM transactions WHERE recurring_id = :rid AND date = :date AND type = 'actual'");
+ checkQuery.bindValue(":rid", rule.id);
+ checkQuery.bindValue(":date", currentDate.toString(Qt::ISODate));
+
+ bool hasActual = false;
+ if (checkQuery.exec() && checkQuery.next()) {
+ hasActual = checkQuery.value(0).toInt() > 0;
+ }
+
+ // Only create projection if no actual exists
+ if (!hasActual) {
+ Transaction t;
+ t.date = currentDate;
+ t.amount = rule.amount;
+ t.account = rule.account;
+ t.category = rule.category;
+ t.description = rule.description;
+ t.type = TransactionType::Estimated;
+ t.recurringId = rule.id;
+ t.reconciled = false;
+ t.sortOrder = rule.sortOrder;
+
+ addTransaction(t);
+ }
+ count++;
+
+ // Calculate next occurrence
+ switch (rule.frequency) {
+ case RecurrenceFrequency::Daily:
+ currentDate = currentDate.addDays(1);
+ break;
+ case RecurrenceFrequency::Weekly:
+ currentDate = currentDate.addDays(7);
+ break;
+ case RecurrenceFrequency::BiWeekly:
+ currentDate = currentDate.addDays(14);
+ break;
+ case RecurrenceFrequency::Monthly: {
+ currentDate = currentDate.addMonths(1);
+ int targetDay = qMin(rule.dayOfMonth, currentDate.daysInMonth());
+ currentDate = QDate(currentDate.year(), currentDate.month(), targetDay);
+ break;
+ }
+ case RecurrenceFrequency::Yearly:
+ currentDate = currentDate.addYears(1);
+ break;
+ default:
+ currentDate = endDate.addDays(1); // Exit loop
+ break;
+ }
+ }
+}
+
+void Database::regenerateAllProjections() {
+ // Delete ALL existing projections
+ QSqlQuery deleteQuery(db);
+ deleteQuery.prepare("DELETE FROM transactions WHERE type = 'estimated'");
+ if (!deleteQuery.exec()) {
+ errorMsg = deleteQuery.lastError().text();
+ return;
+ }
+
+ // Regenerate projections for all recurring rules
+ QList<RecurringRule> rules = getAllRecurringRules();
+ for (const RecurringRule &rule : rules) {
+ regenerateProjectionsForRule(rule);
+ }
+}
+
+
generated by cgit v1.2.3 (git 2.39.1) at 2026-01-30 23:15:43 +0000