from flask import Flask, jsonify, render_template, request import pandas as pd import re from datetime import datetime, timedelta app = Flask(__name__) # 加载 Excel 数据 - 学生年级数据 grade_files = { "22级": r"./数据表/22级.xlsx", "23级": r"./数据表/23级.xlsx", "24级": r"./数据表/24级.xlsx", } # 加载学生数据表 student_file_path = r"./数据表/区队-学号-姓名-1.xlsx" student_data = pd.read_excel(student_file_path) # 加载 Excel 数据 - 教师课程数据 teacher_files = { "2024-2025学年第一学期": r"./数据表/教学安排表20241125112641.xlsx", } # 预加载所有年级数据 grade_dataframes = {} for grade, file_path in grade_files.items(): df = pd.read_excel(file_path) df["grade"] = grade # 添加年级字段 grade_dataframes[grade] = df grade_data = pd.concat(grade_dataframes.values(), ignore_index=True) # 预加载教师课程数据 teacher_dataframes = {} for semester, file_path in teacher_files.items(): df = pd.read_excel(file_path) df["学年学期"] = semester # 添加学年学期字段 teacher_dataframes[semester] = df teacher_data = pd.concat(teacher_dataframes.values(), ignore_index=True) # 第一周的开始日期 first_week_start_date = datetime(2024, 9, 2) # 第一周星期一的日期 def parse_weeks(weeks_str): if not weeks_str or pd.isna(weeks_str): return set() weeks = set() for part in weeks_str.split(","): try: if "-" in part: start, end = map(int, part.split("-")) weeks.update(range(start, end + 1)) else: weeks.add(int(part)) except ValueError: print(f"跳过无效周次: {part}") continue return weeks # print(parse_weeks("1-2,4,7-9")) # 星期与节次解析函数 def parse_day_and_period(period_str): if not period_str or pd.isna(period_str): return None try: day_match = re.search(r"[一二三四五六日]", period_str) period_match = re.search(r"\[(\d+)-(\d+)节\]", period_str) if day_match and period_match: day = "一二三四五六日".index(day_match.group()) + 1 start, end = map(int, period_match.groups()) periods = list(range(start, end + 1)) return day, periods except Exception as e: print(f"解析失败: {period_str}, 错误: {e}") return None # 根据周次和星期计算实际日期 def calculate_date(week, day): days_from_start = (week - 1) * 7 + (day - 1) # 从第一周开始的天数差 return first_week_start_date + timedelta(days=days_from_start) @app.route("/") def index(): return render_template("index.html") @app.route("/teachers") def teacher_page(): return render_template("teacher.html") # 学生课程相关 API @app.route("/api/student_courses") def get_student_courses(): week = request.args.get("week", 1) grade = request.args.get("grade", None) admin_class = request.args.get("admin_class", None) # 筛选数据 filtered_data = grade_data if grade: filtered_data = filtered_data[filtered_data["grade"] == grade] if admin_class: filtered_data = filtered_data[filtered_data["行政班级"].str.contains(admin_class, na=False)] # 筛选课程类型：只要必修课 filtered_data = filtered_data[filtered_data["课程类别"].str.contains("必修课", na=False)] if week: week = int(week) filtered_data = filtered_data[ filtered_data["周次"].apply(lambda x: week in parse_weeks(x) if pd.notna(x) else False) ] # 解析课程信息 results = [] for _, row in filtered_data.iterrows(): day_and_period = parse_day_and_period(row["节次"]) if day_and_period: day, periods = day_and_period course_date = calculate_date(week, day) # 计算课程日期 results.append({ "课程": row["课程"].split("]")[1] if "]" in row["课程"] else row["课程"], "教师": row["教师"], "地点": row["地点"].split("（")[0] if "（" in row["地点"] else row["地点"], "星期": day, "日期": course_date.strftime("%Y-%m-%d"), # 格式化为字符串 "节次": periods, "节次范围": f"第{periods[0]}-{periods[-1]}节", "周次": row["周次"] }) # 按星期和节次排序 results = sorted(results, key=lambda x: (x["星期"], x["节次"][0])) return jsonify(results) @app.route("/api/classes") def get_classes(): classes = grade_data["行政班级"].dropna().unique().tolist() return jsonify(sorted(classes)) # 教师课程相关 API @app.route("/api/teachers") def get_teachers(): teachers = teacher_data["教师"].dropna().unique().tolist() return jsonify(sorted(teachers)) @app.route("/api/teacher_courses") def get_courses_by_teacher(): week = request.args.get("week", 1) teacher = request.args.get("teacher", None) # 筛选数据 filtered_data = teacher_data if teacher: filtered_data = filtered_data[filtered_data["教师"] == teacher] if week: week = int(week) filtered_data = filtered_data[filtered_data["周次"].apply(lambda x: week in parse_weeks(x) if pd.notna(x) else False)] # 解析课程信息 results = [] for _, row in filtered_data.iterrows(): day_and_period = parse_day_and_period(row["节次"]) if day_and_period: day, periods = day_and_period course_date = calculate_date(week, day) # 计算课程日期 results.append({ "课程": row["课程"].split("]")[1] if "]" in row["课程"] else row["课程"], "教师": row["教师"], "地点": row["地点"].split("（")[0] if "（" in row["地点"] else row["地点"], "星期": day, "日期": course_date.strftime("%Y-%m-%d"), # 格式化为字符串 "节次": periods, "节次范围": f"第{periods[0]}-{periods[-1]}节", "周次": row["周次"] }) # 按星期和节次排序 results = sorted(results, key=lambda x: (x["星期"], x["节次"][0])) return jsonify(results) # 路由：学生查询页面 @app.route("/students") def student_page(): return render_template("student.html") @app.route("/api/students") def get_students(): students = student_data["姓名"].dropna().unique().tolist() return jsonify(sorted(students)) @app.route("/api/student_courses_v2") def get_student_courses_v2(): week = request.args.get("week", 1) student_name = request.args.get("student_name", "").strip() if not student_name: return jsonify({"error": "缺少学生姓名参数"}), 400 # 使用拷贝的数据，避免修改原始 student_data student_data_copy = student_data.copy() student_data_copy["区队"] = student_data_copy["区队"].str.extract(r"\](.*)$")[0].str.strip() # 查找匹配的学生信息 matching_students = student_data_copy[student_data_copy["姓名"].str.contains(student_name, na=False)] if matching_students.empty: return jsonify({"error": "未找到匹配的学生信息"}), 404 # 获取学生所在班级 admin_classes = matching_students["区队"].unique() # 筛选课程数据 filtered_data = grade_data[grade_data["行政班级"].isin(admin_classes)] # 按课程类型筛选 filtered_data = filtered_data[filtered_data["课程类别"].str.contains("必修课", na=False)] # 按周次筛选 if week: week = int(week) filtered_data = filtered_data[ filtered_data["周次"].apply(lambda x: week in parse_weeks(x) if pd.notna(x) else False) ] # 如果没有找到课程数据 if filtered_data.empty: return jsonify({"error": "未找到匹配的课程表"}), 404 # 解析课程信息 results = [] for _, row in filtered_data.iterrows(): day_and_period = parse_day_and_period(row["节次"]) if day_and_period: day, periods = day_and_period course_date = calculate_date(week, day) # 计算课程日期 results.append({ "课程": row["课程"].split("]")[1] if "]" in row["课程"] else row["课程"], "教师": row["教师"], "地点": row["地点"].split("（")[0] if "（" in row["地点"] else row["地点"], "星期": day, "日期": course_date.strftime("%Y-%m-%d"), # 格式化为字符串 "节次": periods, "节次范围": f"第{periods[0]}-{periods[-1]}节", "周次": row["周次"] }) # 按星期和节次排序 results = sorted(results, key=lambda x: (x["星期"], x["节次"][0])) return jsonify(results) if __name__ == "__main__": app.run(host="0.0.0.0", port=7860)