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 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 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 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, 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, directoryId):
if request.json is not None:
return errors.InvalidBody()
nullableArgs = ['uid']
parser = reqparse.RequestParser(bundle_errors=True)
parser = addToParser(nullableArgs, parser)
args = parser.parse_args()
if isUnique('directoryId', directoryId, 'people', db):
return errors.NoPerson()
if args['uid'] is not None and not isUnique('uid', args['uid'],
'students', db):
return errors.DuplicateUID()
if args['uid'] is not None and not args['uid'].isnumeric():
return errors.NotNumeric()
person = db.session.execute(
"SELECT * FROM people WHERE directoryId = :did;", {
'did': directoryId
}).fetchone()
personId = person['personId']
if not isUnique('personId', personId, 'students', db):
return errors.DuplicateDID()
query = "INSERT INTO students (uid, personId) VALUES (:uid, :personId);"
db.session.execute(query, {'uid': args['uid'], 'personId': personId})
db.session.commit()
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})
return make_response(json.dumps([dict(r) for r in response][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)