def reviewGrade(cid):
if request.method == 'POST':
radio = request.form['radio']
if radio == '√':
db1 = get_db()
cur1 = db1.cursor()
cur1.execute(
'update studentCourse set scoreReviewStatus=%s where cid=%s',
('审核完毕', cid))
db1.commit()
cur1.execute(
'select email from user where id in (select sid from studentCourse where cid = %s)',
(cid))
emails = get_results(cur1)
email_list = []
for email in emails:
if email['email'] is not '':
email_list.append(email['email'])
cur1.execute('select cname from course where cid = %s', (cid))
mesg = '同学你所选的课程——{}的课程成绩已经审核完毕，请查看'
send_email(mesg.format(get_results(cur1)[0]['cname']), email_list)
else:
feedback = request.form['feedback']
db2 = get_db()
cur2 = db2.cursor()
cur2.execute(
'update studentCourse set scoreReviewStatus=%s where cid=%s',
(feedback, cid))
db = get_db()
cur = db.cursor()
cur.execute(
'select sid ,name,dailyScore,finalExamScore,score,scoreReviewStatus from student,studentCourse where cid=%s and sid=student.id',
(cid))
scores = get_results(cur)
return render_template('info/reviewGrade.html', scores=scores)
def setPercent(cid):
tid = session['id']
db = get_db()
cur = db.cursor()
cur.execute(
'SELECT sid,name,school,major FROM student,studentCourse WHERE student.id=studentCourse.sid and cid = %s and tid=%s',
(cid, tid))
students = get_results(cur)
if request.method == 'POST':
tid = session['id']
dailyScoreRatio = request.form['dailyScoreRatio']
a = 100 - int(dailyScoreRatio)
dailyScoreRatioDesc = "平时成绩(" + dailyScoreRatio + "%)，期末成绩(" + str(
a) + "%)"
db = get_db()
db.cursor().execute(
'update course set dailyScoreRatio=%s,dailyScoreRatioDesc=%s where cid=%s',
(dailyScoreRatio, dailyScoreRatioDesc, cid))
db.commit()
return redirect(url_for('info.importScore', cid=cid))
db1 = get_db()
cur1 = db1.cursor()
cur1.execute('select distinct dailyScoreRatio from course where cid=%s',
(cid))
per = get_results(cur1)
db1.commit()
return redirect(url_for('info.importScore', cid=cid))
def courseAnalysis(cid):
db = get_db()
cur = db.cursor()
fail = course_fail(cid)
count = course_count2(cid)
cur.execute(
'SELECT id, name, dailyScore, finalExamScore, score FROM studentCourse JOIN student ON id = sid WHERE cid = %s',
(cid))
students = get_results(cur)
cur.execute('SELECT cid,cname FROM course WHERE cid = %s', (cid))
course = get_results(cur)
cur.execute(
'SELECT avg(score), max(score), min(score) FROM studentCourse JOIN student ON id = sid WHERE cid = %s',
(cid))
score = get_results(cur)
db.commit()
return render_template('info/teacher2.html',
students=students,
course=course,
score=score,
fail=fail,
count=count)
def index():
id = session['id']
db = get_db()
cur = db.cursor()
if request.method == 'POST':
coursetype = request.form['coursetype']
courseyear = request.form['courseyear']
courseterm = request.form['courseterm']
cur.execute(
'SELECT coursetype, cname, tname, courseyear, courseterm, coursepoint, score, gpa '
'FROM studentCourse sc JOIN course c where sc.cid = c.cid and '
'coursetype = %s and courseyear = %s and courseterm = %s and sid=%s',
(coursetype, courseyear, courseterm, id))
courselist = get_results(cur)
return render_template('info/index.html', courses=courselist)
cur.execute(
'SELECT coursetype, cname, tname, courseyear, courseterm, coursepoint, score, gpa '
'FROM studentCourse sc JOIN course c where sc.cid = c.cid and '
'sid=%s', (id))
courselist = get_results(cur)
if len(courselist) is 0:
abort(404, "Student id {0} doesn't have Course score.".format(id))
total_rank = {}
total_rank['avg_coursetype'] = avg_coursetype(id)
total_rank['total_point'] = total_point(id)
total_rank['total_avg_gpa'] = total_avg_gpa(id)
return render_template('info/index.html',
courses=courselist,
scores=total_rank)
def worst_subject(sid):
db = get_db()
cur = db.cursor()
i = cur.execute('select score, gpa, course.cid cid, course.cname cname from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score <= 70 order by gpa limit 3', (sid))
if i != 0:
worse_subject = get_results(cur)
else:
cur.execute('select score, gpa, course.cid cid, course.cname cname from course, studentCourse where sid = %s and course.cid = studentCourse.cid order by gpa limit 1', (sid))
worse_subject = get_results(cur)
return worse_subject
def score_distribution(sid):
db = get_db()
cur = db.cursor()
cur.execute('select count(*) 小于60 from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score < 60', (sid))
score_distribution = get_results(cur)
cur.execute('select count(*) 60至70 from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score >= 60 and score < 70', (sid))
score_distribution.extend(get_results(cur))
cur.execute('select count(*) 70至80 from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score >= 70 and score < 80', (sid))
score_distribution.extend(get_results(cur))
cur.execute('select count(*) 80至90 from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score >= 80 and score < 90', (sid))
score_distribution.extend(get_results(cur))
cur.execute('select count(*) 90至100 from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score >= 90', (sid))
score_distribution.extend(get_results(cur))
return score_distribution
def exportScoreList(cid):
db = get_db()
cur = db.cursor()
sio = BytesIO()
workbook = xlsxwriter.Workbook(
sio, {'in_memory': True}) # xlwt.Workbook(encoding='ascii') # 写到IO中
cur.execute('select * from course where cid = %s' % cid)
course = get_results(cur)[0]
filename = '%s_%s_%s' % (course['cname'], course['courseyear'],
course['courseterm'])
worksheet = workbook.add_worksheet(name=filename)
# worksheet.merge_range(0, 0, 0, 5, 'aaa') # 合并单元格
# worksheet.write(11, 2, '=SUM(1:10)') # 增加公式
# worksheet.set_default_row(35) # 设置默认行高
style1 = workbook.add_format({
'font_size': '11',
'align': 'center',
'valign': 'vcenter',
'bold': True
}) # 设置风格 'bg_color': '#34A0FF',
style2 = workbook.add_format({
'font_size': '11',
'align': 'center',
'valign': 'vcenter',
'bold': False
}) # 'font_color': '#217346'
worksheet.set_column('A:H', None, style2)
worksheet.set_column(0, 7, 20) # 设置列宽
title = ['学号', '姓名', '学院', '专业', '平时成绩', '期末成绩', '最终成绩', '成绩状态']
worksheet.write_row('A1', title, style1)
cur.execute(
'select sid, name, school, major, dailyScore, finalExamScore, score, studentExamStatus from student, studentCourse '
'where sid = id and cid = %s', (cid))
scores = get_results(cur)
i = 0
for score in scores:
data = [
score['sid'], score['name'], score['school'], score['major'],
score['dailyScore'], score['finalExamScore'], score['score'],
score['studentExamStatus']
]
worksheet.write_row('A' + str(i + 2), data)
i = i + 1
workbook.close()
sio.seek(0) # 将byte流再从头读取，之前已经写到最后一个byte了
resp = sio.getvalue() # 通过getvalue函数读取IO流
sio.close() # 关闭IO流
orderdata = [resp, filename.encode().decode('latin1')]
return orderdata
def course_count(cid):
db = get_db()
cur = db.cursor()
cur.execute('select count(*) count from studentCourse where cid = %s',
(cid))
course_count = get_results(cur)
return course_count
def course_avg(cid):
db = get_db()
cur = db.cursor()
cur.execute('select avg(score) avg from studentCourse where cid = %s',
(cid))
course_avg = get_results(cur)
return course_avg
def showProposal():
db = get_db()
cur = db.cursor()
id = g.user['id']
cur.execute('select DISTINCT cname,raisedTime,score, reason, reply,is_checked_by_teacher, is_checked_by_dean from proposal, course,studentCourse where course.cid = studentCourse.cid and proposal.cid=course.cid and proposal.sid = studentCourse.sid and proposal.sid = %s', id)
proposals = get_results(cur)
return render_template('info/showProposal.html', proposals = proposals)
def login():
"""Log in a registered user by adding the user id to the session."""
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
cur = db.cursor()
error = None
cur.execute(
'SELECT * FROM user WHERE username = %s', (username,)
)
#user = cur.fetchone()
user = get_results(cur)
if len(user) is 0:
error = 'Incorrect username.'
elif not check_password_hash(user[0]['password'], password):
error = 'Incorrect password.'
if error is None:
# store the user id in a new session and return to the index
session.clear()
session['id'] = user[0]['id']
session['username'] = user[0]['username']
session['auth'] = user[0]['auth']
return redirect(url_for('info.index'))
flash(error)
return render_template('auth/login.html')
def showProposal():
db = get_db()
cur = db.cursor()
id = g.user['id']
cur.execute('select * from proposal where sid = %s', id)
proposals = get_results(cur)
return render_template('info/showProposal.html', proposals=proposals)
def proposal():
db = get_db()
cur = db.cursor()
cur.execute(
'SELECT distinct course.cid, cname'
' FROM studentCourse JOIN course'
' WHERE sid = %s', (g.user['id']))
courses = get_results(cur)
db.commit()
if request.method == 'POST':
cid = request.form['course']
reason = request.form['reason']
error = None
if not cid:
error = 'Course name is required.'
elif not reason:
error = 'Reason is required'
if error is not None:
flash(error)
else:
db = get_db()
cur = db.cursor()
cur.execute(
'INSERT INTO proposal(sid, cid, reason)'
'values(%s, %s, %s)', (g.user['id'], cid, reason))
db.commit()
flash('成功提交！')
return redirect(url_for('info.showProposal'))
return render_template('info/proposal.html', courses=courses)
def total_point(sid):
db = get_db()
cur = db.cursor()
cur.execute('select sum(coursepoint) totalpoint from course, studentCourse where sid = %s and course.cid = studentCourse.cid', (sid))
total_point = get_results(cur)
point = total_point
return point
def scoreMain(cid):
id = session['id']
db = get_db()
cur = db.cursor()
cur.execute(
'SELECT sid,name,school,major,dailyScore,finalExamScore,score,studentExamStatus FROM student,studentCourse WHERE student.id=studentCourse.sid and cid = %s and tid=%s',
(cid, id))
courses = get_results(cur)
cur.execute(
'select courseyear,courseterm,cname,dailyScoreRatioDesc from course where cid = %s',
(cid))
info = get_results(cur)
return render_template('info/scoreMain.html',
courses=courses,
cid=cid,
info=info)
def create():
if request.method == 'POST':
cname = request.form['cname']
score = request.form['score']
gpa = request.form['gpa']
error = None
if not cname:
error = 'Course name is required.'
elif not score:
error = 'Score is required'
if error is not None:
flash(error)
else:
db = get_db()
cur = db.cursor()
cur.execute('SELECT * FROM course WHERE cname = %s ', (cname))
course = get_results(cur)
if len(course) is 0:
error = 'Course do not exist'
flash(error)
db.execute(
'INSERT INTO studentCourse (sid, cid, score, gpa)'
' VALUES ( %s, %s, %s, %s)',
(g.user['id'], course['cid'], score, gpa))
db.commit()
return redirect(url_for('info.index'))
return render_template('info/create.html')
def total_avg_gpa(sid):
db = get_db()
cur = db.cursor()
cur.execute(
'select sum(sc)/sum(coursepoint) avggpa from (select gpa*coursepoint sc, coursepoint from course, studentCourse where sid = %s and course.cid = studentCourse.cid) as s',
(sid))
total_avg_gpa = get_results(cur)
return total_avg_gpa
def student_rank(cid, sid):
db = get_db()
cur = db.cursor()
cur.execute(
'select rank() over(order by score desc) rnk from (select * from studentCourse where cid = %s) as s where sid = %s',
(cid, sid))
student_rank = get_results(cur)
return student_rank
def course_info(cid):
db = get_db()
cur = db.cursor()
cur.execute(
'select avg(score) avg, max(score) max, count(score > 85 or null)/ count(*) good from studentCourse where cid = %s',
(cid))
course_info = get_results(cur)
return course_info
def course_fail(cid): db = get_db() cur = db.cursor() cur.execute( 'select round((count(score<60 or null)) /count(score) *100 , 2) fail from studentCourse JOIN student ON id = sid where cid = %s',(cid) ) course_fail = get_results(cur) return course_fail
def courseclass_gpa_rank(sid):
db = get_db()
cur = db.cursor()
cur.execute(
'select ANY_VALUE(sum(gpa*coursepoint)/sum( coursepoint)) gp, ANY_VALUE(sum(coursepoint)) ttpoint, courseclass from course, studentCourse where sid = %s and course.cid = studentCourse.cid group by courseclass',
(sid))
courseclass_gpa_rank = get_results(cur)
return courseclass_gpa_rank
def term_avg_gpa(sid):
db = get_db()
cur = db.cursor()
cur.execute(
'select ANY_VALUE(sum(gpa*coursepoint)/sum( coursepoint)) gp,courseyear,courseterm from course, studentCourse where sid = %s and course.cid = studentCourse.cid group by courseyear,courseterm',
(sid))
term_avg_gpa = get_results(cur)
return term_avg_gpa
def avg_coursetype(sid):
db = get_db()
cur = db.cursor()
cur.execute(
'select avg(gpa) gpa, coursetype, sum(coursepoint) coursepoint from course, studentCourse where sid = %s and course.cid = studentCourse.cid group by coursetype',
(sid))
avg_coursetype = get_results(cur)
return avg_coursetype
def load_logged_in_user():
id = session.get('id')
if id is None:
g.user = None
else:
cur = get_db().cursor()
cur.execute('SELECT * FROM user WHERE id = %s', (id))
g.user = get_results(cur)[0]
def course_involve(sid):
db = get_db()
cur = db.cursor()
cur.execute(
'select course.cid, cname from course, studentCourse where sid = %s and studentCourse.cid = course.cid',
(sid))
course_involve = get_results(cur)
return course_involve
def reviewGrade(cid):
db = get_db()
cur = db.cursor()
cur.execute(
'select sid ,name,dailyScore,finalExamScore,score,status from student,studentCourse where cid=%s and sid=student.id',
(cid))
scores = get_results(cur)
return render_template('info/reviewGrade.html', scores=scores)
def review():
db = get_db()
cur = db.cursor()
cur.execute(
'select cid,cname,coursetype,coursepoint,courseyear,courseterm,coursevolume,name from course,teacher'
)
courses = get_results(cur)
return render_template('info/review.html', courses=courses)
def updateScore():
id=session['id']
db =get_db()
cur = db.cursor()
cur.execute('select cid,cname,coursetype,coursepoint,coursevolume from course,teacher '
'where teacher.id=%s and teacher.name=course.tname',(id))
courses = get_results(cur)
return render_template('info/updateScore.html', courses=courses)
def updateScore():
id = session['id']
db = get_db()
cur = db.cursor()
cur.execute(
'select distinct course.cid,cname,coursetype,coursepoint,courseyear,courseterm,coursevolume,dailyScoreRatioDesc,scoreType,scoreReviewStatus from course,teacher,studentCourse where teacher.id=%s and teacher.id=course.tid and teacher.id=studentCourse.tid and studentCourse.cid=course.cid',
(id))
courses = get_results(cur)
return render_template('info/updateScore.html', courses=courses)
def seeScore(cid):
tid = session['id']
db = get_db()
cur1 = db.cursor()
cur1.execute(
'select sid ,name,dailyScore,finalExamScore,score,scoreReviewStatus from student,studentCourse where cid=%s and sid=student.id',
(cid))
scores = get_results(cur1)
db.commit()
return render_template('info/seeScore.html', scores=scores)
