app.py

from flask import Flask, jsonify, render_template, request
import pandas as pd
import re
import json
from datetime import datetime, timedelta

app = Flask(__name__)

# 加载教室到校区的映射
try:
    with open('./数据表/classroom_to_campus_mapping.json', 'r', encoding='utf-8') as f:
        classroom_to_campus_mapping = json.load(f)
    print(f"成功加载教室到校区映射，共{len(classroom_to_campus_mapping)}个教室")
except Exception as e:
    print(f"加载教室到校区映射失败: {e}")
    classroom_to_campus_mapping = {}

# 加载学生过滤名单
try:
    with open('./数据表/student_filter_list.json', 'r', encoding='utf-8') as f:
        student_filter_data = json.load(f)
        allowed_students = set(student_filter_data.get('allowed_students', []))
    print(f"成功加载学生过滤名单，共{len(allowed_students)}个学生")
except Exception as e:
    print(f"加载学生过滤名单失败: {e}")
    allowed_students = set()

# 根据教室名称获取校区的函数
def get_campus_by_classroom(location):
    """
    根据教室名称获取校区
    """
    if not location:
        return "未知校区"
    
    # 移除括号内容，获取纯教室名称
    clean_location = re.sub(r'\([^)]*\)', '', location).strip()
    
    # 首先尝试直接匹配
    if location in classroom_to_campus_mapping:
        return classroom_to_campus_mapping[location]
    
    # 尝试匹配清理后的教室名称
    if clean_location in classroom_to_campus_mapping:
        return classroom_to_campus_mapping[clean_location]
    
    # 如果映射中没有找到，使用原有的逻辑作为备用
    if "仙葫" in location:
        return "仙葫校区"
    elif "五合" in location:
        return "五合校区"
    
    # 默认返回五合校区
    return "五合校区"

# 加载学生数据表
student_file_path = r"./数据表/区队-学号-姓名-1.xlsx"
try:
    student_data = pd.read_excel(student_file_path)
except Exception as e:
    print(f"加载学生数据失败: {e}")
    student_data = pd.DataFrame()

# 加载班级课表数据（包含所有年级的课程信息）
grade_file_path = r"./数据表/班级课表20250830202202.xls"
try:
    grade_data = pd.read_excel(grade_file_path)
    print(f"成功加载课程数据，共{len(grade_data)}条记录")
except Exception as e:
    print(f"加载课程数据失败: {e}")
    grade_data = pd.DataFrame()

# 加载 Excel 数据 - 教师课程数据
teacher_files = {
    "2024-2025学年第一学期": r"./数据表/教学安排表20250829113240.xls",
}

# 加载教室课表数据
classroom_files = {
    "五合校区": r"./数据表/全校课表(按教室) 五合校区.xls",
    "仙葫校区": r"./数据表/全校课表(按教室) 仙葫校区.xls",
}

# 预加载教师课程数据
teacher_dataframes = {}
for semester, file_path in teacher_files.items():
    try:
        df = pd.read_excel(file_path)
        df["学年学期"] = semester  # 添加学年学期字段
        teacher_dataframes[semester] = df
    except Exception as e:
        print(f"加载教师课程数据失败: {e}")
        continue

teacher_data = pd.concat(teacher_dataframes.values(), ignore_index=True) if teacher_dataframes else pd.DataFrame()

# 预加载教室课表数据
classroom_dataframes = {}
for campus, file_path in classroom_files.items():
    try:
        # 读取HTML格式的Excel文件
        df = pd.read_html(file_path, encoding='gbk')[0]  # 取第一个表格
        df["校区"] = campus  # 添加校区字段
        classroom_dataframes[campus] = df
        print(f"成功加载{campus}教室数据，共{len(df)}条记录")
        print(f"数据列名: {df.columns.tolist()}")
    except Exception as e:
        print(f"加载{campus}教室数据失败: {e}")
        continue

classroom_data = pd.concat(classroom_dataframes.values(), ignore_index=True) if classroom_dataframes else pd.DataFrame()
print(f"教室数据总计: {len(classroom_data)}条记录")

# 第一周的开始日期
first_week_start_date = datetime(2025, 9, 1)  # 第一周星期一的日期（2025年9月1日是周一）



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
        else:
            # 移除调试打印，解析失败时静默返回None
            pass
    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)

    # 参数验证：如果没有提供admin_class参数，返回空结果
    if not admin_class:
        return jsonify([])
    
    # 筛选数据
    filtered_data = grade_data
    
    # 注意：新的数据文件中没有'grade'列，所以跳过grade筛选
    # if grade:
    #     filtered_data = filtered_data[filtered_data["grade"] == grade]
        
    # 筛选指定班级的数据
    filtered_data = filtered_data[filtered_data["行政班级"].str.contains(admin_class, na=False)]
    
    # 如果没有找到匹配的班级，返回空结果
    if filtered_data.empty:
        return jsonify([])

    # 移除课程类型筛选，显示所有课程（必修课、选修课等）
    # 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)  # 计算课程日期
            
            # 根据地点判断校区
            location_str = str(row["地点"]) if pd.notna(row["地点"]) else ""
            location = location_str.split("（")[0] if "（" in location_str else location_str
            campus = get_campus_by_classroom(location_str)
            # 简化校区名称显示
            campus_display = campus.replace("校区", "")
            
            results.append({
                "课程": row["课程"].split("]")[1].strip() if "]" in row["课程"] and len(row["课程"].split("]")) > 1 and row["课程"].split("]")[1].strip() else row["课程"],
                "教师": row["教师"],
                "地点": location,
                "星期": day,
                "日期": course_date.strftime("%Y-%m-%d"),  # 格式化为字符串
                "节次": periods,
                "节次范围": f"第{periods[0]}-{periods[-1]}节",
                "周次": row["周次"],
                "校区": campus_display,
                "上课班级": str(row["行政班级"]) if "行政班级" in row else ""
            })

    # 按星期和节次排序
    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)

    # 参数验证：如果没有提供teacher参数，返回空结果
    if not teacher:
        return jsonify([])
    
    # 筛选数据
    filtered_data = teacher_data
    # 筛选指定教师的数据
    filtered_data = filtered_data[filtered_data["教师"] == teacher]
    
    # 如果没有找到匹配的教师，返回空结果
    if filtered_data.empty:
        return jsonify([])
    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)  # 计算课程日期
            
            # 根据地点判断校区
            location_str = str(row["地点"]) if pd.notna(row["地点"]) else ""
            location = location_str.split("（")[0] if "（" in location_str else location_str
            campus = get_campus_by_classroom(location_str)
            # 简化校区名称显示
            campus_display = campus.replace("校区", "")
            
            results.append({
                "课程": row["课程"].split("]")[1].strip() if "]" in row["课程"] and len(row["课程"].split("]")) > 1 and row["课程"].split("]")[1].strip() else row["课程"],
                "教师": row["教师"],
                "地点": location,
                "星期": day,
                "日期": course_date.strftime("%Y-%m-%d"),  # 格式化为字符串
                "节次": periods,
                "节次范围": f"第{periods[0]}-{periods[-1]}节",
                "周次": row["周次"],
                "校区": campus_display,
                "上课班级": str(row["行政班级"]) if "行政班级" in row else ""
            })

    # 按星期和节次排序
    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("/classrooms")
def classroom_page():
    return render_template("classroom.html")

@app.route("/schedule-overlap")
def schedule_overlap_page():
    return render_template("schedule_overlap.html")

@app.route("/api/students")
def get_students():
    students = student_data["姓名"].dropna().unique().tolist()
    return jsonify(sorted(students))

# 教室相关 API
@app.route("/api/campuses")
def get_campuses():
    if classroom_data.empty:
        return jsonify([])
    campuses = classroom_data["校区"].dropna().unique().tolist()
    return jsonify(sorted(campuses))

@app.route("/api/classrooms")
def get_classrooms():
    campus = request.args.get("campus", None)
    print(f"请求校区: {campus}")
    print(f"classroom_data是否为空: {classroom_data.empty}")
    
    if classroom_data.empty:
        print("教室数据为空，返回空列表")
        return jsonify([])
    
    filtered_data = classroom_data
    if campus:
        filtered_data = filtered_data[filtered_data["校区"] == campus]
        print(f"筛选后的数据条数: {len(filtered_data)}")
    
    classrooms = filtered_data["教室"].dropna().unique().tolist()
    print(f"找到的教室数量: {len(classrooms)}")
    print(f"前5个教室: {classrooms[:5] if classrooms else '无'}")
    return jsonify(sorted(classrooms))

@app.route("/api/classroom_courses")
def get_courses_by_classroom():
    week = request.args.get("week", 1)
    classroom = request.args.get("classroom", None)
    campus = request.args.get("campus", None)
    
    # 参数验证：如果没有提供classroom参数，返回空结果
    if not classroom:
        return jsonify([])
    
    if classroom_data.empty:
        return jsonify({"error": "教室数据未加载"}), 500
    
    # 筛选数据
    filtered_data = classroom_data
    
    if campus:
        filtered_data = filtered_data[filtered_data["校区"] == campus]
    
    # 筛选指定教室的数据
    filtered_data = filtered_data[filtered_data["教室"] == classroom]
    
    # 如果没有找到匹配的教室，返回空结果
    if filtered_data.empty:
        return jsonify([])
    
    if week:
        week = int(week)
        filtered_data = filtered_data[
            filtered_data["周次"].apply(lambda x: week in parse_weeks(str(x)) if pd.notna(x) else False)
        ]
    
    # 解析课程信息
    results = []
    seen_courses = set()  # 用于去重的集合
    
    for _, row in filtered_data.iterrows():
        day_and_period = parse_day_and_period(str(row["节次"]))
        if day_and_period:
            day, periods = day_and_period
            course_date = calculate_date(week, day)
            
            # 提取课程名称
            raw_course_name = str(row["课程名称"])
            if "]" in raw_course_name:
                parts = raw_course_name.split("]")
                if len(parts) > 1 and parts[1].strip():
                    course_name = parts[1].strip()
                else:
                    course_name = raw_course_name  # 如果分割后为空，使用原始名称
            else:
                course_name = raw_course_name
            
            # 如果课程名称为空或只有空白字符，跳过这条记录
            if not course_name or course_name.strip() == "" or course_name == "nan":
                continue
            
            # 创建唯一标识符：星期+节次+课程名称
            course_key = (day, tuple(periods), course_name)
            
            # 如果这个课程时间段组合已经存在，跳过
            if course_key in seen_courses:
                continue
                
            seen_courses.add(course_key)
            
            results.append({
                "课程": course_name,
                "教师": str(row["教师"]),
                "地点": str(row["教室"]),
                "星期": day,
                "日期": course_date.strftime("%Y-%m-%d"),
                "节次": periods,
                "节次范围": f"第{periods[0]}-{periods[-1]}节",
                "周次": str(row["周次"]),
                "校区": str(row["校区"]),
                "上课班级": str(row["行政班级"]) if "行政班级" in row else ""
            })
    
    # 按星期和节次排序
    results = sorted(results, key=lambda x: (x["星期"], x["节次"][0]))
    return jsonify(results)

@app.route("/api/schedule_overlap")
def get_schedule_overlap():
    """
    获取23级大数据1区、24级大数据1/2/3区的课表叠加数据
    """
    week = request.args.get("week", "1")
    
    # 目标班级列表
    target_classes = ["23大数据1区", "24大数据1区", "24大数据2区", "24大数据3区", "24信息安全技术应用1区"]
    
    try:
        # 解析周次
        week_num = int(week)
        
        # 获取所有目标班级的课程数据
        all_courses = []
        for class_name in target_classes:
            # 筛选指定班级和周次的课程
            class_courses = grade_data[
                (grade_data["行政班级"] == class_name) &
                (grade_data["周次"].apply(lambda x: week_num in parse_weeks(str(x)) if pd.notna(x) else False))
            ]
            
            for _, course in class_courses.iterrows():
                # 解析节次和星期
                day_and_period = parse_day_and_period(course["节次"])
                
                if day_and_period:
                    day, periods = day_and_period
                    # 计算日期
                    course_date = calculate_date(week_num, day)
                    
                    course_info = {
                        "班级": class_name,
                        "课程": course["课程"],
                        "教师": course["教师"],
                        "地点": course["地点"],
                        "校区": get_campus_by_classroom(course["地点"]),
                        "日期": course_date.strftime("%Y-%m-%d"),
                        "星期": day,
                        "节次": periods,
                        "周次": course["周次"]  # 添加周次信息，便于前端区分
                    }
                    all_courses.append(course_info)
        
        return jsonify(all_courses)
        
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route("/api/class_students")
def get_class_students():
    """
    获取指定班级的学生名单
    """
    class_name = request.args.get("class_name", "")
    
    if not class_name:
        return jsonify({"error": "缺少班级名称参数"}), 400
    
    try:
        # 使用拷贝的数据，避免修改原始 student_data
        student_data_copy = student_data.copy()
        student_data_copy["区队"] = student_data_copy["区队"].str.extract(r"\](.*)$")[0].str.strip()
        
        # 查找指定班级的学生
        class_students = student_data_copy[student_data_copy["区队"] == class_name]
        
        # 获取学生名单并过滤
        all_students = class_students["姓名"].tolist()
        # 只返回在过滤名单中的学生
        filtered_students = [student for student in all_students if student in allowed_students]
        
        return jsonify(filtered_students)
        
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@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([])  # 返回空数组而不是错误对象

    # 获取学生所在班级
    admin_classes = matching_students["区队"].unique()

    # 筛选课程数据
    if '行政班级' in grade_data.columns:
        filtered_data = grade_data[grade_data["行政班级"].isin(admin_classes)]
    else:
        return jsonify([])

    # 移除课程类型筛选，显示所有课程（必修课、选修课等）
    # if '课程类别' in filtered_data.columns:
    #     filtered_data = filtered_data[filtered_data["课程类别"].str.contains("必修课", na=False)]

    # 按周次筛选
    if week:
        week = int(week)
        if '周次' in filtered_data.columns:
            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([])  # 返回空数组而不是错误对象

    # 解析课程信息
    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)  # 计算课程日期
            
            # 根据地点判断校区
            location_str = str(row["地点"]) if pd.notna(row["地点"]) else ""
            location = location_str.split("（")[0] if "（" in location_str else location_str
            campus = get_campus_by_classroom(location_str)
            # 简化校区名称显示
            campus_display = campus.replace("校区", "")
            
            results.append({
               "课程": row["课程"].split("]")[1].strip() if "]" in row["课程"] and len(row["课程"].split("]")) > 1 and row["课程"].split("]")[1].strip() else row["课程"],
                "教师": row["教师"],
                "地点": location,
                "星期": day,
                "日期": course_date.strftime("%Y-%m-%d"),  # 格式化为字符串
                "节次": periods,
                "节次范围": f"第{periods[0]}-{periods[-1]}节",
                "周次": row["周次"],
                "校区": campus_display,
                "上课班级": str(row["行政班级"]) if "行政班级" in row else ""
            })

    # 按星期和节次排序
    results = sorted(results, key=lambda x: (x["星期"], x["节次"][0]))
    return jsonify(results)




if __name__ == "__main__":
    app.run(host="0.0.0.0", port=7860, debug=False)