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