def set(**params):
DB.execute(
"""
INSERT INTO vote
SET word_id = %(word_id)s, game_id = %(game_id)s, round_id = %(round_id)s, weight = %(weight)s, player_id = %(player_id)s
ON DUPLICATE KEY UPDATE weight = %(weight)s
""", params)
def add(wordsLimit, wordMinLength, **params):
params['word'] = params['word'].strip().lower()
wordsForToday = DB.getList(
"""
SELECT *
FROM word
WHERE player_id = %(player_id)s AND game_id = %(game_id)s AND round_id = %(round_id)s AND series_id = %(series_id)s
""", params)
if len(wordsForToday) >= wordsLimit:
return False, Word.ERROR_CODES['NOT_IN_TIME_WORD'] % wordsLimit
status, response = Word.isWordValid(params['word'],
params['series_id'], wordMinLength)
if not status:
return False, response
DB.execute(
"""
INSERT INTO word
SET word = %(word)s, player_id = %(player_id)s, game_id = %(game_id)s, round_id = %(round_id)s, series_id = %(series_id)s
""", params)
wordsForToday = DB.getList(
"""
SELECT *
FROM word
WHERE player_id = %(player_id)s AND game_id = %(game_id)s AND round_id = %(round_id)s AND series_id = %(series_id)s
""", params)
additionalMsg = ""
if len(wordsForToday) == wordsLimit:
additionalMsg = " У тебя больше не осталось словцов в этом раунде, растяпа!"
if len(wordsForToday) < wordsLimit:
additionalMsg = " Ты можешь предложить ещё %d смешных словца" % (
wordsLimit - len(wordsForToday))
return True, "Твоё жалкое словцо \"%s\" принято, свинюшка! %s" % (
params['word'], additionalMsg)
def _registerRoundInGame(**params):
DB.execute(
"""
INSERT INTO game_has_round
SET
game_id = %(game_id)s,
round_id = %(round_id)s
""", params)
def setState(**params):
DB.execute(
"""
INSERT IGNORE INTO player_state
SET isReady = 1, round_id = %(round_id)s, player_id = %(player_id)s
ON DUPLICATE KEY UPDATE isReady=ABS(isReady - 1)
""", params)
return Player.getState(**params)
def setSeriesPassword(**params):
params['password'] = md5(params['password'] +
Config.get('MISC.password_salt'))
DB.execute(
"""
UPDATE series_has_player
SET password = %(password)s
WHERE series_id = %(series_id)s AND player_id = %(player_id)s
""", params)
def setSeriesPassword(**params):
DB.execute(
"""
INSERT IGNORE INTO series_has_player
SET
series_id = %(series_id)s,
player_id = %(player_id)s,
password = %(password)s
ON DUPLICATE KEY UPDATE password = %(password)s
""", params)
def setGamePassword(**params):
DB.execute(
"""
INSERT IGNORE INTO game_has_player
SET
game_id = %(game_id)s,
player_id = %(player_id)s,
password = %(password)s
ON DUPLICATE KEY UPDATE password = %(password)s
""", params)
def joinGame(**params):
DB.execute(
"""
INSERT IGNORE INTO game_has_player
SET
game_id = %(game_id)s,
player_id = %(player_id)s,
role = %(role)s
""", params)
DB.execute(
"""
UPDATE player
SET game_id = %(game_id)s
WHERE id = %(player_id)s
""", params)
def joinSeries(**params):
DB.execute(
"""
INSERT IGNORE INTO series_has_player
SET
series_id = %(series_id)s,
player_id = %(player_id)s,
role = %(role)s
""", params)
DB.execute(
"""
UPDATE player
SET series_id = %(series_id)s, game_id = 0
WHERE id = %(player_id)s
""", params)
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 _init(**params):
if 'status' not in params:
params['status'] = Game.STATUS_PREPARATION
game_id = DB.execute(
"""
INSERT INTO game
SET
creator_id = %(player_id)s,
status = %(status)s,
settings = %(settings)s,
series_id = %(series_id)s
""", params).lastrowid
logging.info("New game was started. ID: %d" % game_id)
Round.getId(game_id)
params[
'role'] = Series.PLAYER_ROLE_MEMBER if 'seriesRole' not in params else params[
'seriesRole']
Player.joinSeries(**params)
params[
'role'] = Game.PLAYER_ROLE_ADMIN if 'gameRole' not in params else params[
'gameRole']
Player.joinGame(game_id=game_id, **params)
return game_id
def update(**params):
return DB.execute(
"""
UPDATE game
SET winner_id = %(winner_id)s, winnerWord_id=%(winnerWord_id)s, status = %(status)s
WHERE id = %(game_id)s
""", params)
def getList(self, limit=None):
self._refreshSeriesState()
if not limit:
limit = 100
gamesList = DB.getList("""
SELECT
game.id,
game.createDate,
game.status,
p1.name winner_name,
p2.name creator_name,
word
FROM game
LEFT JOIN player p1 ON (p1.id=game.winner_id)
JOIN player p2 ON (p2.id=game.creator_id)
LEFT JOIN word ON (word.id=game.winner_id)
WHERE game.series_id = %d
ORDER BY createDate DESC
LIMIT %d
""" % (self._seriesState['id'], limit))
if not gamesList:
return "Возмутительно! До сих пор не было сыграно ни одной игры!"
responseList = ["Список последних %d игр" % limit]
for game in gamesList:
responseList.append(
"Игра ID <b>%d</b> от %s. Статус: <b>%s</b>" %
(game['id'], game['createDate'].strftime('%Y-%m-%d %H:%M:%S'),
game['status']))
responseList.append("Лог игры: /gr_%d" % game['id'])
responseList.append("Автор: <b>%s</b>" % game['creator_name'])
responseList.append("Победитель: <b>%s (%s)</b>" %
(game['winner_name'], game['word']))
responseList.append("<pre></pre>")
return "\n".join(responseList)
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 getPlayerByRound(**params):
return DB.getList(
"""
SELECT *
FROM player
JOIN round ON (player_id = player.id)
WHERE round.id = %(round_id)s
""", 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 get(**params):
return DB.getOne("""
SELECT *
FROM series
WHERE id = %(series_id)s
""",
jsonFields=['settings'],
params=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 getListByGameId(fullAccess=False, **params):
condition = "word.player_id = %(player_id)s" if 'player_id' in params else "round.status = 'ended'" if not fullAccess else ""
return DB.getList(
"""
SELECT word.*, round.number
FROM word
JOIN round ON (round.id = word.round_id)
WHERE word.game_id = %(game_id)s AND
""" + condition, params)
def getPlayerAvailableGames(**params):
params['status'] = Game.STATUS_IN_PROGRESS
return DB.getList("""
SELECT game.*
FROM game
WHERE series_id = %(series_id)s AND status = %(status)s
""",
params,
jsonFields=['settings'])
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 getPlayerWeightPerRoundByWord(**params):
votes = DB.getList(
"""
SELECT word, vote.word_id, weight
FROM vote
JOIN word ON (word.id = vote.word_id)
WHERE vote.game_id = %(game_id)s AND vote.round_id = %(round_id)s AND vote.player_id = %(player_id)s
""", params)
return {vote['word_id']: vote
for vote in votes}, sum([vote['weight'] for vote in votes])
def getPlayerWeightOverAll(**params):
votes = DB.getList(
"""
SELECT word, vote.word_id, weight
FROM vote
JOIN word ON (word.id = vote.word_id)
WHERE vote.player_id = %(player_id)s
""", params)
return {vote['word_id']: vote
for vote in votes}, sum([vote['weight'] for vote in votes])
def getListByGroupNumber(**params):
return DB.getList(
"""
SELECT word.*, player.name, player.telegram_id, round.number
FROM word
JOIN round ON (round.id = word.round_id)
JOIN player ON (player.id = word.player_id)
JOIN groups ON (groups.word_id = word.id AND groups.round_id = %(round_id)s AND groups.number = %(groupNumber)s)
WHERE word.round_id = %(round_id)s
""", params)
def add(update=None, name=None, telegram_id=None):
name = Player._buildPlayerName(
update.message.chat) if not name else name
telegram_id = update.message.chat.id if not telegram_id else telegram_id
player_id = DB.execute(
"""
INSERT INTO player
SET name = %(name)s, telegram_id = %(telegram_id)s
""", dict(name=name, telegram_id=telegram_id)).lastrowid
logging.info("Player '%s' was added. ID: %s" % (name, player_id))
return player_id
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 save(**params):
return DB.execute(
"""
INSERT INTO log
SET
game_id = %(game_id)s,
round_id = %(round_id)s,
group_id = %(group_id)s,
data = %(data)s,
winnerPlayer_id = %(winnerPlayer_id)s,
winnerWord_id = %(winnerWord_id)s
""", params).lastrowid
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 getByGame(game_id):
roundList = DB.getList("""
SELECT *,
(
SELECT count(*)
FROM word
WHERE round_id = round.id
) words
FROM round
WHERE game_id=%(game_id)s
""" % dict(game_id=game_id))
for _round in roundList:
players = DB.getList(
"""
SELECT name, count(*) count
FROM word
JOIN player ON (player.id = player_id)
WHERE round_id = %(round_id)s
GROUP by player_id
""", dict(round_id=_round['id']))
_round['players'] = {w['name']: w['count'] for w in players}
return roundList
def getList(**params):
conditions = ""
if 'status' in params:
conditions += " AND status IN %(status)s"
if 'creator_id' in params:
conditions += " AND creator_id = %(creator_id)s"
if 'series_id' in params:
conditions += " AND series_id = %(series_id)s"
return DB.getList(
"""
SELECT *
FROM game
WHERE 1
""" + conditions, params)