def resolveRegradeRequest(): if not is_staff(get_course()): return dict(success=False) if request.method == "GET": return dict(success=False) email = request.get_json().get("email") assignment = request.get_json().get("assignment") backup_id = request.get_json().get("backup_id") resolution = request.get_json().get("resolution").lower() reason = request.get_json().get("reason") subject = f"Regrade Request for {assignment}" email_preview = request.get_json().get("email_preview") with connect_db() as db: db( """UPDATE regrade_requests SET status=%s, resolution_reason=%s, emailed=%s WHERE courseCode=%s AND email=%s AND assignment=%s AND backup_id=%s""", [ resolution, reason, "yes", get_course(), email, assignment, backup_id ], ) if not DEV: send_email( sender=f"CS 61A <*****@*****.**>", target=email, subject=subject, body=email_preview, _impersonate="mail", ) return dict(success=True)
def submitRegradeRequest(): if not is_logged_in(): return dict(success=False) if request.method == "GET": return dict(success=False) email = request.get_json().get("email") assignment = request.get_json().get("assignment") with connect_db() as db: status = db( "SELECT status FROM regrade_requests WHERE courseCode=%s AND email=%s AND assignment=%s", [get_course(), email, assignment], ).fetchone() if status: status = status[0] if status and status not in ("needs followup"): return dict(success=False) backup_id = request.get_json().get("backup_id") description = request.get_json().get("description") ta = request.form.get("ta") status = "requested" with connect_db() as db: db( """INSERT INTO regrade_requests ( courseCode, email, assignment, backup_id, description, assigned_to, status ) VALUES (%s, %s, %s, %s, %s, %s, %s)""", [ get_course(), email, assignment, backup_id, description, ta, status ], ) return dict(success=True)
def getRegradeRequests(): if not is_staff(get_course()): return dict(success=False) with connect_db() as db: can_access_all_regrades = (True if DEV else can_user( course=get_course(), email=get_user()["email"], action="access_all_regrades", )) if can_access_all_regrades: regrade_requests = db( "SELECT email, assignment, backup_id, description, status FROM regrade_requests WHERE courseCode=%s", [get_course()], ) else: regrade_requests = db( "SELECT email, assignment, backup_id, description, status FROM regrade_requests WHERE courseCode=%s AND assigned_to=%s", [get_course(), get_user()["email"]], ) data = [ dict( email=row[0], assignment=row[1], backup_id=row[2], description=row[3], status=row[4], ) for row in regrade_requests ] return jsonify(data)
def refresh_state(): config = CourseConfig.query.filter_by(course=get_course()).one_or_none() if config is None: config = CourseConfig(course=get_course()) db.session.add(config) db.session.commit() out = { "enrolledSection": None, "taughtSections": None, "sections": [], "currentUser": None, "config": config.json, "custom": None, } if current_user.is_authenticated: out["enrolledSection"] = ( current_user.sections[0].json if current_user.sections else None ) out["taughtSections"] = [ section.json for section in sorted(current_user.sections_taught, key=section_sorter) ] out["sections"] = [ section.json for section in sorted(Section.query.all(), key=section_sorter) ] out["currentUser"] = current_user.full_json return out
def refresh(): with connect_db() as db: db("DELETE FROM shortlinks WHERE course=%s", [get_course()]) sheets = db( "SELECT url, sheet, secure FROM sources WHERE course=(%s)", [get_course()] ).fetchall() data = [] for url, sheet, secure in sheets: try: csvr = read_spreadsheet(url=url, sheet_name=sheet) except: return error(f"Failed to read spreadsheet {url} (Sheet: {sheet})") headers = [x.lower() for x in csvr[0]] for row in csvr[1:]: row = row + [""] * 5 shortlink = row[headers.index("shortlink")] url = row[headers.index("url")] creator = row[headers.index("creator")] data.append([shortlink, url, creator, secure, get_course()]) with connect_db() as db: db( "INSERT INTO shortlinks (shortlink, url, creator, secure, course) VALUES (%s, %s, %s, %s, %s)", data, ) return html("Links updated")
def set_grades_secret_route(): if validate_secret(secret=request.form.get("secret")) != get_course(): return jsonify({"success": False}) data = request.form.get("data") with transaction_db() as db: set_grades(data, get_course(), db) return jsonify({"success": True})
def set_config(): if not is_staff(get_course()): return jsonify({"success": False}) data = request.form.get("data") with connect_db() as db: db("DELETE FROM configs WHERE courseCode=%s", [get_course()]) db("INSERT INTO configs VALUES (%s, %s)", [get_course(), data]) return jsonify({"success": True})
def set_grades_route(): if not is_staff(get_course()): return jsonify({"success": False}) data = request.form.get("data") with transaction_db() as db: set_grades(data, get_course(), db) return jsonify({"success": True})
def query(): try: if is_logged_in(): user = get_user() email = user["email"] target = request.args.get("target", None) if is_staff(get_course()): if target: email = target else: all_students = [] with connect_db() as db: lookup = db( "SELECT shortData FROM students WHERE courseCode=%s", [get_course()], ).fetchall() for row in lookup: parsed = json.loads(row[0]) all_students.append(parsed) return jsonify({ "success": True, "isStaff": True, "allStudents": all_students, "email": user["email"], "name": user["name"], "lastUpdated": last_updated(), }) with connect_db() as db: [short_data, data] = db( "SELECT shortData, data FROM students WHERE courseCode=%s AND email=%s", [get_course(), email], ).fetchone() [header ] = db("SELECT header FROM headers WHERE courseCode=%s", [get_course()]).fetchone() short_data = json.loads(short_data) data = json.loads(data) header = json.loads(header) return jsonify({ "success": True, "header": header, "data": data, "email": short_data["Email"], "name": short_data["Name"], "SID": short_data["SID"], "lastUpdated": last_updated(), }) else: return jsonify({"success": False, "retry": True}) except Exception: pass return jsonify({"success": False, "retry": False})
def is_authorized(secure: AccessRestriction): if secure == AccessRestriction.ALL: return True elif secure == AccessRestriction.STAFF: return is_staff(get_course()) elif secure == AccessRestriction.STUDENT: return is_enrolled(get_course()) else: raise Exception(f"{secure} is not a valid AccessRestriction")
def set_config(): if not is_staff(get_course()): return jsonify({"success": False}) if not DEV and not can_user( course=get_course(), email=get_user()["email"], action="configure_howamidoing", ): return jsonify({"success": False}) data = request.form.get("data") with connect_db() as db: db("DELETE FROM configs WHERE courseCode=%s", [get_course()]) db("INSERT INTO configs VALUES (%s, %s)", [get_course(), data]) return jsonify({"success": True})
def user_from_email(name, email, is_staff): """Get a User with the given email, or create one.""" from common.course_config import get_course user = User.query.filter_by(email=email, course=get_course()).one_or_none() if not user: user = User(name=name, email=email, course=get_course(), is_staff=is_staff) else: user.name = name user.is_staff = is_staff user.course = get_course() db.session.add(user) db.session.commit() return user
def set_grades_route(): if not is_staff(get_course()): return jsonify({"success": False}) if not DEV and not can_user( course=get_course(), email=get_user()["email"], action="configure_howamidoing", ): return jsonify({"success": False}) data = request.form.get("data") with transaction_db() as db: set_grades(data, get_course(), db) return jsonify({"success": True})
def all_scores(): if not is_staff(get_course()): return jsonify({"success": False}) with connect_db() as db: [header] = db("SELECT header FROM headers WHERE courseCode=%s", [get_course()]).fetchone() header = json.loads(header) data = db("SELECT data FROM students WHERE courseCode=%s", get_course()).fetchall() scores = [] for [score] in data: score = json.loads(score) scores.append(score) return jsonify({"header": header, "scores": scores})
def remove_source(): if not is_staff(get_course()): return login() url = request.form["url"] sheet = request.form["sheet"] with connect_db() as db: db( "DELETE FROM sources WHERE url=%s AND sheet=%s AND course=%s", [url, sheet, get_course()], ) return redirect(url_for("index"))
def add_source(): if not is_staff(get_course()): return login() url = request.form["url"] sheet = request.form["sheet"] secure = True if request.form.get("secure", False) else False with connect_db() as db: db( "INSERT INTO sources VALUES (%s, %s, %s, %s)", [url, sheet, secure, get_course()], ) return redirect(url_for("index"))
def is_authorized(secure: AccessRestriction): """Returns authorization status based on the given access restriction. :param secure: access restriction :type secure: AccessRestriction :return: authorization status (``True`` or ``False``) """ if secure == AccessRestriction.ALL: return True elif secure == AccessRestriction.STAFF: return is_staff(get_course()) elif secure == AccessRestriction.STUDENT: return is_enrolled(get_course()) else: raise Exception(f"{secure} is not a valid AccessRestriction")
def handler(path): url, creator, secure = lookup(path) if not url: return error("Target not found!") if secure and not is_staff(get_course()): return login() return redirect(add_url_params(url, request.query_string.decode("utf-8")))
def lookup(path): with connect_db() as db: target = db( "SELECT url, creator, secure FROM shortlinks WHERE shortlink=%s AND course=%s", [path, get_course()], ).fetchone() return list(target) if target else (None, None, None)
def last_updated(): try: with connect_db() as db: return db( "SELECT lastUpdated from lastUpdated where courseCode=%s", [get_course()], ).fetchone()[0] except: return "Unknown"
def for_user(cls, user): if user and user.is_authenticated: from common.course_config import get_course return cls.query.filter( cls.user_id == user.id, cls.course == get_course(), cls.status.in_([TicketStatus.pending, TicketStatus.assigned]), ).one_or_none()
def lookup(path): with connect_db() as db: target = db( "SELECT url, creator, secure FROM shortlinks WHERE shortlink=%s AND course=%s", [path, get_course()], ).fetchone() if target: target = list(target) target[2] = AccessRestriction(target[2]) return target or (None, None, None)
def index(): if not is_staff(get_course()): return login() with connect_db() as db: sources = db( "SELECT url, sheet, secure FROM sources WHERE course=%s", [get_course()] ).fetchall() insert_fields = """<input placeholder="Spreadsheet URL" name="url"></input> <input placeholder="Sheet Name" name="sheet"></input> <label> <input type="checkbox" name="secure"></input> Require Authentication </label>""" sources = "<br/>".join( make_row( f'<a href="{url}">{url}</a> {sheet} (Secure: {secure})' f'<input name="url" type="hidden" value="{url}"></input>' f'<input name="sheet" type="hidden" value="{sheet}"></input>', url_for("remove_source"), ) for url, sheet, secure in sources ) return html( f""" <h2>Course: <code>{get_course()}</code></h2> Each spreadsheet should be shared with the 61A service account <a href="mailto:[email protected]"> [email protected]</a>. They should have three columns with the headers: "URL", "Shortlink", and "Creator". <p> Visit <a href="{url_for("refresh")}">{url_for("refresh")}</a> (no auth required) after adding a link to synchronize with the spreadsheets. <h3>Sources</h3> {sources} <h3>Add Sources</h3> {make_row(insert_fields, url_for("add_source"), "Add")} """ )
def preview(path): url, creator, secure = lookup(path) if url is None: return html("No such link exists.") if secure and not is_staff(get_course()): return login() return html( 'Points to <a href="{0}">{0}</a> by {1}'.format( add_url_params(url, request.query_string.decode("utf-8")), creator ) )
def canRequestRegrade(): if not is_logged_in(): return dict(canRegrade=False) email = request.args.get("email", "") assignment = request.args.get("name", "") with connect_db() as db: status = db( "SELECT status FROM regrade_requests WHERE courseCode=%s AND email=%s AND assignment=%s", [get_course(), email, assignment], ).fetchone() if status: status = status[0] return dict(canRegrade=(not status or status in ("needs followup")))
def authorized(): from common.course_config import get_endpoint message = request.args.get("error") if message: message = "Ok OAuth error: %s" % (message) return redirect(url_for("error", message=message)) try: auth_resp = auth.ok_auth.authorized_response() if auth_resp is None: message = "Invalid Ok response: %s" % (message) return redirect(url_for("error", message=message)) except OAuthException as ex: message = str(ex) return redirect(url_for("error", message=message)) token = auth_resp["access_token"] session["access_token"] = (token, "") # (access_token, secret) info = auth.ok_auth.get("user").data["data"] email = info["email"] name = info["name"] if not name: name = email if ", " in name: last, first = name.split(", ") name = first + " " + last is_staff = False offering = get_endpoint() for p in info["participations"]: if p["course"]["offering"] == offering: if p["role"] != "student": is_staff = True else: is_staff = False break else: if ( ConfigEntry.query.filter_by( course=get_course(), key="only_registered_students" ) .one() .value == "true" ): return redirect( url_for( "error", message="Only registered students can log in", ) ) user = user_from_email(name, email, is_staff) return authorize_user(user)
def lookup(path): """Looks up a path in the database. :param path: path to look up :return: result of lookup, or ``(None, None, None)`` upon failure. """ with connect_db() as db: target = db( "SELECT url, creator, secure FROM shortlinks WHERE shortlink=%s AND course=%s", [path, get_course()], ).fetchone() if target: target = list(target) target[2] = AccessRestriction(target[2]) return target or (None, None, None)
def last_updated(): """Finds the timestamp of when the current database was last updated for this course. Uses a database query function yielded by :func:`common.db.connect_db` and the course code returned by :func:`common.course_config.get_course` :return: Timestamp or ``Unknown`` (string) if any exceptions occur while fetching from the current database """ try: with connect_db() as db: return db( "SELECT lastUpdated from lastUpdated where courseCode=%s", [get_course()], ).fetchone()[0] except: return "Unknown"
def login(): user_data = get_user() user = User.query.filter_by(email=user_data["email"]).one_or_none() if user is None: user = User(email=user_data["email"], name=user_data["name"], is_staff=False) db.session.add(user) user.name = user_data["name"] or user_data["email"] for participation in user_data["participations"]: if participation["course"]["offering"] == get_endpoint(): break else: if getenv("ENV") == "prod": return user.is_staff = is_staff("cs61a" if dev else get_course()) db.session.commit() login_user(user)
def load_user(user_id): return User.query.filter_by(id=user_id, course=get_course()).one_or_none()