def isWordBelongToPlayer(**params):
return True if DB.getOne(
"""
SELECT *
FROM word
WHERE round_id = %(round_id)s AND word = %(word)s AND player_id = %(player_id)s AND series_id = %(series_id)s
""", params) else False
def get(game_id=None):
"""
Returns game summary
:param game_id:
:return: dict with game stats
"""
condition = ("WHERE id=%d" % game_id) if game_id else ""
game = DB.getOne("""
SELECT
*,
(SELECT count(*) FROM word WHERE game_id = game.id) words
FROM game %s ORDER BY createDate DESC
""" % condition)
if not game:
return None
game['rounds'] = Round.getByGame(game['id'])
game['roundsCount'] = len(game['rounds'])
game['roundsNumber'] = ", ".join(
[str(r['number']) for r in game['rounds']])
game['lastRoundNumber'] = game['rounds'][-1]['number']
game['lastRoundCreateDate'] = game['rounds'][-1]['createDate']
game['lastRoundWords'] = game['rounds'][-1]['words']
game['lastRoundPlayers'] = game['rounds'][-1]['players']
game['lastRoundPlayersPlain'] = "\n".join([
"%s: %s" % (p, str(w))
for p, w in game['lastRoundPlayers'].items()
]) if game['lastRoundPlayers'] else ""
return game
def get(**params):
return DB.getOne("""
SELECT *
FROM series
WHERE id = %(series_id)s
""",
jsonFields=['settings'],
params=params)
def getPlayerByWord(**params):
return DB.getOne(
"""
SELECT player.*
FROM player
JOIN word ON (player_id = player.id)
WHERE word.round_id = %(round_id)s AND word.game_id = %(game_id)s AND word = %(word)s
""", params)
def _isWordExist(**params):
return True if DB.getOne(
"""
SELECT *
FROM word
WHERE word = %(word)s AND series_id = %(series_id)s
ORDER BY createDate DESC
LIMIT 1
""", params) else False
def getPlayerLastGame(**params):
return DB.getOne("""
SELECT game.*
FROM player
JOIN game ON (game.id = player.game_id)
WHERE player.id = %(player_id)s AND player.series_id = %(series_id)s
""",
params,
jsonFields=['settings'])
def getFullInfo(**params):
return DB.getOne("""
SELECT game.*, round_id, round.status roundStatus, round.number roundNumber
FROM game_has_round
JOIN game ON (game.id = game_has_round.game_id)
JOIN round ON (round.id = game_has_round.round_id)
WHERE game.id = %(game_id)s
ORDER BY game_has_round.id DESC
LIMIT 1
""",
params,
jsonFields=['settings'])
def getLastGameInSeries(**params):
condition = ""
if 'status' in params:
condition = " AND status IN (%(status)s)"
return DB.getOne("""
SELECT game.*
FROM game
WHERE series_id = %(series_id)s """ + condition + """
ORDER BY game.id DESC
LIMIT 1
""",
params,
jsonFields=['settings'])
def _init(game_id):
params = dict(game_id=game_id, status=Round.STATUS_ENDED)
lastRound = DB.getOne(
"SELECT * FROM round WHERE game_id = %(game_id)s ORDER BY number DESC LIMIT 1",
dict(game_id=game_id))
if not lastRound:
round_id = Round._start(game_id=game_id, number=1)
logging.info("New %d round for game_id %d was started. ID: %s" %
(1, game_id, round_id))
Round._registerRoundInGame(game_id=game_id, round_id=round_id)
return round_id
if lastRound['status'] != Round.STATUS_ENDED:
return lastRound['id']
params['number'] = lastRound['number'] + 1
if not DB.getOne(
"SELECT * FROM round WHERE game_id = %(game_id)s LIMIT 1",
params):
params['number'] = 1
if 'number' in params:
round_id = Round._start(game_id=game_id, number=params['number'])
logging.info("New %d round for game_id %d was started. ID: %s" %
(params['number'], game_id, round_id))
Round._registerRoundInGame(game_id=game_id, round_id=round_id)
return round_id
def get(**params):
player = DB.getOne(
"""
SELECT
player.*,
series.name series_name,
series_has_player.password series_password,
game_has_player.password game_password,
series_has_player.role series_role,
game_has_player.role game_role
FROM player
LEFT JOIN series ON (series.id = player.series_id)
LEFT JOIN game_has_player ON (game_has_player.player_id = player.id AND game_has_player.game_id = player.game_id)
LEFT JOIN series_has_player ON (series_has_player.player_id = player.id AND series_has_player.series_id = player.series_id)
WHERE telegram_id = %(telegram_id)s
""", params)
return player
def update(wordMinLength, **params):
params['oldWord'] = params['oldWord'].lower()
params['newWord'] = params['newWord'].lower()
if params['newWord'] == params['oldWord']:
return "И ты прислал два одинаковых слова... Зачем ты так глуп, а?"
oldWord = DB.getOne(
"""
SELECT *
FROM word
WHERE word = %(oldWord)s AND player_id = %(player_id)s AND round_id = %(round_id)s AND game_id = %(game_id)s
""", params)
if not oldWord:
return "У тебя нет такого словца в последнем раунде или он уже завершён, дурында!"
status, response = Word.isWordValid(word=params['newWord'],
series_id=params['series_id'],
wordMinLength=wordMinLength)
if not status:
return response
affectedRows = DB.execute("""
UPDATE word
SET word = '%s'
WHERE id = %d""" % (params['newWord'], oldWord['id'])).rowcount
return "Хм... Я не смог обновить словцо. Интересно почему?" if not affectedRows else "Словцо успешно обновлено. Надеюсь, оно было получше прежнего"
def getGroupNumberByWordId(**params):
wordInGroup = DB.getOne(
"SELECT number FROM groups WHERE groups.game_id=%(game_id)s AND groups.round_id=%(round_id)s AND word_id=%(word_id)s",
params)
return wordInGroup['number'] if wordInGroup else None
def getState(**params):
playerState = DB.getOne(
"SELECT isReady FROM player_state WHERE player_id = %(player_id)s AND round_id = %(round_id)s",
params)
return 0 if not playerState else playerState['isReady']
def get(**params):
return DB.getOne("SELECT * FROM game WHERE id = %(game_id)s",
params,
jsonFields=['settings'])
def getLast(**params):
return DB.getOne(
"SELECT * FROM round WHERE game_id=%(game_id)s ORDER BY id DESC LIMIT 1",
params)
def get(round_id):
return DB.getOne("SELECT * FROM round WHERE id=%(round_id)s" %
dict(round_id=round_id))
def getIdByName(**params):
word = DB.getOne(
"SELECT id FROM word WHERE word = %(word)s AND word.round_id = %(round_id)s AND word.game_id = %(game_id)s",
params)
return word['id'] if word else None
def _getLastGroupNumber(params):
return DB.getOne("SELECT * FROM groups WHERE round_id = %(round_id)s",
params)
def getGamePassword(**params):
player = DB.getOne(
"SELECT * FROM game_has_player WHERE player_id = %(player_id)s AND game_id = %(game_id)s",
params)
return player['password'] if player else None
def getSeriesPassword(**params):
player = DB.getOne(
"SELECT * FROM series_has_player WHERE player_id = %(player_id)s AND series_id = %(series_id)s",
params)
return player['password'] if player else None
def getId(playerInfo):
player = DB.getOne(
"SELECT id FROM player WHERE telegram_id = %(telegram_id)s",
dict(telegram_id=playerInfo['id']))
return player['id'] if player else Player.add(playerInfo)
