async def answer_q1(message: Message, state: FSMContext): course_name = message.text try: cid = db.get_id_course_by_name(course_name) score = db.get_grades(message.from_user.id, cid) await message.answer("Ваши оценки по курсу: " + str(score), reply_markup=ReplyKeyboardRemove()) except: await message.answer("Произошла непредвиденная ошибка") await state.finish()
async def answer_q1(message: Message, state: FSMContext): try: answer = message.text cid = db.get_id_course_by_name(answer) list_of_students = db.get_list_of_students(cid) worst_students = "" for student in list_of_students: if sum(db.get_grades(student[0], cid)) < 56: worst_students = worst_students + f"{student[1]} {student[2]} : {sum(db.get_grades(student[0], cid))}\n" if len(list_of_students) == 0: await message.answer("Нет студентов на данном курсе", reply_markup=ReplyKeyboardRemove()) else: await message.answer(worst_students, reply_markup=ReplyKeyboardRemove()) except: await message.answer("Произошла непредвиденная ошибка", reply_markup=ReplyKeyboardRemove()) await state.finish()
async def course_corr1(message: Message, state: FSMContext): try: answer = message.text cid = db.get_id_course_by_name(answer) list_of_students = db.get_list_of_students(cid) n = len(list_of_students) grades_list = [] attendence_list = [] ages_list = [] couples_list = [] for student in list_of_students: grades_list.append(sum(db.get_grades(student[0], cid))) couples_list = db.get_attendence(student[0], cid) couples_sum = 0 ages_list.append(db.get_age(student[0])) for couple in couples_list: if couple == "Н" or couple == "У": pass else: couples_sum += 1 attendence_list.append(couples_sum) grades_list = pd.Series(grades_list) attendence_list = pd.Series(attendence_list) ages_list = pd.Series(ages_list) if n == 0: await message.answer("Нет студентов на данном курсе", reply_markup=ReplyKeyboardRemove()) elif len(couples_list) == 0: await message.answer("Занятий ещё не проводилось", reply_markup=ReplyKeyboardRemove()) else: await message.answer( "Коэффециент корреляции между оценками и посещаемость: " + str(grades_list.corr(attendence_list)) + "Коэффециент корреляции между оценками и возрастом: " + str(grades_list.corr(ages_list)) + "Коэффециент корреляции между посещаемостью и возрастом: " + str(attendence_list.corr(ages_list)), reply_markup=ReplyKeyboardRemove()) except: await message.answer("Произошла непредвиденная ошибка", reply_markup=ReplyKeyboardRemove()) await state.finish()
async def answer_q1(message: Message, state: FSMContext): try: answer = message.text cid = db.get_id_course_by_name(answer) list_of_students = db.get_list_of_students(cid) n = len(list_of_students) grades_sum = 0 ages_sum = 0 couples_sum = 0 fin_couples = 0 for student in list_of_students: stud_couple = 0 grades_sum += sum(db.get_grades(student[0], cid)) ages_sum += db.get_age(student[0]) for couple in db.get_attendence(student[0], cid): if couple == "Н" or couple == "У": pass else: couples_sum += 1 stud_couple += 1 fin_couples = max(fin_couples, stud_couple) if n == 0: await message.answer("Нет студентов на данном курсе") elif fin_couples == 0: await message.answer("Занятий ещё не проводилось" + "Средний возраст студентов: " + str(ages_sum / n)) else: await message.answer("Средний балл за курс: " + str(grades_sum / n) + "\nСредняя посещаемость: " + str(couples_sum / (n * fin_couples) * 100) + "%" + "\nСредний возраст студентов: " + str(ages_sum / n)) except: await message.answer("Произошла непредвиденная ошибка") await state.finish()
async def get_journal(message: Message): try: tid = message.from_user.id cid = db.get_cid_by_tid(tid) students = db.get_list_of_students(cid) journal = db.get_from_journal(cid) dates = [] names = [] surnames = [] groups = [] zach_books = [] grades = [] check = False for i in journal: dates.append(i[2]) for student in students: grades_stud = [] names.append(student[1]) surnames.append(student[2]) groups.append(student[4]) zach_books.append(student[3]) for j in db.get_grades(student[0], cid): grades_stud.append(j) grades.append(grades_stud) if grades_stud: check = True amounts_grades = [] for g in grades: amounts_grades.append(len(g)) df = pd.DataFrame({ 'Фамилия': surnames, 'Имя': names, 'Группа': groups, 'Номер зачётной книжки': zach_books }) if check: max_amount_grades = max(amounts_grades) dates = list(set(dates)) dates.sort() for grade in grades: if len(grade) < max_amount_grades: while len(grade) < max_amount_grades: grade.insert(len(grade), 0) df_grades = pd.DataFrame(columns=dates) #print(len(grades),len(df_grades),len(df_grades.columns)) j = 0 #print(grades) for col in df_grades.columns: g = [] for i in range(len(students)): g.append(grades[i][j]) df_grades[col] = np.array(g) #print(col) j += 1 answer = pd.merge(df, df_grades, left_index=True, right_index=True) answer.to_excel(r"Журнал.xlsx") else: answer = df answer.to_excel(r"Журнал.xlsx") await message.answer( f"Журнал для курса {db.get_name_course_by_id(cid)}", reply_markup=ReplyKeyboardRemove()) with open("Журнал.xlsx", 'rb') as file: await dp.bot.send_document(message.from_user.id, file) except: await message.answer( 'Вы не являетесь администратором ни одного из курсов', reply_markup=ReplyKeyboardRemove()) print(traceback.format_exc()) return