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()
