app.py

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)