async def cmd_show_records(client, message, _) -> None:
guild_records = await fr.get_records_by_guild(message.guild.id)
records_as_tuples = []
for record in guild_records.values():
record_item = record.get("record_item")
record_title = record.get("record_title")
record_minimum_requirement = record.get("minimum_requirement")
record_function = record.get("function")
if record_item:
record_value = record_item[0][0]
if record_function:
record_value = await record_function(record_item)
record_holder = record_item[0]['faceit_nickname']
record_date = datetime.utcfromtimestamp(record_item[0]['finished_at']).strftime('%Y-%m-%d')
match_score = record_item[0]['match_score']
item = record_title, record_value, record_holder, record_date, match_score
else:
if record_function:
record_minimum_requirement = await record_function(record_minimum_requirement)
item = record_title, record_minimum_requirement, "-", "-", "-"
records_as_tuples.append(item)
records_as_tuples = sorted(records_as_tuples, reverse=True, key=lambda x: x[3])
column_titles = ["Record name", "Value", "Record holder", "Record date", "Match score"]
table = tablemaker(column_titles, records_as_tuples)
msg = ("```" + table + "```")
if len(msg) > 2000:
widest_record_title, widest_record_value, widest_record_holder, = widest_in_list_of_tuples(records_as_tuples, 0), widest_in_list_of_tuples(records_as_tuples, 1), max([widest_in_list_of_tuples(records_as_tuples, 2),len("Record holder")])
table_first_half, table_second_half = records_as_tuples[:len(records_as_tuples)//2], records_as_tuples[len(records_as_tuples)//2:] #todo implement properly
await message.channel.send("```" + tablemaker(column_titles, table_first_half, column_widths=[widest_record_title, widest_record_value, widest_record_holder,15,10]) + "```")
await asyncio.sleep(.5)
await message.channel.send("```" + tablemaker(column_titles, table_second_half, column_widths=[widest_record_title, widest_record_value, widest_record_holder,15,10]) + "```")
else:
await message.channel.send(msg)
async def get_blackjack_toplist():
items = await db.fetch("""
SELECT
(wins_bj / (wins_bj + losses_bj)) * 100,
wins_bj,
wins_bj + losses_bj,
name,
losses_bj,
surrenders,
ties,
moneyspent_bj,
moneywon_bj,
concat('#', row_number() OVER (ORDER BY (wins_bj / (wins_bj + losses_bj)) * 100 desc)) AS rank
FROM
casino_stats
JOIN
discord_user USING (user_id)
WHERE
(wins_bj + losses_bj) > 1
ORDER BY
(wins_bj / (wins_bj + losses_bj)) * 100 DESC
LIMIT 10
""")
if len(items) == 0:
return None, None
toplist = []
for item in items:
pct, wins, total, name, losses, surrenders, ties, moneyspent, moneywon, rank = item
new_item = (name[0:10], rank, total, wins, losses, surrenders, ties, moneyspent, round(moneywon), round(pct, 3))
toplist.append(new_item)
# toplist = addsymboltolist(toplist,9,' %')
return tablemaker(['NAME', 'RANK', 'TOT', 'W', 'L', 'S', 'T', '$ SPENT', '$ WON', '%'], toplist), len(toplist)
async def get_whosaidit_ranking():
items = await db.fetch("""
with score as (
select
user_id,
sum(case playeranswer when 'correct' then 1 else 0 end) as wins,
sum(case playeranswer when 'wrong' then 1 else 0 end) as losses
from
whosaidit_stats_history
where
date_trunc('week', time) = date_trunc('week', current_timestamp)
group by user_id)
select
wins::float / (wins + losses) * 100 as ratio,
least(0.20 * wins, 20) as bonuspct,
wins,
wins + losses as total,
name,
concat('#', row_number() OVER (ORDER BY (wins::float / (wins + losses) * 100)+ least(0.20* wins, 20) desc)) AS rank
from
score
join
discord_user using (user_id)
where
(wins + losses) >= 20
order by
rank asc""")
if len(items) == 0:
return None, None
toplist = []
for item in items:
pct, bonuspct, correct, total, name, rank = item
new_item = (name, rank, correct, total, round(pct, 3), bonuspct)
toplist.append(new_item)
return tablemaker(['NAME', 'RANK', 'CORRECT', 'TOTAL', 'ACCURACY', 'BONUS PCT'], toplist), len(toplist)
async def get_slots_toplist():
items = await db.fetch("""
SELECT
name,
concat('#', row_number() OVER (ORDER BY (wins_slots / (wins_slots + losses_slots)) * 100 desc)) AS rank,
wins_slots + losses_slots,
wins_slots,
losses_slots,
moneyspent_slots,
moneywon_slots,
moneywon_slots - moneyspent_slots as profit,
(wins_slots / (wins_slots + losses_slots)) * 100
FROM
casino_stats
JOIN
discord_user USING (user_id)
WHERE
AND (wins_slots + losses_slots) > 100
ORDER BY
moneywon_slots - moneyspent_slots DESC
LIMIT 10
""")
if len(items) == 0:
return None, None
toplist = []
for item in items:
name, rank, total, wins, losses, moneyspent, moneywon, profit, pct = item
new_item = (name[0:10], rank, total, wins, losses, moneyspent, moneywon, profit, round(pct, 3))
toplist.append(new_item)
# toplist = addsymboltolist(toplist,7,' %')
return tablemaker(['NAME', 'RANK', 'TOT', 'W', 'L', '$ SPENT', '$ WON', '$ PROFIT', '%'], toplist), len(toplist)
async def cmd_ence(client, message, arg) -> None:
now = to_helsinki(as_utc(datetime.datetime.now())).replace(tzinfo=None)
if not LAST_CHECKED:
await message.channel.send("https://i.ytimg.com/vi/CRvlTjeHWzA/maxresdefault.jpg\n(Matches haven't been fetched yet as the bot was just started, please try again soon)")
else:
list_of_matches = deepcopy([x for y in sorted(MATCHES_DICT.values(), key=lambda x: x[0].get('date')) for x in y])
list_of_matches = [await convert_to_list(match_dict) for match_dict in list_of_matches if match_dict.get('date') > now]
await message.channel.send((("\nAs of %s: ```" % to_helsinki(as_utc(LAST_CHECKED)).strftime(
"%Y-%m-%d %H:%M")) + tablemaker(
['COMPETITION', 'HOME TEAM', 'AWAY TEAM', 'MAP', 'STATUS', 'DATE', 'TOD']
, list_of_matches) + "\n#EZ4ENCE```"))
async def top_message_counts(filters, params, excludecommands, guild_id: str):
# todo: fix dangerous query
sql_excludecommands = "AND content NOT LIKE '!%%'" if excludecommands else ""
user_days_in_chat = await get_user_days_in_chat(guild_id)
items = await db.fetch("""
with custommessage as (
SELECT
coalesce(name, m->'author'->>'username') as name,
user_id,
count(*) as message_count
FROM
message
JOIN
discord_user using (user_id)
WHERE
guild_id = '{guild_id}'
AND NOT bot
AND NOT EXISTS (SELECT * FROM excluded_users WHERE excluded_user_id = user_id)
{sql_excludecommands}
{filters}
GROUP BY
coalesce(name, m->'author'->>'username'), user_id)
SELECT
name,
user_id,
message_count,
(message_count / sum(count(*)) over()) * 100 as pctoftotal
FROM
message
JOIN
custommessage using (user_id)
WHERE
guild_id = '{guild_id}'
AND NOT bot
AND NOT EXISTS (SELECT * FROM excluded_users WHERE excluded_user_id = user_id)
{sql_excludecommands}
{filters}
GROUP BY
user_id, message_count, name
ORDER BY
pctoftotal DESC
""".format(guild_id=guild_id, filters=filters, sql_excludecommands=sql_excludecommands), *params)
if not items:
return None, None
list_with_msg_per_day = []
for item in items:
name, user_id, message_count, pct_of_total = item
msg_per_day = message_count / user_days_in_chat[user_id]
new_item = (name, message_count, round(msg_per_day, 3), round(pct_of_total, 3))
list_with_msg_per_day.append(new_item)
top_ten = add_rank_to_list(sorted(list_with_msg_per_day, key=lambda x: x[2], reverse=True)[:10])
return tablemaker(['NAME', 'RANK', 'TOTAL', 'MSG PER DAY', '% OF TOTAL', emoji.FIRST_PLACE_MEDAL +
emoji.SECOND_PLACE_MEDAL + emoji.THIRD_PLACE_MEDAL], top_ten), len(top_ten)
async def get_faceit_leaderboard(guild_id):
toplist = []
ranking = await faceit_db.get_toplist_from_db(guild_id)
if not ranking:
return None, None
for item in ranking:
eu_ranking, faceit_nickname, csgo_elo, skill_level, last_entry_time, player_last_played = item
if not eu_ranking:
continue
new_item = eu_ranking, faceit_nickname, csgo_elo, skill_level, await get_last_seen_string(
player_last_played)
toplist.append(new_item)
toplist_string = tablemaker(['EU RANKING', 'NAME', 'CS:GO ELO', 'SKILL LEVEL', 'LAST SEEN'],
toplist)
return toplist_string + (
'\nLast changed: %s' % to_utc(as_helsinki(
last_entry_time)).strftime("%d/%m/%y %H:%M")), len(toplist)
async def cmd_leader(client, message, _):
leaders = await db.fetch("""
SELECT
row_number() OVER (ORDER BY balance DESC) AS rank,
name,
balance
FROM casino_account
JOIN discord_user USING (user_id)
ORDER BY balance
DESC LIMIT 5
""")
if len(leaders) > 0:
def format_leader(rank, name, balance):
return "#{0}".format(rank), name, "${0}".format(balance)
formatted = map(lambda row: format_leader(*row), leaders)
reply = tablemaker(['Rank', 'Name', 'Balance'], formatted)
await message.channel.send('```{0}```'.format(reply))
async def get_whosaidit_weekly_ranking():
items = await db.fetch("""
-- week_score = score per pelaaja per viikko
with week_score as (
select
date_trunc('week', time) as dateadded,
user_id,
sum(case playeranswer when 'correct' then 1 else 0 end) as wins,
sum(case playeranswer when 'wrong' then 1 else 0 end) as losses,
-- accuracy
100.0 * sum(case playeranswer when 'correct' then 1 else 0 end) / count (*) as accuracy,
-- bonus
least(20.0, sum(case playeranswer when 'correct' then 1 else 0 end) * 0.20) as bonus,
-- score = accuracy + bonus
100.0 * sum(case playeranswer when 'correct' then 1 else 0 end) / count(*) + least(20.0, sum(case playeranswer when 'correct' then 1 else 0 end) * 0.20) as score,
-- MAGIC! weeks_best_score on kyseisen viikon paras score
max(100.0 * sum(case playeranswer when 'correct' then 1 else 0 end) / count (*) + least(20.0, sum(case playeranswer when 'correct' then 1 else 0 end) * 0.20)) over (partition by date_trunc('week', time)) as weeks_best_score,
-- more magic
count(user_id) over (partition by date_trunc('week', time)) as players
from whosaidit_stats_history
group by user_id, date_trunc('week', time)
having count(*) >= 20
)
select dateadded, name, score, wins, losses, accuracy, bonus, players, wins + losses as total
from week_score
join discord_user using (user_id)
-- Valitaan vain rivit, joilla score on viikon paras score, eli voittajat
where not
AND date_trunc('week', dateadded) = date_trunc('week', current_timestamp) and score = weeks_best_score and players >= 2
order by dateadded desc""")
if len(items) == 0:
return None, None
toplist = []
for item in items:
dateadded, name, score, wins, losses, accuracy, bonus, players, total = item
new_item = (get_week_with_year(dateadded), name, round(score, 3), wins, losses, total)
toplist.append(new_item)
return tablemaker(['WEEK', 'NAME', 'SCORE', 'WINS', 'LOSSES', 'TOTAL'], toplist)
names = []
for i in range(0,num_of_names):
while(1):
print('Enter roommate #',i+1,'name:')
temp = input()
if(temp in names):
print('Kindly enter a unique name. That name is already taken!')
else:
break
names.append(temp)
expenses = []
for i in range(0,num_of_names):
expenses.append([0]*num_of_names)
while(1):
print("Choose Option:")
print("1. Add Expense")
print("2. Show Expenses")
print("3. Exit Program")
option = input()
if(option == '1'):
expenses = calculator(names,expenses)
elif(option == '2'):
tablemaker(names,expenses)
elif(option == '3'):
exit(0)
else:
print("Enter a valid option")
async def get_worst_grammar(guild_id: str):
items = await db.fetch("""
with custommessage as (
select
coalesce(name, m->'author'->>'username') as name,
user_id,
count(*) as message_count
from
message
join
discord_user using (user_id)
where
guild_id = $1
AND NOT bot
AND content not LIKE '!%%'
AND content not like '%http%'
AND content not like '%www%'
AND content ~* '^[A-ZÅÄÖ]'
AND NOT EXISTS (SELECT * FROM excluded_users WHERE excluded_user_id = user_id)
group by
coalesce(name, m->'author'->>'username'), user_id)
select
name,
user_id,
message_count,
message_count - count(*) as bad_messages,
100 - (count(*) / message_count::float) * 100 as pctoftotal
from
message
join
custommessage using (user_id)
where
guild_id = $1
AND NOT bot
and message_count > 300
AND NOT EXISTS (SELECT * FROM excluded_users WHERE excluded_user_id = user_id)
AND content NOT LIKE '!%%'
AND content ~ '^[A-ZÅÄÖ][a-zöäå]'
AND content NOT LIKE '%www%'
AND content NOT LIKE '%http%'
or content ~* '[A-ZÅÄÖ]\?$'
or content ~* '[A-ZÅÄÖ]\.$'
or content ~* '[A-ZÅÄÖ]!$'
or (length(content) > 25
and content like '%,%')
group by
user_id, message_count, name
HAVING
count(*) > 300
order by
pctoftotal desc
""", guild_id)
if not items:
return None, None
toplist = []
for item in items:
name, user_id, message_count, bad_messages, bs_percentage = item
new_item = (name[0:10], message_count, bad_messages, round(bs_percentage, 3))
toplist.append(new_item)
top_ten = add_rank_to_list(sorted(toplist, key=lambda x: x[3], reverse=True)[:10])
return tablemaker(
['NAME', 'RANK', 'TOTAL MSGS', 'BAD MSGS', 'BAD GRAMMAR %', emoji.FIRST_PLACE_MEDAL +
emoji.SECOND_PLACE_MEDAL + emoji.THIRD_PLACE_MEDAL], top_ten), len(top_ten)