def send_survey_reminder_command():
if not click.confirm(
"You are about to send an email to A LOT OF PEOPLE. Are you sure?"
):
return
db = get_db()
count = 0
# get all syndicates where somebody has not filled in the form
syndicate_list = db.execute(
"SELECT crsid FROM user WHERE syndicate NOT NULL AND has_filled = 0"
" GROUP BY syndicate")
for syndicate in syndicate_list:
syndicate_data = get_syndicate_for_user(syndicate["crsid"])
email_recipients = (
list(map(lambda x: x["crsid"], syndicate_data["others"])) +
[syndicate_data["owner"]])
survey_reminder_mail(email_recipients)
count += len(email_recipients)
print("Queued %d emails" % count)
print("They will send on the next run of send-emails")
def is_syndicatable(crsid, year):
# Checks if a user is in a fit state to join a syndicate
db = get_db()
user_data = db.execute("SELECT syndicate, year FROM user WHERE crsid=?",
(crsid, )).fetchone()
if not user_data:
return {"ok": False, "reason": "This person is not in the ballot."}
if user_data["syndicate"]:
return {
"ok": False,
"reason": "This user is already part of another syndicate."
}
if user_data["year"] != year:
return {"ok": False, "reason": "This user is not in your year."}
invite_data = db.execute(
"SELECT id FROM syndicate_invitation WHERE recipient=? AND used=0",
(crsid, )).fetchone()
if invite_data:
return {
"ok":
False,
"reason":
("This user has already been invited to another syndicate. Please allow them to deal with life's "
"dillemas one at a time.")
}
full_name = get_user(crsid)
return {"ok": True, "name": full_name}
def import_survey_data(data):
if not data:
return abort(400)
if "CRSid" not in data:
return abort(400)
if "FormToken" not in data:
return abort(400)
db = get_db()
user_data = db.execute(
"SELECT formtoken, has_filled FROM user WHERE crsid=?",
(data["CRSid"], )).fetchone()
if not user_data:
log(data["CRSid"], "submitted the form despite not existing (WARN)")
return abort(400)
if user_data["formtoken"] != data["FormToken"]:
log(data["CRSid"],
"received a response with an incorrect FormToken (WARN)")
return abort(400)
if user_data["has_filled"] == 1:
log(data["CRSid"], "filled in the form more than once (WARN)")
log(data["CRSid"], "filled in the form ok")
db.execute("UPDATE user SET has_filled=1 WHERE crsid=?", (data["CRSid"], ))
db.commit()
return "OK\n"
def queue_email(to, subject, body):
db = get_db()
db.execute(
"INSERT INTO mail_queue (recipient, subject, body) VALUES (?, ?, ?)",
(to, subject, body))
db.commit()
def get_allocation_for_user(crsid):
db = get_db()
room_data = db.execute("SELECT * FROM room WHERE inhabitant=?",
(crsid, )).fetchone()
if room_data is None:
return None
return room_data["shortname"]
def get_survey_data(crsid):
db = get_db()
survey_data = db.execute(
'SELECT has_filled, formtoken FROM user WHERE crsid=?',
(crsid, )).fetchone()
return survey_data
def has_reviewed(crsid):
db = get_db()
review = db.execute("SELECT author FROM review WHERE author=?",
(crsid, )).fetchone()
if review is None:
return False
else:
return True
def write_review(author, room, form_data):
db = get_db()
db.execute(
"INSERT INTO review (author, room, layout_rating, facilities_rating, noise_rating, overall_rating, title, body) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
(author, room, int(form_data["layout-rating"][:1]),
int(form_data["facilities-rating"][:1]),
int(form_data["noise-rating"][:1]),
int(form_data["overall-rating"][:1]), form_data["title"],
form_data["text"]))
db.commit()
def get_user(crsid):
# returns full name if user exists, else false
db = get_db()
user_data = db.execute("SELECT forename,surname FROM user WHERE crsid=?",
(crsid, )).fetchone()
if user_data:
return user_data["forename"] + " " + user_data["surname"]
return False
def get_year(crsid):
# returns the number of the year of the given user
db = get_db()
user_data = db.execute("SELECT year FROM user WHERE crsid=?",
(crsid, )).fetchone()
if not user_data:
return False
return user_data["year"]
def send_review_reminder_command():
if not click.confirm(
"You are about to send an email to EVERYBODY. Are you sure?"):
return
db = get_db()
rooms = db.execute("SELECT * FROM room").fetchall()
count = 0
for room in rooms:
if room["inhabitant"] is None:
continue
review_mail(room["inhabitant"], room["shortname"])
count += 1
print("Queued %d emails" % count)
print("They will be sent next time send-emails runs.")
def import_users_command():
db = get_db()
with open("users.csv", newline="") as users_file:
# csv format: crsid,forename,surname,year
# cf. https://github.com/dowjcr/ucam-lookup-byyear
count = 0
reader = csv.reader(users_file)
for row in reader:
db.execute(
"INSERT INTO user (crsid, forename, surname, year, formtoken) VALUES (?, ?, ?, ?, ?)",
(row[0], row[1], row[2], row[3], ''.join(
random.SystemRandom().choice(string.ascii_letters +
string.digits)
for _ in range(32))))
db.commit()
count += 1
print("Imported", str(count), "users")
def send_emails_command():
# Right now only sending over plaintext to the local mail server is supported
SMTP_HOST = "localhost"
SMTP_PORT = 25
SMTP_SENDER = "*****@*****.**"
SMTP_REPLYTO = "*****@*****.**"
db = get_db()
emails = db.execute("SELECT * FROM mail_queue").fetchall()
with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
for email in emails:
server.sendmail(
SMTP_SENDER, email["recipient"],
"Subject: " + email["subject"] + "\nTo: " +
email["recipient"] + "\nFrom: " + SMTP_SENDER +
"\nReply-To: " + SMTP_REPLYTO + "\n\n" + email["body"])
db.execute("DELETE FROM mail_queue WHERE id=?", (email["id"], ))
db.commit()
def import_allocations_command():
db = get_db()
with open("allocations.csv", newline="") as allocations_file:
reader = csv.reader(allocations_file)
# Skip the file header
next(reader)
# Some rooms don't have IDs, so we need to generate them within a safe range
next_id = 1001
for row in reader:
id = -1
if (int(row[4]) == -1):
id = next_id
next_id += 1
else:
id = int(row[4])
print(id, row[3], row[0])
db.execute(
"INSERT INTO room (id, shortname, inhabitant) VALUES (?, ?, ?)",
(id, row[3], row[0]))
db.commit()
def log_survey_data(raw_data):
db = get_db()
db.execute("INSERT INTO form_payload (time, json) VALUES (?, ?)",
(int(time.time()), raw_data))
db.commit()
def log(crsid, message):
db = get_db()
db.execute("INSERT INTO log_message (user, message, time) VALUES (?, ?, ?)", (crsid, message, int(time.time())))
db.commit()
