Beispiel #1
0
    def get(self):
        with db.getCur() as cur:
            playerFields = db.table_field_names('Players')
            cur.execute(("SELECT {}, QuarterId FROM Players"
                         " LEFT OUTER JOIN Memberships"
                         " ON Players.Id = Memberships.PlayerId"
                         " WHERE Id != ?"
                         " ORDER BY Name ASC, QuarterId ASC").format(
                             ', '.join(playerFields)),
                        (scores.getUnusedPointsPlayerID(), ))
            rows = cur.fetchall()
            players = collections.OrderedDict({})
            last_player = None
            for row in rows:  # Build dictionary for each player
                if row[0] != last_player:
                    players[row[0]] = collections.defaultdict(
                        lambda: list(), zip(playerFields, row))
                    last_player = row[0]
                    players[row[0]]['GameCounts'] = {}
                memberQtr = row[len(playerFields)]
                if memberQtr is not None:  # Memberships is a list of qtrs
                    players[row[0]]['Memberships'].append(memberQtr)

            cur.execute(("SELECT Players.Id, Quarter, COUNT(Scores.Id)"
                         " FROM Players"
                         " LEFT OUTER JOIN Scores"
                         " ON Players.Id = Scores.PlayerId"
                         " WHERE Players.Id != ?"
                         " GROUP BY Players.Id, Scores.Quarter"
                         " ORDER BY Players.Id ASC, Quarter ASC").format(
                             ', '.join(playerFields)),
                        (scores.getUnusedPointsPlayerID(), ))
            # Update player dictionary with game counts
            for row in cur.fetchall():
                player = players[row[0]]
                player['GameCounts'][row[1]] = int(row[2])

            cur.execute("SELECT DISTINCT Quarter FROM Scores UNION"
                        " SELECT DISTINCT Quarter FROM Quarters"
                        " ORDER BY Quarter ASC")  # Get all known quarters
            quarters = [row[0] for row in cur.fetchall()]
            initialQtrsShown = [scores.quarterString()]  # Current quarter
            if initialQtrsShown[0] in quarters:  # Start at current qtr
                index = quarters.index(initialQtrsShown[0])  # and go forward
                initialQtrsShown = quarters[index:index +
                                            settings.MEMBERSHIPQUARTERS]
            elif len(quarters) > 0:  # Else take most recent qtrs
                initialQtrsShown = quarters[-settings.MEMBERSHIPQUARTERS:]
            else:
                initialQtrsShown = []

        self.render("players.html",
                    message="No players found" if len(rows) == 0 else "",
                    players=players,
                    quarters=quarters,
                    visibleQtrs=initialQtrsShown)
Beispiel #2
0
def get_eligible(quarter=None):
    """Return a nested dictionary structure indexed by quarter and player ID
    that returns a dictionary with the following flags:
    'Member' for whether they were a member that quarter,
    'Played' for whether they played any games that quarter, and
    'Eligible' indicating whether or not the player qualified for the
       end-of-quarter tournament in that quarter.
    """
    eligible = collections.defaultdict(lambda: collections.defaultdict(
        lambda: collections.defaultdict(lambda: False)))

    # Get unused points playerID before opening cursor to avoid db deadlock
    unusedPointsPlayerID = scores.getUnusedPointsPlayerID()
    with db.getCur() as cur:
        cur.execute(
            "SELECT Scores.Quarter AS Qtr, Scores.PlayerId AS Plr,"
            "       QualifyingGames, QualifyingDistinctDates, COUNT(Score), "
            "       COUNT(DISTINCT Date), Memberships.QuarterId IS NOT NULL"
            " FROM Scores LEFT OUTER JOIN Quarters ON"
            "   Scores.Quarter = Quarters.Quarter"
            "   LEFT OUTER JOIN Memberships ON"
            "        Scores.PlayerId = Memberships.PlayerID AND"
            "        Scores.Quarter = Memberships.QuarterId"
            " WHERE Scores.PlayerId != ?"
            " GROUP BY Qtr, Plr "
            "UNION SELECT Quarters.Quarter AS Qtr, Players.Id Plr,"
            "       QualifyingGames, QualifyingDistinctDates, 0, 0,"
            "       Memberships.QuarterId IS NOT NULL"
            " FROM Quarters LEFT OUTER JOIN Players"
            "   LEFT OUTER JOIN Memberships ON"
            "        Players.Id = Memberships.PlayerID AND"
            "        Quarters.Quarter = Memberships.QuarterId"
            " WHERE Players.Id != ?"
            " ORDER BY Qtr, Plr", (unusedPointsPlayerID, unusedPointsPlayerID))
        rows = cur.fetchall()
    previousQtr = None
    for row in rows:
        Quarter, PlayerId, QGames, QDistinctDates, Games, Dates, Memb = row
        if Quarter not in eligible:
            if previousQtr is None:
                eligible[Quarter]['QGames'] = (QGames
                                               or settings.QUALIFYINGGAMES)
                eligible[Quarter]['QDistinctDates'] = (
                    QDistinctDates or settings.QUALIFYINGDISTINCTDATES)
            else:
                eligible[Quarter]['QGames'] = (QGames or
                                               eligible[previousQtr]['QGames'])
                eligible[Quarter]['QDistinctDates'] = (
                    QDistinctDates or eligible[previousQtr]['QDistinctDates'])
        previousQtr = Quarter
        eligible[Quarter][PlayerId]['Member'] = Memb
        eligible[Quarter][PlayerId]['Played'] = Games > 0
        eligible[Quarter][PlayerId]['Eligible'] = Memb and (
            Games >= eligible[Quarter]['QGames']
            or Dates >= eligible[Quarter]['QDistinctDates'])
    return eligible
Beispiel #3
0
 def get(self):
     columns = ["name", "rating", "count"]
     query = """SELECT
         Players.Name,
         ROUND((SUM(Scores.DeltaRating) + {DEFAULT_RATING}) * 100) / 100 AS Rating,
         COUNT(*) AS GameCount
       FROM Scores LEFT JOIN Players ON Players.Id = Scores.PlayerId
       WHERE Players.Id != ?
       GROUP BY Players.Id
       ORDER BY Rating DESC;""".format(DEFAULT_RATING=settings.DEFAULT_RATING)
     with db.getCur() as cur:
         cur.execute(query, (scores.getUnusedPointsPlayerID(),))
         players = [dict(zip(columns, row)) for row in cur.fetchall()]
     self.write(json.dumps({'players':players}))
Beispiel #4
0
 def get(self, q):
     with db.getCur() as cur:
         cur.execute(
             "SELECT Rank, Players.Name, Scores.RawScore, Scores.Chombos, Scores.Date, Players.Id 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:
             unusedPoints = None
             # UnusedPointsPlayer always sorted last in rank
             if rows[-1][5] == scores.getUnusedPointsPlayerID():
                 unusedPoints = rows[-1][2]
                 rows = rows[:-1]
             unusedPointsIncrement, perPlayer = scores.getPointSettings(
                 date=rows[0][4])
             self.render("editgame.html",
                         id=q,
                         scores=json.dumps(rows).replace(
                             "'", "\\'").replace("\\\"", "\\\\\""),
                         unusedPoints=unusedPoints,
                         unusedPointsIncrement=unusedPointsIncrement)
Beispiel #5
0
def genLeaderboard(leaderDate=None):
    """Recalculates the leaderboard for the given datetime object.
    If leaderDate is None, then recalculates all leaderboards."""

    # Get unused points playerID before opening cursor to change leaderboard
    # records to avoid db deadlock if no unused player is yet defined
    unusedPointsPlayerID = scores.getUnusedPointsPlayerID()
    with db.getCur() as cur:
        leadercols = ['Place'] + LBDcolumns
        leaderrows = []

        for periodname, period in periods.items():
            rows = []
            queries = period['queries']
            datefmt = period['datefmt']
            sql = "DELETE FROM Leaderboards WHERE Period = ? "
            if leaderDate is not None:
                datetest = "(" + datefmt + ") = (" + datefmt.format(
                    date="?") + ")"
                bindings = [scores.dateString(leaderDate)
                            ] * datefmt.count("{date}")
                sql += " AND Date = " + datefmt.format(date="?")
            else:
                datetest = "1"
                bindings = []
            cur.execute(sql, [periodname] + bindings)

            for query in queries:
                sql = query.format(datetest=datetest,
                                   datefmt=datefmt,
                                   DEFDROPGAMES=settings.DROPGAMECOUNT).format(
                                       date="Scores.Date")
                cur.execute(sql, [unusedPointsPlayerID] + bindings)
                for row in cur.fetchall():
                    record = dict(zip(LBDcolumns, row))
                    # For Quarterly Leaderboards, compute dropped game average
                    if periodname == 'quarter' and int(
                            record['DropGames']) > 0:
                        record['DropGames'] = min(settings.MAXDROPGAMES,
                                                  record['DropGames'])
                        cur.execute(
                            "SELECT Score FROM Scores"
                            "  WHERE PlayerId = ? AND Quarter = ?"
                            "  ORDER BY Score ASC LIMIT -1 OFFSET ?",
                            (record['PlayerId'], record['Date'],
                             record['DropGames']))
                        total = 0.0
                        count = 0
                        for score in cur.fetchall():
                            total += score[0]
                            count += 1
                        if count > 0:
                            record['AvgScore'] = round(total / count, 2)
                        record['GameCount'] -= record['DropGames']
                    rows += [record]

            rows.sort(key=lambda row: row['AvgScore'],
                      reverse=True)  # sort by score
            places = {}
            for row in rows:
                if row['Date'] not in places:
                    places[row['Date']] = 1
                row['Place'] = places[row['Date']]
                places[row['Date']] += 1

                leaderrow = [row[col] for col in leadercols]
                leaderrows += [leaderrow]

        query = "INSERT INTO Leaderboards({columns}) VALUES({colvals})".format(
            columns=",".join(leadercols),
            colvals=",".join(["?"] * len(leadercols)))
        cur.executemany(query, leaderrows)
Beispiel #6
0
 def get(self):
     with db.getCur() as cur:
         self.set_header('Content-Type', 'application/json')
         cur.execute("SELECT Name FROM Players WHERE Id != ? ORDER BY Name",
                     (scores.getUnusedPointsPlayerID(), ))
         self.write(json.dumps(list(map(lambda x: x[0], cur.fetchall()))))
Beispiel #7
0
 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, Players.Id"
                 " FROM Scores INNER JOIN Players ON"
                 "   Players.Id = Scores.PlayerId"
                 " WHERE Scores.Date BETWEEN ? AND ?;",
                 (dates[min(page * PERPAGE + PERPAGE - 1,
                            gamecount - 1)][0], dates[min(
                                page * PERPAGE, gamecount - 1)][0]))
             rows = cur.fetchall()
             games = {}
             for row in rows:
                 gID, date, rank, name, rawscore, points, chombos, pID = row
                 if gID not in games:
                     games[gID] = {
                         'date': date,
                         'scores': [],
                         'id': gID,
                         'unusedPoints': 0
                     }
                 if pID == scores.getUnusedPointsPlayerID():
                     games[gID]['unusedPoints'] = rawscore
                 else:
                     games[gID]['scores'].append(
                         (rank, name, rawscore, round(points, 2), chombos))
             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'], x['id']),
                            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,
                         ChomboPenalty=settings.CHOMBOPENALTY)
         else:
             self.render("message.html",
                         message="No games entered thusfar",
                         title="Game History")
Beispiel #8
0
 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)
         if gamecount > 0:
             thesegames = games[min(page * PERPAGE, gamecount -
                                    1):min(page * PERPAGE +
                                           PERPAGE, gamecount)]
             placeholder = '?'  # For SQLite. See DBAPI paramstyle
             placeholders = ', '.join(placeholder
                                      for i in range(len(thesegames)))
             cur.execute(
                 "SELECT Scores.GameId,"
                 " strftime('%Y-%m-%d', Scores.Date), Rank, Players.Name,"
                 " Scores.RawScore / 1000.0, Scores.Score, Scores.Chombos,"
                 " Players.Id"
                 " FROM Scores INNER JOIN Players"
                 "  ON Players.Id = Scores.PlayerId"
                 " WHERE Scores.GameId IN (" + placeholders + ")"
                 " ORDER BY Scores.GameId, Rank ASC;", thesegames)
             rows = cur.fetchall()
             games = {}
             for row in rows:
                 gID, date, rank, name, rawScore, score, chombos, pID = row
                 if gID not in games:
                     games[gID] = {
                         'date': date,
                         'scores': [],
                         'id': gID,
                         'unusedPoints': 0
                     }
                 if pID == scores.getUnusedPointsPlayerID():
                     games[gID]['unusedPoints'] = rawScore
                 else:
                     games[gID]['scores'].append(
                         (rank, name, rawScore, round(score, 2), chombos))
             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"], x["id"]),
                            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,
                         ChomboPenalty=settings.CHOMBOPENALTY,
                         nex=nex,
                         prev=prev,
                         user=name,
                         player=player)
         else:
             self.render("message.html",
                         message="No games entered thusfar",
                         title="Game History",
                         user=name)