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 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 getPlayerByRound(**params):
return DB.getList(
"""
SELECT *
FROM player
JOIN round ON (player_id = player.id)
WHERE round.id = %(round_id)s
""", 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 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 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 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 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 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)
def getListByRoundId(fullAccess=False, **params):
condition = " AND word.player_id = %(player_id)s" if 'player_id' in params else \
" AND player.telegram_id = %(telegram_id)s" if 'telegram_id' in params else \
" AND round.status = 'ended'" if not fullAccess else \
""
print(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)
WHERE word.round_id = %(round_id)s
""" + condition, params)
def get(groupByGame=True, log_id=None, **params):
groupsLog = DB.getList(
"""
SELECT
log.*,
round.number round_number,
groups.number group_number,
game.createDate game_createDate,
round.createDate round_createDate,
word.word winnerWord,
player.name winnerPlayer_name
FROM log
JOIN round ON (round.id = log.round_id)
JOIN game ON (game.id = log.game_id)
JOIN groups ON (groups.id = log.group_id)
JOIN word ON (word.id = log.winnerWord_id)
JOIN player ON (player.id = log.winnerPlayer_id)
WHERE log.game_id = %(game_id)s
""" + ((" AND id=%d" % log_id) if log_id else ""), params)
if not groupByGame:
return groupsLog
gamesLog = OrderedDict()
for groupLog in groupsLog:
if groupLog['game_id'] not in gamesLog:
gamesLog[groupLog['game_id']] = dict(
createDate=groupLog['game_createDate'].strftime(
'%Y-%m-%d %H:%M:%S'),
rounds=OrderedDict())
if groupLog['round_id'] not in gamesLog[
groupLog['game_id']]['rounds']:
gamesLog[groupLog['game_id']]['rounds'][
groupLog['round_id']] = dict(
createDate=groupLog['round_createDate'].strftime(
'%Y-%m-%d %H:%M:%S'),
number=groupLog['round_number'],
groups=OrderedDict())
Word.getListByGroupNumber(round_id=groupLog['round_id'],
groupNumber=groupLog['group_number'])
groupLog['number'] = groupLog['group_number']
groupLog['data'] = json.loads(groupLog['data'],
object_hook=OrderedDict)
gamesLog[groupLog['game_id']]['rounds'][groupLog['round_id']][
'groups'][groupLog['group_id']] = groupLog
return gamesLog
def get(groupByGroupNumber=False, **params):
numberCondition = " AND number = %(number)s" if 'number' in params else ""
groupsList = DB.getList(
"""
SELECT groups.id group_id, number, word, word.id word_id, p1.id player_id, p1.name, p1.telegram_id, weight, p2.id electorPlayer_id, p2.name electorName
FROM groups
JOIN word ON (word.id = groups.word_id)
JOIN player p1 ON (p1.id = word.player_id)
LEFT JOIN vote ON (vote.word_id = groups.word_id AND vote.game_id = %(game_id)s AND vote.round_id = %(round_id)s)
LEFT JOIN player p2 ON (p2.id = vote.player_id)
WHERE groups.game_id = %(game_id)s AND groups.round_id = %(round_id)s
""" + numberCondition, params)
if not groupByGroupNumber:
return groupsList
groupedGroups = OrderedDict()
for group in groupsList:
if group['number'] not in groupedGroups:
groupedGroups[group['number']] = []
groupedGroups[group['number']].append(group)
return groupedGroups
def getList():
return DB.getList("""
SELECT *
FROM series
""",
jsonFields=['settings'])