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)