384 lines
11 KiB
C++
384 lines
11 KiB
C++
#include "sqlControler.h"
|
|
bool debugMode = false;
|
|
std::vector<std::string> getFutureDates()
|
|
{
|
|
std::vector<std::string> futureDates;
|
|
|
|
// 获取当前时间点
|
|
auto now = std::chrono::system_clock::now();
|
|
|
|
for (int i = 0; i < 30; i++) {
|
|
// 将当前时间点加上指定天数
|
|
auto future = now + std::chrono::hours(24 * i);
|
|
|
|
// 将时间转换为时间结构体
|
|
std::time_t futureTime = std::chrono::system_clock::to_time_t(future);
|
|
|
|
// 将时间结构体格式化为 "年月日" 的字符串
|
|
std::tm* futureTm = std::localtime(&futureTime);
|
|
std::ostringstream oss;
|
|
oss << futureTm->tm_year + 1900 << '-' << std::setw(2) << std::setfill('0') << futureTm->tm_mon + 1
|
|
<< '-' << std::setw(2) << std::setfill('0') << futureTm->tm_mday;
|
|
|
|
futureDates.push_back(oss.str());
|
|
}
|
|
|
|
return futureDates;
|
|
}
|
|
int callback(void* data, int argc, char** argv, char** azColName) {
|
|
std::vector<sqlDatas>* result = reinterpret_cast<std::vector<sqlDatas>*>(data);
|
|
|
|
for (int i = 0; i < argc; i++) {
|
|
std::string columnName = azColName[i];
|
|
std::string columnValue = argv[i] ? argv[i] : "NULL";
|
|
sqlDatas data;
|
|
data.dataName = columnName;
|
|
data.valueText = columnValue;
|
|
result->push_back(data);
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
void sqlController::initPara()
|
|
{
|
|
QString destinationFilePath;
|
|
{//每次启动备份文件
|
|
QFile sourceFile(QString::fromStdString(dataBasePath));
|
|
if (!sourceFile.open(QIODevice::ReadOnly)) {
|
|
qDebug() << "无法打开源文件： " << sourceFile.errorString();
|
|
}
|
|
else
|
|
{
|
|
destinationFilePath = QString::fromStdString(dataBasePath);
|
|
destinationFilePath.insert(destinationFilePath.lastIndexOf(".db"), "-temp");
|
|
QFile destinationFile(destinationFilePath);
|
|
if (!destinationFile.open(QIODevice::WriteOnly)) {
|
|
qDebug() << "无法打开目标文件： " << destinationFile.errorString();
|
|
}
|
|
else
|
|
{
|
|
QByteArray fileData = sourceFile.readAll();
|
|
destinationFile.write(fileData);
|
|
sourceFile.close();
|
|
destinationFile.close();
|
|
}
|
|
}
|
|
//qDebug() << "文件备份成功: " << destinationFile ;
|
|
}//打开数据库文件
|
|
dataBasePath = destinationFilePath.toStdString();
|
|
int rc = sqlite3_open(dataBasePath.c_str(), &db); // 打开内存数据库，也可以指定文件路径打开实际数据库
|
|
|
|
if (rc != SQLITE_OK) {
|
|
std::cout << "无法打开数据库: " << sqlite3_errmsg(db) << "path:" << dataBasePath;
|
|
return;
|
|
}
|
|
}
|
|
void sqlController::checkCol(sqlite3* db, std::string tableName, std::string columnName)
|
|
{
|
|
try
|
|
{
|
|
// 执行查询，检查表是否存在并且包含指定的列
|
|
const char* query = "SELECT sql FROM sqlite_master WHERE type='table' AND name=?";
|
|
sqlite3_stmt* stmt;
|
|
int rc = sqlite3_prepare_v2(db, query, -1, &stmt, nullptr);
|
|
|
|
if (rc != SQLITE_OK) {
|
|
std::cerr << "无法准备查询: " << sqlite3_errmsg(db) << std::endl;
|
|
}
|
|
sqlite3_bind_text(stmt, 1, tableName.c_str(), -1, SQLITE_STATIC);
|
|
|
|
if (sqlite3_step(stmt) == SQLITE_ROW) {
|
|
// 表存在，现在检查是否包含指定的列
|
|
const char* tableDefinition = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
|
|
if (sqlite3_strlike(columnName.c_str(), tableDefinition, 0)) {
|
|
//std::cout << "table '" << tableName << "' exist and true '" << columnName << "'" << std::endl;
|
|
}
|
|
else {
|
|
std::cout << "table '" << tableName << "' exist ,but not true '" << columnName << "'" << std::endl;
|
|
}
|
|
}
|
|
|
|
// 清理资源
|
|
sqlite3_finalize(stmt);
|
|
|
|
}
|
|
|
|
catch (std::exception& e) {
|
|
std::cerr << "SQLite异常: " << e.what() << std::endl;
|
|
}
|
|
|
|
}
|
|
bool sqlController::addData(sqlDataStruct data)
|
|
{
|
|
if (data.SN.empty())//SN码为空，将停止处理
|
|
{
|
|
qDebug() << "[INFO] " << " SN empty!";
|
|
return false;
|
|
}
|
|
|
|
std::string query = "SELECT COUNT(*) FROM \'" + getCurrentTableName() + "\' WHERE sn = '" + data.SN + "'; ";
|
|
|
|
if (debugMode == true)
|
|
{
|
|
std::cout << query << "\n" << endl;
|
|
qDebug() << "sql query:" << QString::fromStdString(query);
|
|
}
|
|
sqlite3_stmt* statement;
|
|
|
|
int result = sqlite3_prepare_v2(db, query.c_str(), -1, &statement, nullptr);//执行语句
|
|
|
|
if (result == SQLITE_OK) {
|
|
if (sqlite3_step(statement) == SQLITE_ROW) {
|
|
if (sqlite3_column_int(statement, 0) > 0)//查询出来的行数大于0
|
|
{
|
|
//拼接数据
|
|
std::string updateQuery = "UPDATE \'" + getCurrentTableName() + "\' SET send=1,";
|
|
for (auto d : data.dataVec)
|
|
{
|
|
updateQuery += d.dataName + " = ";
|
|
if (d.valueDouble != 0)
|
|
updateQuery += std::to_string(d.valueDouble) + ",";
|
|
else if (d.valueInt != 0)
|
|
updateQuery += std::to_string(d.valueInt) + ",";
|
|
else//TEXT
|
|
updateQuery += "\'" + d.valueText + "\',";
|
|
}
|
|
updateQuery.replace(updateQuery.find_last_of(","), 1, " WHERE SN = \'" + data.SN + "\';");
|
|
int rc = sqlite3_exec(db, updateQuery.c_str(), nullptr, nullptr, nullptr);
|
|
if (debugMode == true)
|
|
{
|
|
std::cout << updateQuery << endl;
|
|
qDebug() << "sql query:" << QString::fromStdString(updateQuery);
|
|
}
|
|
if (rc != SQLITE_OK) {
|
|
std::cout << "更新操作失败: " << sqlite3_errmsg(db) << ";" << updateQuery;
|
|
sqlite3_finalize(statement);
|
|
return false;
|
|
}
|
|
}
|
|
else {
|
|
std::string insertQuery = "INSERT INTO \'" + getCurrentTableName() + "\' (send,SN,";
|
|
for (auto d : data.dataVec)
|
|
insertQuery += d.dataName + ",";
|
|
insertQuery.replace(insertQuery.find_last_of(","), 1, ") VALUES (1,\'" + data.SN + "\',");
|
|
for (auto d : data.dataVec)
|
|
{
|
|
if (d.valueDouble != 0)
|
|
insertQuery += std::to_string(d.valueDouble) + ",";
|
|
else if (d.valueInt != 0)
|
|
insertQuery += std::to_string(d.valueInt) + ",";
|
|
else//TEXT
|
|
insertQuery += "\'" + d.valueText + "\',";
|
|
}
|
|
insertQuery.replace(insertQuery.find_last_of(","), 1, " );");
|
|
if (debugMode == true)
|
|
{
|
|
std::cout << insertQuery << endl;
|
|
qDebug() << "sql query:" << QString::fromStdString(insertQuery);
|
|
}
|
|
int rc = sqlite3_exec(db, insertQuery.c_str(), nullptr, nullptr, nullptr);
|
|
if (rc != SQLITE_OK) {
|
|
std::cout << "插入操作失败: " << sqlite3_errmsg(db) << ";" << insertQuery;
|
|
sqlite3_finalize(statement);
|
|
return false;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
sqlite3_finalize(statement);
|
|
return true;
|
|
}
|
|
int sqlController::checktSn(std::string SN)
|
|
{
|
|
return 0;
|
|
std::string query = "SELECT COUNT(*) FROM \'" + getCurrentTableName() + "\' WHERE sn LIKE '" + SN + "%'; ";
|
|
if (debugMode == true)
|
|
{
|
|
std::cout << query << endl;
|
|
qDebug() << "sql query:" << QString::fromStdString(query);
|
|
}
|
|
sqlite3_stmt* statement;
|
|
int result = sqlite3_prepare_v2(db, query.c_str(), -1, &statement, nullptr);
|
|
int resultReturn = -1;
|
|
int num = 0;
|
|
if (result == SQLITE_OK)
|
|
if (sqlite3_step(statement) == SQLITE_ROW)
|
|
num = sqlite3_column_int(statement, 0);
|
|
if (num > 0)
|
|
{
|
|
query = "SELECT send FROM \'" + getCurrentTableName() + "\' WHERE sn =\'" + SN + "-" + std::to_string(num - 1) + "\' ; ";
|
|
if (debugMode == true)
|
|
{
|
|
std::cout << query << endl;
|
|
qDebug() << "sql query:" << QString::fromStdString(query);
|
|
}
|
|
std::vector<sqlDatas> rowData;
|
|
int rc = sqlite3_exec(db, query.c_str(), callback, &rowData, 0);
|
|
for (int i = 0; i < rowData.size(); i++)
|
|
{
|
|
if (!rowData[i].valueText.compare("1"))
|
|
{
|
|
num -= 1;
|
|
}
|
|
}
|
|
}
|
|
sqlite3_finalize(statement);
|
|
return num;
|
|
}
|
|
#if 0
|
|
sqlDataStruct sqlController::selectData(std::string SN, int snCountIndex, int send)
|
|
{
|
|
std::string query;
|
|
int rc;
|
|
//send设true
|
|
{
|
|
query = "UPDATE \'" + getCurrentTableName() + "\' SET send = " + std::to_string(send) + " WHERE sn = \'" + SN + "-" + std::to_string(snCountIndex) + "\' ; ";
|
|
if (debugMode == true)
|
|
{
|
|
std::cout << query << endl;
|
|
qDebug() << "sql query:" << QString::fromStdString(query);
|
|
}
|
|
rc = sqlite3_exec(db, query.c_str(), nullptr, nullptr, nullptr);
|
|
}
|
|
|
|
//////////////////////////////
|
|
if (snCountIndex > 0)
|
|
query = "SELECT * FROM \'" + getCurrentTableName() + "\' WHERE sn =\'" + SN + "-" + std::to_string(snCountIndex) + "\' ; ";
|
|
else
|
|
query = "SELECT * FROM \'" + getCurrentTableName() + "\' WHERE sn =\'" + SN + "-0\' ; ";
|
|
|
|
if (debugMode == true)
|
|
{
|
|
std::cout << query << endl;
|
|
qDebug() << "sql query:" << QString::fromStdString(query);
|
|
}
|
|
|
|
std::vector<sqlDatas> rowData;
|
|
sqlDataStruct Respone;
|
|
rc = sqlite3_exec(db, query.c_str(), callback, &rowData, 0);
|
|
if (rc != SQLITE_OK) {
|
|
std::cerr << "Failed to execute query: " << sqlite3_errmsg(db) << std::endl;
|
|
}
|
|
else
|
|
{
|
|
Respone.SN = SN;
|
|
Respone.dataVec.swap(rowData);
|
|
Respone.tableName = getCurrentTableName();
|
|
}
|
|
return Respone;
|
|
}
|
|
#endif
|
|
sqlDataStruct sqlController::selectData(std::string SN, std::string TableName)
|
|
{
|
|
std::string query;
|
|
int rc;
|
|
//send设true
|
|
|
|
//////////////////////////////
|
|
query = "SELECT * FROM \'" + TableName + "\' WHERE sn =\'" + SN + "\' ; ";
|
|
|
|
if (debugMode == true)
|
|
{
|
|
std::cout << query << endl;
|
|
qDebug() << "sql query:" << QString::fromStdString(query);
|
|
}
|
|
|
|
std::vector<sqlDatas> rowData;
|
|
sqlDataStruct Respone;
|
|
rc = sqlite3_exec(db, query.c_str(), callback, &rowData, 0);
|
|
if (rc != SQLITE_OK) {
|
|
std::cerr << "Failed to execute query: " << sqlite3_errmsg(db) << std::endl;
|
|
}
|
|
else
|
|
{
|
|
Respone.SN = SN;
|
|
Respone.dataVec.swap(rowData);
|
|
Respone.tableName = TableName;
|
|
}
|
|
return Respone;
|
|
}
|
|
void sqlController::uninitlize()
|
|
{
|
|
sqlite3_close(db);
|
|
remove(dataBasePath.c_str());
|
|
}
|
|
|
|
// 回调函数，用于从sqlite_master表中提取表的名称
|
|
int TableCallback(void* data, int argc, char** argv, char** colName) {
|
|
std::vector<std::string>* tableList = static_cast<std::vector<std::string>*>(data);
|
|
for (int i = 0; i < argc; i++) {
|
|
if (std::string(colName[i]) == "name") {
|
|
tableList->push_back(argv[i]);
|
|
}
|
|
}
|
|
return 0;
|
|
}
|
|
|
|
// 函数来查询数据库中的所有表
|
|
std::vector<std::string> sqlController::GetTables() {
|
|
std::vector<std::string> tableList;
|
|
char* errorMsg = nullptr;
|
|
|
|
// 查询sqlite_master表来获取表的信息
|
|
const char* query = "SELECT name FROM sqlite_master WHERE type='table';";
|
|
int rc = sqlite3_exec(db, query, TableCallback, &tableList, &errorMsg);
|
|
|
|
if (rc != SQLITE_OK) {
|
|
std::cerr << "SQL error: " << errorMsg << std::endl;
|
|
sqlite3_free(errorMsg);
|
|
}
|
|
|
|
return tableList;
|
|
}
|
|
|
|
// 回调函数，用于处理查询结果
|
|
int DataCallback(void* data, int argc, char** argv, char** colName) {
|
|
std::vector<sqlDataStruct>* result = static_cast<std::vector<sqlDataStruct>*>(data);
|
|
|
|
sqlDataStruct dataEntry;
|
|
dataEntry.SN = argv[0]; // SN码
|
|
// dataEntry.SN = argv[1]; // SN码
|
|
|
|
for (int i = 2; i < argc; ++i) {
|
|
sqlDatas dataItem;
|
|
dataItem.dataName = colName[i];
|
|
if (argv[i] != nullptr) {
|
|
// 解析数据，根据需要将数据转换为int、double或string
|
|
//try {
|
|
// dataItem.valueInt = std::stoi(argv[i]);
|
|
//}
|
|
//catch (const std::invalid_argument&) {
|
|
// try {
|
|
// dataItem.valueDouble = std::stod(argv[i]);
|
|
// }
|
|
// catch (const std::invalid_argument&) {
|
|
// dataItem.valueText = argv[i];
|
|
// }
|
|
//}
|
|
dataItem.valueText = argv[i];
|
|
}
|
|
dataEntry.dataVec.push_back(dataItem);
|
|
}
|
|
|
|
result->push_back(dataEntry);
|
|
return 0;
|
|
}
|
|
|
|
// 查询SQLite数据库中的数据
|
|
std::vector<sqlDataStruct> sqlController::getAllData(const std::string& tableName) {
|
|
std::vector<sqlDataStruct> result;
|
|
char* errorMsg = nullptr;
|
|
|
|
// 构建查询SQL语句
|
|
std::string query = "SELECT * FROM \"" + tableName+"\"";
|
|
|
|
// 执行查询
|
|
int rc = sqlite3_exec(db, query.c_str(), DataCallback, &result, &errorMsg);
|
|
|
|
if (rc != SQLITE_OK) {
|
|
std::cerr << "SQL error: " << errorMsg << std::endl;
|
|
sqlite3_free(errorMsg);
|
|
}
|
|
|
|
return result;
|
|
} |