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 post(self, className):
if isUnique('className', className, 'classes', db):
return errors.NoClass()
questions = request.json
if not isinstance(questions, list):
return errors.MalformedList()
for questionObj in questions:
if 'question' not in questionObj:
return errors.MalformedJSON()
# Select Class Id
response = db.session.execute(
'SELECT classId FROM classes WHERE className = :n;',
{'n': className})
classId = [dict(r) for r in response][0]['classId']
# Select Survey Id
response = db.session.execute(
'SELECT surveyId FROM surveys WHERE classId = :classId;',
{'classId': classId})
surveyId = [dict(r) for r in response][0]['surveyId']
# Post the Questions
for questionObj in questions:
questionString = questionObj['question']
query = "INSERT INTO questions (surveyId, question) VALUES (:surveyId, :q);"
db.session.execute(query, {
"surveyId": surveyId,
"q": questionString
})
db.session.commit()
return make_response('', 204)
def get(self, className):
if isUnique('className', className, 'classes', db):
return errors.NoClass()
#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;
"""
response = db.session.execute(query, {"c": className})
result = [dict(r) for r in response]
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})
return make_response(json.dumps(output), 200)
def post(self):
if request.json is not None:
return errors.InvalidBody()
# Add a group to the Groups table
requiredArgs = ['name', 'className']
parser = reqparse.RequestParser(bundle_errors=True)
parser = addToParser(requiredArgs, parser, required=True)
args = parser.parse_args()
# -- Check Unique Constraints -- #
if not isUnique('name', args['name'], 'groups', db):
return errors.DuplicateGroup()
if isUnique('className', args['className'], 'classes', db):
return errors.NoClass()
# -- Add group to table -- #
query = 'INSERT INTO groups (name, classId) VALUES (:n, (SELECT classId FROM classes WHERE className=:cname));'
db.session.execute(query, {
'n': args['name'],
'cname': args['className']
})
response = db.session.execute('SELECT groupId, name, className, watch, groupHealth, groupScore FROM groups t1 JOIN classes t2 ON t1.classID = t2.classID WHERE name = :n;',{'n':args['name']})
db.session.commit()
result = [dict(r) for r in response][0]
return make_response(json.dumps(result), 200)
def get(self, className):
if isUnique('className', className, 'classes', db):
return errors.NoClass()
query = """
SELECT questions.surveyId, classes.className, question FROM questions
JOIN surveys ON surveys.surveyId = questions.surveyId
JOIN classes ON surveys.classId = classes.classId
WHERE className = :c;
"""
response = db.session.execute(query, {"c": className})
result = [dict(r) for r in response]
return make_response(json.dumps(result), 200)
def delete(self, className):
if isUnique('className', className, 'classes', db):
return errors.NoClass()
# Select Class Id
response = db.session.execute(
'SELECT classId FROM classes WHERE className = :n;',
{'n': className})
classId = [dict(r) for r in response][0]['classId']
query = """
DELETE FROM surveyHistory WHERE classId = :c;
"""
response = db.session.execute(query, {"c": classId})
db.session.commit()
return make_response('', 204)
def get(self, className):
if isUnique('className', className, 'classes', db):
return errors.NoClass()
# Select Class Id
response = db.session.execute(
'SELECT classId FROM classes WHERE className = :n;',
{'n': className})
classId = [dict(r) for r in response][0]['classId']
query = """
SELECT className, CAST(timestamp AS CHAR) as harvestedOn, data as results FROM surveyHistory
JOIN classes ON classes.classId = surveyHistory.classId
WHERE classes.classId = :c;
"""
response = db.session.execute(query, {"c": classId})
result = [dict(r) for r in response]
for row in result:
row['results'] = json.loads(row['results'])
return make_response(json.dumps(result, sort_keys=True), 200)
def delete(self):
if request.json is not None:
return errors.InvalidBody()
# Delete a class from the classes table
requiredArgs = ['className']
parser = reqparse.RequestParser(bundle_errors=True)
parser = addToParser(requiredArgs, parser, required=True)
args = parser.parse_args()
# Checks to see if a class with className exists.
if isUnique('className', args['className'], 'classes', db):
return errors.NoClass()
#Delete from surveys
query = """DELETE FROM surveys
WHERE classId = (SELECT classId FROM classes WHERE className = :c);
"""
response = db.session.execute(query, {"c": args['className']})
#Delete from survey History
query = """DELETE FROM surveyHistory
WHERE classId = (SELECT classId FROM classes WHERE className = :c);
"""
response = db.session.execute(query, {"c": args['className']})
#Delete from teams
query = "DELETE FROM student_teams WHERE groupId IN (SELECT groupId FROM groups WHERE classId=(SELECT classId FROM classes WHERE className=:cname));"
db.session.execute(query, {'cname': args['className']})
#Delete from groups and classes
query = """DELETE FROM groups WHERE classId=(SELECT classId from classes WHERE className=:cname);
DELETE FROM classes WHERE className=:cname;
"""
db.session.execute(query, {'cname': args['className']})
db.session.commit()
return make_response('', 204)
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 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, className):
if isUnique('className', className, 'classes', db):
return errors.NoClass()
response = db.session.execute("SELECT groupId, name, className, watch, groupHealth, groupScore FROM groups t1 JOIN classes t2 ON t1.classID = t2.classID WHERE className = :n;", {'n':className})
return make_response(json.dumps([dict(r) for r in response]), 200)