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 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 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 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 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 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 seeScore(cid):
tid = session['id']
db = get_db()
cur = db.cursor()
cur.execute('update studentCourse set score = (dailyScore*dailyScoreRatio/100+finalExamScore*(100-dailyScoreRatio)/100) where cid=%s',(cid))
cur1 = db.cursor()
cur1.execute('select sid ,name,dailyScore,finalExamScore,score,status 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)
def importScore(cid):
tid = session['id']
CourseId = cid
if request.method=="POST":
db = get_db()
cur = db.cursor()
cur.execute('select sid,name from studentCourse,student where cid=%s and sid=student.id',(cid))
Students=get_results(cur)
for student in Students:
dailyScore = request.form[str(student['sid'])]
finalExamScore = request.form[student['name']]
db.cursor().execute('update studentCourse set dailyScore=%s,finalExamScore=%s where sid=%s and cid=%s',(dailyScore,finalExamScore,student['sid'],cid))
db.commit()
return redirect(url_for('info.seeScore',cid=CourseId))
db = get_db()
cur = db.cursor()
cur.execute('select sid ,name,dailyScore,finalExamScore from student,studentCourse where cid=%s and sid=student.id', (cid))
students=get_results(cur)
return render_template('info/importScore.html', students=students,cid=cid)
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)
def courseterm_rank(sid): db = get_db() cur = db.cursor() sql = ''' select totalrank, courseterm from ( select sid, courseterm, row_number() over(PARTITION by courseterm order by avggpa desc) totalrank from ( select sum(sc)/sum(coursepoint) avggpa, courseterm, sid from ( select gpa*coursepoint sc, coursepoint, courseterm, sid from course, studentCourse where course.cid = studentCourse.cid) as s group by courseterm, sid) as s) as s where sid = %s ''' cur.execute(sql, (sid)) courseterm_rank = get_results(cur) return courseterm_rank
def index(check_author=True):
id = session['id']
db = get_db()
cur = db.cursor()
cur.execute(
'SELECT coursetype, cname, tname, courseyear, courseterm, coursepoint, score, gpa'
' FROM studentCourse JOIN course'
' WHERE sid = %s', (id))
courselist = get_results(cur)
if courselist is None:
abort(404, "Student id {0} doesn't have Course score.".format(id))
return render_template('info/index.html', courses=courselist)
def reviewProposal(cid, sid):
db = get_db()
cur = db.cursor()
if g.user['auth'] == 1:
cur.execute(
'update proposal set is_checked_by_teacher = 1 where cid = %s and sid = %s',
(cid, sid))
elif g.user['auth'] == 2:
cur.execute(
'update proposal set is_checked_by_dean = 1 where cid = %s and sid = %s',
(cid, sid))
db.commit()
return redirect(url_for('info.showProposal'))
def myScore():
sid = session['sid']
cate = request.form['cate']
term = request.form['term']
scores = get_db().execute(
'select courseName, score, GPA, render, entryStatus from Performances, Courses where studentNo=?'
'and Performances.courseNo in (select courseNo from Courses where courseCate=? and courseTerm=?) and Performances.courseNo=Courses.courseNo', (sid, cate, term)).fetchall()
if scores is None:
abort(404, "Student id {0} doesn't have selected score.".format(sid))
results = []
for x in scores:
results.append({'courseName': x[0], 'score': x[1], 'GPA': x[2], 'rank': x[3], 'entryStatus': x[4]})
return render_template('info/myScore.html', scores=results)
def load_logged_in_user():
"""If a user id is stored in the session, load the user object from
the database into ``g.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 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 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 selectAna():
courses = {}
sid = session['id']
db = get_db()
cur = db.cursor()
cc = ""
cy = ""
ccd = ""
cyd = ""
if request.method == 'POST':
courseclass = request.form.getlist('coursetype')
courseyear = request.form.getlist('courseyear')
cc = "' or ".join(("courseclass = '" + str(n) for n in courseclass))
cy = "' or ".join(("courseyear = '" + str(n) for n in courseyear))
cc = cc + "'"
cy = cy + "'"
query = 'CREATE TEMPORARY TABLE scores select sid,ROUND(sum(sc)/sum(coursepoint),2) as avggpa from (select gpa*coursepoint sc, coursepoint,sid from course, studentCourse where course.cid = studentCourse.cid and (' + cc + ') and (' + cy + ')) as s GROUP BY sid '
cur.execute(query)
query1 = 'CREATE TEMPORARY TABLE scores1 select sid,ROUND(sum(sc)/sum(coursepoint),2) as avggpa from (select gpa*coursepoint sc, coursepoint,sid from course, studentCourse where course.cid = studentCourse.cid and (' + cc + ') and (' + cy + ')) as s GROUP BY sid '
cur.execute(query1)
query2 = 'SELECT avggpa, FIND_IN_SET( avggpa, (SELECT GROUP_CONCAT( avggpa ORDER BY avggpa DESC ) FROM scores )) as rk FROM scores1 where sid = %s'
cur.execute(query2, sid)
courses = get_results(cur)
for i in courseclass:
if i == 'gx':
ccd = ccd + " 公选 "
elif i == 'gb':
ccd = ccd + " 公必 "
elif i == 'zb':
ccd = ccd + " 专必 "
elif i == 'zx':
ccd = ccd + " 专选 "
for i in courseyear:
if i == '2018':
cyd = cyd + " 2018 "
elif i == '2019':
cyd = cyd + " 2019 "
elif i == '2017':
cyd = cyd + " 2017 "
return render_template('info/selectAna.html',
courses=courses,
cc=ccd,
cy=cyd)
def index():
id = session['id']
db = get_db()
cur = db.cursor()
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)
courseClass= {}
courseClass['gx'] = courseclass_gpa_rank(id)[1]
courseClass['gb'] = courseclass_gpa_rank(id)[2]
courseClass['zx'] = courseclass_gpa_rank(id)[0]
courseClass['zb'] = courseclass_gpa_rank(id)[3]
sql = 'SELECT coursetype, cname, tname, courseyear, courseterm, coursepoint, score, gpa,`status` st '
sql += 'FROM studentCourse sc JOIN course c where sc.cid = c.cid and '
sql += 'sid = %s' % id
if request.method == 'POST':
type = request.form['coursetype']
year = request.form['courseyear']
term = request.form['courseterm']
if type == u'' and year == u'' and term == u'':
pass
if type is not u'':
sql += ' and coursetype = %s' % type
if year is not u'':
sql += ' and courseyear = %s' % year
if term is not u'':
sql += ' and courseterm = %s' % term
cur.execute(sql)
courselist = get_results(cur)
return render_template('info/index.html', courses=courselist, scores=total_rank, cc=courseClass)
cur.execute(sql)
courselist = get_results(cur)
if len(courselist) is 0:
abort(404, "Student id {0} doesn't have Course score.".format(id))
courseClass= {}
courseClass['gx'] = courseclass_gpa_rank(id)[1]
courseClass['gb'] = courseclass_gpa_rank(id)[2]
courseClass['zx'] = courseclass_gpa_rank(id)[0]
courseClass['zb'] = courseclass_gpa_rank(id)[3]
return render_template('info/index.html', courses=courselist, scores=total_rank,cc=courseClass)
def showProposal():
db = get_db()
cur = db.cursor()
id = g.user['id']
if g.user['auth'] == 0:
cur.execute('select distinct * '
'from proposal p join course c '
'where p.cid = c.cid '
'and p.sid = %s' % id)
elif g.user['auth'] == 1:
cur.execute(
'select distinct * from proposal p join course c join student s '
'where p.cid = c.cid and s.id = p.sid and p.cid in (select distinct cid from course where tid = %s)',
id)
else:
cur.execute(
'select distinct * from proposal p join course c join student s '
'where p.cid = c.cid and s.id = p.sid')
proposals = get_results(cur)
return render_template('info/showProposal.html', proposals=proposals)
def update(id):
"""Update a post if the current user is the author."""
post = get_post(id)
if request.method == 'POST':
title = request.form['title']
body = request.form['body']
error = None
if not title:
error = 'Title is required.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute('UPDATE post SET title = ?, body = ? WHERE id = ?',
(title, body, id))
db.commit()
return redirect(url_for('blog.index'))
return render_template('blog/update.html', post=post)
def createCourse():
if request.method == 'POST':
cname = request.form['cname']
courseterm = request.form['courseterm']
coursepoint = request.form['coursepoint']
coursetype = request.form['coursetype']
coursevolume = request.form['coursevolume']
courseyear = request.form['courseyear']
courseclass = request.form['courseclass']
dailyScoreRatio = request.form['dailyScoreRatio']
dailyScoreRatioDesc = request.form['dailyScoreRatioDesc']
tid = g.user['id']
db = get_db()
cur = db.cursor()
cur.execute(
'INSERT INTO course(cname, courseterm, courseyear, coursepoint, coursetype, coursevolume, tid, dailyScoreRatio, dailyScoreRatioDesc, courseclass) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
(cname, courseterm, courseyear, coursepoint, coursetype,
coursevolume, tid, dailyScoreRatio, dailyScoreRatioDesc,
courseclass))
db.commit()
return redirect(url_for('info.index'))
return render_template('info/createCourse.html')
def register():
"""Register a new user.
Validates that the username is not already taken. Hashes the
password for security.
Use cursor to execute SQL query.
"""
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
id = request.form['id']
#sname = request.form['sname']
auth = request.form['auth']
if auth == 0:
tablename = 'student'
else:
tablename = 'teacher'
is_male = request.form['is_male']
#school = request.form['school']
#enrollyear = request.form['enrollyear']
db = get_db()
cur = db.cursor()
error = None
if cur.execute('SELECT id FROM user WHERE username = %s',
(username)) is not 0:
error = 'NetID {0} is already registered.'.format(username)
if error is None:
cur.execute(
'INSERT INTO user (id, username, password, auth, is_male) VALUES (%s, %s, %s, %s, %s)',
(id, username, generate_password_hash(password), auth,
is_male))
db.commit()
return redirect(url_for('auth.login'))
flash(error)
return render_template('auth/register.html')
def course_score(cid): db = get_db() cur = db.cursor() cur.execute( 'select count(*) 小于60 from studentCourse JOIN student ON id = sid where cid = %s and score < 60', (cid)) score_distribution = get_results(cur) cur.execute( 'select count(*) 60至70 from studentCourse JOIN student ON id = sid where cid = %s and score >= 60 and score < 70', (cid)) score_distribution.extend(get_results(cur)) cur.execute( 'select count(*) 70至80 from studentCourse JOIN student ON id = sid where cid = %s and score >= 70 and score < 80', (cid)) score_distribution.extend(get_results(cur)) cur.execute( 'select count(*) 80至90 from studentCourse JOIN student ON id = sid where cid = %s and score >= 80 and score < 90', (cid)) score_distribution.extend(get_results(cur)) cur.execute( 'select count(*) 90至100 from studentCourse JOIN student ON id = sid where cid = %s and score >= 90', (cid)) score_distribution.extend(get_results(cur)) return score_distribution
def importScore(cid):
id = session['id']
db1 = get_db()
cur1 = db1.cursor()
cur1.execute('select distinct scoreType from studentCourse where cid=%s',
(cid))
type = get_results(cur1)
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, id))
students = get_results(cur)
cur.execute('select dailyScoreRatio from course where cid =%s', cid)
per = get_results(cur)
if type[0]['scoreType'] == '百分制':
if request.method == "POST":
db = get_db()
cur = db.cursor()
cur.execute(
'select sid,name from studentCourse,student where cid=%s and sid=student.id',
(cid))
Students1 = get_results(cur)
for student in Students1:
str1 = '%s%s' % (
str(student['sid']), student['name']
) #用+会坑，详见https://blog.csdn.net/zyz511919766/article/details/22072701
StudentExamStatus = request.form[str1]
if (StudentExamStatus == '正常'): #disabled之后请求不了dailyScore
dailyScore = request.form[str(student['sid'])]
finalExamScore = request.form[student['name']]
cur.execute(
'select dailyScoreRatio from course where cid = %s' %
cid)
ratio = int(get_results(cur)[0]['dailyScoreRatio'])
if (dailyScore == ''):
dailyScore = None
else:
dailyScore = int(dailyScore)
if (finalExamScore == ''):
finalExamScore = None
else:
finalExamScore = int(finalExamScore)
if (finalExamScore != None and dailyScore != None):
score = (dailyScore * ratio +
(100 - ratio) * finalExamScore) / 100
else:
score = None
db.cursor().execute(
'update studentCourse set dailyScore=%s,finalExamScore=%s,StudentExamStatus=%s,score=%s where sid=%s and cid=%s',
(dailyScore, finalExamScore, StudentExamStatus, score,
student['sid'], cid))
else:
dailyScore = None
finalExamScore = None
score = None
db.cursor().execute(
'update studentCourse set dailyScore=%s,finalExamScore=%s,StudentExamStatus=%s,score=%s where sid=%s and cid=%s',
(dailyScore, finalExamScore, StudentExamStatus, score,
student['sid'], cid))
db.commit()
return redirect(url_for('info.scoreMain', cid=cid))
#cur.execute('SELECT sid,name,school,major FROM student,studentCourse WHERE student.id=studentCourse.sid and cid = %s and tid=%s',(cid,id))
cur.execute(
'SELECT * FROM student,studentCourse WHERE student.id=studentCourse.sid and cid = %s and tid=%s',
(cid, id))
students = get_results(cur)
return render_template('info/importScore.html',
students=students,
cid=cid,
per=per)
else:
if request.method == "POST":
db = get_db()
cur = db.cursor()
cur.execute(
'select sid,name from studentCourse,student where cid=%s and sid=student.id',
(cid))
Students1 = get_results(cur)
dailyScore = None
finalExamScore = None
for student in Students1:
level = request.form['level']
str1 = '%s%s' % (
str(student['sid']), student['name']
) #用+会坑，详见https://blog.csdn.net/zyz511919766/article/details/22072701
StudentExamStatus = request.form['status']
if level == '优秀':
score = 95
elif level == '良好':
score = 85
elif level == '一般':
score = 75
elif level == '合格':
score = 65
elif level == '不合格':
score = 55
else:
score = None #空键处理
db.cursor().execute(
'update studentCourse set dailyScore=%s,finalExamScore=%s,score=%s,StudentExamStatus=%s where sid=%s and cid=%s',
(dailyScore, finalExamScore, score, StudentExamStatus,
student['sid'], cid))
db.commit()
return redirect(url_for('info.scoreMain', cid=cid))
return render_template('info/importScore0.html',
students=students,
cid=cid,
per=per) #需要等级制页面吗
def myAnalysis():
sid = session['sid']
analysis = get_db().execute(
'select courseTerm, avg(score), max(score), min(score) from Performances, Courses where Performances.courseNo=Courses.courseNo'
)
def index():
if session['auth'] == 2:
id = session['id']
db = get_db()
cur = db.cursor()
sql = 'select distinct course.cid,cname,dailyScoreRatioDesc,coursepoint,courseyear,courseterm,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
if request.method == 'POST':
year = request.form['courseyear']
term = request.form['courseterm']
if year == u'' and term == u'':
pass
if year is not u'':
sql += ' and courseyear = %s' % year
if term is not u'':
sql += ' and courseterm = %s' % term
cur.execute(sql)
courses = get_results(cur)
return render_template('info/index3.html', courses=courses)
cur.execute(sql)
courses = get_results(cur)
return render_template('info/index3.html', courses=courses)
if session['auth'] == 1:
id = session['id']
db = get_db()
cur = db.cursor()
sql = 'select distinct course.cid,cname,dailyScoreRatioDesc,coursepoint,courseyear,courseterm,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
if request.method == 'POST':
year = request.form['courseyear']
term = request.form['courseterm']
if year == u'' and term == u'':
pass
if year is not u'':
sql += ' and courseyear = %s' % year
if term is not u'':
sql += ' and courseterm = %s' % term
cur.execute(sql)
courses = get_results(cur)
return render_template('info/index2.html', courses=courses)
cur.execute(sql)
courses = get_results(cur)
return render_template('info/index2.html', courses=courses)
id = session['id']
db = get_db()
cur = db.cursor()
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)
courseClass = {}
for i in range(len(courseclass_gpa_rank(id))):
ctype = courseclass_gpa_rank(id)[i]['courseclass']
courseClass[ctype] = courseclass_gpa_rank(id)[i]
sql = 'SELECT coursetype, cname, t.name, courseyear, courseterm, coursepoint, dailyScore, finalExamScore, score, gpa,`scoreReviewStatus` st, studentExamStatus '
sql += 'FROM studentCourse sc JOIN course c JOIN teacher t where sc.tid = t.id and sc.cid = c.cid and '
sql += 'sid = %s' % id
if request.method == 'POST':
type = request.form['coursetype']
year = request.form['courseyear']
term = request.form['courseterm']
if type == u'' and year == u'' and term == u'':
pass
if type is not u'':
sql += ' and coursetype = \'%s\'' % type
if year is not u'':
sql += ' and courseyear = %s' % year
if term is not u'':
sql += ' and courseterm = %s' % term
#cur.execute(sql)
#courselist = get_results(cur)
#return render_template('info/index.html', courses=courselist, scores=total_rank, cc=courseClass)
cur.execute(sql)
courselist = get_results(cur)
if len(courselist) is 0:
abort(404, "Student id {0} doesn't have Course score.".format(id))
courseClass = {}
for i in range(len(courseclass_gpa_rank(id))):
ctype = courseclass_gpa_rank(id)[i]['courseclass']
courseClass[ctype] = courseclass_gpa_rank(id)[i]
sql = 'SELECT distinct c.cid, cname, t.name,courseyear,scoreReviewStatus FROM studentCourse sc JOIN course c JOIN teacher t where sc.tid = t.id and sc.cid = c.cid and '
sql += 'sid = %s order by c.cid desc limit 3' % id
cur.execute(sql)
notices = get_results(cur)
return render_template('info/index.html',
courses=courselist,
scores=total_rank,
cc=courseClass,
notices=notices)
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 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
