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