From 773fee1581aebf2b6e8e592a4413da9089561d4a Mon Sep 17 00:00:00 2001 From: root Date: Mon, 25 May 2026 23:14:59 +0800 Subject: [PATCH] =?utf8?q?=E9=A6=96=E9=A1=B5=E5=8F=AF=E4=BB=A5=E6=AD=A3?= =?utf8?q?=E5=B8=B8=E6=98=BE=E7=A4=BA=E8=BF=9B=E5=BA=A6?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- backend/db/update_db.sql | 43 ++++++++++++++++++ backend/main.py | 77 ++++++++++++++++++++------------ frontend/src/components/Home.css | 12 ++--- 3 files changed, 99 insertions(+), 33 deletions(-) create mode 100644 backend/db/update_db.sql diff --git a/backend/db/update_db.sql b/backend/db/update_db.sql new file mode 100644 index 0000000..e24b32e --- /dev/null +++ b/backend/db/update_db.sql @@ -0,0 +1,43 @@ +-- ========================================== +-- 1. 创建学习进度与统计日志表 (study_logs) +-- ========================================== +CREATE TABLE IF NOT EXISTS study_logs ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + group_id INTEGER NOT NULL, -- 组号 + op_type TEXT NOT NULL, -- 操作类型: 'study', 'test', 'review1', 'review2', 'review3' + status TEXT NOT NULL DEFAULT 'completed', -- 状态: 'completed', 'started' + duration_sec INTEGER DEFAULT 0, -- 执行耗时 (秒) + word_count INTEGER DEFAULT 0, -- 本组单词总数 + first_correct INTEGER DEFAULT 0, -- 第一次尝试正确个数 + first_wrong INTEGER DEFAULT 0, -- 第一次尝试错误个数 + total_rounds INTEGER DEFAULT 1, -- 通过本组所用的执行轮数 + log_date DATE, -- 业务完成日期 (YYYY-MM-DD) + created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 记录入库时间 + remark TEXT, -- 备注 + UNIQUE(group_id, op_type, log_date) -- 唯一性约束 +); + +CREATE INDEX IF NOT EXISTS idx_progress_lookup ON study_logs (op_type, group_id); + +-- ========================================== +-- 2. 将结构信息补充到 schema_comments +-- ========================================== + +-- 插入表描述 +INSERT OR REPLACE INTO schema_comments (object_type, table_name, column_name, description) +VALUES ('table', 'study_logs', NULL, '存储单词学习、测试和复习的进度及效率统计日志'); + +-- 插入列描述 +INSERT OR REPLACE INTO schema_comments (object_type, table_name, column_name, description) VALUES +('column', 'study_logs', 'id', '自增主键'), +('column', 'study_logs', 'group_id', '单词分组编号'), +('column', 'study_logs', 'op_type', '操作模式类型: study(学习), test(测试), review1/2/3(复习轮次)'), +('column', 'study_logs', 'status', '任务执行状态: started(开始), completed(已完成)'), +('column', 'study_logs', 'duration_sec', '完成该任务所花费的总时长（秒）'), +('column', 'study_logs', 'word_count', '该分组包含的单词总数'), +('column', 'study_logs', 'first_correct', '在第一轮测试/复习中直接答对的单词数量'), +('column', 'study_logs', 'first_wrong', '在第一轮测试/复习中答错的单词数量'), +('column', 'study_logs', 'total_rounds', '为了达到全对通过，该组测试重复执行的总轮数'), +('column', 'study_logs', 'log_date', '学习任务归属的业务日期'), +('column', 'study_logs', 'created_at', '数据库记录创建的系统时间'), +('column', 'study_logs', 'remark', '额外备注信息或原始日志文本'); diff --git a/backend/main.py b/backend/main.py index 80eb8be..ce7dab3 100644 --- a/backend/main.py +++ b/backend/main.py @@ -2,7 +2,6 @@ from fastapi import FastAPI, HTTPException from fastapi.middleware.cors import CORSMiddleware import sqlite3 import os -import re import datetime from pydantic import BaseModel from typing import List, Optional @@ -18,11 +17,13 @@ app.add_middleware( allow_headers=["*"], ) -# --- 文件路径配置 --- -# 确保路径与你的服务器目录结构一致 +# --- 路径自动修复逻辑 --- BASE_DIR = os.path.dirname(os.path.abspath(__file__)) -DATABASE_PATH = os.path.join(BASE_DIR, "db", "bcd.db") -LOG_FILE_PATH = os.path.join(BASE_DIR, "a", "log.txt") +# 优先检查 /home/words/backend/a/bcd.db +DATABASE_PATH = os.path.join(BASE_DIR, "a", "bcd.db") +if not os.path.exists(DATABASE_PATH): + # 备选检查 /home/words/backend/db/bcd.db + DATABASE_PATH = os.path.join(BASE_DIR, "db", "bcd.db") # --- 数据模型 --- class HomeData(BaseModel): @@ -37,32 +38,55 @@ class ProgressData(BaseModel): review2: str review3: str -# --- 辅助逻辑函数（移植自原 MainApp） --- +# --- 辅助逻辑函数 --- def calculate_hours_since(date_str: str) -> Optional[float]: if not date_str: return None try: - # 兼容原格式 "2023-05-24 10:00:00" - last = datetime.datetime.strptime(date_str[:19], "%Y-%m-%d %H:%M:%S") + # 兼容数据库中的 YYYY-MM-DD HH:MM:SS 或 YYYY-MM-DD + date_part = date_str[:19] + fmt = "%Y-%m-%d %H:%M:%S" if " " in date_part else "%Y-%m-%d" + last = datetime.datetime.strptime(date_part, fmt) return (datetime.datetime.now() - last).total_seconds() / 3600 except: return None -def get_progress_from_log(): - """解析 log.txt 获取每个组的完成时间""" +def get_progress_from_db(): + """解析数据库 study_logs 表获取每个组的完成时间""" progress = {} - if os.path.exists(LOG_FILE_PATH): - with open(LOG_FILE_PATH, 'r', encoding='utf-8') as f: - for line in f: - # 匹配原有的日志格式 - match = re.search(r'PROGRESS_UPDATE: Group:(.+?), Type:(.+?), Status:completed, Date:(.+)', line) - if match: - g_n, a_t, c_d = match.groups() - g_n = g_n.strip() - if g_n not in progress: - progress[g_n] = {'study': None, 'test': None, 'review1': None, 'review2': None, 'review3': None} - progress[g_n][a_t.strip()] = c_d.strip() + try: + if not os.path.exists(DATABASE_PATH): + return progress + + conn = sqlite3.connect(DATABASE_PATH) + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + + # 查询每个组每种类型的最后完成时间 + query = """ + SELECT group_id, op_type, MAX(created_at) as last_date + FROM study_logs + WHERE status = 'completed' + GROUP BY group_id, op_type + """ + cursor.execute(query) + rows = cursor.fetchall() + + for row in rows: + g_n = str(row['group_id']) + a_t = row['op_type'] + c_d = row['last_date'] + + if g_n not in progress: + progress[g_n] = {'study': None, 'test': None, 'review1': None, 'review2': None, 'review3': None} + + if a_t in progress[g_n]: + progress[g_n][a_t] = c_d + + conn.close() + except Exception as e: + print(f"Database error in get_progress_from_db: {e}") return progress # --- 路由实现 --- @@ -84,17 +108,17 @@ async def get_home_data(): @app.get("/api/home/process", response_model=List[ProgressData]) async def get_home_process(): try: - # 1. 从数据库获取所有分组 + # 1. 获取所有分组 conn = sqlite3.connect(DATABASE_PATH) cursor = conn.cursor() cursor.execute("SELECT DISTINCT groupid FROM words ORDER BY CAST(groupid AS INTEGER) ASC") all_groups = [str(row[0]) for row in cursor.fetchall()] conn.close() - # 2. 从日志解析进度 - log_progress = get_progress_from_log() + # 2. 从数据库读取进度 + log_progress = get_progress_from_db() - # 3. 模拟原有的 UI 显示逻辑 + # 3. 业务逻辑处理 (完全保留你要求的 UI 逻辑) results = [] first_unstudied = None for g in all_groups: @@ -104,8 +128,6 @@ async def get_home_process(): for g_name in all_groups: p = log_progress.get(g_name, {'study': None, 'test': None, 'review1': None, 'review2': None, 'review3': None}) - - # 构建每一行数据 row = {"group": f"Group {g_name}"} # 基础学习列 @@ -134,7 +156,6 @@ async def get_home_process(): row[key] = f"✔️ {p[key][:10]}" elif p[prev_key]: hours = calculate_hours_since(p[prev_key]) - # 原逻辑：超过4小时显示“尽快复习”，否则显示“开始复习” if hours and hours > 4: row[key] = "⚠️请尽快复习" else: diff --git a/frontend/src/components/Home.css b/frontend/src/components/Home.css index 1c3c909..92770cc 100644 --- a/frontend/src/components/Home.css +++ b/frontend/src/components/Home.css @@ -114,11 +114,13 @@ text-align: center; } - /* 四列等宽均分 */ - th:nth-child(1), td:nth-child(1) { width: 25%; font-weight: 600; color: #1d4ed8; } - th:nth-child(2), td:nth-child(2) { width: 25%; } - th:nth-child(3), td:nth-child(3) { width: 25%; } - th:nth-child(4), td:nth-child(4) { width: 25%; color: #64748b; font-size: 13px; } + /* 修改为六列均分（100 / 6 ≈ 16.6%） */ + th:nth-child(1), td:nth-child(1) { width: 16.6%; font-weight: 600; color: #1d4ed8; } + th:nth-child(2), td:nth-child(2) { width: 16.6%; } + th:nth-child(3), td:nth-child(3) { width: 16.6%; } + th:nth-child(4), td:nth-child(4) { width: 16.6%; } + th:nth-child(5), td:nth-child(5) { width: 16.6%; } + th:nth-child(6), td:nth-child(6) { width: 16.6%; color: #64748b; font-size: 13px; } /* 最后一行无边框 */ tr:last-child td { -- 2.43.0