def admin_institutions_add(self, institution=None, description=None, contact=None, website=None, *args, **kwargs): """ Adds an institution to the database. """ userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) allow(["HEAD", "GET", "POST"]) options = " " status = "" if institution: cnx, status = db.connect() cursor = cnx.cursor() query = ( "insert into institution (institution_name,description,contact,website) values ('" + institution + "','" + description + "','" + contact + "','" + website + "')") cursor.execute(query) status = "New institution has been added" cursor.close() cnx.close() return templating.render("admin_institutions_add.html", ROOT_URL=config.VIRTUAL_URL, ERROR="", REDIRECT="", OPTION=options, STATUS=status, IS_ADMIN=isAdmin(userid))
def admin_course_add_teacher(self, courseid, username, *args, **kwargs): """Adds a teacher to a course.""" userid = cherrypy.session.get(auth.SESSION_USERID) requireAdminOrTeacher(userid) allow(['POST']) cnx, status = db.connect() cursor = cnx.cursor() query = """SELECT t.teacherid FROM teacher_info t, whiley_user u WHERE u.username = %s AND u.userid = t.userid""" cursor.execute(query, (username, )) teacherid = cursor.fetchone() if not teacherid: return templating.render("redirect.html", STATUS="alert-warning", MESSAGE="No such teacher!") teacherid = teacherid[0] query = """INSERT INTO teacher_course_link (teacherinfoid, courseid) VALUES (%s, %s)""" cursor.execute(query, (teacherid, courseid)) if not cursor.rowcount: return templating.render("redirect.html", STATUS="alert-warning", MESSAGE="Failed to add teacher!") return templating.render("redirect.html", STATUS="alert-success", MESSAGE="Teacher added.")
def admin(self, *args, **kwargs): """ The admin homepage should return a template for the admin page. >>> authorizeTests() >>> self = Admin() >>> results = self.admin() >>> results.ERROR '' >>> results.REDIRECT 'NO' >>> results.STATUS 'DB: Connection ok' """ userid = cherrypy.session.get(auth.SESSION_USERID) requireAdminOrTeacher(userid) allow(["HEAD", "GET"]) error = "" redirect = "NO" status = "DB: Connection ok" cnx = db.connect() return templating.render("admin.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, STATUS=status, IS_ADMIN=isAdmin(userid))
def index(self, *args, **kwargs): allow(["HEAD", "GET"]) error = "" redirect = "NO" admin = False template = lookup.get_template("index.html") username = cherrypy.session.get(auth.SESSION_KEY) userid = cherrypy.session.get(auth.SESSION_USERID) files = DEFAULT_PROJECT if userid is None: loggedin = False print ("not logged in") else: loggedin = True if isAdmin(userid) or isTeacher(userid): admin = True print ("logged") filelist = get_files(username) print filelist files = build_file_tree(filelist) # print files return template.render( ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, USERNAME=username, USERID=userid, LOGGED=loggedin, ADMIN=admin, FILES=json.dumps(files))
def admin_institutions(self, institution="", *args, **kwargs): """ Lists available institutions. >>> authorizeTests() >>> self = Admin() >>> ret = self.admin_institutions() >>> ('Victoria University of Wellington', 2) in ret.OPTION True >>> ret = self.admin_institutions(2) >>> ret.INSTITUTION_ID, ret.INSTITUTION, ret.CONTACT, ret.WEBSITE, ret.DESCRIPTION (2, 'Victoria University of Wellington', None, None, None) """ userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) allow(["HEAD", "GET", "POST"]) redirect = "NO" options = [] if institution: cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT institution_name, institutionid from institution order by institution_name") cursor.execute(query) options = list(cursor) cursor.close() cnx.close() displayInstitution = "" displayContact = "" displayWebsite = "" displayDescription = "" if institution == "": cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT institution_name, institutionid from institution order by institution_name") cursor.execute(query) institution = "" for (institute) in cursor: options.append(institute) if institution == "": institution = institute[1] cursor.close() cnx.close() cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institution_name,description,contact,website from institution where institutionid = '" + str(institution) + "'") cursor.execute(query) displayInstitution, displayDescription, displayContact, displayWebsite = cursor.fetchone() cursor.close() cnx.close() return templating.render("admin_institutions.html", ROOT_URL=config.VIRTUAL_URL, ERROR="", REDIRECT=redirect, OPTION=options, INSTITUTION_ID=institution, INSTITUTION=displayInstitution, CONTACT=displayContact, WEBSITE=displayWebsite, DESCRIPTION=displayDescription, IS_ADMIN=isAdmin(userid))
def index(self, *args, **kwargs): allow(["HEAD", "GET"]) error = "" redirect = "NO" admin = False template = lookup.get_template("index.html") username = cherrypy.session.get(auth.SESSION_KEY) userid = cherrypy.session.get(auth.SESSION_USERID) files = DEFAULT_PROJECT if userid is None: loggedin = False print("not logged in") else: loggedin = True if isAdmin(userid) or isTeacher(userid): admin = True print("logged") filelist = get_files(username) print filelist files = build_file_tree(filelist) # print files return template.render(ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, USERNAME=username, USERID=userid, LOGGED=loggedin, ADMIN=admin, FILES=json.dumps(files))
def save(self, code, *args, **kwargs): allow(["HEAD", "POST"]) # First, create working directory dir = createWorkingDirectory() # Second, save the file save(config.DATA_DIR + "/" + dir + "/tmp.whiley", code, "utf-8") # Fouth, return result as JSON return json.dumps({"id": dir})
def user_courses(self, studentinfoid=None, institution="", validationcode="", courseid="", *args, **kwargs): """Assign user to select course """ if studentinfoid is None: raise cherrypy.HTTPRedirect("/") allow(["HEAD", "GET", "POST"]) error = False error_msg = " " redirect = "NO" options = [] course_list = [] if institution: cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT institutionid,institution_name from institution order by institution_name") cursor.execute(query) options = list(cursor) cursor.close() if courseid: cnx, status = db.connect() cursor = cnx.cursor() error = insertuserdetails(studentinfoid, institution, courseid, validationcode) cursor.close() if error is False: message="User Created, Welcome! Redirecting..." template = lookup.get_template("redirect.html") return template.render(STATUS="alert-success", MESSAGE=message) else: error_msg= "Wrong Validation Code" if institution == "": cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT institutionid,institution_name from institution order by institution_name") cursor.execute(query) for (institutionid,institution_name) in cursor: options.append((institutionid, institution_name)) if institution == "": institution = str(institutionid) cursor.close() ##get courses list cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT courseid,code from course where institutionid = '" + institution + "' order by code") cursor.execute(query) course_list = list(cursor) cursor.close() return templating.render("user_institutions.html", ERROR=error, ERRORMSG=error_msg, NOTALLOWED=False, ROOT_URL=config.VIRTUAL_URL, OPTION=options, COURSE_LIST=course_list, STUDENTINFOID=studentinfoid, INSTITUTION=institution)
def save(self, code, *args, **kwargs): allow(["HEAD", "POST"]) # First, create working directory dir = createWorkingDirectory() # Second, save the file save(config.DATA_DIR + "/" + dir + "/tmp.whiley", code, "utf-8") # Fouth, return result as JSON return json.dumps({ "id": dir })
def admin_course_add(self, course_name=None, course_code=None, course_year=None, course_institution=None, validation_code=None, *args, **kwargs): """ Adds a course to the database. """ userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) import random, string allow(["HEAD", "GET", "POST"]) error = "" redirect = "NO" options = [] newstatus = "" validationCode = ''.join( random.choice(string.ascii_uppercase + string.digits) for _ in range(4)) if course_code: cnx, status = db.connect() cursor = cnx.cursor() query = ( "insert into course (course_name,code,year,institutionid,validationcode) values ('" + course_name + "','" + course_code.upper() + "','" + course_year + "','" + course_institution + "','" + validation_code + "')") cursor.execute(query) newstatus = "New course has been added" cursor.close() cnx.close() cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institutionid,institution_name from institution order by institution_name" ) cursor.execute(query) options = list(cursor) cursor.close() cnx.close() return templating.render("admin_courses_add.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, OPTION=options, NEWSTATUS=newstatus, VALIDATIONCODE=validationCode, IS_ADMIN=isAdmin(userid))
def admin_courses(self, institution="", *args, **kwargs): """ Lists all available courses. >>> authorizeTests() >>> self = Admin() >>> ret = self.admin_courses() >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> ret = self.admin_courses('2') >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> ret.INSTITUTION '2' >>> (1, 'SWEN302') in ret.COURSE_LIST True """ userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) allow(["HEAD", "GET", "POST"]) error = "" redirect = "NO" options = [] course_list = [] if institution: cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT institutionid,institution_name from institution order by institution_name") cursor.execute(query) options = list(cursor) cursor.close() else: cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT institutionid,institution_name from institution order by institution_name") cursor.execute(query) for (institutionid,institution_name) in cursor: options.append((institutionid, institution_name)) if institution == "": institution = str(institutionid) cursor.close() cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT courseid,code from course where institutionid = '" + institution + "' order by code") cursor.execute(query) course_list = list(cursor) cursor.close() return templating.render("admin_courses.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, OPTION=options, INSTITUTION=institution, COURSE_LIST=course_list, IS_ADMIN=isAdmin(userid))
def admin_course_details(self, id, *args, **kwargs): """ Retrieves course details. >>> authorizeTests() >>> self = Admin() >>> ret = self.admin_course_details('1') >>> ret.COURSENAME, ret.COURSECODE, ret.YEAR ('Agile Methods', 'SWEN302', 2014) >>> ret.VALIDATIONCODE, ret.INSTITUTION (u'aaaa', 'Victoria University of Wellington') >>> 'dave, dave' in ret.STUDENTS True """ userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) allow(["HEAD", "GET", "POST"]) error = "" redirect = "NO" newstatus = "" students = [] courseId = id cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT courseid,course_name,code,year,validationcode,institution_name from course a, institution b where a.institutionid = b.institutionid and a.courseid = %s") cursor.execute(query, (id,)) courseID, courseName, courseCode, year, validationcode, institution = cursor.fetchone() sql = "SELECT distinct a.student_info_id,a.givenname,a.surname from student_info a,student_course_link b, course c, course_stream d where c.courseid = %s and c.courseid = d.courseid and d.coursestreamid =b.coursestreamid and b.studentinfoid = a.student_info_id order by a.surname" cursor.execute(sql, (str(courseID),)) students = [(id, name(givenname, surname)) for id, givenname, surname in cursor] sql = """SELECT distinct a.teacherid,a.full_name from teacher_info a, teacher_course_link b where b.courseid = %s and b.teacherinfoid = a.teacherid""" cursor.execute(sql, (str(courseID),)) teachers = list(cursor) sql = """SELECT stream_name from course_stream where courseid = %s""" cursor.execute(sql, (str(courseId),)) streams = [ret[0] for ret in cursor] cursor.close() return templating.render("admin_course_details.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, TEACHERS=teachers, STREAMS=streams, COURSENAME=courseName, COURSECODE=courseCode, YEAR=year, VALIDATIONCODE=validationcode, INSTITUTION=institution, STUDENTS=students, COURSEID=courseId, IS_ADMIN=isAdmin(userid))
def view_project(self, userid, projectname): allow(["HEAD", "GET"]) cnx, status = db.connect() cursor = cnx.cursor() sql = "SELECT p.projectid FROM project p where p.userid = %s AND p.project_name = %s" cursor.execute(sql, (userid, projectname)) result = cursor.fetchone() print result if not result: raise HTTPError(404) result = result[0] return self.student_project(result)
def admin_course_add_stream(self, courseid, name, *args, **kwargs): """Adds a stream to a course.""" userid = cherrypy.session.get(auth.SESSION_USERID) requireAdminOrTeacher(userid) allow(['POST']) print courseid, name cnx, status = db.connect() cursor = cnx.cursor() query = """INSERT INTO course_stream (stream_name, courseid) VALUES (%s, %s)""" cursor.execute(query, (name, courseid)) if not cursor.rowcount: return templating.render("redirect.html", STATUS="alert-warning", MESSAGE="Failed to add course stream!") return templating.render("redirect.html", STATUS="alert-success", MESSAGE="Course stream added.")
def compile(self, code, verify, *args, **kwargs): allow(["HEAD", "POST"]) # First, create working directory dir = createWorkingDirectory() dir = config.DATA_DIR + "/" + dir # Second, compile the code result = compile(code, verify, dir) # Third, delete working directory shutil.rmtree(dir) # Fouth, return result as JSON if type(result) == str: response = {"result": "error", "error": result} elif len(result) != 0: response = {"result": "errors", "errors": result} else: response = {"result": "success"} return json.dumps(response)
def index(self, id="HelloWorld", *args, **kwargs): allow(["HEAD", "GET"]) error = "" redirect = "NO" try: # Sanitize the ID. safe_id = re.sub("[^a-zA-Z0-9-_]+", "", id) # Load the file code = load(config.DATA_DIR + "/" + safe_id + "/tmp.whiley","utf-8") # Escape the code code = cgi.escape(code) except Exception: code = "" error = "Invalid ID: %s" % id redirect = "YES" template = lookup.get_template("index.html") return template.render(ROOT_URL=config.VIRTUAL_URL,CODE=code,ERROR=error,REDIRECT=redirect)
def run(self, code, *args, **kwargs): allow(["HEAD", "POST"]) # First, create working directory dir = createWorkingDirectory() dir = config.DATA_DIR + "/" + dir # Second, compile the code and then run it result = compile(code, "false", dir) if type(result) == str: response = {"result": "error", "error": result} elif len(result) != 0: response = {"result": "errors", "errors": result} else: response = {"result": "success"} # Run the code if the compilation succeeded. output = run(dir) response["output"] = output # Third, delete working directory shutil.rmtree(dir) # Fourth, return result as JSON return json.dumps(response)
def exports(self, _main, *args, **files): import StringIO allow(["HEAD", "POST", "GET"]) # First, create working directory suffix = createWorkingDirectory() dir = config.DATA_DIR + "/" + suffix save_all(files, dir) output = make_tarfile("%s.tar.gz" % _main.split("/")[0], os.path.join(dir, _main.split("/")[0])) tempf = open(output, 'rb') stringf = StringIO.StringIO(tempf.read()) tempf.close() result = cherrypy.lib.static.serve_fileobj(stringf, "application/x-tgz", name="this") os.unlink(output) return result
def run_all(self, _verify, _main, _project, *args, **files): allow(["HEAD", "POST"]) # to start auto-save project for logged in users self.private_save(**files) # First, create working directory suffix = createWorkingDirectory() dir = config.DATA_DIR + "/" + suffix # Find package name package = None main_src = files[_main].strip() if main_src.startswith('package'): first_line = main_src.split('\n')[0] package = first_line.replace('package', '').strip() run_path = os.path.join(dir, os.path.dirname(_main)) result = compile_all(_main, files, _verify, dir) if "internal failure (null)" in str(result): make_tarfile('%s.tar.gz' % suffix, dir) if type(result) == str: response = {"result": "error", "error": result} elif len(result) != 0: response = {"result": "errors", "errors": result} else: response = {"result": "success"} class_to_run = os.path.split(_main[:-len(".whiley")])[1].replace( '/', '.') if package: class_to_run = package + '.' + class_to_run run_path = os.path.join(dir, _project) output = run(run_path, class_to_run) response["output"] = output shutil.rmtree(dir) return json.dumps(response)
def index(self, id="HelloWorld", *args, **kwargs): allow(["HEAD", "GET"]) error = "" redirect = "NO" try: # Sanitize the ID. safe_id = re.sub("[^a-zA-Z0-9-_]+", "", id) # Load the file code = load(config.DATA_DIR + "/" + safe_id + "/tmp.whiley", "utf-8") # Escape the code code = cgi.escape(code) except Exception: code = "" error = "Invalid ID: %s" % id redirect = "YES" template = lookup.get_template("index.html") return template.render(ROOT_URL=config.VIRTUAL_URL, CODE=code, ERROR=error, REDIRECT=redirect)
def run_all(self, _verify, _main, _project, *args, **files): allow(["HEAD", "POST"]) # to start auto-save project for logged in users self.private_save(**files) # First, create working directory suffix = createWorkingDirectory() dir = config.DATA_DIR + "/" + suffix # Find package name package = None main_src = files[_main].strip() if main_src.startswith('package'): first_line = main_src.split('\n')[0] package = first_line.replace('package', '').strip() run_path = os.path.join(dir, os.path.dirname(_main)) result = compile_all(_main, files, _verify, dir) if "internal failure (null)" in str(result): make_tarfile('%s.tar.gz' % suffix, dir) if type(result) == str: response = {"result": "error", "error": result} elif len(result) != 0: response = {"result": "errors", "errors": result} else: response = {"result": "success"} class_to_run = os.path.split(_main[:-len(".whiley")])[1].replace('/','.') if package: class_to_run = package + '.' + class_to_run run_path = os.path.join(dir, _project) output = run(run_path, class_to_run) response["output"] = output shutil.rmtree(dir) return json.dumps(response)
def student_project(self, project): allow(["HEAD", "GET"]) admin = False # TODO This page should REALLY be secured! How should this work? template = lookup.get_template("index.html") username = cherrypy.session.get(auth.SESSION_KEY) userid = cherrypy.session.get(auth.SESSION_USERID) if isAdmin(userid): admin = True files = get_project(project) print files files = build_file_tree(files) return template.render(ROOT_URL=config.VIRTUAL_URL, CODE="", ERROR="", REDIRECT="", USERNAME=username, USERID=userid, LOGGED=username is not None, ADMIN=admin, FILES=json.dumps(files))
def admin_course_add(self, course_name=None, course_code=None, course_year=None, course_institution=None, validation_code=None, *args, **kwargs): """ Adds a course to the database. """ userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) import random, string allow(["HEAD", "GET", "POST"]) error = "" redirect = "NO" options = [] newstatus = "" validationCode = ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(4)) if course_code: cnx, status = db.connect() cursor = cnx.cursor() query = ("insert into course (course_name,code,year,institutionid,validationcode) values ('" + course_name + "','" + course_code.upper() + "','" + course_year + "','" + course_institution + "','" + validation_code + "')") cursor.execute(query) newstatus = "New course has been added" cursor.close() cnx.close() cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT institutionid,institution_name from institution order by institution_name") cursor.execute(query) options = list(cursor) cursor.close() cnx.close() return templating.render("admin_courses_add.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, OPTION=options, NEWSTATUS=newstatus, VALIDATIONCODE=validationCode, IS_ADMIN=isAdmin(userid))
def admin_course_add_teacher(self, courseid, username, *args, **kwargs): """Adds a teacher to a course.""" userid = cherrypy.session.get(auth.SESSION_USERID) requireAdminOrTeacher(userid) allow(['POST']) cnx, status = db.connect() cursor = cnx.cursor() query = """SELECT t.teacherid FROM teacher_info t, whiley_user u WHERE u.username = %s AND u.userid = t.userid""" cursor.execute(query, (username,)) teacherid = cursor.fetchone() if not teacherid: return templating.render("redirect.html", STATUS="alert-warning", MESSAGE="No such teacher!") teacherid = teacherid[0] query = """INSERT INTO teacher_course_link (teacherinfoid, courseid) VALUES (%s, %s)""" cursor.execute(query, (teacherid, courseid)) if not cursor.rowcount: return templating.render("redirect.html", STATUS="alert-warning", MESSAGE="Failed to add teacher!") return templating.render("redirect.html", STATUS="alert-success", MESSAGE="Teacher added.")
def compile_all(self, _verify, _main, *args, **files): allow(["HEAD", "POST"]) # to start auto-save project for logged in users self.private_save(**files) # First, create working directory suffix = createWorkingDirectory() dir = config.DATA_DIR + "/" + suffix result = compile_all(_main, files, _verify, dir) shutil.rmtree(dir) if "internal failure (null)" in str(result): make_tarfile('%s.tar.gz' % suffix, dir) if type(result) == str: response = {"result": "error", "error": result} elif len(result) != 0: response = {"result": "errors", "errors": result} else: response = {"result": "success"} return json.dumps(response)
def student_project(self, project): allow(["HEAD", "GET"]) admin = False # TODO This page should REALLY be secured! How should this work? template = lookup.get_template("index.html") username = cherrypy.session.get(auth.SESSION_KEY) userid = cherrypy.session.get(auth.SESSION_USERID) if isAdmin(userid): admin = True files = get_project(project) print files files = build_file_tree(files) return template.render( ROOT_URL=config.VIRTUAL_URL, CODE="", ERROR="", REDIRECT="", USERNAME=username, USERID=userid, LOGGED=username is not None, ADMIN=admin, FILES=json.dumps(files) )
def admin_students_list(self, id=None, institution="", course=None, *args, **kwargs): """ Lists students under a institution and course. >>> authorizeTests() >>> self = Admin().admin_students_list >>> ret = self() >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> ret.STUDENTNAME, ret.STUDENTCOURSES ('No student selected', []) >>> ret = self(institution='2') >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> (1, 'SWEN302') in ret.OPTIONCOURSE True >>> ret.INSTITUTION '2' >>> ret.STUDENTNAME, ret.STUDENTCOURSES ('No student selected', []) >>> ret = self(institution='2', course='1') >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> ret.INSTITUTION '2' >>> (1, 'SWEN302') in ret.OPTIONCOURSE True >>> ret.COURSE '1' >>> (70, 'dave, dave') in ret.OPTIONSTUDENT True >>> ret.STUDENTNAME, ret.STUDENTCOURSES ('No student selected', []) >>> ret = self(70, '2', '1') >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> ret.INSTITUTION '2' >>> (1, 'SWEN302') in ret.OPTIONCOURSE True >>> ret.COURSE '1' >>> (70, 'dave, dave') in ret.OPTIONSTUDENT True >>> ret.STUDENTNAME 'dave dave' >>> ('Agile Methods', 'SWEN302', 2014, 1) in ret.STUDENTCOURSES True """ isAdmin, permittedCourses, permittedStudents = getAccessPermissions() allow(["HEAD", "GET", "POST"]) error = "" redirect = "NO" options = [] optionsCourse = [] optionsStudent = [] studentInstitution = "" status, studentName, studentInstitution, studentCourses, studentProjects = \ studentInfo(id, "No student selected") if institution: cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT institutionid,institution_name from institution order by institution_name") cursor.execute(query) options = list(cursor) cursor.close() else: cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT institutionid,institution_name from institution order by institution_name") cursor.execute(query) for (institutionid,institution_name) in cursor: options.append((institutionid, institution_name)) if institution == "": institution = str(institutionid) cursor.close() if course: cnx, status = db.connect() cursor = cnx.cursor() sql = "SELECT courseid,code from course where institutionid = %s" cursor.execute(sql, institution) optionsCourse = [(courseid, code) for courseid, code in cursor if permittedCourses is None or courseid in permittedCourses] cursor.close() else: cnx, status = db.connect() cursor = cnx.cursor() sql = "SELECT courseid,code from course where institutionid = %s" cursor.execute(sql, institution) for (courseid,code) in cursor: if permittedCourses is None or courseid in permittedCourses: optionsCourse.append((courseid, code)) if course == "": course = str(courseid) cursor.close() if course and (permittedCourses is None or course in permittedCourses): cnx, status = db.connect() cursor = cnx.cursor() sql = "SELECT distinct a.student_info_id,a.givenname,a.surname from student_info a,student_course_link b, course c, course_stream d where c.courseid = %s and c.courseid = d.courseid and d.coursestreamid =b.coursestreamid and b.studentinfoid = a.student_info_id" cursor.execute(sql, (course,)) for (student_info_id,givenname,surname) in cursor: optionsStudent.append((student_info_id, name(givenname, surname))) if course == "": course = str(courseid) cursor.close() return templating.render("admin_students_list.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, STATUS=status, OPTION=options, INSTITUTION=institution, STUDENTNAME=studentName, STUDENTINSTITUTION=studentInstitution, STUDENTCOURSES=studentCourses, STUDENTPROJECTS=studentProjects, OPTIONCOURSE=optionsCourse, COURSE=course, OPTIONSTUDENT=optionsStudent, IS_ADMIN=isAdmin)
def css(self, filename, *args, **kwargs): allow(["HEAD", "GET"]) abspath = os.path.abspath("css/" + filename) return serve_file(abspath, "text/css")
def js(self, filename, *args, **kwargs): allow(["HEAD", "GET"]) abspath = os.path.abspath("js/" + filename) return serve_file(abspath, "application/javascript")
def images(self, filename, *args, **kwargs): allow(["HEAD", "GET"]) abspath = os.path.abspath("images/" + filename) return serve_file(abspath, "image/png")
def admin_course_details(self, id, *args, **kwargs): """ Retrieves course details. >>> authorizeTests() >>> self = Admin() >>> ret = self.admin_course_details('1') >>> ret.COURSENAME, ret.COURSECODE, ret.YEAR ('Agile Methods', 'SWEN302', 2014) >>> ret.VALIDATIONCODE, ret.INSTITUTION (u'aaaa', 'Victoria University of Wellington') >>> 'dave, dave' in ret.STUDENTS True """ userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) allow(["HEAD", "GET", "POST"]) error = "" redirect = "NO" newstatus = "" students = [] courseId = id cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT courseid,course_name,code,year,validationcode,institution_name from course a, institution b where a.institutionid = b.institutionid and a.courseid = %s" ) cursor.execute(query, (id, )) courseID, courseName, courseCode, year, validationcode, institution = cursor.fetchone( ) sql = "SELECT distinct a.student_info_id,a.givenname,a.surname from student_info a,student_course_link b, course c, course_stream d where c.courseid = %s and c.courseid = d.courseid and d.coursestreamid =b.coursestreamid and b.studentinfoid = a.student_info_id order by a.surname" cursor.execute(sql, (str(courseID), )) students = [(id, name(givenname, surname)) for id, givenname, surname in cursor] sql = """SELECT distinct a.teacherid,a.full_name from teacher_info a, teacher_course_link b where b.courseid = %s and b.teacherinfoid = a.teacherid""" cursor.execute(sql, (str(courseID), )) teachers = list(cursor) sql = """SELECT stream_name from course_stream where courseid = %s""" cursor.execute(sql, (str(courseId), )) streams = [ret[0] for ret in cursor] cursor.close() return templating.render("admin_course_details.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, TEACHERS=teachers, STREAMS=streams, COURSENAME=courseName, COURSECODE=courseCode, YEAR=year, VALIDATIONCODE=validationcode, INSTITUTION=institution, STUDENTS=students, COURSEID=courseId, IS_ADMIN=isAdmin(userid))
def admin_students_search(self, searchValue="", id=None, *args, **kwargs): """ Searches students by searchValue, displaying information for student number id. >>> authorizeTests() >>> self = Admin() >>> ret = self.admin_students_search() >>> ret.SEARCHRESULT, ret.SEARCHVALUE ([], '') >>> ret.STUDENTNAME, ret.INSTITUTIONNAME, ret.STUDENTCOURSES, ret.STUDENTPROJECTS ('', '', [], []) >>> ret = self.admin_students_search("dav") >>> ret.SEARCHVALUE 'dav' >>> (70, 'dave, dave') in ret.SEARCHRESULT True >>> ret.STUDENTNAME, ret.INSTITUTIONNAME, ret.STUDENTCOURSES, ret.STUDENTPROJECTS ('', '', [], []) >>> ret = self.admin_students_search("dav", 70) >>> ret.SEARCHVALUE 'dav' >>> (70, 'dave, dave') in ret.SEARCHRESULT True >>> ret.STUDENTNAME, ret.INSTITUTIONNAME ('dave dave', 'Victoria University of Wellington') >>> ('Agile Methods', 'SWEN302', 2014, 1) in ret.STUDENTCOURSES True """ isAdmin, _, permittedStudents = getAccessPermissions() allow(["HEAD", "GET", "POST"]) error = "" searchResult = [] redirect = "NO" status = "DB: Connection ok" studentCourses = [] studentProjects = [] empty = None if searchValue: cnx, status = db.connect() cursor = cnx.cursor() join = '%' + searchValue.upper() + '%' sql = "select student_info_id,surname,givenname from student_info where UPPER(givenname) like %s or UPPER(surname) like %s order by surname" cursor.execute(sql, (join,join)) searchResult = [(id_, name(givenname, surname)) for id_, surname, givenname in cursor if permittedStudents is None or id_ in permittedStudents] cursor.close() cnx.close() if len(searchResult)< 1: empty = True status, studentName, institutionName, studentCourses, studentProjects = \ studentInfo(id) return templating.render("admin_students_search.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, STATUS=status, SEARCHRESULT=searchResult, SEARCHVALUE=searchValue, STUDENTNAME=studentName, INSTITUTIONNAME=institutionName, STUDENTCOURSES=studentCourses, STUDENTPROJECTS=studentProjects, EMPTYRESULT=empty, IS_ADMIN=isAdmin)
def admin_students_list(self, id=None, institution="", course=None, *args, **kwargs): """ Lists students under a institution and course. >>> authorizeTests() >>> self = Admin().admin_students_list >>> ret = self() >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> ret.STUDENTNAME, ret.STUDENTCOURSES ('No student selected', []) >>> ret = self(institution='2') >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> (1, 'SWEN302') in ret.OPTIONCOURSE True >>> ret.INSTITUTION '2' >>> ret.STUDENTNAME, ret.STUDENTCOURSES ('No student selected', []) >>> ret = self(institution='2', course='1') >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> ret.INSTITUTION '2' >>> (1, 'SWEN302') in ret.OPTIONCOURSE True >>> ret.COURSE '1' >>> (70, 'dave, dave') in ret.OPTIONSTUDENT True >>> ret.STUDENTNAME, ret.STUDENTCOURSES ('No student selected', []) >>> ret = self(70, '2', '1') >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> ret.INSTITUTION '2' >>> (1, 'SWEN302') in ret.OPTIONCOURSE True >>> ret.COURSE '1' >>> (70, 'dave, dave') in ret.OPTIONSTUDENT True >>> ret.STUDENTNAME 'dave dave' >>> ('Agile Methods', 'SWEN302', 2014, 1) in ret.STUDENTCOURSES True """ isAdmin, permittedCourses, permittedStudents = getAccessPermissions() allow(["HEAD", "GET", "POST"]) error = "" redirect = "NO" options = [] optionsCourse = [] optionsStudent = [] studentInstitution = "" status, studentName, studentInstitution, studentCourses, studentProjects = \ studentInfo(id, "No student selected") if institution: cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institutionid,institution_name from institution order by institution_name" ) cursor.execute(query) options = list(cursor) cursor.close() else: cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institutionid,institution_name from institution order by institution_name" ) cursor.execute(query) for (institutionid, institution_name) in cursor: options.append((institutionid, institution_name)) if institution == "": institution = str(institutionid) cursor.close() if course: cnx, status = db.connect() cursor = cnx.cursor() sql = "SELECT courseid,code from course where institutionid = %s" cursor.execute(sql, institution) optionsCourse = [ (courseid, code) for courseid, code in cursor if permittedCourses is None or courseid in permittedCourses ] cursor.close() else: cnx, status = db.connect() cursor = cnx.cursor() sql = "SELECT courseid,code from course where institutionid = %s" cursor.execute(sql, institution) for (courseid, code) in cursor: if permittedCourses is None or courseid in permittedCourses: optionsCourse.append((courseid, code)) if course == "": course = str(courseid) cursor.close() if course and (permittedCourses is None or course in permittedCourses): cnx, status = db.connect() cursor = cnx.cursor() sql = "SELECT distinct a.student_info_id,a.givenname,a.surname from student_info a,student_course_link b, course c, course_stream d where c.courseid = %s and c.courseid = d.courseid and d.coursestreamid =b.coursestreamid and b.studentinfoid = a.student_info_id" cursor.execute(sql, (course, )) for (student_info_id, givenname, surname) in cursor: optionsStudent.append( (student_info_id, name(givenname, surname))) if course == "": course = str(courseid) cursor.close() return templating.render("admin_students_list.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, STATUS=status, OPTION=options, INSTITUTION=institution, STUDENTNAME=studentName, STUDENTINSTITUTION=studentInstitution, STUDENTCOURSES=studentCourses, STUDENTPROJECTS=studentProjects, OPTIONCOURSE=optionsCourse, COURSE=course, OPTIONSTUDENT=optionsStudent, IS_ADMIN=isAdmin)
def admin_students_search(self, searchValue="", id=None, *args, **kwargs): """ Searches students by searchValue, displaying information for student number id. >>> authorizeTests() >>> self = Admin() >>> ret = self.admin_students_search() >>> ret.SEARCHRESULT, ret.SEARCHVALUE ([], '') >>> ret.STUDENTNAME, ret.INSTITUTIONNAME, ret.STUDENTCOURSES, ret.STUDENTPROJECTS ('', '', [], []) >>> ret = self.admin_students_search("dav") >>> ret.SEARCHVALUE 'dav' >>> (70, 'dave, dave') in ret.SEARCHRESULT True >>> ret.STUDENTNAME, ret.INSTITUTIONNAME, ret.STUDENTCOURSES, ret.STUDENTPROJECTS ('', '', [], []) >>> ret = self.admin_students_search("dav", 70) >>> ret.SEARCHVALUE 'dav' >>> (70, 'dave, dave') in ret.SEARCHRESULT True >>> ret.STUDENTNAME, ret.INSTITUTIONNAME ('dave dave', 'Victoria University of Wellington') >>> ('Agile Methods', 'SWEN302', 2014, 1) in ret.STUDENTCOURSES True """ isAdmin, _, permittedStudents = getAccessPermissions() allow(["HEAD", "GET", "POST"]) error = "" searchResult = [] redirect = "NO" status = "DB: Connection ok" studentCourses = [] studentProjects = [] empty = None if searchValue: cnx, status = db.connect() cursor = cnx.cursor() join = '%' + searchValue.upper() + '%' sql = "select student_info_id,surname,givenname from student_info where UPPER(givenname) like %s or UPPER(surname) like %s order by surname" cursor.execute(sql, (join, join)) searchResult = [ (id_, name(givenname, surname)) for id_, surname, givenname in cursor if permittedStudents is None or id_ in permittedStudents ] cursor.close() cnx.close() if len(searchResult) < 1: empty = True status, studentName, institutionName, studentCourses, studentProjects = \ studentInfo(id) return templating.render("admin_students_search.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, STATUS=status, SEARCHRESULT=searchResult, SEARCHVALUE=searchValue, STUDENTNAME=studentName, INSTITUTIONNAME=institutionName, STUDENTCOURSES=studentCourses, STUDENTPROJECTS=studentProjects, EMPTYRESULT=empty, IS_ADMIN=isAdmin)
def admin_institutions(self, institution="", *args, **kwargs): """ Lists available institutions. >>> authorizeTests() >>> self = Admin() >>> ret = self.admin_institutions() >>> ('Victoria University of Wellington', 2) in ret.OPTION True >>> ret = self.admin_institutions(2) >>> ret.INSTITUTION_ID, ret.INSTITUTION, ret.CONTACT, ret.WEBSITE, ret.DESCRIPTION (2, 'Victoria University of Wellington', None, None, None) """ userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) allow(["HEAD", "GET", "POST"]) redirect = "NO" options = [] if institution: cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institution_name, institutionid from institution order by institution_name" ) cursor.execute(query) options = list(cursor) cursor.close() cnx.close() displayInstitution = "" displayContact = "" displayWebsite = "" displayDescription = "" if institution == "": cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institution_name, institutionid from institution order by institution_name" ) cursor.execute(query) institution = "" for (institute) in cursor: options.append(institute) if institution == "": institution = institute[1] cursor.close() cnx.close() cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institution_name,description,contact,website from institution where institutionid = '" + str(institution) + "'") cursor.execute(query) displayInstitution, displayDescription, displayContact, displayWebsite = cursor.fetchone( ) cursor.close() cnx.close() return templating.render("admin_institutions.html", ROOT_URL=config.VIRTUAL_URL, ERROR="", REDIRECT=redirect, OPTION=options, INSTITUTION_ID=institution, INSTITUTION=displayInstitution, CONTACT=displayContact, WEBSITE=displayWebsite, DESCRIPTION=displayDescription, IS_ADMIN=isAdmin(userid))
def manage_admins(self, newadminid="", deleteadminid="", searchuser=None, newteacherid="", *args, **kwargs): """ Manage the admins. >>> self = manage_admins() >>> results = manage_admins() >>> results.ERROR '' >>> results.REDIRECT 'NO' >>> results.STATUS 'DB: Connection ok' """ adminUserid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(adminUserid) allow(["HEAD", "GET", "POST"]) message = "" redirect = "NO" adminList = [] userList = [] options = [] teacheroptions = [] cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT username, userid from whiley_user user order by username") cursor.execute(query) for (username, userid) in cursor: username_clean = ''.join(ch for ch in username if ch.isalnum()) options.append((username_clean, userid)) teacheroptions.append((username_clean, userid)) cursor.close() if searchuser is not None: cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT userid from whiley_user where username=%s") cursor.execute(query, (searchuser, )) userid = cursor.fetchone() if cursor.rowcount > 0: if not auth.create_admin(userid[0]): message = "User is an Admin already" else: message = "User does not exist" cursor.close() if newadminid == "": cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT username, user.userid from whiley_user user, admin_users admin where user.userid=admin.userid" ) cursor.execute(query) for (username, userid) in cursor: adminList.append((username, userid)) cursor.close() userid = None teacherList = [] teacherMessage = "" if newteacherid == "": cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT full_name, userid from teacher_info") cursor.execute(query) for (username, userid) in cursor: teacherList.append((username, userid)) cursor.close() userid = None return templating.render("manage_admins.html", ADMINLIST=adminList, TEACHERLIST=teacherList, TEACHEROPTION=teacheroptions, OPTION=options, MESSAGE=message, TEACHER_MESSAGE=teacherMessage, IS_ADMIN=isAdmin(adminUserid))
def user_courses(self, studentinfoid=None, institution="", validationcode="", courseid="", *args, **kwargs): """Assign user to select course """ if studentinfoid is None: raise cherrypy.HTTPRedirect("/") allow(["HEAD", "GET", "POST"]) error = False error_msg = " " redirect = "NO" options = [] course_list = [] if institution: cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institutionid,institution_name from institution order by institution_name" ) cursor.execute(query) options = list(cursor) cursor.close() if courseid: cnx, status = db.connect() cursor = cnx.cursor() error = insertuserdetails(studentinfoid, institution, courseid, validationcode) cursor.close() if error is False: message = "User Created, Welcome! Redirecting..." template = lookup.get_template("redirect.html") return template.render(STATUS="alert-success", MESSAGE=message) else: error_msg = "Wrong Validation Code" if institution == "": cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institutionid,institution_name from institution order by institution_name" ) cursor.execute(query) for (institutionid, institution_name) in cursor: options.append((institutionid, institution_name)) if institution == "": institution = str(institutionid) cursor.close() ##get courses list cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT courseid,code from course where institutionid = '" + institution + "' order by code") cursor.execute(query) course_list = list(cursor) cursor.close() return templating.render("user_institutions.html", ERROR=error, ERRORMSG=error_msg, NOTALLOWED=False, ROOT_URL=config.VIRTUAL_URL, OPTION=options, COURSE_LIST=course_list, STUDENTINFOID=studentinfoid, INSTITUTION=institution)
def manage_admins(self, newadminid="", deleteadminid="", searchuser=None, newteacherid="", *args, **kwargs): """ Manage the admins. >>> self = manage_admins() >>> results = manage_admins() >>> results.ERROR '' >>> results.REDIRECT 'NO' >>> results.STATUS 'DB: Connection ok' """ adminUserid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(adminUserid) allow(["HEAD", "GET", "POST"]) message = "" redirect = "NO" adminList = [] userList = [] options = [] teacheroptions = [] cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT username, userid from whiley_user user order by username") cursor.execute(query) for (username, userid) in cursor: username_clean = ''.join(ch for ch in username if ch.isalnum()) options.append((username_clean,userid)) teacheroptions.append((username_clean,userid)) cursor.close() if searchuser is not None: cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT userid from whiley_user where username=%s") cursor.execute(query,(searchuser,)) userid = cursor.fetchone() if cursor.rowcount > 0: if not auth.create_admin(userid[0]): message = "User is an Admin already" else: message = "User does not exist" cursor.close() if newadminid == "": cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT username, user.userid from whiley_user user, admin_users admin where user.userid=admin.userid") cursor.execute(query) for (username, userid) in cursor: adminList.append((username,userid)) cursor.close() userid = None teacherList = [] teacherMessage = "" if newteacherid == "": cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT full_name, userid from teacher_info") cursor.execute(query) for (username, userid) in cursor: teacherList.append((username,userid)) cursor.close() userid = None return templating.render("manage_admins.html", ADMINLIST=adminList, TEACHERLIST=teacherList,TEACHEROPTION=teacheroptions,OPTION=options, MESSAGE=message, TEACHER_MESSAGE=teacherMessage, IS_ADMIN=isAdmin(adminUserid))
def admin_courses(self, institution="", *args, **kwargs): """ Lists all available courses. >>> authorizeTests() >>> self = Admin() >>> ret = self.admin_courses() >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> ret = self.admin_courses('2') >>> (2, 'Victoria University of Wellington') in ret.OPTION True >>> ret.INSTITUTION '2' >>> (1, 'SWEN302') in ret.COURSE_LIST True """ userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) allow(["HEAD", "GET", "POST"]) error = "" redirect = "NO" options = [] course_list = [] if institution: cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institutionid,institution_name from institution order by institution_name" ) cursor.execute(query) options = list(cursor) cursor.close() else: cnx, status = db.connect() cursor = cnx.cursor() query = ( "SELECT institutionid,institution_name from institution order by institution_name" ) cursor.execute(query) for (institutionid, institution_name) in cursor: options.append((institutionid, institution_name)) if institution == "": institution = str(institutionid) cursor.close() cnx, status = db.connect() cursor = cnx.cursor() query = ("SELECT courseid,code from course where institutionid = '" + institution + "' order by code") cursor.execute(query) course_list = list(cursor) cursor.close() return templating.render("admin_courses.html", ROOT_URL=config.VIRTUAL_URL, ERROR=error, REDIRECT=redirect, OPTION=options, INSTITUTION=institution, COURSE_LIST=course_list, IS_ADMIN=isAdmin(userid))