from flask import Flask, render_template, request, redirect, url_for, flash, jsonify, session
from werkzeug.security import generate_password_hash, check_password_hash
import pymysql
import uuid
from datetime import datetime, timedelta
from functools import wraps
app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-key-change-in-production'
# 数据库配置 - 请根据您的实际情况修改
DB_CONFIG = {
'host': 'localhost',
'user': 'root',
'password': 'lufei666666',
'database': 'student_course_system',
'charset': 'utf8mb4'
}
def get_db_connection():
"""获取数据库连接"""
return pymysql.connect(**DB_CONFIG)
def execute_query(query, params=None, fetch=True):
"""执行SQL查询"""
conn = get_db_connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
try:
cursor.execute(query, params)
if fetch:
if query.strip().upper().startswith('SELECT'):
result = cursor.fetchall()
else:
conn.commit()
result = cursor.lastrowid
else:
conn.commit()
result = None
return result
except Exception as e:
conn.rollback()
raise e
finally:
cursor.close()
conn.close()
def login_required(role=None):
"""登录装饰器"""
def decorator(f):
@wraps(f)
def decorated_function(*args, **kwargs):
if 'user_id' not in session:
flash('请先登录!', 'error')
return redirect(url_for('login'))
if role and session.get('role') != role:
flash('无权访问此页面!', 'error')
return redirect(url_for('index'))
return f(*args, **kwargs)
return decorated_function
return decorator
# ========== 通用路由 ==========
@app.route('/')
def index():
return render_template('index.html')
@app.route('/login', methods=['GET', 'POST'])
def login():
if request.method == 'POST':
username = request.form.get('username')
password = request.form.get('password')
try:
users = execute_query(
"SELECT * FROM user WHERE username = %s",
(username,)
)
if users and check_password_hash(users[0]['password'], password):
session['user_id'] = users[0]['id']
session['username'] = users[0]['username']
session['role'] = users[0]['role']
flash('登录成功!', 'success')
if users[0]['role'] == 'admin':
return redirect(url_for('admin_dashboard'))
elif users[0]['role'] == 'teacher':
return redirect(url_for('teacher_dashboard'))
else:
return redirect(url_for('student_dashboard'))
else:
flash('用户名或密码错误!', 'error')
except Exception as e:
flash(f'登录失败: {str(e)}', 'error')
return render_template('login.html')
@app.route('/logout')
def logout():
session.clear()
flash('您已成功退出系统!', 'success')
return redirect(url_for('index'))
# ========== 管理员路由 ==========
@app.route('/admin/dashboard')
@login_required(role='admin')
def admin_dashboard():
try:
student_count = execute_query("SELECT COUNT(*) as count FROM student")[0]['count']
teacher_count = execute_query("SELECT COUNT(*) as count FROM teacher")[0]['count']
course_count = execute_query("SELECT COUNT(*) as count FROM course")[0]['count']
classroom_count = execute_query("SELECT COUNT(*) as count FROM classroom")[0]['count']
return render_template('admin/dashboard.html',
student_count=student_count,
teacher_count=teacher_count,
course_count=course_count,
classroom_count=classroom_count)
except Exception as e:
flash(f'加载仪表板失败: {str(e)}', 'error')
return redirect(url_for('index'))
# ========== 院系管理 ==========
@app.route('/admin/yuanxi')
@login_required(role='admin')
def admin_yuanxi():
try:
yuanxi_list = execute_query("SELECT * FROM yuanxi ORDER BY create_time DESC")
return render_template('admin/yuanxi.html', yuanxi_list=yuanxi_list)
except Exception as e:
flash(f'加载院系列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/yuanxi/add', methods=['POST'])
@login_required(role='admin')
def add_yuanxi():
yuanxi_name = request.form.get('yuanxi_name')
yuanxi_desc = request.form.get('yuanxi_desc')
try:
execute_query(
"INSERT INTO yuanxi (yuanxi_name, yuanxi_desc) VALUES (%s, %s)",
(yuanxi_name, yuanxi_desc),
fetch=False
)
return jsonify({'success': True, 'message': '院系添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
@app.route('/admin/yuanxi/<int:id>/delete', methods=['POST'])
@login_required(role='admin')
def delete_yuanxi(id):
try:
execute_query(
"DELETE FROM yuanxi WHERE id = %s",
(id,),
fetch=False
)
return jsonify({'success': True, 'message': '院系删除成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'删除失败: {str(e)}'})
# ========== 班级管理 ==========
@app.route('/admin/banji')
@login_required(role='admin')
def admin_banji():
try:
banji_list = execute_query("""
SELECT b.*, y.yuanxi_name
FROM banji b
LEFT JOIN yuanxi y ON b.yuanxi_id = y.id
ORDER BY b.create_time DESC
""")
yuanxi_list = execute_query("SELECT * FROM yuanxi ORDER BY yuanxi_name")
return render_template('admin/banji.html', banji_list=banji_list, yuanxi_list=yuanxi_list)
except Exception as e:
flash(f'加载班级列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/banji/add', methods=['POST'])
@login_required(role='admin')
def add_banji():
banji_name = request.form.get('banji_name')
yuanxi_id = request.form.get('yuanxi_id')
banji_desc = request.form.get('banji_desc')
try:
execute_query(
"INSERT INTO banji (banji_name, yuanxi_id, banji_desc) VALUES (%s, %s, %s)",
(banji_name, yuanxi_id, banji_desc),
fetch=False
)
return jsonify({'success': True, 'message': '班级添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
@app.route('/admin/banji/<int:id>/delete', methods=['POST'])
@login_required(role='admin')
def delete_banji(id):
try:
execute_query(
"DELETE FROM banji WHERE id = %s",
(id,),
fetch=False
)
return jsonify({'success': True, 'message': '班级删除成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'删除失败: {str(e)}'})
# ========== 课程性质管理 ==========
@app.route('/admin/kecheng_xingzhi')
@login_required(role='admin')
def admin_kecheng_xingzhi():
try:
xingzhi_list = execute_query("SELECT * FROM kecheng_xingzhi ORDER BY create_time DESC")
return render_template('admin/kecheng_xingzhi.html', xingzhi_list=xingzhi_list)
except Exception as e:
flash(f'加载课程性质列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/kecheng_xingzhi/add', methods=['POST'])
@login_required(role='admin')
def add_kecheng_xingzhi():
xingzhi_name = request.form.get('xingzhi_name')
xingzhi_desc = request.form.get('xingzhi_desc')
try:
execute_query(
"INSERT INTO kecheng_xingzhi (xingzhi_name, xingzhi_desc) VALUES (%s, %s)",
(xingzhi_name, xingzhi_desc),
fetch=False
)
return jsonify({'success': True, 'message': '课程性质添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
@app.route('/admin/kecheng_xingzhi/<int:id>/delete', methods=['POST'])
@login_required(role='admin')
def delete_kecheng_xingzhi(id):
try:
execute_query(
"DELETE FROM kecheng_xingzhi WHERE id = %s",
(id,),
fetch=False
)
return jsonify({'success': True, 'message': '课程性质删除成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'删除失败: {str(e)}'})
# ========== 计划类型管理 ==========
@app.route('/admin/jihua_types')
@login_required(role='admin')
def admin_jihua_types():
try:
jihua_types_list = execute_query("SELECT * FROM jihua_types ORDER BY create_time DESC")
return render_template('admin/jihua_types.html', jihua_types_list=jihua_types_list)
except Exception as e:
flash(f'加载计划类型列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/jihua_types/add', methods=['POST'])
@login_required(role='admin')
def add_jihua_types():
type_name = request.form.get('type_name')
type_desc = request.form.get('type_desc')
try:
execute_query(
"INSERT INTO jihua_types (type_name, type_desc) VALUES (%s, %s)",
(type_name, type_desc),
fetch=False
)
return jsonify({'success': True, 'message': '计划类型添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
@app.route('/admin/jihua_types/<int:id>/delete', methods=['POST'])
@login_required(role='admin')
def delete_jihua_types(id):
try:
execute_query(
"DELETE FROM jihua_types WHERE id = %s",
(id,),
fetch=False
)
return jsonify({'success': True, 'message': '计划类型删除成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'删除失败: {str(e)}'})
# ========== 学生管理 ==========
@app.route('/admin/students')
@login_required(role='admin')
def admin_students():
try:
students = execute_query("""
SELECT s.*, u.username, b.banji_name, y.yuanxi_name
FROM student s
JOIN user u ON s.user_id = u.id
LEFT JOIN banji b ON s.banji_types = b.id
LEFT JOIN yuanxi y ON b.yuanxi_id = y.id
ORDER BY s.create_time DESC
""")
banji_list = execute_query("SELECT * FROM banji ORDER BY banji_name")
return render_template('admin/students.html', students=students, banji_list=banji_list)
except Exception as e:
flash(f'加载学生列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/student/add', methods=['POST'])
@login_required(role='admin')
def add_student():
username = request.form.get('username')
password = request.form.get('password')
student_name = request.form.get('student_name')
student_phone = request.form.get('student_phone')
student_email = request.form.get('student_email')
sex_types = request.form.get('sex_types')
banji_types = request.form.get('banji_types')
try:
execute_query(
"INSERT INTO user (username, password, role) VALUES (%s, %s, %s)",
(username, generate_password_hash(password), 'student'),
fetch=False
)
user_id = execute_query("SELECT LAST_INSERT_ID() as id")[0]['id']
execute_query(
"INSERT INTO student (user_id, student_name, student_phone, student_email, sex_types, banji_types) VALUES (%s, %s, %s, %s, %s, %s)",
(user_id, student_name, student_phone, student_email, sex_types, banji_types),
fetch=False
)
return jsonify({'success': True, 'message': '学生添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
# ========== 教师管理 ==========
@app.route('/admin/teachers')
@login_required(role='admin')
def admin_teachers():
try:
teachers = execute_query("""
SELECT t.*, u.username
FROM teacher t
JOIN user u ON t.user_id = u.id
ORDER BY t.create_time DESC
""")
return render_template('admin/teachers.html', teachers=teachers)
except Exception as e:
flash(f'加载教师列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/teacher/add', methods=['POST'])
@login_required(role='admin')
def add_teacher():
username = request.form.get('username')
password = request.form.get('password')
teacher_name = request.form.get('teacher_name')
teacher_phone = request.form.get('teacher_phone')
teacher_email = request.form.get('teacher_email')
sex_types = request.form.get('sex_types')
teacher_shanchang = request.form.get('teacher_shanchang')
teacher_rongyu = request.form.get('teacher_rongyu')
teacher_content = request.form.get('teacher_content')
try:
execute_query(
"INSERT INTO user (username, password, role) VALUES (%s, %s, %s)",
(username, generate_password_hash(password), 'teacher'),
fetch=False
)
user_id = execute_query("SELECT LAST_INSERT_ID() as id")[0]['id']
execute_query(
"""INSERT INTO teacher (user_id, teacher_name, teacher_phone, teacher_email,
sex_types, teacher_shanchang, teacher_rongyu, teacher_content)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)""",
(user_id, teacher_name, teacher_phone, teacher_email, sex_types,
teacher_shanchang, teacher_rongyu, teacher_content),
fetch=False
)
return jsonify({'success': True, 'message': '教师添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
# ========== 课程管理 ==========
@app.route('/admin/courses')
@login_required(role='admin')
def admin_courses():
try:
courses = execute_query("""
SELECT c.*, t.teacher_name, y.yuanxi_name, kx.xingzhi_name, kt.kaohe_name,
(SELECT COUNT(*) FROM course_selection WHERE course_id = c.id) as selected_count
FROM course c
JOIN teacher t ON c.teacher_id = t.id
LEFT JOIN yuanxi y ON c.yuanxi_types = y.id
LEFT JOIN kecheng_xingzhi kx ON c.course_xingzhi_types = kx.id
LEFT JOIN kaohe_types kt ON c.course_kaohe_types = kt.id
ORDER BY c.create_time DESC
""")
teachers = execute_query("SELECT * FROM teacher ORDER BY teacher_name")
yuanxi_list = execute_query("SELECT * FROM yuanxi ORDER BY yuanxi_name")
xingzhi_list = execute_query("SELECT * FROM kecheng_xingzhi ORDER BY xingzhi_name")
kaohe_list = execute_query("SELECT * FROM kaohe_types ORDER BY kaohe_name")
return render_template('admin/courses.html',
courses=courses,
teachers=teachers,
yuanxi_list=yuanxi_list,
xingzhi_list=xingzhi_list,
kaohe_list=kaohe_list,
total_students=len(courses),
now=datetime.now())
except Exception as e:
flash(f'加载课程列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/course/add', methods=['POST'])
@login_required(role='admin')
def add_course():
teacher_id = request.form.get('teacher_id')
course_name = request.form.get('course_name')
course_daima = request.form.get('course_daima')
yuanxi_types = request.form.get('yuanxi_types')
course_xingzhi_types = request.form.get('course_xingzhi_types')
course_xuefen = request.form.get('course_xuefen')
course_zongxueshi = request.form.get('course_zongxueshi')
course_kaohe_types = request.form.get('course_kaohe_types')
course_renshu = request.form.get('course_renshu')
jiezhi_time = request.form.get('jiezhi_time')
course_content = request.form.get('course_content')
try:
execute_query(
"""INSERT INTO course (teacher_id, course_uuid_number, course_name, course_daima,
yuanxi_types, course_xingzhi_types, course_xuefen, course_zongxueshi,
course_kaohe_types, course_renshu, jiezhi_time, course_content)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
(teacher_id, str(uuid.uuid4()), course_name, course_daima,
yuanxi_types, course_xingzhi_types, course_xuefen, course_zongxueshi,
course_kaohe_types, course_renshu, jiezhi_time, course_content),
fetch=False
)
return jsonify({'success': True, 'message': '课程添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
# ========== 教学计划管理 ==========
@app.route('/admin/teaching_plans')
@login_required(role='admin')
def admin_teaching_plans():
try:
teaching_plans = execute_query("""
SELECT tp.*, c.course_name, jt.type_name
FROM teaching_plan tp
JOIN course c ON tp.course_id = c.id
LEFT JOIN jihua_types jt ON tp.teaching_plan_types = jt.id
ORDER BY tp.create_time DESC
""")
courses = execute_query("SELECT * FROM course ORDER BY course_name")
jihua_types_list = execute_query("SELECT * FROM jihua_types ORDER BY type_name")
return render_template('admin/teaching_plans.html',
teaching_plans=teaching_plans,
courses=courses,
jihua_types_list=jihua_types_list)
except Exception as e:
flash(f'加载教学计划列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/teaching_plan/add', methods=['POST'])
@login_required(role='admin')
def add_teaching_plan():
course_id = request.form.get('course_id')
teaching_plan_name = request.form.get('teaching_plan_name')
teaching_plan_types = request.form.get('teaching_plan_types')
teaching_plan_content = request.form.get('teaching_plan_content')
try:
execute_query(
"""INSERT INTO teaching_plan (course_id, teaching_plan_uuid_number,
teaching_plan_name, teaching_plan_types, teaching_plan_content)
VALUES (%s, %s, %s, %s, %s)""",
(course_id, str(uuid.uuid4()), teaching_plan_name, teaching_plan_types, teaching_plan_content),
fetch=False
)
return jsonify({'success': True, 'message': '教学计划添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
# ========== 选课管理 ==========
@app.route('/admin/course_selections')
@login_required(role='admin')
def admin_course_selections():
try:
course_selections = execute_query("""
SELECT cs.*, s.student_name, c.course_name, t.teacher_name
FROM course_selection cs
JOIN student s ON cs.student_id = s.id
JOIN course c ON cs.course_id = c.id
JOIN teacher t ON c.teacher_id = t.id
ORDER BY cs.create_time DESC
""")
students = execute_query("SELECT * FROM student ORDER BY student_name")
courses = execute_query("SELECT * FROM course ORDER BY course_name")
return render_template('admin/course_selections.html',
course_selections=course_selections,
students=students,
courses=courses)
except Exception as e:
flash(f'加载选课列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/course_selection/add', methods=['POST'])
@login_required(role='admin')
def add_course_selection():
student_id = request.form.get('student_id')
course_id = request.form.get('course_id')
try:
# 检查是否已经选过该课程
existing = execute_query(
"SELECT * FROM course_selection WHERE student_id = %s AND course_id = %s",
(student_id, course_id)
)
if existing:
return jsonify({'success': False, 'message': '该学生已经选择了此课程!'})
# 检查课程人数限制
course = execute_query("SELECT * FROM course WHERE id = %s", (course_id,))[0]
current_count = execute_query(
"SELECT COUNT(*) as count FROM course_selection WHERE course_id = %s",
(course_id,)
)[0]['count']
if current_count >= course['course_renshu']:
return jsonify({'success': False, 'message': '该课程已满!'})
execute_query(
"INSERT INTO course_selection (student_id, course_id, selection_uuid_number) VALUES (%s, %s, %s)",
(student_id, course_id, str(uuid.uuid4())),
fetch=False
)
return jsonify({'success': True, 'message': '选课添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
@app.route('/admin/course_selection/<int:id>/delete', methods=['POST'])
@login_required(role='admin')
def delete_course_selection(id):
try:
execute_query(
"DELETE FROM course_selection WHERE id = %s",
(id,),
fetch=False
)
return jsonify({'success': True, 'message': '选课记录删除成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'删除失败: {str(e)}'})
# ========== 成绩管理 ==========
@app.route('/admin/grades')
@login_required(role='admin')
def admin_grades():
try:
grades = execute_query("""
SELECT g.*, s.student_name, c.course_name, t.teacher_name
FROM grade g
JOIN student s ON g.student_id = s.id
JOIN course c ON g.course_id = c.id
JOIN teacher t ON c.teacher_id = t.id
ORDER BY g.create_time DESC
""")
students = execute_query("SELECT * FROM student ORDER BY student_name")
courses = execute_query("SELECT * FROM course ORDER BY course_name")
return render_template('admin/grades.html',
grades=grades,
students=students,
courses=courses)
except Exception as e:
flash(f'加载成绩列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/grade/add', methods=['POST'])
@login_required(role='admin')
def add_grade():
student_id = request.form.get('student_id')
course_id = request.form.get('course_id')
grade_score = request.form.get('grade_score')
grade_comment = request.form.get('grade_comment')
try:
# 检查是否已经存在成绩记录
existing = execute_query(
"SELECT * FROM grade WHERE student_id = %s AND course_id = %s",
(student_id, course_id)
)
if existing:
return jsonify({'success': False, 'message': '该学生在此课程中已有成绩记录!'})
execute_query(
"INSERT INTO grade (student_id, course_id, grade_score, grade_comment, grade_uuid_number) VALUES (%s, %s, %s, %s, %s)",
(student_id, course_id, grade_score, grade_comment, str(uuid.uuid4())),
fetch=False
)
return jsonify({'success': True, 'message': '成绩添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
@app.route('/admin/grade/<int:id>/delete', methods=['POST'])
@login_required(role='admin')
def delete_grade(id):
try:
execute_query(
"DELETE FROM grade WHERE id = %s",
(id,),
fetch=False
)
return jsonify({'success': True, 'message': '成绩记录删除成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'删除失败: {str(e)}'})
# ========== 教室管理 ==========
@app.route('/admin/classrooms')
@login_required(role='admin')
def admin_classrooms():
try:
classrooms = execute_query("SELECT * FROM classroom ORDER BY create_time DESC")
return render_template('admin/classrooms.html', classrooms=classrooms)
except Exception as e:
flash(f'加载教室列表失败: {str(e)}', 'error')
return redirect(url_for('admin_dashboard'))
@app.route('/admin/classroom/add', methods=['POST'])
@login_required(role='admin')
def add_classroom():
classroom_name = request.form.get('classroom_name')
classroom_address = request.form.get('classroom_address')
classroom_content = request.form.get('classroom_content')
try:
execute_query(
"INSERT INTO classroom (classroom_uuid_number, classroom_name, classroom_address, classroom_content) VALUES (%s, %s, %s, %s)",
(str(uuid.uuid4()), classroom_name, classroom_address, classroom_content),
fetch=False
)
return jsonify({'success': True, 'message': '教室添加成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'添加失败: {str(e)}'})
@app.route('/admin/classroom/<int:id>/delete', methods=['POST'])
@login_required(role='admin')
def delete_classroom(id):
try:
execute_query(
"DELETE FROM classroom WHERE id = %s",
(id,),
fetch=False
)
return jsonify({'success': True, 'message': '教室删除成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'删除失败: {str(e)}'})
# ========== 管理员修改密码 ==========
@app.route('/admin/change_password', methods=['GET', 'POST'])
@login_required(role='admin')
def admin_change_password():
if request.method == 'POST':
old_password = request.form.get('old_password')
new_password = request.form.get('new_password')
confirm_password = request.form.get('confirm_password')
if new_password != confirm_password:
flash('新密码和确认密码不一致!', 'error')
return redirect(url_for('admin_change_password'))
try:
user = execute_query("SELECT * FROM user WHERE id = %s", (session['user_id'],))[0]
if not check_password_hash(user['password'], old_password):
flash('旧密码错误!', 'error')
return redirect(url_for('admin_change_password'))
execute_query(
"UPDATE user SET password = %s WHERE id = %s",
(generate_password_hash(new_password), session['user_id']),
fetch=False
)
flash('密码修改成功!', 'success')
return redirect(url_for('admin_dashboard'))
except Exception as e:
flash(f'密码修改失败: {str(e)}', 'error')
return render_template('admin/change_password.html')
# ========== 教师路由 ==========
@app.route('/teacher/dashboard')
@login_required(role='teacher')
def teacher_dashboard():
try:
user_id = session['user_id']
teachers = execute_query(
"SELECT * FROM teacher WHERE user_id = %s",
(user_id,)
)
if not teachers:
flash('教师信息不存在!', 'error')
return redirect(url_for('index'))
teacher = teachers[0]
courses = execute_query(
"SELECT * FROM course WHERE teacher_id = %s",
(teacher['id'],)
)
total_students = execute_query("""
SELECT COUNT(DISTINCT cs.student_id) as count
FROM course_selection cs
JOIN course c ON cs.course_id = c.id
WHERE c.teacher_id = %s
""", (teacher['id'],))[0]['count']
teaching_plans_count = execute_query("""
SELECT COUNT(*) as count
FROM teaching_plan tp
JOIN course c ON tp.course_id = c.id
WHERE c.teacher_id = %s
""", (teacher['id'],))[0]['count']
grades_count = execute_query("""
SELECT COUNT(*) as count
FROM grade g
JOIN course c ON g.course_id = c.id
WHERE c.teacher_id = %s
""", (teacher['id'],))[0]['count']
pending_grades_count = execute_query("""
SELECT COUNT(DISTINCT cs.student_id) as count
FROM course_selection cs
JOIN course c ON cs.course_id = c.id
LEFT JOIN grade g ON g.student_id = cs.student_id AND g.course_id = cs.course_id
WHERE c.teacher_id = %s AND g.id IS NULL
""", (teacher['id'],))[0]['count']
upcoming_deadlines = execute_query("""
SELECT * FROM course
WHERE teacher_id = %s AND jiezhi_time > %s AND jiezhi_time < %s
""", (teacher['id'], datetime.now(), datetime.now() + timedelta(days=7)))
return render_template('teacher/dashboard.html',
teacher=teacher,
courses=courses,
total_students=total_students,
teaching_plans_count=teaching_plans_count,
grades_count=grades_count,
pending_grades_count=pending_grades_count,
upcoming_deadlines=upcoming_deadlines,
now=datetime.now())
except Exception as e:
flash(f'加载教师仪表板失败: {str(e)}', 'error')
return redirect(url_for('index'))
@app.route('/teacher/courses')
@login_required(role='teacher')
def teacher_courses():
try:
user_id = session['user_id']
teachers = execute_query(
"SELECT * FROM teacher WHERE user_id = %s",
(user_id,)
)
if not teachers:
flash('教师信息不存在!', 'error')
return redirect(url_for('teacher_dashboard'))
teacher = teachers[0]
courses = execute_query("""
SELECT c.*,
(SELECT COUNT(*) FROM course_selection WHERE course_id = c.id) as selected_count
FROM course c
WHERE c.teacher_id = %s
ORDER BY c.create_time DESC
""", (teacher['id'],))
return render_template('teacher/courses.html',
courses=courses,
teacher=teacher,
now=datetime.now())
except Exception as e:
flash(f'加载课程列表失败: {str(e)}', 'error')
return redirect(url_for('teacher_dashboard'))
@app.route('/teacher/teaching_plans')
@login_required(role='teacher')
def teacher_teaching_plans():
try:
user_id = session['user_id']
teachers = execute_query(
"SELECT * FROM teacher WHERE user_id = %s",
(user_id,)
)
if not teachers:
flash('教师信息不存在!', 'error')
return redirect(url_for('teacher_dashboard'))
teacher = teachers[0]
teaching_plans = execute_query("""
SELECT tp.*, c.course_name, jt.type_name
FROM teaching_plan tp
JOIN course c ON tp.course_id = c.id
LEFT JOIN jihua_types jt ON tp.teaching_plan_types = jt.id
WHERE c.teacher_id = %s
ORDER BY tp.create_time DESC
""", (teacher['id'],))
courses = execute_query(
"SELECT id, course_name FROM course WHERE teacher_id = %s ORDER BY course_name",
(teacher['id'],)
)
jihua_types_list = execute_query("SELECT * FROM jihua_types ORDER BY type_name")
return render_template('teacher/teaching_plans.html',
teaching_plans=teaching_plans,
courses=courses,
jihua_types_list=jihua_types_list,
teacher=teacher)
except Exception as e:
flash(f'加载教学计划失败: {str(e)}', 'error')
return redirect(url_for('teacher_dashboard'))
@app.route('/teacher/grades')
@login_required(role='teacher')
def teacher_grades():
try:
user_id = session['user_id']
teachers = execute_query(
"SELECT * FROM teacher WHERE user_id = %s",
(user_id,)
)
if not teachers:
flash('教师信息不存在!', 'error')
return redirect(url_for('teacher_dashboard'))
teacher = teachers[0]
grades = execute_query("""
SELECT g.*, s.student_name, c.course_name
FROM grade g
JOIN student s ON g.student_id = s.id
JOIN course c ON g.course_id = c.id
WHERE c.teacher_id = %s
ORDER BY g.create_time DESC
""", (teacher['id'],))
courses = execute_query(
"SELECT id, course_name FROM course WHERE teacher_id = %s ORDER BY course_name",
(teacher['id'],)
)
course_selections = execute_query("""
SELECT cs.*, s.student_name, c.course_name
FROM course_selection cs
JOIN student s ON cs.student_id = s.id
JOIN course c ON cs.course_id = c.id
WHERE c.teacher_id = %s
""", (teacher['id'],))
return render_template('teacher/grades.html',
grades=grades,
courses=courses,
course_selections=course_selections,
teacher=teacher)
except Exception as e:
flash(f'加载成绩列表失败: {str(e)}', 'error')
return redirect(url_for('teacher_dashboard'))
@app.route('/teacher/classrooms')
@login_required(role='teacher')
def teacher_classrooms():
try:
classrooms = execute_query("SELECT * FROM classroom ORDER BY create_time DESC")
return render_template('teacher/classrooms.html', classrooms=classrooms)
except Exception as e:
flash(f'加载教室列表失败: {str(e)}', 'error')
return redirect(url_for('teacher_dashboard'))
@app.route('/teacher/profile')
@login_required(role='teacher')
def teacher_profile():
try:
user_id = session['user_id']
teachers = execute_query("""
SELECT t.*, u.username
FROM teacher t
JOIN user u ON t.user_id = u.id
WHERE t.user_id = %s
""", (user_id,))
if not teachers:
flash('教师信息不存在!', 'error')
return redirect(url_for('teacher_dashboard'))
teacher = teachers[0]
return render_template('teacher/profile.html', teacher=teacher)
except Exception as e:
flash(f'加载个人信息失败: {str(e)}', 'error')
return redirect(url_for('teacher_dashboard'))
@app.route('/teacher/change_password', methods=['GET', 'POST'])
@login_required(role='teacher')
def teacher_change_password():
if request.method == 'POST':
old_password = request.form.get('old_password')
new_password = request.form.get('new_password')
confirm_password = request.form.get('confirm_password')
if new_password != confirm_password:
flash('新密码和确认密码不一致!', 'error')
return redirect(url_for('teacher_change_password'))
try:
user = execute_query("SELECT * FROM user WHERE id = %s", (session['user_id'],))[0]
if not check_password_hash(user['password'], old_password):
flash('旧密码错误!', 'error')
return redirect(url_for('teacher_change_password'))
execute_query(
"UPDATE user SET password = %s WHERE id = %s",
(generate_password_hash(new_password), session['user_id']),
fetch=False
)
flash('密码修改成功!', 'success')
return redirect(url_for('teacher_dashboard'))
except Exception as e:
flash(f'密码修改失败: {str(e)}', 'error')
return render_template('teacher/change_password.html')
# ========== 学生路由 ==========
@app.route('/student/dashboard')
@login_required(role='student')
def student_dashboard():
try:
user_id = session['user_id']
students = execute_query(
"SELECT * FROM student WHERE user_id = %s",
(user_id,)
)
if not students:
flash('学生信息不存在!', 'error')
return redirect(url_for('index'))
student = students[0]
# 获取学生详细信息(班级、院系)
student_details = execute_query("""
SELECT s.*, b.banji_name, y.yuanxi_name
FROM student s
LEFT JOIN banji b ON s.banji_types = b.id
LEFT JOIN yuanxi y ON b.yuanxi_id = y.id
WHERE s.user_id = %s
""", (user_id,))[0]
# 获取已选课程
selected_courses = execute_query("""
SELECT c.*, t.teacher_name, cs.create_time as selection_time
FROM course_selection cs
JOIN course c ON cs.course_id = c.id
JOIN teacher t ON c.teacher_id = t.id
WHERE cs.student_id = %s
ORDER BY cs.create_time DESC
""", (student['id'],))
# 获取成绩
grades = execute_query("""
SELECT g.*, c.course_name, t.teacher_name, c.course_xuefen
FROM grade g
JOIN course c ON g.course_id = c.id
JOIN teacher t ON c.teacher_id = t.id
WHERE g.student_id = %s
ORDER BY g.create_time DESC
""", (student['id'],))
# 获取可选课程数量
available_courses = execute_query("""
SELECT COUNT(*) as count FROM course
WHERE jiezhi_time > %s OR jiezhi_time IS NULL
""", (datetime.now(),))
available_courses_count = available_courses[0]['count'] if available_courses else 0
# 最近成绩(前5个)
recent_grades = grades[:5] if grades else []
return render_template('student/dashboard.html',
student=student_details,
selected_courses=selected_courses,
grades=grades,
recent_grades=recent_grades,
available_courses_count=available_courses_count,
now=datetime.now())
except Exception as e:
flash(f'加载学生仪表板失败: {str(e)}', 'error')
return redirect(url_for('index'))
@app.route('/student/courses')
@login_required(role='student')
def student_courses():
try:
user_id = session['user_id']
# 获取学生信息
students = execute_query(
"SELECT * FROM student WHERE user_id = %s",
(user_id,)
)
if not students:
flash('学生信息不存在!', 'error')
return redirect(url_for('student_dashboard'))
student = students[0]
# 构建查询条件
query_params = []
where_conditions = ["c.jiezhi_time > %s OR c.jiezhi_time IS NULL"]
query_params.append(datetime.now())
# 课程性质筛选
course_type = request.args.get('course_type')
if course_type:
where_conditions.append("c.course_xingzhi_types = %s")
query_params.append(course_type)
# 院系筛选
department = request.args.get('department')
if department:
where_conditions.append("c.yuanxi_types = %s")
query_params.append(department)
# 搜索条件
search = request.args.get('search')
if search:
where_conditions.append("(c.course_name LIKE %s OR c.course_daima LIKE %s OR t.teacher_name LIKE %s)")
query_params.extend([f"%{search}%", f"%{search}%", f"%{search}%"])
# 排序方式
sort_by = request.args.get('sort', 'new')
order_by = "c.create_time DESC"
if sort_by == 'popular':
order_by = "selected_count DESC"
elif sort_by == 'name':
order_by = "c.course_name ASC"
# 获取课程列表
courses = execute_query(f"""
SELECT c.*, t.teacher_name, y.yuanxi_name, kx.xingzhi_name,
(SELECT COUNT(*) FROM course_selection WHERE course_id = c.id) as selected_count
FROM course c
JOIN teacher t ON c.teacher_id = t.id
LEFT JOIN yuanxi y ON c.yuanxi_types = y.id
LEFT JOIN kecheng_xingzhi kx ON c.course_xingzhi_types = kx.id
WHERE {' AND '.join(where_conditions)}
ORDER BY {order_by}
""", tuple(query_params))
# 获取学生已选课程ID
selected_courses = execute_query(
"SELECT course_id FROM course_selection WHERE student_id = %s",
(student['id'],)
)
selected_course_ids = [sc['course_id'] for sc in selected_courses]
# 获取院系列表
yuanxi_list = execute_query("SELECT * FROM yuanxi ORDER BY yuanxi_name")
return render_template('student/courses.html',
courses=courses,
selected_course_ids=selected_course_ids,
yuanxi_list=yuanxi_list,
now=datetime.now())
except Exception as e:
flash(f'加载课程列表失败: {str(e)}', 'error')
return redirect(url_for('student_dashboard'))
@app.route('/student/course/select/<int:course_id>', methods=['POST'])
@login_required(role='student')
def student_select_course(course_id):
try:
user_id = session['user_id']
students = execute_query(
"SELECT * FROM student WHERE user_id = %s",
(user_id,)
)
if not students:
return jsonify({'success': False, 'message': '学生信息不存在!'})
student = students[0]
# 检查是否已经选过该课程
existing_selection = execute_query(
"SELECT * FROM course_selection WHERE student_id = %s AND course_id = %s",
(student['id'], course_id)
)
if existing_selection:
return jsonify({'success': False, 'message': '您已经选择了此课程!'})
# 检查课程信息
courses = execute_query("SELECT * FROM course WHERE id = %s", (course_id,))
if not courses:
return jsonify({'success': False, 'message': '课程不存在!'})
course = courses[0]
# 检查课程人数限制
current_selections = execute_query(
"SELECT COUNT(*) as count FROM course_selection WHERE course_id = %s",
(course_id,)
)[0]['count']
if current_selections >= course['course_renshu']:
return jsonify({'success': False, 'message': '该课程已满!'})
# 检查选课时间
if course['jiezhi_time'] and course['jiezhi_time'] < datetime.now():
return jsonify({'success': False, 'message': '选课时间已过!'})
# 执行选课
execute_query(
"INSERT INTO course_selection (student_id, course_id, selection_uuid_number) VALUES (%s, %s, %s)",
(student['id'], course_id, str(uuid.uuid4())),
fetch=False
)
return jsonify({'success': True, 'message': '选课成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'选课失败: {str(e)}'})
@app.route('/student/course_selections')
@login_required(role='student')
def student_course_selections():
try:
user_id = session['user_id']
students = execute_query(
"SELECT * FROM student WHERE user_id = %s",
(user_id,)
)
if not students:
flash('学生信息不存在!', 'error')
return redirect(url_for('student_dashboard'))
student = students[0]
# 获取已选课程详情
selected_courses = execute_query("""
SELECT c.*, t.teacher_name, cs.create_time as selection_time,
CASE
WHEN c.jiezhi_time IS NULL OR c.jiezhi_time > NOW() THEN 1
ELSE 0
END as can_withdraw
FROM course_selection cs
JOIN course c ON cs.course_id = c.id
JOIN teacher t ON c.teacher_id = t.id
WHERE cs.student_id = %s
ORDER BY cs.create_time DESC
""", (student['id'],))
return render_template('student/course_selections.html',
selected_courses=selected_courses,
now=datetime.now())
except Exception as e:
flash(f'加载选课列表失败: {str(e)}', 'error')
return redirect(url_for('student_dashboard'))
@app.route('/student/course/withdraw/<int:course_id>', methods=['POST'])
@login_required(role='student')
def student_withdraw_course(course_id):
try:
user_id = session['user_id']
students = execute_query(
"SELECT * FROM student WHERE user_id = %s",
(user_id,)
)
if not students:
return jsonify({'success': False, 'message': '学生信息不存在!'})
student = students[0]
# 检查选课记录
selection = execute_query(
"SELECT * FROM course_selection WHERE student_id = %s AND course_id = %s",
(student['id'], course_id)
)
if not selection:
return jsonify({'success': False, 'message': '未找到选课记录!'})
# 检查课程是否允许退选(选课截止前)
course = execute_query("SELECT * FROM course WHERE id = %s", (course_id,))[0]
if course['jiezhi_time'] and course['jiezhi_time'] < datetime.now():
return jsonify({'success': False, 'message': '选课已截止,无法退选!'})
# 执行退选
execute_query(
"DELETE FROM course_selection WHERE student_id = %s AND course_id = %s",
(student['id'], course_id),
fetch=False
)
return jsonify({'success': True, 'message': '退选成功!'})
except Exception as e:
return jsonify({'success': False, 'message': f'退选失败: {str(e)}'})
@app.route('/student/grades')
@login_required(role='student')
def student_grades():
try:
user_id = session['user_id']
students = execute_query(
"SELECT * FROM student WHERE user_id = %s",
(user_id,)
)
if not students:
flash('学生信息不存在!', 'error')
return redirect(url_for('student_dashboard'))
student = students[0]
# 获取成绩列表
grades = execute_query("""
SELECT g.*, c.course_name, t.teacher_name, c.course_xuefen, c.course_daima
FROM grade g
JOIN course c ON g.course_id = c.id
JOIN teacher t ON c.teacher_id = t.id
WHERE g.student_id = %s
ORDER BY g.create_time DESC
""", (student['id'],))
return render_template('student/grades.html',
grades=grades,
student=student)
except Exception as e:
flash(f'加载成绩列表失败: {str(e)}', 'error')
return redirect(url_for('student_dashboard'))
@app.route('/student/classrooms')
@login_required(role='student')
def student_classrooms():
try:
classrooms = execute_query("SELECT * FROM classroom ORDER BY create_time DESC")
return render_template('student/classrooms.html', classrooms=classrooms)
except Exception as e:
flash(f'加载教室列表失败: {str(e)}', 'error')
return redirect(url_for('student_dashboard'))
@app.route('/student/profile')
@login_required(role='student')
def student_profile():
try:
user_id = session['user_id']
student_details = execute_query("""
SELECT s.*, b.banji_name, y.yuanxi_name, u.username
FROM student s
JOIN user u ON s.user_id = u.id
LEFT JOIN banji b ON s.banji_types = b.id
LEFT JOIN yuanxi y ON b.yuanxi_id = y.id
WHERE s.user_id = %s
""", (user_id,))
if not student_details:
flash('学生信息不存在!', 'error')
return redirect(url_for('student_dashboard'))
student = student_details[0]
return render_template('student/profile.html', student=student)
except Exception as e:
flash(f'加载个人信息失败: {str(e)}', 'error')
return redirect(url_for('student_dashboard'))
@app.route('/student/change_password', methods=['GET', 'POST'])
@login_required(role='student')
def student_change_password():
if request.method == 'POST':
old_password = request.form.get('old_password')
new_password = request.form.get('new_password')
confirm_password = request.form.get('confirm_password')
if new_password != confirm_password:
flash('新密码和确认密码不一致!', 'error')
return redirect(url_for('student_change_password'))
try:
user = execute_query("SELECT * FROM user WHERE id = %s", (session['user_id'],))[0]
if not check_password_hash(user['password'], old_password):
flash('旧密码错误!', 'error')
return redirect(url_for('student_change_password'))
execute_query(
"UPDATE user SET password = %s WHERE id = %s",
(generate_password_hash(new_password), session['user_id']),
fetch=False
)
flash('密码修改成功!', 'success')
return redirect(url_for('student_dashboard'))
except Exception as e:
flash(f'密码修改失败: {str(e)}', 'error')
return render_template('student/change_password.html')
# GPA计算函数(辅助函数)
def calculate_gpa(grades):
total_points = 0
total_credits = 0
for grade in grades:
score = grade['grade_score']
credits = grade['course_xuefen']
# 计算绩点
if score >= 90:
points = 4.0
elif score >= 85:
points = 3.7
elif score >= 82:
points = 3.3
elif score >= 78:
points = 3.0
elif score >= 75:
points = 2.7
elif score >= 72:
points = 2.3
elif score >= 68:
points = 2.0
elif score >= 64:
points = 1.5
elif score >= 60:
points = 1.0
else:
points = 0.0
total_points += points * credits
total_credits += credits
return total_points / total_credits if total_credits > 0 else 0
# ========== 其他功能路由 ==========
@app.route('/init_password')
def init_password():
"""初始化用户密码 - 仅在开发环境使用"""
try:
users = execute_query("SELECT * FROM user")
for user in users:
execute_query(
"UPDATE user SET password = %s WHERE id = %s",
(generate_password_hash('123456'), user['id']),
fetch=False
)
return '密码初始化成功! 所有用户密码已设置为: 123456'
except Exception as e:
return f'密码初始化失败: {str(e)}'
if __name__ == '__main__':
app.run(debug=True, host='0.0.0.0', port=5000)。对应的前端代码均已完成。有缺陷。管理和教师面板没有问题,可以正常跳转。学生面板,登录之后,没有反应,没有跳转