From 6d4133a04d79b3313c352e536788333af491d4f8 Mon Sep 17 00:00:00 2001 From: Dmitrii Morozov Date: Fri, 21 Jun 2024 20:17:43 +0200 Subject: Reworked statistics --- persistence/__init__.py | 30 ++++++++++++++++-------------- 1 file changed, 16 insertions(+), 14 deletions(-) (limited to 'persistence') diff --git a/persistence/__init__.py b/persistence/__init__.py index 9d9220e..663522e 100644 --- a/persistence/__init__.py +++ b/persistence/__init__.py @@ -1,5 +1,6 @@ import sqlite3, typing from app_types import * +from datetime import date class UserRepository: @@ -17,21 +18,21 @@ class UserRepository: def get_user(self, chat_id): connection = self.__get_connection() cur = connection.cursor() - query = "select * from user where chat_id = {chat_id}".format(chat_id = chat_id) + query = "SELECT * FROM user WHERE chat_id = {chat_id}".format(chat_id = chat_id) cur.execute(query) return cur.fetchone() def get_all_users(self): connection = self.__get_connection() cur = connection.cursor() - query = "select * from user" + query = "SELECT * FROM user" cur.execute(query) return cur.fetchall() def remove_chat(self, chat_id): connection = self.__get_connection() cur = connection.cursor() - query = "DELETE FROM user where chat_id = {chat_id}".format( + query = "DELETE FROM user WHERE chat_id = {chat_id}".format( chat_id = chat_id) cur.execute(query) connection.commit() @@ -57,16 +58,17 @@ class StatsRepository: def __initialize(self): cur = self.__get_connection().cursor() - cur.execute("CREATE TABLE IF NOT EXISTS stats(user_id TEXT, display_name TEXT, level INT, matches_played INT, kills INT, wins INT)") - cur.execute("CREATE UNIQUE INDEX IF NOT EXISTS user_id_idx ON stats(user_id)") + cur.execute("CREATE TABLE IF NOT EXISTS stats(user_id TEXT, stats_date DATE, display_name TEXT, level INT, matches_played INT, kills INT, wins INT)") + cur.execute("CREATE UNIQUE INDEX IF NOT EXISTS user_id_date_idx ON stats(user_id, stats_date)") - async def put_stats(self, user: User): + async def put_stats(self, user: User, stats_date: date = date.today()): stats: UserStats = await user.fetch_stats() connection = self.__get_connection() cur = connection.cursor() - query = "INSERT OR REPLACE INTO stats(user_id, display_name, level, matches_played, kills, wins) VALUES('{user_id}', '{display_name}', {level}, {matches_played}, {kills}, {wins})".format( + query = "INSERT OR REPLACE INTO stats(user_id, stats_date, display_name, level, matches_played, kills, wins) VALUES('{user_id}', '{stats_date}', '{display_name}', {level}, {matches_played}, {kills}, {wins})".format( user_id = user.id, + stats_date = stats_date, display_name = user.display_name, level = stats.level, matches_played = stats.matches_played, @@ -75,10 +77,10 @@ class StatsRepository: cur.execute(query) connection.commit() - def get_stats(self) -> typing.List[UserStats]: + def get_stats(self, stats_date: date = date.today()) -> typing.List[UserStats]: connection = self.__get_connection() cur = connection.cursor() - query = "SELECT * FROM stats" + query = "SELECT * FROM stats WHERE stats_date IN (SELECT MIN(stats_date) FROM stats WHERE stats_date >= '{stats_date}')".format(stats_date = stats_date) cur.execute(query) result = cur.fetchall() return [self.__map_from_db(stats) for stats in result] @@ -86,11 +88,11 @@ class StatsRepository: def __map_from_db(self, record): user_stats = UserStats() user_stats.user_id = str(record[0]) - user_stats.user_display_name = str(record[1]) - user_stats.level = int(record[2]) - user_stats.matches_played = int(record[3]) - user_stats.kills = int(record[4]) - user_stats.wins = int(record[5]) + user_stats.user_display_name = str(record[2]) + user_stats.level = int(record[3]) + user_stats.matches_played = int(record[4]) + user_stats.kills = int(record[5]) + user_stats.wins = int(record[6]) return user_stats class PresenceRepository: -- cgit v1.2.3