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)