databasemanager.cpp

#include "databasemanager.h"
#include <QStandardPaths>
#include <QDir>
#include <iostream>

DatabaseManager::DatabaseManager(QObject *parent)
    : QObject{parent}
{
    initDatabase();
}

DatabaseManager::~DatabaseManager()
{
    if (m_db.isOpen()) {
        m_db.close();
    }
}

void DatabaseManager::initDatabase()
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Сохраняем БД в папку документов пользователя
    QString dbPath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    QDir().mkpath(dbPath);
    m_db.setDatabaseName(dbPath + "/finance.db");

    if (!m_db.open()) {
        qWarning() << "Ошибка открытия БД:" << m_db.lastError().text();
        return;
    }

    QSqlQuery query;
    QString createTableStr = R"(
        CREATE TABLE IF NOT EXISTS transactions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            category TEXT NOT NULL,
            amount REAL NOT NULL,
            type TEXT NOT NULL,
            date DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    )";

    if (!query.exec(createTableStr)) {
        qWarning() << "Ошибка создания таблицы:" << query.lastError().text();
    }
}

bool DatabaseManager::addTransaction(const QString &name, const QString &category, double amount, const QString &type)
{
    QSqlQuery query;
    query.prepare("INSERT INTO transactions (name, category, amount, type) VALUES (:name, :category, :amount, :type)");
    query.bindValue(":name", name);
    query.bindValue(":category", category);
    query.bindValue(":amount", amount);
    query.bindValue(":type", type);

    if (!query.exec()) {
        qWarning() << "Ошибка добавления записи:" << query.lastError().text();
        return false;
    }
    return true;
}

QVariantList DatabaseManager::getTransactions()
{
    QVariantList list;
    QSqlQuery query("SELECT id, name, category, amount, type, date FROM transactions ORDER BY id DESC");

    while (query.next()) {
        QVariantMap item;
        item["id"] = query.value("id").toInt();
        item["name"] = query.value("name").toString();
        item["category"] = query.value("category").toString();
        item["amount"] = query.value("amount").toDouble();
        item["type"] = query.value("type").toString();
        item["date"] = query.value("date").toString();
        list.append(item);
    }

    return list;
}

double DatabaseManager::getDifference()
{
    double Coming = 0.0;
    double Expense = 0.0;

    QSqlQuery queryComing("SELECT amount FROM transactions WHERE type = 'Coming' ORDER BY id DESC");
    QSqlQuery queryExpence("SELECT amount FROM transactions WHERE type = 'Expense' ORDER BY id DESC");

    while (queryComing.next()) {
        Coming += queryComing.value("amount").toDouble();
    }

    while (queryExpence.next()) {
        Expense += queryExpence.value("amount").toDouble();
    }

    return Coming - Expense;
}

QVariantList DatabaseManager::getCategory()
{
    QVariantList listCategory;

    QSqlQuery queryCategory("select category, sum(amount) as amount from transactions where type = 'Expense' group by category;");

    while (queryCategory.next()) {

        QVariantMap item;
        item["category"] = queryCategory.value("category").toString();
        item["amount"] = queryCategory.value("amount").toDouble();

        listCategory.append(item);
    }

    return listCategory;
}