def get_map_area(x,
y,
z,
size=15,
scale=8,
crosshair=True,
client_coordinates=True):
"""Gets a minimap picture of a map area
size refers to the radius of the image in actual tibia sqm
scale is how much the image will be streched (1 = 1 sqm = 1 pixel)
client_coordinates means the coordinate origin used is the same used for the Tibia Client
If set to False, the origin will be based on the top left corner of the map.
"""
if client_coordinates:
x -= 124 * 256
y -= 121 * 256
c = tibiaDatabase.cursor()
c.execute("SELECT * FROM map WHERE z LIKE ?", (z, ))
result = c.fetchone()
im = Image.open(io.BytesIO(bytearray(result['image'])))
im = im.crop((x - size, y - size, x + size, y + size))
im = im.resize((size * scale, size * scale))
if crosshair:
draw = ImageDraw.Draw(im)
width, height = im.size
draw.line((0, height / 2, width, height / 2), fill=128)
draw.line((width / 2, 0, width / 2, height), fill=128)
img_byte_arr = io.BytesIO()
im.save(img_byte_arr, format='png')
img_byte_arr = img_byte_arr.getvalue()
return img_byte_arr
def get_imbuement(name):
"""Returns a dictionary containing an item's info, if no exact match was found, it returns a list of suggestions.
The dictionary has the following keys: name, look_text, npcs_sold*, value_sell, npcs_bought*, value_buy.
*npcs_sold and npcs_bought are list, each element is a dictionary with the keys: name, city."""
# Reading item database
c = tibiaDatabase.cursor()
# Search query
c.execute(
"SELECT * FROM imbuements WHERE name LIKE ? ORDER BY LENGTH(name) ASC LIMIT 15",
("%" + name + "%", ))
result = c.fetchall()
if len(result) == 0:
return None
elif result[0]["name"].lower() == name.lower() or len(result) == 1:
imbuement = result[0]
else:
return [x['name'] for x in result]
try:
c.execute(
"SELECT items.title as name, amount "
"FROM imbuements_materials "
"INNER JOIN items on items.id = imbuements_materials.item_id "
"WHERE imbuement_id = ?", (imbuement["id"], ))
imbuement["materials"] = c.fetchall()
return imbuement
finally:
c.close()
def get_npc(name):
"""Returns a dictionary containing a NPC's info, a list of possible matches or None"""
c = tibiaDatabase.cursor()
try:
# search query
c.execute(
"SELECT * FROM NPCs WHERE title LIKE ? ORDER BY LENGTH(title) ASC LIMIT 15",
("%" + name + "%", ))
result = c.fetchall()
if len(result) == 0:
return None
elif result[0]["title"].lower() == name.lower or len(result) == 1:
npc = result[0]
else:
return [x["title"] for x in result]
npc["image"] = 0
c.execute(
"SELECT Items.name, Items.category, BuyItems.value FROM BuyItems, Items "
"WHERE Items.id = BuyItems.itemid AND BuyItems.vendorid = ?",
(npc["id"], ))
npc["sell_items"] = c.fetchall()
c.execute(
"SELECT Items.name, Items.category, SellItems.value FROM SellItems, Items "
"WHERE Items.id = SellItems.itemid AND SellItems.vendorid = ?",
(npc["id"], ))
npc["buy_items"] = c.fetchall()
return npc
finally:
c.close()
def get_spell(name):
"""Returns a dictionary containing a spell's info, a list of possible matches or None"""
c = tibiaDatabase.cursor()
try:
c.execute("SELECT * FROM spells WHERE words LIKE ? or name LIKE ?", (name,)*2)
spell = c.fetchone()
if spell is None:
c.execute("SELECT * FROM spells WHERE words LIKE ? OR name LIKE ? ORDER BY LENGTH(name) LIMIT 15",
("%" + name + "%",)*2)
result = c.fetchall()
if len(result) == 0:
return None
elif result[0]["name"].lower() == name.lower() or result[0]["words"].lower() == name.lower() or len(
result) == 1:
spell = result[0]
else:
return ["{name} ({words})".format(**x) for x in result]
spell["npcs"] = []
c.execute("""SELECT npcs.title as name, npcs.city, npcs_spells.knight, npcs_spells.paladin,
npcs_spells.sorcerer, npcs_spells.druid FROM npcs, npcs_spells
WHERE npcs_spells.spell_id = ? AND npcs_spells.npc_id = npcs.id""", (spell["id"],))
result = c.fetchall()
for npc in result:
npc["city"] = npc["city"].title()
spell["npcs"].append(npc)
return spell
finally:
c.close()
def get_monster(name):
"""Returns a dictionary with a monster's info, if no exact match was found, it returns a list of suggestions.
The dictionary has the following keys: name, id, hp, exp, maxdmg, elem_physical, elem_holy,
elem_death, elem_fire, elem_energy, elem_ice, elem_earth, elem_drown, elem_lifedrain, senseinvis,
arm, image."""
# Reading monster database
c = tibiaDatabase.cursor()
c.execute("SELECT * FROM creatures WHERE title LIKE ? ORDER BY LENGTH(title) ASC LIMIT 15", ("%" + name + "%",))
result = c.fetchall()
if len(result) == 0:
return None
elif result[0]["title"].lower() == name.lower() or len(result) == 1:
monster = result[0]
else:
return [x['title'] for x in result]
try:
if monster['hitpoints'] is None or monster['hitpoints'] < 1:
monster['hitpoints'] = None
c.execute("SELECT items.title as item, chance, min, max "
"FROM creatures_drops, items "
"WHERE items.id = creatures_drops.item_id AND creature_id = ? "
"ORDER BY chance DESC",
(monster["id"],))
monster["loot"] = c.fetchall()
return monster
finally:
c.close()
def get_item(name):
"""Returns a dictionary containing an item's info, if no exact match was found, it returns a list of suggestions.
The dictionary has the following keys: name, look_text, npcs_sold*, value_sell, npcs_bought*, value_buy.
*npcs_sold and npcs_bought are list, each element is a dictionary with the keys: name, city."""
# Reading item database
c = tibiaDatabase.cursor()
# Search query
c.execute("SELECT * FROM items WHERE title LIKE ? ORDER BY LENGTH(title) ASC LIMIT 15", ("%" + name + "%",))
result = c.fetchall()
if len(result) == 0:
return None
elif result[0]["title"].lower() == name.lower() or len(result) == 1:
item = result[0]
else:
return [x['title'] for x in result]
try:
c.execute("SELECT npc.name, npc.city, npcs_selling.value, currency.name as currency "
"FROM npcs_selling "
"LEFT JOIN npcs npc on npc.id = npc_id "
"LEFT JOIN items currency on currency.id = currency "
"WHERE item_id = ? "
"ORDER BY npcs_selling.value ASC", (item["id"],))
item["sellers"] = c.fetchall()
c.execute("SELECT npc.name, npc.city, npcs_buying.value, currency.name as currency "
"FROM npcs_buying "
"LEFT JOIN npcs npc on npc.id = npc_id "
"LEFT JOIN items currency on currency.id = currency "
"WHERE item_id = ? "
"ORDER BY npcs_buying.value DESC", (item["id"],))
item["buyers"] = c.fetchall()
c.execute("SELECT creature.title as name, chance "
"FROM creatures_drops "
"LEFT JOIN creatures creature on creature.id = creature_id "
"WHERE item_id = ? "
"ORDER BY chance DESC ", (item["id"],))
item["loot_from"] = c.fetchall()
c.execute("SELECT quests.name "
"FROM quests_rewards "
"INNER JOIN quests ON quests.id = quests_rewards.quest_id "
"WHERE item_id = ? ", (item["id"],))
item["quests_reward"] = c.fetchall()
# Get item's properties:
c.execute("SELECT * FROM items_attributes WHERE item_id = ?", (item["id"],))
results = c.fetchall()
item["attributes"] = {}
for row in results:
if row["attribute"] == "imbuement":
temp = item["attributes"].get("imbuements", list())
temp.append(row["value"])
item["attributes"]["imbuements"] = temp
else:
item["attributes"][row["attribute"]] = row["value"]
return item
finally:
c.close()
def get_rashid_info() -> Dict[str, Union[str, int]]:
"""Returns a dictionary with rashid's info
Dictionary contains: the name of the week, city and x,y,z, positions."""
offset = get_tibia_time_zone() - get_local_timezone()
# Server save is at 10am, so in tibia a new day starts at that hour
tibia_time = dt.datetime.now() + dt.timedelta(hours=offset - 10)
c = tibiaDatabase.cursor()
c.execute("SELECT * FROM rashid_positions WHERE day = ?", (tibia_time.weekday(),))
info = c.fetchone()
c.close()
return info
def get_key(number):
"""Returns a dictionary containing a NPC's info, a list of possible matches or None"""
c = tibiaDatabase.cursor()
try:
# search query
c.execute("SELECT items_keys.*, item.image FROM items_keys "
"INNER JOIN items item ON item.id = items_keys.item_id "
"WHERE number = ? ", (number,))
result = c.fetchone()
return result
finally:
c.close()
def get_npc(name):
"""Returns a dictionary containing a NPC's info, a list of possible matches or None"""
c = tibiaDatabase.cursor()
try:
# search query
c.execute(
"SELECT * FROM npcs WHERE title LIKE ? ORDER BY LENGTH(title) ASC LIMIT 15",
("%" + name + "%", ))
result = c.fetchall()
if len(result) == 0:
return None
elif result[0]["title"].lower() == name.lower or len(result) == 1:
npc = result[0]
else:
return [x["title"] for x in result]
c.execute(
"SELECT item.title as name, npcs_selling.value, currency.name as currency "
"FROM npcs_selling "
"LEFT JOIN items item on item.id = item_id "
"LEFT JOIN items currency on currency.id = currency "
"WHERE npc_id = ? "
"ORDER BY npcs_selling.value DESC", (npc["id"], ))
npc["selling"] = c.fetchall()
c.execute(
"SELECT item.title as name, npcs_buying.value, currency.name as currency "
"FROM npcs_buying "
"LEFT JOIN items item on item.id = item_id "
"LEFT JOIN items currency on currency.id = currency "
"WHERE npc_id = ? "
"ORDER BY npcs_buying.value DESC", (npc["id"], ))
npc["buying"] = c.fetchall()
c.execute(
"SELECT spell.name, spell.price, npcs_spells.knight, npcs_spells.sorcerer, npcs_spells.paladin, "
"npcs_spells.druid "
"FROM npcs_spells "
"INNER JOIN spells spell ON spell.id = spell_id "
"WHERE npc_id = ? "
"ORDER BY price DESC", (npc["id"], ))
npc["spells"] = c.fetchall()
c.execute(
"SELECT destination as name, price, notes "
"FROM npcs_destinations "
"WHERE npc_id = ? "
"ORDER BY name ASC", (npc["id"], ))
npc["destinations"] = c.fetchall()
return npc
finally:
c.close()
def get_achievement(name):
"""Returns an achievement (dictionary), a list of possible matches or none"""
c = tibiaDatabase.cursor()
try:
# Search query
c.execute("SELECT * FROM Achievements WHERE name LIKE ? ORDER BY LENGTH(name) ASC LIMIT 15", ("%" + name + "%",))
result = c.fetchall()
if len(result) == 0:
return None
elif result[0]["name"].lower() == name.lower() or len(result) == 1:
return result[0]
else:
return [x['name'] for x in result]
finally:
c.close()
def search_key(terms):
"""Returns a dictionary containing a NPC's info, a list of possible matches or None"""
c = tibiaDatabase.cursor()
try:
# search query
c.execute("SELECT items_keys.*, item.image FROM items_keys "
"INNER JOIN items item ON item.id = items_keys.item_id "
"WHERE items_keys.name LIKE ? OR notes LIKE ? or origin LIKE ? LIMIT 10 ", ("%" + terms + "%",)*3)
result = c.fetchall()
if len(result) == 0:
return None
elif len(result) == 1:
return result[0]
return result
finally:
c.close()
def get_map_area(x, y, z, size=15, scale=8, crosshair=True):
"""Gets a minimap picture of a map area
size refers to the radius of the image in actual tibia sqm
scale is how much the image will be streched (1 = 1 sqm = 1 pixel)"""
c = tibiaDatabase.cursor()
c.execute("SELECT * FROM WorldMap WHERE z LIKE ?", (z, ))
result = c.fetchone()
im = Image.open(io.BytesIO(bytearray(result['image'])))
im = im.crop((x - size, y - size, x + size, y + size))
im = im.resize((size * scale, size * scale))
if crosshair:
draw = ImageDraw.Draw(im)
width, height = im.size
draw.line((0, height / 2, width, height / 2), fill=128)
draw.line((width / 2, 0, width / 2, height), fill=128)
img_byte_arr = io.BytesIO()
im.save(img_byte_arr, format='png')
img_byte_arr = img_byte_arr.getvalue()
return img_byte_arr
async def get_character(name, tries=5) -> Optional[Character]:
"""Fetches a character from TibiaData, parses and returns a Character object
The character object contains all the information available on Tibia.com
Information from the user's database is also added, like owner and highscores.
If the character can't be fetch due to a network error, an NetworkError exception is raised
If the character doesn 't exist, None is returned.
"""
if tries == 0:
log.error(
"get_character: Couldn't fetch {0}, network error.".format(name))
raise NetworkError()
try:
url = f"https://api.tibiadata.com/v2/characters/{urllib.parse.quote(name, safe='')}.json"
except UnicodeEncodeError:
return None
# Fetch website
try:
async with aiohttp.ClientSession() as session:
async with session.get(url) as resp:
content = await resp.text(encoding='ISO-8859-1')
except Exception:
await asyncio.sleep(config.network_retry_delay)
return await get_character(name, tries - 1)
content_json = json.loads(content)
character = Character.parse_from_tibiadata(content_json)
if character is None:
return None
if character.house is not None:
with closing(tibiaDatabase.cursor()) as c:
c.execute("SELECT id FROM houses WHERE name LIKE ?",
(character.house["name"].strip(), ))
result = c.fetchone()
if result:
character.house["houseid"] = result["id"]
# Database operations
c = userDatabase.cursor()
# Skills from highscores
c.execute("SELECT category, rank, value FROM highscores WHERE name LIKE ?",
(character.name, ))
results = c.fetchall()
if len(results) > 0:
character.highscores = results
# Discord owner
c.execute(
"SELECT user_id, vocation, name, id, world, guild FROM chars WHERE name LIKE ?",
(name, ))
result = c.fetchone()
if result is None:
# Untracked character
return character
character.owner = result["user_id"]
if result["vocation"] != character.vocation:
with userDatabase as conn:
conn.execute("UPDATE chars SET vocation = ? WHERE id = ?", (
character.vocation,
result["id"],
))
log.info(
"{0}'s vocation was set to {1} from {2} during get_character()"
.format(character.name, character.vocation,
result["vocation"]))
if result["name"] != character.name:
with userDatabase as conn:
conn.execute("UPDATE chars SET name = ? WHERE id = ?", (
character.name,
result["id"],
))
log.info("{0} was renamed to {1} during get_character()".format(
result["name"], character.name))
if result["world"] != character.world:
with userDatabase as conn:
conn.execute("UPDATE chars SET world = ? WHERE id = ?", (
character.world,
result["id"],
))
log.info(
"{0}'s world was set to {1} from {2} during get_character()".
format(character.name, character.world, result["world"]))
if character.guild is not None and result["guild"] != character.guild[
"name"]:
with userDatabase as conn:
conn.execute("UPDATE chars SET guild = ? WHERE id = ?", (
character.guild["name"],
result["id"],
))
log.info(
"{0}'s guild was set to {1} from {2} during get_character()".
format(character.name, character.guild["name"],
result["guild"]))
return character
async def loot(self, ctx: NabCtx):
"""Scans an image of a container looking for Tibia items and shows an approximate loot value.
An image must be attached with the message. The prices used are NPC prices only.
The image requires the following:
- Must be a screenshot of inventory windows (backpacks, depots, etc).
- Have the original size, the image can't be scaled up or down, however it can be cropped.
- The image must show the complete slot.
- JPG images are usually not recognized.
- PNG images with low compression settings take longer to be scanned or aren't detected at all.
The bot shows the total loot value and a list of the items detected, separated into the NPC that buy them.
"""
if ctx.author.id in self.processing_users and not checks.is_owner_check(
ctx):
await ctx.send(
"I'm already scanning an image for you! Wait for me to finish that one."
)
return
if len(ctx.message.attachments) == 0:
await ctx.send(
"You need to upload a picture of your loot and type the command in the comment."
)
return
attachment: discord.Attachment = ctx.message.attachments[0]
if attachment.height is None:
await ctx.send("That's not an image!")
return
if attachment.size > 2097152:
await ctx.send(
"That image was too big! Try splitting it into smaller images, or cropping out anything "
"irrelevant.")
return
if attachment.height < MIN_HEIGHT or attachment.width < MIN_WIDTH:
await ctx.send("That image is too small to be a loot image.")
return
try:
async with aiohttp.ClientSession() as session:
async with session.get(attachment.url) as resp:
loot_image = await resp.read()
except aiohttp.ClientError:
log.exception("loot: Couldn't parse image")
await ctx.send("I failed to load your image. Please try again.")
return
await ctx.send(
f"I've begun parsing your image, **@{ctx.author.display_name}**. "
"Please be patient, this may take a few moments.")
status_msg = await ctx.send("Status: Reading")
try:
# Owners are not affected by the limit.
self.processing_users.append(ctx.author.id)
start_time = time.time()
loot_list, loot_image_overlay = await loot_scan(
ctx, loot_image, status_msg)
scan_time = time.time() - start_time
except LootScanException as e:
await ctx.send(e)
return
finally:
self.processing_users.remove(ctx.author.id)
embed = discord.Embed(color=discord.Color.blurple())
embed.set_footer(text=f"Loot scanned in {scan_time:,.2f} seconds.")
long_message = f"These are the results for your image: [{attachment.filename}]({attachment.url})"
if len(loot_list) == 0:
await ctx.send(
f"Sorry {ctx.author.mention}, I couldn't find any loot in that image. Loot parsing will "
f"only work on high quality images, so make sure your image wasn't compressed."
)
return
total_value = 0
unknown = False
for item in loot_list:
if loot_list[item]['group'] == "Unknown":
unknown = loot_list[item]
break
groups = []
for item in loot_list:
if not loot_list[item]['group'] in groups and loot_list[item][
'group'] != "Unknown":
groups.append(loot_list[item]['group'])
has_marketable = False
for group in groups:
value = ""
group_value = 0
for item in loot_list:
if loot_list[item]['group'] == group and loot_list[item][
'group'] != "Unknown":
if group == "No Value":
value += f"x{loot_list[item]['count']} {item}\n"
else:
with closing(tibiaDatabase.cursor()) as c:
c.execute(
"SELECT name FROM items, items_attributes "
"WHERE name LIKE ? AND id = item_id AND attribute = 'imbuement'"
" LIMIT 1", (item, ))
result = c.fetchone()
if result:
has_marketable = True
emoji = "💎"
else:
emoji = ""
value += "x{1} {0}{3} \u2192 {2:,}gp total\n".format(
item, loot_list[item]['count'],
loot_list[item]['count'] *
loot_list[item]['value_sell'], emoji)
total_value += loot_list[item]['count'] * loot_list[item][
'value_sell']
group_value += loot_list[item]['count'] * loot_list[item][
'value_sell']
if group == "No Value":
name = group
else:
name = f"{group} - {group_value:,} gold"
# Split into multiple fields if they exceed field max length
split_group = split_message(value, FIELD_VALUE_LIMIT)
for subgroup in split_group:
if subgroup != split_group[0]:
name = "\u200F"
embed.add_field(name=name, value=subgroup, inline=False)
if unknown:
long_message += f"\n**There were {unknown['count']} unknown items.**\n"
long_message += f"\nThe total loot value is: **{total_value:,}** gold coins."
if has_marketable:
long_message += f"\n💎 Items marked with this are used in imbuements and might be worth " \
f"more in the market."
embed.description = long_message
embed.set_image(url="attachment://results.png")
# Short message
short_message = f"I've finished parsing your image {ctx.author.mention}." \
f"\nThe total value is {total_value:,} gold coins."
if not ctx.long:
short_message += "\nI've also sent you a PM with detailed information."
# Send on ask_channel or PM
if ctx.long:
await ctx.send(short_message,
embed=embed,
file=discord.File(loot_image_overlay,
"results.png"))
else:
try:
await ctx.author.send(file=discord.File(
loot_image_overlay, "results.png"),
embed=embed)
except discord.Forbidden:
await ctx.send(
f"{ctx.tick(False)} {ctx.author.mention}, I tried pming you to send you the results, "
f"but you don't allow private messages from this server.\n"
f"Enable the option and try again, or try the command channel"
)
else:
await ctx.send(short_message)
def get_item(name):
"""Returns a dictionary containing an item's info, if no exact match was found, it returns a list of suggestions.
The dictionary has the following keys: name, look_text, npcs_sold*, value_sell, npcs_bought*, value_buy.
*npcs_sold and npcs_bought are list, each element is a dictionary with the keys: name, city."""
# Reading item database
c = tibiaDatabase.cursor()
# Search query
c.execute(
"SELECT * FROM Items WHERE title LIKE ? ORDER BY LENGTH(title) ASC LIMIT 15",
("%" + name + "%", ))
result = c.fetchall()
if len(result) == 0:
return None
elif result[0]["title"].lower() == name.lower() or len(result) == 1:
item = result[0]
else:
return [x['title'] for x in result]
try:
# Checking if item exists
if item is not None:
# Checking NPCs that buy the item
c.execute(
"SELECT NPCs.title, city, value "
"FROM Items, SellItems, NPCs "
"WHERE Items.name LIKE ? AND SellItems.itemid = Items.id AND NPCs.id = vendorid "
"ORDER BY value DESC", (name, ))
npcs = []
value_sell = None
for npc in c:
name = npc["title"]
city = npc["city"].title()
if value_sell is None:
value_sell = npc["value"]
elif npc["value"] != value_sell:
break
# Replacing cities for special npcs and adding colors
if name == 'Alesar' or name == 'Yaman':
city = 'Green Djinn\'s Fortress'
item["color"] = Colour.green()
elif name == 'Nah\'Bob' or name == 'Haroun':
city = 'Blue Djinn\'s Fortress'
item["color"] = Colour.blue()
elif name == 'Rashid':
city = get_rashid_city()
item["color"] = Colour(0xF0E916)
elif name == 'Yasir':
city = 'his boat'
elif name == 'Briasol':
item["color"] = Colour(0xA958C4)
npcs.append({"name": name, "city": city})
item['npcs_sold'] = npcs
item['value_sell'] = value_sell
# Checking NPCs that sell the item
c.execute(
"SELECT NPCs.title, city, value "
"FROM Items, BuyItems, NPCs "
"WHERE Items.name LIKE ? AND BuyItems.itemid = Items.id AND NPCs.id = vendorid "
"ORDER BY value ASC", (name, ))
npcs = []
value_buy = None
for npc in c:
name = npc["title"]
city = npc["city"].title()
if value_buy is None:
value_buy = npc["value"]
elif npc["value"] != value_buy:
break
# Replacing cities for special npcs
if name == 'Alesar' or name == 'Yaman':
city = 'Green Djinn\'s Fortress'
elif name == 'Nah\'Bob' or name == 'Haroun':
city = 'Blue Djinn\'s Fortress'
elif name == 'Rashid':
offset = get_tibia_time_zone() - get_local_timezone()
# Server save is at 10am, so in tibia a new day starts at that hour
tibia_time = datetime.now() + timedelta(hours=offset - 10)
city = [
"Svargrond", "Liberty Bay", "Port Hope", "Ankrahmun",
"Darashia", "Edron", "Carlin"
][tibia_time.weekday()]
elif name == 'Yasir':
city = 'his boat'
npcs.append({"name": name, "city": city})
item['npcs_bought'] = npcs
item['value_buy'] = value_buy
# Get creatures that drop it
c.execute(
"SELECT Creatures.title as name, CreatureDrops.percentage "
"FROM CreatureDrops, Creatures "
"WHERE CreatureDrops.creatureid = Creatures.id AND CreatureDrops.itemid = ? "
"ORDER BY percentage DESC", (item["id"], ))
item["dropped_by"] = c.fetchall()
# Checking quest rewards:
c.execute(
"SELECT Quests.title FROM Quests, QuestRewards "
"WHERE Quests.id = QuestRewards.questid and itemid = ?",
(item["id"], ))
quests = c.fetchall()
item["quests"] = list()
for quest in quests:
item["quests"].append(quest["title"])
return item
finally:
c.close()
return
async def wikistats(self, ctx: NabCtx):
"""Shows information about the TibiaWiki database."""
embed = discord.Embed(colour=discord.Colour.blurple(),
title="TibiaWiki database statistics",
description="")
embed.set_thumbnail(url=WIKI_ICON)
version = ""
gen_date = None
with closing(tibiaDatabase.cursor()) as c:
info = c.execute("SELECT * FROM database_info").fetchall()
for entry in info: # type: Dict[str, str]
if entry['key'] == "version":
version = f" v{entry['value']}"
if entry['key'] == "generated_date":
gen_date = float(entry['value'])
achievements = c.execute(
"SELECT COUNT(*) as count FROM achievements").fetchone()
embed.description += f"**‣ Achievements:** {achievements['count']:,}"
creatures = c.execute(
"SELECT COUNT(*) as count FROM creatures").fetchone()
embed.description += f"\n**‣ Creatures:** {creatures['count']:,}"
creatures_drops = c.execute(
"SELECT COUNT(*) as count FROM creatures_drops").fetchone()
embed.description += f"\n\t**‣ Drops:** {creatures_drops['count']:,}"
houses = c.execute(
"SELECT COUNT(*) as count FROM houses").fetchone()
embed.description += f"\n**‣ Houses:** {houses['count']:,}"
imbuements = c.execute(
"SELECT COUNT(*) as count FROM imbuements").fetchone()
embed.description += f"\n**‣ Imbuements:** {imbuements['count']:,}"
items = c.execute("SELECT COUNT(*) as count FROM items").fetchone()
embed.description += f"\n**‣ Items:** {items['count']:,}"
items_attributes = c.execute(
"SELECT COUNT(*) as count FROM items_attributes").fetchone()
embed.description += f"\n\t**‣ Attributes:** {items_attributes['count']:,}"
items_keys = c.execute(
"SELECT COUNT(*) as count FROM items_keys").fetchone()
embed.description += f"\n\t**‣ Keys:** {items_keys['count']:,}"
npcs = c.execute("SELECT COUNT(*) as count FROM npcs").fetchone()
embed.description += f"\n**‣ NPCs:** {npcs['count']:,}"
npcs_buying = c.execute(
"SELECT COUNT(*) as count FROM npcs_buying").fetchone()
embed.description += f"\n\t**‣ Buy offers:** {npcs_buying['count']:,}"
npcs_selling = c.execute(
"SELECT COUNT(*) as count FROM npcs_selling").fetchone()
embed.description += f"\n\t**‣ Sell offers:** {npcs_selling['count']:,}"
npcs_destinations = c.execute(
"SELECT COUNT(*) as count FROM npcs_destinations").fetchone()
embed.description += f"\n\t**‣ Destinations:** {npcs_destinations['count']:,}"
npcs_spells = c.execute(
"SELECT COUNT(*) as count FROM npcs_spells").fetchone()
embed.description += f"\n\t**‣ Spell offers:** {npcs_spells['count']:,}"
quests = c.execute(
"SELECT COUNT(*) as count FROM quests").fetchone()
embed.description += f"\n**‣ Quests:** {quests['count']:,}"
spells = c.execute(
"SELECT COUNT(*) as count FROM spells").fetchone()
embed.description += f"\n**‣ Spells:** {spells['count']:,}"
embed.set_footer(text=f"Database generation date")
embed.timestamp = dt.datetime.utcfromtimestamp(gen_date)
embed.set_author(name=f"tibiawiki-sql{version}",
icon_url="https://github.com/fluidicon.png",
url="https://github.com/Galarzaa90/tibiawiki-sql")
await ctx.send(embed=embed)
async def get_house(name, world=None):
"""Returns a dictionary containing a house's info, a list of possible matches or None.
If world is specified, it will also find the current status of the house in that world."""
c = tibiaDatabase.cursor()
try:
# Search query
c.execute(
"SELECT * FROM houses WHERE name LIKE ? ORDER BY LENGTH(name) ASC LIMIT 15",
("%" + name + "%", ))
result = c.fetchall()
if len(result) == 0:
return None
elif result[0]["name"].lower() == name.lower() or len(result) == 1:
house = result[0]
else:
return [x['name'] for x in result]
if world is None or world not in tibia_worlds:
house["fetch"] = False
return house
house["world"] = world
house["url"] = url_house.format(id=house["id"], world=world)
tries = 5
while True:
try:
async with aiohttp.ClientSession() as session:
async with session.get(house["url"]) as resp:
content = await resp.text(encoding='ISO-8859-1')
except Exception:
tries -= 1
if tries == 0:
log.error(
"get_house: Couldn't fetch {0} (id {1}) in {2}, network error."
.format(house["name"], house["id"], world))
house["fetch"] = False
break
await asyncio.sleep(config.network_retry_delay)
continue
# Trimming content to reduce load
try:
start_index = content.index("\"BoxContent\"")
end_index = content.index("</TD></TR></TABLE>")
content = content[start_index:end_index]
except ValueError:
if tries == 0:
log.error(
"get_house: Couldn't fetch {0} (id {1}) in {2}, network error."
.format(house["name"], house["id"], world))
house["fetch"] = False
break
else:
tries -= 1
await asyncio.sleep(config.network_retry_delay)
continue
m = re.search(r'<BR>(.+)<BR><BR>(.+)', content)
if not m:
return house
house["fetch"] = True
house_info = m.group(1)
house_status = m.group(2)
m = re.search(r'monthly rent is <B>(\d+)', house_info)
if m:
house["rent"] = int(m.group(1))
if "rented" in house_status:
house["status"] = "rented"
m = re.search(
r'rented by <A?.+name=([^\"]+).+(He|She) has paid the rent until <B>([^<]+)</B>',
house_status)
if m:
house["owner"] = urllib.parse.unquote_plus(m.group(1))
house["owner_pronoun"] = m.group(2)
house["until"] = m.group(3).replace(" ", " ")
if "move out" in house_status:
house["status"] = "moving"
m = re.search(
r'will move out on <B>([^<]+)</B> \(time of daily server save\)',
house_status)
if m:
house["move_date"] = m.group(1).replace(" ", " ")
else:
break
m = re.search(
r' and (?:will|wants to) pass the house to <A.+name=([^\"]+).+ for <B>(\d+) gold',
house_status)
if m:
house["status"] = "transfering"
house["transferee"] = urllib.parse.unquote_plus(
m.group(1))
house["transfer_price"] = int(m.group(2))
house["accepted"] = ("will pass " in m.group(0))
elif "auctioned" in house_status:
house["status"] = "auctioned"
if ". No bid has" in content:
house["status"] = "empty"
break
m = re.search(
r'The auction (?:has ended|will end) at <B>([^<]+)</B>\. '
r'The highest bid so far is <B>(\d+).+ by .+name=([^\"]+)\"',
house_status)
if m:
house["auction_end"] = m.group(1).replace(" ", " ")
house["top_bid"] = int(m.group(2))
house["top_bidder"] = urllib.parse.unquote_plus(m.group(3))
break
pass
break
return house
finally:
c.close()
async def get_guild(name, title_case=True, tries=5) -> Optional[Guild]:
"""Fetches a guild from TibiaData, parses and returns a Guild object
The Guild object contains all the information available on Tibia.com
Guilds are case sensitive on tibia.com so guildstats.eu is checked for correct case.
If the guild can't be fetched due to a network error, an NetworkError exception is raised
If the character doesn't exist, None is returned."""
guildstats_url = f"http://guildstats.eu/guild?guild={urllib.parse.quote(name)}"
if tries == 0:
log.error(
"get_guild_online: Couldn't fetch {0}, network error.".format(
name))
raise NetworkError()
# Fix casing using guildstats.eu if needed
# Sorry guildstats.eu :D
if not title_case:
try:
async with aiohttp.ClientSession() as session:
async with session.get(guildstats_url) as resp:
content = await resp.text(encoding='ISO-8859-1')
except Exception:
await asyncio.sleep(config.network_retry_delay)
return await get_guild(name, title_case, tries - 1)
# Make sure we got a healthy fetch
try:
content.index('<div class="footer">')
except ValueError:
await asyncio.sleep(config.network_retry_delay)
return await get_guild(name, title_case, tries - 1)
# Check if the guild doesn't exist
if "<div>Sorry!" in content:
return None
# Failsafe in case guildstats.eu changes their websites format
try:
content.index("General info")
content.index("Recruitment")
except Exception:
log.error(
"get_guild_online: -IMPORTANT- guildstats.eu seems to have changed their websites format."
)
raise NetworkError
start_index = content.index("General info")
end_index = content.index("Recruitment")
content = content[start_index:end_index]
m = re.search(r'<a href="set=(.+?)"', content)
if m:
name = urllib.parse.unquote_plus(m.group(1))
else:
name = name.title()
tibiadata_url = f"https://api.tibiadata.com/v2/guild/{urllib.parse.quote(name)}.json"
# Fetch website
try:
async with aiohttp.ClientSession() as session:
async with session.get(tibiadata_url) as resp:
content = await resp.text(encoding='ISO-8859-1')
except Exception:
await asyncio.sleep(config.network_retry_delay)
return await get_guild(name, title_case, tries - 1)
content_json = json.loads(content)
guild = Guild.parse_from_tibiadata(content_json)
if guild is None:
if title_case:
return await get_guild(name, False)
else:
return None
if guild.guildhall is not None:
with closing(tibiaDatabase.cursor()) as c:
c.execute("SELECT id FROM houses WHERE name LIKE ?",
(guild.guildhall["name"].strip(), ))
result = c.fetchone()
if result:
guild.guildhall["id"] = result["id"]
return guild
async def get_character(name,
tries=5,
*,
bot: commands.Bot = None) -> Optional[Character]:
"""Fetches a character from TibiaData, parses and returns a Character object
The character object contains all the information available on Tibia.com
Information from the user's database is also added, like owner and highscores.
If the character can't be fetch due to a network error, an NetworkError exception is raised
If the character doesn 't exist, None is returned.
"""
if tries == 0:
log.error(
"get_character: Couldn't fetch {0}, network error.".format(name))
raise NetworkError()
try:
url = f"https://api.tibiadata.com/v2/characters/{urllib.parse.quote(name.strip(), safe='')}.json"
except UnicodeEncodeError:
return None
# Fetch website
try:
character = CACHE_CHARACTERS[name.lower()]
except KeyError:
req_log.info(f"get_character({name})")
try:
async with aiohttp.ClientSession() as session:
async with session.get(url) as resp:
content = await resp.text(encoding='ISO-8859-1')
except Exception:
await asyncio.sleep(config.network_retry_delay)
return await get_character(name, tries - 1)
content_json = json.loads(content)
character = Character.parse_from_tibiadata(content_json)
CACHE_CHARACTERS[name.lower()] = character
if character is None:
return None
if character.house is not None:
with closing(tibiaDatabase.cursor()) as c:
c.execute("SELECT id FROM houses WHERE name LIKE ?",
(character.house["name"].strip(), ))
result = c.fetchone()
if result:
character.house["houseid"] = result["id"]
# If the character exists in the online list use data from there where possible
for c in global_online_list:
if c == character:
character.level = c.level
character.vocation = c.vocation
break
# Database operations
c = userDatabase.cursor()
# Skills from highscores
c.execute("SELECT category, rank, value FROM highscores WHERE name LIKE ?",
(character.name, ))
results = c.fetchall()
if len(results) > 0:
character.highscores = results
# Check if this user was recently renamed, and update old reference to this
for old_name in character.former_names:
c.execute("SELECT id FROM chars WHERE name LIKE ? LIMIT 1",
(old_name, ))
result = c.fetchone()
if result:
with userDatabase as conn:
conn.execute("UPDATE chars SET name = ? WHERE id = ?",
(character.name, result["id"]))
log.info(
"{0} was renamed to {1} during get_character()".format(
old_name, character.name))
# Discord owner
c.execute(
"SELECT user_id, vocation, name, id, world, guild FROM chars WHERE name LIKE ? OR name LIKE ?",
(name, character.name))
result = c.fetchone()
if result is None:
# Untracked character
return character
character.owner = result["user_id"]
if result["vocation"] != character.vocation:
with userDatabase as conn:
conn.execute("UPDATE chars SET vocation = ? WHERE id = ?", (
character.vocation,
result["id"],
))
log.info(
"{0}'s vocation was set to {1} from {2} during get_character()"
.format(character.name, character.vocation,
result["vocation"]))
# This condition PROBABLY can't be met again
if result["name"] != character.name:
with userDatabase as conn:
conn.execute("UPDATE chars SET name = ? WHERE id = ?", (
character.name,
result["id"],
))
log.info("{0} was renamed to {1} during get_character()".format(
result["name"], character.name))
if result["world"] != character.world:
with userDatabase as conn:
conn.execute("UPDATE chars SET world = ? WHERE id = ?", (
character.world,
result["id"],
))
log.info(
"{0}'s world was set to {1} from {2} during get_character()".
format(character.name, character.world, result["world"]))
if character.guild is not None and result["guild"] != character.guild[
"name"]:
with userDatabase as conn:
conn.execute("UPDATE chars SET guild = ? WHERE id = ?", (
character.guild["name"],
result["id"],
))
log.info(
"{0}'s guild was set to {1} from {2} during get_character()".
format(character.name, character.guild["name"],
result["guild"]))
if bot is not None:
bot.dispatch("character_change", character.owner)
if character.guild is None and result["guild"] is not None:
with userDatabase as conn:
conn.execute("UPDATE chars SET guild = ? WHERE id = ?", (
None,
result["id"],
))
log.info(
"{0}'s guild was set to {1} from {2} during get_character()".
format(character.name, None, result["guild"]))
if bot is not None:
bot.dispatch("character_change", character.owner)
return character
