def get_current_task_id():
with connection.cursor() as cursor:
query = "SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'homeschooldb' AND TABLE_NAME = 'task';"
cursor.execute(query)
result = cursor.fetchall()
print(result)
return result[0]['AUTO_INCREMENT']
def update_student_task_is_done(task_id, is_done, username):
with connection.cursor() as cursor:
query = "update student_task " \
"set student_task.is_done = {} " \
"where student_task.task_id = {} and student_task.name_ = '{}' ".format(is_done, task_id,username)
cursor.execute(query)
connection.commit()
def is_student(username):
with connection.cursor() as cursor:
query = f"select name_ from student where name_ = '{username}';"
cursor.execute(query)
res = cursor.fetchone()
if res:
return True
return False
def insert_student_task(class_, task_id):
students = get_all_students(class_)
with connection.cursor() as cursor:
for student in students:
query = f"insert into student_task values('{student['name_']}', {task_id}, 0)"
cursor.execute(query)
connection.commit()
print("insert students")
def insert_task(grade, day, hour, date, descr):
curr_id = get_current_task_id()
with connection.cursor() as cursor:
query = f"insert into task values (Null, '{grade}', {day}, '{hour}', '{date}', '{descr}');"
cursor.execute(query)
connection.commit()
print("inserted")
insert_student_task(grade, curr_id)
def insert_lesson_to_schedule(grade, day, hour, subject, zoom_link=None):
with connection.cursor() as cursor:
subject_key = get_key_subject_by_name(subject)
print(subject_key)
query = f"insert into week_schedule values ('{grade}', {day}, '{hour}', {subject_key});"
print(query)
cursor.execute(query)
connection.commit()
# insert_lesson_to_schedule("first", 1, '17:00:00', 'Math')
# print(get_week_schedule_by_class('first'))
def get_student_tasks_by_name(name):
today = date.today()
yesterday = date.today() - timedelta(days=1)
with connection.cursor() as cursor:
query = "select id, homework, date(date_) as date_ from task join student_task "\
"on student_task.task_id = task.id " \
"where student_task.name_ = '{}' " \
"and task.date_ <= curdate() and student_task.is_done = 0 " \
"order by date_ desc ".format(name)
cursor.execute(query)
tasks = cursor.fetchall()
for task in tasks:
if task['date_'] == today:
task['date_'] = 'today'
elif task['date_'] == yesterday:
task['date_'] = 'yesterday'
return tasks
def insert_fun_task(grade, descr, link):
with connection.cursor() as cursor:
query = f"insert into fun_task values ('{grade}','{descr}', '{link}');"
cursor.execute(query)
connection.commit()
print("inserted fun task")
def get_all_students(class_):
with connection.cursor() as cursor:
query = f"select name_ from student where class = '{class_}'"
cursor.execute(query)
students = cursor.fetchall()
return students
def get_tasks_by_class_and_date(class_, date):
with connection.cursor() as cursor:
query = "select homework from lesson where class = '{}' and date = {}".format(
class_, date)
cursor.execute(query)
tasks = cursor.fetchall()
def get_fun_tasks_by_grade(grade):
with connection.cursor() as cursor:
query = f"select descr, link from fun_task where class = '{grade}';"
cursor.execute(query)
res = cursor.fetchall()
return res
def get_student_info(name):
with connection.cursor() as cursor:
query = f"select * from student where name_ = '{name}';"
cursor.execute(query)
res = cursor.fetchall()
return res[0]
def get_teacher_email_by_name(name):
with connection.cursor() as cursor:
query = f"select email from teacher where name_ = '{name}';"
cursor.execute(query)
res = cursor.fetchall()
return res[0]['email']
def get_lessons_by_day_and_grade(grade, day):
with connection.cursor() as cursor:
query = f"select hour, teacher, name_, zoom_link from week_schedule , subject_ where class = '{grade}' and day_ = {day} and subject_id = id order by hour;"
cursor.execute(query)
res = cursor.fetchall()
return res
def get_key_subject_by_name(subject):
with connection.cursor() as cursor:
query = f"select id from subject_ where name_ = '{subject}';"
cursor.execute(query)
res = cursor.fetchall()
return res[0]['id']
def get_class_by_student(student_name):
with connection.cursor() as cursor:
query = f"select class from student where name_ = '{student_name}';"
cursor.execute(query)
res = cursor.fetchone()
return res['class']
