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