def get_latlong_json():
# Get speed and timestamp data from database
db.getCur().execute(
"""SELECT unix_timestamp,latitude,longitude from obdreadings ORDER BY unix_timestamp DESC LIMIT 100;"""
) # LIMIT 500 OFFSET 100;""")
# Start with callback handler
callback_str = request.args.get('callback')
if callback_str is not None:
result_str = callback_str
print(callback_str)
else:
result_str = 'callback'
result_str += '(['
# TODO consider jsonify here
for record in db.getCur():
unix_time = record[0]
lat = record[1]
long = record[2]
result_str += '[{},{},{}],\n'.format(unix_time, lat, long)
# Append end part
result_str += ']);'
# Create and send response
response = Response(result_str)
response.headers['Content-Type'] = 'application/json'
response.headers['Access-Control-Allow-Origin'] = '*'
return response
def post(self, id):
tmt_fields = EditTournamentHandler.tmt_fields
tmt_attr_fields = [f for f in tmt_fields if f != 'Id']
state = {}
for f in tmt_fields:
state[f] = self.get_argument(f, None)
if self.get_is_admin() or int(state['Owner']) == int(
self.current_user):
msg = 'Updated tournament {}'.format(state['Id'])
if id == state['Id']:
with db.getCur() as cur:
try:
for f in tmt_attr_fields:
sql = ("UPDATE Tournaments SET {} = ?"
" WHERE Id = ?").format(f)
cur.execute(sql, (state[f], id))
return self.write({'status': 'success', 'message': ''})
except sqlite3.DatabaseError as e:
print('Error updating tournament ({}): {}'.format(
sql, e))
return self.write({
'status':
'Error',
'message':
'Unable to update tournament {}: {}'.format(id, e)
})
elif id and int(state['Id']) < 0:
with db.getCur() as cur:
try:
sql = "DELETE FROM Tournaments WHERE Id = ?"
cur.execute(sql, (id, ))
return self.write({
'status': 'load',
'message': 'Tournament Deleted',
'URL': settings.PROXYPREFIX
})
except sqlite3.DatabaseError as e:
print('Error deleting tournament ({}): {}'.format(
sql, e))
return self.write({
'status':
'Error',
'message':
'Unable to update tournament {}: {}'.format(id, e)
})
else:
return self.write({
'status':
'Error',
'message':
'Inconsistent IDs {} and {}'.format(id, state['Id'])
})
else:
self.write({
'status':
'Error',
'message':
'You are not authorized to edit or create '
'that tournament.'
})
def post(self, q):
with db.getCur() as cur:
cur.execute("SELECT Quarter FROM Quarters WHERE Quarter = ?",
(q, ))
rows = cur.fetchall()
if len(rows) == 0:
self.render("message.html",
message="Quarter {0} not found".format(q),
title="Quarter Not Found",
next="Manage quarters",
next_url="/admin/quarters")
elif len(rows) == 1:
with db.getCur() as cur:
cur.execute("DELETE FROM Quarters WHERE Quarter = ?", (q, ))
self.render("message.html",
message="Quarter {0} deleted".format(q),
title="Quarter Deleted",
next="Manage quarters",
next_url="/admin/quarters")
leaderboard.genLeaderboard(scores.quarterDate(q))
else:
self.render("quarters.html",
message=("Error: Multiple quarters named {0} "
"found. See Adminstrator.").format(q),
quarters=rows)
def view_id(vehicleid):
# Get the vehicleid records from the database
db.getCur().execute("""SELECT * FROM obdreadings WHERE vehicleid=%s;""",
(vehicleid, ))
response = Response(str(db.getCur().fetchall()))
response.mimetype = 'text/plain'
return response
def view_ids():
# Get total number of database entries
db.getCur().execute("""SELECT COUNT(*) FROM obdreadings;""")
# Get the first item of the first tuple in the returned list
num_rows = (db.getCur().fetchall()[0])[0]
# Get a list of all stored vehicleids
db.getCur().execute("""SELECT DISTINCT vehicleid FROM obdreadings;""")
# Return id list
ids = db.getCur().fetchall()
# Since the above returns tuples, use a list comprehension to get the first elements
ids = [x[0] for x in ids]
# Get the most recent timestamp
db.getCur().execute(
"""SELECT unix_timestamp from obdreadings ORDER BY unix_timestamp DESC LIMIT 1;"""
)
timestamp = db.getCur().fetchone()[
0] # [0] because result is a tuple - get the first element
# Also convert timestamp to human-readable time
int_timestamp_secs = int(timestamp) / 1000
ts_utc = datetime.utcfromtimestamp(int_timestamp_secs).strftime(
'%Y-%m-%d %H:%M:%S')
ts_local = datetime.fromtimestamp(int_timestamp_secs).strftime(
'%Y-%m-%d %H:%M:%S')
return render_template('view.html',
num_rows=num_rows,
ids=ids,
ts_unix=timestamp,
ts_utc=ts_utc,
ts_local=ts_local)
def realtime():
# Get 20 most recent entries
db.getCur().execute(
"""SELECT * from obdreadings ORDER BY unix_timestamp DESC LIMIT 20""")
# Publish it
result_str = ''
for record in db.getCur():
result_str += '{}\n'.format(record)
# Create and send response
response = Response(result_str)
response.mimetype = 'text/plain'
return response
def sendNotifications():
with db.getCur() as cur:
# message players to be seated soon
cur.execute(
"SELECT Tables.Type, TableTypes.Players, COUNT(*) FROM Tables "
" INNER JOIN TableTypes ON Tables.Type = TableTypes.Type "
" WHERE "
" (ScheduledStart IS NULL AND (NOT Playing OR"
" strftime('%s', datetime('now', 'localtime')) - strftime('%s', Started) > 60 * (TableTypes.Duration - ?))) "
" OR (ScheduledStart IS NOT NULL AND strftime('%s', ScheduledStart) - strftime('%s', datetime('now', 'localtime')) < ?) "
" GROUP BY Tables.Type ",
(settings.NOTIFY_MINUTES, settings.NOTIFY_MINUTES)
)
tablecounts = cur.fetchall()
playersToNotify = []
for tabletype, count, playercount in tablecounts:
cols = ['Id', 'Phone', 'Notified']
cur.execute(
"SELECT {cols} FROM Queue "
" INNER JOIN People ON Queue.Person = People.Id "
" WHERE Queue.Type = ? "
" ORDER BY Added LIMIT ?".format(cols=",".join(cols)),
(tabletype, count * playercount)
)
playersToNotify += [dict(zip(cols, row)) for row in cur.fetchall()]
playersToNotify = [row for row in playersToNotify if not row['Notified'] and row['Phone'] and row['Phone'] != ""]
if settings.TWILIO_SID != "" and settings.TWILIO_AUTH != "" and settings.TWILIO_NUMBER != "":
try:
client = Client(settings.TWILIO_SID, settings.TWILIO_AUTH)
texted = []
for player in playersToNotify:
try:
message=settings.TEXT_FMT.format("in about {} minutes!".format(settings.NOTIFY_MINUTES))
client.messages.create(
to=player['Phone'],
from_=settings.TWILIO_NUMBER,
body=message
)
events.logEvent("textsent", player['Id'])
texted += [(player['Id'],)]
except:
print("Error sending Twilio message: ", sys.exc_info()[0])
events.logEvent("textfailed", player['Id'])
if len(texted) > 0:
with db.getCur() as cur:
cur.executemany("UPDATE People SET Notified = 1 WHERE Id = ?", texted)
except:
print("Error sending Twilio message: ", sys.exc_info()[0])
events.logEvent("twiliofailed")
def post(self):
email = self.get_argument('email', None)
password = self.get_argument('password', None)
next = self.get_argument("next", "/")
if not email or not password or email == "" or password == "":
self.render("login.html", message = "Please enter an email and password")
return
with db.getCur() as cur:
cur.execute("SELECT Id, Password FROM Users WHERE Email = LOWER(?)", (email,))
row = cur.fetchone()
if row is not None:
result = row[0]
passhash = row[1]
if pbkdf2_sha256.verify(password, passhash):
self.set_secure_cookie("user", str(result))
cur.execute("SELECT EXISTS(SELECT * FROM Admins WHERE Id = ?)", (result,))
if cur.fetchone()[0] == 1:
self.set_secure_cookie("admin", "1")
if result != None:
self.redirect(next)
return
self.render("login.html", message = "Incorrect email and password")
def get_current_user_name(self):
if (getattr(self, 'current_user_name', None)
and self.current_user == self.current_user_ID):
return self.current_user_name
self.current_user_ID = self.current_user
if self.current_user:
with db.getCur() as cur:
cur.execute("SELECT Email FROM Users WHERE Id = ?",
(self.current_user, ))
email = cur.fetchone()
if isinstance(email, tuple):
# Find unique prefix for account name
try:
acctname, domain = email[0].split('@', 1)
except ValueError as e:
acctname = email[0]
cur.execute("SELECT Email FROM Users WHERE Email like ?",
(acctname +
'%' if acctname != email[0] else acctname, ))
emails = [x[0] for x in cur.fetchall()]
for n in range(len(acctname), len(email[0])):
if sum([
1 if e.startswith(email[0][0:n]) else 0
for e in emails
]) <= 1:
acctname = email[0][0:n]
break
self.current_user_name = acctname
return self.current_user_name
return None
def playerGames(players, round = None):
numplayers = len(players)
playergames = dict()
query = """
SELECT COUNT(*) FROM Scores
INNER JOIN Scores AS Scores2 ON
Scores.GameId = Scores2.GameId AND
Scores.Round = Scores2.Round
WHERE Scores.PlayerId = ? AND Scores2.PlayerId = ?
"""
gbindings = []
if round is not None:
query += " AND Scores.Round < ?"
gbindings += [round]
with db.getCur() as c:
for i in range(numplayers):
for j in range(i + 1, numplayers):
bindings = [players[i]['Id'], players[j]['Id']]
games = c.execute(query, bindings + gbindings).fetchone()[0]
if games != 0:
playergames[(players[i]['Id'], players[j]['Id'])] = games
return playergames
def post(self):
round = self.get_argument('round', None)
left = self.get_argument('left', None)
right = self.get_argument('right', None)
ret = {"status":"error", "message":"Unknown error occurred"}
if left == right:
ret["message"] = "Can't swap a player with themself"
return self.write(ret)
with db.getCur() as cur:
cur.execute(
"""SELECT Id, Player, TableNum, Wind
FROM Seating
WHERE Tournament = ?
AND Round = ?
AND (Player = ? OR Player = ?)""",
(self.tournamentid, round, left, right)
)
rows = cur.fetchall()
if len(rows) != 2:
ret["message"] = "Can't find those two players in that round"
elif rows[0][2] == rows[1][2]: # Same table: swap winds
cur.execute("UPDATE Seating SET Wind = ? WHERE Id = ?", (rows[0][3], rows[1][0]))
cur.execute("UPDATE Seating SET Wind = ? WHERE Id = ?", (rows[1][3], rows[0][0]))
ret["status"] = "success"
ret["message"] = "Swapped seats"
else: # Differnt table: swap player IDs
cur.execute("UPDATE Seating SET Player = ? WHERE Id = ?", (rows[0][1], rows[1][0]))
cur.execute("UPDATE Seating SET Player = ? WHERE Id = ?", (rows[1][1], rows[0][0]))
ret["status"] = "success"
ret["message"] = "Swapped players"
return self.write(ret)
def getScores(gameid, getNames=False, unusedPoints=False):
with db.getCur() as cur:
columns = [
"Id", "PlayerId", "GameId", "Score", "RawScore", "Chombos", "Date",
"DeltaRating", "Rank"
]
queryColumns = [
"Scores.Id", "PlayerId", "GameId", "Score", "RawScore", "Chombos",
"Date", "DeltaRating", "Rank"
]
tables = ["Scores"]
if getNames:
columns += ['Name']
queryColumns += ['Name']
tables += ["JOIN Players ON PlayerId = Players.Id"]
conditions = ["GameId = ?"]
bindings = [gameid]
if not unusedPoints:
conditions += ["PlayerId != ?"]
bindings += [getUnusedPointsPlayerID()]
query = "SELECT {columns} FROM {tables} WHERE {conditions}".format(
columns=",".join(queryColumns),
tables=" ".join(tables),
conditions=" AND ".join(conditions))
cur.execute(query, bindings)
scores = [dict(zip(columns, row)) for row in cur.fetchall()]
return scores
def deltaRating(scores, player):
gamedate = scores[0]['Date']
unusedPointsPlayerID = getUnusedPointsPlayerID()
if player['PlayerId'] == unusedPointsPlayerID:
return 0
totalOppRating = 0
realPlayerCount = len(scores)
for score in scores:
if 'PlayerId' not in score: # score player's first game
score['Rating'] = settings.DEFAULT_RATING
elif score['PlayerId'] == unusedPointsPlayerID:
realPlayerCount -= 1
continue
if 'Rating' not in score:
score['Rating'] = playerRatingBeforeDate(score['PlayerId'],
gamedate)
if 'PlayerId' not in score or score['PlayerId'] != player['PlayerId']:
totalOppRating += score['Rating']
avgOppRating = totalOppRating / (realPlayerCount - 1)
with db.getCur() as cur:
cur.execute(
"SELECT COALESCE(COUNT(*), 0) FROM Scores"
" WHERE PlayerId = ? AND Date < ?",
(player['PlayerId'], gamedate))
gameCount = cur.fetchone()[0]
adjPlayer = max(1 - (gameCount * 0.008), 0.2)
return (player['uma'] * 2 + adjEvent *
(avgOppRating - player['Rating']) / 40) * adjPlayer
def playerRatingBeforeDate(playerId, gamedate):
with db.getCur() as cur:
cur.execute(
"SELECT COALESCE(SUM(DeltaRating), 0) + ? FROM Scores WHERE PlayerId = ? AND Date < ?;",
(settings.DEFAULT_RATING, playerId, gamedate))
return cur.fetchone()[0]
return settings.DEFAULT_RATING
def post(self):
email = self.get_argument('email', None)
if not re.match("^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]+$", email, flags = re.IGNORECASE):
self.render("invite.html", message = "Please enter a valid email address.")
else:
with db.getCur() as cur:
cur.execute("SELECT Email from Users where Email = ?", (email,))
try:
existing = cur.fetchone()[0]
self.render("message.html",
message = "Account for {0} already exists.".format(
email),
title="Duplicate Account")
return
except:
pass
code = util.randString(32)
cur.execute("INSERT INTO VerifyLinks (Id, Email, Expires) "
"VALUES (?, LOWER(?), ?)",
(code, email, expiration_date().isoformat()))
util.sendEmail(email, "Your {0} Account".format(settings.CLUBNAME),
format_invite(settings.CLUBNAME, self.request.host,
code))
self.render("message.html",
message = "Invite sent. It will expire in {0} days."
.format(settings.LINKVALIDDAYS),
title = "Invite")
def getTournaments(tournamentID=None):
"""Get list of tournament dictionaries. If a tournament ID is provided
the list will either have 1 dictionary, or be empty if the ID is bad.
The fields in the dictionary are all the tournament fields in the
Tournaments table + Code and Flag_Image for the country, a field
for players that has a dictionary that maps keys of each type of player
to lists of player dictionaries with Id, Name, Association, and
NumberScores fields
"""
tmt_fields = db.table_field_names('Tournaments')
country_fields = ['Code', 'Flag_Image']
with db.getCur() as cur:
sql = """
SELECT {} FROM Tournaments JOIN Countries ON Country = Countries.ID
""".format(','.join(['Tournaments.{}'.format(f)
for f in tmt_fields] + country_fields))
if isinstance(tournamentID, int):
sql += "WHERE Tournaments.ID = {}".format(tournamentID)
sql += " ORDER BY End DESC"
cur.execute(sql)
tournaments = [
dict(zip(tmt_fields + country_fields, row))
for row in cur.fetchall()
]
for tmt in tournaments:
tmt['Dates'] = '{} - {}'.format(tmt['Start'], tmt['End'])
tmt['players'] = dict((t, list()) for t in db.playertypes)
for compete in getCompetitors(tmt['Id'], tmt['Owner']):
tmt['players'][db.playertypes[compete['Type']]].append(compete)
return tournaments
def post(self, q):
with db.getCur() as cur:
cur.execute("SELECT EXISTS(SELECT * FROM Users WHERE Id = ?)",
(q, ))
if cur.fetchone()[0] == 1:
cur.execute("INSERT INTO Admins(Id) VALUES(?)", (q, ))
self.redirect("/admin/users")
def get(self, page):
if page is None:
page = 0
else:
page = int(page[1:]) - 1
PERPAGE = 5
with db.getCur() as cur:
cur.execute("SELECT DISTINCT Date FROM Scores ORDER BY Date DESC")
dates = cur.fetchall()
gamecount = len(dates)
if gamecount > 0:
cur.execute("SELECT Scores.GameId, strftime('%Y-%m-%d', Scores.Date), Rank, Players.Name, Scores.RawScore / 1000.0, Scores.Score, Scores.Chombos FROM Scores INNER JOIN Players ON Players.Id = Scores.PlayerId WHERE Scores.Date BETWEEN ? AND ? GROUP BY Scores.Id ORDER BY Scores.Date ASC;", (dates[min(page * PERPAGE + PERPAGE - 1, gamecount - 1)][0], dates[min(page * PERPAGE, gamecount - 1)][0]))
rows = cur.fetchall()
games = {}
for row in rows:
if row[0] not in games:
games[row[0]] = {'date':row[1], 'scores':{}, 'id':row[0]}
games[row[0]]['scores'][row[2]] = (row[3], row[4], round(row[5], 2), row[6])
maxpage = math.ceil(gamecount * 1.0 / PERPAGE)
pages = range(max(1, page + 1 - 10), int(min(maxpage, page + 1 + 10) + 1))
games = sorted(games.values(), key=lambda x: x["date"], reverse=True)
if page != 0:
prev = page
else:
prev = None
if page + 1 < maxpage:
nex = page + 2
else:
nex = None
self.render("history.html", error=None, games=games, curpage=page + 1, pages=pages, gamecount=gamecount, nex = nex, prev = prev)
else:
self.render("message.html", message="No games entered thusfar", title="Game History")
def post(self):
global email_pattern
stylesheet = self.get_argument('stylesheet', None)
email = self.get_argument('email', None)
if (stylesheet is None or email is None or
email_pattern.match(email) is None):
self.render("message.html",
message="Please pick a stylesheet and enter a valid email",
title="Preference Errors",
next="Preferences", next_url="preferences")
else:
with db.getCur() as cur:
cur.execute(
"DELETE FROM Preferences"
" WHERE UserId = ? AND Preference = 'stylesheet';",
(self.current_user,))
cur.execute(
"INSERT INTO Preferences(UserId, Preference, Value)"
" VALUES(?, 'stylesheet', ?);",
(self.current_user, stylesheet))
cur.execute(
"UPDATE Users SET Email = LOWER(?)"
" WHERE Id = ? AND Email != LOWER(?)",
(email, self.current_user, email))
self.set_secure_cookie("stylesheet", stylesheet)
self.render("message.html",
message="", stylesheet=stylesheet,
title="Preferences Updated",
next="Preferences", next_url="preferences")
def post(self):
result = { 'status': "error",
'message': "Unknown error occurred"}
player = self.get_argument("player", None)
if player is not None:
with db.getCur() as cur:
cur.execute("SELECT Phone FROM People WHERE Id = ?", (player,))
row = cur.fetchone()
phone = row[0]
if phone is not None:
client = Client(settings.TWILIO_SID, settings.TWILIO_AUTH)
try:
client.messages.create(
to=phone,
from_="+14252767908",
body="Your mahjong table is opening up soon!",
)
result["status"] = "success"
result["message"] = "Notified player"
events.logEvent("textsent", player)
except:
events.logEvent("textfailed", player)
result["message"] = "Failed to notify player"
self.write(json.dumps(result))
def get(self, q):
with db.getCur() as cur:
cur.execute("SELECT Quarter FROM Quarters WHERE Quarter = ?",
(q, ))
rows = cur.fetchall()
if len(rows) == 0:
self.render("message.html",
message="Quarter {0} not found".format(q),
title="Quarter Not Found",
next="Manage quarters",
next_url="/admin/quarters")
elif len(rows) == 1:
self.render(
"confirm.html",
question=("Are you sure you want to delete the {} Quarter? "
"This can impact other parts of the system "
"such as membership records, unused points "
"settings, qualification criteria, etc.").format(q),
yesURL=self.request.path,
yesMethod="post",
yesLabel="Yes",
noURL="/admin/quarters",
noMethod="get",
noLabel="No")
else:
self.render("quarters.html",
message=("Error: Multiple quarters named {0} "
"found. See Adminstrator.").format(q),
quarters=rows)
def post(self, q):
with db.getCur() as cur:
cur.execute("SELECT EXISTS(SELECT * FROM Users WHERE Id = ?)",
(q, ))
if cur.fetchone()[0] == 1:
cur.execute("DELETE FROM Admins WHERE Id = ?", (q, ))
self.redirect("/admin/users")
def post(self, q):
quarter = q
values = {'quarter': q}
formfields = [name[0].lower() + name[1:] for name in quarterFields]
for field in formfields[1:]:
values[field] = self.get_argument(field, None)
try:
with db.getCur() as cur:
cur.execute(
"REPLACE INTO Quarters ({}) VALUES ({})".format(
', '.join(quarterFields),
', '.join(['?'] * len(formfields))),
[values[f] for f in formfields])
except Exception as e:
self.render("quarters.html",
message="Unable to update {} {} ({})".format(
q, e, values),
quarters=[])
finally:
leaderboard.genLeaderboard(scores.quarterDate(quarter))
self.render("message.html",
message="Quarter {0} updated".format(quarter),
title="Quarter Updated",
next="Update more quarters",
next_url="/admin/quarters")
def post(self, inviteID):
invitedata = json.loads(self.get_argument('invitedata', None))
if invitedata is None or not (
isinstance(inviteID, str) and
len(inviteID) == login.VerifyLinkIDLength):
return self.write(json.dumps(
{'status':"error", 'message':"Invalid invite ID provided"}))
try:
action = invitedata['action']
with db.getCur() as cur:
if action == 'drop':
cur.execute("DELETE from VerifyLinks WHERE Id = ?",
(inviteID,))
log.info('Deleted invite {}'.format(inviteID))
else:
return self.write(json.dumps(
{'status':"error",
'message':"Invalid action requested: {}".format(
invitedata['action'])}))
return self.write(json.dumps({'status':"success"}))
except Exception as e:
log.error('Error in ManageInvitesHandler.post: {}'.format(e))
return self.write(json.dumps(
{'status':"error",
'message':"Invalid info provided"}))
def storeGamemode():
gamemode = request.form['gamemode']
if gamemode is not "":
with db.getCur() as cur:
cur.execute("DELETE FROM GameModes;")
cur.execute("INSERT INTO GameModes VALUES(?);", (gamemode, ))
return redirect('/')
def post(self):
global VerifyLinkIDLength
with db.getCur() as cur:
email = self.get_argument("email", None)
cur.execute("SELECT Id FROM Users WHERE Email = ?", (email, ))
row = cur.fetchone()
if row is not None:
code = util.randString(VerifyLinkIDLength)
cur.execute(
"INSERT INTO ResetLinks(Id, User, Expires) "
"VALUES (?, ?, ?)",
(code, row[0], expiration_date().isoformat()))
util.sendEmail(
email, "Your {0} Account".format(settings.WEBSITENAME), """
<p>Here's the link to reset your {websitename} account password.<br />
Click <a href="{hostprefix}/reset/{code}">this link</a> to reset your password,
or copy and paste the following into your URL bar:<br />
{hostprefix}/reset/{code} </p>
""".format(websitename=settings.WEBSITENAME,
hostprefix=this_server(self.request),
code=code))
self.render("message.html",
message="Your password reset link has been sent")
else:
self.render(
"message.html",
message="No account found associated with this email",
email=email)
def get(self, q):
with db.getCur() as cur:
cur.execute("SELECT Email, Expires FROM VerifyLinks WHERE Id = ?",
(q, ))
try:
email, expires = cur.fetchone()
except:
self.redirect(settings.PROXYPREFIX)
return
if expires < datetime.date.today().isoformat():
cur.execute("DELETE FROM VerifyLinks WHERE Id = ?", (q, ))
self.render(
"message.html",
message="The invite expired {0}. Please request another.".
format(expires),
title="Expired Invite.")
return
cur.execute("SELECT Email FROM Users WHERE Email = ?", (email, ))
try:
existing = cur.fetchone()[0]
cur.execute("DELETE FROM VerifyLinks WHERE Id = ?", (q, ))
self.render(
"message.html",
message="Account for {0} already exists.".format(email),
title="Duplicate Account")
return
except:
pass
self.render("verify.html", email=email, id=q)
def post(self):
global VerifyLinkIDLength
email = self.get_argument('email', None)
if not db.valid['email'].match(email):
self.render("setup.html",
message="Please enter a valid email address.")
else:
with db.getCur() as cur:
code = util.randString(VerifyLinkIDLength)
cur.execute(
"INSERT INTO VerifyLinks (Id, Email, Expires) "
"VALUES (?, LOWER(?), ?)",
(code, email, expiration_date().isoformat()))
if len(settings.EMAILPASSWORD) > 0:
util.sendEmail(
email, "Your {0} Account".format(settings.WEBSITENAME),
format_invite(settings.WEBSITENAME,
this_server(self.request), code))
self.render(
"message.html",
message="Invite sent. It will expire in {0} days.".
format(settings.LINKVALIDDAYS),
title="Invite")
else:
self.redirect("{}/verify/{}".format(
settings.PROXYPREFIX.rstrip('/'), code))
def post(self):
result = { 'status': "error",
'message': "Unknown error occurred"}
preferences = self.get_argument("preferences", None)
if preferences is not None:
preferences = json.loads(preferences)
print(preferences)
if len(preferences.keys()) > 0:
with db.getCur() as cur:
query = "DELETE FROM Preferences WHERE Key IN ("
query += "?"
query += ", ?" * (len(preferences.keys()) - 1)
query += ")"
args = preferences.keys()
cur.execute(query, args)
args = []
query = "INSERT INTO Preferences(Key, Val) VALUES"
query += "(?, ?)"
query += ", (?, ?)" * (len(preferences.keys()) - 1)
for key,val in preferences.iteritems():
args += [key]
args += [val]
print(query)
print(args)
cur.execute(query, args)
result = { 'status': "success",
'message': "Preferences updated"}
self.write(json.dumps(result))
def post(self):
user = self.get_argument('user', None)
newPlayer = self.get_argument('newPlayer', None)
ret = {
"status": "error",
"message": "Unknown error occurred"
}
with db.getCur() as cur:
if user is None:
ret["message"] = "Please provide a user"
return self.write(json.dumps(ret))
cur.execute("SELECT Id FROM Users WHERE Id = ? or Email = ?",
(user, user))
row = cur.fetchone()
if row is None or len(row) == 0:
ret["message"] = "Unable to find user"
return self.write(json.dumps(ret))
userId = row[0]
if userId is None:
ret["message"] = "Unable to find user Id"
newPlayerId = None
if newPlayer is not None and len(newPlayer) > 0:
cur.execute("SELECT Id FROM Players WHERE Id = ? OR Name = ?",
(newPlayer, newPlayer))
row = cur.fetchone()
if row is not None and len(row) > 0:
newPlayerId = row[0]
cur.execute("UPDATE Users SET PlayerId = ? WHERE Id = ?",
(newPlayerId, userId))
ret["status"] = "success"
ret["message"] = "Updated player"
return self.write(json.dumps(ret))
def post(self):
result = { 'status': "error",
'message': "Unknown error occurred"}
name = self.get_argument("name", None)
phone = self.get_argument("phone", None)
tableType = self.get_argument("type", False)
numplayers = int(self.get_argument("numplayers", "1"))
if name is None or name == "":
result["message"] = "Please enter a name"
else:
if phone == "":
phone = None
players = []
with db.getCur() as cur:
for i in range(numplayers):
n = name
if i > 0:
n += " (" + str(i) + ")"
phone = None
cur.execute("INSERT INTO People(Name, Phone, Added) VALUES(?, ?, datetime('now', 'localtime'))", (n, phone))
cur.execute("INSERT INTO Queue(Person, Type) VALUES(?, ?)", (cur.lastrowid, tableType))
players += [(cur.lastrowid, n)]
for player in players:
events.logEvent('playerqueueadd', (player[0], player[1], tableType, numplayers))
result["status"] = "success"
result["message"] = "Added " + str(numplayers) + "players"
self.write(json.dumps(result))
def get(self, period):
while period.startswith('/'):
period = period[1:]
if '/' in period:
period, rest = period.split('/', 1)
if period not in periods:
period = "quarter"
rows = []
with db.getCur() as cur:
displaycols = ['Name', 'Place', 'Symbol'] + LBDcolumns
cur.execute(
("SELECT {columns} FROM Leaderboards"
" JOIN Players ON PlayerId = Players.Id"
" WHERE Period = ? ORDER BY Date DESC, Place ASC").format(
columns=",".join(displaycols)), (period, ))
rows = [dict(zip(displaycols, row)) for row in cur.fetchall()]
eligible = get_eligible()
leaderboards = collections.defaultdict(lambda: [])
for row in rows:
date = row['Date']
for flag in ['Member', 'Eligible']:
row[flag] = (row['Period'] == 'quarter'
and eligible[date][row['PlayerId']][flag])
leaderboards[date].append(row)
leaderboards = [{
'Date': date,
'Board': board
} for date, board in sorted(leaderboards.items(), reverse=True)]
self.write(json.dumps({'leaderboards': list(leaderboards)}))
def get(self, player, quarter=None):
with db.getCur() as cur:
name = player
cur.execute(
"SELECT Id, Name, MeetupName, Symbol FROM Players"
" WHERE Id = ? OR Name = ?", (player, player))
player = cur.fetchone()
if player is None or len(player) == 0:
return self.render("playerstats.html",
name=name,
error="Couldn't find player")
playerID, name, meetupname, symbol = player
isSelf = self.get_current_player() == stringify(playerID)
eligible = leaderboard.get_eligible()
everplayed = any(eligible[qtr][playerID]['Played']
for qtr in eligible)
quarterHistory = [{
'Name': qtr,
'Played': eligible[qtr][playerID]['Played'],
'Member': eligible[qtr][playerID]['Member'],
'Eligible': eligible[qtr][playerID]['Eligible']
} for qtr in sorted(eligible.keys())[-settings.TIMELINEQUARTERS:]]
self.render("playerstats.html",
error=None,
name=name,
meetupname=meetupname,
symbol=symbol,
quarter=quarter,
quarterHistory=quarterHistory,
everplayed=everplayed,
is_self=isSelf)
def valid_ID(ID, tablename, response=None, msg=None, IDfield='Id'):
"""Check if the ID is a valid Id field in the table.
If a response dictionary is provided, set its status field to -1
and its message field to an appropriate message to pass back
to the browser in a JSON response. The msg will be added on
to any error message.
If a single matching ID is found and the response is a dictionary,
the status field of the response is set to 0.
Return True if ID is valid, False otherwise."""
if ID or isinstance(ID, int):
with db.getCur() as cur:
sql = "SELECT {} FROM {} WHERE {} = ?".format(
IDfield, tablename, IDfield)
args = (ID, )
cur.execute(sql, args)
rows = cur.fetchall()
else:
rows = []
if len(rows) == 0:
if response:
response['status'] = -1
response['message'] = 'Invalid {} field for {}. {}'.format(
IDfield, tablename, msg or '')
return False
if len(rows) > 1:
if response:
response['status'] = -1
response['message'] = (
'Internal error. {} = {} matches multiple rows in {}. {}'.
format(IDfield, ID, tablename, msg or ''))
return False
if isinstance(response, dict):
response['status'] = 0
return True
def get(self):
with db.getCur() as cur:
cur.execute("SELECT COUNT(*) FROM Users")
if cur.fetchone()[0] != 0:
self.redirect(settings.PROXYPREFIX)
else:
self.render("setup.html")
def get(self):
countriesDB = dict(
(country['Code'], country) for country in getCountries())
countriesFile = dict((country['Code'], country)
for country in db.getCountriesFromFile())
adds, mods = [], []
fields = [f for f in countryColumns if f not in ('Id', 'Code')]
updateSQL = "UPDATE Countries SET {} WHERE Id = ?".format(', '.join(
'{} = ?'.format(f) for f in fields))
insertSQL = "INSERT INTO Countries ({}) VALUES ({})".format(
', '.join(fields + ['Code']),
', '.join('?' for f in fields + ['Code']))
with db.getCur() as cur:
for code in countriesFile:
fileCountry = countriesFile[code]
dbCountry = countriesDB.get(code, None)
if dbCountry and util.dict_differences(fileCountry, dbCountry,
fields):
args = [fileCountry[f] for f in fields] + [dbCountry['Id']]
cur.execute(updateSQL, args)
mods.append(code)
elif dbCountry is None:
args = [fileCountry[f] for f in fields + ['Code']]
cur.execute(insertSQL, args)
adds.append(code)
return self.render(
'message.html',
message=('<p>Among {} countries in the database:</p>'
'<p>Countries updated ({}):</p> <p>{}</p>'
'<p>Countries inserted ({}):</p> <p>{}</p>'.format(
len(countriesDB), len(mods), mods, len(adds), adds)),
next='Return to Tournaments Home',
next_url=settings.PROXYPREFIX)
def get(self, player):
with db.getCur() as cur:
cur.execute("SELECT Id,Name FROM Players WHERE Id = ? OR Name = ?", (player, player))
player = cur.fetchone()
if len(player) == 0:
self.render("stats.html", error="Couldn't find that player")
return
name = player[1]
player = player[0]
cur.execute("SELECT Max(Score),MIN(Score),COUNT(*),ROUND(SUM(Score) * 1.0/COUNT(*) * 100) / 100,ROUND(SUM(Rank) * 1.0/COUNT(*) * 100) / 100 FROM Scores WHERE PlayerId = ?", (player,))
row = cur.fetchone()
maxscore = row[0]
minscore = row[1]
numgames = row[2]
avgscore = row[3]
avgrank = row[4]
cur.execute("SELECT ROUND(Sum(Score) * 1.0 / COUNT(*) * 100) / 100, ROUND(Sum(Rank) * 1.0 / COUNT(*) * 100) / 100 FROM (SELECT * FROM Scores WHERE PlayerId = ? ORDER BY Date DESC LIMIT 5)", (player,))
row = cur.fetchone()
avgscore5 = row[0]
avgrank5 = row[1]
self.render("stats.html",
error = None,
name = name,
maxscore = maxscore,
minscore = minscore,
numgames = numgames,
avgscore = avgscore,
avgrank = avgrank,
avgscore5 = avgscore5,
avgrank5 = avgrank5
)
def get(self, q):
with db.getCur() as cur:
cur.execute("SELECT Rank, Players.Name, Scores.RawScore, Scores.Chombos, Scores.Date FROM Scores INNER JOIN Players ON Players.Id = Scores.PlayerId WHERE GameId = ? ORDER BY Rank", (q,))
rows = cur.fetchall()
if len(rows) == 0:
self.render("message.html", message = "Game not found", title = "Edit Game")
else:
self.render("editgame.html", id=q, scores=json.dumps(rows).replace("'", "\\'").replace("\\\"", "\\\\\""))
def getPreference(key):
with db.getCur() as cur:
cur.execute("SELECT Val FROM Preferences WHERE Key = ?", (key,))
row = cur.fetchone()
if row is not None:
return row[0]
else:
return None
def get(self, q):
with db.getCur() as cur:
cur.execute("SELECT Email FROM Users JOIN ResetLinks ON ResetLinks.User = Users.Id WHERE ResetLinks.Id = ?", (q,))
row = cur.fetchone()
if row is None:
self.render("message.html", message = "Link is either invalid or has expired. Please request a new one")
else:
self.render("resetpassword.html", email = row[0], id = q)
def post(self, q):
with db.getCur() as cur:
cur.execute("SELECT EXISTS(SELECT * FROM Scores WHERE GameId = ?)", (q,))
if cur.fetchone()[0] == 0:
self.render("message.html", message = "Game not found", title = "Delete Game")
else:
cur.execute("DELETE FROM Scores WHERE GameId = ?", (q,))
self.redirect("/history")
def get(self):
result = { 'status': "error",
'message': "Unknown error occurred"}
with db.getCur() as cur:
cur.execute("SELECT Time FROM TeachingSessions ORDER BY Time ASC")
result["status"] = "success"
result["times"] = [{'Time':row[0]} for row in cur.fetchall()]
self.write(json.dumps(result))
def get(self, q):
with db.getCur() as cur:
cur.execute("SELECT Email FROM Users WHERE Id = ?", (q,))
row = cur.fetchone()
if len(row) == 0:
self.render("message.html", message = "User not found", title = "Demote User")
else:
self.render("demoteuser.html", email = row[0], q = q)
def get(self):
with db.getCur() as cur:
cur.execute("SELECT Type FROM TableTypes")
rows = cur.fetchall()
queues = []
for row in rows:
queues += [getTypeQueue(row[0])]
self.write(json.dumps({'Queues':queues}))
def get(self, period):
if len(period) > 0:
period = period[1:]
with db.getCur() as cur:
leaderboards = {}
queries = {
"annual":[
"""SELECT strftime('%Y', Scores.Date), Players.Name, ROUND(SUM(Scores.Score) * 1.0 / COUNT(Scores.Score) * 100) / 100 AS AvgScore, COUNT(Scores.Score) AS GameCount, 0
FROM Players LEFT JOIN Scores ON Players.Id = Scores.PlayerId
GROUP BY strftime('%Y', Date),Players.Id
HAVING GameCount >= 4 ORDER BY AvgScore DESC;"""
],
"biannual":[
"""SELECT strftime('%Y', Scores.Date) || ' ' || case ((strftime('%m', Date) - 1) / 6) when 0 then '1st' when 1 then '2nd' end,
Players.Name, ROUND(SUM(Scores.Score) * 1.0 / COUNT(Scores.Score) * 100) / 100 AS AvgScore, COUNT(Scores.Score) AS GameCount, 0
FROM Players LEFT JOIN Scores ON Players.Id = Scores.PlayerId
GROUP BY strftime('%Y', Date) || ' ' || ((strftime('%m', Date) - 1) / 6),Players.Id
HAVING GameCount >= 4 ORDER BY AvgScore DESC;"""
],
"quarter":[
"""SELECT strftime('%Y', Scores.Date) || ' ' || case ((strftime('%m', Date) - 1) / 3) when 0 then '1st' when 1 then '2nd' when 2 then '3rd' when 3 then '4th' end,
Players.Name, ROUND(SUM(Scores.Score) * 1.0 / COUNT(Scores.Score) * 100) / 100 AS AvgScore, COUNT(Scores.Score) AS GameCount, 0
FROM Players LEFT JOIN Scores ON Players.Id = Scores.PlayerId
GROUP BY strftime('%Y', Date) || ' ' || ((strftime('%m', Date) - 1) / 3),Players.Id
HAVING GameCount <= 7 ORDER BY AvgScore DESC;""",
"""SELECT strftime('%Y', Scores.Date) || ' ' || case ((strftime('%m', Date) - 1) / 3) when 0 then '1st' when 1 then '2nd' when 2 then '3rd' when 3 then '4th' end,
Players.Name, ROUND(SUM(Scores.Score) * 1.0 / COUNT(Scores.Score) * 100) / 100 AS AvgScore, COUNT(Scores.Score) + 1 AS GameCount, 1
FROM Players LEFT JOIN Scores ON Players.Id = Scores.PlayerId
WHERE Scores.Id NOT IN (SELECT Id FROM Scores GROUP BY PlayerId,strftime('%Y', Date) || ' ' || ((strftime('%m', Date) - 1) / 3) HAVING Score = MIN(Score))
GROUP BY strftime('%Y', Date) || ' ' || ((strftime('%m', Date) - 1) / 3),Players.Id
HAVING GameCount > 7 ORDER BY AvgScore DESC;"""
]
}
if period not in queries:
period = "quarter"
rows = []
for query in queries[period]:
cur.execute(query)
rows += cur.fetchall()
places={}
rows.sort(key=lambda row: row[2], reverse=True) # sort by score
for row in rows:
if row[0] not in leaderboards:
leaderboards[row[0]] = []
places[row[0]] = 1
leaderboard = leaderboards[row[0]]
leaderboard += [{'place': places[row[0]],
'name':row[1],
'score':row[2],
'count':row[3] if row[4] == 0 else str(row[3] - 1) + " (+1)",
'dropped':row[4]}]
places[row[0]] += 1
leaders = sorted(list(leaderboards.items()), reverse=True)
leaderboards = []
for name, scores in leaders:
leaderboards += [{'name':name, 'scores':scores}]
leaderboards={'leaderboards':leaderboards}
self.write(json.dumps(leaderboards))
def post(self):
result = { 'status': "error",
'message': "Unknown error occurred"}
announcement = self.get_argument("announcement", None)
with db.getCur() as cur:
cur.execute("INSERT INTO Messages VALUES(?, datetime('now', 'localtime'))", (announcement,))
result = { 'status': "success",
'message': "Announcement updated"}
self.write(json.dumps(result))
def post(self):
result = { 'status': "error",
'message': "Unknown error occurred"}
time = self.get_argument("time", None)
with db.getCur() as cur:
cur.execute("DELETE FROM TeachingSessions WHERE Time = ?", (time,))
result = { 'status': "success",
'message': "Teaching sessions updated"}
self.write(json.dumps(result))
def post(self):
result = { 'status': "error",
'message': "Unknown error occurred"}
time = self.get_argument("time", None)
with db.getCur() as cur:
cur.execute("INSERT INTO TeachingSessions(Time) VALUES(?)", (time,))
result = { 'status': "success",
'message': "Teaching sessions updated"}
self.write(json.dumps(result))
def logEvent(eventType, data = None):
if data is not None:
data = json.dumps(data)
else:
data = ""
with db.getCur() as cur:
cur.execute(
"INSERT INTO Events(Type, Time, Data) VALUES (?, datetime('now', 'localtime'), ?)",
(eventType, data))
log.info(str(datetime.datetime.now()) + "|" + eventType + "|" + data)
def get(self):
result = { 'status': "error",
'data': {}}
with db.getCur() as cur:
cur.execute("SELECT Key, Val FROM Preferences")
rows = cur.fetchall()
for row in rows:
result["data"][row[0]] = row[1];
result["status"] = "success"
self.write(json.dumps(result))
def get(self, q):
with db.getCur() as cur:
cur.execute("SELECT Email FROM VerifyLinks WHERE Id = ? AND Expires > datetime('now')", (q,))
if cur.rowcount == 0:
self.redirect("/")
return
email = cur.fetchone()[0]
self.render("verify.html", email = email, id = q)
def get(self, q):
with db.getCur() as cur:
cur.execute("SELECT Rank, Players.Name, Scores.RawScore / 1000.0, Scores.Score, Scores.Chombos FROM Scores INNER JOIN Players ON Players.Id = Scores.PlayerId WHERE GameId = ?", (q,))
rows = cur.fetchall()
if len(rows) == 0:
self.render("message.html", message = "Game not found", title = "Delete Game")
else:
scores = {}
for row in rows:
scores[row[0]] = (row[1], row[2], round(row[3], 2), row[4])
self.render("deletegame.html", id=q, scores=scores)
def get(self):
with db.getCur() as cur:
cur.execute("SELECT Users.Id, Email, Password, Admins.Id FROM Users LEFT JOIN Admins ON Admins.Id = Users.Id")
users = []
for row in cur.fetchall():
users += [{
"Id":row[0],
"Email":row[1],
"Admin":row[3] is not None,
}]
self.render("users.html", users = users)
def get(self):
result = { 'status': "error",
'message': "Unknown error occurred"}
with db.getCur() as cur:
cur.execute("SELECT Message FROM Messages ORDER BY Date DESC LIMIT 1")
result["status"] = "success"
row = cur.fetchone()
if row is not None:
result["message"] = row[0]
else:
result["message"] = "No message at this time"
self.write(json.dumps(result))
def get(self, player, page):
if page is None:
page = 0
else:
page = int(page[1:]) - 1
PERPAGE = 10
with db.getCur() as cur:
cur.execute("SELECT Id,Name FROM Players WHERE Id = ? OR Name = ?", (player, player))
player = cur.fetchone()
if len(player) == 0:
self.render("message.html", message="Couldn't find that player", title="User Game History")
return
name = player[1]
player = player[0]
cur.execute("SELECT DISTINCT GameId FROM Scores WHERE PlayerId = ? ORDER BY Date DESC", (player,))
games = [i[0] for i in cur.fetchall()]
gamecount = len(games)
print min(page * PERPAGE + PERPAGE - 1, gamecount - 1)
if gamecount > 0:
thesegames = games[min(page * PERPAGE, gamecount - 1):min(page * PERPAGE + PERPAGE - 1, gamecount - 1)]
placeholder= '?' # For SQLite. See DBAPI paramstyle
placeholders= ', '.join(placeholder for i in range(len(thesegames)))
print(placeholders)
cur.execute("SELECT Scores.GameId, strftime('%Y-%m-%d', Scores.Date), Rank, Players.Name, Scores.RawScore / 1000.0, Scores.Score, Scores.Chombos FROM Scores INNER JOIN Players ON Players.Id = Scores.PlayerId WHERE Scores.GameId IN (" + placeholders + ") GROUP BY Scores.Id ORDER BY Scores.Date ASC;", thesegames)
rows = cur.fetchall()
games = {}
for row in rows:
if row[0] not in games:
games[row[0]] = {'date':row[1], 'scores':{}, 'id':row[0]}
games[row[0]]['scores'][row[2]] = (row[3], row[4], round(row[5], 2), row[6])
maxpage = math.ceil(gamecount * 1.0 / PERPAGE)
pages = range(max(1, page + 1 - 10), int(min(maxpage, page + 1 + 10) + 1))
games = sorted(games.values(), key=lambda x: x["date"], reverse=True)
if page != 0:
prev = page
else:
prev = None
if page + 1 < maxpage:
nex = page + 2
else:
nex = None
self.render("userhistory.html",
error=None,
games=games,
curpage=page + 1,
pages=pages,
gamecount=gamecount,
nex = nex,
prev = prev,
user = name,
player = player)
else:
self.render("message.html", message="No games entered thusfar", title="Game History", user = name)
def main():
with db.getCur() as cur:
cur.execute("SELECT RawScore,PlayerCount,Rank,GameId,Id FROM Scores WHERE RawScore != 0 AND Date BETWEEN '2015-01-01' AND '2015-12-31' ORDER BY GameId ASC, Rank DESC")
rows = cur.fetchall()
currentscore = 0
for row in rows:
score = getScore(row[0], row[1], row[2])
currentscore += score
if row[2] == 1:
if currentscore != 0:
score -= currentscore
currentscore = 0
cur.execute("UPDATE Scores SET Score = ? WHERE Id = ?", (score, row[4]))
def post(self):
result = { 'status': "error",
'message': "Unknown error occurred"}
player = self.get_argument("player", None)
tableType = self.get_argument("type", None)
if player is not None:
with db.getCur() as cur:
cur.execute("DELETE FROM Queue WHERE Person = ?", (player,))
cur.execute("DELETE FROM Players WHERE PersonId = ?", (player,))
cur.execute("INSERT INTO Queue(Person, Type) VALUES(?, ?)", (player, tableType))
events.logEvent('playerqueuemove', (player, tableType))
result["status"] = "success"
result["message"] = "Moved player"
self.write(json.dumps(result))
def post(self, q):
scores = self.get_argument('scores', None)
gamedate = self.get_argument('gamedate', None)
if scores is None:
self.write('{"status":1, "error":"Please enter some scores"}')
return
scores = json.loads(scores)
if len(scores) != 4 and len(scores) != 5:
self.write('{"status":1, "error":"Please enter 4 or 5 scores"}')
return
total = 0
for score in scores:
total += score['score']
if total != len(scores) * 25000:
self.write('{"status":1, "error":' + "Scores do not add up to " + len(scores) * 25000 + '}')
return
with db.getCur() as cur:
cur.execute("SELECT GameId FROM Scores WHERE GameId = ?", (q,))
row = cur.fetchone()
if len(row) == 0:
self.write('{"status":1, "error":"Game not found"}')
return
gameid = row[0]
print(gameid)
print(gamedate)
for i in range(0, len(scores)):
score = scores[i]
if score['player'] == "":
self.write('{"status":1, "error":"Please enter all player names"}')
return
cur.execute("DELETE FROM Scores WHERE GameId = ?", (gameid,))
for i in range(0, len(scores)):
score = scores[i]
cur.execute("SELECT Id FROM Players WHERE Id = ? OR Name = ?", (score['player'], score['player']))
player = cur.fetchone()
if player is None or len(player) == 0:
cur.execute("INSERT INTO Players(Name) VALUES(?)", (score['player'],))
cur.execute("SELECT Id FROM Players WHERE Name = ?", (score['player'],))
player = cur.fetchone()
player = player[0]
adjscore = util.getScore(score['newscore'], len(scores), i + 1)
cur.execute("INSERT INTO Scores(GameId, PlayerId, Rank, PlayerCount, RawScore, Chombos, Score, Date) VALUES(?, ?, ?, ?, ?, ?, ?, ?)", (gameid, player, i + 1, len(scores), score['score'], score['chombos'], adjscore, gamedate))
self.write('{"status":0}')
def post(self):
with db.getCur() as cur:
email = self.get_argument("email", None)
cur.execute("SELECT Id FROM Users WHERE Email = ?", (email,))
row = cur.fetchone()
if row is not None:
code = util.randString(32)
cur.execute("INSERT INTO ResetLinks(Id, User, Expires) VALUES (?, ?, ?)", (code, row[0], (datetime.date.today() + datetime.timedelta(days=7)).isoformat()))
util.sendEmail(email, "Your SeattleMahjong Account",
"<p>Here's the link to reset your SeattleMahjong account password\n<br />\
Click <a href=\"http://" + self.request.host + "/reset/" + code + "\">this</a> link to reset your password or copy and paste the following into your URL bar:<br />http://" + self.request.host + "/reset/" + code + "</p>\n")
self.render("message.html", message = "Your password reset link has been sent")
else:
self.render("message.html", message = "No accounts found associated with this email", email = email)
def post(self):
email = self.get_argument('email', None)
if not re.match("^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]+$", email, flags = re.IGNORECASE):
self.render("invite.html", message = "Please enter a valid email address.")
else:
with db.getCur() as cur:
code = util.randString(32)
cur.execute("INSERT INTO VerifyLinks (Id, Email, Expires) VALUES (?, LOWER(?), ?)", (code, email, (datetime.date.today() + datetime.timedelta(days=7)).isoformat()))
util.sendEmail(email, "Your SeattleMahjong Account",
"<p>You've been invited to SeattleMahjong\n<br />\
Click <a href=\"http://" + self.request.host + "/verify/" + code + "\">this</a> link to accept the invite and register an account or copy and paste the following into your URL bar:<br />http://" + self.request.host + "/verify/" + code + "</p>\n" +
"<p>If you believe you received this email in error, it can be safely ignored. It is likely a user simply entered your email by mistake.</p>")
self.render("message.html", message = "Invite sent. It will expire in 7 days.", title = "Invite")
