From c62dbec7328a8b44e6ec61758e7b8463f2e502dd Mon Sep 17 00:00:00 2001
From: LAPTOP-SNT8I5JK\Boounion <Chenluhua@qq.com>
Date: 星期五, 12 九月 2025 11:58:15 +0800
Subject: [PATCH] Merge branch 'liuyang'
---
SourceCode/Bond/Servo/GlassLogDb.cpp | 526 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 526 insertions(+), 0 deletions(-)
diff --git a/SourceCode/Bond/Servo/GlassLogDb.cpp b/SourceCode/Bond/Servo/GlassLogDb.cpp
new file mode 100644
index 0000000..b9f0df5
--- /dev/null
+++ b/SourceCode/Bond/Servo/GlassLogDb.cpp
@@ -0,0 +1,526 @@
+// GlassLogDb.cpp - 单例封装：SQLite 写入/查询/分页/统计/CSV 导出（已对接 SERVO::CGlass）
+#include "stdafx.h"
+#include "GlassLogDb.h"
+#include "sqlite3.h"
+#include <stdexcept>
+#include <sstream>
+#include <iomanip>
+#include <ctime>
+#include <fstream>
+#include "GlassJson.h"
+
+
+using namespace SERVO;
+
+#ifndef GLASS_LOG_TABLE
+#define GLASS_LOG_TABLE "glass_log"
+#endif
+
+// ================== 工具函数 ==================
+static void throwIf(int rc, sqlite3* db, const char* msg) {
+ if (rc != SQLITE_OK && rc != SQLITE_DONE && rc != SQLITE_ROW) {
+ std::ostringstream oss;
+ oss << msg << " (rc=" << rc << "): " << (db ? sqlite3_errmsg(db) : "null db");
+ throw std::runtime_error(oss.str());
+ }
+}
+static inline const char* safe_text(sqlite3_stmt* s, int col) {
+ const unsigned char* p = sqlite3_column_text(s, col);
+ return p ? reinterpret_cast<const char*>(p) : "";
+}
+static std::string csvEscape(const std::string& s) {
+ bool needQuote = s.find_first_of(",\"\n\r") != std::string::npos;
+ if (!needQuote) return s;
+ std::string out; out.reserve(s.size() + 2);
+ out.push_back('"');
+ for (char c : s) out += (c == '"') ? "\"\"" : std::string(1, c);
+ out.push_back('"');
+ return out;
+}
+static std::string toIso8601String(const std::optional<std::chrono::system_clock::time_point>& tp) {
+ if (!tp.has_value()) return "";
+ using namespace std::chrono;
+ auto t = system_clock::to_time_t(*tp);
+ std::tm tm{};
+#if defined(_WIN32)
+ gmtime_s(&tm, &t);
+#else
+ gmtime_r(&t, &tm);
+#endif
+ std::ostringstream oss;
+ oss << std::put_time(&tm, "%Y-%m-%dT%H:%M:%SZ");
+ return oss.str();
+}
+
+// ================== 单例静态成员 ==================
+std::unique_ptr<GlassLogDb> GlassLogDb::s_inst;
+std::mutex GlassLogDb::s_instMtx;
+
+// ================== 单例接口实现 ==================
+void GlassLogDb::Init(const std::string& dbPath) {
+ std::lock_guard<std::mutex> g(s_instMtx);
+ if (!s_inst) {
+ s_inst.reset(new GlassLogDb(dbPath));
+ }
+ else if (s_inst->dbPath_ != dbPath) {
+ s_inst->reopenInternal(dbPath);
+ }
+}
+GlassLogDb& GlassLogDb::Instance() {
+ std::lock_guard<std::mutex> g(s_instMtx);
+ if (!s_inst) throw std::runtime_error("GlassLogDb::Instance() called before Init()");
+ return *s_inst;
+}
+bool GlassLogDb::IsInitialized() noexcept {
+ std::lock_guard<std::mutex> g(s_instMtx);
+ return static_cast<bool>(s_inst);
+}
+void GlassLogDb::Reopen(const std::string& dbPath) {
+ std::lock_guard<std::mutex> g(s_instMtx);
+ if (!s_inst) {
+ s_inst.reset(new GlassLogDb(dbPath));
+ }
+ else {
+ s_inst->reopenInternal(dbPath);
+ }
+}
+std::string GlassLogDb::CurrentPath() {
+ std::lock_guard<std::mutex> g(s_instMtx);
+ return s_inst ? s_inst->dbPath_ : std::string();
+}
+
+// ================== 构造/打开/关闭/重开 ==================
+GlassLogDb::GlassLogDb(const std::string& dbPath) {
+ openDb(dbPath);
+}
+void GlassLogDb::openDb(const std::string& dbPath) {
+ int rc = sqlite3_open_v2(dbPath.c_str(), &db_,
+ SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX,
+ nullptr);
+ if (rc != SQLITE_OK) {
+ std::string err = db_ ? sqlite3_errmsg(db_) : "open failed";
+ if (db_) sqlite3_close(db_);
+ db_ = nullptr;
+ throw std::runtime_error("Failed to open sqlite DB: " + err + " | path=" + dbPath);
+ }
+ dbPath_ = dbPath;
+ ensureSchema();
+ prepareStatements();
+}
+void GlassLogDb::closeDb() noexcept {
+ finalizeStatements();
+ if (db_) {
+ sqlite3_close(db_);
+ db_ = nullptr;
+ }
+}
+void GlassLogDb::reopenInternal(const std::string& dbPath) {
+ std::lock_guard<std::mutex> lk(mtx_);
+ if (dbPath == dbPath_) return;
+ closeDb();
+ openDb(dbPath);
+}
+
+// ================== 析构 ==================
+GlassLogDb::~GlassLogDb() {
+ closeDb();
+}
+
+// ================== DDL & 预编译 ==================
+void GlassLogDb::ensureSchema() {
+ const char* ddl =
+ "CREATE TABLE IF NOT EXISTS " GLASS_LOG_TABLE " ("
+ " id INTEGER PRIMARY KEY AUTOINCREMENT,"
+ " cassette_seq_no INTEGER,"
+ " job_seq_no INTEGER,"
+ " class_id TEXT,"
+ " material_type INTEGER,"
+ " state INTEGER,"
+ " t_start TEXT," // ISO8601 UTC（可为 NULL）
+ " t_end TEXT," // ISO8601 UTC（可为 NULL）
+ " buddy_id TEXT,"
+ " aoi_result INTEGER,"
+ " path TEXT,"
+ " params TEXT,"
+ " pretty TEXT"
+ ");"
+ "CREATE INDEX IF NOT EXISTS idx_glass_key "
+ " ON " GLASS_LOG_TABLE " (cassette_seq_no, job_seq_no);"
+ "CREATE INDEX IF NOT EXISTS idx_class_id "
+ " ON " GLASS_LOG_TABLE " (class_id);"
+ "CREATE INDEX IF NOT EXISTS idx_t_start "
+ " ON " GLASS_LOG_TABLE " (t_start);";
+
+ char* errMsg = nullptr;
+ int rc = sqlite3_exec(db_, ddl, nullptr, nullptr, &errMsg);
+ if (rc != SQLITE_OK) {
+ std::string err = errMsg ? errMsg : "unknown";
+ sqlite3_free(errMsg);
+ throw std::runtime_error("Failed to create schema: " + err);
+ }
+}
+void GlassLogDb::prepareStatements() {
+ const char* sql =
+ "INSERT INTO " GLASS_LOG_TABLE " ("
+ " cassette_seq_no, job_seq_no, class_id, material_type, state,"
+ " t_start, t_end, buddy_id, aoi_result, path, params, pretty)"
+ " VALUES (?,?,?,?,?,?,?,?,?,?,?,?);";
+ int rc = sqlite3_prepare_v2(db_, sql, -1, &stmtInsert_, nullptr);
+ throwIf(rc, db_, "prepare insert");
+}
+void GlassLogDb::finalizeStatements() noexcept {
+ if (stmtInsert_) {
+ sqlite3_finalize(stmtInsert_);
+ stmtInsert_ = nullptr;
+ }
+}
+
+// ================== 事务 ==================
+void GlassLogDb::beginTransaction() {
+ std::lock_guard<std::mutex> lk(mtx_);
+ char* err = nullptr;
+ int rc = sqlite3_exec(db_, "BEGIN IMMEDIATE;", nullptr, nullptr, &err);
+ if (rc != SQLITE_OK) {
+ std::string e = err ? err : "unknown";
+ sqlite3_free(err);
+ throw std::runtime_error("BEGIN failed: " + e);
+ }
+}
+void GlassLogDb::commit() {
+ std::lock_guard<std::mutex> lk(mtx_);
+ char* err = nullptr;
+ int rc = sqlite3_exec(db_, "COMMIT;", nullptr, nullptr, &err);
+ if (rc != SQLITE_OK) {
+ std::string e = err ? err : "unknown";
+ sqlite3_free(err);
+ throw std::runtime_error("COMMIT failed: " + e);
+ }
+}
+void GlassLogDb::rollback() {
+ std::lock_guard<std::mutex> lk(mtx_);
+ char* err = nullptr;
+ sqlite3_exec(db_, "ROLLBACK;", nullptr, nullptr, &err);
+ if (err) sqlite3_free(err);
+}
+
+// ================== 时间格式 ==================
+std::string GlassLogDb::toIso8601Utc(std::chrono::system_clock::time_point tp) {
+ using namespace std::chrono;
+ auto t = system_clock::to_time_t(tp);
+ std::tm tm{};
+#if defined(_WIN32)
+ gmtime_s(&tm, &t);
+#else
+ gmtime_r(&t, &tm);
+#endif
+ std::ostringstream oss;
+ oss << std::put_time(&tm, "%Y-%m-%dT%H:%M:%SZ");
+ return oss.str();
+}
+
+// ================== 插入实现 ==================
+long long GlassLogDb::doInsert(
+ int cassetteSeqNo,
+ int jobSeqNo,
+ const std::string& classId,
+ int materialType,
+ int state,
+ std::optional<std::chrono::system_clock::time_point> tStart,
+ std::optional<std::chrono::system_clock::time_point> tEnd,
+ const std::string& buddyId,
+ int aoiResult,
+ const std::string& pathDesc,
+ const std::string& paramsDesc,
+ const std::string& prettyString)
+{
+ std::lock_guard<std::mutex> lk(mtx_);
+ int idx = 1;
+ throwIf(sqlite3_reset(stmtInsert_), db_, "reset insert");
+ throwIf(sqlite3_clear_bindings(stmtInsert_), db_, "clear bindings");
+
+ throwIf(sqlite3_bind_int(stmtInsert_, idx++, cassetteSeqNo), db_, "bind cassette");
+ throwIf(sqlite3_bind_int(stmtInsert_, idx++, jobSeqNo), db_, "bind job");
+ throwIf(sqlite3_bind_text(stmtInsert_, idx++, classId.c_str(), -1, SQLITE_TRANSIENT), db_, "bind class_id");
+ throwIf(sqlite3_bind_int(stmtInsert_, idx++, materialType), db_, "bind material_type");
+ throwIf(sqlite3_bind_int(stmtInsert_, idx++, state), db_, "bind state");
+
+ if (tStart.has_value()) {
+ auto s = toIso8601Utc(*tStart);
+ throwIf(sqlite3_bind_text(stmtInsert_, idx++, s.c_str(), -1, SQLITE_TRANSIENT), db_, "bind t_start");
+ }
+ else {
+ throwIf(sqlite3_bind_null(stmtInsert_, idx++), db_, "bind t_start null");
+ }
+ if (tEnd.has_value()) {
+ auto e = toIso8601Utc(*tEnd);
+ throwIf(sqlite3_bind_text(stmtInsert_, idx++, e.c_str(), -1, SQLITE_TRANSIENT), db_, "bind t_end");
+ }
+ else {
+ throwIf(sqlite3_bind_null(stmtInsert_, idx++), db_, "bind t_end null");
+ }
+
+ throwIf(sqlite3_bind_text(stmtInsert_, idx++, buddyId.c_str(), -1, SQLITE_TRANSIENT), db_, "bind buddy_id");
+ throwIf(sqlite3_bind_int(stmtInsert_, idx++, aoiResult), db_, "bind aoi_result");
+ throwIf(sqlite3_bind_text(stmtInsert_, idx++, pathDesc.c_str(), -1, SQLITE_TRANSIENT), db_, "bind path");
+ throwIf(sqlite3_bind_text(stmtInsert_, idx++, paramsDesc.c_str(), -1, SQLITE_TRANSIENT), db_, "bind params");
+ throwIf(sqlite3_bind_text(stmtInsert_, idx++, prettyString.c_str(), -1, SQLITE_TRANSIENT), db_, "bind pretty");
+
+ int rc = sqlite3_step(stmtInsert_);
+ throwIf(rc, db_, "insert step");
+ return sqlite3_last_insert_rowid(db_);
+}
+
+long long GlassLogDb::insertFromCGlass(CGlass& g)
+{
+ const int cassette = static_cast<int>(g.getCassetteSequenceNo());
+ const int job = static_cast<int>(g.getJobSequenceNo());
+ const std::string& classId = g.getID();
+ const int material = static_cast<int>(g.getType());
+ const int state = static_cast<int>(g.state());
+ const std::string& buddy = g.getBuddyId();
+ const int aoiResult = static_cast<int>(g.getAOIInspResult());
+ const std::string pathDesc = g.getPathDescription();
+ const std::string paramsDesc = g.getParamsDescription();
+ const std::string pretty = GlassJson::ToPrettyString(g);
+
+ auto tStart = g.tStart();
+ auto tEnd = g.tEnd();
+
+ return doInsert(cassette, job, classId, material, state,
+ tStart, tEnd, buddy, aoiResult, pathDesc, paramsDesc, pretty);
+}
+
+long long GlassLogDb::insertExplicit(
+ int cassetteSeqNo,
+ int jobSeqNo,
+ const std::string& classId,
+ int materialType,
+ int state,
+ std::optional<std::chrono::system_clock::time_point> tStart,
+ std::optional<std::chrono::system_clock::time_point> tEnd,
+ const std::string& buddyId,
+ int aoiResult,
+ const std::string& pathDesc,
+ const std::string& paramsDesc,
+ const std::string& prettyString)
+{
+ return doInsert(cassetteSeqNo, jobSeqNo, classId, materialType, state,
+ tStart, tEnd, buddyId, aoiResult, pathDesc, paramsDesc, prettyString);
+}
+
+// ================== 过滤构造（where + bind） ==================
+static std::string buildWhereSql(const GlassLogDb::Filters& f) {
+ std::ostringstream where;
+ bool first = true;
+ auto add = [&](const std::string& cond) {
+ if (first) { where << " WHERE " << cond; first = false; }
+ else { where << " AND " << cond; }
+ };
+ if (f.classId) add("class_id = ?");
+ if (f.cassetteSeqNo) add("cassette_seq_no = ?");
+ if (f.jobSeqNo) add("job_seq_no = ?");
+ if (f.keyword) add("(class_id LIKE ? OR buddy_id LIKE ? OR path LIKE ? OR params LIKE ? OR pretty LIKE ?)");
+ if (f.tStartFrom && f.tStartTo) add("(t_start >= ? AND t_start <= ?)");
+ else if (f.tStartFrom) add("t_start >= ?");
+ else if (f.tStartTo) add("t_start <= ?");
+ return where.str();
+}
+static void bindFilters(sqlite3_stmt* stmt, sqlite3* db, int& idx, const GlassLogDb::Filters& f) {
+ if (f.classId) throwIf(sqlite3_bind_text(stmt, idx++, f.classId->c_str(), -1, SQLITE_TRANSIENT), db, "bind classId");
+ if (f.cassetteSeqNo) throwIf(sqlite3_bind_int(stmt, idx++, *f.cassetteSeqNo), db, "bind cassette");
+ if (f.jobSeqNo) throwIf(sqlite3_bind_int(stmt, idx++, *f.jobSeqNo), db, "bind job");
+ if (f.keyword) {
+ std::string kw = "%" + *f.keyword + "%";
+ for (int i = 0; i < 5; ++i)
+ throwIf(sqlite3_bind_text(stmt, idx++, kw.c_str(), -1, SQLITE_TRANSIENT), db, "bind keyword");
+ }
+ if (f.tStartFrom && f.tStartTo) {
+ std::string s = toIso8601String(f.tStartFrom);
+ std::string e = toIso8601String(f.tStartTo);
+ throwIf(sqlite3_bind_text(stmt, idx++, s.c_str(), -1, SQLITE_TRANSIENT), db, "bind tStartFrom");
+ throwIf(sqlite3_bind_text(stmt, idx++, e.c_str(), -1, SQLITE_TRANSIENT), db, "bind tStartTo");
+ }
+ else if (f.tStartFrom) {
+ std::string s = toIso8601String(f.tStartFrom);
+ throwIf(sqlite3_bind_text(stmt, idx++, s.c_str(), -1, SQLITE_TRANSIENT), db, "bind tStartFrom");
+ }
+ else if (f.tStartTo) {
+ std::string e = toIso8601String(f.tStartTo);
+ throwIf(sqlite3_bind_text(stmt, idx++, e.c_str(), -1, SQLITE_TRANSIENT), db, "bind tStartTo");
+ }
+}
+
+// ================== 查询 / 统计 / 分页 ==================
+std::vector<GlassLogDb::Row> GlassLogDb::query(
+ const Filters& filters,
+ int limit,
+ int offset)
+{
+ std::lock_guard<std::mutex> lk(mtx_);
+
+ std::ostringstream sql;
+ sql << "SELECT id, cassette_seq_no, job_seq_no, class_id, material_type, state,"
+ " IFNULL(strftime('%Y-%m-%d %H:%M:%S', t_start, 'localtime'), ''),"
+ " IFNULL(strftime('%Y-%m-%d %H:%M:%S', t_end, 'localtime'), ''),"
+ " buddy_id, aoi_result, path, params, pretty"
+ " FROM " GLASS_LOG_TABLE;
+
+ std::string where = buildWhereSql(filters);
+ sql << where
+ << " ORDER BY id DESC"
+ << " LIMIT " << (limit < 0 ? 0 : limit)
+ << " OFFSET " << (offset < 0 ? 0 : offset);
+
+ sqlite3_stmt* stmt = nullptr;
+ throwIf(sqlite3_prepare_v2(db_, sql.str().c_str(), -1, &stmt, nullptr), db_, "prepare query");
+
+ int idx = 1;
+ bindFilters(stmt, db_, idx, filters);
+
+ std::vector<Row> rows;
+ for (;;) {
+ int rc = sqlite3_step(stmt);
+ if (rc == SQLITE_ROW) {
+ Row r;
+ r.id = sqlite3_column_int64(stmt, 0);
+ r.cassetteSeqNo = sqlite3_column_int(stmt, 1);
+ r.jobSeqNo = sqlite3_column_int(stmt, 2);
+ r.classId = safe_text(stmt, 3);
+ r.materialType = sqlite3_column_int(stmt, 4);
+ r.state = sqlite3_column_int(stmt, 5);
+ r.tStart = safe_text(stmt, 6);
+ r.tEnd = safe_text(stmt, 7);
+ r.buddyId = safe_text(stmt, 8);
+ r.aoiResult = sqlite3_column_int(stmt, 9);
+ r.path = safe_text(stmt, 10);
+ r.params = safe_text(stmt, 11);
+ r.pretty = safe_text(stmt, 12);
+ rows.push_back(std::move(r));
+ }
+ else if (rc == SQLITE_DONE) {
+ break;
+ }
+ else {
+ sqlite3_finalize(stmt);
+ throwIf(rc, db_, "query step");
+ }
+ }
+ sqlite3_finalize(stmt);
+ return rows;
+}
+
+long long GlassLogDb::count(const Filters& filters) {
+ std::lock_guard<std::mutex> lk(mtx_);
+
+ std::ostringstream sql;
+ sql << "SELECT COUNT(1) FROM " GLASS_LOG_TABLE;
+
+ std::string where = buildWhereSql(filters);
+ sql << where;
+
+ sqlite3_stmt* stmt = nullptr;
+ throwIf(sqlite3_prepare_v2(db_, sql.str().c_str(), -1, &stmt, nullptr), db_, "prepare count");
+
+ int idx = 1;
+ bindFilters(stmt, db_, idx, filters);
+
+ long long total = 0;
+ int rc = sqlite3_step(stmt);
+ if (rc == SQLITE_ROW) {
+ total = sqlite3_column_int64(stmt, 0);
+ }
+ else if (rc != SQLITE_DONE) {
+ sqlite3_finalize(stmt);
+ throwIf(rc, db_, "count step");
+ }
+ sqlite3_finalize(stmt);
+ return total;
+}
+
+GlassLogDb::Page GlassLogDb::queryPaged(
+ const Filters& filters,
+ int limit,
+ int offset)
+{
+ Page p;
+ p.limit = (limit < 0 ? 0 : limit);
+ p.offset = (offset < 0 ? 0 : offset);
+ p.total = count(filters);
+ p.items = query(filters, p.limit, p.offset);
+ return p;
+}
+
+// ================== 导出 CSV（全部符合条件） ==================
+long long GlassLogDb::exportCsv(const std::string& csvPath, const Filters& filters) {
+ std::lock_guard<std::mutex> lk(mtx_);
+
+ std::ofstream ofs(csvPath, std::ios::binary);
+ if (!ofs) {
+ throw std::runtime_error("Failed to open csv for write: " + csvPath);
+ }
+
+ // 表头
+ ofs << "id,cassette_seq_no,job_seq_no,class_id,material_type,state,t_start,t_end,"
+ "buddy_id,aoi_result,path,params,pretty\n";
+
+ std::ostringstream sql;
+ sql << "SELECT id, cassette_seq_no, job_seq_no, class_id, material_type, state,"
+ " IFNULL(strftime('%Y-%m-%d %H:%M:%S', t_start, 'localtime'), ''),"
+ " IFNULL(strftime('%Y-%m-%d %H:%M:%S', t_end, 'localtime'), ''),"
+ " buddy_id, aoi_result, path, params, pretty"
+ " FROM " GLASS_LOG_TABLE;
+
+ std::string where = buildWhereSql(filters);
+ sql << where << " ORDER BY id DESC";
+
+ sqlite3_stmt* stmt = nullptr;
+ throwIf(sqlite3_prepare_v2(db_, sql.str().c_str(), -1, &stmt, nullptr), db_, "prepare export");
+
+ int idx = 1;
+ bindFilters(stmt, db_, idx, filters);
+
+ long long rows = 0;
+ for (;;) {
+ int rc = sqlite3_step(stmt);
+ if (rc == SQLITE_ROW) {
+ std::string id = std::to_string(sqlite3_column_int64(stmt, 0));
+ std::string cassette = std::to_string(sqlite3_column_int(stmt, 1));
+ std::string job = std::to_string(sqlite3_column_int(stmt, 2));
+ std::string class_id = safe_text(stmt, 3);
+ std::string material = std::to_string(sqlite3_column_int(stmt, 4));
+ std::string state = std::to_string(sqlite3_column_int(stmt, 5));
+ std::string t_start = safe_text(stmt, 6);
+ std::string t_end = safe_text(stmt, 7);
+ std::string buddy = safe_text(stmt, 8);
+ std::string aoi = std::to_string(sqlite3_column_int(stmt, 9));
+ std::string path = safe_text(stmt, 10);
+ std::string params = safe_text(stmt, 11);
+ std::string pretty = safe_text(stmt, 12);
+
+ ofs << id << ','
+ << cassette << ','
+ << job << ','
+ << csvEscape(class_id) << ','
+ << material << ','
+ << state << ','
+ << t_start << ','
+ << t_end << ','
+ << csvEscape(buddy) << ','
+ << aoi << ','
+ << csvEscape(path) << ','
+ << csvEscape(params) << ','
+ << csvEscape(pretty) << '\n';
+ ++rows;
+ }
+ else if (rc == SQLITE_DONE) {
+ break;
+ }
+ else {
+ sqlite3_finalize(stmt);
+ throwIf(rc, db_, "export step");
+ }
+ }
+ sqlite3_finalize(stmt);
+ ofs.flush();
+ return rows;
+}
--
Gitblit v1.9.3