106 lines
3.3 KiB
Python
106 lines
3.3 KiB
Python
# -*- coding: utf-8 -*-
|
|
# Copyright (c) 2017 Tomek Wójcik <tomek@bthlabs.pl>
|
|
#
|
|
# Permission is hereby granted, free of charge, to any person obtaining a copy
|
|
# of this software and associated documentation files (the "Software"), to deal
|
|
# in the Software without restriction, including without limitation the rights
|
|
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
|
|
# copies of the Software, and to permit persons to whom the Software is
|
|
# furnished to do so, subject to the following conditions:
|
|
#
|
|
# The above copyright notice and this permission notice shall be included in
|
|
# all copies or substantial portions of the Software.
|
|
#
|
|
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
|
|
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
|
|
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
|
|
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
|
|
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
|
|
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
|
|
# THE SOFTWARE.
|
|
#
|
|
|
|
"""
|
|
q3stats.lib.queries
|
|
===================
|
|
|
|
This module contains functions for querying the database.
|
|
"""
|
|
|
|
import datetime
|
|
|
|
import sqlalchemy as sa
|
|
|
|
from q3stats.models import Game, Score
|
|
|
|
SQL_TOP_PLAYERS = """SELECT
|
|
scores.player,
|
|
sum(scores.%s) as sum_frags
|
|
FROM scores
|
|
LEFT JOIN games ON games.id = scores.game_id
|
|
WHERE
|
|
games.date >= :date_start
|
|
AND games.date < :date_end
|
|
GROUP BY scores.player
|
|
ORDER BY sum_frags DESC
|
|
LIMIT :limit"""
|
|
|
|
|
|
def get_game_dates(db_session):
|
|
"""Returns a list of unique game dates."""
|
|
dates_query = sa.sql.expression.select([Game.__table__.c.date]).\
|
|
distinct(Game.__table__.c.date).\
|
|
order_by(Game.__table__.c.date.desc())
|
|
|
|
dates = [x.date for x in db_session.execute(dates_query)]
|
|
return dates
|
|
|
|
|
|
def get_player_sessions(db_session, player, limit=7):
|
|
"""Returns a list of unique sessions for the *player*."""
|
|
player_sessions_query = sa.sql.expression.\
|
|
select([Game.__table__.c.date]).\
|
|
distinct(Game.__table__.c.date).\
|
|
select_from(Game.__table__.join(
|
|
Score.__table__, Score.__table__.c.game_id == Game.__table__.c.id
|
|
)).\
|
|
where(Score.__table__.c.player == player).\
|
|
order_by(Game.__table__.c.date.desc()).\
|
|
limit(limit)
|
|
|
|
player_sessions = [
|
|
x.date for x in db_session.execute(player_sessions_query)
|
|
]
|
|
|
|
return player_sessions
|
|
|
|
|
|
def get_top_players(session, agg_by='kills', limit=3):
|
|
"""Returns a list of ``(player, score)`` tuples identifying top players
|
|
in the current month.
|
|
|
|
Use the *agg_by* kwargs to specify aggregation level. Valid values are
|
|
``kills`` and ``suicides``."""
|
|
assert agg_by in ('kills', 'suicides')
|
|
|
|
date_start = datetime.datetime.now().replace(day=1).date()
|
|
date_end = None
|
|
|
|
if date_start.month == 12:
|
|
date_end = datetime.date(
|
|
date_start.year + 1, 1, 1
|
|
)
|
|
else:
|
|
date_end = datetime.date(
|
|
date_start.year, date_start.month + 1, 1
|
|
)
|
|
|
|
query = SQL_TOP_PLAYERS % agg_by
|
|
params = {
|
|
'date_start': date_start, 'date_end': date_end, 'limit': limit
|
|
}
|
|
|
|
return [
|
|
(x.player, x.sum_frags) for x in session.execute(query, params)
|
|
]
|