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
