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, 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, 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 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 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): if request.json is not None: return errors.InvalidBody() requiredArgs = ['slack', 'grades', 'lateness', 'survey', 'lastView'] parser = reqparse.RequestParser(bundle_errors=True) parser = addToParser(requiredArgs, parser, required=True) args = parser.parse_args() for param in requiredArgs: if not args[param].replace('.','',1).isdigit(): return errors.NotNumeric() query = """ UPDATE weights SET slack = :slack, grades = :grades, lateness = :lateness, survey = :survey, lastView = :lastView WHERE id = 'main'; """ response = db.session.execute(query, { 'slack':args['slack'], 'grades':args['grades'], 'lateness':args['lateness'], 'survey':args['survey'], 'lastView':args['lastView'] }) query = "SELECT slack, grades, lateness, survey, lastView FROM weights WHERE id = 'main';" response = db.session.execute(query) db.session.commit() return make_response(json.dumps([dict(r) for r in response][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)