def get(self, table, directoryId, className):
if table not in studentTablesIgnorePopulate:
return errors.NoTable
if isUnique('directoryId', directoryId, 'people', db):
return errors.NoPerson()
if isUnique('className', className, 'classes', db):
return errors.NoClass()
response = db.session.execute('SELECT * FROM people WHERE directoryId = :d;', {'d':directoryId})
result = [dict(r) for r in response]
personId = result[0]['personId']
if isUnique('personId', personId, 'students', db):
return errors.NoStudent()
response = db.session.execute('SELECT studentId FROM students t1 JOIN people t2 ON t1.personId = t2.personId WHERE directoryId = :directoryId', {'directoryId' : directoryId})
result = [dict(r) for r in response]
studentId = result[0]['studentId']
response = db.session.execute('SELECT classId FROM classes WHERE className = :className;', {'className' : className})
result = [dict(r) for r in response]
classId = result[0]['classId']
response = db.session.execute('SELECT * FROM {} WHERE classId = :cid AND studentId = :sid;'.format(table), {'cid':classId, 'sid':studentId})
if response is None:
return errors.NoStudent()
return make_response(jsonify([dict(r) for r in response]), 200)
def delete(self, uid, name):
if not uid.isnumeric():
return errors.NotNumeric()
if isUnique('uid', uid, 'students', db):
return errors.NoStudent()
if isUnique('name', name, 'groups', db):
return errors.NoGroup()
student = db.session.execute(
"""SELECT * FROM student_teams WHERE
studentId=(SELECT studentId FROM students WHERE uid=:uid) AND
groupId=(SELECT groupId FROM groups WHERE name=:n)""",
{
'uid': uid,
"n": name
}).fetchone()
if student is None:
return errors.NoStudent()
else:
studentId = student['studentId']
db.session.execute(
"""DELETE FROM student_teams WHERE
studentId=(SELECT studentId FROM students WHERE uid=:uid) AND
groupId=(SELECT groupId FROM groups WHERE name=:n)""",
{
'uid': uid,
"n": name
})
db.session.commit()
return make_response('', 204)
def get(self, directoryId):
if isUnique('directoryId', directoryId, 'people', db):
return errors.NoPerson()
response = db.session.execute('SELECT * FROM people WHERE directoryId = :d;', {'d':directoryId})
result = [dict(r) for r in response]
personId = result[0]['personId']
if isUnique('personId', personId, 'students', db):
return errors.NoStudent()
response = db.session.execute('SELECT studentId FROM students t1 JOIN people t2 ON t1.personId = t2.personId WHERE directoryId = :directoryId', {'directoryId':directoryId})
result = [dict(r) for r in response]
studentId = result[0]['studentId']
response = db.session.execute('''
SELECT groups.groupId, groups.name, classes.className, groups.watch, groups.groupHealth, groups.groupScore FROM groups
JOIN student_teams ON groups.groupId = student_teams.groupId
JOIN classes ON groups.classId = classes.classId
WHERE student_teams.studentId = :sid;
''', {
'sid':studentId
})
return make_response(json.dumps([dict(r) for r in response]), 200)
def delete(self, directoryId, name):
if isUnique('directoryId', directoryId, 'people', db):
return errors.NoPerson()
if isUnique('name', name, 'groups', db):
return errors.NoGroup()
response = db.session.execute(
'SELECT * FROM people WHERE directoryId = :d;', {'d': directoryId})
result = [dict(r) for r in response]
personId = result[0]['personId']
if isUnique('personId', personId, 'students', db):
return errors.NoStudent()
student = db.session.execute(
"""SELECT studentId FROM student_teams WHERE
studentId=(SELECT studentId FROM students WHERE personId=(SELECT personId from people WHERE directoryId=:directoryId)) AND
groupId=(SELECT groupId FROM groups WHERE name=:n)""",
{
'directoryId': directoryId,
"n": name
}).fetchone()
if student is None:
return errors.NoStudent()
else:
studentId = student['studentId']
db.session.execute(
"""DELETE FROM student_teams WHERE
studentId=(SELECT studentId FROM students WHERE personId=(SELECT personId from people WHERE directoryId=:directoryId)) AND
groupId=(SELECT groupId FROM groups WHERE name=:n)""",
{
'directoryId': directoryId,
"n": name
})
db.session.commit()
return make_response('', 204)
def get(self, uid):
if not uid.isnumeric():
return errors.NotNumeric()
if isUnique('uid', uid, 'students', db):
return errors.NoStudent()
# Get student with given UID as a JOIN on Person and Student
response = db.session.execute(
'SELECT studentId, firstName, lastName, directoryId, uid FROM people t1 JOIN students t2 ON t1.personId = t2.personID WHERE t2.uid = :uid',
{'uid': uid})
result = [dict(r) for r in response]
return make_response(json.dumps(result[0]), 200)
def get(self, table, uid, className):
if not uid.isnumeric():
return errors.NotNumeric()
if isUnique('uid', uid, 'students', db):
return errors.NoStudent()
if table not in studentTablesIgnorePopulate:
return errors.NoTable()
if isUnique('className', className, 'classes', db):
return errors.NoClass()
response = db.session.execute('SELECT * FROM students WHERE uid = :uid;', {'uid' : uid})
result = [dict(r) for r in response]
studentId = result[0]['studentId']
response = db.session.execute('SELECT classId FROM classes WHERE className = :className;', {'className' : className})
result = [dict(r) for r in response]
classId = result[0]['classId']
response = db.session.execute('SELECT * FROM {} WHERE classId = :cid AND studentId = :sid;'.format(table), {'cid':classId, 'sid':studentId})
if response is None:
return errors.NoStudent()
return make_response(jsonify([dict(r) for r in response]), 200)
def get(self, directoryId):
if isUnique('directoryId', directoryId, 'people', db):
return errors.NoPerson()
# Get student with given UID as a JOIN on Person and Student
response = db.session.execute(
'SELECT * FROM people WHERE directoryId = :d;', {'d': directoryId})
result = [dict(r) for r in response]
personId = result[0]['personId']
if isUnique('personId', personId, 'students', db):
return errors.NoStudent()
response = db.session.execute(
'SELECT studentId, firstName, lastName, directoryId, uid FROM people t1 JOIN students t2 ON t1.personId = t2.personID WHERE t1.directoryId = :d;',
{'d': directoryId})
result = [dict(r) for r in response]
return make_response(json.dumps(result[0]), 200)
def get(self, uid):
if not uid.isnumeric():
return errors.NotNumeric()
if isUnique('uid', uid, 'students', db):
return errors.NoStudent()
response = db.session.execute('SELECT studentId FROM students WHERE uid = :uid', {'uid':uid})
result = [dict(r) for r in response]
studentId = result[0]['studentId']
response = db.session.execute('''
SELECT groups.groupId, groups.name, classes.className, groups.watch, groups.groupHealth groups.groupScore FROM groups
JOIN student_teams ON groups.groupId = student_teams.groupId
JOIN classes ON groups.classId = classes.classId
WHERE student_teams.studentId = :sid;
''', {
'sid':studentId
})
return make_response(json.dumps([dict(r) for r in response]), 200)
def delete(self, uid):
if not uid.isnumeric():
return errors.NotNumeric()
if isUnique('uid', uid, 'students', db):
return errors.NoStudent()
resp = db.session.execute('SELECT * FROM students WHERE uid=:uid;',
{'uid': uid})
result = [dict(r) for r in resp]
# Must get personId from person table to query student table
query = '''
DELETE FROM student_teams WHERE studentId=(SELECT studentId FROM students WHERE uid=:uid);
DELETE FROM elmsData WHERE studentId=(SELECT studentId FROM students WHERE uid=:uid);
DELETE FROM answers WHERE studentId=(SELECT studentId FROM students WHERE uid=:uid);
DELETE FROM students WHERE uid=:uid; '''
db.session.execute(query, {'uid': uid})
db.session.commit()
return make_response('', 204)
def get(self, directoryId):
if isUnique('directoryId', directoryId, 'people', db):
return errors.NoPerson()
response = db.session.execute(
'SELECT * FROM people WHERE directoryId = :d;', {'d': directoryId})
result = [dict(r) for r in response]
personId = result[0]['personId']
if isUnique('personId', personId, 'students', db):
return errors.NoStudent()
query = """
SELECT className FROM classes
JOIN groups ON classes.classId = groups.classId
JOIN student_teams ON groups.groupId = student_teams.groupId
JOIN students ON student_teams.studentId = students.studentId
WHERE students.personId = :p;
"""
response = db.session.execute(query, {"p": personId})
return make_response(json.dumps([dict(r) for r in response]), 200)
def delete(self, directoryId):
if isUnique('directoryId', directoryId, 'people', db):
return errors.NoPerson()
response = db.session.execute(
'SELECT * FROM people WHERE directoryId = :d;', {'d': directoryId})
result = [dict(r) for r in response]
personId = result[0]['personId']
if isUnique('personId', personId, 'students', db):
return errors.NoStudent()
query = '''DELETE FROM student_teams WHERE studentId=(SELECT studentId FROM students WHERE personId=(SELECT personId FROM people WHERE directoryId=:did));
DELETE FROM elmsData WHERE studentId=(SELECT studentId FROM students WHERE personId=(SELECT personId FROM people WHERE directoryId=:did));
DELETE FROM answers WHERE studentId=(SELECT studentId FROM students WHERE personId=(SELECT personId FROM people WHERE directoryId=:did));
DELETE FROM students WHERE personId=(SELECT personId from people WHERE directoryId=:did);'''
db.session.execute(query, {'did': directoryId})
db.session.commit()
return make_response('', 204)
def post(self, directoryId, name):
if isUnique('directoryId', directoryId, 'people', db):
return errors.NoPerson()
if isUnique('name', name, 'groups', db):
return errors.NoGroup()
response = db.session.execute(
'SELECT * FROM people WHERE directoryId = :d;', {'d': directoryId})
result = [dict(r) for r in response]
personId = result[0]['personId']
if isUnique('personId', personId, 'students', db):
return errors.NoStudent()
getStudentGroupQuery = '''SELECT * FROM student_teams WHERE
studentId=(SELECT studentId FROM students WHERE personId=(SELECT personId from people WHERE directoryId=:directoryId)) AND
groupId=(SELECT groupId FROM groups WHERE name=:n)
'''
resp = db.session.execute(getStudentGroupQuery, {
'directoryId': directoryId,
'n': name
})
if [dict(r) for r in resp]:
return errors.AlreadyGrouped()
query = """INSERT INTO student_teams (studentId, groupId)
SELECT
(SELECT studentId FROM students WHERE personId=(SELECT personId from people WHERE directoryId=:directoryId)),
(SELECT groupId FROM groups WHERE name=:n);"""
db.session.execute(query, {'directoryId': directoryId, 'n': name})
db.session.commit()
resp = db.session.execute(getStudentGroupQuery, {
'directoryId': directoryId,
'n': name
})
return make_response(json.dumps([dict(r) for r in resp][0]), 200)
def post(self, uid, name):
if not uid.isnumeric():
return errors.NotNumeric()
if isUnique('uid', uid, 'students', db):
return errors.NoStudent()
if isUnique('name', name, 'groups', db):
return errors.NoGroup()
# -- If student already part of group -- #
getStudentGroupQuery = '''SELECT * FROM student_teams WHERE
studentId=(SELECT studentId FROM students WHERE uid=:uid) AND
groupId=(SELECT groupId FROM groups WHERE name=:n)
'''
resp = db.session.execute(getStudentGroupQuery, {
'uid': uid,
'n': name
})
if [dict(r) for r in resp]:
return errors.AlreadyGrouped()
# -- Insert -- #
query = """INSERT INTO student_teams (studentId, groupId)
SELECT
(SELECT studentId FROM students WHERE uid=:uid),
(SELECT groupId FROM groups WHERE name=:n);"""
db.session.execute(query, {'uid': uid, 'n': name})
db.session.commit()
resp = db.session.execute(getStudentGroupQuery, {
'uid': uid,
'n': name
})
return make_response(json.dumps([dict(r) for r in resp][0]), 200)
def put(self, directoryId, className):
if isUnique('className', className, 'classes', db):
return errors.NoClass()
if isUnique('directoryId', directoryId, 'people', db):
return errors.NoPerson()
#Class - student
query = """
SELECT * FROM people JOIN students ON people.personId = students.personId
JOIN student_teams ON students.studentId = student_teams.studentId
JOIN groups ON student_teams.groupId = groups.groupId
JOIN classes ON groups.classId = classes.classId
JOIN surveys ON classes.classId = surveys.classId
WHERE classes.className = :cn AND people.directoryId = :directoryId;
"""
response = db.session.execute(query, {
"cn": className,
"directoryId": directoryId
})
result = [dict(r) for r in response]
if len(result) == 0:
return errors.NoStudent()
studentId = result[0]['studentId']
surveyId = result[0]['surveyId']
# Questions for this Survey
questionIds = []
query = "SELECT * FROM questions WHERE surveyId = :s;"
response = db.session.execute(query, {"s": surveyId})
questions = [dict(r) for r in response]
for question in questions:
questionIds.append(question['questionId'])
#Gather answers
answers = request.json
if not isinstance(answers, list):
return errors.MalformedList()
answerList = []
for answerObj in answers:
if 'answer' not in answerObj:
return errors.MalformedJSON()
else:
answerList.append(answerObj['answer'])
if len(answerList) != len(questionIds):
return errors.UnevenLength()
#Check replace condition
query = "SELECT * FROM answers JOIN questions ON answers.questionId = questions.questionId WHERE studentId = :s AND surveyId = :sid;"
response = db.session.execute(query, {
"s": studentId,
"sid": surveyId
}).fetchone()
newAnswer = response is None or len(response) == 0
#Insert
if newAnswer:
for idx in range(len(answerList)):
query = "INSERT INTO answers (studentId, questionId, answer) VALUES (:s, :q, :a);"
db.session.execute(query, {
"s": studentId,
"q": questionIds[idx],
"a": answerList[idx]
})
else:
for idx in range(len(answerList)):
query = "UPDATE answers SET answer = :a WHERE studentId = :s AND questionId = :q;"
db.session.execute(query, {
"s": studentId,
"q": questionIds[idx],
"a": answerList[idx]
})
db.session.commit()
return make_response('', 204)
def get(self, directoryId, className):
if isUnique('className', className, 'classes', db):
return errors.NoClass()
if isUnique('directoryId', directoryId, 'people', db):
return errors.NoPerson()
#Get Questions
query = """
SELECT * FROM questions;
"""
response = db.session.execute(query)
result = [dict(r) for r in response]
questions = []
for questionObj in result:
questions.append(questionObj['question'])
#Gather Info
query = """
SELECT *
FROM students JOIN people ON students.personId = people.personId
JOIN student_teams ON students.studentId = student_teams.studentId
JOIN groups ON student_teams.groupId = groups.groupId
JOIN classes ON groups.classId = classes.classId
WHERE classes.className = :c AND people.directoryId = :d;
"""
response = db.session.execute(query, {
"c": className,
"d": directoryId
})
result = [dict(r) for r in response]
if len(result) == 0:
return errors.NoStudent()
output = {}
questions = output.update({"questions": questions})
studentObjs = []
for student in result:
query = """
SELECT *
FROM surveys JOIN questions ON questions.surveyId = surveys.surveyId
LEFT JOIN answers ON answers.questionId = questions.questionId
WHERE surveys.classId = :c AND answers.studentId = :s;
"""
response = db.session.execute(query, {
"c": student['classId'],
"s": student['studentId']
})
answerObjs = [dict(r) for r in response]
answerList = []
for a in answerObjs:
answerList.append(a['answer'])
studentObjs.append({
"answers": answerList,
"firstName": student['firstName'],
"lastName": student['lastName'],
"uid": student['uid'],
"className": student['className']
})
output.update({"results": studentObjs[0]})
return make_response(json.dumps(output), 200)
