813 lines
27 KiB
C++
813 lines
27 KiB
C++
#include "databaseutils.h"
|
|
|
|
/*
|
|
*
|
|
"all_count INTEGER DEFAULT 0," 总检验次数
|
|
"manual_cnt INTEGER DEFAULT 0," 手动检验次数
|
|
"auto_cnt INTEGER DEFAULT 0," 自动检验次数
|
|
"up_cnt INTEGER DEFAULT 0," 超过大于范围的电阻值
|
|
"down_cnt INTEGER DEFAULT 0," 超过小于范围的电阻值
|
|
"max_res INTEGER DEFAULT 0," 检测范围范最大电阻值
|
|
"min_res INTEGER DEFAULT 0," 检测范围范最小电阻值
|
|
"auto_time INTEGER DEFAULT 1," 自动检测重复时间
|
|
"auto_sd INTEGER DEFAULT 5," 自动检测误差允许
|
|
"chexing TEXT DEFAULT ''," 车型
|
|
"sm_num INTEGER DEFAULT 0)"; 护面扫码编号
|
|
*
|
|
* */
|
|
|
|
namespace DatabaseUtils
|
|
{
|
|
QSqlDatabase db;
|
|
|
|
bool init_Database()
|
|
{
|
|
db = QSqlDatabase::database();
|
|
if (!db.isValid())
|
|
{
|
|
db = QSqlDatabase::addDatabase("QSQLITE");
|
|
db.setDatabaseName("RB_RES.db");
|
|
}
|
|
|
|
if (!db.open())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return false;
|
|
}
|
|
|
|
QSqlQuery query;
|
|
|
|
// value 阻值 state 0正常 1过大 2过小 flag 0手动 1自动
|
|
QString createResDataTable = "CREATE TABLE IF NOT EXISTS res_data_t ("
|
|
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
|
|
"chexing TEXT DEFAULT '',"
|
|
"sm_num TEXT DEFAULT '',"
|
|
"hot_num TEXT DEFAULT '',"
|
|
"value REAL,"
|
|
"detect TEXT DEFAULT '',"
|
|
"state TEXT DEFAULT '',"
|
|
"flag TEXT DEFAULT '',"
|
|
"name TEXT DEFAULT '',"
|
|
"timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)";
|
|
|
|
if (!query.exec(createResDataTable))
|
|
{
|
|
qDebug() << "Error: Unable to create res_data_t table" << query.lastError();
|
|
}
|
|
|
|
// 检查并添加 hot_num 列（兼容旧数据库）
|
|
query.exec("ALTER TABLE res_data_t ADD COLUMN hot_num TEXT DEFAULT ''");
|
|
|
|
// 日检验次数 手动检测次数 合格 过大 过小 最大值 最小值 合格率 日期
|
|
QString createDailyInspectionTable = "CREATE TABLE IF NOT EXISTS daily_data_t ("
|
|
"ID INTEGER PRIMARY KEY AUTOINCREMENT,"
|
|
"daily_inspection_count INTEGER,"
|
|
"manual_inspection_count INTEGER,"
|
|
"qualified INTEGER,"
|
|
"over_limit INTEGER,"
|
|
"under_limit INTEGER,"
|
|
"max_value REAL,"
|
|
"min_value REAL,"
|
|
"qualification_rate REAL,"
|
|
"date DATE)";
|
|
|
|
if (!query.exec(createDailyInspectionTable))
|
|
{
|
|
qDebug() << "Error: Unable to create daily_data_t table" << query.lastError();
|
|
}
|
|
|
|
QString createInfoTable = "CREATE TABLE IF NOT EXISTS info_t ("
|
|
"all_count INTEGER DEFAULT 0," // 总次数
|
|
"manual_cnt INTEGER DEFAULT 0," // 手动检测次数
|
|
"auto_cnt INTEGER DEFAULT 0," // 自动检测次数
|
|
"qualified_cnt INTEGER DEFAULT 0," // 合格次数
|
|
"up_cnt INTEGER DEFAULT 0," // 超过最大范围次数
|
|
"down_cnt INTEGER DEFAULT 0," // 超过最小范围次数
|
|
"res_value REAL DEFAULT 0," // 电阻标准值
|
|
"res_sd REAL DEFAULT 0," // 电阻误差范围
|
|
"max_res REAL DEFAULT 0," // 最大阻值
|
|
"min_res REAL DEFAULT 0," // 最小阻值
|
|
"auto_time INTEGER DEFAULT 3," // 自动检测时间
|
|
"xc_sd REAL DEFAULT 0," // 线材阻值
|
|
"name TEXT DEFAULT ''," // 姓名
|
|
"pwd TEXT DEFAULT ''," // 密码
|
|
"chexing TEXT DEFAULT ''," // 车型
|
|
"sm_num TEXT DEFAULT ''," // 护面扫码编号
|
|
"hot_num TEXT DEFAULT '')"; // 加热垫扫码编号
|
|
|
|
QString checkTableEmpty = "SELECT COUNT(*) FROM info_t";
|
|
QString insertInitialData = "INSERT INTO info_t (all_count, manual_cnt, auto_cnt, qualified_cnt, up_cnt, down_cnt, res_value, res_sd,max_res, min_res, auto_time, xc_sd, name, pwd, chexing, sm_num, hot_num) "
|
|
"VALUES (0, 0, 0, 0, 0, 0, 0.0, 0.0, 0.0, 0.0, 3, 0.0, '', 'nuobo123', '', '', '');";
|
|
|
|
if (!query.exec(createInfoTable))
|
|
{
|
|
qDebug() << "Error: Unable to create info_t table" << query.lastError();
|
|
}
|
|
|
|
// 检查并添加 hot_num 列（兼容旧数据库）
|
|
query.exec("ALTER TABLE info_t ADD COLUMN hot_num TEXT DEFAULT ''");
|
|
|
|
if (query.exec(checkTableEmpty))
|
|
{
|
|
if (query.next() && query.value(0).toInt() == 0)
|
|
{
|
|
if (!query.exec(insertInitialData))
|
|
{
|
|
qDebug() << "Error inserting initial data:" << query.lastError();
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
qDebug() << "Error checking table:" << query.lastError();
|
|
}
|
|
|
|
return true;
|
|
// db.close();
|
|
}
|
|
|
|
bool closeDatabase()
|
|
{
|
|
if (db.isOpen())
|
|
{
|
|
db.close();
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
bool insert_ResData(const QString &chexing, const QString &sm_num, const QString &hot_num, double value, const QString &detect, const QString &state, const QString &flag, const QString &name)
|
|
{
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
if (!init_Database())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return false;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
query.prepare("INSERT INTO res_data_t (chexing, sm_num, hot_num, value, detect, state, flag, name, timestamp) VALUES (:chexing, :sm_num, :hot_num, :value, :detect, :state, :flag, :name, datetime('now', 'localtime'))");
|
|
query.bindValue(":chexing", chexing);
|
|
query.bindValue(":sm_num", sm_num);
|
|
query.bindValue(":hot_num", hot_num);
|
|
query.bindValue(":value", value);
|
|
query.bindValue(":detect", detect);
|
|
query.bindValue(":state", state);
|
|
query.bindValue(":flag", flag);
|
|
query.bindValue(":name", name);
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to insert data into res_data_t table" << query.lastError();
|
|
return false;
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
int delete_ResData(int id)
|
|
{
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
if (!init_Database())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return -1;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
query.prepare("DELETE FROM res_data_t WHERE id = :id");
|
|
query.bindValue(":id", id);
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to delete data from res_data_t table" << query.lastError();
|
|
return -1;
|
|
}
|
|
|
|
// 没有行受到影响
|
|
if (query.numRowsAffected() == 0)
|
|
{
|
|
qDebug() << "Warning: No data deleted, ID not found";
|
|
return 0;
|
|
}
|
|
|
|
return 1;
|
|
}
|
|
|
|
bool update_ResData(int id, const QString &chexing, const QString &sm_num, const QString &hot_num, double value, const QString &detect, const QString &state, const QString &flag, const QString &name)
|
|
{
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
if (!init_Database())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return false;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
query.prepare("UPDATE res_data_t SET chexing = :chexing, sm_num = :sm_num, hot_num = :hot_num, value = :value, detect = :detect, state = :state, flag = :flag, name = :name WHERE id = :id");
|
|
query.bindValue(":chexing", chexing);
|
|
query.bindValue(":sm_num", sm_num);
|
|
query.bindValue(":hot_num", hot_num);
|
|
query.bindValue(":value", value);
|
|
query.bindValue(":detect", detect);
|
|
query.bindValue(":state", state);
|
|
query.bindValue(":flag", flag);
|
|
query.bindValue(":name", name);
|
|
query.bindValue(":id", id);
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to update data in res_data_t table" << query.lastError();
|
|
return false;
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
QList<QVariantMap> read_ResData()
|
|
{
|
|
QList<QVariantMap> dataList;
|
|
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
if (!init_Database())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return dataList;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
if (!query.exec("SELECT * FROM res_data_t"))
|
|
{
|
|
qDebug() << "Error: Unable to read data from res_data_t table" << query.lastError();
|
|
return dataList;
|
|
}
|
|
|
|
while (query.next())
|
|
{
|
|
QVariantMap data;
|
|
data["id"] = query.value("id");
|
|
data["chexing"] = query.value("chexing");
|
|
data["sm_num"] = query.value("sm_num");
|
|
data["hot_num"] = query.value("hot_num");
|
|
data["value"] = query.value("value");
|
|
data["detect"] = query.value("detect");
|
|
data["state"] = query.value("state");
|
|
data["flag"] = query.value("flag");
|
|
data["name"] = query.value("name");
|
|
data["timestamp"] = query.value("timestamp");
|
|
dataList.append(data);
|
|
}
|
|
|
|
return dataList;
|
|
}
|
|
|
|
int getResTotalRows()
|
|
{
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
if (!init_Database())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
query.prepare("SELECT COUNT(*) FROM res_data_t");
|
|
if (query.exec() && query.next())
|
|
{
|
|
return query.value(0).toInt();
|
|
}
|
|
return 0;
|
|
}
|
|
|
|
QVector<QVariantList> getData(int limit, int offset)
|
|
{
|
|
QVector<QVariantList> data;
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
if (!init_Database())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return data;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
query.prepare("SELECT * FROM res_data_t ORDER BY id LIMIT :limit OFFSET :offset");
|
|
query.bindValue(":limit", limit);
|
|
query.bindValue(":offset", offset);
|
|
|
|
if (query.exec())
|
|
{
|
|
while (query.next())
|
|
{
|
|
QVariantList row;
|
|
for (int i = 0; i < query.record().count(); ++i)
|
|
{
|
|
row.append(query.value(i));
|
|
}
|
|
data.append(row);
|
|
}
|
|
}
|
|
else
|
|
{
|
|
qDebug() << "Error: Unable to fetch data from res_data_t table" << query.lastError();
|
|
}
|
|
|
|
return data;
|
|
}
|
|
|
|
bool insertTestData(int numRows)
|
|
{
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
if (!init_Database())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return false;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
query.prepare("INSERT INTO res_data_t (chexing, sm_num, hot_num, value, detect, state, flag, name, timestamp) VALUES (:chexing, :sm_num, :hot_num, :value, :detect, :state, :flag, :name, datetime('now', 'localtime'))");
|
|
|
|
db.transaction();
|
|
for (int i = 1; i <= numRows; ++i)
|
|
{
|
|
query.bindValue(":chexing", QString("chexing%1").arg(i));
|
|
query.bindValue(":sm_num", QString("sm_num%1").arg(i));
|
|
query.bindValue(":hot_num", QString("hot_num%1").arg(i));
|
|
query.bindValue(":value", i * 1.0);
|
|
query.bindValue(":detect", QString("detect%1").arg(i));
|
|
query.bindValue(":state", QString("state%1").arg(i));
|
|
query.bindValue(":flag", QString("flag%1").arg(i));
|
|
query.bindValue(":name", QString("name%1").arg(i));
|
|
if (!query.exec())
|
|
{
|
|
db.rollback();
|
|
qDebug() << "Error inserting data:" << query.lastError();
|
|
return false;
|
|
}
|
|
}
|
|
db.commit();
|
|
return true;
|
|
}
|
|
|
|
QVector<QVariantList> getAllData()
|
|
{
|
|
QVector<QVariantList> data;
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
if (!init_Database())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return data;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
// 明确指定列顺序，确保与导出表头匹配
|
|
if (!query.exec("SELECT id, chexing, sm_num, hot_num, value, detect, state, flag, name, timestamp FROM res_data_t"))
|
|
{
|
|
qDebug() << "Error: failed to execute query -" << query.lastError();
|
|
return data;
|
|
}
|
|
|
|
while (query.next())
|
|
{
|
|
QVariantList row;
|
|
for (int i = 0; i < query.record().count(); ++i)
|
|
{
|
|
row.append(query.value(i));
|
|
}
|
|
data.append(row);
|
|
}
|
|
|
|
return data;
|
|
}
|
|
|
|
bool insert_Info_t(const QString &column, const QVariant &value)
|
|
{
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
if (!init_Database())
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
|
|
query.prepare("SELECT COUNT(*) FROM info_t");
|
|
if (!query.exec() || !query.next())
|
|
{
|
|
qDebug() << "Error: Unable to check data in info_t table" << query.lastError();
|
|
return false;
|
|
}
|
|
|
|
int count = query.value(0).toInt();
|
|
|
|
if (count == 0)
|
|
{
|
|
query.prepare(QString("INSERT INTO info_t (%1) VALUES (:value)").arg(column));
|
|
}
|
|
else
|
|
{
|
|
query.prepare(QString("UPDATE info_t SET %1 = :value").arg(column));
|
|
}
|
|
|
|
query.bindValue(":value", value);
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to insert/update data in info_t table" << query.lastError();
|
|
return false;
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
bool update_Info_t(const QString &column, const QVariant &value)
|
|
{
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
if (!init_Database())
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
query.prepare(QString("UPDATE info_t SET %1 = :value").arg(column));
|
|
query.bindValue(":value", value);
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to update data in info_t table" << query.lastError();
|
|
return false;
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
QVariant read_Info_t(const QString &column)
|
|
{
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
if (!init_Database())
|
|
{
|
|
return QVariant();
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
query.prepare(QString("SELECT %1 FROM info_t").arg(column));
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to read data from info_t table" << query.lastError();
|
|
return QVariant();
|
|
}
|
|
|
|
if (query.next())
|
|
{
|
|
return query.value(0);
|
|
}
|
|
|
|
return QVariant();
|
|
}
|
|
|
|
bool delete_Info_t()
|
|
{
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
if (!init_Database())
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
query.prepare("DELETE FROM info_t");
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to clear data from info_t table" << query.lastError();
|
|
return false;
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
QMap<QString, QVariant> read_all_Info_t()
|
|
{
|
|
QMap<QString, QVariant> data;
|
|
QSqlDatabase db = QSqlDatabase::database();
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
if (!init_Database())
|
|
{
|
|
return data;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
query.prepare("SELECT * FROM info_t");
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to read data from info_t table" << query.lastError();
|
|
return data;
|
|
}
|
|
|
|
if (query.next())
|
|
{
|
|
for (int i = 0; i < query.record().count(); ++i)
|
|
{
|
|
QString columnName = query.record().fieldName(i);
|
|
data[columnName] = query.value(i);
|
|
}
|
|
}
|
|
|
|
return data;
|
|
}
|
|
|
|
bool insertOrUpdateDailyData(int daily_inspection_count, int manual_inspection_count, int qualified, int over_limit, int under_limit, double max_value, double min_value, double qualification_rate)
|
|
{
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
if (!init_Database())
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
|
|
QString currentDate = QDate::currentDate().toString("yyyy-MM-dd");
|
|
|
|
query.prepare("SELECT ID, date FROM daily_data_t ORDER BY ID DESC LIMIT 1");
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to retrieve data from daily_data_t table" << query.lastError();
|
|
return false;
|
|
}
|
|
|
|
int latestId = -1;
|
|
QString latestDate;
|
|
if (query.next())
|
|
{
|
|
latestId = query.value(0).toInt();
|
|
latestDate = query.value(1).toString();
|
|
}
|
|
|
|
// qDebug() << "latestDate:" << latestDate;
|
|
// qDebug() << "currentDate:" << currentDate;
|
|
|
|
QString latestDateOnly = latestDate.left(10);
|
|
|
|
// qDebug() << "latestDateOnly:" << latestDateOnly;
|
|
|
|
if (latestDateOnly == currentDate)
|
|
{
|
|
query.prepare("UPDATE daily_data_t SET daily_inspection_count = :daily_inspection_count, "
|
|
"manual_inspection_count = :manual_inspection_count, qualified = :qualified, "
|
|
"over_limit = :over_limit, under_limit = :under_limit, max_value = :max_value, "
|
|
"min_value = :min_value, qualification_rate = :qualification_rate "
|
|
"WHERE ID = :id");
|
|
query.bindValue(":id", latestId);
|
|
}
|
|
else
|
|
{
|
|
query.prepare("INSERT INTO daily_data_t (daily_inspection_count, manual_inspection_count, qualified, over_limit, under_limit, max_value, min_value, qualification_rate, date) "
|
|
"VALUES (:daily_inspection_count, :manual_inspection_count, :qualified, :over_limit, :under_limit, :max_value, :min_value, :qualification_rate, datetime('now', 'localtime'))");
|
|
}
|
|
|
|
query.bindValue(":daily_inspection_count", daily_inspection_count);
|
|
query.bindValue(":manual_inspection_count", manual_inspection_count);
|
|
query.bindValue(":qualified", qualified);
|
|
query.bindValue(":over_limit", over_limit);
|
|
query.bindValue(":under_limit", under_limit);
|
|
query.bindValue(":max_value", max_value);
|
|
query.bindValue(":min_value", min_value);
|
|
query.bindValue(":qualification_rate", qualification_rate);
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to insert or update data in daily_data_t table" << query.lastError();
|
|
return false;
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
QList<QVariantList> getLastFiveDailyDataSkippingToday()
|
|
{
|
|
QList<QVariantList> dataList;
|
|
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return dataList;
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
|
|
QString currentDate = QDate::currentDate().toString("yyyy-MM-dd");
|
|
|
|
query.prepare("SELECT date FROM daily_data_t ORDER BY ID DESC LIMIT 1");
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to retrieve data from daily_data_t table" << query.lastError();
|
|
return dataList;
|
|
}
|
|
|
|
QString latestDate;
|
|
if (query.next())
|
|
{
|
|
latestDate = query.value(0).toString();
|
|
}
|
|
|
|
QString latestDateOnly = latestDate.left(10);
|
|
|
|
// qDebug() << "currentDate:" << currentDate;
|
|
// qDebug() << "latestDateOnly:" << latestDateOnly;
|
|
|
|
if (latestDateOnly == currentDate)
|
|
{
|
|
query.prepare("SELECT ID, daily_inspection_count, manual_inspection_count, qualified, over_limit, under_limit, max_value, min_value, qualification_rate, date "
|
|
"FROM daily_data_t WHERE date < :currentDate ORDER BY ID DESC LIMIT 5");
|
|
query.bindValue(":currentDate", currentDate);
|
|
}
|
|
else
|
|
{
|
|
query.prepare("SELECT ID, daily_inspection_count, manual_inspection_count, qualified, over_limit, under_limit, max_value, min_value, qualification_rate, date "
|
|
"FROM daily_data_t ORDER BY ID DESC LIMIT 5");
|
|
}
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to retrieve data from daily_data_t table" << query.lastError();
|
|
return dataList;
|
|
}
|
|
|
|
while (query.next())
|
|
{
|
|
QVariantList row;
|
|
row << query.value(0) // ID
|
|
<< query.value(1) // daily_inspection_count
|
|
<< query.value(2) // manual_inspection_count
|
|
<< query.value(3) // qualified
|
|
<< query.value(4) // over_limit
|
|
<< query.value(5) // under_limit
|
|
<< query.value(6) // max_value
|
|
<< query.value(7) // min_value
|
|
<< query.value(8) // qualification_rate
|
|
<< query.value(9); // date
|
|
dataList.append(row);
|
|
}
|
|
|
|
return dataList;
|
|
}
|
|
|
|
QString get_today_daliy_t(int &daily_cnt, int &daily_cnt_manual,
|
|
int &daily_qualified_cnt, int &daily_cnt_over,
|
|
int &daily_cnt_down, double &daily_cnt_max,
|
|
double &daily_cnt_min)
|
|
{
|
|
|
|
if (!db.isOpen() && !db.open())
|
|
{
|
|
qDebug() << "Error: Unable to open the database";
|
|
return "";
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
|
|
QString currentDate = QDate::currentDate().toString("yyyy-MM-dd");
|
|
// qDebug() << "Current date: " << currentDate;
|
|
|
|
query.prepare("SELECT date FROM daily_data_t ORDER BY ID DESC LIMIT 1");
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to retrieve data from daily_data_t table" << query.lastError();
|
|
return "";
|
|
}
|
|
|
|
QString latestDate;
|
|
if (query.next())
|
|
{
|
|
latestDate = query.value(0).toString();
|
|
// qDebug() << "Latest date in database: " << latestDate;
|
|
}
|
|
else
|
|
{
|
|
qDebug() << "No records found in daily_data_t table";
|
|
return "";
|
|
}
|
|
|
|
QString latestDateOnly = latestDate.left(10);
|
|
// qDebug() << "Latest date only: " << latestDateOnly;
|
|
|
|
if (latestDateOnly == currentDate)
|
|
{
|
|
query.prepare("SELECT daily_inspection_count, manual_inspection_count, qualified, over_limit, under_limit, max_value, min_value "
|
|
"FROM daily_data_t WHERE date(date) = :date");
|
|
query.bindValue(":date", currentDate);
|
|
|
|
if (!query.exec())
|
|
{
|
|
qDebug() << "Error: Unable to retrieve today's data from daily_data_t table" << query.lastError();
|
|
return "";
|
|
}
|
|
|
|
if (query.next())
|
|
{
|
|
daily_cnt = query.value(0).toInt(); // daily_inspection_count
|
|
// qDebug() << "daily_cnt db " << daily_cnt;
|
|
daily_cnt_manual = query.value(1).toInt(); // manual_inspection_count
|
|
daily_qualified_cnt = query.value(2).toInt(); // qualified
|
|
daily_cnt_over = query.value(3).toInt(); // over_limit
|
|
daily_cnt_down = query.value(4).toInt(); // under_limit
|
|
daily_cnt_max = query.value(5).toDouble(); // max_value
|
|
daily_cnt_min = query.value(6).toDouble(); // min_value
|
|
}
|
|
else
|
|
{
|
|
qDebug() << "No data found for today's date";
|
|
return "";
|
|
}
|
|
|
|
return latestDateOnly;
|
|
}
|
|
else
|
|
{
|
|
daily_cnt = 0;
|
|
daily_cnt_manual = 0;
|
|
daily_qualified_cnt = 0;
|
|
daily_cnt_over = 0;
|
|
daily_cnt_down = 0;
|
|
daily_cnt_max = 0;
|
|
daily_cnt_min = 0;
|
|
qDebug() << "Latest date does not match current date";
|
|
return "";
|
|
}
|
|
}
|
|
|
|
bool updateLastDate()
|
|
{
|
|
if (!db.isOpen())
|
|
{
|
|
qWarning() << "Database is not open!";
|
|
return false;
|
|
}
|
|
|
|
QSqlQuery query(db);
|
|
if (!query.exec("SELECT date FROM daily_data_t ORDER BY ID DESC LIMIT 1"))
|
|
{
|
|
qWarning() << "Failed to execute query:" << query.lastError().text();
|
|
return false;
|
|
}
|
|
|
|
if (!query.next())
|
|
{
|
|
qWarning() << "No records found in the table.";
|
|
return false;
|
|
}
|
|
|
|
QDate lastDate = query.value(0).toDate();
|
|
qDebug() << "Last date in the table:" << lastDate;
|
|
|
|
QDate newDate = lastDate.addDays(-1);
|
|
qDebug() << "New date after subtracting one day:" << newDate;
|
|
QSqlQuery updateQuery(db);
|
|
updateQuery.prepare("UPDATE daily_data_t SET date = :newDate WHERE ID = (SELECT MAX(ID) FROM daily_data_t)");
|
|
updateQuery.bindValue(":newDate", newDate);
|
|
|
|
if (!updateQuery.exec())
|
|
{
|
|
qWarning() << "Failed to update date:" << updateQuery.lastError().text();
|
|
return false;
|
|
}
|
|
|
|
qDebug() << "Date updated successfully.";
|
|
return true;
|
|
}
|
|
|
|
}
|