async def fill_grades1(message: Message, state: FSMContext):
from urllib import request
import config
try:
file_info = await bot.get_file(message.document.file_id)
journal = file_info.file_path
file_name = message.document.file_name
request.urlretrieve(
f'https://api.telegram.org/file/bot{config.API_TOKEN}/{journal}',
f'./{file_name}')
df = pd.read_excel(r"Журнал.xlsx")
del df['Фамилия']
del df['Имя']
del df['Группа']
del df['Номер зачётной книжки']
for date in df.columns:
grades = []
for i in df[date]:
grades.append(i)
cid = db.get_cid_by_tid(message.from_user.id)
list_of_students = db.get_list_of_students(cid)
for i in range(0, len(list_of_students)):
try:
if math.isnan(grades[i]):
db.assign_grades(list_of_students[i][0], cid, date, 0)
else:
db.assign_grades(list_of_students[i][0], cid, date,
grades[i])
except:
pass
await message.answer('Оценки выставлены!')
except:
await message.answer('Не удалось найти файл с оценками')
print(traceback.format_exc())
async def fill_grades1(message: Message, state: FSMContext):
from urllib import request
import config
try:
file_info = await bot.get_file(message.document.file_id)
journal = file_info.file_path
file_name = message.document.file_name
request.urlretrieve(f'https://api.telegram.org/file/bot{config.API_TOKEN}/{journal}',f'./{file_name}')
df = pd.read_excel(r"Журнал.xlsx")
del df['Фамилия']
del df['Имя']
del df['Группа']
del df['Номер зачётной книжки']
del df['Unnamed: 0']
print(df.columns)
for date in df.columns:
grades = []
for i in df[date]:
grades.append(i)
cid = db.get_cid_by_tid(message.from_user.id)
cur_stud_id_in_journal = db.get_sid_from_journal(cid)
sids = [el[0] for el in cur_stud_id_in_journal]
list_of_students = db.get_list_of_students(cid)
if date in db.get_dates(cid):
for i in range(0, len(list_of_students)):
if list_of_students[i][0] in sids:
try:
if math.isnan(grades[i]):
db.assign_grades(list_of_students[i][0], cid, date, 0)
else:
db.assign_grades(list_of_students[i][0], cid, date, grades[i])
except:
pass
else:
try:
if math.isnan(grades[i]):
db.add_to_journal(list_of_students[i][0], cid, date, 0,' ')
else:
db.add_to_journal(list_of_students[i][0], cid, date, grades[i],' ')
except:
pass
else:
for i in range(0, len(list_of_students)):
try:
if math.isnan(grades[i]):
db.add_to_journal(list_of_students[i][0], cid, date, 0, ' ')
else:
db.add_to_journal(list_of_students[i][0], cid, date, grades[i], ' ')
except:
pass
await message.answer('Оценки выставлены!',reply_markup=ReplyKeyboardRemove())
await state.finish()
return
except:
await message.answer('Не удалось найти файл с оценками')
print(traceback.format_exc())
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 update_attendence1(message: Message, state: FSMContext):
try:
cid = db.get_cid_by_tid(message.from_user.id)
list_of_students = db.get_list_of_students(cid)
answer = message.text
df = pd.read_excel(r"Посещаемость.xlsx")
attendence = []
for i in df[answer]:
if math.isnan(i):
attendence.append(" ")
else:
attendence.append(i)
for i in range(0, len(list_of_students)):
try:
db.assign_attendence1(list_of_students[i][0], cid, answer, attendence[i])
except:
pass
await message.answer('Посещаемость выставлена!')
except:
await message.answer('Не удалось найти файл с посещаемостью')
await state.finish()
async def change_grades1(message: Message, state: FSMContext):
try:
cid = db.get_cid_by_tid(message.from_user.id)
list_of_students = db.get_list_of_students(cid)
answer = message.text
for student in list_of_students:
db.add_couple(student[0],cid,answer)
df = pd.read_excel(r"Оценки.xlsx")
grades = []
for grade in df[answer]:
if math.isnan(grade):
grades.append(0)
else:
grades.append(int(grade))
for i in range(0, len(list_of_students)):
try:
db.assign_grades(list_of_students[i][0], cid, answer, grades[i])
except:
pass
await message.answer('Оценки выставлены!')
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)
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_attendence_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 = []
attendence = []
check = False
for i in journal:
dates.append(i[2])
for student in students:
attendence_stud = []
names.append(student[1])
surnames.append(student[2])
groups.append(student[4])
zach_books.append(student[3])
for j in db.get_attendece(student[0], cid):
attendence_stud.append(j)
attendence.append(attendence_stud)
if attendence_stud:
check = True
amounts_attendence = []
for a in attendence:
amounts_attendence.append(len(a))
df = pd.DataFrame({
'Фамилия': surnames,
'Имя': names,
'Группа': groups,
'Номер зачётной книжки': zach_books
})
if check:
max_amount_attendence = max(amounts_attendence)
dates = list(set(dates))
dates.sort()
for a in attendence:
if len(a) < max_amount_attendence:
while len(a) < max_amount_attendence:
a.insert(len(a), 0)
df_attendence = pd.DataFrame(columns=dates)
#print(len(grades),len(df_grades),len(df_grades.columns))
j = 0
#print(grades)
for col in df_attendence.columns:
at = []
for i in range(len(students)):
at.append(attendence[i][j])
df_attendence[col] = np.array(at)
#print(col)
j += 1
answer = pd.merge(df,
df_attendence,
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