def create_rank(**kwargs):
try:
class_id = kwargs['class_id']
except KeyError:
class_name = request.json.get('class_name')
class_id = FzuClass.query.filter(
FzuClass.name == class_name).first().id
all_mark = Mark.query.filter(Mark.class_id == class_id)
subject_name = []
for mark in all_mark.all():
subject_name.append(mark.name)
subject_name = set(subject_name)
for subject in subject_name:
subject_mark = all_mark.filter(Mark.name == subject)
_mark = subject_mark.order_by(-Mark.mark).all()
rank = 1
count = len(_mark)
for mark in _mark:
mark.rank = rank
mark.rank_rate = ((rank - 1) / count) * 100
rank += 1
_mark = subject_mark.order_by(-Mark.average).all()
rank = 1
count = len(_mark)
for mark in _mark:
mark.class_rank = rank
mark.class_rank_rate = ((mark.class_rank - 1) / count) * 100
rank += 1
if not db_commit():
return jsonify({'status': 303}), 303
return jsonify({'status': 200}), 200
def delete():
student_number = request.json.get('student_number')
user = User.query.filter(User.student_number == student_number).first()
class_id = user.class_id
_mark = Mark.query.filter(Mark.user_id == user.id).all()
for mark in _mark:
db.session.delete(mark)
db.session.delete(user)
if not db_commit():
return jsonify({'status': 303}), 303
create_rank(class_id=class_id)
return jsonify({'status': 200}), 200
def set_master(**kwargs):
try:
student_number = kwargs['student_number']
except KeyError:
student_number = request.json.get('student_number')
master_user = User.query.filter(
User.student_number == student_number).first()
if master_user:
master_user.class_master = True
if not db_commit():
return jsonify({'status': 303}), 303
return jsonify({'status': 200}), 200
def change():
student_number = request.json.get('student_number')
subject = request.json.get('subject_name')
grade = request.json.get('mark')
user = User.query.filter(User.student_number == student_number).first()
all_mark = Mark.query.filter(Mark.user_id == user.id)
subject_mark = all_mark.filter(Mark.name == subject).first()
subject_mark.mark = grade
subject_mark.segment = get_segment(grade)
_mark = all_mark.filter(Mark.grade == subject_mark.grade).all()
count = len(_mark)
average = 0
for mark in _mark:
average += mark.mark
for mark in _mark:
mark.average = average / count
if not db_commit():
return jsonify({'status': 303}), 303
create_rank(class_id=user.class_id)
return jsonify({'status': 200}), 200
def delete_by_id(id):
user = User.query.filter(User.id == id).first()
user.delete()
exts.db_commit(user)
return 'success'
def create():
class_name = request.json.get('class_name')
term_begin = request.json.get('term_begin')
term_end = request.json.get('term_end')
class_master = request.json.get('master_number')
grades = [
'大一上学期', '大一下学期', '大二上学期', '大二下学期', '大三上学期', '大三下学期', '大四上学期', '大四下学期'
]
subject_name = []
# build class
new_class = FzuClass.query.filter(FzuClass.name == class_name).first()
if not new_class:
new_class = FzuClass(name=class_name)
db.session.add(new_class)
if not db_commit():
return jsonify({'status': 303}), 303
# build student
data = pd.read_excel('mark.xls', usecols=[0, 1], converters={
0: str
}).values
for (student_number, name) in data:
_user = User.query.filter(
User.student_number == student_number).first()
if _user:
continue
else:
user = User(student_number=student_number,
password=student_number,
name=name)
user.class_id = new_class.id
db.session.add(user)
if not db_commit():
return jsonify({'status': 303}), 303
# build mark
for i in range(term_end - term_begin + 1):
data = pd.read_excel('mark.xls', sheet_name=[i], converters={0:
str})[i]
(r, c) = data.shape
columns = data.columns.values.tolist()
subject_name.extend(columns[2:])
for j in range(r):
user = User.query.filter(
User.student_number == data.iloc[j, 0]).first()
average = float(data.iloc[j, 2:].mean())
for k in range(2, c):
mark = Mark(name=columns[k],
mark=float(data.iloc[j, k]),
user_id=user.id,
class_id=new_class.id)
mark.grade = grades[term_begin + i]
mark.segment = get_segment(mark.mark)
mark.average = average
db.session.add(mark)
for k in range(2, c):
df = data.iloc[:, k]
class_mark = Class_mark(
name=columns[k],
max_s=float(df.max()),
min_s=float(df.min()),
average=float(df.mean()),
grade=grades[term_begin + i],
pass_rate=float(df[df >= 60].count() / len(df) * 100),
good_rate=float(df[df >= 90].count() / len(df) * 100),
class_id=new_class.id)
db.session.add(class_mark)
if not db_commit():
return jsonify({'status': 303}), 303
# build rank
create_rank(class_id=new_class.id)
# build class master
set_master(student_number=class_master)
return jsonify({'status': 200}), 200