496 lines
14 KiB
C++
496 lines
14 KiB
C++
#include "SqlOpr.h"
|
|
#include <QFile>
|
|
#include <QSqlDatabase>
|
|
#include <QSqlError>
|
|
#include <QSqlQuery>
|
|
#include <QVariant>
|
|
|
|
ACTSqlOpr::~ACTSqlOpr()
|
|
{
|
|
if (db_.isOpen()) {
|
|
db_.close();
|
|
}
|
|
}
|
|
|
|
bool ACTSqlOpr::OpenDb(const std::string& path)
|
|
{
|
|
dbPath_ = path;
|
|
db_ = QSqlDatabase::addDatabase("QSQLITE", "account_connection");
|
|
db_.setDatabaseName(QString::fromStdString(path));
|
|
|
|
if (!db_.open()) {
|
|
lastErr_ = fmt::format("无法打开数据库：{}", db_.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
|
|
if (!generateDefaultTB()) {
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
bool ACTSqlOpr::UpdateAccount(AccountRecord& record)
|
|
{
|
|
QSqlQuery query(db_);
|
|
query.prepare("UPDATE account_records SET money = ?, pay_type = ?, dt = ?, "
|
|
"thing = ?, classify = ?, remark = ?, addition_file = ? WHERE id = ?");
|
|
|
|
query.addBindValue(record.money);
|
|
query.addBindValue(QString::fromStdString(record.payType));
|
|
query.addBindValue(QString::fromStdString(record.dt));
|
|
query.addBindValue(QString::fromStdString(record.thing));
|
|
query.addBindValue(QString::fromStdString(record.classify));
|
|
query.addBindValue(QString::fromStdString(record.remark));
|
|
query.addBindValue(QString::fromStdString(record.additionFile));
|
|
query.addBindValue(record.id);
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("执行更新失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
QSqlDatabase& ACTSqlOpr::GetDb()
|
|
{
|
|
return db_;
|
|
}
|
|
|
|
const std::string& ACTSqlOpr::GetLastErr()
|
|
{
|
|
return lastErr_;
|
|
}
|
|
|
|
bool ACTSqlOpr::AppendAccount(AccountRecord& record)
|
|
{
|
|
QSqlQuery query(db_);
|
|
query.prepare("INSERT INTO account_records "
|
|
"(money, pay_type, dt, thing, classify, remark, addition_file) "
|
|
"VALUES (?, ?, ?, ?, ?, ?, ?)");
|
|
|
|
query.addBindValue(record.money);
|
|
query.addBindValue(QString::fromStdString(record.payType));
|
|
query.addBindValue(QString::fromStdString(record.dt));
|
|
query.addBindValue(QString::fromStdString(record.thing));
|
|
query.addBindValue(QString::fromStdString(record.classify));
|
|
query.addBindValue(QString::fromStdString(record.remark));
|
|
query.addBindValue(QString::fromStdString(record.additionFile));
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("执行插入失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
|
|
record.id = query.lastInsertId().toInt();
|
|
return true;
|
|
}
|
|
|
|
bool ACTSqlOpr::DeleteAccount(int32_t id)
|
|
{
|
|
QSqlQuery query(db_);
|
|
query.prepare("DELETE FROM account_records WHERE id = ?");
|
|
query.addBindValue(id);
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("执行删除失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
bool ACTSqlOpr::GetAccountList(AccountRecordList& ret)
|
|
{
|
|
QSqlQuery query(db_);
|
|
query.prepare("SELECT id, money, pay_type, dt, thing, classify, remark, addition_file "
|
|
"FROM account_records ORDER BY dt DESC");
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("查询失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
|
|
ret.clear();
|
|
while (query.next()) {
|
|
AccountRecord record;
|
|
record.id = query.value(0).toInt();
|
|
record.money = query.value(1).toInt();
|
|
record.payType = query.value(2).toString().toStdString();
|
|
record.dt = query.value(3).toString().toStdString();
|
|
record.thing = query.value(4).toString().toStdString();
|
|
record.classify = query.value(5).toString().toStdString();
|
|
record.remark = query.value(6).toString().toStdString();
|
|
record.additionFile = query.value(7).toString().toStdString();
|
|
|
|
ret.push_back(record);
|
|
}
|
|
return true;
|
|
}
|
|
|
|
bool ACTSqlOpr::generateDefaultTB()
|
|
{
|
|
QSqlQuery query(db_);
|
|
QString sql = "CREATE TABLE IF NOT EXISTS account_records ("
|
|
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
|
|
"money INTEGER NOT NULL, "
|
|
"pay_type TEXT NOT NULL, "
|
|
"dt TEXT NOT NULL, "
|
|
"thing TEXT, "
|
|
"classify TEXT, "
|
|
"remark TEXT, "
|
|
"addition_file TEXT)";
|
|
|
|
if (!query.exec(sql)) {
|
|
lastErr_ = fmt::format("创建表失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
ACTSqlOpr::ACTSqlOpr()
|
|
{
|
|
}
|
|
|
|
ComSqlOpr::ComSqlOpr(QSqlDatabase& db) : db_(db)
|
|
{
|
|
generateDefaultTB();
|
|
CheckContent();
|
|
}
|
|
|
|
bool ComSqlOpr::GetClassifyList(ClassifyRecordList& ret)
|
|
{
|
|
QSqlQuery query(db_);
|
|
query.prepare("SELECT id, key, value, type, mark FROM common_records WHERE key = '分类'");
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("查询失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
|
|
ret.clear();
|
|
while (query.next()) {
|
|
CommonRecord record;
|
|
record.id = query.value(0).toInt();
|
|
record.key = query.value(1).toString().toStdString();
|
|
record.value = query.value(2).toString().toStdString();
|
|
record.type = query.value(3).toString().toStdString();
|
|
record.mark = query.value(4).toString().toStdString();
|
|
ret.push_back(record);
|
|
}
|
|
return true;
|
|
}
|
|
|
|
bool ComSqlOpr::GetItem(CommonRecord& ret, const std::string& key)
|
|
{
|
|
QSqlQuery query(db_);
|
|
query.prepare("SELECT id, key, value, type, mark FROM common_records "
|
|
"WHERE key = ? ORDER BY id DESC LIMIT 1");
|
|
query.addBindValue(QString::fromStdString(key));
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("查询失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
|
|
if (query.next()) {
|
|
ret.id = query.value(0).toInt();
|
|
ret.key = query.value(1).toString().toStdString();
|
|
ret.value = query.value(2).toString().toStdString();
|
|
ret.type = query.value(3).toString().toStdString();
|
|
ret.mark = query.value(4).toString().toStdString();
|
|
return true;
|
|
}
|
|
|
|
lastErr_ = fmt::format("未找到key为'{}'的记录", key);
|
|
return false;
|
|
}
|
|
|
|
bool ComSqlOpr::UpdateItem(CommonRecord& ret)
|
|
{
|
|
if (ret.id <= 0) {
|
|
lastErr_ = "无效的记录ID";
|
|
return false;
|
|
}
|
|
|
|
QSqlQuery query(db_);
|
|
query.prepare("UPDATE common_records SET "
|
|
"key = ?, value = ?, type = ?, mark = ? "
|
|
"WHERE id = ?");
|
|
|
|
query.addBindValue(QString::fromStdString(ret.key));
|
|
query.addBindValue(QString::fromStdString(ret.value));
|
|
query.addBindValue(QString::fromStdString(ret.type));
|
|
query.addBindValue(QString::fromStdString(ret.mark));
|
|
query.addBindValue(ret.id);
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("更新失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
bool ComSqlOpr::InserItem(CommonRecord& ret)
|
|
{
|
|
QSqlQuery query(db_);
|
|
query.prepare("INSERT INTO common_records (key, value, type, mark) "
|
|
"VALUES (?, ?, ?, ?)");
|
|
|
|
query.addBindValue(QString::fromStdString(ret.key));
|
|
query.addBindValue(QString::fromStdString(ret.value));
|
|
query.addBindValue(QString::fromStdString(ret.type));
|
|
query.addBindValue(QString::fromStdString(ret.mark));
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("插入失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
|
|
ret.id = query.lastInsertId().toInt();
|
|
return true;
|
|
}
|
|
|
|
bool ComSqlOpr::DeleteItem(const std::string& value)
|
|
{
|
|
QSqlQuery query(db_);
|
|
query.prepare("DELETE FROM common_records WHERE value = ? and key = '分类'");
|
|
query.addBindValue(QString::fromStdString(value));
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("删除失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
|
|
if (query.numRowsAffected() == 0) {
|
|
lastErr_ = fmt::format("未找到value为'{}'的记录", value);
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
bool ComSqlOpr::CheckContent()
|
|
{
|
|
if (!db_.transaction()) {
|
|
lastErr_ = fmt::format("开始事务失败: {}", db_.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
|
|
const std::vector<std::tuple<std::string, std::string>> defaultCategories = {{"分类", "默认"}};
|
|
|
|
for (const auto& [key, val] : defaultCategories) {
|
|
if (!CheckAndInsert(key, val, "", "")) {
|
|
db_.rollback();
|
|
return false;
|
|
}
|
|
}
|
|
|
|
if (!db_.commit()) {
|
|
lastErr_ = fmt::format("提交事务失败: {}", db_.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
bool ComSqlOpr::CheckClassifyExist(const std::string& value)
|
|
{
|
|
QSqlQuery query(db_);
|
|
query.prepare("SELECT 1 FROM common_records WHERE value = ? and key = '分类'");
|
|
query.addBindValue(QString::fromStdString(value));
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("查询失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
return query.next();
|
|
}
|
|
|
|
const std::string& ComSqlOpr::GetLastErr()
|
|
{
|
|
return lastErr_;
|
|
}
|
|
|
|
bool ComSqlOpr::CheckAndInsert(const std::string& key, const std::string& value, const std::string& type, const std::string& mark)
|
|
{
|
|
QSqlQuery query(db_);
|
|
query.prepare("SELECT 1 FROM common_records WHERE key = ? LIMIT 1");
|
|
query.addBindValue(QString::fromStdString(key));
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("查询失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
|
|
if (query.next()) {
|
|
return true; // 记录已存在
|
|
}
|
|
|
|
query.prepare("INSERT INTO common_records (key, value, type, mark) VALUES (?, ?, ?, ?)");
|
|
query.addBindValue(QString::fromStdString(key));
|
|
query.addBindValue(QString::fromStdString(value));
|
|
query.addBindValue(QString::fromStdString(type));
|
|
query.addBindValue(QString::fromStdString(mark));
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = fmt::format("插入失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
bool ComSqlOpr::generateDefaultTB()
|
|
{
|
|
QSqlQuery query(db_);
|
|
QString sql = "CREATE TABLE IF NOT EXISTS common_records ("
|
|
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
|
|
"key TEXT NOT NULL, "
|
|
"value TEXT, "
|
|
"type TEXT, "
|
|
"mark TEXT)";
|
|
|
|
if (!query.exec(sql)) {
|
|
lastErr_ = fmt::format("创建表失败: {}", query.lastError().text().toStdString());
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
RepaySqlOpr::RepaySqlOpr(QSqlDatabase& db) : db_(db)
|
|
{
|
|
generateDefaultTB();
|
|
}
|
|
|
|
bool RepaySqlOpr::GetRepayResult(RepayRecordList& ret, int32_t accID)
|
|
{
|
|
ret.clear();
|
|
lastErr_.clear();
|
|
|
|
if (!db_.isOpen()) {
|
|
lastErr_ = "Database is not open";
|
|
return false;
|
|
}
|
|
|
|
QSqlQuery query(db_);
|
|
query.prepare("SELECT id, accID, money, dt, remark FROM repay_record WHERE accID = :accID");
|
|
query.bindValue(":accID", accID);
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = query.lastError().text().toStdString();
|
|
return false;
|
|
}
|
|
|
|
while (query.next()) {
|
|
RepayRecord record;
|
|
record.id = query.value("id").toInt();
|
|
record.accID = query.value("accID").toInt();
|
|
record.money = query.value("money").toInt();
|
|
record.dt = query.value("dt").toString().toStdString();
|
|
record.remark = query.value("remark").toString().toStdString();
|
|
ret.push_back(record);
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
bool RepaySqlOpr::InsertRepayRecord(RepayRecord& ret)
|
|
{
|
|
lastErr_.clear();
|
|
|
|
if (!db_.isOpen()) {
|
|
lastErr_ = "Database is not open";
|
|
return false;
|
|
}
|
|
|
|
QSqlQuery query(db_);
|
|
query.prepare("INSERT INTO repay_record (accID, money, dt, remark) "
|
|
"VALUES (:accID, :money, :dt, :remark)");
|
|
query.bindValue(":accID", ret.accID);
|
|
query.bindValue(":money", ret.money);
|
|
query.bindValue(":dt", QString::fromStdString(ret.dt));
|
|
query.bindValue(":remark", QString::fromStdString(ret.remark));
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = query.lastError().text().toStdString();
|
|
return false;
|
|
}
|
|
|
|
// Get the auto-generated id
|
|
ret.id = query.lastInsertId().toInt();
|
|
return true;
|
|
}
|
|
|
|
bool RepaySqlOpr::updateRepayRecord(RepayRecord& ret)
|
|
{
|
|
lastErr_.clear();
|
|
|
|
if (!db_.isOpen()) {
|
|
lastErr_ = "Database is not open";
|
|
return false;
|
|
}
|
|
|
|
QSqlQuery query(db_);
|
|
query.prepare("UPDATE repay_record SET accID = :accID, money = :money, "
|
|
"dt = :dt, remark = :remark WHERE id = :id");
|
|
query.bindValue(":accID", ret.accID);
|
|
query.bindValue(":money", ret.money);
|
|
query.bindValue(":dt", QString::fromStdString(ret.dt));
|
|
query.bindValue(":remark", QString::fromStdString(ret.remark));
|
|
query.bindValue(":id", ret.id);
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = query.lastError().text().toStdString();
|
|
return false;
|
|
}
|
|
|
|
return query.numRowsAffected() > 0;
|
|
}
|
|
|
|
bool RepaySqlOpr::deleteRepayRecord(int32_t id)
|
|
{
|
|
lastErr_.clear();
|
|
|
|
if (!db_.isOpen()) {
|
|
lastErr_ = "Database is not open";
|
|
return false;
|
|
}
|
|
|
|
QSqlQuery query(db_);
|
|
query.prepare("DELETE FROM repay_record WHERE id = :id");
|
|
query.bindValue(":id", id);
|
|
|
|
if (!query.exec()) {
|
|
lastErr_ = query.lastError().text().toStdString();
|
|
return false;
|
|
}
|
|
|
|
return query.numRowsAffected() > 0;
|
|
}
|
|
|
|
const std::string& RepaySqlOpr::GetLastErr()
|
|
{
|
|
return lastErr_;
|
|
}
|
|
|
|
bool RepaySqlOpr::generateDefaultTB()
|
|
{
|
|
lastErr_.clear();
|
|
|
|
if (!db_.isOpen()) {
|
|
lastErr_ = "Database is not open";
|
|
return false;
|
|
}
|
|
|
|
QSqlQuery query(db_);
|
|
QString createTable = "CREATE TABLE IF NOT EXISTS repay_record ("
|
|
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
|
|
"accID INTEGER NOT NULL, "
|
|
"money INTEGER NOT NULL, "
|
|
"dt TEXT NOT NULL, "
|
|
"remark TEXT)";
|
|
|
|
if (!query.exec(createTable)) {
|
|
lastErr_ = query.lastError().text().toStdString();
|
|
return false;
|
|
}
|
|
|
|
return true;
|
|
}
|