def add_item_menu(self, inventory):
# Add a nil_type so that the NOT IN clause always has a valid syntax if there are no weapons in the
# inventory
weapon_types = [w.type for w in inventory.weapons()] + ['nil_type']
fetch_armors = len(inventory.armors()) == 0
weapons_query = """
SELECT items.*, weapons.* FROM weapons
INNER JOIN items ON weapons.item_id = items.id
WHERE weapons.type NOT IN {0}
AND weapons.item_id IN (SELECT item_id FROM owns WHERE username=%(username)s)
""".format(sql.sql_format_tuple(weapon_types))
armors_query = """
SELECT items.*, armors.* FROM armors
INNER JOIN items ON armors.item_id = items.id
WHERE armors.item_id IN (SELECT item_id FROM owns WHERE username=%(username)s)
"""
armors = []
with self.connection.cursor() as cursor:
cursor.execute(weapons_query, {"username": self.player.username})
weapons = list(sql.objects(cursor))
if fetch_armors:
cursor.execute(armors_query,
{"username": self.player.username})
armors = list(sql.objects(cursor))
options = weapons + armors
descriptions = []
for weapon in weapons:
descriptions.append('%s -- %s, weight: %d, range: %d, damage: %d' %
(weapon.name, weapon.type, weapon.weight,
weapon.range, weapon.damage))
for armor in armors:
descriptions.append('%s -- Armor, weight: %d, protection: %d' %
(armor.name, armor.weight, armor.protection))
if len(options) == 0:
print("Your inventory is already full! Please remove items first"
" if you want to add a different weapon or armor.")
else:
new_item = prompts.select_from_list(
options,
descriptions, "Please select an item from this list to add"
" to your inventory",
interactive_with_single_option=True)
with self.connection.cursor() as cursor:
cursor.execute(
"INSERT INTO inventory_contains (username, name, item_id)"
"VALUES (%(username)s, %(name)s, %(item_id)s)", {
"username": self.player.username,
"name": inventory.name,
"item_id": new_item.id
})
def initialize_game_session(self, teams, game_map, game_type,
player_inventories):
with self.connection.cursor() as cursor:
insert_query = sql.insert_query('game_sessions',
['game_type', 'map_name'],
[(game_type, game_map)],
return_fields=['*'])
cursor.execute(insert_query)
game_session = next(sql.objects(cursor))
# Insert records into the plays table
plays_tuples = []
for i, team in enumerate(teams):
for player in team:
plays_tuples.append(
(player, player_inventories[player].name,
game_session.gid, i + 1))
plays_insert_query = sql.insert_query(
'plays', ('username', 'inventory_name', 'gid', 'team_number'),
plays_tuples)
cursor.execute(plays_insert_query)
return game_session
def get_for_player(connection, username):
with connection.cursor() as cursor:
cursor.execute(
"SELECT * FROM inventories WHERE username=%(username)s",
{"username": username})
return [
Inventory(connection, record) for record in sql.objects(cursor)
]
def create_new_inventory(self):
print("Please choose a name for your new inventory!")
new_name = self.prompt_for_name()
with self.connection.cursor() as cursor:
query = sql.insert_query('inventories', ('username', 'name'),
[(self.player.username, new_name)],
return_fields='*')
cursor.execute(query)
inventory = next(sql.objects(cursor))
return Inventory(self.connection, inventory)
def get_recent_games(self):
query = """
WITH recent_games AS (
SELECT plays.gid,
game_sessions.map_name,
game_sessions.game_type,
game_sessions.winning_team,
game_sessions.start_time,
game_sessions.end_time
FROM plays
INNER JOIN game_sessions ON plays.gid = game_sessions.gid
WHERE username = %(username)s
ORDER BY start_time DESC
LIMIT 5
)
SELECT inventory_name,
kills,
deaths,
assists,
map_name,
game_type,
team_number = winning_team as win,
start_time,
end_time
FROM plays
INNER JOIN recent_games
ON plays.gid = recent_games.gid
WHERE username = %(username)s;
"""
with self.connection.cursor() as cursor:
cursor.execute(query, {"username": self.player.username})
game_stats = sql.objects(cursor)
table = []
for game in game_stats:
outcome = 'Win' if game.win else 'Loss'
start = game.start_time.strftime("%Y-%m-%d %H:%M:%S")
duration_timedelta = game.end_time - game.start_time
minutes = duration_timedelta.seconds // 60
table.append([
start, outcome, game.game_type, game.map_name, game.kills,
game.deaths, game.assists,
'%.2f' % (game.kills / game.deaths),
'%d minutes' % minutes
])
print(
tabulate.tabulate(table, [
'Start Time', 'Outcome', 'Game Type', 'Map', 'Kills',
'Deaths', 'Assists', 'KDR', 'Duration'
]))
def create_new_guild(self):
print("Please choose a name for your guild")
new_name = self.prompt_for_name()
with self.connection.cursor() as cursor:
query = sql.insert_query('guilds', ('admin_username', 'name'),
[(self.player.username, new_name)],
return_fields='*')
cursor.execute(query)
guild = next(sql.objects(cursor))
self.set_player_guild(self.player, guild)
self.connection.commit()
def armors_menu(self):
query = """
SELECT items.*, armors.* FROM armors
INNER JOIN items ON armors.item_id = items.id
WHERE items.id NOT IN (SELECT item_id FROM owns WHERE username=%(username)s)
"""
with self.connection.cursor() as cursor:
cursor.execute(query, {"username": self.player.username})
armors = list(sql.objects(cursor))
def formatter(armor):
return 'Price: %d -- %s -- weight: %d, protection: %d' % \
(armor.price, armor.name, armor.weight, armor.protection)
self.purchase_menu_loop(
armors, formatter,
'You have already purchased all the armors in the game!')
def weapons_menu(self):
query = """
SELECT items.*, weapons.* FROM weapons
INNER JOIN items ON weapons.item_id = items.id
WHERE items.id NOT IN (SELECT item_id FROM owns WHERE username=%(username)s)
"""
with self.connection.cursor() as cursor:
cursor.execute(query, {"username": self.player.username})
weapons = list(sql.objects(cursor))
def formatter(w):
return 'Price: %d -- %s -- weight: %d, range: %d, damage: %d' % \
(w.price, w.name, w.weight, w.range, w.damage)
self.purchase_menu_loop(
weapons, formatter,
'You have already purchased all the weapons in the game!')
def add_new_attachment(self, inventory, weapon, current_attachments):
query = """
SELECT items.id, items.name, items.weight FROM attachments
INNER JOIN items ON attachments.item_id = items.id
WHERE attachments.attaches_to_id = %(weapon_item_id)s
AND attachments.item_id NOT IN (
SELECT item_id AS id FROM inventory_contains
WHERE username=%(username)s AND name=%(name)s
)
AND attachments.item_id IN (
SELECT item_id AS id FROM owns
WHERE username=%(username)s
)
"""
with self.connection.cursor() as cursor:
cursor.execute(
query, {
"weapon_item_id": weapon.id,
"username": self.player.username,
"name": inventory.name
})
attachments = list(sql.objects(cursor))
if len(attachments) == 0:
print("You don't own any additional attachments for this item!")
else:
print("Which attachment would you like to add.")
descriptions = [
"%s, weight: %d" % (a.name, a.weight) for a in attachments
]
attachment = prompts.select_from_list(
attachments,
descriptions,
"Choose a number from this list:",
interactive_with_single_option=True)
with self.connection.cursor() as cursor:
cursor.execute(
"INSERT INTO inventory_contains (username, name, item_id)"
"VALUES (%(username)s, %(name)s, %(item_id)s)", {
"username": self.player.username,
"name": inventory.name,
"item_id": attachment.id
})
def join_a_guild(self):
query = "SELECT admin_username, name, created_date FROM guilds"
with self.connection.cursor() as cursor:
cursor.execute(query)
guilds = list(sql.objects(cursor))
descriptions = [
'%s -- Admin: %s, active since %s' %
(g.name, g.admin_username, g.created_date.strftime("%Y-%m-%d"))
for g in guilds
] + ['Exit without joining anything']
guilds.append('exit')
option = prompts.select_from_list(
guilds, descriptions, "Which guild would you like to join?")
if option == 'exit':
return
self.set_player_guild(self.player, option)
self.connection.commit()
print("Welcome to %s!" % option.name)
def attachments_menu(self):
query = """
SELECT items.*, attaches_to.name as attaches_to_name FROM attachments
INNER JOIN items ON attachments.item_id = items.id
INNER JOIN items AS attaches_to ON attachments.attaches_to_id = attaches_to.id
WHERE attaches_to.id IN (SELECT item_id FROM owns WHERE username=%(username)s)
AND items.id NOT IN (SELECT item_id FROM owns WHERE username=%(username)s)
ORDER BY attaches_to_name
"""
with self.connection.cursor() as cursor:
cursor.execute(query, {"username": self.player.username})
attachments = list(sql.objects(cursor))
def formatter(att):
return 'Price: %d -- For: %s -- %s -- weight: %d' % \
(att.price, att.attaches_to_name, att.name, att.weight)
self.purchase_menu_loop(
attachments, formatter,
"None of the items you own have any attachments available, or you've already purchased"
" all the available ones!")
def get_inventory_stats(self):
# Get the win rate by inventory (coalescing so that inventories that aren't used show up in the list)
query = """
SELECT
name,
coalesce(stats.kdr, 0) as kdr,
coalesce(stats.winrate, 0) as winrate,
coalesce(stats.games_played, 0) as games_played
FROM inventories
LEFT JOIN (
SELECT inventory_name,
round(sum(kills)::NUMERIC / sum(deaths)::NUMERIC, 2) as kdr,
round(
(count(*) FILTER (WHERE game_sessions.winning_team = plays.team_number))::NUMERIC /
count(*)::NUMERIC, 2
) as winrate,
count(*) AS games_played
FROM plays
INNER JOIN game_sessions ON plays.gid = game_sessions.gid
WHERE username = %(username)s
GROUP BY inventory_name
) as stats ON inventories.name = stats.inventory_name
WHERE username = %(username)s
ORDER BY games_played DESC;
"""
with self.connection.cursor() as cursor:
cursor.execute(query, {"username": self.player.username})
inventory_stats = list(sql.objects(cursor))
overall_kdr = 0
overall_winrate = 0
total_games = 0
for inventory_entry in inventory_stats:
overall_kdr += inventory_entry.games_played * inventory_entry.kdr
overall_winrate += inventory_entry.games_played * inventory_entry.winrate
total_games += inventory_entry.games_played
return inventory_stats, overall_kdr / total_games, overall_winrate / total_games, total_games
def main():
with database.connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"SELECT *, exp_requirement(level) as exp_requirement FROM players WHERE banned_date IS NULL "
"ORDER BY username")
players = list(sql.objects(cursor))
try:
print("Type q or Q at any time to exit the application.")
# First step is to select a player
player = prompts.select_from_list(
players, [p.username for p in players],
"Please pick a user from this list to play as")
print("Welcome back %s!" % player.username)
menu_item_descriptions = [
flow.prompt_text for flow in flows.menu_items
]
while True:
print(end='\n\n')
flow_class = prompts.select_from_list(
flows.menu_items, menu_item_descriptions,
"Please select something to do!")
with database.connect() as connection:
flow_object = flow_class(player, connection)
flow_object.run()
# Reload the player as data may have changed
player = reload_player(player, connection)
except prompts.QuitException:
return
def attachments(self):
if 'attachments' in self.cache:
return self.cache['attachments']
with self.connection.cursor() as cursor:
query = """
SELECT items.id, items.name, attachments.attaches_to_id FROM inventory_contains
INNER JOIN items
ON items.id = inventory_contains.item_id
INNER JOIN attachments
ON items.id = attachments.item_id
WHERE inventory_contains.username = %(username)s
AND inventory_contains.name = %(name)s
"""
cursor.execute(
query, {
"username": self.inventory_record.username,
"name": self.inventory_record.name
})
attachments = list(sql.objects(cursor))
self.cache['attachments'] = attachments
return attachments
def weapons(self):
if 'weapons' in self.cache:
return self.cache['weapons']
with self.connection.cursor() as cursor:
query = """
SELECT items.id, items.name, weapons.type FROM inventory_contains
INNER JOIN items
ON items.id = inventory_contains.item_id
INNER JOIN weapons
ON items.id = weapons.item_id
WHERE inventory_contains.username = %(username)s
AND inventory_contains.name = %(name)s
"""
cursor.execute(
query, {
"username": self.inventory_record.username,
"name": self.inventory_record.name
})
weapons = list(sql.objects(cursor))
self.cache['weapons'] = weapons
return weapons
def reload_player(player, connection):
query = "SELECT *, exp_requirement(level) as exp_requirement FROM players WHERE username=%(username)s"
with connection.cursor() as cursor:
cursor.execute(query, {"username": player.username})
return next(sql.objects(cursor))
def reload_player(self):
with self.connection.cursor() as cursor:
cursor.execute("SELECT * FROM players WHERE username=%(username)s",
{"username": self.player.username})
self.player = next(sql.objects(cursor))
def run(self):
print()
print("Simulating a game!")
teams, player_inventories = self.select_teams_and_inventories()
print(tabulate.tabulate(teams, headers=["Team1", "Team2"]))
print()
game_map = random.choice(MAPS)
game_type = random.choice(GAME_TYPES)
print("Playing %s on the map: %s" %
(game_type.replace("-", " "), game_map))
print()
inventory = self.choose_inventory()
player_inventories[self.player.username] = inventory
game_session = self.initialize_game_session(teams, game_map, game_type,
player_inventories)
kills, deaths, assists, winning_team = self.simulate_game_events(
teams, game_session)
self.update_player_kda(game_session, kills, deaths, assists)
previous_level = self.player.level
# Close out the game session using the close_game prepared statement.
query = """
SELECT * FROM close_game(%(gid)s, %(winning_team)s, %(game_end)s);
"""
with self.connection.cursor() as cursor:
cursor.execute(
query, {
"gid": game_session.gid,
"winning_team": winning_team,
"game_end": game_session.start_time + timedelta(minutes=20)
})
leaderboard = list(sql.objects(cursor))
print("\nFinal leaderboard:")
for team, players in itertools.groupby(
leaderboard, key=lambda player: player.team_number):
print("Team %d" % team)
table = [[
i + 1, player.username, player.kills, player.deaths,
player.assists, player.exp_gained
] for i, player in enumerate(players)]
print(
tabulate.tabulate(table,
headers=[
'Rank', 'Player', 'Kills', 'Deaths',
'Assists', 'XP Gain'
]))
print()
self.connection.commit()
with self.connection.cursor() as cursor:
cursor.execute(
"SELECT level, experience, exp_requirement(level) as req FROM players WHERE username=%(username)s",
{"username": self.player.username})
updated_pts = next(sql.objects(cursor))
exp_we_gained = [
p.exp_gained for p in leaderboard
if p.username == self.player.username
][0]
if updated_pts.level > previous_level:
print("Congratulations, you've leveled up to %d!" %
updated_pts.level)
print("You have %d/%d experience points to reach level %d" %
(updated_pts.experience, updated_pts.req,
updated_pts.level + 1))
else:
print(
"You gained %d experience points, you now have %d/%d experience points required to reach level %d"
% (exp_we_gained, updated_pts.experience, updated_pts.req,
updated_pts.level + 1))