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 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 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):
query = """
SELECT * FROM people JOIN students ON people.personId = students.personId;
"""
response = db.session.execute(query)
result = [dict(r) for r in response]
for row in result:
if row['firstName'] != 'Collin' and row['firstName'] != 'Amy':
#UPDATE PEOPLE
firstName = names.get_first_name()
lastName = names.get_last_name()
directoryId = (firstName[0] + lastName).lower()
while not isUnique('directoryId', directoryId, 'people', db):
firstName = names.get_first_name()
lastName = names.get_last_name()
directoryId = (firstName[0] + lastName).lower()
personId = row['personId']
query = """
UPDATE people SET
firstName = :f,
lastName = :l,
directoryId = :d
WHERE personId = :p;
"""
db.session.execute(query, {
"f": firstName,
"l": lastName,
"d": directoryId,
"p": personId
})
#UPDATE Students
UID = ''.join(str(randint(0, 9)) for _ in range(9))
while not isUnique('uid', UID, 'students', db):
UID = ''.join(str(randint(0, 9)) for _ in range(9))
query = """
UPDATE students SET
uid = :u
WHERE personId = :p;
"""
db.session.execute(query, {"u": UID, "p": personId})
query = """
SELECT * FROM people JOIN students ON people.personId = students.personId;
"""
response = db.session.execute(query)
result = [dict(r) for r in response]
db.session.commit()
return make_response(json.dumps(result), 200)
def post(self):
if request.json is not None:
return errors.InvalidBody()
requiredArgs = ['firstName', 'lastName', 'directoryId']
nullableArgs = ['uid']
parser = reqparse.RequestParser(bundle_errors=True)
parser = addToParser(requiredArgs, parser, required=True)
parser = addToParser(nullableArgs, parser)
args = parser.parse_args()
# -- Check Unique Constraints -- #
if not isUnique('directoryId', args['directoryId'], 'people', db):
return errors.DuplicateDID()
if args['uid'] is not None and not isUnique('uid', args['uid'],
'students', db):
return errors.DuplicateUID()
# -- Add to People Table -- #
query = '''
INSERT INTO people (firstName, lastName, directoryId) VALUES (:fn,:ln,:d);
'''
db.session.execute(
query, {
'fn': args['firstName'],
'ln': args['lastName'],
'd': args['directoryId']
})
person = db.session.execute(
"SELECT * FROM people WHERE directoryId = :d;", {
'd': args['directoryId']
}).fetchone()
personId = person['personId']
# -- Add to Student Table -- #
query = '''
INSERT INTO students (uid, personId) VALUES (:uid,:pid);
'''
db.session.execute(query, {'uid': args['uid'], 'pid': personId})
# -- Return as a join of the 2 Tables -- #
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': args['directoryId']})
db.session.commit()
return make_response(json.dumps([dict(r) for r in response][0]), 200)
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, name):
if isUnique('name', name, 'groups', db):
return errors.NoGroup()
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':name}).fetchone()
currVal = response['watch']
if currVal == 0:
# Toggle
db.session.execute("UPDATE groups SET watch = :newVal WHERE name = :n;" , {
'n' : name,
'newVal' : 1
})
# TODO: Some function that will notifiy this team they are being watched
else:
# Toggle
db.session.execute("UPDATE groups SET watch = :newVal WHERE name = :n;" , {
'n' : name,
'newVal' : 0
})
# TODO: Some function that will notifiy this they are no longer being watched
db.session.commit()
return make_response('',204)
def get(self, name):
if isUnique('name', name, 'groups', db):
return errors.NoGroup()
response = db.session.execute("SELECT * FROM groups WHERE name = :n;", {'n':name}).fetchone()
return make_response(jsonify({'result':response['watch']}), 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, name):
if isUnique('name', name, 'groups', db):
return errors.NoGroup()
response = db.session.execute(
"SELECT firstName, lastName, directoryId, uid FROM people INNER JOIN students ON people.personId = students.personID INNER JOIN student_teams ON students.studentId = student_teams.studentId INNER JOIN groups ON student_teams.groupId = groups.groupId WHERE name = :n;",
{'n': name})
result = [dict(r) for r in response]
return make_response(json.dumps(result), 200)
def get(self):
if request.json is not None:
return errors.InvalidBody()
requiredArgs = ['key', 'directoryId']
parser = reqparse.RequestParser(bundle_errors=False)
parser = addToParser(requiredArgs, parser, required=True)
args = parser.parse_args()
if args['key'] != os.getenv('JWT_SECRET_KEY'):
return errors.InvalidSecret()
# ---- Admin Auth --- #
response = db.session.execute("SELECT * FROM admin WHERE id = :did", {
"did": args['directoryId']
}).fetchone()
if response is not None:
priv = response['privileges']
identity = {"directoryId": args['directoryId'], "privilege": priv}
expires = datetime.timedelta(days=1)
access_token = create_access_token(identity=identity,
expires_delta=expires)
return make_response(jsonify(access_token=access_token), 200)
# ---- Student Auth ---- #
if isUnique('directoryId', args['directoryId'], 'people', db):
return errors.NoAuth()
response = db.session.execute(
'SELECT * FROM people WHERE directoryId = :d;',
{'d': args['directoryId']})
result = [dict(r) for r in response]
personId = result[0]['personId']
if isUnique('personId', personId, 'students', db):
return errors.NoAuth()
priv = 'student'
identity = {"directoryId": args['directoryId'], "privilege": priv}
expires = datetime.timedelta(days=1)
access_token = create_access_token(identity=identity,
expires_delta=expires)
return make_response(jsonify(access_token=access_token), 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, 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, 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, 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 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 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 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 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, name, table):
if table not in groupTablesIgnorePopulate:
return errors.NoTable()
if isUnique('name', name, 'groups', db):
return errors.NoGroup()
response = db.session.execute('SELECT groupId FROM groups WHERE name = :name;', {'name':name})
result = [dict(r) for r in response]
groupId = result[0]['groupId']
response = db.session.execute('SELECT * FROM {} WHERE groupId = :groupId;'.format(table), {'groupId':groupId})
if response is None:
return make_response(jsonify({'result' : ""}), 200)
return make_response(jsonify([dict(r) for r in response]), 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 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 put(self, name):
if request.json is not None:
return errors.InvalidBody()
requiredArgs = ['groupHealth']
parser = reqparse.RequestParser(bundle_errors=True)
parser = addToParser(requiredArgs, parser, required = True)
parser = addToParser(requiredArgs, parser)
args = parser.parse_args()
if isUnique('name', name, 'groups', db):
return errors.NoGroup()
if not args['groupHealth'].isnumeric() or int(args['groupHealth']) > 3 or int(args['groupHealth']) < 1:
return errors.InvalidHealth()
db.session.execute("UPDATE groups SET groupHealth = :newVal WHERE name = :n;" , {'n' : name, 'newVal' : args['groupHealth']})
db.session.commit()
return make_response('', 204)
def post(self, name):
if isUnique('name', name, 'groups', db):
return errors.NoGroup()
response = db.session.execute('SELECT groupId FROM groups WHERE name = :name;', {'name':name})
result = [dict(r) for r in response]
groupId = result[0]['groupId']
query = ''' LOAD DATA INFILE 'slack.csv'
INTO TABLE slackData
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(slackMsgId, channel, user, timestamp)
SET groupID=:gid
;'''
db.session.execute(query , {'gid' : groupId})
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()
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 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, 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 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)
