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)