def create_teacher():
if request.method == 'POST':
tno = request.form['tno']
tpwd = request.form['tpwd']
tname = request.form['tname']
tsex = request.form['tsex']
ttitle = request.form['ttitle']
tdept = request.form['tdept']
ttel = request.form['ttel']
tmail = request.form['tmail']
try:
validators.Teacher.tno(tno)
validators.Teacher.tpwd(tpwd, True)
validators.Teacher.tname(tname)
validators.Teacher.tsex(tsex)
validators.Teacher.ttitle(ttitle)
validators.Teacher.tdept(tdept)
validators.Teacher.ttel(ttel)
validators.Teacher.tmail(tmail)
db = get_db()
cursor = db.cursor()
cursor.callproc('create_teacher',
(tno, generate_password_hash(tpwd), tname, tsex,
ttitle, tdept, ttel, tmail))
db.commit()
cursor.close()
flash('创建成功！')
except validators.ValidateException as e:
flash(e.info)
return redirect(url_for('admin.create_teacher'))
return render_template('admin/create_teacher.html')
def select_course(cno):
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute('select * from student_course where sno = %s and cno = %s',
(current_user.no, cno))
if cursor.fetchone() is not None:
flash('您已选修过该课程！')
else:
cursor.execute('select ccap, cselect from course where cno = %s',
(cno, ))
item = cursor.fetchone()
if item['cselect'] >= item['ccap']:
flash('课程容量已满！')
else:
cursor.execute(
'select pcno from course_course where cno = %s and pcno not in'
'(select cno from student_course where sno = %s)',
(cno, current_user.no))
if cursor.fetchone() is not None:
flash('先修条件未满足！')
else:
cursor.callproc('select_course', (current_user.no, cno))
db.commit()
flash('选课成功！')
cursor.close()
return redirect(url_for('student.list_unselected_courses'))
def update_course(cno):
db = get_db()
cursor = db.cursor(dictionary=True)
if request.method == 'POST':
cname = request.form['cname']
ctype = request.form['ctype']
ccredit = request.form['ccredit']
cdept = request.form['cdept']
ccap = request.form['ccap']
try:
validators.Course.cname(cname)
validators.Course.ctype(ctype)
validators.Course.ccredit(ccredit)
validators.Course.cdept(cdept)
validators.Course.ccap(ccap)
cursor.callproc('update_course',
(cno, cname, ctype, ccredit, cdept, ccap))
db.commit()
cursor.close()
flash('修改成功！')
except validators.ValidateException as e:
flash(e.info)
return redirect(url_for('admin.update_course', cno=cno))
else:
cursor.execute('select * from course where cno = %s', (cno, ))
course = cursor.fetchone()
cursor.close()
if course is None:
abort(404)
return render_template('admin/update_course.html', course=course)
def create_admin():
if request.method == 'POST':
ano = request.form['ano']
apwd = request.form['apwd']
aname = request.form['aname']
atel = request.form['atel']
amail = request.form['amail']
try:
validators.Admin.ano(ano)
validators.Admin.apwd(apwd)
validators.Admin.aname(aname)
validators.Admin.atel(atel)
validators.Admin.amail(amail)
db = get_db()
cursor = db.cursor()
cursor.callproc(
'create_admin',
(ano, generate_password_hash(apwd), aname, atel, amail))
db.commit()
cursor.close()
flash('创建成功！')
except validators.ValidateException as e:
flash(e.info)
return redirect(url_for('admin.create_admin'))
return render_template('admin/create_admin.html')
def timetable():
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute(
'select course.cno cno, cname, rname, time '
'from course, room, room_course, student_course '
'where course.cno = room_course.cno and room.rno = room_course.rno '
'and room_course.cno = student_course.cno and sno = %s',
(current_user.no, ))
course_rooms = cursor.fetchall()
table = [[[] for j in range(4)] for i in range(5)]
for course_room in course_rooms:
time = course_room['time'].split('-')
dim0 = int(time[0]) - 1
dim1 = int(time[1]) - 1
item = dict()
item['cname'] = course_room['cname']
item['rname'] = course_room['rname']
cursor.execute(
'select tname from teacher, teacher_course '
'where teacher.tno = teacher_course.tno and cno = %s',
(course_room['cno'], ))
entries = cursor.fetchall()
item['tname'] = [entry['tname'] for entry in entries]
table[dim0][dim1].append(item)
cursor.close()
return render_template('student/timetable.html', table=table)
def select_course():
sno = request.form['sno']
cno = request.form['cno']
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute('select * from student_course where sno = %s and cno = %s',
(sno, cno))
if cursor.fetchone() is not None:
flash('该学生已选修过该课程！')
else:
cursor.execute('select * from student where sno = %s', (sno, ))
if cursor.fetchone() is None:
flash('不存在该学生！')
else:
cursor.execute('select ccap, cselect from course where cno = %s',
(cno, ))
item = cursor.fetchone()
if item is None:
flash('不存在该课程！')
elif item['cselect'] >= item['ccap']:
flash('课程容量已满！')
else:
cursor.callproc('select_course', (sno, cno))
db.commit()
flash('选课成功！')
cursor.close()
return redirect(request.referrer or url_for('index'))
def update_textbook(bno):
db = get_db()
cursor = db.cursor(dictionary=True)
if request.method == 'POST':
bname = request.form['bname']
bauthor = request.form['bauthor']
bpress = request.form['bpress']
try:
validators.Textbook.bname(bname)
validators.Textbook.bauthor(bauthor)
validators.Textbook.bpress(bpress)
cursor.callproc('update_textbook', (bno, bname, bauthor, bpress))
db.commit()
cursor.close()
flash('修改成功！')
except validators.ValidateException as e:
flash(e.info)
return redirect(url_for('admin.update_textbook', bno=bno))
else:
cursor.execute('select *' ' from textbook' ' where bno = %s', (bno, ))
textbook = cursor.fetchone()
cursor.close()
if textbook is None:
abort(404)
return render_template('admin/update_textbook.html', textbook=textbook)
def room_to_course(rno):
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute(
'select course.cno cno, cname, time '
'from course, room_course '
'where course.cno = room_course.cno '
'and room_course.rno = %s', (rno, ))
courses = cursor.fetchall()
table = [[[] for j in range(4)] for i in range(5)]
for course in courses:
time = course['time'].split('-')
dim0 = int(time[0]) - 1
dim1 = int(time[1]) - 1
item = dict()
item['cno'] = course['cno']
item['cname'] = course['cname']
cursor.execute(
'select tname from teacher where tno in '
'(select tno from teacher_course where cno = %s)',
(course['cno'], ))
entries = cursor.fetchall()
item['tname'] = [entry['tname'] for entry in entries]
table[dim0][dim1].append(item)
cursor.execute('select rno, rname from room where rno = %s', (rno, ))
room = cursor.fetchone()
cursor.close()
return render_template('admin/room_to_course.html', table=table, room=room)
def create_course():
if request.method == 'POST':
cname = request.form['cname']
ctype = request.form['ctype']
ccredit = request.form['ccredit']
cdept = request.form['cdept']
ccap = request.form['ccap']
try:
validators.Course.cname(cname)
validators.Course.ctype(ctype)
validators.Course.ccredit(ccredit)
validators.Course.cdept(cdept)
validators.Course.ccap(ccap)
db = get_db()
cursor = db.cursor()
cursor.callproc('create_course',
(cname, ctype, ccredit, cdept, ccap))
db.commit()
cursor.close()
flash('创建成功！')
except validators.ValidateException as e:
flash(e.info)
return redirect(url_for('admin.create_course'))
return render_template('admin/create_course.html')
def list_ratings(cno):
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute('select cno, cname from course where cno = %s', (cno, ))
course = cursor.fetchone()
cursor.execute(
'select avg(score) avg_score, count(*) cnt '
'from rating, course '
'where rating.cno = course.cno '
'and rating.cno = %s', (cno, ))
rating_info = cursor.fetchone()
cursor.execute(
'select student.sno sno, sname, score, tags, comment '
'from rating, student '
'where rating.sno = student.sno '
'and rating.cno = %s', (cno, ))
ratings = cursor.fetchall()
for rating in ratings:
tags = rating['tags']
for i in range(1, 7):
target = 'tag' + str(i)
rating[target] = int(tags[i - 1])
cursor.close()
return render_template('admin/list_ratings.html',
course=course,
rating_info=rating_info,
ratings=ratings)
def assign_prev_course():
pcno = request.form['pcno']
cno = request.form['cno']
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute('select * from course_course where pcno = %s and cno = %s',
(pcno, cno))
if cursor.fetchone() is not None:
flash('已存在该先修课程！')
else:
cursor.execute('select * from course where cno = %s', (cno, ))
if cursor.fetchone() is None:
flash('不存在该课程！')
else:
cursor.execute('select * from course where cno = %s', (pcno, ))
if cursor.fetchone() is None:
flash('不存在该课程！')
elif pcno == cno:
flash('不允许设置自己为自己的先修课！')
else:
cursor.callproc('assign_prev_course', (pcno, cno))
db.commit()
flash('先修课程指定成功！')
cursor.close()
return redirect(request.referrer or url_for('index'))
def info_room():
db = get_db()
cursor = db.cursor(dictionary=True)
if request.method == 'POST':
str_select = ''
for key in request.form.keys():
value = request.form[key]
if key == 'rno':
if len(value) > 0:
if not str.isdigit(value):
flash('教室号只能为数字！')
return redirect(url_for('admin.info_room'))
str_select += key + '=' + value + ' and '
else:
if type(value) == str:
if len(value) > 0:
str_select += key + ' LIKE \'%' + value + '%\' and '
else:
abort(500)
if len(str_select) == 0:
return redirect(url_for('admin.info_room'))
sql = 'select *' \
' from room where {} order by rno'.format(str_select[:-4])
cursor.execute(sql)
else:
cursor.execute('select * from room order by rno')
rooms = cursor.fetchall()
cursor.close()
return render_template('admin/info_room.html', rooms=rooms)
def update_room(rno):
db = get_db()
cursor = db.cursor(dictionary=True)
if request.method == 'POST':
rname = request.form['rname']
rcap = request.form['rcap']
try:
validators.Room.rname(rname)
validators.Room.rcap(rcap)
cursor.callproc('update_room', (rno, rname, rcap))
db.commit()
cursor.close()
flash('修改成功！')
except validators.ValidateException as e:
flash(e.info)
return redirect(url_for('admin.update_room', rno=rno))
else:
cursor.execute('select *' ' from room' ' where rno = %s', (rno, ))
room = cursor.fetchone()
cursor.close()
if room is None:
abort(404)
return render_template('admin/update_room.html', room=room)
def create_textbook():
if request.method == 'POST':
bno = request.form['bno']
bname = request.form['bname']
bauthor = request.form['bauthor']
bpress = request.form['bpress']
try:
validators.Textbook.bno(bno)
validators.Textbook.bname(bname)
validators.Textbook.bauthor(bauthor)
validators.Textbook.bpress(bpress)
db = get_db()
cursor = db.cursor()
cursor.callproc('create_textbook', (bno, bname, bauthor, bpress))
db.commit()
cursor.close()
flash('创建成功！')
except validators.ValidateException as e:
flash(e.info)
return redirect(url_for('admin.create_textbook'))
return render_template('admin/create_textbook.html')
def info_stu():
db = get_db()
cursor = db.cursor(dictionary=True)
if request.method == 'POST':
str_select = ''
for key in request.form.keys():
value = request.form[key]
if type(value) == str:
if len(value) > 0:
str_select += key + ' LIKE \'%' + value + '%\' and '
else:
abort(500)
if len(str_select) == 0:
return redirect(url_for('admin.info_stu'))
sql = 'select sno, sname, ssex, sid, sgrade, sdept, stel, smail' \
' from student where {} order by sno'.format(str_select[:-4])
cursor.execute(sql)
else:
cursor.execute(
'select sno, sname, ssex, sid, sgrade, sdept, stel, smail'
' from student'
' order by sno')
students = cursor.fetchall()
cursor.close()
return render_template('admin/info_stu.html', students=students)
def delete_stu(sno):
db = get_db()
cursor = db.cursor()
cursor.callproc('delete_student', (sno, ))
db.commit()
cursor.close()
flash('删除成功！')
return redirect(url_for('admin.info_stu'))
def delete_room(rno):
db = get_db()
cursor = db.cursor()
cursor.callproc('delete_room', (rno, ))
db.commit()
cursor.close()
flash('删除成功！')
return redirect(url_for('admin.info_room'))
def delete_textbook(bno):
db = get_db()
cursor = db.cursor()
cursor.callproc('delete_textbook', (bno, ))
db.commit()
cursor.close()
flash('删除成功！')
return redirect(url_for('admin.info_textbook'))
def list_untaught_courses():
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute(
'select * from course where cno not in '
'(select cno from teacher_course where tno = %s) '
'order by cno', (current_user.no, ))
courses = cursor.fetchall()
cursor.close()
return render_template('teacher/list_untaught_courses.html',
courses=courses)
def list_taught_courses():
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute(
'select course.cno cno, cname, ctype, ccredit, cdept, ccap, cselect '
'from course, teacher_course '
'where course.cno = teacher_course.cno and tno = %s '
'order by cno', (current_user.no, ))
courses = cursor.fetchall()
cursor.close()
return render_template('teacher/list_taught_courses.html', courses=courses)
def delete_admin(ano):
if current_user.no == ano:
flash('不能删除当前用户！')
return redirect(url_for('admin.info_admin'))
db = get_db()
cursor = db.cursor()
cursor.callproc('delete_admin', (ano, ))
db.commit()
cursor.close()
flash('删除成功！')
return redirect(url_for('admin.info_admin'))
def unteach_course(cno):
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute('select * from teacher_course where tno = %s and cno = %s',
(current_user.no, cno))
if cursor.fetchone() is None:
flash('您并未教授该课程！')
else:
cursor.callproc('unteach_course', (current_user.no, cno))
db.commit()
flash('取消教授课程成功！')
cursor.close()
return redirect(url_for('teacher.list_taught_courses'))
def sid(input_str, cur_sno=None):
if not str.isdigit(input_str) or len(input_str) != 18:
raise ValidateException("身份证号不合法！")
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute('select *'
' from student'
' where sid = %s', (input_str, ))
student = cursor.fetchone()
cursor.close()
if student is not None and student['sno'] != cur_sno:
raise ValidateException("身份证号不能重复！")
def unrate_course(cno):
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute('select * from rating where sno = %s and cno = %s',
(current_user.no, cno))
item = cursor.fetchone()
if item is None:
flash('您并未评价过该课程！')
else:
cursor.callproc('unrate_course', (current_user.no, cno))
flash('删除评价成功！')
db.commit()
cursor.close()
return redirect(url_for('student.list_ratings', cno=cno))
def update_stu():
db = get_db()
cursor = db.cursor(dictionary=True)
if request.method == 'POST':
spwd = request.form['spwd']
sname = request.form['sname']
ssex = request.form['ssex']
sid = request.form['sid']
sgrade = request.form['sgrade']
sdept = request.form['sdept']
stel = request.form['stel']
smail = request.form['smail']
try:
validators.Student.sname(sname)
validators.Student.spwd(spwd, True)
validators.Student.ssex(ssex)
validators.Student.sid(sid, cur_sno=current_user.no)
validators.Student.sgrade(sgrade)
validators.Student.sdept(sdept)
validators.Student.stel(stel)
validators.Student.smail(smail)
cursor.callproc('update_student',
(current_user.no, sname, ssex, sid, sgrade, sdept,
stel, smail))
if len(spwd) > 0:
cursor.callproc(
'update_student_pwd',
(current_user.no, generate_password_hash(spwd)))
db.commit()
cursor.close()
flash('修改成功！')
except validators.ValidateException as e:
flash(e.info)
return redirect(url_for('student.update_stu', sno=current_user.no))
else:
cursor.execute(
'select sno, sname, ssex, sid, sgrade, sdept, stel, smail'
' from student'
' where sno = %s', (current_user.no, ))
student = cursor.fetchone()
cursor.close()
if student is None:
abort(404)
return render_template('student/update_stu.html', student=student)
def unassign_textbook():
bno = request.form['bno']
cno = request.form['cno']
db = get_db()
cursor = db.cursor()
cursor.execute('select * from textbook_course where bno = %s and cno = %s',
(bno, cno))
if cursor.fetchone() is None:
flash('该教材并未被该课程指定！')
else:
cursor.callproc('unassign_textbook', (bno, cno))
db.commit()
flash('取消指定教材成功！')
cursor.close()
return redirect(request.referrer or url_for('index'))
def textbook_to_course(bno):
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute(
'select course.cno cno, cname, ctype, ccredit, cdept, ccap, cselect '
'from course, textbook_course '
'where course.cno = textbook_course.cno and bno = %s '
'order by cno', (bno, ))
courses = cursor.fetchall()
cursor.execute('select bno, bname from textbook where bno = %s', (bno, ))
textbook = cursor.fetchone()
cursor.close()
return render_template('admin/textbook_to_course.html',
courses=courses,
textbook=textbook)
def unteach_course():
tno = request.form['tno']
cno = request.form['cno']
db = get_db()
cursor = db.cursor()
cursor.execute('select * from teacher_course where tno = %s and cno = %s',
(tno, cno))
if cursor.fetchone() is None:
flash('该老师并未教授该课程！')
else:
cursor.callproc('unteach_course', (tno, cno))
db.commit()
flash('取消教授课程成功！')
cursor.close()
return redirect(request.referrer or url_for('index'))
def course_to_textbook(cno):
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute(
'select textbook.bno bno, bname, bauthor, bpress '
'from textbook, textbook_course '
'where textbook.bno = textbook_course.bno and cno = %s '
'order by bno', (cno, ))
textbooks = cursor.fetchall()
cursor.execute('select cno, cname from course where cno = %s', (cno, ))
course = cursor.fetchone()
cursor.close()
return render_template('admin/course_to_textbook.html',
textbooks=textbooks,
course=course)
def course_to_teacher(cno):
db = get_db()
cursor = db.cursor(dictionary=True)
cursor.execute(
'select teacher.tno tno, tname, tsex, ttitle, tdept, ttel, tmail '
'from teacher, teacher_course '
'where teacher.tno = teacher_course.tno and cno = %s '
'order by tno', (cno, ))
teachers = cursor.fetchall()
cursor.execute('select cno, cname from course where cno = %s', (cno, ))
course = cursor.fetchone()
cursor.close()
return render_template('admin/course_to_teacher.html',
teachers=teachers,
course=course)
