示例#1
0
    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)
示例#2
0
    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)
示例#3
0
    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)
示例#4
0
    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)
示例#5
0
    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)
示例#6
0
    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)
示例#7
0
    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)
示例#8
0
    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)
示例#9
0
    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)
示例#10
0
    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)
示例#11
0
    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)
示例#12
0
    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)
示例#13
0
    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)
示例#14
0
    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)
示例#15
0
    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)