def add_submission(cls, student_idx, assignment_idx, link, comment, group): """ New submission made by student. :param student_idx: string :param assignment_idx: string :param link: string :param comment: str :return: None """ date_of_submission = datetime.date.today() if group == 0: members = [student_idx] else: members = cls.get_team_users(student_idx) if not members: members = [student_idx] for mem in members: query = "INSERT INTO `Sumbissions` (ID_STUDENT, ID_ASSIGMENT, GRADE, DATE, LINK, ID_MENTOR)" \ "VALUES (?, ?, ?, ?, ?, ?);" values_list = [ mem, assignment_idx, -1, date_of_submission, link, 0 ] sql.query(query, values_list) query_3 = "SELECT max(id) as max_id FROM Sumbissions;" sub_id = sql.query(query_3)[0][0] query_3 = "INSERT INTO `comments` (sub_id, comment) VALUES (?, ?);" param = [sub_id, comment] sql.query(query_3, param)
def save_sql(cls, data): """ Save data to sql :param data: list (FORMAT : NAME, SURNAME, E-MAIL, TELEPHONE, PASSWORD) :return: """ query = """ INSERT INTO Users (Name, Surname, `E-mail`, Telephone, Password, Type) VALUES (?, ?, ?, ?, ?, 'Manager')""" sql.query(query, data)
def update_sql(edit_list): """ :param edit_list: (FORMAT: E-MAIL, ATTRIBUTE, NEW VALUE) :return: """ query = """ UPDATE Users SET `{}` = ? WHERE `E-mail` = ? AND Type = 'Manager'""".format(edit_list[1]) sql.query(query, [edit_list[2], edit_list[0]])
def make_checkpoint(mentor1, mentor2, student, checkpoint_id, grade): today = datetime.date.today() query = "INSERT INTO Users_checkpoints " \ "(ID_CHECKPOINT, DATE, GRADE, ID_STUDENT, ID_MENTOR_1, ID_MENTOR_2)" \ " VALUES (?, ?, ?, ?, ?, ?)" params = [ checkpoint_id, today, grade, int(student), int(mentor1), int(mentor2) ] sql.query(query, params)
def select_mentors_not_in_checkpoint(checkpoint_id): query = "SELECT * FROM Users WHERE `TYPE` = 'Mentor' AND `ID` NOT IN (SELECT ID_USER FROM Checkpoints WHERE ID={})".format( checkpoint_id) sql_result = sql.query(query) return sql_result
def show_statistics_for_mentor_checkpoints(id_mentor): query = "SELECT * FROM Checkpoints WHERE ID_USER = {} ORDER BY START_DATE ASC".format( id_mentor, id_mentor) sql_query_result = sql.query(query) return sql_query_result
def show_statistics_for_mentor_cards(id_mentor): query = "SELECT *, COUNT(GRADE) as cards FROM Users_checkpoints WHERE ID_MENTOR_1 = {} OR ID_MENTOR_2 = {} " \ "GROUP BY grade".format(id_mentor, id_mentor) sql_query_result = sql.query(query) return sql_query_result
def get_students_without_checkpoint(checkpoint_id): """ :param checkpoint_id: :return: list_of_students """ query = "SELECT * FROM Users WHERE ID NOT IN (SELECT ID_STUDENT FROM Users_checkpoints WHERE ID_CHECKPOINT = {}) AND Type='Student'".format( checkpoint_id) students = sql.query(query) return students
def my_attendance(idx): query = 'SELECT STATUS, COUNT(STATUS) AS count FROM `Attendance` WHERE ID_STUDENT=? GROUP BY STATUS' values = [int(idx)] back_values = sql.query(query, values) to_print = [] all_days = 0 average = 0 if back_values: for row in back_values: to_print.append([row[0], row[1]]) if row[0] == 'Present': average += 1 elif row[0] == 'Late': average += 0.75 all_days += row[1] return int(average * 100 / all_days) else: pass
def ave_grade_flask_version(idx): """ Count avg grade of students :param idx: index of studentSELECT * FROM Users WHERE ID NOT IN (SELECT ID_STUDENT FROM Users_checkpoints WHERE ID_CHECKPOINT = 1) AND Type='Student' :return average grade (int): """ query = """ SELECT U.ID, Name, Surname, AVG(GRADE) FROM Users as U LEFT Join Sumbissions as S ON U.ID = S.ID_Student where Type = 'Student' GROUP BY Name;""" sql_data = sql.query(query) for row in sql_data: student_id = row[0] if int(student_id) == int(idx): if row[3]: return int(row[3]) else: return
def show_checkpoint_results(checkpoint_id): if checkpoint_id != 0: query = "SELECT TITLE, ID_CHECKPOINT, DATE, GRADE, student.Name as student_name, " \ "student.Surname as student_surname , mentor.Name as mentor_name, " \ "mentor.Surname as mentor_surname , mentor2.Name as mentor2_name, " \ "mentor2.Surname as mentor2_surname " \ "FROM Users_checkpoints LEFT JOIN Users student ON Users_checkpoints.ID_STUDENT = student.ID " \ "LEFT JOIN Users mentor ON Users_checkpoints.ID_MENTOR_1 = mentor.ID " \ "LEFT JOIN Users mentor2 ON Users_checkpoints.ID_MENTOR_2 = mentor2.ID " \ "LEFT JOIN Checkpoints ON Users_checkpoints.ID_CHECKPOINT = Checkpoints.ID " \ "WHERE ID_CHECKPOINT = {}".format(checkpoint_id) table = [] sql_query_result = sql.query(query) if isinstance(sql_query_result, list): for checkpoint in sql_query_result: table.append({ 'student': checkpoint['student_name'] + ' ' + checkpoint['student_surname'], 'mentor1': checkpoint['mentor_name'] + ' ' + checkpoint['mentor_surname'], 'mentor2': checkpoint['mentor2_name'] + ' ' + checkpoint['mentor2_surname'], 'date': checkpoint['DATE'], 'grade': checkpoint['grade'] }) return table
def show_checkpoints(): query = "SELECT * FROM Checkpoints, Users WHERE Checkpoints.ID_USER = Users.ID" sql_query_result = sql.query(query) return sql_query_result