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 add_popular_category(current_user): if isAdmin(current_user): if 'file' not in request.files: return "No file part", 205 file = request.files["file"] popular_category_name = request.form['name'] if file.filename == "": return "No images selected", 205 if file and allowed_file(file.filename): filename = secure_filename(file.filename) if os.path.exists("images/popular_categories/" + str(file.filename)): os.remove("images/popular_categories/" + str(file.filename)) file.save( os.path.join(app.config["POPULAR_CATEGORIES"], file.filename)) db = mysql_db.get_db() cursor = db.cursor() cursor.execute( "INSERT INTO popular_categories (name, image) VALUES(%s, %s) ", (popular_category_name, file.filename)) db.commit() # print("File uploaded: " + filename + " to test_images/profile_photos") return jsonify({"message": "Added"}), 200 else: return jsonify({"message": "Not authorized"}), 401
def edit_popular_category(current_user): if isAdmin(current_user): if 'file' not in request.files: return "No file part found", 205 file = request.files['file'] popular_category_name = request.form['name'] id = request.form['id'] if file.filename == "": return "No images selected", 205 if file and allowed_file(file.filename): filename = secure_filename(file.filename) if os.path.exists("images/popular_categories/" + str(file.filename)): os.remove("images/popular_categories/" + str(file.filename)) # Remove old image db = mysql_db.get_db() cursor = db.cursor() cursor.execute("SELECT image FROM popular_categories WHERE id=%s", (id, )) old_name = cursor.fetchone() try: os.remove("images/popular_categories/" + str(old_name)) except FileNotFoundError as fne: print(fne) cursor.execute( "UPDATE popular_categories SET name=%s, image=%s WHERE id=%s", (popular_category_name, str(file.filename), id)) file.save( os.path.join(app.config["POPULAR_CATEGORIES"], file.filename)) db.commit() return jsonify({"message": "Edited"}), 200 else: return jsonify({"message": "Not authorized"}), 401
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 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 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 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 get_all_admins(current_user): if isAdmin(current_user ): # returns True if user has attribute of admin(bool)==True db = mysql_db.get_db().cursor() db.execute("SELECT * FROM admins") admins = db.fetchall() return jsonify(admins), 200 else: return jsonify({'message': 'Not verified'}), 401
def get_admin_orders_by_status(current_user, status): if isAdmin(current_user): cursor = mysql_db.get_db().cursor() cursor.execute( "SELECT o.id, o.customer_id, o.order_status_code, o.order_date, SUM(oi.order_item_quantity) order_item_quantity, SUM(oi.order_item_price) total, u.username FROM orders o INNER JOIN order_items oi ON o.id = oi.order_id INNER JOIN users u ON o.customer_id=u.id WHERE o.order_status_code=%s GROUP BY o.id", (status, )) orders = cursor.fetchall() return jsonify(orders) else: return jsonify({"message": "Not authorized"}), 401
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 add_category(current_user): if isAdmin(current_user): data = dict(request.json) db = mysql_db.get_db() cursor = db.cursor() cursor.execute("INSERT INTO categories VALUES (0, %s)", data['category_name']) db.commit() return jsonify({"message": "Category added"}), 200 else: return jsonify({"message": "Not authorized"}), 401
def edit_category(current_user, id): if isAdmin(current_user): data = dict(request.json) db = mysql_db.get_db() cursor = db.cursor() cursor.execute("UPDATE categories SET category_name=%s WHERE id=%s", (data['category_name'], id)) db.commit() return jsonify({"messsage": "Category name updated"}), 200 else: return jsonify({"message": "Not authorized"}), 401
def add_subcategory(current_user): if isAdmin(current_user): data = dict(request.json) db = mysql_db.get_db() cursor = db.cursor() cursor.execute( "INSERT INTO sub_categories (category_id, sub_category_name) VALUES (%(category_id)s, %(sub_category_name)s)", data) db.commit() return jsonify({"message": "Category added"}), 200 else: return jsonify({"message": "Not authorized"}), 401
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 delete_popular_category(current_user, id): if isAdmin(current_user): db = mysql_db.get_db() cursor = db.cursor() cursor.execute("SELECT * FROM popular_categories WHERE id=%s", (id, )) cat = cursor.fetchone() try: os.remove("images/popular_categories/" + str(cat['image'])) except FileNotFoundError as fne: print(fne) cursor.execute("DELETE FROM popular_categories WHERE id=%s", (id, )) db.commit() return jsonify({"message": "Deleted"})
def getAccessPermissions(): userid = cherrypy.session.get(auth.SESSION_USERID) if auth.isAdmin(userid): return True, None, None elif auth.isTeacher(userid): cnx, status = db.connect() cursor = cnx.cursor() sql = """select tc.courseid from teacher_course_link tc, teacher_info t where tc.teacherinfoid = t.teacherid and t.userid = %s""" cursor.execute(sql, (userid,)) courses = [ret[0] for ret in cursor.fetchall()] sql = """select sc.studentinfoid from teacher_info t, teacher_course_link tc, course_stream cs, student_course_link sc where t.userid = %s and tc.teacherinfoid = t.teacherid and tc.courseid = cs.courseid and cs.coursestreamid = sc.coursestreamid""" cursor.execute(sql, (userid,)) students = [ret[0] for ret in cursor.fetchall()] return False, courses, students else: raise cherrypy.HTTPRedirect("/auth/login")
def getAccessPermissions(): userid = cherrypy.session.get(auth.SESSION_USERID) if auth.isAdmin(userid): return True, None, None elif auth.isTeacher(userid): cnx, status = db.connect() cursor = cnx.cursor() sql = """select tc.courseid from teacher_course_link tc, teacher_info t where tc.teacherinfoid = t.teacherid and t.userid = %s""" cursor.execute(sql, (userid, )) courses = [ret[0] for ret in cursor.fetchall()] sql = """select sc.studentinfoid from teacher_info t, teacher_course_link tc, course_stream cs, student_course_link sc where t.userid = %s and tc.teacherinfoid = t.teacherid and tc.courseid = cs.courseid and cs.coursestreamid = sc.coursestreamid""" cursor.execute(sql, (userid, )) students = [ret[0] for ret in cursor.fetchall()] return False, courses, students else: raise cherrypy.HTTPRedirect("/auth/login")
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 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_teacher_add(self, id, login="", staffid="", full_name="", preferred_name="", *args, **kwargs): userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) if request.method == 'POST' and login and staffid and full_name and preferred_name: cnx, status = db.connect() cursor = cnx.cursor() query = "SELECT userid FROM whiley_user WHERE username = %s" cursor.execute(query, (id, )) id = cursor.fetchone()[0] cursor.close() cnx.close() auth.create_teacher(id, login, staffid, full_name, preferred_name) return templating.render("redirect.html", STATUS="alert-success", MESSAGE="Teacher rights added...") else: # prefill login if not login: login = id return templating.render("admin_add_teacher.html", USERID=id, LOGIN=login, STAFFID=staffid, FULLNAME=full_name, PREFERRED_NAME=preferred_name, IS_ADMIN=isAdmin(userid))
def admin_teacher_add(self, id, login="", staffid="", full_name="", preferred_name="", *args, **kwargs): userid = cherrypy.session.get(auth.SESSION_USERID) requireAdmin(userid) if request.method == 'POST' and login and staffid and full_name and preferred_name: cnx, status = db.connect() cursor = cnx.cursor() query = "SELECT userid FROM whiley_user WHERE username = %s" cursor.execute(query, (id,)) id = cursor.fetchone()[0] cursor.close() cnx.close() auth.create_teacher(id, login, staffid, full_name, preferred_name) return templating.render("redirect.html", STATUS="alert-success", MESSAGE="Teacher rights added...") else: # prefill login if not login: login = id return templating.render("admin_add_teacher.html", USERID=id, LOGIN=login, STAFFID=staffid, FULLNAME=full_name, PREFERRED_NAME=preferred_name, IS_ADMIN=isAdmin(userid))
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_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 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_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 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))